PAY_PROC_REL_ACT_RSLT_VL

Details

• Schema: FUSION

• Object owner: PAY

• Object type: VIEW

Columns

Name

OBJECT_TYPE OBJECT_ID OBJECT_NAME OBJECT_ACTION_ID CHILD_OBJECT_ACTION_ID PREPAY_REL_ACTION_ID ACTION_CODE STATUS ACTION_STATUS_CODE ACTION_TYPE ACTION_TYPE_CODE PAYROLL_ACTION_ID PROCESS_DATE ACCOUNTING_START_DATE ACCOUNTING_END_DATE LOCKINGFLAG LOCKINGSTATUS FLOW_NAME SUBMISSION_DATE TASK_NAME BASE_TASK_NAME OBJECT_REFERENCE PERSON_ID PERSON_NUMBER RELATIONSHIP_NUMBER PAYROLL PAYROLL_ID CONSOLIDATION_SET_NAME PAYROLL_PERIOD_NAME PAYROLL_PERIOD_NUMBER DATE_EARNED ASSIGNMENT_NUMBER LOCATION_NAME JOB_NAME ASSIGNMENT_ID RELATIONSHIP_TYPE KEYWORDS LEGISLATIVE_DATA_GROUP_ID LEGISLATIVE_DATA_GROUP_NAME ASSIGNMENT_STATUS TASK_INSTANCE_ID PAY_REQUEST_ID DEF_CATEGORY_TYPE DEF_SUB_CATEGORY_TYPE RUN_TYPE_NAME OBJ_ACT_ROLLBACK_ALLOWED OBJ_ACT_MARK_FOR_RETRY_ALLOWED ROLLBACK_ALLOWED_FLAG REVERSAL_ALLOWED_FLAG MARKFORRETRY_ALLOWED_FLAG SOE_DISPLAY_FLAG RUN_RESULTS_DISPLAY_FLAG RETRO_RESULTS_DISPLAY_FLAG BAL_RESULTS_DISPLAY_FLAG BAL_ADJ_DISPLAY_FLAG COST_RESULTS_DISPLAY_FLAG COST_PAY_RESULTS_DISPLAY_FLAG PREPAY_RESULTS_DISPLAY_FLAG PAYMT_RESULTS_DISPLAY_FLAG ARCH_RESULTS_DISPLAY_FLAG MESSAGES_DISPLAY_FLAG PRIMARY_RESULTS KEY_INFO KEY_INFO2 EARN_START_DATE EARN_END_DATE

Query

SQL_Statement

SELECT zzz.OBJECT_TYPE, zzz.OBJECT_ID , zzz.object_name, zzz.OBJECT_ACTION_ID , zzz.CHILD_OBJECT_ACTION_ID, zzz.prepay_rel_Action_id, zzz.ACTION_CODE, zzz.STATUS , zzz.ACTION_STATUS_CODE , zzz.ACTION_TYPE , zzz.ACTION_TYPE_CODE , zzz.PAYROLL_ACTION_ID , zzz.PROCESS_DATE, zzz.Accounting_Start_Date, zzz.Accounting_End_Date, zzz.LOCKINGFLAG , zzz.LOCKINGSTATUS , zzz.flow_name, zzz.submission_date, zzz.TASK_NAME, zzz.BASE_TASK_NAME, zzz.object_reference, zzz.person_id, zzz.person_number, zzz.relationship_number, zzz.payroll, zzz.PAYROLL_ID, zzz.CONSOLIDATION_SET_NAME, 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, zzz.LOCATION_NAME, zzz.JOB_NAME, substrb(zzz.assignment_id,1,instrb(zzz.assignment_id,'[#]')-1) ASSIGNMENT_ID, zzz.Relationship_type, zzz.keywords, zzz.legislative_data_group_id, zzz.legislative_data_group_name, zzz.assignment_status, zzz.task_instance_id, zzz.pay_request_id, zzz.def_category_type, zzz.def_sub_category_type, zzz.RUN_TYPE_NAME, zzz.OBJ_ACT_ROLLBACK_ALLOWED, zzz.OBJ_ACT_MARK_FOR_RETRY_ALLOWED, 'Y' AS Rollback_Allowed_FLAG, DECODE(( select max(1) from pay_payroll_actions paac2, pay_payroll_rel_actions paac where paac.payroll_rel_action_id =LOCKINGFLAG and paac2.payroll_action_id= paac.payroll_action_id and paac2.ACTION_TYPE='V'),NULL,DECODE(zzz.ACTION_STATUS_CODE,'C',DECODE(zzz.ACTION_TYPE_CODE,'R','Y','Q','Y','N'),'N'),'N') AS Reversal_Allowed_FLAG, 'Y' AS MarkForRetry_Allowed_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'R','Y','Q','Y','V','Y','B','Y','I','Y','CTG','Y','CQ','Y','N')) AS SOE_DISPLAY_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'R','Y','Q','Y','V','Y','B','Y','I','Y','CTG','Y','CQ','Y','N')) as RUN_RESULTS_DISPLAY_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'L','Y','N')) AS RETRO_RESULTS_DISPLAY_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'R','Y','Q','Y','V','Y','B','Y','I','Y','CTG','Y','CQ','Y','N')) AS BAL_RESULTS_DISPLAY_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'B','Y','N')) AS BAL_ADJ_DISPLAY_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'R','Y','Q','Y','V','Y','CTG','Y','CQ','Y','C','Y','CA','Y','S','Y','CR','Y','EC','Y','N')) AS COST_RESULTS_DISPLAY_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'CP','Y','N')) AS COST_PAY_RESULTS_DISPLAY_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'P','Y','U','Y','PRU','Y','N')) AS PREPAY_RESULTS_DISPLAY_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'H','Y','PP','Y','M','Y','E','Y','D','Y','A','Y','N')) AS PAYMT_RESULTS_DISPLAY_FLAG, DECODE(zzz.action_status_code,'E','N',DECODE(zzz.ACTION_TYPE_CODE,'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,'R','Statement of Earnings','Q','Statement of Earnings','V','Statement of Earnings','CTG','Statement of Earnings','L','Retro Results','P','Prepayment Results','U','Prepayment Results','PRU','Prepayment Results','H','Payment Results','M','Payment Results','A','Payment Results','E','Payment Results','PP','Payment Results','D','Payment Results','C','Costing Results','S','Costing Results','CA','Costing Results','EC','Costing Results','CP','Costing of payment','B','Balance Adjustment','I','Balances Results','X','Archive Results','PS','Archive Results','Wr','Archive Results','XWr','Archive Results','Messages')) PRIMARY_RESULTS, DECODE(DECODE(zzz.action_type_code,'P','PrePayment_Amount','U','PrePayment_Amount','H','Payment_Amount','PP','Payment_Amount','M','Payment_Amount','E','Payment_Amount','D','Payment_Amount' ,'R','Gross_Pay','Q','Gross_Pay','V','Gross_Pay','CTG','Gross_Pay','L','Retro_Gross_Pay','B','Gross_Pay','I','Gross_Pay' ,'CQ','CTRS','C','CTRS','CA','CTRS','CP','CTPY','S','CTRS','CR','CTRS','EC','CTRS' ,'G','Archived Information','GI','Archived Information','PS','Archived Information','X','Archived Information','XWr','Archived Information','Wr','Archived Information' ,NULL) ,'PrePayment_Amount',(SELECT 'Amount='||to_char(SUM(PPP.BASE_CURRENCY_VALUE))||','||max(ldg.default_currency_code)||'|' FROM PAY_PRE_PAYMENTS PPP, PAY_ORG_PAY_METHODS_F opm, pay_legislative_data_groups ldg WHERE PPP.PAYROLL_REL_ACTION_ID = object_action_id and ppp.ORG_PAYMENT_METHOD_ID=opm.ORG_PAYMENT_METHOD_ID and opm.legislative_Data_group_id=ldg.legislative_Data_group_id and PROCESS_DATE between opm.effective_start_date and opm.effective_end_date GROUP BY PPP.PAYROLL_REL_ACTION_ID) ,'Payment_Amount',(SELECT (SELECT MEANING FROM HCM_LOOKUPS LK1 WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS' AND LK1.LOOKUP_CODE=HR_PRE_PAY.PAYMENT_STATUS(object_action_id,action_type_code)) ||'='||to_char(ppp.value)||','||opm.CURRENCY_CODE||'|' FROM PAY_PRE_PAYMENTS PPP, pay_payroll_rel_actions pra , PAY_ORG_PAY_METHODS_F opm where pra.PRE_PAYMENT_ID = PPP.PRE_PAYMENT_ID and pra.PAYROLL_REL_ACTION_ID =object_action_id and ppp.ORG_PAYMENT_METHOD_ID=opm.ORG_PAYMENT_METHOD_ID and PROCESS_DATE between opm.effective_start_date and opm.effective_end_date) ,'CTPY',(select listagg('Amount Debited='||sum(decode(ppc.debit_or_credit,'D',ppc.value))||','||ppc.currency_code|| '|Amount Credited='||sum(decode(ppc.debit_or_credit,'C',ppc.value))||','||ppc.currency_code ,'|') from pay_payment_costs ppc where ppc.payroll_rel_action_id=CHILD_OBJECT_ACTION_ID group by ppc.payroll_rel_action_id , ppc.currency_code) ,'CTRS1', null /* removed costing stats from base query (select 'Amount Debited='||sum(decode(inputValues.UOM,'M',decode(costs.DEBIT_OR_CREDIT,'D',costs.COSTED_VALUE)))||','||elementTypes.output_currency_code|| '|Amount Credited='||sum(decode(inputValues.UOM,'M',decode(costs.DEBIT_OR_CREDIT,'C',costs.COSTED_VALUE)))||','||elementTypes.output_currency_code from pay_costs costs, pay_element_types_vl elementTypes, pay_input_values_vl inputValues where costs.payroll_rel_action_id=CHILD_OBJECT_ACTION_ID and costs.input_value_id = inputValues.input_value_id and elementTypes.element_type_id=inputValues.element_type_id group by costs.payroll_rel_action_id,elementTypes.output_currency_code) */ ,'Archived Information',null ,null) KEY_INFO, DECODE(DECODE(zzz.action_type_code,'R','CTRS','Q','CTRS','V','CTRS','CTG','CTRS','L','CTRS','B','CTRS','I','CTRS' ,'CQ','CTRS','C','CTRS','CA','CTRS','S','CTRS','CR','CTRS','EC','CTRS',NULL) ,'CTRS',null /* removed costing stats from base query (select 'Amount Debited='||sum(decode(inputValues.UOM,'M',decode(costs.DEBIT_OR_CREDIT,'D',costs.COSTED_VALUE)))||','||elementTypes.output_currency_code|| '|Amount Credited='||sum(decode(inputValues.UOM,'M',decode(costs.DEBIT_OR_CREDIT,'C',costs.COSTED_VALUE)))||','||elementTypes.output_currency_code from pay_costs costs, pay_element_types_vl elementTypes, pay_input_values_vl inputValues where costs.payroll_rel_action_id=OBJECT_ACTION_ID and costs.input_value_id = inputValues.input_value_id and elementTypes.element_type_id=inputValues.element_type_id group by costs.payroll_rel_action_id,elementTypes.output_currency_code) */ ,null) KEY_INFO2, Earn_start_date, Earn_end_date FROM ( SELECT 'PRA' OBJECT_TYPE, prl.payroll_relationship_id as OBJECT_ID, pay_obj_act.payroll_rel_ACTION_ID OBJECT_ACTION_ID, DECODE(actionstatuslookup.lookup_code,'E',pay_obj_act.payroll_rel_action_id, nvl(decode(actiontypelookup.lookup_code, 'Q',(SELECT MIN(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 = pay_obj_act.payroll_rel_action_id AND ch.RETRO_COMPONENT_ID IS NULL ), 'R', (SELECT MIN(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 = pay_obj_act.payroll_rel_action_id AND ch.RETRO_COMPONENT_ID IS NULL ), 'V', (SELECT MIN(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 = pay_obj_act.payroll_rel_action_id AND ch.RETRO_COMPONENT_ID IS NULL ),pay_obj_act.payroll_rel_action_id),pay_obj_act.payroll_rel_action_id)) AS child_object_Action_id, (SELECT max(ch.payroll_rel_ACTION_ID) from pay_action_interlocks l, pay_payroll_rel_actions ch , pay_payroll_actions ppa where locked_action_id=pay_obj_act.payroll_rel_ACTION_ID and ch.payroll_rel_ACTION_ID=l.locking_action_id and ch.payroll_action_id=ppa.payroll_action_id and ppa.action_type in ('U','P') ) prepay_rel_Action_id, 'PREL' AS ACTION_CODE, 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.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=pay_obj_act.payroll_rel_ACTION_ID ),-1) ) AS LockingStatus , NVL(pt.task_name,actiontypelookup.meaning) AS task_name, NVL(pt.base_task_name,actiontypelookup.meaning) AS base_task_name, (select pname.LIST_NAME LIST_NAME from per_person_names_f pname where pname.person_id = prl.person_id and pname.name_type= 'GLOBAL' and pname.effective_start_Date = (Select max(effective_start_Date) from per_person_names_f ppnf where ppnf.person_id = prl.person_id and ppnf.name_type= 'GLOBAL')) object_name, ppp.payroll_name object_reference, prl.person_id, (select peo.person_number PERSON_NUMBER from per_all_people_f peo where peo.person_id = prl.person_id and peo.effective_start_Date = (Select max(effective_start_Date) from per_all_people_f papf where papf.person_id = prl.person_id)) person_number, prl.payroll_relationship_number relationship_number, ppp.payroll_name payroll, ppp.PAYROLL_ID, pcs.CONSOLIDATION_SET_NAME, 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, null Relationship_type, null keywords, ldg.legislative_data_group_id legislative_data_group_id, ldg.name legislative_data_group_name, null assignment_status, prq.flow_task_instance_id task_instance_id, RunTypeDPEO.RUN_TYPE_NAME, pfi.instance_name as flow_name, trunc(pfi.creation_date) submission_date, null as Accounting_Start_Date, null as Accounting_End_Date, NULL AS LOCATION_NAME, NULL AS Job_NAME , ACT_FLAG.ACT_FLAG_ACTION_TYPE, payrollactioneo.pay_request_id, TimePeriodPEOEarn.START_DATE Earn_start_date, TimePeriodPEOEarn.END_DATE Earn_end_date, pt.def_category_type, pt.def_sub_category_type, pt.task_id, decode(pt.task_id,null,'Y',nvl((select 'Y' from pay_task_actions ppta where ppta.base_task_id=pt.task_id and EXECUTION_MODE='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 EXECUTION_MODE='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_payroll_rel_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, (SELECT LOOKUP_CODE,'1' ACT_FLAG_ACTION_TYPE FROM HCM_LOOKUPS ACT WHERE LOOKUP_CODE IN('A','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','CTG','TV','TP') AND LOOKUP_TYPE='ACTION_TYPE') ACT_FLAG WHERE payrollactioneo.action_type=ACT_FLAG.LOOKUP_CODE(+) 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 payrollactioneo.dedn_time_period_id = paytimeperiodseo.time_period_id (+) AND payrollactioneo.earn_time_period_id =TimePeriodPEOEarn.TIME_PERIOD_ID (+) AND payrollactioneo.RUN_TYPE_ID = RunTypeDPEO.RUN_TYPE_ID (+) AND pay_obj_act.source_action_id IS NULL AND pay_obj_act.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 )) )))) and prl.payroll_relationship_id = pay_obj_act.payroll_relationship_id and payrollactioneo.payroll_id=ppp.payroll_id (+) and pcs.CONSOLIDATION_SET_ID(+)=payrollactioneo.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 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)