Linkin between base document with target document query

[former_member419030]

Dear Experts,

When we do query to link between base document and target document , what is differences between

1) inner join tableB on tableA.Trgetentry=tableB.DocEntry

and

2) inner join tableB on tableA.DocEntry=tableB.BaseEntry

?

why these two will return different result?

Thank you

[former_member390407]

Hi there,

Let's imagine we have a sales order based on the sales quotation and copied to the sales invoice. In this case you will have BaseEntry = Quotation.DocEntry, BaseType = Quotation.ObjectType, TrgetEntry = Invoice.DocEntry, TargetType = Invoice.ObjectType.

Your first query for our case returns a join between order and AR invoice tables, when the second query returns a join between order and quotation tables.

[former_member419030]

Dear Sergei,

thank you for your answer, 😉

Based on your sample, which is sale order, let say i want to query the target document for this SO-->INVOICE.

BaseEntry = Quotation.DocEntry, BaseType = Quotation.ObjectType, TrgetEntry = Invoice.DocEntry, TargetType = Invoice.ObjectType.

Thus, my query will look like this

inner join RDR1.TRGETENTRY=INV1.DOCENTRY right?

what if i changed it into like this:

inner join RDR1.DOCENTRY=INV1.BASEENTRY

Basically, it is the similar thing right?

I did the testing, and these two will lead into different results especially when the SO has many invoices linked into it.

I could not understand the differences.

Do you know?

Thank you so much.

[former_member390407]

Hi MD Amall,

First of all, it's better to join with the header table otherwise you'll get a Cartesian product of the lines.

So I would right your query like the following based on this recommendation:

RDR1.TRGETENTRY=OINV.DOCENTRY or ORDR.DOCENTRY=INV1.BASEENTRY

Secondly, you also need to analyse the BaseType and TargetType fields, cause they can have different object types with the same DocEntry.

What is the use case for your query? I usually use the BaseEntry field but it depends on the query purpose.

[former_member419030]

Dear Sergei,

Thank you for your reply.

My query purpose is actually for SP Notification.

I want to block delivery order that has more quantity than sale order.

Thus, i had put 3 condition

1) ODLN-->OINV

2)ODLN --> ODRN

3)ODLN-->OINV-->OCRN.

The no 1) and 2) return correct value and blocked any delivery with greater quantity than SO.

Only condition no 3) is not working.

[Former Member]

Hi,
Use Following Store procedure to block delivery greater then So Quantity

-------------------------Delivry QTY Greater than SO--------------------------------------------------
IF @transaction_type IN ('A','U') AND @Object_type = '15'
BEGIN
	
	IF exists (
select b.BaseEntry,b.ItemCode
from ODLN a left outer join DLN1 b on a.docentry = b.docentry 


where a.Docentry = @list_of_cols_val_tab_del 


group by b.BaseEntry,b.ItemCode 


having ((select sum(isnull(((c.quantity)),0)) from RDR1 c where c.Docentry = b.BaseEntry and c.Itemcode=b.ItemCode) -
(select isnull(sum(b1.quantity),0) from  ODLN a1 left outer join DLN1 b1 on a1.docentry = b1.docentry where a1.canceled = 'N' and b1.BaseEntry = b.BaseEntry and b1.Itemcode=b.ItemCode))<0


	
	)
	BEGIN


	select @error = 22,@error_message = 'Delivery Quantity is greater than SO!'
END
END

[former_member390407]

Hi MD Amall,

Now I'm really confused with your tables.

ODLN - Delivery notes (ok)

OINV - AR Invoice (ok)

ODRN - Depreciation run (why?)

OCRN - Currency codes (why?)

[former_member419030]

Dear Sergei,

Thank you for pointing out my typo and Sory for the confusion.

it was atually ORDN (Return) and ORIN (Credit Memo)

[former_member419030]

Dear Riasat Ali,

Thank you for your reply. 😉

Your SP Notification only block normal delivery.

However, sometimes we do returns and credit memo for the documents.

I did checked, if the delivery has returns and credit memo, need to include the tables inside the SP notification, to calculate the return quantity.

[former_member390407]

Anyway I'm not sure if you need this joins for your purpose. Please, check the query below (it takes data from delivery notes and sales orders only since you cannot create a delivery based on an invoice).

[former_member390407]

Ok for your TN you can use the following:

IF @object_type = 15 AND @transaction_type = 'A'
BEGIN 
	SELECT TOP 1
		@error = 1,
		@error_message = N'Delivery quantity is not the same with order quantity in line #' + CAST(RDR1.VisOrder AS NVARCHAR(30))
	FROM DLN1
	JOIN RDR1 ON DLN1.BaseEntry = RDR1.DocEntry
		AND DLN1.BaseLine = RDR1.LineNum
		AND DLN1.BaseType = RDR1.ObjType
		AND DLN1.Quantity != RDR1.Quantity  -- Here is your quantity condition
	WHERE DLN1.DocEntry = @list_of_cols_val_tab_del	
END

[former_member419030]

Dear Sergei,

How about when the delivery got credit memo?

ORDR--> ODLN-->OINV-->ORIN.

My SP Notification looks like below.

It it correct?

 SET @CNINVQTY = (SELECT SUM(T0.QUANTITY) 
FROM RIN1 T0 
INNER JOIN INV1 T1 ON T0.BASEENTRY = T1.DOCENTRY 
INNER JOIN DLN1 T2 ON T1.BaseEntry=T2.DOCENTRY
AND T1.BASELINE = T2.LINENUM AND T1.BASETYPE = '15'
 WHERE T2.BASETYPE = '17' AND T2.BASEENTRY = @SOENTRY AND T2.BASELINE = @SOLINE AND T2.ITEMCODE = @SOITMCD)

[former_member390407]

If you want to restrict deliveries why do you check the documents after?

[former_member419030]

Dear Sergei,

I need check for return and credit memo, so that I can create a formula to recalculate the SO quantity.

If I only check for DO, system will block the document with return and credit memo even though logically we are allowed to do that.

Roughly, my formula will look like this.

if Do.qty - (return.qty+creditmemo.qty) > SO.qty then YES (Block user from adding DO)

[Abdul]

Hi

I make Following joins and it gives correct results

Select * From ORDR inner Join RDR1 on ORDR.DocEntry = RDR1.DocEntry Inner Join DLN1 on RDR1.DocEntry = DLN1.BaseEntry and ORDR.ObjType = DLN1.BaseType

[former_member419030]

Dear Abdul Mannan,

Does the SO only has one DO?

How about if the SO linked to many DO?

[Abdul]

It will work for multiple DOs as well but it will shows SO for which there is DO if you want to see SO without DO also thn try this

Select * From ORDR inner Join RDR1 on ORDR.DocEntry = RDR1.DocEntry Left Outer Join DLN1 on RDR1.DocEntry = DLN1.BaseEntry and ORDR.ObjType = DLN1.BaseType