[Home] [Help]
47: cursor check_status_for_elig_prf is
48: select
49: nvl(processing_status,'A') processing_status_flag
50: from
51: ota_user_group_elements
52: where
53: user_group_id = p_user_group_id
54: --and trunc(sysdate) between start_date_active and nvl(end_date_active,trunc(sysdate+1))
55: --Adding above clause will not display members for end dated user groups
59: cursor check_status_for_ug is
60: select
61: processing_status
62: from
63: ota_user_group_elements
64: where
65: user_group_id = p_user_group_id
66: --and trunc(sysdate) between start_date_active and nvl(end_date_active,trunc(sysdate+1))
67: --Adding above clause will not display members for end dated user groups
127:
128: cursor check_resolved_data is
129: select person_id
130: from
131: ota_user_group_elements
132: where
133: user_group_id = p_user_group_id and person_id= p_person_id
134: and processing_status = nvl(p_processing_status,processing_status)
135: and elig_prfl_id is not null
135: and elig_prfl_id is not null
136: and person_id is not null;
137:
138: l_data_exists varchar2(1) := 'N';
139: l_person_id ota_user_group_elements.person_id%type;
140:
141: begin
142: open check_resolved_data ;
143: fetch check_resolved_data into l_person_id;
176: end get_elig_object_id;
177:
178:
179: Procedure process_eligible_lrnr_record(p_person_id per_all_assignments_f.person_id%type,
180: p_user_group_id ota_user_group_elements.user_group_id%type,
181: p_eligibility_profile_id ota_user_group_elements.elig_prfl_id%type,
182: p_business_group_id ota_user_group_elements.business_group_id%type,
183: p_enable_logging in varchar2 default 'N') IS
184:
177:
178:
179: Procedure process_eligible_lrnr_record(p_person_id per_all_assignments_f.person_id%type,
180: p_user_group_id ota_user_group_elements.user_group_id%type,
181: p_eligibility_profile_id ota_user_group_elements.elig_prfl_id%type,
182: p_business_group_id ota_user_group_elements.business_group_id%type,
183: p_enable_logging in varchar2 default 'N') IS
184:
185: l_object_version_number number :=1;
178:
179: Procedure process_eligible_lrnr_record(p_person_id per_all_assignments_f.person_id%type,
180: p_user_group_id ota_user_group_elements.user_group_id%type,
181: p_eligibility_profile_id ota_user_group_elements.elig_prfl_id%type,
182: p_business_group_id ota_user_group_elements.business_group_id%type,
183: p_enable_logging in varchar2 default 'N') IS
184:
185: l_object_version_number number :=1;
186: begin
189: if p_enable_logging = 'Y' then
190: FND_FILE.PUT_LINE(FND_FILE.LOG,'Record exist for person_id : ' ||TO_CHAR(p_person_id) ||' user_group_id : ' ||TO_CHAR(p_user_group_id) ||' so update.Eligibility profile id is : '||TO_CHAR(p_eligibility_profile_id));
191: end if;
192:
193: update ota_user_group_elements set processing_status= 'A' ,elig_prfl_id = p_eligibility_profile_id
194: where
195: user_group_id= p_user_group_id and
196: person_id = p_person_id and
197: elig_prfl_id is not null;
202: if p_enable_logging = 'Y' then
203: FND_FILE.PUT_LINE(FND_FILE.LOG,'Record does not exist for person_id : ' ||TO_CHAR(p_person_id) ||' user_group_id : ' ||TO_CHAR(p_user_group_id) ||' so create.Eligibility profile id is : '||TO_CHAR(p_eligibility_profile_id));
204: end if;
205:
206: insert into ota_user_group_elements(
207: USER_GROUP_ELEMENT_ID,
208: USER_GROUP_ID,
209: BUSINESS_GROUP_ID,
210: PERSON_ID,
212: CREATION_DATE,
213: ELIG_PRFL_ID,
214: processing_status)
215: VALUES(
216: ota_user_group_elements_s.nextval,
217: p_user_group_id,
218: p_business_group_id,
219: p_person_id,
220: l_object_version_number,
258: l_numberof_records_processed number:=0;
259: assignment_whereclause varchar2(100);
260: l_elig_obj_id number;
261: l_counter number:=0;
262: l_learner_group_element_id ota_user_group_elements.user_group_element_id%type;
263:
264: TYPE learner_rec IS RECORD(
265: person_id per_assignments_f.person_id%type,
266: assignment_id per_assignments_f.assignment_id%type,
270:
271: lrnr_rec learner_rec;
272:
273: TYPE ug_rec IS RECORD(
274: user_group_id ota_user_group_elements.user_group_id%type,
275: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
276: user_group_element_id ota_user_group_elements.user_group_element_id%type,
277: business_group_id ota_user_group_elements.business_group_id%type
278: );
271: lrnr_rec learner_rec;
272:
273: TYPE ug_rec IS RECORD(
274: user_group_id ota_user_group_elements.user_group_id%type,
275: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
276: user_group_element_id ota_user_group_elements.user_group_element_id%type,
277: business_group_id ota_user_group_elements.business_group_id%type
278: );
279:
272:
273: TYPE ug_rec IS RECORD(
274: user_group_id ota_user_group_elements.user_group_id%type,
275: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
276: user_group_element_id ota_user_group_elements.user_group_element_id%type,
277: business_group_id ota_user_group_elements.business_group_id%type
278: );
279:
280: user_group_rec ug_rec;
273: TYPE ug_rec IS RECORD(
274: user_group_id ota_user_group_elements.user_group_id%type,
275: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
276: user_group_element_id ota_user_group_elements.user_group_element_id%type,
277: business_group_id ota_user_group_elements.business_group_id%type
278: );
279:
280: user_group_rec ug_rec;
281:
295: elsif p_elig_prof_id is null and p_learner_group_id is not null then
296: ug_ep_whereclause := 'user_group_id = ' ||TO_CHAR(p_learner_group_id) ;
297: elsif p_elig_prof_id is not null and p_learner_group_id is null then
298: ug_ep_whereclause := 'user_group_id in
299: (select user_group_id from ota_user_group_elements
300: where elig_prfl_id ='|| TO_CHAR(p_elig_prof_id)|| ')';
301: else
302: ug_ep_whereclause := '1=1';
303:
310: uge.user_group_element_id,
311: uge.business_group_id
312: from
313: ota_user_groups_b ug,
314: ota_user_group_elements uge
315: where
316: ug.user_group_id = uge.user_group_id
317: and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
318: and uge.elig_prfl_id is not null
347: if check_processing_status(user_group_rec.user_group_id, user_group_rec.elig_prfl_id) <> 'P' then
348: --This is done to avoid the learner group if its been picked/processed by some other run of this conc program
349: --i.e the user group is under processing in the mean time.
350: l_learner_group_element_id := user_group_rec.user_group_element_id;
351: update ota_user_group_elements set processing_status = 'P' --Parent record to Processing
352: where user_group_element_id = user_group_rec.user_group_element_id;
353: update ota_user_group_elements set processing_status = 'H' --Child records to history
354: where user_group_id = user_group_rec.user_group_id and elig_prfl_id =user_group_rec.elig_prfl_id and person_id is not null;
355: commit;--commit early so that other re-runs of conc programs donot pick it up
349: --i.e the user group is under processing in the mean time.
350: l_learner_group_element_id := user_group_rec.user_group_element_id;
351: update ota_user_group_elements set processing_status = 'P' --Parent record to Processing
352: where user_group_element_id = user_group_rec.user_group_element_id;
353: update ota_user_group_elements set processing_status = 'H' --Child records to history
354: where user_group_id = user_group_rec.user_group_id and elig_prfl_id =user_group_rec.elig_prfl_id and person_id is not null;
355: commit;--commit early so that other re-runs of conc programs donot pick it up
356:
357: open get_elig_prof_details(user_group_rec.elig_prfl_id);
450: END LOOP;
451: CLOSE csr_get_lrnr_in_assign;
452: end if;--end if for get_elig_prof_details%NOTFOUND
453:
454: update ota_user_group_elements set processing_status= 'A'
455: where user_group_element_id = user_group_rec.user_group_element_id;--set status of master record to A
456: end if ; --end if for check_processing_status
457: end loop;
458: close csr_get_ug_to_be_processed;
464: commit;
465: exception
466: when others then
467:
468: update ota_user_group_elements set processing_status= 'E' --Parent record to Error
469: where user_group_element_id =l_learner_group_element_id;
470:
471: FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in processing learner group '||l_learner_group_name);
472: commit;
516:
517: ug_whereclause varchar2(200);
518:
519: TYPE ug_rec IS RECORD(
520: user_group_id ota_user_group_elements.user_group_id%type,
521: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
522: user_group_element_id ota_user_group_elements.user_group_element_id%type,
523: business_group_id ota_user_group_elements.business_group_id%type
524: );
517: ug_whereclause varchar2(200);
518:
519: TYPE ug_rec IS RECORD(
520: user_group_id ota_user_group_elements.user_group_id%type,
521: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
522: user_group_element_id ota_user_group_elements.user_group_element_id%type,
523: business_group_id ota_user_group_elements.business_group_id%type
524: );
525:
518:
519: TYPE ug_rec IS RECORD(
520: user_group_id ota_user_group_elements.user_group_id%type,
521: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
522: user_group_element_id ota_user_group_elements.user_group_element_id%type,
523: business_group_id ota_user_group_elements.business_group_id%type
524: );
525:
526: TYPE t_ug_rec IS TABLE OF ug_rec
519: TYPE ug_rec IS RECORD(
520: user_group_id ota_user_group_elements.user_group_id%type,
521: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
522: user_group_element_id ota_user_group_elements.user_group_element_id%type,
523: business_group_id ota_user_group_elements.business_group_id%type
524: );
525:
526: TYPE t_ug_rec IS TABLE OF ug_rec
527: INDEX BY BINARY_INTEGER;
531: TYPE learner_assign_details IS REF CURSOR;
532: csr_get_ug_to_be_processed learner_assign_details;
533:
534: get_ug_to_be_processed varchar2(4000);
535: l_pre_ug_id ota_user_group_elements.user_group_id%TYPE := -1;
536: l_general_bg number := ota_general.get_business_group_id;
537: l_proc varchar2(200) := 'resolve_eligibility_profile';
538: OTA_NO_PERSON_LG_TO_PROCESS exception;
539:
577:
578: elsif p_elig_prof_id is not null and p_learner_group_id is null then
579:
580: ug_whereclause := 'user_group_id in
581: (select user_group_id from ota_user_group_elements
582: where elig_prfl_id ='|| TO_CHAR(p_elig_prof_id)|| ')';
583: else
584:
585: ug_whereclause := '1=1';
592: uge.user_group_element_id,
593: uge.business_group_id
594: from
595: ota_user_groups_b ug,
596: ota_user_group_elements uge
597: where
598: ug.user_group_id = uge.user_group_id
599: and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
600: and uge.elig_prfl_id is not null
616:
617: for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
618: loop
619: if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
620: update ota_user_group_elements set processing_status = fnd_global.conc_request_id --Parent record to Processing
621: where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
622: and elig_prfl_id is not null
623: and person_id is null
624: and BUSINESS_GROUP_ID = l_general_bg;
622: and elig_prfl_id is not null
623: and person_id is null
624: and BUSINESS_GROUP_ID = l_general_bg;
625:
626: update ota_user_group_elements set processing_status = 'H' --Child records to history
627: where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
628: and elig_prfl_id is not null
629: and person_id is not null
630: and BUSINESS_GROUP_ID = l_general_bg;
777: l_pre_ug_id := -1;
778: for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
779: loop
780: if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
781: update ota_user_group_elements set processing_status = 'A' --Parent record to completed
782: where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
783: and nvl(processing_status,'A') <> 'E'
784: and elig_prfl_id is not null
785: and person_id is null
801: IF user_elig_pro_rec.count > 0 THEN
802: for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
803: loop
804: if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
805: update ota_user_group_elements set processing_status = 'P' --Parent record to Processed
806: where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
807: and elig_prfl_id is not null
808: and person_id is null
809: and BUSINESS_GROUP_ID = l_general_bg;
820: IF user_elig_pro_rec.count > 0 THEN
821: for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
822: loop
823: if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
824: update ota_user_group_elements set processing_status = 'E' --Parent record to Error
825: where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
826: and elig_prfl_id is not null
827: and person_id is null
828: and BUSINESS_GROUP_ID = l_general_bg;
928: l_numberof_records_processed number DEFAULT 0;
929: assignment_whereclause varchar2(100);
930: l_elig_obj_id number;
931: l_counter number DEFAULT 0;
932: l_learner_group_element_id ota_user_group_elements.user_group_element_id%TYPE;
933: TYPE learner_rec IS RECORD (person_id per_assignments_f.person_id%TYPE
934: ,assignment_id per_assignments_f.assignment_id%TYPE
935: ,business_group_id per_assignments_f.business_group_id%TYPE);
936: TYPE t_learner_rec IS TABLE OF learner_rec INDEX BY binary_integer;
934: ,assignment_id per_assignments_f.assignment_id%TYPE
935: ,business_group_id per_assignments_f.business_group_id%TYPE);
936: TYPE t_learner_rec IS TABLE OF learner_rec INDEX BY binary_integer;
937: lrnr_rec t_learner_rec;
938: TYPE ug_rec IS RECORD (user_group_id ota_user_group_elements.user_group_id%TYPE
939: ,elig_prfl_id ota_user_group_elements.elig_prfl_id%TYPE
940: ,user_group_element_id ota_user_group_elements.user_group_element_id%TYPE
941: ,business_group_id ota_user_group_elements.business_group_id%TYPE
942: ,elig_pro_bg_id ben_eligy_prfl_f.business_group_id%TYPE);
935: ,business_group_id per_assignments_f.business_group_id%TYPE);
936: TYPE t_learner_rec IS TABLE OF learner_rec INDEX BY binary_integer;
937: lrnr_rec t_learner_rec;
938: TYPE ug_rec IS RECORD (user_group_id ota_user_group_elements.user_group_id%TYPE
939: ,elig_prfl_id ota_user_group_elements.elig_prfl_id%TYPE
940: ,user_group_element_id ota_user_group_elements.user_group_element_id%TYPE
941: ,business_group_id ota_user_group_elements.business_group_id%TYPE
942: ,elig_pro_bg_id ben_eligy_prfl_f.business_group_id%TYPE);
943:
936: TYPE t_learner_rec IS TABLE OF learner_rec INDEX BY binary_integer;
937: lrnr_rec t_learner_rec;
938: TYPE ug_rec IS RECORD (user_group_id ota_user_group_elements.user_group_id%TYPE
939: ,elig_prfl_id ota_user_group_elements.elig_prfl_id%TYPE
940: ,user_group_element_id ota_user_group_elements.user_group_element_id%TYPE
941: ,business_group_id ota_user_group_elements.business_group_id%TYPE
942: ,elig_pro_bg_id ben_eligy_prfl_f.business_group_id%TYPE);
943:
944: TYPE t_ug_rec IS TABLE OF ug_rec INDEX BY binary_integer;
937: lrnr_rec t_learner_rec;
938: TYPE ug_rec IS RECORD (user_group_id ota_user_group_elements.user_group_id%TYPE
939: ,elig_prfl_id ota_user_group_elements.elig_prfl_id%TYPE
940: ,user_group_element_id ota_user_group_elements.user_group_element_id%TYPE
941: ,business_group_id ota_user_group_elements.business_group_id%TYPE
942: ,elig_pro_bg_id ben_eligy_prfl_f.business_group_id%TYPE);
943:
944: TYPE t_ug_rec IS TABLE OF ug_rec INDEX BY binary_integer;
945: user_elig_pro_rec t_ug_rec;
948: csr_get_ug_to_be_processed learner_assign_details;
949: csr_get_ug_ep_det learner_assign_details;
950: ug_whereclause varchar2(200);
951: get_ug_to_be_processed varchar2(4000);
952: l_pre_ug_id ota_user_group_elements.user_group_id%TYPE := -1;
953: l_proc varchar2(200) := 'resolve_eligibility_profile_m';
954: l_ota_general_bg number := ota_general.get_business_group_id;
955: l_is_eligible boolean := false;
956:
967: ug_whereclause := 'user_group_id = '
968: || to_char (p_learner_group_id);
969: ELSIF p_elig_prof_id IS NOT NULL
970: AND p_learner_group_id IS NULL THEN
971: ug_whereclause := 'user_group_id in (select user_group_id from ota_user_group_elements
972: where elig_prfl_id = '
973: || to_char (p_elig_prof_id)
974: || ' )';
975: ELSE
984: uge.business_group_id uge_business_group_id,
985: bep.business_group_id ep_business_group_id
986: from
987: ota_user_groups_b ug,
988: ota_user_group_elements uge,
989: ben_eligy_prfl_f bep
990: where
991: ug.user_group_id = uge.user_group_id
992: and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
1160: IF user_elig_pro_rec.count > 0 THEN
1161: for ug_print_count in user_elig_pro_rec.first..user_elig_pro_rec.last --{ ug_ep loop
1162: loop
1163: if l_pre_ug_id <> user_elig_pro_rec(ug_print_count).user_group_id then
1164: update ota_user_group_elements set processing_status = 'E' --Parent record to Error
1165: where user_group_id = user_elig_pro_rec(ug_print_count).user_group_id
1166: and elig_prfl_id is not null
1167: and person_id is null
1168: and BUSINESS_GROUP_ID = l_ota_general_bg;
1191:
1192: --user_group_rec get_ug_to_be_processed%rowtype;
1193: l_counter number:=0;
1194: l_learner_group_name ota_user_groups_tl.user_group_name%type;
1195: l_learner_group_element_id ota_user_group_elements.user_group_element_id%type;
1196: ug_ep_whereclause varchar2(200);
1197: get_ug_to_be_processed varchar2(4000);
1198: l_business_group_id ben_eligy_prfl_f.business_group_id%type;
1199:
1197: get_ug_to_be_processed varchar2(4000);
1198: l_business_group_id ben_eligy_prfl_f.business_group_id%type;
1199:
1200: TYPE ug_rec IS RECORD(
1201: user_group_id ota_user_group_elements.user_group_id%type,
1202: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
1203: user_group_element_id ota_user_group_elements.user_group_element_id%type,
1204: business_group_id ota_user_group_elements.business_group_id%type
1205: );
1198: l_business_group_id ben_eligy_prfl_f.business_group_id%type;
1199:
1200: TYPE ug_rec IS RECORD(
1201: user_group_id ota_user_group_elements.user_group_id%type,
1202: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
1203: user_group_element_id ota_user_group_elements.user_group_element_id%type,
1204: business_group_id ota_user_group_elements.business_group_id%type
1205: );
1206:
1199:
1200: TYPE ug_rec IS RECORD(
1201: user_group_id ota_user_group_elements.user_group_id%type,
1202: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
1203: user_group_element_id ota_user_group_elements.user_group_element_id%type,
1204: business_group_id ota_user_group_elements.business_group_id%type
1205: );
1206:
1207: user_group_rec ug_rec;
1200: TYPE ug_rec IS RECORD(
1201: user_group_id ota_user_group_elements.user_group_id%type,
1202: elig_prfl_id ota_user_group_elements.elig_prfl_id%type,
1203: user_group_element_id ota_user_group_elements.user_group_element_id%type,
1204: business_group_id ota_user_group_elements.business_group_id%type
1205: );
1206:
1207: user_group_rec ug_rec;
1208:
1221: elsif p_elig_prof_id is null and p_learner_group_id is not null then
1222: ug_ep_whereclause := 'user_group_id = ' ||TO_CHAR(p_learner_group_id) ;
1223: elsif p_elig_prof_id is not null and p_learner_group_id is null then
1224: ug_ep_whereclause := 'user_group_id in
1225: (select user_group_id from ota_user_group_elements
1226: where elig_prfl_id ='|| TO_CHAR(p_elig_prof_id)|| ')';
1227: else
1228: ug_ep_whereclause := '1=1';
1229:
1236: uge.user_group_element_id,
1237: uge.business_group_id
1238: from
1239: ota_user_groups_b ug,
1240: ota_user_group_elements uge
1241: where
1242: ug.user_group_id = uge.user_group_id
1243: and trunc(sysdate) between trunc(ug.start_date_active) and nvl(ug.end_date_active,trunc(sysdate+1))
1244: and uge.elig_prfl_id is not null
1264: --write messages in conc program log;
1265: else
1266:
1267: l_counter := l_counter+1;
1268: delete from ota_user_group_elements uge where
1269: uge.person_id is not null and
1270: uge.elig_prfl_id is not null and
1271: uge.processing_status = 'H' and
1272: uge.user_group_id = user_group_rec.user_group_id and
1273: uge.elig_prfl_id = user_group_rec.elig_prfl_id
1274: and exists
1275: (
1276: select user_group_element_id from
1277: ota_user_group_elements parent
1278: where
1279: parent.elig_prfl_id = uge.elig_prfl_id
1280: and parent.user_group_id = uge.user_group_id
1281: and nvl(parent.processing_status,'A') = 'A'
1293:
1294: exception
1295: when others then
1296:
1297: update ota_user_group_elements set processing_status= 'E' --Parent record to Error
1298: where user_group_element_id =l_learner_group_element_id;
1299:
1300: FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in deleting ineligible records for learner group '||l_learner_group_name);
1301: commit;
1309:
1310: cursor user_group_exists is
1311: select user_group_id
1312: from
1313: ota_user_group_elements
1314: where
1315: elig_prfl_id=p_elig_prof_id;
1316:
1317: l_user_group_id ota_user_group_elements.user_group_id%type;
1313: ota_user_group_elements
1314: where
1315: elig_prfl_id=p_elig_prof_id;
1316:
1317: l_user_group_id ota_user_group_elements.user_group_id%type;
1318:
1319: begin
1320:
1321: open user_group_exists;
1510: else
1511: l_obj_version := 1;
1512: end if;
1513: if l_elig_obj_obj_version is not null and l_obj_version is not null then
1514: delete from ota_user_group_elements
1515: where user_group_id=p_user_group_id and
1516: elig_prfl_id=l_elig_prof_id and
1517: person_id is not null;
1518: p_result := 'S';