1 package pay_ca_rl2_mag AUTHID CURRENT_USER as
2 /* $Header: pycarl2mg.pkh 120.10.12020000.3 2012/11/23 07:07:00 sgotlasw ship $ */
3 /*
4 Name
5 pay_ca_rl2_mag
6
7 Purpose
8 The purpose of this package is to support the generation of magnetic tape RL2
9
10
11 reports for CA legislative requirements incorporating magtape resilience
12 and the new end-of-year design.
13
14 Notes
15 The generation of each magnetic tape report is a two stage process i.e.
16 1. Check if the year end pre-processor has been run for all the GREs
17 and the assignments. If not, then error out without processing further.
18 2. Create a payroll action for the report. Identify all the assignments
19 to be reported and record an assignment action against the payroll action
20 for each one of them.
21 3. Run the generic magnetic tape process which will
22 drive off the data created in stage two. This will result in the
23 production of a structured ascii file which can be transferred to
24 magnetic tape and sent to the relevant authority.
25
26 History
27 23-DEC-2003 SSouresr 115.0 Date created.
28
29 ============================================================================*/
30
31
32 -- 'level_cnt' will allow the cursors to select function results,
33 -- whether it is a standard fuction such as to_char or a function
34 -- defined in a package (with the correct pragma restriction).
35
36 level_cnt NUMBER;
37 type other_info_rec is record (code varchar2(10),
38 amount varchar2(50));
39
40 type other_info_tab is table of other_info_rec index by binary_integer;
41 g_further_info_list other_info_tab;
42 -- Used by Magnetic RL2 (RL2 format).
43 --
44 --
45
46 CURSOR mag_rl2_transmitter IS
47 SELECT 'BUSINESS_GROUP_ID=C',ppa.business_group_id,
48 'PAYROLL_ACTION_ID=P',ppa.payroll_action_id
49 FROM hr_organization_information hoi,
50 pay_payroll_actions ppa
51 WHERE to_char(hoi.organization_id) = pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
52 AND hoi.org_information_context='Prov Reporting Est'
53 AND ppa.report_type = 'RL2' -- RL2 Archiver Report Type
54 AND to_char(hoi.organization_id) =
55 substr(ppa.legislative_parameters,
56 instr(ppa.legislative_parameters,'PRE_ORGANIZATION_ID=')+LENGTH('PRE_ORGANIZATION_ID='))
57 AND to_char(ppa.effective_date,'YYYY')=pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
58 AND to_char(ppa.effective_date,'DD-MM')= '31-12';
59
60 --
61 -- Used by Magnetic RL2 (RL2 format).
62 --
63 -- Sets up the tax unit context for each employer to be reported. sets
64 -- up a parameter holding the tax unit identifier which can then be used by
65 -- subsequent cursors to restrict to employees within the employer.
66 --
67 --
68 CURSOR mag_rl2_employer IS
69 SELECT DISTINCT
70 'BUSINESS_GROUP_ID=C',ppa.business_group_id,
71 'PAYROLL_ACTION_ID=C',ppa.payroll_action_id,
72 'PAYROLL_ACTION_ID=P',ppa.payroll_action_id
73 FROM pay_payroll_actions ppa,
74 hr_organization_information hoi
75 WHERE decode(hoi.org_information3,'Y',to_char(hoi.organization_id) ,hoi.org_information20) =
76 pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
77 AND hoi.org_information_context='Prov Reporting Est'
78 AND to_char(hoi.organization_id) =
79 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID', ppa.legislative_parameters)
80 AND ppa.action_status = 'C'
81 AND to_char(ppa.effective_date,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
82 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
83 AND ppa.report_type = 'RL2';
84
85 --
86 -- Used by Magnetic RL2 (RL2 format).
87 --
88 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
89 -- for an employee. The date_earned context is set to be the least of the
90 -- end of the period being reported and the maximum end date of the
91 -- assignment. This ensures that personal information ie. name etc... is
92 -- current relative to the period being reported on.
93 --
94
95 CURSOR mag_rl2_employee IS
96 SELECT
97 'TRANSFER_ACT_ID=P',paa.assignment_action_id
98 FROM pay_action_information pin,
99 per_all_people_f ppf,
100 per_all_assignments_f paf,
101 pay_action_interlocks pai,
102 pay_assignment_actions paa,
103 pay_payroll_actions ppa,
104 pay_assignment_actions paa_arch
105 WHERE ppa.payroll_action_id =
106 to_number( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'))
107 AND paa.payroll_action_id = ppa.payroll_action_id
108 AND pai.locking_action_id = paa.assignment_action_id
109 AND paf.assignment_id = paa.assignment_id
110 AND ppf.person_id = paf.person_id
111 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id) between
112 paf.effective_start_date and paf.effective_end_date
113 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id) between
114 ppf.effective_start_date and ppf.effective_end_date
115 AND pin.action_context_id = pai.locked_action_id
116 AND pin.action_context_type = 'AAP'
117 AND pin.action_information_category = 'CAEOY RL2 EMPLOYEE INFO'
118 AND paa_arch.assignment_action_id = pai.locked_action_id
119 AND paa_arch.payroll_action_id =
120 to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'))
121 AND to_char(pin.effective_date,'YYYY') = to_char(ppa.effective_date,'YYYY')
122 ORDER BY ppf.last_name,ppf.first_name,ppf.middle_names;
123
124 procedure store_further_information(p_assg_actid IN NUMBER);
125
126 procedure get_further_information(p_index in number,
127 p_code in out nocopy varchar2,
128 p_amount in out nocopy varchar2);
129
130 PROCEDURE get_report_parameters
131 (
132 p_pactid IN NUMBER,
133 p_year_start IN OUT NOCOPY DATE,
134 p_year_end IN OUT NOCOPY DATE,
135 p_report_type IN OUT NOCOPY VARCHAR2,
136 p_business_group_id IN OUT NOCOPY NUMBER,
137 p_legislative_param IN OUT NOCOPY VARCHAR2
138 );
139
140
141 PROCEDURE range_cursor (
142 p_pactid IN NUMBER,
143 p_sqlstr OUT NOCOPY VARCHAR2
144 );
145
146
147 PROCEDURE create_assignment_act(
148 p_pactid IN NUMBER,
149 p_stperson IN NUMBER,
150 p_endperson IN NUMBER,
151 p_chunk IN NUMBER );
152
153 FUNCTION get_parameter(name IN VARCHAR2,
154 parameter_list VARCHAR2)
155 RETURN VARCHAR2;
156
157 /* Bug 14701748 */
158 FUNCTION get_slip_seq_no(name in varchar2,
159 parameter_list varchar2,
160 position number)
161 RETURN varchar2;
162
163 pragma restrict_references(get_parameter, WNDS, WNPS);
164
165 FUNCTION get_transmitter_item(p_business_group_id IN NUMBER,
166 p_pact_id IN NUMBER,
167 p_archived_item IN VARCHAR2)
168 RETURN VARCHAR2;
169
170 FUNCTION get_employer_item(p_business_group_id IN NUMBER,
171 p_pact_id IN NUMBER,
172 p_archived_item IN VARCHAR2)
173 RETURN VARCHAR2;
174
175 FUNCTION get_employee_item(p_asg_action_id IN NUMBER,
176 p_assignment_id IN NUMBER,
177 p_archived_item IN VARCHAR2)
178 RETURN VARCHAR2;
179
180 PROCEDURE xml_transmitter_record;
181
182 PROCEDURE end_of_file;
183
184 PROCEDURE archive_ca_deinit(p_pactid IN NUMBER);
185
186 PROCEDURE xml_employee_record;
187
188 FUNCTION get_final_xml(p_arch_action_id in pay_assignment_actions.assignment_action_id%TYPE,
189 p_taxation_year in varchar2,
190 p_rep_type in varchar2)
191 RETURN BLOB;
192
193 PROCEDURE fetch_rl2_xml(p_arch_action_id in pay_assignment_actions.assignment_action_id%TYPE,
194 p_asg_id in per_assignments_f.assignment_id%TYPE,
195 p_rep_type in varchar2,
196 p_taxation_year in varchar2,
197 p_print_instruction in varchar2,
198 called_from in varchar2,
199 p_xml_blob out NOCOPY BLOB);
200
201 PROCEDURE xml_employer_start;
202
203 PROCEDURE xml_employer_record;
204
205 PROCEDURE xml_report_start;
206
207 PROCEDURE xml_report_end;
208
209 FUNCTION validate_quebec_number (p_quebec_no IN VARCHAR2,p_qin_name varchar2)
210 RETURN NUMBER;
211 CURSOR rl2_asg_actions
212 IS
213 SELECT 'TRANSFER_ACT_ID=P',
214 pay_magtape_generic.get_parameter_value(
215 'TRANSFER_ACT_ID')
216 FROM DUAL;
217
218 cursor main_block is
219 select 'Version_Number=X' ,'Version 1.1'
220 from sys.dual;
221
222 /* commented by sneelapa for bug 7563384 and redefined below.
223 cursor transfer_block is
224 select 'TRANSFER_ACT_ID=P', assignment_action_id
225 from pay_assignment_actions
226 where payroll_action_id =
227 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
228 */
229
230 cursor transfer_block is
231 select 'TRANSFER_ACT_ID=P', assignment_action_id
232 from hr_all_organization_units hou1,
233 hr_all_organization_units hou,
234 hr_locations_all loc,
235 per_all_people_f ppf,
236 per_all_assignments_f paf,
237 pay_assignment_actions paa1,
238 pay_payroll_actions ppa1
239 where ppa1.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
240 and paa1.payroll_action_id = ppa1.payroll_action_id
241 and paa1.assignment_id = paf.assignment_id
242 and paf.assignment_type = 'E'
243 and paf.primary_flag = 'Y'
244 and paf.business_group_id = ppa1.business_group_id
245 and ppa1.effective_date >= paf.effective_start_date
246 and hou.organization_id = paa1.tax_unit_id
247 and loc.location_id = paf.location_id
248 and hou1.organization_id = paf.organization_id
249 and ppf.person_id = paf.person_id
250 and ppa1.effective_date between
251 ppf.effective_start_date and ppf.effective_end_date
252 and paf.effective_end_date = (
253 select max(paaf2.effective_end_date)
254 from per_all_assignments_f paaf2
255 where paaf2.assignment_id = paf.assignment_id
256 and paaf2.effective_start_date <= ppa1.effective_date
257 )
258 order by
259 decode(pay_ca_rl2_reg.get_parameter('P_S1',ppa1.legislative_parameters),
260 'RL1_PRE',hou.name,
261 'RL1_ORG',hou1.name,
262 'RL1_LOC',loc.location_code,null)
263 ,decode(pay_ca_rl2_reg.get_parameter('P_S2',ppa1.legislative_parameters),
264 'RL1_PRE',hou.name,
265 'RL1_ORG',hou1.name,
266 'RL1_LOC',loc.location_code,null)
267 ,decode(pay_ca_rl2_reg.get_parameter('P_S3',ppa1.legislative_parameters),
268 'RL1_PRE',hou.name,
269 'RL1_ORG',hou1.name,
270 'RL1_LOC',loc.location_code,null)
271 ,ppf.last_name,ppf.first_name;
272
273 FUNCTION convert_special_char( p_data IN VARCHAR2)
274 RETURN VARCHAR2;
275
276 /*FUNCTION getnext_seq_num(p_curr_seq IN NUMBER)
277 RETURN NUMBER; */
278
279 END pay_ca_rl2_mag;
[Home] [Help]