PAY_PROC_ACT_RESULTS

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 TASK_NAME OBJECT_REFERENCE PERSON_ID PERSON_NUMBER RELATIONSHIP_NUMBER PAYROLL 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 RUN_TYPE_NAME ROLLBACK_ALLOWED_FLAG REVERSAL_ALLOWED_FLAG MARKFORRETRY_ALLOWED_FLAG SOE_DISPLAY_FLAG RUN_RESULTS_DISPLAY_FLAG BAL_RESULTS_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 EARN_START_DATE EARN_END_DATE

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

TASK_NAME

OBJECT_REFERENCE

PERSON_ID

PERSON_NUMBER

RELATIONSHIP_NUMBER

PAYROLL

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

RUN_TYPE_NAME

ROLLBACK_ALLOWED_FLAG

REVERSAL_ALLOWED_FLAG

MARKFORRETRY_ALLOWED_FLAG

SOE_DISPLAY_FLAG

RUN_RESULTS_DISPLAY_FLAG

BAL_RESULTS_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

EARN_START_DATE

EARN_END_DATE

Query

SQL_Statement
SELECT zzz.OBJECT_TYPE, zzz.OBJECT_ID , decode(zzz.object_name,'###', decode(replace(replace(replace(replace(replace(replace(zzz.object_type,'mycsp_mt_id','PER'),'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),zzz.object_type\|\|'-'\|\|zzz.object_action_id) from pay_pay_relationships_dn prl, PAY_PAYROLL_REL_ACTIONS pra where pra.payroll_rel_action_id=zzz.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),zzz.object_type\|\|'-'\|\|zzz.object_action_id) from pay_pay_relationships_dn prl, PAY_PAYROLL_REL_ACTIONS pra, pay_action_contexts ppp where ppp.action_context_id = zzz.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),zzz.object_type\|\|'-'\|\|zzz.object_action_id) from per_all_assignments_f prl where prl.assignment_id=substrb(zzz.assignment_id,1,instrb(zzz.assignment_id,'[#]')-1)), 'PER','Person Number='\|\|(select nvl(max(ppp.Person_number),zzz.object_type\|\|'-'\|\|zzz.object_action_id) from fusion.per_all_people_f ppp where ppp.person_id = zzz.object_id), zzz.object_type\|\|'='\|\|zzz.object_action_id), zzz.object_name) 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.TASK_NAME, zzz.object_reference, zzz.person_id, zzz.person_number, zzz.relationship_number, zzz.payroll, 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.RUN_TYPE_NAME, DECODE(ACT_FLAG_ACTION_TYPE,'1',DECODE(LOCKINGFLAG,'-1',DECODE(ACTION_TYPE_CODE,'D',decode(( select max(1) from pay_payroll_rel_actions paac2, pay_payroll_rel_actions paac, pay_action_interlocks pai where pai.locking_action_id =OBJECT_ACTION_ID and pai.locked_action_id = paac.payroll_rel_action_id and paac.pre_payment_id = paac2.pre_payment_id and paac2.action_sequence > paac.action_sequence),null,'Y','N'),'Y'),'N'),'N' ) 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, DECODE(zzz.ACT_FLAG_ACTION_TYPE,'1',DECODE(zzz.ACTION_STATUS_CODE,'C',DECODE(zzz.LOCKINGFLAG,'-1',DECODE(zzz.ACTION_TYPE_CODE,'D',decode(( select max(1) from pay_payroll_rel_actions paac2, pay_payroll_rel_actions paac, pay_action_interlocks pai where pai.locking_action_id =zzz.OBJECT_ACTION_ID and pai.locked_action_id = paac.payroll_rel_action_id and paac.pre_payment_id = paac2.pre_payment_id and paac2.action_sequence > paac.action_sequence),null,'Y','N'),'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,'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','L','Y','N')) as RUN_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,'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','CTG','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','CTG','Y','N')) AS PAYMT_RESULTS_DISPLAY_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,'R','Statement of Earnings','Q','Statement of Earnings','V','Statement of Earnings','CTG','Statement of Earnings','L','Run Results','P','Prepayment Results','U','Prepayment Results','PRU','Prepayment Results','H','Payment Results','M','Payment Results','E','Payment Results','PP','Payment Results','D','Payment Results','C','Costing Results','CA','Costing Results','EC','Costing Results','CP','Costing of payment','B','Balances Results','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) ,/* PERFORMANCE FIX - Bug 27934238 : Moved logic to ObjectActionVO 'Gross_Pay', (select pay_balance_view_pkg.get_net_pay_bal_agg(object_action_id) from dual) ,/ 'CTPY',(select '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) ,'CTRS',(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, Earn_start_date, Earn_end_date FROM ( SELECT distinct pay_obj_act.OBJECT_TYPE, decode(pay_obj_act.OBJECT_TYPE,'PERSON_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 as OBJECT_ACTION_ID , pay_obj_act.TEMP_OBJECT_ACTION_ID AS child_object_Action_id, null prepay_rel_Action_id, decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID','PREL','PAYROLL_RELATIONSHIP_ID','PREL','HR_ASSIGNMENT_ID','PREL','PAY_ASSIGNMENT_ID','PREL','PTOA') 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.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, decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT PNAME.LIST_NAME LIST_NAME FROM PER_PERSON_NAMES_F PNAME WHERE PNAME.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID AND PNAME.NAME_TYPE= 'GLOBAL' AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF WHERE PPNF.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID AND PPNF.NAME_TYPE= 'GLOBAL')), 'PAYROLL_RELATIONSHIP_ID',(SELECT PNAME.LIST_NAME LIST_NAME FROM PER_PERSON_NAMES_F PNAME, PAY_PAY_RELATIONSHIPS_DN REL WHERE REL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID AND PNAME.PERSON_ID = REL.PERSON_ID AND PNAME.NAME_TYPE= 'GLOBAL' AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PAY_PAY_RELATIONSHIPS_DN RELL WHERE RELL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID AND PPNF.PERSON_ID = RELL.PERSON_ID AND PPNF.NAME_TYPE= 'GLOBAL')), 'HR_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME FROM PER_PERSON_NAMES_F PNAME, PER_ALL_ASSIGNMENTS_F ASG WHERE ASG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID AND ASG.PERSON_ID=PNAME.PERSON_ID AND PNAME.NAME_TYPE= 'GLOBAL' AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PER_ALL_ASSIGNMENTS_F ASGG WHERE ASGG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID AND ASGG.PERSON_ID=PPNF.PERSON_ID AND PPNF.NAME_TYPE= 'GLOBAL')), 'PAY_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME FROM PER_PERSON_NAMES_F PNAME, 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' AND PNAME.PERSON_ID = REL.PERSON_ID AND PNAME.NAME_TYPE= 'GLOBAL' AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PAY_PAY_RELATIONSHIPS_DN RELL,PAY_REL_GROUPS_DN RGG WHERE RGG.RELATIONSHIP_GROUP_ID = PAY_OBJ_ACT.OBJECT_ID AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID AND RGG.GROUP_TYPE='A' AND PPNF.PERSON_ID = RELL.PERSON_ID AND PPNF.NAME_TYPE= 'GLOBAL')) , 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 min(report_record_id) FROM pay_action_information where action_context_id = pay_obj_act.temp_object_action_id and action_context_type = 'PTOA' 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 OBJECT_NAME , null object_reference, null person_id, decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT peo.person_number PERSON_NUMBER FROM per_all_people_f peo WHERE peo.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf WHERE papf.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID)), 'PAYROLL_RELATIONSHIP_ID',(SELECT peo.person_number PERSON_NUMBER FROM per_all_people_f peo, PAY_PAY_RELATIONSHIPS_DN REL WHERE REL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID AND peo.PERSON_ID = REL.PERSON_ID AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf , PAY_PAY_RELATIONSHIPS_DN RELL WHERE RELL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID AND papf.PERSON_ID = RELL.PERSON_ID)), 'HR_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER FROM per_all_people_f peo, PER_ALL_ASSIGNMENTS_F ASG WHERE ASG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID AND ASG.PERSON_ID=peo.PERSON_ID AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf, PER_ALL_ASSIGNMENTS_F ASGG WHERE ASGG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID AND ASGG.PERSON_ID=papf.PERSON_ID)), 'PAY_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER FROM per_all_people_f peo, 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' AND peo.PERSON_ID = REL.PERSON_ID AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf, PAY_PAY_RELATIONSHIPS_DN RELL,PAY_REL_GROUPS_DN RGG WHERE RGG.RELATIONSHIP_GROUP_ID = PAY_OBJ_ACT.OBJECT_ID AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID AND RGG.GROUP_TYPE='A' AND papf.PERSON_ID = RELL.PERSON_ID)), NULL) AS PERSON_NUMBER , null relationship_number, null payroll, null payroll_period_name, null payroll_period_number, null date_earned, NULL 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, null as RUN_TYPE_NAME, pfi.instance_name as flow_name, 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, null Earn_start_date, null Earn_end_date 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 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 = '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 distinct decode(pay_obj_act.OBJECT_TYPE,'mycsp_mt_id','PERSON_ID') OBJECT_TYPE, decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT MAX(REL.PAYROLL_RELATIONSHIP_ID) FROM PAY_PAY_RELATIONSHIPS_DN REL WHERE REL.PERSON_ID=PAY_OBJ_ACT.OBJECT_ID), '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.OBJECT_ACTION_ID, pay_obj_act.OBJECT_ACTION_ID AS child_object_Action_id, null prepay_rel_Action_id, decode(pay_obj_act.OBJECT_TYPE,'mycsp_mt_id','PREL','PERSON_ID','PREL','PAYROLL_RELATIONSHIP_ID','PREL','HR_ASSIGNMENT_ID','PREL','PAY_ASSIGNMENT_ID','PREL','POA') 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.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, decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT PNAME.LIST_NAME LIST_NAME FROM PER_PERSON_NAMES_F PNAME WHERE PNAME.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID AND PNAME.NAME_TYPE= 'GLOBAL' AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF WHERE PPNF.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID AND PPNF.NAME_TYPE= 'GLOBAL')), 'mycsp_mt_id',(SELECT PNAME.LIST_NAME LIST_NAME FROM PER_PERSON_NAMES_F PNAME WHERE PNAME.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID AND PNAME.NAME_TYPE= 'GLOBAL' AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF WHERE PPNF.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID AND PPNF.NAME_TYPE= 'GLOBAL')), 'PAYROLL_RELATIONSHIP_ID',(SELECT PNAME.LIST_NAME LIST_NAME FROM PER_PERSON_NAMES_F PNAME, PAY_PAY_RELATIONSHIPS_DN REL WHERE REL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID AND PNAME.PERSON_ID = REL.PERSON_ID AND PNAME.NAME_TYPE= 'GLOBAL' AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PAY_PAY_RELATIONSHIPS_DN RELL WHERE RELL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID AND PPNF.PERSON_ID = RELL.PERSON_ID AND PPNF.NAME_TYPE= 'GLOBAL')), 'HR_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME FROM PER_PERSON_NAMES_F PNAME, PER_ALL_ASSIGNMENTS_F ASG WHERE ASG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID AND ASG.PERSON_ID=PNAME.PERSON_ID AND PNAME.NAME_TYPE= 'GLOBAL' AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PER_ALL_ASSIGNMENTS_F ASGG WHERE ASGG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID AND ASGG.PERSON_ID=PPNF.PERSON_ID AND PPNF.NAME_TYPE= 'GLOBAL')), 'PAY_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME FROM PER_PERSON_NAMES_F PNAME, 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' AND PNAME.PERSON_ID = REL.PERSON_ID AND PNAME.NAME_TYPE= 'GLOBAL' AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PAY_PAY_RELATIONSHIPS_DN RELL,PAY_REL_GROUPS_DN RGG WHERE RGG.RELATIONSHIP_GROUP_ID = PAY_OBJ_ACT.OBJECT_ID AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID AND RGG.GROUP_TYPE='A' AND PPNF.PERSON_ID = RELL.PERSON_ID AND PPNF.NAME_TYPE= 'GLOBAL')) , 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 min(report_record_id) FROM pay_action_information where action_context_id = pay_obj_act.object_action_id and action_context_type = 'POA' 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 OBJECT_NAME , null object_reference, null person_id, decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT peo.person_number PERSON_NUMBER FROM per_all_people_f peo WHERE peo.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf WHERE papf.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID)), 'mycsp_mt_id',(SELECT peo.person_number PERSON_NUMBER FROM per_all_people_f peo WHERE peo.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf WHERE papf.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID)), 'PAYROLL_RELATIONSHIP_ID',(SELECT peo.person_number PERSON_NUMBER FROM per_all_people_f peo, PAY_PAY_RELATIONSHIPS_DN REL WHERE REL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID AND peo.PERSON_ID = REL.PERSON_ID AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf , PAY_PAY_RELATIONSHIPS_DN RELL WHERE RELL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID AND papf.PERSON_ID = RELL.PERSON_ID)), 'HR_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER FROM per_all_people_f peo, PER_ALL_ASSIGNMENTS_F ASG WHERE ASG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID AND ASG.PERSON_ID=peo.PERSON_ID AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf, PER_ALL_ASSIGNMENTS_F ASGG WHERE ASGG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID AND ASGG.PERSON_ID=papf.PERSON_ID)), 'PAY_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER FROM per_all_people_f peo, 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' AND peo.PERSON_ID = REL.PERSON_ID AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf, PAY_PAY_RELATIONSHIPS_DN RELL,PAY_REL_GROUPS_DN RGG WHERE RGG.RELATIONSHIP_GROUP_ID = PAY_OBJ_ACT.OBJECT_ID AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID AND RGG.GROUP_TYPE='A' AND papf.PERSON_ID = RELL.PERSON_ID)), NULL) AS PERSON_NUMBER , null relationship_number, null payroll, null payroll_period_name, null payroll_period_number, null date_earned, NULL 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, null as RUN_TYPE_NAME, pfi.instance_name as flow_name, 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, null Earn_start_date, null Earn_end_date 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 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 = '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 distinct 'PRA' OBJECT_TYPE, prl.payroll_relationship_id as OBJECT_ID, pay_obj_act.payroll_rel_ACTION_ID OBJECT_ACTION_ID, NVL( decode(actiontypelookup.lookup_code,'L', (SELECT MAX(ch.payroll_rel_action_id) FROM pay_payroll_rel_actions ch WHERE ch.source_id = pay_obj_act.payroll_rel_action_id AND ch.RETRO_COMPONENT_ID IS NOT NULL AND ch.payroll_relationship_id = pay_obj_act.payroll_relationship_id ) ,(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 = pay_obj_act.payroll_rel_action_id AND ch.RETRO_COMPONENT_ID IS NULL )) ,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, (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, 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, null as Accounting_Start_Date, null as Accounting_End_Date, / PERFORMANCE FIX : Bug 27934238 (select max(LocationDPEO.LOCATION_NAME) FROM HR_LOCATIONS_ALL_F_VL LocationDPEO, PER_ALL_ASSIGNMENTS_f AssignmentDPEO,pay_rel_groups_dn asg WHERE AssignmentDPEO.location_id=LocationDPEO.location_id(+) AND AssignmentDPEO.ASSIGNMENT_ID=asg.ASSIGNMENT_ID AND asg.payroll_relationship_id= pay_obj_act.payroll_relationship_id AND payrollactioneo.effective_date BETWEEN LocationDPEO.EFFECTIVE_START_DATE(+) AND LocationDPEO.EFFECTIVE_END_DATE(+)) / NULL AS LOCATION_NAME, / PERFORMANCE FIX : Bug 27934238 (select max(JobDPEO.NAME) FROM PER_JOBS_F_VL JobDPEO, PER_ALL_ASSIGNMENTS_f AssignmentDPEO,pay_rel_groups_dn asg WHERE AssignmentDPEO.job_id=JobDPEO.job_id(+) AND AssignmentDPEO.ASSIGNMENT_ID=asg.ASSIGNMENT_ID AND asg.payroll_relationship_id= pay_obj_act.payroll_relationship_id AND payrollactioneo.effective_date BETWEEN JobDPEO.EFFECTIVE_START_DATE(+) AND JobDPEO.EFFECTIVE_END_DATE(+)) / 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 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_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('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 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 = 'PAY_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 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 (+) ) union all SELECT distinct null OBJECT_TYPE, / PERFORMANCE FIX - Bug 27934238 to_char((select distinct max(pblv.action_parameter_value) from pay_batch_line_values pblv, pay_task_parameters_vl ddd where pblv.batch_line_id = pbl.batch_line_id and pblv.action_parameter_id = ddd.task_parameter_id and pblv.action_parameter_value is not null and ddd.display_flag='N' and ddd.element_name='PAYROLL_RELATIONSHIP_ID')) / pbl.batch_line_id as OBJECT_ID, / Bug 27934238 : Added dummy column to drive the sql through object_id value passed in where clause / pbl.batch_line_id OBJECT_ACTION_ID, pbl.batch_line_id AS child_object_Action_id, null prepay_rel_Action_id, 'BATCH' AS ACTION_CODE, actionstatuslookup.meaning status, actionstatuslookup.lookup_code action_status_code, pblta.display_task_action_name\|\| (select max(':'\|\|pblv.action_parameter_value) from pay_batch_line_values pblv where pblv.batch_line_id = pbl.batch_line_id and pblv.action_parameter_id = pbltpp.task_parameter_id) action_type, null action_type_code, pbh.batch_id AS payroll_action_id, null AS process_date, null AS LockingFlag, null AS LockingStatus , pblt.display_task_name\|\|'-'\|\|pt.task_name AS task_name, (select distinct listagg( ddd.parameter_name\|\|'='\|\|pblv.action_parameter_value,'\|') within group (order by ddd.param_sequence) from pay_batch_line_values pblv, pay_task_parameters_vl ddd where pblv.batch_line_id = pbl.batch_line_id and pblv.action_parameter_id = ddd.task_parameter_id and ddd.display_flag != 'N' and pblv.action_parameter_value is not null) object_name, null object_reference, null person_id, null person_number, null relationship_number, null payroll, null payroll_period_name, null payroll_period_number, null date_earned, NULL 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, pft.flow_task_instance_id task_instance_id, null as RUN_TYPE_NAME, pfi.instance_name as flow_name, null as Accounting_Start_Date, null as Accounting_End_Date, null as LOCATION_NAME, null as JOB_NAME, NULL ACT_FLAG_ACTION_TYPE, null pay_request_id, null Earn_start_date, null Earn_end_date FROM pay_batch_headers pbh, pay_batch_lines pbl, pay_flow_task_instances pft, pay_flow_tasks_vl ft, PAY_FLOW_TASK_PARAM_VALS ppp, pay_flow_task_parameters pftp, pay_task_parameters ptp, hcm_lookups actionstatuslookup, pay_tasks_vl pt, FUSION.pay_bl_task_actions_vl pblta, pay_bl_tasks_vl pblt, pay_flow_instances pfi, pay_task_properties pbltp, pay_task_parameters pbltpp, per_legislative_data_groups_vl ldg WHERE pfi.flow_instance_id=pft.flow_instance_id AND ppp.flow_task_instance_id = pft.flow_task_instance_id and pbh.legislative_data_group_id= ldg.legislative_data_group_id and pftp.base_flow_task_param_id=ppp.base_flow_task_param_id and pt.base_task_id = ft.base_task_id and pftp.base_task_parameter_id = ptp.base_task_parameter_id and ptp.BASE_TASK_PARAMETER_NAME like '%BATCH%' / PERFORMANCE FIX - Bug 27934238 and ppp.flow_task_param_value = to_char(pbh.batch_id) / and DECODE(length(TRIM(translate(ppp.flow_task_param_value,'0123456789',' ') ) ),NULL,to_number(ppp.flow_task_param_value),NULL) = pbh.batch_id AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID and pbl.batch_id=pbh.batch_id and decode(pbl.batch_line_status,'T','C',pbl.batch_line_status) = actionstatuslookup.lookup_code AND actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS' and pblta.task_action_id = pbl.task_action_id and pblta.task_id = pblt.task_id and pbltp.base_task_action_id (+) = pblta.task_action_id and pbltp.TASK_PROPERTY_TYPE (+) = 'BL_KEY_PARAMETER' and pbltpp.base_task_action_id (+) = pbltp.base_task_action_id and pbltpp.element_name (+) = pbltp.task_property_value and pt.task_type != 'MANUAL_TASK' / Bal Init / union all SELECT distinct null OBJECT_TYPE, pbl.PAYROLL_RELATIONSHIP_ID as OBJECT_ID, pbl.batch_line_id OBJECT_ACTION_ID, pbl.batch_line_id AS child_object_Action_id, null prepay_rel_Action_id, 'BATCH' AS ACTION_CODE, nvl(actionstatuslookup.meaning,BLstatuslookup.meaning) status, nvl(actionstatuslookup.lookup_code,BLstatuslookup.lookup_code) action_status_code, pt.task_name action_type, null action_type_code, pbh.batch_id AS payroll_action_id, null AS process_date, null AS LockingFlag, null AS LockingStatus , pt.task_name AS task_name, 'Line Sequence='\|\|pbl.LINE_SEQUENCE\|\|'\|'\|\| 'Payroll Relationship Number='\|\|pbl.payroll_relationship_number\|\|'\|'\|\| 'Assignment Number='\|\|pbl.assignment_number\|\|'\|'\|\| 'Balance Name='\|\|pbl.BALANCE_NAME\|\|'\|'\|\| 'Balance Dimension='\|\|pbl.DIMENSION_NAME\|\|'\|'\|\| 'Upload Date='\|\|pbl.UPLOAD_DATE\|\|'\|'\|\| 'Value='\|\|pbl.VALUE object_name, null object_reference, null person_id, null person_number, null relationship_number, null payroll, null payroll_period_name, null payroll_period_number, null date_earned, NULL 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, pft.flow_task_instance_id task_instance_id, null as RUN_TYPE_NAME, pfi.instance_name as flow_name, null as Accounting_Start_Date, null as Accounting_End_Date, null as LOCATION_NAME, null as JOB_NAME, NULL ACT_FLAG_ACTION_TYPE, null pay_request_id, null Earn_start_date, null Earn_end_date FROM PAY_BAL_BATCH_HEADERS pbh, PAY_BAL_BATCH_LINES pbl, pay_payroll_rel_actions pra, pay_flow_task_instances pft, pay_flow_tasks_vl ft, PAY_FLOW_TASK_PARAM_VALS ppp, pay_flow_task_parameters pftp, pay_task_parameters ptp, hcm_lookups actionstatuslookup, hcm_lookups BLstatuslookup, pay_flow_instances pfi, per_legislative_data_groups_vl ldg, pay_tasks_vl pt WHERE pfi.flow_instance_id=pft.flow_instance_id AND ppp.flow_task_instance_id = pft.flow_task_instance_id and pbh.legislative_data_group_id= ldg.legislative_data_group_id and pftp.base_flow_task_param_id=ppp.base_flow_task_param_id and pt.base_task_id = ft.base_task_id and pftp.base_task_parameter_id = ptp.base_task_parameter_id and ptp.BASE_TASK_PARAMETER_NAME like '%BATCH%' and pt.base_task_name='BALANCE_INITIALIZATION' and ppp.flow_task_param_value = to_char(pbh.batch_id) AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID and pbl.batch_id=pbh.batch_id and pra.payroll_rel_action_id(+) = pbl.payroll_rel_action_id AND BLstatuslookup.lookup_type = 'PAY_BATCH_STATUS' AND BLstatuslookup.lookup_code = pbl.batch_line_status AND actionstatuslookup.lookup_type(+) = 'PAY_ACTION_STATUS' AND actionstatuslookup.lookup_code(+) = pra.action_status /union SELECT distinct null OBJECT_TYPE, to_char(rownum) as OBJECT_ID, pblr.row_id OBJECT_ACTION_ID, pblr.row_id child_object_Action_id, null prepay_rel_Action_id, 'BATCH' AS ACTION_CODE, actionstatuslookup.meaning status, actionstatuslookup.lookup_code action_status_code, pblr.LINE_OPERATION action_type, null action_type_code, pbh.data_set_id AS payroll_action_id, null AS process_date, null AS LockingFlag, null AS LockingStatus , dbo.bus_obj_file_discriminator AS task_name, 'Business Object='\|\|dbo.bus_obj_file_discriminator\|\|'\|Operation='\|\|pblr.LINE_OPERATION\|\|'\|'\|\|pay_flow_common_util_pkg.pay_hdl_function(pblr.row_id,pblr.VO_MAPPING_ID) object_name, null object_reference, null person_id, null person_number, null relationship_number, null payroll, null payroll_period_name, null payroll_period_number, null date_earned, null assignment_number, NULL ASSIGNMENT_ID, null Relationship_type, null keywords, null legislative_data_group_id, null legislative_data_group_name, null assignment_status, aaa.flow_task_instance_id task_instance_id, null as RUN_TYPE_NAME, pfi.instance_name flow_name, null as Accounting_Start_Date, null as Accounting_End_Date, null as LOCATION_NAME, null as JOB_NAME, NULL ACT_FLAG_ACTION_TYPE, null pay_request_id, null Earn_start_date, null Earn_end_date FROM pay_requests aaa, REQUEST_HISTORY essa, pay_flow_instances pfi, HRC_DL_DATA_SETS pbh, hrc_dl_data_set_bus_objs pbho, hrc_dl_file_headers pbhh, hrc_dl_file_rows pblr, HRC_DL_BUSINESS_OBJECTS dbo, hcm_lookups actionstatuslookup WHERE aaa.flow_instance_id = pfi.flow_instance_id and aaa.call_id = essa.parentrequestid and aaa.call_type like '%ESS%' and essa.requestid = pbh.request_id and pbho.data_set_id = pbh.data_set_id and pbho.DATA_SET_BUS_OBJ_ID = pbhh.DATA_SET_BUS_OBJ_ID and pbhh.header_id = pblr.header_id and dbo.business_object_id = pbhh.business_object_id and actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS' and decode(pblr.transfer_status,null,'C','UNPROCESSED','U','NOT_READY','U','ERROR','E','SUCCESS','C',decode(instrb(pblr.transfer_status,'ERROR'),0,'C','E')) = actionstatuslookup.lookup_code */ ) 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)

SQL_Statement

SELECT

zzz.OBJECT_TYPE,

zzz.OBJECT_ID ,

decode(zzz.object_name,'###',

decode(replace(replace(replace(replace(replace(replace(zzz.object_type,'mycsp_mt_id','PER'),'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),zzz.object_type||'-'||zzz.object_action_id)

from pay_pay_relationships_dn prl,

PAY_PAYROLL_REL_ACTIONS pra

where pra.payroll_rel_action_id=zzz.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),zzz.object_type||'-'||zzz.object_action_id)

from pay_pay_relationships_dn prl,

PAY_PAYROLL_REL_ACTIONS pra,

pay_action_contexts ppp

where ppp.action_context_id = zzz.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),zzz.object_type||'-'||zzz.object_action_id)

from per_all_assignments_f prl

where prl.assignment_id=substrb(zzz.assignment_id,1,instrb(zzz.assignment_id,'[#]')-1)),

'PER','Person Number='||(select nvl(max(ppp.Person_number),zzz.object_type||'-'||zzz.object_action_id)

from fusion.per_all_people_f ppp

where ppp.person_id = zzz.object_id),

zzz.object_type||'='||zzz.object_action_id),

zzz.object_name) 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.TASK_NAME,

zzz.object_reference,

zzz.person_id,

zzz.person_number,

zzz.relationship_number,

zzz.payroll,

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

DECODE(ACT_FLAG_ACTION_TYPE,'1',DECODE(LOCKINGFLAG,'-1',DECODE(ACTION_TYPE_CODE,'D',decode(( select max(1)

from pay_payroll_rel_actions paac2, pay_payroll_rel_actions paac, pay_action_interlocks pai

where pai.locking_action_id =OBJECT_ACTION_ID and pai.locked_action_id = paac.payroll_rel_action_id

and paac.pre_payment_id = paac2.pre_payment_id

and paac2.action_sequence >

paac.action_sequence),null,'Y','N'),'Y'),'N'),'N' ) 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,

DECODE(zzz.ACT_FLAG_ACTION_TYPE,'1',DECODE(zzz.ACTION_STATUS_CODE,'C',DECODE(zzz.LOCKINGFLAG,'-1',DECODE(zzz.ACTION_TYPE_CODE,'D',decode((

select max(1)

from

pay_payroll_rel_actions paac2,

pay_payroll_rel_actions paac,

pay_action_interlocks pai

where pai.locking_action_id =zzz.OBJECT_ACTION_ID and pai.locked_action_id = paac.payroll_rel_action_id

and paac.pre_payment_id = paac2.pre_payment_id

and paac2.action_sequence >

paac.action_sequence),null,'Y','N'),'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,'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','L','Y','N')) as RUN_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,'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','CTG','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','CTG','Y','N')) AS PAYMT_RESULTS_DISPLAY_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,'R','Statement of Earnings','Q','Statement of Earnings','V','Statement of Earnings','CTG','Statement of Earnings','L','Run Results','P','Prepayment Results','U','Prepayment Results','PRU','Prepayment Results','H','Payment Results','M','Payment Results','E','Payment Results','PP','Payment Results','D','Payment Results','C','Costing Results','CA','Costing Results','EC','Costing Results','CP','Costing of payment','B','Balances Results','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)

,/* PERFORMANCE FIX - Bug 27934238 : Moved logic to ObjectActionVO

'Gross_Pay', (select pay_balance_view_pkg.get_net_pay_bal_agg(object_action_id) from dual)

,*/

'CTPY',(select '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)

,'CTRS',(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,

Earn_start_date,

Earn_end_date

FROM

(

SELECT distinct pay_obj_act.OBJECT_TYPE,

decode(pay_obj_act.OBJECT_TYPE,'PERSON_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 as OBJECT_ACTION_ID ,

pay_obj_act.TEMP_OBJECT_ACTION_ID AS child_object_Action_id,

null prepay_rel_Action_id,

decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID','PREL','PAYROLL_RELATIONSHIP_ID','PREL','HR_ASSIGNMENT_ID','PREL','PAY_ASSIGNMENT_ID','PREL','PTOA') 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.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,

decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME

WHERE PNAME.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF WHERE PPNF.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),

'PAYROLL_RELATIONSHIP_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME, PAY_PAY_RELATIONSHIPS_DN REL

WHERE REL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID

AND PNAME.PERSON_ID = REL.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PAY_PAY_RELATIONSHIPS_DN RELL WHERE RELL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID

AND PPNF.PERSON_ID = RELL.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),

'HR_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME, PER_ALL_ASSIGNMENTS_F ASG

WHERE ASG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID

AND ASG.PERSON_ID=PNAME.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PER_ALL_ASSIGNMENTS_F ASGG

WHERE ASGG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID

AND ASGG.PERSON_ID=PPNF.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),

'PAY_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME, 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'

AND PNAME.PERSON_ID = REL.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PAY_PAY_RELATIONSHIPS_DN RELL,PAY_REL_GROUPS_DN RGG

WHERE RGG.RELATIONSHIP_GROUP_ID = PAY_OBJ_ACT.OBJECT_ID

AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID

AND RGG.GROUP_TYPE='A'

AND PPNF.PERSON_ID = RELL.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL'))

, 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 min(report_record_id)

FROM pay_action_information

where action_context_id = pay_obj_act.temp_object_action_id

and action_context_type = 'PTOA'

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 OBJECT_NAME ,

null object_reference,

null person_id,

decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo

WHERE peo.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID

AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf WHERE papf.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID)),

'PAYROLL_RELATIONSHIP_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo, PAY_PAY_RELATIONSHIPS_DN REL

WHERE REL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID

AND peo.PERSON_ID = REL.PERSON_ID

AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf , PAY_PAY_RELATIONSHIPS_DN RELL

WHERE RELL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID

AND papf.PERSON_ID = RELL.PERSON_ID)),

'HR_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo, PER_ALL_ASSIGNMENTS_F ASG

WHERE ASG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID

AND ASG.PERSON_ID=peo.PERSON_ID

AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf, PER_ALL_ASSIGNMENTS_F ASGG

WHERE ASGG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID

AND ASGG.PERSON_ID=papf.PERSON_ID)),

'PAY_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo, 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'

AND peo.PERSON_ID = REL.PERSON_ID

AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf, PAY_PAY_RELATIONSHIPS_DN RELL,PAY_REL_GROUPS_DN RGG

WHERE RGG.RELATIONSHIP_GROUP_ID = PAY_OBJ_ACT.OBJECT_ID

AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID

AND RGG.GROUP_TYPE='A'

AND papf.PERSON_ID = RELL.PERSON_ID)), NULL) AS PERSON_NUMBER ,

null relationship_number,

null payroll,

null payroll_period_name,

null payroll_period_number,

null date_earned,

NULL 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,

null as RUN_TYPE_NAME,

pfi.instance_name as flow_name,

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,

null Earn_start_date,

null Earn_end_date

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 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 = '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 distinct decode(pay_obj_act.OBJECT_TYPE,'mycsp_mt_id','PERSON_ID') OBJECT_TYPE,

decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT MAX(REL.PAYROLL_RELATIONSHIP_ID) FROM PAY_PAY_RELATIONSHIPS_DN REL WHERE REL.PERSON_ID=PAY_OBJ_ACT.OBJECT_ID),

'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.OBJECT_ACTION_ID,

pay_obj_act.OBJECT_ACTION_ID AS child_object_Action_id,

null prepay_rel_Action_id,

decode(pay_obj_act.OBJECT_TYPE,'mycsp_mt_id','PREL','PERSON_ID','PREL','PAYROLL_RELATIONSHIP_ID','PREL','HR_ASSIGNMENT_ID','PREL','PAY_ASSIGNMENT_ID','PREL','POA') 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.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,

decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME

WHERE PNAME.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF WHERE PPNF.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),

'mycsp_mt_id',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME

WHERE PNAME.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF WHERE PPNF.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),

'PAYROLL_RELATIONSHIP_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME, PAY_PAY_RELATIONSHIPS_DN REL

WHERE REL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID

AND PNAME.PERSON_ID = REL.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PAY_PAY_RELATIONSHIPS_DN RELL WHERE RELL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID

AND PPNF.PERSON_ID = RELL.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),

'HR_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME, PER_ALL_ASSIGNMENTS_F ASG

WHERE ASG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID

AND ASG.PERSON_ID=PNAME.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PER_ALL_ASSIGNMENTS_F ASGG

WHERE ASGG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID

AND ASGG.PERSON_ID=PPNF.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),

'PAY_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME, 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'

AND PNAME.PERSON_ID = REL.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

AND PNAME.EFFECTIVE_START_DATE = (SELECT MAX(PPNF.EFFECTIVE_START_DATE) FROM PER_PERSON_NAMES_F PPNF, PAY_PAY_RELATIONSHIPS_DN RELL,PAY_REL_GROUPS_DN RGG

WHERE RGG.RELATIONSHIP_GROUP_ID = PAY_OBJ_ACT.OBJECT_ID

AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID

AND RGG.GROUP_TYPE='A'

AND PPNF.PERSON_ID = RELL.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL'))

, 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 min(report_record_id)

FROM pay_action_information

where action_context_id = pay_obj_act.object_action_id

and action_context_type = 'POA'

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 OBJECT_NAME ,

null object_reference,

null person_id,

decode(pay_obj_act.OBJECT_TYPE,'PERSON_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo

WHERE peo.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID

AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf WHERE papf.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID)),

'mycsp_mt_id',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo

WHERE peo.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID

AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf WHERE papf.PERSON_ID = PAY_OBJ_ACT.OBJECT_ID)),

'PAYROLL_RELATIONSHIP_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo, PAY_PAY_RELATIONSHIPS_DN REL

WHERE REL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID

AND peo.PERSON_ID = REL.PERSON_ID

AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf , PAY_PAY_RELATIONSHIPS_DN RELL

WHERE RELL.PAYROLL_RELATIONSHIP_ID = PAY_OBJ_ACT.OBJECT_ID

AND papf.PERSON_ID = RELL.PERSON_ID)),

'HR_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo, PER_ALL_ASSIGNMENTS_F ASG

WHERE ASG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID

AND ASG.PERSON_ID=peo.PERSON_ID

AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf, PER_ALL_ASSIGNMENTS_F ASGG

WHERE ASGG.ASSIGNMENT_ID = PAY_OBJ_ACT.OBJECT_ID

AND ASGG.PERSON_ID=papf.PERSON_ID)),

'PAY_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo, 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'

AND peo.PERSON_ID = REL.PERSON_ID

AND peo.EFFECTIVE_START_DATE = (SELECT MAX(papf.EFFECTIVE_START_DATE) FROM per_all_people_f papf, PAY_PAY_RELATIONSHIPS_DN RELL,PAY_REL_GROUPS_DN RGG

WHERE RGG.RELATIONSHIP_GROUP_ID = PAY_OBJ_ACT.OBJECT_ID

AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID

AND RGG.GROUP_TYPE='A'

AND papf.PERSON_ID = RELL.PERSON_ID)), NULL) AS PERSON_NUMBER ,

null relationship_number,

null payroll,

null payroll_period_name,

null payroll_period_number,

null date_earned,

NULL 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,

null as RUN_TYPE_NAME,

pfi.instance_name as flow_name,

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,

null Earn_start_date,

null Earn_end_date

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 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 = '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 distinct 'PRA' OBJECT_TYPE,

prl.payroll_relationship_id as OBJECT_ID,

pay_obj_act.payroll_rel_ACTION_ID OBJECT_ACTION_ID,

NVL(

decode(actiontypelookup.lookup_code,'L',

(SELECT MAX(ch.payroll_rel_action_id)

FROM pay_payroll_rel_actions ch

WHERE ch.source_id = pay_obj_act.payroll_rel_action_id

AND ch.RETRO_COMPONENT_ID IS NOT NULL

AND ch.payroll_relationship_id = pay_obj_act.payroll_relationship_id

)

,(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 = pay_obj_act.payroll_rel_action_id

AND ch.RETRO_COMPONENT_ID IS NULL

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

(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,

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,

null as Accounting_Start_Date,

null as Accounting_End_Date,

/* PERFORMANCE FIX : Bug 27934238

(select max(LocationDPEO.LOCATION_NAME) FROM HR_LOCATIONS_ALL_F_VL LocationDPEO,

PER_ALL_ASSIGNMENTS_f AssignmentDPEO,pay_rel_groups_dn asg

WHERE AssignmentDPEO.location_id=LocationDPEO.location_id(+)

AND AssignmentDPEO.ASSIGNMENT_ID=asg.ASSIGNMENT_ID

AND asg.payroll_relationship_id= pay_obj_act.payroll_relationship_id

AND payrollactioneo.effective_date BETWEEN LocationDPEO.EFFECTIVE_START_DATE(+) AND LocationDPEO.EFFECTIVE_END_DATE(+)) */

NULL AS LOCATION_NAME,

/* PERFORMANCE FIX : Bug 27934238

(select max(JobDPEO.NAME) FROM PER_JOBS_F_VL JobDPEO,

PER_ALL_ASSIGNMENTS_f AssignmentDPEO,pay_rel_groups_dn asg

WHERE AssignmentDPEO.job_id=JobDPEO.job_id(+)

AND AssignmentDPEO.ASSIGNMENT_ID=asg.ASSIGNMENT_ID

AND asg.payroll_relationship_id= pay_obj_act.payroll_relationship_id

AND payrollactioneo.effective_date BETWEEN JobDPEO.EFFECTIVE_START_DATE(+) AND JobDPEO.EFFECTIVE_END_DATE(+)) */

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

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_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('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 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 = 'PAY_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 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 (+) )

union all

SELECT distinct null OBJECT_TYPE,

/* PERFORMANCE FIX - Bug 27934238

to_char((select distinct max(pblv.action_parameter_value)

from pay_batch_line_values pblv,

pay_task_parameters_vl ddd

where pblv.batch_line_id = pbl.batch_line_id

and pblv.action_parameter_id = ddd.task_parameter_id

and pblv.action_parameter_value is not null

and ddd.display_flag='N'

and ddd.element_name='PAYROLL_RELATIONSHIP_ID')) */

pbl.batch_line_id as OBJECT_ID, /* Bug 27934238 : Added dummy column to drive the sql through object_id value passed in where clause */

pbl.batch_line_id OBJECT_ACTION_ID,

pbl.batch_line_id AS child_object_Action_id,

null prepay_rel_Action_id,

'BATCH' AS ACTION_CODE,

actionstatuslookup.meaning status,

actionstatuslookup.lookup_code action_status_code,

pblta.display_task_action_name|| (select max(':'||pblv.action_parameter_value)

from pay_batch_line_values pblv

where pblv.batch_line_id = pbl.batch_line_id

and pblv.action_parameter_id = pbltpp.task_parameter_id) action_type,

null action_type_code,

pbh.batch_id AS payroll_action_id,

null AS process_date,

null AS LockingFlag,

null AS LockingStatus ,

pblt.display_task_name||'-'||pt.task_name AS task_name,

(select distinct listagg( ddd.parameter_name||'='||pblv.action_parameter_value,'|') within group (order by ddd.param_sequence)

from pay_batch_line_values pblv,

pay_task_parameters_vl ddd

where pblv.batch_line_id = pbl.batch_line_id

and pblv.action_parameter_id = ddd.task_parameter_id

and ddd.display_flag != 'N'

and pblv.action_parameter_value is not null) object_name,

null object_reference,

null person_id,

null person_number,

null relationship_number,

null payroll,

null payroll_period_name,

null payroll_period_number,

null date_earned,

NULL 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,

pft.flow_task_instance_id task_instance_id,

null as RUN_TYPE_NAME,

pfi.instance_name as flow_name,

null as Accounting_Start_Date,

null as Accounting_End_Date,

null as LOCATION_NAME,

null as JOB_NAME,

NULL ACT_FLAG_ACTION_TYPE,

null pay_request_id,

null Earn_start_date,

null Earn_end_date

FROM pay_batch_headers pbh,

pay_batch_lines pbl,

pay_flow_task_instances pft,

pay_flow_tasks_vl ft,

PAY_FLOW_TASK_PARAM_VALS ppp,

pay_flow_task_parameters pftp,

pay_task_parameters ptp,

hcm_lookups actionstatuslookup,

pay_tasks_vl pt,

FUSION.pay_bl_task_actions_vl pblta,

pay_bl_tasks_vl pblt,

pay_flow_instances pfi,

pay_task_properties pbltp,

pay_task_parameters pbltpp,

per_legislative_data_groups_vl ldg

WHERE pfi.flow_instance_id=pft.flow_instance_id

AND ppp.flow_task_instance_id = pft.flow_task_instance_id

and pbh.legislative_data_group_id= ldg.legislative_data_group_id

and pftp.base_flow_task_param_id=ppp.base_flow_task_param_id

and pt.base_task_id = ft.base_task_id

and pftp.base_task_parameter_id = ptp.base_task_parameter_id

and ptp.BASE_TASK_PARAMETER_NAME like '%BATCH%'

/* PERFORMANCE FIX - Bug 27934238

and ppp.flow_task_param_value = to_char(pbh.batch_id) */

and DECODE(length(TRIM(translate(ppp.flow_task_param_value,'0123456789',' ') ) ),NULL,to_number(ppp.flow_task_param_value),NULL) = pbh.batch_id

AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID

and pbl.batch_id=pbh.batch_id

and decode(pbl.batch_line_status,'T','C',pbl.batch_line_status) = actionstatuslookup.lookup_code

AND actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS'

and pblta.task_action_id = pbl.task_action_id

and pblta.task_id = pblt.task_id

and pbltp.base_task_action_id (+) = pblta.task_action_id

and pbltp.TASK_PROPERTY_TYPE (+) = 'BL_KEY_PARAMETER'

and pbltpp.base_task_action_id (+) = pbltp.base_task_action_id

and pbltpp.element_name (+) = pbltp.task_property_value

and pt.task_type != 'MANUAL_TASK'

/* Bal Init */

union all

SELECT distinct null OBJECT_TYPE,

pbl.PAYROLL_RELATIONSHIP_ID as OBJECT_ID,

pbl.batch_line_id OBJECT_ACTION_ID,

pbl.batch_line_id AS child_object_Action_id,

null prepay_rel_Action_id,

'BATCH' AS ACTION_CODE,

nvl(actionstatuslookup.meaning,BLstatuslookup.meaning) status,

nvl(actionstatuslookup.lookup_code,BLstatuslookup.lookup_code) action_status_code,

pt.task_name action_type,

null action_type_code,

pbh.batch_id AS payroll_action_id,

null AS process_date,

null AS LockingFlag,

null AS LockingStatus ,

pt.task_name AS task_name,

'Line Sequence='||pbl.LINE_SEQUENCE||'|'||

'Payroll Relationship Number='||pbl.payroll_relationship_number||'|'||

'Assignment Number='||pbl.assignment_number||'|'||

'Balance Name='||pbl.BALANCE_NAME||'|'||

'Balance Dimension='||pbl.DIMENSION_NAME||'|'||

'Upload Date='||pbl.UPLOAD_DATE||'|'||

'Value='||pbl.VALUE

object_name,

null object_reference,

null person_id,

null person_number,

null relationship_number,

null payroll,

null payroll_period_name,

null payroll_period_number,

null date_earned,

NULL 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,

pft.flow_task_instance_id task_instance_id,

null as RUN_TYPE_NAME,

pfi.instance_name as flow_name,

null as Accounting_Start_Date,

null as Accounting_End_Date,

null as LOCATION_NAME,

null as JOB_NAME,

NULL ACT_FLAG_ACTION_TYPE,

null pay_request_id,

null Earn_start_date,

null Earn_end_date

FROM PAY_BAL_BATCH_HEADERS pbh,

PAY_BAL_BATCH_LINES pbl,

pay_payroll_rel_actions pra,

pay_flow_task_instances pft,

pay_flow_tasks_vl ft,

PAY_FLOW_TASK_PARAM_VALS ppp,

pay_flow_task_parameters pftp,

pay_task_parameters ptp,

hcm_lookups actionstatuslookup,

hcm_lookups BLstatuslookup,

pay_flow_instances pfi,

per_legislative_data_groups_vl ldg,

pay_tasks_vl pt

WHERE pfi.flow_instance_id=pft.flow_instance_id

AND ppp.flow_task_instance_id = pft.flow_task_instance_id

and pbh.legislative_data_group_id= ldg.legislative_data_group_id

and pftp.base_flow_task_param_id=ppp.base_flow_task_param_id

and pt.base_task_id = ft.base_task_id

and pftp.base_task_parameter_id = ptp.base_task_parameter_id

and ptp.BASE_TASK_PARAMETER_NAME like '%BATCH%'

and pt.base_task_name='BALANCE_INITIALIZATION'

and ppp.flow_task_param_value = to_char(pbh.batch_id)

AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID

and pbl.batch_id=pbh.batch_id

and pra.payroll_rel_action_id(+) = pbl.payroll_rel_action_id

AND BLstatuslookup.lookup_type = 'PAY_BATCH_STATUS'

AND BLstatuslookup.lookup_code = pbl.batch_line_status

AND actionstatuslookup.lookup_type(+) = 'PAY_ACTION_STATUS'

AND actionstatuslookup.lookup_code(+) = pra.action_status

/*union

SELECT distinct

null OBJECT_TYPE,

to_char(rownum) as OBJECT_ID,

pblr.row_id OBJECT_ACTION_ID,

pblr.row_id child_object_Action_id,

null prepay_rel_Action_id,

'BATCH' AS ACTION_CODE,

actionstatuslookup.meaning status,

actionstatuslookup.lookup_code action_status_code,

pblr.LINE_OPERATION action_type,

null action_type_code,

pbh.data_set_id AS payroll_action_id,

null AS process_date,

null AS LockingFlag,

null AS LockingStatus ,

dbo.bus_obj_file_discriminator AS task_name,

'Business Object='||dbo.bus_obj_file_discriminator||'|Operation='||pblr.LINE_OPERATION||'|'||pay_flow_common_util_pkg.pay_hdl_function(pblr.row_id,pblr.VO_MAPPING_ID) object_name,

null object_reference,

null person_id,

null person_number,

null relationship_number,

null payroll,

null payroll_period_name,

null payroll_period_number,

null date_earned,

null assignment_number,

NULL ASSIGNMENT_ID,

null Relationship_type,

null keywords,

null legislative_data_group_id,

null legislative_data_group_name,

null assignment_status,

aaa.flow_task_instance_id task_instance_id,

null as RUN_TYPE_NAME,

pfi.instance_name flow_name,

null as Accounting_Start_Date,

null as Accounting_End_Date,

null as LOCATION_NAME,

null as JOB_NAME,

NULL ACT_FLAG_ACTION_TYPE,

null pay_request_id,

null Earn_start_date,

null Earn_end_date

FROM pay_requests aaa,

REQUEST_HISTORY essa,

pay_flow_instances pfi,

HRC_DL_DATA_SETS pbh,

hrc_dl_data_set_bus_objs pbho,

hrc_dl_file_headers pbhh,

hrc_dl_file_rows pblr,

HRC_DL_BUSINESS_OBJECTS dbo,

hcm_lookups actionstatuslookup

WHERE aaa.flow_instance_id = pfi.flow_instance_id

and aaa.call_id = essa.parentrequestid

and aaa.call_type like '%ESS%'

and essa.requestid = pbh.request_id

and pbho.data_set_id = pbh.data_set_id

and pbho.DATA_SET_BUS_OBJ_ID = pbhh.DATA_SET_BUS_OBJ_ID

and pbhh.header_id = pblr.header_id

and dbo.business_object_id = pbhh.business_object_id

and actionstatuslookup.lookup_type = 'PAY_ACTION_STATUS'

and decode(pblr.transfer_status,null,'C','UNPROCESSED','U','NOT_READY','U','ERROR','E','SUCCESS','C',decode(instrb(pblr.transfer_status,'ERROR'),0,'C','E')) = actionstatuslookup.lookup_code */

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