1 PACKAGE PAY_CA_RL1_AMEND_MAG AUTHID CURRENT_USER as
2 /* $Header: pycarlamd.pkh 120.2.12020000.2 2012/11/21 11:29:01 sbachu ship $ */
3 /*
4 Name
5 pay_ca_rl1_amend_mag
6
7 Purpose
8 The purpose of this package is to support the generation of
9 amended magnetic tape RL1.
10
11 ============================================================================*/
12
13
14 -- 'level_cnt' will allow the cursors to select function results,
15 -- whether it is a standard fuction such as to_char or a function
16 -- defined in a package (with the correct pragma restriction).
17
18 level_cnt NUMBER;
19 /*bug 14701466*/
20 type other_info_rec is record (code varchar2(10),
21 amount varchar2(50));
22
23 type other_info_tab is table of other_info_rec index by binary_integer;
24 g_further_info_list other_info_tab;
25
26 -- Used by Magnetic RL1 (RL1 format).
27 --
28 -- Sets up the tax unit context for the transmitter_GRE
29 --
30 --
31
32 CURSOR mag_rl1_amend_transmitter IS
33 Select 'PAYROLL_ACTION_ID=P',MAX(ppa.payroll_action_id),
34 'TRANSFER_CPP_MAX=P', pcli.information_value
35 FROM hr_organization_information hoi,
36 pay_payroll_actions PPA,
37 pay_ca_legislation_info pcli
38 WHERE to_char(hoi.organization_id) = pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
39 and hoi.org_information_context='Prov Reporting Est'
40 and ppa.report_type ='CAEOY_RL1_AMEND_PP'
41 and to_char(hoi.organization_id) = pycadar_pkg.get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)
42 and to_char(ppa.effective_date,'YYYY')=pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
43 and to_char(ppa.effective_date,'DD-MM')= '31-12'
44 and pcli.information_type = 'MAX_CPP_EARNINGS'
45 and ppa.effective_date between pcli.start_date and pcli.end_date
46 GROUP BY
47 'TRANSFER_CPP_MAX=P', pcli.information_value,
48 'PAYROLL_ACTION_ID=P';
49
50
51
52 --
53 -- Used by Magnetic RL1 (RL1 format).
54 --
55 -- Sets up the tax unit context for each employer to be reported. sets
56 -- up a parameter holding the tax unit identifier which can then be used by
57 -- subsequent cursors to restrict to employees within the employer.
58 --
59 --
60 CURSOR mag_rl1_amend_employer IS
61 select distinct 'PAYROLL_ACTION_ID=C',MAX(ppa.payroll_action_id),
62 'PAYROLL_ACTION_ID=P',MAX(ppa.payroll_action_id)
63 from pay_payroll_actions ppa,
64 hr_organization_information hoi
65 WHERE decode(hoi.org_information3,'Y',to_char(hoi.organization_id) ,hoi.org_information20) =
66 pay_magtape_generic.get_parameter_value('TRANSMITTER_PRE')
67 and hoi.org_information_context='Prov Reporting Est'
68 and to_char(hoi.organization_id) =
69 pay_ca_rl1_reg.get_parameter('PRE_ORGANIZATION_ID', ppa.legislative_parameters)
70 and ppa.action_status = 'C'
71 and to_char(ppa.effective_date,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
72 and to_char(ppa.effective_date,'DD-MM') = '31-12'
73 and ppa.report_type= 'CAEOY_RL1_AMEND_PP'
74 group by 'PAYROLL_ACTION_ID=C','PAYROLL_ACTION_ID=P';
75
76 --
77 -- Used by Magnetic RL1 (RL1 format).
78 --
79 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
80 -- for an employee. The date_earned context is set to be the least of the
81 -- end of the period being reported and the maximum end date of the
82 -- assignment. This ensures that personal information ie. name etc... is
83 -- current relative to the period being reported on.
84 --
85
86 CURSOR mag_rl1_amend_employee IS
87 SELECT
88 'TRANSFER_ACT_ID=P', paa.assignment_action_id
89 FROM
90 per_all_people_f ppf,
91 per_all_assignments_f paf,
92 pay_action_interlocks pai,
93 pay_assignment_actions paa,
94 pay_payroll_actions ppa,
95 pay_assignment_actions paa_arch,
96 pay_payroll_actions ppa_arch
97 WHERE
98 ppa.payroll_action_id =
99 to_number(pay_magtape_generic.get_parameter_value
100 ('TRANSFER_PAYROLL_ACTION_ID')) AND
101 paa.payroll_action_id = ppa.payroll_action_id AND
102 pai.locking_action_id = paa.assignment_action_id AND
103 paf.assignment_id = paa.assignment_id AND
104 ppf.person_id = paf.person_id AND
105 apps.pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
106 between
107 paf.effective_start_date and paf.effective_end_date AND
108 pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
109 between
110 ppf.effective_start_date and ppf.effective_end_date AND
111 paa_arch.assignment_action_id = pai.locked_action_id AND
112 paa_arch.payroll_action_id=ppa_arch.payroll_action_id AND
113 ppa_arch.report_type = 'CAEOY_RL1_AMEND_PP'
114 ORDER BY
115 ppf.last_name,ppf.first_name,ppf.middle_names;
116
117 PROCEDURE get_report_parameters
118 (
119 p_pactid IN NUMBER,
120 p_year_start IN OUT NOCOPY DATE,
121 p_year_end IN OUT NOCOPY DATE,
122 p_report_type IN OUT NOCOPY VARCHAR2,
123 p_business_group_id IN OUT NOCOPY NUMBER
124 );
125 /*bug 14701466*/
126 procedure store_further_information(p_arch_assact_id IN NUMBER
127 ,p_assgn_id IN NUMBER);
128
129 procedure get_further_information(p_index in number,
130 p_code in out nocopy varchar2,
131 p_amount in out nocopy varchar2);
132
133 PROCEDURE range_cursor (
134 p_pactid IN NUMBER,
135 p_sqlstr OUT NOCOPY VARCHAR2
136 );
137
138
139 PROCEDURE create_assignment_act(
140 p_pactid IN NUMBER,
141 p_stperson IN NUMBER,
142 p_endperson IN NUMBER,
143 p_chunk IN NUMBER );
144
145 --
146
147
148
149
150 --
151
152 FUNCTION get_arch_val(p_context_id IN NUMBER,
153 p_user_name IN VARCHAR2)
154 RETURN VARCHAR2;
155
156 --
157 --
158
159 PROCEDURE xml_transmitter_record;
160
161 PROCEDURE end_of_file;
162
163 PROCEDURE xml_employee_record;
164
165 PROCEDURE xml_employer_start;
166
167 PROCEDURE xml_employer_record;
168
169 CURSOR rl1_amend_asg_actions
170 IS
171 SELECT 'TRANSFER_ACT_ID=P',
172 pay_magtape_generic.get_parameter_value(
173 'TRANSFER_ACT_ID')
174 FROM DUAL;
175 /*************************************************/
176 CURSOR rl1xml_asg_actions
177 IS
178 SELECT 'TRANSFER_ACT_ID=P',
179 pay_magtape_generic.get_parameter_value(
180 'TRANSFER_ACT_ID')
181 FROM DUAL;
182
183 cursor rl1xml_main_block is
184 select 'Version_Number=X' ,'Version 1.1'
185 from sys.dual;
186
187 cursor rl1xml_transfer_block is
188 select 'TRANSFER_ACT_ID=P', assignment_action_id
189 from pay_assignment_actions
190 where payroll_action_id =
191 pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
192
193 PROCEDURE RL1XML_emplyer_data(p_assact_id IN NUMBER
194 ,p_emplyr_final1 OUT NOCOPY VARCHAR2
195 ,p_emplyr_final2 OUT NOCOPY VARCHAR2
196 ,p_emplyr_final3 OUT NOCOPY VARCHAR2
197 );
198
199 PROCEDURE xml_footnote_boxo(p_arch_assact_id IN NUMBER
200 ,p_assgn_id IN NUMBER
201 ,p_footnote_boxo1 OUT NOCOPY VARCHAR2
202 ,p_footnote_boxo2 OUT NOCOPY VARCHAR2
203 ,p_footnote_boxo3 OUT NOCOPY VARCHAR2
204 ) ;
205
206 PROCEDURE xml_report_end;
207
208 PROCEDURE xml_rl1_report_start;
209
210 PROCEDURE archive_ca_deinit (p_pactid IN NUMBER);
211
212 /********************************************/
213
214 END pay_ca_rl1_amend_mag;
[Home] [Help]