VAR M_ENT VARCHAR2(4); VAR M_SUB_ENT VARCHAR2(3); VAR M_POST_ACCT VARCHAR2(9); VAR M_CTR_OR_HIER VARCHAR2(14); VAR M_YR VARCHAR2(4); VAR M_PLAN_TYPE VARCHAR2(10); VAR M_SUM_FLAG VARCHAR2(10); VAR M_ADJ VARCHAR2(1); VAR M_REC_TYPE VARCHAR2(25); VAR M_PRODUCT_CODE VARCHAR2(25); VAR M_SEGMENT_CODE VARCHAR2(25); SET SERVEROUTPUT ON; UPDATE /*+ INDEX(M MON_PLAN_IDX_PK) NOPARALLEL(M) NOPARALLEL_INDEX(M MON_PLAN_IDX_PK) */ MON_PLAN M SET (plan_ytd_01, plan_ytd_02, plan_ytd_03, plan_ytd_04, plan_ytd_05, plan_ytd_06, plan_ytd_07, plan_ytd_08, plan_ytd_09, plan_ytd_10, plan_ytd_11, plan_ytd_12) = (SELECT /*+ ORDERED INDEX(T MON_PLAN_IDX_PK) USE_NL (T) INDEX(C COA_REF_M_IDX_PK) USE_NL(C) NOPARALLEL(T) NOPARALLEL_INDEX(T MON_PLAN_IDX_PK) */ NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 ) ) ,2), (M.plan_mtd_01 )), 0) pm1 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1))) / (1 +59) ,2), (M.plan_mtd_01 + M.plan_mtd_02)), 0) pm2 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31)) / (1 +90) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03)), 0) pm3 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31) + (M.plan_mtd_04 * 30)) / (1 +120) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03 + M.plan_mtd_04)), 0) pm4 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31) + (M.plan_mtd_04 * 30) + (M.plan_mtd_05 * 31)) / (1 +151) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03 + M.plan_mtd_04 + M.plan_mtd_05)), 0) pm5 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31) + (M.plan_mtd_04 * 30) + (M.plan_mtd_05 * 31) + (M.plan_mtd_06 * 30)) / (1 +181) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03 + M.plan_mtd_04 + M.plan_mtd_05 + M.plan_mtd_06)), 0) pm6 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31) + (M.plan_mtd_04 * 30) + (M.plan_mtd_05 * 31) + (M.plan_mtd_06 * 30) + (M.plan_mtd_07 * 31)) / (1 +212) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03 + M.plan_mtd_04 + M.plan_mtd_05 + M.plan_mtd_06 + M.plan_mtd_07)), 0) pm7 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31) + (M.plan_mtd_04 * 30) + (M.plan_mtd_05 * 31) + (M.plan_mtd_06 * 30) + (M.plan_mtd_07 * 31) + (M.plan_mtd_08 * 31)) / (1 +243) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03 + M.plan_mtd_04 + M.plan_mtd_05 + M.plan_mtd_06 + M.plan_mtd_07 + M.plan_mtd_08)), 0) pm8 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31) + (M.plan_mtd_04 * 30) + (M.plan_mtd_05 * 31) + (M.plan_mtd_06 * 30) + (M.plan_mtd_07 * 31) + (M.plan_mtd_08 * 31) + (M.plan_mtd_09 * 30)) / (1 +273) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03 + M.plan_mtd_04 + M.plan_mtd_05 + M.plan_mtd_06 + M.plan_mtd_07 + M.plan_mtd_08 + M.plan_mtd_09)), 0) pm9 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31) + (M.plan_mtd_04 * 30) + (M.plan_mtd_05 * 31) + (M.plan_mtd_06 * 30) + (M.plan_mtd_07 * 31) + (M.plan_mtd_08 * 31) + (M.plan_mtd_09 * 30) + (M.plan_mtd_10 * 31)) / (1 +304) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03 + M.plan_mtd_04 + M.plan_mtd_05 + M.plan_mtd_06 + M.plan_mtd_07 + M.plan_mtd_08 + M.plan_mtd_09 + M.plan_mtd_10)), 0) pm10 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31) + (M.plan_mtd_04 * 30) + (M.plan_mtd_05 * 31) + (M.plan_mtd_06 * 30) + (M.plan_mtd_07 * 31) + (M.plan_mtd_08 * 31) + (M.plan_mtd_09 * 30) + (M.plan_mtd_10 * 31) + (M.plan_mtd_11 * 30)) / (1 +334) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03 + M.plan_mtd_04 + M.plan_mtd_05 + M.plan_mtd_06 + M.plan_mtd_07 + M.plan_mtd_08 + M.plan_mtd_09 + M.plan_mtd_10 + M.plan_mtd_11)), 0) pm11 , NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'), 'B', ROUND(((M.plan_mtd_01 * 31) + (M.plan_mtd_02 * (28 + 1)) + (M.plan_mtd_03 * 31) + (M.plan_mtd_04 * 30) + (M.plan_mtd_05 * 31) + (M.plan_mtd_06 * 30) + (M.plan_mtd_07 * 31) + (M.plan_mtd_08 * 31) + (M.plan_mtd_09 * 30) + (M.plan_mtd_10 * 31) + (M.plan_mtd_11 * 30) + (M.plan_mtd_12 * 31)) / (1 +365) ,2), (M.plan_mtd_01 + M.plan_mtd_02 + M.plan_mtd_03 + M.plan_mtd_04 + M.plan_mtd_05 + M.plan_mtd_06 + M.plan_mtd_07 + M.plan_mtd_08 + M.plan_mtd_09 + M.plan_mtd_10 + M.plan_mtd_11 + M.plan_mtd_12)), 0) pm12 FROM MON_PLAN T, COA_REF_M C WHERE T.REC_TYPE = M.REC_TYPE AND T.ENT = M.ENT AND T.SUB_ENT = M.SUB_ENT AND T.POST_ACCT = M.POST_ACCT AND T.CTR_OR_HIER = M.CTR_OR_HIER AND T.YR = M.YR AND T.PLAN_TYPE = M.PLAN_TYPE AND T.SUM_FLAG = M.SUM_FLAG AND T.ADJ = M.ADJ AND T.PRODUCT_CODE = M.PRODUCT_CODE AND T.SEGMENT_CODE = M.SEGMENT_CODE AND T.POST_ACCT = C.ACCT AND C.ACCT_LVL = '8' AND C.ACCT_ID = 'primary' AND C.REL_TYPE = ' ') WHERE M.REC_TYPE = 'A' AND M.ENT like '910' AND M.SUB_ENT = '000' AND M.POST_ACCT like '8%' AND M.CTR_OR_HIER like '%' AND M.YR = '2008' AND M.PLAN_TYPE like 'ztest' AND M.SUM_FLAG LIKE '%' AND M.ADJ >= ' ' AND M.PRODUCT_CODE = 'ALL' AND M.SEGMENT_CODE = 'ALL' RETURNING m.rec_type,m.ent,m.sub_ent,m.post_acct,m.ctr_or_hier,m.yr,m.plan_type,m.sum_flag,m.adj,m.product_code,m.segment_code INTO :m_rec_type,:m_ent,:m_sub_ent,:m_post_acct,:m_ctr_or_hier,:m_yr,:m_plan_type,:m_sum_flag,:m_adj,:m_product_code,:m_segment_code; PRINT m_rec_type; PRINT m_ent; PRINT m_sub_ent; PRINT m_post_acct; PRINT m_ctr_or_hier; PRINT m_yr; PRINT m_plan_type; PRINT m_sum_flag; PRINT m_adj; PRINT m_product_code; PRINT m_segment_code;