1 PACKAGE BODY pay_au_tfn_magtape AS
2 /* $Header: pyautfn.pkb 120.13 2011/05/25 07:48:36 skshin ship $*/
3 ------------------------------------------------------------------------------+
4
5
6 /* Bug 4066194 - REMOVED implementation
7 Procedure - populate_tfn_flags
8 Function - get_tfn_flag_values
9 Above components moved to package "pay_au_tfn_magtape_flags".
10 */
11
12 ------------------------------------------------------------------------------+
13 -- This procedure sets the value of the global variable 'tax_api_called_from'
14 -- The value is set to 'FORM' in 'Tax Declaration' form when making changes to
15 -- the tax details.
16 ------------------------------------------------------------------------------+
17
18 PROCEDURE set_value(p_value in varchar2) IS
19
20 BEGIN
21 hr_utility.trace('Start of procedure set_value');
22 tax_api_called_from := p_value;
23 hr_utility.trace('p value : ' || p_value);
24 hr_utility.trace('End of procedure set_value');
25
26
27
28 EXCEPTION
29 WHEN OTHERS THEN
30 hr_utility.trace('Error in set_value');
31 RAISE;
32
33 END set_value;
34
35
36 ------------------------------------------------------------------------------+
37 -- This function returns the value stored in the variable 'tax_api_called_from'
38 -- It is used in the API 'HR_AU_TAX_API' to check if the API procedures are
39 -- called from the 'FORM' or from the wrapper APIs.
40 ------------------------------------------------------------------------------+
41
42 FUNCTION get_value RETURN varchar2 IS
43 BEGIN
44 /* Bug 4066194 - IF Section Added for Resolving GSCC Warnings
45 Default Value is 'API' */
46 if (tax_api_called_from is null)
47 then
48 tax_api_called_from := 'API';
49 end if;
50 RETURN tax_api_called_from;
51 END;
52
53
54
55
56
57 ------------------------------------------------------------------------------+
58 -- This procedure is used to select the range of persons to be considered
59 -- for the archicve process.
60 ------------------------------------------------------------------------------+
61
62 PROCEDURE range_code
63 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
64 p_sql out nocopy varchar2) IS
65
66 BEGIN
67
68 hr_utility.set_location('Start of range_code',1);
69
70 /*Bug2920725 Corrected base tables to support security model*/
71
72 p_sql := ' SELECT distinct p.person_id' ||
73 ' FROM per_people_f p,' ||
74 ' pay_payroll_actions pa ' ||
75 ' WHERE pa.payroll_action_id = :payroll_action_id' ||
76 ' AND p.business_group_id = pa.business_group_id' ||
77 ' ORDER BY p.person_id';
78
79 hr_utility.set_location('End of range_code',2);
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 hr_utility.trace('Error in range_code');
84 RAISE;
85 END range_code;
86
87
88
89
90
91 ----------------------------------------------------------------------------+
92 -- This procedure is used to further restrict the Assignment Action
93 -- Creation. It calls the procedure that actually inserts the Assignment
94 -- Actions. We need this to restrict person from being reported on the
95 -- magtape once again.
96 ----------------------------------------------------------------------------+
97
98 PROCEDURE assignment_action_code
99 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
100 p_start_person_id in per_all_people_f.person_id%TYPE,
101 p_END_person_id in per_all_people_f.person_id%TYPE,
102 p_chunk in number) IS
103
104 v_next_action_id pay_assignment_actions.assignment_action_id%TYPE;
105 v_run_action_id pay_assignment_actions.assignment_action_id%TYPE;
106 p_assignment_id pay_assignment_actions.assignment_id%TYPE;
107 ps_report_id pay_assignment_actions.assignment_action_id%TYPE;
108
109
110 ----------------------------------------------------------------------------------
111 -- Cursor to restrict the assignments to be processed by the magtape.
112 -- It selects all assignments of the input 'Legal Employer' who has changed the
113 -- tax detail fields value in the current reporting period.
114 -- Select all assignments -
115 -- 1. Within the legal employer
116 -- 2. Either terminated OR tax details updated in the current reporting period
117 -- 3. Not already reported by a magtape in the current reporting period.
118 ----------------------------------------------------------------------------------
119
120 /* Bug 2728358 - Added check for employee terminated on report end date */
121
122 /*Bug2920725 Corrected base tables to support security model*/
123
124 /* Bug 2974527 - Added trunc for fnd_date.canonical_to_date function */
125 /* Bug 2974527 - Backed out the changed for Bug 2974527*/
126 /* Bug 3229452 - Used fnd_date.canonical_to_date instead of to_date for selecting
127 report end date from ppa.legislative paramenters */
128 /* Bug 4215439 - Added ORDERED hint TO the assignment cursor*/
129 /*Bug 5348307 - Commented condition
130 c_report_end_date BETWEEN paa.effective_start_date AND paa.effective_end_date
131 and condition
132 paa.effective_start_date between c_report_end_date - 13 and c_report_end_date */
133 /* Bug 8352719 - Removed NULL termination date check at 2 places and a redundant join*/
134 /* Bug 12377615- Changed to STAR_TRANSFORMATION hint from ORDERED */
135 CURSOR process_assignments
136 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
137 c_start_person_id in per_all_people_f.person_id%TYPE,
138 c_end_person_id in per_all_people_f.person_id%TYPE,
139 c_business_group_id in per_business_groups.business_group_id%TYPE,
140 c_legal_employer_id in hr_soft_coding_keyflex.segment1%TYPE,
141 c_report_end_date in date) IS
142 SELECT /*+ STAR_TRANSFORMATION */ paa.assignment_id,
143 decode(sign(nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) - c_report_end_date),1,
144 null,pps.actual_termination_date) actual_termination_date,
145 peev.screen_entry_value tax_file_number
146 FROM pay_payroll_actions ppa
147 ,per_people_f pap
148 ,per_assignments_f paa
149 ,hr_soft_coding_keyflex hsc
150 ,per_periods_of_service pps
151 ,pay_element_entries_f pee
152 ,pay_element_links_f pel
153 ,pay_element_types_f pet
154 ,pay_input_values_f piv
155 ,pay_element_entry_values_f peev
156 WHERE ppa.payroll_action_id = c_payroll_action_id
157 AND pap.person_id BETWEEN c_start_person_id AND c_end_person_id
158 AND pap.person_id = paa.person_id
159 AND paa.business_group_id = c_business_group_id
160 AND pap.business_group_id = ppa.business_group_id
161 AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
162 AND hsc.segment1 = c_legal_employer_id
163 AND pps.person_id = paa.person_id
164 And pps.period_of_service_id = paa.period_of_service_id
165 AND pps.date_start= (select max(pps1.date_start)
166 from per_periods_of_service pps1
167 where pps1.person_id=pps.person_id
168 AND pps1.date_start <= c_report_end_date
169 ) /*Bug2751008*/
170 AND paa.effective_start_date = (SELECT max(effective_Start_date)
171 FROM per_assignments_f a
172 WHERE a.assignment_id = paa.assignment_id
173 and a.effective_start_date <= c_report_end_Date /*5474358 */
174 group by a.assignment_id
175 )
176 AND pap.effective_start_date = (SELECT max(effective_Start_date)
177 FROM per_people_f p
178 WHERE p.person_id = pap.person_id
179 and p.effective_start_Date <= c_report_end_date /*5474358 */
180 group by p.person_id)
181 AND pet.element_name = 'Tax Information'
182 AND pel.element_type_id = pet.element_type_id
183 AND pee.element_link_id = pel.element_link_id
184 AND pee.assignment_id = paa.assignment_id
185 AND pee.entry_information_category = 'AU_TAX DEDUCTIONS'
186 AND ((trunc(fnd_date.canonical_to_date(pee.entry_information1)) BETWEEN c_report_end_date -13 AND c_report_end_date) /* 8352719 */
187 OR (nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) BETWEEN c_report_end_date - 13 AND c_report_end_date and peev.screen_entry_value = '111 111 111'))
188 AND piv.name = 'Tax File Number'
189 AND piv.element_type_id = pet.element_type_id
190 AND peev.input_value_id = piv.input_value_id
191 AND peev.element_entry_id = pee.element_entry_id
192 AND pee.effective_start_date =
193 (SELECT max(pee1.effective_start_date)
194 FROM pay_element_types_f pet1
195 ,pay_element_links_f pel1
196 ,pay_element_entries_f pee1
197 WHERE pet1.element_name = 'Tax Information'
198 AND pet1.element_type_id = pel1.element_type_id
199 AND pel1.element_link_id = pee1.element_link_id
200 AND pee1.assignment_id = paa.assignment_id
201 AND pee1.entry_information1 is not null /*Bug 5356467*/
202 AND pee1.effective_start_date <= c_report_end_date
203 AND pel1.effective_start_date BETWEEN pet1.effective_start_date
204 AND pet1.effective_end_date
205 )
206 AND peev.effective_start_date = (SELECT max(peev1.effective_start_date)
207 FROM pay_element_entry_values_f peev1
208 WHERE peev1.element_entry_value_id = peev.element_entry_value_id
209 AND peev1.effective_start_date <= c_report_end_date)
210 /* 8352719 - removed the redundant join */
211 AND c_report_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date /* 4620635 */
212 AND c_report_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
213 AND c_report_end_date BETWEEN pel.effective_start_date AND pel.effective_end_date
214 AND c_report_end_date BETWEEN piv.effective_start_date AND piv.effective_end_date
215 AND NOT EXISTS
216 (SELECT 1
217 FROM pay_payroll_actions ppa,
218 pay_assignment_actions pac
219 WHERE pac.assignment_id = paa.assignment_id
220 AND ppa.payroll_Action_id = pac.payroll_action_id
221 AND fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORT_END_DATE',ppa.legislative_parameters))
222 BETWEEN c_report_end_date - 13 AND c_report_end_date
223 AND pac.action_status = 'C'
224 AND ppa.action_TYPE = 'X'
225 AND ppa.report_TYPE = 'AU_TFN_MAGTAPE');
226
227
228
229 ----------------------------------------------------------------------------------
230 -- Cursor to fetch the passed parameters to the magtape process
231 ----------------------------------------------------------------------------------
232 /* Bug 2974527 - Changed the cursor to consider report end date -1 */
233 /* Bug 3229452 - Used fnd_date.canonical_to_date instead of to_date for selecting
234 report end date from ppa.legislative paramenters */
235 CURSOR c_get_parameters( c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE) IS
236 SELECT
237 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
238 pay_core_utils.get_parameter('LEGAL_EMPLOYER',ppa.legislative_parameters),
239 fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORT_END_DATE',ppa.legislative_parameters))-1
240 FROM pay_payroll_actions ppa
241 WHERE ppa.payroll_action_id = c_payroll_action_id;
242
243 l_business_group_id per_business_groups.business_group_id%TYPE;
244 l_legal_employer_id hr_soft_coding_keyflex.segment1%TYPE;
245 l_report_end_date date;
246
247 CURSOR next_action_id IS
248 SELECT pay_assignment_actions_s.nextval
249 FROM dual;
250
251 BEGIN
252
253 hr_utility.set_location('Start of assignment_action_code',3);
254
255 OPEN c_get_parameters(p_payroll_action_id);
256 FETCH c_get_parameters INTO l_business_group_id,
257 l_legal_employer_id,
258 l_report_end_date;
259 CLOSE c_get_parameters;
260
261
262 FOR process_rec in process_assignments (p_payroll_action_id,
263 p_start_person_id,
264 p_end_person_id,
265 l_business_group_id,
266 l_legal_employer_id,
267 l_report_end_date)
268 LOOP
269 EXIT WHEN process_assignments%NOTFOUND;
270
271 -- If the employee's TFN is '111 111 111' and not terminated in the current reporting
272 -- period, then employee is not eligible for magtape.
273
274 IF (process_rec.tax_file_number= '111 111 111' AND
275 nvl(process_rec.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) NOT BETWEEN (l_report_end_date - 13)
276 AND l_report_end_date) THEN
277 hr_utility.trace('Employee not eligible for magtape');
278 ELSE
279 hr_utility.trace(' In the assignment action insertion ');
280
281 OPEN next_action_id;
282 FETCH next_action_id INTO v_next_action_id;
283 CLOSE next_action_id;
284
285 hr_nonrun_asact.insact(v_next_action_id,
286 process_rec.assignment_id,
287 p_payroll_action_id,
288 p_chunk,
289 null);
290 END IF;
291
292
293 END LOOP;
294
295 hr_utility.set_location('End of assignment_action_code',5);
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 hr_utility.trace('Error in assignment_action_code');
300 RAISE;
301
302 END assignment_action_code;
303
304
305 ----------------------------------------------------------------------------+
306 -- This IS used by legislation groups to set global contexts that are
307 -- required for the lifetime of the archiving process.
308 -- We call the procedure 'populate_tfn_flags' to populate the
309 -- plsql table as the table used by the cursor in magtape to select the
310 -- eligible employees to print on the magtape.
311 -----------------------------------------------------------------------------+
312
313 PROCEDURE initialization_code
314 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE) IS
315
316
317 ----------------------------------------------------------------------------------
318 -- CURSOR to FETCH the passed parameters to the magtape process
319 ----------------------------------------------------------------------------------
320 /* Bug 2974527 - Changed the cursor to consider report end date -1 */
321 /* Bug 3229452 - Used fnd_date.canonical_to_date instead of to_date for selecting
322 report end date from ppa.legislative paramenters */
323 CURSOR c_get_parameters( c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE) IS
324 SELECT
325 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
326 pay_core_utils.get_parameter('LEGAL_EMPLOYER',ppa.legislative_parameters),
327 fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORT_END_DATE',ppa.legislative_parameters))-1
328 FROM pay_payroll_actions ppa
329 WHERE ppa.payroll_action_id = c_payroll_action_id;
330
331 l_business_group_id per_business_groups.business_group_id%TYPE;
332 l_legal_employer_id hr_soft_coding_keyflex.segment1%TYPE;
333 l_report_end_date date;
334
335
336 BEGIN
337
338 hr_utility.set_location('Start of initialization_code',6);
339
340 OPEN c_get_parameters(p_payroll_action_id);
341 FETCH c_get_parameters INTO l_business_group_id,
342 l_legal_employer_id,
343 l_report_end_date;
344 CLOSE c_get_parameters;
345
346 --Call populate_tfn_flags to populate the plsql tables
347
348 /* Bug 4066194 - Changed procedure call to refer to
349 procedure in package - pay_au_tfn_magtape_flags
350 */
351
352
353 pay_au_tfn_magtape_flags.populate_tfn_flags(p_payroll_action_id,
354 to_number(l_business_group_id),
355 l_legal_employer_id,
356 l_report_end_date);
357
358
359 hr_utility.set_location('End of initialization_code',7);
360
361 EXCEPTION
362 WHEN OTHERS THEN
363 hr_utility.trace('Error in initialization_code');
364 RAISE;
365
366 END initialization_code;
367
368
369 ------------------------------------------------------------------------------+
370 -- Used to actually perform the archival of data. We are not archiving
371 -- any data here, so thIS IS null.
372 -----------------------------------------------------------------------------+
373
374 PROCEDURE archive_code
375 (p_payroll_action_id in pay_assignment_actions.payroll_action_id%TYPE,
376 p_effective_date in date) IS
377
378 BEGIN
379 hr_utility.set_location('Start of archive_code',8);
380 null;
381 hr_utility.set_location('End of archive_code',9);
382 END archive_code;
383
384
385 END pay_au_tfn_magtape;
[Home] [Help]