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

CREATE procedure [dbo].[USP_IF_OrderAbleQty_Q]
	@ProductNo	bigint,
	@SKUCode	nvarchar(20)
AS
/***********************************************************************
*
*      Program : USP_IF_OrderqbleQty_Q  -   주문가능 수량을 조회한다.
*
*
*      작성일 : 2009/02/01              
*      작성자 : (주)COMMERCEWARE
*      수정일 :                         
*      수정자 :
*	      수정 시 주의할 점 : 2011.11.04. Febsy 관련 dbo.USP_IF_OrderAbleQtyForFebsy_Q 에서 호출함.
*
*      2019-11-15 구성근 가용존만 체크하는 뷰로 변경
***********************************************************************/
BEGIN          
	/* @SKUCode 코드값이 있으면 우선적으로 조회한다. */
	IF LEN(@SKUCode) > 0
        SELECT X.SKU_CODE as SKUCode
	         , SUM(X.AQTY) - SUM(ABS(X.SLIP_CREATE_QTY)) - SUM(ABS(X.EOUT_CREATE_QTY)) - SUM(ABS(X.ALLOC_QTY)) as OrderableQty
	      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 
             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 X.SKU_CODE as SKUCode
             , SUM(X.AQTY) - SUM(ABS(X.SLIP_CREATE_QTY)) - SUM(ABS(X.EOUT_CREATE_QTY))- SUM(ABS(X.ALLOC_QTY)) as OrderableQty
	     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 
				 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