PAY_PER_RESULTS_SEARCH

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
OBJECT_ID OBJECT_ACTION_ID CHILD_OBJECT_ACTION_ID SOURCE_ACTION_ID ACTION_STATUS_CODE ACTION_TYPE_CODE PAYROLL_ACTION_ID PROCESS_DATE PROCESS_START_DATE PROCESS_END_DATE CONSOLIDATION_SET_NAME CONSOLIDATION_SET_ID DATE_EARNED RETRO_RUN_ID PRIMARY_RESULTS ADDITIONAL_RESULTS PAYMENT_REASON PAYROLL_ACTION_STATUS PERSON_ID RELATIONSHIP_NUMBER RUN_TYPE_ID TASK_INSTANCE_ID FLOW_INSTANCE_NAME FLOW_NAME BASE_FLOW_ID SUBMISSION_DATE SUBMITTED_BY PAY_REQUEST_ID FLOW_INSTANCE_ID TASK_ID LEGISLATIVE_DATA_GROUP_NAME LEGISLATIVE_DATA_GROUP_ID OBJECT_TYPE OBJECT_NAME OBJECT_TYPE_CODE STATUS ACTION_STATUS_LOOKUP_TYPE ACTION_TYPE TASK_NAME OBJECT_REFERENCE PERSON_NUMBER PAYROLL PAYROLL_ID PAYROLL_PERIOD_NAME PAYROLL_PERIOD_NUMBER ASSIGNMENT_NUMBER ASSIGNMENT_ID LEGISLATION_CODE EARN_START_DATE EARN_END_DATE EARN_TIME_PERIOD_ID RUN_TYPE_NAME

Name

OBJECT_ID

OBJECT_ACTION_ID

CHILD_OBJECT_ACTION_ID

SOURCE_ACTION_ID

ACTION_STATUS_CODE

ACTION_TYPE_CODE

PAYROLL_ACTION_ID

PROCESS_DATE

PROCESS_START_DATE

PROCESS_END_DATE

CONSOLIDATION_SET_NAME

CONSOLIDATION_SET_ID

DATE_EARNED

RETRO_RUN_ID

PRIMARY_RESULTS

ADDITIONAL_RESULTS

PAYMENT_REASON

PAYROLL_ACTION_STATUS

PERSON_ID

RELATIONSHIP_NUMBER

RUN_TYPE_ID

TASK_INSTANCE_ID

FLOW_INSTANCE_NAME

FLOW_NAME

BASE_FLOW_ID

SUBMISSION_DATE

SUBMITTED_BY

PAY_REQUEST_ID

FLOW_INSTANCE_ID

TASK_ID

LEGISLATIVE_DATA_GROUP_NAME

LEGISLATIVE_DATA_GROUP_ID

OBJECT_TYPE

OBJECT_NAME

OBJECT_TYPE_CODE

STATUS

ACTION_STATUS_LOOKUP_TYPE

ACTION_TYPE

TASK_NAME

OBJECT_REFERENCE

PERSON_NUMBER

PAYROLL

PAYROLL_ID

PAYROLL_PERIOD_NAME

PAYROLL_PERIOD_NUMBER

ASSIGNMENT_NUMBER

ASSIGNMENT_ID

LEGISLATION_CODE

EARN_START_DATE

EARN_END_DATE

EARN_TIME_PERIOD_ID

RUN_TYPE_NAME

Query

SQL_Statement
SELECT zzz.OBJECT_ID , zzz.OBJECT_ACTION_ID , (select min(ch.payroll_rel_action_id) FROM pay_payroll_rel_actions ch WHERE ch.payroll_action_id = zzz.payroll_action_id AND ch.source_action_id = zzz.OBJECT_ACTION_ID AND ch.RETRO_COMPONENT_ID IS NULL) CHILD_OBJECT_ACTION_ID, zzz.SOURCE_ACTION_ID, zzz.ACTION_STATUS_CODE , zzz.ACTION_TYPE_CODE , zzz.PAYROLL_ACTION_ID , zzz.PROCESS_DATE, zzz.process_start_date, zzz.process_end_date, zzz.CONSOLIDATION_SET_NAME, zzz.CONSOLIDATION_SET_ID, zzz.date_earned, 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,'\|') WITHIN GROUP (ORDER BY CLASSIFICATION_NAME ASC) from pay_action_classifications cls where cls.ACTION_TYPE=zzz.ACTION_TYPE_CODE and CLASSIFICATION_NAME like '%RESULTS' )) ADDITIONAL_RESULTS, PAYMENT_REASON, PAYROLL_ACTION_STATUS, person_id, Relationship_number, RUN_TYPE_ID, task_instance_id, flow_name FLOW_INSTANCE_NAME, flow_name, base_flow_id, submission_date, SUBMITTED_BY, pay_request_id, flow_instance_id, task_id, null LEGISLATIVE_DATA_GROUP_NAME , null LEGISLATIVE_DATA_GROUP_ID, null OBJECT_TYPE, null OBJECT_NAME, null OBJECT_TYPE_CODE, STATUS, ACTION_STATUS_LOOKUP_TYPE, ACTION_TYPE, null TASK_NAME, null OBJECT_REFERENCE, null person_number, null payroll, null PAYROLL_ID, null payroll_period_name, null payroll_period_number, null assignment_number, null ASSIGNMENT_ID, null LEGISLATION_CODE, null Earn_start_date, null Earn_end_date, null earn_time_period_id, null RUN_TYPE_NAME FROM (SELECT pay_obj_act.payroll_relationship_id as OBJECT_ID, pay_obj_act.payroll_rel_action_id OBJECT_ACTION_ID, pay_obj_act.SOURCE_ACTION_ID, decode(payrollactioneo.action_type,'L',pay_obj_act.payroll_rel_action_id,pay_obj_act.source_id) retro_run_id, pay_obj_act.action_status action_status_code, payrollactioneo.action_type 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, (select max(CONSOLIDATION_SET_NAME) from pay_consolidation_sets pcs where pcs.CONSOLIDATION_SET_ID=payrollactioneo.CONSOLIDATION_SET_ID) CONSOLIDATION_SET_NAME, payrollactioneo.CONSOLIDATION_SET_ID, payrollactioneo.date_earned date_earned, payrollactioneo.PAYMENT_REASON, payrollactioneo.ACTION_STATUS PAYROLL_ACTION_STATUS, actionstatuslookup.meaning STATUS, actionstatuslookup.lookup_type ACTION_STATUS_LOOKUP_TYPE, actiontypelookup.meaning ACTION_TYPE, prl.person_id, prl.payroll_relationship_number relationship_number, pay_obj_act.RUN_TYPE_ID, prq.flow_task_instance_id task_instance_id, pfi.instance_name as flow_name, pfi.base_flow_id as base_flow_id, (pfi.creation_date) submission_date, (pfi.CREATED_BY) SUBMITTED_BY, payrollactioneo.pay_request_id, pfi.flow_instance_id, pft.base_task_id task_id FROM pay_payroll_rel_actions pay_obj_act, pay_payroll_actions payrollactioneo, pay_pay_relationships_dn prl, pay_requests prq, pay_flow_instances pfi, pay_flow_task_instances pfti, pay_flow_tasks pft, hcm_lookups actiontypelookup, hcm_lookups actionstatuslookup WHERE payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id AND pay_obj_act.SOURCE_ACTION_ID is NULL 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 pfi.flow_instance_id = prq.flow_instance_id and prl.payroll_relationship_id = pay_obj_act.payroll_relationship_id AND prq.pay_request_id = payrollactioneo.pay_request_id AND prq.flow_task_instance_id = pfti.flow_task_instance_id AND pfti.base_flow_task_id = pft.flow_task_id and payrollactioneo.creation_date >= decode(nvl(FND_PROFILE.value('ORA_PAY_ELASTIC_SEARCH_ENABLED'),'N'),'N',sysdate -1,ADD_MONTHS(sysdate,-3)) and ( (payrollactioneo.action_type in('R','Q','P','U','M','H','A','E','PP','PS','CP','L','T','D','TC','TP','V')) or exists ( select 1 from pay_report_categories prc, pay_report_groups prg where payrollactioneo.REPORT_CATEGORY_ID=prc.REPORT_CATEGORY_ID and prc.REPORT_GROUP_ID= prg.REPORT_GROUP_ID and prg.short_name = 'PAYSLIP') ) ) zzz

SQL_Statement

SELECT zzz.OBJECT_ID ,

zzz.OBJECT_ACTION_ID ,

(select min(ch.payroll_rel_action_id)

FROM pay_payroll_rel_actions ch

WHERE ch.payroll_action_id = zzz.payroll_action_id

AND ch.source_action_id = zzz.OBJECT_ACTION_ID

AND ch.RETRO_COMPONENT_ID IS NULL) CHILD_OBJECT_ACTION_ID,

zzz.SOURCE_ACTION_ID,

zzz.ACTION_STATUS_CODE ,

zzz.ACTION_TYPE_CODE ,

zzz.PAYROLL_ACTION_ID ,

zzz.PROCESS_DATE,

zzz.process_start_date,

zzz.process_end_date,

zzz.CONSOLIDATION_SET_NAME,

zzz.CONSOLIDATION_SET_ID,

zzz.date_earned,

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

WITHIN GROUP (ORDER BY CLASSIFICATION_NAME ASC)

from pay_action_classifications cls

where cls.ACTION_TYPE=zzz.ACTION_TYPE_CODE

and CLASSIFICATION_NAME like '%RESULTS' )) ADDITIONAL_RESULTS,

PAYMENT_REASON,

PAYROLL_ACTION_STATUS,

person_id,

Relationship_number,

RUN_TYPE_ID,

task_instance_id,

flow_name FLOW_INSTANCE_NAME,

flow_name,

base_flow_id,

submission_date,

SUBMITTED_BY,

pay_request_id,

flow_instance_id,

task_id,

null LEGISLATIVE_DATA_GROUP_NAME ,

null LEGISLATIVE_DATA_GROUP_ID,

null OBJECT_TYPE,

null OBJECT_NAME,

null OBJECT_TYPE_CODE,

STATUS,

ACTION_STATUS_LOOKUP_TYPE,

ACTION_TYPE,

null TASK_NAME,

null OBJECT_REFERENCE,

null person_number,

null payroll,

null PAYROLL_ID,

null payroll_period_name,

null payroll_period_number,

null assignment_number,

null ASSIGNMENT_ID,

null LEGISLATION_CODE,

null Earn_start_date,

null Earn_end_date,

null earn_time_period_id,

null RUN_TYPE_NAME

FROM

(SELECT

pay_obj_act.payroll_relationship_id as OBJECT_ID,

pay_obj_act.payroll_rel_action_id OBJECT_ACTION_ID,

pay_obj_act.SOURCE_ACTION_ID,

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

pay_obj_act.action_status action_status_code,

payrollactioneo.action_type 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,

(select max(CONSOLIDATION_SET_NAME)

from pay_consolidation_sets pcs

where pcs.CONSOLIDATION_SET_ID=payrollactioneo.CONSOLIDATION_SET_ID) CONSOLIDATION_SET_NAME,

payrollactioneo.CONSOLIDATION_SET_ID,

payrollactioneo.date_earned date_earned,

payrollactioneo.PAYMENT_REASON,

payrollactioneo.ACTION_STATUS PAYROLL_ACTION_STATUS,

actionstatuslookup.meaning STATUS,

actionstatuslookup.lookup_type ACTION_STATUS_LOOKUP_TYPE,

actiontypelookup.meaning ACTION_TYPE,

prl.person_id,

prl.payroll_relationship_number relationship_number,

pay_obj_act.RUN_TYPE_ID,

prq.flow_task_instance_id task_instance_id,

pfi.instance_name as flow_name,

pfi.base_flow_id as base_flow_id,

(pfi.creation_date) submission_date,

(pfi.CREATED_BY) SUBMITTED_BY,

payrollactioneo.pay_request_id,

pfi.flow_instance_id,

pft.base_task_id task_id

FROM pay_payroll_rel_actions pay_obj_act,

pay_payroll_actions payrollactioneo,

pay_pay_relationships_dn prl,

pay_requests prq,

pay_flow_instances pfi,

pay_flow_task_instances pfti,

pay_flow_tasks pft,

hcm_lookups actiontypelookup,

hcm_lookups actionstatuslookup

WHERE payrollactioneo.payroll_action_id =pay_obj_act.payroll_action_id

AND pay_obj_act.SOURCE_ACTION_ID is NULL

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 pfi.flow_instance_id = prq.flow_instance_id

and prl.payroll_relationship_id = pay_obj_act.payroll_relationship_id

AND prq.pay_request_id = payrollactioneo.pay_request_id

AND prq.flow_task_instance_id = pfti.flow_task_instance_id

AND pfti.base_flow_task_id = pft.flow_task_id

and payrollactioneo.creation_date >= decode(nvl(FND_PROFILE.value('ORA_PAY_ELASTIC_SEARCH_ENABLED'),'N'),'N',sysdate -1,ADD_MONTHS(sysdate,-3))

and (

(payrollactioneo.action_type in('R','Q','P','U','M','H','A','E','PP','PS','CP','L','T','D','TC','TP','V'))

or

exists (

select 1

from pay_report_categories prc,

pay_report_groups prg

where payrollactioneo.REPORT_CATEGORY_ID=prc.REPORT_CATEGORY_ID

and prc.REPORT_GROUP_ID= prg.REPORT_GROUP_ID

and prg.short_name = 'PAYSLIP')

)

) zzz