View: PSB_WS_YEAR_POSITION_AMOUNTS_V

Product:	PSB - Public Sector Budgeting
Description:
Implementation/DBA Data:	APPS.PSB_WS_YEAR_POSITION_AMOUNTS_V

View Text

SELECT WL.WORKSHEET_ID
, WAL.CODE_COMBINATION_ID
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.TEMPLATE_ID
, WAL.ACCOUNT_TYPE
, WAL.CURRENCY_CODE
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC1(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC1(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC2(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC2(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC3(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC3(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC4(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC4(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC5(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC5(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC6(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC6(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC7(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC7(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC8(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC8(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC9(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC9(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC10(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC10(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC11(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC11(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC12(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 'P'
, WAL.ACCOUNT_LINE_ID
, 0))
, SUM(DECODE(PSB_WS_MATRIX.GET_WS_LINE_YEARC12(WAL.BUDGET_YEAR_ID
, WAL.BALANCE_TYPE)
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, NULL)
, 'P'
, ROUND(100*(WAL.YTD_AMOUNT/(PSB_WS_AMOUNTS_SETUP.WS_GET_TOTALS( WL.WORKSHEET_ID
, PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.BUDGET_YEAR_ID)))
, 2)
, 0))
FROM PSB_WS_LINES_POSITIONS WL
, PSB_WS_ACCOUNT_LINES WAL
WHERE WL.POSITION_LINE_ID = WAL.POSITION_LINE_ID
AND ( ( PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST = 0
AND WAL.END_STAGE_SEQ IS NULL) OR ( PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST BETWEEN WAL.START_STAGE_SEQ
AND NVL(WAL.END_STAGE_SEQ
, 9.99E125) ) ) GROUP BY WL.WORKSHEET_ID
, WAL.CODE_COMBINATION_ID
, WAL.POSITION_LINE_ID
, WAL.SERVICE_PACKAGE_ID
, WAL.ELEMENT_SET_ID
, WAL.TEMPLATE_ID
, WAL.ACCOUNT_TYPE
, WAL.CURRENCY_CODE
, WAL.STAGE_SET_ID

Columns