1. Tables and Views for HCM
  2. PAY_PROC_NREL_ACT_RSLT_VL

PAY_PROC_NREL_ACT_RSLT_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

OBJECT_TYPE

OBJECT_ID

OBJECT_NAME

PERSON_NUMBER

OBJECT_ACTION_ID

CHILD_OBJECT_ACTION_ID

STATUS

ACTION_STATUS_CODE

ACTION_TYPE

ACTION_TYPE_CODE

PAYROLL_ACTION_ID

PROCESS_DATE

LOCKINGFLAG

LOCKINGSTATUS

FLOW_NAME

SUBMISSION_DATE

TASK_NAME

PERSON_ID

ASSIGNMENT_ID

ASSIGNMENT_NUMBER

PAYROLL_RELATIONSHIP_NUMBER

LEGISLATIVE_DATA_GROUP_ID

LEGISLATIVE_DATA_GROUP_NAME

ASSIGNMENT_STATUS

TASK_INSTANCE_ID

PAY_REQUEST_ID

ARCHIVE_CONTEXT_TYPE

ROLLBACK_ALLOWED_FLAG

MARKFORRETRY_ALLOWED_FLAG

ARCH_RESULTS_DISPLAY_FLAG

MESSAGES_DISPLAY_FLAG

PRIMARY_RESULTS

Query

SQL_Statement

SELECT

zzz.OBJECT_TYPE,

zzz.OBJECT_ID ,

PNAME.LIST_NAME object_name,

peo.person_number PERSON_NUMBER,

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.LOCKINGFLAG ,

zzz.LOCKINGSTATUS ,

zzz.flow_name,

zzz.submission_date,

zzz.TASK_NAME,

peo.person_id,

(select max(ASG.ASSIGNMENT_ID) from PER_ALL_ASSIGNMENTS_F ASG

where ASG.PERSON_ID=peo.PERSON_ID

and ASSIGNMENT_TYPE in ('E','C')

and zzz.process_date between ASG.EFFECTIVE_START_DATE and ASG.EFFECTIVE_END_DATE) ASSIGNMENT_ID,

(select max(ASG.ASSIGNMENT_NUMBER) from PER_ALL_ASSIGNMENTS_F ASG

where ASG.PERSON_ID=peo.PERSON_ID

and ASSIGNMENT_TYPE in ('E','C')

and zzz.process_date between ASG.EFFECTIVE_START_DATE and ASG.EFFECTIVE_END_DATE) ASSIGNMENT_NUMBER,

RELDN.payroll_relationship_number,

zzz.legislative_data_group_id,

zzz.legislative_data_group_name,

(select max(ASG.ASSIGNMENT_STATUS_TYPE) from PER_ALL_ASSIGNMENTS_F ASG

where ASG.PERSON_ID=peo.PERSON_ID

and ASSIGNMENT_TYPE in ('E','C')

and zzz.process_date between ASG.EFFECTIVE_START_DATE and ASG.EFFECTIVE_END_DATE) assignment_status,

zzz.task_instance_id,

zzz.pay_request_id,

zzz.archive_context_type,

decode(OBJ_ACT_ROLLBACK_ALLOWED,'Y',DECODE(ACT_FLAG_ACTION_TYPE,'1',DECODE(LOCKINGFLAG,'-1','Y','N'),'N' ),'N') AS Rollback_Allowed_FLAG,

decode(OBJ_ACT_MARK_FOR_RETRY_ALLOWED,'Y',DECODE(zzz.ACT_FLAG_ACTION_TYPE,'1',DECODE(zzz.ACTION_STATUS_CODE,'C',

DECODE(zzz.LOCKINGFLAG,'-1','Y'),DECODE(zzz.LOCKINGSTATUS,'M','Y','N')),'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','PS','Archive Results','Wr','Archive Results','XWr','Archive Results','Messages')) PRIMARY_RESULTS

FROM

(

SELECT

decode(pay_obj_act.OBJECT_TYPE,'mycsp_mt_id','PERSON_ID',pay_obj_act.OBJECT_TYPE)

OBJECT_TYPE,

pay_obj_act.PAYROLL_RELATIONSHIP_ID AS OBJECT_ID,

pay_obj_act.OBJECT_ACTION_ID,

pay_obj_act.OBJECT_ACTION_ID AS child_object_Action_id,

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(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.OBJECT_ACTION_ID

),-1) AS LockingFlag,

(SELECT NVL(rel.action_status,'N/A')

FROM pay_payroll_rel_actions rel

WHERE rel.payroll_rel_action_id = NVL(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.OBJECT_ACTION_ID

),-1)

) AS LockingStatus ,

NVL(pt.task_name,actiontypelookup.meaning) AS task_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,

trunc(pfi.creation_date) submission_date,

ACT_FLAG.ACT_FLAG_ACTION_TYPE,

'POA' as archive_context_type,

payrollactioneo.pay_request_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_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 IN ('PAYROLL_REL_ACTION_ID','PAYROLL_RELATIONSHIP_ID','PERSON_ID','HR_ASSIGNMENT_ID','TIME','mycsp_mt_id','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

decode(pay_obj_act.OBJECT_TYPE,'mycsp_mt_id','PERSON_ID',pay_obj_act.OBJECT_TYPE)

OBJECT_TYPE,

decode(pay_obj_act.OBJECT_TYPE,

'XWRID',(SELECT

max(REL.PAYROLL_RELATIONSHIP_ID) FROM PAY_PAY_RELATIONSHIPS_DN REL ,

PAY_PAYROLL_REL_ACTIONS pra_p WHERE pra_p.PAYROLL_REL_ACTION_ID=PAY_OBJ_ACT.OBJECT_ID

AND REL.PAYROLL_RELATIONSHIP_ID=pra_p.PAYROLL_RELATIONSHIP_ID),

'PERSON_ID',(SELECT

max(REL.PAYROLL_RELATIONSHIP_ID) FROM PAY_PAY_RELATIONSHIPS_DN REL WHERE

REL.PERSON_ID=PAY_OBJ_ACT.OBJECT_ID),

'PAYROLL_REL_ACTION_ID',(SELECT

max(REL.PAYROLL_RELATIONSHIP_ID) FROM PAY_PAY_RELATIONSHIPS_DN REL ,

PAY_PAYROLL_REL_ACTIONS pra_p WHERE pra_p.PAYROLL_REL_ACTION_ID=PAY_OBJ_ACT.OBJECT_ID

AND REL.PAYROLL_RELATIONSHIP_ID=pra_p.PAYROLL_RELATIONSHIP_ID),

'TIME',(SELECT

max(REL.PAYROLL_RELATIONSHIP_ID) FROM PAY_PAY_RELATIONSHIPS_DN REL ,

HWM_TM_REC_GRP TIME WHERE TIME.TM_REC_GRP_ID=PAY_OBJ_ACT.OBJECT_ID

AND REL.PERSON_ID=TIME.RESOURCE_ID AND TIME.RESOURCE_TYPE='PERSON'),

'mycsp_mt_id',(SELECT max(REL.PAYROLL_RELATIONSHIP_ID) FROM

PAY_PAY_RELATIONSHIPS_DN REL WHERE

REL.PERSON_ID=PAY_OBJ_ACT.OBJECT_ID),

'PAYROLL_RELATIONSHIP_ID',PAY_OBJ_ACT.OBJECT_ID,

'HR_ASSIGNMENT_ID',(SELECT MAX(REL.PAYROLL_RELATIONSHIP_ID) FROM

PAY_PAY_RELATIONSHIPS_DN REL,PAY_REL_GROUPS_DN RG

WHERE RG.ASSIGNMENT_ID=PAY_OBJ_ACT.OBJECT_ID AND

REL.PAYROLL_RELATIONSHIP_ID=RG.PAYROLL_RELATIONSHIP_ID AND

RG.GROUP_TYPE='A'),

'PAY_ASSIGNMENT_ID',(SELECT MAX(REL.PAYROLL_RELATIONSHIP_ID) FROM

PAY_PAY_RELATIONSHIPS_DN REL,PAY_REL_GROUPS_DN RG

WHERE RG.RELATIONSHIP_GROUP_ID=PAY_OBJ_ACT.OBJECT_ID

AND REL.PAYROLL_RELATIONSHIP_ID=RG.PAYROLL_RELATIONSHIP_ID AND

RG.GROUP_TYPE='A'),PAY_OBJ_ACT.OBJECT_ID) AS OBJECT_ID,

pay_obj_act.temp_OBJECT_ACTION_ID OBJECT_ACTION_ID,

pay_obj_act.temp_OBJECT_ACTION_ID AS child_object_Action_id,

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(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.temp_OBJECT_ACTION_ID

),-1) AS LockingFlag,

(SELECT NVL(rel.action_status,'N/A')

FROM pay_payroll_rel_actions rel

WHERE rel.payroll_rel_action_id = NVL(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=pay_obj_act.temp_OBJECT_ACTION_ID

),-1)

) AS LockingStatus ,

NVL(pt.task_name,actiontypelookup.meaning) AS task_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,

trunc(pfi.creation_date) submission_date,

ACT_FLAG.ACT_FLAG_ACTION_TYPE,

'PTOA' as archive_context_type,

payrollactioneo.pay_request_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_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 IN ('XWRID','PAYROLL_REL_ACTION_ID','PAYROLL_RELATIONSHIP_ID','PERSON_ID','HR_ASSIGNMENT_ID','TIME','mycsp_mt_id','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,

per_all_people_f peo, PAY_PAY_RELATIONSHIPS_DN

RELDN,PER_PERSON_NAMES_F PNAME

where RELDN.PERSON_ID=peo.PERSON_ID

and RELDN.PAYROLL_RELATIONSHIP_ID=zzz.OBJECT_ID

and zzz.process_date(+) between peo.EFFECTIVE_START_DATE and

peo.EFFECTIVE_END_DATE

and zzz.process_date(+) between reldn.START_DATE and reldn.END_DATE

AND PNAME.PERSON_ID = RELDN.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

and zzz.process_date(+) between PNAME.EFFECTIVE_START_DATE and

PNAME.EFFECTIVE_END_DATE

order by process_date desc, object_Action_id desc,

decode(action_status_code,'P',0,'E',1,'U',2,'I',3,'V',4,'S',5,'M',6,7)