SAP QUERY GENERATOR :-
Q OPEN PURCHASE ORDER?
ANS:-SELECT T0.[DocEntry], T0.[DocDate], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T1.[Price],T1.WHSCODE FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[OpenQty] >=1 AND T0.DOCDATE >=[%0] AND T0.DOCDATE <=[%1]
________________________________________________________________________________________
Q Totala sale- Segregated?
Ans:- 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_Segregated_Total_Sale @DATEFROM,@DATETO
_______________________________________________________________________________
Q Group Sales Details?
Ans:-DECLARE @GROUPNAME VARCHAR(50)
DECLARE @DATEFROM DATETIME
DECLARE @DATETO DATETIME
SET @GROUPNAME='Sigma Group'
SET @DATEFROM='20141101'
SET @DATETO='20141130'
select 'COMPACT SALES' as DETAIL, t3.GroupName, t1.itemcode, t1.Dscription,t4.U_Subgrp01, sum(t1.quantity) AS [QUANTITY] from COMPACT_LIVE1..oinv t0
inner join COMPACT_LIVE1..INV1 t1 on t0.DocEntry=t1.DocEntry
left join COMPACT_LIVE1..ocrd t2 on t0.cardcode=t2.CardCode
left join COMPACT_LIVE1..ocrg t3 on t2.GroupCode=t3.GroupCode
left join COMPACT_LIVE1..oitm t4 on t1.itemcode=t4.itemcode
group by t1.Quantity,t1.itemcode,t4.U_Subgrp01,T0.CANCELED, t1.Dscription,t0.DocDate,t3.GroupName
having t3.GroupName=@GROUPNAME and t0.docdate >=@DATEFROM and t0.docdate <=@DATETO and t0.CANCELED='N'
union all
select 'COMPACT SALES RETURN' as DETAIL, t3.GroupName,t1.itemcode,t1.Dscription,t4.U_Subgrp01, -1*sum(t1.quantity) AS [QUANTITY] from COMPACT_LIVE1..ORIN t0
inner join COMPACT_LIVE1..rin1 t1 on t0.DocEntry=t1.DocEntry
left join COMPACT_LIVE1..ocrd t2 on t0.cardcode=t2.CardCode
left join COMPACT_LIVE1..ocrg t3 on t2.GroupCode=t3.GroupCode
left join COMPACT_LIVE1..oitm t4 on t1.itemcode=t4.itemcode
group by t1.Quantity,t1.itemcode,T0.CANCELED, t4.U_Subgrp01, t1.Dscription,t0.DocDate,t3.GroupName
having t3.GroupName=@GROUPNAME and t0.docdate >=@DATEFROM and t0.docdate <=@DATETO and t0.CANCELED='N'
UNION ALL
select 'SMART SALES' as DETAIL, t3.GroupName, t1.itemcode, t1.Dscription,'CFL HW' 'U_Subgrp01', sum(t1.quantity) AS [QUANTITY] from Smart_Live..oinv t0
inner join Smart_Live..inv1 t1 on t0.DocEntry=t1.DocEntry
left join Smart_Live..ocrd t2 on t0.cardcode=t2.CardCode
left join Smart_Live..ocrg t3 on t2.GroupCode=t3.GroupCode
left join Smart_Live..oitm t4 on t1.itemcode=t4.itemcode
group by t1.Quantity,t1.itemcode,t4.U_Subgrp01,T0.CANCELED, t1.Dscription,t0.DocDate,t3.GroupName
having t3.GroupName=@GROUPNAME and t0.docdate >=@DATEFROM and t0.docdate <=@DATETO and t0.CANCELED='N'
union all
select 'SMART SALES RETURN' as DETAIL,t3.GroupName,t1.itemcode,t1.Dscription,'CFL HW' 'U_Subgrp01', -1*sum(t1.quantity) AS [QUANTITY] from Smart_Live..ORIN t0
inner join Smart_Live..rin1 t1 on t0.DocEntry=t1.DocEntry
left join Smart_Live..ocrd t2 on t0.cardcode=t2.CardCode
left join Smart_Live..ocrg t3 on t2.GroupCode=t3.GroupCode
left join Smart_Live..oitm t4 on t1.itemcode=t4.itemcode
group by t1.Quantity,t1.itemcode,T0.CANCELED, t4.U_Subgrp01, t1.Dscription,t0.DocDate,t3.GroupName
having t3.GroupName=@GROUPNAME and t0.docdate >=@DATEFROM and t0.docdate <=@DATETO and t0.CANCELED='N'
___________________________________________________________________________________________
Q Payment Terms Customer ?
Ans:-select 'SMART' COMPANY, t0.CardCode, t0.CardName , t0.Balance,t1.GroupNum,t1.PymntGroup from OCRD t0
inner join OCTG t1 on t0.GroupNum=t1.GroupNum
where t0.Balance > 0 and T0.CARDTYPE='C' and CONVERT(Varchar(200),T0.AliasName ) !=''
UNION ALL
select 'COMPACT' COMPANY, t0.CardCode, t0.CardName , t0.Balance,t1.GroupNum,t1.PymntGroup from COMPACT_LIVE1..OCRD t0
inner join COMPACT_LIVE1..OCTG t1 on t0.GroupNum=t1.GroupNum
where t0.Balance > 0 and T0.CARDTYPE='C' and CONVERT(Varchar(200),T0.AliasName ) !=''
UNION ALL
select 'DELTA' COMPANY, t0.CardCode, t0.CardName , t0.Balance,t1.GroupNum,t1.PymntGroup from DELTA_LIVE..OCRD t0
inner join DELTA_LIVE..OCTG t1 on t0.GroupNum=t1.GroupNum
where t0.Balance > 0 and T0.CARDTYPE='C' AND CONVERT(Varchar(200),T0.AliasName ) !=''
_______________________________________________________________________________________________
Q Sales order by Business partner ?
Ans:-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 Import Purchase Details ?
Ans:- SELECT P0.DOCENTRY, 'PURCHASE' AS 'TYPE1' ,'IMPORT PURCHASE ' AS 'TYPE' ,P0.DOCNUM, P0.DOCDATE,P0.CARDCODE,P0.CARDNAME, P1.LineTotal AS 'PURCHASE AMOUNT '
FROM NNM1 S1,PCH1 P1,OLCT L1,PCH4 P4,OPCH P0 LEFT OUTER JOIN PCH12 P12 on P0.DOCENTRY = P12.DOCENTRY
Where P0.SERIES = S1.SERIES
AND P0.DOCENTRY = P1.DOCENTRY
AND P1.LOCCODE = L1.CODE
AND P0.DOCENTRY = P4.DOCENTRY
AND P1.DOCENTRY = P4.DOCENTRY
AND P1.LineNum = P4.Linenum
AND ISNULL(P4.ExpnsCode,'') = '-1'
AND ISNULL(P12.IMPOREXP,'') = 'Y'
AND P0.CANCELED = 'N'
AND P0.DocDate between '20150901' And '20150930'
_______________________________________________________________________________________________
Q Open purchase order Warehouse By ?
Ans:-SELECT T0.[DocEntry], T0.[DocDate], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T1.[Price] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[OpenQty] >=1 and T0.[CardName] ='[%0]' and T0.[Series]='279'
_______________________________________________________________________________________________
Q Freight inword & outward Reports?
Ans:-DECLARE @DOCDATEFROM DATETIME
DECLARE @DOCDATETO DATETIME
SELECT @DOCDATEFROM=TAXDATE FROM OJDT T0 WHERE T0.TAXDATE=[%0]
SELECT @DOCDATETO=TAXDATE FROM OJDT T0 WHERE T0.TAXDATE=[%1]
select
(select AcctName from oact where acctcode=t0.Account) as 'GL Name',
t0.DEBIT, T0.REFDATE,
(select Top 1 ShortName from jdt1 where refdate = t1.refdate and transid=t0.TransId and shortname like 'V%' ORDER BY SHORTNAME DESC) as 'CardCode',
(SELECT CARDNAME FROM OCRD WHERE CARDCODE=(select Top 1 ShortName from jdt1 where refdate = t1.refdate and transid=t0.TransId and shortname like 'V%' ORDER BY SHORTNAME DESC)) as 'Party Name',
t0.Project,t0.U_Branch,
t0.TRANSID,T0.LineMemo,
REF3LINE AS MONTH,
t0.Ref1,
t0.REF2 from jdt1 t0
inner join ojdt t1 on t0.TransId=t1.TransId
where t0.Account='E15034' and t1.RefDate BETWEEN @DOCDATEFROM AND @DOCDATETO and t0.debit>0
union all
select
(select AcctName from oact where acctcode=t0.Account) as 'GL Name',
t0.DEBIT, T0.REFDATE,
(select Top 1 ShortName from jdt1 where refdate = t1.refdate and transid=t0.TransId and shortname like 'V%' ORDER BY SHORTNAME DESC) as 'CardCode',
(SELECT CARDNAME FROM OCRD WHERE CARDCODE=(select Top 1 ShortName from jdt1 where refdate = t1.refdate and transid=t0.TransId and shortname like 'V%' ORDER BY SHORTNAME DESC)) as 'Party Name',
t0.Project,t0.U_Branch,
t0.TRANSID,T0.LineMemo,
REF3LINE AS MONTH,
t0.Ref1,
t0.REF2 from jdt1 t0
inner join ojdt t1 on t0.TransId=t1.TransId
where t0.Account='E15037' and t1.RefDate BETWEEN @DOCDATEFROM AND @DOCDATETO and t0.debit>0
_______________________________________________________________________________________________
Q General Ladger Report?
DECLARE @D1 DATETIME
DECLARE @D2 DATETIME
SET @D1= '2014-12-01 00:00:00.000'
SET @D2= '2015-01-01 00:00:00.000'
select
T0.RefDate as 'Posting Date' ,T0.DueDate ,T0.TaxDate as 'Document Date',T0.BaseRef as 'Doc.No.' ,
T0.TransId as 'Trans.No.' ,T0.Memo as 'Remarks' , T1.[ContraAct] 'OffsetAcct',
Account=(CASE WHEN T2.AcctName IS NULL THEN t3.cardname else T2.AcctName end ),
T1.Debit, T1.Credit
from OJDT T0
inner join JDT1 T1 on T0.TransId =T1.TransId
left outer Join OACT T2 ON T2.AcctCode=T1.ContraAct
left outer join OCRD t3 on t3.CardCode=T1.ContraAct
Where T0.RefDate>=@d1 and T0.RefDate<=@d2 and (T1.[ContraAct]='A17802' or t1.ShortName='A17802')
order by T0.TransId ,t1.Line_ID
-----------------------------------------------------------------------------------------------------------------------------
Q Aging Report for particular Customer?
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 Report Date Wise?
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 VIS_Query_InvWise_SalesSummry @DATEFROM,@DATETO
------------------------------------------------------------------------------------------------------------------
Q Section Wise Production Reports?
Select T1.DocEntry,T1.U_ITEMNO,T1.U_ITEMDSC,T0.DocDate,
T1.U_FINQTY ,T1.U_SCRAP,T2.U_Section,T2.U_Stage,
T1.U_SHIFT,T1.U_LINECODE,T1.U_MANP AS 'Man Power'
From OIGN T0,[@SWPD] T1 ,OITT T2
Where T0.Ref2 = T1.DocEntry
And T0.DocDate Between '[%0]' And '[%1]'
And ISNULL(T1.U_ITEMNO,'') != ''
And T1.U_ITEMNO = T2.Code
Order By T0.DocEntry
------------------------------------------------------------------------------------------------------------------
Q Customer Credit Limit?
DECLARE @Factor as numeric(1,0) SELECT @Factor = CASE (SELECT TOP 1 DispPosDeb FROM OADM) WHEN 'N' THEN 1 ELSE -1 END SELECT T0.CardCode, T0.CardName, T0.Balance, T0.CreditLine,(T0.CreditLine + @Factor*T0.Balance) "Deviation" FROM OCRD T0 WHERE (select T0.CreditLine + @Factor*T0.Balance) < 0 AND T0.CardType = 'C'
------------------------------------------------------------------------------------------------------------------
Q Pending Production Reports?
Select T2.U_Section AS 'Section',T2.U_Stage AS 'Stage',T0.DocNum As 'Production Order No.',T0.U_DATE,T1.U_ITEMNO AS 'ItemCode' ,T1.U_ITEMDSC AS 'ItemDesc',
T1.U_FINQTY As 'FinishQty',T1.U_SCRAP As 'RejectQty'
From [@SWPM] T0 Left Outer Join [@SWPD] T1 ON T0.DocEntry = T1.DocEntry AND (T0.U_STATUS = 'Open' AND ISNULL(T0.U_Trans,'') = '')
INNER join OITT T2 ON T1.U_ITEMNO = T2.Code
Order By T0.U_DATE,T2.U_Section,T2.U_Stage
------------------------------------------------------------------------------------------------------------------
Q Payment Reports ?
SELECT OPDF.DocEntry, OPDF.DocDate as PostingDate,OPDF.TaxDate as DocumentDate, isnull(OPDF.CardCode,'') as SupplierCode, isnull(OPDF.CardName,'') as SupplierName, OPDF.CheckSum, OPDF.DocTotal, isnull(OPDF.Comments,''), isnull(PDF1.CheckNum,'') as CheckNumber, isnull(PDF1.BankCode,'') as BankCode, isnull(PDF1.Branch,'') as BankName, OUSR.U_NAME as UserName, OUSR.USER_CODE as UserCode,OPCH.DocTotal AS InvoiceTotal,opch.TotalExpns,OPDF.DocNum,opdf.DocEntry
FROM PDF1 RIGHT OUTER JOIN
OPDF INNER JOIN
OUSR ON OPDF.UserSign = OUSR.USERID INNER JOIN
OPCH ON OPDF.DocEntry = OPCH.DocEntry ON PDF1.DocNum = OPDF.DocEntry
WHERE (OPDF.Canceled <> 'Y')
---------------------------------------------------------------------------------------------------------------------
Q Job work Order status?
Select A.U_DNUM 'Work Order No',A.U_DocDate AS 'Work Order Date' ,A.U_CardCode,A.U_CardName,A.U_ItemCode,
A.U_Dscription ,A.U_Quantity AS 'OrderQty',B.ReciptQty,(A.U_Quantity - B.ReciptQty) AS 'BalanceQty'
from [@TIJWOH] A
LEFT JOIN (Select U_JwoDE,U_ItemCode,U_JwoNo,SUM(U_RecQty) AS 'ReciptQty' from [@TIJWORH]
group by U_JwoDE,U_ItemCode,U_JwoNo) B ON A.U_DNUM = B.U_JwoDE And A.U_ItemCode = B.U_ItemCode and B.U_JwoNo = A.DocEntry
order by A.U_DocDate desc
Like:-
---------------------------------------------------------------------------------------------------------------------------------
Q Important purchase Details ?
SELECT P0.DOCENTRY, 'PURCHASE' AS 'TYPE1' ,'IMPORT PURCHASE ' AS 'TYPE' ,P0.DOCNUM, P0.DOCDATE,P0.CARDCODE,P0.CARDNAME, P1.LineTotal AS 'PURCHASE AMOUNT '
FROM NNM1 S1,PCH1 P1,OLCT L1,PCH4 P4,OPCH P0 LEFT OUTER JOIN PCH12 P12 on P0.DOCENTRY = P12.DOCENTRY
Where P0.SERIES = S1.SERIES
AND P0.DOCENTRY = P1.DOCENTRY
AND P1.LOCCODE = L1.CODE
AND P0.DOCENTRY = P4.DOCENTRY
AND P1.DOCENTRY = P4.DOCENTRY
AND P1.LineNum = P4.Linenum
AND ISNULL(P4.ExpnsCode,'') = '-1'
AND ISNULL(P12.IMPOREXP,'') = 'Y'
AND P0.CANCELED = 'N'
AND P0.DocDate between '20150901' And '20150930'
Like:-
----------------------------------------------------------------------------------------------------------------------------------
Q how to find out credit limit and 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
like:-
---------------------------------------------------------------------------------------------------------------------------------
Q. HOW TO FIND OUT WHICH RM CODE USE 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]'
LIKE:-
Q Purchase against C Form ?
SELECT T0.[DocNum], T0.[DocDate],T0.[CANCELED], T0.[TaxDate], T0.[CardName], T0.[VatSum], T0. [NumAtCard], T0.[DocTotal], T0.[U_TRNSN], T0.[U_LRNO], T0.[U_DEST], T2.[Location], T3.[TransCat] FROM [dbo].[OPCH] T0 INNER JOIN [dbo].[PCH1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OLCT T2 ON T1.LocCode = T2.Code INNER JOIN PCH12 T3 ON T0.DocEntry = T3.DocEntry WHERE T2.[Location] =[%0] and (T0.[DocDate] >=[%1] and T0.[DocDate] <=[%2] ) and T3.[TransCat] = 'Form C' GROUP BY T0.[DocNum], T0.[DocDate],T0.[CANCELED], T0.[DocStatus], T0.[TaxDate], T0.[CardName], T0.[VatSum], T0. [NumAtCard], T0.[DocTotal], T0.[TrnspCode], T0.[U_TRNSN], T0.[U_LRNO], T0.[U_DEST], T1.[LocCode], T2.[Location], T3.[TransCat] ORDER BY T0.[DocDate]
__________________________________________________________________________________________________
Q Open sales order by location (Warhouse) ?
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] = (N'[%0]' )
_________________________________________________________________________________________________________
Q Open perchase order with custumer/vendor wise?
SELECT T0.[DocEntry], T0.[DocDate], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T1.[Price] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[OpenQty] >=1 and T0.[CardName] ='[%0]' and T0.[Series]='279'
__________________________________________________________________________________________________________
------------------------------------------------------------------------------------------------------------------------------
