[Home] [Help]
PACKAGE BODY: APPS.PA_BILLING_PUB
Source
1 Package BODY pa_billing_pub AS
2 /* $Header: PAXIPUBB.pls 120.6.12020000.2 2012/07/19 10:05:00 admarath ship $ */
3
4 --------------------------------------
5 -- PROCEDURE/FUNCTION IMPLEMENTATIONS
6 --
7
8 ---------------------
9 -- GLOBALS
10 --
11 status VARCHAR2(240); -- For error messages from subprogs
12 last_updated_by NUMBER(15); --|
13 created_by NUMBER(15); --|
14 last_update_login NUMBER(15); --|Standard Who Columns
15 -- request_id NUMBER(15); --|
16 program_application_id NUMBER(15); --|
17 program_id NUMBER(15); --|
18
19 -- get_budget_amount modified to use User defined budget types
20 -- and use api pa_budget_utils.get_project_task_totals
21 --
22 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
23
24 PROCEDURE get_budget_amount( X2_project_id NUMBER,
25 X2_task_id NUMBER DEFAULT NULL,
26 X2_revenue_amount OUT NOCOPY REAL,
27 X2_cost_amount OUT NOCOPY REAL,
28 P_cost_budget_type_code IN VARCHAR2 DEFAULT NULL,
29 P_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
30 P_cost_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin Plan impact */
31 P_rev_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin Plan impact */
32 X_cost_budget_type_code OUT NOCOPY VARCHAR2,
33 X_rev_budget_type_code OUT NOCOPY VARCHAR2,
34 X_error_message OUT NOCOPY VARCHAR2,
35 X_status OUT NOCOPY NUMBER
36 ) IS
37
38 -- local variables for budget codes
39 l_cost_budget_type_code VARCHAR2(30) ;
40 l_rev_budget_type_code VARCHAR2(30) ;
41 l_cost_budget_status_code VARCHAR2(1) ;
42 l_rev_budget_status_code VARCHAR2(1) ;
43 dummy CHAR(1);
44 err_msg VARCHAR2(240);
45 err_status NUMBER;
46 l_status NUMBER;
47 l_cost_budget_version_id NUMBER;
48 l_rev_budget_version_id NUMBER;
49 l_raw_cost_total REAL := 0;
50 l_revenue_total REAL := 0;
51 l_quantity_total NUMBER;
52 l_burdened_cost_total NUMBER;
53 l_err_code NUMBER;
54 l_err_stage VARCHAR2(30);
55 l_err_stack VARCHAR2(630);
56
57 /* Added for Fin Plan Impact */
58 l_cost_plan_type_id NUMBER;
59 l_rev_plan_type_id NUMBER;
60 l_cost_plan_version_id NUMBER;
61 l_rev_plan_version_id NUMBER;
62 /* Till here */
63
64 invalid_cost_budget_code EXCEPTION;
65 invalid_rev_budget_code EXCEPTION;
66 rev_budget_not_baselined EXCEPTION;
67 cost_budget_not_baselined EXCEPTION;
68
69 BEGIN
70 IF g1_debug_mode = 'Y' THEN
71 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.get_budget_amount: ');
72 END IF;
73 X_status := 0;
74 X_error_message := NULL;
75 BEGIN
76 -- If user doesnt provide the budget get the default value from biling extensions
77 --
78
79 IF (P_cost_budget_type_code IS NULL OR P_rev_budget_type_code IS NULL
80 OR P_cost_plan_type_id IS NULL OR P_rev_plan_type_id IS NULL ) /* Added for Fin plan impact */
81 THEN
82
83 SELECT DECODE(P_cost_budget_type_code,NULL,default_cost_budget_type_code, P_cost_budget_type_code),
84 DECODE(P_rev_budget_type_code,NULL,default_rev_budget_type_code,P_rev_budget_type_code),
85 DECODE(P_cost_plan_type_id,NULL,default_cost_plan_type_id,
86 P_cost_plan_type_id), /* Added for Fin plan impact */
87 DECODE(P_rev_plan_type_id,NULL,default_rev_plan_type_id,
88 P_rev_plan_type_id) /* Added for Fin plan impact */
89 INTO l_cost_budget_type_code,
90 l_rev_budget_type_code,
91 l_cost_plan_type_id,
92 l_rev_plan_type_id
93 FROM pa_billing_extensions
94 WHERE billing_extension_id=pa_billing.GetBillingExtensionId;
95
96 END IF;
97
98
99 -- The plan code should be a valid code and of the right version type
100 -- If invalid then process will check for budget code
101 /* Added this select for Fin plan Impact */
102 BEGIN
103 SELECT 'x'
104 INTO dummy
105 FROM dual
106 WHERE EXISTS( SELECT *
107 FROM pa_fin_plan_types_b f
108 WHERE f.fin_plan_type_id=l_cost_plan_type_id );
109
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112
113 -- The budget code should be a valid code and of the right amount code
114 -- If invalid then raise appropriate exception
115
116 BEGIN
117
118 SELECT 'x'
119 INTO dummy
120 FROM pa_budget_types
121 WHERE budget_type_code = l_cost_budget_type_code
122 AND budget_amount_code = 'C';
123
124 EXCEPTION
125 WHEN NO_DATA_FOUND THEN
126 raise invalid_cost_budget_code;
127 END;
128
129 END; /* End of newly added code for Fin plan Impact */
130
131
132
133
134 /* Added this select for Fin plan Impact */
135 BEGIN
136 SELECT 'x'
137 INTO dummy
138 FROM dual
139 WHERE EXISTS( SELECT *
140 FROM pa_fin_plan_types_b f
141 WHERE f.fin_plan_type_id=l_rev_plan_type_id );
142
143 EXCEPTION
144 WHEN NO_DATA_FOUND THEN
145
146 -- The budget code should be a valid code and of the right amount code
147 -- If invalid then raise appropriate exception
148
149 BEGIN
150
151 SELECT 'x'
152 INTO dummy
153 FROM pa_budget_types
154 WHERE budget_type_code = l_rev_budget_type_code
155 AND budget_amount_code = 'R';
156
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 raise invalid_rev_budget_code;
160 END ;
161
162 END; /* End of newly added code for Fin plan Impact */
163
164 -- Get the budget version id for cost and revenue plan
165 -- Changed to use api pa_budget_utils.get_project_task_totals
166
167 /* Added this select for Fin plan Impact */
168 BEGIN
169
170 SELECT v.budget_version_id
171 INTO l_cost_plan_version_id
172 FROM pa_budget_versions v
173 WHERE v.project_id = X2_project_id
174 AND v.current_flag = 'Y'
175 AND v.budget_status_code = 'B'
176 AND v.fin_plan_type_id = l_cost_plan_type_id
177 AND v.version_type IN ('COST','ALL');
178
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 --
182 -- get the budget version id for cost and revenue budget
183 -- Changed to use api pa_budget_utils.get_project_task_totals
184 --
185
186 BEGIN
187 SELECT budget_version_id
188 INTO l_cost_budget_version_id
189 FROM pa_budget_versions pbv
190 WHERE project_id = X2_project_id
191 AND budget_type_code = l_cost_budget_type_code
192 AND budget_status_code = 'B'
193 AND current_flag = 'Y';
194
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 raise cost_budget_not_baselined;
198 END;
199
200 END; /* End of newly added code for Fin plan Impact */
201
202 l_cost_budget_version_id := NVL(l_cost_plan_version_id,l_cost_budget_version_id);
203
204 /* Added this select for Fin plan Impact */
205 BEGIN
206
207 SELECT v.budget_version_id
208 INTO l_rev_plan_version_id
209 FROM pa_budget_versions v
210 WHERE v.project_id = X2_project_id
211 AND v.current_flag = 'Y'
212 AND v.budget_status_code = 'B'
213 AND v.fin_plan_type_id = l_rev_plan_type_id
214 AND v.version_type IN ('REVENUE','ALL');
215
216 EXCEPTION
217 WHEN NO_DATA_FOUND THEN
218 --
219 -- get the budget version id for cost and revenue budget
220 -- Changed to use api pa_budget_utils.get_project_task_totals
221 --
222
223 BEGIN
224
225 SELECT budget_version_id
226 INTO l_rev_budget_version_id
227 FROM pa_budget_versions pbv
228 WHERE project_id = X2_project_id
229 AND budget_type_code = l_rev_budget_type_code
230 AND budget_status_code = 'B'
231 AND current_flag = 'Y';
232
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 raise rev_budget_not_baselined;
236 END;
237
238 END; /* End of newly added code for Fin plan Impact */
239
240 l_rev_budget_version_id := NVL(l_rev_plan_version_id,l_rev_budget_version_id);
241
242 -- Call api to get cost budget amount
243 --
244 pa_budget_utils.get_project_task_totals
245 (l_cost_budget_version_id ,
246 x2_task_id ,
247 l_quantity_total,
248 l_raw_cost_total ,
249 l_burdened_cost_total ,
250 l_revenue_total ,
251 l_err_code ,
252 l_err_stage,
253 l_err_stack );
254
255 X2_cost_amount := pa_currency.round_currency_amt(l_burdened_cost_total);
256
257 -- Call api to get revenue budget amount
258 --
259 pa_budget_utils.get_project_task_totals
260 (l_rev_budget_version_id ,
261 x2_task_id ,
262 l_quantity_total,
263 l_raw_cost_total ,
264 l_burdened_cost_total ,
265 l_revenue_total ,
266 l_err_code ,
267 l_err_stage,
268 l_err_stack );
269
270 X2_revenue_amount := pa_currency.round_currency_amt(l_revenue_total);
271
272 X_cost_budget_type_code := l_cost_budget_type_code;
273 X_rev_budget_type_code := l_rev_budget_type_code;
274
275 -- If any exception then raise it to the calling pl/sql block
276 --
277 IF g1_debug_mode = 'Y' THEN
278 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.get_budget_amount: ');
279 END IF;
280 EXCEPTION
281 WHEN invalid_cost_budget_code THEN
282 status := pa_billing_values.get_message('INVALID_COST_BUDGET_TYPE');
283 l_status := 1;
284 RAISE_APPLICATION_ERROR(-20101,status);
285 WHEN invalid_rev_budget_code THEN
286 status := pa_billing_values.get_message('INVALID_REV_BUDGET_TYPE');
287 l_status := 2;
288 RAISE_APPLICATION_ERROR(-20101,status);
289 WHEN rev_budget_not_baselined THEN
290 status := pa_billing_values.get_message('REV_BUDGET_NOT_BASELINED');
291 l_status := 3;
292 RAISE_APPLICATION_ERROR(-20101,status);
293 WHEN cost_budget_not_baselined THEN
294 status := pa_billing_values.get_message('COST_BUDGET_NOT_BASELINED');
295 l_status := 4;
296 RAISE_APPLICATION_ERROR(-20101,status);
297 WHEN OTHERS THEN
298 status := substr(SQLERRM,1,240);
299 l_status := sqlcode;
300 RAISE;
301 END;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 -- DBMS_OUTPUT.PUT_LINE(status);
306 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
307 X2_cost_amount := NULL;
308 X2_revenue_amount := NULL;
309 X_cost_budget_type_code := NULL;
310 X_rev_budget_type_code := NULL;
311 ----
312
313 X_error_message := status;
314 X_status := l_status;
315
316 insert_message
317 (X_inserting_procedure_name =>'pa_billing_pub.get_budget_amount',
318 X_attribute1 => l_cost_budget_type_code,
319 X_attribute2 => l_rev_budget_type_code,
320 X_message => status,
321 X_error_message=>err_msg,
322 X_status=>err_status);
323
324 IF (l_status < 0 OR NVL(err_status,0) <0) THEN
325 RAISE;
326 END IF;
327
328 END get_budget_amount;
329
330 PROCEDURE get_amount( X_project_id NUMBER,
331 X_request_id NUMBER,
332 X_calling_process VARCHAR2,
333 X_calling_place VARCHAR2 DEFAULT NULL,
334 X_which_amount VARCHAR2 DEFAULT 'R',
335 X_amount OUT NOCOPY NUMBER,
336 X_top_task_id NUMBER DEFAULT NULL,
337 X_system_linkage VARCHAR2 DEFAULT NULL,
338 X_cost_base VARCHAR2 DEFAULT NULL,
339 X_CP_structure VARCHAR2 DEFAULT NULL,
340 X_CB_type VARCHAR2 DEFAULT NULL) IS
341
342
343 ----------------------------
344 -- LOCAL CURSOR DECLARATION
345 --
346 total_amount REAL;
347
348 CURSOR ByRequest IS
349 SELECT ei.expenditure_item_id eid,
350 decode( X_which_amount, 'I', nvl(rdl.bill_amount,0),
351 'R', nvl(rdl.amount,0),
352 'C', nvl(ei.raw_cost,0),
353 'B', nvl(ei.burden_cost,0), nvl(ei.raw_cost,0)) amt
354 FROM pa_tasks t,
355 pa_expenditure_items_all ei,
356 pa_expenditure_items_all ei2,
357 pa_cust_rev_dist_lines rdl
358 WHERE
359 ei.task_id = t.task_Id
360 AND (t.top_task_id = X_top_task_id
361 OR X_top_task_id IS NULL)
362 AND rdl.project_id between nvl(X_project_id, 0)
363 and nvl(X_project_id, 9999999999)
364 AND (ei.system_linkage_function||'' = X_system_linkage
365 OR X_system_linkage IS NULL)
366 AND rdl.request_id = X_request_id
367 AND (EXISTS
368 (select '1'
369 from pa_cost_base_exp_types cb
370 where cb.expenditure_type = ei.expenditure_type
371 and cb.cost_base = X_cost_base
372 and cb.cost_plus_structure = X_CP_structure
373 and cb.cost_base_type = X_CB_type)
374 OR
375 ( X_cost_base IS NULL
376 OR X_CP_structure IS NULL
377 OR X_CB_type IS NULL))
378 AND ei.expenditure_item_id = rdl.expenditure_item_id
379 AND rdl.line_num = decode( X_which_amount, 'C', 1,
380 'B', 1, rdl.line_num)
381 AND ei.adjusted_expenditure_item_id = ei2.expenditure_item_id (+)
382 AND ((X_calling_place = 'ADJ'
383 AND (rdl.line_num_reversed IS NOT NULL
384 OR (ei.adjusted_expenditure_item_id IS NOT NULL
385 and ei2.request_id <> X_request_id)))
386 OR (X_calling_place = 'REG'
387 AND (rdl.line_num_reversed IS NULL
388 and rdl.reversed_flag IS NULL
389 and (ei.adjusted_expenditure_item_id IS NULL
390 or ei2.request_id = X_request_id))));
391
392 -- For explanation of adjustment logic refer to explanation
393 -- under function rdl_amount.
394 -- In the ADJ section get all adjusting ei's, except those that
395 -- are adjusting other ei's which are also being processed in
396 -- this same run
397 -- In the regular section get all regular ei's and also all
398 -- adjusting ei's that are adjusting expenditure items being
399 -- processed in this same run
400
401 Rqst_rec ByRequest%ROWTYPE;
402
403
404 CURSOR ByRequestInv IS
405 SELECT ei.expenditure_item_id eid,
406 decode( X_which_amount, 'I', nvl(rdl.bill_amount,0),
407 'R', nvl(rdl.amount,0),
408 'C', nvl(ei.raw_cost,0),
409 'B', nvl(ei.burden_cost,0), nvl(ei.raw_cost,0)) amt
410 FROM pa_tasks t,
411 pa_expenditure_items_all ei2,
412 pa_expenditure_items_all ei,
413 pa_cust_rev_dist_lines rdl2,
414 pa_cust_rev_dist_lines rdl,
415 pa_draft_invoice_items pdii,
416 pa_draft_invoices pdi
417 WHERE
418 ei.task_id = t.task_Id
419 AND (t.top_task_id = X_top_task_id
420 OR X_top_task_id IS NULL)
421 AND pdi.project_id between nvl(X_project_id, 0)
422 and nvl(X_project_id, 9999999999)
423 AND (ei.system_linkage_function||'' = X_system_linkage
424 OR X_system_linkage IS NULL)
425 AND pdii.project_id = rdl.project_id
426 AND pdii.draft_invoice_num = rdl.draft_invoice_num
427 AND pdii.line_num = rdl.draft_invoice_item_line_num
428 AND pdii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
429 AND pdii.project_id = pdi.project_id
430 AND pdii.draft_invoice_num = pdi.draft_invoice_num
431 AND pdi.request_id = X_request_id
432 AND ei.adjusted_expenditure_item_id = ei2.expenditure_item_id (+)
433 AND ei2.expenditure_item_id = rdl2.expenditure_item_id (+)
434 AND (EXISTS
435 (select '1'
436 from pa_cost_base_exp_types cb
437 where cb.expenditure_type = ei.expenditure_type
438 and cb.cost_base = X_cost_base
439 and cb.cost_plus_structure = X_CP_structure
440 and cb.cost_base_type = X_CB_type)
441 OR
442 ( X_cost_base IS NULL
443 OR X_CP_structure IS NULL
444 OR X_CB_type IS NULL))
445 AND ei.expenditure_item_id = rdl.expenditure_item_id
446 AND rdl.line_num = decode( X_which_amount, 'C', 1,
447 'B', 1, rdl.line_num)
448 AND ((X_calling_place = 'ADJ'
449 AND (rdl.line_num_reversed IS NOT NULL
450 OR (ei.adjusted_expenditure_item_id IS NOT NULL
451 and rdl2.draft_invoice_num <> rdl.draft_invoice_num)))
452 OR (X_calling_place = 'REG'
453 AND (rdl.line_num_reversed IS NULL
454 and rdl.reversed_flag IS NULL
455 and (ei.adjusted_expenditure_item_id IS NULL
456 or rdl2.draft_invoice_num = rdl.draft_invoice_num))));
457
458 RqstInv_rec ByRequestInv%ROWTYPE;
459
460 CURSOR ByProject IS
461 SELECT ei.expenditure_item_id eid,
462 decode(X_which_amount, 'C', nvl(ei.raw_cost,0),
463 'B', nvl(ei.burden_cost,0),
464 nvl(ei.burden_cost,0)) amt,
465 decode(ei.adjusted_expenditure_item_id, NULL, 'N','Y') ei_adj
466 FROM pa_expenditure_items_all ei,
467 /* pa_expenditure_items_all ei2, commented for Bug#2499051*/
468 pa_tasks t
469 WHERE
470 ei.task_id = t.task_Id
471 AND (t.top_task_id = X_top_task_id
472 OR X_top_task_id IS NULL)
473 AND t.project_id = X_project_id
474 AND (ei.system_linkage_function = X_system_linkage
475 OR X_system_linkage IS NULL)
476 AND (EXISTS
477 (select '1'
478 from pa_cost_base_exp_types cb
479 where cb.expenditure_type = ei.expenditure_type
480 and cb.cost_base = X_cost_base
481 and cb.cost_plus_structure = X_CP_structure
482 and cb.cost_base_type = X_CB_type)
483 OR
484 ( X_cost_base IS NULL
485 OR X_CP_structure IS NULL
486 OR X_CB_type IS NULL))
487 AND (ei.request_id = X_request_id
488 OR X_request_id IS NULL);
489
490 Proj_rec ByProject%ROWTYPE;
491
492
493
494 BEGIN
495
496 IF g1_debug_mode = 'Y' THEN
497 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.get_amount: ');
498 END IF;
499 total_amount := 0;
500
501 IF (X_Request_id IS NULL) THEN -->No RqstId given
502
503 IF (X_which_amount = 'I' OR X_which_amount = 'R') THEN -->Want Inv/Rev amts
504 FOR Proj_rec IN ByProject LOOP
505 total_amount := total_amount +
506 nvl(pa_billing_amount.rdl_amount(X_which_amount,
507 Proj_rec.eid, X_calling_place, Proj_rec.ei_adj),0);
508 END LOOP;
509 ELSE -->Want cost amounts
510 FOR Proj_rec IN ByProject LOOP
511 total_amount := total_amount + nvl(Proj_rec.amt,0);
512 END LOOP;
513 END IF;
514
515 ELSE -->Request Id given
516 IF (X_calling_process = 'Invoice') THEN
517 FOR RqstInv_rec IN ByRequestInv LOOP
518 total_amount := total_amount + nvl(RqstInv_rec.amt,0);
519 END LOOP;
520
521 ELSE
522
523 FOR Rqst_rec IN ByRequest LOOP
524 total_amount := total_amount + nvl(Rqst_rec.amt,0);
525 END LOOP;
526
527 END IF;
528
529 END IF;
530 X_amount := pa_currency.round_currency_amt(total_amount);
531
532 IF g1_debug_mode = 'Y' THEN
533 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.get_amount: ');
534 END IF;
535 EXCEPTION
536 WHEN NO_DATA_FOUND THEN
537 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
538 RAISE;
539 WHEN OTHERS THEN
540 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
541 X_amount := NULL;
542 RAISE;
543 END get_amount;
544
545
546
547
548 PROCEDURE insert_message(X_inserting_procedure_name VARCHAR2,
549 X_message VARCHAR2,
550 X_attribute1 VARCHAR2 DEFAULT NULL,
551 X_attribute2 VARCHAR2 DEFAULT NULL,
552 X_attribute3 VARCHAR2 DEFAULT NULL,
553 X_attribute4 VARCHAR2 DEFAULT NULL,
554 X_attribute5 VARCHAR2 DEFAULT NULL,
555 X_attribute6 VARCHAR2 DEFAULT NULL,
556 X_attribute7 VARCHAR2 DEFAULT NULL,
557 X_attribute8 VARCHAR2 DEFAULT NULL,
558 X_attribute9 VARCHAR2 DEFAULT NULL,
559 X_attribute10 VARCHAR2 DEFAULT NULL,
560 X_attribute11 VARCHAR2 DEFAULT NULL,
561 X_attribute12 VARCHAR2 DEFAULT NULL,
562 X_attribute13 VARCHAR2 DEFAULT NULL,
563 X_attribute14 VARCHAR2 DEFAULT NULL,
564 X_attribute15 VARCHAR2 DEFAULT NULL,
565 X_error_message OUT NOCOPY VARCHAR2,
566 X_status OUT NOCOPY NUMBER) IS
567
568 x_last_updated_by NUMBER(15); --|
569 x_created_by NUMBER(15); --|
570 x_last_update_login NUMBER(15); --|Standard Who Columns
571 x_request_id NUMBER(15); --|
572 x_program_application_id NUMBER(15); --|
573 x_program_id NUMBER(15); --|
574 xo_line_num NUMBER(15);
575
576 BEGIN
577 IF g1_debug_mode = 'Y' THEN
578 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.insert_message: ');
579 END IF;
580 x_created_by := FND_GLOBAL.USER_ID;
581 x_last_updated_by := FND_GLOBAL.USER_ID;
582 x_last_update_login := FND_GLOBAL.LOGIN_ID;
583 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
584 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
585
586 X_status := 0;
587 X_error_message := NULL;
588
589 SELECT max(BM.line_num)
590 INTO xo_line_num
591 FROM PA_BILLING_MESSAGES BM
592 WHERE BM.project_id = pa_billing.GlobVars.ProjectId
593 AND nvl(BM.task_Id,0) = nvl(pa_billing.GlobVars.TaskId,0)
594 AND BM.calling_place = pa_billing.GlobVars.CallingPlace
595 AND BM.calling_process = pa_billing.GlobVars.CallingProcess
596 AND BM.request_id = pa_billing.GlobVars.ReqId;
597
598 IF (xo_line_num IS NULL) THEN
599 xo_line_num := 1;
600 ELSE xo_line_num := xo_line_num + 1;
601 END IF;
602
603 INSERT INTO PA_BILLING_MESSAGES
604 (inserting_procedure_name,
605 Billing_Assignment_Id,
606 Project_Id,
607 Task_Id,
608 calling_place,
609 calling_process,
610 request_id,
611 line_Num,
612 message,
613 creation_date,
614 created_by,
615 last_update_date,
616 last_updated_by,
617 last_update_login,
618 attribute1,
619 attribute2,
620 attribute3,
621 attribute4,
622 attribute5,
623 attribute6,
624 attribute7,
625 attribute8,
626 attribute9,
627 attribute10,
628 attribute11,
629 attribute12,
630 attribute13,
631 attribute14,
632 attribute15)
633 VALUES (
634 X_inserting_procedure_name,
635 pa_billing.GlobVars.BillingAssignmentId,
636 pa_billing.GlobVars.ProjectId,
637 pa_billing.GlobVars.TaskId,
638 pa_billing.GlobVars.CallingPlace,
639 pa_billing.GlobVars.CallingProcess,
640 pa_billing.GlobVars.ReqId,
641 xo_line_num,
642 X_message,
643 sysdate,
644 x_created_by,
645 sysdate,
646 x_last_updated_by,
647 x_last_update_login,
648 X_attribute1,
649 X_attribute2,
650 X_attribute3,
651 X_attribute4,
652 X_attribute5,
653 X_attribute6,
654 X_attribute7,
655 X_attribute8,
656 X_attribute9,
657 X_attribute10,
658 X_attribute11,
659 X_attribute12,
660 X_attribute13,
661 X_attribute14,
662 X_attribute15);
663
664 --commit;
665
666 IF g1_debug_mode = 'Y' THEN
667 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.insert_message: ');
668 END IF;
669 EXCEPTION
670 WHEN OTHERS THEN
671 -- DBMS_OUTPUT.PUT(SQLERRM);
672 X_status := sqlcode;
673 X_error_message := SQLERRM;
674 RAISE;
675 END insert_message;
676
677
678 -- Modified to add the new audit parameters , validations for calling place
679
680 PROCEDURE insert_event (X_rev_amt REAL DEFAULT NULL,
681 X_bill_amt REAL DEFAULT NULL,
682 X_project_id NUMBER DEFAULT NULL,
683 X_event_type VARCHAR2 DEFAULT NULL,
684 X_top_task_id NUMBER DEFAULT NULL,
685 X_organization_id NUMBER DEFAULT NULL,
686 X_completion_date DATE DEFAULT NULL,
687 X_event_description VARCHAR2 DEFAULT NULL,
688 X_event_num_reversed NUMBER DEFAULT NULL,
689 X_attribute_category VARCHAR2 DEFAULT NULL,
690 X_attribute1 VARCHAR2 DEFAULT NULL,
691 X_attribute2 VARCHAR2 DEFAULT NULL,
692 X_attribute3 VARCHAR2 DEFAULT NULL,
693 X_attribute4 VARCHAR2 DEFAULT NULL,
694 X_attribute5 VARCHAR2 DEFAULT NULL,
695 X_attribute6 VARCHAR2 DEFAULT NULL,
696 X_attribute7 VARCHAR2 DEFAULT NULL,
697 X_attribute8 VARCHAR2 DEFAULT NULL,
698 X_attribute9 VARCHAR2 DEFAULT NULL,
699 X_attribute10 VARCHAR2 DEFAULT NULL,
700 X_audit_amount1 IN NUMBER DEFAULT NULL,
701 X_audit_amount2 IN NUMBER DEFAULT NULL,
702 X_audit_amount3 IN NUMBER DEFAULT NULL,
703 X_audit_amount4 IN NUMBER DEFAULT NULL,
704 X_audit_amount5 IN NUMBER DEFAULT NULL,
705 X_audit_amount6 IN NUMBER DEFAULT NULL,
706 X_audit_amount7 IN NUMBER DEFAULT NULL,
707 X_audit_amount8 IN NUMBER DEFAULT NULL,
708 X_audit_amount9 IN NUMBER DEFAULT NULL,
709 X_audit_amount10 IN NUMBER DEFAULT NULL,
710 X_audit_cost_budget_type_code IN VARCHAR2 DEFAULT NULL,
711 X_audit_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
712 x_inventory_org_id IN NUMBER DEFAULT NULL,
713 x_inventory_item_id IN NUMBER DEFAULT NULL,
714 x_quantity_billed IN NUMBER DEFAULT NULL,
715 x_uom_code IN VARCHAR2 DEFAULT NULL,
716 x_unit_price IN NUMBER DEFAULT NULL,
717 x_reference1 IN VARCHAR2 DEFAULT NULL,
718 x_reference2 IN VARCHAR2 DEFAULT NULL,
719 x_reference3 IN VARCHAR2 DEFAULT NULL,
720 x_reference4 IN VARCHAR2 DEFAULT NULL,
721 x_reference5 IN VARCHAR2 DEFAULT NULL,
722 x_reference6 IN VARCHAR2 DEFAULT NULL,
723 x_reference7 IN VARCHAR2 DEFAULT NULL,
724 x_reference8 IN VARCHAR2 DEFAULT NULL,
725 x_reference9 IN VARCHAR2 DEFAULT NULL,
726 x_reference10 IN VARCHAR2 DEFAULT NULL,
727 X_txn_currency_code IN VARCHAR2 DEFAULT NULL, /* Added 20 columns for MCB2 */
728 X_project_rate_type IN VARCHAR2 DEFAULT NULL,
729 X_project_rate_date IN DATE DEFAULT NULL,
730 X_project_exchange_rate IN NUMBER DEFAULT NULL,
731 X_project_func_rate_type IN VARCHAR2 DEFAULT NULL,
732 X_project_func_rate_date IN DATE DEFAULT NULL,
733 X_project_func_exchange_rate IN NUMBER DEFAULT NULL,
734 X_funding_rate_type IN VARCHAR2 DEFAULT NULL,
735 X_funding_rate_date IN DATE DEFAULT NULL,
736 X_funding_exchange_rate IN NUMBER DEFAULT NULL,
737 X_zero_revenue_amount_flag IN VARCHAR2 DEFAULT NULL, /* Funding MRC Changes */
738 X_audit_cost_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
739 X_audit_rev_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
740 X_error_message OUT NOCOPY VARCHAR2,
741 X_status OUT NOCOPY NUMBER
742 ) IS
743
744 XD_bill_trans_bill_amt NUMBER(22,5); /* changed for MCB2 from rev/bill amount to trans bill/rev amount */
745 XD_bill_trans_rev_amt NUMBER(22,5);
746 XD_organization_id NUMBER(15);
747 XD_completion_date DATE;
748 XD_event_description VARCHAR2(240);
749 XD_event_type VARCHAR2(30);
750 event_num NUMBER(16);/*Increase size for bug 1742348*/
751 invalid_id EXCEPTION;
752 event_type_error EXCEPTION;
753 null_event_type_error EXCEPTION;
754 invalid_project_event EXCEPTION;
755 mandatory_prm_missing EXCEPTION;
756 zero_amounts EXCEPTION;
757 no_orig_event EXCEPTION;
758 invalid_calling_place EXCEPTION;
759 invalid_invent_id EXCEPTION;
760
761 l_status NUMBER;
762 err_status NUMBER;
763 err_message VARCHAR2(240);
764
765 /* MCB related changes as of 08/21/2001 by skannoji */
766 l_project_id pa_projects_all.project_id%TYPE;
767 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%TYPE;
768 l_baseline_funding_flag pa_projects_all.baseline_funding_flag%TYPE;
769 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
770 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
771 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
772 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
773 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
774 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
775 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
776 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
777 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
778 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
779 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
780 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
781 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
782 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
783 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
784 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
785 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
786 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
787 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
788 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
789 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
790 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
791 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
792 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
793 l_return_status VARCHAR2(30);
794 l_msg_count NUMBER;
795 l_msg_data VARCHAR2(30);
796 l_found VARCHAR2(30);
797
798 l_proj_exch_rate_not_passd EXCEPTION;
799 l_func_exch_rate_not_passd EXCEPTION;
800 l_fund_exch_rate_not_passd EXCEPTION;
801 l_proj_invalid_rate_type EXCEPTION;
802 l_func_invalid_rate_type EXCEPTION;
803 l_fund_invalid_rate_type EXCEPTION;
804 l_invalid_currency EXCEPTION;
805 /* Till Here */
806
807 BEGIN
808 BEGIN
809 IF g1_debug_mode = 'Y' THEN
810 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.insert_event:' );
811 END IF;
812 -- Assigning who columns for insertion into PA_EVENTS.
813
814 created_by := FND_GLOBAL.USER_ID;
815 last_updated_by := FND_GLOBAL.USER_ID;
816 last_update_login := FND_GLOBAL.LOGIN_ID;
817 program_application_id := FND_GLOBAL.PROG_APPL_ID;
818 program_id := FND_GLOBAL.CONC_PROGRAM_ID;
819
820 X_status := 0;
821 X_error_message := NULL;
822 -- Validate Mandatory Parameters
823 IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
824 pa_billing.GlobVars.ReqId IS NULL OR
825 pa_billing.GlobVars.CallingPlace IS NULL OR
826 pa_billing.GlobVars.CallingProcess IS NULL) THEN
827 raise mandatory_prm_missing;
828 END IF;
829
830 /* The following logic has been added for MCB2 functionality */
831 l_project_id := nvl(X_project_id, pa_billing.GlobVars.ProjectId);
832 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
833 p_project_id => l_project_id,
834 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
835 x_baseline_funding_flag => l_baseline_funding_flag,
836 x_revproc_currency_code => l_revproc_currency_code,
837 x_invproc_currency_type => l_invproc_currency_type,
838 x_invproc_currency_code => l_invproc_currency_code,
839 x_project_currency_code => l_project_currency_code,
840 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
841 x_project_bil_rate_type => l_project_bil_rate_type,
842 x_project_bil_rate_date => l_project_bil_rate_date,
843 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
844 x_projfunc_currency_code => l_projfunc_currency_code,
845 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
846 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
847 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
848 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
849 x_funding_rate_date_code => l_funding_rate_date_code,
850 x_funding_rate_type => l_funding_rate_type,
851 x_funding_rate_date => l_funding_rate_date,
852 x_funding_exchange_rate => l_funding_exchange_rate,
853 x_return_status => l_return_status,
854 x_msg_count => l_msg_count,
855 x_msg_data => l_msg_data);
856
857 l_txn_currency_code := NVL(x_txn_currency_code,l_projfunc_currency_code);
858 BEGIN
859 /* Validating Currency code */
860 SELECT 'Y'
861 INTO l_found
862 FROM fnd_currencies /* Bug 4352166 Changed vl to base table*/
863 WHERE currency_code = l_txn_currency_code
864 AND TRUNC(SYSDATE) BETWEEN DECODE (TRUNC(start_date_active), NULL, TRUNC(SYSDATE),
865 TRUNC(start_date_active))
866 AND DECODE(TRUNC(end_date_active), NULL, TRUNC(SYSDATE),TRUNC(end_date_active));
867 EXCEPTION
868 WHEN OTHERS THEN
869 RAISE l_invalid_currency;
870 END;
871
872 /* Rounding the transaction amount upto the precision of transaction currency for MCB2 */
873 XD_bill_trans_rev_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_rev_amt, 0),l_txn_currency_code);
874 IF g1_debug_mode = 'Y' THEN
875 PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event: rev amt'||to_char(XD_bill_trans_rev_amt));
876 END IF;
877 XD_bill_trans_bill_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_bill_amt,0),l_txn_currency_code);
878 IF g1_debug_mode = 'Y' THEN
879 PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event: inv amt'||to_char(XD_bill_trans_bill_amt));
880 END IF;
881 /* The following amounts have been commented for MCB2, the above amounts are satisfying the same requirement */
882 /* XD_bill_trans_rev_amt := (NVL(X_rev_amt, 0));
883 XD_bill_trans_bill_amt := (NVL(X_bill_amt,0)); */
884
885 -- Get defaults for other non-mandatory parameters
886 XD_completion_date := nvl(X_completion_date,
887 to_date(pa_billing.GlobVars.AccrueThruDate,'YYYY/MM/DD'));
888
889
890 -- Added check to prevent event creation from other calling places like 'PRE' 'POST'
891 -- 'DEL' etc.
892 --
893 IF (pa_billing.GlobVars.CallingPlace NOT IN ('REG','ADJ','POST-REG')) THEN
894 raise invalid_calling_place;
895 END IF;
896
897
898 IF (X_organization_id IS NULL) THEN
899 XD_organization_id := pa_billing_values.get_dflt_org(
900 nvl(X_project_id,
901 pa_billing.GlobVars.ProjectId),
902 nvl(X_top_task_id,
903 pa_billing.GlobVars.TaskId));
904 ELSE
905 XD_organization_id := X_organization_id;
906 END IF;
907
908 IF (X_event_description IS NULL OR
909 X_event_type IS NULL) THEN
910 pa_billing_values.get_dflt_desc(pa_billing.GlobVars.BillingAssignmentId,
911 XD_event_type, XD_event_description);
912 XD_event_description := nvl(X_event_description,
913 XD_event_description);
914 XD_event_type := nvl(X_event_type, XD_event_type);
915
916 IF (XD_event_type IS NULL) THEN
917 RAISE null_event_type_error;
918 END IF;
919 ELSE
920 XD_event_description := X_event_description;
921 XD_event_type := X_event_type;
922 END IF;
923
924
925 -- Validate Id's
926
927 IF (pa_billing_validate.valid_proj_task_extn(
928 nvl(X_project_id, pa_billing.GlobVars.ProjectId),
929 nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
930 pa_billing.GlobVars.BillingAssignmentId) AND
931 pa_billing_validate.valid_organization(XD_organization_id)) THEN
932 NULL;
933 ELSE
934 RAISE INVALID_ID;
935 END IF;
936
937
938
939 -- Validate funding level
940
941 IF (nvl(X_top_task_id, pa_billing.GlobVars.TaskId) IS NULL) THEN
942 IF (pa_billing_values.funding_level(nvl(X_project_id,
943 pa_billing.GlobVars.ProjectId))
944 <> 'PROJECT') THEN
945 RAISE invalid_project_event;
946 END IF;
947 END IF;
948
949 -- Funding MRC Changes added the flag X_zero_revenue_amount_flag
950 -- If revenue amount is zero and X_zero_revenue_amount_flag = 'Y' is a valid case
951 -- should not raise the exception. (Create zero dollar revenue event)
952
953 -- Validate amounts based on which process is calling.
954 IF ((((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N')) AND
955 (XD_bill_trans_bill_amt = 0)) OR
956 ((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N') AND
957 pa_billing.GlobVars.CallingProcess = 'Revenue') OR
958 ((XD_bill_trans_bill_amt = 0) AND
959 pa_billing.GlobVars.CallingProcess = 'Invoice')) THEN
960 RAISE zero_amounts;
961 END IF;
962
963 /* Modified the below condition for Bug 9154825 */
964 -- Check original event num for the ADJ automatic events.
965 IF (PA_BILLING.GetCallPlace = 'ADJ' AND
966 (pa_billing.GlobVars.CallingProcess = 'Invoice' OR pa_billing.GlobVars.CallingProcess = 'Revenue') AND
967 nvl(X_event_num_reversed, 0) = 0) THEN
968 RAISE no_orig_event;
969 END IF;
970
971 event_num := pa_billing_seq.next_eventnum(
972 nvl(X_project_id, pa_billing.GlobVars.ProjectId),
973 nvl(X_top_task_id, pa_billing.GlobVars.TaskId));
974
975
976 /* Adding validation of newly added columns in event table for project contract integration */
977 -- Validating inventory_org_id and inventory_item_id
978
979 IF (x_inventory_org_id IS NOT NULL) THEN
980 IF (pa_billing_validate.valid_organization(x_inventory_org_id)) THEN
981 NULL;
982 ELSE
983 RAISE INVALID_INVENT_ID;
984 END IF;
985 END IF;
986
987 IF (x_inventory_item_id IS NOT NULL) THEN
988 DECLARE
989 l_dummy varchar2(30);
990 BEGIN
991 SELECT 'Valid item'
992 INTO l_dummy
993 FROM mtl_item_flexfields
994 WHERE inventory_item_id = x_inventory_item_id;
995
996 EXCEPTION
997 WHEN NO_DATA_FOUND THEN
998 RAISE INVALID_INVENT_ID;
999 WHEN TOO_MANY_ROWS THEN
1000 null;
1001 END;
1002 END IF;
1003
1004 /* MCB2: The following code have benn added to populate the conversion attributes */
1005
1006 IF ( l_txn_currency_code <> l_projfunc_currency_code ) THEN
1007 l_found := NULL;
1008 IF ( X_project_func_rate_type IS NOT NULL) THEN
1009 BEGIN
1010 SELECT 'found'
1011 INTO l_found
1012 FROM ( SELECT conversion_type, user_conversion_type
1013 FROM pa_conversion_types_v
1014 WHERE conversion_type <>'User'
1015 AND (pa_multi_currency.is_user_rate_type_allowed(
1016 l_txn_currency_code,
1017 l_projfunc_currency_code,
1018 DECODE(l_projfunc_bil_rate_date_code,
1019 'PA_INVOICE_DATE', NVL(X_project_func_rate_date,l_projfunc_bil_rate_date),
1020 'FIXED_DATE', NVL(X_project_func_rate_date,l_projfunc_bil_rate_date)))= 'N')
1021 UNION
1022 SELECT conversion_type, user_conversion_type
1023 FROM pa_conversion_types_v
1024 WHERE pa_multi_currency.is_user_rate_type_allowed(
1025 l_txn_currency_code,
1026 l_projfunc_currency_code,
1027 DECODE(l_projfunc_bil_rate_date_code,
1028 'PA_INVOICE_DATE',NVL(X_project_func_rate_date,l_projfunc_bil_rate_date),
1029 'FIXED_DATE',NVL(X_project_func_rate_date,l_projfunc_bil_rate_date) ))= 'Y')
1030 WHERE DECODE(conversion_type,X_project_func_rate_type,'Y','N') = 'Y';
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033 RAISE l_func_invalid_rate_type;
1034 END;
1035 END IF;
1036
1037 IF ( X_project_func_rate_type IS NULL AND l_projfunc_bil_rate_type = 'User') THEN
1038 l_projfunc_bil_rate_date := NULL;
1039 IF ( X_project_func_exchange_rate IS NULL ) THEN
1040 null;
1041 ELSE
1042 l_projfunc_bil_exchange_rate := X_project_func_exchange_rate;
1043 END IF;
1044 ELSIF (X_project_func_rate_type = 'User') THEN
1045 l_projfunc_bil_rate_date := NULL;
1046 l_projfunc_bil_rate_type := X_project_func_rate_type;
1047 IF ( X_project_func_exchange_rate IS NULL ) THEN
1048 RAISE l_func_exch_rate_not_passd;
1049 ELSE
1050 l_projfunc_bil_exchange_rate := X_project_func_exchange_rate;
1051 END IF;
1052 ELSIF ( X_project_func_rate_type <> 'User' OR l_projfunc_bil_rate_type <> 'User') THEN
1053 l_projfunc_bil_rate_type := NVL(X_project_func_rate_type,l_projfunc_bil_rate_type);
1054 l_projfunc_bil_exchange_rate := NULL;
1055 END IF;
1056
1057
1058 IF ( l_projfunc_bil_rate_type <> 'User' AND l_projfunc_bil_rate_date_code = 'FIXED_DATE'
1059 AND X_project_func_rate_date IS NOT NULL ) THEN
1060 l_projfunc_bil_rate_date := X_project_func_rate_date;
1061 ELSIF (l_projfunc_bil_rate_type <> 'User' AND l_projfunc_bil_rate_date_code = 'PA_INVOICE_DATE'
1062 AND X_project_func_rate_date IS NOT NULL ) THEN
1063 l_projfunc_bil_rate_date := X_project_func_rate_date;
1064 END IF;
1065 END IF;
1066
1067 /* Project currency code logic */
1068 IF ( l_txn_currency_code <> l_project_currency_code ) THEN
1069 l_found := NULL;
1070 IF ( X_project_rate_type IS NOT NULL) THEN
1071 BEGIN
1072 SELECT 'found'
1073 INTO l_found
1074 FROM ( SELECT conversion_type, user_conversion_type
1075 FROM pa_conversion_types_v
1076 WHERE conversion_type <>'User'
1077 AND (pa_multi_currency.is_user_rate_type_allowed(
1078 l_txn_currency_code,
1079 l_project_currency_code,
1080 DECODE(l_project_bil_rate_date_code,
1081 'PA_INVOICE_DATE', NVL(X_project_rate_date,l_project_bil_rate_date),
1082 'FIXED_DATE', NVL(X_project_rate_date,l_project_bil_rate_date)))= 'N')
1083 UNION
1084 SELECT conversion_type, user_conversion_type
1085 FROM pa_conversion_types_v
1086 WHERE pa_multi_currency.is_user_rate_type_allowed(
1087 l_txn_currency_code,
1088 l_project_currency_code,
1089 DECODE(l_project_bil_rate_date_code,
1090 'PA_INVOICE_DATE',NVL(X_project_rate_date,l_project_bil_rate_date),
1091 'FIXED_DATE',NVL(X_project_rate_date,l_project_bil_rate_date) ))= 'Y')
1092 WHERE DECODE(conversion_type,X_project_rate_type,'Y','N') = 'Y';
1093 EXCEPTION
1094 WHEN NO_DATA_FOUND THEN
1095 RAISE l_proj_invalid_rate_type;
1096 END;
1097 END IF;
1098
1099 IF ( X_project_rate_type IS NULL AND l_project_bil_rate_type = 'User') THEN
1100 l_project_bil_rate_date := NULL;
1101 IF ( X_project_exchange_rate IS NULL ) THEN
1102 null;
1103 ELSE
1104 l_project_bil_exchange_rate := X_project_exchange_rate;
1105 END IF;
1106 ELSIF (X_project_rate_type = 'User') THEN
1107 l_project_bil_rate_date := NULL;
1108 l_project_bil_rate_type := X_project_rate_type;
1109 IF ( X_project_exchange_rate IS NULL ) THEN
1110 RAISE l_proj_exch_rate_not_passd;
1111 ELSE
1112 l_project_bil_exchange_rate := X_project_exchange_rate;
1113 END IF;
1114 ELSIF ( X_project_rate_type <> 'User' OR l_project_bil_rate_type <> 'User') THEN
1115 l_project_bil_rate_type := NVL(X_project_rate_type,l_project_bil_rate_type);
1116 l_project_bil_exchange_rate := NULL;
1117 END IF;
1118 IF ( l_project_bil_rate_type <> 'User' AND l_project_bil_rate_date_code = 'FIXED_DATE'
1119 AND X_project_rate_date IS NOT NULL ) THEN
1120 l_project_bil_rate_date := X_project_rate_date;
1121 ELSIF (l_project_bil_rate_type <> 'User' AND l_project_bil_rate_date_code = 'PA_INVOICE_DATE'
1122 AND X_project_rate_date IS NOT NULL ) THEN
1123 l_project_bil_rate_date := X_project_rate_date;
1124 END IF;
1125 END IF;
1126
1127 /* Funding Currency code logic */
1128 IF ( l_multi_currency_billing_flag = 'Y' OR l_multi_currency_billing_flag = 'y') THEN
1129 l_found := NULL;
1130 IF ( X_funding_rate_type IS NOT NULL) THEN
1131 BEGIN
1132 SELECT 'found'
1133 INTO l_found
1134 FROM ( SELECT conversion_type, user_conversion_type
1135 FROM pa_conversion_types_v)
1136 WHERE DECODE(conversion_type,X_funding_rate_type,'Y','N') = 'Y';
1137 EXCEPTION
1138 WHEN NO_DATA_FOUND THEN
1139 RAISE l_fund_invalid_rate_type;
1140 END;
1141 END IF;
1142
1143 IF ( X_funding_rate_type IS NULL AND l_funding_rate_type = 'User') THEN
1144 l_funding_rate_date := NULL;
1145 IF ( X_funding_exchange_rate IS NULL ) THEN
1146 null;
1147 ELSE
1148 l_funding_exchange_rate := X_funding_exchange_rate;
1149 END IF;
1150 ELSIF (X_funding_rate_type = 'User') THEN
1151 l_funding_rate_date := NULL;
1152 l_funding_rate_type := X_funding_rate_type;
1153 IF ( X_funding_exchange_rate IS NULL ) THEN
1154 RAISE l_fund_exch_rate_not_passd;
1155 ELSE
1156 l_funding_exchange_rate := X_funding_exchange_rate;
1157 END IF;
1158 ELSIF ( X_funding_rate_type <> 'User' OR l_funding_rate_type <> 'User') THEN
1159 l_funding_rate_type := NVL(X_funding_rate_type,l_funding_rate_type);
1160 l_funding_exchange_rate := NULL;
1161 END IF;
1162 IF ( l_funding_rate_type <> 'User' AND l_funding_rate_date_code = 'FIXED_DATE'
1163 AND X_funding_rate_date IS NOT NULL ) THEN
1164 l_funding_rate_date := X_funding_rate_date;
1165 ELSIF (l_funding_rate_type <> 'User' AND l_funding_rate_date_code = 'PA_INVOICE_DATE'
1166 AND X_funding_rate_date IS NOT NULL ) THEN
1167 l_funding_rate_date := X_funding_rate_date;
1168 END IF;
1169 END IF;
1170
1171 /* Added for Bug3068864 */
1172 IF ( l_txn_currency_code = l_projfunc_currency_code ) THEN
1173 l_projfunc_bil_rate_type := Null;
1174 l_projfunc_bil_rate_date := Null;
1175 l_projfunc_bil_exchange_rate := Null;
1176 ELSIF (l_txn_currency_code = l_project_currency_code ) THEN
1177 l_project_bil_rate_type := Null;
1178 l_project_bil_rate_date := Null;
1179 l_project_bil_exchange_rate := Null;
1180 END IF;
1181 /* till here for Bug3068864 */
1182
1183 /* Populating Invoice attributes */
1184
1185 IF ( l_invproc_currency_code = l_projfunc_currency_code ) THEN
1186 l_invproc_currency_code := l_projfunc_currency_code;
1187 l_invproc_rate_type := l_projfunc_bil_rate_type;
1188 l_invproc_rate_date := l_projfunc_bil_rate_date;
1189 l_invproc_exchange_rate := l_projfunc_bil_exchange_rate;
1190 ELSIF (l_invproc_currency_code = l_project_currency_code ) THEN
1191 l_invproc_currency_code := l_project_currency_code;
1192 l_invproc_rate_type := l_project_bil_rate_type;
1193 l_invproc_rate_date := l_project_bil_rate_date;
1194 l_invproc_exchange_rate := l_project_bil_exchange_rate;
1195 ELSE
1196 l_invproc_currency_code := '';
1197 l_invproc_rate_type := l_funding_rate_type;
1198 l_invproc_rate_date := l_funding_rate_date;
1199 l_invproc_exchange_rate := l_funding_exchange_rate;
1200 END IF;
1201
1202 /* Populating Revenue attributes */
1203
1204 IF ( l_revproc_currency_code = l_projfunc_currency_code ) THEN
1205 l_revproc_currency_code := l_projfunc_currency_code;
1206 l_revproc_rate_type := l_projfunc_bil_rate_type;
1207 l_revproc_rate_date := l_projfunc_bil_rate_date;
1208 l_revproc_exchange_rate := l_projfunc_bil_exchange_rate;
1209 END IF;
1210
1211 /* MCB2: Removed Revenue amount and Bill amount because,these amounts are being used only for transactions */
1212 IF (pa_billing_validate.automatic_event(XD_event_type)) THEN
1213 insert into pa_events
1214 (PROJECT_ID, TASK_ID, ORGANIZATION_ID, EVENT_NUM, EVENT_TYPE, -- 1
1215 REVENUE_AMOUNT,BILL_AMOUNT,COMPLETION_DATE, REQUEST_ID, -- 2
1216 DESCRIPTION, BILL_HOLD_FLAG, REV_DIST_REJECTION_CODE, -- 3
1217 REVENUE_DISTRIBUTED_FLAG, PROGRAM_APPLICATION_ID, PROGRAM_ID, -- 4
1218 PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, -- 5
1219 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, ATTRIBUTE_CATEGORY, -- 6
1220 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, -- 7
1221 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, -- 8
1222 BILLING_ASSIGNMENT_ID, calling_place, calling_process, -- 9
1223 EVENT_NUM_REVERSED, -- 10
1224 AUDIT_AMOUNT1,
1225 AUDIT_AMOUNT2,
1226 AUDIT_AMOUNT3,
1227 AUDIT_AMOUNT4,
1228 AUDIT_AMOUNT5,
1229 AUDIT_AMOUNT6,
1230 AUDIT_AMOUNT7,
1231 AUDIT_AMOUNT8,
1232 AUDIT_AMOUNT9,
1233 AUDIT_AMOUNT10,
1234 AUDIT_COST_BUDGET_TYPE_CODE,
1235 AUDIT_REV_BUDGET_TYPE_CODE,
1236 EVENT_ID,
1237 INVENTORY_ORG_ID,
1238 INVENTORY_ITEM_ID,
1239 QUANTITY_BILLED,
1240 UOM_CODE,
1241 UNIT_PRICE,
1242 REFERENCE1,
1243 REFERENCE2,
1244 REFERENCE3,
1245 REFERENCE4,
1246 REFERENCE5,
1247 REFERENCE6,
1248 REFERENCE7,
1249 REFERENCE8,
1250 REFERENCE9,
1251 REFERENCE10,
1252 BILL_TRANS_CURRENCY_CODE, /* These 22 columns have been added for MCB2 */
1253 BILL_TRANS_REV_AMOUNT,
1254 BILL_TRANS_BILL_AMOUNT,
1255 PROJECT_CURRENCY_CODE,
1256 PROJECT_RATE_TYPE,
1257 PROJECT_RATE_DATE,
1258 PROJECT_EXCHANGE_RATE,
1259 PROJFUNC_CURRENCY_CODE,
1260 PROJFUNC_RATE_TYPE,
1261 PROJFUNC_RATE_DATE,
1262 PROJFUNC_EXCHANGE_RATE,
1263 FUNDING_RATE_TYPE,
1264 FUNDING_RATE_DATE,
1265 FUNDING_EXCHANGE_RATE,
1266 INVPROC_CURRENCY_CODE,
1267 INVPROC_RATE_TYPE,
1268 INVPROC_RATE_DATE,
1269 INVPROC_EXCHANGE_RATE,
1270 REVPROC_CURRENCY_CODE,
1271 REVPROC_RATE_TYPE,
1272 REVPROC_RATE_DATE,
1273 REVPROC_EXCHANGE_RATE,
1274 ZERO_REVENUE_AMOUNT_FLAG, /* Funding MRC Changes */
1275 AUDIT_COST_PLAN_TYPE_ID, /* Added for Fin Plan impact */
1276 AUDIT_REV_PLAN_TYPE_ID /* Added for Fin Plan impact */
1277 )
1278 values
1279 (nvl(X_project_id, pa_billing.GlobVars.ProjectId), nvl(X_top_task_id,pa_billing.GlobVars.TaskId),
1280 XD_organization_id, event_num, XD_event_type, -- 1
1281 0,0,XD_completion_date, pa_billing.GlobVars.ReqId, -- 2
1282 XD_event_description, 'N', NULL, -- 3
1283 'N', program_application_id,program_id, -- 4
1284 sysdate, sysdate, nvl(last_updated_by,0), -- 5
1285 sysdate, nvl(created_by,0), nvl(last_update_login,0),
1286 X_attribute_category, -- 6
1287 X_attribute1, X_attribute2, X_attribute3, X_attribute4,
1288 X_attribute5, -- 7
1289 X_attribute6, X_attribute7, X_attribute8, X_attribute9,
1290 X_attribute10, -- 8
1291 pa_billing.GlobVars.BillingAssignmentId, pa_billing.GlobVars.CallingPlace,
1292 pa_billing.GlobVars.CallingProcess, -- 9
1293 X_event_num_reversed, -- 10
1294 pa_currency.round_currency_amt(X_audit_amount1),
1295 pa_currency.round_currency_amt(X_audit_amount2),
1296 pa_currency.round_currency_amt(X_audit_amount3),
1297 pa_currency.round_currency_amt(X_audit_amount4),
1298 pa_currency.round_currency_amt(X_audit_amount5),
1299 pa_currency.round_currency_amt(X_audit_amount6),
1300 pa_currency.round_currency_amt(X_audit_amount7),
1301 pa_currency.round_currency_amt(X_audit_amount8),
1302 pa_currency.round_currency_amt(X_audit_amount9),
1303 pa_currency.round_currency_amt(X_audit_amount10),
1304 X_audit_cost_budget_type_code,
1305 X_audit_rev_budget_type_code,
1306 pa_events_s.nextval,
1307 x_inventory_org_id,
1308 x_inventory_item_id,
1309 x_quantity_billed,
1310 x_uom_code,
1311 x_unit_price,
1312 x_reference1,
1313 x_reference2,
1314 x_reference3,
1315 x_reference4,
1316 x_reference5,
1317 x_reference6,
1318 x_reference7,
1319 x_reference8,
1320 x_reference9,
1321 x_reference10,
1322 l_txn_currency_code,
1323 XD_bill_trans_rev_amt,
1324 XD_bill_trans_bill_amt,
1325 l_project_currency_code,
1326 l_project_bil_rate_type,
1327 l_project_bil_rate_date,
1328 l_project_bil_exchange_rate,
1329 l_projfunc_currency_code,
1330 l_projfunc_bil_rate_type,
1331 l_projfunc_bil_rate_date,
1332 l_projfunc_bil_exchange_rate,
1333 l_funding_rate_type,
1334 l_funding_rate_date,
1335 l_funding_exchange_rate,
1336 l_invproc_currency_code,
1337 l_invproc_rate_type,
1338 l_invproc_rate_date,
1339 l_invproc_exchange_rate,
1340 l_revproc_currency_code,
1341 l_revproc_rate_type,
1342 l_revproc_rate_date,
1343 l_revproc_exchange_rate,
1344 NVL(X_zero_revenue_amount_flag, 'N'),
1345 X_audit_cost_plan_type_id,
1346 X_audit_rev_plan_type_id
1347 );
1348 ELSE RAISE EVENT_TYPE_ERROR;
1349 END IF;
1350 -- COMMIT;
1351
1352 IF g1_debug_mode = 'Y' THEN
1353 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.insert_events: ');
1354 END IF;
1355 EXCEPTION
1356 WHEN mandatory_prm_missing THEN
1357 status := pa_billing_values.get_message('MANDATORY_PRM_MISSING');
1358 l_status := 1;
1359 RAISE;
1360 WHEN invalid_id THEN
1361 status := pa_billing_values.get_message('INVALID_ID');
1362 l_status := 2;
1363 RAISE;
1364 WHEN invalid_project_event THEN
1365 status := pa_billing_values.get_message('INVALID_PROJECT_EVENT');
1366 l_status := 3;
1367 RAISE;
1368 /*WHEN event_type_error THEN commenting this for bug 3492506
1369 status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
1370 RAISE;*/
1371 WHEN null_event_type_error THEN
1372 status := pa_billing_values.get_message('NULL_EVENT_TYPE_ERROR');
1373 l_status := 4;
1374 RAISE;
1375 WHEN zero_amounts THEN
1376 status := pa_billing_values.get_message('ZERO_AMOUNTS');
1377 l_status := 5;
1378 RAISE;
1379 WHEN no_orig_event THEN
1380 status := pa_billing_values.get_message('NO_ORIG_EVENT');
1381 l_status := 6;
1382 RAISE;
1383 WHEN invalid_calling_place THEN
1384 status := pa_billing_values.get_message('INVALID_CALLING_PLACE');
1385 l_status := 7;
1386 RAISE;
1387 WHEN invalid_invent_id THEN
1388 status := pa_billing_values.get_message('INVALID_INVENT_ID');
1389 l_status := 8;
1390 RAISE;
1391 WHEN l_func_exch_rate_not_passd THEN /* Added for MCB2 */
1392 status := pa_billing_values.get_message('PA_FUNC_EXCH_RATE_NOT_PASSD');
1393 l_status := 9;
1394 RAISE;
1395 WHEN l_proj_exch_rate_not_passd THEN /* Added for MCB2 */
1396 status := pa_billing_values.get_message('PA_PROJ_EXCH_RATE_NOT_PASSD');
1397 l_status := 10;
1398 RAISE;
1399 WHEN l_fund_exch_rate_not_passd THEN /* Added for MCB2 */
1400 status := pa_billing_values.get_message('PA_FUND_EXCH_RATE_NOT_PASSD');
1401 l_status := 11;
1402 RAISE;
1403 WHEN l_func_invalid_rate_type THEN /* Added for MCB2 */
1404 status := pa_billing_values.get_message('PA_FUNC_INVALID_RATE_TYPE');
1405 l_status := 12;
1406 RAISE;
1407 WHEN l_proj_invalid_rate_type THEN /* Added for MCB2 */
1408 status := pa_billing_values.get_message('PA_PROJ_INVALID_RATE_TYPE');
1409 l_status := 13;
1410 RAISE;
1411 WHEN l_fund_invalid_rate_type THEN /* Added for MCB2 */
1412 status := pa_billing_values.get_message('PA_FUND_INVALID_RATE_TYPE');
1413 l_status := 14;
1414 RAISE;
1415 WHEN l_invalid_currency THEN /* Added for MCB2 */
1416 status := pa_billing_values.get_message('PA_CURR_NOT_VALID_BC');
1417 l_status := 15;
1418 RAISE;
1419 WHEN event_type_error THEN /* adding this here for bug 3492506 */
1420 status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
1421 l_status := 16;
1422 RAISE;
1423 WHEN OTHERS THEN
1424 status := substr(SQLERRM,1,240);
1425 l_status := sqlcode;
1426 -- ROLLBACK;
1427 RAISE;
1428 END;
1429 EXCEPTION
1430 WHEN OTHERS THEN
1431 -- DBMS_OUTPUT.PUT_LINE(status);
1432 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
1433 X_error_message := status;
1434 X_status := l_status;
1435
1436 insert_message(X_inserting_procedure_name => 'pa_billing_pub.insert_event',
1437 X_attribute1 => XD_bill_trans_rev_amt,
1438 X_attribute2 => XD_bill_trans_bill_amt,
1439 X_message => status,
1440 X_status => err_status,
1441 X_error_message => err_message);
1442
1443 IF (l_status <0 OR err_status <0) THEN
1444 RAISE;
1445 END IF;
1446
1447 -- COMMIT;
1448 END insert_event;
1449
1450 function GET_MRC_FOR_FUND_FLAG return boolean
1451 is
1452 l_enabled_flag varchar2(1);
1453 begin
1454
1455 SELECT 'N' -- MRC migration to SLA
1456 INTO l_enabled_flag
1457 FROM pa_implementations;
1458
1459 /* Changed for bug 2729975*/
1460 if l_enabled_flag = 'N' then
1461 return FALSE;
1462 else
1463 return TRUE;
1464 end if;
1465 end GET_MRC_FOR_FUND_FLAG;
1466
1467 -- Following APIs added for FP_M changes
1468 -- If the Project is enabled with Top Task Customer Enabled then
1469 -- return the value as 'Y' else 'N'
1470 Function Get_Top_Task_Customer_Flag (
1471 P_Project_ID IN NUMBER
1472 )
1473 Return Varchar2
1474 IS
1475 l_Enable_Top_Task_Cust_Flag VARCHAR2(1);
1476
1477 Begin
1478 Select NVL(Enable_Top_Task_Customer_Flag, 'N')
1479 Into l_Enable_Top_Task_Cust_Flag
1480 From PA_Projects_all
1481 Where Project_ID = P_Project_ID;
1482
1483 Return l_Enable_Top_Task_Cust_Flag;
1484
1485 Exception When Others then
1486 Return 'N';
1487 End Get_Top_Task_Customer_Flag;
1488
1489
1490 -- If the Project is enabled with Override Invoice Method as Enabled then
1491 -- return the value as 'Y' else 'N'
1492 Function Get_Inv_Method_Override_Flag (
1493 P_Project_ID IN NUMBER
1494 )
1495 Return Varchar2 IS
1496
1497 l_ENABLE_TOP_TASK_INV_MTH_FLAG VARCHAR2(1);
1498
1499 Begin
1500 Select NVL(ENABLE_TOP_TASK_INV_MTH_FLAG, 'N')
1501 Into l_ENABLE_TOP_TASK_INV_MTH_FLAG
1502 From PA_Projects_all
1503 Where Project_ID = P_Project_ID;
1504
1505 Return l_ENABLE_TOP_TASK_INV_MTH_FLAG;
1506
1507 Exception When Others then
1508 Return 'N';
1509 End Get_Inv_Method_Override_Flag;
1510
1511 -- End of APIs added for FP_M changes => Customer at Top Task
1512
1513
1514
1515 --ER 14089913
1516
1517 PROCEDURE insert_event2 (X_rev_amt REAL DEFAULT NULL,
1518 X_bill_amt REAL DEFAULT NULL,
1519 X_project_id NUMBER DEFAULT NULL,
1520 X_event_type VARCHAR2 DEFAULT NULL,
1521 X_top_task_id NUMBER DEFAULT NULL,
1522 X_organization_id NUMBER DEFAULT NULL,
1523 X_completion_date DATE DEFAULT NULL,
1524 X_event_description VARCHAR2 DEFAULT NULL,
1525 X_event_num_reversed NUMBER DEFAULT NULL,
1526 X_attribute_category VARCHAR2 DEFAULT NULL,
1527 X_attribute1 VARCHAR2 DEFAULT NULL,
1528 X_attribute2 VARCHAR2 DEFAULT NULL,
1529 X_attribute3 VARCHAR2 DEFAULT NULL,
1530 X_attribute4 VARCHAR2 DEFAULT NULL,
1531 X_attribute5 VARCHAR2 DEFAULT NULL,
1532 X_attribute6 VARCHAR2 DEFAULT NULL,
1533 X_attribute7 VARCHAR2 DEFAULT NULL,
1534 X_attribute8 VARCHAR2 DEFAULT NULL,
1535 X_attribute9 VARCHAR2 DEFAULT NULL,
1536 X_attribute10 VARCHAR2 DEFAULT NULL,
1537 X_audit_amount1 IN NUMBER DEFAULT NULL,
1538 X_audit_amount2 IN NUMBER DEFAULT NULL,
1539 X_audit_amount3 IN NUMBER DEFAULT NULL,
1540 X_audit_amount4 IN NUMBER DEFAULT NULL,
1541 X_audit_amount5 IN NUMBER DEFAULT NULL,
1542 X_audit_amount6 IN NUMBER DEFAULT NULL,
1543 X_audit_amount7 IN NUMBER DEFAULT NULL,
1544 X_audit_amount8 IN NUMBER DEFAULT NULL,
1545 X_audit_amount9 IN NUMBER DEFAULT NULL,
1546 X_audit_amount10 IN NUMBER DEFAULT NULL,
1547 X_audit_cost_budget_type_code IN VARCHAR2 DEFAULT NULL,
1548 X_audit_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
1549 x_inventory_org_id IN NUMBER DEFAULT NULL,
1550 x_inventory_item_id IN NUMBER DEFAULT NULL,
1551 x_quantity_billed IN NUMBER DEFAULT NULL,
1552 x_uom_code IN VARCHAR2 DEFAULT NULL,
1553 x_unit_price IN NUMBER DEFAULT NULL,
1554 x_reference1 IN VARCHAR2 DEFAULT NULL,
1555 x_reference2 IN VARCHAR2 DEFAULT NULL,
1556 x_reference3 IN VARCHAR2 DEFAULT NULL,
1557 x_reference4 IN VARCHAR2 DEFAULT NULL,
1558 x_reference5 IN VARCHAR2 DEFAULT NULL,
1559 x_reference6 IN VARCHAR2 DEFAULT NULL,
1560 x_reference7 IN VARCHAR2 DEFAULT NULL,
1561 x_reference8 IN VARCHAR2 DEFAULT NULL,
1562 x_reference9 IN VARCHAR2 DEFAULT NULL,
1563 x_reference10 IN VARCHAR2 DEFAULT NULL,
1564 X_txn_currency_code IN VARCHAR2 DEFAULT NULL, /* Added 20 columns for MCB2 */
1565 X_project_rate_type IN VARCHAR2 DEFAULT NULL,
1566 X_project_rate_date IN DATE DEFAULT NULL,
1567 X_project_exchange_rate IN NUMBER DEFAULT NULL,
1568 X_project_func_rate_type IN VARCHAR2 DEFAULT NULL,
1569 X_project_func_rate_date IN DATE DEFAULT NULL,
1570 X_project_func_exchange_rate IN NUMBER DEFAULT NULL,
1571 X_funding_rate_type IN VARCHAR2 DEFAULT NULL,
1572 X_funding_rate_date IN DATE DEFAULT NULL,
1573 X_funding_exchange_rate IN NUMBER DEFAULT NULL,
1574 X_zero_revenue_amount_flag IN VARCHAR2 DEFAULT NULL, /* Funding MRC Changes */
1575 X_audit_cost_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
1576 X_audit_rev_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
1577 X_event_id OUT NOCOPY NUMBER,
1578 X_error_message OUT NOCOPY VARCHAR2,
1579 X_status OUT NOCOPY NUMBER
1580 ) IS
1581
1582 XD_bill_trans_bill_amt NUMBER(22,5); /* changed for MCB2 from rev/bill amount to trans bill/rev amount */
1583 XD_bill_trans_rev_amt NUMBER(22,5);
1584 XD_organization_id NUMBER(15);
1585 XD_completion_date DATE;
1586 XD_event_description VARCHAR2(240);
1587 XD_event_type VARCHAR2(30);
1588 event_num NUMBER(16);/*Increase size for bug 1742348*/
1589 invalid_id EXCEPTION;
1590 event_type_error EXCEPTION;
1591 null_event_type_error EXCEPTION;
1592 invalid_project_event EXCEPTION;
1593 mandatory_prm_missing EXCEPTION;
1594 zero_amounts EXCEPTION;
1595 no_orig_event EXCEPTION;
1596 invalid_calling_place EXCEPTION;
1597 invalid_invent_id EXCEPTION;
1598
1599 l_status NUMBER;
1600 err_status NUMBER;
1601 err_message VARCHAR2(240);
1602 l_event_id NUMBER;
1603
1604 /* MCB related changes as of 08/21/2001 by skannoji */
1605 l_project_id pa_projects_all.project_id%TYPE;
1606 l_multi_currency_billing_flag pa_projects_all.multi_currency_billing_flag%TYPE;
1607 l_baseline_funding_flag pa_projects_all.baseline_funding_flag%TYPE;
1608 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
1609 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
1610 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
1611 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
1612 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
1613 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
1614 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
1615 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
1616 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
1617 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
1618 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
1619 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
1620 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
1621 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
1622 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
1623 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
1624 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
1625 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
1626 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
1627 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
1628 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
1629 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
1630 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
1631 l_txn_currency_code pa_events.bill_trans_currency_code%TYPE;
1632 l_return_status VARCHAR2(30);
1633 l_msg_count NUMBER;
1634 l_msg_data VARCHAR2(30);
1635 l_found VARCHAR2(30);
1636
1637 l_proj_exch_rate_not_passd EXCEPTION;
1638 l_func_exch_rate_not_passd EXCEPTION;
1639 l_fund_exch_rate_not_passd EXCEPTION;
1640 l_proj_invalid_rate_type EXCEPTION;
1641 l_func_invalid_rate_type EXCEPTION;
1642 l_fund_invalid_rate_type EXCEPTION;
1643 l_invalid_currency EXCEPTION;
1644 /* Till Here */
1645
1646 BEGIN
1647 BEGIN
1648 IF g1_debug_mode = 'Y' THEN
1649 PA_MCB_INVOICE_PKG.log_message('Entering pa_billing_pub.insert_event2:' );
1650 END IF;
1651 -- Assigning who columns for insertion into PA_EVENTS.
1652
1653 created_by := FND_GLOBAL.USER_ID;
1654 last_updated_by := FND_GLOBAL.USER_ID;
1655 last_update_login := FND_GLOBAL.LOGIN_ID;
1656 program_application_id := FND_GLOBAL.PROG_APPL_ID;
1657 program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1658
1659 X_status := 0;
1660 X_error_message := NULL;
1661 -- Validate Mandatory Parameters
1662 IF (pa_billing.GlobVars.BillingAssignmentId IS NULL OR
1663 pa_billing.GlobVars.ReqId IS NULL OR
1664 pa_billing.GlobVars.CallingPlace IS NULL OR
1665 pa_billing.GlobVars.CallingProcess IS NULL) THEN
1666 raise mandatory_prm_missing;
1667 END IF;
1668
1669 /* The following logic has been added for MCB2 functionality */
1670 l_project_id := nvl(X_project_id, pa_billing.GlobVars.ProjectId);
1671 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
1672 p_project_id => l_project_id,
1673 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
1674 x_baseline_funding_flag => l_baseline_funding_flag,
1675 x_revproc_currency_code => l_revproc_currency_code,
1676 x_invproc_currency_type => l_invproc_currency_type,
1677 x_invproc_currency_code => l_invproc_currency_code,
1678 x_project_currency_code => l_project_currency_code,
1679 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
1680 x_project_bil_rate_type => l_project_bil_rate_type,
1681 x_project_bil_rate_date => l_project_bil_rate_date,
1682 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
1683 x_projfunc_currency_code => l_projfunc_currency_code,
1684 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
1685 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
1686 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
1687 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
1688 x_funding_rate_date_code => l_funding_rate_date_code,
1689 x_funding_rate_type => l_funding_rate_type,
1690 x_funding_rate_date => l_funding_rate_date,
1691 x_funding_exchange_rate => l_funding_exchange_rate,
1692 x_return_status => l_return_status,
1693 x_msg_count => l_msg_count,
1694 x_msg_data => l_msg_data);
1695
1696 l_txn_currency_code := NVL(x_txn_currency_code,l_projfunc_currency_code);
1697 BEGIN
1698 /* Validating Currency code */
1699 SELECT 'Y'
1700 INTO l_found
1701 FROM fnd_currencies /* Bug 4352166 Changed vl to base table*/
1702 WHERE currency_code = l_txn_currency_code
1703 AND TRUNC(SYSDATE) BETWEEN DECODE (TRUNC(start_date_active), NULL, TRUNC(SYSDATE),
1704 TRUNC(start_date_active))
1705 AND DECODE(TRUNC(end_date_active), NULL, TRUNC(SYSDATE),TRUNC(end_date_active));
1706 EXCEPTION
1707 WHEN OTHERS THEN
1708 RAISE l_invalid_currency;
1709 END;
1710
1711 /* Rounding the transaction amount upto the precision of transaction currency for MCB2 */
1712 XD_bill_trans_rev_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_rev_amt, 0),l_txn_currency_code);
1713 IF g1_debug_mode = 'Y' THEN
1714 PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event2: rev amt'||to_char(XD_bill_trans_rev_amt));
1715 END IF;
1716 XD_bill_trans_bill_amt := PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(NVL(X_bill_amt,0),l_txn_currency_code);
1717 IF g1_debug_mode = 'Y' THEN
1718 PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event2: inv amt'||to_char(XD_bill_trans_bill_amt));
1719 END IF;
1720 /* The following amounts have been commented for MCB2, the above amounts are satisfying the same requirement */
1721 /* XD_bill_trans_rev_amt := (NVL(X_rev_amt, 0));
1722 XD_bill_trans_bill_amt := (NVL(X_bill_amt,0)); */
1723
1724 -- Get defaults for other non-mandatory parameters
1725 XD_completion_date := nvl(X_completion_date,
1726 to_date(pa_billing.GlobVars.AccrueThruDate,'YYYY/MM/DD'));
1727
1728
1729 -- Added check to prevent event creation from other calling places like 'PRE' 'POST'
1730 -- 'DEL' etc.
1731 --
1732 IF (pa_billing.GlobVars.CallingPlace NOT IN ('REG','ADJ','POST-REG')) THEN
1733 raise invalid_calling_place;
1734 END IF;
1735
1736
1737 IF (X_organization_id IS NULL) THEN
1738 XD_organization_id := pa_billing_values.get_dflt_org(
1739 nvl(X_project_id,
1740 pa_billing.GlobVars.ProjectId),
1741 nvl(X_top_task_id,
1742 pa_billing.GlobVars.TaskId));
1743 ELSE
1744 XD_organization_id := X_organization_id;
1745 END IF;
1746
1747 IF (X_event_description IS NULL OR
1748 X_event_type IS NULL) THEN
1749 pa_billing_values.get_dflt_desc(pa_billing.GlobVars.BillingAssignmentId,
1750 XD_event_type, XD_event_description);
1751 XD_event_description := nvl(X_event_description,
1752 XD_event_description);
1753 XD_event_type := nvl(X_event_type, XD_event_type);
1754
1755 IF (XD_event_type IS NULL) THEN
1756 RAISE null_event_type_error;
1757 END IF;
1758 ELSE
1759 XD_event_description := X_event_description;
1760 XD_event_type := X_event_type;
1761 END IF;
1762
1763
1764 -- Validate Id's
1765
1766 IF (pa_billing_validate.valid_proj_task_extn(
1767 nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1768 nvl(X_top_task_id, pa_billing.GlobVars.TaskId),
1769 pa_billing.GlobVars.BillingAssignmentId) AND
1770 pa_billing_validate.valid_organization(XD_organization_id)) THEN
1771 NULL;
1772 ELSE
1773 RAISE INVALID_ID;
1774 END IF;
1775
1776
1777
1778 -- Validate funding level
1779
1780 IF (nvl(X_top_task_id, pa_billing.GlobVars.TaskId) IS NULL) THEN
1781 IF (pa_billing_values.funding_level(nvl(X_project_id,
1782 pa_billing.GlobVars.ProjectId))
1783 <> 'PROJECT') THEN
1784 RAISE invalid_project_event;
1785 END IF;
1786 END IF;
1787
1788 -- Funding MRC Changes added the flag X_zero_revenue_amount_flag
1789 -- If revenue amount is zero and X_zero_revenue_amount_flag = 'Y' is a valid case
1790 -- should not raise the exception. (Create zero dollar revenue event)
1791
1792 -- Validate amounts based on which process is calling.
1793 IF ((((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N')) AND
1794 (XD_bill_trans_bill_amt = 0)) OR
1795 ((XD_bill_trans_rev_amt = 0) AND (nvl(X_zero_revenue_amount_flag,'N') = 'N') AND
1796 pa_billing.GlobVars.CallingProcess = 'Revenue') OR
1797 ((XD_bill_trans_bill_amt = 0) AND
1798 pa_billing.GlobVars.CallingProcess = 'Invoice')) THEN
1799 RAISE zero_amounts;
1800 END IF;
1801
1802 /* Modified the below condition for Bug 9154825 */
1803 -- Check original event num for the ADJ automatic events.
1804 IF (PA_BILLING.GetCallPlace = 'ADJ' AND
1805 (pa_billing.GlobVars.CallingProcess = 'Invoice' OR pa_billing.GlobVars.CallingProcess = 'Revenue') AND
1806 nvl(X_event_num_reversed, 0) = 0) THEN
1807 RAISE no_orig_event;
1808 END IF;
1809
1810 event_num := pa_billing_seq.next_eventnum(
1811 nvl(X_project_id, pa_billing.GlobVars.ProjectId),
1812 nvl(X_top_task_id, pa_billing.GlobVars.TaskId));
1813
1814
1815 /* Adding validation of newly added columns in event table for project contract integration */
1816 -- Validating inventory_org_id and inventory_item_id
1817
1818 IF (x_inventory_org_id IS NOT NULL) THEN
1819 IF (pa_billing_validate.valid_organization(x_inventory_org_id)) THEN
1820 NULL;
1821 ELSE
1822 RAISE INVALID_INVENT_ID;
1823 END IF;
1824 END IF;
1825
1826 IF (x_inventory_item_id IS NOT NULL) THEN
1827 DECLARE
1828 l_dummy varchar2(30);
1829 BEGIN
1830 SELECT 'Valid item'
1831 INTO l_dummy
1832 FROM mtl_item_flexfields
1833 WHERE inventory_item_id = x_inventory_item_id;
1834
1835 EXCEPTION
1836 WHEN NO_DATA_FOUND THEN
1837 RAISE INVALID_INVENT_ID;
1838 WHEN TOO_MANY_ROWS THEN
1839 null;
1840 END;
1841 END IF;
1842
1843 /* MCB2: The following code have benn added to populate the conversion attributes */
1844
1845 IF ( l_txn_currency_code <> l_projfunc_currency_code ) THEN
1846 l_found := NULL;
1847 IF ( X_project_func_rate_type IS NOT NULL) THEN
1848 BEGIN
1849 SELECT 'found'
1850 INTO l_found
1851 FROM ( SELECT conversion_type, user_conversion_type
1852 FROM pa_conversion_types_v
1853 WHERE conversion_type <>'User'
1854 AND (pa_multi_currency.is_user_rate_type_allowed(
1855 l_txn_currency_code,
1856 l_projfunc_currency_code,
1857 DECODE(l_projfunc_bil_rate_date_code,
1858 'PA_INVOICE_DATE', NVL(X_project_func_rate_date,l_projfunc_bil_rate_date),
1859 'FIXED_DATE', NVL(X_project_func_rate_date,l_projfunc_bil_rate_date)))= 'N')
1860 UNION
1861 SELECT conversion_type, user_conversion_type
1862 FROM pa_conversion_types_v
1863 WHERE pa_multi_currency.is_user_rate_type_allowed(
1864 l_txn_currency_code,
1865 l_projfunc_currency_code,
1866 DECODE(l_projfunc_bil_rate_date_code,
1867 'PA_INVOICE_DATE',NVL(X_project_func_rate_date,l_projfunc_bil_rate_date),
1868 'FIXED_DATE',NVL(X_project_func_rate_date,l_projfunc_bil_rate_date) ))= 'Y')
1869 WHERE DECODE(conversion_type,X_project_func_rate_type,'Y','N') = 'Y';
1870 EXCEPTION
1871 WHEN NO_DATA_FOUND THEN
1872 RAISE l_func_invalid_rate_type;
1873 END;
1874 END IF;
1875
1876 IF ( X_project_func_rate_type IS NULL AND l_projfunc_bil_rate_type = 'User') THEN
1877 l_projfunc_bil_rate_date := NULL;
1878 IF ( X_project_func_exchange_rate IS NULL ) THEN
1879 null;
1880 ELSE
1881 l_projfunc_bil_exchange_rate := X_project_func_exchange_rate;
1882 END IF;
1883 ELSIF (X_project_func_rate_type = 'User') THEN
1884 l_projfunc_bil_rate_date := NULL;
1885 l_projfunc_bil_rate_type := X_project_func_rate_type;
1886 IF ( X_project_func_exchange_rate IS NULL ) THEN
1887 RAISE l_func_exch_rate_not_passd;
1888 ELSE
1889 l_projfunc_bil_exchange_rate := X_project_func_exchange_rate;
1890 END IF;
1891 ELSIF ( X_project_func_rate_type <> 'User' OR l_projfunc_bil_rate_type <> 'User') THEN
1892 l_projfunc_bil_rate_type := NVL(X_project_func_rate_type,l_projfunc_bil_rate_type);
1893 l_projfunc_bil_exchange_rate := NULL;
1894 END IF;
1895
1896
1897 IF ( l_projfunc_bil_rate_type <> 'User' AND l_projfunc_bil_rate_date_code = 'FIXED_DATE'
1898 AND X_project_func_rate_date IS NOT NULL ) THEN
1899 l_projfunc_bil_rate_date := X_project_func_rate_date;
1900 ELSIF (l_projfunc_bil_rate_type <> 'User' AND l_projfunc_bil_rate_date_code = 'PA_INVOICE_DATE'
1901 AND X_project_func_rate_date IS NOT NULL ) THEN
1902 l_projfunc_bil_rate_date := X_project_func_rate_date;
1903 END IF;
1904 END IF;
1905
1906 /* Project currency code logic */
1907 IF ( l_txn_currency_code <> l_project_currency_code ) THEN
1908 l_found := NULL;
1909 IF ( X_project_rate_type IS NOT NULL) THEN
1910 BEGIN
1911 SELECT 'found'
1912 INTO l_found
1913 FROM ( SELECT conversion_type, user_conversion_type
1914 FROM pa_conversion_types_v
1915 WHERE conversion_type <>'User'
1916 AND (pa_multi_currency.is_user_rate_type_allowed(
1917 l_txn_currency_code,
1918 l_project_currency_code,
1919 DECODE(l_project_bil_rate_date_code,
1920 'PA_INVOICE_DATE', NVL(X_project_rate_date,l_project_bil_rate_date),
1921 'FIXED_DATE', NVL(X_project_rate_date,l_project_bil_rate_date)))= 'N')
1922 UNION
1923 SELECT conversion_type, user_conversion_type
1924 FROM pa_conversion_types_v
1925 WHERE pa_multi_currency.is_user_rate_type_allowed(
1926 l_txn_currency_code,
1927 l_project_currency_code,
1928 DECODE(l_project_bil_rate_date_code,
1929 'PA_INVOICE_DATE',NVL(X_project_rate_date,l_project_bil_rate_date),
1930 'FIXED_DATE',NVL(X_project_rate_date,l_project_bil_rate_date) ))= 'Y')
1931 WHERE DECODE(conversion_type,X_project_rate_type,'Y','N') = 'Y';
1932 EXCEPTION
1933 WHEN NO_DATA_FOUND THEN
1934 RAISE l_proj_invalid_rate_type;
1935 END;
1936 END IF;
1937
1938 IF ( X_project_rate_type IS NULL AND l_project_bil_rate_type = 'User') THEN
1939 l_project_bil_rate_date := NULL;
1940 IF ( X_project_exchange_rate IS NULL ) THEN
1941 null;
1942 ELSE
1943 l_project_bil_exchange_rate := X_project_exchange_rate;
1944 END IF;
1945 ELSIF (X_project_rate_type = 'User') THEN
1946 l_project_bil_rate_date := NULL;
1947 l_project_bil_rate_type := X_project_rate_type;
1948 IF ( X_project_exchange_rate IS NULL ) THEN
1949 RAISE l_proj_exch_rate_not_passd;
1950 ELSE
1951 l_project_bil_exchange_rate := X_project_exchange_rate;
1952 END IF;
1953 ELSIF ( X_project_rate_type <> 'User' OR l_project_bil_rate_type <> 'User') THEN
1954 l_project_bil_rate_type := NVL(X_project_rate_type,l_project_bil_rate_type);
1955 l_project_bil_exchange_rate := NULL;
1956 END IF;
1957 IF ( l_project_bil_rate_type <> 'User' AND l_project_bil_rate_date_code = 'FIXED_DATE'
1958 AND X_project_rate_date IS NOT NULL ) THEN
1959 l_project_bil_rate_date := X_project_rate_date;
1960 ELSIF (l_project_bil_rate_type <> 'User' AND l_project_bil_rate_date_code = 'PA_INVOICE_DATE'
1961 AND X_project_rate_date IS NOT NULL ) THEN
1962 l_project_bil_rate_date := X_project_rate_date;
1963 END IF;
1964 END IF;
1965
1966 /* Funding Currency code logic */
1967 IF ( l_multi_currency_billing_flag = 'Y' OR l_multi_currency_billing_flag = 'y') THEN
1968 l_found := NULL;
1969 IF ( X_funding_rate_type IS NOT NULL) THEN
1970 BEGIN
1971 SELECT 'found'
1972 INTO l_found
1973 FROM ( SELECT conversion_type, user_conversion_type
1974 FROM pa_conversion_types_v)
1975 WHERE DECODE(conversion_type,X_funding_rate_type,'Y','N') = 'Y';
1976 EXCEPTION
1977 WHEN NO_DATA_FOUND THEN
1978 RAISE l_fund_invalid_rate_type;
1979 END;
1980 END IF;
1981
1982 IF ( X_funding_rate_type IS NULL AND l_funding_rate_type = 'User') THEN
1983 l_funding_rate_date := NULL;
1984 IF ( X_funding_exchange_rate IS NULL ) THEN
1985 null;
1986 ELSE
1987 l_funding_exchange_rate := X_funding_exchange_rate;
1988 END IF;
1989 ELSIF (X_funding_rate_type = 'User') THEN
1990 l_funding_rate_date := NULL;
1991 l_funding_rate_type := X_funding_rate_type;
1992 IF ( X_funding_exchange_rate IS NULL ) THEN
1993 RAISE l_fund_exch_rate_not_passd;
1994 ELSE
1995 l_funding_exchange_rate := X_funding_exchange_rate;
1996 END IF;
1997 ELSIF ( X_funding_rate_type <> 'User' OR l_funding_rate_type <> 'User') THEN
1998 l_funding_rate_type := NVL(X_funding_rate_type,l_funding_rate_type);
1999 l_funding_exchange_rate := NULL;
2000 END IF;
2001 IF ( l_funding_rate_type <> 'User' AND l_funding_rate_date_code = 'FIXED_DATE'
2002 AND X_funding_rate_date IS NOT NULL ) THEN
2003 l_funding_rate_date := X_funding_rate_date;
2004 ELSIF (l_funding_rate_type <> 'User' AND l_funding_rate_date_code = 'PA_INVOICE_DATE'
2005 AND X_funding_rate_date IS NOT NULL ) THEN
2006 l_funding_rate_date := X_funding_rate_date;
2007 END IF;
2008 END IF;
2009
2010 /* Added for Bug3068864 */
2011 IF ( l_txn_currency_code = l_projfunc_currency_code ) THEN
2012 l_projfunc_bil_rate_type := Null;
2013 l_projfunc_bil_rate_date := Null;
2014 l_projfunc_bil_exchange_rate := Null;
2015 ELSIF (l_txn_currency_code = l_project_currency_code ) THEN
2016 l_project_bil_rate_type := Null;
2017 l_project_bil_rate_date := Null;
2018 l_project_bil_exchange_rate := Null;
2019 END IF;
2020 /* till here for Bug3068864 */
2021
2022 /* Populating Invoice attributes */
2023
2024 IF ( l_invproc_currency_code = l_projfunc_currency_code ) THEN
2025 l_invproc_currency_code := l_projfunc_currency_code;
2026 l_invproc_rate_type := l_projfunc_bil_rate_type;
2027 l_invproc_rate_date := l_projfunc_bil_rate_date;
2028 l_invproc_exchange_rate := l_projfunc_bil_exchange_rate;
2029 ELSIF (l_invproc_currency_code = l_project_currency_code ) THEN
2030 l_invproc_currency_code := l_project_currency_code;
2031 l_invproc_rate_type := l_project_bil_rate_type;
2032 l_invproc_rate_date := l_project_bil_rate_date;
2033 l_invproc_exchange_rate := l_project_bil_exchange_rate;
2034 ELSE
2035 l_invproc_currency_code := '';
2036 l_invproc_rate_type := l_funding_rate_type;
2037 l_invproc_rate_date := l_funding_rate_date;
2038 l_invproc_exchange_rate := l_funding_exchange_rate;
2039 END IF;
2040
2041 /* Populating Revenue attributes */
2042
2043 IF ( l_revproc_currency_code = l_projfunc_currency_code ) THEN
2044 l_revproc_currency_code := l_projfunc_currency_code;
2045 l_revproc_rate_type := l_projfunc_bil_rate_type;
2046 l_revproc_rate_date := l_projfunc_bil_rate_date;
2047 l_revproc_exchange_rate := l_projfunc_bil_exchange_rate;
2048 END IF;
2049
2050 /* MCB2: Removed Revenue amount and Bill amount because,these amounts are being used only for transactions */
2051
2052
2053 l_event_id := pa_events_s.nextval;
2054
2055
2056 IF (pa_billing_validate.automatic_event(XD_event_type)) THEN
2057 insert into pa_events
2058 (PROJECT_ID, TASK_ID, ORGANIZATION_ID, EVENT_NUM, EVENT_TYPE, -- 1
2059 REVENUE_AMOUNT,BILL_AMOUNT,COMPLETION_DATE, REQUEST_ID, -- 2
2060 DESCRIPTION, BILL_HOLD_FLAG, REV_DIST_REJECTION_CODE, -- 3
2061 REVENUE_DISTRIBUTED_FLAG, PROGRAM_APPLICATION_ID, PROGRAM_ID, -- 4
2062 PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, -- 5
2063 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, ATTRIBUTE_CATEGORY, -- 6
2064 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, -- 7
2065 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, -- 8
2066 BILLING_ASSIGNMENT_ID, calling_place, calling_process, -- 9
2067 EVENT_NUM_REVERSED, -- 10
2068 AUDIT_AMOUNT1,
2069 AUDIT_AMOUNT2,
2070 AUDIT_AMOUNT3,
2071 AUDIT_AMOUNT4,
2072 AUDIT_AMOUNT5,
2073 AUDIT_AMOUNT6,
2074 AUDIT_AMOUNT7,
2075 AUDIT_AMOUNT8,
2076 AUDIT_AMOUNT9,
2077 AUDIT_AMOUNT10,
2078 AUDIT_COST_BUDGET_TYPE_CODE,
2079 AUDIT_REV_BUDGET_TYPE_CODE,
2080 EVENT_ID,
2081 INVENTORY_ORG_ID,
2082 INVENTORY_ITEM_ID,
2083 QUANTITY_BILLED,
2084 UOM_CODE,
2085 UNIT_PRICE,
2086 REFERENCE1,
2087 REFERENCE2,
2088 REFERENCE3,
2089 REFERENCE4,
2090 REFERENCE5,
2091 REFERENCE6,
2092 REFERENCE7,
2093 REFERENCE8,
2094 REFERENCE9,
2095 REFERENCE10,
2096 BILL_TRANS_CURRENCY_CODE, /* These 22 columns have been added for MCB2 */
2097 BILL_TRANS_REV_AMOUNT,
2098 BILL_TRANS_BILL_AMOUNT,
2099 PROJECT_CURRENCY_CODE,
2100 PROJECT_RATE_TYPE,
2101 PROJECT_RATE_DATE,
2102 PROJECT_EXCHANGE_RATE,
2103 PROJFUNC_CURRENCY_CODE,
2104 PROJFUNC_RATE_TYPE,
2105 PROJFUNC_RATE_DATE,
2106 PROJFUNC_EXCHANGE_RATE,
2107 FUNDING_RATE_TYPE,
2108 FUNDING_RATE_DATE,
2109 FUNDING_EXCHANGE_RATE,
2110 INVPROC_CURRENCY_CODE,
2111 INVPROC_RATE_TYPE,
2112 INVPROC_RATE_DATE,
2113 INVPROC_EXCHANGE_RATE,
2114 REVPROC_CURRENCY_CODE,
2115 REVPROC_RATE_TYPE,
2116 REVPROC_RATE_DATE,
2117 REVPROC_EXCHANGE_RATE,
2118 ZERO_REVENUE_AMOUNT_FLAG, /* Funding MRC Changes */
2119 AUDIT_COST_PLAN_TYPE_ID, /* Added for Fin Plan impact */
2120 AUDIT_REV_PLAN_TYPE_ID /* Added for Fin Plan impact */
2121 )
2122 values
2123 (nvl(X_project_id, pa_billing.GlobVars.ProjectId), nvl(X_top_task_id,pa_billing.GlobVars.TaskId),
2124 XD_organization_id, event_num, XD_event_type, -- 1
2125 0,0,XD_completion_date, pa_billing.GlobVars.ReqId, -- 2
2126 XD_event_description, 'N', NULL, -- 3
2127 'N', program_application_id,program_id, -- 4
2128 sysdate, sysdate, nvl(last_updated_by,0), -- 5
2129 sysdate, nvl(created_by,0), nvl(last_update_login,0),
2130 X_attribute_category, -- 6
2131 X_attribute1, X_attribute2, X_attribute3, X_attribute4,
2132 X_attribute5, -- 7
2133 X_attribute6, X_attribute7, X_attribute8, X_attribute9,
2134 X_attribute10, -- 8
2135 pa_billing.GlobVars.BillingAssignmentId, pa_billing.GlobVars.CallingPlace,
2136 pa_billing.GlobVars.CallingProcess, -- 9
2137 X_event_num_reversed, -- 10
2138 pa_currency.round_currency_amt(X_audit_amount1),
2139 pa_currency.round_currency_amt(X_audit_amount2),
2140 pa_currency.round_currency_amt(X_audit_amount3),
2141 pa_currency.round_currency_amt(X_audit_amount4),
2142 pa_currency.round_currency_amt(X_audit_amount5),
2143 pa_currency.round_currency_amt(X_audit_amount6),
2144 pa_currency.round_currency_amt(X_audit_amount7),
2145 pa_currency.round_currency_amt(X_audit_amount8),
2146 pa_currency.round_currency_amt(X_audit_amount9),
2147 pa_currency.round_currency_amt(X_audit_amount10),
2148 X_audit_cost_budget_type_code,
2149 X_audit_rev_budget_type_code,
2150 l_event_id,
2151 x_inventory_org_id,
2152 x_inventory_item_id,
2153 x_quantity_billed,
2154 x_uom_code,
2155 x_unit_price,
2156 x_reference1,
2157 x_reference2,
2158 x_reference3,
2159 x_reference4,
2160 x_reference5,
2161 x_reference6,
2162 x_reference7,
2163 x_reference8,
2164 x_reference9,
2165 x_reference10,
2166 l_txn_currency_code,
2167 XD_bill_trans_rev_amt,
2168 XD_bill_trans_bill_amt,
2169 l_project_currency_code,
2170 l_project_bil_rate_type,
2171 l_project_bil_rate_date,
2172 l_project_bil_exchange_rate,
2173 l_projfunc_currency_code,
2174 l_projfunc_bil_rate_type,
2175 l_projfunc_bil_rate_date,
2176 l_projfunc_bil_exchange_rate,
2177 l_funding_rate_type,
2178 l_funding_rate_date,
2179 l_funding_exchange_rate,
2180 l_invproc_currency_code,
2181 l_invproc_rate_type,
2182 l_invproc_rate_date,
2183 l_invproc_exchange_rate,
2184 l_revproc_currency_code,
2185 l_revproc_rate_type,
2186 l_revproc_rate_date,
2187 l_revproc_exchange_rate,
2188 NVL(X_zero_revenue_amount_flag, 'N'),
2189 X_audit_cost_plan_type_id,
2190 X_audit_rev_plan_type_id
2191 );
2192 ELSE RAISE EVENT_TYPE_ERROR;
2193 END IF;
2194 -- COMMIT;
2195
2196
2197 X_event_id := l_event_id;
2198
2199
2200
2201 IF g1_debug_mode = 'Y' THEN
2202 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.insert_event2: ');
2203 END IF;
2204 EXCEPTION
2205 WHEN mandatory_prm_missing THEN
2206 status := pa_billing_values.get_message('MANDATORY_PRM_MISSING');
2207 l_status := 1;
2208 RAISE;
2209 WHEN invalid_id THEN
2210 status := pa_billing_values.get_message('INVALID_ID');
2211 l_status := 2;
2212 RAISE;
2213 WHEN invalid_project_event THEN
2214 status := pa_billing_values.get_message('INVALID_PROJECT_EVENT');
2215 l_status := 3;
2216 RAISE;
2217 /*WHEN event_type_error THEN commenting this for bug 3492506
2218 status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
2219 RAISE;*/
2220 WHEN null_event_type_error THEN
2221 status := pa_billing_values.get_message('NULL_EVENT_TYPE_ERROR');
2222 l_status := 4;
2223 RAISE;
2224 WHEN zero_amounts THEN
2225 status := pa_billing_values.get_message('ZERO_AMOUNTS');
2226 l_status := 5;
2227 RAISE;
2228 WHEN no_orig_event THEN
2229 status := pa_billing_values.get_message('NO_ORIG_EVENT');
2230 l_status := 6;
2231 RAISE;
2232 WHEN invalid_calling_place THEN
2233 status := pa_billing_values.get_message('INVALID_CALLING_PLACE');
2234 l_status := 7;
2235 RAISE;
2236 WHEN invalid_invent_id THEN
2237 status := pa_billing_values.get_message('INVALID_INVENT_ID');
2238 l_status := 8;
2239 RAISE;
2240 WHEN l_func_exch_rate_not_passd THEN /* Added for MCB2 */
2241 status := pa_billing_values.get_message('PA_FUNC_EXCH_RATE_NOT_PASSD');
2242 l_status := 9;
2243 RAISE;
2244 WHEN l_proj_exch_rate_not_passd THEN /* Added for MCB2 */
2245 status := pa_billing_values.get_message('PA_PROJ_EXCH_RATE_NOT_PASSD');
2246 l_status := 10;
2247 RAISE;
2248 WHEN l_fund_exch_rate_not_passd THEN /* Added for MCB2 */
2249 status := pa_billing_values.get_message('PA_FUND_EXCH_RATE_NOT_PASSD');
2250 l_status := 11;
2251 RAISE;
2252 WHEN l_func_invalid_rate_type THEN /* Added for MCB2 */
2253 status := pa_billing_values.get_message('PA_FUNC_INVALID_RATE_TYPE');
2254 l_status := 12;
2255 RAISE;
2256 WHEN l_proj_invalid_rate_type THEN /* Added for MCB2 */
2257 status := pa_billing_values.get_message('PA_PROJ_INVALID_RATE_TYPE');
2258 l_status := 13;
2259 RAISE;
2260 WHEN l_fund_invalid_rate_type THEN /* Added for MCB2 */
2261 status := pa_billing_values.get_message('PA_FUND_INVALID_RATE_TYPE');
2262 l_status := 14;
2263 RAISE;
2264 WHEN l_invalid_currency THEN /* Added for MCB2 */
2265 status := pa_billing_values.get_message('PA_CURR_NOT_VALID_BC');
2266 l_status := 15;
2267 RAISE;
2268 WHEN event_type_error THEN /* adding this here for bug 3492506 */
2269 status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
2270 l_status := 16;
2271 RAISE;
2272 WHEN OTHERS THEN
2273 status := substr(SQLERRM,1,240);
2274 l_status := sqlcode;
2275 -- ROLLBACK;
2276 RAISE;
2277 END;
2278 EXCEPTION
2279 WHEN OTHERS THEN
2280 -- DBMS_OUTPUT.PUT_LINE(status);
2281 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
2282 X_error_message := status;
2283 X_status := l_status;
2284
2285 insert_message(X_inserting_procedure_name => 'pa_billing_pub.insert_event2',
2286 X_attribute1 => XD_bill_trans_rev_amt,
2287 X_attribute2 => XD_bill_trans_bill_amt,
2288 X_message => status,
2289 X_status => err_status,
2290 X_error_message => err_message);
2291
2292 IF (l_status <0 OR err_status <0) THEN
2293 RAISE;
2294 END IF;
2295
2296
2297
2298 -- COMMIT;
2299 END insert_event2;
2300
2301
2302 --ER 14089913
2303
2304 END pa_billing_pub;