1 PACKAGE BODY pa_rev_ca AS
2 /*$Header: PAXICOSB.pls 120.1.12010000.2 2008/09/25 08:56:47 dlella ship $*/
3
4 /*****************************************************************************
5 -- Global variables to store the attribute12 - 15 columns of
6 -- pa_billing_extensions table
7 ****************************************************************************/
8
9 g_ca_event_type VARCHAR2(30);
10 g_ca_contra_event_type VARCHAR2(30);
11 g_ca_wip_event_type VARCHAR2(30);
12 g_ca_budget_type VARCHAR2(1) ;
13
14
15 /***************************************************************************
16
17 Private Procedures and Functions
18
19 ****************************************************************************/
20 --
21 -- Function to check if closing entries have been created for project and top task
22 --
23 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
24
25 Function Check_Closing_done
26 (X_project_id NUMBER,
27 X_task_id NUMBER DEFAULT NULL
28 )
29 RETURN VARCHAR2
30 IS
31 l_count INTEGER;
32 BEGIN
33
34 --
35 -- If closing entries have been created then attribute10 for pa_events
36 -- will be prefixed with CLOSE and they should not be reversed
37 --
38 -- If top task id is NULL then its project level check
39 --
40 SELECT count(*)
41 INTO l_count
42 from pa_events e
43 WHERE e.project_id = X_project_id
44 and nvl(e.task_id,0) =
45 decode(X_task_id,
46 NULL, nvl(e.task_id,0), X_task_id )
47 and e.attribute10 LIKE 'CLOSE%'
48 and NOT EXISTS
49 ( SELECT 'x'
50 from pa_events pe
51 WHERE pe.project_id = X_project_id
52 and nvl(pe.task_id,0) =
53 decode(X_task_id,
54 NULL, nvl(pe.task_id,0), X_task_id )
55 and pe.attribute10 LIKE 'REV%'
56 and pe.event_num_reversed = e.event_num
57 )
58 ;
59
60 -- If more than one row is returned then Closing entries have been created
61 --
62 IF l_count > 0 THEN
63 RETURN 'Y';
64 ELSE
65 RETURN 'N';
66 END IF;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 RETURN ('N');
71 END Check_Closing_done ;
72
73 --
74 -- Procedure that returns the revenue accrued to date for project and top task
75 --
76 Procedure GetCurrentRevenue( X_project_id NUMBER,
77 X_task_Id NUMBER DEFAULT NULL,
78 X_revenue_amount OUT NOCOPY REAL) IS
79
80 accrued_rev REAL;
81
82 BEGIN
83
84 -- Revenue that has been accrued till date.
85 -- If top task id is NULL then sum revenue at project level
86 --
87 /* Change this column from amount to projfunc_revenue_amount for MCB2 */
88
89 SELECT sum(nvl(dri.projfunc_revenue_amount,0))
90 INTO accrued_rev
91 FROM pa_draft_revenue_items dri
92 WHERE dri.project_id = X_project_id
93 AND nvl(dri.task_id,0) =
94 decode(X_task_id, NULL, nvl(dri.task_id,0), X_task_id);
95
96 X_revenue_amount := nvl(accrued_rev,0);
97 EXCEPTION
98 WHEN OTHERS THEN
99 X_revenue_amount := NULL;
100 END GetCurrentRevenue;
101
102 --
103 -- Procedure that returns the cost accrual to date for a project and top task id
104 --
105 Procedure GetCostAccrued (X_project_id NUMBER,
106 X_task_Id NUMBER DEFAULT NULL,
107 X_cost_accrued OUT NOCOPY REAL) IS
108 cost_accrued REAL := 0;
109 BEGIN
110 --
111 -- sum up the events with event type as g_ca_event_type , which is the event type
112 -- for COST ACCRUAL
113 -- If top task id is NULL them sum up at project level
114 --
115 /* Change this column from revenue_amount to projfunc_revenue_amount for MCB2 */
116 /* Bug 2956009 changed column projfunc_revenue_amount to bill_trans_rev_amount.
117 Since projfunc_revenue_amount will not be calculated till revenue process picks
118 up events created in the same run (reversing entries) ..
119 hence results in additional events getting created.
120 */
121 SELECT sum(nvl(e.bill_trans_rev_amount,0))
122 INTO cost_accrued
123 FROM pa_events e
124 where e.project_id = X_project_id
125 and nvl(e.task_id,0) =
126 decode(X_task_id,
127 NULL, nvl(e.task_id,0), X_task_id )
128 and e.event_type = g_ca_event_type
129 ;
130 -- COST ACCRUAL is stored as a negative amount since its a debit to the account
131 -- so reverse the sign to get the proper amount
132 --
133 X_cost_accrued := (-1) * nvl(cost_accrued,0) ;
134 EXCEPTION
135 WHEN OTHERS THEN
136 X_cost_accrued := NULL;
137 END GetCostAccrued;
138
139 -- Procedure that returns the cost accrual contra to date for a project and top task id
140 --
141 Procedure GetCostAccruedContra (X_project_id NUMBER,
142 X_task_Id NUMBER DEFAULT NULL,
143 X_cost_accrued OUT NOCOPY REAL) IS
144 cost_accrued REAL := 0;
145 BEGIN
146 --
147 -- sum up the events with event type as g_ca_contra_event_type , which is the
148 -- event type for COST ACCRUAL CONTRA
149 -- If top task id is NULL them sum up at project level
150 --
151 /* Change this column from revenue_amount to projfunc_revenue_amount for MCB2 */
152 /* Bug 2956009 changed column projfunc_revenue_amount to bill_trans_rev_amount.
153 Since projfunc_revenue_amount will not be calculated till revenue process picks
154 up events created in the same run (reversing entries) ..
155 hence results in additional events getting created.
156 */
157 SELECT sum(nvl(e.bill_trans_rev_amount,0))
158 INTO cost_accrued
159 FROM pa_events e
160 where e.project_id = X_project_id
161 and nvl(e.task_id,0) =
162 decode(X_task_id,
163 NULL, nvl(e.task_id,0), X_task_id )
164 and e.event_type = g_ca_contra_event_type
165 ;
166 X_cost_accrued := nvl(cost_accrued,0) ;
167 EXCEPTION
168 WHEN OTHERS THEN
169 X_cost_accrued := NULL;
170 END GetCostAccruedContra;
171 --
172 -- Procedure that returns the cost WIP amount from events that has been created as
173 -- part of closing procedure for a project and top task id
174 --
175 Procedure GetCostWIP (X_project_id NUMBER,
176 X_task_Id NUMBER DEFAULT NULL,
177 X_cost_accrued OUT NOCOPY REAL) IS
178 cost_accrued REAL := 0;
179 BEGIN
180 --
181 -- sum up the events with event type as g_ca_wip_event_type , which is the
182 -- event type for COST WIP
183 -- If top task id is NULL them sum up at project level
184 --
185 /* Change this column from revenue_amount to projfunc_revenue_amount for MCB2 */
186 /* Bug 2956009 changed column projfunc_revenue_amount to bill_trans_rev_amount.
187 Since projfunc_revenue_amount will not be calculated till revenue process picks
188 up events created in the same run (reversing entries) ..
189 hence results in additional events getting created.
190 */
191 SELECT sum(nvl(e.bill_trans_rev_amount,0))
192 INTO cost_accrued
193 FROM pa_events e
194 where e.project_id = X_project_id
195 and nvl(e.task_id,0) =
196 decode(X_task_id,
197 NULL, nvl(e.task_id,0), X_task_id )
198 and e.event_type = g_ca_wip_event_type
199 ;
200 X_cost_accrued := nvl(cost_accrued,0) ;
201 EXCEPTION
202 WHEN OTHERS THEN
203 X_cost_accrued := NULL;
204 END GetCostWIP;
205 --
206 -- Procedure that returns the cost budget and revenue budget amount
207 -- This procedure is same as the public api pa_billing_pub.get_budget_amount
208 -- the only difference in this case the raw cost is used for cost budget amount
209 --
210 PROCEDURE get_budget_amount( X2_project_id NUMBER,
211 X2_task_id NUMBER DEFAULT NULL,
212 X2_revenue_amount OUT NOCOPY REAL,
213 X2_cost_amount OUT NOCOPY REAL,
214 P_cost_budget_type_code IN VARCHAR2 DEFAULT NULL,
215 P_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
216 P_cost_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
217 P_rev_plan_type_id IN NUMBER DEFAULT NULL, /* Added for Fin plan impact */
218 X_cost_budget_type_code OUT NOCOPY VARCHAR2,
219 X_rev_budget_type_code OUT NOCOPY VARCHAR2,
220 X_error_message OUT NOCOPY VARCHAR2,
221 X_status OUT NOCOPY NUMBER
222 ) IS
223
224 -- local variables for budget codes
225 l_cost_budget_type_code VARCHAR2(30) ;
226 l_rev_budget_type_code VARCHAR2(30) ;
227 l_cost_budget_status_code VARCHAR2(1) ;
228 l_rev_budget_status_code VARCHAR2(1) ;
229 dummy CHAR(1);
230 err_msg VARCHAR2(240);
231 err_status NUMBER;
232 status VARCHAR2(240);
233 l_status NUMBER;
234 l_cost_budget_version_id NUMBER;
235 l_rev_budget_version_id NUMBER;
236 l_raw_cost_total REAL := 0;
237 l_revenue_total REAL := 0;
238 l_quantity_total NUMBER;
239 l_burdened_cost_total NUMBER;
240 l_err_code NUMBER;
241 l_err_stage VARCHAR2(30);
242 l_err_stack VARCHAR2(630);
243
244 /* Added for Fin Plan Impact */
245 l_cost_plan_type_id NUMBER;
246 l_rev_plan_type_id NUMBER;
247 l_cost_plan_version_id NUMBER;
248 l_rev_plan_version_id NUMBER;
249 /* Till here */
250
251 invalid_cost_budget_code EXCEPTION;
252 invalid_rev_budget_code EXCEPTION;
253 rev_budget_not_baselined EXCEPTION;
254 cost_budget_not_baselined EXCEPTION;
255
256 BEGIN
257 X_status := 0;
258 X_error_message := NULL;
259 BEGIN
260 -- If user doesnt provide the budget get the default value from biling extensions
261 --
262
263 IF (P_cost_budget_type_code IS NULL OR P_rev_budget_type_code IS NULL
264 OR P_cost_plan_type_id IS NULL OR P_rev_plan_type_id IS NULL ) /* Added for Fin plan impact */
265 THEN
266
267 SELECT DECODE(P_cost_budget_type_code,NULL,default_cost_budget_type_code, P_cost_budget_type_code),
268 DECODE(P_rev_budget_type_code,NULL,default_rev_budget_type_code,P_rev_budget_type_code),
269 DECODE(P_cost_plan_type_id,NULL,default_cost_plan_type_id,
270 P_cost_plan_type_id), /* Added for Fin plan impact */
271 DECODE(P_rev_plan_type_id,NULL,default_rev_plan_type_id,
272 P_rev_plan_type_id) /* Added for Fin plan impact */
273 INTO l_cost_budget_type_code,
274 l_rev_budget_type_code,
275 l_cost_plan_type_id,
276 l_rev_plan_type_id
277 FROM pa_billing_extensions
278 WHERE billing_extension_id=pa_billing.GetBillingExtensionId;
279
280 END IF;
281
282
283 -- The plan code should be a valid code and of the right version type
284 -- If invalid then process will check for budget code
285 /* Added this select for Fin plan Impact */
286 BEGIN
287 SELECT 'x'
288 INTO dummy
289 FROM dual
290 WHERE EXISTS( SELECT *
291 FROM pa_fin_plan_types_b f
292 WHERE f.fin_plan_type_id=l_cost_plan_type_id );
293
294 EXCEPTION
295 WHEN NO_DATA_FOUND THEN
296
297 -- The budget code should be a valid code and of the right amount code
298 -- If invalid then raise appropriate exception
299
300 BEGIN
301
302 SELECT 'x'
303 INTO dummy
304 FROM pa_budget_types
305 WHERE budget_type_code = l_cost_budget_type_code
306 AND budget_amount_code = 'C';
307
308 EXCEPTION
309 WHEN NO_DATA_FOUND THEN
310 raise invalid_cost_budget_code;
311 END;
312
313 END; /* End of newly added code for Fin plan Impact */
314
315
316
317 /* Added this select for Fin plan Impact */
318 BEGIN
319 SELECT 'x'
320 INTO dummy
321 FROM dual
322 WHERE EXISTS( SELECT *
323 FROM pa_fin_plan_types_b f
324 WHERE f.fin_plan_type_id=l_rev_plan_type_id );
325
326 EXCEPTION
327 WHEN NO_DATA_FOUND THEN
328
329 -- The budget code should be a valid code and of the right amount code
330 -- If invalid then raise appropriate exception
331
332 BEGIN
333
334 SELECT 'x'
335 INTO dummy
336 FROM pa_budget_types
337 WHERE budget_type_code = l_rev_budget_type_code
338 AND budget_amount_code = 'R';
339
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 raise invalid_rev_budget_code;
343 END ;
344
345 END; /* End of newly added code for Fin plan Impact *
346
347
348
349 -- Get the budget version id for cost and revenue plan
350 -- Changed to use api pa_budget_utils.get_project_task_totals
351
352 /* Added this select for Fin plan Impact */
353 BEGIN
354
355 SELECT v.budget_version_id
356 INTO l_cost_plan_version_id
357 FROM pa_budget_versions v
358 WHERE v.project_id = X2_project_id
359 AND v.current_flag = 'Y'
360 AND v.budget_status_code = 'B'
361 AND v.fin_plan_type_id = l_cost_plan_type_id
362 AND v.version_type IN ('COST','ALL');
363
364 EXCEPTION
365 WHEN NO_DATA_FOUND THEN
366 --
367 -- get the budget version id for cost and revenue budget
368 -- call api pa_budget_utils.get_project_task_totals for budget amounts
369 --
370
371 BEGIN
372 SELECT budget_version_id
373 INTO l_cost_budget_version_id
374 FROM pa_budget_versions pbv
375 WHERE project_id = X2_project_id
376 AND budget_type_code = l_cost_budget_type_code
377 AND budget_status_code = 'B'
378 AND current_flag = 'Y';
379
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 raise cost_budget_not_baselined;
383 END;
384
385 END; /* End of newly added code for Fin plan Impact */
386
387 l_cost_budget_version_id := NVL(l_cost_plan_version_id,l_cost_budget_version_id);
388
389
390
391 /* Added this select for Fin plan Impact */
392 BEGIN
393
394 SELECT v.budget_version_id
395 INTO l_rev_plan_version_id
396 FROM pa_budget_versions v
397 WHERE v.project_id = X2_project_id
398 AND v.current_flag = 'Y'
399 AND v.budget_status_code = 'B'
400 AND v.fin_plan_type_id = l_rev_plan_type_id
401 AND v.version_type IN ('REVENUE','ALL');
402
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 --
406 -- get the budget version id for cost and revenue budget
407 -- Changed to use api pa_budget_utils.get_project_task_totals
408 --
409
410 BEGIN
411
412 SELECT budget_version_id
413 INTO l_rev_budget_version_id
414 FROM pa_budget_versions pbv
415 WHERE project_id = X2_project_id
416 AND budget_type_code = l_rev_budget_type_code
417 AND budget_status_code = 'B'
418 AND current_flag = 'Y';
419
420 EXCEPTION
421 WHEN NO_DATA_FOUND THEN
422 raise rev_budget_not_baselined;
423 END;
424
425 END; /* End of newly added code for Fin plan Impact */
426
427 l_rev_budget_version_id := NVL(l_rev_plan_version_id,l_rev_budget_version_id);
428
429
430 -- Call api to get cost budget amount
431 --
432 pa_budget_utils.get_project_task_totals
433 (l_cost_budget_version_id ,
434 x2_task_id ,
435 l_quantity_total,
436 l_raw_cost_total ,
437 l_burdened_cost_total ,
438 l_revenue_total ,
439 l_err_code ,
440 l_err_stage,
441 l_err_stack );
442
443 X2_cost_amount := pa_currency.round_currency_amt(l_burdened_cost_total);
444
445 -- Call api to get revenue budget amount
446 --
447 pa_budget_utils.get_project_task_totals
448 (l_rev_budget_version_id ,
449 x2_task_id ,
450 l_quantity_total,
451 l_raw_cost_total ,
452 l_burdened_cost_total ,
453 l_revenue_total ,
454 l_err_code ,
455 l_err_stage,
456 l_err_stack );
457
458 X2_revenue_amount := pa_currency.round_currency_amt(l_revenue_total);
459
460 X_cost_budget_type_code := l_cost_budget_type_code;
461 X_rev_budget_type_code := l_rev_budget_type_code;
462
463 -- If any exception then raise it to the calling pl/sql block
464 --
465 EXCEPTION
466 WHEN invalid_cost_budget_code THEN
467 status := pa_billing_values.get_message('INVALID_COST_BUDGET_TYPE');
468 l_status := 1;
469 RAISE_APPLICATION_ERROR(-20101,status);
470 WHEN invalid_rev_budget_code THEN
471 status := pa_billing_values.get_message('INVALID_REV_BUDGET_TYPE');
472 l_status := 2;
473 RAISE_APPLICATION_ERROR(-20101,status);
474 WHEN rev_budget_not_baselined THEN
475 status := pa_billing_values.get_message('REV_BUDGET_NOT_BASELINED');
476 l_status := 3;
477 RAISE_APPLICATION_ERROR(-20101,status);
478 WHEN cost_budget_not_baselined THEN
479 status := pa_billing_values.get_message('COST_BUDGET_NOT_BASELINED');
480 l_status := 4;
481 RAISE_APPLICATION_ERROR(-20101,status);
482 WHEN OTHERS THEN
483 status := substr(SQLERRM,1,240);
484 l_status := sqlcode;
485 RAISE;
486 END;
487
488 EXCEPTION
489 WHEN OTHERS THEN
490 -- DBMS_OUTPUT.PUT_LINE(status);
491 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
492 X2_cost_amount := NULL;
493 X2_revenue_amount := NULL;
494 X_cost_budget_type_code := NULL;
495 X_rev_budget_type_code := NULL;
496 ----
497
498 X_error_message := status;
499 X_status := l_status;
500
501 pa_billing_pub.insert_message
502 (X_inserting_procedure_name =>'pa_rev_ca.get_budget_amount',
503 X_attribute1 => l_cost_budget_type_code,
504 X_attribute2 => l_rev_budget_type_code,
505 X_message => status,
506 X_error_message=>err_msg,
507 X_status=>err_status);
508
509 IF (l_status < 0 OR NVL(err_status,0) <0) THEN
510 RAISE;
511 END IF;
512
513 END get_budget_amount;
514
515 --
516 -- Procedure that creates reverses closing entries (if any) when project
517 -- status is changed back from PENDING CLOSE to ACTIVE
518 --
519 Procedure ReverseClosingEntries
520 (X_project_id NUMBER,
521 X_task_id NUMBER DEFAULT NULL,
522 X_request_id NUMBER DEFAULT NULL,
523 X_error_message OUT NOCOPY VARCHAR2,
524 X_status OUT NOCOPY NUMBER
525 )
526 IS
527 event_description VARCHAR2(240);
528 l_event_set_id VARCHAR2(150);
529 l_error_message VARCHAR2(240);
530 l_status NUMBER;
531 BEGIN
532 -- Select the events from pa_events which have been created for closing
533 -- entries.The attribute10 column will be prefixed with CLOSE
534 -- Also check if there are no reversing events already created , the
535 -- reversing events can be identified from column attribute10 prefixed
536 -- with REV and column event_num_reversed will have the number of the
537 -- event reversed
538 -- For each row do the following
539 FOR r_rec IN
540 (
541 SELECT *
542 from pa_events e
543 WHERE e.project_id = X_project_id
544 and nvl(e.task_id,0) =
545 decode(X_task_id,
546 NULL, nvl(e.task_id,0), X_task_id )
547 and e.attribute10 LIKE 'CLOSE%'
548 and NOT EXISTS
549 ( SELECT 'x'
550 from pa_events pe
551 WHERE pe.project_id = X_project_id
552 and nvl(pe.task_id,0) =
553 decode(X_task_id,
554 NULL, nvl(pe.task_id,0), X_task_id )
555 and pe.attribute10 LIKE 'REV%'
556 and pe.event_num_reversed = e.event_num
557 )
558 )
559 LOOP
560
561 -- Event description will show the event number reversed by this event
562 --
563 Event_Description := 'reversing event num = ' || r_rec.event_num;
564
565 --
566 -- event_set_id is stored in attribute10 column of pa_events table
567 -- this helps in identifying why the event was created i.e. reversing 'REV'
568 -- closing 'CLOSE' .
569 -- event_set_id also helps in identifying the events which were created
570 -- at the same time , i.e. events for Cost Accrual , Cost WIP and Cost Accrual
571 -- contra will have the same event_set_id
572 --
573 l_event_set_id := 'REV-' ||x_project_id||'-'||nvl(x_task_id,0)||'-'
574 ||nvl(x_request_id,0);
575
576 --
577 -- Use the public api to create reversing events for each row
578 --
579 pa_billing_pub.insert_event (
580 X_rev_amt => (-1) * r_rec.revenue_amount,
581 X_bill_amt => 0,
582 X_event_type =>r_rec.event_type ,
583 X_event_description => event_description,
584 X_event_num_reversed => r_rec.event_num,
585 X_attribute10 => l_event_set_id,
586 X_audit_amount1 => r_rec.audit_amount1,
587 X_audit_amount2 => r_rec.audit_amount2,
588 X_audit_amount3 => r_rec.audit_amount3,
589 X_audit_amount4 => r_rec.audit_amount4,
590 X_audit_cost_budget_type_code => r_rec.audit_cost_budget_type_code,
591 X_audit_rev_budget_type_code => r_rec.audit_rev_budget_type_code,
592 X_error_message =>l_error_message,
593 X_status => l_status);
594
595 IF l_status <> 0 THEN
596 X_status := l_status ;
597 EXIT ;
598 END IF;
599
600
601 END LOOP;
602 EXCEPTION
603 WHEN OTHERS THEN
604 X_status := NULL;
605 X_error_message := NULL;
606 END ReverseClosingEntries;
607
608 --
609 -- Procedure that creates the cost accrual , cost accrual contra entries
610 -- when the project status is 'ACTIVE'
611 --
612 Procedure CreateNormalEntries (
613 X_project_id NUMBER,
614 X_top_task_id NUMBER DEFAULT NULL,
615 X_revenue_amount NUMBER,
616 X_budget_revenue NUMBER,
617 X_budget_cost NUMBER,
618 X_cost_accrued NUMBER,
619 X_audit_cost_budget_type_code VARCHAR2,
620 X_audit_rev_budget_type_code VARCHAR2,
621 X_request_id NUMBER DEFAULT NULL,
622 X_cost_plan_type_id IN NUMBER , /* Added for Fin plan impact */
623 X_rev_plan_type_id IN NUMBER , /* Added for Fin plan impact */
624 X_error_message OUT NOCOPY VARCHAR2,
625 X_status OUT NOCOPY NUMBER
626 )
627 IS
628 cost_accrual REAL := 0;
629 l_status NUMBER := 0;
630 l_error_message VARCHAR2(240);
631 l_event_set_id VARCHAR2(150);
632 event_description VARCHAR2(240);
633 l_currency_code VARCHAR2(15);
634
635 /* Declaring varible for MCB2 */
636 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
637 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
638 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
639 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
640 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
641 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
642 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
643 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
644 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
645 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
646 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
647 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
648 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
649 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
650 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
651 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
652 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
653 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
654 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
655 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
656 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
657 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
658 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
659 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
660 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
661 l_return_status VARCHAR2(30);
662 l_msg_count NUMBER;
663 l_msg_data VARCHAR2(30);
664
665
666 BEGIN
667
668
669 /* This is commented because now PFC and Project currency can be diffrent for MCB2 */
670 /* l_currency_code := pa_multi_currency_txn.get_proj_curr_code_sql(X_project_id); */
671
672 /* To get the Project functional currency for Project, calling get default procedure for MCB2 */
673
674 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
675 p_project_id => X_project_id,
676 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
677 x_baseline_funding_flag => l_baseline_funding_flag,
678 x_revproc_currency_code => l_revproc_currency_code,
679 x_invproc_currency_type => l_invproc_currency_type,
680 x_invproc_currency_code => l_invproc_currency_code,
681 x_project_currency_code => l_project_currency_code,
682 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
683 x_project_bil_rate_type => l_project_bil_rate_type,
684 x_project_bil_rate_date => l_project_bil_rate_date,
685 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
686 x_projfunc_currency_code => l_projfunc_currency_code,
687 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
688 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
689 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
690 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
691 x_funding_rate_date_code => l_funding_rate_date_code,
692 x_funding_rate_type => l_funding_rate_type,
693 x_funding_rate_date => l_funding_rate_date,
694 x_funding_exchange_rate => l_funding_exchange_rate,
695 x_return_status => l_return_status,
696 x_msg_count => l_msg_count,
697 x_msg_data => l_msg_data);
698
699 l_currency_code := l_projfunc_currency_code;
700
701 -- Cost accrual calculation formula
702 --
703 -- Cost Accrual = (Accrued Revenue / Budgeted Revenue) * Budgeted Cost
704 -- - cost_accrued
705 --
706 --
707 cost_accrual := nvl(X_revenue_amount,0) /nvl(X_budget_revenue,0)
708 * nvl(X_budget_cost,0) - nvl(X_cost_accrued,0);
709 --
710 -- Event description will show the calculation
711 --
712 /* 2958833 changed get_format_mask call to create mask for 30 char instead of 15 */
713 Event_Description := 'Cost Accrual '|| ' = ' ||
714 to_char(X_revenue_amount,fnd_currency.get_format_mask(l_currency_code,30))
715 || '/ ' ||
716 to_char(X_budget_revenue,fnd_currency.get_format_mask(l_currency_code,30))
717 || ' * '||
718 to_char(X_budget_cost,fnd_currency.get_format_mask(l_currency_code,30))
719 || ' - ' ||
720 to_char(nvl(X_cost_accrued,0),fnd_currency.get_format_mask(l_currency_code,30));
721
722 --
723 -- If the cost accrual is not zero then create the events
724 --
725
726 IF (nvl(cost_accrual,0) <> 0) THEN
727
728 --
729 -- Create the Cost accrual contra entries
730 --
731 l_event_set_id := 'CONTRA-' ||x_project_id||'-'||nvl(x_top_task_id,0)||'-'
732 ||nvl(x_request_id,0);
733 /** public api to insert event **/
734 pa_billing_pub.insert_event (
735 X_rev_amt => cost_accrual,
736 X_bill_amt => 0,
737 X_event_type =>g_ca_contra_event_type ,
738 X_event_description => event_description,
739 X_attribute10 => l_event_set_id,
740 X_audit_amount1 => X_revenue_amount,
741 X_audit_amount2 => X_budget_revenue,
742 X_audit_amount3 => X_budget_cost,
743 X_audit_amount4 => X_cost_accrued,
744 X_audit_cost_budget_type_code => X_audit_cost_budget_type_code,
745 X_audit_rev_budget_type_code => X_audit_rev_budget_type_code,
746 X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
747 X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
748 X_error_message =>l_error_message,
749 X_status => l_status);
750
751 --
752 -- If the previous event is successfuly created then create the next event
753 --
754 IF l_status = 0 THEN
755
756 -- Create the Cost accrual entries
757
758 l_event_set_id := 'NORMAL-' ||x_project_id||'-'||nvl(x_top_task_id,0)||'-'
759 ||nvl(x_request_id,0);
760 --
761 -- In order to debit the cost accrual account the event is created with
762 -- a negative of the cost accrual amount
763 --
764 pa_billing_pub.insert_event (
765 X_rev_amt => (-1) * cost_accrual,
766 X_bill_amt => 0,
767 X_event_description => event_description,
768 X_event_type => g_ca_event_type ,
769 X_attribute10 => l_event_set_id,
770 X_audit_amount1 => X_revenue_amount,
771 X_audit_amount2 => X_budget_revenue,
772 X_audit_amount3 => X_budget_cost,
773 X_audit_amount4 => X_cost_accrued,
774 X_audit_cost_budget_type_code =>x_audit_cost_budget_type_code,
775 X_audit_rev_budget_type_code => x_audit_rev_budget_type_code,
776 X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
777 X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
778 X_error_message =>l_error_message,
779 X_status => l_status
780 );
781 END IF;
782 END IF;
783 EXCEPTION
784 WHEN OTHERS THEN
785 X_error_message :=NULL;
786 X_status := NULL;
787 END CreateNormalEntries;
788 --
789 -- Procedure that creates closing entries when project status is changed
790 -- to 'PENDING CLOSE'
791 --
792 Procedure CreateClosingEntries (
793 X_project_id NUMBER,
794 X_top_task_id NUMBER DEFAULT NULL,
795 X_revenue_amount NUMBER,
796 X_budget_revenue NUMBER,
797 X_budget_cost NUMBER,
798 X_cost_accrued NUMBER,
799 X_audit_cost_budget_type_code VARCHAR2,
800 X_audit_rev_budget_type_code VARCHAR2,
801 X_request_id NUMBER DEFAULT NULL,
802 X_cost_plan_type_id IN NUMBER , /* Added for Fin plan impact */
803 X_rev_plan_type_id IN NUMBER , /* Added for Fin plan impact */
804 X_error_message OUT NOCOPY VARCHAR2,
805 X_status OUT NOCOPY NUMBER
806 )
807 IS
808 cost_accrual REAL := 0;
809 cost_accrual_contra REAL := 0;
810 cost_WIP REAL := 0;
811 l_status NUMBER := 0;
812 l_error_message VARCHAR2(240);
813 l_event_set_id VARCHAR2(150);
814 event_description VARCHAR2(240);
815 l_currency_code VARCHAR2(15);
816
817 /* Declaring varible for MCB2 */
818 l_multi_currency_billing_flag pa_projects_all.MULTI_CURRENCY_BILLING_FLAG%TYPE;
819 l_baseline_funding_flag pa_projects_all.BASELINE_FUNDING_FLAG%TYPE;
820 l_revproc_currency_code pa_projects_all.revproc_currency_code%TYPE;
821 l_revproc_rate_type pa_events.revproc_rate_type%TYPE;
822 l_revproc_rate_date pa_events.revproc_rate_date%TYPE;
823 l_revproc_exchange_rate pa_events.revproc_exchange_rate%TYPE;
824 l_invproc_currency_code pa_events.invproc_currency_code%TYPE;
825 l_invproc_currency_type pa_projects_all.invproc_currency_type%TYPE;
826 l_invproc_rate_type pa_events.invproc_rate_type%TYPE;
827 l_invproc_rate_date pa_events.invproc_rate_date%TYPE;
828 l_invproc_exchange_rate pa_events.invproc_exchange_rate%TYPE;
829 l_project_currency_code pa_projects_all.project_currency_code%TYPE;
830 l_project_bil_rate_date_code pa_projects_all.project_bil_rate_date_code%TYPE;
831 l_project_bil_rate_type pa_projects_all.project_bil_rate_type%TYPE;
832 l_project_bil_rate_date pa_projects_all.project_bil_rate_date%TYPE;
833 l_project_bil_exchange_rate pa_projects_all.project_bil_exchange_rate%TYPE;
834 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
835 l_projfunc_bil_rate_date_code pa_projects_all.projfunc_bil_rate_date_code%TYPE;
836 l_projfunc_bil_rate_type pa_projects_all.projfunc_bil_rate_type%TYPE;
837 l_projfunc_bil_rate_date pa_projects_all.projfunc_bil_rate_date%TYPE;
838 l_projfunc_bil_exchange_rate pa_projects_all.projfunc_bil_exchange_rate%TYPE;
839 l_funding_rate_date_code pa_projects_all.funding_rate_date_code%TYPE;
840 l_funding_rate_type pa_projects_all.funding_rate_type%TYPE;
841 l_funding_rate_date pa_projects_all.funding_rate_date%TYPE;
842 l_funding_exchange_rate pa_projects_all.funding_exchange_rate%TYPE;
843 l_return_status VARCHAR2(30);
844 l_msg_count NUMBER;
845 l_msg_data VARCHAR2(30);
846
847 BEGIN
848 --
849 -- Gets the total cost WIP amount from the cost distribution lines
850 -- line_type = R , indicates raw cost lines only
851 -- If budge type = 'R' is set in the attribute15 column
852 -- of pa_events then use raw cost.
853 -- If budge type = 'B' is set in the attribute15 column
854 -- of pa_events then use burdened cost.
855 --
856 /* This is commented because now PFC and Project currency can be diffrent for MCB2 */
857 /* l_currency_code := pa_multi_currency_txn.get_proj_curr_code_sql(X_project_id); */
858
859 /* To get the Project functional currency for Project, calling get default procedure for MCB2 */
860
861 PA_MULTI_CURRENCY_BILLING.get_project_defaults (
862 p_project_id => X_project_id,
863 x_multi_currency_billing_flag => l_multi_currency_billing_flag,
864 x_baseline_funding_flag => l_baseline_funding_flag,
865 x_revproc_currency_code => l_revproc_currency_code,
866 x_invproc_currency_type => l_invproc_currency_type,
867 x_invproc_currency_code => l_invproc_currency_code,
868 x_project_currency_code => l_project_currency_code,
869 x_project_bil_rate_date_code => l_project_bil_rate_date_code,
870 x_project_bil_rate_type => l_project_bil_rate_type,
871 x_project_bil_rate_date => l_project_bil_rate_date,
872 x_project_bil_exchange_rate => l_project_bil_exchange_rate,
873 x_projfunc_currency_code => l_projfunc_currency_code,
874 x_projfunc_bil_rate_date_code => l_projfunc_bil_rate_date_code,
875 x_projfunc_bil_rate_type => l_projfunc_bil_rate_type,
876 x_projfunc_bil_rate_date => l_projfunc_bil_rate_date,
877 x_projfunc_bil_exchange_rate => l_projfunc_bil_exchange_rate,
878 x_funding_rate_date_code => l_funding_rate_date_code,
879 x_funding_rate_type => l_funding_rate_type,
880 x_funding_rate_date => l_funding_rate_date,
881 x_funding_exchange_rate => l_funding_exchange_rate,
882 x_return_status => l_return_status,
883 x_msg_count => l_msg_count,
884 x_msg_data => l_msg_data);
885
886 l_currency_code := l_projfunc_currency_code;
887
888 /* According to MCB2 changes this columns will be in PFC */
889 /*Change for burden shedule enhancement*/
890 SELECT sum(decode(g_ca_budget_type,'R',nvl(cdl.amount,0),
891 (nvl(cdl.burdened_cost,0)+nvl(cdl.project_burdened_change,0))))
892 INTO cost_WIP
893 FROM pa_cost_distribution_lines_all cdl,
894 pa_expenditure_items_all ei,
895 pa_tasks t
896 WHERE t.project_id = X_project_id
897 AND (t.top_task_id = X_top_task_id
898 OR X_top_task_id IS NULL)
899 AND ei.task_id = t.task_id
900 AND ei.Project_ID = X_project_id -- Perf Bug 2695266
901 AND cdl.expenditure_item_id = ei.expenditure_item_id
902 AND cdl.line_type = 'R'
903 ;
904
905 --
906 -- Get the cost accrual contra to date
907 --
908 pa_rev_ca.GetCostAccruedContra (X_project_id => x_project_id,
909 X_task_id => X_top_task_id,
910 X_cost_accrued => cost_accrual_contra );
911
912 cost_accrual_contra := (-1) * cost_accrual_contra ;
913
914 --
915 -- The adjustment entry for any difference in Cost WIP and cost accrual contra
916 --
917 -- Closing Cost Accrual Entry = -1* ( Closing Cost-WIP + Closing Cost Accrual Contra )
918 --
919 cost_accrual := -1 * (cost_WIP + cost_accrual_contra);
920 /* 2958833 changed get_format_mask call to create mask for 30 char instead of 15 */
921 Event_Description := 'Closing Balance in Cost WIP = ' ||
922 to_char(cost_WIP,fnd_currency.get_format_mask(l_currency_code,30));
923
924
925 l_event_set_id := 'CLOSE-' ||x_project_id||'-'||nvl(x_top_task_id,0)||'-'
926 ||nvl(x_request_id,0);
927
928 --
929 -- Create event to close the cost WIP account
930 --
931 /** public api to insert event **/
932
933 IF cost_WIP <> 0 THEN /* Added for bug 3788835: if project doesnot have EIs then this would be 0 */
934 pa_billing_pub.insert_event (
935 X_rev_amt => cost_WIP,
936 X_bill_amt => 0,
937 X_event_type => g_ca_wip_event_type ,
938 X_event_description => event_description,
939 X_attribute10 => l_event_set_id,
940 X_audit_amount1 => X_revenue_amount,
941 X_audit_amount2 => X_budget_revenue,
942 X_audit_amount3 => X_budget_cost,
943 X_audit_amount4 => X_cost_accrued,
944 X_audit_cost_budget_type_code => X_audit_cost_budget_type_code,
945 X_audit_rev_budget_type_code => X_audit_rev_budget_type_code,
946 X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
947 X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
948 X_error_message =>l_error_message,
949 X_status => l_status);
950 END IF; /* End if added for bug 3788835 */
951
952 IF l_status = 0 THEN
953
954 l_event_set_id := 'CLOSE-' ||x_project_id||'-'||nvl(x_top_task_id,0)||'-'
955 ||nvl(x_request_id,0);
956
957 /* 2958833 changed get_format_mask call to create mask for 30 char instead of 15 */
958 Event_Description := 'Closing Balance in CA contra ' ||
959 to_char(cost_accrual_contra,fnd_currency.get_format_mask(l_currency_code,30));
960
961 --
962 -- Create event to close the cost accrual contra account
963 --
964 pa_billing_pub.insert_event (
965 X_rev_amt => cost_accrual_contra,
966 X_bill_amt => 0,
967 X_event_description => event_description,
968 X_event_type => g_ca_contra_event_type ,
969 X_attribute10 => l_event_set_id,
970 X_audit_amount1 => X_revenue_amount,
971 X_audit_amount2 => X_budget_revenue,
972 X_audit_amount3 => X_budget_cost,
973 X_audit_amount4 => X_cost_accrued,
974 X_audit_cost_budget_type_code =>x_audit_cost_budget_type_code,
975 X_audit_rev_budget_type_code => x_audit_rev_budget_type_code,
976 X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
977 X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
978 X_error_message =>l_error_message,
979 X_status => l_status
980 );
981 END IF;
982
983 IF (l_status = 0 AND cost_accrual <> 0) THEN
984
985 --
986 -- Create event for cost accrual for the adjustment amount
987 --
988 l_event_set_id := 'CLOSE-' ||x_project_id||'-'||nvl(x_top_task_id,0)||'-'
989 ||nvl(x_request_id,0);
990
991 /* 2958833 changed get_format_mask call to create mask for 30 char instead of 15 */
992 Event_Description := 'Closing Balance in CA = (-1) * ' ||
993 to_char(cost_WIP,fnd_currency.get_format_mask(l_currency_code,30))
994 || ' + ' ||
995 to_char(cost_accrual_contra,fnd_currency.get_format_mask(l_currency_code,30));
996
997 pa_billing_pub.insert_event (
998 X_rev_amt => cost_accrual,
999 X_bill_amt => 0,
1000 X_event_description => event_description,
1001 X_event_type => g_ca_event_type,
1002 X_attribute10 => l_event_set_id,
1003 X_audit_amount1 => X_revenue_amount,
1004 X_audit_amount2 => X_budget_revenue,
1005 X_audit_amount3 => X_budget_cost,
1006 X_audit_amount4 => X_cost_accrued,
1007 X_audit_cost_budget_type_code =>x_audit_cost_budget_type_code,
1008 X_audit_rev_budget_type_code => x_audit_rev_budget_type_code,
1009 X_audit_cost_plan_type_id => X_cost_plan_type_id, /* Added for fin plan impact */
1010 X_audit_rev_plan_type_id => X_rev_plan_type_id, /* Added for fin plan impact */
1011 X_error_message =>l_error_message,
1012 X_status => l_status
1013 );
1014
1015 END IF;
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 X_error_message :=NULL;
1019 X_status := NULL;
1020 END CreateClosingEntries;
1021
1022 --
1023 -- This procedure calculates the cost WIP for a project / top task
1024 --
1025 PROCEDURE GetCost ( p_project_id IN NUMBER
1026 ,p_task_id IN NUMBER
1027 ,x_cost_wip_amount OUT NOCOPY VARCHAR2
1028 )
1029 IS
1030 l_cost_wip_amount VARCHAR2(200);
1031 BEGIN
1032
1033 --
1034 -- Gets the total cost WIP amount from the cost distribution lines
1035 -- line_type = R , indicates raw cost lines only
1036 -- If budge type = 'R' is set in the attribute15 column
1037 -- of pa_events then use raw cost.
1038 -- If budge type = 'B' is set in the attribute15 column
1039 -- of pa_events then use burdened cost.
1040 --
1041 /* According to MCB2 changes this columns will be in PFC */
1042 SELECT sum(decode(g_ca_budget_type,'R',nvl(cdl.amount,0),
1043 (nvl(cdl.burdened_cost,0)+nvl(cdl.project_burdened_change,0))))
1044 INTO l_cost_wip_amount
1045 FROM pa_cost_distribution_lines_all cdl,
1046 pa_expenditure_items_all ei,
1047 pa_tasks t
1048 WHERE t.project_id = p_project_id
1049 AND (t.top_task_id = p_task_id
1050 OR p_task_id IS NULL)
1051 AND ei.task_id = t.task_id
1052 AND ei.Project_ID = P_project_id -- Perf Bug 2695266
1053 AND cdl.expenditure_item_id = ei.expenditure_item_id
1054 AND cdl.line_type = 'R'
1055 ;
1056 x_cost_wip_amount := l_cost_wip_amount;
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 x_cost_wip_amount := NULL;
1060 END GetCost;
1061
1062 /****************************************************************************
1063
1064 Public Procedures
1065
1066 ****************************************************************************/
1067
1068 -- Main procedure that calculates the cost accrual
1069 -- and creates the events for cost accrual
1070
1071 PROCEDURE calc_ca_amt
1072 ( X_project_id IN NUMBER,
1073 X_top_task_id IN NUMBER DEFAULT NULL,
1074 X_calling_process IN VARCHAR2 DEFAULT NULL,
1075 X_calling_place IN VARCHAR2 DEFAULT NULL,
1076 X_amount IN NUMBER DEFAULT NULL,
1077 X_percentage IN NUMBER DEFAULT NULL,
1078 X_rev_or_bill_date IN DATE DEFAULT NULL,
1079 X_billing_assignment_id IN NUMBER DEFAULT NULL,
1080 X_billing_extension_id IN NUMBER DEFAULT NULL,
1081 X_request_id IN NUMBER DEFAULT NULL
1082 )
1083 IS
1084
1085 budget_revenue REAL := 0;
1086 budget_cost REAL := 0;
1087 invoice_amount REAL := 0;
1088 revenue_amount REAL := 0;
1089 cost_amount REAL := 0;
1090 revenue REAL := 0;
1091 cost_accrued REAL := 0;
1092
1093 event_description VARCHAR2(240);
1094
1095 l_cost_budget_type_code VARCHAR2(30);
1096 l_rev_budget_type_code VARCHAR2(30);
1097
1098 l_status NUMBER;
1099 l_error_message VARCHAR2(240);
1100
1101 l_project_status VARCHAR2(30);
1102 l_ca_event_type VARCHAR2(30);
1103 l_ca_contra_event_type VARCHAR2(30);
1104 l_ca_wip VARCHAR2(30);
1105 l_ca_budget_type VARCHAR2(1);
1106
1107 /* Added for Fin Plan impact */
1108 l_cost_plan_type_id NUMBER;
1109 l_rev_plan_type_id NUMBER;
1110 /* till here */
1111
1112 BEGIN
1113
1114 l_status := 0;
1115 l_error_message := NULL;
1116
1117 -- select the event types associated with the billing extension
1118 -- these event types will be used while creating events for
1119 -- cost accrual , cost accrual contra and cost WIP
1120
1121 SELECT attribute12 , attribute13 , attribute14 , attribute15
1122 INTO g_ca_event_type ,g_ca_contra_event_type,g_ca_wip_event_type,g_ca_budget_type
1123 FROM pa_billing_extensions
1124 WHERE billing_extension_id = X_billing_extension_id;
1125
1126 IF g1_debug_mode = 'Y' THEN
1127 PA_MCB_INVOICE_PKG.log_message('Before pa_billing_extn_params_v select pa_rev_ca.calc_ca_amt :');
1128 END IF;
1129 /* Added for bug 2649456.Not handling exception intentionaly because if it is coming,
1130 it will be data issue */
1131 BEGIN
1132 SELECT default_cost_plan_type_id,default_rev_plan_type_id
1133 INTO l_cost_plan_type_id,l_rev_plan_type_id
1134 FROM pa_billing_extn_params_v;
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 IF g1_debug_mode = 'Y' THEN
1138 PA_MCB_INVOICE_PKG.log_message('Error from pa_billing_extn_params_v pa_rev_ca.calc_ca_amt :'||SQLERRM);
1139 END IF;
1140 RAISE;
1141 END;
1142 /* till here */
1143
1144 IF g1_debug_mode = 'Y' THEN
1145 PA_MCB_INVOICE_PKG.log_message('pa billing params v.cost_plan_type_id pa_rev_ca.calc_ca_amt :'||l_cost_plan_type_id);
1146 PA_MCB_INVOICE_PKG.log_message('pa billing params v.rev_plan_type_id pa_rev_ca.calc_ca_amt :'||l_rev_plan_type_id);
1147 END IF;
1148
1149
1150 -- gets the cost and revenue budget amounts
1151 -- if budget type = 'R' then call the procedure defined in this package
1152 -- else use the public api in package pa_billing_pub
1153 --
1154 IF g_ca_budget_type = 'R' THEN
1155 pa_rev_ca.get_budget_amount(
1156 X2_project_id => X_project_id,
1157 X2_task_id => X_top_task_id,
1158 X2_revenue_amount => budget_revenue,
1159 X2_cost_amount => budget_cost,
1160 X_cost_budget_type_code => l_cost_budget_type_code,
1161 X_rev_budget_type_code => l_rev_budget_type_code,
1162 P_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
1163 P_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
1164 X_error_message => l_error_message,
1165 X_status => l_status);
1166 ELSE
1167 pa_billing_pub.get_budget_amount(
1168 X2_project_id => X_project_id,
1169 X2_task_id => X_top_task_id,
1170 X2_revenue_amount => budget_revenue,
1171 X2_cost_amount => budget_cost,
1172 X_cost_budget_type_code => l_cost_budget_type_code,
1173 X_rev_budget_type_code => l_rev_budget_type_code,
1174 P_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
1175 P_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
1176 X_error_message =>l_error_message,
1177 X_status => l_status);
1178 END IF;
1179
1180
1181 -- Gets the revenue accrued to date
1182 pa_rev_ca.GetCurrentRevenue( X_project_id => x_project_id,
1183 X_task_id => X_top_task_id,
1184 X_revenue_amount => revenue_amount);
1185
1186 -- Select the project system status code
1187
1188 SELECT pps.project_system_status_code
1189 INTO l_project_status
1190 FROM pa_projects_all ppa , pa_project_statuses pps
1191 WHERE ppa.project_id = x_project_id
1192 AND ppa.project_status_code = pps.project_status_code
1193 AND pps.status_type = 'PROJECT';
1194
1195 /****************************************************************************
1196 When the project system status is ACTIVE , Calculate the cost accrual and
1197 create events for cost accrual and cost accrual contra.
1198
1199 Another condition is if the project is made ACTIVE after PENDING CLOSE
1200 then reverse the closing entries before creating actual entries.
1201
1202 When the project system status is PENDING CLOSE ,
1203 - Calculate the entries and create events as done for project status ACTIVE
1204
1205 - For closing entries calculate the adjustment amount for any difference
1206 between cost WIP and cost accrual accounts .
1207 create reversing events for cost WIP , cost accrual contra and if the
1208 adjustment amount is not zero then create a cost accrual event for the
1209 amount
1210
1211 Please note that all events will be created at project level for project
1212 level funding and at top task level for top task level funding.
1213
1214 *****************************************************************************/
1215
1216 IF (budget_revenue <> 0) THEN
1217
1218 IF l_project_status <> 'PENDING_CLOSE' THEN
1219
1220 -- Reverse the closing entries (if not allready reversed)
1221 pa_rev_ca.ReverseClosingEntries
1222 (X_project_id => x_project_id,
1223 X_task_id => x_top_task_id,
1224 X_error_message => l_error_message,
1225 X_status => l_status
1226 );
1227
1228 -- Get the cost accrual to date
1229 --
1230 pa_rev_ca.GetCostAccrued
1231 (X_project_id => x_project_id,
1232 X_task_id => X_top_task_id,
1233 X_cost_accrued => cost_accrued );
1234
1235 -- Create Normal Entries
1236 --
1237 pa_rev_ca.CreateNormalEntries (
1238 X_project_id => x_project_id,
1239 X_top_task_id => x_top_task_id,
1240 X_request_id => x_request_id,
1241 X_revenue_amount => revenue_amount,
1242 X_budget_revenue => budget_revenue,
1243 X_budget_cost => budget_cost,
1244 X_cost_accrued => cost_accrued,
1245 X_audit_cost_budget_type_code => l_cost_budget_type_code,
1246 X_audit_rev_budget_type_code => l_rev_budget_type_code,
1247 X_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
1248 X_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
1249 X_error_message =>l_error_message,
1250 X_status => l_status);
1251
1252 ELSE
1253 -- Status is pending close
1254 -- Check if closing entries created allready
1255 IF (check_closing_done (X_project_id , X_top_task_id) = 'N') THEN
1256
1257 -- Get the cost accrual to date
1258 pa_rev_ca.GetCostAccrued
1259 (X_project_id => x_project_id,
1260 X_task_id => X_top_task_id,
1261 X_cost_accrued => cost_accrued );
1262
1263 -- Create Normal entries
1264 pa_rev_ca.CreateNormalEntries (
1265 X_project_id => x_project_id,
1266 X_top_task_id => x_top_task_id,
1267 X_request_id => x_request_id,
1268 X_revenue_amount => revenue_amount,
1269 X_budget_revenue => budget_revenue,
1270 X_budget_cost => budget_cost,
1271 X_cost_accrued => cost_accrued,
1272 X_audit_cost_budget_type_code =>l_cost_budget_type_code,
1273 X_audit_rev_budget_type_code => l_rev_budget_type_code,
1274 X_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
1275 X_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
1276 X_error_message =>l_error_message,
1277 X_status => l_status);
1278
1279 -- Get the cost accrual to date
1280 pa_rev_ca.GetCostAccrued
1281 (X_project_id => x_project_id,
1282 X_task_id => X_top_task_id,
1283 X_cost_accrued => cost_accrued );
1284
1285 -- Create closing entries
1286 pa_rev_ca.CreateClosingEntries
1287 (
1288 X_project_id => x_project_id,
1289 X_top_task_id => x_top_task_id,
1290 X_request_id => x_request_id,
1291 X_revenue_amount => revenue_amount,
1292 X_budget_revenue => budget_revenue,
1293 X_budget_cost => budget_cost,
1294 X_cost_accrued => cost_accrued,
1295 X_audit_cost_budget_type_code => l_cost_budget_type_code,
1296 X_audit_rev_budget_type_code => l_rev_budget_type_code,
1297 X_cost_plan_type_id => l_cost_plan_type_id, /* Added for fin plan impact */
1298 X_rev_plan_type_id => l_rev_plan_type_id, /* Added for fin plan impact */
1299 X_error_message =>l_error_message,
1300 X_status => l_status);
1301 END IF;
1302 END IF;
1303 END IF;
1304
1305 EXCEPTION
1306 WHEN OTHERS THEN
1307 -- DBMS_OUTPUT.PUT_LINE(SQLERRM);
1308 RAISE;
1309
1310 END calc_ca_amt;
1311
1312
1313 -- Procedure that sets the psi columns for cost accrual
1314 -- This procedure will be called from the psi client extension
1315 -- pa_client_extn_status.get_psi_cols
1316 -- Uncomment the call to this procedure in pa_client_extn_status.get_psi_cols
1317 -- in order to invoke this procedure
1318 --
1319 PROCEDURE get_psi_cols (
1320 x_project_id IN NUMBER
1321 , x_task_id IN NUMBER
1322 , x_resource_list_member_id IN NUMBER
1323 , x_cost_budget_type_code IN VARCHAR2
1324 , x_rev_budget_type_code IN VARCHAR2
1325 , x_status_view IN VARCHAR2
1326 , x_pa_install IN VARCHAR2
1327 , x_derived_col_1 OUT NOCOPY VARCHAR2
1328 , x_derived_col_2 OUT NOCOPY VARCHAR2
1329 , x_derived_col_3 OUT NOCOPY VARCHAR2
1330 , x_derived_col_4 OUT NOCOPY NUMBER
1331 , x_derived_col_5 OUT NOCOPY NUMBER
1332 , x_derived_col_6 OUT NOCOPY NUMBER
1333 , x_derived_col_7 OUT NOCOPY NUMBER
1334 , x_derived_col_8 OUT NOCOPY NUMBER
1335 , x_derived_col_9 OUT NOCOPY NUMBER
1336 , x_derived_col_10 OUT NOCOPY NUMBER
1337 , x_derived_col_11 OUT NOCOPY NUMBER
1338 , x_derived_col_12 OUT NOCOPY NUMBER
1339 , x_derived_col_13 OUT NOCOPY NUMBER
1340 , x_derived_col_14 OUT NOCOPY NUMBER
1341 , x_derived_col_15 OUT NOCOPY NUMBER
1342 , x_derived_col_16 OUT NOCOPY NUMBER
1343 , x_derived_col_17 OUT NOCOPY NUMBER
1344 , x_derived_col_18 OUT NOCOPY NUMBER
1345 , x_derived_col_19 OUT NOCOPY NUMBER
1346 , x_derived_col_20 OUT NOCOPY NUMBER
1347 , x_derived_col_21 OUT NOCOPY NUMBER
1348 , x_derived_col_22 OUT NOCOPY NUMBER
1349 , x_derived_col_23 OUT NOCOPY NUMBER
1350 , x_derived_col_24 OUT NOCOPY NUMBER
1351 , x_derived_col_25 OUT NOCOPY NUMBER
1352 , x_derived_col_26 OUT NOCOPY NUMBER
1353 , x_derived_col_27 OUT NOCOPY NUMBER
1354 , x_derived_col_28 OUT NOCOPY NUMBER
1355 , x_derived_col_29 OUT NOCOPY NUMBER
1356 , x_derived_col_30 OUT NOCOPY NUMBER
1357 , x_derived_col_31 OUT NOCOPY NUMBER
1358 , x_derived_col_32 OUT NOCOPY NUMBER
1359 , x_derived_col_33 OUT NOCOPY NUMBER
1360 , p_revenue_ptd IN NUMBER
1361 , p_revenue_itd IN NUMBER)
1362 IS
1363 l_raw_cost_itd NUMBER := 0;
1364 l_raw_cost_ptd NUMBER := 0;
1365 l_cost_accrual_itd NUMBER := 0;
1366 l_cost_accrual_ptd NUMBER := 0;
1367 l_accounted_cost_WIP_itd NUMBER := 0;
1368 l_accounted_cost_WIP_ptd NUMBER := 0;
1369 l_gross_profit NUMBER := 0;
1370 l_project_type VARCHAR2(20);
1371 l_funding_flag VARCHAR2(1);
1372 l_ca_event_type VARCHAR2(30);
1373 l_ca_contra_event_type VARCHAR2(30);
1374 l_ca_wip_event_type VARCHAR2(30);
1375 l_ca_budget_type VARCHAR2(1);
1376 l_cost_accrual_flag VARCHAR2(1);
1377 l_revenue_ptd NUMBER := 0;
1378
1379 BEGIN
1380 --------------------------- Cost Accrual Derived Columns --------------------
1381 -- If the project has cost accrual enabled then proceed
1382 -- else dont do anything
1383 --
1384 pa_rev_ca.Check_if_Cost_Accrual ( x_project_id
1385 ,l_cost_accrual_flag
1386 ,l_funding_flag
1387 ,l_ca_event_type
1388 ,l_ca_contra_event_type
1389 ,l_ca_wip_event_type
1390 ,l_ca_budget_type
1391 );
1392
1393 IF l_cost_accrual_flag = 'Y' THEN
1394
1395 --
1396 -- Check if project level or Task level funding
1397 -- If project level funding then dont show at top task level
1398 -- else if top task level funding then show project and top task level
1399 -- The dervied column will always be null for columns other than
1400 -- top task level
1401 --
1402 -- Also the amounts will be shown only if accumulation process has included
1403 -- the revenue and cost distribution lines for accumulation
1404 --
1405
1406 IF ( x_status_view = 'PROJECTS')
1407 OR
1408 ( x_status_view = 'TASKS' and l_funding_flag = 'N')
1409 THEN
1410
1411 --
1412 -- Gets the inception to-date cost incurred from the cost distribution lines
1413 -- line_type = R , indicates raw cost lines only
1414 -- If budge type = 'R' is set in the attribute15 column
1415 -- of pa_events then use raw cost.
1416 -- If budge type = 'B' is set in the attribute15 column
1417 -- of pa_events then use burdened cost.
1418 --
1419 /* According to MCB2 changes this columns will be in PFC */
1420 SELECT sum(decode(l_ca_budget_type,'R',nvl(cdl.amount,0),
1421 (nvl(burdened_cost,0)+nvl(cdl.project_burdened_change,0))))
1422 INTO l_raw_cost_itd
1423 FROM pa_cost_distribution_lines_all cdl,
1424 pa_expenditure_items_all ei,
1425 pa_tasks t
1426 WHERE t.project_id = x_project_id
1427 AND (nvl(t.top_task_id,0) =
1428 decode(x_status_view , 'TASKS', x_task_id , nvl(t.top_task_id,0)))
1429 AND ei.task_id = t.task_id
1430 AND ei.Project_ID = X_project_id -- Perf Bug 2695266
1431 AND cdl.expenditure_item_id = ei.expenditure_item_id
1432 AND cdl.line_type = 'R'
1433 AND cdl.resource_accumulated_flag = 'Y'
1434 ;
1435
1436 --
1437 -- Gets the current reporting period to-date cost incurred from the
1438 -- cost distribution lines line_type = R , indicates raw cost lines only
1439 -- If budge type = 'R' is set in the attribute15 column
1440 -- of pa_events then use raw cost.
1441 -- If budge type = 'B' is set in the attribute15 column
1442 -- of pa_events then use burdened cost.
1443 --
1444 /* According to MCB2 changes this columns will be in PFC */
1445 SELECT sum(decode(l_ca_budget_type,'R',nvl(cdl.amount,0),
1446 (nvl(burdened_cost,0)+nvl(cdl.project_burdened_change,0))))
1447 INTO l_raw_cost_ptd
1448 FROM pa_cost_distribution_lines_all cdl,
1449 pa_expenditure_items_all ei,
1450 pa_tasks t,
1451 pa_periods pp
1452 WHERE pp.current_pa_period_flag = 'Y'
1453 AND TRUNC(cdl.pa_date) BETWEEN pp.start_date AND pp.end_date
1454 AND t.project_id = x_project_id
1455 AND (nvl(t.top_task_id,0) =
1456 decode(x_status_view , 'TASKS', x_task_id , nvl(t.top_task_id,0)))
1457 AND ei.task_id = t.task_id
1458 AND ei.Project_ID = X_project_id -- Perf Bug 2695266
1459 AND cdl.expenditure_item_id = ei.expenditure_item_id
1460 AND cdl.line_type = 'R'
1461 AND cdl.resource_accumulated_flag = 'Y'
1462 ;
1463
1464 --
1465 -- Gets the inception to-date closing cost WIP and closing cost accrual amounts
1466 -- cost WIP is identified by ca_wip_event_type
1467 -- and cost accrual by ca_event_type
1468 --
1469
1470 /* Changed this column from amount to projfunc_revenue_amount for MCB2 */
1471 SELECT sum(decode(pe.event_type,l_ca_wip_event_type,
1472 nvl(erdl.projfunc_revenue_amount,0),0)),
1473 sum(decode(pe.event_type,l_ca_event_type,
1474 nvl(erdl.projfunc_revenue_amount,0),0))
1475 INTO l_accounted_cost_WIP_itd,
1476 l_cost_accrual_itd
1477 FROM pa_events pe, pa_cust_event_rdl_all erdl,
1478 pa_draft_revenues_all dr
1479 WHERE pe.event_num = erdl.event_num
1480 AND pe.project_id = erdl.project_id
1481 AND nvl(pe.task_id,0) = nvl(erdl.task_id,0)
1482 AND nvl(pe.task_id,0) =
1483 decode(x_status_view , 'TASKS', x_task_id , nvl(pe.task_id,0))
1484 AND pe.project_id = x_project_id
1485 AND erdl.draft_revenue_num = dr.draft_revenue_num
1486 AND erdl.project_id = dr.project_id
1487 AND pe.event_type IN (l_ca_wip_event_type , l_ca_event_type)
1488 AND dr.resource_accumulated_flag = 'Y'
1489 ;
1490 --
1491 -- Gets the current reporting period to-date closing cost WIP and closing cost accrual
1492 -- amounts
1493 -- cost WIP is identified by ca_wip_event_type
1494 -- and cost accrual by ca_event_type
1495 --
1496 /* Changed this column from amount to projfunc_revenue_amount for MCB2 */
1497 SELECT sum(decode(pe.event_type,l_ca_wip_event_type,
1498 nvl(erdl.projfunc_revenue_amount,0),0)),
1499 sum(decode(pe.event_type,l_ca_event_type,
1500 nvl(erdl.projfunc_revenue_amount,0),0))
1501 INTO l_accounted_cost_WIP_ptd,
1502 l_cost_accrual_ptd
1503 FROM pa_events pe, pa_cust_event_rdl_all erdl,
1504 pa_draft_revenues_all dr , pa_periods pp /* Bug# 2197991 */
1505 WHERE pp.current_pa_period_flag = 'Y'
1506 -- AND TRUNC(dr.pa_date) = pp.end_date
1507 AND TRUNC(dr.pa_date) BETWEEN pp.start_date AND pp.end_date -- Modified for PA/GL period enhancements
1508 AND pe.event_num = erdl.event_num
1509 AND pe.project_id = erdl.project_id
1510 AND nvl(pe.task_id,0) = nvl(erdl.task_id,0)
1511 AND nvl(pe.task_id,0) =
1512 decode(x_status_view , 'TASKS', x_task_id , nvl(pe.task_id,0))
1513 AND pe.project_id = x_project_id
1514 AND erdl.draft_revenue_num = dr.draft_revenue_num
1515 AND erdl.project_id = dr.project_id
1516 AND pe.event_type IN (l_ca_wip_event_type , l_ca_event_type)
1517 AND dr.resource_accumulated_flag = 'Y'
1518 ;
1519
1520 -- Cost accrual is a debit entry , so its created with a negative sign
1521 -- to get the proper amount reverse the sign
1522 --
1523 l_cost_accrual_itd := (-1)*l_cost_accrual_itd;
1524 l_cost_accrual_ptd := (-1)*l_cost_accrual_ptd;
1525
1526 -- gets the revenue accrued in the current reporting period to-date
1527 --
1528 /* Changed this column from amount to projfunc_revenue_amount for MCB2 */
1529 SELECT sum(nvl(dri.projfunc_revenue_amount,0))
1530 INTO l_revenue_ptd
1531 FROM pa_draft_revenue_items dri,
1532 pa_draft_revenues_all dr,
1533 pa_periods pp /* Bug# 2197991 */
1534 WHERE dri.project_id = x_project_id
1535 AND nvl(dri.task_id,0) =
1536 decode(x_status_view,
1537 'TASKS', x_task_id , nvl(dri.task_id,0))
1538 AND dri.draft_revenue_num = dr.draft_revenue_num
1539 AND dri.project_id = dr.project_id
1540 -- AND TRUNC(dr.pa_date) = pp.end_date
1541 AND TRUNC(dr.pa_date) BETWEEN pp.start_date AND pp.end_date -- Modified for PA/GL period enhancements
1542 AND dr.resource_accumulated_flag = 'Y'
1543 AND pp.current_pa_period_flag = 'Y';
1544
1545 -- Assuming columns 28-33 have been setup in the following order is psi column setup
1546 -- column 28 : Cost WIP inception to date
1547 -- column 29 : Cost WIP period to date
1548 -- column 30 : Cost Accrual inception to date
1549 -- column 31 : Cost Accrual period to date
1550 -- column 32 : Margin inception to date
1551 -- column 33 : Margin inception to date
1552
1553 -- Cost WIP amounts
1554 x_derived_col_28 := l_raw_cost_itd - nvl(l_accounted_cost_WIP_itd,0); --Added NVL for Bug#6666921
1555 x_derived_col_29 := l_raw_cost_ptd - nvl(l_accounted_cost_WIP_ptd,0); --Added NVL for Bug#6666921
1556
1557 -- Cost Accrual amounts
1558 x_derived_col_30 := l_cost_accrual_itd;
1559 x_derived_col_31 := l_cost_accrual_ptd;
1560
1561 -- Margin amoounts
1562 x_derived_col_32 := p_revenue_itd - l_cost_accrual_itd;
1563 x_derived_col_33 := l_revenue_ptd - l_cost_accrual_ptd;
1564
1565 END IF;
1566
1567 END IF;
1568
1569 EXCEPTION
1570 WHEN OTHERS THEN
1571 x_derived_col_1 := NULL;
1572 x_derived_col_2 := NULL;
1573 x_derived_col_3 := NULL;
1574 x_derived_col_4 := NULL;
1575 x_derived_col_5 := NULL;
1576 x_derived_col_6 := NULL;
1577 x_derived_col_7 := NULL;
1578 x_derived_col_8 := NULL;
1579 x_derived_col_9 := NULL;
1580 x_derived_col_10 := NULL;
1581 x_derived_col_11 := NULL;
1582 x_derived_col_12 := NULL;
1583 x_derived_col_13 := NULL;
1584 x_derived_col_14 := NULL;
1585 x_derived_col_15 := NULL;
1586 x_derived_col_16 := NULL;
1587 x_derived_col_17 := NULL;
1588 x_derived_col_18 := NULL;
1589 x_derived_col_19 := NULL;
1590 x_derived_col_20 := NULL;
1591 x_derived_col_21 := NULL;
1592 x_derived_col_22 := NULL;
1593 x_derived_col_23 := NULL;
1594 x_derived_col_24 := NULL;
1595 x_derived_col_25 := NULL;
1596 x_derived_col_26 := NULL;
1597 x_derived_col_27 := NULL;
1598 x_derived_col_28 := NULL;
1599 x_derived_col_29 := NULL;
1600 x_derived_col_30 := NULL;
1601 x_derived_col_31 := NULL;
1602 x_derived_col_32 := NULL;
1603 x_derived_col_33 := NULL;
1604 END get_psi_cols;
1605
1606 -- Procedure that checks pre-requisites before closing a project that has
1607 -- cost accrual enabled
1608 -- This procedure will be called from the client extension
1609 -- pa_client_extn_proj_status.verify_project_status_change
1610 -- Please uncomment the call to this procedure in the above procedure to invoke this
1611 -- procedure
1612
1613 PROCEDURE Verify_Project_Status_CA
1614 (x_calling_module IN VARCHAR2
1615 ,X_project_id IN NUMBER
1616 ,X_old_proj_status_code IN VARCHAR2
1617 ,X_new_proj_status_code IN VARCHAR2
1618 ,X_project_type IN VARCHAR2
1619 ,X_project_start_date IN DATE
1620 ,X_project_end_date IN DATE
1621 ,X_public_sector_flag IN VARCHAR2
1622 ,X_attribute_category IN VARCHAR2
1623 ,X_attribute1 IN VARCHAR2
1624 ,X_attribute2 IN VARCHAR2
1625 ,X_attribute3 IN VARCHAR2
1626 ,X_attribute4 IN VARCHAR2
1627 ,X_attribute5 IN VARCHAR2
1628 ,X_attribute6 IN VARCHAR2
1629 ,X_attribute7 IN VARCHAR2
1630 ,X_attribute8 IN VARCHAR2
1631 ,X_attribute9 IN VARCHAR2
1632 ,X_attribute10 IN VARCHAR2
1633 ,x_pm_product_code IN VARCHAR2
1634 ,x_err_code OUT NOCOPY NUMBER
1635 ,x_warnings_only_flag OUT NOCOPY VARCHAR2
1636 )
1637 IS
1638 l_funding_flag VARCHAR2(1);
1639 l_ca_event_type VARCHAR2(30);
1640 l_ca_contra_event_type VARCHAR2(30);
1641 l_ca_wip_event_type VARCHAR2(30);
1642 l_ca_budget_type VARCHAR2(1);
1643 l_cost_accrual_flag VARCHAR2(1);
1644 l_cost_amount NUMBER;
1645 l_ca_contra_amount NUMBER;
1646 l_ca_wip_amount NUMBER;
1647 l_err_msgname VARCHAR2(30);
1648 l_new_system_status_code VARCHAR2(30);
1649 l_old_system_status_code VARCHAR2(30);
1650 l_err_code NUMBER;
1651
1652 BEGIN
1653
1654 -- Check the system status for the new project status code
1655 -- If system status is CLOSED then perform the checks
1656 --
1657 select project_system_status_code
1658 into l_new_system_status_code
1659 from pa_project_statuses
1660 where project_status_code = x_new_proj_status_code
1661 and status_type = 'PROJECT';
1662
1663 select project_system_status_code
1664 into l_old_system_status_code
1665 from pa_project_statuses
1666 where project_status_code = x_old_proj_status_code
1667 and status_type = 'PROJECT';
1668
1669 -- IF l_new_system_status_code = 'CLOSED' THEN
1670
1671 IF pa_utils2.IsProjectClosed(l_new_system_status_code) = 'Y' THEN
1672
1673 -- If project has cost accrual enabled then perform the checks
1674 -- else ignore the checks
1675 --
1676 pa_rev_ca.Check_if_Cost_Accrual ( x_project_id
1677 ,l_cost_accrual_flag
1678 ,l_funding_flag
1679 ,l_ca_event_type
1680 ,l_ca_contra_event_type
1681 ,l_ca_wip_event_type
1682 ,l_ca_budget_type
1683 );
1684
1685 IF l_cost_accrual_flag = 'Y' THEN
1686
1687 ------------------------------------------------------------------------
1688 -- Check 1.
1689 ------------------------------------------------------------------------
1690 -- There should be no balance in the cost wip account
1691 -- i.e.the cost WIP should have the same amount
1692 -- as the cost incurred to date
1693 -- There could be a difference in the amounts if
1694 -- a. Closing entries are not present
1695 -- b. New costs are incurred after closing entries have been generated
1696 -------------------------------------------------------------------------
1697 -- Get the cost incurred to date
1698 pa_rev_ca.GetCost(x_project_id,NULL,l_cost_amount);
1699
1700 -- Get the closing cost WIP
1701 pa_rev_ca.GetCostWIP(x_project_id,NULL,l_ca_wip_amount);
1702
1703 IF l_cost_amount <> l_ca_wip_amount THEN
1704
1705 -- Add message to the message stack
1706 -- Set the error code
1707
1708 l_err_msgname := 'PA_REV_CA_COST_WIP_BALANCE';
1709 fnd_message.set_name('PA', l_err_msgname);
1710 fnd_msg_pub.add;
1711
1712 l_err_code := 11;
1713
1714 END IF;
1715
1716 ------------------------------------------------------------------------
1717 -- Check 2
1718 ------------------------------------------------------------------------
1719 -- The closing entries for cost accrual must be generated.
1720 -- When cost accrual closing entries are generated , the cost accrual
1721 -- contra account is set to zero
1722 ------------------------------------------------------------------------
1723
1724 -- Get the cost accrued contra to date
1725 pa_rev_ca.GetCostAccruedContra(x_project_id,NULL,l_ca_contra_amount);
1726
1727 IF l_ca_contra_amount <> 0 THEN
1728
1729 -- Add message to the message stack
1730 -- Set the error code
1731
1732 l_err_msgname := 'PA_REV_CA_NO_CLOSING_ENTRIES';
1733 fnd_message.set_name('PA', l_err_msgname);
1734 fnd_msg_pub.add;
1735
1736 l_err_code := 11;
1737
1738 END IF;
1739
1740 --------------------------------------------------------------------
1741 -- Check 3
1742 --------------------------------------------------------------------
1743 -- Check if the project status was set to CLOSE only after it
1744 -- was set to PENDING CLOSE
1745 --------------------------------------------------------------------
1746
1747 -- IF ( l_new_system_status_code = 'CLOSED'
1748
1749 IF ( pa_utils2.IsProjectClosed(l_new_system_status_code) = 'Y'
1750 AND l_old_system_status_code <> 'PENDING_CLOSE') THEN
1751
1752 -- Add message to the message stack
1753 -- Set the error code
1754
1755 l_err_msgname := 'PA_REV_CA_INVALID_STATUS_CHNG';
1756 fnd_message.set_name('PA', l_err_msgname);
1757 fnd_msg_pub.add;
1758
1759 l_err_code := 11;
1760
1761 END IF;
1762
1763 END IF; -- Cost accrual flag
1764
1765 END IF; -- Status = CLOSED
1766 x_err_code := l_err_code;
1767 EXCEPTION
1768 WHEN OTHERS THEN
1769 x_err_code := NULL;
1770 x_warnings_only_flag := NULL;
1771 END Verify_Project_Status_CA;
1772
1773 --
1774 -- Procedure that checks if project has cost accrual and sets the
1775 -- variables from attribute columns 11-15 of billing extension
1776 --
1777 PROCEDURE Check_if_Cost_Accrual ( p_project_id IN NUMBER
1778 ,x_cost_accrual_flag IN OUT NOCOPY VARCHAR2
1779 ,x_funding_flag IN OUT NOCOPY VARCHAR2
1780 ,x_ca_event_type IN OUT NOCOPY VARCHAR2
1781 ,x_ca_contra_event_type IN OUT NOCOPY VARCHAR2
1782 ,x_ca_wip_event_type IN OUT NOCOPY VARCHAR2
1783 ,x_ca_budget_type IN OUT NOCOPY VARCHAR2
1784 )
1785 IS
1786
1787 l_Project_Type VARCHAR2(100);
1788 l_Check_For_Proj_Type NUMBER;
1789 l_Org_ID NUMBER;
1790 l_funding_flag VARCHAR2(1);
1791 l_funding_flag1 VARCHAR2(1);
1792 l_cost_accrual_flag VARCHAR2(1);
1793 l_ca_event_type VARCHAR2(150);
1794 l_ca_contra_event_type VARCHAR2(150);
1795 l_ca_wip_event_type VARCHAR2(150);
1796 l_ca_budget_type VARCHAR2(150);
1797 BEGIN
1798 l_cost_accrual_flag := x_cost_accrual_flag;
1799 l_funding_flag := x_funding_flag;
1800 l_ca_event_type := x_ca_event_type;
1801 l_ca_contra_event_type := x_ca_contra_event_type;
1802 l_ca_wip_event_type := x_ca_wip_event_type;
1803 l_ca_budget_type := x_ca_budget_type;
1804
1805 l_cost_accrual_flag := 'N';
1806
1807 Begin
1808 select 'Y',
1809 nvl(p.project_level_funding_flag ,'X') ,
1810 be.attribute12,
1811 be.attribute13,
1812 be.attribute14,
1813 be.attribute15
1814 INTO l_cost_accrual_flag,
1815 l_funding_flag,
1816 l_ca_event_type,
1817 l_ca_contra_event_type,
1818 l_ca_wip_event_type,
1819 l_ca_budget_type
1820 from pa_billing_extensions be,
1821 pa_billing_assignments_all bea,
1822 pa_projects_all p
1823 where p.project_id = p_project_id
1824 and bea.active_flag = 'Y'
1825 and bea.billing_extension_id = be.billing_extension_id
1826 and be.attribute11 = 'COST-ACCRUAL'
1827 and bea.project_id = p_project_id
1828 order by be.processing_order, bea.billing_assignment_id;
1829 Exception when no_data_found then
1830 l_Check_For_Proj_Type := 1;
1831 End;
1832 IF l_Check_For_Proj_Type = 1 THEN
1833 Begin
1834 Select Project_type, Org_ID , NVL(PROJECT_LEVEL_FUNDING_FLAG,'X')
1835 INTO l_Project_Type, l_Org_ID, l_funding_flag1
1836 from PA_PROJECTS_ALL
1837 where Project_ID = P_Project_ID;
1838 Exception when no_data_found then
1839 l_cost_accrual_flag := 'N';
1840 l_Check_For_Proj_Type := 0;
1841 End;
1842 If l_Check_For_Proj_Type = 1 THEN
1843 Begin
1844 select 'Y',
1845 l_funding_flag1,
1846 be.attribute12,
1847 be.attribute13,
1848 be.attribute14,
1849 be.attribute15
1850 INTO l_cost_accrual_flag,
1851 l_funding_flag,
1852 l_ca_event_type,
1853 l_ca_contra_event_type,
1854 l_ca_wip_event_type,
1855 l_ca_budget_type
1856 from pa_billing_extensions be,
1857 pa_billing_assignments_all bea
1858 where bea.active_flag = 'Y'
1859 and bea.billing_extension_id = be.billing_extension_id
1860 and be.attribute11 = 'COST-ACCRUAL'
1861 and bea.project_type = l_Project_Type
1862 and bea.org_id = l_Org_ID
1863 order by be.processing_order, bea.billing_assignment_id;
1864 Exception When No_Data_Found then
1865 l_cost_accrual_flag := 'N';
1866 End;
1867 End If;
1868 End IF;
1869
1870 If l_cost_accrual_flag = 'Y' then
1871 g_ca_event_type := l_ca_event_type;
1872 g_ca_contra_event_type := l_ca_contra_event_type;
1873 g_ca_wip_event_type := l_ca_wip_event_type;
1874 g_ca_budget_type := l_ca_budget_type;
1875 End If;
1876
1877 x_ca_event_type := l_ca_event_type;
1878 x_ca_contra_event_type := l_ca_contra_event_type;
1879 x_ca_wip_event_type := l_ca_wip_event_type;
1880 x_ca_budget_type := l_ca_budget_type;
1881 x_cost_accrual_flag := l_cost_accrual_flag;
1882 x_funding_flag := l_funding_flag;
1883 EXCEPTION
1884 WHEN OTHERS THEN
1885 x_ca_event_type := NULL;
1886 x_ca_contra_event_type := NULL;
1887 x_ca_wip_event_type := NULL;
1888 x_ca_budget_type := NULL;
1889 x_cost_accrual_flag := NULL;
1890 x_funding_flag := NULL;
1891
1892 END Check_if_Cost_Accrual;
1893
1894 END pa_rev_ca;
[Home] [Help]