[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_PAYPATH_TAPE
Source
1 package body pay_ie_paypath_tape as
2 /* $Header: pyiepppk.pkb 120.4 2011/01/04 13:36:46 abraghun ship $ */
3 --
4 -- Constants
5 --
6 l_package varchar2(31) := 'pay_ie_paypath_tape.';
7 --
8 -- Global Variables
9 --
10 --------------------------------------------------------------------------------+
11 --
12 --------------------------------------------------------------------------------+
13 /*
14 PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
15 p_token_name IN VARCHAR2,
16 p_token_value OUT NOCOPY VARCHAR2) IS
17
18 CURSOR csr_parameter_info(p_pact_id NUMBER,
19 p_token CHAR) IS
20 SELECT SUBSTR(legislative_parameters,
21 INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
22 INSTR(legislative_parameters,' ',
23 INSTR(legislative_parameters,p_token))
24 - (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
25 business_group_id
26 FROM pay_payroll_actions
27 WHERE payroll_action_id = p_pact_id;
28
29 l_business_group_id VARCHAR2(20);
30 l_token_value VARCHAR2(50);
31
32
33 BEGIN
34
35
36
37
38 hr_utility.set_location('p_token_name = ' || p_token_name,20);
39
40 OPEN csr_parameter_info(p_payroll_action_id,p_token_name);
41 FETCH csr_parameter_info INTO l_token_value,l_business_group_id;
42 CLOSE csr_parameter_info;
43
44 p_token_value := trim(l_token_value);
45
46
47 hr_utility.set_location('l_token_value = ' || l_token_value,20);
48 hr_utility.set_location('Leaving ' || 'get_parameters',30);
49
50 END get_parameters;*/
51
52 --------------------------------------------------------------------------------+
53 --Function get_paypathid is used to fetch Paypath ids for a Consolidation Set
54 --------------------------------------------------------------------------------+
55 -- Bug No 3060464 Start
56 /*FUNCTION get_paypathid return varchar2 as
57
58 --Cursor to fetch paypath ids for all payrolls within a consolidation set
59 CURSOR CSR_PAYROLLS
60 IS
61 SELECT count(distinct org_information8) paycount,
62 org_information8
63 FROM pay_all_payrolls_f papf
64 , hr_organization_information org
65 , hr_soft_coding_keyflex sck
66 WHERE
67 papf.consolidation_set_id = pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID')
68 and to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'),'YYYY/MM/DD HH24:MI:SS')
69 between papf.effective_start_date and papf.effective_end_date
70 and org.org_information_context = 'IE_PAYPATH_INFORMATION'
71 and papf.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
72 and org.ORG_INFORMATION_ID = to_number(sck.segment2)
73 and org.org_information8 is not null
74 group by org_information8;
75
76
77 --Cursor to fetch first paypath id defined at BG Level
78 CURSOR CSR_BG_PAYPATH
79 IS
80 SELECT org.org_information8
81 FROM hr_organization_information org
82 , pay_payroll_actions ppa
83 WHERE
84 ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
85 and org.organization_id = ppa.business_group_id
86 and org.org_information_context = 'IE_PAYPATH_INFORMATION'
87 and rownum=1;
88
89 --Cursor to fetch paypath id for a specified payroll parameter
90 CURSOR CSR_PAYROLL_PAYPATH
91 IS
92 SELECT org.org_information8
93 FROM hr_organization_information org
94 WHERE
95 org.org_information_context = 'IE_PAYPATH_INFORMATION'
96 and org.ORG_INFORMATION_ID =
97 (SELECT to_number(segment2)
98 FROM
99 hr_soft_coding_keyflex sck
100 , pay_all_payrolls_f papf
101 , pay_payroll_actions ppa
102 WHERE
103 papf.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
104 and papf.payroll_id = pay_magtape_generic.get_parameter_value('PAYROLL_ID')
105 and papf.consolidation_set_id = pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID')
106 and ppa.effective_date between papf.effective_start_date and papf.effective_end_date
107 and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
108 and papf.payroll_id=ppa.payroll_id);
109
110 l_paypath_ids csr_payrolls%rowtype;
111 l_bg_pathid csr_bg_paypath%rowtype;
112 l_payroll_pathid csr_payroll_paypath%rowtype;
113 cnt number :=0;
114 e_submit_error exception;
115 l_paypathid varchar2(150);
116
117 BEGIN
118
119 --Only consolidation set specified as parameter in IE PayPath Process
120 IF pay_magtape_generic.get_parameter_value('PAYROLL_ID') is null then
121
122 BEGIN
123
124 OPEN csr_payrolls;
125 LOOP
126 FETCH csr_payrolls into l_paypath_ids;
127 EXIT when csr_payrolls%NOTFOUND;
128
129 l_paypathid:=l_paypath_ids.org_information8;
130 pay_ie_paypath_tape.g_pathid:=l_paypathid;
131 cnt:=cnt+1;
132
133 --PayPath process errors when a consolidation set has multiple payrolls, which in turn have multiple Paypath ID's
134 if cnt>=2 then
135 raise e_submit_error;
136 end if;
137 END LOOP;
138
139 --If no Paypath ids are specified at payroll level,Paypath id defined at BG level is picked up.
140 if cnt=0 then
141
142 OPEN CSR_BG_PAYPATH;
143 FETCH CSR_BG_PAYPATH into l_bg_pathid;
144
145 --If no paypath ids defined at BG level and payroll level
146 IF CSR_BG_PAYPATH%NOTFOUND THEN
147
148 l_paypathid:= ' ';
149 pay_ie_paypath_tape.g_pathid:=l_paypathid;
150 return l_paypathid;
151 END IF;
152 CLOSE CSR_BG_PAYPATH;
153
154 pay_ie_paypath_tape.g_pathid:=l_bg_pathid.org_information8;
155 return l_bg_pathid.org_information8;
156 end if;
157 --If payrolls in the consolidation set all have the same paypath id, then that paypath id is picked up.
158 return l_paypathid;
159 EXCEPTION when e_submit_error then
160
161 l_paypathid:='Error';
162 pay_ie_paypath_tape.g_pathid:=l_paypathid;
163 return l_paypathid;
164 END;
165 ELSE
166 --Payroll name as well as the consolidation set specified as parameter then ,select paypath id specified at the payroll level
167
168 OPEN CSR_PAYROLL_PAYPATH;
169 FETCH CSR_PAYROLL_PAYPATH into l_payroll_pathid;
170
171 --Bug No 3086034 Start
172 IF CSR_PAYROLL_PAYPATH%NOTFOUND THEN
173
174 --Payroll name specified as a parameter but ,no paypath id defined for that payroll,hence it picks up
175 --paypath id defined at BG Level
176 OPEN CSR_BG_PAYPATH;
177 FETCH CSR_BG_PAYPATH into l_bg_pathid;
178 CLOSE CSR_BG_PAYPATH;
179 pay_ie_paypath_tape.g_pathid:=l_bg_pathid.org_information8;
180 return l_bg_pathid.org_information8;
181 END IF;
182 --Bug No 3086034 End
183 CLOSE CSR_PAYROLL_PAYPATH;
184
185 pay_ie_paypath_tape.g_pathid:=l_payroll_pathid.org_information8;
186 return l_payroll_pathid.org_information8;
187 END IF;
188 END get_paypathid;
189 -- Bug No 3060464 End
190 */
191
192 /* For Bug 9294983 */
193 FUNCTION get_consolidation_set return varchar2 as
194
195 CURSOR csr_consolidation_name
196 IS
197 SELECT consolidation_set_name
198 FROM PAY_CONSOLIDATION_SETS
199 WHERE consolidation_set_id = pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET_ID');
200
201 l_consolidation_name varchar2(200);
202
203 BEGIN
204
205 OPEN csr_consolidation_name;
206 FETCH csr_consolidation_name
207 INTO l_consolidation_name;
208 CLOSE csr_consolidation_name;
209
210 return l_consolidation_name;
211
212 END;
213
214
215 --------------------------------------------------------------------------------+
216 -- Range cursor returns the ids of the assignments to be archived
217 --------------------------------------------------------------------------------+
218 PROCEDURE range_code(
219 p_payroll_action_id IN NUMBER,
220 p_sqlstr OUT NOCOPY VARCHAR2)
221 IS
222 l_proc_name VARCHAR2(100) := l_package || 'range_code';
223 BEGIN
224 hr_utility.set_location(l_proc_name, 10);
225 -- Changed the cursor to reduce the cost (5042843)
226 p_sqlstr := 'SELECT distinct asg.person_id
227 FROM per_periods_of_service pos,
228 per_assignments_f asg,
229 pay_payroll_actions ppa
230 WHERE ppa.payroll_action_id = :payroll_action_id
231 AND pos.person_id = asg.person_id
232 AND pos.period_of_service_id = asg.period_of_service_id
233 AND pos.business_group_id = ppa.business_group_id
234 AND asg.business_group_id = ppa.business_group_id
235 ORDER BY asg.person_id';
236 hr_utility.set_location(l_proc_name, 20);
237 END range_code;
238
239 --------------------------------------------------------------------------------+
240 -- Creates assignment action id for all the valid person id's in
241 -- the range selected by the Range code.
242 --------------------------------------------------------------------------------+
243 PROCEDURE assignment_action_code(
244 p_payroll_action_id IN NUMBER,
245 p_start_person_id IN NUMBER,
246 p_end_person_id IN NUMBER,
247 p_chunk_number IN NUMBER)
248 IS
249 l_proc_name VARCHAR2(100) := l_package || 'assignment_action_code';
250
251 -- Bug 3221451: Added hint ORDERED for the optimizer
252
253 CURSOR csr_asg(p_payroll_action_id NUMBER,
254 p_start_person_id NUMBER,
255 p_end_person_id NUMBER,
256 p_payroll_id NUMBER,
257 p_consolidation_id NUMBER,
258 p_assignment_set_id NUMBER,
259 p_person_id NUMBER) IS
260 SELECT act.assignment_action_id,
261 act.assignment_id,
262 ppp.pre_payment_id
263 FROM pay_assignment_actions act,
264 per_all_assignments_f asg,
265 pay_payroll_actions pa2,
266 pay_payroll_actions pa1,
267 pay_pre_payments ppp,
268 pay_org_payment_methods_f OPM,
269 pay_payment_types PPT,
270 per_all_people_f pap
271 WHERE pa1.payroll_action_id = p_payroll_action_id
272 AND pa2.consolidation_set_id = p_consolidation_id
273 AND pa2.payroll_id = NVL(p_payroll_id,pa2.payroll_id)
274 AND pa2.effective_date <= pa1.effective_date
275 AND pa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
276 AND act.payroll_action_id = pa2.payroll_action_id
277 AND act.action_status IN ('C','S') --10225372
278 AND asg.assignment_id = act.assignment_id
279 AND pa1.business_group_id = asg.business_group_id
280 AND pa1.effective_date between asg.effective_start_date and asg.effective_end_date
281 AND pa1.effective_date between pap.effective_start_date and pap.effective_end_date
282 AND pap.person_id = asg.person_id
283 AND pap.person_id between p_start_person_id and p_end_person_id
284 AND ppp.assignment_action_id = act.assignment_action_id
285 AND ppp.org_payment_method_id = opm.org_payment_method_id
286 AND opm.payment_type_id = ppt.payment_type_id
287 AND ppt.territory_code = 'IE'
288 AND ppt.payment_type_name = 'PayPath'
289 AND pap.person_id = NVL(p_person_id,pap.person_id)
290 AND (p_assignment_set_id IS NULL
291 OR EXISTS ( SELECT ''
292 FROM hr_assignment_set_amendments hr_asg
293 WHERE hr_asg.assignment_set_id = p_assignment_set_id
294 AND hr_asg.assignment_id = asg.assignment_id
295 ))
296 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
297 FROM pay_action_interlocks pai1,
298 pay_assignment_actions act2,
299 pay_payroll_actions appa
300 WHERE pai1.locked_action_id = act.assignment_action_id
301 AND act2.assignment_action_id = pai1.locking_action_id
302 AND act2.payroll_action_id = appa.payroll_action_id
303 AND appa.action_type = 'X'
304 AND appa.report_type = 'PayPath');
305
306
307 l_payroll_id VARCHAR2(15):=NULL;
308 l_consolidation_set VARCHAR2(15):=NULL;
309 l_locking_action_id VARCHAR2(15):=NULL;
310 l_assignment_set_id VARCHAR2(15):=NULL;
311 l_person_id VARCHAR2(15):=NULL;
312
313 BEGIN
314
315 --hr_utility.trace_on(NULL,'VV');
316 hr_utility.set_location(l_proc_name, 10);
317
318 pay_ie_archive_detail_pkg.get_parameters (
319 p_payroll_action_id => p_payroll_action_id
320 , p_token_name => 'PAYROLL_ID'
321 , p_token_value => l_payroll_id);
322
323 pay_ie_archive_detail_pkg.get_parameters (
324 p_payroll_action_id => p_payroll_action_id
325 , p_token_name => 'CONSOLIDATION_SET_ID'
326 , p_token_value => l_consolidation_set);
327
328 pay_ie_archive_detail_pkg.get_parameters (
329 p_payroll_action_id => p_payroll_action_id
330 , p_token_name => 'ASSIGNMENT_SET_ID'
331 , p_token_value => l_assignment_set_id);
332
333 pay_ie_archive_detail_pkg.get_parameters (
334 p_payroll_action_id => p_payroll_action_id
335 , p_token_name => 'PERSON_ID'
336 , p_token_value => l_person_id);
337
338
339 hr_utility.set_location(l_proc_name, 20);
340
341 FOR rec_asg IN csr_asg(p_payroll_action_id
342 ,p_start_person_id
343 ,p_end_person_id
344 ,l_payroll_id
345 ,l_consolidation_set
346 ,l_assignment_set_id
347 ,l_person_id) LOOP
348
349 SELECT pay_assignment_actions_s.nextval
350 INTO l_locking_action_id
351 FROM dual;
352
353
354 hr_nonrun_asact.insact(lockingactid => l_locking_action_id,
355 assignid => rec_asg.assignment_id,
356 pactid => p_payroll_action_id,
357 chunk => p_chunk_number,
358 greid => NULL,
359 prepayid => rec_asg.pre_payment_id,
360 status => 'U');
361
362
363 --
364 -- insert the lock on the run action.
365 --
366
367 hr_nonrun_asact.insint(l_locking_action_id
368 , rec_asg.assignment_action_id);
369 --
370
371 END LOOP;
372 hr_utility.set_location(l_proc_name, 40);
373
374 EXCEPTION
375 WHEN OTHERS THEN
376 hr_utility.set_location('Error in assignment action code ',100);
377 RAISE;
378 END assignment_action_code;
379
380
381 --Cash Management Reconciliation function
382 FUNCTION f_get_paypath_recon_data (p_effective_date IN DATE,
383 p_identifier_name IN VARCHAR2,
384 p_payroll_action_id IN NUMBER,
385 p_payment_type_id IN NUMBER,
386 p_org_payment_method_id IN NUMBER,
387 p_personal_payment_method_id IN NUMBER,
388 p_assignment_action_id IN NUMBER,
389 p_pre_payment_id IN NUMBER,
390 p_delimiter_string IN VARCHAR2)
391 RETURN VARCHAR2
392 IS
393
394 CURSOR c_get_bus_grp
395 IS
396 Select business_group_id
397 From pay_payroll_actions
398 Where payroll_action_id = p_payroll_action_id;
399
400 CURSOR c_get_trx_date
401 IS
402 Select overriding_dd_date
403 From pay_payroll_actions
404 Where payroll_action_id = p_payroll_action_id;
405
406 CURSOR c_get_conc_ident
407 IS
408 Select ext.segment1, --Sort Code
409 ext.segment4 --Acc Num
410 From pay_external_accounts ext,
411 pay_org_payment_methods_f org
412 Where org.org_payment_method_id = p_org_payment_method_id
413 and p_effective_date between org.effective_start_date and org.effective_end_date
414 and org.external_account_id = ext.external_account_id;
415
416 l_business_grp_id NUMBER;
417 l_usr_fnc_name VARCHAR2(5000):= NULL;
418 l_return_value VARCHAR2(80) := NULL;
419 l_trx_date Date;
420 l_sort_code VARCHAR2(30);
421 l_acc_num VARCHAR2(30);
422
423 BEGIN
424
425 OPEN c_get_bus_grp;
426 FETCH c_get_bus_grp INTO l_business_grp_id;
427 CLOSE c_get_bus_grp;
428
429 Select hruserdt.get_table_value(l_business_grp_id,
430 'IE_EFT_RECONC_FUNC',
431 'RECONCILIATION',
432 'FUNCTION NAME',
433 p_effective_date)
434 Into l_usr_fnc_name
435 From dual;
436
437 IF l_usr_fnc_name IS NOT NULL
438 THEN
439 EXECUTE IMMEDIATE 'select '||l_usr_fnc_name||'(:1,:2,:3,:4,:5,:6,:7,:8,:9) from dual'
440 INTO l_return_value
441 USING p_effective_date ,
442 p_identifier_name,
443 p_payroll_action_id,
444 p_payment_type_id,
445 p_org_payment_method_id,
446 p_personal_payment_method_id,
447 p_assignment_action_id,
448 p_pre_payment_id,
449 p_delimiter_string ;
450 ELSE
451 IF UPPER(p_identifier_name) = 'TRANSACTION_DATE'
452 THEN
453 OPEN c_get_trx_date;
454 FETCH c_get_trx_date INTO l_trx_date;
455 CLOSE c_get_trx_date;
456
457 l_return_value := to_char(l_trx_date, 'yyyy/mm/dd');
458
459 ELSIF UPPER(p_identifier_name) = 'TRANSACTION_GROUP'
460 THEN
461 l_return_value := p_payroll_action_id;
462
463 ELSIF UPPER(p_identifier_name) = 'CONCATENATED_IDENTIFIERS'
464 THEN
465 OPEN c_get_conc_ident;
466 FETCH c_get_conc_ident INTO l_sort_code,l_acc_num;
467 CLOSE c_get_conc_ident;
468
469 l_return_value := 'PAYPATH'||p_delimiter_string||l_sort_code||p_delimiter_string||l_acc_num;
470
471 END IF;
472 END IF;
473
474 RETURN l_return_value;
475
476 END f_get_paypath_recon_data;
477
478
479 END pay_ie_paypath_tape;