[Home] [Help]
PACKAGE BODY: APPS.PAY_EOSY_AC_PKG
Source
1 PACKAGE BODY pay_eosy_ac_pkg AS
2 /* $Header: pyuseoac.pkb 120.0.12000000.2 2007/07/16 17:31:14 rpasumar noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * ma for EO Survey report.
17 26-Apr-2001 fusman 115.0
18
19 Name: This package defines the cursors needed to run
20 EO Survey Multi-Threaded
21
22 Change List
23 -----------
24 Date Name Vers Description
25 ----------- ---------- ----- -----------------------------------
26 25-Apr-01 fusman 115.0 Created.
27 28-JUN-04 vbanner 115.1 Changed to pass GSCC.
28 30-SEP-04 ynegoro 115.2 Changed c_actions cursor and range_cursor
29 by BUG3886008
30 01-OCT-04 ynegoro 115.3 Changed c_actions cursor
31 04-OCT-04 ynegoro 115.4 Changed c_actions cursor to pick up
32 terminated employee
33 25-OCT-04 ynegoro 115.5 Changed c_actions cursor in action_creation
34 BUG3941460 and BUG3964366
35 29-OCT-04 ynegoro Changed range_cursor and c_actions
36 cursor BUG3958260
37 04-NOV-04 ynegoro 115.6 Deleted previous change for BUG3958260
38 3958260 is not a bug.
39 16-JUL-07 rpasumar 115.7 To report a person whose ethnic origin is blank.
40 */
41
42 --------------------------- range_cursor ---------------------------------
43 PROCEDURE range_cursor (pactid in number,
44 sqlstr out nocopy varchar2) is
45 l_payroll_id number;
46 leg_param pay_payroll_actions.legislative_parameters%type;
47 l_package varchar2(70);
48
49 Begin
50
51 l_package := 'pay_eosy_ac_pkg.range_cursor';
52 --hr_utility.trace_on(null,'fusman');
53 hr_utility.set_location('Entering.. ' || l_package,10);
54
55 sqlstr:=
56 'select distinct paf.person_id
57 from pay_payroll_actions ppa, -- pyugen
58 per_gen_hierarchy_nodes pghn,
59 per_assignments_f paf,
60 per_assignment_status_types past,
61 per_jobs pj,
62 fnd_common_lookups fcl
63 where ppa.payroll_action_id = :pactid
64 AND pghn.hierarchy_version_id = pay_eosy_ac_pkg.get_parameter
65 (''HI_VER_ID'',ppa.legislative_parameters)
66 AND (
67 (
68 entity_id = nvl(pay_eosy_ac_pkg.get_parameter
69 (''EST_ID'',ppa.legislative_parameters),pghn.entity_id)
70 AND node_type =''EST''
71 )
72 OR
73 (
74 parent_hierarchy_node_id in(SELECT hierarchy_node_id
75 FROM per_gen_hierarchy_nodes
76 WHERE hierarchy_version_id =pay_eosy_ac_pkg.get_parameter
77 (''HI_VER_ID'',ppa.legislative_parameters)
78 AND entity_id = nvl(pay_eosy_ac_pkg.get_parameter
79 (''EST_ID'',ppa.legislative_parameters),entity_id)
80 AND node_type = ''EST'')
81 AND node_type = ''LOC''
82 )
83 )
84 and paf.location_id = pghn.entity_id
85 and paf.assignment_status_type_id = past.assignment_status_type_id
86 and past.per_system_Status = ''ACTIVE_ASSIGN''
87 and paf.effective_start_Date = (select max(effective_Start_date)
88 from per_assignments_f paf1
89 where paf1.assignment_id = paf.assignment_id
90 and paf1.effective_start_Date <=ppa.start_Date
91 -- and paf1.effective_end_date >=ppa.start_date
92 and paf1.effective_end_date >=trunc(ppa.start_date,''Y'') -- BUG3886008
93 and paf1.assignment_status_type_id =
94 paf.assignment_Status_type_id
95 and paf1.primary_flag = ''Y''
96 -- and paf1.location_id = paf.location_id -- BUG3958260
97 )
98 and paf.assignment_type = ''E''
99 and paf.primary_flag=''Y''
100 AND paf.job_id = pj.job_id
101 AND pj.job_information1= fcl.lookup_code
102 AND fcl.lookup_type = ''US_EEO1_JOB_CATEGORIES''
103 /*AND exists
104 (SELECT ''x'' from per_people_f
105 WHERE person_id = paf.person_id
106 AND per_information1 is not null)*/
107 order by paf.person_id';
108
109 hr_utility.trace('pactid = ' || pactid);
110 hr_utility.set_location('Leaving.. ' || l_package,20);
111 --hr_utility.trace_off;
112 END range_cursor;
113
114
115 ----------------------------- action_creation --------------------------------
116 PROCEDURE action_creation( pactid in number,
117 stperson in number,
118 endperson in number,
119 chunk in number)
120 IS
121
122 cursor c_actions(pactid number,
123 stperson number,
124 endperson number,
125 l_start_date date,
126 l_end_date date,
127 l_version_id number,
128 l_est_id number ) is
129 SELECT paa.assignment_action_id,
130 paf.assignment_id,
131 paf.person_id,
132 paa.tax_unit_id,
133 paf.location_id
134 FROM pay_assignment_actions paa,
135 pay_payroll_actions ppa,
136 per_assignments_f paf,
137 per_jobs pj,
138 per_gen_hierarchy_nodes pghn,
139 -- per_assignment_status_types past, -- BUG3886008
140 fnd_common_lookups fcl
141 WHERE ppa.effective_date between l_start_date and l_end_date
142
143 AND ppa.action_type in ('R','Q','I')
144 AND ppa.action_status = 'C'
145 AND paa.payroll_action_id = ppa.payroll_action_id
146 AND paa.action_status = 'C'
147 AND paa.action_sequence IN (
148 SELECT MAX(paa2.action_sequence)
149 FROM pay_action_classifications pac,
150 pay_payroll_actions ppa2,
151 pay_assignment_actions paa2,
152 per_assignments_f paf1
153 WHERE paf1.person_id = paf.person_id
154 AND paa2.assignment_id = paf1.assignment_id
155 AND paf1.primary_flag = 'Y' -- BUG3941460
156 AND paa2.tax_unit_id = paa.tax_unit_id
157 AND ppa2.payroll_action_id = paa2.payroll_action_id
158 AND ppa2.action_type = pac.action_type
159 AND pac.classification_name = 'SEQUENCED'
160 AND paa2.action_status = 'C' -- BUG3886008
161 AND ppa2.effective_date <= l_end_Date -- BUG3964366
162 --AND ppa2.effective_date between paf1.effective_start_date
163 -- and paf1.effective_end_date -- BUG3958260
164 --AND paf1.location_id = paf.location_id -- BUG3958260
165 )
166
167 AND paf.assignment_id = paa.assignment_id
168 AND paf.person_id between stperson and endperson
169 AND paf.location_id = pghn.entity_id
170 ANd pghn.hierarchy_version_id = l_version_id
171 AND (
172 (
173 pghn.entity_id = nvl(l_est_id,pghn.entity_id)
174 AND pghn.node_type ='EST'
175 )
176 OR
177 (
178 pghn.parent_hierarchy_node_id
179 in(select pghn2.hierarchy_node_id
180 from per_gen_hierarchy_nodes pghn2
181 where pghn2.hierarchy_version_id =l_version_id
182 and pghn2.entity_id = nvl(l_est_id,pghn2.entity_id)
183 and pghn2.node_type = 'EST')
184 AND pghn.node_type = 'LOC'
185 )
186 )
187 -- AND paf.assignment_status_type_id = past.assignment_status_type_id
188 -- AND past.per_system_Status = 'ACTIVE_ASSIGN' -- BUG3886008
189 AND paf.effective_start_Date = (select max(effective_Start_date)
190 from per_assignments_f paf1
191 where paf1.assignment_id = paf.assignment_id
192 and paf1.effective_start_Date <=l_end_date
193 and paf1.effective_end_date >= l_start_date
194 -- and paf1.assignment_status_type_id =
195 -- paf.assignment_Status_type_id
196 and paf1.primary_flag = 'Y'
197 and paf1.location_id = paf.location_id --BUG3958260
198 )
199 -- AND ppa.effective_date between paf.effective_start_Date and paf.effective_end_Date -- BUG3886008
200 AND paf.assignment_type = 'E'
201 AND paf.primary_flag='Y'
202 AND paf.job_id = pj.job_id
203 AND pj.job_information1= fcl.lookup_code
204 AND fcl.lookup_type = 'US_EEO1_JOB_CATEGORIES';
205 /*AND exists
206 (SELECT 'x' from per_people_f ppf2
207 WHERE ppf2.person_id = paf.person_id
208 AND ppf2.per_information1 is not null)*/
209
210 CURSOR c_report_parameters(pactid number)
211 IS
212 SELECT start_date,
213 effective_date,
214 pay_eosy_ac_pkg.get_parameter('HI_VER_ID',legislative_parameters),
215 pay_eosy_ac_pkg.get_parameter('EST_ID',legislative_parameters)
216 FROM pay_payroll_actions
217 WHERE payroll_action_id=pactid;
218
219 lockingactid number;
220 lockedactid number;
221 l_asgnid number;
222 l_person_id number;
223 l_gre_id number;
224 l_start_date date;
225 l_end_date date;
226 l_est_id number;
227 l_version_id number;
228 l_location_id number;
229 l_package varchar2(70);
230
231 BEGIN
232
233 --hr_utility.trace_on(null,'fusman');
234
235 l_package := 'pay_eosy_ac_pkg.action_creation';
236 hr_utility.set_location('Enerring.. '||l_package||':stperson:'||stperson,10);
237 hr_utility.trace('pactid = ' || pactid);
238 hr_utility.trace('stperson = ' || stperson);
239 hr_utility.trace('endperson = ' || endperson);
240 hr_utility.trace('chunk = ' || chunk);
241
242 OPEN c_report_parameters(pactid);
243 FETCH c_report_parameters INTO l_end_date,l_start_date,l_version_id,l_est_id;
244 CLOSE c_report_parameters;
245
246 hr_utility.trace('l_start_date = ' || l_start_date);
247 hr_utility.trace('l_end_date = ' || l_end_date);
248 hr_utility.trace('l_version_id = ' || l_version_id);
249 hr_utility.trace('l_est_id = ' || l_est_id);
250
251 hr_utility.set_location(l_package||':stperson:'||stperson,20);
252 OPEN c_actions(pactid,stperson,endperson,l_start_date,
253 l_end_date,l_version_id,l_est_id);
254 LOOP
255 FETCH c_actions INTO lockedactid
256 ,l_asgnid,l_person_id
257 ,l_gre_id,l_location_id;
258
259 IF c_actions%notfound then
260 hr_utility.trace('In the c_actions%notfound in action cursor');
261 hr_utility.set_location(l_package||':stperson:'||stperson,30);
262 EXIT;
263 END IF;
264
265 hr_utility.set_location(l_package||':stperson:'||stperson,40);
266 --Get the assignment_action_id for creating one for each selected asact_id
267
268 SELECT pay_assignment_actions_s.nextval
269 INTO lockingactid
270 FROM dual;
271
272
273 -- insert the action record.
274 hr_utility.set_location(l_package||':stperson:'||stperson,50);
275 hr_utility.trace('asact_id = '||to_char(lockedactid));
276 hr_utility.trace('l_asgnid = '||to_char(l_asgnid));
277 hr_utility.trace('l_person_id = '||to_char(l_person_id));
278
279 hr_nonrun_asact.insact(lockingactid,l_asgnid,pactid,chunk,l_gre_id);
280 UPDATE pay_assignment_actions
281 SET serial_number = l_person_id,
282 source_action_id = l_location_id
283 WHERE assignment_action_id = lockingactid;
284
285 hr_utility.set_location(l_package||':stperson:'||stperson,60);
286 hr_utility.trace('After inserting into pay_assignment_actions, before pay_action_interlock');
287 -- insert an interlock to this action.
288
289 hr_nonrun_asact.insint(lockingactid,lockedactid);
290 hr_utility.trace('loop ends');
291
292 END LOOP;
293 CLOSE c_actions;
294
295 hr_utility.trace('END action_creation');
296 hr_utility.set_location('Leaving.. '||l_package||':stperson:'||stperson,100);
297
298 --hr_utility.trace_off;
299
300 END action_creation;
301
302 ---------------------------------- sort_action ----------------------------------
303 PROCEDURE sort_action(
304 payactid in varchar2, /* payroll action id */
305 sqlstr in out nocopy varchar2, /* string holding the sql statement */
306 len out nocopy number /* length of the sql string */
307 ) is
308 BEGIN
309 sqlstr :=
310 'select paa.rowid
311 from pay_assignment_actions paa /* PYUGEN assignment action */
312 where paa.payroll_action_id = :payactid
313 for update of paa.assignment_id';
314
315 len := length(sqlstr); -- return the length of the string.
316
317 END sort_action;
318
319
320 ----------------------------- get_parameter -------------------------------
321 FUNCTION get_parameter(name in varchar2,
322 parameter_list varchar2)
323 RETURN VARCHAR2
324 IS
325 start_ptr number;
326 end_ptr number;
327 token_val pay_payroll_actions.legislative_parameters%type;
328 par_value pay_payroll_actions.legislative_parameters%type;
329 BEGIN
330
331 token_val := name || '=';
332
333 start_ptr := instr(parameter_list, token_val) + length(token_val);
334 end_ptr := instr(parameter_list, ' ',start_ptr);
335
336 /* if there is no spaces use then length of the string */
337 if end_ptr = 0 then
338 end_ptr := length(parameter_list) + 1;
339 end if;
340
341 /* Did we find the token */
342 if instr(parameter_list, token_val) = 0 then
343 par_value := NULL;
344 else
345 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
346 end if;
347
348 return par_value;
349
350 END get_parameter;
351
352
353 END pay_eosy_ac_pkg;