[Home] [Help]
PACKAGE BODY: APPS.PA_BILLING_PUB
Source
1 Package BODY pa_billing_pub AS
2 /* $Header: PAXIPUBB.pls 120.5 2006/02/02 00:14:28 rgandhi noship $ */
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
398 -- adjusting ei's that are adjusting expenditure items being
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
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
543 END get_amount;
540 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
541 X_amount := NULL;
542 RAISE;
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,
698 X_attribute9 VARCHAR2 DEFAULT NULL,
695 X_attribute6 VARCHAR2 DEFAULT NULL,
696 X_attribute7 VARCHAR2 DEFAULT NULL,
697 X_attribute8 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;
782 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%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;
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));
879 PA_MCB_INVOICE_PKG.log_message('after pa_billing_pub.insert_event: inv amt'||to_char(XD_bill_trans_bill_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
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 -- Check original event num for the ADJ automatic events.
964 IF (PA_BILLING.GetCallPlace = 'ADJ' AND
965 pa_billing.GlobVars.CallingProcess = 'Invoice' AND
966 nvl(X_event_num_reversed, 0) = 0) THEN
967 RAISE no_orig_event;
968 END IF;
969
970 event_num := pa_billing_seq.next_eventnum(
971 nvl(X_project_id, pa_billing.GlobVars.ProjectId),
972 nvl(X_top_task_id, pa_billing.GlobVars.TaskId));
973
974
975 /* Adding validation of newly added columns in event table for project contract integration */
976 -- Validating inventory_org_id and inventory_item_id
977
978 IF (x_inventory_org_id IS NOT NULL) THEN
979 IF (pa_billing_validate.valid_organization(x_inventory_org_id)) THEN
980 NULL;
981 ELSE
982 RAISE INVALID_INVENT_ID;
983 END IF;
984 END IF;
985
986 IF (x_inventory_item_id IS NOT NULL) THEN
987 DECLARE
988 l_dummy varchar2(30);
989 BEGIN
990 SELECT 'Valid item'
991 INTO l_dummy
992 FROM mtl_item_flexfields
993 WHERE inventory_item_id = x_inventory_item_id;
994
995 EXCEPTION
996 WHEN NO_DATA_FOUND THEN
997 RAISE INVALID_INVENT_ID;
998 WHEN TOO_MANY_ROWS THEN
999 null;
1000 END;
1001 END IF;
1002
1003 /* MCB2: The following code have benn added to populate the conversion attributes */
1004
1005 IF ( l_txn_currency_code <> l_projfunc_currency_code ) THEN
1006 l_found := NULL;
1007 IF ( X_project_func_rate_type IS NOT NULL) THEN
1008 BEGIN
1009 SELECT 'found'
1010 INTO l_found
1014 AND (pa_multi_currency.is_user_rate_type_allowed(
1011 FROM ( SELECT conversion_type, user_conversion_type
1012 FROM pa_conversion_types_v
1013 WHERE conversion_type <>'User'
1015 l_txn_currency_code,
1016 l_projfunc_currency_code,
1017 DECODE(l_projfunc_bil_rate_date_code,
1018 'PA_INVOICE_DATE', NVL(X_project_func_rate_date,l_projfunc_bil_rate_date),
1019 'FIXED_DATE', NVL(X_project_func_rate_date,l_projfunc_bil_rate_date)))= 'N')
1020 UNION
1021 SELECT conversion_type, user_conversion_type
1022 FROM pa_conversion_types_v
1023 WHERE pa_multi_currency.is_user_rate_type_allowed(
1024 l_txn_currency_code,
1025 l_projfunc_currency_code,
1026 DECODE(l_projfunc_bil_rate_date_code,
1027 'PA_INVOICE_DATE',NVL(X_project_func_rate_date,l_projfunc_bil_rate_date),
1028 'FIXED_DATE',NVL(X_project_func_rate_date,l_projfunc_bil_rate_date) ))= 'Y')
1029 WHERE DECODE(conversion_type,X_project_func_rate_type,'Y','N') = 'Y';
1030 EXCEPTION
1031 WHEN NO_DATA_FOUND THEN
1032 RAISE l_func_invalid_rate_type;
1033 END;
1034 END IF;
1035
1036 IF ( X_project_func_rate_type IS NULL AND l_projfunc_bil_rate_type = 'User') THEN
1037 l_projfunc_bil_rate_date := NULL;
1038 IF ( X_project_func_exchange_rate IS NULL ) THEN
1039 null;
1040 ELSE
1041 l_projfunc_bil_exchange_rate := X_project_func_exchange_rate;
1042 END IF;
1043 ELSIF (X_project_func_rate_type = 'User') THEN
1044 l_projfunc_bil_rate_date := NULL;
1045 l_projfunc_bil_rate_type := X_project_func_rate_type;
1046 IF ( X_project_func_exchange_rate IS NULL ) THEN
1047 RAISE l_func_exch_rate_not_passd;
1048 ELSE
1049 l_projfunc_bil_exchange_rate := X_project_func_exchange_rate;
1050 END IF;
1051 ELSIF ( X_project_func_rate_type <> 'User' OR l_projfunc_bil_rate_type <> 'User') THEN
1052 l_projfunc_bil_rate_type := NVL(X_project_func_rate_type,l_projfunc_bil_rate_type);
1053 l_projfunc_bil_exchange_rate := NULL;
1054 END IF;
1055
1056
1057 IF ( l_projfunc_bil_rate_type <> 'User' AND l_projfunc_bil_rate_date_code = 'FIXED_DATE'
1058 AND X_project_func_rate_date IS NOT NULL ) THEN
1059 l_projfunc_bil_rate_date := X_project_func_rate_date;
1060 ELSIF (l_projfunc_bil_rate_type <> 'User' AND l_projfunc_bil_rate_date_code = 'PA_INVOICE_DATE'
1061 AND X_project_func_rate_date IS NOT NULL ) THEN
1062 l_projfunc_bil_rate_date := X_project_func_rate_date;
1063 END IF;
1064 END IF;
1065
1066 /* Project currency code logic */
1067 IF ( l_txn_currency_code <> l_project_currency_code ) THEN
1068 l_found := NULL;
1069 IF ( X_project_rate_type IS NOT NULL) THEN
1070 BEGIN
1071 SELECT 'found'
1072 INTO l_found
1073 FROM ( SELECT conversion_type, user_conversion_type
1074 FROM pa_conversion_types_v
1075 WHERE conversion_type <>'User'
1076 AND (pa_multi_currency.is_user_rate_type_allowed(
1077 l_txn_currency_code,
1078 l_project_currency_code,
1079 DECODE(l_project_bil_rate_date_code,
1080 'PA_INVOICE_DATE', NVL(X_project_rate_date,l_project_bil_rate_date),
1081 'FIXED_DATE', NVL(X_project_rate_date,l_project_bil_rate_date)))= 'N')
1082 UNION
1083 SELECT conversion_type, user_conversion_type
1084 FROM pa_conversion_types_v
1085 WHERE pa_multi_currency.is_user_rate_type_allowed(
1086 l_txn_currency_code,
1087 l_project_currency_code,
1088 DECODE(l_project_bil_rate_date_code,
1089 'PA_INVOICE_DATE',NVL(X_project_rate_date,l_project_bil_rate_date),
1090 'FIXED_DATE',NVL(X_project_rate_date,l_project_bil_rate_date) ))= 'Y')
1091 WHERE DECODE(conversion_type,X_project_rate_type,'Y','N') = 'Y';
1092 EXCEPTION
1093 WHEN NO_DATA_FOUND THEN
1094 RAISE l_proj_invalid_rate_type;
1095 END;
1096 END IF;
1097
1098 IF ( X_project_rate_type IS NULL AND l_project_bil_rate_type = 'User') THEN
1099 l_project_bil_rate_date := NULL;
1100 IF ( X_project_exchange_rate IS NULL ) THEN
1101 null;
1102 ELSE
1103 l_project_bil_exchange_rate := X_project_exchange_rate;
1107 l_project_bil_rate_type := X_project_rate_type;
1104 END IF;
1105 ELSIF (X_project_rate_type = 'User') THEN
1106 l_project_bil_rate_date := NULL;
1108 IF ( X_project_exchange_rate IS NULL ) THEN
1109 RAISE l_proj_exch_rate_not_passd;
1110 ELSE
1111 l_project_bil_exchange_rate := X_project_exchange_rate;
1112 END IF;
1113 ELSIF ( X_project_rate_type <> 'User' OR l_project_bil_rate_type <> 'User') THEN
1114 l_project_bil_rate_type := NVL(X_project_rate_type,l_project_bil_rate_type);
1115 l_project_bil_exchange_rate := NULL;
1116 END IF;
1117 IF ( l_project_bil_rate_type <> 'User' AND l_project_bil_rate_date_code = 'FIXED_DATE'
1118 AND X_project_rate_date IS NOT NULL ) THEN
1119 l_project_bil_rate_date := X_project_rate_date;
1120 ELSIF (l_project_bil_rate_type <> 'User' AND l_project_bil_rate_date_code = 'PA_INVOICE_DATE'
1121 AND X_project_rate_date IS NOT NULL ) THEN
1122 l_project_bil_rate_date := X_project_rate_date;
1123 END IF;
1124 END IF;
1125
1126 /* Funding Currency code logic */
1127 IF ( l_multi_currency_billing_flag = 'Y' OR l_multi_currency_billing_flag = 'y') THEN
1128 l_found := NULL;
1129 IF ( X_funding_rate_type IS NOT NULL) THEN
1130 BEGIN
1131 SELECT 'found'
1132 INTO l_found
1133 FROM ( SELECT conversion_type, user_conversion_type
1134 FROM pa_conversion_types_v)
1135 WHERE DECODE(conversion_type,X_funding_rate_type,'Y','N') = 'Y';
1136 EXCEPTION
1137 WHEN NO_DATA_FOUND THEN
1138 RAISE l_fund_invalid_rate_type;
1139 END;
1140 END IF;
1141
1142 IF ( X_funding_rate_type IS NULL AND l_funding_rate_type = 'User') THEN
1143 l_funding_rate_date := NULL;
1144 IF ( X_funding_exchange_rate IS NULL ) THEN
1145 null;
1146 ELSE
1147 l_funding_exchange_rate := X_funding_exchange_rate;
1148 END IF;
1149 ELSIF (X_funding_rate_type = 'User') THEN
1150 l_funding_rate_date := NULL;
1151 l_funding_rate_type := X_funding_rate_type;
1152 IF ( X_funding_exchange_rate IS NULL ) THEN
1153 RAISE l_fund_exch_rate_not_passd;
1154 ELSE
1155 l_funding_exchange_rate := X_funding_exchange_rate;
1156 END IF;
1157 ELSIF ( X_funding_rate_type <> 'User' OR l_funding_rate_type <> 'User') THEN
1158 l_funding_rate_type := NVL(X_funding_rate_type,l_funding_rate_type);
1159 l_funding_exchange_rate := NULL;
1160 END IF;
1161 IF ( l_funding_rate_type <> 'User' AND l_funding_rate_date_code = 'FIXED_DATE'
1162 AND X_funding_rate_date IS NOT NULL ) THEN
1163 l_funding_rate_date := X_funding_rate_date;
1164 ELSIF (l_funding_rate_type <> 'User' AND l_funding_rate_date_code = 'PA_INVOICE_DATE'
1165 AND X_funding_rate_date IS NOT NULL ) THEN
1166 l_funding_rate_date := X_funding_rate_date;
1167 END IF;
1168 END IF;
1169
1170 /* Added for Bug3068864 */
1171 IF ( l_txn_currency_code = l_projfunc_currency_code ) THEN
1172 l_projfunc_bil_rate_type := Null;
1173 l_projfunc_bil_rate_date := Null;
1174 l_projfunc_bil_exchange_rate := Null;
1175 ELSIF (l_txn_currency_code = l_project_currency_code ) THEN
1176 l_project_bil_rate_type := Null;
1177 l_project_bil_rate_date := Null;
1178 l_project_bil_exchange_rate := Null;
1179 END IF;
1180 /* till here for Bug3068864 */
1181
1182 /* Populating Invoice attributes */
1183
1184 IF ( l_invproc_currency_code = l_projfunc_currency_code ) THEN
1185 l_invproc_currency_code := l_projfunc_currency_code;
1186 l_invproc_rate_type := l_projfunc_bil_rate_type;
1187 l_invproc_rate_date := l_projfunc_bil_rate_date;
1188 l_invproc_exchange_rate := l_projfunc_bil_exchange_rate;
1189 ELSIF (l_invproc_currency_code = l_project_currency_code ) THEN
1190 l_invproc_currency_code := l_project_currency_code;
1191 l_invproc_rate_type := l_project_bil_rate_type;
1192 l_invproc_rate_date := l_project_bil_rate_date;
1193 l_invproc_exchange_rate := l_project_bil_exchange_rate;
1194 ELSE
1195 l_invproc_currency_code := '';
1196 l_invproc_rate_type := l_funding_rate_type;
1197 l_invproc_rate_date := l_funding_rate_date;
1198 l_invproc_exchange_rate := l_funding_exchange_rate;
1199 END IF;
1200
1201 /* Populating Revenue attributes */
1202
1203 IF ( l_revproc_currency_code = l_projfunc_currency_code ) THEN
1207 l_revproc_exchange_rate := l_projfunc_bil_exchange_rate;
1204 l_revproc_currency_code := l_projfunc_currency_code;
1205 l_revproc_rate_type := l_projfunc_bil_rate_type;
1206 l_revproc_rate_date := l_projfunc_bil_rate_date;
1208 END IF;
1209
1210 /* MCB2: Removed Revenue amount and Bill amount because,these amounts are being used only for transactions */
1211 IF (pa_billing_validate.automatic_event(XD_event_type)) THEN
1212 insert into pa_events
1213 (PROJECT_ID, TASK_ID, ORGANIZATION_ID, EVENT_NUM, EVENT_TYPE, -- 1
1214 REVENUE_AMOUNT,BILL_AMOUNT,COMPLETION_DATE, REQUEST_ID, -- 2
1215 DESCRIPTION, BILL_HOLD_FLAG, REV_DIST_REJECTION_CODE, -- 3
1216 REVENUE_DISTRIBUTED_FLAG, PROGRAM_APPLICATION_ID, PROGRAM_ID, -- 4
1217 PROGRAM_UPDATE_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY, -- 5
1218 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, ATTRIBUTE_CATEGORY, -- 6
1219 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, -- 7
1220 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, -- 8
1221 BILLING_ASSIGNMENT_ID, calling_place, calling_process, -- 9
1222 EVENT_NUM_REVERSED, -- 10
1223 AUDIT_AMOUNT1,
1224 AUDIT_AMOUNT2,
1225 AUDIT_AMOUNT3,
1226 AUDIT_AMOUNT4,
1227 AUDIT_AMOUNT5,
1228 AUDIT_AMOUNT6,
1229 AUDIT_AMOUNT7,
1230 AUDIT_AMOUNT8,
1231 AUDIT_AMOUNT9,
1232 AUDIT_AMOUNT10,
1233 AUDIT_COST_BUDGET_TYPE_CODE,
1234 AUDIT_REV_BUDGET_TYPE_CODE,
1235 EVENT_ID,
1236 INVENTORY_ORG_ID,
1237 INVENTORY_ITEM_ID,
1238 QUANTITY_BILLED,
1239 UOM_CODE,
1240 UNIT_PRICE,
1241 REFERENCE1,
1242 REFERENCE2,
1243 REFERENCE3,
1244 REFERENCE4,
1245 REFERENCE5,
1246 REFERENCE6,
1247 REFERENCE7,
1248 REFERENCE8,
1249 REFERENCE9,
1250 REFERENCE10,
1251 BILL_TRANS_CURRENCY_CODE, /* These 22 columns have been added for MCB2 */
1252 BILL_TRANS_REV_AMOUNT,
1253 BILL_TRANS_BILL_AMOUNT,
1254 PROJECT_CURRENCY_CODE,
1255 PROJECT_RATE_TYPE,
1256 PROJECT_RATE_DATE,
1257 PROJECT_EXCHANGE_RATE,
1258 PROJFUNC_CURRENCY_CODE,
1259 PROJFUNC_RATE_TYPE,
1260 PROJFUNC_RATE_DATE,
1261 PROJFUNC_EXCHANGE_RATE,
1262 FUNDING_RATE_TYPE,
1263 FUNDING_RATE_DATE,
1264 FUNDING_EXCHANGE_RATE,
1265 INVPROC_CURRENCY_CODE,
1266 INVPROC_RATE_TYPE,
1267 INVPROC_RATE_DATE,
1268 INVPROC_EXCHANGE_RATE,
1269 REVPROC_CURRENCY_CODE,
1270 REVPROC_RATE_TYPE,
1271 REVPROC_RATE_DATE,
1272 REVPROC_EXCHANGE_RATE,
1273 ZERO_REVENUE_AMOUNT_FLAG, /* Funding MRC Changes */
1274 AUDIT_COST_PLAN_TYPE_ID, /* Added for Fin Plan impact */
1275 AUDIT_REV_PLAN_TYPE_ID /* Added for Fin Plan impact */
1276 )
1277 values
1278 (nvl(X_project_id, pa_billing.GlobVars.ProjectId), nvl(X_top_task_id,pa_billing.GlobVars.TaskId),
1279 XD_organization_id, event_num, XD_event_type, -- 1
1280 0,0,XD_completion_date, pa_billing.GlobVars.ReqId, -- 2
1281 XD_event_description, 'N', NULL, -- 3
1282 'N', program_application_id,program_id, -- 4
1283 sysdate, sysdate, nvl(last_updated_by,0), -- 5
1284 sysdate, nvl(created_by,0), nvl(last_update_login,0),
1285 X_attribute_category, -- 6
1286 X_attribute1, X_attribute2, X_attribute3, X_attribute4,
1287 X_attribute5, -- 7
1288 X_attribute6, X_attribute7, X_attribute8, X_attribute9,
1289 X_attribute10, -- 8
1290 pa_billing.GlobVars.BillingAssignmentId, pa_billing.GlobVars.CallingPlace,
1291 pa_billing.GlobVars.CallingProcess, -- 9
1292 X_event_num_reversed, -- 10
1293 pa_currency.round_currency_amt(X_audit_amount1),
1294 pa_currency.round_currency_amt(X_audit_amount2),
1295 pa_currency.round_currency_amt(X_audit_amount3),
1296 pa_currency.round_currency_amt(X_audit_amount4),
1297 pa_currency.round_currency_amt(X_audit_amount5),
1301 pa_currency.round_currency_amt(X_audit_amount9),
1298 pa_currency.round_currency_amt(X_audit_amount6),
1299 pa_currency.round_currency_amt(X_audit_amount7),
1300 pa_currency.round_currency_amt(X_audit_amount8),
1302 pa_currency.round_currency_amt(X_audit_amount10),
1303 X_audit_cost_budget_type_code,
1304 X_audit_rev_budget_type_code,
1305 pa_events_s.nextval,
1306 x_inventory_org_id,
1307 x_inventory_item_id,
1308 x_quantity_billed,
1309 x_uom_code,
1310 x_unit_price,
1311 x_reference1,
1312 x_reference2,
1313 x_reference3,
1314 x_reference4,
1315 x_reference5,
1316 x_reference6,
1317 x_reference7,
1318 x_reference8,
1319 x_reference9,
1320 x_reference10,
1321 l_txn_currency_code,
1322 XD_bill_trans_rev_amt,
1323 XD_bill_trans_bill_amt,
1324 l_project_currency_code,
1325 l_project_bil_rate_type,
1326 l_project_bil_rate_date,
1327 l_project_bil_exchange_rate,
1328 l_projfunc_currency_code,
1329 l_projfunc_bil_rate_type,
1330 l_projfunc_bil_rate_date,
1331 l_projfunc_bil_exchange_rate,
1332 l_funding_rate_type,
1333 l_funding_rate_date,
1334 l_funding_exchange_rate,
1335 l_invproc_currency_code,
1336 l_invproc_rate_type,
1337 l_invproc_rate_date,
1338 l_invproc_exchange_rate,
1339 l_revproc_currency_code,
1340 l_revproc_rate_type,
1341 l_revproc_rate_date,
1342 l_revproc_exchange_rate,
1343 NVL(X_zero_revenue_amount_flag, 'N'),
1344 X_audit_cost_plan_type_id,
1345 X_audit_rev_plan_type_id
1346 );
1347 ELSE RAISE EVENT_TYPE_ERROR;
1348 END IF;
1349 -- COMMIT;
1350
1351 IF g1_debug_mode = 'Y' THEN
1352 PA_MCB_INVOICE_PKG.log_message('Exiting pa_billing_pub.insert_events: ');
1353 END IF;
1354 EXCEPTION
1355 WHEN mandatory_prm_missing THEN
1356 status := pa_billing_values.get_message('MANDATORY_PRM_MISSING');
1357 l_status := 1;
1358 RAISE;
1359 WHEN invalid_id THEN
1360 status := pa_billing_values.get_message('INVALID_ID');
1361 l_status := 2;
1362 RAISE;
1363 WHEN invalid_project_event THEN
1364 status := pa_billing_values.get_message('INVALID_PROJECT_EVENT');
1365 l_status := 3;
1366 RAISE;
1367 /*WHEN event_type_error THEN commenting this for bug 3492506
1368 status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
1369 RAISE;*/
1370 WHEN null_event_type_error THEN
1371 status := pa_billing_values.get_message('NULL_EVENT_TYPE_ERROR');
1372 l_status := 4;
1373 RAISE;
1374 WHEN zero_amounts THEN
1375 status := pa_billing_values.get_message('ZERO_AMOUNTS');
1376 l_status := 5;
1377 RAISE;
1378 WHEN no_orig_event THEN
1379 status := pa_billing_values.get_message('NO_ORIG_EVENT');
1380 l_status := 6;
1381 RAISE;
1382 WHEN invalid_calling_place THEN
1383 status := pa_billing_values.get_message('INVALID_CALLING_PLACE');
1384 l_status := 7;
1385 RAISE;
1386 WHEN invalid_invent_id THEN
1387 status := pa_billing_values.get_message('INVALID_INVENT_ID');
1388 l_status := 8;
1389 RAISE;
1390 WHEN l_func_exch_rate_not_passd THEN /* Added for MCB2 */
1391 status := pa_billing_values.get_message('PA_FUNC_EXCH_RATE_NOT_PASSD');
1392 l_status := 9;
1393 RAISE;
1394 WHEN l_proj_exch_rate_not_passd THEN /* Added for MCB2 */
1395 status := pa_billing_values.get_message('PA_PROJ_EXCH_RATE_NOT_PASSD');
1396 l_status := 10;
1397 RAISE;
1398 WHEN l_fund_exch_rate_not_passd THEN /* Added for MCB2 */
1399 status := pa_billing_values.get_message('PA_FUND_EXCH_RATE_NOT_PASSD');
1400 l_status := 11;
1401 RAISE;
1402 WHEN l_func_invalid_rate_type THEN /* Added for MCB2 */
1403 status := pa_billing_values.get_message('PA_FUNC_INVALID_RATE_TYPE');
1404 l_status := 12;
1405 RAISE;
1406 WHEN l_proj_invalid_rate_type THEN /* Added for MCB2 */
1407 status := pa_billing_values.get_message('PA_PROJ_INVALID_RATE_TYPE');
1408 l_status := 13;
1409 RAISE;
1410 WHEN l_fund_invalid_rate_type THEN /* Added for MCB2 */
1411 status := pa_billing_values.get_message('PA_FUND_INVALID_RATE_TYPE');
1412 l_status := 14;
1413 RAISE;
1414 WHEN l_invalid_currency THEN /* Added for MCB2 */
1415 status := pa_billing_values.get_message('PA_CURR_NOT_VALID_BC');
1416 l_status := 15;
1417 RAISE;
1418 WHEN event_type_error THEN /* adding this here for bug 3492506 */
1419 status := pa_billing_values.get_message('EVENT_TYPE_ERROR');
1420 l_status := 16;
1421 RAISE;
1422 WHEN OTHERS THEN
1423 status := substr(SQLERRM,1,240);
1424 l_status := sqlcode;
1425 -- ROLLBACK;
1426 RAISE;
1427 END;
1428 EXCEPTION
1429 WHEN OTHERS THEN
1430 -- DBMS_OUTPUT.PUT_LINE(status);
1431 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
1432 X_error_message := status;
1433 X_status := l_status;
1434
1435 insert_message(X_inserting_procedure_name => 'pa_billing_pub.insert_event',
1436 X_attribute1 => XD_bill_trans_rev_amt,
1437 X_attribute2 => XD_bill_trans_bill_amt,
1438 X_message => status,
1439 X_status => err_status,
1440 X_error_message => err_message);
1441
1442 IF (l_status <0 OR err_status <0) THEN
1443 RAISE;
1444 END IF;
1445
1446 -- COMMIT;
1447 END insert_event;
1448
1449 function GET_MRC_FOR_FUND_FLAG return boolean
1450 is
1451 l_enabled_flag varchar2(1);
1452 begin
1453
1454 SELECT 'N' -- MRC migration to SLA
1455 INTO l_enabled_flag
1456 FROM pa_implementations;
1457
1458 /* Changed for bug 2729975*/
1459 if l_enabled_flag = 'N' then
1460 return FALSE;
1461 else
1462 return TRUE;
1463 end if;
1464 end GET_MRC_FOR_FUND_FLAG;
1465
1466 -- Following APIs added for FP_M changes
1467 -- If the Project is enabled with Top Task Customer Enabled then
1468 -- return the value as 'Y' else 'N'
1469 Function Get_Top_Task_Customer_Flag (
1470 P_Project_ID IN NUMBER
1471 )
1472 Return Varchar2
1473 IS
1474 l_Enable_Top_Task_Cust_Flag VARCHAR2(1);
1475
1476 Begin
1477 Select NVL(Enable_Top_Task_Customer_Flag, 'N')
1478 Into l_Enable_Top_Task_Cust_Flag
1479 From PA_Projects_all
1480 Where Project_ID = P_Project_ID;
1481
1482 Return l_Enable_Top_Task_Cust_Flag;
1483
1484 Exception When Others then
1485 Return 'N';
1486 End Get_Top_Task_Customer_Flag;
1487
1488
1489 -- If the Project is enabled with Override Invoice Method as Enabled then
1490 -- return the value as 'Y' else 'N'
1491 Function Get_Inv_Method_Override_Flag (
1492 P_Project_ID IN NUMBER
1493 )
1494 Return Varchar2 IS
1495
1496 l_ENABLE_TOP_TASK_INV_MTH_FLAG VARCHAR2(1);
1497
1498 Begin
1499 Select NVL(ENABLE_TOP_TASK_INV_MTH_FLAG, 'N')
1500 Into l_ENABLE_TOP_TASK_INV_MTH_FLAG
1501 From PA_Projects_all
1502 Where Project_ID = P_Project_ID;
1503
1504 Return l_ENABLE_TOP_TASK_INV_MTH_FLAG;
1505
1506 Exception When Others then
1507 Return 'N';
1508 End Get_Inv_Method_Override_Flag;
1509
1510 -- End of APIs added for FP_M changes => Customer at Top Task
1511
1512 END pa_billing_pub;