The parameters and variables available for use in the Condition and Formula fields of the campaign lines are as follows:
P1 | Gross Line Total |
P82 | Line Amount (VAT Included) |
P2 | Remaining Total (Excl. Surcharges) |
P3 | Net Line Amount |
P4 | Line Quantity (Unit used) |
P5 | Line Quantity (Main Unit) |
P6 | Line Additional Tax Amount |
P87 | Line Discount Amount |
P88 | Line Expense Amount |
P7 | Remaining Amount (Distributed Discount and Promotions) |
P8 | Total Quantity of Goods Received from AR/AP (Main Unit) |
P11 | Total Quantity of Goods Issued to AR/AP (Main Unit) |
P14 | Total Net Amount of Goods Received from AR/AP |
P15 | Total Net Amount of Goods Issued to AR/AP |
P49 | Line Width (Unit Used) |
P50 | Line Width (Main Unit) |
P51 | Line Length (Unit Used) |
P52 | Line Length (Main Unit) |
P53 | Line Height (Unit Used) |
P54 | Line Height (Main Unit) |
P55 | Line Area (Unit Used) |
P56 | Line Area (Main Unit) |
P57 | Line Volume (Unit Used) |
P58 | Line Volume (Main Unit) |
P59 | Line Weight (Unit Used) |
P60 | Line Weight (Main Unit) |
P84 | Line Gross Weight (Line Unit) |
P85 | Line Gross Weight (Main Unit) |
P75 | Unit Price (Unit in Line) |
P76 | Unit Price (Main Unit) |
P101 | Material Code |
P114 | Material Aux. Code |
P118 | Material Aux. Code2 |
P119 | Material Aux. Code3 |
P120 | Material Aux. Code4 |
P121 | Material Aux. Code5 |
P115 | Material Auth. Code |
P77 | Material Purchase VAT Rate (%) |
P78 | Material Sales VAT Rate (%) |
P79 | Material Return VAT Rate (%) |
P80 | Material Retail Sales VAT Rate (%) |
P81 | Material Retail Sales Return VAT Rate (%) |
P102 | Transaction Aux. Code |
P103 | Delivery Code |
P104 | Line Payment Plan Code |
P20 | Gross Total |
P83 | Gross Total (VAT Included) |
P21 | Line Amount Excluding Surcharges |
P22 | Discounted Lines Total |
P23 | Line Discounts Total |
P24 | Line Surcharges Total |
P25 | Quantity Total (Unit Used) |
P26 | Quantity Total (Main Unit) |
P29 | Line Additional Tax Amounts Total (Excl. Prom.) |
P30 | Line Additional Tax Amounts Total (Incl. Prom.) |
P40 | Quantity Total (Excl. Promotion, Unit Used) |
P41 | Quantity Total (Excl. Promotion, Main Unit) |
P42 | AR/AP Debit |
P43 | AR/AP Credit |
P44 | AR/AP Debit Balance |
P45 | AR/AP Credit Balance |
P46 | AR/AP Balance |
P47 | AR/AP Purchase Invoices Total Amount |
P48 | AR/AP Sales Invoices Total Amount |
P61 | Total Width of Lines (Main Unit) |
P62 | Total Length of Lines (Main Unit) |
P63 | Total Height of Lines (Main Unit) |
P64 | Total Area of Lines (Main Unit) |
P65 | Total Volume of Lines (Main Unit) |
P66 | Total Weight of Lines (Main Unit) |
P86 | Total Gross Weight of Lines (Main Unit) |
P105 | AR/AP Code |
P122 | AR/AP Aux. Code |
P123 | AR/AP Aux. Code2 |
P124 | AR/AP Aux. Code3 |
P125 | AR/AP Aux. Code4 |
P126 | AR/AP Aux. Code5 |
P130 | AR/AP Auth. Code |
P106 | Trading Group |
P107 | Payment Plan |
P108 | Payment Plan Group Code |
P109 | City |
P110 | Town |
P111 | District |
P112 | Slip Aux. Code |
P113 | Slip Auth. Code |
P116 | Delivery Account Code |
P117 | Delivery Address Code |
P127 | Division |
P128 | Department |
P129 | Warehouse |
P131 | Unit Set |
P132 | Main Unit |
P133 | Salesperson Code |
P134 | Salesperson Code (Line) |
Functions
MIN(,) | (Takes the minimum parameter/constant into the parenthesis) |
MAX(,) | (Takes the maximum parameter/constant into the parenthesis) |
MOD(,) | (Divides the higher valued parameter/constant into the lower valued one and takes the remainder) |
DIV(,) | (Divides the first parameter/constant to the second and writes the result) |
ABS() | (Takes the absolute value of the parameter/constant) |
VAL( ) | (Converts the string within the parenthesis into a numeric value) |
DATE(,,) | (Converts date format according to day, month and year info) |
AFTER(,) | (Gives the attained date after given number of days) |
DAYS(,) | (Gives number of days between two dates) |
DAYOF() | (Gives date of the day) |
MONTHOF() | (Gives the current month) |
YEAROF() | (Gives the current year) |
WDAYOF() | (Gives the date that shows which day of the week is the given date) |
ROUND( ) | (Rounds the parameter/constant upwards if its decimal point is bigger than 0,5 and downwards if not) |
TRUNC( ) | (Eliminates the decimal characters if the parameter/constant within parenthesis is decimal) |
ERATE(,) | ((Brings the f. currency exchange rate for a specific date. ex: ERATE(DATE(05,05,2002),20) |
CRATE(,,,) | (Multiplies the amount in f. currency with cross rate) |
STRPOS(,) | (Returns the position of the first occurrence of a string inside another string) |
FLOOR( ) | (Rounds the decimal parameter/constant downwards) |
CEIL( ) | (Rounds the decimal parameter/constant upwards) |
FRAC( ) | (Takes the decimal value of the parameter/constant) |
EXP() | (Returns the value of e (the base of natural logarithms) raised to the power of X The inverse of this function is LOG using a single argument only) or LN()) |
LN() | (It is logarithm function. According to "e" base it transforms logarithm function result to the nearest integer) |
POWER(,) | (Gives specified power of a number. For example POWER(9,2)=81 (92) ) |
SQR() | Gives the square of a number) |
SQRT() | (Gives the square root of a number. For example; SQRT(81)=9) |
COS() | (Gives the cosine of a number (given as radiant)) |
SIN() | (Gives sine of a number(given as radiant)) |
TAN() | (Gives tangent of a number (given as radiant)) |
STRLEN() | (Gives the total number of characters of selected text; its type is number) |
WEEKNUM(,,) | (Gives the week number of the related year) |
ROUNDF(,) | (Used to round the decimal amount. (number,frdijits)) |
STR() | (Writes field defined as numbers in text format. For Example STR(1234)=1234) |
DATESTR(,) | (Transforms the given date and writes it in demanded format) |
MONTHSTR(,) | (Writes the month of the given number as text) |
WDAYSTR() | (Writes the day of the given number as text) |
NUMSTR(,,) | (Transforms the given number to text and writes in demanded form) |
TIMESTR(,) | (Transforms the given hour to text and writes in demanded form) |
RESXSTR(,) | (Brings the string corresponding to the related tag and list source at the LRF extended files of resource) |
RESSTR() | (Brings the texts in LRF extended files of resource folder) |
CRESSTR(,) | |
SUBSTR(,,) | (Used to write the selected text in a definite length by starting from a definite character) |
UPCASE() | (Writes the text with capital letters) |
LOWCASE() | (Writes the text with lower cases) |
TRIMSPC(,) | (Deletes the spaces at the beginning and/or at the end of the selected text) |
JUSTIFY(,,,) | (Writes the selected text in demanded length. Fills the spaces with demanded characters in text) |
WRNUM(,,) | (Transforms a number's integer or decimal digits to writing form in specified language) |
WIN2DOS(,) | |
DATEDIFF(,,) | Returns the count (signed integer) of the specified date part boundaries crossed between the specified start date and end date. |
IF(,,) | (Specifies values by defining conditions. When "If (p1>1000, 10,5)" is typed, if p1 is bigger than 1000 the value is used as 10; otherwise as 5) |
CPAR() | (Checkparent: Used to filter the parent material classes) |
FPCLC(,) | (FicheParentCalc: Used to find out the Slip Amount and quantity according to the parent material class) |
FICLC(,) | (FicheItemCalc: Used to find out the slip amount and quantity according to materials) |
FISCCLC(,,) | (Used to take slip total value according to material aux. code) |
EQLC(,) | (EqualCalc: Used to define the code fields as equalities) |
BTWNDATE(,,) | (Used to define condition with date range) |
FPDISC(,,,) | ("Parent Material Class Code)", Order Type, Quantity, Return Type) |
FGDISC(,,,) | ("Material Group Code", Order Type, Quantity, Return Type) |
FIDISC(,,,) | ("Material (Class) Code", Order Type, Quantity, Return Type) |
FUINFOCLC(,,) | |
_SQLINFO(,,) | (Used to define condition by SQLINFO) |
Defining Detail Conditions for Promotion Campaigns
Shops and sales points offer various promotion options according to the purchased material or total amount of transactions for their customers to speed up sales or during the season-end sales.
Some examples for such promotions are; buy 3 get 1 free, giving the cheapest product as a gift among the products with various prices, selling the second one at half-price when the customer buys two of the same products, giving one product as a gift when the customer buys from two different groups of products. New parameters are added to define such campaign conditions and to apply in slips and invoices and; it is aimed to promote the widely-applied campaigns at retail sales.
Specifying the preferred conditions and using these conditions in transactions by using the said parameters for material classes or material groups on discount and surcharge campaign lines have important role in minimizing the errors and enabling ease of use.
For campaigns,
- buy 3 get 1 free campaign - giving the customer who buys 3 products the cheapest product as a gift ,
- selling the second one 50% when the customer buys two of the same products,
- giving the customer who buys 1 product, one product as a gift notwithstanding the groups of products purchased
And for similar transactions, to define detail conditions; the following functions are used in the functions list on formula windows which are accessible on conditions and formula fields;
- FPDISC ("Parent Material Class Code", Order Type, Quantity , Return Type)
- FGDISC ( "Material Group Code", Order Type, Quantity , Return Type)
- FIDISC ( "Material (Class) Code", Order Type, Quantity , Return Type)
These functions are used as follows:
FicheParentDisc (FPDISC), is used to apply slip bottom discount or surcharge according to the slip totals of the materials under the material classes mentioned in the function.
FicheGrupDisc (FGDISC), is used to apply slip bottom discount or surcharge according to the slip totals of the materials under the material groups mentioned in the function.
FicheItemDisc (FIDISC), is used to apply slip bottom discount or surcharge according to the slip totals of the materials mentioned in the function. (* character can bu used to cover all materials on the material code field.)
Order Type:
1: Minimum
2: Maximum
3: Giriş Sırası (Düz)
4: Giriş Sırası (Ters)
Quantity: Indicates the quantity information to apply for slips.
P25 - Quantity Total (Unit Used)
P26 - Quantity Total (Main Unit)
P40 - Quantity Total (Excl. Prom., Unit Used)
P41- Quantity Total (Excl. Prom., Main Unit)
Return Type:
1: Excl. VAT (Main Unit)
2: VAT Included (Main Unit)
3: Net Line Amount (Main Unit)
4: Excl. VAT (Unit Used)
5: VAT Included (Unit Used)
6: Net Line Amount (Unit Used)
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 Name
A
B
C
D
E
Material Class Code
PG
Material Class Description
PG
Ortak kampanya uygulanacak malzeme kartları Malzeme sınıfı açılarak bağlantı yapılmalıdır.
QUERY LISTING THE 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'
TURNING QUERY INTO 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
Not : Fonksiyon SQL' de bir kez çalıştırılarak sql tarafına fonksiyon olarak eklenmesi sağlanmalıdır.
FONKSİYON' UN QUERY İLE ÇALIŞTIRILMASI
SELECT DBO.FN_MS_KAMPANYA_010('PG.004','PG') FROM L_CAPIFIRM WHERE NR=10
FONKSİYON' UN SQLINFO HALİNE GETİRİLMESİ
VAL(_SQLINFO("DBO.FN_MS_KAMPANYA_010('"P101"','PG')","L_CAPIFIRM","NR=10"))
P101: Malzeme Kodu bilgisidir.
Koşul alanında IF(V5>0,1,0) fonksiyonu kullanılarak V5 değişkeninden 1 değeri döndüğü zaman kampanya uygulanması sağlanmaktadır.
V5 değişkeni içerisinde bulunan PG alanı yerine malzemelerin bağlı bulunduğu kampanya uygulanacak üst malzeme sınıf kodu belirtilmelidir.