[Home] [Help]
PACKAGE: APPS.PAY_GB_BACS_TAPE
Source
1 PACKAGE pay_gb_bacs_tape AUTHID CURRENT_USER AS
2 /* $Header: pytapbac.pkh 120.1.12010000.3 2009/07/07 14:27:04 namgoyal ship $ */
3 /*
4 * ***************************************************************************
5
6 Copyright (c) Oracle Corporation (UK) Ltd 1993.
7 All Rights Reserved.
8
9 PRODUCT
10 Oracle*Payroll
11
12 NAME
13
14
15 DESCRIPTION
16 Magnetic tape format procedure.
17
18 1.0 Overview
19
20 A PL/SQL package will be written for each type of magnetic tape. The package
21 will include all cursors and procedures required for the particular magnetic
22 tape format. A stored procedure provides the top level of control flow for
23 the magnetic tape file generation. This may call other procedures dependant
24 on the state of the cursors and the input parameters.
25
26 The stored procedure will be called before each execution of a
27 formula. Parameters returned as results of the previous formula execution
28 will be passed to the procedure. The procedure must handle all context
29 cursors needed and may also set parameters required by the formula.
30
31 Using NACHA as an example, for the file header record formula, a call
32 to a cursor which fetches legal_company_id must be performed.
33
34 The interface between the 'C' process and the stored procedure will make
35 extensive use of PL/SQL tables. PL/SQL tables are single column tables which
36 are accessed by an integer index value. Items in the tables will use indexes
37 begining with 1 and increasing contiguously to the number of elements. The
38 index number will be used to match items in the name and value tables.
39
40 The first element in the value tables will always be the number of elements
41 available in the table. The elements in the tables will be of type VARCHAR2
42 any conversion necessary should be performed within the PL/SQL procedure.
43
44 The parameters returned by formula execution will be passed
45 to the stored procedure. Parameters may or may not be altered by the PL/SQL
46 procedure and will be passed back to the formula for the next execution.
47 Context tables will always be reset by the PL/SQL procedure.
48
49 The names of the tables used to interface with the PL/SQL procedure are
50 param_names type IN/OUT
51 param_values type IN/OUT
52 context_names type OUT
53 context_values type OUT
54
55 The second item in the output_parameter_value table will be the formula ID
56 of the next formula to be executed (the first item is the number of values
57 in the table).
58
59 Change List
60 -----------
61 Date Name Vers Bug No Description
62 ---- ---- ---- ------ -----------
63 30-JUN-95 ASNELL 40.0 Created.
64 30-JUN-95 NBRISTOW 40.1 Modified to use PL/SQL tables
65 to pass parameter and
66 and context rule data.
67 29-AUG-95 NBRISTOW 40.2 Added cursors for the single
68 day single file format.
69 30-JUL-96 JALLOUN 40.3 Added error handling.
70 16-JUL-97 APARKES 40.15 513830 Added ORG_PAY_METHOD_ID
71 Context to %_bacs_payment_method_id
72 cursors.
73 02-DEC-97 APARKES 40.16 572919 changed sub-selects in the
74 %m_bacs_payment% cursors to project
75 1 instead of '' as this was causing
76 only one assignment per processing
77 day to be reported in R11.
78 19-DEC-97 APARKES 40.17 572940 Added correlated subqueries to the
79 593757 %m_bacs_payment% cursors to ensure
80 that only one payment is made per
81 assignment when pre-payments are run
82 across multiple payroll runs.
83 23-FEB-97 APARKES 40.18 619733 Corrected cursors
84 %m_bacs_payment_method_id to
85 order by process date within
86 payment method id.
87 22-APR-98 ARUNDELL 110.4 641673 Changes to sm_bacs_payment_method_id
88 and sm_bacs_payment for multiday
89 performance improvements.
90 23-JUL-98 APARKES 110.6 641673 Further performance fixes to
91 all formats.
92 03-DEC-98 FDUCHENE 110.7 749168 Changes to sm_bacs_payment_method_id
93 and s_bacs_payment_method_id for
94 enabling BACS to report in Euros.
95 Other cursors involved :
96 ms_bacs_header, m_bacs_header,
97 m_bacs_payment_method_id,
98 and ms_bacs_payment_method_id.
99 15-FEB-00 SMROBINS 115.4 1071880 Handle date parameters in canonical
100 format.
101 25-FEB-00 SMROBINS 115.5 1071880 Change to sm_bacs_payment_method_id
102 and m_bacs_payment_method_id handle
103 date parameters in canonical format
104 06-MAR-02 GBUTLER 115.6 Added dbdrv comments
105 22-JUL-02 AMILLS 115.7 2466221 fix to sm_bacs_payment cursor for
106 Canonical date conversion.
107 08-JAN-03 GBUTLER 115.8 2665685 Performance enhancements to cursors
108 sm_bacs_payment_method_id,
109 m_bacs_payment_method_id,
110 ms_bacs_payment_method_id,
111 s_bacs_payment_method_id
112 26-AUG-04 KTHAMPAN 115.9 Change to s_bacs_header, sm_bacs_header,
113 ms_bacs_header and m_bacs_header to
114 return the bacs format type
115 07-SEP-06 NPERSHAD 115.11 5514457 Modified the sort sequence.
116 14-JUL-08 PVELUGUL 115.12 6689591 Modified for 6689591.
117 07-Jul-09 NAMGOYAL 115.13 8505257 Added Cash Management Reconciliation
118 function
119 Package header:
120 */
121 --Single file single day
122 -- Cursors
123 --
124 CURSOR s_bacs_header IS
125 select 'TRANSFER_EFFECTIVE_DATE=P',
126 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
127 'DATE_EARNED=C',
128 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
129 'ORG_PAY_METHOD_ID=C',
130 min(ppp.org_payment_method_id),
131 'TRANSFER_TODAYS_DATE=P',
132 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
133 'TRANSFER_BACS_PROCESS_DATE=P',
134 to_char(ppa.overriding_dd_date, 'YYYY/MM/DD HH24:MI:SS'),
135 'TRANSFER_FORMAT_TYPE=P',
136 substr(ppa.LEGISLATIVE_PARAMETERS,instr(LEGISLATIVE_PARAMETERS,'FORMAT_TYPE=')+12,1)
137 from fnd_sessions fnd,
138 pay_pre_payments ppp,
139 pay_assignment_actions paa,
140 pay_payroll_actions ppa
141 where paa.payroll_action_id =
142 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
143 and ppa.payroll_action_id = paa.payroll_action_id
144 and ppp.pre_payment_id = paa.pre_payment_id
145 and fnd.session_id = userenv('sessionid')
146 group by fnd.effective_date, sysdate, ppa.overriding_dd_date,ppa.legislative_parameters;
147 --
148 CURSOR s_bacs_payment_method_id IS
149 SELECT /*+ ORDERED */ DISTINCT 'TRANSFER_ORG_PAY_METHOD=P',
150 ppp.org_payment_method_id,
151 'ORG_PAY_METHOD_ID=C',
152 ppp.org_payment_method_id,
153 'TRANSFER_USER_NUMBER=P',
154 popm.pmeth_information1,
155 'TRANSFER_CURRENCY_CODE=P',
156 popm.currency_code,
157 'ORG_PAY_METHOD_NAME=P',
158 popm.ORG_PAYMENT_METHOD_NAME
159 from pay_assignment_actions paa,
160 pay_pre_payments ppp,
161 pay_org_payment_methods popm
162 WHERE paa.payroll_action_id =
163 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
164 AND ppp.pre_payment_id = paa.pre_payment_id
165 AND ppp.org_payment_method_id = popm.org_payment_method_id
166 ORDER by ppp.org_payment_method_id;
167 --
168 CURSOR s_bacs_payment IS
169 select 'TRANSFER_VALUE=P',
170 ROUND(ppp.value,2) * 100, /*BUG:6689591*/
171 'TRANSFER_ASSIGN_NO=P',
172 pa.assignment_number,
173 'PER_PAY_METHOD_ID=C',
174 ppp.personal_payment_method_id,
175 'TRANSFER_ASG_ACTION_ID=P',
176 paa.assignment_action_id
177 from pay_external_accounts pea,
178 pay_personal_payment_methods ppm,
179 per_assignments pa,
180 pay_pre_payments ppp,
181 pay_assignment_actions paa
182 where paa.payroll_action_id =
183 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
184 and paa.pre_payment_id = ppp.pre_payment_id
185 and paa.assignment_id = pa.assignment_id
186 and ppp.org_payment_method_id +0 =
187 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD')
188 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
189 and ppm.external_account_id = pea.external_account_id
190 order by decode(pay_magtape_generic.get_parameter_value('SET_ORDER_BY'),
191 'A', pa.assignment_number,
192 'S', pea.segment3||pea.segment5,
193 'E', pea.segment5, null);
194 --
195 -- Single file multi day
196 --
197 -- Cursors
198 --
199 --
200 CURSOR sm_bacs_header IS
201 select 'ORG_PAY_METHOD_ID=C',
202 min(ppp.org_payment_method_id),
203 'TRANSFER_EFFECTIVE_DATE=P',
204 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
205 'DATE_EARNED=C',
206 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
207 'TRANSFER_TODAYS_DATE=P',
208 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
209 'TRANSFER_BACS_PROCESS_DATE=P',
210 to_char(ppa.overriding_dd_date, 'YYYY/MM/DD HH24:MI:SS'),
211 'TRANSFER_BACS_PROCESS_DATE2=P',
212 to_char(ppa.overriding_dd_date, 'YYDDD'),
213 'TRANSFER_FORMAT_TYPE=P',
214 substr(ppa.LEGISLATIVE_PARAMETERS,instr(LEGISLATIVE_PARAMETERS,'FORMAT_TYPE=')+12,1)
215 from fnd_sessions fnd,
216 pay_pre_payments ppp,
217 pay_assignment_actions paa,
218 pay_payroll_actions ppa
219 where paa.payroll_action_id =
220 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
221 and ppa.payroll_action_id = paa.payroll_action_id
222 and ppp.pre_payment_id = paa.pre_payment_id
223 and fnd.session_id = userenv('sessionid')
224 group by fnd.effective_date, sysdate, ppa.overriding_dd_date,ppa.legislative_parameters;
225 --
226 CURSOR sm_bacs_payment_method_id IS
227 SELECT /*+ ORDERED */ DISTINCT 'TRANSFER_ORG_PAY_METHOD=P',
228 ppp.org_payment_method_id,
229 'ORG_PAY_METHOD_ID=C',
230 ppp.org_payment_method_id,
231 'TRANSFER_PER_PROCESS_DATE=P',
232 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
233 to_date(pay_magtape_generic.get_parameter_value(
234 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
235 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
236 to_date(pay_magtape_generic.get_parameter_value(
237 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
238 'TRANSFER_PER_PROCESS_DATE2=P',
239 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
240 to_date(pay_magtape_generic.get_parameter_value(
241 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYDDD'),
242 'TRANSFER_USER_NUMBER=P',
243 popm.pmeth_information1,
244 'TRANSFER_CURRENCY_CODE=P',
245 popm.currency_code,
246 'ORG_PAY_METHOD_NAME=P',
247 popm.ORG_PAYMENT_METHOD_NAME
248 from pay_assignment_actions paa,
249 pay_pre_payments ppp,
250 pay_org_payment_methods popm,
251 pay_run_results prr,
252 pay_element_types pet,
253 pay_run_result_values prrv,
254 pay_input_values piv
255 where paa.payroll_action_id =
256 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
257 and prr.assignment_action_id =
258 (select max(locked_action_id)
259 from pay_action_interlocks pai
260 where ppp.assignment_action_id=pai.locking_action_id
261 )
262 and prrv.run_result_id = prr.run_result_id
263 + decode(pet.element_type_id,0,0,0)
264 and pet.element_type_id = prr.element_type_id
265 and pet.element_name = 'BACS Process Date'
266 and piv.input_value_id = prrv.input_value_id
267 and piv.name = 'Process Date'
268 and ppp.pre_payment_id = paa.pre_payment_id
269 and ppp.org_payment_method_id = popm.org_payment_method_id
270 union
271 select DISTINCT 'TRANSFER_ORG_PAY_METHOD=P',
272 ppp.org_payment_method_id,
273 'ORG_PAY_METHOD_ID=C',
274 ppp.org_payment_method_id,
275 'TRANSFER_PER_PROCESS_DATE=P',
276 to_char(greatest(ptp.default_dd_date,
277 to_date(pay_magtape_generic.get_parameter_value(
278 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
279 to_char(greatest(ptp.default_dd_date,
280 to_date(pay_magtape_generic.get_parameter_value(
281 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
282 'TRANSFER_PER_PROCESS_DATE2=P',
283 to_char(greatest(ptp.default_dd_date,
284 to_date(pay_magtape_generic.get_parameter_value(
285 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYDDD'),
286 'TRANSFER_USER_NUMBER=P',
287 popm.pmeth_information1,
288 'TRANSFER_CURRENCY_CODE=P',
289 popm.currency_code,
290 'ORG_PAY_METHOD_NAME=P',
291 popm.ORG_PAYMENT_METHOD_NAME
292 from per_time_periods ptp,
293 pay_payroll_actions ppa,
294 pay_assignment_actions paa2,
295 pay_org_payment_methods popm,
296 pay_pre_payments ppp,
297 pay_assignment_actions paa
298 where paa.payroll_action_id =
299 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
300 and ppp.pre_payment_id = paa.pre_payment_id
301 and paa2.assignment_action_id =
302 (select max(locked_action_id)
303 from pay_action_interlocks pai
304 where ppp.assignment_action_id=pai.locking_action_id
305 )
306 and not exists (select 1
307 from pay_element_types pet,
308 pay_run_results prr
309 where prr.assignment_action_id = paa2.assignment_action_id
310 and pet.element_type_id = prr.element_type_id
311 and pet.element_name = 'BACS Process Date')
312 and paa2.payroll_action_id = ppa.payroll_action_id
313 and ppa.time_period_id = ptp.time_period_id
314 and ppp.org_payment_method_id = popm.org_payment_method_id
315 order by 2, 7;
316 --
317 CURSOR sm_bacs_payment IS
318 select
319 'TRANSFER_VALUE=P',
320 ROUND(oppp.value,2) * 100, /*BUG:6689591*/
321 'TRANSFER_ASSIGN_NO=P',
322 opa.assignment_number,
323 'PER_PAY_METHOD_ID=C',
324 oppp.personal_payment_method_id,
325 'TRANSFER_ASG_ACTION_ID=P',
326 opaa.assignment_action_id
327 from pay_external_accounts opea,
328 pay_personal_payment_methods oppm,
329 per_assignments opa,
330 pay_pre_payments oppp,
331 pay_assignment_actions opaa
332 where opaa.payroll_action_id =
333 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
334 and opa.assignment_id = opaa.assignment_id
335 and opaa.pre_payment_id = oppp.pre_payment_id
336 and oppp.org_payment_method_id +0 =
337 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD')
338 and oppm.personal_payment_method_id = oppp.personal_payment_method_id
339 and opea.external_account_id = oppm.external_account_id
340 and exists (
341 select 1
342 from pay_input_values piv,
343 pay_run_result_values prrv,
344 pay_element_types pet,
345 pay_run_results prr
346 where prr.assignment_action_id =
347 (select max(pai.locked_action_id)
348 from pay_action_interlocks pai
349 where oppp.assignment_action_id=pai.locking_action_id
350 )
351 and prrv.run_result_id = prr.run_result_id
352 + decode(pet.element_type_id,0,0,0)
353 and pet.element_type_id = prr.element_type_id
354 and pet.element_name = 'BACS Process Date'
355 and piv.input_value_id = prrv.input_value_id
356 and piv.name = 'Process Date'
357 and (to_date(pay_magtape_generic.get_parameter_value(
358 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
359 = to_date(prrv.result_value,'YYYY/MM/DD HH24:MI:SS')
360 OR (
361 to_date(prrv.result_value,'YYYY/MM/DD HH24:MI:SS') <
362 to_date(pay_magtape_generic.get_parameter_value(
363 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
364 AND to_date(pay_magtape_generic.get_parameter_value(
365 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS') =
366 to_date(pay_magtape_generic.get_parameter_value(
367 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
368 )
369 )
370 union
371 select 1
372 from
373 per_time_periods ptp,
374 pay_payroll_actions ppa,
375 pay_assignment_actions paa
376 where paa.assignment_action_id =
377 (select max(pai.locked_action_id)
378 from pay_action_interlocks pai
379 where oppp.assignment_action_id=pai.locking_action_id
380 )
381 and paa.payroll_action_id = ppa.payroll_action_id
382 and ppa.payroll_id = ptp.payroll_id
383 and ppa.time_period_id = ptp.time_period_id
384 and (ptp.default_dd_date =
385 to_date(pay_magtape_generic.get_parameter_value(
386 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
387 or (pay_magtape_generic.get_parameter_value(
388 'TRANSFER_PER_PROCESS_DATE') =
389 pay_magtape_generic.get_parameter_value(
390 'TRANSFER_BACS_PROCESS_DATE')
391 and to_date(pay_magtape_generic.get_parameter_value(
392 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
393 > ptp.default_dd_date))
394 and not exists (select 1
395 from pay_element_types pet,
396 pay_run_results prr
397 where prr.assignment_action_id = paa.assignment_action_id
398 and pet.element_type_id = prr.element_type_id
399 and pet.element_name = 'BACS Process Date')
400 )
401 order by decode(pay_magtape_generic.get_parameter_value('SET_ORDER_BY'),
402 'A', opa.assignment_number,
403 'S', opea.segment3||opea.segment5,
404 'E', opea.segment5, null);
405 --
406 --
407 --Multi file single day
408 -- Cursors
409 --
410 CURSOR ms_bacs_vol IS
411 select distinct
412 'DATE_EARNED=C',
413 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
414 'TRANSFER_EFFECTIVE_DATE=P',
415 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
416 'TRANSFER_FIRST_BUREAU_NO=P',
417 org.pmeth_information3,
418 'TRANSFER_FORMAT_TYPE=P',
419 substr(ppa.LEGISLATIVE_PARAMETERS,instr(LEGISLATIVE_PARAMETERS,'FORMAT_TYPE=')+12,1)
420 from fnd_sessions fnd,
421 PAY_ORG_PAYMENT_METHODS org,
422 pay_pre_payments ppp,
423 pay_assignment_actions paa,
424 pay_payroll_actions ppa
425 where paa.payroll_action_id =
426 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
427 and ppa.payroll_action_id = paa.payroll_action_id
428 and ppp.pre_payment_id = paa.pre_payment_id
429 and ppp.org_payment_method_id = org.org_payment_method_id
430 and fnd.session_id = userenv('sessionid');
431 --
432 CURSOR ms_bacs_header IS
433 select 'TRANSFER_EFFECTIVE_DATE=P',
434 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
435 'BACS_TAPE_BACS_USER_NUMBER=P',
436 org.pmeth_information1,
437 'ORG_PAY_METHOD_ID=C',
438 min(ppp.org_payment_method_id),
439 'TRANSFER_TODAYS_DATE=P',
440 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
441 'TRANSFER_BACS_PROCESS_DATE=P',
442 to_char(ppa.overriding_dd_date, 'YYYY/MM/DD HH24:MI:SS'),
443 'TRANSFER_CURRENCY_CODE=P',
444 org.currency_code,
445 'ORG_PAY_METHOD_NAME=P',
446 min(org.org_payment_method_name)
447 from fnd_sessions fnd,
448 pay_org_payment_methods org,
449 pay_pre_payments ppp,
450 pay_assignment_actions paa,
451 pay_payroll_actions ppa
452 where paa.payroll_action_id =
453 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
454 and ppa.payroll_action_id = paa.payroll_action_id
455 and ppp.pre_payment_id = paa.pre_payment_id
456 and org.org_payment_method_id = ppp.org_payment_method_id
457 and fnd.session_id = userenv('sessionid')
458 group by org.pmeth_information1,fnd.effective_date,
459 sysdate, ppa.overriding_dd_date, org.currency_code
460 order by 4, 12;
461 --
462 CURSOR ms_bacs_payment_method_id IS
463 SELECT /*+ ORDERED */ DISTINCT 'TRANSFER_ORG_PAY_METHOD=P',
464 ppp.org_payment_method_id,
465 'ORG_PAY_METHOD_ID=C',
466 ppp.org_payment_method_id,
467 'TRANSFER_BUREAU_NO=P',
468 org.pmeth_information3,
469 'ORG_PAY_METHOD_NAME=P',
470 org.org_payment_method_name
471 FROM pay_assignment_actions paa,
472 pay_pre_payments ppp,
473 pay_org_payment_methods org
474 WHERE org.currency_code =
475 pay_magtape_generic.get_parameter_value('TRANSFER_CURRENCY_CODE')
476 and paa.payroll_action_id =
477 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
478 AND ppp.pre_payment_id = paa.pre_payment_id
479 and ppp.org_payment_method_id = org.org_payment_method_id
480 and org.pmeth_information1 =
481 pay_magtape_generic.get_parameter_value('BACS_TAPE_BACS_USER_NUMBER')
482 ORDER by ppp.org_payment_method_id;
483 --
484 CURSOR ms_bacs_payment IS
485 select 'TRANSFER_VALUE=P',
486 ROUND(ppp.value,2) * 100, /*BUG:6689591*/
487 'TRANSFER_ASSIGN_NO=P',
488 pa.assignment_number,
489 'PER_PAY_METHOD_ID=C',
490 ppp.personal_payment_method_id,
491 'TRANSFER_ASG_ACTION_ID=P',
492 paa.assignment_action_id
493 from pay_assignment_actions paa,
494 pay_pre_payments ppp,
495 per_assignments pa,
496 pay_personal_payment_methods ppm,
497 pay_external_accounts pea
498 where paa.payroll_action_id =
499 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
500 and paa.pre_payment_id = ppp.pre_payment_id
501 and paa.assignment_id = pa.assignment_id
502 and ppp.org_payment_method_id +0 =
503 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD')
504 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
505 and ppm.external_account_id = pea.external_account_id
506 order by decode(pay_magtape_generic.get_parameter_value('SET_ORDER_BY'),
507 'A', pa.assignment_number, 'S', pea.segment3||pea.segment5, 'E', pea.segment5, null);
508 --
509 --
510 -- Multi file multi day
511 -- Cursors
512 --
513 CURSOR m_bacs_vol IS
514 select distinct
515 'DATE_EARNED=C',
516 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
517 'TRANSFER_EFFECTIVE_DATE=P',
518 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS') ,
519 'TRANSFER_FIRST_BUREAU_NO=P',
520 org.pmeth_information3,
521 'TRANSFER_FORMAT_TYPE=P',
522 substr(ppa.LEGISLATIVE_PARAMETERS,instr(LEGISLATIVE_PARAMETERS,'FORMAT_TYPE=')+12,1)
523 from fnd_sessions fnd,
524 PAY_ORG_PAYMENT_METHODS org,
525 pay_pre_payments ppp,
526 pay_payroll_actions ppa,
527 pay_assignment_actions paa
528 where paa.payroll_action_id =
529 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
530 and ppa.payroll_action_id = paa.payroll_action_id
531 and ppp.pre_payment_id = paa.pre_payment_id
532 and ppp.org_payment_method_id = org.org_payment_method_id
533 and fnd.session_id = userenv('sessionid');
534 --
535 CURSOR m_bacs_header IS
536 select 'BACS_TAPE_BACS_USER_NUMBER=P',
537 org.pmeth_information1,
538 'ORG_PAY_METHOD_ID=C',
539 min(ppp.org_payment_method_id),
540 'TRANSFER_TODAYS_DATE=P',
541 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
542 'TRANSFER_BACS_PROCESS_DATE=P',
543 to_char(ppa.overriding_dd_date, 'YYYY/MM/DD HH24:MI:SS'),
544 'TRANSFER_BACS_PROCESS_DATE2=P',
545 to_char(ppa.overriding_dd_date, 'YYDDD'),
546 'TRANSFER_CURRENCY_CODE=P',
547 org.currency_code,
548 'ORG_PAY_METHOD_NAME=P',
549 min(org.org_payment_method_name)
550 from fnd_sessions fnd,
551 pay_org_payment_methods org,
552 pay_pre_payments ppp,
553 pay_payroll_actions ppa,
554 pay_assignment_actions paa
555 where paa.payroll_action_id =
556 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
557 and ppa.payroll_action_id = paa.payroll_action_id
558 and ppp.pre_payment_id = paa.pre_payment_id
559 and org.org_payment_method_id = ppp.org_payment_method_id
560 and fnd.session_id = userenv('sessionid')
561 group by org.pmeth_information1,fnd.effective_date,
562 sysdate, ppa.overriding_dd_date, org.currency_code
563 order by 2, 12;
564 --
565 CURSOR m_bacs_payment_method_id IS
566 SELECT /*+ ORDERED */ DISTINCT
567 'TRANSFER_ORG_PAY_METHOD=P',
568 ppp.org_payment_method_id,
569 'ORG_PAY_METHOD_ID=C',
570 ppp.org_payment_method_id,
571 'TRANSFER_PER_PROCESS_DATE=P',
572 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
573 to_date(pay_magtape_generic.get_parameter_value(
574 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
575 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
576 to_date(pay_magtape_generic.get_parameter_value(
577 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
578 'TRANSFER_PER_PROCESS_DATE2=P',
579 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
580 to_date(pay_magtape_generic.get_parameter_value(
581 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYDDD'),
582 'TRANSFER_BUREAU_NO=P',
583 org.pmeth_information3,
584 'ORG_PAY_METHOD_NAME=P',
585 org.org_payment_method_name
586 from pay_assignment_actions paa,
587 pay_pre_payments ppp,
588 pay_org_payment_methods org,
589 pay_run_results prr,
590 pay_element_types pet,
591 pay_run_result_values prrv,
592 pay_input_values piv
593 where org.currency_code =
594 pay_magtape_generic.get_parameter_value('TRANSFER_CURRENCY_CODE')
595 and paa.payroll_action_id =
596 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
597 and ppp.pre_payment_id = paa.pre_payment_id
598 and ppp.org_payment_method_id = org.org_payment_method_id
599 and org.pmeth_information1 =
600 pay_magtape_generic.get_parameter_value('BACS_TAPE_BACS_USER_NUMBER')
601 and prr.assignment_action_id =
602 (select max(locked_action_id)
603 + decode(org.org_payment_method_id,0,0,0)
604 from pay_action_interlocks pai
605 where ppp.assignment_action_id=pai.locking_action_id
606 )
607 and pet.element_type_id = prr.element_type_id
608 and pet.element_name = 'BACS Process Date'
609 and prrv.run_result_id = prr.run_result_id
610 + decode(pet.element_type_id,0,0,0)
611 and piv.input_value_id = prrv.input_value_id
612 and piv.name = 'Process Date'
613 union
614 select DISTINCT
615 'TRANSFER_ORG_PAY_METHOD=P',
616 ppp.org_payment_method_id,
617 'ORG_PAY_METHOD_ID=C',
618 ppp.org_payment_method_id,
619 'TRANSFER_PER_PROCESS_DATE=P',
620 to_char(greatest(ptp.default_dd_date,
621 to_date(pay_magtape_generic.get_parameter_value(
622 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
623 to_char(greatest(ptp.default_dd_date,
624 to_date(pay_magtape_generic.get_parameter_value(
625 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
626 'TRANSFER_PER_PROCESS_DATE2=P',
627 to_char(greatest(ptp.default_dd_date,
628 to_date(pay_magtape_generic.get_parameter_value(
629 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYDDD'),
630 'TRANSFER_BUREAU_NO=P', org.pmeth_information3,
631 'ORG_PAY_METHOD_NAME=P', org.org_payment_method_name
632 from per_time_periods ptp,
633 pay_payroll_actions ppa,
634 pay_assignment_actions paa2,
635 pay_org_payment_methods org,
636 pay_pre_payments ppp,
637 pay_assignment_actions paa
638 where org.currency_code =
639 pay_magtape_generic.get_parameter_value('TRANSFER_CURRENCY_CODE')
640 and paa.payroll_action_id =
641 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
642 and ppp.pre_payment_id = paa.pre_payment_id
643 and ppp.org_payment_method_id = org.org_payment_method_id
644 and org.pmeth_information1 =
645 pay_magtape_generic.get_parameter_value('BACS_TAPE_BACS_USER_NUMBER')
646 and paa2.assignment_action_id =
647 (select max(locked_action_id)
648 + decode(org.org_payment_method_id,0,0,0)
649 from pay_action_interlocks pai
650 where ppp.assignment_action_id=pai.locking_action_id
651 )
652 and not exists (select 1
653 from pay_element_types pet,
654 pay_run_results prr
655 where prr.assignment_action_id = paa2.assignment_action_id
656 and pet.element_type_id = prr.element_type_id
657 and pet.element_name = 'BACS Process Date')
658 and paa2.payroll_action_id = ppa.payroll_action_id
659 and ppa.time_period_id = ptp.time_period_id
660 order by 2, 7;
661 --
662 CURSOR m_bacs_payment IS
663 select
664 'TRANSFER_VALUE=P',
665 ROUND(oppp.value,2) * 100,/* BUG:6689691*/
666 'TRANSFER_ASSIGN_NO=P',
667 opa.assignment_number,
668 'PER_PAY_METHOD_ID=C',
669 oppp.personal_payment_method_id,
670 'TRANSFER_ASG_ACTION_ID=P',
671 opaa.assignment_action_id
672 from
673 pay_external_accounts opea,
674 pay_personal_payment_methods oppm,
675 per_assignments opa,
676 pay_pre_payments oppp,
677 pay_assignment_actions opaa
678 where opaa.payroll_action_id =
679 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
680 and opa.assignment_id = opaa.assignment_id
681 and opaa.pre_payment_id = oppp.pre_payment_id
682 and oppp.org_payment_method_id =
683 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD')
684 and oppm.personal_payment_method_id = oppp.personal_payment_method_id
685 and opea.external_account_id = oppm.external_account_id
686 and exists (
687 select 1
688 from pay_input_values piv,
689 pay_run_result_values prrv,
690 pay_element_types pet,
691 pay_run_results prr
692 where prr.assignment_action_id =
693 (select max(pai.locked_action_id)
694 from pay_action_interlocks pai
695 where oppp.assignment_action_id=pai.locking_action_id
696 )
697 and prrv.run_result_id = prr.run_result_id
698 + decode(pet.element_type_id,0,0,0)
699 and pet.element_type_id = prr.element_type_id
700 and pet.element_name = 'BACS Process Date'
701 and piv.input_value_id = prrv.input_value_id
702 and piv.name = 'Process Date'
703 and (to_date(pay_magtape_generic.get_parameter_value(
704 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
705 = to_date(prrv.result_value,'YYYY/MM/DD HH24:MI:SS')
706 OR (
707 to_date(prrv.result_value,'YYYY/MM/DD HH24:MI:SS') <
708 to_date(pay_magtape_generic.get_parameter_value(
709 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
710 AND to_date(pay_magtape_generic.get_parameter_value(
711 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS') =
712 to_date(pay_magtape_generic.get_parameter_value(
713 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
714 )
715 )
716 union
717 select 1
718 from
719 per_time_periods ptp,
720 pay_payroll_actions ppa,
721 pay_assignment_actions paa
722 where paa.assignment_action_id =
723 (select max(pai.locked_action_id)
724 from pay_action_interlocks pai
725 where oppp.assignment_action_id=pai.locking_action_id
726 )
727 and paa.payroll_action_id = ppa.payroll_action_id
728 and ppa.payroll_id = ptp.payroll_id
729 and ppa.time_period_id = ptp.time_period_id
730 and (ptp.default_dd_date =
731 to_date(pay_magtape_generic.get_parameter_value(
732 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
733 or (pay_magtape_generic.get_parameter_value(
734 'TRANSFER_PER_PROCESS_DATE') =
735 pay_magtape_generic.get_parameter_value(
736 'TRANSFER_BACS_PROCESS_DATE')
737 and to_date(pay_magtape_generic.get_parameter_value(
738 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
739 > ptp.default_dd_date))
740 and not exists (select 1
741 from pay_element_types pet,
742 pay_run_results prr
743 where prr.assignment_action_id = paa.assignment_action_id
744 and pet.element_type_id = prr.element_type_id
745 and pet.element_name = 'BACS Process Date')
746 )
747 order by decode(pay_magtape_generic.get_parameter_value('SET_ORDER_BY'),
748 'A', opa.assignment_number,
749 'S', opea.segment3||opea.segment5,
750 'E', opea.segment5, null);
751 --
752 --
753 level_cnt number;
754 --
755 PROCEDURE new_formula;
756 --
757 FUNCTION get_process_date(p_assignment_action_id in number,
758 p_entry_date in date)
759 return date;
760 FUNCTION validate_process_date(p_assignment_action_id in number,
761 p_process_date in date)
762 return date;
763
764 --Cash Management Reconciliation function
765 FUNCTION f_get_eft_recon_data (p_effective_date IN DATE,
766 p_identifier_name IN VARCHAR2,
767 p_payroll_action_id IN NUMBER,
768 p_payment_type_id IN NUMBER,
769 p_org_payment_method_id IN NUMBER,
770 p_personal_payment_method_id IN NUMBER,
771 p_assignment_action_id IN NUMBER,
772 p_pre_payment_id IN NUMBER,
773 p_delimiter_string IN VARCHAR2)
774 RETURN VARCHAR2;
775
776 --
777 END pay_gb_bacs_tape;