1. Tables and Views for HCM
  2. PAY_PERSON_RESULTS_VL

PAY_PERSON_RESULTS_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

OBJECT_TYPE

OBJECT_ID

OBJECT_NAME

OBJECT_ACTION_ID

SOURCE_ACTION_ID

OBJECT_TYPE_CODE

STATUS

ACTION_STATUS_CODE

ACTION_STATUS_LOOKUP_TYPE

ACTION_TYPE

ACTION_TYPE_CODE

PAYROLL_ACTION_ID

PROCESS_DATE

PROCESS_START_DATE

PROCESS_END_DATE

FLOW_NAME

SUBMISSION_DATE

TASK_NAME

OBJECT_REFERENCE

PERSON_ID

PERSON_NUMBER

RELATIONSHIP_NUMBER

PAYROLL

PAYROLL_ID

CONSOLIDATION_SET_NAME

CONSOLIDATION_SET_ID

PAYROLL_PERIOD_NAME

PAYROLL_PERIOD_NUMBER

DATE_EARNED

ASSIGNMENT_NUMBER

ASSIGNMENT_ID

LEGISLATIVE_DATA_GROUP_ID

LEGISLATIVE_DATA_GROUP_NAME

LEGISLATION_CODE

TASK_INSTANCE_ID

PAY_REQUEST_ID

FLOW_INSTANCE_ID

TASK_ID

BASE_FLOW_ID

RUN_TYPE_NAME

RUN_TYPE_ID

RETRO_RUN_ID

PRIMARY_RESULTS

ADDITIONAL_RESULTS

EARN_START_DATE

EARN_END_DATE

Query

SQL_Statement

SELECT

zzz.OBJECT_TYPE,

zzz.OBJECT_ID ,

substrb(zzz.person_number,instrb(zzz.person_number,'#')+2, length(zzz.person_number)) object_name,

zzz.OBJECT_ACTION_ID ,

zzz.SOURCE_ACTION_ID,

zzz.OBJECT_TYPE_CODE,

zzz.STATUS ,

zzz.ACTION_STATUS_CODE ,

zzz.ACTION_STATUS_LOOKUP_TYPE ,

zzz.ACTION_TYPE ,

zzz.ACTION_TYPE_CODE ,

zzz.PAYROLL_ACTION_ID ,

zzz.PROCESS_DATE,

zzz.process_start_date,

zzz.process_end_date,

zzz.flow_name,

zzz.submission_date,

zzz.TASK_NAME,

zzz.object_reference,

zzz.person_id,

substrb(zzz.person_number,1,instrb(zzz.person_number,'[#]')-1) person_number,

zzz.relationship_number,

zzz.payroll,

zzz.PAYROLL_ID,

zzz.CONSOLIDATION_SET_NAME,

zzz.CONSOLIDATION_SET_ID,

zzz.payroll_period_name,

zzz.payroll_period_number,

zzz.date_earned,

substrb(zzz.assignment_id,instrb(zzz.assignment_id,'#')+2, length(zzz.assignment_id)) assignment_number,

substrb(zzz.assignment_id,1,instrb(zzz.assignment_id,'[#]')-1) ASSIGNMENT_ID,

zzz.legislative_data_group_id,

zzz.legislative_data_group_name,

zzz.LEGISLATION_CODE,

zzz.task_instance_id,

zzz.pay_request_id,

zzz.flow_instance_id,

zzz.task_id,

zzz.base_flow_id,

zzz.RUN_TYPE_NAME,

zzz.RUN_TYPE_ID,

zzz.retro_run_id,

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

case

(select CLASSIFICATION_NAME from pay_action_classifications cls

where cls.ACTION_TYPE=zzz.ACTION_TYPE_CODE and CLASSIFICATION_NAME like '%DEFAULT')

when 'SOE_RESULTS_DEFAULT' then 'SOE_RESULTS'

when 'RETRO_RESULTS_DEFAULT' then 'RETRO_RESULTS'

when 'RUN_RESULTS_DEFAULT' then 'RUN_RESULTS'

when 'PREPAY_RESULTS_DEFAULT' then 'PREPAY_RESULTS'

when 'PAYMENT_RESULTS_DEFAULT' then 'PAYMENT_RESULTS'

when 'COST_RESULTS_DEFAULT' then 'COST_RESULTS'

when 'COST_PAY_RESULTS_DEFAULT' then 'COST_PAY_RESULTS'

when 'BAL_ADJ_RESULTS_DEFAULT' then 'BAL_ADJ_RESULTS'

when 'BAL_RESULTS_DEFAULT' then 'BAL_RESULTS'

when 'ARCHIVE_RESULTS_DEFAULT' then 'ARCHIVE_RESULTS'

else 'MESSAGES'

end

) PRIMARY_RESULTS,

DECODE(zzz.action_status_code,'E',null,(select LISTAGG(CLASSIFICATION_NAME,'|')

from pay_action_classifications cls

where cls.ACTION_TYPE=zzz.ACTION_TYPE_CODE

and CLASSIFICATION_NAME like '%RESULTS')) ADDITIONAL_RESULTS,

Earn_start_date,

Earn_end_date

FROM

(

SELECT pay_obj_act.object_type OBJECT_TYPE,

prl.payroll_relationship_id as OBJECT_ID,

pay_obj_act.OBJECT_ID OBJECT_TYPE_ID,

pay_obj_act.object_ACTION_ID OBJECT_ACTION_ID,

pay_obj_act.SOURCE_ACTION_ID,

decode(payrollactioneo.action_type,'L',pay_obj_act.object_ACTION_ID,pay_obj_act.source_id) retro_run_id,

pay_obj_act.ACTION_TYPE OBJECT_TYPE_CODE,

actionstatuslookup.meaning status,

actionstatuslookup.lookup_code action_status_code,

actionstatuslookup.lookup_type action_status_lookup_type,

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,

payrollactioneo.start_date process_start_date,

payrollactioneo.end_date process_end_date,

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

(select peo.person_number||'[#]'||pname.LIST_NAME

from per_all_people_f peo,per_person_names_f pname

where peo.person_id = prl.person_id

and peo.person_id = pname.person_id

and pname.name_type= 'GLOBAL'

and peo.effective_start_Date = (Select max(effective_start_Date)

from per_all_people_f papf where papf.person_id = prl.person_id)

and pname.effective_start_Date = (Select max(effective_start_Date)

from per_person_names_f papf where papf.person_id = prl.person_id)) person_number,

ppp.payroll_name object_reference,

prl.person_id,

prl.payroll_relationship_number relationship_number,

ppp.payroll_name payroll,

ppp.PAYROLL_ID,

pcs.CONSOLIDATION_SET_NAME,

pcs.CONSOLIDATION_SET_ID,

TimePeriodPEOEarn.period_name payroll_period_name,

paytimeperiodseo.PERIOD_NUM payroll_period_number,

payrollactioneo.date_earned date_earned,

(SELECT MAX(asg.assignment_id)||'[#]'||max(asg.assignment_NUMBER)

FROM pay_rel_groups_dn asg

WHERE asg.payroll_relationship_id = prl.payroll_relationship_id and asg.group_type='A'

) AS assignment_id,

ldg.legislative_data_group_id legislative_data_group_id,

ldg.name legislative_data_group_name,

ldg.LEGISLATION_CODE,

prq.flow_task_instance_id task_instance_id,

RunTypeDPEO.RUN_TYPE_NAME,

RunTypeDPEO.RUN_TYPE_ID,

pfi.instance_name as flow_name,

pfi.base_flow_id as base_flow_id,

trunc(pfi.creation_date) submission_date,

payrollactioneo.pay_request_id,

pfi.flow_instance_id,

TimePeriodPEOEarn.START_DATE Earn_start_date,

TimePeriodPEOEarn.END_DATE Earn_end_date,

pt.task_id

FROM hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup,

pay_all_actions pay_obj_act,

pay_payroll_actions payrollactioneo,

pay_requests prq,

FUSION.pay_task_actions pta,

pay_tasks_vl pt,

per_legislative_data_groups_vl ldg,

pay_pay_relationships_dn prl,

pay_all_payrolls_f ppp,

pay_consolidation_sets pcs,

PAY_TIME_PERIODS TimePeriodPEOEarn,

pay_time_periods paytimeperiodseo,

PAY_RUN_TYPES_VL RunTypeDPEO,

pay_flow_instances pfi

WHERE 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 payrollactioneo.dedn_time_period_id = paytimeperiodseo.time_period_id (+)

AND payrollactioneo.earn_time_period_id =TimePeriodPEOEarn.TIME_PERIOD_ID (+)

AND pay_obj_act.RUN_TYPE_ID = RunTypeDPEO.RUN_TYPE_ID (+)

and ((pay_obj_act.SOURCE_ACTION_ID is not null and payrollactioneo.action_type in('R','Q','V','B'))

or (pay_obj_act.SOURCE_ACTION_ID is null and payrollactioneo.action_type in ('R','Q','V','B') and pay_obj_act.action_status='E')

or (pay_obj_act.SOURCE_ACTION_ID is null and payrollactioneo.action_type in('R','Q','V','B') and pay_obj_act.action_status='R')

or (pay_obj_act.SOURCE_ACTION_ID is null and payrollactioneo.action_type in('R','Q','V','B') and pay_obj_act.action_status='U')

or (pay_obj_act.SOURCE_ACTION_ID is null and payrollactioneo.action_type in('R','Q','V','B')

and pay_obj_act.action_status='C'

and not exists( select 1 from pay_payroll_rel_actions pra1

where pay_obj_act.OBJECT_ACTION_ID = pra1.source_action_id))

or (pay_obj_act.SOURCE_ACTION_ID is null and payrollactioneo.action_type not in('R','Q','V','B'))

)

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

))))

and prl.payroll_relationship_id(+) = pay_obj_act.payroll_relationship_id

and payrollactioneo.payroll_id=ppp.payroll_id (+)

and pcs.CONSOLIDATION_SET_ID(+)=ppp.CONSOLIDATION_SET_ID

and payrollactioneo.effective_date between ppp.effective_start_Date (+) and ppp.effective_end_date (+)

AND (payrollactioneo.EFFECTIVE_DATE BETWEEN RunTypeDPEO.EFFECTIVE_START_DATE (+) AND RunTypeDPEO.EFFECTIVE_END_DATE (+) )

) zzz