[Home] [Help]
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;
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:
356: IF (l_st_dt_done = TRUE ) AND (l_end_dt_done = TRUE) THEN
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:
376: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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,
381: x_msg_data =>x_msg_data);
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:
389: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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:
403: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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,
408: x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
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:
416: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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;
421: END IF;
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;
687: NULL;
688: END IF;
689:
690: -- Appending records in l_out_schedule_rec
691: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_curr_schedule_rec,1,1,l_out_schedule_rec,
692: l_x_return_status,x_msg_count,x_msg_data);
693:
694: IF (l_st_dt_done = TRUE ) AND (l_end_dt_done = TRUE) THEN
695: EXIT;
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,
713: l_x_return_status,
726: l_x_return_status,
727: x_msg_count,
728: x_msg_data);
729: ELSE
730: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
731: p_start_date =>p_start_date,
732: p_end_date =>l_out_schedule_rec(l_I).start_date -1,
733: p_monday_hours =>0.00,
734: p_tuesday_hours =>0.00,
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,
752: l_x_return_status,
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,
763: l_x_return_status,
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,
780: l_x_return_status,
795: x_msg_data);
796: /*End of code for Bug 2335580 */
797: ELSE
798:
799: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
800: p_start_date =>l_out_schedule_rec(l_J).end_date + 1,
801: p_end_date => p_end_date,
802: p_monday_hours =>0.00,
803: p_tuesday_hours =>0.00,
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,
821: l_x_return_status,
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,
831: l_x_return_status,
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,
858: l_x_return_status,
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;
1078:
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
1207: BEGIN
1219: p_resource_id => NULL,
1220: x_jtf_resource_id => l_t_resource_id,
1221: x_return_status => l_x_return_status,
1222: x_error_message_code => x_msg_data );
1223: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1224: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id');
1225:
1226: END IF;
1227: ELSIF ( p_source_type = 'PA_RESOURCE_ID') THEN
1220: x_jtf_resource_id => l_t_resource_id,
1221: x_return_status => l_x_return_status,
1222: x_error_message_code => x_msg_data );
1223: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1224: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id');
1225:
1226: END IF;
1227: ELSIF ( p_source_type = 'PA_RESOURCE_ID') THEN
1228: l_resource_id := p_source_id;
1232: p_resource_id => p_source_id,
1233: x_jtf_resource_id => l_t_resource_id,
1234: x_return_status => l_x_return_status,
1235: x_error_message_code => x_msg_data );
1236: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1237: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id2');
1238: END IF;
1239: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1240: END IF;
1233: x_jtf_resource_id => l_t_resource_id,
1234: x_return_status => l_x_return_status,
1235: x_error_message_code => x_msg_data );
1236: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1237: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id2');
1238: END IF;
1239: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1240: END IF;
1241:
1235: x_error_message_code => x_msg_data );
1236: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1237: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id2');
1238: END IF;
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
1238: END IF;
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);
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:
1248: -- 1965289: Work around for CRM bug (cannot create future dated resources).
1249: -- If the jtf_resource_id is null, then we need to use the default
1250: -- calendar instead of going to the jtf_cal_resource_assign table.
1289: p_organization_id => l_resource_organization_id,
1290: x_default_ou => l_resource_ou_id,
1291: x_default_cal_id => l_calendar_id,
1292: x_return_status => l_x_return_status);
1293: PA_SCHEDULE_UTILS.log_message(1,'l_resource_organization_id: '||l_resource_organization_id);
1294: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1295: PA_SCHEDULE_UTILS.log_message(1,'get_org_defaults');
1296: END IF;
1297:
1290: x_default_ou => l_resource_ou_id,
1291: x_default_cal_id => l_calendar_id,
1292: x_return_status => l_x_return_status);
1293: PA_SCHEDULE_UTILS.log_message(1,'l_resource_organization_id: '||l_resource_organization_id);
1294: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1295: PA_SCHEDULE_UTILS.log_message(1,'get_org_defaults');
1296: END IF;
1297:
1298: if (l_calendar_id is null) then
1291: x_default_cal_id => l_calendar_id,
1292: x_return_status => l_x_return_status);
1293: PA_SCHEDULE_UTILS.log_message(1,'l_resource_organization_id: '||l_resource_organization_id);
1294: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1295: PA_SCHEDULE_UTILS.log_message(1,'get_org_defaults');
1296: END IF;
1297:
1298: if (l_calendar_id is null) then
1299: l_calendar_id := fnd_profile.value_specific('PA_PRM_DEFAULT_CALENDAR');
1325: l_cur_schedule_rec,
1326: l_x_return_status,
1327: x_msg_count,
1328: x_msg_data);
1329: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1330: PA_SCHEDULE_UTILS.log_message(1,'get_calendar_schedule');
1331: END IF;
1332:
1333: IF (p_start_date is not null) then
1326: l_x_return_status,
1327: x_msg_count,
1328: x_msg_data);
1329: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1330: PA_SCHEDULE_UTILS.log_message(1,'get_calendar_schedule');
1331: END IF;
1332:
1333: IF (p_start_date is not null) then
1334: l_tc_start_date := p_start_date;
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,
1342: l_out_schedule_rec,
1342: l_out_schedule_rec,
1343: l_x_return_status,
1344: x_msg_count,
1345: x_msg_data);
1346: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1347: PA_SCHEDULE_UTILS.log_message(1,'add_schedule_rec_tab');
1348: END IF;
1349:
1350: ---- Start: Copied from below (same processing for work around)
1343: l_x_return_status,
1344: x_msg_count,
1345: x_msg_data);
1346: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1347: PA_SCHEDULE_UTILS.log_message(1,'add_schedule_rec_tab');
1348: END IF;
1349:
1350: ---- Start: Copied from below (same processing for work around)
1351: IF ( l_out_schedule_rec.count > 0 ) THEN
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:
1362: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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);
1367: END IF;
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:
1374: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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:
1379: l_x_sch_copy_done := TRUE;
1376: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1377: END IF;
1378:
1379: l_x_sch_copy_done := TRUE;
1380: --PA_SCHEDULE_UTILS.log_message(2,'X1 :',x_sch_record_tab);
1381: END IF;
1382:
1383: IF (l_tc_end_date > l_out_schedule_rec(l_J).end_date) THEN
1384:
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:
1390: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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:
1404: 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:
1411: l_x_sch_copy_done := TRUE;
1408: x_msg_data);
1409: END IF;
1410:
1411: l_x_sch_copy_done := TRUE;
1412: --PA_SCHEDULE_UTILS.log_message(2,'X2 :',x_sch_record_tab);
1413: END IF;
1414:
1415: IF l_x_sch_copy_done = FALSE THEN
1416:
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;
1422: ELSE
1449: AND jtf_cal_resource_assign.resource_type_code = 'RS_EMPLOYEE'
1450: AND jtf_cal_resource_assign.primary_calendar_flag = 'Y';
1451: END IF;
1452:
1453: PA_SCHEDULE_UTILS.log_message(1,'Start date ... '||to_char(l_temp_start_date)||to_char(p_start_date));
1454: PA_SCHEDULE_UTILS.log_message(1,'end date ... '||to_char(l_temp_end_date)||to_char(p_end_date));
1455: IF (p_start_date IS NULL ) THEN
1456: l_tc_start_date := l_temp_start_date;
1457: ELSE
1450: AND jtf_cal_resource_assign.primary_calendar_flag = 'Y';
1451: END IF;
1452:
1453: PA_SCHEDULE_UTILS.log_message(1,'Start date ... '||to_char(l_temp_start_date)||to_char(p_start_date));
1454: PA_SCHEDULE_UTILS.log_message(1,'end date ... '||to_char(l_temp_end_date)||to_char(p_end_date));
1455: IF (p_start_date IS NULL ) THEN
1456: l_tc_start_date := l_temp_start_date;
1457: ELSE
1458: l_tc_start_date := p_start_date;
1464: l_tc_end_date := p_end_date;
1465: END IF;
1466:
1467:
1468: PA_SCHEDULE_UTILS.log_message(1,'Start of the get_resource_schedule API ... ');
1469:
1470:
1471: FOR v_c1 IN C1 LOOP
1472:
1469:
1470:
1471: FOR v_c1 IN C1 LOOP
1472:
1473: PA_SCHEDULE_UTILS.log_message(1,'inside cursor ... ');
1474:
1475: PA_SCHEDULE_UTILS.log_message(2,'REC : '||to_char(v_c1.calendar_id)|| ' '||
1476: to_char(v_c1.start_date)||' '||
1477: to_char(v_c1.end_date));
1471: FOR v_c1 IN C1 LOOP
1472:
1473: PA_SCHEDULE_UTILS.log_message(1,'inside cursor ... ');
1474:
1475: PA_SCHEDULE_UTILS.log_message(2,'REC : '||to_char(v_c1.calendar_id)|| ' '||
1476: to_char(v_c1.start_date)||' '||
1477: to_char(v_c1.end_date));
1478:
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:
1488: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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,
1493: p_sunday_hours =>0.00,
1493: p_sunday_hours =>0.00,
1494: x_return_status => l_x_return_status,x_msg_count =>
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,
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;
1504: END IF;
1505:
1504: END IF;
1505:
1506: l_cur_schedule_rec.delete;
1507:
1508: PA_SCHEDULE_UTILS.log_message(2,'in deleting CUR '||l_x_return_status);
1509: IF ( v_c1.end_date > l_tc_end_date ) THEN
1510: l_t_end_date := l_tc_end_date;
1511: ELSE
1512: l_t_end_date := v_c1.end_date;
1515: IF ( v_c1.start_date < l_tc_start_date ) THEN
1516: l_t_start_date := l_tc_start_date;
1517: ELSE
1518: l_t_start_date := v_c1.start_date;
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
1518: l_t_start_date := v_c1.start_date;
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,
1528: l_cur_schedule_rec,
1529: l_x_return_status,
1530: x_msg_count,
1531: x_msg_data);
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
1530: x_msg_count,
1531: x_msg_data);
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,
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;
1541:
1540: END IF;
1541:
1542: l_last_end_date := v_c1.end_date;
1543:
1544: PA_SCHEDULE_UTILS.log_message(2,'OUT :',l_out_schedule_rec);
1545:
1546: END LOOP;
1547:
1548: PA_SCHEDULE_UTILS.log_message(2,'OUTSIDE loop :');
1544: PA_SCHEDULE_UTILS.log_message(2,'OUT :',l_out_schedule_rec);
1545:
1546: END LOOP;
1547:
1548: PA_SCHEDULE_UTILS.log_message(2,'OUTSIDE loop :');
1549:
1550: IF ( l_out_schedule_rec.count > 0 ) THEN
1551: l_I := l_out_schedule_rec.first;
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:
1561: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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);
1566: END IF;
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:
1573: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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:
1578: l_x_sch_copy_done := TRUE;
1575: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1576: END IF;
1577:
1578: l_x_sch_copy_done := TRUE;
1579: --PA_SCHEDULE_UTILS.log_message(2,'X1 :',x_sch_record_tab);
1580: END IF;
1581:
1582: IF (l_tc_end_date > l_out_schedule_rec(l_J).end_date) THEN
1583:
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:
1589: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
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:
1603: 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:
1610: l_x_sch_copy_done := TRUE;
1607: x_msg_data);
1608: END IF;
1609:
1610: l_x_sch_copy_done := TRUE;
1611: --PA_SCHEDULE_UTILS.log_message(2,'X2 :',x_sch_record_tab);
1612: END IF;
1613:
1614: IF l_x_sch_copy_done = FALSE THEN
1615:
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;
1621: ELSE
1632: END IF;
1633:
1634: -- 2196924: Fix table by setting 0 for all dates with no HR assignment.
1635: IF (NVL(x_sch_record_tab.count,0) <> 0) THEN
1636: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab.count<>0');
1637: for i in x_sch_record_tab.first .. x_sch_record_tab.last loop
1638: PA_SCHEDULE_UTILS.log_message(1, i || ' ' || x_sch_record_tab(i).start_date || ' ' || x_sch_record_tab(i).end_date || ' ' || x_sch_record_tab(i).monday_hours);
1639: end loop;
1640:
1634: -- 2196924: Fix table by setting 0 for all dates with no HR assignment.
1635: IF (NVL(x_sch_record_tab.count,0) <> 0) THEN
1636: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab.count<>0');
1637: for i in x_sch_record_tab.first .. x_sch_record_tab.last loop
1638: PA_SCHEDULE_UTILS.log_message(1, i || ' ' || x_sch_record_tab(i).start_date || ' ' || x_sch_record_tab(i).end_date || ' ' || x_sch_record_tab(i).monday_hours);
1639: end loop;
1640:
1641: l_cap_first_start_date := x_sch_record_tab(x_sch_record_tab.first).start_date;
1642: l_cap_last_end_date := x_sch_record_tab(x_sch_record_tab.last).end_date;
1640:
1641: l_cap_first_start_date := x_sch_record_tab(x_sch_record_tab.first).start_date;
1642: l_cap_last_end_date := x_sch_record_tab(x_sch_record_tab.last).end_date;
1643:
1644: PA_SCHEDULE_UTILS.log_message(1,'l_cap_first_start_date: ' || l_cap_first_start_date);
1645: PA_SCHEDULE_UTILS.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1646:
1647: SELECT rou.resource_effective_start_date,
1648: NVL(rou.resource_effective_end_date,SYSDATE)
1641: l_cap_first_start_date := x_sch_record_tab(x_sch_record_tab.first).start_date;
1642: l_cap_last_end_date := x_sch_record_tab(x_sch_record_tab.last).end_date;
1643:
1644: PA_SCHEDULE_UTILS.log_message(1,'l_cap_first_start_date: ' || l_cap_first_start_date);
1645: PA_SCHEDULE_UTILS.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1646:
1647: SELECT rou.resource_effective_start_date,
1648: NVL(rou.resource_effective_end_date,SYSDATE)
1649: BULK COLLECT INTO
1655: AND rou.resource_effective_start_date <= l_cap_last_end_date
1656: ORDER BY rou.resource_effective_start_date;
1657:
1658: if (NVL(l_ResStartDateTab.count,0) = 0) THEN
1659: PA_SCHEDULE_UTILS.log_message(1,'Set all hours to 0 in l_sch_record_tab since no res denorm records exist. ');
1660: -- Set all hours to 0 in l_sch_record_tab since no res denorm records
1661: -- exist.
1662: l_sch_record_tab := x_sch_record_tab;
1663:
1673: else
1674: l_res_first_start_date := l_resstartdatetab(l_resstartdatetab.first);
1675: l_res_last_end_date := l_resenddatetab(l_resenddatetab.last);
1676:
1677: PA_SCHEDULE_UTILS.log_message(1,'l_res_first_start_date: ' || l_res_first_start_date);
1678: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1679:
1680: PA_SCHEDULE_UTILS.log_message(1,'Resource denorm records do exist.');
1681:
1674: l_res_first_start_date := l_resstartdatetab(l_resstartdatetab.first);
1675: l_res_last_end_date := l_resenddatetab(l_resenddatetab.last);
1676:
1677: PA_SCHEDULE_UTILS.log_message(1,'l_res_first_start_date: ' || l_res_first_start_date);
1678: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1679:
1680: PA_SCHEDULE_UTILS.log_message(1,'Resource denorm records do exist.');
1681:
1682: -- Check if there are any holes in resource denorm records.
1676:
1677: PA_SCHEDULE_UTILS.log_message(1,'l_res_first_start_date: ' || l_res_first_start_date);
1678: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1679:
1680: PA_SCHEDULE_UTILS.log_message(1,'Resource denorm records do exist.');
1681:
1682: -- Check if there are any holes in resource denorm records.
1683: -- If so, then adjust x_sch_record_tab with that change.
1684:
1697: l_resstartdatetab(l_resstartdatetab.last+1) := l_cap_last_end_date+1;
1698: l_resenddatetab(l_resenddatetab.last+1) := l_cap_last_end_date +10;
1699: END IF;
1700:
1701: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1702:
1703: l_last_processed_cap_index := 0;
1704: l_cap_tab_index := 1;
1705:
1702:
1703: l_last_processed_cap_index := 0;
1704: l_cap_tab_index := 1;
1705:
1706: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1707: PA_schedule_utils.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1708:
1709: l_last_processed_cap_index := x_sch_record_tab.first-1;
1710: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1703: l_last_processed_cap_index := 0;
1704: l_cap_tab_index := 1;
1705:
1706: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1707: PA_schedule_utils.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1708:
1709: l_last_processed_cap_index := x_sch_record_tab.first-1;
1710: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1711:
1706: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1707: PA_schedule_utils.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1708:
1709: l_last_processed_cap_index := x_sch_record_tab.first-1;
1710: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1711:
1712: -- Find holes in l_ResStartDateTab, l_ResEndDateTab
1713: FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last LOOP
1714: pa_schedule_utils.log_message(1,'i: ' || i);
1710: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1711:
1712: -- Find holes in l_ResStartDateTab, l_ResEndDateTab
1713: FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last LOOP
1714: pa_schedule_utils.log_message(1,'i: ' || i);
1715: PA_SCHEDULE_UTILS.log_message(1,'l_ResStartDateTab(i): ' || l_ResStartDateTab(i));
1716: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1717: if (l_ResStartDateTab(i) > l_prev_res_end_date+1) then
1718: l_hole_start_date := l_prev_res_end_date+1;
1711:
1712: -- Find holes in l_ResStartDateTab, l_ResEndDateTab
1713: FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last LOOP
1714: pa_schedule_utils.log_message(1,'i: ' || i);
1715: PA_SCHEDULE_UTILS.log_message(1,'l_ResStartDateTab(i): ' || l_ResStartDateTab(i));
1716: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1717: if (l_ResStartDateTab(i) > l_prev_res_end_date+1) then
1718: l_hole_start_date := l_prev_res_end_date+1;
1719: l_hole_end_date := l_ResStartDateTab(i)-1;
1712: -- Find holes in l_ResStartDateTab, l_ResEndDateTab
1713: FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last LOOP
1714: pa_schedule_utils.log_message(1,'i: ' || i);
1715: PA_SCHEDULE_UTILS.log_message(1,'l_ResStartDateTab(i): ' || l_ResStartDateTab(i));
1716: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1717: if (l_ResStartDateTab(i) > l_prev_res_end_date+1) then
1718: l_hole_start_date := l_prev_res_end_date+1;
1719: l_hole_end_date := l_ResStartDateTab(i)-1;
1720: PA_SCHEDULE_UTILS.log_message(1,'Hole found: ' || l_hole_start_date || ' ' || l_hole_end_date);
1716: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1717: if (l_ResStartDateTab(i) > l_prev_res_end_date+1) then
1718: l_hole_start_date := l_prev_res_end_date+1;
1719: l_hole_end_date := l_ResStartDateTab(i)-1;
1720: PA_SCHEDULE_UTILS.log_message(1,'Hole found: ' || l_hole_start_date || ' ' || l_hole_end_date);
1721:
1722: -- Adjust x_sch_record_tab with decrease in availability.
1723: -- This is done by copying / modifying records up to hole end date.
1724: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1720: PA_SCHEDULE_UTILS.log_message(1,'Hole found: ' || l_hole_start_date || ' ' || l_hole_end_date);
1721:
1722: -- Adjust x_sch_record_tab with decrease in availability.
1723: -- This is done by copying / modifying records up to hole end date.
1724: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1725: PA_SCHEDULE_UTILS.log_message(1,'Start loop through capacity records.');
1726:
1727: <
1728: for j in (l_last_processed_cap_index + 1) .. x_sch_record_tab.last LOOP
1721:
1722: -- Adjust x_sch_record_tab with decrease in availability.
1723: -- This is done by copying / modifying records up to hole end date.
1724: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1725: PA_SCHEDULE_UTILS.log_message(1,'Start loop through capacity records.');
1726:
1727: <
1728: for j in (l_last_processed_cap_index + 1) .. x_sch_record_tab.last LOOP
1729:
1727: <
1728: for j in (l_last_processed_cap_index + 1) .. x_sch_record_tab.last LOOP
1729:
1730: l_j_index := j;
1731: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1732: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1733: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1734: PA_SCHEDULE_UTILS.log_message(1,'l_hole_end_date: ' || l_hole_end_date);
1735: l_reprocess_flag := 'N';
1728: for j in (l_last_processed_cap_index + 1) .. x_sch_record_tab.last LOOP
1729:
1730: l_j_index := j;
1731: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1732: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1733: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1734: PA_SCHEDULE_UTILS.log_message(1,'l_hole_end_date: ' || l_hole_end_date);
1735: l_reprocess_flag := 'N';
1736:
1729:
1730: l_j_index := j;
1731: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1732: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1733: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1734: PA_SCHEDULE_UTILS.log_message(1,'l_hole_end_date: ' || l_hole_end_date);
1735: l_reprocess_flag := 'N';
1736:
1737:
1730: l_j_index := j;
1731: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1732: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1733: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1734: PA_SCHEDULE_UTILS.log_message(1,'l_hole_end_date: ' || l_hole_end_date);
1735: l_reprocess_flag := 'N';
1736:
1737:
1738: IF (x_sch_record_tab(j).start_date = l_hole_end_date + 1) then
1735: l_reprocess_flag := 'N';
1736:
1737:
1738: IF (x_sch_record_tab(j).start_date = l_hole_end_date + 1) then
1739: PA_SCHEDULE_UTILS.log_message(1,'Finished looping through all capacity records for current hole. Find next hole.');
1740: l_reprocess_flag := 'Y'; -- Added after the fact
1741: EXIT l_cap_record_loop;
1742: -- Reprocess if the capacity record starts after the hole
1743: -- end date because it may overlap the next hole.
1741: EXIT l_cap_record_loop;
1742: -- Reprocess if the capacity record starts after the hole
1743: -- end date because it may overlap the next hole.
1744: ELSIF (x_sch_record_tab(j).start_date > l_hole_end_date + 1) then
1745: PA_SCHEDULE_UTILS.log_message(1,'Finished looping through all capacity records for current hole. Find next hole.');
1746: l_reprocess_flag := 'Y';
1747: EXIT l_cap_record_loop;
1748: end if;
1749:
1749:
1750: -- If Capacity record is before hole.
1751: if (x_sch_record_tab(j).end_date < l_hole_start_date) then
1752:
1753: PA_SCHEDULE_UTILS.log_message(1,'Capacity record before hole');
1754: -- Keep record as is.
1755: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1756: l_cap_tab_index := l_cap_tab_index + 1;
1757: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1753: PA_SCHEDULE_UTILS.log_message(1,'Capacity record before hole');
1754: -- Keep record as is.
1755: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1756: l_cap_tab_index := l_cap_tab_index + 1;
1757: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1758: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1759: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1760: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1761:
1754: -- Keep record as is.
1755: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1756: l_cap_tab_index := l_cap_tab_index + 1;
1757: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1758: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1759: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1760: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1761:
1762: -- If capacity record overlaps start date of hole.
1755: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1756: l_cap_tab_index := l_cap_tab_index + 1;
1757: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1758: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1759: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1760: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1761:
1762: -- If capacity record overlaps start date of hole.
1763: elsif (x_sch_record_tab(j).start_date < l_hole_start_date AND
1756: l_cap_tab_index := l_cap_tab_index + 1;
1757: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1758: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1759: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1760: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1761:
1762: -- If capacity record overlaps start date of hole.
1763: elsif (x_sch_record_tab(j).start_date < l_hole_start_date AND
1764: x_sch_record_tab(j).end_date <= l_hole_end_date) then
1762: -- If capacity record overlaps start date of hole.
1763: elsif (x_sch_record_tab(j).start_date < l_hole_start_date AND
1764: x_sch_record_tab(j).end_date <= l_hole_end_date) then
1765:
1766: PA_SCHEDULE_UTILS.log_message(1,'capacity record overlaps start date of hole.');
1767: -- Keep record as is but end date it
1768: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1769: l_sch_record_tab(l_cap_tab_index).end_date :=
1770: l_hole_start_date - 1;
1787: -- If capacity record overlaps start date and end date of hole.
1788: elsif (x_sch_record_tab(j).start_date < l_hole_start_date AND
1789: x_sch_record_tab(j).end_date > l_hole_end_date) then
1790:
1791: PA_SCHEDULE_UTILS.log_message(1,'capacity record overlaps start date and end date of hole.');
1792:
1793: -- Copy record over and end date it
1794: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1795: l_sch_record_tab(l_cap_tab_index).end_date :=
1810: l_cap_tab_index := l_cap_tab_index + 1;
1811:
1812: -- Modify x_sch_record_tab(j) so that the record will
1813: -- be reprocessed.
1814: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1815: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1816: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1817:
1818: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1811:
1812: -- Modify x_sch_record_tab(j) so that the record will
1813: -- be reprocessed.
1814: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1815: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1816: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1817:
1818: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1819: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1812: -- Modify x_sch_record_tab(j) so that the record will
1813: -- be reprocessed.
1814: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1815: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1816: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1817:
1818: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1819: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1820: l_reprocess_flag := 'Y'; -- Mark for reprocessing.
1815: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1816: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1817:
1818: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1819: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1820: l_reprocess_flag := 'Y'; -- Mark for reprocessing.
1821:
1822: EXIT l_cap_record_loop;
1823:
1824: -- If capacity record within hole.
1825: elsif (x_sch_record_tab(j).start_date <= l_hole_end_date AND
1826: x_sch_record_tab(j).end_date <= l_hole_end_date) THEN
1827:
1828: PA_SCHEDULE_UTILS.log_message(1,'capacity record within hole.');
1829:
1830: -- Create record for hole.
1831: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1832: l_sch_record_tab(l_cap_tab_index).monday_hours := 0;
1841: -- If capacity record overlaps end date of hole.
1842: elsif (x_sch_record_tab(j).start_date <= l_hole_end_date AND
1843: x_sch_record_tab(j).end_date > l_hole_end_date) THEN
1844:
1845: PA_SCHEDULE_UTILS.log_message(1,'capacity record overlaps end date of hole.');
1846:
1847: -- Create record for hole.
1848: PA_SCHEDULE_UTILS.log_message(1,'l_cap_tab_index: ' || l_cap_tab_index);
1849: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1844:
1845: PA_SCHEDULE_UTILS.log_message(1,'capacity record overlaps end date of hole.');
1846:
1847: -- Create record for hole.
1848: PA_SCHEDULE_UTILS.log_message(1,'l_cap_tab_index: ' || l_cap_tab_index);
1849: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1850: l_sch_record_tab(l_cap_tab_index).end_date := l_hole_end_date;
1851: l_sch_record_tab(l_cap_tab_index).monday_hours := 0;
1852: l_sch_record_tab(l_cap_tab_index).tuesday_hours := 0;
1858: l_cap_tab_index := l_cap_tab_index + 1;
1859:
1860: -- Modify x_sch_record_tab(j) so that the record will
1861: -- be reprocessed.
1862: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1863: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1864: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1865: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1866:
1859:
1860: -- Modify x_sch_record_tab(j) so that the record will
1861: -- be reprocessed.
1862: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1863: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1864: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1865: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1866:
1867: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1860: -- Modify x_sch_record_tab(j) so that the record will
1861: -- be reprocessed.
1862: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1863: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1864: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1865: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1866:
1867: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1868: l_reprocess_flag := 'Y'; -- Mark for reprocessing.
1863: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1864: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1865: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1866:
1867: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1868: l_reprocess_flag := 'Y'; -- Mark for reprocessing.
1869: EXIT l_cap_record_loop;
1870: END IF;
1871: -- Type of hole if statement
1872: END LOOP;
1873: -- for j in l_last_processed_cap_index + 1 ..
1874: -- modifying l_sch_record_tab with hole info.
1875:
1876: PA_SCHEDULE_UTILS.log_message(1,'JM: 1');
1877: PA_SCHEDULE_UTILS.log_message(1,'l_j_index: ' || l_j_index);
1878: IF (l_reprocess_flag = 'Y') THEN
1879: l_last_processed_cap_index := l_j_index-1;
1880: ELSE
1873: -- for j in l_last_processed_cap_index + 1 ..
1874: -- modifying l_sch_record_tab with hole info.
1875:
1876: PA_SCHEDULE_UTILS.log_message(1,'JM: 1');
1877: PA_SCHEDULE_UTILS.log_message(1,'l_j_index: ' || l_j_index);
1878: IF (l_reprocess_flag = 'Y') THEN
1879: l_last_processed_cap_index := l_j_index-1;
1880: ELSE
1881: l_last_processed_cap_index := l_j_index;
1885: -- l_ResStartDateTab(i) > l_prev_res_end_date+1
1886: -- When there is a hole
1887:
1888: l_prev_res_end_date := l_ResEndDateTab(i);
1889: PA_SCHEDULE_UTILS.log_message(1,'JM: 2');
1890:
1891: END LOOP;
1892: -- FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last
1893: -- Finding holes
1900:
1901: END IF;
1902: -- if (NVL(l_ResStartDateTab.count,0) = 0) THEN
1903: -- If there are any records in resource denorm.
1904: PA_SCHEDULE_UTILS.log_message(1,'JM: 3');
1905: END IF;
1906: -- IF (NVL(x_sch_record_tab.count,0) <> 0) THEN
1907: -- If there are any capacity records.
1908:
1909: x_sch_record_tab := l_sch_record_tab;
1910: -- End 2196924: Fix table by setting 0 for all dates with no HR assignment.
1911:
1912: x_return_status := l_x_return_status;
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;
1910: -- End 2196924: Fix table by setting 0 for all dates with no HR assignment.
1911:
1912: x_return_status := l_x_return_status;
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;
1958: ELSE
1959: l_I := p_chg_sch_record_tab.first;
1960: END IF;
1961:
1962: PA_SCHEDULE_UTILS.log_message(1,'Start of apply_schedule_change .... ');
1963: LOOP
1964:
1965: IF (p_chg_sch_record_tab(l_I).change_type_code = 'U') THEN
1966: PA_SCHEDULE_UTILS.log_message(1,'U .... ');
1962: PA_SCHEDULE_UTILS.log_message(1,'Start of apply_schedule_change .... ');
1963: LOOP
1964:
1965: IF (p_chg_sch_record_tab(l_I).change_type_code = 'U') THEN
1966: PA_SCHEDULE_UTILS.log_message(1,'U .... ');
1967: -- Calling the SCHEDULE UTILS api which will append the record if it marked for updation
1968: PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_upd_sch_record_tab,
1969: l_x_return_status,x_msg_count,x_msg_data);
1970: ELSIF (p_chg_sch_record_tab(l_I).change_type_code = 'I') THEN
1964:
1965: IF (p_chg_sch_record_tab(l_I).change_type_code = 'U') THEN
1966: PA_SCHEDULE_UTILS.log_message(1,'U .... ');
1967: -- Calling the SCHEDULE UTILS api which will append the record if it marked for updation
1968: PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_upd_sch_record_tab,
1969: l_x_return_status,x_msg_count,x_msg_data);
1970: ELSIF (p_chg_sch_record_tab(l_I).change_type_code = 'I') THEN
1971: PA_SCHEDULE_UTILS.log_message(1,'I .... ');
1972:
1967: -- Calling the SCHEDULE UTILS api which will append the record if it marked for updation
1968: PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_upd_sch_record_tab,
1969: l_x_return_status,x_msg_count,x_msg_data);
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,
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:
1979: END IF;
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 .... ');
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;
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:
2054: -- This procedure will create the new schedule for the given change duration exception it will create by changing
2055: -- the duration
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:
2054: -- This procedure will create the new schedule for the given change duration exception it will create by changing
2055: -- the duration
2056: IF (p_sch_except_record.exception_type_code = 'CHANGE_DURATION' OR
2116: x_msg_data);
2117: END IF;
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',
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,
2127: x_msg_data => x_msg_data
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,
2169: x_return_status => l_x_return_status,
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,
2190: p_assignment_status_code => p_sch_record.assignment_status_code,
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');
2204:
2205: PA_SCHEDULE_UTILS.log_message(1,'ERROR in create_new_schedule procedure ');
2206: raise;
2207:
2208: END create_new_calendar;
2209:
2231:
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:
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
2241: -- values and create new hours for a given period only
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,
2290: x_msg_count => x_msg_count,
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,
2334: x_msg_count => x_msg_count,
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,
2410: x_msg_count => x_msg_count,
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,
2444: x_return_status => l_x_return_status,
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,
2468: x_msg_count,
2469: x_msg_data );
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,
2480: p_assignment_id => p_sch_except_record.assignment_id,
2481: p_calendar_id => p_sch_except_record.calendar_id,
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.
2531: /* Added the below if for 7663765 */
2532: IF (p_sch_except_record.exception_type_code = 'DURATION_PATTERN_SHIFT') then
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.
2531: /* Added the below if for 7663765 */
2532: IF (p_sch_except_record.exception_type_code = 'DURATION_PATTERN_SHIFT') then
2533:
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,
2572: x_msg_count,
2573: x_msg_data
2587: END IF;
2588:
2589: ELSE
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
2603: x_msg_data
2604: );
2605: END IF;
2606:
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
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,
2616: p_calendar_id => p_sch_except_record.calendar_id,
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');
2632:
2633: PA_SCHEDULE_UTILS.log_message(1,'ERROR in create_new_duration procedure ');
2634: raise;
2635:
2636: END create_new_duration;
2637:
2828: IF (l_Merge_Flag = 'Y') THEN
2829: PA_SCHEDULE_PKG.Update_Rows(l_final_sch_rec_tab,l_x_return_status,x_msg_count,x_msg_data);
2830: PA_SCHEDULE_PKG.Delete_Rows(l_del_sch_rec_tab,l_x_return_status,x_msg_count,x_msg_data);
2831: ELSE
2832: PA_SCHEDULE_UTILS.log_message(1,'INFO: No Merging required.... ');
2833:
2834: END IF;
2835:
2836: /*Added for bug 6921728*/
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');
2846:
2847: PA_SCHEDULE_UTILS.log_message(1,'ERROR in merge_work_pattern procedure ');
2848: raise;
2849: END merge_work_pattern;
2850:
2851: --*******************************************************************************
2884: x_sch_record_tab(1).Sunday_hours := nvl(p_sch_except_record.Sunday_hours, p_sch_record.Sunday_hours);
2885:
2886: -- Updating the records with the new work pattern
2887:
2888: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2889: p_project_id => p_sch_except_record.project_id,
2890: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2891: p_assignment_id => p_sch_except_record.assignment_id,
2892: p_calendar_id => p_sch_except_record.calendar_id,
2944: Raise Status_change_not_allowed;
2945: END IF;
2946:
2947: -- Updating the records with the given status
2948: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2949: p_project_id => p_sch_except_record.project_id,
2950: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2951: p_assignment_id => p_sch_except_record.assignment_id,
2952: p_calendar_id => p_sch_except_record.calendar_id,
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;
3026:
3030: l_chg_exp_start_date := p_sch_except_record.start_date;
3031: l_chg_exp_end_date := p_sch_except_record.end_date;
3032:
3033: -- Copying this schedule record for the further calculation
3034: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(p_sch_record_tab,
3035: p_sch_record_tab.first,
3036: p_sch_record_tab.last,
3037: l_temp_p_sch_record_tab,
3038: l_x_return_status,
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,
3047: x_msg_count,
3048: x_msg_data );
3085: END IF; -- IF (exception_type_code = 'RETAIN_DURATION_PATTERN_SHIFT')
3086:
3087: /* Ends uncommenting*/
3088:
3089: PA_SCHEDULE_UTILS.log_message(1,'SCH_START ',l_temp_p_sch_record_tab);
3090:
3091: PA_SCHEDULE_UTILS.log_message(1,'START EXCEPT START_DATE '||p_sch_except_record.start_date||' END_DATE '
3092: ||p_sch_except_record.end_date);
3093:
3087: /* Ends uncommenting*/
3088:
3089: PA_SCHEDULE_UTILS.log_message(1,'SCH_START ',l_temp_p_sch_record_tab);
3090:
3091: PA_SCHEDULE_UTILS.log_message(1,'START EXCEPT START_DATE '||p_sch_except_record.start_date||' END_DATE '
3092: ||p_sch_except_record.end_date);
3093:
3094: -- Exception date is falling outside the start date and end date of the schedule
3095: IF ( p_sch_except_record.start_date IS NOT NULL ) AND
3099: OR
3100: ( p_sch_except_record.start_date >
3101: l_temp_p_sch_record_tab(l_temp_last).end_date ) ) THEN
3102:
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
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,
3112: l_x_return_status,
3113: x_msg_count,
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 ,
3134: l_temp_p_sch_record_tab.first,
3135: l_temp_p_sch_record_tab.last,
3136: l_out_tr_sch_rec_tab,
3137: l_x_return_status,
3137: l_x_return_status,
3138: x_msg_count,
3139: x_msg_data );
3140: else */
3141: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3142: l_temp_p_sch_record_tab.first,
3143: l_temp_p_sch_record_tab.last,
3144: l_out_tr_sch_rec_tab,
3145: l_x_return_status,
3148: /* end if; */ /* commented for bug 9229210 */
3149: END IF;
3150: END IF;
3151:
3152: PA_SCHEDULE_UTILS.log_message(2,'BEFORE LOOP');
3153:
3154: l_I := l_temp_p_sch_record_tab.first ;
3155:
3156: IF ( p_sch_except_record.start_date IS NULL ) THEN
3160: l_end_date_done := true;
3161: END IF;
3162:
3163: LOOP
3164: PA_SCHEDULE_UTILS.log_message(2,'LOOP START (l_I='||to_char(l_I)||') s: '||
3165: l_temp_p_sch_record_tab(l_I).start_date||' e: '||
3166: l_temp_p_sch_record_tab(l_I).end_date );
3167:
3168: l_copy_cur_sch := false;
3172: -- end date will be one day minus from the start date of the scheule for the first record
3173: IF ( l_stat_date_done = false) THEN
3174: IF (( p_sch_except_record.start_date <= l_temp_p_sch_record_tab(l_I).end_date )) THEN
3175:
3176: PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date <= sch_start_date ');
3177:
3178: l_stat_date_done := true;
3179: IF ( ( l_I = l_temp_first ) AND
3180: ( p_sch_except_record.start_date
3179: IF ( ( l_I = l_temp_first ) AND
3180: ( p_sch_except_record.start_date
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,
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,
3202: x_msg_data );
3203: END IF;
3204: ELSE
3205:
3206: PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date <= sch_start_date AND NOT Last shift ');
3207:
3208: l_temp_p_sch_record_tab(l_I).start_date := p_sch_except_record.start_date;
3209:
3210: -- Bug#9952027 - Ensure that the existing schedules are marked for deletion in case of 'Shift Duration & Pattern'
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,
3225: l_temp_p_sch_record_tab.last,
3226: l_out_tr_sch_rec_tab,
3227: l_x_return_status,
3227: l_x_return_status,
3228: x_msg_count,
3229: x_msg_data );
3230: else */
3231: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3232: l_I,
3233: l_temp_p_sch_record_tab.last,
3234: l_out_tr_sch_rec_tab,
3235: l_x_return_status,
3239: END IF;
3240:
3241: END IF;
3242: ELSE
3243: PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date > sch_start_date AND MARKING SHIFT as DELETE');
3244: l_temp_p_sch_record_tab(l_I).change_type_code := 'D';
3245: END IF; -- start_date cond if */
3246: END IF; -- l_stat_date_done if */
3247:
3248: IF ( l_end_date_done = false) THEN
3249: IF (( p_sch_except_record.end_date <= l_temp_p_sch_record_tab(l_I).end_date )) THEN
3250: l_end_date_done := true;
3251:
3252: PA_SCHEDULE_UTILS.log_message(2,'inside exp_end_date <= sch_end_date ');
3253:
3254: l_temp_p_sch_record_tab(l_I).end_date := p_sch_except_record.end_date;
3255:
3256: -- Bug#9952027 - Ensure that the existing schedules are marked for deletion in case of 'Shift Duration & Pattern'
3261: end if;
3262:
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,
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,
3272: x_msg_count,
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,
3282: l_x_return_status,
3285: END IF;
3286: ELSIF ( ( p_sch_except_record.end_date > l_temp_p_sch_record_tab(l_I).end_date ) AND
3287: ( l_I = l_temp_last ) ) THEN
3288:
3289: PA_SCHEDULE_UTILS.log_message(2,'inside exp_end_date > sch_end_date AND LAST SHIFT ');
3290:
3291: l_copy_cur_sch := true;
3292: l_end_date_done := true;
3293: -- Copy this shift into l_out_tr_sch_rec_tab; */
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,
3299: l_x_return_status,
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,
3309: x_msg_count => x_msg_count,
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,
3330: l_temp_tr_sch_rec_tab.last,
3331: l_out_tr_sch_rec_tab,
3332: l_x_return_status,
3332: l_x_return_status,
3333: x_msg_count,
3334: x_msg_data );
3335: else */
3336: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_tr_sch_rec_tab ,
3337: l_temp_tr_sch_rec_tab.first,
3338: l_temp_tr_sch_rec_tab.last,
3339: l_out_tr_sch_rec_tab,
3340: l_x_return_status,
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,
3359: l_x_return_status,
3371: END IF;
3372:
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,
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,
3382: l_x_return_status,
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');
3395:
3396: PA_SCHEDULE_UTILS.log_message(1,'ERROR while running apply...');
3397: raise;
3398: END apply_change_duration;
3399:
3400: -- This procedure is caled from change_schedule procedure .This procedure will apply changes on schedule
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,
3454: x_msg_data );
3459:
3460: l_curr_exp_start_date := p_sch_except_record.start_date;
3461: l_curr_exp_end_date := p_sch_except_record.end_date;
3462:
3463: PA_SCHEDULE_UTILS.log_message(1,'SCH_START ',l_temp_p_sch_record_tab);
3464: PA_SCHEDULE_UTILS.log_message(1,'START EXCEPT START_DATE '||l_curr_exp_start_date||' END_DATE '||l_curr_exp_end_date);
3465:
3466: l_I := l_temp_p_sch_record_tab.first ;
3467:
3460: l_curr_exp_start_date := p_sch_except_record.start_date;
3461: l_curr_exp_end_date := p_sch_except_record.end_date;
3462:
3463: PA_SCHEDULE_UTILS.log_message(1,'SCH_START ',l_temp_p_sch_record_tab);
3464: PA_SCHEDULE_UTILS.log_message(1,'START EXCEPT START_DATE '||l_curr_exp_start_date||' END_DATE '||l_curr_exp_end_date);
3465:
3466: l_I := l_temp_p_sch_record_tab.first ;
3467:
3468: LOOP
3472:
3473: l_curr_sch_start_date := l_temp_p_sch_record_tab(l_I).start_date;
3474: l_curr_sch_end_date := l_temp_p_sch_record_tab(l_I).end_date;
3475:
3476: PA_SCHEDULE_UTILS.log_message(2,'SHIFT('||l_I||') '||l_curr_sch_start_date||' '||l_curr_sch_end_date);
3477: IF ( l_curr_exp_start_date <= l_curr_sch_end_date ) THEN
3478:
3479: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_end_date if .... ');
3480: IF ( l_curr_exp_start_date <= l_curr_sch_start_date ) THEN
3475:
3476: PA_SCHEDULE_UTILS.log_message(2,'SHIFT('||l_I||') '||l_curr_sch_start_date||' '||l_curr_sch_end_date);
3477: IF ( l_curr_exp_start_date <= l_curr_sch_end_date ) THEN
3478:
3479: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_end_date if .... ');
3480: IF ( l_curr_exp_start_date <= l_curr_sch_start_date ) THEN
3481:
3482: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_start_date if .... ');
3483: l_chg_exp_start_date := l_curr_sch_start_date;
3478:
3479: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_end_date if .... ');
3480: IF ( l_curr_exp_start_date <= l_curr_sch_start_date ) THEN
3481:
3482: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_start_date if .... ');
3483: l_chg_exp_start_date := l_curr_sch_start_date;
3484:
3485: IF ( l_curr_exp_end_date < l_curr_sch_end_date ) THEN
3486:
3483: l_chg_exp_start_date := l_curr_sch_start_date;
3484:
3485: IF ( l_curr_exp_end_date < l_curr_sch_end_date ) THEN
3486:
3487: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date < l_curr_sch_end_date if ...... ');
3488: l_chg_exp_end_date := l_curr_exp_end_date;
3489: l_temp_p_sch_record_tab(l_I).start_date := l_curr_exp_end_date + 1;
3490: IF (nvl(l_temp_p_sch_record_tab(l_I).change_type_code,'U') <> 'I') THEN -- Added If condition for 4349232
3491: l_temp_p_sch_record_tab(l_I).change_type_code := 'U';
3492: END IF;
3493:
3494: ELSIF ( l_curr_exp_end_date >= l_curr_sch_end_date ) THEN
3495:
3496: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date >= l_curr_sch_end_date if ...... ');
3497: l_chg_exp_end_date := l_curr_sch_end_date;
3498: l_temp_p_sch_record_tab(l_I).change_type_code := 'D';
3499:
3500: END IF;
3500: END IF;
3501: l_create_new_sch := true;
3502:
3503: IF ( l_curr_exp_end_date <= l_curr_sch_end_date ) THEN
3504: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date <= l_curr_sch_end_date if ........... ');
3505: l_change_done := true;
3506: END IF;
3507:
3508: ELSIF ( l_curr_exp_start_date > l_curr_sch_start_date ) THEN
3506: END IF;
3507:
3508: ELSIF ( l_curr_exp_start_date > l_curr_sch_start_date ) THEN
3509:
3510: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date > l_curr_sch_start_date if ...... ');
3511:
3512: l_temp_p_sch_record_tab(l_I).end_date := l_curr_exp_start_date - 1;
3513:
3514: IF (nvl(l_temp_p_sch_record_tab(l_I).change_type_code,'U') <> 'I') THEN -- Added If condition for 4287560
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,
3524: l_x_return_status,
3525: x_msg_count,
3526: x_msg_data );
3527: END IF;
3528:
3529: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3530: l_chg_exp_start_date := l_curr_exp_start_date;
3531: IF ( l_curr_exp_end_date > l_curr_sch_end_date ) THEN
3532: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date > l_curr_sch_end_date if ....... ');
3533: l_chg_exp_end_date := l_curr_sch_end_date;
3528:
3529: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3530: l_chg_exp_start_date := l_curr_exp_start_date;
3531: IF ( l_curr_exp_end_date > l_curr_sch_end_date ) THEN
3532: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date > l_curr_sch_end_date if ....... ');
3533: l_chg_exp_end_date := l_curr_sch_end_date;
3534: ELSE
3535: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date <= l_curr_sch_end_date if ....... ');
3536: l_chg_exp_end_date := l_curr_exp_end_date;
3531: IF ( l_curr_exp_end_date > l_curr_sch_end_date ) THEN
3532: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date > l_curr_sch_end_date if ....... ');
3533: l_chg_exp_end_date := l_curr_sch_end_date;
3534: ELSE
3535: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date <= l_curr_sch_end_date if ....... ');
3536: l_chg_exp_end_date := l_curr_exp_end_date;
3537: l_change_done := true;
3538: END IF;
3539: l_create_new_sch := true;
3538: END IF;
3539: l_create_new_sch := true;
3540:
3541: IF ( l_curr_exp_end_date < l_curr_sch_end_date ) THEN
3542: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date < l_curr_sch_end_date if .... ');
3543: l_create_third_sch := true;
3544: l_change_done := true;
3545: END IF;
3546:
3547: END IF; -- end of level 2 if */
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,
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,
3567: l_x_return_status,
3568: x_msg_count,
3569: x_msg_data );
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 ,
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,
3579: l_x_return_status,
3579: l_x_return_status,
3580: x_msg_count,
3581: x_msg_data );
3582: END IF;
3583: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3584: END IF;
3585:
3586: IF ( l_create_third_sch = true ) THEN
3587:
3584: END IF;
3585:
3586: IF ( l_create_third_sch = true ) THEN
3587:
3588: PA_SCHEDULE_UTILS.log_message(2,'inside l_create_third_sch if ........');
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
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,
3598: l_x_return_status,
3599: x_msg_count,
3600: x_msg_data );
3601: END IF;
3602:
3603: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
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 ........');
3603: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
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,
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,
3613: l_x_return_status,
3613: l_x_return_status,
3614: x_msg_count,
3615: x_msg_data );
3616: END IF;
3617: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3618: END IF;
3619:
3620: IF ( l_change_done ) OR ( l_I = l_sch_last ) THEN
3621:
3618: END IF;
3619:
3620: IF ( l_change_done ) OR ( l_I = l_sch_last ) THEN
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 ,
3620: IF ( l_change_done ) OR ( l_I = l_sch_last ) THEN
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,
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,
3630: l_x_return_status,
3630: l_x_return_status,
3631: x_msg_count,
3632: x_msg_data );
3633: END IF;
3634: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3635: END IF;
3636: EXIT;
3637: ELSE
3638: PA_SCHEDULE_UTILS.log_message(2,'go to next shift ..... ');
3634: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3635: END IF;
3636: EXIT;
3637: ELSE
3638: PA_SCHEDULE_UTILS.log_message(2,'go to next shift ..... ');
3639: l_I := l_temp_p_sch_record_tab.next(l_I);
3640: END IF;
3641:
3642: END LOOP;
3640: END IF;
3641:
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,
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,
3651: l_x_return_status,
3652: x_msg_count,
3653: x_msg_data );
3654: END IF;
3655:
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;
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');
3665:
3666: PA_SCHEDULE_UTILS.log_message(2,'ERROR in apply_other_changes ....');
3667: PA_SCHEDULE_UTILS.log_message(2,sqlerrm);
3668: raise;
3669: END apply_other_changes;
3670:
3663: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3664: p_procedure_name => 'apply_other_change');
3665:
3666: PA_SCHEDULE_UTILS.log_message(2,'ERROR in apply_other_changes ....');
3667: PA_SCHEDULE_UTILS.log_message(2,sqlerrm);
3668: raise;
3669: END apply_other_changes;
3670:
3671:
3767: END IF;
3768:
3769: END LOOP;
3770:
3771: PA_SCHEDULE_UTILS.log_message(1,'20: ' || l_x_return_status ||
3772: ' ' || x_msg_count ||
3773: ' ' || x_msg_data);
3774:
3775: -- if the records is having more than one status then updating the table for it column multi flag */
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),
3787: x_return_status => l_x_return_status );
3788: ELSE
3789: -- bug#9071974
3790: PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
3790: PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
3791: p_record_version_number => p_record_version_number,
3792: p_assignment_id => l_t_assignment_id,
3793: p_assignment_effort =>
3794: pa_schedule_utils.get_num_hours(l_t_project_id, l_t_assignment_id),
3795: x_return_status => l_x_return_status );
3796: END IF;
3797:
3798: PA_SCHEDULE_UTILS.log_message(1,'20: ' || l_x_return_status ||
3794: pa_schedule_utils.get_num_hours(l_t_project_id, l_t_assignment_id),
3795: x_return_status => l_x_return_status );
3796: END IF;
3797:
3798: PA_SCHEDULE_UTILS.log_message(1,'20: ' || l_x_return_status ||
3799: ' ' || x_msg_count ||
3800: ' ' || x_msg_data);
3801:
3802: -- If assignment record is successfully updated then call procedure to update pa project parties.
3835: IF ( l_t_end_date is NULL ) THEN
3836: l_t_end_date := l_t_asgn_end_date;
3837: END IF;
3838:
3839: PA_SCHEDULE_UTILS.log_message(1,'21: ' || l_x_return_status ||
3840: ' ' || x_msg_count ||
3841: ' ' || x_msg_data);
3842:
3843: IF p_called_by_proj_party = 'N' then -- Bug 6631033
3985:
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',
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:
4056: -- New code to process task assignment summation jraj 12/19/03
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:
4056: -- New code to process task assignment summation jraj 12/19/03
4057: -- Based on if p_sum_tasks_flag = Y
4058:
4139:
4140:
4141: END IF;
4142:
4143: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_calendar_schedule ....');
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:
4141: END IF;
4142:
4143: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_calendar_schedule ....');
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
4142:
4143: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_calendar_schedule ....');
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,
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,
4153: p_schedule_type_code => 'OPEN_ASSIGNMENT',
4154: p_assignment_id => p_assignment_id,
4158: x_msg_data => x_msg_data
4159: );
4160: END IF;
4161:
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
4159: );
4160: END IF;
4161:
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
4160: END IF;
4161:
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,
4182:
4183: END IF;
4184:
4185:
4186: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4187: PA_SCHEDULE_UTILS.log_message(1, 'End of the create_oasgn_schedule API ....');
4188: x_return_status := l_x_return_status;
4189: EXCEPTION
4190:
4183: END IF;
4184:
4185:
4186: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4187: PA_SCHEDULE_UTILS.log_message(1, 'End of the create_oasgn_schedule API ....');
4188: x_return_status := l_x_return_status;
4189: EXCEPTION
4190:
4191: /*Added for 4996210 */
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);
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',
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
4252: -- NULL, a new open assignment schedule needs to be generated based on the -- calendar id passed in, then copy the schedule to the rest of assignments.
4297: -- Copy schedules for the rest of the assignments in the table
4298: -- l_assignment_id_tbl.
4299: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4300: -- Update the passed schedule table of record for creating the schedule for open assignment
4301: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4302: px_sch_record_tab => l_current_sch_rec_tab,
4303: p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
4304: x_return_status => l_x_return_status,
4305: x_msg_count => x_msg_count,
4305: x_msg_count => x_msg_count,
4306: x_msg_data => x_msg_data
4307: );
4308: -- Add the schedule record to l_x_sch_rec_tab.
4309: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4310: p_sch_record_tab => l_current_sch_rec_tab,
4311: p_start_id => l_current_sch_rec_tab.first,
4312: p_end_id => l_current_sch_rec_tab.last,
4313: px_sch_record_tab => l_x_sch_rec_tab,
4325: x_msg_count => x_msg_count,
4326: x_msg_data => x_msg_data
4327: );
4328:
4329: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4330:
4331: -- Copy timeline data.
4332: PA_TIMELINE_PVT.copy_open_asgn_timeline (
4333: p_assignment_id_tbl => l_assignment_id_tbl,
4380: );
4381: END IF ;
4382: /* changes end for Bug 6145532*/
4383: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4384: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4385: -- Update the passed schedule record with p_assignment_status_code
4386: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4387: px_sch_record_tab => l_current_sch_rec_tab,
4388: p_schedule_type_code => 'OPEN_ASSIGNMENT',
4382: /* changes end for Bug 6145532*/
4383: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4384: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4385: -- Update the passed schedule record with p_assignment_status_code
4386: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4387: px_sch_record_tab => l_current_sch_rec_tab,
4388: p_schedule_type_code => 'OPEN_ASSIGNMENT',
4389: p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
4390: p_assignment_status_code => p_assignment_status_code,
4391: x_return_status => l_x_return_status,
4392: x_msg_count => x_msg_count,
4393: x_msg_data => x_msg_data
4394: );
4395: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
4396: -- Add the shedule record to l_x_sch_rec_tab.
4397: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4398: p_sch_record_tab => l_current_sch_rec_tab,
4399: p_start_id => l_current_sch_rec_tab.FIRST,
4393: x_msg_data => x_msg_data
4394: );
4395: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
4396: -- Add the shedule record to l_x_sch_rec_tab.
4397: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4398: p_sch_record_tab => l_current_sch_rec_tab,
4399: p_start_id => l_current_sch_rec_tab.FIRST,
4400: p_end_id => l_current_sch_rec_tab.LAST,
4401: px_sch_record_tab => l_x_sch_rec_tab,
4402: x_return_status => l_x_return_status,
4403: x_msg_count => x_msg_count,
4404: x_msg_data => x_msg_data
4405: );
4406: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API add_schedule_rec_tab .....');
4407: END LOOP;
4408:
4409: -- Bulk insert all the schedule records stored in l_x_sch_rec_tab.
4410: PA_SCHEDULE_PKG.insert_rows(
4412: x_return_status => l_x_return_status,
4413: x_msg_count => x_msg_count,
4414: x_msg_data => x_msg_data
4415: );
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
4450: x_msg_data
4451: );
4452:
4453: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4454: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4455: -- Update the passed schedule record with assignment_id.
4456: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4457: px_sch_record_tab => l_current_sch_rec_tab,
4458: p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
4452:
4453: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4454: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4455: -- Update the passed schedule record with assignment_id.
4456: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4457: px_sch_record_tab => l_current_sch_rec_tab,
4458: p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
4459: x_return_status => l_x_return_status,
4460: x_msg_count => x_msg_count,
4459: x_return_status => l_x_return_status,
4460: x_msg_count => x_msg_count,
4461: x_msg_data => x_msg_data
4462: );
4463: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
4464: -- Add the shedule record to l_x_sch_rec_tab.
4465: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4466: p_sch_record_tab => l_current_sch_rec_tab,
4467: p_start_id => l_current_sch_rec_tab.first,
4461: x_msg_data => x_msg_data
4462: );
4463: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
4464: -- Add the shedule record to l_x_sch_rec_tab.
4465: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4466: p_sch_record_tab => l_current_sch_rec_tab,
4467: p_start_id => l_current_sch_rec_tab.first,
4468: p_end_id => l_current_sch_rec_tab.last,
4469: px_sch_record_tab => l_x_sch_rec_tab,
4470: x_return_status => l_x_return_status,
4471: x_msg_count => x_msg_count,
4472: x_msg_data => x_msg_data
4473: );
4474: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API add_schedule_rec_tab .....');
4475: END LOOP;
4476:
4477: -- Bulk insert all the schedule records stored in l_x_sch_rec_tab.
4478: PA_SCHEDULE_PKG.insert_rows(
4480: x_return_status => l_x_return_status,
4481: x_msg_count => x_msg_count,
4482: x_msg_data => x_msg_data
4483: );
4484: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4485:
4486: -- Copy timeline data.
4487: PA_TIMELINE_PVT.copy_open_asgn_timeline (
4488: p_assignment_id_tbl => l_assignment_id_tbl,
4497: x_return_status := l_x_return_status;
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',
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
4578:
4602:
4603: -- If the calendar type is resource then it will generate the schedule on the basis of
4604: -- the calendar asign to the resource
4605: IF (p_schedule_basis_flag = 'R') then
4606: PA_SCHEDULE_UTILS.log_message(1, 'Schedule Basis Flag - R');
4607: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_resource_schedule ....');
4608:
4609: -- Calling tthe PVT API which will get the schedule for staffed asignment and calendar type is resource
4610: PA_SCHEDULE_PVT.get_resource_schedule(p_project_party_id,
4603: -- If the calendar type is resource then it will generate the schedule on the basis of
4604: -- the calendar asign to the resource
4605: IF (p_schedule_basis_flag = 'R') then
4606: PA_SCHEDULE_UTILS.log_message(1, 'Schedule Basis Flag - R');
4607: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_resource_schedule ....');
4608:
4609: -- Calling tthe PVT API which will get the schedule for staffed asignment and calendar type is resource
4610: PA_SCHEDULE_PVT.get_resource_schedule(p_project_party_id,
4611: 'PA_PROJECT_PARTY_ID',
4615: l_x_return_status,
4616: x_msg_count,
4617: x_msg_data
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
4616: x_msg_count,
4617: x_msg_data
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,
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,
4627: x_msg_count,
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,
4627: x_msg_count,
4628: x_msg_data
4627: x_msg_count,
4628: x_msg_data
4629: );
4630: END IF;
4631: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API apply_percentage ....');
4632:
4633: -- If the calendar type is other then resource then it will generate
4634: -- the schedule on the basis of the assignment asign to the calendar type
4635: ELSIF (p_schedule_basis_flag = 'A') THEN
4632:
4633: -- If the calendar type is other then resource then it will generate
4634: -- the schedule on the basis of the assignment asign to the calendar type
4635: ELSIF (p_schedule_basis_flag = 'A') THEN
4636: PA_SCHEDULE_UTILS.log_message(1, 'Schedule basis flag = A ....');
4637: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_assignment_schedule ....');
4638:
4639: -- code fix starts for bug 2335580
4640: SELECT start_date, end_date,calendar_id
4633: -- If the calendar type is other then resource then it will generate
4634: -- the schedule on the basis of the assignment asign to the calendar type
4635: ELSIF (p_schedule_basis_flag = 'A') THEN
4636: PA_SCHEDULE_UTILS.log_message(1, 'Schedule basis flag = A ....');
4637: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_assignment_schedule ....');
4638:
4639: -- code fix starts for bug 2335580
4640: SELECT start_date, end_date,calendar_id
4641: into l_req_start_date, l_req_end_date, l_calendar_id
4671: x_msg_data
4672: );
4673: END IF;
4674: END IF;
4675: PA_SCHEDULE_UTILS.log_message(1, 'After get_assignment_schedule .',l_x_sch_record_tab);
4676: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_assignment_schedule ....');
4677:
4678: -- if the calendar type is other then resource then it will generate
4679: -- the schedule on the basis of the assignment asign to the calendar type
4672: );
4673: END IF;
4674: END IF;
4675: PA_SCHEDULE_UTILS.log_message(1, 'After get_assignment_schedule .',l_x_sch_record_tab);
4676: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_assignment_schedule ....');
4677:
4678: -- if the calendar type is other then resource then it will generate
4679: -- the schedule on the basis of the assignment asign to the calendar type
4680: ELSIF (p_schedule_basis_flag IN ('P','O')) THEN
4677:
4678: -- if the calendar type is other then resource then it will generate
4679: -- the schedule on the basis of the assignment asign to the calendar type
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
4678: -- if the calendar type is other then resource then it will generate
4679: -- the schedule on the basis of the assignment asign to the calendar type
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,
4692: x_msg_data
4693: );
4694: END IF;
4695:
4696: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_calendar_schedule ....');
4697: END IF;
4698:
4699:
4700: IF p_sum_tasks_flag = 'Y' then
4719: END IF;
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,
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',
4729: p_calendar_id => p_calendar_id,
4730: p_assignment_id => p_assignment_id,
4734: x_msg_data => x_msg_data
4735: );
4736: END IF;
4737:
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
4735: );
4736: END IF;
4737:
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
4736: END IF;
4737:
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(
4759: x_msg_count =>x_msg_count ,
4760: x_msg_data =>x_msg_data );
4761: END IF;
4762:
4763: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4764: PA_SCHEDULE_UTILS.log_message(1,'End of the create_sasgn_schedule API ... ');
4765: x_return_status := l_x_return_status;
4766: EXCEPTION
4767: WHEN OTHERS THEN
4760: x_msg_data =>x_msg_data );
4761: END IF;
4762:
4763: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4764: PA_SCHEDULE_UTILS.log_message(1,'End of the create_sasgn_schedule API ... ');
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);
4764: PA_SCHEDULE_UTILS.log_message(1,'End of the create_sasgn_schedule API ... ');
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',
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');
4837: raise;
4838:
4839: END delete_asgn_schedules;
4895: FROM pa_project_assignments
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:
4942: END LOOP;
4943: CLOSE C1;
4944:
4945: IF l_loop_thru_record ='N' THEN
4946: PA_SCHEDULE_UTILS.log_message(1,'Change status API called once');
4947:
4948: SELECT min(start_date), max(end_date)
4949: INTO l_start_date, l_end_date
4950: FROM pa_schedules
4949: INTO l_start_date, l_end_date
4950: FROM pa_schedules
4951: where assignment_id= p_assignment_id;
4952:
4953: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status');
4954: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
4955: p_status_code => l_first_status,
4956: p_status_type => l_status_type,
4957: x_wf_success_status_code => l_next_status_code,
4958: x_wf_failure_status_code => l_temp_status_code,
4959: x_return_status => l_x_return_status,
4960: x_error_message_code => x_msg_data) ;
4961: if (l_next_status_code <> l_first_status) then
4962: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status');
4963:
4964: PA_SCHEDULE_PUB.change_status(
4965: p_record_version_number => l_record_version_number,
4966: p_project_id => l_project_id,
4984: ELSE
4985: --Code change ends for bug 2279209
4986:
4987: FOR rec IN C1 LOOP
4988: PA_SCHEDULE_UTILS.log_message(1, 'In loop: ' || rec.start_date ||
4989: ' ' || rec.end_date);
4990: -- Call Ramesh's API to get next success status.
4991: -- For now, make them the same.
4992: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
4997: x_return_status => l_x_return_status,
4998: x_error_message_code => x_msg_data) ;
4999:
5000: if (l_next_status_code <> rec.status_code) then
5001: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status');
5002: PA_SCHEDULE_PUB.change_status(
5003: p_record_version_number => l_record_version_number,
5004: p_project_id => l_project_id,
5005: p_calendar_id => l_calendar_id,
5052: l_first_start_date := l_start_date;
5053: END IF;
5054:
5055: IF l_first_status <> l_status_code THEN
5056: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status');
5057: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
5058: p_status_code => l_first_status,
5059: p_status_type => l_status_type,
5060: x_wf_success_status_code => l_next_status_code,
5062: x_return_status => l_x_return_status,
5063: x_error_message_code => x_msg_data);
5064:
5065: IF (l_next_status_code <> l_first_status) THEN
5066: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status');
5067:
5068: PA_SCHEDULE_PUB.change_status(
5069: p_record_version_number => l_record_version_number,
5070: p_project_id => l_project_id,
5089: END IF;
5090: l_last_end_date := l_end_date;
5091:
5092: IF C1%ROWCOUNT = l_count THEN
5093: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status for last record');
5094: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
5095: p_status_code => l_first_status,
5096: p_status_type => l_status_type,
5097: x_wf_success_status_code => l_next_status_code,
5099: x_return_status => l_x_return_status,
5100: x_error_message_code => x_msg_data);
5101:
5102: IF (l_next_status_code <> l_first_status) THEN
5103: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status last record');
5104:
5105: PA_SCHEDULE_PUB.change_status(
5106: p_record_version_number => l_record_version_number,
5107: p_project_id => l_project_id,
5130:
5131:
5132: -- If records exist in pa_schedules_history with last_approved_flag
5133: -- set the flag to 'N'
5134: PA_SCHEDULE_UTILS.log_message(1, 'Setting last approved flag to N');
5135: update pa_schedules_history
5136: set last_approved_flag = 'N'
5137: where assignment_id = p_assignment_id
5138: and last_approved_flag = 'Y';
5136: set last_approved_flag = 'N'
5137: where assignment_id = p_assignment_id
5138: and last_approved_flag = 'Y';
5139:
5140: PA_SCHEDULE_UTILS.log_message(1,'End of the update_sch_wf_success API ... '
5141: || l_x_return_status);
5142:
5143: x_return_status := l_x_return_status;
5144: x_msg_count := FND_MSG_PUB.Count_Msg;
5158:
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',
5217: FROM pa_project_assignments
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:
5301: l_first_start_date := l_start_date;
5302: END IF;
5303:
5304: IF l_first_status <> l_status_code THEN
5305: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status');
5306: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
5307: p_status_code => l_first_status,
5308: p_status_type => l_status_type,
5309: x_wf_success_status_code => l_temp_status_code,
5311: x_return_status => l_x_return_status,
5312: x_error_message_code => x_msg_data);
5313:
5314: IF (l_next_status_code <> l_first_status) THEN
5315: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status');
5316:
5317: PA_SCHEDULE_PUB.change_status(
5318: p_record_version_number => l_record_version_number,
5319: p_project_id => l_project_id,
5338: END IF;
5339: l_last_end_date := l_end_date;
5340:
5341: IF C1%ROWCOUNT = l_count THEN
5342: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status for last record');
5343: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
5344: p_status_code => l_first_status,
5345: p_status_type => l_status_type,
5346: x_wf_success_status_code => l_temp_status_code,
5348: x_return_status => l_x_return_status,
5349: x_error_message_code => x_msg_data);
5350:
5351: IF (l_next_status_code <> l_first_status) THEN
5352: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status last record');
5353:
5354: PA_SCHEDULE_PUB.change_status(
5355: p_record_version_number => l_record_version_number,
5356: p_project_id => l_project_id,
5393: -- added for Bug Fix: 4537865
5394: End If;
5395:
5396: x_return_status := l_x_return_status;
5397: PA_SCHEDULE_UTILS.log_message(1,'End of the update_sch_wf_failure API ... ');
5398:
5399: EXCEPTION
5400: WHEN OTHERS THEN
5401: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_sch_wf_failure API ..'||sqlerrm);
5397: PA_SCHEDULE_UTILS.log_message(1,'End of the update_sch_wf_failure API ... ');
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',
5438: where assignment_id = p_assignment_id
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
5510: -- added for Bug fix: 4537865
5511: End If;
5512:
5513: x_return_status := l_x_return_status;
5514: PA_SCHEDULE_UTILS.log_message(1,'End of the revert_to_last_approved API ... ');
5515:
5516: EXCEPTION
5517: WHEN OTHERS THEN
5518: PA_SCHEDULE_UTILS.log_message(1,'ERROR in revert_to_last_approved API ..'||sqlerrm);
5514: PA_SCHEDULE_UTILS.log_message(1,'End of the revert_to_last_approved API ... ');
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',
5565: from pa_schedules
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.
5618: -- added for bug fix: 4537865
5619: End If;
5620:
5621: x_return_status := l_x_return_status;
5622: PA_SCHEDULE_UTILS.log_message(1,'End of the update_history_table API ... ');
5623:
5624: EXCEPTION
5625: WHEN OTHERS THEN
5626: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_history_table API ..'||sqlerrm);
5622: PA_SCHEDULE_UTILS.log_message(1,'End of the update_history_table API ... ');
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',
6208: WHERE item_type = p_item_type
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
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);
6218: SELECT resource_id, start_date, end_date
6219: INTO l_resource_id, l_start_date, l_end_date
6220: FROM pa_project_assignments
6221: WHERE assignment_id = v_c1.assignment_id;
6218: SELECT resource_id, start_date, end_date
6219: INTO l_resource_id, l_start_date, l_end_date
6220: FROM pa_project_assignments
6221: WHERE assignment_id = v_c1.assignment_id;
6222: PA_SCHEDULE_UTILS.debug('l_resource_id = '|| l_resource_id);
6223:
6224: IF G_AVAILABILITY_CAL_PERIOD = 'DAILY' THEN
6225: select distinct fi.assignment_id,
6226: decode (mass.assignment_id, null, 'N', 'Y') intra_txn_conflict_flag
6298: x_msg_data => x_msg_data);
6299:
6300:
6301: ELSIF G_AVAILABILITY_CAL_PERIOD = 'WEEKLY' THEN
6302: PA_SCHEDULE_UTILS.debug('Entering check_overcommitment_mult: WEEKLY');
6303:
6304: select distinct fi.assignment_id,
6305: decode (mass.assignment_id, null, 'N', 'Y') intra_txn_flag
6306: BULK COLLECT INTO l_assignment_id_tbl, l_intra_txn_conflict_flag_tbl
6368: and a.wf_success_status_code = b.project_status_code
6369: and b.project_system_status_code = 'STAFFED_ASGMT_CONF';
6370:
6371:
6372: PA_SCHEDULE_UTILS.debug('Before check_self_conflict');
6373:
6374: -- Check self conflict.
6375: check_self_conflict(p_assignment_id => v_c1.assignment_id,
6376: p_resource_id => l_resource_id,
6380: x_return_status => x_return_status,
6381: x_msg_count => x_msg_count,
6382: x_msg_data => x_msg_data);
6383:
6384: PA_SCHEDULE_UTILS.debug('After check_self_conflict');
6385:
6386: END IF;
6387:
6388: -- Insert the self conflicting assignment_id if applicable.
6399: -- l_intra_txn_conflict_flag_tbl(1) := 'N';
6400: END IF;
6401: END IF;
6402:
6403: PA_SCHEDULE_UTILS.debug('Before insert_rows into conflict history');
6404: IF l_assignment_id_tbl.COUNT > 0 THEN
6405: PA_ASGN_CONFLICT_HIST_PKG.insert_rows(p_conflict_group_id => l_conflict_group_id,
6406: p_assignment_id => v_c1.assignment_id,
6407: p_conflict_assignment_id_tbl => l_assignment_id_tbl,
6420: END IF;
6421: -- added for bug fix: 4537865
6422:
6423: END IF;
6424: PA_SCHEDULE_UTILS.debug('After insert_rows into conflict history');
6425:
6426: END IF;
6427: END LOOP;
6428:
6551: ELSE
6552: x_self_conflict_flag := 'Y';
6553: END IF;
6554: CLOSE c1;
6555: PA_SCHEDULE_UTILS.debug('daily: x_self_conflict_flag = ' || x_self_conflict_flag);
6556: ELSIF G_AVAILABILITY_CAL_PERIOD = 'WEEKLY' THEN
6557: -- Bug 2288823: check_self_conflict for the period from the week start date of
6558: -- p_start_date and the week end date of p_end_date for WEEKLY profile.
6559: l_week_start_date := PA_TIMELINE_UTIL.get_week_end_date(p_org_id => NULL,
6567: ELSE
6568: x_self_conflict_flag := 'Y';
6569: END IF;
6570: CLOSE c2;
6571: PA_SCHEDULE_UTILS.debug('weekly: x_self_conflict_flag = ' || x_self_conflict_flag);
6572: END IF;
6573:
6574:
6575: EXCEPTION
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;
6673: FETCH c1 INTO v_c1;
6674: IF c1%NOTFOUND THEN
6677: l_resolve_conflict_action_code := v_c1.resolve_conflicts_action_code;
6678: END IF;
6679: CLOSE c1;
6680:
6681: PA_SCHEDULE_UTILS.debug('l_resolve_conflict_action_code = ' || l_resolve_conflict_action_code);
6682:
6683: -- Processing for action code 'KEEP_CONFLICTS'
6684: IF l_resolve_conflict_action_code = 'KEEP_CONFLICTS' THEN
6685: PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
6694: -- process. If yes, return status 'E', populate the error stack with the
6695: -- error message.
6696: FOR v_c3 IN c3 LOOP
6697: IF v_c3.locking_flag = 'Y' THEN
6698: pa_schedule_utils.log_message(1, 'Remove conflicts failed due to assignments locking');
6699: RAISE l_remove_conflicts_failed;
6700: END IF;
6701: END LOOP;
6702:
6791:
6792: IF l_assignment_id_tbl.COUNT > 0 THEN
6793: l_count := 0;
6794: l_cur_item_date := l_item_date_tbl(l_item_date_tbl.FIRST)-1;
6795: --PA_SCHEDULE_UTILS.debug ('l_cur_item_date = '|| l_cur_item_date);
6796: l_cur_week_end_date := l_week_end_date_tbl(l_week_end_date_tbl.FIRST);
6797: --PA_SCHEDULE_UTILS.debug ('l_cur_week_end_date = '|| l_cur_week_end_date);
6798:
6799: FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
6793: l_count := 0;
6794: l_cur_item_date := l_item_date_tbl(l_item_date_tbl.FIRST)-1;
6795: --PA_SCHEDULE_UTILS.debug ('l_cur_item_date = '|| l_cur_item_date);
6796: l_cur_week_end_date := l_week_end_date_tbl(l_week_end_date_tbl.FIRST);
6797: --PA_SCHEDULE_UTILS.debug ('l_cur_week_end_date = '|| l_cur_week_end_date);
6798:
6799: FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
6800: --PA_SCHEDULE_UTILS.debug ('Entering l_assignment_id_tbl LOOP');
6801: --PA_SCHEDULE_UTILS.debug ('j = ' || j);
6796: l_cur_week_end_date := l_week_end_date_tbl(l_week_end_date_tbl.FIRST);
6797: --PA_SCHEDULE_UTILS.debug ('l_cur_week_end_date = '|| l_cur_week_end_date);
6798:
6799: FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
6800: --PA_SCHEDULE_UTILS.debug ('Entering l_assignment_id_tbl LOOP');
6801: --PA_SCHEDULE_UTILS.debug ('j = ' || j);
6802:
6803: -- When starting a new week.
6804: IF l_week_end_date_tbl(j) <> l_cur_week_end_date AND l_cur_work_pattern_tbl.COUNT > 0 THEN
6797: --PA_SCHEDULE_UTILS.debug ('l_cur_week_end_date = '|| l_cur_week_end_date);
6798:
6799: FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
6800: --PA_SCHEDULE_UTILS.debug ('Entering l_assignment_id_tbl LOOP');
6801: --PA_SCHEDULE_UTILS.debug ('j = ' || j);
6802:
6803: -- When starting a new week.
6804: IF l_week_end_date_tbl(j) <> l_cur_week_end_date AND l_cur_work_pattern_tbl.COUNT > 0 THEN
6805: --PA_SCHEDULE_UTILS.debug('New week: l_week_end_date_tbl(j) = '||l_week_end_date_tbl(j));
6801: --PA_SCHEDULE_UTILS.debug ('j = ' || j);
6802:
6803: -- When starting a new week.
6804: IF l_week_end_date_tbl(j) <> l_cur_week_end_date AND l_cur_work_pattern_tbl.COUNT > 0 THEN
6805: --PA_SCHEDULE_UTILS.debug('New week: l_week_end_date_tbl(j) = '||l_week_end_date_tbl(j));
6806: l_cur_week_end_date := l_week_end_date_tbl(j);
6807: -- Insert l_cur_work_pattern_tbl into l_work_pattern_tbl
6808: insert_work_pattern_tab(p_cur_work_pattern_tbl => l_cur_work_pattern_tbl,
6809: x_work_pattern_tbl => l_work_pattern_tbl,
6816: END IF;
6817:
6818: -- When starting a new item_date.
6819: IF l_item_date_tbl(j) <> l_cur_item_date THEN
6820: --PA_SCHEDULE_UTILS.debug('New item date: l_item_date_tbl(j) = '||l_item_date_tbl(j));
6821: -- Remove the overcommitment quantity for all the assignments
6822: -- on the current item date.
6823: IF l_count > 0 THEN
6824: update_work_pattern_record(p_overcom_quantity => l_cur_overcom_quantity,
6846: x_msg_data => x_msg_data);
6847:
6848: -- When starting a new assignment on the same item_date.
6849: ELSIF l_item_date_tbl(j) = l_cur_item_date AND l_assignment_id_tbl(j) <> l_cur_assignment_id THEN
6850: --PA_SCHEDULE_UTILS.debug('New assignment: l_assignment_id_tbl(j) = '||l_assignment_id_tbl(j));
6851: l_count := l_count +1;
6852: insert_work_pattern_record(p_assignment_id => l_assignment_id_tbl(j),
6853: p_item_quantity => l_item_quantity_tbl(j),
6854: p_item_date => l_item_date_tbl(j),
6890:
6891: -- Update schedules for those conflicting assignments based on the work
6892: -- pattern information stored in l_work_pattern_tbl.
6893: FOR v_c3 IN c3 LOOP
6894: PA_SCHEDULE_UTILS.debug ('Entering c3 loop');
6895: -- 2167889: null instead of record_version_number
6896: SELECT null, project_id, calendar_id, assignment_type,start_date, end_date
6897: INTO l_record_version_number, l_project_id, l_calendar_id, l_assignment_type, l_asgn_start_date, l_asgn_end_date
6898: FROM pa_project_assignments
6896: SELECT null, project_id, calendar_id, assignment_type,start_date, end_date
6897: INTO l_record_version_number, l_project_id, l_calendar_id, l_assignment_type, l_asgn_start_date, l_asgn_end_date
6898: FROM pa_project_assignments
6899: WHERE assignment_id = v_c3.conflict_assignment_id;
6900: PA_SCHEDULE_UTILS.debug ('l_work_pattern_tbl.COUNT = '|| l_work_pattern_tbl.COUNT);
6901: FOR i IN l_work_pattern_tbl.FIRST .. l_work_pattern_tbl.LAST LOOP
6902: IF l_work_pattern_tbl(i).assignment_id = v_c3.conflict_assignment_id THEN
6903:
6904: -- Calculate the start_date and end_date for the updating period.
6922: ELSE
6923: l_end_date := l_conflict_end_date;
6924: END IF;
6925: END IF;
6926: PA_SCHEDULE_UTILS.debug('Before Change Work Pattern');
6927: PA_SCHEDULE_UTILS.debug('l_start_date = '|| l_start_date);
6928: PA_SCHEDULE_UTILS.debug('l_end_date = '|| l_end_date);
6929:
6930: IF l_work_pattern_tbl(i).monday_hours = -99 THEN
6923: l_end_date := l_conflict_end_date;
6924: END IF;
6925: END IF;
6926: PA_SCHEDULE_UTILS.debug('Before Change Work Pattern');
6927: PA_SCHEDULE_UTILS.debug('l_start_date = '|| l_start_date);
6928: PA_SCHEDULE_UTILS.debug('l_end_date = '|| l_end_date);
6929:
6930: IF l_work_pattern_tbl(i).monday_hours = -99 THEN
6931: l_work_pattern_tbl(i).monday_hours := NULL;
6924: END IF;
6925: END IF;
6926: PA_SCHEDULE_UTILS.debug('Before Change Work Pattern');
6927: PA_SCHEDULE_UTILS.debug('l_start_date = '|| l_start_date);
6928: PA_SCHEDULE_UTILS.debug('l_end_date = '|| l_end_date);
6929:
6930: IF l_work_pattern_tbl(i).monday_hours = -99 THEN
6931: l_work_pattern_tbl(i).monday_hours := NULL;
6932: END IF;
6948: IF l_work_pattern_tbl(i).sunday_hours = -99 THEN
6949: l_work_pattern_tbl(i).sunday_hours := NULL;
6950: END IF;
6951:
6952: PA_SCHEDULE_UTILS.debug('work_pattern = '||
6953: l_work_pattern_tbl(i).monday_hours||';'||
6954: l_work_pattern_tbl(i).tuesday_hours||';'||
6955: l_work_pattern_tbl(i).wednesday_hours||';'||
6956: l_work_pattern_tbl(i).thursday_hours||';'||
6979: x_return_status => x_return_status,
6980: x_msg_count => x_msg_count,
6981: x_msg_data => x_msg_data );
6982:
6983: PA_SCHEDULE_UTILS.debug('After Change Work Pattern');
6984: END IF;
6985: END LOOP;
6986:
6987: -- Update pa_assignment_conflict_hist table.
7084: ELSIF l_week_day = 'SUN' THEN
7085: x_work_pattern_tbl(l_count).sunday_hours := p_item_quantity;
7086: END IF;
7087:
7088: PA_SCHEDULE_UTILS.debug('After insert_work_pattern_record: work_pattern = '||
7089: x_work_pattern_tbl(l_count).monday_hours||';'||
7090: x_work_pattern_tbl(l_count).tuesday_hours||';'||
7091: x_work_pattern_tbl(l_count).wednesday_hours||';'||
7092: x_work_pattern_tbl(l_count).thursday_hours||';'||
7196: x_work_pattern_tbl(j).sunday_hours:=0;
7197: END IF;
7198: END IF;
7199:
7200: PA_SCHEDULE_UTILS.debug('After update_work_pattern_record: work_pattern = '||
7201: x_work_pattern_tbl(j).monday_hours||';'||
7202: x_work_pattern_tbl(j).tuesday_hours||';'||
7203: x_work_pattern_tbl(j).wednesday_hours||';'||
7204: x_work_pattern_tbl(j).thursday_hours||';'||
7246: -- Otherwise, insert a new record.
7247: FOR i IN x_work_pattern_tbl.FIRST .. x_work_pattern_tbl.LAST LOOP
7248: IF x_work_pattern_tbl(i).assignment_id = p_cur_work_pattern_tbl(j).assignment_id AND x_work_pattern_tbl(i).end_date = p_cur_work_pattern_tbl(j).end_date - 7 THEN
7249:
7250: PA_SCHEDULE_UTILS.debug('Inside insert table: x_work_pattern_tbl(i).end_date = '|| x_work_pattern_tbl(i).end_date);
7251:
7252: IF (x_work_pattern_tbl(i).monday_hours = p_cur_work_pattern_tbl(j).monday_hours
7253: AND x_work_pattern_tbl(i).tuesday_hours = p_cur_work_pattern_tbl(j).tuesday_hours
7254: AND x_work_pattern_tbl(i).wednesday_hours = p_cur_work_pattern_tbl(j).wednesday_hours
7440: FROM DUAL;
7441:
7442: l_conflict_proj_id := v_c2.project_id;
7443: l_conflict_proj_mgr_id := PA_PROJECTS_MAINT_UTILS.get_project_manager(v_c2.project_id);
7444: PA_SCHEDULE_UTILS.debug('l_conflict_proj_mgr_id = '||l_conflict_proj_mgr_id);
7445: -- Send notifications to conflicting project managers.
7446: IF l_conflict_proj_mgr_id IS NOT NULL THEN
7447:
7448: WF_DIRECTORY.getusername
7450: p_orig_system_id => l_conflict_proj_mgr_id,
7451: p_name => l_conflict_mgr_user_name,
7452: p_display_name => l_conflict_mgr_display_name);
7453:
7454: PA_SCHEDULE_UTILS.debug('l_conflict_mgr_user_name = '|| l_conflict_mgr_user_name);
7455:
7456: -- Create the WF process
7457: wf_engine.CreateProcess ( ItemType => l_item_type,
7458: ItemKey => l_item_key,
7594: -- Send Self Conflicts notifications.
7595: OPEN c3;
7596: FETCH c3 INTO v_c3;
7597: IF (c3%FOUND) THEN
7598: PA_SCHEDULE_UTILS.debug('Send self overcommitment notifications');
7599: -- Create the WF process
7600: SELECT PA_PRM_WF_ITEM_KEY_S.nextval
7601: INTO l_item_key
7602: FROM DUAL;
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);
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',
8100: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
8101:
8102: -- Start of Debugging Statements
8103: IF l_debug_mode = 'Y' THEN -- 4387388
8104: PA_SCHEDULE_UTILS.log_message(1, 'p_start_date: ' || p_start_date);
8105: PA_SCHEDULE_UTILS.log_message(1, 'p_end_date: ' || p_end_date);
8106: IF p_task_assignments_tbl IS NOT NULL THEN
8107: IF p_task_assignments_tbl.count > 0 THEN
8108: FOR i IN p_task_assignments_tbl.first .. p_task_assignments_tbl.last LOOP
8101:
8102: -- Start of Debugging Statements
8103: IF l_debug_mode = 'Y' THEN -- 4387388
8104: PA_SCHEDULE_UTILS.log_message(1, 'p_start_date: ' || p_start_date);
8105: PA_SCHEDULE_UTILS.log_message(1, 'p_end_date: ' || p_end_date);
8106: IF p_task_assignments_tbl IS NOT NULL THEN
8107: IF p_task_assignments_tbl.count > 0 THEN
8108: FOR i IN p_task_assignments_tbl.first .. p_task_assignments_tbl.last LOOP
8109: PA_SCHEDULE_UTILS.log_message(1, 'p_task_assignments_tbl('||i||'): ' || p_task_assignments_tbl(i));
8105: PA_SCHEDULE_UTILS.log_message(1, 'p_end_date: ' || p_end_date);
8106: IF p_task_assignments_tbl IS NOT NULL THEN
8107: IF p_task_assignments_tbl.count > 0 THEN
8108: FOR i IN p_task_assignments_tbl.first .. p_task_assignments_tbl.last LOOP
8109: PA_SCHEDULE_UTILS.log_message(1, 'p_task_assignments_tbl('||i||'): ' || p_task_assignments_tbl(i));
8110: END LOOP;
8111: END IF;
8112: END IF;
8113: IF p_schedule_tbl IS NOT NULL THEN
8112: END IF;
8113: IF p_schedule_tbl IS NOT NULL THEN
8114: IF p_schedule_tbl.count > 0 THEN
8115: FOR schedule_counter IN p_schedule_tbl.first .. p_schedule_tbl.last LOOP
8116: PA_SCHEDULE_UTILS.log_message(1,
8117: p_schedule_tbl(schedule_counter).start_date || '|' ||
8118: p_schedule_tbl(schedule_counter).end_date || '|' ||
8119: round(p_schedule_tbl(schedule_counter).monday_hours,2) || '|' ||
8120: round(p_schedule_tbl(schedule_counter).tuesday_hours,2) || '|' ||
8349: IF l_debug_mode = 'Y' THEN -- 4387388
8350: IF x_schedule_tbl IS NOT NULL THEN
8351: IF x_schedule_tbl.count > 0 THEN
8352: FOR schedule_counter IN x_schedule_tbl.first .. x_schedule_tbl.last LOOP
8353: PA_SCHEDULE_UTILS.log_message(1,
8354: x_schedule_tbl(schedule_counter).start_date || '|' ||
8355: x_schedule_tbl(schedule_counter).end_date || '|' ||
8356: round(x_schedule_tbl(schedule_counter).monday_hours,2) || '|' ||
8357: round(x_schedule_tbl(schedule_counter).tuesday_hours,2) || '|' ||