PO Closeout Contract Completion Statement

➜ Back to Library
Description
Categories: BI Publisher
Application: Purchasing
Source:
Short Name: POCLOSEOUTCCS
DB package: PO_DataTemplate_Pkg
Run PO Closeout Contract Completion Statement and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT PO_DATATEMPLATE_PKG.get_addressxml(PHA.po_header_id) ADDRESS,
                      DECODE(PHA.TYPE_LOOKUP_CODE, 'STANDARD', DECODE(PHA.CLM_AWARD_TYPE, 'BPA_CALL', nvl(PHA.CLM_EXTERNAL_IDV, (select CLM_DOCUMENT_NUMBER from PO_HEADERS_ALL where po_header_id = PHA.CLM_SOURCE_DOCUMENT_ID)),
                                                                                          'DELIVERY_ORD' , nvl(PHA.CLM_EXTERNAL_IDV, (select CLM_DOCUMENT_NUMBER from PO_HEADERS_ALL where po_header_id = PHA.CLM_SOURCE_DOCUMENT_ID)),
                                                                                          PHA.CLM_DOCUMENT_NUMBER),
                                                   PHA.CLM_DOCUMENT_NUMBER) PII_NUMBER,
                      (SELECT modification_number FROM po_drafts WHERE draft_id = (
                             SELECT Max(DRAFT_ID)
                               FROM po_drafts dft
                              WHERE dft.document_id = PHA.po_header_id
                                AND dft.draft_type= 'MOD')) LAST_MOD_NUMBER,
                      DECODE(PHA.CLM_AWARD_TYPE, 'BPA_CALL', PHA.CLM_DOCUMENT_NUMBER,
                                                 'DELIVERY_ORD' , PHA.CLM_DOCUMENT_NUMBER)  ORDER_NUMBER,
                      (SELECT vendor_name FROM po_vendors PV WHERE pv.vendor_id = PHA.VENDOR_ID ) VENDOR_NAME,
                      PO_DATATEMPLATE_PKG.get_vendor_address_details(PHA.UDA_TEMPLATE_ID, PHA.po_header_id, -1) VENDOR_ADDRESS,
                      DECODE(PCD.ACO_SIGNATURE_TYPE, 'SELF',
                              (SELECT full_name FROM PER_ALL_PEOPLE_F where person_id = PCD.ACO_SIGNER_ID AND sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE),
                              PCD.ACO_SIGNER_NAME) ACO_SIGNER_NAME,
                      DECODE(PCD.PCO_SIGNATURE_TYPE, 'SELF',
		                                    (SELECT full_name FROM PER_ALL_PEOPLE_F where person_id = PCD.PCO_SIGNER_ID AND sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE),
                              PCD.PCO_SIGNER_NAME) PCO_SIGNER_NAME,
                      DECODE(ACO_SIGNED_DATE, null,'','//' || fnd_message.get_string('PO', 'PO_KO_SIGN_MSG') || '/'  || 
		      DECODE(PCD.ACO_SIGNATURE_TYPE, 'SELF',
		            (SELECT full_name FROM PER_ALL_PEOPLE_F where person_id = PCD.ACO_SIGNER_ID AND sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE),
		            PCD.ACO_SIGNER_NAME)) ACO_SIGNATURE,
		      DECODE(PCO_SIGNED_DATE, null,'','//' || fnd_message.get_string('PO', 'PO_KO_SIGN_MSG') || '/'  || 
		      DECODE(PCD.PCO_SIGNATURE_TYPE, 'SELF',
		            (SELECT full_name FROM PER_ALL_PEOPLE_F where person_id = PCD.PCO_SIGNER_ID AND sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE),
                            PCD.PCO_SIGNER_NAME)) PCO_SIGNATURE,
                      PCD.ACO_SIGNED_DATE,
                      PCD.PCO_SIGNED_DATE,
                      PCD.ACO_REMARKS,
                      PCD.PCO_REMARKS,
                      PCD.PCO_CONCURS_DATE_YN,
                      PO_DATATEMPLATE_PKG.get_closeout_uda_details(PHA.po_header_id, PHA.UDA_TEMPLATE_ID).getClobVal() UDA_ATTRIBUTES
       FROM PO_HEADERS_ALL PHA, PO_CLOSEOUT_DETAILS PCD
      WHERE PHA.po_header_id = :headerId
        AND PCD.REFERENCE_DOC_ID =  PHA.po_header_id
Download
Related Blitz Reports
PO CCR Compliance PO View Award Changes Data Definition PO FPDS Compliance PO RFQ Action Required PO Vendor Service Performance Analysis PO Purchasing Activity Register ECC Project Manufacturing, Procurement Activities, SQL3 ECC Project Manufacturing, Procurement Activities, SQL1 ECC Project Manufacturing, Procurement Activities, SQL4
Blitz Report™

Blitz Report™ provides multiple benefits: