1 Package Body PA_Invoice_Actions as
2 /* $Header: PAXVIACB.pls 120.10 2011/04/01 22:43:24 apaul ship $ */
3
4 /*----------------- Private Procedure/Function Declarations -----------------*/
5
6 /*----------------------------------------------------------------------------+
7 | This Private Procedure Update_Approve_Invoices Updates |
8 | PA_DRAFT_INVOICES_ALL table with invoice approval columns |
9 +----------------------------------------------------------------------------*/
10 Procedure Update_Approve_Invoices ( P_Project_ID in number,
11 P_Draft_Invoice_Num in number,
12 P_User_ID in number,
13 P_Employee_ID in number) is
14 BEGIN
15
16 UPDATE PA_Draft_Invoices_ALL
17 SET Approved_Date = sysdate,
18 Approved_by_person_id = P_Employee_ID,
19 Last_Update_Date = sysdate,
20 Last_Updated_By = P_User_ID
21 WHERE Project_ID = P_Project_ID
22 AND Draft_Invoice_Num = P_Draft_Invoice_Num;
23
24 EXCEPTION
25 WHEN OTHERS THEN
26 RAISE;
27 END Update_Approve_Invoices;
28
29 /*----------------------------------------------------------------------------+
30 | This Private Procedure Update_Unapprove_Invoices Updates |
31 | PA_DRAFT_INVOICES_ALL table with invoice approval columns as NULL |
32 +----------------------------------------------------------------------------*/
33 Procedure Update_Unapprove_Invoices ( P_Invoice_Set_ID in number,
34 P_User_ID in number) is
35 BEGIN
36
37 UPDATE PA_Draft_Invoices_ALL
38 SET Approved_Date = NULL,
39 Approved_by_person_id = NULL,
40 Released_Date = NULL,
41 Released_by_person_id = NULL,
42 RA_Invoice_Number = NULL,
43 Invoice_Date = NULL,
44 Last_Update_Date = sysdate,
45 Last_Updated_By = P_User_ID
46 WHERE Invoice_Set_ID = P_Invoice_Set_ID;
47
48 EXCEPTION
49 WHEN OTHERS THEN
50 RAISE;
51 END Update_Unapprove_Invoices;
52
53 /*----------------------------------------------------------------------------+
54 | This Private Procedure Update_Release_Invoices Updates |
55 | PA_DRAFT_INVOICES_ALL table with invoice Release attributes |
56 +----------------------------------------------------------------------------*/
57 Procedure Update_Release_Invoices ( P_Project_ID in number,
58 P_Draft_Invoice_Num in number,
59 P_RA_Invoice_Date in date,
60 P_RA_Invoice_Num in varchar2,
61 P_User_ID in number,
62 P_Employee_ID in number,
63 P_Credit_Memo_Reason_Code in varchar2) is
64 BEGIN
65
66 UPDATE PA_Draft_Invoices_ALL
67 SET Released_Date = sysdate,
68 Released_by_person_id = P_Employee_ID,
69 RA_Invoice_Number = P_RA_Invoice_Num,
70 Invoice_Date = P_RA_Invoice_Date,
71 Last_Update_Date = sysdate,
72 Last_Updated_By = P_User_ID,
73 Credit_Memo_Reason_Code = P_Credit_Memo_Reason_Code /* Bug #2728431*/
74 WHERE Project_ID = P_Project_ID
75 AND Draft_Invoice_Num = P_Draft_Invoice_Num;
76
77
78 EXCEPTION
79 WHEN OTHERS THEN
80 RAISE;
81 END Update_Release_Invoices;
82
83
84 /*----------------------------------------------------------------------------+
85 | This Private Procedure Update_Unrelease_Invoices Updates |
86 | PA_DRAFT_INVOICES_ALL table with invoice Release attributes as NULL |
87 +----------------------------------------------------------------------------*/
88 Procedure Update_Unrelease_Invoices ( P_Invoice_Set_ID in number,
89 P_User_ID in number) is
90 BEGIN
91
92 UPDATE PA_Draft_Invoices_ALL
93 SET Released_Date = NULL,
94 Released_by_person_id = NULL,
95 RA_Invoice_Number = NULL,
96 Invoice_Date = NULL,
97 Last_Update_Date = sysdate,
98 Last_Updated_By = P_User_ID,
99 Credit_memo_reason_code = NULL /* Bug #2728431*/
100 WHERE Invoice_Set_ID = P_Invoice_Set_ID;
101
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 RAISE;
106 END Update_Unrelease_Invoices;
107
108
109 /*----------------------------------------------------------------------------+
110 | This Private Procedure Insert_Distrbution_Warning Inserts draft Invoice |
111 | distribution warning. |
112 +----------------------------------------------------------------------------*/
113 Procedure Insert_Distrbution_Warning ( P_Project_ID in number,
114 P_Draft_Invoice_Num in number,
115 P_User_ID in number,
116 P_Request_ID in number,
117 P_Invoice_Set_ID in number,
118 P_Error_Message_Code in varchar2) is
119
120 l_error_message pa_lookups.meaning%TYPE;
121
122 BEGIN
123
124 BEGIN
125 SELECT Meaning
126 INTO l_error_message
127 FROM PA_Lookups
128 WHERE Lookup_Type = 'BILLING EXTENSION MESSAGES'
129 AND Lookup_Code = P_Error_Message_Code;
130
131 EXCEPTION
132 WHEN NO_DATA_FOUND THEN
133 l_error_message := P_Error_Message_Code;
134 END;
135
136 IF (P_Invoice_Set_ID is NULL) THEN
137
138 INSERT INTO PA_DISTRIBUTION_WARNINGS
139 (
140 PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
141 CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
142 )
143 VALUES
144 (
145 P_Project_ID, P_Draft_Invoice_Num, sysdate, P_User_ID,
146 sysdate, P_User_ID, P_Request_ID, l_error_message
147 );
148
149 ELSE
150
151 INSERT INTO PA_DISTRIBUTION_WARNINGS
152 (
153 PROJECT_ID, DRAFT_INVOICE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY,
154 CREATION_DATE, CREATED_BY, REQUEST_ID, WARNING_MESSAGE
155 )
156 SELECT Project_ID, Draft_Invoice_Num, sysdate, P_User_ID,
157 sysdate, P_User_ID, P_Request_ID, l_error_message
158 FROM PA_Draft_Invoices_ALL
159 WHERE Invoice_Set_ID = P_Invoice_Set_ID;
160
161 END IF;
162
163 EXCEPTION
164 WHEN OTHERS THEN
165 RAISE;
166 END Insert_Distrbution_Warning;
167
168
169 /*----------------------------------------------------------------------------+
170 | This Private Procedure Validate_Credit_Memo_Reason validates the |
171 | credit memo reason_Code |
172 +----------------------------------------------------------------------------*/
173 Procedure Validate_Credit_Memo_Reason( P_Project_ID in number,
174 P_Draft_Invoice_Num in number,
175 P_RA_Invoice_Date in date,
176 P_Credit_Memo_Reason_Code in out NOCOPY varchar2, --File.Sql.39 bug 4440895
177 X_Error_Message_Code out NOCOPY varchar2) is --File.Sql.39 bug 4440895
178 l_Credit_memo_Reason_flag pa_implementations.credit_Memo_Reason_Flag%TYPE :='N';
179 l_credit_memo_exist VARCHAR2(1):='N';
180 l_credit_memo_reason_valid VARCHAR2(1):='N';
181 Cursor Credit_Memo_Reason_Flag is
182 SELECT Credit_Memo_Reason_Flag
183 FROM pa_implementations;
184
185 Cursor Credit_Memo_exist is
186 SELECT 'Y'
187 FROM dual
188 WHERE EXISTS(SELECT draft_invoice_num
189 FROM pa_draft_invoices i
190 WHERE i.project_id = P_Project_ID
191 AND i.draft_invoice_num = P_Draft_Invoice_Num
192 AND i.draft_invoice_num_credited IS NOT NULL);
193
194 Cursor Credit_Memo_Reason_Valid is
195 SELECT 'Y' FROM dual
196 WHERE EXISTS( select lookup_code
197 from fnd_lookup_values_vl
198 where lookup_type='CREDIT_MEMO_REASON'
199 and lookup_code = P_Credit_Memo_Reason_Code
200 and enabled_flag='Y'
201 and P_RA_invoice_date between start_date_active and nvl(end_date_active,P_RA_invoice_date));
202
203
204 BEGIN
205 X_Error_Message_Code :=NULL;
206 /* Checking the implementation options for Credit Memo Reason Required */
207 OPEN Credit_Memo_Reason_Flag;
208 FETCH Credit_Memo_Reason_Flag into l_Credit_Memo_Reason_Flag;
209 CLOSE Credit_Memo_Reason_Flag;
210
211 /* Checking Whether its a Credit Memo Invoice */
212 OPEN Credit_Memo_exist;
213 FETCH Credit_Memo_exist into l_Credit_Memo_exist;
214
215 IF Credit_Memo_exist%NOTFOUND then
216 l_Credit_Memo_exist := 'N';
217 END IF;
218
219 CLOSE Credit_Memo_exist;
220
221 /* Checking Valid Credit Memo Reason Code */
222 IF P_Credit_Memo_Reason_Code IS NOT NULL THEN
223 OPEN Credit_Memo_Reason_valid;
224 FETCH Credit_Memo_Reason_valid into l_Credit_Memo_Reason_valid;
225 IF Credit_Memo_Reason_Valid%NOTFOUND then
226 l_Credit_Memo_Reason_valid := 'N';
227 END IF;
228 CLOSE Credit_Memo_Reason_valid;
229 END IF;
230
231 IF l_credit_memo_exist ='Y' THEN
232 IF l_Credit_Memo_Reason_Flag ='Y' THEN
233 IF P_Credit_Memo_Reason_Code IS NOT NULL THEN
234 IF l_Credit_Memo_Reason_valid <>'Y' THEN
235 X_Error_Message_Code := 'PA_IN_INV_CR_MEMO_REASON';
236 RETURN;
237 END IF;
238 ELSE
239 X_Error_Message_Code := 'PA_IN_REQ_CR_MEMO_REASON';
240 RETURN;
241 END IF;
242 ELSE
243 IF P_Credit_Memo_Reason_Code IS NOT NULL THEN
244 IF l_Credit_Memo_Reason_valid <>'Y' THEN
245 X_Error_Message_Code := 'PA_IN_INV_CR_MEMO_REASON';
246 RETURN;
247 END IF;
248 END IF;
249 END IF;/* End of l_Credit_Memo_Reason_Flag ='Y'*/
250
251 ELSE
252 P_Credit_Memo_Reason_Code:=NULL;
253 END IF;/* End of l_credit_memo_exist ='Y'*/
254
255 END Validate_Credit_Memo_Reason;
256
257 /*------------- End of Private Procedure/Function Declarations ---------------*/
258
259
260 /*----------------- Public Procedure/Function Declarations -------------------*/
261
262 /*----------------------------------------------------------------------------+
263 | For Details/Comments Refer Package Specification Comments |
264 +----------------------------------------------------------------------------*/
265 Procedure Validate_Approval ( P_Project_ID in number,
266 P_Draft_Invoice_Num in number,
267 P_Validation_Level in varchar2,
268 X_Error_Message_Code out NOCOPY varchar2 ) is --File.Sql.39 bug 4440895
269
270 l_customer_id pa_draft_invoices_v.customer_id%TYPE;
271 l_generation_error_flag pa_draft_invoices_v.generation_error_flag%TYPE;
272 l_approved_date pa_draft_invoices_v.approved_date%TYPE;
273 l_project_status_code pa_draft_invoices_v.project_status_code%TYPE;
274 l_dummy number;
275 l_err_msg_code varchar2(30);
276
277
278 /* Bug#4940211 - Performance Issue for calling the pa_draft_invoices_v
279 Fix : commented the old code and Geting
280 the value from pa_project_all and pa_draft_invoices table
281
282 Cursor Inv_Cur is
283 SELECT i.customer_id, i.generation_error_flag,
284 i.approved_date , i.project_status_code
285 FROM pa_draft_invoices_v i
286 WHERE i.project_id = P_Project_ID
287 AND i.draft_invoice_num = P_Draft_Invoice_Num;
288 */
289
290 Cursor Inv_cur is
291 SELECT i.customer_id, i.generation_error_flag,
292 i.approved_date, prj.project_status_code project_status_code
293 FROM pa_draft_invoices i,
294 pa_projects_all prj
295 WHERE prj.project_id = P_Project_ID
296 AND i.project_id = prj.project_id
297 AND i.draft_invoice_num = P_Draft_Invoice_Num;
298
299
300
301 Cursor Cust_Cur is
302 /* TCA changes
303 SELECT 1
304 FROM RA_CUSTOMERS
305 WHERE customer_id = l_customer_id
306 AND NVL(status, 'A') <> 'A'
307 AND customer_prospect_code = 'CUSTOMER';
308 */
309 SELECT 1
310 FROM HZ_CUST_ACCOUNTS
311 WHERE cust_account_id = l_customer_id
312 AND NVL(status, 'A') <> 'A';
313 -- AND customer_prospect_code = 'CUSTOMER';
314
315 BEGIN
316 -- Reset Output Parameters
317 X_Error_Message_Code := NULL;
318
319 IF P_Validation_Level = 'R' THEN /* Record Level Validation */
320
321 OPEN Inv_Cur;
322 FETCH Inv_Cur into l_customer_id, l_generation_error_flag,
323 l_approved_date,l_project_status_code;
324 CLOSE Inv_Cur;
325
326 /* Check Project Status */
327 /* Remove Project Status Check as discussed
328 IF (PA_Project_Stus_Utils.Is_Project_Status_Closed(l_project_status_code)
329 = 'Y') THEN
330 X_Error_Message_Code := 'PA_EX_CLOSED_PROJECT';
331 GOTO all_done;
332 END IF;*/
333
334 /* Bug#1499480:Check whether action 'GENERATE_INV' is enabled for Project Status */
335 IF (PA_Project_Utils.Check_Prj_Stus_Action_Allowed(l_project_status_code,'GENERATE_INV')
336 = 'N') THEN
337 X_Error_Message_Code := 'PA_INV_ACTION_INVALID';
338 GOTO all_done;
339 END IF;
340
341
342 /* Check Generation Error */
343 IF (l_generation_error_flag = 'Y') THEN
344 X_Error_Message_Code := 'PA_IN_NO_APP_GEN_ERR';
345 GOTO all_done;
346 END IF;
347
348 /* Check Invoice Status */
349 IF (l_approved_date is not NULL) THEN
350 X_Error_Message_Code := 'PA_IN_ALREADY_APPROVED';
351 GOTO all_done;
352 END IF;
353
354 /* Check Customer Status */
355 l_dummy := 0;
356 OPEN Cust_Cur;
357 Fetch Cust_Cur into l_dummy;
358 CLOSE Cust_Cur;
359
360 IF (l_dummy > 0) THEN
361 X_Error_Message_Code := 'PA_EX_INACTIVE_CUSTOMER';
362 GOTO all_done;
363 END IF;
364
365
366 ELSIF P_Validation_Level = 'C' THEN /* Commit Level Validation */
367
368 NULL;
369
370 END IF; /* Validation Level Checks */
371
372 <<all_done>>
373 NULL;
374
375 EXCEPTION
376 WHEN OTHERS THEN
377 RAISE;
378 END Validate_Approval;
379
380
381
382 /*----------------------------------------------------------------------------+
383 | For Details/Comments Refer Package Specification Comments |
384 +----------------------------------------------------------------------------*/
385 Procedure Validate_Release ( P_Project_ID in number,
386 P_Draft_Invoice_Num in number,
387 P_Validation_Level in varchar2,
388 P_User_ID in number,
389 P_RA_Invoice_Date in date,
390 P_RA_Invoice_Num in varchar2,
391 P_Credit_Memo_Reason_Code in out NOCOPY varchar2, --File.Sql.39 bug 4440895
392 X_RA_Invoice_Num out NOCOPY varchar2, --File.Sql.39 bug 4440895
393 X_Error_Message_Code out NOCOPY varchar2) is --File.Sql.39 bug 4440895
394
395 l_entry_method pa_implementations.user_defined_invoice_num_code%TYPE;
396 l_num_type pa_implementations.manual_invoice_num_type%TYPE;
397
398 l_dummy number;
399 l_customer_id number;
400 l_err_msg_code varchar2(30);
401 l_RA_Invoice_Num pa_draft_invoices_v.RA_Invoice_Number%TYPE;
402 l_status number;
403 l_bill_to number;
404 l_ship_to number;
405 l_Error_Message_Code VARCHAR2(80):=NULL;
406
407 l_invoice_category pa_draft_invoices_v.invoice_category%TYPE;
408 l_bill_ship_to_customer_id number; /*Added for customer account relation enhancement*/
409 Cursor Imp_Cur is
410 SELECT user_defined_invoice_num_code, manual_invoice_num_type
411 FROM pa_implementations;
412
413 Cursor Imp_Cur_Inter is
414 SELECT CC_MANUAL_INVOICE_NUM_CODE, CC_MANUAL_INVOICE_NUM_TYPE
415 FROM pa_implementations;
416
417 /* Added for Bug 2941112 */
418 l_draft_inv_num_cr number;
419
420 l_Credit_Memo_Reason_Code varchar2(100);
421
422 BEGIN
423
424 /* ATG Changes */
425
426 l_Credit_Memo_Reason_Code := P_Credit_Memo_Reason_Code;
427
428
429 -- Reset Output Parameters
430 X_Error_Message_Code := NULL;
431 -- Bug 682284
432 /* Get customer, ship_to_address_id, bill_to_address_id
433 for this draft Invoice */
434 BEGIN
435
436 -- IC Changes
437 -- Invoice category field is added here to classify the invoice
438 -- into "INTERNAL INVOICE' and "EXTERNAL INVOICE"
439 --
440 /*PROJCUST.bill_to_customer_id is added for customer account relation enhancement*/
441 SELECT PROJCUST.customer_id,PROJCUST.bill_to_customer_id,PROJCUST.bill_to_address_id,
442 PROJCUST.ship_to_address_id,
443 decode(nvl(PROJTYPE.cc_prvdr_flag,'N'),
444 'Y', 'INTERNAL-INVOICE',
445 decode(nvl(PROJCUST.bill_another_project_flag,'N'),
446 'Y', 'INTERNAL-INVOICE',
447 'EXTERNAL-INVOICE'))
448 INTO l_customer_id,l_bill_ship_to_customer_id, l_bill_to, l_ship_to, l_invoice_category
449 FROM pa_draft_invoices INV, pa_agreements_all AGREE, /* fix bug 2082864 for MCB2 */
450 pa_project_customers PROJCUST,
451 pa_projects PROJ,
452 pa_project_types PROJTYPE
453 WHERE INV.project_id = P_Project_id
454 AND INV.draft_invoice_num = P_Draft_Invoice_Num
455 AND AGREE.agreement_id = INV.agreement_id
456 AND PROJCUST.customer_id = AGREE.customer_id
457 AND PROJCUST.project_id = P_Project_id
458 AND PROJ.project_id = INV.project_id
459 AND PROJ.project_type = PROJTYPE.project_type;
460
461 EXCEPTION
462
463 WHEN NO_DATA_FOUND THEN
464 /* X_Error_Message_Code := 'PA_INV_NO_BILL_TO_ADDRESS'; commented for customer account relation enhancement*/
465 X_Error_Message_Code := 'PA_INV_NO_CUSTOMER';
466 GOTO all_done;
467
468 END;
469
470 /* Check for bill_to_address_id */
471
472 IF (l_bill_to IS NULL) THEN
473 X_Error_Message_Code := 'PA_INV_NO_BILL_TO_ADDRESS';
474 GOTO all_done;
475 END IF;
476
477 /* Check for ship_to_address_id */
478 IF (l_ship_to IS NULL) THEN
479 X_Error_Message_Code := 'PA_INV_NO_SHIP_TO_ADDRESS';
480 GOTO all_done;
481 END IF;
482
483 /* Check for bill_to_contact_id */
484
485 /* Commneting for bug 4879331 as we are no more validating if billing contact is
486 populated or not.
487 BEGIN
488
489 SELECT null INTO l_dummy
490 FROM sys.dual
491 WHERE EXISTS ( SELECT project_id
492 FROM pa_project_contacts
493 WHERE project_id = P_project_id
494 AND customer_id = l_customer_id
495 AND bill_ship_customer_id=l_bill_ship_to_customer_id -- Added for customer account relation
496 -- enhancement
497 AND project_contact_type_code = 'BILLING');
498
499 EXCEPTION
500
501 WHEN NO_DATA_FOUND THEN
502 X_Error_Message_Code := 'PA_INV_NO_BILL_TO_CONTACT';
503 GOTO all_done;
504
505 END;
506 End Bug 4879331. */
507 -- End Bug 682284
508
509 /* Fix for bug 2941112 */
510 BEGIN
511 SELECT nvl(draft_invoice_num_credited,0)
512 INTO l_draft_inv_num_cr
513 FROM pa_draft_invoices
514 WHERE project_id = p_project_id
515 AND draft_invoice_num = P_Draft_Invoice_Num;
516 EXCEPTION
517 WHEN NO_DATA_FOUND THEN
518 NULL;
519 END;
520
521 /* Commneting for bug 4879331 as we are no more validating if billing contact is
522 populated or not.
523 IF ( l_draft_inv_num_cr = 0 )
524 THEN
525 BEGIN
526 -- bug 4325231
527 -- SELECT null INTO l_dummy
528 -- FROM dual
529 -- WHERE EXISTS ( SELECT project_id
530 -- FROM pa_draft_invoices di, ra_contacts rc
531 -- WHERE di.project_id = p_project_id
532 -- AND di.draft_invoice_num = P_Draft_Invoice_Num
533 -- AND di.bill_to_contact_id = rc.contact_id
534 -- AND nvl(rc.status,'N') = 'A'
535 -- );
536 SELECT null INTO l_dummy
537 FROM dual
538 WHERE EXISTS ( SELECT project_id
539 FROM pa_draft_invoices di, hz_cust_account_roles rc
540 WHERE di.project_id = p_project_id
541 AND di.draft_invoice_num = P_Draft_Invoice_Num
542 AND di.bill_to_contact_id = rc.cust_account_role_id
543 AND nvl(rc.status,'N') = 'A'
544 );
545 EXCEPTION
546 WHEN NO_DATA_FOUND THEN
547 X_Error_Message_Code := 'PA_BIL_CONT_NOT_ACT';
548 GOTO all_done;
549 END;
550 END IF;
551
552 End bug 4879331. */
553
554 if l_invoice_category = 'EXTERNAL-INVOICE' then
555 OPEN Imp_Cur;
556 FETCH Imp_Cur into l_entry_method, l_num_type;
557 CLOSE Imp_Cur;
558 else
559 OPEN Imp_Cur_Inter;
560 FETCH Imp_Cur_Inter into l_entry_method, l_num_type;
561 CLOSE Imp_Cur_Inter;
562 end if;
563 IF P_Validation_Level IN ('R','F_INV_DT','F_INV_NUM')
564 THEN /* Record Level Validation/Field level */
565
566 /* RA invoice number checks for mannual numbering type */
567 IF (l_entry_method <> 'AUTOMATIC')
568 AND ( P_Validation_level IN ( 'R','F_INV_NUM')) THEN
569
570 /* RA invoice number should be entered */
571 IF (P_RA_Invoice_Num is null) THEN
572 X_Error_Message_Code := 'PA_IN_ENT_INV_NUMBER';
573 GOTO all_done;
574 END IF;
575
576 /* Check for RA invoice number numeric */
577 IF (l_num_type = 'NUMERIC') THEN
578 BEGIN
579 select to_number(P_RA_Invoice_Num)
580 into l_dummy
581 from dual;
582 EXCEPTION
583 WHEN INVALID_NUMBER THEN
584 X_Error_Message_Code := 'PA_IN_ENT_NUM_INV_NUMBER';
585 GOTO all_done;
586 WHEN OTHERS THEN
587 RAISE;
588 END;
589 END IF;
590 END IF; /* end of checks for mannual numbering type */
591
592 IF (P_Validation_Level IN ('R','F_INV_DT'))
593 THEN
594 /* Inv date should be entered */
595 IF (P_RA_Invoice_Date is NULL) THEN
596 X_Error_Message_Code := 'PA_IN_ENT_INV_DATE';
597 GOTO all_done;
598 END IF;
599
600 /* Crediting Inv Date should be greater than Inv date */
601 l_dummy := 0;
602 SELECT COUNT(*)
603 INTO l_dummy
604 FROM pa_draft_invoices_all cm, pa_draft_invoices_all i
605 WHERE cm.project_id = P_Project_ID
606 AND cm.draft_invoice_num = P_Draft_Invoice_Num
607 AND cm.project_id = i.project_id
608 AND i.draft_invoice_num = cm.draft_invoice_num_credited
609 AND i.invoice_date > P_RA_Invoice_Date;
610
611 IF (l_dummy > 0) THEN
612 X_Error_Message_Code := 'PA_IN_CM_INV_DATE';
613 GOTO all_done;
614 END IF;
615 /* Call to procedure to validate Credit Memo Reason :Enhancement bug 2728431*/
616 IF (P_Validation_Level ='R') THEN
617
618 Validate_Credit_Memo_Reason(P_Project_ID,P_Draft_Invoice_Num,P_RA_Invoice_Date,
619 P_Credit_Memo_Reason_Code,l_Error_Message_Code);
620
621 IF (l_Error_Message_Code IS NOT NULL) THEN
622 X_Error_Message_Code := l_Error_Message_Code;
623 GOTO all_done;
624 END IF;
625 END IF;
626 END IF;/* End Checking based on Record Level or Item level */
627 ELSIF P_Validation_Level = 'C' THEN /* Commit Level Validation */
628
629 /* Check lower Invoice numbers have been released */
630 l_dummy := 0;
631 SELECT count(*)
632 INTO l_dummy
633 FROM pa_draft_invoices_all
634 WHERE project_id = P_Project_ID
635 AND draft_invoice_num < P_Draft_Invoice_Num
636 AND released_date is null
637 AND nvl(generation_error_flag, 'N') = 'N';
638
639 IF (l_dummy > 0) THEN
640 X_Error_Message_Code := 'PA_UNREL_INVOICES_EXIST';
641 GOTO all_done;
642 END IF;
643
644 IF (l_entry_method = 'AUTOMATIC') THEN
645 /* Added for bug 5924290 to get the invoice status. If released then 1 else 0 */
646 begin
647 SELECT 1
648 into l_dummy
649 FROM PA_DRAFT_INVOICES_ALL
650 WHERE PROJECT_ID =P_Project_ID
651 AND DRAFT_INVOICE_NUM = P_Draft_Invoice_Num
652 AND RELEASED_DATE IS NOT NULL;
653 exception
654 when others
655 then
656 l_dummy := 0;
657 end;
658
659 /* Added if condition for bug 5924290. If released then no need to call PA_UTILS_SQNUM_PKG.get_unique_invoice_num
660 to increment the auto number. */
661 IF l_dummy = 0 THEN
662 PA_UTILS_SQNUM_PKG.get_unique_invoice_num(l_invoice_category,
663 P_User_ID ,l_RA_Invoice_Num , l_status);
664 END IF;
665 ELSE
666 /* RA Inv Number is entered (Mannual Method). Check Unique */
667 l_RA_Invoice_Num := P_RA_Invoice_Num;
668
669 l_dummy := 0;
670 SELECT count(*)
671 INTO l_dummy
672 FROM pa_draft_invoices i,
673 pa_projects p,
674 pa_project_types pt,
675 pa_agreements_all AGREE, /* fix bug 2082864 for MCB2 */
676 pa_project_customers PROJCUST
677 WHERE i.ra_invoice_number = l_RA_Invoice_Num
678 AND NOT EXISTS ( SELECT 'x'
679 FROM pa_draft_invoices x
680 WHERE x.project_id = P_Project_ID
681 AND x.draft_invoice_num = P_Draft_Invoice_Num
682 AND x.project_id = i.project_id
683 AND x.draft_invoice_num = i.draft_invoice_num)
684 AND i.project_id = p.project_id
685 AND pt.project_type = p.project_type
686 AND AGREE.agreement_id = i.agreement_id
687 AND PROJCUST.customer_id = AGREE.customer_id
688 AND projcust.project_id = p.project_id /* added for bug#2634995 */
689 AND decode(nvl(pt.cc_prvdr_flag,'N'),
690 'Y', 'INTERNAL-INVOICE',
691 decode(nvl(PROJCUST.bill_another_project_flag,'N'),
692 'Y', 'INTERNAL-INVOICE',
693 'EXTERNAL-INVOICE'))
694 = l_invoice_category;
695
696
697 IF (l_dummy > 0 ) THEN
698 X_Error_Message_Code := 'PA_IN_RA_INV_NUMBER_NOT_UNIQUE';
699 GOTO all_done;
700 END IF;
701
702 END IF;
703
704 X_Ra_Invoice_Num := l_RA_Invoice_Num;
705 /* Call to procedure to validate Credit Memo Reason :Enhancement bug 2728431*/
706 Validate_Credit_Memo_Reason(P_Project_ID,P_Draft_Invoice_Num,P_RA_Invoice_Date,
707 P_Credit_Memo_Reason_Code,l_Error_Message_Code);
708
709 IF (l_Error_Message_Code IS NOT NULL) THEN
710 X_Error_Message_Code := l_Error_Message_Code;
711 GOTO all_done;
712 END IF;
713
714 /* Start of Bug 3344912 - Check if lower invoices have Generation Error */
715 l_dummy := 0;
716
717 SELECT count(*)
718 INTO l_dummy
719 FROM pa_draft_invoices_all
720 WHERE project_id = P_Project_ID
721 AND draft_invoice_num < P_Draft_Invoice_Num
722 AND released_date is null
723 AND nvl(generation_error_flag, 'N') = 'Y';
724
725 IF (l_dummy > 0) THEN
726 X_Error_Message_Code := 'PA_GEN_ERR_INV_EXIST';
727 GOTO all_done;
728 END IF;
729 /* End of Bug 3344912 */
730
731 END IF; /* Validate Level Checks */
732
733 <<all_done>>
734 NULL;
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 /* ATG Changes */
739 P_Credit_Memo_Reason_Code := l_Credit_Memo_Reason_Code ;
740 X_RA_Invoice_Num := null;
741
742 RAISE;
743 END Validate_Release;
744
745 /* Overloaded procedure validate_release for credit memo reason */
746 Procedure Validate_Release ( P_Project_ID in number,
747 P_Draft_Invoice_Num in number,
748 P_Validation_Level in varchar2,
749 P_User_ID in number,
750 P_RA_Invoice_Date in date,
751 P_RA_Invoice_Num in varchar2,
752 X_RA_Invoice_Num out NOCOPY varchar2, --File.Sql.39 bug 4440895
753 X_Error_Message_Code out NOCOPY varchar2) is --File.Sql.39 bug 4440895
754
755 new_excp exception;
756 BEGIN
757 Raise new_excp;
758 END Validate_Release ;
759
760
761 /*----------------------------------------------------------------------------+
762 | For Details/Comments Refer Package Specification Comments |
763 +----------------------------------------------------------------------------*/
764 Procedure validate_multi_Customer ( P_Invoice_Set_ID in number,
765 X_Error_Message_Code out NOCOPY varchar2) is --File.Sql.39 bug 4440895
766 l_dummy number;
767 BEGIN
768
769 X_Error_Message_Code := NULL;
770 l_dummy := 0;
771
772 SELECT count(*)
773 INTO l_dummy
774 FROM PA_Draft_Invoices_ALL i
775 WHERE i.invoice_set_id = P_Invoice_Set_ID
776 AND i.customer_bill_split not in (0, 100)
777 AND ( ( (i.approved_date is not null)
778 AND EXISTS ( SELECT 'APPROVED ERROR'
779 FROM pa_draft_invoices ia
780 WHERE ia.project_id = i.project_id
781 AND ia.invoice_set_id = i.invoice_set_id
782 AND ia.approved_date is null))
783 OR ( (i.released_date is not null)
784 AND EXISTS ( SELECT 'RELEASED ERROR'
785 FROM pa_draft_invoices ir
786 WHERE ir.project_id = i.project_id
787 AND ir.invoice_set_id = i.invoice_set_id
788 AND ir.released_date is null)));
789
790 IF (l_dummy > 0) THEN
791 X_Error_Message_Code := 'PA_REL_ALL_DR_INV';
792 END IF;
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 RAISE;
797 END validate_multi_Customer;
798
799
800 /*----------------------------------------------------------------------------+
801 | For Details/Comments Refer Package Specification Comments |
802 +----------------------------------------------------------------------------*/
803 Procedure Post_Update_Release ( P_Project_ID in number,
804 P_Draft_Invoice_Num in number,
805 P_User_ID in number,
806 P_Employee_ID in number) is
807
808
809 /* Commented for bug 3088395 */ /* The following SQL statement was uncommented for bug 3872496 */
810 CURSOR EI_BILL_HOLD_CUR IS
811 SELECT ei.expenditure_item_id
812 FROM pa_expenditure_items_all ei, pa_tasks t
813 WHERE ei.bill_hold_flag = 'O'
814 AND ei.task_id = t.task_id
815 AND t.project_id = P_Project_ID;
816
817
818 /* The following SQL statement was commented for bug 3872496 */
819 /*Added for bug 3088395
820 CURSOR EI_BILL_HOLD_CUR IS
821 SELECT ei.expenditure_item_id
822 FROM pa_expenditure_items_all ei
823 WHERE ei.bill_hold_flag = 'O' /*Bill Hold Once
824 AND ei.project_id = P_Project_ID;
825 */
826
827
828 l_outcome varchar2(100);
829 l_num_processed number;
830 l_num_rejected number;
831 l_dummy number;
832 l_msg_application VARCHAR2(30) :='PA';
833 l_msg_type VARCHAR2(1) := 'E';
834 l_msg_token1 Varchar2(240) := '';
835 l_msg_token2 Varchar2(240) :='';
836 l_msg_token3 Varchar2(240) :='';
837 l_msg_count Number ;
838
839
840 BEGIN
841
842 /* Releasing bill event based revenue */
843 UPDATE pa_draft_revenues dr
844 SET dr.last_update_date = sysdate,
845 dr.last_updated_by = P_User_ID,
846 dr.released_date = sysdate,
847 dr.last_update_login = P_Employee_ID
848 WHERE dr.project_id = P_Project_ID
849 AND dr.generation_error_flag = 'N'
850 AND dr.released_date IS NULL
851 AND dr.draft_revenue_num <=
852 (SELECT max(rdl.draft_revenue_num)
853 FROM pa_cust_event_rev_dist_lines rdl
854 WHERE rdl.project_id = P_Project_ID
855 AND rdl.draft_invoice_num = P_Draft_Invoice_Num);
856
857
858 /* Releasing Revenue based on Automatic Events which have both revenue and
859 invoice amounts -For bug 5401384 - base bug 5246804*/
860
861 UPDATE pa_draft_revenues dr
862 SET dr.last_update_date = sysdate,
863 dr.last_updated_by = P_User_ID,
864 dr.released_date = sysdate,
865 dr.last_update_login = P_Employee_ID
866 WHERE dr.project_id = P_Project_ID
867 AND dr.generation_error_flag = 'N'
868 AND dr.released_date IS NULL
869 AND dr.draft_revenue_num <=
870 (SELECT max(rdl.draft_revenue_num)
871 FROM pa_cust_event_rev_dist_lines rdl
872 WHERE rdl.project_id = P_Project_ID
873 and exists /* check if the event is an automatic event */
874 (select 1
875 from pa_events e, pa_event_types et
876 where e.project_id = rdl.project_id
877 and e.event_num = rdl.event_num
878 and nvl(e.task_id,-99) = nvl(rdl.task_id,-99)
879 and e.event_type = et.event_type
880 and et.event_type_classification = 'AUTOMATIC')
881 AND exists /* check if the invoice released is related to this automatic event */
882 (select 1
883 from pa_draft_invoice_items dii
884 where dii.project_id = rdl.project_id
885 and dii.draft_invoice_num = P_Draft_Invoice_Num
886 and dii.event_num is not null
887 and dii.event_num = rdl.event_num
888 and nvl(dii.event_task_id, -99) = nvl(rdl.task_id, -99)));
889
890 /* End of Changes for bug 5401384 - Base Bug 5246804 */
891
892
893 /* Releasing Expenditure item based revenue */
894 UPDATE pa_draft_revenues dr
895 SET dr.last_update_date = sysdate,
896 dr.last_updated_by = P_User_ID,
897 dr.released_date = sysdate,
898 dr.last_update_login = P_Employee_ID
899 WHERE dr.project_id = P_Project_ID
900 AND dr.generation_error_flag = 'N'
901 AND dr.released_date IS NULL
902 AND dr.draft_revenue_num <=
903 /* Commented code fix for bug 2968645
904 (SELECT max(rdl.draft_revenue_num)
905 FROM pa_cust_rev_dist_lines rdl
906 WHERE rdl.project_id = P_Project_ID
907 AND rdl.draft_invoice_num = P_Draft_Invoice_Num); */
908 /* Bug fix for bug 2968645 Starts here */
909 (SELECT max(rdl1.draft_revenue_num)
910 FROM pa_cust_rev_dist_lines rdl1
911 WHERE rdl1. expenditure_item_id in
912 ( SELECT expenditure_item_id
913 FROM pa_cust_rev_dist_lines rdl2
914 WHERE rdl2.project_id = P_Project_ID
915 AND rdl2.draft_invoice_num = P_Draft_Invoice_Num));
916 /* Bug fix for bug 2968645 Ends here */
917
918
919
920 /* Release One Time EI BILL Holds */
921 FOR EI_BILL_HOLD_REC in EI_BILL_HOLD_CUR
922 LOOP
923 pa_adjustments.adjust (X_adj_action => 'BILLING HOLD RELEASE',
924 X_module => 'PAXINADI',
925 X_user => P_User_ID,
926 X_login => P_Employee_ID,
927 X_project_id => P_Project_ID,
928 X_adjust_level => 'I', -- item level
929 X_expenditure_item_id =>
930 EI_BILL_HOLD_REC.expenditure_item_id,
931 X_outcome => l_outcome,
932 X_num_processed => l_num_processed,
933 X_num_rejected => l_num_rejected,
934 X_msg_application => l_msg_application,
935 X_msg_type => l_msg_type,
936 X_msg_token1 => l_msg_token1,
937 X_msg_token2 => l_msg_token2,
938 X_msg_token3 => l_msg_token3,
939 X_msg_count => l_msg_count );
940
941 END LOOP;
942
943 /* Release One Time Event BILL Holds */
944 UPDATE pa_events
945 SET bill_hold_flag = 'N',
946 last_update_date = sysdate,
947 last_updated_by = P_User_ID,
948 last_update_login = P_Employee_ID
949 WHERE project_id = P_Project_ID
950 AND bill_hold_flag || '' = 'O';
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 RAISE;
955 END Post_Update_Release;
956
957 /*----------------------------------------------------------------------------+
958 | For Details/Comments Refer Package Specification Comments |
959 +----------------------------------------------------------------------------*/
960 Procedure Client_Extn_Driver( P_Request_ID in number,
961 P_User_ID in number,
962 P_Calling_Place in varchar2,
963 P_Project_ID in number ) is
964
965 l_employee_id number;
966
967 l_project_id number;
968 l_prv_project_id number;
969 l_draft_invoice_num number;
970 l_invoice_set_id number;
971 l_prv_invoice_set_id number;
972 l_tmp_invoice_set_id number;
973 l_invoice_class varchar2(15);
974 l_action_flag varchar2(1);
975 l_err_msg_code varchar2(30);
976 l_status number;
977 l_dummy number;
978 l_invoice_amount number;
979 l_project_amount number;
980 l_inv_currency_code varchar2(15);
981 l_project_currency_code varchar2(15);
982
983 l_ra_invoice_num varchar2(20);
984 l_tmp_ra_invoice_num varchar2(20);
985 l_ra_invoice_date date;
986 l_Credit_memo_reason_code pa_draft_invoices_all.credit_memo_reason_Code%TYPE;
987
988 /* Cursor for Select All Unapproved invoices created in This Run */
989 CURSOR UNAPP_INV_CUR is
990 SELECT i.project_id,
991 nvl(i.invoice_set_id, 0),
992 i.draft_invoice_num,
993 decode(P_Calling_Place, 'INV_CR_MEMO',
994 decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
995 'CREDIT_MEMO'), P_Calling_Place) invoice_class,
996 sum(ii.amount),
997 p.project_currency_code,
998 i.inv_currency_code,
999 sum(ii.inv_amount)
1000 FROM pa_projects p,
1001 pa_draft_invoices i,
1002 pa_draft_invoice_items ii
1003 WHERE p.project_id = i.project_id
1004 AND i.project_id = ii.project_id
1005 AND i.draft_invoice_num = ii.draft_invoice_num
1006 AND i.request_id = P_Request_ID
1007 AND i.approved_date is null
1008 AND nvl(i.generation_error_flag, 'N') = 'N'
1009 AND (i.project_id+0 = P_Project_ID or P_Project_ID is NULL)
1010 GROUP BY i.project_id,
1011 nvl(i.invoice_set_id, 0),
1012 i.draft_invoice_num,
1013 decode(P_Calling_Place, 'INV_CR_MEMO',
1014 decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
1015 'CREDIT_MEMO'), P_Calling_Place),
1016 p.project_currency_code,
1017 i.inv_currency_code
1018 ORDER BY i.project_id, i.draft_invoice_num; /*Added order by clause for bug 6009706 */
1019
1020
1021 /* Cursor for Select All Unreleased invoices created in This Run */
1022 CURSOR UNREL_INV_CUR is
1023 SELECT i.project_id,
1024 nvl(i.invoice_set_id, 0),
1025 i.draft_invoice_num,
1026 decode(P_Calling_Place, 'INV_CR_MEMO',
1027 decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
1028 'CREDIT_MEMO'), P_Calling_Place) invoice_class,
1029 sum(ii.amount),
1030 p.project_currency_code,
1031 i.inv_currency_code,
1032 sum(ii.inv_amount)
1033 FROM pa_projects p,
1034 pa_draft_invoices i,
1035 pa_draft_invoice_items ii
1036 WHERE p.project_id = i.project_id
1037 AND i.project_id = ii.project_id
1038 AND i.draft_invoice_num = ii.draft_invoice_num
1039 AND i.request_id = P_Request_ID
1040 AND i.approved_date is not null
1041 AND i.released_date is null /* For bug 2863710 */
1042 AND nvl(i.generation_error_flag, 'N') = 'N'
1043 AND (i.project_id+0 = P_Project_ID or P_Project_ID is NULL)
1044 GROUP BY i.project_id,
1045 nvl(i.invoice_set_id, 0),
1046 i.draft_invoice_num,
1047 decode(P_Calling_Place, 'INV_CR_MEMO',
1048 decode(i.draft_invoice_num_credited, NULL, 'INVOICE',
1049 'CREDIT_MEMO'), P_Calling_Place),
1050 p.project_currency_code,
1051 i.inv_currency_code
1052 ORDER BY i.project_id, i.draft_invoice_num; /*Added order by clause for bug 6009706 */
1053
1054
1055 /* Cursor for Select All invoices Released in This Run */
1056 CURSOR RELEASED_INV_CUR is
1057 SELECT i.project_id,
1058 i.draft_invoice_num
1059 FROM pa_draft_invoices i
1060 WHERE i.request_id = P_Request_ID
1061 AND i.released_date is not null;
1062
1063 BEGIN
1064
1065 /*------------------------------------------------------------------+
1066 | Get Employee ID corresponding to User ID |
1067 +------------------------------------------------------------------*/
1068 BEGIN
1069 /* nvl added for the resolution of the bug 1510535 */
1070 SELECT nvl(Employee_ID,0)
1071 INTO l_employee_id
1072 FROM FND_USER
1073 WHERE User_ID = P_User_ID;
1074
1075 EXCEPTION
1076 WHEN NO_DATA_FOUND THEN
1077 l_employee_id := 0;
1078 END;
1079
1080
1081 /*------------------------------------------------------------------+
1082 | Select The Invoices that were created in this run |
1083 | For a single run of generate Draft invoice program this API |
1084 | will be called once for Invoice/Credit Memo, Write Off or |
1085 | Cancellation of an invoice. |
1086 +------------------------------------------------------------------*/
1087
1088 /*------------------------------------------------------------------+
1089 | Select UnApproved Invoices that were Created in this run |
1090 +------------------------------------------------------------------*/
1091
1092 -- Initialize Local Variables
1093 l_prv_project_id := 0;
1094 l_prv_invoice_set_id := 0;
1095 l_tmp_invoice_set_id := 0;
1096
1097 OPEN UNAPP_INV_CUR;
1098
1099 LOOP
1100 FETCH UNAPP_INV_CUR into l_project_id, l_invoice_set_id,
1101 l_draft_invoice_num,
1102 l_invoice_class,
1103 l_project_amount,
1104 l_project_currency_code,
1105 l_inv_currency_code,
1106 l_invoice_amount;
1107
1108 EXIT WHEN UNAPP_INV_CUR%NOTFOUND;
1109
1110 l_tmp_invoice_set_id := l_invoice_set_id;
1111
1112 /* Lock The Project */
1113 IF (l_project_id <> l_prv_project_id) THEN
1114 SELECT Project_ID
1115 INTO l_dummy
1116 FROM PA_Projects_ALL
1117 WHERE Project_ID = l_project_id
1118 FOR UPDATE OF LAST_UPDATE_DATE;
1119
1120 l_prv_project_id := l_project_id;
1121
1122 END IF;
1123
1124 -- Initialize Output Variables
1125 l_action_flag := NULL;
1126 l_status := NULL;
1127
1128 PA_Client_Extn_Inv_Actions.Approve_Invoice( l_project_id,
1129 l_draft_invoice_num,
1130 l_invoice_class,
1131 l_project_amount,
1132 l_project_currency_code,
1133 l_inv_currency_code,
1134 l_invoice_amount,
1135 l_action_flag,
1136 l_Status);
1137
1138 /* Check Extension Application Error */
1139 IF l_Status > 0 THEN
1140 Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
1141 P_User_ID, P_Request_ID, NULL,
1142 'PA_CLIENT_EXTN_APP_ERROR');
1143 GOTO FETCH_NEXT_UNAPP_REC;
1144 END IF;
1145
1146 /* Check Extension Oracle Error */
1147 IF l_Status < 0 THEN
1148 Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
1149 P_User_ID, P_Request_ID, NULL,
1150 'PA_CLIENT_EXTN_ORACLE_ERROR');
1151 GOTO FETCH_NEXT_UNAPP_REC;
1152 END IF;
1153
1154 /* At This Point Approval Billing Extension has returned success */
1155 IF (nvl(l_action_flag, 'N') = 'Y') THEN
1156 l_err_msg_code := NULL;
1157 /* Record Level Validations */
1158 PA_Invoice_Actions.Validate_Approval( l_Project_ID,
1159 l_draft_invoice_num, 'R',
1160 l_err_msg_code);
1161 IF l_err_msg_code is null THEN
1162 /* Commit Level Validations */
1163 PA_Invoice_Actions.Validate_Approval( l_Project_ID,
1164 l_draft_invoice_num, 'C',
1165 l_err_msg_code);
1166 END IF;
1167
1168 IF l_err_msg_code is null THEN
1169 /* Approve Invoices */
1170 Update_Approve_Invoices( l_Project_ID, l_draft_invoice_num,
1171 P_User_ID, l_employee_id);
1172 ELSE
1173 Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
1174 P_User_ID, P_Request_ID, NULL,
1175 l_err_msg_code);
1176 GOTO FETCH_NEXT_UNAPP_REC;
1177
1178 END IF;
1179
1180 END IF;
1181
1182 <<FETCH_NEXT_UNAPP_REC>>
1183
1184 /* Check for Multiple Customers */
1185 IF ((l_invoice_set_id <> l_prv_invoice_set_id) AND
1186 (l_invoice_set_id <> 0) AND
1187 (l_prv_invoice_set_id <> 0)) THEN
1188
1189 PA_Invoice_Actions.Validate_Multi_Customer (l_prv_invoice_set_id,
1190 l_err_msg_code);
1191
1192 IF l_err_msg_code is not null THEN
1193 Insert_Distrbution_Warning ( NULL, NULL, P_User_ID, P_Request_ID,
1194 l_prv_invoice_set_id, l_err_msg_code);
1195 Update_Unapprove_Invoices ( l_prv_invoice_set_id, P_User_ID);
1196 END IF;
1197
1198 l_prv_invoice_set_id := l_invoice_set_id;
1199
1200 END IF;
1201
1202
1203 END LOOP;
1204
1205 IF ( l_tmp_invoice_set_id <> 0) THEN
1206
1207 PA_Invoice_Actions.Validate_Multi_Customer (l_invoice_set_id,
1208 l_err_msg_code);
1209
1210 IF l_err_msg_code is not null THEN
1211 Insert_Distrbution_Warning ( NULL, NULL, P_User_ID, P_Request_ID,
1212 l_invoice_set_id, l_err_msg_code);
1213 Update_Unapprove_Invoices ( l_invoice_set_id, P_User_ID);
1214 END IF;
1215 END IF;
1216
1217 CLOSE UNAPP_INV_CUR;
1218
1219 COMMIT;
1220
1221
1222 /*------------------------------------------------------------------+
1223 | Select UnReleased Invoices that were Created in this run |
1224 +------------------------------------------------------------------*/
1225
1226 -- Initialize Local Variables
1227 l_prv_project_id := 0;
1228 l_prv_invoice_set_id := 0;
1229 l_tmp_invoice_set_id := 0;
1230
1231 OPEN UNREL_INV_CUR;
1232
1233 LOOP
1234 FETCH UNREL_INV_CUR into l_project_id, l_invoice_set_id,
1235 l_draft_invoice_num,
1236 l_invoice_class,
1237 l_project_amount,
1238 l_project_currency_code,
1239 l_inv_currency_code,
1240 l_invoice_amount;
1241
1242 EXIT WHEN UNREL_INV_CUR%NOTFOUND;
1243
1244 l_tmp_invoice_set_id := l_invoice_set_id;
1245
1246 /* Lock The Project */
1247 IF (l_project_id <> l_prv_project_id) THEN
1248 SELECT Project_ID
1249 INTO l_dummy
1250 FROM PA_Projects_ALL
1251 WHERE Project_ID = l_project_id
1252 FOR UPDATE OF LAST_UPDATE_DATE;
1253
1254 l_prv_project_id := l_project_id;
1255
1256 END IF;
1257
1258
1259 -- Initialize Output and temporary Variables
1260 l_action_flag := NULL;
1261 l_ra_invoice_date := NULL;
1262 l_ra_invoice_num := NULL;
1263 l_tmp_ra_invoice_num := NULL;
1264 l_status := NULL;
1265 l_Credit_memo_reason_code := NULL;
1266
1267
1268 PA_Client_Extn_Inv_Actions.Release_Invoice( l_project_id,
1269 l_draft_invoice_num,
1270 l_invoice_class,
1271 l_project_amount,
1272 l_project_currency_code,
1273 l_inv_currency_code,
1274 l_invoice_amount,
1275 l_action_flag,
1276 l_ra_invoice_date,
1277 l_ra_invoice_num,
1278 l_Status,
1279 l_Credit_memo_reason_code);
1280
1281 /* Check Extension Application Error */
1282 IF l_Status > 0 THEN
1283 Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
1284 P_User_ID, P_Request_ID, NULL,
1285 'PA_CLIENT_EXTN_APP_ERROR');
1286 GOTO FETCH_NEXT_UNREL_REC;
1287 END IF;
1288
1289 /* Check Extension Oracle Error */
1290 IF l_Status < 0 THEN
1291 Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
1292 P_User_ID, P_Request_ID, NULL,
1293 'PA_CLIENT_EXTN_ORACLE_ERROR');
1294 GOTO FETCH_NEXT_UNREL_REC;
1295 END IF;
1296
1297 /* At This Point Release Billing Extension has returned success */
1298 IF (nvl(l_action_flag, 'N') = 'Y') THEN
1299 l_err_msg_code := NULL;
1300 /* Record Level Validations */
1301 PA_Invoice_Actions.Validate_Release ( l_Project_ID,
1302 l_draft_invoice_num, 'R',
1303 P_User_ID, l_ra_invoice_date,
1304 l_ra_invoice_num,
1305 l_Credit_memo_reason_code,
1306 l_tmp_ra_invoice_num,
1307 l_err_msg_code);
1308 IF l_err_msg_code is null THEN
1309 /* Commit Level Validations */
1310 PA_Invoice_Actions.Validate_Release ( l_Project_ID,
1311 l_draft_invoice_num, 'C',
1312 P_User_ID, l_ra_invoice_date,
1313 l_ra_invoice_num,
1314 l_Credit_memo_reason_code,
1315 l_tmp_ra_invoice_num,
1316 l_err_msg_code);
1317 END IF;
1318
1319 IF l_err_msg_code is null THEN
1320 /* Release Invoices */
1321 l_ra_invoice_num := l_tmp_ra_invoice_num;
1322 Update_Release_Invoices( l_Project_ID, l_draft_invoice_num,
1323 l_ra_invoice_date, l_ra_invoice_num,
1324 P_User_ID, l_employee_id,l_Credit_memo_reason_code);
1325 ELSE
1326 Insert_Distrbution_Warning ( l_Project_ID, l_draft_invoice_num,
1327 P_User_ID, P_Request_ID, NULL,
1328 l_err_msg_code);
1329 GOTO FETCH_NEXT_UNREL_REC;
1330
1331 END IF;
1332
1333 END IF;
1334
1335 <<FETCH_NEXT_UNREL_REC>>
1336
1337 /* Check for Multiple Customers */
1338 IF ((l_invoice_set_id <> l_prv_invoice_set_id) AND
1339 (l_invoice_set_id <> 0) AND
1340 (l_prv_invoice_set_id <> 0)) THEN
1341
1342 PA_Invoice_Actions.Validate_Multi_Customer (l_prv_invoice_set_id,
1343 l_err_msg_code);
1344
1345 IF l_err_msg_code is not null THEN
1346 Insert_Distrbution_Warning ( NULL, NULL, P_User_ID, P_Request_ID,
1347 l_prv_invoice_set_id, l_err_msg_code);
1348 Update_Unrelease_Invoices ( l_prv_invoice_set_id, P_User_ID);
1349 END IF;
1350
1351 l_prv_invoice_set_id := l_invoice_set_id;
1352
1353 END IF;
1354
1355 END LOOP;
1356
1357 IF ( l_tmp_invoice_set_id <> 0) THEN
1358
1359 PA_Invoice_Actions.Validate_Multi_Customer (l_invoice_set_id,
1360 l_err_msg_code);
1361
1362 IF l_err_msg_code is not null THEN
1363 Insert_Distrbution_Warning ( NULL, NULL, P_User_ID, P_Request_ID,
1364 l_invoice_set_id, l_err_msg_code);
1365 Update_Unrelease_Invoices ( l_invoice_set_id, P_User_ID);
1366 END IF;
1367 END IF;
1368
1369 CLOSE UNREL_INV_CUR;
1370
1371 /* Do the Post Release update for all the released Records */
1372 FOR CUR_REC in RELEASED_INV_CUR
1373 LOOP
1374 PA_Invoice_Actions.Post_Update_Release(CUR_REC.Project_ID,
1375 CUR_REC.Draft_Invoice_Num,
1376 P_User_ID,
1377 l_employee_id);
1378 END LOOP;
1379
1380 COMMIT;
1381
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 RAISE;
1386 END Client_Extn_Driver;
1387
1388 /* Begin Concession invoice modification */
1389
1390 /*-----------------------------------------------------------------------------------------+
1391 | Procedure : check_concurrency_issue |
1392 | Purpose : To ensure that no two users do credit processing on the same draft |
1393 | at the same time |
1394 | Parameters : |
1395 | ================================================================================== |
1396 | Name Mode Description |
1397 | ================================================================================== |
1398 | p_project_id IN Project ID |
1399 | p_draft_invoice_num IN Draft invoice for which credit action is to be done |
1400 | p_rec_version_number IN Record version number of the draft invoice as |
1401 | retrieved by the user
1402 | x_return_status OUT Return status of this procedure |
1403 | x_msg_count OUT Error message count |
1404 | x_msg_data OUT Error message |
1405 | ================================================================================== |
1406 +----------------------------------------------------------------------------------------*/
1407 Procedure check_concurrency_issue (
1408 p_project_id IN NUMBER,
1409 p_draft_invoice_num IN NUMBER,
1410 p_rec_version_number IN NUMBER,
1411 x_return_status OUT NOCOPY VARCHAR2,
1412 x_msg_count OUT NOCOPY NUMBER,
1413 x_msg_data OUT NOCOPY VARCHAR2) IS
1414
1415
1416 l_return_status VARCHAR2(30) := NULL;
1417 l_msg_count NUMBER := NULL;
1418 l_msg_data VARCHAR2(250) := NULL;
1419
1420 l_record_version_number NUMBER;
1421 l_cnt_recs NUMBER;
1422
1423 l_last_credit_request_id NUMBER;
1424 l_phase varchar2(255);
1425 l_status varchar2(255);
1426 l_dev_phase varchar2(255);
1427 l_dev_status varchar2(255);
1428 l_message varchar2(255);
1429
1430
1431 BEGIN
1432
1433 x_return_status := FND_API.G_RET_STS_SUCCESS;
1434 x_msg_count := 0;
1435
1436
1437 /* Get the current record version number in draft invoice table */
1438
1439 select record_version_number, last_credit_request_id
1440 into l_record_version_number, l_last_credit_request_id
1441 from pa_draft_invoices
1442 where project_id = p_project_id
1443 and draft_invoice_num = p_draft_invoice_num;
1444
1445 /* Check that it matches the one passed by the calling process */
1446
1447 if (nvl(p_rec_version_number,0) <> nvl(l_record_version_number,0)) then
1448 l_return_status := FND_API.G_RET_STS_ERROR;
1449 l_msg_data := 'PA_REC_ALREADY_UPDATED';
1450 /* This means that another session has updated the record_version_number and fired
1451 the concurrent request for invoice processing since the start of the current session.
1452 Hence the current session needs to rollback and restart in order to include the changes
1453 from the other session.
1454 */
1455 rollback;
1456 RAISE FND_API.G_EXC_ERROR;
1457 End if;
1458
1459 /* Check the last request identifier that was run for crediting the current invoice
1460 and get the status of the last run
1461 */
1462
1463 if (l_last_credit_request_id is not null) then
1464
1465 /* The status needs to be checked only if there was a last run
1466 */
1467
1468 if (FND_CONCURRENT.GET_REQUEST_STATUS
1469 (
1470 l_last_credit_request_id,
1471 null, -- pa_schema_name
1472 null, -- request_name
1473 l_phase,
1474 l_status,
1475 l_dev_phase,
1476 l_dev_status,
1477 l_message
1478 )) then
1479 null;
1480 end if;
1481 /* For reference the possible combinations of dev_phase and dev_status
1482 for the last run are:
1483
1484 PENDING NORMAL
1485 STANDBY
1486 SCHEDULED
1487 PAUSED
1488 RUNNING NORMAL
1489 WAITING
1490 RESUMING
1491 TERMINATING
1492 COMPLETE NORMAL
1493 ERROR
1494 WARNING
1495 CANCELLED
1496 TERMINATED
1497 INACTIVE DISABLED
1498 ON_HOLD
1499 NO_MANAGER
1500 SUSPENDED
1501 */
1502
1503
1504 if (l_dev_phase = 'PENDING' or
1505 l_dev_phase = 'RUNNING' or
1506 l_dev_phase = 'INACTIVE'
1507 ) then
1508 l_return_status := FND_API.G_RET_STS_ERROR;
1509 l_msg_data := 'PA_CREDIT_IN_PROGRESS';
1510 /* This means that the request for invoice processing fired by another session is still in
1511 progress. Hence the current session needs to rollback and restart after the prior request
1512 has been completed, and no more changes are pending from the prior request, in order to
1513 include the changes from the other session.
1514 */
1515 ROLLBACK;
1516 RAISE FND_API.G_EXC_ERROR;
1517 elsif (l_dev_phase = 'COMPLETE') then
1518 null;
1519 end if;
1520
1521 end if; -- (l_last_credit_request_id is not null)
1522
1523 EXCEPTION
1524
1525 WHEN FND_API.G_EXC_ERROR THEN
1526 x_return_status := l_return_status;
1527 x_msg_count := l_msg_count;
1528 x_msg_data := l_msg_data;
1529
1530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1531 x_return_status := l_return_status;
1532 x_msg_count := l_msg_count;
1533 x_msg_data := l_msg_data;
1534
1535 WHEN others then
1536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537 x_msg_count := l_msg_count;
1538 x_msg_data := SUBSTR(SQLERRM,1,100);
1539
1540 END check_concurrency_issue;
1541
1542 /*----------------------------------------------------------------------------------------------+
1543 | Procedure : validate_invoice_amount |
1544 | Purpose : To validate the net invoice amount for Write-Off/Concession |
1545 | Parameters : |
1546 | ================================================================================== |
1547 | Name Mode Description |
1548 | ================================================================================== |
1549 | p_project_id IN Project ID |
1550 | p_credit_action IN Indicates if credit is WRITE-OFF/CONCESSION |
1551 | p_credit_action_type IN Indicates if credit action type is |
1552 | SUMMARY/GROUP/LINES |
1553 | p_draft_invoice_num IN Draft invoice for which credit action is to be done |
1554 | p_invoice_amount IN Invoice amount of the draft invoice for which credit |
1555 | action is to be done. |
1556 | p_net_inv_amount IN Net invoice amount of the selected lines. |
1557 | p_credit_amount IN Total entered credit amount. |
1558 | p_balance due IN Due amount in AR. |
1559 | x_tot_credited_amt OUT Total credited amount applied on invoice. |
1560 | x_return_status OUT Return status of this procedure |
1561 | x_msg_count OUT Error message count |
1562 | x_msg_data OUT Error message |
1563 | ================================================================================== |
1564 +---------------------------------------------------------------------------------------------*/
1565
1566 Procedure validate_invoice_amount (
1567 p_project_id IN NUMBER,
1568 p_credit_action IN VARCHAR2,
1569 p_credit_action_type IN VARCHAR2,
1570 p_draft_invoice_num IN NUMBER,
1571 p_invoice_amount IN NUMBER,
1572 p_net_inv_amount IN NUMBER,
1573 p_credit_amount IN NUMBER,
1574 p_balance_due IN NUMBER,
1575 x_tot_credited_amt OUT NOCOPY NUMBER,
1576 x_return_status OUT NOCOPY VARCHAR2,
1577 x_msg_count OUT NOCOPY NUMBER,
1578 x_msg_data OUT NOCOPY VARCHAR2) IS
1579
1580 l_return_status VARCHAR2(30) := NULL;
1581 l_msg_count NUMBER := NULL;
1582 l_msg_data VARCHAR2(250) := NULL;
1583
1584 l_tot_credited_amt NUMBER ;
1585
1586 /* Bug 9459594 */
1587 l_intercompany_flag VARCHAR2(1) := 'N';
1588 CURSOR overapplication_csr IS
1589 SELECT NVL(typ.allow_overapplication_flag,'N')
1590 FROM pa_implementations imp,
1591 ra_batch_sources bs,
1592 ra_cust_trx_types typ
1593 WHERE decode(l_intercompany_flag,'N',imp.invoice_batch_source_id,
1594 imp.cc_ic_ar_batch_source_id) = bs.batch_source_id
1595 and bs.default_inv_trx_type = typ.cust_trx_type_id;
1596 l_overapplication_flag VARCHAR2(1) := 'N';
1597
1598 CURSOR get_intercompany_flag_csr IS
1599 SELECT NVL(ptype.cc_prvdr_flag, 'N')
1600 FROM pa_projects proj,
1601 pa_project_types ptype
1602 WHERE proj.PROJECT_ID = p_project_id
1603 AND proj.PROJECT_TYPE = ptype.PROJECT_TYPE;
1604
1605 CURSOR get_centralized_flag_csr IS
1606 SELECT DECODE(CENTRALIZED_INVOICING_FLAG, 'N', 'Y', 'N')
1607 FROM PA_IMPLEMENTATIONS;
1608 l_use_inv_org_flag VARCHAR2(1) := 'N';
1609
1610 CURSOR get_misc_details_csr IS
1611 SELECT IMP.business_group_id,
1612 IMP.proj_org_structure_version_id,
1613 BASELANG.language_code,
1614 INV.invoice_date,
1615 PROJ.Carrying_Out_Organization_ID
1616 FROM pa_implementations IMP,
1617 fnd_languages BASELANG,
1618 pa_draft_invoices_all INV,
1619 pa_projects PROJ
1620 WHERE INV.project_id = p_project_id
1621 AND INV.draft_invoice_num = p_draft_invoice_num
1622 AND BASELANG.installed_flag = 'B'
1623 AND PROJ.project_id = p_project_id;
1624 l_business_group_id pa_implementations.business_group_id%TYPE;
1625 l_org_st_ver_id pa_implementations.proj_org_structure_version_id%TYPE;
1626 l_base_language fnd_languages.language_code%TYPE;
1627 l_invoice_date pa_draft_invoices_all.invoice_date%TYPE;
1628 l_carry_out_org_id pa_projects.Carrying_Out_Organization_ID%TYPE;
1629
1630 x_trx_type VARCHAR2(10);
1631 x_cm_trx_type VARCHAR2(10);
1632 x_error_status NUMBER;
1633 x_error_message PA_LOOKUPS.DESCRIPTION%TYPE;
1634
1635 CURSOR overapplication_csr_2 IS
1636 SELECT NVL(typ.allow_overapplication_flag,'N')
1637 FROM ra_cust_trx_types typ
1638 WHERE typ.cust_trx_type_id = TO_NUMBER(x_trx_type);
1639
1640 BEGIN
1641
1642 x_return_status := FND_API.G_RET_STS_SUCCESS;
1643 x_msg_count := 0;
1644 x_tot_credited_amt := 0;
1645
1646 /* First Check - Check if Invoice Amount is negative.
1647 Write-Off/Concession cannot be performed when Invoice Amount is negative. */
1648
1649 IF sign(p_invoice_amount) <> 1 THEN
1650
1651 l_return_status := FND_API.G_RET_STS_ERROR;
1652 l_msg_data := 'PA_IN_CRD_NOT_POSITIVE';
1653 l_msg_count := 1;
1654
1655 RAISE FND_API.G_EXC_ERROR;
1656
1657 END IF;
1658
1659 /* Second Check - Check if net invoice amount of the selected lines is negative.
1660 Write-Off/Concession cannot be performed when net invoice amount of the
1661 selected lines is negative. */
1662 IF (p_credit_action_type in ('GROUP', 'LINE'))THEN
1663 IF sign(p_net_inv_amount) <> 1 THEN
1664
1665 l_return_status := FND_API.G_RET_STS_ERROR;
1666 l_msg_data := 'PA_IN_LINE_CRD_NOT_POSITIVE';
1667 l_msg_count := 1;
1668
1669 RAISE FND_API.G_EXC_ERROR;
1670 END IF;
1671 END IF;
1672
1673 BEGIN
1674
1675 /* Get the total credited amount for the selected invoice in Oracle Projects. */
1676
1677 SELECT ABS(nvl(SUM(nvl(ii.inv_amount,0)),0))
1678 INTO l_tot_credited_amt
1679 FROM pa_draft_invoices i,
1680 pa_draft_invoice_items ii
1681 WHERE i.project_id = p_project_id
1682 AND ii.project_id = i.project_id
1683 AND ii.draft_invoice_num = i.draft_invoice_num
1684 AND i.draft_invoice_num_credited = p_draft_invoice_num
1685 AND i.canceled_flag IS NULL
1686 AND i.generation_error_flag = 'N';
1687
1688 x_tot_credited_amt := l_tot_credited_amt;
1689
1690 EXCEPTION
1691
1692 WHEN NO_DATA_FOUND THEN
1693
1694 l_tot_credited_amt := 0;
1695
1696 END;
1697
1698 /* Third Check - The following check is for over-credit application. We allow
1699 over-credit application only for credit action Concession. */
1700
1701 IF ( p_invoice_amount < (l_tot_credited_amt + p_credit_amount) ) THEN
1702
1703 /* The IF part is for credit action CONCESSION
1704 This check is to warn the user that the Credit amount entered is going to result
1705 to over-credit. If user chooses to continue, Concession Invoice will get
1706 processed otherwise no. */
1707 IF p_credit_action = 'CONCESSION' THEN
1708 /* Bug 9459594 */
1709 OPEN get_intercompany_flag_csr;
1710 FETCH get_intercompany_flag_csr
1711 INTO l_intercompany_flag;
1712 CLOSE get_intercompany_flag_csr;
1713
1714 IF (l_intercompany_flag = 'Y')
1715 THEN
1716 l_use_inv_org_flag := 'N';
1717 ELSE
1718 OPEN get_centralized_flag_csr;
1719 FETCH get_centralized_flag_csr
1720 INTO l_use_inv_org_flag;
1721 CLOSE get_centralized_flag_csr;
1722 END IF;
1723
1724 IF (l_use_inv_org_flag = 'Y') THEN
1725 OPEN get_misc_details_csr;
1726 FETCH get_misc_details_csr
1727 INTO l_business_group_id,
1728 l_org_st_ver_id,
1729 l_base_language,
1730 l_invoice_date,
1731 l_carry_out_org_id;
1732 CLOSE get_misc_details_csr;
1733 -- Call pa_invoice_xfer.get_trx_crmemo_types to
1734 -- get trx_type_id. Get overapplication flag for
1735 -- trx_type_id.
1736 pa_invoice_xfer.get_trx_crmemo_types(
1737 P_business_group_id=> l_business_group_id,
1738 P_carrying_out_org_id => l_carry_out_org_id,
1739 P_proj_org_struct_version_id => l_org_st_ver_id,
1740 p_basic_language => l_base_language,
1741 p_trans_date => l_invoice_date,
1742 P_trans_type => x_trx_type,
1743 P_crmo_trx_type => x_cm_trx_type,
1744 P_error_status => x_error_status,
1745 P_error_message => x_error_message);
1746 IF (x_error_status = 1) THEN
1747 l_return_status := FND_API.G_RET_STS_ERROR;
1748 l_msg_count := 1;
1749 l_msg_data := x_error_message;
1750 RAISE FND_API.G_EXC_ERROR;
1751 ELSE
1752 -- Get overapplication_flag for x_trx_type
1753 OPEN overapplication_csr_2;
1754 FETCH overapplication_csr_2
1755 INTO l_overapplication_flag;
1756 CLOSE overapplication_csr_2;
1757 END IF;
1758
1759 ELSE
1760 OPEN overapplication_csr;
1761 FETCH overapplication_csr
1762 INTO l_overapplication_flag;
1763 CLOSE overapplication_csr;
1764 END IF;
1765
1766 /* Bug 9459594 */
1767
1768 l_return_status := FND_API.G_RET_STS_ERROR;
1769 IF (NVL(l_overapplication_flag,'N') = 'Y') THEN
1770 l_msg_data := 'PA_OVER_CREDIT_INV';
1771 ELSE
1772 l_msg_data := 'PA_OVER_CREDIT_INV_ERR';
1773 END IF;
1774
1775 l_msg_count := 1;
1776
1777 RAISE FND_API.G_EXC_ERROR;
1778
1779 ELSE
1780 /* The ELSE part is for credit action WRITE-OFF */
1781 /* The balance due in PA should not be <= 0.
1782 Write-Off cannot be performed if balance due <= 0. */
1783 IF sign(p_invoice_amount - l_tot_credited_amt) <> 1 THEN
1784 l_return_status := FND_API.G_RET_STS_ERROR;
1785 --l_msg_data := 'PA_IN_CRD_NOT_POSITIVE';
1786 l_msg_data := 'PA_IN_CRD_NOT_POSITIVE_WO'; --Created new message for Bug3725206
1787 l_msg_count := 1;
1788
1789 RAISE FND_API.G_EXC_ERROR;
1790
1791 ELSE
1792 /* The entered credit amount should not exceed the balance due in PA. */
1793 l_return_status := FND_API.G_RET_STS_ERROR;
1794 l_msg_data := 'PA_IN_CRD_NOT_EXCEED_BAL';
1795 l_msg_count := 1;
1796
1797 RAISE FND_API.G_EXC_ERROR;
1798
1799 END IF;
1800
1801 END IF;
1802
1803 END IF;
1804
1805 /* Fourth check - The entered credit amount should not exceed the balance due in AR
1806 for credit action Write-Off. */
1807 IF p_credit_action = 'WRITE_OFF' THEN
1808 IF p_credit_amount > p_balance_due THEN
1809 l_return_status := FND_API.G_RET_STS_ERROR;
1810 l_msg_data := 'PA_IN_CRD_EXCEED';
1811 l_msg_count := 1;
1812
1813 RAISE FND_API.G_EXC_ERROR;
1814 END IF;
1815 END IF;
1816
1817 /* Initialises the credit related columns as previous credit data might have not been wiped out */
1818
1819 pa_invoice_actions.init_draft_inv_lines(
1820 p_project_id => p_project_id,
1821 p_draft_invoice_num => p_draft_invoice_num,
1822 x_return_status => l_return_status,
1823 x_msg_count => l_msg_count,
1824 x_msg_data => l_msg_data );
1825
1826 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1827
1828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1829
1830 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1831
1832 RAISE FND_API.G_EXC_ERROR;
1833
1834 END IF;
1835
1836
1837 /* Fifth check - The total credit amount should not be zero
1838 except for the credit_action_type of LINE.
1839 The purpose of this check is to not allow credit that total to zero in the header
1840 but allow the same if it is distributed over separate lines - eg +100 and -100 in 2 lines
1841 */
1842
1843 IF sign(nvl(p_credit_amount,0))=0 THEN
1844
1845 if p_credit_action_type = 'LINE' then
1846 null;
1847 else
1848 l_return_status := FND_API.G_RET_STS_ERROR;
1849 l_msg_data := 'PA_IN_ENT_CREDIT_AMT';
1850 l_msg_count := 1;
1851
1852 RAISE FND_API.G_EXC_ERROR;
1853
1854 end if;
1855 END IF;
1856
1857 /* Sixth check - the total credit amount should not be negative */
1858 --For bug 4231721 :Changing the if condition from <>1 to <0 So that check is valid only is Credit amount <0
1859 IF sign(nvl(p_credit_amount,0)) < 0 THEN
1860
1861 l_return_status := FND_API.G_RET_STS_ERROR;
1862 l_msg_data := 'PA_SU_NEGATIVE_NUM_NOT_ALLOWED';
1863 l_msg_count := 1;
1864
1865 RAISE FND_API.G_EXC_ERROR;
1866
1867 END IF;
1868
1869
1870 EXCEPTION
1871
1872 WHEN FND_API.G_EXC_ERROR THEN
1873 x_return_status := l_return_status;
1874 x_msg_count := l_msg_count;
1875 x_msg_data := l_msg_data;
1876
1877 /* ATG Changes */
1878 x_tot_credited_amt := null;
1879
1880 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1881 x_return_status := l_return_status;
1882 x_msg_count := l_msg_count;
1883 x_msg_data := l_msg_data;
1884
1885 /* ATG Changes */
1886 x_tot_credited_amt := null;
1887
1888 WHEN others then
1889 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1890 x_msg_count := l_msg_count;
1891 x_msg_data := SUBSTR(SQLERRM,1,100);
1892
1893 /* ATG Changes */
1894 x_tot_credited_amt := null;
1895
1896 END validate_invoice_amount;
1897
1898 /*-----------------------------------------------------------------------------------------+
1899 | Procedure : init_draft_inv_lines |
1900 | Purpose : To initialize the credit related columns in draft invoice lines table |
1901 | Parameters : |
1902 | ================================================================================== |
1903 | Name Mode Description |
1904 | ================================================================================== |
1905 | p_project_id IN Project ID |
1906 | p_draft_invoice_num IN Draft invoice for which credit action is to be done |
1907 | x_return_status OUT Return status of this procedure |
1908 | x_msg_count OUT Error message count |
1909 | x_msg_data OUT Error message |
1910 | ================================================================================== |
1911 +----------------------------------------------------------------------------------------*/
1912
1913 PROCEDURE init_draft_inv_lines(
1914 p_project_id IN NUMBER,
1915 p_draft_invoice_num IN NUMBER,
1916 x_return_status OUT NOCOPY VARCHAR2,
1917 x_msg_count OUT NOCOPY NUMBER,
1918 x_msg_data OUT NOCOPY VARCHAR2) IS
1919
1920
1921 l_return_status VARCHAR2(30) := NULL;
1922 l_msg_count NUMBER := NULL;
1923 l_msg_data VARCHAR2(250) := NULL;
1924
1925 BEGIN
1926
1927 x_return_status := FND_API.G_RET_STS_SUCCESS;
1928 x_msg_count := 0;
1929
1930 /* Update credit_process_flag, credit_amount to NULL */
1931
1932 update pa_draft_invoice_items
1933 set credit_process_flag = NULL,
1934 credit_amount = NULL
1935 where project_id = p_project_id
1936 and draft_invoice_num = p_draft_invoice_num;
1937
1938 EXCEPTION
1939
1940 WHEN others then
1941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1942 x_msg_count := l_msg_count;
1943 x_msg_data := SUBSTR(SQLERRM,1,100);
1944
1945
1946 END init_draft_inv_lines;
1947
1948
1949 /*----------------------------------------------------------------------------------------------+
1950 | Procedure : update_credit_qual_lines |
1951 | Purpose : To mark the draft invoice lines which have been selected for |
1952 | crediting |
1953 | Parameters : |
1954 | ================================================================================== |
1955 | Name Mode Description |
1956 | ================================================================================== |
1957 | p_project_id IN Project ID |
1958 | p_credit_action IN Indicates if credit is WRITE-OFF/CONCESSION |
1959 | p_credit_action_type IN Indicates if credit action type is |
1960 | SUMMARY/GROUP/LINES |
1961 | p_draft_invoice_num IN Draft invoice for which credit action is to be done |
1962 | p_draft_invoice_line_num IN Draft invoice line which has to be credited |
1963 | p_line_credit_amount IN Total credit amount on the invoice |
1964 | x_return_status OUT Return status of this procedure |
1965 | x_msg_count OUT Error message count |
1966 | x_msg_data OUT Error message |
1967 | ================================================================================== |
1968 +---------------------------------------------------------------------------------------------*/
1969
1970 Procedure update_credit_qual_lines (
1971 p_project_id IN NUMBER,
1972 p_credit_action IN VARCHAR2,
1973 p_credit_action_type IN VARCHAR2,
1974 p_draft_invoice_num IN NUMBER,
1975 p_draft_invoice_line_num IN NUMBER,
1976 p_line_credit_amount IN NUMBER,
1977 x_return_status OUT NOCOPY VARCHAR2,
1978 x_msg_count OUT NOCOPY NUMBER,
1979 x_msg_data OUT NOCOPY VARCHAR2) IS
1980
1981 l_return_status VARCHAR2(30) := NULL;
1982 l_msg_count NUMBER := NULL;
1983 l_msg_data VARCHAR2(250) := NULL;
1984
1985 BEGIN
1986
1987 x_return_status := FND_API.G_RET_STS_SUCCESS;
1988 x_msg_count := 0;
1989
1990 /* In the case of credit action type at
1991
1992 a) Invoice SUMMARY level all the lines of the invoice has to be credited.
1993 Basically the total credit amount will be pro-rated across all the lines. Credit amount will not
1994 be populated in the lines as this scenario exists currently and should go through the same flow.
1995 Only the credit_process_flag will be set to 'Y' for all lines. This is required to indicate that
1996 while calculating the rounding difference the sum of ALL line amounts (credit invoice) should match
1997 the credit amount entered by the user. In this case p_line_credit_amount/p_draft_invoice_line_num will be null
1998
1999 b) GROUP level (user selects specific lines and gives credit amount which has to pro-rated only on those lines)
2000 The total credit amount will be pro-rated across the specified lines by subsequent API's. The line selected for
2001 crediting by the user will be specified by p_draft_invoice_line_num. Since the credit amount will be given as one
2002 whole amount, p_line_credit_amount will be NULL. Credit_process_flag will be set to 'Y' for the line specified
2003 by p_draft_invoice_line_num. While calculating the rounding difference the sum of line amounts
2004 SELECTED BY USER (INFERRED BY CREDIT_PROCESS_FLAG) should match the credit amount entered by the user.
2005 In this case p_line_credit_amount will be null
2006
2007 c) LINES level (user selects the line and specifies the credit amount on the line)
2008 No pro-ration of credit amount is to be performed as it is specified one on one on the line. No rounding also
2009 needs to be checked. Both p_line_credit_amount/p_draft_invoice_line_num will be specified
2010
2011 Both (b) and (c) will be called in a loop for as many lines selected by the user in Invoice Review Form
2012 */
2013
2014 if p_credit_action_type = 'SUMMARY' then
2015
2016 /* Update all lines of draft_invoice_items for credit_process_flag = 'Y' */
2017
2018 update pa_draft_invoice_items
2019 set credit_process_flag = 'Y'
2020 where project_id = p_project_id
2021 and draft_invoice_num = p_draft_invoice_num
2022 /* Added for Payroll Billing ER #11896864 - Start */
2023 and NVL(credit_process_flag, 'N') <> 'X';
2024 /* Added for Payroll Billing ER #11896864 - End */
2025
2026 else
2027
2028
2029 if p_credit_action_type = 'GROUP' then
2030
2031 /* Update specific line of draft_invoice_items for credit_process_flag = 'Y' */
2032
2033 update pa_draft_invoice_items
2034 set credit_process_flag = 'Y'
2035 where project_id = p_project_id
2036 and draft_invoice_num = p_draft_invoice_num
2037 and line_num = p_draft_invoice_line_num
2038 /* Added for Payroll Billing ER #11896864 - Start */
2039 and NVL(credit_process_flag, 'N') <> 'X';
2040 /* Added for Payroll Billing ER #11896864 - End */
2041
2042 elsif p_credit_action_type = 'LINE' then
2043
2044 /* Update specific line of draft_invoice_items for credit_process_flag = 'Y'and credit_amount = p_line_credit_amount */
2045
2046 update pa_draft_invoice_items
2047 set credit_process_flag = 'Y',
2048 credit_amount = p_line_credit_amount
2049 where project_id = p_project_id
2050 and draft_invoice_num = p_draft_invoice_num
2051 and line_num = p_draft_invoice_line_num
2052 /* Added for Payroll Billing ER #11896864 - Start */
2053 and NVL(credit_process_flag, 'N') <> 'X';
2054 /* Added for Payroll Billing ER #11896864 - End */
2055
2056 end if;
2057
2058 end if;
2059
2060 EXCEPTION
2061
2062 WHEN others then
2063 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2064 x_msg_count := l_msg_count;
2065 x_msg_data := SUBSTR(SQLERRM,1,100);
2066
2067 END update_credit_qual_lines;
2068
2069 /*----------------------------------------------------------------------------------------------+
2070 | Procedure : validate_line_credit_amount |
2071 | Purpose : To validate the credit amount on the specificed invoice line |
2072 | Parameters : |
2073 | ================================================================================== |
2074 | Name Mode Description |
2075 | ================================================================================== |
2076 | p_project_id IN Project ID |
2077 | p_credit_action IN Indicates if credit is WRITE-OFF/CONCESSION |
2078 | p_credit_action_type IN Indicates if credit action type is |
2079 | SUMMARY/GROUP/LINES |
2080 | p_draft_invoice_num IN Draft invoice for which credit action is to be done |
2081 | p_draft_invoice_line_num IN Draft invoice line which has to be credited |
2082 | p_inv_amount IN Invoice amount on the specified invoice line |
2083 | p_credit_amount IN Credit amount on the specified invoice line |
2084 | x_return_status OUT Return status of this procedure |
2085 | x_msg_count OUT Error message count |
2086 | x_msg_data OUT Error message |
2087 | ================================================================================== |
2088 +---------------------------------------------------------------------------------------------*/
2089
2090 Procedure validate_line_credit_amount (
2091 p_project_id IN NUMBER,
2092 p_credit_action IN VARCHAR2,
2093 p_credit_action_type IN VARCHAR2,
2094 p_draft_invoice_num IN NUMBER,
2095 p_draft_invoice_line_num IN NUMBER,
2096 p_inv_amount IN NUMBER,
2097 p_credit_amount IN NUMBER,
2098 x_return_status OUT NOCOPY VARCHAR2,
2099 x_msg_count OUT NOCOPY NUMBER,
2100 x_msg_data OUT NOCOPY VARCHAR2) IS
2101
2102 l_return_status VARCHAR2(30) := NULL;
2103 l_msg_count NUMBER := NULL;
2104 l_msg_data VARCHAR2(250) := NULL;
2105
2106
2107 BEGIN
2108
2109 x_return_status := FND_API.G_RET_STS_SUCCESS;
2110 x_msg_count := 0;
2111
2112 /* If credit is performed at GROUP, the total credit amount passed should not exceed the
2113 total selected line invoice amount for credit action WRITE-OFF. For Concession, we allow
2114 over credit application. */
2115
2116 IF p_credit_action_type = 'GROUP' THEN
2117
2118 /* First check - Total credit amount should not exceed the net invoice amount of the selected
2119 lines. This is true for Write-Off only. */
2120 IF p_credit_action = 'CONCESSION' THEN
2121 NULL;
2122 ELSE
2123 IF p_credit_amount > p_inv_amount THEN
2124 l_return_status := FND_API.G_RET_STS_ERROR;
2125 l_msg_data := 'PA_IN_CRD_NOT_EXCEED_BAL';
2126
2127 RAISE FND_API.G_EXC_ERROR;
2128 END IF;
2129 END IF;
2130
2131 ELSE
2132 /* This ELSE part is for the line credit amount check when the user
2133 chooses to enter the credit amount by line. */
2134
2135 /* First check - Line credit amount should be greater than zero */
2136
2137 IF p_credit_amount = 0 THEN
2138 l_return_status := FND_API.G_RET_STS_ERROR;
2139 l_msg_data := 'PA_IN_ENT_CREDIT_AMT';
2140 l_msg_count := 1;
2141
2142 RAISE FND_API.G_EXC_ERROR;
2143
2144 END IF;
2145
2146 /* Second check - Line credit amount should be of the same sign as the line invoice amount */
2147
2148 IF sign(p_inv_amount) <> sign(p_credit_amount) THEN
2149 l_return_status := FND_API.G_RET_STS_ERROR;
2150 l_msg_data := 'PA_IN_CRD_SIGN_INV';
2151 l_msg_count := 1;
2152
2153 RAISE FND_API.G_EXC_ERROR;
2154
2155 END IF;
2156 END IF;
2157
2158
2159 /* Check that credit amount does not exceed invoice amount on the line
2160 This check has been removed from here since the check needs to be done in forms
2161 because we allow over-credit on the line for CONCESSION
2162 */
2163
2164
2165 EXCEPTION
2166
2167 WHEN FND_API.G_EXC_ERROR THEN
2168 x_return_status := l_return_status;
2169 x_msg_count := l_msg_count;
2170 x_msg_data := l_msg_data;
2171
2172 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2173 x_return_status := l_return_status;
2174 x_msg_count := l_msg_count;
2175 x_msg_data := l_msg_data;
2176
2177 WHEN others then
2178 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2179 x_msg_count := l_msg_count;
2180 x_msg_data := SUBSTR(SQLERRM,1,100);
2181
2182 END validate_line_credit_amount;
2183
2184 /*-----------------------------------------------------------------------------------------+
2185 | Procedure : distribute_credit_amount |
2186 | Purpose : To pro-rate the total credit amount across the lines selected for |
2187 | crediting on the specified invoice |
2188 | Parameters : |
2189 | ================================================================================== |
2190 | Name Mode Description |
2191 | ================================================================================== |
2192 | p_project_id IN Project ID |
2193 | p_credit_action IN Indicates if credit is WRITE-OFF/CONCESSION |
2194 | p_credit_action_type IN Indicates if credit action type is |
2195 | SUMMARY/GROUP/LINES |
2196 | p_draft_invoice_num IN Draft invoice for which credit action is to be done |
2197 | p_total_credit_amount IN Total credit amount on the invoice |
2198 | x_return_status OUT Return status of this procedure |
2199 | x_msg_count OUT Error message count |
2200 | x_msg_data OUT Error message |
2201 | ================================================================================== |
2202 +----------------------------------------------------------------------------------------*/
2203
2204
2205 Procedure distribute_credit_amount (
2206 p_project_id IN NUMBER,
2207 p_credit_action IN VARCHAR2,
2208 p_credit_action_type IN VARCHAR2,
2209 p_draft_invoice_num IN NUMBER,
2210 p_total_credit_amount IN NUMBER,
2211 x_return_status OUT NOCOPY VARCHAR2,
2212 x_msg_count OUT NOCOPY NUMBER,
2213 x_msg_data OUT NOCOPY VARCHAR2) IS
2214
2215 l_inv_amount NUMBER;
2216 l_inv_currency_code VARCHAR2(3);
2217 l_return_status VARCHAR2(30) := NULL;
2218 l_msg_count NUMBER := NULL;
2219 l_msg_data VARCHAR2(250) := NULL;
2220
2221 BEGIN
2222
2223 x_return_status := FND_API.G_RET_STS_SUCCESS;
2224 x_msg_count := 0;
2225
2226 -- insert into bss_conc (serial_no, proc_ind) values (1, 'DIST_CREDIT_AMT');
2227
2228 /* This API will be called only in the case of GROUP/LINE level as SUMMARY will go through the existing
2229 flow as defined in paisql.lpc
2230 In case of GROUP, the total credit amount has to be pro-rated across the user selected lines indicated by
2231 credit_process_flag = 'Y'
2232 In case of LINES the credit amount is already populated on the lines that are to be credited
2233 In case of GROUP/LINES, after the credit amount is populated in the standard lines, corresponding retention lines
2234 are also to be credited . A separate API distribute_credit_amount_retn will be called to do the same */
2235
2236 if p_credit_action_type = 'GROUP' then
2237
2238 /* Get the sum of invoice amount of the user selected lines (credit_process_flag = 'Y'). Apply the ratio
2239 of p_total_credit_amount over sum_invoice_amount on the line invoice amount. Inv_currency_code is required
2240 to define the precision of the computed credit amount */
2241
2242 select sum(dii.inv_amount), max(di.inv_currency_code)
2243 into l_inv_amount, l_inv_currency_code
2244 from pa_draft_invoice_items dii, pa_draft_invoices di
2245 where di.project_id = p_project_id
2246 and di.draft_invoice_num = p_draft_invoice_num
2247 and dii.project_id = di.project_id
2248 and dii.draft_invoice_num = di.draft_invoice_num
2249 and nvl(dii.credit_process_flag,'N') = 'Y' ;
2250
2251 update pa_draft_invoice_items dii
2252 set credit_amount =
2253 pa_currency.round_trans_currency_amt(
2254 (inv_amount * (p_total_credit_amount/l_inv_amount)),
2255 rtrim(l_inv_currency_code))
2256 where project_id = p_project_id
2257 and draft_invoice_num = p_draft_invoice_num
2258 and nvl(dii.credit_process_flag,'N') = 'Y' ;
2259
2260 end if;
2261
2262 /* Call API to calculate credit amount for corresponding retention lines */
2263
2264 pa_invoice_actions.distribute_credit_amount_retn(
2265 p_project_id => p_project_id,
2266 p_draft_invoice_num => p_draft_invoice_num,
2267 x_return_status => l_return_status,
2268 x_msg_count => l_msg_count,
2269 x_msg_data => l_msg_data );
2270
2271 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2272
2273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2274
2275 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2276
2277 RAISE FND_API.G_EXC_ERROR;
2278
2279 END IF;
2280
2281
2282
2283 EXCEPTION
2284
2285 WHEN FND_API.G_EXC_ERROR THEN
2286 ROLLBACK;
2287 x_return_status := l_return_status;
2288 x_msg_count := l_msg_count;
2289 x_msg_data := l_msg_data;
2290
2291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2292 ROLLBACK;
2293 x_return_status := l_return_status;
2294 x_msg_count := l_msg_count;
2295 x_msg_data := l_msg_data;
2296
2297 WHEN others then
2298 ROLLBACK;
2299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2300 x_msg_count := l_msg_count;
2301 x_msg_data := SUBSTR(SQLERRM,1,100);
2302
2303 END distribute_credit_amount;
2304
2305 /*-----------------------------------------------------------------------------------------+
2306 | Procedure : distribute_credit_amount_retn |
2307 | Purpose : To distribute the credit amount on the retention lines based on the |
2308 | corresponding standard line's credit amount |
2309 | Parameters : |
2310 | ================================================================================== |
2311 | Name Mode Description |
2312 | ================================================================================== |
2313 | p_project_id IN Project ID |
2314 | p_draft_invoice_num IN Draft invoice for which credit action is to be done |
2315 | x_return_status OUT Return status of this procedure |
2316 | x_msg_count OUT Error message count |
2317 | x_msg_data OUT Error message |
2318 | ================================================================================== |
2319 +----------------------------------------------------------------------------------------*/
2320
2321
2322 Procedure distribute_credit_amount_retn (
2323 p_project_id IN NUMBER,
2324 p_draft_invoice_num IN NUMBER,
2325 x_return_status OUT NOCOPY VARCHAR2,
2326 x_msg_count OUT NOCOPY NUMBER,
2327 x_msg_data OUT NOCOPY VARCHAR2) IS
2328
2329
2330 l_retention_percentage number;
2331 l_retention_rule_id number;
2332 l_retention_line_num number;
2333 l_inv_currency_code varchar2(3);
2334
2335 l_tot_credit_amount number;
2336 l_amount number;
2337 l_credit_amount number;
2338 l_retn_credit_amount number;
2339 l_retained_amount number;
2340 l_line_processed VARCHAR2(5);
2341
2342 l_return_status VARCHAR2(30) := NULL;
2343 l_msg_count NUMBER := NULL;
2344 l_msg_data VARCHAR2(250) := NULL;
2345
2346 BEGIN
2347
2348 x_return_status := FND_API.G_RET_STS_SUCCESS;
2349 x_msg_count := 0;
2350
2351 /* insert into bss_conc
2352 (serial_no, proc_ind)
2353 values(2, 'DISTRIBUTE_RETN_CREDIT');
2354 */
2355 /* Check if retention for this invoice is generated using old/new model
2356 In old model, retention% will be stamped on the draft invoice
2357
2358 This SQL also ensures that the invoice does have RETENTION LINE */
2359
2360 SELECT nvl(retention_percentage,0), inv_currency_code
2361 INTO l_retention_percentage, l_inv_currency_code
2362 FROM pa_draft_invoices
2363 WHERE project_id = p_project_id
2364 AND draft_invoice_num = p_draft_invoice_num
2365 AND EXISTS (select null from pa_draft_invoice_items
2366 WHERE project_id = p_project_id
2367 AND draft_invoice_num = p_draft_invoice_num
2368 AND invoice_line_type = 'RETENTION');
2369
2370 if nvl(l_retention_percentage, 0) <> 0 THEN -- old model
2371
2372 /* In old model, the retention credit amount will be calculated by applying the retention% on the sum of
2373 the credit amount stamped on the draft invoice lines
2374
2375 Since retention amount is always negative, the calculated retention credit amount is multiplied by -1 */
2376
2377 select sum(nvl(credit_amount,0))
2378 into l_tot_credit_amount
2379 from pa_draft_invoice_items
2380 where project_id = p_project_id
2381 and draft_invoice_num = p_draft_invoice_num;
2382
2383 l_retn_credit_amount := (-1) * (l_tot_credit_amount * l_retention_percentage)/100;
2384
2385 /* In old model there will be only one retention line for an invoice. So credit amount is stamped on that line */
2386
2387 update pa_draft_invoice_items
2388 set credit_amount =
2389 pa_currency.round_trans_currency_amt(l_retn_credit_amount, rtrim(l_inv_currency_code))
2390 where project_id = p_project_id
2391 and draft_invoice_num = p_draft_invoice_num
2392 and invoice_line_type = 'RETENTION';
2393
2394 else -- NEW MODEL
2395
2396 /* select the standard lines which are to be credited within a loop
2397 For every line of the draft invoice item, we require the retained amount
2398 (will be in invoice processing currency) , retention rule id and retention_line_num (indicates which line
2399 in the draft_invoice_items corresponds to retention of this standard line.
2400 Once we get these, store these values into local variables and set line_processed_flag to TRUE
2401 Call compute_retn_credit_amount API with the fetched values. The logic to get these values will be:
2402
2403 If the line represents event (event_num will be not null)
2404 the retained amount (will be in invoice processing currency) ,
2405 retention rule id, retention_line_num will be stored on the line itself. Set line_processed flag to TRUE
2406
2407 If line_processed is FALSE, then check in ERDL( Could be WRITE-ON events) with project_id, draft_invoice_num,
2408 and line_num. One line of draft invoice item may have multiple lines in ERDL. Since we need to get the
2409 retention info for every retention_rule_id, we group by retention_rule_id and get sum of the amount.
2410 Set line_processed_flag to TRUE
2411
2412 If line_processed is FALSE, then check in RDL( Could be EI's) with project_id, draft_invoice_num,
2413 and line_num. One line of draft invoice item may have multiple lines in RDL. Since we need to get the
2414 retention info for every retention_rule_id, we group by retention_rule_id and get sum of the amount.
2415 Set line_processed_flag to TRUE
2416 */
2417
2418 for inv_lines in (
2419 select nvl(dii.line_num,0) line_num,
2420 nvl(dii.event_num,0) event_num,
2421 nvl(dii.retn_draft_invoice_line_num,0) retention_line_num,
2422 nvl(dii.retention_rule_id,-1) retention_rule_id,
2423 nvl(dii.retained_amount,0) retained_amount,
2424 dii.amount amount,
2425 dii.credit_amount credit_amount
2426 from pa_draft_invoice_items dii
2427 where project_id = p_project_id
2428 AND draft_invoice_num = p_draft_invoice_num
2429 AND invoice_line_type <> 'RETENTION'
2430 AND nvl(dii.credit_amount, 0) <> 0
2431 order by dii.line_num) LOOP
2432
2433 l_amount := inv_lines.amount;
2434 l_credit_amount := inv_lines.credit_amount;
2435 l_line_processed := 'FALSE';
2436
2437 if nvl(inv_lines.event_num,0) <> 0 then -- events line
2438
2439 if nvl(inv_lines.retained_amount,0) <> 0 then -- retention exists for this line
2440
2441 l_line_processed := 'TRUE';
2442 l_retention_line_num := inv_lines.retention_line_num;
2443 l_retention_rule_id := inv_lines.retention_rule_id;
2444 l_retained_amount := inv_lines.retained_amount;
2445
2446 /* insert into bss_conc values (3, p_project_id, p_draft_invoice_num,
2447 'DII', l_retention_rule_id, l_retention_line_num,
2448 l_retained_amount, l_amount, l_credit_amount, inv_lines.line_num, 'RETN');
2449 */
2450 compute_retn_credit_amount (
2451 p_project_id => p_project_id,
2452 p_draft_invoice_num => p_draft_invoice_num,
2453 p_retention_rule_id => l_retention_rule_id,
2454 p_retention_line_num => l_retention_line_num,
2455 p_retained_amount => l_retained_amount,
2456 p_amount => l_amount,
2457 p_credit_amount => l_credit_amount,
2458 x_return_status => l_return_status,
2459 x_msg_count => l_msg_count,
2460 x_msg_data => l_msg_data );
2461
2462 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2463
2464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2465
2466 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2467
2468 RAISE FND_API.G_EXC_ERROR;
2469
2470 END IF;
2471
2472 end if; -- retained_amount <> 0
2473
2474 end if; -- events line
2475
2476 if l_line_processed = 'FALSE' then
2477
2478 for erdl_lines in (
2479 select nvl(erdl.retn_draft_invoice_line_num, -1) retention_line_num,
2480 nvl(erdl.retention_rule_id,-1) retention_rule_id,
2481 nvl(sum(nvl(erdl.retained_amount,0)),0) retained_amount
2482 from pa_cust_event_rdl_all erdl
2483 where project_id = p_project_id
2484 AND draft_invoice_num = p_draft_invoice_num
2485 AND draft_invoice_item_line_num = inv_lines.line_num
2486 group by nvl(erdl.retn_draft_invoice_line_num,-1) , nvl(erdl.retention_rule_id,-1) ) LOOP
2487
2488 l_line_processed := 'TRUE';
2489 l_retention_line_num := erdl_lines.retention_line_num;
2490 l_retention_rule_id := erdl_lines.retention_rule_id;
2491 l_retained_amount := nvl(erdl_lines.retained_amount,0);
2492
2493 if l_retained_amount <> 0 then -- retention exists for this line
2494
2495 /* insert into bss_conc values (4,p_project_id, p_draft_invoice_num,
2496 'ERDL', l_retention_rule_id, l_retention_line_num,
2497 l_retained_amount, l_amount, l_credit_amount, inv_lines.line_num, 'RETN');
2498 */
2499 compute_retn_credit_amount (
2500 p_project_id => p_project_id,
2501 p_draft_invoice_num => p_draft_invoice_num,
2502 p_retention_rule_id => l_retention_rule_id,
2503 p_retention_line_num => l_retention_line_num,
2504 p_retained_amount => l_retained_amount,
2505 p_amount => l_amount,
2506 p_credit_amount => l_credit_amount,
2507 x_return_status => l_return_status,
2508 x_msg_count => l_msg_count,
2509 x_msg_data => l_msg_data );
2510
2511
2512 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2513
2514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2515
2516 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2517
2518 RAISE FND_API.G_EXC_ERROR;
2519
2520 END IF;
2521
2522 end if; -- l_retained_amunt <> 0
2523
2524 end loop; -- erdl_lines cursor
2525
2526 end if; -- l_line_processed = 'FALSE'
2527
2528 if l_line_processed = 'FALSE' then
2529
2530 for rdl_lines in (
2531 select nvl(rdl.retn_draft_invoice_line_num, -1) retention_line_num,
2532 nvl(rdl.retention_rule_id,-1) retention_rule_id,
2533 sum(nvl(rdl.retained_amount,0)) retained_amount
2534 from pa_cust_rev_dist_lines_all rdl
2535 where project_id = p_project_id
2536 AND draft_invoice_num = p_draft_invoice_num
2537 AND draft_invoice_item_line_num = inv_lines.line_num
2538 group by nvl(rdl.retn_draft_invoice_line_num,-1) , nvl(rdl.retention_rule_id,-1) ) LOOP
2539
2540 l_line_processed := 'TRUE';
2541 l_retention_line_num := rdl_lines.retention_line_num;
2542 l_retention_rule_id := rdl_lines.retention_rule_id;
2543 l_retained_amount := nvl(rdl_lines.retained_amount,0);
2544
2545 if l_retained_amount <> 0 then -- retention exists for this line
2546
2547 /* insert into bss_conc values (5, p_project_id, p_draft_invoice_num,
2548 'RDL', l_retention_rule_id, l_retention_line_num,
2549 l_retained_amount, l_amount, l_credit_amount, inv_lines.line_num,'RETN');
2550 */
2551 compute_retn_credit_amount (
2552 p_project_id => p_project_id,
2553 p_draft_invoice_num => p_draft_invoice_num,
2554 p_retention_rule_id => l_retention_rule_id,
2555 p_retention_line_num => l_retention_line_num,
2556 p_retained_amount => l_retained_amount,
2557 p_amount => l_amount,
2558 p_credit_amount => l_credit_amount,
2559 x_return_status => l_return_status,
2560 x_msg_count => l_msg_count,
2561 x_msg_data => l_msg_data );
2562
2563 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2564
2565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2566
2567 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2568
2569 RAISE FND_API.G_EXC_ERROR;
2570
2571 END IF;
2572
2573 end if; -- l_retained_amunt <> 0
2574
2575 end loop; -- rdl_lines cursor
2576
2577 end if; -- l_line_processed = 'FALSE'
2578
2579 end loop; -- inv_lines cursor
2580
2581 /* Since retention credit amount in the original invoice should be negative and precision should be based
2582 on currency code we multiply by -1 and round the amount based on inv_currency_code
2583 */
2584 update pa_draft_invoice_items
2585 set credit_amount =
2586 pa_currency.round_trans_currency_amt(credit_amount, rtrim(l_inv_currency_code)) * -1
2587 where project_id = p_project_id
2588 and draft_invoice_num = p_draft_invoice_num
2589 and invoice_line_type = 'RETENTION';
2590
2591 end if; -- NEW MODEL
2592
2593 EXCEPTION
2594
2595 WHEN no_data_found then
2596 pa_mcb_invoice_pkg.log_message('No retention lines');
2597
2598 WHEN FND_API.G_EXC_ERROR THEN
2599 ROLLBACK;
2600 x_return_status := l_return_status;
2601 x_msg_count := l_msg_count;
2602 x_msg_data := l_msg_data;
2603
2604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2605 ROLLBACK;
2606 x_return_status := l_return_status;
2607 x_msg_count := l_msg_count;
2608 x_msg_data := l_msg_data;
2609
2610 WHEN others then
2611 ROLLBACK;
2612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2613 x_msg_count := l_msg_count;
2614 x_msg_data := SUBSTR(SQLERRM,1,100);
2615
2616 END distribute_credit_amount_retn;
2617
2618
2619 /*-----------------------------------------------------------------------------------------+
2620 | Procedure : compute_retn_credit_amount |
2621 | Purpose : To compute retention credit amount based on the retention setup of |
2622 | the standard lines |
2623 | Parameters : |
2624 | ================================================================================== |
2625 | Name Mode Description |
2626 | ================================================================================== |
2627 | p_project_id IN Project ID |
2628 | p_draft_invoice_num IN Draft invoice for which retention is to be credited |
2629 | p_retention_rule_id IN Retention rule id applied on the standard line |
2630 | p_retention_line_num IN Draft invoice line num on which retention credit amt |
2631 | is to be stamped/computed |
2632 | p_retained_amount IN Retained amount on the standard invoice line |
2633 | p_amount IN Line amount of the standard line for which retention |
2634 | credit amount is to be computed |
2635 | p_credit_amount IN Line credit amount of the standard line for which |
2636 | retention credit amount is to be computed |
2637 | x_return_status OUT Return status of this procedure |
2638 | x_msg_count OUT Error message count |
2639 | x_msg_data OUT Error message |
2640 | ================================================================================== |
2641 +----------------------------------------------------------------------------------------*/
2642
2643 Procedure compute_retn_credit_amount (
2644 p_project_id IN NUMBER,
2645 p_draft_invoice_num IN NUMBER,
2646 p_retention_rule_id IN NUMBER,
2647 p_retention_line_num IN NUMBER,
2648 p_retained_amount IN NUMBER,
2649 p_amount IN NUMBER,
2650 p_credit_amount IN NUMBER,
2651 x_return_status OUT NOCOPY VARCHAR2,
2652 x_msg_count OUT NOCOPY NUMBER,
2653 x_msg_data OUT NOCOPY VARCHAR2) IS
2654
2655 l_threshold_amount number;
2656 l_retention_percentage number;
2657 l_retention_amount number;
2658 l_retn_credit_amount number;
2659
2660 l_return_status VARCHAR2(30) := NULL;
2661 l_msg_count NUMBER := NULL;
2662 l_msg_data VARCHAR2(250) := NULL;
2663 BEGIN
2664
2665 x_return_status := FND_API.G_RET_STS_SUCCESS;
2666 x_msg_count := 0;
2667
2668 /* If the retention setup defines only the retention % (no threshold_amount, no retention amount)
2669 then this percentage will be applied on the line credit amount (p_credit_amount) to get retention
2670 credit amount
2671
2672 If there is a threshold amount/retention amount defined, it is not possible to figure out the value of these
2673 amounts at the point invoice was generated. So we get the ratio of retained amount over line amount of the
2674 standard line on the line credit amount to get retention credit amount */
2675
2676 select nvl(threshold_amount,0), nvl(retention_percentage,0), nvl(retention_amount,0)
2677 into l_threshold_amount, l_retention_percentage, l_retention_amount
2678 from pa_proj_retn_rules
2679 where retention_rule_id = p_retention_rule_id;
2680
2681
2682 if (l_threshold_amount <> 0) OR (l_retention_amount <> 0) then
2683 l_retn_credit_amount := (p_retained_amount/p_amount) * p_credit_amount ;
2684 else
2685
2686 l_retn_credit_amount := (p_credit_amount * l_retention_percentage) / 100 ;
2687
2688 end if;
2689
2690
2691 /* Since one line of draft invoice item may have multiple lines in RDL/ERDL this procedure may be called more than
2692 once for the same line. So credit amount is acutally summed up */
2693
2694 update pa_draft_invoice_items
2695 set credit_amount = nvl(credit_amount ,0) + l_retn_credit_amount
2696 where project_id = p_project_id
2697 and draft_invoice_num = p_draft_invoice_num
2698 and line_num = p_retention_line_num;
2699
2700
2701 EXCEPTION
2702
2703 WHEN no_data_found then
2704 pa_mcb_invoice_pkg.log_message('No retention lines');
2705
2706 WHEN others then
2707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2708 x_msg_count := l_msg_count;
2709 x_msg_data := SUBSTR(SQLERRM,1,100);
2710
2711 END compute_retn_credit_amount;
2712
2713 /* End Concession invoice modification */
2714
2715 /*------------- End of Public Procedure/Function Declarations ----------------*/
2716
2717 end PA_Invoice_Actions;
[Home] [Help]