1 package pay_ca_rl2_mag as
2 /* $Header: pycarl2mg.pkh 120.8.12010000.1 2008/07/27 22:16:12 appldev 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
38 -- Used by Magnetic RL2 (RL2 format).
39 --
40 --
41
42 CURSOR mag_rl2_transmitter IS
43 SELECT 'BUSINESS_GROUP_ID=C',ppa.business_group_id,
44 'PAYROLL_ACTION_ID=P',ppa.payroll_action_id
45 FROM hr_organization_information hoi,
46 pay_payroll_actions ppa
47 WHERE to_char(hoi.organization_id) = pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
48 AND hoi.org_information_context='Prov Reporting Est'
49 AND ppa.report_type = 'RL2' -- RL2 Archiver Report Type
50 AND to_char(hoi.organization_id) =
51 substr(ppa.legislative_parameters,
52 instr(ppa.legislative_parameters,'PRE_ORGANIZATION_ID=')+LENGTH('PRE_ORGANIZATION_ID='))
53 AND to_char(ppa.effective_date,'YYYY')=pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
54 AND to_char(ppa.effective_date,'DD-MM')= '31-12';
55
56 --
57 -- Used by Magnetic RL2 (RL2 format).
58 --
59 -- Sets up the tax unit context for each employer to be reported. sets
60 -- up a parameter holding the tax unit identifier which can then be used by
61 -- subsequent cursors to restrict to employees within the employer.
62 --
63 --
64 CURSOR mag_rl2_employer IS
65 SELECT DISTINCT
66 'BUSINESS_GROUP_ID=C',ppa.business_group_id,
67 'PAYROLL_ACTION_ID=C',ppa.payroll_action_id,
68 'PAYROLL_ACTION_ID=P',ppa.payroll_action_id
69 FROM pay_payroll_actions ppa,
70 hr_organization_information hoi
71 WHERE decode(hoi.org_information3,'Y',to_char(hoi.organization_id) ,hoi.org_information20) =
72 pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
73 AND hoi.org_information_context='Prov Reporting Est'
74 AND to_char(hoi.organization_id) =
75 pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID', ppa.legislative_parameters)
76 AND ppa.action_status = 'C'
77 AND to_char(ppa.effective_date,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
78 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
79 AND ppa.report_type = 'RL2';
80
81 --
82 -- Used by Magnetic RL2 (RL2 format).
83 --
84 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
85 -- for an employee. The date_earned context is set to be the least of the
86 -- end of the period being reported and the maximum end date of the
87 -- assignment. This ensures that personal information ie. name etc... is
88 -- current relative to the period being reported on.
89 --
90
91 CURSOR mag_rl2_employee IS
92 SELECT
93 'TRANSFER_ACT_ID=P',paa.assignment_action_id
94 FROM pay_action_information pin,
95 per_all_people_f ppf,
96 per_all_assignments_f paf,
97 pay_action_interlocks pai,
98 pay_assignment_actions paa,
99 pay_payroll_actions ppa,
100 pay_assignment_actions paa_arch
101 WHERE ppa.payroll_action_id =
102 to_number( pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'))
103 AND paa.payroll_action_id = ppa.payroll_action_id
104 AND pai.locking_action_id = paa.assignment_action_id
105 AND paf.assignment_id = paa.assignment_id
106 AND ppf.person_id = paf.person_id
107 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id) between
108 paf.effective_start_date and paf.effective_end_date
109 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id) between
110 ppf.effective_start_date and ppf.effective_end_date
111 AND pin.action_context_id = pai.locked_action_id
112 AND pin.action_context_type = 'AAP'
113 AND pin.action_information_category = 'CAEOY RL2 EMPLOYEE INFO'
114 AND paa_arch.assignment_action_id = pai.locked_action_id
115 AND paa_arch.payroll_action_id =
116 to_number(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID'))
117 AND to_char(pin.effective_date,'YYYY') = to_char(ppa.effective_date,'YYYY')
118 ORDER BY ppf.last_name,ppf.first_name,ppf.middle_names;
119
120 PROCEDURE get_report_parameters
121 (
122 p_pactid IN NUMBER,
123 p_year_start IN OUT NOCOPY DATE,
124 p_year_end IN OUT NOCOPY DATE,
125 p_report_type IN OUT NOCOPY VARCHAR2,
126 p_business_group_id IN OUT NOCOPY NUMBER,
127 p_legislative_param IN OUT NOCOPY VARCHAR2
128 );
129
130
131 PROCEDURE range_cursor (
132 p_pactid IN NUMBER,
133 p_sqlstr OUT NOCOPY VARCHAR2
134 );
135
136
137 PROCEDURE create_assignment_act(
138 p_pactid IN NUMBER,
139 p_stperson IN NUMBER,
140 p_endperson IN NUMBER,
141 p_chunk IN NUMBER );
142
143 FUNCTION get_parameter(name IN VARCHAR2,
144 parameter_list VARCHAR2)
145 RETURN VARCHAR2;
146
147 pragma restrict_references(get_parameter, WNDS, WNPS);
148
149 FUNCTION get_transmitter_item(p_business_group_id IN NUMBER,
150 p_pact_id IN NUMBER,
151 p_archived_item IN VARCHAR2)
152 RETURN VARCHAR2;
153
154 FUNCTION get_employer_item(p_business_group_id IN NUMBER,
155 p_pact_id IN NUMBER,
156 p_archived_item IN VARCHAR2)
157 RETURN VARCHAR2;
158
159 FUNCTION get_employee_item(p_asg_action_id IN NUMBER,
160 p_assignment_id IN NUMBER,
161 p_archived_item IN VARCHAR2)
162 RETURN VARCHAR2;
163
164 PROCEDURE xml_transmitter_record;
165
166 PROCEDURE end_of_file;
167
168 PROCEDURE archive_ca_deinit(p_pactid IN NUMBER);
169
170 PROCEDURE xml_employee_record;
171
172 PROCEDURE xml_employer_start;
173
174 PROCEDURE xml_employer_record;
175
176 PROCEDURE xml_report_start;
177
178 PROCEDURE xml_report_end;
179
180 FUNCTION validate_quebec_number (p_quebec_no IN VARCHAR2,p_qin_name varchar2)
181 RETURN NUMBER;
182 CURSOR rl2_asg_actions
183 IS
184 SELECT 'TRANSFER_ACT_ID=P',
185 pay_magtape_generic.get_parameter_value(
186 'TRANSFER_ACT_ID')
187 FROM DUAL;
188
189 cursor main_block is
190 select 'Version_Number=X' ,'Version 1.1'
191 from sys.dual;
192
193 cursor transfer_block is
194 select 'TRANSFER_ACT_ID=P', assignment_action_id
195 from pay_assignment_actions
196 where payroll_action_id =
197 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
198
199 FUNCTION convert_special_char( p_data IN VARCHAR2)
200 RETURN VARCHAR2;
201
202 FUNCTION getnext_seq_num(p_curr_seq IN NUMBER)
203 RETURN NUMBER;
204
205 END pay_ca_rl2_mag;
[Home] [Help]