VIEW: APPS.PSB_WS_ELEMENT_LINES_V

Source

View Text - Preformatted

SELECT WEL.POSITION_LINE_ID, WEL.SERVICE_PACKAGE_ID, SP.SHORT_NAME, WEL.PAY_ELEMENT_ID, PE.NAME, sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC1, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC2, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC3, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC4, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC5, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC6, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC7, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC8, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC9, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC10, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC11, WEL.ELEMENT_COST, 0)), sum(decode(WEL.BUDGET_YEAR_ID, PSB_WS_ELEMENT_SETUP.Get_WS_Element_YearC12, WEL.ELEMENT_COST, 0)) from psb_ws_element_lines WEL, psb_budget_periods A, PSB_BUDGET_YEAR_TYPES B, PSB_PAY_ELEMENTS PE, PSB_SERVICE_PACKAGES SP Where WEL.BUDGET_YEAR_ID = A.BUDGET_PERIOD_ID and A.BUDGET_YEAR_TYPE_ID = B.BUDGET_YEAR_TYPE_ID and B.Year_Category_type in ('CY', 'PP') and WEL.PAY_ELEMENT_ID = PE.PAY_ELEMENT_ID and WEL.SERVICE_PACKAGE_ID = SP.SERVICE_PACKAGE_ID(+) AND ( ( PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST = 0 AND WEL.END_STAGE_SEQ IS NULL) OR ( PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST BETWEEN WEL.START_STAGE_SEQ AND NVL(WEL.END_STAGE_SEQ, 9.99E125) ) ) Group By WEL.POSITION_LINE_ID, WEL.SERVICE_PACKAGE_ID, SP.SHORT_NAME, WEL.PAY_ELEMENT_ID, PE.NAME

View Text - HTML Formatted

SELECT WEL.POSITION_LINE_ID
, WEL.SERVICE_PACKAGE_ID
, SP.SHORT_NAME
, WEL.PAY_ELEMENT_ID
, PE.NAME
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC1
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC2
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC3
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC4
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC5
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC6
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC7
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC8
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC9
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC10
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC11
, WEL.ELEMENT_COST
, 0))
, SUM(DECODE(WEL.BUDGET_YEAR_ID
, PSB_WS_ELEMENT_SETUP.GET_WS_ELEMENT_YEARC12
, WEL.ELEMENT_COST
, 0))
FROM PSB_WS_ELEMENT_LINES WEL
, PSB_BUDGET_PERIODS A
, PSB_BUDGET_YEAR_TYPES B
, PSB_PAY_ELEMENTS PE
, PSB_SERVICE_PACKAGES SP
WHERE WEL.BUDGET_YEAR_ID = A.BUDGET_PERIOD_ID
AND A.BUDGET_YEAR_TYPE_ID = B.BUDGET_YEAR_TYPE_ID
AND B.YEAR_CATEGORY_TYPE IN ('CY'
, 'PP')
AND WEL.PAY_ELEMENT_ID = PE.PAY_ELEMENT_ID
AND WEL.SERVICE_PACKAGE_ID = SP.SERVICE_PACKAGE_ID(+)
AND ( ( PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST = 0
AND WEL.END_STAGE_SEQ IS NULL) OR ( PSB_WS_MATRIX.GET_WS_LINE_YEAR_ST BETWEEN WEL.START_STAGE_SEQ
AND NVL(WEL.END_STAGE_SEQ
, 9.99E125) ) ) GROUP BY WEL.POSITION_LINE_ID
, WEL.SERVICE_PACKAGE_ID
, SP.SHORT_NAME
, WEL.PAY_ELEMENT_ID
, PE.NAME