1 PACKAGE pay_us_nacha_tape AUTHID CURRENT_USER AS
2 /* $Header: pytapnac.pkh 120.0.12010000.4 2009/11/20 10:30:38 mikarthi ship $ */
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 05-Aug-2009 kagangul 115.14 Added function f_get_batch_transact_ident
90 for supporting the EFT reconciliation.
91 08-Aug-2009 mikarthi 115.15 Modifications for Nacha IAT enhancement
92 *****************************************************************************/
93
94 -- Global Variable for the Package
95
96 TYPE tt_used_results IS TABLE OF NUMBER
97 INDEX BY BINARY_INTEGER;
98
99 g_used_results_tab tt_used_results;
100 g_effective_date date := null;
101 g_business_group_id number;
102 g_payroll_action_id number := 0;
103 g_assignment_id number := 0;
104 g_assignment_action_id number := 0;
105 g_personal_payment_method_id number := 0;
106 g_org_payment_method_id number := 0; /* Tape Level Id*/
107 g_csr_org_pay_meth_id number := 0; /* Assignment Level Id */
108 g_csr_org_pay_third_party varchar2(1) := null;
109 g_pad_count number := 0;
110 g_temp_count number := 0;
111 g_count number := 0;
112 g_addenda_count number := 0;
113 g_hash number := 0;
114 g_amount number := 0;
115 g_batch_number number := 0;
116 g_legal_company_id number := 0;
117 g_addenda_write varchar2(1) := 'N';
118 g_batch_control_write varchar2(1) := 'N';
119 g_file_id_modifier varchar2(1) := null;
120 g_company_entry_desc varchar2(10) := null;
121 g_descriptive_date varchar2(6) := null;
122 g_file_header varchar2(9) := null;
123 g_batch_header varchar2(9) := null;
124 g_org_pay_dummy varchar2(9) := null;
125 g_entry_detail varchar2(9) := null;
126 g_addenda varchar2(9) := null;
127 g_org_pay_entry_detail varchar2(9) := null;
128 g_batch_control varchar2(9) := null;
129 g_file_control varchar2(9) := null;
130 g_nacha_dest_code varchar2(8) := null;
131 g_padding varchar2(8) := null;
132 g_direct_dep_date varchar2(6) := null;
133 g_legislative_parameters varchar2(240) := null;
134 g_date varchar2(06) := TO_CHAR(SYSDATE,'YYMMDD');
135 g_time varchar2(04) := TO_CHAR(SYSDATE,'HHMI');
136 g_nacha_balance_flag varchar2(1) := null;
137
138 g_overflow_flag varchar2(1) := 'N';
139 g_overflow_amount number := 0;
140 g_overflow_batch varchar2(1) := 'N';
141 g_rowid rowid := null;
142
143 g_test_file varchar2(1) := 'N';
144 g_reset_greid number := 0;
145
146 --IAT Enhancement
147 g_foreign_transact varchar2(1) := null;
148 g_payroll_id NUMBER;
149
150
151 /******************************************************************
152 NAME
153 run_formula
154 DESCRIPTION
155 Setup contexts and parameter for the formula. Setup next formula to call
156 so that Magtape ('C' process) could call appropriate formula
157 NOTES
158 C process 'pymx' uses parameters and contexts set by this procedure
159 to setup the interface for the formula and to call the formula.
160 *******************************************************************/
161 PROCEDURE run_formula;
162
163
164 --
165 -- Cursor to get the Organisation Payment method Flex information
166 -- IF org_payment_method_id is not supplied
167 --
168
169
170 CURSOR csr_org_flex_info (p_business_group_id number,
171 p_payroll_action_id number ) IS
172 select opm.ORG_PAYMENT_METHOD_ID,
173 decode(nvl(to_char(opm.defined_balance_id),'Y'),'Y','Y','N')
174 from pay_org_payment_methods_f opm,
175 pay_payment_types pt,
176 pay_org_pay_method_usages_f opmu,
177 pay_payrolls_f pay,
178 pay_payroll_actions ppa
179 where ppa.PAYROLL_ACTION_ID = p_payroll_action_id
180 and ppa.payroll_id = pay.payroll_id
181 and ppa.CONSOLIDATION_SET_ID = pay.CONSOLIDATION_SET_ID
182 and pay.PAYROLL_ID = opmu.PAYROLL_ID
183 and opmu.ORG_PAYMENT_METHOD_ID = opm.ORG_PAYMENT_METHOD_ID
184 and upper(pt.PAYMENT_TYPE_NAME) = 'NACHA'
185 and pt.PAYMENT_TYPE_ID = opm.PAYMENT_TYPE_ID
186 and g_effective_date between opm.EFFECTIVE_START_DATE and
187 opm.EFFECTIVE_END_DATE
188 and opm.BUSINESS_GROUP_ID + 0 = p_business_group_id ;
189
190
191 -- Cursor to get batch information
192 -- There is one batch for each combination of GRE and Org Payment Method
193 CURSOR csr_nacha_batch (p_business_group_id number,
194 p_payroll_action_id number) IS
195
196 select distinct
197 prepay.org_payment_method_id,
198 decode(nvl(to_char(opm.defined_balance_id),'Y'),'Y','Y','N'),
199 hrorgu.organization_id,
200 opm.pmeth_information6
201 from
202 pay_pre_payments prepay,
203 pay_org_payment_methods_f opm,
204 hr_organization_units hrorgu,
205 hr_organization_information hroinf
206 where
207 opm.org_payment_method_id = prepay.org_payment_method_id
208 and g_effective_date between opm.effective_start_date
209 and opm.effective_end_date
210 and hrorgu.business_group_id = p_business_group_id
211 and opm.business_group_id = p_business_group_id
212 and hrorgu.organization_id = hroinf.organization_id
213 and hroinf.org_information_context = 'CLASS'
214 and hroinf.org_information1 = 'HR_LEGAL'
215 and hroinf.org_information2 = 'Y'
216 and EXISTS
217 ( select 1
218 from per_assignments_f perasg,
219 pay_assignment_actions pyaact
220 where pyaact.payroll_action_id = p_payroll_action_id
221 and pyaact.tax_unit_id = hrorgu.organization_id
222 and perasg.assignment_id = pyaact.assignment_id
223 and g_effective_date between perasg.effective_start_date
224 and perasg.effective_end_date
225 and pyaact.pre_payment_id = prepay.pre_payment_id);
226
227
228 -- gets all prepayments of a specific org payment method whose assignments
229 -- are in a specific GRE
230 --
231 CURSOR csr_assignments(p_legal_company_id number,
232 p_payroll_action_id number,
233 p_org_payment_method_id number,
234 p_rowid rowid) IS
235 select /*+ RULE */
236 paa.assignment_id,
237 paa.assignment_action_id,
238 ppp.value,
239 ppp.personal_payment_method_id,
240 ppp.pre_payment_id,
241 ppp.rowid
242 from pay_assignment_actions paa,
243 pay_pre_payments ppp
244 where ppp.org_payment_method_id+0 = p_org_payment_method_id -- suppressing index for performance improvement Bug 3587226
245 and ppp.PRE_PAYMENT_ID = paa.PRE_PAYMENT_ID
246 and paa.PAYROLL_ACTION_ID = p_payroll_action_id
247 and paa.tax_unit_id = p_legal_company_id -- ADDED for GRE join as the subquery is removed Bug 3587226
248 and ((ppp.rowid >= p_rowid and p_rowid is not null )
249 or
250 (p_rowid is null) )
251 order by ppp.rowid;
252 -- Commented for performance improvement Bug 3587226
253 /* and exists
254 ( select 'x'
255 from per_assignments_f paf,
256 hr_soft_coding_keyflex hsck
257 where paf.assignment_id = paa.assignment_id
258 and g_effective_date between paf.effective_start_date
259 and paf.effective_end_date
260 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
261 and hsck.segment1 = to_char(p_legal_company_id)
262 )
263 order by ppp.rowid; */
264 -- Commented for performance improvement Bug 3587226 upto here
265
266 -- Bug 3331019
267 CURSOR csr_assignments_no_rule (p_legal_company_id number,
268 p_payroll_action_id number,
269 p_org_payment_method_id number,
270 p_rowid rowid) IS
271 select
272 paa.assignment_id,
273 paa.assignment_action_id,
274 ppp.value,
275 ppp.personal_payment_method_id,
276 ppp.pre_payment_id,
277 ppp.rowid
278 from pay_assignment_actions paa,
279 pay_pre_payments ppp
280 where ppp.org_payment_method_id = p_org_payment_method_id
281 and ppp.PRE_PAYMENT_ID = paa.PRE_PAYMENT_ID
282 and paa.PAYROLL_ACTION_ID = p_payroll_action_id
283 and paa.tax_unit_id = p_legal_company_id -- ADDED for GRE join as the subquery is removed Bug 3587226
284 and ((ppp.rowid >= p_rowid and p_rowid is not null )
285 or
286 (p_rowid is null) )
287 order by ppp.rowid;
288 -- Commented for performance improvement Bug 3587226
289 /*
290 and exists
291 ( select 'x'
292 from per_assignments_f paf,
293 hr_soft_coding_keyflex hsck
294 where paf.assignment_id = paa.assignment_id
295 and g_effective_date between paf.effective_start_date
296 and paf.effective_end_date
297 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
298 and hsck.segment1 = to_char(p_legal_company_id)
299 )
300 order by ppp.rowid;
301 */
302 -- Commented for performance improvement Bug 3587226 upto here
303 --
304 --
305 /* Bug 5098064 : Added for supporting EFT reconciliation */
306 FUNCTION f_get_batch_transact_ident(p_effective_date DATE,
307 p_identifier_name VARCHAR2,
308 p_payroll_action_id NUMBER,
309 p_payment_type_id NUMBER,
310 p_org_payment_method_id NUMBER,
311 p_personal_payment_method_id NUMBER,
312 p_assignment_action_id NUMBER,
313 p_pre_payment_id NUMBER,
314 p_delimiter_string VARCHAR2)
315 RETURN VARCHAR2;
316 END pay_us_nacha_tape;
[Home] [Help]