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 1510: -- data model (PA_RES_AVAILABILITY) to get availability information.

1506: --
1507: -- FUNCTION : Get_Resource_Avl_to_date
1508: -- DESCRIPTION: This function gets the resource available to date for staffing
1509: -- home view objects. This function is modified for PA.K using a new
1510: -- data model (PA_RES_AVAILABILITY) to get availability information.
1511: --
1512: ------------------------------------------------------------------------------------
1513: FUNCTION Get_Resource_Avl_To_Date (p_resource_id IN NUMBER,
1514: p_avl_from_date IN DATE)

Line 1525: from pa_res_availability

1521: IF p_avl_from_date is not null THEN
1522:
1523: select min(start_date)-1
1524: into l_avl_to_date
1525: from pa_res_availability
1526: where resource_id = p_resource_id
1527: and record_type = 'C'
1528: and start_date > p_avl_from_date
1529: -- Commented for 4725606 and percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_RES_CAPACITY_PERCENTAGE'));

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

1553: --
1554: -- FUNCTION : Get_Resource_ovc_to_date
1555: -- DESCRIPTION: This function gets the resource overcommited to date for staffing
1556: -- home view objects. This function is modified for PA.K using a new
1557: -- data model (PA_RES_AVAILABILITY) to get overcommitment information.
1558: --
1559: ------------------------------------------------------------------------------------
1560: FUNCTION Get_Resource_Ovc_To_Date (p_resource_id IN NUMBER,
1561: p_ovc_from_date IN DATE)

Line 1571: from pa_res_availability

1567:
1568: IF p_ovc_from_date is not null THEN
1569: select min(start_date)-1
1570: into l_ovc_to_date
1571: from pa_res_availability
1572: where resource_id = p_resource_id
1573: and record_type = 'C'
1574: and start_date > p_ovc_from_date
1575: -- Commented for 4725606 and -1 * percent < FND_NUMBER.CANONICAL_TO_NUMBER(fnd_profile.value('PA_OVERCOMMITMENT_PERCENTAGE'));

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

1597: ------------------------------------------------------------------------------------
1598: --
1599: -- FUNCTION : Get_Resource_ovc_hours
1600: -- DESCRIPTION: This function gets the resource overcommited hours for the given
1601: -- from and to dates using pa_res_availability table.
1602: --
1603: ------------------------------------------------------------------------------------
1604: FUNCTION Get_Resource_Ovc_hours(p_resource_id IN NUMBER,
1605: p_ovc_from_date IN DATE,

Line 1619: from pa_res_availability

1615: -- have to multiply by -1 since we store overcommitment as
1616: -- negative availability
1617: select sum(hours) * -1
1618: into l_ovc_hours
1619: from pa_res_availability
1620: where resource_id = p_resource_id
1621: and record_type = 'C'
1622: and start_date >= p_ovc_from_date
1623: and end_date <= p_ovc_to_date;

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

1798:
1799: -------------------------------------------------------------------------------+
1800: --
1801: -- PROCEDURE : Populate_Res_Availability
1802: -- DESCRIPTION : This Procedure populates PA_RES_AVAILABILITY for the resource
1803: -- for the given dates
1804: -- It populates the following data slices
1805: -- - (Confirmed) Availability/Overcommittment
1806: -- - (Confirmed + Provisional) Availability/Overcommittment

Line 1808: -- used to populate PA_RES_AVAILABILITY

1804: -- It populates the following data slices
1805: -- - (Confirmed) Availability/Overcommittment
1806: -- - (Confirmed + Provisional) Availability/Overcommittment
1807: -- This procedure is also called from the upgrade script
1808: -- used to populate PA_RES_AVAILABILITY
1809: --
1810: --------------------------------------------------------------------------------+
1811: PROCEDURE populate_res_availability (
1812: p_resource_id IN NUMBER,

Line 1850: 'insert into PA_RES_AVAILABILITY

1846:
1847: --For Confirmed Availability/Overcommittment
1848: /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
1849: execute immediate
1850: 'insert into PA_RES_AVAILABILITY
1851: ( start_date,
1852: end_date,
1853: percent,
1854: record_type,

Line 1892: 'insert into PA_RES_AVAILABILITY

1888: where nvl(a,0) - nvl(b,0) <> 0';
1889: */
1890: /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
1891: execute immediate
1892: 'insert into PA_RES_AVAILABILITY
1893: ( start_date,
1894: end_date,
1895: percent,
1896: record_type,

Line 1935: 'insert into PA_RES_AVAILABILITY

1931:
1932: --For Confirmed+Provisional Availability/Overcommittment
1933: /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
1934: execute immediate
1935: 'insert into PA_RES_AVAILABILITY
1936: ( start_date,
1937: end_date,
1938: percent,
1939: record_type,

Line 1976: 'insert into PA_RES_AVAILABILITY

1972: where nvl(a,0) - nvl(b,0) <> 0';
1973: */
1974: /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
1975: execute immediate
1976: 'insert into PA_RES_AVAILABILITY
1977: ( start_date,
1978: end_date,
1979: percent,
1980: record_type,

Line 2016: update pa_res_availability avl

2012: where nvl(a,0) - nvl(b,0) <> 0' using 'B', p_bstart_date, p_bend_date, l_created_by,
2013: l_last_updated_by, l_last_update_login, 'U', p_resource_id, 'N', p_bstart_date, p_bend_date;
2014:
2015: --Logic to update the null dates due to usage of lead/lag
2016: update pa_res_availability avl
2017: set start_date = p_cstart_date
2018: where resource_id = p_resource_id
2019: and record_type = 'C'
2020: and start_date is null;

Line 2022: update pa_res_availability avl

2018: where resource_id = p_resource_id
2019: and record_type = 'C'
2020: and start_date is null;
2021:
2022: update pa_res_availability avl
2023: set end_date = p_cend_date
2024: where resource_id = p_resource_id
2025: and record_type = 'C'
2026: and end_date is null;

Line 2031: from pa_res_availability

2027:
2028: --Logic to insert 0 percent records in the beginning and end
2029: select min(start_date)
2030: into l_start_date
2031: from pa_res_availability
2032: where resource_id = p_resource_id
2033: and record_type = 'C';
2034:
2035: IF l_start_date is not null THEN

Line 2039: from pa_res_availability

2035: IF l_start_date is not null THEN
2036:
2037: select percent
2038: into l_percent
2039: from pa_res_availability
2040: where resource_id = p_resource_id
2041: and record_type = 'C'
2042: and start_date = l_start_date;
2043:

Line 2048: insert into PA_RES_AVAILABILITY

2044: END IF;
2045:
2046: IF l_percent <> 0 THEN
2047:
2048: insert into PA_RES_AVAILABILITY
2049: ( start_date,
2050: end_date,
2051: percent,
2052: record_type,

Line 2069: from pa_res_availability

2065: sysdate,
2066: l_created_by,
2067: l_last_updated_by,
2068: l_last_update_login
2069: from pa_res_availability
2070: where resource_id = p_resource_id
2071: and record_type = 'C'
2072: group by 0,
2073: record_type,

Line 2085: from pa_res_availability

2081:
2082:
2083: select max(end_date)
2084: into l_end_date
2085: from pa_res_availability
2086: where resource_id = p_resource_id
2087: and record_type = 'C';
2088:
2089: IF l_end_date is not null THEN

Line 2093: from pa_res_availability

2089: IF l_end_date is not null THEN
2090:
2091: select percent
2092: into l_percent
2093: from pa_res_availability
2094: where resource_id = p_resource_id
2095: and record_type = 'C'
2096: and end_date = l_end_date;
2097: END IF;

Line 2101: insert into PA_RES_AVAILABILITY

2097: END IF;
2098:
2099: IF l_percent <> 0 THEN
2100:
2101: insert into PA_RES_AVAILABILITY
2102: ( start_date,
2103: end_date,
2104: percent,
2105: record_type,

Line 2122: from pa_res_availability

2118: sysdate,
2119: l_created_by,
2120: l_last_updated_by,
2121: l_last_update_login
2122: from pa_res_availability
2123: where resource_id = p_resource_id
2124: and record_type = 'C'
2125: group by 0,
2126: record_type,

Line 2136: update pa_res_availability avl

2132: l_last_update_login;
2133: END IF;
2134:
2135: --Logic to update the null dates due to usage of lead/lag
2136: update pa_res_availability avl
2137: set start_date = p_bstart_date
2138: where resource_id = p_resource_id
2139: and record_type = 'B'
2140: and start_date is null;

Line 2142: update pa_res_availability avl

2138: where resource_id = p_resource_id
2139: and record_type = 'B'
2140: and start_date is null;
2141:
2142: update pa_res_availability avl
2143: set end_date = p_bend_date
2144: where resource_id = p_resource_id
2145: and record_type = 'B'
2146: and end_date is null;

Line 2151: from pa_res_availability

2147:
2148: --Logic to insert 0 percent records in the beginning and end
2149: select min(start_date)
2150: into l_start_date
2151: from pa_res_availability
2152: where resource_id = p_resource_id
2153: and record_type = 'B';
2154:
2155: IF l_start_date is not null THEN

Line 2159: from pa_res_availability

2155: IF l_start_date is not null THEN
2156:
2157: select percent
2158: into l_percent
2159: from pa_res_availability
2160: where resource_id = p_resource_id
2161: and record_type = 'B'
2162: and start_date = l_start_date;
2163:

Line 2169: insert into PA_RES_AVAILABILITY

2165:
2166: IF l_percent <> 0 THEN
2167:
2168:
2169: insert into PA_RES_AVAILABILITY
2170: ( start_date,
2171: end_date,
2172: percent,
2173: record_type,

Line 2190: from pa_res_availability

2186: sysdate,
2187: l_created_by,
2188: l_last_updated_by,
2189: l_last_update_login
2190: from pa_res_availability
2191: where resource_id = p_resource_id
2192: and record_type = 'B'
2193: group by 0,
2194: record_type,

Line 2205: from pa_res_availability

2201: END IF;
2202:
2203: select max(end_date)
2204: into l_end_date
2205: from pa_res_availability
2206: where resource_id = p_resource_id
2207: and record_type = 'B';
2208:
2209: IF l_end_date is not null THEN

Line 2213: from pa_res_availability

2209: IF l_end_date is not null THEN
2210:
2211: select percent
2212: into l_percent
2213: from pa_res_availability
2214: where resource_id = p_resource_id
2215: and record_type = 'B'
2216: and end_date = l_end_date;
2217:

Line 2222: insert into PA_RES_AVAILABILITY

2218: END IF;
2219:
2220: IF l_percent <> 0 THEN
2221:
2222: insert into PA_RES_AVAILABILITY
2223: ( start_date,
2224: end_date,
2225: percent,
2226: record_type,

Line 2243: from pa_res_availability

2239: sysdate,
2240: l_created_by,
2241: l_last_updated_by,
2242: l_last_update_login
2243: from pa_res_availability
2244: where resource_id = p_resource_id
2245: and record_type = 'B'
2246: group by 0,
2247: record_type,

Line 2263: 'insert into PA_RES_AVAILABILITY

2259:
2260: --For Confirmed 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 2312: 'insert into PA_RES_AVAILABILITY

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

Line 2362: 'insert into PA_RES_AVAILABILITY

2358:
2359: --For Confirmed+Provisional Availability/Overcommittment
2360: /* Commented dynamix SQL and replaced with bind variables for Bug #3964268
2361: execute immediate
2362: 'insert into PA_RES_AVAILABILITY
2363: ( start_date,
2364: end_date,
2365: percent,
2366: record_type,

Line 2412: 'insert into PA_RES_AVAILABILITY

2408: where nvl(a,0) - nvl(b,0) <> 0 ';
2409: */
2410: /* Modified the above commented SQL as below for Bug #3964268 with bind variables */
2411: execute immediate
2412: 'insert into PA_RES_AVAILABILITY
2413: ( start_date,
2414: end_date,
2415: percent,
2416: record_type,

Line 2463: update pa_res_availability avl

2459:
2460: --dbms_output.put_line('After Insert');
2461:
2462: --Logic to update the null dates due to usage of lead/lag
2463: update pa_res_availability avl
2464: set start_date = p_cstart_date
2465: where resource_id = p_resource_id
2466: and record_type = 'C'
2467: and start_date is null;

Line 2471: update pa_res_availability avl

2467: and start_date is null;
2468:
2469: --dbms_output.put_line('After C Update 1');
2470:
2471: update pa_res_availability avl
2472: set end_date = p_cend_date
2473: where resource_id = p_resource_id
2474: and record_type = 'C'
2475: and end_date is null;

Line 2482: from pa_res_availability

2478:
2479: --Logic to insert 0 percent records in the beginning and end
2480: select min(start_date)
2481: into l_start_date
2482: from pa_res_availability
2483: where resource_id = p_resource_id
2484: and record_type = 'C';
2485:
2486: IF l_start_date is not null THEN

Line 2491: from pa_res_availability

2487:
2488: BEGIN
2489: select percent
2490: into l_percent
2491: from pa_res_availability
2492: where resource_id = p_resource_id
2493: and record_type = 'C'
2494: and start_date = l_start_date
2495: and percent = 0;

Line 2506: insert into PA_RES_AVAILABILITY

2502: END IF;
2503:
2504: IF l_percent <> 0 THEN
2505:
2506: insert into PA_RES_AVAILABILITY
2507: ( start_date,
2508: end_date,
2509: percent,
2510: record_type,

Line 2527: from pa_res_availability

2523: sysdate,
2524: l_created_by,
2525: l_last_updated_by,
2526: l_last_update_login
2527: from pa_res_availability
2528: where resource_id = p_resource_id
2529: and record_type = 'C'
2530: group by 0,
2531: record_type,

Line 2542: from pa_res_availability

2538: END IF;
2539:
2540: select max(end_date)
2541: into l_end_date
2542: from pa_res_availability
2543: where resource_id = p_resource_id
2544: and record_type = 'C';
2545:
2546: IF l_end_date is not null THEN

Line 2551: from pa_res_availability

2547:
2548: BEGIN
2549: select percent
2550: into l_percent
2551: from pa_res_availability
2552: where resource_id = p_resource_id
2553: and record_type = 'C'
2554: and end_date = l_end_date
2555: and percent = 0;

Line 2566: insert into PA_RES_AVAILABILITY

2562: END IF;
2563:
2564: IF l_percent <> 0 THEN
2565:
2566: insert into PA_RES_AVAILABILITY
2567: ( start_date,
2568: end_date,
2569: percent,
2570: record_type,

Line 2587: from pa_res_availability

2583: sysdate,
2584: l_created_by,
2585: l_last_updated_by,
2586: l_last_update_login
2587: from pa_res_availability
2588: where resource_id = p_resource_id
2589: and record_type = 'C'
2590: group by 0,
2591: record_type,

Line 2601: update pa_res_availability avl

2597: l_last_update_login;
2598: END IF;
2599:
2600: --Logic to update the null dates due to usage of lead/lag
2601: update pa_res_availability avl
2602: set start_date = p_bstart_date
2603: where resource_id = p_resource_id
2604: and record_type = 'B'
2605: and start_date is null;

Line 2607: update pa_res_availability avl

2603: where resource_id = p_resource_id
2604: and record_type = 'B'
2605: and start_date is null;
2606:
2607: update pa_res_availability avl
2608: set end_date = p_bend_date
2609: where resource_id = p_resource_id
2610: and record_type = 'B'
2611: and end_date is null;

Line 2616: from pa_res_availability

2612:
2613: --Logic to insert 0 percent records in the beginning and end
2614: select min(start_date)
2615: into l_start_date
2616: from pa_res_availability
2617: where resource_id = p_resource_id
2618: and record_type = 'B';
2619:
2620: IF l_start_date is not null THEN

Line 2625: from pa_res_availability

2621:
2622: BEGIN
2623: select percent
2624: into l_percent
2625: from pa_res_availability
2626: where resource_id = p_resource_id
2627: and record_type = 'B'
2628: and start_date = l_start_date
2629: and percent = 0;

Line 2639: insert into PA_RES_AVAILABILITY

2635: END IF;
2636:
2637: IF l_percent <> 0 THEN
2638:
2639: insert into PA_RES_AVAILABILITY
2640: ( start_date,
2641: end_date,
2642: percent,
2643: record_type,

Line 2660: from pa_res_availability

2656: sysdate,
2657: l_created_by,
2658: l_last_updated_by,
2659: l_last_update_login
2660: from pa_res_availability
2661: where resource_id = p_resource_id
2662: and record_type = 'B'
2663: group by 0,
2664: record_type,

Line 2675: from pa_res_availability

2671: END IF;
2672:
2673: select max(end_date)
2674: into l_end_date
2675: from pa_res_availability
2676: where resource_id = p_resource_id
2677: and record_type = 'B';
2678:
2679: IF l_end_date is not null THEN

Line 2684: from pa_res_availability

2680:
2681: BEGIN
2682: select percent
2683: into l_percent
2684: from pa_res_availability
2685: where resource_id = p_resource_id
2686: and record_type = 'B'
2687: and end_date = l_end_date
2688: and percent = 0;

Line 2697: insert into PA_RES_AVAILABILITY

2693: END IF;
2694:
2695: IF l_percent <> 0 THEN
2696:
2697: insert into PA_RES_AVAILABILITY
2698: ( start_date,
2699: end_date,
2700: percent,
2701: record_type,

Line 2718: from pa_res_availability

2714: sysdate,
2715: l_created_by,
2716: l_last_updated_by,
2717: l_last_update_login
2718: from pa_res_availability
2719: where resource_id = p_resource_id
2720: and record_type = 'B'
2721: group by 0,
2722: record_type,

Line 2754: update pa_res_availability

2750: into l_earliest_start_date
2751: from pa_resources_denorm
2752: where RESOURCE_ID = p_resource_id;
2753:
2754: update pa_res_availability
2755: set start_date = l_earliest_start_date
2756: where resource_id = p_resource_id
2757: and start_date < l_earliest_start_date
2758: and percent > 0;

Line 2762: from pa_res_availability

2758: and percent > 0;
2759:
2760: select count(*)
2761: into l_rec_count
2762: from pa_res_availability
2763: where resource_id = p_resource_id
2764: and start_date < l_earliest_start_date
2765: and record_type = 'B'
2766: and percent = 0;

Line 2771: from pa_res_availability

2767:
2768: IF l_rec_count > 1 THEN
2769:
2770: delete
2771: from pa_res_availability
2772: where resource_id = p_resource_id
2773: and start_date < l_earliest_start_date - 1
2774: and record_type = 'B'
2775: and percent = 0;

Line 2781: from pa_res_availability

2777: END IF;
2778:
2779: select count(*)
2780: into l_rec_count
2781: from pa_res_availability
2782: where resource_id = p_resource_id
2783: and start_date < l_earliest_start_date
2784: and record_type = 'C'
2785: and percent = 0;

Line 2790: from pa_res_availability

2786:
2787: IF l_rec_count > 1 THEN
2788:
2789: delete
2790: from pa_res_availability
2791: where resource_id = p_resource_id
2792: and start_date < l_earliest_start_date - 1
2793: and record_type = 'C'
2794: and percent = 0;

Line 2798: update pa_res_availability

2794: and percent = 0;
2795:
2796: END IF;
2797:
2798: update pa_res_availability
2799: set start_date = l_earliest_start_date -1,
2800: end_date = l_earliest_start_date -1
2801: where resource_id = p_resource_id
2802: and start_date < l_earliest_start_date

Line 2821: -- This API updates PA_RES_AVAILABILITY based

2817: --
2818: -- PROCEDURE : Update_Res_Availability
2819: -- DESCRIPTION : This Procedure is called after FIs are generated
2820: -- for any PJR assignment
2821: -- This API updates PA_RES_AVAILABILITY based
2822: -- on the new assignment created
2823: --------------------------------------------------------------------------------+
2824: PROCEDURE update_res_availability (
2825: p_resource_id IN NUMBER,

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

2864: l_start_date := trunc(p_start_date);
2865: l_end_date := trunc(p_end_date);
2866: END;
2867:
2868: --Delete all records in pa_res_availability for this resource
2869: delete
2870: from pa_res_availability
2871: where resource_id = p_resource_id;
2872:

Line 2870: from pa_res_availability

2866: END;
2867:
2868: --Delete all records in pa_res_availability for this resource
2869: delete
2870: from pa_res_availability
2871: where resource_id = p_resource_id;
2872:
2873:
2874: populate_res_availability (

Line 2924: -- to refresh PA_RES_AVAILABILITY

2920: -------------------------------------------------------------------------------+
2921: --
2922: -- PROCEDURE : Refresh_Res_Availability
2923: -- DESCRIPTION : This Procedure is called by the concurrent program
2924: -- to refresh PA_RES_AVAILABILITY
2925: --------------------------------------------------------------------------------+
2926: PROCEDURE refresh_res_availability (
2927: errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2928: retcode OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895

Line 2942: from pa_res_availability

2938: group by resource_id;
2939:
2940: CURSOR redundant_resources IS -- Added for bug 7316435
2941: select resource_id
2942: from pa_res_availability
2943: where resource_id not in (Select resource_id from pa_resources_denorm)
2944: and resource_id <> -1;
2945:
2946: l_res_count Number; --Added for bug 4928773

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

2953:
2954: FOR rec IN redundant_resources LOOP --Added for bug 7316435 (Cleaning Redundant records)
2955:
2956: delete
2957: from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id;
2958:
2959: END LOOP;
2960:
2961:

Line 2965: --Cleanup existing records in PA_RES_AVAILABILITY

2961:
2962:
2963: --SAVEPOINT s_res_avl_begin; commented for bug 4928773
2964:
2965: --Cleanup existing records in PA_RES_AVAILABILITY
2966: --delete
2967: --from PA_RES_AVAILABILITY;commented for bug 4928773
2968: l_res_count := 0;
2969: FOR rec IN resources LOOP

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

2963: --SAVEPOINT s_res_avl_begin; commented for bug 4928773
2964:
2965: --Cleanup existing records in PA_RES_AVAILABILITY
2966: --delete
2967: --from PA_RES_AVAILABILITY;commented for bug 4928773
2968: l_res_count := 0;
2969: FOR rec IN resources LOOP
2970:
2971: If l_res_count = 100 or l_res_count = 0 then --Added If block for bug 4928773

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

2974: SAVEPOINT s_res_avl_begin;
2975: end if;
2976:
2977: delete
2978: from PA_RES_AVAILABILITY where RESOURCE_ID = rec.resource_id; --Added for bug 4928773
2979:
2980: pa_resource_pvt.populate_res_availability(
2981: p_resource_id => rec.resource_id,
2982: p_cstart_date => rec.start_date,

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

3000: -- commented out for perf bug 4930256
3001: -- l_profile := FND_PROFILE.VALUE_SPECIFIC('PA_AVAILABILITY_CAL_PERIOD');
3002:
3003: delete
3004: from PA_RES_AVAILABILITY where RESOURCE_ID = -1; --Added for bug 4928773
3005:
3006: INSERT
3007: INTO PA_RES_AVAILABILITY
3008: (

Line 3007: INTO PA_RES_AVAILABILITY

3003: delete
3004: from PA_RES_AVAILABILITY where RESOURCE_ID = -1; --Added for bug 4928773
3005:
3006: INSERT
3007: INTO PA_RES_AVAILABILITY
3008: (
3009: RESOURCE_ID,
3010: START_DATE,
3011: RECORD_TYPE,