PAY_TASK_STATUS

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
FLOW_INSTANCE_ID LEGISLATIVE_DATA_GROUP_ID BASE_FLOW_ID TASK_INSTANCE_ID FLOW_NAME FLOW_PATTERN CAT_CK_INSTANCE_ID CAT_CK_INSTANCE_NAME SUB_CAT_CK_INSTANCE_ID SUB_CAT_CK_INSTANCE_NAME CHECKLIST_NAME TASK_NAME BASE_TASK_NAME TASK_TYPE TASK_PARAM_PARTIAL FLOW_PARAM_FULL TASK_PARAM_FULL SUBMITTED_BY SUBMITTED_ON SCHEDULED_DATE ACTION_1 ACTION_2 ACTION_3 ACTION_1_ID ACTION_2_ID ACTION_3_ID NUMBER_OF_OBJECTS OBJECT_NAME DUE_DATE EXPECTED_TIME_LEFT TASK_STATUS ERRORS_AND_WARNINGS EXPORT COMPLETION_DATE TASK_PROGRESS TASK_PHASE_DESCRIPTION TASK_PHASE STATUS PROCESSING_FLAG TO_DO_FLAG REQUIRES_ATTENTION_FLAG RECENTLY_COMPLETD_FLAG REQUESTID SUMMARY_TYPE SUMMARY_BREAKDOWN PAYROLL LOCATION TRU PSU EXTRACT_TYPE EXTRACT_GROUP EXTRACT_AREA RUN_SEQUENCE TASK_CHK_INST_ID TARGET_URI TARGET_URI_PARAMS ACTIVE_FLAG REGION_LAST_UPDATED_DATE PAGE_LAST_UPDATED_DATE CHECKLIST_DESCRIPTION TASK_DESCRIPTION BASE_CHECKLIST_ID BASE_TASK_ID BASE_FLOW_TASK_ID REPORT_CATEGORY_ID ALL_ACTIONS DEFAULT_FLOW_FLAG SKIP_FLOW_FLAG FORCE_SUBMIT_FLAG SUBMIT_SIMILAR_FLAG SKIP_TASK_FLAG ESSREQUESTID ARCHIVEDINFO BATCH_ID TIME_TAKEN TIME_LEFT TASK_STATUS_RANK LDG_NAME MODULE_NAME STATS_ENABLED_FLAG STATS_BINDS STATS_ENABLED_FLAG2 STATS_BINDS2 HDL_BATCH_ID

Name

FLOW_INSTANCE_ID

LEGISLATIVE_DATA_GROUP_ID

BASE_FLOW_ID

TASK_INSTANCE_ID

FLOW_NAME

FLOW_PATTERN

CAT_CK_INSTANCE_ID

CAT_CK_INSTANCE_NAME

SUB_CAT_CK_INSTANCE_ID

SUB_CAT_CK_INSTANCE_NAME

CHECKLIST_NAME

TASK_NAME

BASE_TASK_NAME

TASK_TYPE

TASK_PARAM_PARTIAL

FLOW_PARAM_FULL

TASK_PARAM_FULL

SUBMITTED_BY

SUBMITTED_ON

SCHEDULED_DATE

ACTION_1

ACTION_2

ACTION_3

ACTION_1_ID

ACTION_2_ID

ACTION_3_ID

NUMBER_OF_OBJECTS

OBJECT_NAME

DUE_DATE

EXPECTED_TIME_LEFT

TASK_STATUS

ERRORS_AND_WARNINGS

EXPORT

COMPLETION_DATE

TASK_PROGRESS

TASK_PHASE_DESCRIPTION

TASK_PHASE

STATUS

PROCESSING_FLAG

TO_DO_FLAG

REQUIRES_ATTENTION_FLAG

RECENTLY_COMPLETD_FLAG

REQUESTID

SUMMARY_TYPE

SUMMARY_BREAKDOWN

PAYROLL

LOCATION

TRU

PSU

EXTRACT_TYPE

EXTRACT_GROUP

EXTRACT_AREA

RUN_SEQUENCE

TASK_CHK_INST_ID

TARGET_URI

TARGET_URI_PARAMS

ACTIVE_FLAG

REGION_LAST_UPDATED_DATE

PAGE_LAST_UPDATED_DATE

CHECKLIST_DESCRIPTION

TASK_DESCRIPTION

BASE_CHECKLIST_ID

BASE_TASK_ID

BASE_FLOW_TASK_ID

REPORT_CATEGORY_ID

ALL_ACTIONS

DEFAULT_FLOW_FLAG

SKIP_FLOW_FLAG

FORCE_SUBMIT_FLAG

SUBMIT_SIMILAR_FLAG

SKIP_TASK_FLAG

ESSREQUESTID

ARCHIVEDINFO

BATCH_ID

TIME_TAKEN

TIME_LEFT

TASK_STATUS_RANK

LDG_NAME

MODULE_NAME

STATS_ENABLED_FLAG

STATS_BINDS

STATS_ENABLED_FLAG2

STATS_BINDS2

HDL_BATCH_ID

Query

SQL_Statement
select FLOW_INSTANCE_ID, LEGISLATIVE_DATA_GROUP_ID, BASE_FLOW_ID, TASK_INSTANCE_ID, FLOW_NAME, FLOW_PATTERN, CAT_CK_INSTANCE_ID, CAT_CK_INSTANCE_NAME, SUB_CAT_CK_INSTANCE_ID, SUB_CAT_CK_INSTANCE_NAME, CHECKLIST_NAME, TASK_NAME, BASE_TASK_NAME, TASK_TYPE, TASK_PARAM_PARTIAL, FLOW_PARAM_FULL, TASK_PARAM_FULL, SUBMITTED_BY, SUBMITTED_ON, SCHEDULED_DATE, ACTION_1, ACTION_2, ACTION_3, ACTION_1_ID, ACTION_2_ID, ACTION_3_ID, NUMBER_OF_OBJECTS, OBJECT_NAME, DUE_DATE, EXPECTED_TIME_LEFT, TASK_STATUS, ERRORS_AND_WARNINGS, EXPORT, COMPLETION_DATE, TASK_PROGRESS, TASK_PHASE_DESCRIPTION, TASK_PHASE, STATUS, PROCESSING_FLAG, TO_DO_FLAG, REQUIRES_ATTENTION_FLAG, RECENTLY_COMPLETD_FLAG, REQUESTID, SUMMARY_TYPE, SUMMARY_BREAKDOWN, PAYROLL, LOCATION, TRU, PSU, EXTRACT_TYPE, EXTRACT_GROUP, EXTRACT_AREA, RUN_SEQUENCE, TASK_CHK_INST_ID, TARGET_URI, TARGET_URI_PARAMS, ACTIVE_FLAG, REGION_LAST_UPDATED_DATE, PAGE_LAST_UPDATED_DATE, CHECKLIST_DESCRIPTION, TASK_DESCRIPTION, BASE_CHECKLIST_ID, BASE_TASK_ID, BASE_FLOW_TASK_ID, REPORT_CATEGORY_ID, ALL_ACTIONS, DEFAULT_FLOW_FLAG, SKIP_FLOW_FLAG, FORCE_SUBMIT_FLAG, SUBMIT_SIMILAR_FLAG, SKIP_TASK_FLAG, ESSREQUESTID, ARCHIVEDINFO, BATCH_ID, TIME_TAKEN, TIME_LEFT, TASK_STATUS_RANK, LDG_NAME, MODULE_NAME, STATS_ENABLED_FLAG, STATS_BINDS, STATS_ENABLED_FLAG2, STATS_BINDS2, HDL_BATCH_ID from ( select FLOW_INSTANCE_ID, LDG_ID LEGISLATIVE_DATA_GROUP_ID, BASE_FLOW_ID, TASK_INSTANCE_ID, FLOW_NAME, FLOW_PATTERN, CAT_CK_INSTANCE_ID, CAT_CK_INSTANCE_NAME, SUB_CAT_CK_INSTANCE_ID, SUB_CAT_CK_INSTANCE_NAME, CHECKLIST_NAME, TASK_NAME, TASK_TYPE, TASK_PARAM_PARTIAL, FLOW_PARAM_FULL, TASK_PARAM_FULL, SUBMITTED_BY, SUBMITTED_ON, SCHEDULED_DATE, ACTION_1, ACTION_2, ACTION_3, ACTION_1_ID, ACTION_2_ID, ACTION_3_ID, NUMBER_OF_OBJECTS, OBJECT_NAME, DUE_DATE, EXPECTED_TIME_LEFT, TASK_STATUS, ERRORS_AND_WARNINGS, EXPORT, COMPLETION_DATE, TASK_PROGRESS, TASK_PHASE_DESCRIPTION, TASK_PHASE, STATUS, PROCESSING_FLAG, TO_DO_FLAG, REQUIRES_ATTENTION_FLAG, RECENTLY_COMPLETD_FLAG, REQUESTID, SUMMARY_TYPE, SUMMARY_BREAKDOWN, PAYROLL, LOCATION, TRU, PSU, EXTRACT_TYPE, EXTRACT_GROUP, EXTRACT_AREA, RUN_SEQUENCE, TASK_CHK_INST_ID, TARGET_URI, TARGET_URI_PARAMS, ACTIVE_FLAG, REGION_LAST_UPDATED_DATE, PAGE_LAST_UPDATED_DATE, CHECKLIST_DESCRIPTION, TASK_DESCRIPTION, BASE_CHECKLIST_ID, BASE_TASK_ID, BASE_FLOW_TASK_ID, REPORT_CATEGORY_ID, ALL_ACTIONS, DEFAULT_FLOW_FLAG, SKIP_FLOW_FLAG, FORCE_SUBMIT_FLAG, SUBMIT_SIMILAR_FLAG, SKIP_TASK_FLAG, ESSREQUESTID, ARCHIVEDINFO, BATCH_ID, HDL_batch_id, TIME_TAKEN, TIME_LEFT, TASK_STATUS_RANK, LDG_NAME, MODULE_NAME, STATS_ENABLED_FLAG, STATS_BINDS, STATS_ENABLED_FLAG2, STATS_BINDS2, BASE_TASK_NAME from ( select 'N' as STATS_ENABLED_FLAG, 'N' as STATS_ENABLED_FLAG2, ldg_id, base_flow_id ,decode(base_task_name, 'PAYROLL_RUN','pDefaultContext=PAYROLL\|pDefaultStatsType=NET,REGULAR EARNING,SUPPLEMENTARY EARNING,PRE TAX DEDUCTION,INVOLUNTARY DEDUCTION,TOTAL EARNING,TOTAL DEDUCTION\|pRestrictCxtList=PROCESS_DATE,PAYROLL,RUN TYPE,PSU,TRU\|pRestrictCxtName=PROCESS CATEGORY\|pRestrictCxtValList=Payroll Calculation\|StatisticalCompName=Gross-to-Net Balances\|pGraphType=BAR_VERT_CLUST', 'QUICK_PAY','pDefaultContext=PAYROLL\|pDefaultStatsType=NET,REGULAR EARNING,SUPPLEMENTARY EARNING,PRE TAX DEDUCTION,INVOLUNTARY DEDUCTION,TOTAL EARNING,TOTAL DEDUCTION\|pRestrictCxtList=PROCESS_DATE,PAYROLL,RUN TYPE,PSU,TRU\|pRestrictCxtName=PROCESS CATEGORY\|pRestrictCxtValList=Payroll Calculation\|StatisticalCompName=Gross-to-Net Balances\|pGraphType=BAR_VERT_CLUST', 'QUICK_PAY_NEW','pDefaultContext=PAYROLL\|pDefaultStatsType=NET,REGULAR EARNING,SUPPLEMENTARY EARNING,PRE TAX DEDUCTION,INVOLUNTARY DEDUCTION,TOTAL EARNING,TOTAL DEDUCTION\|pRestrictCxtList=PROCESS_DATE,PAYROLL,RUN TYPE,PSU,TRU\|pRestrictCxtName=PROCESS CATEGORY\|pRestrictCxtValList=Payroll Calculation\|StatisticalCompName=Gross-to-Net Balances\|pGraphType=BAR_VERT_CLUST', 'PREPAYMENTS','pDefaultContext=PAYMENT TYPE\|pDefaultStatsType=TOTAL AMOUNT\|pRestrictCxtList=PAYROLL,RUN TYPE,PAYMENT METHOD,PAYMENT TYPE,PAYMENT DATE,PAYMENT CATEGORY\|pRestrictCxtName=PROCESS CATEGORY\|pRestrictCxtValList=Calculate Payment\|StatisticalCompName=Payments\|pGraphType=BAR', 'QUICK_PREPAYMENT','pDefaultContext=PAYMENT TYPE\|pDefaultStatsType=TOTAL AMOUNT\|pRestrictCxtList=PAYROLL,RUN TYPE,PAYMENT METHOD,PAYMENT TYPE,PAYMENT DATE,PAYMENT CATEGORY\|pRestrictCxtName=PROCESS CATEGORY\|pRestrictCxtValList=Calculate Payment\|StatisticalCompName=Payments\|pGraphType=BAR', 'QUICK_PREPAYMENT_NEW','pDefaultContext=PAYMENT TYPE\|pDefaultStatsType=TOTAL AMOUNT\|pRestrictCxtList=PAYROLL,RUN TYPE,PAYMENT METHOD,PAYMENT TYPE,PAYMENT DATE,PAYMENT CATEGORY\|pRestrictCxtName=PROCESS CATEGORY\|pRestrictCxtValList=Calculate Payment\|StatisticalCompName=Payments\|pGraphType=BAR', 'CHECK_PAYMENT','pDefaultContext=PAYMENT TYPE\|pDefaultStatsType=TOTAL AMOUNT\|pRestrictCxtList=PAYROLL,RUN TYPE,PAYMENT METHOD,PAYMENT TYPE,PAYMENT DATE,PAYMENT CATEGORY\|pRestrictCxtName=PROCESS CATEGORY\|pRestrictCxtValList=Calculate Payment\|StatisticalCompName=Payments\|pGraphType=BAR', 'ELECTRONIC_PAYMENT','pDefaultContext=PAYMENT TYPE\|pDefaultStatsType=TOTAL AMOUNT\|pRestrictCxtList=PAYROLL,RUN TYPE,PAYMENT METHOD,PAYMENT TYPE,PAYMENT DATE,PAYMENT CATEGORY\|pRestrictCxtName=PROCESS CATEGORY\|pRestrictCxtValList=Calculate Payment\|StatisticalCompName=Payments\|pGraphType=BAR', 'RETRO_COSTING','pDefaultContext=ACCOUNT (Money)\|pDefaultStatsType=Debit,Credit\|pRestrictCxtList=ACCOUNT (Money), ACCOUNT (Hours)\|pRestrictCxtName=ACCOUNT (Money)\|pRestrictCxtValList=Cost Account,Suspense Account,Default Account\|StatisticalCompName=Payroll Costing Summary\|pGraphType=BAR', 'COSTING_OF_RUN','pDefaultContext=ACCOUNT (Money)\|pDefaultStatsType=Debit,Credit\|pRestrictCxtList=ACCOUNT (Money), ACCOUNT (Hours)\|pRestrictCxtName=ACCOUNT (Money)\|pRestrictCxtValList=Cost Account,Suspense Account,Default Account\|StatisticalCompName=Payroll Costing Summary\|pGraphType=BAR', 'PARTIAL_PERIOD_ACCURAL','pDefaultContext=ACCOUNT (Money)\|pDefaultStatsType=Debit,Credit\|pRestrictCxtList=ACCOUNT (Money), ACCOUNT (Hours)\|pRestrictCxtName=ACCOUNT (Money)\|pRestrictCxtValList=Cost Account,Suspense Account,Default Account\|StatisticalCompName=Payroll Costing Summary\|pGraphType=BAR', 'COSTING_OF_PAYMENT','pDefaultContext=PAYMENT ACCOUNT TYPE\|pDefaultStatsType=Debit,Credit\|pRestrictCxtList=PAYMENT ACCOUNT TYPE\|pRestrictCxtName=PAYMENT ACCOUNT TYPE\|pRestrictCxtValList=Payroll Liability Account,Cash Clearing Account,Cash Account\|StatisticalCompName=Payment Costing Summary\|pGraphType=BAR', null) as STATS_BINDS, decode(base_task_name, 'PAYROLL_RUN','pDefaultContext=ACCOUNT (Money)\|pDefaultStatsType=Debit,Credit\|pRestrictCxtList=ACCOUNT (Money), ACCOUNT (Hours)\|pRestrictCxtName=ACCOUNT (Money)\|pRestrictCxtValList=Cost Account,Suspense Account,Default Account\|StatisticalCompName=Payroll Costing Summary\|pGraphType=BAR', null)as STATS_BINDS2, decode(aaa.Task_Type,'MANUAL', (select listagg (tp.element_name\|\|'='\|\|fp1.flow_task_param_value\|\|'\|') WITHIN GROUP (ORDER BY tp.element_name desc) element_name FROM pay_flow_task_param_vals fp1, pay_flow_task_parameters_vl ftp, pay_task_parameters_vl tp WHERE flow_task_instance_id = task_instance_id and fp1.action=aaa.base_task_action_id and ftp.base_flow_task_param_id = fp1.base_flow_task_param_id and ftp.base_Task_parameter_id = tp.base_task_parameter_id and fp1.action_sequence = (select MAX(action_sequence) from pay_flow_task_param_vals where fp1.action = action and fp1.flow_task_instance_id = flow_task_instance_id and ftp.param_usage_type = 'INPUT')) ,null) as TARGET_URI_PARAMS ,aaa.Flow_Instance_Id,aaa.Task_Instance_Id,aaa.Flow_Name,aaa.Flow_Pattern,aaa.Cat_CK_Instance_id,aaa.Cat_CK_Instance_Name,aaa.Sub_Cat_CK_Instance_id,aaa.Sub_Cat_CK_Instance_Name, aaa.Checklist_Name, nvl(aaa.Checklist_Name,aaa.Task_Name) Task_Name, aaa.Task_Type, aaa.Task_Param_Partial,aaa.Flow_Param_Full,aaa.Task_Param_Full,aaa.Submitted_By,aaa.Submitted_On,aaa.Scheduled_Date, decode(aaa.Action_1,null,decode(aaa.task_type,'MANUAL',(select max(vvv.ACTION_NAME) from pay_task_actions_vl vvv where vvv.base_task_id=aaa.base_task_id and vvv.EXECUTION_MODE in ('SUBMIT','VIEW','INITIALIZE')),null),aaa.Action_1) action_1, aaa.Action_2,aaa.Action_3, decode(aaa.Action_1_id,null,decode(aaa.task_type,'MANUAL',(select max(vvv.task_action_id) from pay_task_actions_vl vvv where vvv.base_task_id=aaa.base_task_id and vvv.EXECUTION_MODE in ('SUBMIT','VIEW','INITIALIZE')),null),aaa.Action_1_id) Action_1_id, aaa.Action_2_id,aaa.Action_3_id, decode(aaa.Number_of_Objects,0,decode(XXHIDEZERO,'Y',null,0),null,decode(XXHIDEZERO,'Y',null,0),aaa.Number_of_Objects) Number_of_Objects, decode(aaa.Number_of_Objects,0,decode(XXHIDEZERO,'Y',null,aaa.object_name),null,decode(XXHIDEZERO,'Y',null,aaa.object_name),aaa.object_name) Object_Name, aaa.Due_Date,aaa.Expected_Time_Left, decode(aaa.Task_Status,'IN_PROCESSING',decode(Task_Progress,100, (nvl((select max(decode(replace(replace(name,'_START_DATE',''),'_END_DATE',''), 'PROC_ARC_XML','INITIALIZING', 'PROC_BIP_JOBS','INITIALIZING', 'PROC_DEL_XML','INITIALIZING', 'PROC_DEL_ATTACHMENT','INITIALIZING', 'PROC_DEL_UCM_DOR','INITIALIZING', 'IN_PROCESSING')) from PAY_ACTION_DETAILS pad, pay_payroll_actions ppa where ppa.pay_request_id=requestid and ppa.action_type='XWr' and ppa.payroll_action_id = pad.payroll_action_id and name in ('PROC_ARC_XML_GEN_START_DATE', 'PROC_ARC_XML_GEN_END_DATE', 'PROC_BIP_JOBS_START_DATE', 'PROC_BIP_JOBS_END_DATE', 'PROC_DEL_XML_START_DATE', 'PROC_DEL_XML_END_DATE', 'PROC_DEL_ATTACHMENT_START_DATE', 'PROC_DEL_ATTACHMENT_END_DATE', 'PROC_DEL_UCM_DOR_START_DATE', 'PROC_DEL_UCM_DOR_END_DATE') group by replace(replace(name,'_START_DATE',''),'_END_DATE','') having count() = 1),'IN_PROCESSING')), aaa.Task_Status),aaa.Task_Status) Task_Status, decode(aaa.Errors_and_Warnings,0,decode(XXHIDEZERO,'Y',null,0),aaa.Errors_and_Warnings) Errors_and_Warnings, aaa.Export, aaa.Completion_Date,aaa.Task_Progress, decode(aaa.Status_code,'TO_DO',xxPercentage_Complete, 'PROCESSING',decode(Task_Progress,100, nvl((select max(decode(replace(replace(name,'_START_DATE',''),'_END_DATE',''), 'PROC_ARC_XML',xxPROC_ARC_XML, 'PROC_BIP_JOBS',xxPROC_BIP_JOBS, 'PROC_DEL_XML',xxPROC_DEL_XML, 'PROC_DEL_ATTACHMENT',xxPROC_DEL_ATTACHMENT, 'PROC_DEL_UCM_DOR',xxPROC_DEL_UCM_DOR, xxPercentage_Complete)) from PAY_ACTION_DETAILS pad, pay_payroll_actions ppa where ppa.pay_request_id=requestid and ppa.action_type='XWr' and ppa.payroll_action_id = pad.payroll_action_id and name in ('PROC_ARC_XML_GEN_START_DATE', 'PROC_ARC_XML_GEN_END_DATE', 'PROC_BIP_JOBS_START_DATE', 'PROC_BIP_JOBS_END_DATE', 'PROC_DEL_XML_START_DATE', 'PROC_DEL_XML_END_DATE', 'PROC_DEL_ATTACHMENT_START_DATE', 'PROC_DEL_ATTACHMENT_END_DATE', 'PROC_DEL_UCM_DOR_START_DATE', 'PROC_DEL_UCM_DOR_END_DATE') group by replace(replace(name,'_START_DATE',''),'_END_DATE','') having count() = 1),xxPercentage_Complete), decode(aaa.Task_Status,'INITIALIZING',xxInitializing, xxPercentage_Complete)), 'NOT_STARTED',xxNot_Started, decode(aaa.Errors_and_Warnings,0,xxComplete,null,xxComplete,xxComplete_with_issues)) Task_Phase_description, decode(aaa.Time_Left,null, decode(aaa.Time_Taken,null,aaa.Task_Phase,aaa.Time_Taken\|\|' '\|\|xxLapsed), decode(taking_longer,null,Time_Left\|\|' '\|\|xxRemaining,Time_Left\|\|' '\|\|xxOverdue)) Task_Phase, aaa.status, aaa.Processing_flag,aaa.to_do_flag,aaa.Requires_attention_flag,aaa.Recently_completd_flag, aaa.requestid, aaa.summary_type,aaa.summary_breakdown,aaa.Payroll,aaa.Location,aaa.TRU,aaa.PSU,aaa.Extract_Type,aaa.Extract_Group,aaa.Extract_Area,aaa.RUN_SEQUENCE,aaa.TASK_CHK_INST_ID, aaa.TARGET_URI ,aaa.active_flag, sysdate region_last_updated_date, sysdate page_last_updated_date, checklist_description, nvl(checklist_description,task_description) task_description, base_checklist_id,base_task_id,base_flow_task_id,report_category_id,all_actions, decode(default_flow_flag,'Y','Y','N') default_flow_flag, skip_flow_flag, force_submit_flag, default_flow_flag submit_similar_flag,skip_task_flag,essrequestid, decode(archivedinfo,0,decode(XXHIDEZERO,'Y',null,decode(aaa.Extract_Type,null,null,0)),null,decode(XXHIDEZERO,'Y',null,decode(aaa.Extract_Type,null,null,0)),archivedinfo) archivedinfo,batchid as batch_id,hdlbatchid hdl_batch_id, Time_Taken,Time_Left, decode(Task_Status,'INITIALIZING',1,'IN_PROCESSING',2,'IN_PROGRESS',3,'COMPLETE_WITH_ISSUES',4,'MARKED_FOR_RETRY',5,'ROLLED_BACK',6,'NOT_STARTED',7,'SKIPPED',8,'COMPLETE',9,'SCHEDULED',10 ) task_status_rank,ldg_name,module_name,BASE_TASK_NAME from ( select flow_Instance_Id,ldg_id,base_flow_id,Task_Instance_Id,base_task_action_id,Flow_Name,Flow_Pattern,Cat_CK_Instance_id,Cat_CK_Instance_Name,Sub_Cat_CK_Instance_id,Sub_Cat_CK_Instance_Name, Checklist_Name, Task_Name, Task_Type, Task_Param_Partial,Flow_Param_Full,Task_Param_Full, Submitted_By,Submitted_On,Scheduled_Date, substrb(all_actions,1,instrb(all_actions,':',1)-1) Action_1, substrb(all_actions,instrb(all_actions,'\|',1,1)+1,instrb(all_actions,':',1,2)-instrb(all_actions,'\|',1,1)-1) Action_2, substrb(all_actions,instrb(all_actions,'\|',1,2)+1,instrb(all_actions,':',1,3)-instrb(all_actions,'\|',1,2)-1) Action_3, to_number(decode(instrb(all_actions,':',1,1),0,null,substrb(all_actions,instrb(all_actions,':',1,1)+1,decode(instrb(all_actions,'\|',1,1),0,length(all_actions),instrb(all_actions,'\|',1,1)-instrb(all_actions,':',1,1)-1)))) Action_1_id, to_number(decode(instrb(all_actions,':',1,2),0,null,substrb(all_actions,instrb(all_actions,':',1,2)+1,decode(instrb(all_actions,'\|',1,2),0,length(all_actions),instrb(all_actions,'\|',1,2)-instrb(all_actions,':',1,2)-1)))) Action_2_id, to_number(decode(instrb(all_actions,':',1,3),0,null,substrb(all_actions,instrb(all_actions,':',1,3)+1,decode(instrb(all_actions,'\|',1,3),0,length(all_actions),instrb(all_actions,'\|',1,3)-instrb(all_actions,':',1,3)-1)))) Action_3_id, substrb(Number_of_Objects_progress,1,instrb(Number_of_Objects_progress,'#')-1) Number_of_Objects, Object_Name, Due_Date, Expected_Time_Left, decode(Task_Status,'COMPLETE',decode(abs(round((last_update_Date-Submitted_On+sysdate-sysdate),2)), 0, abs(round((last_update_Date-Submitted_On+sysdate-sysdate)2460,2))\|\|' '\|\|xxMinutes, abs(round((last_update_Date-Submitted_On+sysdate-sysdate)24,2))\|\|' '\|\|xxHours),null) Time_Taken, decode(Task_Status,'IN_PROCESSING',decode(abs(round((Expected_Time_Left+sysdate-sysdate),2)), 0, abs(round((Expected_Time_Left+sysdate-sysdate)2460,2))\|\|' '\|\|xxMinutes, abs(round((Expected_Time_Left+sysdate-sysdate)24,2))\|\|' '\|\|xxHours),null) Time_Left, taking_longer, Task_Status, ERROR_MESSAGE_COUNT Errors_and_Warnings, decode(Export,null,'N','Y') export, Completion_Date,last_update_Date, Task_Phase, decode(Status_code,'TO_DO',nvl((select max(ci.progress) from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id),0), 'PROCESSING',decode(Task_Status,'INITIALIZING',null, decode(substrb(Number_of_Objects_progress,1,instrb(Number_of_Objects_progress,'#')-1),null,0,0,0, (substrb(Number_of_Objects_progress,1,instrb(Number_of_Objects_progress,'#')-1)-substrb(number_of_objects_progress,instrb(number_of_objects_progress,'#')+1))/substrb(Number_of_Objects_progress,1,instrb(Number_of_Objects_progress,'#')-1) )100) ,null) Task_Progress, Processing_flag,to_do_flag,Requires_attention_flag,Recently_completd_flag, status, requestid, summary_type,summary_breakdown,Payroll,Location,TRU,PSU,Extract_Type,Extract_Group,Extract_Area,RUN_SEQUENCE,TASK_CHK_INST_ID,TARGET_URI, decode(task_status,'SKIPPED','N','SCHEDULED','N','ROLLED_BACK','N','NOT_STARTED','N','COMPLETE','N','MARKED_FOR_RETRY','N','COMPLETE_WITH_ISSUES','N','Y') active_flag, checklist_description, task_description, base_checklist_id,base_task_id,base_flow_task_id,report_category_id,all_actions, default_flow_flag, skip_flow_flag,skip_task_flag,force_submit_flag,essrequestid,Status_code, decode(ARCHIVED_INFORMATION,null,decode(report_category_id,null,null, (nvl((select sum(1) cc from pay_action_information pai, pay_payroll_actions ppa where ppa.pay_request_id = requestid and pai.action_information_category!='DUMMY' and pai.ACTION_CONTEXT_TYPE = 'PPA' and pai.ACTION_CONTEXT_id = ppa.payroll_action_id),0) + nvl((select sum(1) cc from pay_action_information pai, pay_payroll_rel_actions pra, pay_payroll_actions ppa where ppa.pay_request_id = requestid and ppa.payroll_action_id = pra.payroll_action_id and pai.action_information_category!='DUMMY' and pai.ACTION_CONTEXT_TYPE = 'PRA' and pai.ACTION_CONTEXT_id = pra.payroll_rel_action_id),0) + nvl((select sum(1) cc from pay_action_information pai, pay_payroll_actions ppa, pay_object_actions poa where ppa.pay_request_id = requestid and ppa.payroll_action_id = poa.payroll_action_id and pai.action_information_category!='DUMMY' and pai.ACTION_CONTEXT_TYPE = 'POA' and pai.ACTION_CONTEXT_id = poa.object_action_id),0) + nvl((select sum(1) cc from pay_action_information pai, pay_payroll_actions ppa, pay_temp_object_actions pta where ppa.pay_request_id = requestid and ppa.payroll_action_id = pta.payroll_action_id and pai.action_information_category!='DUMMY' and pai.ACTION_CONTEXT_TYPE = 'PTOA' and pai.ACTION_CONTEXT_id = pta.temp_object_action_id ),0))),ARCHIVED_INFORMATION) archivedinfo, batchid,hdlbatchid, XXINITIALIZING,XXPERCENTAGE_COMPLETE,XXTO_DO,XXPROCESSING,XXSTATUS,XXPROC_ARC_XML,XXPROC_BIP_JOBS,XXPROC_DEL_XML,XXPROC_DEL_ATTACHMENT, XXPROC_DEL_UCM_DOR,XXCOMPLETE,XXLAPSED,XXREMAINING,XXOVERDUE,XXMINUTES,XXHOURS,XXCOMPLETE_WITH_ISSUES,XXNOT_STARTED,XXHIDEZERO,ldg_name,module_name,ARCHIVED_INFORMATION,BASE_TASK_NAME from ( select flow_Instance_Id,ldg_id,base_flow_id,Task_Instance_Id,base_task_action_id,Flow_Name,Flow_Pattern, (select prnt_chklst_inst_id from pay_checklist_instances ci1 where ci1.CHECKLIST_INSTANCE_id=( select prnt_chklst_inst_id from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id)) as Cat_CK_Instance_id, (select c.checklist_name from pay_checklist_instances ci1 , pay_checklists_vl c where c.checklist_id= ci1.Base_checklist_id and ci1.CHECKLIST_INSTANCE_id=( select prnt_chklst_inst_id from pay_checklist_instances ci2 where ci2.CHECKLIST_INSTANCE_id= (select prnt_chklst_inst_id from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id))) as Cat_CK_Instance_Name, (select prnt_chklst_inst_id from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id) as Sub_Cat_CK_Instance_id, (select c.checklist_name from pay_checklist_instances ci1 , pay_checklists_vl c where c.checklist_id= ci1.Base_checklist_id and ci1.CHECKLIST_INSTANCE_id=( select prnt_chklst_inst_id from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id)) as Sub_Cat_CK_Instance_Name, Checklist_Name, Task_Name, Task_Type, Task_Param_Partial,Flow_Param_Full,Task_Param_Full, Submitted_By,Submitted_On,Scheduled_Date, action_1,Action_2,Action_3, Action_1_id,Action_2_id,Action_3_id,batchid,hdlbatchid, decode(Number_of_Objects_progress,null,(decode(batchid,null,decode(hdlbatchid,null, (select sum(decode(pra.action_status,null,0,1)+decode(poa.action_status,null,0,1)+decode(pta.action_status,null,0,1))\|\|'#'\|\| sum(decode(pra.action_status,'U',1,0)+decode(poa.action_status,'U',1,0)+decode(pta.action_status,'U',1,0)) from pay_payroll_rel_actions pra, pay_payroll_actions ppa, pay_object_actions poa, pay_temp_object_actions pta where requestid = ppa.pay_request_id AND pra.source_action_id IS NULL AND pra.RETRO_COMPONENT_ID IS NULL AND ppa.action_type 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 ppa.payroll_action_id = pra.payroll_action_id (+) and ppa.payroll_action_id = poa.payroll_action_id (+) and ppa.payroll_action_id = pta.payroll_action_id (+)), (select IMPORT_LINES_TOTAL_COUNT\|\|'#'\|\|(IMPORT_LINES_TOTAL_COUNT-loaded_count-error_count-skipped_count-corrected_count) from HRC_DL_DATA_SETS hds where hds.DATA_SET_ID = hdlbatchid and batch_loader='X')), (select sum(decode(pbl.batch_line_status,null,0,1))\|\|'#'\|\| sum(decode(pbl.batch_line_status,'U',1,0)) from pay_batch_lines pbl where pbl.batch_id=batchid and batch_loader='Y')) ),Number_of_Objects_progress) Number_of_Objects_progress, Object_Name, Due_Date,Expected_Time_Left,taking_longer,Task_Status, Export, decode(task_status,'COMPLETE',last_update_Date,'SKIPPED',last_update_Date,null) Completion_Date, last_update_Date, decode(Status_code,'TO_DO',XXTO_DO,'PROCESSING',XXPROCESSING,XXSTATUS) Task_Phase, Processing_flag,to_do_flag,Requires_attention_flag,Recently_completd_flag,status, status_code,requestid,essrequestid, summary_type,summary_breakdown,Payroll,Location,TRU,PSU,Extract_Type,Extract_Group,Extract_Area,RUN_SEQUENCE,TASK_CHK_INST_ID,TARGET_URI, checklist_description, task_description, base_checklist_id,base_task_id,base_flow_task_id,report_category_id,default_flow_flag,skip_flow_flag,skip_task_flag,force_submit_flag, (select distinct listagg( pta.action_name\|\|':'\|\|pta.task_action_id,'\|') within group (order by pta.action_sequence) from pay_action_matrix ptm, pay_task_actions_vl pta where pta.base_task_id = task_id and action_allowed = 'Y' and ptm.current_node_status= current_status_code and ptm.succeeding_node_status= next_status_code and ptm.available_action= pta.execution_mode / Avoid the Submit if the Pay Action Exists */ and ((task_type != 'MANUAL' and requestid is not null and available_action!='SUBMIT') or (task_type != 'MANUAL' and requestid is null and available_action not in ('RERUN','ROLLBACK','MARK_FOR_RETRY')) or (task_type = 'MANUAL')) and not exists (select null from pay_action_matrix ptma where ptma.current_node_status= current_status_code and ptma.succeeding_node_status= next_status_code and ptma.available_action= pta.execution_mode and ptma.action_matrix_id > ptm.action_matrix_id and ptma.available_action = ptm.available_action)) all_actions, XXINITIALIZING,XXPERCENTAGE_COMPLETE,XXTO_DO,XXPROCESSING,XXSTATUS,XXPROC_ARC_XML,XXPROC_BIP_JOBS,XXPROC_DEL_XML,XXPROC_DEL_ATTACHMENT, XXPROC_DEL_UCM_DOR,XXCOMPLETE,XXLAPSED,XXREMAINING,XXOVERDUE,XXMINUTES,XXHOURS,XXCOMPLETE_WITH_ISSUES,XXNOT_STARTED,XXHIDEZERO,ldg_name,module_name,ARCHIVED_INFORMATION,BASE_TASK_NAME ,ERROR_MESSAGE_COUNT from ( select aaa.Flow_Instance_Id,aaa.ldg_id,aaa.base_flow_id,aaa.Task_Instance_Id,aaa.base_task_action_id,aaa.Flow_Name,aaa.Flow_Pattern,batch_loader, aaa.Checklist_Name, aaa.Task_Name, aaa.Task_Type, null Task_Param_Partial, (select distinct listagg( fp.parameter_name\|\|':'\|\| decode(fp.param_disp_type,'D',fnd_date.date_to_displaydate(to_Date(fpv.flow_param_value,'YYYY-MM-DD HH24:MI:SS')), 'T',fpv.flow_param_value, 'N',fpv.flow_param_value, PAY_FLOW_COMMON_UTIL_PKG.convert_value (to_char(sysdate,'YYYYMMDDHHMISS'),sysdate, aaa.lc_code, aaa.ldg_id, decode(fp.param_disp_type,'LK','LOOKUP','LOV','VO','L','VO','ORA_VALUESET','VALUESET'), fp.param_lookup,fpv.flow_param_value,'Y')) ,' \| ') within group (order by param_sequence) from pay_flow_param_values fpv, pay_flow_parameters_vl fp where aaa.flow_instance_id = fpv.flow_instance_id and fp.flow_parameter_id = fpv.base_flow_parameter_id and fpv.flow_param_value is not null and fp.display_flag!='N' ) Flow_Param_Full, (select distinct listagg( fp.parameter_name\|\|':'\|\| decode(fp.param_disp_type,'D',to_char(to_Date(fpv.flow_task_param_value,'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YYYY'), 'T',fpv.flow_task_param_value, 'N',fpv.flow_task_param_value, PAY_FLOW_COMMON_UTIL_PKG.convert_value (to_char(sysdate,'YYYYMMDDHHMISS'),sysdate, aaa.lc_code, aaa.ldg_id, decode(fp.param_disp_type,'LK','LOOKUP','LOV','VO','L','VO','ORA_VALUESET','VALUESET'), fp.param_lookup,fpv.flow_task_param_value,'Y')) ,' \| ') within group (order by param_sequence) from pay_flow_task_param_vals fpv, pay_flow_task_parameters_vl fp where aaa.task_instance_id = fpv.flow_task_instance_id and fp.flow_task_param_id = fpv.base_flow_task_param_id and fpv.flow_task_param_value is not null and fp.display_flag!='N' and fp.base_parameter_name != 'LEGISLATIVE_PARAMETERS' ) Task_Param_Full, aaa.task_Submitted_By Submitted_By,aaa.task_Submitted_On Submitted_On,aaa.task_Scheduled_Date Scheduled_Date, null Action_1,null Action_2,null Action_3, null Action_1_id,null Action_2_id,null Action_3_id, text.xxrecords Object_Name, (select pcl.due_date from PAY_CHECKLIST_INSTANCES pcl where pcl.flow_task_instance_id=aaa.task_instance_id) Due_Date, Expected_Time_Left,taking_longer, task_status_code Task_Status, (select to_char(max(prq.pay_request_id)) from pay_requests prq where prq.flow_instance_id = aaa.flow_instance_id and prq.flow_task_instance_id = aaa.task_instance_id and (exists( select 1 from pay_payroll_actions ppa where prq.pay_request_id = ppa.pay_request_id and ppa.REPORT_CATEGORY_ID is not null and batch_loader='N' and ppa.action_status='C') or exists(select 1 from fnd_attached_documents doc where to_char(prq.pay_request_id) = doc.pk1_value and doc.entity_name='FLOW_BI_OUTPUT'))) Export, (select to_char(max(prq.pay_request_id)) from pay_payroll_actions ppa, pay_requests prq where prq.flow_instance_id = aaa.flow_instance_id and batch_loader='N' and prq.flow_task_instance_id = aaa.task_instance_id and prq.pay_request_id = ppa.pay_request_id) requestid, (select max(pbh.batch_id) batch_id from PAY_FLOW_TASK_PARAM_VALS ppp, pay_batch_headers pbh, pay_flow_task_parameters pftp, pay_task_parameters ptp where ppp.flow_task_instance_id = aaa.task_instance_id and batch_loader='Y' and pftp.base_flow_task_param_id=ppp.base_flow_task_param_id and pftp.base_task_parameter_id = ptp.base_task_parameter_id and ptp.BASE_TASK_PARAMETER_NAME like '%BATCH%' and ppp.flow_task_param_value = to_char(pbh.batch_id)) batchid, (select max(pbh.data_set_id) batch_id from pay_requests prq, FUSION_ORA_ESS.REQUEST_HISTORY_VIEW essa, FUSION_ORA_ESS.REQUEST_HISTORY_VIEW essb, HRC_DL_DATA_SETS pbh where prq.flow_instance_id = aaa.flow_instance_id and batch_loader='X' and essa.requestid= prq.call_id and prq.call_type='ESS' and essa.absparentid=essb.absparentid and essb.requestid = pbh.request_id) hdlbatchid, (select to_char(max(prq.pay_request_id)) from pay_requests prq where prq.flow_instance_id = aaa.flow_instance_id and prq.flow_task_instance_id = aaa.task_instance_id and prq.call_type = 'ESS') essrequestid, null Processing_flag,null to_do_flag,null Requires_attention_flag,null Recently_completd_flag, aaa.Status,aaa.status_code, aaa.task_last_update_Date last_update_Date,aaa.pt_task_type,aaa.base_task_id,aaa.base_task_id task_id, (select max(decode(pt.base_task_name,'END_FLOW','ROLLEDBACK',npfti.status)) from PAY_FLOW_TASK_INTERACTNS pfti, pay_flow_task_instances npfti, pay_flow_tasks pft, pay_tasks pt where npfti.flow_instance_id = aaa.flow_instance_id and npfti.base_flow_Task_id = pfti.to_flow_task_id and pfti.from_flow_task_id = aaa.base_flow_task_id and pft.flow_task_id= npfti.base_flow_task_id and pt.task_id=pft.base_task_id ) next_status_code, int_task_code current_status_code, aaa.summary_type,aaa.summary_breakdown,aaa.Payroll,aaa.Location, aaa.TRU,aaa.PSU,aaa.Extract_Type,aaa.Extract_Group,aaa.Extract_Area ,aaa.RUN_SEQUENCE,aaa.TASK_CHK_INST_ID,aaa.TARGET_URI, aaa.checklist_description, aaa.task_description, aaa.base_checklist_id,aaa.base_flow_task_id,aaa.report_category_id,aaa.default_flow_flag,aaa.skip_flow_flag,skip_task_flag,force_submit_flag, text.XXINITIALIZING,text.XXPERCENTAGE_COMPLETE,text.XXTO_DO,text.XXPROCESSING,text.XXSTATUS,text.XXPROC_ARC_XML,text.XXPROC_BIP_JOBS,text.XXPROC_DEL_XML, text.XXPROC_DEL_ATTACHMENT,text.XXPROC_DEL_UCM_DOR,text.XXCOMPLETE,text.XXLAPSED,text.XXREMAINING,text.XXOVERDUE,text.XXMINUTES,text.XXHOURS,text.XXCOMPLETE_WITH_ISSUES,text.XXNOT_STARTED,text.XXHIDEZERO, aaa.action_allowed,aaa.ldg_name,module_name,aaa.ARCHIVED_INFORMATION,aaa.Number_of_Objects_progress,aaa.BASE_TASK_NAME, aaa.ERROR_MESSAGE_COUNT from PAY_flow_status_basic_VL aaa, (select max(decode(yyy.lookup_code,'INITIALIZING',yyy.meaning)) XXINITIALIZING, max(decode(yyy.lookup_code,'PERCENTAGE_COMPLETE',yyy.meaning)) XXPERCENTAGE_COMPLETE, max(decode(yyy.lookup_code,'TO_DO',yyy.meaning)) XXTO_DO, max(decode(yyy.lookup_code,'PROCESSING',yyy.meaning)) XXPROCESSING, max(decode(yyy.lookup_code,'STATUS',yyy.meaning)) XXSTATUS, max(decode(yyy.lookup_code,'PROC_ARC_XML',yyy.meaning)) XXPROC_ARC_XML, max(decode(yyy.lookup_code,'PROC_BIP_JOBS',yyy.meaning)) XXPROC_BIP_JOBS, max(decode(yyy.lookup_code,'PROC_DEL_XML',yyy.meaning)) XXPROC_DEL_XML, max(decode(yyy.lookup_code,'PROC_DEL_ATTACHMENT',yyy.meaning)) XXPROC_DEL_ATTACHMENT, max(decode(yyy.lookup_code,'PROC_DEL_UCM_DOR',yyy.meaning)) XXPROC_DEL_UCM_DOR, max(decode(yyy.lookup_code,'COMPLETE',yyy.meaning)) XXCOMPLETE, max(decode(yyy.lookup_code,'LAPSED',yyy.meaning)) XXLAPSED, max(decode(yyy.lookup_code,'REMAINING',yyy.meaning)) XXREMAINING, max(decode(yyy.lookup_code,'OVERDUE',yyy.meaning)) XXOVERDUE, max(decode(yyy.lookup_code,'MINUTES',yyy.meaning)) XXMINUTES, max(decode(yyy.lookup_code,'HOURS',yyy.meaning)) XXHOURS, max(decode(yyy.lookup_code,'COMPLETE_WITH_ISSUES',yyy.meaning)) XXCOMPLETE_WITH_ISSUES, max(decode(yyy.lookup_code,'RECORDS',yyy.meaning)) XXRECORDS, max(decode(yyy.lookup_code,'NOT_STARTED',yyy.meaning)) XXNOT_STARTED, max((select max(decode(PARAMETER_VALUE,null,null,'Y')) from pay_action_parameters where parameter_name='FLOW_UX_SHOW_ZERO')) XXHIDEZERO from hcm_lookups yyy where yyy.lookup_type = 'ORA_PAY_UX_TASK_PHASE') text )) XXX ) aaa) )

SQL_Statement

select FLOW_INSTANCE_ID,

LEGISLATIVE_DATA_GROUP_ID,

BASE_FLOW_ID,

TASK_INSTANCE_ID,

FLOW_NAME,

FLOW_PATTERN,

CAT_CK_INSTANCE_ID,

CAT_CK_INSTANCE_NAME,

SUB_CAT_CK_INSTANCE_ID,

SUB_CAT_CK_INSTANCE_NAME,

CHECKLIST_NAME,

TASK_NAME,

BASE_TASK_NAME,

TASK_TYPE,

TASK_PARAM_PARTIAL,

FLOW_PARAM_FULL,

TASK_PARAM_FULL,

SUBMITTED_BY,

SUBMITTED_ON,

SCHEDULED_DATE,

ACTION_1,

ACTION_2,

ACTION_3,

ACTION_1_ID,

ACTION_2_ID,

ACTION_3_ID,

NUMBER_OF_OBJECTS,

OBJECT_NAME,

DUE_DATE,

EXPECTED_TIME_LEFT,

TASK_STATUS,

ERRORS_AND_WARNINGS,

EXPORT,

COMPLETION_DATE,

TASK_PROGRESS,

TASK_PHASE_DESCRIPTION,

TASK_PHASE,

STATUS,

PROCESSING_FLAG,

TO_DO_FLAG,

REQUIRES_ATTENTION_FLAG,

RECENTLY_COMPLETD_FLAG,

REQUESTID,

SUMMARY_TYPE,

SUMMARY_BREAKDOWN,

PAYROLL,

LOCATION,

TRU,

PSU,

EXTRACT_TYPE,

EXTRACT_GROUP,

EXTRACT_AREA,

RUN_SEQUENCE,

TASK_CHK_INST_ID,

TARGET_URI,

TARGET_URI_PARAMS,

ACTIVE_FLAG,

REGION_LAST_UPDATED_DATE,

PAGE_LAST_UPDATED_DATE,

CHECKLIST_DESCRIPTION,

TASK_DESCRIPTION,

BASE_CHECKLIST_ID,

BASE_TASK_ID,

BASE_FLOW_TASK_ID,

REPORT_CATEGORY_ID,

ALL_ACTIONS,

DEFAULT_FLOW_FLAG,

SKIP_FLOW_FLAG,

FORCE_SUBMIT_FLAG,

SUBMIT_SIMILAR_FLAG,

SKIP_TASK_FLAG,

ESSREQUESTID,

ARCHIVEDINFO,

BATCH_ID,

TIME_TAKEN,

TIME_LEFT,

TASK_STATUS_RANK,

LDG_NAME,

MODULE_NAME,

STATS_ENABLED_FLAG,

STATS_BINDS,

STATS_ENABLED_FLAG2,

STATS_BINDS2,

HDL_BATCH_ID

from (

select FLOW_INSTANCE_ID,

LDG_ID LEGISLATIVE_DATA_GROUP_ID,

BASE_FLOW_ID,

TASK_INSTANCE_ID,

FLOW_NAME,

FLOW_PATTERN,

CAT_CK_INSTANCE_ID,

CAT_CK_INSTANCE_NAME,

SUB_CAT_CK_INSTANCE_ID,

SUB_CAT_CK_INSTANCE_NAME,

CHECKLIST_NAME,

TASK_NAME,

TASK_TYPE,

TASK_PARAM_PARTIAL,

FLOW_PARAM_FULL,

TASK_PARAM_FULL,

SUBMITTED_BY,

SUBMITTED_ON,

SCHEDULED_DATE,

ACTION_1,

ACTION_2,

ACTION_3,

ACTION_1_ID,

ACTION_2_ID,

ACTION_3_ID,

NUMBER_OF_OBJECTS,

OBJECT_NAME,

DUE_DATE,

EXPECTED_TIME_LEFT,

TASK_STATUS,

ERRORS_AND_WARNINGS,

EXPORT,

COMPLETION_DATE,

TASK_PROGRESS,

TASK_PHASE_DESCRIPTION,

TASK_PHASE,

STATUS,

PROCESSING_FLAG,

TO_DO_FLAG,

REQUIRES_ATTENTION_FLAG,

RECENTLY_COMPLETD_FLAG,

REQUESTID,

SUMMARY_TYPE,

SUMMARY_BREAKDOWN,

PAYROLL,

LOCATION,

TRU,

PSU,

EXTRACT_TYPE,

EXTRACT_GROUP,

EXTRACT_AREA,

RUN_SEQUENCE,

TASK_CHK_INST_ID,

TARGET_URI,

TARGET_URI_PARAMS,

ACTIVE_FLAG,

REGION_LAST_UPDATED_DATE,

PAGE_LAST_UPDATED_DATE,

CHECKLIST_DESCRIPTION,

TASK_DESCRIPTION,

BASE_CHECKLIST_ID,

BASE_TASK_ID,

BASE_FLOW_TASK_ID,

REPORT_CATEGORY_ID,

ALL_ACTIONS,

DEFAULT_FLOW_FLAG,

SKIP_FLOW_FLAG,

FORCE_SUBMIT_FLAG,

SUBMIT_SIMILAR_FLAG,

SKIP_TASK_FLAG,

ESSREQUESTID,

ARCHIVEDINFO,

BATCH_ID,

HDL_batch_id,

TIME_TAKEN,

TIME_LEFT,

TASK_STATUS_RANK,

LDG_NAME,

MODULE_NAME,

STATS_ENABLED_FLAG,

STATS_BINDS,

STATS_ENABLED_FLAG2,

STATS_BINDS2,

BASE_TASK_NAME

from (

select

'N' as STATS_ENABLED_FLAG,

'N' as STATS_ENABLED_FLAG2,

ldg_id,

base_flow_id

,decode(base_task_name,

'PAYROLL_RUN','pDefaultContext=PAYROLL|pDefaultStatsType=NET,REGULAR EARNING,SUPPLEMENTARY EARNING,PRE TAX DEDUCTION,INVOLUNTARY DEDUCTION,TOTAL EARNING,TOTAL DEDUCTION|pRestrictCxtList=PROCESS_DATE,PAYROLL,RUN TYPE,PSU,TRU|pRestrictCxtName=PROCESS CATEGORY|pRestrictCxtValList=Payroll Calculation|StatisticalCompName=Gross-to-Net Balances|pGraphType=BAR_VERT_CLUST',

'QUICK_PAY','pDefaultContext=PAYROLL|pDefaultStatsType=NET,REGULAR EARNING,SUPPLEMENTARY EARNING,PRE TAX DEDUCTION,INVOLUNTARY DEDUCTION,TOTAL EARNING,TOTAL DEDUCTION|pRestrictCxtList=PROCESS_DATE,PAYROLL,RUN TYPE,PSU,TRU|pRestrictCxtName=PROCESS CATEGORY|pRestrictCxtValList=Payroll Calculation|StatisticalCompName=Gross-to-Net Balances|pGraphType=BAR_VERT_CLUST',

'QUICK_PAY_NEW','pDefaultContext=PAYROLL|pDefaultStatsType=NET,REGULAR EARNING,SUPPLEMENTARY EARNING,PRE TAX DEDUCTION,INVOLUNTARY DEDUCTION,TOTAL EARNING,TOTAL DEDUCTION|pRestrictCxtList=PROCESS_DATE,PAYROLL,RUN TYPE,PSU,TRU|pRestrictCxtName=PROCESS CATEGORY|pRestrictCxtValList=Payroll Calculation|StatisticalCompName=Gross-to-Net Balances|pGraphType=BAR_VERT_CLUST',

'QUICK_PREPAYMENT_NEW','pDefaultContext=PAYMENT TYPE|pDefaultStatsType=TOTAL AMOUNT|pRestrictCxtList=PAYROLL,RUN TYPE,PAYMENT METHOD,PAYMENT TYPE,PAYMENT DATE,PAYMENT CATEGORY|pRestrictCxtName=PROCESS CATEGORY|pRestrictCxtValList=Calculate Payment|StatisticalCompName=Payments|pGraphType=BAR',

'PARTIAL_PERIOD_ACCURAL','pDefaultContext=ACCOUNT (Money)|pDefaultStatsType=Debit,Credit|pRestrictCxtList=ACCOUNT (Money), ACCOUNT (Hours)|pRestrictCxtName=ACCOUNT (Money)|pRestrictCxtValList=Cost Account,Suspense Account,Default Account|StatisticalCompName=Payroll Costing Summary|pGraphType=BAR',

'COSTING_OF_PAYMENT','pDefaultContext=PAYMENT ACCOUNT TYPE|pDefaultStatsType=Debit,Credit|pRestrictCxtList=PAYMENT ACCOUNT TYPE|pRestrictCxtName=PAYMENT ACCOUNT TYPE|pRestrictCxtValList=Payroll Liability Account,Cash Clearing Account,Cash Account|StatisticalCompName=Payment Costing Summary|pGraphType=BAR',

null) as STATS_BINDS,

decode(base_task_name,

null)as STATS_BINDS2,

decode(aaa.Task_Type,'MANUAL',

(select listagg (tp.element_name||'='||fp1.flow_task_param_value||'|')

WITHIN GROUP (ORDER BY tp.element_name desc) element_name

FROM pay_flow_task_param_vals fp1,

pay_flow_task_parameters_vl ftp,

pay_task_parameters_vl tp

WHERE flow_task_instance_id = task_instance_id

and fp1.action=aaa.base_task_action_id

and ftp.base_flow_task_param_id = fp1.base_flow_task_param_id

and ftp.base_Task_parameter_id = tp.base_task_parameter_id

and fp1.action_sequence = (select MAX(action_sequence)

from pay_flow_task_param_vals where fp1.action = action

and fp1.flow_task_instance_id = flow_task_instance_id

and ftp.param_usage_type = 'INPUT'))

,null) as TARGET_URI_PARAMS

,aaa.Flow_Instance_Id,aaa.Task_Instance_Id,aaa.Flow_Name,aaa.Flow_Pattern,aaa.Cat_CK_Instance_id,aaa.Cat_CK_Instance_Name,aaa.Sub_Cat_CK_Instance_id,aaa.Sub_Cat_CK_Instance_Name,

aaa.Checklist_Name,

nvl(aaa.Checklist_Name,aaa.Task_Name) Task_Name,

aaa.Task_Type,

aaa.Task_Param_Partial,aaa.Flow_Param_Full,aaa.Task_Param_Full,aaa.Submitted_By,aaa.Submitted_On,aaa.Scheduled_Date,

decode(aaa.Action_1,null,decode(aaa.task_type,'MANUAL',(select max(vvv.ACTION_NAME) from pay_task_actions_vl vvv where vvv.base_task_id=aaa.base_task_id and vvv.EXECUTION_MODE in ('SUBMIT','VIEW','INITIALIZE')),null),aaa.Action_1) action_1,

aaa.Action_2,aaa.Action_3,

decode(aaa.Action_1_id,null,decode(aaa.task_type,'MANUAL',(select max(vvv.task_action_id) from pay_task_actions_vl vvv where vvv.base_task_id=aaa.base_task_id and vvv.EXECUTION_MODE in ('SUBMIT','VIEW','INITIALIZE')),null),aaa.Action_1_id) Action_1_id,

aaa.Action_2_id,aaa.Action_3_id,

decode(aaa.Number_of_Objects,0,decode(XXHIDEZERO,'Y',null,0),null,decode(XXHIDEZERO,'Y',null,0),aaa.Number_of_Objects) Number_of_Objects,

decode(aaa.Number_of_Objects,0,decode(XXHIDEZERO,'Y',null,aaa.object_name),null,decode(XXHIDEZERO,'Y',null,aaa.object_name),aaa.object_name) Object_Name,

aaa.Due_Date,aaa.Expected_Time_Left,

decode(aaa.Task_Status,'IN_PROCESSING',decode(Task_Progress,100,

(nvl((select max(decode(replace(replace(name,'_START_DATE',''),'_END_DATE',''),

'PROC_ARC_XML','INITIALIZING',

'PROC_BIP_JOBS','INITIALIZING',

'PROC_DEL_XML','INITIALIZING',

'PROC_DEL_ATTACHMENT','INITIALIZING',

'PROC_DEL_UCM_DOR','INITIALIZING',

'IN_PROCESSING'))

from PAY_ACTION_DETAILS pad,

pay_payroll_actions ppa

where ppa.pay_request_id=requestid

and ppa.action_type='XWr'

and ppa.payroll_action_id = pad.payroll_action_id

and name in ('PROC_ARC_XML_GEN_START_DATE',

'PROC_ARC_XML_GEN_END_DATE',

'PROC_BIP_JOBS_START_DATE',

'PROC_BIP_JOBS_END_DATE',

'PROC_DEL_XML_START_DATE',

'PROC_DEL_XML_END_DATE',

'PROC_DEL_ATTACHMENT_START_DATE',

'PROC_DEL_ATTACHMENT_END_DATE',

'PROC_DEL_UCM_DOR_START_DATE',

'PROC_DEL_UCM_DOR_END_DATE')

group by replace(replace(name,'_START_DATE',''),'_END_DATE','')

having count(*) = 1),'IN_PROCESSING')),

aaa.Task_Status),aaa.Task_Status) Task_Status,

decode(aaa.Errors_and_Warnings,0,decode(XXHIDEZERO,'Y',null,0),aaa.Errors_and_Warnings) Errors_and_Warnings,

aaa.Export,

aaa.Completion_Date,aaa.Task_Progress,

decode(aaa.Status_code,'TO_DO',xxPercentage_Complete,

'PROCESSING',decode(Task_Progress,100,

nvl((select max(decode(replace(replace(name,'_START_DATE',''),'_END_DATE',''),

'PROC_ARC_XML',xxPROC_ARC_XML,

'PROC_BIP_JOBS',xxPROC_BIP_JOBS,

'PROC_DEL_XML',xxPROC_DEL_XML,

'PROC_DEL_ATTACHMENT',xxPROC_DEL_ATTACHMENT,

'PROC_DEL_UCM_DOR',xxPROC_DEL_UCM_DOR,

xxPercentage_Complete))

from PAY_ACTION_DETAILS pad,

pay_payroll_actions ppa

where ppa.pay_request_id=requestid

and ppa.action_type='XWr'

and ppa.payroll_action_id = pad.payroll_action_id

and name in ('PROC_ARC_XML_GEN_START_DATE',

'PROC_ARC_XML_GEN_END_DATE',

'PROC_BIP_JOBS_START_DATE',

'PROC_BIP_JOBS_END_DATE',

'PROC_DEL_XML_START_DATE',

'PROC_DEL_XML_END_DATE',

'PROC_DEL_ATTACHMENT_START_DATE',

'PROC_DEL_ATTACHMENT_END_DATE',

'PROC_DEL_UCM_DOR_START_DATE',

'PROC_DEL_UCM_DOR_END_DATE')

group by replace(replace(name,'_START_DATE',''),'_END_DATE','')

having count(*) = 1),xxPercentage_Complete),

decode(aaa.Task_Status,'INITIALIZING',xxInitializing,

xxPercentage_Complete)),

'NOT_STARTED',xxNot_Started,

decode(aaa.Errors_and_Warnings,0,xxComplete,null,xxComplete,xxComplete_with_issues)) Task_Phase_description,

decode(aaa.Time_Left,null, decode(aaa.Time_Taken,null,aaa.Task_Phase,aaa.Time_Taken||' '||xxLapsed), decode(taking_longer,null,Time_Left||' '||xxRemaining,Time_Left||' '||xxOverdue)) Task_Phase,

aaa.status,

aaa.Processing_flag,aaa.to_do_flag,aaa.Requires_attention_flag,aaa.Recently_completd_flag, aaa.requestid,

aaa.summary_type,aaa.summary_breakdown,aaa.Payroll,aaa.Location,aaa.TRU,aaa.PSU,aaa.Extract_Type,aaa.Extract_Group,aaa.Extract_Area,aaa.RUN_SEQUENCE,aaa.TASK_CHK_INST_ID,

aaa.TARGET_URI ,aaa.active_flag,

sysdate region_last_updated_date, sysdate page_last_updated_date,

checklist_description,

nvl(checklist_description,task_description) task_description,

base_checklist_id,base_task_id,base_flow_task_id,report_category_id,all_actions,

decode(default_flow_flag,'Y','Y','N') default_flow_flag,

skip_flow_flag, force_submit_flag, default_flow_flag submit_similar_flag,skip_task_flag,essrequestid,

decode(archivedinfo,0,decode(XXHIDEZERO,'Y',null,decode(aaa.Extract_Type,null,null,0)),null,decode(XXHIDEZERO,'Y',null,decode(aaa.Extract_Type,null,null,0)),archivedinfo) archivedinfo,batchid as batch_id,hdlbatchid hdl_batch_id,

Time_Taken,Time_Left,

decode(Task_Status,'INITIALIZING',1,'IN_PROCESSING',2,'IN_PROGRESS',3,'COMPLETE_WITH_ISSUES',4,'MARKED_FOR_RETRY',5,'ROLLED_BACK',6,'NOT_STARTED',7,'SKIPPED',8,'COMPLETE',9,'SCHEDULED',10 ) task_status_rank,ldg_name,module_name,BASE_TASK_NAME

from (

select flow_Instance_Id,ldg_id,base_flow_id,Task_Instance_Id,base_task_action_id,Flow_Name,Flow_Pattern,Cat_CK_Instance_id,Cat_CK_Instance_Name,Sub_Cat_CK_Instance_id,Sub_Cat_CK_Instance_Name,

Checklist_Name, Task_Name, Task_Type,

Task_Param_Partial,Flow_Param_Full,Task_Param_Full,

Submitted_By,Submitted_On,Scheduled_Date,

substrb(all_actions,1,instrb(all_actions,':',1)-1) Action_1,

substrb(all_actions,instrb(all_actions,'|',1,1)+1,instrb(all_actions,':',1,2)-instrb(all_actions,'|',1,1)-1) Action_2,

substrb(all_actions,instrb(all_actions,'|',1,2)+1,instrb(all_actions,':',1,3)-instrb(all_actions,'|',1,2)-1) Action_3,

to_number(decode(instrb(all_actions,':',1,1),0,null,substrb(all_actions,instrb(all_actions,':',1,1)+1,decode(instrb(all_actions,'|',1,1),0,length(all_actions),instrb(all_actions,'|',1,1)-instrb(all_actions,':',1,1)-1)))) Action_1_id,

to_number(decode(instrb(all_actions,':',1,2),0,null,substrb(all_actions,instrb(all_actions,':',1,2)+1,decode(instrb(all_actions,'|',1,2),0,length(all_actions),instrb(all_actions,'|',1,2)-instrb(all_actions,':',1,2)-1)))) Action_2_id,

to_number(decode(instrb(all_actions,':',1,3),0,null,substrb(all_actions,instrb(all_actions,':',1,3)+1,decode(instrb(all_actions,'|',1,3),0,length(all_actions),instrb(all_actions,'|',1,3)-instrb(all_actions,':',1,3)-1)))) Action_3_id,

substrb(Number_of_Objects_progress,1,instrb(Number_of_Objects_progress,'#')-1) Number_of_Objects,

Object_Name,

Due_Date,

Expected_Time_Left,

decode(Task_Status,'COMPLETE',decode(abs(round((last_update_Date-Submitted_On+sysdate-sysdate),2)),

0, abs(round((last_update_Date-Submitted_On+sysdate-sysdate)*24*60,2))||' '||xxMinutes,

abs(round((last_update_Date-Submitted_On+sysdate-sysdate)*24,2))||' '||xxHours),null) Time_Taken,

decode(Task_Status,'IN_PROCESSING',decode(abs(round((Expected_Time_Left+sysdate-sysdate),2)),

0, abs(round((Expected_Time_Left+sysdate-sysdate)*24*60,2))||' '||xxMinutes,

abs(round((Expected_Time_Left+sysdate-sysdate)*24,2))||' '||xxHours),null) Time_Left,

taking_longer,

Task_Status,

ERROR_MESSAGE_COUNT Errors_and_Warnings,

decode(Export,null,'N','Y') export,

Completion_Date,last_update_Date,

Task_Phase,

decode(Status_code,'TO_DO',nvl((select max(ci.progress) from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id),0),

'PROCESSING',decode(Task_Status,'INITIALIZING',null,

decode(substrb(Number_of_Objects_progress,1,instrb(Number_of_Objects_progress,'#')-1),null,0,0,0,

(substrb(Number_of_Objects_progress,1,instrb(Number_of_Objects_progress,'#')-1)-substrb(number_of_objects_progress,instrb(number_of_objects_progress,'#')+1))/substrb(Number_of_Objects_progress,1,instrb(Number_of_Objects_progress,'#')-1)

)*100) ,null) Task_Progress,

Processing_flag,to_do_flag,Requires_attention_flag,Recently_completd_flag, status, requestid,

summary_type,summary_breakdown,Payroll,Location,TRU,PSU,Extract_Type,Extract_Group,Extract_Area,RUN_SEQUENCE,TASK_CHK_INST_ID,TARGET_URI,

decode(task_status,'SKIPPED','N','SCHEDULED','N','ROLLED_BACK','N','NOT_STARTED','N','COMPLETE','N','MARKED_FOR_RETRY','N','COMPLETE_WITH_ISSUES','N','Y') active_flag,

checklist_description,

task_description,

base_checklist_id,base_task_id,base_flow_task_id,report_category_id,all_actions,

default_flow_flag,

skip_flow_flag,skip_task_flag,force_submit_flag,essrequestid,Status_code,

decode(ARCHIVED_INFORMATION,null,decode(report_category_id,null,null,

(nvl((select sum(1) cc

from pay_action_information pai,

pay_payroll_actions ppa

where ppa.pay_request_id = requestid

and pai.action_information_category!='DUMMY'

and pai.ACTION_CONTEXT_TYPE = 'PPA'

and pai.ACTION_CONTEXT_id = ppa.payroll_action_id),0) +

nvl((select sum(1) cc

from pay_action_information pai,

pay_payroll_rel_actions pra,

pay_payroll_actions ppa

where ppa.pay_request_id = requestid

and ppa.payroll_action_id = pra.payroll_action_id

and pai.action_information_category!='DUMMY'

and pai.ACTION_CONTEXT_TYPE = 'PRA'

and pai.ACTION_CONTEXT_id = pra.payroll_rel_action_id),0) +

nvl((select sum(1) cc

from pay_action_information pai,

pay_payroll_actions ppa,

pay_object_actions poa

where ppa.pay_request_id = requestid

and ppa.payroll_action_id = poa.payroll_action_id

and pai.action_information_category!='DUMMY'

and pai.ACTION_CONTEXT_TYPE = 'POA'

and pai.ACTION_CONTEXT_id = poa.object_action_id),0) +

nvl((select sum(1) cc

from pay_action_information pai,

pay_payroll_actions ppa,

pay_temp_object_actions pta

where ppa.pay_request_id = requestid

and ppa.payroll_action_id = pta.payroll_action_id

and pai.action_information_category!='DUMMY'

and pai.ACTION_CONTEXT_TYPE = 'PTOA'

and pai.ACTION_CONTEXT_id = pta.temp_object_action_id

),0))),ARCHIVED_INFORMATION) archivedinfo,

batchid,hdlbatchid,

XXINITIALIZING,XXPERCENTAGE_COMPLETE,XXTO_DO,XXPROCESSING,XXSTATUS,XXPROC_ARC_XML,XXPROC_BIP_JOBS,XXPROC_DEL_XML,XXPROC_DEL_ATTACHMENT,

XXPROC_DEL_UCM_DOR,XXCOMPLETE,XXLAPSED,XXREMAINING,XXOVERDUE,XXMINUTES,XXHOURS,XXCOMPLETE_WITH_ISSUES,XXNOT_STARTED,XXHIDEZERO,ldg_name,module_name,ARCHIVED_INFORMATION,BASE_TASK_NAME

from (

select flow_Instance_Id,ldg_id,base_flow_id,Task_Instance_Id,base_task_action_id,Flow_Name,Flow_Pattern,

(select prnt_chklst_inst_id from pay_checklist_instances ci1 where ci1.CHECKLIST_INSTANCE_id=(

select prnt_chklst_inst_id from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id)) as Cat_CK_Instance_id,

(select c.checklist_name from pay_checklist_instances ci1 , pay_checklists_vl c where c.checklist_id= ci1.Base_checklist_id and ci1.CHECKLIST_INSTANCE_id=(

select prnt_chklst_inst_id from pay_checklist_instances ci2 where ci2.CHECKLIST_INSTANCE_id=

(select prnt_chklst_inst_id from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id))) as Cat_CK_Instance_Name,

(select prnt_chklst_inst_id from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id) as Sub_Cat_CK_Instance_id,

(select c.checklist_name from pay_checklist_instances ci1 , pay_checklists_vl c where c.checklist_id= ci1.Base_checklist_id and ci1.CHECKLIST_INSTANCE_id=(

select prnt_chklst_inst_id from pay_checklist_instances ci where ci.flow_task_instance_id=Task_Instance_Id)) as Sub_Cat_CK_Instance_Name,

Checklist_Name, Task_Name, Task_Type,

Task_Param_Partial,Flow_Param_Full,Task_Param_Full,

Submitted_By,Submitted_On,Scheduled_Date,

action_1,Action_2,Action_3,

Action_1_id,Action_2_id,Action_3_id,batchid,hdlbatchid,

decode(Number_of_Objects_progress,null,(decode(batchid,null,decode(hdlbatchid,null,

(select sum(decode(pra.action_status,null,0,1)+decode(poa.action_status,null,0,1)+decode(pta.action_status,null,0,1))||'#'||

sum(decode(pra.action_status,'U',1,0)+decode(poa.action_status,'U',1,0)+decode(pta.action_status,'U',1,0))

from pay_payroll_rel_actions pra,

pay_payroll_actions ppa,

pay_object_actions poa,

pay_temp_object_actions pta

where requestid = ppa.pay_request_id

AND pra.source_action_id IS NULL

AND pra.RETRO_COMPONENT_ID IS NULL

AND ppa.action_type 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 ppa.payroll_action_id = pra.payroll_action_id (+)

and ppa.payroll_action_id = poa.payroll_action_id (+)

and ppa.payroll_action_id = pta.payroll_action_id (+)),

(select IMPORT_LINES_TOTAL_COUNT||'#'||(IMPORT_LINES_TOTAL_COUNT-loaded_count-error_count-skipped_count-corrected_count)

from HRC_DL_DATA_SETS hds

where hds.DATA_SET_ID = hdlbatchid

and batch_loader='X')),

(select sum(decode(pbl.batch_line_status,null,0,1))||'#'||

sum(decode(pbl.batch_line_status,'U',1,0))

from pay_batch_lines pbl

where pbl.batch_id=batchid

and batch_loader='Y'))

),Number_of_Objects_progress) Number_of_Objects_progress,

Object_Name,

Due_Date,Expected_Time_Left,taking_longer,Task_Status,

Export,

decode(task_status,'COMPLETE',last_update_Date,'SKIPPED',last_update_Date,null) Completion_Date,

last_update_Date,

decode(Status_code,'TO_DO',XXTO_DO,'PROCESSING',XXPROCESSING,XXSTATUS) Task_Phase,

Processing_flag,to_do_flag,Requires_attention_flag,Recently_completd_flag,status,

status_code,requestid,essrequestid,

summary_type,summary_breakdown,Payroll,Location,TRU,PSU,Extract_Type,Extract_Group,Extract_Area,RUN_SEQUENCE,TASK_CHK_INST_ID,TARGET_URI,

checklist_description,

task_description,

base_checklist_id,base_task_id,base_flow_task_id,report_category_id,default_flow_flag,skip_flow_flag,skip_task_flag,force_submit_flag,

(select distinct listagg( pta.action_name||':'||pta.task_action_id,'|') within group (order by pta.action_sequence)

from pay_action_matrix ptm,

pay_task_actions_vl pta

where pta.base_task_id = task_id

and action_allowed = 'Y'

and ptm.current_node_status= current_status_code

and ptm.succeeding_node_status= next_status_code

and ptm.available_action= pta.execution_mode

/* Avoid the Submit if the Pay Action Exists */

and ((task_type != 'MANUAL'

and requestid is not null

and available_action!='SUBMIT')

or (task_type != 'MANUAL'

and requestid is null

and available_action not in ('RERUN','ROLLBACK','MARK_FOR_RETRY'))

or (task_type = 'MANUAL'))

and not exists

(select null

from pay_action_matrix ptma

where ptma.current_node_status= current_status_code

and ptma.succeeding_node_status= next_status_code

and ptma.available_action= pta.execution_mode

and ptma.action_matrix_id > ptm.action_matrix_id

and ptma.available_action = ptm.available_action)) all_actions,

XXINITIALIZING,XXPERCENTAGE_COMPLETE,XXTO_DO,XXPROCESSING,XXSTATUS,XXPROC_ARC_XML,XXPROC_BIP_JOBS,XXPROC_DEL_XML,XXPROC_DEL_ATTACHMENT,

XXPROC_DEL_UCM_DOR,XXCOMPLETE,XXLAPSED,XXREMAINING,XXOVERDUE,XXMINUTES,XXHOURS,XXCOMPLETE_WITH_ISSUES,XXNOT_STARTED,XXHIDEZERO,ldg_name,module_name,ARCHIVED_INFORMATION,BASE_TASK_NAME

,ERROR_MESSAGE_COUNT

from (

select aaa.Flow_Instance_Id,aaa.ldg_id,aaa.base_flow_id,aaa.Task_Instance_Id,aaa.base_task_action_id,aaa.Flow_Name,aaa.Flow_Pattern,batch_loader,

aaa.Checklist_Name, aaa.Task_Name, aaa.Task_Type,

null Task_Param_Partial,

(select distinct listagg( fp.parameter_name||':'||

decode(fp.param_disp_type,'D',fnd_date.date_to_displaydate(to_Date(fpv.flow_param_value,'YYYY-MM-DD HH24:MI:SS')),

'T',fpv.flow_param_value,

'N',fpv.flow_param_value,

PAY_FLOW_COMMON_UTIL_PKG.convert_value (to_char(sysdate,'YYYYMMDDHHMISS'),sysdate,

aaa.lc_code, aaa.ldg_id,

decode(fp.param_disp_type,'LK','LOOKUP','LOV','VO','L','VO','ORA_VALUESET','VALUESET'),

fp.param_lookup,fpv.flow_param_value,'Y'))

,' | ') within group (order by param_sequence)

from pay_flow_param_values fpv,

pay_flow_parameters_vl fp

where aaa.flow_instance_id = fpv.flow_instance_id

and fp.flow_parameter_id = fpv.base_flow_parameter_id

and fpv.flow_param_value is not null

and fp.display_flag!='N'

) Flow_Param_Full,

(select distinct listagg( fp.parameter_name||':'||

decode(fp.param_disp_type,'D',to_char(to_Date(fpv.flow_task_param_value,'YYYY-MM-DD HH24:MI:SS'),'DD-MM-YYYY'),

'T',fpv.flow_task_param_value,

'N',fpv.flow_task_param_value,

PAY_FLOW_COMMON_UTIL_PKG.convert_value (to_char(sysdate,'YYYYMMDDHHMISS'),sysdate,

aaa.lc_code, aaa.ldg_id,

decode(fp.param_disp_type,'LK','LOOKUP','LOV','VO','L','VO','ORA_VALUESET','VALUESET'),

fp.param_lookup,fpv.flow_task_param_value,'Y'))

,' | ') within group (order by param_sequence)

from pay_flow_task_param_vals fpv,

pay_flow_task_parameters_vl fp

where aaa.task_instance_id = fpv.flow_task_instance_id

and fp.flow_task_param_id = fpv.base_flow_task_param_id

and fpv.flow_task_param_value is not null

and fp.display_flag!='N'

and fp.base_parameter_name != 'LEGISLATIVE_PARAMETERS'

) Task_Param_Full,

aaa.task_Submitted_By Submitted_By,aaa.task_Submitted_On Submitted_On,aaa.task_Scheduled_Date Scheduled_Date,

null Action_1,null Action_2,null Action_3,

null Action_1_id,null Action_2_id,null Action_3_id,

text.xxrecords Object_Name,

(select pcl.due_date

from PAY_CHECKLIST_INSTANCES pcl

where pcl.flow_task_instance_id=aaa.task_instance_id) Due_Date,

Expected_Time_Left,taking_longer,

task_status_code Task_Status,

(select to_char(max(prq.pay_request_id))

from pay_requests prq

where prq.flow_instance_id = aaa.flow_instance_id

and prq.flow_task_instance_id = aaa.task_instance_id

and (exists( select 1 from pay_payroll_actions ppa

where prq.pay_request_id = ppa.pay_request_id

and ppa.REPORT_CATEGORY_ID is not null

and batch_loader='N'

and ppa.action_status='C') or

exists(select 1 from fnd_attached_documents doc

where to_char(prq.pay_request_id) = doc.pk1_value

and doc.entity_name='FLOW_BI_OUTPUT'))) Export,

(select to_char(max(prq.pay_request_id))

from pay_payroll_actions ppa,

pay_requests prq

where prq.flow_instance_id = aaa.flow_instance_id

and batch_loader='N'

and prq.flow_task_instance_id = aaa.task_instance_id

and prq.pay_request_id = ppa.pay_request_id) requestid,

(select max(pbh.batch_id) batch_id

from PAY_FLOW_TASK_PARAM_VALS ppp,

pay_batch_headers pbh,

pay_flow_task_parameters pftp,

pay_task_parameters ptp

where ppp.flow_task_instance_id = aaa.task_instance_id

and batch_loader='Y'

and pftp.base_flow_task_param_id=ppp.base_flow_task_param_id

and pftp.base_task_parameter_id = ptp.base_task_parameter_id

and ptp.BASE_TASK_PARAMETER_NAME like '%BATCH%'

and ppp.flow_task_param_value = to_char(pbh.batch_id)) batchid,

(select max(pbh.data_set_id) batch_id

from pay_requests prq,

FUSION_ORA_ESS.REQUEST_HISTORY_VIEW essa,

FUSION_ORA_ESS.REQUEST_HISTORY_VIEW essb,

HRC_DL_DATA_SETS pbh

where prq.flow_instance_id = aaa.flow_instance_id

and batch_loader='X'

and essa.requestid= prq.call_id

and prq.call_type='ESS'

and essa.absparentid=essb.absparentid

and essb.requestid = pbh.request_id) hdlbatchid,

(select to_char(max(prq.pay_request_id))

from pay_requests prq

where prq.flow_instance_id = aaa.flow_instance_id

and prq.flow_task_instance_id = aaa.task_instance_id

and prq.call_type = 'ESS') essrequestid,

null Processing_flag,null to_do_flag,null Requires_attention_flag,null Recently_completd_flag,

aaa.Status,aaa.status_code,

aaa.task_last_update_Date last_update_Date,aaa.pt_task_type,aaa.base_task_id,aaa.base_task_id task_id,

(select max(decode(pt.base_task_name,'END_FLOW','ROLLEDBACK',npfti.status))

from PAY_FLOW_TASK_INTERACTNS pfti,

pay_flow_task_instances npfti,

pay_flow_tasks pft,

pay_tasks pt

where npfti.flow_instance_id = aaa.flow_instance_id

and npfti.base_flow_Task_id = pfti.to_flow_task_id

and pfti.from_flow_task_id = aaa.base_flow_task_id

and pft.flow_task_id= npfti.base_flow_task_id

and pt.task_id=pft.base_task_id

) next_status_code,

int_task_code current_status_code,

aaa.summary_type,aaa.summary_breakdown,aaa.Payroll,aaa.Location, aaa.TRU,aaa.PSU,aaa.Extract_Type,aaa.Extract_Group,aaa.Extract_Area ,aaa.RUN_SEQUENCE,aaa.TASK_CHK_INST_ID,aaa.TARGET_URI,

aaa.checklist_description,

aaa.task_description,

aaa.base_checklist_id,aaa.base_flow_task_id,aaa.report_category_id,aaa.default_flow_flag,aaa.skip_flow_flag,skip_task_flag,force_submit_flag,

text.XXINITIALIZING,text.XXPERCENTAGE_COMPLETE,text.XXTO_DO,text.XXPROCESSING,text.XXSTATUS,text.XXPROC_ARC_XML,text.XXPROC_BIP_JOBS,text.XXPROC_DEL_XML,

text.XXPROC_DEL_ATTACHMENT,text.XXPROC_DEL_UCM_DOR,text.XXCOMPLETE,text.XXLAPSED,text.XXREMAINING,text.XXOVERDUE,text.XXMINUTES,text.XXHOURS,text.XXCOMPLETE_WITH_ISSUES,text.XXNOT_STARTED,text.XXHIDEZERO,

aaa.action_allowed,aaa.ldg_name,module_name,aaa.ARCHIVED_INFORMATION,aaa.Number_of_Objects_progress,aaa.BASE_TASK_NAME, aaa.ERROR_MESSAGE_COUNT

from PAY_flow_status_basic_VL aaa,

(select max(decode(yyy.lookup_code,'INITIALIZING',yyy.meaning)) XXINITIALIZING,

max(decode(yyy.lookup_code,'PERCENTAGE_COMPLETE',yyy.meaning)) XXPERCENTAGE_COMPLETE,

max(decode(yyy.lookup_code,'TO_DO',yyy.meaning)) XXTO_DO,

max(decode(yyy.lookup_code,'PROCESSING',yyy.meaning)) XXPROCESSING,

max(decode(yyy.lookup_code,'STATUS',yyy.meaning)) XXSTATUS,

max(decode(yyy.lookup_code,'PROC_ARC_XML',yyy.meaning)) XXPROC_ARC_XML,

max(decode(yyy.lookup_code,'PROC_BIP_JOBS',yyy.meaning)) XXPROC_BIP_JOBS,

max(decode(yyy.lookup_code,'PROC_DEL_XML',yyy.meaning)) XXPROC_DEL_XML,

max(decode(yyy.lookup_code,'PROC_DEL_ATTACHMENT',yyy.meaning)) XXPROC_DEL_ATTACHMENT,

max(decode(yyy.lookup_code,'PROC_DEL_UCM_DOR',yyy.meaning)) XXPROC_DEL_UCM_DOR,

max(decode(yyy.lookup_code,'COMPLETE',yyy.meaning)) XXCOMPLETE,

max(decode(yyy.lookup_code,'LAPSED',yyy.meaning)) XXLAPSED,

max(decode(yyy.lookup_code,'REMAINING',yyy.meaning)) XXREMAINING,

max(decode(yyy.lookup_code,'OVERDUE',yyy.meaning)) XXOVERDUE,

max(decode(yyy.lookup_code,'MINUTES',yyy.meaning)) XXMINUTES,

max(decode(yyy.lookup_code,'HOURS',yyy.meaning)) XXHOURS,

max(decode(yyy.lookup_code,'COMPLETE_WITH_ISSUES',yyy.meaning)) XXCOMPLETE_WITH_ISSUES,

max(decode(yyy.lookup_code,'RECORDS',yyy.meaning)) XXRECORDS,

max(decode(yyy.lookup_code,'NOT_STARTED',yyy.meaning)) XXNOT_STARTED,

max((select max(decode(PARAMETER_VALUE,null,null,'Y')) from pay_action_parameters where parameter_name='FLOW_UX_SHOW_ZERO')) XXHIDEZERO

from hcm_lookups yyy

where yyy.lookup_type = 'ORA_PAY_UX_TASK_PHASE') text

)) XXX

) aaa) )