DWADW.MV_TRACKING_OM_TEST

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

  CREATE MATERIALIZED VIEW "DWADW"."MV_TRACKING_OM_TEST" ("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", "NUMERO_LINEA", "SPLIT_DE_LINEA", "FULFILL_LINE_ID", "FULFILLMENT_SPLIT_REF_ID", "HEADERID", "ITEMBASEPEOINVENTORYITEMID")
  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
    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,
    F.FULFILLLINESTATUSCODE AS ESTADO_CUMPLIMIENTO_LINEA,
    F.FULFILLLINEFULFILLEDQTY AS CANTIDAD_ENTREGADA,
    H.HEADERCREATIONDATE AS FECHA_DE_CREACION_ORDEN,
    H.HEADERORDERNUMBER AS NUMERO_DE_ORDEN,
    TH.TRANSACTIONHEADERTRXNUMBER AS NRO_TRANSACCION,
    CASE
        WHEN (H.HEADERORDERTYPECODE IS NULL OR LOWER(TRIM(H.HEADERORDERTYPECODE)) = 'nan')
         AND H.HEADERSOURCEDOCUMENTTYPECODE = 'TO'
        THEN 'TRANSFER_ORDER'
        ELSE H.HEADERORDERTYPECODE
    END AS TIPO_DE_ORDEN,
    TH.TRANSACTIONHEADERTRXDATE AS FECHA_FACTURACION,
    H.HEADERSOURCEORDERNUMBER AS ORDEN_DE_ORIGEN,
    H.HEADERSTATUSCODE AS ESTADO_DE_ORDEN,
    CAST(C.PARTY_NAME AS VARCHAR2(200)) AS NOMBRE,
    CAST(C.FISCAL_CODE AS VARCHAR2(20)) AS NIT_CLIENTE,
    CAST(LOC.ADDRESS1 || ', ' || LOC.CITY || ', ' || LOC.STATE AS VARCHAR2(200)) AS LUGAR_ENTREGA,
    ORG.ORGANIZATIONCODE AS ORGANIZACION_INVENTARIO,
    CAST(IA.ATRIBUTO_CHAR8 AS VARCHAR2(80)) AS LINEA_REFERENCIA,
    CAST(IA.ATRIBUTO_CHAR9 AS VARCHAR2(80)) AS MARCA,
    CAST(I.ITEM_NUMBER AS VARCHAR2(40)) AS REFERENCIA,
    TT.PREFIJO_CONSECUTIVO || TH.TRANSACTIONHEADERDOCSEQUENCEVALUE AS NUMERO_DE_FRA,
    H.HEADERCHANGEVERSIONNUMBER AS REVISION,
    ROUND(M.COSTO, 2) AS COSTO,
    F.FULFILLLINEUNITSELLINGPRICE AS VENTA_UNITARIA,
    F.FULFILLLINECANCELEDQTY AS CANTIDAD_CANCELADA,
    SR.SALESREPNUMBER AS CODIGO_VENDEDOR,
    CAST(SR.PARTYNAME AS VARCHAR2(80)) AS NOMBRE_VENDEDOR,
    H.HEADERLASTUPDATEDATE AS FECHA_DE_ULTIMA_ACTUALIZACION,
    CASE WHEN H.HEADERCREATEDBY = 'evol.integraciones' THEN 'UsuarioIntegracion'
         ELSE H.HEADERCREATEDBY END AS CREATED_BY,
    CASE WHEN H.HEADERLASTUPDATEDBY = 'evol.integraciones' THEN 'UsuarioIntegracion'
         ELSE H.HEADERLASTUPDATEDBY END AS LAST_UPDATED_BY,
    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.HEADEROBJECTVERSIONNUMBER) AS REVISION_ERP
    FROM   DWADW.FACT_HEADEREXTRACT_OM DHA_B
    WHERE  DHA_B.HEADERSTATUSCODE NOT IN ('DOO_REFERENCE', '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
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
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
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
LEFT JOIN DWADW.DIM_ITEMS_OM I
    ON  I.INVENTORYITEMID = L.LINEINVENTORYITEMID
    AND I.LANGUAGE = 'E'
LEFT JOIN DWADW.DIM_ITEM_ATTRIBUTE_OM IA
    ON  IA.INVENTORY_ITEM_ID = I.INVENTORYITEMID
    AND IA.ID_ORGANIZACION   = I.ORGANIZATIONID
LEFT JOIN DWADW.DIM_INVORGPARAMETERS_OM ORG
    ON ORG.ORGANIZATIONID = F.FULFILLLINEFULFILLORGID
LEFT JOIN DWADW.FACT_FULFILLLINEDETAIL_OM FD
    ON  FD.FULFILLLINEID = F.FULFILLLINEID
    AND FD.TASKTYPE = 'Invoice'
LEFT JOIN DWADW.FACT_SALESINVOICECUSTOMERTRXLINE_OM TL
    ON TL.CUSTOMERTRXLINEID = FD.CUSTOMERTRXLINEID
LEFT JOIN DWADW.DIM_TRANSACTIONHEADER_OM TH
    ON TH.CUSTOMERTRXID = TL.TRANSACTIONLINECUSTOMERTRXID
LEFT JOIN DWADW.DIM_TYPESTRX_OM TT
    ON TT.CUST_TRX_TYPE_SEQ_ID = TH.TRANSACTIONHEADERCUSTTRXTYPESEQID
-- CAMBIO: costo agregado por línea de cumplimiento, solo registros activos
LEFT JOIN (
    SELECT MARGINDETAILSPEODOCLINEID                 AS FULFILL_LINE_ID,
           SUM(MARGINDETAILSPEORECOGNIZEDCOGSAMOUNT) AS COSTO
    FROM   DWADW.FACT_MARGINDETAILSEXTRACT_OM
    WHERE  MARGINDETAILSPEOCOGSACCOUNTID IS NOT NULL
    AND    ROW_ACTIVE = 'A'
    GROUP  BY MARGINDETAILSPEODOCLINEID
) M
    ON M.FULFILL_LINE_ID = F.FULFILLLINEID;
← Volver al esquema DWADW
🏠 Volver al menú principal