[Home] [Help]
PACKAGE: APPS.PAY_GB_BACS_TAPE
Source
1 PACKAGE pay_gb_bacs_tape AS
2 /* $Header: pytapbac.pkh 120.1.12010000.2 2008/08/06 08:23:39 ubhat 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 Package header:
118 */
119 --Single file single day
120 -- Cursors
121 --
122 CURSOR s_bacs_header IS
123 select 'TRANSFER_EFFECTIVE_DATE=P',
124 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
125 'DATE_EARNED=C',
126 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
127 'ORG_PAY_METHOD_ID=C',
128 min(ppp.org_payment_method_id),
129 'TRANSFER_TODAYS_DATE=P',
130 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
131 'TRANSFER_BACS_PROCESS_DATE=P',
132 to_char(ppa.overriding_dd_date, 'YYYY/MM/DD HH24:MI:SS'),
133 'TRANSFER_FORMAT_TYPE=P',
134 substr(ppa.LEGISLATIVE_PARAMETERS,instr(LEGISLATIVE_PARAMETERS,'FORMAT_TYPE=')+12,1)
135 from fnd_sessions fnd,
136 pay_pre_payments ppp,
137 pay_assignment_actions paa,
138 pay_payroll_actions ppa
139 where paa.payroll_action_id =
140 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
141 and ppa.payroll_action_id = paa.payroll_action_id
142 and ppp.pre_payment_id = paa.pre_payment_id
143 and fnd.session_id = userenv('sessionid')
144 group by fnd.effective_date, sysdate, ppa.overriding_dd_date,ppa.legislative_parameters;
145 --
146 CURSOR s_bacs_payment_method_id IS
147 SELECT /*+ ORDERED */ DISTINCT 'TRANSFER_ORG_PAY_METHOD=P',
148 ppp.org_payment_method_id,
149 'ORG_PAY_METHOD_ID=C',
150 ppp.org_payment_method_id,
151 'TRANSFER_USER_NUMBER=P',
152 popm.pmeth_information1,
153 'TRANSFER_CURRENCY_CODE=P',
154 popm.currency_code,
155 'ORG_PAY_METHOD_NAME=P',
156 popm.ORG_PAYMENT_METHOD_NAME
157 from pay_assignment_actions paa,
158 pay_pre_payments ppp,
159 pay_org_payment_methods popm
160 WHERE paa.payroll_action_id =
161 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
162 AND ppp.pre_payment_id = paa.pre_payment_id
163 AND ppp.org_payment_method_id = popm.org_payment_method_id
164 ORDER by ppp.org_payment_method_id;
165 --
166 CURSOR s_bacs_payment IS
167 select 'TRANSFER_VALUE=P',
168 ROUND(ppp.value,2) * 100, /*BUG:6689591*/
169 'TRANSFER_ASSIGN_NO=P',
170 pa.assignment_number,
171 'PER_PAY_METHOD_ID=C',
172 ppp.personal_payment_method_id,
173 'TRANSFER_ASG_ACTION_ID=P',
174 paa.assignment_action_id
175 from pay_external_accounts pea,
176 pay_personal_payment_methods ppm,
177 per_assignments pa,
178 pay_pre_payments ppp,
179 pay_assignment_actions paa
180 where paa.payroll_action_id =
181 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
182 and paa.pre_payment_id = ppp.pre_payment_id
183 and paa.assignment_id = pa.assignment_id
184 and ppp.org_payment_method_id +0 =
185 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD')
186 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
187 and ppm.external_account_id = pea.external_account_id
188 order by decode(pay_magtape_generic.get_parameter_value('SET_ORDER_BY'),
189 'A', pa.assignment_number,
190 'S', pea.segment3||pea.segment5,
191 'E', pea.segment5, null);
192 --
193 -- Single file multi day
194 --
195 -- Cursors
196 --
197 --
198 CURSOR sm_bacs_header IS
199 select 'ORG_PAY_METHOD_ID=C',
200 min(ppp.org_payment_method_id),
201 'TRANSFER_EFFECTIVE_DATE=P',
202 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
203 'DATE_EARNED=C',
204 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
205 'TRANSFER_TODAYS_DATE=P',
206 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
207 'TRANSFER_BACS_PROCESS_DATE=P',
208 to_char(ppa.overriding_dd_date, 'YYYY/MM/DD HH24:MI:SS'),
209 'TRANSFER_BACS_PROCESS_DATE2=P',
210 to_char(ppa.overriding_dd_date, 'YYDDD'),
211 'TRANSFER_FORMAT_TYPE=P',
212 substr(ppa.LEGISLATIVE_PARAMETERS,instr(LEGISLATIVE_PARAMETERS,'FORMAT_TYPE=')+12,1)
213 from fnd_sessions fnd,
214 pay_pre_payments ppp,
215 pay_assignment_actions paa,
216 pay_payroll_actions ppa
217 where paa.payroll_action_id =
218 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
219 and ppa.payroll_action_id = paa.payroll_action_id
220 and ppp.pre_payment_id = paa.pre_payment_id
221 and fnd.session_id = userenv('sessionid')
222 group by fnd.effective_date, sysdate, ppa.overriding_dd_date,ppa.legislative_parameters;
223 --
224 CURSOR sm_bacs_payment_method_id IS
225 SELECT /*+ ORDERED */ DISTINCT 'TRANSFER_ORG_PAY_METHOD=P',
226 ppp.org_payment_method_id,
227 'ORG_PAY_METHOD_ID=C',
228 ppp.org_payment_method_id,
229 'TRANSFER_PER_PROCESS_DATE=P',
230 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
231 to_date(pay_magtape_generic.get_parameter_value(
232 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
233 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
234 to_date(pay_magtape_generic.get_parameter_value(
235 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
236 'TRANSFER_PER_PROCESS_DATE2=P',
237 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
238 to_date(pay_magtape_generic.get_parameter_value(
239 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYDDD'),
240 'TRANSFER_USER_NUMBER=P',
241 popm.pmeth_information1,
242 'TRANSFER_CURRENCY_CODE=P',
243 popm.currency_code,
244 'ORG_PAY_METHOD_NAME=P',
245 popm.ORG_PAYMENT_METHOD_NAME
246 from pay_assignment_actions paa,
247 pay_pre_payments ppp,
248 pay_org_payment_methods popm,
249 pay_run_results prr,
250 pay_element_types pet,
251 pay_run_result_values prrv,
252 pay_input_values piv
253 where paa.payroll_action_id =
254 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
255 and prr.assignment_action_id =
256 (select max(locked_action_id)
257 from pay_action_interlocks pai
258 where ppp.assignment_action_id=pai.locking_action_id
259 )
260 and prrv.run_result_id = prr.run_result_id
261 + decode(pet.element_type_id,0,0,0)
262 and pet.element_type_id = prr.element_type_id
263 and pet.element_name = 'BACS Process Date'
264 and piv.input_value_id = prrv.input_value_id
265 and piv.name = 'Process Date'
266 and ppp.pre_payment_id = paa.pre_payment_id
267 and ppp.org_payment_method_id = popm.org_payment_method_id
268 union
269 select DISTINCT 'TRANSFER_ORG_PAY_METHOD=P',
270 ppp.org_payment_method_id,
271 'ORG_PAY_METHOD_ID=C',
272 ppp.org_payment_method_id,
276 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
273 'TRANSFER_PER_PROCESS_DATE=P',
274 to_char(greatest(ptp.default_dd_date,
275 to_date(pay_magtape_generic.get_parameter_value(
277 to_char(greatest(ptp.default_dd_date,
278 to_date(pay_magtape_generic.get_parameter_value(
279 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
280 'TRANSFER_PER_PROCESS_DATE2=P',
281 to_char(greatest(ptp.default_dd_date,
282 to_date(pay_magtape_generic.get_parameter_value(
283 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYDDD'),
284 'TRANSFER_USER_NUMBER=P',
285 popm.pmeth_information1,
286 'TRANSFER_CURRENCY_CODE=P',
287 popm.currency_code,
288 'ORG_PAY_METHOD_NAME=P',
289 popm.ORG_PAYMENT_METHOD_NAME
290 from per_time_periods ptp,
291 pay_payroll_actions ppa,
292 pay_assignment_actions paa2,
293 pay_org_payment_methods popm,
294 pay_pre_payments ppp,
295 pay_assignment_actions paa
296 where paa.payroll_action_id =
297 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
298 and ppp.pre_payment_id = paa.pre_payment_id
299 and paa2.assignment_action_id =
300 (select max(locked_action_id)
301 from pay_action_interlocks pai
302 where ppp.assignment_action_id=pai.locking_action_id
303 )
304 and not exists (select 1
305 from pay_element_types pet,
306 pay_run_results prr
307 where prr.assignment_action_id = paa2.assignment_action_id
308 and pet.element_type_id = prr.element_type_id
309 and pet.element_name = 'BACS Process Date')
310 and paa2.payroll_action_id = ppa.payroll_action_id
311 and ppa.time_period_id = ptp.time_period_id
312 and ppp.org_payment_method_id = popm.org_payment_method_id
313 order by 2, 7;
314 --
315 CURSOR sm_bacs_payment IS
316 select
317 'TRANSFER_VALUE=P',
318 ROUND(oppp.value,2) * 100, /*BUG:6689591*/
319 'TRANSFER_ASSIGN_NO=P',
320 opa.assignment_number,
321 'PER_PAY_METHOD_ID=C',
322 oppp.personal_payment_method_id,
323 'TRANSFER_ASG_ACTION_ID=P',
324 opaa.assignment_action_id
325 from pay_external_accounts opea,
326 pay_personal_payment_methods oppm,
327 per_assignments opa,
328 pay_pre_payments oppp,
329 pay_assignment_actions opaa
330 where opaa.payroll_action_id =
331 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
332 and opa.assignment_id = opaa.assignment_id
333 and opaa.pre_payment_id = oppp.pre_payment_id
334 and oppp.org_payment_method_id +0 =
335 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD')
336 and oppm.personal_payment_method_id = oppp.personal_payment_method_id
337 and opea.external_account_id = oppm.external_account_id
338 and exists (
339 select 1
340 from pay_input_values piv,
341 pay_run_result_values prrv,
342 pay_element_types pet,
343 pay_run_results prr
344 where prr.assignment_action_id =
345 (select max(pai.locked_action_id)
346 from pay_action_interlocks pai
347 where oppp.assignment_action_id=pai.locking_action_id
348 )
349 and prrv.run_result_id = prr.run_result_id
350 + decode(pet.element_type_id,0,0,0)
351 and pet.element_type_id = prr.element_type_id
352 and pet.element_name = 'BACS Process Date'
353 and piv.input_value_id = prrv.input_value_id
354 and piv.name = 'Process Date'
355 and (to_date(pay_magtape_generic.get_parameter_value(
356 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
357 = to_date(prrv.result_value,'YYYY/MM/DD HH24:MI:SS')
358 OR (
359 to_date(prrv.result_value,'YYYY/MM/DD HH24:MI:SS') <
360 to_date(pay_magtape_generic.get_parameter_value(
361 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
362 AND to_date(pay_magtape_generic.get_parameter_value(
363 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS') =
364 to_date(pay_magtape_generic.get_parameter_value(
365 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
366 )
367 )
368 union
369 select 1
370 from
371 per_time_periods ptp,
372 pay_payroll_actions ppa,
373 pay_assignment_actions paa
374 where paa.assignment_action_id =
375 (select max(pai.locked_action_id)
376 from pay_action_interlocks pai
377 where oppp.assignment_action_id=pai.locking_action_id
378 )
379 and paa.payroll_action_id = ppa.payroll_action_id
380 and ppa.payroll_id = ptp.payroll_id
384 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
381 and ppa.time_period_id = ptp.time_period_id
382 and (ptp.default_dd_date =
383 to_date(pay_magtape_generic.get_parameter_value(
385 or (pay_magtape_generic.get_parameter_value(
386 'TRANSFER_PER_PROCESS_DATE') =
387 pay_magtape_generic.get_parameter_value(
388 'TRANSFER_BACS_PROCESS_DATE')
389 and to_date(pay_magtape_generic.get_parameter_value(
390 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
391 > ptp.default_dd_date))
392 and not exists (select 1
393 from pay_element_types pet,
394 pay_run_results prr
395 where prr.assignment_action_id = paa.assignment_action_id
396 and pet.element_type_id = prr.element_type_id
397 and pet.element_name = 'BACS Process Date')
398 )
399 order by decode(pay_magtape_generic.get_parameter_value('SET_ORDER_BY'),
400 'A', opa.assignment_number,
401 'S', opea.segment3||opea.segment5,
402 'E', opea.segment5, null);
403 --
404 --
405 --Multi file single day
406 -- Cursors
407 --
408 CURSOR ms_bacs_vol IS
409 select distinct
410 'DATE_EARNED=C',
411 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
412 'TRANSFER_EFFECTIVE_DATE=P',
413 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
414 'TRANSFER_FIRST_BUREAU_NO=P',
415 org.pmeth_information3,
416 'TRANSFER_FORMAT_TYPE=P',
417 substr(ppa.LEGISLATIVE_PARAMETERS,instr(LEGISLATIVE_PARAMETERS,'FORMAT_TYPE=')+12,1)
418 from fnd_sessions fnd,
419 PAY_ORG_PAYMENT_METHODS org,
420 pay_pre_payments ppp,
421 pay_assignment_actions paa,
422 pay_payroll_actions ppa
423 where paa.payroll_action_id =
424 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
425 and ppa.payroll_action_id = paa.payroll_action_id
426 and ppp.pre_payment_id = paa.pre_payment_id
427 and ppp.org_payment_method_id = org.org_payment_method_id
428 and fnd.session_id = userenv('sessionid');
429 --
430 CURSOR ms_bacs_header IS
431 select 'TRANSFER_EFFECTIVE_DATE=P',
432 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
433 'BACS_TAPE_BACS_USER_NUMBER=P',
434 org.pmeth_information1,
435 'ORG_PAY_METHOD_ID=C',
436 min(ppp.org_payment_method_id),
437 'TRANSFER_TODAYS_DATE=P',
438 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
439 'TRANSFER_BACS_PROCESS_DATE=P',
440 to_char(ppa.overriding_dd_date, 'YYYY/MM/DD HH24:MI:SS'),
441 'TRANSFER_CURRENCY_CODE=P',
442 org.currency_code,
443 'ORG_PAY_METHOD_NAME=P',
444 min(org.org_payment_method_name)
445 from fnd_sessions fnd,
446 pay_org_payment_methods org,
447 pay_pre_payments ppp,
448 pay_assignment_actions paa,
449 pay_payroll_actions ppa
450 where paa.payroll_action_id =
451 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
452 and ppa.payroll_action_id = paa.payroll_action_id
453 and ppp.pre_payment_id = paa.pre_payment_id
454 and org.org_payment_method_id = ppp.org_payment_method_id
455 and fnd.session_id = userenv('sessionid')
456 group by org.pmeth_information1,fnd.effective_date,
457 sysdate, ppa.overriding_dd_date, org.currency_code
458 order by 4, 12;
459 --
460 CURSOR ms_bacs_payment_method_id IS
461 SELECT /*+ ORDERED */ DISTINCT 'TRANSFER_ORG_PAY_METHOD=P',
462 ppp.org_payment_method_id,
463 'ORG_PAY_METHOD_ID=C',
464 ppp.org_payment_method_id,
465 'TRANSFER_BUREAU_NO=P',
466 org.pmeth_information3,
467 'ORG_PAY_METHOD_NAME=P',
468 org.org_payment_method_name
469 FROM pay_assignment_actions paa,
470 pay_pre_payments ppp,
471 pay_org_payment_methods org
472 WHERE org.currency_code =
473 pay_magtape_generic.get_parameter_value('TRANSFER_CURRENCY_CODE')
474 and paa.payroll_action_id =
475 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
476 AND ppp.pre_payment_id = paa.pre_payment_id
477 and ppp.org_payment_method_id = org.org_payment_method_id
478 and org.pmeth_information1 =
479 pay_magtape_generic.get_parameter_value('BACS_TAPE_BACS_USER_NUMBER')
480 ORDER by ppp.org_payment_method_id;
481 --
482 CURSOR ms_bacs_payment IS
483 select 'TRANSFER_VALUE=P',
484 ROUND(ppp.value,2) * 100, /*BUG:6689591*/
485 'TRANSFER_ASSIGN_NO=P',
486 pa.assignment_number,
487 'PER_PAY_METHOD_ID=C',
488 ppp.personal_payment_method_id,
489 'TRANSFER_ASG_ACTION_ID=P',
490 paa.assignment_action_id
491 from pay_assignment_actions paa,
492 pay_pre_payments ppp,
493 per_assignments pa,
494 pay_personal_payment_methods ppm,
495 pay_external_accounts pea
496 where paa.payroll_action_id =
497 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
498 and paa.pre_payment_id = ppp.pre_payment_id
499 and paa.assignment_id = pa.assignment_id
500 and ppp.org_payment_method_id +0 =
501 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD')
502 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
503 and ppm.external_account_id = pea.external_account_id
504 order by decode(pay_magtape_generic.get_parameter_value('SET_ORDER_BY'),
505 'A', pa.assignment_number, 'S', pea.segment3||pea.segment5, 'E', pea.segment5, null);
506 --
507 --
508 -- Multi file multi day
509 -- Cursors
510 --
511 CURSOR m_bacs_vol IS
512 select distinct
513 'DATE_EARNED=C',
514 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS'),
515 'TRANSFER_EFFECTIVE_DATE=P',
516 to_char(fnd.effective_date, 'YYYY/MM/DD HH24:MI:SS') ,
517 'TRANSFER_FIRST_BUREAU_NO=P',
518 org.pmeth_information3,
519 'TRANSFER_FORMAT_TYPE=P',
520 substr(ppa.LEGISLATIVE_PARAMETERS,instr(LEGISLATIVE_PARAMETERS,'FORMAT_TYPE=')+12,1)
521 from fnd_sessions fnd,
522 PAY_ORG_PAYMENT_METHODS org,
523 pay_pre_payments ppp,
524 pay_payroll_actions ppa,
525 pay_assignment_actions paa
526 where paa.payroll_action_id =
527 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
528 and ppa.payroll_action_id = paa.payroll_action_id
529 and ppp.pre_payment_id = paa.pre_payment_id
530 and ppp.org_payment_method_id = org.org_payment_method_id
531 and fnd.session_id = userenv('sessionid');
532 --
533 CURSOR m_bacs_header IS
534 select 'BACS_TAPE_BACS_USER_NUMBER=P',
535 org.pmeth_information1,
536 'ORG_PAY_METHOD_ID=C',
537 min(ppp.org_payment_method_id),
538 'TRANSFER_TODAYS_DATE=P',
539 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),
540 'TRANSFER_BACS_PROCESS_DATE=P',
541 to_char(ppa.overriding_dd_date, 'YYYY/MM/DD HH24:MI:SS'),
542 'TRANSFER_BACS_PROCESS_DATE2=P',
543 to_char(ppa.overriding_dd_date, 'YYDDD'),
544 'TRANSFER_CURRENCY_CODE=P',
545 org.currency_code,
546 'ORG_PAY_METHOD_NAME=P',
547 min(org.org_payment_method_name)
548 from fnd_sessions fnd,
549 pay_org_payment_methods org,
550 pay_pre_payments ppp,
551 pay_payroll_actions ppa,
552 pay_assignment_actions paa
553 where paa.payroll_action_id =
554 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
555 and ppa.payroll_action_id = paa.payroll_action_id
556 and ppp.pre_payment_id = paa.pre_payment_id
557 and org.org_payment_method_id = ppp.org_payment_method_id
558 and fnd.session_id = userenv('sessionid')
559 group by org.pmeth_information1,fnd.effective_date,
560 sysdate, ppa.overriding_dd_date, org.currency_code
561 order by 2, 12;
562 --
563 CURSOR m_bacs_payment_method_id IS
564 SELECT /*+ ORDERED */ DISTINCT
565 'TRANSFER_ORG_PAY_METHOD=P',
566 ppp.org_payment_method_id,
567 'ORG_PAY_METHOD_ID=C',
568 ppp.org_payment_method_id,
569 'TRANSFER_PER_PROCESS_DATE=P',
570 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
571 to_date(pay_magtape_generic.get_parameter_value(
572 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
573 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
574 to_date(pay_magtape_generic.get_parameter_value(
575 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
576 'TRANSFER_PER_PROCESS_DATE2=P',
577 to_char(greatest(to_date(prrv.result_value, 'YYYY/MM/DD HH24:MI:SS'),
578 to_date(pay_magtape_generic.get_parameter_value(
579 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYDDD'),
580 'TRANSFER_BUREAU_NO=P',
581 org.pmeth_information3,
582 'ORG_PAY_METHOD_NAME=P',
583 org.org_payment_method_name
584 from pay_assignment_actions paa,
588 pay_element_types pet,
585 pay_pre_payments ppp,
586 pay_org_payment_methods org,
587 pay_run_results prr,
589 pay_run_result_values prrv,
590 pay_input_values piv
591 where org.currency_code =
592 pay_magtape_generic.get_parameter_value('TRANSFER_CURRENCY_CODE')
593 and paa.payroll_action_id =
594 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
595 and ppp.pre_payment_id = paa.pre_payment_id
596 and ppp.org_payment_method_id = org.org_payment_method_id
597 and org.pmeth_information1 =
598 pay_magtape_generic.get_parameter_value('BACS_TAPE_BACS_USER_NUMBER')
599 and prr.assignment_action_id =
600 (select max(locked_action_id)
601 + decode(org.org_payment_method_id,0,0,0)
602 from pay_action_interlocks pai
603 where ppp.assignment_action_id=pai.locking_action_id
604 )
605 and pet.element_type_id = prr.element_type_id
606 and pet.element_name = 'BACS Process Date'
607 and prrv.run_result_id = prr.run_result_id
608 + decode(pet.element_type_id,0,0,0)
609 and piv.input_value_id = prrv.input_value_id
610 and piv.name = 'Process Date'
611 union
612 select DISTINCT
613 'TRANSFER_ORG_PAY_METHOD=P',
614 ppp.org_payment_method_id,
615 'ORG_PAY_METHOD_ID=C',
616 ppp.org_payment_method_id,
617 'TRANSFER_PER_PROCESS_DATE=P',
618 to_char(greatest(ptp.default_dd_date,
619 to_date(pay_magtape_generic.get_parameter_value(
620 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
621 to_char(greatest(ptp.default_dd_date,
622 to_date(pay_magtape_generic.get_parameter_value(
623 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYYY/MM/DD HH24:MI:SS'),
624 'TRANSFER_PER_PROCESS_DATE2=P',
625 to_char(greatest(ptp.default_dd_date,
626 to_date(pay_magtape_generic.get_parameter_value(
627 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')), 'YYDDD'),
628 'TRANSFER_BUREAU_NO=P', org.pmeth_information3,
629 'ORG_PAY_METHOD_NAME=P', org.org_payment_method_name
630 from per_time_periods ptp,
631 pay_payroll_actions ppa,
632 pay_assignment_actions paa2,
633 pay_org_payment_methods org,
634 pay_pre_payments ppp,
635 pay_assignment_actions paa
636 where org.currency_code =
637 pay_magtape_generic.get_parameter_value('TRANSFER_CURRENCY_CODE')
638 and paa.payroll_action_id =
639 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
640 and ppp.pre_payment_id = paa.pre_payment_id
641 and ppp.org_payment_method_id = org.org_payment_method_id
642 and org.pmeth_information1 =
643 pay_magtape_generic.get_parameter_value('BACS_TAPE_BACS_USER_NUMBER')
644 and paa2.assignment_action_id =
645 (select max(locked_action_id)
646 + decode(org.org_payment_method_id,0,0,0)
647 from pay_action_interlocks pai
648 where ppp.assignment_action_id=pai.locking_action_id
649 )
650 and not exists (select 1
651 from pay_element_types pet,
652 pay_run_results prr
653 where prr.assignment_action_id = paa2.assignment_action_id
654 and pet.element_type_id = prr.element_type_id
655 and pet.element_name = 'BACS Process Date')
656 and paa2.payroll_action_id = ppa.payroll_action_id
657 and ppa.time_period_id = ptp.time_period_id
658 order by 2, 7;
659 --
660 CURSOR m_bacs_payment IS
661 select
662 'TRANSFER_VALUE=P',
663 ROUND(oppp.value,2) * 100,/* BUG:6689691*/
664 'TRANSFER_ASSIGN_NO=P',
665 opa.assignment_number,
666 'PER_PAY_METHOD_ID=C',
667 oppp.personal_payment_method_id,
668 'TRANSFER_ASG_ACTION_ID=P',
669 opaa.assignment_action_id
670 from
671 pay_external_accounts opea,
672 pay_personal_payment_methods oppm,
673 per_assignments opa,
674 pay_pre_payments oppp,
675 pay_assignment_actions opaa
676 where opaa.payroll_action_id =
677 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
678 and opa.assignment_id = opaa.assignment_id
679 and opaa.pre_payment_id = oppp.pre_payment_id
680 and oppp.org_payment_method_id =
681 pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD')
682 and oppm.personal_payment_method_id = oppp.personal_payment_method_id
683 and opea.external_account_id = oppm.external_account_id
684 and exists (
685 select 1
686 from pay_input_values piv,
687 pay_run_result_values prrv,
688 pay_element_types pet,
689 pay_run_results prr
690 where prr.assignment_action_id =
691 (select max(pai.locked_action_id)
692 from pay_action_interlocks pai
693 where oppp.assignment_action_id=pai.locking_action_id
694 )
695 and prrv.run_result_id = prr.run_result_id
696 + decode(pet.element_type_id,0,0,0)
700 and piv.name = 'Process Date'
697 and pet.element_type_id = prr.element_type_id
698 and pet.element_name = 'BACS Process Date'
699 and piv.input_value_id = prrv.input_value_id
701 and (to_date(pay_magtape_generic.get_parameter_value(
702 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
703 = to_date(prrv.result_value,'YYYY/MM/DD HH24:MI:SS')
704 OR (
705 to_date(prrv.result_value,'YYYY/MM/DD HH24:MI:SS') <
706 to_date(pay_magtape_generic.get_parameter_value(
707 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
708 AND to_date(pay_magtape_generic.get_parameter_value(
709 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS') =
710 to_date(pay_magtape_generic.get_parameter_value(
711 'TRANSFER_BACS_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
712 )
713 )
714 union
715 select 1
716 from
717 per_time_periods ptp,
718 pay_payroll_actions ppa,
719 pay_assignment_actions paa
720 where paa.assignment_action_id =
721 (select max(pai.locked_action_id)
722 from pay_action_interlocks pai
723 where oppp.assignment_action_id=pai.locking_action_id
724 )
725 and paa.payroll_action_id = ppa.payroll_action_id
726 and ppa.payroll_id = ptp.payroll_id
727 and ppa.time_period_id = ptp.time_period_id
728 and (ptp.default_dd_date =
729 to_date(pay_magtape_generic.get_parameter_value(
730 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
731 or (pay_magtape_generic.get_parameter_value(
732 'TRANSFER_PER_PROCESS_DATE') =
733 pay_magtape_generic.get_parameter_value(
734 'TRANSFER_BACS_PROCESS_DATE')
735 and to_date(pay_magtape_generic.get_parameter_value(
736 'TRANSFER_PER_PROCESS_DATE'),'YYYY/MM/DD HH24:MI:SS')
737 > ptp.default_dd_date))
738 and not exists (select 1
739 from pay_element_types pet,
740 pay_run_results prr
741 where prr.assignment_action_id = paa.assignment_action_id
742 and pet.element_type_id = prr.element_type_id
743 and pet.element_name = 'BACS Process Date')
744 )
745 order by decode(pay_magtape_generic.get_parameter_value('SET_ORDER_BY'),
746 'A', opa.assignment_number,
747 'S', opea.segment3||opea.segment5,
748 'E', opea.segment5, null);
749 --
750 --
751 level_cnt number;
752 --
753 PROCEDURE new_formula;
754 --
755 FUNCTION get_process_date(p_assignment_action_id in number,
756 p_entry_date in date)
757 return date;
761 --
758 FUNCTION validate_process_date(p_assignment_action_id in number,
759 p_process_date in date)
760 return date;
762 END pay_gb_bacs_tape;