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_SCHEDULE_PVT dependencies on PA_SCHEDULES

Line 45: -- FROM PA_SCHEDULES sch

41: /* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
42:
43: -- CURSOR C1 IS SELECT calendar_id,start_date,end_date,Monday_hours,Tuesday_hours,Wednesday_hours,Thursday_hours,
44: -- Friday_hours,Saturday_hours,Sunday_hours
45: -- FROM PA_SCHEDULES sch
46: -- WHERE sch.calendar_id = p_calendar_id
47: -- AND sch.schedule_type_code = 'CALENDAR'
48: -- AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
49: -- OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)

Line 55: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES.calendar_id%TYPE

51: -- ORDER BY sch.start_date
52:
53: /* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
54:
55: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES.calendar_id%TYPE
56: INDEX BY BINARY_INTEGER;
57: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES.start_date%TYPE
58: INDEX BY BINARY_INTEGER;
59: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES.end_date%TYPE

Line 57: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES.start_date%TYPE

53: /* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
54:
55: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES.calendar_id%TYPE
56: INDEX BY BINARY_INTEGER;
57: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES.start_date%TYPE
58: INDEX BY BINARY_INTEGER;
59: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES.end_date%TYPE
60: INDEX BY BINARY_INTEGER;
61: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES.Monday_hours%TYPE

Line 59: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES.end_date%TYPE

55: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES.calendar_id%TYPE
56: INDEX BY BINARY_INTEGER;
57: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES.start_date%TYPE
58: INDEX BY BINARY_INTEGER;
59: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES.end_date%TYPE
60: INDEX BY BINARY_INTEGER;
61: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES.Monday_hours%TYPE
62: INDEX BY BINARY_INTEGER;
63: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES.Tuesday_hours%TYPE

Line 61: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES.Monday_hours%TYPE

57: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES.start_date%TYPE
58: INDEX BY BINARY_INTEGER;
59: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES.end_date%TYPE
60: INDEX BY BINARY_INTEGER;
61: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES.Monday_hours%TYPE
62: INDEX BY BINARY_INTEGER;
63: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES.Tuesday_hours%TYPE
64: INDEX BY BINARY_INTEGER;
65: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES.Wednesday_hours%TYPE

Line 63: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES.Tuesday_hours%TYPE

59: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES.end_date%TYPE
60: INDEX BY BINARY_INTEGER;
61: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES.Monday_hours%TYPE
62: INDEX BY BINARY_INTEGER;
63: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES.Tuesday_hours%TYPE
64: INDEX BY BINARY_INTEGER;
65: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES.Wednesday_hours%TYPE
66: INDEX BY BINARY_INTEGER;
67: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES.Thursday_hours%TYPE

Line 65: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES.Wednesday_hours%TYPE

61: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES.Monday_hours%TYPE
62: INDEX BY BINARY_INTEGER;
63: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES.Tuesday_hours%TYPE
64: INDEX BY BINARY_INTEGER;
65: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES.Wednesday_hours%TYPE
66: INDEX BY BINARY_INTEGER;
67: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES.Thursday_hours%TYPE
68: INDEX BY BINARY_INTEGER;
69: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES.Friday_hours%TYPE

Line 67: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES.Thursday_hours%TYPE

63: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES.Tuesday_hours%TYPE
64: INDEX BY BINARY_INTEGER;
65: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES.Wednesday_hours%TYPE
66: INDEX BY BINARY_INTEGER;
67: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES.Thursday_hours%TYPE
68: INDEX BY BINARY_INTEGER;
69: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES.Friday_hours%TYPE
70: INDEX BY BINARY_INTEGER;
71: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES.Saturday_hours%TYPE

Line 69: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES.Friday_hours%TYPE

65: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES.Wednesday_hours%TYPE
66: INDEX BY BINARY_INTEGER;
67: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES.Thursday_hours%TYPE
68: INDEX BY BINARY_INTEGER;
69: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES.Friday_hours%TYPE
70: INDEX BY BINARY_INTEGER;
71: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES.Saturday_hours%TYPE
72: INDEX BY BINARY_INTEGER;
73: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES.Sunday_hours%TYPE

Line 71: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES.Saturday_hours%TYPE

67: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES.Thursday_hours%TYPE
68: INDEX BY BINARY_INTEGER;
69: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES.Friday_hours%TYPE
70: INDEX BY BINARY_INTEGER;
71: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES.Saturday_hours%TYPE
72: INDEX BY BINARY_INTEGER;
73: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES.Sunday_hours%TYPE
74: INDEX BY BINARY_INTEGER;
75:

Line 73: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES.Sunday_hours%TYPE

69: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES.Friday_hours%TYPE
70: INDEX BY BINARY_INTEGER;
71: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES.Saturday_hours%TYPE
72: INDEX BY BINARY_INTEGER;
73: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES.Sunday_hours%TYPE
74: INDEX BY BINARY_INTEGER;
75:
76: l_calendar_id_tbl calendar_id_tbl;
77: l_start_date_tbl start_date_tbl ;

Line 134: FROM PA_SCHEDULES sch

130: l_Thursday_hours_tbl ,
131: l_Friday_hours_tbl ,
132: l_Saturday_hours_tbl ,
133: l_Sunday_hours_tbl
134: FROM PA_SCHEDULES sch
135: WHERE sch.calendar_id = p_calendar_id
136: AND sch.schedule_type_code = 'CALENDAR'
137: AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)
138: OR ( p_end_date BETWEEN sch.start_date AND sch.end_date)

Line 322: -- FROM PA_SCHEDULES_V sch

318: -- assignment_id,project_id,schedule_type_code,status_code,
319: -- system_status_code,start_date,end_date,Monday_hours,Tuesday_hours,
320: -- Wednesday_hours,Thursday_hours,
321: -- Friday_hours,Saturday_hours,Sunday_hours
322: -- FROM PA_SCHEDULES_V sch
323: -- WHERE sch.assignment_id = p_assignment_id
324: -- AND sch.schedule_type_code IN
325: -- ('OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT', 'STAFFED_ADMIN_ASSIGNMENT')
326: -- AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)

Line 333: TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE

329: -- ORDER BY start_date;
330:
331: /* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
332:
333: TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE
334: INDEX BY BINARY_INTEGER;
335: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE
336: INDEX BY BINARY_INTEGER;
337: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE

Line 335: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE

331: /* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
332:
333: TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE
334: INDEX BY BINARY_INTEGER;
335: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE
336: INDEX BY BINARY_INTEGER;
337: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE
338: INDEX BY BINARY_INTEGER;
339: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE

Line 337: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE

333: TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE
334: INDEX BY BINARY_INTEGER;
335: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE
336: INDEX BY BINARY_INTEGER;
337: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE
338: INDEX BY BINARY_INTEGER;
339: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE
340: INDEX BY BINARY_INTEGER;
341: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE

Line 339: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE

335: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE
336: INDEX BY BINARY_INTEGER;
337: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE
338: INDEX BY BINARY_INTEGER;
339: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE
340: INDEX BY BINARY_INTEGER;
341: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE
342: INDEX BY BINARY_INTEGER;
343: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE

Line 341: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE

337: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE
338: INDEX BY BINARY_INTEGER;
339: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE
340: INDEX BY BINARY_INTEGER;
341: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE
342: INDEX BY BINARY_INTEGER;
343: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE
344: INDEX BY BINARY_INTEGER;
345: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE

Line 343: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE

339: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE
340: INDEX BY BINARY_INTEGER;
341: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE
342: INDEX BY BINARY_INTEGER;
343: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE
344: INDEX BY BINARY_INTEGER;
345: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE
346: INDEX BY BINARY_INTEGER;
347: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE

Line 345: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE

341: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE
342: INDEX BY BINARY_INTEGER;
343: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE
344: INDEX BY BINARY_INTEGER;
345: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE
346: INDEX BY BINARY_INTEGER;
347: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE
348: INDEX BY BINARY_INTEGER;
349: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE

Line 347: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE

343: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE
344: INDEX BY BINARY_INTEGER;
345: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE
346: INDEX BY BINARY_INTEGER;
347: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE
348: INDEX BY BINARY_INTEGER;
349: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE
350: INDEX BY BINARY_INTEGER;
351: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE

Line 349: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE

345: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE
346: INDEX BY BINARY_INTEGER;
347: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE
348: INDEX BY BINARY_INTEGER;
349: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE
350: INDEX BY BINARY_INTEGER;
351: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE
352: INDEX BY BINARY_INTEGER;
353: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE

Line 351: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE

347: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE
348: INDEX BY BINARY_INTEGER;
349: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE
350: INDEX BY BINARY_INTEGER;
351: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE
352: INDEX BY BINARY_INTEGER;
353: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE
354: INDEX BY BINARY_INTEGER;
355: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE

Line 353: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE

349: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE
350: INDEX BY BINARY_INTEGER;
351: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE
352: INDEX BY BINARY_INTEGER;
353: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE
354: INDEX BY BINARY_INTEGER;
355: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE
356: INDEX BY BINARY_INTEGER;
357: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE

Line 355: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE

351: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE
352: INDEX BY BINARY_INTEGER;
353: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE
354: INDEX BY BINARY_INTEGER;
355: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE
356: INDEX BY BINARY_INTEGER;
357: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE
358: INDEX BY BINARY_INTEGER;
359: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE

Line 357: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE

353: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE
354: INDEX BY BINARY_INTEGER;
355: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE
356: INDEX BY BINARY_INTEGER;
357: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE
358: INDEX BY BINARY_INTEGER;
359: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE
360: INDEX BY BINARY_INTEGER;
361: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE

Line 359: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE

355: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE
356: INDEX BY BINARY_INTEGER;
357: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE
358: INDEX BY BINARY_INTEGER;
359: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE
360: INDEX BY BINARY_INTEGER;
361: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE
362: INDEX BY BINARY_INTEGER;
363: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Sunday_hours%TYPE

Line 361: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE

357: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE
358: INDEX BY BINARY_INTEGER;
359: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE
360: INDEX BY BINARY_INTEGER;
361: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE
362: INDEX BY BINARY_INTEGER;
363: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Sunday_hours%TYPE
364: INDEX BY BINARY_INTEGER;
365:

Line 363: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Sunday_hours%TYPE

359: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE
360: INDEX BY BINARY_INTEGER;
361: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE
362: INDEX BY BINARY_INTEGER;
363: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Sunday_hours%TYPE
364: INDEX BY BINARY_INTEGER;
365:
366: l_schedule_id_tbl schedule_id_tbl ;
367: l_calendar_id_tbl calendar_id_tbl ;

Line 389: l_assignment_start_date pa_schedules.start_date%TYPE;

385: i NUMBER;
386:
387: /*Bug 2335580 */
388:
389: l_assignment_start_date pa_schedules.start_date%TYPE;
390: l_assignment_end_date pa_schedules.end_date%TYPE;
391: l_assignment_calendar_id pa_schedules.calendar_id%TYPE;
392:
393: CURSOR CUR_ASSIGNMENT_SCHEDULE(x_assignment_id IN NUMBER) IS

Line 390: l_assignment_end_date pa_schedules.end_date%TYPE;

386:
387: /*Bug 2335580 */
388:
389: l_assignment_start_date pa_schedules.start_date%TYPE;
390: l_assignment_end_date pa_schedules.end_date%TYPE;
391: l_assignment_calendar_id pa_schedules.calendar_id%TYPE;
392:
393: CURSOR CUR_ASSIGNMENT_SCHEDULE(x_assignment_id IN NUMBER) IS
394: SELECT calendar_id, min(start_date), max(end_date)

Line 391: l_assignment_calendar_id pa_schedules.calendar_id%TYPE;

387: /*Bug 2335580 */
388:
389: l_assignment_start_date pa_schedules.start_date%TYPE;
390: l_assignment_end_date pa_schedules.end_date%TYPE;
391: l_assignment_calendar_id pa_schedules.calendar_id%TYPE;
392:
393: CURSOR CUR_ASSIGNMENT_SCHEDULE(x_assignment_id IN NUMBER) IS
394: SELECT calendar_id, min(start_date), max(end_date)
395: FROM PA_SCHEDULES

Line 395: FROM PA_SCHEDULES

391: l_assignment_calendar_id pa_schedules.calendar_id%TYPE;
392:
393: CURSOR CUR_ASSIGNMENT_SCHEDULE(x_assignment_id IN NUMBER) IS
394: SELECT calendar_id, min(start_date), max(end_date)
395: FROM PA_SCHEDULES
396: WHERE assignment_id = x_assignment_id
397: AND schedule_type_code='OPEN_ASSIGNMENT'
398: GROUP BY Calendar_id;
399:

Line 469: FROM PA_SCHEDULES_V sch

465: l_Thursday_hours_tbl,
466: l_Friday_hours_tbl,
467: l_Saturday_hours_tbl,
468: l_Sunday_hours_tbl
469: FROM PA_SCHEDULES_V sch
470: WHERE sch.assignment_id = p_assignment_id
471: AND sch.schedule_type_code IN
472: ('OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT', 'STAFFED_ADMIN_ASSIGNMENT')
473: AND ( ( p_start_date BETWEEN sch.start_date AND sch.end_date)

Line 742: -- FROM PA_SCHEDULES_V sch

738: -- assignment_id,project_id,schedule_type_code,status_code,
739: -- system_status_code,start_date,end_date,Monday_hours,Tuesday_hours,Wednesday_hours,
740: -- Thursday_hours,
741: -- Friday_hours,Saturday_hours,Sunday_hours
742: -- FROM PA_SCHEDULES_V sch
743: -- WHERE sch.assignment_id = p_assignment_id
744: -- AND sch.schedule_type_code IN
745: -- ('OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT', 'STAFFED_ADMIN_ASSIGNMENT')
746: -- ORDER BY start_date;

Line 750: TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE

746: -- ORDER BY start_date;
747:
748: /* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
749:
750: TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE
751: INDEX BY BINARY_INTEGER;
752: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE
753: INDEX BY BINARY_INTEGER;
754: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE

Line 752: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE

748: /* Bug - 1846658- Following lines are added to incorporate the BULK SELECT to enhance the performance*/
749:
750: TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE
751: INDEX BY BINARY_INTEGER;
752: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE
753: INDEX BY BINARY_INTEGER;
754: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE
755: INDEX BY BINARY_INTEGER;
756: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE

Line 754: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE

750: TYPE schedule_id_tbl IS TABLE OF PA_SCHEDULES_V.schedule_id%TYPE
751: INDEX BY BINARY_INTEGER;
752: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE
753: INDEX BY BINARY_INTEGER;
754: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE
755: INDEX BY BINARY_INTEGER;
756: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE
757: INDEX BY BINARY_INTEGER;
758: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE

Line 756: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE

752: TYPE calendar_id_tbl IS TABLE OF PA_SCHEDULES_V.calendar_id%TYPE
753: INDEX BY BINARY_INTEGER;
754: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE
755: INDEX BY BINARY_INTEGER;
756: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE
757: INDEX BY BINARY_INTEGER;
758: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE
759: INDEX BY BINARY_INTEGER;
760: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE

Line 758: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE

754: TYPE assignment_id_tbl IS TABLE OF PA_SCHEDULES_V.assignment_id%TYPE
755: INDEX BY BINARY_INTEGER;
756: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE
757: INDEX BY BINARY_INTEGER;
758: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE
759: INDEX BY BINARY_INTEGER;
760: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE
761: INDEX BY BINARY_INTEGER;
762: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE

Line 760: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE

756: TYPE project_id_tbl IS TABLE OF PA_SCHEDULES_V.project_id%TYPE
757: INDEX BY BINARY_INTEGER;
758: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE
759: INDEX BY BINARY_INTEGER;
760: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE
761: INDEX BY BINARY_INTEGER;
762: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE
763: INDEX BY BINARY_INTEGER;
764: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE

Line 762: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE

758: TYPE schedule_type_code_tbl IS TABLE OF PA_SCHEDULES_V.schedule_type_code%TYPE
759: INDEX BY BINARY_INTEGER;
760: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE
761: INDEX BY BINARY_INTEGER;
762: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE
763: INDEX BY BINARY_INTEGER;
764: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE
765: INDEX BY BINARY_INTEGER;
766: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE

Line 764: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE

760: TYPE status_code_tbl IS TABLE OF PA_SCHEDULES_V.status_code%TYPE
761: INDEX BY BINARY_INTEGER;
762: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE
763: INDEX BY BINARY_INTEGER;
764: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE
765: INDEX BY BINARY_INTEGER;
766: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE
767: INDEX BY BINARY_INTEGER;
768: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE

Line 766: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE

762: TYPE system_status_code_tbl IS TABLE OF PA_SCHEDULES_V.system_status_code%TYPE
763: INDEX BY BINARY_INTEGER;
764: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE
765: INDEX BY BINARY_INTEGER;
766: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE
767: INDEX BY BINARY_INTEGER;
768: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE
769: INDEX BY BINARY_INTEGER;
770: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE

Line 768: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE

764: TYPE start_date_tbl IS TABLE OF PA_SCHEDULES_V.start_date%TYPE
765: INDEX BY BINARY_INTEGER;
766: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE
767: INDEX BY BINARY_INTEGER;
768: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE
769: INDEX BY BINARY_INTEGER;
770: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE
771: INDEX BY BINARY_INTEGER;
772: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE

Line 770: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE

766: TYPE end_date_tbl IS TABLE OF PA_SCHEDULES_V.end_date%TYPE
767: INDEX BY BINARY_INTEGER;
768: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE
769: INDEX BY BINARY_INTEGER;
770: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE
771: INDEX BY BINARY_INTEGER;
772: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE
773: INDEX BY BINARY_INTEGER;
774: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE

Line 772: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE

768: TYPE Monday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Monday_hours%TYPE
769: INDEX BY BINARY_INTEGER;
770: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE
771: INDEX BY BINARY_INTEGER;
772: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE
773: INDEX BY BINARY_INTEGER;
774: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE
775: INDEX BY BINARY_INTEGER;
776: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE

Line 774: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE

770: TYPE Tuesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Tuesday_hours%TYPE
771: INDEX BY BINARY_INTEGER;
772: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE
773: INDEX BY BINARY_INTEGER;
774: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE
775: INDEX BY BINARY_INTEGER;
776: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE
777: INDEX BY BINARY_INTEGER;
778: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE

Line 776: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE

772: TYPE Wednesday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Wednesday_hours%TYPE
773: INDEX BY BINARY_INTEGER;
774: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE
775: INDEX BY BINARY_INTEGER;
776: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE
777: INDEX BY BINARY_INTEGER;
778: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE
779: INDEX BY BINARY_INTEGER;
780: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Sunday_hours%TYPE

Line 778: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE

774: TYPE Thursday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Thursday_hours%TYPE
775: INDEX BY BINARY_INTEGER;
776: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE
777: INDEX BY BINARY_INTEGER;
778: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE
779: INDEX BY BINARY_INTEGER;
780: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Sunday_hours%TYPE
781: INDEX BY BINARY_INTEGER;
782:

Line 780: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Sunday_hours%TYPE

776: TYPE Friday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Friday_hours%TYPE
777: INDEX BY BINARY_INTEGER;
778: TYPE Saturday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Saturday_hours%TYPE
779: INDEX BY BINARY_INTEGER;
780: TYPE Sunday_hours_tbl IS TABLE OF PA_SCHEDULES_V.Sunday_hours%TYPE
781: INDEX BY BINARY_INTEGER;
782:
783: l_schedule_id_tbl schedule_id_tbl ;
784: l_calendar_id_tbl calendar_id_tbl ;

Line 867: FROM PA_SCHEDULES_V sch

863: l_Thursday_hours_tbl,
864: l_Friday_hours_tbl,
865: l_Saturday_hours_tbl,
866: l_Sunday_hours_tbl
867: FROM PA_SCHEDULES_V sch
868: WHERE sch.assignment_id = p_assignment_id
869: AND sch.schedule_type_code IN ('OPEN_ASSIGNMENT','STAFFED_ASSIGNMENT', 'STAFFED_ADMIN_ASSIGNMENT')
870: ORDER BY start_date;
871:

Line 2281: -- Updating the pa schedules table

2277: END IF; --IF (p_sch_except_record.change_hours_type_code = 'PERCENTAGE')
2278:
2279:
2280: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2281: -- Updating the pa schedules table
2282: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2283: p_project_id => p_sch_except_record.project_id,
2284: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2285: p_assignment_id => p_sch_except_record.assignment_id,

Line 2458: from pa_schedules

2454: thursday_hours,
2455: friday_hours,
2456: saturday_hours,
2457: sunday_hours
2458: from pa_schedules
2459: where project_id = p_project_id and
2460: assignment_id = p_assignment_id
2461: order by start_date;
2462:

Line 4457: -- Inserting the record in PA_SCHEDULES table

4453: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab ....');
4454: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
4455:
4456: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4457: -- Inserting the record in PA_SCHEDULES table
4458: PA_SCHEDULE_PKG.insert_rows(
4459: l_new_schedule_tab,
4460: l_x_return_status,
4461: x_msg_count,

Line 4510: -- deleting the records from pa_schedules,pa_schedule_exceptions and pa_schedule_except_history */

4506:
4507: -- Storing the value for error tracking
4508: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
4509:
4510: -- deleting the records from pa_schedules,pa_schedule_exceptions and pa_schedule_except_history */
4511:
4512: DELETE pa_schedules
4513: WHERE assignment_id = p_assignment_id;
4514:

Line 4512: DELETE pa_schedules

4508: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
4509:
4510: -- deleting the records from pa_schedules,pa_schedule_exceptions and pa_schedule_except_history */
4511:
4512: DELETE pa_schedules
4513: WHERE assignment_id = p_assignment_id;
4514:
4515: DELETE pa_schedule_exceptions
4516: WHERE assignment_id = p_assignment_id;

Line 4531: DELETE pa_schedules_history

4527: end if;
4528:
4529: -- Delete entire schedules history if p_perm_delete.
4530: if FND_API.TO_BOOLEAN(p_perm_delete) then
4531: DELETE pa_schedules_history
4532: WHERE assignment_id = p_assignment_id;
4533: end if;
4534:
4535: -- Calling the Timeline api to delete the timeline records

Line 4587: l_schedule_id pa_schedules.schedule_id%TYPE;

4583:
4584:
4585: /*Added for bug 2279209 */
4586:
4587: l_schedule_id pa_schedules.schedule_id%TYPE;
4588: l_first_status pa_schedules.status_code%TYPE;
4589: l_start_date pa_schedules.start_date%TYPE;
4590: l_end_date pa_schedules.end_date%TYPE;
4591: l_loop_thru_record varchar2(1) :='Y';

Line 4588: l_first_status pa_schedules.status_code%TYPE;

4584:
4585: /*Added for bug 2279209 */
4586:
4587: l_schedule_id pa_schedules.schedule_id%TYPE;
4588: l_first_status pa_schedules.status_code%TYPE;
4589: l_start_date pa_schedules.start_date%TYPE;
4590: l_end_date pa_schedules.end_date%TYPE;
4591: l_loop_thru_record varchar2(1) :='Y';
4592: l_status_code pa_schedules.status_code%TYPE;

Line 4589: l_start_date pa_schedules.start_date%TYPE;

4585: /*Added for bug 2279209 */
4586:
4587: l_schedule_id pa_schedules.schedule_id%TYPE;
4588: l_first_status pa_schedules.status_code%TYPE;
4589: l_start_date pa_schedules.start_date%TYPE;
4590: l_end_date pa_schedules.end_date%TYPE;
4591: l_loop_thru_record varchar2(1) :='Y';
4592: l_status_code pa_schedules.status_code%TYPE;
4593:

Line 4590: l_end_date pa_schedules.end_date%TYPE;

4586:
4587: l_schedule_id pa_schedules.schedule_id%TYPE;
4588: l_first_status pa_schedules.status_code%TYPE;
4589: l_start_date pa_schedules.start_date%TYPE;
4590: l_end_date pa_schedules.end_date%TYPE;
4591: l_loop_thru_record varchar2(1) :='Y';
4592: l_status_code pa_schedules.status_code%TYPE;
4593:
4594: /* Added for bug 2329948 */

Line 4592: l_status_code pa_schedules.status_code%TYPE;

4588: l_first_status pa_schedules.status_code%TYPE;
4589: l_start_date pa_schedules.start_date%TYPE;
4590: l_end_date pa_schedules.end_date%TYPE;
4591: l_loop_thru_record varchar2(1) :='Y';
4592: l_status_code pa_schedules.status_code%TYPE;
4593:
4594: /* Added for bug 2329948 */
4595: l_first_start_date pa_schedules.start_date%TYPE;
4596: l_last_end_date pa_schedules.end_date%TYPE;

Line 4595: l_first_start_date pa_schedules.start_date%TYPE;

4591: l_loop_thru_record varchar2(1) :='Y';
4592: l_status_code pa_schedules.status_code%TYPE;
4593:
4594: /* Added for bug 2329948 */
4595: l_first_start_date pa_schedules.start_date%TYPE;
4596: l_last_end_date pa_schedules.end_date%TYPE;
4597: l_count NUMBER;
4598:
4599:

Line 4596: l_last_end_date pa_schedules.end_date%TYPE;

4592: l_status_code pa_schedules.status_code%TYPE;
4593:
4594: /* Added for bug 2329948 */
4595: l_first_start_date pa_schedules.start_date%TYPE;
4596: l_last_end_date pa_schedules.end_date%TYPE;
4597: l_count NUMBER;
4598:
4599:
4600: CURSOR C1 IS

Line 4602: FROM pa_schedules

4598:
4599:
4600: CURSOR C1 IS
4601: SELECT schedule_id, status_code, start_date, end_date
4602: FROM pa_schedules
4603: WHERE assignment_id = p_assignment_id
4604: ORDER BY start_date;
4605:
4606: CURSOR C2 IS

Line 4664: FROM pa_schedules

4660: PA_SCHEDULE_UTILS.log_message(1,'Change status API called once');
4661:
4662: SELECT min(start_date), max(end_date)
4663: INTO l_start_date, l_end_date
4664: FROM pa_schedules
4665: where assignment_id= p_assignment_id;
4666:
4667: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status');
4668: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (

Line 4753: FROM pa_schedules

4749:
4750: /* Added for bug#2329948 */
4751: SELECT COUNT(*)
4752: INTO l_count
4753: FROM pa_schedules
4754: WHERE assignment_id = p_assignment_id;
4755:
4756: OPEN C1;
4757: LOOP

Line 4846: -- If records exist in pa_schedules_history with last_approved_flag

4842: CLOSE C1;
4843: /* End of code added for bug 2329948 */
4844:
4845:
4846: -- If records exist in pa_schedules_history with last_approved_flag
4847: -- set the flag to 'N'
4848: PA_SCHEDULE_UTILS.log_message(1, 'Setting last approved flag to N');
4849: update pa_schedules_history
4850: set last_approved_flag = 'N'

Line 4849: update pa_schedules_history

4845:
4846: -- If records exist in pa_schedules_history with last_approved_flag
4847: -- set the flag to 'N'
4848: PA_SCHEDULE_UTILS.log_message(1, 'Setting last approved flag to N');
4849: update pa_schedules_history
4850: set last_approved_flag = 'N'
4851: where assignment_id = p_assignment_id
4852: and last_approved_flag = 'Y';
4853:

Line 4913: l_schedule_id pa_schedules.schedule_id%TYPE;

4909: -- added for Bug Fix: 4537865
4910:
4911:
4912: /* Added for bug 2329948 */
4913: l_schedule_id pa_schedules.schedule_id%TYPE;
4914: l_start_date pa_schedules.start_date%TYPE;
4915: l_end_date pa_schedules.end_date%TYPE;
4916: l_status_code pa_schedules.status_code%TYPE;
4917: l_first_status pa_schedules.status_code%TYPE;

Line 4914: l_start_date pa_schedules.start_date%TYPE;

4910:
4911:
4912: /* Added for bug 2329948 */
4913: l_schedule_id pa_schedules.schedule_id%TYPE;
4914: l_start_date pa_schedules.start_date%TYPE;
4915: l_end_date pa_schedules.end_date%TYPE;
4916: l_status_code pa_schedules.status_code%TYPE;
4917: l_first_status pa_schedules.status_code%TYPE;
4918: l_first_start_date pa_schedules.start_date%TYPE;

Line 4915: l_end_date pa_schedules.end_date%TYPE;

4911:
4912: /* Added for bug 2329948 */
4913: l_schedule_id pa_schedules.schedule_id%TYPE;
4914: l_start_date pa_schedules.start_date%TYPE;
4915: l_end_date pa_schedules.end_date%TYPE;
4916: l_status_code pa_schedules.status_code%TYPE;
4917: l_first_status pa_schedules.status_code%TYPE;
4918: l_first_start_date pa_schedules.start_date%TYPE;
4919: l_last_end_date pa_schedules.end_date%TYPE;

Line 4916: l_status_code pa_schedules.status_code%TYPE;

4912: /* Added for bug 2329948 */
4913: l_schedule_id pa_schedules.schedule_id%TYPE;
4914: l_start_date pa_schedules.start_date%TYPE;
4915: l_end_date pa_schedules.end_date%TYPE;
4916: l_status_code pa_schedules.status_code%TYPE;
4917: l_first_status pa_schedules.status_code%TYPE;
4918: l_first_start_date pa_schedules.start_date%TYPE;
4919: l_last_end_date pa_schedules.end_date%TYPE;
4920: l_count NUMBER;

Line 4917: l_first_status pa_schedules.status_code%TYPE;

4913: l_schedule_id pa_schedules.schedule_id%TYPE;
4914: l_start_date pa_schedules.start_date%TYPE;
4915: l_end_date pa_schedules.end_date%TYPE;
4916: l_status_code pa_schedules.status_code%TYPE;
4917: l_first_status pa_schedules.status_code%TYPE;
4918: l_first_start_date pa_schedules.start_date%TYPE;
4919: l_last_end_date pa_schedules.end_date%TYPE;
4920: l_count NUMBER;
4921:

Line 4918: l_first_start_date pa_schedules.start_date%TYPE;

4914: l_start_date pa_schedules.start_date%TYPE;
4915: l_end_date pa_schedules.end_date%TYPE;
4916: l_status_code pa_schedules.status_code%TYPE;
4917: l_first_status pa_schedules.status_code%TYPE;
4918: l_first_start_date pa_schedules.start_date%TYPE;
4919: l_last_end_date pa_schedules.end_date%TYPE;
4920: l_count NUMBER;
4921:
4922: CURSOR C1 IS

Line 4919: l_last_end_date pa_schedules.end_date%TYPE;

4915: l_end_date pa_schedules.end_date%TYPE;
4916: l_status_code pa_schedules.status_code%TYPE;
4917: l_first_status pa_schedules.status_code%TYPE;
4918: l_first_start_date pa_schedules.start_date%TYPE;
4919: l_last_end_date pa_schedules.end_date%TYPE;
4920: l_count NUMBER;
4921:
4922: CURSOR C1 IS
4923: SELECT schedule_id, status_code, start_date, end_date

Line 4924: FROM pa_schedules

4920: l_count NUMBER;
4921:
4922: CURSOR C1 IS
4923: SELECT schedule_id, status_code, start_date, end_date
4924: FROM pa_schedules
4925: WHERE assignment_id = p_assignment_id
4926: ORDER BY start_date;
4927:
4928: CURSOR C2 IS

Line 5002: FROM pa_schedules

4998:
4999: /* Added for bug#2329948 */
5000: SELECT COUNT(*)
5001: INTO l_count
5002: FROM pa_schedules
5003: WHERE assignment_id = p_assignment_id;
5004:
5005: OPEN C1;
5006: LOOP

Line 5151: from pa_schedules_history

5147: tuesday_hours, wednesday_hours, thursday_hours, friday_hours,
5148: saturday_hours, sunday_hours, change_id, last_approved_flag, request_id,
5149: program_application_id, program_id, program_update_date, creation_date,
5150: created_by, last_update_date, last_update_by, last_update_login
5151: from pa_schedules_history
5152: where assignment_id = p_assignment_id
5153: and last_approved_flag = 'Y';
5154:
5155: BEGIN

Line 5174: -- Insert row into PA_SCHEDULES

5170: x_return_status => l_x_return_status,
5171: x_msg_count => x_msg_count,
5172: x_msg_data => x_msg_data);
5173: end if;
5174: -- Insert row into PA_SCHEDULES
5175: pa_schedule_pkg.insert_rows (
5176: p_calendar_id => rec.calendar_id,
5177: p_assignment_id => rec.assignment_id ,
5178: p_project_id => rec.project_id ,

Line 5198: delete pa_schedules_history

5194:
5195: -- Call create_timeline and delete records from schedule history
5196: -- if we inserted any rows.
5197: if l_index <> 0 then
5198: delete pa_schedules_history
5199: where assignment_id = p_assignment_id
5200: and last_approved_flag = 'Y';
5201:
5202: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN

Line 5240: -- Effects: Adds schedule records for p_assignment_id to pa_schedules_history

5236: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5237: p_procedure_name => 'revert_to_last_approved');
5238: END revert_to_last_approved;
5239:
5240: -- Effects: Adds schedule records for p_assignment_id to pa_schedules_history
5241: -- if they do not already exist.
5242: -- Impl Notes: If records already exist in pa_schedule_history with change_id,
5243: -- then do nothing. Otherwise, uncheck any records with last_approved_flag
5244: -- and copy schedule records there with correct change_id with

Line 5267: from pa_schedules_history

5263:
5264: -- Contains all the records with p_change_id.
5265: CURSOR C1 IS
5266: select assignment_id
5267: from pa_schedules_history
5268: where assignment_id = p_assignment_id
5269: and change_id = p_change_id;
5270:
5271: -- Contains the schedule records for assignment_id.

Line 5279: from pa_schedules

5275: tuesday_hours, wednesday_hours, thursday_hours, friday_hours,
5276: saturday_hours, sunday_hours, creation_date, created_by, last_update_date,
5277: last_update_by, last_update_login, request_id, program_application_id,
5278: program_id, program_update_date
5279: from pa_schedules
5280: where assignment_id = p_assignment_id;
5281:
5282: BEGIN
5283: PA_SCHEDULE_UTILS.log_message(1,'Start of the update_history_table API');

Line 5286: -- If there are no records in pa_schedules_history with change_id

5282: BEGIN
5283: PA_SCHEDULE_UTILS.log_message(1,'Start of the update_history_table API');
5284: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
5285:
5286: -- If there are no records in pa_schedules_history with change_id
5287: -- then update table.
5288: open c1;
5289: fetch c1 into l_assignment_id;
5290: if c1%NOTFOUND then

Line 5293: -- Copy records in pa_schedules to pa_schedules_history

5289: fetch c1 into l_assignment_id;
5290: if c1%NOTFOUND then
5291: close c1;
5292:
5293: -- Copy records in pa_schedules to pa_schedules_history
5294: for rec in c2 loop
5295: insert into pa_schedules_history
5296: ( schedule_id, calendar_id, assignment_id, project_id,
5297: schedule_type_code, status_code, start_date, end_date, monday_hours,

Line 5295: insert into pa_schedules_history

5291: close c1;
5292:
5293: -- Copy records in pa_schedules to pa_schedules_history
5294: for rec in c2 loop
5295: insert into pa_schedules_history
5296: ( schedule_id, calendar_id, assignment_id, project_id,
5297: schedule_type_code, status_code, start_date, end_date, monday_hours,
5298: tuesday_hours, wednesday_hours, thursday_hours, friday_hours,
5299: saturday_hours, sunday_hours, change_id, last_approved_flag,

Line 5314: -- If there are records in pa_schedules_history with change_id

5310: rec.request_id, rec.program_application_id, rec.program_id,
5311: rec.program_update_date);
5312: end loop;
5313:
5314: -- If there are records in pa_schedules_history with change_id
5315: -- then do nothing.
5316: else
5317: close c1;
5318: end if;

Line 5399: l_status_code pa_schedules.status_code%TYPE;

5395: l_start_date DATE;
5396: l_end_date DATE;
5397: l_project_calendar_id NUMBER;
5398: l_assignment_type pa_project_assignments.assignment_type%TYPE;
5399: l_status_code pa_schedules.status_code%TYPE;
5400: l_apprvl_status_code pa_project_assignments.apprvl_status_code%TYPE;
5401: l_shifted_days NUMBER;
5402: l_calendar_id NUMBER;
5403:

Line 5739: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b

5735: sum(item_quantity) total_assigned_quantity,
5736: item_date,
5737: delete_flag,
5738: forecast_item_type
5739: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
5740: where (fi1.assignment_id = p_assignment_id or asgmt_sys_status_code = 'STAFFED_ASGMT_CONF' )
5741: and fi1.assignment_id = sch.assignment_id
5742: and fi1.item_date between sch.start_date and sch.end_date
5743: and sch.status_code = a.project_status_code

Line 5793: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b

5789: sum(item_quantity) total_assigned_quantity,
5790: GLOBAL_EXP_PERIOD_END_DATE week_end_date,
5791: delete_flag,
5792: forecast_item_type
5793: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
5794: where (fi1.assignment_id = p_assignment_id or asgmt_sys_status_code = 'STAFFED_ASGMT_CONF' )
5795: and fi1.item_date between l_start_date and l_end_date
5796: and fi1.assignment_id = sch.assignment_id
5797: and fi1.item_date between sch.start_date and sch.end_date

Line 5935: from pa_forecast_items fi, pa_mass_txn_asgmt_success_v mass, pa_schedules sch, pa_project_statuses a, pa_project_statuses b,

5931: IF G_AVAILABILITY_CAL_PERIOD = 'DAILY' THEN
5932: select distinct fi.assignment_id,
5933: decode (mass.assignment_id, null, 'N', 'Y') intra_txn_conflict_flag
5934: BULK COLLECT INTO l_assignment_id_tbl, l_intra_txn_conflict_flag_tbl
5935: from pa_forecast_items fi, pa_mass_txn_asgmt_success_v mass, pa_schedules sch, pa_project_statuses a, pa_project_statuses b,
5936: (select resource_id,
5937: sum(item_quantity) total_assigned_quantity,
5938: item_date,
5939: delete_flag,

Line 5947: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b

5943: item_quantity,
5944: item_date,
5945: delete_flag,
5946: forecast_item_type
5947: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
5948: where (fi1.assignment_id in (select assignment_id from pa_mass_txn_asgmt_success_v where item_type = p_item_type and item_key = p_item_key) or asgmt_sys_status_code = 'STAFFED_ASGMT_CONF' )
5949: and fi1.assignment_id = sch.assignment_id
5950: and fi1.item_date between sch.start_date and sch.end_date
5951: and sch.status_code = a.project_status_code

Line 6014: from pa_forecast_items fi, pa_mass_txn_asgmt_success_v mass, pa_schedules sch, pa_project_statuses a, pa_project_statuses b,

6010:
6011: select distinct fi.assignment_id,
6012: decode (mass.assignment_id, null, 'N', 'Y') intra_txn_flag
6013: BULK COLLECT INTO l_assignment_id_tbl, l_intra_txn_conflict_flag_tbl
6014: from pa_forecast_items fi, pa_mass_txn_asgmt_success_v mass, pa_schedules sch, pa_project_statuses a, pa_project_statuses b,
6015: (select resource_id,
6016: sum(item_quantity) total_assigned_quantity,
6017: GLOBAL_EXP_PERIOD_END_DATE,
6018: delete_flag,

Line 6026: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b

6022: item_quantity,
6023: GLOBAL_EXP_PERIOD_END_DATE,
6024: delete_flag,
6025: forecast_item_type
6026: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
6027: where (fi1.assignment_id in (select assignment_id from pa_mass_txn_asgmt_success_v where item_type = p_item_type and item_key = p_item_key) or asgmt_sys_status_code = 'STAFFED_ASGMT_CONF' )
6028: and fi1.item_date between l_start_date and l_end_date
6029: and fi1.assignment_id = sch.assignment_id
6030: and fi1.item_date between sch.start_date and sch.end_date

Line 6186: from pa_forecast_items fi, pa_schedules sch, pa_project_statuses a, pa_project_statuses b

6182: item_quantity,
6183: item_date,
6184: asgmt_sys_status_code,
6185: delete_flag
6186: from pa_forecast_items fi, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
6187: where fi.assignment_id = p_assignment_id
6188: and fi.assignment_id = sch.assignment_id
6189: and fi.item_date between sch.start_date and sch.end_date
6190: and forecast_item_type = 'A'

Line 6219: from pa_forecast_items fi, pa_schedules sch, pa_project_statuses a, pa_project_statuses b

6215: (select resource_id,
6216: sum(item_quantity) weekly_quantity,
6217: GLOBAL_EXP_PERIOD_END_DATE week_end_date,
6218: delete_flag
6219: from pa_forecast_items fi, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
6220: where fi.assignment_id = p_assignment_id
6221: and fi.assignment_id = sch.assignment_id
6222: and item_date between l_week_start_date and l_week_end_date
6223: and item_date between sch.start_date and sch.end_date

Line 8229: FROM pa_schedules_history

8225: to_char(trunc(sunday_hours,2)) sun_hours,
8226: status_code,
8227: start_date,
8228: end_date
8229: FROM pa_schedules_history
8230: WHERE assignment_id = p_assignment_id
8231: AND last_approved_flag = 'Y'
8232: AND ( (start_date <= p_start_date AND end_date >= p_end_date)
8233: OR (start_date >= p_start_date AND end_date <= p_end_date)

Line 8250: FROM pa_schedules

8246: to_char(trunc(sunday_hours,2)) sun_hours,
8247: status_code,
8248: start_date,
8249: end_date
8250: FROM pa_schedules
8251: WHERE assignment_id = p_assignment_id
8252: AND ( (start_date <= p_start_date AND end_date >= p_end_date)
8253: OR (start_date >= p_start_date AND end_date <= p_end_date)
8254: OR (start_date <= p_start_date AND p_start_date <= end_date)

Line 8684: from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b

8680: (select fi1.resource_id,
8681: fi1.item_quantity,
8682: fi1.item_date,
8683: fi1.delete_flag
8684: from pa_forecast_items fi1, pa_project_assignments asgn, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
8685: where (fi1.assignment_id = p_assignment_id
8686: or fi1.assignment_id in
8687: (select conflict_assignment_id
8688: from pa_assignment_conflict_hist

Line 8767: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b

8763: sum(item_quantity) total_assigned_quantity,
8764: GLOBAL_EXP_PERIOD_END_DATE week_end_date,
8765: delete_flag,
8766: forecast_item_type
8767: from pa_forecast_items fi1, pa_schedules sch, pa_project_statuses a, pa_project_statuses b
8768: where (fi1.assignment_id = p_assignment_id
8769: or fi1.assignment_id in (select conflict_assignment_id
8770: from pa_assignment_conflict_hist
8771: where conflict_group_id = p_conflict_group_id