VIEW: APPS.PA_XLA_BC_PKT_PO_DETAIL_V

Source

View Text - Preformatted

SELECT Pbc.bc_event_id, pbc.document_distribution_id, pbc.document_distribution_id, pbc.document_line_id, pbc.expenditure_type, Pbc.bc_packet_id, Pbc.budget_version_id, decode(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (xev_source.event_type_code ,pbc.main_or_backing_code ,pbc.document_distribution_type), 1, nvl(accounted_dr,0), -1, nvl(accounted_cr,0)), decode(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (xev_source.event_type_code ,pbc.main_or_backing_code ,pbc.document_distribution_type), 1, nvl(entered_dr,0), -1, nvl(entered_cr,0)), decode(pbc.document_type, 'PO', 'PA_PO_BURDEN', 'REQ', 'PA_REQ_BURDEN'), pbc.project_id, pbc.task_id, pbc.expenditure_organization_id, pbc.expenditure_item_Date, pbc.encumbrance_type_id, pbc.proj_encumbrance_type_id, gll.currency_code, gll.currency_code, NULL, to_date(null), to_number(null), pbc.budget_ccid, pbc.document_type, pbc.document_header_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.main_or_backing_code, pbc.burden_cost_flag ,PA_FUNDS_CONTROL_UTILS.get_sla_notupgraded_flag (201,DECODE(document_type,'PO' ,decode(pbc.document_distribution_type ,'SCHEDULED','RELEASE' ,'BLANKET','RELEASE' ,'PURCHASE_ORDER') ,'REQUISITION') ,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) ,pbc.document_header_id) ,pbc.document_distribution_id ,DECODE(document_type,'PO','PO_DISTRIBUTIONS_ALL','PO_REQ_DISTRIBUTIONS_ALL') ,Pbc.budget_version_id ,pbc.budget_line_id) Use_Enc_Upg_Attrib_Flag ,decode(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (xev_source.event_type_code ,pbc.main_or_backing_code ,pbc.document_distribution_type), 1, DECODE(document_Type, 'REQ', (decode(burden_method_code, 'S', 'REQ_PA_BURDENED', 'D', 'REQ_PA_BURDEN', NULL)) , 'PO' , (decode(burden_method_code, 'S', 'PO_PA_BURDENED', 'D', 'PO_PA_BURDEN', NULL)) ), -1, 'PA_BUDGET_ENC' ) Enc_Upg_Dr_Acct_Class ,decode(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (xev_source.event_type_code ,pbc.main_or_backing_code ,pbc.document_distribution_type), 1, 'PA_BUDGET_ENC', -1, DECODE(document_Type, 'REQ', (decode(burden_method_code, 'S', 'REQ_PA_BURDENED', 'D', 'REQ_PA_BURDEN', NULL)) , '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(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (xev_source.event_type_code ,pbc.main_or_backing_code ,pbc.document_distribution_type), 1, pbc.encumbrance_type_id, -1, pbc.proj_encumbrance_type_id ) Enc_Upg_Dr_Enc_Type_Id ,decode(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (xev_source.event_type_code ,pbc.main_or_backing_code ,pbc.document_distribution_type), 1, pbc.proj_encumbrance_type_id, -1, pbc.encumbrance_type_id ) Enc_Upg_Cr_Enc_Type_Id ,decode(document_type, 'REQ', 201, NULL) ,decode(document_type, 'REQ', 'PA_REQ_BURDEN', NULL) ,decode(document_type, 'REQ', 'REQUISITION', NULL) ,decode(document_type, 'REQ', pbc.document_distribution_id, NULL) ,decode(document_type, 'REQ', pbc.expenditure_type, NULL) ,decode(document_type, 'REQ', pbc.document_header_id, NULL) ,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) ,275 ,'PA_BUDGET_ENC' ,'BUDGETS' ,budget_line_id ,null ,budget_version_id ,201 ,'PO_DISTRIBUTIONS_ALL' ,decode(document_type, 'PO', decode(pbc.document_distribution_type ,'SCHEDULED','RELEASE' ,'BLANKET','RELEASE' ,'PURCHASE_ORDER') ,DECODE(pbc.reference1,'REL','RELEASE','PO','PURCHASE_ORDER','REQUISITION')) ,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) , to_number(pbc.reference2)) ,DECODE(document_type, 'PO', pbc.document_distribution_id , to_number(pbc.reference3)) ,null ,'N' ,DECODE(pbc.document_type,'REQ',PO_DOCUMENT_FUNDS_PVT.is_req_enc_flipped(pbc.document_distribution_id,pbc.source_event_id),'N') From Pa_Bc_Packets pbc, Xla_Events_Gt xev, Xla_Events_Gt xev_source, PA_resource_list_members prlm, GL_Ledgers gll WHERE xev.event_id = pbc.bc_event_id And xev_source.event_id = pbc.source_event_id And pbc.document_type in ( 'REQ', 'PO') 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.DOCUMENT_DISTRIBUTION_ID
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.DOCUMENT_LINE_ID
, PBC.EXPENDITURE_TYPE
, PBC.BC_PACKET_ID
, PBC.BUDGET_VERSION_ID
, DECODE(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (XEV_SOURCE.EVENT_TYPE_CODE
, PBC.MAIN_OR_BACKING_CODE
, PBC.DOCUMENT_DISTRIBUTION_TYPE)
, 1
, NVL(ACCOUNTED_DR
, 0)
, -1
, NVL(ACCOUNTED_CR
, 0))
, DECODE(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (XEV_SOURCE.EVENT_TYPE_CODE
, PBC.MAIN_OR_BACKING_CODE
, PBC.DOCUMENT_DISTRIBUTION_TYPE)
, 1
, NVL(ENTERED_DR
, 0)
, -1
, NVL(ENTERED_CR
, 0))
, DECODE(PBC.DOCUMENT_TYPE
, 'PO'
, 'PA_PO_BURDEN'
, 'REQ'
, 'PA_REQ_BURDEN')
, PBC.PROJECT_ID
, PBC.TASK_ID
, PBC.EXPENDITURE_ORGANIZATION_ID
, PBC.EXPENDITURE_ITEM_DATE
, PBC.ENCUMBRANCE_TYPE_ID
, PBC.PROJ_ENCUMBRANCE_TYPE_ID
, GLL.CURRENCY_CODE
, GLL.CURRENCY_CODE
, NULL
, TO_DATE(NULL)
, TO_NUMBER(NULL)
, PBC.BUDGET_CCID
, PBC.DOCUMENT_TYPE
, PBC.DOCUMENT_HEADER_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.MAIN_OR_BACKING_CODE
, PBC.BURDEN_COST_FLAG
, PA_FUNDS_CONTROL_UTILS.GET_SLA_NOTUPGRADED_FLAG (201
, DECODE(DOCUMENT_TYPE
, 'PO'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'SCHEDULED'
, 'RELEASE'
, 'BLANKET'
, 'RELEASE'
, 'PURCHASE_ORDER')
, 'REQUISITION')
, 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)
, PBC.DOCUMENT_HEADER_ID)
, PBC.DOCUMENT_DISTRIBUTION_ID
, DECODE(DOCUMENT_TYPE
, 'PO'
, 'PO_DISTRIBUTIONS_ALL'
, 'PO_REQ_DISTRIBUTIONS_ALL')
, PBC.BUDGET_VERSION_ID
, PBC.BUDGET_LINE_ID) USE_ENC_UPG_ATTRIB_FLAG
, DECODE(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (XEV_SOURCE.EVENT_TYPE_CODE
, PBC.MAIN_OR_BACKING_CODE
, PBC.DOCUMENT_DISTRIBUTION_TYPE)
, 1
, DECODE(DOCUMENT_TYPE
, 'REQ'
, (DECODE(BURDEN_METHOD_CODE
, 'S'
, 'REQ_PA_BURDENED'
, 'D'
, 'REQ_PA_BURDEN'
, NULL))
, 'PO'
, (DECODE(BURDEN_METHOD_CODE
, 'S'
, 'PO_PA_BURDENED'
, 'D'
, 'PO_PA_BURDEN'
, NULL)) )
, -1
, 'PA_BUDGET_ENC' ) ENC_UPG_DR_ACCT_CLASS
, DECODE(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (XEV_SOURCE.EVENT_TYPE_CODE
, PBC.MAIN_OR_BACKING_CODE
, PBC.DOCUMENT_DISTRIBUTION_TYPE)
, 1
, 'PA_BUDGET_ENC'
, -1
, DECODE(DOCUMENT_TYPE
, 'REQ'
, (DECODE(BURDEN_METHOD_CODE
, 'S'
, 'REQ_PA_BURDENED'
, 'D'
, 'REQ_PA_BURDEN'
, NULL))
, '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(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (XEV_SOURCE.EVENT_TYPE_CODE
, PBC.MAIN_OR_BACKING_CODE
, PBC.DOCUMENT_DISTRIBUTION_TYPE)
, 1
, PBC.ENCUMBRANCE_TYPE_ID
, -1
, PBC.PROJ_ENCUMBRANCE_TYPE_ID ) ENC_UPG_DR_ENC_TYPE_ID
, DECODE(PA_FUNDS_CONTROL_UTILS.DERIVE_PO_REQ_AMT_SIDE (XEV_SOURCE.EVENT_TYPE_CODE
, PBC.MAIN_OR_BACKING_CODE
, PBC.DOCUMENT_DISTRIBUTION_TYPE)
, 1
, PBC.PROJ_ENCUMBRANCE_TYPE_ID
, -1
, PBC.ENCUMBRANCE_TYPE_ID ) ENC_UPG_CR_ENC_TYPE_ID
, DECODE(DOCUMENT_TYPE
, 'REQ'
, 201
, NULL)
, DECODE(DOCUMENT_TYPE
, 'REQ'
, 'PA_REQ_BURDEN'
, NULL)
, DECODE(DOCUMENT_TYPE
, 'REQ'
, 'REQUISITION'
, NULL)
, DECODE(DOCUMENT_TYPE
, 'REQ'
, PBC.DOCUMENT_DISTRIBUTION_ID
, NULL)
, DECODE(DOCUMENT_TYPE
, 'REQ'
, PBC.EXPENDITURE_TYPE
, NULL)
, DECODE(DOCUMENT_TYPE
, 'REQ'
, PBC.DOCUMENT_HEADER_ID
, NULL)
, 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)
, 275
, 'PA_BUDGET_ENC'
, 'BUDGETS'
, BUDGET_LINE_ID
, NULL
, BUDGET_VERSION_ID
, 201
, 'PO_DISTRIBUTIONS_ALL'
, DECODE(DOCUMENT_TYPE
, 'PO'
, DECODE(PBC.DOCUMENT_DISTRIBUTION_TYPE
, 'SCHEDULED'
, 'RELEASE'
, 'BLANKET'
, 'RELEASE'
, 'PURCHASE_ORDER')
, DECODE(PBC.REFERENCE1
, 'REL'
, 'RELEASE'
, 'PO'
, 'PURCHASE_ORDER'
, 'REQUISITION'))
, 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)
, TO_NUMBER(PBC.REFERENCE2))
, DECODE(DOCUMENT_TYPE
, 'PO'
, PBC.DOCUMENT_DISTRIBUTION_ID
, TO_NUMBER(PBC.REFERENCE3))
, NULL
, 'N'
, DECODE(PBC.DOCUMENT_TYPE
, 'REQ'
, PO_DOCUMENT_FUNDS_PVT.IS_REQ_ENC_FLIPPED(PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.SOURCE_EVENT_ID)
, 'N')
FROM PA_BC_PACKETS PBC
, XLA_EVENTS_GT XEV
, XLA_EVENTS_GT XEV_SOURCE
, PA_RESOURCE_LIST_MEMBERS PRLM
, GL_LEDGERS GLL
WHERE XEV.EVENT_ID = PBC.BC_EVENT_ID
AND XEV_SOURCE.EVENT_ID = PBC.SOURCE_EVENT_ID
AND PBC.DOCUMENT_TYPE IN ( 'REQ'
, 'PO')
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'