Skip to Content
0

Need to link Incoming payment with this Query

Nov 29, 2017 at 05:53 AM

145

avatar image

Dear Experts

I need to link Incoming payment with my Existing Query.

Pls help me to sort it out

I try so many times

Regards


Select distinct OCRD.CardName [Customer Name] ,OCRD.CardCode [Customer Code]
,ODLN.DocNum [Delivery Num],ODLN.DocDate [Delivery Date],ODLN.DocTotal [Delivery Total],OINV.DocNum [Invoice Num],OINV.DocDate [Invoice Date],OINV.DocTotal [Invoice Total] 
,ORIN.DocNum [CNote Num],ORIN.DocDate [CNote Date],ORIN.DocTotal [CNote Total]
From 
OCRD inner join ODLN on OCRD .CardCode = ODLN .CardCode 
left outer join DLN1 on ODLN .DocEntry = DLN1 .DocEntry 
Left outer join INV1 on DLN1 .DocEntry = INV1 .BaseEntry and DLN1 .LineNum = INV1 .BaseLine and DLN1 .ObjType = INV1 .BaseType 
Left outer join OINV on INV1 .DocEntry = OINV .DocEntry
Left Join ORIN on ORIN.DocEntry = INV1.TrgetEntry and ORIN.ObjType = INV1.TargetType

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Jitin Chawla
Nov 29, 2017 at 06:55 AM
0

Hi,

Try using ORCT & RCT2 table with the RCT2. DocEntry = OINV. DocEntry. This will be however only giving you the list of Invoices which are included in the Payment. Those Invoices which are paid using the Payment on Account will not be available with above link.

If you are not able to check OITR and ITR1 where the record for the reconciliations are available.

Regards,

Jitin

Share
10 |10000 characters needed characters left characters exceeded
P Vinod Kumar Nov 29, 2017 at 08:41 AM
0

Hi Jitin

Thanks for the support

Can you pls provide me in Query, am not much expert in sql

Regards

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Nov 29, 2017 at 10:57 AM
0

Check this query:

Select distinct OCRD.CardName [Customer Name] ,OCRD.CardCode [Customer Code] ,ODLN.DocNum [Delivery Num],ODLN.DocDate [Delivery Date],ODLN.DocTotal [Delivery Total],OINV.DocNum [Invoice Num],OINV.DocDate [Invoice Date],OINV.DocTotal [Invoice Total] ,ORIN.DocNum [CNote Num],ORIN.DocDate [CNote Date],ORIN.DocTotal [CNote Total],t1.DocNum as 'Incoming Payment', t1.DocDate as 'Payment Date', t1.DocTotal as 'Total' From OCRD inner join ODLN on OCRD .CardCode = ODLN .CardCode left outer join DLN1 on ODLN .DocEntry = DLN1 .DocEntry Left outer join INV1 on DLN1 .DocEntry = INV1 .BaseEntry and DLN1 .LineNum = INV1 .BaseLine and DLN1 .ObjType = INV1 .BaseType Left outer join OINV on INV1 .DocEntry = OINV .DocEntry Left Join ORIN on ORIN.DocEntry = INV1.TrgetEntry and ORIN.ObjType = INV1.TargetType LEFT JOIN ORCT T1 ON OINV.ReceiptNum = T1.DocEntry LEFT JOIN RCT1 T2 ON T1.DocNum = T2.DocNum

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Vedanth

In this some invoice is Duplicating, more than 1 time.

Regards

0
P Vinod Kumar Dec 02, 2017 at 05:39 AM
0

Hi experts

Request any one help me with a solution,

Regards

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Dec 30, 2017 at 01:04 AM
0

Hi,

Please check this thread https://archive.sap.com/discussions/thread/3439126

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded