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