1 PACKAGE BODY GMS_RES_MAP AS
2 -- $Header: gmsfcrmb.pls 120.2 2006/02/22 21:51:23 rshaik ship $
3
4 /* -----------------------------------------------------------------------
5 || ************** NEW CODE FOR RESOURCE MAPPING STARTS HERE **************
6 ---------------------------------------------------------------------- */
7
8 -- ## This procedure is used to derive : Group Level Unclassified RLMI
9
10 Procedure get_grp_unclassified(p_resource_list_id in number,
11 p_resource_list_member_id out NOCOPY number,
12 p_error_code out NOCOPY number,
13 p_error_buff out NOCOPY varchar2)
14 is
15
16 Begin
17
18 select a.resource_list_member_id
19 into p_resource_list_member_id
20 from pa_resource_list_members a,
21 pa_resources b,
22 pa_resource_types c
23 where c.resource_type_code = 'UNCLASSIFIED'
24 and b.resource_type_id = c.resource_type_id
25 and a.resource_id = b.resource_id
26 and a.resource_list_id = p_resource_list_id
27 and a.parent_member_id is null
28 and a.enabled_flag='Y'
29 and NVL(a.migration_code,'M') ='M'; --Bug 3626671
30
31 Exception
32
33 When others then
34
35 p_resource_list_member_id := null;
36
37 End get_grp_unclassified;
38
39
40 /* ---------------------------------------------------------------------------
41 || Procedure "get_parent_rlmi" is used to derive : RLMI of the Resource Group
42 || Resource Group could be :
43 || 1. Expenditure Category
44 || 2. Revenue Category
45 || 3. Organization
46 --------------------------------------------------------------------------- */
47
48 Procedure get_parent_rlmi(p_group_resource_type_id in number,
49 p_name in varchar2,
50 p_resource_list_id in number,
51 p_parent_rlmi out NOCOPY number,
52 p_error_code out NOCOPY number,
53 p_error_buff out NOCOPY varchar2)
54 is
55 Begin
56
57 Select prlm.resource_list_member_id
58 into p_parent_rlmi
59 from pa_resource_list_members prlm,
60 pa_resources pr
61 where pr.resource_type_id = p_group_resource_type_id
62 and pr.name = p_name
63 and prlm.resource_id = pr.resource_id
64 and prlm.resource_list_id = p_resource_list_id
65 and prlm.enabled_flag='Y'
66 and NVL(prlm.migration_code,'M') ='M'; --Bug 3626671
67
68 Exception
69
70 When no_data_found then
71
72 p_parent_rlmi := -1;
73
74 When Others then
75
76 p_parent_rlmi := -1;
77
78 End get_parent_rlmi;
79
80
81 /* -----------------------------------------------------------------------------------
82 || Procedure "MAP_RESOURCES" is the main API to derive RLMI
83 || Parameters:
84 || A. IN Parameters:
85 || ==============
86 || 1. x_document_type - Document Type : EXP,ENC,AP,PO,REQ,EVT(Event), MANDATORY VALUE
87 || 2. x_document_header_id - Document Header Id (e.g: Expenditure_item_id for EXP/ENC)
88 || 3. x_document_distribution_id - Document Distribution Id (relevant for AP/PO/REQ)
89 || 4. x_expenditure_type - Expenditure Type
90 || 5. x_expenditure_org_id - Organization Id
91 || 6. x_categorization_code - Categorization Code : Values 'R' or 'N'
92 || 7. x_resource_list_id - Resource List Id
93 || 8. x_event_type - Event Type
94 ||
95 || B. IN OUT NOCOPY Parameters:
96 || =====================
97 || These IN/OUT parameters are helpful in batch mode(i.e. when Resource mapping API
98 || is being called in a loop. API passes the next four values. For the subsequent
99 || transactions, API will check if x_prev_list_processed (resource list for previous
100 || transaction) same as for current, if so it does not derive the next 3 IN/OUT parameter
101 || values.
102 || %%% WARNING %%% :
103 || =================
104 || Programmers using this API should not initialize the values of these
105 || IN/OUT parameters within the loop.
106 || 1. x_prev_list_processed - Resource List Id of the previous transaction
107 || 2. x_group_resource_type_id - Group Resource Id for the resource list
108 || This has a value of zero, if resource list not grouped.
109 || 3. x_group_resource_type_name - Group Resource name for the resource list
110 || 4. resource_type_tab - This pl/sql table stores the different resource types
111 || in a resource list. Define plsql table resource_type_tab of type
112 || "gms_res_map.resource_type_table"
113 || C. OUT NOCOPY Parameters:
114 || ==================
115 || 1. x_resource_list_member_id - Derived RLMI
116 || 2. x_error_code - Has a value other than zero in case of exception
117 || 3. x_error_buff - Has a value in case of exception
118 ||
119 || K.Biju .. Dated 27-MAR-2001
123 x_document_header_id IN number default NULL,
120 ----------------------------------------------------------------------------------- */
121
122 Procedure map_resources(x_document_type IN varchar2,
124 x_document_distribution_id IN number default NULL,
125 x_expenditure_type IN varchar2 default NULL,
126 x_expenditure_org_id IN number default NULL,
127 x_categorization_code IN varchar2 default NULL,
128 x_resource_list_id IN number default NULL,
129 x_event_type IN varchar2 default NULL,
130 x_prev_list_processed IN OUT NOCOPY number,
131 x_group_resource_type_id IN OUT NOCOPY number,
132 x_group_resource_type_name IN OUT NOCOPY varchar2,
133 resource_type_tab IN OUT NOCOPY gms_res_map.resource_type_table,
134 x_resource_list_member_id OUT NOCOPY number,
135 x_error_code OUT NOCOPY number,
136 x_error_buff OUT NOCOPY varchar2)
137 IS
138
139 l_stage varchar2(50);
140 l_count number(2) := 0;
141 l_rowcount number(2) := 0;
142 l_vendor_id number(30);
143 l_resource_type varchar2(100);
144 l_expenditure_type varchar2(100);
145 l_expenditure_category_tmp varchar2(100);
146 l_revenue_category_tmp varchar2(100);
147 l_expenditure_category varchar2(100);
148 l_revenue_category varchar2(100);
149 --l_organization_name varchar2(100);
150 --The width of the variable is changed for UTF8 changes for HRMS schema. Refer bug 2302839.
151 l_organization_name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
152 l_person_name varchar2(100);
153 l_job_title varchar2(100);
154 -- The length of l_vendor_name has been changed for utf8 changes for AP schema. Refer bug 2614745.
155 --l_vendor_name varchar2(100);
156 l_vendor_name PO_VENDORS.VENDOR_NAME%TYPE;
157 l_event_type varchar2(100);
158 l_parent_rlmi number(30);
159
160 -- ## Following Cursor pulls up all the resource types for that resource list
161 -- This cursor has been modified as part of Bug reference : 3631208
162
163 Cursor get_resource_types is
164 select distinct c.resource_type_code resource_type_code
165 from pa_resource_list_members c
166 where c.resource_list_id = x_resource_list_id
167 and ((x_group_resource_type_id <> 0 and c.parent_member_id is not null)
168 or
169 (x_group_resource_type_id = 0 and c.parent_member_id is null)
170 )
171 and c.enabled_flag='Y'
172 and c.resource_type_code <> 'UNCLASSIFIED'
173 and NVL(c.migration_code,'M') ='M';
174
175 /* -------------------------------- Bug reference : 3631208 -------------------+
176 -- Cursor modified as above
177 select distinct a.resource_type_code resource_type_code
178 from pa_resource_types a,
179 pa_resources b,
180 pa_resource_list_members c
181 where c.resource_list_id = x_resource_list_id
182 and b.resource_id = c.resource_id
183 and a.resource_type_id = b.resource_type_id
184 and ((x_group_resource_type_id <> 0 and c.parent_member_id is not null)
185 or
186 (x_group_resource_type_id = 0 and c.parent_member_id is null)
187 )
188 and c.enabled_flag='Y'
189 and a.resource_type_code <> 'UNCLASSIFIED'
190 and NVL(c.migration_code,'M') ='M'; --Bug 3626671
191
192 -- ## Following Cursor pulls up all resource list members, records are sorted
193 -- ## so that we get RLMI for the most granular resource
194 -- This cursor changed to select statement .. Bug reference : 3631208
195 Cursor get_rlmis_unclass is
196 select c.resource_list_member_id,
197 b.name,
198 a.resource_type_code,
199 b.resource_type_id,
200 c.parent_member_id
201 from pa_resource_types a,
202 pa_resources b,
203 pa_resource_list_members c
204 where c.resource_list_id = x_resource_list_id
205 and b.resource_id = c.resource_id
206 and a.resource_type_id = b.resource_type_id
207 and c.enabled_flag='Y'
208 and a.resource_type_code='UNCLASSIFIED'
209 and NVL(c.migration_code,'M') ='M'; --Bug 3626671
210 */
211
212 Cursor get_rlmis_class(p_resource_type_code in varchar2, p_name in varchar2) is
213 select c.resource_list_member_id,
214 b.name,
215 a.resource_type_code,
216 b.resource_type_id,
217 c.parent_member_id
218 from pa_resource_types a,
219 pa_resources b,
220 pa_resource_list_members c
221 where c.resource_list_id = x_resource_list_id
222 and b.resource_id = c.resource_id
223 and a.resource_type_id = b.resource_type_id
224 and c.enabled_flag='Y'
225 and a.resource_type_code=p_resource_type_code
226 and b.name = p_name
227 and NVL(c.migration_code,'M') ='M'; -- Bug 3626671
228
229 TYPE rlmi_record is RECORD(resource_list_member_id NUMBER(15),
230 name VARCHAR2(100),
231 resource_type_code VARCHAR2(30),
232 resource_type_id NUMBER(15),
233 parent_member_id NUMBER(15));
234
235 TYPE rlmi_table is TABLE of rlmi_record index by binary_integer;
236
237 rlmi_tab rlmi_table;
238
239 Begin
240 x_error_code := 0;
241 l_stage := 'Starting Resource mapping';
242
243 -- dbms_output.put_line('In Mapping');
244 -- ## DO NOT DELETE THE dbms_output lines from the code
245 -- ## This has been introduced for debugging purpose only
246 -- ## The checked in version of the file should have all dbms lines commented..
247
248 If x_categorization_code <> 'R' then
249
250 l_stage := 'Deriving Uncategorized RLMI';
251 -- dbms_output.put_line('In Mapping UnCategorized');
252
253 select resource_list_member_id
254 into x_resource_list_member_id
255 from pa_resource_list_members
256 where resource_list_id = x_resource_list_id
257 and NVL(migration_code,'M') ='M'; -- Bug 3626671
258
259 x_prev_list_processed := x_resource_list_id;
260 RETURN;
261
262 Else -- For Budget by resources
263
264 l_stage := 'Deriving Categorized RLMI';
265 --dbms_output.put_line('In Mapping Categorized');
266
267 If (nvl(x_prev_list_processed,-1) <> x_resource_list_id) then
268
269 --dbms_output.put_line('In Mapping Categorized-New');
270
271 l_stage := 'Delete resource type Table';
272
273 -- ##Clean up resource type table, initialize variables
274 resource_type_tab.delete;
275 x_group_resource_type_id := null;
276 x_group_resource_type_name := null;
277
278 l_stage := 'Get Grouping Info';
279
280 -- # Check whether resource list is grouped
281 -- # if list not grouped then Zero (0) is the value for group_resource_type_id
282 select prl.group_resource_type_id
283 into x_group_resource_type_id
284 from pa_resource_lists prl
285 where prl.resource_list_id = x_resource_list_id ;
286
287 If x_group_resource_type_id <> 0 then
288
289 select prt.resource_type_code
290 into x_group_resource_type_name
291 from pa_resource_types prt
292 where prt.resource_type_id = x_group_resource_type_id;
293
294 End if;
295
296 -- ## Recreate resource type table with resource types for resource list being processed
297
298 l_stage := 'Recreate resource type Table';
299
300 for records in get_resource_types
301 loop
302
303 l_count := l_count + 1;
304 resource_type_tab(l_count) := records.resource_type_code;
305
306 end loop;
307
308
309 x_prev_list_processed := x_resource_list_id ;
310
311 End if; -- If x_prev_list_processed <> x_resource_list_id
312
313
314 /* --------------------------------------------------------------------------
315 || Following piece of code derives all the values necessary for
316 || carrying out NOCOPY resource mapping
317 -------------------------------------------------------------------------- */
318
319 l_stage := 'Deriving values';
320
321 l_rowcount := resource_type_tab.COUNT;
322
323 --dbms_output.put_line('l_rowcount:'||l_rowcount);
324
325 for i in 1..l_rowcount
326 loop
327
328 l_resource_type := resource_type_tab(i);
329
330 if (l_resource_type = 'EXPENDITURE_TYPE') then
331
332 l_stage := 'Deriving values : EXP TYPE';
333
334 l_expenditure_type := x_expenditure_type;
335
336 elsif (l_resource_type = 'EXPENDITURE_CATEGORY' or l_resource_type = 'REVENUE_CATEGORY') then
337
338 l_stage := 'Deriving values : EXP/REV CAT';
339
340 -- ## NOTE: exp category and rev cateogry are being derived at one shot
341 -- ## so that we do not have query pa_expenditure_types again
342
343 If x_expenditure_type is not null then
344
345 Select expenditure_category,
346 revenue_category_code
347 into l_expenditure_category,
348 l_revenue_category
349 from pa_expenditure_types
350 where expenditure_type = x_expenditure_type;
351
352 End if;
353
354 elsif (l_resource_type = 'ORGANIZATION') then
355
356 l_stage := 'Deriving values : ORG';
357 -- -----------
358 -- BUG 1773952
359 -- -----------
360 BEGIN
361
362 Select name
363 into l_organization_name
364 from hr_all_organization_units -- Bug 4732065
365 where organization_id = x_expenditure_org_id;
366 EXCEPTION
367 WHEN NO_DATA_FOUND THEN
368 NULL;
369 END;
370
371 elsif (l_resource_type = 'EMPLOYEE') then
372
373 If x_document_type = 'EXP' then
374
375 l_stage := 'Deriving values : PERSON FOR EXP';
376
377 -- -----------
378 -- BUG 1773952
379 -- -----------
380 BEGIN
381
382 select substrb(papf.full_name,1,100)
383 into l_person_name
384 from per_all_people_f papf,
385 pa_expenditure_items_all peia,
386 pa_expenditures_all pea
387 where peia.expenditure_item_id = x_document_header_id
388 and pea.expenditure_id = peia.expenditure_id
389 and papf.person_id = pea.incurred_by_person_id
390 and trunc(peia.expenditure_item_date) between trunc(papf.effective_start_date) and trunc(nvl(effective_end_Date,sysdate));
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 NULL;
394 END;
395
396 Elsif x_document_type = 'ENC' then
397
398 l_stage := 'Deriving values : PERSON FOR ENC';
399 -- -----------
400 -- BUG 2069108
401 -- -----------
402 BEGIN
403
404 select substrb(papf.full_name,1,100)
405 into l_person_name
406 from per_all_people_f papf,
407 gms_encumbrance_items_all geia,
408 gms_encumbrances_all gea
409 where geia.encumbrance_item_id = x_document_header_id
410 and gea.encumbrance_id = geia.encumbrance_id
411 and papf.person_id = gea.incurred_by_person_id
412 and trunc(geia.encumbrance_item_date) between trunc(papf.effective_start_date) and trunc(nvl(effective_end_Date,sysdate));
413 EXCEPTION
414 WHEN NO_DATA_FOUND THEN
415 NULL;
416 END;
417
418
419 End if;
420
421 elsif (l_resource_type = 'JOB') then
422
423 If x_document_type = 'EXP' then
424
425 l_stage := 'Deriving values : JOB FOR EXP';
426
427 -- -----------
428 -- BUG 1773952
429 -- -----------
430 BEGIN
431
432 select substrb(pj.name,1,100)
433 into l_job_title
434 from per_jobs pj,
435 pa_expenditure_items_all peia
436 where peia.expenditure_item_id = x_document_header_id
437 and pj.job_id = peia.job_id;
438 EXCEPTION
439 WHEN NO_DATA_FOUND THEN
440 NULL;
441 END;
445
442 Elsif x_document_type = 'ENC' then
443
444 l_stage := 'Deriving values : JOB FOR ENC';
446 -- -----------
447 -- BUG 2069108
448 -- -----------
449 BEGIN
450
451 select substrb(pj.name,1,100)
452 into l_job_title
453 from per_jobs pj,
454 gms_encumbrance_items_all geia
455 where geia.encumbrance_item_id = x_document_header_id
456 and pj.job_id = geia.job_id;
457 EXCEPTION
458 WHEN NO_DATA_FOUND THEN
459 NULL;
460 END;
461
462
463 End if;
464
465 elsif (l_resource_type = 'VENDOR') then
466
467 l_stage := 'Deriving values : VENDOR ID';
468
469 -- -----------
470 -- BUG 1773952
471 -- -----------
472 Begin
473
474 If (x_document_type = 'REQ') then
475
476
477 select DISTINCT line.vendor_id
478 into l_vendor_id
479 from po_requisition_lines line,
480 po_requisition_headers_all req
481 where req.requisition_header_id = x_document_header_id
482 and line.requisition_header_id = req.requisition_header_id;
483
484
485 Elsif (x_document_type = 'PO') then
486
487 select head.vendor_id
488 into l_vendor_id
489 from po_headers_all head
490 where head.po_header_id = x_document_header_id;
491
492 Elsif (x_document_type = 'AP') then
493
494 select head.vendor_id
495 into l_vendor_id
496 from ap_invoices_all head
497 where head.invoice_id = x_document_header_id;
498
499 Elsif (x_document_type = 'EXP') then
500
501 -- ------------------------------------------------------------------
502 -- Bug 2200161. Included the following to calculate vendor_id for EXP
503 -- if it is transferred from AP. This enables the resource map API
504 -- to map the correct resource_list_member_id
505 -- ------------------------------------------------------------------
506
507
508 select system_reference1
509 into l_vendor_id
510 from pa_cost_distribution_lines_all
511 where expenditure_item_id = x_document_header_id
512 and line_num = x_document_distribution_id
513 and system_reference1 is not null
514 and system_reference2 is not null
515 and system_reference3 is not null;
516
517 End if;
518
519 Exception
520
521 when no_data_found then
522 null;
523 End;
524
525 If l_vendor_id is not null then
526
527 l_stage := 'Deriving values : VENDOR' ;
528
529 --Select substrb(vendor_name,1,100)
530 -- The select statement has been changed for utf8 changes for AP schema.
531 -- Refer bug 2614745
532 Select vendor_name
533 into l_vendor_name
534 from po_vendors
535 where vendor_id = l_vendor_id;
536
537 End if;
538
539 elsif l_resource_type = 'EVENT_TYPE' then
540
541 l_stage := 'Deriving values : EVENT';
542
543 l_event_type := x_event_type;
544
545 -- ## Event Type also has a revenue_category
546 -- ## In gms, we associate event with expenditure_type
547 -- ## If expenditure_type null, then we calc. revenue_category here
548
549 If x_expenditure_type is null and x_event_type is not null then
550
551 l_stage := 'Deriving values : EVENT REV CAT.';
552
553 Select revenue_category_code
554 into l_revenue_category
555 from pa_event_types
556 where event_type = x_event_type;
557
558 End if;
559
560 end if; -- if l_resource_type = .....
561
562 --dbms_output.put_line('l_resource_type:'||l_resource_type);
563
564 end loop; -- for 1..l_rowcount
565
566 /* --------------------------------------------------------------------------
567 || Following piece of code derives Resource List Member Id (RLMI)
568 -------------------------------------------------------------------------- */
569 l_stage := 'Deriving RLMI';
570 l_count := 0;
571
572
573 -- 1. get parent_member_id:
574
575 If nvl(x_group_resource_type_name,'NONE') = 'NONE' then
576
577 l_parent_rlmi := null;
578
579 ElsIf nvl(x_group_resource_type_name,'NONE') = 'EXPENDITURE_CATEGORY' then
580
581 If l_expenditure_category is null and x_expenditure_type is not null then
582
583 Select expenditure_category,
584 revenue_category_code
585 into l_expenditure_category,
586 l_revenue_category
587 from pa_expenditure_types
591
588 where expenditure_type = x_expenditure_type;
589
590 End if;
592 get_parent_rlmi(x_group_resource_type_id,
593 l_expenditure_category,
594 x_resource_list_id,
595 l_parent_rlmi,
596 x_error_code,
597 x_error_buff);
598
599 ElsIf nvl(x_group_resource_type_name,'NONE') = 'REVENUE_CATEGORY' then
600
601 If l_revenue_category is null then
602
603 If x_expenditure_type is not null then
604
605 l_stage := 'Deriving RLMI:UNCLASSIFIED RES - REV - FOR EXP';
606
607 Select expenditure_category,
608 revenue_category_code
609 into l_expenditure_category,
610 l_revenue_category
611 from pa_expenditure_types
612 where expenditure_type = x_expenditure_type;
613
614 Elsif (x_expenditure_type is null and x_event_type is not null) then
615
616 l_stage := 'Deriving RLMI:UNCLASSIFIED RES - REV - FOR EVT';
617
618 Select revenue_category_code
619 into l_revenue_category
620 from pa_event_types
621 where event_type = x_event_type;
622
623 End if;
624
625 End if;
626
627 get_parent_rlmi(x_group_resource_type_id,
628 l_revenue_category,
629 x_resource_list_id,
630 l_parent_rlmi,
631 x_error_code,
632 x_error_buff);
633
634 ElsIf nvl(x_group_resource_type_name,'NONE') = 'ORGANIZATION' then
635
636 If l_organization_name is null then
637
638 Select name
639 into l_organization_name
640 from hr_all_organization_units -- Bug 4732065
641 where organization_id = x_expenditure_org_id;
642
643 End If;
644
645 get_parent_rlmi(x_group_resource_type_id,
646 l_organization_name,
647 x_resource_list_id,
648 l_parent_rlmi,
649 x_error_code,
650 x_error_buff);
651
652 End if;
653
654 If nvl(l_parent_rlmi,0) <> -1 then
655
656 -- l_parent_rlmi is -1 when the parent group for the resource does not exist
657 -- This can happen only for resource list that are grouped..
658 -- In this case we need to get the Unclassified RLMI at the resource group level
659 -- For all other combination, RLMI derivation logic continues in this if part...
660
661 -- 2. get RLMI for classified:
662
663 --2a. If resource group has employee resource_type ...
664
665 If l_person_name is not null then
666
667 for rlmi_records in get_rlmis_class('EMPLOYEE',l_person_name)
668 loop
669
670 If l_parent_rlmi is null then
671
672 -- ## RLMI has been derived
673 x_resource_list_member_id := rlmi_records.resource_list_member_id;
674
675 RETURN;
676
677 ElsIf l_parent_rlmi is not null then
678
679 If l_parent_rlmi = rlmi_records.parent_member_id then
680
681 -- ## RLMI has been derived
682 x_resource_list_member_id := rlmi_records.resource_list_member_id;
683
684 RETURN;
685
686 End if;
687 End if;-- l_parent_rlmi is not null then
688
689 end loop;
690
691 End If;
692
693 --2b. If resource group has job resource_type and RLMI still underived...
694
695 If l_job_title is not null then
696
697 for rlmi_records in get_rlmis_class('JOB',l_job_title)
698 loop
699
700 If l_parent_rlmi is null then
701
702 -- ## RLMI has been derived
703 x_resource_list_member_id := rlmi_records.resource_list_member_id;
704
705 RETURN;
706
707 ElsIf l_parent_rlmi is not null then
708
709 If l_parent_rlmi = rlmi_records.parent_member_id then
710
711 -- ## RLMI has been derived
712 x_resource_list_member_id := rlmi_records.resource_list_member_id;
713
714 RETURN;
715
716 End if;
717 End if;-- l_parent_rlmi is not null then
718
719 end loop;
720
721 End If;
722
723 --2c. If resource group has organization resource_type and RLMI still underived...
724
725 If l_organization_name is not null then
726
727 for rlmi_records in get_rlmis_class('ORGANIZATION',l_organization_name)
728 loop
729
730 If l_parent_rlmi is null then
731
732 -- ## RLMI has been derived
733 x_resource_list_member_id := rlmi_records.resource_list_member_id;
734
735 RETURN;
736
737 ElsIf l_parent_rlmi is not null then
738
739 If l_parent_rlmi = rlmi_records.parent_member_id then
740
741 -- ## RLMI has been derived
742 x_resource_list_member_id := rlmi_records.resource_list_member_id;
743
744 RETURN;
745
746 End if;
747 End if;-- l_parent_rlmi is not null then
748
749 end loop;
750
751 End If;
752
753 --2d. If resource group has vendor resource_type and RLMI still underived...
754
755 If l_vendor_name is not null then
756
760 If l_parent_rlmi is null then
757 for rlmi_records in get_rlmis_class('VENDOR',l_vendor_name)
758 loop
759
761
762 -- ## RLMI has been derived
763 x_resource_list_member_id := rlmi_records.resource_list_member_id;
764
765 RETURN;
766
767 ElsIf l_parent_rlmi is not null then
768
769 If l_parent_rlmi = rlmi_records.parent_member_id then
770
771 -- ## RLMI has been derived
772 x_resource_list_member_id := rlmi_records.resource_list_member_id;
773
774 RETURN;
775
776 End if;
777 End if;-- l_parent_rlmi is not null then
778
779 end loop;
780
781 End If;
782
783 --2e. If resource group has expenditure_type resource_type and RLMI still underived...
784
785 If l_expenditure_type is not null then
786
787 for rlmi_records in get_rlmis_class('EXPENDITURE_TYPE',l_expenditure_type)
788 loop
789
790 If l_parent_rlmi is null then
791
792 -- ## RLMI has been derived
793 x_resource_list_member_id := rlmi_records.resource_list_member_id;
794
795 RETURN;
796
797 ElsIf l_parent_rlmi is not null then
798
799 If l_parent_rlmi = rlmi_records.parent_member_id then
800
801 -- ## RLMI has been derived
802 x_resource_list_member_id := rlmi_records.resource_list_member_id;
803
804 RETURN;
805
806 End if;
807 End if;-- l_parent_rlmi is not null then
808
809 end loop;
810
811 End If;
812
813 --2f. If resource group has event_type resource_type and RLMI still underived...
814
815 If l_event_type is not null then
816
817 for rlmi_records in get_rlmis_class('EVENT_TYPE',l_event_type)
818 loop
819
820 If l_parent_rlmi is null then
821
822 -- ## RLMI has been derived
823 x_resource_list_member_id := rlmi_records.resource_list_member_id;
824
825 RETURN;
826
827 ElsIf l_parent_rlmi is not null then
828
829 If l_parent_rlmi = rlmi_records.parent_member_id then
830
831 -- ## RLMI has been derived
832 x_resource_list_member_id := rlmi_records.resource_list_member_id;
833
834 RETURN;
835
836 End if;
837 End if;-- l_parent_rlmi is not null then
838
839 end loop;
840
841 End If;
842
843 --2g. If resource group has expenditure_category resource_type and RLMI still underived...
844
845 If l_expenditure_category is not null then
846
847 for rlmi_records in get_rlmis_class('EXPENDITURE_CATEGORY',l_expenditure_category)
848 loop
849
850 If l_parent_rlmi is null then
851
852 -- ## RLMI has been derived
853 x_resource_list_member_id := rlmi_records.resource_list_member_id;
854
855 RETURN;
856
857 ElsIf l_parent_rlmi is not null then
858
859 If l_parent_rlmi = rlmi_records.parent_member_id then
860
861 -- ## RLMI has been derived
862 x_resource_list_member_id := rlmi_records.resource_list_member_id;
863
864 RETURN;
865
866 End if;
867 End if;-- l_parent_rlmi is not null then
868
869 end loop;
870
871 End If;
872
873 --2h. If resource group has revenue_category resource_type and RLMI still underived...
874
875 If l_revenue_category is not null then
876
877 for rlmi_records in get_rlmis_class('REVENUE_CATEGORY',l_revenue_category)
878 loop
879
880 If l_parent_rlmi is null then
881
882 -- ## RLMI has been derived
883 x_resource_list_member_id := rlmi_records.resource_list_member_id;
884
885 RETURN;
886
887 ElsIf l_parent_rlmi is not null then
888
889 If l_parent_rlmi = rlmi_records.parent_member_id then
890
891 -- ## RLMI has been derived
892 x_resource_list_member_id := rlmi_records.resource_list_member_id;
893
894 RETURN;
895
896 End if;
897 End if;-- l_parent_rlmi is not null then
898
899 end loop;
900
901 End If;
902
903 -- If RLMI sill underived, then derive unclassified RLMI
904 -- 3. get RLMI for unclassified:
905
906 -- New code to derive unclassified start .. Bug reference 3631208
907 Begin
908
909 If l_parent_rlmi is null then
910
911 -- ## Resource list not grouped
912 -- ## RLMI has been derived for Ungrouped Resource List
913 -- ## RLMI is RLMI for Unclassified resource
914
915 select c.resource_list_member_id
916 into x_resource_list_member_id
917 from pa_resource_list_members c
918 where c.resource_list_id = x_resource_list_id
919 and c.enabled_flag = 'Y'
920 and c.alias = 'Unclassified'
921 and c.parent_member_id is NULL
922 and NVL(c.migration_code,'M') ='M'; -- Bug 3626671;
923
924 Else
925 -- ## Resource list grouped
926 select c.resource_list_member_id
927 into x_resource_list_member_id
928 from pa_resource_list_members c
929 where c.resource_list_id = x_resource_list_id
930 and c.enabled_flag = 'Y'
934
931 and c.alias = 'Unclassified'
932 and c.parent_member_id = l_parent_rlmi
933 and NVL(c.migration_code,'M') ='M'; -- Bug 3626671;
935 End If;
936 RETURN;
937 Exception
938 When no_data_found then
939 NULL;
940 End;
941 -- New code to derive unclassified end .. Bug reference 3631208
942
943 /* ---------------------------------------------------------------------------------
944 for rlmi_records in get_rlmis_unclass
945 loop
946
947 --dbms_output.put_line('UNCLASSIFIED ');
948
949 If l_parent_rlmi is null then
950
951 -- ## Resource list not grouped
952 -- ## RLMI has been derived for Ungrouped Resource List
953 -- ## RLMI is RLMI for Unclassified resource
954 x_resource_list_member_id := rlmi_records.resource_list_member_id;
955 RETURN;
956
957 Else
958
959 -- ## Resource list grouped
960 If l_parent_rlmi = rlmi_records.parent_member_id then
961
962 x_resource_list_member_id := rlmi_records.resource_list_member_id;
963
964 RETURN;
965
966 End if;
967
968 End if;-- l_parent_rlmi is not null then
969
970 end loop;
971 ----------------------------------------------------------------------------------------- */
972 -- 4. If Resource List by Resource Group, but no resources...
973
974 -- 4a. If grouping by organization but no resources..
975
976 If l_organization_name is not null and
977 nvl(x_group_resource_type_name,'NONE') = 'ORGANIZATION' and
978 l_parent_rlmi is not null
979 then
980
981 for rlmi_records in get_rlmis_class('ORGANIZATION',l_organization_name)
982 loop
983
984 If rlmi_records.resource_list_member_id = l_parent_rlmi
985 then
986 x_resource_list_member_id := rlmi_records.resource_list_member_id;
987 RETURN;
988 End if;
989
990 end loop;
991
992 End If;
993
994 -- 4b. If grouping by expenditure_category but no resources..
995
996 If l_expenditure_category is not null and
997 nvl(x_group_resource_type_name,'NONE') = 'EXPENDITURE_CATEGORY' and
998 l_parent_rlmi is not null
999 then
1000
1001 for rlmi_records in get_rlmis_class('EXPENDITURE_CATEGORY',l_expenditure_category)
1002 loop
1003
1004 If rlmi_records.resource_list_member_id = l_parent_rlmi
1005 then
1006 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1007 RETURN;
1008 End if;
1009
1010 end loop;
1011
1012 End If;
1013
1014 -- 4c. If grouping by revenue_category but no resources..
1015
1016 If l_revenue_category is not null and
1017 nvl(x_group_resource_type_name,'NONE') = 'REVENUE_CATEGORY' and
1018 l_parent_rlmi is not null
1019 then
1020
1021 for rlmi_records in get_rlmis_class('REVENUE_CATEGORY',l_revenue_category)
1022 loop
1023
1024 If rlmi_records.resource_list_member_id = l_parent_rlmi
1025 then
1026 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1027 RETURN;
1028 End if;
1029
1030 end loop;
1031
1032 End If;
1033
1034
1035 -- 5. RLMI COULD NOT BE DERIVED:
1036
1037 If (x_resource_list_member_id is NULL) then
1038
1039 X_Error_Code := 1;
1040 X_Error_Buff := l_stage || 'RLMI Could not be Derived';
1041
1042 RETURN;
1043
1044 End if;
1045
1046 ElsIf nvl(l_parent_rlmi,0) = -1 then
1047
1048 l_stage := 'Deriving RLMI:UNCLASSIFIED GRP - EXP';
1049
1050 -- ## Get unclassified Resource Group
1051
1052 get_grp_unclassified(x_resource_list_id,
1053 x_resource_list_member_id,
1054 x_error_code,
1055 x_error_buff);
1056
1057 RETURN;
1058
1059 End if; --If nvl(l_parent_rlmi,0) <> -1 then
1060
1061 End if ; -- ## If categorization_code = 'R' then ...
1062
1063 Exception
1064
1065 When no_data_found then
1066
1067 X_Error_Code := 2;
1068 X_Error_Buff := l_stage || ' :: ' || substrb(sqlerrm,1,200);
1069 RETURN;
1070
1071 When others then
1072
1073 X_Error_Code := 3;
1074 X_Error_Buff := l_stage || ' :: ' || substrb(sqlerrm,1,200);
1075 RETURN;
1076
1077 End map_resources;
1078
1079 /* ----------------------------------------------------------------------
1080 || ************** NEW CODE FOR RESOURCE MAPPING ENDS HERE **************
1081 ---------------------------------------------------------------------- */
1082
1083 /* ---------------------------------------------------------------------------
1084 || ************** NEW CODE FOR RESOURCE GROUP MAPPING ENDS HERE **************
1085 --------------------------------------------------------------------------- */
1086
1087
1088 /* -----------------------------------------------------------------------------------
1089 || Procedure "MAP_RESOURCES_GROUP" is the main API to derive RLMI
1090 || Parameters:
1091 || A. IN Parameters:
1095 || 3. x_expenditure_org_id - Organization Id
1092 || ==============
1093 || 1. x_document_type - Document Type : EXP,ENC,AP,PO,REQ,EVT(Event), MANDATORY VALUE
1094 || 2. x_expenditure_type - Expenditure Type
1096 || 4. x_person_id - Person Id (Only for Expenditures and Manual Encumbrances)
1097 || 5. x_job_id - Job Id (Only for Expenditures and Manual Encumbrances)
1098 || 6. x_vendor_id - Vendor Id (Only for AP/PO/REQ)
1099 || 7. x_expenditure_category - Expenditure Category of the Expenditure Type
1100 || 8. x_revenue_category - Revenue Category of the Expenditure Type
1101 || 9. x_categorization_code - Categorization Code : Values 'R' or 'N'
1102 || 10. x_resource_list_id - Resource List Id
1103 || 11. x_event_type - Event Type
1104 ||
1105 || B. IN OUT NOCOPY Parameters:
1106 || =====================
1107 || These IN/OUT parameters are helpful in batch mode(i.e. when Resource mapping API
1108 || is being called in a loop. API passes the next four values. For the subsequent
1109 || transactions, API will check if x_prev_list_processed (resource list for previous
1110 || transaction) same as for current, if so it does not derive the next 3 IN/OUT parameter
1111 || values.
1112 || %%% WARNING %%% :
1113 || =================
1114 || Programmers using this API should not initialize the values of these
1115 || IN/OUT parameters within the loop.
1116 || 1. x_prev_list_processed - Resource List Id of the previous transaction
1117 || 2. x_group_resource_type_id - Group Resource Id for the resource list
1118 || This has a value of zero, if resource list not grouped.
1119 || 3. x_group_resource_type_name - Group Resource name for the resource list
1120 || 4. resource_type_tab - This pl/sql table stores the different resource types
1121 || in a resource list. Define plsql table resource_type_tab of type
1122 || "gms_res_map.resource_type_table"
1123 || C. OUT NOCOPY Parameters:
1124 || ==================
1125 || 1. x_resource_list_member_id - Derived RLMI
1126 || 2. x_error_code - Has a value other than zero in case of exception
1127 || 3. x_error_buff - Has a value in case of exception
1128 ||
1129 || K.Biju .. Dated 15-NOV-2001
1130 ----------------------------------------------------------------------------------- */
1131
1132
1133 Procedure map_resources_group(x_document_type IN varchar2,
1134 x_expenditure_type IN varchar2 default NULL,
1135 x_expenditure_org_id IN number default NULL,
1136 x_person_id IN number default NULL,
1137 x_job_id IN number default NULL,
1138 x_vendor_id IN number default NULL,
1139 x_expenditure_category IN varchar2 default NULL,
1140 x_revenue_category IN varchar2 default NULL,
1141 x_categorization_code IN varchar2 default NULL,
1142 x_resource_list_id IN number default NULL,
1143 x_event_type IN varchar2 default NULL,
1144 x_prev_list_processed IN OUT NOCOPY number,
1145 x_group_resource_type_id IN OUT NOCOPY number,
1146 x_group_resource_type_name IN OUT NOCOPY varchar2,
1147 resource_type_tab IN OUT NOCOPY gms_res_map.resource_type_table,
1148 x_resource_list_member_id OUT NOCOPY number,
1149 x_error_code OUT NOCOPY number,
1150 x_error_buff OUT NOCOPY varchar2)
1151 IS
1152
1153 l_stage varchar2(50);
1154 l_count number(2) := 0;
1155 l_rowcount number(2) := 0;
1156 --l_vendor_id number(30);
1157 l_resource_type varchar2(100);
1158 l_expenditure_type varchar2(100);
1159 l_expenditure_category_tmp varchar2(100);
1160 l_revenue_category_tmp varchar2(100);
1161 l_expenditure_category varchar2(100);
1162 l_revenue_category varchar2(100);
1163 --l_organization_name varchar2(100);
1164 -- The width of the variable is changed for UTF8 changes for HRMSschema. Refer bug 2302839.
1165 l_organization_name HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
1166
1167
1168 l_person_name varchar2(100);
1169 l_job_title varchar2(100);
1170 -- The length of varaible l_vendor_name has been changed for utf8 changes for AP schema. refer bug 2614745.
1171 --l_vendor_name varchar2(100);
1172 l_vendor_name PO_VENDORS.VENDOR_NAME%TYPE;
1173 l_event_type varchar2(100);
1174 l_parent_rlmi number(30);
1175
1176 -- ## Following Cursor pulls up all the resource types for that resource list
1177 -- This cursor has been modified as part of Bug reference : 3631208
1178
1179 Cursor get_resource_types is
1180 select distinct c.resource_type_code resource_type_code
1181 from pa_resource_list_members c
1182 where c.resource_list_id = x_resource_list_id
1183 and ((x_group_resource_type_id <> 0 and c.parent_member_id is not null)
1184 or
1185 (x_group_resource_type_id = 0 and c.parent_member_id is null)
1186 )
1187 and c.enabled_flag='Y'
1188 and c.resource_type_code <> 'UNCLASSIFIED'
1189 and NVL(c.migration_code,'M') ='M';
1190
1191 /* ------------------------------------------------ Bug reference : 3631208 -----------+
1192 -- Cursor modified as above ..
1193 select distinct a.resource_type_code resource_type_code
1194 from pa_resource_types a,
1195 pa_resources b,
1196 pa_resource_list_members c
1197 where c.resource_list_id = x_resource_list_id
1198 and b.resource_id = c.resource_id
1199 and a.resource_type_id = b.resource_type_id
1200 and ((x_group_resource_type_id <> 0 and c.parent_member_id is not null)
1201 or
1205 and a.resource_type_code <> 'UNCLASSIFIED'
1202 (x_group_resource_type_id = 0 and c.parent_member_id is null)
1203 )
1204 and c.enabled_flag='Y'
1206 and NVL(c.migration_code,'M') ='M'; -- Bug 3626671
1207
1211 Cursor get_rlmis_unclass is
1208 -- ## Following Cursor pulls up all resource list members, records are sorted
1209 -- ## so that we get RLMI for the most granular resource
1210
1212 select c.resource_list_member_id,
1213 b.name,
1214 a.resource_type_code,
1215 b.resource_type_id,
1216 c.parent_member_id
1217 from pa_resource_types a,
1218 pa_resources b,
1219 pa_resource_list_members c
1220 where c.resource_list_id = x_resource_list_id
1221 and b.resource_id = c.resource_id
1222 and a.resource_type_id = b.resource_type_id
1223 and c.enabled_flag='Y'
1224 and a.resource_type_code='UNCLASSIFIED'
1225 and NVL(c.migration_code,'M') ='M'; -- Bug 3626671
1226 */
1227
1228 Cursor get_rlmis_class(p_resource_type_code in varchar2, p_name in varchar2) is
1229 select c.resource_list_member_id,
1230 b.name,
1231 a.resource_type_code,
1232 b.resource_type_id,
1233 c.parent_member_id
1234 from pa_resource_types a,
1235 pa_resources b,
1236 pa_resource_list_members c
1237 where c.resource_list_id = x_resource_list_id
1238 and b.resource_id = c.resource_id
1239 and a.resource_type_id = b.resource_type_id
1240 and c.enabled_flag='Y'
1241 and a.resource_type_code=p_resource_type_code
1242 and b.name = p_name
1243 and NVL(c.migration_code,'M') ='M'; -- Bug 3626671
1244
1245 TYPE rlmi_record is RECORD(resource_list_member_id NUMBER(15),
1246 name VARCHAR2(100),
1247 resource_type_code VARCHAR2(30),
1248 resource_type_id NUMBER(15),
1249 parent_member_id NUMBER(15));
1250
1251 TYPE rlmi_table is TABLE of rlmi_record index by binary_integer;
1252
1253 rlmi_tab rlmi_table;
1254
1255 Begin
1256 x_error_code := 0;
1257 l_stage := 'Starting Resource mapping';
1258
1259 -- dbms_output.put_line('In Mapping');
1260 -- ## DO NOT DELETE THE dbms_output lines from the code
1261 -- ## This has been introduced for debugging purpose only
1262 -- ## The checked in version of the file should have all dbms lines commented..
1263
1264 If x_categorization_code <> 'R' then
1265
1266 l_stage := 'Deriving Uncategorized RLMI';
1267 -- dbms_output.put_line('In Mapping UnCategorized');
1268
1269 select resource_list_member_id
1270 into x_resource_list_member_id
1271 from pa_resource_list_members
1272 where resource_list_id = x_resource_list_id
1273 and NVL(migration_code,'M') ='M'; -- Bug 3626671
1274
1275 x_prev_list_processed := x_resource_list_id;
1276 RETURN;
1277
1278 Else -- For Budget by resources
1279
1280 l_stage := 'Deriving Categorized RLMI';
1281 --dbms_output.put_line('In Mapping Categorized');
1282
1283 If (nvl(x_prev_list_processed,-1) <> x_resource_list_id) then
1284
1285 --dbms_output.put_line('In Mapping Categorized-New');
1286
1287 l_stage := 'Delete resource type Table';
1288
1289 -- ##Clean up resource type table, initialize variables
1290 resource_type_tab.delete;
1291 x_group_resource_type_id := null;
1292 x_group_resource_type_name := null;
1293
1294 l_stage := 'Get Grouping Info';
1295
1296 -- # Check whether resource list is grouped
1297 -- # if list not grouped then Zero (0) is the value for group_resource_type_id
1298 select prl.group_resource_type_id
1299 into x_group_resource_type_id
1300 from pa_resource_lists prl
1301 where prl.resource_list_id = x_resource_list_id;
1302
1303 If x_group_resource_type_id <> 0 then
1304
1305 select prt.resource_type_code
1306 into x_group_resource_type_name
1307 from pa_resource_types prt
1308 where prt.resource_type_id = x_group_resource_type_id;
1309
1310 End if;
1311
1312 -- ## Recreate resource type table with resource types for resource list being processed
1313
1314 l_stage := 'Recreate resource type Table';
1315
1316 for records in get_resource_types
1317 loop
1318
1319 l_count := l_count + 1;
1320 resource_type_tab(l_count) := records.resource_type_code;
1321
1322 end loop;
1323
1324
1325 x_prev_list_processed := x_resource_list_id ;
1326
1327 End if; -- If x_prev_list_processed <> x_resource_list_id
1328
1329
1330 /* --------------------------------------------------------------------------
1331 || Following piece of code derives all the values necessary for
1332 || carrying out NOCOPY resource mapping
1333 -------------------------------------------------------------------------- */
1334
1335 l_stage := 'Deriving values';
1336
1337 l_rowcount := resource_type_tab.COUNT;
1338
1342 loop
1339 --dbms_output.put_line('l_rowcount:'||l_rowcount);
1340
1341 for i in 1..l_rowcount
1343
1344 l_resource_type := resource_type_tab(i);
1345
1346 if (l_resource_type = 'EXPENDITURE_TYPE') then
1347
1348 l_stage := 'Deriving values : EXP TYPE';
1349
1350 l_expenditure_type := x_expenditure_type;
1351
1352 elsif (l_resource_type = 'EXPENDITURE_CATEGORY' or l_resource_type = 'REVENUE_CATEGORY') then
1353
1354 l_stage := 'Deriving values : EXP/REV CAT';
1355
1356 -- ## NOTE: exp category and rev cateogry are being derived at one shot
1357 -- ## so that we do not have query pa_expenditure_types again
1358
1359 If x_expenditure_type is not null then
1360
1361 l_expenditure_category := x_expenditure_category;
1362 l_revenue_category := x_revenue_category;
1363
1364 End if;
1365
1366 elsif (l_resource_type = 'ORGANIZATION') then
1367
1368 l_stage := 'Deriving values : ORG';
1369 -- -----------
1370 -- BUG 1773952
1371 -- -----------
1372 BEGIN
1373
1374 Select name
1375 into l_organization_name
1376 from hr_organization_units
1377 where organization_id = x_expenditure_org_id;
1378 EXCEPTION
1379 WHEN NO_DATA_FOUND THEN
1380 NULL;
1381 END;
1382
1383 elsif (l_resource_type = 'EMPLOYEE') then
1384
1385 If x_document_type in ('EXP','ENC') then
1386
1387 l_stage := 'Deriving values : PERSON FOR '||x_document_type;
1388
1389 BEGIN
1390
1391 select substrb(papf.full_name,1,100)
1392 into l_person_name
1393 from per_all_people_f papf,
1394 pa_resources pr,
1395 pa_resource_types prt
1396 where papf.person_id = x_person_id
1397 and papf.full_name = pr.name
1398 and prt.resource_type_id = pr.resource_type_id
1399 and prt.resource_type_code = 'EMPLOYEE'
1400 and rownum=1 ;
1401
1402 EXCEPTION
1403 WHEN NO_DATA_FOUND THEN
1404 NULL;
1405 END;
1406
1407 End If;
1408
1409 elsif (l_resource_type = 'JOB') then
1410
1411 If x_document_type in( 'EXP', 'ENC') then
1412
1413 l_stage := 'Deriving values : JOB FOR '||x_document_type;
1414
1415 BEGIN
1416
1417 select substrb(pj.name,1,100)
1418 into l_job_title
1419 from per_jobs pj
1420 where pj.job_id = x_job_id;
1421 EXCEPTION
1422 WHEN NO_DATA_FOUND THEN
1423 NULL;
1424 END;
1425 End if;
1426
1427 elsif (l_resource_type = 'VENDOR') then
1428
1429 If x_vendor_id is not null then
1430
1431 l_stage := 'Deriving values : VENDOR' ;
1432
1433 --Select substrb(vendor_name,1,100)
1434 -- The select statement has been changed for utf8 changes for AP schema. refer bug 2614745.
1435 Select vendor_name
1436 into l_vendor_name
1437 from po_vendors
1438 where vendor_id = x_vendor_id;
1439
1440 End if;
1441
1442 elsif l_resource_type = 'EVENT_TYPE' then
1443
1444 l_stage := 'Deriving values : EVENT';
1445
1446 l_event_type := x_event_type;
1447
1448 -- ## Event Type also has a revenue_category
1449 -- ## In gms, we associate event with expenditure_type
1450 -- ## If expenditure_type null, then we calc. revenue_category here
1451
1452 If x_expenditure_type is null and x_event_type is not null then
1453
1454 l_stage := 'Deriving values : EVENT REV CAT.';
1455
1456 Select revenue_category_code
1457 into l_revenue_category
1458 from pa_event_types
1459 where event_type = x_event_type;
1460
1461 End if;
1462
1463 end if; -- if l_resource_type = .....
1464
1465 --dbms_output.put_line('l_resource_type:'||l_resource_type);
1466
1467 end loop; -- for 1..l_rowcount
1468
1469 /* --------------------------------------------------------------------------
1470 || Following piece of code derives Resource List Member Id (RLMI)
1471 -------------------------------------------------------------------------- */
1472 l_stage := 'Deriving RLMI';
1473 l_count := 0;
1474
1475
1476 -- 1. get parent_member_id:
1477
1478 If nvl(x_group_resource_type_name,'NONE') = 'NONE' then
1479
1480 l_parent_rlmi := null;
1481
1482 ElsIf nvl(x_group_resource_type_name,'NONE') = 'EXPENDITURE_CATEGORY' then
1483
1484 If l_expenditure_category is null and x_expenditure_type is not null then
1485
1486 l_expenditure_category := x_expenditure_category;
1487 l_revenue_category := x_revenue_category;
1488
1489 End if;
1490
1491 get_parent_rlmi(x_group_resource_type_id,
1492 l_expenditure_category,
1493 x_resource_list_id,
1497
1494 l_parent_rlmi,
1495 x_error_code,
1496 x_error_buff);
1498 ElsIf nvl(x_group_resource_type_name,'NONE') = 'REVENUE_CATEGORY' then
1499
1500 If l_revenue_category is null then
1501
1502 If x_expenditure_type is not null then
1503
1504 l_stage := 'Deriving RLMI:UNCLASSIFIED RES - REV - FOR EXP';
1505
1506 l_expenditure_category := x_expenditure_category;
1507 l_revenue_category := x_revenue_category;
1508
1509 Elsif (x_expenditure_type is null and x_event_type is not null) then
1510
1511 l_stage := 'Deriving RLMI:UNCLASSIFIED RES - REV - FOR EVT';
1512
1513 l_revenue_category := x_revenue_category;
1514
1515 End if;
1516
1517 End if;
1518
1519 get_parent_rlmi(x_group_resource_type_id,
1520 l_revenue_category,
1521 x_resource_list_id,
1522 l_parent_rlmi,
1523 x_error_code,
1524 x_error_buff);
1525
1526 ElsIf nvl(x_group_resource_type_name,'NONE') = 'ORGANIZATION' then
1527
1528 If l_organization_name is null then
1529
1530 Select name
1531 into l_organization_name
1532 from hr_organization_units
1533 where organization_id = x_expenditure_org_id;
1534
1535 End If;
1536
1537 get_parent_rlmi(x_group_resource_type_id,
1538 l_organization_name,
1539 x_resource_list_id,
1540 l_parent_rlmi,
1541 x_error_code,
1542 x_error_buff);
1543
1544 End if;
1545
1546 If nvl(l_parent_rlmi,0) <> -1 then
1547
1548 -- l_parent_rlmi is -1 when the parent group for the resource does not exist
1549 -- This can happen only for resource list that are grouped..
1550 -- In this case we need to get the Unclassified RLMI at the resource group level
1551 -- For all other combination, RLMI derivation logic continues in this if part...
1552
1553 -- 2. get RLMI for classified:
1554
1555 --2a. If resource group has employee resource_type ...
1556
1557 If l_person_name is not null then
1558
1559 for rlmi_records in get_rlmis_class('EMPLOYEE',l_person_name)
1560 loop
1561
1562 If l_parent_rlmi is null then
1563
1564 -- ## RLMI has been derived
1565 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1566
1567 RETURN;
1568
1569 ElsIf l_parent_rlmi is not null then
1570
1571 If l_parent_rlmi = rlmi_records.parent_member_id then
1572
1573 -- ## RLMI has been derived
1574 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1575
1576 RETURN;
1577
1578 End if;
1579 End if;-- l_parent_rlmi is not null then
1580
1581 end loop;
1582
1583 End If;
1584
1585 --2b. If resource group has job resource_type and RLMI still underived...
1586
1587 If l_job_title is not null then
1588
1589 for rlmi_records in get_rlmis_class('JOB',l_job_title)
1590 loop
1591
1592 If l_parent_rlmi is null then
1593
1594 -- ## RLMI has been derived
1595 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1596
1597 RETURN;
1598
1599 ElsIf l_parent_rlmi is not null then
1600
1601 If l_parent_rlmi = rlmi_records.parent_member_id then
1602
1603 -- ## RLMI has been derived
1604 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1605
1606 RETURN;
1607
1608 End if;
1609 End if;-- l_parent_rlmi is not null then
1610
1611 end loop;
1612
1613 End If;
1614
1615 --2c. If resource group has organization resource_type and RLMI still underived...
1616
1617 If l_organization_name is not null then
1618
1619 for rlmi_records in get_rlmis_class('ORGANIZATION',l_organization_name)
1620 loop
1621
1622 If l_parent_rlmi is null then
1623
1624 -- ## RLMI has been derived
1625 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1626
1627 RETURN;
1628
1629 ElsIf l_parent_rlmi is not null then
1630
1631 If l_parent_rlmi = rlmi_records.parent_member_id then
1632
1633 -- ## RLMI has been derived
1634 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1635
1636 RETURN;
1637
1638 End if;
1639 End if;-- l_parent_rlmi is not null then
1640
1641 end loop;
1642
1643 End If;
1644
1645 --2d. If resource group has vendor resource_type and RLMI still underived...
1646
1647 If l_vendor_name is not null then
1648
1649 for rlmi_records in get_rlmis_class('VENDOR',l_vendor_name)
1650 loop
1651
1652 If l_parent_rlmi is null then
1653
1654 -- ## RLMI has been derived
1655 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1656
1657 RETURN;
1658
1659 ElsIf l_parent_rlmi is not null then
1660
1661 If l_parent_rlmi = rlmi_records.parent_member_id then
1662
1663 -- ## RLMI has been derived
1667
1664 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1665
1666 RETURN;
1668 End if;
1669 End if;-- l_parent_rlmi is not null then
1670
1671 end loop;
1672
1673 End If;
1674
1675 --2e. If resource group has expenditure_type resource_type and RLMI still underived...
1676
1677 If l_expenditure_type is not null then
1678
1679 for rlmi_records in get_rlmis_class('EXPENDITURE_TYPE',l_expenditure_type)
1680 loop
1681
1682 If l_parent_rlmi is null then
1683
1684 -- ## RLMI has been derived
1685 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1686
1687 RETURN;
1688
1689 ElsIf l_parent_rlmi is not null then
1690
1691 If l_parent_rlmi = rlmi_records.parent_member_id then
1692
1693 -- ## RLMI has been derived
1694 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1695
1696 RETURN;
1697
1698 End if;
1699 End if;-- l_parent_rlmi is not null then
1700
1701 end loop;
1702
1703 End If;
1704
1705 --2f. If resource group has event_type resource_type and RLMI still underived...
1706
1707 If l_event_type is not null then
1708
1709 for rlmi_records in get_rlmis_class('EVENT_TYPE',l_event_type)
1710 loop
1711
1712 If l_parent_rlmi is null then
1713
1714 -- ## RLMI has been derived
1715 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1716
1717 RETURN;
1718
1719 ElsIf l_parent_rlmi is not null then
1720
1721 If l_parent_rlmi = rlmi_records.parent_member_id then
1722
1723 -- ## RLMI has been derived
1724 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1725
1726 RETURN;
1727
1728 End if;
1729 End if;-- l_parent_rlmi is not null then
1730
1731 end loop;
1732
1733 End If;
1734
1735 --2g. If resource group has expenditure_category resource_type and RLMI still underived...
1736
1737 If l_expenditure_category is not null then
1738
1739 for rlmi_records in get_rlmis_class('EXPENDITURE_CATEGORY',l_expenditure_category)
1740 loop
1741
1742 If l_parent_rlmi is null then
1743
1744 -- ## RLMI has been derived
1745 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1746
1747 RETURN;
1748
1749 ElsIf l_parent_rlmi is not null then
1750
1751 If l_parent_rlmi = rlmi_records.parent_member_id then
1752
1753 -- ## RLMI has been derived
1754 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1755
1756 RETURN;
1757
1758 End if;
1759 End if;-- l_parent_rlmi is not null then
1760
1761 end loop;
1762
1763 End If;
1764
1765 --2h. If resource group has revenue_category resource_type and RLMI still underived...
1766
1767 If l_revenue_category is not null then
1768
1769 for rlmi_records in get_rlmis_class('REVENUE_CATEGORY',l_revenue_category)
1770 loop
1771
1772 If l_parent_rlmi is null then
1773
1774 -- ## RLMI has been derived
1775 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1776
1777 RETURN;
1778
1779 ElsIf l_parent_rlmi is not null then
1780
1781 If l_parent_rlmi = rlmi_records.parent_member_id then
1782
1783 -- ## RLMI has been derived
1784 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1785
1786 RETURN;
1787
1788 End if;
1789 End if;-- l_parent_rlmi is not null then
1790
1791 end loop;
1792
1793 End If;
1794
1795 -- If RLMI sill underived, then derive unclassified RLMI
1796 -- 3. get RLMI for unclassified:
1797 -- New code to derive unclassified start .. Bug reference 3631208
1798 Begin
1799
1800 If l_parent_rlmi is null then
1801
1802 -- ## Resource list not grouped
1803 -- ## RLMI has been derived for Ungrouped Resource List
1804 -- ## RLMI is RLMI for Unclassified resource
1805
1806 select c.resource_list_member_id
1807 into x_resource_list_member_id
1808 from pa_resource_list_members c
1809 where c.resource_list_id = x_resource_list_id
1810 and c.enabled_flag = 'Y'
1811 and c.alias = 'Unclassified'
1812 and c.parent_member_id is NULL
1813 and NVL(c.migration_code,'M') ='M'; -- Bug 3626671;
1814
1815 Else
1816 -- ## Resource list grouped
1817 select c.resource_list_member_id
1818 into x_resource_list_member_id
1819 from pa_resource_list_members c
1820 where c.resource_list_id = x_resource_list_id
1821 and c.enabled_flag = 'Y'
1822 and c.alias = 'Unclassified'
1823 and c.parent_member_id = l_parent_rlmi
1824 and NVL(c.migration_code,'M') ='M'; -- Bug 3626671;
1825
1826 End If;
1827 RETURN;
1828 Exception
1829 When no_data_found then
1830 NULL;
1831 End;
1835
1832 -- New code to derive unclassified end .. Bug reference 3631208
1833
1834 /* ---------------------------------------------------------------------------------
1836 for rlmi_records in get_rlmis_unclass
1837 loop
1838
1839 --dbms_output.put_line('UNCLASSIFIED ');
1840
1841 If l_parent_rlmi is null then
1842
1843 -- ## Resource list not grouped
1844 -- ## RLMI has been derived for Ungrouped Resource List
1845 -- ## RLMI is RLMI for Unclassified resource
1846 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1847 RETURN;
1848
1849 Else
1850
1851 -- ## Resource list grouped
1852 If l_parent_rlmi = rlmi_records.parent_member_id then
1853
1854 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1855
1856 RETURN;
1857
1858 End if;
1859
1860 End if;-- l_parent_rlmi is not null then
1861
1862 end loop;
1863 -------------------------------------------------------------------------------------- */
1864
1865 -- 4. If Resource List by Resource Group, but no resources...
1866
1867 -- 4a. If grouping by organization but no resources..
1868
1869 If l_organization_name is not null and
1870 nvl(x_group_resource_type_name,'NONE') = 'ORGANIZATION' and
1871 l_parent_rlmi is not null
1872 then
1873
1874 for rlmi_records in get_rlmis_class('ORGANIZATION',l_organization_name)
1875 loop
1876
1877 If rlmi_records.resource_list_member_id = l_parent_rlmi
1878 then
1879 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1880 RETURN;
1881 End if;
1882
1883 end loop;
1884
1885 End If;
1886
1887 -- 4b. If grouping by expenditure_category but no resources..
1888
1889 If l_expenditure_category is not null and
1890 nvl(x_group_resource_type_name,'NONE') = 'EXPENDITURE_CATEGORY' and
1891 l_parent_rlmi is not null
1892 then
1893
1894 for rlmi_records in get_rlmis_class('EXPENDITURE_CATEGORY',l_expenditure_category)
1895 loop
1896
1897 If rlmi_records.resource_list_member_id = l_parent_rlmi
1898 then
1899 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1900 RETURN;
1901 End if;
1902
1903 end loop;
1904
1905 End If;
1906
1907 -- 4c. If grouping by revenue_category but no resources..
1908
1909 If l_revenue_category is not null and
1910 nvl(x_group_resource_type_name,'NONE') = 'REVENUE_CATEGORY' and
1911 l_parent_rlmi is not null
1912 then
1913
1914 for rlmi_records in get_rlmis_class('REVENUE_CATEGORY',l_revenue_category)
1915 loop
1916
1917 If rlmi_records.resource_list_member_id = l_parent_rlmi
1918 then
1919 x_resource_list_member_id := rlmi_records.resource_list_member_id;
1920 RETURN;
1921 End if;
1922
1923 end loop;
1924
1925 End If;
1926
1927
1928 -- 5. RLMI COULD NOT BE DERIVED:
1929
1930 If (x_resource_list_member_id is NULL) then
1931
1932 X_Error_Code := 1;
1933 X_Error_Buff := l_stage || 'RLMI Could not be Derived';
1934
1935 RETURN;
1936
1937 End if;
1938
1939 ElsIf nvl(l_parent_rlmi,0) = -1 then
1940
1941 l_stage := 'Deriving RLMI:UNCLASSIFIED GRP - EXP';
1942
1943 -- ## Get unclassified Resource Group
1944
1945 get_grp_unclassified(x_resource_list_id,
1946 x_resource_list_member_id,
1947 x_error_code,
1948 x_error_buff);
1949
1950 RETURN;
1951
1952 End if; --If nvl(l_parent_rlmi,0) <> -1 then
1953
1954 End if ; -- ## If categorization_code = 'R' then ...
1955
1956 Exception
1957
1958 When no_data_found then
1959
1960 X_Error_Code := 2;
1961 X_Error_Buff := l_stage || ' :: ' || substr(sqlerrm,1,200);
1962 RETURN;
1963
1964 When others then
1965
1966 X_Error_Code := 3;
1967 X_Error_Buff := l_stage || ' :: ' || substr(sqlerrm,1,200);
1968 RETURN;
1969
1970 End map_resources_group;
1971
1972 /* ---------------------------------------------------------------------------
1973 || ************** NEW CODE FOR RESOURCE GROUP MAPPING ENDS HERE **************
1974 --------------------------------------------------------------------------- */
1975
1976
1977
1978 -- Initialize function
1979
1980 FUNCTION initialize RETURN NUMBER IS
1981 x_err_code NUMBER:=0;
1982 BEGIN
1983
1984 RETURN 0;
1985 EXCEPTION
1986 WHEN OTHERS THEN
1987 x_err_code := SQLCODE;
1988 RETURN x_err_code;
1989 END initialize;
1990
1991 -- ----------------------------------------------------------------------------
1992 -- This procedure gets the resouce list member id and for the same combinations
1993 -- by pass map trans. To increase the performance . 21-SEP-2000
1997 x_res_list_id in number,
1994 -- ----------------------------------------------------------------------------
1995 Procedure get_rlmi
1996 ( x_project_id in number,
1998 x_organization_id in number,
1999 x_vendor_id in number,
2000 x_expenditure_type in varchar2,
2001 x_non_labor_resource in varchar2,
2002 x_expenditure_category in varchar2,
2003 x_revenue_category in varchar2,
2004 x_non_labor_resource_org_id in number,
2005 x_system_linkage in varchar2,
2006 x_job_id in number,
2007 x_person_id in number,
2008 x_resource_list_member_id out NOCOPY number) is
2009
2010 Begin
2011
2012 select prm.resource_list_member_id
2013 into x_resource_list_member_id
2014 from pa_resource_maps prm,
2015 pa_resource_list_assignments prla
2016 where prla.resource_list_assignment_id = prm.resource_list_assignment_id
2017 and prla.project_id = x_project_id
2018 and prm.resource_list_id = x_res_list_id
2019 and prm.organization_id = x_organization_id
2020 and prm.expenditure_category = x_expenditure_category
2021 and prm.system_linkage_function = x_system_linkage
2022 and nvl(prm.job_id,-1) = nvl(x_job_id,-1)
2023 and nvl(prm.person_id,-1) = nvl(x_person_id,-1)
2024 and nvl(prm.vendor_id,-1) = nvl(x_vendor_id ,-1)
2025 and nvl(prm.expenditure_type,'X') = nvl(x_expenditure_type,'X')
2026 and nvl(prm.non_labor_resource,'X') = nvl(x_non_labor_resource,'X')
2027 and nvl(prm.revenue_category,'X') = nvl(x_revenue_category,'X')
2028 and nvl(prm.non_labor_resource_org_id,-1) = nvl(x_non_labor_resource_org_id,-1)
2029 and rownum = 1;
2030 Exception
2031 when others then
2032 x_resource_list_member_id := null;
2033 End get_rlmi;
2034
2035 PROCEDURE get_resource_map
2036 (x_resource_list_id IN NUMBER,
2037 x_resource_list_assignment_id IN NUMBER,
2038 x_person_id IN NUMBER,
2039 x_job_id IN NUMBER,
2040 x_organization_id IN NUMBER,
2041 x_vendor_id IN NUMBER,
2042 x_expenditure_type IN VARCHAR2,
2043 x_event_type IN VARCHAR2,
2044 x_non_labor_resource IN VARCHAR2,
2045 x_expenditure_category IN VARCHAR2,
2046 x_revenue_category IN VARCHAR2,
2047 x_non_labor_resource_org_id IN NUMBER,
2048 x_event_type_classification IN VARCHAR2,
2049 x_system_linkage_function IN VARCHAR2,
2050 x_resource_list_member_id IN OUT NOCOPY NUMBER,
2051 x_resource_id IN OUT NOCOPY NUMBER,
2052 x_resource_map_found IN OUT NOCOPY BOOLEAN,
2053 x_err_stage IN OUT NOCOPY VARCHAR2,
2054 x_err_code IN OUT NOCOPY NUMBER)
2055 IS
2056 BEGIN
2057
2058 x_err_code := 0;
2059 -- x_err_stage := 'Getting the resource map';
2060 x_resource_map_found := TRUE;
2061 x_resource_list_member_id := NULL;
2062 x_resource_id := NULL;
2063
2064 -- pa_debug.debug(x_err_stage);
2065
2066 /* Seperating Map Check for Expenditures based/Event based Txns */
2067
2068 IF (x_expenditure_type IS NOT NULL) THEN
2069
2070 -- Process records differently based on the person_id is null/not null
2071 -- to take advantage of the index on person_id column
2072
2073 IF ( x_person_id IS NOT NULL ) THEN
2074 -- person_id is not null
2075 SELECT
2076 resource_list_member_id,
2077 resource_id
2078 INTO
2079 x_resource_list_member_id,
2080 x_resource_id
2081 FROM
2082 pa_resource_maps prm
2083 WHERE
2084 prm.resource_list_assignment_id = x_resource_list_assignment_id
2085 AND prm.resource_list_id = x_resource_list_id
2086 AND prm.expenditure_type = x_expenditure_type
2087 AND prm.organization_id = x_organization_id
2088 AND prm.person_id = x_person_id
2089 AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
2090 AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
2091 AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
2092 AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
2093 AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
2094 AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
2095 AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X');
2096 ELSE
2097 -- person_id is null
2098 SELECT
2099 resource_list_member_id,
2100 resource_id
2101 INTO
2102 x_resource_list_member_id,
2103 x_resource_id
2104 FROM
2105 pa_resource_maps prm
2106 WHERE
2107 prm.resource_list_assignment_id = x_resource_list_assignment_id
2108 AND prm.resource_list_id = x_resource_list_id
2109 AND prm.expenditure_type = x_expenditure_type
2110 AND prm.organization_id = x_organization_id
2111 AND prm.person_id IS NULL
2112 AND NVL(prm.job_id,-1) = NVL(x_job_id,-1)
2113 AND NVL(prm.vendor_id,-1) = NVL(x_vendor_id,-1)
2114 AND NVL(prm.non_labor_resource,'X') = NVL(x_non_labor_resource,'X')
2115 AND NVL(prm.expenditure_category,'X') = NVL(x_expenditure_category,'X')
2119 END IF; -- IF ( x_person_id IS NOT NULL )
2116 AND NVL(prm.revenue_category,'X') = NVL(x_revenue_category,'X')
2117 AND NVL(prm.non_labor_resource_org_id,-1) = NVL(x_non_labor_resource_org_id,-1)
2118 AND NVL(prm.system_linkage_function,'X') = NVL(x_system_linkage_function,'X');
2120 ELSE
2121 /* Events */
2122 SELECT
2123 resource_list_member_id,
2124 resource_id
2125 INTO
2126 x_resource_list_member_id,
2127 x_resource_id
2128 FROM
2129 pa_resource_maps prm
2130 WHERE
2131 prm.resource_list_assignment_id = x_resource_list_assignment_id
2132 AND prm.resource_list_id = x_resource_list_id
2133 AND prm.event_type = x_event_type
2134 AND prm.organization_id = x_organization_id
2135 AND prm.revenue_category = x_revenue_category
2136 AND prm.event_type_classification = x_event_type_classification;
2137
2138 END IF; --IF (x_expenditure_type IS NOT NULL)
2139
2140 EXCEPTION
2141 WHEN NO_DATA_FOUND THEN
2142 x_resource_map_found := FALSE;
2143 WHEN OTHERS THEN
2144 x_err_code := SQLCODE;
2145 RAISE;
2146 END get_resource_map;
2147
2148 -- deleting the resource maps for the given resource list assignment id
2149
2150 PROCEDURE delete_res_maps_on_asgn_id
2151 (x_resource_list_assignment_id IN NUMBER,
2152 x_err_stage IN OUT NOCOPY VARCHAR2,
2153 x_err_code IN OUT NOCOPY NUMBER)
2154 IS
2155 BEGIN
2156
2157 x_err_code := 0;
2158 -- x_err_stage := 'Deleting the resource map for given resource list assignment id';
2159
2160 -- pa_debug.debug(x_err_stage);
2161 IF (x_resource_list_assignment_id is null) THEN
2162 DELETE
2163 pa_resource_maps;
2164 ELSE
2165 DELETE
2166 pa_resource_maps prm
2167 WHERE
2168 prm.resource_list_assignment_id = x_resource_list_assignment_id;
2169 END IF;
2170
2171 pa_debug.debug('Numbers of Records Deleted = ' || TO_CHAR(SQL%ROWCOUNT));
2172
2173 EXCEPTION
2174 WHEN NO_DATA_FOUND THEN
2175 NULL;
2176 WHEN OTHERS THEN
2177 x_err_code := SQLCODE;
2178 RAISE;
2179 END delete_res_maps_on_asgn_id;
2180
2181 -- deleting the resource maps for the given project_id and
2182 -- resource_list_id
2183
2184 PROCEDURE delete_res_maps_on_prj_id
2185 (x_project_id IN NUMBER,
2186 x_resource_list_id IN NUMBER,
2187 x_err_stage IN OUT NOCOPY VARCHAR2,
2188 x_err_code IN OUT NOCOPY NUMBER)
2189 IS
2190 BEGIN
2191
2192 x_err_code := 0;
2193 -- x_err_stage := 'Deleting the resource map for given project Id';
2194
2195 -- pa_debug.debug(x_err_stage);
2196
2197 DELETE
2198 pa_resource_maps prm
2199 WHERE
2200 prm.resource_list_assignment_id IN
2201 ( SELECT
2202 resource_list_assignment_id
2203 FROM
2204 pa_resource_list_assignments
2205 WHERE project_id = x_project_id
2206 AND resource_list_id = NVL(x_resource_list_id,resource_list_id)
2207 );
2208
2209 pa_debug.debug('Numbers of Records Deleted = ' || TO_CHAR(SQL%ROWCOUNT));
2210
2211 EXCEPTION
2212 WHEN NO_DATA_FOUND THEN
2213 NULL;
2214 WHEN OTHERS THEN
2215 x_err_code := SQLCODE;
2216 RAISE;
2217 END delete_res_maps_on_prj_id;
2218
2219 -- the function given below creates a resource map
2220
2221 PROCEDURE create_resource_map
2222 (x_resource_list_id IN NUMBER,
2223 x_resource_list_assignment_id IN NUMBER,
2224 x_resource_list_member_id IN NUMBER,
2225 x_resource_id IN NUMBER,
2226 x_person_id IN NUMBER,
2227 x_job_id IN NUMBER,
2228 x_organization_id IN NUMBER,
2229 x_vendor_id IN NUMBER,
2230 x_expenditure_type IN VARCHAR2,
2231 x_event_type IN VARCHAR2,
2232 x_non_labor_resource IN VARCHAR2,
2233 x_expenditure_category IN VARCHAR2,
2234 x_revenue_category IN VARCHAR2,
2235 x_non_labor_resource_org_id IN NUMBER,
2236 x_event_type_classification IN VARCHAR2,
2237 x_system_linkage_function IN VARCHAR2,
2238 x_err_stage IN OUT NOCOPY VARCHAR2,
2239 x_err_code IN OUT NOCOPY NUMBER)
2240 IS
2241 BEGIN
2242
2243 x_err_code :=0;
2244 --x_err_stage := 'Creating resource map';
2245
2246 -- pa_debug.debug(x_err_stage);
2247
2248 INSERT INTO pa_resource_maps
2249 (resource_list_id,
2253 person_id,
2250 resource_list_assignment_id,
2251 resource_list_member_id,
2252 resource_id,
2254 job_id,
2255 organization_id,
2256 vendor_id,
2257 expenditure_type,
2258 event_type,
2259 non_labor_resource,
2260 expenditure_category,
2261 revenue_category,
2262 non_labor_resource_org_id,
2263 event_type_classification,
2264 system_linkage_function,
2265 creation_date,
2266 created_by,
2267 last_updated_by,
2268 last_update_date,
2269 last_update_login,
2270 request_id,
2271 program_application_id,
2272 program_id)
2273 VALUES
2274 (x_resource_list_id,
2275 x_resource_list_assignment_id,
2276 x_resource_list_member_id,
2277 x_resource_id,
2278 x_person_id,
2279 x_job_id,
2280 x_organization_id,
2281 x_vendor_id,
2282 x_expenditure_type,
2283 x_event_type,
2284 x_non_labor_resource,
2285 x_expenditure_category,
2286 x_revenue_category,
2287 x_non_labor_resource_org_id,
2288 x_event_type_classification,
2289 x_system_linkage_function,
2290 SYSDATE,
2291 x_created_by,
2292 x_last_updated_by,
2293 SYSDATE,
2294 x_last_update_login,
2295 x_request_id,
2296 x_program_application_id,
2297 x_program_id);
2298
2299 EXCEPTION
2300 WHEN OTHERS THEN
2301 x_err_code := SQLCODE;
2302 RAISE;
2303 END create_resource_map;
2304
2305 -- change resource list assignment
2306
2307 PROCEDURE change_resource_list_status
2308 (x_resource_list_assignment_id IN NUMBER,
2309 x_err_stage IN OUT NOCOPY VARCHAR2,
2310 x_err_code IN OUT NOCOPY NUMBER)
2311 IS
2312 BEGIN
2313
2314 x_err_code := 0;
2315 -- x_err_stage := 'Updating resource list assignment status';
2316
2317 -- pa_debug.debug(x_err_stage);
2318
2319 UPDATE
2320 pa_resource_list_assignments
2321 SET
2322 resource_list_changed_flag ='N'
2323 WHERE
2324 resource_list_assignment_id = x_resource_list_assignment_id;
2325
2326 EXCEPTION
2327 WHEN OTHERS THEN
2328 x_err_code := SQLCODE;
2329 RAISE;
2330 END change_resource_list_status;
2331
2332 FUNCTION get_resource_list_status
2333 (x_resource_list_assignment_id IN NUMBER)
2334 RETURN VARCHAR2
2335 IS
2336 x_resource_list_changed_flag VARCHAR2(1);
2337 BEGIN
2338
2339 pa_debug.debug('Getting Resource List Status');
2340
2341 SELECT
2342 NVL(resource_list_changed_flag,'N')
2343 INTO
2344 x_resource_list_changed_flag
2345 FROM
2346 pa_resource_list_assignments
2347 WHERE
2348 resource_list_assignment_id = x_resource_list_assignment_id;
2349
2350 RETURN x_resource_list_changed_flag;
2351
2352 EXCEPTION
2353 WHEN OTHERS THEN
2354 RETURN NULL;
2355 END get_resource_list_status;
2356
2357 -- Get the resource Rank
2358
2359
2360 -- If we donot find a rank for a given format and class code then
2361 -- no resource mapping will be done against that resource
2362
2363 FUNCTION get_resource_rank
2364 (x_resource_format_id IN NUMBER,
2365 x_txn_class_code IN VARCHAR2)
2366 RETURN NUMBER
2367 IS
2368 x_rank NUMBER;
2369 BEGIN
2370
2371 pa_debug.debug('Getting Resource Rank');
2372
2373 SELECT
2374 rank
2375 INTO
2376 x_rank
2377 FROM
2378 pa_resource_format_ranks
2379 WHERE
2380 resource_format_id = x_resource_format_id
2381 AND txn_class_code = x_txn_class_code;
2382
2383 RETURN x_rank;
2384
2385 EXCEPTION
2386 WHEN NO_DATA_FOUND THEN
2387 RETURN NULL;
2388 WHEN OTHERS THEN
2389 RETURN NULL;
2390 END get_resource_rank;
2391
2392 -- This function returns the group resource_type_code for the given resoure list
2393 -- In case of 'None' Group Resource type, the table pa_resource_lists
2394 -- will not join to the pa_resource_types table
2395
2396 FUNCTION get_group_resource_type_code
2397 (x_resource_list_id IN NUMBER)
2398 RETURN VARCHAR2
2399 IS
2400 x_group_resource_type_code VARCHAR2(20);
2401 BEGIN
2402
2403 pa_debug.debug('Getting Resource Type Code');
2404
2405 SELECT
2406 rt.resource_type_code
2407 INTO
2408 x_group_resource_type_code
2409 FROM
2410 pa_resource_types rt,
2411 pa_resource_lists rl
2412 WHERE
2413 rl.resource_list_id = x_resource_list_id
2414 AND rl.group_resource_type_id = rt.resource_type_id;
2415
2416 RETURN x_group_resource_type_code;
2417
2418 EXCEPTION
2419 WHEN NO_DATA_FOUND THEN
2420 x_group_resource_type_code := 'NONE';
2421 RETURN x_group_resource_type_code;
2422 WHEN OTHERS THEN
2423 RETURN NULL;
2424 END get_group_resource_type_code;
2425
2426 -- This procedure created resource accum details
2427 -- We will not allow to have multiple PA_RESOURCE_ACCUM_DETAILS
2428 -- for the same TXN_ACCUM_ID and different resource_id and
2429 -- pa_resource_list_member_id
2430
2434 x_resource_list_member_id IN NUMBER,
2431 PROCEDURE create_resource_accum_details
2432 (x_resource_list_id IN NUMBER,
2433 x_resource_list_assignment_id IN NUMBER,
2435 x_resource_id IN NUMBER,
2436 x_txn_accum_id IN NUMBER,
2437 x_project_id IN NUMBER,
2438 x_task_id IN NUMBER,
2439 x_err_stage IN OUT NOCOPY VARCHAR2,
2440 x_err_code IN OUT NOCOPY NUMBER)
2441 IS
2442 BEGIN
2443
2444 x_err_code :=0;
2445 -- x_err_stage := 'Creating resource accum details';
2446
2447 -- pa_debug.debug(x_err_stage);
2448
2449 INSERT INTO pa_resource_accum_details
2450 (resource_list_id,
2451 resource_list_assignment_id,
2452 resource_list_member_id,
2453 resource_id,
2454 txn_accum_id,
2455 project_id,
2456 task_id,
2457 creation_date,
2458 created_by,
2459 last_updated_by,
2460 last_update_date,
2461 last_update_login,
2462 request_id,
2463 program_application_id,
2464 program_id)
2465 SELECT
2466 x_resource_list_id,
2467 x_resource_list_assignment_id,
2468 x_resource_list_member_id,
2469 x_resource_id,
2470 x_txn_accum_id,
2471 x_project_id,
2472 x_task_id,
2473 SYSDATE,
2474 x_created_by,
2475 x_last_updated_by,
2476 SYSDATE,
2477 x_last_update_login,
2478 x_request_id,
2479 x_program_application_id,
2480 x_program_id
2481 FROM
2482 dual
2483 WHERE NOT EXISTS
2484 (SELECT
2485 'Yes'
2486 FROM
2487 pa_resource_accum_details rad
2488 WHERE
2489 resource_list_id = x_resource_list_id
2490 AND resource_list_assignment_id = x_resource_list_assignment_id
2491 /*
2492 AND resource_list_member_id = x_resource_list_member_id
2493 AND resource_id = x_resource_id
2494 */
2495 AND txn_accum_id = x_txn_accum_id
2496 AND project_id = x_project_id
2497 AND task_id = x_task_id
2498 );
2499
2500 EXCEPTION
2501 WHEN NO_DATA_FOUND THEN
2502 NULL;
2503 WHEN OTHERS THEN
2504 x_err_code := SQLCODE;
2505 RAISE;
2506 END create_resource_accum_details;
2507
2508 -- This procedure deleted resource accum details
2509
2510 PROCEDURE delete_resource_accum_details
2511 (x_resource_list_assignment_id IN NUMBER,
2512 x_resource_list_id IN NUMBER,
2513 x_project_id IN NUMBER,
2514 x_err_stage IN OUT NOCOPY VARCHAR2,
2515 x_err_code IN OUT NOCOPY NUMBER)
2516 IS
2517 BEGIN
2518
2519 x_err_code :=0;
2520 -- x_err_stage := 'Deleting resource accum details';
2521
2522 -- pa_debug.debug(x_err_stage);
2523
2524 IF (x_resource_list_id IS NULL) THEN
2525
2526 DELETE
2527 pa_resource_accum_details
2528 WHERE
2529 resource_list_assignment_id =
2530 NVL(x_resource_list_assignment_id,resource_list_assignment_id)
2531 AND project_id = x_project_id;
2532 ELSE
2533
2534 DELETE
2535 pa_resource_accum_details
2536 WHERE
2537 resource_list_assignment_id =
2538 NVL(x_resource_list_assignment_id,resource_list_assignment_id)
2539 AND resource_list_id = x_resource_list_id
2540 AND project_id = x_project_id;
2541
2542 END IF;
2543
2544 pa_debug.debug('Numbers of Records Deleted = ' || TO_CHAR(SQL%ROWCOUNT));
2545
2546 EXCEPTION
2547 WHEN NO_DATA_FOUND THEN
2548 NULL;
2549 WHEN OTHERS THEN
2550 x_err_code := SQLCODE;
2551 RAISE;
2552 END delete_resource_accum_details;
2553
2554 -- This procedure will return the resource and its attributes for the
2555 -- given project_id. It will return the group level resource for
2556 -- the resources for which no child resource exists and for the
2557 -- group if child exists then it will return only the childs
2558 -- please note that outer join is done for pa_resources to pa_resource_txn_attributes
2559 -- because some of the resource may not have attributes
2560
2561 PROCEDURE get_mappable_resources
2562 ( x_project_id IN NUMBER,
2563 x_res_list_id IN NUMBER,
2564 x_resource_list_id IN OUT NOCOPY resource_list_id_tabtype,
2565 x_resource_list_assignment_id IN OUT NOCOPY resource_list_asgn_id_tabtype,
2566 x_resource_list_member_id IN OUT NOCOPY member_id_tabtype,
2567 x_resource_id IN OUT NOCOPY resource_id_tabtype,
2568 x_member_level IN OUT NOCOPY member_level_tabtype,
2569 x_person_id IN OUT NOCOPY person_id_tabtype,
2570 x_job_id IN OUT NOCOPY job_id_tabtype,
2571 x_organization_id IN OUT NOCOPY organization_id_tabtype,
2572 x_vendor_id IN OUT NOCOPY vendor_id_tabtype,
2573 x_expenditure_type IN OUT NOCOPY expenditure_type_tabtype,
2574 x_event_type IN OUT NOCOPY event_type_tabtype,
2575 x_non_labor_resource IN OUT NOCOPY non_labor_resource_tabtype,
2576 x_expenditure_category IN OUT NOCOPY expenditure_category_tabtype,
2577 x_revenue_category IN OUT NOCOPY revenue_category_tabtype,
2578 x_non_labor_resource_org_id IN OUT NOCOPY nlr_org_id_tabtype,
2582 x_resource_type_code IN OUT NOCOPY resource_type_code_tabtype,
2579 x_event_type_classification IN OUT NOCOPY event_type_class_tabtype,
2580 x_system_linkage_function IN OUT NOCOPY system_linkage_tabtype,
2581 x_resource_format_id IN OUT NOCOPY resource_format_id_tabtype,
2583 x_no_of_resources IN OUT NOCOPY BINARY_INTEGER,
2584 x_err_stage IN OUT NOCOPY VARCHAR2,
2585 x_err_code IN OUT NOCOPY NUMBER,
2586 x_exp_type IN VARCHAR2 DEFAULT NULL)
2587 IS
2588
2589 -- Cursor for getting mappable resources for the given resource list
2590
2591 CURSOR selmembers IS
2592 SELECT
2593 rla.resource_list_assignment_id,
2594 rl.resource_list_id,
2595 rlm.resource_list_member_id,
2596 rlm.resource_id,
2597 rlm.member_level,
2598 rta.person_id,
2599 rta.job_id,
2600 rta.organization_id,
2601 rta.vendor_id,
2602 rta.expenditure_type,
2603 rta.event_type,
2604 rta.non_labor_resource,
2605 rta.expenditure_category,
2606 rta.revenue_category,
2607 rta.non_labor_resource_org_id,
2608 rta.event_type_classification,
2609 rta.system_linkage_function,
2610 rta.resource_format_id,
2611 rt.resource_type_code
2612 FROM
2613 pa_resource_lists rl,
2614 pa_resource_list_members rlm,
2615 pa_resource_txn_attributes rta,
2616 pa_resources r,
2617 pa_resource_types rt,
2618 pa_resource_list_assignments rla
2619 WHERE
2620 rlm.resource_list_id = rl.resource_list_id
2621 AND rl.resource_list_id = NVL(x_res_list_id,rl.resource_list_id)
2622 AND NVL(rlm.parent_member_id,0) = 0
2623 --AND rlm.enabled_flag = 'Y' Bug Fix 1370475
2624 AND rlm.resource_id = rta.resource_id(+) --- rta may not available for resource
2625 AND r.resource_id = rlm.resource_id
2626 AND rt.resource_type_id = r.resource_type_id
2627 AND rla.resource_list_id = rl.resource_list_id
2628 AND rla.project_id = x_project_id
2629 AND nvl(rta.expenditure_type,0)=nvl(x_exp_type,nvl(rta.expenditure_type,0))
2630 AND NOT EXISTS
2631 ( SELECT
2632 'Yes'
2633 FROM
2634 pa_resource_list_members rlmc
2635 WHERE
2636 rlmc.parent_member_id = rlm.resource_list_member_id
2637 AND NVL(rlmc.migration_code,'M') ='M' -- Bug 3626671
2638 --AND rlmc.enabled_flag = 'Y' Bug Fix 1370475
2639 )
2640 AND NVL(rl.migration_code,'M') ='M' -- Bug 3626671
2641 AND NVL(rlm.migration_code,'M') ='M' -- Bug 3626671
2642 UNION
2643 SELECT
2644 rla.resource_list_assignment_id,
2645 rl.resource_list_id,
2646 rlmc.resource_list_member_id,
2647 rlmc.resource_id,
2648 rlmc.member_level,
2649 NVL(rtac.person_id,rtap.person_id),
2650 NVL(rtac.job_id,rtap.job_id),
2651 NVL(rtac.organization_id,rtap.organization_id),
2652 NVL(rtac.vendor_id,rtap.vendor_id),
2653 NVL(rtac.expenditure_type,rtap.expenditure_type),
2654 NVL(rtac.event_type,rtap.event_type),
2655 NVL(rtac.non_labor_resource,rtap.non_labor_resource),
2656 NVL(rtac.expenditure_category,rtap.expenditure_category),
2657 NVL(rtac.revenue_category,rtap.revenue_category),
2658 NVL(rtac.non_labor_resource_org_id,rtap.non_labor_resource_org_id),
2659 NVL(rtac.event_type_classification,rtap.event_type_classification),
2660 NVL(rtac.system_linkage_function,rtap.system_linkage_function),
2661 rtac.resource_format_id,
2662 rtc.resource_type_code
2663 FROM
2664 pa_resource_lists rl,
2665 pa_resource_list_members rlmc,
2666 pa_resource_list_members rlmp,
2667 pa_resource_txn_attributes rtac,
2668 pa_resource_txn_attributes rtap,
2669 pa_resources rc,
2670 pa_resource_types rtc,
2671 pa_resource_list_assignments rla
2672 WHERE
2673 rlmc.resource_list_id = rl.resource_list_id
2674 AND rl.resource_list_id = NVL(x_res_list_id,rl.resource_list_id)
2675 --AND rlmc.enabled_flag = 'Y' --Bug Fix 1370475
2676 AND rlmc.resource_id = rtac.resource_id(+) --- rta may not available for resource
2677 AND rlmc.parent_member_id = rlmp.resource_list_member_id
2678 --AND rlmp.enabled_flag = 'Y' --Bug Fix 1370475
2679 AND rlmp.resource_id = rtap.resource_id(+) --- rta may not available for resource
2680 AND rc.resource_id = rlmc.resource_id
2681 AND rtc.resource_type_id = rc.resource_type_id
2682 AND rla.resource_list_id = rl.resource_list_id
2683 AND rla.project_id = x_project_id
2684 AND nvl(rtac.expenditure_type,0)=nvl(x_exp_type,nvl(rtac.expenditure_type,0))
2685 AND NVL(rl.migration_code,'M') ='M' -- Bug 3626671
2686 AND NVL(rlmc.migration_code,'M') ='M' -- Bug 3626671
2687 AND NVL(rlmp.migration_code,'M') ='M' -- Bug 3626671
2688 /* The next order by is very important.
2689 Ordering the resource by resource_list_assignment_id, resource_list_id */
2690 ORDER BY 1,2;
2691
2692 memberrec selmembers%ROWTYPE;
2693
2694 BEGIN
2695
2696 x_err_code := 0;
2697 x_no_of_resources := 0;
2698 -- x_err_stage := 'Getting Mappable Resources';
2699
2700
2701 --pa_debug.debug(x_err_stage);
2702
2703 -- get the resource list assignments and process them one by one
2704
2705 FOR memberrec IN selmembers LOOP
2706
2707 x_no_of_resources := x_no_of_resources + 1;
2708
2712 memberrec.resource_list_assignment_id;
2709 -- Get the mappable resource for this project
2710
2711 x_resource_list_assignment_id (x_no_of_resources) :=
2713 x_resource_list_id (x_no_of_resources) :=
2714 memberrec.resource_list_id;
2715 x_resource_list_member_id (x_no_of_resources) :=
2716 memberrec.resource_list_member_id;
2717 x_resource_list_member_id (x_no_of_resources) :=
2718 memberrec.resource_list_member_id;
2719 x_resource_id (x_no_of_resources) := memberrec.resource_id;
2720 x_member_level (x_no_of_resources) := memberrec.member_level;
2721 x_person_id (x_no_of_resources) := memberrec.person_id;
2722 x_job_id (x_no_of_resources) := memberrec.job_id;
2723 x_organization_id (x_no_of_resources) := memberrec.organization_id;
2724 x_vendor_id (x_no_of_resources) := memberrec.vendor_id;
2725 x_expenditure_type (x_no_of_resources) := memberrec.expenditure_type;
2726 x_event_type (x_no_of_resources) := memberrec.event_type;
2727 x_non_labor_resource (x_no_of_resources) := memberrec.non_labor_resource;
2728 x_expenditure_category (x_no_of_resources):= memberrec.expenditure_category;
2729 x_revenue_category (x_no_of_resources) := memberrec.revenue_category;
2730 x_non_labor_resource_org_id (x_no_of_resources) :=
2731 memberrec.non_labor_resource_org_id;
2732 x_event_type_classification (x_no_of_resources) :=
2733 memberrec.event_type_classification;
2734 x_system_linkage_function (x_no_of_resources) :=
2735 memberrec.system_linkage_function;
2736 x_resource_format_id (x_no_of_resources) := memberrec.resource_format_id;
2737 x_resource_type_code (x_no_of_resources) := memberrec.resource_type_code;
2738
2739 END LOOP;
2740
2741 -- pa_debug.debug('Number of resources found = ' || TO_CHAR(x_no_of_resources));
2742
2743 EXCEPTION
2744 WHEN NO_DATA_FOUND THEN
2745 NULL;
2746 WHEN OTHERS THEN
2747 x_err_code := SQLCODE;
2748 RAISE;
2749 END get_mappable_resources;
2750
2751 /* ----------------------------------------------------------------------------------------------------------------------------- */
2752 PROCEDURE map_trans
2753 ( x_project_id IN NUMBER,
2754 x_res_list_id IN NUMBER,
2755 x_person_id IN NUMBER,
2756 x_job_id IN NUMBER,
2757 x_organization_id IN NUMBER,
2758 x_vendor_id IN NUMBER,
2759 x_expenditure_type IN VARCHAR2,
2760 x_event_type IN VARCHAR2,
2761 x_non_labor_resource IN VARCHAR2,
2762 x_expenditure_category IN VARCHAR2,
2763 x_revenue_category IN VARCHAR2,
2764 x_non_labor_resource_org_id IN NUMBER,
2765 x_event_type_classification IN VARCHAR2,
2766 x_system_linkage_function IN VARCHAR2 ,
2767 x_exptype IN VARCHAR2 DEFAULT NULL,
2768 x_resource_list_member_id IN OUT NOCOPY NUMBER,
2769 x_err_stage IN OUT NOCOPY VARCHAR2,
2770 x_err_code IN OUT NOCOPY NUMBER
2771 ) -- bug 1111920 , add
2772 IS
2773
2774
2775 p_resource_list_assignment_id resource_list_asgn_id_tabtype;
2776 p_resource_list_id resource_list_id_tabtype;
2777 p_resource_list_member_id member_id_tabtype;
2778 p_resource_id resource_id_tabtype;
2779 p_member_level member_level_tabtype;
2780 p_person_id person_id_tabtype;
2781 p_job_id job_id_tabtype;
2782 p_organization_id organization_id_tabtype;
2783 p_vendor_id vendor_id_tabtype;
2784 p_expenditure_type expenditure_type_tabtype;
2785 p_event_type event_type_tabtype;
2786 p_non_labor_resource non_labor_resource_tabtype;
2787 p_expenditure_category expenditure_category_tabtype;
2788 p_revenue_category revenue_category_tabtype;
2789 p_non_labor_resource_org_id nlr_org_id_tabtype;
2790 p_event_type_classification event_type_class_tabtype;
2791 p_system_linkage_function system_linkage_tabtype;
2792 p_resource_format_id resource_format_id_tabtype;
2793 p_resource_type_code resource_type_code_tabtype;
2794 x_no_of_resources BINARY_INTEGER;
2795 res_count BINARY_INTEGER;
2796
2797 -- Variable to store the attributes of the resource list
2798
2799 current_rl_assignment_id NUMBER; -- Current resource list assignment id
2800 current_rl_id NUMBER; -- Current resource list id
2801 current_rl_changed_flag VARCHAR2(1); -- was this resource list changed?
2802 mapping_done BOOLEAN; -- is mapping done for current resource list
2803 current_rl_type_code VARCHAR2(20);-- current resource list type code
2804
2805 current_rl_member_id NUMBER;
2806 current_resource_id NUMBER;
2807 current_resource_rank NUMBER;
2808 current_member_level NUMBER;
2809 group_category_found BOOLEAN;
2810 attr_match_found BOOLEAN;
2811 new_resource_rank NUMBER;
2812
2813 old_resource_id NUMBER;
2814 old_rl_member_id NUMBER;
2815
2816 resource_map_found BOOLEAN;
2817
2818 -- member id for unclassified resources
2819
2820 uncl_group_member_id NUMBER;
2821 uncl_child_member_id NUMBER;
2822 uncl_resource_id NUMBER; -- assuming one resource_id for unclassfied
2826
2823
2824 BEGIN
2825 x_err_code :=0;
2827 -- ----------------------------------------------------------------
2828 -- To increase the resource map performance.
2829 -- ---------------------------------------------------------------
2830 --21-SEP-2000
2831 /*get_rlmi ( x_project_id ,
2832 x_res_list_id ,
2833 x_organization_id ,
2834 x_vendor_id ,
2835 x_expenditure_type ,
2836 x_non_labor_resource ,
2837 x_expenditure_category ,
2838 x_revenue_category ,
2839 x_non_labor_resource_org_id ,
2840 x_system_linkage_function,
2841 x_job_id,
2842 x_person_id,
2843 x_resource_list_member_id );*/
2844
2845 --if x_resource_list_member_id is null then -- Performance Improvement
2846
2847 -- Get the mappable resource for this project
2848 get_mappable_resources
2849 ( x_project_id,
2850 x_res_list_id,
2851 p_resource_list_id,
2852 p_resource_list_assignment_id,
2853 p_resource_list_member_id,
2854 p_resource_id,
2855 p_member_level,
2856 p_person_id,
2857 p_job_id,
2858 p_organization_id,
2859 p_vendor_id,
2860 p_expenditure_type,
2861 p_event_type,
2862 p_non_labor_resource,
2863 p_expenditure_category,
2864 p_revenue_category,
2865 p_non_labor_resource_org_id,
2866 p_event_type_classification,
2867 p_system_linkage_function,
2868 p_resource_format_id,
2869 p_resource_type_code,
2870 x_no_of_resources,
2871 x_err_stage,
2872 x_err_code,
2873 x_exptype);
2874
2875 -- Now process the transaction
2876
2877 -- Get the txns for which mapping is to be done
2878
2879
2880 -- Map this txn to all the resoure lists for this project
2881
2882 mapping_done := TRUE;
2883 current_rl_assignment_id :=0;
2884
2885 FOR res_count IN 1..x_no_of_resources LOOP
2886
2887 IF (current_rl_assignment_id <> p_resource_list_assignment_id(res_count)) THEN
2888
2889 -- Mapping to the next resource list
2890 -- Check if resource mapping was done for last resource_list_assigment_id or not
2891
2892 IF ( NOT mapping_done ) THEN
2893
2894 IF ( current_resource_id IS NULL ) THEN -- The last txn_accum could not be mapped
2895
2896 -- Map to unclassified Resource
2897 -- also if the group_category_found flag is true than map to unclassfied
2898 -- category within the group
2899
2900 current_resource_id := uncl_resource_id;
2901
2902 IF (group_category_found AND uncl_child_member_id <> 0) THEN
2903 current_rl_member_id := uncl_child_member_id;
2904 ELSE
2905 current_rl_member_id := uncl_group_member_id;
2906 END IF;
2907
2908 END IF; --- IF ( current_resource_id IS NULL )
2909
2910
2911 -- Create a map now
2912 create_resource_map
2913 (current_rl_id,
2914 current_rl_assignment_id,
2915 current_rl_member_id,
2916 current_resource_id,
2917 x_person_id,
2918 x_job_id,
2919 x_organization_id,
2920 x_vendor_id,
2921 x_expenditure_type,
2922 x_event_type,
2923 x_non_labor_resource,
2924 x_expenditure_category,
2925 x_revenue_category,
2926 x_non_labor_resource_org_id,
2927 x_event_type_classification,
2928 x_system_linkage_function,
2929 x_err_stage,
2930 x_err_code);
2931
2932
2933 END IF; -- IF ( NOT mapping_done )
2934
2935
2936 --- Proceed to the next resource list now
2937
2938 current_rl_assignment_id := p_resource_list_assignment_id(res_count);
2939 current_rl_id := p_resource_list_id(res_count);
2940 current_rl_changed_flag := get_resource_list_status(current_rl_assignment_id);
2941 current_rl_type_code := get_group_resource_type_code(current_rl_id);
2942 mapping_done := FALSE;
2943
2944 -- This variables will store the information for best match for the resource
2945 current_rl_member_id := NULL;
2946 current_resource_id := NULL;
2947 current_resource_rank := NULL;
2948 current_member_level := NULL;
2949 group_category_found := FALSE;
2950 uncl_group_member_id := 0;
2951 uncl_child_member_id := 0;
2952 uncl_resource_id := 0;
2953
2954 IF ( current_rl_changed_flag = 'Y' ) THEN -- This resource list assignmnet
2955 -- has been changed
2956 -- delete all the old maps for this resource list assignments
2957 -- for all the transactions
2958
2959 delete_res_maps_on_asgn_id(current_rl_assignment_id,x_err_stage,x_err_code);
2960 change_resource_list_status(current_rl_assignment_id,x_err_stage,x_err_code);
2961
2962 ELSIF ( current_rl_changed_flag = 'N' ) THEN
2963 -- Get the resource map status
2964
2965
2966
2967 get_resource_map
2968 (current_rl_id,
2969 current_rl_assignment_id,
2970 x_person_id,
2971 x_job_id,
2972 x_organization_id,
2973 x_vendor_id,
2974 x_expenditure_type,
2975 x_event_type,
2976 x_non_labor_resource,
2977 x_expenditure_category,
2978 x_revenue_category,
2982 old_rl_member_id,
2979 x_non_labor_resource_org_id,
2980 x_event_type_classification,
2981 x_system_linkage_function,
2983 old_resource_id,
2984 resource_map_found,
2985 x_err_stage,
2986 x_err_code);
2987
2988 -- check if a map exist for the given attributes in the map table
2989 IF (resource_map_found) THEN
2990 mapping_done := TRUE;
2991 x_resource_list_member_id := old_rl_member_id;
2992 return;
2993 END IF; -- IF (resource_map_found)
2994
2995 if(resource_map_found) THEN
2996 pa_debug.debug('an old MAP IS FOUND');
2997 else
2998 pa_debug.debug('old MAP IS not FOUND');
2999 end if;
3000 END IF;
3001
3005
3002 END IF; -- IF (current_rl_assignment_id <> p_resource_list_assignment_id ....
3003
3004 IF ( NOT mapping_done ) THEN
3006 -- Mapping still need to be done
3007 attr_match_found := TRUE;
3008
3009 IF ((p_resource_type_code(res_count) = 'UNCLASSIFIED' OR
3010 p_resource_type_code(res_count) = 'UNCATEGORIZED') AND
3011 p_member_level(res_count) = 1 ) THEN
3012 attr_match_found := FALSE;
3013 uncl_resource_id := p_resource_id(res_count);
3014 uncl_group_member_id := p_resource_list_member_id(res_count);
3015 END IF;
3016
3017 IF ( current_rl_type_code = 'EXPENDITURE_CATEGORY') THEN
3018
3019 -- The resource list is based on the expenditure category
3020
3021 IF ( p_expenditure_category(res_count) = x_expenditure_category) THEN
3022 group_category_found := TRUE;
3023 ELSE
3024 attr_match_found := FALSE;
3025 END IF; --IF ( p_expenditure_category(res_count).....
3026
3027 ELSIF ( current_rl_type_code = 'REVENUE_CATEGORY' ) THEN
3028
3029 -- The resource list is based on the revenue category
3030
3031 IF (p_revenue_category(res_count) = x_revenue_category) THEN
3032 group_category_found := TRUE;
3033 ELSE
3034 attr_match_found := FALSE;
3035 END IF; -- IF (p_revenue_category(res_count) ....
3036
3037 ELSIF ( current_rl_type_code = 'ORGANIZATION' ) THEN
3038
3039 -- The resource list is based on the organization
3040
3041 IF (p_organization_id(res_count) = x_organization_id) THEN
3042 group_category_found := TRUE;
3043 ELSE
3044 attr_match_found := FALSE;
3045 END IF; -- IF (p_organization_id(res_count)
3046
3047 END IF; -- IF ( current_rl_type_code = 'EXPENDITURE_CATEGORY'...
3048
3049 IF ( current_rl_type_code = 'NONE' OR attr_match_found ) THEN
3050
3051 -- The resource list is based on the none category
3052
3053 -- Now compare the txn attributes with resource attributes
3054
3055 -- The table given below determines if the resource is eligible
3056 -- for accumulation or not
3057
3058 -- TXN ATTRIBUTE RESOURCE ATTRIBUTE ELIGIBLE
3059 -- NULL NULL YES
3060 -- NULL NOT NULL NO
3061 -- NOT NULL NULL YES
3062 -- NOT NULL NOT NULL YES/NO depending on value
3063
3064 -- Do not match the attributes for an unclassified resource
3065
3066 IF (p_resource_type_code(res_count) = 'UNCLASSIFIED' ) THEN
3067 attr_match_found := FALSE;
3068 uncl_resource_id := p_resource_id(res_count);
3069 IF ( p_member_level(res_count) = 1 ) THEN -- group level unclassified
3070 uncl_group_member_id := p_resource_list_member_id(res_count);
3071 ELSE
3072 uncl_child_member_id := p_resource_list_member_id(res_count);
3073 END IF;
3074 END IF;
3075
3076 IF (NOT (attr_match_found AND
3077 (NVL(p_person_id(res_count),NVL(x_person_id,-1)) =
3078 NVL(x_person_id, -1)))) THEN
3079 attr_match_found := FALSE;
3080 END IF;
3081 IF (NOT (attr_match_found AND
3082 (NVL(p_job_id(res_count),NVL(x_job_id,-1)) =
3083 NVL(x_job_id, -1)))) THEN
3084 attr_match_found := FALSE;
3085 END IF;
3086 IF (NOT (attr_match_found AND
3087 (NVL(p_organization_id(res_count),NVL(x_organization_id,-1)) =
3088 NVL(x_organization_id, -1)))) THEN
3089 attr_match_found := FALSE;
3090 END IF;
3091 IF (NOT (attr_match_found AND
3092 (NVL(p_vendor_id(res_count),NVL(x_vendor_id,-1)) =
3093 NVL(x_vendor_id, -1)))) THEN
3094 attr_match_found := FALSE;
3095 END IF;
3096 IF (NOT (attr_match_found AND
3097 (NVL(p_expenditure_type(res_count),NVL(x_expenditure_type,'X')) =
3101 IF (NOT (attr_match_found AND
3098 NVL(x_expenditure_type, 'X')))) THEN
3099 attr_match_found := FALSE;
3100 END IF;
3102 (NVL(p_event_type(res_count),NVL(x_event_type,'X')) =
3103 NVL(x_event_type, 'X')))) THEN
3104 attr_match_found := FALSE;
3105 END IF;
3106 IF (NOT (attr_match_found AND
3107 (NVL(p_non_labor_resource(res_count),NVL(x_non_labor_resource,'X')) =
3108 NVL(x_non_labor_resource, 'X')))) THEN
3109 attr_match_found := FALSE;
3110 END IF;
3111 IF (NOT (attr_match_found AND
3112 (NVL(p_expenditure_category(res_count),NVL(x_expenditure_category,'X')) =
3113 NVL(x_expenditure_category, 'X')))) THEN
3114 attr_match_found := FALSE;
3115 END IF;
3116 IF (NOT (attr_match_found AND
3117 (NVL(p_revenue_category(res_count),NVL(x_revenue_category,'X')) =
3118 NVL(x_revenue_category,'X')))) THEN
3119 attr_match_found := FALSE;
3120 END IF;
3121 IF (NOT (attr_match_found AND
3122 (NVL(p_non_labor_resource_org_id(res_count),NVL(x_non_labor_resource_org_id,-1)) =
3123 NVL(x_non_labor_resource_org_id,-1)))) THEN
3124 attr_match_found := FALSE;
3125 END IF;
3126 IF (NOT (attr_match_found AND
3127 (NVL(p_event_type_classification(res_count),NVL(x_event_type_classification,'X')) =
3128 NVL(x_event_type_classification,'X')))) THEN
3129 attr_match_found := FALSE;
3130 END IF;
3131 IF (NOT (attr_match_found AND
3132 (NVL(p_system_linkage_function(res_count),NVL(x_system_linkage_function,'X')) =
3133 NVL(x_system_linkage_function,'X')))) THEN
3134 attr_match_found := FALSE;
3135 END IF;
3136
3137 END IF; --IF ( current_rl_type_code = 'NONE'......
3138 IF (attr_match_found) THEN
3139
3140 -- Get the resource rank now
3141
3142 IF ( x_event_type_classification IS NOT NULL ) THEN
3143
3144 -- determine the rank based on event_type_classification
3145 new_resource_rank := get_resource_rank(
3146 p_resource_format_id(res_count),
3147 x_event_type_classification);
3148 ELSE
3149 -- determine the rank based on system_linkage_function
3150 new_resource_rank := get_resource_rank(
3151 p_resource_format_id(res_count),
3152 x_system_linkage_function);
3153 END IF; -- IF ( x_event_type_classification IS NOT NULL )
3154
3155 IF ( NVL(new_resource_rank,99) < NVL(current_resource_rank,99) ) THEN
3156
3157 current_resource_rank := new_resource_rank;
3158 current_rl_member_id := p_resource_list_member_id(res_count);
3159 current_resource_id := p_resource_id(res_count);
3160 current_member_level := p_member_level(res_count);
3161
3162 END IF;
3163 END IF; -- IF (attr_match_found)
3164
3165 END IF; -- IF ( NOT mapping_done ) THEN
3166
3167 END LOOP;
3168
3169 -- Now create the map for the last resoure list assignment
3170 IF ( NOT mapping_done ) THEN
3171
3172 IF ( current_resource_id IS NULL ) THEN -- The last txn_accum could not be mapped
3173
3174 -- Map to unclassified Resource
3175 -- also if the group_category_found flag is true than map to unclassfied
3176 -- category within the group
3177
3178 current_resource_id := uncl_resource_id;
3179
3180 IF (group_category_found AND uncl_child_member_id <> 0) THEN
3181 current_rl_member_id := uncl_child_member_id;
3182 ELSE
3183 current_rl_member_id := uncl_group_member_id;
3184 END IF;
3185
3186 END IF; --- IF ( current_resource_id IS NULL )
3187 -- Create a map now
3188 create_resource_map
3189 (current_rl_id,
3190 current_rl_assignment_id,
3191 current_rl_member_id,
3192 current_resource_id,
3193 x_person_id,
3194 x_job_id,
3195 x_organization_id,
3196 x_vendor_id,
3197 x_expenditure_type,
3198 x_event_type,
3199 null,--x_non_labor_resource,
3200 x_expenditure_category,
3201 x_revenue_category,
3202 x_non_labor_resource_org_id,
3203 x_event_type_classification,
3204 x_system_linkage_function,
3205 x_err_stage,
3206 x_err_code);
3207
3208 END IF;
3209 x_resource_list_member_id := current_rl_member_id;
3210 return;
3211
3212
3213 --END IF; --Performance Improvement
3214 EXCEPTION
3215 -- Return if either no resource list are assigned to the project and/or
3216 -- no records in pa_txn_accum table need to be rolled up
3217
3218 WHEN NO_DATA_FOUND THEN
3219 NULL;
3220 WHEN OTHERS THEN
3221 x_err_code := SQLCODE;
3222 RAISE;
3223 END map_trans;
3224
3225 END GMS_RES_MAP;
[Home] [Help]