Skip to Content

Need to link Incoming payment with this Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Nov 29, 2017 at 06:55 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 29, 2017 at 08:41 AM

    Hi Jitin

    Thanks for the support

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

    Regards

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 29, 2017 at 10:57 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 02, 2017 at 05:39 AM

    Hi experts

    Request any one help me with a solution,

    Regards

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 30, 2017 at 01:04 AM

    Hi,

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

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded