1. Tables and Views for HCM
  2. PAY_TASK_ITERATIONS_VL

PAY_TASK_ITERATIONS_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

ESS_CALL_ID

ITERATION_NAME

FLOW_TASK_INSTANCE_ID

ESS_REQUEST_ID

REQUEST_ID

ITERATION_STATUS

PAYROLL_ACTION_ID

ACTION_TYPE

TASK_TYPE

TASK_TYPE_CODE

Query

SQL_Statement

select ESS_CALL_ID,

Iteration_Name,

flow_Task_Instance_Id,

Ess_Request_Id,

Request_Id,

Iteration_STATUS,

payroll_action_id,

action_type,

Task_type,

Task_type_code

from(

select pfti.flow_Task_Instance_Id ,

eprq.pay_request_id Ess_Request_Id,

pprq.pay_request_id Request_Id,

eprq.call_id ESS_CALL_ID,

(select lk.meaning from pay_tasks_vl pt, hcm_lookups lk where

lk.lookup_type='PAY_TASK_TYPE'

and lk.lookup_code=pt.task_type

and pt.base_task_name=eprq.ITERATION_BASE_TASK_NAME ) task_type,

(select pt.task_type from pay_tasks_vl pt

where pt.base_task_name=eprq.ITERATION_BASE_TASK_NAME ) task_type_code,

to_char(

nvl((select pt.task_name from pay_tasks_vl pt where pt.base_task_name=eprq.ITERATION_BASE_TASK_NAME),null)

||

(case when ppa.action_type ='XWr' and eprq.ITERATION_BASE_TASK_NAME is null then

(SELECT MEANING from hcm_lookups where LOOKUP_TYPE='ORA_PAY_FLOW_EXTRACT_ESS_DEFN' AND LOOKUP_CODE = 'ArchiveWriteJob')

when ppa.action_type ='XRD' and eprq.ITERATION_BASE_TASK_NAME is null then

(select (dl.DELIVERY_OPTION_NAME)

||' - '||lk.meaning

from pay_payroll_actions ppa1, per_ext_delivery_options_vl dl,hcm_lookups lk

where ppa1.payroll_action_id=ppa.target_payroll_action_id

and lookup_type = 'PER_EXT_DELIVERY_TYPES'

and lookup_code = dl.delivery_type

and dl.EXT_DELIVERY_OPTION_ID=pay_core_utils.get_parameter('delivery_option_id' ,ppa1.LEGISLATIVE_PARAMETERS)

)

when ppa.action_type ='I' and eprq.ITERATION_BASE_TASK_NAME is null then

(select pap.payroll_name||' - '||(ppa.effective_date)

from pay_all_payrolls_f pap

where pap.payroll_id=ppa.payroll_id

)

else null end

)||decode(eprq.ITERATION_BASE_TASK_NAME,null,decode( ppa.action_type ,'XWr' ,null,'XRD',null,'I',null,' - '),' - ')

||nvl((select listagg(Param.parameter_name||' - '||Param.Parameter_value ,' | ') within group (order by Param.param_sequence)

from(select distinct ftp1.parameter_name,

decode(ftp1.param_disp_type,'D',fnd_date.date_to_displaydate(to_Date(fpv.value_4,'YYYY-MM-DD HH24:MI:SS')),

'T',fpv.value_4,

'N',fpv.value_4,

PAY_FLOW_COMMON_UTIL_PKG.convert_value (to_char(sysdate,'YYYYMMDDHHMISS'),sysdate,

pfi.LEGISLATION_CODE,pfi.LEGISLATIVE_DATA_GROUP_ID,

decode(ftp1.param_disp_type,'LK','LOOKUP','LOV','VO','L','VO','ORA_VALUESET','VALUESET'),

ftp1.param_lookup ,fpv.value_4,'Y')

) Parameter_value ,ftp1.param_sequence ,ftp1.param_disp_type

from table(pay_process_flow_utils.get_tab_pay_requests_tf(pfti.flow_Task_Instance_Id)) fpv,

(

SELECT distinct trim(regexp_substr(ITERATION_LABEL, '[^|]+', 1, level)) ITERATION_LABEL

FROM (select itr.ITERATION_LABEL from pay_requests itr where itr.pay_request_id=eprq.pay_request_id) t

CONNECT BY instr(ITERATION_LABEL, '|', 1, level - 1) > 0

order by ITERATION_LABEL) KeyParam,

pay_task_parameters_vl ftp1

where ftp1.BASE_TASK_PARAMETER_ID = fpv.value_3

and KeyParam.ITERATION_LABEL=ftp1.BASE_TASK_PARAMETER_NAME

and ftp1.DISPLAY_FLAG != 'N'

and fpv.value_1 =pprq.pay_request_id

and fpv.value_3 is not null

and fpv.value_4 is not null) Param),

(case when eprq.ITERATION_BASE_TASK_NAME is null then null

else ' - '||eprq.call_id end)

)) Iteration_Name,

decode(lks.meaning,null,q1.status,lks.meaning) Iteration_STATUS,

nvl(ppa.payroll_action_id,-1) payroll_action_id,

ppa.action_type

from fusion.pay_flow_task_instances pfti,

pay_flow_instances pfi,

fusion.pay_flow_tasks_vl pft,

fusion.pay_requests eprq

,fusion.pay_requests pprq

, fusion.pay_payroll_actions ppa,

pay_tasks_vl ptv,

hcm_lookups lkp,

hcm_lookups lks,

(

select e.*,nvl(h1.meaning,e.state) status,nvl(h2.meaning,REGEXP_SUBSTR(e.definition, '[^//]*$', 1)) process_name

FROM ess_request_history e,

hcm_lookups h1,

hcm_lookups h2

WHERE h1.LOOKUP_TYPE (+) = 'ORA_PAY_FLOW_EXTRACT_ESS_STATE'

AND h1.LOOKUP_CODE (+) = 'ORA_PAY_FLOW_'||to_char(e.state)

AND h2.LOOKUP_TYPE (+) = 'ORA_PAY_FLOW_EXTRACT_ESS_DEFN'

AND h2.LOOKUP_CODE (+) = substrb(REGEXP_SUBSTR(e.definition, '[^//]*$', 1),1,30)

) q1

where lks.lookup_type (+) = 'PAY_ACTION_STATUS'

AND lks.lookup_code (+) = ppa.action_status

and lkp.lookup_type (+) = 'ACTION_TYPE'

AND lkp.lookup_code (+) = ppa.action_type

and q1.requestid(+)= eprq.call_id

and ptv.base_task_id= pft.BASE_TASK_ID

and pft.flow_task_id = pfti.base_flow_task_id

and eprq.flow_task_instance_id=pfti.flow_task_instance_id

and eprq.call_type='ESS'

and pprq.flow_task_instance_id=pfti.flow_task_instance_id

and pfi.flow_instance_id = pfti.flow_instance_id

and not exists(

SELECT NULL FROM fusion.pay_task_actions TA WHERE TA.BASE_TASK_ACTION_NAME IN('SUBMIT_ANOTHER_TASK_ACTION','SUBMIT_ANOTHER_TASK_ROLLBACK_ACTION')

AND TA.TASK_ACTION_ID=pprq.pay_task_action_id

)

and pprq.call_type='FLOW_TASK_INSTANCE'

and ppa.pay_request_id(+)=pprq.pay_request_id

and eprq.pay_request_id =

(select min(aaa.pay_request_id) from pay_requests aaa where aaa.flow_Task_Instance_Id = pfti.flow_task_instance_id and aaa.pay_request_id > pprq.pay_request_id)

and ppa.ACTION_TYPE(+) != 'BIP'

union all

select pfti.flow_Task_Instance_Id ,

pprq.pay_request_id Ess_Request_Id,

pprq.pay_request_id Request_Id,

pprq.call_id ESS_CALL_ID,

(select lk.meaning from pay_tasks_vl pt, hcm_lookups lk where

lk.lookup_type='PAY_TASK_TYPE'

and lk.lookup_code=pt.task_type

and pt.base_task_name=pprq.ITERATION_BASE_TASK_NAME ) task_type,

(select pt.task_type from pay_tasks_vl pt

where pt.base_task_name=pprq.ITERATION_BASE_TASK_NAME ) task_type_code,

to_char(

nvl((select pt.task_name from pay_tasks_vl pt where pt.base_task_name=pprq.ITERATION_BASE_TASK_NAME),null)

||

(case when ppa.action_type ='XWr' and pprq.ITERATION_BASE_TASK_NAME is null then

(SELECT MEANING from hcm_lookups where LOOKUP_TYPE='ORA_PAY_FLOW_EXTRACT_ESS_DEFN' AND LOOKUP_CODE = 'ArchiveWriteJob')

when ppa.action_type ='XRD' and pprq.ITERATION_BASE_TASK_NAME is null then

(select (dl.DELIVERY_OPTION_NAME)

||' - '||lk.meaning

from pay_payroll_actions ppa1, per_ext_delivery_options_vl dl,hcm_lookups lk

where ppa1.payroll_action_id=ppa.target_payroll_action_id

and lookup_type = 'PER_EXT_DELIVERY_TYPES'

and lookup_code = dl.delivery_type

and dl.EXT_DELIVERY_OPTION_ID=pay_core_utils.get_parameter('delivery_option_id' ,ppa1.LEGISLATIVE_PARAMETERS)

)

when ppa.action_type ='I' and pprq.ITERATION_BASE_TASK_NAME is null then

(select pap.payroll_name||' - '||(ppa.effective_date)

from pay_all_payrolls_f pap

where pap.payroll_id=ppa.payroll_id

)

else null end

)||decode(pprq.ITERATION_BASE_TASK_NAME,null,decode( ppa.action_type ,'XWr' ,null,'XRD',null,'I',null,' - '),' - ')

||nvl((select listagg(Param.parameter_name||' - '||Param.Parameter_value ,' | ') within group (order by Param.param_sequence)

from(select distinct ftp1.parameter_name,

decode(ftp1.param_disp_type,'D',fnd_date.date_to_displaydate(to_Date(fpv.value_4,'YYYY-MM-DD HH24:MI:SS')),

'T',fpv.value_4,

'N',fpv.value_4,

PAY_FLOW_COMMON_UTIL_PKG.convert_value (to_char(sysdate,'YYYYMMDDHHMISS'),sysdate,

pfi.LEGISLATION_CODE,pfi.LEGISLATIVE_DATA_GROUP_ID,

decode(ftp1.param_disp_type,'LK','LOOKUP','LOV','VO','L','VO','ORA_VALUESET','VALUESET'),

ftp1.param_lookup ,fpv.value_4,'Y')

) Parameter_value ,ftp1.param_sequence ,ftp1.param_disp_type

from table(pay_process_flow_utils.get_tab_pay_requests_tf(pfti.flow_Task_Instance_Id)) fpv,

(

SELECT distinct trim(regexp_substr(ITERATION_LABEL, '[^|]+', 1, level)) ITERATION_LABEL

FROM (select itr.ITERATION_LABEL from pay_requests itr where itr.pay_request_id=pprq.pay_request_id) t

CONNECT BY instr(ITERATION_LABEL, '|', 1, level - 1) > 0

order by ITERATION_LABEL) KeyParam,

pay_task_parameters_vl ftp1

where ftp1.BASE_TASK_PARAMETER_ID = fpv.value_3

and KeyParam.ITERATION_LABEL=ftp1.BASE_TASK_PARAMETER_NAME

and ftp1.DISPLAY_FLAG != 'N'

and fpv.value_1 =pprq.pay_request_id

and fpv.value_3 is not null

and fpv.value_4 is not null) Param),

(case when pprq.ITERATION_BASE_TASK_NAME is null then null

else ' - '||pprq.call_id end)

)) Iteration_Name,

decode(lks.meaning,null,'Complete',lks.meaning) Iteration_STATUS,

nvl(ppa.payroll_action_id,-1) payroll_action_id,

ppa.action_type

from fusion.pay_flow_task_instances pfti,

pay_flow_instances pfi,

fusion.pay_flow_tasks_vl pft,

fusion.pay_requests pprq ,

fusion.pay_payroll_actions ppa,

pay_tasks_vl ptv,

hcm_lookups lkp,

hcm_lookups lks

where lks.lookup_type (+) = 'PAY_ACTION_STATUS'

AND lks.lookup_code (+) = ppa.action_status

and lkp.lookup_type (+) = 'ACTION_TYPE'

AND lkp.lookup_code (+) = ppa.action_type

and ptv.base_task_id= pft.BASE_TASK_ID

and pft.flow_task_id = pfti.base_flow_task_id

and pprq.flow_task_instance_id=pfti.flow_task_instance_id

and pfi.flow_instance_id = pfti.flow_instance_id

and not exists(

SELECT NULL FROM fusion.pay_task_actions TA WHERE TA.BASE_TASK_ACTION_NAME IN('SUBMIT_ANOTHER_TASK_ACTION','SUBMIT_ANOTHER_TASK_ROLLBACK_ACTION')

AND TA.TASK_ACTION_ID=pprq.pay_task_action_id

union all

SELECT NULL FROM fusion.pay_requests eprq, fusion.pay_task_actions TA WHERE eprq.call_type='ESS' and eprq.flow_task_instance_id=pfti.flow_task_instance_id

and TA.BASE_TASK_ACTION_NAME not IN('SUBMIT_ANOTHER_TASK_ACTION','SUBMIT_ANOTHER_TASK_ROLLBACK_ACTION')

AND TA.TASK_ACTION_ID=eprq.pay_task_action_id

)

and pprq.call_type='FLOW_TASK_INSTANCE'

and ppa.pay_request_id(+)=pprq.pay_request_id

and ppa.ACTION_TYPE(+) != 'BIP'

)

ORDER BY coalesce(payroll_action_id,ESS_CALL_ID)asc