PAY_FLOW_STATUS_BASIC

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
FLOW_INSTANCE_ID BASE_FLOW_ID FLOW_NAME FLOW_PATTERN SUBMITTED_BY SUBMITTED_ON SCHEDULED_DATE FLOW_STATUS WITH_ERROR OVERDUE TAKING_LONGER EXPECTED_TIME_LEFT FLOW_STATUS_MEANING STATUS_CODE STATUS TASK_STATUS_CODE LDG_NAME LDG_ID LC_CODE SUMMARY_TYPE SUMMARY_BREAKDOWN PAYROLL LOCATION TRU PSU EXTRACT_TYPE EXTRACT_GROUP EXTRACT_AREA REPORT_CATEGORY_ID BASE_CHECKLIST_ID BASE_TASK_ID TASK_TYPE CHECKLIST_NAME TASK_NAME BASE_TASK_NAME PT_TASK_TYPE TASK_SUBMITTED_BY TASK_SUBMITTED_ON TASK_SCHEDULED_DATE TASK_LAST_UPDATE_DATE TASK_INSTANCE_ID BASE_TASK_ACTION_ID BASE_FLOW_TASK_ID FLOW_DESC SCHEDULE_END_DATE RECURRING_FLAG RECUR_SCH_FORMULA_ID RECUR_TIME_COMPONENT RECUR_SCH_TIME_DEF_ID RUN_SEQUENCE TARGET_URI TASK_CHK_INST_ID CHECKLIST_DESCRIPTION TASK_DESCRIPTION INT_TASK_CODE DEFAULT_FLOW_FLAG SKIP_FLOW_FLAG SKIP_TASK_FLAG FORCE_SUBMIT_FLAG BATCH_LOADER ACTION_ALLOWED MODULE_NAME ARCHIVED_INFORMATION NUMBER_OF_OBJECTS_PROGRESS ERROR_MESSAGE_COUNT

Name

FLOW_INSTANCE_ID

BASE_FLOW_ID

FLOW_NAME

FLOW_PATTERN

SUBMITTED_BY

SUBMITTED_ON

SCHEDULED_DATE

FLOW_STATUS

WITH_ERROR

OVERDUE

TAKING_LONGER

EXPECTED_TIME_LEFT

FLOW_STATUS_MEANING

STATUS_CODE

STATUS

TASK_STATUS_CODE

LDG_NAME

LDG_ID

LC_CODE

SUMMARY_TYPE

SUMMARY_BREAKDOWN

PAYROLL

LOCATION

TRU

PSU

EXTRACT_TYPE

EXTRACT_GROUP

EXTRACT_AREA

REPORT_CATEGORY_ID

BASE_CHECKLIST_ID

BASE_TASK_ID

TASK_TYPE

CHECKLIST_NAME

TASK_NAME

BASE_TASK_NAME

PT_TASK_TYPE

TASK_SUBMITTED_BY

TASK_SUBMITTED_ON

TASK_SCHEDULED_DATE

TASK_LAST_UPDATE_DATE

TASK_INSTANCE_ID

BASE_TASK_ACTION_ID

BASE_FLOW_TASK_ID

FLOW_DESC

SCHEDULE_END_DATE

RECURRING_FLAG

RECUR_SCH_FORMULA_ID

RECUR_TIME_COMPONENT

RECUR_SCH_TIME_DEF_ID

RUN_SEQUENCE

TARGET_URI

TASK_CHK_INST_ID

CHECKLIST_DESCRIPTION

TASK_DESCRIPTION

INT_TASK_CODE

DEFAULT_FLOW_FLAG

SKIP_FLOW_FLAG

SKIP_TASK_FLAG

FORCE_SUBMIT_FLAG

BATCH_LOADER

ACTION_ALLOWED

MODULE_NAME

ARCHIVED_INFORMATION

NUMBER_OF_OBJECTS_PROGRESS

ERROR_MESSAGE_COUNT

Query

SQL_Statement
select FLOW_INSTANCE_ID, BASE_FLOW_ID, FLOW_NAME, FLOW_PATTERN, SUBMITTED_BY, SUBMITTED_ON, SCHEDULED_DATE, FLOW_STATUS, WITH_ERROR, OVERDUE, TAKING_LONGER, EXPECTED_TIME_LEFT, FLOW_STATUS_MEANING, STATUS_CODE, STATUS, TASK_STATUS_CODE, LDG_NAME, LDG_ID, LC_CODE, SUMMARY_TYPE, SUMMARY_BREAKDOWN, PAYROLL, LOCATION, TRU, PSU, EXTRACT_TYPE, EXTRACT_GROUP, EXTRACT_AREA, REPORT_CATEGORY_ID, BASE_CHECKLIST_ID, BASE_TASK_ID, TASK_TYPE, CHECKLIST_NAME, TASK_NAME, BASE_TASK_NAME, PT_TASK_TYPE, TASK_SUBMITTED_BY, TASK_SUBMITTED_ON, TASK_SCHEDULED_DATE, TASK_LAST_UPDATE_DATE, TASK_INSTANCE_ID, BASE_TASK_ACTION_ID, BASE_FLOW_TASK_ID, FLOW_DESC, SCHEDULE_END_DATE, RECURRING_FLAG, RECUR_SCH_FORMULA_ID, RECUR_TIME_COMPONENT, RECUR_SCH_TIME_DEF_ID, RUN_SEQUENCE, TARGET_URI, TASK_CHK_INST_ID, CHECKLIST_DESCRIPTION, TASK_DESCRIPTION, INT_TASK_CODE, DEFAULT_FLOW_FLAG, SKIP_FLOW_FLAG, SKIP_TASK_FLAG, FORCE_SUBMIT_FLAG, BATCH_LOADER, ACTION_ALLOWED, MODULE_NAME, ARCHIVED_INFORMATION, TOTAL_COMPLETED\|\|'#'\|\| UNPROCESSED as NUMBER_OF_OBJECTS_PROGRESS, ERROR_MESSAGE_COUNT from ( select Flow_Instance_Id,base_Flow_id,Flow_Name Flow_Name,Flow_Pattern,Submitted_By,Submitted_On, Scheduled_Date,Flow_Status,With_Error,Overdue, (case when Expected_Time_Left < sysdate then 1 else null end) Taking_Longer, decode(status_code,'PROCESSING',Expected_Time_Left-systimestamp,null) Expected_Time_Left, FLOW_STATUS_MEANING,status_code, status, Task_Status_code, ldg_name,ldg_id,lc_code,summary_type,summary_breakdown,Payroll ,Location, TRU,PSU, Extract_Type,report_category_id,Extract_Group,Extract_Area, base_checklist_id,base_task_id, Task_Type,Checklist_Name, Task_Name,BASE_TASK_NAME,pt_task_type, task_Submitted_By,Task_Submitted_On,Task_Scheduled_Date,task_last_update_Date,task_instance_id,base_task_action_id,base_flow_task_id, flow_desc, SCHEDULE_END_DATE,RECURRING_FLAG,RECUR_SCH_FORMULA_ID,RECUR_TIME_COMPONENT,RECUR_SCH_TIME_DEF_ID,RUN_SEQUENCE,TARGET_URI,TASK_CHK_INST_ID, checklist_description, task_description,int_task_code,default_flow_flag,skip_flow_flag,skip_task_flag,force_submit_flag,PBL as BATCH_LOADER,action_allowed,module_name, 0 ARCHIVED_INFORMATION, null TOTAL_COMPLETED, null UNPROCESSED, null ERROR_MESSAGE_COUNT from ( select Flow_Instance_Id,Flow_Name Flow_Name,Flow_Pattern,Submitted_By,Submitted_On, Scheduled_Date,Flow_Status,With_Error,Overdue, decode(status_code,'PROCESSING', pay_statistics_utility_pkg.get_estimated_time (flowtaskid => BASE_FLOW_TASK_ID, status_code => status_code, taskintanceid =>task_instance_id) + (select max(lll.creation_date) from pay_requests lll where lll.FLOW_TASK_INSTANCE_ID = task_instance_id), null) Expected_Time_Left, FLOW_STATUS_MEANING,status_code, jjj.meaning status, Task_Status_code, ldg_name,ldg_id,lc_code, decode(nvl(ctx_summary_context,'STATUS'), 'FLOWSTATUS',flow_status_meaning, 'STATUS',jjj.meaning, 'PAYROLL',payroll, 'LOCATION',Location, 'TRU',TRU, 'PSU',PSU, 'EXTTYPE',decode(Extract_type,null,null,substrb(Extract_type,1,instrb(Extract_type,'[#]')-1)), 'EXTGRP',decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]',1,2)+3)), 'EXTAREA',decode(Extract_type,null,null,Extract_Area)) summary_type, nvl(ctx_summary_context,'STATUS') summary_breakdown,Payroll ,Location, TRU,PSU, decode(Extract_type,null,null,substrb(Extract_type,1,instrb(Extract_type,'[#]')-1)) Extract_Type, decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]')+3,instrb(Extract_type,'[#]',1,2)-instrb(Extract_type,'[#]',1,1)-3)) report_category_id, decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]',1,2)+3)) Extract_Group, decode(Extract_type,null,null,'[#][#]',null,Extract_Area) Extract_Area, base_checklist_id,base_task_id, Task_Type,Checklist_Name, Task_Name,BASE_TASK_NAME,pt_task_type, task_Submitted_By,Task_Submitted_On,Task_Scheduled_Date,task_last_update_Date,task_instance_id,base_task_action_id,base_flow_task_id, flow_desc, SCHEDULE_END_DATE,RECURRING_FLAG,RECUR_SCH_FORMULA_ID,RECUR_TIME_COMPONENT,RECUR_SCH_TIME_DEF_ID,RUN_SEQUENCE,TARGET_URI,TASK_CHK_INST_ID, checklist_description, task_description,int_task_code ,default_flow_flag,skip_flow_flag,skip_task_flag,force_submit_flag,base_flow_id,PBL,action_allowed,MODULE_NAME,ctx_submitted_from,ctx_completed_from,ctx_summary_context from ( select distinct pft.base_flow_task_name, pfi.flow_instance_id Flow_Instance_Id,pfi.instance_name Flow_Name,pf.flow_name Flow_Pattern, pu.username Submitted_By,pfi.creation_date Submitted_On,pfi.scheduled_date Scheduled_Date, tpu.username task_Submitted_By, pfti.creation_date Task_Submitted_On, pfti.scheduled_date Task_Scheduled_Date, decode(pfi.status,'COMPLETED','COMPLETE','CREATED','NOT_STARTED',pfi.status) Flow_Status, (select decode(count(distinct ppti.base_flow_task_id),0,to_number(null),count(distinct ppti.base_flow_task_id)) from pay_flow_task_instances ppti where ppti.flow_instance_id = pfi.flow_instance_id and ppti.status in ('ERRORED','ESS_CHILD_JOB_SUB_ERROR','ESS_MT_SERV_NOT_FOUND','ESS_PARENT_JOB_SUB_ERROR','ESS_RT_SERV_NOT_FOUND','FUNCTIONAL_ERROR') and ppti.flow_task_instance_id=pfti.flow_task_instance_id) With_Error, (select 1 from PAY_CHECKLIST_INSTANCES pcl where pcl.flow_task_instance_id=pfti.flow_task_instance_id and sysdate>pcl.due_date and pfti.status not in ('COMPLETED','SKIPPED')) Overdue, ldg.name ldg_name, ldg.legislative_data_group_id ldg_id, ldg.legislation_code lc_code, decode(pfti.status,'COMPLETED','RECENTLY_COMPLETED','ERRORED','REQUIRES_ATTENTION', 'ESS_CHILD_JOB_COMPLETED','PROCESSING','ESS_CHILD_JOB_NOT_FOUND','REQUIRES_ATTENTION', 'ESS_CHILD_JOB_SUBMITTED','PROCESSING','ESS_CHILD_JOB_SUB_ERROR','REQUIRES_ATTENTION', 'ESS_MT_SERV_NOT_FOUND','REQUIRES_ATTENTION', 'ESS_PARENT_JOB_SUBMITTED','PROCESSING','ESS_PARENT_JOB_SUB_ERROR','REQUIRES_ATTENTION', 'ESS_RT_SERV_NOT_FOUND','REQUIRES_ATTENTION', 'EXECUTION_STARTED',decode(pc.checklist_type,'MAN_CHECKLIST','TO_DO','PROCESSING'), 'FUNCTIONAL_ERROR','REQUIRES_ATTENTION', 'IN_PROGRESS',decode(pc.checklist_type,'MAN_CHECKLIST','TO_DO','PROCESSING'), 'IN_PROGRESS_IDLE','TO_DO','MARKED_FOR_RETRY','REQUIRES_ATTENTION','NOT_STARTED','NOT_STARTED', 'PUBLISHED',decode(pc.checklist_type,'MAN_CHECKLIST','TO_DO','PROCESSING'), 'ROLLEDBACK','REQUIRES_ATTENTION', 'SCHEDULED','NOT_STARTED','SKIPPED','RECENTLY_COMPLETED', pfti.status) status_code, decode(pfti.status,'COMPLETED','COMPLETE', 'ERRORED','COMPLETE_WITH_ISSUES', 'ESS_CHILD_JOB_COMPLETED','IN_PROCESSING', 'ESS_CHILD_JOB_NOT_FOUND','COMPLETE_WITH_ISSUES', 'ESS_CHILD_JOB_SUBMITTED','IN_PROCESSING', 'ESS_CHILD_JOB_SUB_ERROR','COMPLETE_WITH_ISSUES', 'ESS_MT_SERV_NOT_FOUND','COMPLETE_WITH_ISSUES', 'ESS_PARENT_JOB_SUBMITTED','INITIALIZING', 'ESS_PARENT_JOB_SUB_ERROR','COMPLETE_WITH_ISSUES', 'ESS_RT_SERV_NOT_FOUND','COMPLETE_WITH_ISSUES', 'EXECUTION_STARTED','IN_PROGRESS', 'FUNCTIONAL_ERROR','COMPLETE_WITH_ISSUES', 'IN_PROGRESS',decode(pc.checklist_type,'MAN_CHECKLIST','IN_PROGRESS','IN_PROCESSING'), 'IN_PROGRESS_IDLE','IN_PROGRESS', 'MARKED_FOR_RETRY','MARKED_FOR_RETRY', 'NOT_STARTED','NOT_STARTED', 'PUBLISHED','INITIALIZING', 'ROLLEDBACK','ROLLED_BACK', 'SCHEDULED','SCHEDULED', 'SKIPPED','SKIPPED', pfti.status) Task_Status_code, decode(pfi.status,'COMPLETED','N','SKIPPED','N','Y') skip_flow_flag, decode(pfti.status,'COMPLETED','N','SKIPPED','N','Y') skip_task_flag, 'N' force_submit_flag, /* This needs to stored in the flow instance attributes / (select max(ppp.payroll_name) from pay_all_payrolls_f ppp where ppp.payroll_id = pfi.attribute1 and pfi.attribute1 is not null) Payroll, / This needs to stored in the flow instance attributes or cached/ null Location,null TRU,null PSU, / This needs to stored in the flow instance attributes or cached/ (select /+ ORDERED / max(extt.TYPE_NAME)\|\|'[#]'\|\|max(prg.report_category_id)\|\|'[#]'\|\|max((select max(hhh.meaning) from hcm_lookups hhh where hhh.lookup_type='ORA_PER_EXT_EXTRACT_CATEGORY' and hhh.lookup_code=ext.attribute27)) from PAY_FLOW_TASK_PARAM_VALS fpv, PAY_FLOW_TASK_PARAMETERS_VL fp, PAY_REPORT_CATEGORIES prg, PAY_REPORT_DEFINITIONS prd, PER_EXT_DEFINITIONS_VL ext, PER_EXT_TYPES_VL extt where pfti.flow_task_instance_id = fpv.flow_task_instance_id and fpv.flow_task_param_value is not null and fp.flow_task_param_id = fpv.base_flow_task_param_id and fp.base_parameter_name='REPORT_CATEGORY' and fpv.flow_task_param_value = prg.report_category_id and prg.REPORT_GROUP_ID=prd.REPORT_GROUP_ID and prd.EXT_DEFINITION_ID=ext.EXT_DEFINITION_ID and extt.EXT_TYPE_CODE=ext.EXT_TYPE_CODE) Extract_Type, null Extract_Group,pt.task_name Extract_Area, pfis.meaning flow_status_meaning, pc.base_checklist_id, pt.base_task_id, decode(pc.checklist_type,'MAN_CHECKLIST','MANUAL','AUTOMATIC') Task_Type,pc.checklist_name Checklist_Name, pt.task_name Task_Name,pt.BASE_TASK_NAME,pt.task_type pt_task_type,pfti.last_update_Date task_last_update_Date, pfti.flow_task_instance_id task_instance_id,pfti.base_task_action_id,pft.base_flow_task_id, pf.description flow_desc, pfi.SCHEDULE_END_DATE,pfi.RECURRING_FLAG,pfi.RECUR_SCH_FORMULA_ID,pfi.RECUR_TIME_COMPONENT,pfi.RECUR_SCH_TIME_DEF_ID,to_char(pfi.creation_date,'YYYYMMDDHHMISS')+pc.RUN_SEQUENCE RUN_SEQUENCE, decode(trim(pc.dest_UI_URL),null,decode(trim(pft.dest_ui_url),null,pt.dest_ui_url,pft.dest_ui_url),pc.dest_UI_URL) as TARGET_URI, pc.description checklist_description, pt.description task_description, pfti.status int_task_code, pf.default_flow_flag, pf.base_flow_id, pci.CHECKLIST_INSTANCE_ID TASK_CHK_INST_ID, pci.owner_id,pci.owner_type,session_contexts.user_id, decode(pci.owner_id,session_contexts.user_id,'Y',decode(pu.username,session_contexts.username,'Y','N')) action_allowed, decode(pt.base_task_name,'ORA_PAY_HDL_LOAD','X','CREATE_BATCH','Y','ENTER_BATCH','Y','PURGE_BATCH','Y','TRANSFER_BATCH','Y','LOAD_BATCH','Y','CREATE_BATCH_FROM_FILE','Y','ROLLBACK_PBL_BATCH','Y','FILE_UPLOAD_BATCH','Y','N') PBL, MODULE_NAME,ctx_submitted_from,ctx_completed_from,ctx_summary_context from pay_flow_instances pfi, pay_flows_vl pf, per_legislative_data_groups_vl ldg, per_users pu, hcm_lookups pfis, pay_flow_task_instances pfti, pay_flow_tasks pft, per_users tpu, PAY_CHECKLISTS_vl pc, pay_tasks_vl pt, PAY_CHECKLIST_INSTANCES pci, PAY_CHECKLISTS pfcat, (select max(decode(fup.PREFERENCE_NAME,'DEF_RECENTLY_COMP_FROM',decode(fup.preference_value,'LAST_24_HOURS',1,'LAST_WEEK',7,'LAST_MONTH',sysdate-add_months(sysdate,-1),'LAST_3_MONTHS',sysdate-add_months(sysdate,-3),'LAST_6_MONTHS',sysdate-add_months(sysdate,-6),'LAST_YEAR',sysdate-add_months(sysdate,-12),'FOREVER',999999,null),null)) ctx_completed_from, max(decode(fup.PREFERENCE_NAME,'DEF_SEARCH_BACK_FROM',decode(fup.preference_value,'LAST_24_HOURS',1,'LAST_WEEK',7,'LAST_MONTH',sysdate-add_months(sysdate,-1),'LAST_3_MONTHS',sysdate-add_months(sysdate,-3),'LAST_6_MONTHS',sysdate-add_months(sysdate,-6),'LAST_YEAR',sysdate-add_months(sysdate,-12),'FOREVER',999999,null),null)) ctx_submitted_from, max(decode(fup.PREFERENCE_NAME,'DEF_SUMMARY_TYPE',fup.preference_value,null)) ctx_summary_context, max(FND_GLOBAL.get_session_attribute('HCM_USER_ID')) user_id, max(fup.USER_NAME) username, max(decode(fup.PREFERENCE_NAME,'USER_IN_PREF',fup.preference_value,null)) ctx_username, max(decode(fup.PREFERENCE_NAME,'USER_IN_PREF',decode(fup.preference_value,null,null,(select max(ppp.user_id) from per_users ppp where ppp.username=fup.preference_value)),null)) ctx_user_id, max(decode(fup.PREFERENCE_NAME,'CATEGORY',fup.preference_value,null)) ctx_flow_category, max(decode(fup.PREFERENCE_NAME,'SUB_CATEGORY',fup.preference_value,null)) ctx_flow_sub_category, max(decode(fup.PREFERENCE_NAME,'FLOW_PATTERN',fup.preference_value,null)) ctx_flow_pattern, max(decode(fup.PREFERENCE_NAME,'DEFAULT_FLOW',fup.preference_value,null)) ctx_default_flow, max(decode(fup.PREFERENCE_NAME,'LDG',fup.preference_value,null)) ctx_ldg, fup.MODULE_NAME from FND_USER_PREFERENCES fup where fup.user_name=fnd_session_ns.get_namespace_attr(FND_GLOBAL.session_id,'FND$SECURITY', 'USER_NAME') and fup.MODULE_NAME like 'ORA_DX%' group by fup.MODULE_NAME) session_contexts where pfi.base_flow_id = pf.base_flow_id and pfcat.base_flow_id = pfi.base_flow_id AND pf.default_flow_flag IN('Y','N') and (PAY_FLOW_COMMON_UTIL_PKG.check_security(pf.base_flow_id,'PAY_FLOWS','BASE_FLOW_ID','PAY_VIEW_PAYROLL_FLOW_DATA') ='Y' and (PAY_FLOW_COMMON_UTIL_PKG.check_security(pfi.LEGISLATIVE_DATA_GROUP_ID,'PER_LEGISLATIVE_DATA_GROUPS','LEGISLATIVE_DATA_GROUP_ID','PER_CHOOSE_LEGISLATIVE_DATA_GROUP_DATA') ='Y' OR pfi.LEGISLATIVE_DATA_GROUP_ID is null)) and nvl(pfcat.category_type,'#')=nvl(session_contexts.ctx_flow_category, nvl(pfcat.category_type,'#')) and nvl(pfcat.sub_category_type,'#')=nvl(session_contexts.ctx_flow_sub_category, nvl(pfcat.sub_category_type,'#')) and ldg.legislative_data_group_id (+) = pfi.legislative_data_group_id and pu.user_id = pfi.instantiated_by and pfis.lookup_type='PAY_FLOW_INSTANCE_STATUS' and pfis.lookup_code=decode(pfi.status,'CREATED','NOT_STARTED',pfi.status) and pfi.creation_date >= (sysdate-nvl(greatest(session_contexts.ctx_submitted_from,session_contexts.ctx_completed_from),'30')) and ((pfi.instantiated_by=session_contexts.ctx_user_id or session_contexts.ctx_user_id is null) or (pci.owner_id=session_contexts.ctx_user_id or session_contexts.ctx_user_id is null)) and pf.base_flow_name=nvl(session_contexts.ctx_flow_pattern,pf.base_flow_name) and pfti.flow_instance_id = pfi.flow_instance_id and pft.base_flow_task_id = pfti.base_flow_task_id and pft.basE_task_id=pt.base_task_id and pf.default_flow_flag=nvl(session_contexts.ctx_default_flow,pf.default_flow_flag) and pci.flow_task_instance_id (+) = pfti.flow_task_instance_id and ( (pfti.status in ('ESS_PARENT_JOB_SUBMITTED','ESS_CHILD_JOB_COMPLETED','ESS_CHILD_JOB_SUBMITTED','IN_PROGRESS','EXECUTION_STARTED','IN_PROGRESS_IDLE','NOT_STARTED','PUBLISHED','SCHEDULED') and pfti.creation_date > ( SYSDATE - 7 )) or ( not exists (select null from PAY_STATS_FLOW_ACTIONS bbb where pfti.flow_task_instance_id = bbb.FLOW_TASK_INSTANCE_ID and bbb.TASK_STATUS not in ('IN_PROGRESS','EXECUTION_STARTED','NOT_STARTED'))and pc.checklist_type !='MAN_CHECKLIST')) and pc.base_checklist_id (+) = pci.base_checklist_id and tpu.user_id (+) = decode(pci.owner_type,null,pci.owner_id,'USER',pci.owner_id,-1) and ((nvl(session_contexts.ctx_summary_context,'STATUS')='FLOWSTATUS' and pt.base_task_name = 'START_FLOW') or (nvl(session_contexts.ctx_summary_context,'STATUS')!='FLOWSTATUS' and pt.base_task_name not in ('START_FLOW','END_FLOW'))) and nvl(pfi.legislative_data_group_id,-9999)=to_number(nvl(session_contexts.ctx_ldg, nvl(pfi.legislative_data_group_id,-9999))) and (pc.legislative_data_group_id = ldg.legislative_data_group_id or pc.legislation_code = ldg.legislation_code or (pc.legislative_data_group_id is null and pc.legislation_code is null)) and (pft.legislative_data_group_id = ldg.legislative_data_group_id or pft.legislation_code = ldg.legislation_code or (pft.legislative_data_group_id is null and pft.legislation_code is null)) and (pf.legislative_data_group_id = ldg.legislative_data_group_id or pf.legislation_code = ldg.legislation_code or (pf.legislative_data_group_id is null and pf.legislation_code is null)) and (pt.legislative_data_group_id = ldg.legislative_data_group_id or pt.legislation_code = ldg.legislation_code or (pt.legislative_data_group_id is null and pt.legislation_code is null)) ) ddd, hcm_lookups jjj where jjj.lookup_type ='ORA_PAY_UX_TASK_STATUS' and jjj.lookup_code(+) =ddd.status_code) aaa where ((Submitted_On>= (sysdate-nvl(ctx_submitted_from,'30')) and Flow_Status!='COMPLETE') or (Submitted_On>= (sysdate-nvl(ctx_completed_from,'7')) and Flow_Status='COMPLETE')) and (nvl(ctx_summary_context,'STATUS')='FLOWSTATUS' or base_checklist_id is not null) union all select FLOW_INSTANCE_ID, BASE_FLOW_ID, FLOW_NAME, FLOW_PATTERN, SUBMITTED_BY, SUBMITTED_ON, SCHEDULED_DATE, FLOW_STATUS, WITH_ERROR, null OVERDUE, null TAKING_LONGER, EXPECTED_TIME_LEFT, FLOW_STATUS_MEANING, STATUS_CODE, jjj.meaning STATUS, TASK_STATUS_CODE, LDG_NAME, LDG_ID, LC_CODE, decode(nvl(ctx_summary_context,'STATUS'), 'FLOWSTATUS',flow_status_meaning, 'STATUS',jjj.meaning, 'PAYROLL',payroll, 'LOCATION',Location, 'TRU',TRU, 'PSU',PSU, 'EXTTYPE',decode(Extract_type,null,null,substrb(Extract_type,1,instrb(Extract_type,'[#]')-1)), 'EXTGRP',decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]',1,2)+3)), 'EXTAREA',decode(Extract_type,null,null,Extract_Area)) SUMMARY_TYPE, SUMMARY_BREAKDOWN, PAYROLL, LOCATION, TRU, PSU, decode(Extract_type,null,null,substrb(Extract_type,1,instrb(Extract_type,'[#]')-1)) Extract_Type, decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]')+3,instrb(Extract_type,'[#]',1,2)-instrb(Extract_type,'[#]',1,1)-3)) report_category_id, decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]',1,2)+3)) Extract_Group, decode(Extract_type,null,null,'[#][#]',null,Extract_Area) Extract_Area, BASE_CHECKLIST_ID, BASE_TASK_ID, TASK_TYPE, CHECKLIST_NAME, TASK_NAME, BASE_TASK_NAME, PT_TASK_TYPE, TASK_SUBMITTED_BY, TASK_SUBMITTED_ON, TASK_SCHEDULED_DATE, TASK_LAST_UPDATE_DATE, TASK_INSTANCE_ID, BASE_TASK_ACTION_ID, BASE_FLOW_TASK_ID, FLOW_DESC, SCHEDULE_END_DATE, RECURRING_FLAG, RECUR_SCH_FORMULA_ID, RECUR_TIME_COMPONENT, RECUR_SCH_TIME_DEF_ID, RUN_SEQUENCE, TARGET_URI, TASK_CHK_INST_ID, CHECKLIST_DESCRIPTION, TASK_DESCRIPTION, INT_TASK_CODE, DEFAULT_FLOW_FLAG, SKIP_FLOW_FLAG, SKIP_TASK_FLAG, FORCE_SUBMIT_FLAG, BATCH_LOADER, ACTION_ALLOWED, MODULE_NAME, ARCHIVED_INFORMATION, TOTAL_COMPLETED, UNPROCESSED, ERROR_MESSAGE_COUNT from( SELECT LDG_ID,ldg.name LDG_NAME, pf.base_flow_id,sfa.FLOW_NAME, FLOW_PATTERN_ID,pf.flow_name FLOW_PATTERN,pf.DESCRIPTION FLOW_DESC, flowStatus.meaning FLOW_STATUS_MEANING, (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, pfi.LEGISLATION_CODE, sfa.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 sfa.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,FLOW_SUBMITTED_ON, FLOW_SUBMITTED_BY,pfi.SCHEDULED_DATE SCHEDULED_DATE,pfi.SCHEDULE_END_DATE SCHEDULE_END_DATE, sfa.TASK_ID BASE_TASK_ID,pt.task_name TASK_NAME,pt.BASE_TASK_NAME,sfa.FLOW_TASK_ID BASE_FLOW_TASK_ID,sfa.CHECKLIST_ID BASE_CHECKLIST_ID,decode(pt.TASK_TYPE,'MANUAL_TASK','MANUAL','AUTOMATIC') TASK_TYPE, pct.CHECKLIST_NAME,pct.DESCRIPTION CHECKLIST_DESCRIPTION, pt.DESCRIPTION TASK_DESCRIPTION, decode(PROCESS_STATUS_CODE,'REQUIRES_ATTENTION','N','RECENTLY_COMPLETED','N','Y') ACTIVE_FLAG, TASK_SUBMITTED_ON SUBMITTED_ON,TASK_SUBMITTED_BY,pfti.scheduled_date TASK_SCHEDULED_DATE, pcit.due_date DUE_DATE,decode(PROCESS_STATUS_CODE,'RECENTLY_COMPLETED',pfti.last_update_Date,'SKIPPED',pfti.last_update_Date,null) COMPLETION_DATE, sfa.FLOW_INSTANCE_ID, sfa.FLOW_TASK_INSTANCE_ID TASK_INSTANCE_ID,pcit.checklist_instance_id TASK_CHK_INST_ID, to_char(pfi.creation_date,'YYYYMMDDHHMISS')+pct.RUN_SEQUENCE RUN_SEQUENCE, pfi.instantiated_by SUBMITTED_BY, decode(pfi.status,'COMPLETED','COMPLETE','CREATED','NOT_STARTED',pfi.status) Flow_Status, (select decode(count(distinct ppti.base_flow_task_id),0,to_number(null),count(distinct ppti.base_flow_task_id)) from pay_flow_task_instances ppti where ppti.flow_instance_id = pfi.flow_instance_id and ppti.status in ('ERRORED','ESS_CHILD_JOB_SUB_ERROR','ESS_MT_SERV_NOT_FOUND','ESS_PARENT_JOB_SUB_ERROR','ESS_RT_SERV_NOT_FOUND','FUNCTIONAL_ERROR') and ppti.flow_task_instance_id=pfti.flow_task_instance_id) With_Error, PROCESS_STATUS_CODE status_code, decode(TASK_STATUS,'COMPLETED','COMPLETE', 'ERRORED','COMPLETE_WITH_ISSUES', 'ESS_CHILD_JOB_COMPLETED','IN_PROCESSING', 'ESS_CHILD_JOB_NOT_FOUND','COMPLETE_WITH_ISSUES', 'ESS_CHILD_JOB_SUBMITTED','IN_PROCESSING', 'ESS_CHILD_JOB_SUB_ERROR','COMPLETE_WITH_ISSUES', 'ESS_MT_SERV_NOT_FOUND','COMPLETE_WITH_ISSUES', 'ESS_PARENT_JOB_SUBMITTED','INITIALIZING', 'ESS_PARENT_JOB_SUB_ERROR','COMPLETE_WITH_ISSUES', 'ESS_RT_SERV_NOT_FOUND','COMPLETE_WITH_ISSUES', 'EXECUTION_STARTED','IN_PROGRESS', 'FUNCTIONAL_ERROR','COMPLETE_WITH_ISSUES', 'IN_PROGRESS',decode(pct.checklist_type,'MAN_CHECKLIST','IN_PROGRESS','IN_PROCESSING'), 'IN_PROGRESS_IDLE','IN_PROGRESS', 'MARKED_FOR_RETRY','MARKED_FOR_RETRY', 'NOT_STARTED','NOT_STARTED', 'PUBLISHED','INITIALIZING', 'ROLLEDBACK','ROLLED_BACK', 'SCHEDULED','SCHEDULED', 'SKIPPED','SKIPPED', TASK_STATUS) TASK_STATUS_CODE, PROCESS_STATUS_CODE, PROCESS_DATE, sfa.PAYROLL_ID,payroll.payroll_name PAYROLL, sfa.CONSOLIDATION_SET_ID, BATCH_NAME, FILE_NAME, PSU_ID,null PSU, TRU_ID,null TRU, LOCATION_ID,null LOCATION, LEGAL_EMPLOYER_ID, ORGANIZATION_ID, DATA_SET_ID, null EXPECTED_TIME_LEFT, BATCH_ID,BATCH_ID HDL_BATCH_ID, pf.DEFAULT_FLOW_FLAG,decode(pfi.status,'COMPLETED','N','SKIPPED','N','Y') SKIP_FLOW_FLAG, 'N' FORCE_SUBMIT_FLAG,pf.DEFAULT_FLOW_FLAG SUBMIT_SIMILAR_FLAG, decode(pfti.status,'COMPLETED','N','SKIPPED','N','Y') SKIP_TASK_FLAG,decode(Task_status,'COMPLETED','Y','N') EXPORT, (select /+ ORDERED */ max(extt.TYPE_NAME)\|\|'[#]'\|\|max(prg.report_category_id)\|\|'[#]'\|\|max((select max(hhh.meaning) from hcm_lookups hhh where hhh.lookup_type='ORA_PER_EXT_EXTRACT_CATEGORY' and hhh.lookup_code=ext.attribute27)) from PAY_FLOW_TASK_PARAM_VALS fpv, PAY_FLOW_TASK_PARAMETERS_VL fp, PAY_REPORT_CATEGORIES prg, PAY_REPORT_DEFINITIONS prd, PER_EXT_DEFINITIONS_VL ext, PER_EXT_TYPES_VL extt where pfti.flow_task_instance_id = fpv.flow_task_instance_id and fpv.flow_task_param_value is not null and fp.flow_task_param_id = fpv.base_flow_task_param_id and fp.base_parameter_name='REPORT_CATEGORY' and fpv.flow_task_param_value = prg.report_category_id and prg.REPORT_GROUP_ID=prd.REPORT_GROUP_ID and prd.EXT_DEFINITION_ID=ext.EXT_DEFINITION_ID and extt.EXT_TYPE_CODE=ext.EXT_TYPE_CODE) EXTRACT_TYPE,null EXTRACT_GROUP, pt.task_name EXTRACT_AREA,null REPORT_CATEGORY_ID, nvl(ctx_summary_context,'STATUS') SUMMARY_BREAKDOWN, 'Y' action_allowed, pfti.status int_task_code, pfti.base_task_action_id, ldg.legislation_code lc_code, pt.task_type pt_task_type, pfti.last_update_Date task_last_update_Date, pfti.creation_date Task_Submitted_On, decode(trim(pct.dest_UI_URL),null,decode(trim(pft.dest_ui_url),null,pt.dest_ui_url,pft.dest_ui_url),pct.dest_UI_URL) TARGET_URI, pfi.RECUR_SCH_TIME_DEF_ID, pfi.RECURRING_FLAG,pfi.RECUR_SCH_FORMULA_ID,pfi.RECUR_TIME_COMPONENT, decode(pt.base_task_name,'ORA_PAY_HDL_LOAD','X','CREATE_BATCH','Y','ENTER_BATCH','Y','PURGE_BATCH','Y','TRANSFER_BATCH','Y','LOAD_BATCH','Y','CREATE_BATCH_FROM_FILE','Y','ROLLBACK_PBL_BATCH','Y','FILE_UPLOAD_BATCH','Y','N') BATCH_LOADER, MODULE_NAME, ctx_submitted_from,ctx_completed_from,ctx_summary_context, ARCHIVED_INFORMATION, TOTAL_COMPLETED, UNPROCESSED, ERROR_MESSAGE_COUNT FROM (SELECT LDG_ID , FLOW_NAME , FLOW_PATTERN_ID , FLOW_TATUS , FLOW_SUBMITTED_ON , FLOW_SUBMITTED_BY , TASK_ID , FLOW_TASK_ID , CHECKLIST_ID , TASK_STATUS , TASK_SUBMITTED_ON , TASK_SUBMITTED_BY , FLOW_INSTANCE_ID , FLOW_TASK_INSTANCE_ID , PROCESS_STATUS_CODE , PROCESS_DATE , PAYROLL_ID , CONSOLIDATION_SET_ID , BATCH_ID , BATCH_NAME , FILE_NAME , PSU_ID , TRU_ID , LOCATION_ID , LEGAL_EMPLOYER_ID , ORGANIZATION_ID , DATA_SET_ID , ARCHIVED_INFORMATION , TOTAL_COMPLETED, UNPROCESSED, ERROR_MESSAGE_COUNT FROM fusion.PAY_STATS_FLOW_ACTIONS sfa WHERE TASK_STATUS NOT in ('ESS_PARENT_JOB_SUBMITTED','ESS_CHILD_JOB_COMPLETED','ESS_CHILD_JOB_SUBMITTED','IN_PROGRESS','EXECUTION_STARTED','IN_PROGRESS_IDLE','NOT_STARTED','PUBLISHED','SCHEDULED') and (sfa.active_flag = 'Y' or (not exists (select null from PAY_STATS_FLOW_ACTIONS aaa where aaa.flow_task_instance_id=sfa.flow_task_instance_id and aaa.active_flag = 'Y') and sfa.TI_REQUEST_ID\|\|sfa.REQUEST_ID = (select max(aaa.TI_REQUEST_ID\|\|aaa.REQUEST_ID) from PAY_STATS_FLOW_ACTIONS aaa where aaa.flow_task_instance_id=sfa.flow_task_instance_id and nvl(aaa.active_flag,'N') != 'Y' ))) ) sfa , per_legislative_data_groups_vl ldg, pay_flows_vl pf, pay_flow_instances pfi, hcm_lookups flowStatus, pay_tasks_vl pt, PAY_CHECKLISTS_VL pct , pay_flow_task_instances pfti, PAY_CHECKLIST_INSTANCES pcit, pay_flow_tasks_vl pft, pay_all_payrolls_f payroll, (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 ,(select max(decode(fup.PREFERENCE_NAME,'DEF_RECENTLY_COMP_FROM',decode(fup.preference_value,'LAST_24_HOURS',1,'LAST_WEEK',7,'LAST_MONTH',sysdate-add_months(sysdate,-1),'LAST_3_MONTHS',sysdate-add_months(sysdate,-3),'LAST_6_MONTHS',sysdate-add_months(sysdate,-6),'LAST_YEAR',sysdate-add_months(sysdate,-12),'FOREVER',999999,null),null)) ctx_completed_from, max(decode(fup.PREFERENCE_NAME,'DEF_SEARCH_BACK_FROM',decode(fup.preference_value,'LAST_24_HOURS',1,'LAST_WEEK',7,'LAST_MONTH',sysdate-add_months(sysdate,-1),'LAST_3_MONTHS',sysdate-add_months(sysdate,-3),'LAST_6_MONTHS',sysdate-add_months(sysdate,-6),'LAST_YEAR',sysdate-add_months(sysdate,-12),'FOREVER',999999,null),null)) ctx_submitted_from, max(decode(fup.PREFERENCE_NAME,'DEF_SUMMARY_TYPE',fup.preference_value,null)) ctx_summary_context, max(FND_GLOBAL.get_session_attribute('HCM_USER_ID')) user_id, max(fup.USER_NAME) username, max(decode(fup.PREFERENCE_NAME,'USER_IN_PREF',fup.preference_value,null)) ctx_username, max(decode(fup.PREFERENCE_NAME,'USER_IN_PREF',decode(fup.preference_value,null,null,(select max(ppp.user_id) from per_users ppp where ppp.username=fup.preference_value)),null)) ctx_user_id, max(decode(fup.PREFERENCE_NAME,'CATEGORY',fup.preference_value,null)) ctx_flow_category, max(decode(fup.PREFERENCE_NAME,'SUB_CATEGORY',fup.preference_value,null)) ctx_flow_sub_category, max(decode(fup.PREFERENCE_NAME,'FLOW_PATTERN',fup.preference_value,null)) ctx_flow_pattern, max(decode(fup.PREFERENCE_NAME,'DEFAULT_FLOW',fup.preference_value,null)) ctx_default_flow, max(decode(fup.PREFERENCE_NAME,'LDG',fup.preference_value,null)) ctx_ldg, fup.MODULE_NAME from FND_USER_PREFERENCES fup where fup.user_name=fnd_session_ns.get_namespace_attr(FND_GLOBAL.session_id,'FND$SECURITY', 'USER_NAME') and fup.MODULE_NAME like 'ORA_DX%' group by fup.MODULE_NAME) session_contexts where sfa.LDG_ID=ldg.legislative_data_group_id(+) and sfa.FLOW_PATTERN_ID=pf.flow_id and sfa.flow_instance_id= pfi.flow_instance_id and flowStatus.lookup_type='PAY_FLOW_INSTANCE_STATUS' and flowStatus.lookup_code=pfi.STATUS and sfa.TASK_ID=pt.TASK_ID and sfa.CHECKLIST_ID=pct.CHECKLIST_ID and sfa.FLOW_TASK_INSTANCE_ID= pfti.FLOW_TASK_INSTANCE_ID and sfa.FLOW_TASK_INSTANCE_ID=pcit.FLOW_TASK_INSTANCE_ID and sfa.FLOW_TASK_ID=pft.flow_task_id and sfa.payroll_id =payroll.payroll_id(+) and nvl(sfa.process_date,sfa.task_submitted_on) between payroll.effective_start_date(+) and payroll.effective_end_date(+) and decode(pt.BASE_TASK_NAME,'QUICK_PAY_SIMPLIFIED','AUTOMATIC', pt.Task_Type)!='MANUAL_TASK' and (PAY_FLOW_COMMON_UTIL_PKG.check_security(pf.base_flow_id,'PAY_FLOWS','BASE_FLOW_ID','PAY_VIEW_PAYROLL_FLOW_DATA') ='Y' and (PAY_FLOW_COMMON_UTIL_PKG.check_security(pfi.LEGISLATIVE_DATA_GROUP_ID,'PER_LEGISLATIVE_DATA_GROUPS','LEGISLATIVE_DATA_GROUP_ID','PER_CHOOSE_LEGISLATIVE_DATA_GROUP_DATA') ='Y' OR pfi.LEGISLATIVE_DATA_GROUP_ID is null)) and ((pfi.instantiated_by=session_contexts.ctx_user_id or session_contexts.ctx_user_id is null) or (pcit.owner_id=session_contexts.ctx_user_id or session_contexts.ctx_user_id is null)) and pf.default_flow_flag=nvl(session_contexts.ctx_default_flow,pf.default_flow_flag) and pfi.creation_date >= (sysdate-nvl(greatest(session_contexts.ctx_submitted_from,session_contexts.ctx_completed_from),'30')) AND EXISTS( SELECT 1 FROM PAY_CHECKLISTS pfcat WHERE nvl(pfcat.category_type,'#') = nvl(session_contexts.ctx_flow_category, nvl(pfcat.category_type,'#')) and nvl(pfcat.sub_category_type,'#')=nvl(session_contexts.ctx_flow_sub_category, nvl(pfcat.sub_category_type,'#'))) and nvl(sfa.LDG_ID,-9999)=to_number(nvl(session_contexts.ctx_ldg, nvl(sfa.LDG_ID,-9999))) ) ddd , hcm_lookups jjj where jjj.lookup_type ='ORA_PAY_UX_TASK_STATUS' and jjj.lookup_code (+) =ddd.PROCESS_STATUS_CODE ) order by Submitted_On desc

SQL_Statement

select FLOW_INSTANCE_ID,

BASE_FLOW_ID,

FLOW_NAME,

FLOW_PATTERN,

SUBMITTED_BY,

SUBMITTED_ON,

SCHEDULED_DATE,

FLOW_STATUS,

WITH_ERROR,

OVERDUE,

TAKING_LONGER,

EXPECTED_TIME_LEFT,

FLOW_STATUS_MEANING,

STATUS_CODE,

STATUS,

TASK_STATUS_CODE,

LDG_NAME,

LDG_ID,

LC_CODE,

SUMMARY_TYPE,

SUMMARY_BREAKDOWN,

PAYROLL,

LOCATION,

TRU,

PSU,

EXTRACT_TYPE,

EXTRACT_GROUP,

EXTRACT_AREA,

REPORT_CATEGORY_ID,

BASE_CHECKLIST_ID,

BASE_TASK_ID,

TASK_TYPE,

CHECKLIST_NAME,

TASK_NAME,

BASE_TASK_NAME,

PT_TASK_TYPE,

TASK_SUBMITTED_BY,

TASK_SUBMITTED_ON,

TASK_SCHEDULED_DATE,

TASK_LAST_UPDATE_DATE,

TASK_INSTANCE_ID,

BASE_TASK_ACTION_ID,

BASE_FLOW_TASK_ID,

FLOW_DESC,

SCHEDULE_END_DATE,

RECURRING_FLAG,

RECUR_SCH_FORMULA_ID,

RECUR_TIME_COMPONENT,

RECUR_SCH_TIME_DEF_ID,

RUN_SEQUENCE,

TARGET_URI,

TASK_CHK_INST_ID,

CHECKLIST_DESCRIPTION,

TASK_DESCRIPTION,

INT_TASK_CODE,

DEFAULT_FLOW_FLAG,

SKIP_FLOW_FLAG,

SKIP_TASK_FLAG,

FORCE_SUBMIT_FLAG,

BATCH_LOADER,

ACTION_ALLOWED,

MODULE_NAME,

ARCHIVED_INFORMATION,

TOTAL_COMPLETED||'#'|| UNPROCESSED as NUMBER_OF_OBJECTS_PROGRESS,

ERROR_MESSAGE_COUNT

from

(

select Flow_Instance_Id,base_Flow_id,Flow_Name Flow_Name,Flow_Pattern,Submitted_By,Submitted_On,

Scheduled_Date,Flow_Status,With_Error,Overdue,

(case when Expected_Time_Left < sysdate then 1 else null end) Taking_Longer,

decode(status_code,'PROCESSING',Expected_Time_Left-systimestamp,null) Expected_Time_Left,

FLOW_STATUS_MEANING,status_code,

status,

Task_Status_code,

ldg_name,ldg_id,lc_code,summary_type,summary_breakdown,Payroll ,Location, TRU,PSU,

Extract_Type,report_category_id,Extract_Group,Extract_Area,

base_checklist_id,base_task_id, Task_Type,Checklist_Name, Task_Name,BASE_TASK_NAME,pt_task_type,

task_Submitted_By,Task_Submitted_On,Task_Scheduled_Date,task_last_update_Date,task_instance_id,base_task_action_id,base_flow_task_id,

flow_desc,

SCHEDULE_END_DATE,RECURRING_FLAG,RECUR_SCH_FORMULA_ID,RECUR_TIME_COMPONENT,RECUR_SCH_TIME_DEF_ID,RUN_SEQUENCE,TARGET_URI,TASK_CHK_INST_ID,

checklist_description,

task_description,int_task_code,default_flow_flag,skip_flow_flag,skip_task_flag,force_submit_flag,PBL as BATCH_LOADER,action_allowed,module_name,

0 ARCHIVED_INFORMATION,

null TOTAL_COMPLETED,

null UNPROCESSED,

null ERROR_MESSAGE_COUNT

from ( select Flow_Instance_Id,Flow_Name Flow_Name,Flow_Pattern,Submitted_By,Submitted_On,

Scheduled_Date,Flow_Status,With_Error,Overdue,

decode(status_code,'PROCESSING',

pay_statistics_utility_pkg.get_estimated_time (flowtaskid => BASE_FLOW_TASK_ID,

status_code => status_code,

taskintanceid =>task_instance_id) +

(select max(lll.creation_date) from pay_requests lll where lll.FLOW_TASK_INSTANCE_ID = task_instance_id),

null) Expected_Time_Left,

FLOW_STATUS_MEANING,status_code,

jjj.meaning status,

Task_Status_code,

ldg_name,ldg_id,lc_code,

decode(nvl(ctx_summary_context,'STATUS'),

'FLOWSTATUS',flow_status_meaning,

'STATUS',jjj.meaning,

'PAYROLL',payroll,

'LOCATION',Location,

'TRU',TRU,

'PSU',PSU,

'EXTTYPE',decode(Extract_type,null,null,substrb(Extract_type,1,instrb(Extract_type,'[#]')-1)),

'EXTGRP',decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]',1,2)+3)),

'EXTAREA',decode(Extract_type,null,null,Extract_Area)) summary_type,

nvl(ctx_summary_context,'STATUS') summary_breakdown,Payroll ,Location, TRU,PSU,

decode(Extract_type,null,null,substrb(Extract_type,1,instrb(Extract_type,'[#]')-1)) Extract_Type,

decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]')+3,instrb(Extract_type,'[#]',1,2)-instrb(Extract_type,'[#]',1,1)-3)) report_category_id,

decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]',1,2)+3)) Extract_Group,

decode(Extract_type,null,null,'[#][#]',null,Extract_Area) Extract_Area,

base_checklist_id,base_task_id, Task_Type,Checklist_Name, Task_Name,BASE_TASK_NAME,pt_task_type,

task_Submitted_By,Task_Submitted_On,Task_Scheduled_Date,task_last_update_Date,task_instance_id,base_task_action_id,base_flow_task_id,

flow_desc,

SCHEDULE_END_DATE,RECURRING_FLAG,RECUR_SCH_FORMULA_ID,RECUR_TIME_COMPONENT,RECUR_SCH_TIME_DEF_ID,RUN_SEQUENCE,TARGET_URI,TASK_CHK_INST_ID,

checklist_description,

task_description,int_task_code ,default_flow_flag,skip_flow_flag,skip_task_flag,force_submit_flag,base_flow_id,PBL,action_allowed,MODULE_NAME,ctx_submitted_from,ctx_completed_from,ctx_summary_context

from (

select distinct pft.base_flow_task_name,

pfi.flow_instance_id Flow_Instance_Id,pfi.instance_name Flow_Name,pf.flow_name Flow_Pattern,

pu.username Submitted_By,pfi.creation_date Submitted_On,pfi.scheduled_date Scheduled_Date,

tpu.username task_Submitted_By,

pfti.creation_date Task_Submitted_On,

pfti.scheduled_date Task_Scheduled_Date,

decode(pfi.status,'COMPLETED','COMPLETE','CREATED','NOT_STARTED',pfi.status) Flow_Status,

(select decode(count(distinct ppti.base_flow_task_id),0,to_number(null),count(distinct ppti.base_flow_task_id))

from pay_flow_task_instances ppti

where ppti.flow_instance_id = pfi.flow_instance_id

and ppti.status in ('ERRORED','ESS_CHILD_JOB_SUB_ERROR','ESS_MT_SERV_NOT_FOUND','ESS_PARENT_JOB_SUB_ERROR','ESS_RT_SERV_NOT_FOUND','FUNCTIONAL_ERROR')

and ppti.flow_task_instance_id=pfti.flow_task_instance_id) With_Error,

(select 1

from PAY_CHECKLIST_INSTANCES pcl

where pcl.flow_task_instance_id=pfti.flow_task_instance_id

and sysdate>pcl.due_date

and pfti.status not in ('COMPLETED','SKIPPED')) Overdue,

ldg.name ldg_name,

ldg.legislative_data_group_id ldg_id,

ldg.legislation_code lc_code,

decode(pfti.status,'COMPLETED','RECENTLY_COMPLETED','ERRORED','REQUIRES_ATTENTION',

'ESS_CHILD_JOB_COMPLETED','PROCESSING','ESS_CHILD_JOB_NOT_FOUND','REQUIRES_ATTENTION',

'ESS_CHILD_JOB_SUBMITTED','PROCESSING','ESS_CHILD_JOB_SUB_ERROR','REQUIRES_ATTENTION',

'ESS_MT_SERV_NOT_FOUND','REQUIRES_ATTENTION',

'ESS_PARENT_JOB_SUBMITTED','PROCESSING','ESS_PARENT_JOB_SUB_ERROR','REQUIRES_ATTENTION',

'ESS_RT_SERV_NOT_FOUND','REQUIRES_ATTENTION',

'EXECUTION_STARTED',decode(pc.checklist_type,'MAN_CHECKLIST','TO_DO','PROCESSING'),

'FUNCTIONAL_ERROR','REQUIRES_ATTENTION',

'IN_PROGRESS',decode(pc.checklist_type,'MAN_CHECKLIST','TO_DO','PROCESSING'),

'IN_PROGRESS_IDLE','TO_DO','MARKED_FOR_RETRY','REQUIRES_ATTENTION','NOT_STARTED','NOT_STARTED',

'PUBLISHED',decode(pc.checklist_type,'MAN_CHECKLIST','TO_DO','PROCESSING'),

'ROLLEDBACK','REQUIRES_ATTENTION',

'SCHEDULED','NOT_STARTED','SKIPPED','RECENTLY_COMPLETED',

pfti.status) status_code,

decode(pfti.status,'COMPLETED','COMPLETE',

'ERRORED','COMPLETE_WITH_ISSUES',

'ESS_CHILD_JOB_COMPLETED','IN_PROCESSING',

'ESS_CHILD_JOB_NOT_FOUND','COMPLETE_WITH_ISSUES',

'ESS_CHILD_JOB_SUBMITTED','IN_PROCESSING',

'ESS_CHILD_JOB_SUB_ERROR','COMPLETE_WITH_ISSUES',

'ESS_MT_SERV_NOT_FOUND','COMPLETE_WITH_ISSUES',

'ESS_PARENT_JOB_SUBMITTED','INITIALIZING',

'ESS_PARENT_JOB_SUB_ERROR','COMPLETE_WITH_ISSUES',

'ESS_RT_SERV_NOT_FOUND','COMPLETE_WITH_ISSUES',

'EXECUTION_STARTED','IN_PROGRESS',

'FUNCTIONAL_ERROR','COMPLETE_WITH_ISSUES',

'IN_PROGRESS',decode(pc.checklist_type,'MAN_CHECKLIST','IN_PROGRESS','IN_PROCESSING'),

'IN_PROGRESS_IDLE','IN_PROGRESS',

'MARKED_FOR_RETRY','MARKED_FOR_RETRY',

'NOT_STARTED','NOT_STARTED',

'PUBLISHED','INITIALIZING',

'ROLLEDBACK','ROLLED_BACK',

'SCHEDULED','SCHEDULED',

'SKIPPED','SKIPPED',

pfti.status) Task_Status_code,

decode(pfi.status,'COMPLETED','N','SKIPPED','N','Y') skip_flow_flag,

decode(pfti.status,'COMPLETED','N','SKIPPED','N','Y') skip_task_flag,

'N' force_submit_flag,

/* This needs to stored in the flow instance attributes */

(select max(ppp.payroll_name)

from pay_all_payrolls_f ppp

where ppp.payroll_id = pfi.attribute1

and pfi.attribute1 is not null) Payroll,

/* This needs to stored in the flow instance attributes or cached*/

null Location,null TRU,null PSU,

/* This needs to stored in the flow instance attributes or cached*/

(select /*+ ORDERED */ max(extt.TYPE_NAME)||'[#]'||max(prg.report_category_id)||'[#]'||max((select max(hhh.meaning) from hcm_lookups hhh where hhh.lookup_type='ORA_PER_EXT_EXTRACT_CATEGORY' and hhh.lookup_code=ext.attribute27))

from PAY_FLOW_TASK_PARAM_VALS fpv,

PAY_FLOW_TASK_PARAMETERS_VL fp,

PAY_REPORT_CATEGORIES prg,

PAY_REPORT_DEFINITIONS prd,

PER_EXT_DEFINITIONS_VL ext,

PER_EXT_TYPES_VL extt

where pfti.flow_task_instance_id = fpv.flow_task_instance_id

and fpv.flow_task_param_value is not null

and fp.flow_task_param_id = fpv.base_flow_task_param_id

and fp.base_parameter_name='REPORT_CATEGORY'

and fpv.flow_task_param_value = prg.report_category_id

and prg.REPORT_GROUP_ID=prd.REPORT_GROUP_ID

and prd.EXT_DEFINITION_ID=ext.EXT_DEFINITION_ID

and extt.EXT_TYPE_CODE=ext.EXT_TYPE_CODE) Extract_Type,

null Extract_Group,pt.task_name Extract_Area,

pfis.meaning flow_status_meaning,

pc.base_checklist_id,

pt.base_task_id,

decode(pc.checklist_type,'MAN_CHECKLIST','MANUAL','AUTOMATIC') Task_Type,pc.checklist_name Checklist_Name,

pt.task_name Task_Name,pt.BASE_TASK_NAME,pt.task_type pt_task_type,pfti.last_update_Date task_last_update_Date,

pfti.flow_task_instance_id task_instance_id,pfti.base_task_action_id,pft.base_flow_task_id,

pf.description flow_desc,

pfi.SCHEDULE_END_DATE,pfi.RECURRING_FLAG,pfi.RECUR_SCH_FORMULA_ID,pfi.RECUR_TIME_COMPONENT,pfi.RECUR_SCH_TIME_DEF_ID,to_char(pfi.creation_date,'YYYYMMDDHHMISS')+pc.RUN_SEQUENCE RUN_SEQUENCE,

decode(trim(pc.dest_UI_URL),null,decode(trim(pft.dest_ui_url),null,pt.dest_ui_url,pft.dest_ui_url),pc.dest_UI_URL) as TARGET_URI,

pc.description checklist_description,

pt.description task_description,

pfti.status int_task_code,

pf.default_flow_flag,

pf.base_flow_id,

pci.CHECKLIST_INSTANCE_ID TASK_CHK_INST_ID,

pci.owner_id,pci.owner_type,session_contexts.user_id,

decode(pci.owner_id,session_contexts.user_id,'Y',decode(pu.username,session_contexts.username,'Y','N')) action_allowed,

decode(pt.base_task_name,'ORA_PAY_HDL_LOAD','X','CREATE_BATCH','Y','ENTER_BATCH','Y','PURGE_BATCH','Y','TRANSFER_BATCH','Y','LOAD_BATCH','Y','CREATE_BATCH_FROM_FILE','Y','ROLLBACK_PBL_BATCH','Y','FILE_UPLOAD_BATCH','Y','N') PBL,

MODULE_NAME,ctx_submitted_from,ctx_completed_from,ctx_summary_context

from pay_flow_instances pfi,

pay_flows_vl pf,

per_legislative_data_groups_vl ldg,

per_users pu,

hcm_lookups pfis,

pay_flow_task_instances pfti,

pay_flow_tasks pft,

per_users tpu,

PAY_CHECKLISTS_vl pc,

pay_tasks_vl pt,

PAY_CHECKLIST_INSTANCES pci,

PAY_CHECKLISTS pfcat,

(select max(decode(fup.PREFERENCE_NAME,'DEF_RECENTLY_COMP_FROM',decode(fup.preference_value,'LAST_24_HOURS',1,'LAST_WEEK',7,'LAST_MONTH',sysdate-add_months(sysdate,-1),'LAST_3_MONTHS',sysdate-add_months(sysdate,-3),'LAST_6_MONTHS',sysdate-add_months(sysdate,-6),'LAST_YEAR',sysdate-add_months(sysdate,-12),'FOREVER',999999,null),null)) ctx_completed_from,

max(decode(fup.PREFERENCE_NAME,'DEF_SEARCH_BACK_FROM',decode(fup.preference_value,'LAST_24_HOURS',1,'LAST_WEEK',7,'LAST_MONTH',sysdate-add_months(sysdate,-1),'LAST_3_MONTHS',sysdate-add_months(sysdate,-3),'LAST_6_MONTHS',sysdate-add_months(sysdate,-6),'LAST_YEAR',sysdate-add_months(sysdate,-12),'FOREVER',999999,null),null)) ctx_submitted_from,

max(decode(fup.PREFERENCE_NAME,'DEF_SUMMARY_TYPE',fup.preference_value,null)) ctx_summary_context,

max(FND_GLOBAL.get_session_attribute('HCM_USER_ID')) user_id,

max(fup.USER_NAME) username,

max(decode(fup.PREFERENCE_NAME,'USER_IN_PREF',fup.preference_value,null)) ctx_username,

max(decode(fup.PREFERENCE_NAME,'USER_IN_PREF',decode(fup.preference_value,null,null,(select max(ppp.user_id) from per_users ppp where ppp.username=fup.preference_value)),null)) ctx_user_id,

max(decode(fup.PREFERENCE_NAME,'CATEGORY',fup.preference_value,null)) ctx_flow_category,

max(decode(fup.PREFERENCE_NAME,'SUB_CATEGORY',fup.preference_value,null)) ctx_flow_sub_category,

max(decode(fup.PREFERENCE_NAME,'FLOW_PATTERN',fup.preference_value,null)) ctx_flow_pattern,

max(decode(fup.PREFERENCE_NAME,'DEFAULT_FLOW',fup.preference_value,null)) ctx_default_flow,

max(decode(fup.PREFERENCE_NAME,'LDG',fup.preference_value,null)) ctx_ldg,

fup.MODULE_NAME

from FND_USER_PREFERENCES fup

where fup.user_name=fnd_session_ns.get_namespace_attr(FND_GLOBAL.session_id,'FND$SECURITY', 'USER_NAME')

and fup.MODULE_NAME like 'ORA_DX%'

group by fup.MODULE_NAME) session_contexts

where pfi.base_flow_id = pf.base_flow_id

and pfcat.base_flow_id = pfi.base_flow_id

AND pf.default_flow_flag IN('Y','N')

and (PAY_FLOW_COMMON_UTIL_PKG.check_security(pf.base_flow_id,'PAY_FLOWS','BASE_FLOW_ID','PAY_VIEW_PAYROLL_FLOW_DATA') ='Y'

and (PAY_FLOW_COMMON_UTIL_PKG.check_security(pfi.LEGISLATIVE_DATA_GROUP_ID,'PER_LEGISLATIVE_DATA_GROUPS','LEGISLATIVE_DATA_GROUP_ID','PER_CHOOSE_LEGISLATIVE_DATA_GROUP_DATA') ='Y'

OR pfi.LEGISLATIVE_DATA_GROUP_ID is null))

and nvl(pfcat.category_type,'#')=nvl(session_contexts.ctx_flow_category, nvl(pfcat.category_type,'#'))

and nvl(pfcat.sub_category_type,'#')=nvl(session_contexts.ctx_flow_sub_category, nvl(pfcat.sub_category_type,'#'))

and ldg.legislative_data_group_id (+) = pfi.legislative_data_group_id

and pu.user_id = pfi.instantiated_by

and pfis.lookup_type='PAY_FLOW_INSTANCE_STATUS'

and pfis.lookup_code=decode(pfi.status,'CREATED','NOT_STARTED',pfi.status)

and pfi.creation_date >= (sysdate-nvl(greatest(session_contexts.ctx_submitted_from,session_contexts.ctx_completed_from),'30'))

and ((pfi.instantiated_by=session_contexts.ctx_user_id or session_contexts.ctx_user_id is null)

or (pci.owner_id=session_contexts.ctx_user_id or session_contexts.ctx_user_id is null))

and pf.base_flow_name=nvl(session_contexts.ctx_flow_pattern,pf.base_flow_name)

and pfti.flow_instance_id = pfi.flow_instance_id

and pft.base_flow_task_id = pfti.base_flow_task_id

and pft.basE_task_id=pt.base_task_id

and pf.default_flow_flag=nvl(session_contexts.ctx_default_flow,pf.default_flow_flag)

and pci.flow_task_instance_id (+) = pfti.flow_task_instance_id

and ( (pfti.status in ('ESS_PARENT_JOB_SUBMITTED','ESS_CHILD_JOB_COMPLETED','ESS_CHILD_JOB_SUBMITTED','IN_PROGRESS','EXECUTION_STARTED','IN_PROGRESS_IDLE','NOT_STARTED','PUBLISHED','SCHEDULED')

and pfti.creation_date > ( SYSDATE - 7 ))

or ( not exists (select null from PAY_STATS_FLOW_ACTIONS bbb where pfti.flow_task_instance_id = bbb.FLOW_TASK_INSTANCE_ID and bbb.TASK_STATUS not in ('IN_PROGRESS','EXECUTION_STARTED','NOT_STARTED'))and pc.checklist_type !='MAN_CHECKLIST'))

and pc.base_checklist_id (+) = pci.base_checklist_id

and tpu.user_id (+) = decode(pci.owner_type,null,pci.owner_id,'USER',pci.owner_id,-1)

and ((nvl(session_contexts.ctx_summary_context,'STATUS')='FLOWSTATUS' and pt.base_task_name = 'START_FLOW')

or (nvl(session_contexts.ctx_summary_context,'STATUS')!='FLOWSTATUS' and pt.base_task_name not in ('START_FLOW','END_FLOW')))

and nvl(pfi.legislative_data_group_id,-9999)=to_number(nvl(session_contexts.ctx_ldg, nvl(pfi.legislative_data_group_id,-9999)))

and (pc.legislative_data_group_id = ldg.legislative_data_group_id

or pc.legislation_code = ldg.legislation_code

or (pc.legislative_data_group_id is null and pc.legislation_code is null))

and (pft.legislative_data_group_id = ldg.legislative_data_group_id

or pft.legislation_code = ldg.legislation_code

or (pft.legislative_data_group_id is null and pft.legislation_code is null))

and (pf.legislative_data_group_id = ldg.legislative_data_group_id

or pf.legislation_code = ldg.legislation_code

or (pf.legislative_data_group_id is null and pf.legislation_code is null))

and (pt.legislative_data_group_id = ldg.legislative_data_group_id

or pt.legislation_code = ldg.legislation_code

or (pt.legislative_data_group_id is null and pt.legislation_code is null))

) ddd, hcm_lookups jjj

where jjj.lookup_type ='ORA_PAY_UX_TASK_STATUS'

and jjj.lookup_code(+) =ddd.status_code) aaa where ((Submitted_On>= (sysdate-nvl(ctx_submitted_from,'30')) and Flow_Status!='COMPLETE')

or (Submitted_On>= (sysdate-nvl(ctx_completed_from,'7')) and Flow_Status='COMPLETE'))

and (nvl(ctx_summary_context,'STATUS')='FLOWSTATUS' or base_checklist_id is not null)

union all

select

FLOW_INSTANCE_ID,

BASE_FLOW_ID,

FLOW_NAME,

FLOW_PATTERN,

SUBMITTED_BY,

SUBMITTED_ON,

SCHEDULED_DATE,

FLOW_STATUS,

WITH_ERROR,

null OVERDUE,

null TAKING_LONGER,

EXPECTED_TIME_LEFT,

FLOW_STATUS_MEANING,

STATUS_CODE,

jjj.meaning STATUS,

TASK_STATUS_CODE,

LDG_NAME,

LDG_ID,

LC_CODE,

decode(nvl(ctx_summary_context,'STATUS'),

'FLOWSTATUS',flow_status_meaning,

'STATUS',jjj.meaning,

'PAYROLL',payroll,

'LOCATION',Location,

'TRU',TRU,

'PSU',PSU,

'EXTTYPE',decode(Extract_type,null,null,substrb(Extract_type,1,instrb(Extract_type,'[#]')-1)),

'EXTGRP',decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]',1,2)+3)),

'EXTAREA',decode(Extract_type,null,null,Extract_Area)) SUMMARY_TYPE,

SUMMARY_BREAKDOWN,

PAYROLL,

LOCATION,

TRU,

PSU,

decode(Extract_type,null,null,substrb(Extract_type,1,instrb(Extract_type,'[#]')-1)) Extract_Type,

decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]')+3,instrb(Extract_type,'[#]',1,2)-instrb(Extract_type,'[#]',1,1)-3)) report_category_id,

decode(Extract_type,null,null,substrb(Extract_type,instrb(Extract_type,'[#]',1,2)+3)) Extract_Group,

decode(Extract_type,null,null,'[#][#]',null,Extract_Area) Extract_Area,

BASE_CHECKLIST_ID,

BASE_TASK_ID,

TASK_TYPE,

CHECKLIST_NAME,

TASK_NAME,

BASE_TASK_NAME,

PT_TASK_TYPE,

TASK_SUBMITTED_BY,

TASK_SUBMITTED_ON,

TASK_SCHEDULED_DATE,

TASK_LAST_UPDATE_DATE,

TASK_INSTANCE_ID,

BASE_TASK_ACTION_ID,

BASE_FLOW_TASK_ID,

FLOW_DESC,

SCHEDULE_END_DATE,

RECURRING_FLAG,

RECUR_SCH_FORMULA_ID,

RECUR_TIME_COMPONENT,

RECUR_SCH_TIME_DEF_ID,

RUN_SEQUENCE,

TARGET_URI,

TASK_CHK_INST_ID,

CHECKLIST_DESCRIPTION,

TASK_DESCRIPTION,

INT_TASK_CODE,

DEFAULT_FLOW_FLAG,

SKIP_FLOW_FLAG,

SKIP_TASK_FLAG,

FORCE_SUBMIT_FLAG,

BATCH_LOADER,

ACTION_ALLOWED,

MODULE_NAME,

ARCHIVED_INFORMATION,

TOTAL_COMPLETED,

UNPROCESSED,

ERROR_MESSAGE_COUNT

from(

SELECT

LDG_ID,ldg.name LDG_NAME, pf.base_flow_id,sfa.FLOW_NAME, FLOW_PATTERN_ID,pf.flow_name FLOW_PATTERN,pf.DESCRIPTION FLOW_DESC, flowStatus.meaning FLOW_STATUS_MEANING,

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

pfi.LEGISLATION_CODE, sfa.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 sfa.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,FLOW_SUBMITTED_ON, FLOW_SUBMITTED_BY,pfi.SCHEDULED_DATE SCHEDULED_DATE,pfi.SCHEDULE_END_DATE SCHEDULE_END_DATE,

sfa.TASK_ID BASE_TASK_ID,pt.task_name TASK_NAME,pt.BASE_TASK_NAME,sfa.FLOW_TASK_ID BASE_FLOW_TASK_ID,sfa.CHECKLIST_ID BASE_CHECKLIST_ID,decode(pt.TASK_TYPE,'MANUAL_TASK','MANUAL','AUTOMATIC') TASK_TYPE,

pct.CHECKLIST_NAME,pct.DESCRIPTION CHECKLIST_DESCRIPTION, pt.DESCRIPTION TASK_DESCRIPTION,

decode(PROCESS_STATUS_CODE,'REQUIRES_ATTENTION','N','RECENTLY_COMPLETED','N','Y') ACTIVE_FLAG,

TASK_SUBMITTED_ON SUBMITTED_ON,TASK_SUBMITTED_BY,pfti.scheduled_date TASK_SCHEDULED_DATE,

pcit.due_date DUE_DATE,decode(PROCESS_STATUS_CODE,'RECENTLY_COMPLETED',pfti.last_update_Date,'SKIPPED',pfti.last_update_Date,null) COMPLETION_DATE,

sfa.FLOW_INSTANCE_ID, sfa.FLOW_TASK_INSTANCE_ID TASK_INSTANCE_ID,pcit.checklist_instance_id TASK_CHK_INST_ID,

to_char(pfi.creation_date,'YYYYMMDDHHMISS')+pct.RUN_SEQUENCE RUN_SEQUENCE, pfi.instantiated_by SUBMITTED_BY,

decode(pfi.status,'COMPLETED','COMPLETE','CREATED','NOT_STARTED',pfi.status) Flow_Status,

(select decode(count(distinct ppti.base_flow_task_id),0,to_number(null),count(distinct ppti.base_flow_task_id))

from pay_flow_task_instances ppti

where ppti.flow_instance_id = pfi.flow_instance_id

and ppti.status in ('ERRORED','ESS_CHILD_JOB_SUB_ERROR','ESS_MT_SERV_NOT_FOUND','ESS_PARENT_JOB_SUB_ERROR','ESS_RT_SERV_NOT_FOUND','FUNCTIONAL_ERROR')

and ppti.flow_task_instance_id=pfti.flow_task_instance_id) With_Error,

PROCESS_STATUS_CODE status_code,

decode(TASK_STATUS,'COMPLETED','COMPLETE',

'ERRORED','COMPLETE_WITH_ISSUES',

'ESS_CHILD_JOB_COMPLETED','IN_PROCESSING',

'ESS_CHILD_JOB_NOT_FOUND','COMPLETE_WITH_ISSUES',

'ESS_CHILD_JOB_SUBMITTED','IN_PROCESSING',

'ESS_CHILD_JOB_SUB_ERROR','COMPLETE_WITH_ISSUES',

'ESS_MT_SERV_NOT_FOUND','COMPLETE_WITH_ISSUES',

'ESS_PARENT_JOB_SUBMITTED','INITIALIZING',

'ESS_PARENT_JOB_SUB_ERROR','COMPLETE_WITH_ISSUES',

'ESS_RT_SERV_NOT_FOUND','COMPLETE_WITH_ISSUES',

'EXECUTION_STARTED','IN_PROGRESS',

'FUNCTIONAL_ERROR','COMPLETE_WITH_ISSUES',

'IN_PROGRESS',decode(pct.checklist_type,'MAN_CHECKLIST','IN_PROGRESS','IN_PROCESSING'),

'IN_PROGRESS_IDLE','IN_PROGRESS',

'MARKED_FOR_RETRY','MARKED_FOR_RETRY',

'NOT_STARTED','NOT_STARTED',

'PUBLISHED','INITIALIZING',

'ROLLEDBACK','ROLLED_BACK',

'SCHEDULED','SCHEDULED',

'SKIPPED','SKIPPED',

TASK_STATUS) TASK_STATUS_CODE,

PROCESS_STATUS_CODE,

PROCESS_DATE, sfa.PAYROLL_ID,payroll.payroll_name PAYROLL, sfa.CONSOLIDATION_SET_ID, BATCH_NAME, FILE_NAME, PSU_ID,null PSU, TRU_ID,null TRU, LOCATION_ID,null LOCATION, LEGAL_EMPLOYER_ID, ORGANIZATION_ID,

DATA_SET_ID,

null EXPECTED_TIME_LEFT, BATCH_ID,BATCH_ID HDL_BATCH_ID,

pf.DEFAULT_FLOW_FLAG,decode(pfi.status,'COMPLETED','N','SKIPPED','N','Y') SKIP_FLOW_FLAG, 'N' FORCE_SUBMIT_FLAG,pf.DEFAULT_FLOW_FLAG SUBMIT_SIMILAR_FLAG,

decode(pfti.status,'COMPLETED','N','SKIPPED','N','Y') SKIP_TASK_FLAG,decode(Task_status,'COMPLETED','Y','N') EXPORT,

(select /*+ ORDERED */ max(extt.TYPE_NAME)||'[#]'||max(prg.report_category_id)||'[#]'||max((select max(hhh.meaning) from hcm_lookups hhh where hhh.lookup_type='ORA_PER_EXT_EXTRACT_CATEGORY' and hhh.lookup_code=ext.attribute27))

from PAY_FLOW_TASK_PARAM_VALS fpv,

PAY_FLOW_TASK_PARAMETERS_VL fp,

PAY_REPORT_CATEGORIES prg,

PAY_REPORT_DEFINITIONS prd,

PER_EXT_DEFINITIONS_VL ext,

PER_EXT_TYPES_VL extt

where pfti.flow_task_instance_id = fpv.flow_task_instance_id

and fpv.flow_task_param_value is not null

and fp.flow_task_param_id = fpv.base_flow_task_param_id

and fp.base_parameter_name='REPORT_CATEGORY'

and fpv.flow_task_param_value = prg.report_category_id

and prg.REPORT_GROUP_ID=prd.REPORT_GROUP_ID

and prd.EXT_DEFINITION_ID=ext.EXT_DEFINITION_ID

and extt.EXT_TYPE_CODE=ext.EXT_TYPE_CODE) EXTRACT_TYPE,null EXTRACT_GROUP, pt.task_name EXTRACT_AREA,null REPORT_CATEGORY_ID,

nvl(ctx_summary_context,'STATUS') SUMMARY_BREAKDOWN,

'Y' action_allowed,

pfti.status int_task_code,

pfti.base_task_action_id,

ldg.legislation_code lc_code,

pt.task_type pt_task_type,

pfti.last_update_Date task_last_update_Date,

pfti.creation_date Task_Submitted_On,

decode(trim(pct.dest_UI_URL),null,decode(trim(pft.dest_ui_url),null,pt.dest_ui_url,pft.dest_ui_url),pct.dest_UI_URL) TARGET_URI,

pfi.RECUR_SCH_TIME_DEF_ID,

pfi.RECURRING_FLAG,pfi.RECUR_SCH_FORMULA_ID,pfi.RECUR_TIME_COMPONENT,

decode(pt.base_task_name,'ORA_PAY_HDL_LOAD','X','CREATE_BATCH','Y','ENTER_BATCH','Y','PURGE_BATCH','Y','TRANSFER_BATCH','Y','LOAD_BATCH','Y','CREATE_BATCH_FROM_FILE','Y','ROLLBACK_PBL_BATCH','Y','FILE_UPLOAD_BATCH','Y','N') BATCH_LOADER,

MODULE_NAME,

ctx_submitted_from,ctx_completed_from,ctx_summary_context,

ARCHIVED_INFORMATION,

TOTAL_COMPLETED,

UNPROCESSED,

ERROR_MESSAGE_COUNT

FROM (SELECT LDG_ID ,

FLOW_NAME ,

FLOW_PATTERN_ID ,

FLOW_TATUS ,

FLOW_SUBMITTED_ON ,

FLOW_SUBMITTED_BY ,

TASK_ID ,

FLOW_TASK_ID ,

CHECKLIST_ID ,

TASK_STATUS ,

TASK_SUBMITTED_ON ,

TASK_SUBMITTED_BY ,

FLOW_INSTANCE_ID ,

FLOW_TASK_INSTANCE_ID ,

PROCESS_STATUS_CODE ,

PROCESS_DATE ,

PAYROLL_ID ,

CONSOLIDATION_SET_ID ,

BATCH_ID ,

BATCH_NAME ,

FILE_NAME ,

PSU_ID ,

TRU_ID ,

LOCATION_ID ,

LEGAL_EMPLOYER_ID ,

ORGANIZATION_ID ,

DATA_SET_ID ,

ARCHIVED_INFORMATION ,

TOTAL_COMPLETED,

UNPROCESSED,

ERROR_MESSAGE_COUNT

FROM fusion.PAY_STATS_FLOW_ACTIONS sfa

WHERE TASK_STATUS NOT in ('ESS_PARENT_JOB_SUBMITTED','ESS_CHILD_JOB_COMPLETED','ESS_CHILD_JOB_SUBMITTED','IN_PROGRESS','EXECUTION_STARTED','IN_PROGRESS_IDLE','NOT_STARTED','PUBLISHED','SCHEDULED')

and (sfa.active_flag = 'Y'

or

(not exists (select null from PAY_STATS_FLOW_ACTIONS aaa where aaa.flow_task_instance_id=sfa.flow_task_instance_id and aaa.active_flag = 'Y')

and sfa.TI_REQUEST_ID||sfa.REQUEST_ID = (select max(aaa.TI_REQUEST_ID||aaa.REQUEST_ID)

from PAY_STATS_FLOW_ACTIONS aaa

where aaa.flow_task_instance_id=sfa.flow_task_instance_id

and nvl(aaa.active_flag,'N') != 'Y' )))

) sfa

, per_legislative_data_groups_vl ldg, pay_flows_vl pf, pay_flow_instances pfi, hcm_lookups flowStatus, pay_tasks_vl pt, PAY_CHECKLISTS_VL pct

, pay_flow_task_instances pfti, PAY_CHECKLIST_INSTANCES pcit, pay_flow_tasks_vl pft,

pay_all_payrolls_f payroll,

(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

,(select max(decode(fup.PREFERENCE_NAME,'DEF_RECENTLY_COMP_FROM',decode(fup.preference_value,'LAST_24_HOURS',1,'LAST_WEEK',7,'LAST_MONTH',sysdate-add_months(sysdate,-1),'LAST_3_MONTHS',sysdate-add_months(sysdate,-3),'LAST_6_MONTHS',sysdate-add_months(sysdate,-6),'LAST_YEAR',sysdate-add_months(sysdate,-12),'FOREVER',999999,null),null)) ctx_completed_from,

max(decode(fup.PREFERENCE_NAME,'DEF_SEARCH_BACK_FROM',decode(fup.preference_value,'LAST_24_HOURS',1,'LAST_WEEK',7,'LAST_MONTH',sysdate-add_months(sysdate,-1),'LAST_3_MONTHS',sysdate-add_months(sysdate,-3),'LAST_6_MONTHS',sysdate-add_months(sysdate,-6),'LAST_YEAR',sysdate-add_months(sysdate,-12),'FOREVER',999999,null),null)) ctx_submitted_from,

max(decode(fup.PREFERENCE_NAME,'DEF_SUMMARY_TYPE',fup.preference_value,null)) ctx_summary_context,

max(FND_GLOBAL.get_session_attribute('HCM_USER_ID')) user_id,

max(fup.USER_NAME) username,

max(decode(fup.PREFERENCE_NAME,'USER_IN_PREF',fup.preference_value,null)) ctx_username,

max(decode(fup.PREFERENCE_NAME,'USER_IN_PREF',decode(fup.preference_value,null,null,(select max(ppp.user_id) from per_users ppp where ppp.username=fup.preference_value)),null)) ctx_user_id,

max(decode(fup.PREFERENCE_NAME,'CATEGORY',fup.preference_value,null)) ctx_flow_category,

max(decode(fup.PREFERENCE_NAME,'SUB_CATEGORY',fup.preference_value,null)) ctx_flow_sub_category,

max(decode(fup.PREFERENCE_NAME,'FLOW_PATTERN',fup.preference_value,null)) ctx_flow_pattern,

max(decode(fup.PREFERENCE_NAME,'DEFAULT_FLOW',fup.preference_value,null)) ctx_default_flow,

max(decode(fup.PREFERENCE_NAME,'LDG',fup.preference_value,null)) ctx_ldg,

fup.MODULE_NAME

from FND_USER_PREFERENCES fup

where fup.user_name=fnd_session_ns.get_namespace_attr(FND_GLOBAL.session_id,'FND$SECURITY', 'USER_NAME')

and fup.MODULE_NAME like 'ORA_DX%'

group by fup.MODULE_NAME) session_contexts

where sfa.LDG_ID=ldg.legislative_data_group_id(+)

and sfa.FLOW_PATTERN_ID=pf.flow_id

and sfa.flow_instance_id= pfi.flow_instance_id

and flowStatus.lookup_type='PAY_FLOW_INSTANCE_STATUS'

and flowStatus.lookup_code=pfi.STATUS

and sfa.TASK_ID=pt.TASK_ID

and sfa.CHECKLIST_ID=pct.CHECKLIST_ID

and sfa.FLOW_TASK_INSTANCE_ID= pfti.FLOW_TASK_INSTANCE_ID

and sfa.FLOW_TASK_INSTANCE_ID=pcit.FLOW_TASK_INSTANCE_ID

and sfa.FLOW_TASK_ID=pft.flow_task_id

and sfa.payroll_id =payroll.payroll_id(+)

and nvl(sfa.process_date,sfa.task_submitted_on) between payroll.effective_start_date(+) and payroll.effective_end_date(+)

and decode(pt.BASE_TASK_NAME,'QUICK_PAY_SIMPLIFIED','AUTOMATIC', pt.Task_Type)!='MANUAL_TASK'

and (PAY_FLOW_COMMON_UTIL_PKG.check_security(pf.base_flow_id,'PAY_FLOWS','BASE_FLOW_ID','PAY_VIEW_PAYROLL_FLOW_DATA') ='Y'

and (PAY_FLOW_COMMON_UTIL_PKG.check_security(pfi.LEGISLATIVE_DATA_GROUP_ID,'PER_LEGISLATIVE_DATA_GROUPS','LEGISLATIVE_DATA_GROUP_ID','PER_CHOOSE_LEGISLATIVE_DATA_GROUP_DATA') ='Y'

OR pfi.LEGISLATIVE_DATA_GROUP_ID is null))

and ((pfi.instantiated_by=session_contexts.ctx_user_id or session_contexts.ctx_user_id is null)

or (pcit.owner_id=session_contexts.ctx_user_id or session_contexts.ctx_user_id is null))

and pf.default_flow_flag=nvl(session_contexts.ctx_default_flow,pf.default_flow_flag)

and pfi.creation_date >= (sysdate-nvl(greatest(session_contexts.ctx_submitted_from,session_contexts.ctx_completed_from),'30'))

AND EXISTS( SELECT 1 FROM PAY_CHECKLISTS pfcat

WHERE nvl(pfcat.category_type,'#') = nvl(session_contexts.ctx_flow_category, nvl(pfcat.category_type,'#'))

and nvl(pfcat.sub_category_type,'#')=nvl(session_contexts.ctx_flow_sub_category, nvl(pfcat.sub_category_type,'#')))

and nvl(sfa.LDG_ID,-9999)=to_number(nvl(session_contexts.ctx_ldg, nvl(sfa.LDG_ID,-9999)))

) ddd

, hcm_lookups jjj

where jjj.lookup_type ='ORA_PAY_UX_TASK_STATUS'

and jjj.lookup_code (+) =ddd.PROCESS_STATUS_CODE

)

order by Submitted_On desc