PAY_REP_DELIVERY

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
KEY TEMPLATE OUTPUT_FORMAT OUTPUT_NAME CALENDAR SAVE_OUTPUT DEL_CHANNEL PARAMETER1 PARAMETER2 PARAMETER3 PARAMETER4 PARAMETER5 PARAMETER6 PARAMETER7 PARAMETER8 PARAMETER9 PARAMETE10 LOCALE TIMEZONE EXT_DELIVERY_OPTION_ID PAYROLL_ACTION_ID DEL_PREF

Name

KEY

TEMPLATE

OUTPUT_FORMAT

OUTPUT_NAME

CALENDAR

SAVE_OUTPUT

DEL_CHANNEL

PARAMETER1

PARAMETER2

PARAMETER3

PARAMETER4

PARAMETER5

PARAMETER6

PARAMETER7

PARAMETER8

PARAMETER9

PARAMETE10

LOCALE

TIMEZONE

EXT_DELIVERY_OPTION_ID

PAYROLL_ACTION_ID

DEL_PREF

Query

SQL_Statement
SELECT pay_report_delivery.get_key_value(data_ele.information_column,pai.action_information_id,'Y') KEY ,del_opt.bip_template_name TEMPLATE ,del_opt.output_type OUTPUT_FORMAT ,pay_report_delivery.get_output_file_name(del_opt.output_name,pact.effective_date,del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) OUTPUT_NAME ,del_opt.calendar_code CALENDAR ,'true' SAVE_OUTPUT ,decode(del_opt.delivery_type,'DOR',null,'NONE',NULL,'HCMCONNECT',NULL,'INBINT',null,'DLINT',null,del_opt.delivery_type) DEL_CHANNEL ,pay_report_delivery.get_del_param_value('1',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER1 ,pay_report_delivery.get_del_param_value('2',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER2 ,pay_report_delivery.get_del_param_value('3',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER3 ,pay_report_delivery.get_del_param_value('4',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER4 ,pay_report_delivery.get_del_param_value('5',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER5 ,pay_report_delivery.get_del_param_value('6',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER6 ,pay_report_delivery.get_del_param_value('7',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER7 ,pay_report_delivery.get_del_param_value('8',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER8 ,pay_report_delivery.get_del_param_value('9',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER9 ,pay_report_delivery.get_del_param_value('10',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETE10 ,pay_report_delivery.get_del_param_value('LOCALE',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) LOCALE ,pay_report_delivery.get_del_param_value('TIMEZONE',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) TIMEZONE ,del_opt.ext_delivery_option_id ,pact.payroll_action_id , pay_report_delivery.get_del_pref(del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) DEL_PREF FROM pay_action_information pai, pay_payroll_actions pact, per_ext_delivery_options_b del_opt, per_ext_delivery_option_dtls del_det, per_ext_data_elements_b data_ele, pay_report_records_f prr where del_opt.ext_delivery_option_id=del_det.ext_delivery_option_id and del_det.option_type= 'KEY' and del_det.option_data_element_id=data_ele.ext_data_element_id and data_ele.report_record_id=prr.report_record_id and pai.report_record_id=prr.report_record_id and ((pay_report_delivery.get_action_context_type(prr.report_block_id)='PPA' and pai.action_context_id=pact.payroll_action_id) or (pay_report_delivery.get_action_context_type(prr.report_block_id)='PRA' and pai.action_context_id in (select payroll_rel_action_id from pay_payroll_rel_actions pra where pra.payroll_action_id=pact.payroll_action_id)) or (pay_report_delivery.get_action_context_type(prr.report_block_id)='POA' and pai.action_context_id in (select object_action_id from pay_object_actions poa where poa.payroll_action_id=pact.payroll_action_id)) or (pay_report_delivery.get_action_context_type(prr.report_block_id)='PTOA' and pai.action_context_id in (select temp_object_action_id from pay_temp_object_actions ptoa where ptoa.payroll_action_id=pact.payroll_action_id))) and pact.effective_date between prr.effective_start_date and prr.effective_end_date union all SELECT pay_report_delivery.get_key_value(data_ele.information_column,pai.action_information_id,'N') KEY ,del_opt.bip_template_name TEMPLATE ,del_opt.output_type OUTPUT_FORMAT ,pay_report_delivery.get_output_file_name(del_opt.output_name,pact.effective_date,del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) OUTPUT_NAME ,del_opt.calendar_code CALENDAR ,'true' SAVE_OUTPUT ,decode(del_opt.delivery_type,'DOR',null,'NONE',NULL,'HCMCONNECT',NULL,'INBINT',null,'DLINT',null,del_opt.delivery_type) DEL_CHANNEL ,pay_report_delivery.get_del_param_value('1',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER1 ,pay_report_delivery.get_del_param_value('2',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER2 ,pay_report_delivery.get_del_param_value('3',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER3 ,pay_report_delivery.get_del_param_value('4',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER4 ,pay_report_delivery.get_del_param_value('5',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER5 ,pay_report_delivery.get_del_param_value('6',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER6 ,pay_report_delivery.get_del_param_value('7',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER7 ,pay_report_delivery.get_del_param_value('8',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER8 ,pay_report_delivery.get_del_param_value('9',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER9 ,pay_report_delivery.get_del_param_value('10',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETE10 ,pay_report_delivery.get_del_param_value('LOCALE',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) LOCALE ,pay_report_delivery.get_del_param_value('TIMEZONE',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) TIMEZONE ,del_opt.ext_delivery_option_id ,pact.payroll_action_id , pay_report_delivery.get_del_pref(del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) DEL_PREF FROM pay_temp_action_information pai, pay_payroll_actions pact, per_ext_delivery_options_b del_opt, per_ext_delivery_option_dtls del_det, per_ext_data_elements_b data_ele, pay_report_records_f prr where pai.payroll_action_id = pact.payroll_action_id and del_opt.ext_delivery_option_id=del_det.ext_delivery_option_id and del_det.option_type= 'KEY' and del_det.option_data_element_id=data_ele.ext_data_element_id and data_ele.report_record_id=prr.report_record_id and pai.report_record_id=prr.report_record_id and ((pay_report_delivery.get_action_context_type(prr.report_block_id)='PPA' and pai.action_context_id=pact.payroll_action_id) or (pay_report_delivery.get_action_context_type(prr.report_block_id)='PRA' and pai.action_context_id in (select payroll_rel_action_id from pay_payroll_rel_actions pra where pra.payroll_action_id=pact.payroll_action_id)) or (pay_report_delivery.get_action_context_type(prr.report_block_id)='POA' and pai.action_context_id in (select object_action_id from pay_object_actions poa where poa.payroll_action_id=pact.payroll_action_id)) or (pay_report_delivery.get_action_context_type(prr.report_block_id)='PTOA' and pai.action_context_id in (select temp_object_action_id from pay_temp_object_actions ptoa where ptoa.payroll_action_id=pact.payroll_action_id))) and pact.effective_date between prr.effective_start_date and prr.effective_end_date

SQL_Statement

SELECT pay_report_delivery.get_key_value(data_ele.information_column,pai.action_information_id,'Y') KEY

,del_opt.bip_template_name TEMPLATE

,del_opt.output_type OUTPUT_FORMAT

,pay_report_delivery.get_output_file_name(del_opt.output_name,pact.effective_date,del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) OUTPUT_NAME

,del_opt.calendar_code CALENDAR

,'true' SAVE_OUTPUT

,decode(del_opt.delivery_type,'DOR',null,'NONE',NULL,'HCMCONNECT',NULL,'INBINT',null,'DLINT',null,del_opt.delivery_type) DEL_CHANNEL ,pay_report_delivery.get_del_param_value('1',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER1

,pay_report_delivery.get_del_param_value('2',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER2

,pay_report_delivery.get_del_param_value('3',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER3

,pay_report_delivery.get_del_param_value('4',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER4

,pay_report_delivery.get_del_param_value('5',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER5

,pay_report_delivery.get_del_param_value('6',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER6

,pay_report_delivery.get_del_param_value('7',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER7

,pay_report_delivery.get_del_param_value('8',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER8

,pay_report_delivery.get_del_param_value('9',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETER9

,pay_report_delivery.get_del_param_value('10',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) PARAMETE10

,pay_report_delivery.get_del_param_value('LOCALE',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) LOCALE

,pay_report_delivery.get_del_param_value('TIMEZONE',del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) TIMEZONE

,del_opt.ext_delivery_option_id

,pact.payroll_action_id ,

pay_report_delivery.get_del_pref(del_opt.ext_delivery_option_id,del_opt.delivery_type,pai.action_information_id,pai.action_context_type,pai.action_context_id) DEL_PREF

FROM

pay_action_information pai,

pay_payroll_actions pact,

per_ext_delivery_options_b del_opt,

per_ext_delivery_option_dtls del_det,

per_ext_data_elements_b data_ele,

pay_report_records_f prr

where del_opt.ext_delivery_option_id=del_det.ext_delivery_option_id

and del_det.option_type= 'KEY'

and del_det.option_data_element_id=data_ele.ext_data_element_id

and data_ele.report_record_id=prr.report_record_id

and pai.report_record_id=prr.report_record_id

and ((pay_report_delivery.get_action_context_type(prr.report_block_id)='PPA'

and pai.action_context_id=pact.payroll_action_id)

or

(pay_report_delivery.get_action_context_type(prr.report_block_id)='PRA'

and pai.action_context_id in (select payroll_rel_action_id

from pay_payroll_rel_actions pra

where pra.payroll_action_id=pact.payroll_action_id))

or

(pay_report_delivery.get_action_context_type(prr.report_block_id)='POA'

and pai.action_context_id in (select object_action_id

from pay_object_actions poa

where poa.payroll_action_id=pact.payroll_action_id))

or

(pay_report_delivery.get_action_context_type(prr.report_block_id)='PTOA'

and pai.action_context_id in (select temp_object_action_id

from pay_temp_object_actions ptoa

where ptoa.payroll_action_id=pact.payroll_action_id)))

and pact.effective_date between prr.effective_start_date and prr.effective_end_date

union all

SELECT pay_report_delivery.get_key_value(data_ele.information_column,pai.action_information_id,'N') KEY