[Home] [Help]
PACKAGE BODY: APPS.PQP_EXPPREPROC_PKG
Source
1 package body pqp_exppreproc_pkg AS
2 /* $Header: pqexrppr.pkb 120.1 2006/03/03 15:57:24 sshetty noship $ */
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 CURSOR c_actions
105 (
106 pactid NUMBER,
107 stperson NUMBER,
108 endperson NUMBER,
109 off_date NUMBER
110 ) IS
111 SELECT
112 MAX(act_run.assignment_action_id),
113 asg.assignment_id
114 FROM per_assignments_f asg,
115 pay_payroll_actions ppa_run, /* run and quickpay payroll actions */
116 pay_assignment_actions act_run, /* run and quickpay assignment actions */
117 pay_payroll_actions ppa_gen, /* PYUGEN information */
118 per_time_periods ptp
119 WHERE ppa_gen.payroll_action_id = pactid
120 --Added by Gattu
121 AND ptp.payroll_id = nvl(l_payroll_id,
122 ppa_run.payroll_id)
123 AND (ppa_run.effective_date BETWEEN ptp.start_date AND
124 ptp.end_date
125 OR ppa_run.effective_date = ptp.regular_payment_date )
126 -- AND (DECODE (off_date ,1,ppa_run.effective_date,ppa_gen.effective_date
127 -- )
128 AND ptp.end_date
129 between /* date join btwn run and pyugen ppa */
130 ppa_gen.start_date
131 and ppa_gen.effective_date --)
132 AND ppa_run.action_type in ('R','Q','V')
133 AND ppa_run.action_status = 'C'
134 AND ppa_run.consolidation_set_id = l_consolidation_set_id
135 AND ppa_run.payroll_id = nvl(l_payroll_id,
136 ppa_run.payroll_id)
137 AND ppa_run.payroll_action_id = act_run.payroll_action_id
138 AND act_run.action_status = 'C'
139 AND asg.assignment_id = act_run.assignment_id
140 AND ppa_run.effective_date between /* date join btwn run and asg */
141 asg.effective_start_date
142 and asg.effective_end_date
143 AND asg.business_group_id = ppa_gen.business_group_id
144 AND ( asg.soft_coding_keyflex_id IN
145 (SELECT hsck.soft_coding_keyflex_id
146 FROM hr_soft_coding_keyflex hsck
147 WHERE hsck.segment1 = TO_CHAR(l_tax_unit_id)
148 )
149 OR l_tax_unit_id IS NULL)
150 AND (l_jd_cd IS NULL OR
151 l_jd_cd in (select jurisdiction_code
152 from pay_us_emp_state_tax_rules_f puest
153 WHERE puest.assignment_id=asg.assignment_id
154 AND ppa_run.effective_date BETWEEN
155 puest.effective_start_date AND
156 puest.effective_end_date)
157 )
158 AND asg.person_id between stperson and endperson
159 GROUP BY asg.assignment_id;
160
161
162 --
163 lockingactid NUMBER;
164 lockedactid NUMBER;
165 assignid NUMBER;
166 greid NUMBER;
167 num NUMBER;
168 action_type VARCHAR2(1);
169 l_payments_bal NUMBER;
170 --
171 -- algorithm is quite similar to the other process cases,
172 -- but we have to take into account assignments and
173 -- personal payment methods.
174 BEGIN
175
176 --hr_utility.trace_on('Y','ORACLE');
177 hr_utility.set_location('procpyr',1);
178
179 OPEN c_parameters(pactid);
180 FETCH c_parameters into leg_param,
181 l_consolidation_set_id,
182 l_payroll_id,
183 l_tax_unit_id ,
184 l_act_date,
185 l_jd_cd
186 ;
187 CLOSE c_parameters;
188 hr_utility.set_location('procpyr',1);
189
190 l_off_date :=pqp_expreplod_pkg.get_offset_date
191 ( l_payroll_id
192 ,l_consolidation_set_id
193 ,fnd_date.canonical_to_date(l_act_date));
194
195 OPEN c_actions(pactid,stperson,endperson,l_off_date);
196 num := 0;
197 LOOP
198 hr_utility.set_location('procpyr',2);
199 fetch c_actions into lockedactid,assignid;
200 if c_actions%found then num := num + 1; end if;
201 exit when c_actions%notfound;
202
203 --
204
205
206 hr_utility.set_location('procpyr',3);
207 select pay_assignment_actions_s.nextval
208 into lockingactid
209 from dual;
210 --
211 -- insert the action record.
212 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
213 --
214 -- insert an interlock to this action.
215 hr_nonrun_asact.insint(lockingactid,lockedactid);
216 --
217 END LOOP;
218 CLOSE c_actions;
219
220 end action_creation;
221 ---------------------------------- sort_action ----------------------------------
222 PROCEDURE sort_action
223 (
224 payactid in varchar2, /* payroll action id */
225 sqlstr in out nocopy varchar2, /* string holding the sql statement */
226 len out nocopy number /* length of the sql string */
227 ) IS
228
229 BEGIN
230
231 sqlstr := 'select paa1.rowid
232 from pay_assignment_actions paa1, -- PYUGEN assignment action
233 pay_payroll_actions ppa1 -- PYUGEN payroll action id
234 where ppa1.payroll_action_id = :pactid
235 and paa1.payroll_action_id = ppa1.payroll_action_id
236 order by paa1.assignment_action_id for update of paa1.assignment_id';
237
238 len := length(sqlstr); -- return the length of the string.
239 -- Added by tmehra for nocopy changes Feb'03
240 -- Not storing the original value of sqlstr
241
242 EXCEPTION
243 WHEN OTHERS THEN
244 len := 0;
245 sqlstr := NULL;
246 raise;
247
248 END sort_action;
249 ------------------------------ get_parameter -------------------------------
250 FUNCTION get_parameter(name in varchar2,
251 parameter_list varchar2) return varchar2
252 is
253 start_ptr number;
254 end_ptr number;
255 token_val pay_payroll_actions.legislative_parameters%type;
256 par_value pay_payroll_actions.legislative_parameters%type;
257 BEGIN
258 --
259 token_val := name||'=';
260 --
261 start_ptr := instr(parameter_list, token_val) + length(token_val);
262 end_ptr := instr(parameter_list, ' ',start_ptr);
263 --
264 /* if there is no spaces use then length of the string */
265 if end_ptr = 0 then
266 end_ptr := length(parameter_list)+1;
267 end if;
268 --
269 /* Did we find the token */
270 if instr(parameter_list, token_val) = 0 then
271 par_value := NULL;
272 else
273 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
274 end if;
275 --
276 return par_value;
277 --
278 END get_parameter;
279
280
281 --This is called in deinitialize phase
282 procedure deinitialize (pactid in number)
283 is
284 --
285 l_remove_act varchar2(10);
286 --
287 begin
288 --
289 select
290 pay_core_utils.get_parameter('REMOVE_ACT',
291 ppa.legislative_parameters)
292 into l_remove_act
293 from pay_payroll_actions ppa
294 where ppa.payroll_action_id = pactid;
295
296 --
297 if (l_remove_act is null or l_remove_act = 'Y') then
298 pay_archive.remove_report_actions(pactid);
299 end if;
300 --
301 end deinitialize;
302 END;