SELECT 'Oracle Payables' transaction_source,
apinv.invoice_number source_id,
apinv.vendor_name Suppler,
pp.segment1 project_num,
tt.task_number,
apinv.expenditure_type ,
TO_CHAR(apinv.expenditure_item_date,'DD-MON-YY') exp_date,
NULL batch_name ,
apinv.quantity,
expt.UOM unit,
pa_currency.round_currency_amt((apinv.denom_amount * pa_multi_currency_txn.get_projfunc_curr_amount(apinv.project_id, apinv.task_id , apinv.expenditure_item_date,apinv.denom_currency_code))) amount,
pp.projfunc_currency_code curr
FROM pa_ap_inv_exceptions_sum_v apinv,
pa_projects_all pp,
pa_tasks tt,
pa_expenditure_types_desc_v expt,
pa_periods_all pap
WHERE apinv.expenditure_type =expt.expenditure_type
AND apinv.task_id = tt.task_id
AND apinv.project_id = pp.project_id
AND tt.project_id = pp.project_id
AND pp.org_id =apinv.org_id
AND TRUNC(apinv.pa_date) BETWEEN pap.start_date AND pap.end_date
AND apinv.org_id = pap.org_id
AND pap.period_name = :PERIOD_NAME
AND :non_infd_txn_exists = 'Y'
AND apinv.org_id =fnd_global.org_id
UNION
SELECT 'Oracle Purchasing' transaction_source,
(select distinct receipt_num from rcv_shipment_headers WHERE shipment_header_id= rcv.shipment_header_id) source_id,
(SELECT vendor_name FROM po_vendors WHERE vendor_id=rcv.vendor_id
) Suppler,
pp.segment1 project_num,
tt.task_number,
rcv.expenditure_type ,
TO_CHAR(rcv.expenditure_item_date,'DD-MON-YY') exp_date,
NULL batch_name ,
RT.quantity,
expt.UOM unit,
pa_currency.round_currency_amt((rcv.denom_amount * pa_multi_currency_txn.get_projfunc_curr_amount(rcv.project_id, rcv.task_id , rcv.expenditure_item_date,rcv.denom_currency_code))) amount,
pp.projfunc_currency_code
FROM pa_rcv_exceptions_sum_v rcv,
rcv_transactions rt,
pa_projects_all pp,
pa_tasks tt,
pa_expenditure_types_desc_v expt,
pa_periods_all pap
WHERE rcv.rcv_transaction_id=rt.transaction_id
AND rcv.expenditure_type =expt.expenditure_type
AND rcv.task_id = tt.task_id
AND rcv.project_id = pp.project_id
AND tt.project_id = pp.project_id
AND pp.org_id =rcv.org_id
AND TRUNC(rcv.pa_date) BETWEEN pap.start_date AND pap.end_date
AND pap.period_name = :PERIOD_NAME
AND :non_infd_txn_exists = 'Y'
AND rcv.org_id = pap.org_id
AND rcv.org_id =fnd_global.org_id
UNION
SELECT pti.user_transaction_source transaction_source,
pti.orig_transaction_reference source_id,
NULL Suppler,
pti.project_number project_num,
pti.task_number,
pti.expenditure_type ,
TO_CHAR(pti.expenditure_item_date,'DD-MON-YY') exp_date,
pti.batch_name ,
pti.quantity,
expt.UOM unit,
pa_currency.round_currency_amt((pti.denom_raw_cost * pa_multi_currency_txn.get_projfunc_curr_amount(tt.project_id, tt.task_id , pti.expenditure_item_date,pti.denom_currency_code))) amount,
pp.projfunc_currency_code curr
FROM pa_transaction_interface_all pti,
pa_implementations_all imp,
pa_expenditure_types_desc_v expt,
pa_projects_all pp,
pa_periods_all pap,
pa_tasks tt
WHERE pti.transaction_status_code IN ('P','R')
AND pti.expenditure_type =expt.expenditure_type
AND pti.task_number = tt.task_number
AND pti.project_number = pp.segment1
AND tt.project_id = pp.project_id
AND pti.system_linkage IN ('PJ' , 'USG' ,'VI' , 'INV','ER', 'WIP')
AND pa_utils2.get_pa_date(pti.expenditure_item_date, sysdate,imp.org_id) BETWEEN pap.start_date AND pap.end_date
AND NVL(pti.org_id,-99) = imp.org_id
AND NVL(pap.org_id,-99) = imp.org_id
AND pap.period_name = :PERIOD_NAME
AND :non_infd_txn_exists = 'Y'
AND imp.org_id = fnd_global.org_id
UNION
SELECT 'Inventory/Project Manufacturing' transaction_source,
to_char(pm.transaction_id) source_id,
NULL Suppler,
prj.segment1 project_num,
tsk.task_number,
mtl. expenditure_type ,
TO_CHAR(pm.transaction_date,'DD-MON-YY') exp_date,
NULL batch_name ,
mtl.primary_quantity,
expt.UOM unit,
pa_currency.round_currency_amt((mtl.actual_cost * pa_multi_currency_txn.get_projfunc_curr_amount(pm.project_id, pm.task_id , pm.transaction_date,mtl.currency_code))) amount ,
prj.projfunc_currency_code curr
FROM cst_pm_pending_txn_v pm,
pa_projects_all prj,
pa_tasks tsk,
hr_all_organization_units org,
pa_implementations_all imp,
pa_periods_all pap,
mtl_material_transactions mtl,
pa_expenditure_types_desc_v expt
WHERE pm.transaction_id = mtl.transaction_id
AND pm.project_id = prj.project_id
AND pm.task_id = tsk.task_id(+)
AND pm.orig_transaction_table= 1
AND pm.organization_id = org.organization_id
AND mtl.expenditure_type =expt.expenditure_type
AND TRUNC(pm.transaction_date) BETWEEN pap.start_date AND pap.end_date
AND NVL(imp.org_id, -99) = NVL(prj.org_id,-99)
AND NVL(pap.org_id, -99) = imp.org_id
AND pap.period_name = :PERIOD_NAME
AND :non_infd_txn_exists = 'Y'
AND imp.org_id =fnd_global.org_id
union
SELECT 'Inventory/Project Manufacturing' transaction_source,
TO_CHAR(pm.transaction_id) source_id,
NULL Suppler,
prj.segment1 project_num,
tsk.task_number,
NULL,
TO_CHAR(pm.transaction_date,'DD-MON-YY') exp_date,
NULL batch_name ,
wip.primary_quantity,
NULL unit,
NULL amount ,
prj.projfunc_currency_code curr
FROM cst_pm_pending_txn_v pm,
pa_projects_all prj,
pa_tasks tsk,
hr_all_organization_units org,
pa_implementations_all imp,
pa_periods_all pap,
wip_transactions wip
WHERE pm.transaction_id = wip.transaction_id
AND pm.project_id = prj.project_id
AND pm.task_id = tsk.task_id(+)
AND pm.orig_transaction_table= 2
AND pm.organization_id = org.organization_id
AND TRUNC(pm.transaction_date) BETWEEN pap.start_date AND pap.end_date
AND NVL(imp.org_id, -99) = NVL(prj.org_id,-99)
AND NVL(pap.org_id, -99) = imp.org_id
AND pap.period_name = :PERIOD_NAME
AND :non_infd_txn_exists = 'Y'
AND imp.org_id =fnd_global.org_id |