Link Payment with Invoice and Credit memo which have both reconciled and non-reconciled transactions

jinia · November 14, 2018, 7:26pm

Hi all,

I really need your help on this please.

I need to create the query to match invoice and credit note with Payment and I also need to see invoice amount with profit, terms and net after all expenses. However, there are transactions with reconciled and non-reconciled also Journal entry to balance out the payment. Please see below for what I have now which I know I am missing the Journal Entry (JE), reconcile parts and I also see JR (I guess it means Journal reconcile?) and the expenses from AP which I have either PO# or Invoice# to match back to Incoming Payment.

SELECT IsNull(T2.DocNum, T4.DocNum) AS 'Invoice/Credit#', T2.[NumAtCard] AS 'PO#', T0.[DocNum] AS 'Incoming payment#', T0.[CardCode], T0.[CardName], T2.[DocTotal] As 'Invoice amount', T2.[GrosProfit], (T2.[GrosProfit] / T2.[DocTotal]) * 100 AS 'Profit %', T1.[Dcount] AS 'Terms%', T1.[DcntSum], CASE WHEN T2.DocNum>0 then 1 else -1 end * T1.SumApplied AS 'Amount Paid', T0.[CreateDate] AS 'Incoming payment creation date', IsNull(T2.CreateDate, T4.CreateDate) AS 'Invoice/Credit creation date', T5.[SlpName] FROM ORCT T0 LEFT JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum LEFT JOIN dbo.OINV T2 ON T1.[DocEntry] =T2.[DocEntry] AND T1.Invtype = 13 LEFT JOIN dbo.ORIN T4 ON T1.[DocEntry] =T4.[DocEntry] AND T1.Invtype = 14 LEFT JOIN dbo.OCRD T3 ON T0.CardCode = T3.CardCode Left JOIN OSLP T5 ON T2.[SlpCode] = T5.[SlpCode] LEFT JOIN RCT1 T6 ON T0.DocEntry = T6.DocNum LEFT JOIN RCT3 T7 ON T0.DocEntry = T7.DocNum LEFT JOIN RCT4 T8 ON T0.DocEntry = T8.DocNum WHERE (T0.[CreateDate] >=[%0] AND T0.[CreateDate] <=[%1]) Order by T0.[CreateDate], T0.[DocNum], T4.[DocNum]

Please advise and I really appreciate all your helps! Thank you very much.

bitsmed · November 14, 2018, 8:38pm

Please provide:

table definitions in the form of create statements
sample data in the form of insert statements
expected output (from the sample data you provide)
description of how to join tables not included in the query you provided
version of your Microsoft SQL Server

jinia · November 14, 2018, 9:03pm

Hi,
This is the format I got so far.

And you can see the part I did not delete out is no data shown.

It is SAP business one 8.82.

I am not sure for your second last question as they are all joined now.

yosiasz · November 14, 2018, 9:10pm

this is what is being asked of you.

bitsmed · November 14, 2018, 9:15pm

Sorry, I can't insert your picture into any tables to build a test environment in order to help you

jinia · November 14, 2018, 9:18pm

Would you be able to build SQL based on my code then?

bitsmed · November 14, 2018, 9:29pm

Probably, if you supply the things I requested, and most importantly; supply representative data and we'll have a better chance of helping

jinia · November 14, 2018, 9:35pm

I am sorry, I don't understand your questions as I am very new to this ERP, what I provided already all I know about. If you could provide an example table I could follow of?

yosiasz · November 14, 2018, 9:37pm

provide sample data is what you are being asked

in order to do the following, we need you to provide sample tables and sample data to work with to provide you an answer

FROM ORCT T0 
LEFT JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum 
LEFT JOIN dbo.OINV T2 ON T1.[DocEntry] =T2.[DocEntry] AND T1.Invtype = 13 
LEFT JOIN dbo.ORIN T4 ON T1.[DocEntry] =T4.[DocEntry] AND T1.Invtype = 14 
LEFT JOIN dbo.OCRD T3 ON T0.CardCode = T3.CardCode 
Left JOIN OSLP T5 ON T2.[SlpCode] = T5.[SlpCode] 
LEFT JOIN RCT1 T6 ON T0.DocEntry = T6.DocNum 
LEFT JOIN RCT3 T7 ON T0.DocEntry = T7.DocNum 
LEFT JOIN RCT4 T8 ON T0.DocEntry = T8.DocNum

yosiasz · November 14, 2018, 9:41pm

create table orct([DocNum] varchar(50),DocEntry varchar(25), [CardCode] varchar(50),  [CardName] varchar(50), CreateDate datetime)
create table RCT2(DocNum varchar(50), Invtype varchar(25) )
create table OINV(DocNum varchar(50), [DocTotal] int, [GrosProfit] money, [SlpCode] varchar(2))

--then inserts with real or near to real data
insert into orct

jinia · November 14, 2018, 10:09pm

Hi,

Thanks for your reply.

I am not sure if you could use the code I provided to create the table in SAP then you will get exact same result as I show you the table above. I also trying to provide you the sample data as per below.

Customer A pay money let say $10 but they pay in lump sum which could go to a few invoice (OINV) and credit memo(ORIN). So we have to receive money in incoming payment (ORCT) and then reconcile it (OJDT). Now, because I am not sure the relationship for the table how could I pull the data for invoice# and credit memo under reconcile table to be showing under column invoice#/credit# also the amount. So you can see the table I show you for line# 3 only show lump sum amount but you have no data it is for which invoice or credit memo. Is this give you better information? By the way, what is Varchar...

#	Invoice/Credit#	PO#	Incoming payment#	Customer/Vendor Code	Customer/Vendor Name	Invoice amount	Gross Profit	Profit %	Terms%	Discount Amount	Amount Paid	Non-Calculated Amount	Incoming payment creation date	Invoice/Credit creation date
1	1	1.1	1234	A	AA	10	2	1	1	3	8	0	1/3/2018	12/1/2017
2	2	1.2	1234	A	AA	15.00	10	3	2	5	10.00	0	1/3/2018	12/1/2017
3			1236	B	BB	0	0	0	0	0	0	20,000.00	1/3/2018

yosiasz · November 14, 2018, 10:11pm

please provide the sample data as insert command(s) not as an image? then we would have to do all of that work for you.

jinia · November 14, 2018, 10:14pm

I am sorry but I do not know how to do..

yosiasz · November 14, 2018, 10:18pm

create table #orct([DocNum] varchar(50),DocEntry varchar(25), [CardCode] varchar(50),  [CardName] varchar(50), CreateDate datetime)
create table #RCT2(DocNum varchar(50), Invtype varchar(25) )
create table #OINV(DocNum varchar(50), [DocTotal] int, [GrosProfit] money, [SlpCode] varchar(2))

--then inserts with real or near to real data
insert into #orct
select 1234, 1234, 'A', 'Whatever Card Name', getdate() union
select 1236, 1236, 'A', 'Whatever Card Name', getdate() 
--next data set etcv

jinia · November 14, 2018, 10:25pm

This is an exact command I put in SAP

SELECT IsNull(T2.DocNum, T4.DocNum) AS 'Invoice/Credit#', T2.[NumAtCard] AS 'PO#', T0.[DocNum] AS 'Incoming payment#', T0.[CardCode], T0.[CardName], T2.[DocTotal] As 'Invoice amount', T2.[GrosProfit], (T2.[GrosProfit] / T2.[DocTotal]) * 100 AS 'Profit %', T1.[Dcount] AS 'Terms%', T1.[DcntSum], CASE WHEN T2.DocNum>0 then 1 else -1 end * T1.SumApplied AS 'Amount Paid', T0.[CreateDate] AS 'Incoming payment creation date', IsNull(T2.CreateDate, T4.CreateDate) AS 'Invoice/Credit creation date', T5.[SlpName] FROM ORCT T0 LEFT JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum LEFT JOIN dbo.OINV T2 ON T1.[DocEntry] =T2.[DocEntry] AND T1.Invtype = 13 LEFT JOIN dbo.ORIN T4 ON T1.[DocEntry] =T4.[DocEntry] AND T1.Invtype = 14 LEFT JOIN dbo.OCRD T3 ON T0.CardCode = T3.CardCode Left JOIN OSLP T5 ON T2.[SlpCode] = T5.[SlpCode] LEFT JOIN RCT1 T6 ON T0.DocEntry = T6.DocNum LEFT JOIN RCT3 T7 ON T0.DocEntry = T7.DocNum LEFT JOIN RCT4 T8 ON T0.DocEntry = T8.DocNum WHERE (T0.[CreateDate] >=[%0] AND T0.[CreateDate] <=[%1]) Order by T0.[CreateDate], T0.[DocNum], T4.[DocNum]

???? Did I misunderstand anything???

yosiasz · November 14, 2018, 11:06pm

we do not have access to your SAP and your database and your tables so we cannot do

FROM ORCT T0 
LEFT JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum 
LEFT JOIN dbo.OINV T2 ON T1.[DocEntry] =T2.[DocEntry] 
AND T1.Invtype = 13 LEFT JOIN dbo.ORIN T4 ON T1.[DocEntry] =T4.[DocEntry] AND T1.Invtype = 14 LEFT JOIN dbo.OCRD T3 ON T0.CardCode = T3.CardCode 
Left JOIN OSLP T5 ON T2.[SlpCode] = T5.[SlpCode] 
LEFT JOIN RCT1 T6 ON T0.DocEntry = T6.DocNum 
LEFT JOIN RCT3 T7 ON T0.DocEntry = T7.DocNum 
LEFT JOIN RCT4

harishgg1 · November 15, 2018, 11:22am

hi

the first thing i have done for you is FORMAT the sql
becomes easier to read and understand

Reading the whole post
looks like you need guidance on how to debug issues

All people including experts face such issues
all the time

We all need help from a lot of people

Things like how to understand stuff we dont understand

how to understand what we dont understand

WHAT TO DO WHEN YOU DON'T UNDERSTAND WHAT YOU'RE READING

http://www.shsu.edu/centers/testing-center/tips/dontk5.html

having said that

Formatted SQL

SELECT Isnull(T2.docnum, T4.docnum)              AS 'Invoice/Credit#', 
       T2.[numatcard]                            AS 'PO#', 
       T0.[docnum]                               AS 'Incoming payment#', 
       T0.[cardcode], 
       T0.[cardname], 
       T2.[doctotal]                             AS 'Invoice amount', 
       T2.[grosprofit], 
       ( T2.[grosprofit] / T2.[doctotal] ) * 100 AS 'Profit %', 
       T1.[dcount]                               AS 'Terms%', 
       T1.[dcntsum], 
       CASE 
         WHEN T2.docnum > 0 THEN 1 
         ELSE -1 
       END * T1.sumapplied                       AS 'Amount Paid', 
       T0.[createdate]                           AS 
       'Incoming payment creation date', 
       Isnull(T2.createdate, T4.createdate)      AS 
       'Invoice/Credit creation date', 
       T5.[slpname] 
FROM   orct T0 
       LEFT JOIN rct2 T1 
              ON T0.docentry = T1.docnum 
       LEFT JOIN dbo.oinv T2 
              ON T1.[docentry] = T2.[docentry] 
                 AND T1.invtype = 13 
       LEFT JOIN dbo.orin T4 
              ON T1.[docentry] = T4.[docentry] 
                 AND T1.invtype = 14 
       LEFT JOIN dbo.ocrd T3 
              ON T0.cardcode = T3.cardcode 
       LEFT JOIN oslp T5 
              ON T2.[slpcode] = T5.[slpcode] 
       LEFT JOIN rct1 T6 
              ON T0.docentry = T6.docnum 
       LEFT JOIN rct3 T7 
              ON T0.docentry = T7.docnum 
       LEFT JOIN rct4 T8 
              ON T0.docentry = T8.docnum 
WHERE  ( T0.[createdate] >= [%0] 
         AND T0.[createdate] <= [%1] ) 
ORDER  BY T0.[createdate], 
          T0.[docnum], 
          T4.[docnum]

harishgg1 · November 15, 2018, 11:31am

hi

now that i have formatted your sql

please tell your issues in a way that i can understand

simple 1 line
OR
one word two word three word
statements

not making any sense
i can try to help without data

need to create the query to match invoice and credit note with Payment and I also need to see invoice amount with profit, terms and net after all expenses. However, there are transactions with reconciled and non-reconciled also Journal entry to balance out the payment. Please see below for what I have now which I know I am missing the Journal Entry (JE), reconcile parts and I also see JR (I guess it means Journal reconcile?) and the expenses from AP which I have either PO# or Invoice# to match back to Incoming Payment.