FORMATTED SEARCH QUERY FOR UDV LINK ORDR AND OINV

[kingastrong]

Hello

One of our customer , want in Incoming Payments documents row level , a column that display all Sales order related to the invoice .

The incoming payments documents only display Invoice to be paid and it's difficult for our customer to see which Invoice is related to a specific sales order

So i created an UDF called COM and i want this UDF to show all ORDR related to the Invoice ID from the same line num .

Can you please help ?

Thanks

[Johan_Hakkesteegt]

Hi,

The challenge here is that an invoice can be linked to a Quotation, a Sales Order, or a Delivery Note. It can also be created directly, and not be linked to anything.

Does your customer create Delivery Notes, or do they always copy Sales Orders directly to Invoices? Does your customer ever copy Quotations directly to invoices?

Regards,

Johan

[kingastrong]

Hi Johan

When creating a Sales Oder , the customer always copy them to one or multiple delivery notes then each delivery note is copied to an invoice . So all are linked .

So for exemple : A Sales Order is copied to 5 Delivery then copied to 5 Invoice .

In the Incoming payment tab , they only see all the invoice and they would like to see the Sales order num related to them in a separate column.

[kingastrong]

In Case the invoice is not linked to any sales order , the field will be blank .

[kingastrong · Accepted solution]

Hello , i finally found solution for this . Check This out (this only work for ORDR- DLN - OINV scenario which is the scenario of our customer)

SELECT Distinct(T0."DocNum" ) 
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry" 
INNER JOIN ODLN T2 on T2."DocEntry" = T1."TrgetEntry"
INNER JOIN DLN1 T3 on T3."DocEntry" = T2."DocEntry"
INNER JOIN OINV T4 ON T4."DocEntry" = T3."TrgetEntry"
INNER JOIN INV1 T5 ON T5."DocEntry" = T4."DocEntry"
LEFT JOIN ORDN T6 ON T6."DocEntry" = T5."TrgetEntry"
LEFT JOIN RDN1 T7 ON T7."DocEntry" = T6."DocEntry"
where T4."DocNum" = $[$20.1.0]

Regards

[LoHa]

Hi Samba,

try this one

DECLARE @DocNum AS INT
DECLARE @String AS NVARCHAR(max)

SET @DocNum = $[$20.1.0]
SET @String = ''


/*Invoice -> DelNote -> SalesOrder*/
SELECT
@String =
		ISNULL(
		(SELECT STUFF((
		SELECT
		Distinct
		', SO-' + CAST(ORDR.DocNum AS nvarchar(max))
		FROM
		OINV
		INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry
		LEFT JOIN DLN1 ON DLN1.DocEntry = INV1.BaseEntry AND DLN1.LineNum = INV1.BaseLine AND DLN1.ObjType = INV1.BaseType
		LEFT JOIN ODLN ON ODLN.DocEntry = DLN1.DocEntry
		LEFT JOIN RDR1 ON RDR1.DocEntry = DLN1.BaseEntry AND RDR1.LineNum = DLN1.BaseLine AND RDR1.ObjType = DLN1.BaseType
		LEFT JOIN ORDR ON ORDR.DocEntry = RDR1.DocEntry
		WHERE
		OINV.DocNum = @DocNum 
		FOR XML PATH ('')), 1, 2, '') AS InvDelNoteSalesOrder)
		,'')

/*Invoice -> SalesOrder*/
SELECT
@String = @String +
		ISNULL(
		(SELECT STUFF((
		SELECT
		Distinct
		', SO-' + CAST(ORDR.DocNum AS nvarchar(max))
		FROM
		OINV
		INNER JOIN INV1 ON INV1.DocEntry = OINV.DocEntry
		LEFT JOIN RDR1 ON RDR1.DocEntry = INV1.BaseEntry AND RDR1.LineNum = INV1.BaseLine AND RDR1.ObjType = INV1.BaseType
		LEFT JOIN ORDR ON ORDR.DocEntry = RDR1.DocEntry
		WHERE
		OINV.DocNum = @DocNum 
		FOR XML PATH ('')), 1, 2, '') AS InvSalesOrder)
		,'')


SELECT @String

it gets the sales order when it goes INV-DelNote-SalesOrder or INV-SalesOrder.

But there is an other problem left, if you use Series in your Invoice-Document the DocNum, it could be that the DocNum is not unique.

regards

Lothar

[Johan_Hakkesteegt]

Not to mention that if the invoice was based on a delivery that was itself created directly, instead of copied from a sales order, it will not show. Thus incorrectly suggesting that the invoice was created directly.

You can fix that with a coalesce for example, but then you need some way to distinguish between the document type of the number in the result.

Regards,

Johan

[kingastrong]

Thanks for your reply

Thankfully the customer does not use series , i will test that and comeback to you !

[LoHa]

@Johan, you are right. I didn't tought twice about it. Sorry

regards Lothar

[kingastrong]

Hello Lothar , could you please update your code to HANA SQL syntaxe type ?

Because when i use it in UDV , it's not working

[LoHa]

Hi Samba,

I'm sorry but I'am not sure about handling with Hana. I'm sure that parts of my syntax is not useable in Hana.

regards

Lothar

[medlemine]

Hi all

can anyone help me to convert the above query into hana?