1 PACKAGE BODY PAY_CA_ARCHIVE_RULES AS
2 /* $Header: paycaarcyema.pkb 120.4.12010000.5 2008/11/13 15:16:12 sneelapa ship $ */
3 /******************************************************************************
4
5 ******************************************************************
6 * *
7 * Copyright (C) 1996 Oracle Corporation. *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disCLOSEd to third parties without *
16 * the express written permission of Oracle Corporation, *
17 * 500 Oracle Parkway, Redwood City, CA, 94065. *
18 * *
19 ******************************************************************
20 --
21 Name : PAY_CA_ARCHIVE_RULES
22 Description : This package contains the rules for archiving
23 for missing assignment report specific to CA legislation
24 --
25 Change List
26 -----------
27 Date Name Vers Bug Description
28 ----------- ---------- ----- -------- ----------------------------------
29 30-NOV-2005 pganguly 115.0 Created
30 19-JAN-2007 ydevi 115.1 4886285 adding the condition for
31 RL1 and RL2 PRE in range_cursor
32 and action creation
33 adding the procedure archive_code
34 08-OCT-2007 sapalani 115.3 6454571 Added condition in action_creation
35 to include only Quebec location
36 assignments for RL1 and RL2
37 13-NOV-2007 sapalani 115.4 6454571 Modified condition in action_creation
38 to include only Quebec location
39 assignments for RL1 and RL2
40
41 07-NOV-2008 sneelapa 115.5 7518875 Modified condition in action_creation
42 to check whether Employee is in Quebec
43 location in the Year for which
44 Missing Assignments concurrent request
45 is executed.
46 07-NOV-2008 sneelapa 115.6 7518875 Modified condition in action_creation
47 to check whether Employee is in Quebec
48 location in the Year for which
49 Missing Assignments concurrent request
50 is executed.
51 10-NOV-2008 sneelapa 115.7 7518875 Modified condition in action_creation
52 to check whether Employee is in Quebec
53 Comparing the STATE from
54 pay_action_context table instead of
55 per_all_assignments_f table.
56
57 13-NOV-2008 sneelapa 115.8 7518875 Modified code to resolve QA reported issue
58 during testing the bug 7518875.
59 If QC check is present in ACTION_CREATION
60 Procedure, it was validating the Employees
61 for whom Quickpay is executed.
65 procedure and added the code to check
62 This Validation was not working for Employees
63 for whom Payroll RUN was executed.
64 Commented the code in "action_creation"
66 QC in ARCHIVE_CODE procedure.
67 ******************************************************************************/
68 gv_package_name VARCHAR2(50) := 'pay_ca_archive_rules.';
69
70
71 ----------------------------------- range_cursor ------------------------------
72 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
73
74 l_proc VARCHAR2(240);
75 l_pre_organization_id VARCHAR2(50);
76 l_gre_id VARCHAR2(50);
77 lv_legislative_param VARCHAR2(240);
78
79 begin
80
81 l_proc := gv_package_name||'range_cursor';
82 hr_utility.set_location(l_proc, 10);
83
84 hr_utility.trace('PACTID = '||to_char(pactid));
85 select ppa.legislative_parameters
86 into lv_legislative_param
87 from pay_payroll_actions ppa
88 where payroll_action_id = pactid;
89 l_pre_organization_id := pay_core_utils.get_parameter('TRANSFER_PRE',lv_legislative_param);
90 l_gre_id := pay_core_utils.get_parameter('TRANSFER_GRE',lv_legislative_param);
91
92 if (l_gre_id is not null) then
93 sqlstr := 'AND PAA.tax_unit_id = '||to_number(l_gre_id);
94 elsif (l_pre_organization_id is not null) then
95 sqlstr := 'AND exists
96 ( select 1
97 from hr_organization_information hoi
98 where hoi.org_information_context = ''Canada Employer Identification''
99 and hoi.org_information2 = '''|| l_pre_organization_id||'''
100 and hoi.org_information5 in (''T4/RL1'',''T4A/RL1'',''T4A/RL2'')
101 and PAA.tax_unit_id=hoi.organization_id
102 )';
103
104 end if;
105 hr_utility.set_location(l_proc, 20);
106 hr_utility.trace(sqlstr);
107
108 exception when others then
109 hr_utility.set_location(l_proc, 30);
110 end range_cursor;
111 ---------------------------------- action_creation -----------------------------------
112 PROCEDURE action_creation(pactid IN NUMBER,
113 stperson IN NUMBER,
114 endperson IN NUMBER,
115 chunk IN NUMBER,
116 sqlstr OUT NOCOPY VARCHAR2) IS
117
118 l_proc VARCHAR2(240);
119 ld_effective_date DATE;
120 -- ld_year_start added by sneelapa for bug 7518875
121 ld_year_start DATE;
122 ld_year_end DATE;
123 ln_tax_unit_id NUMBER;
124 l_report_type VARCHAR2(30);
125 l_pre_organization_id VARCHAR2(50);
126
127 BEGIN
128 l_proc := gv_package_name||'action_creation';
129 hr_utility.set_location(l_proc, 10);
130 hr_utility.trace('PACTID = '||to_char(pactid));
131 SELECT effective_date,
132 pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters),
133 pay_core_utils.get_parameter('TRANSFER_PRE',legislative_parameters),
134 pay_core_utils.get_parameter('REPORT_TYPE',legislative_parameters)
135 INTO ld_effective_date,
136 ln_tax_unit_id,
137 l_pre_organization_id,
138 l_report_type
139 FROM pay_payroll_actions
140 WHERE payroll_action_id = pactid;
141
142 ld_year_start := trunc(ld_effective_date, 'Y');
143 hr_utility.trace('year start new '|| to_char(ld_year_start,'dd-mm-yyyy'));
144
145 ld_year_end := add_months(trunc(ld_effective_date, 'Y'),12) -1;
146 hr_utility.trace('year end '|| to_char(ld_year_end,'dd-mm-yyyy'));
147
148 if (ln_tax_unit_id is not null) then
149 sqlstr := 'AND PAA.tax_unit_id = '||to_number(ln_tax_unit_id);
150 elsif (l_pre_organization_id is not null) then
151 sqlstr := 'AND exists
152 ( select 1
153 from hr_organization_information hoi
154 where hoi.org_information_context = ''Canada Employer Identification''
155 and hoi.org_information2 = '''|| l_pre_organization_id||'''
156 and hoi.org_information5 in (''T4/RL1'',''T4A/RL1'',''T4A/RL2'')
157 and PAA.tax_unit_id=hoi.organization_id
158 )';
159 end if;
160 sqlstr := sqlstr ||'AND not exists (SELECT 1
161 FROM pay_payroll_actions ppa,
162 pay_assignment_actions paa
163 WHERE ppa.report_type = ''' || l_report_type ||
164 ''' AND ppa.action_status = ''C''
165 AND ppa.effective_date = '''|| ld_year_end ||'''
166 AND ppa.payroll_action_id = paa.payroll_action_id
167 AND paa.action_status = ''C''
168 AND ppa.business_group_id = paf.business_group_id
169 AND paa.serial_number = TO_CHAR(paf.person_id))';
170
171 -- To include only Quebec location assignments for RL1 and RL2 (Bug: 6454571)
172
173 -- modified for bug 7518875
174 -- Same validation is carried out in "archive_code" procedure, hence commenting the code.
175 /* if (l_report_type in ('RL1', 'RL2')) then
176 -- Modified the logic for checking QC state.
177 -- Comparing the pay_action_context data instead of per_all_assignments_f
178 -- table location id.
179 sqlstr := sqlstr ||' AND exists
180 ( select 1
181 from pay_action_contexts pac, ff_contexts fc,
182 pay_assignment_actions paa1
183 where paa1.payroll_action_id = ppa.payroll_action_id
184 and pac.context_id = fc.context_id
185 and paa1.assignment_action_id = pac.assignment_action_id
186 and ppa.effective_date BETWEEN '''||ld_year_start||''' and '''
187 || ld_year_end ||'''
188 and fc.context_name = ''JURISDICTION_CODE''
189 and pac.context_value = ''QC'') ';
190 end if;
191 */
192 hr_utility.set_location(l_proc, 20);
193 hr_utility.trace(sqlstr);
194
195 EXCEPTION WHEN OTHERS THEN
196 hr_utility.set_location(l_proc, 30);
197 END action_creation;
198
199 PROCEDURE archive_code (pactid IN NUMBER,
200 sqlstr OUT NOCOPY VARCHAR2) IS
201
202 l_pre_organization_id VARCHAR2(50);
203 l_gre_id VARCHAR2(50);
204 lv_legislative_param VARCHAR2(240);
205 l_proc VARCHAR2(240);
206
207 -- fix for bug 7518875 starts here.
208 ld_effective_date DATE;
209 ld_year_start DATE;
210 ld_year_end DATE;
211 l_report_type VARCHAR2(30);
212 -- fix for bug 7518875 ends here.
213
214 BEGIN
215 l_proc := gv_package_name||'archive_code';
216 hr_utility.set_location(l_proc, 10);
217
218 hr_utility.trace('PACTID = '||to_char(pactid));
219 select ppa.legislative_parameters
220 into lv_legislative_param
221 from pay_payroll_actions ppa
222 where payroll_action_id = pactid;
223 l_pre_organization_id := pay_core_utils.get_parameter('TRANSFER_PRE',lv_legislative_param);
224 l_gre_id := pay_core_utils.get_parameter('TRANSFER_GRE',lv_legislative_param);
225
226 -- fix for bug 7518875 starts here.
227
228 SELECT effective_date,
229 pay_core_utils.get_parameter('REPORT_TYPE',legislative_parameters)
230 INTO ld_effective_date,
231 l_report_type
232 FROM pay_payroll_actions
233 WHERE payroll_action_id = pactid;
234
235 ld_year_start := trunc(ld_effective_date, 'Y');
236 hr_utility.trace('year start new '|| to_char(ld_year_start,'dd-mm-yyyy'));
237
238 ld_year_end := add_months(trunc(ld_effective_date, 'Y'),12) -1;
239 hr_utility.trace('year end '|| to_char(ld_year_end,'dd-mm-yyyy'));
240
241 -- fix for bug 7518875 ends here.
242
243 if (l_gre_id is not null) then
244 sqlstr := 'AND PAA.tax_unit_id = '||to_number(l_gre_id);
245 elsif (l_pre_organization_id is not null) then
246 sqlstr := 'AND exists
247 ( select 1
248 from hr_organization_information hoi
249 where hoi.org_information_context = ''Canada Employer Identification''
250 and hoi.org_information2 = '''|| l_pre_organization_id||'''
251 and hoi.org_information5 in (''T4/RL1'',''T4A/RL1'',''T4A/RL2'')
252 and PAA.tax_unit_id=hoi.organization_id
253 )';
254 end if;
255
256 -- fix for bug 7518875 starts here.
257
258 if (l_report_type in ('RL1', 'RL2')) then
259 sqlstr := sqlstr ||' AND exists
260 ( select 1
261 from pay_action_contexts pac, ff_contexts fc--,pay_assignment_actions paa1
262 where pac.context_id = fc.context_id
263 and paa.assignment_action_id = pac.assignment_action_id
264 and ppa.effective_date BETWEEN '''||ld_year_start||''' and '''
265 || ld_year_end ||'''
266 and fc.context_name = ''JURISDICTION_CODE''
267 and pac.context_value = ''QC'') ';
268
269 end if;
270
271 -- fix for bug 7518875 ends here.
272
273 hr_utility.set_location(l_proc, 20);
274 hr_utility.trace(sqlstr);
275
276 EXCEPTION WHEN OTHERS THEN
277 hr_utility.set_location(l_proc, 30);
278 END archive_code;
279
280 --begin
281 --hr_utility.trace_on(null,'YREND_YEMA');
282
283 END PAY_CA_ARCHIVE_RULES;
[Home] [Help]