[Home] [Help]
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: ---------------------------------------------------------------------
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'
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'
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'
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'
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')
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'
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')
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')
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')
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')
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'))