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" |
