DROP MATERIALIZED VIEW dp_inv_master_MNP_stg;

CREATE MATERIALIZED VIEW dp_inv_master_MNP_stg
PARALLEL NOLOGGING
PARTITION BY HASH(material_id)
BUILD IMMEDIATE
USING INDEX TABLESPACE users
REFRESH FORCE ON DEMAND
WITH ROWID
AS SELECT MANDT AS CLIENT,
       REC_TYPE AS REC_TYPE,
       REC_CATEGORY AS REC_CATEGORY,
       ZRUNDATE AS RUNDATE,
       SYSTEM_ID AS SAP_SYSTEM_ID,
       VBELN AS SALES_DOCUMENT_NO,
       POSNR AS SALES_LINE_ITEM,
       MATNR AS MATERIAL_ID,
       WERKS AS PLANT,
       LGORT AS STORAGE_LOC,
       LGOBE AS STORAGE_LOC_NAME,
       CASE
         WHEN INSTR(MBWBEST, '-', 1) > 0 THEN
          TO_NUMBER(TRANSLATE(MBWBEST, '1234567890-', '1234567890')) * -1
         ELSE
          TO_NUMBER(NVL(TRIM(MBWBEST),'0'))
       END AS QTY,
       CASE
         WHEN INSTR(WBWBEST, '-', 1) > 0 THEN
          TO_NUMBER(TRANSLATE(WBWBEST, '1234567890-', '1234567890')) * -1
         ELSE
          TO_NUMBER(NVL(TRIM(WBWBEST),'0'))
       END AS VALUE,
       EBELN AS PO_DOCUMENT_NO,
       POSNN AS PO_LINE_ITEM,
       CASE
         WHEN INSTR(PRG_PAYMENT, '-', 1) > 0 THEN
          TO_NUMBER(TRANSLATE(PRG_PAYMENT, '1234567890-', '1234567890')) * -1
         ELSE
          TO_NUMBER(NVL(TRIM(PRG_PAYMENT),'0'))
       END AS PROGRESS_PMT,
       BG AS BG_CODE,
       BU AS BU_CODE,
       MAG AS MAG_CODE,
       AG AS AG_CODE,
       KUNNR AS SAP_CUST_NO,
       NAME1 AS ACCOUNT_NAME,
       ORT01 AS CITY,
       REGIO AS STATE,
       PSTLZ AS ZIP,
       VKBUR AS ZONE_CODE,
       BEZEI AS ZONE_DESC,
       COSTCTR AS REGION_CODE,
       REG_NAME AS REGION_DESC,
       LFGJA AS POST_YEAR,
       LFMON AS POST_MONTH,
       LETZTBEW AS LAST_MAT_MOVE_DATE,
       ZZFORINST AS PROJ_USAGE_DATE,
       ERSDA AS SAP_CREATE_DATE
  FROM SAPR3.ZZDASHBOARD_INV@SAP.MNP;

CREATE INDEX dp_inv_MNP_stg_matnr_idx ON dp_inv_master_MNP_stg(MATERIAL_ID) LOCAL TABLESPACE users;
analyze table dp_inv_master_MNP_stg estimate statistics for table for all indexes for all indexed columns;

COMMIT;