Notes

1.if(ComboBox.selection() == ABC::A)

2. Sales Invoice report in ax 2012 R3

Getting the customer address from salesorder i,e salestable ... we should consider
LogisticsPostalAddress table

select salesTable
        join logisticsPostalAddress where salesTable.SalesId == "05SSSSO171800115"
        && logisticsPostalAddress.RecId == salesTable.DeliveryPostalAddress;     
 
    info(strFmt("%1",logisticsPostalAddress.Address));

----

tables and classes involved in Sales Invoice report

url

https://atinkerersnotebook.com/2012/10/05/creating-automatic-email-confirmations-from-dynamics-ax/

send mail withy pdf in ax 2012

SysEmailParameters parameters = SysEmailParameters::find();
    FilePath FilePath;
    SMTPRelayServerName relayServer;
    SMTPPortNumber portNumber;
    SMTPUserName userName;
    SMTPPassword password;
    Str1260 subject,body;
    InteropPermission interopPermission;
    SysMailer mailer;
    SysMailerAttachments    sysMailerAttachments;
    System.Exception e;

    ;
    if (parameters.SMTPRelayServerName)
        relayServer = parameters.SMTPRelayServerName;
    else
        relayServer = parameters.SMTPServerIPAddress;
        portNumber = parameters.SMTPPortNumber;
        userName = parameters.SMTPUserName;
        password = SysEmailParameters::password();
   
        subject = "Subject line for the email";
        body = "<B>Body of the email</B>";
            body += "<br/> <B>testing</B>"; /
        body += "<br/>red</Br/>";
         body += "<br/>blue</Br/>";
         body += "<br/>orange</Br/>";
         body += "<br/>testing</Br/>";

    CodeAccessPermission::revertAssert();

    try
    {
        interopPermission = new InteropPermission(InteropKind::ComInterop);
        interopPermission.assert();
       
        FilePath = '@C//q.pdf';
        FilePath = strfmt('C:\\q.pdf');
        mailer = new SysMailer();
        mailer.SMTPRelayServer(relayServer,portNumber,userName,password, parameters.NTLM);
        //instantiate email
        mailer.fromAddress(userName);

        mailer.tos().appendAddress("email");
        mailer.subject(subject);
        mailer.htmlBody(body);
        mailer.attachments().add(FilePath);
        mailer.sendMail();
        CodeAccessPermission::revertAssert();
        info("Email has been sent!");
    }
    catch (Exception::CLRError)

    {
    e = ClrInterop::getLastException();

    while (e)

    {
        info(e.get_Message());
        e = e.get_InnerException();
    }
    CodeAccessPermission::revertAssert();
    //info(e);
    info ("Failed to Send Email some Error occure");

Run base batch to clean undecided number Seq in status List

class NumsSeq extends RunBaseBatch
{
    RefRecId                numSeqId;
    NumberSequenceList      numberSequenceList;

    DialogField dlgNumSeqId;

    #define.CurrentVersion(4)
    #localmacro.CurrentList
    dlgNumSeqId
    #endmacro
}

...................................................................................

protected Object dialog()
{
DialogRunbase dialog;

dialog = super();
dialog = super();
dlgNumSeqId = dialog.addFieldValue(extendedTypeStr(RefRecId),numSeqId);

return dialog;
}

..................................

public boolean getFromDialog()
{
boolean ret;

ret = super();
numSeqId = dlgNumSeqId.value();

return ret;
}

//////////////////////////////

public container pack()
{
    return [numSeqId];
}

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

/// <summary>
///    Contains the code that does the actual job of the class.
/// </summary>
public void run()
{
    while select forUpdate numberSequenceList where numberSequenceList.NumberSequenceId == numSeqId
        &&   numberSequenceList.Status == NumStatus::Active//5637147121
    {
        ttsBegin;
        if(numberSequenceList.cleanAction() == NumberSeqCleanAction::Undecided )
        {
            numberSequenceList.delete();
        }

        ttsCommit;
    }
}


...........................


public boolean unpack(container packedClass)
{
[numSeqId] = packedClass;
    return true;

}

.........................................

public static void main(Args args)
{
    NumsSeq numsSeq = new NumsSeq();

    if(numsSeq.prompt())
    {
        numsSeq.run();
    }
}

update proj dimensions in ax 2012

class ProjDimUpload
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
}

.................................................................................

public void importProjDim()
{
    ProjId              projId;

    RecId               recIdDefaultDimension;


    //table
    ProjTable               projTable;

    container   financialDimensionFromExcel;
    container   conDimensionName;
    Dialog                  dialog;
    DialogGroup             dialogGroup;
    DialogField             dialogfilename;
    Filename                filename;
    COMVariantType          type;

    str                     strDate;
    int                     row;

    ;
       startLengthyOperation();
    //variable yang diisi manual
    filename              = "C:\\Proj  excel Dim upload.xlsx";
   


    dialog          = new Dialog("Select File For Proj Master Dim Import");
    dialogGroup     = dialog.addGroup("Excel File");
    dialogfilename  = dialog.addField(extendedTypeStr(FilenameOpen));

    if (dialog.run())
    {
        application = SysExcelApplication::construct();
        workbooks   = application.workbooks();
        filename=dialogfilename.value();
         try
    {
        if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
        {
            workbook   = workbooks.item(1);
            workSheets = workbook.worksheets();
            workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
            cells      = workSheet.cells();
            conDimensionName = this.TIDgetDimensionName();
            row = 2;

            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //financial dimension
                financialDimensionFromExcel = conNull();
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,2).value().bStr();
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,3).value().bStr();
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,4).value().bStr();
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,5).value().bStr();
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,6).value().bStr();
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,7).value().bStr();
                //dimension value 7
                financialDimensionFromExcel += cells.item(row,8).value().bStr();
                //dimension value 8
                financialDimensionFromExcel += cells.item(row,9).value().bStr();
                //dimension value 9
                financialDimensionFromExcel += cells.item(row,10).value().bStr();
                //dimension value 10
                financialDimensionFromExcel += cells.item(row,11).value().bStr();
                //generateDefaultDimension
                recIdDefaultDimension       = this.TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);

                //variable
                //assetId           = this.TIDComVariant2STR(cells.item(row,2).value());
                projId      = this.TIDComVariant2STR(cells.item(row,1).value());

                //table
                projTable      = ProjTable::find(projId);

                //validation
                if(!projTable)
                {
                    throw error(strFmt("Row : %1 ,ProjId not exists, please check again",row));
                }
             

                    projTable.selectForUpdate(true);

                    projTable.DefaultDimension      = recIdDefaultDimension;
                    projTable.update();
                row++;
                }
             
            }
            ttsCommit;
            application.quit();
        }

    catch(Exception::Error)
    {
        info("Proj upload Error");
        application.quit();
    }
    }
    info(strFmt("%1 Dimensions had been updated",row));
}

......................................................................

public  str TIDComVariant2STR(COMVariant _variant)

{

    str valueStr;

    ;

    switch(_variant.variantType())

    {

        case COMVariantType::VT_EMPTY   :

            valueStr = '';

            break;

        case COMVariantType::VT_BSTR    :

            valueStr = _variant.bStr();

            break;

        case COMVariantType::VT_R4      :

        case COMVariantType::VT_R8      :

            if(_variant.double())

            {

                valueStr = strFmt("@SYS311964",

                                    num2Str0(_variant.double(), 0),

                                    num2str(_variant.double(),

                                    0,

                                    numOfDec(_variant.double()),

                                    1,

                                    0));

            }

            break;

        default                         :

            throw error(strfmt("@SYS26908",

                                _variant.variantType()));

    }

    return valueStr;

}

...........................

Public DimensionDefault TIDcreateDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)

{

    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();

    DimensionDefault                    result;

    int                                 i;

    DimensionAttribute                  dimensionAttribute;

    DimensionAttributeValue             dimensionAttributeValue;

    //_attr is dimension name in table DimensionAttribute

    container               conAttr =   _attr;

    container               conValue = _value;

    str                     dimValue;

    for (i = 1; i <= conLen(conAttr); i++)

    {

        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

        if (dimensionAttribute.RecId == 0)

        {

            continue;

        }

        dimValue = conPeek(conValue,i);

        if (dimValue != "")

        {

            // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.

            dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

            // Add the dimensionAttibuteValue to the default dimension

            valueSetStorage.addItem(dimensionAttributeValue);

        }

    }

    result = valueSetStorage.save();

    return result;

}

..............................

public  container TIDgetDimensionName()

{

    DimensionAttribute              dimAttr;

    DimensionAttributeSetItem       dimAttrSetItem;

    DimensionEnumeration            dimensionSetId;

    DimensionAttributeValue         dimAttributeValue;

    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name

                where dimAttr.Type != DimensionAttributeType::MainAccount

            join RecId from dimAttrSetItem

                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &&

                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId

            {

                dimensionName += dimAttr.Name;

            }

    return DimensionName;

}

.......................................................................

public static void main(Args args)

{

   ProjDimUpload projDimUpload = new ProjDimUpload();

    projDimUpload.importProjDim();

}

Import fixed assets dim ax 2012

class FixedAssetDimUpload
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;


}


//////////////////////////////////////////////////////////////////////

public void importFixedAssetDim()
{
    AssetId             assetId;
    AssetGroupId        assetGroupId;
    AssetName           assetName;
    AssetLocationId     assetLocationId;
    AssetServiceLife    assetServiceLife;
    AssetLifeTimeRest   assetLifeTimeRest;
    AssetPostingProfile assetPostingProfile;
    AssetBookId         assetBookId;
    TransDate           depreciationStartDate,LastDepreciationDate,AcquisitionDate;
    RecId               recIdDefaultDimension;

    AxAssetTable        axAssetTable;



    //table
    AssetTable          assetTable;
    AssetGroup          assetGroup;
    AssetLocation       assetLocation;
    AssetBook           assetBook,assetBookLoc;
    AssetLedger         assetLedger;
    AssetBookTable      assetBookTable;

    container   financialDimensionFromExcel;
    container   conDimensionName;
    Dialog                  dialog;
    DialogGroup             dialogGroup;
    DialogField             dialogfilename;
    Filename                filename;
    COMVariantType          type;

    str                     strDate;
    int                                         row;

    ;
       startLengthyOperation();
    //variable yang diisi manual
    filename              = "C:\\Fixed Asset  excel Dim upload.xlsx";
    assetPostingProfile   = "FA POSTING";
    depreciationStartDate = today();
    LastDepreciationDate  = today();
    AcquisitionDate       = today();

    dialog          = new Dialog("Select File For customer Bank Master Import");
    dialogGroup     = dialog.addGroup("Excel File");
    dialogfilename  = dialog.addField(extendedTypeStr(FilenameOpen));

    if (dialog.run())
    {
        application = SysExcelApplication::construct();
        workbooks   = application.workbooks();
        filename=dialogfilename.value();
         try
    {
        if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
        {
            workbook   = workbooks.item(1);
            workSheets = workbook.worksheets();
            workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
            cells      = workSheet.cells();
            conDimensionName = this.TIDgetDimensionName();
            row = 2;

            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //financial dimension
                financialDimensionFromExcel = conNull();
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,7).value().bStr();
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,8).value().bStr();
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,9).value().bStr();
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,10).value().bStr();
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,11).value().bStr();
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,12).value().bStr();
                //generateDefaultDimension
                recIdDefaultDimension       = this.TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);

                //variable
                assetId           = this.TIDComVariant2STR(cells.item(row,2).value());
                assetGroupId      = this.TIDComVariant2STR(cells.item(row,1).value());
                assetLocationId   = this.TIDComVariant2STR(cells.item(row,6).value());
                assetName         = this.TIDComVariant2STR(cells.item(row,3).value());
                strDate = this.TIDComVariant2STR(cells.item(row,4).value());
                depreciationStartDate = str2Date(strDate,123);
                //assetServiceLife  = str2num(this.TIDComVariant2STR(cells.item(row,4).value()));
                assetLifeTimeRest = str2num(this.TIDComVariant2STR(cells.item(row,5).value()));
                assetBookId       = this.TIDComVariant2STR(cells.item(row,13).value());
                //table
                assetTable      = assetTable::find(assetId);
                assetGroup      = AssetGroup::find(assetGroupId);
                assetLocation   = AssetLocation::find(assetLocationId);

                //validation
                if(!assetGroup)// || !assetLocation)
                {
                    throw error(strFmt("Row : %1 ,Asset group or asset location not existed, please check again",row));
                }
                //create asset Table


                //create assetBook
                assetBookLoc = assetBook::find(assetId,assetBookId);
                select assetBook
                    join assetBookTable
                        where assetBook.BookId == assetBookTable.BookId
                        && assetBook.AssetId == assetBookLoc.AssetId
                        && assetBook.BookId == assetBookLoc.BookId
                        && assetBookTable.CurrentOperationsTax == CurrentOperationsTax::Current;
                if(!assetBook)
                {
                    assetBook.clear();
                    //assetBook.initValue();
                    //assetBook.AssetId               = assetId;
                    //assetBook.BookId                = assetBookId;
                    //assetBook.PostingProfile        = assetPostingProfile;
                    //assetBook.ServiceLife           = assetServiceLife;
                    //assetBook.LifeTime              = assetServiceLife*12;
                    //assetBook.LifeTimeRest          = assetLifeTimeRest;
                    //assetBook.DepreciationStartDate = depreciationStartDate;
                    //assetBook.LastDepreciationDate  = LastDepreciationDate;
                    //assetBook.AcquisitionDate       = AcquisitionDate;
                    //assetBook.DefaultDimension      = recIdDefaultDimension;
                    //assetBook.insert();
                }
                else
                {
                    assetBook.selectForUpdate(true);

                    assetBook.DepreciationStartDate = depreciationStartDate;

                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.update();
                }


                row++;
            }
            ttsCommit;
            application.quit();
        }
    }
    catch(Exception::Error)
    {
        info("Fixed Asset upload Error");
        application.quit();
    }
    }
    info("Dimensions had been updated");
}

///////

public  str TIDComVariant2STR(COMVariant _variant)
{
    str valueStr;
    ;

    switch(_variant.variantType())
    {
        case COMVariantType::VT_EMPTY   :
            valueStr = '';
            break;

        case COMVariantType::VT_BSTR    :

            valueStr = _variant.bStr();
            break;

        case COMVariantType::VT_R4      :
        case COMVariantType::VT_R8      :

            if(_variant.double())
            {
                valueStr = strFmt("@SYS311964",
                                    num2Str0(_variant.double(), 0),
                                    num2str(_variant.double(),
                                    0,
                                    numOfDec(_variant.double()),
                                    1,
                                    0));
            }
            break;

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

    return valueStr;
}

/////

Public DimensionDefault TIDcreateDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                    result;
    int                                 i;
    DimensionAttribute                  dimensionAttribute;
    DimensionAttributeValue             dimensionAttributeValue;
    //_attr is dimension name in table DimensionAttribute
    container               conAttr =   _attr;
    container               conValue = _value;
    str                     dimValue;

    for (i = 1; i <= conLen(conAttr); i++)
    {
        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

        if (dimensionAttribute.RecId == 0)
        {
            continue;
        }

        dimValue = conPeek(conValue,i);

        if (dimValue != "")
        {
            // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
            dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

            // Add the dimensionAttibuteValue to the default dimension
            valueSetStorage.addItem(dimensionAttributeValue);
        }
    }
    result = valueSetStorage.save();
    return result;
}

////

public  container TIDgetDimensionName()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &&
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}
///

public static void main(Args args)
{
    FixedAssetDimUpload fixedAssetDimUpload = newFixedAssetDimUpload();
    fixedAssetDimUpload.importFixedAssetDim();
}

company address in ax 2009

Display str CompanyAddress()
{
    InventSiteId  inventSiteId;
    TextBuffer  txt = new TextBuffer();
    Addressing  address;
    CompanyInfo companyInfoLoc;
    ;

    companyInfoLoc = CompanyInfo::find();
    inventSiteId =PurchTable::find(VendPurchOrderJour.PurchId).InventSiteId;
    address = Address::findByInventSiteIdandTableRecId(inventSiteId,companyInfoLoc.TableId,companyInfoLoc.RecId
            ,AddressType::Invoice).Address;

    txt.setText(address);
    txt.replace('\n',' ');
    address = txt.getText();

    return(address);
}

convert currency

static void CurrencyConvertor(Args _args)
{
CurrencyExchangeHelper currencyExchangeHelper;
CurrencyCode transCurrency = "INR";
AmountCur amountCur = 500.00;
AmountMst amountMST;

currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), systemDateGet());
amountMST = currencyExchangeHelper.calculateTransactionToAccounting(transCurrency, amountCur ,true);
info(strFmt("%1",amountMST));
}

Upload HSN code rate for GST .csv file code

//Added  to Upload GST Tax Rate-13/6/2017
public void Alle_UploadGSTTaxRate()
{
    TaxRuntimeLookupCondition           taxRuntimeLookupCondition;
    TaxRuntimeLookupMeasureResultDetail taxRuntimeLookupMeasureResultDetail;
    TaxRuntimeLookupMeasureResult       taxRuntimeLookupMeasureResult;
    Dialog                              dialog  = new Dialog();
    DialogField                         dialogField;
    str                                 Delimiter = ",";
    str                                 filePath,fileNameOnly,type;
    AsciiIo                             importFile;
    container                           record;
    int                                 totalRecords;
    NumberSeq                           num;
    TaxRuntimeLookup                    runTimeLookup;
    TaxRuntimeDocComponentMeasure       docComponentMeasure;
    TaxRuntimeDocContext                taxRuntimeDocContext;
    TaxSolutionScopeSetup               taxSolutionScopeSetup;
    RefRecId                            ledger;


    dialogField=dialog.addField(extendedTypeStr(FilenameOpen),"Select File to Import","Select file to import");
    dialog.addText("File-Format Sequence: ConsumptionState, HSNCode, SAC, TransactionDateFrom, TransactionDateTo");
    dialog.addText("NetPriceMin, NetPriceMax, PriceIncl, MRPMin, MRPMax");

    dialog.caption("Import File");
    dialog.filenameLookupFilter(['csv','*.csv']);
    if(!dialog.run())

    return;

    [filePath, fileNameOnly, type] = fileNameSplit(dialogField.value());

    importFile = new AsciiIo(dialogField.value(), 'R');

    if((!importFile) || (importFile.status() != IO_Status::Ok))
    {
        warning("Error in opening import file");
        throw(Exception::Error);
    }

    importFile.inFieldDelimiter(Delimiter);

    if((!importFile) || (importFile.status() != IO_Status::Ok))
    {
        warning("Error in opening log file");
        throw(Exception::Error);
    }

    ttsbegin;

    try
    {
        record = importFile.read();

        while(importFile.status() ==  IO_Status::Ok)
        {
            taxRuntimeLookupCondition.clear();
            TaxRuntimeLookupMeasureResultDetail.clear();
            taxRuntimeLookupMeasureResult.clear();

            record = importFile.read();

            if(!record)

            break;

            totalRecords = totalRecords + 1;
            runTimeLookup   =   this.parmRuntimeLookup();

            taxRuntimeLookupCondition.LookupVersion         =   lookup.RecId;
            taxRuntimeLookupCondition.DimValue1             =   conPeek(record,1);  //State
            taxRuntimeLookupCondition.DimValue2             =   conPeek(record,2);  //HSN
            taxRuntimeLookupCondition.DimValue3             =   conPeek(record,3);  //SAC
            taxRuntimeLookupCondition.DateRangeFrom1        =   str2Date(conPeek(record,4),213);    //TransDateFrom
            taxRuntimeLookupCondition.DateRangeTo1          =   str2Date(conPeek(record,5),213);    //TransDateTo
            taxRuntimeLookupCondition.ValueRangeFrom1       =   conPeek(record,6);  //NetPrice Min
            taxRuntimeLookupCondition.ValueRangeTo1         =   conPeek(record,7);  //NetPrice Max
            taxRuntimeLookupCondition.DimValue4             =   conPeek(record,8);  // Price Incl
            taxRuntimeLookupCondition.ValueRangeFrom2       =   conPeek(record,9);  // MRP min
            taxRuntimeLookupCondition.ValueRangeTo2         =   conPeek(record,10); // MRP max

            select firstOnly taxSolutionScopeSetup where
                    taxSolutionScopeSetup.TaxSolutionScope   ==  taxruntimedoccontext::find(TaxRuntimeDocComponentMeasure::find
                                                                    (runTimeLookup.LookupOwnerRecId).DocContext).TaxSolutionScope;

            taxRuntimeLookupCondition.Ledger    =   taxSolutionScopeSetup.Ledger;

            taxRuntimeLookupCondition.write();

            try
            {
                conditionEditingHelper.writtenLookupCondition(taxRuntimeLookupCondition);

                select firstOnly taxRuntimeLookupMeasureResult
                        join forUpdate taxRuntimeLookupMeasureResultDetail
                    where taxRuntimeLookupMeasureResult.LookupCondition == taxRuntimeLookupCondition.RecId
                        && taxRuntimeLookupMeasureResultDetail.LookupMeasureResult == taxRuntimeLookupMeasureResult.RecId;


                taxRuntimeLookupMeasureResultDetail.Value   =   conPeek(record,11);
                taxRuntimeLookupMeasureResultDetail.write();

            }

            catch(Exception::Error)
            {
                throw(Exception::Error);
            }
        }

    }

    catch(Exception::Error)
    {
        Throw(Exception::Error);
    }

    ttsCommit;

    info(strFmt("%1 Lines imported",totalRecords));
}

read all files from folder

The following examples will show you how to list all of the files in a folder and sub folder.

 static void GetFilesInFoldersAndSubFolders(Args _args)
{
    System.String[] filePaths = System.IO.Directory::GetFiles(@"folder location", "*.*", System.IO.SearchOption::AllDirectories); //get listing of all files within the folder
    int fileCount = filepaths.get_Length(); //get how many files were found
    int currentFileCount;
    
    //go throw each one of the files that were found
    for(currentFileCount = 0; currentFileCount < fileCount ; ++currentFileCount)
    {
        info(filepaths.GetValue(currentFileCount)); 
    }
}

filter grid based on conbobox ona form

public class FormRun extends ObjectRun
{
    SysFormSplitter_X verticalSplitter;
    QueryBuildRange                qbr;
}


public void executeQuery()
{
    if(BusinessUnit.text() != "")
    {
        qbr.value(queryValue(BusinessUnit.text()));
    }
    else
    {
        qbr.value(SysQuery::valueUnlimited());

    }

    super();
}




public void init()
{
    super();
    qbr= this.query().dataSourceTable(tableNum(VendTable)).addRange(fieldNum(VendTable, InventSiteId));

}


public void lookup()
{
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource;

    SysTableLookup  sysTableLookup = SysTableLookup::newParameters(tableNum(OMOperatingUnit), this);
    ;
    queryBuildDataSource = query.addDataSource(tableNum(OMOperatingUnit));

    sysTableLookup.addLookupfield(fieldNum(OMOperatingUnit, OMOperatingUnitNumber));
    //sysTableLookup.addLookupfield(fieldNum(CustVendExternalItem, ExternalItemId));

    queryBuildDataSource.addRange(fieldNum(OMOperatingUnit, OMOperatingUnitType)).value(queryValue(OMOperatingUnitType::OMBusinessUnit));
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

    super();
}

---------------------------------------------------------------------------------------------------------------
public boolean modified()
{
    boolean ret;

    ret = super();
    VendTable_ds.executeQuery();
    return ret;
}

write to csv from ax

static void customerExportCsv(Args _args)
{

    CommaTextIo file;
    container line;
    FileIoPermission perm;
    CustTable                   custTable;
        FreeText                address;
        TaxRegistrationNumber_IN gstNo;


    #define.filename(@"C:\cust.csv")
    #File
    perm = new FileIoPermission(@"C:\cust.csv", "w");
    perm.assert();
    file = new CommaTextIo(@"C:\cust.csv", "w");

    if (!file || file.status() != IO_Status::Ok)
    {
    throw error("@SYS19358");//throws error if file path is not found
    }

    while    select custTable
    {
        gstNo = TaxRegistrationNumbers_IN::find(TaxInformation_IN::findDefaultbyLocation(DirPartyTable::findRec(CustTable::find(custTable.AccountNum).Party).PrimaryAddressLocation).GSTIN).RegistrationNumber;
        address = DirParty::primaryPostalAddress(custTable.Party).Address;

    if(custTable)
    {
        line = [custTable.AccountNum,custTable.name(),custTable.InventSiteId,address,gstNo];
        file.writeExp(line);
    }
    }
   info("done");
}



Read file dynamically.

 int currentFileCount;
   
     System.String[] filePaths = System.IO.Directory::GetFiles(@"C:\folder\", "*.CSV*", System.IO.SearchOption::AllDirectories);
   fileNameOpen = filepaths.GetValue(currentFileCount);

custom number seq

NumberSequenceCode numberSequenceCode,numberSequenceCodeVoucher;
Numberseq          numberseqLocal,numberseqVoucher;

 localSalesTable = salesParmTable.salesTable();
    numberSequenceCode = Alle_ARBusinessUnitNumberSeq::find(localSalesTable.Alle_BusinessUnit).CustomerInvoice;
    numberSequenceCodeVoucher = Alle_ARBusinessUnitNumberSeq::find(localSalesTable.Alle_BusinessUnit).CustomerInvoiceVoucher;

 numberseqLocal = NumberSeq::newGetNumFromCode(numberSequenceCode,NumberSeqScopeFactory::createDefaultScope(), true,true);
    numberseqVoucher = NumberSeq::newGetNumFromCode(numberSequenceCodeVoucher,NumberSeqScopeFactory::createDefaultScope(), true,true);
    custInvoiceJour.InvoiceId        = numberseqLocal.num();
    custInvoiceJour.LedgerVoucher        = numberseqVoucher.num();

sysoperation framework 2012

class MRKPEmailBatchServiceController extends SysOperationServiceController
{
    TransDate                               fromDate;
    TransDate                               toDate;
    MainAccount                             mainAccount;
    GeneralJournalEntry                     generalJournalEntry;
    #define.RecordsToDisplay(6)

}

-----------------------------------------------------------------------------------------------------------
protected ClassDescription defaultCaption()
{
    ClassDescription ret;

    ret = "@AAM50";

    return ret;

}

---------------------------------------------------------------------------------------------------------------

protected void new()
{
    super(classStr(MRKPEmailBatchService), methodStr(MRKPEmailBatchService, SemdEmail), SysOperationExecutionMode::Synchronous);

}

-----------------------------------------------------------------------------------------------------------

public void run()
{
    super();

}

---------------------------------------------------------------------------------------------------

public static MRKPEmailBatchServiceController construct()
{
    MRKPEmailBatchServiceController    controller;
    MRKPEmailBatchServiceContract      datacontract;

    controller  = new MRKPEmailBatchServiceController();
    controller.parmShowDialog(true);
    controller.parmShowProgressForm(false);
    datacontract = controller.getDataContractObject('');
    datacontract.parmFromDate(dateStartMth(prevMth(systemDateGet())));
    datacontract.parmToDate(dateEndMth(prevMth(systemDateGet())));

    return controller;

}

-------------------------------------------------------------------------------------------------

public static void main(args _args)
{
    MRKPEmailBatchServiceController controller;

    controller = MRKPEmailBatchServiceController::construct();
    controller.startOperation();

}

---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

[DataContractAttribute,
SysOperationLabelAttribute(literalStr("@SYS80056")),
SysOperationGroupAttribute('FromGroup', literalStr("@SYS181135"), '1', FormArrangeMethod::HorizontalFlushRight),
SysOperationGroupAttribute('ToGroup', literalStr("@SYS53942"), '1')]

class MRKPEmailBatchServiceContract  implements SysOperationValidatable
{
    TransDate   fromDate;
    TransDate   toDate;

}

----------------
[DataMemberAttribute
,SysOperationLabelAttribute(literalStr("@SYS80056")),
SysOperationGroupMemberAttribute('FromGroup'),SysOperationDisplayOrderAttribute('1')]
public sysdate parmFromDate(TransDate _fromDate = fromDate)
{
    fromDate = _fromDate;
    return fromDate;
}
--------------
[DataMemberAttribute
,SysOperationLabelAttribute(literalStr("@SYS80056")),
SysOperationGroupMemberAttribute('ToGroup'),SysOperationDisplayOrderAttribute('1')]
public TransDate parmToDate(TransDate _toDate = toDate)
{
    toDate = _toDate;
    return toDate;
}
--------------------
public boolean validate()
{
    boolean     isValid;
    isValid = true;

    if (!fromDate)
    {
        isValid = checkFailed(strFmt("@SYS84753", "@SYS24050"));
    }
    if (!toDate)
    {
        isValid = checkFailed(strFmt("@SYS84753", "@SYS36680"));
    }

    return isValid;
}

=-----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------

class MRKPEmailBatchService  extends SysOperationServiceBase
{
    TransDate               fromDate;
    TransDate               toDate;
    LedgerJournalId         journalNumber;
    boolean                 headerCreated;
    #define.WorkFlowComment("Auto submitted to workflow")
}

------------------------------------------------------------

[SysEntryPointAttribute]
public void SemdEmail(MRKPEmailBatchServiceContract _serviceContract)
{
  #File
SampleTable  sampleTable;

    IO                    iO;
HcmPersonnelNumberId  employerefno;
ProjId                site;
TextIo                file;
//FilenameOpen          filename = "C:o assign file name
     FilenameOpen          filename = "C:\\MO\\test1.csv";
Container             record,line;
ProjJournalTrans      projJournalTrans;

HcmWorker             hcmWorker;
  ProjJournalTable      projJournalTable;
    date     tdate;
    str emply,dates;
boolean first = true;

;
projJournalTable.JournalNameId = "Hours";
//projJournalTable.JournalId = "SelfTEst";
projJournalTable.Description = "devel";

   // ProjJournalTable.insert();
}

---------------------------------------------------------------------------------------------------------------
public static  MRKPEmailBatchService construct()
{
    MRKPEmailBatchService      service;

    service = new MRKPEmailBatchService();

    return service;
}

Tax calculation in ax 2009 GST


TaxEngineService
getTaxDocumentGUIDBySource(TableId _sourceTableId, RecId _sourceRecId)

use view in look up in ax 2012

use view in look up in ax 2012

1. create a view
2. create a query , if you want range/filter add datasource to prarent datasource
3. in look up

Client static void lookup(FormStringControl  _ctrl )
{
    SysTableLookup sysTableLookup =                      SysTableLookup::newParameters(tablenum(CustomerView),_ctrl);
    Query          query = new Query(queryStr(SWS_CTMCustomerViewQ));

    query.dataSourceTable(tablenum(CustTable)).addRange(fieldnum(CustTable,InventSiteId)).value(xyz);
    sysTableLookup.addLookupfield(fieldnum(CustomerView, AccountNum));
    sysTableLookup.addLookupfield(fieldNum(CustomerView, Name));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();
}

Company GST number from TaxInformation form Ax 2012

invoiveHeaderTempLoc.GSTRegNum           = TaxInformation_IN::find(salesLine_IN.TaxInformation).getTaxRegistrationNumbersTable(TaxType_IN::GST).RegistrationNumber;

print address in 1 line

invoiveHeaderTempLoc.CompanyAddress = strReplace(dirPartyPostalAddressView.Address,"\n"," ");

error : Voucher number must be filled in while posting PO invoice

error message: Acces denied to purchformletter_confirmation

error message: Acces denied to purchformletter_confirmation 

create Privilege
and add server method : FormletterService.postPurchaseOrderConfirmation
EffectiveAccess to Invoke

excel upload 2009

public class Ale_ImportCustomerDetails
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
}



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 "";
}


public void importCustomerDetails()
{
    Dialog                  dialog;
    DialogGroup             dialogGroup;
    DialogField             dialogfilename;
    Filename                filename;

    COMVariantType          type;
    int                     row;
    NoYesCombo              NoYes;
    NoYes                   NoYes1;
    CustVendorBlocked       CustVendorBlocked;

    AccountNum              custAccount;

    custTable               custTable;
    CustTable               custTablePrimaryContact;
    int                         arrindex;
    Struct                      struct = new Struct();
    container                   defaultDimension;
    container                   attrib , value ;

    ;
    dialog          = new Dialog("Select File For customer Master Import");
    dialogGroup     = dialog.addGroup("Excel File");

    dialogfilename  = dialog.addField(typeId(FilenameOpen));

    if (dialog.run())
    {
        application = SysExcelApplication::construct();
        workbooks   = application.workbooks();
        filename=dialogfilename.value();
        try
        {
            workbooks.open(filename);
        }
        catch (Exception::Error)
        {
            throw error(strFmt("@GLS101746",filename));
        }
        workbook    = workbooks.item(1);
        worksheets  = workbook.worksheets();
        worksheet   = worksheets.itemFromNum(1);
        cells       = worksheet.cells();
        row = 1;
        ttsBegin;
        do
        {
            row++;
            custAccount = this.COMVariant2Str(cells.item(row, 1).value());

            Select forupdate *  from custTable
                where custTable.AccountNum == custAccount;
            if (custTable)
            {
                custTable.initValue();
                //custTable.AccountNum        = this.COMVariant2Str(cells.item(row, 1).value());
                custTable.GSTNumber_IN      = this.COMVariant2Str(cells.item(row, 2).value());
                custTable.update();
            }
            type = cells.item(row+1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);
        ttsCommit;
        workbooks.close();
        info(strFmt("%1 records successfully inserted", row - 1));
        application.quit();
    }
}

customer excel upload

Customer Excel upload


class Ale_CustomerMastersExcelUpload
{
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
}

///---------------------------------------------------------------------------------------------------------------

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 contactPersonContactDetail(DirPartyRecId _partyRecId,
                LogisticsElectronicAddressLocator _email,
                LogisticsElectronicAddressLocator _website,
                LogisticsElectronicAddressLocator _Phone,
                LogisticsElectronicAddressLocator _telex,
                LogisticsElectronicAddressLocator _fax)
{
    DirParty        DirParty;
    DirPartyContactInfoView     contactView;

    DirParty = DirParty::constructFromPartyRecId(_partyRecId);
    contactView.clear();

    if(_Phone)
    {
        contactView.LocationName = "Phone Number";
        contactView.Locator      = _Phone;
        contactView.Type         = LogisticsElectronicAddressMethodType::Phone;
        contactView.Party        = _partyRecId;
        contactView.IsPrimary    = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }

    if(_fax)
    {
        contactView.LocationName = "Fax Number";
        contactView.Locator      = _fax;
        contactView.Type         = LogisticsElectronicAddressMethodType::Fax;
        contactView.Party        = _partyRecId;
        contactView.IsPrimary    = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }

    if(_website)
    {
        contactView.LocationName = "Website";
        contactView.Locator      = _website;
        contactView.Type         = LogisticsElectronicAddressMethodType::URL;
        contactView.Party        = _partyRecId;
        contactView.IsPrimary    = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }

    if(_email)
    {
        contactView.LocationName = "Email";
        contactView.Locator      = _email;
        contactView.Type         = LogisticsElectronicAddressMethodType::Email;
        contactView.Party        = _partyRecId;
        contactView.IsPrimary    = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }
    if(_telex)
    {
        contactView.LocationName = "Email";
        contactView.Locator      = _telex;
        contactView.Type         = LogisticsElectronicAddressMethodType::Email;
        contactView.Party        = _partyRecId;
        contactView.IsPrimary    = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }
}

/////---------------------------------------------------------------------------------------------

public DimensionDefault createDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
 {
     DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
     DimensionDefault                               result;
     int                                                      i;
     DimensionAttribute                            dimensionAttribute;
     DimensionAttributeValue                   dimensionAttributeValue;
     //_attr is dimension name in table DimensionAttribute
     container               conAttr =   _attr;
     container               conValue = _value;
     str                     dimValue;

     for (i = 1; i <= conLen(conAttr); i++)
     {
         dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

         if (dimensionAttribute.RecId == 0)
         {
             continue;
         }

         dimValue = conPeek(conValue,i);

         if (dimValue != "")
         {
             // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
             dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

             // Add the dimensionAttibuteValue to the default dimension
             valueSetStorage.addItem(dimensionAttributeValue);
         }
     }
     result = valueSetStorage.save();
     return result;
 }
////---------------------------------------------------------------------------------------------------------

public void importCustBankAccount()
{
    custBankAccount         custBankAccount;
    LogisticsLocation       LogisticsLocation;
    LogisticsPostalAddress  logisticsPostalAddress;
    Dialog                  dialog;
    DialogGroup             dialogGroup;
    DialogField             dialogfilename;
    Filename                filename;
    COMVariantType          type;
    int                     row;

    dialog          = new Dialog("Select File For customer Bank Master Import");
    dialogGroup     = dialog.addGroup("Excel File");
    dialogfilename  = dialog.addField(extendedTypeStr(FilenameOpen));

    if (dialog.run())
    {
        application = SysExcelApplication::construct();
        workbooks   = application.workbooks();
        filename=dialogfilename.value();
        try
        {
            workbooks.open(filename);
        }
        catch (Exception::Error)
        {
            throw error(strFmt("@GLS101746",filename));
        }
        workbook    = workbooks.item(1);
        worksheets  = workbook.worksheets();
        worksheet   = worksheets.itemFromNum(1);
        cells       = worksheet.cells();
        row = 3;

        ttsBegin;
        do
        {
            row++;
            logisticsLocation.Description      = this.COMVariant2Str(cells.item(row, 9).value());
            logisticsLocation.insert();

            logisticsPostalAddress.Street       = this.COMVariant2Str(cells.item(row, 9).value());
            logisticsPostalAddress.Address      = this.COMVariant2Str(cells.item(row, 9).value());
            logisticsPostalAddress.Location     = LogisticsLocation.RecId;
            logisticsPostalAddress.insert();

            custBankAccount.initValue();
            custBankAccount.custAccount         = this.COMVariant2Str(cells.item(row, 1).value());
            custBankAccount.AccountID           = this.COMVariant2Str(cells.item(row, 2).value());
            custBankAccount.AccountNum          = this.COMVariant2Str(cells.item(row, 3).value());
            custBankAccount.Name                = this.COMVariant2Str(cells.item(row, 4).value());
            custBankAccount.RegistrationNum     = this.COMVariant2Str(cells.item(row, 5).value());
            custBankAccount.SWIFTNo             = this.COMVariant2Str(cells.item(row, 6).value());
            custBankAccount.BankIBAN            = this.COMVariant2Str(cells.item(row, 7).value());
            custBankAccount.Location            = LogisticsLocation.RecId;
            custBankAccount.insert();

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

        ttscommit;
        info(strFmt("%1 records successfully inserted", row - 3));
        application.quit();
    }
}
///--------------------------------------------------------------------------------------------------------
public void importCustomerMaster()
{
    Dialog                  dialog;
    DialogGroup             dialogGroup;
    DialogField             dialogfilename;
    Filename                filename;

    COMVariantType          type;
    int                     row;
    NoYesCombo              NoYes;
    NoYes                   NoYes1;
    CustVendorBlocked       CustVendorBlocked;

    custTable               custTable;
    CustTable               custTablePrimaryContact;
    DirPartyTable           DirPartyTable;
    DirPartyTable               dirPartyTableParent;
    LogisticsPostalAddress      address;
    DirPartyPostalAddressView   addressView;
    DirParty                    dirParty;
    DirPartyRecId               partyRecId;
    ContactPerson               contactPerson;
    DirPerson                   dirPerson, dirPersonEmployee;
    DirPartyLocation            dirPartylocation;
    int                         arrindex;
    Struct                      struct = new Struct();
    container                   defaultDimension;
    container                   attrib , value ;
    LogisticsElectronicAddress  logisticsElectronicAddress;


    ;
    dialog          = new Dialog("Select File For customer Master Import");
    dialogGroup     = dialog.addGroup("Excel File");
    dialogfilename  = dialog.addField(extendedTypeStr(FilenameOpen));

    if (dialog.run())
    {
        application = SysExcelApplication::construct();
        workbooks   = application.workbooks();
        filename=dialogfilename.value();
        try
        {
            workbooks.open(filename);
        }
        catch (Exception::Error)
        {
            throw error(strFmt("@GLS101746",filename));
        }
        workbook    = workbooks.item(1);
        worksheets  = workbook.worksheets();
        worksheet   = worksheets.itemFromNum(1);
        cells       = worksheet.cells();
        row = 3;
        ttsBegin;
        do
        {
            row++;
            custTable.initValue();
            custTable.AccountNum        = this.COMVariant2Str(cells.item(row, 1).value());
            custTable.InvoiceAccount    = this.COMVariant2Str(cells.item(row, 2).value());
            //custTable.FamilyGroup   = this.COMVariant2Str(cells.item(row, 3).value());
            custTable.Currency          = this.COMVariant2Str(cells.item(row, 5).value());
            custTable.custGroup         = this.COMVariant2Str(cells.item(row, 6).value());
            custTable.CustClassificationId  = this.COMVariant2Str(cells.item(row, 7).value());
            //DUNS number 8
            //custTable.WorldCheckDone    = str2enum(NoYes, cells.item(row, 9).value().bStr());
            //custTable.WorldCheckDate    = cells.item(row, 10).value().date();

            select firstFast Name from dirPersonEmployee
                where dirPersonEmployee.Name == this.COMVariant2Str(cells.item(row, 11).value());
            custTable.MainContactWorker = HcmWorker::findByPerson(dirPersonEmployee.RecId).RecId;
            custTable.SegmentId     = this.COMVariant2Str(cells.item(row, 12).value());
            custTable.SubsegmentId  = this.COMVariant2Str(cells.item(row, 13).value());
            custTable.DlvTerm       = this.COMVariant2Str(cells.item(row, 14).value());
            custTable.Blocked           = str2enum(CustVendorBlocked, cells.item(row, 15).value().bStr());
            custTable.PaymTermId        = this.COMVariant2Str(cells.item(row, 16).value());
            custTable.PaymMode          = this.COMVariant2Str(cells.item(row, 17).value());
            custTable.DlvMode           = this.COMVariant2Str(cells.item(row, 18).value());
            //our account number: not require :19
            custTable.CreditRating      = this.COMVariant2Str(cells.item(row, 20).value());
            custTable.CreditMax         = cells.item(row, 21).value().double();
            custTable.InventSiteId      = this.COMVariant2Str(cells.item(row, 28).value());
            custTable.InventLocation    = this.COMVariant2Str(cells.item(row, 29).value());
            custTable.LineOfBusinessId  = this.COMVariant2Str(cells.item(row, 36).value());
            //custTable.IsParent      = str2enum(NoYes1, cells.item(row, 37).value().bStr());
            //stTable.RegExpDate = cells.item(row, 42).value().date();

            select firstFast recId from  dirPartyTableParent
                where dirPartyTableParent.Name == this.COMVariant2Str(cells.item(row, 38).value());
            //custTable.ParentAccount = CustTable::findByPartyRecId(dirPartyTableParent.RecId).AccountNum;//change to code instead of name
            //custTable.ParentAccount = this.COMVariant2Str(cells.item(row, 38).value());

            if(this.COMVariant2Str(cells.item(row, 55).value()) != "")
            {
                attrib += ["BusinessUnit"];
                value += [this.COMVariant2Str(cells.item(row, 55).value())];
            }
            if(this.COMVariant2Str(cells.item(row, 59).value()) != "")
            {
                attrib += ["CVBPM"];
                value += [this.COMVariant2Str(cells.item(row, 59).value())];
            }
            if(this.COMVariant2Str(cells.item(row, 56).value()) != "")
            {
                attrib += ["Department"];
                value += [this.COMVariant2Str(cells.item(row, 56).value())];
            }
            if(this.COMVariant2Str(cells.item(row, 58).value()) != "")
            {
                attrib += ["Project"];
                value += [this.COMVariant2Str(cells.item(row, 58).value())];
            }
            if(this.COMVariant2Str(cells.item(row, 57).value()) != "")
            {
                attrib += ["REGION"];
                value += [this.COMVariant2Str(cells.item(row, 57).value())];
            }
            if(this.COMVariant2Str(cells.item(row, 60).value()) != "")
            {
                attrib += ["Worker"];
                value += [this.COMVariant2Str(cells.item(row, 60).value())];
            }
            custTable.DefaultDimension = this.createDefaultDimension(attrib,value);
            /*AxdDimensionUtil::getDimensionAttributeValueSetId([arrindex,
                                            "BusinessUnit",this.COMVariant2Str(cells.item(row, 55).value()),
                                            "CVBPM", this.COMVariant2Str(cells.item(row, 59).value()) ,
                                            "Department", this.COMVariant2Str(cells.item(row, 56).value()),
                                            "Project", this.COMVariant2Str(cells.item(row, 58).value()),
                                            "REGION", this.COMVariant2Str(cells.item(row, 57).value()),
                                            "Worker", this.COMVariant2Str(cells.item(row, 60).value())]);*/
            custTable.insert();
            partyRecId = custTable.Party;

            //customer name
            select forUpdate DirPartyTable where DirPartyTable.RecId == custTable.Party;
            DirPartyTable.Name = this.COMVariant2Str(cells.item(row, 4).value());
            DirPartyTable.NameAlias = this.COMVariant2Str(cells.item(row, 4).value());
            DirPartyTable.update();

            //Address details
            address.clear();
            address.Street  = this.COMVariant2Str(cells.item(row, 24).value());
            address.City    = this.COMVariant2Str(cells.item(row, 25).value());
            address.State   = this.COMVariant2Str(cells.item(row, 26).value());
            address.CountryRegionId = this.COMVariant2Str(cells.item(row, 27).value());
            address.PostBox  = this.COMVariant2Str(cells.item(row, 22).value());  //

            addressView.LocationName = this.COMVariant2Str(cells.item(row, 23).value());
            addressView.IsPrimary = NoYes::Yes;
            addressView.Party = custTable.Party;
            addressview.initFromPostalAddress(address);

            DirParty = DirParty::constructFromPartyRecId(addressView.Party );
            DirParty.createOrUpdatePostalAddress(addressView);
            /*
            //Tax registration details
            select RecId from dirPartylocation
                where //dirPartylocation.Location ==  addressView.Location &&
                    dirPartylocation.Party == addressView.Party;
                //Year established
                if(cells.item(row, 33).value().date() && this.COMVariant2Str(cells.item(row, 35).value()))
                {
                    this.locationTaxRegistrationUpdate(dirPartylocation.RecId,
                                                        "YearofEst",
                                                        cells.item(row, 33).value().date(),
                                                        dateMax() ,
                                                        "",
                                                        this.COMVariant2Str(cells.item(row, 35).value()));
                }

                //Trade lic num
                if(cells.item(row, 40).value().date() && this.COMVariant2Str(cells.item(row, 39).value()))
                {
                    this.locationTaxRegistrationUpdate(dirPartylocation.RecId,
                                                        "TradeLicense",
                                                        today() ,
                                                        cells.item(row, 40).value().date(),
                                                        "",
                                                        this.COMVariant2Str(cells.item(row, 39).value()));
                }

                //Tax reg no
                if(cells.item(row, 42).value().date() && this.COMVariant2Str(cells.item(row, 41).value()))
                {
                    this.locationTaxRegistrationUpdate(dirPartylocation.RecId,
                                                        "TaxRegDet",
                                                        today() ,
                                                        cells.item(row, 42).value().date(),
                                                        "",
                                                        this.COMVariant2Str(cells.item(row, 41).value()));
                }
            */

            //Customer contact detail
            this.contactPersonContactDetail(custTable.Party,
                    this.COMVariant2Str(cells.item(row, 32).value()),
                    this.COMVariant2Str(cells.item(row, 34).value()),
                    this.COMVariant2Str(cells.item(row, 30).value()),
                    "",
                    this.COMVariant2Str(cells.item(row, 31).value()));


           // ContactPerson details
           // 1st contact
          if(this.COMVariant2Str(cells.item(row, 43).value()))
          {
                DirPerson.clear();
                DirPerson.Name = this.COMVariant2Str(cells.item(row, 43).value()) + " " +
                    this.COMVariant2Str(cells.item(row, 44).value()) + " " +
                    this.COMVariant2Str(cells.item(row, 45).value());
                DirPerson.NameAlias = DirPerson.Name;
                DirPerson.NameSequence = DirNameSequence::find("FirstLastMiddle").RecId;
                DirPerson.insert();

                DirPersonName::addNewNameToPerson(DirPerson.RecId,
                    this.COMVariant2Str(cells.item(row, 43).value()),
                    this.COMVariant2Str(cells.item(row, 44).value()),
                    this.COMVariant2Str(cells.item(row, 45).value()));

                contactPerson.clear();
                contactPerson.initFromcustTable(custTable);
                contactPerson.Party = dirPerson.RecId;
                contactPerson.insert();

                this.contactPersonContactDetail(contactPerson.Party,
                    this.COMVariant2Str(cells.item(row, 46).value()),
                    "",
                    this.COMVariant2Str(cells.item(row, 47).value()),
                    this.COMVariant2Str(cells.item(row, 48).value()),
                    "");
                //primary contact update
                select forUpdate custTablePrimaryContact where custTablePrimaryContact.RecId == CustTable.RecId;
                custTablePrimaryContact.ContactPersonId = contactPerson.ContactPersonId;
                custTablePrimaryContact.update();
          }


            //2nd contact
            if(this.COMVariant2Str(cells.item(row, 49).value()))
            {
                DirPerson.clear();
                DirPerson.Name = this.COMVariant2Str(cells.item(row, 49).value()) + " " +
                        this.COMVariant2Str(cells.item(row, 50).value()) + " " +
                        this.COMVariant2Str(cells.item(row, 51).value());
                DirPerson.NameAlias = DirPerson.Name;
                DirPerson.NameSequence = DirNameSequence::find("FirstLastMiddle").RecId;
                DirPerson.insert();

                DirPersonName::addNewNameToPerson(DirPerson.RecId,
                    this.COMVariant2Str(cells.item(row, 49).value()),
                    this.COMVariant2Str(cells.item(row, 50).value()),
                    this.COMVariant2Str(cells.item(row, 51).value()));

                contactPerson.clear();
                contactPerson.initFromcustTable(custTable);
                contactPerson.Party = dirPerson.RecId;
                contactPerson.insert();

                this.contactPersonContactDetail(contactPerson.Party,
                    this.COMVariant2Str(cells.item(row, 52).value()),
                    "",
                    this.COMVariant2Str(cells.item(row, 53).value()),
                    this.COMVariant2Str(cells.item(row, 54).value()),
                    "");
            }

            type = cells.item(row+1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);
        ttsCommit;
        workbooks.close();
        info(strFmt("%1 records successfully inserted", row - 3));
        application.quit();
    }
}
///-------------------------------------------------------------------------------------------------------
public void locationTaxRegistrationUpdate(RefRecId _dirPartyLocation,
                                TaxRegistrationTypeName _type,
                                TransDate _validFromDate = today(),
                                TransDate  _validToDate = dateMax(),
                                Description _Description =  "-",
                                TaxRegistrationNumber   _TaxRegistrationNumber = "-")
{
    TaxRegistration         taxRegistration;
    TaxRegistrationType     taxRegType;


    taxRegistration.initValue();
    taxRegistration.DirPartyLocation = _dirPartyLocation;
    taxRegistration.TaxRegistrationTypeApplicabilityRule = TaxRegistrationType::findByType(_type).RecId;
    taxRegistration.description = _Description;
    taxRegistration.RegistrationNumber = _TaxRegistrationNumber;
    taxRegistration.ValidFrom = _validFromDate;
    taxRegistration.validTo = _validToDate;

    taxRegistration.insert();
}