Post date: Feb 11, 2020 3:51:32 PM
SELECT PJUM.ORG_ID
, AIA.INVOICE_ID
, TO_CHAR(SYSDATE, :P_FORMAT_DATE) AS PJUM_DATE
, AIA.ATTRIBUTE6 AS PJUM_NO
, XXPPAAP_PJUM_PKG.GET_REQUESTOR_NAME(PHA.ATTRIBUTE1) AS PJUM_REQUESTOR
, APS.VENDOR_NAME AS VENDOR_NAME
, BANK_EXT.BANK_ACCOUNT_NUM AS BANK_ACCOUNT_NUM
, BANK_EXT.BANK_ACCOUNT_NAME AS BANK_ACCOUNT_NAME
, HP_BANK.PARTY_NAME AS BANK_NAME
, HP_BANKBRANCH.PARTY_NAME AS BANK_BRANCH_NAME
, PHA.SEGMENT1 AS PO_NUM
, PHA.ATTRIBUTE2 AS PO_MEMO
, PHA.COMMENTS AS PO_DESC
, PHA.CURRENCY_CODE AS PO_CURR
, XXPPAAP_PJUM_PKG.GET_PUM_AMOUNT(PJUM.INV_PREPAY_ID, PJUM.ORG_ID) AS PUM_AMOUNT
, AILA.DESCRIPTION AS INV
, XXPPAAP_PJUM_PKG.GET_PJUM_INVOICE_AMOUNT(AIA.INVOICE_ID, AILA.LINE_NUMBER, AIA.ORG_ID) AS PJUM_INV_AMOUNT
, XXPPAAP_PJUM_PKG.GET_PJUM_DPP_AMOUNT(AIA.INVOICE_ID, AILA.LINE_NUMBER, AIA.ORG_ID) AS PJUM_INV_DPP
, XXPPAAP_PJUM_PKG.GET_PJUM_PPN_AMOUNT(AIA.INVOICE_ID, AILA.LINE_NUMBER, AIA.ORG_ID) AS PJUM_INV_PPN
, XXPPAAP_PJUM_PKG.GET_PJUM_PPN_PUNGUT_AMOUNT(AIA.INVOICE_ID, AILA.LINE_NUMBER, AIA.ORG_ID) AS PJUM_INV_PPN_PUNGUT
, XXPPAAP_PJUM_PKG.GET_PJUM_PPH_AMOUNT(AIA.INVOICE_ID, AILA.LINE_NUMBER, AIA.ORG_ID) AS PJUM_INV_PPH
, XXPPAAP_PJUM_PKG.GET_INVOIVE_TOTAL_AMOUNT(AIA.INVOICE_ID, AILA.LINE_NUMBER, AIA.ORG_ID) AS PJUM_INV_TOTAL
FROM XXPPAAP_PJUM_V PJUM
, AP_INVOICES_ALL AIA
, AP_SUPPLIERS APS
, IBY_EXT_BANK_ACCOUNTS BANK_EXT
, HZ_PARTIES HP_BANK
, HZ_PARTIES HP_BANKBRANCH
, PO_HEADERS_ALL PHA
, AP_INVOICE_LINES_ALL AILA
, AP_INVOICE_DISTRIBUTIONS_ALL AIDA
, PO_DISTRIBUTIONS_ALL PDA
WHERE 1=1
AND PJUM.INV_ID = AIA.INVOICE_ID
AND AIA.INVOICE_ID = NVL(:P_INVOICE_ID, AIA.INVOICE_ID)
AND AIA.ORG_ID = :P_ORG_ID
AND NVL(AIA.ATTRIBUTE6, 'XXXXXXXXXX') = NVL(:P_PJUM_NO, NVL(AIA.ATTRIBUTE6, 'XXXXXXXXXX'))
AND AIA.VENDOR_ID = APS.VENDOR_ID
AND AIA.EXTERNAL_BANK_ACCOUNT_ID = BANK_EXT.EXT_BANK_ACCOUNT_ID (+)
AND BANK_EXT.BANK_ID = HP_BANK.PARTY_ID (+)
AND BANK_EXT.BRANCH_ID = HP_BANKBRANCH.PARTY_ID (+)
-- AND PJUM.INV_PREPAY_NUM = PHA.SEGMENT1
AND PJUM.ORG_ID = PHA.ORG_ID
AND AIA.INVOICE_ID = AILA.INVOICE_ID
AND AIA.ORG_ID = AILA.ORG_ID
AND UPPER(AILA.LINE_TYPE_LOOKUP_CODE) IN ('ITEM')
AND AILA.INVOICE_ID = AIDA.INVOICE_ID
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND AILA.ORG_ID = AIDA.ORG_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE IN ('ACCRUAL')
AND 1=1
AND PDA.PO_DISTRIBUTION_ID=AIDA.PO_DISTRIBUTION_ID
AND PHA.PO_HEADER_ID = PDA.PO_HEADER_ID
-- GROUP BY