on 05-06-2022 1:37 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Hi all
can anyone help me to convert the above query into hana?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
99 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.