6x2sh0kvzac0t | select /* kmtst-auto */ D1.c10 as c1, D1.c5 as c2, D1.c11 as c3, D1.c12 as c4, D1.c13 as c5, D1.c14 as c6, D1.c15 as c7, D1.c7 as c8, D1.c9 as c9, D1.c8 as c10, D1.c6 as c11, sum(D1.c2) over (partition by D1.c5) as c12, sum(D1.c4) over (partition by D1.c5) as c13, sum(D1.c3) over (partition by D1.c5) as c14, sum(D1.c1) over (partition by D1.c5) as c15 from (select /*+ LEADING(T156337) */ sum(T348279.ACTIVITY_LOC_AMT_YTD) as c1, sum(T348279.ACTIVITY_LOC_AMT) as c2, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c3, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c4, T348271.ACCOUNT_CODE_DISP as c5, sum(T348279.ACTIVITY_LOC_AMT_YTD) as c6, sum(T348279.ACTIVITY_LOC_AMT) as c7, sum(T348279.ACTIVITY_LOC_AMT_YTD_YAGO) as c8, sum(T348279.ACTIVITY_LOC_AMT_YAGO) as c9, T149255.SEGMENT_VAL_CODE as c10, T348271.ACCOUNT_NAME_DISP as c11, T348271.LEVEL_NUM as c12, T348271.REPORT_SIGN as c13, case when T125129.HIER10_CODE = 'INTEREST_REVENUE10' then T125129.X_NODE_ID * 10 + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) else T125129.X_NODE_ID + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) end as c14, T348271.SORT_NUM as c15 from WC_ACCT_HIER_DH T348271 /* Dim_WC_ACCT_HIER_DH */ , (SELECT DISTINCT FROM_CURCY_CD CURRENCY FROM W_EXCH_RATE_G) T347319, W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ , W_HIERARCHY_D T148616 /* Dim_W_HIERARCHY_D_Segment2 *
/ , W_GL_SEGMENT_D T148908 /* Dim_W_GL_SEGMENT_D_Segment2 */ , W_HIERARCHY_D T148543 /* Dim_W_HIERARCHY_D_Segment3 */ , W_GL_SEGMENT_D T148937 /* Dim_W_GL_SEGMENT_D_Segment3 */ , W_HIERARCHY_D T125129 /* Dim_W_HIERARCHY_D_Segment1 */ , W_GL_SEGMENT_D T149255 /* Dim_W_GL_SEGMENT_D_Segment1 */ , W_INT_ORG_D T111515 /* Dim_W_INT_ORG_D_Company */ , W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ , WC_GL_BALANCE_A_KM T348279 /* Fact_WC_GL_BALANCE_A */ where ( T156337.ROW_WID = T348279.BALANCE_DT_WID and T347319.CURRENCY = T348279.LOC_CURR_CODE and T148616.HIER_CODE = T148908.SEGMENT_LOV_ID and T148616.HIER20_CODE = T148908.SEGMENT_VAL_CODE and T148543.HIER_CODE = T148937.SEGMENT_LOV_ID and T148543.HIER20_CODE = T148937.SEGMENT_VAL_CODE and T148908.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG2_ATTRIB and T148908.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG2_CODE and T125129.HIER_CODE = T149255.SEGMENT_LOV_ID and T125129.HIER20_CODE = T149255.SEGMENT_VAL_CODE and T148937.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG3_ATTRIB and T148937.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG3_CODE and T111515.ROW_WID = T348279.COMPANY_ORG_WID and T146058.ROW_WID = T348279.LEDGER_WID and T149255.SEGMENT_LOV_ID = T348279.ACCOUNT_SEG1_ATTRIB and T149255.SEGMENT_VAL_CODE = T348279.ACCOUNT_SEG1_CODE and T111515.COMPANY_FLG = 'Y' and T148908.SEGMENT_LOV_ID = 'Department~SHARE' and T148937.SEGMENT_LOV_ID = 'Operating Unit~SHARE' and T149255.SEGMENT_LOV_ID = 'Account~SHARE' and T156337.MCAL_PERIOD_NAME = 'March' and T156337.MCAL_P
ER_NAME_YEAR = '2012' and T347319.CURRENCY = 'USD' and T348271.ROW_WID = T348279.ACCOUNT_HIER_WID and T348279.LOC_CURR_CODE = 'USD' and T111515.ORG_NUM is not null and T148543.HIER9_CODE is not null and (T146058.LEDGER_NAME in ('Actuals', 'Local Ledger in Local Currency')) and (T348271.LEVEL_NUM in (2, 3, 4, 5, 6, 7, 8, 9, 10, 12)) and T148616.HIER10_CODE is not null ) group by T149255.SEGMENT_VAL_CODE, T348271.LEVEL_NUM, T348271.SORT_NUM, T348271.ACCOUNT_CODE_DISP, T348271.ACCOUNT_NAME_DISP, T348271.REPORT_SIGN, case when T125129.HIER10_CODE = 'INTEREST_REVENUE10' then T125129.X_NODE_ID * 10 + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) else T125129.X_NODE_ID + cast(T149255.SEGMENT_VAL_CODE as DOUBLE PRECISION ) end ) D1 order by c2, c3, c4, c5, c1, c6, c7 |