Skip to Content
avatar image
Former Member

Link Return Document (Sales) with the Query

Hi experts

Pls help me to add Sales Return in this query

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
Where ODLN.DocDate between '2016-01-01 00:00:00.000' and '2016-11-30 00:00:00.000'

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 16, 2017 at 06:11 AM

    Select distinct OCRD.CardName [CustomerName],OCRD.CardCode [CustomerCode],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] ,ORDN.DocNum [Sale Return Num] ,ORDN.DocDate [Sale Return Date],ORDN.DocTotal [Sale Return Total] From OCRD inner join ODLN on OCRD .CardCode = ODLN .CardCode left outer join DLN1 on ODLN .DocEntry = DLN1 .DocEntry Left outer join RDN1 on DLN1 .DocEntry = RDN1.BaseEntry and DLN1 .LineNum = RDN1.BaseLine and DLN1 .ObjType = RDN1.BaseType Left outer join ORDN on RDN1 .DocEntry = ORDN .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 outer join RIN1 on INV1 .DocEntry = RIN1.BaseEntry and INV1 .LineNum = RIN1.BaseLine and INV1 .ObjType = RIN1.BaseType Left Join ORIN on ORIN.DocEntry = RIN1.Docentry and ORIN.ObjType = INV1.TargetType Where ODLN.DocDate between'2016-01-01 00:00:00.000'and'2017-11-30 00:00:00.000'

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 17, 2016 at 11:50 AM

    Dear Shahan,

    Why don't u used RIN1 table as u used INV1.

    Leftouterjoin RIN1 on INV1 .DocEntry = RIN1.BaseEntry and INV1 .LineNum = 

    RIN1.BaseLine and INV1 .ObjType = RIN1.BaseType

    After this use ORIN as u already used it

    Regards,

    Zuber Kazi

    Add comment
    10|10000 characters needed characters exceeded