[Home] [Help]
PACKAGE: APPS.PAY_AU_TFN_MAGTAPE
Source
1 PACKAGE pay_au_tfn_magtape AUTHID CURRENT_USER AS
2 /* $Header: pyautfn.pkh 120.6.12010000.5 2009/10/30 11:50:29 dduvvuri ship $*/
3 --------------------------------------------------------------------------+
4
5
6
7 --------------------------------------------------------------------------+
8 -- The variable is used to decide wether tax details are getting
9 -- updated using API or from form PAYAUTAX .
10 -- If called from FORM the varables is initialized to 'FORM'
11 --------------------------------------------------------------------------+
12 /* Bug 4066194 - Removed default settings to resolve GSCC Warnings */
13
14 tax_api_called_from varchar2(10);
15
16 /*Bug 5367067 variable inv_character_string is used to store set of invalid characters
17 variable blank_character_string is used to store the blank character the no of spaces should be
18 equal to number of invalid characters in inv_character_string */
19
20 inv_character_string varchar2(40) default ',_$#+@`!*^%~[]{};:\|?><.'; /* 9000052 - Added a dot character */
21 blank_character_string varchar2(40) default ' '; /* 9000052 - Added an extra space for the above dot character */
22
23
24 level_cnt number;
25
26 /* Bug 4066194 -
27 Removed Record Type - tfn_flags_record
28 Procedure - populate_tfn_flags
29 Function - get_tfn_flag_values
30 */
31
32 --------------------------------------------------------------------------+
33 -- PROCEDURE to return the sql statement to select the range of employees
34 -- to be processed.
35 --------------------------------------------------------------------------+
36 PROCEDURE range_code
37 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
38 p_sql out nocopy varchar2);
39
40
41
42
43 --------------------------------------------------------------------------+
44 -- PROCEDURE to further restrict the assignments to be processed by the
45 -- archive process.
46 --------------------------------------------------------------------------+
47 PROCEDURE assignment_action_code
48 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
49 p_start_person_id in per_all_people_f.person_id%TYPE,
50 p_end_person_id in per_all_people_f.person_id%TYPE,
51 p_chunk in number);
52
53
54
55
56 --------------------------------------------------------------------------+
57 -- PROCEDURE to initialize the globals, plsql tables and contexts.
58 --------------------------------------------------------------------------+
59 PROCEDURE initialization_code
60 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE);
61
62
63
64
65 --------------------------------------------------------------------------+
66 -- PROCEDURE to actually archive the data.
67 --------------------------------------------------------------------------+
68 PROCEDURE archive_code
69 (p_payroll_action_id in pay_assignment_actions.payroll_action_id%TYPE,
70 p_effective_date in date);
71
72
73 --------------------------------------------------------------------------+
74 -- PROCEDURE to set the value of the variable tax_api_called_from.
75 --------------------------------------------------------------------------+
76 PROCEDURE set_value
77 (p_value in varchar2);
78
79
80
81
82 --------------------------------------------------------------------------+
83 -- FUNCTION to get the value of the variable tax_api_called_from.
84 --------------------------------------------------------------------------+
85 FUNCTION get_value return varchar2;
86
87
88 --------------------------------------------------------------------------+
89 -- Declaration of the cursors for the magtape process
90 --------------------------------------------------------------------------+
91
92 /*
93 ** Cursor to retrieve the Supplier Detail information
94 */
95
96 /*Bug2920725 Corrected base tables to support security model*/
97 /* Bug 3229452 - Used fnd_date.canonical_to_date instead of to_date for selecting
98 REPORT_END_DATE*/
99 /* Bug 9000052 - Used function remove_extra_spaces in package pay_au_tfn_magtape_flags for reporting name fields correctly */
100 CURSOR C_TFN_SUPPLIER IS
101 SELECT
102 'LEGAL_EMPLOYER_NAME=P'
103 ,hou.name
104 ,'SUPPLIER_NUMBER=P'
105 ,hoi.org_information12
106 ,'RUN_TYPE=P'
107 ,pay_magtape_generic.get_parameter_value('RUN_TYPE')
108 ,'REPORT_END_DATE=P'
109 ,to_char(fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('REPORT_END_DATE')),'ddmmyyyy')
110 ,'GATEWAY_USER_ID=P'
111 ,NVL(hoi.org_information15,' ')
112 ,'SUPPLIER_NAME=P'
113 ,pay_au_tfn_magtape_flags.remove_extra_spaces(translate(hoi.org_information3,inv_character_string,blank_character_string)) /*Bug 9000052*/
114 ,'CONTACT_NAME=P'
115 ,pay_au_tfn_magtape_flags.remove_extra_spaces(translate(decode(pap.first_name,null,'',pap.first_name || ' ') || pap.last_name,inv_character_string,blank_character_string)) /*Bug 9000052*/
116 ,'TELEPHONE_NUMBER=P'
117 ,hoi.org_information14
118 ,'SUPP_FILE_REFERENCE=P'
119 ,nvl(pay_magtape_generic.get_parameter_value('SUPP_FILE_REF'),' ')
120 ,'STREET_ADDRESS1=P'
121 ,translate(nvl(hlc.address_line_1,' '),inv_character_string,blank_character_string)
122 ,'STREET_ADDRESS2=P'
123 ,translate(nvl(hlc.address_line_2,' '),inv_character_string,blank_character_string)
124 ,'SUBURB=P'
125 ,translate(nvl(hlc.town_or_city,' '),inv_character_string,blank_character_string)
126 ,'STATE=P'
127 ,nvl(hlc.region_1,' ')
128 ,'POST_CODE=P'
129 ,nvl(hlc.postal_code,' ')
130 ,'COUNTRY=P'
131 ,nvl(ftl.territory_short_name,' ')
132 ,'SUPP_EMAIL=P'
133 ,nvl(pap.email_address,' ')
134 FROM hr_organization_information hoi
135 ,hr_locations_all hlc --Modified the table from hr_locations to hr_locations_all
136 ,fnd_territories_tl ftl
137 ,hr_organization_units hou
138 ,per_people_f pap
139 WHERE hou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
140 AND hou.organization_id = hoi.organization_id
141 AND hoi.organization_id = pay_magtape_generic.get_parameter_value('LEGAL_EMPLOYER')
142 AND hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
143 AND ftl.territory_code = hlc.country
144 AND ftl.language(+) = userenv('LANG')
145 AND hlc.location_id(+) = hou.location_id
146 AND hoi.org_information7 = pap.person_id
147 AND pap.effective_start_date = (SELECT max(effective_start_date)
148 FROM per_people_f p
149 WHERE pap.person_id=p.person_id
150 and p.effective_start_date <= /* 5474358 */
151 fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('REPORT_END_DATE'))
152 group by p.person_id);
153
154
155
156
157 /*
158 ** Cursor to retrieve the Payer(Employer) Detail information
159 */
160
161 /*Bug2920725 Corrected base tables to support security model*/
162 /* Bug 9000052 - Used function remove_extra_spaces in package pay_au_tfn_magtape_flags for reporting name fields correctly */
163 CURSOR C_TFN_PAYER IS
164 SELECT
165 'REPORT_START_DATE=P'
166 ,to_char(to_date(pay_magtape_generic.get_parameter_value('REPORT_END_DATE'),'ddmmyyyy')-14,'ddmmyyyy')/*Bug 2974527*/
167 ,'REPORT_END_DATE=P'
168 ,pay_magtape_generic.get_parameter_value('REPORT_END_DATE')
169 ,'PAYER_ABN=P'
170 ,hoi.org_information12
171 ,'BRANCH_NUMBER=P'
172 ,nvl(hoi.org_information13,'001') /* Bug#5570822 Setting Branch number to 001 to avoid ECI checker error*/
173 ,'BUSINESS_NAME=P'
174 ,pay_au_tfn_magtape_flags.remove_extra_spaces(translate(nvl(hoi.org_information3,' '),inv_character_string,blank_character_string)) /*Bug 9000052*/
175 ,'TRADING_NAME=P'
176 ,pay_au_tfn_magtape_flags.remove_extra_spaces(translate(nvl(hoi.org_information4,' '),inv_character_string,blank_character_string)) /*Bug 9000052*/
177 ,'ADDRESS_LINE1=P'
178 ,translate(nvl(hlc.address_line_1,' '),inv_character_string,blank_character_string)
179 ,'ADDRESS_LINE2=P'
180 ,translate(nvl(hlc.address_line_2,' '),inv_character_string,blank_character_string)
181 ,'SUBURB=P'
182 ,translate(nvl(hlc.town_or_city,' '),inv_character_string,blank_character_string)
183 ,'STATE=P'
184 ,nvl(hlc.region_1,' ')
185 ,'POST_CODE=P'
186 ,nvl(hlc.postal_code,' ')
187 ,'COUNTRY=P'
188 ,nvl(ftl.territory_short_name,' ')
189 ,'CONTACT_NAME=P'
190 ,pay_au_tfn_magtape_flags.remove_extra_spaces(translate(decode(pap.first_name,null,'',pap.first_name || ' ') || pap.last_name,inv_character_string,blank_character_string)) /*Bug 9000052*/
191 ,'TELEPHONE_NUMBER=P'
192 ,hoi.org_information14
193 FROM hr_organization_information hoi
194 ,hr_locations_all hlc --Modified the table from hr_locations to hr_locations_all
195 ,fnd_territories_tl ftl
196 ,hr_organization_units hou
197 ,per_people_f pap
198 WHERE hou.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
199 AND hou.organization_id = hoi.organization_id
200 AND hoi.organization_id = pay_magtape_generic.get_parameter_value('LEGAL_EMPLOYER')
201 AND hoi.org_information_context = 'AU_LEGAL_EMPLOYER'
202 AND ftl.territory_code = hlc.country
203 AND ftl.language(+) = userenv('LANG')
204 AND hlc.location_id(+) = hou.location_id
205 AND hoi.org_information7 = pap.person_id
206 AND pap.effective_start_date = (SELECT max(effective_start_date)
207 FROM per_people_f p
208 WHERE pap.person_id=p.person_id
209 and p.effective_start_date <= /* 5474358 */
210 to_date(pay_magtape_generic.get_parameter_value('REPORT_END_DATE'),'DD-MM-YYYY')
211 group by p.person_id );
212
213
214
215 /*
216 ** Cursor to retrieve the Payee Detail information
217 */
218
219 -------------------------------------------------------------+
220 -- The plsql table populated by the initialization_code will be
221 -- used to get the value of the reportable fields. Also the
222 -- assignment action inserted by the assignment_code will the
223 -- restriction condition for the employees to print on magtape
224 -------------------------------------------------------------+
225 /*Bug2920725 Corrected base tables to support security model*/
226 /*
227 Bug 4066194 - Changed Package reference to "pay_au_tfn_magtape_flags"
228 Removed package self reference.
229
230 Bug4247686 - Modified for performance reason
231 */
232
233 /* Bug 9000052 - Used function remove_extra_spaces in package pay_au_tfn_magtape_flags for reporting name fields correctly */
234 CURSOR C_TFN_PAYEE IS
235 SELECT
236 'PAYEE_TFN=P'
237 ,replace(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'TAX_FILE_NUMBER'),' ','')
238 ,'SURNAME=P'
239 ,pay_au_tfn_magtape_flags.remove_extra_spaces(translate(pap.last_name,inv_character_string,blank_character_string)) /*Bug 9000052*/
240 ,'FIRST_GIVEN_NAME=P'
241 ,pay_au_tfn_magtape_flags.remove_extra_spaces(translate(nvl(pap.first_name,'NULL VALUE'),inv_character_string,blank_character_string)) /*Bug 9000052*/
242 ,'SECOND_GIVEN_NAME=P'
243 ,pay_au_tfn_magtape_flags.remove_extra_spaces(translate(nvl(pap.middle_names,' '),inv_character_string,blank_character_string)) /*Bug 9000052*/
244 ,'EMPLOYEE_NUMBER=P'
245 ,nvl(pap.employee_number,' ')
246 ,'PREVIOUS_SURNAME=P'
247 ,pay_au_tfn_magtape_flags.remove_extra_spaces(translate(nvl(pap.previous_last_name,' '),inv_character_string,blank_character_string)) /*Bug 9000052*/
248 ,'DATE_OF_BIRTH=P'
249 ,to_char(pap.date_of_birth,'DDMMYYYY')
250 ,'ADDRESS_LINE1=P'
251 ,translate(nvl(pad.address_line1,' '),inv_character_string,blank_character_string)
252 ,'ADDRESS_LINE2=P'
253 ,translate(nvl(pad.address_line2,' '),inv_character_string,blank_character_string)
254 ,'SUBURB=P'
255 ,translate(nvl(pad.town_or_city,' '),inv_character_string,blank_character_string)
256 ,'STATE=P'
257 ,nvl(decode(pad.country,null,null,'AU',pad.region_1,'OTH'),' ')/*changed for Bug 2751147*/
258 ,'POST_CODE=P'
259 ,nvl(decode(pad.country,null,null,'AU',pad.postal_code,'9999'),'0000')/*Changed for Bug 2751147*/
260 ,'COUNTRY=P'
261 ,nvl(decode(pad.country,'AU',' ',pad.country),' ')
262 ,'PAYROLL_NUMBER=P'
263 ,paa.assignment_number
264 ,'PAYEE_TERMINATOR_IND=P'
265 ,decode(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'CURRENT_OR_TERMINATED'),'T','T',' ')
266 ,'AU_RES=P'
267 ,nvl(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'AUSTRALIAN_RESIDENT_FLAG'),' ')
268 ,'BASIS_OF_PAYMENT=P'
269 ,pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'BASIS_OF_PAYMENT')
270 ,'TAX_FREE_TH=P'
271 ,nvl(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'TAX_FREE_THRESHOLD_FLAG'),' ')
272 ,'FTB=P'
273 ,nvl(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'FTA_CLAIM_FLAG'),'N')
274 ,'REBATE_FLAG=P'
275 ,nvl(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'REBATE_FLAG'),'N')
276 ,'HECS=P'
277 ,nvl(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'HECS_FLAG'),' ')
278 ,'SFSS=P'
279 ,nvl(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'SFSS_FLAG'),'N')
280 ,'TFN_FOR_SUPER=P'
281 ,nvl(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'TFN_FOR_SUPER'),'N')
282 ,'DATE_DECLARATION=P'
283 ,nvl(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'DECLARATION_SIGNED_DATE'),'00000000')
284 ,'EFFECTIVE_START_DATE=P'
285 ,pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'EFFECTIVE_START_DATE')
286 ,'SATO=P' /*bug7270073*/
287 ,nvl(pay_au_tfn_magtape_flags.get_tfn_flag_values(paa.assignment_id,'SENIOR_FLAG'),'N')
288 FROM
289 hr_soft_coding_keyflex hsc
290 ,per_assignments_f paa
291 ,per_people_f pap
292 ,per_addresses pad
293 ,pay_payroll_actions ppa
294 ,pay_assignment_actions pac
295 WHERE paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
296 AND ppa.report_type='AU_TFN_MAGTAPE' /*Bug4247686 */
297 AND ppa.report_qualifier='AU'
298 AND ppa.report_category='REPORT'
299 AND pap.business_group_id = pay_magtape_generic.get_parameter_value('BUSINESS_GROUP_ID')
300 AND hsc.segment1 = pay_magtape_generic.get_parameter_value('LEGAL_EMPLOYER')
301 AND pap.person_id = paa.person_id
302 AND pap.person_id = pad.person_id(+)
303 AND pad.primary_flag(+) = 'Y' /*Added for bug 2751147*/
304 AND paa.effective_start_date = ( SELECT max(effective_Start_date)
305 FROM per_assignments_f a
306 WHERE a.assignment_id = paa.assignment_id
307 and a.effective_start_date < =
308 to_date(pay_magtape_generic.get_parameter_value('REPORT_END_DATE'),'DD-MM-YYYY') /*5474358 */
309 group by a.assignment_id /*Bug4247686 */
310 )
311 AND pap.effective_start_date = ( SELECT max(effective_Start_date)
312 FROM per_people_f p
313 WHERE p.person_id = pap.person_id
314 and p.effective_start_date < =
315 to_date(pay_magtape_generic.get_parameter_value('REPORT_END_DATE'),'DD-MM-YYYY') /*5474358 */
316 group by p.person_id /*Bug4247686 */
317 )
318 and sysdate between nvl(pad.date_from , sysdate) and nvl(pad.date_to ,sysdate) /* 4632219 */
319 AND ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
320 AND pac.payroll_action_id = ppa.payroll_action_id
321 AND pac.assignment_id = paa.assignment_id
322 ORDER BY pap.employee_number ;
323
324
325 END PAY_AU_TFN_MAGTAPE;