PSP Encumbrance process run results

 select enc.enc_type,
        haou.name org_name,
        ppf.full_name person_name,
        enc.assignment_number,
        petf.element_name,
        pay.payroll_name,        
        enc.charge_code charge_type,
        enc.gl_code_combination_id,
        enc.project_id,
        enc.task_id,
        enc.award_id,
        enc.expenditure_type,
        enc.expenditure_organization_id,
        to_char(enc.Start_date, &G_icx_date_mask) start_date,
        to_char(enc.End_date, &G_icx_date_mask) end_date,
        enc.enc_total_amt,
        enc.liq_total_amt, 
	  enc.CF_Chargin_inst,
        decode(enc.charge_code, 'SA', pre.error_message, 'DA', pre.error_message, null) asg_error_message,
        enc.charge_code,
        pre.error_sequence_id,
        enc.start_date sort_start_date,
        enc.end_date sort_end_date,
        pre.information4 err_start_date,
        pre.information4 err_end_date,
	enc.UOM
from (
select  1 enc_type,
        pel.hierarchy_code charge_code,
        pel.gl_code_combination_id,
        pel.project_id,
        pel.task_id,
        pel.award_id,
        pel.enc_element_type_id element_type_id,
        pel.payroll_id,
        pel.assignment_id,
        pel.expenditure_type,
        pel.expenditure_organization_id,
        min(pel.enc_start_date) Start_date,
        max(pel.enc_end_date) End_date,
        sum(decode(pel.dr_cr_flag, 'D', encumbrance_amount, -1 * encumbrance_amount) ) enc_total_amt,
        null liq_total_amt, 
        PSP_ENC_CRT_XML.cf_charging_instformula(pel.gl_code_combination_id, pel.project_id, pel.task_id, 
            pel.award_id, pel.expenditure_organization_id, pel.expenditure_type) CF_Chargin_inst,
        paaf.assignment_number,
        pel.person_id,
        paaf.organization_id,
	pec.uom
from  psp_enc_lines pel,
      per_all_assignments_f paaf,
	psp_enc_controls pec
where pel.payroll_action_id  = :p_payroll_Action_id
  and pec.enc_control_id = pel.enc_control_id
  and pel.enc_end_date between paaf.effective_start_date and paaf.effective_end_date
  and pel.assignment_id = paaf.assignment_id
group by pel.gl_code_combination_id,
         pel.project_id,
         pel.task_id,
         pel.award_id,
         pel.expenditure_type,
         pel.expenditure_organization_id,
         pel.hierarchy_code,
         pel.enc_element_type_id,
         pel.payroll_id,
         pel.assignment_id,
         pel.hierarchy_start_date,
         pel.hierarchy_end_date,
         paaf.organization_id,
         pel.person_id,
         paaf.assignment_number,
	pec.uom) enc,
   psp_report_errors pre,
   per_all_people_f ppf,
   pay_element_types_f petf,
   hr_all_organization_units haou,
   pay_all_payrolls_f pay
where enc.assignment_id = pre.source_id(+)
  and pre.payroll_action_id(+) = :p_payroll_Action_id
  and fnd_date.canonical_to_date(pre.information4(+)) <=  enc.end_date
  and fnd_date.canonical_to_date(pre.information5(+)) >=  enc.start_date  
  and pre.value1(+) = enc.payroll_id
  and pre.value3(+) = enc.element_type_id
  and pre.information6(+) = enc.charge_code
  and enc.end_date between pay.effective_start_date and pay.effective_end_date
  and enc.end_date between ppf.effective_start_date and ppf.effective_end_date
  and enc.end_date between petf.effective_start_date and petf.effective_end_date
  and enc.payroll_id = pay.payroll_id
  and enc.element_type_id = petf.element_type_id
  and enc.organization_id = haou.organization_id
  and enc.person_id = ppf.person_id
  UNION ALL
   select  enc.enc_type,
        haou.name org_name,
        ppf.full_name person_name,
        enc.assignment_number,
        petf.element_name,
        pay.payroll_name,        
        enc.charge_code charge_type,
        enc.gl_code_combination_id,
        enc.project_id,
        enc.task_id,
        enc.award_id,
        enc.expenditure_type,
        enc.expenditure_organization_id,
        to_char(enc.Start_date, &G_icx_date_mask) start_date,
        to_char(enc.End_date, &G_icx_date_mask) end_date,
        enc.enc_total_amt,
        enc.liq_total_amt, 
	    enc.CF_Chargin_inst,
        null asg_error_message,
        'NA' charge_code,
        null error_sequence_id,
        enc.start_date sort_start_date,
        enc.end_date sort_end_date,
        null err_start_date,
        null err_end_date,
	enc.UOM
from (
select  decode(pesl.update_flag,'U',2,'L',3) enc_type,
        pelh.hierarchy_code charge_code,
        pelh.gl_code_combination_id,
        pelh.project_id,
        pelh.task_id,
        pelh.award_id,
        pelh.enc_element_type_id element_type_id,
        pelh.payroll_id,
        pelh.assignment_id,
        pelh.expenditure_type,
        pelh.expenditure_organization_id,
        min(pelh.enc_start_date) Start_date,
        max(pelh.enc_end_date) End_date,
        null enc_total_amt,
        decode(sum(decode(pesl.dr_cr_flag, 'C', pelh.encumbrance_amount, -1 * pelh.encumbrance_amount)), 0, sum(null),
               sum(decode(pesl.dr_cr_flag, 'C', pelh.encumbrance_amount, -1 * pelh.encumbrance_amount))) liq_total_amt,
        PSP_ENC_CRT_XML.cf_charging_instformula(pelh.gl_code_combination_id, pelh.project_id, pelh.task_id, pelh.award_id, 
        pelh.expenditure_organization_id, pelh.expenditure_type) CF_Chargin_inst,
        pesl.person_id,
        paaf.organization_id,
        paaf.assignment_number,
	pec.uom
from  psp_enc_lines_history pelh,
      psp_enc_summary_lines pesl,
      per_all_assignments_f paaf,
	psp_enc_controls pec
where pesl.payroll_action_id  = :p_payroll_Action_id
  and pesl.superceded_line_id = pelh.enc_summary_line_id
  and pec.enc_control_id = pesl.enc_control_id
  and pelh.enc_end_date between paaf.effective_start_date and paaf.effective_end_date
  and pesl.assignment_id = paaf.assignment_id
group by pelh.hierarchy_code,
         pelh.gl_code_combination_id,
         pelh.project_id,
         pelh.task_id,
         pelh.award_id,
         pelh.expenditure_type,
         pelh.expenditure_organization_id,
         pelh.enc_element_type_id,
         pelh.payroll_id,
         pelh.assignment_id,
         pelh.hierarchy_start_date,
         pelh.hierarchy_end_date,
         paaf.organization_id,
         pesl.person_id,
         paaf.assignment_number,
         pesl.update_flag,
	pec.uom) enc,
   per_all_people_f ppf,
   pay_element_types_f petf,
   hr_all_organization_units haou,
   pay_all_payrolls_f pay
where enc.end_date between pay.effective_start_date and pay.effective_end_date
  and enc.end_date between ppf.effective_start_date and ppf.effective_end_date
  and enc.end_date between petf.effective_start_date and petf.effective_end_date
  and enc.payroll_id = pay.payroll_id
  and enc.element_type_id = petf.element_type_id
  and enc.organization_id = haou.organization_id
  and enc.person_id = ppf.person_id
order by org_name, payroll_name, UOM, person_name, assignment_number, enc_type,
         element_name, sort_Start_date, sort_End_date, charge_type,
         err_start_date, err_end_date