[Home] [Help]
PACKAGE BODY: APPS.GMS_EVT_BILLING
Source
1 PACKAGE BODY GMS_EVT_BILLING AS
2 -- $Header: gmsinmab.pls 120.1 2005/07/26 14:35:46 appldev ship $
3
4
5 NO_PROCESSING_OF_AWARD Exception;
6 -- ## This exception is handled in the main procedure MANUAL_BILLING
7 -- ## but this exception is called from procedures which are called within MANUAL_BILLING
8
9 Procedure GET_EVENT_INFO(X_Project_Id IN NUMBER,
10 X_Event_Num IN NUMBER,
11 X_Event_Type OUT NOCOPY VARCHAR2,
12 X_Event_Type_Classification OUT NOCOPY VARCHAR2,
13 X_Installment_Id OUT NOCOPY VARCHAR2,
14 X_Actual_Project_Id OUT NOCOPY NUMBER,
15 X_Actual_Task_Id OUT NOCOPY NUMBER) IS
16 X_Err_Code Number;
17 X_Err_buff Varchar2(2000);
18
19 Begin
20
21 -- For 11.5, event information is being picked from gms_events_v view
22
23 Select distinct
24 a.event_type,
25 b.event_type_classification,
26 a.installment_id,
27 a.actual_project_id,
28 a.actual_task_id
29 into
30 X_Event_Type,
31 X_Event_Type_Classification,
32 X_Installment_Id,
33 X_Actual_Project_Id,
34 X_Actual_Task_Id
35 from
36 gms_events_v a,
37 pa_event_types b
38 where
39 a.project_id = X_Project_Id and
40 a.event_num = X_Event_Num and
41 a.event_type = b.event_type
42 ;
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 gms_error_pkg.gms_message('GMS_NO_DATA_PRJ_EVT',
46 'EVT',
47 X_Event_Num,
48 'PRJ',
49 X_Project_Id,
50 X_Exec_Type => 'C',
51 X_Err_Code => X_Err_Code,
52 X_Err_Buff => X_Err_Buff);
53 --RAISE_APPLICATION_ERROR(-20110,X_Err_Buff);
54
55 RAISE NO_PROCESSING_OF_AWARD;
56
57 WHEN TOO_MANY_ROWS THEN
58 gms_error_pkg.gms_message('GMS_TOO_MANY_ROWS_PRJ_EVT',
59 'PRJ',
60 X_Project_Id,
61 'EVT',
62 X_Event_Num,
63 X_Exec_Type =>'C',
64 X_Err_Code => X_Err_Code,
65 X_Err_Buff => X_Err_Buff);
66 --RAISE_APPLICATION_ERROR(-20111,X_Err_Buff);
67
68 RAISE NO_PROCESSING_OF_AWARD;
69
70 End GET_EVENT_INFO;
71
72 Procedure GET_CURR_BILLED_AMOUNT(X_calling_process IN VARCHAR2,
73 X_Actual_Project_Id IN NUMBER,
74 X_Actual_Task_Id IN NUMBER,
75 X_Installment_Id IN NUMBER,
76 X_BillRev_Amount OUT NOCOPY NUMBER) IS
77 X_Err_Code Number;
78 X_Err_buff Varchar2(2000);
79
80 Begin
81
82 Select
83 decode(X_calling_process,'Invoice',nvl(gmf.total_billed_amount,0),'Revenue',nvl(gmf.total_revenue_amount,0),NULL)
84 into
85 X_BillRev_Amount
86 from
87 GMS_SUMMARY_PROJECT_FUNDINGS gmf
88 where
89 gmf.project_id = X_Actual_Project_Id
90 and
91 (
92 (gmf.task_id IS NULL)
93 or (gmf.task_Id = X_Actual_Task_Id)
94 or (gmf.task_id = (select top_task_id from pa_tasks where task_id = X_Actual_Task_Id))
95 )
96 and installment_id = X_Installment_Id;
97
98 EXCEPTION
99 WHEN NO_DATA_FOUND THEN
100 gms_error_pkg.gms_message('GMS_NO_DATA_PRJ_TASK_INST',
101 'PRJ',
102 X_Actual_Project_Id,
103 'TASK',
104 X_Actual_Task_Id,
105 'INST',
106 X_Installment_Id,
107 X_Exec_Type => 'C',
108 X_Err_Code => X_Err_Code,
109 X_Err_Buff => X_Err_Buff);
110 --RAISE_APPLICATION_ERROR(-20112,X_Err_Buff);
111 RAISE NO_PROCESSING_OF_AWARD;
112
113 End GET_CURR_BILLED_AMOUNT;
114
115 Procedure UPDATE_GMS_SUMMARY_FUNDING(X_calling_process IN VARCHAR2,
116 X_Actual_Project_Id IN NUMBER,
117 X_Actual_Task_Id IN NUMBER,
118 X_Installment_Id IN NUMBER,
119 X_Bill_Rev_Amount IN NUMBER) IS
120 X_Curr_BillRev_Amount NUMBER;
121 X_Total_BillRev_Amount NUMBER;
122
123 X_Err_Code Number;
124 X_Err_buff Varchar2(2000);
125
126 Begin
127
128 GET_CURR_BILLED_AMOUNT(X_calling_process,
129 X_Actual_Project_Id,
130 X_Actual_Task_Id,
131 X_Installment_Id,
132 X_Curr_BillRev_Amount);
133
134 X_Total_BillRev_Amount := X_Bill_Rev_Amount + X_Curr_BillRev_Amount;
135
136 If X_calling_process = 'Invoice' then
137
138 Update GMS_SUMMARY_PROJECT_FUNDINGS gmf
139 set
140 gmf.total_billed_amount = X_Total_BillRev_Amount
141 ,gmf.last_update_date = sysdate
142 ,gmf.last_updated_by = fnd_global.user_id
143 ,gmf.last_update_login = fnd_global.login_id
144 where
145 gmf.project_id = X_Actual_Project_Id
146 and
147 (
148 (gmf.task_id IS NULL)
149 or(gmf.task_id = X_Actual_Task_Id)
150 or(gmf.task_id = (select top_task_id from pa_tasks where task_id = X_Actual_Task_Id))
151 )
152 and
153 installment_id = X_Installment_Id;
154
155 Elsif X_calling_process = 'Revenue' then
156
157 Update GMS_SUMMARY_PROJECT_FUNDINGS gmf
158 set
159 gmf.total_revenue_amount = X_Total_BillRev_Amount
160 ,gmf.last_update_date = sysdate
161 ,gmf.last_updated_by = fnd_global.user_id
162 ,gmf.last_update_login = fnd_global.login_id
163 where
164 gmf.project_id = X_Actual_Project_Id
165 and
166 (
167 (gmf.task_id IS NULL)
168 or(gmf.task_id = X_Actual_Task_Id)
169 or(gmf.task_id = (select top_task_id from pa_tasks where task_id = X_Actual_Task_Id))
170 )
171 and
172 installment_id = X_Installment_Id;
173
174 End If;
175 IF SQL%NOTFOUND then
176 gms_error_pkg.gms_message('GMS_NO_UPD_PRJ_TASK_INST',
177 'PRJ',
178 X_Actual_Project_Id,
179 'TASK',
180 X_Actual_Task_Id,
181 'INST',
182 X_Installment_Id,
183 X_Exec_Type => 'C',
184 X_Err_Code => X_Err_Code,
185 X_Err_Buff => X_Err_Buff);
186 --RAISE_APPLICATION_ERROR(-20113,X_Err_Buff);
187 RAISE NO_PROCESSING_OF_AWARD;
188 End IF;
189
190 End UPDATE_GMS_SUMMARY_FUNDING;
191
192 PROCEDURE MANUAL_BILLING( X_project_id IN NUMBER,
193 X_top_Task_id IN NUMBER DEFAULT NULL,
194 X_calling_process IN VARCHAR2 DEFAULT NULL,
195 X_calling_place IN VARCHAR2 DEFAULT NULL,
196 X_amount IN NUMBER DEFAULT NULL,
197 X_percentage IN NUMBER DEFAULT NULL,
198 X_rev_or_bill_date IN DATE DEFAULT NULL,
199 X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
200 X_bill_extension_id IN NUMBER DEFAULT NULL,
201 X_request_id IN NUMBER DEFAULT NULL) IS
202
203 /*------------------------------------------------------------------*/
204
205 CURSOR GET_INVOICES IS
206 Select
207 draft_invoice_num
208 from
209 pa_draft_invoices
210 where
211 project_id = X_project_id and
212 request_id = X_request_id;
213
214 F_Draft_Invoice_Num NUMBER;
215
216 CURSOR GET_INVOICE_ITEMS IS
217 Select
218 line_num,
219 event_num,
220 amount
221 from
222 pa_draft_invoice_items
223 where
224 project_id = X_project_id and
225 draft_invoice_num = F_Draft_Invoice_Num;
226 F_Line_Num NUMBER;
227 F_Event_Num NUMBER;
228 F_Bill_Amount NUMBER;
229
230 /*--------------------------------------------------------------------*/
231
232 CURSOR GET_REVENUES IS
233 Select
234 draft_revenue_num
235 from
236 pa_draft_revenues
237 where project_id = X_project_id and
238 request_id = X_request_id;
239
240 F_Draft_Revenue_Num NUMBER;
241
242 CURSOR GET_REV_ITEMS_RDL IS
243 Select
244 ri.project_id,
245 ri.line_num,
246 rdl.event_num,
247 ri.amount
248 from
249 pa_draft_revenue_items ri,
250 pa_cust_event_rdl_all rdl
251 where
252 ri.draft_revenue_num = F_Draft_Revenue_Num and
253 ri.project_id = X_project_Id and
254 rdl.draft_revenue_num = ri.draft_revenue_num and
255 rdl.draft_revenue_item_line_num = ri.line_num and
256 rdl.project_id = ri.project_id ;
257
258 F_Award_Project_Id NUMBER;
259 F_Rev_Line_Num NUMBER;
260 F_Rev_Event_Num NUMBER;
261 F_Rev_Amount NUMBER;
262
263 /*----------------------------------------------------------------------*/
264
265 X_Event_Type VARCHAR2(30);
266 X_Event_Type_Classification VARCHAR2(30);
267 X_Installment_Id NUMBER(15);
268 X_Actual_Project_Id NUMBER(15);
269 X_Actual_Task_Id NUMBER(15);
270 X_Sponsored_Type_Flag VARCHAR2(1);
271
272 X_Project_Type VARCHAR2(30);
273
274 X_Award_Rev_Distribution_Rule VARCHAR2(30);
275 X_Award_Bill_Distribution_Rule VARCHAR2(30);
276
277 X_Err_Code Number;
278 X_Err_buff Varchar2(2000);
279
280 X_Stage Number(3);
281
282 Begin
283
284 gms_error_pkg.set_debug_context; -- Added for Bug: 2510024
285
286 SAVEPOINT MANUAL_BILLING_SAVEPOINT;
287
288 /* CHECK IF GMS_INSTALLATION HAS BEEN CARRIED OUT NOCOPY */
289
290 If gms_install.enabled then
291
292 X_Stage := 10;
293
294 /* Checking the Sponsored Flag on the Project Type */
295 /* -- This checking is supposed to be for the Actual Projects on the Exp Items
296 -- not for Award Projects !!! Mistake.
297 Begin
298 select
299 nvl(pt.attribute1,'N')--Sponsored Flag
300 into
301 X_Sponsored_Type_Flag
302 from
303 PA_PROJECTS_ALL p,
304 PA_PROJECT_TYPES pt
305 where
306 p.project_id = X_Project_Id
307 and p.project_type = pt.project_type;
308 End;
309 */
310
311 /* Getting the PROJECT_TYPE. GO Through Extension only if PROJECT_TYPE is
312 'AWARD_PROJECT'
313 */
314 Begin
315 select
316 project_type
317 into
318 X_Project_Type
319 from
320 PA_PROJECTS_ALL
321 where project_id = X_Project_Id;
322 End;
323
324 X_Stage := 20;
325
326 /* Checking the Revenue and Billing Distribution Rule on the Award */
327 Begin
328 select
329 Revenue_Distribution_Rule,
330 Billing_Distribution_Rule
331 into
332 X_Award_Rev_Distribution_Rule,
333 X_Award_Bill_Distribution_Rule
334 from
335 GMS_AWARDS
336 where
337 Award_Project_Id = X_Project_Id;
338 End;
339
340 If X_Project_Type = 'AWARD_PROJECT' then
341
342 /*-------------------------------------------------------------------------------------*/
343
344 IF (X_calling_process = 'Invoice' and X_Award_Bill_Distribution_Rule = 'EVENT') then
345
346 open GET_INVOICES;
347 LOOP
348 FETCH GET_INVOICES
349 into
350 F_Draft_Invoice_Num;
351
352
353 EXIT WHEN GET_INVOICES%NOTFOUND;
354
355 open GET_INVOICE_ITEMS;
356 LOOP
357 FETCH GET_INVOICE_ITEMS
358 into
359 F_Line_Num,
360 F_Event_Num,
361 F_Bill_Amount;
362
363
364 EXIT WHEN GET_INVOICE_ITEMS%NOTFOUND;
365
366 GET_EVENT_INFO(X_Project_Id,
367 F_Event_Num,
368 X_Event_Type,
369 X_Event_Type_Classification,
370 X_Installment_Id,
371 X_Actual_Project_Id,
372 X_Actual_Task_Id);
373
374 If X_Event_Type_Classification = 'MANUAL' then
375
376 UPDATE_GMS_SUMMARY_FUNDING(X_calling_process,
377 X_Actual_Project_Id,
378 X_Actual_Task_Id,
379 X_Installment_Id,
380 F_Bill_Amount);
381 End If;
382 END LOOP;
383 close GET_INVOICE_ITEMS;
384 End LOOP;
385 close GET_INVOICES;
386 End If; -- End If for Billing Distribution Rule Check
387
388 /*----------------------------------------------------------------------------------------*/
389 If (X_calling_process = 'Revenue' and X_Award_Rev_Distribution_Rule = 'EVENT') then
390
391 open GET_REVENUES;
392
393 LOOP
394 FETCH GET_REVENUES into
395 F_Draft_Revenue_Num;
396
397 EXIT WHEN GET_REVENUES%NOTFOUND;
398
399 /* Get Revenue RDLS and corresponding Event Information */
400 open GET_REV_ITEMS_RDL;
401
402 LOOP
403 FETCH GET_REV_ITEMS_RDL
404 into
405 F_Award_Project_Id,
406 F_Rev_Line_Num,
407 F_Rev_Event_Num,
408 F_Rev_Amount;
409 EXIT WHEN GET_REV_ITEMS_RDL%NOTFOUND;
410
411 GET_EVENT_INFO(F_Award_Project_Id,
412 F_Rev_Event_Num,
413 X_Event_Type,
414 X_Event_Type_Classification,
415 X_Installment_Id,
416 X_Actual_Project_Id,
417 X_Actual_Task_Id);
418
419
420 If X_Event_Type_Classification = 'MANUAL' then
421
422 UPDATE_GMS_SUMMARY_FUNDING(X_calling_process,
423 X_Actual_Project_Id,
424 X_Actual_Task_Id,
425 X_Installment_Id,
426 F_Rev_Amount);
427 End If;
428
429
430 End LOOP; --End of LOOP for GET_REVENUE_ITEMS_RDL
431 close GET_REV_ITEMS_RDL;
432
433 End LOOP;
434 close GET_REVENUES;
435
436 End If; -- End If for Revenue Distribution Rule Check
437
438 /*----------------------------------------------------------------------------------------*/
439 End If; -- End If for PROJECT_TYPE Check
440
441 End if; -- GMS_INSTALLATION CHECK
442
443 Exception
444
445 WHEN NO_DATA_FOUND THEN
446
447 gms_error_pkg.gms_message('GMS_NO_DATA_PRJ_AT_STAGE',
448 'PRJ',
449 X_Project_Id,
450 'STAGE',
451 X_Stage,
452 X_Exec_Type => 'C',
453 X_Err_Code => X_Err_Code,
454 X_Err_Buff => X_Err_Buff);
455 --RAISE_APPLICATION_ERROR(-20114,X_Err_Buff);
456 ROLLBACK TO MANUAL_BILLING_SAVEPOINT;
457 RETURN;
458
459 When NO_PROCESSING_OF_AWARD then
460
461 -- ## This exception is declared at the package level
462 -- ## This exception is called from many procedures called by MANUAL_BILLING
463 -- ## It is only handled in this program
464 -- ## When this exception is raised, all data for that award_project_id
465 -- ## which has been modified or created is rolled back thus ensuring consistency
466 -- ## Further processing for that award is ignored.
467
468 gms_error_pkg.gms_message('GMS_UNEXPECTED_ERR_NO_PROCESS',
469 'PRJ',
470 X_Project_id,
471 X_Exec_Type => 'C',
472 X_Err_Code => X_Err_Code,
473 X_Err_Buff => X_Err_Buff);
474 ROLLBACK TO MANUAL_BILLING_SAVEPOINT;
475 RETURN;
476
477 End MANUAL_BILLING;
478 End GMS_EVT_BILLING;