PA Delete PA Periods

Description
Categories: BI Publisher, Financials
Application: Projects
Source: PRC: Delete PA Periods
Short Name: PAXDELPA
DB package: PA_PERIODS_MODIFICATION_PKG
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
Parameter Name SQL text Validation
Mode
 
LOV Oracle
To Period
 
LOV Oracle
From Period
 
LOV Oracle