Following are the query which is use to get some standard report data from SAP Business
------------------------------------------
------------------------------------------
/* Sales Invoice Alert Query */
SELECT CARDNAME,DOCNUM, DOCTOTAL, DOCENTRY, CREATEDATE, DOCTIME
FROM OINV
WHERE CREATEDATE=DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
AND LEFT(CONVERT(CHAR,DATEPART(HOUR,GETDATE()),2),2)+
RIGHT('0'+LEFT(CONVERT(CHAR,DATEPART(MINUTE,GETDATE()),2),2),2)
<DOCTIME+2
-------------------------------------------
-------------------------------------------
/* Debtors Balance */
SELECT T0.[CardCode], T0.[CardName], T0.[State1], T0.[CntctPrsn], T0.[Phone1],
T0.[Balance], T1.[SlpName]
FROM OCRD T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.[Balance] <>0.00 AND T0.[CardType] in ('C','L')
-------------------------------------------
-------------------------------------------
/* Debtors Test */
SELECT T0.[CardCode], T0.[CardName], T0.[State1], T1.[SlpName], T0.[Balance], T2.[DocNum]
FROM OCRD T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
INNER JOIN OINV T2 ON T0.CardCode = T2.CardCode
WHERE T2.[Series] =[%0] AND T0.[Balance] <>0.00 AND T2.[DocDate] <[%1]
--------------------------------------------
--------------------------------------------
/* Dealers */
SELECT T0.[CardCode], T0.[CardName], T1.[Address]
FROM OCRD T0
INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode
WHERE T0.[QryGroup3] =[%0]
--------------------------------------------
--------------------------------------------
/* Auditrs Query */
SELECT T0.[CardCode], T0.[CardName], T0.[Project], T0.[DocNum],
T0.[DocDate], T0.[TaxDate],T1.[Quantity], T0.[CreateDate],
T1.[PriceBefDi], T1.[ItemCode], T1.[LineTotal], T1.[Dscription],
T0.[TotalExpns],T2.[TaxId1], T2.[TaxId11], T0.[DocStatus], T0.[VatSum],
T3.[SlpName]
FROM [dbo].[OINV] T0
INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN INV12 T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode
WHERE T0.[DocDate] >=[%1] AND T0.[DocDate] <=[%2]
--------------------------------------------
--------------------------------------------
/* BP DATA */
SELECT T0.[CardCode], T0.[CardName], T0.[CreditLine], T0.[Balance],
T0.[ProjectCod],T0.[CntctPrsn], T0.[Building], T0.[Block], T0.[Address],
T0.[City], T0.[ZipCode]
FROM [dbo].[OCRD] T0
INNER JOIN CRD7 T1 ON T0.CardCode = T1.CardCode
INNER JOIN OPRJ T2 ON T0.ProjectCod = T2.PrjCode
WHERE T0.[ProjectCod] =[%0] AND T0.[CardType] =[%1]
-------------------------------------------
-------------------------------------------
/* Check Details */
SELECT T0.[DocNum], T0.[Canceled], T0.[DocDate],
T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[TransId],T0.[Comments],
T1.[DueDate], T1.[CheckNum]
FROM ORCT T0
INNER JOIN RCT1 T1 ON T0.DocEntry = T1.DocNum
--------------------------------------------
--------------------------------------------
/* Form Category */
SELECT T0.[TransCat] FROM OTNC T0
--------------------------------------------
--------------------------------------------
/* Incoming Payment */
SELECT T0.[CardCode], T0.[CardName], T0.[PrjCode], T0.[Series],
T0.[DocNum], T0.[DocDate], T0.[Comments], T0.[JrnlMemo],
T0.[DocTotal]
FROM ORCT T0
WHERE T0.[Series]=[%0]
and T0.[DocDate] <=[%1] AND T0.[DocDate] >=[%2] AND T0.[Canceled] ='n'
--------------------------------------------
--------------------------------------------
/* Invoice raised in a day */
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum],
T0.[TaxDate], T0.[DocTotal]
FROM OINV T0
WHERE T0.[TaxDate] =[%1]
--------------------------------------------
--------------------------------------------
/* Purchase Inventory Transfer */
SELECT T0.[DocNum], T0.[DocDate], T0.[Filler] as 'Purchase Whse',
T1.[WhsCode] as 'To Whse' ,T0.[CardCode], T0.[CardName], T0.[DocTotal],
T1.[ItemCode], T1.[DSCRIPTION], T1.[Quantity], T0.[Comments],
T0.[JrnlMemo] as 'Auto Remarks',T0.[CANCELED], T0.[U_Cancelled]
FROM OWTR T0
INNER JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[Filler] =[%0] and T0.[DocDate] >=[%1] AND T0.[DocDate] <=[%2]
----------------------------------------------
----------------------------------------------
/* Sales Inventory Transfer */
SELECT T0.[DocNum], T0.[DocDate], T0.[Filler] as 'Purchase Whse',
T1.[WhsCode] as 'To Whse' ,T0.[CardCode], T0.[CardName],
T0.[DocTotal], T1.[ItemCode], T1.[DSCRIPTION], T1.[Quantity],
T0.[Comments], T0.[JrnlMemo] as 'Auto Remarks',T0.[CANCELED],
T0.[U_Cancelled]
FROM OWTR T0
INNER JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[Filler]=[%1] AND T0.[DocDate] >=[%2] AND T0.[DocDate] <=[%3]
-----------------------------------------------
-----------------------------------------------
/* Purchase Summary Report */
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum],
T0.[DocDate], T0.[DocTotal], T1.[ItemCode],
T1.[Dscription], T1.[Quantity], T1.[WhsCode],
T1.[LineTotal], T1.[TaxCode]
FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND T0.[DocType] =[%2]
AND T0.[Project] =[%3] AND T1.[ItemCode] =[%4]
-----------------------------------------------
-----------------------------------------------
/* Goods Issue */
SELECT T0.[DocNum],T0.[DocDate], T0.[DocType],
T0.[CANCELED], T1.[ItemCode], T1.[Dscription],T1.[Quantity],
T1.[WhsCode], T1.[Project], T0.[Comments] FROM OIGE T0 INNER JOIN IGE1 T1 ON
T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] >=[%0]
AND T0.[DocDate] <=[%1]
AND T1.[WhsCode] =[%2]
----------------------------------------------
----------------------------------------------
/* Goods Receipt */
SELECT T0.[DocNum],T0.[DocDate], T0.[DocType],
T0.[CANCELED], T1.[ItemCode], T1.[Dscription],
T1.[Quantity], T1.[WhsCode], T1.[Project],
T0.[Comments]
FROM OIGN T0
INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND T1.[WhsCode] =[%3]
----------------------------------------------
----------------------------------------------
/* Account Balance By BP */
SELECT T0.RefDate, T0.DueDate, T0.TransId, T0.LineMemo, T0.Ref1,
(CASE WHEN T0.TransType = '13' THEN T1.DunnLevel ELSE (CASE WHEN T0.TransType = '18' THEN T2.DunnLevel ELSE 0 END) END) AS 'Dunning Level',
(CASE WHEN T0.Credit <> 0 THEN T0.Credit ELSE T0.Debit END) AS 'C/D (LC)'
FROM JDT1 T0
LEFT JOIN OINV T1 ON T0.Ref1 = T1.DocNum
LEFT JOIN OPCH T2 ON T0.Ref1 = T2.DocNum
WHERE (T0.ShortName = '[%0]') ORDER BY T0.RefDate DESC
------------------------------------------------
------------------------------------------------
/* Activity By BP */
SELECT T0.Recontact,
(CAST(CAST(T0.BeginTime / 100 AS INT) + CAST(T0.BeginTime - 100 * CAST(T0.BeginTime / 100 AS INT) AS FLOAT) / 60 AS FLOAT) / 24) AS BegTime,
T0.ClgCode, T1.SlpName, T2.Name, T3.Name, T0.Details,
(CASE WHEN T0.Closed = 'Y' THEN 'Closed' ELSE 'Open' END) AS Status
FROM OCLG T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN OCPR T2 ON T0.CntctCode = T2.CntctCode
LEFT JOIN OCLT T3 ON T0.CntctType = T3.Code
WHERE T0.CardCode = '[%0]'
ORDER BY T0.ClgCode DESC
-------------------------------------------------
-------------------------------------------------
/* Activities by Contact Person */
SELECT T0.Recontact,
(CAST(CAST(T0.BeginTime / 100 AS INT) + CAST(T0.BeginTime - 100 * CAST(T0.BeginTime / 100 AS INT) AS FLOAT) / 60 AS FLOAT) / 24) AS BegTime,
T0.ClgCode, T1.SlpName, T3.Name, T0.Details,
(CASE WHEN T0.Closed = 'Y' THEN 'Closed' ELSE 'Open' END) AS Status
FROM OCLG T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN OCLT T3 ON T0.CntctType = T3.Code
WHERE T0.CardCode = '[%0]' AND T0.CntctCode = [%1]
ORDER BY T0.ClgCode DESC
------------------------------------------------
------------------------------------------------
/*AP Invoice By BP */
SELECT T0.DocDate, T0.DocNum, T1.SlpName, T2.Name, T0.DocDueDate,
DATEDIFF(day, T0.DocDueDate, getdate()) AS DaysPastDue,
T0.DocTotal, T0.PaidToDate
FROM OPCH T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN OCPR T2 ON T0.CntctCode = T2.CntctCode
WHERE T0.CardCode = '[%0]' AND T0.DocStatus = 'O' ORDER BY T0.DocNum DESC
----------------------------------------------
----------------------------------------------
/* AP Invoices by Contact Person */
SELECT T0.DocDate, T0.DocNum, T1.SlpName, T0.DocDueDate,
DATEDIFF(day, T0.DocDueDate, getdate()) AS DaysPastDue,
T0.DocTotal, T0.PaidToDate
FROM OPCH T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.CardCode = '[%0]' AND T0.CntctCode = [%1] AND T0.DocStatus = 'O'
ORDER BY T0.DocNum DESC
-----------------------------------------------
-----------------------------------------------
/* Products Purchased from BP */
SELECT T1.ItemCode, T1.Dscription, SUM(T1.Quantity) AS Quantity,
SUM(T1.LineTotal) AS SalesAmount
FROM OPCH T0
JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.CardCode = '[%0]'
AND DATEDIFF(mm, T0.DocDueDate, getdate()) < 3
GROUP BY T1.ItemCode, T1.Dscription
ORDER BY T1.Dscription
---------------------------------------------
---------------------------------------------
/* Production Order Report */
SELECT T0.[DocNum], T0.[PostDate], T0.[ItemCode],
T2.[ItemName], T0.[PlannedQty] as 'Sheets Planned',
T1.[ItemCode], T1.[BaseQty] as 'Quantity in BOM',
T1.[PlannedQty] as 'RM Planned Qty', T1.[IssuedQty],
T1.[wareHouse]
FROM OWOR T0
INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.[PostDate] >=[%0] AND T0.[PostDate] <=[%1]
AND T0.[Project] =[%2]
ORDER BY T0.[DocNum]
-----------------------------------------------
-----------------------------------------------
/* Bank and Cash */
SELECT T0.AcctCode, T0.AcctName, T0.CurrTotal
FROM OACT T0
WHERE T0.AcctCode in ('157000','157001','157002','158004','213001','212001','212002')
----------------------------------------------
----------------------------------------------
/* Check Details Report */
SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T1.CheckNum, T1.CheckSum,
T0.U_MOD, T1.BankCode, T1.Branch
FROM OVPM T0
JOIN VPM1 T1 ON T0.DocEntry = T1.DocNum
AND t0.docdate >= [%0]
AND t0.docdate <= [%1]
----------------------------------------------
----------------------------------------------
/* Check Dispatch Status */
SELECT T0.DocNum, T0.CardName, T1.CheckNum, T0.TaxDate,
T0.U_DOD, T0.U_MOD, T0.U_DS, T0.U_DDN
FROM OVPM T0
INNER JOIN VPM1 T1 ON T0.DocNum = T1.DocNum
WHERE T0.DocNum = T1.DocNum
-----------------------------------------------
-----------------------------------------------
/* Payment Received Details */
SELECT T0.DocNum, T0.DocDate, T0.CardName, T0.CounterRef, T0.DocTotal,
T0.U_TDSAmt
FROM ORCT T0
WHERE T0.[DocDate] between [%0] and [%1] and t0.JrnlMemo<>'Canceled'
------------------------------------------------
------------------------------------------------
/* BP Tax Information */
SELECT * FROM CRD7 T0 ORDER BY T0.CardCode
------------------------------------------------
------------------------------------------------
/* Store Item Stock */
SELECT T0.ItemCode, T1.ItemName, T0.WhsCode, T0.OnHand
from OITW T0
nner join OITM T1 on T0.ItemCode=T1.ItemCode
where T0.OnHand>0
order by T0.WhsCode,T0.ItemCode
--------------------------------------------------
--------------------------------------------------
/* Account Statement */
SELECT T0.RefDate, T0.ShortName, T1.CardName, T0.Ref1, T0.Ref2, T0.TaxDate,
t0.LineMemo, T0.Debit, T0.Credit, T0.Account, T0.IntrnMatch,T0.ExtrMatch
FROM JDT1 T0, OCRD T1, OACT t2
WHERE t0.shortname=t1.cardcode and T0.Account =t2.AcctCode
-------------------------------------------------
-------------------------------------------------
/* Customer Due Overdue Summary */
SELECT distinct t0.CardName, t2.PymntGroup,t5.Balance,t0.Docstatus,
ISNULL ((SELECT SUM ( ISNULL (A0.DOCTOTAL,0.00)) from OINV A0 where A0.Docstatus = 'O' and t0.CardCode=A0.CardCode and DATEDIFF(day ,t0.docdate ,t0.docduedate) <= (DATEDIFF(day ,t0.docdate ,current_timestamp)) ),0.00)[Over dues Amount],
ISNULL ((SELECT SUM ( ISNULL (A0.DOCTOTAL,0.00)) from OINV A0 where A0.Docstatus = 'O' and t0.CardCode=A0.CardCode and DATEDIFF(day ,t0.docdate ,t0.docduedate) >= (DATEDIFF(day ,t0.docdate ,current_timestamp)) ),0.00)[Dues Amount]
FROM OINV t0
left JOIN OCTG t2 ON t0.GroupNum = t2.GroupNum
left OUTER JOIN OCRD t5 on t0.cardcode=t5.CardCode
WHERE t0.Docstatus = 'O'
and t2.AcctName >=[%0] AND t2.AcctName <=[%1] AND T0.RefDate >=[%2]
AND T0.RefDate <=[%3] ORDER BY T0.ShortName
------------------------------------------------
------------------------------------------------
-----------------
------------------------------------------------
------------------------------------------------
/* User Update Query */
Select T2.USER_CODE,
Case When T2.U_Name <> '' Then U_NAME Else 'Empty' End as 'Current User Name',
Case When (SELECT T1.[U_NAME] FROM AUSR T1 WHERE T1.userid = T2.UserID and
T1.[logInstanc] = (Select Top 1 T3.[logInstanc] From AUSR T3 where T3.U_name <> T2.U_Name and T3.userid =T2.USERID order by T3.[logInstanc] desc)
) <> '' Then
(SELECT T1.[U_NAME]
FROM AUSR T1
WHERE T1.userid = T2.UserID and
T1.[logInstanc] = (Select Top 1 T3.[logInstanc] From AUSR T3 where T3.U_name <> T2.U_Name and T3.userid =T2.USERID order by T3.[logInstanc] desc)
) Else 'Empty' End as 'Previous User Name', T2.updateDate as 'Update Date'
From OUSR T2
--------------------------------------------
--------------------------------------------
/* Reconcile Bank Transaction */
SELECT
T0.Refdate,T1.[ExtrMatch],
(select T2.acctname from OACT T2 where T2.AcctCode=T1.ContraAct) as AcctName,
(select T3.cardname from OCRD T3 where T3.cardcode=T1.ContraAct) as BPname,
(T1.[Debit]), (T1.[Credit]),
(select (Sum(T1.[Debit])-Sum(T1.[Credit])) as OB from OJDT T0
INNER JOIN JDT1 T1 with (nolock) ON T0.TransId = T1.TransId
WHERE T1.[Account] =[%0] and T0.[RefDate] >=[%1] and T0.[RefDate] <=[%2])
FROM OJDT T0
INNER JOIN JDT1 T1 with (nolock) ON T0.TransId = T1.TransId
WHERE T1.[Account] =[%0] and T1.[ExtrMatch] !='0' and T0.[RefDate] >=[%1]
and T0.[RefDate] <=[%2] order by T0.[RefDate]
----------------------------------------------
----------------------------------------------
------------------------------------------
------------------------------------------
/* Sales Invoice Alert Query */
SELECT CARDNAME,DOCNUM, DOCTOTAL, DOCENTRY, CREATEDATE, DOCTIME
FROM OINV
WHERE CREATEDATE=DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)
AND LEFT(CONVERT(CHAR,DATEPART(HOUR,GETDATE()),2),2)+
RIGHT('0'+LEFT(CONVERT(CHAR,DATEPART(MINUTE,GETDATE()),2),2),2)
<DOCTIME+2
-------------------------------------------
-------------------------------------------
/* Debtors Balance */
SELECT T0.[CardCode], T0.[CardName], T0.[State1], T0.[CntctPrsn], T0.[Phone1],
T0.[Balance], T1.[SlpName]
FROM OCRD T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.[Balance] <>0.00 AND T0.[CardType] in ('C','L')
-------------------------------------------
-------------------------------------------
/* Debtors Test */
SELECT T0.[CardCode], T0.[CardName], T0.[State1], T1.[SlpName], T0.[Balance], T2.[DocNum]
FROM OCRD T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
INNER JOIN OINV T2 ON T0.CardCode = T2.CardCode
WHERE T2.[Series] =[%0] AND T0.[Balance] <>0.00 AND T2.[DocDate] <[%1]
--------------------------------------------
--------------------------------------------
/* Dealers */
SELECT T0.[CardCode], T0.[CardName], T1.[Address]
FROM OCRD T0
INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode
WHERE T0.[QryGroup3] =[%0]
--------------------------------------------
--------------------------------------------
/* Auditrs Query */
SELECT T0.[CardCode], T0.[CardName], T0.[Project], T0.[DocNum],
T0.[DocDate], T0.[TaxDate],T1.[Quantity], T0.[CreateDate],
T1.[PriceBefDi], T1.[ItemCode], T1.[LineTotal], T1.[Dscription],
T0.[TotalExpns],T2.[TaxId1], T2.[TaxId11], T0.[DocStatus], T0.[VatSum],
T3.[SlpName]
FROM [dbo].[OINV] T0
INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN INV12 T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode
WHERE T0.[DocDate] >=[%1] AND T0.[DocDate] <=[%2]
--------------------------------------------
--------------------------------------------
/* BP DATA */
SELECT T0.[CardCode], T0.[CardName], T0.[CreditLine], T0.[Balance],
T0.[ProjectCod],T0.[CntctPrsn], T0.[Building], T0.[Block], T0.[Address],
T0.[City], T0.[ZipCode]
FROM [dbo].[OCRD] T0
INNER JOIN CRD7 T1 ON T0.CardCode = T1.CardCode
INNER JOIN OPRJ T2 ON T0.ProjectCod = T2.PrjCode
WHERE T0.[ProjectCod] =[%0] AND T0.[CardType] =[%1]
-------------------------------------------
-------------------------------------------
/* Check Details */
SELECT T0.[DocNum], T0.[Canceled], T0.[DocDate],
T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[TransId],T0.[Comments],
T1.[DueDate], T1.[CheckNum]
FROM ORCT T0
INNER JOIN RCT1 T1 ON T0.DocEntry = T1.DocNum
--------------------------------------------
--------------------------------------------
/* Form Category */
SELECT T0.[TransCat] FROM OTNC T0
--------------------------------------------
--------------------------------------------
/* Incoming Payment */
SELECT T0.[CardCode], T0.[CardName], T0.[PrjCode], T0.[Series],
T0.[DocNum], T0.[DocDate], T0.[Comments], T0.[JrnlMemo],
T0.[DocTotal]
FROM ORCT T0
WHERE T0.[Series]=[%0]
and T0.[DocDate] <=[%1] AND T0.[DocDate] >=[%2] AND T0.[Canceled] ='n'
--------------------------------------------
--------------------------------------------
/* Invoice raised in a day */
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum],
T0.[TaxDate], T0.[DocTotal]
FROM OINV T0
WHERE T0.[TaxDate] =[%1]
--------------------------------------------
--------------------------------------------
/* Purchase Inventory Transfer */
SELECT T0.[DocNum], T0.[DocDate], T0.[Filler] as 'Purchase Whse',
T1.[WhsCode] as 'To Whse' ,T0.[CardCode], T0.[CardName], T0.[DocTotal],
T1.[ItemCode], T1.[DSCRIPTION], T1.[Quantity], T0.[Comments],
T0.[JrnlMemo] as 'Auto Remarks',T0.[CANCELED], T0.[U_Cancelled]
FROM OWTR T0
INNER JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[Filler] =[%0] and T0.[DocDate] >=[%1] AND T0.[DocDate] <=[%2]
----------------------------------------------
----------------------------------------------
/* Sales Inventory Transfer */
SELECT T0.[DocNum], T0.[DocDate], T0.[Filler] as 'Purchase Whse',
T1.[WhsCode] as 'To Whse' ,T0.[CardCode], T0.[CardName],
T0.[DocTotal], T1.[ItemCode], T1.[DSCRIPTION], T1.[Quantity],
T0.[Comments], T0.[JrnlMemo] as 'Auto Remarks',T0.[CANCELED],
T0.[U_Cancelled]
FROM OWTR T0
INNER JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[Filler]=[%1] AND T0.[DocDate] >=[%2] AND T0.[DocDate] <=[%3]
-----------------------------------------------
-----------------------------------------------
/* Purchase Summary Report */
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum],
T0.[DocDate], T0.[DocTotal], T1.[ItemCode],
T1.[Dscription], T1.[Quantity], T1.[WhsCode],
T1.[LineTotal], T1.[TaxCode]
FROM [dbo].[OPCH] T0
INNER JOIN [dbo].[PCH1] T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND T0.[DocType] =[%2]
AND T0.[Project] =[%3] AND T1.[ItemCode] =[%4]
-----------------------------------------------
-----------------------------------------------
/* Goods Issue */
SELECT T0.[DocNum],T0.[DocDate], T0.[DocType],
T0.[CANCELED], T1.[ItemCode], T1.[Dscription],T1.[Quantity],
T1.[WhsCode], T1.[Project], T0.[Comments] FROM OIGE T0 INNER JOIN IGE1 T1 ON
T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] >=[%0]
AND T0.[DocDate] <=[%1]
AND T1.[WhsCode] =[%2]
----------------------------------------------
----------------------------------------------
/* Goods Receipt */
SELECT T0.[DocNum],T0.[DocDate], T0.[DocType],
T0.[CANCELED], T1.[ItemCode], T1.[Dscription],
T1.[Quantity], T1.[WhsCode], T1.[Project],
T0.[Comments]
FROM OIGN T0
INNER JOIN IGN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND T1.[WhsCode] =[%3]
----------------------------------------------
----------------------------------------------
/* Account Balance By BP */
SELECT T0.RefDate, T0.DueDate, T0.TransId, T0.LineMemo, T0.Ref1,
(CASE WHEN T0.TransType = '13' THEN T1.DunnLevel ELSE (CASE WHEN T0.TransType = '18' THEN T2.DunnLevel ELSE 0 END) END) AS 'Dunning Level',
(CASE WHEN T0.Credit <> 0 THEN T0.Credit ELSE T0.Debit END) AS 'C/D (LC)'
FROM JDT1 T0
LEFT JOIN OINV T1 ON T0.Ref1 = T1.DocNum
LEFT JOIN OPCH T2 ON T0.Ref1 = T2.DocNum
WHERE (T0.ShortName = '[%0]') ORDER BY T0.RefDate DESC
------------------------------------------------
------------------------------------------------
/* Activity By BP */
SELECT T0.Recontact,
(CAST(CAST(T0.BeginTime / 100 AS INT) + CAST(T0.BeginTime - 100 * CAST(T0.BeginTime / 100 AS INT) AS FLOAT) / 60 AS FLOAT) / 24) AS BegTime,
T0.ClgCode, T1.SlpName, T2.Name, T3.Name, T0.Details,
(CASE WHEN T0.Closed = 'Y' THEN 'Closed' ELSE 'Open' END) AS Status
FROM OCLG T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN OCPR T2 ON T0.CntctCode = T2.CntctCode
LEFT JOIN OCLT T3 ON T0.CntctType = T3.Code
WHERE T0.CardCode = '[%0]'
ORDER BY T0.ClgCode DESC
-------------------------------------------------
-------------------------------------------------
/* Activities by Contact Person */
SELECT T0.Recontact,
(CAST(CAST(T0.BeginTime / 100 AS INT) + CAST(T0.BeginTime - 100 * CAST(T0.BeginTime / 100 AS INT) AS FLOAT) / 60 AS FLOAT) / 24) AS BegTime,
T0.ClgCode, T1.SlpName, T3.Name, T0.Details,
(CASE WHEN T0.Closed = 'Y' THEN 'Closed' ELSE 'Open' END) AS Status
FROM OCLG T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN OCLT T3 ON T0.CntctType = T3.Code
WHERE T0.CardCode = '[%0]' AND T0.CntctCode = [%1]
ORDER BY T0.ClgCode DESC
------------------------------------------------
------------------------------------------------
/*AP Invoice By BP */
SELECT T0.DocDate, T0.DocNum, T1.SlpName, T2.Name, T0.DocDueDate,
DATEDIFF(day, T0.DocDueDate, getdate()) AS DaysPastDue,
T0.DocTotal, T0.PaidToDate
FROM OPCH T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
LEFT JOIN OCPR T2 ON T0.CntctCode = T2.CntctCode
WHERE T0.CardCode = '[%0]' AND T0.DocStatus = 'O' ORDER BY T0.DocNum DESC
----------------------------------------------
----------------------------------------------
/* AP Invoices by Contact Person */
SELECT T0.DocDate, T0.DocNum, T1.SlpName, T0.DocDueDate,
DATEDIFF(day, T0.DocDueDate, getdate()) AS DaysPastDue,
T0.DocTotal, T0.PaidToDate
FROM OPCH T0
LEFT JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
WHERE T0.CardCode = '[%0]' AND T0.CntctCode = [%1] AND T0.DocStatus = 'O'
ORDER BY T0.DocNum DESC
-----------------------------------------------
-----------------------------------------------
/* Products Purchased from BP */
SELECT T1.ItemCode, T1.Dscription, SUM(T1.Quantity) AS Quantity,
SUM(T1.LineTotal) AS SalesAmount
FROM OPCH T0
JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.CardCode = '[%0]'
AND DATEDIFF(mm, T0.DocDueDate, getdate()) < 3
GROUP BY T1.ItemCode, T1.Dscription
ORDER BY T1.Dscription
---------------------------------------------
---------------------------------------------
/* Production Order Report */
SELECT T0.[DocNum], T0.[PostDate], T0.[ItemCode],
T2.[ItemName], T0.[PlannedQty] as 'Sheets Planned',
T1.[ItemCode], T1.[BaseQty] as 'Quantity in BOM',
T1.[PlannedQty] as 'RM Planned Qty', T1.[IssuedQty],
T1.[wareHouse]
FROM OWOR T0
INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.[PostDate] >=[%0] AND T0.[PostDate] <=[%1]
AND T0.[Project] =[%2]
ORDER BY T0.[DocNum]
-----------------------------------------------
-----------------------------------------------
/* Bank and Cash */
SELECT T0.AcctCode, T0.AcctName, T0.CurrTotal
FROM OACT T0
WHERE T0.AcctCode in ('157000','157001','157002','158004','213001','212001','212002')
----------------------------------------------
----------------------------------------------
/* Check Details Report */
SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T1.CheckNum, T1.CheckSum,
T0.U_MOD, T1.BankCode, T1.Branch
FROM OVPM T0
JOIN VPM1 T1 ON T0.DocEntry = T1.DocNum
AND t0.docdate >= [%0]
AND t0.docdate <= [%1]
----------------------------------------------
----------------------------------------------
/* Check Dispatch Status */
SELECT T0.DocNum, T0.CardName, T1.CheckNum, T0.TaxDate,
T0.U_DOD, T0.U_MOD, T0.U_DS, T0.U_DDN
FROM OVPM T0
INNER JOIN VPM1 T1 ON T0.DocNum = T1.DocNum
WHERE T0.DocNum = T1.DocNum
-----------------------------------------------
-----------------------------------------------
/* Payment Received Details */
SELECT T0.DocNum, T0.DocDate, T0.CardName, T0.CounterRef, T0.DocTotal,
T0.U_TDSAmt
FROM ORCT T0
WHERE T0.[DocDate] between [%0] and [%1] and t0.JrnlMemo<>'Canceled'
------------------------------------------------
------------------------------------------------
/* BP Tax Information */
SELECT * FROM CRD7 T0 ORDER BY T0.CardCode
------------------------------------------------
------------------------------------------------
/* Store Item Stock */
SELECT T0.ItemCode, T1.ItemName, T0.WhsCode, T0.OnHand
from OITW T0
nner join OITM T1 on T0.ItemCode=T1.ItemCode
where T0.OnHand>0
order by T0.WhsCode,T0.ItemCode
--------------------------------------------------
--------------------------------------------------
/* Account Statement */
SELECT T0.RefDate, T0.ShortName, T1.CardName, T0.Ref1, T0.Ref2, T0.TaxDate,
t0.LineMemo, T0.Debit, T0.Credit, T0.Account, T0.IntrnMatch,T0.ExtrMatch
FROM JDT1 T0, OCRD T1, OACT t2
WHERE t0.shortname=t1.cardcode and T0.Account =t2.AcctCode
-------------------------------------------------
-------------------------------------------------
/* Customer Due Overdue Summary */
SELECT distinct t0.CardName, t2.PymntGroup,t5.Balance,t0.Docstatus,
ISNULL ((SELECT SUM ( ISNULL (A0.DOCTOTAL,0.00)) from OINV A0 where A0.Docstatus = 'O' and t0.CardCode=A0.CardCode and DATEDIFF(day ,t0.docdate ,t0.docduedate) <= (DATEDIFF(day ,t0.docdate ,current_timestamp)) ),0.00)[Over dues Amount],
ISNULL ((SELECT SUM ( ISNULL (A0.DOCTOTAL,0.00)) from OINV A0 where A0.Docstatus = 'O' and t0.CardCode=A0.CardCode and DATEDIFF(day ,t0.docdate ,t0.docduedate) >= (DATEDIFF(day ,t0.docdate ,current_timestamp)) ),0.00)[Dues Amount]
FROM OINV t0
left JOIN OCTG t2 ON t0.GroupNum = t2.GroupNum
left OUTER JOIN OCRD t5 on t0.cardcode=t5.CardCode
WHERE t0.Docstatus = 'O'
and t2.AcctName >=[%0] AND t2.AcctName <=[%1] AND T0.RefDate >=[%2]
AND T0.RefDate <=[%3] ORDER BY T0.ShortName
------------------------------------------------
------------------------------------------------
-----------------
------------------------------------------------
------------------------------------------------
/* User Update Query */
Select T2.USER_CODE,
Case When T2.U_Name <> '' Then U_NAME Else 'Empty' End as 'Current User Name',
Case When (SELECT T1.[U_NAME] FROM AUSR T1 WHERE T1.userid = T2.UserID and
T1.[logInstanc] = (Select Top 1 T3.[logInstanc] From AUSR T3 where T3.U_name <> T2.U_Name and T3.userid =T2.USERID order by T3.[logInstanc] desc)
) <> '' Then
(SELECT T1.[U_NAME]
FROM AUSR T1
WHERE T1.userid = T2.UserID and
T1.[logInstanc] = (Select Top 1 T3.[logInstanc] From AUSR T3 where T3.U_name <> T2.U_Name and T3.userid =T2.USERID order by T3.[logInstanc] desc)
) Else 'Empty' End as 'Previous User Name', T2.updateDate as 'Update Date'
From OUSR T2
--------------------------------------------
--------------------------------------------
/* Reconcile Bank Transaction */
SELECT
T0.Refdate,T1.[ExtrMatch],
(select T2.acctname from OACT T2 where T2.AcctCode=T1.ContraAct) as AcctName,
(select T3.cardname from OCRD T3 where T3.cardcode=T1.ContraAct) as BPname,
(T1.[Debit]), (T1.[Credit]),
(select (Sum(T1.[Debit])-Sum(T1.[Credit])) as OB from OJDT T0
INNER JOIN JDT1 T1 with (nolock) ON T0.TransId = T1.TransId
WHERE T1.[Account] =[%0] and T0.[RefDate] >=[%1] and T0.[RefDate] <=[%2])
FROM OJDT T0
INNER JOIN JDT1 T1 with (nolock) ON T0.TransId = T1.TransId
WHERE T1.[Account] =[%0] and T1.[ExtrMatch] !='0' and T0.[RefDate] >=[%1]
and T0.[RefDate] <=[%2] order by T0.[RefDate]
----------------------------------------------
----------------------------------------------
