SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_ItemHistory] 
@fromdate datetime ,  
@todate datetime ,@StoreShop Nvarchar(500),
@Itemcode Nvarchar(500)

AS        
BEGIN       

   
DECLARE @stockstartdate AS datetime
DECLARE @DeliveryEffect AS integer

set @stockstartdate=(select isnull(stockdate,'01/01/1900')'date' from stockstartdate) 
set @DeliveryEffect=(select isnull(DeliveryEffect,1)'DeliveryEffect' from PermanentSettings) 

if @DeliveryEffect=1
begin

select   [dbo].[SP_OpstockNewMethod]  (@fromdate,@Itemcode,@StoreShop) as opstock,EntryDate,DOCNO,unit,qtyperunit,storeshop,TrType,sum(Purchaseqty) as Purchaseqty,sum(InvoiceRetQty) as InvoiceRetQty,sum(TransQty) as TransQty,sum(PurchaseRetqty) as PurchaseRetqty,sum(Invoiceqty) as Invoiceqty,
sum(Deliveryqty) as Deliveryqty,sum(ProductionQtyF) as ProductionQtyF,sum(productionQtyR) as productionQtyR,sum(Damageqty) as Damageqty,sum(TransOutqty) as TransOutqty,sum(AdjQty) as AdjQty,RATE,CUSTSUPNAME  from (

select 0 as Opstock,@stockstartdate as  EntryDate,'Opstock' as DOCNO,'Each' as unit,1 as qtyperunit,@StoreShop as storeshop,'Opstock' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as TransQty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty   from item 
WHERE  isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) 

union all

select 0 as Opstock, PURCHASEITEM.EntryDate,PURCHASEITEM.Documentno as DOCNO,unit,qtyperunit,storeshop,'Purchase' as TrType
,isnull(sum(isnull(qty,0)),0) as Purchaseqty,0 as  InvoiceRetQty,0 as TransQty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,RATE AS RATE,SUPPLIERNAME AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from PurchaseItem  INNER  JOIN PURCHASE ON PURCHASE.DOCUMENTNO=PURCHASEITEM.DOCUMENTNO AND PURCHASE.PR=PURCHASEITEM.PR
where PurchaseItem.entrydate>= @stockstartdate and  PURCHASEITEM.entryDate between @fromdate and  @todate and PURCHASEITEM.PR='Purchase'  and isnull(PURCHASEITEM.deleted,'N')='N' and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,PURCHASEITEM.EntryDate,PURCHASEITEM.Documentno,unit,qtyperunit,storeshop,RATE,SUPPLIERNAME

UNION all

select 0 as Opstock, InvoiceItem.EntryDate,InvoiceItem.Invoiceretno as DOCNO,unit,qtyperunit,storeshop,'InvoiceReturns' as TrType,0 as Purchaseqty,isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as InvoiceRetQty,0 as TransQty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,RATE AS RATE,CUSTOMERNAME AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from InvoiceItem  INNER  JOIN INVOICE ON INVOICE.Invoiceretno=InvoiceItem.Invoiceretno AND INVOICE.SR=InvoiceItem.SR 
where InvoiceItem.entrydate>= @stockstartdate and InvoiceItem.entryDate between @fromdate and  @todate and InvoiceItem.SR='Returns'  and isnull(InvoiceItem.deleted,'N')='N'  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,InvoiceItem.EntryDate,InvoiceItem.Invoiceretno,unit,qtyperunit,storeshop,RATE,CUSTOMERNAME

UNION all

select 0 as Opstock, MaterialTransDetails.EntryDate,Documentno as DOCNO,unit,qtyperunit,storeshop,'MaterialTrans' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty, isnull(sum(qty),0) as Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from MaterialTransDetails   INNER JOIN MaterialTrans ON MaterialTransDetails.INVNO=MaterialTrans.INVNO  
WHERE ItemCode NOT IN ('SER','DLC')   and MaterialTransDetails.entrydate>=@stockstartdate and MaterialTransDetails.entryDate between @fromdate and  @todate and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,MaterialTransDetails.EntryDate,Documentno,unit,qtyperunit,storeshop

UNION all

select  0 as Opstock, PurchaseItem.EntryDate,PurchaseItem.PurchaseRet as DOCNO,unit,qtyperunit,storeshop,
'PurchaseReturns' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,isnull(sum(isnull(qty,0)),0) as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,RATE AS RATE,SUPPLIERNAME AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from PurchaseItem  INNER  JOIN PURCHASE ON PURCHASE.PurchaseRet=PURCHASEITEM.PurchaseRet AND PURCHASE.PR=PURCHASEITEM.PR
where PurchaseItem.entrydate>= @stockstartdate and  PurchaseItem.entryDate between @fromdate and  @todate and PurchaseItem.PR='Returns'  and isnull(PurchaseItem.deleted,'N')='N'  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by  ItemCode,PurchaseItem.entryDate,PurchaseItem.PurchaseRet,unit,qtyperunit,storeshop,rate,suppliername 

UNION all

select 0 as Opstock, InvoiceItem.EntryDate,InvoiceItem.INVNO as DOCNO,unit,qtyperunit,storeshop,'Invoice' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty, isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,RATE AS RATE,CUSTOMERNAME AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from InvoiceItem  INNER  JOIN INVOICE ON INVOICE.invno=InvoiceItem.invno AND INVOICE.SR=InvoiceItem.SR 
where InvoiceItem.entrydate>= @stockstartdate and  InvoiceItem.entryDate between @fromdate and  @todate and InvoiceItem.SR='Sales'  and isnull(InvoiceItem.deleted,'N')='N'  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,InvoiceItem.EntryDate,InvoiceItem.INVNO,unit,qtyperunit,storeshop,rate,customername 

UNION all

select 0 as Opstock, EntryDate,DNNO as DOCNO,unit,qtyperunit,storeshop,'DeliveryNote' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty, isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from DeliveryNoteItem
where DeliveryNoteItem.entrydate>= @stockstartdate and  entryDate between @fromdate and  @todate  and isnull(deleted,'N')='N'  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
and Status=0  

Group by ItemCode,EntryDate,DNNO,unit,qtyperunit,storeshop 

 UNION all

select 0 as Opstock, EntryDate,ProductionMasterItemFP.DOCNO as DOCNO,unit,qtyperunit,storeshop,'ProductionFinal' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty, sum(qty)as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from ProductionMasterItemFP  inner join ProductionEntryMaster on ProductionEntryMaster.docno=ProductionMasterItemFP.docno
WHERE ItemCode NOT IN ('SER','DLC') and EntryDate>=@stockstartdate and  entryDate between @fromdate and  @todate  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,EntryDate,ProductionMasterItemFP.DOCNO,unit,qtyperunit,storeshop 

union all

select 0 as Opstock, EntryDate,ProductionMasterItem.DOCNO as DOCNO,unit,qtyperunit,storeshop,'ProductionRaw' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,sum(qty) as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from ProductionMasterItem  inner join ProductionEntryMaster on ProductionEntryMaster.docno=ProductionMasterItem.docno 
WHERE ItemCode NOT IN ('SER','DLC')  and Entrydate>=@stockstartdate and  entryDate between @fromdate and  @todate  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,EntryDate,ProductionMasterItem.DOCNO,unit,qtyperunit,storeshop

union all

select 0 as Opstock, Date,damageItem.DOCNO as DOCNO,'Each'unit,1 as qtyperunit,storeshop,'Damage' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR, isnull(sum(qty),0) as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from damageItem
where damageItem.Date>= @stockstartdate and  Date between @fromdate and  @todate  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,Date,damageItem.DOCNO,storeshop 
 
UNION all
 
select 0 as Opstock, MaterialTransOutDetails.EntryDate,MaterialTransOutDetails.invno as DOCNO,unit,qtyperunit,storeshop,'TransferOut' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty, isnull(sum(qty),0)as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty from MaterialTransOutDetails  INNER JOIN MaterialTransOut ON MaterialTransOutDetails.INVNO=MaterialTransOut.INVNO 
WHERE ItemCode NOT IN ('SER','DLC') and MaterialTransOutDetails.entrydate>= @stockstartdate  and MaterialTransOutDetails.entrydate between @fromdate and  @todate   AND FROMSTORE IN (SELECT NAME FROM BRANCHMASTER )  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,MaterialTransOutDetails.EntryDate,MaterialTransOutDetails.invno,unit,qtyperunit,storeshop 
  
UNION all

select 0 as Opstock, EntryDate,DOCNO as DOCNO,unit,qtyperunit,storeshop,'AdjQty' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty, isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from QtyAdjustmentDetails
Where entryDate between @fromdate and  @todate  and entryDate>=@stockstartdate    and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,EntryDate,DOCNO,unit,qtyperunit,storeshop 

UNION ALL


select 0 as Opstock, InternalMaterialTransDetails.EntryDate,Documentno as DOCNO,unit,qtyperunit,storeshop,'IntMaterialTrans' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0  as Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME, isnull(sum(qty),0) as IntTransqty,0 as IntTransOutqty from InternalMaterialTransDetails   INNER JOIN InternalMaterialTrans ON InternalMaterialTransDetails.INVNO=InternalMaterialTrans.INVNO  
WHERE ItemCode NOT IN ('SER','DLC')   and InternalMaterialTransDetails.entrydate>=@stockstartdate and InternalMaterialTransDetails.entryDate between @fromdate and  @todate and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(TOSTORE,'')=isnull(@StoreShop,isnull(TOSTORE,''))
Group by ItemCode,InternalMaterialTransDetails.EntryDate,Documentno,unit,qtyperunit,storeshop

UNION all
 
select 0 as Opstock, InternalMaterialTransDetails.EntryDate,InternalMaterialTransDetails.invno as DOCNO,unit,qtyperunit,storeshop,'IntTransferOut' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty, 0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0  as IntTransQty, isnull(sum(qty),0)as IntTransOutqty from InternalMaterialTransDetails  INNER JOIN InternalMaterialTrans ON InternalMaterialTransDetails.INVNO=InternalMaterialTrans.INVNO 
WHERE ItemCode NOT IN ('SER','DLC') and InternalMaterialTransDetails.entrydate>= @stockstartdate  and InternalMaterialTransDetails.entrydate between @fromdate and  @todate   AND FROMSTORE IN (SELECT NAME FROM StoreMaster )  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(FROMSTORE,'')=isnull(@StoreShop,isnull(FROMSTORE,''))
Group by ItemCode,InternalMaterialTransDetails.EntryDate,InternalMaterialTransDetails.invno,unit,qtyperunit,storeshop 
  

   )a
   group by EntryDate,DOCNO,unit,qtyperunit,storeshop,TrType,rate,custsupname 
   order by entrydate,DOCNO
END
else
begin

select   [dbo].[SP_OpstockNewMethod]  (@fromdate,@Itemcode,@StoreShop) as opstock,EntryDate,DOCNO,unit,qtyperunit,storeshop,TrType,sum(Purchaseqty) as Purchaseqty,sum(InvoiceRetQty) as InvoiceRetQty,sum(TransQty) as TransQty,sum(PurchaseRetqty) as PurchaseRetqty,sum(Invoiceqty) as Invoiceqty,
sum(Deliveryqty) as Deliveryqty,sum(ProductionQtyF) as ProductionQtyF,sum(productionQtyR) as productionQtyR,sum(Damageqty) as Damageqty,sum(TransOutqty) as TransOutqty,sum(AdjQty) as AdjQty  from (
select  0 as Opstock,@stockstartdate as  EntryDate,'Opstock' as DOCNO,'Each' as unit,1 as qtyperunit,@StoreShop as storeshop,'Opstock' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as TransQty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty   from item 
WHERE  isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) 

union all

select 0 as Opstock, PurchaseItem.EntryDate,PurchaseItem.Documentno as DOCNO,unit,qtyperunit,storeshop,'Purchase' as TrType
,isnull(sum(isnull(qty,0)),0) as Purchaseqty,0 as  InvoiceRetQty,0 as TransQty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,RATE AS RATE,SUPPLIERNAME AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from PurchaseItem  inner join PURCHASE ON PURCHASE.DOCUMENTNO=PURCHASEITEM.DOCUMENTNO AND PURCHASE.PR=PURCHASEITEM.PR
where PurchaseItem.entrydate>= @stockstartdate and  PurchaseItem.entryDate between @fromdate and  @todate and PurchaseItem.PR='Purchase'  and isnull(PurchaseItem.deleted,'N')='N' and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,PurchaseItem.EntryDate,PurchaseItem.Documentno,unit,qtyperunit,storeshop,RATE,SUPPLIERNAME

UNION all

select 0 as Opstock, InvoiceItem.EntryDate,InvoiceItem.Invoiceretno as DOCNO,unit,qtyperunit,storeshop,'InvoiceReturns' as TrType,0 as Purchaseqty,isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as InvoiceRetQty,0 as TransQty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,RATE AS RATE,CUSTOMERNAME AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from InvoiceItem INNER  JOIN INVOICE ON INVOICE.Invoiceretno=InvoiceItem.Invoiceretno AND INVOICE.SR=InvoiceItem.SR 
where InvoiceItem.entrydate>= @stockstartdate and InvoiceItem.entryDate between @fromdate and  @todate and InvoiceItem.SR='Returns'  and isnull(InvoiceItem.deleted,'N')='N'  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,InvoiceItem.EntryDate,InvoiceItem.Invoiceretno,unit,qtyperunit,storeshop,rate,customername

UNION all

select 0 as Opstock, MaterialTransDetails.EntryDate,Documentno as DOCNO,unit,qtyperunit,storeshop,'MaterialTrans' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty, isnull(sum(qty),0) as Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from MaterialTransDetails   INNER JOIN MaterialTrans ON MaterialTransDetails.INVNO=MaterialTrans.INVNO  
WHERE ItemCode NOT IN ('SER','DLC')   and MaterialTransDetails.entrydate>=@stockstartdate and MaterialTransDetails.entryDate between @fromdate and  @todate and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,MaterialTransDetails.EntryDate,Documentno,unit,qtyperunit,storeshop

UNION all

select  0 as Opstock, EntryDate,PurchaseRet as DOCNO,unit,qtyperunit,storeshop,'PurchaseReturns' as TrType,0 as Purchaseqty,
0 as  InvoiceRetQty,0 as  Transqty,isnull(sum(isnull(qty,0)),0) as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from PurchaseItem
where PurchaseItem.entrydate>= @stockstartdate and  entryDate between @fromdate and  @todate and PR='Returns'  and isnull(deleted,'N')='N'  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by  ItemCode,EntryDate,PurchaseRet,unit,qtyperunit,storeshop 

UNION all

select 0 as Opstock, EntryDate,INVNO as DOCNO,unit,qtyperunit,storeshop,'Invoice' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty, isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from InvoiceItem
where InvoiceItem.entrydate>= @stockstartdate and  entryDate between @fromdate and  @todate and SR='Sales'  and isnull(deleted,'N')='N'  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,EntryDate,INVNO,unit,qtyperunit,storeshop 

--UNION all

--select 0 as Opstock, EntryDate,DNNO as DOCNO,unit,qtyperunit,storeshop,'DeliveryNote' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty, 0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty from DeliveryNoteItem
--where DeliveryNoteItem.entrydate>= @stockstartdate and  entryDate between @fromdate and  @todate  and isnull(deleted,'N')='N'  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
--Group by ItemCode,EntryDate,DNNO,unit,qtyperunit,storeshop 

 UNION all

select 0 as Opstock, EntryDate,ProductionMasterItemFP.DOCNO as DOCNO,unit,qtyperunit,storeshop,'ProductionFinal' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty, sum(qty)as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from ProductionMasterItemFP  inner join ProductionEntryMaster on ProductionEntryMaster.docno=ProductionMasterItemFP.docno
WHERE ItemCode NOT IN ('SER','DLC') and EntryDate>=@stockstartdate and  entryDate between @fromdate and  @todate  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,EntryDate,ProductionMasterItemFP.DOCNO,unit,qtyperunit,storeshop 

union all

select 0 as Opstock, EntryDate,ProductionMasterItem.DOCNO as DOCNO,unit,qtyperunit,storeshop,'ProductionRaw' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,sum(qty) as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty ,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from ProductionMasterItem  inner join ProductionEntryMaster on ProductionEntryMaster.docno=ProductionMasterItem.docno 
WHERE ItemCode NOT IN ('SER','DLC')  and Entrydate>=@stockstartdate and  entryDate between @fromdate and  @todate  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,EntryDate,ProductionMasterItem.DOCNO,unit,qtyperunit,storeshop

union all

select 0 as Opstock, Date,damageItem.DOCNO as DOCNO,'Each'unit,1 as qtyperunit,storeshop,'Damage' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR, isnull(sum(qty),0) as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from damageItem
where damageItem.Date>= @stockstartdate and  Date between @fromdate and  @todate  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,Date,damageItem.DOCNO,storeshop 
 
UNION all
 
select 0 as Opstock, MaterialTransOutDetails.EntryDate,MaterialTransOutDetails.invno as DOCNO,unit,qtyperunit,storeshop,'TransferOut' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty, isnull(sum(qty),0)as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty  from MaterialTransOutDetails  INNER JOIN MaterialTransOut ON MaterialTransOutDetails.INVNO=MaterialTransOut.INVNO 
WHERE ItemCode NOT IN ('SER','DLC') and MaterialTransOutDetails.entrydate>= @stockstartdate  and MaterialTransOutDetails.entrydate between @fromdate and  @todate   AND FROMSTORE IN (SELECT NAME FROM BRANCHMASTER )  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,MaterialTransOutDetails.EntryDate,MaterialTransOutDetails.invno,unit,qtyperunit,storeshop 
  
UNION all

select 0 as Opstock, EntryDate,DOCNO as DOCNO,unit,qtyperunit,storeshop,'AdjQty' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty, isnull(sum(isnull(qty,0)*isnull(qtyperunit,1)),0) as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0 as IntTransqty,0 as IntTransOutqty   from QtyAdjustmentDetails
Where entryDate between @fromdate and  @todate  and entryDate>=@stockstartdate    and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(StoreShop,'')=isnull(@StoreShop,isnull(StoreShop,''))
Group by ItemCode,EntryDate,DOCNO,unit,qtyperunit,storeshop 
UNION ALL


select 0 as Opstock, InternalMaterialTransDetails.EntryDate,Documentno as DOCNO,unit,qtyperunit,storeshop,'IntMaterialTrans' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0  as Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty,0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME, isnull(sum(qty),0) as IntTransqty,0 as IntTransOutqty from InternalMaterialTransDetails   INNER JOIN InternalMaterialTrans ON InternalMaterialTransDetails.INVNO=InternalMaterialTrans.INVNO  
WHERE ItemCode NOT IN ('SER','DLC')   and InternalMaterialTransDetails.entrydate>=@stockstartdate and InternalMaterialTransDetails.entryDate between @fromdate and  @todate and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(TOSTORE,'')=isnull(@StoreShop,isnull(TOSTORE,''))
Group by ItemCode,InternalMaterialTransDetails.EntryDate,Documentno,unit,qtyperunit,storeshop

UNION all
 
select 0 as Opstock, InternalMaterialTransDetails.EntryDate,InternalMaterialTransDetails.invno as DOCNO,unit,qtyperunit,storeshop,'IntTransferOut' as TrType,0 as Purchaseqty,0 as  InvoiceRetQty,0 as  Transqty,0 as PurchaseRetqty,0 as Invoiceqty,0 as Deliveryqty,0 as ProductionQtyF,0 as productionQtyR,0 as Damageqty, 0 as TransOutqty,0 as AdjQty,0 AS RATE,'' AS CUSTSUPNAME,0  as IntTransQty, isnull(sum(qty),0)as IntTransOutqty from InternalMaterialTransDetails  INNER JOIN InternalMaterialTrans ON InternalMaterialTransDetails.INVNO=InternalMaterialTrans.INVNO 
WHERE ItemCode NOT IN ('SER','DLC') and InternalMaterialTransDetails.entrydate>= @stockstartdate  and InternalMaterialTransDetails.entrydate between @fromdate and  @todate   AND FROMSTORE IN (SELECT NAME FROM StoreMaster )  and isnull(itemcode,'')=isnull(@Itemcode,isnull(itemcode,'')) and isnull(FROMSTORE,'')=isnull(@StoreShop,isnull(FROMSTORE,''))
Group by ItemCode,InternalMaterialTransDetails.EntryDate,InternalMaterialTransDetails.invno,unit,qtyperunit,storeshop 
  
   )a
   group by EntryDate,DOCNO,unit,qtyperunit,storeshop,TrType ,RATE,CUSTSUPNAME
   order by entrydate,DOCNO
END
end