Ranish666

Purchase Edit changes

Apr 27th, 2021
1,004
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. alter table tblPurchaseEditEntry add soldQTY decimal(18,2) null, isSold int null;
  2. go
  3. go
  4. Alter proc [dbo].[PurchaseEdit_getPurchaseDetailsbyRefno]    
  5. @ReferenceNo nvarchar(500)                  
  6. as                  
  7. begin    
  8.  with checkSoldQty as (
  9.     select pd.ItemID, pd.Itemtype, pd.Batch, isnull(sum( st.outward_qnty),0) soldQTY from tblpurchaseDetails pd
  10.      left join tblstock st on st.batch = pd.Batch and st.item_id = pd.ItemID and st.stock_type = 'Pharmacy Sale'
  11.     where PurchaseMasterID = (select PurchaseMasterID from tblPurchaseMaster where ReferenceNo = @ReferenceNo)
  12.     group by pd.ItemID, pd.Itemtype, pd.Batch
  13.  )
  14.  
  15.  select PD.PurchaseDetailsID,PM.PurchaseMasterID,Pm.ReferenceNo,Pm.VendorBillNo,TPT.typeid,TPT.purchaseType as Itemtype,PD.ItemID,  
  16.  (case when PD.Itemtype=1 then  (select brandname from tblBrand where bid=pd.ItemID)                
  17.  when PD.Itemtype=2 then  (select brandname from tblSurgicalbrand where SurgBrandId=pd.ItemID)                
  18.  else (select brandname from tblExtra where EId=pd.ItemID) end) as 'BrandName',          
  19.  PD.StoreDepartmentID,TSD.Department,PD.ledgerID as AccountledgerID,ta.ledgerName,PD.Batch,PD.mode,convert(date,pd.ExpireDate) as 'ExpireDate',          
  20.  PD.TaxAmt as  'TaxAmt',                  
  21.  PD.Taxper as 'Taxper',                
  22.  PD.TotalCost as 'TotalCost',                  
  23.  PD.profitPer as 'profitPer',                  
  24.  PD.TotalQty as 'TotalQty',                
  25.  PD.rate as 'rate',                
  26.  PD.MRP as 'MRP',                  
  27.  PD.CashDiscPer as 'CashDiscPer',                  
  28.  PD.CashDisc as 'CashDisc',                  
  29.  PD.QtyBonus as 'QtyBonus',                  
  30.  PD.CCPer as 'CCPer',                  
  31.  PD.NetUnitCost as 'NetUnitCost',                  
  32.  PD.DiscUnitCost as 'DiscUnitCost',                  
  33.  PD.CurentSellPrice as 'CurentSellPrice',                  
  34.  PD.newSellproce as 'newSellproce',              
  35.  PD.TotalCost as 'TotalCost' ,
  36.  cs.soldQTY,
  37.  case when cs.soldQTY > 0 then 1 else 0 end isSold
  38.  from tblPurchaseMaster PM                
  39.  inner join tblpurchaseDetails PD on PM.PurchaseMasterID=PD.PurchaseMasterID  and PM.Type='Purchases'              
  40.  inner join tbl_AccountLedger ta on PM.AccountledgerID = ta.ledgerId                  
  41.  inner join tblStoreDepartment TSD on TSD.StoreDepartmentID=PD.StoreDepartmentID                  
  42.  inner join tblPurchaseType TPT on TPT.typeid=PD.Itemtype  
  43.  left join checkSoldQty cs on cs.ItemID = pd.ItemID and cs.Batch = pd.Batch and cs.Itemtype = pd.Itemtype
  44.  where ltrim(rtrim(PM.ReferenceNo))=ltrim(rtrim(@ReferenceNo))    
  45.      
  46. end  
  47. go
  48. Alter proc [dbo].[PurchaseEdit_Insert]                
  49. @PurchaseMasterID int,                
  50. @PurchaseDetailsID int ,                
  51. @VendorBillNo nvarchar(MAX),                
  52. @ReferenceNo nvarchar(MAX),              
  53. @AccountledgerID int ,              
  54. @mode nvarchar(MAX) ,                
  55. @StoreDepartmentID int ,                
  56. @Itemtype nvarchar(MAX) ,                
  57. @ItemID int ,                
  58. @BrandName nvarchar(MAX),                
  59. @Batch nvarchar(MAX) ,                
  60. @ExpireDate datetime ,                
  61. @TotalQty decimal(18, 2),                
  62. @rate decimal(18, 2) ,                
  63. @CashDiscPer decimal(18, 2),                
  64. @CashDisc decimal(18, 2),                
  65. @Taxper decimal(18, 2) ,                
  66. @TaxAmt decimal(18, 2) ,                
  67. @MRP decimal(18, 2) ,                
  68. @profitPer decimal(18, 2) ,                
  69. @QtyBonus decimal(18, 2) ,                
  70. @CCPer decimal(18, 2) ,                
  71. @NetUnitCost decimal(18, 2),                
  72. @DiscUnitCost decimal(18, 2),                
  73. @TotalCost decimal(18, 2),                
  74. @CurentSellPrice decimal(18, 2) ,                
  75. @newSellproce decimal(18, 2),          
  76. @Action nvarchar(MAX),  
  77. @userid int  ,
  78. @soldQTY decimal(18,2),
  79. @isSold int
  80. as                
  81. BEGIN                
  82. IF not exists(select * from tblPurchaseEditEntry where PurchaseMasterID=@PurchaseMasterID and ItemID=@ItemID and Itemtype=@Itemtype        
  83. and Batch=@Batch  and StoreDepartmentID=@StoreDepartmentID)                
  84. BEGIN                
  85.  insert into tblPurchaseEditEntry(PurchaseMasterID,PurchaseDetailsID,VendorBillNo,ReferenceNo,AccountledgerID,mode,StoreDepartmentID,Itemtype,ItemID,BrandName,Batch,          
  86.  ExpireDate,TotalQty,rate,CashDiscPer,CashDisc,Taxper,TaxAmt,MRP,profitPer,QtyBonus,CCPer,NetUnitCost,DiscUnitCost,TotalCost,CurentSellPrice,newSellproce,[Action],userid,createdate,
  87.  soldQTY, isSold)                
  88.  values (@PurchaseMasterID,@PurchaseDetailsID,@VendorBillNo,@ReferenceNo,@AccountledgerID,@mode,@StoreDepartmentID,@Itemtype,@ItemID,@BrandName,@Batch,@ExpireDate,          
  89.  @TotalQty,@rate,@CashDiscPer,@CashDisc,@Taxper,@TaxAmt,@MRP,@profitPer,@QtyBonus,@CCPer,@NetUnitCost,@DiscUnitCost,@TotalCost,@CurentSellPrice,@newSellproce,@Action,@userid,GETDATE(),
  90.  @soldQTY, @isSold)                
  91. END                
  92. END  
  93. go
  94. go
  95. Alter proc [dbo].[PurchaseEditEntry_getEditEntry]                  
  96. @VenderInvoiceNum nvarchar(500)                      
  97. as                      
  98. begin                      
  99.  select Pm.Id,PM.PurchaseDetailsID,PM.PurchaseMasterID,PM.VendorBillNo,Pm.ReferenceNo,PM.StoreDepartmentID,TSD.Department,PM.AccountledgerID,ta.ledgerName,                    
  100.  TPT.typeid,TPT.purchaseType as Itemtype,PM.Batch,PM.mode,convert(date,PM.ExpireDate) as 'ExpireDate',PM.ItemID,PM.BrandName,                  
  101.  convert(decimal(18,5),PM.TaxAmt)as 'TaxAmt',                      
  102.  convert(decimal(18,5),PM.Taxper)as 'Taxper',                    
  103.  convert(decimal(18,5),PM.TotalCost)as 'TotalCost',                      
  104.  convert(decimal(18,5),PM.profitPer)as 'profitPer',                      
  105.  convert(decimal(18,5),PM.TotalQty)as 'TotalQty',                    
  106.  convert(decimal(18,5),PM.rate)as 'rate',                    
  107.  convert(decimal(18,5),PM.MRP)as 'MRP',                      
  108.  convert(decimal(18,5),PM.CashDiscPer)as 'CashDiscPer',                      
  109.  convert(decimal(18,5),PM.CashDisc)as 'CashDisc',                      
  110.  convert(decimal(18,5),PM.QtyBonus)as 'QtyBonus',                      
  111.  convert(decimal(18,5),PM.CCPer)as 'CCPer',                      
  112.  convert(decimal(18,5),PM.NetUnitCost)as 'NetUnitCost',                      
  113.  convert(decimal(18,5),PM.DiscUnitCost)as 'DiscUnitCost',                      
  114.  convert(decimal(18,5),PM.CurentSellPrice)as 'CurentSellPrice',                      
  115.  convert(decimal(18,5),PM.newSellproce)as 'newSellproce',      
  116.  (select isnull(qnty,0)  from tblstockSummary where itemid=PM.ItemID and itemtype=PM.Itemtype and batch=pm.Batch and  StoreDepartmentID=pm.StoreDepartmentID) as StockQty,  
  117.  PM.[Action]  ,
  118.  pm.soldQTY, pm.isSold
  119.  from tblPurchaseEditEntry PM                  
  120.  inner join tbl_AccountLedger ta on PM.AccountledgerID = ta.ledgerId                      
  121.  inner join tblStoreDepartment TSD on TSD.StoreDepartmentID=PM.StoreDepartmentID                      
  122.  inner join tblPurchaseType TPT on TPT.typeid=PM.Itemtype                      
  123.  where ltrim(rtrim(PM.ReferenceNo))=ltrim(rtrim(@VenderInvoiceNum))  --and Action<>'Delete'                    
  124. end
  125. go
  126. go
  127. Alter proc [dbo].[PurchaseEdit_UpdatePurchaseDetails]    
  128. @PurchaseMasterId int,    
  129. @PurchaseDetailsID int,      
  130. @VenderInvoiceNum nvarchar(500),        
  131. @ledgerID int,        
  132. @StoreDepartmentID int,        
  133. @Itemtype nvarchar(500),        
  134. @Batch nvarchar(500),        
  135. @ExpireDate datetime,          
  136. @TotalCost decimal(18,2),        
  137. @profitPer decimal(18,2),        
  138. @TotalQty  decimal(18,2),        
  139. @MRP decimal(18,2),        
  140. @CashDiscPer  decimal(18,2),        
  141. @CashDisc decimal(18,2),        
  142. @QtyBonus decimal(18,2),        
  143. @CCPer decimal(18,2),        
  144. @NetUnitCost decimal(18,2),        
  145. @DiscUnitCost decimal(18,2),        
  146. @CurentSellPrice decimal(18,2),        
  147. @newSellproce decimal(18,2),        
  148. @ItemID int,        
  149. @rate decimal(18,2),        
  150. @Taxper decimal(18,2),        
  151. @TaxAmt decimal(18,2),        
  152. @mode nvarchar(500),  
  153. @invoicedate datetime  
  154. as        
  155. begin        
  156. declare @preqty decimal(18,2)  
  157. declare @stockqty decimal(18,2)  
  158. declare @diffqty decimal(18,2)  
  159. declare @remainstock decimal(18,2)  
  160.  
  161. --previous  qty  
  162. set @preqty=(select (TotalQty+isnull(QtyBonus,0)) as TotalQty from tblpurchaseDetails  where PurchaseDetailsID=@PurchaseDetailsID)  
  163.  
  164. --stock  qty  
  165. if exists( select * from tblstockSummary where itemid=@ItemID and itemtype=@Itemtype and batch=@batch and  StoreDepartmentID=@StoreDepartmentID)    
  166.  begin    
  167.  set @stockqty=(select isnull(qnty,0) as Qty from tblstockSummary where itemid=@ItemID and itemtype=@Itemtype and batch=@batch and  StoreDepartmentID=@StoreDepartmentID)    
  168.  end    
  169. else    
  170.  begin    
  171.  set @stockqty=0  
  172.  end  
  173.  
  174.  
  175. set @diffqty=(isnUll(@TotalQty+@QtyBonus,0))-@preqty --qty  diff  
  176. set @remainstock=@stockqty+(@diffqty) --remain qty    
  177.  
  178. --check qty  
  179.   if (@remainstock >= 0)  
  180.  begin  
  181.     --update in purchasedetails table  
  182.   update tblpurchasedetails set   VenderInvoiceNum=@VenderInvoiceNum,ledgerID=@ledgerID,StoreDepartmentID=@StoreDepartmentID,Itemtype=@Itemtype,      
  183.   Batch=@Batch,ExpireDate=@ExpireDate,TotalCost=@TotalCost,profitPer=@profitPer,TotalQty=@TotalQty,MRP=@MRP,CashDiscPer=@CashDiscPer,        
  184.   CashDisc=@CashDisc,QtyBonus=@QtyBonus,CCPer=@CCPer,NetUnitCost=@NetUnitCost,DiscUnitCost=@DiscUnitCost,CurentSellPrice=@CurentSellPrice,newSellproce=@newSellproce,      
  185.   ItemID=@ItemID,rate=@rate,Taxper=@Taxper,TaxAmt=@TaxAmt,mode=@mode where  PurchaseDetailsID=@PurchaseDetailsID and PurchaseMasterID=@PurchaseMasterId  
  186.   update tblpurchasedetails set VenderInvoiceNum=@VenderInvoiceNum,ledgerID=@ledgerID,mode=@mode where PurchaseMasterID=@PurchaseMasterId  
  187.    
  188.   --delete from purchase edit table  
  189.   delete  from tblPurchaseEditEntry  where PurchaseDetailsID=@PurchaseDetailsID  
  190.   if (select count(PurchaseDetailsID) from tblPurchaseEditEntry) > 0 begin truncate table tblPurchaseEditEntry end
  191.  
  192.   declare @Itemname nvarchar(500)                  
  193.   declare @UnitCost decimal(18,2)                  
  194.   declare @Dosageform nvarchar(max)                  
  195.   declare @strengthlabeling nvarchar(max)  
  196.    
  197.   IF(@Itemtype='1')                  
  198.    BEGIN                  
  199.    SET @Itemname =(select brandname from tblBrand where bid=@ItemID)                  
  200.    SET @Dosageform=(select Dosageform from tblBrand where bid=@ItemID)                  
  201.    SET @strengthlabeling=(select Saltwithstrength  from tblBrand where  bid=@ItemID)                  
  202.    END                  
  203.                  
  204.   ELSE IF(@Itemtype='2')                  
  205.    BEGIN                  
  206.    SET @Itemname =(select brandname from tblSurgicalbrand where SurgBrandId=@ItemID)                  
  207.    SET @Dosageform=(select Unit from tblSurgicalbrand where SurgBrandId=@ItemID)              
  208.    SET @strengthlabeling=(select Surgicalname from tblSurgicalbrand where SurgBrandId=@ItemID)                      
  209.    END                  
  210.                  
  211.   ELSE IF(@Itemtype='3')                  
  212.    BEGIN                  
  213.    SET @Itemname =(select brandname from tblExtra where EId=@ItemID)                  
  214.    SET @Dosageform=(select unit from tblExtra where EId=@ItemID)                  
  215.    SET @strengthlabeling=(select Groupname from tblExtra where EId=@ItemID)                    
  216.    END    
  217.               ---update in stock table  
  218.    update tblstock set [date]=@invoicedate,item_id=@ItemID,item_type=@Itemtype,batch=@batch,[expiry_date]=@ExpireDate,netunit_cost=@NetUnitCost,        
  219.    inward_qnty=inward_qnty+@diffqty,outward_qnty=0,newsellPrice=@newSellproce,StoreDepartmentID=@StoreDepartmentID        
  220.    where stock_type='Purchases' and details_id=@PurchaseDetailsID  and master_id=@PurchaseMasterId        
  221.              
  222.    ---update in stocksummary table  
  223.    update tblstockSummary set qnty=qnty+@diffqty,lastupdatedate=getdate(),salesprice=@newSellproce,UnitCost=@NetUnitCost                  
  224.    where itemid=@ItemID and itemtype=@Itemtype and batch=@batch and  StoreDepartmentID=@StoreDepartmentID  
  225.      
  226.    select 'Success'  
  227.   END  
  228. ELSE  
  229.   BEGIN  
  230.   select 'Failed'  
  231.   END  
  232. END  
  233. go
  234. go
RAW Paste Data