VIEW: APPS.PSB_WS_YEAR_LINES_V

Source

View Text - Preformatted

SELECT WL.WORKSHEET_ID , WAL.BUDGET_GROUP_ID , WAL.CODE_COMBINATION_ID , WAL.SERVICE_PACKAGE_ID , SP.SHORT_NAME, WAL.TEMPLATE_ID , WAL.CURRENCY_CODE , CON.ACCOUNT_FLAG, WAL.ACCOUNT_TYPE , SUM(DECODE( NVL (WAL.POSITION_LINE_ID, 0) , 0, 0,1 ) ) POSITION_ACCOUNT_FLAG , SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR1_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT1_TYPE,'A',DECODE(CON.AMOUNT1_TYPE,'A','N',CON.AMOUNT1_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN1_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR1_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT1_TYPE, 'A', DECODE(CON.AMOUNT1_TYPE,'A','N',CON.AMOUNT1_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN1, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR1_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT1_TYPE, 'A', DECODE(CON.AMOUNT1_TYPE,'A','N',CON.AMOUNT1_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN1_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR2_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT2_TYPE,'A',DECODE(CON.AMOUNT2_TYPE,'A','N',CON.AMOUNT2_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN2_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR2_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT2_TYPE, 'A', DECODE(CON.AMOUNT2_TYPE,'A','N',CON.AMOUNT2_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN2, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR2_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT2_TYPE, 'A', DECODE(CON.AMOUNT2_TYPE,'A','N',CON.AMOUNT2_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN2_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR3_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT3_TYPE,'A',DECODE(CON.AMOUNT3_TYPE,'A','N',CON.AMOUNT3_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN3_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR3_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT3_TYPE, 'A', DECODE(CON.AMOUNT3_TYPE,'A','N',CON.AMOUNT3_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN3, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR3_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT3_TYPE, 'A', DECODE(CON.AMOUNT3_TYPE,'A','N',CON.AMOUNT3_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN3_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR4_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT4_TYPE,'A',DECODE(CON.AMOUNT4_TYPE,'A','N',CON.AMOUNT4_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN4_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR4_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT4_TYPE, 'A', DECODE(CON.AMOUNT4_TYPE,'A','N',CON.AMOUNT4_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN4, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR4_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT4_TYPE, 'A', DECODE(CON.AMOUNT4_TYPE,'A','N',CON.AMOUNT4_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN4_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR5_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT5_TYPE,'A',DECODE(CON.AMOUNT5_TYPE,'A','N',CON.AMOUNT5_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN5_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR5_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT5_TYPE, 'A', DECODE(CON.AMOUNT5_TYPE,'A','N',CON.AMOUNT5_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN5, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR5_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT5_TYPE, 'A', DECODE(CON.AMOUNT5_TYPE,'A','N',CON.AMOUNT5_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN5_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR6_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT6_TYPE,'A',DECODE(CON.AMOUNT6_TYPE,'A','N',CON.AMOUNT6_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN6_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR6_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT6_TYPE, 'A', DECODE(CON.AMOUNT6_TYPE,'A','N',CON.AMOUNT6_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN6, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR6_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT6_TYPE, 'A', DECODE(CON.AMOUNT6_TYPE,'A','N',CON.AMOUNT6_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN6_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR7_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT7_TYPE,'A',DECODE(CON.AMOUNT7_TYPE,'A','N',CON.AMOUNT7_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN7_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR7_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT7_TYPE, 'A', DECODE(CON.AMOUNT7_TYPE,'A','N',CON.AMOUNT7_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN7, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR7_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT7_TYPE, 'A', DECODE(CON.AMOUNT7_TYPE,'A','N',CON.AMOUNT7_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN7_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR8_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT8_TYPE,'A',DECODE(CON.AMOUNT8_TYPE,'A','N',CON.AMOUNT8_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN8_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR8_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT8_TYPE, 'A', DECODE(CON.AMOUNT8_TYPE,'A','N',CON.AMOUNT8_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN8, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR8_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT8_TYPE, 'A', DECODE(CON.AMOUNT8_TYPE,'A','N',CON.AMOUNT8_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN8_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR9_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT9_TYPE,'A',DECODE(CON.AMOUNT9_TYPE,'A','N',CON.AMOUNT9_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN9_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR9_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT9_TYPE, 'A', DECODE(CON.AMOUNT9_TYPE,'A','N',CON.AMOUNT9_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN9, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR9_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT9_TYPE, 'A', DECODE(CON.AMOUNT9_TYPE,'A','N',CON.AMOUNT9_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN9_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR10_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT10_TYPE,'A',DECODE(CON.AMOUNT10_TYPE,'A','N',CON.AMOUNT10_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN10_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR10_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT10_TYPE, 'A', DECODE(CON.AMOUNT10_TYPE,'A','N',CON.AMOUNT10_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN10, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR10_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT10_TYPE, 'A', DECODE(CON.AMOUNT10_TYPE,'A','N',CON.AMOUNT10_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN10_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR11_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT11_TYPE,'A',DECODE(CON.AMOUNT11_TYPE,'A','N',CON.AMOUNT11_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN11_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR11_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT11_TYPE, 'A', DECODE(CON.AMOUNT11_TYPE,'A','N',CON.AMOUNT11_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN11, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR11_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT11_TYPE, 'A', DECODE(CON.AMOUNT11_TYPE,'A','N',CON.AMOUNT11_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN11_CAL, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR12_ID,DECODE(WAL.BALANCE_TYPE,CON.AMOUNT12_TYPE,'A',DECODE(CON.AMOUNT12_TYPE,'A','N',CON.AMOUNT12_TYPE)),'N') ,'A',WAL.ACCOUNT_LINE_ID,'F',WAL.ACCOUNT_LINE_ID,0)) COLUMN12_ID, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR12_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT12_TYPE, 'A', DECODE(CON.AMOUNT12_TYPE,'A','N',CON.AMOUNT12_TYPE) ), 'N' ) , 'A', WAL.YTD_AMOUNT, 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN12, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID,CON.YEAR12_ID, DECODE(WAL.BALANCE_TYPE,CON.AMOUNT12_TYPE, 'A', DECODE(CON.AMOUNT12_TYPE,'A','N',CON.AMOUNT12_TYPE) ), 'N' ) , 'A', DECODE(CON.ACCOUNT_FLAG,'X',-1,1) *DECODE(WAL.ACCOUNT_TYPE, 'A',-WAL.YTD_AMOUNT, 'E',-WAL.YTD_AMOUNT, 'D',-WAL.YTD_AMOUNT, WAL.YTD_AMOUNT ), 'F', DECODE(WAL.SALARY_ACCOUNT_LINE,'Y',WAL.ANNUAL_FTE,0), 0 ) ) COLUMN12_CAL, MIN(GLC.SEGMENT1) SEGMENT1, MIN(GLC.SEGMENT2) SEGMENT2, MIN(GLC.SEGMENT3) SEGMENT3, MIN(GLC.SEGMENT4) SEGMENT4, MIN(GLC.SEGMENT5) SEGMENT5, MIN(GLC.SEGMENT6) SEGMENT6,MIN(GLC.SEGMENT7) SEGMENT7, MIN(GLC.SEGMENT8) SEGMENT8, MIN(GLC.SEGMENT9) SEGMENT9, MIN(GLC.SEGMENT10) SEGMENT10, MIN(GLC.SEGMENT11) SEGMENT11, MIN(GLC.SEGMENT12) SEGMENT12, MIN(GLC.SEGMENT13) SEGMENT13, MIN(GLC.SEGMENT14) SEGMENT14, MIN(GLC.SEGMENT15) SEGMENT15, MIN(GLC.SEGMENT16) SEGMENT16, MIN(GLC.SEGMENT17) SEGMENT17, MIN(GLC.SEGMENT18) SEGMENT18,MIN(GLC.SEGMENT19) SEGMENT19, MIN(GLC.SEGMENT20) SEGMENT20, MIN(GLC.SEGMENT21) SEGMENT21, MIN(GLC.SEGMENT22) SEGMENT22, MIN(GLC.SEGMENT23) SEGMENT23, MIN(GLC.SEGMENT24) SEGMENT24, MIN(GLC.SEGMENT25) SEGMENT25, MIN(GLC.SEGMENT26) SEGMENT26, MIN(GLC.SEGMENT27) SEGMENT27, MIN(GLC.SEGMENT28) SEGMENT28, MIN(GLC.SEGMENT29) SEGMENT29, MIN(GLC.SEGMENT30) SEGMENT30, CON.SESSION_ID, MAX(WAL.SALARY_ACCOUNT_LINE ) FROM PSB_WS_LINES WL, PSB_SERVICE_PACKAGES SP, PSB_WS_ACCOUNT_LINES WAL, GL_CODE_COMBINATIONS GLC, PSB_WORKSHEET_CONTEXT CON WHERE WL.ACCOUNT_LINE_ID = WAL.ACCOUNT_LINE_ID AND WAL.CODE_COMBINATION_ID=GLC.CODE_COMBINATION_ID AND WAL.SERVICE_PACKAGE_ID = SP.SERVICE_PACKAGE_ID AND WL.VIEW_LINE_FLAG= 'Y' and WAL.BUDGET_YEAR_ID IN (CON.YEAR1_ID,CON.YEAR2_ID,CON.YEAR3_ID,CON.YEAR4_ID,CON.YEAR5_ID,CON.YEAR6_ID, CON.YEAR7_ID,CON.YEAR8_ID,CON.YEAR9_ID,CON.YEAR10_ID,CON.YEAR11_ID,CON.YEAR12_ID) AND ( ( CON.STAGE = 0 AND WAL.END_STAGE_SEQ IS NULL) OR ( CON.STAGE BETWEEN WAL.START_STAGE_SEQ AND NVL(WAL.END_STAGE_SEQ, 9.99E125) ) ) AND WL.WORKSHEET_ID = CON.WORKSHEET_ID GROUP BY WL.WORKSHEET_ID,WAL.BUDGET_GROUP_ID, WAL.CODE_COMBINATION_ID,WAL.SERVICE_PACKAGE_ID, SP.SHORT_NAME, WAL.TEMPLATE_ID , WAL.ACCOUNT_TYPE, WAL.CURRENCY_CODE,CON.SESSION_ID, CON.ACCOUNT_FLAG

View Text - HTML Formatted

SELECT WL.WORKSHEET_ID
, WAL.BUDGET_GROUP_ID
, WAL.CODE_COMBINATION_ID
, WAL.SERVICE_PACKAGE_ID
, SP.SHORT_NAME
, WAL.TEMPLATE_ID
, WAL.CURRENCY_CODE
, CON.ACCOUNT_FLAG
, WAL.ACCOUNT_TYPE
, SUM(DECODE( NVL (WAL.POSITION_LINE_ID
, 0)
, 0
, 0
, 1 ) ) POSITION_ACCOUNT_FLAG
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR1_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT1_TYPE
, 'A'
, DECODE(CON.AMOUNT1_TYPE
, 'A'
, 'N'
, CON.AMOUNT1_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN1_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR1_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT1_TYPE
, 'A'
, DECODE(CON.AMOUNT1_TYPE
, 'A'
, 'N'
, CON.AMOUNT1_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN1
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR1_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT1_TYPE
, 'A'
, DECODE(CON.AMOUNT1_TYPE
, 'A'
, 'N'
, CON.AMOUNT1_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN1_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR2_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT2_TYPE
, 'A'
, DECODE(CON.AMOUNT2_TYPE
, 'A'
, 'N'
, CON.AMOUNT2_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN2_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR2_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT2_TYPE
, 'A'
, DECODE(CON.AMOUNT2_TYPE
, 'A'
, 'N'
, CON.AMOUNT2_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN2
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR2_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT2_TYPE
, 'A'
, DECODE(CON.AMOUNT2_TYPE
, 'A'
, 'N'
, CON.AMOUNT2_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN2_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR3_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT3_TYPE
, 'A'
, DECODE(CON.AMOUNT3_TYPE
, 'A'
, 'N'
, CON.AMOUNT3_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN3_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR3_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT3_TYPE
, 'A'
, DECODE(CON.AMOUNT3_TYPE
, 'A'
, 'N'
, CON.AMOUNT3_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN3
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR3_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT3_TYPE
, 'A'
, DECODE(CON.AMOUNT3_TYPE
, 'A'
, 'N'
, CON.AMOUNT3_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN3_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR4_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT4_TYPE
, 'A'
, DECODE(CON.AMOUNT4_TYPE
, 'A'
, 'N'
, CON.AMOUNT4_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN4_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR4_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT4_TYPE
, 'A'
, DECODE(CON.AMOUNT4_TYPE
, 'A'
, 'N'
, CON.AMOUNT4_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN4
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR4_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT4_TYPE
, 'A'
, DECODE(CON.AMOUNT4_TYPE
, 'A'
, 'N'
, CON.AMOUNT4_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN4_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR5_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT5_TYPE
, 'A'
, DECODE(CON.AMOUNT5_TYPE
, 'A'
, 'N'
, CON.AMOUNT5_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN5_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR5_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT5_TYPE
, 'A'
, DECODE(CON.AMOUNT5_TYPE
, 'A'
, 'N'
, CON.AMOUNT5_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN5
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR5_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT5_TYPE
, 'A'
, DECODE(CON.AMOUNT5_TYPE
, 'A'
, 'N'
, CON.AMOUNT5_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN5_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR6_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT6_TYPE
, 'A'
, DECODE(CON.AMOUNT6_TYPE
, 'A'
, 'N'
, CON.AMOUNT6_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN6_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR6_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT6_TYPE
, 'A'
, DECODE(CON.AMOUNT6_TYPE
, 'A'
, 'N'
, CON.AMOUNT6_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN6
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR6_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT6_TYPE
, 'A'
, DECODE(CON.AMOUNT6_TYPE
, 'A'
, 'N'
, CON.AMOUNT6_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN6_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR7_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT7_TYPE
, 'A'
, DECODE(CON.AMOUNT7_TYPE
, 'A'
, 'N'
, CON.AMOUNT7_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN7_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR7_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT7_TYPE
, 'A'
, DECODE(CON.AMOUNT7_TYPE
, 'A'
, 'N'
, CON.AMOUNT7_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN7
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR7_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT7_TYPE
, 'A'
, DECODE(CON.AMOUNT7_TYPE
, 'A'
, 'N'
, CON.AMOUNT7_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN7_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR8_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT8_TYPE
, 'A'
, DECODE(CON.AMOUNT8_TYPE
, 'A'
, 'N'
, CON.AMOUNT8_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN8_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR8_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT8_TYPE
, 'A'
, DECODE(CON.AMOUNT8_TYPE
, 'A'
, 'N'
, CON.AMOUNT8_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN8
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR8_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT8_TYPE
, 'A'
, DECODE(CON.AMOUNT8_TYPE
, 'A'
, 'N'
, CON.AMOUNT8_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN8_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR9_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT9_TYPE
, 'A'
, DECODE(CON.AMOUNT9_TYPE
, 'A'
, 'N'
, CON.AMOUNT9_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN9_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR9_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT9_TYPE
, 'A'
, DECODE(CON.AMOUNT9_TYPE
, 'A'
, 'N'
, CON.AMOUNT9_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN9
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR9_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT9_TYPE
, 'A'
, DECODE(CON.AMOUNT9_TYPE
, 'A'
, 'N'
, CON.AMOUNT9_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN9_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR10_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT10_TYPE
, 'A'
, DECODE(CON.AMOUNT10_TYPE
, 'A'
, 'N'
, CON.AMOUNT10_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN10_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR10_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT10_TYPE
, 'A'
, DECODE(CON.AMOUNT10_TYPE
, 'A'
, 'N'
, CON.AMOUNT10_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN10
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR10_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT10_TYPE
, 'A'
, DECODE(CON.AMOUNT10_TYPE
, 'A'
, 'N'
, CON.AMOUNT10_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN10_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR11_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT11_TYPE
, 'A'
, DECODE(CON.AMOUNT11_TYPE
, 'A'
, 'N'
, CON.AMOUNT11_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN11_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR11_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT11_TYPE
, 'A'
, DECODE(CON.AMOUNT11_TYPE
, 'A'
, 'N'
, CON.AMOUNT11_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN11
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR11_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT11_TYPE
, 'A'
, DECODE(CON.AMOUNT11_TYPE
, 'A'
, 'N'
, CON.AMOUNT11_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN11_CAL
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR12_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT12_TYPE
, 'A'
, DECODE(CON.AMOUNT12_TYPE
, 'A'
, 'N'
, CON.AMOUNT12_TYPE))
, 'N')
, 'A'
, WAL.ACCOUNT_LINE_ID
, 'F'
, WAL.ACCOUNT_LINE_ID
, 0)) COLUMN12_ID
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR12_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT12_TYPE
, 'A'
, DECODE(CON.AMOUNT12_TYPE
, 'A'
, 'N'
, CON.AMOUNT12_TYPE) )
, 'N' )
, 'A'
, WAL.YTD_AMOUNT
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN12
, SUM(DECODE(DECODE(WAL.BUDGET_YEAR_ID
, CON.YEAR12_ID
, DECODE(WAL.BALANCE_TYPE
, CON.AMOUNT12_TYPE
, 'A'
, DECODE(CON.AMOUNT12_TYPE
, 'A'
, 'N'
, CON.AMOUNT12_TYPE) )
, 'N' )
, 'A'
, DECODE(CON.ACCOUNT_FLAG
, 'X'
, -1
, 1) *DECODE(WAL.ACCOUNT_TYPE
, 'A'
, -WAL.YTD_AMOUNT
, 'E'
, -WAL.YTD_AMOUNT
, 'D'
, -WAL.YTD_AMOUNT
, WAL.YTD_AMOUNT )
, 'F'
, DECODE(WAL.SALARY_ACCOUNT_LINE
, 'Y'
, WAL.ANNUAL_FTE
, 0)
, 0 ) ) COLUMN12_CAL
, MIN(GLC.SEGMENT1) SEGMENT1
, MIN(GLC.SEGMENT2) SEGMENT2
, MIN(GLC.SEGMENT3) SEGMENT3
, MIN(GLC.SEGMENT4) SEGMENT4
, MIN(GLC.SEGMENT5) SEGMENT5
, MIN(GLC.SEGMENT6) SEGMENT6
, MIN(GLC.SEGMENT7) SEGMENT7
, MIN(GLC.SEGMENT8) SEGMENT8
, MIN(GLC.SEGMENT9) SEGMENT9
, MIN(GLC.SEGMENT10) SEGMENT10
, MIN(GLC.SEGMENT11) SEGMENT11
, MIN(GLC.SEGMENT12) SEGMENT12
, MIN(GLC.SEGMENT13) SEGMENT13
, MIN(GLC.SEGMENT14) SEGMENT14
, MIN(GLC.SEGMENT15) SEGMENT15
, MIN(GLC.SEGMENT16) SEGMENT16
, MIN(GLC.SEGMENT17) SEGMENT17
, MIN(GLC.SEGMENT18) SEGMENT18
, MIN(GLC.SEGMENT19) SEGMENT19
, MIN(GLC.SEGMENT20) SEGMENT20
, MIN(GLC.SEGMENT21) SEGMENT21
, MIN(GLC.SEGMENT22) SEGMENT22
, MIN(GLC.SEGMENT23) SEGMENT23
, MIN(GLC.SEGMENT24) SEGMENT24
, MIN(GLC.SEGMENT25) SEGMENT25
, MIN(GLC.SEGMENT26) SEGMENT26
, MIN(GLC.SEGMENT27) SEGMENT27
, MIN(GLC.SEGMENT28) SEGMENT28
, MIN(GLC.SEGMENT29) SEGMENT29
, MIN(GLC.SEGMENT30) SEGMENT30
, CON.SESSION_ID
, MAX(WAL.SALARY_ACCOUNT_LINE )
FROM PSB_WS_LINES WL
, PSB_SERVICE_PACKAGES SP
, PSB_WS_ACCOUNT_LINES WAL
, GL_CODE_COMBINATIONS GLC
, PSB_WORKSHEET_CONTEXT CON
WHERE WL.ACCOUNT_LINE_ID = WAL.ACCOUNT_LINE_ID
AND WAL.CODE_COMBINATION_ID=GLC.CODE_COMBINATION_ID
AND WAL.SERVICE_PACKAGE_ID = SP.SERVICE_PACKAGE_ID
AND WL.VIEW_LINE_FLAG= 'Y'
AND WAL.BUDGET_YEAR_ID IN (CON.YEAR1_ID
, CON.YEAR2_ID
, CON.YEAR3_ID
, CON.YEAR4_ID
, CON.YEAR5_ID
, CON.YEAR6_ID
, CON.YEAR7_ID
, CON.YEAR8_ID
, CON.YEAR9_ID
, CON.YEAR10_ID
, CON.YEAR11_ID
, CON.YEAR12_ID)
AND ( ( CON.STAGE = 0
AND WAL.END_STAGE_SEQ IS NULL) OR ( CON.STAGE BETWEEN WAL.START_STAGE_SEQ
AND NVL(WAL.END_STAGE_SEQ
, 9.99E125) ) )
AND WL.WORKSHEET_ID = CON.WORKSHEET_ID GROUP BY WL.WORKSHEET_ID
, WAL.BUDGET_GROUP_ID
, WAL.CODE_COMBINATION_ID
, WAL.SERVICE_PACKAGE_ID
, SP.SHORT_NAME
, WAL.TEMPLATE_ID
, WAL.ACCOUNT_TYPE
, WAL.CURRENCY_CODE
, CON.SESSION_ID
, CON.ACCOUNT_FLAG