151: l_count number;
152:
153: BEGIN
154:
155: l_fnd_date := to_date(to_char(fnd_api.g_miss_date, 'DD-MM-RRRR'), 'DD-MM-RRRR');
156: end_date_active := to_date(to_char(p_end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
157: l_updated_by := jtf_resource_utl.updated_by;
158:
159: --Standard Start of API SAVEPOINT
158:
159: --Standard Start of API SAVEPOINT
160: SAVEPOINT res_save;
161:
162: l_return_status := fnd_api.g_ret_sts_success;
163: l_count := 0;
164: IF FND_API.To_boolean(P_INIT_MSG_LIST)
165: THEN
166: FND_MSG_PUB.Initialize;
160: SAVEPOINT res_save;
161:
162: l_return_status := fnd_api.g_ret_sts_success;
163: l_count := 0;
164: IF FND_API.To_boolean(P_INIT_MSG_LIST)
165: THEN
166: FND_MSG_PUB.Initialize;
167: END IF;
168: open term_res_cur(p_resource_id);
179: fetch res_team_cur INTO res_team_rec;
180: WHILE(res_team_cur%FOUND)
181: LOOP
182:
183: l_return_status := fnd_api.g_ret_sts_success;
184: l_end_date_active := to_date(to_char(res_team_rec.end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
185: --if start date > sysdate -1 then delete the role
186: IF(trunc(res_team_rec.start_date_active) > trunc(p_end_date_active) )
187: THEN
208: X_MSG_DATA => l_msg_data);
209:
210: END IF; -- end of start_date check
211:
212: if ( l_return_status <> fnd_api.g_ret_sts_success)
213: then
214: raise fnd_api.g_exc_error;
215: END IF;
216: FETCH res_team_cur INTO res_team_rec;
210: END IF; -- end of start_date check
211:
212: if ( l_return_status <> fnd_api.g_ret_sts_success)
213: then
214: raise fnd_api.g_exc_error;
215: END IF;
216: FETCH res_team_cur INTO res_team_rec;
217: END LOOP; -- end of res_team_cur
218: CLOSE res_team_cur;
247: X_MSG_COUNT => l_msg_count,
248: X_MSG_DATA => l_msg_data);
249:
250: END IF; -- end of start_date check
251: if ( l_return_status <> fnd_api.g_ret_sts_success)
252: then
253: raise fnd_api.g_exc_error;
254: END IF;
255: FETCH res_group_cur INTO res_group_rec;
249:
250: END IF; -- end of start_date check
251: if ( l_return_status <> fnd_api.g_ret_sts_success)
252: then
253: raise fnd_api.g_exc_error;
254: END IF;
255: FETCH res_group_cur INTO res_group_rec;
256: END LOOP; -- end of res_group_cur
257: CLOSE res_group_cur;
288:
289: END IF; -- end of start_date check
290:
291:
292: if ( l_return_status <> fnd_api.g_ret_sts_success)
293: then
294: raise fnd_api.g_exc_error;
295: END IF;
296:
290:
291:
292: if ( l_return_status <> fnd_api.g_ret_sts_success)
293: then
294: raise fnd_api.g_exc_error;
295: END IF;
296:
297:
298:
323: IF res_srp_terr_dup_cur%FOUND THEN
324: fnd_message.set_name ('JTF','JTF_RS_DUP_TERR');
325: fnd_msg_pub.add;
326: CLOSE res_srp_terr_dup_cur;
327: raise fnd_api.g_exc_error;
328: END IF;
329: CLOSE res_srp_terr_dup_cur;
330:
331: update ra_salesrep_territories
380: X_RETURN_STATUS => l_return_status,
381: X_MSG_COUNT => l_msg_count,
382: X_MSG_DATA => l_msg_data ) ;
383: END IF; -- end of start_date check
384: if ( l_return_status <> fnd_api.g_ret_sts_success)
385: then
386: raise fnd_api.g_exc_error;
387: END IF;
388:
382: X_MSG_DATA => l_msg_data ) ;
383: END IF; -- end of start_date check
384: if ( l_return_status <> fnd_api.g_ret_sts_success)
385: then
386: raise fnd_api.g_exc_error;
387: END IF;
388:
389:
390:
402:
403: --for future dated resources terminate it anyway
404: jtf_rs_resource_pub.update_resource
405: (P_API_VERSION => 1,
406: P_INIT_MSG_LIST => fnd_api.g_true,
407: P_COMMIT => fnd_api.g_false,
408: P_RESOURCE_ID => term_res_rec.resource_id,
409: P_RESOURCE_NUMBER => term_res_rec.resource_number,
410: P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
403: --for future dated resources terminate it anyway
404: jtf_rs_resource_pub.update_resource
405: (P_API_VERSION => 1,
406: P_INIT_MSG_LIST => fnd_api.g_true,
407: P_COMMIT => fnd_api.g_false,
408: P_RESOURCE_ID => term_res_rec.resource_id,
409: P_RESOURCE_NUMBER => term_res_rec.resource_number,
410: P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
411: P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
418:
419: --put end_date to p_end_date_active
420: jtf_rs_resource_pub.update_resource
421: ( P_API_VERSION => 1,
422: P_INIT_MSG_LIST => fnd_api.g_true,
423: P_COMMIT => fnd_api.g_false,
424: P_RESOURCE_ID => term_res_rec.resource_id,
425: P_RESOURCE_NUMBER => term_res_rec.resource_number,
426: P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
419: --put end_date to p_end_date_active
420: jtf_rs_resource_pub.update_resource
421: ( P_API_VERSION => 1,
422: P_INIT_MSG_LIST => fnd_api.g_true,
423: P_COMMIT => fnd_api.g_false,
424: P_RESOURCE_ID => term_res_rec.resource_id,
425: P_RESOURCE_NUMBER => term_res_rec.resource_number,
426: P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
427: P_OBJECT_VERSION_NUM => l_object_version_num_res,
432: END IF; -- end of terminate employee
433:
434: x_object_ver_number := l_object_version_num_res;
435:
436: if ( l_return_status <> fnd_api.g_ret_sts_success)
437: then
438: raise fnd_api.g_exc_error;
439: END IF;
440: --------------------------------------------------
434: x_object_ver_number := l_object_version_num_res;
435:
436: if ( l_return_status <> fnd_api.g_ret_sts_success)
437: then
438: raise fnd_api.g_exc_error;
439: END IF;
440: --------------------------------------------------
441: --Bug9009376
442: --When No Data Found against Resource Id then
445: fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
446: fnd_message.set_token('P_RESOURCE_ID', p_resource_id);
447: fnd_msg_pub.add;
448:
449: RAISE fnd_api.g_exc_error;
450:
451: end if; -- end of term_res_cur
452:
453: close term_res_cur;
457: --Status in local varaible assigned to Out parameter.
458: x_return_status := l_return_status;
459:
460: EXCEPTION
461: WHEN fnd_api.g_exc_unexpected_error
462: THEN
463: ROLLBACK TO res_save;
464: x_return_status := fnd_api.g_ret_sts_error;
465: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
460: EXCEPTION
461: WHEN fnd_api.g_exc_unexpected_error
462: THEN
463: ROLLBACK TO res_save;
464: x_return_status := fnd_api.g_ret_sts_error;
465: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
466: WHEN fnd_api.g_exc_error
467: THEN
468: ROLLBACK TO res_save;
462: THEN
463: ROLLBACK TO res_save;
464: x_return_status := fnd_api.g_ret_sts_error;
465: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
466: WHEN fnd_api.g_exc_error
467: THEN
468: ROLLBACK TO res_save;
469: x_return_status := fnd_api.g_ret_sts_error;
470: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
465: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
466: WHEN fnd_api.g_exc_error
467: THEN
468: ROLLBACK TO res_save;
469: x_return_status := fnd_api.g_ret_sts_error;
470: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
471:
472: WHEN OTHERS
473: THEN
476: fnd_message.set_token('P_SQLCODE',SQLCODE);
477: fnd_message.set_token('P_SQLERRM',SQLERRM);
478: fnd_message.set_token('P_API_NAME',l_api_name);
479: FND_MSG_PUB.add;
480: x_return_status := fnd_api.g_ret_sts_unexp_error;
481: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
482: END end_date_employee;
483:
484: PROCEDURE add_message
495: AS
496: l_api_name VARCHAR2(30);
497: BEGIN
498: l_api_name := 'ADD_MESSAGE';
499: x_return_status := fnd_api.g_ret_sts_success;
500: FND_MSG_PUB.Initialize;
501: if(P_MESSAGE_CODE is not null)
502: then
503: fnd_message.set_name ('JTF', p_message_code);
500: FND_MSG_PUB.Initialize;
501: if(P_MESSAGE_CODE is not null)
502: then
503: fnd_message.set_name ('JTF', p_message_code);
504: if((P_TOKEN1_NAME is not null) OR (P_TOKEN1_NAME <> fnd_api.g_miss_char))
505: then
506: fnd_message.set_token (p_token1_name, p_token1_value);
507: end if;
508: if((P_TOKEN2_NAME is not null) OR (P_TOKEN2_NAME <> fnd_api.g_miss_char))
504: if((P_TOKEN1_NAME is not null) OR (P_TOKEN1_NAME <> fnd_api.g_miss_char))
505: then
506: fnd_message.set_token (p_token1_name, p_token1_value);
507: end if;
508: if((P_TOKEN2_NAME is not null) OR (P_TOKEN2_NAME <> fnd_api.g_miss_char))
509: then
510: fnd_message.set_token (p_token2_name, p_token2_value);
511: end if;
512: FND_MSG_PUB.add;
520: fnd_message.set_token('P_SQLCODE',SQLCODE);
521: fnd_message.set_token('P_SQLERRM',SQLERRM);
522: fnd_message.set_token('P_API_NAME',l_api_name);
523: FND_MSG_PUB.add;
524: x_return_status := fnd_api.g_ret_sts_unexp_error;
525: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
526:
527: END add_message ;
528:
826: grp_relations_rec grp_relations_cur%rowtype;
827:
828: BEGIN
829:
830: l_fnd_date := to_date(to_char(fnd_api.g_miss_date, 'DD-MM-RRRR'), 'DD-MM-RRRR');
831: end_date_active := to_date(to_char(p_end_date_active, 'DD-MM-RRRR'), 'DD-MM-RRRR');
832:
833: --Standard Start of API SAVEPOINT
834: SAVEPOINT group_mem_roles_save;
832:
833: --Standard Start of API SAVEPOINT
834: SAVEPOINT group_mem_roles_save;
835:
836: l_return_status := fnd_api.g_ret_sts_success;
837:
838: IF FND_API.To_boolean(P_INIT_MSG_LIST)
839: THEN
840: FND_MSG_PUB.Initialize;
834: SAVEPOINT group_mem_roles_save;
835:
836: l_return_status := fnd_api.g_ret_sts_success;
837:
838: IF FND_API.To_boolean(P_INIT_MSG_LIST)
839: THEN
840: FND_MSG_PUB.Initialize;
841: END IF;
842:
877: X_MSG_COUNT => l_msg_count,
878: X_MSG_DATA => l_msg_data);
879:
880: END IF; -- end of start_date check
881: if ( l_return_status <> fnd_api.g_ret_sts_success)
882: then
883: raise fnd_api.g_exc_error;
884: END IF;
885: FETCH group_mem_roles_cur INTO group_mem_roles_rec;
879:
880: END IF; -- end of start_date check
881: if ( l_return_status <> fnd_api.g_ret_sts_success)
882: then
883: raise fnd_api.g_exc_error;
884: END IF;
885: FETCH group_mem_roles_cur INTO group_mem_roles_rec;
886: END LOOP; -- end of group_mem_roles_cur
887: CLOSE group_mem_roles_cur;
916: X_MSG_COUNT => l_msg_count,
917: X_MSG_DATA => l_msg_data);
918:
919: END IF; -- end of start_date check
920: if ( l_return_status <> fnd_api.g_ret_sts_success)
921: then
922: raise fnd_api.g_exc_error;
923: END IF;
924: FETCH grp_relations_cur INTO grp_relations_rec;
918:
919: END IF; -- end of start_date check
920: if ( l_return_status <> fnd_api.g_ret_sts_success)
921: then
922: raise fnd_api.g_exc_error;
923: END IF;
924: FETCH grp_relations_cur INTO grp_relations_rec;
925: END LOOP; -- end of grp_relations_cur
926: CLOSE grp_relations_cur;
933:
934: --for future dated groups terminate it anyway
935: jtf_rs_groups_pub.update_resource_group
936: (P_API_VERSION => 1.0,
937: P_INIT_MSG_LIST => fnd_api.g_true,
938: P_COMMIT => fnd_api.g_false,
939: P_GROUP_ID => term_grp_rec.group_id,
940: P_GROUP_NUMBER => term_grp_rec.group_number,
941: P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
934: --for future dated groups terminate it anyway
935: jtf_rs_groups_pub.update_resource_group
936: (P_API_VERSION => 1.0,
937: P_INIT_MSG_LIST => fnd_api.g_true,
938: P_COMMIT => fnd_api.g_false,
939: P_GROUP_ID => term_grp_rec.group_id,
940: P_GROUP_NUMBER => term_grp_rec.group_number,
941: P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
942: P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
949:
950: --put end_date to p_end_date_active
951: jtf_rs_groups_pub.update_resource_group
952: (P_API_VERSION => 1.0,
953: P_INIT_MSG_LIST => fnd_api.g_true,
954: P_COMMIT => fnd_api.g_false,
955: P_GROUP_ID => term_grp_rec.group_id,
956: P_GROUP_NUMBER => term_grp_rec.group_number,
957: P_END_DATE_ACTIVE => trunc(p_end_date_active),
950: --put end_date to p_end_date_active
951: jtf_rs_groups_pub.update_resource_group
952: (P_API_VERSION => 1.0,
953: P_INIT_MSG_LIST => fnd_api.g_true,
954: P_COMMIT => fnd_api.g_false,
955: P_GROUP_ID => term_grp_rec.group_id,
956: P_GROUP_NUMBER => term_grp_rec.group_number,
957: P_END_DATE_ACTIVE => trunc(p_end_date_active),
958: P_OBJECT_VERSION_NUM => l_object_version_num_grp,
963: END IF; -- end of terminate group
964:
965: x_object_ver_number := l_object_version_num_grp;
966:
967: if ( l_return_status <> fnd_api.g_ret_sts_success) then
968: raise fnd_api.g_exc_error;
969: END IF;
970:
971: end if; -- end of term_grp_cur
964:
965: x_object_ver_number := l_object_version_num_grp;
966:
967: if ( l_return_status <> fnd_api.g_ret_sts_success) then
968: raise fnd_api.g_exc_error;
969: END IF;
970:
971: end if; -- end of term_grp_cur
972: close term_grp_cur;
973:
974: FND_MSG_PUB.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
975:
976: EXCEPTION
977: WHEN fnd_api.g_exc_unexpected_error
978: THEN
979: ROLLBACK TO group_mem_roles_save;
980: x_return_status := fnd_api.g_ret_sts_error;
981: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
976: EXCEPTION
977: WHEN fnd_api.g_exc_unexpected_error
978: THEN
979: ROLLBACK TO group_mem_roles_save;
980: x_return_status := fnd_api.g_ret_sts_error;
981: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
982: WHEN fnd_api.g_exc_error
983: THEN
984: ROLLBACK TO group_mem_roles_save;
978: THEN
979: ROLLBACK TO group_mem_roles_save;
980: x_return_status := fnd_api.g_ret_sts_error;
981: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
982: WHEN fnd_api.g_exc_error
983: THEN
984: ROLLBACK TO group_mem_roles_save;
985: x_return_status := fnd_api.g_ret_sts_error;
986: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
981: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
982: WHEN fnd_api.g_exc_error
983: THEN
984: ROLLBACK TO group_mem_roles_save;
985: x_return_status := fnd_api.g_ret_sts_error;
986: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
987:
988: WHEN OTHERS
989: THEN
992: fnd_message.set_token('P_SQLCODE',SQLCODE);
993: fnd_message.set_token('P_SQLERRM',SQLERRM);
994: fnd_message.set_token('P_API_NAME',l_api_name);
995: FND_MSG_PUB.add;
996: x_return_status := fnd_api.g_ret_sts_unexp_error;
997: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
998:
999: END end_date_group;
1000: