PAY_PAY_MSG_RESULTS

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
MESSAGE_LEVEL PROCESS MESSAGE MSG_SOURCE FLOW_ID CATEGORY_TYPE TASK_INSTANCE_ID MSG_SOURCE_ID LINE_SEQUENCE MESSAGE_LEVEL_MEANING MESSAGE_TEXT

Name

MESSAGE_LEVEL

PROCESS

MESSAGE

MSG_SOURCE

FLOW_ID

CATEGORY_TYPE

TASK_INSTANCE_ID

MSG_SOURCE_ID

LINE_SEQUENCE

MESSAGE_LEVEL_MEANING

MESSAGE_TEXT

Query

SQL_Statement
SELECT MESSAGE_LEVEL , PROCESS, MESSAGE, Msg_Source, FLOW_ID, CATEGORY_TYPE, task_instance_id , Msg_Source_Id, line_sequence, SOURCE_TYPE message_level_meaning, MESSAGE_TEXT FROM ( SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE, HcmLookupPEO2.MEANING AS PROCESS, NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE, MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL, pft.flow_instance_id AS FLOW_ID, ft.category_type AS CATEGORY_TYPE, MessageLinePEO.SOURCE_TYPE AS Msg_Source, MessageLinePEO.SOURCE_ID AS Msg_Source_Id, prq.flow_task_instance_id task_instance_id, MessageLinePEO.line_sequence , MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT FROM PAY_PAYROLL_REL_ACTIONS PayrollRelationshipActionPEO, FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO, HCM_LOOKUPS HcmLookupPEO1, HCM_LOOKUPS HcmLookupPEO2, PAY_MESSAGE_LINES MessageLinePEO, PAY_requests prq, pay_flow_task_instances pft, pay_flow_tasks ft WHERE pft.flow_task_instance_id = prq.flow_task_instance_id AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID AND prq.pay_request_id = payrollactionpeo.pay_request_id AND PayrollRelationshipActionPEO.PAYROLL_REL_ACTION_ID = MessageLinePEO.SOURCE_ID AND PayrollRelationshipActionPEO.PAYROLL_ACTION_ID = PayrollActionPEO.PAYROLL_ACTION_ID AND MessageLinePEO.SOURCE_TYPE = 'A' AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL' AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL AND HcmLookupPEO2.LOOKUP_TYPE = 'ACTION_TYPE' AND HcmLookupPEO2.LOOKUP_CODE = PayrollActionPEO.ACTION_TYPE UNION SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE, HcmLookupPEO2.MEANING AS PROCESS, NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE, MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL, pft.flow_instance_id AS FLOW_ID, ft.category_type AS CATEGORY_TYPE, MessageLinePEO.SOURCE_TYPE AS Msg_Source, MessageLinePEO.SOURCE_ID AS Msg_SourceId, prq.flow_task_instance_id task_instance_id, MessageLinePEO.line_sequence , MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT FROM FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO, HCM_LOOKUPS HcmLookupPEO1, HCM_LOOKUPS HcmLookupPEO2, PAY_MESSAGE_LINES MessageLinePEO, PAY_requests prq, pay_flow_task_instances pft, pay_flow_tasks ft WHERE pft.flow_task_instance_id = prq.flow_task_instance_id AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID AND prq.pay_request_id = payrollactionpeo.pay_request_id AND PayrollActionPEO.PAYROLL_ACTION_ID = MessageLinePEO.SOURCE_ID AND MessageLinePEO.SOURCE_TYPE = 'P' AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL' AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL AND HcmLookupPEO2.LOOKUP_TYPE = 'ACTION_TYPE' AND HcmLookupPEO2.LOOKUP_CODE = PayrollActionPEO.ACTION_TYPE UNION SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE, HcmLookupPEO2.MEANING AS PROCESS, NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE, MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL, pft.flow_instance_id AS FLOW_ID, ft.category_type AS CATEGORY_TYPE, MessageLinePEO.SOURCE_TYPE AS Msg_Source, MessageLinePEO.SOURCE_ID AS Msg_SourceId, prq.flow_task_instance_id task_instance_id, MessageLinePEO.line_sequence , MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT FROM FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO, pay_object_actions ObjectAction, HCM_LOOKUPS HcmLookupPEO1, HCM_LOOKUPS HcmLookupPEO2, PAY_MESSAGE_LINES MessageLinePEO, PAY_requests prq, pay_flow_task_instances pft, pay_flow_tasks ft WHERE pft.flow_task_instance_id = prq.flow_task_instance_id AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID AND ObjectAction.payroll_action_id=PayrollActionPEO.payroll_action_id AND prq.pay_request_id = payrollactionpeo.pay_request_id AND ObjectAction.object_action_id =MessageLinePEO.SOURCE_ID AND MessageLinePEO.SOURCE_TYPE = 'A' AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL' AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL AND HcmLookupPEO2.LOOKUP_TYPE = 'ACTION_TYPE' AND HcmLookupPEO2.LOOKUP_CODE = PayrollActionPEO.ACTION_TYPE union SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE, HcmLookupPEO2.MEANING AS PROCESS, NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE, MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL, pft.flow_instance_id AS FLOW_ID, ft.category_type AS CATEGORY_TYPE, MessageLinePEO.SOURCE_TYPE AS Msg_Source, MessageLinePEO.SOURCE_ID AS Msg_SourceId, prq.flow_task_instance_id task_instance_id, MessageLinePEO.line_sequence, MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT FROM FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO, pay_temp_object_actions ObjectAction, HCM_LOOKUPS HcmLookupPEO1, HCM_LOOKUPS HcmLookupPEO2, PAY_MESSAGE_LINES MessageLinePEO, PAY_requests prq, pay_flow_task_instances pft, pay_flow_tasks ft WHERE pft.flow_task_instance_id = prq.flow_task_instance_id AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID AND ObjectAction.payroll_action_id=PayrollActionPEO.payroll_action_id AND prq.pay_request_id = payrollactionpeo.pay_request_id AND ObjectAction.temp_object_action_id =MessageLinePEO.SOURCE_ID AND MessageLinePEO.SOURCE_TYPE = 'A' AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL' AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL AND HcmLookupPEO2.LOOKUP_TYPE = 'ACTION_TYPE' AND HcmLookupPEO2.LOOKUP_CODE = PayrollActionPEO.ACTION_TYPE union SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE, ft.flow_task_name AS PROCESS, NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE, MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL, pft.flow_instance_id AS FLOW_ID, ft.category_type AS CATEGORY_TYPE, MessageLinePEO.SOURCE_TYPE AS Msg_Source, MessageLinePEO.SOURCE_ID AS Msg_SourceId, pft.flow_task_instance_id task_instance_id, MessageLinePEO.line_sequence , MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT FROM pay_batch_headers pbh, HCM_LOOKUPS HcmLookupPEO1, PAY_MESSAGE_LINES MessageLinePEO, pay_flow_task_instances pft, pay_flow_tasks_vl ft, PAY_FLOW_TASK_PARAM_VALS ppp, pay_flow_task_parameters pftp, pay_task_parameters ptp WHERE ppp.flow_task_instance_id = pft.flow_task_instance_id 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) AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID AND pbh.batch_id =MessageLinePEO.SOURCE_ID AND MessageLinePEO.SOURCE_TYPE in ('G','H') AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL' AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL union SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE, pt.task_name AS PROCESS, NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE, MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL, pft.flow_instance_id AS FLOW_ID, ft.category_type AS CATEGORY_TYPE, MessageLinePEO.SOURCE_TYPE AS Msg_Source, MessageLinePEO.SOURCE_ID AS Msg_SourceId, pft.flow_task_instance_id task_instance_id, MessageLinePEO.line_sequence, MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT FROM pay_batch_headers pbh, pay_batch_lines pbl, HCM_LOOKUPS HcmLookupPEO1, PAY_MESSAGE_LINES MessageLinePEO, pay_flow_task_instances pft, pay_flow_tasks_vl ft, PAY_FLOW_TASK_PARAM_VALS ppp, pay_flow_task_parameters pftp, pay_task_parameters ptp, pay_tasks_vl pt WHERE ppp.flow_task_instance_id = pft.flow_task_instance_id and pt.base_task_id = ft.base_task_id 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) AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID AND pbl.batch_line_id =MessageLinePEO.SOURCE_ID AND MessageLinePEO.SOURCE_TYPE in ('G','L') AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL' AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL and pbl.batch_id=pbh.batch_id union SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE, aaa.task_name AS PROCESS, NVL(MessageLinePEO.msg_text,MessageLinePEO.stack_trace) AS MESSAGE, decode(MessageLinePEO.MESSAGE_TYPE,'ERROR','F','WARNING','F','I') AS MESSAGE_LEVEL, aaa.flow_instance_id AS FLOW_ID, null AS CATEGORY_TYPE, 'H' AS Msg_Source, pbh.data_set_id AS Msg_SourceId, aaa.task_instance_id, MessageLinePEO.message_line_id line_sequence, MessageLinePEO.msg_text AS MESSAGE_TEXT FROM HRC_DL_DATA_SETS pbh, HCM_LOOKUPS HcmLookupPEO1, HRC_DL_MESSAGE_LINES MessageLinePEO, PAY_TASK_STATUS_VL aaa WHERE aaa.hdl_batch_id = pbh.data_set_id and pbh.data_set_id = MessageLinePEO.MESSAGE_SOURCE_LINE_ID and MessageLinePEO.MESSAGE_SOURCE_TABLE_NAME = 'HRC_DL_DATA_SETS' AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL' AND HcmLookupPEO1.LOOKUP_CODE = decode(MessageLinePEO.MESSAGE_TYPE,'ERROR','F','WARNING','F','I') and aaa.module_name='ORA_DX_CHK' union SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE, aaa.task_name AS PROCESS, NVL(MessageLinePEO.msg_text,MessageLinePEO.stack_trace) AS MESSAGE, decode(MessageLinePEO.MESSAGE_TYPE,'ERROR','F','WARNING','F','I') AS MESSAGE_LEVEL, aaa.flow_instance_id AS FLOW_ID, null AS CATEGORY_TYPE, 'H' AS Msg_Source, pbh.data_set_id AS Msg_SourceId, aaa.task_instance_id, MessageLinePEO.message_line_id line_sequence, MessageLinePEO.msg_text AS MESSAGE_TEXT FROM HRC_DL_DATA_SETS pbh, HRC_DL_DATA_SET_BUS_OBJS pbl, HCM_LOOKUPS HcmLookupPEO1, HRC_DL_MESSAGE_LINES MessageLinePEO, pay_task_status_vl aaa WHERE aaa.hdl_batch_id = pbh.data_set_id and pbl.data_set_id = pbh.data_set_id and MessageLinePEO.DATA_SET_BUS_OBJ_ID = pbl.DATA_SET_BUS_OBJ_ID AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL' AND HcmLookupPEO1.LOOKUP_CODE = decode(MessageLinePEO.MESSAGE_TYPE,'ERROR','F','WARNING','F','I') and aaa.module_name='ORA_DX_CHK' ) pay_payroll_msg_results_v

SQL_Statement

SELECT MESSAGE_LEVEL ,

PROCESS,

MESSAGE,

Msg_Source,

FLOW_ID,

CATEGORY_TYPE,

task_instance_id ,

Msg_Source_Id,

line_sequence,

SOURCE_TYPE message_level_meaning,

MESSAGE_TEXT

FROM

(

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

HcmLookupPEO2.MEANING AS PROCESS,

NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

MessageLinePEO.SOURCE_ID AS Msg_Source_Id,

prq.flow_task_instance_id task_instance_id,

MessageLinePEO.line_sequence ,

MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT

FROM PAY_PAYROLL_REL_ACTIONS PayrollRelationshipActionPEO,

FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO,

HCM_LOOKUPS HcmLookupPEO1,

HCM_LOOKUPS HcmLookupPEO2,

PAY_MESSAGE_LINES MessageLinePEO,

PAY_requests prq,

pay_flow_task_instances pft,

pay_flow_tasks ft

WHERE pft.flow_task_instance_id = prq.flow_task_instance_id

AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID

AND prq.pay_request_id = payrollactionpeo.pay_request_id

AND PayrollRelationshipActionPEO.PAYROLL_REL_ACTION_ID = MessageLinePEO.SOURCE_ID

AND PayrollRelationshipActionPEO.PAYROLL_ACTION_ID = PayrollActionPEO.PAYROLL_ACTION_ID

AND MessageLinePEO.SOURCE_TYPE = 'A'

AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL'

AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL

AND HcmLookupPEO2.LOOKUP_TYPE = 'ACTION_TYPE'

AND HcmLookupPEO2.LOOKUP_CODE = PayrollActionPEO.ACTION_TYPE

UNION

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

HcmLookupPEO2.MEANING AS PROCESS,

NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

MessageLinePEO.SOURCE_ID AS Msg_SourceId,

prq.flow_task_instance_id task_instance_id,

MessageLinePEO.line_sequence ,

MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT

FROM FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO,

HCM_LOOKUPS HcmLookupPEO1,

HCM_LOOKUPS HcmLookupPEO2,

PAY_MESSAGE_LINES MessageLinePEO,

PAY_requests prq,

pay_flow_task_instances pft,

pay_flow_tasks ft

WHERE pft.flow_task_instance_id = prq.flow_task_instance_id

AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID

AND prq.pay_request_id = payrollactionpeo.pay_request_id

AND PayrollActionPEO.PAYROLL_ACTION_ID = MessageLinePEO.SOURCE_ID

AND MessageLinePEO.SOURCE_TYPE = 'P'

AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL'

AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL

AND HcmLookupPEO2.LOOKUP_TYPE = 'ACTION_TYPE'

AND HcmLookupPEO2.LOOKUP_CODE = PayrollActionPEO.ACTION_TYPE

UNION

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

HcmLookupPEO2.MEANING AS PROCESS,

NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

MessageLinePEO.SOURCE_ID AS Msg_SourceId,

prq.flow_task_instance_id task_instance_id,

MessageLinePEO.line_sequence ,

MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT

FROM FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO,

pay_object_actions ObjectAction,

HCM_LOOKUPS HcmLookupPEO1,

HCM_LOOKUPS HcmLookupPEO2,

PAY_MESSAGE_LINES MessageLinePEO,

PAY_requests prq,

pay_flow_task_instances pft,

pay_flow_tasks ft

WHERE pft.flow_task_instance_id = prq.flow_task_instance_id

AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID

AND ObjectAction.payroll_action_id=PayrollActionPEO.payroll_action_id

AND prq.pay_request_id = payrollactionpeo.pay_request_id

AND ObjectAction.object_action_id =MessageLinePEO.SOURCE_ID

AND MessageLinePEO.SOURCE_TYPE = 'A'

AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL'

AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL

AND HcmLookupPEO2.LOOKUP_TYPE = 'ACTION_TYPE'

AND HcmLookupPEO2.LOOKUP_CODE = PayrollActionPEO.ACTION_TYPE

union

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

HcmLookupPEO2.MEANING AS PROCESS,

NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

MessageLinePEO.SOURCE_ID AS Msg_SourceId,

prq.flow_task_instance_id task_instance_id,

MessageLinePEO.line_sequence,

MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT

FROM FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO,

pay_temp_object_actions ObjectAction,

HCM_LOOKUPS HcmLookupPEO1,

HCM_LOOKUPS HcmLookupPEO2,

PAY_MESSAGE_LINES MessageLinePEO,

PAY_requests prq,

pay_flow_task_instances pft,

pay_flow_tasks ft

WHERE pft.flow_task_instance_id = prq.flow_task_instance_id

AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID

AND ObjectAction.payroll_action_id=PayrollActionPEO.payroll_action_id

AND prq.pay_request_id = payrollactionpeo.pay_request_id

AND ObjectAction.temp_object_action_id =MessageLinePEO.SOURCE_ID

AND MessageLinePEO.SOURCE_TYPE = 'A'

AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL'

AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL

AND HcmLookupPEO2.LOOKUP_TYPE = 'ACTION_TYPE'

AND HcmLookupPEO2.LOOKUP_CODE = PayrollActionPEO.ACTION_TYPE

union

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

ft.flow_task_name AS PROCESS,

NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

MessageLinePEO.SOURCE_ID AS Msg_SourceId,

pft.flow_task_instance_id task_instance_id,

MessageLinePEO.line_sequence ,

MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT

FROM pay_batch_headers pbh,

HCM_LOOKUPS HcmLookupPEO1,

PAY_MESSAGE_LINES MessageLinePEO,

pay_flow_task_instances pft,

pay_flow_tasks_vl ft,

PAY_FLOW_TASK_PARAM_VALS ppp,

pay_flow_task_parameters pftp,

pay_task_parameters ptp

WHERE ppp.flow_task_instance_id = pft.flow_task_instance_id

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)

AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID

AND pbh.batch_id =MessageLinePEO.SOURCE_ID

AND MessageLinePEO.SOURCE_TYPE in ('G','H')

AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL'

AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL

union

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

pt.task_name AS PROCESS,

NVL(MessageLinePEO.Message_name,MessageLinePEO.Line_text) AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

MessageLinePEO.SOURCE_ID AS Msg_SourceId,

pft.flow_task_instance_id task_instance_id,

MessageLinePEO.line_sequence,

MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT

FROM pay_batch_headers pbh,

pay_batch_lines pbl,

HCM_LOOKUPS HcmLookupPEO1,

PAY_MESSAGE_LINES MessageLinePEO,

pay_flow_task_instances pft,

pay_flow_tasks_vl ft,

PAY_FLOW_TASK_PARAM_VALS ppp,

pay_flow_task_parameters pftp,

pay_task_parameters ptp,

pay_tasks_vl pt

WHERE ppp.flow_task_instance_id = pft.flow_task_instance_id

and pt.base_task_id = ft.base_task_id

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)

AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID

AND pbl.batch_line_id =MessageLinePEO.SOURCE_ID

AND MessageLinePEO.SOURCE_TYPE in ('G','L')

AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL'

AND HcmLookupPEO1.LOOKUP_CODE = MessageLinePEO.MESSAGE_LEVEL

and pbl.batch_id=pbh.batch_id

union

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

aaa.task_name AS PROCESS,

NVL(MessageLinePEO.msg_text,MessageLinePEO.stack_trace) AS MESSAGE,

decode(MessageLinePEO.MESSAGE_TYPE,'ERROR','F','WARNING','F','I') AS MESSAGE_LEVEL,

aaa.flow_instance_id AS FLOW_ID,

null AS CATEGORY_TYPE,

'H' AS Msg_Source,

pbh.data_set_id AS Msg_SourceId,

aaa.task_instance_id,

MessageLinePEO.message_line_id line_sequence,

MessageLinePEO.msg_text AS MESSAGE_TEXT

FROM HRC_DL_DATA_SETS pbh,

HCM_LOOKUPS HcmLookupPEO1,

HRC_DL_MESSAGE_LINES MessageLinePEO,

PAY_TASK_STATUS_VL aaa

WHERE aaa.hdl_batch_id = pbh.data_set_id

and pbh.data_set_id = MessageLinePEO.MESSAGE_SOURCE_LINE_ID

and MessageLinePEO.MESSAGE_SOURCE_TABLE_NAME = 'HRC_DL_DATA_SETS'

AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL'

AND HcmLookupPEO1.LOOKUP_CODE = decode(MessageLinePEO.MESSAGE_TYPE,'ERROR','F','WARNING','F','I')

and aaa.module_name='ORA_DX_CHK'

union

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

aaa.task_name AS PROCESS,

NVL(MessageLinePEO.msg_text,MessageLinePEO.stack_trace) AS MESSAGE,

decode(MessageLinePEO.MESSAGE_TYPE,'ERROR','F','WARNING','F','I') AS MESSAGE_LEVEL,

aaa.flow_instance_id AS FLOW_ID,

null AS CATEGORY_TYPE,

'H' AS Msg_Source,

pbh.data_set_id AS Msg_SourceId,

aaa.task_instance_id,

MessageLinePEO.message_line_id line_sequence,

MessageLinePEO.msg_text AS MESSAGE_TEXT

FROM HRC_DL_DATA_SETS pbh,

HRC_DL_DATA_SET_BUS_OBJS pbl,

HCM_LOOKUPS HcmLookupPEO1,

HRC_DL_MESSAGE_LINES MessageLinePEO,

pay_task_status_vl aaa

WHERE aaa.hdl_batch_id = pbh.data_set_id

and pbl.data_set_id = pbh.data_set_id

and MessageLinePEO.DATA_SET_BUS_OBJ_ID = pbl.DATA_SET_BUS_OBJ_ID

AND HcmLookupPEO1.LOOKUP_TYPE = 'PAY_MESSAGE_LEVEL'

AND HcmLookupPEO1.LOOKUP_CODE = decode(MessageLinePEO.MESSAGE_TYPE,'ERROR','F','WARNING','F','I')

and aaa.module_name='ORA_DX_CHK'

) pay_payroll_msg_results_v