[Home] [Help]
419: END Get_element_Name;
420: /********************************************************
421: * Procedure : Insert_elements
422: * Description : This Procedure is used to insert into
423: * the pa_rbs_element_names_b table
424: * it does a direct
425: * Insert from the pa_rbs_elem_in_temp
426: * table based on the resource_type_id
427: * passed.
431: IS
432: --l_element_name_id Number;
433: BEGIN
434: -- IF p_call_flag = 'B' THEN
435: INSERT INTO Pa_rbs_element_names_b
436: (RBS_ELEMENT_NAME_ID,
437: RESOURCE_SOURCE_ID,
438: RESOURCE_TYPE_ID,
439: LAST_UPDATE_DATE,
452: FND_GLOBAL.LOGIN_ID
453: FROM pa_rbs_elem_in_temp a
454: WHERE a.resource_type_id = p_resource_type_id
455: AND NOT EXISTS (select 'Y'
456: FROM Pa_rbs_element_names_b b
457: where b.RESOURCE_TYPE_ID = a.resource_type_id
458: and b.RESOURCE_SOURCE_ID = a.resource_source_id);
459: -- AND (a.resource_source_id,a.resource_type_id)
460: -- NOT IN (SELECT RESOURCE_SOURCE_ID, RESOURCE_TYPE_ID
457: where b.RESOURCE_TYPE_ID = a.resource_type_id
458: and b.RESOURCE_SOURCE_ID = a.resource_source_id);
459: -- AND (a.resource_source_id,a.resource_type_id)
460: -- NOT IN (SELECT RESOURCE_SOURCE_ID, RESOURCE_TYPE_ID
461: -- FROM Pa_rbs_element_names_b
462: -- where RESOURCE_TYPE_ID = p_resource_type_id);
463: EXCEPTION
464: WHEN OTHERS THEN
465: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
473: * For those res_type_codes for which there
474: * is no Multi lang support.
475: * We are going to insert the value based
476: * on the values inserted into the
477: * pa_rbs_element_names_b table.
478: * **************************************************/
479: PROCEDURE Insert_non_tl_names
480: (p_resource_type_id IN Number,
481: p_resource_type_code IN Varchar2,
505: sysdate,
506: FND_GLOBAL.USER_ID,
507: FND_GLOBAL.LOGIN_ID
508: FROM
509: pa_rbs_element_names_b a,
510: pa_rbs_elem_in_temp b,
511: Pa_rbs_versions_tl c
512: WHERE a.resource_source_id = b.resource_source_id
513: AND a.resource_type_id = p_resource_type_id
540: sysdate,
541: FND_GLOBAL.USER_ID,
542: FND_GLOBAL.LOGIN_ID
543: FROM
544: pa_rbs_element_names_b a,
545: pa_rbs_elem_in_temp b,
546: fnd_lookup_values c
547: WHERE a.resource_source_id = b.resource_source_id
548: AND a.resource_type_id = p_resource_type_id
578: sysdate,
579: FND_GLOBAL.USER_ID,
580: FND_GLOBAL.LOGIN_ID
581: FROM
582: pa_rbs_element_names_b a,
583: pa_rbs_elem_in_temp b,
584: per_all_people_f c,
585: fnd_languages l
586: WHERE a.resource_source_id = b.resource_source_id
618: sysdate,
619: FND_GLOBAL.USER_ID,
620: FND_GLOBAL.LOGIN_ID
621: FROM
622: pa_rbs_element_names_b a,
623: pa_rbs_elem_in_temp b,
624: Per_Jobs c,
625: Fnd_Languages L
626: WHERE a.resource_source_id = b.resource_source_id
655: sysdate,
656: FND_GLOBAL.USER_ID,
657: FND_GLOBAL.LOGIN_ID
658: FROM
659: pa_rbs_element_names_b a,
660: pa_rbs_elem_in_temp b,
661: fnd_lookup_values lk,
662: /* Changes for Bug 3780201 start*/
663: pa_rbs_element_map c
699: sysdate,
700: FND_GLOBAL.USER_ID,
701: FND_GLOBAL.LOGIN_ID
702: FROM
703: pa_rbs_element_names_b a,
704: pa_rbs_elem_in_temp b,
705: Bom_Resources c,
706: Fnd_Languages L
707: WHERE a.resource_source_id = b.resource_source_id
738: sysdate,
739: FND_GLOBAL.USER_ID,
740: FND_GLOBAL.LOGIN_ID
741: FROM
742: pa_rbs_element_names_b a,
743: pa_rbs_elem_in_temp b,
744: pa_non_labor_resources c,
745: Fnd_Languages L
746: WHERE a.resource_source_id = b.resource_source_id
776: sysdate,
777: FND_GLOBAL.USER_ID,
778: FND_GLOBAL.LOGIN_ID
779: FROM
780: pa_rbs_element_names_b a,
781: pa_rbs_elem_in_temp b,
782: Pa_Resource_Classes_tl c
783: WHERE a.resource_source_id = b.resource_source_id
784: AND a.resource_type_id = p_resource_type_id
814: sysdate,
815: FND_GLOBAL.USER_ID,
816: FND_GLOBAL.LOGIN_ID
817: FROM
818: pa_rbs_element_names_b a,
819: pa_rbs_elem_in_temp b,
820: Pa_rbs_element_map c,
821: Fnd_Languages L
822: WHERE a.resource_source_id = b.resource_source_id
854: sysdate,
855: FND_GLOBAL.USER_ID,
856: FND_GLOBAL.LOGIN_ID
857: FROM
858: pa_rbs_element_names_b a,
859: pa_rbs_elem_in_temp b,
860: Pa_rbs_element_map c,
861: fnd_lookup_values lk
862: WHERE a.resource_source_id = b.resource_source_id
895: sysdate,
896: FND_GLOBAL.USER_ID,
897: FND_GLOBAL.LOGIN_ID
898: FROM
899: pa_rbs_element_names_b a,
900: pa_rbs_elem_in_temp b,
901: pa_event_types c,
902: Fnd_Languages L
903: WHERE a.resource_source_id = b.resource_source_id
934: sysdate,
935: FND_GLOBAL.USER_ID,
936: FND_GLOBAL.LOGIN_ID
937: FROM
938: pa_rbs_element_names_b a,
939: pa_rbs_elem_in_temp b,
940: pa_expenditure_types c,
941: Fnd_Languages L
942: WHERE a.resource_source_id = b.resource_source_id
973: sysdate,
974: FND_GLOBAL.USER_ID,
975: FND_GLOBAL.LOGIN_ID
976: FROM
977: pa_rbs_element_names_b a,
978: pa_rbs_elem_in_temp b,
979: pa_expenditure_categories c,
980: Fnd_Languages L
981: WHERE a.resource_source_id = b.resource_source_id
1012: sysdate,
1013: FND_GLOBAL.USER_ID,
1014: FND_GLOBAL.LOGIN_ID
1015: FROM
1016: pa_rbs_element_names_b a,
1017: pa_rbs_elem_in_temp b,
1018: po_vendors c,
1019: Fnd_Languages L
1020: WHERE a.resource_source_id = b.resource_source_id
1041: * For those res_type_codes for which there
1042: * are corr TL tables
1043: * We are going to insert the value based
1044: * on the values inserted into the
1045: * pa_rbs_element_names_b table.
1046: * and do a join with the corr TL
1047: * tables for the res_type_codes.
1048: * **************************************************/
1049: PROCEDURE Insert_tl_names
1075: sysdate,
1076: FND_GLOBAL.USER_ID,
1077: FND_GLOBAL.LOGIN_ID
1078: FROM
1079: pa_rbs_element_names_b a,
1080: pa_rbs_elem_in_temp b,
1081: hr_all_organization_units_tl c
1082: WHERE a.resource_source_id = b.resource_source_id
1083: and c.organization_id = a.resource_source_id
1111: sysdate,
1112: FND_GLOBAL.USER_ID,
1113: FND_GLOBAL.LOGIN_ID
1114: FROM
1115: pa_rbs_element_names_b a,
1116: pa_rbs_elem_in_temp b,
1117: Pa_Project_Role_Types_tl c
1118: WHERE a.resource_source_id = b.resource_source_id
1119: and c.project_role_id = a.resource_source_id
1148: sysdate,
1149: FND_GLOBAL.USER_ID,
1150: FND_GLOBAL.LOGIN_ID
1151: FROM
1152: pa_rbs_element_names_b a,
1153: pa_rbs_elem_in_temp b,
1154: Fnd_Languages L,
1155: Mtl_Categories_v c
1156: WHERE a.resource_source_id = b.resource_source_id
1189: sysdate,
1190: FND_GLOBAL.USER_ID,
1191: FND_GLOBAL.LOGIN_ID
1192: FROM
1193: pa_rbs_element_names_b a,
1194: pa_rbs_elem_in_temp b,
1195: Mtl_System_Items_tl c,
1196: Mtl_System_Items_b d --For bug 3602566
1197: WHERE a.resource_source_id = b.resource_source_id
1225: * call this api.
1226: * -> In the first case we will take in a
1227: * resource_source_id and resource_type_id
1228: * and derive the element_name associated.
1229: * And then insert into the pa_rbs_element_names_b
1230: * and pa_rbs_element_names_tl tables.
1231: * Pass back the element_name_id.
1232: * -> In the 2nd case
1233: * Reads the records from the temp table.
1231: * Pass back the element_name_id.
1232: * -> In the 2nd case
1233: * Reads the records from the temp table.
1234: * For each of them derives the element_name
1235: * Inserts into the pa_rbs_element_names_b
1236: * table.
1237: * Inserts into the pa_rbs_element_names_tl
1238: * table.
1239: * Delete the recs in the temp table.
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
1357: * have rows in pa_rbs_element_names_b */
1358:
1359: DELETE FROM pa_rbs_elem_in_temp a
1360: WHERE EXISTS (SELECT 'Y'
1361: FROM pa_rbs_element_names_b b
1362: WHERE a.resource_source_id = b.resource_source_id
1363: AND a.resource_type_id = b.resource_type_id);
1364: END;
1365:
1366: select count(*) into l_temp_count from pa_rbs_elem_in_temp;
1367: IF l_temp_count = 0 AND l_call_flag = 'A' THEN
1368: SELECT rbs_element_name_id
1369: INTO x_rbs_element_name_id
1370: FROM pa_rbs_element_names_b
1371: WHERE resource_source_id = p_resource_source_id
1372: AND resource_type_id = l_resource_type_id
1373: AND rownum = 1;
1374:
1388: * we can roll back the insert into any prev tables.
1389: **************************************************/
1390: Savepoint insert_for_call_flag_AB;
1391: /***********************************************
1392: * Insert into the Pa_rbs_element_names_b
1393: * for the res_type passed. it will just do an
1394: * Insert as Select from the pa_rbs_elem_in_temp
1395: * table for the corr res_type.
1396: *****************************************************/
1451: IF l_call_flag = 'A' THEN
1452: BEGIN
1453: SELECT rbs_element_name_id
1454: INTO x_rbs_element_name_id
1455: FROM pa_rbs_element_names_b
1456: WHERE resource_source_id = p_resource_source_id
1457: AND resource_type_id = l_resource_type_id;
1458: EXCEPTION
1459: WHEN OTHERS THEN
1545: -- : This API will be called from :
1546: -- : 1.Concurrent program: Refresh RBS Element Names
1547:
1548: -- Note : This API will refresh Resource names(associated with all RBS) for each resource type present
1549: -- in pa_rbs_element_names_b table by
1550: -- making join with respective tables.
1551:
1552: -- Assumptions :
1553:
1626: fnd_global.user_id,
1627: fnd_global.login_id
1628: FROM --pa_lookups lk,
1629: fnd_lookup_values lk,
1630: pa_rbs_element_names_b a
1631: WHERE
1632: a.resource_type_id = -2
1633: and a.resource_source_id = -1
1634: AND lk.lookup_code = 'USER_DEFINED_RESOURCE'
1664: sysdate,
1665: fnd_global.user_id,
1666: fnd_global.login_id
1667: FROM pa_rbs_versions_tl vertl,
1668: pa_rbs_element_names_b b
1669: WHERE b.resource_type_id = -1
1670: AND b.resource_source_id = vertl.rbs_version_id);
1671:
1672:
1714: sysdate,
1715: fnd_global.user_id,
1716: fnd_global.login_id
1717: FROM bom_resources b,
1718: pa_rbs_element_names_b a,
1719: Fnd_Languages L
1720: WHERE a.resource_type_id = 1
1721: AND b.resource_id = a.resource_source_id
1722: AND l.Installed_Flag in ('I', 'B'));
1755: sysdate,
1756: fnd_global.user_id,
1757: fnd_global.login_id
1758: FROM bom_resources b,
1759: pa_rbs_element_names_b a,
1760: Fnd_Languages L
1761: WHERE a.resource_type_id = 2
1762: AND b.resource_id = a.resource_source_id
1763: and L.Installed_Flag in ('I', 'B'));
1796: sysdate,
1797: fnd_global.user_id,
1798: fnd_global.login_id
1799: FROM per_all_people_f per,
1800: pa_rbs_element_names_b b,
1801: Fnd_Languages L
1802: WHERE TRUNC(sysdate) BETWEEN
1803: effective_start_date AND
1804: NVL(effective_end_date,TRUNC(sysdate))
1840: sysdate,
1841: fnd_global.user_id,
1842: fnd_global.login_id
1843: FROM PA_EVENT_TYPES E,
1844: pa_rbs_element_names_b a,
1845: Fnd_Languages L
1846: WHERE a.resource_type_id=4
1847: AND E.event_type_id=a.resource_source_id
1848: AND L.Installed_Flag in ('I', 'B'));
1882: sysdate,
1883: fnd_global.user_id,
1884: fnd_global.login_id
1885: FROM pa_expenditure_categories ec,
1886: pa_rbs_element_names_b a,
1887: Fnd_Languages L
1888: WHERE a.resource_type_id = 5
1889: AND ec.EXPENDITURE_CATEGORY_ID =
1890: a.resource_source_id
1923: sysdate,
1924: fnd_global.user_id,
1925: fnd_global.login_id
1926: FROM pa_expenditure_types ec,
1927: pa_rbs_element_names_b a,
1928: Fnd_Languages L
1929: WHERE a.resource_type_id = 6
1930: AND ec.EXPENDITURE_TYPE_ID =
1931: a.resource_source_id
1966: fnd_global.user_id,
1967: fnd_global.login_id
1968: FROM mtl_categories_v c,
1969: Fnd_Languages L,
1970: pa_rbs_element_names_b a
1971: WHERE a.resource_source_id=c.CATEGORY_ID
1972: AND a.resource_type_id=7
1973: AND L.Installed_Flag in ('I', 'B'));
1974: END IF;
2006: fnd_global.login_id
2007: FROM MTL_SYSTEM_ITEMS_tl t,
2008: MTL_SYSTEM_ITEMS_b b,
2009: pa_plan_res_defaults p,
2010: pa_rbs_element_names_b a
2011: WHERE b.inventory_item_id=t.inventory_item_id
2012: AND b.organization_id=t.organization_id
2013: AND t.organization_id = p.item_master_id
2014: AND p.resource_class_id = 3
2048: sysdate,
2049: fnd_global.user_id,
2050: fnd_global.login_id
2051: FROM per_jobs job,
2052: pa_rbs_element_names_b a,
2053: Fnd_Languages L
2054: WHERE a.resource_type_id=9
2055: AND a.resource_source_id=job.job_id
2056: AND L.Installed_Flag in ('I', 'B'));
2088: sysdate,
2089: fnd_global.user_id,
2090: fnd_global.login_id
2091: FROM hr_all_organization_units_tl tl,
2092: pa_rbs_element_names_b a,
2093: Fnd_Languages L
2094: WHERE tl.organization_id = a.resource_source_id
2095: AND a.resource_type_id = 10);
2096: END IF;
2126: fnd_global.user_id,
2127: fnd_global.login_id
2128: FROM pa_rbs_element_map map,-- For bug 3799582
2129: fnd_lookup_values lk,
2130: pa_rbs_element_names_b a
2131: WHERE map.resource_type_id = a.resource_type_id
2132: AND a.resource_source_id=map.resource_id
2133: AND a.resource_type_id=11
2134: AND lk.lookup_type = 'PA_PERSON_TYPE'
2168: sysdate,
2169: fnd_global.user_id,
2170: fnd_global.login_id
2171: FROM pa_non_labor_resources r,
2172: pa_rbs_element_names_b a,
2173: Fnd_Languages L
2174: WHERE a.resource_source_id =
2175: r.non_labor_resource_id
2176: AND a.resource_type_id = 12
2208: sysdate,
2209: fnd_global.user_id,
2210: fnd_global.login_id
2211: FROM pa_resource_classes_tl tl,
2212: pa_rbs_element_names_b a
2213: WHERE a.resource_source_id=tl.resource_class_id
2214: AND a.resource_type_id=13);
2215: END IF;
2216:
2245: sysdate,
2246: fnd_global.user_id,
2247: fnd_global.login_id
2248: FROM fnd_lookup_values lk,
2249: pa_rbs_element_names_b a,
2250: pa_rbs_element_map map
2251: WHERE a.resource_source_id=map.resource_id
2252: AND map.resource_name=lk.lookup_code
2253: AND lk.Lookup_Type = 'REVENUE CATEGORY'
2285: sysdate,
2286: fnd_global.user_id,
2287: fnd_global.login_id
2288: FROM pa_project_role_types_tl tl,
2289: pa_rbs_element_names_b a
2290: WHERE a.resource_type_id = 15
2291: AND a.resource_source_id = tl.project_role_id);
2292: END IF;
2293:
2323: sysdate,
2324: fnd_global.user_id,
2325: fnd_global.login_id
2326: FROM po_vendors v,
2327: pa_rbs_element_names_b a,
2328: Fnd_Languages L
2329: WHERE a.resource_type_id = 16
2330: AND a.resource_source_id = v.vendor_id
2331: AND L.Installed_Flag in ('I', 'B'));
2364: sysdate,
2365: fnd_global.user_id,
2366: fnd_global.login_id
2367: FROM pa_rbs_element_map map,
2368: pa_rbs_element_names_b a,
2369: Fnd_Languages L
2370: WHERE a.resource_type_id = 18
2371: AND a.resource_source_id = map.resource_id
2372: AND L.Installed_Flag in ('I', 'B'));
2968: END LOOP;
2969:
2970: --dbms_output.put_line('Call Populate_RBS_Element_Name');
2971:
2972: -- select count(*) into l_name_count from pa_rbs_element_names_b;
2973: --dbms_output.put_line('l_name_count before Populate_RBS_Element_Name is ' || l_name_count);
2974:
2975: PA_RBS_UTILS.Populate_RBS_Element_Name(
2976: p_resource_source_id => NULL,
2976: p_resource_source_id => NULL,
2977: p_resource_type_id => NULL,
2978: x_rbs_element_name_id => l_rbs_dummy_id,
2979: x_return_status => x_return_status);
2980: -- select count(*) into l_name_count from pa_rbs_element_names_b;
2981: --dbms_output.put_line('l_name_count after Populate_RBS_Element_Name is ' || l_name_count);
2982: --dbms_output.put_line('error after Populate_RBS_Element_Name is ' || sqlerrm);
2983:
2984: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2995: INTO l_rbs_identifier_id from dual;
2996:
2997:
2998: SELECT rbs_element_name_id INTO l_element_name_id
2999: FROM pa_rbs_element_names_b
3000: WHERE resource_source_id = l_rbs_version_id
3001: AND resource_type_id = -1;
3002:
3003: INSERT INTO PA_RBS_ELEMENTS (
3101:
3102: BEGIN
3103: SELECT rbs_element_name_id
3104: INTO l_element_name_id
3105: FROM pa_rbs_element_names_b
3106: WHERE resource_source_id = l_resource_source_id
3107: AND resource_type_id = l_res_type_id;
3108: EXCEPTION WHEN NO_DATA_FOUND THEN
3109: l_element_name_id := -888;
3292: FROM dual;
3293:
3294: BEGIN
3295: SELECT rbs_element_name_id INTO l_element_name_id
3296: FROM pa_rbs_element_names_b
3297: WHERE resource_source_id = l_resource_source_id
3298: AND resource_type_id = l_res_type_id;
3299: EXCEPTION WHEN NO_DATA_FOUND THEN
3300: l_element_name_id := -888;
3844: begin
3845: delete from pa_rbs_element_names_tl T
3846: where not exists
3847: (select NULL
3848: from pa_rbs_element_names_b B
3849: where B.RBS_ELEMENT_NAME_ID = T.RBS_ELEMENT_NAME_ID
3850: );
3851:
3852: update pa_rbs_element_names_tl T set (