[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'NOT COST DISTRIBUTED'
from dual
where exists (
select ei.expenditure_item_id
from pa_expenditure_items_all ei
where ei.project_id = p_project_id
and ( p_active_flag <> 'A'
or (trunc(ei.expenditure_item_date ) <=
trunc(p_txn_to_date ) ))
and nvl(ei.cost_distributed_flag, 'N') <> 'Y') ;
select 'NOT IMPORTED'
from dual
where exists (
select 'X'
from pa_transaction_interface_all it,
pa_projects t
where it.project_number = t.segment1
and t.project_id = p_project_id
and ( p_active_flag <> 'A'
or (trunc(it.expenditure_item_date ) <=
trunc(p_txn_to_date ) ))
and it.transaction_status_code <> 'A') ;
select 'NOT Transferred and Accepted in other system'
from dual
where exists (
select ei.expenditure_item_id
from pa_expenditure_items_all ei,
pa_cost_distribution_lines_all cdl
where ei.expenditure_item_id = cdl.expenditure_item_id
and ei.system_linkage_function not in ('VI', 'ER') /* Bug#2427766 */
/* and ei.system_linkage_function <> 'VI' Bug#2616111 */
and ei.project_id = p_project_id
and ( p_active_flag <> 'A'
or (trunc(ei.expenditure_item_date ) <=
trunc(p_txn_to_date ) ))
and (cdl.transfer_status_code not in ( 'A','V','G') /** 4317826 **/
or exists (select 1
from xla_events xe
where xe.event_id = cdl.acct_event_id
and xe.event_status_code <> 'P'
and xe.process_status_code <> 'P')));
or exists (select reference26
from pa_gl_interface gl
where
gl.user_je_source_name || '' = P_User_Source_Name
and gl.reference26 = cdl.batch_name
and ((gl.Status NOT LIKE 'W%'
and gl.Status <> 'NEW'
and gl.Status <> 'PROCESSED')
or
gl.Status = 'NEW')))) ;
select 'MRC NOT Transferred and Accepted in other system'
from dual
where exists (
select ei.expenditure_item_id
from pa_expenditure_items_all ei,
pa_mc_cost_dist_lines_all mrccdl
where ei.expenditure_item_id = mrccdl.expenditure_item_id
and ei.system_linkage_function not in ('VI', 'ER')
and ei.project_id = p_project_id
and ( p_active_flag <> 'A'
or (trunc(ei.expenditure_item_date ) <= trunc(p_txn_to_date ) ))
and (mrccdl.transfer_status_code not in ('A','V','G')
or exists (select reference26
from pa_gl_interface gl
where
gl.user_je_source_name || '' = P_User_Source_Name
and gl.reference26 = mrccdl.batch_name
and ((gl.Status NOT LIKE 'W%'
and gl.Status <> 'NEW'
and gl.Status <> 'PROCESSED')
or
gl.Status = 'NEW')))) ;
select 'Cost not accumulated'
from dual
where exists (
select ei.expenditure_item_id
from pa_expenditure_items_all ei,
pa_cost_distribution_lines_all cdl
where ei.expenditure_item_id = cdl.expenditure_item_id
and ei.project_id = p_project_id
and ( p_active_flag <> 'A'
or (trunc(ei.expenditure_item_date ) <=
trunc(p_txn_to_date ) ))
and cdl.line_type = 'R'
and nvl(cdl.resource_accumulated_flag, 'N') <> 'Y') ;
select 'Not Burden Distributed'
from dual
where exists (
select ei.expenditure_item_id
from pa_expenditure_items_all ei,
pa_project_types_all pt,
pa_tasks t,
pa_projects_all p
where ei.task_id = t.task_id
and t.project_id = p.project_id
and t.project_id = p_project_id
and p.project_type = pt.project_type
and pt.org_id = p.org_id -- Removed NVL for bug#590817 by vvjoshi
and pt.burden_cost_flag = 'Y'
and ( p_active_flag <> 'A'
or (trunc(ei.expenditure_item_date ) <=
trunc(p_txn_to_date ) ))) ;
select 'INTERCOMPANY EIs INV EXISTS'
from dual
where exists (
select ei.expenditure_item_id
from pa_expenditure_items_all ei,
pa_tasks t
where ei.task_id = t.task_id
and t.project_id = p_project_id
and ei.cc_cross_charge_code = 'I')
or exists (
select null
from pa_draft_invoice_details_all di
where di.cc_project_id = p_project_id );
select 'NOT BL DISTRIBUTED'
from dual
where exists (
select ei.expenditure_item_id
from pa_expenditure_items_all ei
where ei.project_id = p_project_id
and ( p_active_flag <> 'A'
or trunc(ei.expenditure_item_date) <= trunc(p_txn_to_date) )
and ei.cc_cross_charge_code = 'B'
and ei.cc_bl_distributed_code <> 'Y') ;
select 'NOT Transferred and Accepted in other system'
from dual
where exists (
select ei.expenditure_item_id
from pa_expenditure_items_all ei,
pa_cc_dist_lines_all ccdl
where ei.expenditure_item_id = ccdl.expenditure_item_id
and ei.project_id = p_project_id
and ( p_active_flag <> 'A'
or (trunc(ei.expenditure_item_date ) <=
trunc(p_txn_to_date ) ))
and (ccdl.transfer_status_code not in ( 'A','V')
or exists (select 1
from xla_events xe
where xe.event_id = ccdl.acct_event_id
and xe.event_status_code <> 'P'
and xe.process_status_code <> 'P')));
or exists (select reference26
from pa_gl_interface gl
where
gl.user_je_source_name || '' = P_User_Source_Name
and gl.reference26 = ccdl.gl_batch_name
and ((gl.Status NOT LIKE 'W%'
and gl.Status <> 'NEW'
and gl.Status <> 'PROCESSED')
or
gl.Status = 'NEW')))) ;
select 'This project is defined as a destination project in project type'
from dual
where exists (
select project_type
from pa_project_types
where burden_sum_dest_project_id = p_project_id
);
SELECT 'VENDOR INVOICE NOT FULLY PAID'
FROM dual
WHERE EXISTS ( SELECT aid.invoice_id
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid
-- bug 2404115 WHERE ai.project_id = p_project_id
WHERE aid.project_id = p_project_id
AND nvl(ai.invoice_amount,0) <> 0 /* Bug 5063560 */
AND ai.invoice_id = aid.invoice_id
AND ai.invoice_type_lookup_code <> 'EXPENSE REPORT'
AND (p_active_flag <> 'A' or
-- bug 2404115 ai.expenditure_item_date < p_txn_to_date)
trunc(aid.expenditure_item_date) <= trunc(p_txn_to_date))
AND aid.pa_addition_flag = 'Y'
AND nvl(ai.payment_status_flag,'N') <> 'Y'
AND nvl(aid.reversal_flag, 'N') <> 'Y' /* 4065283 */
/* Bug#2407614. For all the supplier invoices check if payment has been done.
AND exists (SELECT NULL
FROM ap_payment_schedules_all aps
WHERE aps.invoice_id = ai.invoice_id
AND nvl(aps.discount_amount_available, 0) > 0
AND nvl(aps.amount_remaining, 0 ) > 0
) */
);
select 'This is an Inter-Project Receiver Project'
from dual
where exists ( select NULL
from pa_tasks pt
where pt.project_id = p_project_id
and pt.receive_project_invoice_flag = 'Y'
and exists ( select NULL
from pa_project_customers ppc
where ppc.receiver_task_id = pt.task_id )
);
select 'Discount Not Interfaced'
from dual
where exists (
select null from ap_invoices ai,
ap_invoice_distributions aid,
-- ap_invoice_payments pay --R12 change
ap_payment_hist_dists paydist
where aid.project_id = p_project_id
and ai.invoice_id = aid.invoice_id
-- and paydist.invoice_id = ai.invoice_id -- R12 change
-- and pay.discount_taken <> 0 -- R12 change
and paydist.invoice_distribution_id = aid.invoice_distribution_id
and paydist.amount <> 0
and ai.invoice_type_lookup_code <> 'EXPENSE REPORT'
and paydist.pay_dist_lookup_code = 'DISCOUNT'
and paydist.pa_addition_flag = 'N'
and aid.expenditure_item_date >= nvl(to_date(fnd_profile.value_specific('PA_DISC_PULL_START_DATE'),'YYYY/MM/DD'),to_date('01/01/2051','DD/MM/YYYY')) /* Bug 3134267 */ /*Bug4124600*/ /*Bug 6855026*/
/* --Commented for R12
and not exists
( select 'X' from pa_cost_distribution_lines cdl
where cdl.system_reference2=to_char(aid.invoice_id)
and cdl.system_reference3=to_char(aid.distribution_line_number)
and cdl.system_reference4=to_char(pay.invoice_payment_id)
)
*/
);
select 'Organization Forecast Project'
from dual
where exists (
select null from pa_projects pj,
pa_project_types pt
where pj.project_id = p_project_id
and pj.project_type = pt.project_type
and pt.org_project_flag = 'Y'
);
select 'Unassigned Time Project Type'
from dual
where exists ( select pt.project_type
from pa_project_types pt,
pa_projects p
where p.project_id = p_project_id
and pt.project_type = p.project_type
and nvl(pt.unassigned_time, 'N') = 'Y' );
select 'Iexpense Transactions Pending'
from dual
where exists (select h.report_header_id
from ap_expense_report_headers h,
ap_expense_report_lines l
where h.report_header_id = l.report_header_id
and h.source <> 'Oracle Project Accounting'
and l.project_id = p_project_id
and ( p_active_flag <> 'A'
or (trunc(l.expenditure_item_date ) <=
trunc(p_txn_to_date ) ))
and nvl(l.pa_interfaced_flag,'N') <> 'Y'); */
select 'Iexpense Transactions Pending'
from dual
where exists (
select null
from ap_expense_report_headers h,
ap_expense_report_lines l
where l.project_id = p_project_id
and (p_active_flag <> 'A'
or (trunc(l.expenditure_item_date ) <=
trunc(p_txn_to_date )))
and l.report_header_id = h.report_header_id
and h.source <> 'Oracle Project Accounting'
and not exists (
select null
from ap_invoice_distributions d
where d.invoice_id = h.vouchno
and d.pa_addition_flag IN ('Z','T','E','Y')));
SELECT 'EXPENSE REPORTS NOT FULLY PAID'
FROM dual
WHERE EXISTS ( SELECT aid.invoice_id
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid
WHERE aid.project_id = p_project_id
AND ai.invoice_id = aid.invoice_id
AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
AND (p_active_flag <> 'A' or
trunc(aid.expenditure_item_date) <=
trunc(p_txn_to_date))
AND nvl(ai.payment_status_flag,'N') <> 'Y' );
V_Delete_Allowed VARCHAR2(1);
SELECT user_je_source_name
INTO l_user_source_name
FROM GL_Je_Sources
WHERE je_source_name='Project Accounting';
IF ( NOT PNP_OTH_PROD.delete_project (p_project_id) ) then
fnd_message.set_name('PA', 'PA_ARPR_PROJ_INUSE_PROP_MGR');
call to the procedure IGC_CC_PROJECTS_PKG.delete_project for bug#2272487
IF ( NOT IGC_CC_PROJECTS_PKG.delete_project (p_project_id) ) then
fnd_message.set_name('PA', 'PA_ARPR_PROJ_INUSE_CNTR_CMTS');
select count(*) into l_igc_exists
from fnd_product_installations
where application_id = 8407
and status <> 'N';
IGC_CC_PROJECTS_PKG.delete_project (:project_id, :delete_allowed);
DBMS_SQL.BIND_VARIABLE(V_CursorID, ':delete_allowed', V_Delete_Allowed, 1);
DBMS_SQL.VARIABLE_VALUE(V_CursorID, ':delete_allowed', V_Delete_Allowed);
IF ( V_Delete_Allowed = 'N' ) then
fnd_message.set_name('PA', 'PA_ARPR_PROJ_INUSE_CNTR_CMTS');
select to_number(replace(release_name,'.'))
into l_dummy_num
from fnd_product_groups;
V_Stmt := 'select ' || '''Iexpense Transactions Pending''' ||
' from dual '||
' where exists (select h.report_header_id '||
' from ap_expense_report_headers h, '||
' ap_expense_report_lines l '||
' where h.report_header_id = l.report_header_id '||
' and h.source <> '||'''Oracle Project Accounting''' ||
' and l.project_id = :p_proj_id '||
' and ( :p_act_flag <> '|| '''A''' ||
' or (trunc(l.expenditure_item_date ) <= trunc(:p_txn_date ) )) '||
' and nvl(l.pa_interfaced_flag,'||'''N'''||') <> '||'''Y'''||')';
select 1
from dual
where exists ( select null
from pa_alloc_run_sources pars,
pa_alloc_runs par
where par.rule_id = pars.rule_id
and pars.project_id = p_proj_id
and par.run_id = pars.run_id
/* and par.run_status <> 'RS' commented for bug#2446122 */
and par.run_status not in ( 'RS', 'RV') /* Added for bug#2446122 */
);
select 1
from dual
where exists ( select null
from pa_alloc_run_targets part,
pa_alloc_runs par
where par.rule_id = part.rule_id
and part.project_id = p_proj_id
and par.run_id = part.run_id
/* and par.run_status <> 'RS' commented for bug#2446122 */
and par.run_status not in ( 'RS', 'RV') /* Added for bug#2446122 */
);
select 1
from dual
where exists ( select null
from pa_alloc_rules_all para,
pa_alloc_runs par
where par.rule_id = para.rule_id
and para.offset_project_id = p_proj_id
/* and par.run_status <> 'RS' commented for bug#2446122 */
and par.run_status not in ( 'RS', 'RV') /* Added for bug#2446122 */
);