daily pastebin goal
41%
SHARE
TWEET

Untitled

a guest Dec 14th, 2017 59 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top