DWADW.MV_TRACKING_OM

Base de datos origen: ADW COEXITO
Tipo de objeto: MATERIALIZED VIEW
SQL

  CREATE MATERIALIZED VIEW "DWADW"."MV_TRACKING_OM" ("CANTIDAD_ORDENADA", "ESTADO_CUMPLIMIENTO_LINEA", "CANTIDAD_ENTREGADA", "FECHA_DE_CREACION_ORDEN", "NUMERO_DE_ORDEN", "NRO_TRANSACCION", "TIPO_DE_ORDEN", "FECHA_FACTURACION", "ORDEN_DE_ORIGEN", "ESTADO_DE_ORDEN", "NOMBRE", "NIT_CLIENTE", "LUGAR_ENTREGA", "ORGANIZACION_INVENTARIO", "LINEA_REFERENCIA", "MARCA", "REFERENCIA", "NUMERO_DE_FRA", "REVISION", "COSTO", "VENTA_UNITARIA", "CANTIDAD_CANCELADA", "CODIGO_VENDEDOR", "NOMBRE_VENDEDOR", "FECHA_DE_ULTIMA_ACTUALIZACION", "CREATED_BY", "LAST_UPDATED_BY")
  DEFAULT COLLATION "USING_NLS_COMP"  SEGMENT CREATION IMMEDIATE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 
 COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING LOGGING
  BUILD DEFERRED
  USING INDEX 
  REFRESH COMPLETE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE DISABLE CONCURRENT REFRESH
  AS SELECT DISTINCT

    -- CANTIDADES / ESTADO DE CUMPLIMIENTO
    CASE
        WHEN F.FULFILLLINEORDEREDQTY <
             (
                 NVL(F.FULFILLLINECANCELEDQTY, 0)
                 + NVL(F.FULFILLLINEFULFILLEDQTY, 0)
             )
         AND F.FULFILLLINESTATUSCODE IN ('CLOSED', 'CANCELED')
        THEN NVL(F.FULFILLLINECANCELEDQTY, 0)
             + NVL(F.FULFILLLINEFULFILLEDQTY, 0)
        ELSE NVL(F.FULFILLLINEORDEREDQTY, 0)
    END AS CANTIDAD_ORDENADA,

    CAST(
        F.FULFILLLINESTATUSCODE
        AS VARCHAR2(30 CHAR)
    ) AS ESTADO_CUMPLIMIENTO_LINEA,

    F.FULFILLLINEFULFILLEDQTY AS CANTIDAD_ENTREGADA,

    -- CABECERA DE ORDEN
    H.HEADERCREATIONDATE AS FECHA_DE_CREACION_ORDEN,

    CAST(
        H.HEADERORDERNUMBER
        AS VARCHAR2(15 CHAR)
    ) AS NUMERO_DE_ORDEN,

    CAST(
        TH.TRANSACTIONHEADERTRXNUMBER
        AS VARCHAR2(15 CHAR)
    ) AS NRO_TRANSACCION,

    CAST(
        CASE
            WHEN (
                H.HEADERORDERTYPECODE IS NULL
                OR LOWER(TRIM(H.HEADERORDERTYPECODE)) = 'nan'
            )
            AND H.HEADERSOURCEDOCUMENTTYPECODE = 'TO'
            THEN 'TRANSFER_ORDER'
            ELSE H.HEADERORDERTYPECODE
        END
        AS VARCHAR2(30 CHAR)
    ) AS TIPO_DE_ORDEN,

    TH.TRANSACTIONHEADERTRXDATE AS FECHA_FACTURACION,

    CAST(
        H.HEADERSOURCEORDERNUMBER
        AS VARCHAR2(30 CHAR)
    ) AS ORDEN_DE_ORIGEN,

    CAST(
        H.HEADERSTATUSCODE
        AS VARCHAR2(30 CHAR)
    ) AS ESTADO_DE_ORDEN,

    -- CLIENTE
    CAST(
        C.PARTY_NAME
        AS VARCHAR2(160 CHAR)
    ) AS NOMBRE,

    CAST(
        C.FISCAL_CODE
        AS VARCHAR2(20 CHAR)
    ) AS NIT_CLIENTE,

    -- LUGAR DE ENTREGA
    CAST(
        LOC.ADDRESS1 || ', ' || LOC.CITY || ', ' || LOC.STATE
        AS VARCHAR2(160 CHAR)
    ) AS LUGAR_ENTREGA,

    -- ORGANIZACIÓN
    CAST(
        ORG.ORGANIZATIONCODE
        AS VARCHAR2(30 CHAR)
    ) AS ORGANIZACION_INVENTARIO,

    -- ATRIBUTOS DEL ITEM
    CAST(
        IA.ATRIBUTO_CHAR8
        AS VARCHAR2(50 CHAR)
    ) AS LINEA_REFERENCIA,

    CAST(
        IA.ATRIBUTO_CHAR9
        AS VARCHAR2(60 CHAR)
    ) AS MARCA,

    CAST(
        I.ITEM_NUMBER
        AS VARCHAR2(20 CHAR)
    ) AS REFERENCIA,

    -- NÚMERO DE FRA
    CAST(
        TT.PREFIJO_CONSECUTIVO ||
        TH.TRANSACTIONHEADERDOCSEQUENCEVALUE
        AS VARCHAR2(20 CHAR)
    ) AS NUMERO_DE_FRA,

    -- REVISIÓN
    H.HEADERCHANGEVERSIONNUMBER AS REVISION,

    -- COSTO / VENTA
    ROUND(
        M.MARGINDETAILSPEORECOGNIZEDCOGSAMOUNT, 2
    ) AS COSTO,

    F.FULFILLLINEUNITSELLINGPRICE AS VENTA_UNITARIA,

    F.FULFILLLINECANCELEDQTY AS CANTIDAD_CANCELADA,

    -- VENDEDOR
    CAST(
        SR.SALESREPNUMBER
        AS VARCHAR2(25 CHAR)
    ) AS CODIGO_VENDEDOR,

    CAST(
        SR.PARTYNAME
        AS VARCHAR2(40 CHAR)
    ) AS NOMBRE_VENDEDOR,

    -- AUDITORÍA
    H.HEADERLASTUPDATEDATE
        AS FECHA_DE_ULTIMA_ACTUALIZACION,

    CAST(
        CASE
            WHEN H.HEADERCREATEDBY = 'evol.integraciones'
            THEN 'UsuarioIntegracion'
            ELSE H.HEADERCREATEDBY
        END
        AS VARCHAR2(50 CHAR)
    ) AS CREATED_BY,

    CAST(
        CASE
            WHEN H.HEADERLASTUPDATEDBY = 'evol.integraciones'
            THEN 'UsuarioIntegracion'
            ELSE H.HEADERLASTUPDATEDBY
        END
        AS VARCHAR2(50 CHAR)
    ) AS LAST_UPDATED_BY

    -- CAMPOS DE CONTROL
    /*
    F.FULFILLLINESOURCELINENUMBER AS NUMERO_LINEA,
    F.FULFILLLINEFULFILLLINENUMBER AS SPLIT_DE_LINEA,
    F.FULFILLLINEID AS FULFILL_LINE_ID,
    F.FULFILLLINEFULFILLMENTSPLITREFID
        AS FULFILLMENT_SPLIT_REF_ID,
    H.HEADERID AS HEADERID,
    I.INVENTORYITEMID AS ITEMBASEPEOINVENTORYITEMID
    */

FROM DWADW.FACT_HEADEREXTRACT_OM H

INNER JOIN (
    SELECT
        DHA_B.HEADERORDERNUMBER,
        MAX(DHA_B.HEADERCHANGEVERSIONNUMBER)
            AS MAX_CHANGEVERSIONNUMBER,
        MAX(DHA_B.HEADEROBJECTVERSIONNUMBER)
            AS REVISION_ERP
    FROM DWADW.FACT_HEADEREXTRACT_OM DHA_B
    WHERE DHA_B.HEADERSTATUSCODE <> 'DOO_REFERENCE'
      AND DHA_B.HEADERSTATUSCODE <> 'DOO_DRAFT'
    GROUP BY DHA_B.HEADERORDERNUMBER
) H_BOLV
    ON H_BOLV.HEADERORDERNUMBER = H.HEADERORDERNUMBER
   AND H_BOLV.REVISION_ERP = H.HEADEROBJECTVERSIONNUMBER

INNER JOIN DWADW.FACT_LINEEXTRACT_OM L
    ON L.LINEHEADERID = H.HEADERID

INNER JOIN DWADW.FACT_FULFILLLINE_OM F
    ON F.FULFILLLINEHEADERID = L.LINEHEADERID
   AND F.FULFILLLINELINEID = L.LINEID
   AND H.HEADERID = F.HEADERHEADERID

-- VENDEDOR
LEFT JOIN DWADW.DIM_SALESCREDIT_OM SC
    ON SC.SALESCREDITFULFILLLINEID = F.FULFILLLINEID

LEFT JOIN (
    SELECT *
    FROM (
        SELECT
            SR.*,
            ROW_NUMBER() OVER (
                PARTITION BY RESOURCEID
                ORDER BY RESOURCESALESREPPEOLASTUPDATEDATE DESC
            ) RN
        FROM DWADW.DIM_SALESREP_OM SR
        WHERE SR.RESOURCESALESREPPEOSTATUS = 'A'
    )
    WHERE RN = 1
) SR
    ON SR.RESOURCEID = SC.SALESCREDITSALESPERSONID

-- CLIENTE
LEFT JOIN DWADW.DIM_CUSTOMER_AR C
    ON C.PARTY_ID = H.HEADERSOLDTOPARTYID

LEFT JOIN DWADW.DIM_ORGANIZATIONCUSTOMER_AR OC
    ON OC.PARTYID = C.PARTY_ID

-- LUGAR DE ENTREGA
LEFT JOIN DWADW.DIM_SITECUSTOMER_AR SITEC
    ON SITEC.PARTYID = C.PARTY_ID
   AND SITEC.LOCATIONID = OC.IDENADDRLOCATIONID

LEFT JOIN DWADW.DIM_LACSCUSTOMER_AR LOC
    ON LOC.LOCATION_ID = SITEC.LOCATIONID

-- ITEMS
LEFT JOIN DWADW.DIM_ITEMS_OM I
    ON I.INVENTORYITEMID = L.LINEINVENTORYITEMID
   --AND I.ORGANIZATIONID = L.LINEINVENTORYORGANIZATIONID
   AND I.LANGUAGE = 'E'

-- ATRIBUTOS DEL ITEM
LEFT JOIN DWADW.DIM_ITEM_ATTRIBUTE_OM IA
    ON IA.INVENTORY_ITEM_ID = I.INVENTORYITEMID
   AND IA.ID_ORGANIZACION = I.ORGANIZATIONID

-- ORGANIZACIÓN INVENTARIO
LEFT JOIN DWADW.DIM_INVORGPARAMETERS_OM ORG
    ON ORG.ORGANIZATIONID = F.FULFILLLINEFULFILLORGID

-- FULFILL LINE DETAIL
LEFT JOIN DWADW.FACT_FULFILLLINEDETAIL_OM FD
    ON FD.FULFILLLINEID = F.FULFILLLINEID
   AND FD.TASKTYPE = 'Invoice'

-- CUSTOMER TRX LINE
LEFT JOIN DWADW.FACT_SALESINVOICECUSTOMERTRXLINE_OM TL
    ON TL.CUSTOMERTRXLINEID = FD.CUSTOMERTRXLINEID

-- TRANSACTION HEADER
LEFT JOIN DWADW.DIM_TRANSACTIONHEADER_OM TH
    ON TH.CUSTOMERTRXID = TL.TRANSACTIONLINECUSTOMERTRXID

-- TIPO DE TRANSACCIÓN
LEFT JOIN DWADW.DIM_TYPESTRX_OM TT
    ON TT.CUST_TRX_TYPE_SEQ_ID =
       TH.TRANSACTIONHEADERCUSTTRXTYPESEQID

-- MARGIN / COSTO
LEFT JOIN DWADW.FACT_MARGINDETAILSEXTRACT_OM M
    ON M.MARGINDETAILSPEOINVENTORYITEMID =
       L.LINEINVENTORYITEMID
   AND M.MARGINDETAILSPEODOOORDERNUMBER =
       H.HEADERSOURCEORDERNUMBER
   AND M.MARGINDETAILSPEOCOGSACCOUNTID IS NOT NULL;
← Volver al esquema DWADW
🏠 Volver al menú principal