PSP Encumbrance History by Organization
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Encumbrance History by Organization Report
Application: Labor Distribution
Source: PSP: Encumbrance History by Organization Report (XML)
Short Name: PSPENORG_XML
DB package: PSP_PSPENORG_XMLP_PKG
Description: Encumbrance History by Organization Report
Application: Labor Distribution
Source: PSP: Encumbrance History by Organization Report (XML)
Short Name: PSPENORG_XML
DB package: PSP_PSPENORG_XMLP_PKG
Run
PSP Encumbrance History by Organization and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT pelh.payroll_id, hrou.name, ppf.full_name, paf.assignment_number, pelh.enc_element_type_id, pelh.hierarchy_code Charge_Type, pelh.gl_code_combination_id, 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) enc_amount, papf.payroll_name, petf.element_name, DECODE(pec.uom, 'M', currency.currency_code, 'STAT') currency_code, PSP_PSPENORG_XMLP_PKG.cf_currency_formatformula(DECODE ( pec.uom , 'M' , currency.currency_code , 'STAT' )) CF_currency_format, -- PSP_PSPENORG_XMLP_PKG.cf_currency_total_dspformula(DECODE ( pec.uom , 'M' , currency.currency_code , 'STAT' ), :CF_currency_format, :CS_currency_total) CF_currency_total_dsp, -- PSP_PSPENORG_XMLP_PKG.cf_org_total_dspformula(:CS_org_total, :CF_currency_format) CF_org_total_dsp, -- PSP_PSPENORG_XMLP_PKG.cf_pay_total_dispformula(:CS_pay_total, :CF_currency_format) CF_pay_total_disp, PSP_PSPENORG_XMLP_PKG.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_charging_inst, -- PSP_PSPENORG_XMLP_PKG.cf_enc_amount_dspformula(SUM ( pelh.encumbrance_amount ), :CF_currency_format) CF_enc_amount_dsp, PSP_PSPENORG_XMLP_PKG.cf_awd_end_dtformula(Pelh.award_id) CF_awd_end_dt, PSP_PSPENORG_XMLP_PKG.cf_date_checkformula(PSP_PSPENORG_XMLP_PKG.cf_awd_end_dtformula(Pelh.award_id), least ( least ( :p_end_date , pelh.hierarchy_end_date ) , max ( paf.effective_end_date ) )) CF_Date_Check, PSP_PSPENORG_XMLP_PKG.cf_enc_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_enc_date_range FROM psp_enc_lines_history pelh, Per_all_people_f ppf, Per_all_assignments_f paf , hr_all_organization_units hrou, 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 pelh.enc_summary_line_id = pesl.enc_summary_line_id AND pelh.assignment_id = paf.assignment_id AND paf.organization_id = hrou.organization_id AND paf.person_id = ppf.person_id AND pelh.person_id = paf.person_id AND pec.enc_control_id = pesl.enc_control_id 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 &p_organization_id &p_payroll_id AND pelh.encumbrance_date between :p_begin_date and :p_end_date AND pelh.encumbrance_date between petf.effective_start_date and petf.effective_end_date AND pelh.enc_element_type_id = petf.element_type_id AND pelh.payroll_id = papf.payroll_id AND pelh.encumbrance_date between papf.effective_start_date and papf.effective_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, hrou.name, ppf.full_name, paf.assignment_number, pelh.enc_element_type_id, pelh.hierarchy_code, pelh.gl_code_combination_id, Pelh.project_id, pelh.award_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 18 ASC,2 ASC,16 ASC,1 ASC , 17 , 2 , 16 , 3 , 4 , 17 , 13 , 14 , 6 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Organization Set |
|
LOV Oracle | |
Payroll Set |
|
LOV Oracle | |
Begin Date |
|
Date | |
End Date |
|
Date |