SAP Query Manager

 Q CREDIT NOTE DETAILS(VENDOR ALL COMPANY )?

SELECT 'COMPACT' AS [COMPANY],T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal]-T0.[VatSum]-t0.RoundDif+t0.DiscSum as [Total], T0.[VatSum], t0.RoundDif, T0.[DocTotal], t0.Comments FROM [Compact_Live1].[dbo].[ORPC]  T0
where t0.[docdate] >=[%0] and t0.[docdate] <=[%1]
UNION ALL
SELECT 'KANIK' AS [COMPANY], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal]-T0.[VatSum]-t0.RoundDif+t0.DiscSum as [Total], T0.[VatSum], t0.RoundDif, T0.[DocTotal], t0.Comments FROM [Kanik_Live1].[dbo].[ORPC]  T0
where t0.[docdate] >=[%0] and t0.[docdate] <=[%1]
UNION ALL
SELECT 'DELTA' AS [COMPANY], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal]-T0.[VatSum]-t0.RoundDif+t0.DiscSum as [Total], T0.[VatSum], t0.RoundDif, T0.[DocTotal], t0.Comments FROM [DELTA_LIVE].[dbo].[ORPC]  T0
where t0.[docdate] >=[%0] and t0.[docdate] <=[%1]
UNION ALL
SELECT 'SMART' AS [COMPANY], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal]-T0.[VatSum]-t0.RoundDif+t0.DiscSum as [Total], T0.[VatSum], t0.RoundDif, T0.[DocTotal], t0.Comments FROM [SMART_LIVE].[dbo].[ORPC]  T0
where t0.[docdate] >=[%0] and t0.[docdate] <=[%1]
_______________________________________________________________________________________

Q CREDIT NOTE DETAILS ?

SELECT T0.[DocNum], T0.[Numatcard], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[Price], T1.[LineTotal], T1.[VatSum],T0.[DocTotal], T0.[Comments], T0.[Ref1], T0.[Ref2]
FROM ORIN T0
LEFT JOIN RIN1 T1 ON T0.DOCENTRY=T1.DOCENTRY
LEFT JOIN OCRD T2 ON T0.CARDCODE=T2.CARDCODE
LEFT JOIN OCRG T3 ON T2.GroupCode=T3.GroupCode
WHERE T0.DOCDATE>='[%0]' AND T0.DOCDATE<='[%1]' AND T3.GroupName='[%2]' AND T0.CANCELED='N'

__________________________________________________________________________________________
Q Open sales order warehouse wise ?

SELECT T1.[CardCode] AS 'Customer/Vendor Code', T1.[CardName] AS 'Customer/Vendor Name', T0.[Quantity] AS 'Quantity',
T0.[ItemCode] AS 'Item No.', T0.[Dscription] AS 'Item/Service Description', T0.[WhsCode] AS 'Warehouse Code',
T0.[OpenQty] AS 'Remaining Open Quantity', T0.[DocEntry] AS 'Document Internal ID'
FROM  [dbo].[RDR1] T0 
INNER  JOIN [dbo].[ORDR] T1  ON  T1.[DocEntry] = T0.[DocEntry]  
WHERE T0.[OpenQty] > (0 ) 
AND  T0.[WhsCode] = '[%0]'

____________________________________________________________________________________
 Q Open sales order ?

 SELECT T1.[CardCode] AS 'Customer/Vendor Code', T1.[CardName] AS 'Customer/Vendor Name', T0.[Quantity] AS 'Quantity',
T0.[ItemCode] AS 'Item No.', T0.[Dscription] AS 'Item/Service Description', T0.[WhsCode] AS 'Warehouse Code',
T0.[OpenQty] AS 'Remaining Open Quantity', T0.[DocEntry] AS 'Document Internal ID'
FROM  [dbo].[RDR1] T0
INNER  JOIN [dbo].[ORDR] T1  ON  T1.[DocEntry] = T0.[DocEntry] 
WHERE T0.[OpenQty] > (0 )
AND  T0.[WhsCode] >= '[%0]'  AND  T0.[WhsCode] <= '[%1]' AND T1.[CARDNAME] >= '[%2]'  AND  T1.[CARDNAME] <= '[%3]'

 _____________________________________________________________________________________
Q A/P Invoice-Location Wise ?

SELECT T0.DOCDATE, T0.DocType,T0.[CANCELED],T0.TaxDate AS 'Doc Date',  T0.DocNum as 'A/P Invoice No.', T3.DocNum as 'GRN No.', T0.CARDCODE, T0.CARDNAME,t0.numatcard, T1.taxcode,T0.DocTotal - T0.[DiscSum] - T0.VatSum - T0.[TotalExpns] - T0.[WTSum] - T0.[RoundDif] as 'Base Amount', T0.[DiscSum], T0.VatSum, T0.[TotalExpns], T0.[WTSum], T0.[RoundDif], T0.DocTotal, T2.Location,
t5.transcat ,T0.[Comments]

FROM OPCH T0 INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
left outer join pch12 t4 on t0.docentry = t4.docentry
left outer join otnc t5 on t4.transcat = t5.transcat
LEFT OUTER JOIN OLCT T2 ON T1.LocCode = T2.Code
LEFT OUTER JOIN OPDN T3 ON T1.BaseEntry = T3.DocEntry

WHERE (T0.DOCDATE >= [%0] AND T0.DOCDATE <= [%1])
AND T2.CODE = [%2]

GROUP BY T0.DOCDATE, T0.DocNum, T3.DocNum, T0.CARDCODE, T0.CARDNAME,t0.numatcard, T1.taxcode,T0.DocTotal, T0.VatSum, T2.Location,t5.transcat,T0.TaxDate, T0.[DiscSum], T0.VatSum, T0.[TotalExpns], T0.[WTSum], T0.[RoundDif], T0.DocType,T0.[CANCELED], T0.[Comments]


______________________________________________________________________________________

Q Credit limit For available For Sale ?

select ocrd.cardcode,ocrd.cardname, ocrg.groupname, ocrd.balance,ocrd.creditline, ocrd.creditline-ocrd.balance as [Available for Sale] from ocrd
inner join ocrg on ocrd.GroupCode=ocrg.GroupCode
where cardtype='C' and balance >0

_____________________________________________________________________________________________

Q Identify Item code In BOM ?

SELECT T0.[Code], T2.[ITEMNAME], T0.[Father], T1.[ITEMNAME],T0.[Quantity], T3.[ToWh] FROM ITT1 T0
left join oitm t1 on t0.father=t1.itemcode
left join oitm t2 on t0.code=t2.itemcode
left join oitt t3 on t0.father=t3.code
where t0.code='[%0]'
__________________________________________________________________________________________
Q Ageing Report For Customer wise ?
select T1.cardcode 'Bp Code',T1.cardname 'Name',sysdeb 'Debit Amount',syscred 'Credit Amount',
(T0.BALDUEDEB - T0.BALDUECRED) as 'Balance Due',
case T0.transtype
when '13' then 'INV'
when '14' then 'AR CN'
when '24' then 'INCOMING'
else 'Other'
end 'Type',
Ref1,
fccurrency 'BP Currency',
CONVERT(VARCHAR(10), refdate, 103) 'Posting Date',
CONVERT(VARCHAR(10), duedate, 103) 'Due Date',
CONVERT(VARCHAR(10), taxdate, 103) 'Doc Date' ,
CASE when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31 then
case
when BALDUECRED <> 0 then -BALDUECRED
else BALDUEDEB
end
end "0-30 days",
case when ((datediff(dd,refdate,current_timestamp))+1 > 30
and (datediff(dd,refdate,current_timestamp))+1< 61)
then
case
when BALDUECRED <> 0 then -BALDUECRED
else BALDUEDEB
end
end "31 to 60 days",
case when ((datediff(dd,refdate,current_timestamp))+1 > 60
and (datediff(dd,refdate,current_timestamp))+1< 91)
then case when BALDUECRED <> 0 then -BALDUECRED else BALDUEDEB  end end "61 to 90 days",
CASE
when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90
then
case
when BALDUECRED= 0 then BALDUEDEB
when BALDUEDEB= 0 then -BALDUECRED
end
end "90 + days"
from dbo.JDT1 T0
INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'
where T0.intrnmatch = '0' and T0.BALDUEDEB != T0.BALDUECRED and t1.CardCode='[%0]'
ORDER BY T1.CARDCODE, T0.taxdate

____________________________________________________________________________________________
Q Sales as per Trial Balance ?

Select T0.REF1,T0.TAXDATE,
P1.DOCENTRY,
P1.CARDCODE,
P1.CARDNAME,
P1.doctotal-p1.VatSum+p1.DiscSum [GROSS SALE],
P1.VatSum,
P1.DiscSum,
P1.DocTotal,
T0.Account,T1.AcctName,T0.Debit  - T0.Credit AS 'Amount in Trial Balance'
 From JDT1 T0
 INNER JOIN OACT T1 ON T0.Account=T1.AcctCode
 left JOIN OINV P1 ON T0.Ref1=P1.DocNum AND T0.TaxDate=P1.DocDate
 Left Outer Join OACG T2 ON  T1.Category    = T2.AbsId And    T2.[Source]    = 'P'
 WHERE T1.AcctCode='R14000' AND T0.RefDate  between '[%0]' AND '[%1]' AND T1.GroupMask IN (4,5)
__________________________________________________________________________________________________
Q Top 10 invoice ?
SELECT TOP 10 T0.[DoceNTRY], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM OINV T0 WHERE T0.[DocDate] between [%0] and [%1] ORDER BY T0.[DocTotal] desc
_________________________________________________________________________________________________
Q Last Purchase Price ?
SELECT T0.ItemCode,t0.dscription, MAX(T0.DocDate) 'Latest', (SELECT MAX(Price) FROM PCH1 WHERE ItemCode=T0.ItemCode AND DocDate= MAX(T0.DocDate)) 'Last Price'
FROM PCH1 T0
Group By T0.ItemCode, t0.dscription having itemcode is not null
_____________________________________________________________________________________________
Q Ordered By Business Partners?

SELECT T0.DocDate, T0.DocDueDate, T0.DocNum, T2.Name, T1.SlpName,    (CASE WHEN T0.DocStatus = 'O' THEN 'Open' ELSE 'Closed' END) AS Status,    T0.DocTotal, T3.GrossProfit    FROM ORDR T0 LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode LEFT JOIN OCPR T2 ON T0.CntctCode = T2.CntctCode LEFT JOIN    (SELECT DocEntry, SUM(GrssProfit) AS GrossProfit FROM RDR1 GROUP BY DocEntry) T3 ON T0.DocEntry = T3.DocEntry    WHERE T0.CardCode = N'[%0]' ORDER BY T0.DocNum DESC

_________________________________________________________________________________________________
Q Total Sale of company ?
DECLARE @DATEFROM VARCHAR(20)
DECLARE @DATETO VARCHAR(20)
SELECT @DATEFROM = DOCDATE FROM OINV T0 WHERE T0.DOCDATE = '[%0]'
SELECT @DATETO = DOCDATE FROM OINV T0 WHERE T0.DOCDATE = '[%1]'
EXEC SAT_Consolidate_Total_Sale_Report @DATEFROM,@DATETO

_______________________________________________________________________________________________