select ml.*
from pay_bl_message_lines_vl ml
, pay_batch_lines bl
, pay_batch_headers bh
where bh.batch_id = bl.batch_id
and bl.batch_line_id = ml.source_id
and bh.batch_name = 'BATCH NAME'
and bl.batch_line_status = 'E';
Full Query for all PBL errors with batch name, task name, task action name, assignment number and error message
SELECT pbh.batch_name,
pbh.legislative_data_group_id,pbt.display_task_name,pbta.display_task_action_name,
pivot_line_value_data.action_parameter_name1,pivot_line_value_data.action_parameter_value1,pivot_line_value_data.action_parameter_name2,pivot_line_value_data.action_parameter_value2,
pbmlv.message_text
FROM
(SELECT batch_line_id,
MAX(decode(rn,1,display_action_parameter_name))
action_parameter_name1,MAX(decode(rn,1,action_parameter_value))
action_parameter_value1,
MAX(decode(rn,2,display_action_parameter_name))
action_parameter_name2,MAX(decode(rn,2,action_parameter_value))
action_parameter_value2,
MAX(decode(rn,3,display_action_parameter_name))
action_parameter_name3,MAX(decode(rn,3,action_parameter_value))
action_parameter_value3,
MAX(decode(rn,4,display_action_parameter_name))
action_parameter_name4,MAX(decode(rn,4,action_parameter_value))
action_parameter_value4
FROM ( SELECT batch_line_id,
action_parameter_name,
action_parameter_value,
display_action_parameter_name,
row_number() over (partition by batch_line_id
order by batch_line_id,
action_parameter_name ) rn
FROM (SELECT pbavl.element_name action_parameter_name,
pbavl.parameter_name display_action_parameter_name,
pblv.action_parameter_value,
pblv.batch_line_id
FROM pay_task_parameters_vl pbavl, pay_batch_line_values pblv
WHERE pbavl.base_task_parameter_id = pblv.action_parameter_id
AND pbavl.display_flag <> 'N' )
)
GROUP BY batch_line_id) pivot_line_value_data,
pay_batch_lines pbl ,
pay_batch_headers pbh,
pay_bl_message_lines_vl pbmlv,
PAY_BL_TASK_ACTIONS_VL pbta,PAY_BL_TASKS_VL pbt
WHERE pivot_line_value_data.batch_line_id = pbl.batch_line_id
AND pbl.batch_id = pbh.batch_id
AND pbl.batch_line_id = pbmlv.source_id
AND pbl.batch_line_status = 'E'
AND pbh.batch_name like '%Pay%%%'
AND pbt.task_id=pbta.task_id
and pbl.task_action_id=pbta.task_action_id
