1 package body PA_COST_PLUS1 as
2 -- $Header: PAXCCPFB.pls 120.2.12000000.4 2007/05/02 09:42:33 haananth ship $
3
4 NO_DATA_FOUND_ERR number := 100;
5
6
7 /* Added code for 2798971 */
8 procedure get_indirect_cost_import_sum1(org_id IN number,
9 c_base IN varchar2,
10 rate_sch_rev_id IN number,
11 direct_cost IN number,
12 direct_cost_denom IN number,
13 direct_cost_acct IN number,
14 direct_cost_project IN number,
15 precision IN number,
16 indirect_cost_sum IN OUT NOCOPY number,
17 indirect_cost_denom_sum IN OUT NOCOPY number,
18 indirect_cost_acct_sum IN OUT NOCOPY number,
19 indirect_cost_project_sum IN OUT NOCOPY number,
20 l_projfunc_currency_code IN varchar2,
21 l_project_currency_code IN varchar2,
22 l_acct_currency_code IN varchar2 default null,
23 l_denom_currency_code IN varchar2,
24 status IN OUT NOCOPY number,
25 stage IN OUT NOCOPY number)
26 IS
27
28 BEGIN
29
30 status := 0;
31 stage := 100;
32 /*========================================================+
33 | 21-MAY-03 Burdening Enhancements. |
34 | Added Cost Base join to pa_ind_compiled_sets |
35 +========================================================*/
36 SELECT SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((direct_cost * icpm.compiled_multiplier),
37 l_projfunc_currency_code)),
38 SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((direct_cost_denom * icpm.compiled_multiplier),
39 l_denom_currency_code)),
40 SUM(PA_CURRENCY.round_currency_amt(direct_cost_acct * icpm.compiled_multiplier)),
41 SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((direct_cost_project * icpm.compiled_multiplier),
42 l_project_currency_code))
43 into indirect_cost_sum,
44 indirect_cost_denom_sum,
45 indirect_cost_acct_sum,
46 indirect_cost_project_sum
47 FROM pa_ind_compiled_sets ics,
48 pa_compiled_multipliers icpm
49 WHERE
50 ics.ind_rate_sch_revision_id = rate_sch_rev_id
51 AND ics.organization_id = org_id
52 AND ics.status = 'A'
53 AND ics.ind_compiled_set_id =
54 icpm.ind_compiled_set_id
55 AND ics.cost_base = c_base
56 AND icpm.cost_base = c_base;
57
58 if (indirect_cost_sum is null) then
59 status := NO_DATA_FOUND_ERR;
60 end if;
61
62 EXCEPTION
63
64 WHEN OTHERS THEN
65 status := SQLCODE;
66
67 END get_indirect_cost_import_sum1;
68 /* Added code for 2798971 ends*/
69 /* Added code for 2798971 */
70
71 procedure get_indirect_cost_import
72 ( task_id IN Number,
73 p_txn_interface_id IN Number, /* 3246794 */
74 effective_date IN Date,
75 expenditure_type IN Varchar2,
76 organization_id IN Number,
77 schedule_type IN Varchar2,
78 direct_cost IN Number,
79 direct_cost_denom IN number,
80 direct_cost_acct IN number,
81 direct_cost_project IN number,
82 indirect_cost_sum IN OUT NOCOPY Number,
83 indirect_cost_denom_sum IN OUT NOCOPY number,
84 indirect_cost_acct_sum IN OUT NOCOPY number,
85 indirect_cost_project_sum IN OUT NOCOPY number,
86 l_projfunc_currency_code IN varchar2,
87 l_project_currency_code IN varchar2,
88 l_acct_currency_code IN varchar2,
89 l_denom_currency_code IN varchar2,
90 Compiled_set_id IN OUT NOCOPY Number,
91 status IN OUT NOCOPY Number,
92 stage IN OUT NOCOPY Number)
93
94 IS
95
96 --
97 -- Local variables
98 --
99
100 sch_id Number(15);
101 sch_fixed_date Date;
102 rate_sch_rev_id Number(15);
103 cp_structure Varchar2(30);
104 c_base Varchar2(30);
105 --compiled_multiplier pa_compiled_multipliers.compiled_multiplier%TYPE;
106 BEGIN
107
108 status := 0;
109 Compiled_set_id:= TO_NUMBER(NULL); /*Bug# 3671809*/
110
111 --
112 -- Get the rate schedule revision id
113 --
114
115 pa_cost_plus.find_rate_sch_rev_id(
116 p_txn_interface_id, -- added instead of NULL Bug 3246794
117 'TRANSACTION_IMPORT', -- added instead of 'PA' Bug 3246794
118 task_id,
119 schedule_type,
120 effective_date,
121 sch_id,
122 rate_sch_rev_id,
123 sch_fixed_date,
124 status,
125 stage);
126
127 stage := 100;
128
129 IF (status <> 0) THEN
130 return;
131 END IF;
132
133 --
134 -- Get the cost plus structure
135 --
136
137 pa_cost_plus.get_cost_plus_structure(rate_sch_rev_id,
138 cp_structure,
139 status,
140 stage);
141
142 IF (status <> 0) THEN
143 stage := 200;
144 return;
145 END IF;
146
147
148 --
149 -- Get the cost base
150 --
151
152 pa_cost_plus.get_cost_base(expenditure_type,
153 cp_structure,
154 c_base,
155 status,
156 stage);
157
158 /* If expenditure type is not defined with a cost base,
159 get_cost_base return with status = 100. This means this expenditure
160 type should not be burdened. Thus, indirect costs should be 0. */
161 IF (status <> 0) THEN
162 IF (status = 100) THEN
163 indirect_cost_sum := 0;
164 indirect_cost_denom_sum := 0;
165 indirect_cost_acct_sum := 0;
166 indirect_cost_project_sum := 0;
167 status := 0;
168 return;
169 ELSE
170 stage := 300;
171 return;
172 END IF;
173 END IF;
174
175 stage := 400;
176 pa_cost_plus.get_compiled_set_id(rate_sch_rev_id,
177 organization_id,
178 c_base,
179 Compiled_set_id,
180 status,
181 stage);
182
183 IF ( status <>0 ) THEN
184 return;
185 END IF;
186
187 --
188 -- Get the indirect cost
189 --
190 pa_cost_plus1.get_indirect_cost_import_sum1 ( org_id => organization_id
191 ,c_base => c_base
192 ,rate_sch_rev_id => rate_sch_rev_id
193 ,direct_cost => direct_cost
194 ,direct_cost_denom => direct_cost_denom
195 ,direct_cost_acct => direct_cost_acct
196 ,direct_cost_project => direct_cost_project
197 ,precision => 2 -- FOR US CURRENCY
198 ,indirect_cost_sum => indirect_cost_sum
199 ,indirect_cost_denom_sum => indirect_cost_denom_sum
200 ,indirect_cost_acct_sum => indirect_cost_acct_sum
201 ,indirect_cost_project_sum => indirect_cost_project_sum
202 ,l_projfunc_currency_code => l_projfunc_currency_code
203 ,l_project_currency_code => l_project_currency_code
204 ,l_acct_currency_code => l_acct_currency_code
205 ,l_denom_currency_code => l_denom_currency_code
206 ,status => status
207 ,stage => stage
208 );
209
210 IF (status <> 0) THEN
211 stage := 400;
212 return;
213 END IF;
214
215 EXCEPTION
216
217 WHEN OTHERS THEN
218 status := SQLCODE;
219
220 END get_indirect_cost_import;
221
222 /* Added code for 2798971 ends */
223
224 --
225 -- PROCEDURE
226 -- view_indirect_cost
227 --
228 -- PURPOSE
229 -- The objective of this procedure is to retrieve the total
230 -- indirect cost based on a set of qualifications. User can
231 -- specify the qualifications and the type of indirect rate
232 -- schedule, then get the total amount of indirect cost.
233 --
234 -- HISTORY
235 --
236 -- 10-JUN-94 S Lee Created
237 -- 23-Aug-97 Shree Added two new parameters
238 --
239
240 procedure view_indirect_cost( task_id IN Number,
241 effective_date IN Date,
242 expenditure_type IN Varchar2,
243 organization_id IN Number,
244 schedule_type IN Varchar2,
245 direct_cost IN Number,
246 indirect_cost IN OUT NOCOPY Number,
247 status IN OUT NOCOPY Number,
248 stage IN OUT NOCOPY Number)
249
250 IS
251
252 --
253 -- Local variables
254 --
255
256 sch_id Number(15);
257 sch_fixed_date Date;
258 rate_sch_rev_id Number(15);
259 cp_structure Varchar2(30);
260 c_base Varchar2(30);
261 compiled_multiplier pa_compiled_multipliers.compiled_multiplier%TYPE; /*Bug# 1904585*/
262 BEGIN
263
264 status := 0;
265
266 --
267 -- Get the rate schedule revision id
268 --
269
270 pa_cost_plus.find_rate_sch_rev_id(
271 NULL,
272 'PA',
273 task_id,
274 schedule_type,
275 effective_date,
276 sch_id,
277 rate_sch_rev_id,
278 sch_fixed_date,
279 status,
280 stage);
281
282 stage := 100;
283
284 IF (status <> 0) THEN
285 return;
286 END IF;
287
288 --
289 -- Get the cost plus structure
290 --
291
292 pa_cost_plus.get_cost_plus_structure(rate_sch_rev_id,
293 cp_structure,
294 status,
295 stage);
296
297 IF (status <> 0) THEN
298 stage := 200;
299 return;
300 END IF;
301
302
303 --
304 -- Get the cost base
305 --
306
307 pa_cost_plus.get_cost_base(expenditure_type,
308 cp_structure,
309 c_base,
310 status,
311 stage);
312
313 /* Bug 925488: If expenditure type is not defined with a cost base,
314 get_cost_base return with status = 100. This means this expenditure
315 type should not be burdened. Thus, indirect cost should be 0. */
316 IF (status <> 0) THEN
317 IF (status = 100) THEN
318 indirect_cost := 0;
319 status := 0;
320 return;
321 ELSE
322 stage := 300;
323 return;
324 END IF;
325 END IF;
326
327 --
328 -- Get the indirect cost
329 --
330 /*For bug# 2110452:To implement the same logic for burdening as is used in R10.7/R11.0*/
331 pa_cost_plus.get_indirect_cost_sum(organization_id,
332 c_base,
333 rate_sch_rev_id,
334 direct_cost,
335 2, -- FOR US CURRENCY
336 indirect_cost,
337 status,
338 stage);
339
340 /*Bug# 2110452:Commented out to implement the same logic for burdening as is used in R10.7/R11*/
341 /*Bug# 2110452:
342 Get_compiled_multiplier is called to get the sum of the compiled multipliers.
343 --
344 -- Get the sum of the compiled Multipliers
345 --
346
347 pa_cost_plus.get_compiled_multiplier(organization_id,
348 c_base,
349 rate_sch_rev_id,
350 compiled_multiplier,
351 status,
352 stage); */
353
354 IF (status <> 0) THEN
355 stage := 400;
356 return;
357 END IF;
358
359 /*indirect_cost := PA_CURRENCY.ROUND_CURRENCY_AMT(direct_cost*compiled_multiplier);Bug# 2110452*/
360
361 EXCEPTION
362
363 WHEN OTHERS THEN
364 status := SQLCODE;
365
366 END view_indirect_cost;
367
368
369
370 -- Bug 886868, BURDEN AMOUNT DOUBLED IN PSI FOR REQUISITIONS
371 -- Modified get_indirect_cost_amounts procedure to call view_indirect_cost
372 -- from local package instead of pa_cost_plus.view_indirect_cost
373 -- This package is being called from form and the objective of the form
374 -- is to show burden cost as well as burden cost breakdown for
375 -- entered data. To show true burden calculation we should NOT implement
376 -- the burden summarization logic into this form
377 --
378 -- Also appropriately modified the arguments to view_indirect_cost calls
379 -- from get_indirect_cost_amounts procedure
380 --
381
382 procedure get_indirect_cost_amounts (x_indirect_cost_costing IN OUT NOCOPY number,
383 x_indirect_cost_revenue IN OUT NOCOPY number,
384 x_indirect_cost_invoice IN OUT NOCOPY number,
385 x_task_id IN number,
386 x_gl_date IN date,
387 x_expenditure_type IN varchar2,
388 x_organization_id IN number,
389 x_direct_cost IN number,
390 x_return_status IN OUT NOCOPY number,
391 x_stage IN OUT NOCOPY number)
392 is
393 begin
394
395 --
396 -- Get the costing indirect cost
397 --
398 pa_cost_plus1.view_indirect_cost( x_task_id,
399 x_gl_date,
400 x_expenditure_type,
401 x_organization_id,
402 'C',
403 x_direct_cost,
404 x_indirect_cost_costing,
405 x_return_status,
406 x_stage);
407
408 /*
409 if (x_return_status <> 0) then
410 x_stage := x_stage + 1000;
411 end if;
412 */
413
414 if (x_return_status <> 0) then
415 x_indirect_cost_costing := 0;
416 end if;
417
418 --
419 -- Get the revenue indirect cost
420 --
421 pa_cost_plus1.view_indirect_cost( x_task_id,
422 x_gl_date,
423 x_expenditure_type,
424 x_organization_id,
425 'R',
426 x_direct_cost,
427 x_indirect_cost_revenue,
428 x_return_status,
429 x_stage);
430
431 /*
432 if (x_return_status = NO_RATE_SCH_ID) then
433 -- Acceptable. Reset the status
434 x_indirect_cost_revenue := 0;
435 x_return_status := 0;
436 elsif (x_return_status <> 0) then
437 x_stage := x_stage + 2000;
438 return;
439 end if;
440 */
441
442 if (x_return_status <> 0) then
443 x_indirect_cost_revenue := 0;
444 end if;
445
446 --
447 -- Get the invoice indirect cost
448 --
449 pa_cost_plus1.view_indirect_cost( x_task_id,
450 x_gl_date,
451 x_expenditure_type,
452 x_organization_id,
453 'I',
454 x_direct_cost,
455 x_indirect_cost_invoice,
456 x_return_status,
457 x_stage);
458
459 /*
460 if (x_return_status = NO_RATE_SCH_ID) then
461 -- Acceptable. Reset the status
462 x_indirect_cost_invoice := 0;
463 x_return_status := 0;
464 elsif (x_return_status <> 0) then
465 x_stage := x_stage + 3000;
466 return;
467 end if;
468 */
469
470 if (x_return_status <> 0) then
471 x_indirect_cost_invoice := 0;
472 end if;
473
474
475 end get_indirect_cost_amounts;
476
477
478
479 procedure get_ind_rate_sch_rev(x_ind_rate_sch_name IN OUT NOCOPY varchar2,
480 x_ind_rate_sch_revision IN OUT NOCOPY varchar2,
481 x_ind_rate_sch_revision_type IN OUT NOCOPY varchar2,
482 x_start_date_active IN OUT NOCOPY date,
483 x_end_date_active IN OUT NOCOPY date,
484 x_task_id IN number,
485 x_gl_date IN date,
486 x_detail_type_flag IN varchar2,
487 x_expenditure_type IN varchar2,
488 x_cost_base IN OUT NOCOPY varchar2,
489 x_ind_compiled_set_id IN OUT NOCOPY number,
490 x_organization_id IN number,
491 x_return_status IN OUT NOCOPY number,
492 x_stage IN OUT NOCOPY number)
493 is
494 x_sch_id number;
495 x_sch_fixed_date date;
496 x_rate_sch_rev_id number;
497 x_cp_structure varchar2(30);
498
499 begin
500
501 x_return_status := 0;
502 x_stage := 0;
503
504 pa_cost_plus.find_rate_sch_rev_id (NULL,
505 'PA',
506 x_task_id,
507 x_detail_type_flag,
508 x_gl_date,
509 x_sch_id,
510 x_rate_sch_rev_id,
511 x_sch_fixed_date,
512 x_return_status,
513 x_stage);
514
515 if (x_return_status > 0) then
516 begin
517 x_stage := 1;
518 return;
519 end;
520 elsif (x_return_status < 0) then
521 begin
522 return;
523 end;
524 end if;
525
526
527 begin
528
529 pa_cost_plus.get_cost_plus_structure(x_rate_sch_rev_id,
530 x_cp_structure,
531 x_return_status,
532 x_stage);
533
534 pa_cost_plus.get_cost_base (x_expenditure_type,
535 x_cp_structure,
536 x_cost_base,
537 x_return_status,
538 x_stage);
539 if (x_return_status > 0) then
540 begin
541 x_stage := 2;
542 return;
543 end;
544 elsif (x_return_status < 0) then
545 begin
546 return;
547 end;
548 end if;
549
550 begin
551 /*========================================================+
552 | 21-MAY-03 Burdening Enhancements. |
553 | Added Cost Base join to pa_ind_compiled_sets |
554 +========================================================*/
555 select ind_compiled_set_id
556 into x_ind_compiled_set_id
557 from pa_ind_compiled_sets
558 where ind_rate_sch_revision_id = x_rate_sch_rev_id
559 and organization_id = x_organization_id
560 and cost_base = x_cost_base
561 and status = 'A';
562
563 EXCEPTION
564 WHEN NO_DATA_FOUND then
565 x_stage := 3;
566 x_return_status := 1;
567 end;
568
569 begin
570 select s.ind_rate_sch_name,
571 sr.ind_rate_sch_revision,
572 pl.meaning,
573 sr.start_date_active,
574 sr.end_date_active
575 into x_ind_rate_sch_name,
576 x_ind_rate_sch_revision,
577 x_ind_rate_sch_revision_type,
578 x_start_date_active,
579 x_end_date_active
580 from pa_ind_rate_schedules s,
581 pa_ind_rate_sch_revisions sr,
582 pa_lookups pl
583 where s.ind_rate_sch_id = sr.ind_rate_sch_id
584 and sr.ind_rate_sch_revision_type = pl.lookup_code
585 and pl.lookup_type = 'IND RATE SCHEDULE REV TYPE'
586 and sr.ind_rate_sch_revision_id = x_rate_sch_rev_id;
587
588 EXCEPTION
589 WHEN NO_DATA_FOUND then
590 if x_stage = 3 then
591 x_stage := 3;
592 else
593 x_stage := 4;
594 end if;
595 x_return_status := 1;
596 end;
597
598
599 EXCEPTION
600 WHEN NO_DATA_FOUND then
601 x_return_status := 1;
602
603 WHEN OTHERS then
604 x_return_status := SQLCODE;
605 end;
606
607 end get_ind_rate_sch_rev;
608
609 --
610 -- PROCEDURE
611 -- get_mc_indirect_cost
612 --
613 -- PURPOSE
614 -- The objective of this procedure is to retrieve the Multi-Currency
615 -- indirect cost based on a set of qualifications. User can
616 -- specify the qualifications and the type of indirect rate
617 -- schedule, then get the total amount of indirect cost.
618 --
619 -- HISTORY
620 --
621
622 procedure get_compile_set_info(p_txn_interface_id IN number DEFAULT NULL, --added for bug 2563364
623 task_id IN Number,
624 effective_date IN Date,
625 expenditure_type IN Varchar2,
626 organization_id IN Number,
627 schedule_type IN Varchar2,
628 compiled_multiplier IN OUT NOCOPY Number,
629 compiled_set_id IN OUT NOCOPY Number,
630 status IN OUT NOCOPY Number,
631 stage IN OUT NOCOPY Number,
632 x_cp_structure IN OUT NOCOPY VARCHAR2, --Bug# 5743708
633 x_cost_base IN OUT NOCOPY VARCHAR2 --Bug# 5743708
634 )
635 IS
636 --
637 -- Local Variables
638 --
639 sch_id Number(15);
640 sch_fixed_date Date;
641 rate_sch_rev_id Number(15);
642 cp_structure Varchar2(30);
643 c_base Varchar2(30);
644
645 BEGIN
646
647 --
648 -- Get the rate schedule revision id
649 --
650
651 stage := 100;
652 pa_cost_plus.find_rate_sch_rev_id(
653 p_txn_interface_id, -- changed from NULL for bug 2563364
654 'PA', /*Bug 4311703 */ -- changed from 'PA' for bug 2563364
655 task_id,
656 schedule_type,
657 effective_date,
658 sch_id,
659 rate_sch_rev_id,
660 sch_fixed_date,
661 status,
662 stage);
663 IF (status <> 0) THEN
664 return;
665 END IF;
666
667 --
668 -- Get the cost plus structure
669 --
670
671 stage := 200;
672 pa_cost_plus.get_cost_plus_structure(rate_sch_rev_id,
673 cp_structure,
674 status,
675 stage);
676
677 IF (status <> 0) THEN
678 return;
679 END IF;
680
681 x_cp_structure := cp_structure; --Bug# 5743708
682 --
683 -- Get the cost base
684 --
685
686 stage := 300;
687 pa_cost_plus.get_cost_base(expenditure_type,
688 cp_structure,
689 c_base,
690 status,
691 stage);
692
693 IF (status <> 0) THEN
694 return;
695 END IF;
696
697 x_cost_base := c_base; --Bug# 5743708
698 stage := 400;
699 pa_cost_plus.get_compiled_set_id(rate_sch_rev_id,
700 organization_id,
701 c_base,
702 Compiled_set_id,
703 status,
704 stage);
705
706 IF ( status <>0 ) THEN
707 return;
708 END IF;
709
710 stage := 500;
711 pa_cost_plus.get_compiled_multiplier( organization_id,
712 c_base,
713 rate_sch_rev_id,
714 compiled_multiplier,
715 status,
716 stage);
717 IF ( status <>0 ) THEN
718 return;
719 END IF;
720 EXCEPTION WHEN OTHERS THEN
721 status := SQLCODE;
722 END get_compile_set_info;
723
724 end PA_COST_PLUS1 ;
[Home] [Help]