eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_PLANNING_RESOURCE_UTILS dependencies on PA_RES_MEMBERS_TEMP

Line 1826: UPDATE pa_res_members_temp res_temp

1822: --Join to the pa_expenditure_types table.
1823: -- Fixed bug 3962699 - Removed the setting of fc_res_type_code from
1824: -- all the below selects. No need to set fc res type code - only
1825: -- need to set Exp Type.
1826: UPDATE pa_res_members_temp res_temp
1827: SET res_temp.expenditure_type =
1828: (SELECT exp.expenditure_type
1829: FROM bom_resources bom, pa_expenditure_types exp
1830: WHERE bom.resource_id = res_temp.bom_resource_id

Line 1844: UPDATE pa_res_members_temp res_temp

1840: * Update to set the value for expenditure_type
1841: *************************************************/
1842: --Bug 3615477
1843: --Also updating the value of fc_res_type_code.
1844: UPDATE pa_res_members_temp res_temp
1845: SET res_temp.expenditure_type =
1846: (SELECT n.expenditure_type
1847: FROM pa_non_labor_resources n
1848: WHERE n.non_labor_resource =

Line 1858: UPDATE pa_res_members_temp res_temp

1854: -- Get the item's exp type if it exists
1855:
1856: --Bug 3615477
1857: --Also updating the value of fc_res_type_code.
1858: UPDATE pa_res_members_temp res_temp
1859: SET res_temp.expenditure_type =
1860: PJM_COMMITMENT_UTILS.MTL_EXPENDITURE_TYPE(
1861: res_temp.organization_id, res_temp.inventory_item_id)
1862: WHERE res_temp.organization_id IS NOT NULL

Line 1899: UPDATE pa_res_members_temp res_temp

1895: * Update to set the value for rate_expenditure_type
1896: * ************************************************/
1897: --Bug 3628429
1898: --Join to the pa_expenditure_types table.
1899: UPDATE pa_res_members_temp res_temp
1900: SET res_temp.rate_expenditure_type =
1901: (SELECT exp.expenditure_type
1902: FROM bom_resources bom,pa_expenditure_types exp
1903: WHERE bom.resource_id = res_temp.bom_resource_id

Line 1914: UPDATE pa_res_members_temp res_temp

1910: * Update to use when the rate_expenditure_type is null
1911: * and non_labor_resource is not null. We use the below
1912: * Update to set the value for rate_expenditure_type
1913: *************************************************/
1914: UPDATE pa_res_members_temp res_temp
1915: SET res_temp.rate_expenditure_type =
1916: (SELECT n.expenditure_type
1917: FROM pa_non_labor_resources n
1918: WHERE n.non_labor_resource =

Line 1926: UPDATE pa_res_members_temp res_temp

1922: AND res_temp.non_labor_resource IS NOT NULL;
1923:
1924: -- Get the item's exp type if it exists
1925:
1926: UPDATE pa_res_members_temp res_temp
1927: SET res_temp.rate_expenditure_type =
1928: PJM_COMMITMENT_UTILS.MTL_EXPENDITURE_TYPE(
1929: res_temp.organization_id, res_temp.inventory_item_id)
1930: WHERE res_temp.organization_id IS NOT NULL

Line 1943: pa_res_members_temp prmt

1939: begin
1940: select nvl(prlab.resource_class_flag,'Y') into l_resource_class_flag
1941: from PA_RESOURCE_LISTS_ALL_BG prlab,
1942: PA_RESOURCE_LIST_MEMBERS prlm,
1943: pa_res_members_temp prmt
1944: where prlab.resource_list_id = prlm.resource_list_id and
1945: prlm.resource_list_member_id = prmt.resource_list_member_id and
1946: rownum = 1;
1947: exception

Line 1952: UPDATE pa_res_members_temp res_temp

1948: when others then
1949: l_resource_class_flag := 'Y';
1950: end;
1951: if l_resource_class_flag = 'N' then
1952: UPDATE pa_res_members_temp res_temp
1953: SET res_temp.rate_expenditure_type = (select expenditure_type_2
1954: from PA_RESOURCE_LIST_MEMBERS where
1955: resource_list_member_id = res_temp.resource_list_member_id)
1956: WHERE (res_temp.rate_expenditure_type IS NULL OR

Line 1959: UPDATE pa_res_members_temp res_temp

1955: resource_list_member_id = res_temp.resource_list_member_id)
1956: WHERE (res_temp.rate_expenditure_type IS NULL OR
1957: (res_temp.rate_expenditure_type = 'NO Val'));
1958: else
1959: UPDATE pa_res_members_temp res_temp
1960: SET res_temp.rate_expenditure_type =
1961: (SELECT expenditure_type
1962: FROM pa_plan_res_defaults
1963: WHERE resource_class_id =

Line 1990: UPDATE pa_res_members_temp res_temp

1986: PROCEDURE default_ou(p_project_id IN PA_PROJECTS_ALL.PROJECT_ID%TYPE)
1987: IS
1988: l_proj_ou NUMBER;
1989: BEGIN
1990: UPDATE pa_res_members_temp res_temp
1991: SET org_id = (SELECT to_number(org_information1)
1992: FROM hr_organization_information
1993: WHERE org_information_context = 'Exp Organization Defaults'
1994: AND organization_id = res_temp.organization_id

Line 2010: UPDATE pa_res_members_temp res_temp

2006: EXCEPTION WHEN NO_DATA_FOUND THEN
2007: l_proj_ou := NULL;
2008: END;
2009:
2010: UPDATE pa_res_members_temp res_temp
2011: SET org_id = l_proj_ou
2012: WHERE res_temp.org_id IS NULL;
2013:
2014: EXCEPTION

Line 2033: UPDATE pa_res_members_temp res_temp

2029: /*********************************************************
2030: * If the resource_class code in the table is 'PEOPLE' or EQUIPMENT
2031: * Then by default the Unit of measure should only be 'HOURS'
2032: *************************************************************/
2033: UPDATE pa_res_members_temp res_temp
2034: SET unit_of_measure = 'HOURS'
2035: WHERE res_temp.resource_class_code IN ('PEOPLE', 'EQUIPMENT')
2036: AND res_temp.unit_of_measure IS NULL;
2037:

Line 2046: UPDATE pa_res_members_temp res_temp

2042: * And Organization id that matches the master_item_id in
2043: * pa_plan_res_defaults. If the planning resource has an organization,
2044: * use that; if not, use item master.
2045: ***************************************************************/
2046: UPDATE pa_res_members_temp res_temp
2047: SET unit_of_measure = (SELECT primary_uom_code
2048: FROM mtl_system_items_b items
2049: WHERE items.inventory_item_id =
2050: res_temp.inventory_item_id

Line 2059: UPDATE pa_res_members_temp res_temp

2055: AND res_temp.unit_of_measure IS NULL
2056: AND res_temp.organization_id IS NOT NULL
2057: AND res_temp.inventory_item_id IS NOT NULL;
2058:
2059: UPDATE pa_res_members_temp res_temp
2060: SET unit_of_measure = (SELECT primary_uom_code
2061: FROM mtl_system_items_b items
2062: WHERE items.inventory_item_id =
2063: res_temp.inventory_item_id

Line 2082: UPDATE pa_res_members_temp res_temp

2078: * Items but there is no item in the planning resource,
2079: * and there is an expenditure type, then take the UOM of
2080: * the expenditure type.
2081: ***************************************************************/
2082: UPDATE pa_res_members_temp res_temp
2083: SET res_temp.unit_of_measure = (SELECT unit_of_measure
2084: FROM pa_expenditure_types et
2085: WHERE et.expenditure_type = res_temp.expenditure_type
2086: AND ROWNUM = 1)

Line 2097: UPDATE pa_res_members_temp res_temp

2093: /******************************************************
2094: * If the Unit of measure column is still null, then
2095: * Default it to 'DOLLARS'
2096: *********************************************************/
2097: UPDATE pa_res_members_temp res_temp
2098: SET res_temp.unit_of_measure = 'DOLLARS'
2099: WHERE res_temp.unit_of_measure IS NULL;
2100:
2101: EXCEPTION

Line 2116: UPDATE pa_res_members_temp res_temp

2112: * ******************************************************/
2113: PROCEDURE Default_Supplier
2114: IS
2115: BEGIN
2116: UPDATE pa_res_members_temp res_temp
2117: SET res_temp.vendor_id =
2118: (SELECT asgn.vendor_id
2119: FROM per_all_assignments_f asgn
2120: WHERE asgn.person_id = res_temp.person_id

Line 2143: UPDATE pa_res_members_temp res_temp

2139: PROCEDURE Default_Currency_Code
2140: IS
2141:
2142: BEGIN
2143: UPDATE pa_res_members_temp res_temp
2144: SET rate_func_curr_code = (
2145: SELECT FC.Currency_Code
2146: FROM FND_CURRENCIES FC,
2147: GL_SETS_OF_BOOKS GB,

Line 2155: UPDATE pa_res_members_temp res_temp

2151: AND IMP.Set_Of_Books_ID = GB.Set_Of_Books_ID
2152: AND FC.Currency_Code =
2153: DECODE(IMP.Set_Of_Books_ID, NULL,NULL,GB.CURRENCY_CODE));
2154:
2155: UPDATE pa_res_members_temp res_temp
2156: SET rate_func_curr_code = unit_of_measure
2157: WHERE inventory_item_id IS NOT NULL
2158: AND rate_based_flag = 'N';
2159:

Line 2162: UPDATE pa_res_members_temp res_temp

2158: AND rate_based_flag = 'N';
2159:
2160: -- Added for bug 3841920 - The UOM for all non-rate based
2161: -- transactions should be 'Currency'
2162: UPDATE pa_res_members_temp res_temp
2163: SET unit_of_measure = 'DOLLARS'
2164: WHERE inventory_item_id IS NOT NULL
2165: AND rate_based_flag = 'N';
2166:

Line 2182: UPDATE pa_res_members_temp res_temp

2178: * ******************************************************/
2179: PROCEDURE default_job
2180: IS
2181: BEGIN
2182: UPDATE pa_res_members_temp res_temp
2183: SET job_id = (SELECT job_id
2184: FROM per_all_assignments_f assn
2185: WHERE assn.person_id = res_temp.person_id
2186: AND SYSDATE BETWEEN assn.effective_start_date

Line 2194: UPDATE pa_res_members_temp res_temp

2190: AND ROWNUM = 1)
2191: WHERE res_temp.job_id IS NULL
2192: AND res_temp.person_id IS NOT NULL;
2193:
2194: UPDATE pa_res_members_temp res_temp
2195: SET job_id = (SELECT default_job_id
2196: FROM pa_project_role_types_vl role
2197: WHERE role.project_role_id = res_temp.project_role_id
2198: AND ROWNUM = 1)

Line 2221: UPDATE pa_res_members_temp res_temp

2217: IS
2218: BEGIN
2219: -- Issue with future dated or terminated employees/contingent workers?
2220:
2221: UPDATE pa_res_members_temp res_temp
2222: SET person_type_code = (SELECT
2223: decode(peo.current_employee_flag, 'Y', 'EMP', 'CWK')
2224: FROM per_all_people_f peo
2225: WHERE peo.person_id = res_temp.person_id

Line 2266: UPDATE pa_res_members_temp res_temp

2262: * and person_id IS NOT NULL.
2263: * We are updating the value for organization_id = derived value
2264: * based on person's HR org.
2265: * ***************************************************/
2266: UPDATE pa_res_members_temp res_temp
2267: SET organization_id = (SELECT a.organization_id
2268: FROM per_assignments_x a,
2269: pa_all_organizations org
2270: WHERE a.person_id = res_temp.person_id

Line 2285: UPDATE pa_res_members_temp res_temp

2281: * This Update will fire when the incurred_by_res_flag = 'N'
2282: * and bom_resource_id IS NOT NULL and organization_id IS NULL.
2283: * We are deriving the value for organization_id
2284: * ***************************************************/
2285: UPDATE pa_res_members_temp res_temp
2286: SET organization_id = (SELECT b.organization_id
2287: FROM bom_resources b
2288: WHERE b.resource_id = res_temp.bom_resource_id
2289: AND ROWNUM = 1)

Line 2299: UPDATE pa_res_members_temp res_temp

2295: * This Update will fire when the incurred_by_res_flag = 'N'
2296: * and inventory_item_id IS NOT NULL and organization_id IS NULL.
2297: * We are deriving the value for organization_id
2298: * ***************************************************/
2299: UPDATE pa_res_members_temp res_temp
2300: SET organization_id = (SELECT i.organization_id
2301: FROM mtl_system_items_b i
2302: WHERE i.inventory_item_id = res_temp.inventory_item_id
2303: AND i.organization_id =

Line 2338: UPDATE pa_res_members_temp res_temp

2334: l_organization_id := NULL;
2335: END;
2336:
2337: IF l_organization_id IS NOT NULL THEN
2338: UPDATE pa_res_members_temp res_temp
2339: SET organization_id = l_organization_id
2340: WHERE res_temp.organization_id IS NULL;
2341: END IF;
2342:

Line 2364: UPDATE pa_res_members_temp res_temp

2360: /****************************************
2361: * First Update all the rows no 'N' as by
2362: * default it should be N and not null.
2363: ******************************************/
2364: UPDATE pa_res_members_temp res_temp
2365: SET rate_based_flag = 'N';
2366:
2367: /********************************************
2368: * Update it to 'Y' for resource_class_code

Line 2371: UPDATE pa_res_members_temp res_temp

2367: /********************************************
2368: * Update it to 'Y' for resource_class_code
2369: * in 'PEOPLE' or 'EQUIPMENT'
2370: ***********************************************/
2371: UPDATE pa_res_members_temp res_temp
2372: SET rate_based_flag = 'Y'
2373: WHERE res_temp.resource_class_code in ('PEOPLE','EQUIPMENT');
2374:
2375: UPDATE pa_res_members_temp res_temp

Line 2375: UPDATE pa_res_members_temp res_temp

2371: UPDATE pa_res_members_temp res_temp
2372: SET rate_based_flag = 'Y'
2373: WHERE res_temp.resource_class_code in ('PEOPLE','EQUIPMENT');
2374:
2375: UPDATE pa_res_members_temp res_temp
2376: SET rate_based_flag = 'Y'
2377: WHERE res_temp.resource_class_code
2378: = 'MATERIAL_ITEMS'
2379: AND res_temp.inventory_item_id IS NOT NULL

Line 2399: UPDATE pa_res_members_temp res_temp

2395: * rate expenditure type if the expenditure type is null
2396: * - added an extra condition to check that either one of
2397: * expenditure type or rate expenditure type should not be null.
2398: * ********************************************************/
2399: UPDATE pa_res_members_temp res_temp
2400: SET rate_based_flag =
2401: (SELECT c.cost_rate_flag
2402: FROM pa_expenditure_types c
2403: WHERE c.expenditure_type = res_temp.expenditure_type)

Line 2512: 'PA_RES_MEMBERS_TEMP',

2508:
2509: -- Commenting out for TEMP fix to bug 4887312 - proper fix will be done soon.
2510: /*
2511: FND_STATS.SET_TABLE_STATS('PA',
2512: 'PA_RES_MEMBERS_TEMP',
2513: 100,
2514: 10,
2515: 100);
2516:

Line 2528: PA_TASK_ASSIGNMENT_UTILS.set_table_stats(PJI_UTILS.GET_PA_SCHEMA_NAME,'PA_RES_MEMBERS_TEMP',100,10,100);

2524:
2525: -- Proper Fix for 4887312 *** RAMURTHY 03/01/06 02:33 pm ***
2526: -- It solves the issue above wrt commit by the FND_STATS.SET_TABLE_STATS call
2527: -- Bug 12533202: Replaced 'PA' by PJI_UTILS.GET_PA_SCHEMA_NAME
2528: PA_TASK_ASSIGNMENT_UTILS.set_table_stats(PJI_UTILS.GET_PA_SCHEMA_NAME,'PA_RES_MEMBERS_TEMP',100,10,100);
2529: PA_TASK_ASSIGNMENT_UTILS.set_table_stats(PJI_UTILS.GET_PA_SCHEMA_NAME,'PA_RES_MEMBER_ID_TEMP',100,10,100);
2530:
2531: -- End Bug fix 4887312
2532:

Line 2537: DELETE FROM pa_res_members_temp;

2533: /***********************************************
2534: * Deleting from the temp tables in the beginning as well
2535: * to be on the safe side.
2536: ***********************************************/
2537: DELETE FROM pa_res_members_temp;
2538:
2539: DELETE FROM pa_res_member_id_temp;
2540: /************************************************************
2541: * Created a script to create 2 tables in the DB

Line 2544: * 2. pa_res_members_temp which would hold the

2540: /************************************************************
2541: * Created a script to create 2 tables in the DB
2542: * 1. pa_res_member_id_temp which would only hold the
2543: * resource_list_member_id
2544: * 2. pa_res_members_temp which would hold the
2545: * resource_list_member_id and all the other attributes
2546: * which need to be defaulted
2547: *************************************************************/
2548:

Line 2568: * pa_res_members_temp table. And in the below insert

2564: * Description : When Duplicate resource list member ID's
2565: * were passed as IN parameters into the PL/SQL
2566: * table, this proc was failing. To rectify that
2567: * we have now induded a column order_id in the
2568: * pa_res_members_temp table. And in the below insert
2569: * we are populating value into it, same as the order
2570: * id in pa_res_members_id_temp.
2571: *********************************************************/
2572: INSERT INTO pa_res_members_temp

Line 2572: INSERT INTO pa_res_members_temp

2568: * pa_res_members_temp table. And in the below insert
2569: * we are populating value into it, same as the order
2570: * id in pa_res_members_id_temp.
2571: *********************************************************/
2572: INSERT INTO pa_res_members_temp
2573: (resource_list_member_id,
2574: order_id,
2575: resource_class_flag ,
2576: resource_class_code ,

Line 2659: UPDATE pa_res_members_temp res_temp

2655: * as Null. We have however fixed the issue with the below Update.
2656: * This Update will fire when if the res_type_code is still null
2657: * and the incurred_by_res_flag='Y'
2658: ****************************************************************/
2659: UPDATE pa_res_members_temp res_temp
2660: SET res_type_code = DECODE(res_temp.person_id, NULL,
2661: DECODE(res_temp.job_id, NULL,
2662: DECODE(res_temp.person_type_code, NULL,
2663: DECODE(res_temp.incur_by_role_id, NULL,

Line 2675: * in the temp table pa_res_members_temp with the correct

2671: AND res_type_code IS NULL;
2672:
2673: /******************************************************************
2674: * Call the Procedure Default_job which will Update the Job ID
2675: * in the temp table pa_res_members_temp with the correct
2676: * value.
2677: * *****************************************************************/
2678: pa_planning_resource_utils.default_job;
2679:

Line 2682: * person_type_code in the temp table pa_res_members_temp with the correct

2678: pa_planning_resource_utils.default_job;
2679:
2680: /******************************************************************
2681: * Call the Procedure default_person_type which will Update the
2682: * person_type_code in the temp table pa_res_members_temp with the correct
2683: * value.
2684: * *****************************************************************/
2685: pa_planning_resource_utils.default_person_type;
2686:

Line 2690: * in the temp table pa_res_members_temp with the correct

2686:
2687: /******************************************************************
2688: * Call the Procedure Default_Organization which will
2689: * Update the organization_id and rate_incurred_by_org_id
2690: * in the temp table pa_res_members_temp with the correct
2691: * values.
2692: * *****************************************************************/
2693: pa_planning_resource_utils.default_organization(
2694: p_project_id => p_project_id);

Line 2699: * in the temp table pa_res_members_temp with the correct

2695:
2696: /******************************************************************
2697: * Call the Procedure Default_Expenditure_Type which will
2698: * Update the expenditure_type
2699: * in the temp table pa_res_members_temp with the correct
2700: * values.
2701: * *****************************************************************/
2702: pa_planning_resource_utils.default_expenditure_type;
2703:

Line 2707: * in the temp table pa_res_members_temp with the correct

2703:
2704: /******************************************************************
2705: * Call the Procedure Default_Rate_Expenditure_Type which will
2706: * Update the rate_expenditure_type
2707: * in the temp table pa_res_members_temp with the correct
2708: * values.
2709: * *****************************************************************/
2710: pa_planning_resource_utils.default_rate_expenditure_type;
2711:

Line 2715: * in the temp table pa_res_members_temp with the correct

2711:
2712: /******************************************************************
2713: * Call the Procedure Default_Supplier which will
2714: * Update the vendor_id
2715: * in the temp table pa_res_members_temp with the correct
2716: * values.
2717: * *****************************************************************/
2718: pa_planning_resource_utils.default_supplier;
2719:

Line 2723: * in the temp table pa_res_members_temp with the correct

2719:
2720: /******************************************************************
2721: * Call the Procedure Default_rate_based which will
2722: * Update the Rate_based_flag
2723: * in the temp table pa_res_members_temp with the correct
2724: * values.
2725: * *****************************************************************/
2726: pa_planning_resource_utils.default_rate_based;
2727:

Line 2731: * in the temp table pa_res_members_temp with the correct

2727:
2728: /******************************************************************
2729: * Call the Procedure Default_OU which will
2730: * Update the OU
2731: * in the temp table pa_res_members_temp with the correct
2732: * values.
2733: *****************************************************************/
2734: pa_planning_resource_utils.default_ou(p_project_id);
2735:

Line 2739: * in the temp table pa_res_members_temp with the correct

2735:
2736: /******************************************************************
2737: * Call the Procedure Default_UOM which will
2738: * Update the Unit_of_measure
2739: * in the temp table pa_res_members_temp with the correct
2740: * values.
2741: *****************************************************************/
2742: pa_planning_resource_utils.default_uom;
2743:

Line 2747: * in the temp table pa_res_members_temp with the correct

2743:
2744: /******************************************************************
2745: * Call the Procedure Default_Currency_Code which will
2746: * Update the Rate_Func_Curr_Code
2747: * in the temp table pa_res_members_temp with the correct
2748: * values.
2749: *****************************************************************/
2750: pa_planning_resource_utils.default_currency_code;
2751:

Line 2753: * Fetch the values that are currently there in the pa_res_members_temp

2749: *****************************************************************/
2750: pa_planning_resource_utils.default_currency_code;
2751:
2752: /*****************************************************************
2753: * Fetch the values that are currently there in the pa_res_members_temp
2754: * table into the out var's. Select it from the Temp table
2755: * pa_res_members_temp and Bulk collect it into the OUT var's.
2756: *********************************************************************/
2757: /**********************************************************

Line 2755: * pa_res_members_temp and Bulk collect it into the OUT var's.

2751:
2752: /*****************************************************************
2753: * Fetch the values that are currently there in the pa_res_members_temp
2754: * table into the out var's. Select it from the Temp table
2755: * pa_res_members_temp and Bulk collect it into the OUT var's.
2756: *********************************************************************/
2757: /**********************************************************
2758: * Added an extra order ID join between the pa_res_members_temp
2759: * and pa_res_member_id_temp table to keep in sync.

Line 2758: * Added an extra order ID join between the pa_res_members_temp

2754: * table into the out var's. Select it from the Temp table
2755: * pa_res_members_temp and Bulk collect it into the OUT var's.
2756: *********************************************************************/
2757: /**********************************************************
2758: * Added an extra order ID join between the pa_res_members_temp
2759: * and pa_res_member_id_temp table to keep in sync.
2760: * Bug - 3473425
2761: **********************************************************/
2762: /*********************************************************

Line 2837: FROM pa_res_members_temp a,

2833: x_rate_based_flag ,
2834: x_rate_expenditure_type ,
2835: x_rate_func_curr_code
2836: -- x_rate_incurred_by_org_id
2837: FROM pa_res_members_temp a,
2838: pa_res_member_id_temp b
2839: WHERE a.resource_list_member_id = b.resource_list_member_id
2840: AND a.order_id = b.order_id
2841: ORDER BY b.order_id;

Line 2848: DELETE FROM pa_res_members_temp;

2844: X_Return_Status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2845: RAISE l_exception;
2846: END IF;
2847:
2848: DELETE FROM pa_res_members_temp;
2849:
2850: DELETE FROM pa_res_member_id_temp;
2851:
2852: EXCEPTION