PSB Pooled Position Report for Worksheet

Description
Categories: BI Publisher
Application: Public Sector Budgeting (Obsolete)
Source: Pooled Position Report for Worksheet (XML)
Short Name: PSBRPPPS_XML
DB package: PSB_PSBRPPPS_XMLP_PKG
SELECT
 WAL.CURRENCY_CODE "Currency Code"
,POS.NAME "Position"
,DECODE(WAL.POSITION_LINE_ID, NULL, NULL, DECODE(WEL.PAY_ELEMENT_NAME, NULL,
  DECODE( substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1),
'AMERICAN','Follow Salary Element', NULL), WEL.PAY_ELEMENT_NAME ) ) "Element Name"
,SP.SHORT_NAME "Service Package"
,GCC.CONCATENATED_SEGMENTS "GLAccount"
,SUM(WAL.YTD_AMOUNT)"Amount"
,BYTV.SEQUENCE_NUMBER
,BP.NAME "Year"
FROM  PSB_WS_ACCOUNT_LINES WAL ,
     PSB_BUDGET_PERIODS BP,
     PSB_SERVICE_PACKAGES SP,
     GL_CODE_COMBINATIONS_KFV GCC,
     PSB_WS_ELEMENT_LINE_NAMES_V WEL,
     PSB_POSITIONS POS,
     PSB_BUDGET_YEAR_TYPES_VL BYTV,
     PSB_WS_POSITION_LINES WPL,
     PSB_WS_LINES_POSITIONS WLP,
     PSB_WORKSHEETS WS
WHERE BP.BUDGET_YEAR_TYPE_ID = BYTV.BUDGET_YEAR_TYPE_ID
AND WAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND ((:P_BUDGET_YEAR_ID IS NOT NULL
     AND BP.BUDGET_PERIOD_ID = :P_BUDGET_YEAR_ID)
         OR (:P_BUDGET_YEAR_ID IS NULL))
AND WAL.BUDGET_YEAR_ID = BP.BUDGET_PERIOD_ID
AND WAL.SERVICE_PACKAGE_ID = SP.SERVICE_PACKAGE_ID
AND POS.HR_POSITION_ID IN
      (SELECT HR_POSITION_ID
           FROM PSB_POSITIONS
         WHERE DATA_EXTRACT_ID = WS.DATA_EXTRACT_ID
           GROUP BY HR_POSITION_ID
         HAVING COUNT(HR_POSITION_ID) > 1)
AND WAL.POSITION_LINE_ID = WEL.POSITION_LINE_ID(+)
AND WAL.ELEMENT_SET_ID = WEL.ELEMENT_SET_ID(+)
AND WAL.BUDGET_YEAR_ID = WEL.BUDGET_YEAR_ID(+)
AND WAL.END_STAGE_SEQ IS NULL
AND WAL.TEMPLATE_ID IS NULL
AND WAL.POSITION_LINE_ID = WLP.Position_Line_ID
AND POS.Position_ID = WPL.Position_ID
AND WPL.Position_Line_ID = WLP.Position_Line_ID
AND WLP.WORKSHEET_ID = :P_WORKSHEET_ID
AND WS.WORKSHEET_ID = :P_WORKSHEET_ID
GROUP BY
 BYTV.SEQUENCE_NUMBER
, BP.NAME
, WAL.CURRENCY_CODE
, POS.NAME
, DECODE(WAL.POSITION_LINE_ID, NULL, NULL, DECODE(WEL.PAY_ELEMENT_NAME, NULL,
  DECODE( substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1),
'AMERICAN','Follow Salary Element', NULL), WEL.PAY_ELEMENT_NAME ) )
,SP.SHORT_NAME
,GCC.CONCATENATED_SEGMENTS
ORDER BY BYTV.SEQUENCE_NUMBER, "Position","GLAccount","Service Package","Element Name"
Parameter Name SQL text Validation
Worksheet
 
LOV Oracle
Budget Year
 
LOV Oracle