1.
SAP INVENTORY QUERY IN & OUT
<---------------------------------------Choose from date,To date and WareHouse--------------------------> Declare @FromDate Datetime Declare @ToDate Datetime Declare @Whse nvarchar(10) select @FromDate = min([Link]) from [Link] S0 where [Link] >='[%0]' select @ToDate = max([Link]) from [Link] s1 where [Link] <='[%1]' select @Whse = Max([Link]) from [Link] S2 Where [Link] = '[%2]' Select @Whse as 'Warehouse', [Link], max([Link]) as ItemName, sum([Link]) as OpeningBalance, sum([Link]) as 'IN', sum([Link]) as OUT, ((sum([Link]) + sum([Link])) - Sum([Link])) as Closing , (Select [Link] from OITM t where [Link]=[Link]) as LastPurPrice, (Select [Link] from OITM i where [Link]=[Link]) as UOM from ( Select [Link], [Link], [Link], (sum([Link])-sum([Link])) as OpeningBalance, 0 as INq, 0 as OUT From [Link] N1 Where [Link] < @FromDate and [Link] = @Whse Group By [Link],[Link],[Link] Union All select [Link], [Link], [Link], 0 as OpeningBalance, sum([Link]) , 0 as OUT From [Link] N1 Where [Link] >= @FromDate and [Link] <= @ToDate and [Link] >0 and [Link] = @Whse Group By [Link],[Link],[Link] Union All select [Link], [Link], [Link], 0 as OpeningBalance, 0 , sum([Link]) as OUT From [Link] N1 Where [Link] >= @FromDate and [Link] <=@ToDate and [Link] >0 and [Link] = @Whse Group By [Link],[Link],[Link]) a, [Link] I1 where [Link]=[Link] Group By [Link] Having sum([Link]) + sum([Link]) + sum([Link]) > 0 Order By [Link] <---------------------------------------To Filter WareHouse in above report--------------------------> Declare @FromDate Datetime Declare @ToDate Datetime Declare @Whse nvarchar(10) select @FromDate = min([Link]) from [Link] S0 where [Link] >='[%0]' select @ToDate = max([Link]) from [Link] s1 where [Link] <='[%1]' select @Whse = Max([Link]) from [Link] S2 Where [Link] NOT IN ('01','02') Select @Whse as 'Warehouse', [Link], max([Link]) as ItemName, sum([Link]) as OpeningBalance, sum([Link]) as 'IN', sum([Link]) as OUT, ((sum([Link]) + sum([Link])) - Sum([Link])) as Closing , (Select [Link] from OITM t where [Link]=[Link]) as LastPurPrice, (Select [Link] from OITM i where [Link]=[Link]) as UOM from ( Select [Link], [Link], [Link], (sum([Link])-sum([Link])) as OpeningBalance, 0 as INq, 0 as OUT From [Link] N1
Where [Link] < @FromDate and [Link] = @Whse Group By [Link],[Link],[Link] Union All select [Link], [Link], [Link], 0 as OpeningBalance, sum([Link]) , 0 as OUT From [Link] N1 Where [Link] >= @FromDate and [Link] <= @ToDate and [Link] >0 and [Link] = @Whse Group By [Link],[Link],[Link] Union All select [Link], [Link], [Link], 0 as OpeningBalance, 0 , sum([Link]) as OUT From [Link] N1 Where [Link] >= @FromDate and [Link] <=@ToDate and [Link] >0 and [Link] = @Whse Group By [Link],[Link],[Link]) a, [Link] I1 where [Link]=[Link] Group By [Link] Having sum([Link]) + sum([Link]) + sum([Link]) > 0 Order By [Link]
2.
Purchase-GRPO-Applied Amount-SQL query
SELECT --[Link], MAX([Link]) as ' PO NO', [Link] as ' PO NO Navigation', [Link] as 'PoDATE', [Link] as 'Supplier Name', [Link] as 'POItem', [Link] as 'PoQty', [Link] as 'POPrice', [Link] as 'POTot', MAX([Link]) as 'GRN No', [Link] as 'GRN No Navigation', [Link] as 'GRN Date', [Link] as 'Supplier Reference No', [Link] as 'GRN Item', [Link] as 'GRN Qty', [Link] as 'GRN Price', [Link] as 'GRN Cur', MAX([Link]) as 'INV No', [Link] as 'INV No Navigation', [Link] as 'INV Date', [Link] as 'Supplier Reference No', [Link] as 'INV Qty', [Link] as 'INV Price', [Link] as 'INV Cur', [Link] as 'INV Tot', [Link] as 'Paid Sum', ([Link] - [Link]) as 'Balance Due', --[Link] AS 'CheckNo', --[Link] as 'CheckDate', --[Link] as 'CheckSum',
--[Link] as 'Cash Amount', [Link] AS ' PO Document Status' FROM POR1 T0 INNER JOIN PDN1 T1 ON [Link] = [Link] AND [Link] = [Link] AND [Link] = [Link] AND [Link] = [Link] INNER JOIN OPOR T2 ON [Link] = [Link] INNER JOIN PCH1 T3 ON [Link] = [Link] AND [Link] = [Link] AND [Link] = [Link] AND [Link] = [Link] INNER JOIN OPDN T4 ON [Link] = [Link] INNER JOIN OPCH T5 ON [Link] = [Link] left outer JOIN OPRJ T6 ON [Link] = [Link] LEFT OUTER join VPM1 T7 on [Link] = [Link] LEFT OUTER JOIN OVPM T8 on [Link] = [Link] WHERE T0.[DocDate] >='[%0]' and T0.[DocDate] <='[%1]' GROUP BY --[Link], [Link], [Link], [Link], [Link], [Link], [Link], [Link], [Link],
[Link] Register Query:
SELECT [Link] AS 'AP Inv. #', [Link] as 'Date', [Link] as 'Vendor Name', [Link] as'Vendor Ref No',[Link] as 'Currency', (Select Sum(LineTotal) FROM PCH1 L Where [Link]=[Link]) as 'Base Amt.(Rs.)', (SELECT Sum(TaxSum) FROM PCH4 where statype=-90 and DocEntry=[Link]) as 'BED (Rs.)', (SELECT Sum(TaxSum) FROM PCH4 where statype=-60 and DocEntry=[Link]) as 'Cess (Rs.)', (SELECT Sum(TaxSum) FROM PCH4 where statype=-55 and DocEntry=[Link]) as 'HECess (Rs.)', (SELECT Sum(TaxSum) FROM PCH4 where statype in ('1','10','11') and DocEntry=[Link]) as ' VAT (Rs.) ', (SELECT Sum(TaxSum) FROM PCH4 where statype in ('4','12') and DocEntry=[Link]) as ' CST (Rs.) ', (SELECT Sum(TaxSum) FROM PCH4 where statype=5 and DocEntry=[Link]) as ' [Link] (Rs.) ', (SELECT Sum(TaxSum) FROM PCH4 where statype=6 and DocEntry=[Link]) as 'Cess_ST(Rs.)', (SELECT Sum(TaxSum) FROM PCH4 where statype=-10 and DocEntry=[Link]) as 'HECess_ST(Rs.)', (Select Sum(LineTotal) From PCH3 Q Where [Link]=[Link]) AS 'Freight (Rs.)', [Link] AS 'TDS (Rs.)',
[Link] as 'Total (Rs.)' FROM OPCH M LEFT OUTER JOIN PCH1 L on [Link]=[Link] LEFT OUTER JOIN PCH4 T on [Link]=[Link] and [Link]=[Link] LEFT OUTER JOIN PCH5 J ON [Link] = [Link] LEFT OUTER JOIN PCH3 Q ON [Link] = [Link] WHERE ([Link] >= '[%0]' AND [Link] <= '[%1]') AND [Link]<>'19' GROUP BY [Link],[Link],[Link],[Link],[Link],[Link],[Link],[Link],M. DocTotal ORDER BY [Link],[Link],[Link],[Link],[Link],[Link],[Link],[Link],M. DocTotal
[Link] Register Query:
SELECT [Link] AS 'AR Inv. #', [Link] as 'Date', [Link] as 'Customer Name', [Link] as'Vendor Ref No', (Select Sum(LineTotal) FROM inv1 L Where [Link]=[Link]) as 'Base Amt.(Rs.)', (SELECT Sum(TaxSum) FROM inv4 where statype=-90 and DocEntry=[Link]) as 'BED (Rs.)', (SELECT Sum(TaxSum) FROM inv4 where statype=-60 and DocEntry=[Link]) as 'Cess (Rs.)', (SELECT Sum(TaxSum) FROM inv4 where statype=-55 and DocEntry=[Link]) as 'HECess (Rs.)', (SELECT Sum(TaxSum) FROM inv4 where statype in ('1','10','11') and DocEntry=[Link]) as ' VAT (Rs.) ', (SELECT Sum(TaxSum) FROM inv4 where statype in ('4','12') and DocEntry=[Link]) as ' CST (Rs.) ', (SELECT Sum(TaxSum) FROM inv4 where statype=5 and DocEntry=[Link]) as ' [Link] (Rs.) ', (SELECT Sum(TaxSum) FROM inv4 where statype=6 and DocEntry=[Link]) as 'Cess_ST(Rs.)', (SELECT Sum(TaxSum) FROM inv4 where statype=-10 and DocEntry=[Link]) as 'HECess_ST(Rs.)', (Select Sum(LineTotal) From inv3 Q Where [Link]=[Link]) AS 'Freight (Rs.)', [Link] AS 'TDS (Rs.)', [Link] as 'Total (Rs.)' FROM OINV M LEFT OUTER JOIN inv1 L on [Link]=[Link] LEFT OUTER JOIN inv4 T on [Link]=[Link] and [Link]=[Link] LEFT OUTER JOIN inv5 J ON [Link] = [Link] LEFT OUTER JOIN inv3 Q ON [Link] = [Link] WHERE ([Link] >= '[%0]' AND [Link] <= '[%1]') AND [Link]<>'14' GROUP BY [Link],[Link],[Link],[Link],[Link],[Link],[Link],[Link] ORDER BY [Link],[Link],[Link],[Link],[Link],[Link],[Link],[Link]