Pathway Activity Activity Type Description of the Copy Warehouse Type Description pathway activity from REF_ACTIVITY_TYPE Pathway Activity Activity Name for the pathway Copy Warehouse Name activity ACTIVITY_NAME from ICIS_ACTIVITY Pathway Activity Activity unique identifier for the Warehouse UID pathway activity Pathway Activity Warehouse Source For violations with multiple Get the minumun Warehouse HPV-Day-Zero dates, this holds HPV_Day_Zero_Date the earliest date. from the table for every Case File. Other Air_LCON_Code Geo data is added to the Copy data from Warehouse State_Code ICIS_ACTIVITY for Region_Code table from the each Case File Violation ICIS_ACTIVITY table to be where the Facility was used for filtering sensitive data advised. based on the Sensitive_Data_Flag Air_LCON_Code Geo data is added to the Copy data from Warehouse State_Code AIR_CASE_FILE_VIOLATION ICIS_ACTIVITY for Region_Code table from the ICIS_ACTIVITY each Case File Violation table to be used for filtering where the Facility was sensitive data based on the advised. Sensitive_Data_Flag Air_LCON_Code Geo data is added to the Copy data from Warehouse State_Code ICIS_ACTIVITY for Region_Code table from the each Case File Violation ICIS_ACTIVITY table to be where the Facility was used for filtering sensitive data advised. based on the Sensitive_Data_Flag Determination_Date This will be either Warehouse Comp_Determination_Date, or if null, the earliest FRV that’s determination date from the violations table. This is the Copy Warehouse Pathway_Activity_Date from Pathway_Activity_Date AIR_CASE_FILE_PATHWAY from where Pathway_Category_Code = DSCV where Pathway_Category_Code = DSCV This is the Copy Warehouse Pathway_Activity_Date from Pathway_Activity_Date AIR_CASE_FILE_PATHWAY from where Pathway_Category_Code =NTFC where Pathway_Category_Code = NTFC
Pathway Activity Type Description Activity Type Description of the pathway activity Copy from REF_ACTIVITY_TYPE Warehouse Pathway Activity Name Activity Name for the pathway activity Copy ACTIVITY_NAME from ICIS_ACTIVITY Warehouse Pathway Activity UID Activity unique identifier for the pathway activity Warehouse Pathway Activity Source Warehouse For violations with multiple HPV-Day-Zero dates, this holds the earliest date. Get the minumun HPV_Day_Zero_Date from the table for every Case File. Warehouse Other Air_LCON_Code State_Code Region_Code Geo data is added to the table from the ICIS_ACTIVITY table to be used for filtering sensitive data based on the Sensitive_Data_Flag Copy data from ICIS_ACTIVITY for each Case File Violation where the Facility was advised. Warehouse Air_LCON_Code State_Code Region_Code Geo data is added to the AIR_CASE_FILE_VIOLATION table from the ICIS_ACTIVITY table to be used for filtering sensitive data based on the Sensitive_Data_Flag Copy data from ICIS_ACTIVITY for each Case File Violation where the Facility was advised. Warehouse Air_LCON_Code State_Code Region_Code Geo data is added to the table from the ICIS_ACTIVITY table to be used for filtering sensitive data based on the Sensitive_Data_Flag Copy data from ICIS_ACTIVITY for each Case File Violation where the Facility was advised. Warehouse Determination_Date This will be either Comp_Determination_Date, or if that’s null, the earliest FRV determination date from the violations table. Warehouse This is the Pathway_Activity_Date from AIR_CASE_FILE_PATHWAY where Pathway_Category_Code = DSCV Copy Pathway_Activity_Date from where Pathway_Category_Code = DSCV Warehouse This is the Pathway_Activity_Date from AIR_CASE_FILE_PATHWAY where Pathway_Category_Code =NTFC Copy Pathway_Activity_Date from where Pathway_Category_Code = NTFC Warehouse
ICIS-Air_Data_Warehouse_Detailed_Design_Document_v1_4.docx