Job upload General Journal example code 2

July 31, 2015 Sti F. Cavendish AX 2012, x++ AX 2012, GENERAL JOURNAL, GL

static void TIDF_UploadJournal(Args _args)
{
    container   accEntryPattern;
    container   offSetEntryPattern;
    container   financialDimensionFromExcel;
    container   conDimensionName;
    int         countCon;
    int         countLedger;
    RecId       recIdDefaultDimension;

    DimensionDynamicAccount parmLedgerDimension;
    DimensionDynamicAccount parmOffsetLedgerDimension;

    LedgerJournalTable   ledgerJournalTable;
    LedgerJournalACType  LedgerJournalACType;
    ledgerJournalName    ledgerJournalName;
    journalID            JournalID;
    axLedgerJournalTable axLedgerJournalTable;
    axledgerJournalTrans axLedgerJournalTrans;
    LedgerJournalType JournalType = ledgerJournalType::Daily;


    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename, numSeq;

    boolean         _return = true;
    ;

    startLengthyOperation();
    filename = "C:\\test.xlsx";
    JournalID = "G0615-0040";// <<< JOURNALNUM
    //filename = _filePath;

    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();

            row = 5;

            ledgerJournalTable = ledgerJournalTable::find(JournalID);
            conDimensionName = Global::TIDgetDimensionName();

            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //empty container
                financialDimensionFromExcel = conNull();
                countLedger = 0;

                //Create Trans ...
                axLedgerJournalTrans = new axLedgerJournalTrans();
                axLedgerJournalTrans.parmJournalNum(JournalID);
                //start from A
                axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date());
                axLedgerJournalTrans.parmAccountType(str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr()));
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,17).value().bStr();
                if(cells.item(row,17).value().bStr() != "") {countLedger++;}
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,18).value().bStr();
                if(cells.item(row,18).value().bStr() != "") {countLedger++;}
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,19).value().bStr();
                if(cells.item(row,19).value().bStr() != "") {countLedger++;}
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,20).value().bStr();
                if(cells.item(row,20).value().bStr() != "") {countLedger++;}
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,21).value().bStr();
                if(cells.item(row,21).value().bStr() != "") {countLedger++;}
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,22).value().bStr();
                if(cells.item(row,22).value().bStr() != "")  {countLedger++;}

                //ledger Dimension
                if(axLedgerJournalTrans.parmAccountType() == LedgerJournalACType::Ledger)
                {
                    accEntryPattern =
                    [   cells.item(row, 3).value().bStr(),
                        cells.item(row, 3).value().bStr(),
                        countLedger
                    ];

                    countCon = 1;
                    while(countCon <= countLedger)
                    {
                        accEntryPattern += conPeek(conDimensionName,countCon);
                        accEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                        countCon++;
                    }
                    parmLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern));
                }
                else
                {
                    parmLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 3).value().bStr(),axLedgerJournalTrans.parmAccountType());
                }
                AxLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, LedgerDimension), false);
                axLedgerJournalTrans.parmLedgerDimension(parmLedgerDimension);

                //offset dimension
                axLedgerJournalTrans.parmOffsetAccountType(str2Enum(LedgerJournalACType, cells.item(row,8).value().bStr()));
                if(axLedgerJournalTrans.parmOffsetAccountType() == LedgerJournalACType::Ledger)
                {
                    offSetEntryPattern =
                    [   cells.item(row, 9).value().bStr(),
                        cells.item(row, 9).value().bStr(),
                        countLedger
                    ];

                    countCon = 1;
                    while(countCon <= countLedger)
                    {
                        offSetEntryPattern += conPeek(conDimensionName,countCon);
                        offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                        countCon++;
                    }
                    parmOffsetLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern));
                }
                else
                {
                    parmOffsetLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 9).value().bStr(),axLedgerJournalTrans.parmAccountType());
                }
                
                axLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, offsetLedgerDimension), false);
                axLedgerJournalTrans.parmOffsetLedgerDimension(parmOffsetLedgerDimension);
                if(cells.item(row,5).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double());
                }

                if(cells.item(row,6).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double());
                }

                axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr());
                axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr());
                axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double());
                axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr());
                axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr());
                axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr());
                axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr());
                axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date());
                //
                axLedgerJournalTrans.parmPostingProfile(cells.item(row,23).value().bStr());

                recIdDefaultDimension = Global::TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
                axLedgerJournalTrans.parmDefaultDimension(recIdDefaultDimension);
                axLedgerJournalTrans.parmOffsetDefaultDimension(recIdDefaultDimension);

                axLedgerJournalTrans.save();
                row++;
            }
               ttsCommit;

            info('Success !!');
            application.quit();
        }
    }
    catch(Exception::Error)
    {
        info("Journal Upload Error");
        application.quit();
    }

}

Job upload fixed asset ( AssetTable and AssetBook) example code

July 31, 2015 Sti F. Cavendish AX 2012, x++ ASSETBOOK, ASSETTABLE, AX 2012, Barcode, FIXED ASSETS, UPLOAD

static void TIDF_uploadFAwithValueModels(Args _args)
{
    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;
    AssetLedger         assetLedger;
    
    container   financialDimensionFromExcel;
    container   conDimensionName;
    
    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename;
    ;
    
    startLengthyOperation();
    //variable yang diisi manual
    filename              = "C:\\FA master.xlsx";
    assetPostingProfile   = "FA POSTING";
    depreciationStartDate = today();
    LastDepreciationDate  = today();
    AcquisitionDate       = today();
    
    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 = TIDgetDimensionName();

            row = 5;
            
            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       = TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
                
                //variable
                assetId           = TIDComVariant2STR(cells.item(row,2).value());
                assetGroupId      = TIDComVariant2STR(cells.item(row,1).value());
                assetLocationId   = TIDComVariant2STR(cells.item(row,6).value());
                assetName         = TIDComVariant2STR(cells.item(row,3).value());
                assetServiceLife  = str2num(TIDComVariant2STR(cells.item(row,4).value()));
                assetLifeTimeRest = str2num(TIDComVariant2STR(cells.item(row,5).value()));
                assetBookId       = 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
                if(!assetTable)
                {
                    axAssetTable    = new AxAssetTable();
                    axAssetTable.parmAssetId(assetId);
                    axAssetTable.parmAssetGroup(assetGroupId);
                    axAssetTable.parmName(assetName);
                    axAssetTable.parmLocation(assetLocationId);
                    axAssetTable.save();
                }
                
                //create assetBook
                assetBook = assetBook::find(assetId,assetBookId);
                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.ServiceLife           = assetServiceLife;
                    assetBook.LifeTime              = assetServiceLife*12;
                    assetBook.LifeTimeRest          = assetLifeTimeRest;
                    assetBook.DepreciationStartDate = depreciationStartDate;
                    assetBook.LastDepreciationDate  = LastDepreciationDate;
                    assetBook.AcquisitionDate       = AcquisitionDate;
                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.update();
                }
                
              
                row++;
            }
            ttsCommit;
            application.quit();
        }
    }    
    catch(Exception::Error)
    {
        info("Fixed Asset upload Error");
        application.quit();
    }
}

TIDGetDimensionName

public static 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;
}

TIDcreateDefaultDimension

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

TIDComVariant2STR

public static 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;
}

source :
http://krishhdax.blogspot.com/2012/05/ax2012-import-fixed-assets-table.html
https://axblog4u.wordpress.com/2012/09/06/tip-comvarianttype-for-real-values-in-dynamics-ax/

Convert null date from outer join on view to maxDate with SysComputedColumn

July 29, 2015 Sti F. Cavendish AX 2012, x++ MAXDATE, NULL DATE, OUTER JOIN, RELATION, SYSCOMPUTEDCOLUMN, VIEW

Example Code :

public static server str ReleasePostingDate()
{

    return SysComputedColumn::if(
            SysComputedColumn::isNullExpression(SysComputedColumn::returnField(
            tableStr(TIDAPUninvoiceConsignmentItem) ,
            identifierStr(LedgerJournalTableRealease),
            fieldStr(LedgerJournalTable, PostedDateTime))),
            SysComputedColumn::cast(strFmt("'%1'",DateTimeUtil::date(DateTimeUtil::maxValue())),'NVARCHAR'),
            SysComputedColumn::cast(SysComputedColumn::returnField(
            tableStr(TIDAPUninvoiceConsignmentItem) ,
            identifierStr(LedgerJournalTableRealease),
            fieldStr(LedgerJournalTable, PostedDateTime)),'NVARCHAR')
            );
}

open form from infolog.

July 23, 2015July 26, 2015 Sti F. Cavendish AX 2012, x++ FORMS, INFOLOG, LINK

example code

SysInfoAction_FormRun    infoAction = SysInfoAction_FormRun::newFormName(formStr(TIDFakturPajakTableIN));

infoAction.parmDescription("Open form VAT IN");
infoAction.parmCallerBuffer(VendInvoiceJour); // automatic dynalink
info("Purchase invoice contains VAT IN. Please record TAX Invoice Number in ID Localization module after this.", "", infoAction);

example max value datetime on Query AOT ranges

July 10, 2015July 14, 2015 Sti F. Cavendish AX 2012, Technical Tutorial AOT, QUERY, RANGES, UTCDATETIME

example :

value example is “>1/1/2154 06:59:59 am”

you can modify the query AOT value with condition expression see example on :
https://msdn.microsoft.com/en-us/library/bb314753.aspx

create Address , Phone , Email for Worker example code

July 9, 2015 Sti F. Cavendish AX 2012, x++ ADDRESS, CREATE, EMAIL, HCMWORKER, PHONE, WORKER

Static void WorkerHireAddressUpdate(HCMworker _worker,StreetName _address = "",str _Phone = "",str _email = "")
{
    
    HcmWorker                   hcmWorker;
    DirPersonRecId              _dirPersonRecid;
    DirPartyPostalAddressView   addressView;
    dirPartyContactInfoView     contactView;
    DirParty                    dirParty;
    container                   roles;
    
    HcmWorker = _worker;
    _dirPersonRecid = HcmWorker.Person;
    roles  = [LogisticsLocationRole::findBytype(LogisticsLocationRoleType::Home).RecId];
    
    //create address
    DirParty = DirParty::constructFromPartyRecId(_dirPersonRecid);
    if(_address)
    {
        //addressView.LocationName  
        //addressView.City    = 
        //addressView.State   = 
        //addressView.ZipCode =
        
        addressView.CountryRegionId = LogisticsAddressCountryRegion::findByISOCode(SysCountryRegionCode::countryInfo(curext())).CountryRegionId;
        addressView.Street  = _address;
        addressView.IsPrimary   = NoYes::Yes;
        addressView.Party   = _dirPersonRecid;
        DirParty.createOrUpdatePostalAddress(addressView,roles);
    }
    
    //create primary email
    if(_email)
    {
        contactView.clear();
        contactView.LocationName                = 'Primary Email';
        contactView.Locator                     = _email;
        contactView.Type                        = LogisticsElectronicAddressMethodType::Email;
        contactView.Party                       = _dirPersonRecid;
        contactView.IsPrimary                   = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }
    
    //create primary phone
    if(_Phone)
    {
        contactView.clear();
        contactView.LocationName                = 'Primary Phone';
        contactView.Locator                     = _Phone;
        contactView.Type                        = LogisticsElectronicAddressMethodType::Phone;
        contactView.Party                       = _dirPersonRecid;
        contactView.IsPrimary                   = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }

}

get company current Country Region Code example code

July 9, 2015 Sti F. Cavendish AX 2012, x++ AX 2012, COUNTRY REGION CODE, CURRENT WORKER, X++

  addressView.CountryRegionId = LogisticsAddressCountryRegion::findByISOCode(SysCountryRegionCode::countryInfo(curext())).CountryRegionId;

hello Dynamics AX

AX TECHNICAL BLOG

Month: July 2015