[Home] [Help]
421: * Procedure : Insert_elements
422: * Description : This Procedure is used to insert into
423: * the pa_rbs_element_names_b table
424: * it does a direct
425: * Insert from the pa_rbs_elem_in_temp
426: * table based on the resource_type_id
427: * passed.
428: * *****************************************************/
429: PROCEDURE Insert_elements(p_resource_type_id IN NUMBER,
449: FND_GLOBAL.USER_ID,
450: sysdate,
451: FND_GLOBAL.USER_ID,
452: FND_GLOBAL.LOGIN_ID
453: FROM pa_rbs_elem_in_temp a
454: WHERE a.resource_type_id = p_resource_type_id
455: AND NOT EXISTS (select 'Y'
456: FROM Pa_rbs_element_names_b b
457: where b.RESOURCE_TYPE_ID = a.resource_type_id
506: FND_GLOBAL.USER_ID,
507: FND_GLOBAL.LOGIN_ID
508: FROM
509: pa_rbs_element_names_b a,
510: pa_rbs_elem_in_temp b,
511: Pa_rbs_versions_tl c
512: WHERE a.resource_source_id = b.resource_source_id
513: AND a.resource_type_id = p_resource_type_id
514: AND b.resource_type_id = p_resource_type_id
541: FND_GLOBAL.USER_ID,
542: FND_GLOBAL.LOGIN_ID
543: FROM
544: pa_rbs_element_names_b a,
545: pa_rbs_elem_in_temp b,
546: fnd_lookup_values c
547: WHERE a.resource_source_id = b.resource_source_id
548: AND a.resource_type_id = p_resource_type_id
549: AND b.resource_type_id = p_resource_type_id
579: FND_GLOBAL.USER_ID,
580: FND_GLOBAL.LOGIN_ID
581: FROM
582: pa_rbs_element_names_b a,
583: pa_rbs_elem_in_temp b,
584: per_all_people_f c,
585: fnd_languages l
586: WHERE a.resource_source_id = b.resource_source_id
587: AND a.resource_type_id = p_resource_type_id
619: FND_GLOBAL.USER_ID,
620: FND_GLOBAL.LOGIN_ID
621: FROM
622: pa_rbs_element_names_b a,
623: pa_rbs_elem_in_temp b,
624: Per_Jobs c,
625: Fnd_Languages L
626: WHERE a.resource_source_id = b.resource_source_id
627: AND a.resource_type_id = p_resource_type_id
656: FND_GLOBAL.USER_ID,
657: FND_GLOBAL.LOGIN_ID
658: FROM
659: pa_rbs_element_names_b a,
660: pa_rbs_elem_in_temp b,
661: fnd_lookup_values lk,
662: /* Changes for Bug 3780201 start*/
663: pa_rbs_element_map c
664: WHERE a.resource_source_id = b.resource_source_id
700: FND_GLOBAL.USER_ID,
701: FND_GLOBAL.LOGIN_ID
702: FROM
703: pa_rbs_element_names_b a,
704: pa_rbs_elem_in_temp b,
705: Bom_Resources c,
706: Fnd_Languages L
707: WHERE a.resource_source_id = b.resource_source_id
708: and c.resource_id = a.resource_source_id
739: FND_GLOBAL.USER_ID,
740: FND_GLOBAL.LOGIN_ID
741: FROM
742: pa_rbs_element_names_b a,
743: pa_rbs_elem_in_temp b,
744: pa_non_labor_resources c,
745: Fnd_Languages L
746: WHERE a.resource_source_id = b.resource_source_id
747: AND a.resource_type_id = p_resource_type_id
777: FND_GLOBAL.USER_ID,
778: FND_GLOBAL.LOGIN_ID
779: FROM
780: pa_rbs_element_names_b a,
781: pa_rbs_elem_in_temp b,
782: Pa_Resource_Classes_tl c
783: WHERE a.resource_source_id = b.resource_source_id
784: AND a.resource_type_id = p_resource_type_id
785: AND b.resource_type_id = p_resource_type_id
815: FND_GLOBAL.USER_ID,
816: FND_GLOBAL.LOGIN_ID
817: FROM
818: pa_rbs_element_names_b a,
819: pa_rbs_elem_in_temp b,
820: Pa_rbs_element_map c,
821: Fnd_Languages L
822: WHERE a.resource_source_id = b.resource_source_id
823: AND a.resource_type_id = p_resource_type_id
855: FND_GLOBAL.USER_ID,
856: FND_GLOBAL.LOGIN_ID
857: FROM
858: pa_rbs_element_names_b a,
859: pa_rbs_elem_in_temp b,
860: Pa_rbs_element_map c,
861: fnd_lookup_values lk
862: WHERE a.resource_source_id = b.resource_source_id
863: AND a.resource_type_id = p_resource_type_id
896: FND_GLOBAL.USER_ID,
897: FND_GLOBAL.LOGIN_ID
898: FROM
899: pa_rbs_element_names_b a,
900: pa_rbs_elem_in_temp b,
901: pa_event_types c,
902: Fnd_Languages L
903: WHERE a.resource_source_id = b.resource_source_id
904: and c.event_type_id = a.resource_source_id
935: FND_GLOBAL.USER_ID,
936: FND_GLOBAL.LOGIN_ID
937: FROM
938: pa_rbs_element_names_b a,
939: pa_rbs_elem_in_temp b,
940: pa_expenditure_types c,
941: Fnd_Languages L
942: WHERE a.resource_source_id = b.resource_source_id
943: and c.expenditure_type_id = a.resource_source_id
974: FND_GLOBAL.USER_ID,
975: FND_GLOBAL.LOGIN_ID
976: FROM
977: pa_rbs_element_names_b a,
978: pa_rbs_elem_in_temp b,
979: pa_expenditure_categories c,
980: Fnd_Languages L
981: WHERE a.resource_source_id = b.resource_source_id
982: and c.expenditure_category_id = a.resource_source_id
1013: FND_GLOBAL.USER_ID,
1014: FND_GLOBAL.LOGIN_ID
1015: FROM
1016: pa_rbs_element_names_b a,
1017: pa_rbs_elem_in_temp b,
1018: po_vendors c,
1019: Fnd_Languages L
1020: WHERE a.resource_source_id = b.resource_source_id
1021: and c.vendor_id = a.resource_source_id
1076: FND_GLOBAL.USER_ID,
1077: FND_GLOBAL.LOGIN_ID
1078: FROM
1079: pa_rbs_element_names_b a,
1080: pa_rbs_elem_in_temp b,
1081: hr_all_organization_units_tl c
1082: WHERE a.resource_source_id = b.resource_source_id
1083: and c.organization_id = a.resource_source_id
1084: AND a.resource_type_id = p_resource_type_id
1112: FND_GLOBAL.USER_ID,
1113: FND_GLOBAL.LOGIN_ID
1114: FROM
1115: pa_rbs_element_names_b a,
1116: pa_rbs_elem_in_temp b,
1117: Pa_Project_Role_Types_tl c
1118: WHERE a.resource_source_id = b.resource_source_id
1119: and c.project_role_id = a.resource_source_id
1120: AND a.resource_type_id = p_resource_type_id
1149: FND_GLOBAL.USER_ID,
1150: FND_GLOBAL.LOGIN_ID
1151: FROM
1152: pa_rbs_element_names_b a,
1153: pa_rbs_elem_in_temp b,
1154: Fnd_Languages L,
1155: Mtl_Categories_v c
1156: WHERE a.resource_source_id = b.resource_source_id
1157: and c.category_id = a.resource_source_id
1190: FND_GLOBAL.USER_ID,
1191: FND_GLOBAL.LOGIN_ID
1192: FROM
1193: pa_rbs_element_names_b a,
1194: pa_rbs_elem_in_temp b,
1195: Mtl_System_Items_tl c,
1196: Mtl_System_Items_b d --For bug 3602566
1197: WHERE a.resource_source_id = b.resource_source_id
1198: and c.Inventory_Item_Id = a.resource_source_id
1219: * Procedure : Populate_RBS_Element_Name
1220: * Description : This API does the following:-
1221: * - It can be called in 2 ways
1222: * 1. passing 1 resource_source_id and resource_type_id
1223: * 2. Populating the pa_rbs_elem_in_temp with a bunch
1224: * of resource_source_id and resource_type_id and
1225: * call this api.
1226: * -> In the first case we will take in a
1227: * resource_source_id and resource_type_id
1253: Cursor c_get_res_types
1254: IS
1255: SELECT distinct a.resource_type_id,
1256: decode(a.resource_type_id,-1,'VERSION',-2,'RULE',b.res_type_code)
1257: FROM pa_rbs_elem_in_temp a,pa_res_types_b b
1258: WHERE a.resource_type_id = b.res_type_id(+)
1259: ORDER by resource_type_id;
1260:
1261: --Declaration of the local variables
1281: --For bug 4026456, 4887312
1282:
1283: /*
1284: FND_STATS.SET_TABLE_STATS('PA',
1285: 'PA_RBS_ELEM_IN_TEMP',
1286: 100,
1287: 10,
1288: 100);
1289: */
1291:
1292: -- Proper Fix for 4887312 *** RAMURTHY 03/01/06 02:33 pm ***
1293: -- It solves the issue above wrt commit by the FND_STATS.SET_TABLE_STATS call
1294: -- Bug 8261905, Replaced 'PA' by PJI_UTILS.GET_PA_SCHEMA_NAME
1295: PA_TASK_ASSIGNMENT_UTILS.set_table_stats(PJI_UTILS.GET_PA_SCHEMA_NAME,'PA_RBS_ELEM_IN_TEMP',100,10,100);
1296:
1297: -- End fix 4887312
1298: /*******************************************************
1299: * Test to determine how this API is being called.
1318: ELSE
1319: l_resource_type_id := p_resource_type_id;
1320: END IF;
1321: /*******************************************
1322: * Insert into the pa_rbs_elem_in_temp table
1323: * explicitely.
1324: **********************************************/
1325: BEGIN
1326: Insert into pa_rbs_elem_in_temp
1322: * Insert into the pa_rbs_elem_in_temp table
1323: * explicitely.
1324: **********************************************/
1325: BEGIN
1326: Insert into pa_rbs_elem_in_temp
1327: (resource_source_id,
1328: resource_type_id)
1329: Values
1330: (p_resource_source_id,
1337: END;
1338: END IF;
1339: /*************************************************
1340: * Added an extra check to check for duplicate rows
1341: * in the pa_rbs_elem_in_temp temporary table
1342: * and delete it, before proceeding.
1343: * Because if duplicate values are passed for
1344: * combination of resource_source_id and
1345: * resource_type_id, then we cannot anyway
1345: * resource_type_id, then we cannot anyway
1346: * insert multiple rows into the 2 main tables.
1347: **************************************************/
1348: BEGIN
1349: DELETE FROM pa_rbs_elem_in_temp a WHERE ROWID > (
1350: SELECT min(rowid) FROM pa_rbs_elem_in_temp b
1351: WHERE a.resource_source_id = b.resource_source_id
1352: AND a.resource_type_id = b.resource_type_id);
1353:
1346: * insert multiple rows into the 2 main tables.
1347: **************************************************/
1348: BEGIN
1349: DELETE FROM pa_rbs_elem_in_temp a WHERE ROWID > (
1350: SELECT min(rowid) FROM pa_rbs_elem_in_temp b
1351: WHERE a.resource_source_id = b.resource_source_id
1352: AND a.resource_type_id = b.resource_type_id);
1353:
1354: /* Also check to see that rows don't already exist
1355: * in element names for these resources - therefore,
1356: * delete rows from the temp table which already
1357: * have rows in pa_rbs_element_names_b */
1358:
1359: DELETE FROM pa_rbs_elem_in_temp a
1360: WHERE EXISTS (SELECT 'Y'
1361: FROM pa_rbs_element_names_b b
1362: WHERE a.resource_source_id = b.resource_source_id
1363: AND a.resource_type_id = b.resource_type_id);
1362: WHERE a.resource_source_id = b.resource_source_id
1363: AND a.resource_type_id = b.resource_type_id);
1364: END;
1365:
1366: select count(*) into l_temp_count from pa_rbs_elem_in_temp;
1367: IF l_temp_count = 0 AND l_call_flag = 'A' THEN
1368: SELECT rbs_element_name_id
1369: INTO x_rbs_element_name_id
1370: FROM pa_rbs_element_names_b
1390: Savepoint insert_for_call_flag_AB;
1391: /***********************************************
1392: * Insert into the Pa_rbs_element_names_b
1393: * for the res_type passed. it will just do an
1394: * Insert as Select from the pa_rbs_elem_in_temp
1395: * table for the corr res_type.
1396: *****************************************************/
1397: Insert_elements
1398: (p_resource_type_id => l_res_type,
1444:
1445: END LOOP;
1446: CLOSE c_get_res_types;
1447:
1448: DELETE FROM pa_rbs_elem_in_temp;
1449:
1450:
1451: IF l_call_flag = 'A' THEN
1452: BEGIN
2830:
2831: -- Create Elements for the Version top nodes
2832: -- First populate element names for the top node.
2833:
2834: INSERT INTO PA_RBS_ELEM_IN_TEMP
2835: (resource_source_id, resource_type_id)
2836: VALUES (l_rbs_version_id, -1);
2837:
2838: -- Upgrade all the resource list members for this list -
2840: -- First, Populate rbs element names for all elements.
2841: -- populate temp table and call API.
2842:
2843: --dbms_output.put_line('Populate RBS Element Names');
2844: INSERT INTO PA_RBS_ELEM_IN_TEMP
2845: (resource_source_id,
2846: resource_type_id)
2847: SELECT decode(typ.res_type_code, 'ORGANIZATION', rlm.organization_id,
2848: decode(typ.res_type_code, 'NAMED_PERSON', rlm.person_id,
2865: (rlm.resource_type_id = 111
2866: AND typ.res_type_code = 'ROLE') -- Role
2867: );
2868:
2869: /*INSERT INTO PA_RBS_ELEM_IN_TEMP
2870: (resource_source_id,
2871: resource_type_id)
2872: SELECT source.resource_source_id,
2873: typ.res_type_id -- resource_type_id
2898: AND source.resource_name = rlm.expenditure_category
2899: AND source.type_number = 2) --ECat
2900: );*/
2901: -- rewrite the above sql for perf bug 4887375
2902: INSERT INTO PA_RBS_ELEM_IN_TEMP
2903: (resource_source_id,
2904: resource_type_id)
2905: SELECT resource_source_id,
2906: res_type_id -- resource_type_id
2958: --x_msg_count => l_msg_count,
2959: x_msg_data => x_msg_data);
2960:
2961:
2962: INSERT INTO PA_RBS_ELEM_IN_TEMP
2963: (resource_source_id,
2964: resource_type_id)
2965: VALUES (l_resource_id,
2966: l_rev_cat.res_type_id);