PSP Encumbrance History by Award
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Encumbrance History by Award Report
Application: Labor Distribution
Source: PSP: Encumbrance History by Award Report (XML)
Short Name: PSPENAWD_XML
DB package: PSP_PSPENAWD_XMLP_PKG
Description: Encumbrance History by Award Report
Application: Labor Distribution
Source: PSP: Encumbrance History by Award Report (XML)
Short Name: PSPENAWD_XML
DB package: PSP_PSPENAWD_XMLP_PKG
Run
PSP Encumbrance History by Award and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT pelh.payroll_id, gaa.award_number, ppf.full_name, paf.assignment_number, pelh.enc_element_type_id, pelh.hierarchy_code Charge_Type, Pelh.project_id, Pelh.expenditure_organization_id, Pelh.expenditure_type, Pelh.task_id, Pelh.award_id, greatest(greatest(:p_begin_date,pelh.hierarchy_start_date) ,min(paf.effective_start_date)) enc_begin_date, least(least(:p_end_date,pelh.hierarchy_end_date) ,max(paf.effective_end_date)) enc_end_date, SUM(pelh.encumbrance_amount), papf.payroll_name, petf.element_name, DECODE(pec.uom, 'M', currency.currency_code, 'STAT') currency_code, PSP_PSPENAWD_XMLP_PKG.cf_currency_formatformula(DECODE ( pec.uom , 'M' , currency.currency_code , 'STAT' )) CF_currency_format, PSP_PSPENAWD_XMLP_PKG.cf_currency_total_dspformula(:CS_currency_total, :CF_currency_format) CF_currency_total_dsp, PSP_PSPENAWD_XMLP_PKG.cf_award_total_dspformula(:CS_award_total, :CF_currency_format) CF_award_total_dsp, PSP_PSPENAWD_XMLP_PKG.cf_pay_total_dspformula(:CS_pay_total, :CF_currency_format) CF_pay_total_dsp, PSP_PSPENAWD_XMLP_PKG.cf_charging_instformula(Pelh.project_id, Pelh.task_id, Pelh.award_id, Pelh.expenditure_organization_id, Pelh.expenditure_type) CF_charging_inst, --PSP_PSPENAWD_XMLP_PKG.cf_pelh_enc_amount_dspformula(SUM ( pelh.encumbrance_amount ), :CF_currency_format) CF_pelh_enc_amount_dsp, SUM ( pelh.encumbrance_amount ) encumbered_amt, PSP_PSPENAWD_XMLP_PKG.cf_awd_end_dtformula(Pelh.award_id) CF_awd_end_dt, PSP_PSPENAWD_XMLP_PKG.cf_date_checkformula(:CF_awd_end_dt, least ( least ( :p_end_date , pelh.hierarchy_end_date ) , max ( paf.effective_end_date ) )) CF_Date_Check, PSP_PSPENAWD_XMLP_PKG.cf_end_date_rangeformula(greatest ( greatest ( :p_begin_date , pelh.hierarchy_start_date ) , min ( paf.effective_start_date ) ), least ( least ( :p_end_date , pelh.hierarchy_end_date ) , max ( paf.effective_end_date ) )) CF_end_date_range FROM psp_enc_lines_history pelh, Per_all_people_f ppf, Per_all_assignments_f paf , gms_awards_all gaa, psp_enc_summary_lines pesl, pay_all_payrolls_f papf, pay_element_types_f petf, psp_enc_controls pec, (SELECT psp_general.get_currency_code(:p_business_group_id) currency_code FROM DUAL) currency WHERE pelh.status_code <>'L' AND pesl.status_code <> 'S' AND pec.enc_control_id = pesl.enc_control_id AND pesl.enc_summary_line_id = pelh.enc_summary_line_id AND pelh.assignment_id = paf.assignment_id AND paf.person_id = ppf.person_id AND pelh.person_id = paf.person_id AND pelh.payroll_id = papf.payroll_id AND pelh.enc_element_type_id = petf.element_type_id AND pelh.encumbrance_date between petf.effective_start_date and petf.effective_end_date AND pelh.encumbrance_date between papf.effective_start_date AND papf.effective_end_date AND pelh.encumbrance_date between ppf.effective_Start_date and ppf.effective_End_date and pelh.enc_start_date <= paf.effective_end_date AND pelh.enc_end_date >= paf.effective_start_date AND pelh.award_id = gaa.award_id &p_award_id &p_payroll_id AND pelh.encumbrance_date BETWEEN :p_begin_date AND :p_end_date AND pelh.set_of_books_id = :p_set_of_books_id AND pelh.business_group_id = :p_business_group_id GROUP BY pelh.payroll_id, gaa.award_number, Pelh.award_id, ppf.full_name, paf.assignment_number, pelh.enc_element_type_id, pelh.hierarchy_code, Pelh.project_id, Pelh.expenditure_organization_id, Pelh.expenditure_type, Pelh.task_id, papf.payroll_name, pelh.hierarchy_start_date, pelh.hierarchy_end_date, paf.organization_id, petf.element_name, DECODE(pec.uom, 'M', currency.currency_code, 'STAT') order by 17,2,15,3,4,16,12,13,6 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Award Set |
|
LOV Oracle | |
Payroll Set |
|
LOV Oracle | |
Begin Date |
|
Date | |
End Date |
|
Date |