PAY_PER_RES_PAYROLL

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
OBJECT_ACTION_ID PAYROLL_ACTION_ID PAYROLL_ID PAYROLL_NAME TIME_PERIOD_ID PAYROLL_PERIOD_NAME PAYROLL_PERIOD_NUMBER CALC_FLOW_INSTANCE_ID CALC_FLOW_INSTANCE_NAME CALC_FLOW_PATTERN_ID CALC_FLOW_PATTERN RUN_TYPE_ID RUN_TYPE_NAME

Name

OBJECT_ACTION_ID

PAYROLL_ACTION_ID

PAYROLL_ID

PAYROLL_NAME

TIME_PERIOD_ID

PAYROLL_PERIOD_NAME

PAYROLL_PERIOD_NUMBER

CALC_FLOW_INSTANCE_ID

CALC_FLOW_INSTANCE_NAME

CALC_FLOW_PATTERN_ID

CALC_FLOW_PATTERN

RUN_TYPE_ID

RUN_TYPE_NAME

Query

SQL_Statement
select zzz.OBJECT_ACTION_ID, zzz.PAYROLL_ACTION_ID, zzz.PAYROLL_ID, zzz.PAYROLL_NAME, zzz.time_period_id, zzz.PAYROLL_PERIOD_NAME, null PAYROLL_PERIOD_NUMBER, zzz.Calc_flow_instance_id, zzz.Calc_flow_instance_name, zzz.Calc_FLOW_PATTERN_ID, zzz.Calc_FLOW_PATTERN, zzz.run_type_id, zzz.RUN_TYPE_NAME from ( select PRA.PAYROLL_REL_ACTION_ID OBJECT_ACTION_ID, ppa.payroll_action_id, ppa.payroll_id, pap.payroll_name, ppa.earn_time_period_id time_period_id, tp.period_name payroll_period_name, prq.flow_instance_id Calc_flow_instance_id, pfi.instance_name Calc_flow_instance_name, pf.FLOW_ID Calc_FLOW_PATTERN_ID, pf.FLOW_NAME Calc_FLOW_PATTERN, rt.run_type_id, rt.RUN_TYPE_NAME from pay_payroll_actions ppa, PAY_PAYROLL_REL_ACTIONS PRA, PAY_ALL_PAYROLLS_F pap, PAY_TIME_PERIODS tp, pay_requests prq, pay_flow_instances pfi, pay_flows_vl pf, PAY_RUN_TYPES_VL rt where ppa.PAYROLL_ID=pap.PAYROLL_ID and ppa.payROLL_ACTION_ID=PRA.payROLL_ACTION_ID and rt.run_type_id = ppa.run_type_id and ppa.earn_time_period_id =tp.TIME_PERIOD_ID and ppa.effective_date between pap.effective_start_date and pap.effective_end_date and ppa.pay_request_id(+)= prq.pay_request_id and prq.flow_instance_id=pfi.flow_instance_id(+) and pfi.base_flow_id=pf.flow_id(+) union all select PRA_P.PAYROLL_REL_ACTION_ID OBJECT_ACTION_ID, ppa.payroll_action_id, pap.payroll_id, pap.payroll_name, PPA_R.EARN_TIME_PERIOD_ID time_period_id, tp.period_name payroll_period_name, prq.flow_instance_id Calc_flow_instance_id, pfi.instance_name Calc_flow_instance_name, pf.FLOW_ID Calc_FLOW_PATTERN_ID, pf.FLOW_NAME Calc_FLOW_PATTERN, rt.run_type_id, rt.RUN_TYPE_NAME from pay_payroll_actions PPA_R, pay_payroll_actions ppa, PAY_ALL_PAYROLLS_F pap,PAY_TIME_PERIODS tp, pay_requests prq, pay_flow_instances pfi, pay_flows_vl pf, PAY_RUN_TYPES_VL rt, PAY_PAYROLL_REL_ACTIONS PRA_P where PPA_R.pay_request_id(+)= prq.pay_request_id and prq.flow_instance_id=pfi.flow_instance_id(+) and pfi.base_flow_id=pf.flow_id(+) and rt.run_type_id = ppa_r.run_type_id and PPA_R.earn_time_period_id =tp.TIME_PERIOD_ID and ppa.payROLL_ACTION_ID=PRA_P.payROLL_ACTION_ID and ppa.effective_date between pap.effective_start_date and pap.effective_end_date and exists(select 1 from PAY_PAYROLL_REL_ACTIONS PRA_R, PAY_ACTION_INTERLOCKS int where PRA_R.PAYROLL_REL_ACTION_ID= INT.LOCKED_ACTION_ID and PRA_P.PAYROLL_REL_ACTION_ID= INT.LOCKING_ACTION_ID and PPA_R.payROLL_ACTION_ID=PRA_R.payROLL_ACTION_ID) and PPA_R.PAYROLL_ID=pap.PAYROLL_ID and PPA_R.action_type in('R','Q') union all select PRA_PS.PAYROLL_REL_ACTION_ID OBJECT_ACTION_ID, ppa.payroll_action_id, pap.payroll_id, pap.payroll_name, PPA_R.EARN_TIME_PERIOD_ID time_period_id, tp.period_name payroll_period_name, prq.flow_instance_id Calc_flow_instance_id, pfi.instance_name Calc_flow_instance_name, pf.FLOW_ID Calc_FLOW_PATTERN_ID, pf.FLOW_NAME Calc_FLOW_PATTERN, rt.run_type_id, rt.RUN_TYPE_NAME from pay_payroll_actions PPA_R, pay_payroll_actions ppa, PAY_ALL_PAYROLLS_F pap, PAY_TIME_PERIODS tp, pay_requests prq, pay_flow_instances pfi, pay_flows_vl pf, PAY_RUN_TYPES_VL rt, PAY_PAYROLL_REL_ACTIONS PRA_PS where PPA_R.pay_request_id(+)= prq.pay_request_id and prq.flow_instance_id=pfi.flow_instance_id(+) and pfi.base_flow_id=pf.flow_id(+) and PPA_R.earn_time_period_id =tp.TIME_PERIOD_ID and PPA_R.PAYROLL_ID=pap.PAYROLL_ID and ppa.effective_date between pap.effective_start_date and pap.effective_end_date and PPA_R.action_type in('R','Q') and rt.run_type_id = ppa_r.run_type_id and ppa.payROLL_ACTION_ID=PRA_PS.payROLL_ACTION_ID and exists(select 1 from PAY_PAYROLL_REL_ACTIONS PRA_R, PAY_ACTION_INTERLOCKS int, PAY_PAYROLL_REL_ACTIONS PRA_P, PAY_ACTION_INTERLOCKS int_ps where PRA_R.PAYROLL_REL_ACTION_ID= INT.LOCKED_ACTION_ID and PRA_P.PAYROLL_REL_ACTION_ID= INT.LOCKING_ACTION_ID and PPA_R.payROLL_ACTION_ID=PRA_R.payROLL_ACTION_ID and PRA_PS.PAYROLL_REL_ACTION_ID= int_ps.LOCKING_ACTION_ID and PRA_P.PAYROLL_REL_ACTION_ID= int_ps.LOCKED_ACTION_ID) )zzz

SQL_Statement

select

zzz.OBJECT_ACTION_ID,

zzz.PAYROLL_ACTION_ID,

zzz.PAYROLL_ID,

zzz.PAYROLL_NAME,

zzz.time_period_id,

zzz.PAYROLL_PERIOD_NAME,

null PAYROLL_PERIOD_NUMBER,

zzz.Calc_flow_instance_id,

zzz.Calc_flow_instance_name,

zzz.Calc_FLOW_PATTERN_ID,

zzz.Calc_FLOW_PATTERN,

zzz.run_type_id,

zzz.RUN_TYPE_NAME

from

(

select

PRA.PAYROLL_REL_ACTION_ID OBJECT_ACTION_ID,

ppa.payroll_action_id,

ppa.payroll_id,

pap.payroll_name,

ppa.earn_time_period_id time_period_id,

tp.period_name payroll_period_name,

prq.flow_instance_id Calc_flow_instance_id,

pfi.instance_name Calc_flow_instance_name,

pf.FLOW_ID Calc_FLOW_PATTERN_ID,

pf.FLOW_NAME Calc_FLOW_PATTERN,

rt.run_type_id,

rt.RUN_TYPE_NAME

from pay_payroll_actions ppa,

PAY_PAYROLL_REL_ACTIONS PRA,

PAY_ALL_PAYROLLS_F pap,

PAY_TIME_PERIODS tp,

pay_requests prq,

pay_flow_instances pfi,

pay_flows_vl pf,

PAY_RUN_TYPES_VL rt

where ppa.PAYROLL_ID=pap.PAYROLL_ID

and ppa.payROLL_ACTION_ID=PRA.payROLL_ACTION_ID

and rt.run_type_id = ppa.run_type_id

and ppa.earn_time_period_id =tp.TIME_PERIOD_ID

and ppa.effective_date between pap.effective_start_date and pap.effective_end_date

and ppa.pay_request_id(+)= prq.pay_request_id

and prq.flow_instance_id=pfi.flow_instance_id(+)

and pfi.base_flow_id=pf.flow_id(+)

union all

select

PRA_P.PAYROLL_REL_ACTION_ID OBJECT_ACTION_ID,

ppa.payroll_action_id,

pap.payroll_id,

pap.payroll_name,

PPA_R.EARN_TIME_PERIOD_ID time_period_id,

tp.period_name payroll_period_name,

prq.flow_instance_id Calc_flow_instance_id,

pfi.instance_name Calc_flow_instance_name,

pf.FLOW_ID Calc_FLOW_PATTERN_ID,

pf.FLOW_NAME Calc_FLOW_PATTERN,

rt.run_type_id,

rt.RUN_TYPE_NAME

from pay_payroll_actions PPA_R, pay_payroll_actions ppa,

PAY_ALL_PAYROLLS_F pap,PAY_TIME_PERIODS tp,

pay_requests prq,

pay_flow_instances pfi,

pay_flows_vl pf,

PAY_RUN_TYPES_VL rt,

PAY_PAYROLL_REL_ACTIONS PRA_P

where PPA_R.pay_request_id(+)= prq.pay_request_id

and prq.flow_instance_id=pfi.flow_instance_id(+)

and pfi.base_flow_id=pf.flow_id(+)

and rt.run_type_id = ppa_r.run_type_id

and PPA_R.earn_time_period_id =tp.TIME_PERIOD_ID

and ppa.payROLL_ACTION_ID=PRA_P.payROLL_ACTION_ID

and ppa.effective_date between pap.effective_start_date and pap.effective_end_date

and exists(select 1

from

PAY_PAYROLL_REL_ACTIONS PRA_R,

PAY_ACTION_INTERLOCKS int

where PRA_R.PAYROLL_REL_ACTION_ID= INT.LOCKED_ACTION_ID

and PRA_P.PAYROLL_REL_ACTION_ID= INT.LOCKING_ACTION_ID

and PPA_R.payROLL_ACTION_ID=PRA_R.payROLL_ACTION_ID)

and PPA_R.PAYROLL_ID=pap.PAYROLL_ID

and PPA_R.action_type in('R','Q')

union all

select

PRA_PS.PAYROLL_REL_ACTION_ID OBJECT_ACTION_ID,

ppa.payroll_action_id,

pap.payroll_id,

pap.payroll_name,

PPA_R.EARN_TIME_PERIOD_ID time_period_id,

tp.period_name payroll_period_name,

prq.flow_instance_id Calc_flow_instance_id,

pfi.instance_name Calc_flow_instance_name,

pf.FLOW_ID Calc_FLOW_PATTERN_ID,

pf.FLOW_NAME Calc_FLOW_PATTERN,

rt.run_type_id,

rt.RUN_TYPE_NAME

from pay_payroll_actions PPA_R, pay_payroll_actions ppa, PAY_ALL_PAYROLLS_F pap,

PAY_TIME_PERIODS tp,

pay_requests prq,

pay_flow_instances pfi,

pay_flows_vl pf,

PAY_RUN_TYPES_VL rt,

PAY_PAYROLL_REL_ACTIONS PRA_PS

where PPA_R.pay_request_id(+)= prq.pay_request_id

and prq.flow_instance_id=pfi.flow_instance_id(+)

and pfi.base_flow_id=pf.flow_id(+)

and PPA_R.earn_time_period_id =tp.TIME_PERIOD_ID

and PPA_R.PAYROLL_ID=pap.PAYROLL_ID

and ppa.effective_date between pap.effective_start_date and pap.effective_end_date

and PPA_R.action_type in('R','Q')

and rt.run_type_id = ppa_r.run_type_id

and ppa.payROLL_ACTION_ID=PRA_PS.payROLL_ACTION_ID

and exists(select 1

from PAY_PAYROLL_REL_ACTIONS PRA_R, PAY_ACTION_INTERLOCKS int,

PAY_PAYROLL_REL_ACTIONS PRA_P, PAY_ACTION_INTERLOCKS int_ps

where PRA_R.PAYROLL_REL_ACTION_ID= INT.LOCKED_ACTION_ID

and PRA_P.PAYROLL_REL_ACTION_ID= INT.LOCKING_ACTION_ID

and PPA_R.payROLL_ACTION_ID=PRA_R.payROLL_ACTION_ID

and PRA_PS.PAYROLL_REL_ACTION_ID= int_ps.LOCKING_ACTION_ID

and PRA_P.PAYROLL_REL_ACTION_ID= int_ps.LOCKED_ACTION_ID)

)zzz