[Home] [Help]
PACKAGE: APPS.PAY_US_NACHA_IAT_TAPE
Source
1 PACKAGE pay_us_nacha_iat_tape AUTHID CURRENT_USER AS
2 /* $Header: pytapnaciat.pkh 120.0.12010000.1 2009/08/20 20:07:58 mikarthi 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 IAT Tape.
24
25 Uses : hr_utility
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ----- ------- -----------
31 AUG-17-2009 MIKARTHI 1.0 Initial Version
32
33
34 *****************************************************************************/
35
36 -- Global Variable for the Package
37
38
39
40 /* *****************************************************************
41 NAME
42 run_formula
43 DESCRIPTION
44 Setup contexts and parameter for the formula. Setup next formula to call
45 so that Magtape ('C' process) could call appropriate formula
46 NOTES
47 C process 'pymx' uses parameters and contexts set by this procedure
48 to setup the interface for the formula and to call the formula.
49 *******************************************************************/
50 PROCEDURE run_formula (p_business_group_id NUMBER,
51 p_effective_date DATE,
52 p_direct_dep_date varchar2,
53 p_org_payment_method_id NUMBER,
54 p_csr_org_pay_third_party VARCHAR2,
55 p_file_id_modifier VARCHAR2,
56 p_test_file VARCHAR2,
57 p_payroll_id NUMBER);
58
59
60
61
62 g_payroll_action_id number := 0;
63 g_assignment_id number := 0;
64 g_assignment_action_id number := 0;
65 g_personal_payment_method_id number := 0;
66 g_org_payment_method_id number := 0; /* Tape Level Id*/
67 g_csr_org_pay_meth_id number := 0; /* Assignment Level Id */
68 g_csr_org_pay_third_party varchar2(1) := null;
69 g_pad_count number := 0;
70 g_temp_count number := 0;
71 g_count number := 0;
72 g_addenda_count number := 0;
73 g_hash number := 0;
74 g_amount number := 0;
75 g_batch_number number := 0;
76 g_legal_company_id number := 0;
77 g_addenda_write varchar2(1) := 'N';
78 g_batch_control_write varchar2(1) := 'N';
79 g_company_entry_desc varchar2(10) := null;
80 g_descriptive_date varchar2(6) := null;
81 g_file_header varchar2(9) := null;
82 g_batch_header varchar2(9) := null;
83 g_org_pay_dummy varchar2(9) := null;
84 g_entry_detail varchar2(9) := null;
85 g_addenda varchar2(9) := null;
86 g_org_pay_entry_detail varchar2(9) := null;
87 g_batch_control varchar2(9) := null;
88 g_file_control varchar2(9) := null;
89 g_nacha_dest_code varchar2(8) := null;
90 g_padding varchar2(8) := null;
91 g_legislative_parameters varchar2(240) := null;
92 g_date varchar2(06) := TO_CHAR(SYSDATE,'YYMMDD');
93 g_time varchar2(04) := TO_CHAR(SYSDATE,'HHMI');
94 g_nacha_balance_flag varchar2(1) := null;
95
96 g_overflow_flag varchar2(1) := 'N';
97 g_overflow_amount number := 0;
98 g_overflow_batch varchar2(1) := 'N';
99 g_rowid rowid := null;
100
101
102 g_reset_greid number := 0;
103
104 --IAT Enhancement
105 g_sec_code varchar2(3) := null;
106 g_org_sec_code varchar2(3) := null;
107 g_addenda_num NUMBER :=0;
108 g_foreign_transact varchar2(1) := null;
109
110 g_payroll_id PAY_PAYROLLS_F.PAYROLL_ID%TYPE;
111 g_organization_id per_all_assignments.ORGANIZATION_ID%TYPE;
112 g_full_name per_all_people_f.full_name%type;
113 g_org_name hr_organization_units.name%type;
114 g_street_address hr_locations.ADDRESS_LINE_1%type;
115 g_city hr_locations.TOWN_OR_CITY%type;
116 g_state hr_locations.REGION_1%type;
117 g_county hr_locations.REGION_2%type;
118 g_country hr_locations.COUNTRY%type;
119 g_postal_code hr_locations.POSTAL_CODE%type;
120 g_emp_num per_all_people_f.employee_number%type := null;
121 g_emp_adress per_addresses_v.ADDRESS_LINE1%type := null;
122 g_emp_city per_addresses_v.TOWN_OR_CITY%type := null;
123 g_emp_state per_addresses_v.REGION_2%type := null;
124 g_emp_county per_addresses_v.REGION_1%type := null;
125 g_emp_country per_addresses_v.D_COUNTRY%type := null;
126 g_emp_postal per_addresses_v.POSTAL_CODE%type := null;
127 g_first_exec VARCHAR2(1) := 'Y';
128 g_org_addenda VARCHAR2(1) := 'N';
129
130
131 --
132 -- Cursor to get the Organisation Payment method Flex information
133 -- IF org_payment_method_id is not supplied
134 --
135
136
137 CURSOR csr_org_flex_info (p_business_group_id number,
138 p_payroll_action_id number,
139 p_effective_date date ) IS
140 select opm.ORG_PAYMENT_METHOD_ID,
141 ppa.payroll_id
142 from pay_org_payment_methods_f opm,
143 pay_payment_types pt,
144 pay_org_pay_method_usages_f opmu,
145 pay_payrolls_f pay,
146 pay_payroll_actions ppa
147 where ppa.PAYROLL_ACTION_ID = p_payroll_action_id
148 and ppa.payroll_id = pay.payroll_id
149 and ppa.CONSOLIDATION_SET_ID = pay.CONSOLIDATION_SET_ID
150 and pay.PAYROLL_ID = opmu.PAYROLL_ID
151 and opmu.ORG_PAYMENT_METHOD_ID = opm.ORG_PAYMENT_METHOD_ID
152 and upper(pt.PAYMENT_TYPE_NAME) = 'NACHA'
153 and pt.PAYMENT_TYPE_ID = opm.PAYMENT_TYPE_ID
154 and p_effective_date between opm.EFFECTIVE_START_DATE and
155 opm.EFFECTIVE_END_DATE
156 and opm.BUSINESS_GROUP_ID + 0 = p_business_group_id ;
157
158
159 -- Cursor to get batch information
160 -- There is one batch for each combination of GRE and Org Payment Method
161 CURSOR csr_nacha_batch (p_business_group_id number,
162 p_payroll_action_id number,
163 p_effective_date date) IS
164
165 SELECT DISTINCT opm.org_payment_method_id, -- Changed to use OPM, moved PAY_PRE_PAYMENTS fully TO subquery
166 hrorgu.organization_id ,
167 opm.pmeth_information6
168 FROM pay_payroll_actions ppa , -- Add here to allow join directly to ORG_PAYMENT_METHOD_ID
169 pay_org_payment_methods_f opm,
170 hr_organization_units hrorgu ,
171 hr_organization_information hroinf
172 WHERE ppa.payroll_action_id = p_payroll_action_id -- New clause to allow primary key access
173 AND ppa.org_payment_method_id = opm.org_payment_method_id -- join PPA to OPM to narrow to one method
174 AND p_effective_date BETWEEN opm.effective_start_date AND opm.effective_end_date
175 AND hrorgu.business_group_id = p_business_group_id
176 AND opm.business_group_id = p_business_group_id
177 AND hrorgu.organization_id = hroinf.organization_id
178 AND hroinf.org_information_context = 'CLASS'
179 AND hroinf.org_information1 = 'HR_LEGAL'
180 AND hroinf.org_information2 = 'Y'
181 AND EXISTS
182 ( SELECT 1
183 FROM per_assignments_f perasg ,
184 pay_assignment_actions pyaact,
185 pay_pre_payments prepay -- Moved from outer query to subquery
186 WHERE pyaact.payroll_action_id = ppa.payroll_action_id
187 -- join now to outer query
188 AND pyaact.tax_unit_id = hrorgu.organization_id
189 AND perasg.assignment_id = pyaact.assignment_id
190 AND p_effective_date BETWEEN perasg.effective_start_date AND perasg.effective_end_date
191 AND pyaact.pre_payment_id = prepay.pre_payment_id
192 AND prepay.org_payment_method_id = opm.org_payment_method_id
193 AND rownum = 1
194 );
195
196
197 CURSOR csr_assignments(p_legal_company_id number,
198 p_payroll_action_id number,
199 p_org_payment_method_id number,
200 p_rowid rowid) IS
201 select /*+ RULE */
202 paa.assignment_id,
203 paa.assignment_action_id,
204 ppp.value,
205 ppp.personal_payment_method_id,
206 ppp.pre_payment_id,
207 ppp.rowid
208 from pay_assignment_actions paa,
209 pay_pre_payments ppp
210 where ppp.org_payment_method_id+0 = p_org_payment_method_id -- suppressing index for performance improvement Bug 3587226
211 and ppp.PRE_PAYMENT_ID = paa.PRE_PAYMENT_ID
212 and paa.PAYROLL_ACTION_ID = p_payroll_action_id
213 and paa.tax_unit_id = p_legal_company_id -- ADDED for GRE join as the subquery is removed Bug 3587226
214 and ((ppp.rowid >= p_rowid and p_rowid is not null )
215 or
216 (p_rowid is null) )
217 order by ppp.rowid;
218
219 CURSOR csr_assignments_no_rule (p_legal_company_id number,
220 p_payroll_action_id number,
221 p_org_payment_method_id number,
222 p_rowid rowid) IS
223 select
224 paa.assignment_id,
225 paa.assignment_action_id,
226 ppp.value,
227 ppp.personal_payment_method_id,
228 ppp.pre_payment_id,
229 ppp.rowid
230 from pay_assignment_actions paa,
231 pay_pre_payments ppp
232 where ppp.org_payment_method_id = p_org_payment_method_id
233 and ppp.PRE_PAYMENT_ID = paa.PRE_PAYMENT_ID
234 and paa.PAYROLL_ACTION_ID = p_payroll_action_id
235 and paa.tax_unit_id = p_legal_company_id -- ADDED for GRE join as the subquery is removed Bug 3587226
236 and ((ppp.rowid >= p_rowid and p_rowid is not null )
237 or
238 (p_rowid is null) )
239 order by ppp.rowid;
240
241 --
242 END pay_us_nacha_iat_tape;