Diccionario de Datos
Diccionario de datos de los modelos de COÉXITO
| 🏠 Menú principal | ← Volver a vistas materializadas |
SIMACMOBILE.VISTA_REPORTE_CUMPLIMIENTO_GANEMAS
Base de datos origen: ORCLSMM
Tipo de objeto: MATERIALIZED VIEW
SQL
CREATE MATERIALIZED VIEW "SIMACMOBILE"."VISTA_REPORTE_CUMPLIMIENTO_GANEMAS" ("ANO", "MES", "CODIGO_SUCURSAL", "ID_CLIENTE", "NIT_CLIENTE", "SITE_CLIENTE", "NOMBRE_CLIENTE", "CODIGO_VENDEDOR", "ESTADO_CLIENTE", "ID_SUCURSAL", "ID_VENDEDOR", "ID_PERIODO_CONTABLE", "CUOTA_BAPUS", "BAPUS_RECOGIDAS", "VENTA_PERIODO_ANTERIOR", "BONUS_MES", "CUOTA_TRIMESTRE", "BONUS_TRIMESTRE", "RECOGIDAS_TRIMESTRE", "VENTAS_TRIMESTRE", "BAPUS_RECOGIDAS_VALIDADAS", "CUOTA_BAPUS_EXCEDENTE_MES_ANTERIOR", "FECHA_ULTIMA_NOTA_CREDITO")
SEGMENT CREATION IMMEDIATE
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
BUILD DEFERRED
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
AS SELECT
PCE.ANO,
PCE.MES,
SE.CODIGO AS CODIGO_SUCURSAL,
CBE.ID_CLERP AS ID_CLIENTE,
CE.NIT AS NIT_CLIENTE,
CE.SITE AS SITE_CLIENTE,
CASE
WHEN CE.NATURALEZA IS NOT NULL AND CE.NATURALEZA = 'JURIDICA' THEN
CE.RAZON_SOCIAL
ELSE
CE.PARTY_SITE_NAME
END AS NOMBRE_CLIENTE,
CASE
WHEN VE.CODIGO IS NULL THEN
'ERROR:Vendedor no encontrado'
ELSE
VE.CODIGO
END AS CODIGO_VENDEDOR,
CBE.ESTADO_CLIENTE,
CBE.ID_SUERP AS ID_SUCURSAL,
NVL(CBE.ID_VERP, 0) AS ID_VENDEDOR,
CBE.ID_PECOERP AS ID_PERIODO_CONTABLE,
NVL(CBE.CUOTA_BAPUS, 0) AS CUOTA_BAPUS,
NVL(CBE.BAPUS_RECOGIDAS, 0) AS BAPUS_RECOGIDAS,
NVL(CBE.VENTA_PERIODO_ANTERIOR, 0) AS VENTA_PERIODO_ANTERIOR,
NVL(CBE.BONIFICACION, 0) AS BONUS_MES,
NVL(SUM(CBE2.CUOTA_BAPUS), 0) AS CUOTA_TRIMESTRE,
NVL(SUM(CBE2.BONIFICACION), 0) AS BONUS_TRIMESTRE,
NVL(SUM(CBE2.BAPUS_RECOGIDAS), 0) AS RECOGIDAS_TRIMESTRE,
NVL(SUM(CBE2.VENTA_PERIODO_ANTERIOR), 0) AS VENTAS_TRIMESTRE,
NVL((
SELECT
FLOOR(NVL(SUM(VHE.CANTIDAD_ITEM * RE.EQUIVALENCIA_GANEMAS),
0)) AS A
FROM
SIMACMOBILE.VENTA_HISTORICO_ERP VHE
INNER JOIN SIMACMOBILE.REFERENCIA_ERP RE
ON VHE.CODIGO_ITEM = RE.CODIGO
INNER JOIN SIMACMOBILE.CLIENTE_ERP CE
ON VHE.CLIENTE_SITE = CE.SITE
INNER JOIN SIMACMOBILE.SUCURSAL_ERP S1
ON SUBSTR(VHE.CODIGO_SUCURSAL_ORACLE,
1,
3) = S1.CODIGO
AND S1.ID_SUERP = CBE.ID_SUERP
INNER JOIN SIMACMOBILE.SUCURSAL_ERP S2
ON SUBSTR(VHE.CLIENTE_SITE,
1,
1) = S2.CODIGO_REGIONAL
AND S2.ID_SUERP = CBE.ID_SUERP
WHERE
VHE.TIPO_TRANS LIKE 'BP%'
AND RE.SUBLINEA_COMERCIAL = 'BATERIAS_USADAS'
AND RE.EQUIVALENCIA_GANEMAS IS NOT NULL
AND CE.ID_SUERP IN (
SELECT
ID_SUERP
FROM
SIMACMOBILE.SUCURSAL_ERP
WHERE
CODIGO_REGIONAL = S2.CODIGO_REGIONAL
)
AND EXTRACT(YEAR FROM VHE.FECHA_VENTA) = PCE.ANO
AND EXTRACT(MONTH FROM VHE.FECHA_VENTA) = PCE.MES
AND VHE.CLIENTE_SITE = (
SELECT
C.SITE
FROM
SIMACMOBILE.CLIENTE_ERP C
WHERE
C.ID_CLERP = CBE.ID_CLERP
)
), 0) AS BAPUS_RECOGIDAS_VALIDADAS,
NVL((
SELECT
NVL(TO_NUMBER(MIN(CB.CUOTA_BAPUS_EXCEDENTE), '9999999999.99'), 0)
FROM
SIMACMOBILE.CUOTA_BAPUS_ERP CB
WHERE
CB.ID_PECOERP = (
SELECT
PC.ID_PECOERP
FROM
SIMACMOBILE.PERIODO_CONTABLE_ERP PC
WHERE
( TO_NUMBER(PC.ANO) =
CASE
WHEN TO_NUMBER(PCE.MES) = 1 THEN
TO_NUMBER(PCE.ANO) - 1
ELSE
TO_NUMBER(PCE.ANO)
END )
AND TO_NUMBER(PC.MES) = CASE
WHEN TO_NUMBER(PCE.MES) = 1 THEN
12
ELSE
TO_NUMBER(PCE.MES) - 1
END
)
AND CB.ID_CLERP = CBE.ID_CLERP
),
0) AS CUOTA_BAPUS_EXCEDENTE_MES_ANTERIOR,
CBE.FECHA_ULTIMA_NOTA_CREDITO
FROM
CUOTA_BAPUS_ERP CBE
JOIN SUCURSAL_ERP SE
ON SE.ID_SUERP = CBE.ID_SUERP
LEFT JOIN CUOTA_BAPUS_ERP CBE2
ON CBE2.ID_CLERP = CBE.ID_CLERP
LEFT JOIN PERIODO_CONTABLE_ERP PCE
ON PCE.ID_PECOERP = CBE.ID_PECOERP
LEFT JOIN PERIODO_CONTABLE_ERP PCE2
ON PCE2.ID_PECOERP = CBE2.ID_PECOERP
LEFT JOIN CLIENTE_ERP CE
ON CE.ID_CLERP = CBE.ID_CLERP
LEFT JOIN VENDEDOR_ERP VE
ON VE.ID_VERP = CBE.ID_VERP
WHERE
SE.CODIGO_REGIONAL IN (
SELECT
S.CODIGO_REGIONAL
FROM
SUCURSAL_ERP S
WHERE
S.ID_SUERP = (
SELECT
ID_SUERP
FROM
SUCURSAL_ERP
WHERE
CODIGO = SE.CODIGO
)
)
AND CBE.ID_PECOERP = (
SELECT
ID_PECOERP
FROM
PERIODO_CONTABLE_ERP
WHERE
ANO = PCE.ANO
AND MES = PCE.MES
)
AND CBE.ID_CLERP IS NOT NULL
AND PCE2.TRIMESTRE = (
SELECT
P.TRIMESTRE
FROM
PERIODO_CONTABLE_ERP P
WHERE
P.ID_PECOERP = (
SELECT
ID_PECOERP
FROM
PERIODO_CONTABLE_ERP
WHERE
ANO = PCE.ANO
AND MES = PCE.MES
)
)
GROUP BY
PCE.ANO,
PCE.MES,
SE.CODIGO,
CE.NIT,
CE.SITE,
CE.NATURALEZA,
CE.RAZON_SOCIAL,
CE.PARTY_SITE_NAME,
VE.CODIGO,
CBE.ID_CLERP,
CBE.ESTADO_CLIENTE,
CBE.ID_SUERP,
CBE.ID_VERP,
CBE.ID_PECOERP,
CBE.CUOTA_BAPUS,
CBE.BAPUS_RECOGIDAS,
CBE.VENTA_PERIODO_ANTERIOR,
CBE.FECHA_ULTIMA_NOTA_CREDITO,
CBE.BONIFICACION;