[Home] [Help]
PACKAGE: APPS.PAY_IE_PAYPATH_TAPE
Source
1 PACKAGE pay_ie_paypath_tape AUTHID CURRENT_USER AS
2 /* $Header: pyiepppk.pkh 120.4 2010/02/03 13:03:33 jvaradra ship $ */
3
4 level_cnt NUMBER;
5 c_credit_type Varchar2(10) :='CREDIT';
6 c_xxxxx_type Varchar2(10) :='XXXXX';
7 g_pathid varchar2(150);
8
9 -- FUNCTION GET_PAYPATHID return varchar2; -- Bug No 3060464
10
11 /* For Bug 9294983 */
12 FUNCTION get_consolidation_set return varchar2;
13
14
15 /*Bug no. 3813140*/
16 CURSOR CSR_PPH_HEADER
17 IS
18 SELECT 'DUMMY=P' , '1' --pay_ie_paypath_tape.get_paypathid pathid
19 FROM dual;
20
21 CURSOR CSR_PPH_HEADER_RECORD
22 IS
23 SELECT /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_PK,
24 paa PAY_ASSIGNMENT_ACTIONS_N50,
25 ppp PAY_PRE_PAYMENTS_PK,
26 popm PAY_ORG_PAYMENT_METHODS_F_PK,
27 pppm PAY_PERSONAL_PAYMENT_METHO_PK,
28 a PER_ASSIGNMENTS_F_PK,
29 p PER_PEOPLE_F_PK ,
30 oea PAY_EXTERNAL_ACCOUNTS_PK,
31 pea PAY_EXTERNAL_ACCOUNTS_PK)
32 USE_NL(ppa,paa,ppp,popm,pppm,a,p,oea,pea,hou,org) */
33 'VOLUME_ID=P' ,pay_magtape_generic.get_parameter_value('PAYPATH_VOLUME')
34 ,'PAYPATH_ID=P' ,pay_ie_archive_detail_pkg.get_paypathid --Bug no. 3813140
35 ,'PROCESSING_DATE=P',to_char(to_date(pay_magtape_generic.get_parameter_value('PROCESSING_DATE'),'YYYY/MM/DD HH24:MI:SS'),'YYDDD')
36 ,'FILE_NUMBER=P' ,pay_magtape_generic.get_parameter_value('FILE_NUMBER')
37 ,'RECEIVER_ID=P' ,org.org_information9
38 ,'SES_DATE=P' ,to_char(to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'),'YYYY/MM/DD HH24:MI:SS'),'YYMMDD')
39 ,'FILE_FORMAT=P' ,nvl(org.org_information11, 'AIB')
40 ,'EXPIRATION_DATE=P',to_char(to_date(pay_magtape_generic.get_parameter_value('PROCESSING_DATE'),'YYYY/MM/DD HH24:MI:SS'),'YYMMDD')
41 FROM
42 pay_payroll_actions ppa
43 , pay_assignment_actions paa
44 , pay_pre_payments ppp
45 , pay_org_payment_methods_f popm
46 , pay_personal_payment_methods_f pppm
47 , per_all_assignments_f a
48 , per_all_people_f p
49 , pay_external_accounts oea
50 , pay_external_accounts pea
51 , hr_organization_units hou
52 , hr_organization_information org
53
54 -- , (select pay_ie_paypath_tape.get_paypathid pathid from dual) paypath -- Bug No 3060464
55 where
56 ppa.payroll_action_id = -- Bug No 3513042
57 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
58 and paa.payroll_action_id = ppa.payroll_action_id
59 and ppp.pre_payment_id = paa.pre_payment_id
60 and oea.external_account_id = popm.external_account_id
61 and popm.org_payment_method_id = ppp.org_payment_method_id
62 and pea.external_account_id = pppm.external_account_id
63 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
64 and paa.assignment_id = a.assignment_id
65 and a.person_id = p.person_id
66 -- Added nvl function for bug fix 3649139
67 and a.payroll_id = nvl(ppa.payroll_id,a.payroll_id ) -- Bug No 3513042
68 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
69 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
70 and ppa.effective_date between a.effective_start_date and a.effective_end_date
71 and ppa.effective_date between p.effective_start_date and p.effective_end_date
72 and org.organization_id (+) = p.business_group_id
73 and org.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
74 -- Added for bug fix 3649139
75 and org.organization_id = hou.organization_id
76 and pppm.business_group_id = hou.business_group_id
77 and popm.business_group_id = hou.business_group_id
78 -- Added for bug fix 5696117
79 and decode (org.org_information8,' ', pay_ie_archive_detail_pkg.get_paypathid, pay_ie_archive_detail_pkg.get_paypathid)
80 in (org.org_information8,'Error',' ')
81 and rownum < 2;
82
83 /* and (org.org_information8 = pay_ie_paypath_tape.g_pathid --Bug no. 3813140
84 or 'Error'=pay_ie_paypath_tape.g_pathid --Bug no. 3813140
85 or ' '=pay_ie_paypath_tape.g_pathid ) --Bug no. 3813140 and 3060464
86 and rownum < 2;*/
87
88
89 CURSOR CSR_PPH_CREDIT_RECORD
90 IS
91 SELECT /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_PK,
92 paa PAY_ASSIGNMENT_ACTIONS_N50,
93 ppp PAY_PRE_PAYMENTS_PK,
94 popm PAY_ORG_PAYMENT_METHODS_F_PK,
95 pppm PAY_PERSONAL_PAYMENT_METHO_PK,
96 a PER_ASSIGNMENTS_F_PK,
97 p PER_PEOPLE_F_PK ,
98 oea PAY_EXTERNAL_ACCOUNTS_PK,
99 pea PAY_EXTERNAL_ACCOUNTS_PK,
100 org HR_ORGANIZATION_INFORMATIO_FK2)
101 USE_NL(ppa,paa,ppp,popm,pppm,a,p,oea,pea,org) */
102 'TYPE=P', c_credit_type
103 ,'EMP_NAME=P' , p.full_name
104 ,'EMPLOYEE_NSC=P', pea.segment1
105 ,'EMPLOYEE_ACC_NO=P', pea.segment4
106 ,'TRANSACTION_CODE=P', '99'
107 ,'EMPLOYER_NSC=P', oea.segment1
108 ,'EMPLOYER_ACC_NO=P', oea.segment4
109 ,'PAY_AMOUNT=P', (ppp.value * 100)
110 ,'USER_NAME=P', oea.segment5
111 ,'USER_REFERNCE=P', org.org_information10
112 ,'ACC_CREDIT_NAME=P', pea.segment5
113 ,'FILE_FORMAT=P', nvl(org.org_information11, 'AIB')
114 from
115 pay_payroll_actions ppa
116 , pay_assignment_actions paa
117 , pay_pre_payments ppp
118 , pay_org_payment_methods_f popm
119 , pay_personal_payment_methods_f pppm
120 , per_all_assignments_f a -- Bug No 3513042
121 , per_all_people_f p
122 , pay_external_accounts oea
123 , pay_external_accounts pea
124 , hr_organization_information org
125 where ppa.payroll_action_id = -- Bug No 3513042
126 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
127 and ppp.pre_payment_id = paa.pre_payment_id
128 and paa.payroll_action_id = ppa.payroll_action_id
129 and oea.external_account_id = popm.external_account_id
130 and popm.org_payment_method_id = ppp.org_payment_method_id
131 and pea.external_account_id = pppm.external_account_id
132 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
133 and paa.assignment_id = a.assignment_id
134 and a.person_id = p.person_id
135 --Added nvl function code for bug fix 3649139
136 and a.payroll_id = nvl(ppa.payroll_id,a.payroll_id ) -- Bug No 3513042
137 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
138 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
139 and ppa.effective_date between a.effective_start_date and a.effective_end_date
140 and ppa.effective_date between p.effective_start_date and p.effective_end_date
141 and org.organization_id (+) = p.business_group_id
142 and org.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
143 -- Added for bug fix 5696117
144 and decode (org.org_information8,' ', pay_ie_archive_detail_pkg.get_paypathid,
145 pay_ie_archive_detail_pkg.get_paypathid) in (org.org_information8,' ')
146
147 /*and (org.org_information8= pay_ie_paypath_tape.g_pathid
148 or ' '=pay_ie_paypath_tape.g_pathid) --Bug No 3086034*/
149 union all
150 SELECT 'TYPE=P', c_xxxxx_type
151 ,'EMP_NAME=P' , 'null'
152 ,'EMPLOYEE_NSC=P', 'null'
153 ,'EMPLOYEE_ACC_NO=P', 'null'
154 ,'TRANSACTION_CODE=P','null'
155 ,'EMPLOYER_NSC=P', 'null'
156 ,'EMPLOYER_ACC_NO=P', 'null'
157 ,'PAY_AMOUNT=P', 0
158 ,'USER_NAME=P', 'null'
159 ,'USER_REFERNCE=P', 'null'
160 ,'ACC_CREDIT_NAME=P', 'null'
161 ,'FILE_FORMAT=P','null'
162 from dual
163 order by 1,2;
164
165
166 CURSOR CSR_PPH_CONTRA_RECORD
167 IS
168 SELECT /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_PK,
169 paa PAY_ASSIGNMENT_ACTIONS_N50,
170 ppp PAY_PRE_PAYMENTS_PK,
171 popm PAY_ORG_PAYMENT_METHODS_F_PK,
172 pppm PAY_PERSONAL_PAYMENT_METHO_PK,
173 a PER_ASSIGNMENTS_F_PK,
174 p PER_PEOPLE_F_PK ,
175 oea PAY_EXTERNAL_ACCOUNTS_PK,
176 pea PAY_EXTERNAL_ACCOUNTS_PK,
177 org HR_ORGANIZATION_INFORMATIO_FK2)
178 USE_NL(ppa,paa,ppp,popm,pppm,a,p,oea,pea,org) */
179 'TYPE=P', 'Z'
180 ,'EMP_NAME=P' , 'null'
181 ,'EMPLOYEE_ACC_NO=P', oea.segment4
182 ,'EMPLOYEE_NSC=P', oea.segment1
183 ,'TRANSACTION_CODE=P','17'
184 ,'PAY_AMOUNT=P', (sum(ppp.value)* 100)
185 ,'USER_NAME=P', oea.segment5
186 ,'USER_REFERNCE=P', org.org_information10
187 ,'EMPLOYER_NSC=P', oea.segment1
188 ,'EMPLOYER_ACC_NO=P', oea.segment4
189 ,'ACC_CREDIT_NAME=P', oea.segment5
190 ,'FILE_FORMAT=P', nvl(org.org_information11, 'AIB')
191 from
192 pay_payroll_actions ppa
193 , pay_assignment_actions paa
194 , pay_pre_payments ppp
195 , pay_org_payment_methods_f popm
196 , pay_personal_payment_methods_f pppm
197 , per_all_assignments_f a -- Bug No 3513042
198 , per_all_people_f p
199 , pay_external_accounts oea
200 , pay_external_accounts pea
201 , hr_organization_information org
202 where ppa.payroll_action_id = -- Bug No 3513042
203 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
204 and ppp.pre_payment_id = paa.pre_payment_id
205 and paa.payroll_action_id = ppa.payroll_action_id
206 and oea.external_account_id = popm.external_account_id
207 and popm.org_payment_method_id = ppp.org_payment_method_id
208 and pea.external_account_id = pppm.external_account_id
209 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
210 and paa.assignment_id = a.assignment_id
211 and a.person_id = p.person_id
212 --Added nvl function code for bug fix 3649139
213 and a.payroll_id = nvl(ppa.payroll_id,a.payroll_id ) -- Bug No 3513042
214 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
215 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
216 and ppa.effective_date between a.effective_start_date and a.effective_end_date
217 and ppa.effective_date between p.effective_start_date and p.effective_end_date
218 and org.organization_id (+) = p.business_group_id
219 and org.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
220 -- Added for bug fix 5696117
221 and decode (org.org_information8,' ', pay_ie_archive_detail_pkg.get_paypathid,
222 pay_ie_archive_detail_pkg.get_paypathid) in (org.org_information8,' ')
223
224 /* and (org.org_information8= pay_ie_paypath_tape.g_pathid
225 or ' '=pay_ie_paypath_tape.g_pathid) --Bug No 3086034*/
226 group by 'TYPE=P', 'Z'
227 ,'EMPLOYEE_ACC_NO=P', oea.segment4
228 ,'EMPLOYEE_NSC=P', oea.segment1
229 ,'USER_NAME=P', oea.segment5
230 ,'USER_REFERNCE=P', org.org_information10
231 ,'FILE_FORMAT=P', nvl(org.org_information11, 'AIB');
232
233
234 /*PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
235 p_token_name IN VARCHAR2,
236 p_token_value OUT NOCOPY VARCHAR2);
237
238 */
239
240 /* Added for Bug 9294983 (Bulk Payment File) */
241
242
243 CURSOR CSR_BPH_HEADER_RECORD
244 IS
245 SELECT /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_PK,
246 paa PAY_ASSIGNMENT_ACTIONS_N50,
247 ppp PAY_PRE_PAYMENTS_PK,
248 popm PAY_ORG_PAYMENT_METHODS_F_PK,
249 pppm PAY_PERSONAL_PAYMENT_METHO_PK,
250 a PER_ASSIGNMENTS_F_PK,
251 p PER_PEOPLE_F_PK ,
252 oea PAY_EXTERNAL_ACCOUNTS_PK,
253 pea PAY_EXTERNAL_ACCOUNTS_PK)
254 USE_NL(ppa,paa,ppp,popm,pppm,a,p,oea,pea,hou,org) */
255 'VOLUME_ID=P' ,pay_magtape_generic.get_parameter_value('PAYPATH_VOLUME')
256 ,'PAYPATH_ID=P' ,pay_ie_archive_detail_pkg.get_paypathid --Bug no. 3813140
257 ,'PROCESSING_DATE=P',to_char(to_date(pay_magtape_generic.get_parameter_value('PROCESSING_DATE'),'YYYY/MM/DD HH24:MI:SS'),'DDMMYYYY')
258 ,'FILE_NUMBER=P' ,pay_magtape_generic.get_parameter_value('FILE_NUMBER')
259 ,'RECEIVER_ID=P' ,org.org_information9
260 ,'SES_DATE=P' ,to_char(to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'),'YYYY/MM/DD HH24:MI:SS'),'YYMMDD')
261 ,'FILE_FORMAT=P' ,nvl(org.org_information11, 'AIB')
262 ,'CONSOLIDATION_SET=P',pay_magtape_generic.get_parameter_value('CONSOLIDATION_SET')
263 FROM
264 pay_payroll_actions ppa
265 , pay_assignment_actions paa
266 , pay_pre_payments ppp
267 , pay_org_payment_methods_f popm
268 , pay_personal_payment_methods_f pppm
269 , per_all_assignments_f a
270 , per_all_people_f p
271 , pay_external_accounts oea
272 , pay_external_accounts pea
273 , hr_organization_units hou
274 , hr_organization_information org
275
276 -- , (select pay_ie_paypath_tape.get_paypathid pathid from dual) paypath -- Bug No 3060464
277 where
278 ppa.payroll_action_id = -- Bug No 3513042
279 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
280 and paa.payroll_action_id = ppa.payroll_action_id
281 and ppp.pre_payment_id = paa.pre_payment_id
282 and oea.external_account_id = popm.external_account_id
283 and popm.org_payment_method_id = ppp.org_payment_method_id
284 and pea.external_account_id = pppm.external_account_id
285 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
286 and paa.assignment_id = a.assignment_id
287 and a.person_id = p.person_id
288 -- Added nvl function for bug fix 3649139
289 and a.payroll_id = nvl(ppa.payroll_id,a.payroll_id ) -- Bug No 3513042
290 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
291 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
292 and ppa.effective_date between a.effective_start_date and a.effective_end_date
293 and ppa.effective_date between p.effective_start_date and p.effective_end_date
294 and org.organization_id (+) = p.business_group_id
295 and org.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
296 -- Added for bug fix 3649139
297 and org.organization_id = hou.organization_id
298 and pppm.business_group_id = hou.business_group_id
299 and popm.business_group_id = hou.business_group_id
300 -- Added for bug fix 5696117
301 and decode (org.org_information8,' ', pay_ie_archive_detail_pkg.get_paypathid, pay_ie_archive_detail_pkg.get_paypathid)
302 in (org.org_information8,'Error',' ')
303 and rownum < 2;
304
305
306
307 CURSOR CSR_BPH_CREDIT_RECORD
308 IS
309 SELECT /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_PK,
310 paa PAY_ASSIGNMENT_ACTIONS_N50,
311 ppp PAY_PRE_PAYMENTS_PK,
312 popm PAY_ORG_PAYMENT_METHODS_F_PK,
313 pppm PAY_PERSONAL_PAYMENT_METHO_PK,
314 a PER_ASSIGNMENTS_F_PK,
315 p PER_PEOPLE_F_PK ,
316 oea PAY_EXTERNAL_ACCOUNTS_PK,
317 pea PAY_EXTERNAL_ACCOUNTS_PK,
318 org HR_ORGANIZATION_INFORMATIO_FK2)
319 USE_NL(ppa,paa,ppp,popm,pppm,a,p,oea,pea,org) */
320 'TYPE=P', c_credit_type
321 ,'EMP_NAME=P' , replace(p.full_name,',','')
322 ,'EMP_NUMBER=P', p.employee_number
323 ,'EMPLOYEE_NSC=P', pea.segment1
324 ,'EMPLOYEE_ACC_NO=P', pea.segment4
325 --,'TRANSACTION_CODE=P', '99' For Bug 9294983
326 ,'CONSOLIDATION_SET_NAME=P', PAY_IE_PAYPATH_TAPE.get_consolidation_set
327 ,'EMPLOYER_NSC=P', oea.segment1
328 ,'EMPLOYER_ACC_NO=P', oea.segment4
329 ,'PAY_AMOUNT=P', ppp.value
330 ,'USER_NAME=P', oea.segment5
331 ,'USER_REFERNCE=P', org.org_information10
332 ,'ACC_CREDIT_NAME=P', pea.segment5
333 ,'FILE_FORMAT=P', nvl(org.org_information11, 'UIB')
334 from
335 pay_payroll_actions ppa
336 , pay_assignment_actions paa
337 , pay_pre_payments ppp
338 , pay_org_payment_methods_f popm
339 , pay_personal_payment_methods_f pppm
340 , per_all_assignments_f a -- Bug No 3513042
341 , per_all_people_f p
342 , pay_external_accounts oea
343 , pay_external_accounts pea
344 , hr_organization_information org
345 where ppa.payroll_action_id = -- Bug No 3513042
346 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
347 and ppp.pre_payment_id = paa.pre_payment_id
348 and paa.payroll_action_id = ppa.payroll_action_id
349 and oea.external_account_id = popm.external_account_id
350 and popm.org_payment_method_id = ppp.org_payment_method_id
351 and pea.external_account_id = pppm.external_account_id
355 --Added nvl function code for bug fix 3649139
352 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
353 and paa.assignment_id = a.assignment_id
354 and a.person_id = p.person_id
356 and a.payroll_id = nvl(ppa.payroll_id,a.payroll_id ) -- Bug No 3513042
357 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
358 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
359 and ppa.effective_date between a.effective_start_date and a.effective_end_date
360 and ppa.effective_date between p.effective_start_date and p.effective_end_date
361 and org.organization_id (+) = p.business_group_id
362 and org.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
363 -- Added for bug fix 5696117
364 and decode (org.org_information8,' ', pay_ie_archive_detail_pkg.get_paypathid,
365 pay_ie_archive_detail_pkg.get_paypathid) in (org.org_information8,' ')
366
367 /*and (org.org_information8= pay_ie_paypath_tape.g_pathid
368 or ' '=pay_ie_paypath_tape.g_pathid) --Bug No 3086034*/
369 union all
370 SELECT 'TYPE=P', c_xxxxx_type
371 ,'EMP_NAME=P' , 'null'
372 ,'EMP_NUMBER=P', 'null'
373 ,'EMPLOYEE_NSC=P', 'null'
374 ,'EMPLOYEE_ACC_NO=P', 'null'
375 --,'TRANSACTION_CODE=P','null'
376 ,'CONSOLIDATION_SET_NAME', 'null'
377 ,'EMPLOYER_NSC=P', 'null'
378 ,'EMPLOYER_ACC_NO=P', 'null'
379 ,'PAY_AMOUNT=P', 0
380 ,'USER_NAME=P', 'null'
381 ,'USER_REFERNCE=P', 'null'
382 ,'ACC_CREDIT_NAME=P', 'null'
383 ,'FILE_FORMAT=P','null'
384 from dual
385 order by 1,2;
386
387 CURSOR CSR_BPH_CONTRA_RECORD
388 IS
389 SELECT /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_PK,
390 paa PAY_ASSIGNMENT_ACTIONS_N50,
391 ppp PAY_PRE_PAYMENTS_PK,
392 popm PAY_ORG_PAYMENT_METHODS_F_PK,
393 pppm PAY_PERSONAL_PAYMENT_METHO_PK,
394 a PER_ASSIGNMENTS_F_PK,
395 p PER_PEOPLE_F_PK ,
396 oea PAY_EXTERNAL_ACCOUNTS_PK,
397 pea PAY_EXTERNAL_ACCOUNTS_PK,
398 org HR_ORGANIZATION_INFORMATIO_FK2)
399 USE_NL(ppa,paa,ppp,popm,pppm,a,p,oea,pea,org) */
400 'TYPE=P', 'Z'
401 ,'EMP_NAME=P' , 'null'
402 ,'EMP_NUMBER=P' , 'null'
403 ,'EMPLOYEE_ACC_NO=P', oea.segment4
404 ,'EMPLOYEE_NSC=P', oea.segment1
405 --,'TRANSACTION_CODE=P','17' For Bug 9294983
406 ,'CONSOLIDATION_SET_NAME=P', PAY_IE_PAYPATH_TAPE.get_consolidation_set
407 ,'PAY_AMOUNT=P', sum(ppp.value)
408 ,'USER_NAME=P', oea.segment5
409 ,'USER_REFERNCE=P', org.org_information10
410 ,'EMPLOYER_NSC=P', oea.segment1
411 ,'EMPLOYER_ACC_NO=P', oea.segment4
412 ,'ACC_CREDIT_NAME=P', oea.segment5
413 ,'FILE_FORMAT=P', nvl(org.org_information11, 'UIB')
414 from
415 pay_payroll_actions ppa
416 , pay_assignment_actions paa
417 , pay_pre_payments ppp
418 , pay_org_payment_methods_f popm
419 , pay_personal_payment_methods_f pppm
420 , per_all_assignments_f a -- Bug No 3513042
421 , per_all_people_f p
422 , pay_external_accounts oea
423 , pay_external_accounts pea
424 , hr_organization_information org
425 where ppa.payroll_action_id = -- Bug No 3513042
426 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
427 and ppp.pre_payment_id = paa.pre_payment_id
428 and paa.payroll_action_id = ppa.payroll_action_id
429 and oea.external_account_id = popm.external_account_id
430 and popm.org_payment_method_id = ppp.org_payment_method_id
431 and pea.external_account_id = pppm.external_account_id
432 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
433 and paa.assignment_id = a.assignment_id
434 and a.person_id = p.person_id
435 --Added nvl function code for bug fix 3649139
436 and a.payroll_id = nvl(ppa.payroll_id,a.payroll_id ) -- Bug No 3513042
437 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
438 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
439 and ppa.effective_date between a.effective_start_date and a.effective_end_date
440 and ppa.effective_date between p.effective_start_date and p.effective_end_date
441 and org.organization_id (+) = p.business_group_id
442 and org.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
443 -- Added for bug fix 5696117
444 and decode (org.org_information8,' ', pay_ie_archive_detail_pkg.get_paypathid,
445 pay_ie_archive_detail_pkg.get_paypathid) in (org.org_information8,' ')
446
447 /* and (org.org_information8= pay_ie_paypath_tape.g_pathid
448 or ' '=pay_ie_paypath_tape.g_pathid) --Bug No 3086034*/
449 group by 'TYPE=P', 'Z'
450 ,'EMPLOYEE_ACC_NO=P', oea.segment4
451 ,'EMPLOYEE_NSC=P', oea.segment1
452 ,'USER_NAME=P', oea.segment5
453 ,'USER_REFERNCE=P', org.org_information10
454 ,'FILE_FORMAT=P', nvl(org.org_information11, 'UIB');
455
456
457 PROCEDURE range_code(
458 p_payroll_action_id IN NUMBER,
459 p_sqlstr OUT NOCOPY VARCHAR2);
460 --
461 PROCEDURE assignment_action_code(
462 p_payroll_action_id IN NUMBER,
463 p_start_person_id IN NUMBER,
464 p_end_person_id IN NUMBER,
465 p_chunk_number IN NUMBER);
466
467
468 --Cash Management Reconciliation function
469 FUNCTION f_get_paypath_recon_data (p_effective_date IN DATE,
470 p_identifier_name IN VARCHAR2,
471 p_payroll_action_id IN NUMBER,
472 p_payment_type_id IN NUMBER,
473 p_org_payment_method_id IN NUMBER,
474 p_personal_payment_method_id IN NUMBER,
475 p_assignment_action_id IN NUMBER,
476 p_pre_payment_id IN NUMBER,
477 p_delimiter_string IN VARCHAR2)
478 RETURN VARCHAR2;
479
480 END pay_ie_paypath_tape;