[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_NPA_PKG
Source
1 package body pay_kr_npa_pkg as
2 /* $Header: pykrnpa.pkb 120.1 2005/12/29 22:05:23 ssutar noship $ */
3 --
4 -- Constants
5 --
6 l_package varchar2(31) := ' pay_kr_npa_pkg.';
7 g_debug boolean := hr_utility.debug_enabled;
8 --
9 -- Global Variables
10 --
11 TYPE t_pact IS RECORD(
12 payroll_action_id NUMBER,
13 report_type pay_payroll_actions.report_type%TYPE,
14 report_qualifier pay_payroll_actions.report_qualifier%TYPE,
15 report_category pay_payroll_actions.report_category%TYPE,
16 business_group_id NUMBER,
17 effective_date date,
18 -- Bug 3506172
19 bp_np_number hr_organization_information.org_information1%type);
20 -- End of 3506172
21 g_pact t_pact;
22 --------------------------------------------------------------------------------+
23 -- Range cursor returns the ids of the assignments to be archived
24 --------------------------------------------------------------------------------+
25 PROCEDURE range_code(
26 p_payroll_action_id IN NUMBER,
27 p_sqlstr OUT NOCOPY VARCHAR2)
28 IS
29 l_proc_name VARCHAR2(100) := l_package || 'range_code';
30 BEGIN
31 if g_debug then
32 hr_utility.set_location(l_proc_name, 10);
33 end if;
34
35 p_sqlstr :=
36 'SELECT DISTINCT ppf.person_id
37 FROM pay_payroll_actions ppa
38 ,per_people_f ppf
39 WHERE ppa.payroll_action_id = :p_payroll_action_id
40 AND ppa.business_group_id = ppf.business_group_id
41 ORDER BY ppf.person_id';
42
43 if g_debug then
44 hr_utility.set_location(l_proc_name, 20);
45 end if;
46 END range_code;
47 --------------------------------------------------------------------------------+
48 -- Cache ARCHIVE payroll action parameters
49 --------------------------------------------------------------------------------+
50 PROCEDURE initialization_code(
51 p_payroll_action_id IN NUMBER)
52 IS
53 l_proc_name VARCHAR2(100) := l_package || 'initialization_code';
54 BEGIN
55 if g_debug then
56 hr_utility.set_location(l_proc_name, 10);
57 end if;
58
59 SELECT ppa.payroll_action_id,
60 ppa.report_type,
61 ppa.report_qualifier,
62 ppa.report_category,
63 ppa.business_group_id,
64 ppa.effective_date,
65 pay_core_utils.get_parameter('BP_NP_NUMBER',ppa.legislative_parameters) -- Bug 3506172
66 INTO g_pact
67 FROM pay_payroll_actions ppa
68 WHERE ppa.payroll_action_id = p_payroll_action_id;
69
70 if g_debug then
71 hr_utility.set_location(l_proc_name, 20);
72 end if;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 hr_utility.set_location('Error in initialization code ',10);
77 RAISE;
78 END initialization_code;
79
80 --------------------------------------------------------------------------------+
81 -- Creates assignment action id for all the valid person id's in
82 -- the range selected by the Range code.
83 --------------------------------------------------------------------------------+
84 PROCEDURE assignment_action_code(
85 p_payroll_action_id IN NUMBER,
86 p_start_person_id IN NUMBER,
87 p_end_person_id IN NUMBER,
88 p_chunk_number IN NUMBER)
89 IS
90 l_proc_name VARCHAR2(100) := l_package || 'assignment_action_code';
91 l_locking_action_id NUMBER;
92 l_start_date DATE;
93 l_end_date DATE;
94 CURSOR csr_date IS
95 SELECT to_date('01-11-'||to_char(to_number(to_char(fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',bppa.legislative_parameters)),'YYYY'))-1),'DD-MM-YYYY')
96 ,fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORTED_DATE',bppa.legislative_parameters))
97 FROM pay_payroll_actions bppa
98 WHERE bppa.payroll_action_id = p_payroll_action_id;
99
100 ------------------------------------------------------------------------------
101 -- Cursor altered for fix 2928733
102 -- Added distinct to the SELECT clause
103 ------------------------------------------------------------------------------
104 CURSOR csr_asg(p_bp_np_number hr_organization_information.org_information1%type
105 ,p_start_date in date
106 ,p_end_date in date)
107 IS
108 SELECT distinct ass.assignment_id
109 ,ass.establishment_id
110 FROM pay_payroll_actions bppa
111 ,per_assignments_f ass
112 ,hr_organization_information hoi
113 ,hr_organization_units hou
114 WHERE bppa.payroll_action_id = p_payroll_action_id
115 AND ass.person_id BETWEEN p_start_person_id AND p_end_person_id
116 -- Bug 3506172
117 AND ass.establishment_id = hoi.organization_id
118 AND hou.business_group_id = bppa.business_group_id
119 AND hoi.organization_id = hou.organization_id
120 AND hoi.org_information_context = 'KR_NP_INFORMATION'
121 AND hoi.org_information1 = p_bp_np_number
122 -- End of 3506172
123 -- 3453776 : This WHERE clause has been moved from the inner SELECT
124 AND bppa.effective_date between ass.effective_start_date and ass.effective_end_date
125 --
126 AND EXISTS ( SELECT NULL
127 FROM pay_payroll_actions ppa
128 ,pay_assignment_actions paa
129 WHERE ppa.payroll_action_id = paa.payroll_action_id
130 AND paa.assignment_id = ass.assignment_id
131 AND ppa.effective_date BETWEEN p_start_date
132 AND p_end_date
133 AND ppa.action_type in ('R','Q')
134 AND ppa.action_status = 'C'
135 )
136 AND NOT EXISTS( SELECT NULL
137 FROM pay_payroll_actions ppa4
138 ,pay_assignment_actions paa4
139 WHERE paa4.assignment_id = ass.assignment_id
140 AND paa4.source_action_id IS NULL
141 AND paa4.payroll_action_id = ppa4.payroll_action_id
142 AND ppa4.action_type = 'X'
143 AND ppa4.report_type = 'NPA'
144 AND ppa4.report_qualifier = 'KR'
145 AND ppa4.report_category = 'A'
146 AND trunc(ppa4.effective_date,'YYYY') = trunc(bppa.effective_date,'YYYY')
147 UNION ALL -- 4660204
148 SELECT NULL
149 FROM per_people_extra_info pei
150 WHERE pei.person_id = ass.person_id
151 AND pei.information_type = 'PER_KR_NATIONAL_PENSION_INFO'
152 AND pei.pei_information7 IN ('03')
153 );
154
155 BEGIN
156 if g_debug then
157 hr_utility.set_location(l_proc_name, 10);
158 end if;
159
160 initialization_code(p_payroll_action_id);
161
162 OPEN csr_date;
163 FETCH csr_date INTO l_start_date,l_end_date;
164 CLOSE csr_date;
165 FOR l_asg IN csr_asg(g_pact.bp_np_number,l_start_date,l_end_date) -- Bug 3506172
166 LOOP
167 SELECT pay_assignment_actions_s.nextval
168 INTO l_locking_action_id
169 FROM dual;
170 hr_nonrun_asact.insact(lockingactid => l_locking_action_id,
171 assignid => l_asg.assignment_id,
172 pactid => p_payroll_action_id,
173 chunk => p_chunk_number,
174 greid => l_asg.establishment_id,
175 prepayid => null,
176 status => 'U');
177 END LOOP;
178
179 if g_debug then
180 hr_utility.set_location(l_proc_name, 20);
181 end if;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 hr_utility.set_location('Error in assignment action code ',10);
186 RAISE;
187 END assignment_action_code;
188 --------------------------------------------------------------------------------+
189 -- Archives item
190 --------------------------------------------------------------------------------+
191 PROCEDURE archive_item
192 ( p_item IN ff_user_entities.user_entity_name%TYPE,
193 p_context1 IN pay_assignment_actions.assignment_action_id%TYPE,
194 p_value IN OUT NOCOPY ff_archive_items.value%TYPE)
195 IS
196 CURSOR csr_get_user_entity_id(c_user_entity_name IN VARCHAR2)
197 IS
198 SELECT fue.user_entity_id,
199 dbi.data_type
200 FROM ff_user_entities fue,
201 ff_database_items dbi
202 WHERE user_entity_name = c_user_entity_name
203 AND fue.user_entity_id = dbi.user_entity_id;
204 l_user_entity_id ff_user_entities.user_entity_id%TYPE;
205 l_archive_item_id ff_archive_items.archive_item_id%TYPE;
206 l_data_type ff_database_items.data_type%TYPE;
207 l_object_version_number ff_archive_items.object_version_number%TYPE;
208 l_some_warning BOOLEAN;
209 BEGIN
210 if g_debug then
211 hr_utility.set_location('Entering : archive_item',1);
212 end if;
213
214 OPEN csr_get_user_entity_id (p_item);
215 FETCH csr_get_user_entity_id INTO l_user_entity_id,l_data_type;
216 IF csr_get_user_entity_id%found THEN
217 CLOSE csr_get_user_entity_id;
218 ff_archive_api.create_archive_item
219 (p_validate => false -- boolean in default
220 ,p_archive_item_id => l_archive_item_id -- NUMBER out
221 ,p_user_entity_id => l_user_entity_id -- NUMBER in
222 ,p_archive_value => p_value -- VARCHAR2 in
223 ,p_archive_type => 'AAP' -- VARCHAR2 in default
224 ,p_action_id => p_context1 -- NUMBER in
225 ,p_legislation_code => 'KR' -- VARCHAR2 in
226 ,p_object_version_number => l_object_version_number -- NUMBER out
227 ,p_some_warning => l_some_warning); -- boolean out
228 ELSE
229 CLOSE csr_get_user_entity_id;
230 if g_debug then
231 hr_utility.set_location('User entity not found :'||p_item,20);
232 end if;
233 END IF;
234
235 if g_debug then
236 hr_utility.set_location('Leaving : archive_item',1);
237 end if;
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 IF csr_get_user_entity_id%isopen THEN
242 CLOSE csr_get_user_entity_id;
243 hr_utility.set_location('closing..',117);
244 END IF;
245
246 hr_utility.set_location('Error in archive_item',20);
247 RAISE;
248 END archive_item;
249
250 --------------------------------------------------------------------------------+
251 -- Archive code selects the items to be archived.
252 --------------------------------------------------------------------------------+
253 PROCEDURE archive_code(
254 p_assignment_action_id IN NUMBER,
255 p_effective_date IN DATE)
256 IS
257 l_proc_name VARCHAR2(100) := l_package || 'archive_code';
258 l_assignment_id NUMBER;
259 l_payroll_id NUMBER;
260 -- Bug 3506172
261 l_est_id NUMBER ;
262 -- End of 3506172
263 TYPE t_arch_rec IS RECORD(item VARCHAR2(50)
264 ,value VARCHAR2(1000));
265 TYPE t_arch_tab IS TABLE OF t_arch_rec INDEX BY BINARY_INTEGER;
266 l_arch_tab t_arch_tab;
267 CURSOR csr_get_context_values
268 IS
269 SELECT paa.assignment_id,
270 pa.payroll_id,
271 pa.establishment_id -- Bug 3506172
272 FROM per_assignments_f pa,
273 pay_assignment_actions paa
274 WHERE paa.assignment_action_id = p_assignment_action_id
275 AND pa.assignment_id = paa.assignment_id
276 AND g_pact.effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date;
277
278 --employee details cursor
279 CURSOR csr_employee_details
280 IS
281 SELECT hoi.org_information1 business_place_code
282 ,pp.national_identifier registration_code
283 ,hoi.org_information2 branch_code
284 ,hoi1.org_information14 computerization_code
285 FROM pay_assignment_actions paa
286 ,per_assignments_f ass
287 ,per_people_f pp
288 ,hr_organization_information hoi
289 ,hr_organization_information hoi1
290 WHERE paa.assignment_action_id = p_assignment_action_id
291 AND paa.assignment_id = ass.assignment_id
292 AND ass.person_id = pp.person_id
293 AND ass.establishment_id = hoi.organization_id
294 AND hoi.org_information_context = 'KR_NP_INFORMATION'
295 AND ass.establishment_id = hoi1.organization_id
296 AND hoi1.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
297 AND p_effective_date BETWEEN ass.effective_start_date AND ass.effective_end_date
298 AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
299
300 BEGIN
301 if g_debug then
302 hr_utility.set_location(l_proc_name, 10);
303 end if;
304
305 OPEN csr_get_context_values;
306 FETCH csr_get_context_values INTO l_assignment_id,l_payroll_id, l_est_id; -- Bug 3506172
307 CLOSE csr_get_context_values;
308
309 if g_debug then
310 hr_utility.set_location(l_proc_name, 20);
311 end if;
312
313 pay_archive.g_context_values.name(1) := 'BUSINESS_GROUP_ID';
314 pay_archive.g_context_values.value(1) := to_char(g_pact.business_group_id);
315 pay_archive.g_context_values.name(2) := 'PAYROLL_ID';
316 pay_archive.g_context_values.value(2) := to_char(l_payroll_id);
317 pay_archive.g_context_values.name(3) := 'PAYROLL_ACTION_ID';
318 pay_archive.g_context_values.value(3) := to_char(g_pact.payroll_action_id);
319 pay_archive.g_context_values.name(4) := 'ASSIGNMENT_ID';
320 pay_archive.g_context_values.value(4) := to_char(l_assignment_id);
321 pay_archive.g_context_values.name(5) := 'ASSIGNMENT_ACTION_ID';
322 pay_archive.g_context_values.value(5) := to_char(p_assignment_action_id);
323 pay_archive.g_context_values.name(6) := 'DATE_EARNED';
324 pay_archive.g_context_values.value(6) := fnd_date.date_to_canonical(g_pact.effective_date);
325 pay_archive.g_context_values.name(7) := 'TAX_UNIT_ID';
326 pay_archive.g_context_values.value(7) := to_char(l_est_id); -- Bug 3506172
327 pay_archive.g_context_values.sz := 7;
328
329 if g_debug then
330 hr_utility.set_location(l_proc_name, 30);
331 end if;
332
333 /* Start of Archiving Employee Details */
334 -----------------------------------------+
335 -- note : the fetch order FROM the cursor
336 -- should be same as the order
337 -- defined in the pl/sql table below
338 -----------------------------------------+
339 l_arch_tab.delete;
340 l_arch_tab(1).item := 'X_KR_NPA_BUSINESS_PLACE_CODE';
341 l_arch_tab(2).item := 'X_KR_NPA_REGISTRATION_NUMBER';
342 l_arch_tab(3).item := 'X_KR_NPA_BRANCH_CODE';
343 l_arch_tab(4).item := 'X_KR_NPA_COMPUTERIZATION_CODE';
344
345 if g_debug then
346 hr_utility.set_location('Entering : Archiving emp Details ',1);
347 hr_utility.set_location('Assignments action id is '||p_assignment_action_id,2);
348 end if;
349
350 OPEN csr_employee_details ;
351 LOOP
352 FETCH csr_employee_details
353 INTO l_arch_tab(1).value,
354 l_arch_tab(2).value,
355 l_arch_tab(3).value,
356 l_arch_tab(4).value ;
357 EXIT WHEN csr_employee_details%NOTFOUND;
358
359 if g_debug then
360 hr_utility.set_location('Creating Archive Item ',3);
361 end if;
362
363 FOR i IN 1..l_arch_tab.count
364 LOOP
365 archive_item(p_item => l_arch_tab(i).item
366 ,p_context1 => p_assignment_action_id
367 ,p_value => l_arch_tab(i).value);
368 END LOOP;
369 END LOOP;
370 CLOSE csr_employee_details;
371
372 if g_debug then
373 hr_utility.set_location('Exiting : Archiving emp Details ',200);
374 end if;
375
376 EXCEPTION
377 WHEN OTHERS THEN
378 hr_utility.set_location('Error in archiving emp details ',10);
379 RAISE;
380 /* End of Archiving Employee Details */
381 END archive_code;
382
383 FUNCTION return_header(
384 P_lookup_type IN VARCHAR2,
385 p_lookup_code IN VARCHAR2 )
386 RETURN VARCHAR2
387 IS
388 l_meaning VARCHAR2(80);
389 no_lookup EXCEPTION;
390 CURSOR csr_hr_lookups
391 IS
392 SELECT meaning
393 FROM hr_lookups
394 WHERE lookup_type = p_lookup_type
395 AND lookup_code = p_lookup_code;
396 BEGIN
397 OPEN csr_hr_lookups ;
398 FETCH csr_hr_lookups INTO l_meaning;
399 CLOSE csr_hr_lookups ;
400
401 IF l_meaning IS NOT NULL THEN
402 RETURN l_meaning;
403 ELSE
404 RAISE no_lookup;
405 END IF;
406 EXCEPTION
407 WHEN OTHERS THEN
408 hr_utility.set_location('Error in return_header ',10);
409 RAISE;
410 END;
411
412 END pay_kr_npa_pkg;