[Home] [Help]
PACKAGE BODY: APPS.PA_RETENTION_UTIL
Source
1 PACKAGE BODY pa_retention_util as
2 /* $Header: PAXIRUTB.pls 120.2 2005/08/19 17:14:48 mwasowic noship $ */
3
4 /*----------------------------------------------------------------------------------------+
5 | Function : IsBillingCycleQualified |
6 | Purpose : |
7 | Parameters : |
8 | ================================================================================== |
9 | Name Mode Description |
10 | ================================================================================== |
11 | p_project_id IN Project Id |
12 | p_task_id IN Task ID |
13 | p_bill_thru_date IN Bill thru Date |
14 | p_billing_cycle_id IN Billing cycle ID |
15 | ================================================================================== |
16 +----------------------------------------------------------------------------------------*/
17
18
19 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
20
21 FUNCTION IsBillingCycleQualified(p_project_id IN NUMBER,
22 p_task_id IN NUMBER,
23 P_bill_thru_date IN DATE,
24 p_billing_cycle_id IN NUMBER) RETURN VARCHAR2 IS
25
26
27 QualifiedFlag VARCHAR2(1) := 'N';
28 Last_Bill_thru_date Date ;
29 TmpBillingDate Date;
30
31 BEGIN
32
33
34 -- Finding the last bill thru date
35 IF g1_debug_mode = 'Y' THEN
36 pa_retention_util.write_log('Entering IsBillingCycleQualified Function ');
37 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'Billing Cycle Id : ' || p_billing_cycle_id );
38 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'Bill Thru Date : ' || to_char(p_bill_thru_date));
39 END IF;
40
41 BEGIN
42
43 -- Find the maximum bill through date from invoices table for retention invoices
44
45 IF NVL(p_task_id,0) = 0 THEN
46
47 SELECT MAX(bill_through_date)
48 INTO last_bill_thru_date
49 FROM pa_draft_invoices
50 WHERE project_id = p_project_id
51 AND retention_invoice_flag = 'Y';
52
53 IF g1_debug_mode = 'Y' THEN
54 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'Project Level Last Bill Thru Date : '
55 || to_char(last_bill_thru_date));
56 END IF;
57 ELSE
58 SELECT MAX(di.bill_through_date)
59 INTO last_bill_thru_date
60 FROM pa_draft_invoices di
61 WHERE di.project_id = p_project_id
62 AND di.retention_invoice_flag = 'Y'
63 AND EXISTS(
64 SELECT null FROM pa_draft_invoice_items dii
65 WHERE dii.draft_invoice_num = di.draft_invoice_num
66 AND dii.project_id = di.project_id
67 AND dii.task_id = p_task_id);
68 IF g1_debug_mode = 'Y' THEN
69 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'Task Level Last Bill Thru Date : '
70 || to_char(last_bill_thru_date));
71 END IF;
72 END IF;
73
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 last_bill_thru_date := NULL;
77
78 END;
79
80 IF g1_debug_mode = 'Y' THEN
81 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'Last Bill Thru Date : ' || to_char(last_bill_thru_date));
82 END IF;
83
84 IF last_bill_thru_date IS NULL THEN
85
86 IF g1_debug_mode = 'Y' THEN
87 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'Last Bill Thru Date IS NULL ');
88 END IF;
89
90 BEGIN
91
92 IF NVL(p_task_id,0) = 0 THEN
93
94 SELECT MIN(di.bill_through_date)
95 INTO last_bill_thru_date
96 FROM pa_draft_invoices di
97 WHERE EXISTS (
98 SELECT null FROM pa_draft_invoice_items dii
99 WHERE dii.draft_invoice_num = di.draft_invoice_num
100 AND dii.project_id = di.project_id
101 AND di.project_id = p_project_id
102 AND dii.invoice_line_type = 'RETENTION');
103
104 IF g1_debug_mode = 'Y' THEN
105 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'II Project Level Last Bill Thru Date : '
106 || to_char(last_bill_thru_date));
107 END IF;
108
109 ELSE
110 SELECT MIN(di.bill_through_date)
111 INTO last_bill_thru_date
112 FROM pa_draft_invoices di
113 WHERE EXISTS (
114 SELECT null FROM pa_draft_invoice_items dii
115 WHERE dii.draft_invoice_num = di.draft_invoice_num
116 AND dii.project_id = di.project_id
117 AND dii.task_id = p_task_id
118 AND dii.invoice_line_type = 'RETENTION');
119
120 IF g1_debug_mode = 'Y' THEN
121 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'II Task Level Last Bill Thru Date : '
122 || to_char(last_bill_thru_date));
123 END IF;
124 END IF;
125
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128
129 IF g1_debug_mode = 'Y' THEN
130 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'No Data Found ');
131 END IF;
132 QualifiedFlag := 'N';
133
134 END;
135
136 END IF;
137
138 IF g1_debug_mode = 'Y' THEN
139 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'Calling PA_Billing_Cycles_Pkg.Get_Billing_Date');
140 END IF;
141
142 TmpBillingDate := PA_Billing_Cycles_Pkg.Get_Billing_Date (
143 X_Project_ID =>p_project_id,
144 X_Project_Start_Date =>last_bill_thru_date,
145 X_Billing_Cycle_ID =>p_billing_cycle_id,
146 X_Bill_Thru_Date =>P_Bill_thru_date,
147 X_Last_Bill_Thru_Date =>last_bill_thru_date);
148
149 IF g1_debug_mode = 'Y' THEN
150 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'TmpBillDate : ' || to_char(tmpBillingDate));
151 END IF;
152
153 IF TmpBillingDate <= P_Bill_thru_date THEN
154 IF g1_debug_mode = 'Y' THEN
155 pa_retention_util.write_log('IsBillingCycleQualified: ' || 'Qualified ');
156 END IF;
157 QualifiedFlag := 'Y';
158 ELSE
159 IF g1_debug_mode = 'Y' THEN
160 pa_retention_util.write_log('IsBillingCycleQualified: ' || ' not Qualified ');
161 END IF;
162 QualifiedFlag := 'N';
163
164 END IF;
165
166 Return (QualifiedFlag);
167
168 END IsBillingCycleQualified;
169
170 /*----------------------------------------------------------------------------------------+
171 | Procedure : write_log_message |
172 | Purpose : To write log message as supplied by other processe |
173 | Parameters : |
174 | ================================================================================== |
175 | Name Mode Description |
176 | ================================================================================== |
177 | p_log_message IN Message to be logged |
178 | ================================================================================== |
179 +----------------------------------------------------------------------------------------*/
180
181
182 PROCEDURE Write_log(p_message IN VARCHAR2) IS
183
184 BEGIN
185 IF g1_debug_mode = 'Y' THEN
186 PA_MCB_INVOICE_PKG.log_message('Write_log: ' || p_message);
187 END IF;
188 END Write_Log;
189
190 /*----------------------------------------------------------------------------------------+
191 | Procedure : copy_retention setup |
192 | Purpose : To copy retention setup from project to another project |
193 | (called from forms) OR |
194 | one project customer to other customers of the same project |
195 | (called from OA) |
196 | NOTE : When called from OA the setup of one project-customer is to be copied to |
197 | the same project - different customer |
198 | When called from FORMS, the setup of one project is to be copied to another |
199 | project. |
200 | In this case - if the customers are existing in both projects, then |
201 | a customer-customer copy is made. |
202 | if not, then the setup of primary customer of source project |
203 | is copied to destination project |
204 | Parameters : |
205 | ================================================================================== |
206 | Name Mode Description |
207 | ================================================================================== |
208 | p_fr_project_id IN Source Project Id |
209 | p_to_project_id IN Destination Project ID |
210 | p_fr_customer_id IN Source Customer ID |
211 | p_to_customer_id IN Destination Customer ID |
212 | p_fr_date IN Effective Start Date |
213 | p_to_date IN Effective End Date |
214 | p_call_mode IN Call Mode ('PROJECT', 'CUSTOMER') |
215 | x_return_status OUT Return Status |
216 | x_msg_count OUT Message Count |
217 | x_msg_data OUT Message Data |
218 | ================================================================================== |
219 +----------------------------------------------------------------------------------------*/
220
221 PROCEDURE copy_retention_setup (
222 p_fr_project_id IN NUMBER DEFAULT NULL,
223 p_to_project_id IN NUMBER DEFAULT NULL,
224 p_fr_customer_id IN NUMBER DEFAULT NULL,
225 p_to_customer_id IN NUMBER DEFAULT NULL,
226 p_fr_date IN DATE DEFAULT NULL,
227 p_to_date IN DATE DEFAULT NULL,
228 p_call_mode IN VARCHAR2 DEFAULT 'PROJECT',
229 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
230 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
231 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
232
233
234 /* This cursor selects retention_level_code (source project) of customers existing in both the
235 source and destination project */
236
237 cursor pc_cur (l_fr_project_id number, l_to_project_id number) IS
238 select pc.customer_id, pc.retention_level_code
239 from pa_project_customers pc
240 where pc.project_id = l_fr_project_id
241 and pc.customer_id in (select customer_id from pa_project_customers
242 where project_id = l_to_project_id);
243
244 /* This cursor selects those customer records of destination project which do not have
245 the same customers in the source project
246 These records will not have their retention_level_code updated by the previous cursor */
247
248 cursor no_cust_cur (l_fr_project_id number, l_to_project_id number) IS
249 select pc.customer_id, pc.retention_level_code
250 from pa_project_customers pc
251 where pc.project_id = l_to_project_id
252 and pc.customer_id not in (select customer_id from pa_project_customers
253 where project_id = l_fr_project_id);
254
255 l_to_project_id NUMBER;
256 l_primary_cust_id NUMBER;
257 l_not_update NUMBER;
258 l_retention_level_code VARCHAR2(30);
259 l_insufficient_parameters EXCEPTION;
260
261 l_return_status VARCHAR2(30) := NULL;
262 l_msg_count NUMBER := NULL;
263 l_msg_data VARCHAR2(30) := NULL;
264
265 l_delta NUMBER;
266
267
268 BEGIN
269
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271 x_msg_count := 0;
272
273 /* Check for proper set of parameters
274 Source project_id (p_fr_project_id) is mandatory
275 Either destination project_id (p_to_project_id) (when called from FORMS)
276 OR both source and destination customer_id's (when called from OA) are required */
277
278 --dbms_output.put_line ('in copy retention setup 1');
279 --dbms_output.put_line ('calling calculate date factor ');
280
281 calculate_date_factor(p_fr_project_id => p_fr_project_id,
282 p_to_project_id => p_to_project_id,
283 x_delta => l_delta,
284 x_return_status => l_return_status ,
285 x_msg_count => l_msg_count ,
286 x_msg_data => l_msg_data );
287
288 --dbms_output.put_line ('after calculate date factor ' || l_delta);
289
290 if p_call_mode = 'CUSTOMER' THEN
291
292 --dbms_output.put_line ('in OA');
293 --l_to_project_id := p_fr_project_id;
294
295 update pa_project_customers
296 set retention_level_code = (select retention_level_code
297 from pa_project_customers
298 where project_id = p_fr_project_id
299 and customer_id = p_fr_customer_id)
300 where project_id = p_to_project_id
301 and customer_id = p_to_customer_id;
302
303 --dbms_output.put_line ('calling delete retention rules OA');
304
305 /* If the setup already exists for the customer it has to be deleted
306 The validation (retained_amount / billed amount is zero is done at UI*/
307
308 delete_retn_rules_customer (
309 p_project_id => p_to_project_id ,
310 p_customer_id => p_to_customer_id ,
311 x_return_status => l_return_status ,
312 x_msg_count => l_msg_count ,
313 x_msg_data => l_msg_data );
314
315 --dbms_output.put_line ('calling insert retention rules OA');
316
317 /* Insert into retention rules table */
318
319 insert_retention_rules (
320 p_fr_project_id => p_fr_project_id ,
321 p_fr_customer_id => p_fr_customer_id ,
322 p_to_project_id => p_to_project_id ,
323 p_to_customer_id => p_to_customer_id ,
324 p_fr_date => p_fr_date ,
325 p_to_date => p_to_date ,
326 p_delta => l_delta ,
327 x_return_status => l_return_status ,
328 x_msg_count => l_msg_count ,
329 x_msg_data => l_msg_data );
330
331 else
332
333 --dbms_output.put_line ('in forms');
334
335 --dbms_output.put_line ('delete retention rules');
336
337 /* If the setup already exists for the project it has to be deleted */
338
339 delete_retention_rules (
340 p_project_id => p_to_project_id ,
341 p_task_id => NULL ,
342 x_return_status => l_return_status ,
343 x_msg_count => l_msg_count ,
344 x_msg_data => l_msg_data );
345
346 for pc_rec in pc_cur (p_fr_project_id, p_to_project_id) loop
347
348 update pa_project_customers
349 set retention_level_code = pc_rec.retention_level_code
350 where project_id = p_to_project_id
351 and customer_id = pc_rec.customer_id;
352
353 --dbms_output.put_line ('calling insert retention rules Forms');
354
355 insert_retention_rules (
356 p_fr_project_id => p_fr_project_id ,
357 p_fr_customer_id => pc_rec.customer_id ,
358 p_to_project_id => p_to_project_id ,
359 p_to_customer_id => pc_rec.customer_id ,
360 p_fr_date => p_fr_date ,
361 p_to_date => p_to_date ,
362 p_delta => l_delta ,
363 x_return_status => l_return_status ,
364 x_msg_count => l_msg_count ,
365 x_msg_data => l_msg_data );
366
367 END LOOP;
368
369
370 /* If there are customers in the destination project not existing in source project
371 the source project's primary customer setup is copied to the destination project */
372
373 SELECT count(*) into l_not_update
374 from pa_project_customers pc
375 where pc.project_id = p_to_project_id
376 and pc.customer_id not in (select customer_id from pa_project_customers
377 where project_id = p_fr_project_id);
378
379 if l_not_update <> 0 then
380
381 --dbms_output.put_line ('same customer not in source getting primary cust');
382
383 l_primary_cust_id := PA_PROJECTS_MAINT_UTILS.get_primary_customer(
384 p_project_id => p_fr_project_id);
385
386 if (nvl(l_primary_cust_id,0) <> 0) then
387
388 select pc.retention_level_code
389 into l_retention_level_code
390 from pa_project_customers pc
391 where pc.project_id = p_fr_project_id
392 and pc.customer_id = l_primary_cust_id;
393
394 for pc_no_rec in no_cust_cur (p_fr_project_id, p_to_project_id ) loop
395
396 update pa_project_customers
397 set retention_level_code = l_retention_level_code
398 where project_id = p_to_project_id
399 and customer_id = pc_no_rec.customer_id;
400
401 --dbms_output.put_line ('calling insert_retetniton rules for primary cust');
402
403 insert_retention_rules (
404 p_fr_project_id => p_fr_project_id ,
405 p_fr_customer_id => l_primary_cust_id ,
406 p_to_project_id => p_to_project_id ,
407 p_to_customer_id => pc_no_rec.customer_id ,
408 p_fr_date => p_fr_date ,
409 p_to_date => p_to_date ,
410 p_delta => l_delta ,
411 x_return_status => l_return_status ,
412 x_msg_count => l_msg_count ,
413 x_msg_data => l_msg_data );
414
415 END LOOP;
416
417 end if;
418
419 end if;
420
421 end if;
422
423 if l_return_status = 'E' then
424 x_return_status := l_return_status;
425 x_msg_count := l_msg_count ;
426 x_msg_data := l_msg_data ;
427 end if;
428
429
430 EXCEPTION
431
432 WHEN others THEN
433 x_msg_count := 1;
434 x_msg_data := SUBSTR(SQLERRM, 1, 240);
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
436 FND_MSG_PUB.add_Exc_msg(
437 p_pkg_name => 'PA_RETENTION_UTIL',
438 p_procedure_name => 'copy_retention_setup');
439
440 RAISE ;
441
442 END copy_retention_setup;
443
444 /*----------------------------------------------------------------------------------------+
445 | Procedure : copy_retention setup |
446 | Purpose : This is an overloaded procedure. When called from OA there could be |
447 | multiple destination customers. This procedure loops through and calls |
448 | the copy for single customer |
449 | This will be called from OA |
450 | Parameters : |
451 | ================================================================================== |
452 | Name Mode Description |
453 | ================================================================================== |
454 | p_fr_project_id IN Source Project Id |
455 | p_to_project_id IN Destination Project ID |
456 | p_fr_customer_id IN Source Customer ID |
457 | p_to_customer_id_tab IN Array of Destination Customer ID |
458 | p_fr_date IN Effective Start Date |
459 | p_to_date IN Effective End Date |
460 | p_call_mode IN Call Mode ('PROJECT', 'CUSTOMER') |
461 | x_return_status OUT Return Status |
462 | x_msg_count OUT Message Count |
463 | x_msg_data OUT Message Data |
464 | ================================================================================== |
465 +----------------------------------------------------------------------------------------*/
466
467
468 PROCEDURE copy_retention_setup (
469 p_fr_project_id IN NUMBER ,
470 p_to_project_id IN NUMBER DEFAULT NULL,
471 p_fr_customer_id IN NUMBER DEFAULT NULL,
472 p_to_customer_id_tab IN PA_NUM_1000_NUM,
473 p_rec_version_tab IN PA_NUM_1000_NUM,
474 p_fr_date IN DATE DEFAULT NULL,
475 p_to_date IN DATE DEFAULT NULL,
476 p_call_mode IN VARCHAR2 DEFAULT 'PROJECT',
477 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
478 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
479 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
480
481
482 l_tab_count NUMBER := 0;
483 l_to_customer_id NUMBER;
484 l_to_project_id NUMBER;
485
486 l_return_status VARCHAR2(30) := NULL;
487 l_msg_count NUMBER := NULL;
488 l_msg_data VARCHAR2(30) := NULL;
489
490
491 BEGIN
492
493 x_return_status := FND_API.G_RET_STS_SUCCESS;
494 x_msg_count := 0;
495 --dbms_output.put_line ('in copy retention setup 2');
496 l_tab_count := p_to_customer_id_tab.COUNT;
497
498 IF l_tab_count = 0 then
499 RETURN;
500
501 END IF;
502
503 if p_to_project_id is null then
504
505 l_to_project_id := p_fr_project_id;
506
507 else
508
509 l_to_project_id := p_to_project_id;
510
511 end if;
512
513 FOR i in 1..l_tab_count LOOP
514
515 --dbms_output.put_line ('calling copy retention setup 1');
516 set_rec_version_num ( p_project_id => l_to_project_id,
517 p_customer_id => p_to_customer_id_tab(i),
518 p_version_num => p_rec_version_tab(i),
519 x_return_status => l_return_status,
520 x_msg_count => l_msg_count,
521 x_msg_data => l_msg_data );
522
523 if l_return_status = FND_API.G_RET_STS_ERROR then
524
525 rollback;
526 exit;
527
528 end if;
529
530 copy_retention_setup (
531 p_fr_project_id => p_fr_project_id,
532 p_to_project_id => l_to_project_id,
533 p_fr_customer_id => p_fr_customer_id,
534 p_to_customer_id => p_to_customer_id_tab(i),
535 p_fr_date => p_fr_date,
536 p_to_date => p_to_date,
537 p_call_mode => p_call_mode,
538 x_return_status => l_return_status,
539 x_msg_count => l_msg_count,
540 x_msg_data => l_msg_data);
541
542
543 END LOOP;
544
545 x_return_status := l_return_status;
546 x_msg_count := l_msg_count ;
547 x_msg_data := l_msg_data ;
548
549 EXCEPTION
550
551 WHEN others THEN
552 x_msg_count := 1;
553 x_msg_data := SUBSTR(SQLERRM, 1, 240);
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555 FND_MSG_PUB.add_Exc_msg(
556 p_pkg_name => 'PA_RETENTION_UTIL',
557 p_procedure_name => 'copy_retention_setup');
558
559
560 END copy_retention_setup;
561
562 /*----------------------------------------------------------------------------------------+
563 | Procedure : delete_retn_rules_customer |
564 | Purpose : To delete from retention rules table for a project and customer |
565 | This will be called from OA |
566 | |
567 | Parameters : |
568 | ================================================================================== |
569 | Name Mode Description |
570 | ================================================================================== |
571 | p_project_id IN Destination project id |
572 | p_customer_id IN Destination customer id |
573 | x_return_status OUT Return status of this procedure |
574 | x_msg_count OUT Error message count |
575 | x_msg_data OUT Error message |
576 | ================================================================================== |
577 +----------------------------------------------------------------------------------------*/
578 PROCEDURE delete_retn_rules_customer (
579 p_project_id IN NUMBER,
580 p_customer_id IN NUMBER,
581 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
582 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
583 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
584
585
586 l_return_status VARCHAR2(30) := NULL;
587 l_msg_count NUMBER := NULL;
588 l_msg_data VARCHAR2(30) := NULL;
589
590
591 BEGIN
592
593 x_return_status := FND_API.G_RET_STS_SUCCESS;
594 x_msg_count := 0;
595 --dbms_output.put_line ('in delete retention rules');
596
597 DELETE FROM pa_proj_retn_rules
598 WHERE project_id = p_project_id
599 AND customer_id = p_customer_id;
600
601
602 DELETE FROM pa_proj_retn_bill_rules
603 WHERE project_id = p_project_id
604 AND customer_id = p_customer_id;
605
606
607 EXCEPTION
608
609 WHEN others THEN
610 x_msg_count := 1;
611 x_msg_data := SUBSTR(SQLERRM, 1, 240);
612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
613 FND_MSG_PUB.add_Exc_msg(
614 p_pkg_name => 'PA_RETENTION_UTIL',
615 p_procedure_name => 'delete_retn_rules_customer');
616
617
618 END delete_retn_rules_customer;
619
620 /*----------------------------------------------------------------------------------------+
621 | Procedure : delete_retention_rules |
622 | Purpose : To delete from retention rules table for a project |
623 | This will be called from Forms |
624 | |
625 | Parameters : |
626 | ================================================================================== |
627 | Name Mode Description |
628 | ================================================================================== |
629 | p_project_id IN Destination project id |
630 | x_return_status OUT Return status of this procedure |
631 | x_msg_count OUT Error message count |
632 | x_msg_data OUT Error message |
633 | ================================================================================== |
634 +----------------------------------------------------------------------------------------*/
635 PROCEDURE delete_retention_rules (
636 p_project_id IN NUMBER,
637 p_task_id IN NUMBER DEFAULT NULL,
638 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
639 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
640 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
641
642
643 l_return_status VARCHAR2(30) := NULL;
644 l_msg_count NUMBER := NULL;
645 l_msg_data VARCHAR2(30) := NULL;
646
647
648 BEGIN
649
650 --dbms_output.put_line ('in delete retention rules');
651
652 x_return_status := FND_API.G_RET_STS_SUCCESS;
653 x_msg_count := 0;
654
655 IF NVL(p_task_id, 0) = 0 then
656
657 DELETE FROM pa_proj_retn_rules
658 WHERE project_id = p_project_id;
659
660 DELETE FROM pa_proj_retn_bill_rules
661 WHERE project_id = p_project_id;
662
663 ELSE
664 DELETE FROM pa_proj_retn_rules
665 WHERE project_id = p_project_id
666 AND task_id = p_task_id;
667
668 DELETE FROM pa_proj_retn_bill_rules
669 WHERE project_id = p_project_id
670 AND task_id = p_task_id;
671
672 END IF;
673
674
675
676 EXCEPTION
677
678 WHEN others THEN
679 x_msg_count := 1;
680 x_msg_data := SUBSTR(SQLERRM, 1, 240);
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 FND_MSG_PUB.add_Exc_msg(
683 p_pkg_name => 'PA_RETENTION_UTIL',
684 p_procedure_name => 'delete_retention_rules');
685
686 END delete_retention_rules;
687
688
689
690 /*----------------------------------------------------------------------------------------+
691 | Procedure : insert_retention_rules |
692 | Purpose : To insert into retention rules table |
693 | Parameters : |
694 | ================================================================================== |
695 | Name Mode Description |
696 | ================================================================================== |
697 | p_fr_project_id IN Source project id |
698 | p_fr_customer_id IN Source customer id |
699 | p_to_project_id IN Destination project id |
700 | p_to_customer_id IN Destination customer id |
701 | p_fr_date IN From effective date |
702 | p_to_date IN To effective date |
703 | x_return_status OUT Return status of this procedure |
704 | x_msg_count OUT Error message count |
705 | x_msg_data OUT Error message |
706 | ================================================================================== |
707 +----------------------------------------------------------------------------------------*/
708 PROCEDURE insert_retention_rules (
709 p_fr_project_id IN NUMBER,
710 p_fr_customer_id IN NUMBER,
711 p_to_project_id IN NUMBER,
712 p_to_customer_id IN NUMBER,
713 p_fr_date IN DATE,
714 p_to_date IN DATE,
715 p_delta IN NUMBER,
716 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
717 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
718 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
719
720
721 cursor retn_cur( l_fr_project_id number, l_fr_customer_id number) IS
722 select retention_level_code, task_id, expenditure_category,
723 expenditure_type, non_labor_resource, event_type,
724 effective_start_date, effective_end_date,
725 retention_percentage, retention_amount, threshold_amount,
726 projfunc_total_retained, project_total_retained,
727 total_retained, revenue_category_code
728 from pa_proj_retn_rules
729 where project_id = l_fr_project_id
730 and customer_id = l_fr_customer_id;
731
732 cursor bill_cur( l_fr_project_id number, l_fr_customer_id number) IS
733 select billing_method_code, task_id, completed_percentage,
734 total_retention_amount, retn_billing_cycle_id, client_extension_flag,
735 retn_billing_percentage, retn_billing_amount
736 from pa_proj_retn_bill_rules
737 where project_id = l_fr_project_id
738 and customer_id = l_fr_customer_id;
739
740 l_to_task_id NUMBER;
741 l_fr_start_date DATE;
742 l_to_start_date DATE;
743
744 l_return_status VARCHAR2(30) := NULL;
745 l_msg_count NUMBER := NULL;
746 l_msg_data VARCHAR2(30) := NULL;
747
748
749 BEGIN
750
751 --dbms_output.put_line ('in insert retention rules');
752
753 x_return_status := FND_API.G_RET_STS_SUCCESS;
754 x_msg_count := 0;
755
756
757 for retn_rec in retn_cur ( p_fr_project_id, p_fr_customer_id) loop
758
759 if nvl(retn_rec.task_id,0) <> 0 then
760
761 get_corresponding_task (
762 p_fr_project_id => p_fr_project_id,
763 p_fr_task_id => retn_rec.task_id,
764 p_to_project_id => p_to_project_id,
765 x_task_id => l_to_task_id,
766 x_fr_start_date => l_fr_start_date,
767 x_to_start_date => l_to_start_date,
768 x_return_status => l_return_status ,
769 x_msg_count => l_msg_count ,
770 x_msg_data => l_msg_data );
771
772
773 INSERT INTO pa_proj_retn_rules
774 (RETENTION_RULE_ID,
775 RETENTION_LEVEL_CODE,
776 PROJECT_ID,
777 CUSTOMER_ID,
778 TASK_ID ,
779 EXPENDITURE_CATEGORY,
780 EXPENDITURE_TYPE,
781 NON_LABOR_RESOURCE,
782 EVENT_TYPE,
783 EFFECTIVE_START_DATE,
784 EFFECTIVE_END_DATE,
785 RETENTION_PERCENTAGE,
786 RETENTION_AMOUNT,
787 THRESHOLD_AMOUNT,
788 CREATION_DATE,
789 CREATED_BY ,
790 LAST_UPDATE_DATE ,
791 LAST_UPDATED_BY,
792 REVENUE_CATEGORY_CODE)
793 VALUES
794 (pa_proj_retn_rules_s.nextval,
795 retn_rec.retention_level_code,
796 p_to_project_id,
797 p_to_customer_id,
798 l_to_task_id,
799 retn_rec.expenditure_category,
800 retn_rec.expenditure_type,
801 retn_rec.non_labor_resource,
802 retn_rec.event_type,
803 decode(l_to_start_date, NULL,
804 retn_rec.effective_start_date + p_delta,
805 retn_rec.effective_start_date + (l_to_start_date -
806 l_fr_start_date)),
807 decode( retn_rec.effective_end_date, null, null,
808 decode(l_to_start_date, NULL,
809 retn_rec.effective_end_date + p_delta,
810 retn_rec.effective_end_date +
811 (l_to_start_date - l_fr_start_date))),
812 retn_rec.retention_percentage,
813 retn_rec.retention_amount,
814 retn_rec.threshold_amount,
815 sysdate,
816 fnd_global.user_id,
817 sysdate,
818 fnd_global.user_id,
819 retn_rec.revenue_category_code);
820
821 else
822 l_to_task_id := NULL;
823
824 INSERT INTO pa_proj_retn_rules
825 (RETENTION_RULE_ID,
826 RETENTION_LEVEL_CODE,
827 PROJECT_ID,
828 CUSTOMER_ID,
829 TASK_ID ,
830 EXPENDITURE_CATEGORY,
831 EXPENDITURE_TYPE,
832 NON_LABOR_RESOURCE,
833 EVENT_TYPE,
834 EFFECTIVE_START_DATE,
835 EFFECTIVE_END_DATE,
836 RETENTION_PERCENTAGE,
837 RETENTION_AMOUNT,
838 THRESHOLD_AMOUNT,
839 CREATION_DATE,
840 CREATED_BY ,
841 LAST_UPDATE_DATE ,
842 LAST_UPDATED_BY,
843 REVENUE_CATEGORY_CODE)
844 VALUES
845 (pa_proj_retn_rules_s.nextval,
846 retn_rec.retention_level_code,
847 p_to_project_id,
848 p_to_customer_id,
849 l_to_task_id,
850 retn_rec.expenditure_category,
851 retn_rec.expenditure_type,
852 retn_rec.non_labor_resource,
853 retn_rec.event_type,
854 retn_rec.effective_start_date + p_delta,
855 retn_rec.effective_end_date + p_delta,
856 retn_rec.retention_percentage,
857 retn_rec.retention_amount,
858 retn_rec.threshold_amount,
859 sysdate,
860 fnd_global.user_id,
861 sysdate,
862 fnd_global.user_id,
863 retn_rec.revenue_category_code);
864
865 end if;
866
867 END LOOP;
868
869 for bill_rec in bill_cur ( p_fr_project_id, p_fr_customer_id) loop
870
871 if nvl(bill_rec.task_id,0) <> 0 then
872
873
874 if (p_fr_project_id = p_to_project_id) then
875
876 l_to_task_id := bill_rec.task_id;
877
878 else
879
880 get_corresponding_task (
881 p_fr_project_id => p_fr_project_id,
882 p_fr_task_id => bill_rec.task_id,
883 p_to_project_id => p_to_project_id,
884 x_task_id => l_to_task_id,
885 x_fr_start_date => l_fr_start_date,
886 x_to_start_date => l_to_start_date,
887 x_return_status => l_return_status ,
888 x_msg_count => l_msg_count ,
889 x_msg_data => l_msg_data );
890
891 end if;
892
893 else
894 l_to_task_id := NULL;
895 end if;
896
897 --dbms_output.put_line ('actual insertion');
898 INSERT INTO pa_proj_retn_bill_rules
899 ( RETN_BILLING_RULE_ID,
900 BILLING_METHOD_CODE,
901 PROJECT_ID,
902 CUSTOMER_ID,
903 TASK_ID ,
904 COMPLETED_PERCENTAGE,
905 TOTAL_RETENTION_AMOUNT,
906 RETN_BILLING_CYCLE_ID,
907 CLIENT_EXTENSION_FLAG,
908 RETN_BILLING_PERCENTAGE,
909 RETN_BILLING_AMOUNT,
910 CREATION_DATE,
911 CREATED_BY ,
912 LAST_UPDATE_DATE ,
913 LAST_UPDATED_BY)
914 VALUES
915 (pa_proj_retn_bill_rules_s.nextval,
916 bill_rec.billing_method_code,
917 p_to_project_id,
918 p_to_customer_id,
919 l_to_task_id,
920 bill_rec.completed_percentage,
921 bill_rec.total_retention_amount,
922 bill_rec.retn_billing_cycle_id,
923 bill_rec.client_extension_flag,
924 bill_rec.retn_billing_percentage,
925 bill_rec.retn_billing_amount,
926 sysdate,
927 fnd_global.user_id,
928 sysdate,
929 fnd_global.user_id );
930
931 END LOOP;
932
933
934 EXCEPTION
935
936 WHEN others THEN
937 x_msg_count := 1;
938 x_msg_data := SUBSTR(SQLERRM, 1, 240);
939 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
940 FND_MSG_PUB.add_Exc_msg(
941 p_pkg_name => 'PA_RETENTION_UTIL',
942 p_procedure_name => 'insert_retention_rules');
943
944 END insert_retention_rules;
945
946
947 /*----------------------------------------------------------------------------------------+
948 | Procedure : get_currency_code |
949 | Purpose : This procedure returns the various currency code for the given project |
950 | ID |
951 | Parameters : |
952 | ================================================================================== |
953 | Name Mode Description |
954 | ================================================================================== |
955 | p_project_id IN Project_id |
956 | x_invproc_currency_type OUT invproc currency type |
957 | x_project_currency_code OUT project currency code |
958 | x_projfunc_currency_code OUT project functional currency code |
959 | x_funding_currency_code OUT funding currency code |
960 | x_invproc_currency_code OUT invoice processing currency code |
961 | x_return_status OUT Return status of this procedure |
962 | x_msg_count OUT Error message count |
963 | x_msg_data OUT Error message |
964 | ================================================================================== |
965 +----------------------------------------------------------------------------------------*/
966
967 PROCEDURE get_currency_code(
968 p_project_id IN NUMBER,
969 x_invproc_currency_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
970 x_project_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
971 x_projfunc_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
972 x_funding_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
973 x_invproc_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
974 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
975 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
976 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
977
978
979 l_return_status VARCHAR2(30) := NULL;
980 l_msg_count NUMBER := NULL;
981 l_msg_data VARCHAR2(30) := NULL;
982
983 BEGIN
984
985 x_return_status := FND_API.G_RET_STS_SUCCESS;
986 x_msg_count := 0;
987 --dbms_output.put_line ('in get currency code');
988 SELECT invproc_currency_type,
989 project_currency_code,
990 projfunc_currency_code
991 INTO x_invproc_currency_type,
992 x_project_currency_code,
993 x_projfunc_currency_code
994 FROM pa_projects_all
995 WHERE project_id = p_project_id;
996
997
998 x_funding_currency_code := NULL;
999
1000 IF x_invproc_currency_type = 'PROJECT_CURRENCY' THEN
1001
1002 x_invproc_currency_code := x_project_currency_code;
1003
1004 ELSIF x_invproc_currency_type = 'PROJFUNC_CURRENCY' THEN
1005
1006 x_invproc_currency_code := x_projfunc_currency_code;
1007
1008 ELSIF x_invproc_currency_type = 'FUNDING_CURRENCY' THEN
1009
1010 BEGIN
1011
1012 SELECT funding_currency_code
1013 INTO x_invproc_currency_code
1014 FROM pa_summary_project_fundings
1015 WHERE project_id = p_project_id
1016 AND rownum = 1
1017 GROUP BY funding_currency_code
1018 HAVING sum(nvl(total_baselined_amount,0)) > 0;
1019
1020 x_funding_currency_code := x_invproc_currency_code;
1021
1022 EXCEPTION
1023
1024 WHEN NO_DATA_FOUND THEN
1025
1026 x_invproc_currency_code := null;
1027
1028 /*
1029 x_msg_count := 1;
1030 x_msg_data := 'PA_NO_FUNDING_EXISTS';
1031 x_return_status := FND_API.G_RET_STS_ERROR;
1032 FND_MSG_PUB.add_Exc_msg(
1033 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
1034 p_procedure_name => 'get_project_defaults');
1035
1036 RAISE ;
1037 */
1038
1039 END;
1040
1041
1042 END IF;
1043
1044
1045 EXCEPTION
1046
1047 WHEN others THEN
1048
1049 x_invproc_currency_type := NULL; -- NOCOPY
1050 x_project_currency_code := NULL; -- NOCOPY
1051 x_projfunc_currency_code := NULL; -- NOCOPY
1052 x_funding_currency_code := NULL; -- NOCOPY
1053 x_invproc_currency_code := NULL; -- NOCOPY
1054 x_msg_count := 1;
1055 x_msg_data := SUBSTR(SQLERRM, 1, 240);
1056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1057 FND_MSG_PUB.add_Exc_msg(
1058 p_pkg_name => 'PA_RETENTION_UTILS',
1059 p_procedure_name => 'get_currency_code');
1060
1061 RAISE ;
1062
1063 END get_currency_code;
1064
1065 /*----------------------------------------------------------------------------------------+
1066 | Procedure : get_corresponding_task |
1067 | Purpose : This procedure returns task_id of the destination project corresponding|
1068 | to the source project's task_id. |
1069 | NOTE : When setup is copied from source project to destination project |
1070 | both project's will have same task_number. |
1071 | If the retention setup of source project is defined at task_level the |
1072 | corresponding task_id of destination project (based on tak_number) |
1073 | will be returned by this function |
1074 | Parameters : |
1075 | ================================================================================== |
1076 | Name Mode Description |
1077 | ================================================================================== |
1078 | p_fr_project_id IN Project ID (Source) |
1079 | p_fr_task_id IN Task ID (Source) |
1080 | p_to_project_id IN Project ID (destination) |
1081 | x_task_id OUT task ID (destination) |
1082 | x_fr_start_date OUT task start date (Source) |
1083 | x_to_start_date OUT task start date (Destination) |
1084 | x_return_status OUT return status |
1085 | x_msg_count OUT message count |
1086 | x_msg_data OUT message data |
1087 | ================================================================================== |
1088 +----------------------------------------------------------------------------------------*/
1089
1090 PROCEDURE get_corresponding_task ( p_fr_project_id IN NUMBER,
1091 p_fr_task_id IN NUMBER,
1092 p_to_project_id IN NUMBER,
1093 x_task_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1094 x_fr_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
1095 x_to_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
1096 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1097 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1098 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1099
1100 l_return_status VARCHAR2(30) := NULL;
1101 l_msg_count NUMBER := NULL;
1102 l_msg_data VARCHAR2(30) := NULL;
1103
1104 BEGIN
1105
1106 x_return_status := FND_API.G_RET_STS_SUCCESS;
1107 x_msg_count := 0;
1108
1109 --dbms_output.put_line ('in get corresponding task');
1110
1111 SELECT new.task_id, new.start_date, old.start_date
1112 INTO x_task_id, x_to_start_date, x_fr_start_date
1113 FROM pa_tasks old, pa_tasks new
1114 WHERE old.project_id = p_fr_project_id
1115 AND old.task_id = p_fr_task_id
1116 AND old.task_number = new.task_number
1117 AND new.project_id = p_to_project_id;
1118
1119
1120 EXCEPTION
1121
1122 WHEN others THEN
1123
1124 x_task_id := NULL; --NOCOPY
1125 x_fr_start_date := NULL; --NOCOPY
1126 x_to_start_date := NULL; --NOCOPY
1127 x_msg_count := 1;
1128 x_msg_data := SUBSTR(SQLERRM, 1, 240);
1129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1130
1131 FND_MSG_PUB.add_Exc_msg(
1132 p_pkg_name => 'PA_RETENTION_UTIL',
1133 p_procedure_name => 'get_corresponding_task');
1134
1135 RAISE ;
1136
1137 END get_corresponding_task;
1138
1139
1140 /*----------------------------------------------------------------------------------------+
1141 | Procedure : get_project_info |
1142 | Purpose : This procedure returns the project related information for the given |
1143 | project ID |
1144 | Parameters : |
1145 | ================================================================================== |
1146 | Name Mode Description |
1147 | ================================================================================== |
1148 | p_project_id IN Input Project id |
1149 | x_project_name OUT Project Name |
1150 | x_project_number OUT Project Number |
1151 | x_invproc_currency_type OUT Invoice processing currency type |
1152 | x_invproc_currency_code OUT Invoice processing currency code |
1153 | x_projfunc_currency_code OUT Invoice processing currency code |
1154 | x_return_status OUT Return status of this procedure |
1155 | x_msg_count OUT Error message count |
1156 | x_msg_data OUT Error message |
1157 | ================================================================================== |
1158 +----------------------------------------------------------------------------------------*/
1159
1160
1161 PROCEDURE get_project_info (p_project_id IN NUMBER,
1162 x_project_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1163 x_project_number OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1164 x_invproc_currency_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1165 x_invproc_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1166 x_projfunc_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1167 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1168 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1169 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1170
1171
1172 l_project_currency_code VARCHAR2(30);
1173 l_invproc_currency_type VARCHAR2(30);
1174
1175
1176 l_return_status VARCHAR2(30) := NULL;
1177 l_msg_count NUMBER := NULL;
1178 l_msg_data VARCHAR2(30) := NULL;
1179
1180 BEGIN
1181
1182 x_return_status := FND_API.G_RET_STS_SUCCESS;
1183 x_msg_count := 0;
1184
1185 SELECT p.segment1, p.name, p.invproc_currency_type,
1186 p.project_currency_code, p.projfunc_currency_code, lk.meaning
1187 INTO x_project_number, x_project_name, l_invproc_currency_type,
1188 l_project_currency_code, x_projfunc_currency_code, x_invproc_currency_type
1189 FROM pa_projects_all p , pa_lookups lk
1190 WHERE project_id = p_project_id
1191 and lk.lookup_type = 'INVPROCE_CURR_TYPE'
1192 and lk.lookup_code = p.invproc_currency_type;
1193
1194
1195 IF l_invproc_currency_type = 'PROJECT_CURRENCY' THEN
1196
1197 x_invproc_currency_code := l_project_currency_code;
1198
1199 ELSIF l_invproc_currency_type = 'PROJFUNC_CURRENCY' THEN
1200
1201 x_invproc_currency_code := x_projfunc_currency_code;
1202
1203 ELSIF l_invproc_currency_type = 'FUNDING_CURRENCY' THEN
1204
1205 BEGIN
1206
1207 SELECT funding_currency_code
1208 INTO x_invproc_currency_code
1209 FROM pa_summary_project_fundings
1210 WHERE project_id = p_project_id
1211 AND rownum = 1
1212 GROUP BY funding_currency_code
1213 HAVING sum(nvl(total_baselined_amount,0)) > 0;
1214
1215
1216 EXCEPTION
1217
1218 WHEN NO_DATA_FOUND THEN
1219
1220 x_invproc_currency_code := null;
1221
1222 /*
1223 x_msg_count := 1;
1224 x_msg_data := 'PA_NO_FUNDING_EXISTS';
1225 x_return_status := FND_API.G_RET_STS_ERROR;
1226 FND_MSG_PUB.add_Exc_msg(
1227 p_pkg_name => 'PA_MULTI_CURRENCY_BILLING',
1228 p_procedure_name => 'get_project_defaults');
1229
1230 RAISE ;
1231 */
1232
1233 END;
1234
1235
1236 END IF;
1237
1238
1239
1240 EXCEPTION
1241
1242 WHEN others THEN
1243
1244 x_project_name := NULL; --NOCOPY
1245 x_project_number := NULL; --NOCOPY
1246 x_invproc_currency_type := NULL; --NOCOPY
1247 x_invproc_currency_code := NULL; --NOCOPY
1248 x_projfunc_currency_code := NULL; --NOCOPY
1249 x_msg_count := 1;
1250 x_msg_data := SUBSTR(SQLERRM, 1, 240);
1251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1252
1253 FND_MSG_PUB.add_Exc_msg(
1254 p_pkg_name => 'PA_RETENTION_UTILS',
1255 p_procedure_name => 'get_project_info');
1256
1257 RAISE ;
1258
1259
1260 END get_project_info;
1261
1262 /*----------------------------------------------------------------------------------------+
1263 | Procedure : calculate_date_factor |
1264 | Purpose : To calculate date factor based on the difference in dates between |
1265 | source project and destination project |
1266 | |
1267 | Parameters : |
1268 | ================================================================================== |
1269 | Name Mode Description |
1270 | ================================================================================== |
1271 | p_fr_project_id IN Source project id |
1272 | p_to_project_id IN Destination project id |
1273 | x_delta OUT date factor to be used for effective dates |
1274 | x_return_status OUT Return status of this procedure |
1275 | x_msg_count OUT Error message count |
1276 | x_msg_data OUT Error message |
1277 | ================================================================================== |
1278 +----------------------------------------------------------------------------------------*/
1279
1280 PROCEDURE calculate_date_factor (
1281 p_fr_project_id IN NUMBER,
1282 p_to_project_id IN NUMBER,
1283 x_delta OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1284 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1285 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1286 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1287
1288
1289 l_return_status VARCHAR2(30) := NULL;
1290 l_msg_count NUMBER := NULL;
1291 l_msg_data VARCHAR2(30) := NULL;
1292
1293 -- use min(start_date) as pseudo original project start
1294
1295 CURSOR c2 is SELECT min(start_date) min_start
1296 FROM pa_tasks
1297 WHERE project_id = p_fr_project_id;
1298
1299 c2_rec c2%rowtype;
1300
1301
1302 l_fr_start_date DATE;
1303 l_to_start_date DATE;
1304
1305
1306
1307 BEGIN
1308
1309 x_return_status := FND_API.G_RET_STS_SUCCESS;
1310 x_msg_count := 0;
1311
1312 --dbms_output.put_line ('in calculate date factor ');
1313
1314 SELECT fr_proj.start_date, to_proj.start_date
1315 INTO l_fr_start_date, l_to_start_date
1316 FROM pa_projects_all fr_proj, pa_projects_all to_proj
1317 WHERE fr_proj.project_id = p_fr_project_id
1318 AND to_proj.project_id = p_to_project_id;
1319
1320 if (l_to_start_date is null) then
1321 x_delta := 0;
1322 elsif (l_fr_start_date is not null) then
1323 x_delta := l_to_start_date - l_fr_start_date;
1324 else
1325 open c2;
1326 fetch c2 into c2_rec;
1327 if c2%found then
1328 x_delta := l_to_start_date - c2_rec.min_start;
1329 end if;
1330 close c2;
1331
1332 end if;
1333
1334
1335
1336 EXCEPTION
1337
1338 WHEN others THEN
1339 x_delta := NULL; --NOCOPY
1340 x_msg_count := 1;
1341 x_msg_data := SUBSTR(SQLERRM, 1, 240);
1342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1343 FND_MSG_PUB.add_Exc_msg(
1344 p_pkg_name => 'PA_RETENTION_UTIL',
1345 p_procedure_name => 'calculate_date_factor');
1346
1347
1348 END calculate_date_factor;
1349
1350
1351 /*----------------------------------------------------------------------------------------+
1352 | Procedure : get_rec_version_num |
1353 | Purpose : This procedure returns the record version number of the given project |
1354 | and customer id from pa_project_customers table |
1355 | Parameters : |
1356 | ================================================================================== |
1357 | Name Mode Description |
1358 | ================================================================================== |
1359 | p_project_id IN Input Project id |
1360 | p_customer_id IN Input Customer id |
1361 | x_version_num OUT Record version number |
1362 | ================================================================================== |
1363 +----------------------------------------------------------------------------------------*/
1364
1365
1366 PROCEDURE get_rec_version_num ( p_project_id IN NUMBER,
1367 p_customer_id IN NUMBER,
1368 x_version_num OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
1369
1370 BEGIN
1371
1372 SELECT record_version_number
1373 INTO x_version_num
1374 FROM pa_project_customers
1375 WHERE project_id = p_project_id
1376 AND customer_id = p_customer_id;
1377
1378 EXCEPTION
1379 WHEN NO_DATA_FOUND THEN
1380 x_version_num := NULL;
1381
1382 END get_rec_version_num;
1383
1384 /*----------------------------------------------------------------------------------------+
1385 | Procedure : check_rec_version_num |
1386 | Purpose : This procedure checks the record version number with the record version|
1387 | number of the given project and customer id in pa_project_customers |
1388 | table |
1389 | Parameters : |
1390 | ================================================================================== |
1391 | Name Mode Description |
1392 | ================================================================================== |
1393 | p_project_id IN Input Project id |
1394 | p_customer_id IN Input Customer id |
1395 | p_version_num IN Record version number |
1396 | ================================================================================== |
1397 +----------------------------------------------------------------------------------------*/
1398
1399
1400 FUNCTION check_rec_version_num ( p_project_id IN NUMBER,
1401 p_customer_id IN NUMBER,
1402 p_version_num IN NUMBER)
1403 RETURN VARCHAR2 IS
1404
1405 l_version_num NUMBER;
1406
1407 BEGIN
1408
1409 BEGIN
1410
1411 SELECT record_version_number
1412 INTO l_version_num
1413 FROM pa_project_customers
1414 WHERE project_id = p_project_id
1415 AND customer_id = p_customer_id;
1416
1417 EXCEPTION
1418 WHEN NO_DATA_FOUND THEN
1419 l_version_num := NULL;
1420 END;
1421
1422 IF NVL(l_version_num,0) = NVL(p_version_num,0) THEN
1423
1424 RETURN 'T';
1425
1426 ELSE
1427
1428 RETURN 'F';
1429
1430 END IF;
1431
1432 END check_rec_version_num;
1433
1434 /*----------------------------------------------------------------------------------------+
1435 | Procedure : set_rec_version_num |
1436 | Purpose : This procedure sets the record version number of the given project |
1437 | and customer id in pa_project_customers table |
1438 | Parameters : |
1439 | ================================================================================== |
1440 | Name Mode Description |
1441 | ================================================================================== |
1442 | p_project_id IN Input Project id |
1443 | p_customer_id IN Input Customer id |
1444 | p_version_num IN old Record version number |
1445 | x_version_num OUT new Record version number |
1446 | x_return_status OUT Return Status |
1447 | x_msg_count OUT Message Count |
1448 | x_msg_data OUT Message Data |
1449 | ================================================================================== |
1450 +----------------------------------------------------------------------------------------*/
1451
1452
1453 PROCEDURE set_rec_version_num ( p_project_id IN NUMBER,
1454 p_customer_id IN NUMBER,
1455 p_version_num IN NUMBER,
1456 /* x_version_num OUT NUMBER, */
1457 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1458 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1459 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1460
1461 vers_valid VARCHAR2(1);
1462 l_return_status VARCHAR2(30) := NULL;
1463 l_msg_count NUMBER := NULL;
1464 l_msg_data VARCHAR2(30) := NULL;
1465 l_record_modified EXCEPTION;
1466
1467 BEGIN
1468
1469 x_return_status := FND_API.G_RET_STS_SUCCESS;
1470 x_msg_count := 0;
1471
1472 vers_valid := check_rec_version_num(p_project_id => p_project_id,
1473 p_customer_id => p_customer_id,
1474 p_version_num => p_version_num);
1475
1476 IF vers_valid = 'T' THEN
1477
1478 update pa_project_customers
1479 set record_version_number = p_version_num + 1
1480 where project_id = p_project_id
1481 and customer_id = p_customer_id
1482 and record_version_number = p_version_num;
1483
1484 /* x_version_num := p_version_num + 1; */
1485
1486 ELSE
1487
1488 RAISE l_record_modified;
1489
1490 END IF;
1491
1492 EXCEPTION
1493
1494 WHEN l_record_modified THEN
1495 x_msg_count := 1;
1496 x_msg_data := 'PA_XC_RECORD_CHANGED';
1497 x_return_status := FND_API.G_RET_STS_ERROR;
1498 FND_MSG_PUB.add_Exc_msg(
1499 p_pkg_name => 'PA_RETENTION_UTIL',
1500 p_procedure_name => 'set_rec_version_num');
1501
1502 END set_rec_version_num;
1503
1504
1505 PROCEDURE retn_billing_method_single(
1506 p_billing_mode IN VARCHAR2,
1507 P_retention_level IN VARCHAR2,
1508 p_project_id IN VARCHAR2,
1509 p_task_id IN VARCHAR2,
1510 p_customer_id IN VARCHAR2,
1511 p_retn_billing_cycle_id IN VARCHAR2,
1512 p_billing_method_code IN VARCHAR2,
1513 p_invproc_currency_code IN VARCHAR2,
1514 p_completed_percentage IN VARCHAR2,
1515 p_total_retention_amount IN VARCHAR2,
1516 p_client_extension_flag IN VARCHAR2,
1517 p_retn_billing_percentage IN VARCHAR2,
1518 p_retn_billing_amount IN VARCHAR2,
1519 p_version_num IN NUMBER,
1520 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1521 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1522 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1523 IS
1524
1525
1526 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
1527
1528 l_count NUMBER;
1529 l_row_count NUMBER;
1530
1531 l_tot_retn_amount EXCEPTION;
1532 l_retn_billing_cycle EXCEPTION;
1533 l_bill_per_either_amount EXCEPTION;
1534 l_percentage_invalid EXCEPTION;
1535 l_bill_per_either_amount_null EXCEPTION;
1536 l_neg_not_allowed EXCEPTION;
1537 l_used_other_method EXCEPTION;
1538
1539
1540
1541 l_x_return_status VARCHAR2(30) := NULL;
1542 l_x_msg_count NUMBER := NULL;
1543 l_x_msg_data VARCHAR2(200) := NULL;
1544
1545
1546
1547 BEGIN
1548
1549 x_return_status := FND_API.G_RET_STS_SUCCESS;
1550 x_msg_count := 0;
1551
1552 --Clear the global PL/SQL message table
1553 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
1554 FND_MSG_PUB.initialize;
1555 END IF;
1556
1557
1558 /* -------------------------------------------------------------
1559 Validating the Input Data
1560 1) Total Retention Amount should not null, It can be Zero
1561 2) Cycle Id Should not be null
1562 ------------------------------------------------------------- */
1563
1564 IF (p_billing_method_code = 'TOTAL_RETENTION_AMOUNT') THEN
1565
1566
1567 IF ((nvl(p_total_retention_amount, 0) < 0) OR (nvl(p_retn_billing_percentage,0) < 0)
1568 OR (nvl(p_retn_billing_amount,0) < 0)) THEN
1569
1570 RAISE l_neg_not_allowed;
1571
1572 END IF;
1573
1574
1575 IF p_total_retention_amount IS NULL THEN
1576
1577 RAISE l_tot_retn_amount;
1578
1579 END IF;
1580
1581
1582 IF (p_retn_billing_percentage IS NULL) and (p_retn_billing_amount IS NULL) THEN
1583
1584 RAISE l_bill_per_either_amount_null;
1585
1586 END IF;
1587
1588 IF (p_retn_billing_percentage IS NOT NULL) and (p_retn_billing_amount IS NOT NULL) THEN
1589
1590 RAISE l_bill_per_either_amount;
1591
1592 END IF;
1593
1594
1595 IF (NVL(p_retn_billing_percentage, 0) > 100) THEN
1596
1597 RAISE l_percentage_invalid;
1598
1599 END IF;
1600
1601
1602
1603 ELSIF (p_billing_method_code = 'RETENTION_BILLING_CYCLE') THEN
1604
1605 IF ((nvl(p_retn_billing_percentage,0) < 0)
1606 OR (nvl(p_retn_billing_amount,0) < 0)) THEN
1607
1608 RAISE l_neg_not_allowed;
1609
1610 END IF;
1611
1612
1613 IF (p_retn_billing_cycle_id is NULL) THEN
1614
1615 RAISE l_retn_billing_cycle;
1616
1617 END IF;
1618
1619
1620 IF (p_retn_billing_percentage IS NULL) and (p_retn_billing_amount IS NULL) THEN
1621
1622 RAISE l_bill_per_either_amount_null;
1623
1624 END IF;
1625
1626 IF (p_retn_billing_percentage IS NOT NULL) and (p_retn_billing_amount IS NOT NULL) THEN
1627
1628 RAISE l_bill_per_either_amount;
1629
1630 END IF;
1631
1632
1633 IF (NVL(p_retn_billing_percentage, 0) > 100) THEN
1634
1635 RAISE l_percentage_invalid;
1636
1637 END IF;
1638
1639
1640
1641
1642 END IF;
1643
1644
1645
1646 /* Set the Record Version Number, This package will check for the database version number and
1647 What ever version number use in the program, If both are equal then return staus will be successful,
1648 If both are differnt then Other User is already updated the project Record so raising the error */
1649
1650
1651 pa_retention_util.set_rec_version_num ( p_project_id,
1652 p_customer_id,
1653 p_version_num,
1654 l_x_return_status,
1655 l_x_msg_count,
1656 l_x_msg_data
1657 );
1658
1659
1660 IF (l_x_return_status = 'E') THEN
1661
1662
1663 x_return_status := 'X';
1664 x_msg_count := l_x_msg_count;
1665 x_msg_data := l_x_msg_data;
1666
1667 return;
1668
1669
1670 END IF;
1671
1672
1673
1674
1675
1676 /* ------------------------------------------------------------------
1677 Delete the Old method from Table and Insert a Row with New Method
1678 ------------------------------------------------------------------ */
1679
1680
1681 DELETE FROM pa_proj_retn_bill_rules
1682 WHERE project_id = p_project_id
1683 AND nvl(task_id, -99) = nvl(p_task_id, -99)
1684 AND customer_id = p_customer_id ;
1685
1686
1687 l_row_count := SQL%ROWCOUNT;
1688
1689
1690 IF (p_billing_mode = 'U') AND (l_row_count = 0) THEN
1691
1692
1693 RAISE l_used_other_method; /* Other User is Changed this methdo */
1694
1695
1696 END IF;
1697
1698
1699
1700 /* ------------------------------------------------------------------
1701 Insert for the Following Billing Method.
1702 Total Retention Amount
1703 Retention Billing Cycle
1704 Client Extension
1705 'None' - No Insertion for or this method
1706 ------------------------------------------------------------------ */
1707
1708 IF ((p_billing_method_code = 'TOTAL_RETENTION_AMOUNT') OR (p_billing_method_code = 'RETENTION_BILLING_CYCLE')
1709 OR (p_billing_method_code = 'CLIENT_EXTENSION')) THEN
1710
1711
1712 INSERT INTO pa_proj_retn_bill_rules
1713 ( PROJECT_ID ,
1714 CUSTOMER_ID ,
1715 TASK_ID,
1716 BILLING_METHOD_CODE,
1717 COMPLETED_PERCENTAGE,
1718 TOTAL_RETENTION_AMOUNT,
1719 RETN_BILLING_CYCLE_ID,
1720 CLIENT_EXTENSION_FLAG,
1721 RETN_BILLING_PERCENTAGE,
1722 RETN_BILLING_AMOUNT,
1723 CREATION_DATE,
1724 CREATED_BY,
1725 LAST_UPDATE_DATE,
1726 LAST_UPDATED_BY,
1727 RETN_BILLING_RULE_ID
1728 )
1729 VALUES(
1730 p_project_id,
1731 p_customer_id,
1732 p_task_id,
1733 p_billing_method_code,
1734 p_completed_percentage,
1735 p_total_retention_amount,
1736 p_retn_billing_cycle_id,
1737 p_client_extension_flag,
1738 p_retn_billing_percentage,
1739 p_retn_billing_amount,
1740 sysdate,
1741 -1,
1742 sysdate,
1743 -1,
1744 pa_proj_retn_bill_rules_s.nextval
1745 );
1746
1747 END IF;
1748
1749
1750
1751 EXCEPTION
1752 WHEN l_retn_billing_cycle THEN
1753 x_return_status := FND_API.G_RET_STS_ERROR;
1754 x_msg_count := 1;
1755 x_msg_data := 'PA_RETN_BILL_CYCLE_NULL';
1756 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
1757 p_procedure_name => 'retn_billing_method_single');
1758 WHEN l_tot_retn_amount THEN
1759 x_return_status := FND_API.G_RET_STS_ERROR;
1760 x_msg_count := 1;
1761 x_msg_data := 'PA_RETN_BILL_TOT_AMT_NULL';
1762 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
1763 p_procedure_name => 'retn_billing_method_single');
1764
1765 WHEN l_bill_per_either_amount THEN
1766 x_return_status := FND_API.G_RET_STS_ERROR;
1767 x_msg_count := 1;
1768 x_msg_data := 'PA_RETN_PERC_AMNT_EXIST';
1769 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
1770 p_procedure_name => 'retn_billing_method_single');
1771
1772 WHEN l_bill_per_either_amount_null THEN
1773 x_return_status := FND_API.G_RET_STS_ERROR;
1774 x_msg_count := 1;
1775 x_msg_data := 'PA_RETN_PERC_AMNT_NO_EXIST';
1776 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
1777 p_procedure_name => 'retn_billing_method_single');
1778 WHEN l_neg_not_allowed THEN
1779 x_return_status := FND_API.G_RET_STS_ERROR;
1780 x_msg_count := 1;
1781 x_msg_data := 'PA_RETN_NEG_VAL';
1782 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
1783 p_procedure_name => 'retn_billing_method_single');
1784
1785 WHEN l_percentage_invalid THEN
1786 x_return_status := FND_API.G_RET_STS_ERROR;
1787 x_msg_count := 1;
1788 x_msg_data := 'PA_RETN_PERCENT_RANGE';
1789 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
1790 p_procedure_name => 'retn_billing_method_single');
1791
1792 WHEN l_used_other_method THEN
1793 x_return_status := FND_API.G_RET_STS_ERROR;
1794 x_msg_count := 1;
1795 x_msg_data := 'PA_RETN_RECORD_CHANGED';
1796 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
1797 p_procedure_name => 'retn_billing_method_single');
1798
1799 WHEN OTHERS THEN
1800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1801 x_msg_count := 1;
1802 x_msg_data := SUBSTR(SQLERRM, 1, 240);
1803 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
1804 p_procedure_name => 'retn_billing_method_single');
1805
1806 END retn_billing_method_single;
1807
1808
1809 PROCEDURE retn_billing_method_PerComp(
1810 p_billing_mode IN VARCHAR2,
1811 P_retention_level IN VARCHAR2,
1812 p_project_id IN VARCHAR2,
1813 p_task_id IN VARCHAR2,
1814 p_customer_id IN VARCHAR2,
1815 p_retn_billing_cycle_id IN VARCHAR2,
1816 p_billing_method_code IN VARCHAR2,
1817 p_invproc_currency_code IN VARCHAR2,
1818 p_completed_percentage IN PA_VC_1000_25,
1819 p_total_retention_amount IN VARCHAR2,
1820 p_client_extension_flag IN VARCHAR2,
1821 p_retn_billing_percentage IN PA_VC_1000_25,
1822 p_retn_billing_amount IN PA_VC_1000_25,
1823 p_version_num IN NUMBER,
1824 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1825 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1826 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1827 IS
1828
1829 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
1830
1831 l_count NUMBER;
1832 l_row_count NUMBER;
1833
1834 l_completed_percentage PA_PLSQL_DATATYPES.Char30TabTyp;
1835 l_retn_billing_percentage PA_PLSQL_DATATYPES.Char30TabTyp;
1836 l_retn_billing_amount PA_PLSQL_DATATYPES.Char30TabTyp;
1837
1838
1839 l_comp_per NUMBER;
1840
1841 l_rec_count NUMBER;
1842
1843 l_retn_per_comp_dup EXCEPTION;
1844 l_retn_per_comp_null EXCEPTION;
1845 l_bill_per_either_amount EXCEPTION;
1846 l_percentage_invalid EXCEPTION;
1847 l_bill_per_either_amount_null EXCEPTION;
1848 l_neg_not_allowed EXCEPTION;
1849 l_used_other_method EXCEPTION;
1850
1851
1852 l_x_return_status VARCHAR2(30) := NULL;
1853 l_x_msg_count NUMBER := NULL;
1854 l_x_msg_data VARCHAR2(200) := NULL;
1855
1856
1857 BEGIN
1858
1859
1860 x_return_status := FND_API.G_RET_STS_SUCCESS;
1861 x_msg_count := 0;
1862
1863
1864 --Clear the global PL/SQL message table
1865 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
1866 FND_MSG_PUB.initialize;
1867 END IF;
1868
1869
1870
1871 IF (p_completed_percentage.COUNT > 0) THEN
1872
1873
1874 FOR I in 1 .. p_completed_percentage.COUNT
1875 LOOP
1876
1877
1878 IF ( NVL(p_completed_percentage(i),0) = 0 ) THEN
1879
1880 RAISE l_retn_per_comp_null;
1881
1882 END IF;
1883
1884
1885 IF (p_retn_billing_percentage(I) IS NULL) and (p_retn_billing_amount(i) IS NULL) THEN
1886
1887 RAISE l_bill_per_either_amount_null;
1888
1889 END IF;
1890
1891
1892 IF (p_retn_billing_percentage(I) IS NOT NULL) and (p_retn_billing_amount(i) IS NOT NULL) THEN
1893
1894 RAISE l_bill_per_either_amount;
1895
1896 END IF;
1897
1898
1899 IF (nvl(p_retn_billing_percentage(I),0) > 100) OR ( NVL(p_completed_percentage(i),0) >100 ) THEN
1900
1901 RAISE l_percentage_invalid;
1902
1903 END IF;
1904
1905
1906 IF ((nvl(p_completed_percentage(i), 0) < 0) OR (nvl(p_retn_billing_percentage(i),0) < 0)
1907 OR (nvl(p_retn_billing_amount(i),0) < 0)) THEN
1908
1909 RAISE l_neg_not_allowed;
1910
1911 END IF;
1912
1913
1914
1915
1916 l_completed_percentage(i) := p_completed_percentage(i);
1917 l_retn_billing_percentage(i) := p_retn_billing_percentage(i);
1918 l_retn_billing_amount(i) := p_retn_billing_amount(i);
1919
1920
1921 END LOOP;
1922
1923 END IF;
1924
1925
1926 /* -------------------------------------------------------------
1927 Validating the Input
1928 -------------------------------------------------------------*/
1929
1930 l_rec_count := l_completed_percentage.COUNT;
1931
1932
1933 IF (l_rec_count > 0) THEN
1934
1935
1936 FOR I in 1..l_rec_count
1937 LOOP
1938
1939
1940 FOR J in 1..l_rec_count
1941 LOOP
1942
1943
1944 IF ((i <> j) AND (l_completed_percentage(I) = l_completed_percentage(J))) THEN
1945
1946 RAISE l_retn_per_comp_dup;
1947
1948 END IF;
1949
1950
1951 END LOOP ;
1952
1953 END LOOP ;
1954
1955 END IF;
1956
1957
1958
1959 /* ---------------------------------------------------------------------------------------------------
1960 Set the Record Version Number, This package will check for the database version number and
1961 What ever version number use in the program, If both are equal then return staus will be successful,
1962 If both are differnt then Other User is already updated the project Record so raising the error
1963 --------------------------------------------------------------------------------------------------- */
1964
1965
1966 pa_retention_util.set_rec_version_num ( p_project_id,
1967 p_customer_id,
1968 p_version_num,
1969 l_x_return_status,
1970 l_x_msg_count,
1971 l_x_msg_data
1972 );
1973
1974
1975 IF (l_x_return_status = 'E') THEN
1976
1977
1978 x_return_status := 'X';
1979 x_msg_count := l_x_msg_count;
1980 x_msg_data := l_x_msg_data;
1981
1982
1983 return;
1984
1985
1986 END IF;
1987
1988
1989
1990
1991
1992 /* ---------------------------------------------------------------
1993 Delete the Old Method from Database
1994 --------------------------------------------------------------- */
1995
1996 DELETE FROM pa_proj_retn_bill_rules
1997 WHERE project_id = p_project_id
1998 AND nvl(task_id, -99) = nvl(p_task_id, -99)
1999 AND customer_id = p_customer_id ;
2000
2001
2002 l_row_count := SQL%ROWCOUNT;
2003
2004
2005 IF (p_billing_mode = 'U') AND (l_row_count = 0) THEN
2006
2007
2008 RAISE l_used_other_method; /* Other User is Changed this method */
2009
2010
2011 END IF;
2012
2013
2014
2015 IF (l_completed_percentage.COUNT > 0) THEN
2016
2017
2018 FOR I IN 1 .. l_completed_percentage.COUNT
2019 LOOP
2020
2021 INSERT INTO pa_proj_retn_bill_rules
2022 ( PROJECT_ID ,
2023 CUSTOMER_ID ,
2024 TASK_ID,
2025 BILLING_METHOD_CODE,
2026 COMPLETED_PERCENTAGE,
2027 TOTAL_RETENTION_AMOUNT,
2028 RETN_BILLING_CYCLE_ID,
2029 CLIENT_EXTENSION_FLAG,
2030 RETN_BILLING_PERCENTAGE,
2031 RETN_BILLING_AMOUNT,
2032 CREATION_DATE,
2033 CREATED_BY,
2034 LAST_UPDATE_DATE,
2035 LAST_UPDATED_BY,
2036 RETN_BILLING_RULE_ID
2037 )
2038 VALUES(
2039 p_project_id,
2040 p_customer_id,
2041 p_task_id,
2042 p_billing_method_code,
2043 l_completed_percentage(i),
2044 p_total_retention_amount,
2045 p_retn_billing_cycle_id,
2046 p_client_extension_flag,
2047 l_retn_billing_percentage(i),
2048 l_retn_billing_amount(i),
2049 sysdate,
2050 -1,
2051 sysdate,
2052 -1,
2053 pa_proj_retn_bill_rules_s.nextval
2054 );
2055
2056
2057 END LOOP;
2058
2059 END IF;
2060
2061
2062
2063 EXCEPTION
2064 WHEN l_retn_per_comp_dup THEN
2065 x_return_status := FND_API.G_RET_STS_ERROR;
2066 x_msg_count := 1;
2067 x_msg_data := 'PA_RETN_BILL_PER_COMP_DUP';
2068 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2069 p_procedure_name => 'retn_billing_method_PerComp');
2070
2071 WHEN l_retn_per_comp_null THEN
2072 x_return_status := FND_API.G_RET_STS_ERROR;
2073 x_msg_count := 1;
2074 x_msg_data := 'PA_RETN_BILL_PER_COMP_NULL';
2075 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2076 p_procedure_name => 'retn_billing_method_PerComp');
2077
2078 WHEN l_bill_per_either_amount THEN
2079 x_return_status := FND_API.G_RET_STS_ERROR;
2080 x_msg_count := 1;
2081 x_msg_data := 'PA_RETN_PERC_AMNT_EXIST';
2082 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2083 p_procedure_name => 'retn_billing_method_PerComp');
2084
2085 WHEN l_bill_per_either_amount_null THEN
2086 x_return_status := FND_API.G_RET_STS_ERROR;
2087 x_msg_count := 1;
2088 x_msg_data := 'PA_RETN_PERC_AMNT_NO_EXIST';
2089 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2090 p_procedure_name => 'retn_billing_method_PerComp');
2091
2092 WHEN l_percentage_invalid THEN
2093 x_return_status := FND_API.G_RET_STS_ERROR;
2094 x_msg_count := 1;
2095 x_msg_data := 'PA_RETN_PERCENT_RANGE';
2096 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2097 p_procedure_name => 'retn_billing_method_PerComp');
2098 WHEN l_neg_not_allowed THEN
2099 x_return_status := FND_API.G_RET_STS_ERROR;
2100 x_msg_count := 1;
2101 x_msg_data := 'PA_RETN_NEG_VAL';
2102 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2103 p_procedure_name => 'retn_billing_method_PerComp');
2104 WHEN l_used_other_method THEN
2105 x_return_status := FND_API.G_RET_STS_ERROR;
2106 x_msg_count := 1;
2107 x_msg_data := 'PA_RETN_RECORD_CHANGED';
2108 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2109 p_procedure_name => 'retn_billing_method_PerComp');
2110 WHEN OTHERS THEN
2111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2112 x_msg_count := 1;
2113 x_msg_data := SQLERRM;
2114 -- dbms_output.put_line(SQLERRM);
2115 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2116 p_procedure_name => 'retn_billing_method_PerComp');
2117
2118 END retn_billing_method_PerComp ;
2119
2120
2121
2122
2123
2124
2125
2126 PROCEDURE retn_billing_task_validate(
2127 p_project_id IN VARCHAR2,
2128 P_task_name IN VARCHAR2,
2129 p_task_no IN VARCHAR2,
2130 p_customer_id IN VARCHAR2,
2131 p_retention_level IN VARCHAR2,
2132 x_task_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2133 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2134 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2135 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2136 IS
2137
2138
2139 l_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
2140
2141
2142 l_retn_task_name_null EXCEPTION;
2143 l_retn_task_no_null EXCEPTION;
2144 l_bill_rec_exists EXCEPTION;
2145
2146 l_message_code VARCHAR2(30);
2147
2148
2149 l_name_task_id NUMBER;
2150 l_no_task_id NUMBER;
2151 l_bill_rec_count NUMBER;
2152
2153
2154
2155
2156 BEGIN
2157
2158
2159
2160 x_return_status := FND_API.G_RET_STS_SUCCESS;
2161 x_msg_count := 0;
2162
2163
2164 --Clear the global PL/SQL message table
2165 IF FND_API.TO_BOOLEAN( l_init_msg_list ) THEN
2166 FND_MSG_PUB.initialize;
2167 END IF;
2168
2169
2170 l_message_code := NULL;
2171
2172
2173 /* If Retention Level is Task then validating the Task and also check this
2174 task already exists in pa_proj_retn_bill_rules table */
2175
2176 IF (p_retention_level = 'T') THEN
2177
2178
2179 IF ((p_task_name IS NOT NULL) AND (p_task_no IS NULL)) THEN
2180
2181
2182
2183 BEGIN
2184
2185 SELECT top_task_id
2186 INTO x_task_id
2187 FROM pa_tasks
2188 WHERE project_id = p_project_id
2189 AND task_id = top_task_id
2190 AND task_name = p_task_name;
2191
2192
2193 EXCEPTION
2194 WHEN NO_DATA_FOUND THEN
2195 l_message_code := 'PA_TASK_NAME_INVALID';
2196 WHEN TOO_MANY_ROWS THEN
2197 l_message_code := 'PA_TASK_NAME_INVALID';
2198
2199 END;
2200
2201
2202 END IF;
2203
2204
2205
2206 IF ((p_task_name IS NULL) AND (p_task_no IS NOT NULL)) THEN
2207
2208
2209 BEGIN
2210
2211
2212 SELECT top_task_id
2213 INTO x_task_id
2214 FROM pa_tasks
2215 WHERE project_id = p_project_id
2216 AND task_id = top_task_id
2217 AND task_number = p_task_no;
2218
2219
2220 EXCEPTION
2221 WHEN NO_DATA_FOUND THEN
2222 l_message_code := 'PA_TASK_NUMBER_INVALID';
2223 WHEN TOO_MANY_ROWS THEN
2224 l_message_code := 'PA_TASK_NUMBER_INVALID';
2225
2226
2227 END;
2228
2229 END IF;
2230
2231
2232
2233 IF ((p_task_name IS NOT NULL) AND (p_task_no IS NOT NULL)) THEN
2234
2235
2236
2237 BEGIN
2238
2239
2240 SELECT top_task_id
2241 INTO l_name_task_id
2242 FROM pa_tasks
2243 WHERE project_id = p_project_id
2244 AND task_id = top_task_id
2245 AND task_name = p_task_name;
2246
2247
2248 EXCEPTION
2249 WHEN NO_DATA_FOUND THEN
2250 l_name_task_id := -99;
2251 WHEN TOO_MANY_ROWS THEN
2252 l_name_task_id := -99;
2253
2254 END;
2255
2256
2257 BEGIN
2258
2259
2260 SELECT top_task_id
2261 INTO l_no_task_id
2262 FROM pa_tasks
2263 WHERE project_id = p_project_id
2264 AND task_id = top_task_id
2265 AND task_number = p_task_no;
2266
2267
2268
2269 EXCEPTION
2270 WHEN NO_DATA_FOUND THEN
2271 l_name_task_id := -99;
2272 WHEN TOO_MANY_ROWS THEN
2273 l_name_task_id := -99;
2274
2275 END;
2276
2277
2278 IF (l_name_task_id <> l_no_task_id) OR (l_name_task_id = -99) THEN
2279
2280
2281 l_message_code := 'PA_TASK_INVALID';
2282
2283 ELSE
2284
2285
2286 x_task_id := l_name_task_id;
2287
2288 END IF;
2289
2290
2291 END IF;
2292
2293
2294 IF ((p_task_name IS NULL) AND (p_task_no IS NULL)) THEN
2295
2296 l_message_code := 'PA_TASK_NULL';
2297
2298 END IF;
2299
2300
2301
2302
2303 IF (l_message_code IS NOT NULL) THEN
2304
2305
2306 x_return_status := FND_API.G_RET_STS_ERROR;
2307 x_msg_count := 1;
2308 x_msg_data := l_message_code;
2309
2310 END IF;
2311
2312
2313
2314 IF (l_message_code IS NULL) THEN
2315
2316
2317 SELECT count(*)
2318 INTO l_bill_rec_count
2319 FROM pa_proj_retn_bill_rules
2320 WHERE project_id = p_project_id
2321 AND task_id = x_task_id
2322 AND customer_id = p_customer_id;
2323
2324 IF (l_bill_rec_count > 0) THEN
2325
2326 RAISE l_bill_rec_exists;
2327
2328 END IF;
2329
2330
2331 END IF;
2332
2333
2334 ELSIF (p_retention_level = 'P') THEN
2335
2336
2337 SELECT count(*)
2338 INTO l_bill_rec_count
2339 FROM pa_proj_retn_bill_rules
2340 WHERE project_id = p_project_id
2341 AND customer_id = p_customer_id;
2342
2343
2344 IF (l_bill_rec_count > 0) THEN
2345
2346 RAISE l_bill_rec_exists;
2347
2348 END IF;
2349
2350
2351 END IF;
2352
2353 EXCEPTION
2354 WHEN l_bill_rec_exists THEN
2355 x_task_id := NULL; --NOCOPY
2356 x_return_status := FND_API.G_RET_STS_ERROR;
2357 x_msg_count := 1;
2358 x_msg_data := 'PA_RETN_BILL_REC_EXIST';
2359 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2360 p_procedure_name => 'retn_billing_task_validate');
2361
2362 WHEN OTHERS THEN
2363 x_task_id := NULL; --NOCOPY
2364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2365 x_msg_count := 1;
2366 x_msg_data := SQLERRM;
2367 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RETENTION_UTIL',
2368 p_procedure_name => 'retn_billing_task_validate');
2369
2370
2371
2372 END retn_billing_task_validate;
2373
2374 ---- Following APIs are added by Bhumesh K.
2375
2376 PROCEDURE Check_For_Overlap_Dates (
2377 P_RowID VARCHAR2,
2378 P_Project_ID NUMBER,
2379 P_Task_ID NUMBER,
2380 P_Customer_ID NUMBER,
2381 P_Retention_Level_Code VARCHAR2,
2382 P_Expenditure_Category VARCHAR2,
2383 P_Expenditure_Type VARCHAR2,
2384 P_Non_Labor_Resource VARCHAR2,
2385 P_Revenue_Category_Code VARCHAR2,
2386 P_Event_Type VARCHAR2,
2387 P_Effective_Start_Date DATE,
2388 P_Effective_End_Date DATE,
2389 X_Return_Status_Code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2390 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2391 )
2392 IS
2393 l_Exist_Flag VARCHAR2(1) := 'N';
2394 l_Max_Start_Date date;
2395 l_Max_End_Date date;
2396
2397 CURSOR Retention_Rules IS
2398 SELECT Effective_Start_Date, Effective_End_Date
2399 FROM PA_PROJ_RETN_RULES
2400 WHERE Project_ID = P_Project_ID
2401 AND NVL(Task_ID, -1) = NVL(P_Task_ID, -1)
2402 AND Customer_ID = P_Customer_ID
2403 AND Retention_Level_Code = P_Retention_Level_Code
2404 AND NVL(Expenditure_Category, 'X') = NVL(P_Expenditure_Category, 'X')
2405 AND NVL(Expenditure_Type, 'X') = NVL(P_Expenditure_Type, 'X')
2406 AND NVL(Non_Labor_Resource, 'X') = NVL(P_Non_Labor_Resource, 'X')
2407 AND NVL(Revenue_Category_Code, 'X')= NVL(P_Revenue_Category_Code, 'X')
2408 AND NVL(Event_Type, 'X') = NVL(P_Event_Type, 'X')
2409 AND decode(P_RowID, NULL, 'X', RowIDToChar(RowID))
2410 <> decode(P_RowID, NULL, 'Y', P_RowID );
2411
2412 BEGIN
2413
2414 -- dbms_output.put_line('Check for Start Date is Null');
2415 IF P_Effective_Start_Date IS NULL
2416 THEN
2417 X_Return_Status_Code := FND_API.G_RET_STS_ERROR; -- 'E';
2418 X_Error_Message_Code := 'PA_EFF_START_DATE_NULL';
2419 RETURN;
2420 END IF;
2421
2422 -- dbms_output.put_line('Check for End Date is Null');
2423 IF P_Effective_End_Date IS NOT NULL
2424 THEN
2425 IF P_Effective_End_Date < P_Effective_Start_Date
2426 THEN
2427 X_Return_Status_Code := FND_API.G_RET_STS_ERROR; -- 'E';
2428 X_Error_Message_Code := 'PA_INVALID_END_DATE';
2429 RETURN;
2430 END IF;
2431 END IF;
2432
2433 FOR Rules_Rec IN Retention_Rules
2434 LOOP
2435 BEGIN
2436 IF ( Rules_Rec.Effective_End_Date IS NULL AND
2437 P_Effective_Start_Date >= Rules_Rec.Effective_Start_Date ) OR
2438 ( Rules_Rec.Effective_End_Date IS NULL AND
2439 P_Effective_End_Date >= Rules_Rec.Effective_Start_Date )
2440 THEN
2441 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2442 X_Error_Message_Code := 'PA_SU_OVERLAP_RANGES';
2443 RETURN;
2444 END IF;
2445
2446 IF ( P_Effective_Start_Date BETWEEN
2447 Rules_Rec.Effective_Start_Date AND Rules_Rec.Effective_End_Date ) OR
2448 ( P_Effective_End_Date BETWEEN
2449 Rules_Rec.Effective_Start_Date AND Rules_Rec.Effective_End_Date )
2450 THEN
2451 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2452 X_Error_Message_Code := 'PA_SU_OVERLAP_RANGES';
2453 RETURN;
2454 END IF;
2455
2456 IF ( Rules_Rec.Effective_End_Date IS NOT NULL AND
2457 ( P_Effective_Start_Date <= Rules_Rec.Effective_Start_Date OR
2458 P_Effective_Start_Date <= Rules_Rec.Effective_End_Date ) AND
2459 P_Effective_End_Date >= Rules_Rec.Effective_Start_Date )
2460 THEN
2461 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2462 X_Error_Message_Code := 'PA_SU_OVERLAP_RANGES';
2463 RETURN;
2464 END IF;
2465
2466 IF Rules_Rec.Effective_End_Date IS NOT NULL AND
2467 P_Effective_Start_Date <= Rules_Rec.Effective_Start_Date AND
2468 P_Effective_End_Date IS NULL
2469 THEN
2470 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2471 X_Error_Message_Code := 'PA_SU_OVERLAP_RANGES';
2472 RETURN;
2473 END IF;
2474 END;
2475 END LOOP;
2476
2477 END Check_For_Overlap_Dates;
2478
2479
2480 PROCEDURE Validate_Expenditure_Category (
2481 P_Expenditure_Category IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2482 P_Expenditure_Type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2483 P_Non_Labor_Resource IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2484 X_Return_Status_Code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2485 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2486 )
2487 IS
2488
2489 l_expenditure_type varchar2(30) := p_expenditure_type;
2490 l_expenditure_category varchar2(30) := p_expenditure_category;
2491 l_non_labor_resource varchar2(30) := p_non_labor_resource ;
2492 BEGIN
2493
2494 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS;
2495
2496 IF P_Expenditure_Category IS NULL
2497 THEN
2498 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2499 X_Error_Message_Code := 'PA_NO_EXPENDITURE_CATEGORY';
2500 RETURN;
2501 END IF;
2502
2503 IF P_Expenditure_Category IS NOT NULL AND
2504 P_Non_Labor_Resource IS NOT NULL AND
2505 P_Expenditure_Type IS NULL
2506 THEN
2507 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2508 X_Error_Message_Code := 'PA_INVALID_EXPENDITURE_TYPE';
2509 RETURN;
2510 END IF;
2511
2512 BEGIN
2513 SELECT
2514 Expenditure_Category
2515 INTO
2516 P_Expenditure_Category
2517 FROM
2518 PA_EXPENDITURE_CATEGORIES
2519 WHERE
2520 upper(Expenditure_Category) = upper(P_Expenditure_Category);
2521 EXCEPTION
2522 WHEN NO_DATA_FOUND THEN
2523 P_expenditure_category := l_expenditure_category; -- NOCOPY
2524 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2525 X_Error_Message_Code := 'PA_NO_EXPENDITURE_CATEGORY';
2526 RETURN;
2527 END;
2528
2529 IF P_Expenditure_Type IS NOT NULL AND
2530 P_Expenditure_Category IS NOT NULL AND
2531 X_Return_Status_Code <> FND_API.G_RET_STS_ERROR
2532 THEN
2533 BEGIN
2534 SELECT
2535 Expenditure_Type
2536 INTO
2537 P_Expenditure_Type
2538 FROM
2539 PA_EXPENDITURE_TYPES
2540 WHERE
2541 upper(Expenditure_Category) = upper(P_Expenditure_Category)
2542 AND upper(Expenditure_Type) = upper(P_Expenditure_Type);
2543 EXCEPTION
2544 WHEN NO_DATA_FOUND THEN
2545 P_expenditure_category := l_expenditure_category; -- NOCOPY
2546 P_expenditure_type := l_expenditure_type; -- NOCOPY
2547 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2548 X_Error_Message_Code := 'PA_INVALID_EXPENDITURE_TYPE';
2549 RETURN;
2550 END;
2551 END IF;
2552
2553 IF P_Non_Labor_Resource IS NOT NULL AND
2554 P_Expenditure_Type IS NOT NULL AND
2555 P_Expenditure_Category IS NOT NULL AND
2556 X_Return_Status_Code <> FND_API.G_RET_STS_ERROR
2557 THEN
2558 BEGIN
2559 SELECT
2560 Non_Labor_Resource
2561 INTO
2562 P_Non_Labor_Resource
2563 FROM
2564 PA_NON_LABOR_RESOURCES
2565 WHERE
2566 upper(Non_Labor_Resource) = upper(P_Non_Labor_Resource)
2567 AND upper(Expenditure_Type) = upper(P_Expenditure_Type);
2568 EXCEPTION
2569 WHEN NO_DATA_FOUND THEN
2570 P_expenditure_category := l_expenditure_category; -- NOCOPY
2571 P_expenditure_type := l_expenditure_type; -- NOCOPY
2572 P_non_labor_resource := l_non_labor_resource; -- NOCOPY
2573 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2574 X_Error_Message_Code := 'PA_NLR_INV_FOR_EXP_TYPE';
2575 RETURN;
2576 END;
2577 END IF;
2578
2579 END Validate_Expenditure_Category;
2580
2581
2582 PROCEDURE Validate_Revenue_Category (
2583 P_Revenue_Category_Code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2584 P_Revenue_Category IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2585 P_Event_Type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2586 X_Return_Status_Code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2587 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2588 )
2589 IS
2590 l_Exist_Flag VARCHAR2(1) := '';
2591 l_revenue_category varchar2(30) := p_revenue_category;
2592 l_event_type varchar2(30) := p_event_type;
2593 BEGIN
2594
2595 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS;
2596
2597 IF P_Revenue_Category IS NULL
2598 THEN
2599 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2600 X_Error_Message_Code := 'PA_NO_REVENUE_CATEGORY';
2601 RETURN;
2602 END IF;
2603
2604 BEGIN
2605 SELECT Lookup_Code, Meaning
2606 INTO P_Revenue_Category_Code, P_Revenue_Category
2607 FROM PA_LOOKUPS
2608 WHERE Lookup_Type = 'REVENUE CATEGORY'
2609 AND upper(Meaning) = upper(P_Revenue_Category);
2610 EXCEPTION
2611 WHEN OTHERS THEN
2612 p_revenue_category := l_revenue_category; --NOCOPY
2613 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2614 X_Error_Message_Code := 'PA_NO_REVENUE_CATEGORY';
2615 RETURN;
2616 END;
2617
2618 IF P_Event_Type IS NOT NULL AND
2619 P_Revenue_Category_Code IS NOT NULL
2620 THEN
2621 BEGIN
2622 SELECT Event_Type
2623 INTO P_Event_Type
2624 FROM PA_EVENT_TYPES
2625 WHERE upper(Revenue_Category_Code) = upper(P_Revenue_Category_Code)
2626 AND upper(Event_Type) = upper(P_Event_Type);
2627 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS;
2628 EXCEPTION
2629 WHEN OTHERS THEN
2630 p_revenue_category_code := null; --NOCOPY
2631 p_revenue_category := l_revenue_category; --NOCOPY
2632 p_event_type := l_event_type; --NOCOPY
2633 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2634 X_Error_Message_Code := 'PA_EVENT_INV_FOR_REV_CATEG';
2635 RETURN;
2636 END;
2637 END IF;
2638
2639 END Validate_Revenue_Category;
2640
2641
2642 PROCEDURE Delete_Retentions (
2643 P_Project_ID NUMBER,
2644 P_Customer_ID NUMBER,
2645 X_Return_Status_Code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2646 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2647 )
2648 IS
2649 l_Exist_Flag VARCHAR2(1) := '';
2650 BEGIN
2651
2652 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS; -- 'S';
2653
2654 BEGIN
2655 DELETE FROM PA_PROJ_RETN_RULES
2656 WHERE Project_ID = P_Project_ID
2657 AND Customer_ID = P_Customer_ID;
2658
2659 DELETE FROM PA_PROJ_RETN_BILL_RULES
2660 WHERE Project_ID = P_Project_ID
2661 AND Customer_ID = P_Customer_ID;
2662
2663 UPDATE PA_PROJECT_CUSTOMERS
2664 SET Retention_Level_Code = ''
2665 WHERE Project_ID = P_Project_ID
2666 AND Customer_ID = P_Customer_ID;
2667
2668 COMMIT;
2669
2670 EXCEPTION
2671 WHEN OTHERS THEN
2672 X_Return_Status_Code := FND_API.G_RET_STS_ERROR; -- 'E';
2673 X_Error_Message_Code := 'PA_DATA_ERROR';
2674 END;
2675 END Delete_Retentions;
2676
2677
2678 PROCEDURE Check_Top_Task_Details (
2679 P_Project_ID NUMBER,
2680 P_Task_Number VARCHAR2,
2681 P_Task_Name VARCHAR2,
2682 X_Task_ID IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2683 X_Return_Status_Code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2684 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2685 ) IS
2686
2687 l_task_id number := x_task_id;
2688 BEGIN
2689
2690 BEGIN
2691 SELECT Task_ID
2692 INTO X_Task_ID
2693 FROM PA_TASKS
2694 WHERE Project_ID = P_Project_ID
2695 AND upper(Task_Number) = upper(P_Task_Number)
2696 AND upper(Task_Name) = upper(P_Task_Name) ;
2697 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS;
2698 EXCEPTION WHEN OTHERS THEN
2699 X_Task_ID := l_task_id;
2700 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2701 X_Error_Message_Code := 'PA_TASK_INVALID';
2702 END;
2703
2704 END Check_Top_Task_Details;
2705
2706 PROCEDURE Delete_Bill_Retentions (
2707 P_Bill_Rule_ID NUMBER,
2708 X_Return_Status_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2709 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2710 ) IS
2711
2712 BEGIN
2713
2714 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS; -- 'S';
2715
2716 DELETE FROM
2717 PA_PROJ_RETN_BILL_RULES
2718 WHERE
2719 RETN_BILLING_RULE_ID = P_Bill_Rule_ID ;
2720 COMMIT;
2721
2722 EXCEPTION
2723 WHEN OTHERS THEN
2724 X_Return_Status_Code := FND_API.G_RET_STS_ERROR; -- 'E';
2725 X_Error_Message_Code := 'PA_DATA_ERROR';
2726
2727 END Delete_Bill_Retentions ;
2728
2729 PROCEDURE Check_Billing_Retentions (
2730 P_Project_ID NUMBER,
2731 P_Customer_ID NUMBER,
2732 X_Return_Status_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2733 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2734 ) IS
2735 l_Exist_Flag NUMBER;
2736 BEGIN
2737 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS; -- 'S';
2738 BEGIN
2739 SELECT
2740 1
2741 INTO
2742 l_Exist_Flag
2743 FROM
2744 PA_PROJ_RETN_BILL_RULES
2745 WHERE
2746 Project_ID = P_Project_ID
2747 AND Customer_ID = P_Customer_ID
2748 AND RowNum < 2;
2749 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS; -- 'S';
2750 X_Error_Message_Code := '';
2751 EXCEPTION
2752 WHEN OTHERS THEN
2753 X_Return_Status_Code := FND_API.G_RET_STS_ERROR; -- 'E';
2754 X_Error_Message_Code := '';
2755 END;
2756
2757 END Check_Billing_Retentions ;
2758
2759
2760 PROCEDURE Validate_Retention_Data (
2761 P_RowID VARCHAR2,
2762 P_Project_ID NUMBER,
2763 P_Task_Number VARCHAR2,
2764 P_Task_Name VARCHAR2,
2765 P_Customer_ID NUMBER,
2766 P_Retention_Level_Code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2767 P_Expenditure_Category IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2768 P_Expenditure_Type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2769 P_Non_Labor_Resource IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2770 P_Revenue_Category IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2771 P_Event_Type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2772 P_Retention_Percentage NUMBER,
2773 P_Retention_Amount NUMBER,
2774 P_Threshold_Amount NUMBER,
2775 P_Effective_Start_Date DATE,
2776 P_Effective_End_Date DATE,
2777 P_Task_Flag VARCHAR2,
2778 X_Task_ID IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2779 X_Revenue_Category_Code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2780 X_Return_Status_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2781 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2782 ) IS
2783
2784 l_retention_level_code varchar2(30) := P_Retention_Level_Code;
2785 l_Expenditure_Category varchar2(30) := P_Expenditure_Category;
2786 l_Expenditure_Type varchar2(30) := P_Expenditure_Type;
2787 l_Non_Labor_Resource varchar2(30) := P_Non_Labor_Resource;
2788 l_Revenue_Category varchar2(30) := P_Revenue_Category;
2789 l_Event_Type varchar2(30) := P_Event_Type;
2790 l_Task_ID varchar2(30) := X_Task_ID ;
2791 l_Revenue_Category_Code varchar2(30) := X_Revenue_Category_Code;
2792
2793
2794
2795 BEGIN
2796
2797 IF (P_Task_Number IS NULL AND
2798 P_Task_Name IS NULL AND
2799 P_Expenditure_Category IS NULL AND
2800 P_Expenditure_Type IS NULL AND
2801 P_Non_Labor_Resource IS NULL AND
2802 P_Revenue_Category IS NULL AND
2803 P_Event_Type IS NULL AND
2804 P_Retention_Percentage IS NULL AND
2805 P_Retention_Amount IS NULL AND
2806 P_Threshold_Amount IS NULL AND
2807 P_Effective_Start_Date IS NULL AND
2808 P_Effective_End_Date IS NULL
2809 )
2810 THEN
2811 RETURN;
2812 END IF;
2813
2814 IF P_Task_Flag = 'Y' AND P_Task_Number IS NULL
2815 THEN
2816 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2817 X_Error_Message_Code := 'PA_TASK_NUMBER_INVALID'; -- 'PA_NO_TASK_NUMBER';
2818 RETURN;
2819 END IF;
2820
2821 IF P_Task_Flag = 'Y' AND P_Task_Name IS NULL
2822 THEN
2823 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2824 X_Error_Message_Code := 'PA_TASK_NAME_INVALID'; -- 'PA_NO_TASK_NAME';
2825 RETURN;
2826 END IF;
2827
2828 IF P_Task_Number IS NOT NULL AND P_Task_Name IS NOT NULL
2829 THEN
2830 Check_Top_Task_Details (
2831 P_Project_ID => P_Project_ID,
2832 P_Task_Number => P_Task_Number,
2833 P_Task_Name => P_Task_Name,
2834 X_Task_ID => X_Task_ID,
2835 X_Return_Status_Code => X_Return_Status_Code,
2836 X_Error_Message_Code => X_Error_Message_Code );
2837
2838 IF X_Return_Status_Code = FND_API.G_RET_STS_ERROR
2839 THEN
2840 RETURN;
2841 END IF;
2842
2843 END IF;
2844
2845 IF P_Retention_Percentage IS NULL AND P_Retention_Amount IS NULL
2846 THEN
2847 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2848 X_Error_Message_Code := 'PA_RETN_PERC_OR_AMT_ENTER';
2849 RETURN;
2850 END IF;
2851
2852 IF P_Retention_Percentage > 0 AND P_Retention_Amount > 0
2853 THEN
2854 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2855 X_Error_Message_Code := 'PA_RETN_PERC_OR_AMT_ENTER';
2856 RETURN;
2857 END IF;
2858
2859 IF P_Retention_Percentage NOT BETWEEN 0 AND 100
2860 THEN
2861 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2862 X_Error_Message_Code := 'PA_RETN_PERCENT_RANGE';
2863 RETURN;
2864 END IF;
2865
2866 IF P_Retention_Percentage < 0 OR
2867 P_Retention_Amount < 0 OR
2868 P_Threshold_Amount < 0
2869 THEN
2870 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2871 X_Error_Message_Code := 'PA_RETN_NEG_VAL';
2872 RETURN;
2873 END IF;
2874
2875 IF (P_Threshold_Amount < P_Retention_Amount) AND
2876 (P_Retention_Amount IS NOT NULL)
2877 THEN
2878 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
2879 X_Error_Message_Code := 'PA_RETN_INV_THRESHOLD_AMOUNT';
2880 RETURN;
2881 END IF;
2882
2883 IF P_Retention_Level_Code IN ('EXPENDITURE_CATEGORY', 'EXPENDITURE_TYPE', 'NON_LABOR')
2884 THEN
2885 PA_Retention_Util.Validate_Expenditure_Category (
2886 P_Expenditure_Category => P_Expenditure_Category,
2887 P_Expenditure_Type => P_Expenditure_Type,
2888 P_NON_Labor_Resource => P_Non_Labor_Resource,
2889 X_Return_Status_Code => X_Return_Status_code,
2890 X_Error_Message_Code => X_Error_Message_Code
2891 );
2892 IF X_Return_Status_Code = FND_API.G_RET_STS_SUCCESS
2893 THEN
2894 IF P_Expenditure_Category IS NOT NULL
2895 THEN
2896 P_Retention_Level_Code := 'EXPENDITURE_CATEGORY';
2897 END IF;
2898 IF P_Expenditure_Type IS NOT NULL
2899 THEN
2900 P_Retention_Level_Code := 'EXPENDITURE_TYPE';
2901 END IF;
2902 IF P_NON_Labor_Resource IS NOT NULL
2903 THEN
2904 P_Retention_Level_Code := 'NON_LABOR';
2905 END IF;
2906 END IF;
2907 END IF;
2908
2909 IF P_Retention_Level_Code IN ('REVENUE_CATEGORY', 'EVENT_TYPE')
2910 THEN
2911 PA_Retention_Util.Validate_Revenue_Category (
2912 P_Revenue_Category_Code => X_Revenue_Category_Code,
2913 P_Revenue_Category => P_Revenue_Category,
2914 P_Event_Type => P_Event_Type,
2915 X_Return_Status_Code => X_Return_Status_code,
2916 X_Error_Message_Code => X_Error_Message_Code
2917 );
2918 IF X_Return_Status_Code = FND_API.G_RET_STS_SUCCESS
2919 THEN
2920 IF P_Revenue_Category IS NOT NULL
2921 THEN
2922 P_Retention_Level_Code := 'REVENUE_CATEGORY';
2923 END IF;
2924 IF P_Event_Type IS NOT NULL
2925 THEN
2926 P_Retention_Level_Code := 'EVENT_TYPE';
2927 END IF;
2928 END IF;
2929 END IF;
2930
2931 IF X_Return_Status_Code = FND_API.G_RET_STS_ERROR
2932 THEN
2933 RETURN;
2934 END IF;
2935
2936 --- Performing Validations
2937 PA_Retention_Util.Check_For_Overlap_Dates (
2938 P_RowID => P_RowID,
2939 P_PROJECT_ID => P_Project_ID,
2940 P_Task_ID => X_Task_ID,
2941 P_CUSTOMER_ID => P_Customer_ID,
2942 P_Retention_Level_Code => P_Retention_Level_Code,
2943 P_Expenditure_Category => P_Expenditure_Category,
2944 P_Expenditure_Type => P_Expenditure_Type,
2945 P_Non_Labor_Resource => P_Non_Labor_Resource,
2946 P_Revenue_Category_Code => X_Revenue_Category_Code,
2947 P_Event_Type => P_Event_Type,
2948 P_EFFECTIVE_START_DATE => P_Effective_Start_Date,
2949 P_EFFECTIVE_END_DATE => P_Effective_End_Date,
2950 X_RETURN_STATUS_CODE => X_RETURN_STATUS_CODE,
2951 X_ERROR_MESSAGE_CODE => X_ERROR_MESSAGE_CODE
2952 );
2953
2954 IF X_Return_Status_Code = FND_API.G_RET_STS_ERROR
2955 THEN
2956 RETURN;
2957 END IF;
2958 EXCEPTION
2959 when no_data_found then
2960
2961 p_retention_level_code := l_Retention_Level_Code;
2962 p_Expenditure_Category := l_Expenditure_Category;
2963 p_Expenditure_Type := l_Expenditure_Type;
2964 p_Non_Labor_Resource := l_Non_Labor_Resource;
2965 p_Revenue_Category := l_Revenue_Category;
2966 p_Event_Type := l_Event_Type;
2967 x_Task_ID := l_Task_ID ;
2968 x_Revenue_Category_Code := l_Revenue_Category_Code;
2969
2970 END Validate_Retention_Data;
2971
2972 PROCEDURE Check_Retention_Rules (
2973 P_Project_ID NUMBER,
2974 P_Customer_ID NUMBER,
2975 X_Return_Value IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2976 X_Return_Status_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2977 X_Error_Message_Code IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2978 ) IS
2979
2980 l_Retained_Count NUMBER := 0;
2981 l_NonRetained_Count NUMBER := 0;
2982 l_Billing_Rules_Count NUMBER := 0;
2983
2984 BEGIN
2985 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS; -- 'S';
2986 BEGIN
2987 SELECT
2988 NVL(sum( decode(nvl(total_retained,0),0,1,0)),0),
2989 NVL(sum( decode(nvl(total_retained,0),0,0,1)),0)
2990 INTO
2991 l_NonRetained_Count,
2992 l_Retained_Count
2993 FROM
2994 PA_PROJ_RETN_RULES
2995 WHERE
2996 Project_ID = P_Project_ID
2997 AND Customer_ID = P_Customer_ID;
2998 END;
2999
3000 BEGIN
3001 SELECT 1
3002 INTO l_Billing_Rules_Count
3003 FROM
3004 PA_PROJ_RETN_BILL_RULES
3005 WHERE
3006 Project_ID = P_Project_ID
3007 AND Customer_ID = P_Customer_ID
3008 AND RowNum < 2;
3009 EXCEPTION WHEN OTHERS THEN
3010 l_Billing_Rules_Count := 0;
3011 END;
3012
3013 -- Dbms_Output.Put_Line('l_NonRetained_Count : '||l_NonRetained_Count);
3014 -- Dbms_Output.Put_Line('l_Retained_Count : '||l_Retained_Count);
3015 -- Dbms_Output.Put_Line('l_Billing_Rules_Count : '||l_Billing_Rules_Count);
3016
3017 -- Disable Both the buttons
3018 IF l_Retained_Count > 0
3019 THEN
3020 X_Return_Value := 0;
3021 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
3022 RETURN;
3023 END IF;
3024
3025 IF ( l_NonRetained_Count = 0 AND
3026 l_Retained_Count = 0 AND
3027 l_Billing_Rules_Count = 0 )
3028 THEN
3029 X_Return_Value := 2;
3030 X_Return_Status_Code := FND_API.G_RET_STS_ERROR;
3031 RETURN;
3032 END IF;
3033
3034 -- Enable Both the buttons
3035 IF l_NonRetained_Count > 0 OR l_Billing_Rules_Count > 0
3036 THEN
3037 X_Return_Value := 1;
3038 X_Return_Status_Code := FND_API.G_RET_STS_SUCCESS;
3039 RETURN;
3040 END IF;
3041
3042 END Check_Retention_Rules ;
3043 /*----------------------------------------------------------------------------------------+
3044 | Function : IsRetentionExists |
3045 | Purpose : To find the retention setup exists or not |
3046 | Parameters : |
3047 | ================================================================================== |
3048 | Name Mode Description |
3049 | ================================================================================== |
3050 | p_project_id IN Project Id |
3051 | p_retn_inv_fmt IN Invoice Format ID |
3052 | ================================================================================== |
3053 +----------------------------------------------------------------------------------------*/
3054
3055 FUNCTION CheckRetnInvFormat(p_project_id IN NUMBER,
3056 p_retn_inv_fmt IN NUMBER) RETURN NUMBER IS
3057 l_retn_invfmt_error NUMBER:=0;
3058 BEGIN
3059 BEGIN
3060
3061 SELECT 1
3062 INTO l_retn_invfmt_error
3063 FROM dual
3064 WHERE EXISTS(SELECT null
3065 FROM pa_proj_retn_rules rtn
3066 WHERE rtn.project_id = p_project_id);
3067
3068 IF l_retn_invfmt_error = 1 THEN
3069 IF NVL(p_retn_inv_fmt,0)<>0 THEN
3070 l_retn_invfmt_error := 0;
3071 ELSE
3072 l_retn_invfmt_error := 1;
3073 END IF;
3074 END IF;
3075
3076 EXCEPTION
3077 WHEN NO_DATA_FOUND THEN
3078 l_retn_invfmt_error := 0;
3079 END;
3080
3081 RETURN(l_retn_invfmt_error);
3082 END CheckRetnInvFormat;
3083
3084
3085 END pa_retention_util;