USE [BELSFA]
GO
/****** Object:  StoredProcedure [dbo].[sp_ACU_importOrder]    Script Date: 5/20/2019 2:53:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_ACU_importOrder] --'SM000001'
    @UserName NVARCHAR(50)
AS

    BEGIN

/******************************
*  Initialize Variables
*******************************/
        -- Lay companyID
        DECLARE @CompanyID INT
        SELECT  @CompanyID = CompanyID
        FROM    dbo.DMSAimStagingConfig


  /****************************************************************************
        * Step 1
        * --Lay danh sach nhung don hang chua import vao acu
        ****************************************************************************/

  --Lay danh sach nhung don hang chua import vao acu

            SELECT  *
            INTO    #Orderlist
     --FROM aimOrderHeader WHERE  DistributorCode = @companyID AND SlsPerID = @SlsPerID
            FROM    dbo.DMSAimOrderHeader
            WHERE   DATEDIFF(dd, OrderDate, GETDATE()) <= 2
                    AND UserName = @UserName
                    AND OrderCode NOT IN (
                    SELECT  OrderNbr
                    FROM    BELDMS.dbo.DMSPDAOrder WITH ( NOLOCK )
                    WHERE   CompanyID = @CompanyID
                            AND SalesmanID = @UserName
                            AND DATEDIFF(dd, OrderDate, GETDATE()) <= 2 )
                    AND OrderType IN ( 0, 2, 4 )
      --Lay detail

            SELECT  *
            INTO    #OrderDetail
            FROM    DMSAimOrderDetail
            WHERE   UserName = @UserName
                    AND OrderCode IN ( SELECT   OrderCode
                                       FROM     #Orderlist )
      -- Discount
            SELECT  *
            INTO    #OrderDiscount
            FROM    DMSAimOrderDiscountDetail
            WHERE   UserName = @UserName
                    AND OrderCode IN ( SELECT   OrderCode
                                       FROM     #Orderlist )
    -- Loc bo nhung don hang khong có detail
            DELETE  h
            FROM    #Orderlist h
                    LEFT JOIN #OrderDetail d ON h.OrderCode = d.OrderCode
                                                AND h.UserName = d.UserName
            WHERE   d.OrderCode IS NULL
    -- Loc bo nhung don hang co detail, co KM nhung ko co trong bang KM
            DELETE  h
            FROM    #Orderlist h
                    INNER JOIN #OrderDetail d ON h.OrderCode = d.OrderCode
                                                 AND h.UserName = d.UserName
                                                 AND d.AutoOrManual = 'A'
                                                 AND d.PromotionID <> ''
                    LEFT JOIN #OrderDiscount p ON d.OrderCode = p.OrderCode
                                                  AND d.UserName = p.UserName
                                                  AND d.PromotionID = p.PromotionID
            WHERE   p.OrderCode IS NULL
/****************************************************************************
        * Step 2
        * Insert header
        ****************************************************************************/

            SELECT  @CompanyID CompanyID, -- CompanyID - int
                    b.BranchID , -- BranchID - int
                    CASE WHEN l.OrderType = 2 THEN 'SO'
                            WHEN l.OrderType = 4 THEN 'EO'
                            ELSE 'IN'
                    END OrderType, -- OrderType - nvarchar(2)
                    l.OrderCode , -- OrderNbr - nvarchar(20)
                    l.OrderDate , -- OrderDate - datetime
                    l.Note , -- Description - nvarchar(255) dang thieu cot nay tren pda
                    l.TotalLine , -- LineCntr - int
                    l.CustomerCD , -- CustomerID - nvarchar(20)
                    c.LocationID , -- CustomerLocation - int
                    REPLACE(l.RouteCode, '_T', '') RouteCD, -- Route - nvarchar(20)
                    l.UserName , -- SalesmanID - nvarchar(20)
                    l.TotalQuantity , -- TotalQuantity - int
                    l.TotalSKU , -- TotalSKUs - int
                    l.OrderAmount , -- OrderAmount - decimal
                    l.LineDiscount , -- LineDiscount - decimal
                    l.DocDiscountAmt , -- DocumentDiscount - decimal
                    l.OrderTotal , -- OrderTotal - decimal
                    'U' Status, -- Status - char(1)
                    'D' PaymentStatus, -- PaymentStatus - char(10
                    l.ValidDate, -- ValidDate - bit
                    l.ValidDistance,  -- ValidDistance - bit
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' CreatedByID, -- CreatedByID - uniqueidentifier
                    'PDA' CreatedByScreenID, -- CreatedByScreenID - char(8)
                    GETDATE() CreatedDateTime, -- CreatedDateTime - datetime
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' LastModifiedByID, -- LastModifiedByID - uniqueidentifier
                    'PDA' LastModifiedByScreenID, -- LastModifiedByScreenID - char(8)
                    GETDATE() LastModifiedDateTime -- LastModifiedDateTime - datetime
            INTO    #tmpHeader
            FROM    #Orderlist l
                    INNER JOIN BELDMS.dbo.Branch b ON b.BranchCD = l.DistributorCD
                                                        AND b.CompanyID = @CompanyID
                    INNER JOIN dbo.DMSAllCustomer c ON c.CustomerCD = l.CustomerCD
                                                        AND c.LocationCD = l.LocationCD
                    INNER JOIN dbo.DMSAimCustomerVisitedTime v ON v.CustomerCD = l.CustomerCD
                                                        AND v.LocationCD = l.LocationCD
                                                        AND v.VisitID = l.VisitID
			WHERE b.Active = 1;

            -- Insert header cho đơn của KH mới
            INSERT INTO #tmpHeader
            (
                CompanyID,
                BranchID,
                OrderType,
                OrderCode,
                OrderDate,
                Note,
                TotalLine,
                CustomerCD,
                LocationID,
                RouteCD,
                UserName,
                TotalQuantity,
                TotalSKU,
                OrderAmount,
                LineDiscount,
                DocDiscountAmt,
                OrderTotal,
                Status,
                PaymentStatus,
                ValidDate,
                ValidDistance,
                CreatedByID,
                CreatedByScreenID,
                CreatedDateTime,
                LastModifiedByID,
                LastModifiedByScreenID,
                LastModifiedDateTime
            )
            SELECT  @CompanyID , -- CompanyID - int
                    b.BranchID , -- BranchID - int
                    CASE WHEN l.OrderType = 2 THEN 'SO'
                            WHEN l.OrderType = 4 THEN 'EO'
                            ELSE 'IN'
                    END , -- OrderType - nvarchar(2)
                    l.OrderCode , -- OrderNbr - nvarchar(20)
                    l.OrderDate , -- OrderDate - datetime
                    l.Note , -- Description - nvarchar(255) dang thieu cot nay tren pda
                    l.TotalLine , -- LineCntr - int
                    l.CustomerCD , -- CustomerID - nvarchar(20)
                    0 , -- CustomerLocation - int
                    REPLACE(l.RouteCode, '_T', '') , -- Route - nvarchar(20)
                    l.UserName , -- SalesmanID - nvarchar(20)
                    l.TotalQuantity , -- TotalQuantity - int
                    l.TotalSKU , -- TotalSKUs - int
                    l.OrderAmount , -- OrderAmount - decimal
                    l.LineDiscount , -- LineDiscount - decimal
                    l.DocDiscountAmt , -- DocumentDiscount - decimal
                    l.OrderTotal , -- OrderTotal - decimal
                    'U' , -- Status - char(1)
                    'D' , -- PaymentStatus - char(10
                    0 , -- ValidDate - bit
                    0 ,  -- ValidDistance - bit
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' , -- CreatedByID - uniqueidentifier
                    'PDA' , -- CreatedByScreenID - char(8)
                    GETDATE() , -- CreatedDateTime - datetime
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' , -- LastModifiedByID - uniqueidentifier
                    'PDA' , -- LastModifiedByScreenID - char(8)
                    GETDATE()
            FROM    #Orderlist l
                    INNER JOIN BELDMS.dbo.Branch b ON b.BranchCD = l.DistributorCD
                                                        AND b.CompanyID = @CompanyID
            WHERE b.Active = 1 AND NOT EXISTS (SELECT * FROM dbo.DMSAllCustomer c WHERE c.CustomerCD = l.CustomerCD)



/****************************************************************************
        * Step 3
        * Insert detail
        ****************************************************************************/

            SELECT  @CompanyID CompanyID, -- CompanyID - int
                    b.BranchID , -- BranchID - int
                    CASE WHEN l.OrderType = 2 THEN 'SO'
                            WHEN l.OrderType = 4 THEN 'EO'
                            ELSE 'IN'
                    END OrderType, -- OrderType - nvarchar(2) =
                    l.OrderCode , -- OrderNbr - nvarchar(20)
                    ROW_NUMBER() OVER ( ORDER BY l.OrderCode ) LineNbr, -- LineNbr - int
                    i.InventoryID , -- InventoryID - int
                    N'' Description, -- Description - nvarchar(255)
                    CASE WHEN m.SiteID IS NULL THEN c.SiteID
                            ELSE m.SiteID
                    END SiteID, -- SiteID - int
                    0 LocationID, -- LocationID - int
                    d.FreeItem , -- IsFree - bit
                    i.UnitPerCase , -- UOM - nvarchar(10)
                    d.UnitPrice , -- SalesPrice - decimal
                    d.SuggestQty , -- SuggestQty - decimal
                    d.OrderQty , -- OrderQty - decimal
                    d.DeliveryQty , -- DeliveryQty - decimal
                    d.ShippedQty , -- ShippedQty - decimal
                    CASE WHEN d.AutoOrManual = 'M' THEN 1
                            ELSE 0
                    END ManualDiscount, -- ManualDiscount - bit
                    d.AllocationID , -- AllocationID - int
                    p.PromotionID , -- PromotionID - int
                    p2.PromotionID ManualPromotionID, -- ManualPromotionID - int
                    d.IncentiveID , -- IncentiveID - int
                    d.LineBeforeDiscount , -- LineBeforeDiscount - decimal
                    d.DiscountAmt , -- DiscountAmt - decimal
                    d.LineAmt , -- LineAmt - decimal
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' CreatedByID, -- CreatedByID - uniqueidentifier
                    'PDA' CreatedByScreenID, -- CreatedByScreenID - char(8)
                    GETDATE() CreatedDateTime, -- CreatedDateTime - datetime
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' LastModifiedByID, -- LastModifiedByID - uniqueidentifier
                    'PDA' LastModifiedByScreenID, -- LastModifiedByScreenID - char(8)
                    GETDATE() LastModifiedDateTime,  -- LastModifiedDateTime - datetime
                    d.IsPosm -- IsPosm - int
            INTO    #tmpDetail
            FROM    #OrderDetail d
                    INNER JOIN #Orderlist l ON l.OrderCode = d.OrderCode
                                                AND l.UserName = d.UserName
                    INNER JOIN BELDMS.dbo.Branch b ON b.BranchCD = l.DistributorCD
                                                        AND b.CompanyID = @CompanyID
                    INNER JOIN dbo.DMSAimInventoryList i ON d.InventoryCD = i.InventoryCD
                    INNER JOIN dbo.DMSAimCustomer c ON c.CustomerCD = l.CustomerCD
                                                        AND c.LocationCD = l.LocationCD
                                                        AND c.UserName = l.UserName
                    INNER JOIN dbo.DMSAllCustomer cu ON c.CustomerCD = cu.CustomerCD
                                                        AND cu.LocationCD = c.LocationCD
                    LEFT  JOIN BELDMS.dbo.DMSPRODiscount p ON d.PromotionID = p.PromotionCD
                                                        AND p.CompanyID = @CompanyID
                    LEFT  JOIN BELDMS.dbo.DMSPRODiscount p2 ON d.ManualPromotionID = p2.PromotionCD
                                                        AND p2.CompanyID = @CompanyID
                    LEFT JOIN BELDMS.dbo.DMSCustomerDeliveryMapping m ON cu.BaccountID = m.CustomerID
                                                        AND cu.LocationID = m.CustomerLocationID
                                                        AND m.CompanyID = @CompanyID
                                                        AND b.BranchID = m.DistributorID
			WHERE b.Active = 1
            ORDER BY d.OrderCode

            -- Insert detail cho đơn của KH mới
            INSERT INTO #tmpDetail
            (
                CompanyID,
                BranchID,
                OrderType,
                OrderCode,
                LineNbr,
                InventoryID,
                Description,
                SiteID,
                LocationID,
                FreeItem,
                UnitPerCase,
                UnitPrice,
                SuggestQty,
                OrderQty,
                DeliveryQty,
                ShippedQty,
                ManualDiscount,
                AllocationID,
                PromotionID,
                ManualPromotionID,
                IncentiveID,
                LineBeforeDiscount,
                DiscountAmt,
                LineAmt,
                CreatedByID,
                CreatedByScreenID,
                CreatedDateTime,
                LastModifiedByID,
                LastModifiedByScreenID,
                LastModifiedDateTime,
                IsPosm
            )
            SELECT  @CompanyID , -- CompanyID - int
                    b.BranchID , -- BranchID - int
                    CASE WHEN l.OrderType = 2 THEN 'SO'
                            WHEN l.OrderType = 4 THEN 'EO'
                            ELSE 'IN'
                    END , -- OrderType - nvarchar(2) =
                    l.OrderCode , -- OrderNbr - nvarchar(20)
                    ROW_NUMBER() OVER ( ORDER BY l.OrderCode ) , -- LineNbr - int
                    i.InventoryID , -- InventoryID - int
                    N'' , -- Description - nvarchar(255)
                    s.SiteID , -- SiteID - int
                    s.ReceiptLocationID , -- LocationID - int
                    d.FreeItem , -- IsFree - bit
                    i.UnitPerCase , -- UOM - nvarchar(10)
                    d.UnitPrice , -- SalesPrice - decimal
                    d.SuggestQty , -- SuggestQty - decimal
                    d.OrderQty , -- OrderQty - decimal
                    d.DeliveryQty , -- DeliveryQty - decimal
                    d.ShippedQty , -- ShippedQty - decimal
                    CASE WHEN d.AutoOrManual = 'M' THEN 1
                            ELSE 0
                    END , -- ManualDiscount - bit
                    d.AllocationID , -- AllocationID - int
                    p.PromotionID , -- PromotionID - int
                    p2.PromotionID , -- ManualPromotionID - int
                    d.IncentiveID , -- IncentiveID - int
                    d.LineBeforeDiscount , -- LineBeforeDiscount - decimal
                    d.DiscountAmt , -- DiscountAmt - decimal
                    d.LineAmt , -- LineAmt - decimal
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' , -- CreatedByID - uniqueidentifier
                    'PDA' , -- CreatedByScreenID - char(8)
                    GETDATE() , -- CreatedDateTime - datetime
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' , -- LastModifiedByID - uniqueidentifier
                    'PDA' , -- LastModifiedByScreenID - char(8)
                    GETDATE() ,  -- LastModifiedDateTime - datetime
                    d.IsPosm
            FROM    #OrderDetail d
                    INNER JOIN #Orderlist l ON l.OrderCode = d.OrderCode
                                                AND l.UserName = d.UserName
                    INNER JOIN BELDMS.dbo.Branch b ON b.BranchCD = l.DistributorCD
                                                        AND b.CompanyID = @CompanyID
                    INNER JOIN
                    (
                        SELECT BranchID,
                               SiteID,
                               ReceiptLocationID,
                               UsrIsDefault,
                               RowNumber = ROW_NUMBER() OVER (PARTITION BY CompanyID,
                                                                           BranchID,
                                                                           SiteID,
                                                                           ISNULL(UsrIsDefault, 0)
                                                              ORDER BY CompanyID,
                                                                       BranchID,
                                                                       SiteID,
                                                                       ISNULL(UsrIsDefault, 0) DESC
                                                             )
                        FROM BELDMS.dbo.INSite
                        WHERE CompanyID = @CompanyID AND Active = 1
                     ) s ON s.BranchID = b.BranchID
                            AND (s.UsrIsDefault = 1 OR s.RowNumber = 1)
                    INNER JOIN dbo.DMSAimInventoryList i ON d.InventoryCD = i.InventoryCD
                    LEFT  JOIN BELDMS.dbo.DMSPRODiscount p ON d.PromotionID = p.PromotionCD
                                                        AND p.CompanyID = @CompanyID
                    LEFT  JOIN BELDMS.dbo.DMSPRODiscount p2 ON d.ManualPromotionID = p2.PromotionCD
                                                        AND p2.CompanyID = @CompanyID
            WHERE NOT EXISTS (SELECT * FROM dbo.DMSAllCustomer c WHERE c.CustomerCD = l.CustomerCD)
            ORDER BY d.OrderCode


            -- Insert lô
            SELECT  @CompanyID CompanyID, -- CompanyID - int
                    ol.BranchID , -- BranchID - int
                    ol.OrderType , -- OrderType - nvarchar(2)
                    ol.OrderCode , -- OrderNbr - nvarchar(20)
                    ol.LineNbr , -- LineNbr - int
                    i.InventoryID , -- InventoryID - int
                    ol.FreeItem , -- IsFree - bit
                    lot.Quantity , -- OrderQty - decimal
                    lot.AllocationID , -- AllocationID - int
                    ol.PromotionID , -- PromotionID - int
                    ol.ManualPromotionID , -- ManualPromotionID - int
                    lot.IncentiveID , -- IncentiveID - nvarchar(20)
                    lot.Lot , -- LotSerialNbr - nvarchar(100)
                    lot.ExpireDate -- ExpireDate - datetime
            INTO    #tmpLot
            FROM    dbo.DMSAimOrderInventoryLot lot
                    INNER JOIN dbo.DMSAimInventoryList i ON lot.InventoryCD = i.InventoryCD
                    LEFT  JOIN BELDMS.dbo.DMSPRODiscount p ON lot.PromotionID = p.PromotionCD
                                                        AND p.CompanyID = @CompanyID
                    LEFT  JOIN BELDMS.dbo.DMSPRODiscount p2 ON lot.ManualPromotionID = p2.PromotionCD
                                                        AND p2.CompanyID = @CompanyID
                    INNER JOIN #tmpDetail ol ON lot.OrderCode = ol.OrderCode
                                                        AND i.InventoryID = ol.InventoryID
                                                        AND lot.FreeItem = ol.FreeItem
                                                        AND ( p.PromotionID = ol.PromotionID
                                                        OR ( p.PromotionID IS NULL
                                                        AND ol.PromotionID IS NULL
                                                        )
                                                        )
                                                        AND lot.AllocationID = ol.AllocationID
                                                        AND ( p2.PromotionID = ol.ManualPromotionID
                                                        OR ( p2.PromotionID IS NULL
                                                        AND ol.ManualPromotionID IS NULL
                                                        )
                                                        )
                                                        AND lot.IncentiveID = ol.IncentiveID

/****************************************************************************
        * Step 4
        * Insert promotion
        ****************************************************************************/

            SELECT  @CompanyID CompanyID, -- CompanyID - int
                    b.BranchID , -- BranchID - int
                    l.OrderCode , -- OrderNbr - varchar(15)
                    CASE WHEN l.OrderType = 2 THEN 'SO'
                            WHEN l.OrderType = 4 THEN 'EO'
                            ELSE 'IN'
                    END OrderType, -- OrderType - varchar(15)
                    p.PromotionID , -- PromotionID - int
                    di.Type , -- Type - char(1)
                    i.InventoryID , -- InventoryID - int
                    ROW_NUMBER() OVER ( ORDER BY l.OrderCode ) LineNbr, -- LineNbr - int
                    di.OrderBreakQty , -- OrderBreakByQty - decimal
                    CAST(CAST(di.OrderBreakAmt AS FLOAT) AS DECIMAL(18,
                                                        2)) OrderBreakByAmt, -- OrderBreakByAmt - decimal
                    di.OrderFreeQty , -- OrderFreeQty - decimal
                    CAST(CAST(di.OrderFreeAmt AS FLOAT) AS DECIMAL(18,
                                                        2)) OrderFreeAmt, -- OrderFreeAmt - decimal
                    CAST(CAST(di.OrderContributeFreeAmt AS FLOAT) AS DECIMAL(18,
                                                        2)) OrderContributeFreeAmt, -- OrderContributeFreeAmt - decimal
                    0 ShipmentBreakQty, -- ShipmentBreakQty - decimal
                    0 ShipmentFreeQty, -- ShipmentFreeQty - decimal
                    0 InvoiceBreakQty, -- InvoiceBreakQty - decimal
                    0 InvoiceFreeQty, -- InvoiceFreeQty - decimal
                    0 InvoiceContributeFreeAmt, -- InvoiceContributeFreeAmt - decimal
                    di.BreakDownLevelID
            INTO    #tmpPromo
            FROM    #OrderDiscount di
                    INNER JOIN #Orderlist l ON l.DistributorCD = di.DistributorCD
                                                AND l.OrderCode = di.OrderCode
                                                AND l.UserName = di.UserName
                    INNER JOIN BELDMS.dbo.Branch b ON b.BranchCD = l.DistributorCD
                                                        AND b.CompanyID = @CompanyID
                    INNER JOIN dbo.DMSAimInventoryList i ON i.InventoryCD = di.InventoryCD
                    LEFT  JOIN BELDMS.dbo.DMSPRODiscount p ON di.PromotionID = p.PromotionCD
                                                        AND p.CompanyID = @CompanyID
			WHERE b.Active = 1

 /****************************************************************************
        * Step 5
        * Insert Incentive detail
        ****************************************************************************/

            SELECT  @CompanyID AS CompanyID,         -- CompanyID - int
                    d.IncentiveLineNbr,         -- IncentiveLineNbr - int
                    d.IncentiveType,        -- IncentiveType - char(1)
                    b.BranchID,         -- BranchID - int
                    o.OrderCode,        -- OrderNbr - varchar(20)
                    o.OrderType,        -- OrderType - varchar(15)
                    ed.ProgramID,         -- ProgramID - int
                    ISNULL(il.InventoryID, 0) AS InventoryID,         -- InventoryID - int
                    ISNULL(d.IncentiveQty, 0) AS IncentiveQty,      -- InventoryQty - decimal(18, 2)
                    ISNULL(d.IncentiveAmount, 0) AS IncentiveAmount,      -- IncentiveAmt - decimal(18, 2)
                    ed.RefNbr,       -- RefNbr - nvarchar(50)
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' AS CreatedByID,      -- CreatedByID - uniqueidentifier
                    'PDA' AS CreatedByScreenID,        -- CreatedByScreenID - char(8)
                    GETDATE() AS CreatedDateTime, -- CreatedDateTime - datetime
                    'B5344897-037E-4D58-B5C3-1BDFD0F47BF9' AS LastModifiedByID,      -- LastModifiedByID - uniqueidentifier
                    'PDA' AS LastModifiedByScreenID,        -- LastModifiedByScreenID - char(8)
                    GETDATE() AS LastModifiedDateTime -- LastModifiedDateTime - datetime
            INTO    #tmpIncentiveDetail
            FROM    dbo.DMSAimOrderIncentiveDetail d
                    JOIN #Orderlist o ON o.UserName = d.UserName
                                    AND o.OrderCode = d.OrderCode
                    JOIN BELDMS.dbo.Branch b ON b.BranchCD = o.DistributorCD
                                                        AND b.CompanyID = @CompanyID
                    JOIN BELDMS.dbo.DMSEvaluationDefine ed ON ed.CompanyID = @CompanyID
                                                            AND ed.RefNbr = d.IncentiveCD
                    LEFT JOIN dbo.DMSAimInventoryList il ON il.InventoryCD = d.InventoryCD
			WHERE b.Active = 1

 /****************************************************************************
        * Step 6
        * Insert all using transaction
        ****************************************************************************/

        BEGIN TRY
            BEGIN TRANSACTION

            INSERT INTO BELDMS.dbo.DMSPDAOrder
            (
                CompanyID,
                BranchID,
                OrderType,
                OrderNbr,
                OrderDate,
                Description,
                LineCntr,
                CustomerID,
                CustomerLocation,
                Route,
                SalesmanID,
                TotalQuantity,
                TotalSKUs,
                OrderAmount,
                LineDiscount,
                DocumentDiscount,
                OrderTotal,
                Status,
                PaymentStatus,
                ValidDate,
                ValidDistance,
                CreatedByID,
                CreatedByScreenID,
                CreatedDateTime,
                LastModifiedByID,
                LastModifiedByScreenID,
                LastModifiedDateTime
            )
            SELECT CompanyID,
                   BranchID,
                   OrderType,
                   OrderCode,
                   OrderDate,
                   Note,
                   TotalLine,
                   CustomerCD,
                   LocationID,
                   RouteCD,
                   UserName,
                   TotalQuantity,
                   TotalSKU,
                   OrderAmount,
                   LineDiscount,
                   DocDiscountAmt,
                   OrderTotal,
                   Status,
                   PaymentStatus,
                   ValidDate,
                   ValidDistance,
                   CreatedByID,
                   CreatedByScreenID,
                   CreatedDateTime,
                   LastModifiedByID,
                   LastModifiedByScreenID,
                   LastModifiedDateTime
            FROM #tmpHeader;


            INSERT INTO BELDMS.dbo.DMSPDAOrderLine
            (
                CompanyID,
                BranchID,
                OrderType,
                OrderNbr,
                LineNbr,
                InventoryID,
                Description,
                SiteID,
                LocationID,
                IsFree,
                UOM,
                SalesPrice,
                SuggestQty,
                OrderQty,
                DeliveryQty,
                ShippedQty,
                ManualDiscount,
                AllocationID,
                PromotionID,
                ManualPromotionID,
                IncentiveID,
                LineBeforeDiscount,
                DiscountAmt,
                LineAmt,
                CreatedByID,
                CreatedByScreenID,
                CreatedDateTime,
                LastModifiedByID,
                LastModifiedByScreenID,
                LastModifiedDateTime,
                IsPosm
            )
            SELECT CompanyID,
                   BranchID,
                   OrderType,
                   OrderCode,
                   LineNbr,
                   InventoryID,
                   Description,
                   SiteID,
                   LocationID,
                   FreeItem,
                   UnitPerCase,
                   UnitPrice,
                   SuggestQty,
                   OrderQty,
                   DeliveryQty,
                   ShippedQty,
                   ManualDiscount,
                   AllocationID,
                   PromotionID,
                   ManualPromotionID,
                   IncentiveID,
                   LineBeforeDiscount,
                   DiscountAmt,
                   LineAmt,
                   CreatedByID,
                   CreatedByScreenID,
                   CreatedDateTime,
                   LastModifiedByID,
                   LastModifiedByScreenID,
                   LastModifiedDateTime,
                   IsPosm
            FROM #tmpDetail;

            INSERT INTO BELDMS.dbo.DMSPDAInventoryLot
            (
                CompanyID,
                BranchID,
                OrderType,
                OrderNbr,
                LineNbr,
                InventoryID,
                IsFree,
                OrderQty,
                AllocationID,
                PromotionID,
                ManualPromotionID,
                IncentiveID,
                LotSerialNbr,
                ExpireDate
            )
            SELECT CompanyID,
                   BranchID,
                   OrderType,
                   OrderCode,
                   LineNbr,
                   InventoryID,
                   FreeItem,
                   Quantity,
                   AllocationID,
                   PromotionID,
                   ManualPromotionID,
                   IncentiveID,
                   Lot,
                   ExpireDate
            FROM #tmpLot;


            INSERT INTO BELDMS.dbo.DMSPDAPromotionDetail
            (
                CompanyID,
                BranchID,
                OrderNbr,
                OrderType,
                PromotionID,
                Type,
                InventoryID,
                LineNbr,
                OrderBreakByQty,
                OrderBreakByAmt,
                OrderFreeQty,
                OrderFreeAmt,
                OrderContributeFreeAmt,
                ShipmentBreakQty,
                ShipmentFreeQty,
                InvoiceBreakQty,
                InvoiceFreeQty,
                InvoiceContributeFreeAmt,
                OrderBreakByLevel
            )
            SELECT CompanyID,
                   BranchID,
                   OrderCode,
                   OrderType,
                   PromotionID,
                   Type,
                   InventoryID,
                   LineNbr,
                   OrderBreakQty,
                   OrderBreakByAmt,
                   OrderFreeQty,
                   OrderFreeAmt,
                   OrderContributeFreeAmt,
                   ShipmentBreakQty,
                   ShipmentFreeQty,
                   InvoiceBreakQty,
                   InvoiceFreeQty,
                   InvoiceContributeFreeAmt,
                   BreakDownLevelID
            FROM #tmpPromo;

            INSERT INTO BELDMS.dbo.DMSPDAIncentiveDetail
            (
                CompanyID,
                IncentiveLineNbr,
                IncentiveType,
                BranchID,
                OrderNbr,
                OrderType,
                ProgramID,
                InventoryID,
                InventoryQty,
                IncentiveAmt,
                RefNbr,
                CreatedByID,
                CreatedByScreenID,
                CreatedDateTime,
                LastModifiedByID,
                LastModifiedByScreenID,
                LastModifiedDateTime
            )
            SELECT CompanyID,
                   IncentiveLineNbr,
                   IncentiveType,
                   BranchID,
                   OrderCode,
                   OrderType,
                   ProgramID,
                   InventoryID,
                   IncentiveQty,
                   IncentiveAmount,
                   RefNbr,
                   CreatedByID,
                   CreatedByScreenID,
                   CreatedDateTime,
                   LastModifiedByID,
                   LastModifiedByScreenID,
                   LastModifiedDateTime
            FROM #tmpIncentiveDetail;

            COMMIT TRANSACTION
        END TRY

        BEGIN CATCH
            SELECT  ERROR_MESSAGE() AS ErrorMessage;
            ROLLBACK TRANSACTION
        END CATCH

        DROP TABLE #Orderlist
        DROP TABLE #OrderDetail
        DROP TABLE #OrderDiscount

        DROP TABLE #tmpHeader
        DROP TABLE #tmpDetail
        DROP TABLE #tmpLot
        DROP TABLE #tmpPromo
        DROP TABLE #tmpIncentiveDetail

        EXEC dbo.sp_Import_SalesmanStockRequest  @UserName, @CompanyID   -- int
        EXEC dbo.sp_Import_ManagementPOSM @UserName , @CompanyID
		EXEC dbo.sp_import_Incentive @UserName , @CompanyID

		EXEC dbo.sp_Import_Survey @UserName ,@CompanyID
	--	EXEC sp_Import_StockCount @UserName , @CompanyID

    END