[Home] [Help]
PACKAGE: APPS.PAY_SG_IRAS_MAGTAPE
Source
1 package pay_sg_iras_magtape AUTHID CURRENT_USER as
2 /* $Header: pysgirmt.pkh 120.1.12010000.9 2009/06/04 05:40:50 jalin ship $ */
3
4 level_cnt number;
5 g_report_type VARCHAR2(20);
6 g_file VARCHAR2(20);
7
8 --------------------------------------------------------------------
9 -- These are PUBLIC procedures are required by the Archive process.
10 -- Their names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
11 -- the archive process knows what code to execute for each step of
12 -- the archive.
13 --------------------------------------------------------------------
14 procedure range_code
15 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
16 p_sql out nocopy varchar2);
17
18 procedure assignment_action_code
19 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
20 p_start_person_id in per_all_people_f.person_id%type,
21 p_end_person_id in per_all_people_f.person_id%type,
22 p_chunk in number);
23
24 procedure initialization_code
25 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type);
26
27 procedure archive_code
28 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
29 p_effective_date in date);
30
31 -- commented out for bug 5616519
32 -- function return_indicator_flag( p_assignment_id in number,
33 -- p_report_type in varchar2 ) return varchar2 ;
34 ---------------------------------------------------------------------
35 -- PUBLIC cursors to retrieve data and pass it to IRAS formulae that
36 -- will write out header information for the 3 file types.
37 -- Bug 3232300: Added joins for table ff_archive_items and ff_database_items
38 -- so that it selects only that assignment action id against
39 -- which archived header information exist.
40
41 ---------------------------------------------------------------------
42 -- Passes parameters to SG_IRAS_HEADER formula
43 cursor ir8a_header is
44 select 'ASSIGNMENT_ACTION_ID=C',
45 aac.assignment_action_id,
46 'APPLICATION_REFERENCE=P',
47 'IR8A' application_reference,
48 'BASIS_YEAR=P',
49 pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters) basis_year,
50 'CREATION_DATE=P',
51 to_char(sysdate,'YYYYMMDD') creation_date
52 from pay_payroll_actions mpa,
53 pay_payroll_actions apa,
54 pay_assignment_actions aac,
55 ff_archive_items ffi,
56 ff_database_items fdi
57 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
58 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
59 and apa.payroll_action_id = aac.payroll_action_id
60 and apa.action_status = 'C'
61 and ffi.context1 = aac.assignment_action_id /* Added joins for bug:3232300 */
62 and fdi.user_name = 'X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME'
63 and ffi.user_entity_id = fdi.user_entity_id
64 and rownum=1;
65
66 -- Passes parameters to SG_IRAS_HEADER formula
67 cursor ir8s_header is
68 select 'ASSIGNMENT_ACTION_ID=C',
69 aac.assignment_action_id,
70 'APPLICATION_REFERENCE=P',
71 'IR8S' application_reference,
72 'BASIS_YEAR=P',
73 pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters) basis_year,
74 'CREATION_DATE=P',
75 to_char(sysdate,'YYYYMMDD') creation_date
76 from pay_payroll_actions mpa,
77 pay_payroll_actions apa,
78 pay_assignment_actions aac
79 ,ff_archive_items ffi
80 ,ff_database_items fdi
81 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
82 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
83 and apa.payroll_action_id = aac.payroll_action_id
84 and apa.action_status = 'C'
85 and ffi.context1 = aac.assignment_action_id /* Added joins for bug:3232300 */
86 and fdi.user_name = 'X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME'
87 and ffi.user_entity_id = fdi.user_entity_id
88 and rownum=1;
89
90 -- Passes parameters to SG_IRAS_HEADER formula
91 cursor a8a_header is
92 select 'ASSIGNMENT_ACTION_ID=C',
93 aac.assignment_action_id,
94 'APPLICATION_REFERENCE=P',
95 'IRA8A' application_reference,
96 'BASIS_YEAR=P',
97 pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters) basis_year,
98 'CREATION_DATE=P',
99 to_char(sysdate,'YYYYMMDD') creation_date
100 from pay_payroll_actions mpa,
101 pay_payroll_actions apa,
102 pay_assignment_actions aac,
103 ff_archive_items ffi,
104 ff_database_items fdi
105 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
106 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
107 and apa.payroll_action_id = aac.payroll_action_id
108 and apa.action_status = 'C'
109 and ffi.context1 = aac.assignment_action_id /* Added joins for bug:3232300 */
110 and fdi.user_name = 'X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME'
111 and ffi.user_entity_id = fdi.user_entity_id
112 and rownum=1;
113
114 -- Passes parameters to SG_IRAS_HEADER formula
115 cursor a8b_header is
116 select 'ASSIGNMENT_ACTION_ID=C',
117 aac.assignment_action_id,
118 'APPLICATION_REFERENCE=P',
119 'IRA8B' application_reference,
120 'BASIS_YEAR=P',
121 pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters) basis_year,
122 'CREATION_DATE=P',
123 to_char(sysdate,'YYYYMMDD') creation_date
124 from pay_payroll_actions mpa,
125 pay_payroll_actions apa,
126 pay_assignment_actions aac,
127 ff_archive_items ffi,
128 ff_database_items fdi
129 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
130 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
131 and apa.payroll_action_id = aac.payroll_action_id
132 and apa.action_status = 'C'
133 and ffi.context1 = aac.assignment_action_id /* Added joins for bug:3232300 */
134 and fdi.user_name = 'X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME'
135 and ffi.user_entity_id = fdi.user_entity_id
136 and rownum=1;
137
138 ---------------------------------------------------------------------
139 -- PUBLIC cursor used to pass parameters to SG_IR8A_DETAILS,
140 -- SG_IR8S_DETAILS, and SG_A8A_DETAILS formulae.
141 -- Not all parameters are required by all formulae, but it is more
142 -- efficient and easily maintainable if 1 cursor is used for all 3
143 -- detail sections of the magtapes.
144 -- Bug 3249481 - Added function call pay_sg_iras_magtape.return_indicator_flag( )
145 -- Bug 5616519 - Removed function call pay_sg_iras_magtape.return_indicator_flag( )
146 ---------------------------------------------------------------------
147 cursor iras_details is
148 select distinct
149 'ASSIGNMENT_ACTION_ID=C',
150 aac.assignment_action_id,
151 'TAX_UNIT_ID=C',
152 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', apa.legislative_parameters) tax_unit_id,
153 'BASIS_YEAR=P',
154 pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters) basis_year,
155 'P_ASSIGNMENT_ACTION_ID=P',
156 to_char(mac.assignment_action_id),
157 'ASSIGNMENT_ID=P',
158 to_char(paa.assignment_id) assignment_id,
159 'PERSON_ID=P',
160 to_char(paa.person_id) person_id,
161 'ASSIGNMENT_NUMBER=P',
162 paa.assignment_number assignment_number
163 -- 'RECORD_INDICATOR_FLAG=P',
164 -- pay_sg_iras_magtape.return_indicator_flag(paa.assignment_id ,mpa.report_type)
165 from per_all_assignments_f paa,
166 pay_payroll_actions mpa,
167 pay_assignment_actions mac,
168 pay_payroll_actions apa,
169 pay_assignment_actions aac
170 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
171 and mpa.payroll_action_id = mac.payroll_action_id
172 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
173 and apa.payroll_action_id = aac.payroll_action_id
174 and paa.assignment_id = aac.assignment_id
175 and apa.action_status = 'C'
176 and aac.action_status = 'C'
177 and (paa.effective_start_date <= to_date('31/' || '12/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy')
178 and paa.effective_end_date >= to_date('01/' || '01/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy'))
179 and paa.assignment_id = mac.assignment_id
180 order by paa.person_id;
181
182 ---------------------------------------------------------------------
183 -- PUBLIC cursors used to retrieve data and pass it to IR8S formulae
184 -- as this magtape has more detailed records.
185 -- Modified for Bug 2672462, Bug 2654499
186 ---------------------------------------------------------------------
187 -- Passes parameters to SG_IR8S_MONTH_DETAILS formula
188 cursor ir8s_month_details is
189 select distinct
190 'ASSIGNMENT_ACTION_ID=C',
191 aac.assignment_action_id,
192 'TAX_UNIT_ID=C',
193 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', apa.legislative_parameters) tax_unit_id,
194 'DATE_EARNED=C',
195 ac3.context date_earned,
196 'MONTH=P',
197 to_char(fnd_date.canonical_to_date(ac3.context),'MON') month,
198 'PERSON_ID=P',
199 to_char(paa.person_id) person_id,
200 'DATE_ORDER=P',
201 fnd_date.canonical_to_date(ac3.context) date_order
202 from per_all_assignments_f paa,
203 pay_payroll_actions apa,
204 pay_assignment_actions aac,
205 pay_payroll_actions mpa,
206 ff_archive_items fai,
207 ff_archive_item_contexts ac2,
208 ff_archive_item_contexts ac3,
209 ff_user_entities ffe,
210 ff_routes ffr
211 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
212 and paa.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
213 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
214 and apa.payroll_action_id = aac.payroll_action_id
215 and paa.assignment_id = aac.assignment_id
216 and aac.assignment_action_id = fai.context1
217 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
218 and fai.archive_item_id = ac3.archive_item_id and ac3.sequence_no = 3
219 and apa.action_status = 'C'
220 and fai.user_entity_id = ffe.user_entity_id
221 and ffe.route_id = ffr.route_id
222 and ffr.route_name = 'SG IRAS Month Balances Archive'
223 and (paa.effective_start_date <= to_date('31/' || '12/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy')
224 and paa.effective_end_date >= to_date('01/' || '01/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy'))
225 order by paa.person_id, fnd_date.canonical_to_date(ac3.context);
226
227 ---------------------------------------------------------------------
228 -- Passes parameters to SG_IR8S_WAGE_DETAILS
229 -- Modified for bug 3027801
230 -- Bug 3249481 - Added person_id check for performance
231 ---------------------------------------------------------------------
232 cursor ir8s_wage_details is
233 select distinct 'ASSIGNMENT_ACTION_ID=C',
234 aac.assignment_action_id,
235 'TAX_UNIT_ID=C',
236 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', apa.legislative_parameters) tax_unit_id,
237 'SOURCE_ID=C',
238 ac3.context source_id,
239 'ASS_EXTRA_ID=P',
240 ac3.context ass_extra_id,
241 'PERSON_ID=P',
242 paa.person_id
243 from pay_payroll_actions mpa,
244 pay_payroll_actions apa,
245 pay_assignment_actions aac,
246 per_all_assignments_f paa,
247 ff_archive_items fai,
248 ff_archive_items fai1,
249 ff_database_items fdi,
250 ff_archive_item_contexts ac2,
251 ff_archive_item_contexts ac3
252 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
253 and paa.person_id = pay_magtape_generic.get_parameter_value('PERSON_ID')
254 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
255 and apa.payroll_action_id = aac.payroll_action_id
256 and aac.assignment_id = paa.assignment_id
257 and aac.assignment_action_id = fai.context1
258 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
259 and fai.archive_item_id = ac3.archive_item_id and ac3.sequence_no = 3
260 and fai.user_entity_id in (
261 select user_entity_id
262 from ff_database_items
263 where user_name = 'X_MOA410')
264 and fai.context1 = fai1.context1
265 and fai1.user_entity_id = fdi.user_entity_id
266 and fdi.user_name = 'X_IR8S_C_INVALID_RECORDS'
267 and fai1.value = 'Y'
268 and (paa.effective_start_date <= to_date('31/' || '12/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy')
269 and paa.effective_end_date >= to_date('01/' || '01/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy'))
270 order by paa.person_id, ac3.context;
271
272
273 -- Bug 3501956. Modified to retrieve information from PER_PEOPLE_EXTRA_INFO
274 cursor a8b_esop_details is
275 select distinct 'ASSIGNMENT_ACTION_ID=C',
276 aac.assignment_action_id,
277 'TAX_UNIT_ID=C',
278 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', apa.legislative_parameters) tax_unit_id,
279 'SOURCE_ID=C',
280 ac3.context source_id,
281 'PERSON_EXTRA_ID=P', -- 'ASS_EXTRA_ID=P',
282 ac3.context person_extra_id, -- ac3.context ass_extra_id,
283 'PERSON_ID=P',
284 to_char(paa.person_id) person_id
285 from per_all_assignments_f paa,
286 pay_payroll_actions mpa,
287 pay_assignment_actions mac,
288 pay_payroll_actions apa,
289 pay_assignment_actions aac,
290 ff_archive_items fai,
291 ff_archive_item_contexts ac2,
292 ff_archive_item_contexts ac3
293 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
294 and paa.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
295 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
296 and apa.payroll_action_id = aac.payroll_action_id
297 and paa.assignment_id = aac.assignment_id
298 and apa.action_status = 'C'
299 and aac.assignment_action_id = fai.context1
300 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
301 and fai.archive_item_id = ac3.archive_item_id and ac3.sequence_no = 3
302 and fai.user_entity_id in (
303 select user_entity_id
304 from ff_database_items
305 where user_name = 'X_A8B_OPTION')
306 and fai.value = 'E'
307 /* Bug 2654499 */
308 and (paa.effective_start_date <= to_date('31/' || '12/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy')
309 and paa.effective_end_date >= to_date('01/' || '01/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy'))
310 and paa.assignment_id = mac.assignment_id /* Bug 2676415 */
311 order by paa.person_id, ac3.context;
312
313 -- Bug 3501956. Modified to retrieve information from PER_PEOPLE_EXTRA_INFO
314 cursor a8b_eesop_details is
315 select distinct 'ASSIGNMENT_ACTION_ID=C',
316 aac.assignment_action_id,
317 'TAX_UNIT_ID=C',
318 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', apa.legislative_parameters) tax_unit_id,
319 'SOURCE_ID=C',
320 ac3.context source_id,
321 'PERSON_EXTRA_ID=P', -- 'ASS_EXTRA_ID=P',
322 ac3.context person_extra_id, -- ac3.context ass_extra_id,
323 'PERSON_ID=P',
324 to_char(paa.person_id) person_id
325 from per_all_assignments_f paa,
326 pay_payroll_actions mpa,
327 pay_assignment_actions mac,
328 pay_payroll_actions apa,
329 pay_assignment_actions aac,
330 ff_archive_items fai,
331 ff_archive_item_contexts ac2,
332 ff_archive_item_contexts ac3
333 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
334 and paa.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
335 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
336 and apa.payroll_action_id = aac.payroll_action_id
337 and paa.assignment_id = aac.assignment_id
338 and apa.action_status = 'C'
339 and aac.assignment_action_id = fai.context1
340 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
341 and fai.archive_item_id = ac3.archive_item_id and ac3.sequence_no = 3
342 and fai.user_entity_id in (
343 select user_entity_id
344 from ff_database_items
345 where user_name = 'X_A8B_OPTION')
346 and fai.value = 'EE'
347 /* Bug 2654499 */
348 and (paa.effective_start_date <= to_date('31/' || '12/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy')
349 and paa.effective_end_date >= to_date('01/' || '01/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy'))
350 and paa.assignment_id = mac.assignment_id
351 order by paa.person_id, ac3.context;
352
353 -- Bug 3501956. Modified to retrieve information from PER_PEOPLE_EXTRA_INFO
354 cursor a8b_csop_details is
355 select distinct 'ASSIGNMENT_ACTION_ID=C',
356 aac.assignment_action_id,
357 'TAX_UNIT_ID=C',
358 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', apa.legislative_parameters) tax_unit_id,
359 'SOURCE_ID=C',
360 ac3.context source_id,
361 'PERSON_EXTRA_ID=P', -- 'ASS_EXTRA_ID=P'
362 ac3.context person_extra_id, -- ac3.context ass_extra_id
363 'PERSON_ID=P',
364 to_char(paa.person_id) person_id
365 from per_all_assignments_f paa,
366 pay_payroll_actions mpa,
367 pay_assignment_actions mac,
368 pay_payroll_actions apa,
369 pay_assignment_actions aac,
370 ff_archive_items fai,
371 ff_archive_item_contexts ac2,
372 ff_archive_item_contexts ac3
373 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
374 and paa.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
375 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
376 and apa.payroll_action_id = aac.payroll_action_id
377 and paa.assignment_id = aac.assignment_id
378 and apa.action_status = 'C'
379 and aac.assignment_action_id = fai.context1
380 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
381 and fai.archive_item_id = ac3.archive_item_id and ac3.sequence_no = 3
382 and fai.user_entity_id in (
383 select user_entity_id
384 from ff_database_items
385 where user_name = 'X_A8B_OPTION')
386 and fai.value = 'C'
387 /* Bug 2654499 */
388 and (paa.effective_start_date <= to_date('31/' || '12/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy')
389 and paa.effective_end_date >= to_date('01/' || '01/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy'))
390 and paa.assignment_id = mac.assignment_id
391 order by paa.person_id, ac3.context;
392
393 --Bug 7415444, added new cursor for A8B NSOP details
394 cursor a8b_nsop_details is
395 select distinct 'ASSIGNMENT_ACTION_ID=C',
396 aac.assignment_action_id,
397 'TAX_UNIT_ID=C',
398 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', apa.legislative_parameters) tax_unit_id,
399 'SOURCE_ID=C',
400 ac3.context source_id,
401 'PERSON_EXTRA_ID=P', -- 'ASS_EXTRA_ID=P'
402 ac3.context person_extra_id, -- ac3.context ass_extra_id
403 'PERSON_ID=P',
404 to_char(paa.person_id) person_id
405 from per_all_assignments_f paa,
406 pay_payroll_actions mpa,
407 pay_assignment_actions mac,
408 pay_payroll_actions apa,
409 pay_assignment_actions aac,
410 ff_archive_items fai,
411 ff_archive_item_contexts ac2,
412 ff_archive_item_contexts ac3
413 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
414 and paa.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
415 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
416 and apa.payroll_action_id = aac.payroll_action_id
417 and paa.assignment_id = aac.assignment_id
418 and apa.action_status = 'C'
419 and aac.assignment_action_id = fai.context1
420 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
421 and fai.archive_item_id = ac3.archive_item_id and ac3.sequence_no = 3
422 and fai.user_entity_id in (
423 select user_entity_id
424 from ff_database_items
425 where user_name = 'X_A8B_OPTION')
426 and fai.value = 'N'
427 /* Bug 2654499 */
428 and (paa.effective_start_date <= to_date('31/' || '12/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy')
429 and paa.effective_end_date >= to_date('01/' || '01/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy'))
430 and paa.assignment_id = mac.assignment_id
431 order by paa.person_id, ac3.context;
432
433
434
435 -- Bug 3501956. Modified to retrieve information from PER_PEOPLE_EXTRA_INFO
436 cursor a8b_total_details is
437 select distinct 'ASSIGNMENT_ACTION_ID=C',
438 aac.assignment_action_id,
439 'TAX_UNIT_ID=C',
440 pay_core_utils.get_parameter('LEGAL_ENTITY_ID', apa.legislative_parameters) tax_unit_id,
441 'PERSON_ID=P',
442 to_char(paa.person_id) person_id
443 from per_all_assignments_f paa,
444 pay_payroll_actions mpa,
445 pay_assignment_actions mac,
446 pay_payroll_actions apa,
447 pay_assignment_actions aac,
448 ff_archive_items fai,
449 ff_archive_item_contexts ac2,
450 ff_archive_item_contexts ac3
451 where mpa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
452 and paa.assignment_id = pay_magtape_generic.get_parameter_value('ASSIGNMENT_ID')
453 and apa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_RUN_ID', mpa.legislative_parameters)
454 and apa.payroll_action_id = aac.payroll_action_id
455 and paa.assignment_id = aac.assignment_id
456 and apa.action_status = 'C'
457 and aac.assignment_action_id = fai.context1
458 and fai.archive_item_id = ac2.archive_item_id and ac2.sequence_no = 2
459 and fai.archive_item_id = ac3.archive_item_id and ac3.sequence_no = 3
460 and fai.user_entity_id in (
461 select user_entity_id
462 from ff_database_items
463 where user_name = 'X_A8B_OPTION')
464 /* Bug 2654499 */
465 and (paa.effective_start_date <= to_date('31/' || '12/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy')
466 and paa.effective_end_date >= to_date('01/' || '01/' || pay_core_utils.get_parameter('BASIS_YEAR', apa.legislative_parameters),'dd/mm/yyyy'))
467 and paa.assignment_id = mac.assignment_id
468 order by paa.person_id;
469 end pay_sg_iras_magtape;