Ax Helper
Home dynamics ax ax 2012 – upload fixed asset
dynamics ax

ax 2012 – upload fixed asset

By Ronny   Posted in dynamics ax
Posted on

this post is discussing about how to upload fixed asset in ax 2012 FP

if you have asset to registered in ax 2012, there are 2 ways to do, first is manual input in entity–>fixed asset–>common–>fixed assets–>fixed assets

second is upload the data through xls file

if you want to upload you’ll need to follow the step:

  • have xls contains fixed asset data, if you need the acquisition journal and accumulation depreciation journal, you must provide the xls also
  • have the xpo for upload, i will explain the xpo later, but if you need to upload acquisition journal and accumulation depreciation journal, you must have other xpo also

please look for this xls 20150430-FAFIX it is containing asset data master only, such as Asset Group, Asset Number, Asset Name, Asset Location, Asset QTY, Asset UOM, and Asset Costs (purchase cost)

another file is 20150430-ABFIX it is asset book, contains data posting profile, value model depreciation run date, service life, depreciation periods, acquisition date, acquisition price, financial dimension and full month

financial dimension is in recid, so if you need to find the recid, you should trial test financial dimension in sample data

to upload FAFIX and ABFIX, just execute this 2 xpo


xpo upload assettable

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
static void ron_tmp(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    AssetTable Aste ;
    str AssetGroup="" , AssetId="" ;
    str _assetid = "";
    int row;
    FileName filename;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    filename = "C:\\upload\\FA\\20150430-FAFIX.xlsx";
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    row =2;
    ttsBegin;
    try{
        do
        {
            row++;
            aste.AssetGroup =cells.item(row, 1).value().bStr();
            aste.AssetId  =cells.item(row, 2).value().bStr();
            aste.Name  =cells.item(row, 3).value().bStr();
            aste.Location  =cells.item(row, 4).value().bStr();
            aste.Quantity  =cells.item(row, 5).value().int();
            aste.UnitOfMeasure  =cells.item(row, 6).value().bStr();
            //aste.UnitCost  =cells.item(row, 7).value().double();
            aste.insert();
            _assetid = aste.AssetId;
            info(strfmt('upload %1 success', _assetid  ));
            type = cells.item(row+1, 1).value().variantType();
        }
    
        while (type != COMVariantType::VT_EMPTY);
    }
    catch
    {
        //info(strFmt("error at %1",_assetid));
        throw error(strFmt("error at %1",_assetid));
    }
    ttsCommit;
    application.quit();
}

xpo upload asset book

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
static void ron_tmp(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    AssetBook   Asb;
    AssetBookMerge Asbm;
    
    str AssetGroup="" , AssetId="" ;
    str _assetid = "";
    int row;
    FileName filename;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    //filename = "C:\\item.xls";
    filename = "C:\\upload\\FA\\20150430-ABFIX.xlsx";
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    row =1;
    ttsBegin;
    
    try{
        do
        {
            row++;
            
            select forUpdate Asb
                where Asb.AssetId == cells.item(row, 1).value().bStr()
                    && Asb.BookId == cells.item(row, 3).value().bStr();
            
            Asb.PostingProfile = cells.item(row, 2).value().bStr();
            Asb.LifeTime = cells.item(row, 6).value().double();
            Asb.LifeTimeRest=cells.item(row, 10).value().double();           
            Asb.DepreciationStartDate = cells.item(row, 4).value().date();
            Asb.LastDepreciationDate = systemDateGet();
            Asb.AcquisitionPrice =cells.item(row, 8).value().double();
            Asb.AcquisitionDate  =cells.item(row, 7).value().date();
            Asb.ServiceLife =cells.item(row, 5).value().double();
            Asb.Depreciation = NoYes::Yes;
            Asb.DefaultDimension = cells.item(row, 9).value().double();
            
            Asb.update();
            
            select forUpdate Asbm
                where Asbm.AssetId == cells.item(row, 1).value().bStr()
                    && Asbm.BookId == cells.item(row, 3).value().bStr();
            Asbm.PostingProfile= cells.item(row, 2).value().bStr();
            Asbm.AcquisitionDate  =cells.item(row, 7).value().date();
            Asbm.AcquisitionPrice =cells.item(row, 8).value().double();
            Asbm.DepreciationStartDate = cells.item(row, 4).value().date();
            Asbm.ServiceLife = cells.item(row, 5).value().double();
            Asbm.LifeTime= cells.item(row, 6).value().double();
            Asbm.LastDepreciationDate = systemDateGet();
            Asbm.LifeTimeRest=cells.item(row, 10).value().double();           
            Asbm.Depreciation = NoYes::Yes;
            Asbm.DefaultDimension = cells.item(row, 9).value().double();
            Asbm.update();
            
            info(strfmt('%1 sukses', Asb.AssetId ));
            type = cells.item(row+1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);
    }
    catch
    {
        throw error(strFmt("error at %1",_assetid));
    }
    ttsCommit;
    application.quit();
}

and how about the journal? if your finance have the acquisition journal and depreciation journal, data must updated also, use this file sample for journal 20150430-JurnalAkuisisi 20150430-JurnalDepresiasi

create the journal header in fixed assets–>journals–>fixed assets, use the number for acquisition and depreciation in the xls file

this is the xpo for journal

xpo upload acquisition journal

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
static void ron_tmp(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    
    LedgerJournalTrans ljtrans;
    LedgerJournalTrans ljtrans2;
    LedgerJournalTrans_Asset ljtrans_asset;
    LedgerJournalAC ljtrans_actype;
    
    str AssetGroup="" , AssetId="" ;
    str _assetid = "0001-05-IK-ACE-14";
    str _journalnum = "JN14006131";
    int row;
    FileName filename;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    //filename = "C:\\item.xls";
    filename = "C:\\upload\\FA\\20150430-JurnalAkuisisi.xlsx";
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    row =1;
    ttsBegin;
    
    try{
        do
        {
            row++;
            _assetid=cells.item(row, 1).value().bStr();
            ljtrans.JournalNum = _journalnum;
            ljtrans.TransDate = systemDateGet();
            ljtrans.TransactionType = 0;       
            ljtrans.LineNum = row;
        
            ljtrans.OffsetLedgerDimension = 5637148691;
            ljtrans.AccountType = 5;
            ljtrans.Txt = "upload";
            ljtrans.AmountCurDebit = cells.item(row, 5).value().double();
            ljtrans.CurrencyCode = "IDR";
            ljtrans.ExchRate = 100;
            ljtrans.PostingProfile = cells.item(row, 2).value().bStr();
            ljtrans.Voucher=cells.item(row, 4).value().bStr();
            ljtrans.Company = "mtl_";
            //ljtrans.LedgerDimension=5637268074;
            ljtrans.LedgerDimension = DimensionStorage::getDynamicAccount(_assetid,5);
            ljtrans.insert();
        
            select forupdate ljtrans2
                where ljtrans2.LineNum == row
                    && ljtrans2.JournalNum==ljtrans.JournalNum;
        
            ljtrans_asset.RefRecId = ljtrans2.RecId;
            ljtrans_asset.AssetId = _assetid;
            ljtrans_asset.BookId = cells.item(row, 3).value().bStr();
            ljtrans_asset.TransType = 1;
            ljtrans_asset.DepreciationStartDate = systemDateGet();
            ljtrans_asset.insert();
            
            info(strFmt("%1", row));
            type = cells.item(row+1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);
    }
    catch
    {
        throw error(strFmt("error at %1",_assetid));
    }
    ttsCommit;
    application.quit();
}

xpo upload depreciation journal

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
static void ron_tmp(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    
    LedgerJournalTrans ljtrans;
    LedgerJournalTrans ljtrans2;
    LedgerJournalTrans_Asset ljtrans_asset;
    LedgerJournalAC ljtrans_actype;
    
    str AssetGroup="" , AssetId="" ;
    str _assetid = "0001-05-IK-ACE-14";
    str _journalnum = "JN15001414";
    int row;
    FileName filename;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    //specify the file path that you want to read
    //filename = "C:\\item.xls";
    filename = "C:\\upload\\FA\\20150430-JurnalDepresiasi.xlsx";
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    row =1;
    ttsBegin;
    
    try{
        do
        {
            row++;
            _assetid=cells.item(row, 1).value().bStr();
            ljtrans.JournalNum = _journalnum;
            ljtrans.TransDate = systemDateGet();
            ljtrans.TransactionType = 0;       
            ljtrans.LineNum = row;
        
            ljtrans.OffsetLedgerDimension = 5637148691;
            ljtrans.AccountType = 5;
            ljtrans.Txt = "upload dpr";
            ljtrans.AmountCurCredit = cells.item(row, 5).value().double();
            ljtrans.CurrencyCode = "IDR";
            ljtrans.ExchRate = 100;
            ljtrans.PostingProfile = cells.item(row, 2).value().bStr();
            ljtrans.Voucher=cells.item(row, 4).value().bStr();
            ljtrans.Company = "mtl_";
            //ljtrans.LedgerDimension=5637268074;
            ljtrans.LedgerDimension = DimensionStorage::getDynamicAccount(_assetid,ljtrans.AccountType);
            ljtrans.insert();
        
            select forupdate ljtrans2
                where ljtrans2.LineNum == row
                    && ljtrans2.JournalNum==ljtrans.JournalNum;
        
            ljtrans_asset.RefRecId = ljtrans2.RecId;
            ljtrans_asset.AssetId = _assetid;
            ljtrans_asset.BookId = cells.item(row, 3).value().bStr();
            ljtrans_asset.TransType = 3;
            ljtrans_asset.DepreciationStartDate = systemDateGet();
            ljtrans_asset.insert();
            
            info(strFmt("%1", row));
            type = cells.item(row+1, 1).value().variantType();
        }
        while (type != COMVariantType::VT_EMPTY);
    }
    catch
    {
        throw error(strFmt("error at %1",_assetid));
    }
    info(strFmt("total record is %1", row));
    ttsCommit;
    application.quit();
}

Author

Ronny

Leave a Reply