[Home] [Help]
178:
179:
180: -- Appending the record which is being changed in above validation
181: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
182: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_curr_schedule_rec,1,1,l_out_schedule_rec,l_x_return_status,x_msg_count,
183: x_msg_data);
184: END IF;
185:
186: IF (l_st_dt_done = TRUE ) AND (l_end_dt_done = TRUE) THEN
198: -- If the start date is lower than the schedule start date the its schedule will be 0 and take the actual schedule
199: -- on the schedule start date
200: IF (p_start_date < l_out_schedule_rec(l_I).start_date) THEN
201: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
202: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
203: x_msg_data);
204: END IF;
205:
206: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
203: x_msg_data);
204: END IF;
205:
206: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
207: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,p_start_date =>p_start_date,
208: p_end_date =>l_out_schedule_rec(l_I).start_date -1 ,p_monday_hours =>0.00,p_tuesday_hours =>0.00,
209: p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
210: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,
211: x_msg_data =>x_msg_data);
211: x_msg_data =>x_msg_data);
212: END IF;
213:
214: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
215: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
216: x_msg_data);
217: END IF;
218:
219: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
216: x_msg_data);
217: END IF;
218:
219: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
220: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
221: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
222: END IF;
223: l_x_sch_copy_done := TRUE;
224: END IF;
225: -- If the end date is beyond the end date of the schedule of the passed calendar
226: -- then its that period will be having 0 work pattern
227: IF (p_end_date > l_out_schedule_rec(l_J).end_date) THEN
228: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
229: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,
230: x_msg_count,x_msg_data);
231: END IF;
232:
233: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
230: x_msg_count,x_msg_data);
231: END IF;
232:
233: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
234: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
235: p_start_date =>l_out_schedule_rec(l_J).end_date + 1 , p_end_date => p_end_date,p_monday_hours =>0.00,
236: p_tuesday_hours =>0.00, p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,
237: p_saturday_hours =>0.00,p_sunday_hours =>0.00,
238: x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
238: x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
239: END IF;
240:
241: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS AND l_x_sch_copy_done <> TRUE ) THEN
242: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
243: x_sch_record_tab, l_x_return_status,x_msg_count,x_msg_data);
244: END IF;
245:
246: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
243: x_sch_record_tab, l_x_return_status,x_msg_count,x_msg_data);
244: END IF;
245:
246: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
247: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
248: x_msg_data);
249: END IF;
250: l_x_sch_copy_done := TRUE;
251: END IF;
262: END IF;
263:
264: IF l_x_sch_copy_done = FALSE THEN
265: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
266: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
267: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
268: END IF;
269: END IF;
270: x_return_status := l_x_return_status;
517: NULL;
518: END IF;
519:
520: -- Appending records in l_out_schedule_rec
521: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_curr_schedule_rec,1,1,l_out_schedule_rec,
522: l_x_return_status,x_msg_count,x_msg_data);
523:
524: IF (l_st_dt_done = TRUE ) AND (l_end_dt_done = TRUE) THEN
525: EXIT;
535: -- If the start date is falling before the start date of the schedule then its work patern will be 0
536: IF (p_start_date < l_out_schedule_rec(l_I).start_date) THEN
537:
538: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
539: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,
540: 1,
541: 1,
542: l_temp_schedule_rec,
543: l_x_return_status,
556: l_x_return_status,
557: x_msg_count,
558: x_msg_data);
559: ELSE
560: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
561: p_start_date =>p_start_date,
562: p_end_date =>l_out_schedule_rec(l_I).start_date -1,
563: p_monday_hours =>0.00,
564: p_tuesday_hours =>0.00,
574: END IF; --l_calendar_id is not null
575: END IF;
576:
577: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
578: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,
579: 1,
580: 1,
581: x_sch_record_tab,
582: l_x_return_status,
585: END IF;
586:
587:
588: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
589: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,
590: l_out_schedule_rec.first,
591: l_out_schedule_rec.last,
592: x_sch_record_tab,
593: l_x_return_status,
602: -- If the end date is falling after the end date of the schedule then its work patern will be 0
603: IF (p_end_date > l_out_schedule_rec(l_J).end_date) THEN
604:
605: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
606: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,
607: 1,
608: 1,
609: l_temp_schedule_rec,
610: l_x_return_status,
625: x_msg_data);
626: /*End of code for Bug 2335580 */
627: ELSE
628:
629: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
630: p_start_date =>l_out_schedule_rec(l_J).end_date + 1,
631: p_end_date => p_end_date,
632: p_monday_hours =>0.00,
633: p_tuesday_hours =>0.00,
643: END IF;
644:
645:
646: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS AND l_x_sch_copy_done <> TRUE ) THEN
647: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,
648: l_out_schedule_rec.first,
649: l_out_schedule_rec.last,
650: x_sch_record_tab,
651: l_x_return_status,
653: x_msg_data);
654: END IF;
655:
656: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
657: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,
658: l_temp_schedule_rec.FIRST , --1,Modified for bug 4504473/4375409
659: l_temp_schedule_rec.Last , --1,Modified for bug 4504473/4375409
660: x_sch_record_tab,
661: l_x_return_status,
680: END IF;
681:
682: IF l_x_sch_copy_done = FALSE THEN
683: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
684: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,
685: l_out_schedule_rec.first,
686: l_out_schedule_rec.last,
687: x_sch_record_tab,
688: l_x_return_status,
891: l_curr_schedule_rec(1).Sunday_hours := l_Sunday_hours_tbl(i);
892:
893: -- appending the record
894: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
895: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_curr_schedule_rec,1,1,l_out_schedule_rec,l_x_return_status,
896: x_msg_count,x_msg_data);
897: END IF;
898:
899: END LOOP;
900:
901: IF l_x_sch_copy_done = FALSE THEN
902:
903: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
904: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
905: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
906: END IF;
907: END IF;
908:
1029: l_end_dt_done := FALSE;
1030: l_x_sch_copy_done := FALSE;
1031: l_t_first_record := FALSE;
1032: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1033: PA_SCHEDULE_UTILS.log_message(1,'first status ... '||l_x_return_status);
1034:
1035:
1036: IF ( p_source_type = 'PA_PROJECT_PARTY_ID') THEN
1037: BEGIN
1049: p_resource_id => NULL,
1050: x_jtf_resource_id => l_t_resource_id,
1051: x_return_status => l_x_return_status,
1052: x_error_message_code => x_msg_data );
1053: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1054: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id');
1055:
1056: END IF;
1057: ELSIF ( p_source_type = 'PA_RESOURCE_ID') THEN
1050: x_jtf_resource_id => l_t_resource_id,
1051: x_return_status => l_x_return_status,
1052: x_error_message_code => x_msg_data );
1053: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1054: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id');
1055:
1056: END IF;
1057: ELSIF ( p_source_type = 'PA_RESOURCE_ID') THEN
1058: l_resource_id := p_source_id;
1062: p_resource_id => p_source_id,
1063: x_jtf_resource_id => l_t_resource_id,
1064: x_return_status => l_x_return_status,
1065: x_error_message_code => x_msg_data );
1066: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1067: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id2');
1068: END IF;
1069: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1070: END IF;
1063: x_jtf_resource_id => l_t_resource_id,
1064: x_return_status => l_x_return_status,
1065: x_error_message_code => x_msg_data );
1066: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1067: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id2');
1068: END IF;
1069: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1070: END IF;
1071:
1065: x_error_message_code => x_msg_data );
1066: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1067: PA_SCHEDULE_UTILS.log_message(1,'get_crm_res_id2');
1068: END IF;
1069: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1070: END IF;
1071:
1072: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1073: IF ( l_x_return_status <> fnd_api.g_ret_sts_success ) THEN
1068: END IF;
1069: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1070: END IF;
1071:
1072: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1073: IF ( l_x_return_status <> fnd_api.g_ret_sts_success ) THEN
1074: RAISE l_invalid_source_id;
1075: END IF;
1076: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1072: PA_SCHEDULE_UTILS.log_message(1,'second status ... '||l_x_return_status);
1073: IF ( l_x_return_status <> fnd_api.g_ret_sts_success ) THEN
1074: RAISE l_invalid_source_id;
1075: END IF;
1076: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1077:
1078: -- 1965289: Work around for CRM bug (cannot create future dated resources).
1079: -- If the jtf_resource_id is null, then we need to use the default
1080: -- calendar instead of going to the jtf_cal_resource_assign table.
1119: p_organization_id => l_resource_organization_id,
1120: x_default_ou => l_resource_ou_id,
1121: x_default_cal_id => l_calendar_id,
1122: x_return_status => l_x_return_status);
1123: PA_SCHEDULE_UTILS.log_message(1,'l_resource_organization_id: '||l_resource_organization_id);
1124: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1125: PA_SCHEDULE_UTILS.log_message(1,'get_org_defaults');
1126: END IF;
1127:
1120: x_default_ou => l_resource_ou_id,
1121: x_default_cal_id => l_calendar_id,
1122: x_return_status => l_x_return_status);
1123: PA_SCHEDULE_UTILS.log_message(1,'l_resource_organization_id: '||l_resource_organization_id);
1124: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1125: PA_SCHEDULE_UTILS.log_message(1,'get_org_defaults');
1126: END IF;
1127:
1128: if (l_calendar_id is null) then
1121: x_default_cal_id => l_calendar_id,
1122: x_return_status => l_x_return_status);
1123: PA_SCHEDULE_UTILS.log_message(1,'l_resource_organization_id: '||l_resource_organization_id);
1124: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1125: PA_SCHEDULE_UTILS.log_message(1,'get_org_defaults');
1126: END IF;
1127:
1128: if (l_calendar_id is null) then
1129: l_calendar_id := fnd_profile.value_specific('PA_PRM_DEFAULT_CALENDAR');
1155: l_cur_schedule_rec,
1156: l_x_return_status,
1157: x_msg_count,
1158: x_msg_data);
1159: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1160: PA_SCHEDULE_UTILS.log_message(1,'get_calendar_schedule');
1161: END IF;
1162:
1163: IF (p_start_date is not null) then
1156: l_x_return_status,
1157: x_msg_count,
1158: x_msg_data);
1159: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1160: PA_SCHEDULE_UTILS.log_message(1,'get_calendar_schedule');
1161: END IF;
1162:
1163: IF (p_start_date is not null) then
1164: l_tc_start_date := p_start_date;
1164: l_tc_start_date := p_start_date;
1165: END IF;
1166:
1167: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1168: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
1169: l_cur_schedule_rec,
1170: l_cur_schedule_rec.first,
1171: l_cur_schedule_rec.last,
1172: l_out_schedule_rec,
1172: l_out_schedule_rec,
1173: l_x_return_status,
1174: x_msg_count,
1175: x_msg_data);
1176: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1177: PA_SCHEDULE_UTILS.log_message(1,'add_schedule_rec_tab');
1178: END IF;
1179:
1180: ---- Start: Copied from below (same processing for work around)
1173: l_x_return_status,
1174: x_msg_count,
1175: x_msg_data);
1176: PA_SCHEDULE_UTILS.log_message(1,'status ... '||l_x_return_status);
1177: PA_SCHEDULE_UTILS.log_message(1,'add_schedule_rec_tab');
1178: END IF;
1179:
1180: ---- Start: Copied from below (same processing for work around)
1181: IF ( l_out_schedule_rec.count > 0 ) THEN
1184:
1185: IF (l_tc_start_date < l_out_schedule_rec(l_I).start_date) THEN
1186:
1187: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1188: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1189: x_msg_data);
1190: END IF;
1191:
1192: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1189: x_msg_data);
1190: END IF;
1191:
1192: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1193: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,p_start_date =>l_tc_start_date,
1194: p_end_date =>l_out_schedule_rec(l_I).start_date -1 ,p_monday_hours =>0.00,p_tuesday_hours =>0.00,
1195: p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
1196: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1197: END IF;
1196: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1197: END IF;
1198:
1199: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1200: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1201: x_msg_data);
1202: END IF;
1203:
1204: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1201: x_msg_data);
1202: END IF;
1203:
1204: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1205: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1206: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1207: END IF;
1208:
1209: l_x_sch_copy_done := TRUE;
1206: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1207: END IF;
1208:
1209: l_x_sch_copy_done := TRUE;
1210: --PA_SCHEDULE_UTILS.log_message(2,'X1 :',x_sch_record_tab);
1211: END IF;
1212:
1213: IF (l_tc_end_date > l_out_schedule_rec(l_J).end_date) THEN
1214:
1212:
1213: IF (l_tc_end_date > l_out_schedule_rec(l_J).end_date) THEN
1214:
1215: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1216: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1217: x_msg_data);
1218: END IF;
1219:
1220: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1217: x_msg_data);
1218: END IF;
1219:
1220: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1221: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
1222: p_start_date =>l_out_schedule_rec(l_J).end_date + 1 , p_end_date => l_tc_end_date,p_monday_hours =>0.00,
1223: p_tuesday_hours =>0.00, p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,
1224: p_saturday_hours =>0.00,p_sunday_hours =>0.00,
1225: x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1226: END IF;
1227:
1228: IF ( l_x_sch_copy_done = FALSE ) THEN
1229: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1230: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1231: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1232: END IF;
1233:
1234: END IF;
1233:
1234: END IF;
1235:
1236: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1237: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1238: x_msg_data);
1239: END IF;
1240:
1241: l_x_sch_copy_done := TRUE;
1238: x_msg_data);
1239: END IF;
1240:
1241: l_x_sch_copy_done := TRUE;
1242: --PA_SCHEDULE_UTILS.log_message(2,'X2 :',x_sch_record_tab);
1243: END IF;
1244:
1245: IF l_x_sch_copy_done = FALSE THEN
1246:
1244:
1245: IF l_x_sch_copy_done = FALSE THEN
1246:
1247: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1248: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1249: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1250: END IF;
1251: END IF;
1252: ELSE
1279: AND jtf_cal_resource_assign.resource_type_code = 'RS_EMPLOYEE'
1280: AND jtf_cal_resource_assign.primary_calendar_flag = 'Y';
1281: END IF;
1282:
1283: PA_SCHEDULE_UTILS.log_message(1,'Start date ... '||to_char(l_temp_start_date)||to_char(p_start_date));
1284: PA_SCHEDULE_UTILS.log_message(1,'end date ... '||to_char(l_temp_end_date)||to_char(p_end_date));
1285: IF (p_start_date IS NULL ) THEN
1286: l_tc_start_date := l_temp_start_date;
1287: ELSE
1280: AND jtf_cal_resource_assign.primary_calendar_flag = 'Y';
1281: END IF;
1282:
1283: PA_SCHEDULE_UTILS.log_message(1,'Start date ... '||to_char(l_temp_start_date)||to_char(p_start_date));
1284: PA_SCHEDULE_UTILS.log_message(1,'end date ... '||to_char(l_temp_end_date)||to_char(p_end_date));
1285: IF (p_start_date IS NULL ) THEN
1286: l_tc_start_date := l_temp_start_date;
1287: ELSE
1288: l_tc_start_date := p_start_date;
1294: l_tc_end_date := p_end_date;
1295: END IF;
1296:
1297:
1298: PA_SCHEDULE_UTILS.log_message(1,'Start of the get_resource_schedule API ... ');
1299:
1300:
1301: FOR v_c1 IN C1 LOOP
1302:
1299:
1300:
1301: FOR v_c1 IN C1 LOOP
1302:
1303: PA_SCHEDULE_UTILS.log_message(1,'inside cursor ... ');
1304:
1305: PA_SCHEDULE_UTILS.log_message(2,'REC : '||to_char(v_c1.calendar_id)|| ' '||
1306: to_char(v_c1.start_date)||' '||
1307: to_char(v_c1.end_date));
1301: FOR v_c1 IN C1 LOOP
1302:
1303: PA_SCHEDULE_UTILS.log_message(1,'inside cursor ... ');
1304:
1305: PA_SCHEDULE_UTILS.log_message(2,'REC : '||to_char(v_c1.calendar_id)|| ' '||
1306: to_char(v_c1.start_date)||' '||
1307: to_char(v_c1.end_date));
1308:
1309:
1310: IF( l_t_first_record) THEN
1311: l_t_first_record := FALSE;
1312: ELSIF( v_c1.start_date <> (l_last_end_date + 1) ) THEN
1313: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1314: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_cur_schedule_rec,1,1,
1315: l_temp_schedule_rec,l_x_return_status,x_msg_count,x_msg_data);
1316: END IF;
1317:
1318: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1315: l_temp_schedule_rec,l_x_return_status,x_msg_count,x_msg_data);
1316: END IF;
1317:
1318: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1319: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
1320: p_start_date =>l_last_end_date + 1, p_end_date =>v_c1.start_date -1 ,
1321: p_monday_hours =>0.00,p_tuesday_hours =>0.00,p_wednesday_hours =>0.00,
1322: p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
1323: p_sunday_hours =>0.00,
1323: p_sunday_hours =>0.00,
1324: x_return_status => l_x_return_status,x_msg_count =>
1325: x_msg_count,x_msg_data =>x_msg_data);
1326: END IF;
1327: --PA_SCHEDULE_UTILS.log_message(2,'TEMP :',l_temp_schedule_rec);
1328:
1329: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1330: -- Appending the records
1331: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,
1327: --PA_SCHEDULE_UTILS.log_message(2,'TEMP :',l_temp_schedule_rec);
1328:
1329: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1330: -- Appending the records
1331: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,
1332: l_out_schedule_rec,l_x_return_status,x_msg_count,x_msg_data);
1333: END IF;
1334: END IF;
1335:
1334: END IF;
1335:
1336: l_cur_schedule_rec.delete;
1337:
1338: PA_SCHEDULE_UTILS.log_message(2,'in deleting CUR '||l_x_return_status);
1339: IF ( v_c1.end_date > l_tc_end_date ) THEN
1340: l_t_end_date := l_tc_end_date;
1341: ELSE
1342: l_t_end_date := v_c1.end_date;
1345: IF ( v_c1.start_date < l_tc_start_date ) THEN
1346: l_t_start_date := l_tc_start_date;
1347: ELSE
1348: l_t_start_date := v_c1.start_date;
1349: PA_SCHEDULE_UTILS.log_message(2,'before get resource '||to_char(l_t_start_date,'dd-mon-yyyy'));
1350: END IF;
1351:
1352: PA_SCHEDULE_UTILS.log_message(2,'before get resource '||l_x_return_status);
1353: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1348: l_t_start_date := v_c1.start_date;
1349: PA_SCHEDULE_UTILS.log_message(2,'before get resource '||to_char(l_t_start_date,'dd-mon-yyyy'));
1350: END IF;
1351:
1352: PA_SCHEDULE_UTILS.log_message(2,'before get resource '||l_x_return_status);
1353: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1354: -- Calling the get calendar schedule procedure which will bring the schedule for the specified calendar id
1355: get_calendar_schedule( v_c1.calendar_id,
1356: l_t_start_date,
1358: l_cur_schedule_rec,
1359: l_x_return_status,
1360: x_msg_count,
1361: x_msg_data);
1362: PA_SCHEDULE_UTILS.log_message(2,'inside get calendar CUR :',l_cur_schedule_rec);
1363: END IF;
1364: PA_SCHEDULE_UTILS.log_message(2,'after get resource CUR :',l_cur_schedule_rec);
1365:
1366: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1360: x_msg_count,
1361: x_msg_data);
1362: PA_SCHEDULE_UTILS.log_message(2,'inside get calendar CUR :',l_cur_schedule_rec);
1363: END IF;
1364: PA_SCHEDULE_UTILS.log_message(2,'after get resource CUR :',l_cur_schedule_rec);
1365:
1366: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1367: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_cur_schedule_rec,l_cur_schedule_rec.first,
1368: l_cur_schedule_rec.last,l_out_schedule_rec,l_x_return_status,x_msg_count,
1363: END IF;
1364: PA_SCHEDULE_UTILS.log_message(2,'after get resource CUR :',l_cur_schedule_rec);
1365:
1366: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1367: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_cur_schedule_rec,l_cur_schedule_rec.first,
1368: l_cur_schedule_rec.last,l_out_schedule_rec,l_x_return_status,x_msg_count,
1369: x_msg_data);
1370: END IF;
1371:
1370: END IF;
1371:
1372: l_last_end_date := v_c1.end_date;
1373:
1374: PA_SCHEDULE_UTILS.log_message(2,'OUT :',l_out_schedule_rec);
1375:
1376: END LOOP;
1377:
1378: PA_SCHEDULE_UTILS.log_message(2,'OUTSIDE loop :');
1374: PA_SCHEDULE_UTILS.log_message(2,'OUT :',l_out_schedule_rec);
1375:
1376: END LOOP;
1377:
1378: PA_SCHEDULE_UTILS.log_message(2,'OUTSIDE loop :');
1379:
1380: IF ( l_out_schedule_rec.count > 0 ) THEN
1381: l_I := l_out_schedule_rec.first;
1382: l_J := l_out_schedule_rec.Last;
1383:
1384: IF (l_tc_start_date < l_out_schedule_rec(l_I).start_date) THEN
1385:
1386: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1387: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1388: x_msg_data);
1389: END IF;
1390:
1391: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1388: x_msg_data);
1389: END IF;
1390:
1391: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1392: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,p_start_date =>l_tc_start_date,
1393: p_end_date =>l_out_schedule_rec(l_I).start_date -1 ,p_monday_hours =>0.00,p_tuesday_hours =>0.00,
1394: p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,p_saturday_hours =>0.00,
1395: p_sunday_hours =>0.00,x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1396: END IF;
1395: p_sunday_hours =>0.00,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_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1399: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1400: x_msg_data);
1401: END IF;
1402:
1403: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1400: x_msg_data);
1401: END IF;
1402:
1403: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1404: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1405: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1406: END IF;
1407:
1408: l_x_sch_copy_done := TRUE;
1405: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1406: END IF;
1407:
1408: l_x_sch_copy_done := TRUE;
1409: --PA_SCHEDULE_UTILS.log_message(2,'X1 :',x_sch_record_tab);
1410: END IF;
1411:
1412: IF (l_tc_end_date > l_out_schedule_rec(l_J).end_date) THEN
1413:
1411:
1412: IF (l_tc_end_date > l_out_schedule_rec(l_J).end_date) THEN
1413:
1414: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1415: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,1,1,l_temp_schedule_rec,l_x_return_status,x_msg_count,
1416: x_msg_data);
1417: END IF;
1418:
1419: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1416: x_msg_data);
1417: END IF;
1418:
1419: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1420: PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab => l_temp_schedule_rec,
1421: p_start_date =>l_out_schedule_rec(l_J).end_date + 1 , p_end_date => l_tc_end_date,p_monday_hours =>0.00,
1422: p_tuesday_hours =>0.00, p_wednesday_hours =>0.00,p_thursday_hours =>0.00,p_friday_hours =>0.00,
1423: p_saturday_hours =>0.00,p_sunday_hours =>0.00,
1424: x_return_status => l_x_return_status,x_msg_count => x_msg_count,x_msg_data =>x_msg_data);
1425: END IF;
1426:
1427: IF ( l_x_sch_copy_done = FALSE ) THEN
1428: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1429: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1430: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1431: END IF;
1432:
1433: END IF;
1432:
1433: END IF;
1434:
1435: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1436: PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_schedule_rec,1,1,x_sch_record_tab,l_x_return_status,x_msg_count,
1437: x_msg_data);
1438: END IF;
1439:
1440: l_x_sch_copy_done := TRUE;
1437: x_msg_data);
1438: END IF;
1439:
1440: l_x_sch_copy_done := TRUE;
1441: --PA_SCHEDULE_UTILS.log_message(2,'X2 :',x_sch_record_tab);
1442: END IF;
1443:
1444: IF l_x_sch_copy_done = FALSE THEN
1445:
1443:
1444: IF l_x_sch_copy_done = FALSE THEN
1445:
1446: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1447: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_schedule_rec,l_out_schedule_rec.first,l_out_schedule_rec.last,
1448: x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1449: END IF;
1450: END IF;
1451: ELSE
1462: END IF;
1463:
1464: -- 2196924: Fix table by setting 0 for all dates with no HR assignment.
1465: IF (NVL(x_sch_record_tab.count,0) <> 0) THEN
1466: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab.count<>0');
1467: for i in x_sch_record_tab.first .. x_sch_record_tab.last loop
1468: 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);
1469: end loop;
1470:
1464: -- 2196924: Fix table by setting 0 for all dates with no HR assignment.
1465: IF (NVL(x_sch_record_tab.count,0) <> 0) THEN
1466: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab.count<>0');
1467: for i in x_sch_record_tab.first .. x_sch_record_tab.last loop
1468: 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);
1469: end loop;
1470:
1471: l_cap_first_start_date := x_sch_record_tab(x_sch_record_tab.first).start_date;
1472: l_cap_last_end_date := x_sch_record_tab(x_sch_record_tab.last).end_date;
1470:
1471: l_cap_first_start_date := x_sch_record_tab(x_sch_record_tab.first).start_date;
1472: l_cap_last_end_date := x_sch_record_tab(x_sch_record_tab.last).end_date;
1473:
1474: PA_SCHEDULE_UTILS.log_message(1,'l_cap_first_start_date: ' || l_cap_first_start_date);
1475: PA_SCHEDULE_UTILS.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1476:
1477: SELECT rou.resource_effective_start_date,
1478: NVL(rou.resource_effective_end_date,SYSDATE)
1471: l_cap_first_start_date := x_sch_record_tab(x_sch_record_tab.first).start_date;
1472: l_cap_last_end_date := x_sch_record_tab(x_sch_record_tab.last).end_date;
1473:
1474: PA_SCHEDULE_UTILS.log_message(1,'l_cap_first_start_date: ' || l_cap_first_start_date);
1475: PA_SCHEDULE_UTILS.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1476:
1477: SELECT rou.resource_effective_start_date,
1478: NVL(rou.resource_effective_end_date,SYSDATE)
1479: BULK COLLECT INTO
1485: AND rou.resource_effective_start_date <= l_cap_last_end_date
1486: ORDER BY rou.resource_effective_start_date;
1487:
1488: if (NVL(l_ResStartDateTab.count,0) = 0) THEN
1489: PA_SCHEDULE_UTILS.log_message(1,'Set all hours to 0 in l_sch_record_tab since no res denorm records exist. ');
1490: -- Set all hours to 0 in l_sch_record_tab since no res denorm records
1491: -- exist.
1492: l_sch_record_tab := x_sch_record_tab;
1493:
1503: else
1504: l_res_first_start_date := l_resstartdatetab(l_resstartdatetab.first);
1505: l_res_last_end_date := l_resenddatetab(l_resenddatetab.last);
1506:
1507: PA_SCHEDULE_UTILS.log_message(1,'l_res_first_start_date: ' || l_res_first_start_date);
1508: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1509:
1510: PA_SCHEDULE_UTILS.log_message(1,'Resource denorm records do exist.');
1511:
1504: l_res_first_start_date := l_resstartdatetab(l_resstartdatetab.first);
1505: l_res_last_end_date := l_resenddatetab(l_resenddatetab.last);
1506:
1507: PA_SCHEDULE_UTILS.log_message(1,'l_res_first_start_date: ' || l_res_first_start_date);
1508: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1509:
1510: PA_SCHEDULE_UTILS.log_message(1,'Resource denorm records do exist.');
1511:
1512: -- Check if there are any holes in resource denorm records.
1506:
1507: PA_SCHEDULE_UTILS.log_message(1,'l_res_first_start_date: ' || l_res_first_start_date);
1508: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1509:
1510: PA_SCHEDULE_UTILS.log_message(1,'Resource denorm records do exist.');
1511:
1512: -- Check if there are any holes in resource denorm records.
1513: -- If so, then adjust x_sch_record_tab with that change.
1514:
1527: l_resstartdatetab(l_resstartdatetab.last+1) := l_cap_last_end_date+1;
1528: l_resenddatetab(l_resenddatetab.last+1) := l_cap_last_end_date +10;
1529: END IF;
1530:
1531: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1532:
1533: l_last_processed_cap_index := 0;
1534: l_cap_tab_index := 1;
1535:
1532:
1533: l_last_processed_cap_index := 0;
1534: l_cap_tab_index := 1;
1535:
1536: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1537: PA_schedule_utils.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1538:
1539: l_last_processed_cap_index := x_sch_record_tab.first-1;
1540: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1533: l_last_processed_cap_index := 0;
1534: l_cap_tab_index := 1;
1535:
1536: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1537: PA_schedule_utils.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1538:
1539: l_last_processed_cap_index := x_sch_record_tab.first-1;
1540: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1541:
1536: PA_SCHEDULE_UTILS.log_message(1,'l_res_last_end_date: ' || l_res_last_end_date);
1537: PA_schedule_utils.log_message(1,'l_cap_last_end_date: ' || l_cap_last_end_date);
1538:
1539: l_last_processed_cap_index := x_sch_record_tab.first-1;
1540: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1541:
1542: -- Find holes in l_ResStartDateTab, l_ResEndDateTab
1543: FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last LOOP
1544: pa_schedule_utils.log_message(1,'i: ' || i);
1540: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1541:
1542: -- Find holes in l_ResStartDateTab, l_ResEndDateTab
1543: FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last LOOP
1544: pa_schedule_utils.log_message(1,'i: ' || i);
1545: PA_SCHEDULE_UTILS.log_message(1,'l_ResStartDateTab(i): ' || l_ResStartDateTab(i));
1546: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1547: if (l_ResStartDateTab(i) > l_prev_res_end_date+1) then
1548: l_hole_start_date := l_prev_res_end_date+1;
1541:
1542: -- Find holes in l_ResStartDateTab, l_ResEndDateTab
1543: FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last LOOP
1544: pa_schedule_utils.log_message(1,'i: ' || i);
1545: PA_SCHEDULE_UTILS.log_message(1,'l_ResStartDateTab(i): ' || l_ResStartDateTab(i));
1546: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1547: if (l_ResStartDateTab(i) > l_prev_res_end_date+1) then
1548: l_hole_start_date := l_prev_res_end_date+1;
1549: l_hole_end_date := l_ResStartDateTab(i)-1;
1542: -- Find holes in l_ResStartDateTab, l_ResEndDateTab
1543: FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last LOOP
1544: pa_schedule_utils.log_message(1,'i: ' || i);
1545: PA_SCHEDULE_UTILS.log_message(1,'l_ResStartDateTab(i): ' || l_ResStartDateTab(i));
1546: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1547: if (l_ResStartDateTab(i) > l_prev_res_end_date+1) then
1548: l_hole_start_date := l_prev_res_end_date+1;
1549: l_hole_end_date := l_ResStartDateTab(i)-1;
1550: PA_SCHEDULE_UTILS.log_message(1,'Hole found: ' || l_hole_start_date || ' ' || l_hole_end_date);
1546: PA_SCHEDULE_UTILS.log_message(1,'l_prev_res_end_date: ' || l_prev_res_end_date);
1547: if (l_ResStartDateTab(i) > l_prev_res_end_date+1) then
1548: l_hole_start_date := l_prev_res_end_date+1;
1549: l_hole_end_date := l_ResStartDateTab(i)-1;
1550: PA_SCHEDULE_UTILS.log_message(1,'Hole found: ' || l_hole_start_date || ' ' || l_hole_end_date);
1551:
1552: -- Adjust x_sch_record_tab with decrease in availability.
1553: -- This is done by copying / modifying records up to hole end date.
1554: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1550: PA_SCHEDULE_UTILS.log_message(1,'Hole found: ' || l_hole_start_date || ' ' || l_hole_end_date);
1551:
1552: -- Adjust x_sch_record_tab with decrease in availability.
1553: -- This is done by copying / modifying records up to hole end date.
1554: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1555: PA_SCHEDULE_UTILS.log_message(1,'Start loop through capacity records.');
1556:
1557: <
1558: for j in (l_last_processed_cap_index + 1) .. x_sch_record_tab.last LOOP
1551:
1552: -- Adjust x_sch_record_tab with decrease in availability.
1553: -- This is done by copying / modifying records up to hole end date.
1554: PA_SCHEDULE_UTILS.log_message(1,'l_last_processed_cap_index: ' || l_last_processed_cap_index);
1555: PA_SCHEDULE_UTILS.log_message(1,'Start loop through capacity records.');
1556:
1557: <
1558: for j in (l_last_processed_cap_index + 1) .. x_sch_record_tab.last LOOP
1559:
1557: <
1558: for j in (l_last_processed_cap_index + 1) .. x_sch_record_tab.last LOOP
1559:
1560: l_j_index := j;
1561: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1562: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1563: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1564: PA_SCHEDULE_UTILS.log_message(1,'l_hole_end_date: ' || l_hole_end_date);
1565: l_reprocess_flag := 'N';
1558: for j in (l_last_processed_cap_index + 1) .. x_sch_record_tab.last LOOP
1559:
1560: l_j_index := j;
1561: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1562: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1563: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1564: PA_SCHEDULE_UTILS.log_message(1,'l_hole_end_date: ' || l_hole_end_date);
1565: l_reprocess_flag := 'N';
1566:
1559:
1560: l_j_index := j;
1561: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1562: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1563: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1564: PA_SCHEDULE_UTILS.log_message(1,'l_hole_end_date: ' || l_hole_end_date);
1565: l_reprocess_flag := 'N';
1566:
1567:
1560: l_j_index := j;
1561: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1562: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1563: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1564: PA_SCHEDULE_UTILS.log_message(1,'l_hole_end_date: ' || l_hole_end_date);
1565: l_reprocess_flag := 'N';
1566:
1567:
1568: IF (x_sch_record_tab(j).start_date = l_hole_end_date + 1) then
1565: l_reprocess_flag := 'N';
1566:
1567:
1568: IF (x_sch_record_tab(j).start_date = l_hole_end_date + 1) then
1569: PA_SCHEDULE_UTILS.log_message(1,'Finished looping through all capacity records for current hole. Find next hole.');
1570: l_reprocess_flag := 'Y'; -- Added after the fact
1571: EXIT l_cap_record_loop;
1572: -- Reprocess if the capacity record starts after the hole
1573: -- end date because it may overlap the next hole.
1571: EXIT l_cap_record_loop;
1572: -- Reprocess if the capacity record starts after the hole
1573: -- end date because it may overlap the next hole.
1574: ELSIF (x_sch_record_tab(j).start_date > l_hole_end_date + 1) then
1575: PA_SCHEDULE_UTILS.log_message(1,'Finished looping through all capacity records for current hole. Find next hole.');
1576: l_reprocess_flag := 'Y';
1577: EXIT l_cap_record_loop;
1578: end if;
1579:
1579:
1580: -- If Capacity record is before hole.
1581: if (x_sch_record_tab(j).end_date < l_hole_start_date) then
1582:
1583: PA_SCHEDULE_UTILS.log_message(1,'Capacity record before hole');
1584: -- Keep record as is.
1585: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1586: l_cap_tab_index := l_cap_tab_index + 1;
1587: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1583: PA_SCHEDULE_UTILS.log_message(1,'Capacity record before hole');
1584: -- Keep record as is.
1585: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1586: l_cap_tab_index := l_cap_tab_index + 1;
1587: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1588: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1589: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1590: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1591:
1584: -- Keep record as is.
1585: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1586: l_cap_tab_index := l_cap_tab_index + 1;
1587: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1588: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1589: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1590: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1591:
1592: -- If capacity record overlaps start date of hole.
1585: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1586: l_cap_tab_index := l_cap_tab_index + 1;
1587: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1588: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1589: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1590: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1591:
1592: -- If capacity record overlaps start date of hole.
1593: elsif (x_sch_record_tab(j).start_date < l_hole_start_date AND
1586: l_cap_tab_index := l_cap_tab_index + 1;
1587: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1588: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1589: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1590: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1591:
1592: -- If capacity record overlaps start date of hole.
1593: elsif (x_sch_record_tab(j).start_date < l_hole_start_date AND
1594: x_sch_record_tab(j).end_date <= l_hole_end_date) then
1592: -- If capacity record overlaps start date of hole.
1593: elsif (x_sch_record_tab(j).start_date < l_hole_start_date AND
1594: x_sch_record_tab(j).end_date <= l_hole_end_date) then
1595:
1596: PA_SCHEDULE_UTILS.log_message(1,'capacity record overlaps start date of hole.');
1597: -- Keep record as is but end date it
1598: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1599: l_sch_record_tab(l_cap_tab_index).end_date :=
1600: l_hole_start_date - 1;
1617: -- If capacity record overlaps start date and end date of hole.
1618: elsif (x_sch_record_tab(j).start_date < l_hole_start_date AND
1619: x_sch_record_tab(j).end_date > l_hole_end_date) then
1620:
1621: PA_SCHEDULE_UTILS.log_message(1,'capacity record overlaps start date and end date of hole.');
1622:
1623: -- Copy record over and end date it
1624: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1625: l_sch_record_tab(l_cap_tab_index).end_date :=
1640: l_cap_tab_index := l_cap_tab_index + 1;
1641:
1642: -- Modify x_sch_record_tab(j) so that the record will
1643: -- be reprocessed.
1644: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1645: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1646: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1647:
1648: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1641:
1642: -- Modify x_sch_record_tab(j) so that the record will
1643: -- be reprocessed.
1644: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1645: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1646: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1647:
1648: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1649: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1642: -- Modify x_sch_record_tab(j) so that the record will
1643: -- be reprocessed.
1644: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1645: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1646: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1647:
1648: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1649: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1650: l_reprocess_flag := 'Y'; -- Mark for reprocessing.
1645: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1646: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1647:
1648: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1649: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1650: l_reprocess_flag := 'Y'; -- Mark for reprocessing.
1651:
1652: EXIT l_cap_record_loop;
1653:
1654: -- If capacity record within hole.
1655: elsif (x_sch_record_tab(j).start_date <= l_hole_end_date AND
1656: x_sch_record_tab(j).end_date <= l_hole_end_date) THEN
1657:
1658: PA_SCHEDULE_UTILS.log_message(1,'capacity record within hole.');
1659:
1660: -- Create record for hole.
1661: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1662: l_sch_record_tab(l_cap_tab_index).monday_hours := 0;
1671: -- If capacity record overlaps end date of hole.
1672: elsif (x_sch_record_tab(j).start_date <= l_hole_end_date AND
1673: x_sch_record_tab(j).end_date > l_hole_end_date) THEN
1674:
1675: PA_SCHEDULE_UTILS.log_message(1,'capacity record overlaps end date of hole.');
1676:
1677: -- Create record for hole.
1678: PA_SCHEDULE_UTILS.log_message(1,'l_cap_tab_index: ' || l_cap_tab_index);
1679: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1674:
1675: PA_SCHEDULE_UTILS.log_message(1,'capacity record overlaps end date of hole.');
1676:
1677: -- Create record for hole.
1678: PA_SCHEDULE_UTILS.log_message(1,'l_cap_tab_index: ' || l_cap_tab_index);
1679: l_sch_record_tab(l_cap_tab_index) := x_sch_record_tab(j);
1680: l_sch_record_tab(l_cap_tab_index).end_date := l_hole_end_date;
1681: l_sch_record_tab(l_cap_tab_index).monday_hours := 0;
1682: l_sch_record_tab(l_cap_tab_index).tuesday_hours := 0;
1688: l_cap_tab_index := l_cap_tab_index + 1;
1689:
1690: -- Modify x_sch_record_tab(j) so that the record will
1691: -- be reprocessed.
1692: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1693: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1694: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1695: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1696:
1689:
1690: -- Modify x_sch_record_tab(j) so that the record will
1691: -- be reprocessed.
1692: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1693: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1694: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1695: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1696:
1697: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1690: -- Modify x_sch_record_tab(j) so that the record will
1691: -- be reprocessed.
1692: PA_SCHEDULE_UTILS.log_message(1,'j: ' || j);
1693: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1694: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1695: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1696:
1697: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1698: l_reprocess_flag := 'Y'; -- Mark for reprocessing.
1693: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).start_date: ' || x_sch_record_tab(j).start_date);
1694: PA_SCHEDULE_UTILS.log_message(1,'x_sch_record_tab(j).end_date: ' || x_sch_record_tab(j).end_date);
1695: x_sch_record_tab(j).start_date := l_hole_end_date + 1;
1696:
1697: PA_SCHEDULE_UTILS.log_message(1,'Mark for reprocessing.');
1698: l_reprocess_flag := 'Y'; -- Mark for reprocessing.
1699: EXIT l_cap_record_loop;
1700: END IF;
1701: -- Type of hole if statement
1702: END LOOP;
1703: -- for j in l_last_processed_cap_index + 1 ..
1704: -- modifying l_sch_record_tab with hole info.
1705:
1706: PA_SCHEDULE_UTILS.log_message(1,'JM: 1');
1707: PA_SCHEDULE_UTILS.log_message(1,'l_j_index: ' || l_j_index);
1708: IF (l_reprocess_flag = 'Y') THEN
1709: l_last_processed_cap_index := l_j_index-1;
1710: ELSE
1703: -- for j in l_last_processed_cap_index + 1 ..
1704: -- modifying l_sch_record_tab with hole info.
1705:
1706: PA_SCHEDULE_UTILS.log_message(1,'JM: 1');
1707: PA_SCHEDULE_UTILS.log_message(1,'l_j_index: ' || l_j_index);
1708: IF (l_reprocess_flag = 'Y') THEN
1709: l_last_processed_cap_index := l_j_index-1;
1710: ELSE
1711: l_last_processed_cap_index := l_j_index;
1715: -- l_ResStartDateTab(i) > l_prev_res_end_date+1
1716: -- When there is a hole
1717:
1718: l_prev_res_end_date := l_ResEndDateTab(i);
1719: PA_SCHEDULE_UTILS.log_message(1,'JM: 2');
1720:
1721: END LOOP;
1722: -- FOR i in l_ResStartDateTab.first .. l_ResStartDateTab.last
1723: -- Finding holes
1730:
1731: END IF;
1732: -- if (NVL(l_ResStartDateTab.count,0) = 0) THEN
1733: -- If there are any records in resource denorm.
1734: PA_SCHEDULE_UTILS.log_message(1,'JM: 3');
1735: END IF;
1736: -- IF (NVL(x_sch_record_tab.count,0) <> 0) THEN
1737: -- If there are any capacity records.
1738:
1739: x_sch_record_tab := l_sch_record_tab;
1740: -- End 2196924: Fix table by setting 0 for all dates with no HR assignment.
1741:
1742: x_return_status := l_x_return_status;
1743: PA_SCHEDULE_UTILS.log_message(1,'last status ... '||l_x_return_status);
1744: PA_SCHEDULE_UTILS.log_message(1,'leaving get_resource_schedule');
1745: EXCEPTION
1746: WHEN l_invalid_source_id THEN
1747: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1740: -- End 2196924: Fix table by setting 0 for all dates with no HR assignment.
1741:
1742: x_return_status := l_x_return_status;
1743: PA_SCHEDULE_UTILS.log_message(1,'last status ... '||l_x_return_status);
1744: PA_SCHEDULE_UTILS.log_message(1,'leaving get_resource_schedule');
1745: EXCEPTION
1746: WHEN l_invalid_source_id THEN
1747: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1748: x_msg_count := 1;
1788: ELSE
1789: l_I := p_chg_sch_record_tab.first;
1790: END IF;
1791:
1792: PA_SCHEDULE_UTILS.log_message(1,'Start of apply_schedule_change .... ');
1793: LOOP
1794:
1795: IF (p_chg_sch_record_tab(l_I).change_type_code = 'U') THEN
1796: PA_SCHEDULE_UTILS.log_message(1,'U .... ');
1792: PA_SCHEDULE_UTILS.log_message(1,'Start of apply_schedule_change .... ');
1793: LOOP
1794:
1795: IF (p_chg_sch_record_tab(l_I).change_type_code = 'U') THEN
1796: PA_SCHEDULE_UTILS.log_message(1,'U .... ');
1797: -- Calling the SCHEDULE UTILS api which will append the record if it marked for updation
1798: PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_upd_sch_record_tab,
1799: l_x_return_status,x_msg_count,x_msg_data);
1800: ELSIF (p_chg_sch_record_tab(l_I).change_type_code = 'I') THEN
1794:
1795: IF (p_chg_sch_record_tab(l_I).change_type_code = 'U') THEN
1796: PA_SCHEDULE_UTILS.log_message(1,'U .... ');
1797: -- Calling the SCHEDULE UTILS api which will append the record if it marked for updation
1798: PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_upd_sch_record_tab,
1799: l_x_return_status,x_msg_count,x_msg_data);
1800: ELSIF (p_chg_sch_record_tab(l_I).change_type_code = 'I') THEN
1801: PA_SCHEDULE_UTILS.log_message(1,'I .... ');
1802:
1797: -- Calling the SCHEDULE UTILS api which will append the record if it marked for updation
1798: PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_upd_sch_record_tab,
1799: l_x_return_status,x_msg_count,x_msg_data);
1800: ELSIF (p_chg_sch_record_tab(l_I).change_type_code = 'I') THEN
1801: PA_SCHEDULE_UTILS.log_message(1,'I .... ');
1802:
1803: -- Calling the SCHEDULE UTILS api which will append the record if it marked for insertion
1804: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1805: PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_ins_sch_record_tab,
1801: PA_SCHEDULE_UTILS.log_message(1,'I .... ');
1802:
1803: -- Calling the SCHEDULE UTILS api which will append the record if it marked for insertion
1804: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1805: PA_SCHEDULE_UTILS.Add_Schedule_Rec_Tab(p_chg_sch_record_tab,l_I,l_I,l_ins_sch_record_tab,
1806: l_x_return_status,x_msg_count,x_msg_data);
1807: END IF;
1808:
1809: END IF;
1833: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1834: 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);
1835: END IF;
1836:
1837: PA_SCHEDULE_UTILS.log_message(1,'end of apply_schedule_change .... ');
1838: x_return_status := l_x_return_status;
1839: EXCEPTION
1840: WHEN l_empty_tab_record THEN
1841: x_return_status := FND_API.G_RET_STS_SUCCESS;
1875:
1876: BEGIN
1877: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1878:
1879: PA_SCHEDULE_UTILS.log_message(1,'Start of create_new_schedule API ..... ');
1880: PA_SCHEDULE_UTILS.log_message(1,'exception_type_code '||p_sch_except_record.exception_type_code);
1881:
1882: -- This procedure will create the new schedule for the given change duration exception it will create by changing
1883: -- the duration
1876: BEGIN
1877: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1878:
1879: PA_SCHEDULE_UTILS.log_message(1,'Start of create_new_schedule API ..... ');
1880: PA_SCHEDULE_UTILS.log_message(1,'exception_type_code '||p_sch_except_record.exception_type_code);
1881:
1882: -- This procedure will create the new schedule for the given change duration exception it will create by changing
1883: -- the duration
1884: IF (p_sch_except_record.exception_type_code = 'CHANGE_DURATION' OR
1941: x_msg_data);
1942: END IF;
1943: END IF;
1944:
1945: PA_SCHEDULE_UTILS.log_message(1,'after calling respective APIs return x_sch_record_tab.coun '||to_char
1946: (x_sch_record_tab.count)||' Status '||l_x_return_status);
1947: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1948: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
1949: p_change_type_code => 'I',
1944:
1945: PA_SCHEDULE_UTILS.log_message(1,'after calling respective APIs return x_sch_record_tab.coun '||to_char
1946: (x_sch_record_tab.count)||' Status '||l_x_return_status);
1947: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1948: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
1949: p_change_type_code => 'I',
1950: x_return_status => l_x_return_status,
1951: x_msg_count => x_msg_count,
1952: x_msg_data => x_msg_data
1986: BEGIN
1987: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1988:
1989: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1990: PA_SCHEDULE_UTILS.check_calendar(
1991: p_calendar_id => p_sch_except_record.calendar_id,
1992: p_start_date => p_sch_except_record.start_date,
1993: p_end_date => p_sch_except_record.end_date,
1994: x_return_status => l_x_return_status,
2007: x_msg_data
2008: );
2009: END IF;
2010: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2011: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2012: p_project_id => p_sch_except_record.project_id,
2013: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2014: p_assignment_id => p_sch_except_record.assignment_id,
2015: p_assignment_status_code => p_sch_record.assignment_status_code,
2026: x_msg_data := SQLERRM;
2027: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2028: p_procedure_name => 'create_new_calendar');
2029:
2030: PA_SCHEDULE_UTILS.log_message(1,'ERROR in create_new_schedule procedure ');
2031: raise;
2032:
2033: END create_new_calendar;
2034:
2056:
2057: l_t_resource_id NUMBER; -- to sto
2058: l_t_asgn_type pa_project_assignments.assignment_type%TYPE; /*Bug 5682726*/
2059: BEGIN
2060: PA_SCHEDULE_UTILS.log_message(1,'start of create new hours ... ');
2061: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2062: PA_SCHEDULE_UTILS.log_message(1,'start of create new hours ...change_hours_type ...'||
2063: p_sch_except_record.change_hours_type_code);
2064:
2058: l_t_asgn_type pa_project_assignments.assignment_type%TYPE; /*Bug 5682726*/
2059: BEGIN
2060: PA_SCHEDULE_UTILS.log_message(1,'start of create new hours ... ');
2061: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2062: PA_SCHEDULE_UTILS.log_message(1,'start of create new hours ...change_hours_type ...'||
2063: p_sch_except_record.change_hours_type_code);
2064:
2065: -- For this hours code we will changed the previous schedule 's hours with the new passed
2066: -- values and create new hours for a given period only
2103: FROM pa_project_assignments
2104: WHERE assignment_id = p_sch_except_record.assignment_id;
2105:
2106: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2107: PA_SCHEDULE_UTILS.check_calendar(p_resource_id => l_t_resource_id,
2108: p_start_date => p_sch_except_record.start_date,
2109: p_end_date => p_sch_except_record.end_date,
2110: x_return_status => l_x_return_status,
2111: x_msg_count => x_msg_count,
2131: FROM pa_project_assignments
2132: where assignment_id = p_sch_except_record.assignment_id;
2133:
2134: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2135: PA_SCHEDULE_UTILS.check_calendar(p_calendar_id => l_t_calendar_id,
2136: p_start_date => p_sch_except_record.start_date,
2137: p_end_date => p_sch_except_record.end_date,
2138: x_return_status => l_x_return_status,
2139: x_msg_count => x_msg_count,
2207: FROM pa_project_assignments
2208: WHERE assignment_id = p_sch_except_record.assignment_id;
2209:
2210: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2211: PA_SCHEDULE_UTILS.check_calendar(p_resource_id => l_t_resource_id,
2212: p_start_date => p_sch_except_record.start_date,
2213: p_end_date => p_sch_except_record.end_date,
2214: x_return_status => l_x_return_status,
2215: x_msg_count => x_msg_count,
2241: l_t_calendar_id := p_sch_except_record.change_calendar_id;
2242: END IF;
2243:
2244: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2245: PA_SCHEDULE_UTILS.check_calendar(
2246: p_calendar_id => l_t_calendar_id,
2247: p_start_date => p_sch_except_record.start_date,
2248: p_end_date => p_sch_except_record.end_date,
2249: x_return_status => l_x_return_status,
2266: END IF;
2267:
2268: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2269: -- Calling the schedule API
2270: PA_SCHEDULE_UTILS.apply_percentage(x_sch_record_tab,
2271: p_sch_except_record.resource_calendar_percent,
2272: l_x_return_status,
2273: x_msg_count,
2274: x_msg_data );
2278:
2279:
2280: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2281: -- Updating the pa schedules table
2282: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2283: p_project_id => p_sch_except_record.project_id,
2284: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2285: p_assignment_id => p_sch_except_record.assignment_id,
2286: p_calendar_id => p_sch_except_record.calendar_id,
2327: l_t_calendar_type VARCHAR2(30);
2328: BEGIN
2329: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2330:
2331: PA_SCHEDULE_UTILS.log_message(1,'Start of create_new_duration API .........');
2332: PA_SCHEDULE_UTILS.log_message(1,'schedule_type_code '||p_sch_except_record.schedule_type_code);
2333: -- Bug 1580455: added STAFFED_ADMIN_ASSIGNMENT case.
2334: IF (p_sch_except_record.schedule_type_code = 'STAFFED_ASSIGNMENT') OR
2335: (p_sch_except_record.schedule_type_code = 'STAFFED_ADMIN_ASSIGNMENT') THEN
2328: BEGIN
2329: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2330:
2331: PA_SCHEDULE_UTILS.log_message(1,'Start of create_new_duration API .........');
2332: PA_SCHEDULE_UTILS.log_message(1,'schedule_type_code '||p_sch_except_record.schedule_type_code);
2333: -- Bug 1580455: added STAFFED_ADMIN_ASSIGNMENT case.
2334: IF (p_sch_except_record.schedule_type_code = 'STAFFED_ASSIGNMENT') OR
2335: (p_sch_except_record.schedule_type_code = 'STAFFED_ADMIN_ASSIGNMENT') THEN
2336:
2354: END IF;
2355:
2356: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2357: -- Calling the schedule API which will apply the percentage on the schedule
2358: PA_SCHEDULE_UTILS.apply_percentage(x_sch_record_tab,
2359: l_t_res_cal_percent,
2360: l_x_return_status,
2361: x_msg_count,
2362: x_msg_data
2376: END IF;
2377:
2378: ELSE
2379:
2380: PA_SCHEDULE_UTILS.log_message(2,'cal_id '||p_sch_except_record.calendar_id||' st_dt '||
2381: p_sch_except_record.start_date||' en_dt '||p_sch_except_record.end_date);
2382:
2383: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2384: -- Calling the procedure get_calendar_schedule on the basis of passed calendar id and for
2392: x_msg_data
2393: );
2394: END IF;
2395:
2396: PA_SCHEDULE_UTILS.log_message(2,'x_sch ',x_sch_record_tab);
2397: END IF;
2398:
2399: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2400: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2396: PA_SCHEDULE_UTILS.log_message(2,'x_sch ',x_sch_record_tab);
2397: END IF;
2398:
2399: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2400: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2401: p_project_id => p_sch_except_record.project_id,
2402: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2403: p_assignment_id => p_sch_except_record.assignment_id,
2404: p_calendar_id => p_sch_except_record.calendar_id,
2417: x_msg_data := SQLERRM;
2418: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2419: p_procedure_name => 'create_new_duration');
2420:
2421: PA_SCHEDULE_UTILS.log_message(1,'ERROR in create_new_duration procedure ');
2422: raise;
2423:
2424: END create_new_duration;
2425:
2616: IF (l_Merge_Flag = 'Y') THEN
2617: PA_SCHEDULE_PKG.Update_Rows(l_final_sch_rec_tab,l_x_return_status,x_msg_count,x_msg_data);
2618: PA_SCHEDULE_PKG.Delete_Rows(l_del_sch_rec_tab,l_x_return_status,x_msg_count,x_msg_data);
2619: ELSE
2620: PA_SCHEDULE_UTILS.log_message(1,'INFO: No Merging required.... ');
2621:
2622: END IF;
2623:
2624: /*Added for bug 6921728*/
2631: x_msg_data := SQLERRM;
2632: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2633: p_procedure_name => 'merge_work_pattern');
2634:
2635: PA_SCHEDULE_UTILS.log_message(1,'ERROR in merge_work_pattern procedure ');
2636: raise;
2637: END merge_work_pattern;
2638:
2639: --*******************************************************************************
2672: x_sch_record_tab(1).Sunday_hours := nvl(p_sch_except_record.Sunday_hours, p_sch_record.Sunday_hours);
2673:
2674: -- Updating the records with the new work pattern
2675:
2676: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2677: p_project_id => p_sch_except_record.project_id,
2678: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2679: p_assignment_id => p_sch_except_record.assignment_id,
2680: p_calendar_id => p_sch_except_record.calendar_id,
2732: Raise Status_change_not_allowed;
2733: END IF;
2734:
2735: -- Updating the records with the given status
2736: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2737: p_project_id => p_sch_except_record.project_id,
2738: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2739: p_assignment_id => p_sch_except_record.assignment_id,
2740: p_calendar_id => p_sch_except_record.calendar_id,
2802: l_chg_exp_end_date DATE;
2803:
2804: BEGIN
2805: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2806: PA_SCHEDULE_UTILS.log_message(1,'start of the apply_change_duration API ....... ');
2807:
2808: l_stat_date_done := false;
2809: l_end_date_done := false;
2810:
2814: l_chg_exp_start_date := p_sch_except_record.start_date;
2815: l_chg_exp_end_date := p_sch_except_record.end_date;
2816:
2817: -- Copying this schedule record for the further calculation
2818: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(p_sch_record_tab,
2819: p_sch_record_tab.first,
2820: p_sch_record_tab.last,
2821: l_temp_p_sch_record_tab,
2822: l_x_return_status,
2824: x_msg_data );
2825:
2826: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2827: -- Copying exception record for the further calculation
2828: PA_SCHEDULE_UTILS.copy_except_record(p_sch_except_record,
2829: l_temp_except_rec,
2830: l_x_return_status,
2831: x_msg_count,
2832: x_msg_data );
2870: END IF; -- IF (exception_type_code = 'SHIFT_DURATION')
2871:
2872: */
2873:
2874: PA_SCHEDULE_UTILS.log_message(1,'SCH_START ',l_temp_p_sch_record_tab);
2875:
2876: PA_SCHEDULE_UTILS.log_message(1,'START EXCEPT START_DATE '||p_sch_except_record.start_date||' END_DATE '
2877: ||p_sch_except_record.end_date);
2878:
2872: */
2873:
2874: PA_SCHEDULE_UTILS.log_message(1,'SCH_START ',l_temp_p_sch_record_tab);
2875:
2876: PA_SCHEDULE_UTILS.log_message(1,'START EXCEPT START_DATE '||p_sch_except_record.start_date||' END_DATE '
2877: ||p_sch_except_record.end_date);
2878:
2879: -- Exception date is falling outside the start date and end date of the schedule
2880: IF ( p_sch_except_record.start_date IS NOT NULL ) AND
2884: OR
2885: ( p_sch_except_record.start_date >
2886: l_temp_p_sch_record_tab(l_temp_last).end_date ) ) THEN
2887:
2888: PA_SCHEDULE_UTILS.log_message(1,'out side boundary condition ');
2889:
2890: l_stat_date_done := true;
2891: l_end_date_done := true;
2892: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2890: l_stat_date_done := true;
2891: l_end_date_done := true;
2892: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2893: -- Marking the schedule record for deletion
2894: PA_SCHEDULE_UTILS.mark_del_sch_rec_tab ( l_temp_p_sch_record_tab.first,
2895: l_temp_p_sch_record_tab.last,
2896: l_temp_p_sch_record_tab,
2897: l_x_return_status,
2898: x_msg_count,
2910: END IF;
2911:
2912: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2913: -- Adding the previous record into the new record
2914: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
2915: l_temp_p_sch_record_tab.first,
2916: l_temp_p_sch_record_tab.last,
2917: l_out_tr_sch_rec_tab,
2918: l_x_return_status,
2920: x_msg_data );
2921: END IF;
2922: END IF;
2923:
2924: PA_SCHEDULE_UTILS.log_message(2,'BEFORE LOOP');
2925:
2926: l_I := l_temp_p_sch_record_tab.first ;
2927:
2928: IF ( p_sch_except_record.start_date IS NULL ) THEN
2932: l_end_date_done := true;
2933: END IF;
2934:
2935: LOOP
2936: PA_SCHEDULE_UTILS.log_message(2,'LOOP START (l_I='||to_char(l_I)||') s: '||
2937: l_temp_p_sch_record_tab(l_I).start_date||' e: '||
2938: l_temp_p_sch_record_tab(l_I).end_date );
2939:
2940: l_copy_cur_sch := false;
2944: -- end date will be one day minus from the start date of the scheule for the first record
2945: IF ( l_stat_date_done = false) THEN
2946: IF (( p_sch_except_record.start_date <= l_temp_p_sch_record_tab(l_I).end_date )) THEN
2947:
2948: PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date <= sch_start_date ');
2949:
2950: l_stat_date_done := true;
2951: IF ( ( l_I = l_temp_first ) AND
2952: ( p_sch_except_record.start_date
2951: IF ( ( l_I = l_temp_first ) AND
2952: ( p_sch_except_record.start_date
2953: < l_temp_p_sch_record_tab(l_I).start_date )) THEN
2954:
2955: PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date < sch_start_date AND First shift ');
2956:
2957: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2958: PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
2959: p_start_date => p_sch_except_record.start_date,
2954:
2955: PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date < sch_start_date AND First shift ');
2956:
2957: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2958: PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
2959: p_start_date => p_sch_except_record.start_date,
2960: p_end_date => l_temp_p_sch_record_tab( l_temp_first).start_date -1,
2961: x_return_status => l_x_return_status,
2962: x_msg_count => x_msg_count,
2972: x_msg_data );
2973: END IF;
2974: ELSE
2975:
2976: PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date <= sch_start_date AND NOT Last shift ');
2977:
2978: l_temp_p_sch_record_tab(l_I).start_date := p_sch_except_record.start_date;
2979: l_temp_p_sch_record_tab(l_I).change_type_code := 'U';
2980: END IF;
2981:
2982: IF ( l_end_date_done ) THEN
2983: l_copy_cur_sch := true;
2984: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2985: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
2986: l_I,
2987: l_temp_p_sch_record_tab.last,
2988: l_out_tr_sch_rec_tab,
2989: l_x_return_status,
2992: END IF;
2993:
2994: END IF;
2995: ELSE
2996: PA_SCHEDULE_UTILS.log_message(2,'inside exp_start_date > sch_start_date AND MARKING SHIFT as DELETE');
2997: l_temp_p_sch_record_tab(l_I).change_type_code := 'D';
2998: END IF; -- start_date cond if */
2999: END IF; -- l_stat_date_done if */
3000:
3001: IF ( l_end_date_done = false) THEN
3002: IF (( p_sch_except_record.end_date <= l_temp_p_sch_record_tab(l_I).end_date )) THEN
3003: l_end_date_done := true;
3004:
3005: PA_SCHEDULE_UTILS.log_message(2,'inside exp_end_date <= sch_end_date ');
3006:
3007: l_temp_p_sch_record_tab(l_I).end_date := p_sch_except_record.end_date;
3008: l_temp_p_sch_record_tab(l_I).change_type_code := 'U';
3009:
3008: l_temp_p_sch_record_tab(l_I).change_type_code := 'U';
3009:
3010: IF ( l_I < l_temp_last ) THEN
3011:
3012: PA_SCHEDULE_UTILS.log_message(2,'inside exp_end_date <= sch_end_date AND MARKING DELETE ');
3013: -- Mark remaining shifts as delete. */
3014: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3015: PA_SCHEDULE_UTILS.mark_del_sch_rec_tab ( l_temp_p_sch_record_tab.next(l_I),
3016: l_temp_p_sch_record_tab.last,
3011:
3012: PA_SCHEDULE_UTILS.log_message(2,'inside exp_end_date <= sch_end_date AND MARKING DELETE ');
3013: -- Mark remaining shifts as delete. */
3014: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3015: PA_SCHEDULE_UTILS.mark_del_sch_rec_tab ( l_temp_p_sch_record_tab.next(l_I),
3016: l_temp_p_sch_record_tab.last,
3017: l_temp_p_sch_record_tab,
3018: l_x_return_status,
3019: x_msg_count,
3021: END IF;
3022: END IF;
3023: l_copy_cur_sch := true;
3024: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3025: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3026: l_I,
3027: l_temp_p_sch_record_tab.last,
3028: l_out_tr_sch_rec_tab,
3029: l_x_return_status,
3032: END IF;
3033: ELSIF ( ( p_sch_except_record.end_date > l_temp_p_sch_record_tab(l_I).end_date ) AND
3034: ( l_I = l_temp_last ) ) THEN
3035:
3036: PA_SCHEDULE_UTILS.log_message(2,'inside exp_end_date > sch_end_date AND LAST SHIFT ');
3037:
3038: l_copy_cur_sch := true;
3039: l_end_date_done := true;
3040: -- Copy this shift into l_out_tr_sch_rec_tab; */
3038: l_copy_cur_sch := true;
3039: l_end_date_done := true;
3040: -- Copy this shift into l_out_tr_sch_rec_tab; */
3041: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3042: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3043: l_I,
3044: l_I,
3045: l_out_tr_sch_rec_tab,
3046: l_x_return_status,
3048: x_msg_data );
3049: END IF;
3050:
3051: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3052: PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
3053: p_start_date => l_temp_p_sch_record_tab(l_I).end_date +1 ,
3054: p_end_date => p_sch_except_record.end_date,
3055: x_return_status => l_x_return_status,
3056: x_msg_count => x_msg_count,
3067: x_msg_data );
3068: END IF;
3069:
3070: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3071: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_tr_sch_rec_tab ,
3072: l_temp_tr_sch_rec_tab.first,
3073: l_temp_tr_sch_rec_tab.last,
3074: l_out_tr_sch_rec_tab,
3075: l_x_return_status,
3080: END IF; -- l_end_date_done if */
3081:
3082: IF ( l_copy_cur_sch = false ) THEN
3083: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3084: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3085: l_I,
3086: l_I,
3087: l_out_tr_sch_rec_tab,
3088: l_x_return_status,
3100: END IF;
3101:
3102: END LOOP;
3103:
3104: PA_SCHEDULE_UTILS.log_message(2,'DONE_CHANGE_DURATION : out_tr.count : '||l_out_tr_sch_rec_tab.count);
3105:
3106: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3107: PA_SCHEDULE_UTILS.copy_schedule_rec_tab ( l_out_tr_sch_rec_tab ,
3108: l_out_tr_sch_rec_tab.first,
3103:
3104: PA_SCHEDULE_UTILS.log_message(2,'DONE_CHANGE_DURATION : out_tr.count : '||l_out_tr_sch_rec_tab.count);
3105:
3106: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3107: PA_SCHEDULE_UTILS.copy_schedule_rec_tab ( l_out_tr_sch_rec_tab ,
3108: l_out_tr_sch_rec_tab.first,
3109: l_out_tr_sch_rec_tab.last,
3110: x_sch_record_tab,
3111: l_x_return_status,
3121: x_msg_data := SQLERRM;
3122: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3123: p_procedure_name => 'apply_change_duration');
3124:
3125: PA_SCHEDULE_UTILS.log_message(1,'ERROR while running apply...');
3126: raise;
3127: END apply_change_duration;
3128:
3129: -- This procedure is caled from change_schedule procedure .This procedure will apply changes on schedule
3166: l_create_third_sch BOOLEAN;
3167:
3168: BEGIN
3169: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
3170: PA_SCHEDULE_UTILS.copy_schedule_rec_tab(p_sch_record_tab,
3171: p_sch_record_tab.first,
3172: p_sch_record_tab.last,
3173: l_temp_p_sch_record_tab,
3174: l_x_return_status,
3175: x_msg_count,
3176: x_msg_data );
3177:
3178: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3179: PA_SCHEDULE_UTILS.copy_except_record(p_sch_except_record,
3180: l_temp_except_rec,
3181: l_x_return_status,
3182: x_msg_count,
3183: x_msg_data );
3188:
3189: l_curr_exp_start_date := p_sch_except_record.start_date;
3190: l_curr_exp_end_date := p_sch_except_record.end_date;
3191:
3192: PA_SCHEDULE_UTILS.log_message(1,'SCH_START ',l_temp_p_sch_record_tab);
3193: PA_SCHEDULE_UTILS.log_message(1,'START EXCEPT START_DATE '||l_curr_exp_start_date||' END_DATE '||l_curr_exp_end_date);
3194:
3195: l_I := l_temp_p_sch_record_tab.first ;
3196:
3189: l_curr_exp_start_date := p_sch_except_record.start_date;
3190: l_curr_exp_end_date := p_sch_except_record.end_date;
3191:
3192: PA_SCHEDULE_UTILS.log_message(1,'SCH_START ',l_temp_p_sch_record_tab);
3193: PA_SCHEDULE_UTILS.log_message(1,'START EXCEPT START_DATE '||l_curr_exp_start_date||' END_DATE '||l_curr_exp_end_date);
3194:
3195: l_I := l_temp_p_sch_record_tab.first ;
3196:
3197: LOOP
3201:
3202: l_curr_sch_start_date := l_temp_p_sch_record_tab(l_I).start_date;
3203: l_curr_sch_end_date := l_temp_p_sch_record_tab(l_I).end_date;
3204:
3205: PA_SCHEDULE_UTILS.log_message(2,'SHIFT('||l_I||') '||l_curr_sch_start_date||' '||l_curr_sch_end_date);
3206: IF ( l_curr_exp_start_date <= l_curr_sch_end_date ) THEN
3207:
3208: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_end_date if .... ');
3209: IF ( l_curr_exp_start_date <= l_curr_sch_start_date ) THEN
3204:
3205: PA_SCHEDULE_UTILS.log_message(2,'SHIFT('||l_I||') '||l_curr_sch_start_date||' '||l_curr_sch_end_date);
3206: IF ( l_curr_exp_start_date <= l_curr_sch_end_date ) THEN
3207:
3208: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_end_date if .... ');
3209: IF ( l_curr_exp_start_date <= l_curr_sch_start_date ) THEN
3210:
3211: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_start_date if .... ');
3212: l_chg_exp_start_date := l_curr_sch_start_date;
3207:
3208: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_end_date if .... ');
3209: IF ( l_curr_exp_start_date <= l_curr_sch_start_date ) THEN
3210:
3211: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date <= l_curr_sch_start_date if .... ');
3212: l_chg_exp_start_date := l_curr_sch_start_date;
3213:
3214: IF ( l_curr_exp_end_date < l_curr_sch_end_date ) THEN
3215:
3212: l_chg_exp_start_date := l_curr_sch_start_date;
3213:
3214: IF ( l_curr_exp_end_date < l_curr_sch_end_date ) THEN
3215:
3216: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date < l_curr_sch_end_date if ...... ');
3217: l_chg_exp_end_date := l_curr_exp_end_date;
3218: l_temp_p_sch_record_tab(l_I).start_date := l_curr_exp_end_date + 1;
3219: IF (nvl(l_temp_p_sch_record_tab(l_I).change_type_code,'U') <> 'I') THEN -- Added If condition for 4349232
3220: l_temp_p_sch_record_tab(l_I).change_type_code := 'U';
3221: END IF;
3222:
3223: ELSIF ( l_curr_exp_end_date >= l_curr_sch_end_date ) THEN
3224:
3225: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date >= l_curr_sch_end_date if ...... ');
3226: l_chg_exp_end_date := l_curr_sch_end_date;
3227: l_temp_p_sch_record_tab(l_I).change_type_code := 'D';
3228:
3229: END IF;
3229: END IF;
3230: l_create_new_sch := true;
3231:
3232: IF ( l_curr_exp_end_date <= l_curr_sch_end_date ) THEN
3233: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date <= l_curr_sch_end_date if ........... ');
3234: l_change_done := true;
3235: END IF;
3236:
3237: ELSIF ( l_curr_exp_start_date > l_curr_sch_start_date ) THEN
3235: END IF;
3236:
3237: ELSIF ( l_curr_exp_start_date > l_curr_sch_start_date ) THEN
3238:
3239: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_start_date > l_curr_sch_start_date if ...... ');
3240:
3241: l_temp_p_sch_record_tab(l_I).end_date := l_curr_exp_start_date - 1;
3242:
3243: IF (nvl(l_temp_p_sch_record_tab(l_I).change_type_code,'U') <> 'I') THEN -- Added If condition for 4287560
3245: END IF;
3246:
3247: l_copy_cur_sch := true;
3248: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3249: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3250: l_I,
3251: l_I,
3252: l_out_tr_sch_rec_tab,
3253: l_x_return_status,
3254: x_msg_count,
3255: x_msg_data );
3256: END IF;
3257:
3258: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3259: l_chg_exp_start_date := l_curr_exp_start_date;
3260: IF ( l_curr_exp_end_date > l_curr_sch_end_date ) THEN
3261: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date > l_curr_sch_end_date if ....... ');
3262: l_chg_exp_end_date := l_curr_sch_end_date;
3257:
3258: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3259: l_chg_exp_start_date := l_curr_exp_start_date;
3260: IF ( l_curr_exp_end_date > l_curr_sch_end_date ) THEN
3261: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date > l_curr_sch_end_date if ....... ');
3262: l_chg_exp_end_date := l_curr_sch_end_date;
3263: ELSE
3264: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date <= l_curr_sch_end_date if ....... ');
3265: l_chg_exp_end_date := l_curr_exp_end_date;
3260: IF ( l_curr_exp_end_date > l_curr_sch_end_date ) THEN
3261: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date > l_curr_sch_end_date if ....... ');
3262: l_chg_exp_end_date := l_curr_sch_end_date;
3263: ELSE
3264: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date <= l_curr_sch_end_date if ....... ');
3265: l_chg_exp_end_date := l_curr_exp_end_date;
3266: l_change_done := true;
3267: END IF;
3268: l_create_new_sch := true;
3267: END IF;
3268: l_create_new_sch := true;
3269:
3270: IF ( l_curr_exp_end_date < l_curr_sch_end_date ) THEN
3271: PA_SCHEDULE_UTILS.log_message(2,'inside l_curr_exp_end_date < l_curr_sch_end_date if .... ');
3272: l_create_third_sch := true;
3273: l_change_done := true;
3274: END IF;
3275:
3276: END IF; -- end of level 2 if */
3277: END IF; -- end of level 1 if */
3278:
3279: IF ( l_create_new_sch ) THEN
3280: PA_SCHEDULE_UTILS.log_message(2,'inside l_create_new_sch if ........'||'status '||l_x_return_status);
3281: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3282: PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
3283: p_start_date => l_chg_exp_start_date,
3284: p_end_date => l_chg_exp_end_date,
3278:
3279: IF ( l_create_new_sch ) THEN
3280: PA_SCHEDULE_UTILS.log_message(2,'inside l_create_new_sch if ........'||'status '||l_x_return_status);
3281: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3282: PA_SCHEDULE_UTILS.update_except_record(px_except_record => l_temp_except_rec,
3283: p_start_date => l_chg_exp_start_date,
3284: p_end_date => l_chg_exp_end_date,
3285: x_return_status => l_x_return_status,
3286: x_msg_count => x_msg_count,
3294: l_x_return_status,
3295: x_msg_count,
3296: x_msg_data );
3297: END IF;
3298: PA_SCHEDULE_UTILS.log_message(2,'l_temp_tr_sch_rec_tab.count '||to_char(l_temp_tr_sch_rec_tab.count)||
3299: l_x_return_status);
3300:
3301: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3302: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_tr_sch_rec_tab ,
3298: PA_SCHEDULE_UTILS.log_message(2,'l_temp_tr_sch_rec_tab.count '||to_char(l_temp_tr_sch_rec_tab.count)||
3299: l_x_return_status);
3300:
3301: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3302: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_tr_sch_rec_tab ,
3303: l_temp_tr_sch_rec_tab.first,
3304: l_temp_tr_sch_rec_tab.last,
3305: l_out_tr_sch_rec_tab,
3306: l_x_return_status,
3306: l_x_return_status,
3307: x_msg_count,
3308: x_msg_data );
3309: END IF;
3310: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3311: END IF;
3312:
3313: IF ( l_create_third_sch = true ) THEN
3314:
3311: END IF;
3312:
3313: IF ( l_create_third_sch = true ) THEN
3314:
3315: PA_SCHEDULE_UTILS.log_message(2,'inside l_create_third_sch if ........');
3316: l_temp_p_sch_record_tab(l_I).start_date := l_curr_exp_end_date + 1;
3317: l_temp_p_sch_record_tab(l_I).end_date := l_curr_sch_end_date;
3318: l_temp_p_sch_record_tab(l_I).change_type_code := 'I';
3319: l_temp_p_sch_record_tab(l_I).schedule_id := -1; -- Included for Bug 4616327
3317: l_temp_p_sch_record_tab(l_I).end_date := l_curr_sch_end_date;
3318: l_temp_p_sch_record_tab(l_I).change_type_code := 'I';
3319: l_temp_p_sch_record_tab(l_I).schedule_id := -1; -- Included for Bug 4616327
3320: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3321: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3322: l_I,
3323: l_I,
3324: l_out_tr_sch_rec_tab,
3325: l_x_return_status,
3326: x_msg_count,
3327: x_msg_data );
3328: END IF;
3329:
3330: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3331: END IF;
3332:
3333: IF ( l_copy_cur_sch = false ) THEN
3334: PA_SCHEDULE_UTILS.log_message(2,'inside l_copy_cur_sch if ........');
3330: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3331: END IF;
3332:
3333: IF ( l_copy_cur_sch = false ) THEN
3334: PA_SCHEDULE_UTILS.log_message(2,'inside l_copy_cur_sch if ........');
3335: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3336: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3337: l_I,
3338: l_I,
3332:
3333: IF ( l_copy_cur_sch = false ) THEN
3334: PA_SCHEDULE_UTILS.log_message(2,'inside l_copy_cur_sch if ........');
3335: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3336: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3337: l_I,
3338: l_I,
3339: l_out_tr_sch_rec_tab,
3340: l_x_return_status,
3340: l_x_return_status,
3341: x_msg_count,
3342: x_msg_data );
3343: END IF;
3344: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3345: END IF;
3346:
3347: IF ( l_change_done ) OR ( l_I = l_sch_last ) THEN
3348:
3345: END IF;
3346:
3347: IF ( l_change_done ) OR ( l_I = l_sch_last ) THEN
3348:
3349: PA_SCHEDULE_UTILS.log_message(2,'inside l_change_done if ........');
3350: IF ( l_I < l_sch_last ) THEN
3351: PA_SCHEDULE_UTILS.log_message(2,'inside l_I < l_sch_last if ....... ');
3352: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3353: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3347: IF ( l_change_done ) OR ( l_I = l_sch_last ) THEN
3348:
3349: PA_SCHEDULE_UTILS.log_message(2,'inside l_change_done if ........');
3350: IF ( l_I < l_sch_last ) THEN
3351: PA_SCHEDULE_UTILS.log_message(2,'inside l_I < l_sch_last if ....... ');
3352: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3353: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3354: l_temp_p_sch_record_tab.next(l_I),
3355: l_sch_last,
3349: PA_SCHEDULE_UTILS.log_message(2,'inside l_change_done if ........');
3350: IF ( l_I < l_sch_last ) THEN
3351: PA_SCHEDULE_UTILS.log_message(2,'inside l_I < l_sch_last if ....... ');
3352: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3353: PA_SCHEDULE_UTILS.add_schedule_rec_tab ( l_temp_p_sch_record_tab ,
3354: l_temp_p_sch_record_tab.next(l_I),
3355: l_sch_last,
3356: l_out_tr_sch_rec_tab,
3357: l_x_return_status,
3357: l_x_return_status,
3358: x_msg_count,
3359: x_msg_data );
3360: END IF;
3361: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3362: END IF;
3363: EXIT;
3364: ELSE
3365: PA_SCHEDULE_UTILS.log_message(2,'go to next shift ..... ');
3361: PA_SCHEDULE_UTILS.log_message(2,'l_out_tr_sch_rec_tab.count '||to_char(l_out_tr_sch_rec_tab.count));
3362: END IF;
3363: EXIT;
3364: ELSE
3365: PA_SCHEDULE_UTILS.log_message(2,'go to next shift ..... ');
3366: l_I := l_temp_p_sch_record_tab.next(l_I);
3367: END IF;
3368:
3369: END LOOP;
3367: END IF;
3368:
3369: END LOOP;
3370:
3371: PA_SCHEDULE_UTILS.log_message(2,'end of the loop l_out_tr_sch_rec_tab.count '||
3372: to_char(l_out_tr_sch_rec_tab.count)||'status '||l_x_return_status);
3373: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3374: PA_SCHEDULE_UTILS.copy_schedule_rec_tab ( l_out_tr_sch_rec_tab ,
3375: l_out_tr_sch_rec_tab.first,
3370:
3371: PA_SCHEDULE_UTILS.log_message(2,'end of the loop l_out_tr_sch_rec_tab.count '||
3372: to_char(l_out_tr_sch_rec_tab.count)||'status '||l_x_return_status);
3373: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3374: PA_SCHEDULE_UTILS.copy_schedule_rec_tab ( l_out_tr_sch_rec_tab ,
3375: l_out_tr_sch_rec_tab.first,
3376: l_out_tr_sch_rec_tab.last,
3377: x_sch_record_tab,
3378: l_x_return_status,
3379: x_msg_count,
3380: x_msg_data );
3381: END IF;
3382:
3383: PA_SCHEDULE_UTILS.log_message(2,'end of the loop x_sch_record_tab.count '||to_char(x_sch_record_tab.count));
3384: x_return_status := l_x_return_status;
3385: EXCEPTION
3386: WHEN OTHERS THEN
3387: x_return_status := FND_API.G_RET_STS_ERROR;
3389: x_msg_data := SQLERRM;
3390: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3391: p_procedure_name => 'apply_other_change');
3392:
3393: PA_SCHEDULE_UTILS.log_message(2,'ERROR in apply_other_changes ....');
3394: PA_SCHEDULE_UTILS.log_message(2,sqlerrm);
3395: raise;
3396: END apply_other_changes;
3397:
3390: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3391: p_procedure_name => 'apply_other_change');
3392:
3393: PA_SCHEDULE_UTILS.log_message(2,'ERROR in apply_other_changes ....');
3394: PA_SCHEDULE_UTILS.log_message(2,sqlerrm);
3395: raise;
3396: END apply_other_changes;
3397:
3398:
3487: END IF;
3488:
3489: END LOOP;
3490:
3491: PA_SCHEDULE_UTILS.log_message(1,'20: ' || l_x_return_status ||
3492: ' ' || x_msg_count ||
3493: ' ' || x_msg_data);
3494:
3495: -- if the records is having more than one status then updating the table for it column multi flag */
3501: p_end_date => nvl(l_t_end_date,FND_API.G_MISS_DATE),
3502: p_multiple_status_flag => l_t_multi_flag,
3503: p_status_code => l_curr_status,
3504: p_assignment_effort =>
3505: pa_schedule_utils.get_num_hours(l_t_project_id, l_t_assignment_id),
3506: x_return_status => l_x_return_status );
3507: ELSE
3508: PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
3509: p_record_version_number => p_record_version_number,
3508: PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
3509: p_record_version_number => p_record_version_number,
3510: p_assignment_id => l_t_assignment_id,
3511: p_assignment_effort =>
3512: pa_schedule_utils.get_num_hours(l_t_project_id, l_t_assignment_id),
3513: x_return_status => l_x_return_status );
3514: END IF;
3515:
3516: PA_SCHEDULE_UTILS.log_message(1,'20: ' || l_x_return_status ||
3512: pa_schedule_utils.get_num_hours(l_t_project_id, l_t_assignment_id),
3513: x_return_status => l_x_return_status );
3514: END IF;
3515:
3516: PA_SCHEDULE_UTILS.log_message(1,'20: ' || l_x_return_status ||
3517: ' ' || x_msg_count ||
3518: ' ' || x_msg_data);
3519:
3520: -- If assignment record is successfully updated then call procedure to update pa project parties.
3553: IF ( l_t_end_date is NULL ) THEN
3554: l_t_end_date := l_t_asgn_end_date;
3555: END IF;
3556:
3557: PA_SCHEDULE_UTILS.log_message(1,'21: ' || l_x_return_status ||
3558: ' ' || x_msg_count ||
3559: ' ' || x_msg_data);
3560:
3561: pa_project_parties_pvt.UPDATE_PROJECT_PARTY(
3699:
3700:
3701: EXCEPTION
3702: WHEN OTHERS THEN
3703: PA_SCHEDULE_UTILS.log_message(1, 'Error in get_periodic_start_end.....'||sqlerrm);
3704: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3705: x_msg_count := 1;
3706: x_msg_data := SQLERRM;
3707: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3762: l_alias_name PA_RESOURCE_LIST_MEMBERS.ALIAS%TYPE; /*Added for 4996210 */
3763: l_total_hours NUMBER; -- Bug 5126919
3764: BEGIN
3765: l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
3766: PA_SCHEDULE_UTILS.log_message(1, 'Start of the create_oasgn_schedule API ....');
3767:
3768: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_calendar_schedule ....');
3769:
3770: -- New code to process task assignment summation jraj 12/19/03
3764: BEGIN
3765: l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
3766: PA_SCHEDULE_UTILS.log_message(1, 'Start of the create_oasgn_schedule API ....');
3767:
3768: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_calendar_schedule ....');
3769:
3770: -- New code to process task assignment summation jraj 12/19/03
3771: -- Based on if p_sum_tasks_flag = Y
3772:
3853:
3854:
3855: END IF;
3856:
3857: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_calendar_schedule ....');
3858:
3859: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
3860: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....'||l_x_return_status);
3861:
3855: END IF;
3856:
3857: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_calendar_schedule ....');
3858:
3859: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
3860: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....'||l_x_return_status);
3861:
3862: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3863: -- updating the passed schedule table of record for creating the schedule for open assignment
3856:
3857: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_calendar_schedule ....');
3858:
3859: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
3860: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....'||l_x_return_status);
3861:
3862: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3863: -- updating the passed schedule table of record for creating the schedule for open assignment
3864: PA_SCHEDULE_UTILS.update_sch_rec_tab(l_new_schedule_tab_rec,
3860: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....'||l_x_return_status);
3861:
3862: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3863: -- updating the passed schedule table of record for creating the schedule for open assignment
3864: PA_SCHEDULE_UTILS.update_sch_rec_tab(l_new_schedule_tab_rec,
3865: p_project_id => p_project_id,
3866: p_calendar_id => p_calendar_id,
3867: p_schedule_type_code => 'OPEN_ASSIGNMENT',
3868: p_assignment_id => p_assignment_id,
3872: x_msg_data => x_msg_data
3873: );
3874: END IF;
3875:
3876: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
3877: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
3878: PA_SCHEDULE_UTILS.log_message(1,'SCH_REC',l_x_schedule_tab_rec);
3879:
3880: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3873: );
3874: END IF;
3875:
3876: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
3877: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
3878: PA_SCHEDULE_UTILS.log_message(1,'SCH_REC',l_x_schedule_tab_rec);
3879:
3880: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3881: -- Inserting the schedule in the PA_SCHEDULE table
3874: END IF;
3875:
3876: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
3877: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
3878: PA_SCHEDULE_UTILS.log_message(1,'SCH_REC',l_x_schedule_tab_rec);
3879:
3880: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3881: -- Inserting the schedule in the PA_SCHEDULE table
3882: PA_SCHEDULE_PKG.insert_rows(l_new_schedule_tab_rec,
3896:
3897: END IF;
3898:
3899:
3900: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
3901: PA_SCHEDULE_UTILS.log_message(1, 'End of the create_oasgn_schedule API ....');
3902: x_return_status := l_x_return_status;
3903: EXCEPTION
3904:
3897: END IF;
3898:
3899:
3900: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
3901: PA_SCHEDULE_UTILS.log_message(1, 'End of the create_oasgn_schedule API ....');
3902: x_return_status := l_x_return_status;
3903: EXCEPTION
3904:
3905: /*Added for 4996210 */
3907: x_return_status := FND_API.G_RET_STS_ERROR;
3908: /*End 4996210 */
3909:
3910: WHEN OTHERS THEN
3911: PA_SCHEDULE_UTILS.log_message(1, 'Error in create_oasgn_schedule API .....'||sqlerrm);
3912: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3913: x_msg_count := 1;
3914: x_msg_data := SQLERRM;
3915: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3958: l_temp_number NUMBER;
3959: BEGIN
3960: l_assignment_id_tbl := p_assignment_id_tbl;
3961: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
3962: PA_SCHEDULE_UTILS.log_message(1, 'Start of the create_asgn_schedule API ....');
3963:
3964:
3965: -- Case I: add multiple open assignments. When p_assignment_source_id IS
3966: -- 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.
4011: -- Copy schedules for the rest of the assignments in the table
4012: -- l_assignment_id_tbl.
4013: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4014: -- Update the passed schedule table of record for creating the schedule for open assignment
4015: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4016: px_sch_record_tab => l_current_sch_rec_tab,
4017: p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
4018: x_return_status => l_x_return_status,
4019: x_msg_count => x_msg_count,
4019: x_msg_count => x_msg_count,
4020: x_msg_data => x_msg_data
4021: );
4022: -- Add the schedule record to l_x_sch_rec_tab.
4023: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4024: p_sch_record_tab => l_current_sch_rec_tab,
4025: p_start_id => l_current_sch_rec_tab.first,
4026: p_end_id => l_current_sch_rec_tab.last,
4027: px_sch_record_tab => l_x_sch_rec_tab,
4039: x_msg_count => x_msg_count,
4040: x_msg_data => x_msg_data
4041: );
4042:
4043: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4044:
4045: -- Copy timeline data.
4046: PA_TIMELINE_PVT.copy_open_asgn_timeline (
4047: p_assignment_id_tbl => l_assignment_id_tbl,
4094: );
4095: END IF ;
4096: /* changes end for Bug 6145532*/
4097: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4098: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4099: -- Update the passed schedule record with p_assignment_status_code
4100: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4101: px_sch_record_tab => l_current_sch_rec_tab,
4102: p_schedule_type_code => 'OPEN_ASSIGNMENT',
4096: /* changes end for Bug 6145532*/
4097: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4098: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4099: -- Update the passed schedule record with p_assignment_status_code
4100: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4101: px_sch_record_tab => l_current_sch_rec_tab,
4102: p_schedule_type_code => 'OPEN_ASSIGNMENT',
4103: p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
4104: p_assignment_status_code => p_assignment_status_code,
4105: x_return_status => l_x_return_status,
4106: x_msg_count => x_msg_count,
4107: x_msg_data => x_msg_data
4108: );
4109: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
4110: -- Add the shedule record to l_x_sch_rec_tab.
4111: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4112: p_sch_record_tab => l_current_sch_rec_tab,
4113: p_start_id => l_current_sch_rec_tab.FIRST,
4107: x_msg_data => x_msg_data
4108: );
4109: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
4110: -- Add the shedule record to l_x_sch_rec_tab.
4111: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4112: p_sch_record_tab => l_current_sch_rec_tab,
4113: p_start_id => l_current_sch_rec_tab.FIRST,
4114: p_end_id => l_current_sch_rec_tab.LAST,
4115: px_sch_record_tab => l_x_sch_rec_tab,
4116: x_return_status => l_x_return_status,
4117: x_msg_count => x_msg_count,
4118: x_msg_data => x_msg_data
4119: );
4120: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API add_schedule_rec_tab .....');
4121: END LOOP;
4122:
4123: -- Bulk insert all the schedule records stored in l_x_sch_rec_tab.
4124: PA_SCHEDULE_PKG.insert_rows(
4126: x_return_status => l_x_return_status,
4127: x_msg_count => x_msg_count,
4128: x_msg_data => x_msg_data
4129: );
4130: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4131:
4132: -- Calling the Timeline API to build timeline records for the first
4133: -- assignment in the table l_assignment_id_tbl.
4134: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4164: x_msg_data
4165: );
4166:
4167: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4168: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4169: -- Update the passed schedule record with assignment_id.
4170: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4171: px_sch_record_tab => l_current_sch_rec_tab,
4172: p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
4166:
4167: FOR l_counter IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
4168: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4169: -- Update the passed schedule record with assignment_id.
4170: PA_SCHEDULE_UTILS.update_sch_rec_tab(
4171: px_sch_record_tab => l_current_sch_rec_tab,
4172: p_assignment_id => l_assignment_id_tbl(l_counter).assignment_id,
4173: x_return_status => l_x_return_status,
4174: x_msg_count => x_msg_count,
4173: x_return_status => l_x_return_status,
4174: x_msg_count => x_msg_count,
4175: x_msg_data => x_msg_data
4176: );
4177: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
4178: -- Add the shedule record to l_x_sch_rec_tab.
4179: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4180: p_sch_record_tab => l_current_sch_rec_tab,
4181: p_start_id => l_current_sch_rec_tab.first,
4175: x_msg_data => x_msg_data
4176: );
4177: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab .....');
4178: -- Add the shedule record to l_x_sch_rec_tab.
4179: PA_SCHEDULE_UTILS.add_schedule_rec_tab(
4180: p_sch_record_tab => l_current_sch_rec_tab,
4181: p_start_id => l_current_sch_rec_tab.first,
4182: p_end_id => l_current_sch_rec_tab.last,
4183: px_sch_record_tab => l_x_sch_rec_tab,
4184: x_return_status => l_x_return_status,
4185: x_msg_count => x_msg_count,
4186: x_msg_data => x_msg_data
4187: );
4188: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API add_schedule_rec_tab .....');
4189: END LOOP;
4190:
4191: -- Bulk insert all the schedule records stored in l_x_sch_rec_tab.
4192: PA_SCHEDULE_PKG.insert_rows(
4194: x_return_status => l_x_return_status,
4195: x_msg_count => x_msg_count,
4196: x_msg_data => x_msg_data
4197: );
4198: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4199:
4200: -- Copy timeline data.
4201: PA_TIMELINE_PVT.copy_open_asgn_timeline (
4202: p_assignment_id_tbl => l_assignment_id_tbl,
4211: x_return_status := l_x_return_status;
4212:
4213: EXCEPTION
4214: WHEN OTHERS THEN
4215: PA_SCHEDULE_UTILS.log_message(1, 'Error in create_opn_asgn_schedule API .....'||sqlerrm);
4216: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4217: x_msg_count := 1;
4218: x_msg_data := SQLERRM;
4219: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
4284: --jm_profiler.set_time('Create Schedule');
4285:
4286: -- Assigning status successs for tracking the error
4287: l_x_return_status := FND_API.G_RET_STS_SUCCESS ;
4288: PA_SCHEDULE_UTILS.log_message(1,'Start of the create_sasgn_schedule API ... ');
4289:
4290: -- =================================================
4291: -- New code to process task assignment summation jraj 12/19/03
4292:
4316:
4317: -- If the calendar type is resource then it will generate the schedule on the basis of
4318: -- the calendar asign to the resource
4319: IF (p_schedule_basis_flag = 'R') then
4320: PA_SCHEDULE_UTILS.log_message(1, 'Schedule Basis Flag - R');
4321: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_resource_schedule ....');
4322:
4323: -- Calling tthe PVT API which will get the schedule for staffed asignment and calendar type is resource
4324: PA_SCHEDULE_PVT.get_resource_schedule(p_project_party_id,
4317: -- If the calendar type is resource then it will generate the schedule on the basis of
4318: -- the calendar asign to the resource
4319: IF (p_schedule_basis_flag = 'R') then
4320: PA_SCHEDULE_UTILS.log_message(1, 'Schedule Basis Flag - R');
4321: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_resource_schedule ....');
4322:
4323: -- Calling tthe PVT API which will get the schedule for staffed asignment and calendar type is resource
4324: PA_SCHEDULE_PVT.get_resource_schedule(p_project_party_id,
4325: 'PA_PROJECT_PARTY_ID',
4329: l_x_return_status,
4330: x_msg_count,
4331: x_msg_data
4332: );
4333: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_resource_schedule ....');
4334: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API apply_percentage ....');
4335:
4336: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4337: -- Calling the PA_SCHEDULE_UTILS API whice will apply the percentage of the resource can be used
4330: x_msg_count,
4331: x_msg_data
4332: );
4333: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_resource_schedule ....');
4334: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API apply_percentage ....');
4335:
4336: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4337: -- Calling the PA_SCHEDULE_UTILS API whice will apply the percentage of the resource can be used
4338: PA_SCHEDULE_UTILS.apply_percentage(l_x_sch_record_tab,
4333: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_resource_schedule ....');
4334: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API apply_percentage ....');
4335:
4336: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4337: -- Calling the PA_SCHEDULE_UTILS API whice will apply the percentage of the resource can be used
4338: PA_SCHEDULE_UTILS.apply_percentage(l_x_sch_record_tab,
4339: p_resource_calendar_percent,
4340: l_x_return_status,
4341: x_msg_count,
4334: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API apply_percentage ....');
4335:
4336: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4337: -- Calling the PA_SCHEDULE_UTILS API whice will apply the percentage of the resource can be used
4338: PA_SCHEDULE_UTILS.apply_percentage(l_x_sch_record_tab,
4339: p_resource_calendar_percent,
4340: l_x_return_status,
4341: x_msg_count,
4342: x_msg_data
4341: x_msg_count,
4342: x_msg_data
4343: );
4344: END IF;
4345: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API apply_percentage ....');
4346:
4347: -- If the calendar type is other then resource then it will generate
4348: -- the schedule on the basis of the assignment asign to the calendar type
4349: ELSIF (p_schedule_basis_flag = 'A') THEN
4346:
4347: -- If the calendar type is other then resource then it will generate
4348: -- the schedule on the basis of the assignment asign to the calendar type
4349: ELSIF (p_schedule_basis_flag = 'A') THEN
4350: PA_SCHEDULE_UTILS.log_message(1, 'Schedule basis flag = A ....');
4351: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_assignment_schedule ....');
4352:
4353: -- code fix starts for bug 2335580
4354: SELECT start_date, end_date,calendar_id
4347: -- If the calendar type is other then resource then it will generate
4348: -- the schedule on the basis of the assignment asign to the calendar type
4349: ELSIF (p_schedule_basis_flag = 'A') THEN
4350: PA_SCHEDULE_UTILS.log_message(1, 'Schedule basis flag = A ....');
4351: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_assignment_schedule ....');
4352:
4353: -- code fix starts for bug 2335580
4354: SELECT start_date, end_date,calendar_id
4355: into l_req_start_date, l_req_end_date, l_calendar_id
4385: x_msg_data
4386: );
4387: END IF;
4388: END IF;
4389: PA_SCHEDULE_UTILS.log_message(1, 'After get_assignment_schedule .',l_x_sch_record_tab);
4390: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_assignment_schedule ....');
4391:
4392: -- if the calendar type is other then resource then it will generate
4393: -- the schedule on the basis of the assignment asign to the calendar type
4386: );
4387: END IF;
4388: END IF;
4389: PA_SCHEDULE_UTILS.log_message(1, 'After get_assignment_schedule .',l_x_sch_record_tab);
4390: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API get_assignment_schedule ....');
4391:
4392: -- if the calendar type is other then resource then it will generate
4393: -- the schedule on the basis of the assignment asign to the calendar type
4394: ELSIF (p_schedule_basis_flag IN ('P','O')) THEN
4391:
4392: -- if the calendar type is other then resource then it will generate
4393: -- the schedule on the basis of the assignment asign to the calendar type
4394: ELSIF (p_schedule_basis_flag IN ('P','O')) THEN
4395: PA_SCHEDULE_UTILS.log_message(1, 'Schedule Basis Flag P or O');
4396: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_calendar_schedule ....');
4397:
4398: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4399: -- Calling the PVT API which will get the schedule on the basis of passed calendar id
4392: -- if the calendar type is other then resource then it will generate
4393: -- the schedule on the basis of the assignment asign to the calendar type
4394: ELSIF (p_schedule_basis_flag IN ('P','O')) THEN
4395: PA_SCHEDULE_UTILS.log_message(1, 'Schedule Basis Flag P or O');
4396: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_calendar_schedule ....');
4397:
4398: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4399: -- Calling the PVT API which will get the schedule on the basis of passed calendar id
4400: PA_SCHEDULE_PVT.get_calendar_schedule(p_calendar_id,
4406: x_msg_data
4407: );
4408: END IF;
4409:
4410: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API get_calendar_schedule ....');
4411: END IF;
4412:
4413:
4414: IF p_sum_tasks_flag = 'Y' then
4433: END IF;
4434:
4435:
4436:
4437: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4438: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4439: -- updating the passed schedule table of record for creating the schedule for staffed assignment
4440: PA_SCHEDULE_UTILS.update_sch_rec_tab(l_new_schedule_tab,
4441: P_project_id => p_project_id,
4436:
4437: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API update_sch_rec_tab ....');
4438: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4439: -- updating the passed schedule table of record for creating the schedule for staffed assignment
4440: PA_SCHEDULE_UTILS.update_sch_rec_tab(l_new_schedule_tab,
4441: P_project_id => p_project_id,
4442: p_schedule_type_code => 'STAFFED_ASSIGNMENT',
4443: p_calendar_id => p_calendar_id,
4444: p_assignment_id => p_assignment_id,
4448: x_msg_data => x_msg_data
4449: );
4450: END IF;
4451:
4452: PA_SCHEDULE_UTILS.log_message(1, 'SCH_REC',l_new_schedule_tab);
4453: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab ....');
4454: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
4455:
4456: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4449: );
4450: END IF;
4451:
4452: PA_SCHEDULE_UTILS.log_message(1, 'SCH_REC',l_new_schedule_tab);
4453: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab ....');
4454: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
4455:
4456: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4457: -- Inserting the record in PA_SCHEDULES table
4450: END IF;
4451:
4452: PA_SCHEDULE_UTILS.log_message(1, 'SCH_REC',l_new_schedule_tab);
4453: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API update_sch_rec_tab ....');
4454: PA_SCHEDULE_UTILS.log_message(1, 'Before Calling the API insert_rows ....');
4455:
4456: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4457: -- Inserting the record in PA_SCHEDULES table
4458: PA_SCHEDULE_PKG.insert_rows(
4473: x_msg_count =>x_msg_count ,
4474: x_msg_data =>x_msg_data );
4475: END IF;
4476:
4477: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4478: PA_SCHEDULE_UTILS.log_message(1,'End of the create_sasgn_schedule API ... ');
4479: x_return_status := l_x_return_status;
4480: EXCEPTION
4481: WHEN OTHERS THEN
4474: x_msg_data =>x_msg_data );
4475: END IF;
4476:
4477: PA_SCHEDULE_UTILS.log_message(1, 'After Calling the API insert_rows ....');
4478: PA_SCHEDULE_UTILS.log_message(1,'End of the create_sasgn_schedule API ... ');
4479: x_return_status := l_x_return_status;
4480: EXCEPTION
4481: WHEN OTHERS THEN
4482: PA_SCHEDULE_UTILS.log_message(1,'ERROR in create_sasgn_schedule API ..'||sqlerrm);
4478: PA_SCHEDULE_UTILS.log_message(1,'End of the create_sasgn_schedule API ... ');
4479: x_return_status := l_x_return_status;
4480: EXCEPTION
4481: WHEN OTHERS THEN
4482: PA_SCHEDULE_UTILS.log_message(1,'ERROR in create_sasgn_schedule API ..'||sqlerrm);
4483: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4484: x_msg_count := 1;
4485: x_msg_data := SQLERRM;
4486: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
4545: WHEN OTHERS THEN
4546: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4547: x_msg_count := 1;
4548: x_msg_data := SQLERRM;
4549: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_UTILS',
4550: p_procedure_name => 'delete_asgn_schedules');
4551: raise;
4552:
4553: END delete_asgn_schedules;
4609: FROM pa_project_assignments
4610: WHERE assignment_id = p_assignment_id;
4611:
4612: BEGIN
4613: PA_SCHEDULE_UTILS.log_message(1,'Start of the update_sch_wf_success API');
4614: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
4615:
4616: l_record_version_number := p_record_version_number;
4617:
4656: END LOOP;
4657: CLOSE C1;
4658:
4659: IF l_loop_thru_record ='N' THEN
4660: PA_SCHEDULE_UTILS.log_message(1,'Change status API called once');
4661:
4662: SELECT min(start_date), max(end_date)
4663: INTO l_start_date, l_end_date
4664: FROM pa_schedules
4663: INTO l_start_date, l_end_date
4664: FROM pa_schedules
4665: where assignment_id= p_assignment_id;
4666:
4667: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status');
4668: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
4669: p_status_code => l_first_status,
4670: p_status_type => l_status_type,
4671: x_wf_success_status_code => l_next_status_code,
4672: x_wf_failure_status_code => l_temp_status_code,
4673: x_return_status => l_x_return_status,
4674: x_error_message_code => x_msg_data) ;
4675: if (l_next_status_code <> l_first_status) then
4676: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status');
4677:
4678: PA_SCHEDULE_PUB.change_status(
4679: p_record_version_number => l_record_version_number,
4680: p_project_id => l_project_id,
4698: ELSE
4699: --Code change ends for bug 2279209
4700:
4701: FOR rec IN C1 LOOP
4702: PA_SCHEDULE_UTILS.log_message(1, 'In loop: ' || rec.start_date ||
4703: ' ' || rec.end_date);
4704: -- Call Ramesh's API to get next success status.
4705: -- For now, make them the same.
4706: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
4711: x_return_status => l_x_return_status,
4712: x_error_message_code => x_msg_data) ;
4713:
4714: if (l_next_status_code <> rec.status_code) then
4715: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status');
4716: PA_SCHEDULE_PUB.change_status(
4717: p_record_version_number => l_record_version_number,
4718: p_project_id => l_project_id,
4719: p_calendar_id => l_calendar_id,
4766: l_first_start_date := l_start_date;
4767: END IF;
4768:
4769: IF l_first_status <> l_status_code THEN
4770: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status');
4771: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
4772: p_status_code => l_first_status,
4773: p_status_type => l_status_type,
4774: x_wf_success_status_code => l_next_status_code,
4776: x_return_status => l_x_return_status,
4777: x_error_message_code => x_msg_data);
4778:
4779: IF (l_next_status_code <> l_first_status) THEN
4780: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status');
4781:
4782: PA_SCHEDULE_PUB.change_status(
4783: p_record_version_number => l_record_version_number,
4784: p_project_id => l_project_id,
4803: END IF;
4804: l_last_end_date := l_end_date;
4805:
4806: IF C1%ROWCOUNT = l_count THEN
4807: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status for last record');
4808: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
4809: p_status_code => l_first_status,
4810: p_status_type => l_status_type,
4811: x_wf_success_status_code => l_next_status_code,
4813: x_return_status => l_x_return_status,
4814: x_error_message_code => x_msg_data);
4815:
4816: IF (l_next_status_code <> l_first_status) THEN
4817: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status last record');
4818:
4819: PA_SCHEDULE_PUB.change_status(
4820: p_record_version_number => l_record_version_number,
4821: p_project_id => l_project_id,
4844:
4845:
4846: -- If records exist in pa_schedules_history with last_approved_flag
4847: -- set the flag to 'N'
4848: PA_SCHEDULE_UTILS.log_message(1, 'Setting last approved flag to N');
4849: update pa_schedules_history
4850: set last_approved_flag = 'N'
4851: where assignment_id = p_assignment_id
4852: and last_approved_flag = 'Y';
4850: set last_approved_flag = 'N'
4851: where assignment_id = p_assignment_id
4852: and last_approved_flag = 'Y';
4853:
4854: PA_SCHEDULE_UTILS.log_message(1,'End of the update_sch_wf_success API ... '
4855: || l_x_return_status);
4856:
4857: x_return_status := l_x_return_status;
4858: x_msg_count := FND_MSG_PUB.Count_Msg;
4872:
4873:
4874: EXCEPTION
4875: WHEN OTHERS THEN
4876: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_sch_wf_success API ..'||sqlerrm);
4877: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4878: x_msg_count := 1;
4879: x_msg_data := SQLERRM;
4880: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
4931: FROM pa_project_assignments
4932: WHERE assignment_id = p_assignment_id;
4933:
4934: BEGIN
4935: PA_SCHEDULE_UTILS.log_message(1,'Start of the update_sch_wf_failure API');
4936: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
4937:
4938: l_record_version_number := p_record_version_number;
4939:
5015: l_first_start_date := l_start_date;
5016: END IF;
5017:
5018: IF l_first_status <> l_status_code THEN
5019: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status');
5020: PA_PROJECT_STUS_UTILS.get_wf_success_failure_status (
5021: p_status_code => l_first_status,
5022: p_status_type => l_status_type,
5023: x_wf_success_status_code => l_temp_status_code,
5025: x_return_status => l_x_return_status,
5026: x_error_message_code => x_msg_data);
5027:
5028: IF (l_next_status_code <> l_first_status) THEN
5029: PA_SCHEDULE_UTILS.log_message(1, 'Calling change_status');
5030:
5031: PA_SCHEDULE_PUB.change_status(
5032: p_record_version_number => l_record_version_number,
5033: p_project_id => l_project_id,
5052: END IF;
5053: l_last_end_date := l_end_date;
5054:
5055: IF C1%ROWCOUNT = l_count THEN
5056: PA_SCHEDULE_UTILS.log_message(1,'Call API to get next status for last record');
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_temp_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 last record');
5067:
5068: PA_SCHEDULE_PUB.change_status(
5069: p_record_version_number => l_record_version_number,
5070: p_project_id => l_project_id,
5107: -- added for Bug Fix: 4537865
5108: End If;
5109:
5110: x_return_status := l_x_return_status;
5111: PA_SCHEDULE_UTILS.log_message(1,'End of the update_sch_wf_failure API ... ');
5112:
5113: EXCEPTION
5114: WHEN OTHERS THEN
5115: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_sch_wf_failure API ..'||sqlerrm);
5111: PA_SCHEDULE_UTILS.log_message(1,'End of the update_sch_wf_failure API ... ');
5112:
5113: EXCEPTION
5114: WHEN OTHERS THEN
5115: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_sch_wf_failure API ..'||sqlerrm);
5116: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5117: x_msg_count := 1;
5118: x_msg_data := SQLERRM;
5119: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5152: where assignment_id = p_assignment_id
5153: and last_approved_flag = 'Y';
5154:
5155: BEGIN
5156: PA_SCHEDULE_UTILS.log_message(1,'Start of the revert_to_last_approved API');
5157: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
5158:
5159: l_index := 0;
5160: for rec in c1 loop
5224: -- added for Bug fix: 4537865
5225: End If;
5226:
5227: x_return_status := l_x_return_status;
5228: PA_SCHEDULE_UTILS.log_message(1,'End of the revert_to_last_approved API ... ');
5229:
5230: EXCEPTION
5231: WHEN OTHERS THEN
5232: PA_SCHEDULE_UTILS.log_message(1,'ERROR in revert_to_last_approved API ..'||sqlerrm);
5228: PA_SCHEDULE_UTILS.log_message(1,'End of the revert_to_last_approved API ... ');
5229:
5230: EXCEPTION
5231: WHEN OTHERS THEN
5232: PA_SCHEDULE_UTILS.log_message(1,'ERROR in revert_to_last_approved API ..'||sqlerrm);
5233: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5234: x_msg_count := 1;
5235: x_msg_data := SQLERRM;
5236: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5279: from pa_schedules
5280: where assignment_id = p_assignment_id;
5281:
5282: BEGIN
5283: PA_SCHEDULE_UTILS.log_message(1,'Start of the update_history_table API');
5284: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
5285:
5286: -- If there are no records in pa_schedules_history with change_id
5287: -- then update table.
5332: -- added for bug fix: 4537865
5333: End If;
5334:
5335: x_return_status := l_x_return_status;
5336: PA_SCHEDULE_UTILS.log_message(1,'End of the update_history_table API ... ');
5337:
5338: EXCEPTION
5339: WHEN OTHERS THEN
5340: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_history_table API ..'||sqlerrm);
5336: PA_SCHEDULE_UTILS.log_message(1,'End of the update_history_table API ... ');
5337:
5338: EXCEPTION
5339: WHEN OTHERS THEN
5340: PA_SCHEDULE_UTILS.log_message(1,'ERROR in update_history_table API ..'||sqlerrm);
5341: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5342: x_msg_count := 1;
5343: x_msg_data := SQLERRM;
5344: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5915: WHERE item_type = p_item_type
5916: AND item_key = p_item_key;
5917:
5918: BEGIN
5919: PA_SCHEDULE_UTILS.debug('Entering check_overcommitment_mult');
5920: x_return_status := FND_API.G_RET_STS_SUCCESS;
5921:
5922: FOR v_c1 IN c1 LOOP
5923: IF v_c1.assignment_id IS NOT NULL THEN
5920: x_return_status := FND_API.G_RET_STS_SUCCESS;
5921:
5922: FOR v_c1 IN c1 LOOP
5923: IF v_c1.assignment_id IS NOT NULL THEN
5924: PA_SCHEDULE_UTILS.debug('v_c1.assignment_id = '|| v_c1.assignment_id);
5925: SELECT resource_id, start_date, end_date
5926: INTO l_resource_id, l_start_date, l_end_date
5927: FROM pa_project_assignments
5928: WHERE assignment_id = v_c1.assignment_id;
5925: SELECT resource_id, start_date, end_date
5926: INTO l_resource_id, l_start_date, l_end_date
5927: FROM pa_project_assignments
5928: WHERE assignment_id = v_c1.assignment_id;
5929: PA_SCHEDULE_UTILS.debug('l_resource_id = '|| l_resource_id);
5930:
5931: IF G_AVAILABILITY_CAL_PERIOD = 'DAILY' THEN
5932: select distinct fi.assignment_id,
5933: decode (mass.assignment_id, null, 'N', 'Y') intra_txn_conflict_flag
6005: x_msg_data => x_msg_data);
6006:
6007:
6008: ELSIF G_AVAILABILITY_CAL_PERIOD = 'WEEKLY' THEN
6009: PA_SCHEDULE_UTILS.debug('Entering check_overcommitment_mult: WEEKLY');
6010:
6011: select distinct fi.assignment_id,
6012: decode (mass.assignment_id, null, 'N', 'Y') intra_txn_flag
6013: BULK COLLECT INTO l_assignment_id_tbl, l_intra_txn_conflict_flag_tbl
6075: and a.wf_success_status_code = b.project_status_code
6076: and b.project_system_status_code = 'STAFFED_ASGMT_CONF';
6077:
6078:
6079: PA_SCHEDULE_UTILS.debug('Before check_self_conflict');
6080:
6081: -- Check self conflict.
6082: check_self_conflict(p_assignment_id => v_c1.assignment_id,
6083: p_resource_id => l_resource_id,
6087: x_return_status => x_return_status,
6088: x_msg_count => x_msg_count,
6089: x_msg_data => x_msg_data);
6090:
6091: PA_SCHEDULE_UTILS.debug('After check_self_conflict');
6092:
6093: END IF;
6094:
6095: -- Insert the self conflicting assignment_id if applicable.
6106: -- l_intra_txn_conflict_flag_tbl(1) := 'N';
6107: END IF;
6108: END IF;
6109:
6110: PA_SCHEDULE_UTILS.debug('Before insert_rows into conflict history');
6111: IF l_assignment_id_tbl.COUNT > 0 THEN
6112: PA_ASGN_CONFLICT_HIST_PKG.insert_rows(p_conflict_group_id => l_conflict_group_id,
6113: p_assignment_id => v_c1.assignment_id,
6114: p_conflict_assignment_id_tbl => l_assignment_id_tbl,
6127: END IF;
6128: -- added for bug fix: 4537865
6129:
6130: END IF;
6131: PA_SCHEDULE_UTILS.debug('After insert_rows into conflict history');
6132:
6133: END IF;
6134: END LOOP;
6135:
6258: ELSE
6259: x_self_conflict_flag := 'Y';
6260: END IF;
6261: CLOSE c1;
6262: PA_SCHEDULE_UTILS.debug('daily: x_self_conflict_flag = ' || x_self_conflict_flag);
6263: ELSIF G_AVAILABILITY_CAL_PERIOD = 'WEEKLY' THEN
6264: -- Bug 2288823: check_self_conflict for the period from the week start date of
6265: -- p_start_date and the week end date of p_end_date for WEEKLY profile.
6266: l_week_start_date := PA_TIMELINE_UTIL.get_week_end_date(p_org_id => NULL,
6274: ELSE
6275: x_self_conflict_flag := 'Y';
6276: END IF;
6277: CLOSE c2;
6278: PA_SCHEDULE_UTILS.debug('weekly: x_self_conflict_flag = ' || x_self_conflict_flag);
6279: END IF;
6280:
6281:
6282: EXCEPTION
6373:
6374: BEGIN
6375: x_return_status := FND_API.G_RET_STS_SUCCESS;
6376:
6377: PA_SCHEDULE_UTILS.debug('p_conflict_group_id = ' || p_conflict_group_id);
6378:
6379: OPEN c1;
6380: FETCH c1 INTO v_c1;
6381: IF c1%NOTFOUND THEN
6384: l_resolve_conflict_action_code := v_c1.resolve_conflicts_action_code;
6385: END IF;
6386: CLOSE c1;
6387:
6388: PA_SCHEDULE_UTILS.debug('l_resolve_conflict_action_code = ' || l_resolve_conflict_action_code);
6389:
6390: -- Processing for action code 'KEEP_CONFLICTS'
6391: IF l_resolve_conflict_action_code = 'KEEP_CONFLICTS' THEN
6392: PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
6401: -- process. If yes, return status 'E', populate the error stack with the
6402: -- error message.
6403: FOR v_c3 IN c3 LOOP
6404: IF v_c3.locking_flag = 'Y' THEN
6405: pa_schedule_utils.log_message(1, 'Remove conflicts failed due to assignments locking');
6406: RAISE l_remove_conflicts_failed;
6407: END IF;
6408: END LOOP;
6409:
6498:
6499: IF l_assignment_id_tbl.COUNT > 0 THEN
6500: l_count := 0;
6501: l_cur_item_date := l_item_date_tbl(l_item_date_tbl.FIRST)-1;
6502: --PA_SCHEDULE_UTILS.debug ('l_cur_item_date = '|| l_cur_item_date);
6503: l_cur_week_end_date := l_week_end_date_tbl(l_week_end_date_tbl.FIRST);
6504: --PA_SCHEDULE_UTILS.debug ('l_cur_week_end_date = '|| l_cur_week_end_date);
6505:
6506: FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
6500: l_count := 0;
6501: l_cur_item_date := l_item_date_tbl(l_item_date_tbl.FIRST)-1;
6502: --PA_SCHEDULE_UTILS.debug ('l_cur_item_date = '|| l_cur_item_date);
6503: l_cur_week_end_date := l_week_end_date_tbl(l_week_end_date_tbl.FIRST);
6504: --PA_SCHEDULE_UTILS.debug ('l_cur_week_end_date = '|| l_cur_week_end_date);
6505:
6506: FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
6507: --PA_SCHEDULE_UTILS.debug ('Entering l_assignment_id_tbl LOOP');
6508: --PA_SCHEDULE_UTILS.debug ('j = ' || j);
6503: l_cur_week_end_date := l_week_end_date_tbl(l_week_end_date_tbl.FIRST);
6504: --PA_SCHEDULE_UTILS.debug ('l_cur_week_end_date = '|| l_cur_week_end_date);
6505:
6506: FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
6507: --PA_SCHEDULE_UTILS.debug ('Entering l_assignment_id_tbl LOOP');
6508: --PA_SCHEDULE_UTILS.debug ('j = ' || j);
6509:
6510: -- When starting a new week.
6511: IF l_week_end_date_tbl(j) <> l_cur_week_end_date AND l_cur_work_pattern_tbl.COUNT > 0 THEN
6504: --PA_SCHEDULE_UTILS.debug ('l_cur_week_end_date = '|| l_cur_week_end_date);
6505:
6506: FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
6507: --PA_SCHEDULE_UTILS.debug ('Entering l_assignment_id_tbl LOOP');
6508: --PA_SCHEDULE_UTILS.debug ('j = ' || j);
6509:
6510: -- When starting a new week.
6511: IF l_week_end_date_tbl(j) <> l_cur_week_end_date AND l_cur_work_pattern_tbl.COUNT > 0 THEN
6512: --PA_SCHEDULE_UTILS.debug('New week: l_week_end_date_tbl(j) = '||l_week_end_date_tbl(j));
6508: --PA_SCHEDULE_UTILS.debug ('j = ' || j);
6509:
6510: -- When starting a new week.
6511: IF l_week_end_date_tbl(j) <> l_cur_week_end_date AND l_cur_work_pattern_tbl.COUNT > 0 THEN
6512: --PA_SCHEDULE_UTILS.debug('New week: l_week_end_date_tbl(j) = '||l_week_end_date_tbl(j));
6513: l_cur_week_end_date := l_week_end_date_tbl(j);
6514: -- Insert l_cur_work_pattern_tbl into l_work_pattern_tbl
6515: insert_work_pattern_tab(p_cur_work_pattern_tbl => l_cur_work_pattern_tbl,
6516: x_work_pattern_tbl => l_work_pattern_tbl,
6523: END IF;
6524:
6525: -- When starting a new item_date.
6526: IF l_item_date_tbl(j) <> l_cur_item_date THEN
6527: --PA_SCHEDULE_UTILS.debug('New item date: l_item_date_tbl(j) = '||l_item_date_tbl(j));
6528: -- Remove the overcommitment quantity for all the assignments
6529: -- on the current item date.
6530: IF l_count > 0 THEN
6531: update_work_pattern_record(p_overcom_quantity => l_cur_overcom_quantity,
6553: x_msg_data => x_msg_data);
6554:
6555: -- When starting a new assignment on the same item_date.
6556: ELSIF l_item_date_tbl(j) = l_cur_item_date AND l_assignment_id_tbl(j) <> l_cur_assignment_id THEN
6557: --PA_SCHEDULE_UTILS.debug('New assignment: l_assignment_id_tbl(j) = '||l_assignment_id_tbl(j));
6558: l_count := l_count +1;
6559: insert_work_pattern_record(p_assignment_id => l_assignment_id_tbl(j),
6560: p_item_quantity => l_item_quantity_tbl(j),
6561: p_item_date => l_item_date_tbl(j),
6597:
6598: -- Update schedules for those conflicting assignments based on the work
6599: -- pattern information stored in l_work_pattern_tbl.
6600: FOR v_c3 IN c3 LOOP
6601: PA_SCHEDULE_UTILS.debug ('Entering c3 loop');
6602: -- 2167889: null instead of record_version_number
6603: SELECT null, project_id, calendar_id, assignment_type,start_date, end_date
6604: INTO l_record_version_number, l_project_id, l_calendar_id, l_assignment_type, l_asgn_start_date, l_asgn_end_date
6605: FROM pa_project_assignments
6603: SELECT null, project_id, calendar_id, assignment_type,start_date, end_date
6604: INTO l_record_version_number, l_project_id, l_calendar_id, l_assignment_type, l_asgn_start_date, l_asgn_end_date
6605: FROM pa_project_assignments
6606: WHERE assignment_id = v_c3.conflict_assignment_id;
6607: PA_SCHEDULE_UTILS.debug ('l_work_pattern_tbl.COUNT = '|| l_work_pattern_tbl.COUNT);
6608: FOR i IN l_work_pattern_tbl.FIRST .. l_work_pattern_tbl.LAST LOOP
6609: IF l_work_pattern_tbl(i).assignment_id = v_c3.conflict_assignment_id THEN
6610:
6611: -- Calculate the start_date and end_date for the updating period.
6629: ELSE
6630: l_end_date := l_conflict_end_date;
6631: END IF;
6632: END IF;
6633: PA_SCHEDULE_UTILS.debug('Before Change Work Pattern');
6634: PA_SCHEDULE_UTILS.debug('l_start_date = '|| l_start_date);
6635: PA_SCHEDULE_UTILS.debug('l_end_date = '|| l_end_date);
6636:
6637: IF l_work_pattern_tbl(i).monday_hours = -99 THEN
6630: l_end_date := l_conflict_end_date;
6631: END IF;
6632: END IF;
6633: PA_SCHEDULE_UTILS.debug('Before Change Work Pattern');
6634: PA_SCHEDULE_UTILS.debug('l_start_date = '|| l_start_date);
6635: PA_SCHEDULE_UTILS.debug('l_end_date = '|| l_end_date);
6636:
6637: IF l_work_pattern_tbl(i).monday_hours = -99 THEN
6638: l_work_pattern_tbl(i).monday_hours := NULL;
6631: END IF;
6632: END IF;
6633: PA_SCHEDULE_UTILS.debug('Before Change Work Pattern');
6634: PA_SCHEDULE_UTILS.debug('l_start_date = '|| l_start_date);
6635: PA_SCHEDULE_UTILS.debug('l_end_date = '|| l_end_date);
6636:
6637: IF l_work_pattern_tbl(i).monday_hours = -99 THEN
6638: l_work_pattern_tbl(i).monday_hours := NULL;
6639: END IF;
6655: IF l_work_pattern_tbl(i).sunday_hours = -99 THEN
6656: l_work_pattern_tbl(i).sunday_hours := NULL;
6657: END IF;
6658:
6659: PA_SCHEDULE_UTILS.debug('work_pattern = '||
6660: l_work_pattern_tbl(i).monday_hours||';'||
6661: l_work_pattern_tbl(i).tuesday_hours||';'||
6662: l_work_pattern_tbl(i).wednesday_hours||';'||
6663: l_work_pattern_tbl(i).thursday_hours||';'||
6686: x_return_status => x_return_status,
6687: x_msg_count => x_msg_count,
6688: x_msg_data => x_msg_data );
6689:
6690: PA_SCHEDULE_UTILS.debug('After Change Work Pattern');
6691: END IF;
6692: END LOOP;
6693:
6694: -- Update pa_assignment_conflict_hist table.
6791: ELSIF l_week_day = 'SUN' THEN
6792: x_work_pattern_tbl(l_count).sunday_hours := p_item_quantity;
6793: END IF;
6794:
6795: PA_SCHEDULE_UTILS.debug('After insert_work_pattern_record: work_pattern = '||
6796: x_work_pattern_tbl(l_count).monday_hours||';'||
6797: x_work_pattern_tbl(l_count).tuesday_hours||';'||
6798: x_work_pattern_tbl(l_count).wednesday_hours||';'||
6799: x_work_pattern_tbl(l_count).thursday_hours||';'||
6903: x_work_pattern_tbl(j).sunday_hours:=0;
6904: END IF;
6905: END IF;
6906:
6907: PA_SCHEDULE_UTILS.debug('After update_work_pattern_record: work_pattern = '||
6908: x_work_pattern_tbl(j).monday_hours||';'||
6909: x_work_pattern_tbl(j).tuesday_hours||';'||
6910: x_work_pattern_tbl(j).wednesday_hours||';'||
6911: x_work_pattern_tbl(j).thursday_hours||';'||
6953: -- Otherwise, insert a new record.
6954: FOR i IN x_work_pattern_tbl.FIRST .. x_work_pattern_tbl.LAST LOOP
6955: 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
6956:
6957: PA_SCHEDULE_UTILS.debug('Inside insert table: x_work_pattern_tbl(i).end_date = '|| x_work_pattern_tbl(i).end_date);
6958:
6959: IF (x_work_pattern_tbl(i).monday_hours = p_cur_work_pattern_tbl(j).monday_hours
6960: AND x_work_pattern_tbl(i).tuesday_hours = p_cur_work_pattern_tbl(j).tuesday_hours
6961: AND x_work_pattern_tbl(i).wednesday_hours = p_cur_work_pattern_tbl(j).wednesday_hours
7147: FROM DUAL;
7148:
7149: l_conflict_proj_id := v_c2.project_id;
7150: l_conflict_proj_mgr_id := PA_PROJECTS_MAINT_UTILS.get_project_manager(v_c2.project_id);
7151: PA_SCHEDULE_UTILS.debug('l_conflict_proj_mgr_id = '||l_conflict_proj_mgr_id);
7152: -- Send notifications to conflicting project managers.
7153: IF l_conflict_proj_mgr_id IS NOT NULL THEN
7154:
7155: WF_DIRECTORY.getusername
7157: p_orig_system_id => l_conflict_proj_mgr_id,
7158: p_name => l_conflict_mgr_user_name,
7159: p_display_name => l_conflict_mgr_display_name);
7160:
7161: PA_SCHEDULE_UTILS.debug('l_conflict_mgr_user_name = '|| l_conflict_mgr_user_name);
7162:
7163: -- Create the WF process
7164: wf_engine.CreateProcess ( ItemType => l_item_type,
7165: ItemKey => l_item_key,
7301: -- Send Self Conflicts notifications.
7302: OPEN c3;
7303: FETCH c3 INTO v_c3;
7304: IF (c3%FOUND) THEN
7305: PA_SCHEDULE_UTILS.debug('Send self overcommitment notifications');
7306: -- Create the WF process
7307: SELECT PA_PRM_WF_ITEM_KEY_S.nextval
7308: INTO l_item_key
7309: FROM DUAL;
7555: BEGIN
7556: x_return_status := FND_API.G_RET_STS_SUCCESS;
7557:
7558: FOR v_c1 IN c1 LOOP
7559: PA_SCHEDULE_UTILS.debug('Revert_Overcom_Txn_Items: v_c1.assignment_id = ' || v_c1.assignment_id);
7560: PA_ASSIGNMENT_APPROVAL_PUB.revert_to_last_approved(p_assignment_id => v_c1.assignment_id,
7561: x_return_status => x_return_status,
7562: x_msg_count => x_msg_count,
7563: x_msg_data => x_msg_data);
7560: PA_ASSIGNMENT_APPROVAL_PUB.revert_to_last_approved(p_assignment_id => v_c1.assignment_id,
7561: x_return_status => x_return_status,
7562: x_msg_count => x_msg_count,
7563: x_msg_data => x_msg_data);
7564: PA_SCHEDULE_UTILS.debug('Revert_Overcom_Txn_Items: After revert_to_last_approved');
7565: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7566: PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
7567: p_assignment_id => v_c1.assignment_id,
7568: p_processed_flag => 'Y',
7807: fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
7808:
7809: -- Start of Debugging Statements
7810: IF l_debug_mode = 'Y' THEN -- 4387388
7811: PA_SCHEDULE_UTILS.log_message(1, 'p_start_date: ' || p_start_date);
7812: PA_SCHEDULE_UTILS.log_message(1, 'p_end_date: ' || p_end_date);
7813: IF p_task_assignments_tbl IS NOT NULL THEN
7814: IF p_task_assignments_tbl.count > 0 THEN
7815: FOR i IN p_task_assignments_tbl.first .. p_task_assignments_tbl.last LOOP
7808:
7809: -- Start of Debugging Statements
7810: IF l_debug_mode = 'Y' THEN -- 4387388
7811: PA_SCHEDULE_UTILS.log_message(1, 'p_start_date: ' || p_start_date);
7812: PA_SCHEDULE_UTILS.log_message(1, 'p_end_date: ' || p_end_date);
7813: IF p_task_assignments_tbl IS NOT NULL THEN
7814: IF p_task_assignments_tbl.count > 0 THEN
7815: FOR i IN p_task_assignments_tbl.first .. p_task_assignments_tbl.last LOOP
7816: PA_SCHEDULE_UTILS.log_message(1, 'p_task_assignments_tbl('||i||'): ' || p_task_assignments_tbl(i));
7812: PA_SCHEDULE_UTILS.log_message(1, 'p_end_date: ' || p_end_date);
7813: IF p_task_assignments_tbl IS NOT NULL THEN
7814: IF p_task_assignments_tbl.count > 0 THEN
7815: FOR i IN p_task_assignments_tbl.first .. p_task_assignments_tbl.last LOOP
7816: PA_SCHEDULE_UTILS.log_message(1, 'p_task_assignments_tbl('||i||'): ' || p_task_assignments_tbl(i));
7817: END LOOP;
7818: END IF;
7819: END IF;
7820: IF p_schedule_tbl IS NOT NULL THEN
7819: END IF;
7820: IF p_schedule_tbl IS NOT NULL THEN
7821: IF p_schedule_tbl.count > 0 THEN
7822: FOR schedule_counter IN p_schedule_tbl.first .. p_schedule_tbl.last LOOP
7823: PA_SCHEDULE_UTILS.log_message(1,
7824: p_schedule_tbl(schedule_counter).start_date || '|' ||
7825: p_schedule_tbl(schedule_counter).end_date || '|' ||
7826: round(p_schedule_tbl(schedule_counter).monday_hours,2) || '|' ||
7827: round(p_schedule_tbl(schedule_counter).tuesday_hours,2) || '|' ||
8056: IF l_debug_mode = 'Y' THEN -- 4387388
8057: IF x_schedule_tbl IS NOT NULL THEN
8058: IF x_schedule_tbl.count > 0 THEN
8059: FOR schedule_counter IN x_schedule_tbl.first .. x_schedule_tbl.last LOOP
8060: PA_SCHEDULE_UTILS.log_message(1,
8061: x_schedule_tbl(schedule_counter).start_date || '|' ||
8062: x_schedule_tbl(schedule_counter).end_date || '|' ||
8063: round(x_schedule_tbl(schedule_counter).monday_hours,2) || '|' ||
8064: round(x_schedule_tbl(schedule_counter).tuesday_hours,2) || '|' ||