CREATE MATERIALIZED VIEW "PPM"."CLX_CSDB_PROP_V_MAT" ("PROPOSAL_ID", "NAME", "DESCRIPTION", "STATUS", "FS_ID", "BENEFIT_ID", "BENEFIT_LINE_ID", "USER_DATA_ID", "DIVISION_FUNCTION_CODE", "DIVISION_FUNCTION", "BUSINESS_UNIT_CODE", "BUSINESS_UNIT_MEANING", "BU_CODE", "BU_MEANING", "SECONDARY_BU_CODE", "SECONDARY_BU", "CATEGORY_CODE", "CATEGORY", "COUNTRY_CODE", "COUNTRY_MEANING", "BRAND_CODE", "BRAND_MEANING", "COMPANY_REGION_CODE", "COMPANY_REGION", "PARENT_PROJECT", "PROJECT_CLASS_CODE", "PROJECT_CLASS_MEANING", "PROJECT_SUB_TYPE", "BENEFIT_TYPE", "BENEFIT_CATEGORY", "BENEFIT_CATEGORY_M", "FEEDER_STREAM_DETAIL_CODE", "FEEDER_STREAM_DETAIL_MEANING", "FEEDER_STREAM", "COMMODITY_CODE", "COMMODITY_MEANING", "PL_CODE", "PL_MEANING", "HAVE_FIN_VALIDATED", "DATE_SAVINGS_START", "PROJECT_MANAGER", "LOCATION", "SUPPLIER_DIVERSITY", "PSO_INDIRECT", "PROJECT_PROBABILITY") ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "PPM_DATA" BUILD IMMEDIATE USING INDEX REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT (SYSDATE +1/24) USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS (SELECT prop.request_id PROPOSAL_ID , prop.PROPOSAL_NAME NAME , substr(rd.parameter42,1,250) DESCRIPTION , s.STATUS_NAME STATUS , prop.prop_financial_summary_id FS_ID , fml.BENEFIT_ID BENEFIT_ID , fml.benefit_line_id BENEFIT_LINE_ID , fml.user_data_id USER_DATA_ID , rh.parameter17 Division_Function_CODE , DECODE(rh.parameter17, 'BURTS_BEES', 'Burts Bees', rh.visible_parameter17) Division_Function , REPLACE(prop.PROP_BUSINESS_UNIT_CODE, '#@#', '; ') BUSINESS_UNIT_CODE , REPLACE(prop.PROP_BUSINESS_UNIT_MEANING, '#@#', '; ') BUSINESS_UNIT_MEANING , fu.DATUM3_CODE BU_CODE , fu.DATUM3_VISIBLE BU_MEANING , REPLACE(rh.parameter33, '#@#', '; ') SECONDARY_BU_CODE , REPLACE(rh.visible_parameter33, '#@#', '; ') SECONDARY_BU , REPLACE( rh.parameter18, '#@#', '; ') CATEGORY_CODE , REPLACE( rh.visible_parameter18, '#@#', '; ') CATEGORY , fu.DATUM8_CODE COUNTRY_CODE , fu.DATUM8_VISIBLE COUNTRY_MEANING , fu.DATUM1_CODE BRAND_CODE , fu.DATUM1_VISIBLE BRAND_MEANING , rh.parameter12 COMPANY_REGION_CODE , DECODE(rh.parameter12, 'BURTS_BEES', 'Burts Bees', rh.visible_parameter12) COMPANY_REGION , rh.visible_parameter44 PARENT_PROJECT , lk.lookup_code PROJECT_CLASS_CODE , lk.meaning PROJECT_CLASS_MEANING , rd.visible_parameter7 PROJECT_SUB_TYPE , fml.benefit_type_code BENEFIT_TYPE , fml.category_code BENEFIT_CATEGORY , lk1.meaning BENEFIT_CATEGORY_M , fu.DATUM4_CODE FEEDER_STREAM_DETAIL_CODE , fu.DATUM4_VISIBLE FEEDER_STREAM_DETAIL_MEANING , DECODE(fu.DATUM4_CODE,null, null, 'INDIRECT_NON_EXTERNAL_BUY', null, 'BUY_CO_PACK','Buy', 'BUY_INDIRECT', 'Buy', 'BUY_PACK', 'Buy', 'BUY_RAW', 'Buy', 'CATEGORY_', 'Category','MAKE_MATERIALS','Make', 'MAKE_OPERATING', 'Make', 'MAKE_OTHER', 'Make', 'Ship') FEEDER_STREAM , fu.DATUM5_CODE COMMODITY_CODE , fu.DATUM5_VISIBLE COMMODITY_MEANING , fu.DATUM2_CODE PL_CODE , fu.DATUM2_VISIBLE PL_MEANING , 'NA' HAVE_FIN_VALIDATED , CLX_CSDB_GET_DATE_SAVING_START(prop.prop_financial_summary_id,fml.BENEFIT_LINE_ID, 'PROPOSAL', NULL, NULL, 0) DATE_SAVINGS_START , REPLACE(prop.prop_project_manager_username, '#@#', '; ') PROJECT_MANAGER , REPLACE(rh.visible_parameter46, '#@#', '; ') LOCATION , rd1.visible_parameter24 SUPPLIER_DIVERSITY , fu.DATUM6_VISIBLE PSO_INDIRECT , 0 PROJECT_Probability from kcrt_fg_pfm_proposal prop , kcrt_requests r , kcrt_req_header_details rh , kcrt_request_details rd , kcrt_request_details rd1 , KCRT_STATUSES_NLS s , knta_lookups_nls lk , knta_lookups_nls lk1 , FM_FINANCIAL_SUMMARY fm , FM_BENEFIT_LINES fml , FM_USER_DATA fu where prop.request_type_id IN (select rt.request_type_id from kcrt_request_types_nls rt where rt.request_type_name in ('PPM - Proposal')) and r.request_id = prop.request_id and r.status_code IN ('IN_PROGRESS') and lk.lookup_type = 'PFM - Project Class' and lk.enabled_flag = 'Y' and lk.user_data1 = 'Y' and prop.prop_project_class_code = lk.lookup_code and fml.category_code = lk1.lookup_code and lk1.lookup_type = 'FINANCIAL_BENEFIT_SAVINGS_CATEGORY' and lk1.enabled_flag = 'Y' and r.request_id = rh.request_id and rh.batch_number = 1 and r.request_id = rd.request_id and rd.batch_number = 1 and r.request_id = rd1.request_id and rd1.batch_number = 4 and s.status_id=r.status_id and s.STATUS_NAME NOT IN ('Not Submitted') and UPPER(s.STATUS_NAME) NOT LIKE UPPER('%Cancel%') and UPPER(s.STATUS_NAME) NOT LIKE UPPER('%On Hold%') and UPPER(s.STATUS_NAME) NOT LIKE UPPER('%Draft%') and prop.prop_financial_summary_id = fm.financial_summary_id and fm.benefit_id = fml.benefit_id and fml.benefit_type_code = 'SAVINGS' and fml.USER_DATA_ID = fu.USER_DATA_ID UNION SELECT prop.request_id PROPOSAL_ID , prop.PROPOSAL_NAME NAME , substr(rd.parameter42,1,250) DESCRIPTION , s.STATUS_NAME STATUS , prop.prop_financial_summary_id FS_ID , fml.BENEFIT_ID BENEFIT_ID , fml.benefit_line_id BENEFIT_LINE_ID , fml.user_data_id USER_DATA_ID , rh.parameter17 Division_Function_CODE --, rh.visible_parameter17 Division_Function , DECODE(rh.parameter17, 'BURTS_BEES', 'Burts Bees', rh.visible_parameter17) Division_Function , REPLACE(prop.PROP_BUSINESS_UNIT_CODE, '#@#', '; ') BUSINESS_UNIT_CODE , REPLACE(prop.PROP_BUSINESS_UNIT_MEANING, '#@#', '; ') BUSINESS_UNIT_MEANING , fu.DATUM3_CODE BU_CODE , fu.DATUM3_VISIBLE BU_MEANING , REPLACE(rh.parameter33, '#@#', '; ') SECONDARY_BU_CODE , REPLACE(rh.visible_parameter33, '#@#', '; ') SECONDARY_BU , REPLACE( rh.parameter18, '#@#', '; ') CATEGORY_CODE , REPLACE( rh.visible_parameter18, '#@#', '; ') CATEGORY , fu.DATUM8_CODE COUNTRY_CODE , fu.DATUM8_VISIBLE COUNTRY_MEANING , fu.DATUM1_CODE BRAND_CODE , fu.DATUM1_VISIBLE BRAND_MEANING , rh.parameter12 COMPANY_REGION_CODE , decode(rh.parameter12, 'BURTS_BEES', 'Burts Bees', rh.visible_parameter12) COMPANY_REGION , rh.visible_parameter44 PARENT_PROJECT , lk.lookup_code PROJECT_CLASS_CODE , lk.meaning PROJECT_CLASS_MEANING , rd.visible_parameter7 PROJECT_SUB_TYPE , fml.benefit_type_code BENEFIT_TYPE , fml.category_code BENEFIT_CATEGORY , lk1.meaning BENEFIT_CATEGORY_M , fu.DATUM4_CODE FEEDER_STREAM_DETAIL_CODE , fu.DATUM4_VISIBLE FEEDER_STREAM_DETAIL_MEANING , DECODE(fu.DATUM4_CODE,null, null, 'INDIRECT_NON_EXTERNAL_BUY', null, 'BUY_CO_PACK','Buy', 'BUY_INDIRECT', 'Buy', 'BUY_PACK', 'Buy', 'BUY_RAW', 'Buy', 'CATEGORY_', 'Category','MAKE_MATERIALS','Make', 'MAKE_OPERATING', 'Make', 'MAKE_OTHER', 'Make', 'Ship') FEEDER_STREAM , fu.DATUM5_CODE COMMODITY_CODE , fu.DATUM5_VISIBLE COMMODITY_MEANING , fu.DATUM2_CODE PL_CODE , fu.DATUM2_VISIBLE PL_MEANING , 'NA' HAVE_FIN_VALIDATED , CLX_CSDB_GET_DATE_SAVING_START(prop.prop_financial_summary_id,fml.BENEFIT_LINE_ID, 'PROPOSAL', NULL, NULL, 0) DATE_SAVINGS_START , REPLACE(prop.prop_project_manager_username, '#@#', '; ') PROJECT_MANAGER , REPLACE(rh.visible_parameter46, '#@#', '; ') LOCATION , rd.visible_parameter24 SUPPLIER_DIVERSITY , fu.DATUM6_VISIBLE PSO_INDIRECT , 0 PROJECT_Probability from kcrt_fg_pfm_proposal prop , kcrt_requests r , kcrt_req_header_details rh , kcrt_request_details rd , KCRT_STATUSES_NLS s , knta_lookups_nls lk , knta_lookups_nls lk1 , FM_FINANCIAL_SUMMARY fm , FM_BENEFIT_LINES fml , FM_USER_DATA fu where prop.request_type_id IN (select rt.request_type_id from kcrt_request_types_nls rt where rt.request_type_name in ('CLX - Proposal')) and r.request_id = prop.request_id and r.status_code IN ('IN_PROGRESS') and lk.lookup_type = 'PFM - Project Class' and lk.enabled_flag = 'Y' and lk.user_data1 = 'Y' and prop.prop_project_class_code = lk.lookup_code and fml.category_code = lk1.lookup_code and lk1.lookup_type = 'FINANCIAL_BENEFIT_SAVINGS_CATEGORY' and lk1.enabled_flag = 'Y' and r.request_id = rh.request_id and rh.batch_number = 1 and r.request_id = rd.request_id and rd.batch_number = 1 and s.status_id=r.status_id and s.STATUS_NAME NOT IN ('Not Submitted') and UPPER(s.STATUS_NAME) NOT LIKE UPPER('%Cancel%') and UPPER(s.STATUS_NAME) NOT LIKE UPPER('%On Hold%') and UPPER(s.STATUS_NAME) NOT LIKE UPPER('%Draft%') and prop.prop_financial_su CREATE MATERIALIZED VIEW PPM.CLX_CSDB_PRJ_CHILD_V_MAT (PROPOSAL_ID,PARENT_PROJECT_ID,PARENT_PRJ_PROJECT_ID,NAME,DESCRIPTION,STATUS1,STATUS,CHILD_PROJECT_ID,CHILD_PROJECT_NAME,FS_ID,CHILD_FS_ID,BENEFIT_ID,BENEFIT_LINE_ID,USER_DATA_ID,DIVISION_FUNCTION_CODE,DIVISION_FUNCTION,BUSINESS_UNIT_CODE,BUSINESS_UNIT_MEANING,BU_CODE,BU_MEANING,SECONDARY_BU_CODE,SECONDARY_BU,CATEGORY_CODE,CATEGORY,COUNTRY_CODE,COUNTRY_MEANING,BRAND_CODE,BRAND_MEANING,COMPANY_REGION_CODE,COMPANY_REGION,PARENT_PROJECT,PROJECT_CLASS_CODE,PROJECT_CLASS_MEANING,PROJECT_SUB_TYPE,BENEFIT_TYPE,BENEFIT_CATEGORY,BENEFIT_CATEGORY_M,FEEDER_STREAM_DETAIL_CODE,FEEDER_STREAM_DETAIL_MEANING,FEEDER_STREAM,COMMODITY_CODE,COMMODITY_MEANING,PL_CODE,PL_MEANING,HAVE_FIN_VALIDATED,DATE_SAVINGS_START,PROJECT_MANAGER,LOCATION,SUPPLIER_DIVERSITY,PSO_INDIRECT,PROJECT_PROBABILITY) TABLESPACE PPM_DATA PCTUSED 0 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH COMPLETE START WITH TO_DATE('19-Dec-2013 22:00:42','dd-mon-yyyy hh24:mi:ss') WITH PRIMARY KEY AS (SELECT rd2.parameter2 PROPOSAL_ID, prj.request_id PARENT_PROJECT_ID, prj.prj_project_id PARENT_PRJ_PROJECT_ID, prj.prj_project_name NAME, SUBSTR (rd1.parameter46, 1, 250) DESCRIPTION, s.STATUS_NAME STATUS1, DECODE ( RTRIM ( SUBSTR ( s.STATUS_NAME, 1, (INSTR (REPLACE (s.STATUS_NAME, '–', '-'), '-', 1) - 1))), 'Comm', 'Commercialization', 'Dev', 'Development', 'Implemen', 'Implementation', 'Implement', 'Implementation', 'Assessment', 'Assessment', 'OA', 'Assessment', 'Recommendation', 'Recommendation', 'Closure', 'Closure', 'Closed', 'Closed', s.STATUS_NAME) STATUS, rd2.request_id CHILD_PROJECT_ID, prj1.prj_project_name CHILD_PROJECT_NAME, prj.prj_financial_summary_id FS_ID, prj1.prj_financial_summary_id CHILD_FS_ID, fml.BENEFIT_ID BENEFIT_ID, fml.benefit_line_id BENEFIT_LINE_ID, fml.user_data_id USER_DATA_ID, rh.parameter16 Division_Function_Code, rh.visible_parameter16 Division_Function, REPLACE (prj.PRJ_BUSINESS_UNIT_CODE, '#@#', '; ') BUSINESS_UNIT_CODE, REPLACE (prj.PRJ_BUSINESS_UNIT_MEANING, '#@#', '; ') BUSINESS_UNIT_MEANING--, fu.DATUM3_CODE BU_CODE , DECODE (fu.DATUM3_CODE, 'LAUNDRY', 'ONLY_LAUNDRY', 'CHARCOAL', 'ONLY_CHARCOAL', 'FOODS', 'ONLY_FOODS', 'GLAD', 'ONLY_GLAD', 'AFH', 'ONLY_AFH', 'BRITA', 'ONLY_BRITA', 'GREENWORKS', 'ONLY_GREENWORKS', 'HOME_CARE', 'ONLY_HOME_CARE', 'LITTER', 'ONLY_LITTER', fu.DATUM3_CODE) BU_CODE, fu.DATUM3_VISIBLE BU_MEANING, REPLACE (rh.parameter14, '#@#', '; ') SECONDARY_BU_CODE, REPLACE (rh.visible_parameter14, '#@#', '; ') SECONDARY_BU, REPLACE (rh.parameter19, '#@#', '; ') CATEGORY_CODE, REPLACE (rh.visible_parameter19, '#@#', '; ') CATEGORY, fu.DATUM8_CODE COUNTRY_CODE, fu.DATUM8_VISIBLE COUNTRY_MEANING, fu.DATUM1_CODE BRAND_CODE, fu.DATUM1_VISIBLE BRAND_MEANING, rh.parameter13 COMPANY_REGION_CODE, rh.visible_parameter13 COMPANY_REGION, rh.visible_parameter22 PARENT_PROJECT, lk.lookup_code PROJECT_CLASS_CODE, lk.meaning PROJECT_CLASS_MEANING, rd.visible_parameter41 PROJECT_SUB_TYPE, fml.benefit_type_code BENEFIT_TYPE, fml.category_code BENEFIT_CATEGORY, lk1.meaning BENEFIT_CATEGORY_M, fu.DATUM4_CODE FEEDER_STREAM_DETAIL_CODE, fu.DATUM4_VISIBLE FEEDER_STREAM_DETAIL_MEANING, DECODE (fu.DATUM4_CODE, NULL, NULL, 'INDIRECT_NON_EXTERNAL_BUY', NULL, 'BUY_CO_PACK', 'Buy', 'BUY_INDIRECT', 'Buy', 'BUY_PACK', 'Buy', 'BUY_RAW', 'Buy', 'CATEGORY_', 'Category', 'MAKE_MATERIALS', 'Make', 'MAKE_OPERATING', 'Make', 'MAKE_OTHER', 'Make', 'Ship') FEEDER_STREAM, fu.DATUM5_CODE COMMODITY_CODE, fu.DATUM5_VISIBLE COMMODITY_MEANING, fu.DATUM2_CODE PL_CODE, fu.DATUM2_VISIBLE PL_MEANING, rd1.visible_parameter27 HAVE_FIN_VALIDATED, CLX_CSDB_GET_DATE_SAVING_START (prj.prj_financial_summary_id, fml.BENEFIT_LINE_ID, 'PROJECT', fml.benefit_type_code, fml.category_code, fml.user_data_id) DATE_SAVINGS_START, REPLACE (prj.prj_project_manager_username, '#@#', '; ') PROJECT_MANAGER, REPLACE (rh.visible_parameter46, '#@#', '; ') LOCATION, rd1.visible_parameter24 SUPPLIER_DIVERSITY, fu.DATUM6_VISIBLE PSO_INDIRECT, CLX_CSDB_GET_PRJ_PROBABILITY (prj.prj_project_class_code, s.STATUS_NAME, rd1.parameter27) PROJECT_Probability FROM kcrt_fg_pfm_project prj, kcrt_fg_pfm_project prj1, kcrt_requests r, kcrt_req_header_details rh, kcrt_request_details rd, kcrt_request_details rd1, kcrt_request_details rd2, KCRT_STATUSES_NLS s, knta_lookups_nls lk, knta_lookups_nls lk1, FM_FINANCIAL_SUMMARY fm, FM_BENEFIT_LINES fml, FM_USER_DATA fu WHERE prj.request_type_id IN (SELECT rt.request_type_id FROM kcrt_request_types_nls rt WHERE rt.request_type_name IN ('PPM - Project')) AND prj1.request_type_id = (SELECT rt1.request_type_id FROM kcrt_request_types_nls rt1 WHERE rt1.request_type_name = 'CLX - Child Project') AND r.request_id = prj.request_id AND rD2.request_id = prj1.request_id AND rd2.parameter1 = prj.prj_project_id AND rd2.batch_number = 1 AND r.status_code IN ('IN_PROGRESS', 'CLOSED_SUCCESS') AND lk.lookup_type = 'PFM - Project Class' AND lk.enabled_flag = 'Y' AND lk.user_data1 = 'Y' AND prj.prj_project_class_code = lk.lookup_code AND fml.category_code = lk1.lookup_code AND lk1.lookup_type = 'FINANCIAL_BENEFIT_SAVINGS_CATEGORY' AND lk1.enabled_flag = 'Y' AND r.request_id = rh.request_id AND rh.batch_number = 1 AND r.request_id = rd.request_id AND rd.batch_number = 1 AND r.request_id = rd1.request_id AND rd1.batch_number = 4 AND s.status_id = r.status_id AND s.STATUS_NAME NOT IN ('Not Submitted') AND UPPER (s.STATUS_NAME) NOT LIKE UPPER ('%Cancel%') AND UPPER (s.STATUS_NAME) NOT LIKE UPPER ('%On Hold%') AND UPPER (s.STATUS_NAME) NOT LIKE UPPER ('%Draft%') AND prj1.prj_financial_summary_id = fm.financial_summary_id AND fm.benefit_id = fml.benefit_id AND fml.benefit_type_code = 'SAVINGS' AND fml.USER_DATA_ID = fu.USER_DATA_ID --order by prj.request_id desc; UNION SELECT rd2.parameter2 PROPOSAL_ID, prj.request_id PARENT_PROJECT_ID, prj.prj_project_id PARENT_PRJ_PROJECT_ID, prj.prj_project_name NAME, SUBSTR (rd.parameter50, 1, 250) DESCRIPTION, s.STATUS_NAME STATUS1, DECODE ( RTRIM ( SUBSTR ( s.STATUS_NAME, 1, (INSTR (REPLACE (s.STATUS_NAME, '–', '-'), '-', 1) - 1))), 'Comm', 'Commercialization', 'Dev', 'Development', 'Implemen', 'Implementation', 'Implement', 'Implementation', 'Assessment', 'Assessment', 'OA', 'Assessment', 'Recommendation', 'Recommendation', 'Closure', 'Closure', 'Closed', 'Closed', s.STATUS_NAME) STATUS, rd2.request_id CHILD_PROJECT_ID, prj1.prj_project_name CHILD_PROJECT_NAME, prj.prj_financial_summary_id FS_ID, prj1.prj_financial_summary_id CHILD_FS_ID, fml.BENEFIT_ID BENEFIT_ID, fml.benefit_line_id BENEFIT_LINE_ID, fml.user_data_id USER_DATA_ID, rh.parameter17 Division_Function_Code, rh.visible_parameter17 Division_Function, REPLACE (prj.PRJ_BUSINESS_UNIT_CODE, '#@#', '; ') BUSINESS_UNIT_CODE, REPLACE (prj.PRJ_BUSINESS_UNIT_MEANING, '#@#', '; ') BUSINESS_UNIT_MEANING--, fu.DATUM3_CODE BU_CODE , DECODE (fu.DATUM3_CODE, 'LAUNDRY', 'ONLY_LAUNDRY', 'CHARCOAL', 'ONLY_CHARCOAL', 'FOODS', 'ONLY_FOODS', 'GLAD', 'ONLY_GLAD', 'AFH', 'ONLY_AFH', 'BRITA', 'ONLY_BRITA', 'GREENWORKS', 'ONLY_GREENWORKS', 'HOME_CARE', 'ONLY_HOME_CARE', 'LITTER', 'ONLY_LITTER', fu.DATUM3_CODE) BU_CODE, fu.DATUM3_VISIBLE BU_MEANING, REPLACE (rh.parameter14, '#@#', '; ') SECONDARY_BU_CODE, REPLACE (rh.visible_parameter14, '#@#', '; ') SECONDARY_BU, REPLACE (rh.parameter18, '#@#', '; ') CATEGORY_CODE, REPLACE (rh.visible_parameter18, '#@#', '; ') CATEGORY, fu.DATUM8_CODE COUNTRY_CODE, fu.DATUM8_VISIBLE COUNTRY_MEANING, fu.DATUM1_CODE BRAND_CODE, fu.DATUM1_VISIBLE BRAND_MEANING, rh.parameter32 COMPANY_REGION_CODE, rh.visible_parameter32 COMPANY_REGION, rh.visible_parameter44 PARENT_PROJECT, lk.lookup_code PROJECT_CLASS_CODE, lk.meaning PROJECT_CLASS_MEANING, rd.visible_parameter18 PROJECT_SUB_TYPE, fml.benefit_type_code BENEFIT_TYPE, fml.category_code BENEFIT_CATEGORY, lk1.meaning BENEFIT_CATEGORY_M, fu.DATUM4_CODE FEEDER_STREAM_DETAIL_CODE, fu.DATUM4_VISIBLE FEEDER_STREAM_DETAIL_MEANING, DECODE (fu.DATUM4_CODE, NULL, NULL, 'INDIRECT_NON_EXTERNAL_BUY', NULL, 'BUY_CO_PACK', 'Buy', 'BUY_INDIRECT', 'Buy', 'BUY_PACK', 'Buy', 'BUY_RAW', 'Buy', 'CATEGORY_', 'Category', 'MAKE_MATERIALS', 'Make', 'MAKE_OPERATING', 'Make', 'MAKE_OTHER', 'Make', 'Ship') FEEDER_STREAM, fu.DATUM5_CODE COMMODITY_CODE, fu.DATUM5_VISIBLE COMMODITY_MEANING, fu.DATUM2_CODE PL_CODE, fu.DATUM2_VISIBLE PL_MEANING, rd.visible_parameter27 HAVE_FIN_VALIDATED, CLX_CSDB_GET_DATE_SAVING_START (prj.prj_financial_summary_id, fml.BENEFIT_LINE_ID, 'PROJECT', fml.benefit_type_code, fml.category_code, fml.user_data_id) DATE_SAVINGS_START, REPLACE (prj.prj_project_manager_username, '#@#', '; ') PROJECT_MANAGER, REPLACE (rh.visible_parameter46, '#@#', '; ') LOCATION, rd.visible_parameter24 SUPPLIER_DIVERSITY, fu.DATUM6_VISIBLE PSO_INDIRECT, CLX_CSDB_GET_PRJ_PROBABILITY (prj.prj_project_class_code, s.STATUS_NAME, rd.parameter27) PROJECT_Probability FROM kcrt_fg_pfm_project prj, kcrt_fg_pfm_project prj1, kcrt_requests r, kcrt_req_header_details rh, kcrt_request_details rd, kcrt_request_details rd2, KCRT_STATUSES_NLS s, knta_lookups_nls lk, knta_lookups_nls lk1, FM_FINANCIAL_SUMMARY fm, FM_BENEFIT_LINES fml, FM_USER_DATA fu WHERE prj.request_type_id IN (SELECT rt.request_type_id FROM kcrt_request_types_nls rt WHERE rt.request_type_name IN ('CLX - Project')) AND prj1.request_type_id = (SELECT rt1.request_type_id FROM kcrt_request_types_nls rt1 WHERE rt1.request_type_name = 'CLX - Child Project') AND r.request_id = prj.request_id AND rD2.request_id = prj1.request_id AND rd2.parameter1 = prj.prj_project_id AND rd2.batch_number = 1 AND r.status_code IN ('IN_PROGRESS', 'CLOSED_SUCCESS') AND lk.lookup_type = 'PFM - Project Class' AND lk.enabled_flag = 'Y' AND lk.user_data1 = 'Y' AND prj.prj_project_class_code = lk.lookup_code AND fml.category_code = lk1.lookup_code AND lk1.lookup_type = 'FINANCIAL_BENEFIT_SAVINGS_CATEGORY' AND lk1.enabled_flag = 'Y' AND r.request_id = rh.request_id AND rh.batch_number = 1 AND r.request_id = rd.request_id AND rd.batch_number = 1 AND s.status_id = r.status_id AND s.STATUS_NAME NOT IN ('Not Submitted') AND UPPER (s.STATUS_NAME) NOT LIKE UPPER ('%Cancel%') AND UPPER (s.STATUS_NAME) NOT LIKE UPPER ('%On Hold%') AND UPPER (s.STATUS_NAME) NOT LIKE UPPER ('%Draft%') AND prj1.prj_financial_summary_id = fm.financial_summary_id AND fm.benefit_id = fml.benefit_id AND fml.benefit_type_code = 'SAVINGS' AND fml.USER_DATA_ID = fu.USER_DATA_ID--order by prj.request_id desc; ); CREATE TABLE PPM.CLX_CSDB_RPT_PERIODS ( REPORT_ID VARCHAR2(20 CHAR) NOT NULL, PERIOD_ID NUMBER NOT NULL, MONTH VARCHAR2(20 CHAR) NOT NULL, YEAR NUMBER NOT NULL, QUARTER VARCHAR2(20 CHAR) NOT NULL, SHORT_NAME VARCHAR2(200 CHAR) NOT NULL, LONG_NAME VARCHAR2(200 CHAR) NOT NULL, FS_SOURCE VARCHAR2(20 CHAR), FS_DATA_TYPE VARCHAR2(20 CHAR), MONTH_SEQ NUMBER NOT NULL, SEQ NUMBER NOT NULL ) TABLESPACE PPM_DATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; CREATE INDEX PPM.CLX_CSDB_RPT_PERIODS_1 ON PPM.CLX_CSDB_RPT_PERIODS (REPORT_ID) LOGGING TABLESPACE PPM_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE UNIQUE INDEX PPM.CLX_CSDB_RPT_PERIODS_PK ON PPM.CLX_CSDB_RPT_PERIODS (SEQ) LOGGING TABLESPACE PPM_DATA PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; ALTER TABLE PPM.CLX_CSDB_RPT_PERIODS ADD ( CONSTRAINT CLX_CSDB_RPT_PERIODS_PK PRIMARY KEY (SEQ) USING INDEX PPM.CLX_CSDB_RPT_PERIODS_PK); Explain Plan SELECT STATEMENT ALL_ROWSCost: 726 Bytes: 16,754,248 Cardinality: 952 13 VIEW SYS. Cost: 726 Bytes: 16,754,248 Cardinality: 952 12 TRANSPOSE 11 SORT GROUP BY PIVOT Cost: 726 Bytes: 350,336 Cardinality: 952 10 VIEW PPM. Cost: 725 Bytes: 350,336 Cardinality: 952 9 UNION-ALL 4 MERGE JOIN CARTESIAN Cost: 76 Bytes: 55,328 Cardinality: 104 1 TABLE ACCESS FULL TABLE PPM.CLX_CSDB_RPT_PERIODS Cost: 23 Bytes: 246 Cardinality: 6 3 BUFFER SORT Cost: 53 Bytes: 8,347 Cardinality: 17 2 MAT_VIEW ACCESS FULL MAT_VIEW PPM.CLX_CSDB_PROP_V_MAT Cost: 9 Bytes: 8,347 Cardinality: 17 8 MERGE JOIN CARTESIAN Cost: 649 Bytes: 354,464 Cardinality: 848 5 TABLE ACCESS FULL TABLE PPM.CLX_CSDB_RPT_PERIODS Cost: 23 Bytes: 246 Cardinality: 6 7 BUFFER SORT Cost: 626 Bytes: 50,895 Cardinality: 135 6 MAT_VIEW ACCESS FULL MAT_VIEW PPM.CLX_CSDB_PRJ_CHILD_V_MAT Cost: 104 Bytes: 50,895 Cardinality: 135