SQLINFO function can be used to bring value to nonexistent fields in campaign card or in another table.
For Example: Using SQLINFO function to apply same discount to more than one material in an order or invoice.
Material Code Material Name
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- ----------------------*
PG.001 A
PG.002 B
PG.003 C
PG.004 D
PG.005 E
MC Code MC Description ----------------------*
PG PG
When a common campaign will be applied to material cards, they must be connected to material class card.
THE QUERY THAT LISTS MATERIAL – MATERIAL CLASS CONNECTION
SELECT
COUNT
FROM
LG_010_ITEMS I (NOLOCK)
LEFT OUTER JOIN
LG_010_ITMCLSAS C (NOLOCK) ON I.LOGICALREF=C.CHILDREF AND C.UPLEVEL=0
LEFT OUTER JOIN
LG_010_ITEMS S (NOLOCK) ON C.PARENTREF=S.LOGICALREF
WHERE
I.CODE LIKE 'PG.001'
AND S.CODE LIKE 'PG'
TRANSFORM QUERY TO FUNCTION
CREATE FUNCTION [dbo].[FN_MS_KAMPANYA_010] (@MALZ VARCHAR(21), @SINIF VARCHAR(21))
RETURNS FLOAT
AS
BEGIN
DECLARE @LG_FN_LINETOPLAM FLOAT
SET @LG_FN_LINETOPLAM=(
SELECT
COUNT
FROM
LG_010_ITEMS I (NOLOCK)
LEFT OUTER JOIN LG_010_ITMCLSAS C (NOLOCK) ON I.LOGICALREF=C.CHILDREF AND C.UPLEVEL=0
LEFT OUTER JOIN
LG_010_ITEMS S (NOLOCK) ON C.PARENTREF=S.LOGICALREF
WHERE
I.CODE LIKE @MALZ+'%'
AND S.CODE LIKE @SINIF+'%'
)
;
RETURN(@LG_FN_LINETOPLAM)
END
Note: .The function should be added to sql part as function by running it in SQL once.
TO RUN FUNCTION WITH QUERY
SELECT DBO.FN_MS_KAMPANYA_010('PG.004','PG') FROM L_CAPIFIRM WHERE NR=10
TRANSFORMING FUNCTION TO SQLINFO
VAL(_SQLINFO("DBO.FN_MS_KAMPANYA_010('"P101"','PG')","L_CAPIFIRM","NR=10"))
USING SQLINFO FUNCTION IN CAMPAIGN CARD
P101: Material Code info
Campaign application is provided by using IF(V5>0,1,0) function in condition field, 1 value is turned from V5 variable.
Parent material class code that the campaign will be applied to, has to be indicated in PG field located in V5 variable.
- ----------------------*
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-