1 PACKAGE BODY PA_RES_ACCUMS AS
2 /* $Header: PARESACB.pls 120.3.12010000.2 2008/10/14 09:06:17 sugupta ship $ */
3
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
5
6
7 -- Initialize function
8
9 FUNCTION initialize RETURN NUMBER IS
10 x_err_code NUMBER:=0;
11 BEGIN
12
13 RETURN 0;
14 EXCEPTION
15 WHEN OTHERS THEN
16 x_err_code := SQLCODE;
17 RETURN x_err_code;
18 END initialize;
19
20
21
22 PROCEDURE get_resource_map
23 (x_resource_list_id IN NUMBER,
24 x_resource_list_assignment_id IN NUMBER,
25 x_person_id IN NUMBER,
26 x_job_id IN NUMBER,
27 x_organization_id IN NUMBER,
28 x_vendor_id IN NUMBER,
29 x_expenditure_type IN VARCHAR2,
30 x_event_type IN VARCHAR2,
31 x_non_labor_resource IN VARCHAR2,
32 x_expenditure_category IN VARCHAR2,
33 x_revenue_category IN VARCHAR2,
34 x_non_labor_resource_org_id IN NUMBER,
35 x_event_type_classification IN VARCHAR2,
36 x_system_linkage_function IN VARCHAR2,
37 x_resource_list_member_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
38 x_resource_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
39 x_resource_map_found IN OUT NOCOPY BOOLEAN, --File.Sql.39 bug 4440895
40 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
41 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
42 IS
43 BEGIN
44
45 x_err_code := 0;
46 x_err_stage := 'Getting the resource map';
47 x_resource_map_found := TRUE;
48 x_resource_list_member_id := NULL;
49 x_resource_id := NULL;
50
51 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
52 pa_debug.debug('old_map_txns: ' || x_err_stage);
53 END IF;
54
55 /* Seperating Map Check for Expenditures based/Event based Txns */
56
57 IF (x_expenditure_type IS NOT NULL) THEN
58
59 -- Process records differently based on the person_id is null/not null
60 -- to take advantage of the index on person_id column
61
62 IF ( x_person_id IS NOT NULL ) THEN
63 -- person_id is not null
64 SELECT
65 resource_list_member_id,
66 resource_id
67 INTO
68 x_resource_list_member_id,
69 x_resource_id
70 FROM
71 pa_resource_maps prm
72 WHERE
73 prm.resource_list_assignment_id = x_resource_list_assignment_id
74 AND prm.resource_list_id = x_resource_list_id
75 AND prm.expenditure_type = x_expenditure_type
76 AND prm.organization_id = x_organization_id
77 AND prm.person_id = x_person_id
78 AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
79 AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
80 AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
81 AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
82 AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
83 AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
84 AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X');
85 ELSE
86 -- person_id is null
87 SELECT
88 resource_list_member_id,
89 resource_id
90 INTO
91 x_resource_list_member_id,
92 x_resource_id
93 FROM
94 pa_resource_maps prm
95 WHERE
96 prm.resource_list_assignment_id = x_resource_list_assignment_id
97 AND prm.resource_list_id = x_resource_list_id
98 AND prm.expenditure_type = x_expenditure_type
99 AND prm.organization_id = x_organization_id
100 AND prm.person_id IS NULL
101 AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
102 AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
103 AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
104 AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
105 AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
106 AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
107 AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X');
108 END IF; -- IF ( x_person_id IS NOT NULL )
109 ELSE
110 /* Events */
111 SELECT
112 resource_list_member_id,
113 resource_id
114 INTO
115 x_resource_list_member_id,
116 x_resource_id
117 FROM
118 pa_resource_maps prm
119 WHERE
120 prm.resource_list_assignment_id = x_resource_list_assignment_id
121 AND prm.resource_list_id = x_resource_list_id
122 AND prm.event_type = x_event_type
123 AND prm.organization_id = x_organization_id
124 AND prm.revenue_category = x_revenue_category
125 AND prm.event_type_classification = x_event_type_classification;
126
127 END IF; --IF (x_expenditure_type IS NOT NULL)
128
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 x_resource_map_found := FALSE;
132 WHEN OTHERS THEN
133 x_err_code := SQLCODE;
134 RAISE;
135 END get_resource_map;
136
137 PROCEDURE get_resource_map_new
138 (x_resource_list_id IN NUMBER,
139 x_person_id IN NUMBER,
140 x_job_id IN NUMBER,
141 x_organization_id IN NUMBER,
142 x_vendor_id IN NUMBER,
143 x_expenditure_type IN VARCHAR2,
144 x_event_type IN VARCHAR2,
145 x_non_labor_resource IN VARCHAR2,
146 x_expenditure_category IN VARCHAR2,
147 x_revenue_category IN VARCHAR2,
148 x_non_labor_resource_org_id IN NUMBER,
149 x_event_type_classification IN VARCHAR2,
150 x_system_linkage_function IN VARCHAR2,
151 x_resource_list_member_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
152 x_resource_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
153 x_resource_map_found IN OUT NOCOPY BOOLEAN, --File.Sql.39 bug 4440895
154 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
155 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
156 IS
157 BEGIN
158
159 x_err_code := 0;
160 x_err_stage := 'Getting the resource map';
161 x_resource_map_found := TRUE;
162 x_resource_list_member_id := NULL;
163 x_resource_id := NULL;
164
165 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
166 pa_debug.debug('old_map_txns: ' || x_err_stage);
167 END IF;
168
169 /* Seperating Map Check for Expenditures based/Event based Txns */
170
171 IF (x_expenditure_type IS NOT NULL) THEN
172
173 -- Process records differently based on the person_id is null/not null
174 -- to take advantage of the index on person_id column
175
176 IF ( x_person_id IS NOT NULL ) THEN
177 -- person_id is not null
178 SELECT
179 resource_list_member_id,
180 resource_id
181 INTO
182 x_resource_list_member_id,
183 x_resource_id
184 FROM
185 pa_resource_maps prm,
186 pa_resource_list_assignments parla
187 WHERE
188 prm.resource_list_id = x_resource_list_id
189 AND prm.expenditure_type = x_expenditure_type
190 AND prm.organization_id = x_organization_id
191 AND prm.person_id = x_person_id
192 AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
193 AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
194 AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
195 AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
196 AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
197 AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
198 AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X')
199 AND prm.resource_list_assignment_id = parla.resource_list_assignment_id
200 AND NVL(parla.resource_list_changed_flag,'N') <> 'Y'
201 AND rownum < 2;
202 ELSE
203 -- person_id is null
204 SELECT
205 resource_list_member_id,
206 resource_id
207 INTO
208 x_resource_list_member_id,
209 x_resource_id
210 FROM
211 pa_resource_maps prm,
212 pa_resource_list_assignments parla
213 WHERE
214 prm.resource_list_id = x_resource_list_id
215 AND prm.expenditure_type = x_expenditure_type
216 AND prm.organization_id = x_organization_id
217 AND prm.person_id IS NULL
218 AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
219 AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
220 AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
221 AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
222 AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
223 AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
224 AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X')
225 AND prm.resource_list_assignment_id = parla.resource_list_assignment_id
226 AND NVL(parla.resource_list_changed_flag,'N') <> 'Y'
227 AND rownum < 2;
228 END IF; -- IF ( x_person_id IS NOT NULL )
229 ELSE
230 /* Events */
231 SELECT
232 resource_list_member_id,
233 resource_id
234 INTO
235 x_resource_list_member_id,
236 x_resource_id
237 FROM
238 pa_resource_maps prm,
239 pa_resource_list_assignments parla
240 WHERE
241 prm.resource_list_id = x_resource_list_id
242 AND prm.event_type = x_event_type
243 AND prm.organization_id = x_organization_id
244 AND prm.revenue_category = x_revenue_category
245 AND prm.event_type_classification = x_event_type_classification
246 AND prm.resource_list_assignment_id = parla.resource_list_assignment_id
247 AND NVL(parla.resource_list_changed_flag,'N') <> 'Y'
248 AND rownum < 2;
249
250 END IF; --IF (x_expenditure_type IS NOT NULL)
251
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 x_resource_map_found := FALSE;
255 WHEN OTHERS THEN
256 x_err_code := SQLCODE;
257 RAISE;
258 END get_resource_map_new;
259
260 -- deleting the resource maps for the given resource list assignment id
261
262 PROCEDURE delete_res_maps_on_asgn_id
263 (x_resource_list_assignment_id IN NUMBER,
264 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
265 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
266 IS
267 tot_recs_processed number(15):=0;
268 BEGIN
269 /* Commented for bug# 1889671
270 x_err_code := 0;
271 x_err_stage := 'Deleting the resource map for given resource list assignment id';
272
273
274 pa_debug.debug('old_map_txns: ' || x_err_stage);
275
276 Loop
277 IF (x_resource_list_assignment_id is null) THEN
278 DELETE
279 pa_resource_maps where rownum <= pa_proj_accum_main.x_commit_size;
280 ELSE
281 DELETE
282 pa_resource_maps prm
283 WHERE
284 prm.resource_list_assignment_id = x_resource_list_assignment_id
285 and rownum <= pa_proj_accum_main.x_commit_size;
286 END IF;
287 if sql%rowcount < pa_proj_accum_main.x_commit_size then
288 Commit;
289 tot_recs_processed := tot_recs_processed + sql%rowcount;
290 exit;
291 else
292 commit;
293 tot_recs_processed := tot_recs_processed + sql%rowcount;
294 end if;
295 End loop;
296
297
298 pa_debug.debug('old_map_txns: ' || 'Numbers of Records Deleted = ' || TO_CHAR(tot_recs_processed));
299
300 Completely commented for bug# 1889671 */
301 Null;
302
303 EXCEPTION
304 WHEN NO_DATA_FOUND THEN
305 NULL;
306 WHEN OTHERS THEN
307 x_err_code := SQLCODE;
308 RAISE;
309 END delete_res_maps_on_asgn_id;
310
311 -- deleting the resource maps for the given project_id and
312 -- resource_list_id
313
314 PROCEDURE delete_res_maps_on_prj_id
315 (x_project_id IN NUMBER,
316 x_resource_list_id IN NUMBER,
317 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
318 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
319 IS
320 tot_recs_processed number(15):=0;
321 BEGIN
322
323 /* Commented for bug# 1889671
324 x_err_code := 0;
325 x_err_stage := 'Deleting the resource map for given project Id';
326
327
328 pa_debug.debug('old_map_txns: ' || x_err_stage);
329
330 LOOP
331
332 DELETE
333 pa_resource_maps prm
334 WHERE
335 prm.resource_list_assignment_id IN
336 ( SELECT
337 resource_list_assignment_id
338 FROM
339 pa_resource_list_assignments
340 WHERE project_id = x_project_id
341 AND resource_list_id = NVL(x_resource_list_id,resource_list_id)
342 )
343 and rownum <= pa_proj_accum_main.x_commit_size;
344 if sql%rowcount < pa_proj_accum_main.x_commit_size then
345 Commit;
346 tot_recs_processed := tot_recs_processed + sql%rowcount;
347 exit;
348 else
349 commit;
350 tot_recs_processed := tot_recs_processed + sql%rowcount;
351 end if;
352 End loop;
353
354
355 pa_debug.debug('old_map_txns: ' || 'Numbers of Records Deleted = ' || TO_CHAR(tot_recs_processed));
356
357 Completely commented for bug# 1889671 */
358 Null;
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 NULL;
362 WHEN OTHERS THEN
363 x_err_code := SQLCODE;
364 RAISE;
365 END delete_res_maps_on_prj_id;
366
367 -- the function given below creates a resource map
368
369 PROCEDURE create_resource_map
370 (x_resource_list_id IN NUMBER,
371 x_resource_list_assignment_id IN NUMBER,
372 x_resource_list_member_id IN NUMBER,
373 x_resource_id IN NUMBER,
374 x_person_id IN NUMBER,
375 x_job_id IN NUMBER,
376 x_organization_id IN NUMBER,
377 x_vendor_id IN NUMBER,
378 x_expenditure_type IN VARCHAR2,
379 x_event_type IN VARCHAR2,
380 x_non_labor_resource IN VARCHAR2,
381 x_expenditure_category IN VARCHAR2,
382 x_revenue_category IN VARCHAR2,
383 x_non_labor_resource_org_id IN NUMBER,
384 x_event_type_classification IN VARCHAR2,
385 x_system_linkage_function IN VARCHAR2,
386 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
387 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
388 IS
389 BEGIN
390
391 x_err_code :=0;
392 x_err_stage := 'Creating resource map';
393
394 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
395 pa_debug.debug('old_map_txns: ' || x_err_stage);
396 END IF;
397
398 INSERT INTO pa_resource_maps
399 (resource_list_id,
400 resource_list_assignment_id,
401 resource_list_member_id,
402 resource_id,
403 person_id,
404 job_id,
405 organization_id,
406 vendor_id,
407 expenditure_type,
408 event_type,
409 non_labor_resource,
410 expenditure_category,
411 revenue_category,
412 non_labor_resource_org_id,
413 event_type_classification,
414 system_linkage_function,
415 creation_date,
416 created_by,
417 last_updated_by,
418 last_update_date,
419 last_update_login,
420 request_id,
421 program_application_id,
422 program_id)
423 VALUES
424 (x_resource_list_id,
425 x_resource_list_assignment_id,
426 x_resource_list_member_id,
427 x_resource_id,
428 x_person_id,
429 x_job_id,
430 x_organization_id,
431 x_vendor_id,
432 x_expenditure_type,
433 x_event_type,
434 x_non_labor_resource,
435 x_expenditure_category,
436 x_revenue_category,
437 x_non_labor_resource_org_id,
438 x_event_type_classification,
439 x_system_linkage_function,
440 SYSDATE,
441 x_created_by,
442 x_last_updated_by,
443 SYSDATE,
444 x_last_update_login,
445 x_request_id,
446 x_program_application_id,
447 x_program_id);
448
449 EXCEPTION
450 WHEN OTHERS THEN
451 x_err_code := SQLCODE;
452 RAISE;
453 END create_resource_map;
454
455 -- change resource list assignment
456
457 PROCEDURE change_resource_list_status
458 (x_resource_list_assignment_id IN NUMBER,
459 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
460 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
461 IS
462 BEGIN
463
464 x_err_code := 0;
465 x_err_stage := 'Updating resource list assignment status';
466
467 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
468 pa_debug.debug('old_map_txns: ' || x_err_stage);
469 END IF;
470
471 UPDATE
472 pa_resource_list_assignments
473 SET
474 resource_list_changed_flag ='N'
475 WHERE
476 resource_list_assignment_id = x_resource_list_assignment_id;
477
478 EXCEPTION
479 WHEN OTHERS THEN
480 x_err_code := SQLCODE;
481 RAISE;
482 END change_resource_list_status;
483
484 FUNCTION get_resource_list_status
485 (x_resource_list_assignment_id IN NUMBER)
486 RETURN VARCHAR2
487 IS
488 x_resource_list_changed_flag VARCHAR2(1);
489 BEGIN
490
491 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
492 pa_debug.debug('old_map_txns: ' || 'Getting Resource List Status');
493 END IF;
494
495 SELECT
496 NVL(resource_list_changed_flag,'N')
497 INTO
498 x_resource_list_changed_flag
499 FROM
500 pa_resource_list_assignments
501 WHERE
502 resource_list_assignment_id = x_resource_list_assignment_id;
503
504 RETURN x_resource_list_changed_flag;
505
506 EXCEPTION
507 WHEN OTHERS THEN
508 RETURN NULL;
509 END get_resource_list_status;
510
511 -- Get the resource Rank
512
513
514 -- If we donot find a rank for a given format and class code then
515 -- no resource mapping will be done against that resource
516
517 FUNCTION get_resource_rank
518 (x_resource_format_id IN NUMBER,
519 x_txn_class_code IN VARCHAR2)
520 RETURN NUMBER
521 IS
522 x_rank NUMBER;
523 BEGIN
524
525 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
526 pa_debug.debug('old_map_txns: ' || 'Getting Resource Rank');
527 END IF;
528
529 SELECT
530 rank
531 INTO
532 x_rank
533 FROM
534 pa_resource_format_ranks
535 WHERE
536 resource_format_id = x_resource_format_id
537 AND txn_class_code = x_txn_class_code;
538
539 RETURN x_rank;
540
541 EXCEPTION
542 WHEN NO_DATA_FOUND THEN
543 RETURN NULL;
544 WHEN OTHERS THEN
545 RETURN NULL;
546 END get_resource_rank;
547
548 -- This function returns the group resource_type_code for the given resoure list
549 -- In case of 'None' Group Resource type, the table pa_resource_lists_all_bg
550 -- will not join to the pa_resource_types table
551
552 FUNCTION get_group_resource_type_code
553 (x_resource_list_id IN NUMBER)
554 RETURN VARCHAR2
555 IS
556 x_group_resource_type_code VARCHAR2(20);
557 BEGIN
558
559 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
560 pa_debug.debug('old_map_txns: ' || 'Getting Resource Type Code');
561 END IF;
562
563 SELECT
564 rt.resource_type_code
565 INTO
566 x_group_resource_type_code
567 FROM
568 pa_resource_types rt,
569 pa_resource_lists_all_bg rl
570 WHERE
571 rl.resource_list_id = x_resource_list_id
572 and nvl(rl.migration_code,'-99') <> 'N'
573 AND rl.group_resource_type_id = rt.resource_type_id
574 ;
575
576 RETURN x_group_resource_type_code;
577
578 EXCEPTION
579 WHEN NO_DATA_FOUND THEN
580 x_group_resource_type_code := 'NONE';
581 RETURN x_group_resource_type_code;
582 WHEN OTHERS THEN
583 RETURN NULL;
584 END get_group_resource_type_code;
585
586 -- This procedure created resource accum details
587 -- We will not allow to have multiple PA_RESOURCE_ACCUM_DETAILS
588 -- for the same TXN_ACCUM_ID and different resource_id and
589 -- pa_resource_list_member_id
590
591 PROCEDURE create_resource_accum_details
592 (x_resource_list_id IN NUMBER,
593 x_resource_list_assignment_id IN NUMBER,
594 x_resource_list_member_id IN NUMBER,
595 x_resource_id IN NUMBER,
596 x_txn_accum_id IN NUMBER,
597 x_project_id IN NUMBER,
598 x_task_id IN NUMBER,
599 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
600 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
601 IS
602 BEGIN
603
604 x_err_code :=0;
605 x_err_stage := 'Creating resource accum details';
606
607 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
608 pa_debug.debug('old_map_txns: ' || x_err_stage);
609 END IF;
610
611 INSERT INTO pa_resource_accum_details
612 (resource_list_id,
613 resource_list_assignment_id,
614 resource_list_member_id,
615 resource_id,
616 txn_accum_id,
617 project_id,
618 task_id,
619 creation_date,
620 created_by,
621 last_updated_by,
622 last_update_date,
623 last_update_login,
624 request_id,
625 program_application_id,
626 program_id)
627 SELECT
628 x_resource_list_id,
629 x_resource_list_assignment_id,
630 x_resource_list_member_id,
631 x_resource_id,
632 x_txn_accum_id,
633 x_project_id,
634 x_task_id,
635 SYSDATE,
636 x_created_by,
637 x_last_updated_by,
638 SYSDATE,
639 x_last_update_login,
640 x_request_id,
641 x_program_application_id,
642 x_program_id
643 FROM
644 sys.dual
645 WHERE NOT EXISTS
646 (SELECT
647 'Yes'
648 FROM
649 pa_resource_accum_details rad
650 WHERE
651 resource_list_id = x_resource_list_id
652 AND resource_list_assignment_id = x_resource_list_assignment_id
653 /*
654 AND resource_list_member_id = x_resource_list_member_id
655 AND resource_id = x_resource_id
656 */
657 AND txn_accum_id = x_txn_accum_id
658 AND project_id = x_project_id
659 AND task_id = x_task_id
660 );
661
662 EXCEPTION
663 WHEN NO_DATA_FOUND THEN
664 NULL;
665 WHEN OTHERS THEN
666 x_err_code := SQLCODE;
667 RAISE;
668 END create_resource_accum_details;
669
670 -- This procedure deleted resource accum details
671
672 PROCEDURE delete_resource_accum_details
673 (x_resource_list_assignment_id IN NUMBER,
674 x_resource_list_id IN NUMBER,
675 x_project_id IN NUMBER,
676 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
677 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
678 IS
679 tot_recs_processed number(15):=0;
680 BEGIN
681
682 x_err_code :=0;
683 x_err_stage := 'Deleting resource accum details';
684
685 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
686 pa_debug.debug('old_map_txns: ' || x_err_stage);
687 END IF;
688
689 LOOP
690 IF (x_resource_list_id IS NULL) THEN
691 DELETE
692 pa_resource_accum_details
693 WHERE
694 resource_list_assignment_id =
695 NVL(x_resource_list_assignment_id,resource_list_assignment_id)
696 AND project_id = x_project_id
697 and rownum <= pa_proj_accum_main.x_commit_size;
698 ELSE
699
700 DELETE
701 pa_resource_accum_details
702 WHERE
703 resource_list_assignment_id =
704 NVL(x_resource_list_assignment_id,resource_list_assignment_id)
705 AND resource_list_id = x_resource_list_id
706 AND project_id = x_project_id
707 and rownum <= pa_proj_accum_main.x_commit_size;
708
709 END IF;
710 if sql%rowcount < pa_proj_accum_main.x_commit_size then
711 /* Commented for Bug 2984871 Commit; */
712 tot_recs_processed := tot_recs_processed + sql%rowcount;
713 /*Code Changes for Bug No.2984871 start */
714 Commit;
715 /*Code Changes for Bug No.2984871 end */
716 exit;
717 else
718 /* Commented for Bug 2984871 Commit; */
719 tot_recs_processed := tot_recs_processed + sql%rowcount;
720 /*Code Changes for Bug No.2984871 start */
721 Commit;
722 /*Code Changes for Bug No.2984871 end */
723 end if;
724 End loop;
725
726 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
727 pa_debug.debug('old_map_txns: ' || 'Numbers of Records Deleted = ' || TO_CHAR(tot_recs_processed));
728 END IF;
729
730 EXCEPTION
731 WHEN NO_DATA_FOUND THEN
732 NULL;
733 WHEN OTHERS THEN
734 x_err_code := SQLCODE;
735 RAISE;
736 END delete_resource_accum_details;
737
738 -- This procedure will return the resource and its attributes for the
739 -- given project_id. It will return the group level resource for
740 -- the resources for which no child resource exists and for the
741 -- group if child exists then it will return only the childs
742 -- please note that outer join is done for pa_resources to pa_resource_txn_attributes
743 -- because some of the resource may not have attributes
744
745 -- x_resource_list_id IN OUT resource_list_id_tabtype,
746 -- x_resource_list_assignment_id IN OUT resource_list_asgn_id_tabtype,
747 -- x_resource_list_member_id IN OUT member_id_tabtype,
748 -- x_resource_id IN OUT resource_id_tabtype,
749 -- x_member_level IN OUT member_level_tabtype,
750 -- x_person_id IN OUT person_id_tabtype,
751 -- x_job_id IN OUT job_id_tabtype,
752 -- x_organization_id IN OUT organization_id_tabtype,
753 -- x_vendor_id IN OUT vendor_id_tabtype,
754 -- x_expenditure_type IN OUT expenditure_type_tabtype,
755 -- x_event_type IN OUT event_type_tabtype,
756 -- x_non_labor_resource IN OUT non_labor_resource_tabtype,
757 -- x_expenditure_category IN OUT expenditure_category_tabtype,
758 -- x_revenue_category IN OUT revenue_category_tabtype,
759 -- x_non_labor_resource_org_id IN OUT nlr_org_id_tabtype,
760 -- x_event_type_classification IN OUT event_type_class_tabtype,
761 -- x_system_linkage_function IN OUT system_linkage_tabtype,
762 -- x_resource_format_id IN OUT resource_format_id_tabtype,
763 -- x_resource_type_code IN OUT resource_type_code_tabtype,
764
765
766 PROCEDURE get_mappable_resources
767 ( x_project_id IN NUMBER,
768 x_res_list_id IN NUMBER,
769 x_resource_ind IN OUT NOCOPY resource_index_tbl, /*Added nocopy for bug 2674619*/
770 x_resources_in IN OUT NOCOPY resources_tbl_type, /*Added nocopy for bug 2674619*/
771 x_no_of_resources IN OUT NOCOPY BINARY_INTEGER, --File.Sql.39 bug 4440895
772 x_index IN OUT NOCOPY BINARY_INTEGER, --File.Sql.39 bug 4440895
773 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
774 x_err_code IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
775 )
776
777 IS
778
779 -- Cursor for getting mappable resources for the given resource list
780 CURSOR selmembers IS
781 SELECT
782 rla.resource_list_assignment_id,
783 rl.resource_list_id,
784 rlm.resource_list_member_id,
785 rlm.resource_id,
786 rlm.member_level,
787 rta.person_id,
788 rta.job_id,
789 rta.organization_id,
790 rta.vendor_id,
791 rta.expenditure_type,
792 rta.event_type,
793 rta.non_labor_resource,
794 rta.expenditure_category,
795 rta.revenue_category,
796 rta.non_labor_resource_org_id,
797 rta.event_type_classification,
798 rta.system_linkage_function,
799 rta.resource_format_id,
800 rt.resource_type_code
801 , rl.job_group_id
802 FROM
803 pa_resource_lists_all_bg rl,
804 pa_resource_list_members rlm,
805 pa_resource_txn_attributes rta,
806 pa_resources r,
807 pa_resource_types rt,
808 pa_resource_list_assignments rla
809 WHERE
810 rlm.resource_list_id = rl.resource_list_id
811 AND rl.resource_list_id = NVL(x_res_list_id,rl.resource_list_id)
812 and nvl(rl.migration_code,'-99') <> 'N'
813 and nvl(rlm.migration_code,'-99') <> 'N'
814 AND NVL(rlm.parent_member_id,0) = 0
815 AND rlm.enabled_flag = 'Y'
816 AND rlm.resource_id = rta.resource_id(+) --- rta may not available for resource
817 AND r.resource_id = rlm.resource_id
818 AND rt.resource_type_id = r.resource_type_id
819 AND rla.resource_list_id = rl.resource_list_id
820 AND rla.project_id = x_project_id
821 AND NOT EXISTS
822 ( SELECT
823 'Yes'
824 FROM
825 pa_resource_list_members rlmc
826 WHERE
827 rlmc.parent_member_id = rlm.resource_list_member_id
828 and nvl(rlmc.migration_code,'-99') <> 'N'
829 AND rlmc.enabled_flag = 'Y'
830 )
831 UNION
832 SELECT
833 rla.resource_list_assignment_id,
834 rl.resource_list_id,
835 rlmc.resource_list_member_id,
836 rlmc.resource_id,
837 rlmc.member_level,
838 NVL(rtac.person_id,rtap.person_id),
839 NVL(rtac.job_id,rtap.job_id),
840 NVL(rtac.organization_id,rtap.organization_id),
841 NVL(rtac.vendor_id,rtap.vendor_id),
842 NVL(rtac.expenditure_type,rtap.expenditure_type),
843 NVL(rtac.event_type,rtap.event_type),
844 NVL(rtac.non_labor_resource,rtap.non_labor_resource),
845 NVL(rtac.expenditure_category,rtap.expenditure_category),
846 NVL(rtac.revenue_category,rtap.revenue_category),
847 NVL(rtac.non_labor_resource_org_id,rtap.non_labor_resource_org_id),
848 NVL(rtac.event_type_classification,rtap.event_type_classification),
849 NVL(rtac.system_linkage_function,rtap.system_linkage_function),
850 rtac.resource_format_id,
851 rtc.resource_type_code
852 , rl.job_group_id
853 FROM
854 pa_resource_lists_all_bg rl,
855 pa_resource_list_members rlmc,
856 pa_resource_list_members rlmp,
857 pa_resource_txn_attributes rtac,
858 pa_resource_txn_attributes rtap,
859 pa_resources rc,
860 pa_resource_types rtc,
861 pa_resource_list_assignments rla
862 WHERE
863 rlmc.resource_list_id = rl.resource_list_id
864 and nvl(rl.migration_code,'-99') <> 'N'
865 and nvl(rlmc.migration_code,'-99') <> 'N'
866 and nvl(rlmp.migration_code,'-99') <> 'N'
867 AND rl.resource_list_id = NVL(x_res_list_id,rl.resource_list_id)
868 AND rlmc.enabled_flag = 'Y'
869 AND rlmc.resource_id = rtac.resource_id(+) --- rta may not available for resource
870 AND rlmc.parent_member_id = rlmp.resource_list_member_id
871 AND rlmp.enabled_flag = 'Y'
872 AND rlmp.resource_id = rtap.resource_id(+) --- rta may not available for resource
873 AND rc.resource_id = rlmc.resource_id
874 AND rtc.resource_type_id = rc.resource_type_id
875 AND rla.resource_list_id = rl.resource_list_id
876 AND rla.project_id = x_project_id
877 /* The next order by is very impotant.
878 Ordering the resource by resource_list_assignment_id, resource_list_id */
879 ORDER BY 1,2;
880
881 memberrec selmembers%ROWTYPE;
882
883 BEGIN
884
885 x_err_code := 0;
886 x_no_of_resources := 0;
887 x_index := 0;
888 x_err_stage := 'Getting Mappable Resources';
889
890 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
891 pa_debug.debug('old_map_txns: ' || x_err_stage);
892 END IF;
893
894 -- get the resource list assignments and process them one by one
895
896 FOR memberrec IN selmembers LOOP
897 ---------------------------------------------getting index of resource_list
898 if x_index = 0 then
899 x_index := x_index + 1;
900 x_resource_ind(x_index).resource_list_id := memberrec.resource_list_id;
901 x_resource_ind(x_index).location := x_no_of_resources + 1;
902 elsif memberrec.resource_list_id <> x_resources_in(x_no_of_resources).resource_list_id then
903 x_index := x_index + 1;
904 x_resource_ind(x_index).resource_list_id := memberrec.resource_list_id;
905 x_resource_ind(x_index).location := x_no_of_resources + 1;
906 end if;
907
908 -- Get the mappable resource for this project
909 x_no_of_resources := x_no_of_resources + 1;
910
911 x_resources_in(x_no_of_resources).resource_list_assignment_id :=
912 memberrec.resource_list_assignment_id;
913 x_resources_in(x_no_of_resources).resource_list_id := memberrec.resource_list_id;
914 x_resources_in(x_no_of_resources).resource_list_member_id := memberrec.resource_list_member_id;
915 x_resources_in(x_no_of_resources).resource_id := memberrec.resource_id;
916 x_resources_in(x_no_of_resources).member_level := memberrec.member_level;
917 x_resources_in(x_no_of_resources).person_id := memberrec.person_id;
918 x_resources_in(x_no_of_resources).job_id := memberrec.job_id;
919 x_resources_in(x_no_of_resources).organization_id := memberrec.organization_id;
920 x_resources_in(x_no_of_resources).vendor_id := memberrec.vendor_id;
921 x_resources_in(x_no_of_resources).expenditure_type := memberrec.expenditure_type;
922 x_resources_in(x_no_of_resources).event_type := memberrec.event_type;
923 x_resources_in(x_no_of_resources).non_labor_resource := memberrec.non_labor_resource;
924 x_resources_in(x_no_of_resources).expenditure_category := memberrec.expenditure_category;
925 x_resources_in(x_no_of_resources).revenue_category := memberrec.revenue_category;
926 x_resources_in(x_no_of_resources).non_labor_resource_org_id :=
927 memberrec.non_labor_resource_org_id;
928 x_resources_in(x_no_of_resources).event_type_classification :=
929 memberrec.event_type_classification;
930 x_resources_in(x_no_of_resources).system_linkage_function :=
931 memberrec.system_linkage_function;
932 x_resources_in(x_no_of_resources).resource_format_id := memberrec.resource_format_id;
933 x_resources_in(x_no_of_resources).resource_type_code := memberrec.resource_type_code;
934 x_resources_in(x_no_of_resources).job_group_id := memberrec.job_group_id;
935
936 END LOOP;
937
938 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
939 pa_debug.debug('old_map_txns: ' || 'Number of resources found = ' || TO_CHAR(x_no_of_resources));
940 END IF;
941
942 EXCEPTION
943 WHEN NO_DATA_FOUND THEN
944 NULL;
945 WHEN OTHERS THEN
946 x_err_code := SQLCODE;
947 RAISE;
948 END get_mappable_resources;
949
950
951 /* With resource mapping enhancement bug 1889671 this procedure is now obsolete and will
952 not be used any more
953 */
954
955 PROCEDURE old_map_txns
956
957 ( x_project_id IN NUMBER,
958 x_res_list_id IN NUMBER,
959 x_mode IN VARCHAR2 DEFAULT 'I',
960 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
961 x_err_code IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
962 IS
963
964 -- Cursor for getting the txns for which the mapping needs to be done
965
966 CURSOR seltxnaccums IS
967 SELECT
968 pta.txn_accum_id,
969 pta.project_id,
970 pta.task_id,
971 pta.person_id,
972 pta.job_id,
973 pta.organization_id,
974 pta.vendor_id,
975 pta.expenditure_type,
976 pta.event_type,
977 pta.non_labor_resource,
978 pta.expenditure_category,
979 pta.revenue_category,
980 pta.non_labor_resource_org_id,
981 pta.event_type_classification,
982 pta.system_linkage_function
983 FROM
984 pa_txn_accum pta
985 WHERE
986 pta.project_id = x_project_id
987 AND ((pta.actual_cost_rollup_flag = DECODE(x_mode,'I','Y',
988 'F',pta.actual_cost_rollup_flag,
989 pta.actual_cost_rollup_flag))
990 OR
991 (pta.revenue_rollup_flag = DECODE(x_mode,'I','Y',
992 'F',pta.revenue_rollup_flag,
993 pta.revenue_rollup_flag))
994 OR
995 (pta.cmt_rollup_flag = DECODE(x_mode,'I','Y',
996 'F',pta.cmt_rollup_flag,
997 pta.cmt_rollup_flag)))
998 AND EXISTS
999 ( SELECT 'Yes'
1000 FROM pa_txn_accum_details ptad
1001 WHERE pta.txn_accum_id = ptad.txn_accum_id
1002 );
1003
1004 txnaccumrec seltxnaccums%ROWTYPE;
1005
1006
1007 -- x_resource_list_assignment_id resource_list_asgn_id_tabtype;
1008 -- x_resource_list_id resource_list_id_tabtype;
1009 -- x_resource_list_member_id member_id_tabtype;
1010 -- x_resource_id resource_id_tabtype;
1011 -- x_member_level member_level_tabtype;
1012 -- x_person_id person_id_tabtype;
1013 -- x_job_id job_id_tabtype;
1014 -- x_organization_id organization_id_tabtype;
1015 -- x_vendor_id vendor_id_tabtype;
1016 -- x_expenditure_type expenditure_type_tabtype;
1017 -- x_event_type event_type_tabtype;
1018 -- x_non_labor_resource non_labor_resource_tabtype;
1019 -- x_expenditure_category expenditure_category_tabtype;
1020 -- x_revenue_category revenue_category_tabtype;
1021 -- x_non_labor_resource_org_id nlr_org_id_tabtype;
1022 -- x_event_type_classification event_type_class_tabtype;
1023 -- x_system_linkage_function system_linkage_tabtype;
1024 -- x_resource_format_id resource_format_id_tabtype;
1025 -- x_resource_type_code resource_type_code_tabtype;
1026
1027 x_resource_ind resource_index_tbl;
1028 x_resources_in resources_tbl_type;
1029 x_no_of_resources BINARY_INTEGER;
1030 x_index BINARY_INTEGER;
1031 res_count BINARY_INTEGER;
1032 ind_count BINARY_INTEGER;
1033
1034 -- Variable to store the attributes of the resource list
1035
1036 current_rl_assignment_id NUMBER; -- Current resource list assignment id
1037 current_rl_id NUMBER; -- Current resource list id
1038 current_rl_changed_flag VARCHAR2(1); -- was this resource list changed?
1039 mapping_done BOOLEAN; -- is mapping done for current resource list
1040 current_rl_type_code VARCHAR2(20);-- current resource list type code
1041
1042 current_rl_member_id NUMBER;
1043 current_resource_id NUMBER;
1044 current_resource_rank NUMBER;
1045 current_member_level NUMBER;
1046 group_category_found BOOLEAN;
1047 attr_match_found BOOLEAN;
1048 new_resource_rank NUMBER;
1049
1050 old_resource_id NUMBER;
1051 old_rl_member_id NUMBER;
1052 old_rl_assignment_id NUMBER;
1053
1054 resource_map_found BOOLEAN;
1055
1056 -- member id for unclassified resources
1057
1058 uncl_group_member_id NUMBER;
1059 uncl_child_member_id NUMBER;
1060 uncl_resource_id NUMBER; -- assuming one resource_id for unclassfied
1061 commit_rows NUMBER;
1062 current_rl_job_group_id NUMBER; -- for Project Jobs
1063
1064 BEGIN
1065
1066 x_err_code :=0;
1067 x_err_stage := 'Maping Transaction to Resources - APR-28';
1068 commit_rows := 0;
1069
1070 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1071 pa_debug.debug('old_map_txns: ' || x_err_stage);
1072 END IF;
1073
1074 -- Get the mappable resource for this project
1075 get_mappable_resources
1076 ( x_project_id,
1077 x_res_list_id,
1078 x_resource_ind,
1079 x_resources_in,
1080 x_no_of_resources,
1081 x_index,
1082 x_err_stage,
1083 x_err_code);
1084
1085 /* FOR res_count IN 1..x_no_of_resources LOOP
1086
1087 pa_debug.debug('old_map_txns: ' || 'List id= '|| to_char(x_resources_in(res_count).resource_list_id)||
1088 ' Res id= '|| to_char(x_resources_in(res_count).resource_id) ||
1089 ' Asgn id= '|| to_char(x_resources_in(res_count).resource_list_assignment_id) ||
1090 ' Member id= '|| to_char(x_resources_in(res_count).resource_list_member_id));
1091
1092 END LOOP; */
1093
1094 -- Now process all the eligible pa_txn_accum
1095
1096 -- Get the txns for which mapping is to be done
1097 FOR txnaccumrec IN seltxnaccums LOOP
1098
1099 -- Map this txn to all the resoure lists for this project
1100 commit_rows := commit_rows + 1;
1101 ind_count := 1;
1102 res_count := 0;
1103 mapping_done := TRUE;
1104 current_rl_assignment_id :=0;
1105
1106 LOOP
1107 res_count := res_count + 1;
1108 if res_count > x_no_of_resources then
1109 exit;
1110 end if;
1111 /* pa_debug.debug('Rescount value is :' || TO_CHAR(res_count)); */
1112 IF (current_rl_assignment_id <> x_resources_in(res_count).resource_list_assignment_id) THEN
1113
1114 -- Mapping to the next resource list
1115 -- Check if resource mapping was done for last resource_list_assigment_id or not
1116 IF ( NOT mapping_done ) THEN
1117
1118 IF ( current_resource_id IS NULL ) THEN -- The last txn_accum could not be mapped
1119
1120 -- Map to unclassified Resource
1121 -- also if the group_category_found flag is true than map to unclassfied
1122 -- category within the group
1123
1124 current_resource_id := uncl_resource_id;
1125
1126 IF (group_category_found AND uncl_child_member_id <> 0) THEN
1127 current_rl_member_id := uncl_child_member_id;
1128 ELSE
1129 current_rl_member_id := uncl_group_member_id;
1130 END IF;
1131
1132 END IF; --- IF ( current_resource_id IS NULL )
1133
1134 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1135 pa_debug.debug('old_map_txns: ' || 'Resource mapping=' ||
1136 to_char(current_rl_member_id)|| ' ' || to_char(current_resource_id));
1137 END IF;
1138
1139 -- Create a map now
1140 create_resource_map
1141 (current_rl_id,
1142 current_rl_assignment_id,
1143 current_rl_member_id,
1144 current_resource_id,
1145 txnaccumrec.person_id,
1146 PA_Cross_Business_Grp.IsMappedToJob(txnaccumrec.job_id,current_rl_job_group_id),
1147 txnaccumrec.organization_id,
1148 txnaccumrec.vendor_id,
1149 txnaccumrec.expenditure_type,
1150 txnaccumrec.event_type,
1151 txnaccumrec.non_labor_resource,
1152 txnaccumrec.expenditure_category,
1153 txnaccumrec.revenue_category,
1154 txnaccumrec.non_labor_resource_org_id,
1155 txnaccumrec.event_type_classification,
1156 txnaccumrec.system_linkage_function,
1157 x_err_stage,
1158 x_err_code);
1159
1160 -- Now create pa_resource_accum_details
1161
1162 create_resource_accum_details
1163 (current_rl_id,
1164 current_rl_assignment_id,
1165 current_rl_member_id,
1166 current_resource_id,
1167 txnaccumrec.txn_accum_id,
1168 txnaccumrec.project_id,
1169 txnaccumrec.task_id,
1170 x_err_stage,
1171 x_err_code);
1172
1173 END IF; -- IF ( NOT mapping_done )
1174
1175 --- Proceed to the next resource list now
1176
1177 current_rl_assignment_id := x_resources_in(res_count).resource_list_assignment_id;
1178 current_rl_id := x_resources_in(res_count).resource_list_id;
1179 current_rl_changed_flag := get_resource_list_status(current_rl_assignment_id);
1180 current_rl_type_code := get_group_resource_type_code(current_rl_id);
1181 current_rl_job_group_id := x_resources_in(res_count).job_group_id;
1182 mapping_done := FALSE;
1183
1184 -- This variables will store the information for best match for the resource
1185 current_rl_member_id := NULL;
1186 current_resource_id := NULL;
1187 current_resource_rank := NULL;
1188 current_member_level := NULL;
1189 group_category_found := FALSE;
1190 uncl_group_member_id := 0;
1191 uncl_child_member_id := 0;
1192 uncl_resource_id := 0;
1193
1194 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1195 pa_debug.debug('old_map_txns: ' || 'asn id='||current_rl_assignment_id ||
1196 ' list id='||current_rl_id ||
1197 ' changed flag='||current_rl_changed_flag ||
1198 ' type code ='||current_rl_type_code );
1199 END IF;
1200 IF ( current_rl_changed_flag = 'Y' ) THEN -- This resource list assignmnet
1201 -- has been changed
1202 -- delete all the old maps for this resource list assignments
1203 -- for all the transactions
1204
1205 delete_res_maps_on_asgn_id(current_rl_assignment_id,x_err_stage,x_err_code);
1206 change_resource_list_status(current_rl_assignment_id,x_err_stage,x_err_code);
1207
1208 ELSIF ( current_rl_changed_flag = 'N' ) THEN
1209 -- Get the resource map status
1210 get_resource_map
1211 (current_rl_id,
1212 current_rl_assignment_id,
1213 txnaccumrec.person_id,
1214 PA_Cross_Business_Grp.IsMappedToJob(txnaccumrec.job_id,current_rl_job_group_id),
1215 txnaccumrec.organization_id,
1216 txnaccumrec.vendor_id,
1217 txnaccumrec.expenditure_type,
1218 txnaccumrec.event_type,
1219 txnaccumrec.non_labor_resource,
1220 txnaccumrec.expenditure_category,
1221 txnaccumrec.revenue_category,
1222 txnaccumrec.non_labor_resource_org_id,
1223 txnaccumrec.event_type_classification,
1224 txnaccumrec.system_linkage_function,
1225 old_rl_member_id,
1226 old_resource_id,
1227 resource_map_found,
1228 x_err_stage,
1229 x_err_code);
1230
1231 -- check if a map exist for the given attributes in the map table
1232 IF NOT(resource_map_found) THEN
1233 get_resource_map_new
1234 (current_rl_id,
1235 txnaccumrec.person_id,
1236 PA_Cross_Business_Grp.IsMappedToJob(txnaccumrec.job_id,current_rl_job_group_id),
1237 txnaccumrec.organization_id,
1238 txnaccumrec.vendor_id,
1239 txnaccumrec.expenditure_type,
1240 txnaccumrec.event_type,
1241 txnaccumrec.non_labor_resource,
1242 txnaccumrec.expenditure_category,
1243 txnaccumrec.revenue_category,
1244 txnaccumrec.non_labor_resource_org_id,
1245 txnaccumrec.event_type_classification,
1246 txnaccumrec.system_linkage_function,
1247 old_rl_member_id,
1248 old_resource_id,
1249 resource_map_found,
1250 x_err_stage,
1251 x_err_code);
1252
1253 if (resource_map_found) THEN
1254 create_resource_map
1255 (current_rl_id,
1256 current_rl_assignment_id,
1257 old_rl_member_id,
1258 old_resource_id,
1259 txnaccumrec.person_id,
1260 PA_Cross_Business_Grp.IsMappedToJob(txnaccumrec.job_id,current_rl_job_group_id),
1261 txnaccumrec.organization_id,
1262 txnaccumrec.vendor_id,
1263 txnaccumrec.expenditure_type,
1264 txnaccumrec.event_type,
1265 txnaccumrec.non_labor_resource,
1266 txnaccumrec.expenditure_category,
1267 txnaccumrec.revenue_category,
1268 txnaccumrec.non_labor_resource_org_id,
1269 txnaccumrec.event_type_classification,
1270 txnaccumrec.system_linkage_function,
1271 x_err_stage,
1272 x_err_code);
1273 mapping_done := TRUE;
1274 end if;
1275 else
1276 mapping_done := TRUE;
1277 end if;
1278
1279 if mapping_done then
1280 -- Now create pa_resource_accum_details
1281 create_resource_accum_details
1282 (current_rl_id,
1283 current_rl_assignment_id,
1284 old_rl_member_id,
1285 old_resource_id,
1286 txnaccumrec.txn_accum_id,
1287 txnaccumrec.project_id,
1288 txnaccumrec.task_id,
1289 x_err_stage,
1290 x_err_code);
1291 end if;
1292
1293 if(resource_map_found) THEN
1294 --------------------------------------------go to the next res list
1295 if ind_count >= x_index then
1296 res_count := x_no_of_resources;
1297 else
1298 ind_count := ind_count + 1;
1299 res_count := x_resource_ind(ind_count).location - 1;
1300 end if;
1301 ------------------------------------------------------------------
1302 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1303 pa_debug.debug('old_map_txns: ' || 'an old MAP IS FOUND');
1304 END IF;
1305 else
1306 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1307 pa_debug.debug('old_map_txns: ' || 'old MAP IS not FOUND');
1308 END IF;
1309 end if;
1310 END IF;
1311
1312 END IF; -- IF (current_rl_assignment_id <> x_resource_list_assignment_id ....
1313
1314 IF ( NOT mapping_done ) THEN
1315
1316 -- Mapping still need to be done
1317 attr_match_found := TRUE;
1318
1319 IF ((x_resources_in(res_count).resource_type_code = 'UNCLASSIFIED' OR
1320 x_resources_in(res_count).resource_type_code = 'UNCATEGORIZED') AND
1321 x_resources_in(res_count).member_level = 1 ) THEN
1322 attr_match_found := FALSE;
1323 uncl_resource_id := x_resources_in(res_count).resource_id;
1324 uncl_group_member_id := x_resources_in(res_count).resource_list_member_id;
1325 END IF;
1326
1327 IF ( current_rl_type_code = 'EXPENDITURE_CATEGORY') THEN
1328
1329 -- The resource list is based on the expenditure category
1330
1331 IF ( x_resources_in(res_count).expenditure_category = txnaccumrec.expenditure_category) THEN
1332 group_category_found := TRUE;
1333 ELSE
1334 attr_match_found := FALSE;
1335 END IF; --IF ( x_expenditure_category(res_count).....
1336
1337
1338 ELSIF ( current_rl_type_code = 'REVENUE_CATEGORY' ) THEN
1339
1340 -- The resource list is based on the revenue category
1341
1342 IF (x_resources_in(res_count).revenue_category = txnaccumrec.revenue_category) THEN
1343 group_category_found := TRUE;
1344 ELSE
1345 attr_match_found := FALSE;
1346 END IF; -- IF (x_revenue_category(res_count) ....
1347
1348
1349 ELSIF ( current_rl_type_code = 'ORGANIZATION' ) THEN
1350
1351 -- The resource list is based on the organization
1352
1353 IF (x_resources_in(res_count).organization_id = txnaccumrec.organization_id) THEN
1354 group_category_found := TRUE;
1355 ELSE
1356 attr_match_found := FALSE;
1357 END IF; -- IF (x_organization_id(res_count)
1358
1359
1360 END IF; -- IF ( current_rl_type_code = 'EXPENDITURE_CATEGORY'...
1361
1362 IF ( current_rl_type_code = 'NONE' OR attr_match_found ) THEN
1363
1364 -- The resource list is based on the none category
1365
1366 -- Now compare the txn attributes with resource attributes
1367
1368 -- The table given below determines if the resource is eligible
1369 -- for accumulation or not
1370
1371 -- TXN ATTRIBUTE RESOURCE ATTRIBUTE ELIGIBLE
1372 -- NULL NULL YES
1373 -- NULL NOT NULL NO
1374 -- NOT NULL NULL YES
1375 -- NOT NULL NOT NULL YES/NO depending on value
1376
1377 -- Do not match the attributes for an unclassified resource
1378
1379 IF (x_resources_in(res_count).resource_type_code = 'UNCLASSIFIED' ) THEN
1380 attr_match_found := FALSE;
1381 uncl_resource_id := x_resources_in(res_count).resource_id;
1382
1383 IF ( x_resources_in(res_count).member_level = 1 ) THEN -- group level unclassified
1384 uncl_group_member_id := x_resources_in(res_count).resource_list_member_id;
1385 ELSE
1386 uncl_child_member_id := x_resources_in(res_count).resource_list_member_id;
1387 END IF;
1388
1389 END IF;
1390
1391 IF (NOT (attr_match_found AND
1392 (NVL(x_resources_in(res_count).person_id,NVL(txnaccumrec.person_id,-1)) =
1393 NVL(txnaccumrec.person_id, -1)))) THEN
1394 attr_match_found := FALSE;
1395 END IF;
1396
1397
1398 IF (NOT (attr_match_found AND
1399 (NVL(x_resources_in(res_count).job_id,NVL(PA_Cross_Business_Grp.IsMappedToJob(txnaccumrec.job_id,current_rl_job_group_id),-1)) =
1400 NVL(PA_Cross_Business_Grp.IsMappedToJob(txnaccumrec.job_id,current_rl_job_group_id), -1)))) THEN
1401 attr_match_found := FALSE;
1402 END IF;
1403
1404 IF (NOT (attr_match_found AND
1405 (NVL(x_resources_in(res_count).organization_id,NVL(txnaccumrec.organization_id,-1)) =
1406 NVL(txnaccumrec.organization_id, -1)))) THEN
1407 attr_match_found := FALSE;
1408 END IF;
1409
1410 IF (NOT (attr_match_found AND
1411 (NVL(x_resources_in(res_count).vendor_id,NVL(txnaccumrec.vendor_id,-1)) =
1412 NVL(txnaccumrec.vendor_id, -1)))) THEN
1413 attr_match_found := FALSE;
1414 END IF;
1415
1416 IF (NOT (attr_match_found AND
1417 (NVL(x_resources_in(res_count).expenditure_type,NVL(txnaccumrec.expenditure_type,'X')) =
1418 NVL(txnaccumrec.expenditure_type, 'X')))) THEN
1419 attr_match_found := FALSE;
1420 END IF;
1421
1422 IF (NOT (attr_match_found AND
1423 (NVL(x_resources_in(res_count).event_type,NVL(txnaccumrec.event_type,'X')) =
1424 NVL(txnaccumrec.event_type, 'X')))) THEN
1425 attr_match_found := FALSE;
1426 END IF;
1427
1428 IF (NOT (attr_match_found AND
1429 (NVL(x_resources_in(res_count).non_labor_resource,NVL(txnaccumrec.non_labor_resource,'X')) =
1430 NVL(txnaccumrec.non_labor_resource, 'X')))) THEN
1431 attr_match_found := FALSE;
1432 END IF;
1433
1434 IF (NOT (attr_match_found AND
1435 (NVL(x_resources_in(res_count).expenditure_category,NVL(txnaccumrec.expenditure_category,'X')) =
1436 NVL(txnaccumrec.expenditure_category, 'X')))) THEN
1437 attr_match_found := FALSE;
1438 END IF;
1439
1440 IF (NOT (attr_match_found AND
1441 (NVL(x_resources_in(res_count).revenue_category,NVL(txnaccumrec.revenue_category,'X')) =
1442 NVL(txnaccumrec.revenue_category,'X')))) THEN
1443 attr_match_found := FALSE;
1444 END IF;
1445
1446 IF (NOT (attr_match_found AND
1447 (NVL(x_resources_in(res_count).non_labor_resource_org_id,NVL(txnaccumrec.non_labor_resource_org_id,-1)) =
1448 NVL(txnaccumrec.non_labor_resource_org_id,-1)))) THEN
1449 attr_match_found := FALSE;
1450 END IF;
1451
1452 IF (NOT (attr_match_found AND
1453 (NVL(x_resources_in(res_count).event_type_classification,NVL(txnaccumrec.event_type_classification,'X')) =
1454 NVL(txnaccumrec.event_type_classification,'X')))) THEN
1455 attr_match_found := FALSE;
1456 END IF;
1457
1458 IF (NOT (attr_match_found AND
1459 (NVL(x_resources_in(res_count).system_linkage_function,NVL(txnaccumrec.system_linkage_function,'X')) =
1460 NVL(txnaccumrec.system_linkage_function,'X')))) THEN
1461 attr_match_found := FALSE;
1462 END IF;
1463
1464 END IF; --IF ( current_rl_type_code = 'NONE'......
1465 IF (attr_match_found) THEN
1466
1467 -- Get the resource rank now
1468 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1469 pa_debug.debug('old_map_txns: ' || 'This resource is eligible for mapping');
1470 END IF;
1471
1472 IF ( txnaccumrec.event_type_classification IS NOT NULL ) THEN
1473
1474 -- determine the rank based on event_type_classification
1475 new_resource_rank := get_resource_rank(
1476 x_resources_in(res_count).resource_format_id,
1477 txnaccumrec.event_type_classification);
1478 ELSE
1479 -- determine the rank based on system_linkage_function
1480 new_resource_rank := get_resource_rank(
1481 x_resources_in(res_count).resource_format_id,
1482 txnaccumrec.system_linkage_function);
1483 END IF; -- IF ( txnaccumrec.event_type_classification IS NOT NULL )
1484 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1485 pa_debug.debug('old_map_txns: ' || 'Rank for this resource=' || to_char(new_resource_rank));
1486 END IF;
1487
1488 IF ( NVL(new_resource_rank,99) < NVL(current_resource_rank,99) ) THEN
1489
1490 current_resource_rank := new_resource_rank;
1491 current_rl_member_id := x_resources_in(res_count).resource_list_member_id;
1492 current_resource_id := x_resources_in(res_count).resource_id;
1493 current_member_level := x_resources_in(res_count).member_level;
1494
1495 END IF;
1496 END IF; -- IF (attr_match_found)
1497
1498 END IF; -- IF ( NOT mapping_done ) THEN
1499
1500 END LOOP;
1501
1502 -- Now create the map for the last resoure list assignment
1503 IF ( NOT mapping_done ) THEN
1504
1505 IF ( current_resource_id IS NULL ) THEN -- The last txn_accum could not be mapped
1506
1507 -- Map to unclassified Resource
1508 -- also if the group_category_found flag is true than map to unclassfied
1509 -- category within the group
1510
1511 current_resource_id := uncl_resource_id;
1512
1513 IF (group_category_found AND uncl_child_member_id <> 0) THEN
1514 current_rl_member_id := uncl_child_member_id;
1515 ELSE
1516 current_rl_member_id := uncl_group_member_id;
1517 END IF;
1518
1519 END IF; --- IF ( current_resource_id IS NULL )
1520 -- Create a map now
1521 create_resource_map
1522 (current_rl_id,
1523 current_rl_assignment_id,
1524 current_rl_member_id,
1525 current_resource_id,
1526 txnaccumrec.person_id,
1527 PA_Cross_Business_Grp.IsMappedToJob(txnaccumrec.job_id,current_rl_job_group_id),
1528 txnaccumrec.organization_id,
1529 txnaccumrec.vendor_id,
1530 txnaccumrec.expenditure_type,
1531 txnaccumrec.event_type,
1532 txnaccumrec.non_labor_resource,
1533 txnaccumrec.expenditure_category,
1534 txnaccumrec.revenue_category,
1535 txnaccumrec.non_labor_resource_org_id,
1536 txnaccumrec.event_type_classification,
1537 txnaccumrec.system_linkage_function,
1538 x_err_stage,
1539 x_err_code);
1540
1541 -- Now create pa_resource_accum_details
1542
1543 create_resource_accum_details
1544 (current_rl_id,
1545 current_rl_assignment_id,
1546 current_rl_member_id,
1547 current_resource_id,
1548 txnaccumrec.txn_accum_id,
1549 txnaccumrec.project_id,
1550 txnaccumrec.task_id,
1551 x_err_stage,
1552 x_err_code);
1553
1554 END IF;
1555 If commit_rows >= pa_proj_accum_main.x_commit_size then
1556 commit;
1557 commit_rows := 0;
1558 end if;
1559
1560 END LOOP;
1561
1562
1563 EXCEPTION
1564 -- Return if either no resource list are assigned to the project and/or
1565 -- no records in pa_txn_accum table need to be rolled up
1566
1567 WHEN NO_DATA_FOUND THEN
1568 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1569 pa_debug.debug('old_map_txns: ' || 'Exception Raised on Procedure map_txns');
1570 END IF;
1571 NULL;
1572 WHEN OTHERS THEN
1573 x_err_code := SQLCODE;
1574 RAISE;
1575 END old_map_txns;
1576
1577 /* Map_txns is a wrapper around new_map_txns. This is called from Summarization
1578 process and this will call new_map_txns. */
1579
1580 PROCEDURE map_txns
1581
1582 ( x_project_id IN NUMBER,
1583 x_res_list_id IN NUMBER,
1584 x_mode IN VARCHAR2 DEFAULT 'I',
1585 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1586 x_err_code IN OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
1587
1588 l_resource_list_assignment_id NUMBER;
1589 l_resource_list_id NUMBER;
1590
1591 /* In case x_res_list_id is null then do mapping for all
1592 resource lists attached to the project */
1593
1594 /* Bug 3812290 Added pa_resource_lists_all_bg for the below cursor c1 */
1595
1596 CURSOR C1 IS
1597 SELECT prla.resource_list_id
1598 ,prla.resource_list_assignment_id
1599 FROM pa_resource_list_assignments prla,
1600 pa_resource_lists_all_bg res
1601 WHERE prla.resource_list_id = nvl(x_res_list_id,prla.resource_list_id)
1602 AND prla.project_id = x_project_id
1603 AND res.resource_list_id = prla.resource_list_id
1604 AND NVL(res.MIGRATION_CODE,'-99') <> 'N';
1605
1606 /* This cursor is used print all the attribute details of the txns which
1607 result in NULL insert into PA_RESOURCE_ACCUM_DETAILS. This cursor is called
1608 only during NULL insert exception (resource_id and resource_list_member_id can
1609 be NULL if MAP_TXNS has failed to derive the same) */
1610
1611 CURSOR C2 IS
1612 SELECT resource_id,
1613 resource_list_member_id,
1614 person_id,
1615 job_id,
1616 organization_id,
1617 vendor_id,
1618 expenditure_type,
1619 event_type,
1620 non_labor_resource,
1621 expenditure_category,
1622 revenue_category,
1623 non_labor_resource_org_id,
1624 event_type_classification,
1625 system_linkage_function,
1626 system_reference1 txn_accum_id,
1627 system_reference2 project_id,
1628 system_reference3 task_id
1629 FROM PA_MAPPABLE_TXNS_TMP pmt
1630 WHERE NOT EXISTS
1631 (SELECT 'Yes'
1632 FROM pa_resource_accum_details rad
1633 WHERE resource_list_id = l_resource_list_id
1634 AND resource_list_assignment_id = l_resource_list_assignment_id
1635 AND txn_accum_id = pmt.system_reference1
1636 AND project_id = pmt.system_reference2
1637 AND task_id = pmt.system_reference3)
1638 AND (pmt.resource_list_member_id is null OR
1639 pmt.resource_id is null);
1640 /*Code Changes for Bug No.2984871 start */
1641 l_rowcount number :=0;
1642 /*Code Changes for Bug No.2984871 end */
1643
1644 BEGIN
1645
1646 FOR c1rec IN c1 LOOP
1647
1648 l_resource_list_id := c1rec.resource_list_id;
1649 l_resource_list_assignment_id := c1rec.resource_list_assignment_id;
1650
1651 x_err_stage := ('Processing for resource list ' || l_resource_list_id);
1652 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1653 pa_debug.debug('map_txns: ' || x_err_stage);
1654 END IF;
1655
1656 delete from pa_mappable_txns_tmp;
1657
1658 x_err_stage := 'Inserting into pa_mappable_txns_tmp';
1659 /* Bug 5552602/ 5571792: Split the insert based on x_mode = I (Incremental-Update Process) or F (Full-Refresh process)*/
1660 If nvl(x_mode,'F') = 'I' then
1661 INSERT INTO PA_MAPPABLE_TXNS_TMP (
1662 txn_id,
1663 person_id,
1664 job_id,
1665 organization_id,
1666 vendor_id,
1667 expenditure_type,
1668 event_type,
1669 non_labor_resource,
1670 expenditure_category,
1671 revenue_category,
1672 non_labor_resource_org_id,
1673 event_type_classification,
1674 system_linkage_function,
1675 project_role_id,
1676 resource_list_id,
1677 system_reference1,
1678 system_reference2,
1679 system_reference3
1680 )
1681 SELECT
1682 pa_mappable_txns_tmp_s.NEXTVAL,
1683 pta.person_id,
1684 pta.job_id,
1685 pta.organization_id,
1686 pta.vendor_id,
1687 pta.expenditure_type,
1688 pta.event_type,
1689 pta.non_labor_resource,
1690 pta.expenditure_category,
1691 pta.revenue_category,
1692 pta.non_labor_resource_org_id,
1693 pta.event_type_classification,
1694 pta.system_linkage_function,
1695 NULL, /* Project role id is not there on pa_txn_accum */
1696 l_resource_list_id,
1697 pta.txn_accum_id, /* To identify our records back */
1698 pta.project_id, /* This will avoid joining to pa_txn_accum again during insertion */
1699 pta.task_id /* pa_resource_accum_details table */
1700 FROM pa_txn_accum pta
1701 WHERE pta.project_id = x_project_id
1702 AND ((pta.actual_cost_rollup_flag = 'Y') OR
1703 (pta.revenue_rollup_flag = 'Y') OR
1704 (pta.cmt_rollup_flag = 'Y') )
1705 /* 5571792 AND ((pta.actual_cost_rollup_flag = DECODE(x_mode,'I','Y',
1706 'F',pta.actual_cost_rollup_flag,
1707 pta.actual_cost_rollup_flag)) OR
1708 (pta.revenue_rollup_flag = DECODE(x_mode,'I','Y',
1709 'F',pta.revenue_rollup_flag,
1710 pta.revenue_rollup_flag)) OR
1711 (pta.cmt_rollup_flag = DECODE(x_mode,'I','Y',
1712 'F',pta.cmt_rollup_flag,
1713 pta.cmt_rollup_flag))) 5571792 */
1714 AND EXISTS
1715 (SELECT 'Yes'
1716 FROM pa_txn_accum_details ptad
1717 WHERE pta.txn_accum_id = ptad.txn_accum_id
1718 /* following not exists will be valid even in case of refresh ( x_mode = 'F' )
1719 because from refresh process we call map_txns only after we have
1720 deleted records from pa_resource_accum_details table */
1721 AND NOT EXISTS
1722 (SELECT 'Yes'
1723 FROM pa_resource_accum_details prad
1724 WHERE prad.txn_accum_id = pta.txn_accum_id
1725 AND resource_list_id = l_resource_list_id
1726 AND resource_list_assignment_id = l_resource_list_assignment_id
1727 )
1728 );
1729 ELSE /* 5571792*/
1730 INSERT INTO PA_MAPPABLE_TXNS_TMP (
1731 txn_id,
1732 person_id,
1733 job_id,
1734 organization_id,
1735 vendor_id,
1736 expenditure_type,
1737 event_type,
1738 non_labor_resource,
1739 expenditure_category,
1740 revenue_category,
1741 non_labor_resource_org_id,
1742 event_type_classification,
1743 system_linkage_function,
1744 project_role_id,
1745 resource_list_id,
1746 system_reference1,
1747 system_reference2,
1748 system_reference3
1749 )
1750 SELECT
1751 pa_mappable_txns_tmp_s.NEXTVAL,
1752 pta.person_id,
1753 pta.job_id,
1754 pta.organization_id,
1755 pta.vendor_id,
1756 pta.expenditure_type,
1757 pta.event_type,
1758 pta.non_labor_resource,
1759 pta.expenditure_category,
1760 pta.revenue_category,
1761 pta.non_labor_resource_org_id,
1762 pta.event_type_classification,
1763 pta.system_linkage_function,
1764 NULL, /* Project role id is not there on pa_txn_accum */
1765 l_resource_list_id,
1766 pta.txn_accum_id, /* To identify our records back */
1767 pta.project_id, /* This will avoid joining to pa_txn_accum again during insertion */
1768 pta.task_id /* pa_resource_accum_details table */
1769 FROM pa_txn_accum pta
1770 WHERE pta.project_id = x_project_id
1771 AND EXISTS
1772 (SELECT 'Yes'
1773 FROM pa_txn_accum_details ptad
1774 WHERE pta.txn_accum_id = ptad.txn_accum_id
1775 /* following not exists will be valid even in case of refresh ( x_mode = 'F' )
1776 because from refresh process we call map_txns only after we have
1777 deleted records from pa_resource_accum_details table */
1778 AND NOT EXISTS
1779 (SELECT 'Yes'
1780 FROM pa_resource_accum_details prad
1781 WHERE prad.txn_accum_id = pta.txn_accum_id
1782 AND resource_list_id = l_resource_list_id
1783 AND resource_list_assignment_id = l_resource_list_assignment_id
1784 )
1785 );
1786 END IF; /* 5571792*/
1787
1788 /*Code Changes for Bug No.2984871 start */
1789 l_rowcount:=sql%rowcount;
1790 /*Code Changes for Bug No.2984871 end */
1791
1792 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1793 /* Commented for Bug 2984871
1794 pa_debug.debug('map_txns: ' || 'Inserted ' || sql%rowcount|| ' rows in pa_mappable_txns_tmp ');*/
1795 /*Code Changes for Bug No.2984871 start */
1796 pa_debug.debug('map_txns: ' || 'Inserted ' || l_rowcount || ' rows in pa_mappable_txns_tmp ');
1797 /*Code Changes for Bug No.2984871 end*/
1798 END IF;
1799 /* Commented for Bug 2984871
1800 IF sql%rowcount = 0 THEN*/
1801 /*Code Changes for Bug No.2984871 start */
1802 IF l_rowcount= 0 THEN
1803 /*Code Changes for Bug No.2984871 end*/
1804 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1805 pa_debug.debug('NEW_MAP_TXNS is not called for this resource list since there ' ||
1806 'arent any records to process');
1807 END IF;
1808
1809 ELSE /* PA_MAPPABLE_TXNS_TMP contains records to be processed */
1810
1811 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1812 pa_debug.debug('map_txns: ' || 'Calling new mapping api @ ' ||
1813 to_CHAR(sysdate,'DD-MON-RR::HH:MI:SS'));
1814 END IF;
1815
1816 new_map_txns (x_resource_list_id => l_resource_list_id,
1817 x_error_stage => x_err_stage,
1818 x_error_code => x_err_code);
1819
1820 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1821 pa_debug.debug('map_txns: ' || 'Call returned from new mapping api @ ' ||
1822 to_char(sysdate,'DD-MON-RR::HH:MI:SS'));
1823 END IF;
1824
1825 DECLARE
1826
1827 null_insert EXCEPTION;
1828 PRAGMA EXCEPTION_INIT(null_insert,-1400);
1829
1830 BEGIN
1831 x_err_stage := 'Inserting into pa_resource_accum_details';
1832 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1833 pa_debug.debug('map_txns: ' || x_err_stage);
1834 END IF;
1835
1836 INSERT INTO pa_resource_accum_details
1837 (resource_list_id,
1838 resource_list_assignment_id,
1839 resource_list_member_id,
1840 resource_id,
1841 txn_accum_id,
1842 project_id,
1843 task_id,
1844 creation_date,
1845 created_by,
1846 last_updated_by,
1847 last_update_date,
1848 last_update_login,
1849 request_id,
1850 program_application_id,
1851 program_id)
1852 SELECT
1853 l_resource_list_id,
1854 l_resource_list_assignment_id,
1855 pmt.resource_list_member_id,
1856 pmt.resource_id,
1857 pmt.system_reference1 txn_accum_id,
1858 pmt.system_reference2 project_id,
1859 pmt.system_reference3 task_id,
1860 SYSDATE,
1861 x_created_by, /* Global who columns initialized in spec of the package */
1862 x_last_updated_by,
1863 SYSDATE,
1864 x_last_update_login,
1865 x_request_id,
1866 x_program_application_id,
1867 x_program_id
1868 FROM PA_MAPPABLE_TXNS_TMP pmt
1869 WHERE NOT EXISTS
1870 (SELECT 'Yes'
1871 FROM pa_resource_accum_details rad
1872 WHERE resource_list_id = l_resource_list_id
1873 AND resource_list_assignment_id = l_resource_list_assignment_id
1874 AND txn_accum_id = pmt.system_reference1
1875 AND project_id = pmt.system_reference2
1876 AND task_id = pmt.system_reference3
1877 );
1878 EXCEPTION
1879 WHEN NULL_INSERT THEN
1880 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1881 pa_debug.debug('map_txns: ' || 'Trying to insert null into PA_RESORCE_ACCUM_DETAILS');
1882 END IF;
1883 FOR c2rec IN c2 LOOP
1884 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1885 pa_debug.debug('map_txns: ' || 'Resource id : ' || to_char(c2rec.resource_id));
1886 pa_debug.debug('map_txns: ' || 'Resource list member id : ' || to_char(c2rec.resource_list_member_id));
1887 pa_debug.debug('map_txns: ' || 'Person id : ' || to_char(c2rec.person_id));
1888 pa_debug.debug('map_txns: ' || 'Job id : ' || to_char(c2rec.job_id));
1889 pa_debug.debug('map_txns: ' || 'Organization id : ' || to_char(c2rec.organization_id));
1890 pa_debug.debug('map_txns: ' || 'Vendor_id : ' || to_char(c2rec.vendor_id));
1891 pa_debug.debug('map_txns: ' || 'Expenditure type : ' || c2rec.expenditure_type);
1892 pa_debug.debug('map_txns: ' || 'Event type : ' || c2rec.event_type);
1893 pa_debug.debug('map_txns: ' || 'Non labor resource : ' || c2rec.non_labor_resource);
1894 pa_debug.debug('map_txns: ' || 'Expenditure category : ' || c2rec.expenditure_category);
1895 pa_debug.debug('map_txns: ' || 'Revenue category : ' || c2rec.revenue_category);
1896 pa_debug.debug('map_txns: ' || 'Non-labor Resource org id : ' || to_char(c2rec.non_labor_resource_org_id));
1897 pa_debug.debug('map_txns: ' || 'Event Type Classification : ' || c2rec.event_type_classification);
1898 pa_debug.debug('map_txns: ' || 'System Linkage Function : ' || c2rec.system_linkage_function);
1899 pa_debug.debug('map_txns: ' || 'System ref1 : txn accum id : ' || c2rec.txn_accum_id);
1900 pa_debug.debug('map_txns: ' || 'System ref2 : project id : ' || c2rec.project_id);
1901 pa_debug.debug('map_txns: ' || 'System ref3 : task id : ' || c2rec.task_id);
1902 END IF;
1903 END LOOP;
1904 RAISE;
1905 WHEN OTHERS THEN
1906 RAISE;
1907 END;
1908
1909 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1910 pa_debug.debug('map_txns: ' || 'Inserted ' || sql%rowcount || ' rows into PA_RESOURCE_ACCUM_DETAILS');
1911 END IF;
1912
1913 COMMIT;
1914 END IF; /* PA_MAPPABLE_TXNS_TMP%rowcount check */
1915
1916 END LOOP;
1917 EXCEPTION
1918 WHEN OTHERS THEN
1919 IF x_err_code IS NULL THEN
1920 x_err_code := SQLCODE;
1921 END IF;
1922 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1923 pa_debug.debug('map_txns: ' || 'Error occurred at ' || x_err_stage || ' error code = ' || x_err_code);
1924 END IF;
1925 RAISE;
1926 END map_txns;
1927
1928 /* MAP_TRANSACTIONS Procedure : Created for bug# 1889671
1929 This process will update the RESOURCE_LIST_MEMBER_ID and RESOURCE_ID in
1930 table PA_MAPPABLE_TXNS_TMP table. Following needs to be done before a call
1931 to this API is made.
1932
1933 PA_MAPPABLE_TXNS_TMP table should have been populated for a single RESOURCE_LIST
1934 with all the transaction attributes with TXN_ID populated with a unique id.
1935
1936 Populate SYSTEM_REFERENCE1-5 columns are populated with unique identifiers
1937 which will be used by the calling program after the completion of the
1938 currenct process, i.e., after assigning the resources to the transactions.
1939 */
1940
1941 PROCEDURE new_map_txns
1942 (x_resource_list_id IN NUMBER,
1943 x_error_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1944 x_error_code OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
1945
1946 l_unclassified_rlm_id NUMBER;
1947 l_unclassified_res_id NUMBER;
1948 l_group_resource_type_id NUMBER;
1949 l_rl_job_group_id pa_resource_lists_all_bg.job_group_id%type;
1950
1951 /* According to guidelines from Performance group
1952 plsql table size should never exceed 200 */
1953
1954 l_plsql_max_array_size NUMBER := 200;
1955 l_prev_txn_id NUMBER := NULL;
1956 l_counter NUMBER; /* Used by plsql tables during their population */
1957
1958 TYPE l_resource_member_id_tbl_typ IS TABLE OF
1959 PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
1960 TYPE l_resource_id_tbl_typ IS TABLE OF
1961 PA_RESOURCE_LIST_MEMBERS.RESOURCE_ID%TYPE;
1962 TYPE l_txn_id_table_typ IS TABLE OF PA_MAPPABLE_TXNS_TMP.TXN_ID%TYPE;
1963
1964 l_resource_member_id_tbl L_RESOURCE_MEMBER_ID_TBL_TYP := L_RESOURCE_MEMBER_ID_TBL_TYP();
1965 l_resource_id_tbl L_RESOURCE_ID_TBL_TYP := L_RESOURCE_ID_TBL_TYP();
1966 l_txn_id_tbl L_TXN_ID_TABLE_TYP := L_TXN_ID_TABLE_TYP();
1967 l_uncategorized_flag PA_RESOURCE_LISTS_ALL_BG.UNCATEGORIZED_FLAG%type;
1968
1969 /* Logical Flow of this API
1970
1971 1. In case resource list to which mapping needs to be done is grouped.
1972 1.1 Insert all parents in resource list and their attributes into
1973 PA_RESOURCE_LIST_PARENTS_TMP temp table. Currently oracle projects allows
1974 resource list to be grouped only by organization, expenditure_category
1975 and revenue_category. Hence PA_RESOURCE_LIST_PARENTS_TMP table has only
1976 these three attributes.
1977
1978 1.2 Now assign parents to each transaction in PA_MAPPABLE_TXNS_TMP table. This can
1979 be done by matching organization, expenditure_category or revenue_category
1980 of the txn with that in PA_RESOURCE_LIST_PARENTS_TMP.
1981
1982 1.3 At this point if parent could not be assigned then the txn should be
1983 assigned to list level unclassified resource.
1984
1985 1.4 Now insert all possible child level resources in PA_TEMP_RES_MAPS_TMP table and
1986 their ranks in this table. This is done by matching all attributes of
1987 transactions with corresponding attribute of the child resource.
1988
1989 1.5 Fetch all the resources with highest (lowest in magnitude) rank in pl/sql
1990 tables and update PA_MAPPABLE_TXNS_TMP table with the resource id.
1991
1992 1.6 At this stage if no resource is assigned to any txn but parent is assigned
1993 then assign parent level unclassified resource to these transactions.
1994
1995 1.7 This is possible that a parent does not have any child under it. In such case
1996 it may not have any unclassified member under it also. In such case parent is
1997 the resource that should be assigned to the txn.
1998
1999 2. In case resource list not categorized then
2000 2.1 Insert all possible child level resources in PA_TEMP_RES_MAPS_TMP table and their
2001 ranks in this table. This is done by matching all attributes of transactions
2002 with corresponding attribute of the child resource.
2003
2004 2.2 Same as 1.5
2005
2006 2.3 If no resource is assigned to any txn then list level unclassified resource
2007 should be assigned to the txn.
2008 */
2009
2010 /* This cursor is used for processing in step 1.5 mentioned above */
2011
2012 CURSOR C1 IS
2013 SELECT txn_id
2014 ,resource_list_member_id
2015 ,resource_id
2016 FROM pa_temp_res_maps_tmp
2017 ORDER BY txn_id, rank; /* ORDER BY is important and should not be changed */
2018
2019 /* This cursor selects parent level unclassified members for the transactions.
2020 This cursor is used to achieve point 1.6 mentioned above.
2021 */
2022
2023 CURSOR C2 IS
2024 SELECT pmt.txn_id
2025 ,prlm.resource_list_member_id
2026 ,prlm.resource_id
2027 FROM pa_mappable_txns_tmp pmt
2028 ,pa_resource_list_members prlm
2029 WHERE pmt.resource_list_member_id is null /* A resource is not already assigned */
2030 AND pmt.parent_member_id is not null /* But a parent is assigned */
2031 AND pmt.parent_member_id = prlm.parent_member_id
2032 and nvl(prlm.migration_code,'-99') <> 'N'
2033 AND prlm.resource_type_code = 'UNCLASSIFIED';
2034
2035 BEGIN
2036
2037 x_error_stage := 'Start of new_map_txns : ' ||
2038 'Selecting group_resource_type_id ' ||
2039 'and list level unclassified member';
2040 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2041 pa_debug.debug('new_map_txns: ' || x_error_stage);
2042 END IF;
2043
2044 /* The following select should always return just one row, i.e.,
2045 one and only one list level unclassified resource should be
2046 present. With debug mode set to Yes, if the error stage is
2047 the one above, its an abnormal case of a list level unclassified
2048 resource not being present */
2049
2050 SELECT prl.group_resource_type_id
2051 , prlm.resource_list_member_id
2052 , prlm.resource_id
2053 , prl.job_group_id
2054 , nvl(prl.uncategorized_flag,'N')
2055 INTO l_group_resource_type_id
2056 , l_unclassified_rlm_id
2057 , l_unclassified_res_id
2058 , l_rl_job_group_id
2059 , l_uncategorized_flag
2060 FROM pa_resource_lists_all_bg prl
2061 ,pa_resource_list_members prlm
2062 WHERE prl.resource_list_id = x_resource_list_id
2063 AND prl.resource_list_id = prlm.resource_list_id
2064 and nvl(prl.migration_code,'-99') <> 'N'
2065 and nvl(prlm.migration_code,'-99') <> 'N'
2066 AND prlm.parent_member_id is NULL
2067 AND prlm.resource_type_code = decode(Nvl(prl.uncategorized_flag,'N'),
2068 'Y','UNCATEGORIZED','UNCLASSIFIED');
2069
2070 IF l_uncategorized_Flag = 'Y' THEN
2071 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2072 pa_debug.debug('new_map_txns: ' || 'Resource List is UNCATEGORIZED');
2073 END IF;
2074
2075 UPDATE pa_mappable_txns_tmp PMT
2076 SET resource_list_member_id = l_unclassified_rlm_id
2077 ,resource_id = l_unclassified_res_id
2078 WHERE PMT.resource_list_id = x_resource_list_id;
2079 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2080 pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
2081 END IF;
2082 x_error_stage := 'Mapping done';
2083 Return;
2084 END IF;
2085
2086 IF l_group_resource_type_id <> 0 THEN /* resource list is grouped */
2087
2088 /* Point 1.1 and 1.2 */
2089
2090 update_parents_mem_id(x_res_list_id => x_resource_list_id,
2091 x_err_stage => x_error_stage,
2092 x_err_code => x_error_code);
2093
2094 x_error_stage := 'In case no parent is determined assign list level unclassified';
2095 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2096 pa_debug.debug('new_map_txns: ' || x_error_stage);
2097 END IF;
2098
2099 /* Point 1.3 : If PARENT_MEMBER_ID is NULL even after the above update, then
2100 those txns will be mapped to resource level UNCLASSIFIED resource.
2101 Doing this update at this level will avoid selection of these records later
2102 and will improve the process throughput also.
2103 */
2104
2105
2106 UPDATE pa_mappable_txns_tmp PMT
2107 SET resource_list_member_id = l_unclassified_rlm_id
2108 ,resource_id = l_unclassified_res_id
2109 WHERE pmt.parent_member_id is null;
2110
2111 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2112 pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows with list level unclassified resource ');
2113 END IF;
2114
2115 /* Point 1.4 */
2116
2117 ins_temp_res_map_grp(x_res_list_id => x_resource_list_id,
2118 x_rl_job_grp_id => l_rl_job_group_id,
2119 x_err_stage => x_error_stage,
2120 x_err_code => x_error_code);
2121
2122 ELSE /* i.e. IF l_group_resource_type_id = 0 */
2123
2124 x_error_stage := 'Resource list is NOT grouped';
2125
2126 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2127 pa_debug.debug('new_map_txns: ' || x_error_stage);
2128 END IF;
2129
2130 /* Point 2.1 */
2131
2132 ins_temp_res_map_ungrp(x_res_list_id => x_resource_list_id,
2133 x_rl_job_grp_id => l_rl_job_group_id,
2134 x_err_stage => x_error_stage,
2135 x_err_code => x_error_code);
2136
2137 END IF; /* IF group_resource_type_id <> 0 */
2138
2139
2140 /* following bulk update logic is irrespective of whether resource list is categorized or not */
2141
2142 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2143 pa_debug.debug ('new_map_txns: ' || 'Update PA_MAPPABLE_TXNS_TMP table with resources assigned');
2144 END IF;
2145
2146 /* Bulk update has very consistent time for updations. It takes precisely 30 secs for 50000
2147 updates. The time does not vary whether we do bulk updates in batches of 200 records or in
2148 batches of 50000 records. Hence as per guidelines by performance team we are taking batch
2149 size of 200 (PL/SQL size should not increase this limit)
2150 */
2151
2152 x_error_stage := 'Initializing plsql tables';
2153
2154 l_txn_id_tbl.extend(l_plsql_max_array_size);
2155 l_resource_member_id_tbl.extend(l_plsql_max_array_size);
2156 l_resource_id_tbl.extend(l_plsql_max_array_size);
2157
2158 /* Point 1.5 and 2.2 : Just update PA_MAPPABLE_TXNS_TMP with records
2159 in plsql table and handle for every 200 records */
2160
2161 l_prev_txn_id := NULL;
2162 l_counter := 1;
2163
2164 x_error_stage := 'Starting loop for cursor c1';
2165
2166 FOR c1rec in c1 LOOP
2167
2168 IF (c1rec.txn_id <> nvl(l_prev_txn_id,-1)) THEN
2169
2170 l_txn_id_tbl(l_counter) := c1rec.txn_id;
2171 l_resource_member_id_tbl(l_counter) := c1rec.resource_list_member_id;
2172 l_resource_id_tbl(l_counter) := c1rec.resource_id;
2173 l_counter := l_counter + 1;
2174 l_prev_txn_id := c1rec.txn_id;
2175
2176 IF l_counter > l_plsql_max_array_size THEN
2177
2178 x_error_stage := 'Updating pa_mappable_txns_tmp with records in pl/sql tables';
2179
2180 FORALL i in l_resource_member_id_tbl.first..l_resource_member_id_tbl.last
2181 UPDATE pa_mappable_txns_tmp
2182 SET resource_list_member_id = l_resource_member_id_tbl(i)
2183 ,resource_id = l_resource_id_tbl(i)
2184 WHERE txn_id = l_txn_id_tbl(i);
2185
2186 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2187 pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
2188 END IF;
2189
2190 l_counter := 1;
2191 END IF;
2192
2193 END IF;
2194
2195 END LOOP;
2196
2197 IF l_counter > 1 THEN
2198
2199 x_error_stage := 'Updating if any more records left';
2200
2201 FORALL i in l_resource_member_id_tbl.first..(l_counter-1)
2202 UPDATE pa_mappable_txns_tmp
2203 SET resource_list_member_id = l_resource_member_id_tbl(i)
2204 ,resource_id = l_resource_id_tbl(i)
2205 WHERE txn_id = l_txn_id_tbl(i);
2206
2207 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2208 pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
2209 END IF;
2210
2211 END IF;
2212
2213 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2214 pa_debug.debug ('new_map_txns: ' || 'Bulk update done');
2215 END IF;
2216
2217 IF l_group_resource_type_id <> 0 THEN
2218
2219 /* Point 1.6 */
2220
2221 x_error_stage := 'Now update with parent level unclassified resource';
2222 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2223 pa_debug.debug('new_map_txns: ' || x_error_stage);
2224 END IF;
2225
2226 l_counter := 1;
2227
2228 /* Select txns with PARENT_MEMBER_ID populated and RESOURCE_LIST_MEMBER_ID not
2229 populated. These are to be mapped to resource parent level UNCLASSIFIED
2230 resources. Refer comments of C2 rec for comments on this loop */
2231
2232 /* Using bulk collect logic here */
2233
2234 x_error_stage := 'Opening cursor c2';
2235
2236 OPEN C2;
2237 LOOP
2238 x_error_stage := 'Doing bulk collect from c2';
2239 FETCH C2 BULK COLLECT INTO
2240 l_txn_id_tbl
2241 ,l_resource_member_id_tbl
2242 ,l_resource_id_tbl
2243 LIMIT l_plsql_max_array_size;
2244
2245 IF nvl(l_txn_id_tbl.last,0) >= 1 THEN /* only if something is fetched */
2246
2247 x_error_stage := 'Doing bulk update of pa_mappable_txns_tmp from data fetched from c2';
2248
2249 FORALL i in l_resource_member_id_tbl.first..l_resource_member_id_tbl.last
2250 UPDATE pa_mappable_txns_tmp
2251 SET resource_list_member_id = l_resource_member_id_tbl(i)
2252 ,resource_id = l_resource_id_tbl(i)
2253 WHERE txn_id = l_txn_id_tbl(i);
2254 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2255 pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
2256 END IF;
2257
2258 END IF;
2259
2260 EXIT WHEN nvl(l_txn_id_tbl.last,0) < l_plsql_max_array_size;
2261
2262 END LOOP;
2263 CLOSE C2; -- Bug#6320026
2264
2265 x_error_stage := 'Updating resource_list_member_id with parent_member_id ' ||
2266 'in case member_id is still null';
2267 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2268 pa_debug.debug ('new_map_txns: ' || x_error_stage);
2269 END IF;
2270
2271 /* If the RESOURCE_LIST_MEMBER_ID is NULL even at this stage, then assign the
2272 PARENT_MEMBER_ID as the RESOURCE_LIST_MEMBER_ID. Reason being, this is a
2273 categorized resource, and since there arent any children, assign the parent
2274 itself as the resource */
2275
2276 UPDATE pa_mappable_txns_tmp pmt
2277 set resource_list_member_id = parent_member_id
2278 ,resource_id = parent_resource_id
2279 WHERE pmt.resource_list_member_id is null
2280 AND pmt.parent_member_id is not null;
2281
2282 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2283 pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
2284 END IF;
2285
2286
2287 ELSE /* if resource list is not grouped */
2288
2289 /* Point 2.3 */
2290
2291 /* update all txns with resource list level unclassified resource */
2292
2293 x_error_stage := 'Updating unassigned txns to list level unclassified';
2294 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2295 pa_debug.debug('new_map_txns: ' || x_error_stage);
2296 END IF;
2297
2298 UPDATE pa_mappable_txns_tmp PMT
2299 SET resource_list_member_id = l_unclassified_rlm_id
2300 ,resource_id = l_unclassified_res_id
2301 WHERE pmt.resource_list_member_id is null;
2302
2303 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2304 pa_debug.debug('new_map_txns: ' || 'Updated ' || sql%rowcount || ' rows ');
2305 END IF;
2306
2307 END IF;/* resource list is grouped */
2308
2309 x_error_stage := 'Mapping done';
2310
2311 l_txn_id_tbl.delete;
2312 l_resource_member_id_tbl.delete;
2313 l_resource_id_tbl.delete;
2314
2315 EXCEPTION
2316 WHEN OTHERS THEN
2317 IF x_error_code is null THEN
2318 x_error_code := sqlcode;
2319 END IF;
2320 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2321 pa_debug.debug('new_map_txns: ' || 'Error occurred at ' || x_error_stage || ' errcode = ' || x_error_code);
2322 END IF;
2323 RAISE;
2324 END new_map_txns;
2325
2326 PROCEDURE update_parents_mem_id
2327 (x_res_list_id IN pa_resource_lists_all_bg.resource_list_id%type,
2328 x_err_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2329 x_err_code OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
2330 BEGIN
2331
2332 x_err_stage := 'Resource list is grouped : Deleting from resource list parents table';
2333 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2334 pa_debug.debug('update_parents_mem_id: ' || x_err_stage);
2335 END IF;
2336
2337 delete from pa_resource_list_parents_tmp;
2338
2339 /* PA_RESOURCE_LIST_PARENTS_TMP is a global temp table used by this process only.
2340 This table will have only one of the columns, either ORGANIZATION_ID or
2341 REVENUE_CATEGORY or EXPENDITURE_CATEGORY as NOT NULL, since PA_RESOURCE_LIST_MEMBERS
2342 can be grouped by any one of these three attributes only */
2343
2344 /* Point 1.1 */
2345
2346 x_err_stage := 'Inserting into pa_resource_list_parents_tmp table';
2347 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2348 pa_debug.debug('update_parents_mem_id: ' || x_err_stage);
2349 END IF;
2350
2351 INSERT INTO pa_resource_list_parents_tmp
2352 (resource_list_id
2353 ,resource_list_member_id
2354 ,resource_id
2355 ,organization_id
2356 ,expenditure_category
2357 ,revenue_category
2358 )
2359 ( SELECT
2360 prlm.resource_list_id
2361 ,prlm.resource_list_member_id
2362 ,prlm.resource_id
2363 ,prlm.organization_id
2364 ,prlm.expenditure_category
2365 ,prlm.revenue_category
2366 FROM pa_resource_list_members prlm
2367 WHERE prlm.parent_member_id is null
2368 AND prlm.resource_list_id = x_res_list_id
2369 and nvl(prlm.migration_code,'-99') <> 'N'
2370 AND prlm.enabled_flag = 'Y'
2371 );
2372
2373 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2374 pa_debug.debug('update_parents_mem_id: ' || 'Inserted ' || sql%rowcount || ' rows into pa_resource_list_parents_tmp');
2375 END IF;
2376
2377 /* Determine parent for each transaction in PA_MAPPABLE_TXNS_TMP.
2378 As parents can be only organizations, expenditure category or revenue category,
2379 and only one of the three attributes will be populated in the parents tables,
2380 one single update will do for resource lists grouped by any of these three
2381 */
2382
2383 /* Point 1.2 */
2384
2385 x_err_stage := 'Updating the parent member details in PA_MAPPABLE_TXNS_TMP';
2386 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2387 pa_debug.debug('update_parents_mem_id: ' || x_err_stage);
2388 END IF;
2389
2390 UPDATE pa_mappable_txns_tmp PMT
2391 SET (parent_member_id, parent_resource_id) =
2392 (SELECT resource_list_member_id, resource_id
2393 FROM pa_resource_list_parents_tmp PRLP
2394 WHERE (pmt.expenditure_category = prlp.expenditure_category
2395 OR pmt.organization_id = prlp.organization_id
2396 OR pmt.revenue_category = prlp.revenue_category)
2397 AND pmt.resource_list_id = prlp.resource_list_id);
2398
2399 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2400 pa_debug.debug('update_parents_mem_id: ' || 'Updated ' || sql%rowcount || ' rows in pa_mappable_txns_tmp with parent member details');
2401 END IF;
2402
2403
2404 EXCEPTION
2405 WHEN OTHERS THEN
2406 x_err_code := SQLCODE;
2407 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2408 pa_debug.debug('Procedure Update_Parents_Mem_Id' || x_err_stage || ' error code = ' || x_err_code);
2409 END IF;
2410 RAISE;
2411 END update_parents_mem_id;
2412
2413 PROCEDURE ins_temp_res_map_grp
2414 (x_res_list_id IN pa_resource_lists_all_bg.resource_list_id%type,
2415 x_rl_job_grp_id IN pa_resource_lists_all_bg.job_group_id%type,
2416 x_err_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2417 x_err_code OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
2418
2419 /* This cursor select distinct resource types defined in the resource list at
2420 child level. This cursor is used in point 1.4 in order to fire only those
2421 inserts for which resources are defined in the list.
2422 */
2423
2424 CURSOR C3 IS
2425 SELECT DISTINCT resource_type_code
2426 FROM pa_resource_list_members
2427 WHERE resource_list_id = x_res_list_id
2428 and nvl(migration_code,'-99') <> 'N'
2429 AND parent_member_id is not null;
2430
2431 BEGIN
2432
2433 x_err_stage := 'Deleting from pa_temp_res_maps_tmp table';
2434 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2435 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2436 END IF;
2437
2438 DELETE FROM pa_temp_res_maps_tmp;
2439
2440 /* Point 1.4 : In following statements we will insert records into temp table
2441 pa_temp_res_maps_tmp table. As one transaction can belong to multiple resources
2442 all such resources with txn_id and rank will be inserted into this table.
2443 Later only those resources will be picked up which have highest rank
2444 (lowest in magnitude).
2445
2446 Since resource list is grouped then we use the parent_member_id info already
2447 stamped on PA_MAPPABLE_TXNS_TMP table else we do not.
2448 */
2449
2450 FOR C3REC in C3 LOOP
2451 IF C3REC.RESOURCE_TYPE_CODE = 'EMPLOYEE' THEN
2452
2453 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2454 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2455 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2456 END IF;
2457
2458 /* During prototyping it has been found that these 8 nuclear inserts work much faster than
2459 one insert having 8 conditions. The one single insert with all 8 conditions combined
2460 took hours to come back while these 8 inserts did the same job in few seconds.
2461 These inserts are not modified to dynamic inserts because of performance reasons.
2462 */
2463
2464 INSERT INTO pa_temp_res_maps_tmp
2465 (SELECT txn_id
2466 , prlm.resource_list_member_id
2467 , prlm.resource_id,
2468 prfr.rank
2469 FROM pa_mappable_txns_tmp temp
2470 ,pa_resource_list_members prlm
2471 ,pa_resource_format_ranks prfr
2472 WHERE temp.parent_member_id = prlm.parent_member_id
2473 AND prlm.person_id = temp.person_id
2474 AND prlm.resource_format_id = prfr.resource_format_id
2475 and prlm.resource_list_id = x_res_list_id
2476 AND prlm.enabled_flag = 'Y'
2477 and nvl(prlm.migration_code,'-99') <> 'N'
2478 AND prlm.parent_member_id is not null
2479 AND prlm.person_id is not null
2480 AND temp.resource_list_member_id is null /* resource is not already determined */
2481 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2482
2483 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2484 pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2485 END IF;
2486
2487 ELSIF C3REC.RESOURCE_TYPE_CODE = 'JOB' THEN
2488
2489 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2490 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2491 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2492 END IF;
2493
2494 INSERT INTO pa_temp_res_maps_tmp
2495 (SELECT txn_id
2496 , prlm.resource_list_member_id
2497 , prlm.resource_id,
2498 prfr.rank
2499 FROM pa_mappable_txns_tmp temp
2500 ,pa_resource_list_members prlm
2501 ,pa_resource_format_ranks prfr
2502 WHERE temp.parent_member_id = prlm.parent_member_id
2503 AND prlm.job_id = PA_Cross_Business_Grp.IsMappedToJob(temp.job_id,x_rl_job_grp_id)
2504 AND prlm.resource_format_id = prfr.resource_format_id
2505 and prlm.resource_list_id = x_res_list_id
2506 AND prlm.parent_member_id is not null
2507 and nvl(prlm.migration_code,'-99') <> 'N'
2508 AND prlm.enabled_flag = 'Y'
2509 AND prlm.job_id is not null
2510 AND temp.resource_list_member_id is null /* resource is not already determined */
2511 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2512
2513 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2514 pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2515 END IF;
2516
2517 ELSIF C3REC.RESOURCE_TYPE_CODE = 'ORGANIZATION' THEN
2518
2519 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2520 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2521 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2522 END IF;
2523
2524 INSERT INTO pa_temp_res_maps_tmp
2525 (SELECT txn_id
2526 , prlm.resource_list_member_id
2527 , prlm.resource_id
2528 , prfr.rank
2529 FROM pa_mappable_txns_tmp TEMP
2530 ,pa_resource_list_members PRLM
2531 ,pa_resource_format_ranks prfr
2532 WHERE temp.parent_member_id = prlm.parent_member_id
2533 AND prlm.organization_id = temp.organization_id
2534 AND prlm.resource_format_id = prfr.resource_format_id
2535 and prlm.resource_list_id = x_res_list_id
2536 and nvl(prlm.migration_code,'-99') <> 'N'
2537 AND prlm.parent_member_id is not null
2538 AND prlm.enabled_flag = 'Y'
2539 AND prlm.organization_id is not null
2540 AND temp.resource_list_member_id is null /* resource is not already determined */
2541 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2542
2543 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2544 pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2545 END IF;
2546
2547 ELSIF C3REC.RESOURCE_TYPE_CODE = 'REVENUE_CATEGORY' THEN
2548
2549 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2550 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2551 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2552 END IF;
2553
2554 INSERT INTO pa_temp_res_maps_tmp
2555 (SELECT txn_id
2556 , prlm.resource_list_member_id
2557 , prlm.resource_id
2558 , prfr.rank
2559 FROM pa_mappable_txns_tmp TEMP
2560 ,pa_resource_list_members PRLM
2561 ,pa_resource_format_ranks prfr
2562 WHERE temp.parent_member_id = prlm.parent_member_id
2563 AND prlm.revenue_category = temp.revenue_category
2564 AND prlm.resource_format_id = prfr.resource_format_id
2565 and prlm.resource_list_id = x_res_list_id
2566 AND prlm.parent_member_id is not null
2567 and nvl(prlm.migration_code,'-99') <> 'N'
2568 AND prlm.enabled_flag = 'Y'
2569 AND prlm.revenue_category is not null
2570 AND temp.resource_list_member_id is null /* resource is not already determined */
2571 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2572
2573 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2574 pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2575 END IF;
2576
2577 ELSIF C3REC.RESOURCE_TYPE_CODE = 'VENDOR' THEN
2578
2579 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2580 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2581 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2582 END IF;
2583
2584 INSERT INTO pa_temp_res_maps_tmp
2585 (SELECT txn_id
2586 , prlm.resource_list_member_id
2587 , prlm.resource_id
2588 , prfr.rank
2589 FROM pa_mappable_txns_tmp TEMP
2590 ,pa_resource_list_members PRLM
2591 ,pa_resource_format_ranks prfr
2592 WHERE temp.parent_member_id = prlm.parent_member_id
2593 AND prlm.vendor_id = temp.vendor_id
2594 AND prlm.resource_format_id = prfr.resource_format_id
2595 and prlm.resource_list_id = x_res_list_id
2596 AND prlm.parent_member_id is not null
2597 and nvl(prlm.migration_code,'-99') <> 'N'
2598 AND prlm.enabled_flag = 'Y'
2599 AND prlm.vendor_id is not null
2600 AND temp.resource_list_member_id is null /* resource is not already determined */
2601 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2602
2603 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2604 pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2605 END IF;
2606
2607 ELSIF C3REC.RESOURCE_TYPE_CODE = 'EXPENDITURE_TYPE' THEN
2608
2609 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2610 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2611 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2612 END IF;
2613
2614 INSERT INTO pa_temp_res_maps_tmp
2615 (SELECT txn_id
2616 , prlm.resource_list_member_id
2617 , prlm.resource_id
2618 , prfr.rank
2619 FROM pa_mappable_txns_tmp TEMP
2620 ,pa_resource_list_members PRLM
2621 ,pa_resource_format_ranks prfr
2622 WHERE temp.parent_member_id = prlm.parent_member_id
2623 AND prlm.expenditure_type = temp.expenditure_type
2624 AND prlm.resource_format_id = prfr.resource_format_id
2625 and prlm.resource_list_id = x_res_list_id
2626 AND prlm.enabled_flag = 'Y'
2627 and nvl(prlm.migration_code,'-99') <> 'N'
2628 AND prlm.expenditure_type is not null
2629 AND prlm.parent_member_id is not null
2630 AND temp.resource_list_member_id is null /* resource is not already determined */
2631 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2632
2633 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2634 pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2635 END IF;
2636
2637 ELSIF C3REC.RESOURCE_TYPE_CODE = 'EXPENDITURE_CATEGORY' THEN
2638
2639 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2640 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2641 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2642 END IF;
2643
2644 INSERT INTO pa_temp_res_maps_tmp
2645 (SELECT txn_id
2646 , prlm.resource_list_member_id
2647 , prlm.resource_id
2648 , prfr.rank
2649 FROM pa_mappable_txns_tmp TEMP
2650 ,pa_resource_list_members PRLM
2651 ,pa_resource_format_ranks prfr
2652 WHERE temp.parent_member_id = prlm.parent_member_id
2653 AND prlm.expenditure_category = temp.expenditure_category
2654 AND prlm.resource_format_id = prfr.resource_format_id
2655 and prlm.resource_list_id = x_res_list_id
2656 AND prlm.enabled_flag = 'Y'
2657 and nvl(prlm.migration_code,'-99') <> 'N'
2658 AND prlm.expenditure_category is not null
2659 AND prlm.parent_member_id is not null
2660 AND temp.resource_list_member_id is null /* resource is not already determined */
2661 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2662
2663 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2664 pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2665 END IF;
2666
2667 ELSIF C3REC.RESOURCE_TYPE_CODE = 'EVENT_TYPE' THEN
2668
2669 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2670 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2671 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2672 END IF;
2673
2674 INSERT INTO pa_temp_res_maps_tmp
2675 (SELECT txn_id
2676 , prlm.resource_list_member_id
2677 , prlm.resource_id
2678 , prfr.rank
2679 FROM pa_mappable_txns_tmp TEMP
2680 ,pa_resource_list_members PRLM
2681 ,pa_resource_format_ranks prfr
2682 WHERE temp.parent_member_id = prlm.parent_member_id
2683 AND prlm.event_type = temp.event_type
2684 AND prlm.resource_format_id = prfr.resource_format_id
2685 and prlm.resource_list_id = x_res_list_id
2686 AND prlm.parent_member_id is not null
2687 and nvl(prlm.migration_code,'-99') <> 'N'
2688 AND prlm.enabled_flag = 'Y'
2689 AND prlm.event_type is not null
2690 AND temp.resource_list_member_id is null /* resource is not already determined */
2691 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2692
2693 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2694 pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2695 END IF;
2696
2697 ELSIF C3REC.RESOURCE_TYPE_CODE = 'PROJECT_ROLE' THEN
2698
2699 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2700 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2701 pa_debug.debug('ins_temp_res_map_grp: ' || x_err_stage);
2702 END IF;
2703
2704 INSERT INTO pa_temp_res_maps_tmp
2705 (SELECT txn_id
2706 , prlm.resource_list_member_id
2707 , prlm.resource_id
2708 , prfr.rank
2709 FROM pa_mappable_txns_tmp TEMP
2710 ,pa_resource_list_members PRLM
2711 ,pa_resource_format_ranks prfr
2712 WHERE temp.parent_member_id = prlm.parent_member_id
2713 AND prlm.project_role_id = temp.project_role_id
2714 AND prlm.resource_format_id = prfr.resource_format_id
2715 and prlm.resource_list_id = x_res_list_id
2716 AND prlm.parent_member_id is not null
2717 and nvl(prlm.migration_code,'-99') <> 'N'
2718 AND prlm.enabled_flag = 'Y'
2719 AND prlm.project_role_id is not null
2720 AND temp.resource_list_member_id is null /* resource is not already determined */
2721 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2722
2723 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2724 pa_debug.debug('ins_temp_res_map_grp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2725 END IF;
2726
2727 END IF;
2728 END LOOP;
2729 EXCEPTION
2730 WHEN OTHERS THEN
2731 x_err_code := SQLCODE;
2732 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2733 pa_debug.debug('Procedure Ins_Temp_Res_Map_Grp ' || x_err_stage || ' error code = ' || x_err_code);
2734 END IF;
2735 RAISE;
2736 END ins_temp_res_map_grp;
2737
2738 PROCEDURE ins_temp_res_map_ungrp
2739 (x_res_list_id IN pa_resource_lists_all_bg.resource_list_id%type,
2740 x_rl_job_grp_id IN pa_resource_lists_all_bg.job_group_id%type,
2741 x_err_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2742 x_err_code OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
2743
2744 /* This cursor select distinct resource types defined in the resource list at
2745 child level. This cursor is used in point 2.1 in order to fire only those
2746 inserts for which resources are defined in the list.
2747 */
2748
2749 CURSOR C3 IS
2750 SELECT DISTINCT resource_type_code
2751 FROM pa_resource_list_members
2752 WHERE resource_list_id = x_res_list_id
2753 and nvl(migration_code,'-99') <> 'N';
2754
2755 BEGIN
2756 x_err_stage := 'Deleting from pa_temp_res_maps_tmp table';
2757 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2758 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
2759 END IF;
2760
2761 DELETE FROM pa_temp_res_maps_tmp;
2762
2763 /* Point 2.1 : In following statements we will insert records into temp table
2764 pa_temp_res_maps_tmp table. As one transaction can belong to multiple resources
2765 all such resources with txn_id and rank will be inserted into this table.
2766 Later only those resources will be picked up which have highest rank
2767 (lowest in magnitude).
2768 */
2769
2770 /* The only difference in the INSERTs for categorized and uncategorized resource
2771 lists, is the TEMP.PARENT_MEMBER_ID = PRLM.PARENT_MEMBER_ID condition.
2772 Uncategorized resource lists will not have the PARENT_MEMBER_ID populated
2773 */
2774
2775 FOR C3REC in C3 LOOP
2776 IF C3REC.RESOURCE_TYPE_CODE = 'EMPLOYEE' THEN
2777
2778 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2779 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2780 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
2781 END IF;
2782
2783 INSERT INTO pa_temp_res_maps_tmp
2784 (SELECT txn_id
2785 , prlm.resource_list_member_id
2786 , prlm.resource_id
2787 , prfr.rank
2788 FROM pa_mappable_txns_tmp TEMP
2789 ,pa_resource_list_members PRLM
2790 ,pa_resource_format_ranks prfr
2791 WHERE prlm.person_id = temp.person_id
2792 AND prlm.resource_format_id = prfr.resource_format_id
2793 AND temp.resource_list_id = prlm.resource_list_id
2794 and prlm.resource_list_id = x_res_list_id
2795 AND prlm.enabled_flag = 'Y'
2796 and nvl(prlm.migration_code,'-99') <> 'N'
2797 AND prlm.person_id is not null
2798 AND temp.resource_list_member_id is null /* resource is not already determined */
2799 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2800
2801 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2802 pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2803 END IF;
2804
2805 ELSIF C3REC.RESOURCE_TYPE_CODE = 'JOB' THEN
2806
2807 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2808 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2809 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
2810 END IF;
2811
2812 INSERT INTO pa_temp_res_maps_tmp
2813 (SELECT txn_id
2814 , prlm.resource_list_member_id
2815 , prlm.resource_id
2816 , prfr.rank
2817 FROM pa_mappable_txns_tmp TEMP
2818 ,pa_resource_list_members PRLM
2819 ,pa_resource_format_ranks prfr
2820 WHERE prlm.job_id = PA_Cross_Business_Grp.IsMappedToJob(temp.job_id,x_rl_job_grp_id)
2821 AND prlm.resource_format_id = prfr.resource_format_id
2822 AND temp.resource_list_id = prlm.resource_list_id
2823 and prlm.resource_list_id = x_res_list_id
2824 AND prlm.enabled_flag = 'Y'
2825 and nvl(prlm.migration_code,'-99') <> 'N'
2826 AND prlm.job_id is not null
2827 AND temp.resource_list_member_id is null /* resource is not already determined */
2828 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2829
2830 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2831 pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2832 END IF;
2833
2834 ELSIF C3REC.RESOURCE_TYPE_CODE = 'ORGANIZATION' THEN
2835
2836 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2837 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2838 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
2839 END IF;
2840
2841 INSERT INTO pa_temp_res_maps_tmp
2842 (SELECT txn_id
2843 , prlm.resource_list_member_id
2844 , prlm.resource_id
2845 , prfr.rank
2846 FROM pa_mappable_txns_tmp TEMP
2847 ,pa_resource_list_members PRLM
2848 ,pa_resource_format_ranks prfr
2849 WHERE prlm.organization_id = temp.organization_id
2850 AND prlm.resource_format_id = prfr.resource_format_id
2851 AND temp.resource_list_id = prlm.resource_list_id
2852 and prlm.resource_list_id = x_res_list_id
2853 AND prlm.enabled_flag = 'Y'
2854 and nvl(prlm.migration_code,'-99') <> 'N'
2855 AND prlm.organization_id is not null
2856 AND temp.resource_list_member_id is null /* resource is not already determined */
2857 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2858
2859 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2860 pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2861 END IF;
2862
2863 ELSIF C3REC.RESOURCE_TYPE_CODE = 'REVENUE_CATEGORY' THEN
2864
2865 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2866 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2867 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
2868 END IF;
2869
2870 INSERT INTO pa_temp_res_maps_tmp
2871 (SELECT txn_id
2872 , prlm.resource_list_member_id
2873 , prlm.resource_id
2874 , prfr.rank
2875 FROM pa_mappable_txns_tmp TEMP
2876 ,pa_resource_list_members PRLM
2877 ,pa_resource_format_ranks prfr
2878 WHERE prlm.revenue_category = temp.revenue_category
2879 AND prlm.resource_format_id = prfr.resource_format_id
2880 AND temp.resource_list_id = prlm.resource_list_id
2881 and prlm.resource_list_id = x_res_list_id
2882 AND prlm.enabled_flag = 'Y'
2883 and nvl(prlm.migration_code,'-99') <> 'N'
2884 AND prlm.revenue_category is not null
2885 AND temp.resource_list_member_id is null /* resource is not already determined */
2886 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2887
2888 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2889 pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2890 END IF;
2891
2892 ELSIF C3REC.RESOURCE_TYPE_CODE = 'VENDOR' THEN
2893
2894 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2895 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2896 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
2897 END IF;
2898
2899 INSERT INTO pa_temp_res_maps_tmp
2900 (SELECT txn_id
2901 , prlm.resource_list_member_id
2902 , prlm.resource_id
2903 , prfr.rank
2904 FROM pa_mappable_txns_tmp TEMP
2905 ,pa_resource_list_members PRLM
2906 ,pa_resource_format_ranks prfr
2907 WHERE prlm.vendor_id = temp.vendor_id
2908 AND prlm.resource_format_id = prfr.resource_format_id
2909 AND temp.resource_list_id = prlm.resource_list_id
2910 and prlm.resource_list_id = x_res_list_id
2911 AND prlm.enabled_flag = 'Y'
2912 and nvl(prlm.migration_code,'-99') <> 'N'
2913 AND prlm.vendor_id is not null
2914 AND temp.resource_list_member_id is null /* resource is not already determined */
2915 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2916
2917 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2918 pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2919 END IF;
2920
2921 ELSIF C3REC.RESOURCE_TYPE_CODE = 'EXPENDITURE_TYPE' THEN
2922
2923 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2924 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2925 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
2926 END IF;
2927
2928 INSERT INTO pa_temp_res_maps_tmp
2929 (SELECT txn_id
2930 , prlm.resource_list_member_id
2931 , prlm.resource_id
2932 , prfr.rank
2933 FROM pa_mappable_txns_tmp TEMP
2934 ,pa_resource_list_members PRLM
2935 ,pa_resource_format_ranks prfr
2936 WHERE prlm.expenditure_type = temp.expenditure_type
2937 AND prlm.resource_format_id = prfr.resource_format_id
2938 AND temp.resource_list_id = prlm.resource_list_id
2939 and prlm.resource_list_id = x_res_list_id
2940 AND prlm.enabled_flag = 'Y'
2941 and nvl(prlm.migration_code,'-99') <> 'N'
2942 AND prlm.expenditure_type is not null
2943 AND temp.resource_list_member_id is null /* resource is not already determined */
2944 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2945
2946 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2947 pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2948 END IF;
2949
2950 ELSIF C3REC.RESOURCE_TYPE_CODE = 'EXPENDITURE_CATEGORY' THEN
2951
2952 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2953 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2954 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
2955 END IF;
2956
2957 INSERT INTO pa_temp_res_maps_tmp
2958 (SELECT txn_id
2959 , prlm.resource_list_member_id
2960 , prlm.resource_id
2961 , prfr.rank
2962 FROM pa_mappable_txns_tmp TEMP
2963 ,pa_resource_list_members PRLM
2964 ,pa_resource_format_ranks prfr
2965 WHERE prlm.expenditure_category = temp.expenditure_category
2966 AND prlm.resource_format_id = prfr.resource_format_id
2967 AND temp.resource_list_id = prlm.resource_list_id
2968 and prlm.resource_list_id = x_res_list_id
2969 AND prlm.enabled_flag = 'Y'
2970 and nvl(prlm.migration_code,'-99') <> 'N'
2971 AND prlm.expenditure_category is not null
2972 AND temp.resource_list_member_id is null /* resource is not already determined */
2973 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
2974
2975 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2976 pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
2977 END IF;
2978
2979 ELSIF C3REC.RESOURCE_TYPE_CODE = 'EVENT_TYPE' THEN
2980
2981 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
2982 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2983 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
2984 END IF;
2985
2986 INSERT INTO pa_temp_res_maps_tmp
2987 (SELECT txn_id
2988 , prlm.resource_list_member_id
2989 , prlm.resource_id
2990 , prfr.rank
2991 FROM pa_mappable_txns_tmp TEMP
2992 ,pa_resource_list_members PRLM
2993 ,pa_resource_format_ranks prfr
2994 WHERE prlm.event_type = temp.event_type
2995 AND prlm.resource_format_id = prfr.resource_format_id
2996 AND temp.resource_list_id = prlm.resource_list_id
2997 and prlm.resource_list_id = x_res_list_id
2998 AND prlm.enabled_flag = 'Y'
2999 and nvl(prlm.migration_code,'-99') <> 'N'
3000 AND prlm.event_type is not null
3001 AND temp.resource_list_member_id is null /* resource is not already determined */
3002 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
3003
3004 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3005 pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
3006 END IF;
3007
3008 ELSIF C3REC.RESOURCE_TYPE_CODE = 'PROJECT_ROLE' THEN
3009
3010 x_err_stage := ('Inserting into pa_temp_res_maps_tmp for ' || C3REC.RESOURCE_TYPE_CODE);
3011 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3012 pa_debug.debug('ins_temp_res_map_ungrp: ' || x_err_stage);
3013 END IF;
3014
3015 INSERT INTO pa_temp_res_maps_tmp
3016 (SELECT txn_id
3017 , prlm.resource_list_member_id
3018 , prlm.resource_id
3019 , prfr.rank
3020 FROM pa_mappable_txns_tmp TEMP
3021 ,pa_resource_list_members PRLM
3022 ,pa_resource_format_ranks prfr
3023 WHERE prlm.project_role_id = temp.project_role_id
3024 AND prlm.resource_format_id = prfr.resource_format_id
3025 AND temp.resource_list_id = prlm.resource_list_id
3026 and prlm.resource_list_id = x_res_list_id
3027 AND prlm.enabled_flag = 'Y'
3028 and nvl(prlm.migration_code,'-99') <> 'N'
3029 AND prlm.project_role_id is not null
3030 AND temp.resource_list_member_id is null /* resource is not already determined */
3031 AND NVL(temp.event_type_classification,temp.system_linkage_function) = prfr.txn_class_code);
3032
3033 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3034 pa_debug.debug('ins_temp_res_map_ungrp: ' || 'Inserted ' || sql%rowcount || ' rows ');
3035 END IF;
3036
3037 END IF; /* If C3REC.RESOURCE_TYPE_CODE = 'EMPLOYEE' */
3038 END LOOP;
3039 EXCEPTION
3040 WHEN OTHERS THEN
3041 x_err_code := SQLCODE;
3042 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3043 pa_debug.debug('Procedure Ins_Temp_Res_Map_Ungrp ' || x_err_stage || ' error code = ' || x_err_code);
3044 END IF;
3045 RAISE;
3046 END ins_temp_res_map_ungrp;
3047
3048
3049 END PA_RES_ACCUMS;
[Home] [Help]