[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_PAYSLIP_ARCHIVE
Source
1 PACKAGE BODY pay_jp_payslip_archive AS
2 /* $Header: pyjpparc.pkb 120.8 2011/01/19 09:05:50 keyazawa ship $ */
3 --
4 -- Constants
5 --
6 c_package CONSTANT VARCHAR2(31) := 'pay_jp_payslip_archive.';
7 -----------------------------------------------------------------------------
8 -- Set Defined Balance Ids
9 -----------------------------------------------------------------------------
10 /*
11 Corrected balance dimension as _ASG_YTD instead of _ASG_YTD_RUN to fix Bug 5401179
12 */
13 c_ytd_allowance_def_bal_id CONSTANT NUMBER := hr_jp_id_pkg.defined_balance_id(
14 'B_YEA_ERN',
15 '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01',
16 NULL, 'JP');
17 c_ytd_sal_taxable_def_bal_id CONSTANT NUMBER := hr_jp_id_pkg.defined_balance_id(
18 'B_YEA_TXBL_ERN_MONEY',
19 '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01',
20 NULL, 'JP');
21 c_ytd_mat_taxable_def_bal_id CONSTANT NUMBER := hr_jp_id_pkg.defined_balance_id(
22 'B_YEA_TXBL_ERN_KIND',
23 '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01',
24 NULL, 'JP');
25 c_ytd_si_prem_def_bal_id CONSTANT NUMBER := hr_jp_id_pkg.defined_balance_id(
26 'B_YEA_SAL_DCT_SI_PREM',
27 '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01',
28 NULL, 'JP');
29 c_ytd_itax_def_bal_id CONSTANT NUMBER := hr_jp_id_pkg.defined_balance_id(
30 'B_YEA_WITHHOLD_ITX',
31 '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01',
32 NULL, 'JP');
33 c_ytd_yea_itax_def_bal_id CONSTANT NUMBER := hr_jp_id_pkg.defined_balance_id(
34 'B_YEA_TAX_PAY',
35 '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01',
36 NULL, 'JP');
37 --c_net_pay_bal_id CONSTANT NUMBER := hr_jp_id_pkg.balance_type_id('B_NET_PAY', null, 'JP');
38 c_net_pay_bal_id CONSTANT NUMBER := hr_jp_id_pkg.balance_type_id('B_PAYSLIP_NET_PAY', null, 'JP');
39 --
40 -- Global Variables (Concurrent Program parameters)
41 --
42 g_arch_payroll_action_id NUMBER;
43 g_bg_id NUMBER;
44 g_effective_date date;
45 --
46 g_payroll_id NUMBER;
47 g_consolidation_set_id number;
48 g_payment_date DATE;
49 g_payslip_label pay_action_information.action_information1%type;
50 -- +--------------------------------------------------------------------------+
51 -- |-----------------------------< init_globals >-----------------------------|
52 -- +--------------------------------------------------------------------------+
53 --
54 -- 2786851. created
55 -- Call this in the following procedures.
56 -- PAY_REPORT_FORMAT_MAPPINGS_F.RANGE_CODE
57 -- PAY_REPORT_FORMAT_MAPPINGS_F.ASSIGNMENT_ACTION_CODE
58 -- PAY_REPORT_FORMAT_MAPPINGS_F.INITIALIZATION_CODE
59 --
60 procedure init_globals(p_arch_payroll_action_id in number)
61 is
62 c_proc CONSTANT VARCHAR2(61) := c_package || 'init_globals';
63 l_legislative_parameters pay_payroll_actions.legislative_parameters%type;
64 l_start_pos number;
65 l_end_pos number;
66 begin
67 hr_utility.set_location('Entering ' || c_proc, 10);
68 --
69 -- This global variables deriving routine is only kicked
70 -- when global variables are not set.
71 -- Once populated, the following code is skipped.
72 --
73 if g_arch_payroll_action_id is null then
74 select business_group_id,
75 effective_date,
76 legislative_parameters
77 into g_bg_id,
78 g_effective_date,
79 l_legislative_parameters
80 from pay_payroll_actions
81 where payroll_action_id = p_arch_payroll_action_id;
82 --
83 -- Better to change how to derive PAYSLIP_LABEL from legislative parameters.
84 -- 1. Replace all "spaces" set in PAYSLIP_LABEL legislative parameter to "underscores" when issueing concurrent programs.
85 -- e.g. <Payslip Label> : Monthly Pay
86 -- <Payslip Label Hidden> : PAYSLIP_LABEL=MONTHLY_PAY
87 -- 2. All "underscores" are replaced back to "spaces"
88 -- e.g. g_payslip_label := replace(pay_core_utils.get_parameter('PAYSLIP_LABEL', l_legislative_parameters), '_', ' ');
89 -- Future enhancement.
90 --
91 g_arch_payroll_action_id := p_arch_payroll_action_id;
92 g_payroll_id := to_number(pay_core_utils.get_parameter('PAYROLL', l_legislative_parameters));
93 g_consolidation_set_id := to_number(pay_core_utils.get_parameter('CONSOLIDATION', l_legislative_parameters));
94 g_payment_date := fnd_date.canonical_to_date(pay_core_utils.get_parameter('PAYMENT_DATE', l_legislative_parameters));
95 --
96 l_start_pos := instr(l_legislative_parameters, 'PAYSLIP_LABEL');
97 if l_start_pos > 0 then
98 l_start_pos := l_start_pos + length('PAYSLIP_LABEL') + 1;
99 l_end_pos := instr(l_legislative_parameters, 'PAYMENT_DATE') - 2;
100 g_payslip_label := substr(l_legislative_parameters, l_start_pos, l_end_pos - l_start_pos + 1);
101 end if;
102 --
103 hr_utility.trace('g_arch_payroll_action_id : ' || g_arch_payroll_action_id);
104 hr_utility.trace('g_bg_id : ' || g_bg_id);
105 hr_utility.trace('g_effective_date : ' || g_effective_date);
106 hr_utility.trace('g_payroll_id : ' || g_payroll_id);
107 hr_utility.trace('g_consolidation_set_id : ' || g_consolidation_set_id);
108 hr_utility.trace('g_payment_date : ' || g_payment_date);
109 hr_utility.trace('g_payslip_label : ' || g_payslip_label);
110 end if;
111 --
112 hr_utility.set_location('Leaving ' || c_proc, 20);
113 end init_globals;
114 -- +--------------------------------------------------------------------------+
115 -- |-----------------------< setup_payment_information >----------------------|
116 -- +--------------------------------------------------------------------------+
117 --
118 -- This procedure is to archive payment information which includes payment date
119 -- and payslip label. These data are defined at archiver process.
120 --
121 PROCEDURE setup_payment_information(p_arch_assignment_action_id IN NUMBER)
122 IS
123 c_proc CONSTANT VARCHAR2(61) := c_package || 'setup_payment_information';
124 c_action_information_category constant pay_action_information.action_information_category%type := 'EMPLOYEE PAYMENT INFORMATION';
125 l_pay_date_disp pay_action_information.action_information1%type;
126 l_payslip_name pay_action_information.action_information1%type;
127 l_act_info_rec pay_emp_action_arch.act_info_rec;
128 --
129 CURSOR csr_element_set_name(cp_arch_assignment_action_id NUMBER) IS
130 SELECT pes.element_set_name
131 FROM pay_element_sets_vl pes,
132 pay_payroll_actions rppa, -- run pact
133 pay_assignment_actions rpaa, -- run assact
134 pay_action_interlocks rpai -- run interlock by archive assact
135 WHERE rpai.locking_action_id = cp_arch_assignment_action_id
136 AND rpaa.assignment_action_id = rpai.locked_action_id
137 AND rppa.payroll_action_id = rpaa.payroll_action_id
138 -- Element Set is available only when "Run"
139 -- waste of resource to check action_type.
140 -- AND rppa.action_type = 'R'
141 AND pes.element_set_id = rppa.element_set_id
142 ORDER BY rpaa.action_sequence desc;
143 BEGIN
144 hr_utility.set_location('Entering ' || c_proc, 10);
145 --
146 -- Better to store this with canonical format and apply the following conversion
147 -- in framework, but there're many limitations for current online payslip.
148 -- Future enhancement required.
149 --
150 l_pay_date_disp := to_char(g_payment_date, fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK'));
151 l_payslip_name := g_payslip_label;
152 --
153 -- When PAYSLIP_LABEL is not set when running concurrent program,
154 -- concatenate the element_set_name of runs locked by current archive assact
155 -- order by action_sequence desc.
156 --
157 IF l_payslip_name IS NULL THEN
158 FOR l_element_set_name_rec IN csr_element_set_name(p_arch_assignment_action_id) LOOP
159 if l_payslip_name is null then
160 l_payslip_name := l_element_set_name_rec.element_set_name;
161 else
162 l_payslip_name := l_element_set_name_rec.element_set_name || ' ' || l_payslip_name;
163 end if;
164 END LOOP;
165 END IF;
166 --
167 -- l_payslip_name can be null when
168 -- 1) PAYSLIP_LABEL legislative parameter is null
169 -- 2) element_set is not set
170 --
171 l_payslip_name := trim(l_pay_date_disp || ' ' || l_payslip_name);
172 --
173 hr_utility.trace('pay_date_disp : ' || l_pay_date_disp);
174 hr_utility.trace('payslip_name : "' || l_payslip_name || '"');
175 --
176 -- Stack information into global variable
177 --
178 l_act_info_rec.action_info_category := c_action_information_category;
179 l_act_info_rec.act_info1 := l_pay_date_disp;
180 l_act_info_rec.act_info2 := l_payslip_name;
181 -- Note the index starts from "0".
182 pay_emp_action_arch.lrr_act_tab(pay_emp_action_arch.lrr_act_tab.count) := l_act_info_rec;
183 --
184 hr_utility.set_location('Leaving ' || c_proc, 20);
185 END setup_payment_information;
186 -- +--------------------------------------------------------------------------+
187 -- |-----------------------< setup_element_information >----------------------|
188 -- +--------------------------------------------------------------------------+
189 --
190 -- This procedure is to archive statutory element information.
191 -- These archived data is displayed on Earnings and Deductions regions.
192 --
193 PROCEDURE setup_element_information(p_arch_assignment_action_id IN NUMBER)
194 IS
195 c_proc CONSTANT VARCHAR2(61) := c_package || 'setup_element_information';
196 c_action_information_category constant pay_action_information.action_information_category%type := 'JP ELEMENT INFORMATION';
197 -- l_exists VARCHAR2(1);
198 -- l_payment_type hr_lookups.lookup_code%type;
199 l_act_info_rec pay_emp_action_arch.act_info_rec;
200 --
201 -- Seems to be better to store "MEANING" information
202 -- for the input_values with LOOKUP_TYPE.
203 --
204 /* Removed the Hint in the below cursor as per Bug# 4674234. */
205
206 CURSOR csr_element_info(cp_arch_assignment_action_id NUMBER) IS
207 SELECT /*+ ORDERED */
208 rpaa.assignment_id,
209 prr.element_type_id,
210 NVL(pettl.reporting_name, pettl.element_name) reporting_name,
211 prrv.input_value_id,
212 prrv.result_value,
213 decode(pbf.scale, 1, 'E', -1, 'D', 'O') payment_type
214 FROM pay_action_interlocks pai, -- run interlock by archive assact
215 pay_assignment_actions rpaa, -- run assact
216 pay_payroll_actions rppa, -- run pact
217 pay_run_results prr,
218 pay_run_result_values prrv,
219 pay_balance_feeds_f pbf,
220 pay_element_types_f_tl pettl
221 WHERE pai.locking_action_id = cp_arch_assignment_action_id
222 AND rpaa.assignment_action_id = pai.locked_action_id
223 AND rppa.payroll_action_id = rpaa.payroll_action_id
224 AND rppa.action_type in ('R', 'Q', 'B')
225 AND prr.assignment_action_id = rpaa.assignment_action_id
226 AND prr.status IN ('P', 'PA')
227 AND prrv.run_result_id = prr.run_result_id
228 AND prrv.result_value IS NOT NULL
229 and pbf.balance_type_id = c_net_pay_bal_id
230 and pbf.input_value_id = prrv.input_value_id
231 and rppa.effective_date
232 between pbf.effective_start_date and pbf.effective_end_date
233 AND pettl.element_type_id = prr.element_type_id
234 AND pettl.language = userenv('LANG');
235 /*
236 --
237 CURSOR csr_yea_sub_class(
238 cp_element_type_id NUMBER,
239 cp_run_effective_date date) IS
240 SELECT 'Y'
241 FROM pay_element_classifications pec,
242 pay_sub_classification_rules_f sub
243 WHERE pec.classification_name = c_yea_deduction_cl
244 and sub.element_type_id = cp_element_type_id
245 AND pec.classification_id = sub.classification_id
246 and cp_run_effective_date
247 between sub.effective_start_date and sub.effective_end_date;
248 */
249 BEGIN
250 hr_utility.set_location('Entering ' || c_proc, 10);
251 --
252 FOR l_element_info_rec IN csr_element_info(p_arch_assignment_action_id) LOOP
253 /*
254 --
255 -- Earnings
256 --
257 IF l_element_info_rec.classification_name IN (
258 c_sal_allowance_cl,
259 c_sal_mat_allowance_cl,
260 c_bon_allowance_cl,
261 c_bon_mat_allowance_cl,
262 c_sp_bon_allowance_cl,
263 c_sp_bon_mat_allowance_cl,
264 c_term_allowance_cl,
265 c_term_mat_allowance_cl) THEN
266 l_payment_type := 'E';
267 --
268 -- Deductions
269 --
270 ELSIF l_element_info_rec.classification_name IN (
271 c_sal_si_prem_res_cl,
272 c_sal_si_prem_nr_cl,
273 c_sal_deduction_cl,
274 c_bon_si_prem_res_cl,
275 c_bon_si_prem_nr_cl,
276 c_bon_deduction_cl,
277 c_sp_bon_si_prem_res_cl,
278 c_sp_bon_si_prem_nr_cl,
279 c_sp_bon_deduction_cl,
280 c_term_deduction_cl) THEN
281 l_payment_type := 'D';
282 --
283 -- YEA case. We can not detect whether the element is deduction or not
284 -- from YEA primary classification, so derive the secondary classification
285 -- which allows us to distinguish whether it is deduction or not.
286 --
287 ELSIF l_element_info_rec.classification_name = c_yea_cl THEN
288 --
289 -- Check Yea Deduction
290 --
291 OPEN csr_yea_sub_class(l_element_info_rec.element_type_id, l_element_info_rec.run_effective_date);
292 FETCH csr_yea_sub_class INTO l_exists;
293 --
294 -- Deductions
295 --
296 IF csr_yea_sub_class%FOUND THEN
297 l_payment_type := 'D';
298 --
299 -- Other
300 --
301 ELSE
302 l_payment_type := 'O';
303 END IF;
304 CLOSE csr_yea_sub_class;
305 --
306 -- Other
307 --
308 ELSE
309 l_payment_type := 'O';
310 END IF;
311 */
312 --
313 hr_utility.trace('action_information_category : ' || c_action_information_category);
314 hr_utility.trace('element_type_id : ' || to_char(l_element_info_rec.element_type_id));
315 hr_utility.trace('input_value_id : ' || to_char(l_element_info_rec.input_value_id));
316 hr_utility.trace('reporting_name : ' || l_element_info_rec.reporting_name);
317 hr_utility.trace('payment_type : ' || l_element_info_rec.payment_type);
318 hr_utility.trace('result_value : ' || l_element_info_rec.result_value);
319 --
320 -- Stack information into global variable
321 --
322 l_act_info_rec.assignment_id := l_element_info_rec.assignment_id;
323 l_act_info_rec.action_info_category := c_action_information_category;
324 l_act_info_rec.act_info1 := fnd_number.number_to_canonical(l_element_info_rec.element_type_id);
325 l_act_info_rec.act_info2 := fnd_number.number_to_canonical(l_element_info_rec.input_value_id);
326 l_act_info_rec.act_info3 := l_element_info_rec.reporting_name;
327 l_act_info_rec.act_info4 := l_element_info_rec.payment_type;
328 l_act_info_rec.act_info5 := 'M';
329 l_act_info_rec.act_info6 := l_element_info_rec.result_value;
330 -- Note the index starts from "0".
331 pay_emp_action_arch.lrr_act_tab(pay_emp_action_arch.lrr_act_tab.count) := l_act_info_rec;
332 END LOOP;
333 --
334 hr_utility.set_location('Leaving ' || c_proc, 20);
335 END setup_element_information;
336 -- +--------------------------------------------------------------------------+
337 -- |----------------------< setup_net_pay_distribution >----------------------|
338 -- +--------------------------------------------------------------------------+
339 PROCEDURE setup_net_pay_distribution(p_arch_assignment_action_id IN NUMBER)
340 IS
341 c_proc CONSTANT VARCHAR2(61) := c_package || 'setup_net_pay_distribution';
342 c_action_information_category constant pay_action_information.action_information_category%type := 'EMPLOYEE NET PAY DISTRIBUTION';
343 l_act_info_rec pay_emp_action_arch.act_info_rec;
344 --
345 cursor csr_assact(cp_arch_assignment_action_id number) is
346 select /*+ ORDERED */
347 ppaa.assignment_action_id prepay_assignment_action_id,
348 pppa.effective_date prepay_effective_date,
349 ppaa.assignment_id prepay_assignment_id
350 from pay_action_interlocks ppai, -- prepay interlocks by arch assact
351 pay_assignment_actions ppaa, -- prepay assact
352 pay_payroll_actions pppa -- prepay pact
353 where ppai.locking_action_id = cp_arch_assignment_action_id
354 and ppaa.assignment_action_id = ppai.locked_action_id
355 and pppa.payroll_action_id = ppaa.payroll_action_id
356 and pppa.action_type in ('P', 'U');
357 --
358 -- A master prepayments assignment action can have multiple child assignment actions,
359 -- but child can not have child assignment actions.
360 -- Both master and child assignment actions can have PAY_PRE_PAYMENTS record.
361 -- PAY_PRE_PAYMENTS indicates source "Run" assignment_action_id.
362 --
363 cursor csr_payment(
364 cp_prepay_assignment_action_id number,
365 cp_prepay_effective_date date) is
366 select /*+ ORDERED USE_NL(PPP OPM OPMTL PPT PEA PPM BNK BCH) */
367 pea.segment1 bank_code,
368 bnk.bank_name,
369 bch.branch_name,
370 pea.segment4 branch_code,
371 hr_general.decode_lookup('PAY_METHOD_PAYMENT_TYPE',
372 decode(ppt.category, 'CA', 'CASH', 'MT', 'DEPOSIT', NULL)) payment_type_meaning,
373 hr_general.decode_lookup('JP_BANK_ACCOUNT_TYPE', pea.segment7) account_type_meaning,
374 pea.segment7 account_type,
375 pea.segment8 account_number,
376 pea.segment9 account_name,
377 pea.segment10 description1,
378 ppp.value,
379 -- ppp.pre_payment_id,
380 opm.org_payment_method_id,
381 opmtl.org_payment_method_name,
382 opm.currency_code,
383 ppm.personal_payment_method_id
384 from (
385 select paa.assignment_action_id
386 from pay_assignment_actions paa
387 connect by prior paa.assignment_action_id = paa.source_action_id
388 start with paa.assignment_action_id = cp_prepay_assignment_action_id
389 ) v,
390 pay_pre_payments ppp,
391 pay_org_payment_methods_f opm,
392 pay_org_payment_methods_f_tl opmtl,
393 pay_payment_types ppt,
394 pay_external_accounts pea,
395 pay_personal_payment_methods_f ppm,
396 pay_jp_banks bnk,
397 pay_jp_bank_branches bch
398 where ppp.assignment_action_id = v.assignment_action_id
399 and opm.org_payment_method_id = ppp.org_payment_method_id
400 and cp_prepay_effective_date
401 between opm.effective_start_date and opm.effective_end_date
402 -- Exclude 3rd party pay
403 and opm.defined_balance_id is not null
404 and opmtl.org_payment_method_id = opm.org_payment_method_id
405 and opmtl.language = userenv('LANG')
406 and ppt.payment_type_id = opm.payment_type_id
407 -- Exclude 3rd party payment
408 and ppm.personal_payment_method_id(+) = ppp.personal_payment_method_id
409 and cp_prepay_effective_date
410 between ppm.effective_start_date(+) and ppm.effective_end_date(+)
411 and pea.external_account_id(+) = ppm.external_account_id
412 and bnk.bank_code(+) = pea.segment1
413 and bch.bank_code(+) = pea.segment1
414 and bch.branch_code(+) = pea.segment4;
415 BEGIN
416 hr_utility.set_location('Entering ' || c_proc, 10);
417 --
418 for l_assact_rec in csr_assact(p_arch_assignment_action_id) loop
419 for l_rec in csr_payment(l_assact_rec.prepay_assignment_action_id, l_assact_rec.prepay_effective_date) loop
420 --
421 -- Stack information into global variable
422 --
423 l_act_info_rec.assignment_id := l_assact_rec.prepay_assignment_id;
424 l_act_info_rec.action_info_category := c_action_information_category;
425 l_act_info_rec.act_info1 := fnd_number.number_to_canonical(l_rec.org_payment_method_id);
426 l_act_info_rec.act_info2 := fnd_number.number_to_canonical(l_rec.personal_payment_method_id);
427 -- l_act_info_rec.act_info4 := null;
428 l_act_info_rec.act_info5 := l_rec.bank_code;
429 l_act_info_rec.act_info6 := l_rec.bank_name;
430 l_act_info_rec.act_info7 := l_rec.branch_name;
431 l_act_info_rec.act_info8 := l_rec.branch_code;
432 l_act_info_rec.act_info9 := l_rec.payment_type_meaning;
433 l_act_info_rec.act_info10 := l_rec.account_type_meaning;
434 l_act_info_rec.act_info11 := l_rec.account_type;
435 l_act_info_rec.act_info12 := l_rec.account_number;
436 l_act_info_rec.act_info13 := l_rec.account_name;
437 l_act_info_rec.act_info14 := l_rec.description1;
438 -- l_act_info_rec.act_info15 := fnd_number.number_to_canonical(l_rec.pre_payment_id);
439 l_act_info_rec.act_info16 := fnd_number.number_to_canonical(l_rec.value);
440 -- l_act_info_rec.act_info17 := fnd_number.number_to_canonical(l_assact_rec.prepay_assignment_action_id);
441 l_act_info_rec.act_info18 := l_rec.org_payment_method_name;
442 -- Note the index starts from "0".
443 pay_emp_action_arch.lrr_act_tab(pay_emp_action_arch.lrr_act_tab.count) := l_act_info_rec;
444 end loop;
445 end loop;
446 --
447 hr_utility.set_location('Leaving ' || c_proc, 20);
448 END setup_net_pay_distribution;
449 -- +--------------------------------------------------------------------------+
450 -- |---------------------------< setup_ytd_amount >---------------------------|
451 -- +--------------------------------------------------------------------------+
452 --
453 -- This procedure is to archive YTD amount for JP statutory information.
454 -- These archived data is displayed on Year To Date region.
455 -- If an archiving process includes multiple run processes, balance value
456 -- is gotten only for the latest run process.
457 --
458 PROCEDURE setup_ytd_amount(
459 p_run_assignment_action_id IN NUMBER,
460 p_run_effective_date IN DATE,
461 p_run_assignment_id IN NUMBER)
462 IS
463 c_proc CONSTANT VARCHAR2(61) := c_package || 'setup_ytd_amount';
464 c_action_information_category constant pay_action_information.action_information_category%type := 'JP YTD AMOUNT';
465 l_allowance_ytd NUMBER;
466 l_taxable_ytd NUMBER;
467 l_si_prem_ytd NUMBER;
468 l_itax_ytd NUMBER;
469 l_act_info_rec pay_emp_action_arch.act_info_rec;
470 BEGIN
471 hr_utility.set_location('Entering ' || c_proc, 10);
472 --
473 l_allowance_ytd := pay_balance_pkg.get_value(c_ytd_allowance_def_bal_id, p_run_assignment_action_id);
474 l_taxable_ytd := pay_balance_pkg.get_value(c_ytd_sal_taxable_def_bal_id, p_run_assignment_action_id)
475 + pay_balance_pkg.get_value(c_ytd_mat_taxable_def_bal_id, p_run_assignment_action_id);
476 l_si_prem_ytd := pay_balance_pkg.get_value(c_ytd_si_prem_def_bal_id, p_run_assignment_action_id);
477 l_itax_ytd := pay_balance_pkg.get_value(c_ytd_itax_def_bal_id, p_run_assignment_action_id)
478 + pay_balance_pkg.get_value(c_ytd_yea_itax_def_bal_id, p_run_assignment_action_id);
479 --
480 hr_utility.trace('action_information_category : ' || c_action_information_category);
481 hr_utility.trace('allowance_ytd : ' || to_char(l_allowance_ytd));
482 hr_utility.trace('taxable_ytd : ' || to_char(l_taxable_ytd));
483 hr_utility.trace('si_prem_ytd : ' || to_char(l_si_prem_ytd));
484 hr_utility.trace('itax_ytd : ' || to_char(l_itax_ytd));
485 hr_utility.trace('run_effective_date : ' || to_char(p_run_effective_date));
486 --
487 -- Stack information into global variable
488 --
489 l_act_info_rec.assignment_id := p_run_assignment_id;
490 l_act_info_rec.action_info_category := c_action_information_category;
491 l_act_info_rec.act_info1 := fnd_number.number_to_canonical(l_allowance_ytd);
492 l_act_info_rec.act_info2 := fnd_number.number_to_canonical(l_taxable_ytd);
493 l_act_info_rec.act_info3 := fnd_number.number_to_canonical(l_si_prem_ytd);
494 l_act_info_rec.act_info4 := fnd_number.number_to_canonical(l_itax_ytd);
495 l_act_info_rec.act_info5 := fnd_date.date_to_canonical(p_run_effective_date); -- Not used
496 -- Note the index starts from "0".
497 pay_emp_action_arch.lrr_act_tab(pay_emp_action_arch.lrr_act_tab.count) := l_act_info_rec;
498 --
499 hr_utility.set_location('Leaving ' || c_proc, 20);
500 END setup_ytd_amount;
501 -- +--------------------------------------------------------------------------+
502 -- |--------------------------< setup_eit_element >---------------------------|
503 -- +--------------------------------------------------------------------------+
504 --
505 -- This procedure is to archive element which is defined at Organization EIT.
506 -- This procedure archives from PAY_RUN_RESULT_VALUES while core procedure
507 -- pay_emp_action_arch.get_employee_other_info archives from PAY_ELEMENT_ENTRY_VALUES_F.
508 -- These archived data is displayed on Further Information region.
509 -- Note: The condition to get archive data is the same as global package,
510 -- pay_emp_action_arch.get_employee_other_info.
511 --
512 PROCEDURE setup_eit_element(
513 p_arch_assignment_action_id IN NUMBER,
514 p_organization_id IN NUMBER)
515 IS
516 c_proc CONSTANT VARCHAR2(61) := c_package || 'setup_eit_element';
517 c_org_information_type constant hr_org_information_types.org_information_type%type := 'Organization:Payslip Info';
518 c_bg_information_type constant hr_org_information_types.org_information_type%type := 'Business Group:Payslip Info';
519 c_action_information_category constant pay_action_information.action_information_category%type := 'JP ELEMENT INFORMATION';
520 c_payment_type constant hr_lookups.lookup_code%type := 'F';
521 l_exists VARCHAR2(1);
522 l_organization_id number;
523 l_org_information_context hr_organization_information.org_information_context%type;
524 l_act_info_rec pay_emp_action_arch.act_info_rec;
525 l_result_value varchar2(255);
526 --
527 CURSOR csr_organization_info(
528 cp_organization_id NUMBER,
529 cp_org_info_context VARCHAR2) IS
530 SELECT 'Y'
531 FROM hr_organization_information
532 WHERE organization_id = cp_organization_id
533 AND org_information_context = cp_org_info_context;
534 --
535 CURSOR csr_eit_element(
536 cp_arch_assignment_action_id NUMBER,
537 cp_organization_id NUMBER,
538 cp_org_information_context VARCHAR2) IS
539 SELECT /*+ ORDERED */
540 rpaa.assignment_id,
541 hoi.org_information2 element_type_id,
542 hoi.org_information3 input_value_id,
543 nvl(hoi.org_information7, nvl(pettl.reporting_name, pettl.element_name)) reporting_name,
544 piv.uom,
545 prrv.result_value,
546 piv.lookup_type,
547 piv.value_set_id
548 FROM pay_action_interlocks rpai, -- run interlock by archive assact
549 pay_assignment_actions rpaa, -- run assact
550 pay_payroll_actions rppa, -- run pact
551 pay_run_results prr,
552 hr_organization_information hoi,
553 pay_element_types_f pet,
554 pay_element_types_f_tl pettl,
555 pay_input_values_f piv,
556 pay_run_result_values prrv
557 WHERE rpai.locking_action_id = cp_arch_assignment_action_id
558 AND rpaa.assignment_action_id = rpai.locked_action_id
559 AND rppa.payroll_action_id = rpaa.payroll_action_id
560 AND rppa.action_type in ('R', 'Q', 'B')
561 AND prr.assignment_action_id = rpaa.assignment_action_id
562 AND prr.status IN ('P', 'PA')
563 AND hoi.organization_id = cp_organization_id
564 AND hoi.org_information_context = cp_org_information_context
565 AND hoi.org_information1 = 'ELEMENT'
566 AND fnd_number.canonical_to_number(hoi.org_information2) = prr.element_type_id
567 and pet.element_type_id = prr.element_type_id
568 and rppa.effective_date
569 between pet.effective_start_date and pet.effective_end_date
570 AND pettl.element_type_id = pet.element_type_id
571 AND pettl.language = userenv('LANG')
572 AND piv.input_value_id = fnd_number.canonical_to_number(hoi.org_information3)
573 AND rppa.effective_date
574 between piv.effective_start_date and piv.effective_end_date
575 AND prrv.input_value_id = piv.input_value_id
576 AND prrv.run_result_id = prr.run_result_id;
577 BEGIN
578 hr_utility.set_location('Entering ' || c_proc, 10);
579 --
580 -- Check whether "ELEMENT" org_information_type is set at HR organization level.
581 -- If exists, "ELEMENT" org_information_type at only HR organization level is used,
582 -- and "ELEMENT" org_information_type at BG level is ignored.
583 --
584 OPEN csr_organization_info(p_organization_id, c_org_information_type);
585 FETCH csr_organization_info INTO l_exists;
586 IF csr_organization_info%NOTFOUND THEN
587 l_organization_id := g_bg_id;
588 l_org_information_context := c_bg_information_type;
589 ELSE
590 l_organization_id := p_organization_id;
591 l_org_information_context := c_org_information_type;
592 END IF;
593 CLOSE csr_organization_info;
594 --
595 FOR l_eit_element_rec IN csr_eit_element(p_arch_assignment_action_id, l_organization_id, l_org_information_context) LOOP
596 hr_utility.trace('action_information_category : ' || c_action_information_category);
597 hr_utility.trace('element_type_id : ' || l_eit_element_rec.element_type_id);
598 hr_utility.trace('input_value_id : ' || l_eit_element_rec.input_value_id);
599 hr_utility.trace('reporting_name : ' || l_eit_element_rec.reporting_name);
600 hr_utility.trace('payment_type : ' || c_payment_type);
601 hr_utility.trace('uom : ' || l_eit_element_rec.uom);
602 hr_utility.trace('result_value : ' || l_eit_element_rec.result_value);
603 --
604 -- If input value is either "LookupType" or "ValueSet",
605 -- decode the value to meaning.
606 --
607 if l_eit_element_rec.result_value is not null then
608 if l_eit_element_rec.lookup_type is not null then
609 l_result_value := hr_general.decode_lookup(l_eit_element_rec.lookup_type, l_eit_element_rec.result_value);
610 if l_result_value is not null then
611 l_eit_element_rec.result_value := l_result_value;
612 end if;
613 elsif l_eit_element_rec.value_set_id is not null then
614 l_result_value := pay_input_values_pkg.decode_vset_value(l_eit_element_rec.value_set_id, l_eit_element_rec.result_value);
615 if l_result_value is not null then
616 l_eit_element_rec.result_value := l_result_value;
617 end if;
618 end if;
619 end if;
620 --
621 -- Stack information into global variable
622 --
623 l_act_info_rec.assignment_id := l_eit_element_rec.assignment_id;
624 l_act_info_rec.action_info_category := c_action_information_category;
625 l_act_info_rec.act_info1 := fnd_number.number_to_canonical(l_eit_element_rec.element_type_id);
626 l_act_info_rec.act_info2 := fnd_number.number_to_canonical(l_eit_element_rec.input_value_id);
627 l_act_info_rec.act_info3 := l_eit_element_rec.reporting_name;
628 l_act_info_rec.act_info4 := c_payment_type;
629 l_act_info_rec.act_info5 := l_eit_element_rec.uom;
630 l_act_info_rec.act_info6 := l_eit_element_rec.result_value;
631 -- Note the index starts from "0".
632 pay_emp_action_arch.lrr_act_tab(pay_emp_action_arch.lrr_act_tab.count) := l_act_info_rec;
633 END LOOP;
634 --
635 hr_utility.set_location('Leaving ' || c_proc, 20);
636 END setup_eit_element;
637 /*
638 -- +--------------------------------------------------------------------------+
639 -- |--------------------------< setup_eit_balance >---------------------------|
640 -- +--------------------------------------------------------------------------+
641 --
642 -- Need to remove this procedure when bug.2810320 is fixed
643 -- This procedure to archive balance which is defined at Organization EIT.
644 -- These archived data is displayed on Balances region.
645 -- If an archiving process includes multiple run processes, balance value
646 -- is gotten only for the latest run process.
647 --
648 PROCEDURE setup_eit_balance(
649 p_arch_assignment_action_id IN NUMBER,
650 p_arch_assignment_id in number,
651 p_run_assignment_action_id IN NUMBER,
652 p_organization_id IN NUMBER)
653 IS
654 c_proc CONSTANT VARCHAR2(61) := c_package || 'setup_eit_balance';
655 --
656 CURSOR csr_emp_other_info_bal(cp_arch_assignment_action_id NUMBER) IS
657 SELECT pai.action_information_id,
658 pai.object_version_number
659 FROM pay_action_information pai
660 WHERE pai.action_context_id = cp_arch_assignment_action_id
661 AND pai.action_context_type = 'AAP'
662 AND pai.action_information_category = 'EMPLOYEE OTHER INFORMATION'
663 AND pai.action_information2 = 'BALANCE';
664 BEGIN
665 hr_utility.set_location('Entering ' || c_proc, 10);
666 --
667 -- Delete balances which has archived in get_personal_information procedure
668 -- because balances archived are based on prepay assact, not run assact,
669 -- which means only balances with "_PAYMENTS" dimensions are archived.
670 --
671 FOR l_emp_other_info_bal_rec IN csr_emp_other_info_bal(p_arch_assignment_action_id) LOOP
672 pay_action_information_api.delete_action_information(
673 p_action_information_id => l_emp_other_info_bal_rec.action_information_id,
674 p_object_version_number => l_emp_other_info_bal_rec.object_version_number);
675 END LOOP;
676 --
677 pay_emp_action_arch.initialization_process;
678 pay_emp_action_arch.get_employee_other_info(
679 p_run_action_id => p_run_assignment_action_id,
680 p_assignment_id => null, -- used to derive element entry values
681 p_organization_id => p_organization_id,
682 p_business_group_id => g_bg_id,
683 p_curr_pymt_eff_date => NULL, -- used to derive element entry values
684 p_tax_unit_id => NULL); -- not used by JP legislation
685 pay_emp_action_arch.insert_rows_thro_api_process(
686 p_action_context_id => p_arch_assignment_action_id,
687 p_action_context_type => 'AAP',
688 p_assignment_id => p_arch_assignment_id,
689 p_tax_unit_id => NULL,
690 p_curr_pymt_eff_date => g_payment_date,
691 p_tab_rec_data => pay_emp_action_arch.lrr_act_tab);
692 --
693 hr_utility.set_location('Leaving ' || c_proc, 20);
694 END setup_eit_balance;
695 */
696 -- +--------------------------------------------------------------------------+
697 -- |-----------------------------< range_cursor >-----------------------------|
698 -- +--------------------------------------------------------------------------+
699 --
700 -- This procedure returns the SQL statement to select all the employee that may
701 -- be eligible for online payslip.
702 -- The archiver uses this cursor to split the people into chunks for parallel
703 -- processing.
704 --
705 PROCEDURE range_cursor(
706 p_payroll_action_id IN NUMBER,
707 p_sqlstr OUT NOCOPY VARCHAR2)
708 IS
709 c_proc CONSTANT VARCHAR2(61):= c_package || 'range_cursor';
710 BEGIN
711 hr_utility.set_location('Entering ' || c_proc, 10);
712 --
713 init_globals(p_payroll_action_id);
714 pay_emp_action_arch.arch_pay_action_level_data(
715 p_payroll_action_id => p_payroll_action_id,
716 p_payroll_id => g_payroll_id,
717 p_effective_date => g_payment_date);
718 --
719 p_sqlstr :=
720 'SELECT DISTINCT per.person_id
721 FROM per_all_people_f per,
722 pay_payroll_actions ppa
723 WHERE ppa.payroll_action_id = :payroll_action_id
724 AND ppa.business_group_id + 0 = per.business_group_id
725 ORDER BY per.person_id';
726 --
727 hr_utility.set_location('Leaving ' || c_proc, 20);
728 END range_cursor;
729 -- +--------------------------------------------------------------------------+
730 -- |---------------------------< action_creation >----------------------------|
731 -- +--------------------------------------------------------------------------+
732 --
733 -- This procedure creates locking data for run and prepayment assignment actions
734 -- by archiving assignment action.
735 -- The successfully completed prepayments are selected and locked by archiving
736 -- action. All the successfully completed runs under the prepayments are also
737 -- selected and locked by archiving action.
738 -- The archive will not pickup already archived prepayments.
739 -- Note: JP online payslip is given for each archiving action. It's different from
740 -- the core specification.
741 --
742 PROCEDURE action_creation(
743 p_payroll_action_id IN NUMBER,
744 p_start_person_id IN NUMBER,
745 p_end_person_id IN NUMBER,
746 p_chunk IN NUMBER)
747 IS
748 c_proc CONSTANT VARCHAR2(61):= c_package || 'action_creation';
749 l_arch_assignment_action_id NUMBER;
750 l_prepay_assignment_id NUMBER;
751 l_prepay_assignment_action_id NUMBER;
752 --
753 CURSOR csr_asg(
754 cp_payroll_action_id NUMBER,
755 cp_start_person_id NUMBER,
756 cp_end_person_id NUMBER,
757 cp_payroll_id NUMBER,
758 cp_consolidation_set_id NUMBER) IS
759 SELECT /*+ ORDERED */
760 ppaa.assignment_id prepay_assignment_id,
761 ppaa.assignment_action_id prepay_assignment_action_id,
762 rpaa.assignment_action_id run_assignment_action_id
763 FROM pay_payroll_actions xppa, -- archive pact
764 pay_payroll_actions pppa, -- prepay pact
765 pay_assignment_actions ppaa, -- prepay assact
766 per_all_assignments_f paaf,
767 pay_action_interlocks rpai, -- run interlock by archive assact
768 pay_assignment_actions rpaa, -- run assact
769 pay_payroll_actions rppa -- run pact
770 WHERE xppa.payroll_action_id = cp_payroll_action_id
771 AND pppa.payroll_id = cp_payroll_id
772 AND pppa.consolidation_set_id = cp_consolidation_set_id
773 AND pppa.action_type IN ('P', 'U')
774 AND pppa.effective_date
775 BETWEEN xppa.start_date AND xppa.effective_date
776 AND ppaa.payroll_action_id = pppa.payroll_action_id
777 -- Only lock master prepayment assignment action
778 AND ppaa.source_action_id is null
779 AND ppaa.action_status = 'C'
780 AND paaf.assignment_id = ppaa.assignment_id
781 AND xppa.effective_date
782 BETWEEN paaf.effective_start_date AND paaf.effective_end_date
783 AND paaf.person_id
784 BETWEEN cp_start_person_id AND cp_end_person_id
785 -- The following payroll_id validation will removed in near future.
786 AND paaf.payroll_id + 0 = pppa.payroll_id
787 AND rpai.locking_action_id = ppaa.assignment_action_id
788 AND rpaa.assignment_action_id = rpai.locked_action_id
789 -- AND rpaa.action_status = 'C'
790 AND rppa.payroll_action_id = rpaa.payroll_action_id
791 AND rppa.action_type IN ('R', 'Q', 'B')
792 AND NOT EXISTS(
793 SELECT /*+ ORDERED */
794 NULL
795 FROM pay_action_interlocks xpai2,
796 pay_assignment_actions xpaa2,
797 pay_payroll_actions xppa2
798 WHERE xpai2.locked_action_id = ppaa.assignment_action_id
799 AND xpaa2.assignment_action_id = xpai2.locking_action_id
800 AND xppa2.payroll_action_id = xpaa2.payroll_action_id
801 AND xppa2.action_type = 'X'
802 AND xppa2.report_type = 'JPPS')
803 AND NOT EXISTS(
804 SELECT /*+ ORDERED */
805 null
806 FROM pay_action_interlocks vpai,
807 pay_assignment_actions vpaa,
808 pay_payroll_actions vppa
809 WHERE vpai.locked_action_id = rpaa.assignment_action_id
810 AND vpaa.assignment_action_id = vpai.locking_action_id
811 AND vppa.payroll_action_id = vpaa.payroll_action_id
812 AND vppa.action_type = 'V')
813 ORDER BY ppaa.assignment_id, ppaa.assignment_action_id
814 FOR UPDATE OF paaf.assignment_id;
815 BEGIN
816 hr_utility.set_location('Entering ' || c_proc, 10);
817 --
818 init_globals(p_payroll_action_id);
819 --
820 FOR l_asg_rec IN csr_asg(
821 p_payroll_action_id,
822 p_start_person_id,
823 p_end_person_id,
824 g_payroll_id,
825 g_consolidation_set_id) LOOP
826 --
827 -- Even if multiple prepayment processes match the condition of archiving, only single assignment
828 -- action for archiving process should be created.
829 --
830 IF (l_prepay_assignment_id is null) or (l_prepay_assignment_id <> l_asg_rec.prepay_assignment_id) THEN
831 SELECT pay_assignment_actions_s.NEXTVAL
832 INTO l_arch_assignment_action_id
833 FROM dual;
834 --
835 -- create an archive assignment action for the master assignment action
836 --
837 hr_utility.trace('inserting into PAY_ASSIGNMENT_ACTIONS');
838 hr_utility.trace('arch_assignmen_action_id : ' || to_char(l_arch_assignment_action_id));
839 hr_utility.trace('arch_assignment_id : ' || to_char(l_asg_rec.prepay_assignment_id));
840 --
841 hr_nonrun_asact.insact(l_arch_assignment_action_id, l_asg_rec.prepay_assignment_id, p_payroll_action_id, p_chunk, NULL);
842 END IF;
843 --
844 -- If a prepayment process locks multiple run processes, multiple prepayment ids are derived by csr_asg.
845 -- But only single prepayment id is required as below.
846 --
847 IF (l_prepay_assignment_action_id is null) or (l_prepay_assignment_action_id <> l_asg_rec.prepay_assignment_action_id) THEN
848 --
849 -- create an archive to payroll master assignment action interlock and create an archive to
850 -- prepayment assignment action interlock
851 --
852 hr_utility.trace('inserting into PAY_ACTION_INTERLOCKS (PREPAY)');
853 hr_utility.trace('locking_action_id : ' || to_char(l_arch_assignment_action_id));
854 hr_utility.trace('locked_action_id : ' || to_char(l_asg_rec.prepay_assignment_action_id));
855 --
856 hr_nonrun_asact.insint(l_arch_assignment_action_id, l_asg_rec.prepay_assignment_action_id);
857 END IF;
858 --
859 hr_utility.trace('inserting into PAY_ACTION_INTERLOCKS (RUN)');
860 hr_utility.trace('locking_action_id : ' || to_char(l_arch_assignment_action_id));
861 hr_utility.trace('locked_action_id : ' || to_char(l_asg_rec.run_assignment_action_id));
862 --
863 hr_nonrun_asact.insint(l_arch_assignment_action_id, l_asg_rec.run_assignment_action_id);
864 --
865 l_prepay_assignment_id := l_asg_rec.prepay_assignment_id;
866 l_prepay_assignment_action_id := l_asg_rec.prepay_assignment_action_id;
867 END LOOP;
868 --
869 hr_utility.set_location('Leaving ' || c_proc, 20);
870 END action_creation;
871 -- +--------------------------------------------------------------------------+
872 -- |------------------------------< arch_init >-------------------------------|
873 -- +--------------------------------------------------------------------------+
874 --
875 -- This procedure is to archive global context at payroll run level.
876 --
877 PROCEDURE archinit(p_payroll_action_id IN NUMBER)
878 IS
879 c_proc CONSTANT VARCHAR2(61):= c_package || 'archinit';
880 BEGIN
881 hr_utility.set_location('Entering ' || c_proc, 10);
882 --
883 init_globals(p_payroll_action_id);
884 --
885 hr_utility.set_location('Leaving ' || c_proc, 20);
886 END archinit;
887 -- +--------------------------------------------------------------------------+
888 -- |-----------------------------< archive_code >-----------------------------|
889 -- +--------------------------------------------------------------------------+
890 --
891 -- This procedure is to archive data at assignment action level.
892 --
893 PROCEDURE archive_code(
894 p_assignment_action_id IN NUMBER,
895 p_effective_date IN DATE)
896 IS
897 c_proc CONSTANT VARCHAR2(61):= c_package || 'archive_code';
898 l_arch_assignment_id NUMBER;
899 l_run_assignment_action_id NUMBER;
900 l_run_assignment_id number;
901 l_run_effective_date DATE;
902 l_time_period_id NUMBER;
903 l_organization_id NUMBER;
904 BEGIN
905 hr_utility.set_location('Entering ' || c_proc, 10);
906 --
907 -- Here derives the latest RUN assignment action information
908 -- locked by current archive assignment action.
909 --
910 select /*+ ORDERED */
911 xpaa.assignment_id,
912 rpaa.assignment_action_id,
913 rpaa.assignment_id,
914 rppa.effective_date,
915 rppa.time_period_id,
916 asg.organization_id
917 into l_arch_assignment_id,
918 l_run_assignment_action_id,
919 l_run_assignment_id,
920 l_run_effective_date,
921 l_time_period_id,
922 l_organization_id
923 from pay_assignment_actions xpaa, -- archive assact
924 pay_action_interlocks rpai, -- run interlock by archive
925 pay_assignment_actions rpaa, -- run assact
926 pay_payroll_actions rppa, -- run pact
927 per_all_assignments_f asg
928 where xpaa.assignment_action_id = p_assignment_action_id
929 and rpai.locking_action_id = xpaa.assignment_action_id
930 and rpaa.assignment_action_id = rpai.locked_action_id
931 and rppa.payroll_action_id = rpaa.payroll_action_id
932 and rppa.action_type in ('R', 'Q', 'B')
933 and asg.assignment_id = rpaa.assignment_id
934 and rppa.effective_date
935 between asg.effective_start_date and asg.effective_end_date
936 and not exists(
937 select /*+ ORDERED */
938 null
939 from pay_action_interlocks rpai2, -- run interlock by archive
940 pay_assignment_actions rpaa2, -- run assact
941 pay_payroll_actions rppa2 -- run pact
942 where rpai2.locking_action_id = xpaa.assignment_action_id
943 and rpaa2.assignment_action_id = rpai2.locked_action_id
944 and rpaa2.action_sequence > rpaa.action_sequence
945 and rppa2.payroll_action_id = rpaa2.payroll_action_id
946 and rppa2.action_type in ('R', 'Q', 'B'));
947 --
948 hr_utility.trace('run_assignment_action_id : ' || to_char(l_run_assignment_action_id));
949 hr_utility.trace('run_assignment_id : ' || to_char(l_run_assignment_id));
950 hr_utility.trace('run_effective_date : ' || to_char(l_run_effective_date));
951 hr_utility.trace('time_period_id : ' || to_char(l_time_period_id));
952 hr_utility.trace('organization_id : ' || to_char(l_organization_id));
953 --
954 -- call generic procedure to retrieve and archive all data for
955 -- EMPLOYEE DETAILS, ADDRESS DETAILS and EMPLOYEE OTHER INFORMATION(only balances).
956 -- Note EMPLOYEE NET PAY DISTRIBUTION needs to be populated without using US proc
957 -- because JP locks multiple prepayment assignment actions by 1 archive assignment action.
958 -- This is not supported by US procedure now.
959 --
960 pay_emp_action_arch.get_personal_information(
961 p_payroll_action_id => g_arch_payroll_action_id, -- archive pact (not used)
962 p_assactid => p_assignment_action_id, -- pay_action_information.action_context_id
963 p_assignment_id => l_arch_assignment_id, -- Used to archive assignment info
964 p_curr_pymt_ass_act_id => null, -- N/A (used for net pay)
965 p_curr_eff_date => l_run_effective_date, -- Used to archive assignment info
966 p_date_earned => l_run_effective_date, -- Used to employee info (only required for US previously)
967 p_curr_pymt_eff_date => l_run_effective_date, -- pay_action_information.effective_date
968 p_tax_unit_id => null, -- N/A (Not used by JP)
969 p_time_period_id => l_time_period_id, -- run Time Period Id
970 p_ppp_source_action_id => null, -- netpay distribution (for Separate Payment)
971 p_ytd_balcall_aaid => l_run_assignment_action_id); -- Used to archive balance. PAYMENTS dimension is not supported in JP.
972 --
973 -- Clear pay_emp_action_arch.lrr_act_tab global variable
974 --
975 pay_emp_action_arch.initialization_process;
976 --
977 -- Archive EMPLOYEE PAYMENT INFORMATION(Payslip Assignment Action Information) into global variable
978 --
979 setup_payment_information(p_arch_assignment_action_id => p_assignment_action_id);
980 --
981 -- Archive JP ELEMENT INFORMATION(Earnings/Deductions) into global variable
982 --
983 setup_element_information(p_arch_assignment_action_id => p_assignment_action_id);
984 --
985 -- Archive EMPLOYEE NET PAY DISTRIBUTION into global variable
986 --
987 setup_net_pay_distribution(p_arch_assignment_action_id => p_assignment_action_id);
988 --
989 -- Archive JP YTD AMOUNT into global variable
990 --
991 setup_ytd_amount(
992 p_run_assignment_action_id => l_run_assignment_action_id,
993 p_run_effective_date => l_run_effective_date,
994 p_run_assignment_id => l_run_assignment_id);
995 --
996 -- Archive JP ELEMENT INFORMATION(Run Result) into global variable
997 --
998 setup_eit_element(
999 p_arch_assignment_action_id => p_assignment_action_id,
1000 p_organization_id => l_organization_id);
1001 --
1002 -- Upload global variable lrr_act_tab into PAY_ACTION_INFORMATION
1003 --
1004 pay_emp_action_arch.insert_rows_thro_api_process(
1005 p_action_context_id => p_assignment_action_id,
1006 p_action_context_type => 'AAP',
1007 p_assignment_id => l_arch_assignment_id,
1008 p_tax_unit_id => null,
1009 p_curr_pymt_eff_date => l_run_effective_date,
1010 p_tab_rec_data => pay_emp_action_arch.lrr_act_tab);
1011 --
1012 hr_utility.set_location('Leaving ' || c_proc, 20);
1013 END archive_code;
1014 --
1015 PROCEDURE deinitialization_code (p_payroll_action_id IN NUMBER)
1016 IS
1017 l_dummy varchar2(1);
1018 cursor csr_pa_exists is
1019 select 'Y'
1020 from dual
1021 where exists(
1022 select null
1023 from pay_action_information
1024 where action_context_id = p_payroll_action_id
1025 and action_context_type = 'PA');
1026 BEGIN
1027 --
1028 -- "initialization_code" is kicked for each child thread, so arch_pay_action_level_data
1029 -- should not be called in child threads, but called by parent thread, which means in
1030 -- either "range_code" or "deinitialization_code".
1031 -- But "range_code" is not called when retrying, so need to implement payroll level
1032 -- archiving in deinitialization_code here.
1033 --
1034 open csr_pa_exists;
1035 fetch csr_pa_exists into l_dummy;
1036 if csr_pa_exists%notfound then
1037 --
1038 -- When retrying payroll action whose assignment actions are all "completed",
1039 -- initialization_code is not called. So init_globals needs to be called here also
1040 -- to guarantee that all globals are set.
1041 --
1042 init_globals(p_payroll_action_id);
1043 pay_emp_action_arch.arch_pay_action_level_data(
1044 p_payroll_action_id => p_payroll_action_id,
1045 p_payroll_id => g_payroll_id,
1046 p_effective_date => g_payment_date);
1047 end if;
1048 END deinitialization_code;
1049 --
1050 END pay_jp_payslip_archive;