Depending on your localization, sometimes you might need to know which A/R Credit Memos still have not been converted into Incoming Excise Invoices.
The following query was initially created to display the Incoming Excise Invoices, and has the AR Credit Memo as a base document. However, the initial attempt was not successful.
SELECT distinct T0.[DocNum], T0.[DocDate], T0.[CardName], T1.[BaseRef] as 'Credit Memo No', T1.[BaseType]
FROM OIEI T0 INNER JOIN IEI1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] between '[%0]' and '[%1]' and T1.BaseType ='14'
Here is the solution:
SELECT distinct T0.[DocNum], T0.[DocDate],
T0.[CardName] FROM dbo.ORIN T0
This is a typical case of getting the query solution to test without the actual tables. To find out if something is not being done, you always need to check the source table. There is no way to get such information from the target table. This is because, before copying the source table to a target table, nothing can be found in the target table yet. With the solution, the source Credit Memo tables ORIN and RIN1 are checked. The ORIN table is the header table to get the date range. The RIN1 table is the row table to check if the target Incoming Excise Invoices have been converted or not. Since the TargetType for Incoming Excise Invoices is unknown to me, I have left it as XXX. It can be found easily if you have this object.
The symbol != instead of <> is used within the book. They are equivalent; the latter symbol (Greater Than or Smaller Than) may not be able to display well on the web. Also, the symbol != is more straightforward to stand for Not (!) Equal (=).
Case 4-M6: Goods Receipt PO within 10 days
A good way to manage warehouse receipts is to create a list of Goods Receipt POs and review it periodically. However, there is a special requirement of all distinct vendors who have supplied material in user defined 10 days and whose total quantity – transaction wise – is less than 10. For example, suppose the date range is five days, then if the total number of Goods Receipt PO on any given day is in the range less than or equal to five, the document's total summary for the vendor should be listed.
The solution:
SELECT T0.CardCode,
Max(T0.Cardname) 'Vendor Name', SUM(T0.DocTotal) 'Total'
FROM dbo.OPDN T0
WHERE T0.DocDate >= '[%0]' AND T0.DocDate <= '[%0]' + 10
AND T0.CardCode NOT in (SELECT T1.CardCode FROM OPDN T1 WHERE T1.DocDate >= '[%0]' AND T1.DocDate <= '[%0]' + 10
GROUP BY T1.CardCode, T1.DocDate Having COUNT(T1.DocDate) > 10) GROUP BY T0.CardCode
The key part of the solution is again by a subquery:
SELECT T1.CardCode FROM OPDN T1 WHERE T1.DocDate >= '[%0] ' AND T1.DocDate <= '[%0] ' + 10
GROUP BY T1.CardCode, T1.DocDate Having COUNT(T1.DocDate) > 10
This subquery will return the list of vendors that have more than 10 transactions during any specific day in the selected date range. Those vendors who are not in this list will be displayed. The 10 days can be changed to a variable. However, the display of the variable will be misleading because it is the same as the first one. If you change this 10 to [%1], you will soon find out that the variable description changes to "posting date smaller or equal".
Case 4-M7: Quantity purchased, received, and
returned
A very common requirement in the purchasing function inside companies is to have a query to capture the quantity purchased, quantity received, and quantity returned. The solution is to link the required tables by left join:
SELECT T0.ItemCode, SUM(T0.Quantity) 'PO Qty', SUM(T1.Quantity) 'GRPO Qty', SUM(T2.Quantity) 'Return Qty' FROM dbo.POR1 T0
LEFT JOIN dbo.PDN1 T1
ON T0.DocEntry = T1.BaseEntry AND T0.LineNum = T1.BaseLine LEFT JOIN dbo.RPD1 T2
ON T1.DocEntry = T2.BaseEntry AND T1.LineNum = T2.BaseLine
WHERE T0.ItemCode is not NULL AND T0.DocDate >= [%0] AND T0.DocDate <= [%1]
GROUP BY T0.ItemCode ORDER BY T0.ItemCode
LEFT JOIN is essential here. We need to list all Purchase Orders during the user selected periods. However, not all Purchase Orders have been received yet. There are even fewer Goods Returns. (No business can survive if all goods purchased have been returned!) By LEFT JOIN clause, it will not bind any Goods Receipt PO or Goods Returns to the Purchase Order. Only when there are target documents, will they be included. Therefore, all Purchase Orders will be in the report regardless of whether the target documents exist or not. This unbinding feature is a useful tool to get a complete list with or without data binding to the main entry.
In this way, quantity purchased, quantity received, and quantity returned will be listed in three columns for each item number. The first column is item code. The second column must be full, because all item codes are taken from POR1. The third and the last column should have empty lines if the Purchase Orders have not been received in full during the selected period.
Case 4-M8: Customized sales analysis report
Sometimes users are looking for a customized Sales Analysis query report so that they can export the query result to Excel.
This query should include Invoices and Credit Memos, or the debit and the credit columns in the journal entries details.
Here is the half done query that needs to be completed:
SELECT Distinct T0.[CardCode], T0.[CardName],
(SELECT (SUM(T2.Debit) - sum(T2.Credit))
FROM OCRD T0 INNER JOIN JDT1 T1 ON T1.ShortName = T0.CardCode
WHERE T0.CardType = 'C' AND DateDiff(DD,T2.Duedate,GetDate()) between 0 and 30) AS 'Present Month',
FROM OCRD T0
INNER JOIN JDT1 T1 ON T1.ShortName = T0.CardCode WHERE T0.CardType = 'C'
The query should pick up all sales amounts for those months such as July and August. It also needs to remove payment transactions from the query results to show only the invoices/credit memo amounts.
FROM dbo.OCRD T0
LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Month(T1. Duedate) = 7 AND Year(T1.Duedate) = Year(GetDate()) AND T1.TransType in ('13','14')
LEFT JOIN dbo.JDT1 T2 ON T2.ShortName = T0.CardCode AND Month(T2. Duedate) = 8 AND Year(T2.Duedate) = Year(GetDate()) AND T2.TransType in ('13','14')
WHERE T0.CardType = 'C'
Group By T0.[CardCode], T0.[CardName]
Since this solution query only deals with journal entry tables, it should be under the financial query section. However, in common sense, sales analysis should be covered within sales. That is why it is under this category. All query records are extracted from the journal lines with TransType in ('13','14'). All those lines are the
result of automatic posting of A/R Invoices or A/R Credit Memos. So it makes sense to be under this category.
The first thing you should notice is that TransType in ('13','14') is equivalent
to ObjType in ('13','14') in the ADOC table. The difference is only the column
name. Under the journal entry, they are different transactions. Before the journal entry, they are only different objects.
In this query, you can see the JDT1 table joined twice to the OCRD table. By using two aliases from two joins, the query result can show the same table with different selections for the same Shortname (equivalent to CardCode in the OCRD table).
Case 4-M9: Average sales per month
A user worked out a query to get the average sales for 2009:
SELECT T0.CardCode, T0.CardName, (SUM(T1.Debit) - sum(T1.Credit)) AS '2009', ((SUM(T1.Debit) - sum(T1.Credit))/12)
FROM OCRD T0
LEFT JOIN JDT1 T1 ON T1.ShortName = T0.CardCode LEFT JOIN JDT1 T2 ON T2.ShortName = T0.CardCode WHERE T0.CardType = 'C' AND Year(T1. Duedate) = 2009 Group By T0.CardCode, T0.CardName
The solution is:
SELECT T0.CardCode, T0.CardName,
(SUM(T1.Debit) - sum(T1.Credit)) AS '2009',
((SUM(T1.Debit) - sum(T1.Credit))/12) AS '2009 Avg', (SUM(T2.Debit) - sum(T2.Credit)) AS '2010',
((SUM(T2.Debit) - sum(T2.Credit))/Month(GetDate()))AS '2010 Avg' FROM dbo.OCRD T0
LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Year(T1. Duedate) = 2009 AND T1.TransType in ('13','14')
LEFT JOIN dbo.JDT1 T2 ON T2.ShortName = T0.CardCode AND Year(T2. Duedate) = 2010 AND T2.TransType in ('13','14')
WHERE T0.CardType = 'C'
Group By T0.CardCode, T0.CardName
This query structure is similar to the previous example. The concept is the same. You can always use two separate joins to the same table by different aliases and selections to get the different scopes of data under the same line of query result. Maybe your company also has a requirement to see the average sales per month. This average is a good tool for the sales personnel to forecast and project their upcoming year.
Case 4-M10: Credit Memo user check
This example is to show the credit memo, document number, and the change log user name for the credit memo.
A user had tried to use the ADOC table but could not find the link between this and the credit memo.
SELECT T0.[DocNum], T0.[DocType], T0.[ObjType], T0.[CardCode], T0.[CardName], T0.[UserSign], T0.[UserSign2]
FROM ADOC T0 WHERE T0.[ObjType] = '14'
This query does not show the same data as the change log screen. With the query, updated user is always the same user as the original user.
The solution is:
INNER JOIN dbo.ORIN T1 ON T0.DocNum = T1.DocNum
WHERE T0.[ObjType] = '14' AND T0.[UserSign2] != T1.[UserSign2] The first query just looks at the historical log of credit memos from ADOC. It therefore could not get the required information.
In the solution query, by joining the ORIN table, that is, the current credit memo table and adding the column from this table, the query result should satisfy the needs. The condition T0.[UserSign2] != T1.[UserSign2] will list all records in the query
results if the user who updates the document is not the same as the current one. This is a good example of what many users are looking for in the instance of who has changed a document. It is an extension to "Change Log" for showing all changes.
Case 4-M11: Delivery date on sales order
A user requests an SQL query that identifies when the delivery date on a Sales Order is modified from the original date. This query needs to list who made the change and the date of the change. It will be a base query for an alert in case any changes are made on a sales order.
It has been doubly confirmed that the query results do not need to include the delivery dates from the initial set up of a Sales Order.
The solution is:
SELECT T0.DocNum,
MAX(T1.UpdateDate) 'Last Update' From dbo.ADOC T0
JOIN dbo.ADOC T1 on T1.DocNum = T0.DocNum AND T1.ObjType = '17' WHERE T0.ObjType = '17' and T0.DocDuedate != T1.DocDueDate Group BY T0.DocNum
In this solution query, a self join is used for the ADOC table. T0 and T1 are all referred to in the same table. The query checks the same document number with the object type of '17', that is, sales order records. If the query finds that the DocDueDates are not the same, the results of the document number and the latest
Be careful to run this query if you have a long history and a large size database. ADOC table has too many records. Self join of this table is not recommended. Unless you know exactly what you are looking for, do not try any other self join of ADOC. This applies to AJDT and AJD1 tables too.