LuisLeon_FactuGES/SCRIPTS/SCRIPT STOCK(ROBERTO).sql

DROP VIEW V_PED_CLI_SITUACION;
DROP VIEW V_PED_CLI_ARTICULOS;
DROP VIEW V_PED_CLI_ARTICULOS_AUX;
DROP VIEW V_PED_CLI_ART_SITUACION_CANT;
DROP VIEW V_PED_CLI_ART_SITUACION;
DROP VIEW V_PED_CLI_DETALLES;
DROP VIEW V_ALB_CLI_DETALLES;
DROP VIEW V_PED_PROV_SITUACION;
DROP VIEW V_PED_PROV_ARTICULOS;
DROP VIEW V_PED_PROV_ARTICULOS_AUX;
DROP VIEW V_PED_PROV_ARTICULOS_RECIBIDOS;
DROP VIEW V_PED_PROV_DETALLES;
DROP VIEW V_ALB_PROV_DETALLES;
DROP VIEW V_ALB_CLI_SITUACION;


/* Situación de los albaranes de cliente */
CREATE VIEW V_ALB_CLI_SITUACION (
    ID,
    SITUACION)
AS
SELECT ALBARANES_CLIENTE.ID,
  case when (FECHA_RECEPCION is not null) then 'SERVIDO'
       when (FECHA_ENVIO is null) then 'PENDIENTE'
       when (FECHA_ENVIO <= current_date) then 'ENVIADO'
       when (FECHA_ENVIO > current_date) then 'PENDIENTE'
       else 'N/A'
  end as SITUACION
FROM ALBARANES_CLIENTE
;


/*Agrupa los artículos de un mismo albarán (ya que en un albarán puede existir varias lineas con el mismo artículo).
  Para cada artículo de albarán le ponemos el pedido con el que esta asociado y el almacén donde se recibio.
  Se quitan todos los artículos que no tengamos en catálogo (ID_ARTICULO nulo, lineas de detalle libres)
  Se quitan también aquellos que no seán inventariables */

CREATE VIEW V_ALB_PROV_DETALLES(
    ID_ALBARAN,
    ID_PEDIDO,
    ID_ALMACEN,
    ID_ARTICULO,
    CANTIDAD)
AS
SELECT  ALBARANES_PROVEEDOR_DETALLES.ID_ALBARAN,
        ALBARANES_PROVEEDOR.ID_PEDIDO,
        ALBARANES_PROVEEDOR.ID_ALMACEN,
        ALBARANES_PROVEEDOR_DETALLES.ID_ARTICULO,
        SUM (COALESCE(ALBARANES_PROVEEDOR_DETALLES.CANTIDAD, 0)) AS CANTIDAD

FROM ALBARANES_PROVEEDOR_DETALLES
LEFT JOIN ALBARANES_PROVEEDOR
ON (ALBARANES_PROVEEDOR_DETALLES.ID_ALBARAN = ALBARANES_PROVEEDOR.ID)
LEFT JOIN ARTICULOS
ON (ALBARANES_PROVEEDOR_DETALLES.ID_ARTICULO = ARTICULOS.ID)

WHERE (ALBARANES_PROVEEDOR_DETALLES.ID_ARTICULO is not null)
AND   (ARTICULOS.INVENTARIABLE = 1)

GROUP BY ALBARANES_PROVEEDOR_DETALLES.ID_ALBARAN,
        ALBARANES_PROVEEDOR.ID_PEDIDO,
        ALBARANES_PROVEEDOR.ID_ALMACEN,
        ALBARANES_PROVEEDOR_DETALLES.ID_ARTICULO;


/*Agrupa los artículos de un mismo pedido (ya que en un pedido puede existir varias lineas con el mismo artículo).
  Para cada artículo de pedido le ponemos el pedido con el que esta asociado y el almacén donde será recibido.
  Se quitan todos los artículos que no tengamos en catálogo (ID_ARTICULO nulo, lineas de detalle libres)
  Se quitan también aquellos que no seán inventariables */

CREATE VIEW V_PED_PROV_DETALLES(
    ID_PEDIDO,
    ID_ALMACEN,
    ID_ARTICULO,
    CANTIDAD)
AS
SELECT  PEDIDOS_PROVEEDOR_DETALLES.ID_PEDIDO,
        PEDIDOS_PROVEEDOR.ID_ALMACEN,
        PEDIDOS_PROVEEDOR_DETALLES.ID_ARTICULO,
        SUM(COALESCE(PEDIDOS_PROVEEDOR_DETALLES.CANTIDAD, 0))

FROM PEDIDOS_PROVEEDOR_DETALLES
LEFT JOIN PEDIDOS_PROVEEDOR
ON (PEDIDOS_PROVEEDOR_DETALLES.ID_PEDIDO = PEDIDOS_PROVEEDOR.ID)
LEFT JOIN ARTICULOS
ON (PEDIDOS_PROVEEDOR_DETALLES.ID_ARTICULO = ARTICULOS.ID)

WHERE (PEDIDOS_PROVEEDOR_DETALLES.ID_ARTICULO is not null)
AND   (ARTICULOS.INVENTARIABLE = 1)

GROUP BY PEDIDOS_PROVEEDOR_DETALLES.ID_PEDIDO,
         PEDIDOS_PROVEEDOR.ID_ALMACEN,
         PEDIDOS_PROVEEDOR_DETALLES.ID_ARTICULO;


/*Agrupa todos los artículos recibidos (por albaranes de proveedor) de cada uno de los pedidos asociados en los albaranes existentes*/
/*No le ponemos el almacen donde se recibió ya que un mismo pedido podría tener varios albaranes con distintos almacenes de destino*/
/*Aquellos articulos que no tengan ID_PEDIDO es porque el albarán al que pertenecen no tiene pedido asociado por ello no los tendremos en cuenta*/

CREATE VIEW V_PED_PROV_ARTICULOS_RECIBIDOS(
    ID_PEDIDO,
    ID_ARTICULO,
    CANTIDAD)
AS
SELECT ID_PEDIDO, ID_ARTICULO, SUM(CANTIDAD) AS CANTIDAD
FROM V_ALB_PROV_DETALLES
WHERE (ID_PEDIDO is not null)
GROUP BY ID_PEDIDO, ID_ARTICULO;


/*No hacemos LEFT JOIN entre V_PED_PROV_DETALLES y V_PED_PROV_ARTICULOS_RECIBIDOS, porque por cada tupla de la primera*/
/*repetiría la consulta de la segunda disparando tiempos, por ello teniendo las dos vistas ejecutadas hacemos una union*/
/*obteniendo todos los artículos del pedido, tanto recibidos como pendientes, luego haremos la agrupación sobre este resultado*/

CREATE VIEW V_PED_PROV_ARTICULOS_AUX(
    ID_PEDIDO,
    ID_ARTICULO,
    CANTIDAD_PEDIDA,
    CANTIDAD_RECIBIDA)
AS
SELECT
      V_PED_PROV_DETALLES.ID_PEDIDO,
      V_PED_PROV_DETALLES.ID_ARTICULO,
      V_PED_PROV_DETALLES.CANTIDAD AS CANTIDAD_PEDIDA,
      0 as CANTIDAD_RECIBIDA
FROM V_PED_PROV_DETALLES
UNION
SELECT
      V_PED_PROV_ARTICULOS_RECIBIDOS.ID_PEDIDO,
      V_PED_PROV_ARTICULOS_RECIBIDOS.ID_ARTICULO,
      NULL as CANTIDAD_PEDIDA,
      V_PED_PROV_ARTICULOS_RECIBIDOS.CANTIDAD AS CANTIDAD_RECIBIDA
FROM V_PED_PROV_ARTICULOS_RECIBIDOS;


/*A partir de la vista anterior obtenemos el estado de cada uno de los artículos del pedido de proveedor*/
/*Despreciamos aquellas tuplas cuya cantidad pedida sea null, porque son articulos añadidos en el albarán que*/
/*no están en su pedido correspondiente por lo tanto no los tendremos en cuenta para saber si los articulos del*/
/*pedido se han recibido todos. De todas formas no tiene mucho sentido este caso*/

CREATE VIEW V_PED_PROV_ARTICULOS(
    ID_PEDIDO,
    ID_ALMACEN,
    ID_ARTICULO,
    CANTIDAD_PEDIDA,
    CANTIDAD_RECIBIDA,
    CANTIDAD_PENDIENTE)
AS
SELECT ID_PEDIDO,
       PEDIDOS_PROVEEDOR.ID_ALMACEN,
       ID_ARTICULO,
       SUM(CANTIDAD_PEDIDA) as CANTIDAD_PEDIDA,
       SUM(CANTIDAD_RECIBIDA) as CANTIDAD_RECIBIDA,
       SUM(CANTIDAD_PEDIDA) - SUM(CANTIDAD_RECIBIDA) as CANTIDAD_PENDIENTE
FROM V_PED_PROV_ARTICULOS_AUX
LEFT JOIN PEDIDOS_PROVEEDOR
ON (PEDIDOS_PROVEEDOR.ID = V_PED_PROV_ARTICULOS_AUX.ID_PEDIDO)
GROUP BY ID_PEDIDO,
         PEDIDOS_PROVEEDOR.ID_ALMACEN,
         ID_ARTICULO
HAVING SUM(CANTIDAD_PEDIDA) IS NOT NULL;


/*Agrupa los artículos de un mismo albarán (ya que en un albarán puede existir varias lineas con el mismo artículo).
  Para cada artículo de albarán le ponemos el pedido con el que esta asociado, la situacion y el almacén de donde salió.
  Se quitan todos los artículos que no tengamos en catálogo (ID_ARTICULO nulo, lineas de detalle libres)
  Se quitan también aquellos que no seán inventariables*/

CREATE VIEW V_ALB_CLI_DETALLES(
    ID_ALBARAN,
    ID_PEDIDO,
    SITUACION,
    ID_ALMACEN,
    ID_ARTICULO,
    CANTIDAD)
AS
SELECT  ALBARANES_CLIENTE_DETALLES.ID_ALBARAN,
        ALBARANES_CLIENTE.ID_PEDIDO,
        V_ALB_CLI_SITUACION.SITUACION,
        ALBARANES_CLIENTE.ID_ALMACEN,
        ALBARANES_CLIENTE_DETALLES.ID_ARTICULO,
        SUM(COALESCE(ALBARANES_CLIENTE_DETALLES.CANTIDAD, 0))

FROM ALBARANES_CLIENTE_DETALLES
LEFT JOIN ALBARANES_CLIENTE
ON (ALBARANES_CLIENTE_DETALLES.ID_ALBARAN = ALBARANES_CLIENTE.ID)
LEFT JOIN ARTICULOS
ON (ALBARANES_CLIENTE_DETALLES.ID_ARTICULO = ARTICULOS.ID)
LEFT JOIN V_ALB_CLI_SITUACION
ON (ALBARANES_CLIENTE_DETALLES.ID_ALBARAN = V_ALB_CLI_SITUACION.ID)

WHERE (ALBARANES_CLIENTE_DETALLES.ID_ARTICULO is not null)
AND   (ARTICULOS.INVENTARIABLE = 1)

group BY  ALBARANES_CLIENTE_DETALLES.ID_ALBARAN,
          ALBARANES_CLIENTE.ID_PEDIDO,
          V_ALB_CLI_SITUACION.SITUACION,
          ALBARANES_CLIENTE.ID_ALMACEN,
          ALBARANES_CLIENTE_DETALLES.ID_ARTICULO;



/*Agrupa los artículos de un mismo pedido (ya que en un pedido puede existir varias lineas con el mismo artículo).
  Se quitan todos los artículos que no tengamos en catálogo (ID_ARTICULO nulo, lineas de detalle libres)
  Se quitan también aquellos que no seán inventariables */

CREATE VIEW V_PED_CLI_DETALLES(
    ID_PEDIDO,
    ID_ARTICULO,
    CANTIDAD)
AS
SELECT  PEDIDOS_CLIENTE_DETALLES.ID_PEDIDO,
        PEDIDOS_CLIENTE_DETALLES.ID_ARTICULO,
        SUM(COALESCE(PEDIDOS_CLIENTE_DETALLES.CANTIDAD, 0)) AS CANTIDAD

FROM PEDIDOS_CLIENTE_DETALLES
LEFT JOIN ARTICULOS
ON (PEDIDOS_CLIENTE_DETALLES.ID_ARTICULO = ARTICULOS.ID)

WHERE (PEDIDOS_CLIENTE_DETALLES.ID_ARTICULO is not null)
AND   (ARTICULOS.INVENTARIABLE = 1)

GROUP BY  PEDIDOS_CLIENTE_DETALLES.ID_PEDIDO,
          PEDIDOS_CLIENTE_DETALLES.ID_ARTICULO;



/*Agrupa todos los artículos de un pedido por situacion (a partir de albaranes de cliente) de cada uno de los pedidos asociados en los albaranes existentes*/
/*No le ponemos el almacen donde salió o va a salir ya que un mismo pedido podría tener varios albaranes con distintos almacenes de origen*/
/*Aquellos articulos que no tengan ID_PEDIDO es porque el albarán al que pertenecen no tiene pedido asociado por ello no los tendremos en cuenta*/

CREATE VIEW V_PED_CLI_ART_SITUACION(
    ID_PEDIDO,
    SITUACION,
    ID_ARTICULO,
    CANTIDAD)
AS
SELECT ID_PEDIDO, SITUACION, ID_ARTICULO, SUM(CANTIDAD) AS CANTIDAD
FROM V_ALB_CLI_DETALLES
WHERE (ID_PEDIDO is not null)
GROUP BY ID_PEDIDO,
         SITUACION,
         ID_ARTICULO;


		 
/*Nos desglosa las cantidades del artículo*/		 

CREATE VIEW V_PED_CLI_ART_SITUACION_CANT(
    ID_PEDIDO,
    ID_ARTICULO,
    CANTIDAD_RESERVADA,
    CANTIDAD_ENVIADA,
    CANTIDAD_SERVIDA)
AS
SELECT ID_PEDIDO, ID_ARTICULO, 
COALESCE((CASE SITUACION when 'PENDIENTE' THEN CANTIDAD END), 0) as CANTIDAD_RESERVADA,
COALESCE((CASE SITUACION when 'ENVIADO' THEN CANTIDAD END), 0) as CANTIDAD_ENVIADA,
COALESCE((CASE SITUACION when 'SERVIDO' THEN CANTIDAD END), 0) as CANTIDAD_SERVIDA
FROM V_PED_CLI_ART_SITUACION;



/*Al igual que en la parte de proveedores, no hacemos LEFT JOIN entre V_PED_CLI_DETALLES y V_PED_CLI_ART_SITUACION_CANT,*/
/*porque por cada tupla de la primera repetiría la consulta de la segunda disparando tiempos, por ello teniendo las*/
/*dos vistas ejecutadas hacemos una union obteniendo todos los artículos del pedido, tanto recibidos como pendientes,*/
/*luego haremos la agrupación sobre este resultado*/

CREATE VIEW V_PED_CLI_ARTICULOS_AUX(
    ID_PEDIDO,
    ID_ARTICULO,
    CANTIDAD_PEDIDA,
    CANTIDAD_RESERVADA,
    CANTIDAD_ENVIADA,
    CANTIDAD_SERVIDA)
AS
SELECT
        V_PED_CLI_DETALLES.ID_PEDIDO,
        V_PED_CLI_DETALLES.ID_ARTICULO,
        V_PED_CLI_DETALLES.CANTIDAD AS CANTIDAD_PEDIDA,
        0 AS CANTIDAD_RESERVADA,
        0 AS CANTIDAD_ENVIADA,
        0 AS CANTIDAD_SERVIDA
FROM V_PED_CLI_DETALLES
UNION
SELECT
        V_PED_CLI_ART_SITUACION_CANT.ID_PEDIDO,
        V_PED_CLI_ART_SITUACION_CANT.ID_ARTICULO,
        NULL AS CANTIDAD_PEDIDA,
        V_PED_CLI_ART_SITUACION_CANT.CANTIDAD_RESERVADA AS CANTIDAD_RESERVADA,
        V_PED_CLI_ART_SITUACION_CANT.CANTIDAD_ENVIADA AS CANTIDAD_ENVIADA,
        V_PED_CLI_ART_SITUACION_CANT.CANTIDAD_SERVIDA AS CANTIDAD_SERVIDA
FROM V_PED_CLI_ART_SITUACION_CANT;


/*A partir de la vista anterior obtenemos el estado de cada uno de los artículos del pedido de cliente*/
/*Despreciamos aquellas tuplas cuya cantidad pedida sea null, porque son articulos añadidos en el albarán que*/
/*no están en su pedido correspondiente por lo tanto no los tendremos en cuenta para saber si los articulos del*/
/*pedido se han recibido todos. De todas formas no tiene mucho sentido este caso*/
/*Esta vista nos determina el estado de cada uno de los artículos del pedido de cliente*/

CREATE VIEW V_PED_CLI_ARTICULOS(
    ID_PEDIDO,
    ID_ARTICULO,
    CANTIDAD_PEDIDA,
    CANTIDAD_RESERVADA,
    CANTIDAD_ENVIADA,
    CANTIDAD_SERVIDA,
    CANTIDAD_PENDIENTE)
AS
SELECT ID_PEDIDO, 
       ID_ARTICULO,
       SUM(CANTIDAD_PEDIDA),
       SUM(CANTIDAD_RESERVADA) as CANTIDAD_RESERVADA,
       SUM(CANTIDAD_ENVIADA) as CANTIDAD_ENVIADA,
       SUM(CANTIDAD_SERVIDA) as CANTIDAD_SERVIDA,
      (SUM(CANTIDAD_PEDIDA) - (SUM(CANTIDAD_RESERVADA) +
                               SUM(CANTIDAD_ENVIADA) +
                               SUM(CANTIDAD_SERVIDA))) as CANTIDAD_PENDIENTE
FROM V_PED_CLI_ARTICULOS_AUX
GROUP BY ID_PEDIDO,
         ID_ARTICULO
HAVING SUM(CANTIDAD_PEDIDA) IS NOT NULL;

	
CREATE VIEW V_PED_CLI_SITUACION (
    ID_PEDIDO,
    SITUACION)
AS
SELECT ID_PEDIDO,
       CASE
           WHEN HAY_ALBARANES = 0 THEN 'PENDIENTE'
           WHEN (CANTIDAD_PENDIENTE <= 0) AND (CANTIDAD_PEDIDA <= CANTIDAD_SERVIDA) THEN 'SERVIDO'
           ELSE 'EN PROCESO'
       END AS SITUACION
FROM
(SELECT V_PED_CLI_ARTICULOS.ID_PEDIDO,
       SUM(V_PED_CLI_ARTICULOS.CANTIDAD_PEDIDA) AS CANTIDAD_PEDIDA,
       SUM(V_PED_CLI_ARTICULOS.CANTIDAD_RESERVADA) AS CANTIDAD_RESERVADA,
       SUM(V_PED_CLI_ARTICULOS.CANTIDAD_ENVIADA) AS CANTIDAD_ENVIADA,
       SUM(V_PED_CLI_ARTICULOS.CANTIDAD_SERVIDA) AS CANTIDAD_SERVIDA,
       SUM(V_PED_CLI_ARTICULOS.CANTIDAD_PENDIENTE) AS CANTIDAD_PENDIENTE,
       CASE
           WHEN COUNT(ALBARANES_CLIENTE.ID) > 0 THEN 1
           ELSE 0
       END AS HAY_ALBARANES
FROM ALBARANES_CLIENTE
     RIGHT OUTER JOIN V_PED_CLI_ARTICULOS ON (ALBARANES_CLIENTE.ID_PEDIDO =
     V_PED_CLI_ARTICULOS.ID_PEDIDO)
GROUP BY V_PED_CLI_ARTICULOS.ID_PEDIDO);	



CREATE VIEW V_PED_PROV_SITUACION(
  ID_PEDIDO,
  SITUACION)
AS
SELECT ID_PEDIDO,
       CASE
           WHEN (HAY_ALBARANES = 0) OR (CANTIDAD_RECIBIDA <= 0) THEN 'PENDIENTE'
           WHEN (CANTIDAD_PENDIENTE <= 0) THEN 'RECIBIDO'
           ELSE 'PARCIAL'
       END AS SITUACION
FROM
(SELECT V_PED_PROV_ARTICULOS.ID_PEDIDO,
       SUM(V_PED_PROV_ARTICULOS.CANTIDAD_PEDIDA) AS CANTIDAD_PEDIDA,
       SUM(V_PED_PROV_ARTICULOS.CANTIDAD_RECIBIDA) AS CANTIDAD_RECIBIDA,
       SUM(V_PED_PROV_ARTICULOS.CANTIDAD_PENDIENTE) AS CANTIDAD_PENDIENTE,
       CASE
           WHEN COUNT(ALBARANES_PROVEEDOR.ID) > 0 THEN 1
           ELSE 0
       END AS HAY_ALBARANES
FROM ALBARANES_PROVEEDOR
     RIGHT OUTER JOIN V_PED_PROV_ARTICULOS ON (ALBARANES_PROVEEDOR.ID_PEDIDO =
     V_PED_PROV_ARTICULOS.ID_PEDIDO)
GROUP BY V_PED_PROV_ARTICULOS.ID_PEDIDO)
;