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 |