1 package body pay_ie_paypath_tape as
2 /* $Header: pyiepppk.pkb 120.1.12000000.1 2007/01/17 20:54:36 appldev noship $ */
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 -- Range cursor returns the ids of the assignments to be archived
193 --------------------------------------------------------------------------------+
194 PROCEDURE range_code(
195 p_payroll_action_id IN NUMBER,
196 p_sqlstr OUT NOCOPY VARCHAR2)
197 IS
198 l_proc_name VARCHAR2(100) := l_package || 'range_code';
199 BEGIN
200 hr_utility.set_location(l_proc_name, 10);
201 -- Changed the cursor to reduce the cost (5042843)
202 p_sqlstr := 'SELECT distinct asg.person_id
203 FROM per_periods_of_service pos,
204 per_assignments_f asg,
205 pay_payroll_actions ppa
206 WHERE ppa.payroll_action_id = :payroll_action_id
207 AND pos.person_id = asg.person_id
208 AND pos.period_of_service_id = asg.period_of_service_id
209 AND pos.business_group_id = ppa.business_group_id
210 AND asg.business_group_id = ppa.business_group_id
211 ORDER BY asg.person_id';
212 hr_utility.set_location(l_proc_name, 20);
213 END range_code;
214
215 --------------------------------------------------------------------------------+
216 -- Creates assignment action id for all the valid person id's in
217 -- the range selected by the Range code.
218 --------------------------------------------------------------------------------+
219 PROCEDURE assignment_action_code(
220 p_payroll_action_id IN NUMBER,
221 p_start_person_id IN NUMBER,
222 p_end_person_id IN NUMBER,
223 p_chunk_number IN NUMBER)
224 IS
225 l_proc_name VARCHAR2(100) := l_package || 'assignment_action_code';
226
227 -- Bug 3221451: Added hint ORDERED for the optimizer
228
229 CURSOR csr_asg(p_payroll_action_id NUMBER,
230 p_start_person_id NUMBER,
231 p_end_person_id NUMBER,
232 p_payroll_id NUMBER,
233 p_consolidation_id NUMBER,
234 p_assignment_set_id NUMBER,
235 p_person_id NUMBER) IS
236 SELECT act.assignment_action_id,
237 act.assignment_id,
238 ppp.pre_payment_id
239 FROM pay_assignment_actions act,
240 per_all_assignments_f asg,
241 pay_payroll_actions pa2,
242 pay_payroll_actions pa1,
243 pay_pre_payments ppp,
244 pay_org_payment_methods_f OPM,
245 pay_payment_types PPT,
246 per_all_people_f pap
247 WHERE pa1.payroll_action_id = p_payroll_action_id
248 AND pa2.consolidation_set_id = p_consolidation_id
249 AND pa2.payroll_id = NVL(p_payroll_id,pa2.payroll_id)
250 AND pa2.effective_date <= pa1.effective_date
251 AND pa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
252 AND act.payroll_action_id = pa2.payroll_action_id
253 AND act.action_status = 'C'
254 AND asg.assignment_id = act.assignment_id
255 AND pa1.business_group_id = asg.business_group_id
256 AND pa1.effective_date between asg.effective_start_date and asg.effective_end_date
257 AND pa1.effective_date between pap.effective_start_date and pap.effective_end_date
258 AND pap.person_id = asg.person_id
259 AND pap.person_id between p_start_person_id and p_end_person_id
260 AND ppp.assignment_action_id = act.assignment_action_id
261 AND ppp.org_payment_method_id = opm.org_payment_method_id
262 AND opm.payment_type_id = ppt.payment_type_id
263 AND ppt.territory_code = 'IE'
264 AND ppt.payment_type_name = 'PayPath'
265 AND pap.person_id = NVL(p_person_id,pap.person_id)
266 AND (p_assignment_set_id IS NULL
267 OR EXISTS ( SELECT ''
268 FROM hr_assignment_set_amendments hr_asg
269 WHERE hr_asg.assignment_set_id = p_assignment_set_id
270 AND hr_asg.assignment_id = asg.assignment_id
271 ))
272 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
273 FROM pay_action_interlocks pai1,
274 pay_assignment_actions act2,
275 pay_payroll_actions appa
276 WHERE pai1.locked_action_id = act.assignment_action_id
277 AND act2.assignment_action_id = pai1.locking_action_id
278 AND act2.payroll_action_id = appa.payroll_action_id
279 AND appa.action_type = 'X'
280 AND appa.report_type = 'PayPath');
281
282
283 l_payroll_id VARCHAR2(15):=NULL;
284 l_consolidation_set VARCHAR2(15):=NULL;
285 l_locking_action_id VARCHAR2(15):=NULL;
286 l_assignment_set_id VARCHAR2(15):=NULL;
287 l_person_id VARCHAR2(15):=NULL;
288
289 BEGIN
290
291 --hr_utility.trace_on(NULL,'VV');
292 hr_utility.set_location(l_proc_name, 10);
293
294 pay_ie_archive_detail_pkg.get_parameters (
295 p_payroll_action_id => p_payroll_action_id
296 , p_token_name => 'PAYROLL_ID'
297 , p_token_value => l_payroll_id);
298
299 pay_ie_archive_detail_pkg.get_parameters (
300 p_payroll_action_id => p_payroll_action_id
301 , p_token_name => 'CONSOLIDATION_SET_ID'
302 , p_token_value => l_consolidation_set);
303
304 pay_ie_archive_detail_pkg.get_parameters (
305 p_payroll_action_id => p_payroll_action_id
306 , p_token_name => 'ASSIGNMENT_SET_ID'
307 , p_token_value => l_assignment_set_id);
308
309 pay_ie_archive_detail_pkg.get_parameters (
310 p_payroll_action_id => p_payroll_action_id
311 , p_token_name => 'PERSON_ID'
312 , p_token_value => l_person_id);
313
314
315 hr_utility.set_location(l_proc_name, 20);
316
317 FOR rec_asg IN csr_asg(p_payroll_action_id
318 ,p_start_person_id
319 ,p_end_person_id
320 ,l_payroll_id
321 ,l_consolidation_set
322 ,l_assignment_set_id
323 ,l_person_id) LOOP
324
325 SELECT pay_assignment_actions_s.nextval
326 INTO l_locking_action_id
327 FROM dual;
328
329
330 hr_nonrun_asact.insact(lockingactid => l_locking_action_id,
331 assignid => rec_asg.assignment_id,
332 pactid => p_payroll_action_id,
333 chunk => p_chunk_number,
334 greid => NULL,
335 prepayid => rec_asg.pre_payment_id,
336 status => 'U');
337
338
339 --
340 -- insert the lock on the run action.
341 --
342
343 hr_nonrun_asact.insint(l_locking_action_id
344 , rec_asg.assignment_action_id);
345 --
346
347 END LOOP;
348 hr_utility.set_location(l_proc_name, 40);
349
350 EXCEPTION
351 WHEN OTHERS THEN
352 hr_utility.set_location('Error in assignment action code ',100);
353 RAISE;
354 END assignment_action_code;
355
356 END pay_ie_paypath_tape;
[Home] [Help]