🏠 Menú principal

← Volver a vistas simples

---

SIMACMOBILE.VW_CONSULTA_ORDENES_CONSOLIDADAS

Base de datos origen: ORCLSMM

Tipo de objeto: VIEW

SQL

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "SIMACMOBILE"."VW_CONSULTA_ORDENES_CONSOLIDADAS" ("ID_ORDEN", "ORIGEN", "ID_ORDEN_INTERNO", "ORDER_OM", "FECHA_ORDEN", "ID_CLERP", "ID_ESPEERP", "CEDI", "DESCRIPCION_RESPUESTA", "COMMERCE_ID", "COMMERCE_NAME", "CODIGO_ESTADO", "DESCRIPCION_ESTADO", "NUMERO_SOLICITUD") AS 
  WITH ULTIMA_TRAZABILIDAD AS
       (
         -- OBTIENE LA ÚLTIMA TRAZABILIDAD GVTA USANDO WINDOW FUNCTION
         SELECT 'GVTA' AS TIPO_ORIGEN,
                ID_GVTAORD AS ORDEN_ID,
                JSON_VALUE(RESPUESTA_JSON, '$.description') AS DESCRIPCION_RESPUESTA,
                ROW_NUMBER() OVER(PARTITION BY ID_GVTAORD ORDER BY ID_GVTATRO DESC) AS RN
         FROM SIMACMOBILE.GVTA_TRAZABILIDAD_ORDEN_PEDIDO

         UNION ALL

         -- OBTIENE LA ÚLTIMA TRAZABILIDAD WHCCIAL USANDO WINDOW FUNCTION
         SELECT 'WHCCIAL' AS TIPO_ORIGEN,
                ID_WHCCIALORD AS ORDEN_ID,
                JSON_VALUE(RESPUESTA_JSON, '$.description') AS DESCRIPCION_RESPUESTA,
                ROW_NUMBER() OVER(PARTITION BY ID_WHCCIALORD ORDER BY ID_WHCCIALTRORD DESC) AS RN
         FROM SIMACMOBILE.WHCCIAL_TRAZABILIDAD_ORDEN_PEDIDO

         UNION ALL

         -- OBTIENE LA ÚLTIMA TRAZABILIDAD GP USANDO WINDOW FUNCTION
         SELECT  'GPED' AS TIPO_ORIGEN,
                 op.ID_GPORDPE AS ORDEN_ID,
                 CASE
                   WHEN DBMS_LOB.INSTR(tzc.RESPONSE_JSON, '{') = 1 THEN
                     --JSON_VALUE(tzc.RESPONSE_JSON, '$.description')
                     REPLACE(REPLACE(COALESCE(
                                       JSON_VALUE(tzc.RESPONSE_JSON, '$.message' RETURNING VARCHAR2(4000) NULL ON ERROR),
                                       JSON_VALUE(tzc.RESPONSE_JSON, '$.description' RETURNING VARCHAR2(4000) NULL ON ERROR)
                                     ), '<![CDATA[', ''), ']]>', '')
                   WHEN DBMS_LOB.INSTR(tzc.RESPONSE_JSON, '{') > 1 THEN
                     --JSON_VALUE(SUBSTR(tzc.RESPONSE_JSON, INSTR(tzc.RESPONSE_JSON, '{')), '$.description')
                     REPLACE(REPLACE(COALESCE(
                                       JSON_VALUE(SUBSTR(tzc.RESPONSE_JSON, INSTR(tzc.RESPONSE_JSON, '{')), '$.message' RETURNING VARCHAR2(4000) NULL ON ERROR),
                                       JSON_VALUE(SUBSTR(tzc.RESPONSE_JSON, INSTR(tzc.RESPONSE_JSON, '{')), '$.description' RETURNING VARCHAR2(4000) NULL ON ERROR)
                                     ), '<![CDATA[', ''), ']]>', '')
                   ELSE
                     DBMS_LOB.SUBSTR(tzc.RESPONSE_JSON, 4000, 1)
                   END AS DESCRIPCION_RESPUESTA,
                 ROW_NUMBER() OVER(PARTITION BY op.ID_GPORDPE ORDER BY op.ID_GPORDPE DESC) AS RN
         FROM SIMACMOBILE.gp_trazabilidad_orden_pedido tzc
                JOIN SIMACMOBILE.gp_consolidado_pedido cop ON cop.ID_GPCLPE = tzc.ID_GPCLPE
                JOIN SIMACMOBILE.gp_orden_pedido op ON op.ID_GPORDPE = cop.ID_GPORDPE

       ),
     ORDENES_BASE AS
       (
         -- ÓRDENES GVTA
         SELECT GOP.ID_GVTAORD AS ID_ORDEN,
                'GVTA' AS ORIGEN,
                GOP.ORDEN_ID_INTERNO AS ID_ORDEN_INTERNO,
                GOP.ORDER_OM,
                GOP.FECHA_ORDEN,
                GOP.ID_CLERP,
                GOP.ID_ESPEERP,
                GOP.CODIGO_SUCURSAL AS CEDI,
                GOP.ID_GVTAORD AS ORDEN_TRAZABILIDAD_ID,
                NVL(GOP.ORDEN_ID, CONCAT('YOM-' , GOP.ID_GVTAORD)) AS NUMERO_SOLICITUD
         FROM SIMACMOBILE.GVTA_ORDEN_PEDIDO GOP

         UNION ALL

         -- ÓRDENES WHCCIAL
         SELECT WOP.ID_WHCCIALORD AS ID_ORDEN,
                'WHCCIAL' AS ORIGEN,
                WOP.IDENTIFICADOR_ORDEN AS ID_ORDEN_INTERNO,
                WOP.ORDER_OM,
                WOP.FECHA_PEDIDO AS FECHA_ORDEN,
                WOP.ID_CLERP,
                WOP.ID_ESPEERP,
                WOP.CODIGO_SUCURSAL AS CEDI,
                WOP.ID_WHCCIALORD AS ORDEN_TRAZABILIDAD_ID,
                CONCAT('WSPC-' , WOP.ID_WHCCIALORD) AS NUMERO_SOLICITUD
         FROM SIMACMOBILE.WHCCIAL_ORDEN_PEDIDO WOP

         UNION ALL

         -- ÓRDENES GPED
         SELECT GOP.ID_GPORDPE AS ID_ORDEN,
                'GPED' AS ORIGEN,
                'GPED-'|| GOP.ID_GPORDPE AS ID_ORDEN_INTERNO,
                GOP.NUMERO_OM ORDER_OM,
                GOP.FECHA_ORDEN,
                GOP.ID_CLERP,
                GOP.ID_ESPEERP,
                GOP.CODIGO_SUCURSAL_CLIENTE AS CEDI,
                GOP.ID_GPORDPE AS ORDEN_TRAZABILIDAD_ID,
                CONCAT('GPED-' , GOP.ID_GPORDPE) AS NUMERO_SOLICITUD
         FROM SIMACMOBILE.GP_ORDEN_PEDIDO GOP
       )
-- QUERY PRINCIPAL OPTIMIZADA
SELECT OB.ID_ORDEN,
       OB.ORIGEN,
       OB.ID_ORDEN_INTERNO,
       OB.ORDER_OM,
       OB.FECHA_ORDEN,
       OB.ID_CLERP,
       OB.ID_ESPEERP,
       OB.CEDI,
       UT.DESCRIPCION_RESPUESTA,
       CE.SITE                  AS COMMERCE_ID,
       CE.PARTY_SITE_NAME       AS COMMERCE_NAME,
       MEPE.CODIGO              AS CODIGO_ESTADO,
       MEPE.DESCRIPCION         AS DESCRIPCION_ESTADO,
       OB.NUMERO_SOLICITUD      AS NUMERO_SOLICITUD
FROM ORDENES_BASE OB
       LEFT JOIN ULTIMA_TRAZABILIDAD UT
                 ON UT.ORDEN_ID = OB.ORDEN_TRAZABILIDAD_ID
                   AND UT.TIPO_ORIGEN = OB.ORIGEN
                   AND UT.RN = 1
       INNER JOIN SIMACMOBILE.MDF_ESTADO_PEDIDO_ERP MEPE
                  ON MEPE.ID_ESPEERP = OB.ID_ESPEERP
       INNER JOIN SIMACMOBILE.CLIENTE_ERP CE
                  ON CE.ID_CLERP = OB.ID_CLERP;

---

← Volver al esquema SIMACMOBILE

🏠 Volver al menú principal