VIEW: APPS.PA_FC_COMMITMENT_DTLS_V

Source

View Text - Preformatted

SELECT project_id , project_name , task_id , vendor_name supplier_name , vendor_id supplier_id , segment1 supplier_number , expenditure_item_date , document_type commitment_type_code , document_type_desc commitment_type , expenditure_type , document_number , line_number , distribution_number , task_number , sum (entered_dr - entered_cr) txn_commitment_amt , txn_currency , sum (accounted_dr - accounted_cr) func_commitment_amt , func_currency , project_currency , project_rate_type , project_rate_date , projfunc_currency_code , projfunc_cost_rate_date , projfunc_cost_rate_type , period_name as_of_period , gl_date , pa_date , org_id FROM ( SELECT pbc.project_id , pp.segment1 project_name , pbc.task_id , ptl.task_number , pv.vendor_name , pv.vendor_id , pv.segment1 , pbc.je_source_name , pbc.document_type , pl.meaning document_type_desc , ap.invoice_num document_number , apd.invoice_line_number line_number , apd.distribution_line_number distribution_number , pbc.pa_date , pbc.gl_date , pbc.resource_list_member_id , pbc.budget_ccid , pbc.txn_ccid , pbc.expenditure_type , pbc.expenditure_item_date , pbc.period_name , pbc.je_category_name , pbc.je_source_name , pbc.document_header_id , pbc.document_distribution_id , pbc.entered_dr , pbc.entered_cr , nvl (ap.invoice_currency_code, gl.currency_code) txn_currency , pbc.accounted_dr , pbc.accounted_cr , gl.currency_code func_currency , pp.project_currency_code project_currency , pp.project_rate_type , pp.project_rate_date , pp.projfunc_currency_code , pp.projfunc_cost_rate_date , pp.projfunc_cost_rate_type , pbc.org_id FROM pa_bc_commitments pbc , pa_tasks ptl , ap_invoices ap , ap_invoice_distributions apd , po_vendors pv , gl_ledgers gl , pa_implementations pia , pa_projects pp , pa_lookups pl WHERE pbc.task_id = ptl.task_id AND pbc.document_header_id = apd.invoice_id AND ap.invoice_id = apd.invoice_id AND pbc.document_distribution_id = apd.invoice_distribution_id AND ap.vendor_id = pv.vendor_id AND pbc.project_id = pp.project_id AND pbc.org_id = pia.org_id AND pia.set_of_books_id = gl.ledger_id AND pp.org_id = pia.org_id AND document_type = 'AP' AND pl.lookup_type = 'FC_DOC_TYPE' AND pbc.document_type = pl.lookup_code UNION ALL SELECT pbc.project_id , pp.segment1 project_name , pbc.task_id , ptl.task_number , pv.vendor_name , pv.vendor_id , pv.segment1 , pbc.je_source_name , pbc.document_type , pl.meaning document_type_desc , poh.segment1 document_number , pol.line_num line_number , pod.distribution_num distribution_number , pbc.pa_date , pbc.gl_date , pbc.resource_list_member_id , pbc.budget_ccid , pbc.txn_ccid , pbc.expenditure_type , pbc.expenditure_item_date , pbc.period_name , pbc.je_category_name , pbc.je_source_name , pbc.document_header_id , pbc.document_distribution_id , pbc.entered_dr , pbc.entered_cr , nvl (poh.currency_code, gl.currency_code) txn_currency , pbc.accounted_dr , pbc.accounted_cr , gl.currency_code func_currency , pp.project_currency_code project_currency , pp.project_rate_type , pp.project_rate_date , pp.projfunc_currency_code , pp.projfunc_cost_rate_date , pp.projfunc_cost_rate_type , pbc.org_id FROM pa_bc_commitments pbc , pa_tasks ptl , po_headers poh , po_distributions pod , po_lines_all pol , po_vendors pv , gl_ledgers gl , pa_implementations pia , pa_projects pp , pa_lookups pl WHERE pbc.task_id = ptl.task_id AND pbc.document_header_id = poh.po_header_id AND poh.po_header_id = pod.po_header_id AND pbc.project_id = pp.project_id AND pbc.document_distribution_id = pod.po_distribution_id AND pod.po_line_id = pol.po_line_id AND pol.po_header_id = poh.po_header_id AND pv.vendor_id = poh.vendor_id AND pbc.project_id = pp.project_id AND pbc.org_id = pia.org_id AND pia.set_of_books_id = gl.ledger_id AND pp.org_id = pia.org_id AND document_type = 'PO' AND pl.lookup_type = 'FC_DOC_TYPE' AND pbc.document_type = pl.lookup_code UNION ALL SELECT pbc.project_id , pp.segment1 project_name , pbc.task_id , ptl.task_number , pv.vendor_name , pv.vendor_id , pv.segment1 , pbc.je_source_name , pbc.document_type , pl.meaning document_type_desc , porh.segment1 document_number , porl.line_num , pord.distribution_num , pbc.pa_date , pbc.gl_date , pbc.resource_list_member_id , pbc.budget_ccid , pbc.txn_ccid , pbc.expenditure_type , pbc.expenditure_item_date , pbc.period_name , pbc.je_category_name , pbc.je_source_name , pbc.document_header_id , pbc.document_distribution_id , pbc.entered_dr , pbc.entered_cr , nvl (porl.currency_code, gl.currency_code) txn_currency , pbc.accounted_dr , pbc.accounted_cr , gl.currency_code func_currency , pp.project_currency_code , pp.project_rate_type , pp.project_rate_date , pp.projfunc_currency_code , pp.projfunc_cost_rate_date , pp.projfunc_cost_rate_type , pbc.org_id FROM pa_bc_commitments pbc , pa_tasks ptl , po_requisition_headers porh , po_req_distributions pord , po_requisition_lines porl , po_vendors pv , gl_ledgers gl , pa_implementations pia , pa_projects pp , pa_lookups pl WHERE pbc.task_id = ptl.task_id AND pbc.document_header_id = porh.requisition_header_id AND porh.requisition_header_id = porl.requisition_header_id AND pbc.document_distribution_id = pord.distribution_id AND pord.requisition_line_id = porl.requisition_line_id AND pv.vendor_id (+) = nvl (porl.vendor_id, - 1) AND pbc.project_id = pp.project_id AND pbc.org_id = pia.org_id AND pia.set_of_books_id = gl.ledger_id AND pp.org_id = pia.org_id AND document_type = 'REQ' AND pl.lookup_type = 'FC_DOC_TYPE' AND pbc.document_type = pl.lookup_code UNION ALL SELECT pbc.project_id , pp.segment1 project_name , pbc.task_id , ptl.task_number , pv.vendor_name , pv.vendor_id , pv.segment1 , pbc.je_source_name , pbc.document_type , pl.meaning document_type_desc , ap.invoice_num document_number , apd.invoice_line_number line_number , apd.distribution_line_number distribution_number , pbc.pa_date , pbc.gl_date , pbc.resource_list_member_id , pbc.budget_ccid , pbc.txn_ccid , pbc.expenditure_type , pbc.expenditure_item_date , pbc.period_name , pbc.je_category_name , pbc.je_source_name , pbc.document_header_id , pbc.document_distribution_id , pbc.entered_dr , pbc.entered_cr , nvl (ap.invoice_currency_code, gl.currency_code) txn_currency , pbc.accounted_dr , pbc.accounted_cr , gl.currency_code func_currency , pp.project_currency_code project_currency , pp.project_rate_type , pp.project_rate_date , pp.projfunc_currency_code , pp.projfunc_cost_rate_date , pp.projfunc_cost_rate_type , pbc.org_id FROM pa_bc_packets pbc , pa_tasks ptl , ap_invoices ap , ap_invoice_distributions apd , po_vendors pv , gl_ledgers gl , pa_implementations pia , pa_projects pp , pa_lookups pl WHERE pbc.task_id = ptl.task_id AND pbc.document_header_id = apd.invoice_id AND ap.invoice_id = apd.invoice_id AND pbc.document_distribution_id = apd.invoice_distribution_id AND ap.vendor_id = pv.vendor_id AND pbc.status_code IN ('A', 'C') AND pbc.balance_posted_flag = 'N' AND pbc.project_id = pp.project_id AND pbc.org_id = pia.org_id AND pia.set_of_books_id = gl.ledger_id AND pp.org_id = pia.org_id AND document_type = 'AP' AND pl.lookup_type = 'FC_DOC_TYPE' AND pbc.document_type = pl.lookup_code UNION ALL SELECT pbc.project_id , pp.segment1 project_name , pbc.task_id , ptl.task_number , pv.vendor_name , pv.vendor_id , pv.segment1 , pbc.je_source_name , pbc.document_type , pl.meaning document_type_desc , poh.segment1 document_number , pol.line_num line_number , pod.distribution_num distribution_number , pbc.pa_date , pbc.gl_date , pbc.resource_list_member_id , pbc.budget_ccid , pbc.txn_ccid , pbc.expenditure_type , pbc.expenditure_item_date , pbc.period_name , pbc.je_category_name , pbc.je_source_name , pbc.document_header_id , pbc.document_distribution_id , pbc.entered_dr , pbc.entered_cr , nvl (poh.currency_code, gl.currency_code) txn_currency , pbc.accounted_dr , pbc.accounted_cr , gl.currency_code func_currency , pp.project_currency_code project_currency , pp.project_rate_type , pp.project_rate_date , pp.projfunc_currency_code , pp.projfunc_cost_rate_date , pp.projfunc_cost_rate_type , pbc.org_id FROM pa_bc_packets pbc , pa_tasks ptl , po_headers_all poh , po_distributions_all pod , po_lines_all pol , po_vendors pv , gl_ledgers gl , pa_implementations_all pia , pa_projects_all pp , pa_lookups pl WHERE pbc.task_id = ptl.task_id AND pbc.document_header_id = poh.po_header_id AND poh.po_header_id = pod.po_header_id AND pbc.project_id = pp.project_id AND pbc.document_distribution_id = pod.po_distribution_id AND pbc.status_code IN ('A', 'C') AND pbc.balance_posted_flag = 'N' AND pod.po_line_id = pol.po_line_id AND pol.po_header_id = poh.po_header_id AND pv.vendor_id = poh.vendor_id AND pbc.project_id = pp.project_id AND pbc.org_id = pia.org_id AND pia.set_of_books_id = gl.ledger_id AND pp.org_id = pia.org_id AND document_type = 'PO' AND pl.lookup_type = 'FC_DOC_TYPE' AND pbc.document_type = pl.lookup_code UNION ALL SELECT pbc.project_id , pp.segment1 project_name , pbc.task_id , ptl.task_number , pv.vendor_name , pv.vendor_id , pv.segment1 , pbc.je_source_name , pbc.document_type , pl.meaning document_type_desc , porh.segment1 document_number , porl.line_num , pord.distribution_num , pbc.pa_date , pbc.gl_date , pbc.resource_list_member_id , pbc.budget_ccid , pbc.txn_ccid , pbc.expenditure_type , pbc.expenditure_item_date , pbc.period_name , pbc.je_category_name , pbc.je_source_name , pbc.document_header_id , pbc.document_distribution_id , pbc.entered_dr , pbc.entered_cr , nvl (porl.currency_code, gl.currency_code) txn_currency , pbc.accounted_dr , pbc.accounted_cr , gl.currency_code func_currency , pp.project_currency_code , pp.project_rate_type , pp.project_rate_date , pp.projfunc_currency_code , pp.projfunc_cost_rate_date , pp.projfunc_cost_rate_type , pbc.org_id FROM pa_bc_packets pbc , pa_tasks ptl , po_requisition_headers porh , po_req_distributions pord , po_requisition_lines porl , po_vendors pv , gl_ledgers gl , pa_implementations pia , pa_projects pp , pa_lookups pl WHERE pbc.task_id = ptl.task_id AND pbc.document_header_id = porh.requisition_header_id AND porh.requisition_header_id = porl.requisition_header_id AND pbc.status_code IN ('A', 'C') AND pbc.balance_posted_flag = 'N' AND pbc.document_distribution_id = pord.distribution_id AND pord.requisition_line_id = porl.requisition_line_id AND pv.vendor_id (+) = nvl (porl.vendor_id, - 1) AND pbc.project_id = pp.project_id AND pbc.org_id = pia.org_id AND pia.set_of_books_id = gl.ledger_id AND pp.org_id = pia.org_id AND document_type = 'REQ' AND pl.lookup_type = 'FC_DOC_TYPE' AND pbc.document_type = pl.lookup_code ) GROUP BY project_id , project_name , task_id , vendor_name , vendor_id , segment1 , expenditure_item_date , document_type , document_type_desc , expenditure_type , document_number , line_number , distribution_number , task_number , func_currency , txn_currency , project_currency , project_rate_type , project_rate_date , projfunc_currency_code , projfunc_cost_rate_date , projfunc_cost_rate_type , period_name , gl_date , pa_date , org_id HAVING sum (accounted_dr - accounted_cr) <> 0

View Text - HTML Formatted

SELECT PROJECT_ID
, PROJECT_NAME
, TASK_ID
, VENDOR_NAME SUPPLIER_NAME
, VENDOR_ID SUPPLIER_ID
, SEGMENT1 SUPPLIER_NUMBER
, EXPENDITURE_ITEM_DATE
, DOCUMENT_TYPE COMMITMENT_TYPE_CODE
, DOCUMENT_TYPE_DESC COMMITMENT_TYPE
, EXPENDITURE_TYPE
, DOCUMENT_NUMBER
, LINE_NUMBER
, DISTRIBUTION_NUMBER
, TASK_NUMBER
, SUM (ENTERED_DR - ENTERED_CR) TXN_COMMITMENT_AMT
, TXN_CURRENCY
, SUM (ACCOUNTED_DR - ACCOUNTED_CR) FUNC_COMMITMENT_AMT
, FUNC_CURRENCY
, PROJECT_CURRENCY
, PROJECT_RATE_TYPE
, PROJECT_RATE_DATE
, PROJFUNC_CURRENCY_CODE
, PROJFUNC_COST_RATE_DATE
, PROJFUNC_COST_RATE_TYPE
, PERIOD_NAME AS_OF_PERIOD
, GL_DATE
, PA_DATE
, ORG_ID
FROM ( SELECT PBC.PROJECT_ID
, PP.SEGMENT1 PROJECT_NAME
, PBC.TASK_ID
, PTL.TASK_NUMBER
, PV.VENDOR_NAME
, PV.VENDOR_ID
, PV.SEGMENT1
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_TYPE
, PL.MEANING DOCUMENT_TYPE_DESC
, AP.INVOICE_NUM DOCUMENT_NUMBER
, APD.INVOICE_LINE_NUMBER LINE_NUMBER
, APD.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_NUMBER
, PBC.PA_DATE
, PBC.GL_DATE
, PBC.RESOURCE_LIST_MEMBER_ID
, PBC.BUDGET_CCID
, PBC.TXN_CCID
, PBC.EXPENDITURE_TYPE
, PBC.EXPENDITURE_ITEM_DATE
, PBC.PERIOD_NAME
, PBC.JE_CATEGORY_NAME
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_HEADER_ID
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.ENTERED_DR
, PBC.ENTERED_CR
, NVL (AP.INVOICE_CURRENCY_CODE
, GL.CURRENCY_CODE) TXN_CURRENCY
, PBC.ACCOUNTED_DR
, PBC.ACCOUNTED_CR
, GL.CURRENCY_CODE FUNC_CURRENCY
, PP.PROJECT_CURRENCY_CODE PROJECT_CURRENCY
, PP.PROJECT_RATE_TYPE
, PP.PROJECT_RATE_DATE
, PP.PROJFUNC_CURRENCY_CODE
, PP.PROJFUNC_COST_RATE_DATE
, PP.PROJFUNC_COST_RATE_TYPE
, PBC.ORG_ID
FROM PA_BC_COMMITMENTS PBC
, PA_TASKS PTL
, AP_INVOICES AP
, AP_INVOICE_DISTRIBUTIONS APD
, PO_VENDORS PV
, GL_LEDGERS GL
, PA_IMPLEMENTATIONS PIA
, PA_PROJECTS PP
, PA_LOOKUPS PL
WHERE PBC.TASK_ID = PTL.TASK_ID
AND PBC.DOCUMENT_HEADER_ID = APD.INVOICE_ID
AND AP.INVOICE_ID = APD.INVOICE_ID
AND PBC.DOCUMENT_DISTRIBUTION_ID = APD.INVOICE_DISTRIBUTION_ID
AND AP.VENDOR_ID = PV.VENDOR_ID
AND PBC.PROJECT_ID = PP.PROJECT_ID
AND PBC.ORG_ID = PIA.ORG_ID
AND PIA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND PP.ORG_ID = PIA.ORG_ID
AND DOCUMENT_TYPE = 'AP'
AND PL.LOOKUP_TYPE = 'FC_DOC_TYPE'
AND PBC.DOCUMENT_TYPE = PL.LOOKUP_CODE UNION ALL SELECT PBC.PROJECT_ID
, PP.SEGMENT1 PROJECT_NAME
, PBC.TASK_ID
, PTL.TASK_NUMBER
, PV.VENDOR_NAME
, PV.VENDOR_ID
, PV.SEGMENT1
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_TYPE
, PL.MEANING DOCUMENT_TYPE_DESC
, POH.SEGMENT1 DOCUMENT_NUMBER
, POL.LINE_NUM LINE_NUMBER
, POD.DISTRIBUTION_NUM DISTRIBUTION_NUMBER
, PBC.PA_DATE
, PBC.GL_DATE
, PBC.RESOURCE_LIST_MEMBER_ID
, PBC.BUDGET_CCID
, PBC.TXN_CCID
, PBC.EXPENDITURE_TYPE
, PBC.EXPENDITURE_ITEM_DATE
, PBC.PERIOD_NAME
, PBC.JE_CATEGORY_NAME
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_HEADER_ID
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.ENTERED_DR
, PBC.ENTERED_CR
, NVL (POH.CURRENCY_CODE
, GL.CURRENCY_CODE) TXN_CURRENCY
, PBC.ACCOUNTED_DR
, PBC.ACCOUNTED_CR
, GL.CURRENCY_CODE FUNC_CURRENCY
, PP.PROJECT_CURRENCY_CODE PROJECT_CURRENCY
, PP.PROJECT_RATE_TYPE
, PP.PROJECT_RATE_DATE
, PP.PROJFUNC_CURRENCY_CODE
, PP.PROJFUNC_COST_RATE_DATE
, PP.PROJFUNC_COST_RATE_TYPE
, PBC.ORG_ID
FROM PA_BC_COMMITMENTS PBC
, PA_TASKS PTL
, PO_HEADERS POH
, PO_DISTRIBUTIONS POD
, PO_LINES_ALL POL
, PO_VENDORS PV
, GL_LEDGERS GL
, PA_IMPLEMENTATIONS PIA
, PA_PROJECTS PP
, PA_LOOKUPS PL
WHERE PBC.TASK_ID = PTL.TASK_ID
AND PBC.DOCUMENT_HEADER_ID = POH.PO_HEADER_ID
AND POH.PO_HEADER_ID = POD.PO_HEADER_ID
AND PBC.PROJECT_ID = PP.PROJECT_ID
AND PBC.DOCUMENT_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PV.VENDOR_ID = POH.VENDOR_ID
AND PBC.PROJECT_ID = PP.PROJECT_ID
AND PBC.ORG_ID = PIA.ORG_ID
AND PIA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND PP.ORG_ID = PIA.ORG_ID
AND DOCUMENT_TYPE = 'PO'
AND PL.LOOKUP_TYPE = 'FC_DOC_TYPE'
AND PBC.DOCUMENT_TYPE = PL.LOOKUP_CODE UNION ALL SELECT PBC.PROJECT_ID
, PP.SEGMENT1 PROJECT_NAME
, PBC.TASK_ID
, PTL.TASK_NUMBER
, PV.VENDOR_NAME
, PV.VENDOR_ID
, PV.SEGMENT1
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_TYPE
, PL.MEANING DOCUMENT_TYPE_DESC
, PORH.SEGMENT1 DOCUMENT_NUMBER
, PORL.LINE_NUM
, PORD.DISTRIBUTION_NUM
, PBC.PA_DATE
, PBC.GL_DATE
, PBC.RESOURCE_LIST_MEMBER_ID
, PBC.BUDGET_CCID
, PBC.TXN_CCID
, PBC.EXPENDITURE_TYPE
, PBC.EXPENDITURE_ITEM_DATE
, PBC.PERIOD_NAME
, PBC.JE_CATEGORY_NAME
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_HEADER_ID
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.ENTERED_DR
, PBC.ENTERED_CR
, NVL (PORL.CURRENCY_CODE
, GL.CURRENCY_CODE) TXN_CURRENCY
, PBC.ACCOUNTED_DR
, PBC.ACCOUNTED_CR
, GL.CURRENCY_CODE FUNC_CURRENCY
, PP.PROJECT_CURRENCY_CODE
, PP.PROJECT_RATE_TYPE
, PP.PROJECT_RATE_DATE
, PP.PROJFUNC_CURRENCY_CODE
, PP.PROJFUNC_COST_RATE_DATE
, PP.PROJFUNC_COST_RATE_TYPE
, PBC.ORG_ID
FROM PA_BC_COMMITMENTS PBC
, PA_TASKS PTL
, PO_REQUISITION_HEADERS PORH
, PO_REQ_DISTRIBUTIONS PORD
, PO_REQUISITION_LINES PORL
, PO_VENDORS PV
, GL_LEDGERS GL
, PA_IMPLEMENTATIONS PIA
, PA_PROJECTS PP
, PA_LOOKUPS PL
WHERE PBC.TASK_ID = PTL.TASK_ID
AND PBC.DOCUMENT_HEADER_ID = PORH.REQUISITION_HEADER_ID
AND PORH.REQUISITION_HEADER_ID = PORL.REQUISITION_HEADER_ID
AND PBC.DOCUMENT_DISTRIBUTION_ID = PORD.DISTRIBUTION_ID
AND PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID
AND PV.VENDOR_ID (+) = NVL (PORL.VENDOR_ID
, - 1)
AND PBC.PROJECT_ID = PP.PROJECT_ID
AND PBC.ORG_ID = PIA.ORG_ID
AND PIA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND PP.ORG_ID = PIA.ORG_ID
AND DOCUMENT_TYPE = 'REQ'
AND PL.LOOKUP_TYPE = 'FC_DOC_TYPE'
AND PBC.DOCUMENT_TYPE = PL.LOOKUP_CODE UNION ALL SELECT PBC.PROJECT_ID
, PP.SEGMENT1 PROJECT_NAME
, PBC.TASK_ID
, PTL.TASK_NUMBER
, PV.VENDOR_NAME
, PV.VENDOR_ID
, PV.SEGMENT1
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_TYPE
, PL.MEANING DOCUMENT_TYPE_DESC
, AP.INVOICE_NUM DOCUMENT_NUMBER
, APD.INVOICE_LINE_NUMBER LINE_NUMBER
, APD.DISTRIBUTION_LINE_NUMBER DISTRIBUTION_NUMBER
, PBC.PA_DATE
, PBC.GL_DATE
, PBC.RESOURCE_LIST_MEMBER_ID
, PBC.BUDGET_CCID
, PBC.TXN_CCID
, PBC.EXPENDITURE_TYPE
, PBC.EXPENDITURE_ITEM_DATE
, PBC.PERIOD_NAME
, PBC.JE_CATEGORY_NAME
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_HEADER_ID
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.ENTERED_DR
, PBC.ENTERED_CR
, NVL (AP.INVOICE_CURRENCY_CODE
, GL.CURRENCY_CODE) TXN_CURRENCY
, PBC.ACCOUNTED_DR
, PBC.ACCOUNTED_CR
, GL.CURRENCY_CODE FUNC_CURRENCY
, PP.PROJECT_CURRENCY_CODE PROJECT_CURRENCY
, PP.PROJECT_RATE_TYPE
, PP.PROJECT_RATE_DATE
, PP.PROJFUNC_CURRENCY_CODE
, PP.PROJFUNC_COST_RATE_DATE
, PP.PROJFUNC_COST_RATE_TYPE
, PBC.ORG_ID
FROM PA_BC_PACKETS PBC
, PA_TASKS PTL
, AP_INVOICES AP
, AP_INVOICE_DISTRIBUTIONS APD
, PO_VENDORS PV
, GL_LEDGERS GL
, PA_IMPLEMENTATIONS PIA
, PA_PROJECTS PP
, PA_LOOKUPS PL
WHERE PBC.TASK_ID = PTL.TASK_ID
AND PBC.DOCUMENT_HEADER_ID = APD.INVOICE_ID
AND AP.INVOICE_ID = APD.INVOICE_ID
AND PBC.DOCUMENT_DISTRIBUTION_ID = APD.INVOICE_DISTRIBUTION_ID
AND AP.VENDOR_ID = PV.VENDOR_ID
AND PBC.STATUS_CODE IN ('A'
, 'C')
AND PBC.BALANCE_POSTED_FLAG = 'N'
AND PBC.PROJECT_ID = PP.PROJECT_ID
AND PBC.ORG_ID = PIA.ORG_ID
AND PIA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND PP.ORG_ID = PIA.ORG_ID
AND DOCUMENT_TYPE = 'AP'
AND PL.LOOKUP_TYPE = 'FC_DOC_TYPE'
AND PBC.DOCUMENT_TYPE = PL.LOOKUP_CODE UNION ALL SELECT PBC.PROJECT_ID
, PP.SEGMENT1 PROJECT_NAME
, PBC.TASK_ID
, PTL.TASK_NUMBER
, PV.VENDOR_NAME
, PV.VENDOR_ID
, PV.SEGMENT1
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_TYPE
, PL.MEANING DOCUMENT_TYPE_DESC
, POH.SEGMENT1 DOCUMENT_NUMBER
, POL.LINE_NUM LINE_NUMBER
, POD.DISTRIBUTION_NUM DISTRIBUTION_NUMBER
, PBC.PA_DATE
, PBC.GL_DATE
, PBC.RESOURCE_LIST_MEMBER_ID
, PBC.BUDGET_CCID
, PBC.TXN_CCID
, PBC.EXPENDITURE_TYPE
, PBC.EXPENDITURE_ITEM_DATE
, PBC.PERIOD_NAME
, PBC.JE_CATEGORY_NAME
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_HEADER_ID
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.ENTERED_DR
, PBC.ENTERED_CR
, NVL (POH.CURRENCY_CODE
, GL.CURRENCY_CODE) TXN_CURRENCY
, PBC.ACCOUNTED_DR
, PBC.ACCOUNTED_CR
, GL.CURRENCY_CODE FUNC_CURRENCY
, PP.PROJECT_CURRENCY_CODE PROJECT_CURRENCY
, PP.PROJECT_RATE_TYPE
, PP.PROJECT_RATE_DATE
, PP.PROJFUNC_CURRENCY_CODE
, PP.PROJFUNC_COST_RATE_DATE
, PP.PROJFUNC_COST_RATE_TYPE
, PBC.ORG_ID
FROM PA_BC_PACKETS PBC
, PA_TASKS PTL
, PO_HEADERS_ALL POH
, PO_DISTRIBUTIONS_ALL POD
, PO_LINES_ALL POL
, PO_VENDORS PV
, GL_LEDGERS GL
, PA_IMPLEMENTATIONS_ALL PIA
, PA_PROJECTS_ALL PP
, PA_LOOKUPS PL
WHERE PBC.TASK_ID = PTL.TASK_ID
AND PBC.DOCUMENT_HEADER_ID = POH.PO_HEADER_ID
AND POH.PO_HEADER_ID = POD.PO_HEADER_ID
AND PBC.PROJECT_ID = PP.PROJECT_ID
AND PBC.DOCUMENT_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND PBC.STATUS_CODE IN ('A'
, 'C')
AND PBC.BALANCE_POSTED_FLAG = 'N'
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PV.VENDOR_ID = POH.VENDOR_ID
AND PBC.PROJECT_ID = PP.PROJECT_ID
AND PBC.ORG_ID = PIA.ORG_ID
AND PIA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND PP.ORG_ID = PIA.ORG_ID
AND DOCUMENT_TYPE = 'PO'
AND PL.LOOKUP_TYPE = 'FC_DOC_TYPE'
AND PBC.DOCUMENT_TYPE = PL.LOOKUP_CODE UNION ALL SELECT PBC.PROJECT_ID
, PP.SEGMENT1 PROJECT_NAME
, PBC.TASK_ID
, PTL.TASK_NUMBER
, PV.VENDOR_NAME
, PV.VENDOR_ID
, PV.SEGMENT1
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_TYPE
, PL.MEANING DOCUMENT_TYPE_DESC
, PORH.SEGMENT1 DOCUMENT_NUMBER
, PORL.LINE_NUM
, PORD.DISTRIBUTION_NUM
, PBC.PA_DATE
, PBC.GL_DATE
, PBC.RESOURCE_LIST_MEMBER_ID
, PBC.BUDGET_CCID
, PBC.TXN_CCID
, PBC.EXPENDITURE_TYPE
, PBC.EXPENDITURE_ITEM_DATE
, PBC.PERIOD_NAME
, PBC.JE_CATEGORY_NAME
, PBC.JE_SOURCE_NAME
, PBC.DOCUMENT_HEADER_ID
, PBC.DOCUMENT_DISTRIBUTION_ID
, PBC.ENTERED_DR
, PBC.ENTERED_CR
, NVL (PORL.CURRENCY_CODE
, GL.CURRENCY_CODE) TXN_CURRENCY
, PBC.ACCOUNTED_DR
, PBC.ACCOUNTED_CR
, GL.CURRENCY_CODE FUNC_CURRENCY
, PP.PROJECT_CURRENCY_CODE
, PP.PROJECT_RATE_TYPE
, PP.PROJECT_RATE_DATE
, PP.PROJFUNC_CURRENCY_CODE
, PP.PROJFUNC_COST_RATE_DATE
, PP.PROJFUNC_COST_RATE_TYPE
, PBC.ORG_ID
FROM PA_BC_PACKETS PBC
, PA_TASKS PTL
, PO_REQUISITION_HEADERS PORH
, PO_REQ_DISTRIBUTIONS PORD
, PO_REQUISITION_LINES PORL
, PO_VENDORS PV
, GL_LEDGERS GL
, PA_IMPLEMENTATIONS PIA
, PA_PROJECTS PP
, PA_LOOKUPS PL
WHERE PBC.TASK_ID = PTL.TASK_ID
AND PBC.DOCUMENT_HEADER_ID = PORH.REQUISITION_HEADER_ID
AND PORH.REQUISITION_HEADER_ID = PORL.REQUISITION_HEADER_ID
AND PBC.STATUS_CODE IN ('A'
, 'C')
AND PBC.BALANCE_POSTED_FLAG = 'N'
AND PBC.DOCUMENT_DISTRIBUTION_ID = PORD.DISTRIBUTION_ID
AND PORD.REQUISITION_LINE_ID = PORL.REQUISITION_LINE_ID
AND PV.VENDOR_ID (+) = NVL (PORL.VENDOR_ID
, - 1)
AND PBC.PROJECT_ID = PP.PROJECT_ID
AND PBC.ORG_ID = PIA.ORG_ID
AND PIA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND PP.ORG_ID = PIA.ORG_ID
AND DOCUMENT_TYPE = 'REQ'
AND PL.LOOKUP_TYPE = 'FC_DOC_TYPE'
AND PBC.DOCUMENT_TYPE = PL.LOOKUP_CODE ) GROUP BY PROJECT_ID
, PROJECT_NAME
, TASK_ID
, VENDOR_NAME
, VENDOR_ID
, SEGMENT1
, EXPENDITURE_ITEM_DATE
, DOCUMENT_TYPE
, DOCUMENT_TYPE_DESC
, EXPENDITURE_TYPE
, DOCUMENT_NUMBER
, LINE_NUMBER
, DISTRIBUTION_NUMBER
, TASK_NUMBER
, FUNC_CURRENCY
, TXN_CURRENCY
, PROJECT_CURRENCY
, PROJECT_RATE_TYPE
, PROJECT_RATE_DATE
, PROJFUNC_CURRENCY_CODE
, PROJFUNC_COST_RATE_DATE
, PROJFUNC_COST_RATE_TYPE
, PERIOD_NAME
, GL_DATE
, PA_DATE
, ORG_ID HAVING SUM (ACCOUNTED_DR - ACCOUNTED_CR) <> 0