CREATE MATERIALIZED VIEW INSIS_CNT_V1.CI_ALL_OK_CALLS (CALL_DATE,CALL_TIME,CALL_REASON,OPERATOR_RESULT,OPERATOR_NAME,ID_INLIST,ABONENT_NUMBER,CUSTOMER_NAME,MAN_ID,CUSTOMER_IIN,CALL_RESULT_INFO)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('01-01-2015','dd-mm-yyyy')
WITH PRIMARY KEY
AS 
/* Formatted on 04/01/2017 14:32:59 (QP5 v5.256.13226.35538) */
SELECT "Call_date" AS CALL_DATE,
       "Call_time" AS CALL_TIME,
       "Call_reason" AS CALL_REASON,
       "Operator_result" AS OPERATOR_RESULT,
       "Operator_name" AS OPERATOR_NAME,
       "IdInList" AS ID_INLIST,
       "AbonentNumber" AS ABONENT_NUMBER,
       DECODE (OK."customer_name",
               NULL, COALESCE (PP.NAME, PP2.NAME),
               OK."customer_name")
          AS CUSTOMER_NAME,
       DECODE (
          TO_CHAR (NVL (OK."man_id", '0')),
          '0', COALESCE (TO_CHAR (PC.MAN_ID), TO_CHAR (PP.MAN_ID)),
          COALESCE (TO_CHAR (PP2.MAN_ID), TO_CHAR (PP.MAN_ID)), TO_CHAR (
                                                                   OK."man_id"))
          AS MAN_ID,
       "customer_iin" AS CUSTOMER_IIN,
       "CallResultInfo" AS CALL_RESULT_INFO
  FROM CI_ALL_OK_CALLS@OKTELL OK
       LEFT JOIN P_CONTACTS PC ON OK."AbonentNumber" = PC.DETAILS
       LEFT JOIN P_PEOPLE PP ON OK."customer_iin" = PP.STATE_ID
       LEFT JOIN P_PEOPLE PP2
          ON UPPER (REPLACE (OK."customer_name", ' ', '')) =
                UPPER (REPLACE (PP2.NAME, ' ', ''));


COMMENT ON MATERIALIZED VIEW INSIS_CNT_V1.CI_ALL_OK_CALLS IS 'snapshot table for snapshot INSIS_CNT_V1.CI_ALL_OK_CALLS';