[Home] [Help]
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'
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'
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'
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'
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),
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,
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'
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:
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: ---------------------------------------------------------------------
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,
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
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: ---------------------------------------------------------------------
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
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,
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
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:
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,
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')
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:
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,
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')
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:
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
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')
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:
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 */
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')
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;
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;