3,015
I need help getting started in creating a report for sales payments. The report needs to look like this:
Customer Purchase Order Number - Customer Name - Master Sales Rep ID
| Document # | Document Date | Check # | Batch # | Invoice amount | Amount paid | Balance |
All the info that I need are contained in tables: RM30101 and RM20201. I am new to SQL and am not very good at combining tables yet. A simple example would be very helpful.
*This post is locked for comments
Or if you want to use the original view and just want to select just the checks:
Select * from view_AR_Apply_Detail
Where Document_Type_and_Number like ‘%Check%’
Hi Isaac,
1. When you are using multiple tables, you will often have a situation where the same column is in more than one table. If you do not tell SQL which table to look at, you will get errors saying that the column name is ambiguous. Instead of referring to the entire table name every single time, you can give them shorter names. You can also do the same thing when you are combining tables, etc. For example, in my code, I am combining information from RM20101 and RM30101 with a union - all of that is named T. When I need to refer to a column from that section of code, I put 'T.' in front of the column name, so T.CUSTNMBR is getting the customer number from that section of code. Further down in my code you can see that I am linking in table RM00101 to get the customer name...right after the table name I put 'CM' - so now instead of typing 'RM00101' in front of the column names, I can put 'CM'.
A common practice is to put the short names in front of every column in your select statement, even if the column is only in one table. This makes it easier to know where the column is coming from later when you need examine or make changes to the code.
2. The reason you're not able to add the check number is that I have not actually added it separately to the data that I am gathering. The code below will replace the Document_Type_and_Number with the check number:
SELECT T.CUSTNMBR Customer_ID,
CM.CUSTNAME Customer_Name,
T.DOCDATE Document_Date,
T.GLPOSTDT GL_Posting_Date,
CASE T.RMDTYPAL
WHEN 7 THEN 'Credit Memo'
WHEN 8 THEN 'Return'
WHEN 9 THEN 'Payment'
END AS RM_Doc_Type,
T.BACHNUMB Payment_Batch,
T.CHEKNMBR Check_Number,
T.DOCNUMBR Document_Number,
T.ORTRXAMT Original_Trx_Amount,
T.CURTRXAM Current_Trx_Amount,
T.amountApplied Total_Applied_Amount,
A.APPTOAMT Amount_Applied,
A.APTODCTY Applied_to_Doc_Type,
A.debitType Applied_to_Doc_Type_Name,
A.APTODCNM Applied_to_Doc_Number,
A.APTODCDT Applied_to_Document_Date,
A.ApplyToGLPostDate Applied_to_GL_Posting_Date,
A.DISTKNAM Discount,
A.WROFAMNT Writeoff,
A.DATE1 Apply_Document_Date,
A.GLPOSTDT Apply_GL_Posting_Date,
D.ORTRXAMT Applied_To_Doc_Total,
D.DINVPDOF Applied_To_Date_Paid_Off,
D.CURTRXAM Applied_To_Doc_Unapplied_Amount,
D.CSPORNBR Customer_PO_Number
FROM
(SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL, CHEKNMBR, DOCNUMBR,
ORTRXAMT, CURTRXAM, BACHNUMB, ORTRXAMT - CURTRXAM amountApplied
FROM RM20101
WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0)
UNION
SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL, CHEKNMBR, DOCNUMBR,
ORTRXAMT, CURTRXAM, BACHNUMB, ORTRXAMT - CURTRXAM amountApplied
FROM RM30101
WHERE (RMDTYPAL > 6) and (VOIDSTTS = 0)) T
INNER JOIN RM00101 CM
ON T.CUSTNMBR = CM.CUSTNMBR
INNER JOIN
(SELECT tO1.CUSTNMBR, APTODCTY, APTODCNM, APFRDCTY,APFRDCNM,
CASE APTODCTY
WHEN 1 THEN 'Sale / Invoice'
WHEN 2 THEN 'Scheduled Payment'
WHEN 3 THEN 'Debit Memo'
WHEN 4 THEN 'Finance Charge'
WHEN 5 THEN 'Service Repair'
WHEN 6 THEN 'Warranty'
END as debitType,
APPTOAMT, ApplyToGLPostDate, APTODCDT, tO2.DISTKNAM, tO2.WROFAMNT, tO2.DATE1, tO2.GLPOSTDT
FROM RM20201 tO2
INNER JOIN RM20101 tO1
ON tO2.APTODCTY = tO1.RMDTYPAL AND tO2.APTODCNM = tO1.DOCNUMBR
UNION
SELECT tH1.CUSTNMBR, APTODCTY, APTODCNM, APFRDCTY, APFRDCNM,
CASE APTODCTY
WHEN 1 THEN 'Sale / Invoice'
WHEN 2 THEN 'Scheduled Payment'
WHEN 3 THEN 'Debit Memo'
WHEN 4 THEN 'Finance Charge'
WHEN 5 THEN 'Service Repair'
WHEN 6 THEN 'Warranty'
END AS debitType,
APPTOAMT, ApplyToGLPostDate, APTODCDT, tH2.DISTKNAM, tH2.WROFAMNT, tH2.DATE1, tH2.GLPOSTDT
FROM RM30201 tH2
INNER JOIN RM30101 tH1
ON tH2.APTODCTY = tH1.RMDTYPAL AND tH2.APTODCNM = tH1.DOCNUMBR) A
ON A.APFRDCTY = T.RMDTYPAL and A.APFRDCNM = T.DOCNUMBR
INNER JOIN
(SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, CURTRXAM, CSPORNBR
FROM RM20101
UNION
SELECT RMDTYPAL, DOCNUMBR, ORTRXAMT, DINVPDOF, CURTRXAM = 0, CSPORNBR
FROM RM30101) D
ON A.APTODCTY = D.RMDTYPAL and A.APTODCNM = D.DOCNUMBR
I knew Victoria would come through with the answer! :)
Wow, thank you Victoria! That was exactly what we needed. You just saved me so much headache.
Two questions....
1. How do you know when to put and and what letter to put in front of each selection (i.e. CM.CUSTNMBR, D.CSPORNBR). Where does the CM, D, A, and T come from?
2. While I do see that the check number is on "document type and number" is there a way to show only the check number? We don't care to see the document type. We just want the check number. I tried adding the check number to the selection but every letter that I put in front of my selection results in an error. I tried....
D. CHEKNMBR check_number
T. CHEKNMBR check_number
CM. CHEKNMBR check_number
A. CHEKNMBR check_number
All give me an error stating the CHEKNMBR is not a valid column.
Thank you again!
I just updated the code on my blog to include the batch number and customer PO number. The check number is already in there - it's combined with the payment type in the Document_Type_and_Number column.
Unfortunately, what you're asking for involves both unions and joins, so 'a simple example' is difficult to give, as it will then be missing data.
Also, I would be surprised if all the data you need is only in the RM30101 and RM20201. At the very least you will also need RM20101 and RM30201.
Check number is included under "Document_Type_and_Number" along with Payment Number under "Document_Number". As for Customer Po Number, I am not quite sure that it is relevant in here since the view is customized to show the applied details for all receivable transactions. Customer PO number is included in the SOP tables.
I've looked at Victoria's. It unfortunately does not include the customer PO number. I've tried putting it in myself but keep getting errors.
It doesn't have the check number and payment batch number either.
Do you know how I would insert these into her sample report? Again, I am new to SQL and that query is way beyond me.
Try this view that Victoria Yudin has on her website. She has tons of goodies like this on her blog.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (