VIEW: APPS.PA_XLA_BC_PKT_AP_DETAIL_V

Source

View Text - Preformatted

SELECT Pbc.bc_event_id, Pbc.source_event_id, pbc.document_distribution_id, pbc.document_distribution_id, pbc.expenditure_type AID_EXPENDITURE_TYPE, Pbc.bc_packet_id, Pbc.budget_version_id, DECODE(pbc.document_Type,'AP',DECODE(pbc.document_distribution_type ,'PREPAY APPL' , Nvl(pbc.accounted_cr,0) - Nvl(pbc.accounted_dr,0) ,'PREPAY APPL NONREC TAX', Nvl(pbc.accounted_cr,0) - Nvl(pbc.accounted_dr,0) , NVL(pbc.accounted_dr,0) - Nvl(pbc.accounted_cr,0)) ,Nvl(pbc.accounted_cr,0) - Nvl(pbc.accounted_dr,0)) AID_BASE_AMT_NO_ROUND, DECODE(pbc.document_Type,'AP',DECODE(pbc.document_distribution_type ,'PREPAY APPL' , Nvl(pbc.entered_cr,0) - Nvl(pbc.entered_dr,0) ,'PREPAY APPL NONREC TAX', Nvl(pbc.entered_cr,0) - Nvl(pbc.entered_dr,0) , Nvl(pbc.entered_dr,0) - Nvl(pbc.entered_cr,0)) ,Nvl(pbc.entered_cr,0) - Nvl(pbc.entered_dr,0)) AID_AMOUNT, decode(pbc.document_type, 'PO', 'PA_PO_BURDEN', 'AP', 'PA_AP_BURDEN') DISTRIBUTION_LINK_TYPE, pbc.project_id AID_PROJECT_ID, pbc.task_id AID_TASK_ID, pbc.expenditure_organization_id AID_EXPENDITURE_ORG_ID, pbc.expenditure_item_Date AID_EXPENDITURE_ITEM_DATE, pbc.encumbrance_type_id, pbc.proj_encumbrance_type_id, gll.currency_code AI_INVOICE_CURRENCY_CODE, gll.currency_code, NULL, to_date(null), to_number(null), pbc.budget_ccid, pbc.document_type, pbc.document_header_id AI_INVOICE_ID, Prlm.event_type, Prlm.expenditure_category, Prlm.revenue_category, Prlm.person_id, Prlm.job_id, pbc.vendor_id, Pbc.packet_id, Pbc.parent_bc_packet_id, pbc.set_of_books_id, pbc.burden_cost_flag ,PA_FUNDS_CONTROL_UTILS.get_sla_notupgraded_flag (DECODE(document_type,'AP',200,'PO',201) ,DECODE(document_type ,'AP','AP_INVOICES' ,'PO',decode(pbc.document_distribution_type ,'SCHEDULED','RELEASE' ,'BLANKET','RELEASE' ,'PURCHASE_ORDER')) ,DECODE(document_type ,'AP',pbc.document_header_id ,'PO', decode(pbc.document_distribution_type ,'SCHEDULED',pbc.document_header_id_2 ,'BLANKET',pbc.document_header_id_2 ,pbc.document_header_id)) ,pbc.document_distribution_id ,DECODE(document_type,'AP','AP_INV_DIST' ,'PO','PO_DISTRIBUTIONS_ALL') ,Pbc.budget_version_id ,pbc.budget_line_id) Use_Enc_Upg_Attrib_Flag /* For AP, only non-budget credit JLT's are PO/REL relieving and PREPAY APPL. Hence for credit JLT,dr will hold budget details and cr will hold transaction details*/ ,DECODE(pbc.document_Type,'AP',DECODE(pbc.document_distribution_type ,'PREPAY APPL','PA_BUDGET_ENC' ,'PREPAY APPL NONREC TAX','PA_BUDGET_ENC' ,Decode(burden_method_code, 'S', 'AP_INV_PA_BURDENED', 'D', 'AP_INV_PA_BURDEN', NULL)) ,'PO','PA_BUDGET_ENC') Enc_Upg_Dr_Acct_Class ,DECODE(pbc.document_Type,'AP',DECODE(pbc.document_distribution_type ,'PREPAY APPL',Decode(burden_method_code, 'S', 'AP_INV_PA_BURDENED', 'D', 'AP_INV_PA_BURDEN', NULL) ,'PREPAY APPL NONREC TAX',Decode(burden_method_code, 'S', 'AP_INV_PA_BURDENED', 'D', 'AP_INV_PA_BURDEN', NULL) ,'PA_BUDGET_ENC' ) ,'PO',decode(burden_method_code, 'S', 'PO_PA_BURDENED', 'D', 'PO_PA_BURDEN', NULL)) Enc_Upg_Cr_Acct_Class ,pbc.budget_ccid Enc_Upg_Dr_Account ,pbc.budget_ccid Enc_Upg_Cr_Account ,DECODE(pbc.document_Type,'AP',DECODE(pbc.document_distribution_type ,'PREPAY APPL' ,pbc.proj_encumbrance_type_id ,'PREPAY APPL NONREC TAX' ,pbc.proj_encumbrance_type_id ,pbc.encumbrance_type_id) ,'PO',pbc.proj_encumbrance_type_id ) Enc_Upg_Dr_Enc_Type_Id ,DECODE(pbc.document_Type,'AP',DECODE(pbc.document_distribution_type ,'PREPAY APPL' , pbc.encumbrance_type_id ,'PREPAY APPL NONREC TAX' , pbc.encumbrance_type_id , pbc.proj_encumbrance_type_id ) ,'PO',pbc.encumbrance_type_id) Enc_Upg_Cr_Enc_Type_Id ,decode(document_type, 'PO', 201, NULL) ,decode(document_type, 'PO', 'PA_PO_BURDEN', NULL) ,decode(document_type, 'PO', decode(pbc.document_distribution_type ,'SCHEDULED','RELEASE' ,'BLANKET','RELEASE' ,'PURCHASE_ORDER') , NULL) ,decode(document_type, 'PO', pbc.document_distribution_id, NULL) ,decode(document_type, 'PO', pbc.expenditure_type, NULL) ,decode(document_type, 'PO', decode(pbc.document_distribution_type ,'SCHEDULED',pbc.document_header_id_2 ,'BLANKET',pbc.document_header_id_2 ,pbc.document_header_id) , NULL) ,decode(document_type, 'AP', 200, NULL) ,decode(document_type, 'AP', 'PA_AP_BURDEN', NULL) ,decode(document_type, 'AP', 'AP_INVOICES', NULL) ,decode(document_type, 'AP', DECODE(pbc.document_distribution_type ,'PREPAY APPL',TO_NUMBER(pbc.reference3) ,'PREPAY APPL NONREC TAX',TO_NUMBER(pbc.reference3) ,pbc.document_distribution_id) , NULL) ,decode(document_type, 'AP', pbc.expenditure_type, NULL) ,decode(document_type, 'AP', DECODE(pbc.document_distribution_type ,'PREPAY APPL',TO_NUMBER(pbc.reference2) ,'PREPAY APPL NONREC TAX',TO_NUMBER(pbc.reference2) ,pbc.document_header_id) , NULL) ,275 ,'PA_BUDGET_ENC' ,'BUDGETS' ,budget_line_id ,null ,budget_version_id ,200 ,DECODE(substr(pbc.document_distribution_type,1,11),'PREPAY APPL','AP_PREPAY','AP_INV_DIST') ,'AP_INVOICES' ,(DECODE(pbc.document_type,'PO',DECODE(pbc.reference1,'AP',to_number(pbc.reference2),pbc.document_header_id),pbc.document_header_id)) ,( SELECT DECODE(substr(pbc.document_distribution_type,1,11), 'PREPAY APPL',(SELECT PREPAY_APP_DIST_ID FROM ap_prepay_app_dists WHERE bc_event_id = apd.bc_event_id AND PREPAY_APP_DISTRIBUTION_ID = apd.invoice_distribution_id AND rownum = 1 ), COALESCE(apd.charge_applicable_to_dist_id,apd.related_id,apd.invoice_distribution_id)) FROM ap_invoice_distributions_all apd WHERE apd.invoice_distribution_id = DECODE(pbc.document_type,'PO' ,DECODE(pbc.reference1,'AP' ,to_number(pbc.reference3) ,pbc.document_distribution_id) ,pbc.document_distribution_id)) alloc_to_first_dist_id ,null ,decode(document_type, 'PO', decode(pbc.document_distribution_type ,'SCHEDULED',pbc.document_header_id_2 ,'BLANKET',pbc.document_header_id_2 ,NULL) , NULL) ,document_distribution_type ,txn_ccid AI_DIST_CCID ,DECODE(document_type,'AP',DECODE(PA_FUNDS_CONTROL_UTILS.get_ap_acct_reversal_attr(xev.event_type_code, pbc.document_distribution_id, pbc.document_distribution_type),NULL,'N','Y'),'N') acct_reversal_indicator ,DECODE(document_type,'AP',PA_FUNDS_CONTROL_UTILS.get_ap_acct_reversal_attr(xev.event_type_code, pbc.document_distribution_id, pbc.document_distribution_type),to_number(NULL)) acct_reversal_first_dist_id ,DECODE(document_type,'AP','PA_AP_BURDEN',NULL) acct_rev_distribution_type From Pa_Bc_Packets pbc, Xla_Events_Gt xev, PA_resource_list_members prlm, GL_Ledgers gll WHERE xev.event_id = pbc.bc_event_id And pbc.document_type in ('PO', 'AP') And pbc.resource_list_member_id = prlm.resource_list_member_id And pbc.burden_cost_flag in ('N','O') And pbc.set_of_books_id = gll.ledger_id And pbc.status_code = 'I'

View Text - HTML Formatted

SELECT PBC.BC_EVENT_ID
, PBC.SOURCE_EVENT_ID
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.EXPENDITURE_TYPE AID_EXPENDITURE_TYPE
, PBC.BC_PACKET_ID
, PBC.BUDGET_VERSION_ID
, DECODE(PBC.DOCUMENT_TYPE
, 'AP'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'PREPAY APPL'
, NVL(PBC.ACCOUNTED_CR
, 0) - NVL(PBC.ACCOUNTED_DR
, 0)
, 'PREPAY APPL NONREC TAX'
, NVL(PBC.ACCOUNTED_CR
, 0) - NVL(PBC.ACCOUNTED_DR
, 0)
, NVL(PBC.ACCOUNTED_DR
, 0) - NVL(PBC.ACCOUNTED_CR
, 0))
, NVL(PBC.ACCOUNTED_CR
, 0) - NVL(PBC.ACCOUNTED_DR
, 0)) AID_BASE_AMT_NO_ROUND
, DECODE(PBC.DOCUMENT_TYPE
, 'AP'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'PREPAY APPL'
, NVL(PBC.ENTERED_CR
, 0) - NVL(PBC.ENTERED_DR
, 0)
, 'PREPAY APPL NONREC TAX'
, NVL(PBC.ENTERED_CR
, 0) - NVL(PBC.ENTERED_DR
, 0)
, NVL(PBC.ENTERED_DR
, 0) - NVL(PBC.ENTERED_CR
, 0))
, NVL(PBC.ENTERED_CR
, 0) - NVL(PBC.ENTERED_DR
, 0)) AID_AMOUNT
, DECODE(PBC.DOCUMENT_TYPE
, 'PO'
, 'PA_PO_BURDEN'
, 'AP'
, 'PA_AP_BURDEN') DISTRIBUTION_LINK_TYPE
, PBC.PROJECT_ID AID_PROJECT_ID
, PBC.TASK_ID AID_TASK_ID
, PBC.EXPENDITURE_ORGANIZATION_ID AID_EXPENDITURE_ORG_ID
, PBC.EXPENDITURE_ITEM_DATE AID_EXPENDITURE_ITEM_DATE
, PBC.ENCUMBRANCE_TYPE_ID
, PBC.PROJ_ENCUMBRANCE_TYPE_ID
, GLL.CURRENCY_CODE AI_INVOICE_CURRENCY_CODE
, GLL.CURRENCY_CODE
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, PBC.BUDGET_CCID
, PBC.DOCUMENT_TYPE
, PBC.DOCUMENT_HEADER_ID AI_INVOICE_ID
, PRLM.EVENT_TYPE
, PRLM.EXPENDITURE_CATEGORY
, PRLM.REVENUE_CATEGORY
, PRLM.PERSON_ID
, PRLM.JOB_ID
, PBC.VENDOR_ID
, PBC.PACKET_ID
, PBC.PARENT_BC_PACKET_ID
, PBC.SET_OF_BOOKS_ID
, PBC.BURDEN_COST_FLAG
, PA_FUNDS_CONTROL_UTILS.GET_SLA_NOTUPGRADED_FLAG (DECODE(DOCUMENT_TYPE
, 'AP'
, 200
, 'PO'
, 201)
, DECODE(DOCUMENT_TYPE
, 'AP'
, 'AP_INVOICES'
, 'PO'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'SCHEDULED'
, 'RELEASE'
, 'BLANKET'
, 'RELEASE'
, 'PURCHASE_ORDER'))
, DECODE(DOCUMENT_TYPE
, 'AP'
, PBC.DOCUMENT_HEADER_ID
, 'PO'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'SCHEDULED'
, PBC.DOCUMENT_HEADER_ID_2
, 'BLANKET'
, PBC.DOCUMENT_HEADER_ID_2
, PBC.DOCUMENT_HEADER_ID))
, PBC.DOCUMENT_DISTRIBUTION_ID
, DECODE(DOCUMENT_TYPE
, 'AP'
, 'AP_INV_DIST'
, 'PO'
, 'PO_DISTRIBUTIONS_ALL')
, PBC.BUDGET_VERSION_ID
, PBC.BUDGET_LINE_ID) USE_ENC_UPG_ATTRIB_FLAG /* FOR AP
, ONLY NON-BUDGET CREDIT JLT'S ARE PO/REL RELIEVING
AND PREPAY APPL. HENCE FOR CREDIT JLT
, DR WILL HOLD BUDGET DETAILS
AND CR WILL HOLD TRANSACTION DETAILS*/
, DECODE(PBC.DOCUMENT_TYPE
, 'AP'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'PREPAY APPL'
, 'PA_BUDGET_ENC'
, 'PREPAY APPL NONREC TAX'
, 'PA_BUDGET_ENC'
, DECODE(BURDEN_METHOD_CODE
, 'S'
, 'AP_INV_PA_BURDENED'
, 'D'
, 'AP_INV_PA_BURDEN'
, NULL))
, 'PO'
, 'PA_BUDGET_ENC') ENC_UPG_DR_ACCT_CLASS
, DECODE(PBC.DOCUMENT_TYPE
, 'AP'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'PREPAY APPL'
, DECODE(BURDEN_METHOD_CODE
, 'S'
, 'AP_INV_PA_BURDENED'
, 'D'
, 'AP_INV_PA_BURDEN'
, NULL)
, 'PREPAY APPL NONREC TAX'
, DECODE(BURDEN_METHOD_CODE
, 'S'
, 'AP_INV_PA_BURDENED'
, 'D'
, 'AP_INV_PA_BURDEN'
, NULL)
, 'PA_BUDGET_ENC' )
, 'PO'
, DECODE(BURDEN_METHOD_CODE
, 'S'
, 'PO_PA_BURDENED'
, 'D'
, 'PO_PA_BURDEN'
, NULL)) ENC_UPG_CR_ACCT_CLASS
, PBC.BUDGET_CCID ENC_UPG_DR_ACCOUNT
, PBC.BUDGET_CCID ENC_UPG_CR_ACCOUNT
, DECODE(PBC.DOCUMENT_TYPE
, 'AP'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'PREPAY APPL'
, PBC.PROJ_ENCUMBRANCE_TYPE_ID
, 'PREPAY APPL NONREC TAX'
, PBC.PROJ_ENCUMBRANCE_TYPE_ID
, PBC.ENCUMBRANCE_TYPE_ID)
, 'PO'
, PBC.PROJ_ENCUMBRANCE_TYPE_ID ) ENC_UPG_DR_ENC_TYPE_ID
, DECODE(PBC.DOCUMENT_TYPE
, 'AP'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'PREPAY APPL'
, PBC.ENCUMBRANCE_TYPE_ID
, 'PREPAY APPL NONREC TAX'
, PBC.ENCUMBRANCE_TYPE_ID
, PBC.PROJ_ENCUMBRANCE_TYPE_ID )
, 'PO'
, PBC.ENCUMBRANCE_TYPE_ID) ENC_UPG_CR_ENC_TYPE_ID
, DECODE(DOCUMENT_TYPE
, 'PO'
, 201
, NULL)
, DECODE(DOCUMENT_TYPE
, 'PO'
, 'PA_PO_BURDEN'
, NULL)
, DECODE(DOCUMENT_TYPE
, 'PO'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'SCHEDULED'
, 'RELEASE'
, 'BLANKET'
, 'RELEASE'
, 'PURCHASE_ORDER')
, NULL)
, DECODE(DOCUMENT_TYPE
, 'PO'
, PBC.DOCUMENT_DISTRIBUTION_ID
, NULL)
, DECODE(DOCUMENT_TYPE
, 'PO'
, PBC.EXPENDITURE_TYPE
, NULL)
, DECODE(DOCUMENT_TYPE
, 'PO'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'SCHEDULED'
, PBC.DOCUMENT_HEADER_ID_2
, 'BLANKET'
, PBC.DOCUMENT_HEADER_ID_2
, PBC.DOCUMENT_HEADER_ID)
, NULL)
, DECODE(DOCUMENT_TYPE
, 'AP'
, 200
, NULL)
, DECODE(DOCUMENT_TYPE
, 'AP'
, 'PA_AP_BURDEN'
, NULL)
, DECODE(DOCUMENT_TYPE
, 'AP'
, 'AP_INVOICES'
, NULL)
, DECODE(DOCUMENT_TYPE
, 'AP'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'PREPAY APPL'
, TO_NUMBER(PBC.REFERENCE3)
, 'PREPAY APPL NONREC TAX'
, TO_NUMBER(PBC.REFERENCE3)
, PBC.DOCUMENT_DISTRIBUTION_ID)
, NULL)
, DECODE(DOCUMENT_TYPE
, 'AP'
, PBC.EXPENDITURE_TYPE
, NULL)
, DECODE(DOCUMENT_TYPE
, 'AP'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'PREPAY APPL'
, TO_NUMBER(PBC.REFERENCE2)
, 'PREPAY APPL NONREC TAX'
, TO_NUMBER(PBC.REFERENCE2)
, PBC.DOCUMENT_HEADER_ID)
, NULL)
, 275
, 'PA_BUDGET_ENC'
, 'BUDGETS'
, BUDGET_LINE_ID
, NULL
, BUDGET_VERSION_ID
, 200
, DECODE(SUBSTR(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 1
, 11)
, 'PREPAY APPL'
, 'AP_PREPAY'
, 'AP_INV_DIST')
, 'AP_INVOICES'
, (DECODE(PBC.DOCUMENT_TYPE
, 'PO'
, DECODE(PBC.REFERENCE1
, 'AP'
, TO_NUMBER(PBC.REFERENCE2)
, PBC.DOCUMENT_HEADER_ID)
, PBC.DOCUMENT_HEADER_ID))
, ( SELECT DECODE(SUBSTR(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 1
, 11)
, 'PREPAY APPL'
, (SELECT PREPAY_APP_DIST_ID
FROM AP_PREPAY_APP_DISTS
WHERE BC_EVENT_ID = APD.BC_EVENT_ID
AND PREPAY_APP_DISTRIBUTION_ID = APD.INVOICE_DISTRIBUTION_ID
AND ROWNUM = 1 )
, COALESCE(APD.CHARGE_APPLICABLE_TO_DIST_ID
, APD.RELATED_ID
, APD.INVOICE_DISTRIBUTION_ID))
FROM AP_INVOICE_DISTRIBUTIONS_ALL APD
WHERE APD.INVOICE_DISTRIBUTION_ID = DECODE(PBC.DOCUMENT_TYPE
, 'PO'
, DECODE(PBC.REFERENCE1
, 'AP'
, TO_NUMBER(PBC.REFERENCE3)
, PBC.DOCUMENT_DISTRIBUTION_ID)
, PBC.DOCUMENT_DISTRIBUTION_ID)) ALLOC_TO_FIRST_DIST_ID
, NULL
, DECODE(DOCUMENT_TYPE
, 'PO'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'SCHEDULED'
, PBC.DOCUMENT_HEADER_ID_2
, 'BLANKET'
, PBC.DOCUMENT_HEADER_ID_2
, NULL)
, NULL)
, DOCUMENT_DISTRIBUTION_TYPE
, TXN_CCID AI_DIST_CCID
, DECODE(DOCUMENT_TYPE
, 'AP'
, DECODE(PA_FUNDS_CONTROL_UTILS.GET_AP_ACCT_REVERSAL_ATTR(XEV.EVENT_TYPE_CODE
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.DOCUMENT_DISTRIBUTION_TYPE)
, NULL
, 'N'
, 'Y')
, 'N') ACCT_REVERSAL_INDICATOR
, DECODE(DOCUMENT_TYPE
, 'AP'
, PA_FUNDS_CONTROL_UTILS.GET_AP_ACCT_REVERSAL_ATTR(XEV.EVENT_TYPE_CODE
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.DOCUMENT_DISTRIBUTION_TYPE)
, TO_NUMBER(NULL)) ACCT_REVERSAL_FIRST_DIST_ID
, DECODE(DOCUMENT_TYPE
, 'AP'
, 'PA_AP_BURDEN'
, NULL) ACCT_REV_DISTRIBUTION_TYPE
FROM PA_BC_PACKETS PBC
, XLA_EVENTS_GT XEV
, PA_RESOURCE_LIST_MEMBERS PRLM
, GL_LEDGERS GLL
WHERE XEV.EVENT_ID = PBC.BC_EVENT_ID
AND PBC.DOCUMENT_TYPE IN ('PO'
, 'AP')
AND PBC.RESOURCE_LIST_MEMBER_ID = PRLM.RESOURCE_LIST_MEMBER_ID
AND PBC.BURDEN_COST_FLAG IN ('N'
, 'O')
AND PBC.SET_OF_BOOKS_ID = GLL.LEDGER_ID
AND PBC.STATUS_CODE = 'I'