PAY_PROC_RESULTS

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
FLOW_ID TASK_TYPE TASK_ID DATA_SET_ID DATA_SET_NAME FLOW_INSTANCE_ID FLOW_TASK_INSTANCE_ID FLOW_INSTANCE_NAME FLOW_TASK_NAME FLOW_NAME BASE_FLOW_ID TASK_STATUS TASK_STATUS_MEANING TASK_SUB_STATUS TASK_SUB_STATUS_MEANING IS_PROCESSING IS_COMPLETED IS_ERRORS IS_MARKED_FOR_RETRY IS_ROLLED_BACK_RECORDS IS_CRITICAL_ERRORS IS_STOPPED IS_SKIPPED TASK_TYPE_MEANING TASK_TYPE_CODE PROCESS_DATE PAYROLL_ID PAYROLL_NAME SUBMITTED_BY SUBMISSION_DATE RECORD MESSAGES TIME_ELAPSED LOGGING_TURNED_ON PERIOD_NAME STATUTORY_PERIOD DATE_EARNED LEGISLATIVE_DATA_GROUP_NAME LEGISLATIVE_DATA_GROUP_ID BASE_TASK_NAME SUBMIT_ANOTHER_TASK TASK_NAME QUICKPAY_TASK_FLAG PAYROLL_ACTION_ID ESS_REQUEST_ID TI_REQUEST_ID STAT_ERROR_TOTAL LAST_UPDATE_DATE UNPROCESSED IS_CANCELLING ROLLBACK_ERROR_FLAG

Name

FLOW_ID

TASK_TYPE

TASK_ID

DATA_SET_ID

DATA_SET_NAME

FLOW_INSTANCE_ID

FLOW_TASK_INSTANCE_ID

FLOW_INSTANCE_NAME

FLOW_TASK_NAME

FLOW_NAME

BASE_FLOW_ID

TASK_STATUS

TASK_STATUS_MEANING

TASK_SUB_STATUS

TASK_SUB_STATUS_MEANING

IS_PROCESSING

IS_COMPLETED

IS_ERRORS

IS_MARKED_FOR_RETRY

IS_ROLLED_BACK_RECORDS

IS_CRITICAL_ERRORS

IS_STOPPED

IS_SKIPPED

TASK_TYPE_MEANING

TASK_TYPE_CODE

PROCESS_DATE

PAYROLL_ID

PAYROLL_NAME

SUBMITTED_BY

SUBMISSION_DATE

RECORD

MESSAGES

TIME_ELAPSED

LOGGING_TURNED_ON

PERIOD_NAME

STATUTORY_PERIOD

DATE_EARNED

LEGISLATIVE_DATA_GROUP_NAME

LEGISLATIVE_DATA_GROUP_ID

BASE_TASK_NAME

SUBMIT_ANOTHER_TASK

TASK_NAME

QUICKPAY_TASK_FLAG

PAYROLL_ACTION_ID

ESS_REQUEST_ID

TI_REQUEST_ID

STAT_ERROR_TOTAL

LAST_UPDATE_DATE

UNPROCESSED

IS_CANCELLING

ROLLBACK_ERROR_FLAG

Query

SQL_Statement
SELECT PF.FLOW_ID, PT.TASK_TYPE, PSFA.TASK_ID, PSFA.DATA_SET_ID, PSFA.FILE_NAME DATA_SET_NAME, PSFA.FLOW_INSTANCE_ID,PSFA.FLOW_TASK_INSTANCE_ID, PSFA.FLOW_NAME FLOW_INSTANCE_NAME, PFT.FLOW_TASK_NAME, PF.FLOW_NAME,PF.BASE_FLOW_ID, PFTI.UI_TASK_STATUS TASK_STATUS, (SELECT MAX(MEANING) FROM HCM_LOOKUPS WHERE LOOKUP_CODE=PFTI.UI_TASK_STATUS and LOOKUP_TYPE='ORA_FLOW_UI_STATUS') TASK_STATUS_MEANING, PFTI.UI_TASK_STATUS TASK_SUB_STATUS, (SELECT MAX(MEANING) FROM HCM_LOOKUPS WHERE LOOKUP_CODE=PFTI.UI_TASK_STATUS and LOOKUP_TYPE='ORA_FLOW_UI_STATUS') TASK_SUB_STATUS_MEANING, decode(PFTI.UI_TASK_STATUS,'IN_PROGRESS','Y','N') IS_PROCESSING, decode(PFTI.UI_TASK_STATUS,'COMPLETED','Y','N') IS_COMPLETED, decode(PFTI.UI_TASK_STATUS,'COMPLETED','N',decode(PFTI.IS_ERROR,'Y','Y','N')) IS_ERRORS, decode(PFTI.UI_TASK_STATUS,'COMPLETED','N',decode(PFTI.IS_MFR,'Y','Y','N')) IS_Marked_for_Retry, PFI.FI_ROLLEDBACK IS_Rolled_Back_Records, decode(PFTI.UI_TASK_STATUS,'CRITICAL_ALERTS',decode(PFTI.IS_ERROR,'Y','Y','N'),'N') IS_Critical_Errors, decode(PFTI.UI_TASK_STATUS,'CRITICAL_ALERTS',decode(PFTI.IS_ERROR,'Y','N','Y'),'N') IS_Stopped, decode(PFTI.STATUS,'SKIPPED','Y','N') IS_Skipped, (SELECT HcmLookupPEO.MEANING FROM HCM_LOOKUPS HcmLookupPEO WHERE HcmLookupPEO.LOOKUP_CODE= decode((select 1 from dual where PT.BASE_TASK_NAME in('ORA_PAY_HSDL_LOAD','ORA_HDL_GENERATE_FILE_IMPORT','LOAD_BATCH','ORA_PAY_HDL_LOAD')),1,'DATA_LOADER', decode(nvl(PFI.FI_TASK_TYPE,decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE)),'TASK_FLOW', decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE),nvl(PFI.FI_TASK_TYPE,decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE)))) AND HcmLookupPEO.LOOKUP_TYPE='PAY_TASK_TYPE' ) TASK_TYPE_MEANING, decode((select 1 from dual where PT.BASE_TASK_NAME in('ORA_PAY_HSDL_LOAD','ORA_HDL_GENERATE_FILE_IMPORT','LOAD_BATCH','ORA_PAY_HDL_LOAD')),1,'DATA_LOADER', decode(nvl(PFI.FI_TASK_TYPE,decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE)),'TASK_FLOW', decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE),nvl(PFI.FI_TASK_TYPE,decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE)))) TASK_TYPE_CODE, PSFA.PROCESS_DATE, PSFA.PAYROLL_ID, PP.PAYROLL_NAME, PFTI.CREATED_BY SUBMITTED_BY, PSFA.CREATION_DATE SUBMISSION_DATE, NVL(PSFA.TOTAL_COMPLETED,0) RECORD, NVL(PSFA.ERROR_MESSAGE_COUNT,0) MESSAGES , TRUNC(SYSDATE)- TRUNC(PFTI.CREATION_DATE) TIME_ELAPSED, PSFA.LOGGING_LEVEL LOGGING_TURNED_ON, (select max(TimePeriodPEO.period_name) from PAY_TIME_PERIODS TimePeriodPEO, pay_payroll_actions payrollactioneo where payrollactioneo.earn_time_period_id =TimePeriodPEO.TIME_PERIOD_ID(+) and payrollactioneo.payroll_action_id=PSFA.payroll_action_id and pp.payroll_id=TimePeriodPEO.payroll_id) PERIOD_NAME, (select max(TimePeriodPEO.PERIOD_NUM) from PAY_TIME_PERIODS TimePeriodPEO, pay_payroll_actions payrollactioneo where payrollactioneo.dedn_time_period_id =TimePeriodPEO.TIME_PERIOD_ID(+) and payrollactioneo.payroll_action_id=PSFA.payroll_action_id and pp.payroll_id=TimePeriodPEO.payroll_id) STATUTORY_PERIOD, NULL DATE_EARNED, LDG.NAME LEGISLATIVE_DATA_GROUP_NAME, PFI.LEGISLATIVE_DATA_GROUP_ID, PT.BASE_TASK_NAME, decode((select 1 from hcm_lookups where lookup_type='ORA_PAY_ITR_PROC' and lookup_code=PT.BASE_TASK_NAME) ,1,'Y','N') SUBMIT_ANOTHER_TASK, PT.TASK_NAME, DECODE(PT.BASE_TASK_NAME,'QUICK_PAY','Y','QUICK_PREPAYMENT','Y','QUICK_PREPAYMENT_SIMPLIFIED','Y', 'QUICK_PAY_SIMPLIFIED','Y','QUICK_PAY_NEW','Y','QUICK_PREPAYMENT_NEW','Y','N') QUICKPAY_TASK_FLAG, PSFA.PAYROLL_ACTION_ID, to_char(ABS(PSFA.REQUEST_ID)) ESS_REQUEST_ID, PSFA.TI_REQUEST_ID, PSFA.Stat_error_total, PFTI.LAST_UPDATE_DATE, PSFA.UNPROCESSED, PFTI.IS_CANCELLING, PSFA.ROLLBACK_ERROR_FLAG FROM FUSION.PAY_STATS_FLOW_ACTIONS PSFA, PAY_FLOW_TASKS_VL PFT, PAY_TASKS_VL PT, PAY_FLOWS_VL PF, PAY_FLOW_INSTANCES PFI, PAY_ALL_PAYROLLS_F PP, PAY_FLOW_TASK_INSTANCES PFTI, PER_LEGISLATIVE_DATA_GROUPS_VL LDG, (select (select max('Y') from PAY_PAYROLL_ACTIONS ppa, pay_upgrade_objects puo where puo.upgrade_object_base_name ='UPG_FLOW_STATS' and ppa.parameter1 =to_char(puo.UPGRADE_OBJECT_ID) and ACTION_TYPE='UPGP' and ACTION_STATUS='C' and ppa.payment_reason = 'V1') upgrade_flag from dual) zzz WHERE PSFA.FLOW_TASK_ID = PFT.FLOW_TASK_ID AND PSFA.TASK_ID=PT.TASK_ID AND (PT.TASK_TYPE != 'MANUAL_TASK' OR PT.BASE_TASK_NAME IN('QUICK_PAY_SIMPLIFIED','QUICK_PAY_NEW')) AND PF.FLOW_ID = PSFA.FLOW_PATTERN_ID AND PFI.FLOW_INSTANCE_ID=PSFA.FLOW_INSTANCE_ID AND PSFA.PAYROLL_ID=PP.PAYROLL_ID(+) AND PFI.FLOW_INSTANCE_ID=PFTI.FLOW_INSTANCE_ID AND PFTI.BASE_FLOW_TASK_ID=PFT.BASE_FLOW_TASK_ID AND PFI.LEGISLATIVE_DATA_GROUP_ID=LDG.LEGISLATIVE_DATA_GROUP_ID (+) AND PFI.CREATION_DATE BETWEEN PP.EFFECTIVE_START_DATE(+) AND PP.EFFECTIVE_END_DATE(+) and (psfa.active_flag='Y' or (zzz.upgrade_flag is null and not exists (select null from FUSION.PAY_STATS_FLOW_ACTIONS aaa where aaa.flow_instance_id = psfa.flow_instance_id and aaa.flow_task_instance_id=psfa.flow_task_instance_id and aaa.active_flag = 'Y') and psfa.TI_REQUEST_ID\|\|psfa.REQUEST_ID = (select max(aaa.TI_REQUEST_ID\|\|aaa.REQUEST_ID) from FUSION.PAY_STATS_FLOW_ACTIONS aaa where aaa.flow_instance_id = psfa.flow_instance_id and aaa.flow_task_instance_id=psfa.flow_task_instance_id and nvl(aaa.active_flag,'N') != 'Y' ))) ORDER BY PFTI.LAST_UPDATE_DATE DESC

SQL_Statement

SELECT PF.FLOW_ID,

PT.TASK_TYPE,

PSFA.TASK_ID,

PSFA.DATA_SET_ID,

PSFA.FILE_NAME DATA_SET_NAME,

PSFA.FLOW_INSTANCE_ID,PSFA.FLOW_TASK_INSTANCE_ID,

PSFA.FLOW_NAME FLOW_INSTANCE_NAME, PFT.FLOW_TASK_NAME, PF.FLOW_NAME,PF.BASE_FLOW_ID,

PFTI.UI_TASK_STATUS TASK_STATUS,

(SELECT MAX(MEANING) FROM HCM_LOOKUPS

WHERE LOOKUP_CODE=PFTI.UI_TASK_STATUS

and LOOKUP_TYPE='ORA_FLOW_UI_STATUS') TASK_STATUS_MEANING,

PFTI.UI_TASK_STATUS TASK_SUB_STATUS,

(SELECT MAX(MEANING) FROM HCM_LOOKUPS

WHERE LOOKUP_CODE=PFTI.UI_TASK_STATUS

and LOOKUP_TYPE='ORA_FLOW_UI_STATUS') TASK_SUB_STATUS_MEANING,

decode(PFTI.UI_TASK_STATUS,'IN_PROGRESS','Y','N') IS_PROCESSING,

decode(PFTI.UI_TASK_STATUS,'COMPLETED','Y','N') IS_COMPLETED,

decode(PFTI.UI_TASK_STATUS,'COMPLETED','N',decode(PFTI.IS_ERROR,'Y','Y','N')) IS_ERRORS,

decode(PFTI.UI_TASK_STATUS,'COMPLETED','N',decode(PFTI.IS_MFR,'Y','Y','N')) IS_Marked_for_Retry,

PFI.FI_ROLLEDBACK IS_Rolled_Back_Records,

decode(PFTI.UI_TASK_STATUS,'CRITICAL_ALERTS',decode(PFTI.IS_ERROR,'Y','Y','N'),'N') IS_Critical_Errors,

decode(PFTI.UI_TASK_STATUS,'CRITICAL_ALERTS',decode(PFTI.IS_ERROR,'Y','N','Y'),'N') IS_Stopped,

decode(PFTI.STATUS,'SKIPPED','Y','N') IS_Skipped,

(SELECT HcmLookupPEO.MEANING FROM HCM_LOOKUPS HcmLookupPEO

WHERE HcmLookupPEO.LOOKUP_CODE= decode((select 1 from dual where PT.BASE_TASK_NAME in('ORA_PAY_HSDL_LOAD','ORA_HDL_GENERATE_FILE_IMPORT','LOAD_BATCH','ORA_PAY_HDL_LOAD')),1,'DATA_LOADER',

decode(nvl(PFI.FI_TASK_TYPE,decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE)),'TASK_FLOW',

decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE),nvl(PFI.FI_TASK_TYPE,decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE))))

AND HcmLookupPEO.LOOKUP_TYPE='PAY_TASK_TYPE' ) TASK_TYPE_MEANING,

decode((select 1 from dual where PT.BASE_TASK_NAME in('ORA_PAY_HSDL_LOAD','ORA_HDL_GENERATE_FILE_IMPORT','LOAD_BATCH','ORA_PAY_HDL_LOAD')),1,'DATA_LOADER',

decode(nvl(PFI.FI_TASK_TYPE,decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE)),'TASK_FLOW',

decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE),nvl(PFI.FI_TASK_TYPE,decode(PT.TASK_TYPE,'PUYGEN','PROCESS','PYUGEN','PROCESS',PT.TASK_TYPE)))) TASK_TYPE_CODE,

PSFA.PROCESS_DATE,

PSFA.PAYROLL_ID,

PP.PAYROLL_NAME,

PFTI.CREATED_BY SUBMITTED_BY,

PSFA.CREATION_DATE SUBMISSION_DATE,

NVL(PSFA.TOTAL_COMPLETED,0) RECORD,

NVL(PSFA.ERROR_MESSAGE_COUNT,0) MESSAGES ,

TRUNC(SYSDATE)- TRUNC(PFTI.CREATION_DATE) TIME_ELAPSED,

PSFA.LOGGING_LEVEL LOGGING_TURNED_ON,

(select max(TimePeriodPEO.period_name)

from PAY_TIME_PERIODS TimePeriodPEO, pay_payroll_actions payrollactioneo

where payrollactioneo.earn_time_period_id =TimePeriodPEO.TIME_PERIOD_ID(+)

and payrollactioneo.payroll_action_id=PSFA.payroll_action_id

and pp.payroll_id=TimePeriodPEO.payroll_id) PERIOD_NAME,

(select max(TimePeriodPEO.PERIOD_NUM)

from PAY_TIME_PERIODS TimePeriodPEO, pay_payroll_actions payrollactioneo

where payrollactioneo.dedn_time_period_id =TimePeriodPEO.TIME_PERIOD_ID(+)

and payrollactioneo.payroll_action_id=PSFA.payroll_action_id

and pp.payroll_id=TimePeriodPEO.payroll_id) STATUTORY_PERIOD,

NULL DATE_EARNED,

LDG.NAME LEGISLATIVE_DATA_GROUP_NAME,

PFI.LEGISLATIVE_DATA_GROUP_ID,

PT.BASE_TASK_NAME,

decode((select 1 from hcm_lookups where lookup_type='ORA_PAY_ITR_PROC' and lookup_code=PT.BASE_TASK_NAME)

,1,'Y','N') SUBMIT_ANOTHER_TASK,

PT.TASK_NAME,

DECODE(PT.BASE_TASK_NAME,'QUICK_PAY','Y','QUICK_PREPAYMENT','Y','QUICK_PREPAYMENT_SIMPLIFIED','Y',

'QUICK_PAY_SIMPLIFIED','Y','QUICK_PAY_NEW','Y','QUICK_PREPAYMENT_NEW','Y','N') QUICKPAY_TASK_FLAG,

PSFA.PAYROLL_ACTION_ID,

to_char(ABS(PSFA.REQUEST_ID)) ESS_REQUEST_ID,

PSFA.TI_REQUEST_ID,

PSFA.Stat_error_total,

PFTI.LAST_UPDATE_DATE,

PSFA.UNPROCESSED,

PFTI.IS_CANCELLING,

PSFA.ROLLBACK_ERROR_FLAG

FROM FUSION.PAY_STATS_FLOW_ACTIONS PSFA,

PAY_FLOW_TASKS_VL PFT,

PAY_TASKS_VL PT,

PAY_FLOWS_VL PF,

PAY_FLOW_INSTANCES PFI,

PAY_ALL_PAYROLLS_F PP,

PAY_FLOW_TASK_INSTANCES PFTI,

PER_LEGISLATIVE_DATA_GROUPS_VL LDG,

(select (select max('Y')

from PAY_PAYROLL_ACTIONS ppa,

pay_upgrade_objects puo

where puo.upgrade_object_base_name ='UPG_FLOW_STATS'

and ppa.parameter1 =to_char(puo.UPGRADE_OBJECT_ID)

and ACTION_TYPE='UPGP'

and ACTION_STATUS='C'

and ppa.payment_reason = 'V1') upgrade_flag

from dual) zzz

WHERE PSFA.FLOW_TASK_ID = PFT.FLOW_TASK_ID

AND PSFA.TASK_ID=PT.TASK_ID

AND (PT.TASK_TYPE != 'MANUAL_TASK' OR PT.BASE_TASK_NAME IN('QUICK_PAY_SIMPLIFIED','QUICK_PAY_NEW'))

AND PF.FLOW_ID = PSFA.FLOW_PATTERN_ID

AND PFI.FLOW_INSTANCE_ID=PSFA.FLOW_INSTANCE_ID

AND PSFA.PAYROLL_ID=PP.PAYROLL_ID(+)

AND PFI.FLOW_INSTANCE_ID=PFTI.FLOW_INSTANCE_ID

AND PFTI.BASE_FLOW_TASK_ID=PFT.BASE_FLOW_TASK_ID

AND PFI.LEGISLATIVE_DATA_GROUP_ID=LDG.LEGISLATIVE_DATA_GROUP_ID (+)

AND PFI.CREATION_DATE BETWEEN PP.EFFECTIVE_START_DATE(+) AND PP.EFFECTIVE_END_DATE(+)

and (psfa.active_flag='Y'

or (zzz.upgrade_flag is null

and not exists (select null from FUSION.PAY_STATS_FLOW_ACTIONS aaa where

aaa.flow_instance_id = psfa.flow_instance_id and aaa.flow_task_instance_id=psfa.flow_task_instance_id and aaa.active_flag = 'Y')

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

from FUSION.PAY_STATS_FLOW_ACTIONS aaa

where

aaa.flow_instance_id = psfa.flow_instance_id and aaa.flow_task_instance_id=psfa.flow_task_instance_id

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

ORDER BY PFTI.LAST_UPDATE_DATE DESC