PO Closeout Contract Completion Statement

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