[Home] [Help]
PACKAGE BODY: APPS.GMS_BILLING_ADJUSTMENTS
Source
1 PACKAGE BODY GMS_BILLING_ADJUSTMENTS AS
2 -- $Header: gmsinadb.pls 120.3 2006/03/23 20:42:31 appldev ship $
3
4 -- To check on, whether to print debug messages in log file or not
5 L_DEBUG varchar2(1) := NVL(FND_PROFILE.value('GMS_ENABLE_DEBUG_MODE'), 'N');
6
7 -- Variable set in procedure wriinv and used in procedure write_off_creation
8 g_request_id number;
9
10 -- Procedure HANDLE_NET_ZERO_EVENTS
11 -- Procedure added for: 4594090
12 PROCEDURE HANDLE_NET_ZERO_EVENTS (P_AWARD_PROJECT_ID IN NUMBER,
13 P_REQUEST_ID IN NUMBER,
14 P_CALLING_PROCESS IN VARCHAR2)
15 IS
16 Cursor c_gspf_update is
17 select gea.installment_id,
18 gea.actual_project_id,
19 gea.actual_task_id,
20 pt.top_task_id,
21 sum(gea.bill_amount) billed_amount -- null handling not reqd.
22 from gms_event_attribute gea,
23 pa_tasks pt
24 where gea.project_id = P_AWARD_PROJECT_ID
25 and gea.event_num in ( -1,-2)
26 and gea.request_id = P_REQUEST_ID
27 and gea.event_calling_process = 'Invoice'
28 and pt.task_id = gea.actual_task_id
29 group by gea.installment_id,
30 gea.actual_project_id,
31 gea.actual_task_id,
32 pt.top_task_id;
33 BEGIN
34
35 -- A. Update gms_summary_project_fundings ....
36 -- This is required for Invoice only as invoice events can
37 -- span across tasks. Revenue is always grouped by tasks ..
38
39 If p_calling_process = 'INVOICE' then
40
41 for x in c_gspf_update loop
42 Update gms_summary_project_fundings gspf
43 set gspf.total_billed_amount = nvl(gspf.total_billed_amount,0) -
44 x.billed_amount
45 where gspf.installment_id = x.installment_id
46 and gspf.project_id = x.actual_project_id
47 and (gspf.task_id is NULL or
48 gspf.task_id = x.actual_task_id or
49 gspf.task_id = x.top_task_id);
50 end loop;
51
52 End If;
53
54 -- B. Update ADL Flag ..
55 Update gms_award_distributions adl
56 set adl.billed_flag = decode(p_calling_process,'REVENUE',
57 adl.billed_flag,'N'),
58 adl.revenue_distributed_flag = decode(p_calling_process,'INVOICE',
59 adl.revenue_distributed_flag,'N')
60 where (expenditure_item_id,adl_line_num) in
61 (select expenditure_item_id,adl_line_num
62 from gms_event_intersect
63 where award_project_id = p_award_project_id
64 and request_id = p_request_id
65 and event_type = p_calling_process
66 and event_num = -1 /*Added for bug 5060427*/
67 UNION ALL
68 select expenditure_item_id,adl_line_num
69 -- from gms_event_intersect /* Commented for bug 5060427 */
70 from gms_burden_components /*Added for bug 5060427*/
71 where award_project_id = p_award_project_id
72 and request_id = p_request_id
73 and event_type = p_calling_process
74 and event_num = -2 /*Added for bug 5060427*/ )
75 and document_type = 'EXP'
76 and adl_status = 'A';
77
78 IF SQL%ROWCOUNT > 0 then
79 -- there are some records to process ..
80 -- C. Delete gei
81 Delete from gms_event_intersect
82 where award_project_id = p_award_project_id
83 and event_num = -1
84 and request_id = p_request_id
85 and event_type = p_calling_process;
86
87 -- D. Delete gbc
88 Delete from gms_burden_components
89 where award_project_id = p_award_project_id
90 and event_num = -2
91 and request_id = p_request_id
92 and event_type = p_calling_process;
93
94 -- E. Delete gea
95 Delete from gms_event_attribute
96 where project_id = p_award_project_id
97 and event_num in ( -1,-2)
98 and request_id = p_request_id
99 and event_calling_process = INITCAP(p_calling_process);
100 END IF;
101 END HANDLE_NET_ZERO_EVENTS;
102
103
104 -- PROCEDURE INSERT_BILL_CANCEL, new procedure to account for deleted revenue items in ASI
105
106 PROCEDURE INSERT_BILL_CANCEL(X_Award_Project_Id IN NUMBER,
107 X_Event_Num IN NUMBER,
108 X_Expenditure_item_id IN NUMBER DEFAULT null,
109 X_Adl_Line_No IN NUMBER DEFAULT null,
110 X_Bill_Amount IN NUMBER,
111 X_Calling_Process IN VARCHAR2,
112 X_Burden_Exp_Type IN VARCHAR2 DEFAULT null,
113 X_Burden_Cost_Code IN VARCHAR2 DEFAULT null,
114 X_Creation_Date IN DATE,
115 X_Actual_Project_Id IN NUMBER,
116 X_Actual_Task_Id IN NUMBER,
117 X_Expenditure_Org_Id IN NUMBER,
118 X_Deletion_Date IN DATE,
119 X_Resource_List_Member_Id IN NUMBER DEFAULT null,
120 X_Err_Code IN OUT NOCOPY NUMBER,
121 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
122
123 Begin
124 /* Inserting into gms_billing_cancellations table */
125
126 INSERT INTO GMS_BILLING_CANCELLATIONS (AWARD_PROJECT_ID,
127 EVENT_NUM,
128 EXPENDITURE_ITEM_ID,
129 ADL_LINE_NUM,
130 BILL_AMOUNT,
131 CALLING_PROCESS,
132 BURDEN_EXP_TYPE,
133 BURDEN_COST_CODE,
134 CREATION_DATE,
135 ACTUAL_PROJECT_ID,
136 ACTUAL_TASK_ID,
137 EXPENDITURE_ORG_ID,
138 DELETION_DATE,
139 RESOURCE_LIST_MEMBER_ID)
140 VALUES(X_Award_Project_Id,
141 X_Event_Num,
142 X_Expenditure_item_id,
143 X_Adl_Line_No,
144 X_Bill_Amount,
145 X_Calling_Process,
146 X_Burden_Exp_Type,
147 X_Burden_Cost_Code,
148 X_Creation_Date,
149 X_Actual_Project_Id,
150 X_Actual_Task_Id,
151 X_Expenditure_Org_Id,
152 X_Deletion_Date,
153 X_Resource_List_Member_Id
154 );
155
156 X_Err_Code := 0;
157
158 Exception
159 WHEN OTHERS THEN
160 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
161 'SQLCODE',
162 SQLCODE,
163 'SQLERRM',
164 SQLERRM,
165 X_Exec_Type => 'C',
166 X_Err_Code => X_Err_Code,
167 X_Err_Buff => X_Err_Buff);
168 RAISE_APPLICATION_ERROR(-20200,X_Err_Buff);
169 End INSERT_BILL_CANCEL;
170
171
172 -- PROCEDURE UPD_GMS_SUMMARY_PRJ_FUNDS, new procedure for project funding table updation for write_off
173
174 PROCEDURE UPD_GMS_SUMMARY_PRJ_FUNDS(X_Actual_Project_Id IN NUMBER,
175 X_Actual_Task_Id IN NUMBER,
176 X_Installment_id IN NUMBER,
177 X_Amount IN NUMBER,
178 X_Process IN VARCHAR2,
179 X_Err_Code IN OUT NOCOPY NUMBER,
180 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
181 Begin
182
183 /* Write_off Deletion/Cancellation */
184
185 If X_Process = 'WRITE_OFF_DEL' then
186
187 Update gms_summary_project_fundings
188 set total_billed_amount = total_billed_amount + X_Amount
189 where project_id = X_Actual_Project_Id
190 and (task_id is null
191 or task_id = X_Actual_Task_id
192 or task_id = (select top_task_id from pa_tasks where task_id = X_Actual_Task_id) -- Bug 2369179,Added
193 )
194 and installment_id = X_Installment_id;
195
196 /* Write_off Creation */
197
198 ElsIf X_Process = 'WRITE_OFF_GEN' then
199
200 Update gms_summary_project_fundings
201 set total_billed_amount = total_billed_amount - X_Amount
202 where project_id = X_Actual_Project_Id
203 and (task_id is null
204 or task_id = X_Actual_Task_id
205 or task_id = (select top_task_id from pa_tasks where task_id = X_Actual_Task_id) -- Bug 2369179,Added
206 )
207 and installment_id = X_Installment_id;
208
209 End if;
210
211 If SQL%NOTFOUND THEN
212
213 gms_error_pkg.gms_message('GMS_NO_UPD_PRJ_TASK_INST',
214 'PRJ',
215 X_Actual_Project_Id,
216 'TASK',
217 X_Actual_Task_Id,
218 'INST',
219 X_Installment_Id,
220 X_Exec_Type => 'C',
221 X_Err_Code => X_Err_Code,
222 X_Err_Buff => X_Err_Buff);
223 RAISE_APPLICATION_ERROR(-20201,X_Err_Buff);
224 Else
225 X_Err_Code := 0;
226 End If;
227
228 End UPD_GMS_SUMMARY_PRJ_FUNDS;
229
230 -- PROCEDURE WRITE_OFF_CREATION, new procedure for write_off creation
231 PROCEDURE WRITE_OFF_CREATION(X_Award_Project_Id IN NUMBER,
232 X_Action IN VARCHAR2,
233 X_Err_Code IN OUT NOCOPY NUMBER,
234 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
235 Cursor c_invoice_lines is
236 select pdii.event_num event_num,
237 -1*pdii.amount amount
238 from pa_draft_invoice_items pdii,
239 pa_draft_invoices pdi
240 where pdi.project_id = X_Award_Project_Id
241 and pdi.request_id = g_request_id
242 and pdii.project_id = pdi.project_id
243 and pdii.draft_invoice_num = pdi.draft_invoice_num
244 and (nvl(pdi.write_off_flag,'N') = 'Y' OR nvl(pdi.concession_flag,'N') = 'Y');
245
246 Cursor c_event_attribute(p_award_project_id in number,
247 p_event_num in number) is
248 select gea.installment_id,
249 gea.actual_Project_Id,
250 gea.Actual_Task_id,
251 gea.bill_amount,
252 gea.rowid
253 from gms_event_attribute gea
254 where gea.project_id = p_award_project_id
255 and gea.event_num = p_event_num;
256
257 F_Total_Bill_Amt gms_event_attribute.bill_amount%type :=0;
258 F_prorate_amt gms_event_attribute.bill_amount%type :=0;
259 F_Amount_written_off gms_event_attribute.bill_amount%type :=0;
260
261 F_Event_Count Number := 0;
262 F_Counter Number := 0;
263 F_Stage VARCHAR2(25);
264
265 BEGIN
266 X_Err_Code := 0;
267 F_Stage := 'Set currency info';
268 pa_currency.set_currency_info; --For Bug 2895874
269
270 For invoice_line in c_invoice_lines
271 Loop
272 F_Stage := 'Get Total Event Inv Amt';
273 select sum(bill_amount),count(*)
274 into F_Total_Bill_Amt,F_Event_Count
275 from gms_event_attribute
276 where project_id = X_award_project_id
277 and event_num = Invoice_line.event_num;
278
279 F_Stage := 'Main Processing';
280 for event_attribute in c_event_attribute(X_award_project_id,
281 invoice_line.event_num)
282 Loop -- event attribute loop
283
284 -- 1. Calculate Prorate Amt.
285 -- If .. end if required for Rounding ( Bug 2895874)
286 If F_Counter = F_Event_count - 1 then
287
288 F_Prorate_Amt := invoice_line.amount - F_Amount_written_off;
289 -- Prorate amt is the remaining line amount ..
290
291 Else
292 -- Formula:
293 -- Prorate Amt = ((Event attribute bill amount) * (Invoice line amount))
294 -- ------------------------------------------------------
295 -- ( Total Event attribute amount for the event )
296
297 F_prorate_amt := (event_attribute.bill_amount * invoice_line.amount)/F_Total_Bill_Amt;
298 F_prorate_amt := pa_currency.round_currency_amt(F_prorate_amt);
299
300 F_Amount_written_off := F_Amount_written_off + F_prorate_amt;
301
302 End If;
303
304 -- 2. Update gea
305 Begin
306 Update gms_event_attribute
307 set bill_amount = bill_amount - F_prorate_amt,
308 write_off_amount = nvl(write_off_amount,0) + F_prorate_amt
309 where rowid = event_attribute.rowid;
310
311 If SQL%NOTFOUND THEN
312 gms_error_pkg.gms_message('GMS_NO_UPD_PRJ_EVT_INST',
313 'PRJ',
314 X_Award_Project_Id,
315 'EVT',
316 Invoice_line.event_num,
317 'INST',
318 event_attribute.Installment_Id,
319 X_Exec_Type => 'C',
320 X_Err_Code => X_Err_Code,
321 X_Err_Buff => X_Err_Buff);
322 RAISE_APPLICATION_ERROR(-20203,X_Err_Buff);
323 Else
324 X_Err_Code := 0;
325 End If;
326 End;
327
328 -- 3. Update gspf:
329
330 /* Update gms_summary_project_fundings */
331
332 UPD_GMS_SUMMARY_PRJ_FUNDS(event_attribute.Actual_project_id,
333 event_attribute.Actual_Task_id,
334 event_attribute.Installment_Id,
335 F_prorate_amt,
336 'WRITE_OFF_GEN',
337 X_Err_Code,
338 X_Err_Buff);
339
340 If X_Err_Code <> 0 then
341 RAISE FND_API.G_EXC_ERROR;
342 End If;
343
344 -- 4. Re initialize variables
345 F_Counter := F_Counter + 1;
346
347 End Loop; -- event attribute loop
348
349 -- Re initialize variables for next event ..
350 F_Total_Bill_Amt := 0;
351 F_Counter := 0;
352 F_Event_Count := 0;
353 F_Amount_written_off:= 0;
354 F_Prorate_amt :=0;
355
356 End loop;
357
358 EXCEPTION
359 WHEN OTHERS THEN
360 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
361 'SQLCODE',
362 SQLCODE,
363 'SQLERRM',
364 F_Stage||SQLERRM,
365 X_Exec_Type => 'C',
366 X_Err_Code => X_Err_Code,
367 X_Err_Buff => X_Err_Buff);
368 RAISE_APPLICATION_ERROR(-20204,X_Err_Buff);
369 END WRITE_OFF_CREATION;
370
371 -- PROCEDURE WRITE_OFF_DELETION, new procedure for write_off invoice deletions/cancellations
372
373 PROCEDURE WRITE_OFF_DELETION(X_Award_Project_Id IN NUMBER,
374 X_Draft_Invoice_Num IN NUMBER,
375 X_Err_Code IN OUT NOCOPY NUMBER,
376 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
377
378 Cursor Get_Invoice_Items is
379 Select project_id,
380 event_num,
381 -1*amount
382 from pa_draft_invoice_items
386 F_invoice_project_id pa_draft_invoice_items.project_id%type;
383 where draft_invoice_num = X_Draft_Invoice_Num
384 and project_id = X_Award_Project_Id;
385
387 F_invoice_event_num pa_draft_invoice_items.event_num%type;
388 F_invoice_amount pa_draft_invoice_items.amount%type;
389
390 Cursor Get_Gms_Event_Lines is
391 Select project_id,
392 event_num,
393 installment_id,
394 write_off_amount,
395 actual_project_id,
396 actual_task_id,
397 rowid
398 from gms_event_attribute
399 where project_id = F_invoice_Project_id
400 and event_num = F_invoice_Event_Num;
401
402 F_project_id gms_event_attribute.project_id%type;
403 F_event_num gms_event_attribute.event_num%type;
404 F_installment_id gms_event_attribute.installment_id%type;
405 F_write_off_amount gms_event_attribute.write_off_amount%type;
406 F_actual_project_id gms_event_attribute.actual_project_id%type;
407 F_actual_task_id gms_event_attribute.actual_task_id%type;
408
409 Upd_amount gms_event_attribute.write_off_amount%type;
410 X_Total_Write_Off_Amt gms_event_attribute.write_off_amount%type;
411 F_rowid varchar2(50);
412 F_Event_Count Number := 0;
413 F_Counter Number := 0;
414 F_Amount_written_off gms_event_attribute.bill_amount%type :=0;
415
416 BEGIN
417
418 X_Err_Code := 0;
419 pa_currency.set_currency_info; --For Bug 2895874
420
421 Open Get_Invoice_items;
422 Loop
423 Fetch Get_Invoice_items
424 into F_Invoice_Project_Id, F_Invoice_Event_Num, F_Invoice_Amount;
425
426 Exit When Get_Invoice_items%notfound;
427
428 /* Start - Get Total Write_off Amount */
429
430 BEGIN
431
432 select sum(nvl(write_off_amount,0)),count(*)
433 into X_Total_Write_Off_Amt,F_Event_Count
434 from gms_event_attribute
435 where project_id = F_invoice_project_id
436 and event_num = F_Invoice_event_num;
437
438 X_Err_Code := 0;
439
440 EXCEPTION
441 WHEN OTHERS THEN
442 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
443 'SQLCODE',
444 SQLCODE,
445 'SQLERRM',
446 SQLERRM,
447 X_Exec_Type => 'C',
448 X_Err_Code => X_Err_Code,
449 X_Err_Buff => X_Err_Buff);
450 RAISE_APPLICATION_ERROR(-20204,X_Err_Buff);
451 END;
452
453 /* End - Get Total Write_off Amount */
454
455
456 Open Get_Gms_Event_Lines;
457 Loop
458 Fetch Get_Gms_Event_Lines
459 Into F_project_id, F_event_num, F_installment_id, F_write_off_amount,
460 F_actual_project_id, F_actual_task_id, F_rowid;
461
462 EXIT WHEN Get_Gms_Event_Lines%NOTFOUND;
463
464 If F_Counter = F_Event_count - 1 then
465 Upd_amount := F_Invoice_Amount - F_Amount_written_off;
466 Else
467 --For Bug 2895874 :Introduced pa_currency.round_curreny_amt
468 Upd_amount := pa_currency.round_currency_amt((F_write_off_amount * F_Invoice_Amount)/X_Total_Write_Off_Amt);
469 F_Amount_written_off := F_Amount_written_off + Upd_amount;
470 End If;
471
472 /* Start Update gms_event_attribute */
473
474 BEGIN
475
476 Update gms_event_attribute
477 set write_off_amount = write_off_amount - Upd_amount,
478 bill_amount = bill_amount + Upd_amount
479 where rowid = F_rowid;
480
481
482 If SQL%NOTFOUND THEN
483 gms_error_pkg.gms_message('GMS_NO_DATA_PRJ_EVT_INST',
484 'PRJ',
485 F_Project_Id,
486 'EVT',
487 F_event_num,
488 'INST',
489 F_Installment_Id,
490 X_Exec_Type => 'C',
491 X_Err_Code => X_Err_Code,
492 X_Err_Buff => X_Err_Buff);
493 RAISE_APPLICATION_ERROR(-20205,X_Err_Buff);
494 Else
495 X_Err_Code := 0;
496 End If;
497
498 END;
499
500 /* End - Update gms_event_attribute */
501
502 /* Update gms_summary_project_fundings */
503
504 UPD_GMS_SUMMARY_PRJ_FUNDS(F_actual_project_id,
505 F_actual_task_id,
506 F_installment_id,
507 Upd_amount,
508 'WRITE_OFF_DEL',
509 X_Err_Code,
510 X_Err_Buff);
511
512 If X_Err_Code <> 0 then
513 RAISE FND_API.G_EXC_ERROR;
514 End If;
515 F_Counter := F_Counter + 1;
516 End Loop; -- get_gms_event_lines
517
518 Close Get_Gms_Event_Lines;
519
520 -- Re-initalize variables
521 F_Amount_written_off := 0;
522 Upd_amount := 0;
523 F_Counter := 0;
524 F_Event_Count := 0;
525
526 End Loop; -- get_invoice_items;
527
528 END WRITE_OFF_DELETION;
529
530 -- Procedure DELETE_GMS_EVENT_ATTRIBUTE deletes the gms_event_attribute records
531 -- Bug 2979125 : added parameter calling_process
532 Procedure DELETE_GMS_EVENT_ATTRIBUTE(X_Award_Project_Id IN NUMBER,
533 X_Event_Num IN NUMBER,
537
534 X_calling_process IN VARCHAR2,
535 X_Err_Code IN OUT NOCOPY NUMBER,
536 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
538 Begin
539
540 DELETE
541 FROM gms_event_attribute
542 WHERE project_id=X_Award_Project_id
543 AND event_num=X_Event_Num
544 AND event_calling_process= x_calling_process ; -- Bug 2979125 : added filter calling_process
545
546 If SQL%ROWCOUNT = 0 then
547 gms_error_pkg.gms_message('GMS_NO_UPD_PRJ_EVT',
548 'PRJ',
549 X_Award_Project_id,
550 'EVT',
551 X_Event_Num,
552 X_Exec_Type => 'C',
553 X_Err_Code => X_Err_Code,
554 X_Err_Buff => X_Err_Buff);
555 RAISE_APPLICATION_ERROR(-20206,X_Err_Buff);
556 Else
557 X_Err_Code := 0;
558 End If;
559
560 End DELETE_GMS_EVENT_ATTRIBUTE;
561
562 Procedure GET_SUMM_REV_BILL_AMT(X_Installment_Id IN NUMBER,
563 X_Act_Project_Id IN NUMBER,
564 X_Act_Task_Id IN NUMBER,
565 X_Calling_Process IN VARCHAR2,
566 X_Amount OUT NOCOPY NUMBER,
567 X_Err_Code IN OUT NOCOPY NUMBER,
568 X_Err_Buff IN OUT NOCOPY VARCHAR2)IS
569 St_Amount NUMBER(22,5) := 0;
570
571 Begin
572
573 Select
574 decode(X_Calling_Process,'Invoice',nvl(spf.total_billed_amount,0),'Revenue',nvl(spf.total_revenue_amount,0))
575 into
576 St_Amount
577 from
578 GMS_SUMMARY_PROJECT_FUNDINGS spf
579 where
580 spf.installment_id = X_Installment_Id
581 and spf.project_id = X_Act_Project_Id
582 and (
583 (spf.task_id IS NULL)
584 OR (spf.task_id = X_Act_Task_Id)
585 OR (spf.task_id = (select top_task_id from pa_tasks where task_id = X_Act_Task_Id))
586 );
587
588 X_Amount := St_Amount;
589
590 X_Err_Code := 0;
591
592 EXCEPTION
593 WHEN NO_DATA_FOUND THEN
594 gms_error_pkg.gms_message('GMS_NO_UPD_PRJ_TASK_INST',
595 'PRJ',
596 X_Act_Project_Id,
597 'TASK',
598 X_Act_Task_Id,
599 'INST',
600 X_Installment_Id,
601 X_Exec_Type => 'C',
602 X_Err_Code => X_Err_Code,
603 X_Err_Buff => X_Err_Buff);
604 RAISE_APPLICATION_ERROR(-20207,X_Err_Buff);
605 WHEN OTHERS THEN
606 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
607 'SQLCODE',
608 SQLCODE,
609 'SQLERRM',
610 SQLERRM,
611 X_Exec_Type => 'C',
612 X_Err_Code => X_Err_Code,
613 X_Err_Buff => X_Err_Buff);
614 RAISE_APPLICATION_ERROR(-20208,X_Err_Buff);
615 End GET_SUMM_REV_BILL_AMT;
616
617 Procedure MANIP_BILLREV_AMOUNT(X_Award_Project_id IN NUMBER,
618 X_Event_Num IN NUMBER,
619 X_Calling_Process IN VARCHAR2,
620 X_Err_Code IN OUT NOCOPY NUMBER,
621 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
622 X_Curr_Amount NUMBER(22,5);
623 X_Amt_To_Update NUMBER(22,5);
624
625 Cursor get_event_details is
626 select installment_id,
627 actual_project_id,
628 actual_task_id,
629 decode(X_calling_Process,'Invoice',bill_amount,'Revenue',revenue_amount) Amount
630 from gms_event_attribute
631 where project_id = X_Award_Project_id
632 and event_num = X_Event_Num;
633
634 F_Installment_id gms_event_attribute.installment_id%type;
635 F_actual_project_id gms_event_attribute.actual_project_id%type;
636 F_actual_task_id gms_event_attribute.actual_task_id%type;
637 F_amount gms_event_attribute.bill_amount%type;
638
639 Begin
640
641 X_Err_Code := 0;
642
643 OPEN get_event_details;
644 LOOP
645 FETCH get_event_details
646 INTO F_Installment_id,F_actual_project_id,F_actual_task_id,F_amount;
647
648 EXIT WHEN get_event_details%NOTFOUND;
649
650 Begin
651 GET_SUMM_REV_BILL_AMT(F_Installment_id,
652 F_actual_project_id,
653 F_actual_task_id,
654 X_Calling_Process,
655 X_Curr_Amount,
656 X_Err_Code,
657 X_Err_Buff);
658
659 If X_Err_Code <> 0 then
660 RAISE FND_API.G_EXC_ERROR;
661 End If;
662
663 /* Amount To Update */
664 X_Amt_To_Update := (X_Curr_Amount - F_amount);
665
666 End;
667
668 /* Update GMS_SUMMARY_PROJECT_FUNDINGS */
669 Begin
670 If X_Calling_Process = 'Invoice' then
671
672 update GMS_SUMMARY_PROJECT_FUNDINGS spf
673 set
674 spf.Total_Billed_Amount = X_Amt_To_Update
675 ,spf.last_update_date = sysdate
676 ,spf.last_update_login = fnd_global.login_id
677 ,spf.last_updated_by = fnd_global.user_id
678 where
679 spf.installment_id = F_Installment_id
680 and spf.project_id = F_actual_project_id
681 and (
682 (spf.task_id IS NULL)
686
683 OR (spf.task_id = F_actual_task_id)
684 OR (spf.task_id = (select t.top_task_id from pa_tasks t where t.task_id = F_actual_task_id))
685 );
687 Elsif
688
689 X_Calling_Process = 'Revenue' then
690 update GMS_SUMMARY_PROJECT_FUNDINGS spf
691 set
692 spf.Total_Revenue_Amount = X_Amt_To_Update
693 ,spf.last_update_date = sysdate
694 ,spf.last_update_login = fnd_global.login_id
695 ,spf.last_updated_by = fnd_global.user_id
696 where
697 spf.installment_id = F_Installment_id
698 and spf.project_id = F_actual_project_id
699 and (
700 (spf.task_id IS NULL)
701 OR (spf.task_id = F_actual_task_id)
702 OR (spf.task_id = (select t.top_task_id from pa_tasks t where t.task_id = F_actual_task_id))
703 );
704
705 End If;
706
707 IF SQL%NOTFOUND then
708 gms_error_pkg.gms_message('GMS_NO_UPD_PRJ_TASK_INST',
709 'PRJ',
710 F_actual_project_id,
711 'TASK',
712 F_Actual_Task_Id,
713 'INST',
714 F_Installment_Id,
715 X_Exec_Type => 'C',
716 X_Err_Code => X_Err_Code,
717 X_Err_Buff => X_Err_Buff);
718 RAISE_APPLICATION_ERROR(-20209,X_Err_Buff);
719 Else
720 X_Err_Code := 0;
721 End If;
722 End;
723
724 End Loop;
725
726 End MANIP_BILLREV_AMOUNT;
727
728 Procedure DELETE_GMS_BURDEN_INTRSCT(X_Expenditure_Item_Id IN NUMBER,
729 X_Award_Project_Id IN NUMBER,
730 X_Event_Num IN NUMBER,
731 X_Adl_Line_No IN NUMBER,
732 X_Calling_Process IN VARCHAR2,
733 X_Burden_Cost_Code IN VARCHAR2, -- Bug 1193080
734 X_Err_Code IN OUT NOCOPY NUMBER,
735 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
736
737 Begin
738
739 X_Err_Code := 0;
740
741 If X_Calling_Process = 'Invoice' then
742
743 DELETE /*+INDEX(GMS_BURDEN_COMPONENTS GMS_BURDEN_COMPONENTS_U1) */
744 from
745 GMS_BURDEN_COMPONENTS
746 where
747 expenditure_item_id = X_Expenditure_Item_Id
748 and award_project_Id = X_Award_Project_Id
749 and event_num = X_Event_Num
750 and adl_line_num = X_Adl_Line_No
751 and burden_cost_code = X_Burden_Cost_Code -- Bug 1193080
752 and event_type = 'INVOICE';
753
754 If SQL%ROWCOUNT = 0 then
755
756 gms_error_pkg.gms_message('GMS_NO_DEL_PRJ_EVT_EXP_ADL',
757 'PRJ',
758 X_Award_Project_Id,
759 'EVT',
760 X_Event_Num,
761 'EXP',
762 X_Expenditure_Item_Id,
763 'ADL',
764 X_Adl_Line_No,
765 X_Exec_Type => 'C',
766 X_Err_Code => X_Err_Code,
767 X_Err_Buff => X_Err_Buff);
768 RAISE_APPLICATION_ERROR(-20210,X_Err_Buff);
769 Else
770 X_Err_Code := 0;
771 End If;
772
773 Elsif X_Calling_Process = 'Revenue' then
774 DELETE /*+INDEX(GMS_BURDEN_COMPONENTS GMS_BURDEN_COMPONENTS_U1) */
775 from
776 GMS_BURDEN_COMPONENTS
777 where
778 expenditure_item_id = X_Expenditure_Item_Id
779 and award_project_Id = X_Award_Project_Id
780 and event_num = X_Event_Num
781 and adl_line_num = X_Adl_Line_No
782 and burden_cost_code = X_Burden_Cost_Code -- Bug 1193080
783 and event_type = 'REVENUE';
784
785 If SQL%ROWCOUNT = 0 then
786 gms_error_pkg.gms_message('GMS_NO_DEL_PRJ_EVT_EXP_ADL',
787 'PRJ',
788 X_Award_Project_Id,
789 'EVT',
790 X_Event_Num,
791 'EXP',
792 X_Expenditure_Item_Id,
793 'ADL',
794 X_Adl_Line_No,
795 X_Exec_Type => 'C',
796 X_Err_Code => X_Err_Code,
797 X_Err_Buff => X_Err_Buff);
798 RAISE_APPLICATION_ERROR(-20211,X_Err_Buff);
799 Else
800 X_Err_Code := 0;
801 End If;
802
803 End If;
804
805 End DELETE_GMS_BURDEN_INTRSCT;
806
807 Procedure DELETE_GMS_INTERSECT(X_Expenditure_Item_Id IN NUMBER,
808 X_Award_Project_Id IN NUMBER,
809 X_Event_Num IN NUMBER,
810 X_Adl_Line_No IN NUMBER,
811 X_Calling_Process IN VARCHAR2,
812 X_Err_Code IN OUT NOCOPY NUMBER,
813 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
814 Begin
815
816 X_Err_Code := 0;
817
818 If X_Calling_Process = 'Invoice' then
819 DELETE /*+INDEX(GMS_EVENT_INTERSECT GMS_EVENT_INTERSECT_U1) */
820 from
821 GMS_EVENT_INTERSECT
822 where
823 expenditure_item_id = X_Expenditure_Item_Id
824 and award_project_Id = X_Award_Project_Id
825 and event_num = X_Event_Num
826 and adl_line_num = X_Adl_Line_No
827 and event_type = 'INVOICE';
828
829 If SQL%ROWCOUNT = 0 then
830
834 'EVT',
831 gms_error_pkg.gms_message('GMS_NO_DEL_PRJ_EVT_EXP_ADL',
832 'PRJ',
833 X_Award_Project_Id,
835 X_Event_Num,
836 'EXP',
837 X_Expenditure_Item_Id,
838 'ADL',
839 X_Adl_Line_No,
840 X_Exec_Type => 'C',
841 X_Err_Code => X_Err_Code,
842 X_Err_Buff => X_Err_Buff);
843 RAISE_APPLICATION_ERROR(-20212,X_Err_Buff);
844 Else
845 X_Err_Code := 0;
846 End If;
847 Elsif X_Calling_Process = 'Revenue' then
848 DELETE /*+INDEX(GMS_EVENT_INTERSECT GMS_EVENT_INTERSECT_U1) */
849 from
850 GMS_EVENT_INTERSECT
851 where
852 expenditure_item_id = X_Expenditure_Item_Id
853 and award_project_Id = X_Award_Project_Id
854 and event_num = X_Event_Num
855 and adl_line_num = X_Adl_Line_No
856 and event_type = 'REVENUE';
857
858 If SQL%ROWCOUNT = 0 then
859
860 gms_error_pkg.gms_message('GMS_NO_DEL_PRJ_EVT_EXP_ADL',
861 'PRJ',
862 X_Award_Project_Id,
863 'EVT',
864 X_Event_Num,
865 'EXP',
866 X_Expenditure_Item_Id,
867 'ADL',
868 X_Adl_Line_No,
869 X_Exec_Type => 'C',
870 X_Err_Code => X_Err_Code,
871 X_Err_Buff => X_Err_Buff);
872 RAISE_APPLICATION_ERROR(-20213,X_Err_Buff);
873 Else
874 X_Err_Code := 0;
875 End If;
876
877 End If;
878
879 End DELETE_GMS_INTERSECT;
880
881 Procedure UPD_PA_EXP_AND_ADL(X_Award_Project_id IN NUMBER,
882 X_Expenditure_Item_Id IN NUMBER,
883 X_Adl_Line_No IN NUMBER,
884 X_Calling_Process IN VARCHAR2,
885 X_Err_Code IN OUT NOCOPY NUMBER,
886 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
887
888 Begin
889
890 If X_Calling_Process = 'Invoice' then
891
892 UPDATE GMS_AWARD_DISTRIBUTIONS
893 set
894 billed_flag = 'N'
895 ,last_update_date = sysdate
896 ,last_updated_by = fnd_global.user_id
897 ,last_update_login = fnd_global.login_id
898 where expenditure_item_id = X_Expenditure_Item_Id
899 and adl_line_num = X_Adl_Line_No
900 and award_id=
901 (select award_id
902 from gms_awards
903 where award_project_id=X_Award_Project_Id
904 )
905 and document_type='EXP'
906 and adl_status = 'A';
907
908 If SQL%NOTFOUND THEN
909 gms_error_pkg.gms_message('GMS_NO_UPD_PRJ_EXP_ADL',
910 'PRJ',
911 X_Award_Project_Id,
912 'EXP',
913 X_Expenditure_Item_Id,
914 'ADL',
915 X_Adl_Line_No,
916 X_Exec_Type => 'C',
917 X_Err_Code => X_Err_Code,
918 X_Err_Buff => X_Err_Buff);
919 RAISE_APPLICATION_ERROR(-20214,X_Err_Buff);
920 Else
921 X_Err_Code := 0;
922 End If;
923
924 Elsif X_Calling_Process = 'Revenue' then
925
926 -- PA_EXPENDITURE_ITEMS_ALL would not be updated
927 /*
928 UPDATE PA_EXPENDITURE_ITEMS_ALL
929 set
930 revenue_distributed_flag = 'N'
931 ,last_update_date = sysdate
932 ,last_updated_by = fnd_global.user_id
933 ,last_update_login = fnd_global.login_id
934 where
935 expenditure_item_id = X_Expenditure_Item_Id;
936
937 If SQL%NOTFOUND THEN
938 X_Err_Code := 'E';
939 FND_MESSAGE.SET_NAME('GMS','GMS_BILLING_ADJUSTMENTS');
940 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','No Expenditure Line Updated');
941 FND_MESSAGE.SET_TOKEN('PROGRAM_NAME','GMS_BILLING_ADJUSTMENTS : UPD_GET_PA_EXP_INFO');
942 X_Err_Buff := FND_MESSAGE.GET;
943 pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.UPD_GET_PA_EXP_INFO'
944 ,x_message => X_Err_Buff
945 ,x_error_message => X_pa_Err_Msg
946 ,x_status => X_pa_Status);
947 RAISE_APPLICATION_ERROR(-20008,X_Err_Buff);
948 RETURN;
949 Else
950 X_Err_Code := 'S';
951 End If;
952 */
953 UPDATE GMS_AWARD_DISTRIBUTIONS
954 set
955 revenue_distributed_flag = 'N'
956 ,last_update_date = sysdate
957 ,last_updated_by = fnd_global.user_id
958 ,last_update_login = fnd_global.login_id
959 where expenditure_item_id = X_Expenditure_Item_Id
960 and adl_line_num = X_Adl_Line_No
961 and award_id=
962 (select award_id
963 from gms_awards
964 where award_project_id=X_Award_Project_Id
965 )
969 If SQL%NOTFOUND THEN
966 and document_type='EXP'
967 and adl_status = 'A';
968
970 gms_error_pkg.gms_message('GMS_NO_UPD_PRJ_EXP_ADL',
971 'PRJ',
972 X_Award_Project_Id,
973 'EXP',
974 X_Expenditure_Item_Id,
975 'ADL',
976 X_Adl_Line_No,
977 X_Exec_Type => 'C',
978 X_Err_Code => X_Err_Code,
979 X_Err_Buff => X_Err_Buff);
980 RAISE_APPLICATION_ERROR(-20215,X_Err_Buff);
981 Else
982 X_Err_Code := 0;
983 End If;
984
985 End If;
986
987 End UPD_PA_EXP_AND_ADL;
988
989
990 Procedure GET_EVENT_INFO(X_Award_Project_Id IN NUMBER,
991 X_Event_Num IN NUMBER,
992 X_Event_Type OUT NOCOPY VARCHAR2,
993 X_Event_Type_Class OUT NOCOPY VARCHAR2,
994 X_Burden_Evt_Flag OUT NOCOPY VARCHAR2,
995 X_Err_Code IN OUT NOCOPY NUMBER,
996 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
997
998 X_Burden_Cost_Code VARCHAR2(30);
999
1000 Begin
1001
1002 Select distinct
1003 nvl(a.burden_cost_code,'NULL'),
1004 a.event_type,
1005 b.event_type_classification
1006 into
1007 X_Burden_Cost_Code,
1008 X_Event_Type,
1009 X_Event_Type_Class
1010 from
1011 gms_events_v a,
1012 pa_event_types b
1013 where
1014 a.project_id = X_Award_Project_Id
1015 and a.event_num = X_Event_Num
1016 and a.event_type = b.event_type;
1017
1018 X_Err_Code := 0;
1019
1020 If X_Burden_Cost_Code = 'NULL' then
1021 X_Burden_Evt_Flag := 'N' ;
1022 Else
1023 X_Burden_Evt_Flag := 'Y';
1024 End If;
1025
1026 EXCEPTION
1027 WHEN NO_DATA_FOUND THEN
1028 gms_error_pkg.gms_message('GMS_NO_DATA_PRJ_EVT',
1029 'PRJ',
1030 X_Award_Project_Id,
1031 'EVT',
1032 X_Event_Num,
1033 X_Exec_Type => 'C',
1034 X_Err_Code => X_Err_Code,
1035 X_Err_Buff => X_Err_Buff);
1036 RAISE_APPLICATION_ERROR(-20216,X_Err_Buff);
1037 WHEN OTHERS THEN
1038 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
1039 'SQLCODE',
1040 SQLCODE,
1041 'SQLERRM',
1042 SQLERRM,
1043 X_Exec_Type => 'C',
1044 X_Err_Code => X_Err_Code,
1045 X_Err_Buff => X_Err_Buff);
1046 RAISE_APPLICATION_ERROR(-20217,X_Err_Buff);
1047 End GET_EVENT_INFO;
1048
1049
1050 /* Procedure GET_EVENT_PROJ_TASK to Get Project and Task for the Event */
1051
1052 Procedure GET_EVENT_PROJ_TASK(X_Event_Num IN NUMBER,
1053 X_Award_Project_Id IN NUMBER,
1054 X_Expenditure_Item_Id IN NUMBER DEFAULT NULL,
1055 X_Actual_Project_Id OUT NOCOPY NUMBER,
1056 X_Actual_Task_Id OUT NOCOPY NUMBER,
1057 X_Expenditure_Org_Id OUT NOCOPY NUMBER,
1058 X_Revenue_Accumulated OUT NOCOPY VARCHAR2,
1059 X_Creation_Date OUT NOCOPY DATE,
1060 X_Err_Code IN OUT NOCOPY NUMBER,
1061 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
1062
1063 Begin
1064 Select distinct
1065 Actual_Project_Id,
1066 Actual_Task_Id,
1067 Expenditure_Org_Id,
1068 Revenue_Accumulated
1069 --,Creation_Date
1070 into
1071 X_Actual_Project_Id,
1072 X_Actual_Task_Id,
1073 X_Expenditure_Org_Id,
1074 X_Revenue_Accumulated
1075 --,X_Creation_Date
1076 from
1077 gms_events_v
1078 where project_id = X_Award_Project_Id
1079 and event_num = X_Event_Num;
1080
1081 If X_Expenditure_Item_Id is not null then
1082 -- Cost Based
1083 Select trunc(expenditure_item_date)
1084 into X_creation_date
1085 from pa_expenditure_items_all
1086 where Expenditure_Item_Id = X_Expenditure_Item_Id;
1087 End If;
1088
1089 X_Err_Code := 0;
1090
1091 EXCEPTION
1092 WHEN NO_DATA_FOUND THEN
1093 gms_error_pkg.gms_message('GMS_NO_DATA_PRJ_EVT',
1094 'PRJ',
1095 X_Award_Project_Id,
1096 'EVT',
1097 X_Event_Num,
1098 X_Exec_Type => 'C',
1099 X_Err_Code => X_Err_Code,
1100 X_Err_Buff => X_Err_Buff);
1101 RAISE_APPLICATION_ERROR(-20218,X_Err_Buff);
1102 WHEN OTHERS THEN
1103 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
1104 'SQLCODE',
1105 SQLCODE,
1106 'SQLERRM',
1107 SQLERRM,
1108 X_Exec_Type => 'C',
1109 X_Err_Code => X_Err_Code,
1110 X_Err_Buff => X_Err_Buff);
1111 RAISE_APPLICATION_ERROR(-20219,X_Err_Buff);
1112 End GET_EVENT_PROJ_TASK;
1113
1114
1115 Procedure GET_INVOICE_CREDIT_INFO(X_Draft_Invoice_Num IN NUMBER,
1116 X_Award_Project_Id IN NUMBER,
1117 X_Write_Off_Flag OUT NOCOPY VARCHAR2,
1121 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
1118 X_Concession_Invoice_Flag OUT NOCOPY VARCHAR2,
1119 X_Draft_Invoice_Num_Credited OUT NOCOPY VARCHAR2,
1120 X_Err_Code IN OUT NOCOPY NUMBER,
1122
1123 Begin
1124 Select
1125 NVL(write_off_flag,'N'),
1126 NVL(concession_flag,'N'),
1127 draft_invoice_num_credited
1128 into
1129 X_Write_Off_Flag,
1130 X_Concession_Invoice_Flag,
1131 X_Draft_Invoice_Num_Credited
1132 from
1133 PA_DRAFT_INVOICES
1134 where project_id = X_Award_Project_Id
1135 and draft_invoice_num = X_Draft_Invoice_Num;
1136 X_Err_Code := 0;
1137 EXCEPTION
1138 WHEN NO_DATA_FOUND THEN
1139 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
1140 'SQLCODE',
1141 SQLCODE,
1142 'SQLERRM',
1143 SQLERRM,
1144 X_Exec_Type => 'C',
1145 X_Err_Code => X_Err_Code,
1146 X_Err_Buff => X_Err_Buff);
1147 RAISE_APPLICATION_ERROR(-20220,X_Err_Buff);
1148 End GET_INVOICE_CREDIT_INFO;
1149
1150
1151 Procedure DO_INV_ITEM_PROCESSING(St_Award_Project_Id IN NUMBER,
1152 St_Draft_Invoice_Num IN NUMBER,
1153 X_Adj_Action IN VARCHAR2,
1154 X_Adjust_Amount IN NUMBER DEFAULT NULL,
1155 X_Calling_Process IN VARCHAR2,
1156 X_Err_Code IN OUT NOCOPY NUMBER,
1157 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
1158
1159 CURSOR GET_INV_ITEMS IS
1160 Select
1161 project_id,
1162 line_num,
1163 event_num,
1164 amount
1165 from
1166 pa_draft_invoice_items
1167 where
1168 draft_invoice_num = St_Draft_Invoice_Num and
1169 project_id = St_Award_Project_Id;
1170
1171 F_Award_Project_Id NUMBER(15);
1172 F_Line_Num NUMBER(15);
1173 F_Event_Num NUMBER(15);
1174 F_Amount NUMBER(22,5);
1175
1176 /* This is the cursor to identify the rows in the intersect table related to the Invoice items */
1177 CURSOR IDENT_INV_INTRSCT_ITEMS is
1178 Select
1179 expenditure_item_id,
1180 adl_line_num,
1181 request_id -- for bug 4594090
1182 from
1183 gms_event_intersect
1184 where
1185 award_project_id = F_Award_Project_Id and
1186 event_num = F_Event_Num and
1187 event_type = 'INVOICE';
1188
1189 F_Expenditure_Item_Id NUMBER(15);
1190 F_Adl_Line_No NUMBER(15);
1191
1192 /* This is the cursor to identify the rows in the Burden Component table related to the Burden
1193 Invoice Items */
1194 CURSOR BURDEN_INV_INTRSCT_ITEMS is
1195 Select
1196 Expenditure_Item_Id,
1197 adl_line_num,
1198 Amount,
1199 Actual_Project_Id,
1200 Actual_Task_Id,
1201 Burden_Exp_Type,
1202 Burden_Cost_Code,
1203 Expenditure_Org_Id,
1204 request_id -- Added for bug 4594090
1205 from
1206 GMS_BURDEN_COMPONENTS
1207 where
1208 award_project_id = F_Award_Project_Id and
1209 event_num = F_Event_Num and
1210 event_type = 'INVOICE';
1211
1212 F_Burd_Expenditure_Item_Id NUMBER(15);
1213 F_Burd_Adl_Line_No NUMBER(15);
1214 F_Burd_Intrsct_Amount NUMBER(22,5);
1215 F_Burd_Actual_Project_Id NUMBER(15);
1216 F_Burd_Actual_Task_Id NUMBER(15);
1217 F_Burd_Exp_Type VARCHAR2(30);
1218 F_Burd_Cost_Code VARCHAR2(30);
1219 F_Burd_Expenditure_Org_Id NUMBER(15);
1220
1221 F_Actual_Project_Id_1 NUMBER(15);
1222 F_Actual_Task_Id_1 NUMBER(15);
1223 F_Line_Num_1 NUMBER(15);
1224 F_Installment_Id_1 NUMBER(15);
1225 F_Write_Off_Amount_1 NUMBER(22,5);
1226
1227 F_Actual_Project_Id_2 NUMBER(15);
1228 F_Actual_Task_Id_2 NUMBER(15);
1229 F_Line_Num_2 NUMBER(15);
1230 F_Installment_Id_2 NUMBER(15);
1231 F_Write_Off_Amount_2 NUMBER(22,5);
1232
1233 X_Event_Type VARCHAR2(30);
1234 X_Event_Type_Class VARCHAR2(30);
1235 X_Installment_Id NUMBER(15);
1236 X_Actual_Project_Id NUMBER(15);
1237 X_Actual_Task_Id NUMBER(15);
1238 X_Write_Off_Flag VARCHAR2(1);
1239 X_Draft_Invoice_Num_Credited NUMBER(15);
1240 X_Write_Off_Amount NUMBER(22,5);
1241
1242 X_Burden_Evt_Flag VARCHAR2(1);
1243
1244 X_Concession_flag VARCHAR2(1);
1245
1246 F_request_id gms_event_attribute.request_id%TYPE; -- Added for bug 4594090
1247
1248 Begin
1249
1250 If X_Adj_Action in ('CANCEL','DELETE') then
1251
1252 /* Find Out NOCOPY if the Invoice that's being processed is a Regular Invoice
1253 or a Write Off on some other Invoice */
1254
1255 GET_INVOICE_CREDIT_INFO(St_Draft_Invoice_Num,
1256 St_Award_Project_Id,
1257 X_Write_Off_Flag,
1258 X_Concession_Flag,
1259 X_Draft_Invoice_Num_Credited,
1260 X_Err_Code,
1261 X_Err_Buff);
1262
1263 If X_Err_Code <> 0 then
1264 RAISE FND_API.G_EXC_ERROR;
1265 End If;
1266
1267 End If;
1268
1269 If (X_Adj_Action = 'CANCEL' OR X_Adj_Action = 'DELETE') then
1270 If ((X_Write_Off_Flag = 'Y') OR (X_Concession_Flag = 'Y')) then
1271 Begin
1272
1276
1273 /* --------------------------------------------------------------- */
1274 -- 11.5 Changes, re writing of Write_off deletion/cancellation Processing
1275 /* --------------------------------------------------------------- */
1277 WRITE_OFF_DELETION(St_Award_Project_Id,
1278 St_Draft_Invoice_Num,
1279 X_Err_Code,
1280 X_Err_Buff);
1281
1282 If X_Err_Code <> 0 then
1283 RAISE FND_API.G_EXC_ERROR;
1284 End If;
1285
1286
1287 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '5 - CANINV'
1288 ,x_message => 'Inside WRITE-OFF Flag = Y '
1289 ,x_error_message => X_Err_Msg
1290 ,x_status => X_Status); */
1291
1292 End;
1293
1294 Else -- (X_Write_Off_Flag <> 'Y'=> Regular Invoice)
1295
1296 open GET_INV_ITEMS;
1297 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6 - CANINV'
1298 ,x_message =>'In WRITE_OFF_FLAG = N'
1299 ,x_error_message => X_Err_Msg
1300 ,x_status => X_Status); */
1301
1302 LOOP -- Loop for PA_DRAFT_INVOICE_ITEMS
1303 FETCH
1304 GET_INV_ITEMS
1305 into
1306 F_Award_Project_Id,
1307 F_Line_Num,
1308 F_Event_Num,
1309 F_Amount;
1310 EXIT WHEN GET_INV_ITEMS%NOTFOUND;
1311
1312 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.1 - CANINV'
1313 ,x_message =>'Before GET_EVENT_INFO'
1314 ,x_error_message => X_Err_Msg
1315 ,x_status => X_Status); */
1316
1317 GET_EVENT_INFO(F_Award_Project_Id,
1318 F_Event_Num,
1319 X_Event_Type,
1320 X_Event_Type_Class,
1321 X_Burden_Evt_Flag,
1322 X_Err_Code,
1323 X_Err_Buff);
1324
1325 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2 - CANINV'
1326 ,x_message =>'After GET_EVENT_INFO'||'-'||St_Err_Code
1327 ,x_error_message => X_Err_Msg
1328 ,x_status => X_Status); */
1329
1330 If X_Err_Code <> 0 then
1331 RAISE FND_API.G_EXC_ERROR;
1332 End If;
1333
1334 If (X_Event_Type_Class = 'AUTOMATIC' and X_Event_Type = 'AWARD_BILLING') then
1335
1336 --------------------------------------------------------------------
1337 If (X_Burden_Evt_Flag = 'N') then
1338
1339 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.1 - CANINV'
1340 ,x_message =>'Inside Burden_Evt_Flag = N'
1341 ,x_error_message => X_Err_Msg
1342 ,x_status => X_Status); */
1343
1344 Begin -- Raw Component Processing
1345 open IDENT_INV_INTRSCT_ITEMS;
1346 LOOP
1347 FETCH
1348 IDENT_INV_INTRSCT_ITEMS
1349 into
1350 F_Expenditure_Item_Id,
1351 F_Adl_Line_No,
1352 F_request_id; -- 4594090
1353
1354 EXIT WHEN IDENT_INV_INTRSCT_ITEMS%NOTFOUND;
1355
1356 /* Updating PA_EXPENDITURE_ITEMS_ALL and GMS_AWARD_DISTRIBUTIONS, setting the Revenue Accrued Flag to 'N' */
1357 UPD_PA_EXP_AND_ADL(F_Award_Project_id,
1358 F_Expenditure_Item_Id,
1359 F_Adl_Line_No,
1360 X_Calling_Process,
1361 X_Err_Code,
1362 X_Err_Buff);
1363
1364 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.2 - CANINV'
1365 ,x_message =>'After UPD_GET_PA_EXP_INFO '||'-'||St_Err_Code
1366 ,x_error_message => X_Err_Msg
1367 ,x_status => X_Status); */
1368
1369 If X_Err_Code <> 0 then
1370 RAISE FND_API.G_EXC_ERROR;
1371 End If;
1372
1373 /* Deleting Items From GMS_EVENT_INTERSECT Table */
1374 DELETE_GMS_INTERSECT(F_Expenditure_Item_Id,
1375 F_Award_Project_Id,
1376 F_Event_Num,
1377 F_Adl_Line_No,
1378 X_Calling_Process,
1379 X_Err_Code,
1380 X_Err_Buff);
1381
1382 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.3 - CANINV'
1383 ,x_message =>'After DELETE_GMS_INTERSECT '||'-'||St_Err_Code
1384 ,x_error_message => X_Err_Msg
1385 ,x_status => X_Status); */
1386
1387 If X_Err_Code <> 0 then
1388 RAISE FND_API.G_EXC_ERROR;
1389 End If;
1390
1391 End LOOP;
1392 close IDENT_INV_INTRSCT_ITEMS;
1393
1397 X_Calling_Process,
1394 /* Updating GMS_SUMMARY_PROJECT_FUNDINGS Revenue Accrued Amount */
1395 MANIP_BILLREV_AMOUNT(F_Award_Project_id,
1396 F_Event_Num,
1398 X_Err_Code,
1399 X_Err_Buff);
1400
1401 /*pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.4 - CANINV'
1402 ,x_message =>'After MANIP_BILLREV_AMOUNT '||'-'||St_Err_Code
1403 ,x_error_message => X_Err_Msg
1404 ,x_status => X_Status); */
1405
1406 If X_Err_Code <> 0 then
1407 RAISE FND_API.G_EXC_ERROR;
1408 End If;
1409
1410 /* Delete entries from GMS_EVENT_ATTRIBUTE */
1411 /* Bug 2979125: added parameter calling_process */
1412 DELETE_GMS_EVENT_ATTRIBUTE(F_Award_Project_id,
1413 F_Event_Num,
1414 X_Calling_Process,
1415 X_Err_Code,
1416 X_Err_Buff);
1417
1418 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.5 - CANINV'
1419 ,x_message =>'After DELETE_GMS_EVENT_ATTRIBUTE '||'-'||St_Err_Code
1420 ,x_error_message => X_Err_Msg
1421 ,x_status => X_Status); */
1422
1423 If X_Err_Code <> 0 then
1424 RAISE FND_API.G_EXC_ERROR;
1425 End If;
1426
1427 End; -- Raw Component Processing
1428
1429 Elsif (X_Burden_Evt_Flag = 'Y') then -----------------------------------
1430
1431 Begin -- Burden Component Processing
1432
1433 open BURDEN_INV_INTRSCT_ITEMS;
1434
1435 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.1 - CANINV'
1436 ,x_message =>'Inside X_Burden_Evt_Flag = Y '||'-'||St_Err_Code
1437 ,x_error_message => X_Err_Msg
1438 ,x_status => X_Status); */
1439 LOOP
1440 FETCH BURDEN_INV_INTRSCT_ITEMS into
1441 F_Burd_Expenditure_Item_Id ,
1442 F_Burd_Adl_Line_No ,
1443 F_Burd_Intrsct_Amount ,
1444 F_Burd_Actual_Project_Id ,
1445 F_Burd_Actual_Task_Id ,
1446 F_Burd_Exp_Type ,
1447 F_Burd_Cost_Code ,
1448 F_Burd_Expenditure_Org_Id ,
1449 F_request_id; -- 4594090
1450
1451 EXIT WHEN BURDEN_INV_INTRSCT_ITEMS%NOTFOUND;
1452
1453 -- Bug 2477972, start
1454 /* On GMS_AWARD_DISTRIBUTIONS setting the Invoice Accrued Flag to 'N' */
1455 UPD_PA_EXP_AND_ADL(F_Award_Project_id,
1456 F_Burd_Expenditure_Item_Id,
1457 F_Burd_Adl_Line_No,
1458 X_Calling_Process,
1459 X_Err_Code,
1460 X_Err_Buff);
1461 If X_Err_Code <> 0 then
1462 RAISE FND_API.G_EXC_ERROR;
1463 End If;
1464 -- Bug 2477972, end
1465
1466 /* Deleting items from GMS_BURDEN_COMPONENTS table */
1467
1468 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.2 - CANINV'
1469 ,x_message =>'Before DELETE_GMS_BURDEN_INTRSCT'
1470 ,x_error_message => X_Err_Msg
1471 ,x_status => X_Status); */
1472
1473 DELETE_GMS_BURDEN_INTRSCT(F_Burd_Expenditure_Item_Id ,
1474 F_Award_Project_Id,
1475 F_Event_Num,
1476 F_Burd_Adl_Line_No,
1477 X_Calling_Process,
1478 F_Burd_Cost_Code, -- Bug 1193080
1479 X_Err_Code,
1480 X_Err_Buff);
1481
1482 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.3 - CANINV'
1483 ,x_message =>'After DELETE_GMS_BURDEN_INTRSCT'||'-'||St_Err_Code
1484 ,x_error_message => X_Err_Msg
1485 ,x_status => X_Status); */
1486
1487 If X_Err_Code <> 0 then
1488 RAISE FND_API.G_EXC_ERROR;
1489 End If;
1490
1491 END LOOP;
1492 close BURDEN_INV_INTRSCT_ITEMS;
1493
1494 /* Updating GMS_SUMMARY_PROJECT_FUNDINGS Revenue Accrued Amount */
1495 MANIP_BILLREV_AMOUNT(F_Award_Project_id,
1496 F_Event_Num,
1497 X_Calling_Process,
1498 X_Err_Code,
1499 X_Err_Buff);
1500
1501 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.4 - CANINV'
1505
1502 ,x_message =>'After MANIP_BILLREV_AMOUNT '||'-'||St_Err_Code
1503 ,x_error_message => X_Err_Msg
1504 ,x_status => X_Status); */
1506 If X_Err_Code <> 0 then
1507 RAISE FND_API.G_EXC_ERROR;
1508 End If;
1509
1510 /* Delete entries from GMS_EVENT_ATTRIBUTE */
1511 /* Bug 2979125 : added parameter calling_process */
1512 DELETE_GMS_EVENT_ATTRIBUTE(F_Award_Project_id,
1513 F_Event_Num,
1514 X_Calling_Process,
1515 X_Err_Code,
1516 X_Err_Buff);
1517
1518 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.5 - CANINV'
1519 ,x_message =>'After DELETE_GMS_EVENT_ATTRIBUTE '||'-'||St_Err_Code
1520 ,x_error_message => X_Err_Msg
1521 ,x_status => X_Status); */
1522
1523 If X_Err_Code <> 0 then
1524 RAISE FND_API.G_EXC_ERROR;
1525 End If;
1526
1527 End; -- Burden Component Processing
1528
1529 End If ; -- End If for X_Burden_Evt_Flag
1530
1531 --------------------------------------------------------------------------
1532 /* -- Handle net zero events .... (4594090)
1533 HANDLE_NET_ZERO_EVENTS (P_AWARD_PROJECT_ID => F_Award_Project_id,
1534 P_REQUEST_ID => F_request_id,
1535 P_CALLING_PROCESS => 'INVOICE');
1536 Moving this call from here to after the close of GET_INV_ITEMS cursor
1537 for perfomance reasons. Bug 5060427 */
1538 Elsif X_Event_Type_Class = 'MANUAL' then
1539
1540 Begin
1541
1542 /* Updating GMS_SUMMARY_PROJECT_FUNDINGS Revenue Accrued Amount */
1543 MANIP_BILLREV_AMOUNT(F_Award_Project_id,
1544 F_Event_Num,
1545 X_Calling_Process,
1546 X_Err_Code,
1547 X_Err_Buff);
1548
1549 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.4.1 - CANINV'
1550 ,x_message =>'After MANIP_BILLREV_AMOUNT '||'-'||St_Err_Code
1551 ,x_error_message => X_Err_Msg
1552 ,x_status => X_Status); */
1553
1554 If X_Err_Code <> 0 then
1555 RAISE FND_API.G_EXC_ERROR;
1556 End If;
1557 End;
1558
1559 End If; -- End of If for Event_Type_Classification('Manual' or 'Automatic')
1560
1561 End LOOP; -- End Loop for PA_DRAFT_INVOICE_ITEMS
1562
1563 CLOSE GET_INV_ITEMS;
1564
1565 X_Err_Code := 0;
1566
1567 -- Handle net zero events .... (4594090)
1568 -- Moved this call to here from an earlier point for bug 5060427
1569 HANDLE_NET_ZERO_EVENTS (P_AWARD_PROJECT_ID => F_Award_Project_id,
1570 P_REQUEST_ID => F_request_id,
1571 P_CALLING_PROCESS => 'INVOICE');
1572
1573 End If; -- Check FOR WRITE_OFF_FLAG
1574
1575 Elsif (X_Adj_Action in ('WRITE_OFF','CONCESSION')) then
1576
1577 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '2 - WRITEOFFF'
1578 ,x_message => 'Getting INTO GRANTS WRITE OFF Process '
1579 ,x_error_message => X_Err_Msg
1580 ,x_status => X_Status); */
1581
1582 Begin
1583
1584 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '2.25 - WRITEOFFF'
1585 ,x_message => to_char(St_Award_Project_Id)||'- '||to_char(St_Draft_Invoice_Num)
1586 ,x_error_message => X_Err_Msg
1587 ,x_status => X_Status); */
1588
1589 /* --------------------------------------------------------------- */
1590 -- 11.5 Changes, re writing of Write_off Processing
1591 /* --------------------------------------------------------------- */
1592
1593 WRITE_OFF_CREATION(St_Award_Project_Id,
1594 X_Adj_Action,
1595 X_Err_Code,
1596 X_Err_Buff);
1597
1598 If X_Err_Code <> 0 then
1599 RAISE FND_API.G_EXC_ERROR;
1600 End If;
1601
1602 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '2.50 - AFTER WRITE OFF PROCESSING'
1603 ,x_message => 'Inside WRITE-OFF Flag = Y '
1604 ,x_error_message => X_Err_Msg
1605 ,x_status => X_Status); */
1606
1607 End;
1608 End If; -- End of X_Adj_Action IF
1609
1610 X_Err_Code := 0;
1611
1612 EXCEPTION
1613 WHEN FND_API.G_EXC_ERROR THEN
1614 ROLLBACK;
1615 RETURN;
1616
1617 End DO_INV_ITEM_PROCESSING;
1621 St_Draft_Revenue_Num IN NUMBER,
1618
1619
1620 Procedure DO_REV_ITEM_PROCESSING(St_Award_Project_Id IN NUMBER,
1622 X_Calling_Process IN VARCHAR2,
1623 X_Err_Code IN OUT NOCOPY NUMBER,
1624 X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
1625 CURSOR GET_REV_ITEMS_RDL IS
1626 Select
1627 ri.project_id,
1628 ri.line_num,
1629 rdl.event_num,
1630 ri.amount
1631 from
1632 pa_draft_revenue_items ri,
1633 pa_cust_event_rdl_all rdl
1634 where
1635 ri.draft_revenue_num = St_Draft_Revenue_Num and
1636 ri.project_id = St_Award_Project_Id and
1637 rdl.draft_revenue_num = ri.draft_revenue_num and
1638 rdl.project_id = ri.project_id and
1639 rdl.draft_revenue_item_line_num = ri.line_num;
1640
1641 F_Award_Project_Id NUMBER(15);
1642 F_Line_Num NUMBER(15);
1643 F_Event_Num NUMBER(15);
1644 F_Amount NUMBER(22,5);
1645
1646 X_Event_Type VARCHAR2(30);
1647 X_Event_Type_Class VARCHAR2(30);
1648 X_Installment_Id NUMBER(15);
1649
1650
1651 /* This is the cursor to identify the rows in the intersect table related to the Revenue Items */
1652 CURSOR IDENT_REV_INTRSCT_ITEMS is
1653 Select
1654 expenditure_item_id,
1655 adl_line_num,
1656 amount,
1657 revenue_accumulated,
1658 request_id -- 4594090
1659 from
1660 gms_event_intersect
1661 where
1662 award_project_id = F_Award_Project_Id and
1663 event_num = F_Event_Num and
1664 event_type = 'REVENUE';
1665
1666 F_Expenditure_Item_Id NUMBER(15);
1667 F_Adl_Line_No NUMBER(15);
1668 F_Raw_Revenue_Amount NUMBER(22,5);
1669 F_Rev_Accumulated VARCHAR2(1);
1670
1671 /* This is the cursor to identify the rows in the Burden Component table related to the Burden
1672 Invoice Items */
1673 CURSOR BURDEN_REV_INTRSCT_ITEMS is
1674 Select
1675 Expenditure_Item_Id,
1676 Adl_Line_Num,
1677 Amount,
1678 Actual_Project_Id,
1679 Actual_Task_Id,
1680 Burden_Exp_Type,
1681 Burden_Cost_Code,
1682 Expenditure_Org_Id,
1683 Creation_Date,
1684 Revenue_Accumulated,
1685 request_id -- 4594090
1686 from
1687 GMS_BURDEN_COMPONENTS
1688 where
1689 award_project_id = F_Award_Project_Id and
1690 event_num = F_Event_Num and
1691 event_type = 'REVENUE';
1692
1693 F_Rev_Burd_Expend_Item_Id NUMBER(15);
1694 F_Rev_Adl_Line_No NUMBER(15);
1695 F_Rev_Burd_Intrsct_Amt NUMBER(22,5);
1696 F_Rev_Actual_Project_Id NUMBER(15);
1697 F_Rev_Actual_Task_Id NUMBER(15);
1698 F_Rev_Burden_Exp_Type VARCHAR2(30);
1699 F_Rev_Burden_Cost_Code VARCHAR2(30);
1700 F_Rev_Burd_Exp_Org_Id NUMBER(15);
1701 F_Rev_Creation_Date DATE;
1702 F_Rev_Revenue_Accumulated VARCHAR2(1);
1703
1704
1705 X_Actual_Project_Id NUMBER(15);
1706 X_Actual_Task_Id NUMBER(15);
1707 X_Expenditure_Org_Id NUMBER(15);
1708 X_Revenue_Accumulated VARCHAR2(1);
1709 X_Creation_Date DATE;
1710
1711 X_Burden_Evt_Flag VARCHAR2(1);
1712
1713 F_request_id gms_event_attribute.request_id%TYPE; -- 4594090
1714
1715 Begin
1716 OPEN GET_REV_ITEMS_RDL;
1717 LOOP
1718 FETCH GET_REV_ITEMS_RDL into
1719 F_Award_Project_Id,
1720 F_Line_Num,
1721 F_Event_Num,
1722 F_Amount;
1723 EXIT WHEN GET_REV_ITEMS_RDL%NOTFOUND;
1724
1725 GET_EVENT_INFO(F_Award_Project_Id,
1726 F_Event_Num,
1727 X_Event_Type,
1728 X_Event_Type_Class,
1729 X_Burden_Evt_Flag,
1730 X_Err_Code,
1731 X_Err_Buff);
1732
1733 If X_Err_Code <> 0 then
1734 RAISE FND_API.G_EXC_ERROR;
1735 End If;
1736
1737 If (X_Event_Type_Class = 'AUTOMATIC' and X_Event_Type = 'AWARD_BILLING' ) then
1738
1739 --------------------------------------------------------------------------------
1740 If (X_Burden_Evt_Flag = 'N') then
1741 Begin -- Raw Component processing
1742
1743 OPEN IDENT_REV_INTRSCT_ITEMS;
1744 LOOP
1745 FETCH
1746 IDENT_REV_INTRSCT_ITEMS
1747 into
1748 F_Expenditure_Item_Id,
1749 F_Adl_Line_No,
1750 F_Raw_Revenue_Amount,
1751 F_Rev_Accumulated,
1752 F_request_id; -- 4594090
1753 EXIT WHEN IDENT_REV_INTRSCT_ITEMS%NOTFOUND;
1754
1755 /* Updating PA_EXPENDITURE_ITEMS_ALL and GMS_AWARD_DISTRIBUTIONS, setting the Revenue Accrued Flag to 'N' */
1756 UPD_PA_EXP_AND_ADL(F_Award_Project_id,
1757 F_Expenditure_Item_Id,
1758 F_Adl_Line_No,
1759 X_Calling_Process,
1760 X_Err_Code,
1761 X_Err_Buff);
1762
1763 If X_Err_Code <> 0 then
1764 RAISE FND_API.G_EXC_ERROR;
1765 End If;
1766
1767 /* Deleting Items From GMS_EVENT_INTERSECT Table */
1768 DELETE_GMS_INTERSECT(F_Expenditure_Item_Id,
1772 X_Calling_Process,
1769 F_Award_Project_Id,
1770 F_Event_Num,
1771 F_Adl_Line_No,
1773 X_Err_Code,
1774 X_Err_Buff);
1775
1776 If X_Err_Code <> 0 then
1777 RAISE FND_API.G_EXC_ERROR;
1778 End If;
1779
1780 /* Get Event Information */
1781 GET_EVENT_PROJ_TASK(F_Event_Num,
1782 F_Award_Project_id,
1783 F_Expenditure_Item_Id,
1784 X_Actual_Project_Id,
1785 X_Actual_Task_Id,
1786 X_Expenditure_Org_Id,
1787 X_Revenue_Accumulated,
1788 X_Creation_Date,
1789 X_Err_Code,
1790 X_Err_Buff);
1791
1792 If X_Err_Code <> 0 then
1793 RAISE FND_API.G_EXC_ERROR;
1794 End If;
1795
1796 If F_Rev_Accumulated ='Y' then
1797
1798 /* Create negative entry in gms_billing_cancellations
1799 for ASI to backout revenue accumulated */
1800 INSERT_BILL_CANCEL(F_Award_Project_id,
1801 F_Event_Num,
1802 F_Expenditure_Item_Id,
1803 F_Adl_Line_No,
1804 -1 * F_Raw_Revenue_Amount,
1805 X_Calling_Process,
1806 NULL, -- burden_exp_type
1807 NULL, -- burden_cost_code
1808 X_Creation_Date,
1809 X_Actual_Project_Id,
1810 X_Actual_Task_Id,
1811 X_Expenditure_Org_Id,
1812 sysdate, -- deletion_date
1813 NULL, -- rlmi
1814 X_Err_Code,
1815 X_Err_Buff);
1816
1817 If X_Err_Code <> 0 then
1818 RAISE FND_API.G_EXC_ERROR;
1819 End If;
1820
1821 End if;
1822
1823 End LOOP;
1824 CLOSE IDENT_REV_INTRSCT_ITEMS;
1825
1826 X_Err_Code := 0;
1827
1828 /* Updating GMS_SUMMARY_PROJECT_FUNDINGS Revenue Accrued Amount */
1829 MANIP_BILLREV_AMOUNT(F_Award_Project_id,
1830 F_Event_Num,
1831 X_Calling_Process,
1832 X_Err_Code,
1833 X_Err_Buff);
1834
1835 If X_Err_Code <> 0 then
1836 RAISE FND_API.G_EXC_ERROR;
1837 End If;
1838
1839 /* Delete entries from GMS_EVENT_ATTRIBUTE */
1840 /* Bug 2979125 : added parameter calling_process */
1841 DELETE_GMS_EVENT_ATTRIBUTE(F_Award_Project_id,
1842 F_Event_Num,
1843 X_Calling_Process,
1844 X_Err_Code,
1845 X_Err_Buff);
1846
1847 If X_Err_Code <> 0 then
1848 RAISE FND_API.G_EXC_ERROR;
1849 End If;
1850
1851 End; -- Raw Component Processing
1852
1853 Elsif (X_Burden_Evt_Flag = 'Y') then----------------------------------------------------
1854 Begin -- Burden Component Processing
1855
1856 open BURDEN_REV_INTRSCT_ITEMS;
1857 LOOP
1858 FETCH BURDEN_REV_INTRSCT_ITEMS into
1859 F_Rev_Burd_Expend_Item_Id,
1860 F_Rev_Adl_Line_No,
1861 F_Rev_Burd_Intrsct_Amt,
1862 F_Rev_Actual_Project_Id,
1863 F_Rev_Actual_Task_Id,
1864 F_Rev_Burden_Exp_Type,
1865 F_Rev_Burden_Cost_Code,
1866 F_Rev_Burd_Exp_Org_Id,
1867 F_Rev_Creation_Date,
1868 F_Rev_Revenue_Accumulated,
1869 F_request_id; -- 4594090
1870 EXIT WHEN BURDEN_REV_INTRSCT_ITEMS%NOTFOUND;
1871
1872 -- Bug 2477972, Start
1873 /* On GMS_AWARD_DISTRIBUTIONS setting the Revenue Accrued Flag to 'N' */
1874 UPD_PA_EXP_AND_ADL(F_Award_Project_id,
1875 F_Rev_Burd_Expend_Item_Id,
1876 F_Rev_Adl_Line_No,
1877 X_Calling_Process,
1878 X_Err_Code,
1879 X_Err_Buff);
1880
1881 If X_Err_Code <> 0 then
1882 RAISE FND_API.G_EXC_ERROR;
1883 End If;
1884
1885 -- Bug 2477972, end
1886
1887 /* Deleting items from GMS_BURDEN_COMPONENTS table */
1888
1889 DELETE_GMS_BURDEN_INTRSCT(F_Rev_Burd_Expend_Item_Id ,
1890 F_Award_Project_Id,
1894 F_Rev_Burden_Cost_Code, -- Bug 1193080
1891 F_Event_Num,
1892 F_Rev_Adl_Line_No,
1893 X_Calling_Process,
1895 X_Err_Code,
1896 X_Err_Buff);
1897
1898 If X_Err_Code <> 0 then
1899 RAISE FND_API.G_EXC_ERROR;
1900 End If;
1901
1902 If F_Rev_Revenue_Accumulated ='Y' then
1903
1904 /* Create negative entry in gms_billing_cancellations
1905 for ASI to backout revenue accumulated */
1906 INSERT_BILL_CANCEL(F_Award_Project_id,
1907 F_Event_Num,
1908 F_Rev_Burd_Expend_Item_Id,
1909 F_Rev_Adl_Line_No,
1910 -1 * F_Rev_Burd_Intrsct_Amt,
1911 X_Calling_Process,
1912 F_Rev_Burden_Exp_Type,
1913 F_Rev_Burden_Cost_Code,
1914 F_Rev_Creation_Date,
1915 F_Rev_Actual_Project_Id,
1916 F_Rev_Actual_Task_Id,
1917 F_Rev_Burd_Exp_Org_Id,
1918 sysdate, -- deletion_date
1919 NULL, -- rlmi
1920 X_Err_Code,
1921 X_Err_Buff);
1922
1923 If X_Err_Code <> 0 then
1924 RAISE FND_API.G_EXC_ERROR;
1925 End If;
1926
1927 End if;
1928
1929 END LOOP;
1930
1931 close BURDEN_REV_INTRSCT_ITEMS;
1932
1933 /* Updating GMS_SUMMARY_PROJECT_FUNDINGS Revenue Accrued Amount */
1934 MANIP_BILLREV_AMOUNT(F_Award_Project_id,
1935 F_Event_Num,
1936 X_Calling_Process,
1937 X_Err_Code,
1938 X_Err_Buff);
1939
1940 If X_Err_Code <> 0 then
1941 RAISE FND_API.G_EXC_ERROR;
1942 End If;
1943
1944 /* Delete entries from GMS_EVENT_ATTRIBUTE */
1945 /* Bug 2979125 : added parameter calling_process */
1946 DELETE_GMS_EVENT_ATTRIBUTE(F_Award_Project_id,
1947 F_Event_Num,
1948 X_Calling_Process,
1949 X_Err_Code,
1950 X_Err_Buff);
1951
1952 If X_Err_Code <> 0 then
1953 RAISE FND_API.G_EXC_ERROR;
1954 End If;
1955
1956 End; -- Burden Component Processing
1957
1958
1959 End If;
1960 ----------------------------------------------------------------------------------------------
1961 /* -- Handle net zero events .... (4594090)
1962 HANDLE_NET_ZERO_EVENTS (P_AWARD_PROJECT_ID => F_Award_Project_id,
1963 P_REQUEST_ID => F_request_id,
1964 P_CALLING_PROCESS => 'REVENUE');
1965 Changing this call to the End of the procedure for perfomance reasons. bug 5060427 */
1966 ----------------------------------------------------------------------------------------------
1967 Elsif (X_Event_Type_Class = 'MANUAL') then
1968 Begin
1969
1970 /* Updating GMS_SUMMARY_PROJECT_FUNDINGS Revenue Accrued Amount */
1971 MANIP_BILLREV_AMOUNT(F_Award_Project_id,
1972 F_Event_Num,
1973 X_Calling_Process,
1974 X_Err_Code,
1975 X_Err_Buff);
1976
1977 If X_Err_Code <> 0 then
1978 RAISE FND_API.G_EXC_ERROR;
1979 End If;
1980
1981 /* Get Event Information */
1982 GET_EVENT_PROJ_TASK(F_Event_Num,
1983 F_Award_Project_id,
1984 NULL,
1985 X_Actual_Project_Id,
1986 X_Actual_Task_Id,
1987 X_Expenditure_Org_Id,
1988 X_Revenue_Accumulated,
1989 X_Creation_Date,
1990 X_Err_Code,
1991 X_Err_Buff);
1992
1993 If X_Err_Code <> 0 then
1994 RAISE FND_API.G_EXC_ERROR;
1995 End If;
1996
1997 If X_Revenue_Accumulated ='Y' then
1998
1999 /* Create negative entry in gms_billing_cancellations
2000 for ASI to backout revenue accumulated */
2001 INSERT_BILL_CANCEL(F_Award_Project_id,
2002 F_Event_Num,
2003 NULL, -- expenditure_id
2004 NULL, -- adl_line_num
2005 -1 * F_amount, -- negative entry
2006 X_Calling_Process,
2007 NULL, -- burden_exp_type
2008 NULL, -- burden_cost_code
2009 X_Creation_Date,
2010 X_Actual_Project_Id,
2011 X_Actual_Task_Id,
2012 X_Expenditure_Org_Id,
2016 X_Err_Buff);
2013 sysdate, -- deletion_date
2014 NULL, -- rlmi
2015 X_Err_Code,
2017
2018 If X_Err_Code <> 0 then
2019 RAISE FND_API.G_EXC_ERROR;
2020 End If;
2021
2022 End if;
2023 End;
2024
2025 End If; -- End of If for Event_Type_Classification('Manual' or 'Automatic')
2026
2027 End LOOP;
2028 CLOSE GET_REV_ITEMS_RDL;
2029 X_Err_Code := 0;
2030
2031 -- Handle net zero events .... (4594090)
2032 -- Moved this call from an earlier point to here for bug 5060427
2033 HANDLE_NET_ZERO_EVENTS (P_AWARD_PROJECT_ID => F_Award_Project_id,
2034 P_REQUEST_ID => F_request_id,
2035 P_CALLING_PROCESS => 'REVENUE');
2036
2037 EXCEPTION
2038 WHEN FND_API.G_EXC_ERROR THEN
2039 ROLLBACK;
2040 RETURN;
2041 End DO_REV_ITEM_PROCESSING;
2042
2043
2044 Procedure PERFORM_REV_BILL_ADJS(X_Adj_Action IN VARCHAR2,
2045 X_calling_process IN VARCHAR2,
2046 X_Award_Project_Id IN NUMBER DEFAULT NULL,
2047 X_Draft_Invoice_Num IN NUMBER DEFAULT NULL,
2048 X_Start_Award_Project_Number IN VARCHAR2 DEFAULT NULL,
2049 X_End_Award_Project_Number IN VARCHAR2 DEFAULT NULL,
2050 X_Mass_Gen_Flag IN VARCHAR2 DEFAULT NULL,
2051 X_Adj_Amount IN NUMBER DEFAULT NULL,
2052 RETCODE OUT NOCOPY VARCHAR2,
2053 ERRBUF OUT NOCOPY VARCHAR2) IS
2054
2055 -- X_Adj_Amount Uncommented out NOCOPY to pass Write Off Amount
2056
2057 X_Err_Code NUMBER(1);
2058 X_Err_Buff VARCHAR2(2000);
2059
2060 X_Award_Number VARCHAR2(25);
2061
2062
2063 /*=======================NOT NEEDED FOR R11 .PA will pass the Project Id For Revenue Deletion======
2064 --Cursor to Select Projects which could have potential revenues to be
2065 --deleted
2066
2067 CURSOR GET_TO_BE_DEL_REV_PROJECTS IS
2068 SELECT p.project_id, p.segment1, p.project_level_funding_flag
2069 FROM pa_projects p, pa_draft_revenues r
2070 WHERE p.segment1 BETWEEN X_Start_Award_Project_Number
2071 AND X_End_Award_Project_Number
2072 AND r.project_id = p.project_id
2073 AND r.released_date||'' is null
2074 AND r.generation_error_flag||'' = 'Y'
2075 GROUP BY p.project_id, p.segment1, p.project_level_funding_flag;
2076
2077 X_Rev_Project_Id NUMBER(15);
2078 X_Rev_Segment1 VARCHAR2(30);
2079 X_Rev_Proj_Level_Fund_Flag VARCHAR2(1);
2080 ====================================================================================*/
2081
2082 /* Cursor to Select Revenues that could be potentially deleted for a Project */
2083 CURSOR GET_TO_BE_DEL_REVENUES(X_Project_Id NUMBER) IS
2084 SELECT
2085 draft_revenue_num
2086 , agreement_id
2087 FROM
2088 PA_BILLING_REV_DELETION_V --View Made available from R11
2089 WHERE PROJECT_ID = X_Project_Id
2090 FOR UPDATE NOWAIT;
2091 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2092 /* The code below is used only in 10.7 */
2093 --pa_draft_revenues r
2094 -- WHERE r.project_id = X_Project_Id
2095 -- AND ( r.released_date||'' is NULL
2096 -- AND X_Mass_Gen_Flag = 'N'
2097 -- OR r.generation_error_flag||'' = 'Y'
2098 -- AND X_Mass_Gen_Flag = 'Y'
2099 -- )
2100 -- FOR UPDATE NOWAIT;
2101 --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2102
2103 X_Draft_Revenue_Num NUMBER(15);
2104 X_Rev_Agreement_Id NUMBER(15);
2105
2106 /* Cursor to Select Invoices that could be deleted as a result of unreleased revenues being
2107 deleted. This should not be applicable in the case of GMS where separete events are
2108 created for Revenue and Invoices hence won't be used */
2109 /*---------------------------------------------------------------------+
2110 CURSOR GET_REV_REL_DEL_INVOICES IS |
2111 SELECT |
2112 di.draft_invoice_num |
2113 FROM pa_draft_invoices di |
2114 WHERE di.project_id = :project_id |
2115 AND di.agreement_id+0 = :agreement_id |
2116 AND di.released_date||'' is null |
2117 AND (EXISTS |
2118 (SELECT NULL |
2119 FROM pa_cust_rev_dist_lines l |
2120 WHERE l.project_id = :project_id |
2121 AND l.draft_revenue_num = :draft_revenue_num |
2122 AND l.draft_invoice_num = di.draft_invoice_num) |
2123 OR |
2124 EXISTS |
2125 (SELECT NULL |
2126 FROM pa_cust_event_rev_dist_lines l |
2127 WHERE l.project_id = :project_id |
2128 AND l.draft_revenue_num = :draft_revenue_num |
2129 AND l.draft_invoice_num = di.draft_invoice_num) |
2130 ); |
2131 |
2132 X_Rev_Draft_Invoice_Num NUMBER(15); |
2136
2133 ------------------------------------------------------------------------*/
2134
2135
2137 /* Cursor to Select Draft Invoices which could be deleted for a particular
2138 Project */
2139 CURSOR GET_DRAFT_INVOICES is
2140 Select
2141 draft_invoice_num
2142 from
2143 PA_BILLING_INV_DELETION_V --View made available in R11
2144 WHERE PROJECT_ID = X_Award_Project_Id
2145 FOR UPDATE NOWAIT;
2146
2147 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2148 --The code below is used only in 10.7 as the view above is not available
2149 pa_draft_invoices I,
2150 pa_projects P
2151 where I.project_id = X_Award_Project_Id
2152 AND P.project_id = I.project_id
2153 AND I.Released_By_Person_Id IS NULL
2154 AND nvl(I.CANCEL_CREDIT_MEMO_FLAG, 'N') = 'N'
2155 ORDER BY I.Draft_Invoice_Num
2156 FOR UPDATE NOWAIT;
2157 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
2158
2159 F_Draft_Invoice_Num NUMBER(15);
2160
2161 ROW_LOCKED EXCEPTION;
2162 PRAGMA EXCEPTION_INIT(ROW_LOCKED,-00054);
2163 X_Locked_Row NUMBER;
2164
2165 Begin
2166
2167 fnd_msg_pub.initialize;
2168
2169 If X_Award_Project_Id is NOT NULL THEN
2170 Begin
2171 select award_number into
2172 X_Award_Number from gms_awards
2173 where award_project_id = X_Award_Project_Id;
2174 End;
2175 End If;
2176
2177
2178 If X_Calling_Process = 'Invoice' then -- BEGIN OF IF FOR CALLING PROCESS
2179
2180 /*-------------------Processing Begins for Invoice Adjustments--------------------------*/
2181
2182 Begin
2183 If X_Adj_Action = 'CANCEL' then
2184 Begin
2185 If X_Draft_Invoice_Num is NULL then
2186 gms_error_pkg.gms_message('GMS_DRAFT_INV_NUM_NULL',
2187 X_Exec_Type => 'C',
2188 X_Err_Code => X_Err_Code,
2189 X_Err_Buff => X_Err_Buff);
2190 RETCODE := 'E';
2191 RAISE_APPLICATION_ERROR(-20221,X_Err_Buff);
2192 --RAISE FND_API.G_EXC_ERROR;
2193 RETURN;
2194 Else
2195
2196 /* Lock the Invoice Row so that another process doesn't use it */
2197 Begin
2198 Select
2199 draft_invoice_num
2200 into
2201 X_Locked_Row
2202 from
2203 pa_draft_invoices
2204 where
2205 draft_invoice_num = X_Draft_Invoice_Num
2206 and project_id = X_Award_Project_Id
2207 FOR UPDATE NOWAIT;
2208 EXCEPTION
2209 WHEN ROW_LOCKED THEN
2210 gms_error_pkg.gms_message('GMS_INV_FOR_CANCEL_LOCKED',
2211 'INVOICE_NUM',
2212 X_Draft_Invoice_Num,
2213 X_Exec_Type => 'C',
2214 X_Err_Code => X_Err_Code,
2215 X_Err_Buff => X_Err_Buff);
2216 RETCODE := 'E';
2217 --RAISE_APPLICATION_ERROR(-20222,X_Err_Buff);
2218 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '3 - CANINV'
2219 ,x_message => 'GMS_INV_FOR_CANCEL_LOCKED'
2220 ,x_error_message => X_Err_Msg
2221 ,x_status => X_Status); */
2222
2223 RETURN;
2224 WHEN OTHERS THEN
2225 RETCODE := 'U';
2226 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
2227 'SQLCODE',
2228 SQLCODE,
2229 'SQLERRM',
2230 SQLERRM,
2231 X_Exec_Type => 'C',
2232 X_Err_Code => X_Err_Code,
2233 X_Err_Buff => X_Err_Buff);
2234 --RAISE_APPLICATION_ERROR(-20223,X_Err_Buff);
2235 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '4 - CANINV'
2236 ,x_message => SQLCODE||' - '||SQLERRM
2237 ,x_error_message => X_Err_Msg
2238 ,x_status => X_Status); */
2239
2240 RETURN;
2241 End;
2242
2243 F_Draft_Invoice_Num := X_Draft_Invoice_Num;
2244
2245 DO_INV_ITEM_PROCESSING(X_Award_Project_Id,
2246 F_Draft_Invoice_Num,
2247 'CANCEL',
2248 NULL, --X_Adj_Amount,
2249 X_Calling_Process,
2250 X_Err_Code,
2251 X_Err_Buff);
2252
2253 If X_Err_Code <> 0 then
2254 RAISE FND_API.G_EXC_ERROR;
2255 End If;
2256 End If;
2257 End;
2258
2259 Elsif X_Adj_Action = 'DELETE' then
2260 Begin
2261 open GET_DRAFT_INVOICES;
2262 LOOP
2263
2264 Begin
2265
2266 SAVEPOINT NEXT_INVOICE;
2267
2268 FETCH GET_DRAFT_INVOICES into
2269 F_Draft_Invoice_Num;
2270 EXIT WHEN GET_DRAFT_INVOICES%NOTFOUND;
2271
2272 DO_INV_ITEM_PROCESSING(X_Award_Project_Id,
2273 F_Draft_Invoice_Num,
2274 'DELETE',
2275 NULL,--X_Adj_Amount,
2276 X_Calling_Process,
2277 X_Err_Code,
2278 X_Err_Buff);
2279 If X_Err_Code <> 0 then
2283 WHEN ROW_LOCKED THEN
2280 RAISE FND_API.G_EXC_ERROR;
2281 End If;
2282 EXCEPTION
2284 ROLLBACK to NEXT_INVOICE;
2285 WHEN OTHERS THEN
2286 ROLLBACK to NEXT_INVOICE;
2287 End;
2288
2289 End LOOP;
2290 CLOSE GET_DRAFT_INVOICES;
2291 End;
2292
2293 Elsif X_Adj_Action = 'WRITE_OFF' THEN
2294
2295 /* Lock the Invoice Row so that another process doesn't use it */
2296 Begin
2297 Select
2298 draft_invoice_num
2299 into
2300 X_Locked_Row
2301 from
2302 pa_draft_invoices
2303 where
2304 draft_invoice_num = X_Draft_Invoice_Num
2305 and project_id = X_Award_Project_Id
2306 FOR UPDATE NOWAIT;
2307 EXCEPTION
2308 WHEN ROW_LOCKED THEN
2309 RETCODE := 'E';
2310 gms_error_pkg.gms_message('GMS_INV_FOR_WRITE_OFF_LOCK',
2311 'INVOICE_NUM',
2312 X_Draft_Invoice_Num,
2313 'AWARD_NUMBER',
2314 X_Award_Number,
2315 X_Exec_Type => 'C',
2316 X_Err_Code => X_Err_Code,
2317 X_Err_Buff => X_Err_Buff);
2318 --RAISE_APPLICATION_ERROR(-20224,X_Err_Buff);
2319 RETURN;
2320 WHEN OTHERS THEN
2321 RETCODE := 'U';
2322 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
2323 'SQLCODE',
2324 SQLCODE,
2325 'SQLERRM',
2326 SQLERRM,
2327 X_Exec_Type => 'C',
2328 X_Err_Code => X_Err_Code,
2329 X_Err_Buff => X_Err_Buff);
2330 --RAISE_APPLICATION_ERROR(-20225,X_Err_Buff);
2331 RETURN;
2332 End;
2333 Begin
2334
2335 If (X_Draft_Invoice_Num is NULL ) then
2336 RAISE FND_API.G_EXC_ERROR;
2337 Else
2338
2339 F_Draft_Invoice_Num := X_Draft_Invoice_Num;
2340
2341 DO_INV_ITEM_PROCESSING(X_Award_Project_Id,
2342 F_Draft_Invoice_Num,
2343 'WRITE_OFF',
2344 X_Adj_Amount,
2345 X_Calling_Process,
2346 X_Err_Code,
2347 X_Err_Buff);
2348
2349 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6 - WRIINV'
2350 ,x_message => '6 - After DO_INV_ITEM '||X_Err_Code||' '||X_Err_Buff
2351 ,x_error_message => X_Err_Msg
2352 ,x_status => X_Status);
2353
2354 */
2355 If X_Err_Code <> 0 then
2356 RAISE FND_API.G_EXC_ERROR;
2357 End If;
2358 End If;
2359 End;
2360 End If;
2361
2362 End;
2363
2364 /*--------------------Processing Ends for Invoice Adjustments----------------------*/
2365
2366 Elsif X_Calling_Process = 'Revenue' then
2367
2368 /*--------------------Processing Begins for Revenue Adjustments--------------------*/
2369 If X_Adj_Action = 'DELETE' then
2370 Begin
2371
2372
2373 If X_Mass_Gen_Flag = 'Y' then
2374 Begin
2375 /*=========================================Commented out NOCOPY for R11=========================
2376 --Commented out NOCOPY for R11 as PA will run the extension in a loop for all potential projects
2377 --So the code to actually fetch the projects which will have potential revenues to be deleted
2378 --is not necessary
2379 OPEN GET_TO_BE_DEL_REV_PROJECTS;
2380 LOOP
2381 FETCH GET_TO_BE_DEL_REV_PROJECTS into
2382 X_Rev_Project_Id,
2383 X_Rev_Segment1,
2384 X_Rev_Proj_Level_Fund_Flag;
2385 EXIT WHEN GET_TO_BE_DEL_REV_PROJECTS%NOTFOUND;
2386
2387 ======================================================================================*/
2388
2389 OPEN GET_TO_BE_DEL_REVENUES(X_Award_Project_Id);
2390 LOOP
2391 Begin
2392
2393 SAVEPOINT NEXT_REVENUE;
2394
2395 FETCH GET_TO_BE_DEL_REVENUES into
2396 X_Draft_Revenue_Num,
2397 X_Rev_Agreement_Id;
2398 EXIT WHEN GET_TO_BE_DEL_REVENUES%NOTFOUND;
2399
2400 DO_REV_ITEM_PROCESSING(X_Award_Project_Id,
2401 X_Draft_Revenue_Num,
2402 X_Calling_Process,
2403 X_Err_Code,
2404 X_Err_Buff);
2405
2406 If X_Err_Code <> 0 then
2407 RAISE FND_API.G_EXC_ERROR;
2408 End If;
2409 EXCEPTION
2410 WHEN ROW_LOCKED THEN
2411 ROLLBACK to NEXT_REVENUE;
2412 WHEN OTHERS THEN
2413 ROLLBACK to NEXT_REVENUE;
2414 End;
2415 End LOOP;
2416 CLOSE GET_TO_BE_DEL_REVENUES;
2417 X_Err_Code := 0;
2418 /*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2419 --The code below is commented out NOCOPY for R11 as PA individually passed the Project Id to
2420 -- the extension.
2421 End LOOP;
2422 -- dbms_output.put_line('After Loop for GET_TO_BE_DEL_REV_PROJECTS');
2423 CLOSE GET_TO_BE_DEL_REV_PROJECTS;
2424 X_Err_Code := 'S';
2428 Begin
2425 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
2426 End;
2427 Elsif X_Mass_Gen_Flag = 'N' then
2429 OPEN GET_TO_BE_DEL_REVENUES(X_Award_Project_Id);
2430
2431 LOOP
2432 FETCH GET_TO_BE_DEL_REVENUES into
2433 X_Draft_Revenue_Num,
2434 X_Rev_Agreement_Id;
2435
2436 EXIT WHEN GET_TO_BE_DEL_REVENUES%NOTFOUND;
2437
2438 DO_REV_ITEM_PROCESSING(X_Award_Project_Id,
2439 X_Draft_Revenue_Num,
2440 X_Calling_Process,
2441 X_Err_Code,
2442 X_Err_Buff);
2443
2444 If X_Err_Code <> 0 then
2445 RAISE FND_API.G_EXC_ERROR;
2446 End If;
2447 End LOOP;
2448 CLOSE GET_TO_BE_DEL_REVENUES;
2449 X_Err_Code := 0;
2450 End;
2451 End If;
2452
2453 End;
2454 End If;
2455 /*--------------------Processing Ends for Revenue Adjustments----------------------*/
2456
2457
2458 End If; -- END OF CHECK FOR X_CALLING_PROCESS( INVOICE OR REVENUE)
2459
2460 RETCODE := 'S';
2461
2462 EXCEPTION
2463 WHEN FND_API.G_EXC_ERROR THEN
2464 RETCODE := 'E';
2465 ERRBUF := X_Err_Buff;
2466 ROLLBACK;
2467 RETURN;
2468
2469 End PERFORM_REV_BILL_ADJS;
2470
2471
2472 Procedure DELINV(X_project_id IN NUMBER,
2473 X_top_Task_id IN NUMBER DEFAULT NULL,
2474 X_calling_process IN VARCHAR2 DEFAULT NULL,
2475 X_calling_place IN VARCHAR2 DEFAULT NULL,
2476 X_amount IN NUMBER DEFAULT NULL,
2477 X_percentage IN NUMBER DEFAULT NULL,
2478 X_rev_or_bill_date IN DATE DEFAULT NULL,
2479 X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
2480 X_bill_extension_id IN NUMBER DEFAULT NULL,
2481 X_request_id IN NUMBER DEFAULT NULL) IS
2482 X_retcode VARCHAR2(1);
2483 X_errbuf VARCHAR2(2000);
2484
2485 Begin
2486
2487 gms_error_pkg.set_debug_context; -- Added for Bug: 2510024
2488
2489 IF L_DEBUG = 'Y' THEN
2490 gms_error_pkg.gms_debug('DELINV - Start GMS process for Invoice Deletion','C');
2491 END IF;
2492 /* GMS INSTALLATION CHECK */
2493 If gms_install.enabled then
2494
2495 GMS_BILLING_ADJUSTMENTS.PERFORM_REV_BILL_ADJS('DELETE',
2496 'Invoice',
2497 X_project_id,
2498 NULL,
2499 NULL,
2500 NULL,
2501 NULL,
2502 NULL,
2503 X_retcode,
2504 X_errbuf);
2505 If X_retcode <> 'S' then
2506 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.DELINV'
2507 ,x_message => X_errbuf
2508 ,x_error_message => X_Err_Msg
2509 ,x_status => X_Status); */
2510 RAISE_APPLICATION_ERROR(-20226,X_errbuf);
2511
2512 End If;
2513
2514 End if;
2515 IF L_DEBUG = 'Y' THEN
2516 gms_error_pkg.gms_debug('DELINV - End GMS process for Invoice Deletion','C');
2517 END IF;
2518
2519 End DELINV;
2520
2521 Procedure CANINV(X_project_id IN NUMBER,
2522 X_top_Task_id IN NUMBER DEFAULT NULL,
2523 X_calling_process IN VARCHAR2 DEFAULT NULL,
2524 X_calling_place IN VARCHAR2 DEFAULT NULL,
2525 X_amount IN NUMBER DEFAULT NULL,
2526 X_percentage IN NUMBER DEFAULT NULL,
2527 X_rev_or_bill_date IN DATE DEFAULT NULL,
2528 X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
2529 X_bill_extension_id IN NUMBER DEFAULT NULL,
2530 X_request_id IN NUMBER DEFAULT NULL) IS
2531
2532 X_retcode VARCHAR2(1);
2533 X_errbuf VARCHAR2(2000);
2534
2535 X_Draft_Invoice_Num_Credited NUMBER;
2536
2537 X_Err_Code NUMBER(1);
2538 X_Err_Buff VARCHAR2(2000);
2539
2540 Begin
2541
2542 gms_error_pkg.set_debug_context; -- Added for Bug: 2510024
2543
2544 IF L_DEBUG = 'Y' THEN
2545 gms_error_pkg.gms_debug('CANINV - Start GMS process for Invoice Cancellation','C');
2546 END IF;
2547
2548 /* GMS INSTALLATION CHECK */
2549 If gms_install.enabled then
2550
2551 Begin
2552 select
2553 b.DRAFT_INVOICE_NUM_CREDITED
2554 into
2555 X_Draft_Invoice_Num_Credited
2556 from
2557 PA_BILLING_INV_PROCESSED_V a
2558 ,PA_DRAFT_INVOICES b
2559 where a.project_id = X_project_id
2560 and b.project_id = a.project_id
2561 and b.draft_invoice_num = a.draft_invoice_num;
2562
2563 EXCEPTION
2564 WHEN NO_DATA_FOUND THEN
2565 gms_error_pkg.gms_message('GMS_NO_INV_FOR_CANCEL',
2566 'PRJ',
2567 X_Project_Id,
2568 X_Exec_Type => 'C',
2569 X_Err_Code => X_Err_Code,
2570 X_Err_Buff => X_Err_Buff);
2574 ,x_status => X_Status); */
2571 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '1 - CANINV'
2572 ,x_message => 'No Invoice found for Cancellation'
2573 ,x_error_message => X_Err_Msg
2575 RAISE_APPLICATION_ERROR(-20027,X_Err_Buff);
2576 End;
2577
2578 GMS_BILLING_ADJUSTMENTS.PERFORM_REV_BILL_ADJS('CANCEL',
2579 'Invoice',
2580 X_project_id,
2581 X_Draft_Invoice_Num_Credited,
2582 NULL,
2583 NULL,
2584 NULL,
2585 NULL,
2586 X_retcode,
2587 X_errbuf);
2588
2589 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.CANINV'
2590 ,x_message => 'Retcode is '||X_retcode
2591 ,x_error_message => X_Err_Msg
2592 ,x_status => X_Status);
2593 */
2594 If X_retcode <> 'S' then
2595
2596 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.CANINV'
2597 ,x_message => X_Errbuf
2598 ,x_error_message => X_Err_Msg
2599 ,x_status => X_Status); */
2600 RAISE_APPLICATION_ERROR(-20228,X_Errbuf);
2601 End If;
2602
2603 End if;
2604 IF L_DEBUG = 'Y' THEN
2605 gms_error_pkg.gms_debug('CANINV - End GMS process for Invoice Cancellation','C');
2606 END IF;
2607 End CANINV;
2608
2609
2610 Procedure WRIINV(X_project_id IN NUMBER,
2611 X_top_Task_id IN NUMBER DEFAULT NULL,
2612 X_calling_process IN VARCHAR2 DEFAULT NULL,
2613 X_calling_place IN VARCHAR2 DEFAULT NULL,
2614 X_amount IN NUMBER DEFAULT NULL,
2615 X_percentage IN NUMBER DEFAULT NULL,
2616 X_rev_or_bill_date IN DATE DEFAULT NULL,
2617 X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
2618 X_bill_extension_id IN NUMBER DEFAULT NULL,
2619 X_request_id IN NUMBER DEFAULT NULL) IS
2620
2621 X_retcode VARCHAR2(1);
2622 X_errbuf VARCHAR2(2000);
2623 X_Draft_Invoice_Num_Credited NUMBER;
2624 X_Err_Msg VARCHAR2(2000);
2625 X_status NUMBER;
2626 X_Err_Code NUMBER(1);
2627 X_Err_Buff VARCHAR2(2000);
2628
2629 Begin
2630
2631 gms_error_pkg.set_debug_context; -- Added for Bug: 2510024
2632
2633 /* GMS INSTALLATION CHECK */
2634 If gms_install.enabled then
2635
2636 Begin
2637
2638 select draft_invoice_num_credited
2639 into X_Draft_Invoice_Num_Credited
2640 from pa_draft_invoices_all
2641 where project_id = X_project_id
2642 and request_id = X_request_id
2643 and (nvl(write_off_flag,'N') = 'Y' OR
2644 nvl(concession_flag,'N') = 'Y');
2645
2646 g_request_id := X_request_id;
2647
2648 GMS_BILLING_ADJUSTMENTS.PERFORM_REV_BILL_ADJS('WRITE_OFF',
2649 'Invoice',
2650 X_project_id,
2651 X_Draft_Invoice_Num_Credited,
2652 NULL,
2653 NULL,
2654 NULL,
2655 NULL,
2656 X_retcode,
2657 X_errbuf);
2658
2659 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '7 - WRIINV'
2660 ,x_message => 'AFTER ADJUSTMENTS Retcode '||x_retcode||' '||X_errbuf
2661 ,x_error_message => X_Err_Msg
2662 ,x_status => X_Status); */
2663
2664 If X_retcode <> 'S' then
2668 ,x_status => X_Status); */
2665 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '8 - WRIINV'
2666 ,x_message => 'Failure '
2667 ,x_error_message => X_Err_Msg
2669
2670 RAISE_APPLICATION_ERROR(-20230,X_errbuf);
2671
2672 End If;
2673 EXCEPTION
2674 WHEN OTHERS THEN
2675 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '9 - WRIINV'
2676 ,x_message => 'Failure - When Others'
2677 ,x_error_message => X_Err_Msg
2678 ,x_status => X_Status);
2679 */
2680 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERROR',
2681 'SQLCODE',
2682 SQLCODE,
2683 'SQLERRM',
2684 SQLERRM,
2685 X_Exec_Type => 'C',
2686 X_Err_Code => X_Err_Code,
2687 X_Err_Buff => X_Err_Buff);
2688 RAISE_APPLICATION_ERROR(-20231,X_Err_Buff);
2689 End;
2690
2691 End if;
2692
2693 End WRIINV;
2694
2695
2696 Procedure DELREV(X_project_id IN NUMBER,
2697 X_top_Task_id IN NUMBER DEFAULT NULL,
2698 X_calling_process IN VARCHAR2 DEFAULT NULL,
2699 X_calling_place IN VARCHAR2 DEFAULT NULL,
2700 X_amount IN NUMBER DEFAULT NULL,
2701 X_percentage IN NUMBER DEFAULT NULL,
2702 X_rev_or_bill_date IN DATE DEFAULT NULL,
2703 X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
2704 X_bill_extension_id IN NUMBER DEFAULT NULL,
2705 X_request_id IN NUMBER DEFAULT NULL) IS
2706
2707 X_retcode VARCHAR2(1);
2708 X_errbuf VARCHAR2(2000);
2709
2710 X_Err_Msg VARCHAR2(2000);
2711 X_Status NUMBER;
2712 X_Err_Code NUMBER(1);
2713 X_Err_Buff VARCHAR2(2000);
2714
2715 Begin
2716
2717 gms_error_pkg.set_debug_context; -- Added for Bug: 2510024
2718
2719 IF L_DEBUG = 'Y' THEN
2720 gms_error_pkg.gms_debug('DELREV - Start GMS process for Revenue Deletion','C');
2721 END IF;
2722
2723 /* GMS INSTALLATION CHECK */
2724 If gms_install.enabled then
2725
2726 GMS_BILLING_ADJUSTMENTS.PERFORM_REV_BILL_ADJS('DELETE',
2727 'Revenue',
2728 X_project_id,
2729 NULL,
2730 NULL,
2731 NULL,
2732 'N',
2733 NULL,
2734 X_retcode,
2735 X_errbuf);
2736 If X_retcode <> 'S' then
2737 /* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.DELREV'
2738 ,x_message => X_errbuf
2739 ,x_error_message => X_Err_Msg
2740 ,x_status => X_Status);
2741 */
2742 RAISE_APPLICATION_ERROR(-20232,X_errbuf);
2743
2744 End If;
2745
2746 End if;
2747 IF L_DEBUG = 'Y' THEN
2748 gms_error_pkg.gms_debug('DELREV - End GMS process for Revenue Deletion','C');
2749 END IF;
2750
2751 End DELREV;
2752
2753 End GMS_BILLING_ADJUSTMENTS;