1 package body pa_purge_validate_costing as
2 /* $Header: PAXCSVTB.pls 120.8 2007/12/09 10:23:35 vvjoshi ship $ */
3
4 -- forward declarations
5
6 -- Start of comments
7 -- API name : validate_costing
8 -- Type : Public
9 -- Pre-reqs : None
10 -- Function : This procedure is the main validate procedure for
11 -- costing validations.
12 --
13 -- Parameters : p_project_Id IN NUMBER,
14 -- The project id for which records have
15 -- to be purged/archived.
16 -- p_txn_to_date IN DATE,
17 -- If the purging is being done on projects
18 -- that are active then this parameter is
19 -- determine the date to which the transactions
20 -- need to be purged.
21 -- p_active_flag IN VARCHAR2,
22 -- The flag to specify purging is done on
23 -- open or closed projects.
24 -- X_Err_Stack IN OUT VARCHAR2,
25 -- Error stack
26 -- X_Err_Stage IN OUT VARCHAR2,
27 -- Stage in the procedure where error occurred
28 -- X_Err_Code IN OUT NUMBER
29 -- Error code returned from the procedure
30 -- End of comments
31
32 procedure Validate_Costing (
33 p_project_id in NUMBER,
34 p_txn_to_date in DATE,
35 p_active_flag in VARCHAR2,
36 x_err_code in OUT NOCOPY NUMBER,
37 x_err_stack in OUT NOCOPY VARCHAR2,
38 x_err_stage in OUT NOCOPY VARCHAR2 )
39 is
40
41
42 cursor IsCostDistributed is
43 select 'NOT COST DISTRIBUTED'
44 from dual
45 where exists (
46 select ei.expenditure_item_id
47 from pa_expenditure_items_all ei
48 where ei.project_id = p_project_id
49 and ( p_active_flag <> 'A'
50 or (trunc(ei.expenditure_item_date ) <=
51 trunc(p_txn_to_date ) ))
52 and nvl(ei.cost_distributed_flag, 'N') <> 'Y') ;
53
54 cursor IsTransImported is
55 select 'NOT IMPORTED'
56 from dual
57 where exists (
58 select 'X'
59 from pa_transaction_interface_all it,
60 pa_projects t
61 where it.project_number = t.segment1
62 and t.project_id = p_project_id
63 and ( p_active_flag <> 'A'
64 or (trunc(it.expenditure_item_date ) <=
65 trunc(p_txn_to_date ) ))
66 and it.transaction_status_code <> 'A') ;
67
68 cursor IsInterfaced (P_User_Source_Name IN VARCHAR2) is
69 select 'NOT Transferred and Accepted in other system'
70 from dual
71 where exists (
72 select ei.expenditure_item_id
73 from pa_expenditure_items_all ei,
74 pa_cost_distribution_lines_all cdl
75 where ei.expenditure_item_id = cdl.expenditure_item_id
76 and ei.system_linkage_function not in ('VI', 'ER') /* Bug#2427766 */
77 /* and ei.system_linkage_function <> 'VI' Bug#2616111 */
78 and ei.project_id = p_project_id
79 and ( p_active_flag <> 'A'
80 or (trunc(ei.expenditure_item_date ) <=
81 trunc(p_txn_to_date ) ))
82 and (cdl.transfer_status_code not in ( 'A','V','G') /** 4317826 **/
83 or exists (select 1
84 from xla_events xe
85 where xe.event_id = cdl.acct_event_id
86 and xe.event_status_code <> 'P'
87 and xe.process_status_code <> 'P')));
88
89 /* Commented below for R12
90 or exists (select reference26
91 from pa_gl_interface gl
92 where
93 gl.user_je_source_name || '' = P_User_Source_Name
94 and gl.reference26 = cdl.batch_name
95 and ((gl.Status NOT LIKE 'W%'
96 and gl.Status <> 'NEW'
97 and gl.Status <> 'PROCESSED')
98 or
99 gl.Status = 'NEW')))) ;
100 */
101
102
103 /* Commented below code for R12 changes.
104 cursor IsMRCInterfaced (P_User_Source_Name IN VARCHAR2) is
105 select 'MRC NOT Transferred and Accepted in other system'
106 from dual
107 where exists (
108 select ei.expenditure_item_id
109 from pa_expenditure_items_all ei,
110 pa_mc_cost_dist_lines_all mrccdl
111 where ei.expenditure_item_id = mrccdl.expenditure_item_id
112 and ei.system_linkage_function not in ('VI', 'ER')
113 and ei.project_id = p_project_id
114 and ( p_active_flag <> 'A'
115 or (trunc(ei.expenditure_item_date ) <= trunc(p_txn_to_date ) ))
116 and (mrccdl.transfer_status_code not in ('A','V','G')
117 or exists (select reference26
118 from pa_gl_interface gl
119 where
120 gl.user_je_source_name || '' = P_User_Source_Name
121 and gl.reference26 = mrccdl.batch_name
122 and ((gl.Status NOT LIKE 'W%'
123 and gl.Status <> 'NEW'
124 and gl.Status <> 'PROCESSED')
125 or
126 gl.Status = 'NEW')))) ;
127 */
128
129 cursor IsResAccumulated is
130 select 'Cost not accumulated'
131 from dual
132 where exists (
133 select ei.expenditure_item_id
134 from pa_expenditure_items_all ei,
135 pa_cost_distribution_lines_all cdl
136 where ei.expenditure_item_id = cdl.expenditure_item_id
137 and ei.project_id = p_project_id
138 and ( p_active_flag <> 'A'
139 or (trunc(ei.expenditure_item_date ) <=
140 trunc(p_txn_to_date ) ))
141 and cdl.line_type = 'R'
142 and nvl(cdl.resource_accumulated_flag, 'N') <> 'Y') ;
143
144 cursor IsBurdenDistributed is
145 select 'Not Burden Distributed'
146 from dual
147 where exists (
148 select ei.expenditure_item_id
149 from pa_expenditure_items_all ei,
150 pa_project_types_all pt,
151 pa_tasks t,
152 pa_projects_all p
153 where ei.task_id = t.task_id
154 and t.project_id = p.project_id
155 and t.project_id = p_project_id
156 and p.project_type = pt.project_type
157 and pt.org_id = p.org_id -- Removed NVL for bug#590817 by vvjoshi
158 and pt.burden_cost_flag = 'Y'
159 and ( p_active_flag <> 'A'
160 or (trunc(ei.expenditure_item_date ) <=
161 trunc(p_txn_to_date ) ))) ;
162
163 /*** In part I of archive purge,projects with intercompany transactions are not to be
164 purged. Hence check for the existence of any EIs with cross charge code I or
165 existence of records in pa_draft_invoice_details for the project. ***/
166 /* Bug#2416385 Commented for Phase-3 Archive and Purge
167 cursor IsIntercompany is
168 select 'INTERCOMPANY EIs INV EXISTS'
169 from dual
170 where exists (
171 select ei.expenditure_item_id
172 from pa_expenditure_items_all ei,
173 pa_tasks t
174 where ei.task_id = t.task_id
175 and t.project_id = p_project_id
176 and ei.cc_cross_charge_code = 'I')
177 or exists (
178 select null
179 from pa_draft_invoice_details_all di
180 where di.cc_project_id = p_project_id );
181 */
182
183 cursor IsBorrLentDistributed is
184 select 'NOT BL DISTRIBUTED'
185 from dual
186 where exists (
187 select ei.expenditure_item_id
188 from pa_expenditure_items_all ei
189 where ei.project_id = p_project_id
190 and ( p_active_flag <> 'A'
191 or trunc(ei.expenditure_item_date) <= trunc(p_txn_to_date) )
192 and ei.cc_cross_charge_code = 'B'
193 and ei.cc_bl_distributed_code <> 'Y') ;
194
195 cursor IsCCDLInterfaced (P_User_Source_Name IN VARCHAR2) is
196 select 'NOT Transferred and Accepted in other system'
197 from dual
198 where exists (
199 select ei.expenditure_item_id
200 from pa_expenditure_items_all ei,
201 pa_cc_dist_lines_all ccdl
202 where ei.expenditure_item_id = ccdl.expenditure_item_id
203 and ei.project_id = p_project_id
204 and ( p_active_flag <> 'A'
205 or (trunc(ei.expenditure_item_date ) <=
206 trunc(p_txn_to_date ) ))
207 and (ccdl.transfer_status_code not in ( 'A','V')
208 or exists (select 1
209 from xla_events xe
210 where xe.event_id = ccdl.acct_event_id
211 and xe.event_status_code <> 'P'
212 and xe.process_status_code <> 'P')));
213
214 /* Commented below code for R12 changes.
215 or exists (select reference26
216 from pa_gl_interface gl
217 where
218 gl.user_je_source_name || '' = P_User_Source_Name
219 and gl.reference26 = ccdl.gl_batch_name
220 and ((gl.Status NOT LIKE 'W%'
221 and gl.Status <> 'NEW'
222 and gl.Status <> 'PROCESSED')
223 or
224 gl.Status = 'NEW')))) ;
225
226 */
227
228 cursor IsDestProjectType is
229 select 'This project is defined as a destination project in project type'
230 from dual
231 where exists (
232 select project_type
233 from pa_project_types
234 where burden_sum_dest_project_id = p_project_id
235 );
236
237 /*******************************************************************************
238 project related supplier invoices pending for transfer is handled as part of
239 capital check. Now check is done for successfully interfaced VI, if eligible
240 for discounts, fully paid or not.
241 This check is necessary as discount is generated during payment of an invoice
242 and if main invoice is purged before payment, discount transaction will not
243 find the parent record during transfer to PA.
244 *******************************************************************************/
245 CURSOR IsVIPaymentPendg IS
246 SELECT 'VENDOR INVOICE NOT FULLY PAID'
247 FROM dual
248 WHERE EXISTS ( SELECT aid.invoice_id
249 FROM ap_invoices_all ai,
250 ap_invoice_distributions_all aid
251 -- bug 2404115 WHERE ai.project_id = p_project_id
252 WHERE aid.project_id = p_project_id
253 AND nvl(ai.invoice_amount,0) <> 0 /* Bug 5063560 */
254 AND ai.invoice_id = aid.invoice_id
255 AND ai.invoice_type_lookup_code <> 'EXPENSE REPORT'
256 AND (p_active_flag <> 'A' or
257 -- bug 2404115 ai.expenditure_item_date < p_txn_to_date)
258 trunc(aid.expenditure_item_date) <= trunc(p_txn_to_date))
259 AND aid.pa_addition_flag = 'Y'
260 AND nvl(ai.payment_status_flag,'N') <> 'Y'
261 AND nvl(aid.reversal_flag, 'N') <> 'Y' /* 4065283 */
262 /* Bug#2407614. For all the supplier invoices check if payment has been done.
263 AND exists (SELECT NULL
264 FROM ap_payment_schedules_all aps
265 WHERE aps.invoice_id = ai.invoice_id
266 AND nvl(aps.discount_amount_available, 0) > 0
267 AND nvl(aps.amount_remaining, 0 ) > 0
268 ) */
269 );
270
271 /* bug 2396427. Inter project receiver project check. */
272 /* Bug#2416385 Commented for Phase-3 Archive and Purg
273 cursor ISInterPrjRecvPrj IS
274 select 'This is an Inter-Project Receiver Project'
275 from dual
276 where exists ( select NULL
277 from pa_tasks pt
278 where pt.project_id = p_project_id
279 and pt.receive_project_invoice_flag = 'Y'
280 and exists ( select NULL
281 from pa_project_customers ppc
282 where ppc.receiver_task_id = pt.task_id )
283 );
284 */
285 /* Bug# 2405565. Check whether discount lines are interfaced to projects. */
286
287 cursor ISDistLinesIntfed IS
288 select 'Discount Not Interfaced'
289 from dual
290 where exists (
291 select null from ap_invoices ai,
292 ap_invoice_distributions aid,
293 -- ap_invoice_payments pay --R12 change
294 ap_payment_hist_dists paydist
295 where aid.project_id = p_project_id
296 and ai.invoice_id = aid.invoice_id
297 -- and paydist.invoice_id = ai.invoice_id -- R12 change
298 -- and pay.discount_taken <> 0 -- R12 change
299 and paydist.invoice_distribution_id = aid.invoice_distribution_id
300 and paydist.amount <> 0
301 and ai.invoice_type_lookup_code <> 'EXPENSE REPORT'
302 and paydist.pay_dist_lookup_code = 'DISCOUNT'
303 and paydist.pa_addition_flag = 'N'
304 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 4468796 */
305 /* --Commented for R12
306 and not exists
307 ( select 'X' from pa_cost_distribution_lines cdl
308 where cdl.system_reference2=to_char(aid.invoice_id)
309 and cdl.system_reference3=to_char(aid.distribution_line_number)
310 and cdl.system_reference4=to_char(pay.invoice_payment_id)
311 )
312 */
313 );
314
315 /* Bug 2503876. Organization forecast Dummy projects should not be purged */
316
317 cursor ISOrgForecastProject IS
318 select 'Organization Forecast Project'
319 from dual
320 where exists (
321 select null from pa_projects pj,
322 pa_project_types pt
323 where pj.project_id = p_project_id
324 and pj.project_type = pt.project_type
325 and pt.org_project_flag = 'Y'
326 );
327
328 /* Bug 2553535. Unassigned forecast items should be purged using the
329 new concurrent process PRC:Archive/Purge Unassinged forecast items */
330
331 cursor IsUnassignedTimeType IS
332 select 'Unassigned Time Project Type'
333 from dual
334 where exists ( select pt.project_type
335 from pa_project_types pt,
336 pa_projects p
337 where p.project_id = p_project_id
338 and pt.project_type = p.project_type
339 and nvl(pt.unassigned_time, 'N') = 'Y' );
340
341 /* Check for project related iExpense transactions Pending for import */
342 /* This portion has been done dynamically since pa_interfaced_flag is not
343 added to ap_expense_report_lines table in release 11.5.3 bug 2695986
344 cursor IsIexpenseTxnsPending IS
345 select 'Iexpense Transactions Pending'
346 from dual
347 where exists (select h.report_header_id
348 from ap_expense_report_headers h,
349 ap_expense_report_lines l
353 and ( p_active_flag <> 'A'
350 where h.report_header_id = l.report_header_id
351 and h.source <> 'Oracle Project Accounting'
352 and l.project_id = p_project_id
354 or (trunc(l.expenditure_item_date ) <=
355 trunc(p_txn_to_date ) ))
356 and nvl(l.pa_interfaced_flag,'N') <> 'Y'); */
357 /* Bug2767419. Checking for the existence of iexpense txns pending
358 for transfer to PA. Instead of using pa_interfaced_flag of ap_exp_lines
359 the check is done thru ap_invoice tables. */
360 cursor IsIexpenseTxnsPending IS
361 select 'Iexpense Transactions Pending'
362 from dual
363 where exists (
364 select null
365 from ap_expense_report_headers h,
366 ap_expense_report_lines l
367 where l.project_id = p_project_id
368 and (p_active_flag <> 'A'
369 or (trunc(l.expenditure_item_date ) <=
370 trunc(p_txn_to_date )))
371 and l.report_header_id = h.report_header_id
372 and h.source <> 'Oracle Project Accounting'
373 and not exists (
374 select null
375 from ap_invoice_distributions d
376 where d.invoice_id = h.vouchno
377 and d.pa_addition_flag IN ('Z','T','E','Y')));
378
379
380 /* Bug 2610276. Added Expense report payment check */
381 cursor IsERPaymentPendg IS
382 SELECT 'EXPENSE REPORTS NOT FULLY PAID'
383 FROM dual
384 WHERE EXISTS ( SELECT aid.invoice_id
385 FROM ap_invoices_all ai,
386 ap_invoice_distributions_all aid
387 WHERE aid.project_id = p_project_id
388 AND ai.invoice_id = aid.invoice_id
389 AND ai.invoice_type_lookup_code = 'EXPENSE REPORT'
390 AND (p_active_flag <> 'A' or
391 trunc(aid.expenditure_item_date) <=
392 trunc(p_txn_to_date))
393 AND nvl(ai.payment_status_flag,'N') <> 'Y' );
394
395 l_err_stack_old VARCHAR2(2000);
396 l_err_stack VARCHAR2(2000);
397 l_err_stage VARCHAR2(500);
398 l_err_code NUMBER ;
399 l_dummy VARCHAR2(500);
400 l_user_source_name GL_JE_SOURCES.USER_JE_SOURCE_NAME%TYPE;
401 --l_used_in_OTL BOOLEAN := FALSE; Commented for Bug 2726711
402 l_purgeable BOOLEAN := FALSE; /* Added for Bug 2726711 */
403
404 l_source_project NUMBER;
405 l_target_project NUMBER;
406 l_offset_project NUMBER;
407
408 V_CursorID INTEGER;
409 V_Stmt VARCHAR2(500);
410 V_Delete_Allowed VARCHAR2(1);
411 V_Dummy INTEGER;
412 l_igc_exists NUMBER;
413 l_dummy_num NUMBER;
414
415 BEGIN
416 l_err_code := 0 ;
417 l_err_stack_old := x_err_stack;
418 pa_debug.debug(' -- Performing costing validation for project '||
419 to_char(p_project_id));
420 -- Check if there are expenditure items that are not Cost
421 -- Distributed
422
423 Open IsCostDistributed ;
424 Fetch IsCostDistributed into l_dummy ;
425 If l_dummy is not null then
426 fnd_message.set_name('PA', 'PA_ARPR_NOT_COSTED');
427 fnd_msg_pub.add;
428 l_err_code := 10 ;
429
430 l_err_stage := 'After open cursor IsCostDistributed' ;
431 l_err_stack := l_err_stack ||
432 ' ->After open cursor IsCostDistributed' ;
433 pa_debug.debug(' * Uncosted items exists for project '||
434 to_char(p_project_id));
435 End If;
436 close IsCostDistributed;
437 l_dummy := NULL;
438
439 -- Check if there are expenditure items that are not Cost
440 -- Distributed
441
442 Open IsTransImported ;
443 Fetch IsTransImported into l_dummy ;
444 If l_dummy is not null then
445 fnd_message.set_name('PA', 'PA_ARPR_NOT_TR_IMPORTED');
446 fnd_msg_pub.add;
447 l_err_code := 10 ;
448
449 l_err_stage := 'After open cursor IsTransImported' ;
450 l_err_stack := l_err_stack || ' ->After open cursor IsTransImported' ;
451 pa_debug.debug(' * Items that are not revenue generated exists for project '||to_char(p_project_id));
452 End If;
453 close IsTransImported;
454 l_dummy := NULL;
455
456 -- Check if there are CDLs that are not accepted in the other
457 -- application
458
459 /*
460 * Need the user_je_source_name use in the the IsInterfaced
461 * and IsMRCInterfaced cursors. Added the parameter to Open
462 * IsInterfaced and IsMRCInterfaced cursors.
463 */
464 SELECT user_je_source_name
465 INTO l_user_source_name
466 FROM GL_Je_Sources
467 WHERE je_source_name='Project Accounting';
468
472 fnd_message.set_name('PA', 'PA_ARPR_NOT_INFCED');
469 Open IsInterfaced(l_User_Source_Name) ;
470 Fetch IsInterfaced into l_dummy ;
471 If l_dummy is not null then
473 fnd_msg_pub.add;
474 l_err_code := 10 ;
475
476 l_err_stage := 'After transfer check' ;
477 l_err_stack := l_err_stack || ' ->After transfer check' ;
478 pa_debug.debug(' * Not all costs are transferred for the project '||to_char(p_project_id));
479 End If;
480 close IsInterfaced;
481
482 /* Check if there are MRC CDLs that are not accepted in the other
483 * application. New for 11.0 due to MRC.
484 */
485
486 l_dummy := NULL;
487
488 /* Commented below code for R12 changes.
489 Open IsMRCInterfaced(l_User_Source_Name) ;
490 Fetch IsMRCInterfaced into l_dummy ;
491 If l_dummy is not null then
492 fnd_message.set_name('PA', 'PA_MRC_ARPR_NOT_INFCED');
493 fnd_msg_pub.add;
494 l_err_code := 10 ;
495 l_err_stage := 'After transfer check' ;
496 l_err_stack := l_err_stack || ' ->After transfer check' ;
497 pa_debug.debug(' * Not all MRC costs are transferred for the project ' || to_char(p_project_id));
498 End If;
499 close IsMRCInterfaced;
500 */
501
502
503 /* End of new section for MRC CDLs */
504
505 l_dummy := NULL;
506
507 -- Check if there are CDLs whose costs are not accumulated
508
509 /*** -- This section of the code has been commented out due to the request from
510 -- some customers who do not run summarization. So if the user wants to
511 -- check if summarization has been run the this section of the code needs
512 -- to be put in the client-extension.
513
514 Open IsResAccumulated ;
515 Fetch IsResAccumulated into l_dummy ;
516 If l_dummy is not null then
517 fnd_message.set_name('PA', 'PA_ARPR_NOT_RES_ACCUM');
518 fnd_msg_pub.add;
519 l_err_code := 10 ;
520
521 l_err_stage := 'After resource accumulation check ' ;
522 l_err_stack := l_err_stack || ' ->After resource accumulation check ' ;
523 pa_debug.debug(' * Not all costs are accumulated for the project '||to_char(p_project_id));
524 End If;
525 close IsResAccumulated;
526 l_dummy := NULL;
527
528 ***/
529 -- Check if total burden cost distribution is run for the costs
530 /*
531 Open IsBurdenDistributed ;
532 Fetch IsBurdenDistributed into l_dummy ;
533 If l_dummy is not null then
534 fnd_message.set_name('PA', 'PA_ARPR_NOT_BRDN_DIST');
535 fnd_msg_pub.add;
536 l_err_code := 10 ;
537
538 l_err_stage := 'After Burden Cost Dist. check ' ;
539 l_err_stack := l_err_stack || ' ->After Burden Cost Dist. check ' ;
540 pa_debug.debug(' * Not all costs are burden distributed for project '||to_char(p_project_id));
541 End If;
542 close IsBurdenDistributed;
543 l_dummy := NULL;
544 */
545
546 /* Bug#2416385 Commented for Phase-3 Archive and Purg
547 -- Check there are no intercompany tranactions
548
549 Open IsIntercompany;
550 Fetch IsIntercompany into l_dummy;
551 If l_dummy is not null then
552 fnd_message.set_name('PA','PA_ARPR_IC_TRX_EXISTS');
553 fnd_msg_pub.add;
554 l_err_code := 10;
555 l_err_stage := 'After intercompany check ';
556 l_err_stack := l_err_stack|| ' -> After intercompany check';
557 pa_debug.debug(' * Intercompany transactions exists for the project '||to_char(p_project_id));
558 End If;
559 Close IsIntercompany;
560 l_dummy := NULL;
561 */
562
563 -- Check if all the eligible borrowed lent EIs are BL distributed.
564
565 Open IsBorrLentDistributed;
566 Fetch IsBorrLentDistributed into l_dummy;
567 If l_dummy is not null then
568 fnd_message.set_name('PA','PA_ARPR_NOT_BL_DIST');
569 fnd_msg_pub.add;
570 l_err_code := 10;
571 l_err_stage := 'After Borrowed and Lent Dist check ';
572 l_err_stack := l_err_stack|| ' -> After Borrwed and Lent Dist Check ';
573 pa_debug.debug(' * Eligible Borrowed and Lent EIs are not BL distributed for project '||to_char(p_project_id));
574 End If;
575 Close IsBorrLentDistributed;
576 l_dummy := NULL;
577
578 -- Check if BL cost is transferred to other applications.
579
580 Open IsCCDLInterfaced(l_User_Source_Name) ;
581 Fetch IsCCDLInterfaced into l_dummy ;
582 If l_dummy is not null then
583 fnd_message.set_name('PA', 'PA_ARPR_BL_NOT_IFCD');
584 fnd_msg_pub.add;
585 l_err_code := 10 ;
586 l_err_stage := 'After BL transfer check' ;
587 l_err_stack := l_err_stack || ' ->After BL transfer check' ;
588 pa_debug.debug(' * Not all BL costs are transferred for the project '||to_char(p_project_id));
589 End If;
590 Close IsCCDLInterfaced;
591 l_dummy := NULL;
592
593 -- Check if the project is defined as a destination project in any project type
594
595 Open IsDestProjectType;
596 Fetch IsDestProjectType into l_dummy;
597 If l_dummy is not null then
598 fnd_message.set_name('PA', 'PA_ARPR_DEST_PRJ_TYPE');
599 fnd_msg_pub.add;
603 pa_debug.debug(' * The project '||to_char(p_project_id)||' is defined as a destination project in project type check ');
600 l_err_code := 10 ;
601 l_err_stage := 'After destination project in project type check' ;
602 l_err_stack := l_err_stack || ' ->After destination project in project type check' ;
604 End If;
605 Close IsDestProjectType;
606 l_dummy := NULL;
607
608 --Check to see if the project has been used in OTL
609 /* Commented for Bug 2726711
610 Modified call to ProjectTaskPurgeable to validate if the project is purgable
611 PA_OTC_API.ProjectTaskUsed( p_search_attribute => 'PROJECT',
612 p_search_value => p_project_id,
613 x_used => l_used_in_OTL );*/
614
615 PA_OTC_API.ProjectTaskPurgeable /* Add for bug 2726711 */
616 (P_Search_Attribute => 'PROJECT',
617 P_Search_Value => p_project_id,
618 X_Purgeable => l_purgeable);
619
620 --IF l_used_in_OTL Commented for Bug 2726711
621 IF NOT l_purgeable
622 THEN
623 fnd_message.set_name('PA', 'PA_ARPR_OTL_NOT_IFCD');
624 fnd_msg_pub.add;
625 l_err_code := 10;
626 l_err_stage := 'After OTL check' ;
627 l_err_stack := l_err_stack || ' ->After OTL check' ;
628 pa_debug.debug(' * OTL records exists for the project '||to_char(p_project_id));
629 END IF;
630
631 pa_debug.debug(' -- Performing Allocations validation for project '||
632 to_char(p_project_id));
633
634 pa_purge_validate_costing.Validate_Allocations(p_proj_id => p_project_id,
635 x_source => l_source_project,
636 x_target => l_target_project,
637 x_offset => l_offset_project);
638 IF l_source_project = 1 THEN
639 fnd_message.set_name('PA', 'PA_ARPR_SOURCE_PROJECT');
640 fnd_msg_pub.add;
641 l_err_code := 10 ;
642
643 l_err_stage := 'After checking for source project' ;
644 l_err_stack := l_err_stack ||
645 ' ->After checking for source project' ;
646 pa_debug.debug(' * There exists a rule with run status not as RS which contains
647 the project '||to_char(p_project_id)|| 'as source project');
648 END IF;
649
650 IF l_target_project = 1 THEN
651 fnd_message.set_name('PA', 'PA_ARPR_TARGET_PROJECT');
652 fnd_msg_pub.add;
653 l_err_code := 10 ;
654
655 l_err_stage := 'After checking for tartget project' ;
656 l_err_stack := l_err_stack ||
657 ' ->After checking for target project' ;
658 pa_debug.debug(' * There exists a rule with run status not as RS which contains
659 the project '||to_char(p_project_id)|| 'as target project');
660 END IF;
661
662 IF l_offset_project = 1 THEN
663 fnd_message.set_name('PA', 'PA_ARPR_OFFSET_PROJECT');
664 fnd_msg_pub.add;
665 l_err_code := 10 ;
666
667 l_err_stage := 'After checking for offset project' ;
668 l_err_stack := l_err_stack ||
669 ' ->After checking for offset project' ;
670 pa_debug.debug(' * There exists a rule with run status not as RS which contains
671 the project '||to_char(p_project_id)|| 'as offset project');
672 END IF;
673
674 /* checking for the project in property manager */
675 IF ( NOT PNP_OTH_PROD.delete_project (p_project_id) ) then
676 fnd_message.set_name('PA', 'PA_ARPR_PROJ_INUSE_PROP_MGR');
677 fnd_msg_pub.add;
678 l_err_code := 10 ;
679
680 l_err_stage := 'After checking for the project in property manager';
681 l_err_stack := l_err_stack ||
682 ' ->After checking for the project in property manager';
683 pa_debug.debug(' * The project '||to_char(p_project_id)|| 'is in use by Property Manager module');
684 END IF;
685
686
687 /* checking for the project in contract commitments */
688 /* commneted the code and added the modified code below since the arguments are changed in the
689 call to the procedure IGC_CC_PROJECTS_PKG.delete_project for bug#2272487
690 IF ( NOT IGC_CC_PROJECTS_PKG.delete_project (p_project_id) ) then
691 fnd_message.set_name('PA', 'PA_ARPR_PROJ_INUSE_CNTR_CMTS');
692 fnd_msg_pub.add;
693 l_err_code := 10 ;
694
695 l_err_stage := 'After checking for the project in contract commitments';
696 l_err_stack := l_err_stack ||
697 ' ->After checking for the project in contract commitments';
698 pa_debug.debug(' * The project '||to_char(p_project_id)|| 'is in use by Contract Commitments');
699 END IF;
700 */
701
702 /* checking for the project in contract commitments */
703 select count(*) into l_igc_exists
704 from fnd_product_installations
705 where application_id = 8407
706 and status <> 'N';
707
708 IF ( l_igc_exists > 0 ) then
709
710 V_CursorID := DBMS_SQL.OPEN_CURSOR;
711
712 V_Stmt := ' begin
713 IGC_CC_PROJECTS_PKG.delete_project (:project_id, :delete_allowed);
714 end; ';
715
716 DBMS_SQL.PARSE(V_CursorID, V_Stmt, DBMS_SQL.v7);
717
718 DBMS_SQL.BIND_VARIABLE(V_CursorID, ':project_id', p_project_id, 20);
722
719 DBMS_SQL.BIND_VARIABLE(V_CursorID, ':delete_allowed', V_Delete_Allowed, 1);
720
721 V_Dummy := DBMS_SQL.EXECUTE(V_CursorID);
723 DBMS_SQL.VARIABLE_VALUE(V_CursorID, ':delete_allowed', V_Delete_Allowed);
724
725 DBMS_SQL.CLOSE_CURSOR(V_CursorID);
726
727 IF ( V_Delete_Allowed = 'N' ) then
728 fnd_message.set_name('PA', 'PA_ARPR_PROJ_INUSE_CNTR_CMTS');
729 fnd_msg_pub.add;
730 l_err_code := 10 ;
731
732 l_err_stage := 'After checking for the project in contract commitments';
733 l_err_stack := l_err_stack ||
734 ' ->After checking for the project in contract commitments';
735 pa_debug.debug(' * The project '||to_char(p_project_id)|| 'is in use by Contract Commitments');
736 END IF;
737
738 END IF;
739 -- Check supplier invoices eligible for discounts are fully paid.
740
741 Open IsVIPaymentPendg;
742 Fetch IsVIPaymentPendg into l_dummy ;
743 If l_dummy is not null then
744 fnd_message.set_name('PA', 'PA_ARPR_VI_DIS_NOT_PAID');
745 fnd_msg_pub.add;
746 l_err_code := 10 ;
747 l_err_stage := 'After VI discount check' ;
748 l_err_stack := l_err_stack || ' ->After VI discount check' ;
749 pa_debug.debug(' * Supplier invoice eligible for discount not fully paid for project '||to_char(p_project_id));
750 End If;
751 Close IsVIPaymentPendg;
752 l_dummy := NULL;
753
754 /* Bug#2416385 Commented for Phase-3 Archive and Purg
755 Open ISInterPrjRecvPrj;
756 Fetch ISInterPrjRecvPrj into l_dummy;
757 If l_dummy is not null then
758 fnd_message.set_name('PA','PA_ARPR_IP_RCVR_PROJ');
759 fnd_msg_pub.add;
760 l_err_code := 10 ;
761 l_err_stage := 'After IP receiver project check' ;
762 l_err_stack := l_err_stack || ' ->After IP receiver project check' ;
763 pa_debug.debug(' * This is an inter-project receiver project');
764 End If;
765 Close ISInterPrjRecvPrj;
766 l_dummy := NULL;
767 */
768 Open ISDistLinesIntfed;
769 Fetch ISDistLinesIntfed into l_dummy;
770 If l_dummy is not null then
771 fnd_message.set_name('PA','PA_ARPR_VI_DISC_NOT_IFCD');
772 fnd_msg_pub.add;
773 l_err_code := 10 ;
774 l_err_stage := 'After Vendor invoice discount interface check' ;
775 l_err_stack := l_err_stack || ' ->After Vendor invoice discount interface check' ;
776 pa_debug.debug(' * Supplier invoice discount lines not interfaced');
777 End If;
778 Close ISDistLinesIntfed;
779 l_dummy := NULL;
780
781 Open ISOrgForecastProject;
782 Fetch ISOrgForecastProject into l_dummy;
783 If l_dummy is not null then
784 fnd_message.set_name('PA','PA_ARPR_ORG_FC_DUM_PRJ');
785 fnd_msg_pub.add;
786 l_err_code := 10 ;
787 l_err_stage := 'After organization forecast project check' ;
788 l_err_stack := l_err_stack || ' ->After organization forecast project check' ;
789 pa_debug.debug(' * This is an organization forecast project');
790 End If;
791 Close ISOrgForecastProject;
792 l_dummy := NULL;
793
794 Open IsUnassignedTimeType;
795 Fetch IsUnassignedTimeType into l_dummy;
796 If l_dummy is not null then
797 fnd_message.set_name('PA','PA_ARPR_UNASS_PROJ_TYPE');
798 fnd_msg_pub.add;
799 l_err_code := 10 ;
800 l_err_stage := 'After Unassigned Time project type check' ;
801 l_err_stack := l_err_stack || ' ->After Unassigned Time project type check' ;
802 pa_debug.debug(' * This is an unassigned time project');
803 End If;
804 Close IsUnassignedTimeType;
805 l_dummy := NULL;
806
807 /* bug # 2695986 */
808 /* bug 2767419
809 select to_number(replace(release_name,'.'))
810 into l_dummy_num
811 from fnd_product_groups;
812
813 IF l_dummy_num > 1154 THEN
814 V_Stmt := 'select ' || '''Iexpense Transactions Pending''' ||
815 ' from dual '||
816 ' where exists (select h.report_header_id '||
817 ' from ap_expense_report_headers h, '||
818 ' ap_expense_report_lines l '||
819 ' where h.report_header_id = l.report_header_id '||
820 ' and h.source <> '||'''Oracle Project Accounting''' ||
821 ' and l.project_id = :p_proj_id '||
822 ' and ( :p_act_flag <> '|| '''A''' ||
823 ' or (trunc(l.expenditure_item_date ) <= trunc(:p_txn_date ) )) '||
824 ' and nvl(l.pa_interfaced_flag,'||'''N'''||') <> '||'''Y'''||')';
825
826
827 V_CursorID := DBMS_SQL.OPEN_CURSOR;
828 DBMS_SQL.PARSE(V_CursorID, V_Stmt, DBMS_SQL.v7);
829 DBMS_SQL.BIND_VARIABLE(V_CursorID, ':p_act_flag',p_active_flag);
830 DBMS_SQL.BIND_VARIABLE(V_CursorID, ':p_proj_id',p_project_id);
831 DBMS_SQL.BIND_VARIABLE(V_CursorID, ':p_txn_date',p_txn_to_date);
832 V_Dummy := DBMS_SQL.EXECUTE(V_CursorID);
833
834 IF (DBMS_SQL.FETCH_ROWS(V_CursorID) > 0 ) THEN
835 fnd_message.set_name('PA','PA_ARPR_IEXP_TXNS_IMP_PEND');
836 fnd_msg_pub.add;
837 l_err_code := 10 ;
838 l_err_stage := 'After Iexpense transactions transfer check' ;
839 l_err_stack := l_err_stack || ' ->After Iexpense transactions transfer check' ;
840 pa_debug.debug(' * Project related Iexpense transactions are yet to be imported to projects');
841 END IF;
842
843 DBMS_SQL.CLOSE_CURSOR(V_CursorID);
844 END IF;
845 */
846
847 Open IsIexpenseTxnsPending;
848 Fetch IsIexpenseTxnsPending into l_dummy;
849 If l_dummy is not null then
850 fnd_message.set_name('PA','PA_ARPR_IEXP_TXNS_IMP_PEND');
851 fnd_msg_pub.add;
852 l_err_code := 10 ;
853 l_err_stage := 'After Iexpense transactions transfer check' ;
854 l_err_stack := l_err_stack || ' ->After Iexpense transactions transfer check' ;
855 pa_debug.debug(' * Project related Iexpense transactions are yet to be imported to projects');
856 End If;
857 Close IsIexpenseTxnsPending;
858 l_dummy := NULL;
859
860 Open IsERPaymentPendg;
861 Fetch IsERPaymentPendg into l_dummy ;
862 If l_dummy is not null then
863 fnd_message.set_name('PA', 'PA_ARPR_ER_NOT_PAID');
864 fnd_msg_pub.add;
865 l_err_code := 10 ;
866 l_err_stage := 'After ER Payment check' ;
867 l_err_stack := l_err_stack || ' ->After ER Payment check' ;
868 pa_debug.debug(' * Expense reports are yet to be paid for the project '||to_char(p_project_id));
869 End If;
870 Close IsERPaymentPendg;
871 l_dummy := NULL;
872
873 x_err_code := l_err_code ;
874 x_err_stage := l_err_stage ;
875 x_err_stack := l_err_stack_old ;
876
877 EXCEPTION
878 WHEN PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error then
879 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
880
881 WHEN OTHERS THEN
882 x_err_stage := l_err_stage ;
883 pa_debug.debug('Error Procedure Name := PA_PURGE_VALIDATE_COSTING.VALIDATE_COSTING' );
884 pa_debug.debug('Error stage is '||l_err_stage );
885 pa_debug.debug('Error stack is '||l_err_stack );
886 pa_debug.debug(SQLERRM);
887 PA_PROJECT_UTILS2.g_sqlerrm := SQLERRM ;
888
889 RAISE PA_PROJECT_UTILS2.PA_Arch_Pur_Subroutine_Error ;
890
891 END Validate_Costing ;
892
893
894 PROCEDURE Validate_Allocations ( p_proj_id IN NUMBER,
895 x_source OUT NOCOPY NUMBER,
896 x_target OUT NOCOPY NUMBER,
897 x_offset OUT NOCOPY NUMBER)
898 IS
899
900 /* Modified these 2 cursors and added the modified code below for the bug#2272487 */
901
902 cursor c_source is
903 select 1
904 from dual
905 where exists ( select null
906 from pa_alloc_run_sources pars,
907 pa_alloc_runs par
908 where par.rule_id = pars.rule_id
909 and pars.project_id = p_proj_id
910 and par.run_id = pars.run_id
911 /* and par.run_status <> 'RS' commented for bug#2446122 */
912 and par.run_status not in ( 'RS', 'RV') /* Added for bug#2446122 */
913 );
914
915 cursor c_target is
916 select 1
917 from dual
918 where exists ( select null
919 from pa_alloc_run_targets part,
920 pa_alloc_runs par
921 where par.rule_id = part.rule_id
922 and part.project_id = p_proj_id
923 and par.run_id = part.run_id
924 /* and par.run_status <> 'RS' commented for bug#2446122 */
925 and par.run_status not in ( 'RS', 'RV') /* Added for bug#2446122 */
926 );
927
928 /* added the check for offsets for bug#2272487 */
929
930 cursor c_offset is
931 select 1
932 from dual
933 where exists ( select null
934 from pa_alloc_rules_all para,
935 pa_alloc_runs par
936 where par.rule_id = para.rule_id
937 and para.offset_project_id = p_proj_id
938 /* and par.run_status <> 'RS' commented for bug#2446122 */
939 and par.run_status not in ( 'RS', 'RV') /* Added for bug#2446122 */
940 );
941
942
943
944 l_source NUMBER DEFAULT 0;
945 l_target NUMBER DEFAULT 0;
946 l_offset NUMBER DEFAULT 0;
947
948 BEGIN
949
950 -- Check if there is any allocation rule existing with status not as
951 -- Released Successfully (RS) having the input project_id
952
953 open c_source;
954 fetch c_source into l_source;
955 close c_source;
956
957 open c_target;
958 fetch c_target into l_target;
959 close c_target;
960
961 open c_offset;
962 fetch c_offset into l_offset;
963 close c_offset;
964
965 x_source := l_source;
966 x_target := l_target;
967 x_offset := l_offset;
968
969 END Validate_Allocations;
970
971 END ;
[Home] [Help]