第一个：SY_split_KitPO-CSDN博客

本文链接：https://blog.csdn.net/chengxu1020/article/details/3895310

USE [panda]
GO
/****** Object: StoredProcedure [dbo].[SY_split_KitPO] Script Date: 02/16/2009 14:19:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SY_split_KitPO]
@ComputerName varchar(50),
@TransactionType varchar(10)
AS

--1. find kitPO from sy_order_file join with pricemaster and kitmaster to split kitPO into partsPO
---Create tmp table
CREATE table #tmpKitPO
(
Line_No int,
KitPONO varchar(20),
KitNo varchar(10),
KitQTY int,
KitID varchar(20),
PartPONo varchar(20),
PartNo int,
PartQTY int,
FOBDate datetime,
MaxLine_No int,
ID INT IDENTITY(1,1) NOT NULL,
)

--step 1.1 insert kitPO into #tmpKitPO
INSERT INTO #tmpKitPO
  SELECT SF.Line_No, SF.PONo AS KitPONO, SF.PartNo AS KitNo, SF.QTY AS KitQTY,
        PM.KitID, SF.PONo AS PartPONo, KM.PartNo, SF.QTY * KM.QTY AS partQTY,
        SF.FOBDate, MAX(SF.Line_No) AS MaxLine_No
  FROM SY_ORDER_FILE AS SF INNER JOIN
        PriceMaster AS PM ON SF.PartNo = PM.PartNo AND SF.Customer = PM.Seller AND
        CAST(SF.FOBDate AS Datetime) BETWEEN CAST(PM.CutIn_From AS Datetime) AND CAST(PM.CutIn_To AS Datetime) INNER JOIN
        KitMaster AS KM ON PM.KitID = KM.KitID
  WHERE (SF.ComputerName = @ComputerName) AND
        (SF.Transactiontype = @TransactionType) AND (PM.KitID IS NOT NULL)
  GROUP BY SF.Line_No, SF.PONo, SF.QTY, PM.KitID, KM.PartNo, SF.FOBDate, SF.PartNo,
        SF.QTY * KM.QTY

--step 1.2 Amend line_No, PartPONo
update #tmpKitPO set Line_No = MaxLine_No + ID

update #tmpKitPO
  set PartPONO = PartPONo + '-' + convert(varchar(2),Line_No - (select min(line_No) from #tmpKitPO where KitPONo = TP.KitPONo) + 1)
FROM #tmpKitPO TP


--step 1.3. use tmptbl and match with SF_order_file to get the kitPO and join with kitmaster using kitid to split kit po into part po.
INSERT INTO SY_ORDER_FILE
  SELECT #tmpKitPO.Line_No, #tmpKitPO.PartPONo AS PONO, SY_ORDER_FILE.Indicator,
        #tmpKitPO.PartNo, SY_ORDER_FILE.Vendor, SY_ORDER_FILE.VendorSuffix,
        SY_ORDER_FILE.PortCode, SY_ORDER_FILE.XJCode, SY_ORDER_FILE.IssueDate,
        #tmpKitPO.PartQTY AS QTY, SY_ORDER_FILE.Box_QTY, SY_ORDER_FILE.MOQ,
        SY_ORDER_FILE.Fluct_qty, SY_ORDER_FILE.Firmness, SY_ORDER_FILE.Category,
        SY_ORDER_FILE.FOBDate, SY_ORDER_FILE.Originator,
        SY_ORDER_FILE.CancelDate, SY_ORDER_FILE.ShipMode, SY_ORDER_FILE.Week,
        SY_ORDER_FILE.Customer, SY_ORDER_FILE.V_Currency,
        SY_ORDER_FILE.V_Tradeterms, SY_ORDER_FILE.V_Country,
        SY_ORDER_FILE.V_Name, SY_ORDER_FILE.V_Address,
        SY_ORDER_FILE.V_PaymentTerms, SY_ORDER_FILE.S_Point,
        SY_ORDER_FILE.B_Point, SY_ORDER_FILE.B_Date, SY_ORDER_FILE.B_UnitPrice,
        SY_ORDER_FILE.S_UnitPrice, SY_ORDER_FILE.B_Amount,
        SY_ORDER_FILE.S_Amount, SY_ORDER_FILE.M_ShipMode,
        SY_ORDER_FILE.LeadTime, SY_ORDER_FILE.Description,
        SY_ORDER_FILE.QTY_Unit, SY_ORDER_FILE.ErrorCode, SY_ORDER_FILE.Error,
        SY_ORDER_FILE.c_FLG, SY_ORDER_FILE.AcceptDate,
        SY_ORDER_FILE.Ign_Fluctuation, SY_ORDER_FILE.Ign_BoxQty,
        SY_ORDER_FILE.Ign_MOQ, SY_ORDER_FILE.ComputerName,
        SY_ORDER_FILE.Transactiontype, SY_ORDER_FILE.CustomerPONo,
        SY_ORDER_FILE.ETADate, SY_ORDER_FILE.Ign_GetCustomer,
        SY_ORDER_FILE.Filter_Customer, SY_ORDER_FILE.Remarks,
        SY_ORDER_FILE.Filter_NotCustomer, SY_ORDER_FILE.errmsg,
        SY_ORDER_FILE.rowguid, SY_ORDER_FILE.Comment, #tmpKitPO.KitPONO,
        #tmpKitPO.KitNo, #tmpKitPO.KitQTY, #tmpKitPO.KitID
  FROM #tmpKitPO INNER JOIN
        SY_ORDER_FILE ON #tmpKitPO.MaxLine_No = SY_ORDER_FILE.Line_No
  WHERE (SY_ORDER_FILE.ComputerName = @ComputerName) AND
     (SY_ORDER_FILE.Transactiontype = @TransactionType)

--Delete KitPONO in SY_Order_File
DELETE SY_ORDER_FILE
  FROM SY_ORDER_FILE SY
  INNER JOIN #tmpKitPO TP
  ON SY.PONo COLLATE DATABASE_DEFAULT = TP.KitPONO COLLATE DATABASE_DEFAULT
  WHERE SY.computername COLLATE DATABASE_DEFAULT =@ComputerName COLLATE DATABASE_DEFAULT and SY.transactiontype COLLATE DATABASE_DEFAULT = @TransactionType COLLATE DATABASE_DEFAULT

---delete tmpTbl
DROP TABLE #tmpKitPO