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/
hello Dynamics AX 