1 PACKAGE BODY PER_PL_PEM_INFO AS
2 /* $Header: peplpemp.pkb 120.0.12000000.3 2007/02/28 11:02:25 spendhar noship $ */
3
4 PROCEDURE validate_dates(p_start_date DATE,
5 p_end_date DATE,
6 p_period_years NUMBER,
7 p_period_months NUMBER,
8 p_period_days NUMBER,
9 p_employment_category varchar2) IS
10
11 BEGIN
12
13 if p_employment_category is NULL then -- Previous Employer is called
14 if p_start_date is NULL and p_end_date is NULL then
15 if p_period_years is NULL and p_period_months is NULL and p_period_days is NULL then
16 hr_utility.set_message(800,'HR_PEM_DATE_NULL_PL');
17 hr_utility.raise_error;
18 end if; -- End if of Y/M/D NULL
19 end if; -- End if of Start/End Date NULL
20
21 if (p_start_date is not NULL and p_end_date is NULL) then
22 hr_utility.set_message(800,'HR_PEM_END_DATE_NULL_PL');
23 hr_utility.raise_error;
24 end if;
25
26 if (p_start_date is NULL and p_end_date is not NULL) then
27 hr_utility.set_message(800,'HR_PEM_START_DATE_NULL_PL');
28 hr_utility.raise_error;
29 end if;
30 end if; -- End if of Employment Category NULL
31
32
33 if p_employment_category is not NULL then
34 if p_start_date is NULL and p_end_date is NULL then
35 if p_period_years is NULL and p_period_months is NULL and p_period_days is NULL then
36 hr_utility.set_message(800,'HR_PEJ_DATE_NULL_PL');
37 hr_utility.set_message_token('SERVICE',hr_general.decode_lookup('PL_TYPE_OF_SERVICE',p_employment_category));
38 hr_utility.raise_error;
39 end if; -- End if of Y/M/D NULL
40 end if; -- End if of Start/End Date NULL
41
42 if (p_start_date is not NULL and p_end_date is NULL) then
43 hr_utility.set_message(800,'HR_PEJ_END_DATE_NULL_PL');
44 hr_utility.set_message_token('CATEGORY',hr_general.decode_lookup('PL_TYPE_OF_SERVICE',p_employment_category));
45 hr_utility.raise_error;
46 end if;
47
48 if (p_start_date is NULL and p_end_date is not NULL) then
49 hr_utility.set_message(800,'HR_PEJ_START_DATE_NULL_PL');
50 hr_utility.set_message_token('CATEGORY',hr_general.decode_lookup('PL_TYPE_OF_SERVICE',p_employment_category));
51 hr_utility.raise_error;
52 end if;
53
54
55 end if; -- End if of Employment Category Not NULL
56
57 END validate_dates;
58
59
60 PROCEDURE VALIDATE_PREV_EMP(p_employer_type VARCHAR2,
61 p_start_date DATE,
62 p_end_date DATE,
63 p_period_years NUMBER,
64 p_period_months NUMBER,
65 p_period_days NUMBER,
66 p_employment_category VARCHAR2) IS
67
68 BEGIN
69
70 if p_employer_type not in ('PARALLEL','PREVIOUS') then
71 hr_utility.set_message(800,'HR_PEM_TYPE_INVALID_PL');
72 hr_utility.raise_error;
73 end if;
74
75 if p_employer_type = 'PREVIOUS' then
76
77 validate_dates(p_start_date,
78 p_end_date,
79 p_period_years,
80 p_period_months,
81 p_period_days,
82 p_employment_category);
83
84 end if; -- End if of Type in Previous
85
86 END VALIDATE_PREV_EMP;
87
88
89 PROCEDURE CREATE_PL_PREV_EMPLOYER(p_effective_date DATE,
90 p_business_group_id NUMBER,
91 p_person_id NUMBER,
92 p_start_date DATE,
93 p_end_date DATE,
94 p_period_years NUMBER,
95 p_period_months NUMBER,
96 p_period_days NUMBER,
97 p_employer_type VARCHAR2,
98 p_employer_name VARCHAR2,
99 p_party_id NUMBER,
100 p_employer_subtype VARCHAR2,
101 p_pem_information_category VARCHAR2,
102 p_pem_information1 VARCHAR2,
103 p_pem_information2 VARCHAR2,
104 p_pem_information3 VARCHAR2,
105 p_pem_information4 VARCHAR2,
106 p_pem_information5 VARCHAR2,
107 p_pem_information6 VARCHAR2) IS
108
109 l_proc VARCHAR2(72); -- Variable used when data is uploaded directly by api
110 BEGIN
111
112 l_proc := 'PER_PL_PEM_INFO.'||'CREATE_PL_PREV_EMPLOYER';
113
114 /* Added for GSI Bug 5472781 */
115 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
116 hr_utility.set_location('Leaving : '||l_proc,10);
117 return;
118 END IF;
119
120 -- Check when data is uploaded by api's directly
121 hr_api.mandatory_arg_error
122 (p_api_name => l_proc,
123 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PEM_NAME'),
124 p_argument_value => p_employer_name
125 );
126
127
128 VALIDATE_PREV_EMP(p_employer_type,
129 p_start_date,
130 p_end_date,
131 p_period_years,
132 p_period_months,
133 p_period_days,
134 NULL);
135
136
137 END CREATE_PL_PREV_EMPLOYER;
138
139
140
141 PROCEDURE UPDATE_PL_PREV_EMPLOYER(p_effective_date DATE,
142 p_previous_employer_id NUMBER,
143 p_start_date DATE,
144 p_end_date DATE,
145 p_period_years NUMBER,
146 p_period_months NUMBER,
147 p_period_days NUMBER,
148 p_employer_type VARCHAR2,
149 p_employer_name VARCHAR2,
150 p_employer_subtype VARCHAR2,
151 p_pem_information_category VARCHAR2,
152 p_pem_information1 VARCHAR2,
153 p_pem_information2 VARCHAR2,
154 p_pem_information3 VARCHAR2,
155 p_pem_information4 VARCHAR2,
156 p_pem_information5 VARCHAR2,
157 p_pem_information6 VARCHAR2) IS
158
159 l_proc varchar2(72);
160
161 BEGIN
162
163 l_proc := 'PER_PL_PEM_INFO.'||'UPDATE_PL_PREV_EMPLOYER';
164 /* Added for GSI Bug 5472781 */
165 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
166 hr_utility.set_location('Leaving : '||l_proc,10);
167 return;
168 END IF;
169 -- The Employer Name check code is called when data is uploaded using api's directly
170
171 if p_employer_name <> hr_api.g_varchar2 then
172 hr_api.mandatory_arg_error
173 (p_api_name => l_proc,
174 p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PEM_NAME'),
175 p_argument_value => p_employer_name
176 );
177 end if;
178
179 VALIDATE_PREV_EMP(p_employer_type,
180 p_start_date,
181 p_end_date,
182 p_period_years,
183 p_period_months,
184 p_period_days,
185 NULL);
186
187
188 END UPDATE_PL_PREV_EMPLOYER;
189
190 PROCEDURE CREATE_PL_PREV_JOB(p_effective_date DATE,
191 p_previous_employer_id NUMBER,
192 p_start_date DATE,
193 p_end_date DATE,
194 p_period_years NUMBER,
195 p_period_months NUMBER,
196 p_period_days NUMBER,
197 p_employment_category VARCHAR2,
198 p_pjo_information1 VARCHAR2) IS
199
200 cursor csr_emp_cat is
201 select 1 from per_previous_jobs where previous_employer_id = p_previous_employer_id
202 and pjo_information1 = p_pjo_information1;
203
204
205 dup_emp_cat number;
206
207 BEGIN
208 /* Added for GSI Bug 5472781 */
209 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
210 hr_utility.trace('PL not installed.Leaving CREATE_PL_PREV_JOB');
211 return;
212 END IF;
213
214 dup_emp_cat := NULL;
215
216 open csr_emp_cat;
217 fetch csr_emp_cat into dup_emp_cat;
218 if csr_emp_cat%found then
219 hr_utility.set_message(800,'HR_PL_DUPLICATE_EMP_CATEGORY');
220 hr_utility.set_message_token('SERVICE',hr_general.decode_lookup('PL_TYPE_OF_SERVICE',p_pjo_information1));
221 hr_utility.raise_error;
222 end if;
223 close csr_emp_cat;
224
225 validate_dates(p_start_date,
226 p_end_date,
227 p_period_years,
228 p_period_months,
229 p_period_days,
230 p_pjo_information1);
231
232 END CREATE_PL_PREV_JOB;
233
234 PROCEDURE UPDATE_PL_PREV_JOB(p_effective_date DATE,
235 p_previous_job_id NUMBER,
236 p_start_date DATE,
237 p_end_date DATE,
238 p_period_years NUMBER,
239 p_period_months NUMBER,
240 p_period_days NUMBER,
241 p_employment_category VARCHAR2,
242 p_pjo_information1 VARCHAR2) IS
243
244 cursor csr_emp_catupd is
245 select ppj.pjo_information1 from per_previous_employers ppe, per_previous_jobs ppj
246 where
247 ppe.previous_employer_id = ppj.previous_employer_id and
248 ppj.previous_job_id <> p_previous_job_id and ppj.previous_employer_id in (
249 select previous_employer_id from per_previous_jobs where previous_job_id = p_previous_job_id);
250
251 dup_emp_cat per_previous_jobs.pjo_information1%TYPE;
252
253
254 BEGIN
255
256 /* Added for GSI Bug 5472781 */
257 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
258 hr_utility.trace('PL not installed.Leaving UPDATE_PL_PREV_JOB');
259 return;
260 END IF;
261
262 dup_emp_cat := NULL;
263
264 open csr_emp_catupd;
265 fetch csr_emp_catupd into dup_emp_cat;
266 close csr_emp_catupd;
267
268 if p_pjo_information1 = dup_emp_cat then
269 hr_utility.set_message(800,'HR_PL_DUPLICATE_EMP_CATEGORY');
270 hr_utility.set_message_token('SERVICE',hr_general.decode_lookup('PL_TYPE_OF_SERVICE',p_pjo_information1));
271 hr_utility.raise_error;
272 end if;
273
274 validate_dates(p_start_date,
275 p_end_date,
276 p_period_years,
277 p_period_months,
278 p_period_days,
279 p_pjo_information1);
280
281 END UPDATE_PL_PREV_JOB;
282
283 END PER_PL_PEM_INFO;
[Home] [Help]