PSB Detailed FTE and Headcount by Position Report by Responsibility

Description
Categories: BI Publisher
Application: Public Sector Budgeting (Obsolete)
Source: Detailed FTE and Headcount by Position Report by Responsibility (XML)
Short Name: PSBRRFHP_XML
DB package: PSB_PSBRPFHP_XMLP_PKG
select bp.budget_period_id budget_period_id2, p.position_id position_id1, p.name, sum(fte.annual_fte)fte
from   psb_ws_fte_lines fte,
	   psb_ws_position_lines pl,
	   psb_ws_lines_positions lp,
	   psb_positions p,
	   psb_worksheets ws,
	   psb_budget_periods bp
where  pl.position_id = p.position_id
and      pl.position_line_id = lp.position_line_id
and      pl.position_line_id = fte.position_line_id
and      lp.worksheet_id = ws.worksheet_id
and      ws.budget_calendar_id = bp.budget_calendar_id
and  (:P_BUDGET_PERIOD_ID IS NULL OR fte.budget_year_id = :P_BUDGET_PERIOD_ID)
and      bp.budget_period_id = fte.budget_year_id
and      fte.end_stage_seq is null
and      lp.worksheet_id = :P_WORKSHEET_ID
and      pl.position_id in
    (select distinct position_id 
    from psb_position_assignments
    where (worksheet_id = :P_WORKSHEET_ID or worksheet_id IS NULL)
    and   (
              ((effective_start_date <= bp.end_date) and (effective_end_date is null))
                or  ((effective_start_date between bp.start_date and bp.end_date)
                or (effective_end_date between bp.start_date and bp.end_date)
                or ((effective_start_date < bp.start_date)
                       and (effective_end_date > bp.end_date)) )
             )
     ) 
 and bp.budget_period_id=:budget_period_id1
group by bp.budget_period_id, p.position_id, p.name
order by p.name
Parameter Name SQL text Validation
Worksheet
 
LOV Oracle
Budget Year
 
LOV Oracle