[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'VENDOR INVOICE PENDING'
FROM dual
WHERE EXISTS ( SELECT aid.invoice_id
FROM ap_invoice_distributions_all aid
WHERE aid.project_id = p_project_id
AND (p_active_flag = 'C' or
aid.expenditure_item_date < trunc(p_purge_to_date))
AND aid.pa_addition_flag <> 'Y');
SELECT 'VENDOR INVOICE PENDING'
FROM dual
WHERE EXISTS ( SELECT aid.invoice_id
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai
WHERE aid.project_id = p_project_id
AND aid.invoice_id = ai.invoice_id
AND ai.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND (p_active_flag = 'C' or
trunc(aid.expenditure_item_date) <= trunc(p_purge_to_date))
-- Bug 2767507 Added 'G' to the list of values which pa_addition_flag
-- shouldnot check when looking for pending invoices.
AND aid.pa_addition_flag NOT IN ('Z','T','E','Y','G', 'F') --bug# 4351752
-- Bug# 2407499
AND nvl(aid.reversal_flag, 'N') <> 'Y');
SELECT 'ASSET LINE NOT TRANSFERRED'
FROM dual
WHERE EXISTS ( SELECT pal.project_asset_line_id
FROM pa_project_asset_lines_all pal
WHERE pal.project_id = p_project_id
AND pal.transfer_status_code <> 'T');
SELECT 'ADJ NOT TRANSFERRED TO AP'
FROM dual
WHERE EXISTS (SELECT ei.expenditure_item_id
FROM pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei
WHERE ei.expenditure_item_id = cdl.expenditure_item_id
AND ((cdl.transfer_status_code in ('P','R','X')
and ei.system_linkage_function = 'VI') or
(cdl.transfer_status_code in ('P','R','X','T')
and ei.system_linkage_function = 'ER'))
AND cdl.line_type = 'R'
AND (p_active_flag = 'C' or
trunc(ei.expenditure_item_date)<= trunc(p_purge_to_date))
AND ei.project_id = p_project_id);
SELECT 'EI NOT COSTED'
FROM dual
WHERE EXISTS ( SELECT ei.expenditure_item_id
FROM pa_expenditure_items_all ei,
pa_tasks pt
WHERE ei.system_linkage_function in ( 'VI', 'ER' )
AND ei.task_id = pt.task_id
AND pt.project_id = p_project_id
AND (p_active_flag = 'C' or ei.expenditure_item_date < trunc(p_purge_to_date))
AND ei.cost_distributed_flag||'' = 'N');
SELECT 'EXP ITEM NOT CAPITALIZED'
FROM dual
WHERE EXISTS ( SELECT pcdl.expenditure_item_id
FROM pa_cost_distribution_lines_all pcdl,
pa_expenditure_items_all pei,
pa_tasks pt,
pa_projects pp,
pa_project_types ppt
WHERE pcdl.expenditure_item_id = pei.expenditure_item_id
AND pei.revenue_distributed_flag||'' = 'N'
AND pcdl.line_type = DECODE(ppt.capital_cost_type_code,'B','D','R')
AND pcdl.billable_flag = 'Y'
AND pei.task_id = pt.task_id
AND pt.project_id = pp.project_id
AND pp.project_id = p_project_id
AND (p_active_flag = 'C' or
trunc(pei.expenditure_item_date) <= trunc(p_purge_to_date))
AND pp.project_type = ppt.project_type
AND pei.task_id IN
( select task_id
from pa_tasks pt2
where project_id = pp.project_id
and ( exists
(SELECT task_id
FROM pa_project_asset_assignments paa
WHERE paa.project_id = pp.project_id
and (paa.task_id = pt2.task_id or
paa.task_id = pt2.top_task_id))
or exists --- Return all common tasks
(SELECT task_id
FROM pa_project_asset_assignments paa
WHERE paa.project_id = pp.project_id
and task_id = pt2.task_id
AND paa.project_asset_id = 0))
UNION
SELECT task_id
FROM pa_tasks
WHERE project_id IN
(SELECT project_id --- project level asset assignment
FROM pa_project_asset_assignments paa
WHERE project_id = pp.project_id
AND NVL(task_id, 0) = 0
UNION
SELECT project_id --- return all common tasks
FROM pa_project_asset_assignments paa
WHERE paa.project_id = pp.project_id
and NVL(paa.task_id,0) = 0
AND paa.project_asset_id = 0))
AND NOT EXISTS
(
SELECT 'This CDL was summarized before'
FROM pa_project_asset_line_details pald
WHERE pald.expenditure_item_id = pcdl.expenditure_item_id
AND pald.line_num = pcdl.line_num
AND pald.reversed_flag||'' = 'N'
));
SELECT 'COMMITMENT EXISTS'
FROM dual
WHERE EXISTS ( SELECT pctv.project_id
FROM pa_commitment_txns_v pctv
WHERE pctv.project_id = p_project_id
AND pctv.expenditure_item_date is not null
AND (p_active_flag = 'C' or
trunc(pctv.expenditure_item_date) <= trunc(p_purge_to_date))
AND pctv.line_type not in ('P','I') /* Bug 2503781.*/
UNION /* Below lines added for bug 2503781 */
SELECT pctv1.project_id
FROM pa_commitment_txns_v pctv1
WHERE pctv1.project_id = p_project_id
AND pctv1.expenditure_item_date is not null
AND (p_active_flag = 'C' or
trunc(pctv1.expenditure_item_date) <= trunc(p_purge_to_date))
AND pctv1.line_type = 'P'
AND pctv1.tot_cmt_quantity > 0
AND nvl(pctv1.quantity_cancelled,0) =0
UNION /* Added for bug 2553822 */
/* Bug 2598071 SELECT aid.invoice_id */
SELECT aid.invoice_id
FROM ap_invoice_distributions_all aid,
ap_invoices_all ai
WHERE aid.project_id = p_project_id
AND aid.invoice_id = ai.invoice_id
AND ai.source <> 'Oracle Project Accounting'
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
AND (p_active_flag = 'C' or
trunc(aid.expenditure_item_date) <= trunc(p_purge_to_date))
AND aid.pa_addition_flag NOT IN ('Z','T','E','Y')
AND nvl(aid.reversal_flag, 'N') <> 'Y');