1 package pay_ca_rl1_mag as
2 /* $Header: pycarlmg.pkh 120.6.12010000.1 2008/07/27 22:16:22 appldev 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
36 -- Used by Magnetic RL1 (RL1 format).
37 --
38 -- Sets up the tax unit context for the transmitter_GRE
39 --
40 --
41
42 CURSOR mag_rl1_transmitter IS
43 Select 'PAYROLL_ACTION_ID=P',ppa.payroll_action_id,
44 'TRANSFER_CPP_MAX=P', pcli.information_value
45 FROM hr_organization_information hoi,
46 pay_payroll_actions PPA,
47 pay_ca_legislation_info pcli
48 WHERE to_char(hoi.organization_id) = pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
49 and hoi.org_information_context='Prov Reporting Est'
50 and ppa.report_type = 'RL1' -- RL1 Archiver Report Type
51 and to_char(hoi.organization_id) = substr(ppa.legislative_parameters,instr(ppa.legislative_parameters,'PRE_ORGANIZATION_ID=')+LENGTH('PRE_ORGANIZATION_ID='))
52 and to_char(ppa.effective_date,'YYYY')=pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
53 and to_char(ppa.effective_date,'DD-MM')= '31-12'
54 and pcli.information_type = 'MAX_CPP_EARNINGS'
55 and ppa.effective_date between pcli.start_date and pcli.end_date;
56
57 --
58 -- Used by Magnetic RL1 (RL1 format).
59 --
60 -- Sets up the tax unit context for each employer to be reported. sets
61 -- up a parameter holding the tax unit identifier which can then be used by
62 -- subsequent cursors to restrict to employees within the employer.
63 --
64 --
65 CURSOR mag_rl1_employer IS
66 select distinct 'PAYROLL_ACTION_ID=C',ppa.payroll_action_id,
67 'PAYROLL_ACTION_ID=P',ppa.payroll_action_id
68 from pay_payroll_actions ppa,
69 hr_organization_information hoi
70 WHERE decode(hoi.org_information3,'Y',to_char(hoi.organization_id) ,hoi.org_information20) =
71 pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
72 and hoi.org_information_context='Prov Reporting Est'
73 and to_char(hoi.organization_id) =
74 pay_ca_rl1_reg.get_parameter('PRE_ORGANIZATION_ID', ppa.legislative_parameters)
75 and ppa.action_status = 'C'
76 and to_char(ppa.effective_date,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
77 and to_char(ppa.effective_date,'DD-MM') = '31-12'
78 and ppa.report_type = 'RL1';
79
80 --
81 -- Used by Magnetic RL1 (RL1 format).
82 --
83 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
84 -- for an employee. The date_earned context is set to be the least of the
85 -- end of the period being reported and the maximum end date of the
86 -- assignment. This ensures that personal information ie. name etc... is
87 -- current relative to the period being reported on.
88 --
89
90 CURSOR mag_rl1_employee IS
91 SELECT
92 'TRANSFER_ACT_ID=P', paa.assignment_action_id
93 FROM
94 per_all_people_f ppf,
95 per_all_assignments_f paf,
96 pay_action_interlocks pai,
97 pay_assignment_actions paa,
98 pay_payroll_actions ppa,
99 pay_assignment_actions paa_arch
100 WHERE
101 ppa.payroll_action_id =
102 to_number(pay_magtape_generic.get_parameter_value
103 ('TRANSFER_PAYROLL_ACTION_ID')) AND
104 paa.payroll_action_id = ppa.payroll_action_id AND
105 pai.locking_action_id = paa.assignment_action_id AND
106 paf.assignment_id = paa.assignment_id AND
107 ppf.person_id = paf.person_id AND
108 apps.pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
109 between
110 paf.effective_start_date and paf.effective_end_date AND
111 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
112 between
113 ppf.effective_start_date and ppf.effective_end_date AND
114 paa_arch.payroll_action_id =
115 to_number(pay_magtape_generic.get_parameter_value
116 ('PAYROLL_ACTION_ID')) AND
117 paa_arch.assignment_action_id = pai.locked_action_id
118 ORDER BY
119 ppf.last_name,ppf.first_name,ppf.middle_names;
120
121 PROCEDURE get_report_parameters
122 (
123 p_pactid IN NUMBER,
124 p_year_start IN OUT NOCOPY DATE,
125 p_year_end IN OUT NOCOPY DATE,
126 p_report_type IN OUT NOCOPY VARCHAR2,
127 p_business_group_id IN OUT NOCOPY NUMBER
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 --
144 --
145
146 FUNCTION get_parameter(name in varchar2,
147 parameter_list varchar2)
148 RETURN varchar2;
149
150 --
151 --
152 pragma restrict_references(get_parameter, WNDS, WNPS);
153
154
155
156 FUNCTION validate_quebec_number(p_quebec_no IN VARCHAR2,
157 p_qin_name varchar2)
158 RETURN NUMBER;
159
160 --
161 --
162
163 FUNCTION get_arch_val(p_context_id IN NUMBER,
164 p_user_name IN VARCHAR2)
165 RETURN VARCHAR2;
166
167 --
168 --
169
170 PROCEDURE xml_transmitter_record;
171
172 PROCEDURE end_of_file;
173
174 PROCEDURE xml_employee_record;
175
176 PROCEDURE xml_employer_start;
177
178 PROCEDURE XML_EMPLOYER_RECORD;
179
180 CURSOR rl1_asg_actions
181 IS
182 SELECT 'TRANSFER_ACT_ID=P',
183 pay_magtape_generic.get_parameter_value(
184 'TRANSFER_ACT_ID')
185 FROM DUAL;
186 /*************************************************/
187 CURSOR rl1xml_asg_actions
188 IS
189 SELECT 'TRANSFER_ACT_ID=P',
190 pay_magtape_generic.get_parameter_value(
191 'TRANSFER_ACT_ID')
192 FROM DUAL;
193
194 cursor rl1xml_main_block is
195 select 'Version_Number=X' ,'Version 1.1'
196 from sys.dual;
197
198 cursor rl1xml_transfer_block is
199 select 'TRANSFER_ACT_ID=P', assignment_action_id
200 from pay_assignment_actions
201 where payroll_action_id =
202 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
203
204 PROCEDURE RL1XML_emplyer_data(p_assact_id IN NUMBER
205 ,p_emplyr_final1 OUT NOCOPY VARCHAR2
206 ,p_emplyr_final2 OUT NOCOPY VARCHAR2
207 ,p_emplyr_final3 OUT NOCOPY VARCHAR2
208 );
209
210 PROCEDURE xml_footnote_boxo(p_arch_assact_id IN NUMBER
211 ,p_assgn_id IN NUMBER
212 ,p_footnote_boxo1 OUT NOCOPY VARCHAR2
213 ,p_footnote_boxo2 OUT NOCOPY VARCHAR2
214 ,p_footnote_boxo3 OUT NOCOPY VARCHAR2
215 ) ;
216
217 PROCEDURE xml_report_end;
218
219 PROCEDURE xml_rl1_report_start;
220
221 PROCEDURE archive_ca_deinit (p_pactid IN NUMBER);
222
223 /********************************************/
224
225 END pay_ca_rl1_mag;
[Home] [Help]