1. Tables and Views for HCM
  2. PAY_PROCESS_MESSAGES_VL

PAY_PROCESS_MESSAGES_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

MSG_SOURCE_ID

MESSAGE_LEVEL

MESSAGE_LEVEL_MEANING

MSG_SOURCE

LINE_SEQUENCE

OBJECT_ID

OBJECT_TYPE

PAYROLL_RELATIONSHIP_ID

PERSON_ID

OBJECT_NAME

OBJECT_NUMBER

PAYROLL_NAME

LEGISLATIVE_DATA_GROUP_ID

LEGISLATIVE_DATA_GROUP_NAME

FLOW_TASK_NAME

FLOW_INSTANCE_ID

TASK_INSTANCE_ID

MESSAGE

MESSAGE_TEXT

Query

SQL_Statement

SELECT Msg_Source_Id,

MESSAGE_LEVEL ,

SOURCE_TYPE message_level_meaning,

Msg_Source,

line_sequence,

object_id,

OBJECT_TYPE,

payroll_relationship_id,

person_id,

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

FROM PER_PERSON_NAMES_F PNAME

WHERE PNAME.PERSON_ID = OBJECT_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

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

AND PPNF.NAME_TYPE= 'GLOBAL')),

'mycsp_mt_id',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME

WHERE PNAME.PERSON_ID = OBJECT_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

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

AND PPNF.NAME_TYPE= 'GLOBAL')),

'PAYROLL_RELATIONSHIP_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME, PAY_PAY_RELATIONSHIPS_DN REL

WHERE REL.PAYROLL_RELATIONSHIP_ID = OBJECT_ID

AND PNAME.PERSON_ID = REL.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

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

AND PPNF.PERSON_ID = RELL.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),

'HR_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME, PER_ALL_ASSIGNMENTS_F ASG

WHERE ASG.ASSIGNMENT_ID = OBJECT_ID

AND ASG.PERSON_ID=PNAME.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

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

WHERE ASGG.ASSIGNMENT_ID = OBJECT_ID

AND ASGG.PERSON_ID=PPNF.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),

'PAY_ASSIGNMENT_ID',(SELECT PNAME.LIST_NAME LIST_NAME

FROM PER_PERSON_NAMES_F PNAME, PAY_PAY_RELATIONSHIPS_DN REL,PAY_REL_GROUPS_DN RG

WHERE RG.RELATIONSHIP_GROUP_ID = OBJECT_ID

AND REL.PAYROLL_RELATIONSHIP_ID=RG.PAYROLL_RELATIONSHIP_ID

AND RG.GROUP_TYPE='A'

AND PNAME.PERSON_ID = REL.PERSON_ID

AND PNAME.NAME_TYPE= 'GLOBAL'

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

WHERE RGG.RELATIONSHIP_GROUP_ID = OBJECT_ID

AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID

AND RGG.GROUP_TYPE='A'

AND PPNF.PERSON_ID = RELL.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL')),object_id ) OBJECT_NAME,

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

FROM per_all_people_f peo

WHERE peo.PERSON_ID = OBJECT_ID

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

'mycsp_mt_id',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo

WHERE peo.PERSON_ID = OBJECT_ID

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

'PAYROLL_RELATIONSHIP_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo, PAY_PAY_RELATIONSHIPS_DN REL

WHERE REL.PAYROLL_RELATIONSHIP_ID = OBJECT_ID

AND peo.PERSON_ID = REL.PERSON_ID

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

WHERE RELL.PAYROLL_RELATIONSHIP_ID = OBJECT_ID

AND papf.PERSON_ID = RELL.PERSON_ID)),

'HR_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo, PER_ALL_ASSIGNMENTS_F ASG

WHERE ASG.ASSIGNMENT_ID = OBJECT_ID

AND ASG.PERSON_ID=peo.PERSON_ID

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

WHERE ASGG.ASSIGNMENT_ID = OBJECT_ID

AND ASGG.PERSON_ID=papf.PERSON_ID)),

'PAY_ASSIGNMENT_ID',(SELECT peo.person_number PERSON_NUMBER

FROM per_all_people_f peo, PAY_PAY_RELATIONSHIPS_DN REL,PAY_REL_GROUPS_DN RG

WHERE RG.RELATIONSHIP_GROUP_ID = OBJECT_ID

AND REL.PAYROLL_RELATIONSHIP_ID=RG.PAYROLL_RELATIONSHIP_ID

AND RG.GROUP_TYPE='A'

AND peo.PERSON_ID = REL.PERSON_ID

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

WHERE RGG.RELATIONSHIP_GROUP_ID = OBJECT_ID

AND RELL.PAYROLL_RELATIONSHIP_ID=RGG.PAYROLL_RELATIONSHIP_ID

AND RGG.GROUP_TYPE='A'

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

(select max(payroll_name) from

PAY_ASSIGNED_PAYROLLS_DN ap,

pay_rel_groups_dn pt,

pay_all_payrolls_f ppp

where ap.PAYROLL_TERM_ID=pt.relationship_group_id

and pt.group_type='T'

and pt.PAYROLL_RELATIONSHIP_ID=PAYROLL_RELATIONSHIP_ID

and ppp.payroll_id=ap.payroll_id) payroll_name,

legislative_data_group_id,

(select name from

per_legislative_data_groups_vl ldg

where ldg.legislative_data_group_id= pay_process_messages.legislative_data_group_id) legislative_data_group_name,

PROCESS FLOW_TASK_NAME,

FLOW_INSTANCE_ID,

task_instance_id ,

MESSAGE,

MESSAGE_TEXT

FROM

(

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

ft.flow_task_name AS PROCESS,

NVL((SELECT MESSAGE_TEXT FROM FND_MESSAGES_VL FM WHERE FM.MESSAGE_NAME(+) =MessageLinePEO.Message_name),MessageLinePEO.Line_text)

||' '||MessageLinePEO.TOKEN1||' '||MessageLinePEO.TOKEN2||' '||MessageLinePEO.TOKEN3 AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_INSTANCE_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

rel.payroll_relationship_id object_id,

'PAYROLL_RELATIONSHIP_ID' OBJECT_TYPE,

rel.person_id,

rel.payroll_relationship_id,

rel.legislative_data_group_id,

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,

PAY_PAY_RELATIONSHIPS_DN rel,

FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO,

HCM_LOOKUPS HcmLookupPEO1,

PAY_MESSAGE_LINES MessageLinePEO,

PAY_requests prq,

pay_flow_task_instances pft,

pay_flow_tasks_vl ft

WHERE rel.payroll_relationship_id= PayrollRelationshipActionPEO.payroll_relationship_id

And 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

UNION

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

ft.flow_task_name AS PROCESS,

NVL((SELECT MESSAGE_TEXT FROM FND_MESSAGES_VL FM WHERE FM.MESSAGE_NAME(+) =MessageLinePEO.Message_name),MessageLinePEO.Line_text)

||' '||MessageLinePEO.TOKEN1||' '||MessageLinePEO.TOKEN2||' '||MessageLinePEO.TOKEN3 AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_INSTANCE_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

PayrollActionPEO.payroll_action_id object_id,

'PAYROLL_ACTION_ID' OBJECT_TYPE,

null person_id,

null payroll_relationship_id,

null legislative_data_group_id,

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 FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO,

HCM_LOOKUPS HcmLookupPEO1,

PAY_MESSAGE_LINES MessageLinePEO,

PAY_requests prq,

pay_flow_task_instances pft,

pay_flow_tasks_vl 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

UNION

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

ft.flow_task_name AS PROCESS,

NVL((SELECT MESSAGE_TEXT FROM FND_MESSAGES_VL FM WHERE FM.MESSAGE_NAME(+) =MessageLinePEO.Message_name),MessageLinePEO.Line_text)

||' '||MessageLinePEO.TOKEN1||' '||MessageLinePEO.TOKEN2||' '||MessageLinePEO.TOKEN3 AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_INSTANCE_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

ObjectAction.object_id,

ObjectAction.OBJECT_TYPE,

rel.person_id,

rel.payroll_relationship_id,

rel.legislative_data_group_id,

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 FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO,

pay_object_actions ObjectAction,

PAY_PAY_RELATIONSHIPS_DN rel,

HCM_LOOKUPS HcmLookupPEO1,

PAY_MESSAGE_LINES MessageLinePEO,

PAY_requests prq,

pay_flow_task_instances pft,

pay_flow_tasks_vl ft

WHERE rel.payroll_relationship_id= ObjectAction.object_id(+)

And 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

union

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

ft.flow_task_name AS PROCESS,

NVL((SELECT MESSAGE_TEXT FROM FND_MESSAGES_VL FM WHERE FM.MESSAGE_NAME(+) =MessageLinePEO.Message_name),MessageLinePEO.Line_text)

||' '||MessageLinePEO.TOKEN1||' '||MessageLinePEO.TOKEN2||' '||MessageLinePEO.TOKEN3 AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_INSTANCE_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

ObjectAction.object_id,

ObjectAction.OBJECT_TYPE,

rel.person_id,

rel.payroll_relationship_id,

rel.legislative_data_group_id,

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 FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO,

pay_temp_object_actions ObjectAction,

PAY_PAY_RELATIONSHIPS_DN rel,

HCM_LOOKUPS HcmLookupPEO1,

HCM_LOOKUPS HcmLookupPEO2,

PAY_MESSAGE_LINES MessageLinePEO,

PAY_requests prq,

pay_flow_task_instances pft,

pay_flow_tasks_vl ft

WHERE rel.payroll_relationship_id= ObjectAction.object_id(+)

And 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((SELECT MESSAGE_TEXT FROM FND_MESSAGES_VL FM WHERE FM.MESSAGE_NAME(+) =MessageLinePEO.Message_name),MessageLinePEO.Line_text)

||' '||MessageLinePEO.TOKEN1||' '||MessageLinePEO.TOKEN2||' '||MessageLinePEO.TOKEN3 AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_INSTANCE_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

pbh.batch_id object_id,

'BATCH' OBJECT_TYPE,

null person_id,

null payroll_relationship_id,

null legislative_data_group_id,

MessageLinePEO.SOURCE_ID AS Msg_Source_Id,

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,

ft.flow_task_name AS PROCESS,

NVL((SELECT MESSAGE_TEXT FROM FND_MESSAGES_VL FM WHERE FM.MESSAGE_NAME(+) =MessageLinePEO.Message_name),MessageLinePEO.Line_text)

||' '||MessageLinePEO.TOKEN1||' '||MessageLinePEO.TOKEN2||' '||MessageLinePEO.TOKEN3 AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_INSTANCE_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

pbh.batch_id object_id,

'BATCH' OBJECT_TYPE,

null person_id,

null payroll_relationship_id,

null legislative_data_group_id,

MessageLinePEO.SOURCE_ID AS Msg_Source_Id,

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

/* Bal Init*/

union

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

ft.flow_task_name AS PROCESS,

NVL((SELECT MESSAGE_TEXT FROM FND_MESSAGES_VL FM WHERE FM.MESSAGE_NAME(+) =MessageLinePEO.Message_name),MessageLinePEO.Line_text)

||' '||MessageLinePEO.TOKEN1||' '||MessageLinePEO.TOKEN2||' '||MessageLinePEO.TOKEN3 AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_INSTANCE_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

pbh.batch_id object_id,

'BATCH' OBJECT_TYPE,

null person_id,

null payroll_relationship_id,

null legislative_data_group_id,

MessageLinePEO.SOURCE_ID AS Msg_Source_Id,

pft.flow_task_instance_id task_instance_id,

MessageLinePEO.line_sequence ,

MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT

FROM PAY_BAL_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((SELECT MESSAGE_TEXT FROM FND_MESSAGES_VL FM WHERE FM.MESSAGE_NAME(+) =MessageLinePEO.Message_name),MessageLinePEO.Line_text)

||' '||MessageLinePEO.TOKEN1||' '||MessageLinePEO.TOKEN2||' '||MessageLinePEO.TOKEN3 AS MESSAGE,

MessageLinePEO.MESSAGE_LEVEL AS MESSAGE_LEVEL,

pft.flow_instance_id AS FLOW_INSTANCE_ID,

ft.category_type AS CATEGORY_TYPE,

MessageLinePEO.SOURCE_TYPE AS Msg_Source,

pbh.batch_id object_id,

'BATCH' OBJECT_TYPE,

null person_id,

null payroll_relationship_id,

null legislative_data_group_id,

MessageLinePEO.SOURCE_ID AS Msg_Source_Id,

pft.flow_task_instance_id task_instance_id,

MessageLinePEO.line_sequence,

MessageLinePEO.LINE_TEXT AS MESSAGE_TEXT

FROM PAY_BAL_BATCH_HEADERS pbh,

PAY_BAL_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 pt.base_task_name='BALANCE_INITIALIZATION'

and ppp.flow_task_param_value = to_char(pbh.batch_id)

AND ft.flow_task_id = pft.BASE_FLOW_TASK_ID

AND pbl.batch_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,

ft.flow_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_INSTANCE_ID,

null AS CATEGORY_TYPE,

'H' AS Msg_Source,

pbh.data_set_id object_id,

'BATCH' OBJECT_TYPE,

null person_id,

null payroll_relationship_id,

null legislative_data_group_id,

pbh.data_set_id AS Msg_Source_Id,

aaa.FLOW_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_STATS_FLOW_ACTIONS aaa,

pay_flow_tasks_vl ft

WHERE aaa.DATA_SET_ID = pbh.data_set_id

and aaa.FLOW_TASK_ID = ft.base_flow_task_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')

union

SELECT HcmLookupPEO1.meaning AS SOURCE_TYPE,

ft.flow_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 object_id,

'BATCH' OBJECT_TYPE,

null person_id,

null payroll_relationship_id,

null legislative_data_group_id,

pbh.data_set_id AS Msg_Source_Id,

aaa.FLOW_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_STATS_FLOW_ACTIONS aaa,

pay_flow_tasks_vl ft

WHERE aaa.DATA_SET_ID = pbh.data_set_id

and aaa.FLOW_TASK_ID = ft.base_flow_task_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')

) pay_process_messages