PO Awards by COTR

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Awards by COTR
Short Name: POXAWDCOTR_XML
DB package: PO_POXAWDCOTR_XMLP_PKG
SELECT PHA.CLM_COTR_CONTACT, PHA.CLM_DOCUMENT_NUMBER, 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,
            (Select PVN.VENDOR_NAME FROM PO_VENDORS PVN WHERE PVN.VENDOR_ID = PHA.VENDOR_ID)VENDOR_NAME,
            PHA.VENDOR_ID, PDS.DISPLAY_NAME, PHA.CURRENCY_CODE,
            PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, -1, NULL, NULL, NULL, NULL,
            'addresses', NULL, 'contact','COTR_OFFICE','DISPLAY_VALUE') COTR_CONTACT,
            (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_DOCUMENT_TOTALS_PVT.getAmountOrdered('HEADER',PHA.PO_HEADER_ID, 'TRANSACTION' , NULL, -1) TOTAL_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 &cotr_contact_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_cotr_contact, pha.clm_document_number
Parameter Name SQL text Validation
Date To
 
Date
Date From
 
Date
COTR
 
LOV Oracle