style24.doc/jsh77b/10.문서/02.업무분석참조/11.WMS/SP20201202/p_dailyWarehousingInfo_inTB_IF_IncomeLot.sql

  
  
CREATE procedure [dbo].[p_dailyWarehousingInfo_inTB_IF_IncomeLot]  
(@arg_date varchar(8)  
, @arg_insertno  int  
, @rtn_cnt int output)  
as  
  
/******************************변수값 SETTING****************************/  
declare @exist_cnt int  
  
set @exist_cnt = 0  
set @rtn_cnt = 0  
/*************************************************************************/  
  
  
/* 수불일을 Argument로 넘어오지 않을시 현재일 -1일 Date를 마감처리한다 */  
if @arg_date = '' or @arg_date is null  
    begin  
        select @arg_date = convert(char(8), getdate() -1, 112)  
    end  
  
/* 처리자가 Argument로 넘어오지 않을시 SYSTEM 계정으로 처리한다 */  
if @arg_insertno = 0 or @arg_insertno is null  
    begin  
        set @arg_insertno = 1  
    end  
  
-- select @arg_date, @arg_insertno  
-- return  
  
/* 기존에 동일날짜에 대한 수불Data가 있는 경우 삭제처리한다. */  
select @exist_cnt = count(*)    
  from istyle24_wmsif.dbo.TB_IF_IncomeLotItem with (nolock)   
 where DateIncome = istyle24_wms.dbo.f_chartodate(@arg_date)  
  
if (@exist_cnt > 0)   
    begin  
        delete from istyle24_wmsif.dbo.TB_IF_IncomeLotItem with (rowlock)   
         where DateIncome = istyle24_wms.dbo.f_chartodate(@arg_date)  
                
        delete from istyle24_wmsif.dbo.TB_IF_IncomeLot with (rowlock)  
         where DateIncome = istyle24_wms.dbo.f_chartodate(@arg_date)  
                          
  
     end  
  
BEGIN TRY  
  
/* 입고정보 M */  
insert istyle24_wmsif.dbo.TB_IF_IncomeLot with (rowlock)  
        (  
         LotNo  
        ,PurchaseNo  
        ,ProviderNo  
        ,ProviderName  
        ,BrandNo  
        ,BrandName  
        ,DateIncome  
        )  
  select distinct  
         a.in_no  
        ,b.pur_no  
        ,a.entp_no  
        ,a.entp_nm  
        ,a.brand_no  
        ,c.brandname  
        ,istyle24_wms.dbo.f_chartodate(@arg_date)  
  from istyle24_wms.dbo.tloin01m a with (nolock)  
       join istyle24_wms.dbo.tloin01d b with (nolock)  
            on (a.in_no = b.in_no)  
       join istyle24_wmsif.dbo.tb_if_brand c with(nolock)  
            on (a.brand_no = c.BrandNo)  
  where a.insert_date    >=    convert(datetime, @arg_date, 112)  
    and a.insert_date    <     convert(datetime, @arg_date, 112) + 1   
          
/* 입고정보 D */  
insert istyle24_wmsif.dbo.TB_IF_IncomeLotItem with (rowlock)  
        (  
         LotNo  
        ,WMSItemNo  
        ,DateIncome  
        ,ProductNo  
        ,ProductCode  
        ,ProductName  
        ,SKUCode  
        ,NormalQty  
        ,BrokenQty  
        ,TotalQty  
        )  
select  b.in_no  
       ,b.in_seq  
       ,istyle24_wms.dbo.f_chartodate(@arg_date)  
       ,b.product_no  
       ,c.productcode  
       ,c.productname  
       ,b.sku_code  
       ,isnull(b.in_qty_old,0) + isnull(b.sample_qty_old,0)  
       ,isnull(b.in_bqty_old,0)  
       ,isnull(b.in_qty_old,0) + isnull(b.sample_qty_old,0) + isnull(b.in_bqty_old,0)  
  from istyle24_wms.dbo.tloin01d b with (nolock)  
       join istyle24_wmsif.dbo.tb_if_product c with(nolock)  
            on (b.product_no = c.productno)  
  where b.insert_date    >=    convert(datetime, @arg_date, 112)  
    and b.insert_date    <     convert(datetime, @arg_date, 112) + 1  
  
  
/* 입고 수정 D */  
insert istyle24_wmsif.dbo.TB_IF_IncomeLotItem with (rowlock)  
        (  
         LotNo  
        ,WMSItemNo  
        ,DateIncome  
        ,ProductNo  
        ,ProductCode  
        ,ProductName  
        ,SKUCode  
        ,NormalQty  
        ,BrokenQty  
        ,TotalQty  
        )  
select  b.in_no  
       ,b.in_seq  
       ,istyle24_wms.dbo.f_chartodate(@arg_date)  
       ,b.product_no  
       ,c.productcode  
       ,c.productname  
       ,b.sku_code  
       ,isnull(b.modify_aqty,0)  
       ,isnull(b.modify_bqty,0)  
       ,isnull(b.modify_aqty,0) + isnull(b.modify_bqty,0)  
  from istyle24_wms.dbo.tloin02d b with (nolock)  
       join istyle24_wmsif.dbo.tb_if_product c with(nolock)  
            on (b.product_no = c.productno)  
       join istyle24_wms.dbo.tloin01m a with (nolock)  
            on (a.in_no = b.in_no  
                and a.insert_date    >=    convert(datetime, '20090426', 112) )  
 where b.insert_date    >=    convert(datetime, @arg_date, 112)  
   and b.insert_date    <     convert(datetime, @arg_date, 112) + 1  
     
 END TRY  
   
 BEGIN CATCH  
   
 END CATCH