PO CCR Compliance

SELECT PHA.CLM_ISSUING_OFFICE ISSUE_OFFICE, 
                PHA.CLM_DOCUMENT_NUMBER, 
                PHA.AUTHORIZATION_STATUS, 
                PHA.APPROVED_DATE, 
                PHA.TYPE_LOOKUP_CODE, 
                PHA.ORG_ID,
                PHA.CLM_COTR_OFFICE, 
            PHA.AGENT_ID DOCUMENT_BUYER_AGENT_ID,
            PHA.VENDOR_ID, 
            PHA.CREATION_DATE, 
            PDS.DISPLAY_NAME, 
            PHA.CURRENCY_CODE,
            (Select PVN.VENDOR_NAME FROM PO_VENDORS PVN WHERE PVN.VENDOR_ID = PHA.VENDOR_ID)VENDOR_NAME,
            (SELECT PPF.FULL_NAME FROM PER_PEOPLE_F PPF WHERE PPF.PERSON_ID = PHA.CLM_CONTRACT_OFFICER
             AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) CONTRACT_OFC_CONTACT,
            PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, -1, NULL, NULL, NULL, NULL,
            'addresses', NULL, 'addresscode', 'ISSUING_OFFICE', 'DISPLAY_VALUE')ISSUE_OFFICE_CODE,
            PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, -1, NULL, NULL, NULL, NULL,
            'addresses', NULL, 'location', 'ISSUING_OFFICE', 'DISPLAY_VALUE')ISSUE_OFFICE_NAME,
            PO_DOCUMENT_TOTALS_PVT.getAmountOrdered('HEADER',PHA.PO_HEADER_ID, 'TRANSACTION' , NULL, -1) TOTAL_AMOUNT,
            PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, -1, NULL, NULL, NULL, NULL,
            'SUPPLIER_DTLS', NULL, 'CCR_EXP_REASON', 'DISPLAY_VALUE')CCR_EXP_REASON,
            (Select flv1.displayed_field FROM po_lookup_codes flv1 WHERE flv1.lookup_type = 'POXMUB_DOCUMENT_TYPE' 
            AND flv1.LOOKUP_CODE = PHA.TYPE_LOOKUP_CODE) DOCUMENT_TYPE_DESC,
            (Select flv2.displayed_field FROM po_lookup_codes flv2 WHERE flv2.lookup_type = 'AUTHORIZATION STATUS'
            AND flv2.LOOKUP_CODE = PHA.AUTHORIZATION_STATUS) DOCUMENT_STATUS_DESC
            FROM PO_HEADERS PHA, 
            po_doc_style_lines_vl PDS,
            PO_DOC_STYLE_HEADERS pdsh
            WHERE &doc_iss_off_where
            AND &doc_date_where
            AND PDSH.STYLE_ID = PHA.STYLE_ID 
            AND PDSH.CLM_FLAG = 'Y'
            AND (nvl(PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, -1, NULL, NULL, NULL, NULL,
            'SUPPLIER_DTLS', NULL, 'CCR_REG_STATUS', 'INTERNAL_VALUE'), 'Unknown') <> 'Active')
            AND PDS.STYLE_ID = PHA.STYLE_ID 
            AND PDS.document_subtype = PHA.TYPE_LOOKUP_CODE
UNION ALL
SELECT      PHA.CLM_MOD_ISSUING_OFFICE ISSUE_OFFICE, 
            PD.MODIFICATION_NUMBER CLM_DOCUMENT_NUMBER, 
            PHA.AUTHORIZATION_STATUS, 
            decode(pd.status, 'COMPLETED', PHA.APPROVED_DATE, null) APPROVED_DATE,
            PHA.TYPE_LOOKUP_CODE, 
            PHA.ORG_ID,
            PHA.CLM_COTR_OFFICE, 
            PHA.AGENT_ID DOCUMENT_BUYER_AGENT_ID,
            PHA.VENDOR_ID, 
            PHA.CREATION_DATE, 
            PDS.DISPLAY_NAME,
            PHA.CURRENCY_CODE,
            (Select PVN.VENDOR_NAME FROM PO_VENDORS PVN WHERE PVN.VENDOR_ID = PHA.VENDOR_ID)VENDOR_NAME,
            (SELECT PPF.FULL_NAME FROM PER_PEOPLE_F PPF WHERE PPF.PERSON_ID = PD.CLM_CONTRACT_OFFICER
             AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE) CONTRACT_OFC_CONTACT,
            PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, PHA.DRAFT_ID, NULL, NULL, NULL, NULL,
            'addresses', NULL, 'addresscode', 'MOD_ISSUING_OFFICE', 'DISPLAY_VALUE')ISSUE_OFFICE_CODE,
            PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, PHA.DRAFT_ID, NULL, NULL, NULL, NULL,
            'addresses', NULL, 'location', 'MOD_ISSUING_OFFICE', 'DISPLAY_VALUE')ISSUE_OFFICE_NAME,
            PO_DOCUMENT_TOTALS_PVT.getAmountOrdered('HEADER',PHA.PO_HEADER_ID, 'TRANSACTION' , NULL, PHA.DRAFT_ID) TOTAL_AMOUNT,
            PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, PHA.DRAFT_ID, NULL, NULL, NULL, NULL,
            'SUPPLIER_DTLS', NULL, 'CCR_EXP_REASON', 'DISPLAY_VALUE')CCR_EXP_REASON,
            (Select flv1.displayed_field FROM po_lookup_codes flv1 WHERE flv1.lookup_type = 'POXMUB_DOCUMENT_TYPE' 
            AND flv1.LOOKUP_CODE = PHA.TYPE_LOOKUP_CODE) DOCUMENT_TYPE_DESC,
            (Select flv2.displayed_field FROM po_lookup_codes flv2 WHERE flv2.lookup_type = 'DRAFT_STATUS'
            AND flv2.LOOKUP_CODE = PD.STATUS) DOCUMENT_STATUS_DESC
            FROM PO_HEADERS_DRAFT_ALL PHA, PO_HEADERS PH,
            po_doc_style_lines_vl PDS, PO_DRAFTS PD, PO_DOC_STYLE_HEADERS pdsh
            WHERE &doc_mod_iss_off_where 
            AND PHA.PO_HEADER_ID = PH.PO_HEADER_ID
            AND PD.DRAFT_ID = PHA.DRAFT_ID 
			AND PD.DRAFT_TYPE = 'MOD'
            AND PD.DOCUMENT_ID = PHA.PO_HEADER_ID
            AND &doc_date_where 
            AND PDS.STYLE_ID = PHA.STYLE_ID 
            AND PDS.document_subtype = PHA.TYPE_LOOKUP_CODE
            AND PDSH.STYLE_ID = PHA.STYLE_ID 
            AND PDSH.CLM_FLAG = 'Y'
            AND (nvl(PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, PHA.DRAFT_ID, NULL, NULL, NULL, NULL,
            'SUPPLIER_DTLS', NULL, 'CCR_REG_STATUS', 'INTERNAL_VALUE'), 'Unknown') <> 'Active')
ORDER BY 1,2