1. Tables and Views for HCM
  2. PAY_SEARCH_ACTIONS_V

PAY_SEARCH_ACTIONS_V

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

LIST_NAME

FULL_NAME

PERSON_NUMBER

OBJECT_TYPE

ACTION_CODE

PAYROLL_RELATIONSHIP_NUMBER

SOURCE_ACTION_ID

OBJECT_ACTION_ID

STATUS

ACTION_STATUS_CODE

ACTION_TYPE

ACTION_TYPE_CODE

PAYROLL_ACTION_ID

PAYROLL_REL_ACTION_ID

PAYROLL_RELATIONSHIP_ID

OBJECT_ID

PERSON_ID

TITLE

FIRST_NAME

MIDDLE_NAMES

LAST_NAME

PAYROLL_NAME

PAYROLL_ID

DATE_EARNED

PROCESS_DATE

STATUTORY_PERIOD_NAME

EARN_PERIOD_NAME

EARN_PERIOD_NUM

STAT_PERIOD_NUM

LEGISLATIVE_DATA_GROUP_ID

CHILD_ACTION_ID

ASSIGNMENT_ID

LOCKINGFLAG

LOCKINGSTATUS

TASK_NAME

Query

SQL_Statement

SELECT

decode(LIST_NAME,'###',

decode(replace(replace(replace(replace(replace(object_type,'pspra.arcret_ract_id','PRA'),'pract.payroll_rel_action_id','PRA'),'pspra.payroll_rel_action_id','PRA'),'pra.payroll_rel_action_id','PRA'),'target.payroll_rel_action_id','PRA'),

'PRA','Payroll Relationship Number='||(select nvl(max(prl.payroll_relationship_number),object_type||'-'||OBJECT_ACTION_ID)

from pay_pay_relationships_dn prl,

PAY_PAYROLL_REL_ACTIONS pra

where pra.payroll_rel_action_id=Object_id

and prl.payroll_relationship_id = pra.payroll_relationship_id),

'target.multi_threading_id','Payroll Relationship Number='||(select nvl(max(prl.payroll_relationship_number),object_type||'-'||OBJECT_ACTION_ID)

from pay_pay_relationships_dn prl,

PAY_PAYROLL_REL_ACTIONS pra,

pay_action_contexts ppp

where ppp.action_context_id = Object_id

and pra.payroll_rel_action_id=ppp.payroll_rel_action_id

and prl.payroll_relationship_id = pra.payroll_relationship_id),

'ASG','Assignment Number='||(select nvl(max(prl.assignment_number),object_type||'-'||OBJECT_ACTION_ID)

from per_all_assignments_f prl

where prl.assignment_id=Object_id),

object_type||'='||OBJECT_ACTION_ID),

LIST_NAME) as

LIST_NAME,

FULL_NAME ,

PERSON_NUMBER ,

OBJECT_TYPE,

ACTION_CODE,

PAYROLL_RELATIONSHIP_NUMBER ,

SOURCE_ACTION_ID ,

OBJECT_ACTION_ID,

STATUS ,

ACTION_STATUS_CODE ,

ACTION_TYPE ,

ACTION_TYPE_CODE ,

PAYROLL_ACTION_ID ,

PAYROLL_REL_ACTION_ID ,

PAYROLL_RELATIONSHIP_ID ,

Object_id,

PERSON_ID ,

TITLE,

FIRST_NAME ,

MIDDLE_NAMES ,

LAST_NAME ,

PAYROLL_NAME,

PAYROLL_ID ,

DATE_EARNED ,

DECODE(PROCESS_DATE,TO_DATE('900/01/01', 'yyyy/mm/dd'),NULL,PROCESS_DATE) AS PROCESS_DATE,

STATUTORY_PERIOD_NAME ,

Earn_period_name,

Earn_period_num,

Stat_period_num,

LEGISLATIVE_DATA_GROUP_ID,

CHILD_ACTION_ID ,

ASSIGNMENT_ID ,

LOCKINGFLAG ,

LOCKINGSTATUS ,

TASK_NAME

FROM

(SELECT personnamedeo.LIST_NAME AS list_name,

personnamedeo.DISPLAY_NAME AS full_name,

personeo.person_number AS person_number,

'Relationship' AS object_type,

'PRA' AS ACTION_CODE,

payrelationshipeo.payroll_relationship_number,

payrelactioneo.source_action_id,

payrelactioneo.payroll_rel_action_id AS OBJECT_ACTION_ID,

actionstatuslookup.meaning status,

actionstatuslookup.lookup_code action_status_code,

actiontypelookup.meaning action_type,

actiontypelookup.lookup_code action_type_code,

payrollactioneo.payroll_action_id,

payrelactioneo.payroll_rel_action_id,

payrelactioneo.payroll_relationship_id,

payrelactioneo.payroll_relationship_id Object_id,

payrelationshipeo.person_id,

personnamedeo.title,

personnamedeo.first_name,

personnamedeo.middle_names,

personnamedeo.last_name,

allpayrolldeo.payroll_name,

payrollactioneo.payroll_id,

payrollactioneo.date_earned AS date_earned,

payrollactioneo.effective_date AS process_date,

paytimeperiodseo.period_name AS statutory_period_name,

TimePeriodPEOEarn.period_name AS Earn_period_name,

TimePeriodPEOEarn.PERIOD_NUM AS Earn_period_num,

paytimeperiodseo.PERIOD_NUM AS Stat_period_num,

payrelationshipeo.LEGISLATIVE_DATA_GROUP_ID,

NVL(

(SELECT MAX(ch.payroll_rel_action_id)

FROM pay_payroll_rel_actions ch

WHERE ch.payroll_action_id = payrollactioneo.payroll_action_id

AND ch.source_action_id = payrelactioneo.payroll_rel_action_id

AND ch.RETRO_COMPONENT_ID IS NULL) ,payrelactioneo.payroll_rel_action_id) AS child_Action_id,

(SELECT MAX(asg.assignment_id)

FROM pay_rel_groups_dn asg

WHERE asg.payroll_relationship_id = payrelactioneo.payroll_relationship_id

) AS assignment_id,

NVL(

(SELECT MAX(l.locking_action_id) AS lockingId

FROM pay_action_interlocks l

WHERE l.locked_action_id=payrelactioneo.payroll_rel_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=payrelactioneo.payroll_rel_action_id

),-1)

) AS LockingStatus ,

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

FROM pay_payroll_actions payrollactioneo,

pay_all_payrolls_f allpayrolldeo,

pay_payroll_rel_actions payrelactioneo,

pay_pay_relationships_dn payrelationshipeo,

per_person_names_f personnamedeo,

hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup,

pay_time_periods paytimeperiodseo,

per_all_people_f personeo,

pay_requests prq,

fusion.pay_task_actions pta,

fusion.pay_tasks_vl pt,

PAY_TIME_PERIODS TimePeriodPEOEarn,

fusion.per_legislative_data_groups ldg

WHERE personeo.person_id =personnamedeo.person_id

AND payrollactioneo.payroll_id = allpayrolldeo.payroll_id (+)

AND payrelactioneo.payroll_action_id = payrollactioneo.payroll_action_id

AND payrelactioneo.payroll_relationship_id =payrelationshipeo.payroll_relationship_id

AND payrelationshipeo.person_id = personnamedeo.person_id

AND payrollactioneo.effective_date BETWEEN allpayrolldeo.effective_start_date (+) AND allpayrolldeo.effective_end_date (+)

AND payrollactioneo.effective_date BETWEEN personnamedeo.effective_start_date AND personnamedeo.effective_end_date

AND payrollactioneo.effective_date BETWEEN personeo.effective_start_date AND personeo.effective_end_date

AND personnamedeo.name_type = 'GLOBAL'

AND personnamedeo.person_id = payrelationshipeo.person_id

AND actiontypelookup.lookup_type = 'ACTION_TYPE'

AND actiontypelookup.lookup_code = payrollactioneo.action_type

AND actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS'

AND actionstatuslookup.lookup_code = payrelactioneo.action_status

AND payrollactioneo.dedn_time_period_id = paytimeperiodseo.time_period_id (+)

AND payrollactioneo.earn_time_period_id = TimePeriodPEOEarn.time_period_id (+)

AND payrelactioneo.source_action_id IS NULL

AND payrelactioneo.RETRO_COMPONENT_ID IS NULL

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

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.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 report_record_id

FROM pay_action_information

where action_context_id = pay_obj_act.object_action_id

and action_context_type = 'POA'

and rownum=1

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))))

),'###')

AS list_name,

TO_CHAR( pay_obj_act.OBJECT_TYPE

||'_'

|| pay_obj_act.OBJECT_ACTION_ID) AS full_name,

NULL AS person_number,

pay_obj_act.OBJECT_TYPE,

'POA' AS ACTION_CODE,

TO_CHAR(NULL) AS payroll_relationship_number,

pay_obj_act.OBJECT_ACTION_ID AS source_action_id,

pay_obj_act.OBJECT_ACTION_ID AS 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,

pay_obj_act.OBJECT_ACTION_ID AS payroll_rel_action_id,

pay_obj_act.OBJECT_ID AS payroll_relationship_id,

pay_obj_act.OBJECT_ID Object_id,

TO_NUMBER(NULL) AS person_id,

TO_CHAR(NULL) AS title,

TO_CHAR(pay_obj_act.OBJECT_ACTION_ID) AS first_name,

TO_CHAR(NULL) AS middle_names,

TO_CHAR(NULL) AS last_name,

TO_CHAR(NULL) AS payroll_name,

TO_NUMBER(NULL) AS payroll_id,

TO_DATE(NULL) AS date_earned,

DECODE(payrollactioneo.effective_date,NULL,TO_DATE('900/01/01', 'yyyy/mm/dd'),payrollactioneo.effective_date) AS process_date,

TO_CHAR(NULL) AS statutory_period_name,

TO_CHAR(NULL) AS Earn_period_name,

TO_NUMBER(NULL) AS Earn_period_num,

TO_NUMBER(NULL) AS Stat_period_num,

TO_NUMBER(NULL) AS LEGISLATIVE_DATA_GROUP_ID,

pay_obj_act.OBJECT_ACTION_ID AS child_Action_id,

TO_NUMBER(NULL) AS assignment_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 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

FROM hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup,

pay_object_actions pay_obj_act,

pay_payroll_actions payrollactioneo,

pay_requests prq,

fusion.pay_task_actions pta,

fusion.pay_tasks_vl pt,

fusion.per_legislative_data_groups ldg

WHERE actiontypelookup.lookup_type = 'ACTION_TYPE'

AND actiontypelookup.lookup_code ='X'

AND actionstatuslookup.lookup_type = 'PAY_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

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 report_record_id

FROM pay_action_information

where action_context_id = pay_obj_act.temp_object_action_id

and action_context_type = 'PTOA'

and rownum=1

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))))

),'###')

AS list_name,

TO_CHAR(pay_obj_act.OBJECT_TYPE

||'_'

|| pay_obj_act.TEMP_OBJECT_ACTION_ID) AS full_name,

NULL AS person_number,

pay_obj_act.OBJECT_TYPE,

'PTOA' AS ACTION_CODE,

TO_CHAR(NULL) AS payroll_relationship_number,

pay_obj_act.TEMP_OBJECT_ACTION_ID AS source_action_id,

pay_obj_act.TEMP_OBJECT_ACTION_ID AS 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,

pay_obj_act.TEMP_OBJECT_ACTION_ID AS payroll_rel_action_id,

pay_obj_act.OBJECT_ID AS payroll_relationship_id,

pay_obj_act.OBJECT_ID Object_id,

TO_NUMBER(NULL) AS person_id,

TO_CHAR(NULL) AS title,

TO_CHAR(pay_obj_act.TEMP_OBJECT_ACTION_ID) AS first_name,

TO_CHAR(NULL) AS middle_names,

TO_CHAR(NULL) AS last_name,

TO_CHAR(NULL) AS payroll_name,

TO_NUMBER(NULL) AS payroll_id,

TO_DATE(NULL) AS date_earned,

DECODE(payrollactioneo.effective_date,NULL,TO_DATE('900/01/01', 'yyyy/mm/dd'),payrollactioneo.effective_date) AS process_date,

TO_CHAR(NULL) AS statutory_period_name,

TO_CHAR(NULL) AS Earn_period_name,

TO_NUMBER(NULL) AS Earn_period_num,

TO_NUMBER(NULL) AS Stat_period_num,

TO_NUMBER(NULL) AS LEGISLATIVE_DATA_GROUP_ID,

pay_obj_act.TEMP_OBJECT_ACTION_ID AS child_Action_id,

TO_NUMBER(NULL) AS assignment_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 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

FROM hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup,

pay_temp_object_actions pay_obj_act,

pay_payroll_actions payrollactioneo,

pay_requests prq,

fusion.pay_task_actions pta,

fusion.pay_tasks_vl pt,

fusion.per_legislative_data_groups ldg

WHERE actiontypelookup.lookup_type = 'ACTION_TYPE'

AND actiontypelookup.lookup_code ='X'

AND actionstatuslookup.lookup_type = 'PAY_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 ))

))))

ORDER BY process_date DESC

)