Spreadsheet: 287422d1388782385-converting-a-spreadsheet-into-a-heirarchical-display-of-the-data-fruit
Sheet: Origianal Fields not used

Form section> | Linked column> | Data field> | Field type> | Reportable Field? | Where data is pulled from> (system notes) | Provantis/TPS? | Data Warehouse | Devlopment? | SQL (Primary) | SQL (Secondary)
 | None | Study related? | radio or other toggle |  | hardcoded y/n
 | None | Shift when even occurred (default as 1st, option to update) - DELETE | dropdown |  | system admin
 |  | SOP / Method? Group |  |  | eDocs |  |  |  | C.Kost Note 6/10/13 - added to support delimitation of SOP# query next.  Not sure if it will be needed.  NextDocs will probably have a better way of handling this.  I could not find a table with these categorizations in eDocs.  SELECT DISTINCT SUBSTR(qm_document_base.object_name,1,INSTR(qm_document_base.object_name,'-')-1) as Doc_Group  FROM           qm_document_base           join qm_filestorage on qm_filestorage.parent_object_id = qm_document_base.r_object_id           join qm_filestoragecontent on qm_filestoragecontent.r_object_id = qm_filestorage.r_object_id            WHERE         qm_document_base.r_object_id = qm_filestorage.parent_object_id AND qm_filestorage.r_object_id = qm_filestoragecontent.r_object_id AND (qm_document_base.r_object_type = 'qu_sop_document'        or qm_document_base.r_object_type = 'qu_policy_document'     --   or qm_document_base.r_object_type = 'qu_corporate_form'        or qm_document_base.r_object_type = '[NAME]'       or qm_document_base.r_object_type = 'qu_immuno_methods') AND qm_document_base.i_has_folder = 1 AND qm_document_base.r_issystem_copy = 0 AND qm_document_base.r_lifecyclestate in (1229, 4174214, 6304017, 32979908) AND qm_document_base.object_name <> 'POL-SEC-7' AND qm_filestorage.rendition = 0           ORDER BY SUBSTR(qm_document_base.object_name,1,INSTR(qm_document_base.object_name,'-')-1)
 | Y | study interval (pretest, terminal, recovery, phase (prompt free text), other (prompt free text)) | dropdown |  | Mulitple | probably calculated in TPS; see [NAME] to determine if [NAME] has concept of interval - is this just calculated? | NO | Match SOP/provanits and update? | [NAME], per [NAME], Study interval is relative to a point in time in the life of the study.  There is no master table of Study Intervals in Provantis or TPS.  It sounds like it could be calculated but would be difficult.  Can we use a hardcoded list here? c.kost 6/10/13 do! - Analyze percent of study numbers with an alpha character. Per [NAME]'ll probably go with a hardcoded list in NextDocs.
 |  | Study day offset (0 or 1) | supporting info |  |  | STPR_OPTIONS.VALUEN where STPR_OPTIONS.opt_id=1 |  |  | See Temp Note 1 below - C.Kost Note 6/11/13: [NAME] generated SQL. There are two separate Queries (due to contexts) that pull the information requested with the applicable joins.
 |  | Animal start date | supporting info |  |  | STPR_STD_ANML.START_DATE |  |  | See Temp Note 1 below
 | Y | study day | Calculated in form |  | calculated field in the NextDocs Form: Date of event - animal start date + study day offset
 | Y | route of administration - auto-pop | dropdown; prepopulate based on study number and group |  | Mulitple | STCD_ADMIN_ROUTE_DESCR.DESCR | DW - DIM_StudyDesign via DIM_Study (via SF) |  | Same as "species - auto-pop" query.
 | AC | dose level - derive from group no. | prepopulate from group no. |  |  | STPR_GRP.LONG_NAME |  |  | See Temp Note 1 below - Per [NAME], this is usually take from the Group name.  [NAME] will double check on how TPS handles this so we can be consistent.
 | Y | dose volume - if pulled in, keep; if not, do not keep | prepopulate |  |  | STPR_DOSE_DET.VEH_AMOUNT and VEH_UNIT.DESCR if  Rout_Unit.DESCR = perKg/BW, otherwise may be STPR_DOSE_DET.TA_AMOUNT if dosed as received from sponsor |  |  | See Temp Note 1 below - Per Rober Bacon 6/10/13 - This is treatment specific, so there are potential for multiple treatments per group.
 | Y | vehicle (only keep if auto-pop) | prepopulate |  |  | [NAME] |  |  | See Temp Note 1 below - Per Rober Bacon 6/10/13 - This is treatment specific, so there are potential for multiple treatments per group.  Another Note: Per [NAME], vehicle is freeform and can differ between Spnsors.
 | Y | type of molecule (only keep if auto-pop) | prepopulate |  |  | Per [NAME]-not housed in Provantis/Dispense |  |  | See Temp Note 1 above - Not in Provantis or Dispense.
 |  | Environmental Specific Events |  |  | future? |  |  | Siemens link? | Chris - Check into this 6/10/13. ASK [NAME].
 | A | °C/°F or % Rh Recorded | free text |  | future? |  |  | Siemens link?
 | A | instances of out of spec | number |  | future? |  |  | Siemens link?
 |  | Facility Event Type | radio buttons: Airflow, Humidity,Temperature
 | J | who conducted investigation - via audit trail | drop |  | AD |  |  |  | Temp Note 2 - C.Kost 6/7/13 Note: [NAME] does not have code for this.  [NAME] has written C# code to extract similar info from AD.  I think it depends on what [NAME] can best use as to whether we go to AD or the DW for the information. | Alternate Source DW: SELECT EmployeeKey,       EmployeeName,       Title,       [NAME],       EmployeeNumber,       FirstName,       LastName,       Active,       ADUserName   FROM dbo.Dim_Employee   WHERE Active = 1   ORDER BY [NAME]
 | J | what was looked at/reviewed | free text |  | free text
 | J | Potential impact on other analysis or interpretations of data? (e.g., TK, ClinPath, etc.) | multi-select |  | Study Personnel List (multiple) Quick Look up task sched | AVAILABLE |  |  | Per Andrea 6/10/13 - To be determined based on Stephanie Morley's findings.
 | J | study impact | free text |  | system admin
 | J | Does this event adversely affect the quality or integrity of the study? - Does it impact the integrity of the study | Y/N radio |  | hardcoded y/n
 |  | Justification | free text
 | J | SUMMARY:  Outcome of investigation (steps of investigation) attach a file (e.g. run results) | free text |  | free text
 | J | direction from SD/PI/CS/BM/RSO/Mgmt. (include as part of the summary/diary) | free text |  | free text
 | J | taken out of use? (subsection of outcome, checkbox) | checkbox |  | ExyLMS (ICOC table) |  |  |  | c.kost note 5/31/13: This query needs more work.  I need to know how to delimit the data.  2 problems: how to ID the most recent row for an instrument and how to determine its status.  SELECT inst.INSTRUMENT,   inst.[NAME],   inst.[NAME],   inst.[NAME],   inst.AVAILABLE,  -- T/F Use to delimit list?   inst.REMOVED,  -- T/F Use to delimit list?   inst.EIN_ID,   icoc.ACTIVE,   icoc.ACTION_CODE,   icoc.REASON FROM dbo.INSTRUMENTS inst JOIN dbo.ICOC icoc  on inst.INSTRUMENT_ID = icoc.INSTRUMENT_ID ORDER BY inst.INSTRUMENT_ID, icoc.ICOC_ID
 |  | Safety Event Only - Location of Material Released and/or Hazard - Location of Event | [NAME] with "other" |  | see above |  | DIM_Location?  Check with [NAME] and see if this is viable - c.kost 5/31/13 6/10/13 note - Resolved, see above. |  | See "location of event"
 | Specific items not currently on form.  Will these appear only if the Main category is selected as Safety? | Safety Event Only - Date of Release | validated time field,  < now |  | validated time field,  < now
 |  | Safety Event Only - Time of Release | Time |  | validated time field
 |  | Safety Event Only - Physical State | [NAME] |  | Solid/liquid/gas hardcoded
 |  | Safety Event Only - Chemical Name | free text |  | MSDS list? |  |  |  | Ask [NAME] where we could get this from. 6/10/13 note - [NAME] to check with [NAME].
 |  | Safety Event Only - Was Any Material Released Outside of Building | Y/N |  | hardcoded y/n
 |  | Safety Event Only - Was Any Material Released Down a Drain | Y/N |  | hardcoded y/n
 |  | Safety Event Only - Was #555 Contacted | Y/N - Free Text for No |  | hardcoded y/n with NO giving a free text box
 |  | Safety Event Only - What Caused The Release | Free Text |  | free text
 |  | Safety Event Only - Name of Reporting Individual | free text |  | AD with "other" free text option |  |  |  | See Temp Note 2 above | See "who conducted investigation - via audit trail"
 |  | Safety Event Only - Who Was Involved In Spill | free text |  | AD multiple with "other" free text option |  |  |  | See Temp Note 2 above | See "who conducted investigation - via audit trail"
 |  | Safety Event Only - Were Outside Resources Called IN | Y/N - Text Box for who when Yes |  | hardcoded y/n with yes giving a free text box
 |  | Safety Event Only - Who Was Involved In Clean-Up | free text |  | AD mutliple with "other" free text option |  |  |  | See Temp Note 2 above | See "who conducted investigation - via audit trail"
 |  | Safety Event Only - Time and Date Event Ended | validated time field, must be > start and < now |  | validated time field, must be > start and < now
 |  | Safety Event Only - Steps Taken to Prevent | Free Text |  | free text
 |  | Safety Event Only - Potential Hazard | [NAME] |  | free text
 |  |  |  |  |  | Temp Note 1. - The two Provantis queries. |  |  | [NAME],   STCD_ACT_DESCR.DESCR,   STCD_ADMIN_ROUTE_DESCR.DESCR,   STPR_DOSE_DET.VEH_AMOUNT,   VEH_UNIT.DESCR,   STPR_DOSE_DET.TA_AMOUNT,   TA_UNIT.DESCR,   Rout_Unit.DESCR,   STPR_GRP.GRP_NO,   STPR_GRP.LONG_NAME,   VEHICLE.VEH_FULL_NAME FROM   STCD_ACT_DESCR INNER JOIN STCD_ACT ON (STCD_ACT_DESCR.ACT_ID=STCD_ACT.ID)    INNER JOIN STPR_STUDY ON (STCD_ACT.STD_ID=STPR_STUDY.ID)    INNER JOIN STPR_GRP [NAME] (STPR_STUDY.ID=STPR_GRP.STD_ID)    INNER JOIN STPR_DOSE_DET ON (STPR_DOSE_DET.GRP_ID=STPR_GRP.ID)    LEFT OUTER JOIN STCD_UNIT_DESCR  TA_UNIT ON (TA_UNIT.UNIT_ID=STPR_DOSE_DET.TA_UNITS_ID)    INNER JOIN STCD_UNIT_DESCR  VEH_UNIT ON (VEH_UNIT.UNIT_ID=STPR_DOSE_DET.VEH_UNITS_ID)    INNER JOIN (    select  ilco_subrequest.request_id,  ilco_subrequest.id,  ilco_subrequest.group_id from ilco_subrequest union all select  ilco_subrequest_aud.request_id,  ilco_subrequest_aud.id,  ilco_subrequest_aud.group_id from ilco_subrequest_aud   )  Table__423 ON (STPR_GRP.ID=Table__423.GROUP_ID)    INNER JOIN (    select REP_SEQ_DETAILS.RSD_FROM_FK, RSD_PROV_SUBREQUEST_ID_FK FROM DISPENSE.REP_SEQ_DETAILS REP_SEQ_DETAILS INNER JOIN DISPENSE.FORM_OPERATION FORM_OPERATION ON  REP_SEQ_DETAILS.RSD_FO_OP_ID_FK=FORM_OPERATION.FO_OP_ID_PK WHERE  (FORM_OPERATION.FO_OPER_TYPE=2 OR [NAME] oR FORM_OPERATION.FO_OPER_TYPE=3 OR FORM_OPERATION.FO_OPER_TYPE=19)    )  Table__429 ON (Table__423.ID=Table__429.RSD_PROV_SUBREQUEST_ID_FK)    INNER JOIN VEHICLE_BATCH ON (Table__429.RSD_FROM_FK=VEHICLE_BATCH.VB_BATCH_ID_PK)    INNER JOIN VEHICLE ON (VEHICLE.VEH_VEHICLE_ID_PK=VEHICLE_BATCH.VB_VEHICLE_ID_FK)    LEFT OUTER JOIN STPR_TREATMT ON (STPR_TREATMT.ACT_ID=STCD_ACT.ID)    INNER JOIN STCD_ADMIN_ROUTE ON (STPR_TREATMT.ADMIN_ROUTE_ID=STCD_ADMIN_ROUTE.ID)    INNER JOIN STCD_ADMIN_ROUTE_DESCR ON (STCD_ADMIN_ROUTE.ID=STCD_ADMIN_ROUTE_DESCR.ADMIN_ROUTE_ID)    INNER JOIN STCD_UNIT_DESCR  Rout_Unit ON (STPR_TREATMT.ADMIN_ROUTE_UNIT_ID=Rout_Unit.UNIT_ID)    WHERE ( STCD_ACT_DESCR.LANG_ID=1   )   AND     STPR_STUDY.STD_REF  IN  ( '1746-002'  ) | [NAME],   STPR_GRP.GRP_NO,   STPR_GRP.LONG_NAME,   STPR_STD_ANML.ANML_NO,   VEHICLE.VEH_FULL_NAME,   STPR_OPTIONS.VALUEN,   STCD_SPECIES_DESCR.DESCR,   STPR_STD_ANML.START_DATE FROM   STCD_SPECIES_DESCR INNER JOIN STCD_STRAIN ON (STCD_SPECIES_DESCR.SPECIES_ID=[NAME])    INNER JOIN STPR_ANML ON (STCD_STRAIN.ID=STPR_ANML.STRAIN_ID)    INNER JOIN STPR_STD_ANML ON (STPR_STD_ANML.UAR=STPR_ANML.UAR)    INNER JOIN STPR_GRP [NAME] (STPR_STD_ANML.GRP_ID=STPR_GRP.ID)    INNER JOIN STPR_STUDY ON (STPR_STUDY.ID=STPR_GRP.STD_ID)    INNER JOIN STPR_OPTIONS ON (STPR_STUDY.ID=STPR_OPTIONS.STD_ID)    INNER JOIN (    select  ilco_subrequest.request_id,  ilco_subrequest.id,  ilco_subrequest.group_id from ilco_subrequest union all select  ilco_subrequest_aud.request_id,  ilco_subrequest_aud.id,  ilco_subrequest_aud.group_id from ilco_subrequest_aud   )  Table__423 ON (STPR_GRP.ID=Table__423.GROUP_ID)    INNER JOIN (    select REP_SEQ_DETAILS.RSD_FROM_FK, RSD_PROV_SUBREQUEST_ID_FK FROM DISPENSE.REP_SEQ_DETAILS REP_SEQ_DETAILS INNER JOIN DISPENSE.FORM_OPERATION FORM_OPERATION ON  REP_SEQ_DETAILS.RSD_FO_OP_ID_FK=FORM_OPERATION.FO_OP_ID_PK WHERE  (FORM_OPERATION.FO_OPER_TYPE=2 OR [NAME] oR FORM_OPERATION.FO_OPER_TYPE=3 OR FORM_OPERATION.FO_OPER_TYPE=19)    )  Table__429 ON (Table__423.ID=Table__429.RSD_PROV_SUBREQUEST_ID_FK)    INNER JOIN VEHICLE_BATCH ON (Table__429.RSD_FROM_FK=VEHICLE_BATCH.VB_BATCH_ID_PK)    INNER JOIN VEHICLE ON (VEHICLE.VEH_VEHICLE_ID_PK=VEHICLE_BATCH.VB_VEHICLE_ID_FK)    WHERE ( STPR_OPTIONS.opt_id=1  )   AND  ( STCD_SPECIES_DESCR.LANG_ID = 1  )   AND     STPR_STUDY.STD_REF  IN  ( '1746-002'  )
 |  | Clin path | pick list
 |  | TK/PK/PD | pick list
 |  | [NAME] | pick list
 |  | BIO | pick list
 |  | Formulations | pick list
 |  | Vet | pick list
 |  | Other | pick list
 |  | Principal Investigator | pick list
 | A | Action taken | dropdown or free text |  | system admin
 |  | AW only - Reason for Protesting Non-Compliance |  |  | free text
 | A | who completed the action (audit trail) | audit trail |  | audit trail
 | A | date of action | validated time field, must be > start and < now |  | validated time field, must be > start and < now
Event Sign off | J | Study Impact (only for studies where PI/CS oversees investigation) | dropdown/free text |  | system admin
 | J | Does this event adversely affect the quality or integrity of the study? | Y/N radio |  | hardcoded y/n
 |  | Is this an Unanticipated Adverse Animal Related Event (IACUC Reporting)? |  |  | hardcoded y/n with yes giving a free text box
 |  | AW Only - IACUC classification |  |  | major /minor hardcode
 |  | AW Only - IACUC Response |  |  | free text