[Home] [Help]
PACKAGE: APPS.PAY_ZA_IRP5_MAGTAPE_PKG
Source
1 package pay_za_irp5_magtape_pkg as
2 /* $Header: pyzamag.pkh 120.5.12000000.2 2007/07/03 11:32:39 rpahune noship $ */
3
4 type char240_data_type_table is table of varchar2(240)
5 index by binary_integer;
6
7 -- Note: A driving cursor applies to a specific magnetic block. Each block
8 -- could have several formulas associated with it.
9 -- Cursors can pass parameters to the formulas, by indicating them
10 -- with a TRANSFER...=P. Parameters are available to all subsequent
11 -- blocks. The same go for contexts (C). Contexts will be used for
12 -- archive and live database items.
13 -- If you want to use a parameter from a previous cursor in the WHERE
14 -- clause of a subsequent cursor, use get_parameter_value.
15
16 -- The driving cursor for the File Header
17 cursor header_cursor is
18 select 'TRANSFER_BUSINESS_GROUP_ID=P', nvl(to_char(hoi.organization_id), '&&&'),
19 'TRANSFER_CREATOR_NAME=P', nvl(substr(hoi.org_information1, 1, 80), '&&&'),
20 'TRANSFER_PAYE_NUMBER=P', nvl(substr(hoi.org_information2, 1, 80), '&&&'),
21 'TRANSFER_CONTACT_NAME=P', nvl(substr(hoi.org_information3, 1, 80), '&&&'),
22 'TRANSFER_CONTACT_PHN=P', nvl(substr(hoi.org_information4, 1, 80), '&&&'),
23 'TRANSFER_ALT_PHN=P', nvl(substr(hoi.org_information5, 1, 80), '&&&'),
24 'TRANSFER_ADD_1=P', nvl(substr(hoi.org_information6, 1, 80), '&&&'),
25 'TRANSFER_ADD_2=P', nvl(substr(hoi.org_information7, 1, 80), '&&&'),
26 'TRANSFER_ADD_3=P', nvl(substr(hoi.org_information8, 1, 80), '&&&'),
27 'TRANSFER_ADD_4=P', nvl(substr(hoi.org_information9, 1, 80), '&&&'),
28 'TRANSFER_POST_CODE=P', nvl(substr(hoi.org_information10, 1, 80), '&&&')
29 from hr_all_organization_units haou,
30 hr_organization_information hoi
31 where hoi.organization_id = haou.organization_id
32 and hoi.org_information_context = 'ZA_TAX_FILE_ENTITY'
33 and pay_magtape_generic.get_parameter_value('BUS_GRP') = hoi.organization_id;
34
35 -- The driving cursor for the Employer Header
36 -- Note: TRANSFER_PAYROLL_ACTION_ID is the optional Payroll Action ID parameter
37 -- on the Electronic Tax File Magtape SRS. If supplied it is the Payroll
38 -- Action of the Archive Run.
39 cursor subheader_cursor is
40 select 'TRANSFER_TRADE_NAME=P', nvl(substr(hoi.org_information1, 1, 80), '&&&'),
41 'TRANSFER_PAYE_NUMBER=P', nvl(substr(hoi.org_information3, 1, 80), '&&&'),
42 'TRANSFER_DIP_IND=P', nvl(substr(hoi.org_information4, 1, 80), '&&&'),
43 'TRANSFER_EMP_ADD_1=P', nvl(substr(hloc.address_line_1, 1, 80), '&&&'),
44 'TRANSFER_EMP_ADD_2=P', nvl(substr(hloc.address_line_2, 1, 80), '&&&'),
45 'TRANSFER_EMP_ADD_3=P', nvl(substr(hloc.address_line_3, 1, 80), '&&&'),
46 'TRANSFER_EMP_ADD_4=P', nvl(substr(hloc.town_or_city, 1, 80), '&&&'),
47 'TRANSFER_EMP_POSTCODE=P', nvl(substr(hloc.postal_code, 1, 80), '&&&'),
48 'TRANSFER_LEG_ENTITY_ID=P', haou.organization_id
49 from hr_all_organization_units haou,
50 hr_organization_information hoi,
51 hr_locations hloc
52 where hoi.organization_id = haou.organization_id
53 and pay_magtape_generic.get_parameter_value('BUS_GRP') = haou.business_group_id
54 and hoi.org_information_context = 'ZA_LEGAL_ENTITY'
55 and hloc.location_id (+) = haou.location_id
56 and exists
57 (
58 select ''
59 from pay_payroll_actions ppa,
60 pay_assignment_actions paa,
61 per_assignment_extra_info paei
62 where ppa.payroll_action_id =
63 nvl(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),
64 ppa.payroll_action_id)
65 and ppa.action_type = 'X'
66 and ppa.report_type = 'ZA_IRP5'
67 and pay_za_irp5_archive_pkg.get_parameter('TAX_YEAR',
68 ppa.legislative_parameters) =
69 pay_magtape_generic.get_parameter_value('TAX_YEAR')
70 and paa.payroll_action_id = ppa.payroll_action_id
71 and paa.assignment_id = paei.assignment_id
72 and to_char(haou.organization_id) = paei.aei_information7
73 and paei.aei_information_category = 'ZA_SPECIFIC_INFO'
74 and paa.serial_number is not null
75 and py_za_tax_certificates.irp5_indicator(paa.assignment_action_id) in ('Y','N') -- 6166892
76 and substr(paa.serial_number, 1, 1) in ('0','1','2','3','4','5','6','7','8','9')
77 )
78 order by haou.organization_id;
79
80 -- The driving cursor for the Employee Details
81 -- Note: TRANSFER_PAYROLL_ACTION_ID is the optional Payroll Action ID parameter
82 -- on the Electronic Tax File Magtape SRS. If supplied it is the Payroll
83 -- Action of the Archive Run.
84 -- Note: TRANSFER_LEG_ENTITY_ID is passed from the subheader_cursor
85 -- Note: The ASSIGNMENT_ACTION_ID context is the Assignment Action of the Archiver
86 -- Note: The last entry in this tax year is chosen. It might happen that a person
87 -- transfers between payrolls, but this is not catered for; since he is
88 -- supposed to start on a new assignment number.
89 cursor employee_cursor is
90 SELECT 'TRANSFER_EMPEE_NAME=P', nvl(ltrim(rtrim(substr(paei.aei_information2, 1, 80))), '&&&'),
91 'TRANSFER_LAST_NAME=P', nvl(substr(ppf.last_name, 1, 80), '&&&'),
92 'TRANSFER_ID_NUMBER=P', nvl(substr(ppf.national_identifier, 1, 80), '&&&'),
93 'TRANSFER_BIRTH_DATE=P', nvl(to_char(ppf.date_of_birth, 'YYYYMMDD'), '8010101'),
94 'TRANSFER_EMPLOYEE_NUMBER=P', nvl(substr(ppf.employee_number, 1, 80), '&&&'),
95 'TRANSFER_FIRST_NAME=P', nvl(substr(ppf.first_name, 1, 80), '&&&'),
96 'TRANSFER_MIDDLE_NAMES=P', nvl(substr(ppf.middle_names, 1, 80), '&&&'),
97 'TRANSFER_CC_TRUST_NUMBER=P', nvl(substr(paei.aei_information3, 1, 80), '&&&'),
98 'TRANSFER_NATURE=P', nvl(substr(paei.aei_information4, 1, 80), '&&&'),
99 'TRANSFER_CONTRACTOR=P', nvl(substr(paei.aei_information6, 1, 80), '&&&'),
100 'TRANSFER_LABOUR_BROKER=P', nvl(substr(paei.aei_information10, 1, 80), '&&&'), -- Bug 3396163
101 'TRANSFER_PENSION_BASIS=P', nvl(substr(paei.aei_information8, 1, 80), nvl(substr(scl.segment10, 1, 80), '1')),
102 'TRANSFER_PASSPORT=P', nvl(substr(ppf.per_information2, 1, 80), '&&&'),
103 'TRANSFER_INCOME_NUMBER=P', nvl(substr(ppf.per_information1, 1, 80), '&&&'),
104 'TRANSFER_ASSIGNMENT_ACTION_ID=P', paa.assignment_action_id,
105 'TRANSFER_CERTIFICATE_NUMBER=P', substr(paa.serial_number, 1, 80),
106 'TRANSFER_TAX_DIRECTIVE_NO=P', nvl(faidn.context,'&&&'),
107 'ASSIGNMENT_ID=C', paa.assignment_id,
108 'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id,
109 'PAYROLL_ACTION_ID=C', paa.payroll_action_id,
110 'PERSON_ID=C', pef.person_id,
111 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
112 'PAYROLL_ID=C', pef.payroll_id,
113 'SOURCE_TEXT=C', nvl(faidn.context,'To Be Advised')
114 from pay_payroll_actions ppa,
115 hr_soft_coding_keyflex scl,
116 pay_all_payrolls_f papf,
117 per_assignments_f pef,
118 per_people_f ppf,
119 pay_assignment_actions paa,
120 per_assignment_extra_info paei,
121 ff_archive_items fai,
122 ff_archive_item_contexts faidn,
123 ff_contexts fc1,
124 ff_database_items fdi
125 where ppa.payroll_action_id =
126 nvl(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),
127 ppa.payroll_action_id)
128 and ppa.action_type = 'X'
129 and ppa.report_type = 'ZA_IRP5'
130 and pay_za_irp5_archive_pkg.get_parameter('TAX_YEAR', ppa.legislative_parameters) =
131 pay_magtape_generic.get_parameter_value('TAX_YEAR')
132 and paa.payroll_action_id = ppa.payroll_action_id
133 and paa.assignment_id = paei.assignment_id
134 and paa.serial_number is not null
135 and py_za_tax_certificates.irp5_indicator(paa.assignment_action_id) in ('Y','N') -- 6166892
136 and substr(paa.serial_number, 1, 1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
137 and pef.assignment_id = paa.assignment_id
138 and ppf.person_id = pef.person_id
139 and ppf.per_information_category = 'ZA'
140 and pef.effective_start_date =
141 (
142 select max(pef2.effective_start_date)
143 from per_assignments_f pef2
144 where pef2.effective_start_date <= ppa.effective_date
145 and pef2.assignment_id = paa.assignment_id
146 )
147 and ppf.effective_start_date =
148 (
149 select max(ppf2.effective_start_date)
150 from per_people_f ppf2
151 where ppf2.effective_start_date <= ppa.effective_date
152 and ppf2.person_id = ppf.person_id
153 )
154 and papf.payroll_id = pef.payroll_id
155 and papf.effective_start_date =
156 (
157 select max(papf2.effective_start_date)
158 from pay_all_payrolls_f papf2
159 where papf2.effective_start_date <= ppa.effective_date
160 and papf2.payroll_id = pef.payroll_id
161 )
162 and scl.soft_coding_keyflex_id (+) = papf.soft_coding_keyflex_id
163 and paei.aei_information7 =
164 pay_magtape_generic.get_parameter_value('TRANSFER_LEG_ENTITY_ID')
165 and paei.aei_information_category = 'ZA_SPECIFIC_INFO'
166 AND fai.context1 = paa.assignment_action_id
167 and fc1.context_name = 'SOURCE_TEXT'
168 and faidn.context_id = fc1.context_id
169 and fai.archive_item_id = faidn.archive_item_id
170 and fdi.user_entity_id = fai.user_entity_id
171 and fdi.user_name = 'A_TAX_ON_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
172 order by paa.serial_number;
173
174 -- The driving cursor for the Deductions Details
175 -- Note: TRANSFER_ASSIGNMENT_ACTION_ID is the assignment action id passed from the
176 -- employee_cursor.
177 cursor deductions_cursor is
178 SELECT distinct 'TRANSFER_DEDUCTION=P', 1,
179 'TRANSFER_SARS_CODE=P', SARS_CODE,
180 'TRANSFER_CLEARANCE_NO=P', CLEARANCE_NO,
181 'SOURCE_ID=C', SOURCE_ID,
182 'SOURCE_NUMBER=C', SOURCE_NUMBER
183 from
184 (
185 Select substr(fai.value, 1, 80) DEDUCTION,
186 code.code SARS_CODE,
187 faic2.CONTEXT CLEARANCE_NO,
188 code.code SOURCE_ID,
189 faic2.CONTEXT SOURCE_NUMBER
190 FROM
191 ff_archive_items fai,
192 ff_archive_item_contexts faic2,
193 ff_contexts fc2,
194 pay_za_irp5_bal_codes code,
195 ff_database_items fdi
196 where fai.context1 =
197 pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ACTION_ID')
198 and fc2.context_name = 'SOURCE_NUMBER'
199 and faic2.context_id = fc2.context_id
200 and fai.archive_item_id = faic2.archive_item_id
201 AND fdi.user_entity_id = fai.user_entity_id
202 AND fdi.user_name = code.user_name
203 AND code.user_name IN (
204 'A_ANNUAL_PENSION_FUND_ASG_CLRNO_TAX_YTD'
205 ,'A_CURRENT_PENSION_FUND_ASG_CLRNO_TAX_YTD'
206 ,'A_ANNUAL_ARREAR_PENSION_FUND_ASG_CLRNO_TAX_YTD'
207 ,'A_ARREAR_PENSION_FUND_ASG_CLRNO_TAX_YTD'
208 ,'A_ANNUAL_PROVIDENT_FUND_ASG_CLRNO_TAX_YTD'
209 ,'A_CURRENT_PROVIDENT_FUND_ASG_CLRNO_TAX_YTD'
210 ,'A_ARREAR_PROVIDENT_FUND_ASG_CLRNO_TAX_YTD'
211 ,'A_ANNUAL_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
212 ,'A_CURRENT_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
213 ,'A_ANNUAL_ARREAR_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
214 ,'A_ARREAR_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
215 )
216 UNION all
217 select
218 substr(fai.value, 1, 80) DEDUCTION,
219 code.code SARS_CODE,
220 '99999999999' CLEARANCE_NO,
221 code.code SOURCE_ID,
222 '99999999999' SOURCE_NUMBER
223 FROM
224 ff_archive_items fai,
225 pay_za_irp5_bal_codes code,
226 ff_database_items fdi
227 Where fai.context1 =
228 pay_magtape_generic.get_parameter_value('TRANSFER_ASSIGNMENT_ACTION_ID')
229 AND fdi.user_entity_id = fai.user_entity_id
230 AND fdi.user_name = code.user_name
231 And code.user_name in
232 (
233 'A_MEDICAL_AID_CONTRIBUTION_ASG_TAX_YTD' --4005
234 ,'A_EE_INCOME_PROTECTION_POLICY_CONTRIBUTIONS_ASG_TAX_YTD' --4018
235 ,'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_RFI_ASG_TAX_YTD' --4024
236 ,'A_MEDICAL_CONTRIBUTIONS_ABATEMENT_ASG_TAX_YTD' --4025
237 )
238 ) deduction
239 order by SARS_CODE , SOURCE_NUMBER;
240
241 level_cnt number;
242
243 end pay_za_irp5_magtape_pkg;