1 package body pay_nz_eft as
2 /* $Header: pynzeft.pkb 120.0.12010000.8 2008/12/08 15:55:38 pmatamsr ship $
3 **
4 ** Copyright (c) 1999 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** EFT direct credit of pay stuff
8 **
9 ** Change List
10 ** ===========
11 **
12 ** Date Author Reference Description
13 ** -----------+--------+---------+-------------
14 ** 1 NOV 1999 ATOPOL N/A Big bang
15 ** 30 MAY 2000 PUCHIL 2920728 Corrected check_sql errors.
16 ** 07 OCT 2008 PMATAMSR 6891410 As part of NewZealand Direct Credit Enhancement
17 ** /AVENKATK procedure add_custom_xml is added to the
18 ** package.This procedure will be called by the
19 ** XML generation process and adds required XML tags
20 ** like BATCH_DUE_DATE,BATCH_CREATION_DATE
21 ** NBNZ_HASH_ACCT and REG_EMPLOYER in the XML
22 ** generated for each assignment.
23 ** 05 DEC 2008 PMATAMSR 7614146 A new cursor get_batch_due_date is added
24 ** to fetch default_dd_date from the per_time_periods
25 ** as batch_due_date to the NZ Direct Credit XML report.
26 */
27
28 g_debug BOOLEAN := hr_utility.debug_enabled;
29 g_legislation_code VARCHAR2(10) := 'NZ';
30
31 /* Bug# 6891410--This procedure will be called by the XML generation process
32 * and adds required XML tags for each assignment*/
33 PROCEDURE add_custom_xml
34 IS
35
36 l_text VARCHAR(900);
37 l_batch_due_date VARCHAR2(100);
38 l_batch_creation_date VARCHAR2(100);
39
40 l_pre_pay_id VARCHAR2(100);
41 l_payroll_action_id VARCHAR2(100);
42 l_assignment_action_id NUMBER(16);
43 l_eff_date DATE;
44 l_nbnz_account VARCHAR2(20);
45 l_legal_employer VARCHAR2(250);
46 CURSOR get_effective_date
47 (c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
48 IS
49 SELECT ppa.effective_date
50 FROM pay_payroll_actions ppa
51 WHERE ppa.payroll_action_id = c_payroll_action_id;
52
53 CURSOR get_nbnz_account_information
54 (c_pre_payment_id pay_pre_payments.pre_payment_id%TYPE
55 ,c_effective_date DATE)
56 IS
57 SELECT SUBSTR(pea.segment1,3,4)||SUBSTR(LPAD(pea.segment2,8,'0'),2,7)
58 FROM pay_pre_payments ppp
59 ,pay_personal_payment_methods_f ppmf
60 ,pay_external_accounts pea
61 WHERE ppp.pre_payment_id = c_pre_payment_id
62 AND ppp.personal_payment_method_id = ppmf.personal_payment_method_id
63 AND ppmf.external_account_id = pea.external_account_id
64 AND c_effective_date BETWEEN ppmf.effective_start_date AND ppmf.effective_end_date;
65
66 CURSOR get_legal_employer_info
67 (c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
68 ,c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
69 IS
70 SELECT hou.name
71 FROM pay_assignment_actions paa,
72 hr_organization_units hou
73 WHERE paa.payroll_action_id = c_payroll_action_id
74 AND paa.assignment_action_id = c_assignment_action_id
75 AND to_char(hou.organization_id) = paa.tax_unit_id;
76
77 /*Bug#7614146 -New Cursor added to fetch the batch_due_date from the payroll tables*/
78
79 CURSOR get_batch_due_date(c_pre_payment_id pay_pre_payments.pre_payment_id%TYPE)
80 IS
81 SELECT to_char(ptp.default_dd_date,'YYYYMMDD')
82 FROM pay_assignment_actions paa,
83 pay_pre_payments ppp,
84 pay_payroll_actions ppa,
85 pay_action_interlocks pai,
86 per_time_periods ptp
87 WHERE ppp.pre_payment_id = c_pre_payment_id
88 AND pai.locking_action_id = ppp.assignment_action_id
89 AND paa.assignment_action_id = pai.locked_action_id
90 AND ppa.payroll_action_id = paa.payroll_action_id
91 AND ppa.payroll_id = ptp.payroll_id
92 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
93 /*End of bug#7614146*/
94
95 BEGIN
96
97 if g_debug then
98 hr_utility.trace('Add Custom XML starts here .... ');
99 end if;
100 /*commented for bug#7614146*/
101 /*l_batch_due_date := pay_magtape_generic.get_parameter_value('BATCH_DUE_DATE');*/
102 l_batch_creation_date := pay_magtape_generic.get_parameter_value('BATCH_CREATION_DATE');
103 l_pre_pay_id := pay_magtape_generic.get_parameter_value('PRE_PAY_ID');
104 l_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
105 l_assignment_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
106
107 if g_debug then
108 /* hr_utility.trace('l_batch_due_date =>'||l_batch_due_date);*/
109 hr_utility.trace('l_batch_creation_date =>'||l_batch_creation_date);
110 hr_utility.trace('l_pre_pay_id =>'||l_pre_pay_id);
111 hr_utility.trace('l_payroll_action_id =>'||l_payroll_action_id);
112 hr_utility.trace('l_assignment_action_id =>'||l_assignment_action_id);
113 end if;
114
115 OPEN get_effective_date(l_payroll_action_id);
116 FETCH get_effective_date INTO l_eff_date;
117 CLOSE get_effective_date;
118
119
120 OPEN get_nbnz_account_information(l_pre_pay_id,l_eff_date);
121 FETCH get_nbnz_account_information INTO l_nbnz_account;
122 CLOSE get_nbnz_account_information;
123
124 OPEN get_legal_employer_info(l_payroll_action_id,l_assignment_action_id);
125 FETCH get_legal_employer_info INTO l_legal_employer;
126 CLOSE get_legal_employer_info;
127
128 /*Bug#7614146 -Default_dd_date from per_time_periods table is fetched as
129 * Batch Due date into the XML report*/
130 OPEN get_batch_due_date(l_pre_pay_id);
131 FETCH get_batch_due_date INTO l_batch_due_date;
132 CLOSE get_batch_due_date;
133 /*End of bug#7614146*/
134
135 l_text :=
136 '<NBNZ_HASH_ACCT>'|| l_nbnz_account || '</NBNZ_HASH_ACCT>'||
137 '<BATCH_DUE_DATE>'|| l_batch_due_date || '</BATCH_DUE_DATE>'||
138 '<BATCH_CREATION_DATE>'||l_batch_creation_date||'</BATCH_CREATION_DATE>'||
139 '<REG_EMPLOYER>'||l_legal_employer||'</REG_EMPLOYER>';
140
141 pay_core_files.write_to_magtape_lob(l_text);
142
143 if g_debug then
144 hr_utility.trace('Add Custom XML ends here .......');
145 end if;
146
147 END add_custom_xml;
148
149 end pay_nz_eft ;
[Home] [Help]