1. Tables and Views for HCM
  2. PAY_PROC_NP_ACT_RLST_VL

PAY_PROC_NP_ACT_RLST_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

OBJECT_ID

OBJECT_NAME

OBJECT_TYPE

OBJECT_ACTION_ID

CHILD_OBJECT_ACTION_ID

STATUS

ACTION_STATUS_CODE

ACTION_TYPE

ACTION_TYPE_CODE

PAYROLL_ACTION_ID

PROCESS_DATE

FLOW_NAME

TASK_NAME

LEGISLATIVE_DATA_GROUP_ID

LEGISLATIVE_DATA_GROUP_NAME

TASK_INSTANCE_ID

FLOW_INSTANCE_ID

SUBMISSION_DATE

PAY_REQUEST_ID

PAYMT_RESULTS_DISPLAY_FLAG

ARCHIVE_CONTEXT_TYPE

ROLLBACK_ALLOWED_FLAG

MARKFORRETRY_ALLOWED_FLAG

ARCH_RESULTS_DISPLAY_FLAG

MESSAGES_DISPLAY_FLAG

PRIMARY_RESULTS

Query

SQL_Statement

SELECT

zzz.OBJECT_ID ,

zzz.object_name,

zzz.object_type,

zzz.OBJECT_ACTION_ID ,

zzz.CHILD_OBJECT_ACTION_ID,

zzz.STATUS ,

zzz.ACTION_STATUS_CODE ,

zzz.ACTION_TYPE ,

zzz.ACTION_TYPE_CODE ,

zzz.PAYROLL_ACTION_ID ,

zzz.PROCESS_DATE,

zzz.flow_name,

zzz.TASK_NAME,

zzz.legislative_data_group_id,

zzz.legislative_data_group_name,

zzz.task_instance_id,

zzz.flow_instance_id,

zzz.submission_date,

zzz.pay_request_id,

DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'H','Y','PP','Y','M','Y','E','Y','D','Y','N')) AS PAYMT_RESULTS_DISPLAY_FLAG,

zzz.archive_context_type,

DECODE(OBJ_ACT_ROLLBACK_ALLOWED,'Y','Y','N' ) AS Rollback_Allowed_FLAG,

DECODE(OBJ_ACT_MARK_FOR_RETRY_ALLOWED,'Y',DECODE(zzz.action_status_code,'C','Y','N'),'N') AS MarkForRetry_Allowed_FLAG,

DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'CTG','Y','X','Y','PS','Y','XWr','Y','Wr','Y','N')) AS ARCH_RESULTS_DISPLAY_FLAG,

'Y' MESSAGES_DISPLAY_FLAG,

DECODE(zzz.action_status_code,'U','NONE','E','Messages'

,DECODE(zzz.ACTION_TYPE_CODE,'X','Archive Results',

'D','Payment Results','H','Payment Results','M','Payment Results','PP','Payment Results',

'PS','Archive Results','Wr','Archive Results','XWr','Archive Results','Messages')) PRIMARY_RESULTS

FROM

(

SELECT distinct pay_obj_act.OBJECT_ID ,

pay_obj_act.TEMP_OBJECT_ACTION_ID as OBJECT_ACTION_ID ,

pay_obj_act.TEMP_OBJECT_ACTION_ID AS child_object_Action_id,

'PTOA' as archive_context_type,

actionstatuslookup.meaning status,

actionstatuslookup.lookup_code action_status_code,

actiontypelookup.meaning action_type,

actiontypelookup.lookup_code action_type_code,

pay_obj_act.PAYROLL_ACTION_ID AS payroll_action_id,

payrollactioneo.effective_date AS process_date,

NVL(pt.task_name,actiontypelookup.meaning) AS task_name,

pay_obj_act.OBJECT_type,

NVL((select

(select distinct listagg( xxx.information_label||'-'||

(select decode(xxx.information_column,

1,ttt.action_information1,

2,ttt.action_information2,

3,ttt.action_information3,

4,ttt.action_information4,

5,ttt.action_information5,

6,ttt.action_information6,

7,ttt.action_information7,

8,ttt.action_information8,

9,ttt.action_information9,

10,ttt.action_information10,

null)

from pay_action_information ttt

where ttt.report_record_id=xxx.report_record_id

and ttt.action_context_id=pay_obj_act.temp_object_action_id

and rownum=1)

,'|') within group (order by xxx.information_column)

from PER_EXT_DATA_ELEMENTS_VL xxx

where xxx.report_record_id=yyy.report_record_id

and xxx.data_type='T'

and xxx.information_column <= 10)

from pay_report_records_vl yyy

where yyy.report_record_id = (

SELECT min(report_record_id)

FROM pay_action_information

where action_context_id = pay_obj_act.temp_object_action_id

and action_context_type = 'PTOA'

and ((PARENT_ACT_INFO_ID is null)

or (PARENT_ACT_INFO_ID is not null

and parent_act_info_id in (select action_information_id

from pay_action_information par_pai

where par_pai.action_context_type='PPA'

and action_context_id = payrollactioneo.payroll_action_id))))

),pay_obj_act.object_type||'-'||pay_obj_act.object_id) AS OBJECT_NAME ,

ldg.legislative_data_group_id legislative_data_group_id,

ldg.name legislative_data_group_name,

prq.flow_task_instance_id task_instance_id,

pfi.instance_name as flow_name,

ACT_FLAG.ACT_FLAG_ACTION_TYPE,

payrollactioneo.pay_request_id,

trunc(pfi.creation_date) submission_date,

pfi.flow_instance_id,

decode(pt.task_id,null,'Y',nvl((select 'Y' from pay_task_actions ppta

where ppta.base_task_id=pt.task_id

and BASE_TASK_ACTION_NAME='ROLLBACK'

and not exists(select 1 from pay_task_properties pptp

where pptp.base_task_action_id=ppta.task_action_id

and pptp.BASE_TASK_PROPERTY_NAME='OBJ_LEVEL_ACTION_DISABLED'

and pptp.TASK_PROPERTY_VALUE='Y')

and rownum=1),'N')) OBJ_ACT_ROLLBACK_ALLOWED,

decode(pt.task_id,null,'Y',nvl((select 'Y' from pay_task_actions ppta

where ppta.base_task_id=pt.task_id

and BASE_TASK_ACTION_NAME='MARK_FOR_RETRY'

and not exists(select 1 from pay_task_properties pptp

where pptp.base_task_action_id=ppta.task_action_id

and pptp.BASE_TASK_PROPERTY_NAME='OBJ_LEVEL_ACTION_DISABLED'

and pptp.TASK_PROPERTY_VALUE='Y')

and rownum=1),'N')) OBJ_ACT_MARK_FOR_RETRY_ALLOWED

FROM hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup,

pay_temp_object_actions pay_obj_act,

pay_payroll_actions payrollactioneo,

pay_requests prq,

pay_flow_instances pfi,

FUSION.pay_task_actions pta,

pay_tasks_vl pt,

per_legislative_data_groups_vl ldg,

(SELECT LOOKUP_CODE,'1' ACT_FLAG_ACTION_TYPE FROM HCM_LOOKUPS ACT WHERE LOOKUP_CODE IN('B','C','CP','CA','CQ','CR','D','E','EC','GI','H','L','M','P','PP','PS','PRU','Q','R','RG','S','T','TC','U','X','XWr','V','Wr')

AND LOOKUP_TYPE='ACTION_TYPE') ACT_FLAG

WHERE PAYROLLACTIONEO.ACTION_TYPE=ACT_FLAG.LOOKUP_CODE(+)

AND pay_obj_act.OBJECT_TYPE NOT IN ('PAYROLL_RELATIONSHIP_ID','PERSON_ID','HR_ASSIGNMENT_ID','PAYROLL_REL_ACTION_ID','BIPOUTPUT','PAYROLL_ACTION_ID','TIME','PAY_ASSIGNMENT_ID')

AND pfi.flow_instance_id=prq.flow_instance_id

AND actiontypelookup.lookup_type = 'ACTION_TYPE'

AND actiontypelookup.lookup_code =payrollactioneo.action_type

AND actionstatuslookup.lookup_type = 'ORA_PAY_WU_ACTION_STATUS'

AND actionstatuslookup.lookup_code = pay_obj_act.ACTION_STATUS

AND payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id

AND prq.pay_request_id (+) = payrollactioneo.pay_request_id

AND prq.pay_task_action_id = pta.task_action_id (+)

AND pta.base_task_id = pt.task_id (+)

AND payrollactioneo.legislative_data_group_id = ldg.legislative_data_group_id (+)

AND ((pt.legislative_data_group_id IS NOT NULL

AND pt.legislation_code IS NULL

AND pt.legislative_data_group_id = ldg.legislative_data_group_id)

OR (pt.legislation_code IS NOT NULL

AND pt.legislative_data_group_id IS NULL

AND pt.legislation_code =ldg.legislation_code

AND ( NOT EXISTS

(SELECT TASK_ID

FROM FUSION.PAY_TASK_ACTIONS_VL c1

WHERE pt.BASE_TASK_ID = c1.BASE_TASK_ID

AND (c1.legislative_data_group_id IS NOT NULL

AND c1.legislation_code IS NULL

AND c1.legislative_data_group_id = ldg.legislative_data_group_id )

)))

OR ( pt.legislative_data_group_id IS NULL

AND pt.legislation_code IS NULL

AND ( NOT EXISTS

(SELECT TASK_ID

FROM FUSION.PAY_TASK_ACTIONS_VL c2

WHERE pt.BASE_TASK_ID = c2.BASE_TASK_ID

AND (( c2.legislative_data_group_id IS NOT NULL

AND c2.legislation_code IS NULL

AND c2.legislative_data_group_id = ldg.legislative_data_group_id)

OR ( c2.legislation_code IS NOT NULL

AND c2.legislative_data_group_id IS NULL

AND c2.legislation_code = ldg.legislation_code ))

))))

union all

SELECT distinct

pay_obj_act.OBJECT_ID,

pay_obj_act.OBJECT_ACTION_ID,

pay_obj_act.OBJECT_ACTION_ID AS child_object_Action_id,

'POA' as archive_context_type,

actionstatuslookup.meaning status,

actionstatuslookup.lookup_code action_status_code,

actiontypelookup.meaning action_type,

actiontypelookup.lookup_code action_type_code,

pay_obj_act.PAYROLL_ACTION_ID AS payroll_action_id,

payrollactioneo.effective_date AS process_date,

NVL(pt.task_name,actiontypelookup.meaning) AS task_name,

PAY_OBJ_ACT.OBJECT_TYPE,

NVL(( (select max((select distinct listagg( xxx.information_label||'-'||

(select decode(xxx.information_column,

1,ttt.action_information1,

2,ttt.action_information2,

3,ttt.action_information3,

4,ttt.action_information4,

5,ttt.action_information5,

6,ttt.action_information6,

7,ttt.action_information7,

8,ttt.action_information8,

9,ttt.action_information9,

10,ttt.action_information10,

null)

from pay_action_information ttt

where ttt.report_record_id=xxx.report_record_id

and ttt.action_context_id=pay_obj_act.object_action_id

and rownum=1)

,'|') within group (order by xxx.information_column)

from PER_EXT_DATA_ELEMENTS_VL xxx

where xxx.report_record_id=rec.report_record_id

and xxx.data_type='T'

and xxx.information_column <= 10))

from pay_report_records_vl rec, pay_action_information paa

where

rec.sequence= (select min(rec1.sequence)

from pay_report_records_vl rec1

where rec1.REPORT_BLOCK_ID=rec.REPORT_BLOCK_ID

)

and rec.report_record_id=paa.report_record_id

and paa.action_context_id = pay_obj_act.object_action_id

and paa.action_context_type = 'POA'

and ((paa.PARENT_ACT_INFO_ID is null)

or (paa.PARENT_ACT_INFO_ID is not null

and paa.parent_act_info_id in (select action_information_id

from pay_action_information par_pai

where par_pai.action_context_type='PPA'

AND ACTION_CONTEXT_ID = PAYROLLACTIONEO.PAYROLL_ACTION_ID))))

),decode(PAY_OBJ_ACT.OBJECT_TYPE,'PPP',(SELECT 'Payee Name-'||PARTYEO.PARTY_NAME

FROM HZ_PARTIES PARTYEO,

HZ_PARTY_USG_ASSIGNMENTS PARTYUSAGEASSIGNMENTEO,

PAY_PRE_PAYMENTS PPP

WHERE PARTYEO.PARTY_ID = PARTYUSAGEASSIGNMENTEO.PARTY_ID

AND PPP.PRE_PAYMENT_ID = pay_obj_act.OBJECT_ID

AND PARTYUSAGEASSIGNMENTEO.PARTY_USAGE_CODE IN

('EXTERNAL_PAYEE','PROFESSIONAL_BODY','PENSION_PROVIDER','PAYMENT_ISSUING_AUTHORITY','DISABILITY_ORGANIZATION','BARGAINING_ASSOCIATION')

AND PPP.THIRD_PARTY_PAYEE_ID =PARTYEO.PARTY_ID

and rownum=1),'PRE_PAYMENT_ID',(SELECT 'Payee Name-'||PARTYEO.PARTY_NAME

FROM HZ_PARTIES PARTYEO,

HZ_PARTY_USG_ASSIGNMENTS PARTYUSAGEASSIGNMENTEO,

PAY_PRE_PAYMENTS PPP

WHERE PARTYEO.PARTY_ID = PARTYUSAGEASSIGNMENTEO.PARTY_ID

AND PPP.PRE_PAYMENT_ID = pay_obj_act.OBJECT_ID

AND PARTYUSAGEASSIGNMENTEO.PARTY_USAGE_CODE IN

('EXTERNAL_PAYEE','PROFESSIONAL_BODY','PENSION_PROVIDER','PAYMENT_ISSUING_AUTHORITY','DISABILITY_ORGANIZATION','BARGAINING_ASSOCIATION')

AND PPP.THIRD_PARTY_PAYEE_ID =PARTYEO.PARTY_ID

and rownum=1),pay_obj_act.object_type||'-'||pay_obj_act.OBJECT_ID)) AS OBJECT_NAME ,

ldg.legislative_data_group_id legislative_data_group_id,

ldg.name legislative_data_group_name,

prq.flow_task_instance_id task_instance_id,

pfi.instance_name as flow_name,

ACT_FLAG.ACT_FLAG_ACTION_TYPE,

payrollactioneo.pay_request_id,

trunc(pfi.creation_date) submission_date,

pfi.flow_instance_id,

(select 'Y' from pay_task_actions ppta

where ppta.base_task_id=pt.task_id

and BASE_TASK_ACTION_NAME='ROLLBACK'

and not exists(select 1 from pay_task_properties pptp

where pptp.base_task_action_id=pta.task_action_id

and pptp.BASE_TASK_PROPERTY_NAME='OBJ_LEVEL_ACTION_DISABLED'

and pptp.TASK_PROPERTY_VALUE='Y')

and rownum=1) OBJ_ACT_ROLLBACK_ALLOWED,

(select 'Y' from pay_task_actions ppta

where ppta.base_task_id=pt.task_id

and BASE_TASK_ACTION_NAME='MARK_FOR_RETRY'

and not exists(select 1 from pay_task_properties pptp

where pptp.base_task_action_id=pta.task_action_id

and pptp.BASE_TASK_PROPERTY_NAME='OBJ_LEVEL_ACTION_DISABLED'

and pptp.TASK_PROPERTY_VALUE='Y')

and rownum=1) OBJ_ACT_MARK_FOR_RETRY_ALLOWED

FROM hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup,

pay_object_actions pay_obj_act,

pay_payroll_actions payrollactioneo,

pay_requests prq,

pay_flow_instances pfi,

FUSION.pay_task_actions pta,

pay_tasks_vl pt,

per_legislative_data_groups_vl ldg,

(SELECT LOOKUP_CODE,'1' ACT_FLAG_ACTION_TYPE FROM HCM_LOOKUPS ACT WHERE LOOKUP_CODE IN('B','C','CP','CA','CQ','CR','D','E','EC','GI','H','L','M','P','PP','PS','PRU','Q','R','RG','S','T','TC','U','X','XWr','V','Wr')

AND LOOKUP_TYPE='ACTION_TYPE') ACT_FLAG

WHERE PAYROLLACTIONEO.ACTION_TYPE=ACT_FLAG.LOOKUP_CODE(+)

AND pay_obj_act.OBJECT_TYPE NOT IN ('PAYROLL_RELATIONSHIP_ID','PERSON_ID','HR_ASSIGNMENT_ID','PAYROLL_REL_ACTION_ID','BIPOUTPUT','PAYROLL_ACTION_ID','TIME','PAY_ASSIGNMENT_ID')

AND pfi.flow_instance_id=prq.flow_instance_id

AND actiontypelookup.lookup_type = 'ACTION_TYPE'

AND actiontypelookup.lookup_code =payrollactioneo.action_type

AND actionstatuslookup.lookup_type = 'ORA_PAY_WU_ACTION_STATUS'

AND actionstatuslookup.lookup_code = pay_obj_act.ACTION_STATUS

AND payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id

AND prq.pay_request_id (+) = payrollactioneo.pay_request_id

AND prq.pay_task_action_id = pta.task_action_id (+)

AND pta.base_task_id = pt.task_id (+)

AND payrollactioneo.legislative_data_group_id = ldg.legislative_data_group_id (+)

AND ((pt.legislative_data_group_id IS NOT NULL

AND pt.legislation_code IS NULL

AND pt.legislative_data_group_id = ldg.legislative_data_group_id)

OR (pt.legislation_code IS NOT NULL

AND pt.legislative_data_group_id IS NULL

AND pt.legislation_code =ldg.legislation_code

AND ( NOT EXISTS

(SELECT TASK_ID

FROM FUSION.PAY_TASK_ACTIONS_VL c1

WHERE pt.BASE_TASK_ID = c1.BASE_TASK_ID

AND (c1.legislative_data_group_id IS NOT NULL

AND c1.legislation_code IS NULL

AND c1.legislative_data_group_id = ldg.legislative_data_group_id )

)))

OR ( pt.legislative_data_group_id IS NULL

AND pt.legislation_code IS NULL

AND ( NOT EXISTS

(SELECT TASK_ID

FROM FUSION.PAY_TASK_ACTIONS_VL c2

WHERE pt.BASE_TASK_ID = c2.BASE_TASK_ID

AND (( c2.legislative_data_group_id IS NOT NULL

AND c2.legislation_code IS NULL

AND c2.legislative_data_group_id = ldg.legislative_data_group_id)

OR ( c2.legislation_code IS NOT NULL

AND c2.legislative_data_group_id IS NULL

AND c2.legislation_code = ldg.legislation_code ))

))))

) ZZZ

order by zzz.process_date desc, zzz.object_Action_id desc, decode(zzz.action_status_code,'P',0,'E',1,'U',2,'I',3,'V',4,'S',5,'M',6,7)