PO Awards by Status
Description
Categories: BI Publisher
Application: Purchasing
Source: Awards by Status
Short Name: POXAWDSTA_XML
DB package: PO_POXAWDSTA_XMLP_PKG
Source: Awards by Status
Short Name: POXAWDSTA_XML
DB package: PO_POXAWDSTA_XMLP_PKG
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Issue Office |
|
LOV Oracle | |
Document Number From |
|
LOV Oracle | |
Document Number To |
|
LOV Oracle | |
Date From |
|
Date | |
Date To |
|
Date |