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