1 PACKAGE BODY hr_nl_extra_person_rules AS
2 /* $Header: penlexpr.pkb 120.1.12000000.2 2007/02/28 10:57:58 spendhar ship $ */
3 --
4 -- First Name, SOFI Number and Initials must be not null.
5 -- Lookup code for Academic Title must be valid.
6 --
7 -- Note: Supports both real and API system values (these are passed when the value has not
8 -- been changed.
9 --
10 PROCEDURE extra_person_checks
11 (p_first_name IN varchar2,
12 p_national_identifier IN varchar2,
13 p_honors IN varchar2,
14 p_per_information1 IN varchar2,
15 p_per_information4 IN varchar2) IS
16
17 l_lookup_meaning varchar2(50);
18
19 BEGIN
20 --
21 -- Added for GSI Bug 5472781
22 --
23 IF hr_utility.chk_product_install('Oracle Human Resources', 'NL') THEN
24 --
25 if p_first_name is null then
26 hr_utility.set_message(800, 'HR_NL_REQUIRED_FIELD');
27 hr_utility.set_message_token('FIELD','First Name');
28 hr_utility.set_message_token('LEG_CODE','NL');
29 hr_utility.raise_error;
30 end if;
31
32 if (p_national_identifier is null OR p_national_identifier = hr_api.g_varchar2) AND NVL(fnd_profile.value('HR_NL_NI_OPTIONAL'),'N') = 'N'
33 AND fnd_profile.VALUE('PER_NATIONAL_IDENTIFIER_VALIDATION') in ('ERROR','WARN') /* bug 4570899*/ then
34 hr_utility.set_message(800, 'HR_NL_REQUIRED_FIELD');
35 hr_utility.set_message_token('FIELD','SOFI Number');
36 hr_utility.set_message_token('LEG_CODE','NL');
37 hr_utility.raise_error;
38 end if;
39
40 if p_per_information1 is null then
41 hr_utility.set_message(800, 'HR_NL_REQUIRED_FIELD');
42 hr_utility.set_message_token('FIELD','Initials');
43 hr_utility.set_message_token('LEG_CODE','NL');
44 hr_utility.raise_error;
45 end if;
46
47 if p_per_information4 is null then
48 hr_utility.set_message(800, 'HR_NL_REQUIRED_FIELD');
49 hr_utility.set_message_token('FIELD','Full Name Format');
50 hr_utility.set_message_token('LEG_CODE','NL');
51 hr_utility.raise_error;
52 end if;
53 END IF;
54 END extra_person_checks;
55
56 /* Procedure checks if parameters reported in
57 the Dutch First Day Report have been changed
58 after report has been generated. If the
59 parameters are changed, then RESEND flag in
60 First Day Report Information EIT is set to
61 YES.
62 */
63 PROCEDURE fdr_update_check
64 ( P_PERSON_ID per_all_people_f.person_id%TYPE
65 , P_DATE_OF_BIRTH date
66 , P_PER_INFORMATION1 varchar2
67 , P_PRE_NAME_ADJUNCT per_all_people_f.PRE_NAME_ADJUNCT%TYPE default ' '
68 , P_LAST_NAME per_all_people_f.LAST_NAME%TYPE
69 , P_EFFECTIVE_DATE date default to_date('01/01/4712','DD/MM/YYYY')
70 , P_NATIONAL_IDENTIFIER per_all_people_f.NATIONAL_IDENTIFIER%TYPE
71 , P_EMPLOYEE_NUMBER per_all_people_f.EMPLOYEE_NUMBER%TYPE) IS
72
73 l_hiredate date;
74 l_initial varchar2(20);
75 l_sofi per_all_people_f.national_identifier%TYPE;
76 l_dob per_all_people_f.DATE_OF_BIRTH%TYPE;
77 l_prefix per_all_people_f.PRE_NAME_ADJUNCT%TYPE;
78 l_last_name per_all_people_f.LAST_NAME%TYPE;
79 l_employee_num per_all_people_f.EMPLOYEE_NUMBER%TYPE;
80
81 l_resend_enabled number := 0;
82 l_ext_info_id per_people_extra_info.PERSON_EXTRA_INFO_ID%type;
83 l_ovn per_people_extra_info.OBJECT_VERSION_NUMBER%TYPE;
84
85 CURSOR resend_enabled(p_csr_person_id PER_PEOPLE_EXTRA_INFO.person_id%TYPE) IS
86 SELECT count(*)
87 FROM PER_PEOPLE_EXTRA_INFO
88 WHERE PERSON_ID = p_csr_person_id
89 AND INFORMATION_TYPE = 'NL_FIRST_DAY_REPORT'
90 AND PEI_INFORMATION_CATEGORY = 'NL_FIRST_DAY_REPORT'
91 AND upper(pei_information2) = 'Y';
92
93
94 CURSOR person_previous_information(p_csr_person_id PER_PEOPLE_EXTRA_INFO.person_id%TYPE) IS
95 SELECT DATE_OF_BIRTH
96 ,PER_INFORMATION1
97 ,PRE_NAME_ADJUNCT
98 ,LAST_NAME
99 ,NATIONAL_IDENTIFIER
100 ,EMPLOYEE_NUMBER
101 FROM per_all_people_f
102 WHERE person_id = p_csr_person_id
103 AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE
104 AND EFFECTIVE_END_DATE
105 AND business_group_id in (SELECT business_group_id
106 FROM per_business_groups
107 WHERE legislation_code = 'NL');
108
109 BEGIN
110 /* Fetching information prior to changes made */
111
112 OPEN person_previous_information(p_person_id);
113 FETCH person_previous_information into
114 l_dob,
115 l_initial,
116 l_prefix,
117 l_last_name,
118 l_sofi,
119 l_employee_num;
120 CLOSE person_previous_information;
121
122 /*If parameters are not supplied */
123
124 IF P_EMPLOYEE_NUMBER = NULL THEN
125 l_employee_num := P_EMPLOYEE_NUMBER;
126 END IF;
127
128 IF P_LAST_NAME = hr_api.g_varchar2 THEN
129 l_last_name := P_LAST_NAME;
130 END IF;
131
132 IF P_PER_INFORMATION1 = hr_api.g_varchar2 THEN
133 l_initial := P_PER_INFORMATION1;
134 END IF;
135
136 IF P_NATIONAL_IDENTIFIER = hr_api.g_varchar2 THEN
137 l_sofi := P_NATIONAL_IDENTIFIER;
138 END IF;
139
140 /* If resend flag is already set to YES
141 , then need not to set it to yes again .*/
142
143 OPEN resend_enabled(p_person_id);
144 FETCH resend_enabled into l_resend_enabled;
145 CLOSE resend_enabled;
146
147 /* If parameter's values have been changed,
148 set resend_flag to YES .*/
149
150 IF (nvl(l_dob,to_date('01/01/4712','DD/MM/YYYY'))
151 <> nvl(P_DATE_OF_BIRTH,to_date('01/01/4712','DD/MM/YYYY')) OR
152 nvl(l_prefix,' ') <> nvl(P_PRE_NAME_ADJUNCT,' ') OR
153 l_last_name <> p_last_name OR
154 l_initial <> P_PER_INFORMATION1 OR
155 --l_sofi <> P_NATIONAL_IDENTIFIER OR
156 (l_sofi is not NULL and P_NATIONAL_IDENTIFIER is not NULL and l_sofi <> P_NATIONAL_IDENTIFIER) OR
157 (l_sofi is not NULL and P_NATIONAL_IDENTIFIER is NULL) OR
158 (l_sofi is NULL and P_NATIONAL_IDENTIFIER is not NULL) OR
159 l_employee_num <> P_EMPLOYEE_NUMBER)
160 AND l_resend_enabled = 0 THEN
161
162 IF p_person_id IS NOT NULL THEN
163 HR_PERSON_EXTRA_INFO_API. create_person_extra_info
164 (p_person_id => p_PERSON_ID,
165 p_information_type => 'NL_FIRST_DAY_REPORT',
166 p_pei_information2 => 'Y',
167 p_person_extra_info_id => l_ext_info_id,
168 p_object_version_number => l_ovn,
169 p_pei_information_category => 'NL_FIRST_DAY_REPORT');
170 END IF;
171 END IF;
172
173 END fdr_update_check;
174
175 procedure fdr_rehire_check
176 ( P_PERSON_ID per_all_people_f.person_id%TYPE) IS
177
178 l_resend_enabled number := 0;
179 l_ext_info_id per_people_extra_info.PERSON_EXTRA_INFO_ID%type;
180 l_ovn per_people_extra_info.OBJECT_VERSION_NUMBER%TYPE;
181
182 CURSOR resend_enabled(p_csr_person_id PER_PEOPLE_EXTRA_INFO.person_id%TYPE) IS
183 SELECT count(*)
184 FROM PER_PEOPLE_EXTRA_INFO
185 WHERE PERSON_ID = p_csr_person_id
186 AND INFORMATION_TYPE = 'NL_FIRST_DAY_REPORT'
187 AND PEI_INFORMATION_CATEGORY = 'NL_FIRST_DAY_REPORT'
188 AND upper(pei_information2) = 'Y';
189
190 BEGIN
191
192 /* If resend flag is already set to YES
193 , then need not to set it to yes again .*/
194
195 OPEN resend_enabled(p_person_id);
196 FETCH resend_enabled into l_resend_enabled;
197 CLOSE resend_enabled;
198
199 /* If parameter's values have been changed,
200 set resend_flag to YES .*/
201
202 IF l_resend_enabled = 0 THEN
203
204 IF p_person_id IS NOT NULL THEN
205 HR_PERSON_EXTRA_INFO_API. create_person_extra_info
206 (p_person_id => p_PERSON_ID,
207 p_information_type => 'NL_FIRST_DAY_REPORT',
208 p_pei_information2 => 'Y',
209 p_person_extra_info_id => l_ext_info_id,
210 p_object_version_number => l_ovn,
211 p_pei_information_category => 'NL_FIRST_DAY_REPORT');
212 END IF;
213 END IF;
214 END fdr_rehire_check;
215
216 END hr_nl_extra_person_rules;
[Home] [Help]