[Home] [Help]
PACKAGE BODY: APPS.PA_RBS_MAPPING
Source
1 PACKAGE BODY PA_RBS_MAPPING AS
2 /* $Header: PARBSMPB.pls 120.24.12010000.4 2008/09/25 10:58:17 admarath ship $ */
3
4 -------------------------------------------------
5 --global variables
6 -------------------------------------------------
7 g_user_id NUMBER := fnd_global.user_id;
8 g_login_id NUMBER := fnd_global.login_id;
9 g_debug_mode VARCHAR2(10) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
10 g_module_name VARCHAR2(100) := 'PA_RBS_MAPPING';
11
12 -- added for bug#3995697
13 g_rule_id NUMBER := NULL;
14 g_max_level NUMBER := NULL;
15 g_res_type_cols PA_PLSQL_DATATYPES.Char30TabTyp ;
16 g_res_type_cols_flag VARCHAR2 (1) := NULL;
17 g_rbs_element_id NUMBER := NULL;
18 g_rule_type VARCHAR2 (1) := NULL;
19
20 ---------------------------------------------------------------------------------
21 -- g_denorm_refresh is used to conditionally populate RBS denorm tables only when
22 -- a new RBS element is created
23 --------------------------------------------------------------------------------
24 g_denorm_refresh VARCHAR2(1) := 'N';
25 -----------------------------------
26
27 FUNCTION auto_allocate_unique
28 (
29 p_lock_name VARCHAR2
30 ) RETURN VARCHAR2
31 IS
32 PRAGMA autonomous_transaction;
33 lockhndl varchar2(128);
34 BEGIN
35 dbms_lock.allocate_unique(p_lock_name,lockhndl,864000);
36 commit;
37 RETURN lockhndl;
38 END;
39
40 --------------------------------------------------
41 --Gets the sorted rules from pa_rbs_mapping_rules
42 --given rbs structure version and resource class
43 --------------------------------------------------
44 FUNCTION get_sorted_rules
45 (
46 p_struct_version_id NUMBER,
47 p_res_class_id NUMBER --1,2,3,4
48 ) RETURN SYSTEM.pa_num_tbl_type
49 IS
50 l_sorted_rules SYSTEM.pa_num_tbl_type;
51 l_precedence PA_PLSQL_DATATYPES.Char30TabTyp;
52
53 BEGIN
54 IF g_debug_mode = 'Y' THEN
55 PA_DEBUG.set_curr_function( p_function => 'get_sorted_rules'
56 ,p_debug_mode => g_debug_mode );
57 pa_debug.g_err_stage:= 'Inside get_sorted_rules . - p_struct_version_id : ' ||p_struct_version_id || ' - p_res_class_id: ' || p_res_class_id ;
58 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
59 END IF;
60 SELECT decode(p_res_class_id,1,PERSON_RC_PRECEDENCE,
61 2,EQUIPMENT_RC_PRECEDENCE,
62 3,MATERIAL_RC_PRECEDENCE,
63 4,FIN_ELEM_RC_PRECEDENCE),
64 rule_id
65 BULK COLLECT INTO
66 l_precedence,
67 l_sorted_rules
68 FROM pa_rbs_mapping_rules
69 WHERE element_version_id = p_struct_version_id
70 ORDER BY
71 max_level desc ,
72 1 ,
73 decode(level15,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level15,3,3),p_res_class_id)), --bug#3940722
74 decode(level14,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level14,3,3),p_res_class_id)),
75 decode(level13,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level13,3,3),p_res_class_id)),
76 decode(level12,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level12,3,3),p_res_class_id)),
77 decode(level11,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level11,3,3),p_res_class_id)),
78 decode(level10,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level10,3,3),p_res_class_id)),
79 decode(level9,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level9,3,3),p_res_class_id)),
80 decode(level8,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level8,3,3),p_res_class_id)),
81 decode(level7,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level7,3,3),p_res_class_id)),
82 decode(level6,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level6,3,3),p_res_class_id)),
83 decode(level5,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level5,3,3),p_res_class_id)),
84 decode(level4,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level4,3,3),p_res_class_id)),
85 decode(level3,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level3,3,3),p_res_class_id)),
86 decode(level2,null,1,PA_RBS_PREC_PUB.calc_rule_precedence(substr(level2,3,3),p_res_class_id)),
87 decode(substr(level15,1,1),'I',2,'R',1,0) desc, --bug#3908041
88 decode(substr(level14,1,1),'I',2,'R',1,0) desc,
89 decode(substr(level13,1,1),'I',2,'R',1,0) desc,
90 decode(substr(level12,1,1),'I',2,'R',1,0) desc,
91 decode(substr(level11,1,1),'I',2,'R',1,0) desc,
92 decode(substr(level10,1,1),'I',2,'R',1,0) desc,
93 decode(substr(level9,1,1),'I',2,'R',1,0) desc,
94 decode(substr(level8,1,1),'I',2,'R',1,0) desc,
95 decode(substr(level7,1,1),'I',2,'R',1,0) desc,
96 decode(substr(level6,1,1),'I',2,'R',1,0) desc,
97 decode(substr(level5,1,1),'I',2,'R',1,0) desc,
98 decode(substr(level4,1,1),'I',2,'R',1,0) desc,
99 decode(substr(level3,1,1),'I',2,'R',1,0) desc,
100 decode(substr(level2,1,1),'I',2,'R',1,0) desc;
101
102 IF g_debug_mode = 'Y' THEN
103 pa_debug.g_err_stage:= 'Exiting get_sorted_rules' ;
104 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
105 pa_debug.reset_curr_function;
106 END IF;
107 RETURN l_sorted_rules ;
108 END ;
109
110
111 ----------------------------------------
112 --Get the resource type id column name
113 --for the resource type token
114 ----------------------------------------
115 FUNCTION get_resource_type_cols
116 (
117 p_resource_type_token VARCHAR2
118 ) RETURN VARCHAR2
119 IS
120 BEGIN
121
122 IF p_resource_type_token = 'BML' THEN
123 RETURN 'bom_labor_id';
124 ELSIF p_resource_type_token = 'BME' THEN
125 RETURN 'bom_equipment_id';
126 ELSIF p_resource_type_token = 'PER' THEN
127 RETURN 'person_id';
128 ELSIF p_resource_type_token = 'EVT' THEN
129 RETURN 'event_type_id';
130 ELSIF p_resource_type_token = 'EXC' THEN
131 RETURN 'expenditure_category_id';
132 ELSIF p_resource_type_token = 'EXT' THEN
133 RETURN 'expenditure_type_id';
134 ELSIF p_resource_type_token = 'ITC' THEN
135 RETURN 'item_category_id';
136 ELSIF p_resource_type_token = 'ITM' THEN
137 RETURN 'inventory_item_id';
138 ELSIF p_resource_type_token = 'JOB' THEN
139 RETURN 'job_id';
140 ELSIF p_resource_type_token = 'ORG' THEN
141 RETURN 'organization_id';
142 ELSIF p_resource_type_token = 'PTP' THEN
143 RETURN 'person_type_id';
144 ELSIF p_resource_type_token = 'NLR' THEN
145 RETURN 'non_labor_resource_id';
146 ELSIF p_resource_type_token = 'RES' THEN
147 RETURN 'resource_class_id';
148 ELSIF p_resource_type_token = 'RVC' THEN
149 RETURN 'revenue_category_id';
150 ELSIF p_resource_type_token = 'ROL' THEN
151 RETURN 'role_id';
152 ELSIF p_resource_type_token = 'SUP' THEN
153 RETURN 'supplier_id';
154
155 -- added for custom nodes
156 -- bug#3810558 changed CUS1 to CU1 etc
157
158 ELSIF p_resource_type_token = 'CU1' THEN
159 RETURN 'USER_DEFINED_CUSTOM1_ID';
160 ELSIF p_resource_type_token = 'CU2' THEN
161 RETURN 'USER_DEFINED_CUSTOM2_ID';
162 ELSIF p_resource_type_token = 'CU3' THEN
163 RETURN 'USER_DEFINED_CUSTOM3_ID';
164 ELSIF p_resource_type_token = 'CU4' THEN
165 RETURN 'USER_DEFINED_CUSTOM4_ID';
166 ELSIF p_resource_type_token = 'CU5' THEN
167 RETURN 'USER_DEFINED_CUSTOM5_ID';
168
169 END IF;
170
171
172 END ;
173
174 ----------------------------------------
175 --Get the resource type id for
176 --the resource type token
177 ----------------------------------------
178 FUNCTION get_res_type_id
179 (
180 p_resource_type_token VARCHAR2
181 ) RETURN NUMBER
182 IS
183 BEGIN
184
185 IF p_resource_type_token = 'bom_labor_id' THEN
186 RETURN 1;
187 ELSIF p_resource_type_token = 'bom_equipment_id' THEN
188 RETURN 2;
189 ELSIF p_resource_type_token = 'person_id' THEN
190 RETURN 3;
191 ELSIF p_resource_type_token = 'event_type_id' THEN
192 RETURN 4;
193 ELSIF p_resource_type_token = 'expenditure_category_id' THEN
194 RETURN 5;
195 ELSIF p_resource_type_token = 'expenditure_type_id' THEN
196 RETURN 6;
197 ELSIF p_resource_type_token = 'item_category_id' THEN
198 RETURN 7;
199 ELSIF p_resource_type_token = 'inventory_item_id' THEN
200 RETURN 8;
201 ELSIF p_resource_type_token = 'job_id' THEN
202 RETURN 9;
203 ELSIF p_resource_type_token = 'organization_id' THEN
204 RETURN 10;
205 ELSIF p_resource_type_token = 'person_type_id' THEN
206 RETURN 11;
207 ELSIF p_resource_type_token = 'non_labor_resource_id' THEN
208 RETURN 12;
209 ELSIF p_resource_type_token = 'resource_class_id' THEN
210 RETURN 13;
211 ELSIF p_resource_type_token = 'revenue_category_id' THEN
212 RETURN 14;
213 ELSIF p_resource_type_token = 'role_id' THEN
214 RETURN 15;
215 ELSIF p_resource_type_token = 'supplier_id' THEN
216 RETURN 16;
217
218 -- added for custom nodes
219 ELSIF p_resource_type_token = 'USER_DEFINED_CUSTOM1_ID' THEN
220 RETURN 18;
221 ELSIF p_resource_type_token = 'USER_DEFINED_CUSTOM2_ID' THEN
222 RETURN 18;
223 ELSIF p_resource_type_token = 'USER_DEFINED_CUSTOM3_ID' THEN
224 RETURN 18;
225 ELSIF p_resource_type_token = 'USER_DEFINED_CUSTOM4_ID' THEN
226 RETURN 18;
227 ELSIF p_resource_type_token = 'USER_DEFINED_CUSTOM5_ID' THEN
228 RETURN 18;
229
230 END IF;
231
232
233 END ;
234
235 -------------------------------------------------------------------
236 -- Get the Level from the pa_rbs_mapping_rules for the Rule Id
237 -------------------------------------------------------------------
238
239 --modified function below for bug#4478902
240
241 FUNCTION get_level
242 (
243 p_rule_id NUMBER,
244 p_level NUMBER
245 )
246 RETURN VARCHAR2
247 IS
248
249 l_value VARCHAR2(30);
250
251
252 BEGIN
253 IF g_debug_mode = 'Y' THEN
254 PA_DEBUG.set_curr_function( p_function => 'get_level'
255 ,p_debug_mode => g_debug_mode );
256 pa_debug.g_err_stage:= 'Inside get_level : - p_rule_id: ' || p_rule_id || ' - p_level :' || p_level ;
257 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
258 END IF;
259
260 CASE p_level
261 WHEN 1 THEN
262 Select Level1 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
263 WHEN 2 THEN
264 Select Level2 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
265 WHEN 3 THEN
266 Select Level3 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
267 WHEN 4 THEN
268 Select Level4 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
269 WHEN 5 THEN
270 Select Level5 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
271 WHEN 6 THEN
272 Select Level6 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
273 WHEN 7 THEN
274 Select Level7 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
275 WHEN 8 THEN
276 Select Level8 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
277 WHEN 9 THEN
278 Select Level9 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
279 WHEN 10 THEN
280 Select Level10 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
281 WHEN 11 THEN
282 Select Level11 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
283 WHEN 12 THEN
284 Select Level12 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
285 WHEN 13 THEN
286 Select Level13 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
287 WHEN 14 THEN
288 Select Level14 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
289 WHEN 15 THEN
290 Select Level15 into l_value from pa_rbs_mapping_rules where rule_id=p_rule_id;
291 ELSE
292 l_value:=NULL;
293 END CASE;
294
295
296 IF g_debug_mode = 'Y' THEN
297 pa_debug.g_err_stage:= 'Exiting get_level - l_level :' || l_value ;
298 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
299 pa_debug.reset_curr_function;
300 END IF;
301 RETURN l_value;
302 END;
303
304 ------------------------------------------------------
305 --map the transactions
306 ------------------------------------------------------
307
308 PROCEDURE delete_tmp_tables
309 (
310 p_max_level IN NUMBER
311 )
312 IS
313
314 BEGIN
315 IF g_debug_mode = 'Y' THEN
316 PA_DEBUG.set_curr_function( p_function => 'Delete Tmp Tables'
317 ,p_debug_mode => g_debug_mode );
318 pa_debug.g_err_stage:= 'Inside Delete Tmp Tables - p_max_level : '|| p_max_level;
319 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
320 END IF;
321
322 FOR l IN 3..p_max_level+3 LOOP
323 CASE l
324 WHEN 1 THEN
325 DELETE pa_rbs_map_tmp1;
326 WHEN 2 THEN
327 DELETE pa_rbs_map_tmp2;
328 WHEN 3 THEN
329 DELETE pa_rbs_map_tmp3;
330 WHEN 4 THEN
331 DELETE pa_rbs_map_tmp4;
332 WHEN 5 THEN
333 DELETE pa_rbs_map_tmp5;
334 WHEN 6 THEN
335 DELETE pa_rbs_map_tmp6;
336 WHEN 7 THEN
337 DELETE pa_rbs_map_tmp7;
338 WHEN 8 THEN
339 DELETE pa_rbs_map_tmp8;
340 WHEN 9 THEN
341 DELETE pa_rbs_map_tmp9;
342 WHEN 10 THEN
343 DELETE pa_rbs_map_tmp10;
344 WHEN 11 THEN
345 DELETE pa_rbs_map_tmp11;
346 WHEN 12 THEN
347 DELETE pa_rbs_map_tmp12;
348 WHEN 13 THEN
349 DELETE pa_rbs_map_tmp13;
350 ELSE
351 NULL;
352 END CASE;
353 END LOOP;
354
355
356
357 IF g_debug_mode = 'Y' THEN
358 pa_debug.g_err_stage:= 'Exiting Delete Tmp Tables' ;
359 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
360 pa_debug.reset_curr_function;
361 END IF;
362 END;
363
364 ---------------------------------------------
365 -- Get the list of resource type columns for
366 -- a rule
367 ---------------------------------------------
368 FUNCTION get_res_type_cols
369 (
370 p_rule_id NUMBER
371 )
372 RETURN PA_PLSQL_DATATYPES.Char30TabTyp
373 IS
374
375 --bug#3995697 l_max_level NUMBER;
376 l_token VARCHAR2(30);
377 l_res_type_cols PA_PLSQL_DATATYPES.Char30TabTyp;
378 j NUMBER;
379
380 BEGIN
381 IF g_debug_mode = 'Y' THEN
382 PA_DEBUG.set_curr_function( p_function => 'get_res_type_cols'
383 ,p_debug_mode => g_debug_mode );
384 pa_debug.g_err_stage:= 'Inside get_res_type_cols';
385 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
386 END IF;
387
388
389 j := 1;
390 FOR i IN 2..g_max_level LOOP
391 l_token:= get_level(p_rule_id,i);
392 --EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;
393 --commented if and endif for custom nodes
394 --IF substr(l_token,1,1) = 'R' OR substr(l_token,1,1) = 'I' THEN -- condition should be false for custom based node
395 l_res_type_cols(j) := get_resource_type_cols(substr(l_token,3,3)); --bug#3759977
396 j := j+1;
397 --END IF;
398
399 END LOOP;
400 IF g_debug_mode = 'Y' THEN
401 pa_debug.g_err_stage:= 'Exiting get_res_type_cols' ;
402 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
403 pa_debug.reset_curr_function;
404 END IF;
405 RETURN l_res_type_cols ;
406 END ;
407
408 ---------------------------------------------
409 -- Get the list of resource type columns along
410 -- with values for nodes of a rule which
411 -- contain value
412 ---------------------------------------------
413
414 --modified function below for bug#3759977
415
416 FUNCTION get_res_type_cols_inst
417 (
418 p_rule_id NUMBER,
419 p_level NUMBER
420 ) RETURN PA_PLSQL_DATATYPES.Char60TabTyp
421 IS
422 l_token VARCHAR2(30);
423 l_res_type_cols PA_PLSQL_DATATYPES.Char60TabTyp;
424 j NUMBER;
425
426 l_prev_token VARCHAR2(30);
427
428 BEGIN
429 IF g_debug_mode = 'Y' THEN
430 PA_DEBUG.set_curr_function( p_function => 'get_res_type_cols_inst'
431 ,p_debug_mode => g_debug_mode );
432 pa_debug.g_err_stage:= 'Inside get_res_type_cols_inst';
433 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
434 END IF;
435
436 j := 1 ;
437 l_prev_token := 'NULL' ;
438 FOR i IN REVERSE 2..p_level LOOP
439 l_token:= get_level(p_rule_id,i);
440
441 --EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;
442 IF substr(l_token,0,1) = 'I' AND substr(l_token,3,3) <> l_prev_token THEN
443 l_res_type_cols(j) := ' TMP.' || get_resource_type_cols(substr(l_token,3,3));
444 l_res_type_cols(j) := l_res_type_cols(j) || ' = ' || substr(l_token,7);
445 j := j+1 ;
446 l_prev_token := substr(l_token,3,3) ;
447 END IF;
448 END LOOP;
449
450
451 IF g_debug_mode = 'Y' THEN
452 pa_debug.g_err_stage:= 'Exiting get_res_type_cols_inst' ;
453 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
454 pa_debug.reset_curr_function;
455 END IF;
456
457 RETURN l_res_type_cols ;
458 END;
459
460 ---------------------------------------------
461 -- function added for same resource type
462 -- below function would return the col name
463 -- as it is for rule based nodes and value followed
464 -- col name for instance based nodes. the sql clause
465 -- would like below in case of operation type NONE
466 --(job is rule, org is instance) .
467 -- l_sql_clause := job_id, 243 organization_id
468 -- in case of operation type EQUAL
469 -- l_sql_clause := RBS.job_id = TMP.job_id and
470 -- RBS.organization_id = 243
471 ---------------------------------------------
472 FUNCTION get_sql_clause_unmap
473 (
474 p_rule_id NUMBER,
475 p_level NUMBER,
476 p_operation_type VARCHAR2
477 ) RETURN VARCHAR2
478 IS
479 l_token VARCHAR2(30);
480 l_res_type_cols PA_PLSQL_DATATYPES.Char60TabTyp;
481
482 l_res_type_cols2 PA_PLSQL_DATATYPES.Char240TabTyp;
483 j NUMBER;
484
485 l_prev_token VARCHAR2(30);
486 l_sql_clause VARCHAR2 (1000);
487
488 BEGIN
489 IF g_debug_mode = 'Y' THEN
490 PA_DEBUG.set_curr_function( p_function => 'get_sql_clause_unmap'
491 ,p_debug_mode => g_debug_mode );
492 pa_debug.g_err_stage:= 'Inside get_sql_clause_unmap - p_operation_type :'|| p_operation_type;
493 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
494 END IF;
495
496 j := 1 ;
497 l_prev_token := 'NULL' ;
498 FOR i IN REVERSE 2..p_level LOOP
499 l_token:= get_level(p_rule_id,i);
500 --EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;
501 IF substr(l_token,0,1) <> 'I' THEN
502 l_res_type_cols(j) := get_resource_type_cols(substr(l_token,3,3));
503 l_res_type_cols2(j) := 'RBS.' || get_resource_type_cols(substr(l_token,3,3)) || ' (+) = TMP.' || get_resource_type_cols(substr(l_token,3,3));
504 j := j+1 ;
505 ELSIF substr(l_token,0,1) = 'I' AND substr(l_token,3,3) <> l_prev_token THEN
506 l_res_type_cols(j) := substr(l_token,7) || ' ' || get_resource_type_cols(substr(l_token,3,3));
507 l_res_type_cols2(j) := 'RBS.' || get_resource_type_cols(substr(l_token,3,3)) || ' (+) = ' || substr(l_token,7) ;
508 j := j+1 ;
509 l_prev_token := substr(l_token,3,3) ;
510 END IF;
511 END LOOP;
512
513 l_sql_clause := ' ';
514 IF p_operation_type = 'NONE' THEN
515 FOR i IN 1..l_res_type_cols.COUNT LOOP
516 IF i=1 THEN
517 l_sql_clause := l_sql_clause || l_res_type_cols(i) ;
518 ELSE
519 l_sql_clause := l_sql_clause || ',' || l_res_type_cols(i);
520 END IF;
521 END LOOP;
522 END IF;
523
524 IF p_operation_type = 'EQUAL' THEN
525 FOR i IN 1..l_res_type_cols2.COUNT LOOP
526 IF i=1 THEN
527 l_sql_clause := l_sql_clause || l_res_type_cols2(i) ;
528 ELSE
529 l_sql_clause := l_sql_clause || ' AND ' || l_res_type_cols2(i);
530 END IF;
531 END LOOP;
532 END IF;
533
534 IF p_level = 1 AND p_operation_type = 'EQUAL' THEN
535 l_sql_clause := ' 1 = 1 ' ;
536 END IF;
537
538 IF g_debug_mode = 'Y' THEN
539 pa_debug.g_err_stage:= 'Exiting get_sql_clause_unmap - l_sql_clause : ' || l_sql_clause;
540 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
541 pa_debug.reset_curr_function;
542 END IF;
543
544 RETURN l_sql_clause ;
545 END;
546
547
548 ---------------------------------------------
549 -- function added for same resource type
550 -- below function would return only rule based col name
551 ---------------------------------------------
552 FUNCTION get_sql_clause_rule
553 (
554 p_rule_id NUMBER,
555 p_level NUMBER,
556 p_operation_type VARCHAR2
557 ) RETURN VARCHAR2
558 IS
559 l_token VARCHAR2(30);
560 l_res_type_cols PA_PLSQL_DATATYPES.Char60TabTyp;
561 j NUMBER;
562
563 l_sql_clause VARCHAR2 (1000);
564
565 BEGIN
566 IF g_debug_mode = 'Y' THEN
567 PA_DEBUG.set_curr_function( p_function => 'get_sql_clause_rule'
568 ,p_debug_mode => g_debug_mode );
569 pa_debug.g_err_stage:= 'Inside get_sql_clause_rule - p_operation_type :'|| p_operation_type;
570 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
571 END IF;
572
573 j := 1 ;
574 FOR i IN REVERSE 2..p_level LOOP
575 l_token:= get_level(p_rule_id,i);
576
577 --EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = ' || p_rule_id INTO l_token ;
578 IF substr(l_token,0,1) <> 'I' THEN
579 l_res_type_cols(j) := get_resource_type_cols(substr(l_token,3,3));
580 j := j+1 ;
581 END IF;
582 END LOOP;
583
584 l_sql_clause := '';
585 IF p_operation_type = 'EQUAL2' THEN
586 FOR i IN 1..l_res_type_cols.COUNT LOOP
587 IF i=1 THEN
588 l_sql_clause := l_sql_clause || 'TMP.' || l_res_type_cols(i) || ' = TMP1.' || l_res_type_cols(i) || '(+)' ;
589 ELSE
590 l_sql_clause := l_sql_clause || ' AND TMP.' || l_res_type_cols(i) || ' = TMP1.' || l_res_type_cols(i) || '(+)' ;
591 END IF;
592 END LOOP;
593 END IF;
594
595 IF l_sql_clause IS NULL THEN
596 l_sql_clause := ' 1=1 ';
597 END IF;
598
599 IF g_debug_mode = 'Y' THEN
600 pa_debug.g_err_stage:= 'Exiting get_sql_clause_rule- l_sql_clause : ' || l_sql_clause;
601 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
602 pa_debug.reset_curr_function;
603 END IF;
604
605 RETURN l_sql_clause ;
606 END;
607
608 -------------------------------------------------------------------
609 --Generate the SQL clause
610 --Operation types allowed - NONE,TMP,EQUAL(RBS=TMP),EQUAL2(TMP=TMP1),
611 --RES_SOURCE (it would return resource source column name),
612 --RES_TYP_ID (it would return resource type id )
613 -------------------------------------------------------------------
614
615 --modified function below for bug#3759977
616
617 FUNCTION get_sql_clause
618 (
619 p_rule_id NUMBER,
620 p_level NUMBER,
621 p_operation_type VARCHAR2
622 ) RETURN VARCHAR2
623 IS
624 l_res_type_cols PA_PLSQL_DATATYPES.Char30TabTyp;
625 l_sql_clause VARCHAR2 (500);
626
627 l_curr_res_col VARCHAR2 (240);
628 l_prev_res_col VARCHAR2 (240);
629
630 BEGIN
631 IF g_debug_mode = 'Y' THEN
632 PA_DEBUG.set_curr_function( p_function => 'get_sql_clause'
633 ,p_debug_mode => g_debug_mode );
634 pa_debug.g_err_stage:= 'Inside get_sql_clause - p_operation_type:'|| p_operation_type;
635 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
636 END IF;
637
638 -- implement caching, bug#3995697
639 IF p_rule_id = g_rule_id AND g_res_type_cols_flag IS NOT NULL THEN
640 l_res_type_cols := g_res_type_cols ;
641 ELSE
642 l_res_type_cols := get_res_type_cols(p_rule_id);
643
644 g_rule_id := p_rule_id;
645 g_res_type_cols := l_res_type_cols;
646 g_res_type_cols_flag := 'Y' ;
647 END IF ;
648
649 l_sql_clause := ' ';
650 IF p_level > 1 THEN
651 IF p_operation_type = 'NONE' THEN
652 FOR i IN 1..(p_level-1) LOOP
653 IF i=1 THEN
654 l_sql_clause := l_sql_clause || l_res_type_cols(i) ;
655 l_prev_res_col := l_res_type_cols(i);
656 ELSE
657 l_curr_res_col := l_res_type_cols(i) ;
658 IF l_curr_res_col <> l_prev_res_col THEN
659 l_sql_clause := l_sql_clause || ',' || l_res_type_cols(i);
660 END IF;
661 l_prev_res_col := l_res_type_cols(i) ;
662 END IF;
663 END LOOP;
664 ELSIF p_operation_type = 'TMP' THEN
665 FOR i IN 1..(p_level-1) LOOP
666 IF i=1 THEN
667 l_sql_clause := l_sql_clause || 'TMP.' || l_res_type_cols(i) ;
668 l_prev_res_col := l_res_type_cols(i);
669 ELSE
670 l_curr_res_col := l_res_type_cols(i) ;
671 IF l_curr_res_col <> l_prev_res_col THEN
672 l_sql_clause := l_sql_clause || ',' || 'TMP.' || l_res_type_cols(i);
673 END IF;
674 l_prev_res_col := l_res_type_cols(i) ;
675 END IF;
676 END LOOP;
677 ELSIF p_operation_type = 'EQUAL' THEN
678 FOR i IN 1..(p_level-1) LOOP
679 IF i=1 THEN
680 l_sql_clause := l_sql_clause || 'TMP.' || l_res_type_cols(i) || ' = RBS.' || l_res_type_cols(i) || '(+)' ;
681 l_prev_res_col := l_res_type_cols(i);
682 ELSE
683 l_curr_res_col := l_res_type_cols(i) ;
684 IF l_curr_res_col <> l_prev_res_col THEN
685 l_sql_clause := l_sql_clause || ' AND TMP.' || l_res_type_cols(i) || ' = RBS.' || l_res_type_cols(i) || '(+) ' ;
686 END IF;
687 l_prev_res_col := l_res_type_cols(i) ;
688 END IF;
689 END LOOP;
690 ELSIF p_operation_type = 'EQUAL2' THEN
691 FOR i IN 1..(p_level-1) LOOP
692 IF i=1 THEN
693 l_sql_clause := l_sql_clause || 'TMP.' || l_res_type_cols(i) || ' = TMP1.' || l_res_type_cols(i) || '(+)' ;
694 l_prev_res_col := l_res_type_cols(i);
695 ELSE
696 l_curr_res_col := l_res_type_cols(i) ;
697 IF l_curr_res_col <> l_prev_res_col THEN
698 l_sql_clause := l_sql_clause || ' AND TMP.' || l_res_type_cols(i) || ' = TMP1.' || l_res_type_cols(i) || '(+) ' ;
699 END IF;
700 l_prev_res_col := l_res_type_cols(i) ;
701 END IF;
702 END LOOP;
703 ELSIF p_operation_type = 'NOTNULL' THEN
704 FOR i IN 1..(p_level-1) LOOP
705 IF i=1 THEN
706 l_sql_clause := l_sql_clause || 'TMP.' || l_res_type_cols(i) || ' IS NOT NULL' ;
707 l_prev_res_col := l_res_type_cols(i);
708 ELSE
709 l_curr_res_col := l_res_type_cols(i) ;
710 IF l_curr_res_col <> l_prev_res_col THEN
711 l_sql_clause := l_sql_clause || ' AND TMP.' || l_res_type_cols(i) || ' IS NOT NULL' ;
712 END IF;
713 l_prev_res_col := l_res_type_cols(i) ;
714 END IF;
715 END LOOP;
716 ELSIF p_operation_type = 'RES_SOURCE' THEN
717 l_sql_clause := 'TMP.' || l_res_type_cols(p_level-1);
718 ELSIF p_operation_type = 'RES_TYP_ID' THEN
719 l_sql_clause := get_res_type_id(l_res_type_cols(p_level-1));
720 END IF;
721 END IF;
722
723 IF p_level = 1 AND ( p_operation_type = 'EQUAL' OR p_operation_type = 'EQUAL2') THEN
724 l_sql_clause := ' 1 = 1 ' ;
725 END IF;
726 IF g_debug_mode = 'Y' THEN
727 pa_debug.g_err_stage:= 'Exiting get_sql_clause - l_sql_clause' || l_sql_clause;
728 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
729 pa_debug.reset_curr_function;
730 END IF;
731 RETURN l_sql_clause;
732 END;
733
734
735
736 -------------------------------------------------------------------
737 -- Generate the SQL clause for Instance for nodes of a rule which
738 -- contain value
739 -------------------------------------------------------------------
740
741 --modified function below for bug#3759977
742
743 FUNCTION get_sql_clause_inst
744 (
745 p_struct_version_id NUMBER,
746 p_rule_id NUMBER,
747 p_level NUMBER
748 )
749 RETURN VARCHAR2
750 IS
751
752 l_res_type_cols PA_PLSQL_DATATYPES.Char60TabTyp;
753 l_sql_clause VARCHAR2 (500);
754 l_value NUMBER;
755 l_sql_stmt VARCHAR2 (500);
756 l_rbs_element_id NUMBER;
757
758 BEGIN
759 IF g_debug_mode = 'Y' THEN
760 PA_DEBUG.set_curr_function( p_function => 'get_sql_clause_inst'
761 ,p_debug_mode => g_debug_mode );
762 pa_debug.g_err_stage:= 'Inside get_sql_clause_inst- p_struct_version_id :'|| p_struct_version_id || ' p_level: ' || p_level;
763 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
764 END IF;
765
766 l_res_type_cols := get_res_type_cols_inst(p_rule_id, p_level);
767 l_sql_clause := '';
768
769 FOR i IN 1..l_res_type_cols.COUNT LOOP
770 /*
771 select rbs_element_id
772 into l_rbs_element_id
773 from pa_rbs_mapping_rules
774 where rule_id = p_rule_id ;
775
776 --bug#3642329
777 l_sql_stmt := ' SELECT ' || l_res_type_cols(i) ||
778 ' FROM pa_rbs_elements ' ||
779 ' WHERE rbs_version_id = ' || p_struct_version_id ||
780 ' AND user_created_flag = ' || '''Y''' ||
781 ' AND rbs_element_id = ' || l_rbs_element_id ;
782
783 EXECUTE IMMEDIATE l_sql_stmt INTO l_value ;
784
785 */
786
787 IF i = l_res_type_cols.COUNT THEN
788 l_sql_clause := l_sql_clause || l_res_type_cols(i) ;
789 ELSE
790 l_sql_clause := l_sql_clause || l_res_type_cols(i) || ' AND ' ;
791 END IF;
792 END LOOP;
793
794
795 IF p_level = 1 or l_sql_clause is null THEN --bug#3749017
796 l_sql_clause := ' 1 = 1 ' ;
797 END IF;
798 IF g_debug_mode = 'Y' THEN
799 pa_debug.g_err_stage:= 'Exiting get_sql_clause_inst - l_sql_clause' || l_sql_clause;
800 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
801 pa_debug.reset_curr_function;
802 END IF;
803 RETURN l_sql_clause;
804 END;
805
806
807
808 ----------------------------------------
809 --Get the RBS element id for the rule
810 ----------------------------------------
811
812 --added function for bug#3974663
813
814 FUNCTION get_rbs_element_id
815 (
816 p_rule_id NUMBER,
817 p_level NUMBER
818 )
819 RETURN NUMBER
820 IS
821 --bug#3995697 l_rbs_element_id NUMBER;
822 l_parent_element_id NUMBER;
823 BEGIN
824
825 SELECT rbs_element_id
826 INTO l_parent_element_id
827 FROM pa_rbs_elements
828 WHERE rbs_level = p_level
829 CONNECT BY rbs_element_id = PRIOR parent_element_id
830 START WITH rbs_element_id = g_rbs_element_id ;
831
832 RETURN l_parent_element_id;
833
834 END ;
835
836
837 ------------------------------------------------------
838 --map the transactions
839 ------------------------------------------------------
840
841 PROCEDURE mapped_header
842 (
843 p_rule_id IN NUMBER,
844 p_struct_version_id IN NUMBER,
845 p_level IN NUMBER,
846 p_res_class_id IN NUMBER,
847 x_return_status OUT NOCOPY VARCHAR2,
848 x_msg_count OUT NOCOPY NUMBER,
849 x_msg_data OUT NOCOPY VARCHAR2
850 )
851 IS
852 l_SQL_statement VARCHAR2 (2000);
853 l_INSERT_clause VARCHAR2 (500);
854 l_SELECT_clause VARCHAR2 (500);
855 l_FROM_clause VARCHAR2 (500);
856 l_WHERE_clause VARCHAR2 (1000);
857 --bug#3995697 l_rule_type VARCHAR2 (1);
858
859 BEGIN
860 IF g_debug_mode = 'Y' THEN
861 PA_DEBUG.set_curr_function( p_function => 'mapped_header'
862 ,p_debug_mode => g_debug_mode );
863 pa_debug.g_err_stage:= 'Inside mapped_header- p_struct_version_id :'|| p_struct_version_id || ' p_level: ' || p_level || ' p_res_class_id:'||p_res_class_id||' p_rule_id:'||p_rule_id;
864 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
865 END IF;
866
867
868 x_return_status := 0;
869
870 --delete pa_rbs_map_tmp3;
871 l_INSERT_clause := 'INSERT INTO pa_rbs_map_tmp3('
872 || 'txn_accum_header_id,'
873 || 'struct_version_id,'
874 || 'element_version_id,'
875 || 'parent_element_version_id,'
876 || 'resource_type_id,'
877 || 'resource_source_id,'
878 || get_sql_clause(p_rule_id,p_level,'NONE')
879 || ')' ;
880
881 l_SELECT_clause := 'SELECT '
882 || 'TMP.txn_accum_header_id,'
883 || ':p_struct_version_id,'
884 || 'RBS.rbs_element_id,'
885 || 'RBS.parent_element_id,'
886 || get_sql_clause(p_rule_id,p_level,'RES_TYP_ID') || ' ,'
887 || get_sql_clause(p_rule_id,p_level,'RES_SOURCE') || ' ,'
888 || get_sql_clause(p_rule_id,p_level,'TMP') ;
889
890 l_FROM_clause := 'FROM pa_rbs_map_tmp2 TMP,'
891 || 'pa_rbs_elements RBS ' ;
892
893
894
895 IF g_rule_type = 'N' THEN
896
897 l_WHERE_clause := ' WHERE ' ||
898 get_sql_clause(p_rule_id,p_level,'EQUAL') || ' AND ' ||
899 get_sql_clause_inst(p_struct_version_id, p_rule_id,p_level) || ' AND ' || -- added for instance based only
900 get_sql_clause(p_rule_id,p_level,'NOTNULL')|| ' AND ' ||
901 ' RBS.rbs_version_id(+) = TMP.struct_version_id AND ' ||
902 ' RBS.user_created_flag (+)= ' || '''' || 'N' || '''' || ' AND ' ||
903 ' RBS.rbs_level(+) = :p_level AND ' ||
904 ' TMP.resource_class_id = :p_res_class_id AND ' ||
905 ' TMP.txn_accum_header_id NOT IN ' ||
906 ' (SELECT txn_accum_header_id ' ||
907 ' FROM pa_rbs_txn_accum_map ' ||
908 ' WHERE struct_version_id = :p_struct_version_id)' ;
909
910 ELSE
911
912 l_WHERE_clause := ' WHERE ' ||
913 get_sql_clause(p_rule_id,p_level,'EQUAL') || ' AND ' ||
914 get_sql_clause(p_rule_id,p_level,'NOTNULL')|| ' AND ' ||
915 ' RBS.rbs_version_id (+)= TMP.struct_version_id AND ' ||
916 ' RBS.user_created_flag (+)= ' || '''' || 'N' || '''' || ' AND ' ||
917 ' RBS.rbs_level(+) = :p_level AND ' ||
918 ' TMP.resource_class_id = :p_res_class_id AND ' ||
919 ' TMP.txn_accum_header_id NOT IN ' ||
920 ' (SELECT txn_accum_header_id ' ||
921 ' FROM pa_rbs_txn_accum_map ' ||
922 ' WHERE struct_version_id = :p_struct_version_id)' ;
923
924 END IF;
925
926 l_SQL_statement := l_INSERT_clause || ' ' ||
927 l_SELECT_clause || ' ' ||
928 l_FROM_clause || ' ' ||
929 l_WHERE_clause ;
930
931
932 EXECUTE IMMEDIATE l_SQL_statement USING p_struct_version_id, p_level, p_res_class_id,p_struct_version_id;
933
934
935 IF SQL%ROWCOUNT = 0 THEN
936 x_return_status := 1;
937 END IF;
938
939
940 IF g_debug_mode = 'Y' THEN
941 pa_debug.g_err_stage:= 'Exiting mapped_header- Inserts in pa_rbs_map_tmp :'||SQL%ROWCOUNT ;
942 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
943 pa_debug.reset_curr_function;
944 END IF;
945 END;
946
947 --------------------------------------------------------
948 --insert unmapped transactions into the temporary table
949 --------------------------------------------------------
950
951 PROCEDURE unmapped_header
952 (
953 p_rule_id IN NUMBER,
954 p_struct_version_id IN NUMBER,
955 p_counter IN NUMBER,
956 p_level IN NUMBER
957 --x_return_status OUT NOCOPY VARCHAR2,
958 --x_msg_count OUT NOCOPY NUMBER,
959 --x_msg_data OUT NOCOPY VARCHAR2
960 )
961 IS
962 l_SQL_statement VARCHAR2 (2000);
963 l_INSERT_clause VARCHAR2 (500);
964 l_SELECT_clause VARCHAR2 (500);
965 l_FROM_clause VARCHAR2 (500);
966 l_WHERE_clause VARCHAR2 (500);
967 l_tmp NUMBER ;
968 --bug#3995697 l_rule_type VARCHAR2 (1);
969
970 BEGIN
971 IF g_debug_mode = 'Y' THEN
972 PA_DEBUG.set_curr_function( p_function => 'unmapped_header'
973 ,p_debug_mode => g_debug_mode );
974 pa_debug.g_err_stage:= 'Inside unmapped_header- p_struct_version_id :'|| p_struct_version_id || ' p_level: ' || p_level || ' p_counter:'||p_counter;
975 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
976 END IF;
977
978 l_tmp := 3 + p_counter ;
979
980 --EXECUTE IMMEDIATE 'DELETE pa_rbs_map_tmp' || l_tmp;
981
982 l_INSERT_clause := 'INSERT INTO pa_rbs_map_tmp' || l_tmp || ' ('
983 || 'struct_version_id, '
984 || 'parent_element_version_id,'
985 || 'resource_type_id,'
986 || 'resource_source_id,'
987 || 'sequence,'
988 || get_sql_clause(p_rule_id,p_level,'NONE')
989 || ')' ;
990
991 l_SELECT_clause := 'SELECT '
992 || ':p_struct_version_id,'
993 || 'RBS.rbs_element_id,'
994 || get_sql_clause(p_rule_id,p_level,'RES_TYP_ID') || ' ,'
995 || get_sql_clause(p_rule_id,p_level,'RES_SOURCE') || ' ,'
996 || 'pa_rbs_elements_s.nextval,'
997 || get_sql_clause(p_rule_id,p_level,'TMP') ;
998 l_tmp := 2 + p_counter ;
999
1000 l_FROM_clause := ' FROM ( SELECT DISTINCT struct_version_id, '
1001 || get_sql_clause_unmap(p_rule_id,p_level ,'NONE') --bug#3749017
1002 || ' FROM pa_rbs_map_tmp' || l_tmp
1003 || ' WHERE parent_element_version_id IS NULL '
1004 || ' ) TMP,'
1005 || 'pa_rbs_elements RBS ' ;
1006
1007
1008 IF g_rule_type = 'Y' THEN
1009
1010 l_tmp := p_level - 1;
1011 l_WHERE_clause := ' WHERE ' ||
1012 get_sql_clause(p_rule_id,p_level-1,'EQUAL') ||
1013 ' AND RBS.rbs_version_id(+) = TMP.struct_version_id ' ||
1014 ' AND RBS.user_created_flag (+)= ' || '''' || 'N' || '''' ||
1015 ' AND RBS.rbs_level(+) = :l_tmp';
1016
1017 ELSE
1018
1019 l_tmp := p_level - 1;
1020 l_WHERE_clause := ' WHERE ' ||
1021 get_sql_clause_unmap(p_rule_id,p_level-1,'EQUAL') || --bug#3749017
1022 --' AND ' || --bug#3749017
1023 --get_sql_clause_inst(p_struct_version_id, p_rule_id,p_level-1) || /* added for instance based only*/
1024 ' AND RBS.rbs_version_id(+) = TMP.struct_version_id ' ||
1025 ' AND RBS.user_created_flag (+)= ' || '''' || 'N' || '''' ||
1026 ' AND RBS.rbs_level(+) = :l_tmp';
1027
1028 END IF;
1029
1030 l_SQL_statement := l_INSERT_clause || ' ' ||
1031 l_SELECT_clause || ' ' ||
1032 l_FROM_clause || ' ' ||
1033 l_WHERE_clause || ';' ;
1034
1035 EXECUTE IMMEDIATE 'BEGIN ' ||l_SQL_statement || ' END;' USING p_struct_version_id, l_tmp;
1036
1037
1038 IF g_debug_mode = 'Y' THEN
1039 pa_debug.g_err_stage:= 'Exiting unmapped_header- Inserts in pa_rbs_map_tmp :'||SQL%ROWCOUNT ;
1040 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1041 pa_debug.reset_curr_function;
1042 END IF;
1043
1044
1045 END;
1046
1047 ------------------------------------------------------
1048 --last step in inserting unmapped transactions
1049 --into the temporary table
1050 ------------------------------------------------------
1051
1052 PROCEDURE unmapped_header_laststep
1053 (
1054 p_rule_id IN NUMBER,
1055 p_struct_version_id IN NUMBER,
1056 p_level IN NUMBER -- ,
1057 --x_return_status OUT NOCOPY VARCHAR2,
1058 --x_msg_count OUT NOCOPY NUMBER,
1059 --x_msg_data OUT NOCOPY VARCHAR2
1060 )
1061 IS
1062 l_SQL_statement VARCHAR2 (2000);
1063 l_INSERT_clause VARCHAR2 (500);
1064 l_SELECT_clause VARCHAR2 (500);
1065 l_FROM_clause VARCHAR2 (500);
1066 l_WHERE_clause VARCHAR2 (500);
1067 l_temp NUMBER;
1068 BEGIN
1069
1070 IF g_debug_mode = 'Y' THEN
1071 PA_DEBUG.set_curr_function( p_function => 'unmapped_header_laststep'
1072 ,p_debug_mode => g_debug_mode );
1073 pa_debug.g_err_stage:= 'Inside unmapped_header_laststep- p_struct_version_id :'|| p_struct_version_id || ' p_level: ' || p_level || ' p_rule_id:'||p_rule_id;
1074 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1075 END IF;
1076
1077 l_temp := 3+p_level ;
1078 --EXECUTE IMMEDIATE 'DELETE pa_rbs_map_tmp' || l_temp;
1079
1080 l_INSERT_clause := 'INSERT INTO pa_rbs_map_tmp' || l_temp
1081 || ' ('
1082 || ' struct_version_id, '
1083 || 'sequence, '
1084 || 'resource_type_id,'
1085 || 'resource_source_id '
1086 || ')' ;
1087
1088 l_SELECT_clause := 'SELECT '
1089 || ':p_struct_version_id,'
1090 || ' pa_rbs_elements_s.nextval , '
1091 || ' -1 , '
1092 || ':p_struct_version_id';
1093
1094 l_temp := 2+p_level ;
1095 l_FROM_clause := ' FROM ( SELECT distinct struct_version_id '
1096 || ' FROM pa_rbs_map_tmp' || l_temp
1097 || ' WHERE parent_element_version_id IS NULL '
1098 || ' ) ' ;
1099
1100 l_SQL_statement := l_INSERT_clause || ' ' ||
1101 l_SELECT_clause || ' ' ||
1102 l_FROM_clause || ' ;' ;
1103
1104
1105 EXECUTE IMMEDIATE 'BEGIN ' ||l_SQL_statement || ' END;' USING p_struct_version_id;
1106
1107
1108 IF g_debug_mode = 'Y' THEN
1109 pa_debug.g_err_stage:= 'Exiting unmapped_header_laststep- Inserts in pa_rbs_map_tmp :'||SQL%ROWCOUNT ;
1110 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1111 pa_debug.reset_curr_function;
1112 END IF;
1113
1114
1115
1116 END;
1117
1118 ------------------------------------------------------
1119 --create top rbs element inserting into pa_rbs_elements
1120 ------------------------------------------------------
1121
1122 PROCEDURE create_top_rbs_element
1123 (
1124 p_rule_id IN NUMBER,
1125 p_struct_version_id IN NUMBER,
1126 p_max_level IN NUMBER --,
1127 --x_return_status OUT NOCOPY VARCHAR2,
1128 --x_msg_count OUT NOCOPY NUMBER,
1129 --x_msg_data OUT NOCOPY VARCHAR2
1130 )
1131 IS
1132 l_SQL_statement VARCHAR2 (2000);
1133 l_INSERT_clause VARCHAR2 (500);
1134 l_SELECT_clause VARCHAR2 (500);
1135 l_FROM_clause VARCHAR2 (500);
1136 l_WHERE_clause VARCHAR2 (500);
1137 l_tmp NUMBER;
1138
1139 x_rbs_element_name_id NUMBER;
1140 x_return_status Varchar2(30);
1141
1142 BEGIN
1143 IF g_debug_mode = 'Y' THEN
1144 PA_DEBUG.set_curr_function( p_function => 'create_top_rbs_element'
1145 ,p_debug_mode => g_debug_mode );
1146 pa_debug.g_err_stage:= 'Inside create_top_rbs_element';
1147 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1148 END IF;
1149
1150 PA_RBS_UTILS.Populate_RBS_Element_Name(p_struct_version_id,-1,x_rbs_element_name_id,x_return_status);
1151
1152 l_INSERT_clause := 'INSERT INTO pa_rbs_elements ('
1153 || 'rbs_version_id,'
1154 || 'rbs_element_id,'
1155 || 'resource_source_id,'
1156 || 'rbs_level,'
1157 || 'rbs_element_name_id,'
1158 || 'outline_number,'
1159 || 'order_number,'
1160 || 'resource_type_id,'
1161 || 'rule_flag,'
1162 || 'element_identifier,'
1163 || 'user_created_flag,'
1164 || 'last_update_date,'
1165 || 'LAST_UPDATED_BY,'
1166 || 'CREATION_DATE,'
1167 || 'CREATED_BY,'
1168 || 'LAST_UPDATE_LOGIN,'
1169 || 'RECORD_VERSION_NUMBER )' ;
1170
1171 l_SELECT_clause := 'SELECT '
1172 || ':p_struct_version_id,'
1173 || 'sequence ' || ' ,'
1174 || ':p_struct_version_id,'
1175 || 1 || ' ,'
1176 || ':x_rbs_element_name_id,'
1177 || 1 || ' ,'
1178 || 1 || ' ,'
1179 || -1 || ' ,'
1180 || '''' || 'N'|| '''' || ' ,'
1181 || 1 || ' ,'
1182 || '''' || 'N' || '''' ||' ,'
1183 || '''' || sysdate || '''' || ' ,'
1184 || ':g_user_id,'
1185 || '''' || sysdate || '''' || ' ,'
1186 || ':g_user_id,'
1187 || ':g_login_id,'
1188 || 1 ;
1189
1190 l_tmp := 3+p_max_level ;
1191 l_FROM_clause := ' FROM pa_rbs_map_tmp' || l_tmp ;
1192 l_SQL_statement := l_INSERT_clause || ' ' ||
1193 l_SELECT_clause || ' ' ||
1194 l_FROM_clause || ' ;' ;
1195
1196
1197 EXECUTE IMMEDIATE 'BEGIN ' ||l_SQL_statement || ' END;' USING p_struct_version_id, x_rbs_element_name_id, g_user_id, g_login_id ;
1198
1199
1200 g_denorm_refresh := 'Y';
1201
1202
1203
1204 IF g_debug_mode = 'Y' THEN
1205 pa_debug.g_err_stage:= 'Exiting create_top_rbs_element- Inserts in pa_rbs_elements :'||SQL%ROWCOUNT ;
1206 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1207 pa_debug.reset_curr_function;
1208 END IF;
1209
1210
1211 END ;
1212
1213 ------------------------------------------------------
1214 --create rbs elements inserting into pa_rbs_elements
1215 ------------------------------------------------------
1216
1217 PROCEDURE create_rbs_element
1218 (
1219 p_rule_id IN NUMBER,
1220 p_struct_version_id IN NUMBER,
1221 p_level IN NUMBER,
1222 p_max_level IN NUMBER --,
1223 --x_return_status OUT NOCOPY VARCHAR2,
1224 --x_msg_count OUT NOCOPY NUMBER,
1225 --x_msg_data OUT NOCOPY VARCHAR2
1226 )
1227 IS
1228 l_SQL_statement VARCHAR2 (2000);
1229 l_INSERT_clause VARCHAR2 (500);
1230 l_SELECT_clause VARCHAR2 (500);
1231 l_FROM_clause VARCHAR2 (500);
1232 l_WHERE_clause VARCHAR2 (500);
1233 l_max_level NUMBER;
1234 l_tmp1 NUMBER;
1235 l_tmp2 NUMBER;
1236 l_rbs_element_id NUMBER;
1237
1238 x_resource_source_id NUMBER := NULL;
1239 x_resource_type_id NUMBER := NULL;
1240 x_rbs_element_name_id NUMBER;
1241 x_return_status Varchar2(30);
1242 x_msg_count NUMBER;
1243 x_error_msg_data Varchar2(500);
1244 BEGIN
1245 IF g_debug_mode = 'Y' THEN
1246 PA_DEBUG.set_curr_function( p_function => 'create_rbs_element'
1247 ,p_debug_mode => g_debug_mode );
1248 pa_debug.g_err_stage:= 'Inside create_rbs_element';
1249 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1250 END IF;
1251
1252 -- to get element_names start here
1253 delete pa_rbs_elem_in_temp;
1254
1255 l_INSERT_clause := 'INSERT INTO pa_rbs_elem_in_temp ( '
1256 || 'resource_type_id,'
1257 || 'resource_source_id ) ' ;
1258 l_SELECT_clause := 'SELECT distinct '
1259 || get_sql_clause(p_rule_id,p_level,'RES_TYP_ID') || ' ,'
1260 || get_sql_clause(p_rule_id,p_level,'RES_SOURCE') ;
1261
1262 l_tmp1 := 4+p_max_level-p_level;
1263 l_tmp2 := 5+p_max_level-p_level;
1264
1265 l_FROM_clause := ' FROM '
1266 || 'pa_rbs_map_tmp' || l_tmp1 || ' TMP, '
1267 || 'pa_rbs_map_tmp' || l_tmp2 || ' TMP1 ' ;
1268
1269 l_WHERE_clause := ' WHERE '
1270 || get_sql_clause(p_rule_id,p_level-1,'EQUAL2')
1271 || ' AND TMP.struct_version_id = TMP1.struct_version_id (+)' ;
1272
1273
1274 l_SQL_statement := l_INSERT_clause || ' ' ||
1275 l_SELECT_clause || ' ' ||
1276 l_FROM_clause || ' ' ||
1277 l_WHERE_clause || ';' ;
1278
1279
1280
1281 EXECUTE IMMEDIATE 'BEGIN ' ||l_SQL_statement || ' END;';
1282
1283 PA_RBS_UTILS.Populate_RBS_Element_Name(x_resource_source_id,x_resource_type_id,x_rbs_element_name_id,x_return_status);
1284
1285
1286 l_INSERT_clause := 'INSERT INTO pa_rbs_elements ( '
1287 || 'rbs_version_id,'
1288 || 'rbs_element_id,'
1289 || 'parent_element_id,'
1290 || 'rbs_level,'
1291 || get_sql_clause(p_rule_id,p_level,'NONE') || ' ,'
1292 || 'rbs_element_name_id,'
1293 || 'outline_number,'
1294 || 'order_number,'
1295 || 'resource_type_id,'
1296 || 'resource_source_id,'
1297 || 'rule_flag,'
1298 || 'element_identifier,'
1299 || 'user_created_flag,'
1300 || 'last_update_date,'
1301 || 'LAST_UPDATED_BY,'
1302 || 'CREATION_DATE,'
1303 || 'CREATED_BY,'
1304 || 'LAST_UPDATE_LOGIN,'
1305 || 'RECORD_VERSION_NUMBER )' ;
1306
1307 l_SELECT_clause := 'SELECT '
1308 || ':p_struct_version_id,'
1309 || 'TMP.sequence , '
1310 || ' nvl(TMP.parent_element_version_id ,TMP1.sequence ), '
1311 || ':p_level,'
1312 || get_sql_clause(p_rule_id,p_level,'TMP') || ' ,'
1313 || 'name.rbs_element_name_id , '
1314 || 'RBS.outline_number , ' --bug#3974663
1315 || 'RBS.order_number , ' --bug#3974663
1316 || get_sql_clause(p_rule_id,p_level,'RES_TYP_ID') || ' ,'
1317 || get_sql_clause(p_rule_id,p_level,'RES_SOURCE') || ' ,'
1318 || '''' || 'N' || '''' || ' ,'
1319 || 1 || ' ,'
1320 || '''' || 'N' || '''' ||' ,'
1321 || '''' || sysdate || '''' || ' ,'
1322 || ':g_user_id,'
1323 || '''' || sysdate || '''' || ' ,'
1324 || ':g_user_id,'
1325 || ':g_login_id,'
1326 || 1 ;
1327
1328 l_tmp1 := 4+p_max_level-p_level;
1329 l_tmp2 := 5+p_max_level-p_level;
1330 l_rbs_element_id := get_rbs_element_id(p_rule_id,p_level);
1331
1332 l_FROM_clause := ' FROM '
1333 || 'pa_rbs_map_tmp' || l_tmp1 || ' TMP, '
1334 || 'pa_rbs_map_tmp' || l_tmp2 || ' TMP1, '
1335 || 'pa_rbs_element_names_b name, '
1336 || 'pa_rbs_elements RBS ' ; --bug#3974663
1337 l_WHERE_clause := ' WHERE '
1338 || get_sql_clause_rule(p_rule_id,p_level-1,'EQUAL2') --bug#3759977
1339 || ' AND TMP.resource_type_id = name.resource_type_id '
1340 || ' AND TMP.resource_source_id = name.resource_source_id '
1341 || ' AND TMP.struct_version_id = TMP1.struct_version_id (+)'
1342 || ' AND RBS.rbs_element_id = :l_rbs_element_id'; --bug#3974663
1343
1344
1345 l_SQL_statement := l_INSERT_clause || ' ' ||
1346 l_SELECT_clause || ' ' ||
1347 l_FROM_clause || ' ' ||
1348 l_WHERE_clause || ';' ;
1349
1350
1351 EXECUTE IMMEDIATE 'BEGIN ' ||l_SQL_statement || ' END;' USING p_struct_version_id, p_level, g_user_id, g_login_id, l_rbs_element_id;
1352
1353 g_denorm_refresh := 'Y';
1354
1355 IF g_debug_mode = 'Y' THEN
1356 pa_debug.g_err_stage:= 'Exiting create_rbs_element- Inserts in pa_rbs_elements :'||SQL%ROWCOUNT ;
1357 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1358 pa_debug.reset_curr_function;
1359 END IF;
1360
1361
1362
1363 END ;
1364
1365 ------------------------------------------------------
1366 --create mapping inserting into pa_rbs_txn_accum_map
1367 ------------------------------------------------------
1368
1369 PROCEDURE populate_txn_map
1370 (
1371 p_rule_id IN NUMBER,
1372 p_struct_version_id IN NUMBER,
1373 p_max_level IN NUMBER --,
1374 --x_return_status OUT NOCOPY VARCHAR2,
1375 --x_msg_count OUT NOCOPY NUMBER,
1376 --x_msg_data OUT NOCOPY VARCHAR2
1377 )
1378 IS
1379 l_SQL_statement1 VARCHAR2 (2000);
1380 l_SQL_statement2 VARCHAR2 (2000);
1381 l_SQL_statement VARCHAR2 (2000);
1382 l_INSERT_clause VARCHAR2 (500);
1383 l_SELECT_clause VARCHAR2 (500);
1384 l_FROM_clause VARCHAR2 (500);
1385 l_WHERE_clause VARCHAR2 (1000); --bug#3888373 increased size from 500 to 1000
1386 l_max_level NUMBER;
1387 BEGIN
1388 IF g_debug_mode = 'Y' THEN
1389 PA_DEBUG.set_curr_function( p_function => 'populate_txn_map'
1390 ,p_debug_mode => g_debug_mode );
1391 pa_debug.g_err_stage:= 'Inside populate_txn_map- p_struct_version_id :'|| p_struct_version_id || ' p_max_level: ' || p_max_level || ' p_rule_id:'||p_rule_id;
1392 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1393 END IF;
1394
1395 l_INSERT_clause := 'INSERT INTO pa_rbs_txn_accum_map ('
1396 || 'txn_accum_header_id,'
1397 || 'element_id,'
1398 || 'struct_version_id,'
1399 || 'last_update_date,'
1400 || 'LAST_UPDATED_BY,'
1401 || 'CREATION_DATE,'
1402 || 'CREATED_BY,'
1403 || 'LAST_UPDATE_LOGIN'
1404 || ' )' ;
1405 l_SELECT_clause := ' SELECT '
1406 || 'TMP.txn_accum_header_id,'
1407 || 'RBS.rbs_element_id,'
1408 || ':p_struct_version_id,'
1409 || '''' || sysdate || '''' || ' ,'
1410 || ':g_user_id,'
1411 || '''' || sysdate || '''' || ' ,'
1412 || ':g_user_id,'
1413 || ':g_login_id ';
1414 l_FROM_clause := ' FROM '
1415 || 'pa_rbs_map_tmp3 TMP, '
1416 || 'pa_rbs_elements RBS' ;
1417 l_WHERE_clause := ' WHERE '
1418 || get_sql_clause(p_rule_id,p_max_level,'EQUAL') || ' AND '
1419 || 'TMP.element_version_id IS NULL '
1420 || ' AND RBS.user_created_flag = ' || '''' || 'N' || ''''
1421 || 'AND RBS.rbs_version_id = :p_struct_version_id '
1422 || 'AND RBS.rbs_level = :p_max_level';
1423
1424 l_SQL_statement1 := l_SELECT_clause || ' ' ||
1425 l_FROM_clause || ' ' ||
1426 l_WHERE_clause ;
1427
1428 l_SELECT_clause := ' SELECT '
1429 || 'TMP.txn_accum_header_id,'
1430 || 'TMP.element_version_id,'
1431 || ':p_struct_version_id,'
1432 || '''' || sysdate || '''' || ' ,'
1433 || ':g_user_id,'
1434 || '''' || sysdate || '''' || ' ,'
1435 || ':g_user_id,'
1436 || ':g_login_id';
1437
1438 l_FROM_clause := ' FROM pa_rbs_map_tmp3 TMP ' ;
1439 l_WHERE_clause := 'WHERE TMP.element_version_id IS NOT NULL ' ;
1440
1441 l_SQL_statement2 := l_SELECT_clause || ' ' ||
1442 l_FROM_clause || ' ' ||
1443 l_WHERE_clause ;
1444
1445 l_SQL_statement := l_INSERT_clause || ' ' ||
1446 l_SQL_statement1 ||
1447 ' UNION ALL ' ||
1448 l_SQL_statement2 || ';' ;
1449
1450 EXECUTE IMMEDIATE 'BEGIN ' ||l_SQL_statement || ' END;'
1451 USING p_struct_version_id, g_user_id, g_login_id, p_max_level;
1452
1453
1454
1455 IF g_debug_mode = 'Y' THEN
1456 pa_debug.g_err_stage:= 'Exiting populate_txn_map- Inserts in Txn_accum_map :'||SQL%ROWCOUNT ;
1457 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1458 pa_debug.reset_curr_function;
1459 END IF;
1460
1461 END ;
1462
1463 -- procedure added for custom nodes
1464 PROCEDURE populate_custom_columns
1465 (
1466 p_rule_id IN NUMBER
1467 )
1468 IS
1469
1470 --bug#3995697 l_max_level NUMBER;
1471 l_token VARCHAR2(30);
1472 l_res_type_cols PA_PLSQL_DATATYPES.Char30TabTyp;
1473 j NUMBER;
1474
1475 --bug#3995697 l_rbs_element_id NUMBER;
1476 l_sql_cols VARCHAR2(200);
1477 BEGIN
1478 IF g_debug_mode = 'Y' THEN
1479 PA_DEBUG.set_curr_function( p_function => 'populate_custom_columns'
1480 ,p_debug_mode => g_debug_mode );
1481 pa_debug.g_err_stage:= 'Inside populate_custom_columns';
1482 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1483 END IF;
1484
1485 j := 1;
1486 FOR i IN 2..g_max_level LOOP
1487
1488 l_token:= get_level(p_rule_id,i);
1489
1490 --EXECUTE IMMEDIATE 'SELECT LEVEL' || i || ' FROM pa_rbs_mapping_rules WHERE rule_id = :p_rule_id' INTO l_token USING p_rule_id ;
1491 IF substr(l_token,1,1) = 'C' THEN
1492 l_res_type_cols(j) := get_resource_type_cols(substr(l_token,3));
1493 j := j+1;
1494 END IF;
1495 END LOOP;
1496
1497 IF l_res_type_cols.count > 0 THEN
1498 FOR i in 1..l_res_type_cols.count LOOP
1499 IF i = l_res_type_cols.count THEN
1500 l_sql_cols := l_sql_cols || l_res_type_cols(i) ; --bug#3878303
1501 ELSE
1502 l_sql_cols := l_sql_cols || l_res_type_cols(i) || ' , ';
1503 END IF;
1504 END LOOP;
1505
1506 EXECUTE IMMEDIATE 'UPDATE pa_rbs_map_tmp2 SET ( ' || l_sql_cols || ' ) = ( SELECT ' || l_sql_cols || ' FROM PA_RBS_ELEMENTS where rbs_element_id = :g_rbs_element_id) ' USING g_rbs_element_id;
1507
1508 END IF;
1509
1510 IF g_debug_mode = 'Y' THEN
1511 pa_debug.g_err_stage:= 'Exiting populate_custom_columns - Columns Inserted :'|| l_sql_cols;
1512 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1513 pa_debug.reset_curr_function;
1514 END IF;
1515
1516 END ;
1517
1518
1519 PROCEDURE process_top_rule
1520 (
1521 p_struct_version_id IN NUMBER,
1522 p_res_class_id IN NUMBER --,
1523 --x_return_status OUT NOCOPY VARCHAR2,
1524 --x_msg_count OUT NOCOPY NUMBER,
1525 --x_msg_data OUT NOCOPY VARCHAR2
1526 )
1527 IS
1528 l_SQL_statement VARCHAR2 (2000);
1529 l_count NUMBER;
1530 l_sequence NUMBER;
1531
1532 l_txn_header_id PA_PLSQL_DATATYPES.Char30TabTyp;
1533
1534 x_rbs_element_name_id NUMBER;
1535 x_return_status VARCHAR2(30);
1536 BEGIN
1537 IF g_debug_mode = 'Y' THEN
1538 PA_DEBUG.set_curr_function( p_function => 'process_top_rule'
1539 ,p_debug_mode => g_debug_mode );
1540 pa_debug.g_err_stage:= 'Inside process_top_rule';
1541 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1542 END IF;
1543
1544 SELECT distinct txn_accum_header_id
1545 bulk collect into l_txn_header_id
1546 from pa_rbs_map_tmp2
1547 where resource_class_id = p_res_class_id
1548 and txn_accum_header_id not in (SELECT txn_accum_header_id FROM
1549 pa_rbs_txn_accum_map
1550 WHERE struct_version_id = p_struct_version_id ) ;
1551
1552
1553 IF SQL%ROWCOUNT>0 THEN
1554 select count(*) into l_count from pa_rbs_elements where
1555 rbs_level = 1 and
1556 rbs_version_id = p_struct_version_id and
1557 user_created_flag = 'N' ;
1558
1559
1560 IF l_count = 0 THEN
1561
1562 SELECT pa_rbs_elements_s.nextval INTO l_sequence FROM dual ;
1563 PA_RBS_UTILS.Populate_RBS_Element_Name(p_struct_version_id, -1, x_rbs_element_name_id, x_return_status);
1564
1565 INSERT INTO pa_rbs_elements (
1566 rbs_version_id,
1567 rbs_element_id,
1568 rbs_level,
1569 rbs_element_name_id,
1570 outline_number,
1571 order_number,
1572 resource_type_id,
1573 rule_flag,
1574 element_identifier,
1575 user_created_flag,
1576 last_update_date,
1577 LAST_UPDATED_BY,
1578 CREATION_DATE,
1579 CREATED_BY,
1580 LAST_UPDATE_LOGIN,
1581 RECORD_VERSION_NUMBER )
1582 VALUES (
1583 p_struct_version_id ,
1584 l_sequence ,
1585 1 ,
1586 x_rbs_element_name_id ,
1587 1 ,
1588 1 ,
1589 -1,
1590 'N',
1591 1 ,
1592 'N' ,
1593 sysdate,
1594 g_user_id ,
1595 sysdate ,
1596 g_user_id ,
1597 g_login_id ,
1598 1 ) ;
1599
1600 g_denorm_refresh := 'Y';
1601
1602
1603 ELSIF l_count = 1 THEN
1604
1605 select rbs_element_id into l_sequence from pa_rbs_elements where rbs_level = 1 and
1606 rbs_version_id = p_struct_version_id and
1607 user_created_flag = 'N' ;
1608
1609
1610
1611 END IF;
1612
1613 FORALL i IN 1..l_txn_header_id.count
1614 INSERT INTO pa_rbs_txn_accum_map (
1615 txn_accum_header_id,
1616 element_id,
1617 struct_version_id,
1618 last_update_date,
1619 LAST_UPDATED_BY,
1620 CREATION_DATE,
1621 CREATED_BY,
1622 LAST_UPDATE_LOGIN
1623 )
1624 VALUES (l_txn_header_id(i),
1625 l_sequence,
1626 p_struct_version_id,
1627 sysdate,
1628 g_user_id ,
1629 sysdate ,
1630 g_user_id,
1631 g_login_id);
1632 END IF;
1633
1634 IF g_debug_mode = 'Y' THEN
1635 pa_debug.g_err_stage:= 'Exiting process_top_rule' ;
1636 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1637 pa_debug.reset_curr_function;
1638 END IF;
1639 END;
1640
1641 ---------------------------------------------------
1642 --procedure for creating mapping of transaction
1643 --having resource type attributes
1644 ---------------------------------------------------
1645 PROCEDURE map_rbs_txn
1646 (
1647 p_rbs_struct_version_id NUMBER ,
1648 x_return_status OUT NOCOPY VARCHAR2,
1649 x_msg_count OUT NOCOPY NUMBER,
1650 x_msg_data OUT NOCOPY VARCHAR2
1651 )
1652 IS
1653 l_res_class_id SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1654 l_sorted_rule_id SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1655 --bug#3995697 l_rule_type VARCHAR2(1);
1656 --bug#3995697 l_max_level number;
1657 l_sql_stmt VARCHAR2(500);
1658 l_leaf_type VARCHAR2(30);
1659
1660 l_return_status VARCHAR2(15);
1661 l_msg_count NUMBER ;
1662 l_msg_data VARCHAR2(500);
1663
1664 l_top_created number;
1665 l_tmp number;
1666
1667 no_rule_excp exception;
1668
1669 can_not_lock_rbs_version exception; --added for bug#4101364
1670 lockhndl varchar2(128); --added for bug#4101364
1671 l number; --added for bug#4101364
1672
1673 BEGIN
1674
1675 x_return_status := FND_API.G_RET_STS_SUCCESS;
1676
1677 IF g_debug_mode = 'Y' THEN
1678 PA_DEBUG.set_curr_function( p_function => 'map_rbs_txn'
1679 ,p_debug_mode => g_debug_mode );
1680 pa_debug.g_err_stage:= 'Inside map_rbs_txn- p_rbs_struct_version_id :'|| p_rbs_struct_version_id;
1681 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1682 END IF;
1683
1684
1685 /* added for bug#4101364 */
1686 lockhndl := auto_allocate_unique('RBS_VERSION_'||p_rbs_struct_version_id);
1687 l := dbms_lock.request(lockhndl, 6, 864000, TRUE) ;
1688 IF l <>0 AND l <> 4 THEN -- 4 has been added to make it compatible within same session
1689 raise can_not_lock_rbs_version;
1690 END IF;
1691
1692 l_top_created := 0;
1693
1694 SELECT resource_class_id
1695 BULK COLLECT
1696 INTO l_res_class_id
1697 FROM pa_resource_classes_b;
1698
1699
1700 FOR j IN 1..l_res_class_id.COUNT LOOP
1701
1702
1703 l_sorted_rule_id := get_sorted_rules(p_rbs_struct_version_id,l_res_class_id(j));
1704
1705 -- if no rules are present then no processing should happen
1706 IF l_sorted_rule_id.COUNT > 0 THEN
1707
1708 FOR k IN 1..l_sorted_rule_id.COUNT-1 LOOP
1709 SELECT rule_flag, max_level, rbs_element_id
1710 INTO g_rule_type, g_max_level, g_rbs_element_id
1711 FROM pa_rbs_mapping_rules
1712 WHERE rule_id = l_sorted_rule_id(k);
1713
1714 -- checking for wheteher leaf node of the rule is custom then processing for the rule would be ignored
1715 l_sql_stmt := 'SELECT level' || g_max_level ||
1716 ' FROM pa_rbs_mapping_rules ' ||
1717 ' WHERE rule_id = :rule_id' ;
1718
1719 l_leaf_type:= get_level(l_sorted_rule_id(k),g_max_level);
1720
1721 --EXECUTE IMMEDIATE l_sql_stmt INTO l_leaf_type USING l_sorted_rule_id(k) ;
1722
1723 IF substr(l_leaf_type,1,1) <> 'C' THEN -- skip the rule which ends with custom node
1724
1725 --added for custom nodes
1726 populate_custom_columns(l_sorted_rule_id(k));
1727 mapped_header(l_sorted_rule_id(k),p_rbs_struct_version_id, g_max_level,l_res_class_id(j),l_return_status,l_msg_count,l_msg_data);
1728 IF l_return_status = 1 THEN
1729 goto next_rule;
1730 END IF;
1731 FOR l IN 1..g_max_level-1 LOOP
1732 unmapped_header(l_sorted_rule_id(k), p_rbs_struct_version_id,l,g_max_level - l + 1);
1733 END LOOP;
1734
1735 IF l_top_created = 0 THEN
1736
1737 unmapped_header_laststep(l_sorted_rule_id(k), p_rbs_struct_version_id,g_max_level);
1738 create_top_rbs_element(l_sorted_rule_id(k), p_rbs_struct_version_id,g_max_level);
1739 l_top_created := 1;
1740
1741 END IF;
1742
1743 FOR l IN 2..g_max_level LOOP
1744 create_rbs_element(l_sorted_rule_id(k), p_rbs_struct_version_id,l,g_max_level);
1745 END LOOP;
1746
1747 populate_txn_map(l_sorted_rule_id(k), p_rbs_struct_version_id,g_max_level);
1748
1749 END IF;
1750
1751 -- delete from all temp tables
1752 delete_tmp_tables(g_max_level);
1753
1754 /* FOR l IN 0..g_max_level LOOP
1755 l_tmp := 3+l;
1756 EXECUTE IMMEDIATE 'DELETE pa_rbs_map_tmp' || l_tmp;
1757 END LOOP; */
1758
1759 <<next_rule>>
1760 null;
1761
1762 END LOOP; -- done with all the rules but one i.e. SELF rule
1763
1764 -- process for SELF rule
1765
1766 process_top_rule(p_rbs_struct_version_id,l_res_class_id(j));
1767
1768 ELSE --no rules are present
1769 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1770 x_msg_count := 1;
1771 x_msg_data := 'Rules for RBS version is missing';
1772 raise no_rule_excp;
1773 END IF;
1774
1775 END LOOP;
1776
1777 IF g_debug_mode = 'Y' THEN
1778 pa_debug.g_err_stage:= 'Exiting map_rbs_txn' ;
1779 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1780 pa_debug.reset_curr_function;
1781 END IF;
1782
1783 EXCEPTION WHEN OTHERS THEN /* Added the exception block for bug #6377425 */
1784
1785 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1786 x_msg_count := 1;
1787 x_msg_data := 'pa_rbs_mapping.map_rbs_actuals.' || NVL(l_msg_data, SQLERRM);
1788
1789 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => 'PA_RBS_MAPPING'
1790 ,p_procedure_name => 'map_rbs_txn');
1791
1792 IF g_debug_mode = 'Y' THEN
1793 Pa_Debug.g_err_stage:='Unexpected Error inside pa_rbs_mapping.map_rbs_actuals for Structure Version ='||p_rbs_struct_version_id||' '|| NVL(l_msg_data,SQLERRM) ;
1794 Pa_Debug.WRITE(g_module_name, Pa_Debug.g_err_stage,5);
1795 Pa_Debug.reset_curr_function;
1796 END IF;
1797
1798 RAISE;
1799
1800 END;
1801
1802
1803 ---------------------------------------------------
1804 --procedure for creating mapping of actual transactions
1805 ---------------------------------------------------
1806 PROCEDURE map_rbs_actuals
1807 (
1808 p_worker_id IN NUMBER DEFAULT NULL,
1809 x_return_status OUT NOCOPY VARCHAR2,
1810 x_msg_count OUT NOCOPY NUMBER,
1811 x_msg_data OUT NOCOPY VARCHAR2
1812 )
1813 IS
1814 l_status NUMBER := 0;
1815 l_rbs_struct_version_id SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1816
1817 l_revenue_category PA_PLSQL_DATATYPES.Char30TabTyp;
1818 l_res_id NUMBER;
1819
1820 l_return_status VARCHAR2(15);
1821 l_msg_count NUMBER ;
1822 l_msg_data VARCHAR2(500);
1823
1824 l_worker_id NUMBER;
1825
1826 l_job_group_id NUMBER ; --added for bug#4027727
1827
1828 l_process VARCHAR2(30);
1829 l_extraction_type VARCHAR2(30);
1830 l_out number;
1831
1832 --AECOM Change START bug 6739719
1833
1834 l_rbs_assoc_flag VARCHAR2(1) := 'Y' ;
1835 l_rbs_prg_flag VARCHAR2(15) := 'Y' ;
1836
1837 --AECOM Change END
1838
1839 BEGIN
1840
1841 x_return_status := FND_API.G_RET_STS_SUCCESS;
1842
1843
1844 l_process := Pji_Pjp_Sum_Main.g_process || TO_CHAR(p_worker_id);
1845 l_extraction_type := Pji_Process_Util.GET_PROCESS_PARAMETER(l_process, 'EXTRACTION_TYPE');
1846 Pji_Pjp_Fp_Curr_Wrap.print_time( ' l_process = ' || l_process );
1847 Pji_Pjp_Fp_Curr_Wrap.print_time( ' l_extraction_type = ' || l_extraction_type );
1848
1849 IF g_debug_mode = 'Y' THEN
1850 PA_DEBUG.set_curr_function( p_function => 'map_rbs_actuals'
1851 ,p_debug_mode => g_debug_mode );
1852 pa_debug.g_err_stage:= 'Inside map_rbs_actuals : extrn type = ' || l_extraction_type ;
1853 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
1854 END IF;
1855
1856 DELETE pa_rbs_map_tmp1 ;
1857
1858 --AECOM Change START bug 6739719
1859
1860 BEGIN
1861
1862 SELECT 'Y'
1863 INTO l_rbs_assoc_flag
1864 FROM dual
1865 WHERE
1866 EXISTS (
1867 SELECT event_type
1868 FROM pji_pa_proj_events_log
1869 WHERE event_type = 'RBS_ASSOC' )
1870 OR EXISTS (
1871 SELECT event_type
1872 FROM pa_pji_proj_events_log
1873 WHERE event_type = 'RBS_ASSOC' ) ;
1874
1875
1876 EXCEPTION
1877
1878 WHEN NO_DATA_FOUND THEN
1879 l_rbs_assoc_flag := 'N' ;
1880 WHEN OTHERS THEN
1881 raise;
1882
1883 END;
1884
1885
1886 BEGIN
1887
1888 SELECT 'Y'
1889 INTO l_rbs_prg_flag
1890 FROM dual
1891 WHERE
1892 EXISTS (
1893 SELECT event_type
1894 FROM pji_pa_proj_events_log
1895 WHERE event_type = 'RBS_PRG' )
1896 OR EXISTS (
1897 SELECT event_type
1898 FROM pa_pji_proj_events_log
1899 WHERE event_type = 'RBS_PRG' ) ;
1900
1901 EXCEPTION
1902
1903 WHEN NO_DATA_FOUND THEN
1904 l_rbs_prg_flag := 'N' ;
1905 WHEN OTHERS THEN
1906 raise;
1907
1908 END;
1909
1910 --AECOM Change END
1911
1912 /* bug#3651414 added join with rbs version and rbs header tables
1913 bug#3826585 added lookup to FIN7 and txn accum for project and txn accum header*/
1914
1915 INSERT INTO pa_rbs_map_tmp1
1916 (txn_accum_header_id,
1917 struct_version_id)
1918 SELECT /*+ ORDERED */ DISTINCT det.txn_accum_header_id, -- Bug#5578221 Performance Fix
1919 ASSIGN.rbs_version_id
1920 FROM (SELECT DISTINCT
1921 project_id,
1922 txn_accum_header_id
1923 FROM
1924 (
1925 --SELECT /*+ ORDERED index(fin7 pji_fm_aggr_fin7_n1) */
1926 SELECT /*+ ORDERED USE_NL(map, fin7) index(fin7 pji_fm_aggr_fin7_n1) */ --AECOM added ordered and use_nl bug 6739719
1927 fin7.project_id,
1928 txn_accum_header_id
1929 FROM
1930 pji_pjp_proj_batch_map map, -- Bug#5223360 - Peformance Fix
1931 pji_fm_aggr_fin7 fin7
1932 WHERE fin7.project_id = map.project_id
1933 AND map.worker_id = p_worker_id
1934 AND fin7.recvr_period_type='GL'
1935 UNION ALL
1936 --SELECT /*+ ORDERED index(accum pji_fp_txn_accum_n2)*/
1937 SELECT /*+ ORDERED USE_NL(map, accum) index(accum pji_fp_txn_accum_n2)*/ --AECOM added ordered and use_nl bug 6739719
1938 accum.project_id,
1939 txn_accum_header_id
1940 FROM
1941 pji_pjp_proj_batch_map map, -- Bug#5223360 - Peformance Fix
1942 pji_fp_txn_accum accum
1943 WHERE accum.project_id = map.project_id
1944 AND map.worker_id = p_worker_id
1945 AND accum.recvr_period_type='GL'
1946 UNION ALL
1947 --SELECT /*+ ORDERED index(accum1 pji_fp_txn_accum1_n1)*/
1948 SELECT /*+ ORDERED USE_NL(map, accum1) index(accum1 pji_fp_txn_accum1_n1)*/ --AECOM added ordered and use_nl bug 6739719
1949 accum1.project_id,
1950 txn_accum_header_id
1951 FROM
1952 pji_pjp_proj_batch_map map, -- Bug#5223360 - Peformance Fix
1953 pji_fp_txn_accum1 accum1
1954 WHERE accum1.project_id = map.project_id
1955 AND map.worker_id = p_worker_id
1956 UNION ALL
1957 SELECT /*+ORDERED*/DISTINCT
1958 ra.project_id,
1959 ra.txn_accum_header_id
1960 FROM
1961 pji_pjp_proj_batch_map map, -- Bug#5223360 - Peformance Fix
1962 pji_pjp_wbs_header wbs,
1963 pa_resource_assignments ra ,
1964 ( SELECT worker_id
1965 , event_type
1966 , attribute1
1967 FROM pji_pa_proj_events_log
1968 UNION ALL
1969 SELECT p_worker_id
1970 , event_type
1971 , attribute1
1972 FROM pa_pji_proj_events_log
1973 )LOG
1974 WHERE
1975 l_rbs_assoc_flag = 'Y' and --AECOM Change bug 6739719
1976 map.worker_id = p_worker_id and
1977 map.project_id = wbs.project_id and
1978 LOG.event_type = 'RBS_ASSOC' AND
1979 TO_NUMBER(LOG.attribute1) = wbs.project_id AND
1980 wbs.plan_version_id = ra.budget_version_id AND
1981 wbs.project_id = ra.project_id AND
1982 wbs.wp_flag = 'N' AND
1983 ra.txn_accum_header_id IS NOT NULL AND
1984 LOG.worker_id = p_worker_id
1985 UNION ALL
1986 SELECT /*+ ORDERED index(den PJI_XBS_DENORM_N1)*/ -- Bug#5578221 - Peformance Fix
1987 DISTINCT
1988 ra.project_id,
1989 ra.txn_accum_header_id
1990 FROM
1991 pji_pjp_proj_batch_map map,
1992 pji_pjp_wbs_header hd2, -- sub
1993 pa_resource_assignments ra,
1994 ( SELECT worker_id
1995 , event_type
1996 , attribute1
1997 FROM pji_pa_proj_events_log
1998 UNION ALL
1999 SELECT p_worker_id
2000 , event_type
2001 , attribute1
2002 FROM pa_pji_proj_events_log
2003 )LOG,
2004 pji_xbs_denorm den
2005 WHERE
2006 l_rbs_prg_flag = 'Y' and --AECOM Change bug 6739719
2007 map.worker_id = p_worker_id and
2008 map.project_id = hd2.project_id and
2009 LOG.event_type = 'RBS_PRG' AND
2010 ra.txn_accum_header_id IS NOT NULL AND
2011 LOG.worker_id = p_worker_id AND
2012 den.struct_version_id IS NULL AND
2013 TO_NUMBER(LOG.attribute1) = den.sup_project_id AND
2014 hd2.wbs_version_id = den.sub_id AND
2015 den.struct_type = 'PRG' AND
2016 hd2.wp_flag = 'N' AND
2017 hd2.plan_version_id = ra.budget_version_id AND
2018 hd2.project_id = ra.project_id )
2019 ) det,
2020 pa_rbs_prj_assignments ASSIGN,
2021 pa_rbs_versions_b rbsv,
2022 pa_rbs_headers_b rbsh
2023 WHERE
2024 det.project_id = ASSIGN.project_id AND
2025 det.txn_accum_header_id NOT IN
2026 (SELECT txn_accum_header_id FROM pa_rbs_txn_accum_map
2027 WHERE struct_version_id = ASSIGN.rbs_version_id ) AND
2028 ASSIGN.reporting_usage_flag = 'Y' AND
2029 ASSIGN.rbs_version_id = rbsv.rbs_version_id AND
2030 rbsv.current_reporting_flag = 'Y' AND
2031 rbsv.rbs_header_id = rbsh.rbs_header_id AND
2032 SYSDATE BETWEEN rbsh.effective_from_date AND
2033 NVL(rbsh.effective_to_date,SYSDATE);
2034
2035
2036 IF (l_extraction_type = 'RBS') THEN
2037
2038 INSERT INTO pa_rbs_map_tmp1
2039 ( txn_accum_header_id
2040 , struct_version_id)
2041 SELECT DISTINCT
2042 ra.txn_accum_header_id
2043 , rbv2.rbs_version_id
2044 FROM pji_pa_proj_events_log LOG
2045 , pa_rbs_prj_assignments asg
2046 , pa_rbs_versions_b rbv1
2047 , pa_rbs_versions_b rbv2
2048 , pa_budget_versions bv
2049 , pa_resource_assignments ra
2050 WHERE
2051 LOG.event_type = 'RBS_PUSH' AND
2052 LOG.worker_id = p_worker_id AND
2053 asg.rbs_version_id = TO_NUMBER(LOG.attribute2) AND
2054 asg.rbs_version_id = rbv1.rbs_version_id AND
2055 rbv1.rbs_header_id = rbv2.rbs_header_id AND
2056 rbv2.current_reporting_flag = 'Y' AND
2057 asg.project_id = bv.project_id AND
2058 bv.budget_version_id = ra.budget_version_id AND
2059 NVL(bv.wp_version_flag, 'N')= 'N' AND
2060 bv.budget_status_code = 'B' AND
2061 ra.txn_accum_header_id IS NOT NULL AND
2062 ra.txn_accum_header_id NOT IN
2063 (
2064 SELECT txn_accum_header_id
2065 FROM pa_rbs_txn_accum_map
2066 WHERE struct_version_id = rbv2.rbs_version_id
2067 );
2068
2069 Pji_Pjp_Fp_Curr_Wrap.print_time( ' # rows inserted = ' || SQL%ROWCOUNT );
2070
2071 END IF;
2072
2073
2074 SELECT distinct struct_version_id
2075 BULK COLLECT
2076 INTO l_rbs_struct_version_id
2077 FROM pa_rbs_map_tmp1 ;
2078
2079 create_res_type_numeric_id('EMP',11,l_res_id,l_return_status,l_msg_data);
2080 create_res_type_numeric_id('CWK',11,l_res_id,l_return_status,l_msg_data);
2081
2082 l_worker_id := pji_pjp_extraction_utils.get_worker_id;
2083
2084 FOR i IN 1..l_rbs_struct_version_id.COUNT LOOP
2085
2086 SAVEPOINT map_rbs_txn;
2087
2088 SELECT distinct head.revenue_category
2089 BULK COLLECT INTO l_revenue_category
2090 FROM pa_rbs_map_tmp1 tmp, pji_fp_txn_accum_header head
2091 WHERE tmp.struct_version_id = l_rbs_struct_version_id(i)
2092 AND tmp.txn_accum_header_id = head.txn_accum_header_id
2093 AND head.revenue_category IS NOT NULL
2094 AND head.revenue_category NOT IN
2095 (select resource_name from pa_rbs_element_map where
2096 resource_type_id = 14);
2097
2098 FOR p IN 1..l_revenue_category.COUNT LOOP
2099 create_res_type_numeric_id(l_revenue_category(p), 14, l_res_id,l_return_status,l_msg_data);
2100 END LOOP;
2101
2102
2103 DELETE pa_rbs_map_tmp2 ;
2104
2105 /* added for bug#4027727 */
2106 BEGIN
2107
2108 SELECT job_group_id
2109 INTO l_job_group_id
2110 FROM pa_rbs_versions_b
2111 WHERE rbs_version_id = l_rbs_struct_version_id(i) ;
2112
2113 EXCEPTION
2114 WHEN NO_DATA_FOUND THEN
2115 NULL;
2116 END;
2117
2118 INSERT INTO pa_rbs_map_tmp2
2119 (txn_accum_header_id,
2120 struct_version_id, ---not needed to insert but doing
2121 supplier_id, --- supplier
2122 role_id, --- role
2123 revenue_category_id, --- revenue category
2124 resource_class_id, --- resource class
2125 non_labor_resource_id, --- project non-labor resource
2126 person_type_id, --- person type
2127 organization_id, --- organization
2128 job_id, --- job
2129 inventory_item_id, --- inventory item
2130 item_category_id, --- item category
2131 expenditure_type_id, --- expenditure type
2132 expenditure_category_id,--- expenditure category
2133 event_type_id, --- event type
2134 person_id, --- named person
2135 bom_equipment_id, --- BOM equipment
2136 bom_labor_id --- BOM labor
2137 )
2138 SELECT DISTINCT
2139 head.txn_accum_header_id,
2140 l_rbs_struct_version_id(i),
2141 decode(head.vendor_id,-1,null,head.vendor_id), --- supplier
2142 decode(head.PROJECT_ROLE_ID,-1,null, head.project_role_id), --- role
2143 decode(head.revenue_category,'PJI$NULL',null,get_res_type_numeric_id(head.revenue_category,14)), --- revenue category
2144 decode(head.resource_class_id,-1,null,head.resource_class_id), --- resource class
2145 decode(head.non_labor_resource_id,-1,null,head.non_labor_resource_id), --- non labor resource
2146 decode(head.person_type,'PJI$NULL',null,get_res_type_numeric_id(head.person_type,11)), --- person type
2147 decode(head.expenditure_organization_id,-1,null,head.expenditure_organization_id), --- organization
2148 decode(head.job_id,-1,null,l_job_group_id,null,head.job_id,PA_Cross_Business_Grp.IsMappedToJob(head.job_id, l_job_group_id)), --- job, bug#4027727
2149 decode(head.inventory_item_id,-1,null,head.inventory_item_id), --- inventory item
2150 decode(head.item_category_id,-1,null,head.item_category_id), --- item category
2151 decode(head.expenditure_type_id,-1,null,head.expenditure_type_id), --- expenditure type
2152 decode(head.expenditure_category_id,-1,null,head.expenditure_category_id), --- expenditure category
2153 decode(head.event_type_id,-1,null,head.event_type_id), --- event type
2154 decode(head.person_id,-1,null,head.person_id), --- named person
2155 decode(head.bom_equipment_resource_id,-1,null,head.bom_equipment_resource_id), --- BOM equipment
2156 decode(head.bom_labor_resource_id,-1,null,head.bom_labor_resource_id) --- BOM labor
2157 FROM pa_rbs_map_tmp1 tmp,
2158 pji_fp_txn_accum_header head
2159 WHERE tmp.struct_version_id = l_rbs_struct_version_id(i) AND
2160 tmp.txn_accum_header_id = head.txn_accum_header_id;
2161
2162
2163 BEGIN
2164
2165 map_rbs_txn(l_rbs_struct_version_id(i),l_return_status,l_msg_count,l_msg_data);
2166
2167
2168 IF g_denorm_refresh = 'Y' THEN
2169 --Bug: 3952330
2170 --Log event to populate RBS denorm again for this RBS version
2171 insert into pji_pa_proj_events_log (
2172 EVENT_OBJECT,
2173 EVENT_TYPE,
2174 WORKER_ID
2175 )
2176 values (
2177 l_rbs_struct_version_id(i),
2178 'PJI_RBS_CHANGE',
2179 l_worker_id
2180 );
2181
2182 END IF;
2183
2184 --Reset the variable as it is not used in Actuals processing
2185 g_denorm_refresh := 'N';
2186
2187
2188 EXCEPTION WHEN OTHERS THEN
2189
2190 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2191 x_msg_count := 1;
2192 x_msg_data := 'pa_rbs_mapping.map_rbs_actuals.' || NVL(l_msg_data, SQLERRM);
2193
2194 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => 'PA_RBS_MAPPING'
2195 ,p_procedure_name => 'map_rbs_txn');
2196
2197 IF g_debug_mode = 'Y' THEN
2198 Pa_Debug.g_err_stage:='Unexpected Error inside pa_rbs_mapping.map_rbs_actuals for Structure Version ='||l_rbs_struct_version_id(i)||' '|| NVL(l_msg_data,SQLERRM) ;
2199 Pa_Debug.WRITE(g_module_name, Pa_Debug.g_err_stage,5);
2200 Pa_Debug.reset_curr_function;
2201 END IF;
2202
2203 ROLLBACK TO map_rbs_txn;
2204 RAISE; -- Bug#5223360 - RBS mapping Errors will not get suppressed
2205 END;
2206
2207
2208 END LOOP;
2209
2210 IF g_debug_mode = 'Y' THEN
2211
2212 select count(*) into l_out from PA_RBS_PLANS_OUT_TMP;
2213 pa_debug.g_err_stage:= ' Number of rows in plans_out after map_rbs_txn ' ||l_out;
2214 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2215
2216 pa_debug.g_err_stage:= 'Exiting map_rbs_actuals' ;
2217 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2218 pa_debug.reset_curr_function;
2219
2220
2221 END IF;
2222
2223
2224 EXCEPTION
2225 WHEN OTHERS THEN
2226 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2227 x_msg_count := 1;
2228 x_msg_data := 'pa_rbs_mapping.map_rbs_actuals.' || nvl(l_msg_data, SQLERRM);
2229 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => 'PA_RBS_MAPPING'
2230 ,p_procedure_name => 'map_rbs_actuals');
2231
2232 IF g_debug_mode = 'Y' THEN
2233 pa_debug.g_err_stage:='Unexpected Error inside pa_rbs_mapping.map_rbs_actuals '|| nvl(l_msg_data, SQLERRM) ;
2234 pa_debug.write(g_module_name, pa_debug.g_err_stage,5);
2235 pa_debug.reset_curr_function;
2236 END IF;
2237 raise;
2238 END;
2239
2240 PROCEDURE map_rbs_plans
2241 (
2242 p_rbs_version_id IN NUMBER DEFAULT NULL,
2243 x_return_status OUT NOCOPY VARCHAR2,
2244 x_msg_count OUT NOCOPY NUMBER,
2245 x_msg_data OUT NOCOPY VARCHAR2
2246 )
2247 IS
2248 l_revenue_category PA_PLSQL_DATATYPES.Char30TabTyp;
2249 l_person_type_code PA_PLSQL_DATATYPES.Char30TabTyp;
2250
2251 l_rbs_struct_version_id PA_PLSQL_DATATYPES.NumTabTyp;
2252
2253 l_res_id NUMBER;
2254 l_return_status VARCHAR2(30);
2255 l_msg_data VARCHAR2(500);
2256
2257 l_msg_count NUMBER;
2258
2259 l_job_group_id NUMBER ; --added for bug#4027727
2260
2261 can_not_lock_header exception; --added for bug#4102476, 4101364
2262 can_not_release_lock exception;
2263 lockhndl varchar2(128); --added for bug#4102476
2264 l number; --added for bug#4101364
2265 l_release number;
2266 l_out number;
2267 l_tmpcnt number; -- Bug#5503706
2268 BEGIN
2269
2270 x_return_status := FND_API.G_RET_STS_SUCCESS;
2271
2272 IF g_debug_mode = 'Y' THEN
2273 PA_DEBUG.set_curr_function( p_function => 'map_rbs_plans'
2274 ,p_debug_mode => g_debug_mode );
2275 pa_debug.g_err_stage:= 'Inside map_rbs_plans : p_rbs_version_id = ' || p_rbs_version_id ;
2276 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2277 END IF;
2278
2279 l_tmpcnt :=0;
2280
2281 IF p_rbs_version_id IS NOT NULL THEN
2282
2283 IF g_debug_mode = 'Y' THEN
2284 pa_debug.g_err_stage:= 'rbs version is provided';
2285 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2286 END IF;
2287
2288 --check whether headers are present in pji_fp_txn_accum_header or not, if not then create them in pji_fp_txn_accum_header
2289 --assuming that source_id is populated in pji_fp_txn_accum_header
2290
2291 -- PJI_FP_TXN_ACCUM_HEADER will have -1 for NUMBER columns and PJI$NULL for varchar columns and hence nvl
2292 -- on PJI_FP_TXN_ACCUM_HEADER columns is not required
2293
2294 SELECT distinct revenue_category_code
2295 BULK COLLECT INTO l_revenue_category
2296 FROM PA_RBS_PLANS_IN_TMP
2297 WHERE revenue_category_code NOT IN
2298 (select resource_name from pa_rbs_element_map where resource_type_id = 14)
2299 AND revenue_category_code IS NOT NULL; /*Added for bug 3575147*/
2300
2301 FOR p IN 1..l_revenue_category.COUNT LOOP
2302 create_res_type_numeric_id(l_revenue_category(p), 14, l_res_id,l_return_status,l_msg_data);
2303 END LOOP;
2304
2305
2306 SELECT distinct person_type_code
2307 BULK COLLECT INTO l_person_type_code
2308 FROM PA_RBS_PLANS_IN_TMP
2309 WHERE person_type_code NOT IN
2310 (select resource_name from pa_rbs_element_map where resource_type_id = 11)
2311 AND person_type_code IS NOT NULL; /* Added for bug 3575147 */
2312
2313 FOR p IN 1..l_person_type_code.COUNT LOOP
2314 create_res_type_numeric_id(l_person_type_code(p), 11, l_res_id,l_return_status,l_msg_data);
2315 END LOOP;
2316
2317
2318 /* added for bug#4027727 */
2319 BEGIN
2320 SELECT job_group_id INTO l_job_group_id FROM pa_rbs_versions_b WHERE rbs_version_id = p_rbs_version_id ;
2321 EXCEPTION
2322 WHEN NO_DATA_FOUND THEN
2323 NULL;
2324 END;
2325
2326
2327 /* added for bug#4102476, 4101364 */
2328 lockhndl := auto_allocate_unique('LOCK_PJI_FP_TXN_ACCUM_HEADER');
2329 l := dbms_lock.request(lockhndl, 6, 864000, TRUE) ;
2330 IF l <> 0 AND l <> 4 THEN -- 4 has been added to make it compatible within same session
2331 raise can_not_lock_header;
2332 END IF;
2333
2334 INSERT INTO PJI_FP_TXN_ACCUM_HEADER(
2335 txn_accum_header_id,
2336 vendor_id,
2337 project_role_id,
2338 revenue_category,
2339 resource_class_id,
2340 non_labor_resource_id,
2341 expenditure_organization_id,
2342 expenditure_org_id,
2343 work_type_id,
2344 exp_evt_type_id,
2345 event_type,
2346 event_type_classification,
2347 expenditure_type,
2348 expenditure_category,
2349 system_linkage_function,
2350 job_id,
2351 inventory_item_id,
2352 item_category_id,
2353 expenditure_type_id,
2354 expenditure_category_id,
2355 event_type_id,
2356 person_id,
2357 bom_equipment_resource_id,
2358 bom_labor_resource_id,
2359 person_type,
2360 named_role,
2361 last_update_date,
2362 LAST_UPDATED_BY,
2363 CREATION_DATE,
2364 CREATED_BY,
2365 LAST_UPDATE_LOGIN )
2366 SELECT
2367 PJI_FP_TXN_ACCUM_HEADER_S.nextval ,
2368 decode(TAB1.supplier_id,null,-1,TAB1.supplier_id),
2369 decode(TAB1.role_id,null,-1,TAB1.role_id),
2370 decode(TAB1.revenue_category_code,null,'PJI$NULL',TAB1.revenue_category_code),
2371 decode(TAB1.resource_class_id,null,-1,TAB1.resource_class_id) ,
2372 decode(TAB1.non_labor_resource_id,null,-1,TAB1.non_labor_resource_id),
2373 decode(TAB1.organization_id,null,-1,TAB1.organization_id),
2374 -1,
2375 -1,
2376 -1,
2377 nvl(evt.event_type, 'PJI$NULL'),
2378 nvl(evt.event_type_classification, 'PJI$NULL'),
2379 nvl(et.expenditure_type,'PJI$NULL'),
2380 nvl(ec.expenditure_category,'PJI$NULL'),
2381 'PJI$NULL',
2382 decode(TAB1.job_id,null,-1,TAB1.job_id),
2383 decode(TAB1.inventory_item_id,null,-1,TAB1.inventory_item_id),
2384 decode(TAB1.item_category_id,null,-1,TAB1.item_category_id),
2385 decode(TAB1.expenditure_type_id,null,-1,TAB1.expenditure_type_id),
2386 decode(TAB1.expenditure_category_id,null,-1,TAB1.expenditure_category_id),
2387 decode(TAB1.event_type_id,null,-1,TAB1.event_type_id),
2388 decode(TAB1.person_id,null,-1,TAB1.person_id),
2389 decode(TAB1.bom_equipment_id,null,-1,TAB1.bom_equipment_id),
2390 decode(TAB1.bom_labor_id,null,-1,TAB1.bom_labor_id),
2391 decode(TAB1.person_type_code,null,'PJI$NULL',TAB1.person_type_code),
2392 'PJI$NULL',
2393 SYSDATE,
2394 g_user_id,
2395 SYSDATE,
2396 g_user_id,
2397 g_login_id
2398 FROM
2399 (SELECT
2400 distinct
2401 supplier_id,
2402 role_id,
2403 revenue_category_code,
2404 resource_class_id,
2405 non_labor_resource_id,
2406 organization_id,
2407 job_id,
2408 inventory_item_id,
2409 item_category_id,
2410 expenditure_type_id,
2411 expenditure_category_id,
2412 event_type_id,
2413 person_id,
2414 bom_equipment_id,
2415 bom_labor_id,
2416 person_type_code
2417 FROM PA_RBS_PLANS_IN_TMP
2418 WHERE rowid NOT IN
2419 (SELECT /*+ ordered */ tmp.rowid
2420 FROM PA_RBS_PLANS_IN_TMP tmp, PJI_FP_TXN_ACCUM_HEADER head
2421 WHERE
2422 head.vendor_id = nvl(tmp.supplier_id,-1) AND
2423 head.project_role_id = nvl(tmp.role_id,-1) AND
2424 head.revenue_category = nvl(tmp.revenue_category_code,'PJI$NULL') AND
2425 head.resource_class_id = nvl(tmp.resource_class_id,-1) AND
2426 head.non_labor_resource_id = nvl(tmp.non_labor_resource_id,-1) AND
2427 head.expenditure_organization_id= nvl(tmp.organization_id,-1) AND
2428 head.job_id = nvl(tmp.job_id,-1) AND
2429 head.inventory_item_id = nvl(tmp.inventory_item_id,-1) AND
2430 head.item_category_id = nvl(tmp.item_category_id,-1) AND
2431 head.expenditure_type_id = nvl(tmp.expenditure_type_id,-1) AND
2432 head.expenditure_category_id = nvl(tmp.expenditure_category_id,-1) AND
2433 head.event_type_id = nvl(tmp.event_type_id,-1) AND
2434 head.person_id = nvl(tmp.person_id,-1) AND
2435 head.bom_equipment_resource_id = nvl(tmp.bom_equipment_id,-1) AND
2436 head.bom_labor_resource_id = nvl(tmp.bom_labor_id,-1) AND
2437 head.person_type = nvl(tmp.person_type_code,'PJI$NULL')
2438 ) )TAB1,
2439 pa_event_types evt,
2440 pa_expenditure_types et,
2441 pa_expenditure_categories ec
2442 WHERE
2443 tab1.event_type_id = evt.event_type_id (+) and
2444 tab1.expenditure_type_id = et.expenditure_type_id (+) and
2445 tab1.expenditure_category_id = ec.expenditure_category_id (+);
2446
2447 /*bug#4770784, to remove the locking issue changes start*/
2448 IF SQL%ROWCOUNT = 0 THEN
2449 l_release := dbms_lock.release(lockhndl) ;
2450 IF l_release <> 0 AND l <> 4 THEN -- 4 has been added to make it compatible within same session
2451 raise can_not_release_lock;
2452 END IF;
2453 END IF;
2454 /*bug#4770784, end here */
2455
2456 IF g_debug_mode = 'Y' THEN
2457 pa_debug.g_err_stage:= 'accum headers are inserted';
2458 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2459 END IF;
2460
2461 DELETE PA_RBS_PLANS_OUT_TMP;
2462 INSERT INTO PA_RBS_PLANS_OUT_TMP
2463 (txn_accum_header_id,
2464 rbs_version_id,
2465 source_id,
2466 supplier_id,
2467 role_id,
2468 revenue_category_code,
2469 resource_class_id,
2470 non_labor_resource_id,
2471 organization_id,
2472 job_id,
2473 inventory_item_id,
2474 item_category_id,
2475 expenditure_type_id,
2476 expenditure_category_id,
2477 event_type_id,
2478 person_id,
2479 bom_equipment_id,
2480 bom_labor_id,
2481 person_type_code)
2482 SELECT /*+ ordered */
2483 head.txn_accum_header_id,
2484 p_rbs_version_id,
2485 tmp.source_id,
2486 tmp.supplier_id,
2487 tmp.role_id,
2488 tmp.revenue_category_code,
2489 tmp.resource_class_id,
2490 tmp.non_labor_resource_id,
2491 tmp.organization_id,
2492 tmp.job_id,
2493 tmp.inventory_item_id,
2494 tmp.item_category_id,
2495 tmp.expenditure_type_id,
2496 tmp.expenditure_category_id,
2497 tmp.event_type_id,
2498 tmp.person_id,
2499 tmp.bom_equipment_id,
2500 tmp.bom_labor_id,
2501 tmp.person_type_code
2502 FROM PA_RBS_PLANS_IN_TMP tmp, PJI_FP_TXN_ACCUM_HEADER head
2503 WHERE
2504 head.vendor_id = nvl(tmp.supplier_id,-1) AND
2505 head.project_role_id = nvl(tmp.role_id,-1) AND
2506 head.revenue_category = nvl(tmp.revenue_category_code,'PJI$NULL') AND
2507 head.resource_class_id = nvl(tmp.resource_class_id,-1) AND
2508 head.non_labor_resource_id = nvl(tmp.non_labor_resource_id,-1) AND
2509 head.expenditure_organization_id= nvl(tmp.organization_id,-1) AND
2510 head.job_id = nvl(tmp.job_id,-1) AND
2511 head.inventory_item_id = nvl(tmp.inventory_item_id,-1) AND
2512 head.item_category_id = nvl(tmp.item_category_id,-1) AND
2513 head.expenditure_type_id = nvl(tmp.expenditure_type_id,-1) AND
2514 head.expenditure_category_id = nvl(tmp.expenditure_category_id,-1) AND
2515 head.event_type_id = nvl(tmp.event_type_id,-1) AND
2516 head.person_id = nvl(tmp.person_id,-1) AND
2517 head.bom_equipment_resource_id = nvl(tmp.bom_equipment_id,-1) AND
2518 head.bom_labor_resource_id = nvl(tmp.bom_labor_id,-1) AND
2519 head.person_type = nvl(tmp.person_type_code,'PJI$NULL') ;
2520
2521 --bug#4098679
2522 UPDATE pa_rbs_plans_out_tmp dest
2523 SET revenue_category_id =
2524 ( SELECT
2525 src.resource_id
2526 FROM
2527 pa_rbs_element_map src
2528 WHERE
2529 src.resource_name = dest.revenue_category_code AND
2530 src.resource_type_id = 14 )
2531 WHERE
2532 revenue_category_code is not null;
2533
2534 UPDATE pa_rbs_plans_out_tmp dest
2535 SET person_type_id =
2536 ( SELECT
2537 src.resource_id
2538 FROM
2539 pa_rbs_element_map src
2540 WHERE
2541 src.resource_name = dest.person_type_code AND
2542 src.resource_type_id = 11 )
2543 WHERE
2544 dest.person_type_code is not null;
2545 /** Commented for bug 6662808
2546 UPDATE pa_rbs_plans_out_tmp dest
2547 SET job_id =
2548 ( decode(job_id, null, null, l_job_group_id, null, job_id,
2549 PA_Cross_Business_Grp.IsMappedToJob(job_id, l_job_group_id)))
2550 WHERE
2551 dest.job_id is not null;
2552 **/
2553
2554 /** Added for bug 6662808 **/
2555
2556 if l_job_group_id is not null then
2557
2558 UPDATE pa_rbs_plans_out_tmp dest
2559 SET job_id= PA_Cross_Business_Grp.IsMappedToJob(job_id,l_job_group_id)
2560 WHERE dest.job_id is not null;
2561
2562 end if;
2563 /** AEnd of addition for bug 6662808 **/
2564
2565 DELETE pa_rbs_map_tmp2 ;
2566 INSERT INTO pa_rbs_map_tmp2
2567 (txn_accum_header_id,
2568 struct_version_id,
2569 supplier_id,
2570 role_id,
2571 revenue_category_id,
2572 resource_class_id,
2573 non_labor_resource_id,
2574 organization_id,
2575 job_id,
2576 inventory_item_id,
2577 item_category_id,
2578 expenditure_type_id,
2579 expenditure_category_id,
2580 event_type_id,
2581 person_id,
2582 bom_equipment_id,
2583 bom_labor_id,
2584 person_type_id)
2585 SELECT
2586 distinct /* bug#3656352 */
2587 txn_accum_header_id,
2588 p_rbs_version_id,
2589 supplier_id,
2590 role_id,
2591 -- get_res_type_numeric_id(revenue_category_code,14),
2592 revenue_category_id, --bug#4098679
2593 resource_class_id,
2594 non_labor_resource_id,
2595 organization_id,
2596 -- decode(job_id, null, null, l_job_group_id, null, job_id, PA_Cross_Business_Grp.IsMappedToJob(job_id, l_job_group_id)), --bug#4027727
2597 job_id, --bug#4098679
2598 inventory_item_id,
2599 item_category_id,
2600 expenditure_type_id,
2601 expenditure_category_id,
2602 event_type_id,
2603 person_id,
2604 bom_equipment_id,
2605 bom_labor_id,
2606 -- get_res_type_numeric_id(person_type_code,11)
2607 person_type_id --bug#4098679
2608 FROM PA_RBS_PLANS_OUT_TMP a
2609 WHERE NOT EXISTS
2610 (SELECT 1
2611 FROM pa_rbs_txn_accum_map b
2612 WHERE b.struct_version_id = p_rbs_version_id and
2613 b.txn_accum_header_id=a.txn_accum_header_id);
2614
2615
2616
2617 IF g_debug_mode = 'Y' THEN
2618 pa_debug.g_err_stage:= 'calling map_rbs_txn for rbs version = '||p_rbs_version_id;
2619 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2620 END IF;
2621
2622 -- Bug#5503706 Start
2623 select count(*) into l_tmpcnt from PA_RBS_MAP_TMP2;
2624
2625 IF g_debug_mode = 'Y' THEN
2626 pa_debug.g_err_stage:= 'The number of rows in pa_rbs_map_tmp2 table = '||l_tmpcnt;
2627 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2628 END IF;
2629
2630 if l_tmpcnt >0 then
2631 map_rbs_txn(p_rbs_version_id,l_return_status,l_msg_count,l_msg_data);
2632 end if;
2633
2634 -- Bug#5503706 End
2635
2636 --bug#4098679
2637 IF NVL(PJI_UTILS.GET_PARAMETER('PJI_FPM_UPGRADE'), 'P') = 'C'
2638 AND g_denorm_refresh = 'Y'
2639 THEN
2640
2641 IF g_debug_mode = 'Y' THEN
2642 pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Denorm.POPULATE_RBS_DENORM' ;
2643 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2644 pa_debug.reset_curr_function;
2645 END IF;
2646
2647 Pji_Pjp_Sum_Denorm.POPULATE_RBS_DENORM(
2648 p_worker_id => 1,
2649 p_denorm_type => 'RBS',
2650 p_rbs_version_id => p_rbs_version_id) ;
2651
2652 IF g_debug_mode = 'Y' THEN
2653 pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Rollup.set_online_context' ;
2654 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2655 pa_debug.reset_curr_function;
2656 END IF;
2657
2658 Pji_Pjp_Sum_Rollup.set_online_context (
2659 p_event_id => NULL,
2660 p_project_id => NULL,
2661 p_plan_type_id => NULL,
2662 p_old_baselined_version => NULL,
2663 p_new_baselined_version => NULL,
2664 p_old_original_version => NULL,
2665 p_new_original_version => NULL,
2666 p_old_struct_version => NULL,
2667 p_new_struct_version => NULL,
2668 p_rbs_version => p_rbs_version_id );
2669
2670 IF g_debug_mode = 'Y' THEN
2671 pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM' ;
2672 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2673 pa_debug.reset_curr_function;
2674 END IF;
2675
2676 Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM;
2677
2678 IF g_debug_mode = 'Y' THEN
2679 pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Denorm.cleanup_rbs_denorm' ;
2680 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2681 pa_debug.reset_curr_function;
2682 END IF;
2683
2684 Pji_Pjp_Sum_Denorm.cleanup_rbs_denorm(
2685 p_worker_id => 1,
2686 p_extraction_type => 'ONLINE'
2687 );
2688
2689 g_denorm_refresh := 'N';
2690
2691 END IF;
2692
2693 UPDATE PA_RBS_PLANS_OUT_TMP tmp
2694 SET tmp.rbs_element_id =
2695 (select map.element_id
2696 from pa_rbs_txn_accum_map map
2697 where map.txn_accum_header_id = tmp.txn_accum_header_id
2698 and map.struct_version_id = p_rbs_version_id);
2699
2700
2701 ELSE ---------------------- if rbs structure version id is not provided, header and source_id is populated
2702
2703 IF g_debug_mode = 'Y' THEN
2704 pa_debug.g_err_stage:= 'rbs version is not provided';
2705 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2706 END IF;
2707
2708 DELETE pa_rbs_map_tmp1 ;
2709
2710 INSERT INTO pa_rbs_map_tmp1
2711 (txn_accum_header_id,
2712 struct_version_id)
2713 SELECT distinct tmp.txn_accum_header_id, prj_assign.rbs_version_id
2714 FROM PA_RBS_PLANS_IN_TMP tmp,
2715 PA_RESOURCE_ASSIGNMENTS res_assign,
2716 PA_RBS_PRJ_ASSIGNMENTS prj_assign
2717 WHERE tmp.source_id = res_assign.resource_assignment_id AND
2718 res_assign.project_id = prj_assign.project_id AND
2719 tmp.txn_accum_header_id not in
2720 (SELECT txn_accum_header_id
2721 FROM pa_rbs_txn_accum_map
2722 WHERE struct_version_id = prj_assign.rbs_version_id );
2723
2724 SELECT distinct struct_version_id
2725 BULK COLLECT
2726 INTO l_rbs_struct_version_id
2727 FROM pa_rbs_map_tmp1;
2728
2729
2730
2731 FOR i IN 1..l_rbs_struct_version_id.COUNT LOOP
2732
2733 DELETE pa_rbs_map_tmp2 ;
2734 INSERT INTO pa_rbs_map_tmp2
2735 (txn_accum_header_id,
2736 struct_version_id, ---not needed to insert but doing
2737 supplier_id, --- supplier
2738 role_id, --- role
2739 revenue_category_id, --- revenue category
2740 resource_class_id, --- resource class
2741 non_labor_resource_id, --- project non-labor resource
2742 person_type_id, --- person type
2743 organization_id, --- organization
2744 job_id, --- job
2745 inventory_item_id, --- inventory item
2746 item_category_id, --- item category
2747 expenditure_type_id, --- expenditure type
2748 expenditure_category_id,--- expenditure category
2749 event_type_id, --- event type
2750 person_id, --- named person
2751 bom_equipment_id, --- BOM equipment
2752 bom_labor_id --- BOM labor
2753 )
2754 SELECT
2755 tmp.txn_accum_header_id,
2756 l_rbs_struct_version_id(i),
2757 tmp.supplier_id, --- supplier
2758 tmp.role_id, --- role
2759 get_res_type_numeric_id(tmp.revenue_category_code,14), --- revenue category
2760 tmp.resource_class_id, --- resource class
2761 tmp.non_labor_resource_id, --- non labor resource
2762 get_res_type_numeric_id(tmp.person_type_code,11), --- person_type
2763 tmp.organization_id, --- organization
2764 tmp.job_id, --- job
2765 tmp.inventory_item_id, --- inventory item
2766 tmp.item_category_id, --- item category
2767 tmp.expenditure_type_id, --- expenditure type
2768 tmp.expenditure_category_id, --- expenditure category
2769 tmp.event_type_id, --- event type
2770 tmp.person_id, --- named person
2771 tmp.bom_equipment_id, --- BOM equipment
2772 tmp.bom_labor_id --- BOM labor
2773 FROM pa_rbs_map_tmp1 tmp1, PA_RBS_PLANS_IN_TMP tmp
2774 WHERE tmp1.struct_version_id = l_rbs_struct_version_id(i) AND
2775 tmp1.txn_accum_header_id = tmp.txn_accum_header_id AND
2776 tmp.rowid = (select max(rowid) from PA_RBS_PLANS_IN_TMP where txn_accum_header_id = tmp.txn_accum_header_id);
2777
2778 IF g_debug_mode = 'Y' THEN
2779 pa_debug.g_err_stage:= 'calling map_rbs_txn for rbs version = '||l_rbs_struct_version_id(i) ;
2780 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2781 END IF;
2782
2783 -- Bug#5503706 Start
2784 select count(*) into l_tmpcnt from PA_RBS_MAP_TMP2;
2785
2786 IF g_debug_mode = 'Y' THEN
2787 pa_debug.g_err_stage:= 'The number of rows in pa_rbs_map_tmp2 table = '||l_tmpcnt;
2788 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2789 END IF;
2790
2791 if l_tmpcnt >0 then
2792 map_rbs_txn(l_rbs_struct_version_id(i),l_return_status,l_msg_count,l_msg_data);
2793 end if;
2794
2795 -- Bug#5503706 End
2796
2797 --bug#4098679
2798 IF NVL(PJI_UTILS.GET_PARAMETER('PJI_FPM_UPGRADE'), 'P') = 'C'
2799 AND g_denorm_refresh = 'Y' THEN
2800
2801 IF g_debug_mode = 'Y' THEN
2802 pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Denorm.POPULATE_RBS_DENORM' ;
2803 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2804 pa_debug.reset_curr_function;
2805 END IF;
2806
2807 Pji_Pjp_Sum_Denorm.POPULATE_RBS_DENORM(
2808 p_worker_id => 1,
2809 p_denorm_type => 'RBS',
2810 p_rbs_version_id => p_rbs_version_id) ;
2811
2812 IF g_debug_mode = 'Y' THEN
2813 pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Rollup.set_online_context' ;
2814 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2815 pa_debug.reset_curr_function;
2816 END IF;
2817
2818 Pji_Pjp_Sum_Rollup.set_online_context (
2819 p_event_id => NULL,
2820 p_project_id => NULL,
2821 p_plan_type_id => NULL,
2822 p_old_baselined_version => NULL,
2823 p_new_baselined_version => NULL,
2824 p_old_original_version => NULL,
2825 p_new_original_version => NULL,
2826 p_old_struct_version => NULL,
2827 p_new_struct_version => NULL,
2828 p_rbs_version => p_rbs_version_id );
2829
2830 IF g_debug_mode = 'Y' THEN
2831 pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM' ;
2832 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2833 pa_debug.reset_curr_function;
2834 END IF;
2835
2836 Pji_Pjp_Sum_Rollup.UPDATE_RBS_DENORM;
2837
2838 IF g_debug_mode = 'Y' THEN
2839 pa_debug.g_err_stage:= 'Calling Pji_Pjp_Sum_Denorm.cleanup_rbs_denorm' ;
2840 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2841 pa_debug.reset_curr_function;
2842 END IF;
2843
2844 Pji_Pjp_Sum_Denorm.cleanup_rbs_denorm(
2845 p_worker_id => 1,
2846 p_extraction_type => 'ONLINE'
2847 );
2848
2849 g_denorm_refresh := 'N';
2850
2851 END IF;
2852
2853 END LOOP;
2854
2855 END IF;
2856
2857 IF g_debug_mode = 'Y' THEN
2858 pa_debug.g_err_stage:= 'Exiting map_rbs_plans ' ;
2859 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2860 pa_debug.reset_curr_function;
2861
2862 select count(*) into l_out from PA_RBS_PLANS_OUT_TMP;
2863 pa_debug.g_err_stage:= ' Number of rows in plans_out after map_rbs_txn ' ||l_out;
2864 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2865 pa_debug.reset_curr_function;
2866
2867 END IF;
2868
2869 EXCEPTION
2870 WHEN OTHERS THEN
2871 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2872 x_msg_count := 1;
2873 x_msg_data := 'pa_rbs_mapping.map_rbs_plans.' || nvl(l_msg_data, SQLERRM) ;
2874 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => 'PA_RBS_MAPPING'
2875 ,p_procedure_name => 'map_rbs_plans');
2876
2877 IF g_debug_mode = 'Y' THEN
2878 pa_debug.g_err_stage:='Unexpected Error inside pa_rbs_mapping.map_rbs_plans '|| nvl(l_msg_data, SQLERRM) ;
2879 pa_debug.write(g_module_name, pa_debug.g_err_stage,5);
2880 pa_debug.reset_curr_function;
2881 END IF;
2882 raise;
2883 END;
2884
2885 PROCEDURE create_res_type_numeric_id
2886 (
2887 p_resource_name IN VARCHAR2,
2888 p_resource_type_id IN NUMBER,
2889 x_resource_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
2890 x_return_status OUT NOCOPY VARCHAR2,
2891 x_msg_data OUT NOCOPY VARCHAR2
2892 )
2893 IS
2894 l_resource_name VARCHAR2(240) := p_resource_name; /* bug#3656974 changed 30 to 240 */
2895 l_count NUMBER;
2896 BEGIN
2897
2898 x_return_status := FND_API.G_RET_STS_SUCCESS;
2899
2900 IF g_debug_mode = 'Y' THEN
2901 PA_DEBUG.set_curr_function( p_function => 'create_res_type_numeric_id'
2902 ,p_debug_mode => g_debug_mode );
2903 pa_debug.g_err_stage:= 'Inside create_res_type_numeric_id';
2904 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
2905 END IF;
2906
2907 IF l_resource_name = 'EMP_APL' THEN
2908 l_resource_name := 'EMP' ;
2909 END IF;
2910
2911 SELECT count(*)
2912 INTO l_count
2913 FROM PA_RBS_ELEMENT_MAP
2914 WHERE resource_name = l_resource_name
2915 AND resource_type_id = p_resource_type_id ;
2916
2917 IF l_count = 1 THEN
2918 SELECT resource_id
2919 INTO x_resource_id
2920 FROM PA_RBS_ELEMENT_MAP
2921 WHERE resource_name = l_resource_name
2922 AND resource_type_id = p_resource_type_id ;
2923 ELSIF l_count = 0 THEN
2924 SELECT PA_RBS_ELEMENT_MAP_S.nextval
2925 INTO x_resource_id
2926 FROM dual ;
2927
2928 INSERT INTO PA_RBS_ELEMENT_MAP
2929 (
2930 RESOURCE_TYPE_ID,
2931 RESOURCE_NAME,
2932 RESOURCE_ID,
2933 CREATION_DATE,
2934 CREATED_BY,
2935 LAST_UPDATE_LOGIN,
2936 LAST_UPDATED_BY,
2937 LAST_UPDATE_DATE)
2938 VALUES
2939 (
2940 p_resource_type_id,
2941 l_resource_name,
2942 x_resource_id,
2943 sysdate,
2944 g_user_id ,
2945 g_login_id ,
2946 g_user_id ,
2947 sysdate) ;
2948 END IF;
2949
2950 IF g_debug_mode = 'Y' THEN
2951 pa_debug.g_err_stage:= 'Exiting create_res_type_numeric_id' ;
2952 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2953 pa_debug.reset_curr_function;
2954 END IF;
2955
2956 EXCEPTION
2957 WHEN OTHERS THEN
2958 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2959 x_msg_data := 'pa_rbs_mapping.create_res_type_numeric_id.' || SQLERRM;
2960 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => 'PA_RBS_MAPPING'
2961 ,p_procedure_name => 'create_res_type_numeric_id');
2962
2963 IF g_debug_mode = 'Y' THEN
2964 pa_debug.g_err_stage:='Unexpected Error inside pa_rbs_mapping.create_res_type_numeric_id '||SQLERRM;
2965 pa_debug.write(g_module_name, pa_debug.g_err_stage,5);
2966 pa_debug.reset_curr_function;
2967 END IF;
2968 raise;
2969 END;
2970
2971
2972 FUNCTION get_res_type_numeric_id
2973 (
2974 p_resource_name IN VARCHAR2,
2975 p_resource_type_id IN NUMBER
2976 ) RETURN NUMBER
2977 IS
2978 l_resource_id NUMBER;
2979 BEGIN
2980 IF p_resource_name IS NULL THEN
2981 RETURN NULL;
2982 ELSE
2983 SELECT resource_id
2984 INTO l_resource_id
2985 FROM PA_RBS_ELEMENT_MAP
2986 WHERE resource_name = p_resource_name
2987 AND resource_type_id = p_resource_type_id;
2988
2989 RETURN l_resource_id;
2990 END IF;
2991
2992 END;
2993
2994 ---------------------------------------------------
2995 --insert rule into pa_rbs_mapping_rules
2996 ---------------------------------------------------
2997
2998 PROCEDURE insert_rule
2999 (
3000 rbs_version_id number,
3001 depth number,
3002 level PA_PLSQL_DATATYPES.Char30TabTyp,
3003 element_id number,
3004 rule_flag varchar2,
3005 per_rc_pre number,
3006 equip_rc_pre number,
3007 mat_rc_pre number,
3008 fin_rc_pre number
3009 )
3010 IS
3011 l_SQL_statement varchar2(5000);
3012 col_sql_clause varchar2(1000);
3013 val_sql_clause varchar2(1000);
3014 l_rule_id number;
3015
3016 l_rule_flag varchar2(15);
3017 BEGIN
3018
3019
3020 IF g_debug_mode = 'Y' THEN
3021 PA_DEBUG.set_curr_function( p_function => 'insert_rule'
3022 ,p_debug_mode => g_debug_mode );
3023 pa_debug.g_err_stage:= 'Inside insert_rule';
3024 pa_debug.g_err_stage:= 'Inside insert_rule- rbs_version_id :'|| rbs_version_id||' depth:'||depth;
3025 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3026 END IF;
3027
3028 col_sql_clause := '' ;
3029 for i in 1..depth loop
3030 if i=depth then
3031 col_sql_clause := col_sql_clause || ' level' || i ;
3032 else
3033 col_sql_clause := col_sql_clause || ' level' || i || ' ,' ;
3034 end if;
3035 end loop;
3036
3037
3038
3039
3040
3041 val_sql_clause := '' ;
3042 for i in 1..depth loop
3043 if i=depth then
3044 val_sql_clause := val_sql_clause || '''' || level(i) || '''' ;
3045 else
3046 val_sql_clause := val_sql_clause || '''' || level(i) || '''' || ' ,' ;
3047 end if;
3048 end loop;
3049
3050
3051
3052 select PA_RBS_MAPPING_RULE_S.nextval
3053 into l_rule_id
3054 from dual;
3055
3056 -- commented select below as its not required because rule_flag in pa_rbs_elements is not null column
3057 -- select decode(rule_flag,null,'null',''''||rule_flag||'''') into l_rule_flag from dual ;
3058
3059 l_rule_flag := rule_flag; --l_rule_flag is redundant, we can remove and directly use rule_flag
3060 l_SQL_statement := 'INSERT INTO PA_RBS_MAPPING_RULES' ||
3061 ' (ELEMENT_VERSION_ID, ' ||
3062 ' RULE_ID, ' ||
3063 ' RBS_ELEMENT_ID, ' ||
3064 ' RULE_FLAG, ' ||
3065 col_sql_clause || ' , ' ||
3066 ' PERSON_RC_PRECEDENCE, ' ||
3067 ' EQUIPMENT_RC_PRECEDENCE, ' ||
3068 ' MATERIAL_RC_PRECEDENCE, ' ||
3069 ' FIN_ELEM_RC_PRECEDENCE, ' ||
3070 ' MAX_LEVEL, ' ||
3071 ' LAST_UPDATE_DATE, ' ||
3072 ' LAST_UPDATED_BY, ' ||
3073 ' CREATION_DATE, ' ||
3074 ' CREATED_BY, ' ||
3075 ' LAST_UPDATE_LOGIN ) ' ||
3076 ' VALUES ' ||
3077 '( :rbs_version_id,' ||
3078 ':l_rule_id,' ||
3079 ':element_id,' ||
3080 ':l_rule_flag,' ||
3081 val_sql_clause ||' ,' ||
3082 ':per_rc_pre,' ||
3083 ':equip_rc_pre,' ||
3084 ':mat_rc_pre,' ||
3085 ':fin_rc_pre,' ||
3086 ':depth,' ||
3087 '''' || sysdate || '''' || ' ,' ||
3088 ':g_user_id,' ||
3089 '''' || sysdate || '''' || ' ,' ||
3090 ':g_user_id,' ||
3091 ':g_login_id ) ; ' ;
3092
3093
3094
3095
3096 EXECUTE IMMEDIATE 'BEGIN ' || l_SQL_statement || ' END;'
3097 USING rbs_version_id, l_rule_id, element_id, l_rule_flag, per_rc_pre,
3098 equip_rc_pre, mat_rc_pre, fin_rc_pre, depth, g_user_id, g_login_id;
3099
3100
3101
3102 IF g_debug_mode = 'Y' THEN
3103 pa_debug.g_err_stage:= 'Exiting insert_rule' ;
3104 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
3105 pa_debug.reset_curr_function;
3106 END IF;
3107
3108
3109 END;
3110
3111 ------------------------------------------------------
3112 --Gets the token names corresponding to all 17
3113 --resource types
3114 ------------------------------------------------------
3115
3116 FUNCTION get_res_token
3117 (
3118 p_res_type_code VARCHAR2,
3119 p_elem_version_id NUMBER
3120 ) RETURN VARCHAR2
3121 IS
3122 l_value NUMBER;
3123 -- added for custom nodes
3124 l_value1 NUMBER;
3125 l_value2 NUMBER;
3126 l_value3 NUMBER;
3127 l_value4 NUMBER;
3128 l_value5 NUMBER;
3129
3130 BEGIN
3131
3132 IF p_res_type_code = 'BOM_LABOR' THEN
3133 select bom_labor_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3134 if l_value = -1 then
3135 RETURN 'R:BML';
3136 elsif l_value > 0 then
3137 RETURN 'I:BML:'||l_value; --bug#3759977
3138 end if;
3139 ELSIF p_res_type_code = 'BOM_EQUIPMENT' THEN
3140 select bom_equipment_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3141 if l_value = -1 then
3142 RETURN 'R:BME';
3143 elsif l_value > 0 then
3144 RETURN 'I:BME:'||l_value; --bug#3759977
3145 end if;
3146 ELSIF p_res_type_code = 'NAMED_PERSON' THEN
3147 select person_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3148 if l_value = -1 then
3149 RETURN 'R:PER';
3150 elsif l_value > 0 then
3151 RETURN 'I:PER:'||l_value; --bug#3759977
3152 end if;
3153 ELSIF p_res_type_code = 'EVENT_TYPE' THEN
3154 select event_type_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3155 if l_value = -1 then
3156 RETURN 'R:EVT';
3157 elsif l_value > 0 then
3158 RETURN 'I:EVT:'||l_value; --bug#3759977
3159 end if;
3160 ELSIF p_res_type_code = 'EXPENDITURE_CATEGORY' THEN
3161 select expenditure_category_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3162 if l_value = -1 then
3163 RETURN 'R:EXC';
3164 elsif l_value > 0 then
3165 RETURN 'I:EXC:'||l_value; --bug#3759977
3166 end if;
3167 ELSIF p_res_type_code = 'EXPENDITURE_TYPE' THEN
3168 select expenditure_type_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3169 if l_value = -1 then
3170 RETURN 'R:EXT';
3171 elsif l_value > 0 then
3172 RETURN 'I:EXT:'||l_value; --bug#3759977
3173 end if;
3174 ELSIF p_res_type_code = 'ITEM_CATEGORY' THEN
3175 select item_category_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3176 if l_value = -1 then
3177 RETURN 'R:ITC';
3178 elsif l_value > 0 then
3179 RETURN 'I:ITC:'||l_value; --bug#3759977
3180 end if;
3181 ELSIF p_res_type_code = 'INVENTORY_ITEM' THEN
3182 select inventory_item_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3183 if l_value = -1 then
3184 RETURN 'R:ITM';
3185 elsif l_value > 0 then
3186 RETURN 'I:ITM:'||l_value; --bug#3759977
3187 end if;
3188 ELSIF p_res_type_code = 'JOB' THEN
3189 select job_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3190 if l_value = -1 then
3191 RETURN 'R:JOB';
3192 elsif l_value > 0 then
3193 RETURN 'I:JOB:'||l_value; --bug#3759977
3194 end if;
3195 ELSIF p_res_type_code = 'ORGANIZATION' THEN
3196 select organization_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3197 if l_value = -1 then
3198 RETURN 'R:ORG';
3199 elsif l_value > 0 then
3200 RETURN 'I:ORG:'||l_value; --bug#3759977
3201 end if;
3202 ELSIF p_res_type_code = 'PERSON_TYPE' THEN
3203 select person_type_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3204 if l_value = -1 then
3205 RETURN 'R:PTP';
3206 elsif l_value > 0 then
3207 RETURN 'I:PTP:'||l_value; --bug#3759977
3208 end if;
3209 ELSIF p_res_type_code = 'NON_LABOR_RESOURCE' THEN
3210 select non_labor_resource_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3211 if l_value = -1 then
3212 RETURN 'R:NLR';
3213 elsif l_value > 0 then
3214 RETURN 'I:NLR:'||l_value; --bug#3759977
3215 end if;
3216 ELSIF p_res_type_code = 'RESOURCE_CLASS' THEN
3217 select resource_class_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3218 if l_value = -1 then
3219 RETURN 'R:RES';
3220 elsif l_value > 0 then
3221 RETURN 'I:RES:'||l_value; --bug#3759977
3222 end if;
3223 ELSIF p_res_type_code = 'REVENUE_CATEGORY' THEN
3224 select revenue_category_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3225 if l_value = -1 then
3226 RETURN 'R:RVC';
3227 elsif l_value > 0 then
3228 RETURN 'I:RVC:'||l_value; --bug#3759977
3229 end if;
3230 ELSIF p_res_type_code = 'ROLE' THEN
3231 select role_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3232 if l_value = -1 then
3233 RETURN 'R:ROL';
3234 elsif l_value > 0 then
3235 RETURN 'I:ROL:'||l_value; --bug#3759977
3236 end if;
3237 ELSIF p_res_type_code = 'SUPPLIER' THEN
3238 select supplier_id into l_value from pa_rbs_elements where rbs_element_id = p_elem_version_id ;
3239 if l_value = -1 then
3240 RETURN 'R:SUP';
3241 elsif l_value > 0 then
3242 RETURN 'I:SUP:'||l_value; --bug#3759977
3243 end if;
3244 ELSIF p_res_type_code = 'USER_DEFINED' THEN
3245 select USER_DEFINED_CUSTOM1_ID,
3246 USER_DEFINED_CUSTOM2_ID,
3247 USER_DEFINED_CUSTOM3_ID,
3248 USER_DEFINED_CUSTOM4_ID,
3249 USER_DEFINED_CUSTOM5_ID
3250 into l_value1,
3251 l_value2,
3252 l_value3,
3253 l_value4,
3254 l_value5
3255 from pa_rbs_elements where rbs_element_id = p_elem_version_id;
3256
3257 -- bug#3810558 changed CUS1 to CU1 etc
3258
3259 If l_value5 is not null then
3260 RETURN 'C:CU5';
3261 elsif l_value4 is not null then
3262 RETURN 'C:CU4';
3263 elsif l_value3 is not null then
3264 RETURN 'C:CU3';
3265 elsif l_value2 is not null then
3266 RETURN 'C:CU2';
3267 elsif l_value1 is not null then
3268 RETURN 'C:CU1';
3269 end if;
3270 END IF;
3271
3272 END ;
3273
3274 ------------------------------------------------------
3275 --traverse RBS tree to get all the rules for RBS
3276 ------------------------------------------------------
3277 PROCEDURE traverse_tree
3278 (rbs_version_id number,
3279 element_id number,
3280 depth number,
3281 level IN OUT NOCOPY /* file.sql.39 change */ PA_PLSQL_DATATYPES.Char30TabTyp,
3282 per_rc_pre IN OUT NOCOPY /* file.sql.39 change */ number,
3283 equip_rc_pre IN OUT NOCOPY /* file.sql.39 change */ number,
3284 mat_rc_pre IN OUT NOCOPY /* file.sql.39 change */ number,
3285 fin_rc_pre IN OUT NOCOPY /* file.sql.39 change */ number
3286 )
3287 IS
3288 l_count number;
3289 l_res_type_id PA_PLSQL_DATATYPES.IdTabTyp;
3290 elem_version_id PA_PLSQL_DATATYPES.IdTabTyp;
3291 l_tmp varchar2(30);
3292 rule_flag PA_PLSQL_DATATYPES.Char1TabTyp;
3293
3294 per_rc_pre_last number; --bug#3793418
3295 equip_rc_pre_last number; --bug#3793418
3296 mat_rc_pre_last number; --bug#3793418
3297 fin_rc_pre_last number; --bug#3793418
3298
3299 BEGIN
3300
3301 /* commenting this because its recursive function call and set_curr_function was getting
3302 called again and again without reset_curr_function to give rise to
3303 ORA-06502: PL/SQL: numeric or value error
3304
3305 IF g_debug_mode = 'Y' THEN
3306 PA_DEBUG.set_curr_function( p_function => 'traverse_tree'
3307 ,p_debug_mode => g_debug_mode );
3308 pa_debug.g_err_stage:= 'Inside traverse_tree';
3309 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3310 END IF;
3311
3312 */
3313
3314 select element_version_id
3315 , resource_type_id
3316 , rule_flag
3317 bulk collect into elem_version_id
3318 , l_res_type_id
3319 , rule_flag
3320 from PA_RBS_MAP_TMP1
3321 where nvl(parent_element_version_id,-1) = nvl(element_id,-1) ;
3322 l_count := SQL%ROWCOUNT;
3323
3324 if l_count = 0 then
3325 return ;
3326 end if;
3327
3328 per_rc_pre_last := per_rc_pre; --bug#3793418
3329 equip_rc_pre_last := equip_rc_pre; --bug#3793418
3330 mat_rc_pre_last := mat_rc_pre; --bug#3793418
3331 fin_rc_pre_last := fin_rc_pre; --bug#3793418
3332
3333 for i in 1..l_count loop
3334 select get_res_token(res_type_code,elem_version_id(i))
3335 into level(depth+1)
3336 from pa_res_types_b
3337 where res_type_id = l_res_type_id(i); --bug#3917401 replaced pa_res_types_vl by pa_res_types_b
3338 per_rc_pre := per_rc_pre_last + PA_RBS_PREC_PUB.calc_rc_precedence(l_res_type_id(i), 1); --bug#3793418
3339 equip_rc_pre := equip_rc_pre_last + PA_RBS_PREC_PUB.calc_rc_precedence(l_res_type_id(i), 2); --bug#3793418
3340 mat_rc_pre := mat_rc_pre_last + PA_RBS_PREC_PUB.calc_rc_precedence(l_res_type_id(i), 3); --bug#3793418
3341 fin_rc_pre := fin_rc_pre_last + PA_RBS_PREC_PUB.calc_rc_precedence(l_res_type_id(i), 4); --bug#3793418
3342
3343 insert_rule(rbs_version_id,depth+1,level, elem_version_id(i), rule_flag(i),per_rc_pre,equip_rc_pre,mat_rc_pre,fin_rc_pre);
3344
3345 traverse_tree(rbs_version_id, elem_version_id(i),depth+1,level,per_rc_pre,equip_rc_pre,mat_rc_pre,fin_rc_pre) ;
3346
3347 end loop;
3348
3349 IF g_debug_mode = 'Y' THEN
3350 pa_debug.g_err_stage:= 'Exiting traverse_tree' ;
3351 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
3352 pa_debug.reset_curr_function;
3353 END IF;
3354
3355 END;
3356
3357 ------------------------------------------------------
3358 --create rules for a given RBS structure version
3359 ------------------------------------------------------
3360
3361 PROCEDURE create_mapping_rules
3362 (
3363 p_rbs_version_id IN NUMBER,
3364 x_return_status OUT NOCOPY VARCHAR2,
3365 x_msg_count OUT NOCOPY NUMBER,
3366 x_msg_data OUT NOCOPY VARCHAR2
3367 )
3368 IS
3369 l_level PA_PLSQL_DATATYPES.Char30TabTyp;
3370 per_rc_pre number;
3371 equip_rc_pre number;
3372 mat_rc_pre number;
3373 fin_rc_pre number;
3374
3375 p_elem_version_id number;
3376 l_count number;
3377 l_count_tmp1 number; --Bug#5248414
3378 BEGIN
3379
3380 x_return_status := FND_API.G_RET_STS_SUCCESS;
3381
3382 IF g_debug_mode = 'Y' THEN
3383 PA_DEBUG.set_curr_function( p_function => 'create_mapping_rules'
3384 ,p_debug_mode => g_debug_mode );
3385 pa_debug.g_err_stage:= 'Inside create_mapping_rules- p_rbs_version_id :'|| p_rbs_version_id;
3386 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3387 END IF;
3388
3389 select count(1) into l_count from dual
3390 where exists
3391 (select 1
3392 from pa_rbs_mapping_rules
3393 where element_version_id = p_rbs_version_id);
3394
3395
3396 IF l_count =0 THEN
3397 delete PA_RBS_MAP_TMP1;
3398 insert into PA_RBS_MAP_TMP1
3399 (
3400 STRUCT_VERSION_ID,
3401 ELEMENT_VERSION_ID,
3402 PARENT_ELEMENT_VERSION_ID,
3403 RESOURCE_CLASS_ID,
3404 BOM_LABOR_ID,
3405 BOM_EQUIPMENT_ID,
3406 PERSON_ID,
3407 EVENT_TYPE_ID,
3408 EXPENDITURE_CATEGORY_ID,
3409 EXPENDITURE_TYPE_ID,
3410 ITEM_CATEGORY_ID,
3411 INVENTORY_ITEM_ID,
3412 JOB_ID,
3413 ORGANIZATION_ID,
3414 PERSON_TYPE_ID,
3415 NON_LABOR_RESOURCE_ID,
3416 ROLE_ID,
3417 SUPPLIER_ID,
3418 -- added for custom nodes
3419 USER_DEFINED_CUSTOM1_ID,
3420 USER_DEFINED_CUSTOM2_ID,
3421 USER_DEFINED_CUSTOM3_ID,
3422 USER_DEFINED_CUSTOM4_ID,
3423 USER_DEFINED_CUSTOM5_ID,
3424 RULE_FLAG,
3425 RESOURCE_TYPE_ID
3426 )
3427 SELECT
3428 RBS_VERSION_ID,
3429 RBS_ELEMENT_ID,
3430 PARENT_ELEMENT_ID,
3431 RESOURCE_CLASS_ID,
3432 BOM_LABOR_ID,
3433 BOM_EQUIPMENT_ID,
3434 PERSON_ID,
3435 EVENT_TYPE_ID,
3436 EXPENDITURE_CATEGORY_ID,
3437 EXPENDITURE_TYPE_ID,
3438 ITEM_CATEGORY_ID,
3439 INVENTORY_ITEM_ID,
3440 JOB_ID,
3441 ORGANIZATION_ID,
3442 PERSON_TYPE_ID,
3443 NON_LABOR_RESOURCE_ID,
3444 ROLE_ID,
3445 SUPPLIER_ID,
3446 -- added for custom nodes
3447 USER_DEFINED_CUSTOM1_ID,
3448 USER_DEFINED_CUSTOM2_ID,
3449 USER_DEFINED_CUSTOM3_ID,
3450 USER_DEFINED_CUSTOM4_ID,
3451 USER_DEFINED_CUSTOM5_ID,
3452 RULE_FLAG,
3453 RESOURCE_TYPE_ID
3454 from PA_RBS_ELEMENTS
3455 where rbs_version_id = p_rbs_version_id and
3456 user_created_flag = 'Y';
3457
3458 l_count_tmp1:=SQL%ROWCOUNT; --Bug#5248414
3459
3460 IF g_debug_mode = 'Y' THEN
3461 PA_DEBUG.set_curr_function( p_function => 'create_mapping_rules'
3462 ,p_debug_mode => g_debug_mode );
3463 pa_debug.g_err_stage:= 'Inside create_mapping_rules- Inserts in Tmp1 :'||l_count_tmp1 ;
3464 pa_debug.write(g_module_name,pa_debug.g_err_stage,3) ;
3465 END IF;
3466
3467 IF l_count_tmp1 > 0 THEN --Bug#5248414
3468
3469 l_level(1) := 'SELF' ;
3470 per_rc_pre := 0 ;
3471 equip_rc_pre := 0 ;
3472 mat_rc_pre := 0 ;
3473 fin_rc_pre := 0 ;
3474
3475 select element_version_id
3476 into p_elem_version_id
3477 from PA_RBS_MAP_TMP1
3478 where parent_element_version_id is null;
3479
3480 --insertion of SELF node
3481 insert_rule(p_rbs_version_id, 1 ,l_level, p_elem_version_id, 'N',0,0,0,0);
3482
3483 traverse_tree(p_rbs_version_id, p_elem_version_id, 1, l_level,per_rc_pre,equip_rc_pre,mat_rc_pre,fin_rc_pre);
3484
3485 delete PA_RBS_MAP_TMP1;
3486
3487 END IF;
3488
3489 END IF;
3490
3491 IF g_debug_mode = 'Y' THEN
3492 pa_debug.g_err_stage:= 'Exiting create_mapping_rules' ;
3493 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
3494 pa_debug.reset_curr_function;
3495 END IF;
3496
3497 EXCEPTION
3498 WHEN OTHERS THEN
3499 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3500 x_msg_count := 1;
3501 x_msg_data := 'pa_rbs_mapping.create_mapping_rules.' || SQLERRM;
3502 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => 'PA_RBS_MAPPING'
3503 ,p_procedure_name => 'create_mapping_rules');
3504 IF g_debug_mode = 'Y' THEN
3505 pa_debug.g_err_stage:='Unexpected Error inside pa_rbs_mapping.create_mapping_rules '||SQLERRM;
3506 pa_debug.write(g_module_name, pa_debug.g_err_stage,5);
3507 pa_debug.reset_curr_function;
3508
3509 END IF;
3510 raise;
3511 END;
3512
3513
3514
3515 END; --end package pa_rbs_mapping