SQLINFO Usage in Campaign Cards

 
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.