[Home] [Help]
92: l_st_dt_done := FALSE;
93: l_end_dt_done := FALSE;
94: l_x_sch_copy_done := FALSE;
95: -- store status success to track the error
96: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
97:
98: /* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
99:
100: -- FOR v_c1 IN C1 LOOP
177: END IF;
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:
197:
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:
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
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,
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);
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:
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
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:
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
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,
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);
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:
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
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;
261: l_out_schedule_rec(1).sunday_hours := 0.00;
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;
271:
272: EXCEPTION
273: WHEN OTHERS THEN
274: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
275: x_msg_count := 1;
276: x_msg_data := SQLERRM;
277: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
278: p_procedure_name => 'get_calendar_schedule');
401: BEGIN
402: l_st_dt_done := FALSE;
403: l_end_dt_done := FALSE;
404: l_x_sch_copy_done := FALSE;
405: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
406:
407: /* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
408:
409: -- FOR v_c1 IN C1 LOOP
534: l_J := l_out_schedule_rec.Last;
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,
544: x_msg_count,
545: x_msg_data);
546: END IF;
547:
548: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
549: /*bug 2335580 */
550: IF l_assignment_calendar_id IS NOT NULL THEN
551:
552: PA_Schedule_Pvt.get_calendar_schedule(l_assignment_calendar_id,
573:
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,
584: x_msg_data);
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,
601:
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,
612: x_msg_data);
613: END IF;
614:
615: /*Code added for bug 2335580 */
616: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
617: IF l_assignment_calendar_id IS NOT NULL THEN
618:
619: PA_Schedule_Pvt.get_calendar_schedule(l_assignment_calendar_id,
620: l_out_schedule_rec(l_J).end_date+1,
642: END IF; --l_assignment_calendar_id is not null
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,
652: x_msg_count,
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,
679:
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,
694:
695: x_return_status := l_x_return_status;
696: EXCEPTION
697: WHEN OTHERS THEN
698: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
699: x_msg_count := 1;
700: x_msg_data := SQLERRM;
701: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
702: p_procedure_name => 'get_assignment_schedule');
804: BEGIN
805: l_st_dt_done := FALSE;
806: l_end_dt_done := FALSE;
807: l_x_sch_copy_done := FALSE;
808: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
809:
810: /* Bug - 1846658- Following lines are commented to incorporate the BULK SELECT to enhance the performance*/
811:
812: -- FOR v_c1 IN C1 LOOP
890: l_curr_schedule_rec(1).Saturday_hours := l_Saturday_hours_tbl(i);
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;
909: x_return_status := l_x_return_status;
910: EXCEPTION
911: WHEN OTHERS THEN
912:
913: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
914: x_msg_count := 1;
915: x_msg_data := SQLERRM;
916: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
917: p_procedure_name => 'get_assignment_schedule');
1028: l_st_dt_done := FALSE;
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
1043: l_resource_id := -99;
1044: END;
1045:
1046: -- Calling resource API that will return the resource id
1047: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1048: PA_RESOURCE_UTILS.get_crm_res_id( p_project_player_id => p_source_id,
1049: p_resource_id => NULL,
1050: x_jtf_resource_id => l_t_resource_id,
1051: x_return_status => l_x_return_status,
1056: END IF;
1057: ELSIF ( p_source_type = 'PA_RESOURCE_ID') THEN
1058: l_resource_id := p_source_id;
1059: -- Calling resource API That will return the resource id
1060: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1061: PA_RESOURCE_UTILS.get_crm_res_id( p_project_player_id => NULL,
1062: p_resource_id => p_source_id,
1063: x_jtf_resource_id => l_t_resource_id,
1064: x_return_status => l_x_return_status,
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);
1077:
1113:
1114: */
1115:
1116: -- Get default calendar using organization id.
1117: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1118: pa_resource_utils.get_org_defaults(
1119: p_organization_id => l_resource_organization_id,
1120: x_default_ou => l_resource_ou_id,
1121: x_default_cal_id => l_calendar_id,
1147: END IF;
1148:
1149: -- Calling the get calendar schedule procedure which will bring
1150: -- the schedule for the specified calendar id
1151: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1152: get_calendar_schedule(l_calendar_id,
1153: l_tc_start_date,
1154: l_tc_end_date,
1155: l_cur_schedule_rec,
1163: IF (p_start_date is not null) then
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,
1183: l_J := l_out_schedule_rec.Last;
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:
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
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);
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;
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:
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
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:
1211: END IF;
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:
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
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:
1232: 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:
1243: END IF;
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;
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:
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
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,
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,
1332: l_out_schedule_rec,l_x_return_status,x_msg_count,x_msg_data);
1333: END IF;
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,
1357: l_t_end_date,
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,
1369: x_msg_data);
1370: END IF;
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:
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
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);
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;
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:
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
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:
1410: END IF;
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:
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
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:
1431: 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:
1442: END IF;
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;
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;
1749: x_msg_data := 'l_invalid_source_id';
1750: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
1751: p_procedure_name => 'get_resource_schedule');
1752: raise;
1753: WHEN OTHERS THEN
1754: x_msg_count := 1;
1755: x_msg_data := SQLERRM;
1756: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1757: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
1758: p_procedure_name => 'get_resource_schedule');
1759: raise;
1760:
1780: l_upd_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp; -- variable used for storing the records kept for updation
1781: l_ins_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp; -- variable used for storing the records kept for insertion
1782: BEGIN
1783: -- storing status for tracking the error
1784: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1785: -- checking if the passing records is empty or not
1786: IF (p_chg_sch_record_tab.count = 0 ) THEN
1787: RAISE l_empty_tab_record;
1788: ELSE
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,
1806: l_x_return_status,x_msg_count,x_msg_data);
1807: END IF;
1808:
1817:
1818:
1819:
1820: -- Applying the changes according to their status i.e. insert,update or delete
1821: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1822: PA_SCHEDULE_PKG.Insert_Rows(l_ins_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1823: END IF;
1824:
1825: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1821: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1822: PA_SCHEDULE_PKG.Insert_Rows(l_ins_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1823: END IF;
1824:
1825: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1826: PA_SCHEDULE_PKG.Update_Rows(l_upd_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1827: END IF;
1828:
1829: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1825: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1826: PA_SCHEDULE_PKG.Update_Rows(l_upd_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1827: END IF;
1828:
1829: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1830: PA_SCHEDULE_PKG.Delete_Rows(p_del_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1831: END IF;
1832:
1833: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1829: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1830: PA_SCHEDULE_PKG.Delete_Rows(p_del_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
1831: END IF;
1832:
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 .... ');
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;
1842: NULL;
1843: WHEN OTHERS THEN
1844: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1845: x_msg_count := 1;
1840: WHEN l_empty_tab_record THEN
1841: x_return_status := FND_API.G_RET_STS_SUCCESS;
1842: NULL;
1843: WHEN OTHERS THEN
1844: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1845: x_msg_count := 1;
1846: x_msg_data := SQLERRM;
1847: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
1848: p_procedure_name => 'apply_schedule_change');
1873: l_t_calendar_id NUMBER;
1874: l_t_calendar_type VARCHAR2(30);
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:
1883: -- the duration
1884: IF (p_sch_except_record.exception_type_code = 'CHANGE_DURATION' OR
1885: p_sch_except_record.exception_type_code = 'SHIFT_DURATION') THEN
1886: -- Calling the procedure create_new_duration
1887: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1888: PA_SCHEDULE_PVT.create_new_duration(p_sch_except_record,
1889: p_sch_record,
1890: x_sch_record_tab,
1891: l_x_return_status,
1895: ELSIF (p_sch_except_record.exception_type_code = 'CHANGE_HOURS') THEN
1896: -- This procedure will create the new schedule for the given change hours exception it will create by
1897: -- changing the hours for the given period
1898: -- Calling the procedure create_new_hours
1899: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1900: PA_SCHEDULE_PVT.create_new_hours(p_sch_except_record,
1901: p_sch_record,
1902: x_sch_record_tab,
1903: l_x_return_status,
1907: ELSIF (p_sch_except_record.exception_type_code = 'CHANGE_WORK_PATTERN') THEN
1908: -- This procedure will create the new schedule for the given change work pattern exception it will create
1909: -- by just changing the work patern for the given period
1910: -- Calling the procedure create_new_pattern
1911: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1912: PA_SCHEDULE_PVT.create_new_pattern(p_sch_except_record,
1913: p_sch_record,
1914: x_sch_record_tab,
1915: l_x_return_status,
1919: ELSIF (p_sch_except_record.exception_type_code = 'CHANGE_STATUS') THEN
1920: -- This procedure will create the new schedule for the given change status exception it will create
1921: -- by changing the status for that period only
1922: -- Calling the procedure create_new_status
1923: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1924: PA_SCHEDULE_PVT.create_new_status(p_sch_except_record,
1925: p_sch_record,
1926: x_sch_record_tab,
1927: l_x_return_status,
1931: ELSIF (p_sch_except_record.exception_type_code = 'CHANGE_CALENDAR') THEN
1932: -- This procedure will create the new schedule for the given change calendar exception it will create
1933: -- by changing the calendar for that period only
1934: -- Calling the procedure create_new_calendar
1935: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1936: PA_SCHEDULE_PVT.create_new_calendar(p_sch_except_record,
1937: p_sch_record,
1938: x_sch_record_tab,
1939: l_x_return_status,
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',
1950: x_return_status => l_x_return_status,
1951: x_msg_count => x_msg_count,
1955: x_return_status := l_x_return_status;
1956:
1957: EXCEPTION
1958: WHEN OTHERS THEN
1959: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1960: x_msg_count := 1;
1961: x_msg_data := SQLERRM;
1962: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
1963: p_procedure_name => 'create_new_schedule');
1983: x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1984: )
1985: IS
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,
1985: IS
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,
1996: x_msg_data => x_msg_data);
1997: END IF;
1998:
1999: -- Calling the procedure get_calendar_schedule that will bring the new schedule of the passed calendar
2000: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2001: PA_SCHEDULE_PVT.get_calendar_schedule(p_sch_except_record.calendar_id,
2002: p_sch_except_record.start_date,
2003: p_sch_except_record.end_date,
2004: x_sch_record_tab,
2006: x_msg_count,
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,
2020: END IF;
2021: x_return_status := l_x_return_status;
2022: EXCEPTION
2023: WHEN OTHERS THEN
2024: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2025: x_msg_count := 1;
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');
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:
2065: -- For this hours code we will changed the previous schedule 's hours with the new passed
2102: INTO l_t_resource_id
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,
2112: x_msg_data => x_msg_data);
2113: END IF;
2114:
2115: -- Calling the procedure get_resource_schedule that will bring the schedule associated with the resource
2116: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2117: PA_SCHEDULE_PVT.get_resource_schedule(l_t_team_player_id,
2118: 'PA_PROJECT_PARTY_ID',
2119: p_sch_except_record.start_date,
2120: p_sch_except_record.end_date,
2130: INTO l_t_calendar_id
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,
2142:
2143:
2144: -- Calling the procedure get_calendar_schedule that will bring the schedule associated with the
2145: -- assignment on the basis of passed calendar id
2146: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2147: PA_SCHEDULE_PVT.get_calendar_schedule(l_t_calendar_id,
2148: p_sch_except_record.start_date,
2149: p_sch_except_record.end_date,
2150: x_sch_record_tab,
2206: INTO l_t_resource_id
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,
2216: x_msg_data => x_msg_data);
2217: END IF;
2218:
2219: -- Calling the procedure get_resource_schedule that will bring the schedule associated with the resource
2220: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2221: PA_SCHEDULE_PVT.get_resource_schedule(l_t_team_player_id,
2222: 'PA_PROJECT_PARTY_ID',
2223: p_sch_except_record.start_date,
2224: p_sch_except_record.end_date,
2240: ELSIF (p_sch_except_record.change_calendar_type_code = 'OTHER') THEN
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,
2253:
2254:
2255: -- Calling the procedure get_calendar_schedule that will bring the schedule associated with the
2256: -- assignment on the basis of passed calendar id
2257: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2258: PA_SCHEDULE_PVT.get_calendar_schedule(l_t_calendar_id,
2259: p_sch_except_record.start_date,
2260: p_sch_except_record.end_date,
2261: x_sch_record_tab,
2264: x_msg_data);
2265: END IF;
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,
2276:
2277: END IF; --IF (p_sch_except_record.change_hours_type_code = 'PERCENTAGE')
2278:
2279:
2280: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2281: -- Updating the pa schedules table
2282: PA_SCHEDULE_UTILS.update_sch_rec_tab(x_sch_record_tab,
2283: p_project_id => p_sch_except_record.project_id,
2284: p_schedule_type_code => p_sch_except_record.schedule_type_code,
2293:
2294: x_return_status := l_x_return_status;
2295: EXCEPTION
2296: WHEN OTHERS THEN
2297: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2298: x_msg_count := 1;
2299: x_msg_data := SQLERRM;
2300: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2301: p_procedure_name => 'create_new_hours');
2325: l_t_res_cal_percent NUMBER;
2326: l_t_calendar_id NUMBER;
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.
2340: WHERE assignment_id = p_sch_except_record.assignment_id;
2341:
2342: IF ( l_t_calendar_type = 'RESOURCE' ) THEN
2343: -- Calling the procedure get_resource_schedule which is assigned to the assignment
2344: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2345: PA_SCHEDULE_PVT.get_resource_schedule(l_t_team_player_id,
2346: 'PA_PROJECT_PARTY_ID',
2347: p_sch_except_record.start_date,
2348: p_sch_except_record.end_date,
2352: x_msg_data
2353: );
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,
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
2385: -- calendar type like assignment,project or others
2386: PA_SCHEDULE_PVT.get_calendar_schedule(p_sch_except_record.calendar_id,
2387: p_sch_except_record.start_date,
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,
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,
2411:
2412: x_return_status := l_x_return_status;
2413: EXCEPTION
2414: WHEN OTHERS THEN
2415: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2416: x_msg_count := 1;
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');
2489:
2490:
2491: BEGIN
2492: /*Added initialization for bug 6921728*/
2493: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2494:
2495: l_I := 1;
2496:
2497: FOR rec_sch IN csr_get_sch LOOP
2625: x_return_status := l_x_return_status ;
2626:
2627: EXCEPTION
2628: WHEN OTHERS THEN
2629: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2630: x_msg_count := 1;
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');
2658: )
2659: IS
2660:
2661: BEGIN
2662: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2663:
2664: x_sch_record_tab(1).start_date := p_sch_except_record.start_date;
2665: x_sch_record_tab(1).end_date := p_sch_except_record.end_date;
2666: x_sch_record_tab(1).Monday_hours := nvl(p_sch_except_record.Monday_hours, p_sch_record.Monday_hours);
2687:
2688: x_return_status := l_x_return_status;
2689: EXCEPTION
2690: WHEN OTHERS THEN
2691: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2692: x_msg_count := 1;
2693: x_msg_data := SQLERRM;
2694: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2695: p_procedure_name => 'create_new_pattern');
2717: )
2718: IS
2719: Status_change_not_allowed EXCEPTION;
2720: BEGIN
2721: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2722: x_sch_record_tab(1).start_date := p_sch_except_record.start_date;
2723: x_sch_record_tab(1).end_date := p_sch_except_record.end_date;
2724: x_sch_record_tab(1).assignment_status_code := p_sch_except_record.assignment_status_code;
2725:
2753: x_return_status := l_x_return_status;
2754: EXCEPTION
2755: WHEN Status_change_not_allowed THEN
2756: PA_UTILS.add_message('PA','PA_STATUS_CANT_CHANGE');
2757: x_return_status := FND_API.G_RET_STS_ERROR;
2758: x_msg_count := 1;
2759: x_msg_data := 'PA_STATUS_CANT_CHANGE';
2760:
2761: WHEN OTHERS THEN
2758: x_msg_count := 1;
2759: x_msg_data := 'PA_STATUS_CANT_CHANGE';
2760:
2761: WHEN OTHERS THEN
2762: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2763: x_msg_count := 1;
2764: x_msg_data := SQLERRM;
2765: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
2766: p_procedure_name => 'create_new_status');
2801: l_chg_exp_start_date DATE;
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;
2822: l_x_return_status,
2823: x_msg_count,
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,
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
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,
2898: x_msg_count,
2899: x_msg_data);
2900: END IF;
2901:
2902: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2903: -- Create the new schedule after applying the exception i.e after change the duration
2904: create_new_schedule( p_sch_except_record,
2905: l_temp_p_sch_record_tab(l_temp_first),
2906: l_out_tr_sch_rec_tab,
2908: x_msg_count,
2909: x_msg_data );
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,
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,
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,
2963: x_msg_data => x_msg_data );
2964: END IF;
2965:
2966: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
2967: create_new_schedule( l_temp_except_rec,
2968: l_temp_p_sch_record_tab(l_temp_first),
2969: l_out_tr_sch_rec_tab,
2970: l_x_return_status,
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,
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,
3017: l_temp_p_sch_record_tab,
3018: l_x_return_status,
3020: x_msg_data );
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,
3037:
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,
3047: x_msg_count,
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,
3057: x_msg_data => x_msg_data );
3058:
3059: END IF;
3060:
3061: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3062: create_new_schedule( l_temp_except_rec,
3063: l_temp_p_sch_record_tab(l_temp_first),
3064: l_temp_tr_sch_rec_tab,
3065: l_x_return_status,
3066: 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,
3079: END IF; -- end_date cond if */
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,
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,
3109: l_out_tr_sch_rec_tab.last,
3110: x_sch_record_tab,
3115:
3116: x_return_status := l_x_return_status;
3117: EXCEPTION
3118: WHEN OTHERS THEN
3119: x_return_status := FND_API.G_RET_STS_ERROR;
3120: x_msg_count := 1;
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');
3165: l_create_new_sch BOOLEAN;
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,
3244: l_temp_p_sch_record_tab(l_I).change_type_code := 'U';
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,
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,
3285: x_return_status => l_x_return_status,
3286: x_msg_count => x_msg_count,
3287: x_msg_data => x_msg_data );
3288: END IF;
3289:
3290: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3291: create_new_schedule( l_temp_except_rec,
3292: l_temp_p_sch_record_tab(l_I),
3293: l_temp_tr_sch_rec_tab,
3294: l_x_return_status,
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 ,
3303: l_temp_tr_sch_rec_tab.first,
3304: l_temp_tr_sch_rec_tab.last,
3305: l_out_tr_sch_rec_tab,
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
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,
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,
3339: l_out_tr_sch_rec_tab,
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,
3356: l_out_tr_sch_rec_tab,
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,
3376: l_out_tr_sch_rec_tab.last,
3377: x_sch_record_tab,
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;
3388: x_msg_count := 1;
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');
3442: l_t_multi_flag := 'N';
3443: l_change_flag := FALSE;
3444: l_t_assignment_id := chg_tr_sch_rec_tab(1).assignment_id;
3445: l_t_project_id := chg_tr_sch_rec_tab(1).project_id;
3446: l_x_return_status := FND_API.G_RET_STS_SUCCESS; -- Storing the status for tracking the error
3447:
3448: -- Loop for processing the exception records from exception table type variable for change duration */
3449: IF (sch_except_record_tab.count > 0 ) THEN
3450: FOR I IN sch_except_record_tab.first..sch_except_record_tab.last LOOP
3496: IF (l_change_flag) THEN
3497: PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
3498: p_record_version_number => p_record_version_number,
3499: p_assignment_id => l_t_assignment_id,
3500: p_start_date => nvl(l_t_start_date,FND_API.G_MISS_DATE),
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 =>
3497: PA_PROJECT_ASSIGNMENTS_PKG.Update_Row(
3498: p_record_version_number => p_record_version_number,
3499: p_assignment_id => l_t_assignment_id,
3500: p_start_date => nvl(l_t_start_date,FND_API.G_MISS_DATE),
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),
3519:
3520: -- If assignment record is successfully updated then call procedure to update pa project parties.
3521: --
3522: -- jmarques (1590046): Added STAFFED_ADMIN_ASSIGNMENT check to if statement.
3523: IF ( ( l_x_return_status = FND_API.G_RET_STS_SUCCESS ) AND
3524: ( (chg_tr_sch_rec_tab(l_J).schedule_type_code = 'STAFFED_ASSIGNMENT') OR
3525: (chg_tr_sch_rec_tab(l_J).schedule_type_code = 'STAFFED_ADMIN_ASSIGNMENT') )
3526: ) THEN
3527:
3586: X_MSG_DATA => x_msg_data );
3587:
3588: -- added for Bug fix: 4537865
3589:
3590: IF l_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3591: l_t_assignment_id := l_new_t_assignment_id;
3592: END IF;
3593: -- added for Bug fix: 4537865
3594:
3596:
3597: x_return_status := l_x_return_status;
3598: EXCEPTION
3599: WHEN l_empty_tab_record THEN
3600: x_return_status := FND_API.G_RET_STS_SUCCESS;
3601: NULL;
3602: WHEN OTHERS THEN
3603: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3604: x_msg_count := 1;
3599: WHEN l_empty_tab_record THEN
3600: x_return_status := FND_API.G_RET_STS_SUCCESS;
3601: NULL;
3602: WHEN OTHERS THEN
3603: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3604: x_msg_count := 1;
3605: x_msg_data := SQLERRM;
3606: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
3607: p_procedure_name => 'apply_assignment_change');
3678: ***/
3679:
3680: BEGIN
3681:
3682: x_return_status := FND_API.G_RET_STS_SUCCESS ;
3683:
3684:
3685: OPEN C_Periodic_dates1;
3686: FETCH C_Periodic_dates1 into x_min_start_date, x_max_end_date;
3688:
3689: /*Added for 4996210: If dates are null then return error status */
3690:
3691: IF x_min_start_date IS NULL OR x_max_end_date IS NULL THEN
3692: x_return_status := FND_API.G_RET_STS_ERROR;
3693: x_msg_count := 1;
3694: x_msg_data := 'Error while calling get_periodic_start_end';
3695: END IF;
3696:
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',
3708: p_procedure_name => 'get_periodic_start_end');
3761:
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:
3791: END IF;
3792:
3793: /*Bug 4996210 : If get_periodic_start_end returns error then raise error for all the resourcelist members for which the planned work quantity is null or zero*/
3794:
3795: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3796:
3797: BEGIN
3798: IF p_task_assignment_id_tbl.COUNT > 0 THEN
3799: SELECT ALIAS INTO l_alias_name FROM
3812:
3813: FND_MESSAGE.SET_NAME('PA','PA_PLAN_QTY_NULL');
3814: FND_MESSAGE.SET_TOKEN('TASK_ASSIG',l_alias_name);
3815: FND_MSG_PUB.add;
3816: RAISE FND_API.G_EXC_ERROR;
3817: END IF;
3818:
3819: /*End for 4996210 */
3820:
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,
3865: p_project_id => p_project_id,
3866: p_calendar_id => p_calendar_id,
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,
3883: l_x_return_status,
3884: x_msg_count,
3887: );
3888: END IF;
3889:
3890: -- Calling the Timeline api to build the timeline records for the assignment
3891: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
3892: PA_TIMELINE_PVT.CREATE_TIMELINE (p_assignment_id =>p_assignment_id ,
3893: x_return_status =>l_x_return_status ,
3894: x_msg_count =>x_msg_count ,
3895: x_msg_data =>x_msg_data );
3902: x_return_status := l_x_return_status;
3903: EXCEPTION
3904:
3905: /*Added for 4996210 */
3906: WHEN FND_API.G_EXC_ERROR THEN
3907: x_return_status := FND_API.G_RET_STS_ERROR;
3908: /*End 4996210 */
3909:
3910: WHEN OTHERS THEN
3903: EXCEPTION
3904:
3905: /*Added for 4996210 */
3906: WHEN FND_API.G_EXC_ERROR THEN
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);
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',
3916: p_procedure_name => 'create_opn_asg_schedule');
3957: l_current_sch_rec_tab PA_SCHEDULE_GLOB.ScheduleTabTyp;
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
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
4135: PA_TIMELINE_PVT.CREATE_TIMELINE (
4136: p_assignment_id =>l_assignment_id_tbl(l_assignment_id_tbl.FIRST).assignment_id,
4137: x_return_status =>l_x_return_status ,
4138: x_msg_count =>x_msg_count ,
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',
4220: p_procedure_name => 'create_opn_asg_schedule');
4283: BEGIN
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
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,
4339: p_resource_calendar_percent,
4340: l_x_return_status,
4356: from pa_project_assignments
4357: where assignment_id=p_open_assignment_id;
4358:
4359:
4360: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4361: -- Calling the PVT API which will get the schedule on the basis of passed assignment id
4362: --Added for 2335580
4363: -- To check for this date clause jraj 12/19/03..
4364:
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,
4401: l_start_date,
4402: l_end_date,
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,
4442: p_schedule_type_code => 'STAFFED_ASSIGNMENT',
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(
4459: l_new_schedule_tab,
4460: l_x_return_status,
4466:
4467: --jm_profiler.set_time('Create Schedule');
4468:
4469: -- Calling the Timeline api to build the timeline records for the assignment
4470: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
4471: PA_TIMELINE_PVT.CREATE_TIMELINE (p_assignment_id =>p_assignment_id ,
4472: x_return_status =>l_x_return_status ,
4473: x_msg_count =>x_msg_count ,
4474: x_msg_data =>x_msg_data );
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',
4487: p_procedure_name => 'create_stf_asg_schedule');
4495: -- Parameters Type Required Description
4496: -- P_Assignment_Id NUMBER YES it is assignment id used for deletion
4497: --
4498: PROCEDURE delete_asgn_schedules ( p_assignment_id IN NUMBER,
4499: p_perm_delete IN VARCHAR2 := FND_API.G_TRUE,
4500: p_change_id IN NUMBER := null,
4501: x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4502: x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
4503: x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
4504: IS
4505: BEGIN
4506:
4507: -- Storing the value for error tracking
4508: l_x_return_status := FND_API.G_RET_STS_SUCCESS;
4509:
4510: -- deleting the records from pa_schedules,pa_schedule_exceptions and pa_schedule_except_history */
4511:
4512: DELETE pa_schedules
4516: WHERE assignment_id = p_assignment_id;
4517:
4518: -- Delete entire exception history if p_perm_delete
4519: -- Otherwise, just delete exceptions with change_id >= p_change_id
4520: if FND_API.TO_BOOLEAN(p_perm_delete) then
4521: DELETE pa_schedule_except_history
4522: WHERE assignment_id = p_assignment_id;
4523: else
4524: DELETE pa_schedule_except_history
4526: and change_id >= p_change_id;
4527: end if;
4528:
4529: -- Delete entire schedules history if p_perm_delete.
4530: if FND_API.TO_BOOLEAN(p_perm_delete) then
4531: DELETE pa_schedules_history
4532: WHERE assignment_id = p_assignment_id;
4533: end if;
4534:
4542: x_return_status := l_x_return_status;
4543:
4544: EXCEPTION
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');
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:
4618: open c2;
4686: p_end_date => l_end_date,
4687: p_assignment_status_code => l_next_status_code,
4688: p_asgn_start_date => l_asgn_start_date,
4689: p_asgn_end_date => l_asgn_end_date,
4690: p_init_msg_list => FND_API.G_FALSE,
4691: p_save_to_hist => FND_API.G_FALSE,
4692: x_return_status => l_x_return_status,
4693: x_msg_count => x_msg_count,
4694: x_msg_data => x_msg_data);
4687: p_assignment_status_code => l_next_status_code,
4688: p_asgn_start_date => l_asgn_start_date,
4689: p_asgn_end_date => l_asgn_end_date,
4690: p_init_msg_list => FND_API.G_FALSE,
4691: p_save_to_hist => FND_API.G_FALSE,
4692: x_return_status => l_x_return_status,
4693: x_msg_count => x_msg_count,
4694: x_msg_data => x_msg_data);
4695: end if;
4724: p_end_date => rec.end_date,
4725: p_assignment_status_code => l_next_status_code,
4726: p_asgn_start_date => l_asgn_start_date,
4727: p_asgn_end_date => l_asgn_end_date,
4728: p_init_msg_list => FND_API.G_FALSE,
4729: p_save_to_hist => FND_API.G_FALSE,
4730: x_return_status => l_x_return_status,
4731: x_msg_count => x_msg_count,
4732: x_msg_data => x_msg_data);
4725: p_assignment_status_code => l_next_status_code,
4726: p_asgn_start_date => l_asgn_start_date,
4727: p_asgn_end_date => l_asgn_end_date,
4728: p_init_msg_list => FND_API.G_FALSE,
4729: p_save_to_hist => FND_API.G_FALSE,
4730: x_return_status => l_x_return_status,
4731: x_msg_count => x_msg_count,
4732: x_msg_data => x_msg_data);
4733:
4790: p_end_date => l_last_end_date,
4791: p_assignment_status_code => l_next_status_code,
4792: p_asgn_start_date => l_asgn_start_date,
4793: p_asgn_end_date => l_asgn_end_date,
4794: p_init_msg_list => FND_API.G_FALSE,
4795: p_save_to_hist => FND_API.G_FALSE,
4796: x_return_status => l_x_return_status,
4797: x_msg_count => x_msg_count,
4798: x_msg_data => x_msg_data);
4791: p_assignment_status_code => l_next_status_code,
4792: p_asgn_start_date => l_asgn_start_date,
4793: p_asgn_end_date => l_asgn_end_date,
4794: p_init_msg_list => FND_API.G_FALSE,
4795: p_save_to_hist => FND_API.G_FALSE,
4796: x_return_status => l_x_return_status,
4797: x_msg_count => x_msg_count,
4798: x_msg_data => x_msg_data);
4799: END IF;
4827: p_end_date => l_last_end_date,
4828: p_assignment_status_code => l_next_status_code,
4829: p_asgn_start_date => l_asgn_start_date,
4830: p_asgn_end_date => l_asgn_end_date,
4831: p_init_msg_list => FND_API.G_FALSE,
4832: p_save_to_hist => FND_API.G_FALSE,
4833: x_return_status => l_x_return_status,
4834: x_msg_count => x_msg_count,
4835: x_msg_data => x_msg_data);
4828: p_assignment_status_code => l_next_status_code,
4829: p_asgn_start_date => l_asgn_start_date,
4830: p_asgn_end_date => l_asgn_end_date,
4831: p_init_msg_list => FND_API.G_FALSE,
4832: p_save_to_hist => FND_API.G_FALSE,
4833: x_return_status => l_x_return_status,
4834: x_msg_count => x_msg_count,
4835: x_msg_data => x_msg_data);
4836: END IF;
4857: x_return_status := l_x_return_status;
4858: x_msg_count := FND_MSG_PUB.Count_Msg;
4859: If x_msg_count = 1 THEN
4860: pa_interface_utils_pub.get_messages
4861: (p_encoded => FND_API.G_TRUE ,
4862: p_msg_index => 1,
4863: p_msg_count => x_msg_count ,
4864: p_msg_data => x_msg_data ,
4865: --p_data => x_msg_data, * Commented for Bug: 4537865
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',
4881: p_procedure_name => 'update_sch_wf_success');
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:
4940: -- Get assignment information
4975: p_end_date => rec.end_date,
4976: p_assignment_status_code => l_next_status_code,
4977: p_asgn_start_date => l_asgn_start_date,
4978: p_asgn_end_date => l_asgn_end_date,
4979: p_init_msg_list => FND_API.G_FALSE,
4980: p_save_to_hist => FND_API.G_FALSE,
4981: x_return_status => l_x_return_status,
4982: x_msg_count => x_msg_count,
4983: x_msg_data => x_msg_data);
4976: p_assignment_status_code => l_next_status_code,
4977: p_asgn_start_date => l_asgn_start_date,
4978: p_asgn_end_date => l_asgn_end_date,
4979: p_init_msg_list => FND_API.G_FALSE,
4980: p_save_to_hist => FND_API.G_FALSE,
4981: x_return_status => l_x_return_status,
4982: x_msg_count => x_msg_count,
4983: x_msg_data => x_msg_data);
4984:
5039: p_end_date => l_last_end_date,
5040: p_assignment_status_code => l_next_status_code,
5041: p_asgn_start_date => l_asgn_start_date,
5042: p_asgn_end_date => l_asgn_end_date,
5043: p_init_msg_list => FND_API.G_FALSE,
5044: p_save_to_hist => FND_API.G_FALSE,
5045: x_return_status => l_x_return_status,
5046: x_msg_count => x_msg_count,
5047: x_msg_data => x_msg_data);
5040: p_assignment_status_code => l_next_status_code,
5041: p_asgn_start_date => l_asgn_start_date,
5042: p_asgn_end_date => l_asgn_end_date,
5043: p_init_msg_list => FND_API.G_FALSE,
5044: p_save_to_hist => FND_API.G_FALSE,
5045: x_return_status => l_x_return_status,
5046: x_msg_count => x_msg_count,
5047: x_msg_data => x_msg_data);
5048: END IF;
5076: p_end_date => l_last_end_date,
5077: p_assignment_status_code => l_next_status_code,
5078: p_asgn_start_date => l_asgn_start_date,
5079: p_asgn_end_date => l_asgn_end_date,
5080: p_init_msg_list => FND_API.G_FALSE,
5081: p_save_to_hist => FND_API.G_FALSE,
5082: x_return_status => l_x_return_status,
5083: x_msg_count => x_msg_count,
5084: x_msg_data => x_msg_data);
5077: p_assignment_status_code => l_next_status_code,
5078: p_asgn_start_date => l_asgn_start_date,
5079: p_asgn_end_date => l_asgn_end_date,
5080: p_init_msg_list => FND_API.G_FALSE,
5081: p_save_to_hist => FND_API.G_FALSE,
5082: x_return_status => l_x_return_status,
5083: x_msg_count => x_msg_count,
5084: x_msg_data => x_msg_data);
5085: END IF;
5094:
5095: x_msg_count := FND_MSG_PUB.Count_Msg;
5096: If x_msg_count = 1 THEN
5097: pa_interface_utils_pub.get_messages
5098: (p_encoded => FND_API.G_TRUE ,
5099: p_msg_index => 1,
5100: p_msg_count => x_msg_count ,
5101: p_msg_data => x_msg_data ,
5102: --p_data => x_msg_data, * Commented for Bug fix: 4537865
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',
5120: p_procedure_name => 'update_sch_wf_failure');
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
5161: l_index := l_index + 1;
5164: -- Delete schedules for the assignment in order to insert new assignment
5165: -- records.
5166: delete_asgn_schedules(
5167: p_assignment_id => p_assignment_id,
5168: p_perm_delete => FND_API.G_FALSE,
5169: p_change_id => p_change_id,
5170: x_return_status => l_x_return_status,
5171: x_msg_count => x_msg_count,
5172: x_msg_data => x_msg_data);
5198: delete pa_schedules_history
5199: where assignment_id = p_assignment_id
5200: and last_approved_flag = 'Y';
5201:
5202: IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
5203: PA_TIMELINE_PVT.CREATE_TIMELINE (
5204: p_assignment_id => p_assignment_id,
5205: x_return_status => l_x_return_status,
5206: x_msg_count => x_msg_count,
5211:
5212: x_msg_count := FND_MSG_PUB.Count_Msg;
5213: If x_msg_count = 1 THEN
5214: pa_interface_utils_pub.get_messages
5215: (p_encoded => FND_API.G_TRUE ,
5216: p_msg_index => 1,
5217: p_msg_count => x_msg_count ,
5218: p_msg_data => x_msg_data ,
5219: --p_data => x_msg_data, * Commented for Bug fix: 4537865
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',
5237: p_procedure_name => 'revert_to_last_approved');
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.
5288: open c1;
5319:
5320: x_msg_count := FND_MSG_PUB.Count_Msg;
5321: If x_msg_count = 1 THEN
5322: pa_interface_utils_pub.get_messages
5323: (p_encoded => FND_API.G_TRUE ,
5324: p_msg_index => 1,
5325: p_msg_count => x_msg_count ,
5326: p_msg_data => x_msg_data ,
5327: --p_data => x_msg_data, * Commented for Bgu: 4537865
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',
5345: p_procedure_name => 'update_history_table');
5428: WHERE asmt.assignment_id = p_assignment_id
5429: AND prj.project_id = asmt.project_id;
5430:
5431: BEGIN
5432: x_return_status := FND_API.G_RET_STS_SUCCESS;
5433:
5434: -- get approval status
5435: OPEN get_apprvl_status_code;
5436: FETCH get_apprvl_status_code INTO l_apprvl_status_code;
5678: END IF; -- IF (p_populate_mode = 'SCHEDULE_UPDATED')
5679:
5680: EXCEPTION
5681: WHEN OTHERS THEN
5682: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5683:
5684: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5685: p_procedure_name => 'update_asgmt_changed_items_tab');
5686: RAISE;
5717: l_end_date DATE;
5718: l_self_conflict_flag VARCHAR2(1) := 'N';
5719:
5720: BEGIN
5721: x_return_status := FND_API.G_RET_STS_SUCCESS;
5722:
5723: OPEN c1;
5724: FETCH c1 INTO l_resource_id, l_start_date, l_end_date;
5725: CLOSE c1;
5870: WHEN OTHERS THEN
5871: x_overcommitment_flag := NULL ; -- 4537865
5872: x_conflict_group_id := NULL ;-- 4537865
5873:
5874: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5875: x_msg_count := 1;
5876: x_msg_data := SQLERRM;
5877: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
5878: p_procedure_name => 'check_overcommitment_single');
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
5924: PA_SCHEDULE_UTILS.debug('v_c1.assignment_id = '|| v_c1.assignment_id);
6121: x_msg_count => x_msg_count,
6122: x_msg_data => x_msg_data);
6123:
6124: -- added for bug fix: 4537865
6125: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
6126: l_conflict_group_id := l_new_conflict_group_id;
6127: END IF;
6128: -- added for bug fix: 4537865
6129:
6145: EXCEPTION
6146: WHEN OTHERS THEN
6147: x_overcommitment_flag := NULL ; -- 4537865
6148: x_conflict_group_id := NULL ;-- 4537865
6149: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6150: x_msg_count := 1;
6151: x_msg_data := SQLERRM;
6152: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
6153: p_procedure_name => 'check_overcommitment_mult');
6247: v_c1 c1%ROWTYPE;
6248: v_c2 c2%ROWTYPE;
6249:
6250: BEGIN
6251: x_return_status := FND_API.G_RET_STS_SUCCESS;
6252:
6253: IF G_AVAILABILITY_CAL_PERIOD = 'DAILY' THEN
6254: OPEN c1;
6255: FETCH c1 INTO v_c1;
6281:
6282: EXCEPTION
6283: WHEN OTHERS THEN
6284: x_self_conflict_flag := NULL ; -- 4537865
6285: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6286: x_msg_count := 1;
6287: x_msg_data := SQLERRM;
6288: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
6289: p_procedure_name => 'check_self_conflict');
6371: l_new_msg_data VARCHAR2(2000);
6372: -- added for Bug fix: 4537865
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;
6691: END IF;
6692: END LOOP;
6693:
6694: -- Update pa_assignment_conflict_hist table.
6695: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
6696: PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
6697: p_assignment_id => p_assignment_id,
6698: p_processed_flag => 'Y',
6699: x_return_status => x_return_status,
6708:
6709: EXCEPTION
6710: WHEN l_remove_conflicts_failed THEN
6711: PA_UTILS.add_message('PA','PA_REMOVE_CONFLICTS_FAILED');
6712: x_return_status := FND_API.G_RET_STS_ERROR;
6713: x_msg_data := 'PA_REMOVE_CONFLICTS_FAILED';
6714: x_msg_count := FND_MSG_PUB.Count_Msg;
6715: IF x_msg_count = 1 THEN
6716: pa_interface_utils_pub.get_messages
6713: x_msg_data := 'PA_REMOVE_CONFLICTS_FAILED';
6714: x_msg_count := FND_MSG_PUB.Count_Msg;
6715: IF x_msg_count = 1 THEN
6716: pa_interface_utils_pub.get_messages
6717: (p_encoded => FND_API.G_TRUE,
6718: p_msg_index => 1,
6719: p_msg_count => x_msg_count,
6720: p_msg_data => x_msg_data,
6721: --p_data => x_msg_data, * Commented for Bug fix: 4537865
6725: x_msg_data := l_new_msg_data;
6726: -- added for Bug fix: 4537865
6727: End IF;
6728: WHEN OTHERS THEN
6729: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6730: x_msg_count := 1;
6731: x_msg_data := SQLERRM;
6732: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
6733: p_procedure_name => 'resolve_conflicts');
6800: x_work_pattern_tbl(l_count).friday_hours||';'||
6801: x_work_pattern_tbl(l_count).saturday_hours||';'||
6802: x_work_pattern_tbl(l_count).sunday_hours);
6803:
6804: x_return_status := FND_API.G_RET_STS_SUCCESS;
6805: EXCEPTION
6806: WHEN OTHERS THEN
6807: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6808: x_msg_count := 1;
6803:
6804: x_return_status := FND_API.G_RET_STS_SUCCESS;
6805: EXCEPTION
6806: WHEN OTHERS THEN
6807: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6808: x_msg_count := 1;
6809: x_msg_data := SQLERRM;
6810: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
6811: p_procedure_name => 'insert_work_pattern_record');
6828: l_overcom_quantity NUMBER;
6829: l_count NUMBER;
6830:
6831: BEGIN
6832: x_return_status := FND_API.G_RET_STS_SUCCESS;
6833:
6834: IF x_work_pattern_tbl.COUNT > 0 THEN
6835:
6836: l_week_day := TO_CHAR(p_item_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN');
6917: END IF;
6918:
6919: EXCEPTION
6920: WHEN OTHERS THEN
6921: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6922: x_msg_count := 1;
6923: x_msg_data := SQLERRM;
6924: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
6925: p_procedure_name => 'insert_work_pattern_record');
6937: l_cur NUMBER;
6938: l_flag VARCHAR2(1);
6939:
6940: BEGIN
6941: x_return_status := FND_API.G_RET_STS_SUCCESS;
6942:
6943: --l_flag := 'N';
6944: FOR j IN p_cur_work_pattern_tbl.FIRST .. p_cur_work_pattern_tbl.LAST LOOP
6945:
7009:
7010:
7011: EXCEPTION
7012: WHEN OTHERS THEN
7013: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7014: x_msg_count := 1;
7015: x_msg_data := SQLERRM;
7016: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7017: p_procedure_name => 'insert_work_pattern_tab');
7110: l_err_stage VARCHAR2(2000);
7111: l_err_stack VARCHAR2(2000);
7112:
7113: BEGIN
7114: x_return_status := FND_API.G_RET_STS_SUCCESS;
7115:
7116: OPEN c1;
7117: FETCH c1 INTO v_c1;
7118: CLOSE c1;
7384: CLOSE c3;
7385:
7386: EXCEPTION
7387: WHEN OTHERS THEN
7388: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7389: x_msg_count := 1;
7390: x_msg_data := SQLERRM;
7391: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7392: p_procedure_name => 'overcom_post_aprvl_processing');
7424: x_resolve_conflicts_by_rmvl := 'Y';
7425: END IF;
7426: CLOSE c1;
7427:
7428: x_return_status := FND_API.G_RET_STS_SUCCESS;
7429: EXCEPTION
7430: WHEN OTHERS THEN
7431: -- 4537865
7432: x_resolve_conflicts_by_rmvl := NULL ;
7430: WHEN OTHERS THEN
7431: -- 4537865
7432: x_resolve_conflicts_by_rmvl := NULL ;
7433:
7434: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7435: x_msg_count := 1;
7436: x_msg_data := SQLERRM;
7437: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7438: p_procedure_name => 'will_resolve_conflicts_by_rmvl');
7469: x_resolve_conflicts_by_rmvl := 'Y';
7470: END IF;
7471: CLOSE c1;
7472:
7473: x_return_status := FND_API.G_RET_STS_SUCCESS;
7474: EXCEPTION
7475: WHEN OTHERS THEN
7476: -- 4537865
7477: x_resolve_conflicts_by_rmvl := NULL ;
7474: EXCEPTION
7475: WHEN OTHERS THEN
7476: -- 4537865
7477: x_resolve_conflicts_by_rmvl := NULL ;
7478: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7479: x_msg_count := 1;
7480: x_msg_data := SQLERRM;
7481: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7482: p_procedure_name => 'has_resolved_conflicts_by_rmvl');
7502: AND conf.resolve_conflicts_action_code = 'CANCEL_TXN_ITEM'
7503: AND conf.processed_flag = 'N';
7504:
7505: BEGIN
7506: x_return_status := FND_API.G_RET_STS_SUCCESS;
7507:
7508: For v_c1 in c1 LOOP
7509: PA_ASSIGNMENT_APPROVAL_PUB.cancel_assignment(p_record_version_number =>v_c1.record_version_number,
7510: p_assignment_id => v_c1.assignment_id,
7514: x_return_status => x_return_status,
7515: x_msg_count => x_msg_count,
7516: x_msg_data => x_msg_data);
7517:
7518: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
7519: PA_ASGN_CONFLICT_HIST_PKG.update_rows(p_conflict_group_id => p_conflict_group_id,
7520: p_assignment_id => v_c1.assignment_id,
7521: p_processed_flag => 'Y',
7522: x_return_status => x_return_status,
7526: END LOOP;
7527:
7528: EXCEPTION
7529: WHEN OTHERS THEN
7530: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7531: x_msg_count := 1;
7532: x_msg_data := SQLERRM;
7533: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7534: p_procedure_name => 'cancel_overcom_txn_items');
7552: AND resolve_conflicts_action_code = 'REVERT_TXN_ITEM'
7553: AND processed_flag = 'N';
7554:
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);
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',
7569: x_return_status => x_return_status,
7573: END LOOP;
7574:
7575: EXCEPTION
7576: WHEN OTHERS THEN
7577: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7578: x_msg_count := 1;
7579: x_msg_data := SQLERRM;
7580: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7581: p_procedure_name => 'revert_overcom_txn_items');
7599: INTO x_assignment_count
7600: FROM pa_assignment_conflict_hist
7601: WHERE conflict_group_id = p_conflict_group_id;
7602:
7603: x_return_status := FND_API.G_RET_STS_SUCCESS;
7604: EXCEPTION
7605: WHEN OTHERS THEN
7606: -- 4537865
7607: x_assignment_count := NULL ;
7605: WHEN OTHERS THEN
7606: -- 4537865
7607: x_assignment_count := NULL ;
7608:
7609: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7610: x_msg_count := 1;
7611: x_msg_data := SQLERRM;
7612: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7613: p_procedure_name => 'get_conflicting_asgmt_count');
7637:
7638: v_c1 c1%ROWTYPE;
7639:
7640: BEGIN
7641: x_return_status := FND_API.G_RET_STS_SUCCESS;
7642: OPEN c1;
7643: FETCH c1 INTO v_c1;
7644: IF c1%NOTFOUND THEN
7645: x_action_taken := 'Y';
7652: WHEN OTHERS THEN
7653: -- 4537865
7654: x_action_taken := NULL ;
7655:
7656: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7657: x_msg_count := 1;
7658: x_msg_data := SQLERRM;
7659: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7660: p_procedure_name => 'has_action_taken_on_conflicts');
7681: AND asgn.apprvl_status_code = 'ASGMT_APPRVL_WORKING';
7682:
7683: v_c1 c1%ROWTYPE;
7684: BEGIN
7685: x_return_status := FND_API.G_RET_STS_SUCCESS;
7686:
7687: OPEN c1;
7688: FETCH c1 INTO v_c1;
7689: IF c1%NOTFOUND THEN
7697: WHEN OTHERS THEN
7698: -- 4537865
7699: x_result := NULL ;
7700:
7701: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7702: x_msg_count := 1;
7703: x_msg_data := SQLERRM;
7704: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',
7705: p_procedure_name => 'check_asgmt_apprvl_working');
7842: END IF;
7843: END IF; -- 4387388
7844: -- End of Debugging Statements
7845:
7846: x_return_status := FND_API.G_RET_STS_SUCCESS;
7847: -- Check for invalid dates in p_schedule_tbl
7848: IF p_schedule_tbl.first IS NULL OR p_schedule_tbl.last IS NULL THEN
7849: l_schedule_start_date := p_start_date - 1;
7850: l_schedule_end_date := p_start_date - 2;
8080: END IF; -- 4387388
8081:
8082: EXCEPTION
8083: WHEN OTHERS THEN
8084: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
8085: x_msg_count := 1;
8086: x_msg_data := SQLERRM;
8087: x_total_hours := 0; -- Bug 5126919
8088: FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_SCHEDULE_PVT',