Uncategorized

Import Fixed Asset Journal in Microsoft Dynamics AX2012 R3

Requirement:

I need to Import the Fixed Asset Journal, we can do that with DIXF as well but why I need to do it through job is because to get the Offset Account from Fixed Asset Posting Profile and merge the Financial dimensions of the value model set on Fixed Asset to the Offset Account of the Journal Line:

Fixed Assets –> Setup –> Fixed asset posting profiles.

In my case I only need to get the offset account of the depreciation Ledger Account and we only have 1 posting profile that’s why I get it by hard coding the posting profile name. In generally it is set on the Fixed Asset Value Model(General Tab) on Fixed Asset Master.

FAPostingProfile

I need to Merge the following fixed assets financial dimension to the depreciation offset account set on posting profile:

FAFD

The code to Import the asset journal :

static void UploadFixedAssetJournal(Args _args)
{

DimensionDefault defaultDimension,defaultDimensionOffset,offsetAccountFromPostingProfile;
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;
str ValueModel;

SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
int row,rowCount;
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;
AssetBook assetBook;

boolean _return = true;
container dimensionAttrName ;

startLengthyOperation();
filename = @”Excel File Folder.xlsx”;

try
{
if (workbooks.open(filename, false , true ))
{
workbook = workbooks.item(1);
workSheets = workbook.worksheets();
workSheet = workSheets.itemFromNum(1);
cells = workSheet.cells();

row = 1;

ttsBegin;

//Finding the already created Fixed Asset Journal to import the lines

_LedgerJournalTable = LedgerJournalTable::findByRecId(5637146851);
while (cells.item(row,1).value().bStr() != “” )
{
rowCount++;

 
StrDate = ’31/7/2017′;
_TransDate = str2Date(StrDate, 123);
AccountNum = cells.item(row,1).value().bStr();

_AssetBookId = cells.item(row,1).value().bStr();

select firstOnly BookId,DefaultDimension from assetBook
where AssetBook.AssetId == _AssetBookId;

valueModel = AssetBook.BookId;

//Get the financial dimensions from the value model of the fixed asset

defaultDimensionOffset = assetBook.DefaultDimension;

//Get the offset account from posting profile of depreciation Ledger account type

offsetAccountFromPostingProfile = AssetLedgerAccounts::assetOffsetLedgerDimension(_AssetBookId,valueModel,AssetTransType::Depreciation,”FA”,false);

 

_AssetBookId = cells.item(row,1).value().bStr();

_CurrencyCode = ‘AED’;

_AmountCurCredit = cells.item(row,3).value().double();

 

if (_LedgerJournalTable)
{
_LedgerJournalTrans.clear();
_LedgerJournalTrans.JournalNum = _LedgerJournalTable.JournalNum;
_LedgerJournalTrans.TransDate = _TransDate;
_LedgerJournalTrans.TransactionType = LedgerTransType::FixedAssets;
_LedgerJournalTrans.AccountType = LedgerJournalACType::FixedAssets;
_LedgerJournalTrans.LedgerDimension = DimensionStorage::getDynamicAccount(AccountNum, LedgerJournalACType::FixedAssets);
_LedgerJournalTrans.CurrencyCode = _CurrencyCode;

//_LedgerJournalTrans.PostingProfile = _PostingProfile;

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

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

 

_LedgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger;

// Merging the offset account and the financial dimension.
_LedgerJournalTrans.OffsetLedgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension(offsetAccountFromPostingProfile,defaultDimensionOffset);

 

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

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

if (_AssetBookTable)
{
_LedgerJournalTrans_Asset.clear();
_LedgerJournalTrans_Asset.RefRecId = _LedgerJournalTrans.RecId;
_LedgerJournalTrans_Asset.AssetId = AccountNum;
_LedgerJournalTrans_Asset.TransType = AssetTransTypeJournal::Depreciation;
_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 ,_LedgerJournalTable.JournalNum));
ttsCommit;
}
}
catch(Exception::Error)
{
_return = false;
}

application.quit();
}

Output:

FAJournalLine

Thanks,

Happy Daxing with Rizz 😉

#MicrosoftDynamicsAX2012 #AX2012 #FixedAssetJournal#ImportFromExcel #FixedAssetJournalInAX  #AX2012R3

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s