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