[Home] [Help]
PACKAGE BODY: APPS.PA_RBS_UTILS
Source
1 PACKAGE BODY PA_RBS_UTILS AS
2 /* $Header: PARRBSUB.pls 120.10 2011/03/30 05:48:09 svmohamm ship $ */
3
4 /********************************************************
5 * Function : get_max_rbs_frozen_version
6 * Description : Get the latest frozen version of an RBS.
7 *******************************************************/
8 FUNCTION get_max_rbs_frozen_version(p_rbs_header_id IN NUMBER) return NUMBER
9 IS
10
11 l_version_id NUMBER := NULL;
12 BEGIN
13 IF p_rbs_header_id IS NOT NULL THEN
14 BEGIN
15 select rbs_version_id
16 into l_version_id
17 from pa_rbs_versions_b ver1
18 where ver1.rbs_header_id = p_rbs_header_id
19 and ver1.current_reporting_flag = 'Y';
20 -- sysdate join?
21 EXCEPTION
22 WHEN NO_DATA_FOUND THEN
23 l_version_id := NULL;
24 WHEN OTHERS THEN
25 l_version_id := NULL;
26 END;
27 END IF;
28
29 RETURN l_version_id;
30
31 END get_max_rbs_frozen_version;
32
33 /*****************************************************
34 * Function : Get_element_Name
35 * Description : This Function is used to return the
36 * Element_Name for a given
37 * resource_source_id and resource_type_code
38 * passed in.
39 * **************************************************/
40 Function Get_element_Name
41 (p_resource_source_id IN Number,
42 p_resource_type_code IN Varchar2)
43 RETURN Varchar2
44 IS
45 l_element_name Varchar2(240);
46 l_return_status Varchar2(30);
47 l_msg_data Varchar2(30);
48 l_revenue_category_code Varchar2(30);
49 BEGIN
50 /****************************************************************
51 * If the p_resource_source_id or the p_resource_type_code
52 * passed in is Null then we cannot derive the element_name
53 * so just pass back null and return.
54 * ****************************************************************/
55 IF p_resource_source_id IS NULL OR p_resource_type_code IS NULL THEN
56 l_element_name := NULL;
57 Return l_element_name;
58 END IF;
59
60 IF p_resource_type_code = 'VERSION' THEN
61 BEGIN
62 SELECT name
63 INTO l_element_name
64 FROM Pa_rbs_versions_tl
65 WHERE rbs_version_id = p_resource_source_id
66 AND language = userenv('LANG');
67 EXCEPTION
68 WHEN OTHERS THEN
69 l_element_name := Null;
70 END;
71 END IF;
72 --If the p_resource_type_code = NAMED_PERSON
73 --Then call to Get_Resource_Name to get the
74 --element_name
75 IF p_resource_type_code = 'NAMED_PERSON' THEN
76 Pa_Planning_resource_utils.Get_Resource_Name
77 ( P_Res_Type_Code => 'NAMED_PERSON',
78 P_Person_Id => p_resource_source_id,
79 P_Bom_Resource_Id => null,
80 P_Job_Id => null,
81 P_Person_Type_Code => null,
82 P_Non_Labor_Resource => null,
83 P_Inventory_Item_Id => null,
84 P_Item_Category_Id => null,
85 P_Resource_Class_Id => null,
86 P_Proc_Func_Flag => null,
87 P_Res_Assignment_Id => null,
88 X_Resource_Displayed => l_element_name,
89 X_Return_Status => l_return_status,
90 X_Msg_Data => l_msg_data );
91 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
92 l_element_name := NULL;
93 END IF;
94 END IF;
95
96 --If the p_resource_type_code = JOB
97 --Then call to Get_Resource_Name to get the
98 --element_name
99 IF p_resource_type_code = 'JOB' THEN
100 Pa_Planning_resource_utils.Get_Resource_Name
101 ( P_Res_Type_Code => 'JOB',
102 P_Person_Id => null,
103 P_Bom_Resource_Id => null,
104 P_Job_Id => p_resource_source_id,
105 P_Person_Type_Code => null,
106 P_Non_Labor_Resource => null,
107 P_Inventory_Item_Id => null,
108 P_Item_Category_Id => null,
109 P_Resource_Class_Id => null,
110 P_Proc_Func_Flag => null,
111 P_Res_Assignment_Id => null,
112 X_Resource_Displayed => l_element_name,
113 X_Return_Status => l_return_status,
114 X_Msg_Data => l_msg_data );
115 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
116 l_element_name := NULL;
117 END IF;
118 END IF;
119
120 --If the p_resource_type_code = PERSON_TYPE
121 --Then use the below select to get the
122 --element_name
123 IF p_resource_type_code = 'PERSON_TYPE' THEN
124 BEGIN
125 SELECT lk.meaning
126 INTO l_element_name
127 from hr_lookups lk, per_person_types per
128 where lk.lookup_type = 'PERSON_TYPE'
129 and per.system_person_type in ('EMP', 'CWK')
130 and per.system_person_type = lk.lookup_code
131 and per.business_group_id = 0
132 and per.person_type_id = p_resource_source_id;
133 EXCEPTION WHEN OTHERS THEN
134 l_element_name := NULL;
135 END;
136 END IF;
137
138 --If the p_resource_type_code = BOM_LABOR
139 --Then call to Get_Resource_Name to get the
140 --element_name
141 IF p_resource_type_code = 'BOM_LABOR' THEN
142 Pa_Planning_resource_utils.Get_Resource_Name
143 ( P_Res_Type_Code => 'BOM_LABOR',
144 P_Person_Id => null,
145 P_Bom_Resource_Id => p_resource_source_id,
146 P_Job_Id => null,
147 P_Person_Type_Code => null,
148 P_Non_Labor_Resource => null,
149 P_Inventory_Item_Id => null,
150 P_Item_Category_Id => null,
151 P_Resource_Class_Id => null,
152 P_Proc_Func_Flag => null,
153 P_Res_Assignment_Id => null,
154 X_Resource_Displayed => l_element_name,
155 X_Return_Status => l_return_status,
156 X_Msg_Data => l_msg_data );
157 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
158 l_element_name := NULL;
159 END IF;
160 END IF;
161
162 --If the p_resource_type_code = BOM_EQUIPMENT
163 --Then call to Get_Resource_Name to get the
164 --element_name
165 IF p_resource_type_code = 'BOM_EQUIPMENT' THEN
166 Pa_Planning_resource_utils.Get_Resource_Name
167 ( P_Res_Type_Code => 'BOM_EQUIPMENT',
168 P_Person_Id => null,
169 P_Bom_Resource_Id => p_resource_source_id,
170 P_Job_Id => null,
171 P_Person_Type_Code => null,
172 P_Non_Labor_Resource => null,
173 P_Inventory_Item_Id => null,
174 P_Item_Category_Id => null,
175 P_Resource_Class_Id => null,
176 P_Proc_Func_Flag => null,
177 P_Res_Assignment_Id => null,
178 X_Resource_Displayed => l_element_name,
179 X_Return_Status => l_return_status,
180 X_Msg_Data => l_msg_data );
181 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
182 l_element_name := NULL;
183 END IF;
184 END IF;
185
186 --If the p_resource_type_code = ITEM_CATEGORY
187 --Then call to Get_Resource_Name to get the
188 --element_name
189 IF p_resource_type_code = 'ITEM_CATEGORY' THEN
190 Pa_Planning_resource_utils.Get_Resource_Name
191 ( P_Res_Type_Code => 'ITEM_CATEGORY',
192 P_Person_Id => null,
193 P_Bom_Resource_Id => null,
194 P_Job_Id => null,
195 P_Person_Type_Code => null,
196 P_Non_Labor_Resource => null,
197 P_Inventory_Item_Id => null,
198 P_Item_Category_Id => p_resource_source_id,
199 P_Resource_Class_Id => null,
200 P_Proc_Func_Flag => null,
201 P_Res_Assignment_Id => null,
202 X_Resource_Displayed => l_element_name,
203 X_Return_Status => l_return_status,
204 X_Msg_Data => l_msg_data );
205 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
206 l_element_name := NULL;
207 END IF;
208 END IF;
209
210 --If the p_resource_type_code = INVENTORY_ITEM
211 --Then call to Get_Resource_Name to get the
212 --element_name
213 IF p_resource_type_code = 'INVENTORY_ITEM' THEN
214 Pa_Planning_resource_utils.Get_Resource_Name
215 ( P_Res_Type_Code => 'INVENTORY_ITEM',
216 P_Person_Id => null,
217 P_Bom_Resource_Id => null,
218 P_Job_Id => null,
219 P_Person_Type_Code => null,
220 P_Non_Labor_Resource => null,
221 P_Inventory_Item_Id => p_resource_source_id,
222 P_Item_Category_Id => null,
223 P_Resource_Class_Id => null,
224 P_Proc_Func_Flag => null,
225 P_Res_Assignment_Id => null,
226 X_Resource_Displayed => l_element_name,
227 X_Return_Status => l_return_status,
228 X_Msg_Data => l_msg_data );
229 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
230 l_element_name := NULL;
231 END IF;
232 END IF;
233
234 --If the p_resource_type_code = NON_LABOR_RESOURCE
235 --Then call to Get_Resource_Name to get the
236 --element_name
237 IF p_resource_type_code = 'NON_LABOR_RESOURCE' THEN
238 BEGIN
239 SELECT non_labor_resource
240 INTO l_element_name
241 FROM pa_non_labor_resources
242 WHERE NON_LABOR_RESOURCE_ID = p_resource_source_id;
243 EXCEPTION
244 WHEN OTHERS THEN
245 l_element_name := NULL;
246 END;
247 END IF;
248
249 --If the p_resource_type_code = RESOURCE_CLASS
250 --Then call to Get_Resource_Name to get the
251 --element_name
252 IF p_resource_type_code = 'RESOURCE_CLASS' THEN
253 Pa_Planning_resource_utils.Get_Resource_Name
254 ( P_Res_Type_Code => 'RESOURCE_CLASS',
255 P_Person_Id => null,
256 P_Bom_Resource_Id => null,
257 P_Job_Id => null,
258 P_Person_Type_Code => null,
259 P_Non_Labor_Resource => null,
260 P_Inventory_Item_Id => null,
261 P_Item_Category_Id => null,
262 P_Resource_Class_Id => p_resource_source_id,
263 P_Proc_Func_Flag => null,
264 P_Res_Assignment_Id => null,
265 X_Resource_Displayed => l_element_name,
266 X_Return_Status => l_return_status,
267 X_Msg_Data => l_msg_data );
268 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
269 l_element_name := NULL;
270 END IF;
271 END IF;
272
273 IF p_resource_type_code = 'REVENUE_CATEGORY' THEN
274 /***********************************************
275 * Get the revenue Category Code from pa_rbs_element_map
276 * table, based on the ID passed.
277 ************************************************/
278 BEGIN
279 SELECT RESOURCE_NAME
280 INTO l_revenue_category_code
281 FROM Pa_rbs_element_map
282 --WHERE RESOURCE_NAME = 'REVENUE_CATEGORY' -- NEED TO REVISIT
283 WHERE resource_type_id =
284 (SELECT res_type_id
285 FROM pa_res_types_b
286 WHERE res_type_code = 'REVENUE_CATEGORY')
287 AND RESOURCE_ID = p_resource_source_id;
288 EXCEPTION
289 WHEN OTHERS THEN
290 l_element_name := Null;
291 END;
292
293 /***********************************************
294 * Get the Meaning from Pa_Lookups
295 * table, for the lookup_type = 'REVENUE CATEGORY'
296 * and lookup code = the l_revenue_category_code.
297 ************************************************/
298 BEGIN
299 SELECT lk.Meaning
300 INTO l_element_name
301 FROM PA_LOOKUPS lk
302 WHERE lk.Lookup_Type = 'REVENUE CATEGORY'
303 and lk.lookup_code = l_revenue_category_code;
304 EXCEPTION
305 WHEN OTHERS THEN
306 l_element_name := Null;
307 END;
308
309 END IF;
310
311 --If the p_resource_type_code = EVENT_TYPE
312 --Then use the below select to get the
313 --element_name
314 IF p_resource_type_code = 'EVENT_TYPE' THEN
315 BEGIN
316 SELECT Event_Type
317 INTO l_element_name
318 FROM pa_event_types
319 WHERE event_type_id = p_resource_source_id;
320 EXCEPTION
321 WHEN OTHERS THEN
322 l_element_name := Null;
323 END;
324 END IF;
325
326 --If the p_resource_type_code = EXPENDITURE_TYPE
327 --Then use the below select to get the
328 --element_name
329 IF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
330 BEGIN
331 SELECT expenditure_type
332 INTO l_element_name
333 FROM pa_expenditure_types
334 WHERE expenditure_type_id = p_resource_source_id;
335 EXCEPTION
336 WHEN OTHERS THEN
337 l_element_name := Null;
338 END;
339 END IF;
340
341 --If the p_resource_type_code = EXPENDITURE_CATEGORY
342 --Then use the below select to get the
343 --element_name
344 IF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
345 BEGIN
346 SELECT expenditure_category
347 INTO l_element_name
348 FROM pa_expenditure_categories
349 WHERE expenditure_category_id = p_resource_source_id;
350 EXCEPTION
351 WHEN OTHERS THEN
352 l_element_name := Null;
353 END;
354 END IF;
355
356 --If the p_resource_type_code = ORGANIZATION
357 --Then call to Ret_Organization_Name to get the
358 --element_name
359 IF p_resource_type_code = 'ORGANIZATION' THEN
360 BEGIN
361 l_element_name := Pa_Planning_Resource_Utils.Ret_Organization_Name
362 (P_Organization_Id => p_resource_source_id);
363 EXCEPTION
364 WHEN OTHERS THEN
365 l_element_name := Null;
366 END;
367 END IF;
368
369 --If the p_resource_type_code = ROLE
370 --Then call to Ret_Role_Name to get the
371 --element_name
372 IF p_resource_type_code = 'ROLE' THEN
373 BEGIN
374 l_element_name := Pa_Planning_Resource_Utils.Ret_Role_Name
375 (P_Role_Id => p_resource_source_id);
376 EXCEPTION
377 WHEN OTHERS THEN
378 l_element_name := Null;
379 END;
380 END IF;
381
382 --If the p_resource_type_code = SUPPLIER
383 --Then use the below select to get the
384 --element_name
385 IF p_resource_type_code = 'SUPPLIER' THEN
386 BEGIN
387 SELECT Vendor_Name
388 INTO l_element_name
389 FROM po_vendors
390 WHERE vendor_id = p_resource_source_id;
391 EXCEPTION
392 WHEN OTHERS THEN
393 l_element_name := Null;
394 END;
395 END IF;
396
397 --If the p_resource_type_code IN (NAMED_ROLE,USER_DEFINED)
398 --Then use the below select to get the
399 --element_name
400 IF p_resource_type_code IN ('NAMED_ROLE','USER_DEFINED') THEN
401 BEGIN
402 SELECT resource_name
403 INTO l_element_name
404 FROM pa_rbs_element_map
405 --WHERE RESOURCE_NAME = p_resource_type_code -- NEED TO REVISIT
406 WHERE resource_type_id =
407 (SELECT res_type_id
408 FROM pa_res_types_b
409 WHERE res_type_code = 'REVENUE_CATEGORY')
410 AND RESOURCE_ID = p_resource_source_id;
411 EXCEPTION
412 WHEN OTHERS THEN
413 l_element_name := Null;
414 END;
415 END IF;
416
417 Return l_element_name;
418
419 END Get_element_Name;
420 /********************************************************
421 * Procedure : Insert_elements
422 * Description : This Procedure is used to insert into
423 * the pa_rbs_element_names_b table
424 * it does a direct
425 * Insert from the pa_rbs_elem_in_temp
426 * table based on the resource_type_id
427 * passed.
428 * *****************************************************/
429 PROCEDURE Insert_elements(p_resource_type_id IN NUMBER,
430 x_return_status OUT NOCOPY Varchar2)
431 IS
432 --l_element_name_id Number;
433 BEGIN
434 -- IF p_call_flag = 'B' THEN
435 INSERT INTO Pa_rbs_element_names_b
436 (RBS_ELEMENT_NAME_ID,
437 RESOURCE_SOURCE_ID,
438 RESOURCE_TYPE_ID,
439 LAST_UPDATE_DATE,
440 LAST_UPDATED_BY,
441 CREATION_DATE,
442 CREATED_BY,
443 LAST_UPDATE_LOGIN)
444 SELECT
445 PA_RBS_ELEMENT_NAMES_S.NEXTVAL,
446 a.resource_source_id,
447 a.resource_type_id,
448 sysdate,
449 FND_GLOBAL.USER_ID,
450 sysdate,
451 FND_GLOBAL.USER_ID,
452 FND_GLOBAL.LOGIN_ID
453 FROM pa_rbs_elem_in_temp a
454 WHERE a.resource_type_id = p_resource_type_id
455 AND NOT EXISTS (select 'Y'
456 FROM Pa_rbs_element_names_b b
457 where b.RESOURCE_TYPE_ID = a.resource_type_id
458 and b.RESOURCE_SOURCE_ID = a.resource_source_id);
459 -- AND (a.resource_source_id,a.resource_type_id)
460 -- NOT IN (SELECT RESOURCE_SOURCE_ID, RESOURCE_TYPE_ID
461 -- FROM Pa_rbs_element_names_b
462 -- where RESOURCE_TYPE_ID = p_resource_type_id);
463 EXCEPTION
464 WHEN OTHERS THEN
465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466 RETURN;
467 END Insert_elements;
468
469 /******************************************************
470 * Procedure : Insert_non_tl_names
471 * Description : This API is used to insert into the
472 * pa_rbs_element_names_tl table.
473 * For those res_type_codes for which there
474 * is no Multi lang support.
475 * We are going to insert the value based
476 * on the values inserted into the
477 * pa_rbs_element_names_b table.
478 * **************************************************/
479 PROCEDURE Insert_non_tl_names
480 (p_resource_type_id IN Number,
481 p_resource_type_code IN Varchar2,
482 x_return_status OUT NOCOPY Varchar2)
483 IS
484 l_count Number;
485 l_temp_count Number;
486 BEGIN
487 IF p_resource_type_code = 'VERSION' THEN--VERSION
488 INSERT INTO Pa_rbs_element_names_tl
489 (RBS_ELEMENT_NAME_ID,
490 RESOURCE_NAME,
491 Language,
492 Source_Lang,
493 Last_Update_Date,
494 Last_Updated_By,
495 Creation_Date,
496 Created_by,
497 Last_Update_Login)
498 SELECT
499 a.rbs_element_name_id,
500 c.name,
501 c.language,
502 c.source_lang,
503 sysdate,
504 FND_GLOBAL.USER_ID,
505 sysdate,
506 FND_GLOBAL.USER_ID,
507 FND_GLOBAL.LOGIN_ID
508 FROM
509 pa_rbs_element_names_b a,
510 pa_rbs_elem_in_temp b,
511 Pa_rbs_versions_tl c
512 WHERE a.resource_source_id = b.resource_source_id
513 AND a.resource_type_id = p_resource_type_id
514 AND b.resource_type_id = p_resource_type_id
515 AND c.rbs_version_id = a.resource_source_id
516 AND NOT EXISTS (select 'Y'
517 FROM pa_rbs_element_names_tl tl
518 where tl.rbs_element_name_id = a.rbs_element_name_id
519 and tl.language = c.language);
520 END IF;--VERSION
521
522 IF p_resource_type_code = 'RULE' THEN--RULE
523 INSERT INTO Pa_rbs_element_names_tl
524 (RBS_ELEMENT_NAME_ID,
525 RESOURCE_NAME,
526 Language,
527 Source_Lang,
528 Last_Update_Date,
529 Last_Updated_By,
530 Creation_Date,
531 Created_by,
532 Last_Update_Login)
533 SELECT
534 a.rbs_element_name_id,
535 c.meaning,
536 c.language,
537 c.source_lang,
538 sysdate,
539 FND_GLOBAL.USER_ID,
540 sysdate,
541 FND_GLOBAL.USER_ID,
542 FND_GLOBAL.LOGIN_ID
543 FROM
544 pa_rbs_element_names_b a,
545 pa_rbs_elem_in_temp b,
546 fnd_lookup_values c
547 WHERE a.resource_source_id = b.resource_source_id
548 AND a.resource_type_id = p_resource_type_id
549 AND b.resource_type_id = p_resource_type_id
550 AND c.lookup_type = 'RBS_RULE_RESOURCE'
551 AND c.lookup_code = 'USER_DEFINED_RESOURCE'
552 AND c.view_application_id = 275
553 AND NOT EXISTS (select 'Y'
554 FROM pa_rbs_element_names_tl tl
555 where tl.rbs_element_name_id = a.rbs_element_name_id
556 and tl.language = c.language);
557 END IF;--RULE
558
559 --MLS Changes.
560 IF p_resource_type_code = 'NAMED_PERSON' THEN--NAMED_PERSON
561 INSERT INTO Pa_rbs_element_names_tl
562 (RBS_ELEMENT_NAME_ID,
563 RESOURCE_NAME,
564 Language,
565 Source_Lang,
566 Last_Update_Date,
567 Last_Updated_By,
568 Creation_Date,
569 Created_by,
570 Last_Update_Login)
571 SELECT
572 a.rbs_element_name_id,
573 c.full_name,
574 l.language_code,
575 USERENV('LANG'),
576 sysdate,
577 FND_GLOBAL.USER_ID,
578 sysdate,
579 FND_GLOBAL.USER_ID,
580 FND_GLOBAL.LOGIN_ID
581 FROM
582 pa_rbs_element_names_b a,
583 pa_rbs_elem_in_temp b,
584 per_all_people_f c,
585 fnd_languages l
586 WHERE a.resource_source_id = b.resource_source_id
587 AND a.resource_type_id = p_resource_type_id
588 AND b.resource_type_id = p_resource_type_id
589 AND c.person_Id = a.resource_source_id
590 and sysdate between c.effective_start_date
591 and c.effective_end_date
592 and l.Installed_Flag in ('I', 'B')
593 AND NOT EXISTS (select 'Y'
594 FROM pa_rbs_element_names_tl tl
595 where tl.rbs_element_name_id = a.rbs_element_name_id
596 and tl.language = l.language_code);
597 END IF;--NAMED_PERSON
598
599 --MLS Changes.
600 IF p_resource_type_code = 'JOB' THEN--JOB
601 INSERT INTO Pa_rbs_element_names_tl
602 (RBS_ELEMENT_NAME_ID,
603 RESOURCE_NAME,
604 Language,
605 Source_Lang,
606 Last_Update_Date,
607 Last_Updated_By,
608 Creation_Date,
609 Created_by,
610 Last_Update_Login)
611 SELECT
612 a.rbs_element_name_id,
613 c.name,
614 l.language_code,
615 USERENV('LANG'),
616 sysdate,
617 FND_GLOBAL.USER_ID,
618 sysdate,
619 FND_GLOBAL.USER_ID,
620 FND_GLOBAL.LOGIN_ID
621 FROM
622 pa_rbs_element_names_b a,
623 pa_rbs_elem_in_temp b,
624 Per_Jobs c,
625 Fnd_Languages L
626 WHERE a.resource_source_id = b.resource_source_id
627 AND a.resource_type_id = p_resource_type_id
628 AND b.resource_type_id = p_resource_type_id
629 AND c.Job_Id = a.resource_source_id
630 and l.Installed_Flag in ('I', 'B')
631 AND NOT EXISTS (select 'Y'
632 FROM pa_rbs_element_names_tl tl
633 where tl.rbs_element_name_id = a.rbs_element_name_id
634 and tl.language = l.language_code);
635 END IF;--JOB
636
637 IF p_resource_type_code = 'PERSON_TYPE' THEN --PERSON_TYPE
638 INSERT INTO Pa_rbs_element_names_tl
639 (RBS_ELEMENT_NAME_ID,
640 RESOURCE_NAME,
641 Language,
642 Source_Lang,
643 Last_Update_Date,
644 Last_Updated_By,
645 Creation_Date,
646 Created_by,
647 Last_Update_Login)
648 SELECT
649 a.rbs_element_name_id,
650 lk.meaning,
651 lk.language,
652 lk.source_lang,
653 sysdate,
654 FND_GLOBAL.USER_ID,
655 sysdate,
656 FND_GLOBAL.USER_ID,
657 FND_GLOBAL.LOGIN_ID
658 FROM
659 pa_rbs_element_names_b a,
660 pa_rbs_elem_in_temp b,
661 fnd_lookup_values lk,
662 /* Changes for Bug 3780201 start*/
663 pa_rbs_element_map c
664 WHERE a.resource_source_id = b.resource_source_id
665 and a.resource_type_id = p_resource_type_id
666 and b.resource_type_id = p_resource_type_id
667 and lk.lookup_type = 'PA_PERSON_TYPE'
668 and c.resource_id = a.resource_source_id
669 and c.resource_type_id = p_resource_type_id
670 and lk.lookup_code = c.resource_name
671 and NOT EXISTS (select 'Y'
672 FROM pa_rbs_element_names_tl tl
673 where tl.rbs_element_name_id = a.rbs_element_name_id
674 and tl.language = lk.language);
675 END IF;--PERSON_TYPE
676
677 --MLS Changes.
678 IF p_resource_type_code IN ('BOM_LABOR','BOM_EQUIPMENT') THEN
679 --BOM_LABOR,BOM_EQUIPMENT
680 INSERT INTO Pa_rbs_element_names_tl
681 (RBS_ELEMENT_NAME_ID,
682 RESOURCE_NAME,
683 Language,
684 Source_Lang,
685 Last_Update_Date,
686 Last_Updated_By,
687 Creation_Date,
688 Created_by,
689 Last_Update_Login)
690 SELECT
691 a.rbs_element_name_id,
692 --For bug 3602566.
693 --c.description,
694 c.resource_code, --End of bug 3602566.
695 l.language_code,
696 USERENV('LANG'),
697 sysdate,
698 FND_GLOBAL.USER_ID,
699 sysdate,
700 FND_GLOBAL.USER_ID,
701 FND_GLOBAL.LOGIN_ID
702 FROM
703 pa_rbs_element_names_b a,
704 pa_rbs_elem_in_temp b,
705 Bom_Resources c,
706 Fnd_Languages L
707 WHERE a.resource_source_id = b.resource_source_id
708 and c.resource_id = a.resource_source_id
709 AND a.resource_type_id = p_resource_type_id
710 AND b.resource_type_id = p_resource_type_id
711 and L.Installed_Flag in ('I', 'B')
712 AND NOT EXISTS (select 'Y'
713 FROM pa_rbs_element_names_tl tl
714 where tl.rbs_element_name_id = a.rbs_element_name_id
715 and tl.language = l.language_code);
716 END IF;--BOM_LABOR,BOM_EQUIPMENT
717
718 --MLS Changes.
719 IF p_resource_type_code = 'NON_LABOR_RESOURCE' THEN
720 --NON_LABOR_RESOURCE
721 INSERT INTO Pa_rbs_element_names_tl
722 (RBS_ELEMENT_NAME_ID,
723 RESOURCE_NAME,
724 Language,
725 Source_Lang,
726 Last_Update_Date,
727 Last_Updated_By,
728 Creation_Date,
729 Created_by,
730 Last_Update_Login)
731 SELECT
732 a.rbs_element_name_id,
733 c.non_labor_resource,
734 l.language_code,
735 USERENV('LANG'),
736 sysdate,
737 FND_GLOBAL.USER_ID,
738 sysdate,
739 FND_GLOBAL.USER_ID,
740 FND_GLOBAL.LOGIN_ID
741 FROM
742 pa_rbs_element_names_b a,
743 pa_rbs_elem_in_temp b,
744 pa_non_labor_resources c,
745 Fnd_Languages L
746 WHERE a.resource_source_id = b.resource_source_id
747 AND a.resource_type_id = p_resource_type_id
748 AND b.resource_type_id = p_resource_type_id
749 and c.non_labor_resource_id = a.resource_source_id
750 and L.Installed_Flag in ('I', 'B')
751 AND NOT EXISTS (select 'Y'
752 FROM pa_rbs_element_names_tl tl
753 where tl.rbs_element_name_id = a.rbs_element_name_id
754 and tl.language = l.language_code);
755 END IF;--NON_LABOR_RESOURCE
756
757 IF p_resource_type_code = 'RESOURCE_CLASS' THEN
758 --RESOURCE_CLASS
759 INSERT INTO Pa_rbs_element_names_tl
760 (RBS_ELEMENT_NAME_ID,
761 RESOURCE_NAME,
762 Language,
763 Source_Lang,
764 Last_Update_Date,
765 Last_Updated_By,
766 Creation_Date,
767 Created_by,
768 Last_Update_Login)
769 SELECT
770 a.rbs_element_name_id,
771 c.name,
772 c.language,
773 c.source_lang, --USERENV('LANG'),
774 sysdate,
775 FND_GLOBAL.USER_ID,
776 sysdate,
777 FND_GLOBAL.USER_ID,
778 FND_GLOBAL.LOGIN_ID
779 FROM
780 pa_rbs_element_names_b a,
781 pa_rbs_elem_in_temp b,
782 Pa_Resource_Classes_tl c
783 WHERE a.resource_source_id = b.resource_source_id
784 AND a.resource_type_id = p_resource_type_id
785 AND b.resource_type_id = p_resource_type_id
786 and c.resource_class_id = a.resource_source_id
787 and NOT EXISTS (select 'Y'
788 FROM pa_rbs_element_names_tl tl
789 where tl.rbs_element_name_id = a.rbs_element_name_id
790 and tl.language = c.language);
791 END IF;--RESOURCE_CLASS
792
793 --MLS Changes.
794 IF p_resource_type_code IN ('NAMED_ROLE','USER_DEFINED')
795 THEN
796 --NAMED_ROLE, USER_DEFINED
797 INSERT INTO Pa_rbs_element_names_tl
798 (RBS_ELEMENT_NAME_ID,
799 RESOURCE_NAME,
800 Language,
801 Source_Lang,
802 Last_Update_Date,
803 Last_Updated_By,
804 Creation_Date,
805 Created_by,
806 Last_Update_Login)
807 SELECT
808 a.rbs_element_name_id,
809 c.resource_name,
810 l.language_code,
811 USERENV('LANG'),
812 sysdate,
813 FND_GLOBAL.USER_ID,
814 sysdate,
815 FND_GLOBAL.USER_ID,
816 FND_GLOBAL.LOGIN_ID
817 FROM
818 pa_rbs_element_names_b a,
819 pa_rbs_elem_in_temp b,
820 Pa_rbs_element_map c,
821 Fnd_Languages L
822 WHERE a.resource_source_id = b.resource_source_id
823 AND a.resource_type_id = p_resource_type_id
824 AND b.resource_type_id = p_resource_type_id
825 and c.resource_id = a.resource_source_id
826 and c.resource_type_id = p_resource_type_id
827 and L.Installed_Flag in ('I', 'B')
828 AND NOT EXISTS (select 'Y'
829 FROM pa_rbs_element_names_tl tl
830 where tl.rbs_element_name_id = a.rbs_element_name_id
831 and tl.language = l.language_code);
832 END IF;--NAMED_ROLE, USER_DEFINED
833
834 IF p_resource_type_code IN ('REVENUE_CATEGORY')
835 THEN
836 --REVENUE_CATEGORY
837 INSERT INTO Pa_rbs_element_names_tl
838 (RBS_ELEMENT_NAME_ID,
839 RESOURCE_NAME,
840 Language,
841 Source_Lang,
842 Last_Update_Date,
843 Last_Updated_By,
844 Creation_Date,
845 Created_by,
846 Last_Update_Login)
847 SELECT
848 a.rbs_element_name_id,
849 lk.meaning, --c.resource_name,
850 lk.language,
851 lk.source_lang,
852 sysdate,
853 FND_GLOBAL.USER_ID,
854 sysdate,
855 FND_GLOBAL.USER_ID,
856 FND_GLOBAL.LOGIN_ID
857 FROM
858 pa_rbs_element_names_b a,
859 pa_rbs_elem_in_temp b,
860 Pa_rbs_element_map c,
861 fnd_lookup_values lk
862 WHERE a.resource_source_id = b.resource_source_id
863 AND a.resource_type_id = p_resource_type_id
864 AND b.resource_type_id = p_resource_type_id
865 and c.resource_id = a.resource_source_id
866 and c.resource_type_id = p_resource_type_id
867 and lk.lookup_type = 'REVENUE CATEGORY'
868 and lk.lookup_code = c.resource_name
869 AND NOT EXISTS (select 'Y'
870 FROM pa_rbs_element_names_tl tl
871 where tl.rbs_element_name_id = a.rbs_element_name_id
872 and tl.language = lk.language);
873 END IF;--REVENUE_CATEGORY
874
875 --MLS Changes.
876 IF p_resource_type_code = 'EVENT_TYPE' THEN
877 --EVENT_TYPE
878 INSERT INTO Pa_rbs_element_names_tl
879 (RBS_ELEMENT_NAME_ID,
880 RESOURCE_NAME,
881 Language,
882 Source_Lang,
883 Last_Update_Date,
884 Last_Updated_By,
885 Creation_Date,
886 Created_by,
887 Last_Update_Login)
888 SELECT
889 a.rbs_element_name_id,
890 c.event_type,
891 l.language_code,
892 USERENV('LANG'),
893 sysdate,
894 FND_GLOBAL.USER_ID,
895 sysdate,
896 FND_GLOBAL.USER_ID,
897 FND_GLOBAL.LOGIN_ID
898 FROM
899 pa_rbs_element_names_b a,
900 pa_rbs_elem_in_temp b,
901 pa_event_types c,
902 Fnd_Languages L
903 WHERE a.resource_source_id = b.resource_source_id
904 and c.event_type_id = a.resource_source_id
905 AND a.resource_type_id = p_resource_type_id
906 AND b.resource_type_id = p_resource_type_id
907 and L.Installed_Flag in ('I', 'B')
908 AND NOT EXISTS (select 'Y'
909 FROM pa_rbs_element_names_tl tl
910 where tl.rbs_element_name_id = a.rbs_element_name_id
911 and tl.language = l.language_code);
912 END IF;--EVENT_TYPE
913
914 --MLS Changes.
915 IF p_resource_type_code = 'EXPENDITURE_TYPE' THEN
916 --EXPENDITURE_TYPE
917 INSERT INTO Pa_rbs_element_names_tl
918 (RBS_ELEMENT_NAME_ID,
919 RESOURCE_NAME,
920 Language,
921 Source_Lang,
922 Last_Update_Date,
923 Last_Updated_By,
924 Creation_Date,
925 Created_by,
926 Last_Update_Login)
927 SELECT
928 a.rbs_element_name_id,
929 c.expenditure_type,
930 l.language_code,
931 USERENV('LANG'),
932 sysdate,
933 FND_GLOBAL.USER_ID,
934 sysdate,
935 FND_GLOBAL.USER_ID,
936 FND_GLOBAL.LOGIN_ID
937 FROM
938 pa_rbs_element_names_b a,
939 pa_rbs_elem_in_temp b,
940 pa_expenditure_types c,
941 Fnd_Languages L
942 WHERE a.resource_source_id = b.resource_source_id
943 and c.expenditure_type_id = a.resource_source_id
944 AND a.resource_type_id = p_resource_type_id
945 AND b.resource_type_id = p_resource_type_id
946 and L.Installed_Flag in ('I', 'B')
947 AND NOT EXISTS (select 'Y'
948 FROM pa_rbs_element_names_tl tl
949 where tl.rbs_element_name_id = a.rbs_element_name_id
950 and tl.language = l.language_code);
951 END IF;--EXPENDITURE_TYPE
952
953 --MLS Changes.
954 IF p_resource_type_code = 'EXPENDITURE_CATEGORY' THEN
955 --EXPENDITURE_CATEGORY
956 INSERT INTO Pa_rbs_element_names_tl
957 (RBS_ELEMENT_NAME_ID,
958 RESOURCE_NAME,
959 Language,
960 Source_Lang,
961 Last_Update_Date,
962 Last_Updated_By,
963 Creation_Date,
964 Created_by,
965 Last_Update_Login)
966 SELECT
967 a.rbs_element_name_id,
968 c.expenditure_category,
969 l.language_code,
970 USERENV('LANG'),
971 sysdate,
972 FND_GLOBAL.USER_ID,
973 sysdate,
974 FND_GLOBAL.USER_ID,
975 FND_GLOBAL.LOGIN_ID
976 FROM
977 pa_rbs_element_names_b a,
978 pa_rbs_elem_in_temp b,
979 pa_expenditure_categories c,
980 Fnd_Languages L
981 WHERE a.resource_source_id = b.resource_source_id
982 and c.expenditure_category_id = a.resource_source_id
983 AND a.resource_type_id = p_resource_type_id
984 AND b.resource_type_id = p_resource_type_id
985 and L.Installed_Flag in ('I', 'B')
986 AND NOT EXISTS (select 'Y'
987 FROM pa_rbs_element_names_tl tl
988 where tl.rbs_element_name_id = a.rbs_element_name_id
989 and tl.language = l.language_code);
990 END IF;--EXPENDITURE_CATEGORY
991
992 --MLS Changes.
993 IF p_resource_type_code = 'SUPPLIER' THEN
994 --SUPPLIER
995 INSERT INTO Pa_rbs_element_names_tl
996 (RBS_ELEMENT_NAME_ID,
997 RESOURCE_NAME,
998 Language,
999 Source_Lang,
1000 Last_Update_Date,
1001 Last_Updated_By,
1002 Creation_Date,
1003 Created_by,
1004 Last_Update_Login)
1005 SELECT
1006 a.rbs_element_name_id,
1007 c.vendor_name,
1008 l.language_code,
1009 USERENV('LANG'),
1010 sysdate,
1011 FND_GLOBAL.USER_ID,
1012 sysdate,
1013 FND_GLOBAL.USER_ID,
1014 FND_GLOBAL.LOGIN_ID
1015 FROM
1016 pa_rbs_element_names_b a,
1017 pa_rbs_elem_in_temp b,
1018 po_vendors c,
1019 Fnd_Languages L
1020 WHERE a.resource_source_id = b.resource_source_id
1021 and c.vendor_id = a.resource_source_id
1022 AND a.resource_type_id = p_resource_type_id
1023 AND b.resource_type_id = p_resource_type_id
1024 and L.Installed_Flag in ('I', 'B')
1025 AND NOT EXISTS (select 'Y'
1026 FROM pa_rbs_element_names_tl tl
1027 where tl.rbs_element_name_id = a.rbs_element_name_id
1028 and tl.language = l.language_code);
1029 END IF;--SUPPLIER
1030
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1034 RETURN;
1035 END Insert_non_tl_names;
1036
1037 /******************************************************
1038 * Procedure : Insert_tl_names
1039 * Description : This API is used to insert into the
1040 * pa_rbs_element_names_tl table.
1041 * For those res_type_codes for which there
1042 * are corr TL tables
1043 * We are going to insert the value based
1044 * on the values inserted into the
1045 * pa_rbs_element_names_b table.
1046 * and do a join with the corr TL
1047 * tables for the res_type_codes.
1048 * **************************************************/
1049 PROCEDURE Insert_tl_names
1050 (p_resource_type_id IN Number,
1051 p_resource_type_code IN Varchar2,
1052 x_return_status OUT NOCOPY Varchar2)
1053 IS
1054 BEGIN
1055
1056 IF p_resource_type_code = 'ORGANIZATION' THEN
1057 --ORGANIZATION
1058 INSERT INTO Pa_rbs_element_names_tl
1059 (RBS_ELEMENT_NAME_ID,
1060 RESOURCE_NAME,
1061 Language,
1062 Source_Lang,
1063 Last_Update_Date,
1064 Last_Updated_By,
1065 Creation_Date,
1066 Created_by,
1067 Last_Update_Login)
1068 SELECT
1069 a.rbs_element_name_id,
1070 c.name,
1071 c.language,
1072 c.source_lang,
1073 sysdate,
1074 FND_GLOBAL.USER_ID,
1075 sysdate,
1076 FND_GLOBAL.USER_ID,
1077 FND_GLOBAL.LOGIN_ID
1078 FROM
1079 pa_rbs_element_names_b a,
1080 pa_rbs_elem_in_temp b,
1081 hr_all_organization_units_tl c
1082 WHERE a.resource_source_id = b.resource_source_id
1083 and c.organization_id = a.resource_source_id
1084 AND a.resource_type_id = p_resource_type_id
1085 AND b.resource_type_id = p_resource_type_id
1086 AND NOT EXISTS (select 'Y'
1087 from pa_rbs_element_names_tl tl
1088 where tl.rbs_element_name_id = a.rbs_element_name_id
1089 and tl.language = c.language);
1090 END IF;--ORGANIZATION
1091
1092 IF p_resource_type_code = 'ROLE' THEN
1093 --ROLE
1094 INSERT INTO Pa_rbs_element_names_tl
1095 (RBS_ELEMENT_NAME_ID,
1096 RESOURCE_NAME,
1097 Language,
1098 Source_Lang,
1099 Last_Update_Date,
1100 Last_Updated_By,
1101 Creation_Date,
1102 Created_by,
1103 Last_Update_Login)
1104 SELECT
1105 a.rbs_element_name_id,
1106 c.meaning,
1107 c.language,
1108 c.source_lang,
1109 sysdate,
1110 FND_GLOBAL.USER_ID,
1111 sysdate,
1112 FND_GLOBAL.USER_ID,
1113 FND_GLOBAL.LOGIN_ID
1114 FROM
1115 pa_rbs_element_names_b a,
1116 pa_rbs_elem_in_temp b,
1117 Pa_Project_Role_Types_tl c
1118 WHERE a.resource_source_id = b.resource_source_id
1119 and c.project_role_id = a.resource_source_id
1120 AND a.resource_type_id = p_resource_type_id
1121 AND b.resource_type_id = p_resource_type_id
1122 AND NOT EXISTS (select 'Y'
1123 from pa_rbs_element_names_tl tl
1124 where tl.rbs_element_name_id = a.rbs_element_name_id
1125 and tl.language = c.language);
1126 END IF;--ROLE
1127
1128 IF p_resource_type_code = 'ITEM_CATEGORY' THEN
1129 --ITEM_CATEGORY
1130 INSERT INTO Pa_rbs_element_names_tl
1131 (RBS_ELEMENT_NAME_ID,
1132 RESOURCE_NAME,
1133 Language,
1134 Source_Lang,
1135 Last_Update_Date,
1136 Last_Updated_By,
1137 Creation_Date,
1138 Created_by,
1139 Last_Update_Login)
1140 SELECT
1141 a.rbs_element_name_id,
1142 fnd_Flex_ext.GET_SEGS('INV', 'MCAT',
1143 c.structure_id, c.category_id),
1144 l.language_code,
1145 USERENV('LANG'),
1146 sysdate,
1147 FND_GLOBAL.USER_ID,
1148 sysdate,
1149 FND_GLOBAL.USER_ID,
1150 FND_GLOBAL.LOGIN_ID
1151 FROM
1152 pa_rbs_element_names_b a,
1153 pa_rbs_elem_in_temp b,
1154 Fnd_Languages L,
1155 Mtl_Categories_v c
1156 WHERE a.resource_source_id = b.resource_source_id
1157 and c.category_id = a.resource_source_id
1158 AND a.resource_type_id = p_resource_type_id
1159 AND b.resource_type_id = p_resource_type_id
1160 and L.Installed_Flag in ('I', 'B')
1161 AND NOT EXISTS (select 'Y'
1162 FROM pa_rbs_element_names_tl tl
1163 where tl.rbs_element_name_id = a.rbs_element_name_id
1164 and tl.language = l.language_code);
1165
1166 END IF;--ITEM_CATEGORY
1167
1168 IF p_resource_type_code = 'INVENTORY_ITEM' THEN
1169 --INVENTORY_ITEM
1170 INSERT INTO Pa_rbs_element_names_tl
1171 (RBS_ELEMENT_NAME_ID,
1172 RESOURCE_NAME,
1173 Language,
1174 Source_Lang,
1175 Last_Update_Date,
1176 Last_Updated_By,
1177 Creation_Date,
1178 Created_by,
1179 Last_Update_Login)
1180 SELECT
1181 a.rbs_element_name_id,
1182 --For bug 3602566.
1183 --c.description,
1184 d.segment1,
1185 c.language,
1186 c.source_lang,
1187 sysdate,
1188 FND_GLOBAL.USER_ID,
1189 sysdate,
1190 FND_GLOBAL.USER_ID,
1191 FND_GLOBAL.LOGIN_ID
1192 FROM
1193 pa_rbs_element_names_b a,
1194 pa_rbs_elem_in_temp b,
1195 Mtl_System_Items_tl c,
1196 Mtl_System_Items_b d --For bug 3602566
1197 WHERE a.resource_source_id = b.resource_source_id
1198 and c.Inventory_Item_Id = a.resource_source_id
1199 AND a.resource_type_id = p_resource_type_id
1200 AND b.resource_type_id = p_resource_type_id
1201 AND c.inventory_item_id = d.inventory_item_id --For bug 3602566
1202 AND c.organization_id = d.organization_id --For bug 3602566
1203 AND c.organization_id =
1204 (select item_master_id
1205 from pa_resource_classes_b cls,
1206 pa_plan_res_defaults def
1207 where def.resource_class_id = cls.resource_class_id and cls.resource_class_code = 'MATERIAL_ITEMS'
1208 and def.object_type = 'CLASS')
1209 AND NOT EXISTS (select 'Y'
1210 from pa_rbs_element_names_tl tl
1211 where tl.rbs_element_name_id = a.rbs_element_name_id
1212 and tl.language = c.language);
1213 END IF;--INVENTORY_ITEM
1214
1215 END Insert_tl_names;
1216
1217
1218 /*********************************************************
1219 * Procedure : Populate_RBS_Element_Name
1220 * Description : This API does the following:-
1221 * - It can be called in 2 ways
1222 * 1. passing 1 resource_source_id and resource_type_id
1223 * 2. Populating the pa_rbs_elem_in_temp with a bunch
1224 * of resource_source_id and resource_type_id and
1225 * call this api.
1226 * -> In the first case we will take in a
1227 * resource_source_id and resource_type_id
1228 * and derive the element_name associated.
1229 * And then insert into the pa_rbs_element_names_b
1230 * and pa_rbs_element_names_tl tables.
1231 * Pass back the element_name_id.
1232 * -> In the 2nd case
1233 * Reads the records from the temp table.
1234 * For each of them derives the element_name
1235 * Inserts into the pa_rbs_element_names_b
1236 * table.
1237 * Inserts into the pa_rbs_element_names_tl
1238 * table.
1239 * Delete the recs in the temp table.
1240 ********************************************************/
1241 PROCEDURE Populate_RBS_Element_Name
1242 (p_resource_source_id IN Number Default Null,
1243 p_resource_type_id IN Number Default Null,
1244 x_rbs_element_name_id OUT NOCOPY Number,
1245 x_return_status OUT NOCOPY Varchar2)
1246 IS
1247 /*********************************************
1248 * This cursor is used to get the res_type_id
1249 * and the corr res_type_codes for it from the
1250 * temp table. so that we can insert into the
1251 * table for each res_type_code.
1252 ********************************************/
1253 Cursor c_get_res_types
1254 IS
1255 SELECT distinct a.resource_type_id,
1256 decode(a.resource_type_id,-1,'VERSION',-2,'RULE',b.res_type_code)
1257 FROM pa_rbs_elem_in_temp a,pa_res_types_b b
1258 WHERE a.resource_type_id = b.res_type_id(+)
1259 ORDER by resource_type_id;
1260
1261 --Declaration of the local variables
1262 l_return_status Varchar2(30);
1263 l_element_name Varchar2(30);
1264 l_call_flag Varchar2(1);
1265 l_resource_source_id Varchar2(30);
1266 l_resource_type_id Number;
1267 l_res_type_code Varchar2(30);
1268 l_res_type Varchar2(30);
1269 l_count Number;
1270 l_chk_flag Varchar2(1) := 'Y';
1271
1272 l_existing_count Number;
1273 l_element_name_id Number;
1274 l_temp_count Number;
1275 l_last_analyzed all_tables.last_analyzed%TYPE;
1276 l_pa_schema VARCHAR2(30);
1277 BEGIN
1278 --Initialize the x_return_status.
1279 x_return_status := FND_API.G_RET_STS_SUCCESS;
1280
1281 --For bug 4026456, 4887312
1282
1283 /*
1284 FND_STATS.SET_TABLE_STATS('PA',
1285 'PA_RBS_ELEM_IN_TEMP',
1286 100,
1287 10,
1288 100);
1289 */
1290 --End of bug 4026456, 4887312
1291
1292 -- Proper Fix for 4887312 *** RAMURTHY 03/01/06 02:33 pm ***
1293 -- It solves the issue above wrt commit by the FND_STATS.SET_TABLE_STATS call
1294 -- Bug 8261905, Replaced 'PA' by PJI_UTILS.GET_PA_SCHEMA_NAME
1295 PA_TASK_ASSIGNMENT_UTILS.set_table_stats(PJI_UTILS.GET_PA_SCHEMA_NAME,'PA_RBS_ELEM_IN_TEMP',100,10,100);
1296
1297 -- End fix 4887312
1298 /*******************************************************
1299 * Test to determine how this API is being called.
1300 * If a value is passed for the p_resource_source_id
1301 * and p_resource_type_id then set the call_flag = 'A'
1302 * else set the call_flag = 'B'.
1303 * In the case of Call_flag = 'A' the user needs to pass in
1304 * 1 value for p_resource_source_id and p_resource_type_id
1305 * and then call this API.
1306 * In the case of call_flag = 'B' the user should not pass in
1307 * these values and just populate the temp table.
1308 *******************************************************/
1309 IF p_resource_source_id IS NOT NULL and p_resource_type_id IS NOT NULL THEN
1310 l_call_flag := 'A';
1311 ELSE
1312 l_call_flag := 'B';
1313 END IF;
1314 /*******************/
1315 IF l_call_flag = 'A' THEN
1316 IF p_resource_source_id = -1 THEN
1317 l_resource_type_id := -2;
1318 ELSE
1319 l_resource_type_id := p_resource_type_id;
1320 END IF;
1321 /*******************************************
1322 * Insert into the pa_rbs_elem_in_temp table
1323 * explicitely.
1324 **********************************************/
1325 BEGIN
1326 Insert into pa_rbs_elem_in_temp
1327 (resource_source_id,
1328 resource_type_id)
1329 Values
1330 (p_resource_source_id,
1331 l_resource_type_id);
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 x_rbs_element_name_id := Null;
1335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1336 RETURN;
1337 END;
1338 END IF;
1339 /*************************************************
1340 * Added an extra check to check for duplicate rows
1341 * in the pa_rbs_elem_in_temp temporary table
1342 * and delete it, before proceeding.
1343 * Because if duplicate values are passed for
1344 * combination of resource_source_id and
1345 * resource_type_id, then we cannot anyway
1346 * insert multiple rows into the 2 main tables.
1347 **************************************************/
1348 BEGIN
1349 DELETE FROM pa_rbs_elem_in_temp a WHERE ROWID > (
1350 SELECT min(rowid) FROM pa_rbs_elem_in_temp b
1351 WHERE a.resource_source_id = b.resource_source_id
1352 AND a.resource_type_id = b.resource_type_id);
1353
1354 /* Also check to see that rows don't already exist
1355 * in element names for these resources - therefore,
1356 * delete rows from the temp table which already
1357 * have rows in pa_rbs_element_names_b */
1358
1359 DELETE FROM pa_rbs_elem_in_temp a
1360 WHERE EXISTS (SELECT 'Y'
1361 FROM pa_rbs_element_names_b b
1362 WHERE a.resource_source_id = b.resource_source_id
1363 AND a.resource_type_id = b.resource_type_id);
1364 END;
1365
1366 select count(*) into l_temp_count from pa_rbs_elem_in_temp;
1367 IF l_temp_count = 0 AND l_call_flag = 'A' THEN
1368 SELECT rbs_element_name_id
1369 INTO x_rbs_element_name_id
1370 FROM pa_rbs_element_names_b
1371 WHERE resource_source_id = p_resource_source_id
1372 AND resource_type_id = l_resource_type_id
1373 AND rownum = 1;
1374
1375 RETURN;
1376 END IF;
1377 /*****************************
1378 * Open the c_get_res_types cursor
1379 * which would get the res_type_id's
1380 * and corr res_type_code.
1381 *******************************/
1382 OPEN c_get_res_types;
1383 LOOP
1384 FETCH c_get_res_types INTO l_res_type,l_res_type_code;
1385 EXIT WHEN c_get_res_types%NOTFOUND;
1386 /*******************************************
1387 * Set a Savepoint so that in case something fails
1388 * we can roll back the insert into any prev tables.
1389 **************************************************/
1390 Savepoint insert_for_call_flag_AB;
1391 /***********************************************
1392 * Insert into the Pa_rbs_element_names_b
1393 * for the res_type passed. it will just do an
1394 * Insert as Select from the pa_rbs_elem_in_temp
1395 * table for the corr res_type.
1396 *****************************************************/
1397 Insert_elements
1398 (p_resource_type_id => l_res_type,
1399 x_return_status => l_return_status);
1400 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1401 Rollback to Savepoint insert_for_call_flag_AB;
1402 x_rbs_element_name_id := NULL;
1403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1404 RETURN;
1405 END IF;--Return Status
1406
1407 IF l_res_type_code NOT IN
1408 ('ORGANIZATION','ITEM_CATEGORY','INVENTORY_ITEM','ROLE')
1409 THEN
1410 Insert_non_tl_names
1411 (p_resource_type_id => l_res_type,
1412 p_resource_type_code => l_res_type_code,
1413 x_return_status => l_return_status);
1414 /**************************************************
1415 * Rollback changes and pass UNEXP error
1416 * and rbs_element_name_id as NULL and return.
1417 *************************************************/
1418 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1419 Rollback to Savepoint insert_for_call_flag_AB;
1420 x_rbs_element_name_id := NULL;
1421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1422 RETURN;
1423 END IF;--Return Status
1424 END IF;--Res_type_code
1425
1426 IF l_res_type_code IN
1427 ('ORGANIZATION','ITEM_CATEGORY','INVENTORY_ITEM','ROLE')
1428 THEN
1429 Insert_tl_names
1430 (p_resource_type_id => l_res_type,
1431 p_resource_type_code => l_res_type_code,
1432 x_return_status => l_return_status);
1433 /**************************************************
1434 * Rollback changes and pass UNEXP error
1435 * and rbs_element_name_id as NULL and return.
1436 *************************************************/
1437 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1438 Rollback to Savepoint insert_for_call_flag_AB;
1439 x_rbs_element_name_id := NULL;
1440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1441 RETURN;
1442 END IF;--Return Status
1443 END IF;--Res_type_code
1444
1445 END LOOP;
1446 CLOSE c_get_res_types;
1447
1448 DELETE FROM pa_rbs_elem_in_temp;
1449
1450
1451 IF l_call_flag = 'A' THEN
1452 BEGIN
1453 SELECT rbs_element_name_id
1454 INTO x_rbs_element_name_id
1455 FROM pa_rbs_element_names_b
1456 WHERE resource_source_id = p_resource_source_id
1457 AND resource_type_id = l_resource_type_id;
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460 x_rbs_element_name_id := NULL;
1461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1462 RETURN;
1463 END;
1464 END IF;
1465
1466 END Populate_RBS_Element_Name;
1467
1468 /* ----------------------------------------------------------------
1469 Wrapper API for handling RBS version changes. This API is called
1470 by the RBS summarization program. This API includes calls to all
1471 API's that handle RBS version changes in other PA modules. This
1472 API is called in the beginning of PJI concurrent program that
1473 handles RBS version changes
1474 ----------------------------------------------------------------*/
1475 PROCEDURE PROCESS_RBS_CHANGES (
1476 p_rbs_header_id IN NUMBER,
1477 p_new_rbs_version_id IN NUMBER,
1478 p_old_rbs_version_id IN NUMBER,
1479 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1480 x_msg_count OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1481 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1482 IS
1483
1484 l_worker_id NUMBER;
1485
1486 BEGIN
1487
1488 --Initialize return status
1489 x_return_status := FND_API.G_RET_STS_SUCCESS;
1490
1491 --Call to RBS handler API
1492 PA_RBS_VERSIONS_PVT.SET_REPORTING_FLAG (
1493 p_rbs_version_id => p_new_rbs_version_id,
1494 x_return_status => x_return_status );
1495
1496 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1497 pa_debug.log_message ('Error in API PA_RBS_VERSIONS_PVT.SET_REPORTING_FLAG' || SQLERRM);
1498 --RETURN;
1499 END IF;
1500
1501 --Call to Allocations handler API
1502 PA_ALLOC_UTILS.ASSOCIATE_RBS_TO_ALLOC_RULE(
1503 p_rbs_header_id => p_rbs_header_id,
1504 p_rbs_version_id => p_new_rbs_version_id,
1505 x_return_status => x_return_status,
1506 x_error_code => x_msg_data );
1507
1508 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1509 pa_debug.log_message ('Error in API PA_ALLOC_UTILS.ASSOCIATE_RBS_TO_ALLOC_RULE' || SQLERRM);
1510 --RETURN;
1511 END IF;
1512
1513 --Call to Budgeting and Forecasting handler API
1514
1515 PA_RLMI_RBS_MAP_PUB.PUSH_RBS_VERSION (
1516 p_old_rbs_version_id => p_old_rbs_version_id,
1517 p_new_rbs_version_id => p_new_rbs_version_id,
1518 x_return_status => x_return_status,
1519 x_msg_count => x_msg_count,
1520 x_msg_data => x_msg_data );
1521
1522 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1523 pa_debug.log_message ('Error in API PA_RLMI_RBS_MAP_PUB.PUSH_RBS_VERSION' || SQLERRM);
1524 --RETURN;
1525 END IF;
1526
1527
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 x_return_status := FND_API.G_RET_STS_ERROR;
1531 x_msg_data := SQLERRM;
1532 END;
1533
1534
1535 -- History:
1536 --
1537 -- 07-APR-2004 sushma created
1538 /*==============================================================================
1539 This api is used to Refresh Resource names
1540 =============================================================================*/
1541
1542 -- Procedure : Refresh_Resource_Names
1543 -- Type : Public Procedure
1544 -- Purpose : This API will be used to refresh Resource names associated with RBS.
1545 -- : This API will be called from :
1546 -- : 1.Concurrent program: Refresh RBS Element Names
1547
1548 -- Note : This API will refresh Resource names(associated with all RBS) for each resource type present
1549 -- in pa_rbs_element_names_b table by
1550 -- making join with respective tables.
1551
1552 -- Assumptions :
1553
1554 -- Parameters : None
1555 --
1556
1557 PROCEDURE Refresh_Resource_Names(errbuf OUT NOCOPY VARCHAR2,
1558 retcode OUT NOCOPY VARCHAR2)
1559 IS
1560
1561 --This cursor selects all the resource types from pa_res_types_b table
1562 CURSOR Res_Types_c IS
1563 SELECT Res_type_id
1564 FROM pa_res_types_b;
1565
1566 l_res_type_id NUMBER;
1567 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
1568 BEGIN
1569
1570 --Initialize variables
1571 retcode := 0;
1572 errbuf := NULL;
1573
1574 PA_DEBUG.SET_PROCESS(x_process => 'PLSQL',
1575 x_debug_mode => PG_DEBUG);
1576
1577 PA_DEBUG.WRITE_FILE('LOG', TO_CHAR(SYSDATE,'HH:MI:SS')||
1578 ': PA_DEBUG_MODE: '||PG_DEBUG);
1579
1580 --Print report heading
1581 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,TO_CHAR(sysdate,'DD-MON-YYYY')||
1582 ' '||
1583 'PARRCSUB - Refresh RBS Element Names');
1584 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
1585
1586
1587 IF PG_DEBUG = 'Y' THEN
1588 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||
1589 'deleting all rows from Tl table');
1590 END IF;
1591
1592
1593 --Delete all rows from pa_rbs_element_names_tl table
1594
1595 DELETE FROM pa_rbs_element_names_tl;
1596
1597
1598 --Refreshing Rule based Rbs
1599 IF PG_DEBUG = 'Y' THEN
1600 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||
1601 'Refreshing Rule Based Rbs');
1602 END IF;
1603
1604 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1605 to remove the GSCC Warning File.Sql.33 */
1606 INSERT INTO pa_rbs_element_names_tl(
1607 RBS_ELEMENT_NAME_ID,
1608 RESOURCE_NAME,
1609 LANGUAGE,
1610 SOURCE_LANG,
1611 LAST_UPDATE_DATE,
1612 LAST_UPDATED_BY,
1613 CREATION_DATE,
1614 CREATED_BY,
1615 LAST_UPDATE_LOGIN
1616 )
1617 (
1618 SELECT
1619 a.rbs_element_name_id,
1620 lk.meaning,
1621 lk.language,
1622 lk.source_lang,
1623 sysdate,
1624 fnd_global.user_id,
1625 sysdate,
1626 fnd_global.user_id,
1627 fnd_global.login_id
1628 FROM --pa_lookups lk,
1629 fnd_lookup_values lk,
1630 pa_rbs_element_names_b a
1631 WHERE
1632 a.resource_type_id = -2
1633 and a.resource_source_id = -1
1634 AND lk.lookup_code = 'USER_DEFINED_RESOURCE'
1635 AND lk.lookup_type = 'RBS_RULE_RESOURCE');
1636
1637 --Refreshing the Version names
1638 IF PG_DEBUG = 'Y' THEN
1639 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||
1640 'Refreshing Version Names ');
1641 END IF;
1642
1643 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1644 to remove the GSCC Warning File.Sql.33 */
1645 INSERT INTO pa_rbs_element_names_tl(
1646 RBS_ELEMENT_NAME_ID,
1647 RESOURCE_NAME,
1648 LANGUAGE,
1649 SOURCE_LANG,
1650 LAST_UPDATE_DATE,
1651 LAST_UPDATED_BY,
1652 CREATION_DATE,
1653 CREATED_BY,
1654 LAST_UPDATE_LOGIN
1655 )
1656 (
1657 SELECT
1658 b.rbs_element_name_id,
1659 vertl.name,
1660 vertl.language,
1661 vertl.source_lang,
1662 sysdate,
1663 fnd_global.user_id,
1664 sysdate,
1665 fnd_global.user_id,
1666 fnd_global.login_id
1667 FROM pa_rbs_versions_tl vertl,
1668 pa_rbs_element_names_b b
1669 WHERE b.resource_type_id = -1
1670 AND b.resource_source_id = vertl.rbs_version_id);
1671
1672
1673 OPEN Res_Types_c;
1674
1675 --Loop through all resource types reproducing all resources
1676 --associated with all RBS
1677 --into pa_rbs_element_names_tl table.
1678
1679 LOOP
1680 FETCH Res_Types_c INTO l_res_type_id;
1681
1682 EXIT WHEN Res_Types_c%NOTFOUND;
1683
1684 --FOR res_type_id=1 Res_type_code=BOM_LABOR
1685 IF l_res_type_id=1 THEN
1686 --dbms_output.put_line('For Res_Type_Id=1 BOM_LABOR');
1687 IF PG_DEBUG = 'Y' THEN
1688 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
1689 ': '||'Refreshing BOM_LABOR ');
1690 END IF;
1691 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1692 to remove the GSCC Warning File.Sql.33 */
1693 --MLS Changes
1694 INSERT INTO pa_rbs_element_names_tl(
1695 RBS_ELEMENT_NAME_ID,
1696 RESOURCE_NAME,
1697 LANGUAGE,
1698 SOURCE_LANG,
1699 LAST_UPDATE_DATE,
1700 LAST_UPDATED_BY,
1701 CREATION_DATE,
1702 CREATED_BY,
1703 LAST_UPDATE_LOGIN
1704 )
1705 (
1706 SELECT
1707 a.rbs_element_name_id,
1708 b.resource_code,
1709 --b.description,--For bug 3602566
1710 l.language_code,
1711 USERENV('LANG'),
1712 sysdate,
1713 fnd_global.user_id,
1714 sysdate,
1715 fnd_global.user_id,
1716 fnd_global.login_id
1717 FROM bom_resources b,
1718 pa_rbs_element_names_b a,
1719 Fnd_Languages L
1720 WHERE a.resource_type_id = 1
1721 AND b.resource_id = a.resource_source_id
1722 AND l.Installed_Flag in ('I', 'B'));
1723 END IF;
1724
1725
1726 IF l_res_type_id=2 THEN
1727
1728 IF PG_DEBUG = 'Y' THEN
1729 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
1730 ': '||'Refreshing BOM_EQUIPMENT ');
1731 END IF;
1732 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1733 to remove the GSCC Warning File.Sql.33 */
1734 --MLS Changes
1735 INSERT INTO pa_rbs_element_names_tl(
1736 RBS_ELEMENT_NAME_ID,
1737 RESOURCE_NAME,
1738 LANGUAGE,
1739 SOURCE_LANG,
1740 LAST_UPDATE_DATE,
1741 LAST_UPDATED_BY,
1742 CREATION_DATE,
1743 CREATED_BY,
1744 LAST_UPDATE_LOGIN
1745 )
1746 (
1747 SELECT
1748 a.rbs_element_name_id,
1749 b.resource_code,
1750 --b.description,--For bug 3602566
1751 l.language_code,
1752 USERENV('LANG'),
1753 sysdate,
1754 fnd_global.user_id,
1755 sysdate,
1756 fnd_global.user_id,
1757 fnd_global.login_id
1758 FROM bom_resources b,
1759 pa_rbs_element_names_b a,
1760 Fnd_Languages L
1761 WHERE a.resource_type_id = 2
1762 AND b.resource_id = a.resource_source_id
1763 and L.Installed_Flag in ('I', 'B'));
1764 END IF;
1765
1766
1767 --FOR res_type_id=3 Res_type_code=NAMED_PERSON
1768 IF l_res_type_id=3 THEN
1769 --dbms_output.put_line('For Res_Type_Id=3 NAMED_PERSON');
1770 IF PG_DEBUG = 'Y' THEN
1771 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
1772 ': '||'Refreshing NAMED_PERSON ');
1773 END IF;
1774 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1775 to remove the GSCC Warning File.Sql.33 */
1776 --MLS Changes
1777 INSERT INTO pa_rbs_element_names_tl(
1778 RBS_ELEMENT_NAME_ID,
1779 RESOURCE_NAME,
1780 LANGUAGE,
1781 SOURCE_LANG,
1782 LAST_UPDATE_DATE,
1783 LAST_UPDATED_BY,
1784 CREATION_DATE,
1785 CREATED_BY,
1786 LAST_UPDATE_LOGIN
1787 )
1788 (
1789 SELECT
1790 b.rbs_element_name_id,
1791 per.full_name,
1792 l.language_code,
1793 USERENV('LANG'),
1794 sysdate,
1795 fnd_global.user_id,
1796 sysdate,
1797 fnd_global.user_id,
1798 fnd_global.login_id
1799 FROM per_all_people_f per,
1800 pa_rbs_element_names_b b,
1801 Fnd_Languages L
1802 WHERE TRUNC(sysdate) BETWEEN
1803 effective_start_date AND
1804 NVL(effective_end_date,TRUNC(sysdate))
1805 AND b.resource_type_id=3
1806 AND per.person_id=b.resource_source_id
1807 and L.Installed_Flag in ('I', 'B'));
1808 END IF;
1809
1810
1811 --FOR res_type_id=4 Res_type_code=EVENT_TYPE
1812 IF l_res_type_id=4 THEN
1813 --dbms_output.put_line('For Res_Type_Id=4 EVENT_TYPE');
1814 IF PG_DEBUG = 'Y' THEN
1815 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
1816 ': '||'Refreshing EVENT_TYPE ');
1817 END IF;
1818 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1819 to remove the GSCC Warning File.Sql.33 */
1820 --MLS Changes
1821 INSERT INTO pa_rbs_element_names_tl(
1822 RBS_ELEMENT_NAME_ID,
1823 RESOURCE_NAME,
1824 LANGUAGE,
1825 SOURCE_LANG,
1826 LAST_UPDATE_DATE,
1827 LAST_UPDATED_BY,
1828 CREATION_DATE,
1829 CREATED_BY,
1830 LAST_UPDATE_LOGIN
1831 )
1832 (
1833 SELECT
1834 a.rbs_element_name_id,
1835 E.EVENT_TYPE,
1836 l.language_code,
1837 USERENV('LANG'),
1838 sysdate,
1839 fnd_global.user_id,
1840 sysdate,
1841 fnd_global.user_id,
1842 fnd_global.login_id
1843 FROM PA_EVENT_TYPES E,
1844 pa_rbs_element_names_b a,
1845 Fnd_Languages L
1846 WHERE a.resource_type_id=4
1847 AND E.event_type_id=a.resource_source_id
1848 AND L.Installed_Flag in ('I', 'B'));
1849 END IF;
1850
1851 --FOR res_type_id=5 Res_type_code=EXPENDITURE_CATEGORY
1852
1853 IF l_res_type_id=5 THEN
1854 --dbms_output.put_line('For Res_Type_Id=5 EXPENDITURE_CATEGORY');
1855
1856 IF PG_DEBUG = 'Y' THEN
1857 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
1858 ': '||'Refreshing EXPENDITURE_CATEGORY ');
1859 END IF;
1860 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1861 to remove the GSCC Warning File.Sql.33 */
1862 --MLS Changes
1863 INSERT INTO pa_rbs_element_names_tl(
1864 RBS_ELEMENT_NAME_ID,
1865 RESOURCE_NAME,
1866 LANGUAGE,
1867 SOURCE_LANG,
1868 LAST_UPDATE_DATE,
1869 LAST_UPDATED_BY,
1870 CREATION_DATE,
1871 CREATED_BY,
1872 LAST_UPDATE_LOGIN
1873 )
1874 (
1875 SELECT
1876 a.rbs_element_name_id,
1877 ec.EXPENDITURE_CATEGORY,
1878 l.language_code,
1879 USERENV('LANG'),
1880 sysdate,
1881 fnd_global.user_id,
1882 sysdate,
1883 fnd_global.user_id,
1884 fnd_global.login_id
1885 FROM pa_expenditure_categories ec,
1886 pa_rbs_element_names_b a,
1887 Fnd_Languages L
1888 WHERE a.resource_type_id = 5
1889 AND ec.EXPENDITURE_CATEGORY_ID =
1890 a.resource_source_id
1891 AND L.Installed_Flag in ('I', 'B'));
1892 END IF;
1893
1894 --FOR res_type_id=6 Res_type_code=EXPENDITURE_TYPE
1895 IF l_res_type_id=6 THEN
1896 --dbms_output.put_line('For Res_Type_Id=6 EXPENDITURE_TYPE');
1897 IF PG_DEBUG = 'Y' THEN
1898 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||': '||'Refreshing EXPENDITURE_TYPE ');
1899 END IF;
1900
1901 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1902 to remove the GSCC Warning File.Sql.33 */
1903 --MLS Changes
1904 INSERT INTO pa_rbs_element_names_tl(
1905 RBS_ELEMENT_NAME_ID,
1906 RESOURCE_NAME,
1907 LANGUAGE,
1908 SOURCE_LANG,
1909 LAST_UPDATE_DATE,
1910 LAST_UPDATED_BY,
1911 CREATION_DATE,
1912 CREATED_BY,
1913 LAST_UPDATE_LOGIN
1914 )
1915 (
1916 SELECT
1917 a.rbs_element_name_id,
1918 ec.expenditure_type,
1919 l.language_code,
1920 USERENV('LANG'),
1921 sysdate,
1922 fnd_global.user_id,
1923 sysdate,
1924 fnd_global.user_id,
1925 fnd_global.login_id
1926 FROM pa_expenditure_types ec,
1927 pa_rbs_element_names_b a,
1928 Fnd_Languages L
1929 WHERE a.resource_type_id = 6
1930 AND ec.EXPENDITURE_TYPE_ID =
1931 a.resource_source_id
1932 AND L.Installed_Flag in ('I', 'B'));
1933 END IF;
1934
1935 --FOR res_type_id=7 Res_type_code=ITEM_CATEGORY
1936 IF l_res_type_id=7 THEN
1937 --dbms_output.put_line('For Res_Type_Id=7 =ITEM_CATEGORY');
1938 IF PG_DEBUG = 'Y' THEN
1939 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
1940 ': '||'Refreshing ITEM_CATEGORY ');
1941 END IF;
1942
1943 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1944 to remove the GSCC Warning File.Sql.33 */
1945 INSERT INTO pa_rbs_element_names_tl(
1946 RBS_ELEMENT_NAME_ID,
1947 RESOURCE_NAME,
1948 LANGUAGE,
1949 SOURCE_LANG,
1950 LAST_UPDATE_DATE,
1951 LAST_UPDATED_BY,
1952 CREATION_DATE,
1953 CREATED_BY,
1954 LAST_UPDATE_LOGIN
1955 )
1956 (
1957 SELECT
1958 a.rbs_element_name_id,
1959 fnd_Flex_ext.GET_SEGS('INV', 'MCAT',
1960 c.structure_id, c.category_id),
1961 l.language_code,
1962 USERENV('LANG'),
1963 sysdate,
1964 fnd_global.user_id,
1965 sysdate,
1966 fnd_global.user_id,
1967 fnd_global.login_id
1968 FROM mtl_categories_v c,
1969 Fnd_Languages L,
1970 pa_rbs_element_names_b a
1971 WHERE a.resource_source_id=c.CATEGORY_ID
1972 AND a.resource_type_id=7
1973 AND L.Installed_Flag in ('I', 'B'));
1974 END IF;
1975
1976 --FOR res_type_id=8 Res_type_code=INVENTORY_ITEM
1977 IF l_res_type_id=8 THEN
1978 IF PG_DEBUG = 'Y' THEN
1979 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
1980 ': '||'Refreshing INVENTORY_ITEM ');
1981 END IF;
1982
1983 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
1984 to remove the GSCC Warning File.Sql.33 */
1985 INSERT INTO pa_rbs_element_names_tl(
1986 RBS_ELEMENT_NAME_ID,
1987 RESOURCE_NAME,
1988 LANGUAGE,
1989 SOURCE_LANG,
1990 LAST_UPDATE_DATE,
1991 LAST_UPDATED_BY,
1992 CREATION_DATE,
1993 CREATED_BY,
1994 LAST_UPDATE_LOGIN
1995 )
1996 (
1997 SELECT
1998 a.rbs_element_name_id,
1999 b.segment1, --For bug 3602566
2000 t.language,
2001 t.source_lang,
2002 sysdate,
2003 fnd_global.user_id,
2004 sysdate,
2005 fnd_global.user_id,
2006 fnd_global.login_id
2007 FROM MTL_SYSTEM_ITEMS_tl t,
2008 MTL_SYSTEM_ITEMS_b b,
2009 pa_plan_res_defaults p,
2010 pa_rbs_element_names_b a
2011 WHERE b.inventory_item_id=t.inventory_item_id
2012 AND b.organization_id=t.organization_id
2013 AND t.organization_id = p.item_master_id
2014 AND p.resource_class_id = 3
2015 AND a.resource_type_id=8
2016 AND a.resource_source_id=t.inventory_item_id);
2017 END IF;
2018
2019 --FOR res_type_id=9 Res_type_code=JOB
2020 IF l_res_type_id=9 THEN
2021 IF PG_DEBUG = 'Y' THEN
2022 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
2023 ': '||'Refreshing JOB ');
2024 END IF;
2025
2026 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
2027 to remove the GSCC Warning File.Sql.33 */
2028 --MLS Changes
2029 INSERT INTO pa_rbs_element_names_tl(
2030 RBS_ELEMENT_NAME_ID,
2031 RESOURCE_NAME,
2032 LANGUAGE,
2033 SOURCE_LANG,
2034 LAST_UPDATE_DATE,
2035 LAST_UPDATED_BY,
2036 CREATION_DATE,
2037 CREATED_BY,
2038 LAST_UPDATE_LOGIN
2039 )
2040 (
2041 SELECT
2042 a.rbs_element_name_id,
2043 job.name,
2044 l.language_code,
2045 USERENV('LANG'),
2046 sysdate,
2047 fnd_global.user_id,
2048 sysdate,
2049 fnd_global.user_id,
2050 fnd_global.login_id
2051 FROM per_jobs job,
2052 pa_rbs_element_names_b a,
2053 Fnd_Languages L
2054 WHERE a.resource_type_id=9
2055 AND a.resource_source_id=job.job_id
2056 AND L.Installed_Flag in ('I', 'B'));
2057
2058 END IF;
2059
2060 --FOR res_type_id=10 Res_type_code=ORGANIZATION
2061 IF l_res_type_id=10 THEN
2062 IF PG_DEBUG = 'Y' THEN
2063 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,
2064 'HH:MI:SS')|| ': '||'Refreshing ORGANIZATION ');
2065 END IF;
2066
2067 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
2068 to remove the GSCC Warning File.Sql.33 */
2069 INSERT INTO pa_rbs_element_names_tl(
2070 RBS_ELEMENT_NAME_ID,
2071 RESOURCE_NAME,
2072 LANGUAGE,
2073 SOURCE_LANG,
2074 LAST_UPDATE_DATE,
2075 LAST_UPDATED_BY,
2076 CREATION_DATE,
2077 CREATED_BY,
2078 LAST_UPDATE_LOGIN
2079 )
2080 (
2081 SELECT
2082 distinct a.rbs_element_name_id,
2083 tl.name,
2084 tl.language,
2085 tl.source_lang,
2086 sysdate,
2087 fnd_global.user_id,
2088 sysdate,
2089 fnd_global.user_id,
2090 fnd_global.login_id
2091 FROM hr_all_organization_units_tl tl,
2092 pa_rbs_element_names_b a,
2093 Fnd_Languages L
2094 WHERE tl.organization_id = a.resource_source_id
2095 AND a.resource_type_id = 10);
2096 END IF;
2097
2098 --FOR res_type_id=11 Res_type_code=PERSON_TYPE
2099 IF l_res_type_id=11 THEN
2100 IF PG_DEBUG = 'Y' THEN
2101 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
2102 ': '||'Refreshing PERSON_TYPE ');
2103 END IF;
2104
2105 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
2106 to remove the GSCC Warning File.Sql.33 */
2107 INSERT INTO pa_rbs_element_names_tl(
2108 RBS_ELEMENT_NAME_ID,
2109 RESOURCE_NAME,
2110 LANGUAGE,
2111 SOURCE_LANG,
2112 LAST_UPDATE_DATE,
2113 LAST_UPDATED_BY,
2114 CREATION_DATE,
2115 CREATED_BY,
2116 LAST_UPDATE_LOGIN
2117 )
2118 (
2119 SELECT a.rbs_element_name_id,
2120 lk.meaning,
2121 lk.language,
2122 lk.source_lang,
2123 sysdate,
2124 fnd_global.user_id,
2125 sysdate,
2126 fnd_global.user_id,
2127 fnd_global.login_id
2128 FROM pa_rbs_element_map map,-- For bug 3799582
2129 fnd_lookup_values lk,
2130 pa_rbs_element_names_b a
2131 WHERE map.resource_type_id = a.resource_type_id
2132 AND a.resource_source_id=map.resource_id
2133 AND a.resource_type_id=11
2134 AND lk.lookup_type = 'PA_PERSON_TYPE'
2135 AND lk.lookup_code = map.resource_name);
2136
2137 END IF;
2138
2139 --FOR res_type_id=12 Res_type_code=NON_LABOR_RESOURCE
2140 IF l_res_type_id=12 THEN
2141 IF PG_DEBUG = 'Y' THEN
2142 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,
2143 'HH:MI:SS')||': '||'Refreshing NON_LABOR_RESOURCE ');
2144 END IF;
2145
2146 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
2147 to remove the GSCC Warning File.Sql.33 */
2148 --MLS Changes
2149 INSERT INTO pa_rbs_element_names_tl(
2150 RBS_ELEMENT_NAME_ID,
2151 RESOURCE_NAME,
2152 LANGUAGE,
2153 SOURCE_LANG,
2154 LAST_UPDATE_DATE,
2155 LAST_UPDATED_BY,
2156 CREATION_DATE,
2157 CREATED_BY,
2158 LAST_UPDATE_LOGIN
2159 )
2160 (
2161 SELECT
2162 a.rbs_element_name_id,
2163 r.NON_LABOR_RESOURCE,
2164 l.language_code,
2165 USERENV('LANG'),
2166 sysdate,
2167 fnd_global.user_id,
2168 sysdate,
2169 fnd_global.user_id,
2170 fnd_global.login_id
2171 FROM pa_non_labor_resources r,
2172 pa_rbs_element_names_b a,
2173 Fnd_Languages L
2174 WHERE a.resource_source_id =
2175 r.non_labor_resource_id
2176 AND a.resource_type_id = 12
2177 AND L.Installed_Flag in ('I', 'B'));
2178 END IF;
2179
2180 --FOR res_type_id=13 Res_type_code=RESOURCE_CLASS
2181 IF l_res_type_id=13 THEN
2182 IF PG_DEBUG = 'Y' THEN
2183 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
2184 ': '||'Refreshing RESOURCE_CLASS');
2185 END IF;
2186
2187 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
2188 to remove the GSCC Warning File.Sql.33 */
2189 INSERT INTO pa_rbs_element_names_tl(
2190 RBS_ELEMENT_NAME_ID,
2191 RESOURCE_NAME,
2192 LANGUAGE,
2193 SOURCE_LANG,
2194 LAST_UPDATE_DATE,
2195 LAST_UPDATED_BY,
2196 CREATION_DATE,
2197 CREATED_BY,
2198 LAST_UPDATE_LOGIN
2199 )
2200 (
2201 SELECT
2202 a.rbs_element_name_id,
2203 tl.name,
2204 tl.language,
2205 tl.source_lang,
2206 sysdate,
2207 fnd_global.user_id,
2208 sysdate,
2209 fnd_global.user_id,
2210 fnd_global.login_id
2211 FROM pa_resource_classes_tl tl,
2212 pa_rbs_element_names_b a
2213 WHERE a.resource_source_id=tl.resource_class_id
2214 AND a.resource_type_id=13);
2215 END IF;
2216
2217 --FOR res_type_id=14 Res_type_code=REVENUE_CATEGORY
2218 IF l_res_type_id=14 THEN
2219 IF PG_DEBUG = 'Y' THEN
2220 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
2221 ': '||'Refreshing REVENUE_CATEGORY');
2222 END IF;
2223
2224 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
2225 to remove the GSCC Warning File.Sql.33 */
2226 INSERT INTO pa_rbs_element_names_tl(
2227 RBS_ELEMENT_NAME_ID,
2228 RESOURCE_NAME,
2229 LANGUAGE,
2230 SOURCE_LANG,
2231 LAST_UPDATE_DATE,
2232 LAST_UPDATED_BY,
2233 CREATION_DATE,
2234 CREATED_BY,
2235 LAST_UPDATE_LOGIN
2236 )
2237 (
2238 SELECT
2239 a.rbs_element_name_id,
2240 lk.meaning,
2241 lk.language,
2242 lk.source_lang,
2243 sysdate,
2244 fnd_global.user_id,
2245 sysdate,
2246 fnd_global.user_id,
2247 fnd_global.login_id
2248 FROM fnd_lookup_values lk,
2249 pa_rbs_element_names_b a,
2250 pa_rbs_element_map map
2251 WHERE a.resource_source_id=map.resource_id
2252 AND map.resource_name=lk.lookup_code
2253 AND lk.Lookup_Type = 'REVENUE CATEGORY'
2254 AND a.resource_type_id=14);
2255 END IF;
2256
2257 --FOR res_type_id=15 Res_type_code=ROLE
2258 IF l_res_type_id=15 THEN
2259 IF PG_DEBUG = 'Y' THEN
2260 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
2261 ': '||'Refreshing ROLE');
2262 END IF;
2263
2264 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
2265 to remove the GSCC Warning File.Sql.33 */
2266 INSERT INTO pa_rbs_element_names_tl(
2267 RBS_ELEMENT_NAME_ID,
2268 RESOURCE_NAME,
2269 LANGUAGE,
2270 SOURCE_LANG,
2271 LAST_UPDATE_DATE,
2272 LAST_UPDATED_BY,
2273 CREATION_DATE,
2274 CREATED_BY,
2275 LAST_UPDATE_LOGIN
2276 )
2277 (
2278 SELECT
2279 a.rbs_element_name_id,
2280 tl.meaning,
2281 tl.language,
2282 tl.source_lang,
2283 sysdate,
2284 fnd_global.user_id,
2285 sysdate,
2286 fnd_global.user_id,
2287 fnd_global.login_id
2288 FROM pa_project_role_types_tl tl,
2289 pa_rbs_element_names_b a
2290 WHERE a.resource_type_id = 15
2291 AND a.resource_source_id = tl.project_role_id);
2292 END IF;
2293
2294 --FOR res_type_id=16 Res_type_code=SUPPLIER
2295 IF l_res_type_id=16 THEN
2296 IF PG_DEBUG = 'Y' THEN
2297 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
2298 ': '||'Refreshing SUPPLIER');
2299 END IF;
2300
2301 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
2302 to remove the GSCC Warning File.Sql.33 */
2303 --MLS Changes
2304 INSERT INTO pa_rbs_element_names_tl(
2305 RBS_ELEMENT_NAME_ID,
2306 RESOURCE_NAME,
2307 LANGUAGE,
2308 SOURCE_LANG,
2309 LAST_UPDATE_DATE,
2310 LAST_UPDATED_BY,
2311 CREATION_DATE,
2312 CREATED_BY,
2313 LAST_UPDATE_LOGIN
2314 )
2315 (
2316 SELECT
2317 a.rbs_element_name_id,
2318 v.vendor_name,
2319 l.language_code,
2320 USERENV('LANG'),
2321 sysdate,
2322 fnd_global.user_id,
2323 sysdate,
2324 fnd_global.user_id,
2325 fnd_global.login_id
2326 FROM po_vendors v,
2327 pa_rbs_element_names_b a,
2328 Fnd_Languages L
2329 WHERE a.resource_type_id = 16
2330 AND a.resource_source_id = v.vendor_id
2331 AND L.Installed_Flag in ('I', 'B'));
2332 END IF;
2333
2334
2335 --FOR res_type_id=18 Res_type_code=USER DEFINED
2336 IF l_res_type_id=18 THEN
2337 IF PG_DEBUG = 'Y' THEN
2338 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||
2339 ': '||'Refreshing USER DEFINED');
2340 END IF;
2341
2342 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
2343 to remove the GSCC Warning File.Sql.33 */
2344 --MLS
2345 INSERT INTO pa_rbs_element_names_tl(
2346 RBS_ELEMENT_NAME_ID,
2347 RESOURCE_NAME,
2348 LANGUAGE,
2349 SOURCE_LANG,
2350 LAST_UPDATE_DATE,
2351 LAST_UPDATED_BY,
2352 CREATION_DATE,
2353 CREATED_BY,
2354 LAST_UPDATE_LOGIN
2355 )
2356 (
2357 SELECT
2358 a.rbs_element_name_id,
2359 map.resource_name,
2360 l.language_code,
2361 USERENV('LANG'),
2362 sysdate,
2363 fnd_global.user_id,
2364 sysdate,
2365 fnd_global.user_id,
2366 fnd_global.login_id
2367 FROM pa_rbs_element_map map,
2368 pa_rbs_element_names_b a,
2369 Fnd_Languages L
2370 WHERE a.resource_type_id = 18
2371 AND a.resource_source_id = map.resource_id
2372 AND L.Installed_Flag in ('I', 'B'));
2373 END IF;
2374
2375
2376 END LOOP;
2377
2378 CLOSE Res_Types_c;
2379
2380 --dbms_output.put_line('Leaving Refresh_Resource_Names procedure');
2381
2382 IF PG_DEBUG = 'Y' THEN
2383 PA_DEBUG.WRITE_FILE('LOG',TO_CHAR(SYSDATE,'HH:MI:SS')||'Leaving Refresh_Resource_Names procedure');
2384 END IF;
2385 Commit;
2386
2387 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Refresh_Resource_Names completed successfully.');
2388 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
2389
2390 EXCEPTION
2391
2392 WHEN OTHERS THEN
2393 Rollback;
2394 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Unexpected error: '||SQLCODE||' '||SQLERRM);
2395 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
2396
2397 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unexpected error: '||SQLCODE||' '||SQLERRM);
2398 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_global.local_chr(10));
2399
2400 retcode := SQLCODE;
2401 errbuf := SQLERRM;
2402 RAISE;
2403
2404 END Refresh_Resource_Names;
2405
2406 /*****************************************************
2407 * Function : Get_Concatenated_Name
2408 * Description : This Function is used to return the
2409 * Concatenated Name given a rbs_element_id.
2410 ****************************************************/
2411 Function Get_Concatenated_name
2412 (p_rbs_element_id IN Number)
2413 RETURN Varchar2
2414 IS
2415 /******************************************************
2416 * The Below cursor selects the resource_names for
2417 * the rbs_element_id passed in, using a connect by clause.
2418 * It starts with the deepest node first, and then traverses up.
2419 *********************************************************/
2420 Cursor c_element_name
2421 IS
2422 SELECT tl.resource_name
2423 FROM pa_rbs_elements ele, pa_rbs_element_names_vl tl
2424 WHERE ele.RBS_ELEMENT_NAME_ID = tl.RBS_ELEMENT_NAME_ID
2425 CONNECT BY PRIOR ele.parent_element_id = ele.rbs_element_id
2426 START WITH ele.rbs_element_id = p_rbs_element_id
2427 ORDER BY rbs_level DESC;
2428
2429 l_element_name Varchar2(240);
2430 l_concat_name Varchar2(10000);
2431 l_count Number;
2432 BEGIN
2433 /************************************************
2434 * If the p_rbs_element_id is not passed in or null
2435 * passed in just return Null.
2436 ***************************************************/
2437 IF p_rbs_element_id IS NULL THEN
2438 Return Null;
2439 END IF;
2440 OPEN c_element_name;
2441 LOOP
2442 FETCH c_element_name INTO l_element_name;
2443 EXIT WHEN c_element_name%NOTFOUND;
2444 l_count := c_element_name%ROWCOUNT;
2445 /*********************************************
2446 * If Count is 1 just assing the l_element_name to the
2447 * l_concat_name.
2448 ***************************************************/
2449 IF l_count = 1 THEN
2450 l_concat_name := l_element_name;
2451 ELSE
2452 /*********************************************
2453 * If Count > 1 just assing the l_element_name to the
2454 * l_concat_name.
2455 ***************************************************/
2456 l_concat_name := l_concat_name ||'.'||l_element_name;
2457 END IF;
2458 END LOOP;
2459 CLOSE c_element_name;
2460 --Pass back the Concatenated Name.
2461 Return l_concat_name;
2462 EXCEPTION
2463 WHEN OTHERS THEN
2464 --If any exception encountered pass back Null.
2465 Return Null;
2466 END Get_Concatenated_name;
2467
2468 /* ----------------------------------------------------------------
2469 * API for upgrading a resource list to an RBS. This API is called
2470 * by the resource list upgrade concurrent program.
2471 * ----------------------------------------------------------------*/
2472 PROCEDURE UPGRADE_LIST_TO_RBS (
2473 p_resource_list_id IN NUMBER,
2474 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2475 x_msg_count OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2476 x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
2477
2478 -- l_return_status varchar2(1);
2479 l_start_date date;
2480 l_end_date date;
2481
2482 CURSOR get_list_info (p_resource_list_id NUMBER) IS
2483 SELECT resource_list_id,
2484 start_date_active,
2485 end_date_active,
2486 business_group_id,
2487 job_group_id,
2488 nvl(uncategorized_flag, 'N') uncategorized_flag,
2489 decode(group_resource_type_id,103, 'ORGANIZATION',
2490 decode(group_resource_type_id, 108, 'EXPENDITURE_CATEGORY',
2491 decode(group_resource_type_id, 109, 'REVENUE_CATEGORY',
2492 'NONE'))) group_res_type
2493 FROM pa_resource_lists_all_bg
2494 WHERE resource_list_id = p_resource_list_id;
2495 -- AND nvl(uncategorized_flag, 'N') <> 'Y';
2496
2497 CURSOR get_lists_names (p_resource_list_id NUMBER) IS
2498 SELECT name,
2499 description,
2500 language,
2501 source_lang
2502 FROM pa_resource_lists_tl
2503 WHERE resource_list_id = p_resource_list_id;
2504
2505 CURSOR get_parents (p_resource_list_id NUMBER) IS
2506 SELECT resource_list_member_id,
2507 organization_id,
2508 expenditure_category,
2509 revenue_category
2510 FROM pa_resource_list_members
2511 WHERE resource_list_id = p_resource_list_id
2512 AND migration_code = 'M'
2513 AND resource_type_id not in (110, 107)
2514 AND parent_member_id is null;
2515
2516 CURSOR get_non_parents (p_resource_list_id NUMBER) IS
2517 SELECT child.resource_list_member_id,
2518 child.parent_member_id,
2519 child.resource_type_id,
2520 child.organization_id,
2521 child.person_id,
2522 child.job_id,
2523 child.vendor_id,
2524 child.project_role_id,
2525 child.event_type,
2526 child.expenditure_type,
2527 child.expenditure_category,
2528 child.revenue_category
2529 FROM pa_resource_list_members child
2530 WHERE child.resource_list_id = p_resource_list_id
2531 AND ((child.parent_member_id IS NOT NULL AND
2532 EXISTS (SELECT 'Y' FROM pa_resource_lists_all_bg
2533 WHERE resource_list_id = p_resource_list_id
2534 AND group_resource_type_id in (108, 109, 103)))
2535 OR (child.parent_member_id IS NULL AND
2536 EXISTS (SELECT 'Y' FROM pa_resource_lists_all_bg
2537 WHERE resource_list_id = p_resource_list_id
2538 AND group_resource_type_id not in (108, 109, 103))))
2539 AND migration_code = 'M'
2540 AND resource_type_id not in (110, 107)
2541 ORDER BY child.parent_member_id; -- Added for bug 3745326 so that outline numbers for child elements can be properly derived.
2542
2543 CURSOR get_res_type_id(p_resource_type_id NUMBER) IS
2544 SELECT res_type_id, res_type_code
2545 FROM pa_res_types_b
2546 WHERE res_type_code = decode(p_resource_type_id, 101, 'NAMED_PERSON',
2547 decode(p_resource_type_id, 102, 'JOB',
2548 decode(p_resource_type_id, 103, 'ORGANIZATION',
2549 decode(p_resource_type_id, 104, 'SUPPLIER',
2550 decode(p_resource_type_id, 111, 'ROLE',
2551 decode(p_resource_type_id, 105, 'EXPENDITURE_TYPE',
2552 decode(p_resource_type_id, 106, 'EVENT_TYPE',
2553 decode(p_resource_type_id, 108, 'EXPENDITURE_CATEGORY',
2554 decode(p_resource_type_id, 109, 'REVENUE_CATEGORY',
2555 NULL)))))))));
2556
2557 CURSOR get_rev_cat(p_resource_list_id NUMBER) IS
2558 SELECT rlm.revenue_category,
2559 typ.res_type_id -- resource_type_id
2560 FROM pa_resource_list_members rlm,
2561 (select res_type_id from pa_res_types_b
2562 where res_type_code = 'REVENUE_CATEGORY') typ
2563 WHERE rlm.resource_list_id = p_resource_list_id
2564 AND rlm.resource_type_id = 109;
2565
2566 CURSOR get_projects(p_resource_list_id NUMBER) IS
2567 SELECT asg.project_id
2568 FROM pa_resource_list_assignments asg,
2569 pa_resource_list_uses pru
2570 WHERE asg.resource_list_id = p_resource_list_id
2571 AND asg.resource_list_assignment_id = pru.resource_list_assignment_id
2572 AND pru.use_code = 'ACTUALS_ACCUM';
2573
2574 l_res_type_id NUMBER;
2575 l_project_id NUMBER;
2576 l_list get_list_info%ROWTYPE;
2577 l_revenue_category_id NUMBER;
2578 l_expenditure_type_id NUMBER;
2579 l_event_type_id NUMBER;
2580 l_expenditure_category_id NUMBER;
2581 l_rbs_header_id NUMBER;
2582 l_rbs_version_id NUMBER;
2583 l_rbs_element_id NUMBER;
2584 l_resource_source_id NUMBER;
2585 l_resource_id NUMBER;
2586 l_element_name_id NUMBER;
2587 l_parent_element_id NUMBER;
2588 l_old_parent_member_id NUMBER;
2589 l_rbs_identifier_id NUMBER;
2590 l_rbs_level NUMBER;
2591 l_top_node_id NUMBER;
2592 --l_status VARCHAR2(30);
2593 l_new_element_name_id NUMBER;
2594 l_Rbs_Version_From_Id NUMBER;
2595 l_rbs_dummy_id NUMBER;
2596 l_name_count NUMBER;
2597 l_res_type_code VARCHAR2(30);
2598
2599 l_num Number;
2600 l_count Number;
2601 l_done Varchar2(1);
2602 l_rbs_header_name VARCHAR2(240);
2603
2604 -- l_return_status VARCHAR2(30);
2605 -- l_msg_count NUMBER;
2606 -- l_msg_code VARCHAR2(2000);
2607 l_outline_number VARCHAR2(240) := '0';
2608 l_parent_outline_number VARCHAR2(240) := '0';
2609 l_child_outline_number VARCHAR2(240) := '1';
2610 l_last_analyzed all_tables.last_analyzed%TYPE;
2611 l_pa_schema VARCHAR2(30);
2612 BEGIN
2613
2614 --dbms_output.put_line('START UPGRADE');
2615 -- Upgrade Resource List
2616
2617 x_return_status := FND_API.G_RET_STS_SUCCESS;
2618 x_msg_data := NULL;
2619 x_msg_count := 0;
2620
2621 --For bug 4045542, 4887312
2622 /*
2623 FND_STATS.SET_TABLE_STATS('PA',
2624 'PA_RBS_ELEMENTS_TEMP',
2625 100,
2626 10,
2627 100);
2628 */
2629 --End of bug 4045542, 4887312
2630 -- Proper Fix for 4887312 *** RAMURTHY 03/01/06 02:33 pm ***
2631 -- It solves the issue above wrt commit by the FND_STATS.SET_TABLE_STATS call
2632 -- Bug 8261905, Replaced 'PA' by PJI_UTILS.GET_PA_SCHEMA_NAME
2633 PA_TASK_ASSIGNMENT_UTILS.set_table_stats(PJI_UTILS.GET_PA_SCHEMA_NAME,'PA_RBS_ELEMENTS_TEMP',100,10,100);
2634
2635 -- End fix 4887312
2636
2637
2638 OPEN get_list_info(p_resource_list_id);
2639 FETCH get_list_info into l_list;
2640 IF get_list_info%NOTFOUND THEN
2641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2642 CLOSE get_list_info;
2643 RETURN;
2644 END IF;
2645 CLOSE get_list_info;
2646
2647 --dbms_output.put_line('l_list.uncategorized_flag IS : ' || l_list.uncategorized_flag);
2648 IF l_list.uncategorized_flag = 'Y' THEN
2649 -- This is the none list - don't need to do anything.
2650 RETURN;
2651 END IF;
2652
2653 --dbms_output.put_line('Upgrading list : ' || l_list.resource_list_id);
2654 -- set a savepoint for the list so that the entire list
2655 -- can be rolled back if it errors.
2656 savepoint l_resource_list_savepoint;
2657
2658 SELECT PA_RBS_HEADERS_S.nextval
2659 INTO l_rbs_header_id from dual;
2660
2661 INSERT INTO PA_RBS_HEADERS_B
2662 (RBS_HEADER_ID,
2663 EFFECTIVE_FROM_DATE,
2664 EFFECTIVE_TO_DATE,
2665 BUSINESS_GROUP_ID,
2666 CREATION_DATE,
2667 CREATED_BY,
2668 LAST_UPDATE_DATE,
2669 LAST_UPDATED_BY,
2670 LAST_UPDATE_LOGIN,
2671 RECORD_VERSION_NUMBER,
2672 USE_FOR_ALLOC_FLAG)
2673 VALUES
2674 (l_rbs_header_id,
2675 l_list.start_date_active,
2676 l_list.end_date_active,
2677 l_list.business_group_id,
2678 sysdate,
2679 fnd_global.user_id,
2680 sysdate,
2681 fnd_global.user_id,
2682 fnd_global.login_id,
2683 1,
2684 'Y');
2685
2686 --dbms_output.put_line('Created RBS Header : ' || l_rbs_header_id);
2687
2688 -- Insert into the RBS Headers TL table from pa_resource_lists_tl.
2689 FOR l_names in get_lists_names(l_list.resource_list_id) LOOP -- RBS H TL Loop
2690
2691 --dbms_output.put_line('Creating RBS Header Names TL');
2692 --dbms_output.put_line('RBS Header Name is: ' || l_names.name);
2693
2694 -- Check name uniqueness - bug 3725985
2695 Select Count(*)
2696 Into l_Count
2697 From Pa_Rbs_Headers_tl
2698 Where Name = l_names.name
2699 And language = userenv('LANG');
2700
2701 IF l_Count <> 0 THEN
2702 l_num := 1;
2703 l_done := 'N';
2704 LOOP
2705 EXIT when l_done = 'Y';
2706 l_rbs_header_name := substr(l_names.name, 1, 235) || l_num;
2707 Select Count(*)
2708 Into l_Count
2709 From Pa_Rbs_Headers_tl
2710 Where Name = l_rbs_header_name
2711 And language = userenv('LANG');
2712
2713 IF l_Count = 0 THEN
2714 l_done := 'Y';
2715 END IF;
2716 l_num := l_num + 1;
2717 END LOOP;
2718 ELSE
2719 l_rbs_header_name := l_names.name;
2720 END IF;
2721
2722 INSERT INTO PA_RBS_HEADERS_TL(
2723 RBS_HEADER_ID,
2724 NAME,
2725 DESCRIPTION,
2726 LANGUAGE,
2727 SOURCE_LANG,
2728 CREATION_DATE,
2729 CREATED_BY,
2730 LAST_UPDATE_DATE,
2731 LAST_UPDATED_BY,
2732 LAST_UPDATE_LOGIN)
2733 VALUES(
2734 l_rbs_header_id,
2735 l_rbs_header_name,
2736 l_names.description,
2737 l_names.language,
2738 l_names.source_lang,
2739 sysdate,
2740 fnd_global.user_id,
2741 sysdate,
2742 fnd_global.user_id,
2743 fnd_global.login_id);
2744 END LOOP; -- End RBS H TL names Loop
2745 -- Done with RBS Headers
2746 --dbms_output.put_line('Done RBS Header');
2747
2748 -- Insert into RBS Versions - frozen and working
2749 --dbms_output.put_line('Creating RBS Versions');
2750 -- FOR i in 1 .. 2 LOOP -- RBS Versions Loop (Frozen and Working)
2751 -- Create Frozen version, then copy to working
2752
2753 l_outline_number := '0';
2754
2755 SELECT PA_RBS_VERSIONS_S.nextval
2756 INTO l_rbs_version_id from dual;
2757 l_Rbs_Version_From_Id := l_rbs_version_id;
2758
2759 INSERT INTO PA_RBS_VERSIONS_B (
2760 RBS_VERSION_ID,
2761 VERSION_NUMBER,
2762 RBS_HEADER_ID,
2763 VERSION_START_DATE,
2764 VERSION_END_DATE,
2765 JOB_GROUP_ID,
2766 RULE_BASED_FLAG,
2767 VALIDATED_FLAG,
2768 STATUS_CODE,
2769 LAST_UPDATE_DATE,
2770 LAST_UPDATED_BY,
2771 CREATION_DATE,
2772 CREATED_BY,
2773 LAST_UPDATE_LOGIN,
2774 RECORD_VERSION_NUMBER,
2775 current_reporting_flag )
2776 VALUES (
2777 l_rbs_version_id,
2778 1,
2779 l_rbs_header_id,
2780 l_list.start_date_active,
2781 NULL,
2782 l_list.job_group_id,
2783 'N',
2784 'Y',
2785 'FROZEN',
2786 sysdate,
2787 fnd_global.user_id,
2788 sysdate,
2789 fnd_global.user_id,
2790 fnd_global.login_id,
2791 1,
2792 'Y');
2793
2794 --dbms_output.put_line('Created RBS Version : ' || l_rbs_version_id);
2795
2796 FOR l_names in get_lists_names(l_list.resource_list_id) LOOP -- RBS V TL Loop
2797 --dbms_output.put_line('Creating RBS Version Names TL');
2798 --dbms_output.put_line('RBS Version Name is: ' || l_names.name);
2799 INSERT INTO PA_RBS_VERSIONS_TL(
2800 RBS_VERSION_ID,
2801 NAME,
2802 DESCRIPTION,
2803 LANGUAGE,
2804 SOURCE_LANG,
2805 CREATION_DATE,
2806 CREATED_BY,
2807 LAST_UPDATE_DATE,
2808 LAST_UPDATED_BY,
2809 LAST_UPDATE_LOGIN)
2810 VALUES(
2811 l_rbs_version_id,
2812 l_rbs_header_name,
2813 l_names.description,
2814 l_names.language,
2815 l_names.source_lang,
2816 sysdate,
2817 fnd_global.user_id,
2818 sysdate,
2819 fnd_global.user_id,
2820 fnd_global.login_id);
2821 END LOOP; -- End RBS V TL names Loop Version
2822
2823 -- Stamp frozen version ID on pa_resource_lists_all_bg
2824
2825 -- IF l_status = 'FROZEN' THEN
2826 UPDATE pa_resource_lists_all_bg
2827 SET migrated_rbs_version_id = l_rbs_version_id
2828 WHERE resource_list_id = l_list.resource_list_id;
2829 -- END IF;
2830
2831 -- Create Elements for the Version top nodes
2832 -- First populate element names for the top node.
2833
2834 INSERT INTO PA_RBS_ELEM_IN_TEMP
2835 (resource_source_id, resource_type_id)
2836 VALUES (l_rbs_version_id, -1);
2837
2838 -- Upgrade all the resource list members for this list -
2839 -- For each version
2840 -- First, Populate rbs element names for all elements.
2841 -- populate temp table and call API.
2842
2843 --dbms_output.put_line('Populate RBS Element Names');
2844 INSERT INTO PA_RBS_ELEM_IN_TEMP
2845 (resource_source_id,
2846 resource_type_id)
2847 SELECT decode(typ.res_type_code, 'ORGANIZATION', rlm.organization_id,
2848 decode(typ.res_type_code, 'NAMED_PERSON', rlm.person_id,
2849 decode(typ.res_type_code, 'JOB', rlm.job_id,
2850 decode(typ.res_type_code, 'SUPPLIER', rlm.vendor_id,
2851 decode(typ.res_type_code, 'ROLE', rlm.project_role_id,
2852 NULL))))), -- resource_source_id,
2853 typ.res_type_id -- resource_type_id
2854 FROM pa_resource_list_members rlm,
2855 (select res_type_id, res_type_code from pa_res_types_b) typ
2856 WHERE rlm.resource_list_id = l_list.resource_list_id
2857 AND ((rlm.resource_type_id = 103
2858 AND typ.res_type_code = 'ORGANIZATION') OR -- Org
2859 (rlm.resource_type_id = 101
2860 AND typ.res_type_code = 'NAMED_PERSON') OR -- Emp
2861 (rlm.resource_type_id = 102
2862 AND typ.res_type_code = 'JOB') OR -- Job
2863 (rlm.resource_type_id = 104
2864 AND typ.res_type_code = 'SUPPLIER') OR -- Vendor
2865 (rlm.resource_type_id = 111
2866 AND typ.res_type_code = 'ROLE') -- Role
2867 );
2868
2869 /*INSERT INTO PA_RBS_ELEM_IN_TEMP
2870 (resource_source_id,
2871 resource_type_id)
2872 SELECT source.resource_source_id,
2873 typ.res_type_id -- resource_type_id
2874 FROM pa_resource_list_members rlm,
2875 (select res_type_id, res_type_code from pa_res_types_b) typ,
2876 ((select expenditure_type_id resource_source_id,
2877 expenditure_type resource_name, 1 type_number
2878 from pa_expenditure_types)
2879 UNION
2880 (select expenditure_category_id resource_source_id,
2881 expenditure_category resource_name, 2 type_number
2882 from pa_expenditure_categories)
2883 UNION
2884 (select event_type_id resource_source_id,
2885 event_type resource_name, 3 type_number
2886 from pa_event_types)) source
2887 WHERE rlm.resource_list_id = l_list.resource_list_id
2888 AND ((rlm.resource_type_id = 105
2889 AND typ.res_type_code = 'EXPENDITURE_TYPE'
2890 AND source.resource_name = rlm.expenditure_type
2891 AND source.type_number = 1) OR--ExpType
2892 (rlm.resource_type_id = 106
2893 AND typ.res_type_code = 'EVENT_TYPE'
2894 AND source.resource_name = rlm.event_type
2895 AND source.type_number = 3) OR -- Event Type
2896 (rlm.resource_type_id = 108
2897 AND typ.res_type_code = 'EXPENDITURE_CATEGORY'
2898 AND source.resource_name = rlm.expenditure_category
2899 AND source.type_number = 2) --ECat
2900 );*/
2901 -- rewrite the above sql for perf bug 4887375
2902 INSERT INTO PA_RBS_ELEM_IN_TEMP
2903 (resource_source_id,
2904 resource_type_id)
2905 SELECT resource_source_id,
2906 res_type_id -- resource_type_id
2907 FROM
2908 (
2909 (SELECT source.resource_source_id,
2910 typ.res_type_id -- resource_type_id
2911 FROM
2912 pa_resource_list_members rlm,
2913 (select res_type_id, res_type_code from pa_res_types_b) typ,
2914 ((select expenditure_category_id resource_source_id,
2915 expenditure_category resource_name, 2 type_number
2916 from pa_expenditure_categories)
2917 UNION
2918 (select event_type_id resource_source_id,
2919 event_type resource_name, 3 type_number
2920 from pa_event_types)) source
2921 WHERE rlm.resource_list_id = l_list.resource_list_id
2922 AND ((rlm.resource_type_id = 106
2923 AND typ.res_type_code = 'EVENT_TYPE'
2924 AND source.resource_name = rlm.event_type
2925 AND source.type_number = 3) OR -- Event Type
2926 (rlm.resource_type_id = 108
2927 AND typ.res_type_code = 'EXPENDITURE_CATEGORY'
2928 AND source.resource_name = rlm.expenditure_category
2929 AND source.type_number = 2) --ECat
2930 )
2931 )
2932 UNION ALL
2933 (SELECT source.resource_source_id,
2934 typ.res_type_id -- resource_type_id
2935 FROM
2936 pa_resource_list_members rlm,
2937 (select res_type_id, res_type_code from pa_res_types_b) typ,
2938 (select expenditure_type_id resource_source_id,
2939 expenditure_type resource_name, 1 type_number
2940 from pa_expenditure_types) source
2941 WHERE rlm.resource_list_id = l_list.resource_list_id
2942 AND (rlm.resource_type_id = 105
2943 AND typ.res_type_code = 'EXPENDITURE_TYPE'
2944 AND source.resource_name = rlm.expenditure_type
2945 AND source.type_number = 1)--ExpType
2946 )
2947 );
2948
2949
2950 -- Generate number Key for Rev Cat and populate map table
2951 -- before calling populate element name.
2952 FOR l_rev_cat in get_rev_cat(l_list.resource_list_id) LOOP
2953 pa_rbs_mapping.create_res_type_numeric_id (
2954 p_resource_name => l_rev_cat.revenue_category,
2955 p_resource_type_id => l_rev_cat.res_type_id,
2956 x_resource_id => l_resource_id,
2957 x_return_status => x_return_status,
2958 --x_msg_count => l_msg_count,
2959 x_msg_data => x_msg_data);
2960
2961
2962 INSERT INTO PA_RBS_ELEM_IN_TEMP
2963 (resource_source_id,
2964 resource_type_id)
2965 VALUES (l_resource_id,
2966 l_rev_cat.res_type_id);
2967
2968 END LOOP;
2969
2970 --dbms_output.put_line('Call Populate_RBS_Element_Name');
2971
2972 -- select count(*) into l_name_count from pa_rbs_element_names_b;
2973 --dbms_output.put_line('l_name_count before Populate_RBS_Element_Name is ' || l_name_count);
2974
2975 PA_RBS_UTILS.Populate_RBS_Element_Name(
2976 p_resource_source_id => NULL,
2977 p_resource_type_id => NULL,
2978 x_rbs_element_name_id => l_rbs_dummy_id,
2979 x_return_status => x_return_status);
2980 -- select count(*) into l_name_count from pa_rbs_element_names_b;
2981 --dbms_output.put_line('l_name_count after Populate_RBS_Element_Name is ' || l_name_count);
2982 --dbms_output.put_line('error after Populate_RBS_Element_Name is ' || sqlerrm);
2983
2984 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2985 --dbms_output.put_line('Populate_RBS_Element_Name Error');
2986 rollback to l_resource_list_savepoint;
2987 ELSE
2988 --dbms_output.put_line('Populate_RBS_Element_Name Success');
2989 -- Continue by creating RBS elements.
2990 -- First create top node for the version:
2991 SELECT PA_RBS_ELEMENTS_S.nextval
2992 INTO l_top_node_id from dual;
2993
2994 SELECT PA_RBS_ELEMENT_IDENTIFIER_S.nextval
2995 INTO l_rbs_identifier_id from dual;
2996
2997
2998 SELECT rbs_element_name_id INTO l_element_name_id
2999 FROM pa_rbs_element_names_b
3000 WHERE resource_source_id = l_rbs_version_id
3001 AND resource_type_id = -1;
3002
3003 INSERT INTO PA_RBS_ELEMENTS (
3004 RBS_Element_Id,
3005 Rbs_Element_Name_Id,
3006 RBS_Version_Id,
3007 Outline_Number,
3008 Order_Number,
3009 Resource_Type_Id,
3010 Resource_Source_Id,
3011 Rule_Flag,
3012 Parent_Element_Id,
3013 Rbs_Level,
3014 Element_Identifier,
3015 User_Created_Flag,
3016 Last_Update_Date,
3017 Last_Updated_By,
3018 Creation_Date,
3019 Created_By,
3020 Last_Update_Login,
3021 Record_Version_Number )
3022 VALUES (
3023 l_top_node_id,
3024 l_element_name_id,
3025 l_rbs_version_id,
3026 0,
3027 0, -- P_Order_Number,
3028 -1,
3029 l_rbs_version_id,
3030 'N',
3031 NULL,
3032 1,
3033 l_rbs_identifier_id,
3034 'N',
3035 sysdate,
3036 fnd_global.user_id,
3037 sysdate,
3038 fnd_global.user_id,
3039 fnd_global.login_id,
3040 1);
3041
3042 -- First create the parent elements if the list
3043 -- is grouped.
3044 IF l_list.group_res_type in ('ORGANIZATION',
3045 'EXPENDITURE_CATEGORY',
3046 'REVENUE_CATEGORY') THEN
3047 FOR l_parents in get_parents(l_list.resource_list_id) LOOP
3048 --dbms_output.put_line('Creating Parents');
3049 --dbms_output.put_line('Parent ID is : ' || l_parents.resource_list_member_id);
3050
3051 l_expenditure_category_id := NULL;
3052 l_revenue_category_id := NULL;
3053
3054 l_outline_number := to_char(to_number(l_outline_number)
3055 + 1);
3056
3057 SELECT PA_RBS_ELEMENTS_S.nextval
3058 INTO l_rbs_element_id from dual;
3059
3060 SELECT PA_RBS_ELEMENT_IDENTIFIER_S.nextval
3061 INTO l_rbs_identifier_id from dual;
3062
3063 --dbms_output.put_line('Getting Parent Exp Rev');
3064 --dbms_output.put_line('group_res_type is ' || l_list.group_res_type);
3065
3066 IF l_list.group_res_type = 'EXPENDITURE_CATEGORY' THEN
3067 SELECT expenditure_category_id
3068 INTO l_expenditure_category_id
3069 FROM pa_expenditure_categories
3070 WHERE expenditure_category =
3071 l_parents.expenditure_category;
3072 ELSIF l_list.group_res_type = 'REVENUE_CATEGORY' THEN
3073 BEGIN
3074 SELECT resource_id
3075 INTO l_revenue_category_id
3076 FROM pa_rbs_element_map
3077 WHERE resource_name = l_parents.revenue_category
3078 AND resource_type_id = (select res_type_id
3079 from pa_res_types_b
3080 where res_type_code = 'REVENUE_CATEGORY');
3081 EXCEPTION WHEN NO_DATA_FOUND THEN
3082 l_revenue_category_id := -999;
3083 END;
3084 END IF;
3085
3086 SELECT res_type_id INTO l_res_type_id
3087 FROM pa_res_types_b
3088 WHERE res_type_code = l_list.group_res_type;
3089
3090 --dbms_output.put_line('l_parents.organization_id is: ' || l_parents.organization_id);
3091 SELECT decode(l_list.group_res_type,
3092 'ORGANIZATION', l_parents.organization_id,
3093 decode(l_list.group_res_type,'EXPENDITURE_CATEGORY',
3094 l_expenditure_category_id,
3095 decode(l_list.group_res_type, 'REVENUE_CATEGORY',
3096 l_revenue_category_id, NULL)))
3097 INTO l_resource_source_id
3098 FROM dual;
3099
3100 --dbms_output.put_line('l_resource_source_id is: ' || l_resource_source_id);
3101
3102 BEGIN
3103 SELECT rbs_element_name_id
3104 INTO l_element_name_id
3105 FROM pa_rbs_element_names_b
3106 WHERE resource_source_id = l_resource_source_id
3107 AND resource_type_id = l_res_type_id;
3108 EXCEPTION WHEN NO_DATA_FOUND THEN
3109 l_element_name_id := -888;
3110 END;
3111
3112 --dbms_output.put_line('l_element_name_id is: ' || l_element_name_id);
3113
3114 INSERT INTO PA_RBS_ELEMENTS (
3115 RBS_Element_Id,
3116 Rbs_Element_Name_Id,
3117 RBS_Version_Id,
3118 Outline_Number,
3119 Order_Number,
3120 Resource_Type_Id,
3121 Resource_Source_Id,
3122 Organization_Id,
3123 Expenditure_Category_Id,
3124 Revenue_Category_Id,
3125 Rule_Flag,
3126 Parent_Element_Id,
3127 Rbs_Level,
3128 Element_Identifier,
3129 User_Created_Flag,
3130 Last_Update_Date,
3131 Last_Updated_By,
3132 Creation_Date,
3133 Created_By,
3134 Last_Update_Login,
3135 Record_Version_Number )
3136 Values (
3137 l_rbs_element_id,
3138 l_element_name_id,
3139 l_rbs_version_id,
3140 l_Outline_Number,
3141 null, -- P_Order_Number,
3142 l_res_type_id,
3143 l_resource_source_id,
3144 l_parents.organization_id,
3145 l_expenditure_category_id,
3146 l_revenue_category_id,
3147 'N',
3148 l_top_node_id,
3149 2,
3150 l_rbs_identifier_id,
3151 'N',
3152 sysdate,
3153 fnd_global.user_id,
3154 sysdate,
3155 fnd_global.user_id,
3156 fnd_global.login_id,
3157 1);
3158 -- Stamp rbs element ID on pa_resource_list_members for
3159 -- parent
3160
3161 -- IF l_status = 'FROZEN' THEN
3162 UPDATE pa_resource_list_members
3163 SET migrated_rbs_element_id = l_rbs_element_id
3164 WHERE resource_list_member_id =
3165 l_parents.resource_list_member_id;
3166 -- END IF;
3167
3168 END LOOP; -- Parents Loop
3169 END IF; -- Grouped List IF
3170
3171 -- Now create the rest of the elements
3172 l_old_parent_member_id := -1;
3173 l_outline_number := 0;
3174 FOR l_members in get_non_parents(l_list.resource_list_id) LOOP
3175 IF l_old_parent_member_id <> l_members.parent_member_id
3176 THEN
3177 l_child_outline_number := 1;
3178 l_old_parent_member_id := l_members.parent_member_id;
3179 END IF;
3180 --dbms_output.put_line('Creating Non Parents');
3181 --dbms_output.put_line('Member ID is : ' || l_members.resource_list_member_id);
3182 --dbms_output.put_line('Parent Member ID is : ' || l_members.parent_member_id);
3183
3184 l_expenditure_category_id := NULL;
3185 l_revenue_category_id := NULL;
3186 l_event_type_id := NULL;
3187 l_expenditure_type_id := NULL;
3188
3189 SELECT PA_RBS_ELEMENTS_S.nextval
3190 INTO l_rbs_element_id from dual;
3191
3192 SELECT PA_RBS_ELEMENT_IDENTIFIER_S.nextval
3193 INTO l_rbs_identifier_id from dual;
3194
3195 OPEN get_res_type_id(l_members.resource_type_id);
3196 FETCH get_res_type_id into l_res_type_id, l_res_type_code;
3197 CLOSE get_res_type_id;
3198
3199 --dbms_output.put_line('l_res_type_code is : ' || l_res_type_code);
3200
3201 IF l_members.parent_member_id IS NOT NULL THEN
3202 -- BEGIN
3203 SELECT migrated_rbs_element_id
3204 INTO l_parent_element_id
3205 FROM pa_resource_list_members
3206 WHERE resource_list_member_id =
3207 l_members.parent_member_id;
3208 -- EXCEPTION WHEN NO_DATA_FOUND THEN
3209 -- rollback to l_resource_list_savepoint;
3210 -- END;
3211 --dbms_output.put_line('l_parent_element_id is : ' || l_parent_element_id);
3212 SELECT (rbs_level + 1), expenditure_category_id,
3213 revenue_category_id
3214 INTO l_rbs_level, l_expenditure_category_id,
3215 l_revenue_category_id
3216 FROM pa_rbs_elements
3217 WHERE rbs_element_id = l_parent_element_id;
3218 ELSE
3219 l_parent_element_id := l_top_node_id;
3220 l_rbs_level := 2;
3221 END IF;
3222
3223
3224 IF l_parent_element_id IS NOT NULL THEN
3225 SELECT outline_number INTO l_parent_outline_number
3226 FROM pa_rbs_elements
3227 WHERE rbs_element_id = l_parent_element_id;
3228 --dbms_output.put_line('l_parent_outline_number is : ' || l_parent_outline_number);
3229 END IF;
3230
3231 --IF l_res_type_code = 'EXPENDITURE_CATEGORY' THEN
3232 IF l_members.expenditure_category IS NOT NULL THEN
3233 BEGIN
3234 SELECT expenditure_category_id
3235 INTO l_expenditure_category_id
3236 FROM pa_expenditure_categories
3237 WHERE expenditure_category =
3238 l_members.expenditure_category;
3239 EXCEPTION WHEN NO_DATA_FOUND THEN
3240 l_expenditure_category_id := -777;
3241 END;
3242 END IF;
3243
3244 --ELSIF l_res_type_code = 'REVENUE_CATEGORY' THEN
3245 IF l_members.revenue_category IS NOT NULL THEN
3246 BEGIN
3247 SELECT resource_id
3248 INTO l_revenue_category_id
3249 FROM pa_rbs_element_map
3250 WHERE resource_name = l_members.revenue_category
3251 AND resource_type_id = (select res_type_id
3252 from pa_res_types_b
3253 where res_type_code = 'REVENUE_CATEGORY');
3254 EXCEPTION WHEN NO_DATA_FOUND THEN
3255 l_revenue_category_id := -888;
3256 END;
3257 END IF;
3258
3259 --ELSIF l_res_type_code = 'EXPENDITURE_TYPE' THEN
3260 IF l_members.expenditure_type IS NOT NULL THEN
3261 SELECT expenditure_type_id
3262 INTO l_expenditure_type_id
3263 FROM pa_expenditure_types
3264 WHERE expenditure_type = l_members.expenditure_type;
3265 END IF;
3266
3267 --ELSIF l_res_type_code = 'EVENT_TYPE' THEN
3268 IF l_members.event_type IS NOT NULL THEN
3269 SELECT event_type_id
3270 INTO l_event_type_id
3271 FROM pa_event_types
3272 WHERE event_type = l_members.event_type;
3273 END IF;
3274
3275 SELECT decode(l_res_type_code,
3276 'ORGANIZATION', l_members.organization_id,
3277 decode(l_res_type_code,'EXPENDITURE_CATEGORY',
3278 l_expenditure_category_id,
3279 decode(l_res_type_code, 'REVENUE_CATEGORY',
3280 l_revenue_category_id,
3281 decode(l_res_type_code,'EXPENDITURE_TYPE',
3282 l_expenditure_type_id,
3283 decode(l_res_type_code,'EVENT_TYPE',l_event_type_id,
3284 decode(l_res_type_code,'JOB', l_members.job_id,
3285 decode(l_res_type_code,'NAMED_PERSON',
3286 l_members.person_id,
3287 decode(l_res_type_code,'ROLE',
3288 l_members.project_role_id,
3289 decode(l_res_type_code,'SUPPLIER',
3290 l_members.vendor_id, NULL)))))))))
3291 INTO l_resource_source_id
3292 FROM dual;
3293
3294 BEGIN
3295 SELECT rbs_element_name_id INTO l_element_name_id
3296 FROM pa_rbs_element_names_b
3297 WHERE resource_source_id = l_resource_source_id
3298 AND resource_type_id = l_res_type_id;
3299 EXCEPTION WHEN NO_DATA_FOUND THEN
3300 l_element_name_id := -888;
3301 END;
3302
3303 IF (l_parent_element_id IS NULL) OR
3304 (l_parent_element_id = l_top_node_id) THEN
3305 l_outline_number := to_char(to_number(
3306 l_outline_number) + 1);
3307 ELSE
3308 l_outline_number := l_parent_outline_number || '.' ||
3309 l_child_outline_number;
3310 l_child_outline_number := l_child_outline_number + 1;
3311 END IF;
3312
3313 INSERT INTO PA_RBS_ELEMENTS (
3314 RBS_Element_Id,
3315 Rbs_Element_Name_Id,
3316 RBS_Version_Id,
3317 Outline_Number,
3318 Order_Number,
3319 Resource_Type_Id,
3320 Resource_Source_Id,
3321 Organization_Id,
3322 person_id,
3323 job_id,
3324 role_id,
3325 supplier_id,
3326 Expenditure_Category_Id,
3327 Revenue_Category_Id,
3328 Expenditure_type_id,
3329 event_type_id,
3330 Rule_Flag,
3331 Parent_Element_Id,
3332 Rbs_Level,
3333 Element_Identifier,
3334 User_Created_Flag,
3335 Last_Update_Date,
3336 Last_Updated_By,
3337 Creation_Date,
3338 Created_By,
3339 Last_Update_Login,
3340 Record_Version_Number)
3341 Values (
3342 l_rbs_element_id,
3343 l_element_name_id,
3344 l_rbs_version_id,
3345 l_Outline_Number,
3346 null, -- P_Order_Number,
3347 l_res_type_id,
3348 l_resource_source_id,
3349 l_members.organization_id,
3350 l_members.person_id,
3351 l_members.job_id,
3352 l_members.project_role_id,
3353 l_members.vendor_id,
3354 l_expenditure_category_id,
3355 l_revenue_category_id,
3356 l_expenditure_type_id,
3357 l_event_type_id,
3358 'N',
3359 l_parent_element_id,
3360 l_rbs_level,
3361 l_rbs_identifier_id,
3362 'N',
3363 sysdate,
3364 fnd_global.user_id,
3365 sysdate,
3366 fnd_global.user_id,
3367 fnd_global.login_id,
3368 1);
3369
3370 -- Stamp rbs element ID on pa_resource_list_members for
3371 -- parent
3372
3373 -- IF l_status = 'FROZEN' THEN
3374 UPDATE pa_resource_list_members
3375 SET migrated_rbs_element_id = l_rbs_element_id
3376 WHERE resource_list_member_id =
3377 l_members.resource_list_member_id;
3378 -- END IF;
3379
3380 END LOOP; -- Non parents loop.
3381 END IF; -- No errors If
3382
3383 -- END LOOP; -- Versions loop (frozen and working)
3384
3385 -- Create frozen version elements again but with user_created_flag
3386 -- as 'Y'
3387 delete from Pa_Rbs_Elements_Temp;
3388 Insert Into Pa_Rbs_Elements_Temp(
3389 New_Element_Id,
3390 Old_Element_Id,
3391 Old_Parent_Element_Id,
3392 New_Parent_Element_Id )
3393 (Select
3394 Pa_Rbs_Elements_S.NextVal,
3395 Rbs_Element_Id,
3396 Parent_Element_Id,
3397 Null
3398 From
3399 Pa_Rbs_Elements
3400 Where
3401 Rbs_Version_Id = l_rbs_version_id
3402 and user_created_flag = 'N' );
3403
3404 Update Pa_Rbs_Elements_Temp Tmp1
3405 Set New_Parent_Element_Id =
3406 (Select
3407 New_Element_Id
3408 From
3409 Pa_Rbs_Elements_Temp Tmp2
3410 Where
3411 Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
3412
3413 --dbms_output.put_line('Updated into Temp');
3414
3415 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
3416 to remove the GSCC Warning File.Sql.33 */
3417 Insert Into Pa_Rbs_Elements
3418 (
3419 RBS_ELEMENT_ID,
3420 RBS_ELEMENT_NAME_ID,
3421 RBS_VERSION_ID,
3422 OUTLINE_NUMBER,
3423 ORDER_NUMBER,
3424 RESOURCE_TYPE_ID,
3425 RESOURCE_SOURCE_ID,
3426 PERSON_ID,
3427 JOB_ID,
3428 ORGANIZATION_ID,
3429 EXPENDITURE_TYPE_ID,
3430 EVENT_TYPE_ID,
3431 EXPENDITURE_CATEGORY_ID,
3432 REVENUE_CATEGORY_ID,
3433 inventory_item_id,
3434 item_category_id,
3435 bom_labor_id,
3436 bom_equipment_id,
3437 non_labor_resource_id,
3438 role_id,
3439 person_type_id,
3440 resource_class_id,
3441 supplier_id,
3442 rule_flag,
3443 PARENT_ELEMENT_ID,
3444 rbs_level,
3445 element_identifier,
3446 user_defined_custom1_id,
3447 user_defined_custom2_id,
3448 user_defined_custom3_id,
3449 user_defined_custom4_id,
3450 user_defined_custom5_id,
3451 USER_CREATED_FLAG,
3452 LAST_UPDATE_DATE,
3453 LAST_UPDATED_BY,
3454 CREATION_DATE,
3455 CREATED_BY,
3456 LAST_UPDATE_LOGIN,
3457 RECORD_VERSION_NUMBER)
3458 Select
3459 Tmp.New_Element_Id,
3460 Rbs_Elements.Rbs_Element_Name_Id,
3461 l_rbs_version_id,
3462 Rbs_Elements.Outline_Number,
3463 Rbs_Elements.Order_Number,
3464 Rbs_Elements.Resource_Type_Id,
3465 Rbs_Elements.Resource_Source_Id,
3466 Rbs_Elements.Person_Id,
3467 Rbs_Elements.Job_Id,
3468 Rbs_Elements.Organization_Id,
3469 Rbs_Elements.Expenditure_Type_Id,
3470 Rbs_Elements.Event_Type_Id,
3471 Rbs_Elements.Expenditure_Category_Id,
3472 Rbs_Elements.Revenue_Category_Id,
3473 Rbs_Elements.Inventory_Item_Id,
3474 Rbs_Elements.Item_Category_Id,
3475 Rbs_Elements.Bom_Labor_Id,
3476 Rbs_Elements.Bom_Equipment_Id,
3477 Rbs_Elements.Non_Labor_Resource_Id,
3478 Rbs_Elements.Role_Id,
3479 Rbs_Elements.Person_Type_Id,
3480 Rbs_Elements.Resource_Class_Id,
3481 Rbs_Elements.Supplier_Id,
3482 Rbs_Elements.Rule_Flag,
3483 Tmp.New_Parent_Element_Id,
3484 Rbs_Elements.Rbs_Level,
3485 Rbs_Elements.Element_Identifier,
3486 Rbs_Elements.User_Defined_Custom1_Id,
3487 Rbs_Elements.User_Defined_Custom2_Id,
3488 Rbs_Elements.User_Defined_Custom3_Id,
3489 Rbs_Elements.User_Defined_Custom4_Id,
3490 Rbs_Elements.User_Defined_Custom5_Id,
3491 'Y',
3492 Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
3493 Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
3494 Pa_Rbs_Versions_Pvt.G_Creation_Date,
3495 Pa_Rbs_Versions_Pvt.G_Created_By,
3496 Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
3497 1
3498 From
3499 Pa_Rbs_Elements Rbs_Elements,
3500 Pa_Rbs_Elements_Temp Tmp
3501 Where
3502 Tmp.Old_Element_Id = Rbs_Elements.Rbs_Element_Id;
3503
3504 -- Call API to populate mapping rules and mapping denorm tables.
3505 -- Only need to do it for the Frozen version.
3506
3507 -- Create mapping rules
3508 PA_RBS_MAPPING.create_mapping_rules(
3509 p_rbs_version_id => l_rbs_version_id,
3510 x_return_status => x_return_status,
3511 x_msg_count => x_msg_count,
3512 x_msg_data => x_msg_data);
3513
3514 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3515 --dbms_output.put_line('create_mapping_rules ERROR');
3516 rollback to l_resource_list_savepoint;
3517 END IF;
3518
3519 -- Populate denorm table used for reporting
3520 PJI_PJP_SUM_DENORM.populate_rbs_denorm_upgrade(
3521 p_rbs_version_id => l_rbs_version_id,
3522 x_return_status => x_return_status,
3523 x_msg_count => x_msg_count,
3524 x_msg_data => x_msg_data);
3525
3526 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3527 --dbms_output.put_line('POPULATE_RBS_DENORM_UPGRADE ERROR');
3528 rollback to l_resource_list_savepoint;
3529 END IF;
3530
3531 -- Bug 3950096 - create association for reporting if resource list
3532 -- was used for reporting.
3533 -- Bug 4303512 - added outer join to make sure only one
3534 -- RBS association is marked as primary.
3535 INSERT INTO pa_rbs_prj_assignments (
3536 RBS_PRJ_ASSIGNMENT_ID ,
3537 PROJECT_ID ,
3538 RBS_VERSION_ID ,
3539 RBS_HEADER_ID ,
3540 REPORTING_USAGE_FLAG ,
3541 WP_USAGE_FLAG ,
3542 FP_USAGE_FLAG ,
3543 PROG_REP_USAGE_FLAG ,
3544 PRIMARY_REPORTING_RBS_FLAG ,
3545 ASSIGNMENT_STATUS ,
3546 LAST_UPDATE_DATE ,
3547 LAST_UPDATED_BY ,
3548 CREATION_DATE ,
3549 CREATED_BY ,
3550 LAST_UPDATE_LOGIN ,
3551 RECORD_VERSION_NUMBER )
3552 (SELECT pa_rbs_prj_assignments_s.nextval,
3553 asg.project_id,
3554 l_rbs_version_id,
3555 l_rbs_header_id,
3556 'Y',
3557 'N',
3558 'N',
3559 'N',
3560 decode(rpa.primary_reporting_rbs_flag, 'Y', 'N', pru.default_flag),
3561 'ACTIVE',
3562 sysdate,
3563 fnd_global.user_id,
3564 sysdate,
3565 fnd_global.user_id,
3566 fnd_global.login_id,
3567 1
3568 FROM pa_resource_list_assignments asg,
3569 pa_resource_list_uses pru,
3570 pa_rbs_prj_assignments rpa
3571 WHERE asg.resource_list_id = l_list.resource_list_id
3572 AND asg.resource_list_assignment_id =
3573 pru.resource_list_assignment_id
3574 AND pru.use_code = 'ACTUALS_ACCUM'
3575 AND asg.project_id = rpa.project_id(+)
3576 AND rpa.primary_reporting_rbs_flag(+) = 'Y'
3577 );
3578
3579 -- Call PJI API to log an event. Bug 4249632.
3580 OPEN get_projects(l_list.resource_list_id);
3581 LOOP
3582 FETCH get_projects INTO l_project_id;
3583 EXIT WHEN get_projects%NOTFOUND;
3584
3585 PJI_FM_XBS_ACCUM_MAINT.RBS_PUSH
3586 (P_NEW_RBS_VERSION_ID => l_rbs_version_id,
3587 P_PROJECT_ID => l_project_id,
3588 X_RETURN_STATUS => x_return_status,
3589 X_MSG_CODE => x_msg_data);
3590
3591 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3592 --dbms_output.put_line('Even Log ERROR');
3593 rollback to l_resource_list_savepoint;
3594 END IF;
3595
3596 END LOOP;
3597 CLOSE get_projects;
3598
3599 -- Create Working Version and elements.
3600 l_outline_number := '0';
3601
3602 SELECT PA_RBS_VERSIONS_S.nextval
3603 INTO l_rbs_version_id from dual;
3604
3605 INSERT INTO PA_RBS_VERSIONS_B (
3606 RBS_VERSION_ID,
3607 VERSION_NUMBER,
3608 RBS_HEADER_ID,
3609 VERSION_START_DATE,
3610 VERSION_END_DATE,
3611 JOB_GROUP_ID,
3612 RULE_BASED_FLAG,
3613 VALIDATED_FLAG,
3614 STATUS_CODE,
3615 LAST_UPDATE_DATE,
3616 LAST_UPDATED_BY,
3617 CREATION_DATE,
3618 CREATED_BY,
3619 LAST_UPDATE_LOGIN,
3620 RECORD_VERSION_NUMBER,
3621 current_reporting_flag )
3622 VALUES (
3623 l_rbs_version_id,
3624 2,
3625 l_rbs_header_id,
3626 NULL,
3627 NULL,
3628 l_list.job_group_id,
3629 'N',
3630 'Y',
3631 'WORKING',
3632 sysdate,
3633 fnd_global.user_id,
3634 sysdate,
3635 fnd_global.user_id,
3636 fnd_global.login_id,
3637 1,
3638 'N');
3639
3640 --dbms_output.put_line('Created RBS Version : ' || l_rbs_version_id);
3641
3642 FOR l_names in get_lists_names(l_list.resource_list_id) LOOP -- RBS V TL Loop
3643 --dbms_output.put_line('Creating RBS Version Names TL');
3644 --dbms_output.put_line('RBS Version Name is: ' || l_names.name);
3645 INSERT INTO PA_RBS_VERSIONS_TL(
3646 RBS_VERSION_ID,
3647 NAME,
3648 DESCRIPTION,
3649 LANGUAGE,
3650 SOURCE_LANG,
3651 CREATION_DATE,
3652 CREATED_BY,
3653 LAST_UPDATE_DATE,
3654 LAST_UPDATED_BY,
3655 LAST_UPDATE_LOGIN)
3656 VALUES(
3657 l_rbs_version_id,
3658 l_rbs_header_name,
3659 l_names.description,
3660 l_names.language,
3661 l_names.source_lang,
3662 sysdate,
3663 fnd_global.user_id,
3664 sysdate,
3665 fnd_global.user_id,
3666 fnd_global.login_id);
3667 END LOOP; -- End RBS V TL names Loop Version
3668
3669 --dbms_output.put_line('Created RBS Working : ' || l_rbs_version_id);
3670
3671 delete from Pa_Rbs_Elements_Temp;
3672 Insert Into Pa_Rbs_Elements_Temp(
3673 New_Element_Id,
3674 Old_Element_Id,
3675 Old_Parent_Element_Id,
3676 New_Parent_Element_Id )
3677 (Select
3678 Pa_Rbs_Elements_S.NextVal,
3679 Rbs_Element_Id,
3680 Parent_Element_Id,
3681 Null
3682 From
3683 Pa_Rbs_Elements
3684 Where
3685 Rbs_Version_Id = l_Rbs_Version_From_Id
3686 and user_created_flag = 'Y' );
3687
3688 --dbms_output.put_line('Inserted into Temp');
3689 Update Pa_Rbs_Elements_Temp Tmp1
3690 Set New_Parent_Element_Id =
3691 (Select
3692 New_Element_Id
3693 From
3694 Pa_Rbs_Elements_Temp Tmp2
3695 Where
3696 Tmp1.Old_Parent_Element_Id = Tmp2.Old_Element_Id);
3697
3698 --dbms_output.put_line('Updated into Temp');
3699 /*Bug 4377886 : Included explicitly the column names in the INSERT statement
3700 to remove the GSCC Warning File.Sql.33 */
3701 Insert Into Pa_Rbs_Elements
3702 (
3703 RBS_ELEMENT_ID,
3704 RBS_ELEMENT_NAME_ID,
3705 RBS_VERSION_ID,
3706 OUTLINE_NUMBER,
3707 ORDER_NUMBER,
3708 RESOURCE_TYPE_ID,
3709 RESOURCE_SOURCE_ID,
3710 PERSON_ID,
3711 JOB_ID,
3712 ORGANIZATION_ID,
3713 EXPENDITURE_TYPE_ID,
3714 EVENT_TYPE_ID,
3715 EXPENDITURE_CATEGORY_ID,
3716 REVENUE_CATEGORY_ID,
3717 inventory_item_id,
3718 item_category_id,
3719 bom_labor_id,
3720 bom_equipment_id,
3721 non_labor_resource_id,
3722 role_id,
3723 person_type_id,
3724 resource_class_id,
3725 supplier_id,
3726 rule_flag,
3727 PARENT_ELEMENT_ID,
3728 rbs_level,
3729 element_identifier,
3730 user_defined_custom1_id,
3731 user_defined_custom2_id,
3732 user_defined_custom3_id,
3733 user_defined_custom4_id,
3734 user_defined_custom5_id,
3735 USER_CREATED_FLAG,
3736 LAST_UPDATE_DATE,
3737 LAST_UPDATED_BY,
3738 CREATION_DATE,
3739 CREATED_BY,
3740 LAST_UPDATE_LOGIN,
3741 RECORD_VERSION_NUMBER)
3742 Select
3743 Tmp.New_Element_Id,
3744 Rbs_Elements.Rbs_Element_Name_Id,
3745 l_rbs_version_id,
3746 Rbs_Elements.Outline_Number,
3747 Rbs_Elements.Order_Number,
3748 Rbs_Elements.Resource_Type_Id,
3749 Rbs_Elements.Resource_Source_Id,
3750 Rbs_Elements.Person_Id,
3751 Rbs_Elements.Job_Id,
3752 Rbs_Elements.Organization_Id,
3753 Rbs_Elements.Expenditure_Type_Id,
3754 Rbs_Elements.Event_Type_Id,
3755 Rbs_Elements.Expenditure_Category_Id,
3756 Rbs_Elements.Revenue_Category_Id,
3757 Rbs_Elements.Inventory_Item_Id,
3758 Rbs_Elements.Item_Category_Id,
3759 Rbs_Elements.Bom_Labor_Id,
3760 Rbs_Elements.Bom_Equipment_Id,
3761 Rbs_Elements.Non_Labor_Resource_Id,
3762 Rbs_Elements.Role_Id,
3763 Rbs_Elements.Person_Type_Id,
3764 Rbs_Elements.Resource_Class_Id,
3765 Rbs_Elements.Supplier_Id,
3766 Rbs_Elements.Rule_Flag,
3767 Tmp.New_Parent_Element_Id,
3768 Rbs_Elements.Rbs_Level,
3769 Rbs_Elements.Element_Identifier,
3770 Rbs_Elements.User_Defined_Custom1_Id,
3771 Rbs_Elements.User_Defined_Custom2_Id,
3772 Rbs_Elements.User_Defined_Custom3_Id,
3773 Rbs_Elements.User_Defined_Custom4_Id,
3774 Rbs_Elements.User_Defined_Custom5_Id,
3775 Rbs_Elements.User_Created_Flag,
3776 Pa_Rbs_Versions_Pvt.G_Last_Update_Date,
3777 Pa_Rbs_Versions_Pvt.G_Last_Updated_By,
3778 Pa_Rbs_Versions_Pvt.G_Creation_Date,
3779 Pa_Rbs_Versions_Pvt.G_Created_By,
3780 Pa_Rbs_Versions_Pvt.G_Last_Update_Login,
3781 1
3782 From
3783 Pa_Rbs_Elements Rbs_Elements,
3784 Pa_Rbs_Elements_Temp Tmp
3785 Where
3786 Tmp.Old_Element_Id = Rbs_Elements.Rbs_Element_Id;
3787
3788 --dbms_output.put_line('Craeted Elemenst ');
3789 Pa_Rbs_Utils.Populate_RBS_Element_Name (
3790 P_Resource_Source_Id => l_rbs_version_id,
3791 P_Resource_Type_Id => -1,
3792 X_Rbs_Element_Name_Id => l_new_element_name_id,
3793 X_Return_Status => x_return_status);
3794
3795 If x_return_status = Fnd_Api.G_Ret_Sts_Success Then
3796
3797 Update Pa_Rbs_Elements
3798 Set Rbs_Element_Name_Id = l_New_Element_Name_Id,
3799 Resource_Source_Id = l_rbs_version_id
3800 Where Rbs_Version_Id = l_rbs_version_id
3801 And Resource_Type_Id = -1
3802 And Rbs_Level = 1;
3803
3804 Else
3805 rollback to l_resource_list_savepoint;
3806 END IF;
3807
3808
3809 EXCEPTION
3810 WHEN OTHERS THEN
3811 --dbms_output.put_line('IN WHEN OTHERS ERROR');
3812 --dbms_output.put_line('SQLERRM IS : ' || sqlerrm);
3813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3814 --rollback;
3815
3816 END UPGRADE_LIST_TO_RBS;
3817
3818 /*******************************************************************
3819 * Procedure : Delete_proj_specific_RBS
3820 * Desc : This API is used to delete the project specific RBS
3821 * assignment once the project is deleted.
3822 *********************************************************************/
3823 PROCEDURE Delete_Proj_Specific_RBS(
3824 p_project_id IN NUMBER,
3825 x_return_status OUT NOCOPY VARCHAR2,
3826 x_msg_count OUT NOCOPY NUMBER)
3827 IS
3828 BEGIN
3829 x_return_status := FND_API.G_RET_STS_SUCCESS;
3830 x_msg_count := 0;
3831 DELETE FROM pa_rbs_prj_assignments
3832 WHERE project_id = p_project_id;
3833 EXCEPTION
3834 WHEN OTHERS THEN
3835 FND_MSG_PUB.add_exc_msg( p_pkg_name =>
3836 'Pa_RBS_Utils.Delete_Proj_Specific_RBS'
3837 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
3838 x_msg_count := x_msg_count+1;
3839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3840 END Delete_Proj_Specific_RBS;
3841
3842 procedure ADD_LANGUAGE
3843 is
3844 begin
3845 delete from pa_rbs_element_names_tl T
3846 where not exists
3847 (select NULL
3848 from pa_rbs_element_names_b B
3849 where B.RBS_ELEMENT_NAME_ID = T.RBS_ELEMENT_NAME_ID
3850 );
3851
3852 update pa_rbs_element_names_tl T set (
3853 RESOURCE_NAME
3854 ) = (select
3855 B.RESOURCE_NAME
3856 from pa_rbs_element_names_tl b
3857 where B.RBS_ELEMENT_NAME_ID = T.RBS_ELEMENT_NAME_ID
3858 and B.LANGUAGE = T.SOURCE_LANG)
3859 where (
3860 T.RBS_ELEMENT_NAME_ID,
3861 T.LANGUAGE
3862 ) in (select
3863 SUBT.RBS_ELEMENT_NAME_ID,
3864 SUBT.LANGUAGE
3865 from pa_rbs_element_names_tl SUBB, pa_rbs_element_names_tl SUBT
3866 where SUBB.RBS_ELEMENT_NAME_ID = SUBT.RBS_ELEMENT_NAME_ID
3867 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
3868 and (SUBB.RESOURCE_NAME <> SUBT.RESOURCE_NAME
3869 ));
3870
3871 insert into pa_rbs_element_names_tl (
3872 LAST_UPDATE_LOGIN,
3873 CREATION_DATE,
3874 CREATED_BY,
3875 LAST_UPDATE_DATE,
3876 LAST_UPDATED_BY,
3877 RBS_ELEMENT_NAME_ID,
3878 RESOURCE_NAME,
3879 LANGUAGE,
3880 SOURCE_LANG
3881 ) select
3882 B.LAST_UPDATE_LOGIN,
3883 B.CREATION_DATE,
3884 B.CREATED_BY,
3885 B.LAST_UPDATE_DATE,
3886 B.LAST_UPDATED_BY,
3887 B.RBS_ELEMENT_NAME_ID,
3888 B.RESOURCE_NAME,
3889 L.LANGUAGE_CODE,
3890 B.SOURCE_LANG
3891 from pa_rbs_element_names_tl B, FND_LANGUAGES L
3892 where L.INSTALLED_FLAG in ('I', 'B')
3893 and B.LANGUAGE = userenv('LANG')
3894 and not exists
3895 (select NULL
3896 from pa_rbs_element_names_tl T
3897 where T.RBS_ELEMENT_NAME_ID = B.RBS_ELEMENT_NAME_ID
3898 and T.LANGUAGE = L.LANGUAGE_CODE);
3899 end ADD_LANGUAGE;
3900
3901 END PA_RBS_UTILS;