Category Archives: Technical

Exploring Drill through Reports in Dynamics Ax 2012 R3.

Creating basic drill through reports in Dynamics Ax 2012 R3 is really simple. Consider following scenario where we have to show the list of customer and their sales Orders in particular legal Entity.  In this report when User clicks on customer account, a new report opens, which contains list of all possible sales Orders. For this purpose I created two Static/AOT queries. First for Customer, and second for Sales Order. Customer Query:

  • Create a new AOT query with Name DyWorldCustomerList.
  • In DataSource add CustTable table.
  • In fields dynamic to true.

CustListTable Similarly, Create another Query “DyWorldSalesTable” for SalesTable. With Name DyWorldSalesTable   SalesTable Now Open Visual studio and create a new Dynamics AX Report Project   Report Project Add a new report with name

  • DynamicCustomerList

Add New dataset with Name

  • DynamicCustomerList set its properties as follow

CustomerDataSet

  • Select Following fields
    •   AccountNum
    •    CustGroup
    •    Party
  •  Drag this DSCustomerList data set on Report Designer to create an AutoDesign.
  • Change the AutoDesign1 properties as follow

o   Name to DesignCustomerList

o   LayoutStyle to ReportLayoutStyleTemplate

 ReportDesinger2

 Now create another report inside same Dynamics Ax Model Project, this report will be called from customer Report SalesOrderReport Create and DataSet with DSSaleOrder with query  “DyWorldSalesTable” SalesOrderDataSet Drag this dataset on Report Designer and update newly Designer as follow. SalesOrderDesignerProperties     Now open the parameter Section of report and add new parameter as “CustId” of string type CustIdParameter Now expand report designer and expand Table and add filter with following Properties NewParameter Set following Properties of filter o   From  Expression select “=Fields!CustAccount.Value” o   Name= Customer o   Operator =Equals o   Value “=Parameters!CustId.Value” Properties   Save the report.   Now Open the first report “DynamicCustomerList”. And expend the reports design, “DSCustomerListTable” and then Data and select AccountNum. Right click on AccountNum and select “Report Drill Through Action” AddDrillThrough   Now Double click and Select the report designer which will open against the click on field ActionProperties Select SalesOrderReport and its Desinger form dialog ReportSelectionSalesOrderList It will create number of parameters, all belonging to  DynamicSalesOrderList Report as follows: DrillThroughParameters   Delete all parameters except AX_CompanyName and CustId. If any other parameter remains, you definitely got following error when you called Sales Order report form drill through option.

“The Microsoft Dynamic AX parameter ID should be a RECID. The exception is System.FormatException: Input string was not in a correct format.”

AfterDeleteParameters   Please Select following Values for these Properties AX_CompanyName Select its value “=Parameters!AX_CompanyName.Value CompanyNameValue Please select the following value for CustId as “=Field!AccountNum.Value” SaleIdValue Now saves the reports and right click on Report Project to Add to AOT and then deploy on Report Server   AOT and Deploy Now Go to AOT, and refresh the Select the Visual Studio Model Project and SSRS reports into Dynamics Ax project so it will easy to edit or locate easily Menu2 Now create a Menu Item of Display type and set its following Properties Menu Now run the report as follow   CusttomerList As click on first Customer “CNMF-000001” To its salesOrderList , The second report successfully open as follow SaleOrderList   This post has been excerpted from http://tech.alirazazaidi.com/exploring-drill-through-reports-in-dynamics-ax-2012-r3/ Ali Raza Zaidi is MVP, one of the only 6 people qualified from Pakistan! He is our top notch technical consultants and works in the capacity of Advisory Software Engineer for Dynamics AX 2012 in Systems Limited.

Date effective framework – Retrieve Data using Query

Introduction:

In order to cater the scenarios where organizations need to keep track of or analyze data which keeps changing over time, Date effective framework was introduced in Microsoft Dynamics AX 2012.

One of the main requirements of an organization could be to keep track of an exchange rate.  What is the exchange rate today? What was the exchange rate on the day a particular invoice was posted? What was the exchange rate a month ago? In order to cater such needs of an organization, Date effective framework is used which provides ease and consistency for date effective scenarios.

Terminology:

Term Definition
Valid Time State Table A table that tracks the records over time using the fields ValidFrom and ValidTo
Valid Time State Key An Alternate key that enforces the valid time state semantics
Gap A condition in which a record can have a gap between its’ certain date ranges
Overlap A condition where a record has more than one occurrence
Current Record A record effective at the present time
Past Record A record effective in an earlier time
Future Record A record effective in a future time
Create New Time Period Creates a new time period updating the date effective table and closing the initial record
Effective Based When updating a valid time state table in Effective Based mode, if the record being updated is a current record, the record is updated in CreateNewTimePeriod mode. If the record being updated is a future record, the record is updated in Correction mode. If the record is a past record, the record cannot be updated. 

Tutorial – Retrieve Data using Query

Retrieving Data:

Current Get current exchange rate, as in today’s exchange rate
Specific Date Get exchange rate of a specific date such as yesterday’s exchange rate
Date Range Get exchange rate active within the specified date range e.g. valid from January and valid till March

Date Effective framework Methods:

Microsoft Dynamics Ax has introduced some methods in the Query API for the Date effective framework. The methods are mentioned below:

Query Description ValidTimeStateFieldType
Query::ValidTimeStateAsOfDate(Date)  Query records that are effective at the present Date  Date 
Query::ValidTimeStateDateRange(fromdate, todate)  Query records that are effective during the date range: fromdate and todate  Date 
Query::ValidTimeStateAsOfDatetime(DateTime)  Query records that are effective at the present Date and Time  UtcDateTime 
Query::ValidTimeStateDateTimeRange(fromDateTime, toDateTime)  Query records that are effective during  the Datime and Time range:fromdatetime and todatetime

 

UtcDateTime 

 

The following code sample is used to query the data that is effective on 1/1/2014:

static void ValidTimeStateAsOfDate (Args _args)

{

Query query;

QueryBuildDataSource queryBuildDataSource;

QueryRun queryRun;

Date currdate = 1\1\2014;

SL_ExchangeRate exchangeRate;

query = new Query();

queryBuildDataSource = query.addDataSource(tableName2id(“SL_ExchangeRate “));

query.ValidTimeStateAsOfDate(currdate);

queryRun = new QueryRun(query);

while (queryRun.next())

{

exchangeRate = queryRun.get(tableName2id(“SL_ExchangeRate “));

info(strFmt(“%1, %2, %3 %4″, ExchangeRate.CurrencyPair, ExchangeRate.exchangeRate, exchangeRate.ValidFrom, exchangeRate.ValidTo));

}

}

 

 

The following code samples is used to query the data that is effective during 2014-01-01T00:00:00 and 2014-01-31T00:00:00:

static void validTimeStateDateTimeRange (Args _args)

{

Query query;

QueryBuildDataSource queryBuildDataSource;

QueryRun queryRun;

UtcDateTime fromDateTime = 2014-01-01T00:00:00;

utcDateTime toDateTime = 2014-01-31T00:00:00;

SL_ExchangeRate exchangeRate;

query = new Query();

queryBuildDataSource = query.addDataSource(tableName2id(“SL_ExchangeRate “));

query. validTimeStateDateTimeRange (fromDateTime, toDateTime);

queryRun = new QueryRun(query);

while (queryRun.next())

{

exchangeRate = queryRun.get(tableName2id(“SL_ExchangeRate “));

info(strFmt(“%1, %2, %3 %4″, ExchangeRate.CurrencyPair, ExchangeRate.exchangeRate, exchangeRate.ValidFrom, exchangeRate.ValidTo));

}

}

This was the second part of the date effective tutorial. If you are interested in reading the first part of the date effective framework tutorial, please follow the link: 

http://thedynamicsblog.com/date-effective-framework-creating-a-new-table-with-a-valid-time-state-key/

The final and the last part of the date effective framework tutorial will be posted shortly.

Date Lookup in a String Control

Sometimes, you may need to lookup the date calendar in a string control. This was easily possible by overriding the lookup function and call the below method in the AX older versions.

public void lookup()
{
    ;

    this.performFormLookup(SysLookup::LookupDate());
}

But in AX2012R2 you cannot use the lookupDate function like this because it is made protected and can only be used by functions in the classes which are derived from SysLookup.

Its work around is to directly call the SysDateLookup form.

The code is as below.

public void lookup() // Form control lookup
{
    FormRun formRun;
    Args    args;
    ;

    args        = new Args(formStr(SysDateLookup));
    args.caller(element);
    formRun     = classfactory.formRunClass(args);
    formRun.init();
    this.performFormLookup(formRun);
}

Happy DAXing!

Delete All the Transactions in a Company by keeping the Master Data intact Dynamics AX

Sometimes, you may have a requirement to delete all the transactions in a company while keeping the Master data as it is.
In order to achieve this you can duplicate the company and run SysDatabaseTransDelete class, which will delete all the transactions in the related company while keep the master data intact.

1. Press Ctrl + D.
2. Open Class node in AOT.
3. Find SysDatabaseTransDelete class, right click open, it will show the related company from which you want to delete the transactions.

transactions

Note : Before performing the above steps on production please do it in test so you can come to know what exactly you achieve.

Usage Data User Specific Report Error Dynamics AX 2012

th3Sometimes, you might face the error in which some reports like “Summary trial balance” report in general ledger will not execute.

In order to resolve this error you need to delete the related records from SysLastValue table. You cannot find this table in AOT so go to SysLastValue form, open data source node, right click and browse table data.

Filter the table with the UserId who is facing the above error, the ElementName with the related element in error and the related company name.

Delete that particular record by Alt+F9 and restart the client.

You can take the backup of the SysLastValue table if you are performing the above actions in production environment.

Note : This table contains the usage data and deleting the records unnecessarily can reset the user saved settings.

Happy DAXing!

Region as an Internal Organisation

System should allow users to create Region within the internal organization hierarchy. For this we have to do some customization

First create new operating unit type element in OMOperatingUnitType base enum

f1

OMOperatingUnitType is used in OMOperatingUnit tables so we need to Restore OMOperatingUnit tables.

Create new View which named as DimAttributeOMRegion as shown in below screen

f2

 Create Internal Region Organization type

Organization Administration > Common > Organization > Internal Organization

Create new region from Internal Organization action pane button as shown in below screen

f3

Click Ok

f4

General Ledger > Setup > Financial Dimensions > Financials Dimensions

Newly created organization should appear within Financial Dimension Form and could be used throughout AX.

f5

Customize Existing Number Sequence to Fiscal Year Number Sequence

Here we are customizing existing Number sequence and convert it in to Fiscal year number sequence Many customer demands for Purchase order creation with automatic year means how to add year to a number sequence in AX2012, e.g. to generate IDs such as 2013-xxxxx and 2014-xxxxx, that would automatically use the current year. Some people understand that number sequence scopes should allow that, but they don’t know how, from very little bit of customization we can automate purchase order number sequence with year

Customize Purchase order Number Sequence Class:

Add fiscal calendar parameter in NumberSeqModulePurchaseIOrder (LoadModule) method as shown in below screen

 datatype.addParameterType(NumberSeqParameterType::FiscalCalendarPeriod, true, true);
fk1

Load NumberSeqModulePurchaseIOrder (Load Module) method Through Job

 NumberSeqModulePurchaseOrder NumberSeqModulePurchaseOrder = new NumberSeqModulePurchaseOrder();
NumberSeqModulePurchaseOrder.load();
fk2

Before going to run the Job, Remove purchase order existing scope parameter record in NumberSequenceDatatype table, if we don’t remove previous Purchase order scope parameter record it will not effect in Segment configuration as shown below

CEU/Organization administration/Number Sequence/ Segment configuration

fk3

Add Fiscal Year Relation to PurchParamater Table:

CEU/Procurement and sourcing/procurement and Sourcing parameters

fk4

We have customized procurement and Sourcing parameters form for Purchase order Fiscal Number sequence, because we need to select which Fiscal year the purchase order is created every year.

Add fiscal year table relation to PurchParamter table

fk5

Add a fiscal year Field on Procurement and Sourcing parameters form

fk6

Customize Purchase Order Number Sequence Table (PurchParameter):

Add following code in Purchase order Number sequence Parameter table method (numRefPurchId), in this existing method we have change code according to fiscal year.

static client server NumberSequenceReference  numRefPurchId(TransDate _date = systemdateget())
{
   //
   PurchParameters PurchParameters;
   NumberSeqScope  scope;

select firstOnly PurchParameters; // get Selected fiscal year in 
                                  // procurement and    Sourcing parameters Form 

   scope = NumberSeqScopeFactory::CreateDataAreaFiscalCalendarPeriodScope
                            (curext(),FiscalCalendars::findPeriodByPeriodCodeDate(FiscalCalendar::findByCalendarId(PurchParameters.FiscalCalendar).RecId, _date).RecId);
   //

     return NumberSeqReference::findReference(extendedTypeNum(PurchId),scope);
}
fk7

Fiscal Year Number Sequence Configuration

 

General ledger -> Setup -> Fiscal calendars.

Create a new Fiscal year or use existing Fiscal year, but In our case we have created our own Fiscal year for Ten years because we need to create Purchase order automated for Ten years

fk8

If you want to add more years follow below screen.

fk9

Click the Calendar -> Calendar year -> New fiscal year button.

Change Copy from last fiscal year from ‘true’ to ‘false’.

Change Unit from ‘Months’ to ‘Years’.

Click the Create button.

Close the Fiscal calendars form.

Number sequences creation

 

fk10

 

Click Area Page node: Organization administration -> Common -> Number sequences -> Number sequences. Click the Number sequence -> New -> Number sequence button.

fk11

Change Number sequence code from ” to ‘POFiscal’.

Change Name from ” to ‘2013’.

Change Scope from ‘Shared’ to ‘Company and Fiscal calendar period’.

Change Company from ” to ‘ceu’.

Change Calendar from ” to ‘FiscalYear’.

Change Fiscal year from ” to ‘2013’.

Change Period name from ” to ‘Period 1′.

fk12

 

Make it very clear that we are creating PO number sequence for the year 2013 and we have to create PO number sequence again for Year 2014, 2015, 2016.xxxxxx 2023 with same Number sequence Code i.e. ‘POFiscal’  as shown in below screen for Year 2014

fk13

 

Creating Purchase orders:

Before going to create Purchase order make sure that Fiscal year must be selected in Procurement and sourcing Parameter form which we have customized earlier above

fk14

 

This selected Fiscal year must be same which we have selected in creating Purchase order Fiscal year Number Sequence.

Consuming External Web Service through a .Net Class Library Wrapper in Microsoft Dynamics AX 2009

Direct referencing a web service in AOT may lead to different issues some times when it comes to use service reference in the x++ code.

I will show you a simple recipe of how we can consume an external web service in AX2009 by wrapping this web service into a .net assembly wrapper.

Open a visual studio, create a new project.
Select the .net class library as template.
Give it a meaning full name so it can be accessed from x++ code.

th1

th2

Add a web service reference, I’m using a public WSDL of the currency convert.
Click Ok and build the project.
Now right click on the project and go to the project directory.
Copy the dll from bin and rename app.config file to AX32Srv.exe and AX32.exe.
Copy these files to the directory of AOS server and the bin of client if needed.
Restart the AOS.
Now you can consume this webservice from x++ code.
ServiceWrapper.ServiceReference .
If you don’t copy the config file to AOS bin you face this error because It does not read the end points from configuration file when instantiating the service client.

 System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.InvalidOperationException: Could not find default endpoint element that references contract ‘ServiceReference1.ICyclelutionService’ in the ServiceModel client configuration section. This might be because no configuration file was found for your application, or because no endpoint element matching this contract could be found in the client element.

 at System.ServiceModel.Description.ConfigLoader.LoadChannelBehaviors(ServiceEndpoint serviceEndpoint, String configurationName)

 at System.ServiceModel.ChannelFactory.ApplyConfiguration(String configurationName, Configuration configuration)

 at System.ServiceModel.ChannelFactory.ApplyConfiguration(String configurationName)

 at System.ServiceModel.ChannelFactory.InitializeEndpoint(String configurationName, EndpointAddress address)

 at System.ServiceModel.ChannelFactory`1..ctor(String endpointConfigurationName, EndpointAddress remoteAddress)

at System.ServiceModel.ConfigurationEndpointTrait`1.CreateSimplexFactory()

at System.ServiceModel.ConfigurationEndpointTrait`1.CreateChannelFactory()

 at System.ServiceModel.ClientBase`1.CreateChannelFactoryRef(EndpointTrait`1 endpointTrait)

 at System.ServiceModel.ClientBase`1.InitializeChannelFactoryRef()

 at System.ServiceModel.ClientBase`1..ctor()

 at ServiceRef.ServiceReference1.CyclelutionServiceClient..ctor()

— End of inner exception stack trace —

 at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)

 at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)

at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache, StackCrawlMark& stackMark)

 at System.Activator.CreateInstance(Type type, Boolean nonPublic)

at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, StackCrawlMark& stackMark)

 at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)

at System.Activator.CreateInstance(Type type, Object[] args)

at Microsoft.Dynamics.AX.ManagedInterop.ClrBridgeImpl.NewClrObject(ClrBridgeImpl* , Char* pszClassName, Char* assemblyName, Int32 argsLength, ObjectWrapper** arguments, Boolean* isException

Another way to wrap the web service is to wrap it in a web service project in VS. But for that, the web service must be hosted to IIS.

 

Import ledger, AP and AR invoice journal lines

Recently, I came across a requirement in which users want to import the ledger, AP and AR invoice journal lines from a pre-defined excel format in general journal AX2012R2. So I developed a functionality hope that helps you

Below snap shot shows the flat list of all objects in this project.

t1

 Below snap shot shows the UI.

t2

Below is the list of all functions created in a class.

t3

class SLSalesPurchLedgerJourLinesImport extends RunBase
{
    int                                 row;
    int                                 col;
    LedgerJournalId                     journalNum;
    DialogRunbase                       dialog;
    DialogGroup                         dialogGrp;
    FilePath                            filePath;
    DialogField                         dialogFieldsalesPurchLedger,
                                        dialogFieldFilePath;
    SLSalesPurLedger                    salesPurLedger;

    SysExcelApplication                 application;
    SysExcelWorkbooks                   workbooks;
    SysExcelWorkbook                    workbook;
    SysExcelWorksheets                  worksheets;
    SysExcelWorksheet                   worksheet;
    SysExcelCells                       cells;
    COMVariantType                      type;
    str                                 mainAccount;
    LedgerJournalTrans                  ledgerJournalTrans;
    LedgerJournalACType                 accountType;
    LedgerDimensionAccount              ledgerDim;
    DimensionStorage                    dimensionStorage;
    DimensionServiceProvider            dimensionServiceProvider;
    LedgerAccountContract               LedgerAccountContract;
    DimensionAttributeValueContract     ValueContract;
    List                                ListValueContract;

    #DEFINE.CurrentVersion(1)
    #LOCALMACRO.CurrentList
        filePath,
        salesPurLedger
    #ENDMACRO
}

private void clearList(List _list)
{
    ListIterator iterator;
    str          value;

    iterator = new ListIterator(_list);

    while (iterator.more())
    {
        value = iterator.value();

        if (true) // check value
        {
            iterator.delete();
        }
        else
        {
            iterator.next();
        }
    }
}

str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
    switch (_cv.variantType())
    {
        case (COMVariantType::VT_BSTR):
        return _cv.bStr();

        case (COMVariantType::VT_R4):
        return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_R8):
        return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_DECIMAL):
        return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_DATE):
        return date2str(_cv.date(),123,2,1,2,1,4);

        case (COMVariantType::VT_EMPTY):
        return "";

        default:
        throw error(strfmt("@SYS26908", _cv.variantType()));
    }
    return "";
}

private void createCustInvoiceJourLines()
{
    this.initilizeExcel();

    try
    {
        ttsbegin;
        do
        {
            row++;

            ledgerJournalTrans.clear();
            ledgerJournalTrans.initValue();
            ledgerJournalTrans.JournalNum = this.parmJournalNum();
            ledgerJournalTrans.Voucher = this.generateVoucherNum();
            if(!ledgerJournalTrans.Voucher)
                throw error('Voucher number is missing.');
            ledgerJournalTrans.TransDate = cells.item(row, 1).value().date();
            ledgerJournalTrans.Invoice = this.COMVariant2Str(cells.item(row, 2).value());
            ledgerJournalTrans.Txt = this.COMVariant2Str(cells.item(row, 3).value());
            ledgerJournalTrans.AmountCurDebit = cells.item(row, 4).value().double();
            ledgerJournalTrans.AmountCurCredit = cells.item(row, 5).value().double();
            ledgerJournalTrans.AccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row,6).value()));
            if(ledgerJournalTrans.AccountType != LedgerJournalACType::Cust)
            {
                throw error('The sheet does not only contain the customer invoice journal lines.');
            }
            ledgerJournalTrans.OffsetAccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row,18).value()));

            ledgerDim = 0;
            ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,7).value()), LedgerJournalACType::Cust);
            ledgerJournalTrans.LedgerDimension = ledgerDim;

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Cust)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,19).value()), LedgerJournalACType::Cust);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Vend)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,19).value()), LedgerJournalACType::Vend);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Ledger)
            {
                col = 18;
                if(!ListValueContract.empty())
                    this.clearList(ListValueContract);
                do
                {
                    col ++;

                    if(cells.item(row, col).value().bStr() == '')
                    {
                        continue;
                    }

                    ValueContract = new DimensionAttributeValueContract();
                    ValueContract.parmName(this.COMVariant2Str(cells.item(1 , col).value()));
                    ValueContract.parmValue(this.COMVariant2Str(cells.item(row , col).value()));
                    ListValueContract.addEnd(ValueContract);

                    mainAccount = this.COMVariant2Str(cells.item(1, col + 1).value());
                }
                while (mainAccount != 'MainAccount');

                ledgerAccountContract.parmMainAccount(this.COMVariant2Str(cells.item(row, col + 1).value()));
                LedgerAccountContract.parmValues(ListValueContract);

                dimensionStorage =  DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
                ledgerJournalTrans.OffsetLedgerDimension = DimensionAttributeValueCombination::find(dimensionStorage.save()).RecId;
            }

            if(ledgerJournalTrans.validateWrite())
            {
                ledgerJournalTrans.insert();
            }

            type = cells.item(row+1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);

        application.quit();
        ttscommit;
    }
    catch
    {
        Error('Import Failed.');
    }
}

private void createLedgerInvoiceJourLines()
{
    int     dynamicCol;
    this.initilizeExcel();

    try
    {
        ttsbegin;
        do
        {
            row++;

            ledgerJournalTrans.clear();
            ledgerJournalTrans.initValue();
            ledgerJournalTrans.JournalNum = this.parmJournalNum();
            ledgerJournalTrans.Voucher = this.generateVoucherNum();
            if(!ledgerJournalTrans.Voucher)
                throw error('Voucher number is missing.');
            ledgerJournalTrans.TransDate = cells.item(row, 1).value().date();
            ledgerJournalTrans.Invoice = this.COMVariant2Str(cells.item(row, 2).value());
            ledgerJournalTrans.Txt = this.COMVariant2Str(cells.item(row, 3).value());
            ledgerJournalTrans.AmountCurDebit = cells.item(row, 4).value().double();
            ledgerJournalTrans.AmountCurCredit = cells.item(row, 5).value().double();
            ledgerJournalTrans.AccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row, 6).value()));
            if(ledgerJournalTrans.AccountType != LedgerJournalACType::Ledger)
            {
                throw error('The sheet does not contain only the ledger invoice journal lines.');
            }
            ledgerJournalTrans.OffsetAccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row, 18).value()));

            ledgerDim = 0;

            col = 6;
            mainAccount = '';
            if(!ListValueContract.empty())
                this.clearList(ListValueContract);
            do
            {
                col ++;

                if(cells.item(row, col).value().bStr() == '')
                {
                    continue;
                }

                ValueContract = new DimensionAttributeValueContract();
                ValueContract.parmName(this.COMVariant2Str(cells.item(1 , col).value()));
                ValueContract.parmValue(this.COMVariant2Str(cells.item(row , col).value()));
                ListValueContract.addEnd(ValueContract);

                mainAccount = this.COMVariant2Str(cells.item(1 , col + 1).value());
            }
            while (mainAccount != 'MainAccount');

            dynamicCol = col + 9;
            ledgerAccountContract.parmMainAccount(this.COMVariant2Str(cells.item(row, col + 1).value()));
            LedgerAccountContract.parmValues(ListValueContract);

            dimensionStorage =  DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
            ledgerJournalTrans.LedgerDimension = DimensionAttributeValueCombination::find(dimensionStorage.save()).RecId;

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Cust)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,18).value()), LedgerJournalACType::Cust);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Vend)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,18).value()), LedgerJournalACType::Vend);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Ledger)
            {
                col = dynamicCol;
                mainAccount = '';
                if(!ListValueContract.empty())
                    this.clearList(ListValueContract);
                do
                {
                    col ++;
                    if(cells.item(row, col).value().bStr() == '')
                    {
                        continue;
                    }
                    ValueContract = new DimensionAttributeValueContract();
                    ValueContract.parmName(this.COMVariant2Str(cells.item(1 , col).value()));
                    ValueContract.parmValue(this.COMVariant2Str(cells.item(row , col).value()));
                    ListValueContract.addEnd(ValueContract);

                    mainAccount = this.COMVariant2Str(cells.item(1, col + 1).value());
                }
                while (mainAccount != 'MainAccount');

                ledgerAccountContract.parmMainAccount(this.COMVariant2Str(cells.item(row, col + 1).value()));
                LedgerAccountContract.parmValues(ListValueContract);

                dimensionStorage =  DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
                ledgerJournalTrans.OffsetLedgerDimension = DimensionAttributeValueCombination::find(dimensionStorage.save()).RecId;
            }

            if(ledgerJournalTrans.validateWrite())
            {
                ledgerJournalTrans.insert();
            }

            type = cells.item(row + 1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);

        application.quit();
        ttscommit;
    }
    catch
    {
        Error('Import Failed.');
    }
}

private void createVendInvoiceJourLines()
{
    this.initilizeExcel();

    try
    {
        ttsbegin;
        do
        {
            row++;

            ledgerJournalTrans.clear();
            ledgerJournalTrans.initValue();
            ledgerJournalTrans.JournalNum = this.parmJournalNum();
            ledgerJournalTrans.Voucher = this.generateVoucherNum();
            if(!ledgerJournalTrans.Voucher)
                throw error('Voucher number is missing.');
            ledgerJournalTrans.TransDate = cells.item(row, 1).value().date();
            ledgerJournalTrans.Invoice = this.COMVariant2Str(cells.item(row, 2).value());
            ledgerJournalTrans.Txt = this.COMVariant2Str(cells.item(row, 3).value());
            ledgerJournalTrans.AmountCurDebit = cells.item(row, 4).value().double();
            ledgerJournalTrans.AmountCurCredit = cells.item(row, 5).value().double();
            ledgerJournalTrans.AccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row,6).value()));
            if(ledgerJournalTrans.AccountType != LedgerJournalACType::Vend)
            {
                throw error('The sheet does not contain only the vendor invoice journal lines.');
            }
            ledgerJournalTrans.OffsetAccountType = str2enum(accountType, this.COMVariant2Str(cells.item(row,18).value()));

            ledgerDim = 0;

            ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,7).value()), LedgerJournalACType::Vend);
            ledgerJournalTrans.LedgerDimension = ledgerDim;

            ledgerDim = 0;
            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Cust)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,19).value()), LedgerJournalACType::Cust);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Vend)
            {
                ledgerDim = DimensionStorage::getDynamicAccount(this.COMVariant2Str(cells.item(row,19).value()), LedgerJournalACType::Vend);
                ledgerJournalTrans.OffsetLedgerDimension = ledgerDim;
            }

            if(ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Ledger)
            {
                col = 18;
                if(!ListValueContract.empty())
                    this.clearList(ListValueContract);
                do
                {
                    col ++;

                    if(cells.item(row, col).value().bStr() == '')
                    {
                        continue;
                    }

                    ValueContract = new DimensionAttributeValueContract();
                    ValueContract.parmName(this.COMVariant2Str(cells.item(1 , col).value()));
                    ValueContract.parmValue(this.COMVariant2Str(cells.item(row , col).value()));
                    ListValueContract.addEnd(ValueContract);

                    mainAccount = this.COMVariant2Str(cells.item(1, col + 1).value());
                }
                while (mainAccount != 'MainAccount');

                ledgerAccountContract.parmMainAccount(this.COMVariant2Str(cells.item(row, col + 1).value()));
                LedgerAccountContract.parmValues(ListValueContract);

                dimensionStorage =  DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
                ledgerJournalTrans.OffsetLedgerDimension = DimensionAttributeValueCombination::find(dimensionStorage.save()).RecId;
            }

            if(ledgerJournalTrans.validateWrite())
            {
                ledgerJournalTrans.insert();
            }

            type = cells.item(row+1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);

        application.quit();
        ttscommit;
    }
    catch
    {
        Error('Upload Failed.');
    }
}

public Object dialog()
{
    FormBuildControl    setupGroupControl;
    FormRun             formRun;
    #Excel
    dialog = super();

    dialog.alwaysOnTop(true);
    dialog.windowType(FormWindowType::Standard);
    dialogGrp = dialog.addGroup('Import journal lines');
    dialogFieldFilePath = dialog.addFieldValue(extendedTypeStr(FilenameOpen), filePath,  "File path",    "");
    dialog.filenameLookupFilter(["@SYS28576",#XLSX]);
    dialog.filenameLookupTitle("Upload from EXCEL");

    dialogFieldsalesPurchLedger = dialog.addFieldValue(extendedTypeStr(SLSalesPurLedger), salesPurLedger,  "Account Type",    "");

    return dialog;

}

private Num generateVoucherNum()
{
    LedgerJournalName   ledgerJournalName;
    NumberSeq           numberseq;
    select firstOnly ledgerJournalName
        where ledgerJournalName.JournalType == LedgerJournalType::Daily;

    return new JournalVoucherNum(JournalTableData::newTable(LedgerJournalTable::find(journalNum))).getNew(true);

}

public boolean getFromDialog()
{
    boolean ret;

    ret = super();

    salesPurLedger = dialogFieldsalesPurchLedger.value();
    filePath       = dialogFieldFilePath.value();

    return ret;
}

public boolean init()
{
    boolean ret;

    ret = super();

    row                             =    1;
    application                     =    SysExcelApplication::construct();
    workbooks                       =    application.workbooks();

    return ret;
}
private void initilizeExcel()
{
    try
    {
        workbooks.open(filePath);
    }
    catch (Exception::Error)
    {
        throw error('File cannot be opened.');
    }

    workbook            =   workbooks.item(1);
    worksheets          =   workbook.worksheets();
    worksheet           =   worksheets.itemFromNum(1);
    cells               =   worksheet.cells();
}

public void new()
{
    super();

    dimensionServiceProvider = new DimensionServiceProvider();
    LedgerAccountContract    = new LedgerAccountContract();
    ListValueContract        = new List(Types::Class);
}

container pack()
{
    return [#CurrentVersion,#CurrentList];
}

public LedgerJournalId parmJournalNum(LedgerJournalId _journalNum = journalNum)
{
    journalNum = _journalNum;

    return journalNum;
}

public void run()
{
    super();

    switch (salesPurLedger)
    {
        case SLSalesPurchLedger::Customer :
            this.createCustInvoiceJourLines();
            break;

        case SLSalesPurchLedger::Vendor :
            this.createVendInvoiceJourLines();
            break;

        case SLSalesPurchLedger::Ledger :
            this.createLedgerInvoiceJourLines();
            break;
    }

}

boolean unpack(container _packedClass)
{
    Integer     version = conpeek(_packedClass,1);

    switch (version)
    {
        case #CurrentVersion:
            [version,#CurrentList] = _packedClass;
            break;
        default :
            return false;
    }

    return true;
}

public static void main(Args args)
{
    SLSalesPurchLedgerJourLinesImport   salesPurchLedgerJourLinesImport;
    LedgerJournalTable                  ledgerJournalTable;

    if (args && args.record())
    {
        ledgerJournalTable = args.record();
        salesPurchLedgerJourLinesImport = new SLSalesPurchLedgerJourLinesImport();
        salesPurchLedgerJourLinesImport.init();
        salesPurchLedgerJourLinesImport.parmJournalNum(ledgerJournalTable.JournalNum);
        if(salesPurchLedgerJourLinesImport.prompt())
        {
            salesPurchLedgerJourLinesImport.run();
        }
    }
    else
    {
        throw error('arguments are missing');
    }
}

t4The above snap shot method must be written to the import button click event.

Now I’m going to show you the predefined excel templates these templates can accommodate up to 10 ledger dimensions for the account type ledger. Make sure that when you create the excel template the data is defined on the correct alphabetical columns and the dimension column names must be the same as the dimensions you created in the system and they should be in reverse order than you enter it in segmented entry field in the system as below.

 Customer Template

t5

Ledger Template

t6

Vendor Template

t7

 

Find All AOT Object Layers

Sometimes you might need to know the total objects on different layers in AOT.

The below job that I wrote will show all the tables exist on different layers you can copy and paste it to excel from the data in the infolog after the job has been executed.

In order to know about the other objects than the tables just change “#TablesPath” macro which are available in #AOT.

static void displayAOTObjLayers(Args _args)

{

#AOT

#Properties

#define.LayerCount(16)

str                     layers;

str                     aotObjName;

int                     i, j, objectLayers;

str                     total;

TreeNode        treeNode, treeNodeInLayer;

Array               layerArray;

DictEnum        dictEnum;

UtilElements   utilElements;

TreeNode        baseTreeNode;

;

layerArray = new Array(Types::String);

dictEnum = new DictEnum(enumNum(UtilEntryLevel));

//#TablesPath is the path of tables in AOT. Look other object paths in #AOT macro.

baseTreeNode = TreeNode::findNode(#TablesPath);

treeNode = BaseTreeNode.AOTfirstChild();

while(treeNode)

{

for (i = 0; i < dictEnum.values(); i++)

{

layerArray.value(i+1, dictEnum.value2Name(i));

}

objectLayers = treeNode.applObjectLayerMask();

for (i = 0; i < #LayerCount; i++)

{

j = objectLayers;

j = j & power(2, i);

 

if (j)

{

aotObjName = TreeNode.AOTname();

layers += layerArray.value(i + 1)+’ ‘;

}

}

info(strfmt(‘%1 || %2′, aotObjName, layers));

treeNode = treeNode.AOTnextSibling();

aotObjName = ”;

layers = ”;

}

}