Payment lines is an Italian localized table that allows to manage (and post) payments at different due dates.
This table is automatically filled for each document when you insert the payment term in the header; you can see this table in every document by clicking Function->Payments.
CalcDateBOC is instead a useful function i recently discovered in codeunit 7600, which, in few words, does the native CALCDATE function, but based on the calendar, that is, skipping nonworking days (i won’t explain this codeunit further, as i’ve not completely understood it, it has 9 parameters).
Getting Started:
- Create a base calendar, and set at least the weekly nonworking days: to achieve this, go to the base calendar form (you can access it from Administration>Application Setup>General>Company information>Shipping tab>Base calendar code field (click lookup)>Shift+f5 to go to the card)
-Create a calendar by setting a name of your choice
-Click Functions>Maintain Base Calendar. Here you can select the recurring method etc…it’s user friendly, i don’t think i have to explain you how to do it. Set only saturday and sunday holidays for our purpose.
- Assign the calendar you just created in the Base calendar code field of the Company information table.
This table is really easy to use and if we create a payment term like 30/60/90 Days, the result will be 3 payment lines:
one with “due date” = “sales header”.”document date” + 30D,
one with “due date” = “sales header”.”document date” + 60D
and one with one with “due date” = “sales header”.”document date” + 90D.
Ok, pretty straightforward, up to now…
BUT MY CUSTOMER WANTS TO BE PAYED THE FIFTEENTH WORKING DAY OF THE MONTH AFTER THIS!!
mmh, I can use my beautiful calcdateboc for this, can’t I?well, not really.
The calcformula i would use in this case is ‘<CM + 15D>’ (remember the brackets), and i would pass it to the calcdateboc Function:
GMANewDate := CUCalcDate.CalcDateBOC('',MyDate,0,CompanyInfo."Base Calendar Code",'',0,CompanyInfo."Base Calendar Code",'',FALSE);
With the calendar we just set, if we suppose that MyDate is First January 2010, the result will be the 8th march 2010.
What happened???well, the function worked literally as expected: with CM, nav will calculate the number of days to the end of the month, adding them to the base date…but skipping saturdays and sundays!
The result is that CM won’t bring our date to the end of the month, but X days after, where X is the number of holidays from today to the end of the month.
To avoid this problem we should do a plain CALCDATE on the “CM” part of dateformula and a CalcDateBOC on the “15D” part of the formula.
What i’ve do to maintain this?Here you are:
- Add 2 fields to table 12170:
—Date Calc. to Add > DateFormula
—Date Calc. to Add (Working) > DateFormula
- Add these fields to forms 12170 and 12171:
Add this code under Sales Invoice table: “payment terms” - OnValidate Trigger, right after **PaymentSales.”Payment %” := PaymentTermsLine.”Payment %”;** line of code.
OLD VERSION
//Copy Values of new fields
PaymentSales.”Date Calc. to Add” := PaymentTermsLine.”Date Calc. to Add”;
PaymentSales.”Date Calc. to Add (Working)” := PaymentTermsLine.”Date Calc. to Add (Working)”;
//store the plain CALCDATE Part
GMANewDate := CALCDATE(PaymentTermsLine.”Date Calc. to Add”,”Document Date”);
//This “IF” is explained later
IF FORMAT(PaymentSales.”Date Calc. to Add (Working)”) ” THEN BEGIN
//Add to the previously CALCDATEd date the “Working days” part
GMANewDate := CUCalcDate.CalcDateBOC(”,GMANewDate,
0,CompanyInfo.”Base Calendar Code”,”,0,CompanyInfo.”Base Calendar Code”,”,FALSE);
END;
//Evaluate the standard “due date Calculation” field as the difference between the final calculated date and the source date
//(”Document date”, in NAV Standard)
EVALUATE(PaymentSales.”Due Date Calculation”,FORMAT(GMANewDate - “Document Date”) + ‘D’);
EVALUATE(PaymentTermsLine.”Due Date Calculation”,FORMAT(GMANewDate - “Document Date”) + ‘D’);
NEW VERSION
- Create this function in Payment terms Table (12170)
FNTCalcDays
//PARAMETERS:
//pGMASourceDate: starting date for calculation(e.g. document date)
//pTBPaymTermsLine: line of “payment lines” table to use for cacluation
//RETURN VALUE:
//text50lTBCompanyInfo.GET;
lGMANewDate := CALCDATE(pTBPaymTermsLine."Date Calc. to Add",pGMASourceDate);
IF FORMAT(pTBPaymTermsLine."Date Calc. to Add") '' THEN BEGIN
lGMANewDate := lCUCalcDate.CalcDateBOC('',lGMANewDate,
0,lTBCompanyInfo."Base Calendar Code",'',
0,lTBCompanyInfo."Base Calendar Code",'',FALSE);
END;
EXIT('');
-Substitute the previous version’s code with this:
PaymentSales."Date Calc. to Add" := PaymentTermsLine."Date Calc. to Add";
PaymentSales."Date Calc. to Add (Working)" := PaymentTermsLine."Date Calc. to Add (Working)";
EVALUATE(PaymentSales."Due Date Calculation",PaymentTermsLine.FNTCalcDays("Document Date",PaymentTermsLine));
EVALUATE(PaymentTermsLine."Due Date Calculation",PaymentTermsLine.FNTCalcDays("Document Date",PaymentTermsLine));
BE AWARE THAT AFTER THIS MODIFICATION, DUE DATE CALCULATION BECOME USELESS, AS WE WILL HAVE THE SAME RESULT BY ONLY SETTING THE “DATE CALC TO ADD.” FIELD.
After evaluating the standard field, nav can proceed alone as always…no further code to modify!
And now let’s test it and explain that “IF” statement
TEST 1: 15th day of the next month
no problem here: From the sales invoice, invoicing tab, click the lookup of “payment terms code” field and then>Calculation. You’ll see the payment term calculation setup (it’s one of the two forms we’ve modified before: no. 12170).
here you have to set “date calc to add.” = CM+15D. Now try to set this payment term in your invoice and you’re done.
TEST 2: 15th WORKING day of the next month
no problem here, too: From the sales invoice, invoicing tab, click the lookup of “payment terms code” field and then>Calculation. You’ll see the payment term calculation setup (it’s one of the two forms we’ve modified before: no. 12170).
here you have to set “date calc to add.” = CM AND “date calc to add. (working)” = 15D. Now try to set this payment term in your invoice and you’re done.
TEST 3: 15th day if it’s a working date; otherwise the day after, and so on…
Here’s where the IF come in hand: in the first test, calcdateBOC is not run, as the “IF” fails (we didn’t set “date calc to add. (Working)” field).
But now we need to calculate first working date from the fifteenth of the month: let’s put CM+15D in our “date calc to add.” field, and then a ‘0D’ in the “date calc to add. (Working)” field. In this way the “IF” becomes true, and function CalcdateBOC is run correctly, calculating some more days after the fifteen, (if the 15th is an holiday).
Considerations
After this implementation, the due date calculation (standard field), become “user-transparent”: in my personal implementation i’ve hidden in order to not confuse the user. It’s function can be replaced by the new “Date calc to add.” field, that does exactly the same work as “due date calculation” if you don’t set the “Date calc to add (Working)” field (see TEST1
Sorry, i know that my posts are not well edited, but i have to take some confidence with “WordPress” 
I’ll edit this post soon, with a solution using a function in order to reuse the code i put in the sales header table, more “user friendly” field names and as always, cronus italy FOB&TXT…stay tuned! Well, i did it
HERE ARE THE SAMPLE OBJECTS (FOB&TXT)
Sample Objects
It uses the format function on the recordref variables and compares the results.
).
