[Home] [Help]
1: PACKAGE BODY pyusdar_pkg AS
2: /* $Header: pyusdar.pkb 115.40 2004/06/08 13:21:41 rmonge ship $ */
3: /*
4: ******************************************************************
5: * *
24: Change List
25: -----------
26: Date Name Vers Description
27: ---------- ---------- ------ -----------------------------------
28: 08-JUN-04 rmonge 115.40 Changed the name back to pyusdar_pkg for
29: compatibility reasons in case customers have code
30: that reference the old name (pyusdar_pkg).
31: All new changes need to be done to the package
32: pay_us_deposit_advice_pkg delivered in the file
26: Date Name Vers Description
27: ---------- ---------- ------ -----------------------------------
28: 08-JUN-04 rmonge 115.40 Changed the name back to pyusdar_pkg for
29: compatibility reasons in case customers have code
30: that reference the old name (pyusdar_pkg).
31: All new changes need to be done to the package
32: pay_us_deposit_advice_pkg delivered in the file
33: payuslivearchive.pkh/pkb
34: DO NOT ADD CHANGES TO THIS FILE.
109: 02-JAN-2001 ahanda 115.13 Added RULE Hint.
110: 31-OCT-2000 tclewis 115.11 Modifed the c_actions_zero_pay
111: cursor changing the following code.
112: and ppa_mag_pmts.payroll_id =
113: NVL(pyusdar_pkg.get_parameter('PAYROLL_ID',
114: ppa_dar.legislative_parameters),
115: ppa_mag_pmts.payroll_id)
116: To:
117: and (ppa_mag_pmts.payroll_id =
114: ppa_dar.legislative_parameters),
115: ppa_mag_pmts.payroll_id)
116: To:
117: and (ppa_mag_pmts.payroll_id =
118: pyusdar_pkg.get_parameter('PAYROLL_ID',
119: ppa_dar.legislative_parameters)
120: or pyusdar_pkg.get_parameter('PAYROLL_ID',
121: ppa_dar.legislative_parameters)
122: is null)
116: To:
117: and (ppa_mag_pmts.payroll_id =
118: pyusdar_pkg.get_parameter('PAYROLL_ID',
119: ppa_dar.legislative_parameters)
120: or pyusdar_pkg.get_parameter('PAYROLL_ID',
121: ppa_dar.legislative_parameters)
122: is null)
123:
124: 31-AUG-2000 tclewis 115.8 Added a second cursor to the Action_creation
150: into leg_param
151: from pay_payroll_actions ppa
152: where ppa.payroll_action_id = pactid;
153: --
154: l_payroll_id := pyusdar_pkg.get_parameter('PAYROLL_ID', leg_param);
155: --
156: --Database Version --Bug 3331028
157:
158: if (nvl(hr_general2.get_oracle_db_version, 0) < 10.0) then
171: pay_payroll_actions pa1
172: where pa1.payroll_action_id = :payroll_action_id
173: and pa2.action_type in (''R'',''Q'')
174: and pa2.consolidation_set_id =
175: pyusdar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
176: pa1.legislative_parameters)
177: and pa2.payroll_id =
178: pyusdar_pkg.get_parameter(''PAYROLL_ID'',
179: pa1.legislative_parameters)
174: and pa2.consolidation_set_id =
175: pyusdar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
176: pa1.legislative_parameters)
177: and pa2.payroll_id =
178: pyusdar_pkg.get_parameter(''PAYROLL_ID'',
179: pa1.legislative_parameters)
180: and pa2.effective_date between
181: pa1.start_date and pa1.effective_date
182: and act.payroll_action_id = pa2.payroll_action_id
197: pay_payroll_actions pa2,
198: pay_payroll_actions pa1
199: where pa1.payroll_action_id = :payroll_action_id
200: and pa2.consolidation_set_id =
201: pyusdar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
202: pa1.legislative_parameters)
203: and pa2.effective_date between
204: pa1.start_date and pa1.effective_date
205: and act.payroll_action_id = pa2.payroll_action_id
243: pay_payroll_actions ppa_mag_pmts,
244: pay_payrolls_f pay --Bug 3343621
245: where ( ppa_dar.payroll_action_id = pactid
246: and ppa_mag_pmts.consolidation_set_id +0
247: = pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
248: ppa_dar.legislative_parameters)
249: and ppa_mag_pmts.effective_date between ppa_dar.start_date
250: and ppa_dar.effective_date
251: and act.payroll_action_id = ppa_mag_pmts.payroll_action_id
264: and paf2.payroll_id + 0 = paf1.payroll_id + 0
265: and pos.period_of_service_id = paf1.period_of_service_id
266: and pos.person_id between stperson and endperson
267: and (paf1.payroll_id =
268: pyusdar_pkg.get_parameter('PAYROLL_ID',
269: ppa_dar.legislative_parameters)
270: or pyusdar_pkg.get_parameter('PAYROLL_ID',
271: ppa_dar.legislative_parameters)
272: is null)
266: and pos.person_id between stperson and endperson
267: and (paf1.payroll_id =
268: pyusdar_pkg.get_parameter('PAYROLL_ID',
269: ppa_dar.legislative_parameters)
270: or pyusdar_pkg.get_parameter('PAYROLL_ID',
271: ppa_dar.legislative_parameters)
272: is null)
273: -- No run results.
274: AND NOT EXISTS (SELECT ' '
380: pay_payroll_actions ppa_mag,
381: pay_org_payment_methods_f popm --Bug 3009643
382: where ppa_dar.payroll_action_id = :pactid
383: and ppa_mag.consolidation_set_id +0 =
384: pyusdar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
385: ppa_dar.legislative_parameters)
386: and ppa_mag.effective_date between
387: ppa_dar.start_date and ppa_dar.effective_date
388: and act.payroll_action_id = ppa_mag.payroll_action_id
401: and paf2.payroll_id + 0 = paf1.payroll_id + 0
402: and pos.period_of_service_id = paf1.period_of_service_id
403: and pos.person_id between :stperson and :endperson
404: and (paf1.payroll_id =
405: pyusdar_pkg.get_parameter(''PAYROLL_ID'',
406: ppa_dar.legislative_parameters)
407: or pyusdar_pkg.get_parameter(''PAYROLL_ID'',
408: ppa_dar.legislative_parameters)
409: is null)
403: and pos.person_id between :stperson and :endperson
404: and (paf1.payroll_id =
405: pyusdar_pkg.get_parameter(''PAYROLL_ID'',
406: ppa_dar.legislative_parameters)
407: or pyusdar_pkg.get_parameter(''PAYROLL_ID'',
408: ppa_dar.legislative_parameters)
409: is null)
410: and not exists
411: (
610: where ppa_dar.payroll_action_id = pactid
611: and ppa_xfr.report_type = 'XFR_INTERFACE'
612: and ppa_dar.effective_date between ppa_xfr.start_date
613: and ppa_xfr.effective_date
614: and pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
615: ppa_dar.legislative_parameters) =
616: pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
617: ppa_xfr.legislative_parameters)
618: and paa_xfr.payroll_action_id = ppa_xfr.payroll_action_id
612: and ppa_dar.effective_date between ppa_xfr.start_date
613: and ppa_xfr.effective_date
614: and pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
615: ppa_dar.legislative_parameters) =
616: pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
617: ppa_xfr.legislative_parameters)
618: and paa_xfr.payroll_action_id = ppa_xfr.payroll_action_id
619: and pai_pre.locking_action_id = paa_xfr.assignment_action_id
620: /* PRE => RUN */
669: and ppa_mag.action_type = 'M'
670: and ppa_mag.effective_date between ppa_dar.start_date
671: and ppa_dar.effective_date
672: and ppa_mag.consolidation_set_id +0 =
673: pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
674: ppa_dar.legislative_parameters)
675: )
676: and paa_xfr.assignment_id = paf.assignment_id
677: and ppa_dar.effective_date between
676: and paa_xfr.assignment_id = paf.assignment_id
677: and ppa_dar.effective_date between
678: paf.effective_start_date and paf.effective_end_date
679: and (
680: paf.payroll_id = pyusdar_pkg.get_parameter('PAYROLL_ID',
681: ppa_dar.legislative_parameters)
682: or pyusdar_pkg.get_parameter('PAYROLL_ID',
683: ppa_dar.legislative_parameters) is null
684: )
678: paf.effective_start_date and paf.effective_end_date
679: and (
680: paf.payroll_id = pyusdar_pkg.get_parameter('PAYROLL_ID',
681: ppa_dar.legislative_parameters)
682: or pyusdar_pkg.get_parameter('PAYROLL_ID',
683: ppa_dar.legislative_parameters) is null
684: )
685:
686: and paf.person_id between stperson and endperson
733: pay_assignment_actions paa_run,
734: pay_payroll_actions ppa_xfr
735: , pay_payrolls_f pay -- #2264358
736: where ppa_dar.payroll_action_id = pactid
737: and pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
738: ppa_dar.legislative_parameters) =
739: pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
740: ppa_xfr.legislative_parameters)
741: and ppa_xfr.report_type = 'XFR_INTERFACE'
735: , pay_payrolls_f pay -- #2264358
736: where ppa_dar.payroll_action_id = pactid
737: and pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
738: ppa_dar.legislative_parameters) =
739: pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
740: ppa_xfr.legislative_parameters)
741: and ppa_xfr.report_type = 'XFR_INTERFACE'
742: and ppa_dar.effective_date between ppa_xfr.start_date
743: and ppa_xfr.effective_date
746: and paa_pre.assignment_action_id = pai_pre.locked_action_id
747: and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
748: and ppa_pre.action_type in ('P', 'U')
749: and ppa_pre.consolidation_set_id +0 =
750: pyusdar_pkg.get_parameter('CONSOLIDATION_SET_ID',
751: ppa_dar.legislative_parameters)
752: /* PRE => RUN */
753: and pai_run.locking_action_id = pai_pre.locked_action_id
754: and paa_run.assignment_action_id = pai_run.locked_action_id
784: and paa_xfr.assignment_id = paf.assignment_id
785: and ppa_dar.effective_date between
786: paf.effective_start_date and paf.effective_end_date
787: and (
788: paf.payroll_id = pyusdar_pkg.get_parameter('PAYROLL_ID',
789: ppa_dar.legislative_parameters)
790: or pyusdar_pkg.get_parameter('PAYROLL_ID',
791: ppa_dar.legislative_parameters) is null
792: )
786: paf.effective_start_date and paf.effective_end_date
787: and (
788: paf.payroll_id = pyusdar_pkg.get_parameter('PAYROLL_ID',
789: ppa_dar.legislative_parameters)
790: or pyusdar_pkg.get_parameter('PAYROLL_ID',
791: ppa_dar.legislative_parameters) is null
792: )
793:
794: and paf.person_id between stperson and endperson
849: into l_legislative_parameters
850: from pay_payroll_actions
851: where payroll_action_id = pactid;
852:
853: ass_set_id := pyusdar_pkg.get_parameter('ASG_SET_ID',
854: l_legislative_parameters);
855:
856: --hr_utility.trace_on(null, 'DAR');
857: hr_utility.set_location('procdar archive',1);
1018: return par_value;
1019: --
1020: end get_parameter;
1021: --
1022: end pyusdar_pkg;