[Home] [Help]
86: BEGIN
87: l_st_dt_done := FALSE;
88: l_end_dt_done := FALSE;
89: l_x_sch_copy_done := FALSE;
90: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
91:
92: SELECT calendar_id,
93: start_date,
94: end_date,
157: l_next_start_date := l_curr_schedule_rec(i).end_date + 1; */ /* Commented for bug 9229210 */
158:
159: END LOOP;
160:
161: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
162: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_curr_schedule_rec,l_curr_schedule_rec.first,l_curr_schedule_rec.last,
163: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
164: END IF;
165:
166: x_return_status := l_x_return_status;
167:
168: EXCEPTION
169: WHEN OTHERS THEN
170: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171: x_msg_count := 1;
172: x_msg_data := SQLERRM;
173: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
174: p_procedure_name => 'get_existing_schedule');
262: l_st_dt_done := FALSE;
263: l_end_dt_done := FALSE;
264: l_x_sch_copy_done := FALSE;
265: -- store status success to track the error
266: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
267:
268: /* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
269:
270: -- FOR v_c1 IN C1 LOOP
347: END IF;
348:
349:
350: -- Appending the record which is being changed in above validation
351: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
352: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_curr_schedule_rec,1,1,l_out_schedule_rec,l_x_return_status,x_msg_count,
353: x_msg_data);
354: END IF;
355:
367:
368: -- If the start date is lower than the schedule start date the its schedule will be 0 and take the actual schedule
369: -- on the schedule start date
370: IF (p_start_date < l_out_schedule_rec(l_I).start_date) THEN
371: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
372: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
373: x_msg_data);
374: END IF;
375:
372: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
373: x_msg_data);
374: END IF;
375:
376: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
377: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,p_start_date =>p_start_date,
378: p_end_date =>l_out_schedule_rec(l_I).start_date -1 ,p_monday_hours =>0.00,p_tuesday_hours =>0.00,
379: p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
380: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,
380: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,
381: x_msg_data =>x_msg_data);
382: END IF;
383:
384: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
385: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
386: x_msg_data);
387: END IF;
388:
385: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
386: x_msg_data);
387: END IF;
388:
389: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
390: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
391: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
392: END IF;
393: l_x_sch_copy_done := TRUE;
394: END IF;
395: -- If the end date is beyond the end date of the schedule of the passed calendar
396: -- then its that period will be having 0 work pattern
397: IF (p_end_date > l_out_schedule_rec(l_J).end_date) THEN
398: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
399: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,
400: x_msg_count,x_msg_data);
401: END IF;
402:
399: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,
400: x_msg_count,x_msg_data);
401: END IF;
402:
403: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
404: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
405: p_start_date =>l_out_schedule_rec(l_J).end_date + 1 , p_end_date => p_end_date,p_monday_hours =>0.00,
406: p_tuesday_hours =>0.00, p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,
407: p_saturday_hours =>0.00,p_sunday_hours =>0.00,
407: p_saturday_hours =>0.00,p_sunday_hours =>0.00,
408: x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
409: END IF;
410:
411: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS AND l_x_sch_copy_done <> TRUE ) THEN
412: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
413: x_sch_record_tab, l_x_return_status,x_msg_count,x_msg_data);
414: END IF;
415:
412: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
413: x_sch_record_tab, l_x_return_status,x_msg_count,x_msg_data);
414: END IF;
415:
416: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
417: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
418: x_msg_data);
419: END IF;
420: l_x_sch_copy_done := TRUE;
431: l_out_schedule_rec(1).sunday_hours := 0.00;
432: END IF;
433:
434: IF l_x_sch_copy_done = FALSE THEN
435: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
436: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
437: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
438: END IF;
439: END IF;
440: x_return_status := l_x_return_status;
441:
442: EXCEPTION
443: WHEN OTHERS THEN
444: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
445: x_msg_count := 1;
446: x_msg_data := SQLERRM;
447: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
448: p_procedure_name => 'get_calendar_schedule');
571: BEGIN
572: l_st_dt_done := FALSE;
573: l_end_dt_done := FALSE;
574: l_x_sch_copy_done := FALSE;
575: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
576:
577: /* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
578:
579: -- FOR v_c1 IN C1 LOOP
704: l_J := l_out_schedule_rec.Last;
705: -- If the start date is falling before the start date of the schedule then its work patern will be 0
706: IF (p_start_date < l_out_schedule_rec(l_I).start_date) THEN
707:
708: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
709: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,
710: 1,
711: 1,
712: l_temp_schedule_rec,
714: x_msg_count,
715: x_msg_data);
716: END IF;
717:
718: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
719: /*bug 2335580 */
720: IF l_assignment_calendar_id IS NOT NULL THEN
721:
722: PA_Schedule_Pvt.get_calendar_schedule(l_assignment_calendar_id,
743:
744: END IF; --l_calendar_id is not null
745: END IF;
746:
747: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
748: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,
749: l_temp_schedule_rec.first, -- changed for bug 7713013
750: l_temp_schedule_rec.last, -- changed for bug 7713013
751: x_sch_record_tab,
754: x_msg_data);
755: END IF;
756:
757:
758: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
759: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,
760: l_out_schedule_rec.first,
761: l_out_schedule_rec.last,
762: x_sch_record_tab,
771:
772: -- If the end date is falling after the end date of the schedule then its work patern will be 0
773: IF (p_end_date > l_out_schedule_rec(l_J).end_date) THEN
774:
775: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
776: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,
777: 1,
778: 1,
779: l_temp_schedule_rec,
782: x_msg_data);
783: END IF;
784:
785: /*Code added for bug 2335580 */
786: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
787: IF l_assignment_calendar_id IS NOT NULL THEN
788:
789: PA_Schedule_Pvt.get_calendar_schedule(l_assignment_calendar_id,
790: l_out_schedule_rec(l_J).end_date+1,
812: END IF; --l_assignment_calendar_id is not null
813: END IF;
814:
815:
816: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS AND l_x_sch_copy_done <> TRUE ) THEN
817: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,
818: l_out_schedule_rec.first,
819: l_out_schedule_rec.last,
820: x_sch_record_tab,
822: x_msg_count,
823: x_msg_data);
824: END IF;
825:
826: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
827: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,
828: l_temp_schedule_rec.FIRST , --1,Modified for bug 4504473/4375409
829: l_temp_schedule_rec.Last , --1,Modified for bug 4504473/4375409
830: x_sch_record_tab,
849:
850: END IF;
851:
852: IF l_x_sch_copy_done = FALSE THEN
853: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
854: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,
855: l_out_schedule_rec.first,
856: l_out_schedule_rec.last,
857: x_sch_record_tab,
864:
865: x_return_status := l_x_return_status;
866: EXCEPTION
867: WHEN OTHERS THEN
868: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869: x_msg_count := 1;
870: x_msg_data := SQLERRM;
871: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
872: p_procedure_name => 'get_assignment_schedule');
974: BEGIN
975: l_st_dt_done := FALSE;
976: l_end_dt_done := FALSE;
977: l_x_sch_copy_done := FALSE;
978: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
979:
980: /* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
981:
982: -- FOR v_c1 IN C1 LOOP
1060: l_curr_schedule_rec(1).Saturday_hours := l_Saturday_hours_tbl(i);
1061: l_curr_schedule_rec(1).Sunday_hours := l_Sunday_hours_tbl(i);
1062:
1063: -- appending the record
1064: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1065: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_curr_schedule_rec,1,1,l_out_schedule_rec,l_x_return_status,
1066: x_msg_count,x_msg_data);
1067: END IF;
1068:
1069: END LOOP;
1070:
1071: IF l_x_sch_copy_done = FALSE THEN
1072:
1073: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1074: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1075: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1076: END IF;
1077: END IF;
1079: x_return_status := l_x_return_status;
1080: EXCEPTION
1081: WHEN OTHERS THEN
1082:
1083: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1084: x_msg_count := 1;
1085: x_msg_data := SQLERRM;
1086: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
1087: p_procedure_name => 'get_assignment_schedule');
1198: l_st_dt_done := FALSE;
1199: l_end_dt_done := FALSE;
1200: l_x_sch_copy_done := FALSE;
1201: l_t_first_record := FALSE;
1202: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1203: PA_SCHEDULE_UTILS.log_message(1,'first status ... '||l_x_return_status);
1204:
1205:
1206: IF ( p_source_type = 'PA_PROJECT_PARTY_ID') THEN
1213: l_resource_id := -99;
1214: END;
1215:
1216: -- Calling resource API that will return the resource id
1217: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1218: PA_RESOURCE_UTILS.get_crm_res_id( p_project_player_id => p_source_id,
1219: p_resource_id => NULL,
1220: x_jtf_resource_id => l_t_resource_id,
1221: x_return_status => l_x_return_status,
1226: END IF;
1227: ELSIF ( p_source_type = 'PA_RESOURCE_ID') THEN
1228: l_resource_id := p_source_id;
1229: -- Calling resource API That will return the resource id
1230: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1231: PA_RESOURCE_UTILS.get_crm_res_id( p_project_player_id => NULL,
1232: p_resource_id => p_source_id,
1233: x_jtf_resource_id => l_t_resource_id,
1234: x_return_status => l_x_return_status,
1239: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1240: END IF;
1241:
1242: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1243: IF ( l_x_return_status <> fnd_api.g_ret_sts_success ) THEN
1244: RAISE l_invalid_source_id;
1245: END IF;
1246: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1247:
1283:
1284: */
1285:
1286: -- Get default calendar using organization id.
1287: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1288: pa_resource_utils.get_org_defaults(
1289: p_organization_id => l_resource_organization_id,
1290: x_default_ou => l_resource_ou_id,
1291: x_default_cal_id => l_calendar_id,
1317: END IF;
1318:
1319: -- Calling the get calendar schedule procedure which will bring
1320: -- the schedule for the specified calendar id
1321: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1322: get_calendar_schedule(l_calendar_id,
1323: l_tc_start_date,
1324: l_tc_end_date,
1325: l_cur_schedule_rec,
1333: IF (p_start_date is not null) then
1334: l_tc_start_date := p_start_date;
1335: END IF;
1336:
1337: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1338: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
1339: l_cur_schedule_rec,
1340: l_cur_schedule_rec.first,
1341: l_cur_schedule_rec.last,
1353: l_J := l_out_schedule_rec.Last;
1354:
1355: IF (l_tc_start_date < l_out_schedule_rec(l_I).start_date) THEN
1356:
1357: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1358: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1359: x_msg_data);
1360: END IF;
1361:
1358: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1359: x_msg_data);
1360: END IF;
1361:
1362: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1363: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,p_start_date =>l_tc_start_date,
1364: p_end_date =>l_out_schedule_rec(l_I).start_date -1 ,p_monday_hours =>0.00,p_tuesday_hours =>0.00,
1365: p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
1366: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1365: p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
1366: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1367: END IF;
1368:
1369: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1370: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1371: x_msg_data);
1372: END IF;
1373:
1370: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1371: x_msg_data);
1372: END IF;
1373:
1374: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1375: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1376: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1377: END IF;
1378:
1381: END IF;
1382:
1383: IF (l_tc_end_date > l_out_schedule_rec(l_J).end_date) THEN
1384:
1385: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1386: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1387: x_msg_data);
1388: END IF;
1389:
1386: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1387: x_msg_data);
1388: END IF;
1389:
1390: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1391: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
1392: p_start_date =>l_out_schedule_rec(l_J).end_date + 1 , p_end_date => l_tc_end_date,p_monday_hours =>0.00,
1393: p_tuesday_hours =>0.00, p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,
1394: p_saturday_hours =>0.00,p_sunday_hours =>0.00,
1395: x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1396: END IF;
1397:
1398: IF ( l_x_sch_copy_done = FALSE ) THEN
1399: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1400: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1401: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1402: END IF;
1403:
1402: END IF;
1403:
1404: END IF;
1405:
1406: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1407: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1408: x_msg_data);
1409: END IF;
1410:
1413: END IF;
1414:
1415: IF l_x_sch_copy_done = FALSE THEN
1416:
1417: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1418: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1419: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1420: END IF;
1421: END IF;
1479:
1480: IF( l_t_first_record) THEN
1481: l_t_first_record := FALSE;
1482: ELSIF( v_c1.start_date <> (l_last_end_date + 1) ) THEN
1483: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1484: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_cur_schedule_rec,1,1,
1485: l_temp_schedule_rec,l_x_return_status,x_msg_count,x_msg_data);
1486: END IF;
1487:
1484: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_cur_schedule_rec,1,1,
1485: l_temp_schedule_rec,l_x_return_status,x_msg_count,x_msg_data);
1486: END IF;
1487:
1488: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1489: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
1490: p_start_date =>l_last_end_date + 1, p_end_date =>v_c1.start_date -1 ,
1491: p_monday_hours =>0.00,p_tuesday_hours =>0.00,p_wednesday_hours =>0.00,
1492: p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
1495: x_msg_count,x_msg_data =>x_msg_data);
1496: END IF;
1497: --PA_SCHEDULE_UTILS.log_message(2,'TEMP :',l_temp_schedule_rec);
1498:
1499: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1500: -- Appending the records
1501: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,
1502: l_out_schedule_rec,l_x_return_status,x_msg_count,x_msg_data);
1503: END IF;
1519: PA_SCHEDULE_UTILS.log_message(2,'before get resource '||to_char(l_t_start_date,'dd-mon-yyyy'));
1520: END IF;
1521:
1522: PA_SCHEDULE_UTILS.log_message(2,'before get resource '||l_x_return_status);
1523: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1524: -- Calling the get calendar schedule procedure which will bring the schedule for the specified calendar id
1525: get_calendar_schedule( v_c1.calendar_id,
1526: l_t_start_date,
1527: l_t_end_date,
1532: PA_SCHEDULE_UTILS.log_message(2,'inside get calendar CUR :',l_cur_schedule_rec);
1533: END IF;
1534: PA_SCHEDULE_UTILS.log_message(2,'after get resource CUR :',l_cur_schedule_rec);
1535:
1536: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1537: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_cur_schedule_rec,l_cur_schedule_rec.first,
1538: l_cur_schedule_rec.last,l_out_schedule_rec,l_x_return_status,x_msg_count,
1539: x_msg_data);
1540: END IF;
1552: l_J := l_out_schedule_rec.Last;
1553:
1554: IF (l_tc_start_date < l_out_schedule_rec(l_I).start_date) THEN
1555:
1556: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1557: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1558: x_msg_data);
1559: END IF;
1560:
1557: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1558: x_msg_data);
1559: END IF;
1560:
1561: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1562: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,p_start_date =>l_tc_start_date,
1563: p_end_date =>l_out_schedule_rec(l_I).start_date -1 ,p_monday_hours =>0.00,p_tuesday_hours =>0.00,
1564: p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
1565: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1564: p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
1565: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1566: END IF;
1567:
1568: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1569: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1570: x_msg_data);
1571: END IF;
1572:
1569: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1570: x_msg_data);
1571: END IF;
1572:
1573: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1574: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1575: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1576: END IF;
1577:
1580: END IF;
1581:
1582: IF (l_tc_end_date > l_out_schedule_rec(l_J).end_date) THEN
1583:
1584: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1585: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1586: x_msg_data);
1587: END IF;
1588:
1585: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1586: x_msg_data);
1587: END IF;
1588:
1589: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1590: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
1591: p_start_date =>l_out_schedule_rec(l_J).end_date + 1 , p_end_date => l_tc_end_date,p_monday_hours =>0.00,
1592: p_tuesday_hours =>0.00, p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,
1593: p_saturday_hours =>0.00,p_sunday_hours =>0.00,
1594: x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1595: END IF;
1596:
1597: IF ( l_x_sch_copy_done = FALSE ) THEN
1598: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1599: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1600: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1601: END IF;
1602:
1601: END IF;
1602:
1603: END IF;
1604:
1605: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1606: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1607: x_msg_data);
1608: END IF;
1609:
1612: END IF;
1613:
1614: IF l_x_sch_copy_done = FALSE THEN
1615:
1616: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1617: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1618: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1619: END IF;
1620: END IF;
1913: PA_SCHEDULE_UTILS.log_message(1,'last status ... '||l_x_return_status);
1914: PA_SCHEDULE_UTILS.log_message(1,'leaving get_resource_schedule');
1915: EXCEPTION
1916: WHEN l_invalid_source_id THEN
1917: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1918: x_msg_count := 1;
1919: x_msg_data := 'l_invalid_source_id';
1920: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
1921: p_procedure_name => 'get_resource_schedule');
1922: raise;
1923: WHEN OTHERS THEN
1924: x_msg_count := 1;
1925: x_msg_data := SQLERRM;
1926: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1927: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
1928: p_procedure_name => 'get_resource_schedule');
1929: raise;
1930:
1950: l_upd_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp; -- variable used for storing the records kept for updation
1951: l_ins_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp; -- variable used for storing the records kept for insertion
1952: BEGIN
1953: -- storing status for tracking the error
1954: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1955: -- checking if the passing records is empty or not
1956: IF (p_chg_sch_record_tab.count = 0 ) THEN
1957: RAISE l_empty_tab_record;
1958: ELSE
1970: ELSIF (p_chg_sch_record_tab(l_I).change_type_code = 'I') THEN
1971: PA_SCHEDULE_UTILS.log_message(1,'I .... ');
1972:
1973: -- Calling the SCHEDULE UTILS api which will append the record if it marked for insertion
1974: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1975: PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_ins_sch_record_tab,
1976: l_x_return_status,x_msg_count,x_msg_data);
1977: END IF;
1978:
1987:
1988:
1989:
1990: -- Applying the changes according to their status i.e. insert,update or delete
1991: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1992: PA_SCHEDULE_PKG.Insert_Rows(l_ins_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1993: END IF;
1994:
1995: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1991: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1992: PA_SCHEDULE_PKG.Insert_Rows(l_ins_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1993: END IF;
1994:
1995: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1996: PA_SCHEDULE_PKG.Update_Rows(l_upd_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1997: END IF;
1998:
1999: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1995: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1996: PA_SCHEDULE_PKG.Update_Rows(l_upd_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1997: END IF;
1998:
1999: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2000: PA_SCHEDULE_PKG.Delete_Rows(p_del_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
2001: END IF;
2002:
2003: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1999: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2000: PA_SCHEDULE_PKG.Delete_Rows(p_del_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
2001: END IF;
2002:
2003: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2004: PA_SCHEDULE_PVT.merge_work_pattern(p_chg_sch_record_tab(1).project_id,p_chg_sch_record_tab(1).assignment_id,l_x_return_status,x_msg_count,x_msg_data);
2005: END IF;
2006:
2007: PA_SCHEDULE_UTILS.log_message(1,'end of apply_schedule_change .... ');
2007: PA_SCHEDULE_UTILS.log_message(1,'end of apply_schedule_change .... ');
2008: x_return_status := l_x_return_status;
2009: EXCEPTION
2010: WHEN l_empty_tab_record THEN
2011: x_return_status := FND_API.G_RET_STS_SUCCESS;
2012: NULL;
2013: WHEN OTHERS THEN
2014: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2015: x_msg_count := 1;
2010: WHEN l_empty_tab_record THEN
2011: x_return_status := FND_API.G_RET_STS_SUCCESS;
2012: NULL;
2013: WHEN OTHERS THEN
2014: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2015: x_msg_count := 1;
2016: x_msg_data := SQLERRM;
2017: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2018: p_procedure_name => 'apply_schedule_change');
2045: l_t_calendar_id NUMBER;
2046: l_t_calendar_type VARCHAR2(30);
2047:
2048: BEGIN
2049: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2050:
2051: PA_SCHEDULE_UTILS.log_message(1,'Start of create_new_schedule API ..... ');
2052: PA_SCHEDULE_UTILS.log_message(1,'exception_type_code '||p_sch_except_record.exception_type_code);
2053:
2056: IF (p_sch_except_record.exception_type_code = 'CHANGE_DURATION' OR
2057: p_sch_except_record.exception_type_code = 'SHIFT_DURATION' OR
2058: p_sch_except_record.exception_type_code = 'DURATION_PATTERN_SHIFT') THEN
2059: -- Calling the procedure create_new_duration
2060: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2061: PA_SCHEDULE_PVT.create_new_duration(p_sch_except_record,
2062: p_sch_record,
2063: x_sch_record_tab,
2064: x_difference_days, -- Added for bug 7663765
2070: ELSIF (p_sch_except_record.exception_type_code = 'CHANGE_HOURS') THEN
2071: -- This procedure will create the new schedule for the given change hours exception it will create by
2072: -- changing the hours for the given period
2073: -- Calling the procedure create_new_hours
2074: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2075: PA_SCHEDULE_PVT.create_new_hours(p_sch_except_record,
2076: p_sch_record,
2077: x_sch_record_tab,
2078: l_x_return_status,
2082: ELSIF (p_sch_except_record.exception_type_code = 'CHANGE_WORK_PATTERN') THEN
2083: -- This procedure will create the new schedule for the given change work pattern exception it will create
2084: -- by just changing the work patern for the given period
2085: -- Calling the procedure create_new_pattern
2086: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2087: PA_SCHEDULE_PVT.create_new_pattern(p_sch_except_record,
2088: p_sch_record,
2089: x_sch_record_tab,
2090: l_x_return_status,
2094: ELSIF (p_sch_except_record.exception_type_code = 'CHANGE_STATUS') THEN
2095: -- This procedure will create the new schedule for the given change status exception it will create
2096: -- by changing the status for that period only
2097: -- Calling the procedure create_new_status
2098: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2099: PA_SCHEDULE_PVT.create_new_status(p_sch_except_record,
2100: p_sch_record,
2101: x_sch_record_tab,
2102: l_x_return_status,
2106: ELSIF (p_sch_except_record.exception_type_code = 'CHANGE_CALENDAR') THEN
2107: -- This procedure will create the new schedule for the given change calendar exception it will create
2108: -- by changing the calendar for that period only
2109: -- Calling the procedure create_new_calendar
2110: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2111: PA_SCHEDULE_PVT.create_new_calendar(p_sch_except_record,
2112: p_sch_record,
2113: x_sch_record_tab,
2114: l_x_return_status,
2118: END IF;
2119:
2120: PA_SCHEDULE_UTILS.log_message(1,'after calling respective APIs return x_sch_record_tab.coun '||to_char
2121: (x_sch_record_tab.count)||' Status '||l_x_return_status);
2122: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2123: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2124: p_change_type_code => 'I',
2125: x_return_status => l_x_return_status,
2126: x_msg_count => x_msg_count,
2130: x_return_status := l_x_return_status;
2131:
2132: EXCEPTION
2133: WHEN OTHERS THEN
2134: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2135: x_msg_count := 1;
2136: x_msg_data := SQLERRM;
2137: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2138: p_procedure_name => 'create_new_schedule');
2158: x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2159: )
2160: IS
2161: BEGIN
2162: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2163:
2164: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2165: PA_SCHEDULE_UTILS.check_calendar(
2166: p_calendar_id => p_sch_except_record.calendar_id,
2160: IS
2161: BEGIN
2162: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2163:
2164: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2165: PA_SCHEDULE_UTILS.check_calendar(
2166: p_calendar_id => p_sch_except_record.calendar_id,
2167: p_start_date => p_sch_except_record.start_date,
2168: p_end_date => p_sch_except_record.end_date,
2171: x_msg_data => x_msg_data);
2172: END IF;
2173:
2174: -- Calling the procedure get_calendar_schedule that will bring the new schedule of the passed calendar
2175: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2176: PA_SCHEDULE_PVT.get_calendar_schedule(p_sch_except_record.calendar_id,
2177: p_sch_except_record.start_date,
2178: p_sch_except_record.end_date,
2179: x_sch_record_tab,
2181: x_msg_count,
2182: x_msg_data
2183: );
2184: END IF;
2185: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2186: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2187: p_project_id => p_sch_except_record.project_id,
2188: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2189: p_assignment_id => p_sch_except_record.assignment_id,
2195: END IF;
2196: x_return_status := l_x_return_status;
2197: EXCEPTION
2198: WHEN OTHERS THEN
2199: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2200: x_msg_count := 1;
2201: x_msg_data := SQLERRM;
2202: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2203: p_procedure_name => 'create_new_calendar');
2232: l_t_resource_id NUMBER; -- to sto
2233: l_t_asgn_type pa_project_assignments.assignment_type%TYPE; /*Bug 5682726*/
2234: BEGIN
2235: PA_SCHEDULE_UTILS.log_message(1,'start of create new hours ... ');
2236: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2237: PA_SCHEDULE_UTILS.log_message(1,'start of create new hours ...change_hours_type ...'||
2238: p_sch_except_record.change_hours_type_code);
2239:
2240: -- For this hours code we will changed the previous schedule 's hours with the new passed
2281: INTO l_t_resource_id
2282: FROM pa_project_assignments
2283: WHERE assignment_id = p_sch_except_record.assignment_id;
2284:
2285: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2286: PA_SCHEDULE_UTILS.check_calendar(p_resource_id => l_t_resource_id,
2287: p_start_date => p_sch_except_record.start_date,
2288: p_end_date => p_sch_except_record.end_date,
2289: x_return_status => l_x_return_status,
2291: x_msg_data => x_msg_data);
2292: END IF;
2293:
2294: -- Calling the procedure get_resource_schedule that will bring the schedule associated with the resource
2295: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2296: PA_SCHEDULE_PVT.get_resource_schedule(l_t_team_player_id,
2297: 'PA_PROJECT_PARTY_ID',
2298: p_sch_except_record.start_date,
2299: p_sch_except_record.end_date,
2325: INTO l_t_calendar_id
2326: FROM pa_project_assignments
2327: where assignment_id = p_sch_except_record.assignment_id;
2328:
2329: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2330: PA_SCHEDULE_UTILS.check_calendar(p_calendar_id => l_t_calendar_id,
2331: p_start_date => p_sch_except_record.start_date,
2332: p_end_date => p_sch_except_record.end_date,
2333: x_return_status => l_x_return_status,
2337:
2338:
2339: -- Calling the procedure get_calendar_schedule that will bring the schedule associated with the
2340: -- assignment on the basis of passed calendar id
2341: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2342: PA_SCHEDULE_PVT.get_calendar_schedule(l_t_calendar_id,
2343: p_sch_except_record.start_date,
2344: p_sch_except_record.end_date,
2345: x_sch_record_tab,
2401: INTO l_t_resource_id
2402: FROM pa_project_assignments
2403: WHERE assignment_id = p_sch_except_record.assignment_id;
2404:
2405: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2406: PA_SCHEDULE_UTILS.check_calendar(p_resource_id => l_t_resource_id,
2407: p_start_date => p_sch_except_record.start_date,
2408: p_end_date => p_sch_except_record.end_date,
2409: x_return_status => l_x_return_status,
2411: x_msg_data => x_msg_data);
2412: END IF;
2413:
2414: -- Calling the procedure get_resource_schedule that will bring the schedule associated with the resource
2415: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2416: PA_SCHEDULE_PVT.get_resource_schedule(l_t_team_player_id,
2417: 'PA_PROJECT_PARTY_ID',
2418: p_sch_except_record.start_date,
2419: p_sch_except_record.end_date,
2435: ELSIF (p_sch_except_record.change_calendar_type_code = 'OTHER') THEN
2436: l_t_calendar_id := p_sch_except_record.change_calendar_id;
2437: END IF;
2438:
2439: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2440: PA_SCHEDULE_UTILS.check_calendar(
2441: p_calendar_id => l_t_calendar_id,
2442: p_start_date => p_sch_except_record.start_date,
2443: p_end_date => p_sch_except_record.end_date,
2448:
2449:
2450: -- Calling the procedure get_calendar_schedule that will bring the schedule associated with the
2451: -- assignment on the basis of passed calendar id
2452: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2453: PA_SCHEDULE_PVT.get_calendar_schedule(l_t_calendar_id,
2454: p_sch_except_record.start_date,
2455: p_sch_except_record.end_date,
2456: x_sch_record_tab,
2459: x_msg_data);
2460: END IF;
2461: END IF;
2462:
2463: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2464: -- Calling the schedule API
2465: PA_SCHEDULE_UTILS.apply_percentage(x_sch_record_tab,
2466: p_sch_except_record.resource_calendar_percent,
2467: l_x_return_status,
2471:
2472: END IF; --IF (p_sch_except_record.change_hours_type_code = 'PERCENTAGE')
2473:
2474:
2475: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2476: -- Updating the pa schedules table
2477: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2478: p_project_id => p_sch_except_record.project_id,
2479: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2488:
2489: x_return_status := l_x_return_status;
2490: EXCEPTION
2491: WHEN OTHERS THEN
2492: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2493: x_msg_count := 1;
2494: x_msg_data := SQLERRM;
2495: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2496: p_procedure_name => 'create_new_hours');
2522: l_t_res_cal_percent NUMBER;
2523: l_t_calendar_id NUMBER;
2524: l_t_calendar_type VARCHAR2(30);
2525: BEGIN
2526: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2527:
2528: PA_SCHEDULE_UTILS.log_message(1,'Start of create_new_duration API .........');
2529: PA_SCHEDULE_UTILS.log_message(1,'schedule_type_code '||p_sch_except_record.schedule_type_code);
2530: -- Bug 1580455: added STAFFED_ADMIN_ASSIGNMENT case.
2551: WHERE assignment_id = p_sch_except_record.assignment_id;
2552:
2553: IF ( l_t_calendar_type = 'RESOURCE' ) THEN
2554: -- Calling the procedure get_resource_schedule which is assigned to the assignment
2555: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2556: PA_SCHEDULE_PVT.get_resource_schedule(l_t_team_player_id,
2557: 'PA_PROJECT_PARTY_ID',
2558: p_sch_except_record.start_date,
2559: p_sch_except_record.end_date,
2563: x_msg_data
2564: );
2565: END IF;
2566:
2567: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2568: -- Calling the schedule API which will apply the percentage on the schedule
2569: PA_SCHEDULE_UTILS.apply_percentage(x_sch_record_tab,
2570: l_t_res_cal_percent,
2571: l_x_return_status,
2590:
2591: PA_SCHEDULE_UTILS.log_message(2,'cal_id '||p_sch_except_record.calendar_id||' st_dt '||
2592: p_sch_except_record.start_date||' en_dt '||p_sch_except_record.end_date);
2593:
2594: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2595: -- Calling the procedure get_calendar_schedule on the basis of passed calendar id and for
2596: -- calendar type like assignment,project or others
2597: PA_SCHEDULE_PVT.get_calendar_schedule(p_sch_except_record.calendar_id,
2598: p_sch_except_record.start_date,
2607: PA_SCHEDULE_UTILS.log_message(2,'x_sch ',x_sch_record_tab);
2608: END IF;
2609: end if; -- 7663765
2610:
2611: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2612: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2613: p_project_id => p_sch_except_record.project_id,
2614: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2615: p_assignment_id => p_sch_except_record.assignment_id,
2623:
2624: x_return_status := l_x_return_status;
2625: EXCEPTION
2626: WHEN OTHERS THEN
2627: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2628: x_msg_count := 1;
2629: x_msg_data := SQLERRM;
2630: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2631: p_procedure_name => 'create_new_duration');
2701:
2702:
2703: BEGIN
2704: /*Added initialization for bug 6921728*/
2705: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2706:
2707: l_I := 1;
2708:
2709: FOR rec_sch IN csr_get_sch LOOP
2837: x_return_status := l_x_return_status ;
2838:
2839: EXCEPTION
2840: WHEN OTHERS THEN
2841: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2842: x_msg_count := 1;
2843: x_msg_data := SQLERRM;
2844: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2845: p_procedure_name => 'merge_work_pattern');
2870: )
2871: IS
2872:
2873: BEGIN
2874: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2875:
2876: x_sch_record_tab(1).start_date := p_sch_except_record.start_date;
2877: x_sch_record_tab(1).end_date := p_sch_except_record.end_date;
2878: x_sch_record_tab(1).Monday_hours := nvl(p_sch_except_record.Monday_hours, p_sch_record.Monday_hours);
2899:
2900: x_return_status := l_x_return_status;
2901: EXCEPTION
2902: WHEN OTHERS THEN
2903: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2904: x_msg_count := 1;
2905: x_msg_data := SQLERRM;
2906: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2907: p_procedure_name => 'create_new_pattern');
2929: )
2930: IS
2931: Status_change_not_allowed EXCEPTION;
2932: BEGIN
2933: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2934: x_sch_record_tab(1).start_date := p_sch_except_record.start_date;
2935: x_sch_record_tab(1).end_date := p_sch_except_record.end_date;
2936: x_sch_record_tab(1).assignment_status_code := p_sch_except_record.assignment_status_code;
2937:
2965: x_return_status := l_x_return_status;
2966: EXCEPTION
2967: WHEN Status_change_not_allowed THEN
2968: PA_UTILS.add_message('PA','PA_STATUS_CANT_CHANGE');
2969: x_return_status := FND_API.G_RET_STS_ERROR;
2970: x_msg_count := 1;
2971: x_msg_data := 'PA_STATUS_CANT_CHANGE';
2972:
2973: WHEN OTHERS THEN
2970: x_msg_count := 1;
2971: x_msg_data := 'PA_STATUS_CANT_CHANGE';
2972:
2973: WHEN OTHERS THEN
2974: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2975: x_msg_count := 1;
2976: x_msg_data := SQLERRM;
2977: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2978: p_procedure_name => 'create_new_status');
3017: l_difference_days NUMBER := 0;
3018: l_shift_unit_code VARCHAR2(20) := NULL;
3019:
3020: BEGIN
3021: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
3022: PA_SCHEDULE_UTILS.log_message(1,'start of the apply_change_duration API ....... ');
3023:
3024: l_stat_date_done := false;
3025: l_end_date_done := false;
3038: l_x_return_status,
3039: x_msg_count,
3040: x_msg_data );
3041:
3042: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3043: -- Copying exception record for the further calculation
3044: PA_SCHEDULE_UTILS.copy_except_record(p_sch_except_record,
3045: l_temp_except_rec,
3046: l_x_return_status,
3103: PA_SCHEDULE_UTILS.log_message(1,'out side boundary condition ');
3104:
3105: l_stat_date_done := true;
3106: l_end_date_done := true;
3107: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3108: -- Marking the schedule record for deletion
3109: PA_SCHEDULE_UTILS.mark_del_sch_rec_tab ( l_temp_p_sch_record_tab.first,
3110: l_temp_p_sch_record_tab.last,
3111: l_temp_p_sch_record_tab,
3113: x_msg_count,
3114: x_msg_data);
3115: END IF;
3116:
3117: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3118: -- Create the new schedule after applying the exception i.e after change the duration
3119: create_new_schedule( p_sch_except_record,
3120: l_temp_p_sch_record_tab(l_temp_first),
3121: l_out_tr_sch_rec_tab,
3125: x_msg_count,
3126: x_msg_data );
3127: END IF;
3128:
3129: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3130: -- Adding the previous record into the new record
3131: --7663765
3132: /* if (p_sch_except_record.exception_type_code = 'DURATION_PATTERN_SHIFT') then
3133: PA_SCHEDULE_UTILS.copy_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3181: < l_temp_p_sch_record_tab(l_I).start_date )) THEN
3182:
3183: PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date < sch_start_date AND First shift ');
3184:
3185: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3186: PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
3187: p_start_date => p_sch_except_record.start_date,
3188: p_end_date => l_temp_p_sch_record_tab( l_temp_first).start_date -1,
3189: x_return_status => l_x_return_status,
3190: x_msg_count => x_msg_count,
3191: x_msg_data => x_msg_data );
3192: END IF;
3193:
3194: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3195: create_new_schedule( l_temp_except_rec,
3196: l_temp_p_sch_record_tab(l_temp_first),
3197: l_out_tr_sch_rec_tab,
3198: l_difference_days, -- 7663765
3216: END IF;
3217:
3218: IF ( l_end_date_done ) THEN
3219: l_copy_cur_sch := true;
3220: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3221: -- 7663765
3222: /* if (p_sch_except_record.exception_type_code = 'DURATION_PATTERN_SHIFT') then
3223: PA_SCHEDULE_UTILS.copy_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3224: l_I,
3263: IF ( l_I < l_temp_last ) THEN
3264:
3265: PA_SCHEDULE_UTILS.log_message(2,'inside exp_end_date <= sch_end_date AND MARKING DELETE ');
3266: -- Mark remaining shifts as delete. */
3267: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3268: PA_SCHEDULE_UTILS.mark_del_sch_rec_tab ( l_temp_p_sch_record_tab.next(l_I),
3269: l_temp_p_sch_record_tab.last,
3270: l_temp_p_sch_record_tab,
3271: l_x_return_status,
3273: x_msg_data );
3274: END IF;
3275: END IF;
3276: l_copy_cur_sch := true;
3277: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3278: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3279: l_I,
3280: l_temp_p_sch_record_tab.last,
3281: l_out_tr_sch_rec_tab,
3290:
3291: l_copy_cur_sch := true;
3292: l_end_date_done := true;
3293: -- Copy this shift into l_out_tr_sch_rec_tab; */
3294: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3295: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3296: l_I,
3297: l_I,
3298: l_out_tr_sch_rec_tab,
3300: x_msg_count,
3301: x_msg_data );
3302: END IF;
3303:
3304: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3305: PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
3306: p_start_date => l_temp_p_sch_record_tab(l_I).end_date +1 ,
3307: p_end_date => p_sch_except_record.end_date,
3308: x_return_status => l_x_return_status,
3310: x_msg_data => x_msg_data );
3311:
3312: END IF;
3313:
3314: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3315: create_new_schedule( l_temp_except_rec,
3316: l_temp_p_sch_record_tab(l_temp_first),
3317: l_temp_tr_sch_rec_tab,
3318: l_difference_days, -- 7663765
3321: x_msg_count,
3322: x_msg_data );
3323: END IF;
3324:
3325: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3326: -- 7663765
3327: /* if (p_sch_except_record.exception_type_code = 'DURATION_PATTERN_SHIFT') then
3328: PA_SCHEDULE_UTILS.copy_schedule_rec_tab ( l_temp_tr_sch_rec_tab ,
3329: l_temp_tr_sch_rec_tab.first,
3350: END IF; -- end_date cond if */
3351: END IF; -- l_end_date_done if */
3352:
3353: IF ( l_copy_cur_sch = false ) THEN
3354: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3355: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3356: l_I,
3357: l_I,
3358: l_out_tr_sch_rec_tab,
3373: END LOOP;
3374:
3375: PA_SCHEDULE_UTILS.log_message(2,'DONE_CHANGE_DURATION : out_tr.count : '||l_out_tr_sch_rec_tab.count);
3376:
3377: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3378: PA_SCHEDULE_UTILS.copy_schedule_rec_tab ( l_out_tr_sch_rec_tab ,
3379: l_out_tr_sch_rec_tab.first,
3380: l_out_tr_sch_rec_tab.last,
3381: x_sch_record_tab,
3386:
3387: x_return_status := l_x_return_status;
3388: EXCEPTION
3389: WHEN OTHERS THEN
3390: x_return_status := FND_API.G_RET_STS_ERROR;
3391: x_msg_count := 1;
3392: x_msg_data := SQLERRM;
3393: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3394: p_procedure_name => 'apply_change_duration');
3436: l_create_new_sch BOOLEAN;
3437: l_create_third_sch BOOLEAN;
3438:
3439: BEGIN
3440: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
3441: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(p_sch_record_tab,
3442: p_sch_record_tab.first,
3443: p_sch_record_tab.last,
3444: l_temp_p_sch_record_tab,
3445: l_x_return_status,
3446: x_msg_count,
3447: x_msg_data );
3448:
3449: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3450: PA_SCHEDULE_UTILS.copy_except_record(p_sch_except_record,
3451: l_temp_except_rec,
3452: l_x_return_status,
3453: x_msg_count,
3515: l_temp_p_sch_record_tab(l_I).change_type_code := 'U';
3516: END IF;
3517:
3518: l_copy_cur_sch := true;
3519: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3520: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3521: l_I,
3522: l_I,
3523: l_out_tr_sch_rec_tab,
3548: END IF; -- end of level 1 if */
3549:
3550: IF ( l_create_new_sch ) THEN
3551: PA_SCHEDULE_UTILS.log_message(2,'inside l_create_new_sch if ........'||'status '||l_x_return_status);
3552: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3553: PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
3554: p_start_date => l_chg_exp_start_date,
3555: p_end_date => l_chg_exp_end_date,
3556: x_return_status => l_x_return_status,
3557: x_msg_count => x_msg_count,
3558: x_msg_data => x_msg_data );
3559: END IF;
3560:
3561: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3562: create_new_schedule( l_temp_except_rec,
3563: l_temp_p_sch_record_tab(l_I),
3564: l_temp_tr_sch_rec_tab,
3565: 0, -- 7663765
3570: END IF;
3571: PA_SCHEDULE_UTILS.log_message(2,'l_temp_tr_sch_rec_tab.count '||to_char(l_temp_tr_sch_rec_tab.count)||
3572: l_x_return_status);
3573:
3574: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3575: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_tr_sch_rec_tab ,
3576: l_temp_tr_sch_rec_tab.first,
3577: l_temp_tr_sch_rec_tab.last,
3578: l_out_tr_sch_rec_tab,
3589: l_temp_p_sch_record_tab(l_I).start_date := l_curr_exp_end_date + 1;
3590: l_temp_p_sch_record_tab(l_I).end_date := l_curr_sch_end_date;
3591: l_temp_p_sch_record_tab(l_I).change_type_code := 'I';
3592: l_temp_p_sch_record_tab(l_I).schedule_id := -1; -- Included for Bug 4616327
3593: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3594: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3595: l_I,
3596: l_I,
3597: l_out_tr_sch_rec_tab,
3604: END IF;
3605:
3606: IF ( l_copy_cur_sch = false ) THEN
3607: PA_SCHEDULE_UTILS.log_message(2,'inside l_copy_cur_sch if ........');
3608: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3609: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3610: l_I,
3611: l_I,
3612: l_out_tr_sch_rec_tab,
3621:
3622: PA_SCHEDULE_UTILS.log_message(2,'inside l_change_done if ........');
3623: IF ( l_I < l_sch_last ) THEN
3624: PA_SCHEDULE_UTILS.log_message(2,'inside l_I < l_sch_last if ....... ');
3625: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3626: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3627: l_temp_p_sch_record_tab.next(l_I),
3628: l_sch_last,
3629: l_out_tr_sch_rec_tab,
3642: END LOOP;
3643:
3644: PA_SCHEDULE_UTILS.log_message(2,'end of the loop l_out_tr_sch_rec_tab.count '||
3645: to_char(l_out_tr_sch_rec_tab.count)||'status '||l_x_return_status);
3646: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3647: PA_SCHEDULE_UTILS.copy_schedule_rec_tab ( l_out_tr_sch_rec_tab ,
3648: l_out_tr_sch_rec_tab.first,
3649: l_out_tr_sch_rec_tab.last,
3650: x_sch_record_tab,
3656: PA_SCHEDULE_UTILS.log_message(2,'end of the loop x_sch_record_tab.count '||to_char(x_sch_record_tab.count));
3657: x_return_status := l_x_return_status;
3658: EXCEPTION
3659: WHEN OTHERS THEN
3660: x_return_status := FND_API.G_RET_STS_ERROR;
3661: x_msg_count := 1;
3662: x_msg_data := SQLERRM;
3663: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3664: p_procedure_name => 'apply_other_change');
3718: l_t_multi_flag := 'N';
3719: l_change_flag := FALSE;
3720: l_t_assignment_id := chg_tr_sch_rec_tab(1).assignment_id;
3721: l_t_project_id := chg_tr_sch_rec_tab(1).project_id;
3722: l_x_return_status := FND_API.G_RET_STS_SUCCESS; -- Storing the status for tracking the error
3723:
3724: --Bug#9114277 l_new_calendar_id := Nvl (sch_except_record_tab(1).change_calendar_id,FND_API.G_MISS_NUM); ---- bug#9071974
3725: --Bug#9114277 l_RESOURCE_CALENDAR_PERCENT := Nvl (sch_except_record_tab(1).RESOURCE_CALENDAR_PERCENT,FND_API.G_MISS_NUM); ---- bug#9071974
3726:
3720: l_t_assignment_id := chg_tr_sch_rec_tab(1).assignment_id;
3721: l_t_project_id := chg_tr_sch_rec_tab(1).project_id;
3722: l_x_return_status := FND_API.G_RET_STS_SUCCESS; -- Storing the status for tracking the error
3723:
3724: --Bug#9114277 l_new_calendar_id := Nvl (sch_except_record_tab(1).change_calendar_id,FND_API.G_MISS_NUM); ---- bug#9071974
3725: --Bug#9114277 l_RESOURCE_CALENDAR_PERCENT := Nvl (sch_except_record_tab(1).RESOURCE_CALENDAR_PERCENT,FND_API.G_MISS_NUM); ---- bug#9071974
3726:
3727: -- Loop for processing the exception records from exception table type variable for change duration */
3728: IF (sch_except_record_tab.count > 0 ) THEN
3721: l_t_project_id := chg_tr_sch_rec_tab(1).project_id;
3722: l_x_return_status := FND_API.G_RET_STS_SUCCESS; -- Storing the status for tracking the error
3723:
3724: --Bug#9114277 l_new_calendar_id := Nvl (sch_except_record_tab(1).change_calendar_id,FND_API.G_MISS_NUM); ---- bug#9071974
3725: --Bug#9114277 l_RESOURCE_CALENDAR_PERCENT := Nvl (sch_except_record_tab(1).RESOURCE_CALENDAR_PERCENT,FND_API.G_MISS_NUM); ---- bug#9071974
3726:
3727: -- Loop for processing the exception records from exception table type variable for change duration */
3728: IF (sch_except_record_tab.count > 0 ) THEN
3729: FOR I IN sch_except_record_tab.first..sch_except_record_tab.last LOOP
3777: -- bug#9071974
3778: PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
3779: p_record_version_number => p_record_version_number,
3780: p_assignment_id => l_t_assignment_id,
3781: p_start_date => nvl(l_t_start_date,FND_API.G_MISS_DATE),
3782: p_end_date => nvl(l_t_end_date,FND_API.G_MISS_DATE),
3783: p_multiple_status_flag => l_t_multi_flag,
3784: p_status_code => l_curr_status,
3785: p_assignment_effort =>
3778: PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
3779: p_record_version_number => p_record_version_number,
3780: p_assignment_id => l_t_assignment_id,
3781: p_start_date => nvl(l_t_start_date,FND_API.G_MISS_DATE),
3782: p_end_date => nvl(l_t_end_date,FND_API.G_MISS_DATE),
3783: p_multiple_status_flag => l_t_multi_flag,
3784: p_status_code => l_curr_status,
3785: p_assignment_effort =>
3786: pa_schedule_utils.get_num_hours(l_t_project_id, l_t_assignment_id),
3801:
3802: -- If assignment record is successfully updated then call procedure to update pa project parties.
3803: --
3804: -- jmarques (1590046): Added STAFFED_ADMIN_ASSIGNMENT check to if statement.
3805: IF ( ( l_x_return_status = FND_API.G_RET_STS_SUCCESS ) AND
3806: ( (chg_tr_sch_rec_tab(l_J).schedule_type_code = 'STAFFED_ASSIGNMENT') OR
3807: (chg_tr_sch_rec_tab(l_J).schedule_type_code = 'STAFFED_ADMIN_ASSIGNMENT') )
3808: ) THEN
3809:
3872: END IF; -- Bug 6631033
3873:
3874: -- added for Bug fix: 4537865
3875:
3876: IF l_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3877: l_t_assignment_id := l_new_t_assignment_id;
3878: END IF;
3879: -- added for Bug fix: 4537865
3880:
3882:
3883: x_return_status := l_x_return_status;
3884: EXCEPTION
3885: WHEN l_empty_tab_record THEN
3886: x_return_status := FND_API.G_RET_STS_SUCCESS;
3887: NULL;
3888: WHEN OTHERS THEN
3889: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3890: x_msg_count := 1;
3885: WHEN l_empty_tab_record THEN
3886: x_return_status := FND_API.G_RET_STS_SUCCESS;
3887: NULL;
3888: WHEN OTHERS THEN
3889: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3890: x_msg_count := 1;
3891: x_msg_data := SQLERRM;
3892: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3893: p_procedure_name => 'apply_assignment_change');
3964: ***/
3965:
3966: BEGIN
3967:
3968: x_return_status := FND_API.G_RET_STS_SUCCESS ;
3969:
3970:
3971: OPEN C_Periodic_dates1;
3972: FETCH C_Periodic_dates1 into x_min_start_date, x_max_end_date;
3974:
3975: /*Added for 4996210: If dates are null then return error status */
3976:
3977: IF x_min_start_date IS NULL OR x_max_end_date IS NULL THEN
3978: x_return_status := FND_API.G_RET_STS_ERROR;
3979: x_msg_count := 1;
3980: x_msg_data := 'Error while calling get_periodic_start_end';
3981: END IF;
3982:
3986:
3987: EXCEPTION
3988: WHEN OTHERS THEN
3989: PA_SCHEDULE_UTILS.log_message(1, 'Error in get_periodic_start_end.....'||sqlerrm);
3990: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3991: x_msg_count := 1;
3992: x_msg_data := SQLERRM;
3993: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3994: p_procedure_name => 'get_periodic_start_end');
4047:
4048: l_alias_name PA_RESOURCE_LIST_MEMBERS.ALIAS%TYPE; /*Added for 4996210 */
4049: l_total_hours NUMBER; -- Bug 5126919
4050: BEGIN
4051: l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
4052: PA_SCHEDULE_UTILS.log_message(1, 'Start of the create_oasgn_schedule API ....');
4053:
4054: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_calendar_schedule ....');
4055:
4077: END IF;
4078:
4079: /*Bug 4996210 : If get_periodic_start_end returns error then raise error for all the resourcelist members for which the planned work quantity is null or zero*/
4080:
4081: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4082:
4083: BEGIN
4084: IF p_task_assignment_id_tbl.COUNT > 0 THEN
4085: SELECT ALIAS INTO l_alias_name FROM
4098:
4099: FND_MESSAGE.SET_NAME('PA','PA_PLAN_QTY_NULL');
4100: FND_MESSAGE.SET_TOKEN('TASK_ASSIG',l_alias_name);
4101: FND_MSG_PUB.add;
4102: RAISE FND_API.G_EXC_ERROR;
4103: END IF;
4104:
4105: /*End for 4996210 */
4106:
4144:
4145: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4146: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....'||l_x_return_status);
4147:
4148: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4149: -- updating the passed schedule table of record for creating the schedule for open assignment
4150: PA_SCHEDULE_UTILS.update_sch_rec_tab(l_new_schedule_tab_rec,
4151: p_project_id => p_project_id,
4152: p_calendar_id => p_calendar_id,
4162: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
4163: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
4164: PA_SCHEDULE_UTILS.log_message(1,'SCH_REC',l_x_schedule_tab_rec);
4165:
4166: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4167: -- Inserting the schedule in the PA_SCHEDULE table
4168: PA_SCHEDULE_PKG.insert_rows(l_new_schedule_tab_rec,
4169: l_x_return_status,
4170: x_msg_count,
4173: );
4174: END IF;
4175:
4176: -- Calling the Timeline api to build the timeline records for the assignment
4177: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4178: PA_TIMELINE_PVT.CREATE_TIMELINE (p_assignment_id =>p_assignment_id ,
4179: x_return_status =>l_x_return_status ,
4180: x_msg_count =>x_msg_count ,
4181: x_msg_data =>x_msg_data );
4188: x_return_status := l_x_return_status;
4189: EXCEPTION
4190:
4191: /*Added for 4996210 */
4192: WHEN FND_API.G_EXC_ERROR THEN
4193: x_return_status := FND_API.G_RET_STS_ERROR;
4194: /*End 4996210 */
4195:
4196: WHEN OTHERS THEN
4189: EXCEPTION
4190:
4191: /*Added for 4996210 */
4192: WHEN FND_API.G_EXC_ERROR THEN
4193: x_return_status := FND_API.G_RET_STS_ERROR;
4194: /*End 4996210 */
4195:
4196: WHEN OTHERS THEN
4197: PA_SCHEDULE_UTILS.log_message(1, 'Error in create_oasgn_schedule API .....'||sqlerrm);
4194: /*End 4996210 */
4195:
4196: WHEN OTHERS THEN
4197: PA_SCHEDULE_UTILS.log_message(1, 'Error in create_oasgn_schedule API .....'||sqlerrm);
4198: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4199: x_msg_count := 1;
4200: x_msg_data := SQLERRM;
4201: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
4202: p_procedure_name => 'create_opn_asg_schedule');
4243: l_current_sch_rec_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
4244: l_temp_number NUMBER;
4245: BEGIN
4246: l_assignment_id_tbl := p_assignment_id_tbl;
4247: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
4248: PA_SCHEDULE_UTILS.log_message(1, 'Start of the create_asgn_schedule API ....');
4249:
4250:
4251: -- Case I: add multiple open assignments. When p_assignment_source_id IS
4416: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4417:
4418: -- Calling the Timeline API to build timeline records for the first
4419: -- assignment in the table l_assignment_id_tbl.
4420: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4421: PA_TIMELINE_PVT.CREATE_TIMELINE (
4422: p_assignment_id =>l_assignment_id_tbl(l_assignment_id_tbl.FIRST).assignment_id,
4423: x_return_status =>l_x_return_status ,
4424: x_msg_count =>x_msg_count ,
4498:
4499: EXCEPTION
4500: WHEN OTHERS THEN
4501: PA_SCHEDULE_UTILS.log_message(1, 'Error in create_opn_asgn_schedule API .....'||sqlerrm);
4502: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4503: x_msg_count := 1;
4504: x_msg_data := SQLERRM;
4505: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
4506: p_procedure_name => 'create_opn_asg_schedule');
4569: BEGIN
4570: --jm_profiler.set_time('Create Schedule');
4571:
4572: -- Assigning status successs for tracking the error
4573: l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
4574: PA_SCHEDULE_UTILS.log_message(1,'Start of the create_sasgn_schedule API ... ');
4575:
4576: -- =================================================
4577: -- New code to process task assignment summation jraj 12/19/03
4618: );
4619: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_resource_schedule ....');
4620: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API apply_percentage ....');
4621:
4622: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4623: -- Calling the PA_SCHEDULE_UTILS API whice will apply the percentage of the resource can be used
4624: PA_SCHEDULE_UTILS.apply_percentage(l_x_sch_record_tab,
4625: p_resource_calendar_percent,
4626: l_x_return_status,
4642: from pa_project_assignments
4643: where assignment_id=p_open_assignment_id;
4644:
4645:
4646: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4647: -- Calling the PVT API which will get the schedule on the basis of passed assignment id
4648: --Added for 2335580
4649: -- To check for this date clause jraj 12/19/03..
4650:
4680: ELSIF (p_schedule_basis_flag IN ('P','O')) THEN
4681: PA_SCHEDULE_UTILS.log_message(1, 'Schedule Basis Flag P or O');
4682: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_calendar_schedule ....');
4683:
4684: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4685: -- Calling the PVT API which will get the schedule on the basis of passed calendar id
4686: PA_SCHEDULE_PVT.get_calendar_schedule(p_calendar_id,
4687: l_start_date,
4688: l_end_date,
4720:
4721:
4722:
4723: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4724: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4725: -- updating the passed schedule table of record for creating the schedule for staffed assignment
4726: PA_SCHEDULE_UTILS.update_sch_rec_tab(l_new_schedule_tab,
4727: P_project_id => p_project_id,
4728: p_schedule_type_code => 'STAFFED_ASSIGNMENT',
4738: PA_SCHEDULE_UTILS.log_message(1, 'SCH_REC',l_new_schedule_tab);
4739: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab ....');
4740: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
4741:
4742: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4743: -- Inserting the record in PA_SCHEDULES table
4744: PA_SCHEDULE_PKG.insert_rows(
4745: l_new_schedule_tab,
4746: l_x_return_status,
4752:
4753: --jm_profiler.set_time('Create Schedule');
4754:
4755: -- Calling the Timeline api to build the timeline records for the assignment
4756: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4757: PA_TIMELINE_PVT.CREATE_TIMELINE (p_assignment_id =>p_assignment_id ,
4758: x_return_status =>l_x_return_status ,
4759: x_msg_count =>x_msg_count ,
4760: x_msg_data =>x_msg_data );
4765: x_return_status := l_x_return_status;
4766: EXCEPTION
4767: WHEN OTHERS THEN
4768: PA_SCHEDULE_UTILS.log_message(1,'ERROR in create_sasgn_schedule API ..'||sqlerrm);
4769: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4770: x_msg_count := 1;
4771: x_msg_data := SQLERRM;
4772: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
4773: p_procedure_name => 'create_stf_asg_schedule');
4781: -- Parameters Type Required Description
4782: -- P_Assignment_Id NUMBER YES it is assignment id used for deletion
4783: --
4784: PROCEDURE delete_asgn_schedules ( p_assignment_id IN NUMBER,
4785: p_perm_delete IN VARCHAR2 := FND_API.G_TRUE,
4786: p_change_id IN NUMBER := null,
4787: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4788: x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
4789: x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
4790: IS
4791: BEGIN
4792:
4793: -- Storing the value for error tracking
4794: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
4795:
4796: -- deleting the records from pa_schedules,pa_schedule_exceptions and pa_schedule_except_history */
4797:
4798: DELETE pa_schedules
4802: WHERE assignment_id = p_assignment_id;
4803:
4804: -- Delete entire exception history if p_perm_delete
4805: -- Otherwise, just delete exceptions with change_id >= p_change_id
4806: if FND_API.TO_BOOLEAN(p_perm_delete) then
4807: DELETE pa_schedule_except_history
4808: WHERE assignment_id = p_assignment_id;
4809: else
4810: DELETE pa_schedule_except_history
4812: and change_id >= p_change_id;
4813: end if;
4814:
4815: -- Delete entire schedules history if p_perm_delete.
4816: if FND_API.TO_BOOLEAN(p_perm_delete) then
4817: DELETE pa_schedules_history
4818: WHERE assignment_id = p_assignment_id;
4819: end if;
4820:
4828: x_return_status := l_x_return_status;
4829:
4830: EXCEPTION
4831: WHEN OTHERS THEN
4832: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4833: x_msg_count := 1;
4834: x_msg_data := SQLERRM;
4835: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
4836: p_procedure_name => 'delete_asgn_schedules');
4896: WHERE assignment_id = p_assignment_id;
4897:
4898: BEGIN
4899: PA_SCHEDULE_UTILS.log_message(1,'Start of the update_sch_wf_success API');
4900: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
4901:
4902: l_record_version_number := p_record_version_number;
4903:
4904: open c2;
4972: p_end_date => l_end_date,
4973: p_assignment_status_code => l_next_status_code,
4974: p_asgn_start_date => l_asgn_start_date,
4975: p_asgn_end_date => l_asgn_end_date,
4976: p_init_msg_list => FND_API.G_FALSE,
4977: p_save_to_hist => FND_API.G_FALSE,
4978: x_return_status => l_x_return_status,
4979: x_msg_count => x_msg_count,
4980: x_msg_data => x_msg_data);
4973: p_assignment_status_code => l_next_status_code,
4974: p_asgn_start_date => l_asgn_start_date,
4975: p_asgn_end_date => l_asgn_end_date,
4976: p_init_msg_list => FND_API.G_FALSE,
4977: p_save_to_hist => FND_API.G_FALSE,
4978: x_return_status => l_x_return_status,
4979: x_msg_count => x_msg_count,
4980: x_msg_data => x_msg_data);
4981: end if;
5010: p_end_date => rec.end_date,
5011: p_assignment_status_code => l_next_status_code,
5012: p_asgn_start_date => l_asgn_start_date,
5013: p_asgn_end_date => l_asgn_end_date,
5014: p_init_msg_list => FND_API.G_FALSE,
5015: p_save_to_hist => FND_API.G_FALSE,
5016: x_return_status => l_x_return_status,
5017: x_msg_count => x_msg_count,
5018: x_msg_data => x_msg_data);
5011: p_assignment_status_code => l_next_status_code,
5012: p_asgn_start_date => l_asgn_start_date,
5013: p_asgn_end_date => l_asgn_end_date,
5014: p_init_msg_list => FND_API.G_FALSE,
5015: p_save_to_hist => FND_API.G_FALSE,
5016: x_return_status => l_x_return_status,
5017: x_msg_count => x_msg_count,
5018: x_msg_data => x_msg_data);
5019:
5076: p_end_date => l_last_end_date,
5077: p_assignment_status_code => l_next_status_code,
5078: p_asgn_start_date => l_asgn_start_date,
5079: p_asgn_end_date => l_asgn_end_date,
5080: p_init_msg_list => FND_API.G_FALSE,
5081: p_save_to_hist => FND_API.G_FALSE,
5082: x_return_status => l_x_return_status,
5083: x_msg_count => x_msg_count,
5084: x_msg_data => x_msg_data);
5077: p_assignment_status_code => l_next_status_code,
5078: p_asgn_start_date => l_asgn_start_date,
5079: p_asgn_end_date => l_asgn_end_date,
5080: p_init_msg_list => FND_API.G_FALSE,
5081: p_save_to_hist => FND_API.G_FALSE,
5082: x_return_status => l_x_return_status,
5083: x_msg_count => x_msg_count,
5084: x_msg_data => x_msg_data);
5085: END IF;
5113: p_end_date => l_last_end_date,
5114: p_assignment_status_code => l_next_status_code,
5115: p_asgn_start_date => l_asgn_start_date,
5116: p_asgn_end_date => l_asgn_end_date,
5117: p_init_msg_list => FND_API.G_FALSE,
5118: p_save_to_hist => FND_API.G_FALSE,
5119: x_return_status => l_x_return_status,
5120: x_msg_count => x_msg_count,
5121: x_msg_data => x_msg_data);
5114: p_assignment_status_code => l_next_status_code,
5115: p_asgn_start_date => l_asgn_start_date,
5116: p_asgn_end_date => l_asgn_end_date,
5117: p_init_msg_list => FND_API.G_FALSE,
5118: p_save_to_hist => FND_API.G_FALSE,
5119: x_return_status => l_x_return_status,
5120: x_msg_count => x_msg_count,
5121: x_msg_data => x_msg_data);
5122: END IF;
5143: x_return_status := l_x_return_status;
5144: x_msg_count := FND_MSG_PUB.Count_Msg;
5145: If x_msg_count = 1 THEN
5146: pa_interface_utils_pub.get_messages
5147: (p_encoded => FND_API.G_TRUE ,
5148: p_msg_index => 1,
5149: p_msg_count => x_msg_count ,
5150: p_msg_data => x_msg_data ,
5151: --p_data => x_msg_data, * Commented for Bug: 4537865
5159:
5160: EXCEPTION
5161: WHEN OTHERS THEN
5162: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_sch_wf_success API ..'||sqlerrm);
5163: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5164: x_msg_count := 1;
5165: x_msg_data := SQLERRM;
5166: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5167: p_procedure_name => 'update_sch_wf_success');
5218: WHERE assignment_id = p_assignment_id;
5219:
5220: BEGIN
5221: PA_SCHEDULE_UTILS.log_message(1,'Start of the update_sch_wf_failure API');
5222: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
5223:
5224: l_record_version_number := p_record_version_number;
5225:
5226: -- Get assignment information
5261: p_end_date => rec.end_date,
5262: p_assignment_status_code => l_next_status_code,
5263: p_asgn_start_date => l_asgn_start_date,
5264: p_asgn_end_date => l_asgn_end_date,
5265: p_init_msg_list => FND_API.G_FALSE,
5266: p_save_to_hist => FND_API.G_FALSE,
5267: x_return_status => l_x_return_status,
5268: x_msg_count => x_msg_count,
5269: x_msg_data => x_msg_data);
5262: p_assignment_status_code => l_next_status_code,
5263: p_asgn_start_date => l_asgn_start_date,
5264: p_asgn_end_date => l_asgn_end_date,
5265: p_init_msg_list => FND_API.G_FALSE,
5266: p_save_to_hist => FND_API.G_FALSE,
5267: x_return_status => l_x_return_status,
5268: x_msg_count => x_msg_count,
5269: x_msg_data => x_msg_data);
5270:
5325: p_end_date => l_last_end_date,
5326: p_assignment_status_code => l_next_status_code,
5327: p_asgn_start_date => l_asgn_start_date,
5328: p_asgn_end_date => l_asgn_end_date,
5329: p_init_msg_list => FND_API.G_FALSE,
5330: p_save_to_hist => FND_API.G_FALSE,
5331: x_return_status => l_x_return_status,
5332: x_msg_count => x_msg_count,
5333: x_msg_data => x_msg_data);
5326: p_assignment_status_code => l_next_status_code,
5327: p_asgn_start_date => l_asgn_start_date,
5328: p_asgn_end_date => l_asgn_end_date,
5329: p_init_msg_list => FND_API.G_FALSE,
5330: p_save_to_hist => FND_API.G_FALSE,
5331: x_return_status => l_x_return_status,
5332: x_msg_count => x_msg_count,
5333: x_msg_data => x_msg_data);
5334: END IF;
5362: p_end_date => l_last_end_date,
5363: p_assignment_status_code => l_next_status_code,
5364: p_asgn_start_date => l_asgn_start_date,
5365: p_asgn_end_date => l_asgn_end_date,
5366: p_init_msg_list => FND_API.G_FALSE,
5367: p_save_to_hist => FND_API.G_FALSE,
5368: x_return_status => l_x_return_status,
5369: x_msg_count => x_msg_count,
5370: x_msg_data => x_msg_data);
5363: p_assignment_status_code => l_next_status_code,
5364: p_asgn_start_date => l_asgn_start_date,
5365: p_asgn_end_date => l_asgn_end_date,
5366: p_init_msg_list => FND_API.G_FALSE,
5367: p_save_to_hist => FND_API.G_FALSE,
5368: x_return_status => l_x_return_status,
5369: x_msg_count => x_msg_count,
5370: x_msg_data => x_msg_data);
5371: END IF;
5380:
5381: x_msg_count := FND_MSG_PUB.Count_Msg;
5382: If x_msg_count = 1 THEN
5383: pa_interface_utils_pub.get_messages
5384: (p_encoded => FND_API.G_TRUE ,
5385: p_msg_index => 1,
5386: p_msg_count => x_msg_count ,
5387: p_msg_data => x_msg_data ,
5388: --p_data => x_msg_data, * Commented for Bug fix: 4537865
5398:
5399: EXCEPTION
5400: WHEN OTHERS THEN
5401: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_sch_wf_failure API ..'||sqlerrm);
5402: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5403: x_msg_count := 1;
5404: x_msg_data := SQLERRM;
5405: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5406: p_procedure_name => 'update_sch_wf_failure');
5439: and last_approved_flag = 'Y';
5440:
5441: BEGIN
5442: PA_SCHEDULE_UTILS.log_message(1,'Start of the revert_to_last_approved API');
5443: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
5444:
5445: l_index := 0;
5446: for rec in c1 loop
5447: l_index := l_index + 1;
5450: -- Delete schedules for the assignment in order to insert new assignment
5451: -- records.
5452: delete_asgn_schedules(
5453: p_assignment_id => p_assignment_id,
5454: p_perm_delete => FND_API.G_FALSE,
5455: p_change_id => p_change_id,
5456: x_return_status => l_x_return_status,
5457: x_msg_count => x_msg_count,
5458: x_msg_data => x_msg_data);
5484: delete pa_schedules_history
5485: where assignment_id = p_assignment_id
5486: and last_approved_flag = 'Y';
5487:
5488: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
5489: PA_TIMELINE_PVT.CREATE_TIMELINE (
5490: p_assignment_id => p_assignment_id,
5491: x_return_status => l_x_return_status,
5492: x_msg_count => x_msg_count,
5497:
5498: x_msg_count := FND_MSG_PUB.Count_Msg;
5499: If x_msg_count = 1 THEN
5500: pa_interface_utils_pub.get_messages
5501: (p_encoded => FND_API.G_TRUE ,
5502: p_msg_index => 1,
5503: p_msg_count => x_msg_count ,
5504: p_msg_data => x_msg_data ,
5505: --p_data => x_msg_data, * Commented for Bug fix: 4537865
5515:
5516: EXCEPTION
5517: WHEN OTHERS THEN
5518: PA_SCHEDULE_UTILS.log_message(1,'ERROR in revert_to_last_approved API ..'||sqlerrm);
5519: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5520: x_msg_count := 1;
5521: x_msg_data := SQLERRM;
5522: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5523: p_procedure_name => 'revert_to_last_approved');
5566: where assignment_id = p_assignment_id;
5567:
5568: BEGIN
5569: PA_SCHEDULE_UTILS.log_message(1,'Start of the update_history_table API');
5570: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
5571:
5572: -- If there are no records in pa_schedules_history with change_id
5573: -- then update table.
5574: open c1;
5605:
5606: x_msg_count := FND_MSG_PUB.Count_Msg;
5607: If x_msg_count = 1 THEN
5608: pa_interface_utils_pub.get_messages
5609: (p_encoded => FND_API.G_TRUE ,
5610: p_msg_index => 1,
5611: p_msg_count => x_msg_count ,
5612: p_msg_data => x_msg_data ,
5613: --p_data => x_msg_data, * Commented for Bgu: 4537865
5623:
5624: EXCEPTION
5625: WHEN OTHERS THEN
5626: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_history_table API ..'||sqlerrm);
5627: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5628: x_msg_count := 1;
5629: x_msg_data := SQLERRM;
5630: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5631: p_procedure_name => 'update_history_table');
5716: WHERE asmt.assignment_id = p_assignment_id
5717: AND prj.project_id = asmt.project_id;
5718:
5719: BEGIN
5720: x_return_status := FND_API.G_RET_STS_SUCCESS;
5721:
5722: -- get approval status
5723: OPEN get_apprvl_status_code;
5724: FETCH get_apprvl_status_code INTO l_apprvl_status_code;
5966: END IF; -- IF (p_populate_mode = 'SCHEDULE_UPDATED')
5967:
5968: EXCEPTION
5969: WHEN OTHERS THEN
5970: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5971:
5972: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5973: p_procedure_name => 'update_asgmt_changed_items_tab');
5974: RAISE;
6005: l_end_date DATE;
6006: l_self_conflict_flag VARCHAR2(1) := 'N';
6007:
6008: BEGIN
6009: x_return_status := FND_API.G_RET_STS_SUCCESS;
6010:
6011: OPEN c1;
6012: FETCH c1 INTO l_resource_id, l_start_date, l_end_date;
6013: CLOSE c1;
6163: WHEN OTHERS THEN
6164: x_overcommitment_flag := NULL ; -- 4537865
6165: x_conflict_group_id := NULL ;-- 4537865
6166:
6167: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6168: x_msg_count := 1;
6169: x_msg_data := SQLERRM;
6170: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
6171: p_procedure_name => 'check_overcommitment_single');
6209: AND item_key = p_item_key;
6210:
6211: BEGIN
6212: PA_SCHEDULE_UTILS.debug('Entering check_overcommitment_mult');
6213: x_return_status := FND_API.G_RET_STS_SUCCESS;
6214:
6215: FOR v_c1 IN c1 LOOP
6216: IF v_c1.assignment_id IS NOT NULL THEN
6217: PA_SCHEDULE_UTILS.debug('v_c1.assignment_id = '|| v_c1.assignment_id);
6414: x_msg_count => x_msg_count,
6415: x_msg_data => x_msg_data);
6416:
6417: -- added for bug fix: 4537865
6418: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
6419: l_conflict_group_id := l_new_conflict_group_id;
6420: END IF;
6421: -- added for bug fix: 4537865
6422:
6438: EXCEPTION
6439: WHEN OTHERS THEN
6440: x_overcommitment_flag := NULL ; -- 4537865
6441: x_conflict_group_id := NULL ;-- 4537865
6442: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6443: x_msg_count := 1;
6444: x_msg_data := SQLERRM;
6445: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
6446: p_procedure_name => 'check_overcommitment_mult');
6540: v_c1 c1%ROWTYPE;
6541: v_c2 c2%ROWTYPE;
6542:
6543: BEGIN
6544: x_return_status := FND_API.G_RET_STS_SUCCESS;
6545:
6546: IF G_AVAILABILITY_CAL_PERIOD = 'DAILY' THEN
6547: OPEN c1;
6548: FETCH c1 INTO v_c1;
6574:
6575: EXCEPTION
6576: WHEN OTHERS THEN
6577: x_self_conflict_flag := NULL ; -- 4537865
6578: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6579: x_msg_count := 1;
6580: x_msg_data := SQLERRM;
6581: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
6582: p_procedure_name => 'check_self_conflict');
6664: l_new_msg_data VARCHAR2(2000);
6665: -- added for Bug fix: 4537865
6666:
6667: BEGIN
6668: x_return_status := FND_API.G_RET_STS_SUCCESS;
6669:
6670: PA_SCHEDULE_UTILS.debug('p_conflict_group_id = ' || p_conflict_group_id);
6671:
6672: OPEN c1;
6984: END IF;
6985: END LOOP;
6986:
6987: -- Update pa_assignment_conflict_hist table.
6988: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
6989: PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
6990: p_assignment_id => p_assignment_id,
6991: p_processed_flag => 'Y',
6992: x_return_status => x_return_status,
7001:
7002: EXCEPTION
7003: WHEN l_remove_conflicts_failed THEN
7004: PA_UTILS.add_message('PA','PA_REMOVE_CONFLICTS_FAILED');
7005: x_return_status := FND_API.G_RET_STS_ERROR;
7006: x_msg_data := 'PA_REMOVE_CONFLICTS_FAILED';
7007: x_msg_count := FND_MSG_PUB.Count_Msg;
7008: IF x_msg_count = 1 THEN
7009: pa_interface_utils_pub.get_messages
7006: x_msg_data := 'PA_REMOVE_CONFLICTS_FAILED';
7007: x_msg_count := FND_MSG_PUB.Count_Msg;
7008: IF x_msg_count = 1 THEN
7009: pa_interface_utils_pub.get_messages
7010: (p_encoded => FND_API.G_TRUE,
7011: p_msg_index => 1,
7012: p_msg_count => x_msg_count,
7013: p_msg_data => x_msg_data,
7014: --p_data => x_msg_data, * Commented for Bug fix: 4537865
7018: x_msg_data := l_new_msg_data;
7019: -- added for Bug fix: 4537865
7020: End IF;
7021: WHEN OTHERS THEN
7022: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7023: x_msg_count := 1;
7024: x_msg_data := SQLERRM;
7025: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7026: p_procedure_name => 'resolve_conflicts');
7093: x_work_pattern_tbl(l_count).friday_hours||';'||
7094: x_work_pattern_tbl(l_count).saturday_hours||';'||
7095: x_work_pattern_tbl(l_count).sunday_hours);
7096:
7097: x_return_status := FND_API.G_RET_STS_SUCCESS;
7098: EXCEPTION
7099: WHEN OTHERS THEN
7100: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7101: x_msg_count := 1;
7096:
7097: x_return_status := FND_API.G_RET_STS_SUCCESS;
7098: EXCEPTION
7099: WHEN OTHERS THEN
7100: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7101: x_msg_count := 1;
7102: x_msg_data := SQLERRM;
7103: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7104: p_procedure_name => 'insert_work_pattern_record');
7121: l_overcom_quantity NUMBER;
7122: l_count NUMBER;
7123:
7124: BEGIN
7125: x_return_status := FND_API.G_RET_STS_SUCCESS;
7126:
7127: IF x_work_pattern_tbl.COUNT > 0 THEN
7128:
7129: l_week_day := TO_CHAR(p_item_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN');
7210: END IF;
7211:
7212: EXCEPTION
7213: WHEN OTHERS THEN
7214: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7215: x_msg_count := 1;
7216: x_msg_data := SQLERRM;
7217: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7218: p_procedure_name => 'insert_work_pattern_record');
7230: l_cur NUMBER;
7231: l_flag VARCHAR2(1);
7232:
7233: BEGIN
7234: x_return_status := FND_API.G_RET_STS_SUCCESS;
7235:
7236: --l_flag := 'N';
7237: FOR j IN p_cur_work_pattern_tbl.FIRST .. p_cur_work_pattern_tbl.LAST LOOP
7238:
7302:
7303:
7304: EXCEPTION
7305: WHEN OTHERS THEN
7306: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7307: x_msg_count := 1;
7308: x_msg_data := SQLERRM;
7309: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7310: p_procedure_name => 'insert_work_pattern_tab');
7403: l_err_stage VARCHAR2(2000);
7404: l_err_stack VARCHAR2(2000);
7405:
7406: BEGIN
7407: x_return_status := FND_API.G_RET_STS_SUCCESS;
7408:
7409: OPEN c1;
7410: FETCH c1 INTO v_c1;
7411: CLOSE c1;
7677: CLOSE c3;
7678:
7679: EXCEPTION
7680: WHEN OTHERS THEN
7681: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7682: x_msg_count := 1;
7683: x_msg_data := SQLERRM;
7684: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7685: p_procedure_name => 'overcom_post_aprvl_processing');
7717: x_resolve_conflicts_by_rmvl := 'Y';
7718: END IF;
7719: CLOSE c1;
7720:
7721: x_return_status := FND_API.G_RET_STS_SUCCESS;
7722: EXCEPTION
7723: WHEN OTHERS THEN
7724: -- 4537865
7725: x_resolve_conflicts_by_rmvl := NULL ;
7723: WHEN OTHERS THEN
7724: -- 4537865
7725: x_resolve_conflicts_by_rmvl := NULL ;
7726:
7727: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7728: x_msg_count := 1;
7729: x_msg_data := SQLERRM;
7730: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7731: p_procedure_name => 'will_resolve_conflicts_by_rmvl');
7762: x_resolve_conflicts_by_rmvl := 'Y';
7763: END IF;
7764: CLOSE c1;
7765:
7766: x_return_status := FND_API.G_RET_STS_SUCCESS;
7767: EXCEPTION
7768: WHEN OTHERS THEN
7769: -- 4537865
7770: x_resolve_conflicts_by_rmvl := NULL ;
7767: EXCEPTION
7768: WHEN OTHERS THEN
7769: -- 4537865
7770: x_resolve_conflicts_by_rmvl := NULL ;
7771: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7772: x_msg_count := 1;
7773: x_msg_data := SQLERRM;
7774: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7775: p_procedure_name => 'has_resolved_conflicts_by_rmvl');
7795: AND conf.resolve_conflicts_action_code = 'CANCEL_TXN_ITEM'
7796: AND conf.processed_flag = 'N';
7797:
7798: BEGIN
7799: x_return_status := FND_API.G_RET_STS_SUCCESS;
7800:
7801: For v_c1 in c1 LOOP
7802: PA_ASSIGNMENT_APPROVAL_PUB.cancel_assignment(p_record_version_number =>v_c1.record_version_number,
7803: p_assignment_id => v_c1.assignment_id,
7807: x_return_status => x_return_status,
7808: x_msg_count => x_msg_count,
7809: x_msg_data => x_msg_data);
7810:
7811: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7812: PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
7813: p_assignment_id => v_c1.assignment_id,
7814: p_processed_flag => 'Y',
7815: x_return_status => x_return_status,
7819: END LOOP;
7820:
7821: EXCEPTION
7822: WHEN OTHERS THEN
7823: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7824: x_msg_count := 1;
7825: x_msg_data := SQLERRM;
7826: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7827: p_procedure_name => 'cancel_overcom_txn_items');
7845: AND resolve_conflicts_action_code = 'REVERT_TXN_ITEM'
7846: AND processed_flag = 'N';
7847:
7848: BEGIN
7849: x_return_status := FND_API.G_RET_STS_SUCCESS;
7850:
7851: FOR v_c1 IN c1 LOOP
7852: PA_SCHEDULE_UTILS.debug('Revert_Overcom_Txn_Items: v_c1.assignment_id = ' || v_c1.assignment_id);
7853: PA_ASSIGNMENT_APPROVAL_PUB.revert_to_last_approved(p_assignment_id => v_c1.assignment_id,
7854: x_return_status => x_return_status,
7855: x_msg_count => x_msg_count,
7856: x_msg_data => x_msg_data);
7857: PA_SCHEDULE_UTILS.debug('Revert_Overcom_Txn_Items: After revert_to_last_approved');
7858: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7859: PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
7860: p_assignment_id => v_c1.assignment_id,
7861: p_processed_flag => 'Y',
7862: x_return_status => x_return_status,
7866: END LOOP;
7867:
7868: EXCEPTION
7869: WHEN OTHERS THEN
7870: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7871: x_msg_count := 1;
7872: x_msg_data := SQLERRM;
7873: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7874: p_procedure_name => 'revert_overcom_txn_items');
7892: INTO x_assignment_count
7893: FROM pa_assignment_conflict_hist
7894: WHERE conflict_group_id = p_conflict_group_id;
7895:
7896: x_return_status := FND_API.G_RET_STS_SUCCESS;
7897: EXCEPTION
7898: WHEN OTHERS THEN
7899: -- 4537865
7900: x_assignment_count := NULL ;
7898: WHEN OTHERS THEN
7899: -- 4537865
7900: x_assignment_count := NULL ;
7901:
7902: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7903: x_msg_count := 1;
7904: x_msg_data := SQLERRM;
7905: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7906: p_procedure_name => 'get_conflicting_asgmt_count');
7930:
7931: v_c1 c1%ROWTYPE;
7932:
7933: BEGIN
7934: x_return_status := FND_API.G_RET_STS_SUCCESS;
7935: OPEN c1;
7936: FETCH c1 INTO v_c1;
7937: IF c1%NOTFOUND THEN
7938: x_action_taken := 'Y';
7945: WHEN OTHERS THEN
7946: -- 4537865
7947: x_action_taken := NULL ;
7948:
7949: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7950: x_msg_count := 1;
7951: x_msg_data := SQLERRM;
7952: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7953: p_procedure_name => 'has_action_taken_on_conflicts');
7974: AND asgn.apprvl_status_code = 'ASGMT_APPRVL_WORKING';
7975:
7976: v_c1 c1%ROWTYPE;
7977: BEGIN
7978: x_return_status := FND_API.G_RET_STS_SUCCESS;
7979:
7980: OPEN c1;
7981: FETCH c1 INTO v_c1;
7982: IF c1%NOTFOUND THEN
7990: WHEN OTHERS THEN
7991: -- 4537865
7992: x_result := NULL ;
7993:
7994: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7995: x_msg_count := 1;
7996: x_msg_data := SQLERRM;
7997: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7998: p_procedure_name => 'check_asgmt_apprvl_working');
8135: END IF;
8136: END IF; -- 4387388
8137: -- End of Debugging Statements
8138:
8139: x_return_status := FND_API.G_RET_STS_SUCCESS;
8140: -- Check for invalid dates in p_schedule_tbl
8141: IF p_schedule_tbl.first IS NULL OR p_schedule_tbl.last IS NULL THEN
8142: l_schedule_start_date := p_start_date - 1;
8143: l_schedule_end_date := p_start_date - 2;
8373: END IF; -- 4387388
8374:
8375: EXCEPTION
8376: WHEN OTHERS THEN
8377: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8378: x_msg_count := 1;
8379: x_msg_data := SQLERRM;
8380: x_total_hours := 0; -- Bug 5126919
8381: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',