[Home] [Help]
158: -- Declare cursors and local variables
159: --
160: l_proc varchar2(72) := g_package||'update_person';
161: l_effective_date date;
162: l_person_type_id per_person_types.person_type_id%type :=
163: p_person_type_id;
164: l_person_type_id1 per_person_types.person_type_id%type :=
165: p_person_type_id;
166: l_business_group_id per_people_f.business_group_id%type;
160: l_proc varchar2(72) := g_package||'update_person';
161: l_effective_date date;
162: l_person_type_id per_person_types.person_type_id%type :=
163: p_person_type_id;
164: l_person_type_id1 per_person_types.person_type_id%type :=
165: p_person_type_id;
166: l_business_group_id per_people_f.business_group_id%type;
167: l_old_person_type_id per_all_people_f.person_type_id%type;
168: l_current_system_person_type per_person_types.system_person_type%type;
164: l_person_type_id1 per_person_types.person_type_id%type :=
165: p_person_type_id;
166: l_business_group_id per_people_f.business_group_id%type;
167: l_old_person_type_id per_all_people_f.person_type_id%type;
168: l_current_system_person_type per_person_types.system_person_type%type;
169: l_validate boolean := false;
170: l_discard_varchar2 varchar2(40);
171: l_applicant_number per_people_f.applicant_number%TYPE;
172: l_object_version_number per_people_f.object_version_number%TYPE;
205: and per.effective_end_date;
206: --
207: cursor csr_system_type(p_person_type_id1 number) is
208: select pet.system_person_type
209: from per_person_types pet
210: where pet.person_type_id=p_person_type_id1;
211: --
212: cursor csr_period_of_service is
213: select pds.period_of_service_id, pds.object_version_number, pds.adjusted_svc_date
2105:
2106: --for bug 7369431
2107: cursor chk_person_type is
2108: SELECT typ.system_person_type
2109: FROM per_person_types typ
2110: ,per_person_type_usages_f ptu
2111: WHERE typ.person_type_id = ptu.person_type_id
2112: AND p_effective_date BETWEEN ptu.effective_start_date
2113: AND ptu.effective_end_date
2705:
2706: CURSOR csr_sys_person_type(c_person_id NUMBER) IS
2707: SELECT pet.system_person_type
2708: FROM per_all_people_f per,
2709: per_person_types pet
2710: WHERE per.person_type_id = pet.person_type_id
2711: AND per.person_id = c_person_id
2712: AND trunc(sysdate) BETWEEN per.effective_start_date AND per.effective_end_date;
2713:
2795: AND effective_start_date = start_date;
2796:
2797: BEGIN
2798: select 1 into l_exists
2799: from per_person_type_usages_f ptuf, per_person_types ppt
2800: where ppt.system_person_type = 'IRC_REG_USER'
2801: and ptuf.person_type_id = ppt.person_type_id
2802: and ppt.business_group_id + 0 = nvl(l_bg_id,ppt.business_group_id)
2803: and not exists (select null from per_all_assignments_f paaf where paaf.person_id = ptuf.person_id)
2856: begin
2857: select 'Y' into l_prev_exists
2858: from sys.dual where exists (
2859: select 'Previous Person type exists'
2860: from per_person_types ppt
2861: , per_person_type_usages_f ptu
2862: where ptu.person_id = p_source_person_id
2863: and ppt.business_group_id +0= l_bg_id
2864: and ptu.person_type_id = ppt.person_type_id
2863: and ppt.business_group_id +0= l_bg_id
2864: and ptu.person_type_id = ppt.person_type_id
2865: and ppt.system_person_type <>'APL'
2866: AND (ppt.system_person_type='OTHER' and not exists (select 1
2867: from per_person_type_usages_f ptu1,per_person_types ppt1
2868: where ptu1.person_id=p_source_person_id
2869: and ppt1.person_type_id=ptu1.person_type_id
2870: and ppt1.system_person_type='IRC_REG_USER') )
2871: and ppt.system_person_type<>'IRC_REG_USER'
3208: END;
3209:
3210: BEGIN
3211: select 1 into l_can_exists
3212: from per_person_type_usages_f ptuf, per_person_types ppt
3213: where ppt.system_person_type = 'IRC_REG_USER'
3214: and ptuf.person_type_id = ppt.person_type_id
3215: and ppt.business_group_id + 0 = nvl(l_bg_id,ppt.business_group_id)
3216: and not exists (select null from per_all_assignments_f paaf where paaf.person_id = ptuf.person_id)