PO Awards by Status

SELECT PHA.CLM_DOCUMENT_NUMBER, PHA.clm_issuing_office, 
           PHA.AUTHORIZATION_STATUS, PHA.APPROVED_DATE, 
           PHA.TYPE_LOOKUP_CODE, PHA.ORG_ID,
            PHA.CLM_COTR_OFFICE, PHA.CLM_SUPPLIER_NAME, 
            PHA.AGENT_ID DOCUMENT_BUYER_AGENT_ID,
            PHA.VENDOR_ID, 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_PARTIAL_FUNDING_PKG.GET_FUNDED_VALUE('PO', PHA.PO_HEADER_ID) TOTAL_FUNDED_AMOUNT,
            PO_DOCUMENT_TOTALS_PVT.getAmountBilledForHeader(PHA.PO_HEADER_ID, 'TRANSACTION' , NULL) TOTAL_BILLED_AMOUNT,
            PO_DOCUMENT_TOTALS_PVT.getAmountDeliveredForHeader(PHA.PO_HEADER_ID, 'TRANSACTION' , NULL) TOTAL_RECEIVED_AMOUNT,
            (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 = nvl(PHA.AUTHORIZATION_STATUS,'INCOMPLETE')) 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_num_where 
            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'
            order by pha.clm_issuing_office, pha.clm_document_number