[Home] [Help]
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:
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:
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,'
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
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:
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 ;
1118:
1119: END;
1120:
1121: ------------------------------------------------------
1122: --create top rbs element inserting into pa_rbs_elements
1123: ------------------------------------------------------
1124:
1125: PROCEDURE create_top_rbs_element
1126: (
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,'
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: (
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,'
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 '
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:
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' || ''''
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
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:
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,
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,
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:
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
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 ;
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 */
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 */
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' ||
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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