[Home] [Help]
PACKAGE: APPS.PAY_ZA_UIF_MAGTAPE_PKG
Source
1 package pay_za_uif_magtape_pkg AUTHID CURRENT_USER as
2 /* $Header: pyzauifm.pkh 115.8 2003/10/20 23:35:43 rpahune noship $ */
3 /*REM +====================================================================+
4 REM | Copyright (c) 2002 Oracle Corporation |
5 REM | All rights reserved. |
6 REM +======================================================================+
7 REM SQL Script File name : pyzauifm.pkh
8 REM Description : This sql script seeds the Package that creates
9 REM the UIF Magtape Driving cursors
10 REM
11 REM Change List:
12 REM ------------
13 REM
14 REM Name Date Version Bug Text
15 REM -------------- ----------- ------- ------ ------------------------------
16 REM L.Kloppers 21-Apr-2002 115.0 2266156 Initial Version
17 REM L.Kloppers 06-May-2002 115.1 2266156 Removed join between Assignment
18 REM Actions and Creator Payroll
19 REM Action
20 REM L.Kloppers 08-May-2002 115.2 2266156 Modified to allow for multiple
21 REM archives per UIF Month
22 REM Nageswara Rao 24-Jan-2003 115.3 2654703 Modified employer cursor
23 REM with email address
24 REM Nageswara Rao 02-Feb-2003 115.4 Changes for GSCC compliance
25 REM Nageswara Rao 05-Mar-2003 115.5 Changes in Employee cursor
26 REM R.Pahune 26-Sep-2003 115.7 3134183 Modified Employee cursor
27 REM to reduce the cost.
28 REM R.Pahune 21-Oct-2003 115.8 3134183 Modified Employee cursor NVL
29 REM default changed from ':::' to
30 REM '&&&'
31 REM ========================================================================
32 */
33
34 -- Note: A driving cursor applies to a specific magnetic block. Each block
35 -- could have several formulas associated with it.
36 -- Cursors can pass parameters to the formulas, by indicating them
37 -- with a TRANSFER...=P. Parameters are available to all subsequent
38 -- blocks. The same go for contexts (C). Contexts will be used for
39 -- archive and live database items.
40 -- If you want to use a parameter from a previous cursor in the WHERE
41 -- clause of a subsequent cursor, use get_parameter_value.
42
43 -- The driving cursor for the File Header
44 -- Note: The latest Electronic UIF File Preprocess Payroll Action is selected,
45 -- unless a specific Preprocess is selected. In the latter case the
46 -- TRANSFER_ARCH_PAYROLL_ACTION_ID is the optional Payroll Action ID parameter
47 -- on the Electronic UIF File Preprocess SRS. If supplied it is the Payroll
48 -- Action of the Archive Run.
49 cursor header_cursor is
50 select 'TRANSFER_BUSINESS_GROUP_ID=P' , nvl(pai.action_information1, '&&&'),
51 'TRANSFER_UIF_CAL_MONTH=P' , nvl(pai.action_information2, '&&&'),
52 'TRANSFER_CREATOR_UIF_REFERENCE=P' , nvl(pai.action_information4, '&&&'),
53 'TRANSFER_CONTACT_PERSON=P' , nvl(pai.action_information5, '&&&'),
54 'TRANSFER_CONTACT_NUMBER=P' , nvl(pai.action_information6, '&&&'),
55 'TRANSFER_CONTACT_EMAIL_ADDRESS=P' , nvl(pai.action_information7, '&&&')
56 from pay_action_information pai
57 , pay_payroll_actions ppa
58 where pai.action_information1 = pay_magtape_generic.get_parameter_value('BG_ID')
59 and pai.action_information2 = pay_magtape_generic.get_parameter_value('CAL_MONTH')
60 and pai.action_information_category = 'ZA UIF CREATOR DETAILS'
61 and pai.action_context_type = 'PA'
62 and pai.action_context_id = ppa.payroll_action_id
63 and ppa.rowid =
64 (
65 select substr(
66 max(lpad(ppa2.action_sequence, 15, 0) || ppa2.rowid)
67 , -length(ppa2.rowid)
68 )
69 from pay_payroll_actions ppa2
70 where to_char(to_date(pay_za_uif_archive_pkg.get_parameter('END_DATE', ppa2.legislative_parameters), 'YYYY/MM/DD'), 'YYYYMM')
71 = pay_magtape_generic.get_parameter_value('CAL_MONTH')
72 and ppa2.business_group_id = pay_magtape_generic.get_parameter_value('BG_ID')
73 and ppa2.action_type = 'X'
74 and ppa2.report_type = 'ZA_UIF'
75 and ppa2.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('TRANSFER_ARCH_PAYROLL_ACTION_ID'),
76 ppa2.payroll_action_id)
77 group by length(ppa2.rowid)
78 );
79
80
81 -- The driving cursor for the Employer Header
82 -- Note: TRANSFER_ARCH_PAYROLL_ACTION_ID is the optional Payroll Action ID parameter
83 -- on the Electronic UIF File Preprocess SRS. If supplied it is the Payroll
84 -- Action of the Archive Run.
85 -- TRANSFER_BUSINESS_GROUP_ID is passed from the header_cursor
86 cursor subheader_cursor is
87 select 'TRANSFER_UIF_EMPL_REF_NO=P', nvl(max(distinct pai.action_information4), '&&&') /*UIF Employer Ref No*/
88 , 'TRANSFER_PAYE_EMPL_NO=P', nvl(max(distinct pai.action_information5), '&&&') /*PAYE Employer Number*/
89 /* Changes as per Bug2654703 */
90 , 'TRANSFER_EMPLOYER_EMAIL_ADDRESS=P', nvl(max(distinct pai.action_information22), '&&&') /*UIF Employer Email id*/
91
92 from pay_action_information pai
93 , pay_payroll_actions ppa
94 , pay_assignment_actions paa
95 where pai.action_information2 = pay_magtape_generic.get_parameter_value('CAL_MONTH')
96 and pai.action_context_type = 'AAP'
97 and pai.action_information_category = 'ZA UIF EMPLOYEE DETAILS'
98 and pai.action_context_id = paa.assignment_action_id
99 and paa.payroll_action_id = ppa.payroll_action_id
100 and ppa.payroll_action_id in
101 (
102 select ppa2.payroll_action_id
103 from pay_payroll_actions ppa2
104 where to_char(to_date(pay_za_uif_archive_pkg.get_parameter('END_DATE', ppa2.legislative_parameters), 'YYYY/MM/DD'), 'YYYYMM')
105 = pay_magtape_generic.get_parameter_value('CAL_MONTH')
106 and ppa2.business_group_id = pay_magtape_generic.get_parameter_value('TRANSFER_BUSINESS_GROUP_ID')
107 and ppa2.action_type = 'X'
108 and ppa2.report_type = 'ZA_UIF'
109 and ppa2.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('TRANSFER_ARCH_PAYROLL_ACTION_ID'),
110 ppa2.payroll_action_id)
111 )
112 group by pai.action_information4 /*UIF Employer Ref No*/
113 , pai.action_information5 /*PAYE Employer Number*/
114 , pai.action_information22;/*UIF Employer Email id, Changes as per Bug2654703 */
115
116
117 -- The driving cursor for the Employee Details
118 -- Note: TRANSFER_ARCH_PAYROLL_ACTION_ID is the optional Payroll Action ID parameter
119 -- on the Electronic Tax File Magtape SRS. If supplied it is the Payroll
120 -- Action of the Archive Run.
121 -- Note: TRANSFER_UIF_EMPL_REF_NO is passed from the subheader_cursor
122 -- Start R.Pahune 26-Sep-2003 115.7 3134183
123 cursor employee_cursor is
124 select
125 'TRANSFER_UIF_EMPL_REF_NO=P' , nvl(pai.action_information4 , '&&&'),
126 'TRANSFER_ID_NUMBER=P' , nvl(pai.action_information6 , '&&&'),
127 'TRANSFER_OTHER_NUMBER=P' , nvl(pai.action_information7 , '&&&'),
128 'TRANSFER_EMPLOYEE_NUMBER=P' , nvl(pai.action_information8 , '&&&'),
129 'TRANSFER_SURNAME=P' , nvl(pai.action_information9 , '&&&'),
130 'TRANSFER_FIRST_NAMES=P' , nvl(pai.action_information10, '&&&'),
131 'TRANSFER_DATE_OF_BIRTH=P' , nvl(pai.action_information11, '&&&'),
132 'TRANSFER_DATE_EMPLOYED_FROM=P' , nvl(pai.action_information12, '&&&'),
133 'TRANSFER_DATE_EMPLOYED_TO=P' , nvl(pai.action_information13, '&&&'),
134 'TRANSFER_EMPLOYMENT_STATUS=P' , nvl(pai.action_information14, '01'),
135 'TRANSFER_REASON_NON_CONTRIB=P' , nvl(pai.action_information15, '&&&'),
136 'TRANSFER_GROSS_TAX_REMUN=P' , nvl(pai.action_information16, '&&&'),
137 'TRANSFER_GROSS_UIF_REMUN=P' , nvl(pai.action_information17, '&&&'),
138 'TRANSFER_UIF_CONTRIBUTION=P' , nvl(pai.action_information18, '&&&'),
139 'TRANSFER_BANK_BRANCH_CODE=P' , nvl(pai.action_information19, '&&&'),
140 'TRANSFER_BANK_ACCOUNT_NUMBER=P' , nvl(pai.action_information20, '&&&'),
141 'TRANSFER_BANK_ACCOUNT_TYPE=P' , nvl(pai.action_information21, '&&&')
142 from PAY_ACTION_INFORMATION pai, pay_assignment_actions paa ,
143 (
144 select substr(
145 max(lpad(paa2.action_sequence, 15, 0) || paa2.rowid)
146 , -length(paa2.rowid)
147 ) paa_rowid, assignment_id
148 from pay_assignment_actions paa2
149 , pay_payroll_actions ppa2
150 where ppa2.payroll_action_id = paa2.payroll_action_id
151 and to_char(to_date(pay_za_uif_archive_pkg.get_parameter('END_DATE', ppa2.legislative_parameters), 'YYYY/MM/DD'), 'YYYYMM')
152 = pay_magtape_generic.get_parameter_value('CAL_MONTH')
153 and ppa2.business_group_id = pay_magtape_generic.get_parameter_value('TRANSFER_BUSINESS_GROUP_ID')
154 and ppa2.action_type = 'X'
155 and ppa2.report_type = 'ZA_UIF'
156 and paa2.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('TRANSFER_ARCH_PAYROLL_ACTION_ID'),
157 ppa2.payroll_action_id)
158 group by length(paa2.rowid) , paa2.assignment_id
159 ) paa_v
160 where pai.action_information2 = pay_magtape_generic.get_parameter_value('CAL_MONTH')
161 and pai.action_information4 = pay_magtape_generic.get_parameter_value('TRANSFER_UIF_EMPL_REF_NO')
162 and pai.action_context_id = paa.assignment_action_id
163 and pai.action_context_type = 'AAP'
164 and pai.action_information_category = 'ZA UIF EMPLOYEE DETAILS'
165 and paa.rowid = paa_v.paa_rowid
166 and paa.assignment_id = paa_v.assignment_id
167 order by pai.action_information8 /*Employee Number*/;
168
169 /*Query Before R.Pahune 26-Sep-2003 115.7 3134183 */
170 /*cursor employee_cursor is
171 select
172 'TRANSFER_UIF_EMPL_REF_NO=P' , nvl(pai.action_information4 , '&&&'),
173 'TRANSFER_ID_NUMBER=P' , nvl(pai.action_information6 , '&&&'),
174 'TRANSFER_OTHER_NUMBER=P' , nvl(pai.action_information7 , '&&&'),
175 'TRANSFER_EMPLOYEE_NUMBER=P' , nvl(pai.action_information8 , '&&&'),
176 'TRANSFER_SURNAME=P' , nvl(pai.action_information9 , '&&&'),
177 'TRANSFER_FIRST_NAMES=P' , nvl(pai.action_information10, '&&&'),
178 'TRANSFER_DATE_OF_BIRTH=P' , nvl(pai.action_information11, '&&&'),
179 'TRANSFER_DATE_EMPLOYED_FROM=P' , nvl(pai.action_information12, '&&&'),
180 'TRANSFER_DATE_EMPLOYED_TO=P' , nvl(pai.action_information13, '&&&'),
181 'TRANSFER_EMPLOYMENT_STATUS=P' , nvl(pai.action_information14, '01'), */ /* Defaulted to '01' when null */
182 /* 'TRANSFER_REASON_NON_CONTRIB=P' , nvl(pai.action_information15, '&&&'),
183 'TRANSFER_GROSS_TAX_REMUN=P' , nvl(pai.action_information16, '&&&'),
184 'TRANSFER_GROSS_UIF_REMUN=P' , nvl(pai.action_information17, '&&&'),
185 'TRANSFER_UIF_CONTRIBUTION=P' , nvl(pai.action_information18, '&&&'),
186 'TRANSFER_BANK_BRANCH_CODE=P' , nvl(pai.action_information19, '&&&'),
187 'TRANSFER_BANK_ACCOUNT_NUMBER=P' , nvl(pai.action_information20, '&&&'),
188 'TRANSFER_BANK_ACCOUNT_TYPE=P' , nvl(pai.action_information21, '&&&')
189 from pay_action_information pai
190 , pay_assignment_actions paa
191 where pai.action_information2 = pay_magtape_generic.get_parameter_value('CAL_MONTH')
192 and pai.action_context_type = 'AAP'
193 and pai.action_information4 = pay_magtape_generic.get_parameter_value('TRANSFER_UIF_EMPL_REF_NO')
194 and pai.action_information_category = 'ZA UIF EMPLOYEE DETAILS'
195 and pai.action_context_id = paa.assignment_action_id
196 and paa.rowid =
197 (
198 select substr(
199 max(lpad(paa2.action_sequence, 15, 0) || paa2.rowid)
200 , -length(paa2.rowid)
201 )
202 from pay_assignment_actions paa2
203 , pay_payroll_actions ppa2
204 where paa2.assignment_id = paa.assignment_id
205 and ppa2.payroll_action_id = paa2.payroll_action_id
206 and to_char(to_date(pay_za_uif_archive_pkg.get_parameter('END_DATE', ppa2.legislative_parameters), 'YYYY/MM/DD'), 'YYYYMM')
207 = pay_magtape_generic.get_parameter_value('CAL_MONTH')
208 and ppa2.business_group_id = pay_magtape_generic.get_parameter_value('TRANSFER_BUSINESS_GROUP_ID')
209 and ppa2.action_type = 'X'
210 and ppa2.report_type = 'ZA_UIF'
211 and ppa2.payroll_action_id = nvl(pay_magtape_generic.get_parameter_value('TRANSFER_ARCH_PAYROLL_ACTION_ID'),
212 ppa2.payroll_action_id)
213 group by length(paa2.rowid)
214 )
215 order by pai.action_information8 */ /*Employee Number*/
216
217 -- End R.Pahune 26-Sep-2003 115.7 3134183
218
219 level_cnt number;
220
221 end pay_za_uif_magtape_pkg;