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_RESOURCE_PVT dependencies on PA_RES_AVAILABILITY

Line 20: assignments, for PA.K, it uses pa_res_availability

16: FUNCTION: Get_Start_Date
17: DESCRIPTION: This Function finds the earliest available date for
18: the resource passed in (p_resource_id).
19: Instead of using forecast items table or pa_project_
20: assignments, for PA.K, it uses pa_res_availability
21: table to get available from and available to dates
22: ------------------------------------------------------------------*/
23: FUNCTION Get_Start_Date
24: (p_resource_id IN NUMBER,

Line 33: -- new cursor to get available from date using pa_res_availability

29: l_end_date DATE := '';
30: l_available_date DATE := '';
31: l_avl_profile VARCHAR2(240) := FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_RES_CAPACITY_PERCENTAGE'));
32:
33: -- new cursor to get available from date using pa_res_availability
34: -- table (PA.K enhancements)
35: CURSOR get_available_from IS
36: SELECT (max(a1.end_date)+1) available_from
37: FROM pa_res_availability a1,

Line 37: FROM pa_res_availability a1,

33: -- new cursor to get available from date using pa_res_availability
34: -- table (PA.K enhancements)
35: CURSOR get_available_from IS
36: SELECT (max(a1.end_date)+1) available_from
37: FROM pa_res_availability a1,
38: pa_res_availability a2
39: WHERE a1.resource_id = p_resource_id
40: and a1.percent < l_avl_profile
41: and a1.record_type = 'C'

Line 38: pa_res_availability a2

34: -- table (PA.K enhancements)
35: CURSOR get_available_from IS
36: SELECT (max(a1.end_date)+1) available_from
37: FROM pa_res_availability a1,
38: pa_res_availability a2
39: WHERE a1.resource_id = p_resource_id
40: and a1.percent < l_avl_profile
41: and a1.record_type = 'C'
42: and a1.start_date < sysdate

Line 49: FROM pa_res_availability a1,

45: and a2.percent >= l_avl_profile
46: and sysdate between a2.start_date and a2.end_date
47: UNION
48: SELECT min(a1.start_date) available_from
49: FROM pa_res_availability a1,
50: pa_res_availability a2
51: WHERE a1.resource_id = p_resource_id
52: and a1.percent >= l_avl_profile
53: and a1.record_type = 'C'

Line 50: pa_res_availability a2

46: and sysdate between a2.start_date and a2.end_date
47: UNION
48: SELECT min(a1.start_date) available_from
49: FROM pa_res_availability a1,
50: pa_res_availability a2
51: WHERE a1.resource_id = p_resource_id
52: and a1.percent >= l_avl_profile
53: and a1.record_type = 'C'
54: and a1.start_date >= sysdate

Line 61: FROM pa_res_availability a1

57: and a2.percent < l_avl_profile
58: and sysdate between a2.start_date and a2.end_date
59: UNION
60: SELECT min(a1.start_date) available_from
61: FROM pa_res_availability a1
62: WHERE a1.record_type = 'C'
63: and a1.resource_id = p_resource_id
64: and a1.percent >= l_avl_profile
65: and sysdate < (select min(resource_effective_start_date)

Line 131: -- new code using pa_res_availability begins here

127:
128: ------------------------------------------------- */
129:
130: --------------------------------------------------
131: -- new code using pa_res_availability begins here
132: --------------------------------------------------
133: OPEN get_available_from;
134: FETCH get_available_from INTO l_start_date;
135: CLOSE get_available_from;

Line 1411: -- data model (PA_RES_AVAILABILITY) to get availability information.

1407: --
1408: -- FUNCTION : Get_Resource_Avl_to_date
1409: -- DESCRIPTION: This function gets the resource available to date for staffing
1410: -- home view objects. This function is modified for PA.K using a new
1411: -- data model (PA_RES_AVAILABILITY) to get availability information.
1412: --
1413: ------------------------------------------------------------------------------------
1414: FUNCTION Get_Resource_Avl_To_Date (p_resource_id IN NUMBER,
1415: p_avl_from_date IN DATE)

Line 1426: from pa_res_availability

1422: IF p_avl_from_date is not null THEN
1423:
1424: select min(start_date)-1
1425: into l_avl_to_date
1426: from pa_res_availability
1427: where resource_id = p_resource_id
1428: and record_type = 'C'
1429: and start_date > p_avl_from_date
1430: -- Commented for 4725606 and percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_RES_CAPACITY_PERCENTAGE'));

Line 1458: -- data model (PA_RES_AVAILABILITY) to get overcommitment information.

1454: --
1455: -- FUNCTION : Get_Resource_ovc_to_date
1456: -- DESCRIPTION: This function gets the resource overcommited to date for staffing
1457: -- home view objects. This function is modified for PA.K using a new
1458: -- data model (PA_RES_AVAILABILITY) to get overcommitment information.
1459: --
1460: ------------------------------------------------------------------------------------
1461: FUNCTION Get_Resource_Ovc_To_Date (p_resource_id IN NUMBER,
1462: p_ovc_from_date IN DATE)

Line 1472: from pa_res_availability

1468:
1469: IF p_ovc_from_date is not null THEN
1470: select min(start_date)-1
1471: into l_ovc_to_date
1472: from pa_res_availability
1473: where resource_id = p_resource_id
1474: and record_type = 'C'
1475: and start_date > p_ovc_from_date
1476: -- Commented for 4725606 and -1 * percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_OVERCOMMITMENT_PERCENTAGE'));

Line 1502: -- from and to dates using pa_res_availability table.

1498: ------------------------------------------------------------------------------------
1499: --
1500: -- FUNCTION : Get_Resource_ovc_hours
1501: -- DESCRIPTION: This function gets the resource overcommited hours for the given
1502: -- from and to dates using pa_res_availability table.
1503: --
1504: ------------------------------------------------------------------------------------
1505: FUNCTION Get_Resource_Ovc_hours(p_resource_id IN NUMBER,
1506: p_ovc_from_date IN DATE,

Line 1520: from pa_res_availability

1516: -- have to multiply by -1 since we store overcommitment as
1517: -- negative availability
1518: select sum(hours) * -1
1519: into l_ovc_hours
1520: from pa_res_availability
1521: where resource_id = p_resource_id
1522: and record_type = 'C'
1523: and start_date >= p_ovc_from_date
1524: and end_date <= p_ovc_to_date;

Line 1703: -- DESCRIPTION : This Procedure populates PA_RES_AVAILABILITY for the resource

1699:
1700: -------------------------------------------------------------------------------+
1701: --
1702: -- PROCEDURE : Populate_Res_Availability
1703: -- DESCRIPTION : This Procedure populates PA_RES_AVAILABILITY for the resource
1704: -- for the given dates
1705: -- It populates the following data slices
1706: -- - (Confirmed) Availability/Overcommittment
1707: -- - (Confirmed + Provisional) Availability/Overcommittment

Line 1709: -- used to populate PA_RES_AVAILABILITY

1705: -- It populates the following data slices
1706: -- - (Confirmed) Availability/Overcommittment
1707: -- - (Confirmed + Provisional) Availability/Overcommittment
1708: -- This procedure is also called from the upgrade script
1709: -- used to populate PA_RES_AVAILABILITY
1710: --
1711: --------------------------------------------------------------------------------+
1712: PROCEDURE populate_res_availability (
1713: p_resource_id IN NUMBER,

Line 1751: 'insert into PA_RES_AVAILABILITY

1747:
1748: --For Confirmed Availability/Overcommittment
1749: /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
1750: execute immediate
1751: 'insert into PA_RES_AVAILABILITY
1752: ( start_date,
1753: end_date,
1754: percent,
1755: record_type,

Line 1793: 'insert into PA_RES_AVAILABILITY

1789: where nvl(a,0) - nvl(b,0) <> 0';
1790: */
1791: /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
1792: execute immediate
1793: 'insert into PA_RES_AVAILABILITY
1794: ( start_date,
1795: end_date,
1796: percent,
1797: record_type,

Line 1836: 'insert into PA_RES_AVAILABILITY

1832:
1833: --For Confirmed+Provisional Availability/Overcommittment
1834: /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
1835: execute immediate
1836: 'insert into PA_RES_AVAILABILITY
1837: ( start_date,
1838: end_date,
1839: percent,
1840: record_type,

Line 1877: 'insert into PA_RES_AVAILABILITY

1873: where nvl(a,0) - nvl(b,0) <> 0';
1874: */
1875: /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
1876: execute immediate
1877: 'insert into PA_RES_AVAILABILITY
1878: ( start_date,
1879: end_date,
1880: percent,
1881: record_type,

Line 1917: update pa_res_availability avl

1913: where nvl(a,0) - nvl(b,0) <> 0' using 'B', p_bstart_date, p_bend_date, l_created_by,
1914: l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_bstart_date, p_bend_date;
1915:
1916: --Logic to update the null dates due to usage of lead/lag
1917: update pa_res_availability avl
1918: set start_date = p_cstart_date
1919: where resource_id = p_resource_id
1920: and record_type = 'C'
1921: and start_date is null;

Line 1923: update pa_res_availability avl

1919: where resource_id = p_resource_id
1920: and record_type = 'C'
1921: and start_date is null;
1922:
1923: update pa_res_availability avl
1924: set end_date = p_cend_date
1925: where resource_id = p_resource_id
1926: and record_type = 'C'
1927: and end_date is null;

Line 1932: from pa_res_availability

1928:
1929: --Logic to insert 0 percent records in the beginning and end
1930: select min(start_date)
1931: into l_start_date
1932: from pa_res_availability
1933: where resource_id = p_resource_id
1934: and record_type = 'C';
1935:
1936: IF l_start_date is not null THEN

Line 1940: from pa_res_availability

1936: IF l_start_date is not null THEN
1937:
1938: select percent
1939: into l_percent
1940: from pa_res_availability
1941: where resource_id = p_resource_id
1942: and record_type = 'C'
1943: and start_date = l_start_date;
1944:

Line 1949: insert into PA_RES_AVAILABILITY

1945: END IF;
1946:
1947: IF l_percent <> 0 THEN
1948:
1949: insert into PA_RES_AVAILABILITY
1950: ( start_date,
1951: end_date,
1952: percent,
1953: record_type,

Line 1970: from pa_res_availability

1966: sysdate,
1967: l_created_by,
1968: l_last_updated_by,
1969: l_last_update_login
1970: from pa_res_availability
1971: where resource_id = p_resource_id
1972: and record_type = 'C'
1973: group by 0,
1974: record_type,

Line 1986: from pa_res_availability

1982:
1983:
1984: select max(end_date)
1985: into l_end_date
1986: from pa_res_availability
1987: where resource_id = p_resource_id
1988: and record_type = 'C';
1989:
1990: IF l_end_date is not null THEN

Line 1994: from pa_res_availability

1990: IF l_end_date is not null THEN
1991:
1992: select percent
1993: into l_percent
1994: from pa_res_availability
1995: where resource_id = p_resource_id
1996: and record_type = 'C'
1997: and end_date = l_end_date;
1998: END IF;

Line 2002: insert into PA_RES_AVAILABILITY

1998: END IF;
1999:
2000: IF l_percent <> 0 THEN
2001:
2002: insert into PA_RES_AVAILABILITY
2003: ( start_date,
2004: end_date,
2005: percent,
2006: record_type,

Line 2023: from pa_res_availability

2019: sysdate,
2020: l_created_by,
2021: l_last_updated_by,
2022: l_last_update_login
2023: from pa_res_availability
2024: where resource_id = p_resource_id
2025: and record_type = 'C'
2026: group by 0,
2027: record_type,

Line 2037: update pa_res_availability avl

2033: l_last_update_login;
2034: END IF;
2035:
2036: --Logic to update the null dates due to usage of lead/lag
2037: update pa_res_availability avl
2038: set start_date = p_bstart_date
2039: where resource_id = p_resource_id
2040: and record_type = 'B'
2041: and start_date is null;

Line 2043: update pa_res_availability avl

2039: where resource_id = p_resource_id
2040: and record_type = 'B'
2041: and start_date is null;
2042:
2043: update pa_res_availability avl
2044: set end_date = p_bend_date
2045: where resource_id = p_resource_id
2046: and record_type = 'B'
2047: and end_date is null;

Line 2052: from pa_res_availability

2048:
2049: --Logic to insert 0 percent records in the beginning and end
2050: select min(start_date)
2051: into l_start_date
2052: from pa_res_availability
2053: where resource_id = p_resource_id
2054: and record_type = 'B';
2055:
2056: IF l_start_date is not null THEN

Line 2060: from pa_res_availability

2056: IF l_start_date is not null THEN
2057:
2058: select percent
2059: into l_percent
2060: from pa_res_availability
2061: where resource_id = p_resource_id
2062: and record_type = 'B'
2063: and start_date = l_start_date;
2064:

Line 2070: insert into PA_RES_AVAILABILITY

2066:
2067: IF l_percent <> 0 THEN
2068:
2069:
2070: insert into PA_RES_AVAILABILITY
2071: ( start_date,
2072: end_date,
2073: percent,
2074: record_type,

Line 2091: from pa_res_availability

2087: sysdate,
2088: l_created_by,
2089: l_last_updated_by,
2090: l_last_update_login
2091: from pa_res_availability
2092: where resource_id = p_resource_id
2093: and record_type = 'B'
2094: group by 0,
2095: record_type,

Line 2106: from pa_res_availability

2102: END IF;
2103:
2104: select max(end_date)
2105: into l_end_date
2106: from pa_res_availability
2107: where resource_id = p_resource_id
2108: and record_type = 'B';
2109:
2110: IF l_end_date is not null THEN

Line 2114: from pa_res_availability

2110: IF l_end_date is not null THEN
2111:
2112: select percent
2113: into l_percent
2114: from pa_res_availability
2115: where resource_id = p_resource_id
2116: and record_type = 'B'
2117: and end_date = l_end_date;
2118:

Line 2123: insert into PA_RES_AVAILABILITY

2119: END IF;
2120:
2121: IF l_percent <> 0 THEN
2122:
2123: insert into PA_RES_AVAILABILITY
2124: ( start_date,
2125: end_date,
2126: percent,
2127: record_type,

Line 2144: from pa_res_availability

2140: sysdate,
2141: l_created_by,
2142: l_last_updated_by,
2143: l_last_update_login
2144: from pa_res_availability
2145: where resource_id = p_resource_id
2146: and record_type = 'B'
2147: group by 0,
2148: record_type,

Line 2164: 'insert into PA_RES_AVAILABILITY

2160:
2161: --For Confirmed Availability/Overcommittment
2162: /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
2163: execute immediate
2164: 'insert into PA_RES_AVAILABILITY
2165: ( start_date,
2166: end_date,
2167: percent,
2168: record_type,

Line 2213: 'insert into PA_RES_AVAILABILITY

2209: where nvl(a,0) - nvl(b,0) <> 0 ';
2210: */
2211: /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
2212: execute immediate
2213: 'insert into PA_RES_AVAILABILITY
2214: ( start_date,
2215: end_date,
2216: percent,
2217: record_type,

Line 2263: 'insert into PA_RES_AVAILABILITY

2259:
2260: --For Confirmed+Provisional Availability/Overcommittment
2261: /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
2262: execute immediate
2263: 'insert into PA_RES_AVAILABILITY
2264: ( start_date,
2265: end_date,
2266: percent,
2267: record_type,

Line 2313: 'insert into PA_RES_AVAILABILITY

2309: where nvl(a,0) - nvl(b,0) <> 0 ';
2310: */
2311: /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
2312: execute immediate
2313: 'insert into PA_RES_AVAILABILITY
2314: ( start_date,
2315: end_date,
2316: percent,
2317: record_type,

Line 2364: update pa_res_availability avl

2360:
2361: --dbms_output.put_line('After Insert');
2362:
2363: --Logic to update the null dates due to usage of lead/lag
2364: update pa_res_availability avl
2365: set start_date = p_cstart_date
2366: where resource_id = p_resource_id
2367: and record_type = 'C'
2368: and start_date is null;

Line 2372: update pa_res_availability avl

2368: and start_date is null;
2369:
2370: --dbms_output.put_line('After C Update 1');
2371:
2372: update pa_res_availability avl
2373: set end_date = p_cend_date
2374: where resource_id = p_resource_id
2375: and record_type = 'C'
2376: and end_date is null;

Line 2383: from pa_res_availability

2379:
2380: --Logic to insert 0 percent records in the beginning and end
2381: select min(start_date)
2382: into l_start_date
2383: from pa_res_availability
2384: where resource_id = p_resource_id
2385: and record_type = 'C';
2386:
2387: IF l_start_date is not null THEN

Line 2392: from pa_res_availability

2388:
2389: BEGIN
2390: select percent
2391: into l_percent
2392: from pa_res_availability
2393: where resource_id = p_resource_id
2394: and record_type = 'C'
2395: and start_date = l_start_date
2396: and percent = 0;

Line 2407: insert into PA_RES_AVAILABILITY

2403: END IF;
2404:
2405: IF l_percent <> 0 THEN
2406:
2407: insert into PA_RES_AVAILABILITY
2408: ( start_date,
2409: end_date,
2410: percent,
2411: record_type,

Line 2428: from pa_res_availability

2424: sysdate,
2425: l_created_by,
2426: l_last_updated_by,
2427: l_last_update_login
2428: from pa_res_availability
2429: where resource_id = p_resource_id
2430: and record_type = 'C'
2431: group by 0,
2432: record_type,

Line 2443: from pa_res_availability

2439: END IF;
2440:
2441: select max(end_date)
2442: into l_end_date
2443: from pa_res_availability
2444: where resource_id = p_resource_id
2445: and record_type = 'C';
2446:
2447: IF l_end_date is not null THEN

Line 2452: from pa_res_availability

2448:
2449: BEGIN
2450: select percent
2451: into l_percent
2452: from pa_res_availability
2453: where resource_id = p_resource_id
2454: and record_type = 'C'
2455: and end_date = l_end_date
2456: and percent = 0;

Line 2467: insert into PA_RES_AVAILABILITY

2463: END IF;
2464:
2465: IF l_percent <> 0 THEN
2466:
2467: insert into PA_RES_AVAILABILITY
2468: ( start_date,
2469: end_date,
2470: percent,
2471: record_type,

Line 2488: from pa_res_availability

2484: sysdate,
2485: l_created_by,
2486: l_last_updated_by,
2487: l_last_update_login
2488: from pa_res_availability
2489: where resource_id = p_resource_id
2490: and record_type = 'C'
2491: group by 0,
2492: record_type,

Line 2502: update pa_res_availability avl

2498: l_last_update_login;
2499: END IF;
2500:
2501: --Logic to update the null dates due to usage of lead/lag
2502: update pa_res_availability avl
2503: set start_date = p_bstart_date
2504: where resource_id = p_resource_id
2505: and record_type = 'B'
2506: and start_date is null;

Line 2508: update pa_res_availability avl

2504: where resource_id = p_resource_id
2505: and record_type = 'B'
2506: and start_date is null;
2507:
2508: update pa_res_availability avl
2509: set end_date = p_bend_date
2510: where resource_id = p_resource_id
2511: and record_type = 'B'
2512: and end_date is null;

Line 2517: from pa_res_availability

2513:
2514: --Logic to insert 0 percent records in the beginning and end
2515: select min(start_date)
2516: into l_start_date
2517: from pa_res_availability
2518: where resource_id = p_resource_id
2519: and record_type = 'B';
2520:
2521: IF l_start_date is not null THEN

Line 2526: from pa_res_availability

2522:
2523: BEGIN
2524: select percent
2525: into l_percent
2526: from pa_res_availability
2527: where resource_id = p_resource_id
2528: and record_type = 'B'
2529: and start_date = l_start_date
2530: and percent = 0;

Line 2540: insert into PA_RES_AVAILABILITY

2536: END IF;
2537:
2538: IF l_percent <> 0 THEN
2539:
2540: insert into PA_RES_AVAILABILITY
2541: ( start_date,
2542: end_date,
2543: percent,
2544: record_type,

Line 2561: from pa_res_availability

2557: sysdate,
2558: l_created_by,
2559: l_last_updated_by,
2560: l_last_update_login
2561: from pa_res_availability
2562: where resource_id = p_resource_id
2563: and record_type = 'B'
2564: group by 0,
2565: record_type,

Line 2576: from pa_res_availability

2572: END IF;
2573:
2574: select max(end_date)
2575: into l_end_date
2576: from pa_res_availability
2577: where resource_id = p_resource_id
2578: and record_type = 'B';
2579:
2580: IF l_end_date is not null THEN

Line 2585: from pa_res_availability

2581:
2582: BEGIN
2583: select percent
2584: into l_percent
2585: from pa_res_availability
2586: where resource_id = p_resource_id
2587: and record_type = 'B'
2588: and end_date = l_end_date
2589: and percent = 0;

Line 2598: insert into PA_RES_AVAILABILITY

2594: END IF;
2595:
2596: IF l_percent <> 0 THEN
2597:
2598: insert into PA_RES_AVAILABILITY
2599: ( start_date,
2600: end_date,
2601: percent,
2602: record_type,

Line 2619: from pa_res_availability

2615: sysdate,
2616: l_created_by,
2617: l_last_updated_by,
2618: l_last_update_login
2619: from pa_res_availability
2620: where resource_id = p_resource_id
2621: and record_type = 'B'
2622: group by 0,
2623: record_type,

Line 2655: update pa_res_availability

2651: into l_earliest_start_date
2652: from pa_resources_denorm
2653: where RESOURCE_ID = p_resource_id;
2654:
2655: update pa_res_availability
2656: set start_date = l_earliest_start_date
2657: where resource_id = p_resource_id
2658: and start_date < l_earliest_start_date
2659: and percent > 0;

Line 2663: from pa_res_availability

2659: and percent > 0;
2660:
2661: select count(*)
2662: into l_rec_count
2663: from pa_res_availability
2664: where resource_id = p_resource_id
2665: and start_date < l_earliest_start_date
2666: and record_type = 'B'
2667: and percent = 0;

Line 2672: from pa_res_availability

2668:
2669: IF l_rec_count > 1 THEN
2670:
2671: delete
2672: from pa_res_availability
2673: where resource_id = p_resource_id
2674: and start_date < l_earliest_start_date - 1
2675: and record_type = 'B'
2676: and percent = 0;

Line 2682: from pa_res_availability

2678: END IF;
2679:
2680: select count(*)
2681: into l_rec_count
2682: from pa_res_availability
2683: where resource_id = p_resource_id
2684: and start_date < l_earliest_start_date
2685: and record_type = 'C'
2686: and percent = 0;

Line 2691: from pa_res_availability

2687:
2688: IF l_rec_count > 1 THEN
2689:
2690: delete
2691: from pa_res_availability
2692: where resource_id = p_resource_id
2693: and start_date < l_earliest_start_date - 1
2694: and record_type = 'C'
2695: and percent = 0;

Line 2699: update pa_res_availability

2695: and percent = 0;
2696:
2697: END IF;
2698:
2699: update pa_res_availability
2700: set start_date = l_earliest_start_date -1,
2701: end_date = l_earliest_start_date -1
2702: where resource_id = p_resource_id
2703: and start_date < l_earliest_start_date

Line 2722: -- This API updates PA_RES_AVAILABILITY based

2718: --
2719: -- PROCEDURE : Update_Res_Availability
2720: -- DESCRIPTION : This Procedure is called after FIs are generated
2721: -- for any PJR assignment
2722: -- This API updates PA_RES_AVAILABILITY based
2723: -- on the new assignment created
2724: --------------------------------------------------------------------------------+
2725: PROCEDURE update_res_availability (
2726: p_resource_id IN NUMBER,

Line 2769: --Delete all records in pa_res_availability for this resource

2765: l_start_date := trunc(p_start_date);
2766: l_end_date := trunc(p_end_date);
2767: END;
2768:
2769: --Delete all records in pa_res_availability for this resource
2770: delete
2771: from pa_res_availability
2772: where resource_id = p_resource_id;
2773:

Line 2771: from pa_res_availability

2767: END;
2768:
2769: --Delete all records in pa_res_availability for this resource
2770: delete
2771: from pa_res_availability
2772: where resource_id = p_resource_id;
2773:
2774:
2775: populate_res_availability (

Line 2825: -- to refresh PA_RES_AVAILABILITY

2821: -------------------------------------------------------------------------------+
2822: --
2823: -- PROCEDURE : Refresh_Res_Availability
2824: -- DESCRIPTION : This Procedure is called by the concurrent program
2825: -- to refresh PA_RES_AVAILABILITY
2826: --------------------------------------------------------------------------------+
2827: PROCEDURE refresh_res_availability (
2828: errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2829: retcode OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895

Line 2843: from pa_res_availability

2839: group by resource_id;
2840:
2841: CURSOR redundant_resources IS -- Added for bug 7316435
2842: select resource_id
2843: from pa_res_availability
2844: where resource_id not in (Select resource_id from pa_resources_denorm)
2845: and resource_id <> -1;
2846:
2847: l_res_count Number; --Added for bug 4928773

Line 2858: from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id;

2854:
2855: FOR rec IN redundant_resources LOOP --Added for bug 7316435 (Cleaning Redundant records)
2856:
2857: delete
2858: from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id;
2859:
2860: END LOOP;
2861:
2862:

Line 2866: --Cleanup existing records in PA_RES_AVAILABILITY

2862:
2863:
2864: --SAVEPOINT s_res_avl_begin; commented for bug 4928773
2865:
2866: --Cleanup existing records in PA_RES_AVAILABILITY
2867: --delete
2868: --from PA_RES_AVAILABILITY;commented for bug 4928773
2869: l_res_count := 0;
2870: FOR rec IN resources LOOP

Line 2868: --from PA_RES_AVAILABILITY;commented for bug 4928773

2864: --SAVEPOINT s_res_avl_begin; commented for bug 4928773
2865:
2866: --Cleanup existing records in PA_RES_AVAILABILITY
2867: --delete
2868: --from PA_RES_AVAILABILITY;commented for bug 4928773
2869: l_res_count := 0;
2870: FOR rec IN resources LOOP
2871:
2872: If l_res_count = 100 or l_res_count = 0 then --Added If block for bug 4928773

Line 2879: from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id; --Added for bug 4928773

2875: SAVEPOINT s_res_avl_begin;
2876: end if;
2877:
2878: delete
2879: from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id; --Added for bug 4928773
2880:
2881: pa_resource_pvt.populate_res_availability(
2882: p_resource_id => rec.resource_id,
2883: p_cstart_date => rec.start_date,

Line 2905: from PA_RES_AVAILABILITY where RESOURCE_ID = -1; --Added for bug 4928773

2901: -- commented out for perf bug 4930256
2902: -- l_profile := FND_PROFILE.VALUE_SPECIFIC('PA_AVAILABILITY_CAL_PERIOD');
2903:
2904: delete
2905: from PA_RES_AVAILABILITY where RESOURCE_ID = -1; --Added for bug 4928773
2906:
2907: INSERT
2908: INTO PA_RES_AVAILABILITY
2909: (

Line 2908: INTO PA_RES_AVAILABILITY

2904: delete
2905: from PA_RES_AVAILABILITY where RESOURCE_ID = -1; --Added for bug 4928773
2906:
2907: INSERT
2908: INTO PA_RES_AVAILABILITY
2909: (
2910: RESOURCE_ID,
2911: START_DATE,
2912: RECORD_TYPE,