1 PACKAGE BODY PAY_US_RETRO_OVERLAP
2 -- /* $Header: pyusenro.pkb 120.5 2011/03/24 06:42:58 nkjaladi noship $ */
3 /*
4
5 Name : PAY_US_RETRO_OVERLAP
6
7 Description : This procedure is used to Enable the Retro Overlap Functionality
8
9 Change List
10 -----------
11 Date Name Vers Bug No Description
12 ----------- ---------- ------ ------- -----------------------------------
13 06-06-2008 svannian 115.0 Intial Version
14 07-03-2011 nkjaladi 115.3 10428204 Modified procedure enable_retro_overlap
15 to check if any retro pay by element
16 process is run, if the data upgrdation
17 is not run. If no retro pay by element
18 process is run then enable the retro
19 overlap functionality
20 09-03-2011 nkjaladi 115.3 10428204 Modified query of cursor
21 c_check_retro_pay_element for better
22 performance.
23 09-03-2011 nkjaladi 115.4 10428204 Modified query of cursor
24 c_check_retro_pay_element to change
25 the table name to
26 hr_all_organization_units
27 17-03-2011 nkjaladi 115.5 10428204 Modified procedure enable_retro_overlap
28 to check pay_retro_assignments table
29 rather than action_type 'L' to verfiy
30 if retropay(enhanced) is used by
31 customer or not
32
33 */
34 AS
35 gv_package_name VARCHAR2(100);
36
37 -- This procedure is used to Enable Retro Overlap functionality
38 -- ----------------------------------------------------------------------------
39 -- |-------------------------< enable_retro_overlap >--------------------------|
40 -- ----------------------------------------------------------------------------
41 PROCEDURE enable_retro_overlap( errbuf OUT NOCOPY varchar2
42 ,retcode OUT NOCOPY varchar2
43 )
44 IS
45
46 -- Cursor to fetch legislation rule mode
47 CURSOR c_retro_rule_check( cp_rule_type IN varchar2
48 ,cp_legislation_code IN Varchar2)
49 IS
50 SELECT rule_mode
51 FROM pay_legislation_rules
52 WHERE legislation_code = cp_legislation_code
53 AND rule_type = cp_rule_type;
54
55 -- Cursor to fetch upgrade definition id of Enhanced Retropay
56 CURSOR c_upgrade_definitions( cp_short_name IN varchar2
57 ,cp_legislation_code IN Varchar2)
58 IS
59 SELECT upgrade_definition_id
60 FROM pay_upgrade_definitions
61 WHERE legislation_code = cp_legislation_code
62 AND short_name=cp_short_name;
63
64 -- Cursor to fetch the status of Enhanced Retropay
65 CURSOR c_upgrade_status( cp_upgrade_defINation_id IN varchar2
66 ,cp_legISlation_code IN Varchar2)
67 IS
68 SELECT status
69 FROM pay_upgrade_status
70 WHERE legislation_code = cp_legislation_code
71 AND upgrade_definition_id = cp_upgrade_defination_id;
72 --
73
74 -- #10428204 start
75 -- Cursor to check if retro pay by element process is run or not
76 /*
77 CURSOR c_check_retro_pay_element (p_legislation_code IN varchar2)
78 IS
79 SELECT 'Y'
80 FROM pay_payroll_actions ppa
81 WHERE ppa.action_type = 'L'
82 AND EXISTS ( SELECT 'Y'
83 FROM hr_organization_information hoi,
84 hr_all_organization_units hou
85 WHERE hoi.org_information_context = 'Business Group Information'
86 AND hou.organization_id = hoi.organization_id
87 AND hoi.org_information9 = UPPER(p_legislation_code)
88 AND hoi.organization_id = ppa.business_group_id
89 )
90 AND ROWNUM < 2; */
91 /* Above query is introduced to improve performance
92 SELECT 'Y'
93 FROM pay_payroll_actions ppa
94 WHERE ppa.action_type = 'L'
95 AND EXISTS ( SELECT 'Y'
96 FROM hr_organization_information hoi
97 WHERE hoi.org_information_context = 'Business Group Information'
98 AND hoi.org_information9 = UPPER(p_legislation_code)
99 AND hoi.organization_id = ppa.business_group_id)
100 AND ROWNUM < 2;*/
101 CURSOR c_get_legislative_bg(p_legislation_code IN VARCHAR2)
102 IS
103 SELECT business_group_id
104 FROM per_business_groups pbg,
105 hr_organization_information hoi
106 WHERE hoi.organization_id = pbg.business_group_id
107 AND hoi.org_information_context = 'Business Group Information'
108 AND hoi.org_information9 = UPPER(p_legislation_code);
109
110 CURSOR c_chk_adv_retro_pay_enabled(p_business_group_id IN NUMBER)
111 IS
112 SELECT 'Y'
113 FROM pay_assignment_actions paa ,
114 pay_retro_assignments pra,
115 pay_payroll_actions ppa
116 WHERE ppa.action_type = 'L'
117 AND ppa.business_group_id = p_business_group_id
118 AND paa.payroll_action_id = ppa.payroll_action_id
119 AND pra.retro_assignment_action_id=paa.assignment_action_id
120 AND ROWNUM < 2;
121
122 -- #10428204 end
123 lv_qualified VARCHAR2(1);
124 lv_procedure_name VARCHAR2(100);
125 lv_legislation_code VARCHAR2(150);
126 lv_short_name VARCHAR2(100);
127 lv_upgrade_defination_id NUMBER;
128 ln_exists VARCHAR2(1);
129 lv_upgrade_status VARCHAR2(1);
130 l_enro_flag VARCHAR2(1); -- #10428204
131 l_retro_elem_chk_flag VARCHAR2(1); -- #10428204
132
133 TYPE character_data_table IS TABLE OF VARCHAR2(280)
134 INDEX BY BINARY_INTEGER;
135 ltt_rule_type character_data_table;
136 ltt_rule_mode character_data_table;
137 l_bg_count NUMBER; -- #10428204
138 l_adv_ret_bg_count NUMBER; -- #10428204
139 l_retro_payroll_count NUMBER; -- #10428204
140
141 BEGIN
142
143 lv_procedure_name := '.enable_retro_overlap';
144 fnd_file.put_line(fnd_file.log,'Entering ' || gv_package_name || lv_procedure_name);
145 lv_legislation_code := 'US';
146
147 -- These are the legislation rules to check whether Enhanced Retropay is enabled or not
148 ltt_rule_type(1) := 'RETRO_DELETE';
149 ltt_rule_mode(1) := 'N';
150 ltt_rule_type(2) := 'ADVANCED_RETRO';
151 ltt_rule_mode(2) := 'Y';
152
153 -- Legislation rules for enabling Retro Overlap
154 ltt_rule_type(3) := 'RETRO_OVERLAP';
155 ltt_rule_mode(3) := 'N';
156
157 -- Checking whether Enhanced Retropay is enabled or not
158 FOR i in 1 ..2 LOOP
159 OPEN c_retro_rule_check(ltt_rule_type(i),lv_legislation_code) ;
160 FETCH c_retro_rule_check into ln_exists;
161
162 IF (c_retro_rule_check%FOUND) AND (ltt_rule_mode(i) = ln_exists )
163 THEN
164 lv_qualified := 'Y';
165 ELSE
166 lv_qualified := 'N';
167 fnd_file.put_line(fnd_file.log,'Retro Overlap upgrade not performed when '||ltt_rule_type(i)||' legislation rule is not set to '||ltt_rule_mode(i));
168 fnd_file.put_line(fnd_file.log,'Enhanced RetroPay is not enabled');
169 exit;
170 END IF;
171
172 CLOSE c_retro_rule_check;
173 END LOOP;
174 -- #10428204
175 l_enro_flag := 'N';
176
177 IF lv_qualified = 'Y'
178 THEN
179 lv_short_name := 'US_ENHANCED_RETROPAY';
180 OPEN c_upgrade_definitions(lv_short_name,lv_legislation_code);
181 FETCH c_upgrade_definitions into lv_upgrade_defination_id;
182
183 -- Checking whether Enhanced Retropay entire is found in the table pay_upgrade_definitions or not
184 IF(c_upgrade_definitions%FOUND)
185 THEN
186 lv_upgrade_status := 'C';
187 OPEN c_upgrade_status(lv_upgrade_defination_id,lv_legislation_code);
188 FETCH c_upgrade_status into ln_exists;
189
190 -- Checking the status of Enhanced Retro pay
191 IF(c_upgrade_status%FOUND) AND (lv_upgrade_status = ln_exists or ln_exists = 'P' )
192 THEN
193 /* #10428204 Moved the legislation parameter creation inorder to
194 make it common for upgrade definition check and retro pay
195 by element process check*/
196 l_enro_flag :='Y';
197
198 /* #10428204 commented and moved it outside this if condition.
199 OPEN c_retro_rule_check(ltt_rule_type(3),lv_legislation_code);
200 FETCH c_retro_rule_check into ln_exists;
201
202 -- Checking whether Retro Overlap is Enabled or not
203 IF c_retro_rule_check%FOUND
204 THEN
205 IF (ltt_rule_mode(3)= ln_exists)
206 THEN
207 fnd_file.put_line(fnd_file.log,'Retro Overlap is already Enabled');
208 ELSE
209 -- Updating Rero Ovelap rule mode to N
210 UPDATE pay_legislation_rules
211 SET RULE_MODE = ltt_rule_mode(3)
212 WHERE legislation_code = lv_legislation_code
213 AND rule_type = ltt_rule_type(3);
214 fnd_file.put_line(fnd_file.log,'Retro Overlap Enabled successfully');
215 END IF;
216
217 ELSE
218 --If Retro Overlap is not enabled inserting new legislation rule
219 INSERT INTO pay_legislation_rules(legislation_code,rule_type,rule_mode)
220 VALUES( lv_legislation_code,ltt_rule_type(3),ltt_rule_mode(3));
221 fnd_file.put_line(fnd_file.log,'Retro Overlap Enabled successfully');
222 END IF;
223
224 CLOSE c_retro_rule_check; */
225
226 ELSE
227 /* #10428204 Modified code to handle the scenario of customer using
228 enhanced retro pay from starting and not used Retro Pay by Element
229 at all. In this scenario customer doesn't have to run the Generic
230 upgrade process for migrating the elements. Then need to check
231 if any payroll action of action type "retro pay by element" exists.
232 If it doesn't exists set the flag to 'Y' so that retro over lap
233 legislation rule can be inserted */
234
235 IF(c_upgrade_status%FOUND) THEN
236 fnd_file.put_line(fnd_file.log,'Upgrade process of Enhanced RetroPay has Errored' );
237 fnd_file.put_line(fnd_file.log,'The status of Enhanced RetroPay must be C in pay_upgrade_status table');
238 l_enro_flag := 'N';
239 END IF;
240 --
241 IF(c_upgrade_status%NOTFOUND) THEN
242 /*OPEN c_check_retro_pay_element(lv_legislation_code);
243 FETCH c_check_retro_pay_element INTO l_retro_elem_chk_flag;
244 IF (c_check_retro_pay_element%NOTFOUND) THEN
245 l_enro_flag := 'Y';
246 ELSE
247 fnd_file.put_line(fnd_file.log,'Retro Pay by Element payroll process has been run.' );
248 fnd_file.put_line(fnd_file.log,'Upgrade process of Enhanced Retropay is not run ');
249 fnd_file.put_line(fnd_file.log,'There is no entries found in pay_upgrade_status table for Enhanced RetroPay');
250 l_enro_flag := 'N';
251 END IF;
252 CLOSE c_check_retro_pay_element; */
253 /* Retro Enhanced and Retro Pay by element share same action type 'L'
254 So To differentiate the process we need to check the pay retro assignments table
255 this is used only for advanced retro pay. Once advanced retro pay is used
256 ct. wouldn't be able to move back to retro pay by element process*/
257 l_bg_count := 0;
258 l_adv_ret_bg_count := 0;
259 FOR lc IN c_get_legislative_bg('US')
260 LOOP
261 l_bg_count := l_bg_count +1;
262 OPEN c_chk_adv_retro_pay_enabled(lc.business_group_id);
263 FETCH c_chk_adv_retro_pay_enabled INTO l_retro_elem_chk_flag;
264 IF (c_chk_adv_retro_pay_enabled%FOUND) THEN
265 l_adv_ret_bg_count := l_adv_ret_bg_count + 1;
266 END IF;
267 IF (c_chk_adv_retro_pay_enabled%NOTFOUND) THEN
268 l_retro_payroll_count := -1;
269 SELECT count(1)
270 INTO l_retro_payroll_count
271 FROM pay_payroll_actions
272 WHERE business_group_id = lc.business_group_id
273 AND action_type = 'L';
274 IF l_retro_payroll_count = 0 THEN
275 l_adv_ret_bg_count := l_adv_ret_bg_count + 1;
276 END IF;
277 END IF;
278 CLOSE c_chk_adv_retro_pay_enabled;
279 END LOOP;
280 --
281 IF l_bg_count = l_adv_ret_bg_count THEN
282 l_enro_flag := 'Y';
283 ELSE
284 l_enro_flag := 'N';
285 fnd_file.put_line(fnd_file.log,'some the business group has Retro pay by element Process ');
286 fnd_file.put_line(fnd_file.log,'Need to Run the Upgrade process of Enhanced Retropay ');
287 END IF;
288 END IF;
289 END IF;
290
291 CLOSE c_upgrade_status;
292 ELSE
293 fnd_file.put_line(fnd_file.log,'Upgrade process of Enhanced Retropay has not completed ');
294 fnd_file.put_line(fnd_file.log,'There is no entries found in pay_upgrade_definitions table for Enhanced RetroPay');
295 l_enro_flag := 'N';
296 END IF;
297
298 --
299 -- #10428204 Moved the retro overlap legislation rule outside
300 --
301 IF l_enro_flag = 'Y' THEN
302 OPEN c_retro_rule_check(ltt_rule_type(3),lv_legislation_code);
303 FETCH c_retro_rule_check into ln_exists;
304
305 -- Checking whether Retro Overlap is Enabled or not
306 IF c_retro_rule_check%FOUND
307 THEN
308 IF (ltt_rule_mode(3)= ln_exists)
309 THEN
310 fnd_file.put_line(fnd_file.log,'Retro Overlap is already Enabled');
311 ELSE
312 -- Updating Rero Ovelap rule mode to N
313 UPDATE pay_legislation_rules
314 SET RULE_MODE = ltt_rule_mode(3)
315 WHERE legislation_code = lv_legislation_code
316 AND rule_type = ltt_rule_type(3);
317 fnd_file.put_line(fnd_file.log,'Retro Overlap Enabled successfully');
318 END IF;
319 ELSE
320 --If Retro Overlap is not enabled inserting new legislation rule
321 INSERT INTO pay_legislation_rules(legislation_code,rule_type,rule_mode)
322 VALUES( lv_legislation_code,ltt_rule_type(3),ltt_rule_mode(3));
323 fnd_file.put_line(fnd_file.log,'Retro Overlap Enabled successfully');
324 END IF;
325
326 CLOSE c_retro_rule_check;
327 END IF;
328 CLOSE c_upgrade_definitions;
329 END IF;
330
331 fnd_file.put_line(fnd_file.log,'Leaving ' || gv_package_name || lv_procedure_name);
332
333 EXCEPTION
334 WHEN others
335 THEN
336 fnd_file.put_line(fnd_file.log,gv_package_name || lv_procedure_name);
337 fnd_file.put_line(fnd_file.log,'ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
338 RAISE;
339 END enable_retro_overlap;
340
341 BEGIN
342 gv_package_name := 'PAY_US_RETRO_OVERLAP';
343 END PAY_US_RETRO_OVERLAP;
[Home] [Help]