1 PACKAGE BODY pay_au_tfn_magtape AS
2 /* $Header: pyautfn.pkb 120.4.12000000.4 2007/02/11 22:14:28 hnainani noship $*/
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 CURSOR process_assignments
134 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
135 c_start_person_id in per_all_people_f.person_id%TYPE,
136 c_end_person_id in per_all_people_f.person_id%TYPE,
137 c_business_group_id in per_business_groups.business_group_id%TYPE,
138 c_legal_employer_id in hr_soft_coding_keyflex.segment1%TYPE,
139 c_report_end_date in date) IS
140 SELECT /*+ ORDERED */ paa.assignment_id,
141 decode(sign(nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) - c_report_end_date),1,
142 null,pps.actual_termination_date) actual_termination_date,
143 peev.screen_entry_value tax_file_number
144 FROM pay_payroll_actions ppa
145 ,per_people_f pap
146 ,per_assignments_f paa
147 ,hr_soft_coding_keyflex hsc
148 ,per_periods_of_service pps
149 ,pay_element_entries_f pee
150 ,pay_element_links_f pel
151 ,pay_element_types_f pet
152 ,pay_input_values_f piv
153 ,pay_element_entry_values_f peev
154 WHERE ppa.payroll_action_id = c_payroll_action_id
155 AND pap.person_id BETWEEN c_start_person_id AND c_end_person_id
156 AND pap.person_id = paa.person_id
157 AND paa.business_group_id = c_business_group_id
158 AND pap.business_group_id = ppa.business_group_id
159 AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
160 AND hsc.segment1 = c_legal_employer_id
161 AND pps.person_id = paa.person_id
162 AND pps.date_start= (select max(pps1.date_start)
163 from per_periods_of_service pps1
164 where pps1.person_id=pps.person_id
165 AND pps1.date_start <= c_report_end_date
166 ) /*Bug2751008*/
167 AND paa.effective_start_date = (SELECT max(effective_Start_date)
168 FROM per_assignments_f a
169 WHERE a.assignment_id = paa.assignment_id
170 and a.effective_start_date <= c_report_end_Date /*5474358 */
171 group by a.assignment_id
172 )
173 AND pap.effective_start_date = (SELECT max(effective_Start_date)
174 FROM per_people_f p
175 WHERE p.person_id = pap.person_id
176 and p.effective_start_Date <= c_report_end_date /*5474358 */
177 group by p.person_id)
178 AND pet.element_name = 'Tax Information'
179 AND pel.element_type_id = pet.element_type_id
180 AND pee.element_link_id = pel.element_link_id
181 AND pee.assignment_id = paa.assignment_id
182 AND pee.entry_information_category = 'AU_TAX DEDUCTIONS'
183 AND ((trunc(fnd_date.canonical_to_date(pee.entry_information1)) BETWEEN c_report_end_date -13 AND c_report_end_date
184 and pps.actual_termination_date is null)
185 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'))
186 AND piv.name = 'Tax File Number'
187 AND piv.element_type_id = pet.element_type_id
188 AND peev.input_value_id = piv.input_value_id
189 AND peev.element_entry_id = pee.element_entry_id
190 AND pee.effective_start_date =
191 (SELECT max(pee1.effective_start_date)
192 FROM pay_element_types_f pet1
193 ,pay_element_links_f pel1
194 ,pay_element_entries_f pee1
195 WHERE pet1.element_name = 'Tax Information'
196 AND pet1.element_type_id = pel1.element_type_id
197 AND pel1.element_link_id = pee1.element_link_id
198 AND pee1.assignment_id = paa.assignment_id
199 AND pee1.entry_information1 is not null /*Bug 5356467*/
200 AND pee1.effective_start_date <= c_report_end_date
201 AND pel1.effective_start_date BETWEEN pet1.effective_start_date
202 AND pet1.effective_end_date
203 )
204 AND peev.effective_start_date = (SELECT max(peev1.effective_start_date)
205 FROM pay_element_entry_values_f peev1
206 WHERE peev1.element_entry_value_id = peev.element_entry_value_id
207 AND peev1.effective_start_date <= c_report_end_date)
208 /* 4620635 */
209 AND ((
210 /* Begin commented for bug 5348307*/
211 /* c_report_end_date BETWEEN paa.effective_start_date AND paa.effective_end_date
212 and*/
213 /* End commented for bug 5348307*/
214 pps.actual_termination_date is null)
215 OR (nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'))
216 BETWEEN c_report_end_date - 13 AND c_report_end_date and peev.screen_entry_value = '111 111 111'
217 /* Begin commented for bug 5348307*/
218 /* and paa.effective_start_date between c_report_end_date - 13 and c_report_end_date */
219 /* End commented for bug 5348307*/
220 ))
221 AND c_report_end_date BETWEEN pap.effective_start_date AND pap.effective_end_date /* 4620635 */
222 AND c_report_end_date BETWEEN pet.effective_start_date AND pet.effective_end_date
223 AND c_report_end_date BETWEEN pel.effective_start_date AND pel.effective_end_date
224 AND c_report_end_date BETWEEN piv.effective_start_date AND piv.effective_end_date
225 AND NOT EXISTS
226 (SELECT 1
227 FROM pay_payroll_actions ppa,
228 pay_assignment_actions pac
229 WHERE pac.assignment_id = paa.assignment_id
230 AND ppa.payroll_Action_id = pac.payroll_action_id
231 AND fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORT_END_DATE',ppa.legislative_parameters))
232 BETWEEN c_report_end_date - 13 AND c_report_end_date
233 AND pac.action_status = 'C'
234 AND ppa.action_TYPE = 'X'
235 AND ppa.report_TYPE = 'AU_TFN_MAGTAPE');
236
237
238
239 ----------------------------------------------------------------------------------
240 -- Cursor to fetch the passed parameters to the magtape process
241 ----------------------------------------------------------------------------------
242 /* Bug 2974527 - Changed the cursor to consider report end date -1 */
243 /* Bug 3229452 - Used fnd_date.canonical_to_date instead of to_date for selecting
244 report end date from ppa.legislative paramenters */
245 CURSOR c_get_parameters( c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE) IS
246 SELECT
247 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
248 pay_core_utils.get_parameter('LEGAL_EMPLOYER',ppa.legislative_parameters),
249 fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORT_END_DATE',ppa.legislative_parameters))-1
250 FROM pay_payroll_actions ppa
251 WHERE ppa.payroll_action_id = c_payroll_action_id;
252
253 l_business_group_id per_business_groups.business_group_id%TYPE;
254 l_legal_employer_id hr_soft_coding_keyflex.segment1%TYPE;
258 SELECT pay_assignment_actions_s.nextval
255 l_report_end_date date;
256
257 CURSOR next_action_id IS
259 FROM dual;
260
261 BEGIN
262
263 hr_utility.set_location('Start of assignment_action_code',3);
264
265 OPEN c_get_parameters(p_payroll_action_id);
266 FETCH c_get_parameters INTO l_business_group_id,
267 l_legal_employer_id,
268 l_report_end_date;
269 CLOSE c_get_parameters;
270
271
272 FOR process_rec in process_assignments (p_payroll_action_id,
273 p_start_person_id,
274 p_end_person_id,
275 l_business_group_id,
276 l_legal_employer_id,
277 l_report_end_date)
278 LOOP
279 EXIT WHEN process_assignments%NOTFOUND;
280
281 -- If the employee's TFN is '111 111 111' and not terminated in the current reporting
282 -- period, then employee is not eligible for magtape.
283
284 IF (process_rec.tax_file_number= '111 111 111' AND
285 nvl(process_rec.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) NOT BETWEEN (l_report_end_date - 13)
286 AND l_report_end_date) THEN
287 hr_utility.trace('Employee not eligible for magtape');
288 ELSE
289 hr_utility.trace(' In the assignment action insertion ');
290
291 OPEN next_action_id;
292 FETCH next_action_id INTO v_next_action_id;
293 CLOSE next_action_id;
294
295 hr_nonrun_asact.insact(v_next_action_id,
296 process_rec.assignment_id,
297 p_payroll_action_id,
298 p_chunk,
299 null);
300 END IF;
301
302
303 END LOOP;
304
305 hr_utility.set_location('End of assignment_action_code',5);
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 hr_utility.trace('Error in assignment_action_code');
310 RAISE;
311
312 END assignment_action_code;
313
314
315 ----------------------------------------------------------------------------+
316 -- This IS used by legislation groups to set global contexts that are
317 -- required for the lifetime of the archiving process.
318 -- We call the procedure 'populate_tfn_flags' to populate the
319 -- plsql table as the table used by the cursor in magtape to select the
320 -- eligible employees to print on the magtape.
321 -----------------------------------------------------------------------------+
322
323 PROCEDURE initialization_code
324 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE) IS
325
326
327 ----------------------------------------------------------------------------------
328 -- CURSOR to FETCH the passed parameters to the magtape process
329 ----------------------------------------------------------------------------------
330 /* Bug 2974527 - Changed the cursor to consider report end date -1 */
331 /* Bug 3229452 - Used fnd_date.canonical_to_date instead of to_date for selecting
332 report end date from ppa.legislative paramenters */
333 CURSOR c_get_parameters( c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE) IS
334 SELECT
335 pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
336 pay_core_utils.get_parameter('LEGAL_EMPLOYER',ppa.legislative_parameters),
337 fnd_date.canonical_to_date(pay_core_utils.get_parameter('REPORT_END_DATE',ppa.legislative_parameters))-1
338 FROM pay_payroll_actions ppa
339 WHERE ppa.payroll_action_id = c_payroll_action_id;
340
341 l_business_group_id per_business_groups.business_group_id%TYPE;
342 l_legal_employer_id hr_soft_coding_keyflex.segment1%TYPE;
343 l_report_end_date date;
344
345
346 BEGIN
347
348 hr_utility.set_location('Start of initialization_code',6);
349
350 OPEN c_get_parameters(p_payroll_action_id);
351 FETCH c_get_parameters INTO l_business_group_id,
352 l_legal_employer_id,
353 l_report_end_date;
354 CLOSE c_get_parameters;
355
356 --Call populate_tfn_flags to populate the plsql tables
357
358 /* Bug 4066194 - Changed procedure call to refer to
359 procedure in package - pay_au_tfn_magtape_flags
360 */
361
362
363 pay_au_tfn_magtape_flags.populate_tfn_flags(p_payroll_action_id,
364 to_number(l_business_group_id),
365 l_legal_employer_id,
366 l_report_end_date);
367
368
369 hr_utility.set_location('End of initialization_code',7);
370
371 EXCEPTION
372 WHEN OTHERS THEN
373 hr_utility.trace('Error in initialization_code');
374 RAISE;
375
376 END initialization_code;
377
378
379 ------------------------------------------------------------------------------+
380 -- Used to actually perform the archival of data. We are not archiving
381 -- any data here, so thIS IS null.
382 -----------------------------------------------------------------------------+
383
384 PROCEDURE archive_code
385 (p_payroll_action_id in pay_assignment_actions.payroll_action_id%TYPE,
386 p_effective_date in date) IS
387
388 BEGIN
389 hr_utility.set_location('Start of archive_code',8);
390 null;
391 hr_utility.set_location('End of archive_code',9);
392 END archive_code;
393
394
395 END pay_au_tfn_magtape;
[Home] [Help]