SELECT PHA.CLM_ISSUING_OFFICE ISSUE_OFFICE,
PHA.CLM_DOCUMENT_NUMBER,
PHA.AUTHORIZATION_STATUS,
PHA.APPROVED_DATE,
PHA.TYPE_LOOKUP_CODE,
PHA.ORG_ID,
PHA.CLM_COTR_OFFICE,
PHA.AGENT_ID DOCUMENT_BUYER_AGENT_ID,
PHA.VENDOR_ID,
PHA.CREATION_DATE,
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_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, -1, NULL, NULL, NULL, NULL,
'SUPPLIER_DTLS', NULL, 'CCR_EXP_REASON', 'DISPLAY_VALUE')CCR_EXP_REASON,
(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 = PHA.AUTHORIZATION_STATUS) 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_date_where
AND PDSH.STYLE_ID = PHA.STYLE_ID
AND PDSH.CLM_FLAG = 'Y'
AND (nvl(PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, -1, NULL, NULL, NULL, NULL,
'SUPPLIER_DTLS', NULL, 'CCR_REG_STATUS', 'INTERNAL_VALUE'), 'Unknown') <> 'Active')
AND PDS.STYLE_ID = PHA.STYLE_ID
AND PDS.document_subtype = PHA.TYPE_LOOKUP_CODE
UNION ALL
SELECT PHA.CLM_MOD_ISSUING_OFFICE ISSUE_OFFICE,
PD.MODIFICATION_NUMBER CLM_DOCUMENT_NUMBER,
PHA.AUTHORIZATION_STATUS,
decode(pd.status, 'COMPLETED', PHA.APPROVED_DATE, null) APPROVED_DATE,
PHA.TYPE_LOOKUP_CODE,
PHA.ORG_ID,
PHA.CLM_COTR_OFFICE,
PHA.AGENT_ID DOCUMENT_BUYER_AGENT_ID,
PHA.VENDOR_ID,
PHA.CREATION_DATE,
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 = PD.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, PHA.DRAFT_ID, NULL, NULL, NULL, NULL,
'addresses', NULL, 'addresscode', 'MOD_ISSUING_OFFICE', 'DISPLAY_VALUE')ISSUE_OFFICE_CODE,
PO_UDA_PUB.GET_ADDRESS_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, PHA.DRAFT_ID, NULL, NULL, NULL, NULL,
'addresses', NULL, 'location', 'MOD_ISSUING_OFFICE', 'DISPLAY_VALUE')ISSUE_OFFICE_NAME,
PO_DOCUMENT_TOTALS_PVT.getAmountOrdered('HEADER',PHA.PO_HEADER_ID, 'TRANSACTION' , NULL, PHA.DRAFT_ID) TOTAL_AMOUNT,
PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, PHA.DRAFT_ID, NULL, NULL, NULL, NULL,
'SUPPLIER_DTLS', NULL, 'CCR_EXP_REASON', 'DISPLAY_VALUE')CCR_EXP_REASON,
(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 = 'DRAFT_STATUS'
AND flv2.LOOKUP_CODE = PD.STATUS) DOCUMENT_STATUS_DESC
FROM PO_HEADERS_DRAFT_ALL PHA, PO_HEADERS PH,
po_doc_style_lines_vl PDS, PO_DRAFTS PD, PO_DOC_STYLE_HEADERS pdsh
WHERE &doc_mod_iss_off_where
AND PHA.PO_HEADER_ID = PH.PO_HEADER_ID
AND PD.DRAFT_ID = PHA.DRAFT_ID
AND PD.DRAFT_TYPE = 'MOD'
AND PD.DOCUMENT_ID = PHA.PO_HEADER_ID
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'
AND (nvl(PO_UDA_PUB.GET_SINGLE_ATTR_VALUE(PHA.UDA_TEMPLATE_ID, NULL, PHA.PO_HEADER_ID, PHA.DRAFT_ID, NULL, NULL, NULL, NULL,
'SUPPLIER_DTLS', NULL, 'CCR_REG_STATUS', 'INTERNAL_VALUE'), 'Unknown') <> 'Active')
ORDER BY 1,2 |