Wednesday, 24 January 2018

Payroll Batch Loader Error Query



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

No comments:

Post a Comment