mht_brerp10.adempiere

Parameters

Name	Type	Mode
order_id	numeric	IN
warehouse_id	numeric	IN

Definition

declare
    Estado							TEXT := 'N';
	QtyLinha						NUMERIC := 0;
	QtyLinhaComEstoque				NUMERIC := 0;
	QtyLinhaComEstoqueTotal			NUMERIC := 0;
	QtyLinhaComEstoqueNoReposicao	NUMERIC := 0;
begin

SELECT sum(
  				case
  					WHEN
  						bomqtyonhand(ol.m_product_id, warehouse_id, 0)
  						- (COALESCE((SELECT sum(iol.movementqty) AS sum
               				FROM m_inoutline iol
                		 	LEFT JOIN m_inout io ON io.m_inout_id = iol.m_inout_id
              				WHERE (io.docstatus <> ALL (ARRAY['RE'::bpchar, 'VO'::bpchar, 'CO'::bpchar, 'CL'::bpchar])) AND io.cof_emtratamento = 'Y' AND io.issotrx='Y' AND io.m_warehouse_id=warehouse_id AND iol.isinvoiced = 'N'::bpchar AND iol.m_product_id = ol.m_product_id), 0::NUMERIC))
              			- (COALESCE((SELECT sum((CASE WHEN o.cof_LiberarEntregaFutura='Y' AND oline.cof_EntregaParcial='Y' THEN oline.cof_QtdEntregaFutura ELSE oline.qtyentered END) - oline.qtydelivered) AS osum
               				FROM c_orderline oline
              				WHERE oline.c_order_id = order_id
              				AND oline.m_product_id = ol.m_product_id
              				AND oline.c_orderline_id != ol.c_orderline_id), 0::NUMERIC))
             			 <= 0 then 0
  					else 1
  				end
  			) AS qty
        INTO QtyLinhaComEstoque
        from C_OrderLine ol
        left join C_Order o on o.c_order_id = ol.c_order_id
        where o.c_order_id = order_id AND ol.qtyreserved > 0;

SELECT sum(
  				case
  					WHEN
  						bomqtyonhand(ol.m_product_id, warehouse_id, 0)
  						- (COALESCE((SELECT sum(iol.movementqty) AS sum
               				FROM m_inoutline iol
                		 	LEFT JOIN m_inout io ON io.m_inout_id = iol.m_inout_id
              				WHERE (io.docstatus <> ALL (ARRAY['RE'::bpchar, 'VO'::bpchar, 'CO'::bpchar, 'CL'::bpchar])) AND io.cof_emtratamento = 'Y' AND io.issotrx='Y' AND io.m_warehouse_id=warehouse_id AND iol.isinvoiced = 'N'::bpchar AND iol.m_product_id = ol.m_product_id), 0::NUMERIC))
              			- (COALESCE((SELECT sum((CASE WHEN o.cof_LiberarEntregaFutura='Y' AND oline.cof_EntregaParcial='Y' THEN oline.cof_QtdEntregaFutura ELSE oline.qtyentered END) - oline.qtydelivered) AS osum
               				FROM c_orderline oline
              				WHERE oline.c_order_id = order_id
              				AND oline.m_product_id = ol.m_product_id
              				AND oline.c_orderline_id != ol.c_orderline_id), 0::NUMERIC))
             			 < (CASE WHEN o.cof_LiberarEntregaFutura='Y' AND ol.cof_EntregaParcial='Y' THEN ol.cof_QtdEntregaFutura ELSE ol.qtyreserved END) then 0
  					else 1
  				end
  			) AS qty
        INTO QtyLinhaComEstoqueTotal
        from C_OrderLine ol
        left join C_Order o on o.c_order_id = ol.c_order_id
        where o.c_order_id = order_id AND ol.qtyreserved > 0;

SELECT count(*) AS qtyLinha INTO QtyLinha
        from C_OrderLine ol
        left join C_Order o on o.c_order_id = ol.c_order_id
        where o.c_order_id = order_id AND ol.qtyreserved > 0;

CREATE TEMP TABLE IdComEstoqueTotal AS (
	SELECT
  				case
  					WHEN
  						bomqtyonhand(ol.m_product_id, warehouse_id, 0)
  						- (COALESCE((SELECT sum(iol.movementqty) AS sum
               				FROM m_inoutline iol
                		 	LEFT JOIN m_inout io ON io.m_inout_id = iol.m_inout_id
              				WHERE (io.docstatus <> ALL (ARRAY['RE'::bpchar, 'VO'::bpchar, 'CO'::bpchar, 'CL'::bpchar])) AND io.cof_emtratamento = 'Y' AND io.issotrx='Y' AND io.m_warehouse_id=warehouse_id AND iol.isinvoiced = 'N'::bpchar AND iol.m_product_id = ol.m_product_id), 0::NUMERIC))
              			- (COALESCE((SELECT sum((CASE WHEN o.cof_LiberarEntregaFutura='Y' AND oline.cof_EntregaParcial='Y' THEN oline.cof_QtdEntregaFutura ELSE oline.qtyentered END) - oline.qtydelivered) AS osum
               				FROM c_orderline oline
              				WHERE oline.c_order_id = order_id
              				AND oline.m_product_id = ol.m_product_id
              				AND oline.c_orderline_id != ol.c_orderline_id), 0::NUMERIC))
             			 < ol.qtyreserved then 0
  					else ol.m_product_id
  				end
        from C_OrderLine ol
        left join C_Order o on o.c_order_id = ol.c_order_id
        where o.c_order_id = order_id AND ol.qtyreserved > 0
	);

SELECT COALESCE(sum(
  				case
  					WHEN
  						bomqtyonhandreposicao(ol.m_product_id, warehouse_id, 0)
  						- (COALESCE((SELECT sum(iol.movementqty) AS sum
               				FROM m_inoutline iol
                		 	LEFT JOIN m_inout io ON io.m_inout_id = iol.m_inout_id
              				WHERE (io.docstatus <> ALL (ARRAY['RE'::bpchar, 'VO'::bpchar, 'CO'::bpchar, 'CL'::bpchar])) AND io.cof_emtratamento = 'Y' AND io.issotrx='Y' AND io.m_warehouse_id=warehouse_id AND iol.isinvoiced = 'N'::bpchar AND iol.m_product_id = ol.m_product_id), 0::numeric))
             			 <= 0 then 0
  					else 1
  				end
  			), 0) AS qtyrepo
        INTO QtyLinhaComEstoqueNoReposicao
        from C_OrderLine ol
        left join C_Order o on o.c_order_id = ol.c_order_id
        where o.c_order_id = order_id AND ol.qtyreserved > 0 AND ol.m_product_id NOT IN (SELECT * FROM IdComEstoqueTotal);

DROP TABLE IdComEstoqueTotal;

if(QtyLinha = QtyLinhaComEstoqueTotal) then
		Estado = 'G';
	elsif(QtyLinhaComEstoque > 0 AND QtyLinhaComEstoqueNoReposicao = 0) then
		Estado = 'B';
	elsif(QtyLinhaComEstoqueNoReposicao > 0) then
		Estado = 'Y';
	else
		Estado = 'R';
	end if;

if(Estado is null) then
		Estado = 'N';
	end if;

RETURN Estado;
END

cof_estadoestoqueov(numeric, numeric)

Parameters

Definition