[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_BATCH_YEA_PKG
Source
1 package body pay_kr_batch_yea_pkg as
2 /* $Header: pykrbyea.pkb 120.2 2006/06/12 09:08:17 ssutar noship $ */
3 --
4 -- Constants
5 --
6 -- Bug 4442484: Include 'S'kipped assacts in c_range_code
7 c_range_cursor constant varchar2(32767) :=
8 'select
9 distinct pds.person_id
10 from per_periods_of_service pds,
11 per_assignments_f asg,
12 pay_payroll_actions ppa
13 where ppa.payroll_action_id = :payroll_action_id
14 and asg.payroll_id = to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, ''PAYROLL_ID'', null))
15 and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
16 and pds.period_of_service_id = asg.period_of_service_id
17 and ppa.effective_date between pds.date_start and nvl(pds.final_process_date, ppa.effective_date)
18 and exists
19 ( select paa.assignment_id
20 from pay_assignment_actions paa
21 where paa.payroll_action_id = to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, ''BAL_ADJ_ACTION_ID'', null))
22 and paa.assignment_id = asg.assignment_id
23 and paa.action_status in (''C'', ''S'')
24 )
25 order by pds.person_id';
26 --
27 -- Global Variables
28 --
29 g_package varchar2(31) := ' pay_kr_batch_yea_pkg.';
30
31 type t_pact is record(
32 payroll_action_id number,
33 report_type pay_payroll_actions.report_type%TYPE,
34 report_qualifier pay_payroll_actions.report_qualifier%TYPE,
35 report_category pay_payroll_actions.report_category%TYPE,
36 business_group_id number,
37 effective_date date,
38 payroll_id number,
39 consolidation_set_id number,
40 bal_adj_action_id number);
41
42 g_pact t_pact;
43
44 g_debug boolean := hr_utility.debug_enabled;
45 --------------------------------------------------------------------------------
46 procedure range_code(
47 p_payroll_action_id in number,
48 p_sqlstr out NOCOPY varchar2)
49 --------------------------------------------------------------------------------
50 is
51 begin
52 initialization_code(p_payroll_action_id);
53
54 update pay_payroll_actions
55 set payroll_id = g_pact.payroll_id
56 ,consolidation_set_id = g_pact.consolidation_set_id
57 where payroll_action_id = p_payroll_action_id;
58
59 p_sqlstr := c_range_cursor;
60
61 end range_code;
62 --------------------------------------------------------------------------------
63 procedure initialization_code(p_payroll_action_id in number)
64 --------------------------------------------------------------------------------
65 is
66 begin
67 IF g_pact.payroll_action_id is null
68 or g_pact.payroll_action_id <> p_payroll_action_id
69 THEN
70 BEGIN
71 select ppa.payroll_action_id,
72 ppa.report_type,
73 ppa.report_qualifier,
74 ppa.report_category,
75 ppa.business_group_id,
76 ppa.effective_date,
77 to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'BAL_ADJ_ACTION_ID', null)) bal_adj_action_id,
78 to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'PAYROLL_ID', null)) payroll_id,
79 to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'CONSOLIDATION_SET_ID', null)) consolidation_set_id
80 --
81 into g_pact.payroll_action_id
82 ,g_pact.report_type
83 ,g_pact.report_qualifier
84 ,g_pact.report_category
85 ,g_pact.business_group_id
86 ,g_pact.effective_date
87 ,g_pact.bal_adj_action_id
88 ,g_pact.payroll_id
89 ,g_pact.consolidation_set_id
90 --
91 from pay_payroll_actions ppa
92 where ppa.payroll_action_id = p_payroll_action_id;
93 END;
94
95 END IF;
96 end initialization_code;
97
98 --------------------------------------------------------------------------------
99 procedure assignment_action_code(
100 p_payroll_action_id in number,
101 p_start_person_id in number,
102 p_end_person_id in number,
103 p_chunk_number in number)
104 --------------------------------------------------------------------------------
105 is
106 l_process boolean;
107 l_locking_action_id number;
108 l_locked_action_id number;
109 l_tax_unit_id number;
110 --
111 cursor csr_asg(p_payroll_id number)
112 IS
113 select
114 asg.assignment_id
115 --
116 from per_assignments_f asg,
117 per_periods_of_service pds,
118 pay_payroll_actions ppa
119 --
120 where ppa.payroll_action_id = p_payroll_action_id
121 and pds.person_id between p_start_person_id and p_end_person_id
122 and pds.business_group_id = ppa.business_group_id
123 and asg.period_of_service_id = pds.period_of_service_id
124 and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
125 and asg.payroll_id = p_payroll_id
126 --
127 -- Exclude if incomplete assacts exist
128 --
129 and not exists(
130 select /*+
131 ordered
132 use_nl(ppa2)
133 */ null
134 from pay_assignment_actions paa2,
135 pay_payroll_actions ppa2,
136 pay_action_classifications pac
137 where paa2.assignment_id = asg.assignment_id
138 and paa2.action_status not in ('C', 'S') -- Bug 4442484: 'S'kipped assact is not an errored one
139 and paa2.source_action_id is null
140 and ppa2.payroll_action_id = paa2.payroll_action_id
141 and ppa2.effective_date <= ppa.effective_date
142 and pac.action_type = ppa2.action_type
143 and pac.classification_name = 'SEQUENCED')
144 --
145 -- Exclude if Archival has already been processed.
146 --
147 and not exists(
148 select null
149 from pay_payroll_actions ppa4,
150 pay_assignment_actions paa4
151 where paa4.assignment_id = asg.assignment_id
152 and paa4.source_action_id is null
153 and ppa4.payroll_action_id = paa4.payroll_action_id
154 and ppa4.action_type IN ('X','B')
155 and ppa4.report_type = 'YEA'
156 and ppa4.report_qualifier = 'KR'
157 and ppa4.report_category in ('N', 'I')
158 and trunc(ppa4.effective_date, 'YYYY') = trunc(ppa.effective_date, 'YYYY'))
159 --
160 -- Include if BA has been processed
161 --
162 and exists (
163 select null
164 from pay_assignment_actions paa6
165 where paa6.payroll_action_id = to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'BAL_ADJ_ACTION_ID', null))
166 and paa6.assignment_id = asg.assignment_id
167 and paa6.action_status in ('C', 'S')) -- Bug 4442484: Include 'S'kipped assacts
168 --
169 for update of pds.period_of_service_id, asg.assignment_id;
170 -----------------------------------------------------------------------------------
171 Cursor csr_bal_asg(p_bal_adj_action_id number,
172 p_assignment_id number )
173 IS
174 Select asg.assignment_action_id
175 ,asg.tax_unit_id
176 from pay_assignment_actions asg
177 where asg.payroll_action_id = p_bal_adj_action_id
178 and asg.assignment_id = p_assignment_id
179 and asg.action_status in ('C', 'S') ; -- Bug 4442484: Include 'S'kipped assacts
180 ------------------------------------------------------------------------------------
181
182 begin
183 initialization_code(p_payroll_action_id);
184 --
185 for l_asg in csr_asg(g_pact.payroll_id)
186 loop
187 select pay_assignment_actions_s.nextval
188 into l_locking_action_id
189 from dual;
190 --
191 open csr_bal_asg(g_pact.bal_adj_action_id, l_asg.assignment_id);
192 fetch csr_bal_asg into l_locked_action_id, l_tax_unit_id;
193 close csr_bal_asg;
194 --
195 IF l_locked_action_id is null THEN
196 fnd_message.set_name('PAY', 'PAY_KR_INCOMP_ASSACT_EXISTS');
197 fnd_message.raise_error;
198 END IF;
199
200 hr_nonrun_asact.insact(l_locking_action_id,
201 l_asg.assignment_id,
202 p_payroll_action_id,
203 p_chunk_number,
204 l_tax_unit_id);
205
206 hr_nonrun_asact.insint( LOCKINGACTID => l_locking_action_id,
207 LOCKEDACTID => l_locked_action_id );
208
209 end loop;
210 end assignment_action_code;
211 --------------------------------------------------------------------------------
212 procedure reyea_assignment_action_code(
213 p_payroll_action_id in number,
214 p_start_person_id in number,
215 p_end_person_id in number,
216 p_chunk_number in number)
217 --------------------------------------------------------------------------------
218 is
219 l_process boolean;
220 l_locking_action_id number;
221 l_locked_action_id number;
222 l_tax_unit_id number;
223 --
224 cursor csr_asg(p_payroll_id number)
225 IS
226 select
227 asg.assignment_id
228 --
229 from per_assignments_f asg,
230 per_periods_of_service pds,
231 pay_payroll_actions ppa
232 --
233 where ppa.payroll_action_id = p_payroll_action_id
234 and pds.person_id between p_start_person_id and p_end_person_id
235 and pds.business_group_id = ppa.business_group_id
236 and asg.period_of_service_id = pds.period_of_service_id
237 and ppa.effective_date between asg.effective_start_date and asg.effective_end_date
238 and asg.payroll_id = p_payroll_id
239 --
240 -- Exclude if incomplete assacts exist
241 --
242 and not exists(
243 select /*+
244 ordered
245 use_nl(ppa2)
246 */ null
247 from pay_assignment_actions paa2,
248 pay_payroll_actions ppa2,
249 pay_action_classifications pac
250 where paa2.assignment_id = asg.assignment_id
251 and paa2.action_status not in ('C', 'S') -- Bug 4442484: 'S'kipped assact is not an errored one
252 and paa2.source_action_id is null
253 and ppa2.payroll_action_id = paa2.payroll_action_id
254 and ppa2.effective_date <= ppa.effective_date
255 and pac.action_type = ppa2.action_type
256 and pac.classification_name = 'SEQUENCED')
257 --
258 -- Include if Normal / Interim archive has been processed.
259 --
260 and exists(
261 select null
262 from pay_payroll_actions ppa4,
263 pay_assignment_actions paa4
264 where paa4.assignment_id = asg.assignment_id
265 and paa4.source_action_id is null
266 and ppa4.payroll_action_id = paa4.payroll_action_id
267 and ppa4.action_type IN ('X','B')
268 and ppa4.report_type = 'YEA'
269 and ppa4.report_qualifier = 'KR'
270 and ppa4.report_category in ('N', 'I')
271 and trunc(ppa4.effective_date, 'YYYY') = trunc(ppa.effective_date, 'YYYY'))
272 --
273 -- Exclude if future YEA in the same calendar year exists.
274 --
275 and not exists(
276 select null
277 from pay_payroll_actions ppa3,
278 pay_assignment_actions paa3
279 where paa3.assignment_id = asg.assignment_id
280 and paa3.source_action_id is null
281 and ppa3.payroll_action_id = paa3.payroll_action_id
282 and ppa3.effective_date > ppa.effective_date
283 and ppa3.effective_date < add_months(trunc(ppa.effective_date, 'YYYY'), 12)
284 and ppa3.action_type IN ('X','B')
285 and ppa3.report_type = 'YEA'
286 and ppa3.report_qualifier = 'KR')
287 --
288 -- Include if BA has been processed
289 --
290 and exists (
291 select null
292 from pay_assignment_actions paa6
293 where paa6.payroll_action_id = to_number(pay_kr_ff_functions_pkg.get_legislative_parameter(ppa.payroll_action_id, 'BAL_ADJ_ACTION_ID', null))
294 and paa6.assignment_id = asg.assignment_id
295 and paa6.action_status in ('C', 'S')) -- Bug 4442484: Include 'S'kipped assacts
296 --
297 for update of pds.period_of_service_id, asg.assignment_id;
298
299 Cursor csr_bal_asg(p_bal_adj_action_id number,
300 p_assignment_id number )
301 IS
302 Select asg.assignment_action_id
303 ,asg.tax_unit_id
304 from pay_assignment_actions asg
305 where asg.payroll_action_id = p_bal_adj_action_id
306 and asg.assignment_id = p_assignment_id
307 and asg.action_status in ('C', 'S'); -- Bug 4442484: Include 'S'kipped assacts
308
309 begin
310 initialization_code(p_payroll_action_id);
311 --
312 for l_asg in csr_asg(g_pact.payroll_id) loop
313 select pay_assignment_actions_s.nextval
314 into l_locking_action_id
315 from dual;
316 --
317 open csr_bal_asg(g_pact.bal_adj_action_id, l_asg.assignment_id);
318 fetch csr_bal_asg into l_locked_action_id, l_tax_unit_id;
319 close csr_bal_asg;
320
321 IF l_locked_action_id is null THEN
322 fnd_message.set_name('PAY', 'PAY_KR_INCOMP_ASSACT_EXISTS');
323 fnd_message.raise_error;
324 END IF;
325
326 hr_nonrun_asact.insact(l_locking_action_id,
327 l_asg.assignment_id,
328 p_payroll_action_id,
329 p_chunk_number,
330 l_tax_unit_id);
331
332 hr_nonrun_asact.insint( LOCKINGACTID => l_locking_action_id,
333 LOCKEDACTID => l_locked_action_id );
334
335 end loop;
336 end reyea_assignment_action_code;
337 --------------------------------------------------------------------------------
338 procedure archive_code(
339 p_assignment_action_id in number,
340 p_effective_date in date)
341 --------------------------------------------------------------------------------
342 is
343 l_proc_name varchar2(61) := 'archive_code';
344 l_assignment_id number;
345 l_bal_adj_asg_action_id number;
346 l_archive_type_used varchar2(10); -- Bug 5225198
347
348 Cursor csr_bal_asg(p_bal_adj_action_id number,
349 p_assignment_action_id number )
350 IS
351 Select asg_b.assignment_action_id
352 ,asg_b.assignment_id
353 from pay_assignment_actions asg
354 ,pay_assignment_actions asg_b
355 where asg_b.payroll_action_id = p_bal_adj_action_id
356 and asg_b.assignment_id = asg.assignment_id
357 and asg.assignment_action_id = p_assignment_action_id
358 and asg_b.action_status in ('C', 'S') ; -- Bug 4442484: Include 'S'kipped assacts
359 begin
360 --
361 if g_debug then
362 hr_utility.set_location(l_proc_name, 10);
363 end if;
364 --
365 open csr_bal_asg(g_pact.bal_adj_action_id, p_assignment_action_id);
366 fetch csr_bal_asg into l_bal_adj_asg_action_id, l_assignment_id;
367 close csr_bal_asg;
368 --
369 if g_debug then
370 hr_utility.set_location(l_proc_name, 20);
371 hr_utility.trace('Assignment Id : '||to_char(l_assignment_id));
372 hr_utility.trace('Balance Adjustment Assignment Action Id : '||to_char(l_bal_adj_asg_action_id));
373 end if;
374 --
375 -- Bug 5225198
376 l_archive_type_used := pay_kr_ff_functions_pkg.get_legislative_parameter(
377 g_pact.payroll_action_id, 'ARCHIVE_TYPE', 'AAC');
378
379 pay_kr_yea_pkg.process_assignment(
380 p_validate => false,
381 p_business_group_id => g_pact.business_group_id,
382 p_assignment_id => l_assignment_id,
383 p_assignment_action_id => p_assignment_action_id,
384 p_bal_asg_action_id => l_bal_adj_asg_action_id,
385 p_report_type => g_pact.report_type,
386 p_report_qualifier => g_pact.report_qualifier,
387 p_report_category => g_pact.report_category,
388 p_effective_date => g_pact.effective_date,
389 p_payroll_id => g_pact.payroll_id,
390 p_consolidation_set_id => g_pact.consolidation_set_id,
391 p_archive_type_used => l_archive_type_used); -- Bug 5036734
392 --
393 if g_debug then
394 hr_utility.set_location(l_proc_name, 30);
395 end if;
396 --
397 end archive_code;
398 --
399 end pay_kr_batch_yea_pkg;