Link Return Document (Sales) with the Query

[Former Member]

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'

[Former Member]

Hi experts

Pls any Solution

Regards

[Former Member]

Hi Experts

Any update , pls provided

Regards

[Former Member]

Any updates

Regards

[former_member324899 · Accepted solution]

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'

[former_member324899]

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

[Former Member]

Dear Zuber

Pls could u help me by editing the Query, AS i had try, but it got failuer

Regards

[former_member324899]

Here you go-


Selectdistinct 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]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 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'2016-11-30 00:00:00.000'

[Former Member]

Hi Zuber

I try by linking the [ Leftouterjoin RIN1 on INV1 .DocEntry = RIN1.BaseEntry and INV1 .LineNum ]

and also try to add orin table, but not processing

Pls could u help me by editing the query

Regards

[former_member324899]

Dear Shahan,

Query is ok. Run some sales cycles in following manner-

SO->Delivery->Invoice->Sales return

Then try this query-

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]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 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'

Make sure parameter date is delivery documents docdate.

Regards,

Zuber Kazi

[Former Member]

Hi Zuber

Thanks for the Support

the process u show is Absolute Correct

SO >>Delivery>>Invoice>>Sales Return(do u mean it Credit note)

i need like this way:

SO>>DELIVERY>>RETRUN>>INVOICE>>Sales Return(I consider this as Credit Note)

in which other than RETURN all are correct,but do not able to connect

Regards

[former_member324899]

Dear Shahan,

Please share relationship map of any sales cycle.

Regards,

Zuber Kazi

[Former Member]

Hi Zuber

Pls see the Attached File

if a Delivery note is created and if it return before Invoice, then we use Return Document else we use Credit Note

Regards

rel.png