PSB Service Package Utilization

Description
Categories: BI Publisher
Application: Public Sector Budgeting (Obsolete)
Source: Service Package Utilization Report (XML)
Short Name: PSBRPSPU_XML
DB package: PSB_PSBRPSPU_XMLP_PKG
select a.wal_sp_package_id,a.name year_name,a.wal_budget_group_id,
            b.worksheet_id ,a.est_amt,a.stat_amt,bg.name bg_name
from
     psb_budget_groups bg,
(
SELECT  wal.budget_group_id wal_budget_group_id,
               wal.service_package_id wal_sp_package_id,
               p.name,
               sum(decode(balance_type,'E',nvl(YTD_AMOUNT,0),0))  EST_AMT,
                sum(decode(currency_code,'STAT',nvl(YTD_AMOUNT,0),0))  STAT_AMT
FROM  psb_budget_periods p,
           psb_ws_account_lines wal,
            psb_ws_lines wl
where
           wl.worksheet_id  = :sp_global_worksheet_id and
           wl.account_line_id = wal.account_line_id and
           p.budget_period_id = wal.budget_year_id  and
          (
            (:cp_sequence_number IS NULL AND end_stage_seq IS NULL) OR
            (:cp_sequence_number BETWEEN start_stage_seq AND
                                                             NVL(end_stage_seq,start_stage_seq)
           ))
group by wal.service_package_id,wal.budget_group_id,p.name
) a,
( select ws.worksheet_id,ws.budget_group_id
   from psb_worksheets ws
  where ( ws.worksheet_id = :sp_global_worksheet_id or
               ws.global_worksheet_id = :sp_global_worksheet_id ) ) b
where a.wal_budget_group_id = b.budget_group_id(+)
     and a.wal_budget_group_id = bg.budget_group_id
 and a.wal_sp_package_id=:sp_service_package_id
--order by a.wal_sp_package_id,a.wal_budget_group_id,a.name
ORDER BY 7 ASC,4 ASC,1 ASC , a.wal_sp_package_id , a.wal_budget_group_id , a.name
Parameter Name SQL text Validation
Worksheet
 
LOV Oracle
Budget Stage
 
LOV Oracle
Service Package
 
LOV Oracle
Sort By
 
LOV Oracle