Demo entry 5861414

QueryBrasilsql

   

Submitted by anonymous on Jul 23, 2016 at 20:07
Language: SQL. Code size: 5.0 kB.

LECT      /*+ parallel(a) parallel(b) parallel(c) parallel(d) parallel(e) parallel(f) parallel(g) parallel(h) parallel(i) parallel(j) parallel(k) parallel(l) parallel(m) parallel(n) parallel(o) parallel(p) parallel(q) */
            G.ORG_ID
           ,E.PLANTA_ID
           ,H.REGION_ERP_ID
           ,Q.REGION_SIA_ID
           ,J.GERENCIA_ID
           ,K.DIV_ID
           ,L.AGENCIA_ID
           ,G.ORG_DESC AS ORGANIZACION
           ,TO_CHAR (A.FECH_CERRADO, 'dd-mm-yyyy hh24:mi') AS FECHA_DE_RECIBO_EN_CV
           ,TO_CHAR(D.FECH_VENTA,'dd-mm-yyyy') AS FECHA_VENTA
           ,H.REGION_ERP_DESC AS REGION_DESPACHO_CEDIS
           ,D.ORIGEN_ID AS ID_DESPACHO_CEDIS
           ,E.PLANTA_DESC AS DESPACHO_CEDIS
           ,Q.REGION_SIA_DESC AS REGION_CEVE
           ,J.GERENCIA_DESC AS GERENCIA
           ,K.DIV_DESC AS DIVISION
           ,L.AGENCIA_ID AS ID_CEVE
           ,L.AGENCIA_DESC AS CEVE
           ,M.CANALES_ID ID_CPD_CANAL
           ,M.CANALES_DESC AS CANAL
           ,D.ORDEN_VENTA AS OV
           ,D.NUM_REMISION AS REMISION
           ,N.RAZON_DESC AS INCIDENCIA
           ,DECODE (D.STATUS,0, 'TRANSITO',1, 'CPA',3, 'CERRADA',5, 'DSD',7, 'CADUCADA') AS ESTATUS_REMISION
           ,O.ITEM_PLANTA_NUM AS ITEM
           ,O.ITEM_PLANTA_DESC AS DESCRIPCION
		   ,SUM(C.CANT_ORIGINAL) AS CANT_ORIGINAL
           ,SUM(C.CANT_RECIBIDA) AS CANT_RECIBIDA
           ,SUM(C.CANT_RECIBIDA - C.CANT_ORIGINAL) AS DIFERENCIA
           ,C.UOMS_PLANTA_ID AS ENVASE
           ,C.PRECIO_CPD AS PRECIO_UNIT
           ,SUM (C.CANT_RECIBIDA - C.CANT_ORIGINAL) * C.PRECIO_CPD AS TOT_TOTAL
           ,CASE WHEN B.RAZON_ID = 3 THEN 'CONTENEDOR NO RECEPCIONADO' ELSE 'OK' END AS CAUSA
           ,D.USER_CERRO_EMBARQUE AS USR_CERRO_EMB_DESP_CEDIS
           ,P.USER_NOM AS USR_REC_EMB_CEVE
 
From        ADI_ADD_BRA.CAT_HORARIOS A
           ,ADI_ADD_BRA.CONTENEDOR_DETLL B
           ,ADI_ADD_BRA.REMISION_DETLL_SNAP C
           ,ADI_ADD_BRA.REMISION_ENC_SNAP D
           ,ADI_ADD_BRA.CAT_PLANTAS_ERP E
           ,ADI_ADD_BRA.CAT_PLANTA_LOC F
           ,ADI_ADD_BRA.CAT_ORGANIZACION_ADI G
           ,ADI_ADD_BRA.CAT_REGION_ERP H
           ,ADI_ADD_BRA.CAT_AGENCIA_LOC I
           ,ADI_ADD_BRA.CAT_GERENCIA_ADI J
           ,ADI_ADD_BRA.CAT_DIVISION_ADI K
           ,ADI_ADD_BRA.CAT_AGENCIA_ERP L
           ,ADI_ADD_BRA.CAT_CANALES_ERP M
           ,ADI_ADD_BRA.CAT_RAZONES_ADI N
           ,ADI_ADD_BRA.CAT_ITEM_ERP O
           ,ADI_ADD_BRA.CAT_USER_POD P
           ,ADI_ADD_BRA.CAT_REGION_ADI Q
 
Where           A.FECH_CERRADO >=  TRUNC (SYSDATE - 2) + 9.25 / 24
            AND A.FECH_CERRADO <     TRUNC (SYSDATE ) + 9.25 / 24
            AND A.NUM_REMISION    =  B.NUM_REMISION
            AND A.NUM_EMBARQUE   =  B.NUM_EMBARQUE
            AND A.ORIGEN_ID           =  B.ORIGEN_ID
            AND A.NUM_REMISION    =  C.NUM_REMISION
            AND A.NUM_EMBARQUE   =  C.NUM_EMBARQUE
            AND A.ORIGEN_ID           =  C.ORIGEN_ID
            AND A.NUM_REMISION    =  D.NUM_REMISION
            AND A.NUM_EMBARQUE   =  D.NUM_EMBARQUE
            AND A.ORIGEN_ID           =  D.ORIGEN_ID
            AND D.ORIGEN_ID           =  E.PLANTA_ID
            AND E.PLANTA_ID           =  F.PLANTA_ID
            AND F.ORG_ID                =  G.ORG_ID
            AND F.REGION_ERP_ID    =  H.REGION_ERP_ID
            AND I.DIV_ID                 =  K.DIV_ID
            AND I.AGENCIA_ID          = L.AGENCIA_ID
            AND L.AGENCIA_ID          = M.AGENCIA
            AND I.GERENCIA_ID = J.GERENCIA_ID
            AND D.CANALES_ID        = M.CANALES_ID
            AND B.RAZON_ID            = N.RAZON_ID
            AND B.CANT_RECIBIDA   IS NOT NULL
            AND C.CANT_ORIGINAL - C.CANT_RECIBIDA != 0
            AND N.RAZON_ID != 0
            AND B.ITEM_PLANTA_ID = C.ITEM_PLANTA_ID
            AND C.ITEM_PLANTA_ID = O.ITEM_PLANTA_ID
            AND D.USER_RECIBIO_EMBARQUE = P.NICKNAME
            AND I.REGION_SIA_ID = Q.REGION_SIA_ID

 
GROUP BY
             G.ORG_ID
            ,E.PLANTA_ID
            ,H.REGION_ERP_ID
            ,Q.REGION_SIA_ID
            ,J.GERENCIA_ID
            ,K.DIV_ID
            ,L.AGENCIA_ID
            ,G.ORG_DESC
            ,A.FECH_CERRADO
            ,D.FECH_VENTA
            ,H.REGION_ERP_DESC
            ,D.ORIGEN_ID
            ,E.PLANTA_DESC
            ,Q.REGION_SIA_DESC
            ,J.GERENCIA_DESC
            ,K.DIV_DESC
            ,L.AGENCIA_ID
            ,L.AGENCIA_DESC
            ,M.CANALES_ID
            ,M.CANALES_DESC
            ,D.ORDEN_VENTA
            ,D.NUM_REMISION
            ,N.RAZON_DESC
            ,D.STATUS
            ,O.ITEM_PLANTA_NUM
            ,O.ITEM_PLANTA_DESC
            ,C.UOMS_PLANTA_ID
            ,C.PRECIO_CPD
            ,B.RAZON_ID
            ,D.USER_CERRO_EMBARQUE
            ,P.USER_NOM

This snippet took 0.00 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).