1 package body pqp_exppreproc_pkg AS
2 /* $Header: pqexrppr.pkb 120.1.12020000.3 2013/03/14 06:48:56 apudiped ship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5 --
6
7 --
8
9 */
10 ----------------------------------- range_cursor ----------------------------------
11 --
12 PROCEDURE range_cursor (pactid in number, sqlstr out nocopy varchar2) IS
13
14 leg_param pay_payroll_actions.legislative_parameters%TYPE ;
15 l_consolidation_set_id NUMBER ;
16 l_payroll_id NUMBER ;
17 l_tax_unit_id NUMBER ;
18 l_report_id NUMBER;
19 l_group_name varchar2(30);
20 l_start_date VARCHAR2(15); --DATE;
21 l_temp_date DATE;
22 --
23 BEGIN
24
25 SELECT ppa.legislative_parameters ,
26 pqp_exppreproc_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters) ,
27 pqp_exppreproc_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters) ,
28 pqp_exppreproc_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
29 pqp_exppreproc_pkg.get_parameter('TRANSFER_REPORT',ppa.legislative_parameters),
30 pqp_exppreproc_pkg.get_parameter('TRANSFER_GROUP',ppa.legislative_parameters),
31 pqp_exppreproc_pkg.get_parameter('TRANSFER_DATE',ppa.legislative_parameters)
32 INTO leg_param,
33 l_consolidation_set_id,
34 l_payroll_id,
35 l_tax_unit_id,
36 l_report_id ,
37 l_group_name,
38 l_start_date
39 FROM pay_payroll_actions ppa
40 WHERE ppa.payroll_action_id = pactid;
41 l_temp_date:=to_date(l_start_date,'YYYY/MM/DD');
42
43 pqp_expreplod_pkg.upd_payroll_actions (pactid ,
44 l_payroll_id ,
45 l_consolidation_set_id,
46 l_temp_date );
47
48 sqlstr := 'select distinct asg.person_id
49 from per_assignments_f asg,
50 pay_assignment_actions act_run, /* run and quickpay assignment actions */
51 pay_payroll_actions ppa_run, /* run and quickpay payroll actions */
52 pay_payroll_actions ppa_gen /* PYUGEN information */
53 where ppa_gen.payroll_action_id = :payroll_action_id
54 and ppa_run.action_type in (''R'',''Q'',''V'')
55 and ppa_run.action_status = ''C''
56 and ppa_run.consolidation_set_id = nvl('''||l_consolidation_set_id||''',
57 ppa_run.consolidation_set_id)
58 and ppa_run.payroll_id = nvl('''||l_payroll_id||''',
59 ppa_run.payroll_id)
60 and ppa_run.payroll_action_id = act_run.payroll_action_id
61 and act_run.action_status = ''C''
62 and asg.assignment_id = act_run.assignment_id
63 and ppa_run.effective_date between /* date join btwn run and asg */
64 asg.effective_start_date
65 and asg.effective_end_date
66 and asg.business_group_id +0 = ppa_gen.business_group_id
67 order by asg.person_id';
68
69 -- Added by tmehra for nocopy changes Feb'03
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 sqlstr := NULL;
74 raise;
75
76 END range_cursor;
77 ---------------------------------- action_creation ----------------------------------
78 --
79 PROCEDURE action_creation(pactid in number ,
80 stperson in number ,
81 endperson in number,
82 chunk in number) IS
83
84 leg_param pay_payroll_actions.legislative_parameters%TYPE;
85 l_consolidation_set_id NUMBER;
86 l_payroll_id NUMBER;
87 l_tax_unit_id NUMBER;
88 l_act_date VARCHAR2(15);
89 l_off_date number;
90 l_jd_cd VARCHAR2(16);
91 --
92 --Added TRANSFER_DATE parameter by Gattu to Fix 3837327.999
93 CURSOR c_parameters ( pactid number) is
94 SELECT ppa.legislative_parameters,
95 pqp_exppreproc_pkg.get_parameter('TRANSFER_CONC_SET',ppa.legislative_parameters),
96 pqp_exppreproc_pkg.get_parameter('TRANSFER_PAYROLL',ppa.legislative_parameters),
97 pqp_exppreproc_pkg.get_parameter('TRANSFER_GRE',ppa.legislative_parameters) ,
98 pqp_exppreproc_pkg.get_parameter('TRANSFER_DATE',ppa.legislative_parameters),
99 pqp_exppreproc_pkg.get_parameter('TRANSFER_JD',ppa.legislative_parameters)
100 FROM pay_payroll_actions ppa
101 WHERE ppa.payroll_action_id =pactid;
102
103
104 /* Bug 13955510: CA localization supports mulit GRE. Modified this cursor to
105 support this */
106 CURSOR c_actions
107 (
108 pactid NUMBER,
109 stperson NUMBER,
110 endperson NUMBER,
111 off_date NUMBER
112 ) IS
113 SELECT
114 MAX(act_run.assignment_action_id),
115 asg.assignment_id
116 FROM per_assignments_f asg,
117 pay_payroll_actions ppa_run, /* run and quickpay payroll actions */
118 pay_assignment_actions act_run, /* run and quickpay assignment actions */
119 pay_payroll_actions ppa_gen, /* PYUGEN information */
120 per_time_periods ptp
121 WHERE ppa_gen.payroll_action_id = pactid
122 --Added by Gattu
123 AND ptp.payroll_id = nvl(l_payroll_id,
124 ppa_run.payroll_id)
125 AND (ppa_run.effective_date BETWEEN ptp.start_date AND
126 ptp.end_date
127 OR ppa_run.effective_date = ptp.regular_payment_date )
128 -- AND (DECODE (off_date ,1,ppa_run.effective_date,ppa_gen.effective_date
129 -- )
130 AND ptp.end_date
131 between /* date join btwn run and pyugen ppa */
132 ppa_gen.start_date
133 and ppa_gen.effective_date --)
134 AND ppa_run.action_type in ('R','Q','V')
135 AND ppa_run.action_status = 'C'
136 AND ppa_run.consolidation_set_id = l_consolidation_set_id
137 AND ppa_run.payroll_id = nvl(l_payroll_id,
138 ppa_run.payroll_id)
139 AND ppa_run.payroll_action_id = act_run.payroll_action_id
140 AND act_run.action_status = 'C'
141 AND asg.assignment_id = act_run.assignment_id
142 AND ppa_run.effective_date between /* date join btwn run and asg */
143 asg.effective_start_date
144 and asg.effective_end_date
145 AND asg.business_group_id = ppa_gen.business_group_id
146 AND ( asg.soft_coding_keyflex_id IN
147 (SELECT hsck.soft_coding_keyflex_id
148 FROM hr_soft_coding_keyflex hsck
149 WHERE hsck.segment1 = TO_CHAR(l_tax_unit_id)
150 OR hsck.segment11 = TO_CHAR(l_tax_unit_id) /* Bug 13955510 */
151 OR hsck.segment12 = TO_CHAR(l_tax_unit_id)
152 )
153 OR l_tax_unit_id IS NULL)
154 AND (l_jd_cd IS NULL OR
155 l_jd_cd in (select jurisdiction_code
156 from pay_us_emp_state_tax_rules_f puest
157 WHERE puest.assignment_id=asg.assignment_id
158 AND ppa_run.effective_date BETWEEN
159 puest.effective_start_date AND
160 puest.effective_end_date)
161 )
162 AND asg.person_id between stperson and endperson
163 GROUP BY asg.assignment_id;
164
165 /* Bug 14530472: Added the below cursor similar to the cursor c_actions
166 if payroll id is specified in exception report then below query is
167 found to be optimal way hence enhancing the performance of the process */
168
169 CURSOR c_actions_with_payroll
170 (
171 pactid NUMBER,
172 stperson NUMBER,
173 endperson NUMBER,
174 off_date NUMBER
175 ) IS
176 SELECT /*+ LEADING(ppa_gen ppa_run ptp asg act_run) */
177 MAX(act_run.assignment_action_id),
178 asg.assignment_id
179 FROM per_assignments_f asg,
180 pay_payroll_actions ppa_run, /* run and quickpay payroll actions */
181 pay_assignment_actions act_run, /* run and quickpay assignment actions */
182 pay_payroll_actions ppa_gen, /* PYUGEN information */
183 per_time_periods ptp
184 WHERE ppa_gen.payroll_action_id = pactid
185 --Added by Gattu
186 AND ptp.payroll_id = l_payroll_id
187 AND (ppa_run.effective_date BETWEEN ptp.start_date AND
188 ptp.end_date
189 OR ppa_run.effective_date = ptp.regular_payment_date )
190 -- AND (DECODE (off_date ,1,ppa_run.effective_date,ppa_gen.effective_date
191 -- )
192 AND ptp.end_date
193 between /* date join btwn run and pyugen ppa */
194 ppa_gen.start_date
195 and ppa_gen.effective_date --)
196 AND ppa_run.action_type in ('R','Q','V')
197 AND ppa_run.action_status = 'C'
198 AND ppa_run.consolidation_set_id = l_consolidation_set_id
199 AND ppa_run.payroll_id = l_payroll_id
200 AND ppa_run.payroll_action_id = act_run.payroll_action_id
201 AND act_run.action_status = 'C'
202 AND asg.assignment_id = act_run.assignment_id
203 AND ppa_run.effective_date between /* date join btwn run and asg */
204 asg.effective_start_date
205 and asg.effective_end_date
206 AND asg.business_group_id = ppa_gen.business_group_id
207 AND ( asg.soft_coding_keyflex_id IN
208 (SELECT hsck.soft_coding_keyflex_id
209 FROM hr_soft_coding_keyflex hsck
210 WHERE hsck.segment1 = TO_CHAR(l_tax_unit_id)
211 OR hsck.segment11 = TO_CHAR(l_tax_unit_id) /* Bug 13955510 */
212 OR hsck.segment12 = TO_CHAR(l_tax_unit_id)
213 )
214 OR l_tax_unit_id IS NULL)
215 AND (l_jd_cd IS NULL OR
216 l_jd_cd in (select jurisdiction_code
217 from pay_us_emp_state_tax_rules_f puest
218 WHERE puest.assignment_id=asg.assignment_id
219 AND ppa_run.effective_date BETWEEN
220 puest.effective_start_date AND
221 puest.effective_end_date)
222 )
223 AND asg.person_id between stperson and endperson
224 GROUP BY asg.assignment_id;
225
226
227 --
228 lockingactid NUMBER;
229 lockedactid NUMBER;
230 assignid NUMBER;
231 greid NUMBER;
232 num NUMBER;
233 action_type VARCHAR2(1);
234 l_payments_bal NUMBER;
235 --
236 -- algorithm is quite similar to the other process cases,
237 -- but we have to take into account assignments and
238 -- personal payment methods.
239 BEGIN
240
241 --hr_utility.trace_on('Y','ORACLE');
242 hr_utility.set_location('procpyr',1);
243
244 OPEN c_parameters(pactid);
245 FETCH c_parameters into leg_param,
246 l_consolidation_set_id,
247 l_payroll_id,
248 l_tax_unit_id ,
249 l_act_date,
250 l_jd_cd
251 ;
252 CLOSE c_parameters;
253
254 hr_utility.set_location('procpyr',1);
255
256 l_off_date :=pqp_expreplod_pkg.get_offset_date
257 ( l_payroll_id
258 ,l_consolidation_set_id
259 ,fnd_date.canonical_to_date(l_act_date));
260
261 if l_payroll_id is null then -- Bug 14530472: If the report is run for entire consolodation set id
262 OPEN c_actions(pactid,stperson,endperson,l_off_date);
263 num := 0;
264 LOOP
265 hr_utility.set_location('procpyr',2);
266 fetch c_actions into lockedactid,assignid;
267 if c_actions%found then num := num + 1; end if;
268 exit when c_actions%notfound;
269
270 --
271 hr_utility.set_location('procpyr',3);
272 select pay_assignment_actions_s.nextval
273 into lockingactid
274 from dual;
275 --
276 -- insert the action record.
277 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
278 --
279 -- insert an interlock to this action.
280 hr_nonrun_asact.insint(lockingactid,lockedactid);
281 --
282 END LOOP;
283 CLOSE c_actions;
284 else -- Bug 14530472: If the report is run for a single payroll
285 OPEN c_actions_with_payroll(pactid,stperson,endperson,l_off_date);
286 num := 0;
287 LOOP
288 hr_utility.set_location('procpyr',2);
289 fetch c_actions_with_payroll into lockedactid,assignid;
290 if c_actions_with_payroll%found then num := num + 1; end if;
291 exit when c_actions_with_payroll%notfound;
292
293 --
294 hr_utility.set_location('procpyr',3);
295 select pay_assignment_actions_s.nextval
296 into lockingactid
297 from dual;
298 --
299 -- insert the action record.
300 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
301 --
302 -- insert an interlock to this action.
303 hr_nonrun_asact.insint(lockingactid,lockedactid);
304 --
305 END LOOP;
306 CLOSE c_actions_with_payroll;
307 end if;
308
309 end action_creation;
310 ---------------------------------- sort_action ----------------------------------
311 PROCEDURE sort_action
312 (
313 payactid in varchar2, /* payroll action id */
314 sqlstr in out nocopy varchar2, /* string holding the sql statement */
315 len out nocopy number /* length of the sql string */
316 ) IS
317
318 BEGIN
319
320 sqlstr := 'select paa1.rowid
321 from pay_assignment_actions paa1, -- PYUGEN assignment action
322 pay_payroll_actions ppa1 -- PYUGEN payroll action id
323 where ppa1.payroll_action_id = :pactid
324 and paa1.payroll_action_id = ppa1.payroll_action_id
325 order by paa1.assignment_action_id for update of paa1.assignment_id';
326
327 len := length(sqlstr); -- return the length of the string.
328 -- Added by tmehra for nocopy changes Feb'03
329 -- Not storing the original value of sqlstr
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 len := 0;
334 sqlstr := NULL;
335 raise;
336
337 END sort_action;
338 ------------------------------ get_parameter -------------------------------
339 FUNCTION get_parameter(name in varchar2,
340 parameter_list varchar2) return varchar2
341 is
342 start_ptr number;
343 end_ptr number;
344 token_val pay_payroll_actions.legislative_parameters%type;
345 par_value pay_payroll_actions.legislative_parameters%type;
346 BEGIN
347 --
348 token_val := name||'=';
349 --
350 start_ptr := instr(parameter_list, token_val) + length(token_val);
351 end_ptr := instr(parameter_list, ' ',start_ptr);
352 --
353 /* if there is no spaces use then length of the string */
354 if end_ptr = 0 then
355 end_ptr := length(parameter_list)+1;
356 end if;
357 --
358 /* Did we find the token */
359 if instr(parameter_list, token_val) = 0 then
360 par_value := NULL;
361 else
362 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
363 end if;
364 --
365 return par_value;
366 --
367 END get_parameter;
368
369
370 --This is called in deinitialize phase
371 procedure deinitialize (pactid in number)
372 is
373 --
374 l_remove_act varchar2(10);
375 --
376 begin
377 --
378 select
379 pay_core_utils.get_parameter('REMOVE_ACT',
380 ppa.legislative_parameters)
381 into l_remove_act
382 from pay_payroll_actions ppa
383 where ppa.payroll_action_id = pactid;
384
385 --
386 if (l_remove_act is null or l_remove_act = 'Y') then
387 pay_archive.remove_report_actions(pactid);
388 end if;
389 --
390 end deinitialize;
391 END;
[Home] [Help]