Tags » AX 2012

Job upload Fixed Asset Journal

static void TIDF_UploadFAJournal(Args _args)
{
    container                       accEntryPattern;
    container                       offSetEntryPattern;
    //for default dimension
    container                       financialDimensionFromExcel;
    DimensionDefault                defaultDimension;
    int                             countLedger;
    int                             countCon;

    AssetBookTable                  _AssetBookTable;
    AssetBookId                     _AssetBookId;
    LedgerJournalTrans_Asset        _LedgerJournalTrans_Asset;
    LedgerJournalTrans              _LedgerJournalTrans;
    LedgerJournalTable              _LedgerJournalTable;

    AssetBookTableDerived           _AssetBookTableDerived;
    AssetBookTableDerivedJournal    _AssetBookTableDerivedJournal;
    AssetTransTypeJournal           _AssetTransTypeJournal;
    AssetTransType                  _AssetTransType;

    LedgerJournalACType             LedgerJournalACType;
    ledgerJournalName               ledgerJournalName;
    journalID                       JournalID;
    LedgerJournalId                 _journalId;
    axLedgerJournalTable            axLedgerJournalTable;
    axledgerJournalTrans            axLedgerJournalTrans;
    LedgerJournalType               JournalType = ledgerJournalType::Daily;


    TransDate                       _TransDate;
    LedgerTransType                 _LedgerTransType;
    str                             AccountNum;
    LedgerJournalACType             _AccountType;
    CurrencyCode                    _CurrencyCode;
    real                            _ExhcangeRate;
    real                            _AmountCurDebit;
    real                            _AmountCurCredit;
    str                             _InvoiceId;
    TransTxt                        _TransTxt;
    DueDate                         _DueDate;
    DocumentNum                     _DoucumentNum;
    DocumentDate                    _DocumentDate;
    str                             Branch;
    str                             Departement;
    str                             Product;
    str                             Project;
    TaxGroupJournal                 _TaxJournal;
    TaxGroupJournal                 _TaxItemJournal;
    NoYes                           _Transferred;
    BankTransactionType             _BankTransactionType;
    TaxCode                         _TaxCode;
    LedgerJournalACType             _OffsetAccountType;
    str                             _OffsetAcountNum;
    VendPostingProfile              _PostingProfile;


    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename, numSeq;
    str                                         strAccount,strAccountOffset;
    Name                                        dimensionName1,dimensionName2,dimensionName3,dimensionName4;
    str                                         MainAcc,Dim1,Dim2,Dim3,Dim4,OffsetAcc;
    str                                         AccountType,OffsetAccountType;
    str                                         PayReff,BankTransType,DocNo,PostingProfile,noInvoice, transType;
    TransDate                                   DocDate;
    str                                         StrDate;

    boolean         _return = true;
    container dimensionAttrName = Global::TIDgetDimensionName();
    _OffsetAcountNum    = '9900-0000';
    startLengthyOperation();
    filename = @"C:\\#Tectura#\fanddy\UploadFAJournal";//FA Dep Commercial.xlsx";//Depreciation commercial
    //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 = 6;

            ttsBegin;

            while (cells.item(row,5).value().bStr() != '' )
            {
                //financial dimension
                financialDimensionFromExcel = conNull();
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,10).value().bStr();
                if(cells.item(row,10).value().bStr() != '' ) {countLedger++;}
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,11).value().bStr();
                if(cells.item(row,11).value().bStr() != '' ) {countLedger++;}
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,12).value().bStr();
                if(cells.item(row,12).value().bStr() != '' ) {countLedger++;}
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,13).value().bStr();
                if(cells.item(row,13).value().bStr() != '' ) {countLedger++;}
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,14).value().bStr();
                if(cells.item(row,14).value().bStr() != '' ) {countLedger++;}
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,15).value().bStr();
                if(cells.item(row,15).value().bStr() != '' )  {countLedger++;}

                //start from A
                _journalId          = cells.item(row,1).value().bStr();
                _TransDate          = cells.item(row,2).value().date();
                AccountNum          = cells.item(row,3).value().bStr();
                transType           = cells.item(row,4).value().bStr();
                _AssetBookId        = cells.item(row,5).value().bStr();
                _CurrencyCode       = cells.item(row,6).value().bStr();
                _ExhcangeRate       = cells.item(row,7).value().double();
                _AmountCurDebit     = cells.item(row,8).value().double();
                _AmountCurCredit    = cells.item(row,9).value().double();

                //_TransDate          = str2Date(StrDate, 123);
                defaultDimension    = TIDcreateDefaultDimension(dimensionAttrName,financialDimensionFromExcel);

                offSetEntryPattern =
                [   _OffsetAcountNum,
                    _OffsetAcountNum,
                    countLedger
                ];
                countCon = 1;
                while(countCon <= countLedger)
                {
                    offSetEntryPattern += conPeek(dimensionAttrName,countCon);
                    offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                    countCon++;
                }

                _AssetTransType             = str2enum(_AssetTransType, transType);
                _AssetTransTypeJournal      = str2enum(_AssetTransTypeJournal, transType);

                _LedgerJournalTable         = LedgerJournalTable::find(_journalId);
                if (_LedgerJournalTable)
                {
                    _LedgerJournalTrans.clear();
                    _LedgerJournalTrans.JournalNum          = _journalId;
                    _LedgerJournalTrans.TransDate           = _TransDate;
                    _LedgerJournalTrans.AccountType         = LedgerJournalACType::FixedAssets;
                    _LedgerJournalTrans.LedgerDimension     = DimensionStorage::getDynamicAccount(AccountNum, LedgerJournalACType::FixedAssets);
                    _LedgerJournalTrans.DefaultDimension    = defaultDimension;
                    _LedgerJournalTrans.CurrencyCode        = _CurrencyCode;
                    _LedgerJournalTrans.ExchRate            = _ExhcangeRate;
                    _LedgerJournalTrans.PostingProfile      = _PostingProfile;

                    if(_AmountCurDebit != 0)
                    {
                        _LedgerJournalTrans.AmountCurDebit  = _AmountCurDebit;
                    }

                    if(_AmountCurCredit != 0)
                    {
                        _LedgerJournalTrans.AmountCurCredit = _AmountCurCredit;
                    }

                    if(_OffsetAcountNum != "")
                    {
                        _LedgerJournalTrans.OffsetAccountType       = LedgerJournalACType::Ledger;
                        _LedgerJournalTrans.OffsetLedgerDimension   = AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern);
                    }

                    _LedgerJournalTrans.Voucher = NumberSeq::newGetNumFromId(_LedgerJournalTable.ledgerJournalName().NumberSequenceTable).num();
                    _LedgerJournalTrans.insert();

                    if (_LedgerJournalTrans)
                    {
                        if (_AssetBookId)
                        {
                            _AssetBookTable = AssetBookTable::find(_AssetBookId);

                            if (_AssetBookTable)
                            {
                                _LedgerJournalTrans_Asset.clear();
                                _LedgerJournalTrans_Asset.RefRecId      = _LedgerJournalTrans.RecId;
                                _LedgerJournalTrans_Asset.AssetId       = AccountNum;
                                _LedgerJournalTrans_Asset.TransType     = _AssetTransTypeJournal;
                                _LedgerJournalTrans_Asset.BookId        = _AssetBookTable.BookId;
                                _LedgerJournalTrans_Asset.insert();
                            }



                            select firstOnly _AssetBookTableDerived
                                where _AssetBookTableDerived.BookId == _AssetBookId
                                   && _AssetBookTableDerived.AssetTransType == _AssetTransType;

                            if (_AssetBookTableDerived)
                            {
                                _AssetBookTableDerivedJournal.clear();
                                _AssetBookTableDerivedJournal.RefRecId  = _LedgerJournalTrans.RecId;
                                _AssetBookTableDerivedJournal.AssetBookId = _AssetBookTableDerived.BookIdDerived;

                                if (_LedgerJournalTrans.AmountCurCredit > 0)
                                {
                                    _AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurCredit;
                                }
                                else if (_LedgerJournalTrans.AmountCurDebit > 0)
                                {
                                    _AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurDebit;
                                }

                                _AssetBookTableDerivedJournal.insert();
                            }

                        }
                    }
                    print strFmt('Row Number %1 - Asset ID : %2', row,AccountNum);
                    row++;
                }
            }

            info(strFmt('Imported  %1 Items, Journal Numbers : %2',row ,_journalId));
             ttsCommit;
        }
    }
    catch(Exception::Error)
    {
        _return = false;
    }

    application.quit();
}
AX 2012

Job upload General Journal example code 2

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 ... 378 more words
AX 2012

Job upload fixed asset ( AssetTable and AssetBook) example code

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();
    }
}
… 229 more words
AX 2012

Difference between Refresh(), Reread(), Research() and ExecuteQuery() in AX 2012

In X++ we have the below methods for fetching data after any changes are made, below is the short description of what they are and when to use each one. 126 more words

MS Dynamics AX

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

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')
            );
}
AX 2012

Multi-select on form datasource grid

First Method:

callerForm = element.args().caller();
formDataSource = callerForm.dataSource();
;
for(projTable = formDataSource.getFirst(1); projTable; projTable = formDataSource.getNext())
{
info(strFmt(“%1 – %2″,projTable.ProjId, projTable.Name));
}

Second Method:

Inventtable inavnettable; 38 more words

X++

open form from infolog.

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);
AX 2012