style24.doc/jsh77b/10.문서/02.업무분석참조/11.WMS/재고체크_SP/USP_IF_OrderAbleQtyWithOutput_Q.sql

/***********************************************************************
 *
 *      Program : USP_IF_OrderAbleQtyWithOutput_Q  -   주문가능 수량을 Output 으로 반환한다.
 *
 *
 *      작성일 : 2010/06/04              작성자 : 아이스타일이십사(주)
 *      수정일 :                         수정자 :
 *
 *
 * 2019-11-15 구성근 가용존만 체크하는 뷰로 변경
***********************************************************************/
CREATE procedure [dbo].[USP_IF_OrderAbleQtyWithOutput_Q]
	@ProductNo		Bigint
	, @SKUCode		Nvarchar(20)
	, @OrderAbleQty	Int Output
AS

BEGIN          
	/* @SKUCode 코드값이 있으면 우선적으로 조회한다. */
    IF LEN(@SKUCode) > 0
		SELECT
			@OrderAbleQty = (SUM(X.AQTY) - SUM(ABS(X.SLIP_CREATE_QTY)) - SUM(ABS(X.EOUT_CREATE_QTY))- SUM(ABS(X.ALLOC_QTY)))
		  FROM (   
			/* 창고 현재고 조회 변경 by gugu44 2019.11.06 */
			SELECT PRODUCT_NO       as product_no
			     , SKU_CODE         as sku_code 
			     , LOC_QTY	  	    as aqty
				 , 0                as slip_create_qty  
				 , 0                as cvs_qty  
				 , 0                as eout_create_qty  
				 , 0                as alloc_qty  
			  FROM istyle24_wms.dbo.VW_Stock_By_LocCode with(nolock) 
			 WHERE SKU_CODE = @SKUCode 
			 UNION ALL
			/* 출고 할당 수량 = 출하지시이후 출고확정되지 않은 상태의 수량 */
			SELECT S.PRODUCT_NO      as product_no
				 , S.SKU_CODE        as sku_code
				 , 0                 as aqty
				 , S.SLIP_CREATE_QTY as slip_create_qty
				 , S.CVS_QTY         as cvs_qty
				 , S.EOUT_CREATE_QTY as eout_create_qty
				 , 0                 as alloc_qty
		      from istyle24_wms.dbo.tlost06m S with (nolock)
			 WHERE S.WH_CODE    = '001'
			   AND S.SKU_CODE   = @SKUCode
			 UNION ALL
			/* 배송지시수량 = 배송지시후 출하지시 안된 상태의 수량 */
			SELECT A.PRODUCTNO       as product_no
				 , A.SKUCODE         as sku_code
				 , 0                 as aqty
				 , 0                 as slip_create_qty
				 , 0                 as cvs_qty
				 , 0                 as eout_create_qty
				 , ALLOCQTY          as alloc_qty
			  FROM istyle24_wmsif.dbo.TB_IF_OrderAllocStock A with (nolock)
			 WHERE A.SKUCODE    = @SKUCode
		) X
		GROUP BY X.SKU_CODE
    /* @SKUCode 코드값이 없으면 @ProductNo로 조회한다. */
	ELSE
		SELECT @OrderAbleQty = (SUM(X.AQTY) - SUM(ABS(X.SLIP_CREATE_QTY)) - SUM(ABS(X.EOUT_CREATE_QTY))- SUM(ABS(X.ALLOC_QTY)))
		FROM	(   
			/* 창고 현재고 조회 변경 by gugu44 2019.11.06 */
			SELECT PRODUCT_NO       as product_no
				 , SKU_CODE         as sku_code 
				 , LOC_QTY	  	    as aqty
				 , 0                as slip_create_qty  
				 , 0                as cvs_qty  
				 , 0                as eout_create_qty  
				 , 0                as alloc_qty  
			  FROM istyle24_wms.dbo.VW_Stock_By_LocCode with(nolock) 
			 WHERE PRODUCT_NO = @ProductNo
			 UNION ALL
			/* 출고 할당 수량 = 출하지시이후 출고확정되지 않은 상태의 수량 */
			SELECT S.PRODUCT_NO      as product_no
			     , S.SKU_CODE        as sku_code
				 , 0                 as aqty
				 , S.SLIP_CREATE_QTY as slip_create_qty
				 , S.CVS_QTY         as cvs_qty
				 , S.EOUT_CREATE_QTY as eout_create_qty
				 , 0                 as alloc_qty
			  FROM istyle24_wms.dbo.tlost06m s with (nolock)
			 WHERE S.WH_CODE      = '001'
			   AND S.PRODUCT_NO   = @ProductNo
			 UNION ALL
			/* 배송지시수량 = 배송지시후 출하지시 안된 상태의 수량 */
			SELECT A.PRODUCTNO       as product_no
				 , A.SKUCODE         as sku_code
				 , 0                 as aqty
				 , 0                 as slip_create_qty
				 , 0                 as cvs_qty
				 , 0                 as eout_create_qty
				 , ALLOCQTY          as alloc_qty
			  FROM istyle24_wmsif.dbo.TB_IF_OrderAllocStock a with (nolock)
			 WHERE A.PRODUCTNO    = @ProductNo
		) x
		GROUP BY X.SKU_CODE

END