SELECT DISTINCT FILL_NUMBER,PICKUP_DATE,
FACILITY_ID,RX_NUMBER,PICKUP_ID,PICKUP_ID_QUAL,CTT_LEGACY_NUMBER,JURISDICTION_ID,
RELATIONSHIP_CODE,PICKUP_PATIENT_ID_INDICATOR,RXC_FILL_SOLD_MARK_DATE,TIMEZONE_ID,
STATE_CODE,PRESCRIPTION_FILL_ID,PICKUP_LAST_NAME,PICKUP_FIRST_NAME,PICKUP_STREET_ADDRESS,
PICKUP_ZIPCODE,PICKUP_CITY,PICKUP_STATE,PICKUP_DATE_OF_BIRTH,PATIENT_ID_QUALIFIER_VALUE,PATIENT_QUALIFIER_TYPE,
PATIENT_ID_JURISDICTION_CODE,PATIENT_ID_EXP_DATE,RFUFN,LAST_NAME,PATIENT_LAST_NAME,PATIENT_FIRST_NAME,PPI_MESSAGE_ID,DRUG_DB_CODE_QUALIFIER,
DRUG_DB_CODE,PRESCRIBER_ORDER_NUM,PET_OWNER_LAST_NAME,PET_OWNER_FIRST_NAME,SUFFIX,ADDRESS_LINE2,SPECIES_CODE,PARTIAL_FILL_SEQUENCE_NUMBER,
Acquired_Id,facility_num,IS_GCN_SRD,DRUG_SCHEDULE,PRODUCT_NUM,RTFN,SIG,SUBOXONE_DEA,USER_CREDENTIALS,SRD_ATTRIBUTE_SQ_ID,TREATMENT_TYPE,EXEMPTION_CODE,
MME,DIAGNOSIS_CODE_QUALIFIER,DIAGNOSIS_CODE,CREATED_DATE,INTK_MESSAGE_ID FROM 
(SELECT DISTINCT RXP_PRESCRIPTION_FILL.FILL_NUMBER,
                RXP_PRESCRIPTION_FILL.PICKUP_DATE,
			    RXP_PRESCRIPTION_FILL.FACILITY_ID,
                RXP_PRESCRIPTION_FILL.RX_NUMBER,
                SRD_ATTRIBUTE_VALUE.PICKUP_ID,
                SRD_ATTRIBUTE_VALUE.PICKUP_ID_QUAL,
                RXP_PRESCRIPTION_FILL.CTT_LEGACY_NUMBER,
                SRD_ATTRIBUTE_VALUE.JURISDICTION_ID,
                SRD_ATTRIBUTE_VALUE.RELATIONSHIP_CODE,
                SRD_ATTRIBUTE_VALUE.PICKUP_PATIENT_ID_INDICATOR,
                RXP_PRESCRIPTION_FILL.RXC_FILL_SOLD_MARK_DATE,
                FACILITY.TIMEZONE_ID,
                (SELECT ZIP.STATE_CODE
                   FROM ADDRESS ADS, ADDRESS_LINK ADL, ZIPCODE ZIP
                  WHERE ADL.ADDRESS_NUM = ADS.ADDRESS_NUM
                    AND ADL.OWNER_TYPE_CODE = 'C'
                    AND ADS.CSZ_NUM = ZIP.CSZ_NUM
                    AND ADL.OWNER = FACILITY.FACILITY_NUM
                    AND ADL.USAGE = 8
                    AND ROWNUM < 2) STATE_CODE,
                SRD_ATTRIBUTE_VALUE.PRESCRIPTION_FILL_ID,
                SRD_ATTRIBUTE_VALUE.PICKUP_LAST_NAME,
                SRD_ATTRIBUTE_VALUE.PICKUP_FIRST_NAME,
                SRD_ATTRIBUTE_VALUE.PICKUP_STREET_ADDRESS,
                SRD_ATTRIBUTE_VALUE.PICKUP_ZIPCODE,
                SRD_ATTRIBUTE_VALUE.PICKUP_CITY,
                SRD_ATTRIBUTE_VALUE.PICKUP_STATE,
                SRD_ATTRIBUTE_VALUE.PICKUP_DATE_OF_BIRTH,
                SRD_ATTRIBUTE_VALUE.PATIENT_ID_QUALIFIER_VALUE,
                SRD_ATTRIBUTE_VALUE.PATIENT_QUALIFIER_TYPE,
                SRD_ATTRIBUTE_VALUE.PATIENT_ID_JURISDICTION_CODE,
                SRD_ATTRIBUTE_VALUE.PATIENT_ID_EXP_DATE,
                RXP_FILL_USER.FIRST_NAME AS RFUFN,
                RXP_FILL_USER.LAST_NAME,
                SRD_ATTRIBUTE_VALUE.PATIENT_LAST_NAME,
                SRD_ATTRIBUTE_VALUE.PATIENT_FIRST_NAME,
                RXP_PRESCRIPTION.PPI_MESSAGE_ID,
                RXP_PRESCRIPTION.DRUG_DB_CODE_QUALIFIER,
                RXP_PRESCRIPTION.DRUG_DB_CODE,
                RXP_PRESCRIPTION.PRESCRIBER_ORDER_NUM,
                RXP_PATIENT.PET_OWNER_LAST_NAME,
                RXP_PATIENT.PET_OWNER_FIRST_NAME,
                RXP_PATIENT.SUFFIX,
                RXP_PATIENT_ADDRESS.ADDRESS_LINE2,
                RXP_PATIENT.SPECIES_CODE,
                RXP_PRESCRIPTION_FILL.PARTIAL_FILL_SEQUENCE_NUMBER,
                RXP_PRESCRIPTION.Acquired_Id,
                FACILITY.FACILITY_NUM,
                SRD_ATTRIBUTE_VALUE.IS_GCN_SRD,
                RXP_PRESCRIPTION_FILL.DRUG_SCHEDULE,
                RXP_PRESCRIPTION_FILL.PRODUCT_NUM,
                RXP_PATIENT.FIRST_NAME AS RTFN,
                RXP_PRESCRIPTION.SIG,
                RXP_PRESCRIPTION_FILL.SUBOXONE_DEA,
                RXP_FILL_USER.USER_CREDENTIALS,
                SRD_ATTRIBUTE_VALUE.SRD_ATTRIBUTE_SQ_ID,
                SRD_ATTRIBUTE_VALUE.TREATMENT_TYPE,
                SRD_ATTRIBUTE_VALUE.EXEMPTION_CODE,
                SRD_ATTRIBUTE_VALUE.MME,
                SRD_ATTRIBUTE_VALUE.DIAGNOSIS_CODE_QUALIFIER,
                SRD_ATTRIBUTE_VALUE.DIAGNOSIS_CODE,
                SRD_ATTRIBUTE_VALUE.CREATED_DATE,
                RXC_INTK_CROSS_REF.MESSAGE_ID AS INTK_MESSAGE_ID
  FROM RXP_PRESCRIPTION_FILL,
       FACILITY,
       RXP_FILL_USER,
       RXP_PRESCRIPTION,
       RXP_PATIENT, 
       RXP_PATIENT_ADDRESS,
SRD_ATTRIBUTE_VALUE,
RXC_INTK_CROSS_REF
 WHERE RXP_PRESCRIPTION_FILL.FILL_STATUS = 7  
 AND RXP_PRESCRIPTION_FILL.RXC_FILL_SOLD_MARK_DATE BETWEEN
       TO_DATE('20221218' || '230000', 'YYYYMMDDHH24MISS') AND
       TO_DATE('20221220' || '055959', 'YYYYMMDDHH24MISS')
   AND RXP_PRESCRIPTION_FILL.LAST_UPDATED_DATE BETWEEN
       TO_DATE('20221220' || '000000', 'YYYYMMDDHH24MISS') AND
       TO_DATE('20221220' || '055959', 'YYYYMMDDHH24MISS')
   AND RXP_PRESCRIPTION_FILL.FACILITY_ID = FACILITY.FACILITY_ID
   AND RXP_PRESCRIPTION_FILL.PRESCRIPTION_FILL_ID =
       RXP_FILL_USER.PRESCRIPTION_FILL_ID
   AND RXP_FILL_USER.ACTIVITY = 4
   AND RXP_PRESCRIPTION_FILL.PRESCRIPTION_ID =
       RXP_PRESCRIPTION.PRESCRIPTION_ID
   AND RXP_PRESCRIPTION_FILL.PATIENT_ID = RXP_PATIENT.PATIENT_ID
AND RXP_PRESCRIPTION.PATIENT_ADDRESS_ID = RXP_PATIENT_ADDRESS.PATIENT_ADDRESS_ID 
AND SRD_ATTRIBUTE_VALUE.PRESCRIPTION_FILL_ID(+) = RXP_PRESCRIPTION_FILL.PRESCRIPTION_FILL_ID
AND RXP_PRESCRIPTION.PPI_MESSAGE_ID =
       RXC_INTK_CROSS_REF.PPI_MESSAGE_NUM(+)) T1,
     (SELECT DISTINCT CV.VALUE AS VALUE,TEMP_C.DATA_GROUP
  FROM (SELECT CV.CONFIG_PROPERTY_NUM,
                 MIN (TEMP_A.DISTANCE_TO_PARENT) MIN_DISTANCE_TO_PARENT
            FROM CONFIG_VALUE CV,
                 (SELECT DGL.PARENT_DATA_GROUP DATA_GROUP,
                         DGL.DISTANCE_TO_PARENT
                    FROM data_group_link DGL
                   WHERE data_group IN (SELECT f.facility_num
                                         FROM facility f)) TEMP_A
           WHERE CV.DATA_GROUP = TEMP_A.DATA_GROUP
        GROUP BY CV.CONFIG_PROPERTY_NUM) TEMP_B,
       CONFIG_VALUE     CV,
       (SELECT DGL.PARENT_DATA_GROUP DATA_GROUP, DGL.DISTANCE_TO_PARENT
          FROM data_group_link DGL
         WHERE data_group IN (SELECT f.facility_num
                               FROM facility f)) TEMP_C,
                              CONFIG_PROPERTY  CP
WHERE     CV.CONFIG_PROPERTY_NUM = TEMP_B.CONFIG_PROPERTY_NUM
       AND CV.DATA_GROUP = TEMP_C.DATA_GROUP
       AND TEMP_C.DISTANCE_TO_PARENT = TEMP_B.MIN_DISTANCE_TO_PARENT
       AND CP.CONFIG_PROPERTY_NUM = CV.CONFIG_PROPERTY_NUM
       AND CP.NAME = 'InternalApp.EnableSRDDataModelChangeforGGLagImprovement') T2
    WHERE T1.FACILITY_NUM = T2.DATA_GROUP (+)
    AND NVL(T2.VALUE,'N')='Y'
/