1 PACKAGE BODY PAY_CA_ARCHIVE_RULES AS
2 /* $Header: paycaarcyema.pkb 120.4 2007/11/13 14:48:34 sapalani noship $ */
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 gv_package_name VARCHAR2(50) := 'pay_ca_archive_rules.';
42
43
44 ----------------------------------- range_cursor ------------------------------
45 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
46
47 l_proc VARCHAR2(240);
48 l_pre_organization_id VARCHAR2(50);
49 l_gre_id VARCHAR2(50);
50 lv_legislative_param VARCHAR2(240);
51
52 begin
53
54 l_proc := gv_package_name||'range_cursor';
55 hr_utility.set_location(l_proc, 10);
56
57 hr_utility.trace('PACTID = '||to_char(pactid));
58 select ppa.legislative_parameters
59 into lv_legislative_param
60 from pay_payroll_actions ppa
61 where payroll_action_id = pactid;
62 l_pre_organization_id := pay_core_utils.get_parameter('TRANSFER_PRE',lv_legislative_param);
63 l_gre_id := pay_core_utils.get_parameter('TRANSFER_GRE',lv_legislative_param);
64
65 if (l_gre_id is not null) then
66 sqlstr := 'AND PAA.tax_unit_id = '||to_number(l_gre_id);
67 elsif (l_pre_organization_id is not null) then
68 sqlstr := 'AND exists
69 ( select 1
70 from hr_organization_information hoi
71 where hoi.org_information_context = ''Canada Employer Identification''
72 and hoi.org_information2 = '''|| l_pre_organization_id||'''
73 and hoi.org_information5 in (''T4/RL1'',''T4A/RL1'',''T4A/RL2'')
74 and PAA.tax_unit_id=hoi.organization_id
75 )';
76
77 end if;
78 hr_utility.set_location(l_proc, 20);
79 hr_utility.trace(sqlstr);
80
81 exception when others then
82 hr_utility.set_location(l_proc, 30);
83 end range_cursor;
84 ---------------------------------- action_creation -----------------------------------
85 PROCEDURE action_creation(pactid IN NUMBER,
86 stperson IN NUMBER,
87 endperson IN NUMBER,
88 chunk IN NUMBER,
89 sqlstr OUT NOCOPY VARCHAR2) IS
90
91 l_proc VARCHAR2(240);
92 ld_effective_date DATE;
93 ld_year_end DATE;
94 ln_tax_unit_id NUMBER;
95 l_report_type VARCHAR2(30);
96 l_pre_organization_id VARCHAR2(50);
97
98 BEGIN
99 l_proc := gv_package_name||'action_creation';
100 hr_utility.set_location(l_proc, 10);
101 hr_utility.trace('PACTID = '||to_char(pactid));
102 SELECT effective_date,
103 pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters),
104 pay_core_utils.get_parameter('TRANSFER_PRE',legislative_parameters),
105 pay_core_utils.get_parameter('REPORT_TYPE',legislative_parameters)
106 INTO ld_effective_date,
107 ln_tax_unit_id,
108 l_pre_organization_id,
109 l_report_type
110 FROM pay_payroll_actions
111 WHERE payroll_action_id = pactid;
112
113 ld_year_end := add_months(trunc(ld_effective_date, 'Y'),12) -1;
114 hr_utility.trace('year end '|| to_char(ld_year_end,'dd-mm-yyyy'));
115
116 if (ln_tax_unit_id is not null) then
117 sqlstr := 'AND PAA.tax_unit_id = '||to_number(ln_tax_unit_id);
118 elsif (l_pre_organization_id is not null) then
119 sqlstr := 'AND exists
120 ( select 1
121 from hr_organization_information hoi
122 where hoi.org_information_context = ''Canada Employer Identification''
123 and hoi.org_information2 = '''|| l_pre_organization_id||'''
124 and hoi.org_information5 in (''T4/RL1'',''T4A/RL1'',''T4A/RL2'')
125 and PAA.tax_unit_id=hoi.organization_id
126 )';
127 end if;
128 sqlstr := sqlstr ||'AND not exists (SELECT 1
129 FROM pay_payroll_actions ppa,
130 pay_assignment_actions paa
131 WHERE ppa.report_type = ''' || l_report_type ||
132 ''' AND ppa.action_status = ''C''
133 AND ppa.effective_date = '''|| ld_year_end ||'''
134 AND ppa.payroll_action_id = paa.payroll_action_id
135 AND paa.action_status = ''C''
136 AND ppa.business_group_id = paf.business_group_id
137 AND paa.serial_number = TO_CHAR(paf.person_id))';
138
139 -- To include only Quebec location assignments for RL1 and RL2 (Bug: 6454571)
140 if (l_report_type in ('RL1', 'RL2')) then
141 sqlstr := sqlstr ||' AND exists
142 ( select 1
143 from pay_assignment_actions paa,
144 per_all_assignments_f paaf,
145 hr_locations hl
146 where paa.payroll_action_id = ppa.payroll_action_id
147 and paa.assignment_id = paaf.assignment_id
148 and paaf.person_id = paf.person_id --Added lastly
149 and paaf.location_id = hl.location_id
150 and hl.region_1= ''QC'') ';
151 end if;
152
153 hr_utility.set_location(l_proc, 20);
154 hr_utility.trace(sqlstr);
155
156 EXCEPTION WHEN OTHERS THEN
157 hr_utility.set_location(l_proc, 30);
158 END action_creation;
159
160 PROCEDURE archive_code (pactid IN NUMBER,
161 sqlstr OUT NOCOPY VARCHAR2) IS
162
163 l_pre_organization_id VARCHAR2(50);
164 l_gre_id VARCHAR2(50);
165 lv_legislative_param VARCHAR2(240);
166 l_proc VARCHAR2(240);
167
168 BEGIN
169 l_proc := gv_package_name||'archive_code';
170 hr_utility.set_location(l_proc, 10);
171
172 hr_utility.trace('PACTID = '||to_char(pactid));
173 select ppa.legislative_parameters
174 into lv_legislative_param
175 from pay_payroll_actions ppa
176 where payroll_action_id = pactid;
177 l_pre_organization_id := pay_core_utils.get_parameter('TRANSFER_PRE',lv_legislative_param);
178 l_gre_id := pay_core_utils.get_parameter('TRANSFER_GRE',lv_legislative_param);
179 if (l_gre_id is not null) then
180 sqlstr := 'AND PAA.tax_unit_id = '||to_number(l_gre_id);
181 elsif (l_pre_organization_id is not null) then
182 sqlstr := 'AND exists
183 ( select 1
184 from hr_organization_information hoi
185 where hoi.org_information_context = ''Canada Employer Identification''
186 and hoi.org_information2 = '''|| l_pre_organization_id||'''
187 and hoi.org_information5 in (''T4/RL1'',''T4A/RL1'',''T4A/RL2'')
188 and PAA.tax_unit_id=hoi.organization_id
189 )';
190 end if;
191 hr_utility.set_location(l_proc, 20);
192 hr_utility.trace(sqlstr);
193
194 EXCEPTION WHEN OTHERS THEN
195 hr_utility.set_location(l_proc, 30);
196 END archive_code;
197
198 --begin
199 --hr_utility.trace_on(null,'YREND_YEMA');
200
201 END PAY_CA_ARCHIVE_RULES;
[Home] [Help]