[Home] [Help]
108: --
109: l_fnd_resp_rec hr_user_acct_utility.fnd_responsibility_rec;
110: l_responsibility_id fnd_responsibility.responsibility_id%type := null;
111: l_responsibility_key fnd_responsibility.responsibility_key%type := null;
112: l_user_resp_start_date fnd_user_resp_groups.start_date%type;
113: l_user_resp_end_date fnd_user_resp_groups.end_date%type;
114: l_user_level_only boolean default false;
115: l_count number default 0;
116: l_prof_opt_val_count number default 0;
109: l_fnd_resp_rec hr_user_acct_utility.fnd_responsibility_rec;
110: l_responsibility_id fnd_responsibility.responsibility_id%type := null;
111: l_responsibility_key fnd_responsibility.responsibility_key%type := null;
112: l_user_resp_start_date fnd_user_resp_groups.start_date%type;
113: l_user_resp_end_date fnd_user_resp_groups.end_date%type;
114: l_user_level_only boolean default false;
115: l_count number default 0;
116: l_prof_opt_val_count number default 0;
117: l_resp_count number default 0;
260: --
261: l_resp_count := hr_user_acct_utility.g_fnd_resp_tbl.count;
262: --
263: IF l_resp_count < 1
264: -- No fnd_user_resp_groups, per_sec_profile_assignments,
265: -- function exclusions or profile option values to insert
266: THEN
267: goto after_process_hook;
268: END IF;
368: hr_api.g_number
369: THEN
370: l_fnd_resp_rec.new_resp_app_id :=
371: hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_app_id;
372: -- Set the application_id for fnd_user_resp_groups to
373: -- the new resp app id
374: l_user_resp_app_id :=
375: hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_app_id;
376: ELSE
373: -- the new resp app id
374: l_user_resp_app_id :=
375: hr_user_acct_utility.g_fnd_resp_tbl(i).new_resp_app_id;
376: ELSE
377: -- Set the application_id for fnd_user_resp_groups to
378: -- the template responsibility's app id
379: l_user_resp_app_id := l_fnd_resp_rec.new_resp_app_id;
380: END IF;
381: --
526:
527: IF l_enable_sec_groups = 'N'
528: THEN
529: hr_utility.set_location (l_proc ||
530: ' before create_fnd_user_resp_groups', 50);
531: --
532: -- **********************************************
533: -- Create the new fnd_user_resp_groups record
534: -- **********************************************
529: hr_utility.set_location (l_proc ||
530: ' before create_fnd_user_resp_groups', 50);
531: --
532: -- **********************************************
533: -- Create the new fnd_user_resp_groups record
534: -- **********************************************
535: -- NOTE: Only insert a row into fnd_user_resp_groups
536: -- when the profile option 'ENABLE_SECURITY_GROUPS'
537: -- is 'N'.
531: --
532: -- **********************************************
533: -- Create the new fnd_user_resp_groups record
534: -- **********************************************
535: -- NOTE: Only insert a row into fnd_user_resp_groups
536: -- when the profile option 'ENABLE_SECURITY_GROUPS'
537: -- is 'N'.
538: --
539: hr_user_acct_internal.create_fnd_user_resp_groups
535: -- NOTE: Only insert a row into fnd_user_resp_groups
536: -- when the profile option 'ENABLE_SECURITY_GROUPS'
537: -- is 'N'.
538: --
539: hr_user_acct_internal.create_fnd_user_resp_groups
540: (p_user_id => l_user_id
541: ,p_responsibility_id => l_responsibility_id
542: ,p_application_id => l_user_resp_app_id
543: ,p_sec_group_id => 0
546: ,p_description => l_fnd_resp_rec.user_resp_description
547: );
548:
549: hr_utility.set_location (l_proc ||
550: ' after create_fnd_user_resp_groups', 51);
551: ELSE
552: -- 'ENABLE_SECURITY_GROUPS' = 'Y'; customers have the option to insert
553: -- into fnd_user_resp_groups only if view-all security profile of the
554: -- employee's business group is to be used. This will be applicable
549: hr_utility.set_location (l_proc ||
550: ' after create_fnd_user_resp_groups', 51);
551: ELSE
552: -- 'ENABLE_SECURITY_GROUPS' = 'Y'; customers have the option to insert
553: -- into fnd_user_resp_groups only if view-all security profile of the
554: -- employee's business group is to be used. This will be applicable
555: -- to Employee Self Service responsibility. If a restricted security
556: -- profile is to be used, then we must call hrasprhi.pkb which will
557: -- insert one row into per_sec_profile_assignments as well as to
554: -- employee's business group is to be used. This will be applicable
555: -- to Employee Self Service responsibility. If a restricted security
556: -- profile is to be used, then we must call hrasprhi.pkb which will
557: -- insert one row into per_sec_profile_assignments as well as to
558: -- fnd_user_resp_groups.
559:
560: IF l_fnd_resp_rec.sec_group_id IS NULL OR
561: l_fnd_resp_rec.sec_profile_id IS NULL
562: THEN
559:
560: IF l_fnd_resp_rec.sec_group_id IS NULL OR
561: l_fnd_resp_rec.sec_profile_id IS NULL
562: THEN
563: -- Insert into fnd_user_resp_groups only
564: -- get the security_group_id for the employee's business_group_id
565:
566: OPEN lc_get_sec_group_id;
567: FETCH lc_get_sec_group_id into l_sec_group_id;
577: CLOSE lc_get_sec_group_id;
578: END IF;
579:
580: hr_utility.set_location (l_proc ||
581: ' before create_fnd_user_resp_groups', 53);
582: --
583: hr_user_acct_internal.create_fnd_user_resp_groups
584: (p_user_id => l_user_id
585: ,p_responsibility_id => l_responsibility_id
579:
580: hr_utility.set_location (l_proc ||
581: ' before create_fnd_user_resp_groups', 53);
582: --
583: hr_user_acct_internal.create_fnd_user_resp_groups
584: (p_user_id => l_user_id
585: ,p_responsibility_id => l_responsibility_id
586: ,p_application_id => l_user_resp_app_id
587: ,p_sec_group_id => l_sec_group_id
590: ,p_description => l_fnd_resp_rec.user_resp_description
591: );
592:
593: hr_utility.set_location (l_proc ||
594: ' after create_fnd_user_resp_groups', 54);
595: --
596: ELSE
597: -- sec_group_id and sec_profile_id are filled in
598: -- call peasprhi.pkb to insert into per_sec_profile_assignments
595: --
596: ELSE
597: -- sec_group_id and sec_profile_id are filled in
598: -- call peasprhi.pkb to insert into per_sec_profile_assignments
599: -- as well as fnd_user_resp_groups.
600:
601: hr_utility.set_location (l_proc ||
602: ' before create_sec_profile_asg', 56);
603:
994: -- | |
995: -- | USAGE: |
996: -- | ----- |
997: -- | This wrapper module is used to update fnd_user and |
998: -- | fnd_user_resp_groups, or per_sec_profile_assignments records specifically|
999: -- | for expiring a user account. |
1000: -- | User accounts for terminated employees will not be deleted because |
1001: -- | some HR history forms have sql statements join to the fnd_user table |
1002: -- | derive the who columns. |
1052: -- Cursor to select all records which belongs to the terminated employee's
1053: -- user id and the end date is null or the end date is greater than the
1054: -- termination date.
1055: --
1056: -- Fix for bug 4147802 starts here. used fnd_user_resp_groups_direct view
1057: -- in place of fnd_user_resp_groups. Also the column description is removed.
1058: --
1059: CURSOR lc_get_user_resp (c_user_id in number)
1060: IS
1053: -- user id and the end date is null or the end date is greater than the
1054: -- termination date.
1055: --
1056: -- Fix for bug 4147802 starts here. used fnd_user_resp_groups_direct view
1057: -- in place of fnd_user_resp_groups. Also the column description is removed.
1058: --
1059: CURSOR lc_get_user_resp (c_user_id in number)
1060: IS
1061: /*
1064: ,security_group_id
1065: ,start_date
1066: ,end_date
1067: -- ,description
1068: FROM fnd_user_resp_groups_direct
1069: WHERE user_id = c_user_id
1070: AND nvl(end_date, l_date + 1) > l_date
1071: AND trunc(sysdate) between start_date and nvl(end_date,sysdate); --5090502
1072: */
1075: ,furgd.security_group_id
1076: ,furgd.start_date
1077: ,furgd.end_date
1078: -- ,description
1079: FROM fnd_user_resp_groups_direct furgd, FND_RESPONSIBILITY fr
1080: WHERE furgd.user_id = c_user_id
1081: AND fr.responsibility_id = furgd.responsibility_id
1082: AND trunc(sysdate) between fr.start_date and nvl(fr.end_date,sysdate)
1083: AND nvl(furgd.end_date, l_date + 1) > l_date
1154: --
1155: -- Need to end date all rows in per_sec_profile_assignments associated
1156: -- the user id.
1157: -- The per_asp_upd(peasprhi.pkb) api will transparently end date the
1158: -- fnd_user_resp_groups records as well.
1159:
1160:
1161: FOR get_sec_prf_asg in lc_get_sec_profile_asg
1162: (c_user_id => get_user_ids.user_id)
1171: );
1172: END LOOP;
1173:
1174:
1175: -- Need to end date the fnd_user_resp_groups record for
1176: -- each user id.
1177: -- The cursor needs to return the application_id, start_date
1178: -- end_date, description, responsibility_id in addition to
1179: -- user_id because these attributes are the required parameters
1176: -- each user id.
1177: -- The cursor needs to return the application_id, start_date
1178: -- end_date, description, responsibility_id in addition to
1179: -- user_id because these attributes are the required parameters
1180: -- in fnd_user_resp_groups update_row.
1181: --
1182: -- If 'ENABLE_SECURITY_GROUPS' = 'Y' and the data are setup
1183: -- correctly, there should not be any rows returned from the
1184: -- lc_get_user_resp cursor because the peasprhi.pkb api would
1181: --
1182: -- If 'ENABLE_SECURITY_GROUPS' = 'Y' and the data are setup
1183: -- correctly, there should not be any rows returned from the
1184: -- lc_get_user_resp cursor because the peasprhi.pkb api would
1185: -- have already end dated the fnd_user_resp_groups record. If
1186: -- we have rows returned from the lc_get_user_resp, that means
1187: -- 'ENABLE_SECURITY_GROUPS' = 'N'. Hence, we need to end date
1188: -- the fnd_user_resp_groups rows.
1189: FOR get_user_resp in lc_get_user_resp
1184: -- lc_get_user_resp cursor because the peasprhi.pkb api would
1185: -- have already end dated the fnd_user_resp_groups record. If
1186: -- we have rows returned from the lc_get_user_resp, that means
1187: -- 'ENABLE_SECURITY_GROUPS' = 'N'. Hence, we need to end date
1188: -- the fnd_user_resp_groups rows.
1189: FOR get_user_resp in lc_get_user_resp
1190: (c_user_id => get_user_ids.user_id)
1191: LOOP
1192: hr_utility.trace('Calling update_fnd_user_resp_groups with ');
1188: -- the fnd_user_resp_groups rows.
1189: FOR get_user_resp in lc_get_user_resp
1190: (c_user_id => get_user_ids.user_id)
1191: LOOP
1192: hr_utility.trace('Calling update_fnd_user_resp_groups with ');
1193: hr_utility.trace('user_id' || get_user_ids.user_id);
1194: hr_utility.trace('security_group_id' || get_user_resp.security_group_id);
1195: hr_utility.trace('p_end_date' || l_date);
1196:
1193: hr_utility.trace('user_id' || get_user_ids.user_id);
1194: hr_utility.trace('security_group_id' || get_user_resp.security_group_id);
1195: hr_utility.trace('p_end_date' || l_date);
1196:
1197: hr_user_acct_internal.update_fnd_user_resp_groups
1198: (p_user_id => get_user_ids.user_id
1199: ,p_responsibility_id => get_user_resp.responsibility_id
1200: ,p_resp_application_id
1201: => get_user_resp.responsibility_application_id