daily pastebin goal
93%
SHARE
TWEET

Untitled

a guest Dec 14th, 2017 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [Adayroi_TMS]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[APIClient_Sodetail_GetProductTms_V01]    Script Date: 12/15/2017 9:42:53 AM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. --====================================================================================
  9. -- Entity Name: APIClient_Sodetail_GetProductTms_V01
  10. -- Author:  Trần an Bình
  11. -- Create date: 15/12/2014 8:32:27 PM
  12. -- Description: lấy danh sách serail
  13. -- ================================================================================
  14. ALTER PROCEDURE [dbo].[APIClient_Sodetail_GetProductTms_V01]
  15. (
  16.     @Soid bigint,
  17.     @Deliveryfrom datetime,
  18.     @SourceClientId int = 0
  19. )
  20. AS
  21. BEGIN
  22.     If (@SourceClientId is null OR @SourceClientId = 0)
  23.         BEGIN
  24.             Set @SourceClientId = 1
  25.         END
  26.     If(@SourceClientId = 1)
  27.         BEGIN
  28.         SELECT DISTINCT
  29.                 [Soid],
  30.                 [PoNumber],
  31.                 [ProductItemId],
  32.                 [ProductName],
  33.                 [ProductType],
  34.                 [Quantity]
  35.                 FROM
  36.         (SELECT DISTINCT   
  37.                 sd.ClientSoId as [Soid],
  38.                 sd.ClientPoId as [PoNumber],
  39.                 cp.ProductItemId as [ProductItemId],
  40.                 cp.ProductName as [ProductName],
  41.                 sd.ProductTypeID as [ProductType],
  42.                 sd.Amount as [Quantity]
  43.                 FROM
  44.                 dbo.SO_Detail sd WITH (NOLOCK)
  45.                 INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = sd.ClientProductId
  46.                 INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.ProductItemId = cp.ProductItemId AND pcdt.Soid = sd.ClientSoId)
  47.                 WHERE
  48.                 sd.ClientSoId = @Soid AND pcdt.Push = 0
  49.                 AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
  50.                 AND DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
  51.                 --ORDER BY
  52.                 --cp.ClientProductId DESC
  53.                 UNION   ALL
  54.  
  55.                 SELECT DISTINCT
  56.                 @Soid as [Soid],
  57.                 pofm.ClientPoId as [PoNumber],
  58.                 pofm.ProductItemId as [ProductItemId],
  59.                 pof.PoFeeName as [ProductName],
  60.                 1 AS  [ProductType], --hàng binh thường
  61.                 1 as [Quantity]
  62.                 FROM
  63.                 dbo.PurchaseOrder po WITH (NOLOCK)
  64.                 INNER JOIN  dbo.PurchaseOrder_Fee_Map pofm WITH(NOLOCK) ON pofm.ClientPoId= po.ClientPoId
  65.                 INNER JOIN  dbo.PurchaseOrder_Fee pof WITH(NOLOCK)  ON pof.PoFeeId =pofm.PoFeeId
  66.                 INNER JOIN  dbo.SO s WITH(NOLOCK) ON s.SOID = po.SoId  
  67.                 INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.Soid = s.ClientSoId AND po.PoId= pcdt.PoNumber)
  68.                 WHERE
  69.                 s.ClientSoId = @Soid
  70.                 AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
  71.                 AND DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
  72.                 AND pofm.Status  =1
  73.             ) AS t
  74.         UPDATE  PushCsDateTime
  75.         SET NumCount = NumCount + 1
  76.         FROM PushCsDateTime push
  77.         INNER JOIN  dbo.SO s    WITH(nolock) ON push.Soid   =s.ClientSoId
  78.         WHERE push.Soid =@Soid AND push.Push = 0
  79.         AND s.SourceClientId =@SourceClientId
  80.         END
  81.         ELSE
  82.             BEGIN
  83.                 SELECT DISTINCT
  84.                 [Soid],
  85.                 [PoNumber],
  86.                 [ProductItemId],
  87.                 [ProductName],
  88.                 [ProductType],
  89.                 [Quantity]
  90.                 FROM
  91.         (SELECT DISTINCT   
  92.                 sd.ClientSoId as [Soid],
  93.                 sd.ClientPoId as [PoNumber],
  94.                 cp.ProductItemId as [ProductItemId],
  95.                 cp.ProductName as [ProductName],
  96.                 sd.ProductTypeID as [ProductType],
  97.                 sd.Amount as [Quantity]
  98.                 FROM
  99.                 dbo.SO_Detail sd WITH (NOLOCK)
  100.                 INNER JOIN dbo.ClientProduct cp WITH (NOLOCK) ON cp.ClientProductId = sd.ClientProductId
  101.                 INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.Soid = sd.ClientSoId AND pcdt.SODetailID   = sd.SODetailID AND sd.Visible  =1)
  102.                 WHERE
  103.                 sd.ClientSoId = @Soid AND pcdt.Push = 0
  104.                 AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
  105.                 AND DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
  106.                 --ORDER BY
  107.                 --cp.ClientProductId DESC
  108.                 UNION   ALL
  109.  
  110.                 SELECT DISTINCT
  111.                 @Soid as [Soid],
  112.                 pofm.ClientPoId as [PoNumber],
  113.                 pofm.ProductItemId as [ProductItemId],
  114.                 pof.PoFeeName as [ProductName],
  115.                 1 AS  [ProductType], --hàng binh thường
  116.                 1 as [Quantity]
  117.                 FROM
  118.                 dbo.PurchaseOrder po WITH (NOLOCK)
  119.                 INNER JOIN  dbo.PurchaseOrder_Fee_Map pofm WITH(NOLOCK) ON pofm.ClientPoId= po.ClientPoId
  120.                 INNER JOIN  dbo.PurchaseOrder_Fee pof WITH(NOLOCK)  ON pof.PoFeeId =pofm.PoFeeId
  121.                 INNER JOIN  dbo.SO s WITH(NOLOCK) ON s.SOID = po.SoId  
  122.                 INNER JOIN dbo.PushCsDateTime pcdt WITH(NOLOCK) ON (pcdt.Soid = s.ClientSoId AND po.PoId= pcdt.PoNumber)
  123.                 WHERE
  124.                 s.ClientSoId = @Soid
  125.                 AND convert(date,pcdt.TmsShippingFrom) = convert(DATE,@Deliveryfrom)
  126.                 AND DATEPART(hour,pcdt.TmsShippingFrom) = DATEPART(hour,@Deliveryfrom)
  127.                 AND pofm.Status  =1
  128.             ) AS t
  129.             END
  130.  
  131.        
  132. END
  133.  
  134.  
  135.  
  136. --SELECT    * FROM dbo.SO_Detail sd WHERE sd.ClientSoId =160146296
RAW Paste Data
Top