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