1 package body PA_COST_PLUS as
2 -- $Header: PAXCCPEB.pls 120.11.12000000.9 2007/06/28 12:47:29 prabsing ship $
3 -- Package constants
4 NO_DATA_FOUND_ERR number := 100;
5 INDIRECT_COST_CODE varchar2(30) := 'INDIRECT COST';
6 G_MODULE varchar2(10) := 'XXX'; /*3005625*/
7 /*3005625 : Added variable G_MODULE -this is set to value 'NEW_ORG' whenver
8 process Added New organization is run i.e new_organization is called.
9 This is to generate compiled multipliers for the new organization in
10 all the burden schedule revisions (even when nothing has changed in the
11 revision i.e ready_to_compile_flag is <>'Y'/'X')*/
12
13 /* Start : Add a variable (G_GMS_ENABLED) to hold the value of GMS implemented status
14 ** for Operating Unit with a default value of NULL
15 ** 2981752 - PA.L:BURDENING ENHANCEMENTS : TRACKING BUG
16 */
17
18 /*Added these variables for the bug 4527736*/
19 G_RATE_SCH_REVISION_ID PA_IND_RATE_SCH_REVISIONS.IND_RATE_SCH_REVISION_ID%TYPE ;
20 G_CP_STRUCTURE PA_COST_PLUS_STRUCTURES.COST_PLUS_STRUCTURE%TYPE;
21 G_ORG_STRUC_VER_ID PA_IND_RATE_SCH_REVISIONS.ORG_STRUCTURE_VERSION_ID%TYPE;
22 G_START_ORGANIZATION_ID PA_IND_RATE_SCH_REVISIONS.START_ORGANIZATION_ID%TYPE;
23
24 G_IMPACTED_COST_BASES_TAB PA_PLSQL_DATATYPES.Char30TabTyp;
25 G_EXPENDITURE_ITEM_ID_TAB PA_PLSQL_DATATYPES.IDTABTYP;
26 G_ADJ_TYPE_TAB PA_PLSQL_DATATYPES.Char30TabTyp;
27
28 /*
29 * Private Procedure.
30 */
31 PROCEDURE Cache_Impacted_Cost_Bases ( P_Ind_Rate_Sch_Revision_Id IN PA_IND_RATE_SCH_REVISIONS.IND_RATE_SCH_REVISION_ID%TYPE
32 ,P_Cp_Structure IN PA_COST_PLUS_STRUCTURES.COST_PLUS_STRUCTURE%TYPE
33 );
34 /* End Bug# 4527736 */
35
36 G_gms_enabled varchar2(1):= gms_pa_api3.grants_enabled ;
37
38
39 /* End : Add a variable (G_GMS_ENABLED) to hold the value of GMS implemented status
40 ** for Operating Unit with a default value of NULL.
41 ** 2981752 - PA.L:BURDENING ENHANCEMENTS : TRACKING BUG
42 */
43
44
45 -- Package type
46 TYPE precedence_tab_type IS TABLE OF pa_compiled_multipliers.precedence%TYPE
47 INDEX BY BINARY_INTEGER;
48 TYPE ind_cost_code_tab_type IS TABLE OF
49 pa_compiled_multipliers.ind_cost_code%TYPE INDEX BY BINARY_INTEGER;
50 TYPE multiplier_tab_type IS TABLE OF pa_ind_cost_multipliers.multiplier%TYPE
51 INDEX BY BINARY_INTEGER;
52
53 /***Bug# 2933915:Cursor for selecting impacted cost bases for which :
54 ****the organization/cost code has ready_to_compile_flag as 'Y' or 'X' i.e the multiplier is modified or deleted respectively in
55 pa_ind_cost_multipliers
56 OR
57 ****G_MODULE ='NEW_ORG' i.e when we need to generate new compiled set ids in all the revisions for a new organization even when there
58 is no change to the burden schedule******************************/
59
60 CURSOR impacted_cost_bases(rate_sch_rev_id NUMBER)
61 IS
62 SELECT pcb.COST_BASE
63 FROM PA_COST_BASES pcb
64 WHERE pcb.COST_BASE_TYPE = INDIRECT_COST_CODE
65 AND EXISTS
66 (
67 SELECT 1 /* Bug# 4527736 */
68 FROM PA_COST_BASE_COST_CODES CBICC,
69 PA_IND_COST_MULTIPLIERS ICM,
70 PA_IND_RATE_SCH_REVISIONS IRSR
71 WHERE IRSR.IND_RATE_SCH_REVISION_ID = ICM.IND_RATE_SCH_REVISION_ID
72 AND IRSR.IND_RATE_SCH_REVISION_ID = rate_sch_rev_id
73 AND (NVL(ICM.READY_TO_COMPILE_FLAG,'N') IN ('Y','X')
74 OR NVL(G_MODULE ,'XXX') = 'NEW_ORG')
75 AND IRSR.COST_PLUS_STRUCTURE = CBICC.COST_PLUS_STRUCTURE
76 AND CBICC.IND_COST_CODE = ICM.IND_COST_CODE
77 AND CBICC.COST_BASE = PCB.COST_BASE
78 AND CBICC.COST_BASE_TYPE = PCB.COST_BASE_TYPE );
79
80 /*End of change 2933915*/
81 --
82 -- PROCEDURE
83 -- compile_org_rates
84 --
85 -- PURPOSE
86 -- The objective of this procedure is to compile the rates of
87 -- indirect costs. An expenditure item may associate with a
88 -- couple of indirect costs. The amount of these indirect costs
89 -- is the product of the raw cost of expenditure item and the
90 -- indirect cost rate. The indirect cost rate is based on
91 -- rate schedule, cost base, and organization. This procedure
92 -- will compile indirect cost rates for a specific rate schedule,
93 -- cost base, and organization. Moreover, the indirect cost
94 -- rates of all descendant organizations are compiled as well.
95 --
96 -- CONSTRAINTS
97 -- The mulipliers of the top organization MUST be specified.
98 --
99 --
100 -- HISTORY
101 -- 07-JUN-94 S Lee Added status and stage
102 -- 29-MAR-94 S Lee Modified for using new database schema and
103 -- application standards
104 -- 18-NOV-93 S Lee Revamped
105 -- 28-SEP-93 S Lee Created
106 --
107
108
109 procedure compile_org_rates (rate_sch_rev_id IN number,
110 org_id IN number,
111 org_struc_ver_id IN number,
112 start_org IN number,
113 status IN OUT NOCOPY number,
114 stage IN OUT NOCOPY number)
115 IS
116
117 --
118 -- CONSTANT definition
119 --
120
121 --
122 -- VARIABLE definition
123 --
124
125 base pa_compiled_multipliers.compiled_multiplier%TYPE;
126 defined_org_id hr_organization_units.organization_id%TYPE;
127 ind_cost_multiplier pa_ind_cost_multipliers.multiplier%TYPE;
128 old_cost_base pa_cost_bases.cost_base%TYPE DEFAULT NULL;
129 old_precedence pa_cost_base_cost_codes.precedence%TYPE DEFAULT NULL;
130 ind_cost_multiplier_sum pa_ind_cost_multipliers.multiplier%TYPE;
131 compiled_set_id pa_ind_compiled_sets.ind_compiled_set_id%TYPE;
132 org_override NUMBER(15) DEFAULT 0;
133 l_start_date DATE;
134 l_end_date DATE;
135 l_org_override NUMBER(15) DEFAULT 0; /*2933915*/
136
137 -- Standard who
138 x_last_updated_by NUMBER(15);
139 x_created_by NUMBER(15);
140 x_last_update_login NUMBER(15);
141 x_request_id NUMBER(15);
142 x_program_application_id NUMBER(15);
143 x_program_id NUMBER(15);
144
145 --
146 -- CURSOR definition
147 --
148 /*2933915 :Modified the existing ind_cost_code_cursor to select for impacted cost bases ONLY and not for all the
149 cost bases as was earlier*/
150
151 /* Replaced this cursor with the below defined cursor for the bug 4527736
152 CURSOR ind_cost_code_cursor(x_base VARCHAR2) IS -- 2933915
153 SELECT
154 cbicc.cost_base_cost_code_id,
155 cbicc.cost_base,
156 cbicc.ind_cost_code,
157 cbicc.precedence
158 FROM pa_cost_base_cost_codes cbicc,
159 pa_ind_rate_sch_revisions irsr
160 WHERE irsr.ind_rate_sch_revision_id = rate_sch_rev_id
161 AND irsr.cost_plus_structure = cbicc.cost_plus_structure
162 AND cbicc.cost_base = x_base -- 2933915
163 AND cbicc.cost_base_type = INDIRECT_COST_CODE
164 ORDER BY
165 cbicc.cost_base, cbicc.precedence;
166 */
167
168 CURSOR ind_cost_code_cursor(x_base VARCHAR2) IS
169 SELECT
170 cbicc.cost_base_cost_code_id,
171 cbicc.cost_base,
172 cbicc.ind_cost_code,
173 cbicc.precedence
174 FROM pa_cost_base_cost_codes cbicc
175 WHERE cbicc.cost_plus_structure = G_CP_STRUCTURE
176 AND cbicc.cost_base = x_base
177 AND cbicc.cost_base_type = INDIRECT_COST_CODE
178 ORDER BY
179 cbicc.cost_base, cbicc.precedence;
180 --
181 -- Procedure body
182 --
183
184
185 BEGIN
186
187 status := 0;
188 stage := 100;
189
190 --
191 -- Get the standard who information
192 --
193 x_created_by := FND_GLOBAL.USER_ID;
194 x_last_updated_by := FND_GLOBAL.USER_ID;
195 x_last_update_login := FND_GLOBAL.LOGIN_ID;
196 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
197 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
198 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
199
200
201 -- Get the standard who information
202 --
203 -- Compile the indirect cost rates for this organization.
204 -- First check if there is an override on this organization.
205 -- If so, compile rates for this organization, and create a new set.
206 -- If not, use the compiled set of its parent organization.
207 --
208
209 /*2933915 :Whatever org is passed here -we are ensuring this in the calling procedure that it has proper value of
210 ready to compile flag i.e 'N','Y','X' respectively depending on whether the multiplier is deleted ,changed or not changed
211 for that ORG . BUT we also need to ensure that if EXPLICIT multipliers are defined for an org for ALL
212 cost codes belonging to AFFECTED cost bases then new CSID should not be generated for that ie it should not be recompiled
213 If multipliers are not found for ANY of the cost code then we should go ahead with compiling new one*/
214
215
216 BEGIN
217 SELECT /*+ FIRST_ROWS */
218 1
219 INTO org_override
220 FROM sys.dual WHERE EXISTS
221 (SELECT /*+ FIRST_ROWS */
222 1
223 FROM pa_ind_cost_multipliers
224 WHERE ind_rate_sch_revision_id = rate_sch_rev_id
225 AND organization_id = org_id
226 AND nvl(ready_to_compile_flag,'N') <> 'X') ;
227
228 EXCEPTION
229 WHEN NO_DATA_FOUND THEN
230 org_override := 0; /**Multipliers are not found for any of the cost code for this org**/
231 WHEN OTHERS THEN
232 status := SQLCODE;
233 return;
234 END;
235
236 IF check_for_explicit_multiplier(rate_sch_rev_id, org_id) = 0 THEN /*Bug 4739218 */
237
238 IF org_override = 0 THEN
239 --
240 -- This organization does not have cost override. Therefore, its
241 -- compiled rate is as same as its parent organization.
242 -- Bug# 2933915 : Adding loop for doing this only for impacted cost bases
243 -- 4527736
244 -- FOR cost_base_rec in impacted_cost_bases(rate_sch_rev_id) /*2933915*/
245 IF G_IMPACTED_COST_BASES_TAB.COUNT <> 0 THEN /*4590268*/
246
247 FOR i IN G_IMPACTED_COST_BASES_TAB.FIRST .. G_IMPACTED_COST_BASES_TAB.LAST
248 LOOP
249
250 BEGIN
251
252 --
253 -- Get the set id information from its parent organization
254 --
255
256 SELECT /*+ ORDERED
257 INDEX(ose PER_ORG_STRUCTURE_ELEMENTS_FK4)
258 INDEX(ics PA_IND_COMPILED_SETS_N1) */
259 ics.ind_compiled_set_id
260 INTO compiled_set_id
261 FROM per_org_structure_elements ose,
262 pa_ind_compiled_sets ics
263 WHERE ose.organization_id_child = org_id
264 AND ose.org_structure_version_id = org_struc_ver_id
265 AND ose.organization_id_parent = ics.organization_id
266 AND ics.ind_rate_sch_revision_id = rate_sch_rev_id
267 --4527736
268 -- AND ics.cost_base = cost_base_rec.cost_base /*2933915*/
269 AND ics.cost_base = G_IMPACTED_COST_BASES_TAB(i)
270 AND ics.status = 'A';
271
272 --
273 -- Add the set id information to this organization
274 --
275 /*S.O. 4888548
276 INSERT INTO pa_ind_compiled_sets
277 (ind_compiled_set_id,
278 ind_rate_sch_revision_id,
279 organization_id,
280 cost_base, /*2933915*
281 last_update_date,
282 last_updated_by,
283 created_by,
284 creation_date,
285 last_update_login,
286 request_id,
287 program_application_id,
288 program_id,
289 program_update_date,
290 status)
291 VALUES(compiled_set_id,
292 rate_sch_rev_id,
293 org_id,
294 --4527736
295 -- cost_base_rec.cost_base, /*2933915
296 G_IMPACTED_COST_BASES_TAB(i),
297 SYSDATE,
298 x_last_updated_by,
299 x_created_by,
300 SYSDATE,
301 x_last_update_login,
302 x_request_id,
303 x_program_application_id,
304 x_program_id,
305 SYSDATE,
306 'A');
307 E.O. 4888548 */
308
309 /*S.N. 4888548 */
310 INSERT INTO pa_ind_compiled_sets
311 (ind_compiled_set_id,
312 ind_rate_sch_revision_id,
313 organization_id,
314 cost_base,
315 last_update_date,
316 last_updated_by,
317 created_by,
318 creation_date,
319 last_update_login,
320 request_id,
321 program_application_id,
322 program_id,
323 program_update_date,
324 status)
325 SELECT compiled_set_id,
326 rate_sch_rev_id,
327 org_id,
328 --4527736
329 -- cost_base_rec.cost_base,
330 G_IMPACTED_COST_BASES_TAB(i),
331 SYSDATE,
332 x_last_updated_by,
333 x_created_by,
334 SYSDATE,
335 x_last_update_login,
336 x_request_id,
337 x_program_application_id,
338 x_program_id,
339 SYSDATE,
340 'A'
341 FROM DUAL
342 WHERE NOT EXISTS
343 ( SELECT 1 from pa_ind_compiled_sets ics
344 WHERE ics.ind_rate_sch_revision_id =rate_sch_rev_id
345 AND ics.organization_id = org_id
346 AND ics.cost_base = G_IMPACTED_COST_BASES_TAB(i)
347 AND ics.status='A'
348 ) ;
349 /*E.N. 4888548 */
350
351
352 EXCEPTION
353 WHEN NO_DATA_FOUND THEN
354 --
355 -- The parent organization has not been compiled yet.
356 -- or this organization does not have a parent organization.
357 -- We will compile a new set for this organization.
358 --
359 l_org_override := -1 ;
360
361 WHEN OTHERS THEN
362 --
363 -- This rate schedule for this organization has been compiled
364 -- previously. The compiled set must be deleted before
365 -- adding the new set id.
366 --
367 status := SQLCODE;
368 RETURN;
369
370 END;
371
372 END LOOP; /*End of impacted_cost_bases loop :2933915*/
373
374 END IF; /*4590268*/
375
376 IF l_org_override = 0 THEN /*2933915 :Compiled set id found for all the impacted cost bases for the parent org*/
377 COMMIT;
378 RETURN ;
379 END IF ;
380
381 END IF; /*End if org_override =0*/
382 END IF; /* Bug 4739218 */
383 /**2933915 :
384 If EXPLICIT multipliers are defined for ALL the cost codes in that structure for this org then any change in parent orgs would not
385 impact the child org and hence recompilation is not required so return to the calling procedure to get the next org *******/
386
387 IF (org_override = 1 OR l_org_override =-1) THEN
388 --4527736
389 --FOR cost_base_rec in impacted_cost_bases(rate_sch_rev_id)
390 IF G_IMPACTED_COST_BASES_TAB.COUNT <> 0 THEN /*4590268*/
391
392 FOR i IN G_IMPACTED_COST_BASES_TAB.FIRST .. G_IMPACTED_COST_BASES_TAB.LAST
393 LOOP
394 --4527736
395 --FOR cost_code_rec in ind_cost_code_cursor(cost_base_rec.cost_base) **2933915 :Cost codes of impacted cost bases**
396 FOR cost_code_rec in ind_cost_code_cursor(G_IMPACTED_COST_BASES_TAB(i))
397 LOOP
398 BEGIN
399 SELECT /*+ FIRST_ROWS */
400 1
401 INTO l_org_override
402 FROM sys.dual WHERE EXISTS
403 (SELECT /*+ FIRST_ROWS */
404 1
405 FROM pa_ind_cost_multipliers icm,
406 pa_ind_compiled_sets ics
407 WHERE icm.ind_rate_sch_revision_id =ics.ind_rate_sch_revision_id
408 AND icm.ind_rate_sch_revision_id = rate_sch_rev_id
409 AND icm.organization_id =ics.organization_id
410 AND icm.organization_id = org_id
411 --AND ics.cost_base = cost_base_rec.cost_base --4527736
412 AND ics.cost_base = G_IMPACTED_COST_BASES_TAB(i)
413 AND ics.status ='A'
414 AND icm.ind_cost_code =cost_code_rec.ind_cost_code
415 AND nvl(icm.ready_to_compile_flag,'N') <>'X'); /*Should not consider 'X' records as they are actually
416 deleted records */
417
418 EXCEPTION
419 WHEN NO_DATA_FOUND THEN
420 l_org_override := 0;
421 WHEN OTHERS THEN
422 status := SQLCODE;
423 return;
424 END;
425 END LOOP; /*End loop ind_cost_code_cursor*/
426
427 IF l_org_override =0 THEN
428 EXIT;
429 END IF;
430 END LOOP; /*End loop impacted_cost_bases*/
431 END IF; /*4590268*/
432
433 IF l_org_override =1 THEN /***Bug 2933915 :Explicit multipliers found for all the cost codes in impacted cost bases*/
434 RETURN ;
435 END IF ;
436 END IF; /*End if of org_overrride OR l_orgoverride... */
437
438 /****End of changes for bug# 2933915******/
439
440 --
441 -- Okay, there is override for this organization. We need to compile a
442 -- new set of multipliers. First pick up a number for set id.
443 --
444
445 stage := 200;
446
447 SELECT pa_ind_compiled_sets_s.NEXTVAL into compiled_set_id FROM sys.dual;
448
449
450 SAVEPOINT before_adding_multipliers;
451
452 BEGIN
453
454 <<process_ind_cost_codes>>
455 --FOR cost_base_rec in impacted_cost_bases(rate_sch_rev_id) --4527736
456 IF G_IMPACTED_COST_BASES_TAB.COUNT <> 0 THEN /*4590268*/
457
458 FOR i IN G_IMPACTED_COST_BASES_TAB.FIRST .. G_IMPACTED_COST_BASES_TAB.LAST --4527736
459 LOOP
460
461 --FOR icc_row IN ind_cost_code_cursor(cost_base_rec.cost_base) LOOP --4527736
462 FOR icc_row IN ind_cost_code_cursor(G_IMPACTED_COST_BASES_TAB(i)) LOOP --4527736
463
464 --
465 -- We want to get the multiplier for this organization.
466 -- First set the current organization as the starting point.
467 -- If the multiplier is not found, we will go one level higher.
468 --
469 defined_org_id := org_id;
470
471 <<find_multiplier>>
472 LOOP
473 --
474 -- Retrieve the value of multiplier from the pre-defined table
475 --
476 BEGIN
477 --
478 -- Find out whether the ICM of this organization is defined or not.
479 -- If so, retrieve the ICM and exit the loop.
480 -- If not, trace upward to see whether its parent organization is
481 -- defined or not.
482 --
483
484 SELECT multiplier
485 INTO ind_cost_multiplier
486 FROM
487 pa_ind_cost_multipliers
488 WHERE
489 organization_id = defined_org_id
490 AND ind_cost_code = icc_row.ind_cost_code
491 AND ind_rate_sch_revision_id = rate_sch_rev_id
492 AND nvl(ready_to_compile_flag,'N') <> 'X' ; /*3005954 :Multipliers of deleted(i.e marked for deletion
493 internally) record for org should not be considered*/
494
495 --
496 -- If NO_DATA_FOUND exception is not raised, the multiplier is
497 -- defined. Exit this loop.
498 --
499
500 EXIT;
501
502
503 EXCEPTION
504 WHEN NO_DATA_FOUND THEN
505 --
506 -- Verify whether we have reached the top of organization
507 --
508 IF defined_org_id = start_org THEN
509 --
510 -- The multiplier is still not found at the top of the
511 -- organization structure. Set the ICM to 0.
512 --
513 ind_cost_multiplier := 0;
514 EXIT;
515 END IF;
516
517 --
518 -- Multiplier is not defined in this level. Go up one level
519 -- further.
520 --
521
522 SELECT organization_id_parent
523 INTO defined_org_id
524 FROM per_org_structure_elements
525 WHERE
526 organization_id_child = defined_org_id
527 AND org_structure_version_id = org_struc_ver_id;
528
529 WHEN OTHERS THEN
530 status := SQLCODE;
531 RETURN;
532
533 END;
534
535 END LOOP find_multiplier;
536
537 stage := 300;
538
539 --
540 -- Check whether this is a new cost base. If yes, change
541 -- the base of calculation.
542 --
543
544 IF (old_cost_base IS NULL) OR
545 (icc_row.cost_base <> old_cost_base) THEN
546 --
547 -- Base is used to compile the multipier.
548 -- Base is set to 1 when using a new cost base.
549 --
550
551 base := 1;
552
553 --
554 -- ind_cost_multiplier_sum is used to store the summation of
555 -- compiled multipliers which have the same precedence.
556 -- Set to 0 at for the first indirect cost code.
557 --
558
559 ind_cost_multiplier_sum := 0;
560
561 ELSE
562 --
563 -- The cost base of this indirect cost code is as same as the
564 -- previous one.
565 --
566
567 IF old_precedence <> icc_row.precedence THEN
568 --
569 -- The calculation base will grow when the compiled rate
570 -- of previous indirect cost code is added into the base.
571 -- The current indirect cost code has a higher precedence,
572 -- hence change the base of calculation.
573 --
574
575 base := base * (1 + ind_cost_multiplier_sum);
576
577 --
578 -- Reset the sum whenever the precedence is changed.
579 --
580
581 ind_cost_multiplier_sum := 0;
582
583 END IF;
584 END IF;
585
586 --
587 -- Enter the compiled rate into table
588 --
589
590 INSERT INTO pa_compiled_multipliers
591 (ind_compiled_set_id,
592 cost_base_cost_code_id,
593 cost_base,
594 ind_cost_code,
595 precedence,
596 compiled_multiplier,
597 multiplier,
598 last_update_date,
599 last_updated_by,
600 created_by,
601 creation_date,
602 last_update_login,
603 request_id,
604 program_application_id,
605 program_id,
606 program_update_date)
607 VALUES
608 (compiled_set_id,
609 icc_row.cost_base_cost_code_id,
610 icc_row.cost_base,
611 icc_row.ind_cost_code,
612 icc_row.precedence,
613 base * ind_cost_multiplier,
614 ind_cost_multiplier,
615 SYSDATE,
616 x_last_updated_by,
617 x_created_by,
618 SYSDATE,
619 x_last_update_login,
620 x_request_id,
621 x_program_application_id,
622 x_program_id,
623 SYSDATE
624 );
625
626
627 --
628 -- 1. Get the summation of indirect cost multipliers that have
629 -- the same precedence.
630 -- 2. Keep the old precedence in order to know when to change
631 -- base.
632 -- 3. Memorize the current cost base.
633 --
634 ind_cost_multiplier_sum := ind_cost_multiplier_sum + ind_cost_multiplier;
635 old_precedence := icc_row.precedence;
636
637 /*Bug# 2933915 : Insert Compiled sets ids for organization_id/Cost_base combination .
638 Earlier CSID was inserted for organization.Now it has to be inserted for organization_id/Cost_base combination */
639
640 IF (old_cost_base is NULL) OR (icc_row.cost_base <> old_cost_base) THEN /*Bug 2933915*/
641
642 /*S.N. 4888548
643 INSERT INTO pa_ind_compiled_sets
644 (ind_compiled_set_id,
645 ind_rate_sch_revision_id,
646 organization_id,
647 cost_base, /*Bug# 2933915
648 last_update_date,
649 last_updated_by,
650 created_by,
651 creation_date,
652 last_update_login,
653 request_id,
654 program_application_id,
655 program_id,
656 program_update_date,
657 status)
658 VALUES
659 (compiled_set_id,
660 rate_sch_rev_id,
661 org_id,
662 icc_row.cost_base, /*Bug# 2933915
663 SYSDATE,
664 x_last_updated_by,
665 x_created_by,
666 SYSDATE,
667 x_last_update_login,
668 x_request_id,
669 x_program_application_id,
670 x_program_id,
671 SYSDATE,
672 'A'
673 );
674 E.O. 4888548 */
675
676 /*S.N. 4888548 */
677 INSERT INTO pa_ind_compiled_sets
678 (ind_compiled_set_id,
679 ind_rate_sch_revision_id,
680 organization_id,
681 cost_base,
682 last_update_date,
683 last_updated_by,
684 created_by,
685 creation_date,
686 last_update_login,
687 request_id,
688 program_application_id,
689 program_id,
690 program_update_date,
691 status)
692 SELECT
693 compiled_set_id,
694 rate_sch_rev_id,
695 org_id,
696 icc_row.cost_base,
697 SYSDATE,
698 x_last_updated_by,
699 x_created_by,
700 SYSDATE,
701 x_last_update_login,
702 x_request_id,
703 x_program_application_id,
704 x_program_id,
705 SYSDATE,
706 'A'
707 FROM DUAL
708 WHERE NOT EXISTS
709 ( SELECT 1 from pa_ind_compiled_sets ics
710 WHERE ics.ind_rate_sch_revision_id =rate_sch_rev_id
711 AND ics.organization_id = org_id
712 AND ics.cost_base =icc_row.cost_base
713 AND ics.status='A'
714 ) ;
715 /*E.N. 4888548 */
716
717 END IF ;
718 old_cost_base := icc_row.cost_base; /*2933915*/
719
720 END LOOP process_ind_cost_codes; /*2933915*/
721 END LOOP ; /*impacted_cost_base_cur*/ /*2933915*/
722
723 END IF; /*4590268*/
724
725 EXCEPTION
726 WHEN OTHERS THEN
727 --
728 -- remove the multipliers which are defined previously
729 --
730 ROLLBACK TO before_adding_multipliers;
731 status := SQLCODE;
732 RETURN;
733
734 END;
735 --
736 -- Commit the whole transaction now.
737 --
738 COMMIT;
739 RETURN;
740
741 EXCEPTION
742 WHEN OTHERS THEN
743 status := SQLCODE;
744 RETURN;
745
746 END compile_org_rates;
747
748
749 --
750 -- PROCEDURE
751 -- compile_org_hierarchy_rates
752 --
753 -- PURPOSE
754 -- The objective of this procedure is to create compiled rates for
755 -- the whole organization hierarchy using a specific rate schedule.
756 --
757 -- HISTORY
758 --
759 -- 08-JUN-94 S Lee Created
760 --
761
762
763 PROCEDURE compile_org_hierarchy_rates(rate_sch_rev_id IN number,
764 org_id IN number,
765 comp_type IN varchar2,
766 status IN OUT NOCOPY number,
767 stage IN OUT NOCOPY number)
768 IS
769 CURSOR org_cursor(ver_id NUMBER) /*Bug# 2933915 :removed bg_id for business_group_id from cursor as it is not reqd*/
770 IS
771 SELECT organization_id_child
772 FROM per_org_structure_elements
773 CONNECT BY PRIOR organization_id_child = organization_id_parent
774 AND org_structure_version_id = ver_id
775 START WITH organization_id_parent = org_id
776 AND org_structure_version_id = ver_id;
777
778 /*business_gid Number; Bug# 29399915*/
779 org_struc_ver_id Number;
780 start_org Number;
781
782
783 BEGIN
784
785 status := 0;
786
787 org_struc_ver_id := G_ORG_STRUC_VER_ID;
788 start_org := G_START_ORGANIZATION_ID ;
789 /*
790 * Commented for bug 4527736
791 *
792 pa_cost_plus.get_hierarchy_from_revision(rate_sch_rev_id,
793 org_struc_ver_id,
794 start_org,
795 status,
796 stage);
797
798
799 IF status <> 0 THEN
800 stage := 50;
801 RETURN;
802 END IF;
803 */
804 --
805 -- First compile the current organization
806 --
807
808 if (comp_type = 'INCREMENTAL') then
809
810 pa_cost_plus.disable_sch_rev_org(rate_sch_rev_id,
811 org_id,
812 status,
813 stage);
814
815 end if;
816
817 pa_cost_plus.compile_org_rates(rate_sch_rev_id,
818 org_id,
819 org_struc_ver_id,
820 start_org,
821 status,
822 stage);
823
824 IF status <> 0 THEN
825 RETURN;
826 END IF;
827
828 --
829 -- Compile all the organizations under this organization
830 -- Ues the for loop cursor to fetch one qualified row at a time
831 --
832
833 <<process_org>>
834 FOR org_row IN org_cursor(org_struc_ver_id) /*29399915 -Removed business_gid as it is not required */
835 LOOP
836
837 --
838 -- Create the compiled multipliers for the every organization.
839 --
840
841 if (comp_type = 'INCREMENTAL') then
842
843 pa_cost_plus.disable_sch_rev_org(rate_sch_rev_id,
844 org_row.organization_id_child,
845 status,
846 stage);
847
848 end if;
849
850 pa_cost_plus.compile_org_rates(rate_sch_rev_id,
851 org_row.organization_id_child,
852 org_struc_ver_id,
853 start_org,
854 status,
855 stage);
856
857 IF status <> 0 THEN
858 RETURN;
859 END IF;
860
861 END LOOP process_org;
862
863
864 --
865 -- Set the compilation time in the rate schedule revision
866 --
867
868 UPDATE pa_ind_rate_sch_revisions
869 SET
870 compiled_flag = 'Y',
871 compiled_date = SYSDATE
872 WHERE
873 ind_rate_sch_revision_id = rate_sch_rev_id;
874
875
876 END compile_org_hierarchy_rates;
877
878
879 --
880 -- PROCEDURE
881 -- new_organization
882 --
883 -- PURPOSE
884 -- The objective of this procedure is to create compiled rates for
885 -- a new organization and its sub-organizations
886 --
887 -- HISTORY
888 --
889 -- 19-AUG-94 S Lee Created
890 --
891
892
893 PROCEDURE new_organization(errbuf IN OUT NOCOPY varchar2,
894 retcode IN OUT NOCOPY varchar2,
895 organization_id IN varchar2)
896 IS
897 -- Local variables
898 l_org_id number;
899 status number;
900 stage number;
901 l_org_exists BOOLEAN;
902 /* l_business_gid NUMBER; commented as it is not used :Bug 2933915*/
903 l_org_struc_ver_id NUMBER;
904 l_start_org NUMBER;
905 l_compiled_set_id NUMBER;
906
907 -- Cursor definition
908
909 /* CURSOR rev_cursor IS
910 SELECT irsr.ind_rate_sch_revision_id
911 FROM pa_ind_rate_sch_revisions irsr
912 WHERE irsr.compiled_flag = 'Y' -- revision has been compiled before
913 AND irsr.ready_to_compile_flag = 'Y'; -- compilation is not on hold
914 *** cusor commented for bug 3033195 */
915
916 /*
917 * Bug#1163654
918 * cursor added to check the existence of compiled set information
919 * for a given revision and organization.
920 */
921
922 CURSOR rev_org_cursor(p_rev_id IN NUMBER, p_org_id IN NUMBER) IS /* p_org_id added for 3033195 */
923 SELECT '1'
924 FROM pa_ind_compiled_sets cmp
925 WHERE cmp.organization_id = p_org_id
926 AND cmp.ind_rate_sch_revision_id = p_rev_id
927 AND status = 'A';
928
929 -- Standard who
930 x_last_updated_by NUMBER(15);
931 x_last_update_login NUMBER(15);
932 x_request_id NUMBER(15);
933 x_program_application_id NUMBER(15);
934 x_program_id NUMBER(15);
935 x_created_by NUMBER(15);
936
937 /* Two cursors added below for bug 3033195 */
938
939 Cursor rev_struct_cursor is
940 SELECT ind_rate_sch_revision_id, org_structure_version_id,cost_plus_structure,start_organization_id /*4590268*/
941 FROM pa_ind_rate_sch_revisions
942 WHERE compiled_flag = 'Y'
943 AND ready_to_compile_flag = 'Y'
944 AND org_structure_version_id in
945 (select org_structure_version_id
946 from per_org_structure_elements
947 where organization_id_child = l_org_id
948 or organization_id_parent = l_org_id);
949
950 Cursor orgn_cursor(ver_id in NUMBER) is
951 SELECT level, organization_id_child organization_id
952 FROM per_org_structure_elements
953 CONNECT BY PRIOR organization_id_child = organization_id_parent
954 AND org_structure_version_id = ver_id
955 START WITH organization_id_parent = l_org_id
956 AND org_structure_version_id = ver_id
957 UNION ALL
958 SELECT 0,l_org_id organization_id FROM dual
959 ORDER BY 1;
960
961 BEGIN
962
963 l_org_id := to_number(organization_id);
964 G_MODULE := 'NEW_ORG'; /*3005625*/
965 /*3005625 :G_MODULE is set to value 'NEW_ORG' whenver process Add New organization is
966 run i.e new_organization is called.
967 This is to generate compiled multipliers for the new organization in all the burden
968 schedule revisions (even when nothing has changed in the revisions
969 i.e ready_to_compile_flag is <>'Y'/'X')*/
970 --
971 -- Get the standard who information
972 --
973 x_last_updated_by := FND_GLOBAL.USER_ID;
974 x_last_update_login := FND_GLOBAL.LOGIN_ID;
975 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
976 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
977 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
978 x_created_by := FND_GLOBAL.USER_ID;
979
980 /* 3033195 */
981
982 /*S.N. 4590268*/
983 FOR rev_struct_row in rev_struct_cursor LOOP
984 --###
985 IF (nvl(G_RATE_SCH_REVISION_ID,-999)=rev_struct_row.ind_rate_sch_revision_id
986 AND nvl(G_CP_STRUCTURE,-999)= rev_struct_row.cost_plus_structure
987 AND G_IMPACTED_COST_BASES_TAB.count <> 0 ) THEN
988
989 NULL;
990
991 ELSE
992 G_RATE_SCH_REVISION_ID := rev_struct_row.ind_rate_sch_revision_id;
993 G_CP_STRUCTURE := rev_struct_row.cost_plus_structure;
994
995 pa_cost_plus.Cache_Impacted_Cost_Bases( G_RATE_SCH_REVISION_ID
996 ,G_CP_STRUCTURE);
997 END IF;
998
999 G_ORG_STRUC_VER_ID := rev_struct_row.org_structure_version_id;
1000 G_START_ORGANIZATION_ID := rev_struct_row.start_organization_id;
1001 /*E.N. 4590268*/
1002
1003 FOR orgn_row in orgn_cursor(rev_struct_row.org_structure_version_id) LOOP
1004
1005 l_org_exists := FALSE;
1006
1007 FOR rev_org_row in rev_org_cursor(rev_struct_row.ind_rate_sch_revision_id,orgn_row.organization_id)
1008 LOOP
1009 l_org_exists := TRUE;
1010 END LOOP;
1011
1012 IF l_org_exists THEN
1013 NULL;
1014 ELSE
1015
1016 UPDATE pa_ind_rate_sch_revisions
1017 SET
1018 last_update_date = SYSDATE,
1019 last_updated_by = x_last_updated_by,
1020 last_update_login = x_last_update_login,
1021 request_id = x_request_id,
1022 program_application_id = x_program_application_id,
1023 program_id = x_program_id,
1024 program_update_date = SYSDATE
1025 WHERE
1026 ind_rate_sch_revision_id = rev_struct_row.ind_rate_sch_revision_id;
1027
1028 COMMIT;
1029
1030 pa_cost_plus.compile_org_hierarchy_rates(
1031 rev_struct_row.ind_rate_sch_revision_id,
1032 orgn_row.organization_id,
1033 'INCREMENTAL',
1034 status,
1035 stage);
1036
1037 if (status < 0) then
1038 errbuf := sqlerrm(status);
1039 retcode := 2;
1040 return;
1041 end if;
1042
1043 END IF; -- l_org_exists
1044
1045 END LOOP; -- orgn_cursor
1046
1047 END LOOP; -- rev_struct_cursor
1048
1049 EXCEPTION
1050 WHEN OTHERS THEN
1051 errbuf := sqlerrm(status);
1052 retcode := 2;
1053 RAISE;
1054
1055 END new_organization;
1056
1057 /* Code Changes End for 3033195 . The old code below has been commented for better readability. */
1058
1059 --
1060 -- compile all rate schedules
1061 --
1062
1063 -- FOR rev_row IN rev_cursor
1064 -- LOOP
1065 --
1066 --
1067 -- /*
1068 -- * Bug#1163654
1069 -- *
1070 -- * If compiled set exists for a given Organization and revision, then
1071 -- * no action is necessary. Else enter a record in the compiled set table
1072 -- * for the given revision and organization with the compiled set id same as
1073 -- * that of its parent
1074 -- */
1075 --
1076 -- l_org_exists := FALSE;
1077 --
1078 -- FOR rev_org_row in rev_org_cursor(rev_row.ind_rate_sch_revision_id)
1079 -- LOOP
1080 -- l_org_exists := TRUE;
1081 -- END LOOP;
1082 --
1083 -- /*Bug# 1851731:If compiled set information exists for a given organization and
1084 -- revision then no action but instead of exiting from the procedure ,continue for
1085 -- other revisions fetched by rev_cursor */
1086 --
1087 -- IF l_org_exists THEN
1088 -- /*RETURN; Commented for bug# 1851731*/
1089 -- NULL; /*Bug# 1851731*/
1090 -- ELSE
1091 --
1092 --
1093 -- Set the compilation time in the rate schedule revision
1094 --
1095
1096 -- UPDATE pa_ind_rate_sch_revisions
1097 -- SET
1098 -- last_update_date = SYSDATE,
1099 -- last_updated_by = x_last_updated_by,
1100 -- last_update_login = x_last_update_login,
1101 -- request_id = x_request_id,
1102 -- program_application_id = x_program_application_id,
1103 -- program_id = x_program_id,
1104 -- program_update_date = SYSDATE
1105 -- WHERE
1106 -- ind_rate_sch_revision_id = rev_row.ind_rate_sch_revision_id;
1107 --
1108 -- COMMIT;
1109 --
1110 -- pa_cost_plus.compile_org_hierarchy_rates(
1111 -- rev_row.ind_rate_sch_revision_id,
1112 -- l_org_id,
1113 -- 'INCREMENTAL',
1114 -- status,
1115 -- stage);
1116 --
1117 -- if (status < 0) then
1118 -- errbuf := sqlerrm(status);
1119 -- retcode := 2;
1120 -- return;
1121 -- end if;
1122 --
1123 --
1124 --
1125 /*Bug# 1851731:Commented the SELECT and INSERT below as they are redundant.*/
1126 /*The code in SELECT and INSERT was carried over from R11.0 and introduced in*/
1127 /*this procedure as part of code fix for bug# 1163654. But it is not required*/
1128 /*here as code in R11i is restructured.*/
1129
1130 /*
1131 * Get compiled set id of the parent.
1132 */
1133 /*Bug# 1851731:
1134 SELECT + ORDERED
1135 INDEX(ose PER_ORG_STRUCTURE_ELEMENTS_FK4)
1136 INDEX(ics PA_IND_COMPILED_SETS_N1)
1137 ics.ind_compiled_set_id
1138 INTO l_compiled_set_id
1139 FROM per_org_structure_elements ose,
1140 pa_ind_compiled_sets ics
1141 WHERE ose.organization_id_child = l_org_id
1142 AND ose.org_structure_version_id = l_org_struc_ver_id
1143 AND ose.organization_id_parent = ics.organization_id
1144 AND ics.ind_rate_sch_revision_id = rev_row.ind_rate_sch_revision_id
1145 AND ics.status = 'A'; Commented for bug# 1851731*/
1146
1147 /*
1148 * Insert compiled set information
1149 */
1150
1151 /* Bug# 1851731 : INSERT INTO pa_ind_compiled_sets
1152 (ind_compiled_set_id,
1153 ind_rate_sch_revision_id,
1154 organization_id,
1155 last_update_date,
1156 last_updated_by,
1157 created_by,
1158 creation_date,
1159 last_update_login,
1160 request_id,
1161 program_application_id,
1162 program_id,
1163 program_update_date,
1164 status)
1165 VALUES(l_compiled_set_id,
1166 rev_row.ind_rate_sch_revision_id,
1167 l_org_id,
1168 SYSDATE,
1169 x_last_updated_by,
1170 x_created_by,
1171 SYSDATE,
1172 x_last_update_login,
1173 x_request_id,
1174 x_program_application_id,
1175 x_program_id,
1176 SYSDATE,
1177 'A'); Commented for bug# 1851731 */
1178 -- END IF;
1179 -- END LOOP;
1180 --EXCEPTION
1181 -- WHEN OTHERS THEN
1182 -- errbuf := sqlerrm(status);
1183 -- retcode := 2;
1184 -- RAISE;
1185 --
1186 --END new_organization;
1187
1188
1189 --
1190 -- PROCEDURE
1191 -- compile_schedule
1192 --
1193 -- PURPOSE
1194 -- The objective of this procedure is to create compiled rates
1195 -- using a specific rate schedule.
1196 --
1197 -- HISTORY
1198 --
1199 -- 08-JUN-94 S Lee Created
1200 --
1201
1202
1203 PROCEDURE compile_schedule(errbuf IN OUT NOCOPY varchar2,
1204 retcode IN OUT NOCOPY varchar2,
1205 sch_rev_id IN varchar2)
1206
1207 IS
1208 -- Local variables
1209 /* business_gid Number; commented as it is not used :Bug 2933915*/
1210 org_struc_ver_id Number;
1211 start_org Number;
1212
1213 status number;
1214 stage number;
1215 rate_sch_rev_id number;
1216 org_tab org_tab_type; /*2933915*/ /*To store top impacted orgs*/
1217 lstatus number ; /*2933915*/
1218 l_check number ; /*3055700*/
1219 l_cp_structure pa_cost_plus_structures.cost_plus_structure%TYPE ; /*3055700*/
1220 -- Standard who
1221 x_last_updated_by NUMBER(15);
1222 x_last_update_login NUMBER(15);
1223 x_request_id NUMBER(15);
1224 x_program_application_id NUMBER(15);
1225 x_program_id NUMBER(15);
1226 l_created_by NUMBER(15); /*3055700*/
1227
1228 /*3055700 :cursor to select all the cost bases */
1229 CURSOR all_cost_bases
1230 IS
1231 SELECT
1232 distinct cost_base
1233 FROM pa_cost_base_cost_codes
1234 WHERE cost_plus_structure =l_cp_structure
1235 AND cost_base_type = INDIRECT_COST_CODE ;
1236
1237 BEGIN
1238 lstatus :=0; /*2933915*/
1239 status := 0;
1240 rate_sch_rev_id := sch_rev_id;
1241 G_RATE_SCH_REVISION_ID := rate_sch_rev_id;
1242 --
1243 -- Get the standard who information
1244 --
1245 x_last_updated_by := FND_GLOBAL.USER_ID;
1246 x_last_update_login := FND_GLOBAL.LOGIN_ID;
1247 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1248 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
1249 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1250 l_created_by := -999999;
1251 --
1252 -- Set the compilation time in the rate schedule revision
1253 --
1254
1255 UPDATE pa_ind_rate_sch_revisions
1256 SET
1257 last_update_date = SYSDATE,
1258 last_updated_by = x_last_updated_by,
1259 last_update_login = x_last_update_login,
1260 request_id = x_request_id,
1261 program_application_id = x_program_application_id,
1262 program_id = x_program_id,
1263 program_update_date = SYSDATE
1264 WHERE
1265 --ind_rate_sch_revision_id = rate_sch_rev_id; 4527736
1266 ind_rate_sch_revision_id = G_RATE_SCH_REVISION_ID;
1267 COMMIT;
1268
1269 --
1270 -- Get the current org_structure_version_id.
1271 -- Join can not be used in a CONNECT BY statement
1272 --
1273
1274
1275 pa_cost_plus.get_hierarchy_from_revision(G_RATE_SCH_REVISION_ID, /* 4527736 */
1276 org_struc_ver_id,
1277 start_org,
1278 status,
1279 stage);
1280
1281 if (status < 0) then
1282 errbuf := sqlerrm (status);
1283 retcode := 2;
1284 return;
1285 end if;
1286 G_ORG_STRUC_VER_ID := org_struc_ver_id;
1287 G_START_ORGANIZATION_ID := start_org;
1288
1289 /*Changes for Bug# 3055700:
1290 Inserting dummy enteries of 0 in pa_ind_cost_multipliers for Start Org and all the cost
1291 bases when the version is compiled .
1292 This has to be done when there exists no compiled set ids for ALL the cost bases with
1293 status 'A' for start_org .
1294 This will ensure that the compiled set ids are generated for all the orgs in hierarchy
1295 and all the cost bases .
1296 Subsequently any changes in multiplies will affect only impacted org and
1297 impacted cost base as per enhancement .
1298 *********************************************************************************************/
1299
1300 pa_cost_plus.get_cost_plus_structure(rate_sch_rev_id,
1301 l_cp_structure,
1302 status,
1303 stage);
1304
1305 IF (status <> 0) THEN
1306 errbuf := sqlerrm (status);
1307 return;
1308 END IF;
1309 G_CP_STRUCTURE := l_cp_structure;
1310
1311 /* 4527736
1312 * Call Cache_Impacted_Cost_Bases.
1313 */
1314 /* pa_cost_plus.Cache_Impacted_Cost_Bases( G_RATE_SCH_REVISION_ID
1315 ,G_CP_STRUCTURE); Commented for Bug 5181688 */
1316
1317 Begin
1318 /*If for start_org there are compiled set ids present with status 'A' for ALL the cost bases then
1319 l_check = 1 else 0*/
1320 FOR base_rec in all_cost_bases
1321 LOOP
1322 select 1
1323 into l_check
1324 from sys.dual
1325 where exists(select 1
1326 from pa_ind_compiled_sets
1327 where ind_rate_sch_revision_id =G_RATE_SCH_REVISION_ID /* 4527736 */
1328 and organization_id =start_org
1329 and cost_base = base_rec.cost_base
1330 and status ='A');
1331 END LOOP;
1332 Exception
1333 WHEN NO_DATA_FOUND THEN
1334 l_check :=0 ;
1335 End;
1336
1337 IF (l_check =0) THEN
1338 /*If explicit multipliers are not defined for start org for ALL cost codes then only insert*/
1339
1340 IF pa_cost_plus.check_for_explicit_multiplier(G_RATE_SCH_REVISION_ID ,start_org) =0 THEN /* 0 means not present*/
1341
1342 Begin
1343
1344 INSERT into pa_ind_cost_multipliers (ind_rate_sch_revision_id,
1345 organization_id,
1346 ind_cost_code,
1347 multiplier,
1348 last_update_date,
1349 last_updated_by,
1350 created_by,
1351 creation_date,
1352 last_update_login,
1353 ready_to_compile_flag)
1354 select G_RATE_SCH_REVISION_ID,
1355 start_org,
1356 cbicc.ind_cost_code,
1357 0,
1358 SYSDATE,
1359 x_last_updated_by,
1360 l_created_by,
1361 SYSDATE,
1362 x_last_update_login,
1363 'Y'
1364 from pa_cost_base_cost_codes cbicc
1365 where cbicc.cost_plus_structure = G_CP_STRUCTURE /* 4527736 */
1366 and cbicc.cost_base_type = INDIRECT_COST_CODE
1367 and cbicc.ind_cost_code not in (select m.ind_cost_code
1368 from pa_ind_cost_multipliers m
1369 where m.ind_rate_sch_revision_id = G_RATE_SCH_REVISION_ID /* 4527736 */
1370 and m.organization_id =start_org)
1371 group by cbicc.ind_cost_code;
1372
1373 Exception
1374 WHEN OTHERS THEN
1375 status := SQLCODE;
1376 End ;
1377 End if ;
1378 End if ; /*l_check =0*/
1379
1380 /*End of changes for 3055700 */
1381
1382 /*Bug 5181688 */
1383 pa_cost_plus.Cache_Impacted_Cost_Bases( G_RATE_SCH_REVISION_ID
1384 ,G_CP_STRUCTURE);
1385
1386
1387 /*2933915 :Added call to procedure find_impacted_top_org() that should return the PL/SQL table of
1388 AFFECTED TOP organization org_list*/
1389 /*Basically for this the hierarchy attached to the revision is traversed and we find the top most
1390 organizations out of the complete set of organizations for which the multipliers have changed.
1391 This is to avoid compiling ALL the organizations in the hierarchy as was happening earlier and hence
1392 to start compiling from the top most impacted orgs */
1393
1394 /* 4527736 */
1395 pa_cost_plus.find_impacted_top_org(G_RATE_SCH_REVISION_ID,G_ORG_STRUC_VER_ID,start_org,org_tab,status); /*2933915*/
1396
1397 if (status < 0) then /*2933915*/
1398
1399 errbuf := sqlerrm (status);
1400 retcode := 2;
1401 return;
1402 end if;
1403 --
1404 -- Verify whether there is any costed expenditure item
1405 --
1406
1407 -- 4527736
1408 pa_cost_plus.check_revision_used(G_RATE_SCH_REVISION_ID, lstatus, stage); /*2933915*/
1409
1410 /*2933915 : FOR SELECTIVE DELETION AND OBSOLETION */
1411 /***Added a LOOP for the processing to go for each TOP IMPACTED organization returned by the above procedure in org_tab
1412 We are going to process for all the children of the top affected org **********/
1413
1414
1415 IF org_tab.exists(1) THEN
1416 FOR i in org_tab.first..org_tab.last
1417 LOOP
1418
1419 /*4642011 if (lstatus = 0) then /*2933915*/
1420
1421
1422 /*4642011 delete_rate_sch_revision(G_RATE_SCH_REVISION_ID,G_ORG_STRUC_VER_ID,org_tab(i),status,stage); --2933915
1423
1424 if (status < 0) then
1425 errbuf := sqlerrm (status);
1426 retcode := 2;
1427 return;
1428 end if; */
1429
1430 /************************MOVED ALL THIS to new procedure delete_rate_sch_revision()
1431 -
1432
1433 if (status = 0) then
1434
1435 --
1436 -- Remove redundant compiled sets and multipiers.
1437 --
1438 DELETE pa_compiled_multipliers
1439 WHERE ind_compiled_set_id IN
1440 (SELECT ind_compiled_set_id
1441 FROM pa_ind_compiled_sets
1442 WHERE ind_rate_sch_revision_id = rate_sch_rev_id);
1443
1444 DELETE pa_ind_compiled_sets
1445 WHERE ind_rate_sch_revision_id = rate_sch_rev_id;
1446 ***************************************************************************************/
1447 /*4642011
1448 else
1449 /*4642011*/
1450 --
1451 -- Disable the current rate schedule if any
1452 --
1453 -- 4527736
1454 pa_cost_plus.disable_rate_sch_revision(G_RATE_SCH_REVISION_ID,G_ORG_STRUC_VER_ID,org_tab(i),status, stage );
1455 /*2933915 :ADDED org_struc_ver_id and org_id as parameter *****/
1456 if (status < 0) then
1457 errbuf := sqlerrm (status);
1458 retcode := 2;
1459 return;
1460 end if;
1461
1462 /*4642011
1463 end if;
1464 /*4642011*/
1465
1466 --
1467 -- Compile rates for all organizations starting from the top impacted organization
1468 --
1469
1470 pa_cost_plus.compile_org_hierarchy_rates(G_RATE_SCH_REVISION_ID,
1471 org_tab(i), /*2933915 : Replaced start_org by org_tab(i)*/
1472 'ALL',
1473 status,
1474 stage);
1475
1476 if (status < 0) then
1477 errbuf := sqlerrm (status);
1478 retcode := 2;
1479 return;
1480 end if;
1481
1482 END LOOP ; /*2933915*/
1483 END IF ; /*If org_tab.exists*/
1484
1485 -- Mark impacted expenditure items for re-costing
1486
1487 pa_cost_plus.mark_impacted_exp_items(G_RATE_SCH_REVISION_ID,status, stage);
1488
1489 if (status < 0) then
1490 errbuf := sqlerrm (status);
1491 retcode := 2;
1492 return;
1493 end if;
1494
1495 /*2933915 :After compilation is over we need to reset the ready_to_compile_flag back to 'N' for this revision_id and also
1496 delete the records having ready_to_compile_flag as 'X' .
1497 For reference :Ready_to_compile_flag 'X' records are actually deleted records but they were retained till this point of time for
1498 processing and treating them as impacted records***/
1499
1500 /*3055700 :Deleting the dummy entries inserted earlier since by now processing is done*/
1501 DELETE pa_ind_cost_multipliers
1502 where ind_rate_sch_revision_id = G_RATE_SCH_REVISION_ID
1503 and organization_id = start_org
1504 and created_by = l_created_by ;
1505 /*3055700*/
1506
1507 DELETE pa_ind_cost_multipliers
1508 where ind_rate_sch_revision_id = G_RATE_SCH_REVISION_ID
1509 and nvl(ready_to_compile_flag,'N') ='X';
1510
1511 UPDATE pa_ind_cost_multipliers
1512 set ready_to_compile_flag ='N'
1513 where ind_rate_sch_revision_id = G_RATE_SCH_REVISION_ID
1514 and nvl(ready_to_compile_flag,'N') ='Y';
1515
1516 COMMIT;
1517
1518 /*End of changes for bug 2933915*/
1519
1520 END compile_schedule;
1521
1522
1523
1524 --
1525 -- PROCEDURE
1526 -- compile_all
1527 --
1528 -- PURPOSE
1529 -- The objective of this procedure is to create compiled rates
1530 -- for all rate schedules which have been marked as 'ready to
1531 -- compile'.
1532 --
1533 -- HISTORY
1534 --
1535 -- 22-JUN-94 S Lee Created
1536 --
1537
1538
1539 PROCEDURE compile_all(errbuf IN OUT NOCOPY varchar2,
1540 retcode IN OUT NOCOPY varchar2)
1541
1542 IS
1543 --
1544 -- Cursor definition
1545 --
1546
1547 /*Bug 2933915 :We need to compile only the schedules wherein some multiplier has changed .Hence modified the cursor for the same.*/
1548
1549 CURSOR sch_cursor
1550 IS
1551 SELECT ind_rate_sch_revision_id
1552 FROM pa_ind_rate_sch_revisions irsr
1553 WHERE irsr.compiled_flag = 'N'
1554 AND nvl(irsr.ready_to_compile_flag,'N') = 'Y'
1555 AND EXISTS (Select 1
1556 from pa_ind_cost_multipliers icm
1557 WHERE icm.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
1558 AND nvl(icm.ready_to_compile_flag,'N') in ('Y','X'));
1559
1560 -- Local variables
1561
1562 sch_rev_id varchar2(30);
1563
1564 BEGIN
1565
1566 <<process_sch>>
1567 FOR sch_row IN sch_cursor
1568 LOOP
1569
1570 BEGIN
1571 sch_rev_id := sch_row.ind_rate_sch_revision_id;
1572
1573 pa_cost_plus.compile_schedule(errbuf,
1574 retcode,
1575 sch_rev_id);
1576
1577 END;
1578
1579 END LOOP process_sch;
1580
1581 END compile_all;
1582
1583
1584
1585 --
1586 -- PROCEDURE
1587 -- get_exp_item_indirect_cost
1588 --
1589 -- PURPOSE
1590 -- The objective of this procedure is to retrieve the total
1591 -- indirect cost for an expenditure item. User can specify the
1592 -- expenditure item information and the type of indirect rate
1593 -- schedule, and get the total amount of indirect cost associated
1594 -- with the expenditure item.
1595 --
1596 -- Note: This procedure gets called from both the project and expenditure
1597 -- oriented process. Hence should always refer to the base tables
1598 -- and not the Morg view.
1599 --
1600 -- HISTORY
1601 --
1602 -- 10-JUN-94 S Lee Created
1603 --
1604
1605
1606 /*
1607 Multi-Currency Related Changes:
1608 Two new parameters added
1609 indirect_cost_acct
1610 indirect_cost_denom
1611 */
1612 procedure get_exp_item_indirect_cost(exp_item_id IN Number,
1613 schedule_type IN Varchar2,
1614 indirect_cost IN OUT NOCOPY Number,
1615 indirect_cost_acct IN OUT NOCOPY Number,
1616 indirect_cost_denom IN OUT NOCOPY Number,
1617 indirect_cost_project IN OUT NOCOPY Number, /* EPP */
1618 rate_sch_rev_id IN OUT NOCOPY Number,
1619 compiled_set_id IN OUT NOCOPY Number,
1620 status IN OUT NOCOPY Number,
1621 stage IN OUT NOCOPY Number)
1622
1623 IS
1624
1625 --
1626 -- Local variables
1627 --
1628
1629 /*
1630 Multi-Currency Related Changes: New Local variables added
1631 direct_cost_denom, direct_cost_acct
1632 burden_cost_denom, burden_cost_acct
1633 compiled_multiplier
1634 */
1635 exp_type Varchar2(30);
1636 cp_structure Varchar2(30);
1637 c_base Varchar2(30);
1638 org_id Number(15);
1639 direct_cost Number(22,5);
1640 direct_cost_denom Number;
1641 direct_cost_acct Number;
1642 direct_cost_project Number; /* ProjCurr Changes */
1643 l_denom_currency_code VARCHAR2(15);
1644 l_acct_currency_code VARCHAR2(15);
1645 l_project_currency_code VARCHAR2(15);
1646 l_projfunc_currency_code VARCHAR2(15);
1647 quantity pa_expenditure_items_all.quantity%TYPE;
1648 burden_cost pa_expenditure_items_all.burden_cost%TYPE;
1649 burden_cost_denom pa_expenditure_items_all.denom_burdened_cost%TYPE;
1650 burden_cost_acct pa_expenditure_items_all.acct_burdened_cost%TYPE;
1651 burden_cost_project pa_expenditure_items_all.project_burdened_cost%TYPE; /* ProjCurr Changes */
1652 system_linkage pa_expenditure_items_all.system_linkage_function%TYPE;
1653 compiled_multiplier pa_compiled_multipliers.compiled_multiplier%TYPE;
1654
1655 BEGIN
1656
1657 -- Initialize output parameters
1658 status := 0;
1659 stage := 100;
1660 indirect_cost := NULL;
1661 rate_sch_rev_id := NULL;
1662 compiled_set_id := NULL;
1663
1664 --
1665 -- Retrieve the information of the expenditure item
1666 -- As this procedure can be called from project Oriented or Expenditure
1667 -- oriented process, hence the uderlying select uses base table.
1668 --
1669
1670 /*
1671 Multi-Currency Related Changes:
1672 Select additional columns
1673 denom_raw_cost, Acct_raw_cost
1674 denom_burdened_cost,Acct_burdened_cost
1675 */
1676 SELECT expenditure_type,
1677 raw_cost,
1678 denom_raw_cost,
1679 acct_raw_cost,
1680 project_raw_cost,
1681 quantity,
1682 burden_cost,
1683 denom_burdened_cost,
1684 acct_burdened_cost,
1685 project_burdened_cost, /* ProjCurr Changes */
1686 projfunc_currency_code, /* ProjCurr Changes */
1687 acct_currency_code,
1688 denom_currency_code,
1689 project_currency_code,
1690 system_linkage_function
1691 INTO exp_type,
1692 direct_cost,
1693 direct_cost_denom,
1694 direct_cost_acct,
1695 direct_cost_project,
1696 quantity,
1697 burden_cost,
1698 burden_cost_denom,
1699 burden_cost_acct,
1700 burden_cost_project, /* ProjCurr Changes */
1701 l_projfunc_currency_code, /* ProjCurr Changes */
1702 l_acct_currency_code,
1703 l_denom_currency_code,
1704 l_project_currency_code,
1705 system_linkage
1706 FROM pa_expenditure_items_all
1707 WHERE expenditure_item_id = exp_item_id;
1708
1709 -- For Project Manufacturing, specifically BURDEN_TRANSACTIONS, raw_cost,
1710 -- quantity will be zero but burden_cost will not be zero
1711 -- For Billing purpose (Revenue and Invoicing) we will take burden_cost and
1712 -- apply burden multipliers on burden_cost.
1713 --
1714 -- Except Burden Transactions, there will not be any items with raw_cost and
1715 -- quantity=0 with burden_cost <> 0
1716 -- Following query will be modified in R11 for getting system_linkage from
1717 -- new intersection entity
1718 --
1719 /*
1720 Multi-Currency Related Changes:
1721 Set direct_cost_denom and direct_cost_acct also.
1722 */
1723 IF (direct_cost = 0 AND quantity = 0 AND burden_cost <> 0)
1724 AND ( schedule_type='R' OR schedule_type='I') THEN
1725 IF ( system_linkage='BTC') THEN
1726 direct_cost := burden_cost;
1727 direct_cost_denom := burden_cost_denom;
1728 direct_cost_acct := burden_cost_acct;
1729 direct_cost_project := burden_cost_project; /* epp */
1730 END IF;
1731 END IF;
1732 --
1733 -- Get the rate schedule revision id
1734 --
1735
1736 pa_cost_plus.get_rate_sch_rev_id(exp_item_id,
1737 schedule_type,
1738 rate_sch_rev_id,
1739 status,
1740 stage);
1741
1742 IF (status <> 0) THEN
1743 stage := 200;
1744 return;
1745 END IF;
1746
1747 --
1748 -- Get the cost plus structure
1749 --
1750
1751 pa_cost_plus.get_cost_plus_structure(rate_sch_rev_id,
1752 cp_structure,
1753 status,
1754 stage);
1755
1756 IF (status <> 0) THEN
1757 stage := 300;
1758 return;
1759 END IF;
1760
1761
1762 --
1763 -- Get the cost base
1764 --
1765
1766 pa_cost_plus.get_cost_base(exp_type,
1767 cp_structure,
1768 c_base,
1769 status,
1770 stage);
1771
1772 IF (status <> 0) THEN
1773 stage := 400;
1774 return;
1775 END IF;
1776
1777
1778 --
1779 -- Get the organization
1780 --
1781
1782 pa_cost_plus.get_organization_id(exp_item_id,
1783 org_id,
1784 status,
1785 stage);
1786
1787 IF (status <> 0) THEN
1788 stage := 500;
1789 return;
1790 END IF;
1791
1792 /*
1793 Multi-Currency Related Changes:
1794 The Call to Get_indirect_cost_sum is removed
1795 */
1796 --
1797 -- Get the indirect cost
1798 --
1799
1800 /*
1801 pa_cost_plus.get_indirect_cost_sum(org_id,
1802 c_base,
1803 rate_sch_rev_id,
1804 direct_cost,
1805 2, -- FOR US CURRENCY
1806 indirect_cost,
1807 status,
1808 stage);
1809
1810 */
1811
1812 /*
1813 Multi-Currency Related Changes:
1814 Get_compiled_multiplier is called to get the sum of the compiled multipliers.
1815 Use that multiplier to get the indirect costs in all the currencies.
1816 */
1817 --
1818 -- Get the sum of the compiled Multipliers
1819 --
1820
1821 /*
1822 * Bug#2110452
1823 * Commented to implement the same logic for burden cost calculation
1824 * as is used in R10.7/R11.0.
1825 *
1826 * pa_cost_plus.get_compiled_multiplier(org_id,
1827 * c_base,
1828 * rate_sch_rev_id,
1829 * compiled_multiplier,
1830 * status,
1831 * stage);
1832 */
1833
1834 /*
1835 * Bug#2110452
1836 * To implement the same logic as is used in R10.7/R11.0 for
1837 * burden cost calculation.
1838 */
1839
1840 pa_cost_plus.get_indirect_cost_sum1 ( org_id => org_id
1841 ,c_base => c_base
1842 ,rate_sch_rev_id => rate_sch_rev_id
1843 ,direct_cost => direct_cost
1844 ,direct_cost_denom => direct_cost_denom
1845 ,direct_cost_acct => direct_cost_acct
1846 ,direct_cost_project => direct_cost_project
1847 ,precision => 2 -- FOR US CURRENCY
1848 ,indirect_cost_sum => indirect_cost
1849 ,indirect_cost_denom_sum => indirect_cost_denom
1850 ,indirect_cost_acct_sum => indirect_cost_acct
1851 ,indirect_cost_project_sum => indirect_cost_project
1852 ,l_projfunc_currency_code => l_projfunc_currency_code
1853 ,l_project_currency_code => l_project_currency_code
1854 ,l_acct_currency_code => l_acct_currency_code
1855 ,l_denom_currency_code => l_denom_currency_code
1856 ,status => status
1857 ,stage => stage
1858 );
1859
1860 IF (status <> 0) THEN
1861 stage := 600;
1862 return;
1863 END IF;
1864
1865 /*
1866 * Bug#2110452
1867 *
1868 * indirect_cost := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(direct_cost*compiled_multiplier,
1869 * l_project_currency_code);
1870 * indirect_cost_denom := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(direct_cost_denom*compiled_multiplier,
1871 * l_denom_currency_code);
1872 * indirect_cost_acct := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(direct_cost_acct*compiled_multiplier,
1873 * l_acct_currency_code);
1874 * indirect_cost_project := PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT(direct_cost_project*compiled_multiplier,
1875 * l_project_currency_code);
1876 */
1877
1878 stage := 700;
1879
1880 --
1881 -- Get the compiled set id
1882 --
1883
1884 SELECT ind_compiled_set_id
1885 INTO compiled_set_id
1886 FROM pa_ind_compiled_sets
1887 WHERE ind_rate_sch_revision_id = rate_sch_rev_id
1888 AND organization_id = org_id
1889 AND cost_base = c_base /*Bug# 2933915*/
1890 AND STATUS = 'A';
1891
1892
1893 EXCEPTION
1894
1895 WHEN OTHERS THEN
1896 status := SQLCODE;
1897
1898 END get_exp_item_indirect_cost;
1899
1900
1901 --
1902 -- PROCEDURE
1903 -- get_exp_item_burden_amount
1904 --
1905 -- PURPOSE
1906 -- This is a pseudo procedure that provides a shell of
1907 -- get_exp_item_indirect_cost. This procedure is introduced
1908 -- due to terminology change.
1909 --
1910 -- HISTORY
1911 --
1912 -- 30-DEC-94 S Lee Created
1913 --
1914
1915
1916 procedure get_exp_item_burden_amount(exp_item_id IN Number,
1917 schedule_type IN Varchar2,
1918 burden_amount IN OUT NOCOPY Number,
1919 rate_sch_rev_id IN OUT NOCOPY Number,
1920 compiled_set_id IN OUT NOCOPY Number,
1921 status IN OUT NOCOPY Number,
1922 stage IN OUT NOCOPY Number)
1923
1924 IS
1925 /*
1926 Multi-Currency Related Changes:
1927 Interface of the procedure get_exp_item_indirect_cost is changed.
1928 Two additional parameters are passed but not used within this procedure.
1929 */
1930 /*
1931 * EPP.
1932 * Project Currency related Changes.
1933 * Passing new parameter burden_amount_project.
1934 */
1935 burden_amount_denom PA_EXPENDITURE_ITEMS.Denom_Burdened_cost%TYPE;
1936 burden_amount_Acct PA_EXPENDITURE_ITEMS.Acct_Burdened_cost%TYPE;
1937 burden_amount_Project PA_EXPENDITURE_ITEMS.Project_Burdened_cost%TYPE; /* epp */
1938 BEGIN
1939 pa_cost_plus.get_exp_item_indirect_cost(exp_item_id,
1940 schedule_type,
1941 burden_amount,
1942 burden_amount_denom,
1943 burden_amount_acct,
1944 burden_amount_project, /* epp */
1945 rate_sch_rev_id,
1946 compiled_set_id,
1947 status,
1948 stage);
1949
1950
1951 END get_exp_item_burden_amount;
1952
1953 --
1954 -- PROCEDURE
1955 -- populate_indirect_cost
1956 --
1957 -- PURPOSE
1958 -- The objective of this procedure is to populate the total
1959 -- indirect cost for an expenditure item.
1960 --
1961 -- HISTORY
1962 --
1963 -- 09-AUG-94 S Lee Created
1964 --
1965
1966 PROCEDURE populate_indirect_cost(update_count IN OUT NOCOPY NUMBER)
1967 IS
1968
1969 -- Cursor definition
1970 /*
1971 Multi-Currency Related Changes:
1972 Acct_Raw_Cost and Denom_Raw_Cost picked up; also the check is done on the basis
1973 of Denom Costs. (previously the checks were done on the basis of raw_cost)
1974 */
1975 /*
1976 Burdening related changes:
1977 record is picked up for burdening if either of three buckets is null
1978 */
1979 /*
1980 * Bug# 855461
1981 * Denom_burdened_cost and transaction_source also picked up.
1982 */
1983 /*
1984 * Bug# 913273
1985 * BTC Items are not processed here. It is done within
1986 * Costing process.
1987 *
1988 * Bug#1002399
1989 * 'OT' items are not processed . Removed this condition for Bug #1946968
1990 */
1991 CURSOR Exp_Item_Cursor IS
1992 SELECT ITEM.Expenditure_Item_ID,
1993 ITEM.Raw_Cost,
1994 ITEM.Acct_Raw_Cost,
1995 ITEM.Project_Raw_Cost, /* ProjCurr Changes */
1996 ITEM.Denom_Raw_Cost,
1997 ITEM.Denom_Burdened_Cost,
1998 ITEM.Burden_Cost,
1999 ITEM.Acct_burdened_Cost,
2000 ITEM.Project_burdened_Cost,
2001 ITEM.Transaction_Source,
2002 ITEM.Quantity,
2003 ITEM.Raw_Cost_Rate,
2004 ITEM.System_Linkage_Function,
2005 ITEM.cost_ind_compiled_set_id,
2006 nvl(ITEM.net_zero_adjustment_flag,'N') net_zero_adjustment_flag,
2007 TYPE.burden_amt_display_method,
2008 ITEM.adjusted_expenditure_item_id -- Bug 3893837
2009 FROM PA_Expenditure_Items ITEM,
2010 --PA_TASKS TASK, /* Bug 3458139 */
2011 PA_PROJECTS_ALL PROJ,
2012 PA_PROJECT_TYPES_ALL TYPE
2013 WHERE ITEM.Cost_Distributed_Flag = 'S'
2014 AND ITEM.Denom_Raw_Cost IS NOT NULL
2015 AND (ITEM.Denom_Burdened_Cost IS NULL
2016 OR
2017 ITEM.Acct_Burdened_Cost IS NULL
2018 OR
2019 ITEM.Burden_Cost IS NULL
2020 OR
2021 ITEM.Transferred_from_exp_item_id IS NOT NULL /*2217540*/
2022 OR /* 2328366 */
2023 EXISTS (SELECT 1
2024 FROM PA_TRANSACTION_SOURCES PTS
2025 WHERE PTS.Transaction_source = ITEM.Transaction_source
2026 AND PTS.Allow_Burden_Flag = 'Y')
2027 OR ITEM.cost_ind_compiled_set_id is null) /* 3008365 */
2028 AND ITEM.Cost_Dist_Rejection_Code IS NULL
2029 --AND ITEM.Task_ID = TASK.Task_ID /* Bug 3458139 */
2030 --AND TASK.Project_ID = PROJ.Project_ID /* Bug 3458139 */
2031 AND ITEM.Project_Id = PROJ.Project_Id /* Bug 3458139 */
2032 AND PROJ.Project_Type = TYPE.Project_Type
2033 /* AND nvl(TYPE.Org_Id, -99) = nvl(PROJ.Org_Id, -99) bug 5374745 */
2034 AND TYPE.Org_id = PROJ.Org_id -- bug 5374745
2035 AND TYPE.burden_cost_flag = 'Y'
2036 -- AND ITEM.System_Linkage_Function NOT IN ('BTC','OT'); /* Commented for Bug#1946968 */
2037 AND ITEM.System_Linkage_Function <> 'BTC'
2038 ORDER BY ITEM.Expenditure_Item_ID; /*for bug 6066796*/
2039 /*
2040 Multi-Currency Related Changes:
2041 New Variables defined : indirect_cost_denom, indirect_cost_acct
2042 total_burden_cost_denom, total_burden_cost_acct
2043 */
2044 indirect_cost PA_Expenditure_Items.Burden_Cost%TYPE;
2045 indirect_cost_denom PA_Expenditure_Items.Denom_Burdened_Cost%TYPE;
2046 indirect_cost_acct PA_Expenditure_Items.Acct_Burdened_Cost%TYPE;
2047 indirect_cost_project PA_Expenditure_Items.Project_Burdened_Cost%TYPE; /* ProjCurr Changes */
2048 burdened_cost PA_Expenditure_Items.Burden_Cost%TYPE;
2049 burdened_cost_denom PA_Expenditure_Items.Denom_Burdened_Cost%TYPE;
2050 burdened_cost_acct PA_Expenditure_Items.Acct_Burdened_Cost%TYPE;
2051 burdened_cost_project PA_Expenditure_Items.Project_Burdened_Cost%TYPE; /* ProjCurr Changes */
2052 x_Burden_Cost_Rate PA_Expenditure_Items.Burden_Cost_Rate%TYPE;
2053 total_burden_cost PA_Expenditure_Items.Burden_Cost%TYPE;
2054 total_burden_cost_denom PA_Expenditure_Items.Denom_Burdened_Cost%TYPE;
2055 total_burden_cost_acct PA_Expenditure_Items.Acct_Burdened_Cost%TYPE;
2056 total_burden_cost_project PA_Expenditure_Items.Project_Burdened_Cost%TYPE; /* ProjCurr Changes */
2057 qty PA_Expenditure_Items.Quantity%TYPE;
2058 compiled_set_id PA_Expenditure_Items.Cost_Ind_Compiled_Set_ID%TYPE;
2059 reason PA_Expenditure_Items.Ind_Cost_Dist_Rejection_Code%TYPE;
2060 rate_sch_rev_id PA_Ind_Rate_Sch_Revisions.Ind_Rate_Sch_Revision_ID%TYPE;
2061 status number(15);
2062 stage number(15);
2063 /*
2064 * Bug# 855461
2065 * New Variables defined
2066 *
2067 * the variable l_api_call_reqd is used to save the
2068 * call to general API for the imported trnasction with
2069 * transaction source. allow burden = 'Y'
2070 *
2071 */
2072 l_allow_burden_flag PA_TRANSACTION_SOURCES.Allow_Burden_Flag%TYPE;
2073 l_api_call_reqd VARCHAR2(1) ;
2074
2075 -- Bug 3893837 : Introduced cursor to fetch burden cost for adjusted expenditure
2076 -- items from the original expenditure item which is already costed.
2077
2078 CURSOR Adj_Exp_Item_Cursor (p_exp_item_id NUMBER ,p_denom_raw_cost NUMBER ) IS
2079 SELECT cost_ind_compiled_set_id,
2080 -1 * Burden_Cost ,
2081 -1 * Denom_Burdened_Cost,
2082 -1 * Acct_Burdened_Cost ,
2083 -1 * Project_burdened_Cost,
2084 Burden_Cost_Rate
2085 FROM pa_expenditure_items_all
2086 WHERE expenditure_item_id = p_exp_item_id
2087 AND Denom_Raw_Cost = -1 * p_denom_raw_cost
2088 AND Denom_Burdened_Cost IS NOT NULL
2089 AND Acct_Burdened_Cost IS NOT NULL
2090 AND Burden_Cost IS NOT NULL ;
2091 /*
2092 ** Bug: 5155112
2093 ** BURDEN SEPARATE LINE - AMOUNT NOT EQUAL TO BURDENED_COST
2094 ** Issue was with the check " AND cost_ind_compiled_set_id IS NOT NULL" in CURSOR Adj_Exp_Item_Cursor
2095 ** This failed for trxns not attached to any burden cost base thereby populating the burden cost column with cached
2096 ** values(incorrectly).
2097 ** AND cost_ind_compiled_set_id IS NOT NULL ;
2098 */
2099
2100 l_adjusted_item_flag VARCHAR2(1);
2101
2102 BEGIN
2103
2104 update_count := 0;
2105 FOR Exp_Item_Row IN Exp_Item_Cursor LOOP
2106
2107 compiled_set_id := NULL; -- Bug 3365476 : Nullify the compiled_set_id, so that it is not
2108 -- populated for already burdened expenditures.
2109
2110 --
2111 -- Get the indirect cost and other information
2112 --
2113
2114 /*
2115 * Bug# 855461
2116 * For imported transactions with allow_burden_flag = 'Y'
2117 * take the ratio of transaction burdened cost to tranaction raw cost
2118 * to get the burden multiplier and use it to calculate
2119 * acct burdened and project burdened cost
2120 */
2121 l_api_call_reqd := 'Y';
2122 l_adjusted_item_flag := 'N'; -- Bug 3893837
2123 IF Exp_Item_Row.transaction_source IS NOT NULL THEN
2124 SELECT Allow_Burden_Flag
2125 INTO l_allow_burden_flag
2126 FROM PA_TRANSACTION_SOURCES
2127 WHERE Transaction_Source = Exp_Item_Row.transaction_source;
2128 /* Bug 902578: Fist we calculate the burdened cost using the ratio calculation.
2129 Then we derive the burden cost by subtracting raw cost from burdened cost.
2130 The burden cost (indirect cost) will later be used to calculate total burden cost
2131 which is equivalent to our burdened cost
2132 Example
2133 Raw Cost Burdened Cost Indirect Cost
2134 20 30 10
2135 Acct Raw Cost
2136 60
2137 Burdened_cost_acct = 60 * (30/20) = 90
2138 Indirect_cost_acct = 90 - 60 = 30
2139 */
2140
2141 IF l_allow_burden_flag = 'Y' THEN
2142 /* Bug 4375749 - Divide-by-zero error when Denom_Raw_Cost = 0 */
2143 IF Exp_item_Row.Denom_Raw_Cost <> 0 THEN
2144 burdened_cost := (Exp_Item_Row.Denom_Burdened_Cost/Exp_item_Row.Denom_Raw_Cost)
2145 * Exp_Item_Row.Raw_Cost;
2146 indirect_cost := burdened_cost - Exp_item_Row.Raw_Cost;
2147
2148 burdened_cost_acct := (Exp_Item_Row.Denom_Burdened_Cost/Exp_item_Row.Denom_Raw_Cost)
2149 * Exp_Item_Row.Acct_Raw_Cost;
2150 indirect_cost_acct := burdened_cost_acct - Exp_item_Row.Acct_Raw_Cost;
2151
2152 /*
2153 * Bug 4063390
2154 * burdened_cost_denom := Exp_Item_Row.Denom_Burdened_Cost -
2155 * Exp_Item_Row.Denom_Raw_Cost;
2156 */
2157 burdened_cost_denom := Exp_Item_Row.Denom_Burdened_Cost;
2158 indirect_cost_denom := burdened_cost_denom - Exp_Item_Row.Denom_Raw_Cost;
2159
2160 burdened_cost_project := (Exp_Item_Row.Denom_Burdened_Cost/Exp_item_Row.Denom_Raw_Cost)
2161 * Exp_Item_Row.Project_Raw_Cost;
2162 indirect_cost_project := burdened_cost_project - Exp_item_Row.Project_Raw_Cost;
2163
2164 ELSE
2165 burdened_cost := 0;
2166 indirect_cost := 0;
2167 burdened_cost_acct := 0;
2168 indirect_cost_acct := 0;
2169 burdened_cost_denom:= 0;
2170 indirect_cost_denom:= 0;
2171 burdened_cost_project:= 0;
2172 indirect_cost_project:= 0;
2173 END IF;
2174 /* Bug 4375749 */
2175 status := 0;
2176 l_api_call_reqd := 'N';
2177 END IF;
2178 END IF;
2179 /*
2180 Multi-Currency Related Changes:
2181 Additional Parameters passed (indirect_cost_acct, indirect_cost_denom)
2182 */
2183 IF l_api_call_reqd = 'Y' THEN
2184
2185 -- Bug 3893837 : Calculate the burden cost for adjusted expenditure items
2186 -- by taking the original expenditure items cost.If the original item has NULL
2187 -- burden costs/ compiled set id then both the costs of the orginal
2188 -- and adjusting items will be processed in this program as if they were
2189 -- 'normal' (non-adjusted) expenditure items ,and both will have same compiled set id as
2190 -- they are derived at same time.
2191
2192 IF Exp_Item_Row.net_zero_adjustment_flag = 'Y' AND Exp_Item_Row.adjusted_expenditure_item_id IS NOT NULL THEN
2193
2194 OPEN Adj_Exp_Item_Cursor(Exp_Item_Row.adjusted_expenditure_item_id,Exp_Item_Row.Denom_Raw_Cost);
2195 FETCH Adj_Exp_Item_Cursor INTO compiled_set_id,total_burden_cost,total_burden_cost_denom,
2196 total_burden_cost_Acct,total_burden_cost_Project,x_Burden_Cost_Rate;
2197 CLOSE Adj_Exp_Item_Cursor;
2198
2199 l_adjusted_item_flag := 'Y';
2200 status := 0;
2201
2202 ELSE
2203
2204 pa_cost_plus.get_exp_item_indirect_cost(
2205 Exp_Item_Row.Expenditure_Item_ID,
2206 'C',
2207 indirect_cost,
2208 indirect_cost_acct,
2209 indirect_cost_denom,
2210 indirect_cost_project, /* ProjCurr Changes */
2211 rate_sch_rev_id,
2212 compiled_set_id,
2213 status,
2214 stage);
2215 END IF;
2216 END IF;
2217
2218 IF (status = 0) THEN
2219 --
2220 -- The indirect cost is retrieved successfully.
2221 -- Update the expenditure item.
2222 --
2223 qty := Exp_Item_Row.Quantity;
2224 --
2225 -- If Burden amount is going to be displayed on same transaction
2226 -- then do the calculation else burden_cost=raw_cost
2227 --
2228 /*
2229 Multi-Currency Related Changes:
2230 Set total_burden_cost_denom and total_burden_cost_acct
2231 */
2232 IF (Exp_Item_Row.System_Linkage_Function <> 'BTC') AND l_adjusted_item_flag ='N' THEN -- Bug
2233 IF (Exp_Item_Row.Burden_Amt_Display_Method = 'S') THEN
2234 total_burden_cost := Exp_Item_Row.Raw_Cost + indirect_cost;
2235 total_burden_cost_denom := Exp_Item_Row.Denom_Raw_Cost + indirect_cost_denom;
2236 total_burden_cost_acct := Exp_Item_Row.Acct_Raw_Cost + indirect_cost_acct;
2237 total_burden_cost_project := Exp_Item_Row.Project_Raw_Cost + indirect_cost_project; /* ProjCurr Changes */
2238 IF (qty <> 0) THEN
2239 x_Burden_Cost_Rate := (total_burden_cost_denom /nvl(qty,1));
2240 ELSE
2241 x_Burden_Cost_Rate := total_burden_cost_denom;
2242 END IF;
2243 ELSE -- Burden amount to be displayed as separate EI
2244 total_burden_cost := Exp_Item_Row.Raw_Cost;
2245 total_burden_cost_denom := Exp_Item_Row.Denom_Raw_Cost;
2246 total_burden_cost_acct := Exp_Item_Row.Acct_Raw_Cost;
2247 total_burden_cost_project := Exp_Item_Row.Project_Raw_Cost; /* ProjCurr Changes */
2248 x_Burden_Cost_Rate := Exp_Item_Row.Raw_Cost_Rate;
2249 END IF;-- end if of Burden Amount_display_method
2250 END IF; -- end if of Expenditure_type BTC
2251
2252 /*
2253 Multi-Currency Related Changes:
2254 Update Denom_burdened_cost and Acct_burdened_cost
2255 */
2256 /*
2257 Burdening related changes:
2258 Set the value of that bucket which is null.
2259 */
2260
2261 -- Bug 3893837 : Moved the logic of deriving rates for net zero items in the starting.
2262 -- Bug fixes 3617506 and 3834184 are obsoleted as its incorrect to
2263 -- copy the same burden costs in case compiled_set_id is same or net zero is yes.
2264 -- Reason : As per main query ,this code gets fired only if any of the burden costs are NULL/
2265 -- compiled set is NULL.So in case burden costs are null ,compiled set is NOT NULL and
2266 -- net zero is Yes then the burden costs should be copied from original item if
2267 -- already costed else rederive .
2268 -- Note : Please test all scenarios mentioned in bug 3893837 if this logic is modified in future.
2269
2270 UPDATE PA_Expenditure_Items
2271 SET Burden_Cost_Rate = x_Burden_Cost_Rate,
2272 Burden_Cost = total_burden_cost,
2273 Denom_burdened_Cost = total_burden_cost_denom,
2274 Acct_burdened_Cost = total_burden_cost_Acct,
2275 Project_burdened_Cost = total_burden_cost_Project, /* ProjCurr Changes */
2276 Cost_Ind_Compiled_Set_Id = compiled_set_id,
2277 Ind_Cost_Dist_Rejection_Code = NULL,
2278 cost_burden_distributed_flag = decode(l_api_call_reqd,'N','Z',cost_burden_distributed_flag) /*2450423*/
2279 WHERE Expenditure_Item_ID = Exp_Item_Row.Expenditure_Item_ID;
2280
2281 update_count := update_count + 1;
2282
2283 ELSE
2284 --
2285 -- Error handling. Explain the rejection reason.
2286 --
2287 IF ((status = 100) and (stage = 400)) THEN
2288 --
2289 -- Can not find cost base. The total burdened cost
2290 -- equals raw cost.
2291 --
2292 /*
2293 Multi-Currency Related Changes:
2294 Update Denom_burdened_cost and Acct_burdened_cost
2295 */
2296 /*
2297 Bug# 805725
2298 Set cost_burden_distributed_flag to some impossible value ('Z')
2299 */
2300 /*
2301 Burdening related changes:
2302 Set the value of that bucket which is null.
2303 */
2304 UPDATE PA_Expenditure_Items
2305 SET Burden_Cost_Rate = Raw_Cost_Rate,
2306 Burden_Cost = NVL(Burden_Cost,Raw_Cost),
2307 Denom_Burdened_Cost = NVL(Denom_Burdened_Cost,Denom_Raw_Cost),
2308 Acct_Burdened_Cost = NVL(Acct_Burdened_Cost,Acct_Raw_Cost),
2309 Project_Burdened_Cost = NVL(Project_Burdened_Cost,Project_Raw_Cost), /* epp */
2310 Cost_Burden_Distributed_Flag = 'Z',
2311 Cost_Ind_Compiled_Set_Id = NULL
2312 WHERE Expenditure_Item_ID = Exp_Item_Row.Expenditure_Item_ID;
2313
2314 update_count := update_count + 1;
2315 ELSE
2316 IF (status = 100) THEN
2317 IF (stage = 200) THEN
2318 reason := 'NO_IND_RATE_SCH_REVISION';
2319 ELSIF (stage = 300) THEN
2320 reason := 'NO_COST_PLUS_STRUCTURE';
2321 ELSIF (stage = 500) THEN
2322 reason := 'NO_ORGANIZATION';
2323 ELSIF (stage = 600) THEN
2324 reason := 'NO_COMPILED_MULTIPLIER';/* Bug 5884742 */
2325 ELSIF (stage = 700) THEN
2326 reason := 'NO_ACTIVE_COMPILED_SET';
2327 ELSE
2328 reason := 'GET_INDIRECT_COST_FAIL';
2329 END IF;
2330 ELSE
2331 reason := 'GET_INDIRECT_COST_FAIL';
2332 END IF;
2333
2334 /*
2335 Multi-Currency Related Changes:
2336 Update Denom_burdened_cost and Acct_burdened_cost
2337 */
2338 /*
2339 Burdening related changes:
2340 Dont reset burdened cost.
2341 */
2342 UPDATE PA_Expenditure_Items
2343 SET Cost_Dist_Rejection_Code = reason
2344 /*****************************
2345 ***** Burden_Cost_Rate = NULL,
2346 ***** Burden_Cost = NULL,
2347 ***** Denom_Burdened_Cost = NULL,
2348 ***** Acct_Burdened_Cost = NULL,
2349 ***** Cost_Ind_Compiled_Set_Id = NULL
2350 ******************************/
2351 WHERE Expenditure_Item_ID = Exp_Item_Row.Expenditure_Item_ID;
2352
2353 END IF;
2354
2355 END IF;
2356
2357 END LOOP;
2358
2359
2360 END populate_indirect_cost;
2361
2362
2363 --
2364 -- PROCEDURE
2365 -- get_indirect_cost_sum
2366 --
2367 -- PURPOSE
2368 -- The objective of this function is to retrieve the sum of
2369 -- indirect cost. The amount of indirect cost is the procudt
2370 -- of direct cost and the indirect cost multiplier. This
2371 -- procedure calculates the indirect cost for each indirect cost
2372 -- code with a specified precision, then sum up the total
2373 -- indirect cost.
2374 --
2375 -- HISTORY
2376 --
2377 -- 16-MAY-94 S Lee Created
2378 --
2379
2380
2381 procedure get_indirect_cost_sum(org_id IN number,
2382 c_base IN varchar2,
2383 rate_sch_rev_id IN number,
2384 direct_cost IN number,
2385 precision IN number,
2386 indirect_cost_sum IN OUT NOCOPY number,
2387 status IN OUT NOCOPY number,
2388 stage IN OUT NOCOPY number)
2389 IS
2390
2391 BEGIN
2392
2393 status := 0;
2394 stage := 100;
2395
2396 /* No longer precision argument is used; PA_CURRENCY.ROUND_CURRENCY_AMT
2397 is going to take care that. */
2398
2399 --
2400 -- Here the occurrence of ROUND_CURRENCY_AMT was not changed to ROUND_TRANS_CURRENCY_AMT
2401 -- because this is used in View Burden Costs for which displays the amount fields in
2402 -- functional currency
2403 --
2404 SELECT SUM(PA_CURRENCY.ROUND_CURRENCY_AMT((direct_cost * icpm.compiled_multiplier)))
2405 into indirect_cost_sum
2406 FROM pa_ind_compiled_sets ics,
2407 pa_compiled_multipliers icpm
2408 WHERE
2409 ics.ind_rate_sch_revision_id = rate_sch_rev_id
2410 AND ics.organization_id = org_id
2411 AND ics.status = 'A'
2412 AND ics.ind_compiled_set_id =
2413 icpm.ind_compiled_set_id
2414 AND ics.cost_base =icpm.cost_base /*Bug# 2933915*/
2415 AND icpm.cost_base = c_base;
2416
2417 if (indirect_cost_sum is null) then
2418 status := NO_DATA_FOUND_ERR;
2419 end if;
2420
2421 EXCEPTION
2422
2423 WHEN OTHERS THEN
2424 status := SQLCODE;
2425
2426 END get_indirect_cost_sum;
2427
2428
2429
2430 -- PROCEDURE
2431 -- get_indirect_cost_sum1
2432 --
2433 -- PURPOSE
2434 -- The objective of this function is to retrieve the sum of
2435 -- indirect costs(separately for indirect cost ,indirect cost acct and direct cost denom).
2436 -- The amount of indirect cost is the product
2437 -- of direct cost and the indirect cost multiplier. This
2438 -- procedure calculates the indirect cost for each indirect cost
2439 -- code rounds it , then sum up the total indirect cost (each component separately ).
2440 --
2441 -- HISTORY
2442 --
2443 -- 22-NOV-01 Seema Created /*Bug# 2110452*/
2444
2445 procedure get_indirect_cost_sum1(org_id IN number,
2446 c_base IN varchar2,
2447 rate_sch_rev_id IN number,
2448 direct_cost IN number,
2449 direct_cost_denom IN number,
2450 direct_cost_acct IN number,
2451 direct_cost_project IN number,
2452 precision IN number,
2453 indirect_cost_sum IN OUT NOCOPY number,
2454 indirect_cost_denom_sum IN OUT NOCOPY number,
2455 indirect_cost_acct_sum IN OUT NOCOPY number,
2456 indirect_cost_project_sum IN OUT NOCOPY number,
2457 l_projfunc_currency_code IN varchar2,
2458 l_project_currency_code IN varchar2,
2459 l_acct_currency_code IN varchar2,
2460 l_denom_currency_code IN varchar2,
2461 status IN OUT NOCOPY number,
2462 stage IN OUT NOCOPY number)
2463 IS
2464
2465 BEGIN
2466
2467 status := 0;
2468 stage := 100;
2469 /* Begin bug 5391496 */
2470 -- SELECT SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((direct_cost * icpm.compiled_multiplier),
2471 -- l_projfunc_currency_code)),
2472 -- SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((direct_cost_denom * icpm.compiled_multiplier),
2473 -- l_denom_currency_code)),
2474 -- SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((direct_cost_acct * icpm.compiled_multiplier),
2475 -- l_acct_currency_code)),
2476 -- SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT((direct_cost_project * icpm.compiled_multiplier),
2477 -- l_project_currency_code))
2478 -- into indirect_cost_sum,
2479 -- indirect_cost_denom_sum,
2480 -- indirect_cost_acct_sum,
2481 -- indirect_cost_project_sum
2482 -- FROM pa_ind_compiled_sets ics,
2483 -- pa_compiled_multipliers icpm
2484 -- WHERE
2485 -- ics.ind_rate_sch_revision_id = rate_sch_rev_id
2486 -- AND ics.organization_id = org_id
2487 -- AND ics.status = 'A'
2488 -- AND ics.ind_compiled_set_id = icpm.ind_compiled_set_id
2489 -- AND ics.cost_base =icpm.cost_base /*Bug# 2933915*/
2490 -- AND icpm.cost_base = c_base;
2491
2492 SELECT SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((direct_cost * icpm.compiled_multiplier),
2493 l_projfunc_currency_code)),
2494 SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((direct_cost_denom * icpm.compiled_multiplier),
2495 l_denom_currency_code)),
2496 SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((direct_cost_acct * icpm.compiled_multiplier),
2497 l_acct_currency_code)),
2498 SUM(PA_CURRENCY.ROUND_TRANS_CURRENCY_AMT1((direct_cost_project * icpm.compiled_multiplier),
2499 l_project_currency_code))
2500 into indirect_cost_sum,
2501 indirect_cost_denom_sum,
2502 indirect_cost_acct_sum,
2503 indirect_cost_project_sum
2504 FROM pa_ind_compiled_sets ics,
2505 pa_compiled_multipliers icpm
2506 WHERE
2507 ics.ind_rate_sch_revision_id = rate_sch_rev_id
2508 AND ics.organization_id = org_id
2509 AND ics.status = 'A'
2510 AND ics.ind_compiled_set_id = icpm.ind_compiled_set_id
2511 AND ics.cost_base =icpm.cost_base /*Bug# 2933915*/
2512 AND icpm.cost_base = c_base;
2513 /* End bug 5391496 */
2514
2515 if (indirect_cost_sum is null) then
2516 status := NO_DATA_FOUND_ERR;
2517 end if;
2518
2519 EXCEPTION
2520
2521 WHEN OTHERS THEN
2522 status := SQLCODE;
2523
2524 END get_indirect_cost_sum1;
2525
2526 --
2527 -- PROCEDURE
2528 -- view_indirect_cost
2529 --
2530 -- PURPOSE
2531 -- The objective of this procedure is to retrieve the total
2532 -- indirect cost based on a set of qualifications. User can
2533 -- specify the qualifications and the type of indirect rate
2534 -- schedule, then get the total amount of indirect cost.
2535 --
2536 -- HISTORY
2537 --
2538 -- 10-JUN-94 S Lee Created
2539 --
2540
2541 procedure view_indirect_cost(transaction_id IN Number,
2542 transaction_type IN Varchar2,
2543 task_id IN Number,
2544 effective_date IN Date,
2545 expenditure_type IN Varchar2,
2546 organization_id IN Number,
2547 schedule_type IN Varchar2,
2548 direct_cost IN Number,
2549 indirect_cost IN OUT NOCOPY Number,
2550 status IN OUT NOCOPY Number,
2551 stage IN OUT NOCOPY Number)
2552
2553 IS
2554
2555 -- Bug Fix for 886868, Burden amount is showing twice for projects that allow
2556 -- burden amount to be shown as a separate expenditure items on same project
2557 --
2558 -- NOTE : This procedure is called from 2 different programs
2559 -- PABCMTB.pls (commitments)
2560 -- PAAPIMPB.pls (Web Expense) integration
2561 -- This aPi is also called from GET_INDIRECT_COST_AMOUNTS procedure
2562 -- for which I couldn't find any references in 11.0 source stream
2563 -- By adding this change, we will be sending the indirect_cost as
2564 -- 0 if the burden amount to be displayed on same project as separate
2565 -- expenditure items
2566 -- Will make appropriate modifications to PA_COST_PLUS1 package to call
2567 -- PA_COST_PLUS1.view_indirect_cost instead of
2568 -- PA_COST_PLUS.view_indirect_cost
2569 --
2570 /*
2571 * Bug#1065740
2572 * parameter added to this cursor to avoid conflict
2573 * between local variable name and the column name in database.
2574 */
2575 CURSOR Burden_Method_Cursor(l_task_id in number) IS
2576 SELECT TYPE.burden_amt_display_method
2577 FROM PA_TASKS TASK,
2578 PA_PROJECTS_ALL PROJ,
2579 PA_PROJECT_TYPES_ALL TYPE
2580 WHERE
2581 TASK.Task_ID = l_task_id
2582 AND TASK.Project_ID = PROJ.Project_ID
2583 AND PROJ.Project_Type = TYPE.Project_Type
2584 /* AND nvl(TYPE.Org_Id, -99) = nvl(PROJ.Org_Id, -99) bug 5374745 */
2585 AND TYPE.Org_Id = PROJ.Org_id -- bug 5374745
2586 AND TYPE.burden_cost_flag = 'Y';
2587
2588 --
2589 -- Local variables
2590 --
2591
2592 rate_sch_rev_id Number(15);
2593 sch_id Number(15);
2594 sch_fixed_date Date;
2595 cp_structure Varchar2(30);
2596 c_base Varchar2(30);
2597
2598 BEGIN
2599
2600 status := 0;
2601
2602 --
2603 -- Get the rate schedule revision id
2604 --
2605 -- Bug Fix for 886868, Burden amount is showing twice for projects that allow
2606 -- burden amount to be shown as a separate expenditure items on same project
2607 --
2608 -- Opening the cursor before calling other APIs to avoid performance
2609 -- problem. If burden summarization method is D (i.e. burden amount on
2610 -- different expenditure items on same project/task, then we will need to
2611 -- set indirect_cost to 0.
2612 -- NOTE : Objective of this API is only to calculate the indirect cost and
2613 -- not to return compiled_set_id and hence we are calling cursor
2614 -- before deriving the other attributes
2615
2616 /* Bug 2989775: Added the condition to check for the value returned by the client
2617 extension function Same_Line_Burden_Cmt If the value returned is TRUE, then
2618 indirect cost should not be set to 0 even if burdening is set up on separate
2619 line at the project type level so that burdening will be on the same line for
2620 commitment transactions when viewed from the PSI screen */
2621
2622 IF PA_CLIENT_EXTN_BURDEN_SUMMARY.Same_Line_Burden_Cmt
2623 then
2624 null;
2625 ELSE
2626 FOR Burden_Method_Row IN Burden_Method_Cursor(task_id) LOOP
2627 -- If Burden amount is NOT going to be displayed on same transaction
2628 -- then set indirect_cost to 0 and return
2629 --
2630 IF (Burden_Method_Row.Burden_Amt_Display_Method <> 'S') THEN
2631 indirect_cost := 0;
2632 stage := 75;
2633 return;
2634 END IF;
2635 END LOOP;
2636 END IF;
2637
2638
2639 pa_cost_plus.find_rate_sch_rev_id(
2640 transaction_id,
2641 transaction_type,
2642 task_id,
2643 schedule_type,
2644 effective_date,
2645 sch_id,
2646 rate_sch_rev_id,
2647 sch_fixed_date,
2648 status,
2649 stage);
2650
2651 stage := 100;
2652
2653 IF (status <> 0) THEN
2654 return;
2655 END IF;
2656
2657 --
2658 -- Get the cost plus structure
2659 --
2660
2661 pa_cost_plus.get_cost_plus_structure(rate_sch_rev_id,
2662 cp_structure,
2663 status,
2664 stage);
2665
2666 IF (status <> 0) THEN
2667 stage := 200;
2668 return;
2669 END IF;
2670
2671
2672 --
2673 -- Get the cost base
2674 --
2675
2676 pa_cost_plus.get_cost_base(expenditure_type,
2677 cp_structure,
2678 c_base,
2679 status,
2680 stage);
2681
2682 /* Bug 925488: If expenditure type is not defined with a cost base,
2683 get_cost_base return with status = 100. This means this expenditure
2684 type should not be burdened. Thus, indirect cost should be 0. */
2685 IF (status <> 0) THEN
2686 IF (status = 100) THEN
2687 indirect_cost := 0;
2688 status := 0;
2689 return;
2690 ELSE
2691 stage := 300;
2692 return;
2693 END IF;
2694 END IF;
2695
2696 --
2697 -- Get the indirect cost
2698 --
2699
2700 pa_cost_plus.get_indirect_cost_sum(organization_id,
2701 c_base,
2702 rate_sch_rev_id,
2703 direct_cost,
2704 2, -- FOR US CURRENCY
2705 indirect_cost,
2706 status,
2707 stage);
2708
2709 IF (status <> 0) THEN
2710 stage := 400;
2711 return;
2712 END IF;
2713
2714
2715 EXCEPTION
2716
2717 WHEN OTHERS THEN
2718 status := SQLCODE;
2719
2720 END view_indirect_cost;
2721
2722
2723 --
2724 -- PROCEDURE
2725 -- get_burden_amount
2726 --
2727 -- PURPOSE
2728 -- The objective of this procedure is to retrieve the
2729 -- burden amount based on a set of qualifications.
2730 --
2731 -- HISTORY
2732 --
2733 -- 05-JAN-95 S Lee Created
2734 --
2735
2736 procedure get_burden_amount(burden_schedule_id IN Number,
2737 effective_date IN Date,
2738 expenditure_type IN Varchar2,
2739 organization_id IN Number,
2740 raw_amount IN Number,
2741 burden_amount IN OUT NOCOPY Number,
2742 burden_sch_rev_id IN OUT NOCOPY Number,
2743 compiled_set_id IN OUT NOCOPY Number,
2744 status IN OUT NOCOPY Number,
2745 stage IN OUT NOCOPY Number)
2746
2747 IS
2748
2749 --
2750 -- Local variables
2751 --
2752
2753 cp_structure Varchar2(30);
2754 c_base Varchar2(30);
2755
2756 BEGIN
2757
2758 status := 0;
2759
2760 --
2761 -- Get the rate schedule revision id
2762 --
2763
2764 pa_cost_plus.get_revision_by_date(burden_schedule_id,
2765 effective_date,
2766 effective_date,
2767 burden_sch_rev_id,
2768 status,
2769 stage);
2770
2771 stage := 100;
2772
2773 IF (status <> 0) THEN
2774 return;
2775 END IF;
2776
2777 --
2778 -- Get the cost plus structure
2779 --
2780
2781 pa_cost_plus.get_cost_plus_structure(burden_sch_rev_id,
2782 cp_structure,
2783 status,
2784 stage);
2785
2786 IF (status <> 0) THEN
2787 stage := 200;
2788 return;
2789 END IF;
2790
2791
2792 --
2793 -- Get the cost base
2794 --
2795
2796 pa_cost_plus.get_cost_base(expenditure_type,
2797 cp_structure,
2798 c_base,
2799 status,
2800 stage);
2801
2802 IF (status <> 0) THEN
2803 stage := 300;
2804 return;
2805 END IF;
2806
2807
2808 --
2809 -- Get the compiled set id
2810 --
2811
2812 pa_cost_plus.get_compiled_set_id(burden_sch_rev_id,
2813 organization_id,
2814 c_base, /*Bug# 2933915*/
2815 compiled_set_id,
2816 status,
2817 stage);
2818
2819 IF (status <> 0) THEN
2820 stage := 400;
2821 return;
2822 END IF;
2823
2824 --
2825 -- Get the indirect cost
2826 --
2827
2828 pa_cost_plus.get_indirect_cost_sum(organization_id,
2829 c_base,
2830 burden_sch_rev_id,
2831 raw_amount,
2832 2, -- FOR US CURRENCY
2833 burden_amount,
2834 status,
2835 stage);
2836
2837 IF (status <> 0) THEN
2838 stage := 500;
2839 return;
2840 END IF;
2841
2842
2843 EXCEPTION
2844
2845 WHEN OTHERS THEN
2846 status := SQLCODE;
2847
2848 END get_burden_amount;
2849
2850 /* added for bug#3117191 */
2851 procedure get_burden_amount1(--burden_schedule_id IN Number,
2852 -- effective_date IN Date,
2853 expenditure_type IN Varchar2,
2854 organization_id IN Number,
2855 raw_amount IN Number,
2856 burden_amount IN OUT NOCOPY Number,
2857 burden_sch_rev_id IN OUT NOCOPY Number,
2858 compiled_set_id IN OUT NOCOPY Number,
2859 status IN OUT NOCOPY Number,
2860 stage IN OUT NOCOPY Number)
2861
2862 IS
2863
2864 --
2865 -- Local variables
2866 --
2867
2868 cp_structure Varchar2(30);
2869 c_base Varchar2(30);
2870
2871 BEGIN
2872
2873 status := 0;
2874
2875 --
2876 -- Get the rate schedule revision id
2877 --
2878
2879 /*
2880 pa_cost_plus.get_revision_by_date(burden_schedule_id,
2881 effective_date,
2882 effective_date,
2883 burden_sch_rev_id,
2884 status,
2885 stage);
2886
2887 stage := 100;
2888
2889 IF (status <> 0) THEN
2890 return;
2891 END IF;
2892 */
2893
2894
2895 --
2896 -- Get the cost plus structure
2897 --
2898
2899 pa_cost_plus.get_cost_plus_structure(burden_sch_rev_id,
2900 cp_structure,
2901 status,
2902 stage);
2903
2904 IF (status <> 0) THEN
2905 stage := 200;
2906 return;
2907 END IF;
2908
2909
2910 --
2911 -- Get the cost base
2912 --
2913
2914 pa_cost_plus.get_cost_base(expenditure_type,
2915 cp_structure,
2916 c_base,
2917 status,
2918 stage);
2919
2920 IF (status <> 0) THEN
2921 stage := 300;
2922 return;
2923 END IF;
2924
2925
2926 --
2927 -- Get the compiled set id
2928 --
2929
2930 pa_cost_plus.get_compiled_set_id(burden_sch_rev_id,
2931 organization_id,
2932 c_base, /*Bug# 2933915*/
2933 compiled_set_id,
2934 status,
2935 stage);
2936
2937 IF (status <> 0) THEN
2938 stage := 400;
2939 return;
2940 END IF;
2941
2942 --
2943 -- Get the indirect cost
2944 --
2945
2946 pa_cost_plus.get_indirect_cost_sum(organization_id,
2947 c_base,
2948 burden_sch_rev_id,
2949 raw_amount,
2950 2, -- FOR US CURRENCY
2951 burden_amount,
2952 status,
2953 stage);
2954
2955 IF (status <> 0) THEN
2956 stage := 500;
2957 return;
2958 END IF;
2959
2960
2961 EXCEPTION
2962
2963 WHEN OTHERS THEN
2964 status := SQLCODE;
2965
2966 END get_burden_amount1;
2967 /* end for bug#3117191 */
2968
2969 --
2970 -- PROCEDURE
2971 -- get_hierarchy_from_revision
2972 --
2973 -- PURPOSE
2974 --
2975 --
2976 -- HISTORY
2977 --
2978 -- 10-APR-2000 C.Yuvaraj Created
2979 --
2980
2981 procedure get_hierarchy_from_revision(p_sch_rev_id IN number,
2982 x_org_struc_ver_id OUT NOCOPY number,
2983 x_start_org OUT NOCOPY number,
2984 x_status OUT NOCOPY number,
2985 x_stage OUT NOCOPY number)
2986 IS
2987 BEGIN
2988
2989 x_status := 0;
2990 x_stage := 100;
2991
2992
2993 select org_structure_version_id,start_organization_id
2994 into x_org_struc_ver_id,x_start_org
2995 from pa_ind_rate_sch_revisions
2996 where ind_rate_sch_revision_id = p_sch_rev_id;
2997
2998
2999 EXCEPTION
3000 when NO_DATA_FOUND then
3001 x_status := NO_DATA_FOUND_ERR;
3002 when OTHERS then
3003 x_status := SQLCODE;
3004
3005 end get_hierarchy_from_revision;
3006
3007
3008
3009 --
3010 -- PROCEDURE
3011 -- find_rate_sch_rev_id
3012 --
3013 -- PURPOSE
3014 -- The objective of this procedure is to retrieve the rate schedule
3015 -- revision id assigned for a task. The sequence to find the
3016 -- rate schedule revision is
3017 -- (1) task level schedule override
3018 -- (2) project level schedule override
3019 -- (3) lowest level task schedule.
3020 --
3021 -- Note: This procedure gets called from both the project and expenditure
3022 -- oriented process. Hence should always refer to the base tables
3023 -- and not the Morg view.
3024 --
3025 --
3026 -- HISTORY
3027 --
3028 -- 10-JUN-94 S Lee Created
3029 --
3030
3031 procedure find_rate_sch_rev_id(
3032 transaction_id IN Number,
3033 transaction_type IN Varchar2,
3034 t_id IN Number,
3035 schedule_type IN Varchar2,
3036 exp_item_date IN Date,
3037 sch_id IN OUT NOCOPY Number,
3038 rate_sch_rev_id IN OUT NOCOPY Number,
3039 sch_fixed_date IN OUT NOCOPY Date,
3040 status IN OUT NOCOPY Number,
3041 stage IN OUT NOCOPY Number)
3042 IS
3043
3044 -- Local variables
3045
3046 t_rate_sch_rev_id number;
3047 t_sch_fixed_date date;
3048
3049 BEGIN
3050
3051 --
3052 -- Find the rate schedule according to the sequence
3053 -- (1) task level schedule override
3054 -- (2) project level schedule override
3055 -- (3) lowest level task schedule
3056 --
3057
3058 status := 0;
3059 stage := 100;
3060 sch_id := NULL;
3061 rate_sch_rev_id := NULL;
3062 sch_fixed_date := NULL;
3063 t_rate_sch_rev_id := NULL;
3064 t_sch_fixed_date := NULL;
3065
3066 --
3067 -- Find the override rate schedule at task level
3068 --
3069
3070 BEGIN
3071
3072 if (schedule_type = 'C') then
3073
3074 SELECT irs.ind_rate_sch_id,
3075 t.cost_ind_sch_fixed_date
3076 INTO sch_id,
3077 sch_fixed_date
3078 FROM pa_tasks t,
3079 pa_ind_rate_schedules irs
3080 WHERE t.task_id = t_id
3081 AND t.task_id = irs.task_id
3082 AND irs.cost_ovr_sch_flag = 'Y';
3083
3084 elsif (schedule_type = 'R') then
3085
3086 SELECT irs.ind_rate_sch_id,
3087 t.rev_ind_sch_fixed_date
3088 INTO sch_id,
3089 sch_fixed_date
3090 FROM pa_tasks t,
3091 pa_ind_rate_schedules irs
3092 WHERE t.task_id = t_id
3093 AND t.task_id = irs.task_id
3094 AND irs.rev_ovr_sch_flag = 'Y';
3095
3096 else
3097
3098 SELECT irs.ind_rate_sch_id,
3099 t.inv_ind_sch_fixed_date
3100 INTO sch_id,
3101 sch_fixed_date
3102 FROM pa_tasks t,
3103 pa_ind_rate_schedules irs
3104 WHERE t.task_id = t_id
3105 AND t.task_id = irs.task_id
3106 AND irs.inv_ovr_sch_flag = 'Y';
3107
3108 end if;
3109
3110 EXCEPTION
3111
3112 when NO_DATA_FOUND then
3113 sch_id := NULL;
3114 sch_fixed_date := NULL;
3115
3116 when OTHERS then
3117 status := SQLCODE;
3118 return;
3119 END;
3120
3121 --
3122 -- Get revision id if schedule is not null
3123 --
3124
3125 IF (sch_id IS NOT NULL) THEN
3126 pa_cost_plus.get_revision_by_date(sch_id,
3127 sch_fixed_date,
3128 exp_item_date,
3129 rate_sch_rev_id,
3130 status,
3131 stage);
3132 END IF;
3133
3134 --
3135 -- Calling client extension to override rate_sch_rev_id
3136 --
3137 PA_CLIENT_EXTN_BURDEN.Override_Rate_Rev_Id(
3138 'ACTUAL',
3139 transaction_id, -- Transaction Item Id
3140 transaction_type, -- Transaction Type
3141 t_id, -- Task Id
3142 schedule_type, -- Schedule Type
3143 exp_item_date, -- EI Date
3144 t_sch_fixed_date, -- Sch_fixed_date (Out)
3145 t_rate_sch_rev_id, -- Rate_sch_rev_id (Out)
3146 status); -- Status (Out)
3147
3148 /* Begin bug 5169080 */
3149 If (nvl(status , 0) <> 0) Then
3150 Return;
3151 End If;
3152 /* End bug 5169080 */
3153
3154 IF (t_rate_sch_rev_id IS NOT NULL) THEN
3155 rate_sch_rev_id := t_rate_sch_rev_id;
3156 IF (t_sch_fixed_date IS NOT NULL) THEN
3157 sch_fixed_date := t_sch_fixed_date;
3158 END IF;
3159 END IF;
3160
3161 /* Start : GMS code hook to override rate_sch_rev_id.
3162 ** 2981752 - PA.L:BURDENING ENHANCEMENTS : TRACKING BUG
3163 */
3164
3165 IF ( NVL(g_gms_enabled,'N') = 'Y' ) THEN
3166
3167 IF (t_rate_sch_rev_id is NULL ) THEN
3168
3169 t_sch_fixed_date := sch_fixed_date ;
3170
3171 GMS_PA_API3.Override_Rate_Rev_Id( transaction_id, -- Transaction Item Id
3172 transaction_type, -- Transaction Type
3173 t_id, -- Task Id
3174 schedule_type, -- Schedule Type
3175 exp_item_date, -- EI Date
3176 t_sch_fixed_date, -- Sch_fixed_date ( IN Out)
3177 t_rate_sch_rev_id, -- Rate_sch_rev_id (Out)
3178 status); -- Status (Out)
3179
3180 /* Start : The status controls to override rate_sch_rev_id
3181 ** Or not. The possible values are :
3182 ** 0 : Override ( award specific and sponsored project. )
3183 ** 1 : Don't override ( Non sponsored project continue with Default PA logic. )
3184 */
3185
3186 IF ( status = 0 ) THEN
3187
3188 rate_sch_rev_id := t_rate_sch_rev_id;
3189 sch_fixed_date := t_sch_fixed_date ;
3190 return ;
3191
3192 END IF ; -- ( status = 0 )
3193
3194 END IF ; -- (t_rate_sch_rev_id is NULL )
3195
3196 END IF ; -- ( NVL(g_gms_enabled,'N') = 'Y' )
3197
3198 /* End : End of GMS code hook to override rate_sch_rev_id .
3199 ** 2981752 - PA.L:BURDENING ENHANCEMENTS : TRACKING BUG
3200 */
3201
3202
3203 IF (rate_sch_rev_id IS NOT NULL) THEN
3204 return;
3205
3206 ELSE
3207 --
3208 -- There is no override rate schedule id found at the task level
3209 -- Find the override rate schedule at project level
3210 --
3211 stage := 200;
3212 status := 0;
3213 sch_id := NULL;
3214 sch_fixed_date := NULL;
3215
3216 BEGIN
3217 if (schedule_type = 'C') then
3218
3219 SELECT irs.ind_rate_sch_id,
3220 p.cost_ind_sch_fixed_date
3221 INTO sch_id,
3222 sch_fixed_date
3223 FROM pa_tasks t,
3224 pa_projects_all p,
3225 pa_ind_rate_schedules irs
3226 WHERE t.task_id = t_id
3227 AND t.project_id = p.project_id
3228 AND t.project_id = irs.project_id
3229 AND irs.cost_ovr_sch_flag = 'Y'
3230 AND irs.task_id is null;
3231
3232 elsif (schedule_type = 'R') then
3233
3234 SELECT irs.ind_rate_sch_id,
3235 p.rev_ind_sch_fixed_date
3236 INTO sch_id,
3237 sch_fixed_date
3238 FROM pa_tasks t,
3239 pa_projects_all p,
3240 pa_ind_rate_schedules irs
3241 WHERE t.task_id = t_id
3242 AND t.project_id = p.project_id
3243 AND t.project_id = irs.project_id
3244 AND irs.rev_ovr_sch_flag = 'Y'
3245 AND irs.task_id is null;
3246
3247 else
3248
3249 SELECT irs.ind_rate_sch_id,
3250 p.inv_ind_sch_fixed_date
3251 INTO sch_id,
3252 sch_fixed_date
3253 FROM pa_tasks t,
3254 pa_projects_all p,
3255 pa_ind_rate_schedules irs
3256 WHERE t.task_id = t_id
3257 AND t.project_id = p.project_id
3258 AND t.project_id = irs.project_id
3259 AND irs.inv_ovr_sch_flag = 'Y'
3260 AND irs.task_id is null;
3261
3262 end if;
3263
3264 EXCEPTION
3265
3266 when NO_DATA_FOUND then
3267 sch_id := NULL;
3268 sch_fixed_date := NULL;
3269
3270 when OTHERS then
3271 status := SQLCODE;
3272 return;
3273 END;
3274
3275 --
3276 -- Get the project override schedule id and fixed date
3277 --
3278
3279 END IF;
3280
3281
3282 if (sch_id IS NOT NULL) then
3283 pa_cost_plus.get_revision_by_date(sch_id,
3284 sch_fixed_date,
3285 exp_item_date,
3286 rate_sch_rev_id,
3287 status,
3288 stage);
3289 end if;
3290
3291
3292 IF (rate_sch_rev_id IS NOT NULL) then
3293 return;
3294
3295 ELSE
3296 --
3297 -- Override rate schedule does not exist at task or project level
3298 --
3299 stage := 300;
3300 status := 0;
3301 sch_id := NULL;
3302 sch_fixed_date := NULL;
3303
3304 IF (schedule_type = 'C') THEN
3305
3306 SELECT cost_ind_rate_sch_id,
3307 cost_ind_sch_fixed_date
3308 INTO sch_id,
3309 sch_fixed_date
3310 FROM pa_tasks
3311 WHERE task_id = t_id;
3312
3313 ELSIF (schedule_type = 'R') THEN
3314
3315 SELECT rev_ind_rate_sch_id,
3316 rev_ind_sch_fixed_date
3317 INTO sch_id,
3318 sch_fixed_date
3319 FROM pa_tasks
3320 WHERE task_id = t_id;
3321
3322 ELSE
3323
3324 SELECT inv_ind_rate_sch_id,
3325 inv_ind_sch_fixed_date
3326 INTO sch_id,
3327 sch_fixed_date
3328 FROM pa_tasks
3329 WHERE task_id = t_id;
3330
3331 END IF;
3332
3333 END IF;
3334
3335 if (sch_id IS NOT NULL) then
3336 pa_cost_plus.get_revision_by_date(sch_id,
3337 sch_fixed_date,
3338 exp_item_date,
3339 rate_sch_rev_id,
3340 status,
3341 stage);
3342 else
3343 status := 100;
3344 END IF;
3345
3346
3347 EXCEPTION
3348 when NO_DATA_FOUND then
3349 status := NO_DATA_FOUND_ERR;
3350 when OTHERS then
3351 status := SQLCODE;
3352
3353 END find_rate_sch_rev_id;
3354
3355
3356 --
3357 -- PROCEDURE
3358 -- get_rate_sch_rev_id
3359 --
3360 -- PURPOSE
3361 -- The objective of this procedure is to retrieve revision ID for a
3362 -- particular type of indirect rate schedule. A rate schedule may
3363 -- have many revisions divided by periods. This procedure uses
3364 -- an effective date locate the correct revision.
3365 --
3366 -- Note: This procedure gets called from both the project and expenditure
3367 -- oriented process. Hence should always refer to the base tables
3368 -- and not the Morg view.
3369 --
3370 -- HISTORY
3371 --
3372 -- 09-JUN-94 S Lee Created
3373 --
3374
3375 procedure get_rate_sch_rev_id(exp_item_id IN Number,
3376 schedule_type IN Varchar2,
3377 rate_sch_rev_id IN OUT NOCOPY Number,
3378 status IN OUT NOCOPY Number,
3379 stage IN OUT NOCOPY Number)
3380 IS
3381
3382 -- Local variables
3383
3384 t_id NUMBER(15);
3385 exp_item_date DATE;
3386 sch_id NUMBER(15);
3387 sch_fixed_date DATE;
3388 effective_date DATE;
3389
3390 BEGIN
3391
3392
3393 status := 0;
3394
3395 --
3396 -- Get the task id and expenditure item date
3397 -- As this procedure can be called from project Oriented or Expenditure
3398 -- oriented process, hence the uderlying select uses base table.
3399 --
3400
3401 SELECT task_id,
3402 expenditure_item_date
3403 INTO t_id,
3404 exp_item_date
3405 FROM pa_expenditure_items_all
3406 WHERE expenditure_item_id = exp_item_id;
3407
3408 --
3409 -- Get the indirect rate schedule
3410 --
3411
3412 pa_cost_plus.find_rate_sch_rev_id(
3413 exp_item_id,
3414 'PA',
3415 t_id,
3416 schedule_type,
3417 exp_item_date,
3418 sch_id,
3419 rate_sch_rev_id,
3420 sch_fixed_date,
3421 status,
3422 stage);
3423
3424 IF (status <> 0) THEN
3425 stage := 100;
3426 return;
3427 END IF;
3428
3429
3430 EXCEPTION
3431 when NO_DATA_FOUND then
3432 status := NO_DATA_FOUND_ERR;
3433 when OTHERS then
3434 status := SQLCODE;
3435
3436 END get_rate_sch_rev_id;
3437
3438
3439
3440 --
3441 -- PROCEDURE
3442 -- get_cost_base
3443 --
3444 -- PURPOSE
3445 -- The objective of this procedure is to retrieve the current
3446 -- cost base information for a particular expenditure item.
3447 -- We may get the indirect cost rates through cost base.
3448 --
3449 -- HISTORY
3450 --
3451 -- 08-JUN-94 S Lee Changed the input parameter
3452 -- 18-APR-94 S Lee Added error handler
3453 -- 29-MAR-94 S Lee Modified for the new database schema and
3454 -- application standard
3455 -- 20-NOV-93 S Lee Created
3456 --
3457
3458 procedure get_cost_base (exp_type IN varchar2,
3459 cp_structure IN varchar2,
3460 c_base IN OUT NOCOPY varchar2,
3461 status IN OUT NOCOPY number,
3462 stage IN OUT NOCOPY number)
3463 IS
3464
3465 BEGIN
3466
3467 status := 0;
3468 stage := 100;
3469
3470 SELECT
3471 cbet.cost_base
3472 INTO c_base
3473 FROM
3474 pa_cost_base_exp_types cbet
3475 WHERE
3476 cbet.cost_plus_structure = cp_structure
3477 AND cbet.expenditure_type = exp_type
3478 AND cbet.cost_base_type = INDIRECT_COST_CODE;
3479
3480 EXCEPTION
3481
3482 WHEN NO_DATA_FOUND THEN
3483 status := NO_DATA_FOUND_ERR;
3484 WHEN OTHERS THEN
3485 status := SQLCODE;
3486
3487 END get_cost_base;
3488
3489
3490 --
3491 -- PROCEDURE
3492 -- get_cost_plus_structure
3493 --
3494 -- PURPOSE
3495 -- The objective of this procedure is to retrieve the cost plus
3496 -- structure used by a rate schedule revision.
3497 --
3498 -- HISTORY
3499 --
3500 -- 08-JUN-94 S Lee Created
3501 --
3502
3503 procedure get_cost_plus_structure (rate_sch_rev_id IN Number,
3504 cp_structure IN OUT NOCOPY varchar2,
3505 status IN OUT NOCOPY number,
3506 stage IN OUT NOCOPY number)
3507 IS
3508
3509 BEGIN
3510
3511 status := 0;
3512 stage := 100;
3513
3514 SELECT
3515 cost_plus_structure
3516 INTO cp_structure
3517 FROM
3518 pa_ind_rate_sch_revisions
3519 WHERE
3520 ind_rate_sch_revision_id = rate_sch_rev_id;
3521
3522 EXCEPTION
3523
3524 WHEN NO_DATA_FOUND THEN
3525 status := NO_DATA_FOUND_ERR;
3526 WHEN OTHERS THEN
3527 status := SQLCODE;
3528
3529 END get_cost_plus_structure;
3530
3531
3532
3533 -- PROCEDURE
3534 -- get_organization_id
3535 --
3536 -- PURPOSE
3537 -- The objective of this procedure is to retrieve the ID of the
3538 -- organization which the expenditure item is charged against.
3539 --
3540 -- Note: This procedure gets called from both the project and expenditure
3541 -- oriented process. Hence should always refer to the base tables
3542 -- and not the Morg view.
3543 --
3544 -- HISTORY
3545 --
3546 -- 08-JUN-94 S Lee Created
3547 --
3548
3549 procedure get_organization_id (exp_item_id IN number,
3550 organization_id IN OUT NOCOPY number,
3551 status IN OUT NOCOPY number,
3552 stage IN OUT NOCOPY number)
3553 IS
3554
3555 BEGIN
3556
3557 status := 0;
3558 stage := 100;
3559
3560 -- As this procedure can be called from project Oriented or Expenditure
3561 -- oriented process, hence the uderlying select uses base table.
3562
3563 SELECT override_to_organization_id
3564 INTO organization_id
3565 FROM pa_expenditure_items_all
3566 WHERE expenditure_item_id = exp_item_id;
3567
3568 IF organization_id IS NULL THEN
3569 --
3570 -- the organization is at expenditures level
3571 --
3572 stage := 200;
3573
3574 SELECT incurred_by_organization_id
3575 INTO organization_id
3576 FROM pa_expenditures_all exp,
3577 pa_expenditure_items_all exp_item
3578 WHERE exp_item.expenditure_item_id = exp_item_id
3579 AND exp_item.expenditure_id = exp.expenditure_id
3580 AND ( (exp_item.org_id is null) or (exp_item.org_id = exp.org_id));
3581
3582 END IF;
3583
3584 EXCEPTION
3585
3586 WHEN NO_DATA_FOUND THEN
3587 status := NO_DATA_FOUND_ERR;
3588 WHEN OTHERS THEN
3589 status := SQLCODE;
3590
3591 END get_organization_id;
3592
3593
3594
3595 -- PROCEDURE
3596 -- get_compiled_set_id
3597 --
3598 -- PURPOSE
3599 -- The objective of this procedure is to retrieve the ID of the
3600 -- compiled set id by rate schedule revision id and organization
3601 -- id.
3602 --
3603 -- HISTORY
3604 --
3605 -- 05-JAN-94 S Lee Created
3606 --
3607
3608 procedure get_compiled_set_id(rate_sch_rev_id IN Number,
3609 org_id IN Number,
3610 c_base IN Varchar2, /*2933915*/
3611 compiled_set_id IN OUT NOCOPY Number,
3612 status IN OUT NOCOPY Number,
3613 stage IN OUT NOCOPY Number)
3614
3615 IS
3616
3617 BEGIN
3618
3619 status := 0;
3620 stage := 100;
3621
3622 SELECT ics.ind_compiled_set_id
3623 INTO compiled_set_id
3624 FROM pa_ind_compiled_sets ics
3625 WHERE ics.ind_rate_sch_revision_id = rate_sch_rev_id
3626 AND ics.organization_id = org_id
3627 AND cost_base =c_base /*2933915*/
3628 AND ics.status = 'A';
3629
3630 EXCEPTION
3631
3632 WHEN NO_DATA_FOUND THEN
3633 status := NO_DATA_FOUND_ERR;
3634 WHEN OTHERS THEN
3635 status := SQLCODE;
3636
3637 END get_compiled_set_id;
3638
3639
3640
3641 -- PROCEDURE
3642 -- get_revision_by_date
3643 --
3644 -- PURPOSE
3645 -- The objective of this procedure is to retrieve the ID of the
3646 -- rate schedule revision according to the provided date
3647 --
3648 -- HISTORY
3649 --
3650 -- 05-JAN-94 S Lee Created
3651 --
3652
3653 procedure get_revision_by_date(sch_id IN Number,
3654 sch_fixed_date IN Date,
3655 exp_item_date IN Date,
3656 rate_sch_rev_id IN OUT NOCOPY Number,
3657 status IN OUT NOCOPY Number,
3658 stage IN OUT NOCOPY Number)
3659
3660
3661 IS
3662
3663 x_ind_rate_schedule_type VARCHAR2(1);
3664 base_date DATE;
3665
3666 BEGIN
3667
3668 status := 0;
3669 rate_sch_rev_id := NULL;
3670
3671 --
3672 -- Retrieve the type of the schedule
3673 --
3674
3675 /* Bug 3786374 : Introduced Caching Logic for ind_rate_schedule_type */
3676
3677 If g_sch_id = sch_id Then
3678
3679 x_ind_rate_schedule_type := g_ind_rate_schedule_type ;
3680
3681 Else
3682
3683 SELECT ind_rate_schedule_type
3684 INTO x_ind_rate_schedule_type
3685 FROM pa_ind_rate_schedules
3686 WHERE ind_rate_sch_id = sch_id;
3687
3688 g_ind_rate_schedule_type := x_ind_rate_schedule_type;
3689 g_sch_id := sch_id;
3690
3691 End If;
3692
3693
3694 IF (x_ind_rate_schedule_type = 'F') THEN
3695 --
3696 -- Firm rate schedule type. Use expenditure item date except if
3697 -- schedule fixed date is defined.
3698
3699 IF (sch_fixed_date IS NOT NULL) THEN
3700 base_date := sch_fixed_date;
3701 ELSE
3702 base_date := exp_item_date;
3703 END IF;
3704
3705 ELSE
3706
3707 --
3708 -- Find out the effective gl period
3709 --
3710 /* Commented out for Bug 1277815 as this check is done in PAXCOIRS.fmb
3711 SELECT PERIOD.end_date
3712 INTO base_date
3713 FROM GL_Period_Statuses PERIOD,
3714 PA_Implementations IMP
3715 WHERE PERIOD.Application_ID = 101
3716 AND PERIOD.Set_Of_Books_ID = IMP.Set_Of_Books_ID
3717 AND PERIOD.ADJUSTMENT_PERIOD_FLAG = 'N'
3718 AND TRUNC(exp_item_date) BETWEEN
3719 TRUNC(PERIOD.start_date) and TRUNC(PERIOD.end_date);*/
3720
3721 base_date := exp_item_date; /* Added for bug 1277815*/
3722
3723 END IF;
3724
3725 BEGIN
3726
3727 --
3728 -- Get the actual revision if there is one
3729 --
3730 SELECT irsr.ind_rate_sch_revision_id
3731 INTO rate_sch_rev_id
3732 FROM pa_ind_rate_sch_revisions irsr
3733 WHERE irsr.ind_rate_sch_id = sch_id
3734 AND TRUNC(base_date) BETWEEN
3735 TRUNC(irsr.start_date_active) AND
3736 TRUNC(NVL(irsr.end_date_active, base_date))
3737 AND irsr.ind_rate_sch_revision_type = 'A';
3738
3739 EXCEPTION
3740 when NO_DATA_FOUND then
3741 --
3742 -- Actual revision is not available
3743 --
3744 SELECT irsr.ind_rate_sch_revision_id
3745 INTO rate_sch_rev_id
3746 FROM pa_ind_rate_sch_revisions irsr
3747 WHERE irsr.ind_rate_sch_id = sch_id
3748 AND TRUNC(base_date) BETWEEN
3749 TRUNC(irsr.start_date_active) AND
3750 TRUNC(NVL(irsr.end_date_active, base_date));
3751 END;
3752
3753 EXCEPTION
3754
3755 WHEN NO_DATA_FOUND THEN
3756 status := NO_DATA_FOUND_ERR;
3757 WHEN OTHERS THEN
3758 status := SQLCODE;
3759
3760 END get_revision_by_date;
3761
3762
3763
3764 --
3765 -- PROCEDURE
3766 -- check_revision_used
3767 --
3768 -- PURPOSE
3769 -- The objective of this procedure is to check whether the
3770 -- rate schedule revision has been used. 'Used' is defined as
3771 -- there are costed expenditure items with this rate schedule
3772 -- revision.
3773 --
3774 -- HISTORY
3775 --
3776 -- 06-JUL-94 S Lee Changed FK of expenditure item
3777 -- 07-MAY-94 S Lee Created
3778 --
3779
3780 procedure check_revision_used(rate_sch_rev_id IN number,
3781 status IN OUT NOCOPY number,
3782 stage IN OUT NOCOPY number)
3783 is
3784 dummy number;
3785 begin
3786
3787 status := 0;
3788
3789 /*
3790 * IC related change:
3791 * this procedure will return 0 in case of TP schedule
3792 * also.
3793 */
3794
3795 --S.O. /*Bug 4527736 */
3796 -- SELECT 1 INTO dummy FROM sys.dual WHERE NOT EXISTS
3797 -- (SELECT 1 FROM pa_ind_compiled_sets ICS, /* Removed Hint NO_INDEX(ITEM PA_EXPENDITURE_ITEMS_N13) */
3798 -- pa_expenditure_items_all ITEM
3799 -- WHERE
3800 -- ICS.ind_rate_sch_revision_id = rate_sch_rev_id
3801 -- AND ( (ICS.ind_compiled_set_id = ITEM.cost_ind_compiled_set_id)
3802 -- OR (ICS.ind_compiled_set_id = ITEM.rev_ind_compiled_set_id)
3803 -- OR (ICS.ind_compiled_set_id = ITEM.tp_ind_compiled_set_id)
3804 -- OR (ICS.ind_compiled_set_id = ITEM.inv_ind_compiled_set_id)));
3805 --E.O. /*Bug 4527736 */
3806 --S.N. /*Bug 4527736 */
3807 SELECT 1 INTO dummy FROM SYS.DUAL
3808 WHERE NOT EXISTS
3809 (SELECT 1
3810 FROM PA_IND_COMPILED_SETS ICS
3811 WHERE ICS.IND_RATE_SCH_REVISION_ID = rate_sch_rev_id
3812 AND EXISTS
3813 (SELECT ITEM.COST_IND_COMPILED_SET_ID
3814 FROM PA_EXPENDITURE_ITEMS_ALL ITEM
3815 WHERE ICS.IND_COMPILED_SET_ID = ITEM.COST_IND_COMPILED_SET_ID
3816 UNION ALL
3817 SELECT ITEM.REV_IND_COMPILED_SET_ID
3818 FROM PA_EXPENDITURE_ITEMS_ALL ITEM
3819 WHERE ICS.IND_COMPILED_SET_ID = ITEM.REV_IND_COMPILED_SET_ID
3820 UNION ALL
3821 SELECT ITEM.TP_IND_COMPILED_SET_ID
3822 FROM PA_EXPENDITURE_ITEMS_ALL ITEM
3823 WHERE ICS.IND_COMPILED_SET_ID = ITEM.TP_IND_COMPILED_SET_ID
3824 UNION ALL
3825 SELECT ITEM.INV_IND_COMPILED_SET_ID
3826 FROM PA_EXPENDITURE_ITEMS_ALL ITEM
3827 WHERE ICS.IND_COMPILED_SET_ID = ITEM.INV_IND_COMPILED_SET_ID
3828 )
3829 );
3830 --E.N. /*Bug 4527736 */
3831
3832 exception
3833 when NO_DATA_FOUND then
3834 status := 100;
3835 when OTHERS then
3836 status := SQLCODE;
3837
3838 end check_revision_used;
3839
3840
3841 --
3842 -- PROCEDURE
3843 -- check_structure_used
3844 --
3845 -- PURPOSE
3846 -- The objective of this procedure is to check whether the
3847 -- cost plus structure has been used. 'Used' is defined as
3848 -- there are costed expenditure items in this cost plus structure.
3849 --
3850 -- HISTORY
3851 --
3852 -- 07-MAY-94 S Lee Created
3853 --
3854
3855 procedure check_structure_used(structure IN varchar2,
3856 status IN OUT NOCOPY number,
3857 stage IN OUT NOCOPY number)
3858 is
3859 -- cursor definition
3860
3861 CURSOR rev_cursor
3862 IS
3863 SELECT ind_rate_sch_revision_id
3864 FROM pa_ind_rate_sch_revisions
3865 WHERE cost_plus_structure = structure;
3866
3867 BEGIN
3868
3869 status := 0;
3870
3871 FOR rev_row IN rev_cursor LOOP
3872
3873 pa_cost_plus.check_revision_used(rev_row.ind_rate_sch_revision_id,
3874 status,
3875 stage);
3876
3877 if (status <> 0) then
3878 stage := 100;
3879 EXIT;
3880 end if;
3881
3882 END LOOP;
3883
3884 EXCEPTION
3885 WHEN OTHERS THEN
3886 stage := 100;
3887
3888 END check_structure_used;
3889
3890
3891 --
3892 -- PROCEDURE
3893 -- copy_structure
3894 --
3895 -- PURPOSE
3896 -- The objective of this procedure is to check whether the
3897 -- cost plus structure has been used. 'Used' is defined as
3898 -- there are costed expenditure items in this cost plus structure.
3899 --
3900 -- HISTORY
3901 --
3902 -- 07-MAY-94 S Lee Created
3903 --
3904
3905 procedure copy_structure(source IN varchar2,
3906 destination IN varchar2,
3907 status IN OUT NOCOPY number,
3908 stage IN OUT NOCOPY number)
3909 is
3910
3911 -- cursor definition
3912 CURSOR icc_cursor
3913 IS
3914 SELECT cost_base,
3915 cost_base_type,
3916 ind_cost_code,
3917 precedence
3918 FROM pa_cost_base_cost_codes
3919 WHERE cost_plus_structure = source;
3920
3921 CURSOR et_cursor
3922 IS
3923 SELECT cost_base,
3924 cost_base_type,
3925 expenditure_type
3926 FROM pa_cost_base_exp_types
3927 WHERE cost_plus_structure = source;
3928
3929 -- Local variables
3930 cbicc_id number;
3931 structure_type varchar2(30);
3932 icc_precedence number;
3933
3934 -- Standard who
3935 x_last_updated_by NUMBER(15);
3936 x_created_by NUMBER(15);
3937 x_last_update_login NUMBER(15);
3938
3939
3940 begin
3941
3942 stage := 100;
3943 status := 0;
3944
3945 --
3946 -- Standard who
3947 --
3948
3949 x_created_by := FND_GLOBAL.USER_ID;
3950 x_last_updated_by := FND_GLOBAL.USER_ID;
3951 x_last_update_login := FND_GLOBAL.LOGIN_ID;
3952
3953 SELECT cost_plus_structure_type
3954 INTO structure_type
3955 FROM pa_cost_plus_structures
3956 WHERE cost_plus_structure = destination;
3957
3958 if (structure_type = 'A') then
3959 icc_precedence := 1 ;
3960 else
3961 icc_precedence := NULL;
3962 end if;
3963
3964
3965 for icc_row in icc_cursor loop
3966 SELECT pa_cost_base_cost_codes_s.nextval into cbicc_id FROM sys.dual;
3967
3968 INSERT INTO pa_cost_base_cost_codes
3969 (cost_base_cost_code_id,
3970 cost_plus_structure,
3971 cost_base,
3972 cost_base_type,
3973 ind_cost_code,
3974 precedence,
3975 last_update_date,
3976 last_updated_by,
3977 creation_date,
3978 created_by,
3979 last_update_login
3980 )
3981 VALUES
3982 (cbicc_id,
3983 destination,
3984 icc_row.cost_base,
3985 icc_row.cost_base_type,
3986 icc_row.ind_cost_code,
3987 NVL(icc_precedence,icc_row.precedence),
3988 SYSDATE,
3989 x_last_updated_by,
3990 SYSDATE,
3991 x_created_by,
3992 x_last_update_login);
3993
3994 end loop;
3995
3996 stage := 200;
3997
3998 for et_row in et_cursor loop
3999
4000 INSERT INTO pa_cost_base_exp_types
4001 (cost_plus_structure,
4002 cost_base,
4003 cost_base_type,
4004 expenditure_type,
4005 last_update_date,
4006 last_updated_by,
4007 creation_date,
4008 created_by,
4009 last_update_login
4010 )
4011 VALUES
4012 (destination,
4013 et_row.cost_base,
4014 et_row.cost_base_type,
4015 et_row.expenditure_type,
4016 SYSDATE,
4017 x_last_updated_by,
4018 SYSDATE,
4019 x_created_by,
4020 x_last_update_login);
4021
4022 end loop;
4023
4024 COMMIT;
4025
4026 exception
4027 WHEN OTHERS THEN
4028 status := SQLCODE;
4029 end copy_structure;
4030
4031
4032 --
4033 -- PROCEDURE
4034 -- mark_impacted_exp_items
4035 --
4036 -- PURPOSE
4037 -- The objective of this procedure is to mark expenditure items for
4038 -- adjustments. For example, if the multipliers of a rate
4039 -- schedule are changed, then the expenditure items that were
4040 -- costed by the original schedule must be identified.
4041 --
4042 -- HISTORY
4043 --
4044 -- 30-JAN-95 S Lee Added adjustment activities
4045 -- 22-AUG-94 S Lee Revised due to suggestions in design review
4046 -- 19-AUG-94 S Lee Modified to handle adjustments
4047 -- 10-JUN-94 S Lee Created
4048 --
4049 /****PA L Enhancement :Modified this procedure to mark the eis selectively on the
4050 basis of impacted cost bases ****************************/
4051
4052 procedure mark_impacted_exp_items(rate_sch_rev_id IN number,
4053 status IN OUT NOCOPY number,
4054 stage IN OUT NOCOPY number)
4055
4056 is
4057 --
4058 -- Local variables
4059 --
4060 sch_id number;
4061 l_start_date date;
4062 l_end_date date;
4063 rev_type varchar2(1);
4064 rev_done number;
4065 l_gms_enabled VARCHAR2(2) :='N'; /*3059344*/
4066 l_cp_structure pa_ind_rate_sch_revisions.cost_plus_structure%TYPE ; /*3054111*/
4067
4068 l_this_fetch number:= 0;
4069 l_this_commit_cycle number:= 0;
4070 l_totally_fetched number:= 0;
4071 l_fetch_size number:= 1000;
4072 l_commit_size number:= 10000;
4073 adj_module constant varchar2(10) := 'PACOCRSR';
4074 err_buf varchar2(4000);--Bug 5726575
4075 ret_code varchar2(250);--Bug 5726575
4076 GMS_INSERT_ENC_ITEM_ERROR exception; --Bug 5726575
4077
4078 l_eiid_tbl pa_plsql_datatypes.IdTabTyp;
4079 l_csid_tbl pa_plsql_datatypes.IdTabTyp;
4080 l_rowid_tbl pa_plsql_datatypes.RowidTabTyp;
4081 l_rev_inv_flag_tbl pa_plsql_datatypes.Char1TabTyp;
4082
4083 /* Commented for the bug 4527736
4084 CURSOR mark_impacted_cost_bases --3054111
4085 IS
4086 SELECT
4087 DISTINCT cbicc.cost_base ,cbicc.cost_plus_structure
4088 FROM pa_cost_base_cost_codes cbicc,
4089 pa_ind_cost_multipliers icm
4090 WHERE icm.ind_rate_sch_revision_id = rate_sch_rev_id --3054111
4091 AND (nvl(icm.ready_to_compile_flag,'N') in ('Y','X') OR nvl(G_MODULE,'XXX') ='NEW_ORG')
4092 AND cbicc.cost_plus_structure = l_cp_structure --3054111
4093 AND cbicc.ind_cost_code = icm.ind_cost_code
4094 AND cbicc.cost_base_type = INDIRECT_COST_CODE ;
4095 */
4096
4097 /****2933915 :Modified this cursor
4098 CURSOR cs1_cursor
4099 IS
4100 SELECT DISTINCT ind_compiled_set_id
4101 FROM pa_ind_compiled_sets
4102 WHERE ind_rate_sch_revision_id = rate_sch_rev_id
4103 AND status = 'H';
4104 *************************************************/
4105
4106 CURSOR cs1_cursor(c_base VARCHAR2)
4107 IS
4108 SELECT DISTINCT ICS.ind_compiled_set_id
4109 FROM pa_ind_compiled_sets ICS
4110 WHERE ICS.ind_rate_sch_revision_id = rate_sch_rev_id
4111 AND ICS.cost_base = c_base
4112 AND ICS.status = 'H';
4113
4114 /*2933915 :No change reqd in cs2/cs3 as they should pick up all 'A' records for the earlier revisions irrespective
4115 of cost base */
4116
4117 CURSOR cs2_cursor
4118 IS
4119 SELECT /*+ ORDERED
4120 INDEX(irsr PA_IND_RATE_SCH_REVISIONS_N1) */
4121 DISTINCT ics.ind_compiled_set_id
4122 FROM pa_ind_rate_sch_revisions irsr,
4123 pa_ind_compiled_sets ics
4124 WHERE ics.status = 'A'
4125 AND ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
4126 AND irsr.ind_rate_sch_id = sch_id
4127 AND irsr.start_date_active < l_start_date
4128 AND irsr.ind_rate_sch_revision_type <> 'A';
4129
4130 CURSOR cs3_cursor
4131 IS
4132 SELECT /*+ ORDERED */
4133 DISTINCT ics.ind_compiled_set_id
4134 FROM pa_ind_rate_sch_revisions irsr,
4135 pa_ind_compiled_sets ics
4136 WHERE ics.status = 'A'
4137 AND ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id
4138 AND irsr.actual_sch_revision_id = rate_sch_rev_id;
4139
4140
4141
4142 -- Standard who
4143 x_last_updated_by NUMBER(15);
4144 x_last_update_login NUMBER(15);
4145 x_request_id NUMBER(15);
4146 x_program_application_id NUMBER(15);
4147 x_program_id NUMBER(15);
4148 l_burden_profile VARCHAR2(2); /*2933915*/
4149 l_row_count NUMBER(15);
4150
4151 BEGIN
4152
4153 -- Initialize output parameters
4154 status := 0;
4155 l_row_count := 0;
4156
4157 gl_mc_currency_pkg.G_PA_UPGRADE_MODE := TRUE; /*Bug 4527736 */
4158
4159 --
4160 -- Get the standard who information
4161 --
4162 x_last_updated_by := FND_GLOBAL.USER_ID;
4163 x_last_update_login := FND_GLOBAL.LOGIN_ID;
4164 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
4165 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
4166 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
4167
4168 -- l_burden_profile := nvl(fnd_profile.value('PA_ENHANCED_BURDENING'),'N'); /*2933915 */
4169 l_burden_profile := pa_utils2.IsEnhancedBurdeningEnabled;
4170
4171 IF gms_install.enabled THEN /*3059344 :To check if grants is installed */
4172 l_gms_enabled := 'Y' ;
4173 END IF ;
4174
4175 --
4176 -- Case 1: The indirect rate scheudle is modified.
4177 -- Mark expenditure items whose compiled_set_id is associated with
4178 -- the rate schedule revision.
4179 --
4180 BEGIN
4181 SELECT ind_rate_sch_id, start_date_active, end_date_active,
4182 ind_rate_sch_revision_type,cost_plus_structure
4183 INTO sch_id, l_start_date, l_end_date, rev_type,l_cp_structure
4184 FROM pa_ind_rate_sch_revisions irsr
4185 WHERE ind_rate_sch_revision_id = rate_sch_rev_id;
4186 EXCEPTION
4187 WHEN OTHERS THEN
4188 gl_mc_currency_pkg.G_PA_UPGRADE_MODE := FALSE; /*Bug 4456789 */
4189 RAISE;
4190 END;
4191
4192 BEGIN
4193
4194
4195 --FOR cost_base_rec in mark_impacted_cost_bases
4196 IF G_IMPACTED_COST_BASES_TAB.COUNT <> 0 THEN /*4590268*/
4197
4198 FOR i IN G_IMPACTED_COST_BASES_TAB.FIRST .. G_IMPACTED_COST_BASES_TAB.LAST
4199 LOOP
4200
4201 --FOR cs1_row IN cs1_cursor(cost_base_rec.cost_base)
4202 FOR cs1_row IN cs1_cursor(G_IMPACTED_COST_BASES_TAB(i))
4203 LOOP
4204
4205 /*======================================================================================+
4206 | This update handles the following cases. |
4207 | o [Cost/TP] Same and Separate line burdening transactions - when enhanced burdening |
4208 | profile option is not enabled. |
4209 | o [Cost/TP] Same line burdening transactions when enahanced burdening profile option |
4210 | is enabled. |
4211 | o [Revenue] Capital Projects with revenue based on burdened cost - for same line |
4212 | burdening transactions. |
4213 +======================================================================================*/
4214
4215 G_EXPENDITURE_ITEM_ID_TAB.DELETE; /*4456789*/
4216 G_ADJ_TYPE_TAB.DELETE; /*4456789*/
4217
4218 stage := 100;
4219 UPDATE pa_expenditure_items_all ITEM
4220 SET ITEM.cost_distributed_flag =
4221 DECODE(ITEM.cost_distributed_flag, 'Y',
4222 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4223 'N',ITEM.cost_distributed_flag), ITEM.cost_distributed_flag),
4224 ITEM.revenue_distributed_flag =
4225 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4226 decode(pa_utils2.get_capital_cost_type_code(ITEM.project_id),'B', 'N',ITEM.revenue_distributed_flag)
4227 ,ITEM.revenue_distributed_flag),
4228 ITEM.adjustment_type =
4229 DECODE(ITEM.cost_distributed_flag, 'Y',
4230 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4231 'BURDEN_RECOMPILE',ITEM.adjustment_type),ITEM.adjustment_type),
4232 ITEM.cost_burden_distributed_flag =
4233 DECODE(ITEM.cost_distributed_flag, 'Y',
4234 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4235 'N',ITEM.cost_burden_distributed_flag),ITEM.cost_burden_distributed_flag),
4236 ITEM.last_update_date = SYSDATE,
4237 ITEM.last_updated_by = x_last_updated_by,
4238 ITEM.last_update_login = x_last_update_login,
4239 ITEM.request_id = x_request_id,
4240 ITEM.program_application_id = x_program_application_id,
4241 ITEM.program_id = x_program_id,
4242 ITEM.program_update_date = SYSDATE,
4243 ITEM.project_burdened_cost =
4244 DECODE(ITEM.cost_distributed_flag, 'Y',
4245 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4246 NULL, ITEM.project_burdened_cost), ITEM.project_burdened_cost),
4247 ITEM.denom_burdened_cost =
4248 DECODE(ITEM.cost_distributed_flag, 'Y',
4249 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4250 NULL, ITEM.denom_burdened_cost), ITEM.denom_burdened_cost),
4251 ITEM.acct_burdened_cost =
4252 DECODE(ITEM.cost_distributed_flag, 'Y',
4253 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4254 NULL, ITEM.acct_burdened_cost), ITEM.acct_burdened_cost),
4255 ITEM.burden_cost =
4256 DECODE(ITEM.cost_distributed_flag, 'Y',
4257 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4258 NULL, ITEM.burden_cost), ITEM.burden_cost),
4259 ITEM.cc_bl_distributed_code =
4260 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4261 decode(ITEM.cc_cross_charge_code,'B',
4262 'N',
4263 ITEM.cc_bl_distributed_code),
4264 ITEM.cc_bl_distributed_code),
4265 ITEM.cc_ic_processed_code =
4266 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4267 decode(ITEM.cc_cross_charge_code,'I',
4268 'N',
4269 ITEM.cc_ic_processed_code),
4270 ITEM.cc_ic_processed_code),
4271 ITEM.Denom_Tp_Currency_Code =
4272 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4273 NULL, ITEM.denom_tp_currency_code),
4274 ITEM.Denom_Transfer_Price =
4275 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4276 NULL, ITEM.denom_transfer_price),
4277 ITEM.Acct_Tp_Rate_Type =
4278 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4279 NULL, ITEM.acct_tp_rate_type),
4280 ITEM.Acct_Tp_Rate_Date =
4281 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4282 NULL, ITEM.acct_tp_rate_date),
4283 ITEM.Acct_Tp_Exchange_Rate =
4284 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4285 NULL, ITEM.acct_tp_exchange_rate),
4286 ITEM.Acct_Transfer_Price =
4287 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4288 NULL, ITEM.acct_transfer_price),
4289 ITEM.Projacct_Transfer_Price =
4290 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4291 NULL, ITEM.projacct_transfer_price),
4292 ITEM.Cc_Markup_Base_Code =
4293 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4294 NULL, ITEM.cc_markup_base_code),
4295 ITEM.Tp_Base_Amount =
4296 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4297 NULL, ITEM.tp_base_amount),
4298 ITEM.Tp_Bill_Rate =
4299 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4300 NULL, ITEM.tp_bill_rate),
4301 ITEM.Tp_Bill_Markup_Percentage =
4302 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4303 NULL, ITEM.tp_bill_markup_percentage),
4304 ITEM.Tp_Schedule_line_Percentage =
4305 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4306 NULL, ITEM.tp_schedule_line_percentage),
4307 ITEM.Tp_Rule_percentage =
4308 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4309 NULL, ITEM.tp_rule_percentage)
4310 WHERE ((ITEM.cost_ind_compiled_set_id = cs1_row.ind_compiled_set_id AND ITEM.cost_distributed_flag = 'Y')
4311 OR ITEM.tp_ind_compiled_set_id = cs1_row.ind_compiled_set_id)
4312 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
4313 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
4314 AND decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ITEM.expenditure_item_id,ITEM.task_id),'N') = 'N'
4315 AND exists (select /*+ NO_UNNEST */ null
4316 from pa_cost_base_exp_types cbet
4317 --where cbet.cost_base = cost_base_rec.cost_base
4318 where cbet.cost_base = G_IMPACTED_COST_BASES_TAB(i)
4319 AND cbet.cost_plus_structure = G_CP_STRUCTURE
4320 AND cbet.cost_base_type = INDIRECT_COST_CODE
4321 AND cbet.expenditure_type = ITEM.expenditure_type
4322 )
4323 /*3055700 :Added this exist clause for bug# 3016281 :to mark selectively if explicit multipliers
4324 are defined for an org for all the cost codes */
4325 /* 4527736
4326 AND exists (SELECT NULL
4327 FROM pa_expenditures_all exp
4328 ,pa_ind_compiled_sets ics
4329 WHERE exp.expenditure_id = ITEM.expenditure_id
4330 AND ((ics.ind_compiled_set_id = ITEM.cost_ind_compiled_set_id)
4331 or (ics.ind_compiled_set_id = ITEM.tp_ind_compiled_set_id))
4332 AND nvl(ITEM.override_to_organization_id,exp.incurred_by_organization_id) =ics.organization_id
4333 AND ics.status = 'H'
4334 * AND pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ) =0 -- Bug# 3134445
4335 AND decode(rate_sch_rev_id,g_rate_sch_rev_id,decode(ics.organization_id,g_org_id,g_org_override,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id))
4336 ,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ))=0 -- Bug# 3134445 and Bug 3938479
4337 ) */
4338 AND EXISTS ((SELECT NULL
4339 FROM PA_EXPENDITURES_ALL EXP
4340 ,PA_IND_COMPILED_SETS ICS
4341 WHERE EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID
4342 AND (ICS.IND_COMPILED_SET_ID = ITEM.COST_IND_COMPILED_SET_ID)
4343 AND NVL(ITEM.OVERRIDE_TO_ORGANIZATION_ID, EXP.INCURRED_BY_ORGANIZATION_ID) =ICS.ORGANIZATION_ID
4344 AND ICS.IND_RATE_SCH_REVISION_ID = rate_sch_rev_id /* Added for Bug 5683523 */
4345 AND ICS.STATUS = 'H'
4346 AND DECODE(rate_sch_rev_id ,g_rate_sch_rev_id ,DECODE(ICS.ORGANIZATION_ID,g_org_id ,g_org_override
4347 , PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(rate_sch_rev_id ,ICS.ORGANIZATION_ID))
4348 , PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(rate_sch_rev_id ,ICS.ORGANIZATION_ID ))=0
4349 )
4350 UNION ALL (SELECT NULL
4351 FROM PA_EXPENDITURES_ALL EXP
4352 ,PA_IND_COMPILED_SETS ICS
4353 WHERE EXP.EXPENDITURE_ID = ITEM.EXPENDITURE_ID
4354 AND (ICS.IND_COMPILED_SET_ID = ITEM.TP_IND_COMPILED_SET_ID)
4355 AND NVL(ITEM.OVERRIDE_TO_ORGANIZATION_ID, EXP.INCURRED_BY_ORGANIZATION_ID) =ICS.ORGANIZATION_ID
4356 AND ICS.IND_RATE_SCH_REVISION_ID = rate_sch_rev_id /* Added for Bug 5683523 */
4357 AND ICS.STATUS = 'H'
4358 AND DECODE(rate_sch_rev_id ,g_rate_sch_rev_id ,DECODE(ICS.ORGANIZATION_ID,g_org_id ,g_org_override
4359 , PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(rate_sch_rev_id ,ICS.ORGANIZATION_ID))
4360 , PA_COST_PLUS.CHECK_FOR_EXPLICIT_MULTIPLIER(rate_sch_rev_id ,ICS.ORGANIZATION_ID ))=0
4361 )
4362 )
4363 AND ((pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s','D','d') AND l_burden_profile ='N')
4364 OR (pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('S','s') AND l_burden_profile ='Y'))
4365 RETURNING expenditure_item_id, decode(cs1_row.ind_compiled_set_id,ITEM.tp_ind_compiled_set_id,'UPDATE TP SCHEDULE REVISION','UPDATE COST SCHEDULE REVISION')
4366 BULK COLLECT INTO G_EXPENDITURE_ITEM_ID_TAB,G_ADJ_TYPE_TAB;
4367
4368 stage := 290 ;
4369 pa_cost_plus.add_adjustment_activity(G_EXPENDITURE_ITEM_ID_TAB
4370 ,G_ADJ_TYPE_TAB
4371 ,status
4372 ,stage
4373 );
4374 IF (status <> 0) THEN
4375 return;
4376 END IF;
4377
4378 --Bug 5726575
4379 if l_gms_enabled = 'Y' then
4380 gms_pa_api3.mark_impacted_enc_items(errbuf => err_buf,
4381 retcode => ret_code,
4382 p_ind_compiled_set_id => cs1_row.ind_compiled_set_id,
4383 p_g_impacted_cost_bases => G_IMPACTED_COST_BASES_TAB(i),
4384 p_g_cp_structure => G_CP_STRUCTURE,
4385 p_indirect_cost_code => INDIRECT_COST_CODE,
4386 p_rate_sch_rev_id => rate_sch_rev_id,
4387 p_g_rate_sch_rev_id => g_rate_sch_rev_id,
4388 p_g_org_id => g_org_id,
4389 p_g_org_override => g_org_override);
4390 if err_buf is not null then
4391 raise GMS_INSERT_ENC_ITEM_ERROR;
4392 end if;
4393 end if;
4394
4395 /*======================================================================================+
4396 | This update handles the following cases. |
4397 | o [Cost/TP] Separate line burdening transactions when enahanced burdening profile |
4398 | option is enabled. |
4399 | o [Revenue] Capital Projects with revenue based on burdened cost - for separate line |
4400 | burdening transactions. |
4401 +======================================================================================*/
4402 IF l_burden_profile ='Y' THEN
4403
4404 /*===============================================================+
4405 | M - All pre-cost distributed transactions with separate line |
4406 | burdening are set for BURDEN_RESUMMARIZE - if |
4407 | Enhanced Burdening is SET. |
4408 | Cost Distributed Flag is left untouched. |
4409 | Earlier, supplier invoice transactions with budgetory |
4410 | control were being routed via the distribution process. |
4411 +===============================================================*/
4412 l_row_count :=0;
4413 stage := 200;
4414
4415 G_EXPENDITURE_ITEM_ID_TAB.DELETE; /*4527736*/
4416 G_ADJ_TYPE_TAB.DELETE; /*4527736*/
4417
4418 UPDATE pa_expenditure_items_all ITEM
4419 SET ITEM.last_update_date = SYSDATE,
4420 ITEM.last_updated_by = x_last_updated_by,
4421 ITEM.last_update_login = x_last_update_login,
4422 ITEM.request_id = x_request_id,
4423 ITEM.program_application_id = x_program_application_id,
4424 ITEM.program_id = x_program_id,
4425 ITEM.program_update_date = SYSDATE,
4426 /*************************
4427 ITEM.cost_distributed_flag = DECODE(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id
4428 , DECODE(ITEM.cost_distributed_flag , 'Y'
4429
4430 , DECODE(ITEM.system_linkage_function, 'VI'
4431 , DECODE(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', 'N'
4432 ,ITEM.cost_distributed_flag)
4433 ,ITEM.cost_distributed_flag)
4434
4435 ,ITEM.cost_distributed_flag)
4436 ,ITEM.cost_distributed_flag),
4437 ******************/
4438 ITEM.revenue_distributed_flag =
4439 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4440 decode(pa_utils2.get_capital_cost_type_code(ITEM.project_id),'B', 'N',ITEM.revenue_distributed_flag)
4441 ,ITEM.revenue_distributed_flag),
4442 ITEM.cc_bl_distributed_code =
4443 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4444 decode(ITEM.cc_cross_charge_code,'B',
4445 'N',
4446 ITEM.cc_bl_distributed_code),
4447 ITEM.cc_bl_distributed_code),
4448 ITEM.cc_ic_processed_code =
4449 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4450 decode(ITEM.cc_cross_charge_code,'I',
4451 'N',
4452 ITEM.cc_ic_processed_code),
4453 ITEM.cc_ic_processed_code),
4454 ITEM.Denom_Tp_Currency_Code =
4455 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4456 NULL, ITEM.denom_tp_currency_code),
4457 ITEM.Denom_Transfer_Price =
4458 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4459 NULL, ITEM.denom_transfer_price),
4460 ITEM.Acct_Tp_Rate_Type =
4461 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4462 NULL, ITEM.acct_tp_rate_type),
4463 ITEM.Acct_Tp_Rate_Date =
4464 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4465 NULL, ITEM.acct_tp_rate_date),
4466 ITEM.Acct_Tp_Exchange_Rate =
4467 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4468 NULL, ITEM.acct_tp_exchange_rate),
4469 ITEM.Acct_Transfer_Price =
4470 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4471 NULL, ITEM.acct_transfer_price),
4472 ITEM.Projacct_Transfer_Price =
4473 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4474 NULL, ITEM.projacct_transfer_price),
4475 ITEM.Cc_Markup_Base_Code =
4476 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4477 NULL, ITEM.cc_markup_base_code),
4478 ITEM.Tp_Base_Amount =
4479 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4480 NULL, ITEM.tp_base_amount),
4481 ITEM.Tp_Bill_Rate =
4482 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4483 NULL, ITEM.tp_bill_rate),
4484 ITEM.Tp_Bill_Markup_Percentage =
4485 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4486 NULL, ITEM.tp_bill_markup_percentage),
4487 ITEM.Tp_Schedule_line_Percentage =
4488 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4489 NULL, ITEM.tp_schedule_line_percentage),
4490 ITEM.Tp_Rule_percentage =
4491 decode(ITEM.tp_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4492 NULL, ITEM.tp_rule_percentage),
4493 ITEM.adjustment_type = DECODE(ITEM.cost_distributed_flag, 'Y', 'BURDEN_RESUMMARIZE', ITEM.adjustment_type)
4494 /********************
4495 ,ITEM.denom_burdened_cost =
4496 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4497 decode(ITEM.cost_distributed_flag ,'Y'
4498 ,decode(ITEM.system_linkage_function, 'VI'
4499 ,decode(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', NULL,
4500 ITEM.denom_burdened_cost), ITEM.denom_burdened_cost), ITEM.denom_burdened_cost),
4501 ITEM.denom_burdened_cost)
4502 ,ITEM.acct_burdened_cost =
4503 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4504 decode(ITEM.cost_distributed_flag ,'Y'
4505 ,decode(ITEM.system_linkage_function, 'VI'
4506 ,decode(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', NULL,
4507 ITEM.acct_burdened_cost), ITEM.acct_burdened_cost), ITEM.acct_burdened_cost),
4508 ITEM.acct_burdened_cost)
4509 ,ITEM.project_burdened_cost =
4510 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4511 decode(ITEM.cost_distributed_flag ,'Y'
4512 ,decode(ITEM.system_linkage_function, 'VI'
4513 ,decode(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', NULL,
4514 ITEM.project_burdened_cost), ITEM.project_burdened_cost), ITEM.project_burdened_cost),
4515 ITEM.project_burdened_cost)
4516 ,ITEM.burden_cost =
4517 decode(ITEM.cost_ind_compiled_set_id, cs1_row.ind_compiled_set_id,
4518 decode(ITEM.cost_distributed_flag ,'Y'
4519 ,decode(ITEM.system_linkage_function, 'VI'
4520 ,decode(Pa_Funds_Control_Utils.Get_Fnd_Reqd_Flag(ITEM.project_id, 'STD'), 'Y', NULL,
4521 ITEM.burden_cost), ITEM.burden_cost), ITEM.burden_cost),
4522 ITEM.burden_cost)
4523 ***************************/
4524 WHERE (ITEM.tp_ind_compiled_set_id = cs1_row.ind_compiled_set_id OR
4525 ITEM.cost_ind_compiled_set_id = cs1_row.ind_compiled_set_id )
4526 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
4527 AND decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ITEM.expenditure_item_id,ITEM.task_id),'N') = 'N'
4528 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y'
4529 AND exists (select /*+ NO_UNNEST */ null
4530 from pa_cost_base_exp_types cbet
4531 -- where cbet.cost_base = cost_base_rec.cost_base -- 4527736
4532 where cbet.cost_base = G_IMPACTED_COST_BASES_TAB(i)
4533 AND cbet.cost_plus_structure = l_cp_structure
4534 AND cbet.cost_base_type = INDIRECT_COST_CODE
4535 AND cbet.expenditure_type = ITEM.expenditure_type
4536 )
4537 /*Bug# 3055700 ::Added this exist clause back for bug# 3016281*/
4538 AND exists (SELECT NULL
4539 FROM pa_expenditures_all exp
4540 ,pa_ind_compiled_sets ics
4541 WHERE exp.expenditure_id = ITEM.expenditure_id
4542 AND ((ics.ind_compiled_set_id = ITEM.cost_ind_compiled_set_id)
4543 or (ics.ind_compiled_set_id = ITEM.tp_ind_compiled_set_id))
4544 AND nvl(ITEM.override_to_organization_id,exp.incurred_by_organization_id) =ics.organization_id
4545 AND ics.status = 'H'
4546 /*AND pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ) =0 /*3134445*/
4547 AND decode(rate_sch_rev_id,g_rate_sch_rev_id,decode(ics.organization_id,g_org_id,g_org_override,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id))
4548 ,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ))=0 /*Bug# 3134445 and Bug 3938479*/
4549 )
4550
4551 /*****************************************************************
4552 AND exists (select 1
4553 from pa_project_types_all pt,
4554 pa_projects_all pp
4555 where pp.project_id =ITEM.project_id
4556 AND pp.project_type =pt.project_type
4557 AND pt.burden_amt_display_method in ('D','d')
4558 AND nvl(pt.org_id,-99) =nvl(pp.org_id,-99) )
4559 *************************************************************/
4560 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('D','d')
4561 RETURNING expenditure_item_id, decode(cs1_row.ind_compiled_set_id,ITEM.tp_ind_compiled_set_id,'UPDATE TP SCHEDULE REVISION','UPDATE COST SCHEDULE REVISION')
4562 BULK COLLECT INTO G_EXPENDITURE_ITEM_ID_TAB,G_ADJ_TYPE_TAB;
4563
4564 stage := 300 ;
4565 /*
4566 pa_cost_plus.add_adjustment_activity(cs1_row.ind_compiled_set_id
4567 --,cost_base_rec.cost_base
4568 ,G_IMPACTED_COST_BASES_TAB(i)
4569 ,l_cp_structure
4570 ,'UPDATE COST SCHEDULE REVISION'
4571 ,NULL
4572 ,NULL
4573 ,'UPDATE TP SCHEDULE REVISION'
4574 ,status
4575 ,stage
4576 ,G_EXPENDITURE_ITEM_ID_TAB
4577 ,G_ADJ_TYPE_TAB);
4578 */
4579 pa_cost_plus.add_adjustment_activity(G_EXPENDITURE_ITEM_ID_TAB
4580 ,G_ADJ_TYPE_TAB
4581 ,status
4582 ,stage
4583 );
4584 IF (status <> 0) THEN
4585 return;
4586 END IF;
4587
4588 END IF; -- profile option
4589
4590 COMMIT;
4591
4592
4593 /*======================================================================================+
4594 | This update handles the following cases. |
4595 | o [Rev/Inv] Same and Separate line burdening transactions - irrespective of profile |
4596 | option. |
4597 +======================================================================================*/
4598
4599 stage := 400;
4600 l_row_count :=0;
4601
4602 G_EXPENDITURE_ITEM_ID_TAB.DELETE; /*4527736*/
4603 G_ADJ_TYPE_TAB.DELETE; /*4527736*/
4604
4605 UPDATE pa_expenditure_items_all ITEM
4606 SET ITEM.revenue_distributed_flag = 'N',
4607 ITEM.last_update_date = SYSDATE,
4608 ITEM.last_updated_by = x_last_updated_by,
4609 ITEM.last_update_login = x_last_update_login,
4610 ITEM.request_id = x_request_id,
4611 ITEM.program_application_id = x_program_application_id,
4612 ITEM.program_id = x_program_id,
4613 ITEM.program_update_date = SYSDATE
4614 WHERE (ITEM.rev_ind_compiled_set_id = cs1_row.ind_compiled_set_id
4615 OR ITEM.inv_ind_compiled_set_id = cs1_row.ind_compiled_set_id)
4616 AND pa_project_stus_utils.Is_Project_Closed(ITEM.project_id) <>'Y'
4617 AND decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ITEM.expenditure_item_id,ITEM.task_id),'N') = 'N'
4618 AND NVL(ITEM.net_zero_adjustment_flag,'N') <> 'Y' /* missing condition added for bug 4574721 */
4619 AND EXISTS (select /*+ NO_UNNEST */ 1
4620 from pa_cost_base_exp_types cbet
4621 --where cbet.cost_base = cost_base_rec.cost_base
4622 where cbet.cost_base = G_IMPACTED_COST_BASES_TAB(i)
4623 and cbet.cost_plus_structure = l_cp_structure
4624 and cbet.cost_base_type = INDIRECT_COST_CODE
4625 and cbet.expenditure_type = ITEM.expenditure_type
4626 )
4627 /*Bug# 3055700 : Added this exist clause back for bug# 3016281*/
4628 AND EXISTS (SELECT /*+ index(ics PA_IND_COMPILED_SETS_N6) */ NULL /*Added index hint for Bug 5683523 */
4629 FROM pa_expenditures_all exp
4630 ,pa_ind_compiled_sets ics
4631 WHERE exp.expenditure_id = ITEM.expenditure_id
4632 AND ((ics.ind_compiled_set_id = ITEM.rev_ind_compiled_set_id)
4633 or (ics.ind_compiled_set_id = ITEM.inv_ind_compiled_set_id))
4634 AND nvl(ITEM.override_to_organization_id,exp.incurred_by_organization_id) =ics.organization_id
4635 AND ics.ind_rate_sch_revision_id = rate_sch_rev_id /* Added for Bug 5683523 */
4636 AND ics.status = 'H'
4637 /*AND pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ) =0 /*Bug# 3134445*/
4638 AND decode(rate_sch_rev_id,g_rate_sch_rev_id,decode(ics.organization_id,g_org_id,g_org_override,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id))
4639 ,pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id,ics.organization_id ))=0 /*Bug# 3134445 and Bug 3938479*/
4640 )
4641 RETURNING expenditure_item_id, decode(cs1_row.ind_compiled_set_id,ITEM.rev_ind_compiled_set_id,'UPDATE REV SCHEDULE REVISION','UPDATE INV SCHEDULE REVISION')
4642 BULK COLLECT INTO G_EXPENDITURE_ITEM_ID_TAB,G_ADJ_TYPE_TAB;
4643
4644 stage := 450 ;
4645 /* pa_cost_plus.add_adjustment_activity(cs1_row.ind_compiled_set_id
4646 --,cost_base_rec.cost_base
4647 ,G_IMPACTED_COST_BASES_TAB(i)
4648 ,l_cp_structure
4649 ,NULL
4650 ,'UPDATE REV SCHEDULE REVISION'
4651 ,'UPDATE INV SCHEDULE REVISION'
4652 ,NULL
4653 ,status
4654 ,stage
4655 ,G_EXPENDITURE_ITEM_ID_TAB
4656 ,G_ADJ_TYPE_TAB);
4657 */
4658
4659 pa_cost_plus.add_adjustment_activity(G_EXPENDITURE_ITEM_ID_TAB
4660 ,G_ADJ_TYPE_TAB
4661 ,status
4662 ,stage
4663 );
4664 IF (status <> 0) THEN
4665 return;
4666 END IF;
4667
4668 -- consider volume of expenditure items having the same compiled set id
4669 COMMIT;
4670
4671 end loop; -- cs1_cursor
4672 end loop; --G_IMPACTED_COST_BASES_TAB
4673 END IF; --G_IMPACTED_COST_BASES_TAB.COUNT <> 0 THEN /*4590268*/
4674
4675 EXCEPTION /*2933915*/
4676 when OTHERS then
4677 status := SQLCODE;
4678 gl_mc_currency_pkg.G_PA_UPGRADE_MODE := FALSE; /*Bug 4456789 */
4679 END;
4680
4681 --
4682 -- Case 2: Change the end date of an existing indirect rate schedule from
4683 -- NULL to a new date.
4684 -- The expenditure items that have the same rate schedule and their
4685 -- incurred date are in the date range of new rate schedule are
4686 -- marked for recosting.
4687 /****
4688 stage := 400;
4689
4690 *** merged with a pervious select
4691 SELECT ind_rate_sch_id, start_date_active, end_date_active,
4692 ind_rate_sch_revision_type
4693 INTO sch_id, l_start_date, l_end_date, rev_type
4694 FROM pa_ind_rate_sch_revisions irsr
4695 WHERE ind_rate_sch_revision_id = rate_sch_rev_id;
4696 ****/
4697
4698
4699 BEGIN
4700 FOR cs2_row IN cs2_cursor LOOP
4701
4702 stage := 500;
4703
4704 pa_cost_plus.mark_prev_rev_exp_items(cs2_row.ind_compiled_set_id,
4705 rev_type,
4706 'NEW REVISION',
4707 l_start_date,
4708 l_end_date,
4709 status,
4710 stage);
4711 if (status <> 0) then
4712 return;
4713 end if;
4714
4715 END LOOP;
4716
4717 EXCEPTION
4718 when NO_DATA_FOUND then
4719 return;
4720 when OTHERS then
4721 status := SQLCODE;
4722 gl_mc_currency_pkg.G_PA_UPGRADE_MODE := FALSE; /*Bug 4456789 */
4723 END;
4724
4725 --
4726 -- Case 3: Apply actual rates
4727 -- mark those expenditure items that use the same rate schedule and
4728 -- their incurred date are in the date range of actual rate schedule
4729
4730 BEGIN
4731 FOR cs3_row IN cs3_cursor LOOP
4732
4733 stage := 600;
4734
4735 pa_cost_plus.mark_prev_rev_exp_items(cs3_row.ind_compiled_set_id,
4736 rev_type,
4737 'APPLY ACTUAL',
4738 l_start_date,
4739 l_end_date,
4740 status,
4741 stage);
4742
4743 if (status <> 0) then
4744 return;
4745 end if;
4746
4747
4748 END LOOP;
4749
4750 EXCEPTION
4751 when NO_DATA_FOUND then
4752 return;
4753 when OTHERS then
4754 status := SQLCODE;
4755 gl_mc_currency_pkg.G_PA_UPGRADE_MODE := FALSE; /*Bug 4456789 */
4756 END;
4757
4758 COMMIT;
4759 gl_mc_currency_pkg.G_PA_UPGRADE_MODE := FALSE; /*Bug 4456789 */
4760
4761 EXCEPTION
4762 when GMS_INSERT_ENC_ITEM_ERROR then --Bug 5726575
4763 stage := 110;
4764 status := ret_code;
4765 WHEN OTHERS THEN
4766 status := SQLCODE;
4767 gl_mc_currency_pkg.G_PA_UPGRADE_MODE := FALSE; /*Bug 4456789 */
4768 END mark_impacted_exp_items;
4769
4770
4771 --
4772 -- PROCEDURE
4773 -- mark_prev_rev_exp_items
4774 --
4775 -- PURPOSE
4776 -- The objective of this procedure is to mark the impacted
4777 -- expenditure items of previous revisions. When the actual
4778 -- rate schedule revision is applied, or an end date is assigned
4779 -- to an open-end revision, then the expenditure items need
4780 -- to be marked for recosting.
4781 --
4782 -- HISTORY
4783 --
4784 -- 23-AUG-94 S Lee Created
4785 --
4786
4787 procedure mark_prev_rev_exp_items(compiled_set_id IN number,
4788 rev_type IN varchar2,
4789 reason IN varchar2,
4790 l_start_date IN date,
4791 l_end_date IN date,
4792 status IN OUT NOCOPY number,
4793 stage IN OUT NOCOPY number)
4794 is
4795 -- Local variable
4796 ei_count number;
4797 adj_module constant varchar2(10) := 'PACOCRSR';
4798 cost_adj_reason varchar2(30);
4799 rev_adj_reason varchar2(30);
4800 inv_adj_reason varchar2(30);
4801 tp_adj_reason varchar2(30);
4802 l_gms_enabled VARCHAR2(2) :='N'; /*3059344*/
4803 err_buf varchar2(4000);--Bug 5726575
4804 ret_code varchar2(250);--Bug 5726575
4805 GMS_INSERT_ENC_ITEM_ERROR exception; --Bug 5726575
4806 INSERT_ADJ_ACTIVITY_ERROR exception;
4807
4808 -- Cursor definition
4809
4810 -- The following cursor definitions were changed to select from
4811 -- PA_EXPENDITURE_ITEMS_ALL instead of PA_EXPENDITURE_ITEMS to support
4812 -- a multi-org implementation where expenditure items may span across
4813 -- operating units.
4814
4815 -- Modifying the statement to exclude closed project items as well as
4816 -- net zero items (Bug # 730849)
4817 -- (09/17/98)
4818 --
4819 -- Added Nvl for net_zero_adjustment_flag (896190)
4820 --
4821 CURSOR ei1_cursor
4822 IS
4823 SELECT expenditure_item_id
4824 FROM pa_expenditure_items_all ei
4825 WHERE cost_ind_compiled_set_id = compiled_set_id
4826 AND EXISTS
4827 (SELECT task_id
4828 FROM pa_tasks task
4829 WHERE task.task_id = ei.task_id
4830 AND task.cost_ind_sch_fixed_date BETWEEN --Bug 5917245 Removed TRUNC
4831 l_start_date AND
4832 NVL(l_end_date, cost_ind_sch_fixed_date))
4833 AND nvl(ei.net_zero_adjustment_flag,'N') <>'Y'
4834 /******** AND ei.task_id NOT IN
4835 (select t.task_id
4836 FROM pa_projects_all p, pa_tasks t
4837 WHERE t.project_id=p.project_id AND
4838 ei.task_id = t.task_id AND
4839 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
4840 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
4841 AND decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N'; /*3059344*/
4842
4843 CURSOR ei2_cursor
4844 IS
4845 SELECT expenditure_item_id
4846 FROM pa_expenditure_items_all ei
4847 WHERE rev_ind_compiled_set_id = compiled_set_id
4848 AND EXISTS
4849 (SELECT task_id
4850 FROM pa_tasks task
4851 WHERE task.task_id = ei.task_id
4852 AND (task.rev_ind_sch_fixed_date BETWEEN --Bug 5917245 Removed TRUNC
4853 l_start_date AND
4854 NVL(l_end_date, rev_ind_sch_fixed_date)))
4855 AND nvl(ei.net_zero_adjustment_flag ,'N')<>'Y'
4856 /********AND ei.task_id NOT IN
4857 (select t.task_id
4858 FROM pa_projects_all p, pa_tasks t
4859 WHERE t.project_id=p.project_id AND
4860 ei.task_id = t.task_id AND
4861 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
4862 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
4863 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
4864
4865 CURSOR ei3_cursor
4866 IS
4867 SELECT expenditure_item_id
4868 FROM pa_expenditure_items_all ei
4869 WHERE inv_ind_compiled_set_id = compiled_set_id
4870 AND EXISTS
4871 (SELECT task_id
4872 FROM pa_tasks task
4873 WHERE task.task_id = ei.task_id
4874 AND (task.inv_ind_sch_fixed_date BETWEEN --Bug 5917245Removed TRUNC
4875 l_start_date AND
4876 NVL(l_end_date, inv_ind_sch_fixed_date)))
4877 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
4878 /***** AND ei.task_id NOT IN
4879 (select t.task_id
4880 FROM pa_projects_all p, pa_tasks t
4881 WHERE t.project_id=p.project_id AND
4882 ei.task_id = t.task_id AND
4883 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
4884 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
4885 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
4886
4887
4888 CURSOR ei4_cursor
4889 IS
4890 SELECT expenditure_item_id
4891 FROM pa_expenditure_items_all ei
4892 WHERE cost_ind_compiled_set_id = compiled_set_id
4893 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
4894 l_start_date AND
4895 NVL(l_end_date, expenditure_item_date)
4896 AND EXISTS
4897 (SELECT task_id
4898 FROM pa_tasks task
4899 WHERE task.task_id = ei.task_id
4900 AND task.cost_ind_sch_fixed_date IS NULL)
4901 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
4902 /****** AND ei.task_id NOT IN
4903 (select t.task_id
4904 FROM pa_projects_all p, pa_tasks t
4905 WHERE t.project_id=p.project_id AND
4906 ei.task_id = t.task_id AND
4907 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
4908 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
4909 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
4910
4911
4912 CURSOR ei5_cursor
4913 IS
4914 SELECT expenditure_item_id
4915 FROM pa_expenditure_items_all ei
4916 WHERE rev_ind_compiled_set_id = compiled_set_id
4917 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
4918 l_start_date AND
4919 NVL(l_end_date, expenditure_item_date)
4920 AND EXISTS
4921 (SELECT task_id
4922 FROM pa_tasks task
4923 WHERE task.task_id = ei.task_id
4924 AND task.rev_ind_sch_fixed_date IS NULL)
4925 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
4926 /****** AND ei.task_id NOT IN
4927 (select t.task_id
4928 FROM pa_projects_all p, pa_tasks t
4929 WHERE t.project_id=p.project_id AND
4930 ei.task_id = t.task_id AND
4931 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug# 2933915*/
4932 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
4933 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
4934
4935
4936 CURSOR ei6_cursor
4937 IS
4938 SELECT expenditure_item_id
4939 FROM pa_expenditure_items_all ei
4940 WHERE inv_ind_compiled_set_id = compiled_set_id
4941 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
4942 l_start_date AND
4943 NVL(l_end_date, expenditure_item_date)
4944 AND EXISTS
4945 (SELECT task_id
4946 FROM pa_tasks task
4947 WHERE task.task_id = ei.task_id
4948 AND task.inv_ind_sch_fixed_date IS NULL)
4949 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
4950 /****** AND ei.task_id NOT IN
4951 (select t.task_id
4952 FROM pa_projects_all p, pa_tasks t
4953 WHERE t.project_id=p.project_id AND
4954 ei.task_id = t.task_id AND
4955 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug2933915*/
4956 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
4957 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
4958
4959
4960 CURSOR ei7_cursor
4961 IS
4962 SELECT expenditure_item_id
4963 FROM pa_expenditure_items_all ei
4964 WHERE cost_ind_compiled_set_id = compiled_set_id
4965 AND expenditure_item_date BETWEEN --Bug 5917245 removed TRUNC
4966 l_start_date AND NVL(l_end_date, expenditure_item_date)
4967 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
4968 /****** AND ei.task_id NOT IN
4969 (select t.task_id
4970 FROM pa_projects_all p, pa_tasks t
4971 WHERE t.project_id=p.project_id AND
4972 ei.task_id = t.task_id AND
4973 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
4974 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
4975 AND (decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
4976
4977 CURSOR ei8_cursor
4978 IS
4979 SELECT expenditure_item_id
4980 FROM pa_expenditure_items_all ei
4981 WHERE rev_ind_compiled_set_id = compiled_set_id
4982 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
4983 l_start_date AND
4984 NVL(l_end_date, expenditure_item_date)
4985 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
4986 /*****AND ei.task_id NOT IN
4987 (select t.task_id
4988 FROM pa_projects_all p, pa_tasks t
4989 WHERE t.project_id=p.project_id AND
4990 ei.task_id = t.task_id AND
4991 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug 2933915*/
4992 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
4993 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
4994
4995 CURSOR ei9_cursor
4996 IS
4997 SELECT expenditure_item_id
4998 FROM pa_expenditure_items_all ei
4999 WHERE inv_ind_compiled_set_id = compiled_set_id
5000 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
5001 l_start_date AND
5002 NVL(l_end_date, expenditure_item_date)
5003 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5004 /***** AND ei.task_id NOT IN
5005 (select t.task_id
5006 FROM pa_projects_all p, pa_tasks t
5007 WHERE t.project_id=p.project_id AND
5008 ei.task_id = t.task_id AND
5009 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug 2933915 */
5010 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
5011 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
5012
5013 CURSOR ei10_cursor
5014 IS
5015 SELECT expenditure_item_id
5016 FROM pa_expenditure_items_all ei
5017 WHERE cost_ind_compiled_set_id = compiled_set_id
5018 AND EXISTS
5019 (SELECT t1.task_id
5020 FROM pa_project_types_all pt,
5021 pa_projects_all p,
5022 pa_tasks t1
5023 WHERE pt.project_type = p.project_type
5024 /* AND nvl(pt.org_id, -99) = nvl(p.org_id, -99) Bug 5374745 */
5025 AND pt.org_id = p.org_id -- bug 5374745
5026 AND p.project_id = t1.project_id
5027 AND t1.cost_ind_sch_fixed_date BETWEEN --Bug 5917245 Removed TRUNC
5028 l_start_date AND
5029 NVL(l_end_date, t1.rev_ind_sch_fixed_date)
5030 AND t1.task_id = ei.task_id
5031 AND pt.project_type_class_code = 'CAPITAL'
5032 AND pt.capital_cost_type_code = 'B')
5033 AND nvl(ei.net_zero_adjustment_flag,'N') <>'Y'
5034 /**** AND ei.task_id NOT IN
5035 (select t.task_id
5036 FROM pa_projects_all p, pa_tasks t
5037 WHERE t.project_id=p.project_id AND
5038 ei.task_id = t.task_id AND
5039 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug 2933915*/
5040 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
5041 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
5042
5043 CURSOR ei11_cursor
5044 IS
5045 SELECT expenditure_item_id
5046 FROM pa_expenditure_items_all ei
5047 WHERE cost_ind_compiled_set_id = compiled_set_id
5048 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
5049 l_start_date AND
5050 NVL(l_end_date, expenditure_item_date)
5051 AND EXISTS
5052 (SELECT t1.task_id
5053 FROM pa_project_types_all pt,
5054 pa_projects_all p,
5055 pa_tasks t1
5056 WHERE pt.project_type = p.project_type
5057 /* AND nvl(pt.org_id, -99) = nvl(p.org_id, -99) bug 5374745 */
5058 AND pt.org_id = p.org_id -- bug 5374745
5059 AND p.project_id = t1.project_id
5060 AND t1.cost_ind_sch_fixed_date is NULL
5061 AND t1.task_id = ei.task_id
5062 AND pt.project_type_class_code = 'CAPITAL'
5063 AND pt.capital_cost_type_code = 'B')
5064 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5065 /***** AND ei.task_id NOT IN
5066 (select t.task_id
5067 FROM pa_projects_all p, pa_tasks t
5068 WHERE t.project_id=p.project_id AND
5069 ei.task_id = t.task_id AND
5070 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug 2933915*/
5071 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
5072 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
5073
5074 CURSOR ei12_cursor
5075 IS
5076 SELECT expenditure_item_id
5077 FROM pa_expenditure_items_all ei
5078 WHERE cost_ind_compiled_set_id = compiled_set_id
5079 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
5080 l_start_date AND
5081 NVL(l_end_date, expenditure_item_date)
5082 AND EXISTS
5083 (SELECT t1.task_id
5084 FROM pa_project_types_all pt,
5085 pa_projects_all p,
5086 pa_tasks t1
5087 WHERE pt.project_type = p.project_type
5088 /* AND nvl(pt.org_id, -99) = nvl(p.org_id, -99) Bug 5374745 */
5089 AND pt.org_id = p.org_id -- bug 5374745
5090 AND p.project_id = t1.project_id
5091 AND t1.task_id = ei.task_id
5092 AND pt.project_type_class_code = 'CAPITAL'
5093 AND pt.capital_cost_type_code = 'B')
5094 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5095 /****** AND ei.task_id NOT IN
5096 (select t.task_id
5097 FROM pa_projects_all p, pa_tasks t
5098 WHERE t.project_id=p.project_id AND
5099 ei.task_id = t.task_id AND
5100 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
5101 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*Added for bug# 2933915*/
5102 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
5103
5104
5105 /*
5106 * IC related change:
5107 * cursors defined for TP schedule related changes.
5108 * ei13 is for FIRM schedule where as ei14 is for PROVISIONAL schedule.
5109 * Note: explain plan is fine in RBO, cant test it in CBO because of
5110 * non-availability of volume data.
5111 */
5112 /*Bug# 2164590:Commenting this query and tuned it below*/
5113 /*
5114 CURSOR ei13_cursor
5115 IS
5116 SELECT expenditure_item_id
5117 FROM pa_expenditure_items_all ei,
5118 pa_system_linkages syslink,
5119 pa_tasks task,
5120 pa_projects_all proj
5121 WHERE ei.tp_ind_compiled_set_id = compiled_set_id
5122 AND ei.system_linkage_function = syslink.function
5123 AND task.task_id = ei.task_id
5124 AND (
5125 ( TRUNC(NVL(task.labor_tp_fixed_date, ei.expenditure_item_date)) BETWEEN
5126 TRUNC(l_start_date) AND
5127 TRUNC(NVL(l_end_date, NVL(task.labor_tp_fixed_date,ei.expenditure_item_date)))
5128 AND
5129 syslink.labor_non_labor_flag = 'Y')
5130 OR
5131 ( TRUNC(NVL(task.nl_tp_fixed_date, ei.expenditure_item_date)) BETWEEN
5132 TRUNC(l_start_date) AND
5133 TRUNC(NVL(l_end_date, NVL(task.nl_tp_fixed_date,ei.expenditure_item_date)))
5134 AND
5135 syslink.labor_non_labor_flag = 'N')
5136 )
5137 AND nvl(ei.net_zero_adjustment_flag, 'N') <> 'Y'
5138 AND proj.project_id = task.project_id
5139 AND pa_project_stus_utils.Is_Project_Status_Closed(proj.project_status_code) <> 'Y';*/
5140
5141 /*Bug# 2164590:Changed ei13_cursor for performance*/
5142 CURSOR ei13_cursor
5143 IS
5144 SELECT EXPENDITURE_ITEM_ID
5145 FROM PA_EXPENDITURE_ITEMS_ALL EI
5146 WHERE tp_ind_compiled_set_id = compiled_set_id
5147 AND EXISTS
5148 (SELECT task_id
5149 FROM pa_tasks task, pa_system_linkages syslink /*2933915 : ,pa_projects_all proj :Join with pa_projects is not required here */
5150 WHERE task.task_id = ei.task_id
5151 AND ei.system_linkage_function = syslink.function
5152 /*AND task.project_id = proj.project_id 2933915*/
5153 AND task.project_id = ei.project_id /*2933915*/
5154 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
5155 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ) /*3059344*/
5156 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5157 AND ((NVL(task.labor_tp_fixed_date, ei.expenditure_item_date) BETWEEN --Bug 5917245 Removed TRUNC
5158 l_start_date
5159 AND NVL(l_end_date, NVL(task.labor_tp_fixed_date,ei.expenditure_item_date))
5160 AND syslink.labor_non_labor_flag = 'Y')
5161 OR (NVL(task.nl_tp_fixed_date, ei.expenditure_item_date) BETWEEN --Bug 5917245 Removed TRUNC
5162 l_start_date AND
5163 NVL(l_end_date, NVL(task.nl_tp_fixed_date,ei.expenditure_item_date))
5164 AND syslink.labor_non_labor_flag = 'N')
5165 ));
5166
5167 CURSOR ei14_cursor
5168 IS
5169 SELECT expenditure_item_id
5170 FROM pa_expenditure_items_all ei
5171 /* pa_tasks task -- Commented for Bug#3585192 */
5172 /* pa_projects_all proj :2933915 :Redundant join and hence can be removed */
5173 WHERE tp_ind_compiled_set_id = compiled_set_id
5174 AND ei.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
5175 l_start_date AND
5176 NVL(l_end_date, ei.expenditure_item_date)
5177 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5178 /**** AND ei.task_id = task.task_id
5179 AND proj.project_id = task.project_id
5180 AND pa_project_stus_utils.Is_Project_Status_Closed(proj.project_status_code) <> 'Y' ****2933915*/
5181 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
5182 AND ( decode(l_gms_enabled,'Y',gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id),'N') = 'N' ); /*3059344*/
5183
5184
5185
5186 -- Standard who
5187 x_last_updated_by NUMBER(15);
5188 x_last_update_login NUMBER(15);
5189 x_request_id NUMBER(15);
5190 x_program_application_id NUMBER(15);
5191 x_program_id NUMBER(15);
5192
5193 l_burden_profile VARCHAR2(2); /*2933915*/
5194
5195 BEGIN
5196
5197 --
5198 -- Get the standard who information
5199 --
5200 x_last_updated_by := FND_GLOBAL.USER_ID;
5201 x_last_update_login := FND_GLOBAL.LOGIN_ID;
5202 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
5203 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
5204 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
5205
5206 -- l_burden_profile := nvl(fnd_profile.value('PA_ENHANCED_BURDENING'),'N'); /*2933915*/
5207 l_burden_profile := pa_utils2.IsEnhancedBurdeningEnabled;
5208
5209 IF gms_install.enabled THEN /*3059344 :To check if grants is installed */
5210 l_gms_enabled := 'Y' ;
5211 END IF ;
5212
5213 /*
5214 * IC related change:
5215 * New reason added for TP schedule changes.
5216 */
5217 if (reason = 'APPLY ACTUAL') then
5218
5219 cost_adj_reason := 'APPLY ACTUAL COST SCH REV';
5220 rev_adj_reason := 'APPLY ACTUAL REV SCH REV';
5221 inv_adj_reason := 'APPLY ACTUAL INV SCH REV';
5222 tp_adj_reason := 'APPLY_ACTUAL_TP_SCH_REV';
5223
5224 else
5225
5226 cost_adj_reason := 'NEW COST SCHEDULE REVISION';
5227 rev_adj_reason := 'NEW REV SCHEDULE REVISION';
5228 inv_adj_reason := 'NEW INV SCHEDULE REVISION';
5229 tp_adj_reason := 'NEW_TP_SCHEDULE_REVISION';
5230
5231 end if;
5232
5233 --
5234 -- Mark expenditure items for previous revisions
5235 --
5236
5237 if (rev_type = 'F') then
5238
5239 --
5240 -- Check if schedule fixed date is within the range.
5241 -- Costing with schedule fixed date
5242 --
5243
5244 /*
5245 Burdening related changes
5246 Reset burdened_costs to null so that costing program recalculates them.
5247 */
5248 -- Modifying the statement to exclude closed project items as well as
5249 -- net zero items (Bug # 730849)
5250 -- (09/17/98)
5251 --
5252 -- Added Nvl for net_zero_adjustment_flag (896190)
5253 -- ---------------------------------------------------------------------
5254 /* Updating Project_Burdened_Cost also for Bug 2736773 */
5255
5256 /***Bug 2933915 : Added the exists clause in this update to indicate that except profile ='Y' and display_method ='D' -for all
5257 cases we will be marking ei for cost reprocessing */
5258 /*If Burdening is on same ei then update adjustment_type as BURDEN_COMPILE else update it as BURDEN_RESUMMARIZE .*/
5259
5260 UPDATE pa_expenditure_items_all ei
5261 SET cost_distributed_flag = 'N',
5262 adjustment_type ='BURDEN_RECOMPILE', /*2933915*/
5263 cost_burden_distributed_flag = 'N',
5264 last_update_date = SYSDATE,
5265 last_updated_by = x_last_updated_by,
5266 last_update_login = x_last_update_login,
5267 request_id = x_request_id,
5268 program_application_id = x_program_application_id,
5269 program_id = x_program_id,
5270 program_update_date = SYSDATE,
5271 denom_burdened_cost = NULL,
5272 project_burdened_cost = NULL,
5273 acct_burdened_cost = NULL,
5274 burden_cost = NULL
5275 WHERE cost_ind_compiled_set_id = compiled_set_id
5276 AND EXISTS
5277 (SELECT task_id
5278 FROM pa_tasks task
5279 WHERE task.task_id = ei.task_id
5280 AND task.cost_ind_sch_fixed_date BETWEEN
5281 l_start_date AND
5282 NVL(l_end_date, cost_ind_sch_fixed_date))
5283 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5284 /****** AND ei.task_id NOT IN
5285 (select t.task_id
5286 FROM pa_projects_all p, pa_tasks t
5287 WHERE t.project_id=p.project_id AND
5288 ei.task_id = t.task_id AND
5289 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for Bug 2933915*/
5290 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <>'Y' /*2933915*/
5291 AND ((pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s','D','d') AND l_burden_profile ='N') /*Added for 2933915*/
5292 OR (pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s') AND l_burden_profile ='Y'))
5293 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
5294
5295
5296 -- consider volume of expenditure items having the same compiled set id
5297 COMMIT;
5298
5299 if l_gms_enabled = 'Y' then --Bug 5693864
5300 gms_pa_api3.mark_prev_rev_enc_items (errbuf => err_buf,
5301 retcode => ret_code,
5302 p_compiled_set_id => compiled_set_id,
5303 p_start_date => l_start_date,
5304 p_end_date => l_end_date,
5305 p_mode => 'T');
5306 if err_buf is not null then
5307 raise GMS_INSERT_ENC_ITEM_ERROR;
5308 end if;
5309 commit;
5310 end if;
5311
5312 ei_count := 0;
5313
5314 FOR ei1_row IN ei1_cursor LOOP
5315
5316 PA_Adjustments.InsAuditRec(ei1_row.expenditure_item_id,
5317 cost_adj_reason,
5318 adj_module,
5319 x_last_updated_by,
5320 x_last_update_login,
5321 status,
5322 x_request_id,
5323 x_program_id,
5324 x_program_application_id,
5325 SYSDATE);
5326
5327 IF (status <> 0) THEN
5328 raise INSERT_ADJ_ACTIVITY_ERROR;
5329 END IF;
5330
5331 ei_count := ei_count + 1;
5332
5333 IF (ei_count >= 500) THEN
5334 COMMIT;
5335 ei_count := 0;
5336 END IF;
5337
5338 END LOOP;
5339
5340 COMMIT;
5341
5342
5343 --
5344 -- Revenue and invoice with schedule fixed date
5345 --
5346
5347 -- Modifying the statement to exclude closed project items as well as
5348 -- net zero items (Bug # 730849)
5349 -- (09/17/98)
5350 --
5351 -- Added Nvl for net_zero_adjustment_flag (896190)
5352 --
5353 UPDATE pa_expenditure_items_all ei
5354 SET revenue_distributed_flag = 'N',
5355 last_update_date = SYSDATE,
5356 last_updated_by = x_last_updated_by,
5357 last_update_login = x_last_update_login,
5358 request_id = x_request_id,
5359 program_application_id = x_program_application_id,
5360 program_id = x_program_id,
5361 program_update_date = SYSDATE
5362 WHERE rev_ind_compiled_set_id = compiled_set_id
5363 AND EXISTS
5364 (SELECT task_id
5365 FROM pa_tasks task
5366 WHERE task.task_id = ei.task_id
5367 AND task.rev_ind_sch_fixed_date BETWEEN --Bug#5917245 Removed TRUNC
5368 l_start_date AND
5369 NVL(l_end_date, rev_ind_sch_fixed_date))
5370 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5371 /****2933915 AND ei.task_id NOT IN
5372 (select t.task_id
5373 FROM pa_projects_all p, pa_tasks t
5374 WHERE t.project_id=p.project_id AND
5375 ei.task_id = t.task_id AND
5376 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commenteed for bug# 2933915*/
5377 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
5378 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
5379
5380
5381 -- consider volume of expenditure items having the same compiled set id
5382 COMMIT;
5383
5384 ei_count := 0;
5385
5386 FOR ei2_row IN ei2_cursor LOOP
5387
5388 PA_Adjustments.InsAuditRec(ei2_row.expenditure_item_id,
5389 rev_adj_reason,
5390 adj_module,
5391 x_last_updated_by,
5392 x_last_update_login,
5393 status,
5394 x_request_id,
5395 x_program_id,
5396 x_program_application_id,
5397 SYSDATE);
5398
5399 IF (status <> 0) THEN
5400 raise INSERT_ADJ_ACTIVITY_ERROR;
5401 END IF;
5402
5403 ei_count := ei_count + 1;
5404
5405 IF (ei_count >= 500) THEN
5406 COMMIT;
5407 ei_count := 0;
5408 END IF;
5409
5410 END LOOP;
5411
5412 COMMIT;
5413
5414 -- Modifying the statement to exclude closed project items as well as
5415 -- net zero items (Bug # 730849)
5416 -- (09/17/98)
5417 --
5418 -- Added Nvl for net_zero_adjustment_flag (896190)
5419 --
5420
5421 UPDATE pa_expenditure_items_all ei
5422 SET revenue_distributed_flag = 'N',
5423 last_update_date = SYSDATE,
5424 last_updated_by = x_last_updated_by,
5425 last_update_login = x_last_update_login,
5426 request_id = x_request_id,
5427 program_application_id = x_program_application_id,
5428 program_id = x_program_id,
5429 program_update_date = SYSDATE
5430 WHERE inv_ind_compiled_set_id = compiled_set_id
5431 AND EXISTS
5432 (SELECT task_id
5433 FROM pa_tasks task
5434 WHERE task.task_id = ei.task_id
5435 AND task.inv_ind_sch_fixed_date BETWEEN --Bug#5917245 Removed TRUNC
5436 l_start_date AND
5437 NVL(l_end_date, inv_ind_sch_fixed_date))
5438 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5439 /***** AND ei.task_id NOT IN
5440 (select t.task_id
5441 FROM pa_projects_all p, pa_tasks t
5442 WHERE t.project_id=p.project_id AND
5443 ei.task_id = t.task_id AND
5444 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
5445 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
5446 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
5447
5448
5449 -- consider volume of expenditure items having the same compiled set id
5450 COMMIT;
5451
5452 ei_count := 0;
5453
5454 FOR ei3_row IN ei3_cursor LOOP
5455
5456 PA_Adjustments.InsAuditRec(ei3_row.expenditure_item_id,
5457 inv_adj_reason,
5458 adj_module,
5459 x_last_updated_by,
5460 x_last_update_login,
5461 status,
5462 x_request_id,
5463 x_program_id,
5464 x_program_application_id,
5465 SYSDATE);
5466
5467 IF (status <> 0) THEN
5468 raise INSERT_ADJ_ACTIVITY_ERROR;
5469 END IF;
5470
5471 ei_count := ei_count + 1;
5472
5473 IF (ei_count >= 500) THEN
5474 COMMIT;
5475 ei_count := 0;
5476 END IF;
5477
5478 END LOOP;
5479
5480 COMMIT;
5481
5482 --
5483 -- Section For Capital Projects
5484 --
5485 -- This section is added as a part of fix for bug 897479.
5486 --
5487 UPDATE pa_expenditure_items_all ei
5488 SET revenue_distributed_flag = 'N',
5489 last_update_date = SYSDATE,
5490 last_updated_by = x_last_updated_by,
5491 last_update_login = x_last_update_login,
5492 request_id = x_request_id,
5493 program_application_id = x_program_application_id,
5494 program_id = x_program_id,
5495 program_update_date = SYSDATE
5496 WHERE cost_ind_compiled_set_id = compiled_set_id
5497 AND EXISTS
5498 (SELECT t1.task_id
5499 FROM pa_project_types_all pt,
5500 pa_projects_all p,
5501 pa_tasks t1
5502 WHERE pt.project_type = p.project_type
5503 AND nvl(pt.org_id, -99) = nvl(p.org_id, -99)
5504 AND p.project_id = t1.project_id
5505 AND TRUNC(t1.cost_ind_sch_fixed_date) BETWEEN
5506 TRUNC(l_start_date) AND
5507 TRUNC(NVL(l_end_date, t1.rev_ind_sch_fixed_date))
5508 AND t1.task_id = ei.task_id
5509 AND pt.project_type_class_code = 'CAPITAL'
5510 AND pt.capital_cost_type_code = 'B')
5511 AND nvl(ei.net_zero_adjustment_flag,'N') <>'Y'
5512 /**** AND ei.task_id NOT IN
5513 (select t.task_id
5514 FROM pa_projects_all p, pa_tasks t
5515 WHERE t.project_id=p.project_id AND
5516 ei.task_id = t.task_id AND
5517 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
5518 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
5519 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
5520
5521 -- consider volume of expenditure items having the same compiled set id
5522 COMMIT;
5523
5524 ei_count := 0;
5525
5526 FOR ei10_row IN ei10_cursor LOOP
5527
5528 PA_Adjustments.InsAuditRec(ei10_row.expenditure_item_id,
5529 cost_adj_reason,
5530 adj_module,
5531 x_last_updated_by,
5532 x_last_update_login,
5533 status,
5534 x_request_id,
5535 x_program_id,
5536 x_program_application_id,
5537 SYSDATE);
5538
5539 IF (status <> 0) THEN
5540 raise INSERT_ADJ_ACTIVITY_ERROR;
5541 END IF;
5542
5543 ei_count := ei_count + 1;
5544
5545 IF (ei_count >= 500) THEN
5546 COMMIT;
5547 ei_count := 0;
5548 END IF;
5549
5550 END LOOP;
5551
5552 COMMIT;
5553
5554 --
5555 -- End Section For Capital Projects
5556 --
5557
5558 --
5559 -- Costing without schedule fixed date. Use expenditure item date.
5560 --
5561
5562 /*
5563 Burdening related changes
5564 Reset burdened_costs to null so that costing program recalculates them.
5565 */
5566 -- Modifying the statement to exclude closed project items as well as
5567 -- net zero items (Bug # 730849)
5568 -- (09/17/98)
5569 --
5570 -- Added Nvl for net_zero_adjustment_flag (896190)
5571 -- ---------------------------------------------------------------------
5572 /* Updating Project_Burdened_Cost also for Bug 2736773 */
5573 UPDATE pa_expenditure_items_all ei
5574 SET cost_distributed_flag = 'N' ,
5575 adjustment_type ='BURDEN_RECOMPILE', /*2933915*/
5576 cost_burden_distributed_flag = 'N',
5577 last_update_date = SYSDATE,
5578 last_updated_by = x_last_updated_by,
5579 last_update_login = x_last_update_login,
5580 request_id = x_request_id,
5581 program_application_id = x_program_application_id,
5582 program_id = x_program_id,
5583 program_update_date = SYSDATE,
5584 denom_burdened_cost = NULL,
5585 project_burdened_cost = NULL,
5586 acct_burdened_cost = NULL,
5587 burden_cost = NULL
5588 WHERE cost_ind_compiled_set_id = compiled_set_id
5589 AND ei.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
5590 l_start_date AND NVL(l_end_date, ei.expenditure_item_date)
5591 AND EXISTS
5592 (SELECT task_id
5593 FROM pa_tasks task
5594 WHERE task.task_id = ei.task_id
5595 AND task.cost_ind_sch_fixed_date IS NULL)
5596 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5597 /***** AND ei.task_id NOT IN
5598 (select t.task_id
5599 FROM pa_projects_all p, pa_tasks t
5600 WHERE t.project_id=p.project_id AND
5601 ei.task_id = t.task_id AND
5602 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug 2933915*/
5603 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
5604 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' )
5605 AND ((pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s','D','d') AND l_burden_profile ='N') /*Added for 2933915*/
5606 OR (pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s') AND l_burden_profile ='Y')) ;
5607
5608 COMMIT;
5609
5610 if l_gms_enabled = 'Y' then --Bug 5726575
5611 gms_pa_api3.mark_prev_rev_enc_items (errbuf => err_buf,
5612 retcode => ret_code,
5613 p_compiled_set_id => compiled_set_id,
5614 p_start_date => l_start_date,
5615 p_end_date => l_end_date,
5616 p_mode => 'N');
5617 if err_buf is not null then
5618 raise GMS_INSERT_ENC_ITEM_ERROR;
5619 end if;
5620 commit;
5621 end if;
5622
5623 ei_count := 0;
5624
5625 FOR ei4_row IN ei4_cursor LOOP
5626
5627 PA_Adjustments.InsAuditRec(ei4_row.expenditure_item_id,
5628 cost_adj_reason,
5629 adj_module,
5630 x_last_updated_by,
5631 x_last_update_login,
5632 status,
5633 x_request_id,
5634 x_program_id,
5635 x_program_application_id,
5636 SYSDATE);
5637
5638 IF (status <> 0) THEN
5639 raise INSERT_ADJ_ACTIVITY_ERROR;
5640 END IF;
5641
5642 ei_count := ei_count + 1;
5643
5644 IF (ei_count >= 500) THEN
5645 COMMIT;
5646 ei_count := 0;
5647 END IF;
5648
5649 END LOOP;
5650
5651 COMMIT;
5652
5653 --
5654 -- Revenue and invoice without schedule fixed date. Use expenditure
5655 -- item date.
5656 --
5657 -- Modifying the statement to exclude closed project items as well as
5658 -- net zero items (Bug # 730849)
5659 -- (09/17/98)
5660 --
5661 -- Added Nvl for net_zero_adjustment_flag (896190)
5662 --
5663
5664 UPDATE pa_expenditure_items_all ei
5665 SET revenue_distributed_flag = 'N',
5666 last_update_date = SYSDATE,
5667 last_updated_by = x_last_updated_by,
5668 last_update_login = x_last_update_login,
5669 request_id = x_request_id,
5670 program_application_id = x_program_application_id,
5671 program_id = x_program_id,
5672 program_update_date = SYSDATE
5673 WHERE rev_ind_compiled_set_id = compiled_set_id
5674 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
5675 l_start_date AND
5676 NVL(l_end_date, expenditure_item_date)
5677 AND EXISTS
5678 (SELECT task_id
5679 FROM pa_tasks task
5680 WHERE task.task_id = ei.task_id
5681 AND task.rev_ind_sch_fixed_date IS NULL)
5682 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5683 /*** AND ei.task_id NOT IN
5684 (select t.task_id
5685 FROM pa_projects_all p, pa_tasks t
5686 WHERE t.project_id=p.project_id AND
5687 ei.task_id = t.task_id AND
5688 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug2933915*/
5689 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
5690 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
5691
5692
5693 -- consider volume of expenditure items having the same compiled set id
5694 COMMIT;
5695
5696 ei_count := 0;
5697
5698 FOR ei5_row IN ei5_cursor LOOP
5699
5700 PA_Adjustments.InsAuditRec(ei5_row.expenditure_item_id,
5701 rev_adj_reason,
5702 adj_module,
5703 x_last_updated_by,
5704 x_last_update_login,
5705 status,
5706 x_request_id,
5707 x_program_id,
5708 x_program_application_id,
5709 SYSDATE);
5710
5711 IF (status <> 0) THEN
5712 raise INSERT_ADJ_ACTIVITY_ERROR;
5713 END IF;
5714
5715 ei_count := ei_count + 1;
5716
5717 IF (ei_count >= 500) THEN
5718 COMMIT;
5719 ei_count := 0;
5720 END IF;
5721
5722 END LOOP;
5723
5724 COMMIT;
5725
5726 -- Modifying the statement to exclude closed project items as well as
5727 -- net zero items (Bug # 730849)
5728 -- (09/17/98)
5729 --
5730 -- Added Nvl for net_zero_adjustment_flag (896190)
5731 --
5732 UPDATE pa_expenditure_items_all ei
5733 SET revenue_distributed_flag = 'N',
5734 last_update_date = SYSDATE,
5735 last_updated_by = x_last_updated_by,
5736 last_update_login = x_last_update_login,
5737 request_id = x_request_id,
5738 program_application_id = x_program_application_id,
5739 program_id = x_program_id,
5740 program_update_date = SYSDATE
5741 WHERE inv_ind_compiled_set_id = compiled_set_id
5742 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
5743 l_start_date AND
5744 NVL(l_end_date, expenditure_item_date)
5745 AND EXISTS
5746 (SELECT task_id
5747 FROM pa_tasks task
5748 WHERE task.task_id = ei.task_id
5749 AND task.inv_ind_sch_fixed_date IS NULL)
5750 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5751 /*** AND ei.task_id NOT IN
5752 (select t.task_id
5753 FROM pa_projects_all p, pa_tasks t
5754 WHERE t.project_id=p.project_id AND
5755 ei.task_id = t.task_id AND
5756 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
5757 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
5758 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
5759
5760
5761 -- consider volume of expenditure items having the same compiled set id
5762 COMMIT;
5763
5764 ei_count := 0;
5765
5766 FOR ei6_row IN ei6_cursor LOOP
5767
5768 PA_Adjustments.InsAuditRec(ei6_row.expenditure_item_id,
5769 inv_adj_reason,
5770 adj_module,
5771 x_last_updated_by,
5772 x_last_update_login,
5773 status,
5774 x_request_id,
5775 x_program_id,
5776 x_program_application_id,
5777 SYSDATE);
5778
5779 IF (status <> 0) THEN
5780 raise INSERT_ADJ_ACTIVITY_ERROR;
5781 END IF;
5782
5783 ei_count := ei_count + 1;
5784
5785 IF (ei_count >= 500) THEN
5786 COMMIT;
5787 ei_count := 0;
5788 END IF;
5789
5790 END LOOP;
5791
5792 COMMIT;
5793
5794 --
5795 -- Section for Capital projects
5796 --
5797 --
5798 -- This section has been added as a part of bug 897479.
5799 --
5800 --
5801 UPDATE pa_expenditure_items_all ei
5802 SET revenue_distributed_flag = 'N',
5803 last_update_date = SYSDATE,
5804 last_updated_by = x_last_updated_by,
5805 last_update_login = x_last_update_login,
5806 request_id = x_request_id,
5807 program_application_id = x_program_application_id,
5808 program_id = x_program_id,
5809 program_update_date = SYSDATE
5810 WHERE cost_ind_compiled_set_id = compiled_set_id
5811 AND expenditure_item_date BETWEEN --Bug5917245 Removed TRUNC
5812 l_start_date AND
5813 NVL(l_end_date, expenditure_item_date)
5814 AND EXISTS
5815 (SELECT t1.task_id
5816 FROM pa_project_types_all pt,
5817 pa_projects_all p,
5818 pa_tasks t1
5819 WHERE pt.project_type = p.project_type
5820 AND nvl(pt.org_id, -99) = nvl(p.org_id, -99)
5821 AND p.project_id = t1.project_id
5822 AND t1.cost_ind_sch_fixed_date is NULL
5823 AND t1.task_id = ei.task_id
5824 AND pt.project_type_class_code = 'CAPITAL'
5825 AND pt.capital_cost_type_code = 'B')
5826 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5827 /* AND ei.task_id NOT IN
5828 (select t.task_id
5829 FROM pa_projects_all p, pa_tasks t
5830 WHERE t.project_id=p.project_id AND
5831 ei.task_id = t.task_id AND
5832 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for bug# 2933915*/
5833 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
5834 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' );
5835
5836 COMMIT;
5837
5838 ei_count := 0;
5839
5840 FOR ei11_row IN ei11_cursor LOOP
5841
5842 PA_Adjustments.InsAuditRec(ei11_row.expenditure_item_id,
5843 cost_adj_reason,
5844 adj_module,
5845 x_last_updated_by,
5846 x_last_update_login,
5847 status,
5848 x_request_id,
5849 x_program_id,
5850 x_program_application_id,
5851 SYSDATE);
5852
5853 IF (status <> 0) THEN
5854 raise INSERT_ADJ_ACTIVITY_ERROR;
5855 END IF;
5856
5857 ei_count := ei_count + 1;
5858
5859 IF (ei_count >= 500) THEN
5860 COMMIT;
5861 ei_count := 0;
5862 END IF;
5863
5864 END LOOP;
5865
5866 COMMIT;
5867
5868 --
5869 -- Section for Capital projects
5870 --
5871
5872
5873 /*
5874 * IC related change:
5875 * updates and activity logging done for TP schedule change.
5876 * Note: explain plan is fine in RBO, cant test it in CBO because of
5877 * non-availability of volume data.
5878 */
5879 /*
5880 * Bug 4885396 : Moved 3 EI based checks from Exists subquery to the
5881 * main query.
5882 */
5883 UPDATE pa_expenditure_items_all ei
5884 SET cc_bl_distributed_code =
5885 decode( cc_cross_charge_code,'B',
5886 'N',
5887 cc_bl_distributed_code),
5888 cc_ic_processed_code =
5889 decode( cc_cross_charge_code,'I',
5890 'N',
5891 cc_ic_processed_code),
5892 Denom_Tp_Currency_Code = NULL,
5893 Denom_Transfer_Price = NULL,
5894 Acct_Tp_Rate_Type = NULL,
5895 Acct_Tp_Rate_Date = NULL,
5896 Acct_Tp_Exchange_Rate = NULL,
5897 Acct_Transfer_Price = NULL,
5898 Projacct_Transfer_Price = NULL,
5899 Cc_Markup_Base_Code = NULL,
5900 Tp_Base_Amount = NULL,
5901 Tp_Bill_Rate = NULL,
5902 Tp_Bill_Markup_Percentage = NULL,
5903 Tp_Schedule_line_Percentage = NULL,
5904 Tp_Rule_percentage = NULL,
5905 last_update_date = SYSDATE,
5906 last_updated_by = x_last_updated_by,
5907 last_update_login = x_last_update_login,
5908 request_id = x_request_id,
5909 program_application_id = x_program_application_id,
5910 program_id = x_program_id,
5911 program_update_date = SYSDATE
5912 WHERE tp_ind_compiled_set_id = compiled_set_id
5913 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y'
5914 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' )
5915 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
5916 AND EXISTS
5917 (SELECT task_id
5918 FROM pa_tasks task, pa_system_linkages syslink /*, pa_projects_all proj :Redundant :2933915*/
5919 WHERE task.task_id = ei.task_id
5920 AND ei.system_linkage_function = syslink.function
5921 /* AND task.project_id = proj.project_id Commented for 2933915*/
5922 AND task.project_id = ei.project_id /*2933915*/
5923 AND (
5924 ( NVL(task.labor_tp_fixed_date, ei.expenditure_item_date) BETWEEN --Bug 5917245 Removed TRUNC
5925 l_start_date AND
5926 NVL(l_end_date, NVL(task.labor_tp_fixed_date,ei.expenditure_item_date))
5927 AND
5928 syslink.labor_non_labor_flag = 'Y')
5929 OR
5930 ( NVL(task.nl_tp_fixed_date, ei.expenditure_item_date) BETWEEN --Bug 5917245 Removed TRUNC
5931 l_start_date AND
5932 NVL(l_end_date, NVL(task.nl_tp_fixed_date,ei.expenditure_item_date))
5933 AND
5934 syslink.labor_non_labor_flag = 'N')
5935 ));
5936
5937 COMMIT;
5938
5939 ei_count := 0;
5940
5941 FOR ei13_row IN ei13_cursor LOOP
5942
5943 PA_Adjustments.InsAuditRec(ei13_row.expenditure_item_id,
5944 tp_adj_reason,
5945 adj_module,
5946 x_last_updated_by,
5947 x_last_update_login,
5948 status,
5949 x_request_id,
5950 x_program_id,
5951 x_program_application_id,
5952 SYSDATE);
5953
5954 IF (status <> 0) THEN
5955 raise INSERT_ADJ_ACTIVITY_ERROR;
5956 END IF;
5957
5958 ei_count := ei_count + 1;
5959
5960 IF (ei_count >= 500) THEN
5961 COMMIT;
5962 ei_count := 0;
5963 END IF;
5964
5965 END LOOP;
5966
5967 COMMIT;
5968
5969 else
5970
5971 --
5972 -- Provisional types
5973 --
5974
5975 /*
5976 Burdening related changes
5977 Reset burdened_costs to null so that costing program recalculates them.
5978 */
5979 -- Modifying the statement to exclude closed project items as well as
5980 -- net zero items (Bug # 730849)
5981 -- (09/17/98)
5982 --
5983 -- Added Nvl for net_zero_adjustment_flag (896190)
5984 -- ---------------------------------------------------------------------
5985 /* Updating Project_Burdened_Cost also for Bug 2736773 */
5986 UPDATE pa_expenditure_items_all ei
5987 SET cost_distributed_flag = 'N' ,
5988 adjustment_type ='BURDEN_RECOMPILE',
5989 cost_burden_distributed_flag = 'N',
5990 last_update_date = SYSDATE,
5991 last_updated_by = x_last_updated_by,
5992 last_update_login = x_last_update_login,
5993 request_id = x_request_id,
5994 program_application_id = x_program_application_id,
5995 program_id = x_program_id,
5996 program_update_date = SYSDATE,
5997 denom_burdened_cost = NULL,
5998 project_burdened_cost = NULL,
5999 acct_burdened_cost = NULL,
6000 burden_cost = NULL
6001 WHERE ei.cost_ind_compiled_set_id = compiled_set_id
6002 AND ei.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
6003 l_start_date AND NVL(l_end_date, ei.expenditure_item_date)
6004 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
6005 /* AND ei.task_id NOT IN
6006 (select t.task_id
6007 FROM pa_projects_all p, pa_tasks t
6008 WHERE t.project_id=p.project_id AND
6009 ei.task_id = t.task_id AND
6010 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
6011 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id)<>'Y' /*2933915*/
6012 AND ((pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s','D','d') AND l_burden_profile ='N') /*Added for 2933915*/
6013 OR (pa_utils2.Proj_Type_Burden_Disp_Method(ei.project_id) IN ('S','s') AND l_burden_profile ='Y'))
6014 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' ) ;
6015
6016
6017 /*2933915 :Added the exists clause in above update to indicate that except profile ='Y' and display_method ='D' -for all cases we will
6018 be marking ei for cost reprocessing */
6019
6020 -- consider volume of expenditure items having the same compiled set id
6021 COMMIT;
6022
6023 if l_gms_enabled = 'Y' then --Bug 5726575
6024 gms_pa_api3.mark_prev_rev_enc_items (errbuf => err_buf,
6025 retcode => ret_code,
6026 p_compiled_set_id => compiled_set_id,
6027 p_start_date => l_start_date,
6028 p_end_date => l_end_date,
6029 p_mode => 'O');
6030 if err_buf is not null then
6031 raise GMS_INSERT_ENC_ITEM_ERROR;
6032 end if;
6033 commit;
6034 end if;
6035
6036 FOR ei7_row IN ei7_cursor LOOP
6037
6038 PA_Adjustments.InsAuditRec(ei7_row.expenditure_item_id,
6039 cost_adj_reason,
6040 adj_module,
6041 x_last_updated_by,
6042 x_last_update_login,
6043 status,
6044 x_request_id,
6045 x_program_id,
6046 x_program_application_id,
6047 SYSDATE);
6048
6049 IF (status <> 0) THEN
6050 raise INSERT_ADJ_ACTIVITY_ERROR;
6051 END IF;
6052
6053 ei_count := ei_count + 1;
6054
6055 IF (ei_count >= 500) THEN
6056 COMMIT;
6057 ei_count := 0;
6058 END IF;
6059
6060 END LOOP;
6061
6062 COMMIT;
6063
6064 -- Modifying the statement to exclude closed project items as well as
6065 -- net zero items (Bug # 730849)
6066 -- (09/17/98)
6067 --
6068 -- Added Nvl for net_zero_adjustment_flag (896190)
6069 --
6070 UPDATE pa_expenditure_items_all ei
6071 SET revenue_distributed_flag = 'N',
6072 last_update_date = SYSDATE,
6073 last_updated_by = x_last_updated_by,
6074 last_update_login = x_last_update_login,
6075 request_id = x_request_id,
6076 program_application_id = x_program_application_id,
6077 program_id = x_program_id,
6078 program_update_date = SYSDATE
6079 WHERE ( (rev_ind_compiled_set_id = compiled_set_id)
6080 OR (inv_ind_compiled_set_id = compiled_set_id))
6081 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
6082 l_start_date AND
6083 NVL(l_end_date, expenditure_item_date)
6084 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
6085 /***** AND ei.task_id NOT IN
6086 (select t.task_id
6087 FROM pa_projects_all p, pa_tasks t
6088 WHERE t.project_id=p.project_id AND
6089 ei.task_id = t.task_id AND
6090 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
6091 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
6092 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' ) ;
6093
6094
6095 FOR ei8_row IN ei8_cursor LOOP
6096
6097 PA_Adjustments.InsAuditRec(ei8_row.expenditure_item_id,
6098 rev_adj_reason,
6099 adj_module,
6100 x_last_updated_by,
6101 x_last_update_login,
6102 status,
6103 x_request_id,
6104 x_program_id,
6105 x_program_application_id,
6106 SYSDATE);
6107
6108 IF (status <> 0) THEN
6109 raise INSERT_ADJ_ACTIVITY_ERROR;
6110 END IF;
6111
6112 ei_count := ei_count + 1;
6113
6114 IF (ei_count >= 500) THEN
6115 COMMIT;
6116 ei_count := 0;
6117 END IF;
6118
6119 END LOOP;
6120
6121 COMMIT;
6122
6123 FOR ei9_row IN ei9_cursor LOOP
6124
6125 PA_Adjustments.InsAuditRec(ei9_row.expenditure_item_id,
6126 inv_adj_reason,
6127 adj_module,
6128 x_last_updated_by,
6129 x_last_update_login,
6130 status,
6131 x_request_id,
6132 x_program_id,
6133 x_program_application_id,
6134 SYSDATE);
6135
6136 IF (status <> 0) THEN
6137 raise INSERT_ADJ_ACTIVITY_ERROR;
6138 END IF;
6139
6140 ei_count := ei_count + 1;
6141
6142 IF (ei_count >= 500) THEN
6143 COMMIT;
6144 ei_count := 0;
6145 END IF;
6146
6147 END LOOP;
6148
6149 COMMIT;
6150
6151 --
6152 -- Section for Capital Projects
6153 --
6154 -- This section is created as a part of fix for bug 897479 .
6155 --
6156 --
6157 UPDATE pa_expenditure_items_all ei
6158 SET revenue_distributed_flag = 'N',
6159 last_update_date = SYSDATE,
6160 last_updated_by = x_last_updated_by,
6161 last_update_login = x_last_update_login,
6162 request_id = x_request_id,
6163 program_application_id = x_program_application_id,
6164 program_id = x_program_id,
6165 program_update_date = SYSDATE
6166 WHERE cost_ind_compiled_set_id = compiled_set_id
6167 AND expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
6168 l_start_date AND
6169 NVL(l_end_date, expenditure_item_date)
6170 AND EXISTS
6171 (SELECT t1.task_id
6172 FROM pa_project_types_all pt,
6173 pa_projects_all p,
6174 pa_tasks t1
6175 WHERE pt.project_type = p.project_type
6176 AND nvl(pt.org_id, -99) = nvl(p.org_id, -99)
6177 AND p.project_id = t1.project_id
6178 AND t1.task_id = ei.task_id
6179 AND pt.project_type_class_code = 'CAPITAL'
6180 AND pt.capital_cost_type_code = 'B')
6181 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
6182 /**** AND ei.task_id NOT IN
6183 (select t.task_id
6184 FROM pa_projects_all p, pa_tasks t
6185 WHERE t.project_id=p.project_id AND
6186 ei.task_id = t.task_id AND
6187 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
6188 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
6189 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' ) ;
6190
6191 -- consider volume of expenditure items having the same compiled set id
6192 COMMIT;
6193
6194 FOR ei12_row IN ei12_cursor LOOP
6195
6196 PA_Adjustments.InsAuditRec(ei12_row.expenditure_item_id,
6197 cost_adj_reason,
6198 adj_module,
6199 x_last_updated_by,
6200 x_last_update_login,
6201 status,
6202 x_request_id,
6203 x_program_id,
6204 x_program_application_id,
6205 SYSDATE);
6206
6207 IF (status <> 0) THEN
6208 raise INSERT_ADJ_ACTIVITY_ERROR;
6209 END IF;
6210
6211 ei_count := ei_count + 1;
6212
6213 IF (ei_count >= 500) THEN
6214 COMMIT;
6215 ei_count := 0;
6216 END IF;
6217
6218 END LOOP;
6219
6220 COMMIT;
6221
6222 --
6223 -- End section for Capital projects
6224 --
6225
6226
6227 /*
6228 * IC related change:
6229 * updates and activity logging done for TP schedule change.
6230 * Note: explain plan is fine in RBO, cant test it in CBO because of
6231 * non-availability of volume data.
6232 */
6233 UPDATE pa_expenditure_items_all ei
6234 SET cc_bl_distributed_code =
6235 decode( cc_cross_charge_code,'B',
6236 'N',
6237 cc_bl_distributed_code),
6238 cc_ic_processed_code =
6239 decode( cc_cross_charge_code,'I',
6240 'N',
6241 cc_ic_processed_code),
6242 Denom_Tp_Currency_Code = NULL,
6243 Denom_Transfer_Price = NULL,
6244 Acct_Tp_Rate_Type = NULL,
6245 Acct_Tp_Rate_Date = NULL,
6246 Acct_Tp_Exchange_Rate = NULL,
6247 Acct_Transfer_Price = NULL,
6248 Projacct_Transfer_Price = NULL,
6249 Cc_Markup_Base_Code = NULL,
6250 Tp_Base_Amount = NULL,
6251 Tp_Bill_Rate = NULL,
6252 Tp_Bill_Markup_Percentage = NULL,
6253 Tp_Schedule_line_Percentage = NULL,
6254 Tp_Rule_percentage = NULL,
6255 last_update_date = SYSDATE,
6256 last_updated_by = x_last_updated_by,
6257 last_update_login = x_last_update_login,
6258 request_id = x_request_id,
6259 program_application_id = x_program_application_id,
6260 program_id = x_program_id,
6261 program_update_date = SYSDATE
6262 WHERE ei.tp_ind_compiled_set_id = compiled_set_id
6263 AND ei.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
6264 l_start_date AND
6265 NVL(l_end_date, ei.expenditure_item_date)
6266 AND nvl(ei.net_zero_adjustment_flag, 'N') <>'Y'
6267 /*** AND ei.task_id NOT IN
6268 (select t.task_id
6269 FROM pa_projects_all p, pa_tasks t
6270 WHERE t.project_id=p.project_id AND
6271 ei.task_id = t.task_id AND
6272 pa_project_stus_utils.Is_Project_Status_Closed(p.project_status_code)='Y') Commented for 2933915*/
6273 AND pa_project_stus_utils.Is_Project_Closed(ei.project_id) <> 'Y' /*2933915*/
6274 AND ( gms_pa_api2.is_award_closed(ei.expenditure_item_id,ei.task_id) = 'N' ) ;
6275
6276
6277 /***2933915:UPDATE eis for 'BURDEN_RESUMMARIZE' ONLY if cost_distributed_flag ='Y' and profile option is enabled and burdening is
6278 on separate ei */
6279
6280 /*====================================================================+
6281 | M - If Enhanced Burdening is SET, for Separate line burdening |
6282 | transactions the adjustment_type is set to BURDEN_RESUMMARIZE |
6283 | - if cost_distributed_flag is Y. |
6284 +====================================================================*/
6285 UPDATE pa_expenditure_items_all ITEM
6286 SET ITEM.adjustment_type = decode(ITEM.cost_ind_compiled_set_id, compiled_set_id, 'BURDEN_RESUMMARIZE' ,ITEM.adjustment_type)
6287 WHERE ITEM.cost_distributed_flag ='Y'
6288 AND ITEM.adjustment_type IS NULL
6289 AND exists ( select 1 from pa_cost_distribution_lines_all cdl
6290 where cdl.burden_sum_source_run_id >0
6291 AND cdl.expenditure_item_id =ITEM.expenditure_item_id
6292 AND cdl.line_type ='R'
6293 AND nvl(cdl.reversed_flag,'N') ='N'
6294 AND cdl.line_num_reversed is NULL)
6295 AND ITEM.cost_ind_compiled_set_id = compiled_set_id
6296 /*S.N. Bug4560630*/
6297 AND (ITEM.expenditure_item_date BETWEEN --Bug 5917245 Removed TRUNC
6298 l_start_date AND
6299 NVL(l_end_date, ITEM.expenditure_item_date))
6300 /*E.N. Bug4560630*/
6301 AND l_burden_profile ='Y'
6302 AND pa_utils2.Proj_Type_Burden_Disp_Method(ITEM.project_id) IN ('D','d');
6303
6304 COMMIT;
6305
6306 FOR ei14_row IN ei14_cursor LOOP
6307
6308 PA_Adjustments.InsAuditRec(ei14_row.expenditure_item_id,
6309 tp_adj_reason,
6310 adj_module,
6311 x_last_updated_by,
6312 x_last_update_login,
6313 status,
6314 x_request_id,
6315 x_program_id,
6316 x_program_application_id,
6317 SYSDATE);
6318
6319 IF (status <> 0) THEN
6320 raise INSERT_ADJ_ACTIVITY_ERROR;
6321 END IF;
6322
6323 ei_count := ei_count + 1;
6324
6325 IF (ei_count >= 500) THEN
6326 COMMIT;
6327 ei_count := 0;
6328 END IF;
6329
6330 END LOOP;
6331
6332 COMMIT;
6333
6334 end if;
6335
6336 EXCEPTION
6337 when INSERT_ADJ_ACTIVITY_ERROR then
6338 return;
6339
6340 when GMS_INSERT_ENC_ITEM_ERROR then --Bug 5726575
6341 stage := 120;
6342 status := ret_code;
6343
6344 when OTHERS then
6345 stage := 100;
6346 status := sqlcode;
6347
6348 END mark_prev_rev_exp_items;
6349
6350
6351 --
6352 -- PROCEDURE
6353 -- add_adjustment_activity
6354 --
6355 -- PURPOSE
6356 -- The objective of this procedure is to add adjustment
6357 -- activity of affected expenditure items for auditing purpose
6358 --
6359 -- HISTORY
6360 --
6361 -- 30-JAN-95 S Lee Created
6362 --
6363
6364 /*
6365 procedure add_adjustment_activity(compiled_set_id IN number,
6366 p_cost_base IN pa_cost_bases.cost_base%TYPE
6367 ,p_cost_plus_structure IN pa_cost_plus_structures.cost_plus_structure%TYPE,
6368 cost_adj_reason IN varchar2,
6369 rev_adj_reason IN varchar2,
6370 inv_adj_reason IN varchar2,
6371 tp_adj_reason IN varchar2,
6372 status IN OUT NOCOPY number,
6373 stage IN OUT NOCOPY number,
6374 l_expenditure_item_id_tab IN PA_PLSQL_DATATYPES.IDTABTYP,
6375 l_adj_tyep_tab IN PA_PLSQL_DATATYPES.Char30TabTyp)
6376 */
6377 procedure add_adjustment_activity( l_expenditure_item_id_tab IN PA_PLSQL_DATATYPES.IDTABTYP
6378 ,l_adj_type_tab IN PA_PLSQL_DATATYPES.Char30TabTyp
6379 ,status IN OUT NOCOPY number
6380 ,stage IN OUT NOCOPY number
6381 )
6382 IS
6383
6384 -- Local variable
6385 ei_count number;
6386 adj_module constant varchar2(10) := 'PACOCRSR';
6387
6388 -- Standard who
6389 x_request_id NUMBER(15);
6390
6391 -- Exception
6392 INSERT_ADJ_ACTIVITY_ERROR exception;
6393
6394 /* -- Commented for bug4527736
6395 -- Cursor definition
6396
6397 CURSOR ei_cost_cursor
6398 IS
6399 SELECT expenditure_item_id
6400 FROM pa_expenditure_items_all ITEM
6401 WHERE cost_ind_compiled_set_id = compiled_set_id
6402 AND adjustment_type in ('BURDEN_RECOMPILE','BURDEN_RESUMMARIZE','RECALC_BURDEN')
6403 AND request_id = x_request_id
6404 AND EXISTS (SELECT NULL
6405 FROM pa_cost_base_exp_types cbet
6406 WHERE cbet.cost_base = p_cost_base
6407 AND cbet.cost_plus_structure = p_cost_plus_structure
6408 AND cbet.cost_base_type = INDIRECT_COST_CODE
6409 AND cbet.expenditure_type = ITEM.expenditure_type
6410 )
6411 ;
6412
6413 CURSOR ei_rev_cursor
6414 IS
6415 SELECT expenditure_item_id
6416 FROM pa_expenditure_items_all ITEM
6417 WHERE rev_ind_compiled_set_id = compiled_set_id
6418 AND request_id = x_request_id
6419 AND EXISTS (SELECT NULL
6420 FROM pa_cost_base_exp_types cbet
6421 WHERE cbet.cost_base = p_cost_base
6422 AND cbet.cost_plus_structure = p_cost_plus_structure
6423 AND cbet.cost_base_type = INDIRECT_COST_CODE
6424 AND cbet.expenditure_type = ITEM.expenditure_type
6425 )
6426 ;
6427
6428 CURSOR ei_inv_cursor
6429 IS
6430 SELECT expenditure_item_id
6431 FROM pa_expenditure_items_all ITEM
6432 WHERE inv_ind_compiled_set_id = compiled_set_id
6433 AND request_id = x_request_id
6434 AND EXISTS (SELECT NULL
6435 FROM pa_cost_base_exp_types cbet
6436 WHERE cbet.cost_base = p_cost_base
6437 AND cbet.cost_plus_structure = p_cost_plus_structure
6438 AND cbet.cost_base_type = INDIRECT_COST_CODE
6439 AND cbet.expenditure_type = ITEM.expenditure_type
6440 )
6441 ; End Comment bug4527736 */
6442 /*
6443 * IC related change:
6444 * New cursor added for TP schedule.
6445 */
6446 /* Commented for bug 4527736
6447 CURSOR ei_tp_cursor
6448 IS
6449 SELECT expenditure_item_id
6450 FROM pa_expenditure_items_all ITEM
6451 WHERE tp_ind_compiled_set_id = compiled_set_id
6452 AND request_id = x_request_id
6453 AND EXISTS (SELECT NULL
6454 FROM pa_cost_base_exp_types cbet
6455 WHERE cbet.cost_base = p_cost_base
6456 AND cbet.cost_plus_structure = p_cost_plus_structure
6457 AND cbet.cost_base_type = INDIRECT_COST_CODE
6458 AND cbet.expenditure_type = ITEM.expenditure_type
6459 )
6460 ;*/
6461
6462 -- Standard who
6463 x_last_updated_by NUMBER(15);
6464 x_last_update_login NUMBER(15);
6465 x_program_application_id NUMBER(15);
6466 x_program_id NUMBER(15);
6467 -- l_eid_tbl PA_PLSQL_DATATYPES.IdTabTyp; /*3040724*/
6468 l_limit_size NUMBER :=500 ; /*3040724*/
6469
6470 BEGIN
6471
6472 -- Initialize output parameters
6473 status := 0;
6474 stage := 100;
6475
6476 --
6477 -- Get the standard who information
6478 --
6479 x_last_updated_by := FND_GLOBAL.USER_ID;
6480 x_last_update_login := FND_GLOBAL.LOGIN_ID;
6481 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
6482 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
6483 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
6484
6485 ei_count := 0;
6486 /*
6487 IF (cost_adj_reason IS NOT NULL)
6488 THEN
6489 begin
6490
6491 OPEN ei_cost_cursor;
6492 LOOP
6493
6494 l_eid_tbl.DELETE;
6495 FETCH ei_cost_cursor BULK COLLECT INTO l_eid_tbl
6496 LIMIT l_limit_size ;
6497
6498 IF l_eid_tbl.count = 0
6499 THEN
6500 EXIT;
6501 END IF;
6502
6503 FORALL i in 1..l_eid_tbl.count
6504 INSERT INTO pa_expend_item_adj_activities (
6505 expenditure_item_id
6506 , last_update_date
6507 , last_updated_by
6508 , creation_date
6509 , created_by
6510 , last_update_login
6511 , activity_date
6512 , exception_activity_code
6513 , module_code
6514 , request_id
6515 , program_application_id
6516 , program_id
6517 , program_update_date )
6518 VALUES (
6519 l_eid_tbl(i) -- expenditure_item_id
6520 , sysdate -- last_update_date
6521 , x_last_updated_by -- last_updated_by
6522 , sysdate -- creation_date
6523 , x_last_updated_by -- created_by
6524 , x_last_update_login -- last_update_login
6525 , sysdate -- activity_date
6526 , cost_adj_reason -- exception_activity_code
6527 , adj_module -- module_code
6528 , x_request_id -- request_id
6529 , x_program_application_id -- program_application_id
6530 , x_program_id -- program_id
6531 , sysdate ); -- program_update_date
6532
6533 EXIT WHEN ei_cost_cursor%NOTFOUND;
6534
6535 END LOOP;
6536 CLOSE ei_cost_cursor;
6537 EXCEPTION
6538 WHEN OTHERS THEN
6539 raise INSERT_ADJ_ACTIVITY_ERROR;
6540 end ;
6541 END IF ; ------ cost_adj_reason
6542
6543 IF (rev_adj_reason IS NOT NULL)
6544 THEN
6545 begin
6546
6547 OPEN ei_rev_cursor;
6548 LOOP
6549
6550 l_eid_tbl.DELETE;
6551 FETCH ei_rev_cursor BULK COLLECT INTO l_eid_tbl
6552 LIMIT l_limit_size ;
6553
6554 IF l_eid_tbl.count = 0
6555 THEN
6556 EXIT;
6557 END IF;
6558
6559 FORALL i in 1..l_eid_tbl.count
6560 INSERT INTO pa_expend_item_adj_activities (
6561 expenditure_item_id
6562 , last_update_date
6563 , last_updated_by
6564 , creation_date
6565 , created_by
6566 , last_update_login
6567 , activity_date
6568 , exception_activity_code
6569 , module_code
6570 , request_id
6571 , program_application_id
6572 , program_id
6573 , program_update_date )
6574 VALUES (
6575 l_eid_tbl(i) -- expenditure_item_id
6576 , sysdate -- last_update_date
6577 , x_last_updated_by -- last_updated_by
6578 , sysdate -- creation_date
6579 , x_last_updated_by -- created_by
6580 , x_last_update_login -- last_update_login
6581 , sysdate -- activity_date
6582 , rev_adj_reason -- exception_activity_code
6583 , adj_module -- module_code
6584 , x_request_id -- request_id
6585 , x_program_application_id -- program_application_id
6586 , x_program_id -- program_id
6587 , sysdate ); -- program_update_date
6588
6589 EXIT WHEN ei_rev_cursor%NOTFOUND;
6590 END LOOP;
6591 Close ei_rev_cursor;
6592 EXCEPTION
6593 WHEN OTHERS THEN
6594 raise INSERT_ADJ_ACTIVITY_ERROR;
6595 end ;
6596 END IF ; ------ rev_adj_reason
6597
6598 IF (inv_adj_reason IS NOT NULL)
6599 THEN
6600 begin
6601
6602 OPEN ei_inv_cursor;
6603 LOOP
6604
6605 l_eid_tbl.DELETE;
6606 FETCH ei_inv_cursor BULK COLLECT INTO l_eid_tbl
6607 LIMIT l_limit_size ;
6608
6609 IF l_eid_tbl.count = 0
6610 THEN
6611 EXIT;
6612 END IF;
6613
6614 FORALL i in 1..l_eid_tbl.count
6615 INSERT INTO pa_expend_item_adj_activities (
6616 expenditure_item_id
6617 , last_update_date
6618 , last_updated_by
6619 , creation_date
6620 , created_by
6621 , last_update_login
6622 , activity_date
6623 , exception_activity_code
6624 , module_code
6625 , request_id
6626 , program_application_id
6627 , program_id
6628 , program_update_date )
6629 VALUES (
6630 l_eid_tbl(i) -- expenditure_item_id
6631 , sysdate -- last_update_date
6632 , x_last_updated_by -- last_updated_by
6633 , sysdate -- creation_date
6634 , x_last_updated_by -- created_by
6635 , x_last_update_login -- last_update_login
6636 , sysdate -- activity_date
6637 , inv_adj_reason -- exception_activity_code
6638 , adj_module -- module_code
6639 , x_request_id -- request_id
6640 , x_program_application_id -- program_application_id
6641 , x_program_id -- program_id
6642 , sysdate ); -- program_update_date
6643
6644 EXIT WHEN ei_inv_cursor%NOTFOUND;
6645 END LOOP;
6646 Close ei_inv_cursor;
6647 EXCEPTION
6648 WHEN OTHERS THEN
6649 raise INSERT_ADJ_ACTIVITY_ERROR;
6650 end ;
6651 END IF ; ------ inv_adj_reason
6652
6653 IF (tp_adj_reason IS NOT NULL)
6654 THEN
6655 begin
6656
6657 OPEN ei_tp_cursor;
6658 LOOP
6659
6660 l_eid_tbl.DELETE;
6661 FETCH ei_tp_cursor BULK COLLECT INTO l_eid_tbl
6662 LIMIT l_limit_size ;
6663
6664 IF l_eid_tbl.count = 0
6665 THEN
6666 EXIT;
6667 END IF;
6668
6669 FORALL i in 1..l_eid_tbl.count
6670 INSERT INTO pa_expend_item_adj_activities (
6671 expenditure_item_id
6672 , last_update_date
6673 , last_updated_by
6674 , creation_date
6675 , created_by
6676 , last_update_login
6677 , activity_date
6678 , exception_activity_code
6679 , module_code
6680 , request_id
6681 , program_application_id
6682 , program_id
6683 , program_update_date )
6684 VALUES (
6685 l_eid_tbl(i) -- expenditure_item_id
6686 , sysdate -- last_update_date
6687 , x_last_updated_by -- last_updated_by
6688 , sysdate -- creation_date
6689 , x_last_updated_by -- created_by
6690 , x_last_update_login -- last_update_login
6691 , sysdate -- activity_date
6692 , tp_adj_reason -- exception_activity_code
6693 , adj_module -- module_code
6694 , x_request_id -- request_id
6695 , x_program_application_id -- program_application_id
6696 , x_program_id -- program_id
6697 , sysdate ); -- program_update_date
6698
6699 EXIT WHEN ei_tp_cursor%NOTFOUND;
6700 END LOOP;
6701 Close ei_tp_cursor;
6702 EXCEPTION
6703 WHEN OTHERS THEN
6704 raise INSERT_ADJ_ACTIVITY_ERROR;
6705 end ;
6706 END IF ; ------ tp_adj_reason
6707 */
6708 if l_expenditure_item_id_tab.count > 0 then
6709 FORALL i in 1..l_expenditure_item_id_tab.count
6710 INSERT INTO pa_expend_item_adj_activities (
6711 expenditure_item_id
6712 , last_update_date
6713 , last_updated_by
6714 , creation_date
6715 , created_by
6716 , last_update_login
6717 , activity_date
6718 , exception_activity_code
6719 , module_code
6720 , request_id
6721 , program_application_id
6722 , program_id
6723 , program_update_date )
6724 VALUES (
6725 l_expenditure_item_id_tab(i) -- expenditure_item_id
6726 , sysdate -- last_update_date
6727 , x_last_updated_by -- last_updated_by
6728 , sysdate -- creation_date
6729 , x_last_updated_by -- created_by
6730 , x_last_update_login -- last_update_login
6731 , sysdate -- activity_date
6732 , l_adj_type_tab(i) -- exception_activity_code
6733 , adj_module -- module_code
6734 , x_request_id -- request_id
6735 , x_program_application_id -- program_application_id
6736 , x_program_id -- program_id
6737 , sysdate ); -- program_update_date
6738 end if;
6739
6740 EXCEPTION
6741 when OTHERS then
6742 status := SQLCODE;
6743
6744 END add_adjustment_activity;
6745
6746
6747 --
6748 -- PROCEDURE
6749 -- disable_rate_sch_revision
6750 --
6751 -- PURPOSE
6752 -- The objective of this procedure is to mark the compiled sets
6753 -- as history for an out-of-date rate schedule revision.
6754 -- When the indirect cost multipliers are updated, the original
6755 -- compiled sets are out of date, and should be marked as history.
6756 --
6757 -- HISTORY
6758 --
6759 -- 10-JUN-94 S Lee Created
6760 --
6761 /****2933915 : Restructured this procedure to do Selective Obsoletion .
6762 Selective obsoletion implies that only those compiled set ids will be obsoleted
6763 for which the cost base is impacted i.e if any of the cost code is modified/deleted
6764 for any org . in a particular revision .
6765 If multipliers are present explicitly for any org for ALL the cost codes -that have
6766 not changed then we should not be obsoleting the compiled set id for this org/cost base .
6767 ************************************************************************************/
6768 procedure disable_rate_sch_revision(rate_sch_rev_id IN number,
6769 ver_id IN number, /*2933915**/
6770 org_id IN number, /**2933915**/
6771 status IN OUT NOCOPY number,
6772 stage IN OUT NOCOPY number)
6773
6774 is
6775
6776 CURSOR org_cursor
6777 IS
6778 SELECT organization_id_child org_id_child
6779 FROM per_org_structure_elements
6780 CONNECT BY PRIOR organization_id_child = organization_id_parent
6781 AND org_structure_version_id = ver_id
6782 START WITH organization_id_parent = org_id
6783 AND org_structure_version_id = ver_id
6784 UNION
6785 select org_id from dual ;
6786
6787 -- Standard who
6788 x_last_updated_by NUMBER(15);
6789 x_last_update_login NUMBER(15);
6790 x_request_id NUMBER(15);
6791 x_program_application_id NUMBER(15);
6792 x_program_id NUMBER(15);
6793 org_override NUMBER :=0;
6794 BEGIN
6795
6796 --
6797 -- Get the standard who information
6798 --
6799 x_last_updated_by := FND_GLOBAL.USER_ID;
6800 x_last_update_login := FND_GLOBAL.LOGIN_ID;
6801 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
6802 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
6803 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
6804
6805 FOR org_id_rec in org_cursor /*Loop for all the children (including impacted org) of the impacted org*/
6806 LOOP
6807
6808 /*3016281 :If explicit multipliers are defined for all the cost codes in impacted cost bases for an org then we should not obsolete
6809 compiled set ids for that.
6810 This is in view of the enhancement that new compiled set ids will not be generated for an org if explicit multipliers are defined
6811 for all the cost codes in impacted cost bases for that org*/
6812
6813 IF pa_cost_plus.check_for_explicit_multiplier(rate_sch_rev_id ,org_id_rec.org_id_child) =0 THEN /*3016281*/
6814 --FOR cost_base_rec in impacted_cost_bases(rate_sch_rev_id)
6815 IF G_IMPACTED_COST_BASES_TAB.COUNT <> 0 THEN /*4590268*/
6816
6817 FOR i IN G_IMPACTED_COST_BASES_TAB.FIRST .. G_IMPACTED_COST_BASES_TAB.LAST
6818 LOOP
6819 UPDATE pa_ind_compiled_sets
6820 SET status = 'H',
6821 last_update_date = SYSDATE,
6822 last_updated_by = x_last_updated_by,
6823 last_update_login = x_last_update_login,
6824 request_id = x_request_id,
6825 program_application_id = x_program_application_id,
6826 program_id = x_program_id,
6827 program_update_date = SYSDATE
6828 WHERE ind_rate_sch_revision_id = rate_sch_rev_id
6829 AND organization_id = org_id_rec.org_id_child
6830 --AND cost_base = cost_base_rec.cost_base
6831 AND cost_base = G_IMPACTED_COST_BASES_TAB(i)
6832 AND status = 'A' ;
6833 END LOOP ; /*End of LOOP for impacted_cost_bases*/
6834 END IF; /*4590268*/
6835 END IF; /*3016281*/
6836 END LOOP; /*End of LOOP for Org_cursor*/
6837 EXCEPTION
6838 WHEN OTHERS THEN
6839 stage := 100;
6840 status := SQLCODE;
6841 END disable_rate_sch_revision;
6842
6843 --
6844 -- PROCEDURE
6845 -- disable_sch_rev_org
6846 --
6847 -- PURPOSE
6848 -- The objective of this procedure is to mark the compiled sets
6849 -- as history for the specified rate schedule revision and
6850 -- organization .
6851 --
6852 -- HISTORY
6853 --
6854 -- 25-AUG-94 S Lee Created
6855 --
6856
6857 procedure disable_sch_rev_org(rate_sch_rev_id IN number,
6858 org_id IN number,
6859 status IN OUT NOCOPY number,
6860 stage IN OUT NOCOPY number)
6861
6862 is
6863
6864 -- Standard who
6865 x_last_updated_by NUMBER(15);
6866 x_last_update_login NUMBER(15);
6867 x_request_id NUMBER(15);
6868 x_program_application_id NUMBER(15);
6869 x_program_id NUMBER(15);
6870
6871 BEGIN
6872
6873 --
6874 -- Get the standard who information
6875 --
6876 x_last_updated_by := FND_GLOBAL.USER_ID;
6877 x_last_update_login := FND_GLOBAL.LOGIN_ID;
6878 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
6879 x_program_application_id := FND_GLOBAL.PROG_APPL_ID;
6880 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
6881
6882 --
6883 -- Set the compiled set to history
6884 --
6885 /*FOR cost_base_rec in impacted_cost_bases(rate_sch_rev_id) **2933915*/
6886 IF G_IMPACTED_COST_BASES_TAB.COUNT <> 0 THEN /*4590268*/
6887
6888 FOR i IN G_IMPACTED_COST_BASES_TAB.FIRST .. G_IMPACTED_COST_BASES_TAB.LAST
6889 LOOP
6890
6891 --
6892 -- Set the compiled set to history
6893 --
6894
6895 UPDATE pa_ind_compiled_sets
6896 SET status = 'H',
6897 last_update_date = SYSDATE,
6898 last_updated_by = x_last_updated_by,
6899 last_update_login = x_last_update_login,
6900 request_id = x_request_id,
6901 program_application_id = x_program_application_id,
6902 program_id = x_program_id,
6903 program_update_date = SYSDATE
6904 WHERE ind_rate_sch_revision_id = rate_sch_rev_id
6905 AND organization_id = org_id
6906 --AND cost_base = cost_base_rec.cost_base
6907 AND cost_base = G_IMPACTED_COST_BASES_TAB(i)
6908 AND status = 'A' ; /*2933915*/
6909
6910 END LOOP;
6911
6912 END IF; /*4590268*/
6913
6914 EXCEPTION
6915 WHEN OTHERS THEN
6916 stage := 100;
6917 status := SQLCODE;
6918
6919 END disable_sch_rev_org;
6920
6921
6922 procedure get_indirect_cost_amounts (x_indirect_cost_costing IN OUT NOCOPY number,
6923 x_indirect_cost_revenue IN OUT NOCOPY number,
6924 x_indirect_cost_invoice IN OUT NOCOPY number,
6925 x_task_id IN number,
6926 x_gl_date IN date,
6927 x_expenditure_type IN varchar2,
6928 x_organization_id IN number,
6929 x_direct_cost IN number,
6930 x_return_status IN OUT NOCOPY number,
6931 x_stage IN OUT NOCOPY number)
6932 is
6933 begin
6934
6935 --
6936 -- Get the costing indirect cost
6937 --
6938 pa_cost_plus.view_indirect_cost(
6939 NULL,
6940 'PA',
6941 x_task_id,
6942 x_gl_date,
6943 x_expenditure_type,
6944 x_organization_id,
6945 'C',
6946 x_direct_cost,
6947 x_indirect_cost_costing,
6948 x_return_status,
6949 x_stage);
6950
6951 /*
6952 if (x_return_status <> 0) then
6953 x_stage := x_stage + 1000;
6954 end if;
6955 */
6956
6957 if (x_return_status <> 0) then
6958 x_indirect_cost_costing := 0;
6959 end if;
6960
6961 --
6962 -- Get the revenue indirect cost
6963 --
6964 pa_cost_plus.view_indirect_cost(
6965 NULL,
6966 'PA',
6967 x_task_id,
6968 x_gl_date,
6969 x_expenditure_type,
6970 x_organization_id,
6971 'R',
6972 x_direct_cost,
6973 x_indirect_cost_revenue,
6974 x_return_status,
6975 x_stage);
6976
6977 /*
6978 if (x_return_status = NO_RATE_SCH_ID) then
6979 -- Acceptable. Reset the status
6980 x_indirect_cost_revenue := 0;
6981 x_return_status := 0;
6982 elsif (x_return_status <> 0) then
6983 x_stage := x_stage + 2000;
6984 return;
6985 end if;
6986 */
6987
6988 if (x_return_status <> 0) then
6989 x_indirect_cost_revenue := 0;
6990 end if;
6991
6992 --
6993 -- Get the invoice indirect cost
6994 --
6995 pa_cost_plus.view_indirect_cost(
6996 NULL,
6997 'PA',
6998 x_task_id,
6999 x_gl_date,
7000 x_expenditure_type,
7001 x_organization_id,
7002 'I',
7003 x_direct_cost,
7004 x_indirect_cost_invoice,
7005 x_return_status,
7006 x_stage);
7007
7008 /*
7009 if (x_return_status = NO_RATE_SCH_ID) then
7010 -- Acceptable. Reset the status
7011 x_indirect_cost_invoice := 0;
7012 x_return_status := 0;
7013 elsif (x_return_status <> 0) then
7014 x_stage := x_stage + 3000;
7015 return;
7016 end if;
7017 */
7018
7019 if (x_return_status <> 0) then
7020 x_indirect_cost_invoice := 0;
7021 end if;
7022
7023
7024 end get_indirect_cost_amounts;
7025
7026
7027
7028 procedure get_ind_rate_sch_rev(x_ind_rate_sch_name IN OUT NOCOPY varchar2,
7029 x_ind_rate_sch_revision IN OUT NOCOPY varchar2,
7030 x_ind_rate_sch_revision_type IN OUT NOCOPY varchar2,
7031 x_start_date_active IN OUT NOCOPY date,
7032 x_end_date_active IN OUT NOCOPY date,
7033 x_task_id IN number,
7034 x_gl_date IN date,
7035 x_detail_type_flag IN varchar2,
7036 x_expenditure_type IN varchar2,
7037 x_cost_base IN OUT NOCOPY varchar2,
7038 x_ind_compiled_set_id IN OUT NOCOPY number,
7039 x_organization_id IN number,
7040 x_return_status IN OUT NOCOPY number,
7041 x_stage IN OUT NOCOPY number)
7042 is
7043 x_rate_sch_rev_id number;
7044 x_sch_id number;
7045 x_sch_fixed_date date;
7046 x_cp_structure varchar2(30);
7047
7048 begin
7049
7050 x_return_status := 0;
7051 x_stage := 0;
7052
7053 pa_cost_plus.find_rate_sch_rev_id (NULL,
7054 'PA',
7055 x_task_id,
7056 x_detail_type_flag,
7057 x_gl_date,
7058 x_sch_id,
7059 x_rate_sch_rev_id,
7060 x_sch_fixed_date,
7061 x_return_status,
7062 x_stage);
7063 if (x_return_status > 0) then
7064 begin
7065 x_stage := 1;
7066 return;
7067 end;
7068 elsif (x_return_status < 0) then
7069 begin
7070 return;
7071 end;
7072 end if;
7073
7074
7075 begin
7076
7077 pa_cost_plus.get_cost_plus_structure(x_rate_sch_rev_id,
7078 x_cp_structure,
7079 x_return_status,
7080 x_stage);
7081
7082 pa_cost_plus.get_cost_base (x_expenditure_type,
7083 x_cp_structure,
7084 x_cost_base,
7085 x_return_status,
7086 x_stage);
7087 if (x_return_status > 0) then
7088 begin
7089 x_stage := 2;
7090 return;
7091 end;
7092 elsif (x_return_status < 0) then
7093 begin
7094 return;
7095 end;
7096 end if;
7097
7098 begin
7099 select ind_compiled_set_id
7100 into x_ind_compiled_set_id
7101 from pa_ind_compiled_sets
7102 where ind_rate_sch_revision_id = x_rate_sch_rev_id
7103 and organization_id = x_organization_id
7104 and status = 'A';
7105
7106 EXCEPTION
7107 WHEN NO_DATA_FOUND then
7108 x_stage := 3;
7109 x_return_status := 1;
7110 end;
7111
7112 begin
7113 select s.ind_rate_sch_name,
7114 sr.ind_rate_sch_revision,
7115 pl.meaning,
7116 sr.start_date_active,
7117 sr.end_date_active
7118 into x_ind_rate_sch_name,
7119 x_ind_rate_sch_revision,
7120 x_ind_rate_sch_revision_type,
7121 x_start_date_active,
7122 x_end_date_active
7123 from pa_ind_rate_schedules s,
7124 pa_ind_rate_sch_revisions sr,
7125 pa_lookups pl
7126 where s.ind_rate_sch_id = sr.ind_rate_sch_id
7127 and sr.ind_rate_sch_revision_type = pl.lookup_code
7128 and pl.lookup_type = 'IND RATE SCHEDULE REV TYPE'
7129 and sr.ind_rate_sch_revision_id = x_rate_sch_rev_id;
7130
7131 EXCEPTION
7132 WHEN NO_DATA_FOUND then
7133 if x_stage = 3 then
7134 x_stage := 3;
7135 else
7136 x_stage := 4;
7137 end if;
7138 x_return_status := 1;
7139 end;
7140
7141
7142 EXCEPTION
7143 WHEN NO_DATA_FOUND then
7144 x_return_status := 1;
7145
7146 WHEN OTHERS then
7147 x_return_status := SQLCODE;
7148 end;
7149
7150 end get_ind_rate_sch_rev;
7151
7152 /*
7153 Multi-Currency Related Changes:
7154 New procedure added to get the sum of the compiled multiplier.
7155 This multiplier is used to calculate the burden cost in all the currencies.
7156 */
7157
7158 PROCEDURE get_compiled_multiplier(P_Org_id IN NUMBER,
7159 P_C_base IN VARCHAR2,
7160 P_Rate_sch_rev_id IN NUMBER,
7161 P_Compiled_multiplier IN OUT NOCOPY NUMBER,
7162 P_Status IN OUT NOCOPY NUMBER,
7163 P_Stage IN OUT NOCOPY NUMBER)
7164
7165 IS
7166
7167 BEGIN
7168
7169 P_status := 0;
7170 P_stage := 100;
7171
7172 SELECT SUM(icpm.compiled_multiplier)
7173 INTO P_Compiled_multiplier
7174 FROM pa_ind_compiled_sets ics,
7175 pa_compiled_multipliers icpm
7176 WHERE ics.ind_rate_sch_revision_id = P_Rate_sch_rev_id
7177 AND ics.organization_id = P_Org_id
7178 AND ics.status = 'A'
7179 AND ics.ind_compiled_set_id = icpm.ind_compiled_set_id
7180 AND icpm.cost_base =ics.cost_base /*2933915*/
7181 AND icpm.cost_base = P_C_base;
7182
7183 if (P_compiled_multiplier is null) then
7184 P_Status := NO_DATA_FOUND_ERR;
7185 end if;
7186
7187 EXCEPTION
7188
7189 WHEN OTHERS THEN
7190 P_Status := SQLCODE;
7191
7192 END get_compiled_multiplier;
7193
7194 FUNCTION Get_Mltplr_For_Compiled_Set( P_Ind_Compiled_Set_Id IN NUMBER) RETURN NUMBER
7195 IS
7196 l_Compiled_Multiplier NUMBER;
7197 BEGIN
7198 /*
7199 * Here we sum up all the compiled multipliers against a compiled set, across
7200 * all cost-base/cost-code combinations to get the final multiplier
7201 * which can be applied on the raw-cost to get the burden cost.
7202 */
7203 SELECT SUM(icpm.compiled_multiplier)
7204 INTO l_Compiled_multiplier
7205 FROM pa_compiled_multipliers icpm
7206 WHERE icpm.ind_compiled_set_id = P_Ind_Compiled_Set_Id;
7207 /*
7208 * No Compiled Multipliers available for the compiled set, passed.
7209 * Raise no_data_found exception explicitly.
7210 */
7211 IF (l_Compiled_Multiplier IS NULL) THEN
7212 RAISE NO_DATA_FOUND;
7213 END IF;
7214 /*
7215 * Return compiled multiplier
7216 */
7217 RETURN(l_Compiled_Multiplier);
7218 EXCEPTION
7219 WHEN OTHERS THEN
7220 RAISE;
7221 END Get_Mltplr_For_Compiled_Set;
7222
7223 /************2933915 :New procedure to do Selective Deletion now *****************************************************/
7224 /*
7225 PROCEDURE
7226 delete_rate_sch_revision
7227
7228 PURPOSE
7229 The objective of this procedure is to delete only the
7230 impacted compiled sets i.e for impacted organizations and impacted cost bases ,
7231 for which no ei exists . */
7232 /***********************************************************************************************************************/
7233 procedure delete_rate_sch_revision(rate_sch_rev_id IN number,
7234 ver_id IN number,
7235 org_id IN number,
7236 status IN OUT NOCOPY number,
7237 stage IN OUT NOCOPY number)
7238
7239 is
7240 CURSOR org_cursor
7241 IS
7242 SELECT organization_id_child org_id_child
7243 FROM per_org_structure_elements
7244 CONNECT BY PRIOR organization_id_child = organization_id_parent
7245 AND org_structure_version_id = ver_id
7246 START WITH organization_id_parent = org_id
7247 AND org_structure_version_id = ver_id
7248 UNION
7249 select org_id from dual ;
7250
7251 BEGIN
7252 --
7253 -- Remove redundant compiled sets and multipiers.
7254 --
7255 FOR ORG_REC in ORG_CURSOR
7256 LOOP
7257 FOR cost_base_rec in impacted_cost_bases(rate_sch_rev_id)
7258 LOOP
7259
7260 /* S.N. Bug 3946409
7261
7262 DELETE pa_compiled_multipliers
7263 WHERE ind_compiled_set_id IN
7264 (SELECT ind_compiled_set_id
7265 FROM pa_ind_compiled_sets
7266 WHERE ind_rate_sch_revision_id = rate_sch_rev_id
7267 and organization_id = ORG_REC.org_id_child
7268 and cost_base = cost_base_rec.cost_base) ;
7269
7270 E.N. Bug 3946409 */
7271
7272 /* Bug# 4527736
7273 DELETE pa_ind_compiled_sets
7274 WHERE ind_rate_sch_revision_id = rate_sch_rev_id
7275 and organization_id =ORG_REC.org_id_child
7276 and cost_base =G_IMPACTED_COST_BASES_TAB(i)
7277 Bug# 4527736 */
7278 NULL;
7279
7280 END LOOP ;
7281 END LOOP ;
7282 EXCEPTION
7283 WHEN OTHERS THEN
7284 stage := 100;
7285 status := SQLCODE;
7286
7287 END delete_rate_sch_revision;
7288
7289
7290 /*****************2933915 :New procedure to find the impacted top orgs******************************************
7291
7292 PROCEDURE
7293 find_impacted_top_org
7294
7295 PURPOSE
7296 The objective of this procedure is to find the highest
7297 organizations with ready_to_compile_flag as 'Y'
7298 in pa_ind_cost_multipliers .
7299 This is to ensure that compilation starts from highest impacted orgs rather than from Start_org
7300 ***************************************************************************************************************/
7301
7302 procedure find_impacted_top_org(rate_sch_rev_id IN number,
7303 ver_id IN number ,
7304 start_org IN number ,
7305 org_tab OUT NOCOPY org_tab_type,
7306 status IN OUT NOCOPY number)
7307
7308 is
7309
7310 /*Cursor to select distinct organizations having ready_to_compile_flag as 'Y' */
7311 Cursor ready_to_compile_orgs is
7312 select DISTINCT organization_id
7313 from pa_ind_cost_multipliers
7314 where ind_rate_sch_revision_id = rate_sch_rev_id
7315 and nvl(ready_to_compile_flag,'N') in ('Y','X') ;
7316
7317 l_count NUMBER ;
7318 l_parent NUMBER ;
7319 i NUMBER :=1 ;
7320
7321 BEGIN
7322
7323 FOR org in ready_to_compile_orgs LOOP
7324 BEGIN
7325
7326 SELECT count(a.organization_id_parent)
7327 into l_count FROM
7328 ( SELECT organization_id_parent
7329 FROM per_org_structure_elements
7330 CONNECT BY PRIOR organization_id_parent = organization_id_child
7331 AND org_structure_version_id = ver_id
7332 START WITH organization_id_child = org.organization_id
7333 AND org_structure_version_id = ver_id) a
7334 WHERE a.organization_id_parent in (select DISTINCT organization_id
7335 from pa_ind_cost_multipliers
7336 where ind_rate_sch_revision_id = rate_sch_rev_id
7337 and nvl(ready_to_compile_flag,'N') in ('Y','X') );
7338
7339
7340 if (l_count =0 ) then
7341
7342 l_parent := org.organization_id ;
7343
7344 /*There will not be many records in this table since we are storing only the top impacted org in this after
7345 full traversal of one branch(starting from start org till last child i.e leaf node) -so looping through the
7346 table should be OK */
7347
7348 org_tab(i) := l_parent;
7349
7350
7351
7352 /* If at any point of finding top org we come across any org that is same as start org then no need to
7353 process remaining orgs in cursor ready_to_compile_orgs since we have reached start org so that means
7354 compilation has to start from stat org itself. */
7355
7356 If l_parent = start_org then
7357 status :=0 ;
7358 EXIT ;
7359 End if ;
7360 i:=i +1 ;
7361 elsif (l_count =1 ) then
7362
7363 SELECT b.organization_id_parent
7364 into l_parent
7365 FROM (SELECT organization_id_parent
7366 FROM per_org_structure_elements
7367 CONNECT BY PRIOR organization_id_parent = organization_id_child
7368 AND org_structure_version_id = ver_id
7369 START WITH organization_id_child = org.organization_id
7370 AND org_structure_version_id = ver_id) b
7371 WHERE b.organization_id_parent in (select DISTINCT organization_id
7372 from pa_ind_cost_multipliers
7373 where ind_rate_sch_revision_id = rate_sch_rev_id
7374 and nvl(ready_to_compile_flag,'N') in ('Y','X'));
7375
7376 /*There will not be many records in this table since we are storing only the top impacted org in this after full traversal of one
7377 branch (starting from start org till last child i.e leaf node) -so looping through the table should be OK */
7378
7379
7380 org_tab(i) := l_parent;
7381
7382
7383 /* If at any point of finding top org we come across any org that is same as start org then no need to process
7384 remaining orgs in cursor ready_to_compile_orgs since we have reached start org so that means compilation has to start
7385 from stat org itself. */
7386
7387 If l_parent = start_org then
7388 status :=0 ;
7389 EXIT ;
7390 End if ;
7391
7392 i:=i+1 ;
7393 End if ;
7394
7395
7396 EXCEPTION
7397 WHEN OTHERS THEN
7398 status := SQLCODE;
7399 END ;
7400 END LOOP ;
7401 END find_impacted_top_org ;
7402
7403 /*3016281 :Added this new function to check (and return 1 else 0 ) if explicit multipliers are defined for all the
7404 cost codes in impacted cost bases for an org .
7405 This is to implement the functionality that in the above mentioned case compiled set ids should neither be obsoleted
7406 nor generated for an org. */
7407
7408 FUNCTION check_for_explicit_multiplier(rate_sch_rev_id IN NUMBER,org_id IN NUMBER) RETURN NUMBER
7409 IS
7410 org_override NUMBER :=0;
7411 l_org_id_parent NUMBER(15) DEFAULT 0; /* Bug 4739218 */
7412
7413 /*
7414 * Repalced with the below sql. Hari 19-JUL-05.
7415 CURSOR impacted_cost_code_cur(x_base VARCHAR2) **Cursor for all the cost codes of impacted cost bases**
7416 IS
7417 SELECT distinct cbicc.ind_cost_code
7418 FROM pa_cost_base_cost_codes cbicc,
7419 pa_ind_rate_sch_revisions irsr
7420 WHERE irsr.ind_rate_sch_revision_id = rate_sch_rev_id
7421 AND irsr.cost_plus_structure = cbicc.cost_plus_structure
7422 AND cbicc.cost_base = x_base
7423 AND cbicc.cost_base_type = INDIRECT_COST_CODE ;
7424 */
7425 CURSOR impacted_cost_code_cur(x_base VARCHAR2) /*Cursor for all the cost codes of impacted cost bases*/
7426 IS
7427 SELECT distinct cbicc.ind_cost_code
7428 FROM pa_cost_base_cost_codes cbicc
7429 WHERE cbicc.cost_plus_structure = G_CP_STRUCTURE
7430 AND cbicc.cost_base = x_base
7431 AND cbicc.cost_base_type = INDIRECT_COST_CODE ;
7432
7433 BEGIN
7434
7435 /* S.N. Bug 3938479 */
7436 IF rate_sch_rev_id = g_rate_sch_rev_id AND org_id = g_org_id Then
7437
7438 org_override := g_org_override;
7439 RETURN org_override;
7440
7441 ELSE
7442
7443 g_rate_sch_rev_id := rate_sch_rev_id;
7444 g_org_id := org_id ;
7445
7446 /* E.N. Bug 3938479 */
7447
7448
7449 /*FOR cost_base_rec in impacted_cost_bases(rate_sch_rev_id) **Loop for impacted cost bases*/
7450 IF G_IMPACTED_COST_BASES_TAB.COUNT <> 0 THEN /*4590268*/
7451
7452 FOR i in G_IMPACTED_COST_BASES_TAB.FIRST .. G_IMPACTED_COST_BASES_TAB.LAST
7453 LOOP
7454 --FOR cost_code_rec in impacted_cost_code_cur(cost_base_rec.cost_base)
7455 FOR cost_code_rec in impacted_cost_code_cur(G_IMPACTED_COST_BASES_TAB(i))
7456
7457 LOOP
7458 BEGIN
7459 select 1 into org_override
7460 from sys.dual
7461 where exists(select 1
7462 from pa_ind_cost_multipliers icm ,
7463 pa_ind_compiled_sets ics
7464 where icm.ind_rate_sch_revision_id =ics.ind_rate_sch_revision_id
7465 and icm.ind_rate_sch_revision_id = rate_sch_rev_id
7466 AND icm.organization_id =ics.organization_id
7467 and icm.organization_id = org_id
7468 --AND ics.cost_base = cost_base_rec.cost_base
7469 AND ics.cost_base = G_IMPACTED_COST_BASES_TAB(i)
7470 AND ics.status ='A'
7471 and icm.ind_cost_code = cost_code_rec.ind_cost_code
7472 and nvl(icm.ready_to_compile_flag,'N') ='N');
7473 /*Ready_to_compile_flag 'N' means unchanged so will check for this only*/
7474 EXCEPTION
7475 WHEN NO_DATA_FOUND THEN
7476 BEGIN /* Code change for Bug 4739218 Starts */
7477 select 0 into org_override
7478 from sys.dual
7479 where exists
7480 (
7481 select 1
7482 from pa_ind_cost_multipliers icm ,
7483 pa_ind_compiled_sets ics
7484 where icm.ind_rate_sch_revision_id =ics.ind_rate_sch_revision_id
7485 and icm.ind_rate_sch_revision_id = rate_sch_rev_id
7486 and icm.organization_id =ics.organization_id
7487 and icm.organization_id = org_id
7488 and nvl(icm.ready_to_compile_flag,'N') <> 'N'
7489 );
7490 /*If explicit multipliers exists and got changed then org_overrride = 0 */
7491 EXIT;
7492 EXCEPTION
7493 WHEN NO_DATA_FOUND THEN
7494 BEGIN
7495 SELECT organization_id_parent into l_org_id_parent
7496 FROM per_org_structure_elements
7497 WHERE organization_id_child = org_id
7498 AND org_structure_version_id = G_ORG_STRUC_VER_ID;
7499 org_override := check_for_explicit_multiplier(rate_sch_rev_id, l_org_id_parent);
7500 return org_override;
7501 EXCEPTION
7502 WHEN NO_DATA_FOUND THEN
7503 org_override := 1 ;/* This is the start_org and explicit multiplier are not defined */
7504 return org_override;
7505 WHEN OTHERS THEN
7506 RAISE;
7507 END;
7508 END; /*Code change for Bug 4739218 Ends */
7509 END;
7510 END LOOP;
7511
7512 If org_override =0 THEN
7513 EXIT;
7514 END IF;
7515 END LOOP;
7516 END IF; -- G_IMPACTED_COST_BASES_TAB.COUNT <> 0 THEN /*4590268*/
7517
7518 END IF ;/* Bug 3938479 */
7519
7520 g_org_override := org_override; /* Bug 3938479 */
7521 RETURN org_override;
7522
7523 EXCEPTION
7524 WHEN OTHERS THEN
7525 RAISE;
7526 END check_for_explicit_multiplier ; /*3012681*/
7527
7528 PROCEDURE Cache_Impacted_Cost_Bases ( P_Ind_Rate_Sch_Revision_Id IN PA_IND_RATE_SCH_REVISIONS.IND_RATE_SCH_REVISION_ID%TYPE
7529 ,P_Cp_Structure IN PA_COST_PLUS_STRUCTURES.COST_PLUS_STRUCTURE%TYPE
7530 )
7531 IS
7532 CURSOR impacted_cost_bases_cur( P_Ind_Rate_Sch_Revision_Id PA_IND_RATE_SCH_REVISIONS.IND_RATE_SCH_REVISION_ID%TYPE
7533 ,P_Cp_Structure PA_COST_PLUS_STRUCTURES.COST_PLUS_STRUCTURE%TYPE
7534 )
7535 IS
7536 SELECT pcb.COST_BASE
7537 FROM PA_COST_BASES pcb
7538 WHERE pcb.COST_BASE_TYPE = INDIRECT_COST_CODE
7539 AND nvl(G_MODULE ,'XXX') <> 'NEW_ORG' /*4870539*/
7540 AND EXISTS
7541 (
7542 SELECT 1
7543 FROM PA_COST_BASE_COST_CODES CBICC,
7544 PA_IND_COST_MULTIPLIERS ICM
7545 WHERE ICM.IND_RATE_SCH_REVISION_ID = P_Ind_Rate_Sch_Revision_Id
7546 AND (NVL(ICM.READY_TO_COMPILE_FLAG,'N') IN ('Y','X') AND NVL(G_MODULE ,'XXX') <> 'NEW_ORG')/*4870539*/
7547 AND CBICC.COST_PLUS_STRUCTURE = P_Cp_Structure
7548 AND CBICC.IND_COST_CODE = ICM.IND_COST_CODE
7549 AND CBICC.COST_BASE = PCB.COST_BASE
7550 AND CBICC.COST_BASE_TYPE = PCB.COST_BASE_TYPE )
7551 UNION /*4870539 :Added union*/
7552 SELECT pcb.COST_BASE
7553 FROM PA_COST_BASES pcb
7554 WHERE pcb.COST_BASE_TYPE = INDIRECT_COST_CODE
7555 AND nvl(G_MODULE ,'XXX') = 'NEW_ORG'
7556 AND EXISTS
7557 (
7558 SELECT 1
7559 FROM PA_COST_BASE_COST_CODES CBICC,
7560 PA_IND_rate_sch_revisions IRSR
7561 WHERE IRSR.IND_RATE_SCH_REVISION_ID = P_Ind_Rate_Sch_Revision_Id
7562 AND nvl(G_MODULE ,'XXX') = 'NEW_ORG'
7563 AND IRSR.COST_PLUS_STRUCTURE= CBICC.COST_PLUS_STRUCTURE
7564 AND CBICC.COST_PLUS_STRUCTURE = P_Cp_Structure
7565 AND CBICC.COST_BASE = PCB.COST_BASE
7566 AND CBICC.COST_BASE_TYPE = PCB.COST_BASE_TYPE ); /*End of changes for 4870539*/
7567 BEGIN
7568
7569 G_IMPACTED_COST_BASES_TAB.DELETE;
7570 OPEN impacted_cost_bases_cur (P_Ind_Rate_Sch_Revision_Id,P_Cp_Structure);
7571 FETCH impacted_cost_bases_cur BULK COLLECT INTO G_IMPACTED_COST_BASES_TAB;
7572 CLOSE impacted_cost_bases_cur;
7573
7574 END Cache_Impacted_Cost_Bases;
7575
7576 end PA_COST_PLUS ;
[Home] [Help]