SHARE
TWEET

Untitled

a guest Oct 16th, 2019 82 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ALTER PROCEDURE xspStyleReconciliationGetList
  2. (  
  3.     @guid xdtLongName,
  4.     @fromPage xdtName,
  5.     @pageParams xdtUniCodeMaxText='',
  6.     @searchParams xdtUniCodeMaxText='',
  7.     @sortParams xdtUniCodeLongText='',
  8.     @pageIndex smallInt=1,
  9.     @pageSize Int=20,
  10.     @errorMsg VARCHAR(1000)='' OUTPUT    
  11. )
  12. AS
  13. BEGIN  
  14.     DECLARE @companyCode xdtCompanyCode,@memberCompanyCode xdtCompanyCode,@companyDivisionCode xdtCompanyCode,@userName xdtName    
  15.     EXEC xspGetSessionVariable  @guid=@guid ,@companyCode =@companyCode  OUTPUT,@membercompanyCode =@membercompanyCode  OUTPUT,
  16.     @companyDivisionCode =@companyDivisionCode  OUTPUT, @userName =@userName OUTPUT
  17.    
  18.     DECLARE  @StyleCode xdtLongCode, @StyleName xdtText, @BuyerStyleRef xdtName, @StyleClosedDate xdtName
  19.         , @StyleClosedDateFrom xdtDate, @StyleClosedDateTo xdtDate, @ClosedBy xdtUniCodeLongText
  20.         , @LastETDDate xdtName, @LastETDDateFrom xdtDate, @LastETDDateTo xdtDate, @TranNum xdtName, @CalledFrom xdtName
  21.         , @Buyer xdtName, @DivisionManager xdtUnicodeLongText, @Manager xdtUnicodeLongText, @Status xdtName
  22.         , @IsExportToExcel xdtTinyNum
  23.    
  24.     IF (ISNULL(@searchParams,'') <>'')
  25.     BEGIN
  26.         CREATE TABLE #SearchParam(ParamName Nvarchar(1000),ParamValue Nvarchar(max))
  27.         EXEC dbo.xspGetTableFromString @details =@searchParams,@rowSeprator ='~',@columnSeperator ='|',@tableName ='#SearchParam'      
  28.         SELECT @StyleCode=ParamValue From #SearchParam Where ParamName='colStyleCode'
  29.         SELECT @StyleName=ParamValue From #SearchParam Where ParamName='colStyleName'      
  30.         SELECT @BuyerStyleRef=ParamValue From #SearchParam Where ParamName='colBuyerStyleRef'      
  31.         SELECT @StyleClosedDate=ParamValue From #SearchParam Where ParamName='colClosedDate'      
  32.         SELECT @LastETDDate=ParamValue From #SearchParam Where ParamName='colLastETDDate'      
  33.         SELECT @ClosedBy=ParamValue From #SearchParam Where ParamName='colClosedBy'
  34.         SELECT @TranNum=ParamValue From #SearchParam Where ParamName='colTranNum'              
  35.         SELECT @Status = ParamValue From #SearchParam Where ParamName='colStatus'  
  36.         SELECT @Buyer = ParamValue From #SearchParam Where ParamName='colBuyer'
  37.  
  38.         IF ISNULL(@StyleClosedDate,'')=''
  39.         BEGIN
  40.             SELECT @StyleClosedDateFrom=paramvalue from #SearchParam where ParamName='colClosedDateFrom';
  41.             SELECT @StyleClosedDateTo=paramvalue from #SearchParam where paramname='colClosedDateTo'
  42.         END
  43.         ELSE
  44.         EXEC xspGetDateRange @param=@StyleClosedDate, @fromDate=@StyleClosedDateFrom output, @toDate=@StyleClosedDateTo output, @errorMsg=@errorMsg output
  45.  
  46.         IF ISNULL(@LastETDDate,'')=''
  47.         BEGIN
  48.             SELECT @LastETDDateFrom=paramvalue from #SearchParam where ParamName='colLastETDDateFrom';
  49.             SELECT @LastETDDateTo=paramvalue from #SearchParam where paramname='colLastETDDateTo'
  50.         END
  51.         ELSE
  52.         EXEC xspGetDateRange @param=@LastETDDate, @fromDate=@LastETDDateFrom output, @toDate=@LastETDDateTo output, @errorMsg=@errorMsg output
  53.     END    
  54.  
  55.     if ISNULL(@pageParams,'') <>''
  56.     BEGIN
  57.         CREATE TABLE #PageParam(ParamName varchar(1000),ParamValue varchar(max))
  58.         EXEC dbo.xspGetTableFromString @Details =@pageParams,@RowSeprator ='~',@ColumnSeperator ='|',@TableName ='#PageParam'                  
  59.         SELECT @fromPage=ParamValue FROM #PageParam where ParamName='FromPage'    
  60.         SELECT @CalledFrom=ParamValue FROM #PageParam where ParamName='CalledFrom'
  61.         select @IsExportToExcel=ParamValue FROM #PageParam where ParamName='IsExportToExcel'  
  62.     END    
  63.        
  64.     CREATE TABLE #PendingStyleReconciledData(
  65.         [StyleReconciliationID] xdtId, [BuyerCompanyName] xdtUnicodeText, [DivisionManager] xdtUniCodeLongText NULL,
  66.         [Manager] xdtUniCodeLongText NULL, [Style] xdtText NULL, [StyleCode]  xdtText NULL, [StyleName]  xdtText NULL,
  67.         [BuyerStyleRefNo]  xdtName NULL, [LastETDDate] xdtName NULL, [GMPOETDDate] xdtName NULL, [StyleClosedDate] xdtName NULL,
  68.         [TranNum] xdtName NULL, [ClosedBy] xdtLongName NULL, [Status] xdtName NULL, [IsStyleReconciled] xdtbit NULL,
  69.         [SupplierShipmentCode] xdtInt NULL
  70.     )  
  71.    
  72.     IF (@CalledFrom='PendingStyleReconciliationList')
  73.     BEGIN
  74.         /*Start<IssueID:112364>*/
  75.         INSERT INTO #PendingStyleReconciledData(StyleReconciliationID, StyleCode, StyleName, BuyerStyleRefNo, BuyerCompanyName, DivisionManager, Manager
  76.             , LastETDDate,GMPOETDDate, ClosedBy, Status, TranNum,IsStyleReconciled,StyleClosedDate,SupplierShipmentCode)
  77.         SELECT st.id , st.ArticleCode, st.ArticleName, st.BuyerReference, buyer.CompanyName , ''[DivisionManager], ''[Manager],
  78.             ''[LastETDDate],NULL , ''[ClosedBy],
  79.             CASE WHEN (IsNull(st.IsRMReco,0)= 1 OR IsNull(st.IsShipmentDelayRemarks,0)= 1 OR IsNull(st.IsGarmentQtyReco,0)= 1) THEN 'Part Reco'
  80.             ELSE '' END,'',st.IsStyleReconciled,
  81.             Convert(varchar(11),st.LastChanged,106), 0 SupplierShipmentCode    
  82.         From xtArticle st(nolock)
  83.         JOIN xtCompany buyer(NOLOCK) on buyer.MemberCompanyCode=st.Buyer                      
  84.         where Not Exists(Select top 1 1 from xtArticle art
  85.                 Where art.ID=st.ID and Isnull(art.IsStyleReconciled,0) = 1 And IsNull(art.ReconTranNum,'')<>''
  86.                     And IsNull(art.IsShipmentDelayRemarks,0)=1 And IsNull(art.IsGarmentQtyReco,0)=1 And IsNull(art.IsRMReco,0)=1)
  87.          
  88.         AND EXISTS(
  89.             SELECT TOP  1 1  FROM  xtOrder ord
  90.             JOIN xtOrderShipment os ON os.OrderCode = ord.OrderCode and os.IsLastVersion =1 and os.Confirmed=1
  91.             WHERE ord.OCType<>-1 and ord.IsLastVersion = 1 And ord.OrderType<>6 and ord.StyleID = st.ID
  92.         )
  93.         AND NOT EXISTS  (
  94.             SELECT TOP  1 1  FROM  xtOrder ord
  95.             JOIN xtOrderShipment os ON os.OrderCode = ord.OrderCode and os.IsLastVersion =1 and os.Confirmed=1
  96.             LEFT JOIN xtGMPOActualConsASN asn (NOLOCK) ON asn.SupplierShipmentCode=os.GMPOASNSupplierShipmentCode  
  97.                 AND asn.ShipmentRevisedCode = os.ShipmentRevisedCode
  98.             WHERE ord.OCType<>-1 and ord.IsLastVersion = 1 And ord.OrderType<>6 and ord.StyleID = st.ID and asn.GMPOActualConsASNID IS NULL
  99.         )                                  
  100.                                
  101.         AND (IsNull(@StyleCode,'')='' OR st.ArticleCode LIKE '%' + @StyleCode + '%')
  102.         AND (IsNull(@StyleName,'')='' OR st.ArticleName LIKE '%' + @StyleName + '%')
  103.         AND (IsNull(@BuyerStyleRef,'')='' OR st.BuyerReference like '%' + @BuyerStyleRef + '%')
  104.         AND (IsNull(@Buyer,'')='' OR Buyer.CompanyName like '%' + @Buyer + '%')
  105.         Order By st.ID Desc
  106.         /*End<IssueID:112364>*/
  107.     END
  108.     Else IF (@CalledFrom='StyleReconciliationList')
  109.     BEGIN
  110.         /*Start<IssueID:112364>*/
  111.         INSERT INTO #PendingStyleReconciledData(StyleReconciliationID, StyleCode, StyleName, BuyerStyleRefNo, BuyerCompanyName, DivisionManager, Manager
  112.             , LastETDDate,GMPOETDDate, ClosedBy, Status, TranNum,IsStyleReconciled,StyleClosedDate,SupplierShipmentCode)
  113.         SELECT st.ID , st.ArticleCode, st.ArticleName, st.BuyerReference, buyer.CompanyName , ''[DivisionManager], ''[Manager],
  114.             ''[LastETDDate],NULL , us.FullName [ClosedBy],'',st.ReconTranNum,st.IsStyleReconciled,Convert(varchar(11),st.LastChanged,106), 0 SupplierShipmentCode              
  115.         FROM xtArticle st(NOLOCK)          
  116.         JOIN xtCompany buyer(NOLOCK) on buyer.MemberCompanyCode=st.Buyer                      
  117.         JOIN xtUser us(NOLOCK) on us.UserName=st.CreatedBy                    
  118.         where IsStyleReconciled = 1 And ReconTranNum Is Not NULL
  119.             AND (IsNull(@StyleCode,'')='' OR st.ArticleCode LIKE '%' + @StyleCode + '%')
  120.             AND (IsNull(@StyleName,'')='' OR st.ArticleName LIKE '%' + @StyleName + '%')
  121.             AND (IsNull(@BuyerStyleRef,'')='' OR st.BuyerReference like '%' + @BuyerStyleRef + '%')
  122.             AND (IsNull(@Buyer,'')='' OR Buyer.CompanyName like '%' + @Buyer + '%')
  123.             AND (@StyleClosedDateFrom IS NULL OR (st.LastChanged) > = @StyleClosedDateFrom)
  124.             AND (@StyleClosedDateTo IS NULL OR (st.LastChanged) < = @StyleClosedDateTo)            
  125.             AND (IsNull(@ClosedBy,'')='' OR us.FullName like '%' + @ClosedBy + '%')
  126.             AND (IsNull(@TranNum,'')='' OR st.ReconTranNum like '%' + @TranNum + '%')
  127.         /*End<IssueID:112364>*/
  128.     END
  129.    
  130.     UPDATE g SET g.DivisionManager=u.FullName,g.Manager = u1.FullName
  131.     from  #PendingStyleReconciledData  g
  132.     JOIN xtOrder a(NOLOCK)  on a.StyleID = g.StyleReconciliationID  and a.OrderCode>0  and a.OCType<>-1 and a.IsLastVersion=1
  133.         and a.ID =(select Min(ID) from xtOrder  where StyleID = a.StyleID and OrderCode>0  and OCType<>-1 and IsLastVersion=1)
  134.     JOIN xtUser u(NOLOCK) On u.UserName = a.DivisionManagerUserName
  135.     JOIN xtUser u1(NOLOCK) On u1.UserName = a.ManagerUserName          
  136.  
  137.     Select  g.StyleReconciliationID, max(dbo.xfGetETDDate(b.ShipmentMode,b.AWBETD,b.SubETD,b.BLMotherETD))  LastETDDate, b.SupplierShipmentCode
  138.     INTO #ShipmentETDDate
  139.     from  #PendingStyleReconciledData  g
  140.     Join xtOrder a(NOLOCK)  on a.StyleID = g.StyleReconciliationID  and a.OrderCode>0  and a.OCType<>-1 and a.IsLastVersion=1
  141.     Join xtOrderShipment  b on b.CompanyCode = a.CompanyCode AND b.OrderCode=a.OrderCode and b.IsLastVersion  = 1
  142.     GROUP BY g.StyleReconciliationID, b.SupplierShipmentCode
  143.        
  144.     UPDATE a SET a.LastETDDate = Convert(varchar(11),b.LastETDDate,106), a.GMPOETDDate = Convert(varchar(11),b.LastETDDate,106),
  145.     a.SupplierShipmentCode = b.SupplierShipmentCode
  146.     From #PendingStyleReconciledData a
  147.     JOIN #ShipmentETDDate b On a.StyleReconciliationID = b.StyleReconciliationID      
  148.    
  149.     --UPDATE a set a.ClosedBy = c.FullName
  150.     --From #PendingStyleReconciledData a
  151.     --JOIN xtArticleRC b (NOLOCK) ON b.StyleID = a.StyleReconciliationID
  152.     --JOIN xtUser c (NOLOCK) ON c.UserName = b.CreatedBy
  153.    
  154.     SELECT * INTO #GMPOData1
  155.     FROM #PendingStyleReconciledData  
  156.     Where (IsNull(@DivisionManager,'')='' OR DivisionManager like '%' + @DivisionManager + '%')
  157.         AND (IsNull(@Manager,'')='' OR Manager like '%' + @Manager + '%')
  158.  
  159.     SELECT * INTO #GMPOData
  160.     FROM #GMPOData1
  161.     Where (IsNull(@LastETDDateFrom ,'')='' OR (LastETDDate) > = @LastETDDateFrom)
  162.         AND (IsNull(@LastETDDateTo ,'')='' OR (LastETDDate) < = @LastETDDateTo)    
  163.         And (@Status Is NULL OR Status LIKE '%' + @Status + '%')
  164.        -- and (@searchParams <> '' OR ISNULL(@LastETDDate, '') <> '' OR (ISNULL(@LastETDDateFrom, '') = '' AND ISNULL(@LastETDDateTo, '') = ''
  165.         --      AND DATEDIFF(M, LastETDDate, GETDate()) <=2 ))  
  166.        
  167.     If ISNULL(@sortParams,'') =''
  168.         set @sortParams = 'colStyleReconciliationID|Desc'
  169.  
  170.     Select * into #GMPODataFinal From #GMPOData where 1=2  
  171.            
  172.     IF @IsExportToExcel = '1'
  173.     BEGIN
  174.         SELECT @pageIndex = 1, @pageSize = COUNT(1) FROM #GMPOData
  175.     END        
  176.     Execute xspGetPageData @sourceTable = '#GMPOData',@sortParams=@sortParams,@pageIndex=@pageIndex, @pageSize=@pageSize,@errorMsg=@errorMsg output
  177.                        
  178.     SELECT  * FROM #GMPODataFinal Order By StyleReconciliationID Desc  
  179.     SELECT Count(1) RecordCount FROM [#GMPODataFinal]              
  180. END
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