Skip to Content
0

Link Return Document (Sales) with the Query

Dec 15, 2016 at 05:43 AM

155

avatar image

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'

10 |10000 characters needed characters left characters exceeded

Hi experts

Pls any Solution

Regards

0

Hi Experts

Any update , pls provided

Regards

0

Any updates

Regards

0
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Zuber Kazi Jan 16, 2017 at 06:11 AM
0

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'

Share
10 |10000 characters needed characters left characters exceeded
Zuber Kazi Dec 17, 2016 at 11:50 AM
0

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

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

Dear Zuber

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

Regards

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

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

0

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

0

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

0

Dear Shahan,

Please share relationship map of any sales cycle.

Regards,

Zuber Kazi

0
Show more comments