1 PACKAGE pay_us_nacha_tape AS
2 /* $Header: pytapnac.pkh 120.0.12000000.1 2007/01/18 01:44:51 appldev noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_nacha_tape
21
22 Description : This package holds building blocks used in the generation
23 of nacha Tape.
24
25 Uses : hr_utility
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ----- ------- -----------
31 JUL-23-1993 RMAMGAIN 1.0 Created with following proc.
32 . run_formula
33 15-May-95 RFine 40.3 Changed 'show errors' from
34 package body to package.
35 28-JUN-1995 NBRISTOW 40.4 Package now uses PL/SQL
36 tables to interface with
37 the C process.
38 17-Apr-1996 ALLEE Added more global variables
39 to support padding functionality
40 24-JUL-1996 allee Fixed org_flex cursor to account
41 for consolidation sets.
42 Added gloabl variables.
43 01-MAR-1998 EKIM 40.10 Modified CURSOR csr_org_flex_info
44 17-MAY-2000 DSCULLY Added support for child care addenda
45 records. Removed cursors
46 csr_org_pay_method and
47 csr_leg_comp in favor of one cursor,
48 csr_nacha_batch which does both
49 queries.
50 17-JUN-2000 DSCULLY Added g_addenda_count
51
52 ****************************************************************************
53 Due to extensive changes in the 11.0 version, and little difference between
54 the previous 11.0 version and the 11.5 version, we are taking the modified
55 11.0 version and redoing the changes made in earlier revisions of the 11.5
56 version
57 ****************************************************************************
58
59 17-JUN-2000 DSCULLY 115.2 Updated the 11.0 version into the 11.5
60 codetree
61 15-AUG-2000 ahanda 115.4 Changed the Format mask for g_time
62 from HHMMto HHMI
63 05-DEC-2000 ahanda 115.5 Added the RULE Hint to the cursor
64 csr_assignments. Also changed cursor
65 to use exists clause.
66 09-JUL-2001 MESHAH 115.6 Direct join in csr_org_flex_info
67 between pay_payroll_actions and
68 pay_payrolls_f.
69 19-JUL-2001 MESHAH 115.7 1357404 changed cursor csr_assignments to
70 include rowid as a parameter for the
71 cursor, as a select value in the
72 query and in the where clause.
73 Also declared 3 new global variables.
74 27-JUL-2001 MESHAH 115.8 new parameter g_test_file.
75 31-AUG-2001 MESHAH 115.9 new parameter g_reset_greid.
76 20-DEC-2002 MESHAH 115.10 2714155 changed csr_nacha_batch to remove a
77 cartesian join for 1159.
78 18-FEB-2004 kvsankar 115.11 3331019 Added cursor csr_assignments_no_rule,
79 equivalent to cursor csr_assignments,
80 but doesnt use RULE hint.
81 (as part of 10G certification).
82 06-MAY-2004 svmadira 115.12 3587226 Commented the sub query from
83 csr_assignments cursor for improving
84 performance of the process.
85 06-JUL-2004 ahanda 115.13 Added third_party flag to cursor
86 csr_org_flex_info and changed
87 business_group_id initialize from
88 '0' to null.
89
90 *****************************************************************************/
91
92 -- Global Variable for the Package
93
94 TYPE tt_used_results IS TABLE OF NUMBER
95 INDEX BY BINARY_INTEGER;
96
97 g_used_results_tab tt_used_results;
98 g_effective_date date := null;
99 g_business_group_id number;
100 g_payroll_action_id number := 0;
101 g_assignment_id number := 0;
102 g_assignment_action_id number := 0;
103 g_personal_payment_method_id number := 0;
104 g_org_payment_method_id number := 0; /* Tape Level Id*/
105 g_csr_org_pay_meth_id number := 0; /* Assignment Level Id */
106 g_csr_org_pay_third_party varchar2(1) := null;
107 g_pad_count number := 0;
108 g_temp_count number := 0;
109 g_count number := 0;
110 g_addenda_count number := 0;
111 g_hash number := 0;
112 g_amount number := 0;
113 g_batch_number number := 0;
114 g_legal_company_id number := 0;
115 g_addenda_write varchar2(1) := 'N';
116 g_batch_control_write varchar2(1) := 'N';
117 g_file_id_modifier varchar2(1) := null;
118 g_company_entry_desc varchar2(10) := null;
119 g_descriptive_date varchar2(6) := null;
120 g_file_header varchar2(9) := null;
121 g_batch_header varchar2(9) := null;
122 g_org_pay_dummy varchar2(9) := null;
123 g_entry_detail varchar2(9) := null;
124 g_addenda varchar2(9) := null;
125 g_org_pay_entry_detail varchar2(9) := null;
126 g_batch_control varchar2(9) := null;
127 g_file_control varchar2(9) := null;
128 g_nacha_dest_code varchar2(8) := null;
129 g_padding varchar2(8) := null;
130 g_direct_dep_date varchar2(6) := null;
131 g_legislative_parameters varchar2(240) := null;
132 g_date varchar2(06) := TO_CHAR(SYSDATE,'YYMMDD');
133 g_time varchar2(04) := TO_CHAR(SYSDATE,'HHMI');
134 g_nacha_balance_flag varchar2(1) := null;
135
136 g_overflow_flag varchar2(1) := 'N';
137 g_overflow_amount number := 0;
138 g_overflow_batch varchar2(1) := 'N';
139 g_rowid rowid := null;
140
141 g_test_file varchar2(1) := 'N';
142 g_reset_greid number := 0;
143
144
145
146 /******************************************************************
147 NAME
148 run_formula
149 DESCRIPTION
150 Setup contexts and parameter for the formula. Setup next formula to call
151 so that Magtape ('C' process) could call appropriate formula
152 NOTES
153 C process 'pymx' uses parameters and contexts set by this procedure
154 to setup the interface for the formula and to call the formula.
155 *******************************************************************/
156 PROCEDURE run_formula;
157
158
159 --
160 -- Cursor to get the Organisation Payment method Flex information
161 -- IF org_payment_method_id is not supplied
162 --
163
164
165 CURSOR csr_org_flex_info (p_business_group_id number,
166 p_payroll_action_id number ) IS
167 select opm.ORG_PAYMENT_METHOD_ID,
168 decode(nvl(to_char(opm.defined_balance_id),'Y'),'Y','Y','N')
169 from pay_org_payment_methods_f opm,
170 pay_payment_types pt,
171 pay_org_pay_method_usages_f opmu,
172 pay_payrolls_f pay,
173 pay_payroll_actions ppa
174 where ppa.PAYROLL_ACTION_ID = p_payroll_action_id
175 and ppa.payroll_id = pay.payroll_id
176 and ppa.CONSOLIDATION_SET_ID = pay.CONSOLIDATION_SET_ID
177 and pay.PAYROLL_ID = opmu.PAYROLL_ID
178 and opmu.ORG_PAYMENT_METHOD_ID = opm.ORG_PAYMENT_METHOD_ID
179 and upper(pt.PAYMENT_TYPE_NAME) = 'NACHA'
180 and pt.PAYMENT_TYPE_ID = opm.PAYMENT_TYPE_ID
181 and g_effective_date between opm.EFFECTIVE_START_DATE and
182 opm.EFFECTIVE_END_DATE
183 and opm.BUSINESS_GROUP_ID + 0 = p_business_group_id ;
184
185
186 -- Cursor to get batch information
187 -- There is one batch for each combination of GRE and Org Payment Method
188 CURSOR csr_nacha_batch (p_business_group_id number,
189 p_payroll_action_id number) IS
190
191 select distinct
192 prepay.org_payment_method_id,
193 decode(nvl(to_char(opm.defined_balance_id),'Y'),'Y','Y','N'),
194 hrorgu.organization_id,
195 opm.pmeth_information6
196 from
197 pay_pre_payments prepay,
198 pay_org_payment_methods_f opm,
199 hr_organization_units hrorgu,
200 hr_organization_information hroinf
201 where
202 opm.org_payment_method_id = prepay.org_payment_method_id
203 and g_effective_date between opm.effective_start_date
204 and opm.effective_end_date
205 and hrorgu.business_group_id = p_business_group_id
206 and opm.business_group_id = p_business_group_id
207 and hrorgu.organization_id = hroinf.organization_id
208 and hroinf.org_information_context = 'CLASS'
209 and hroinf.org_information1 = 'HR_LEGAL'
210 and hroinf.org_information2 = 'Y'
211 and EXISTS
212 ( select 1
213 from per_assignments_f perasg,
214 pay_assignment_actions pyaact
215 where pyaact.payroll_action_id = p_payroll_action_id
216 and pyaact.tax_unit_id = hrorgu.organization_id
217 and perasg.assignment_id = pyaact.assignment_id
218 and g_effective_date between perasg.effective_start_date
219 and perasg.effective_end_date
220 and pyaact.pre_payment_id = prepay.pre_payment_id);
221
222
223 -- gets all prepayments of a specific org payment method whose assignments
224 -- are in a specific GRE
225 --
226 CURSOR csr_assignments(p_legal_company_id number,
227 p_payroll_action_id number,
228 p_org_payment_method_id number,
229 p_rowid rowid) IS
230 select /*+ RULE */
231 paa.assignment_id,
232 paa.assignment_action_id,
233 ppp.value,
234 ppp.personal_payment_method_id,
235 ppp.pre_payment_id,
236 ppp.rowid
237 from pay_assignment_actions paa,
238 pay_pre_payments ppp
239 where ppp.org_payment_method_id+0 = p_org_payment_method_id -- suppressing index for performance improvement Bug 3587226
240 and ppp.PRE_PAYMENT_ID = paa.PRE_PAYMENT_ID
241 and paa.PAYROLL_ACTION_ID = p_payroll_action_id
242 and paa.tax_unit_id = p_legal_company_id -- ADDED for GRE join as the subquery is removed Bug 3587226
243 and ((ppp.rowid >= p_rowid and p_rowid is not null )
244 or
245 (p_rowid is null) )
246 order by ppp.rowid;
247 -- Commented for performance improvement Bug 3587226
248 /* and exists
249 ( select 'x'
250 from per_assignments_f paf,
251 hr_soft_coding_keyflex hsck
252 where paf.assignment_id = paa.assignment_id
253 and g_effective_date between paf.effective_start_date
254 and paf.effective_end_date
255 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
256 and hsck.segment1 = to_char(p_legal_company_id)
257 )
258 order by ppp.rowid; */
259 -- Commented for performance improvement Bug 3587226 upto here
260
261 -- Bug 3331019
262 CURSOR csr_assignments_no_rule (p_legal_company_id number,
263 p_payroll_action_id number,
264 p_org_payment_method_id number,
265 p_rowid rowid) IS
266 select
267 paa.assignment_id,
268 paa.assignment_action_id,
269 ppp.value,
270 ppp.personal_payment_method_id,
271 ppp.pre_payment_id,
272 ppp.rowid
273 from pay_assignment_actions paa,
274 pay_pre_payments ppp
275 where ppp.org_payment_method_id = p_org_payment_method_id
276 and ppp.PRE_PAYMENT_ID = paa.PRE_PAYMENT_ID
277 and paa.PAYROLL_ACTION_ID = p_payroll_action_id
278 and paa.tax_unit_id = p_legal_company_id -- ADDED for GRE join as the subquery is removed Bug 3587226
279 and ((ppp.rowid >= p_rowid and p_rowid is not null )
280 or
281 (p_rowid is null) )
282 order by ppp.rowid;
283 -- Commented for performance improvement Bug 3587226
284 /*
285 and exists
286 ( select 'x'
287 from per_assignments_f paf,
288 hr_soft_coding_keyflex hsck
289 where paf.assignment_id = paa.assignment_id
290 and g_effective_date between paf.effective_start_date
291 and paf.effective_end_date
292 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
293 and hsck.segment1 = to_char(p_legal_company_id)
294 )
295 order by ppp.rowid;
296 */
297 -- Commented for performance improvement Bug 3587226 upto here
298 --
299 --
300 END pay_us_nacha_tape;
[Home] [Help]