Microsoft Dynamics AX Technical Blog: January 2017

static void DIPL_ExcelImportGeneralJournals(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
Name name;
FileName filename;
int row;
str path;
str filetype;
int i;
NoYes NoYes;
FileIoPermission perm;
DocuValue docuvalue;
#define.FileMode('W')
LedgerJournalName LedgerJournalName;
LedgerJournalTable ledgerJournalTable;
LedgerJournalTrans ledgerJournalTrans;
LedgerJournalCheckPost ledgerJournalCheckPost;
LedgerJournalACType LedgerJournalACType;
ledgerJournalTrans_Project ledgerJournalTrans_Project;
NumberSeq numberseq;
voucher voucher;
int flag,flag1 ;
LedgerJournalNameId LedgerJournalNameId = 'BPJ 16-17';

//BankAccountID BankAccountID = 'EUR OPER';
//ledgerAccount offsetAccount = '601500';
amountCur amountCur = 102;
;

//specify the file path that you want to read
filename = "E:\\GenLines1.xls"; //path of excel

perm = new FileIOPermission(filename, #FileMode);
perm.assert();
application = SysExcelApplication::construct();
workbooks = application.workbooks();
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();
flag = 0;
//progress.setAnimation(#AviTransfer);
Row=1;
try
{

do
{
row++;

ttsbegin;
if(flag==0)
{
// Find a ledgerJournalName record
select firstonly LedgerJournalName
where LedgerJournalName.JournalName ==LedgerJournalNameId;
//Created the ledgerJournalTable
ledgerJournalTable.JournalName =LedgerJournalName.JournalName;
ledgerJournalTable.initFromLedgerJournalName();
ledgerJournalTable.Name = 'Bank Payment Journal';
ledgerJournalTable.insert();
flag = 1;
}

// if(flag==1)
// {
// numberseq =NumberSeq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
// flag++;

// }

ledgerJournalTrans.clear();
ledgerJournalTrans.initValue();

if (!voucher)
{
numberseq = numberseq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
voucher = numberseq.voucher();
}
ledgerJournalTrans.Voucher = voucher;
//Generate the transaction line

ledgerJournalTrans.JournalNum =ledgerJournalTable.JournalNum;

//ledgerJournalTrans.ExchRate =Currency::exchRate(ledgerJournalTrans.CurrencyCode);
ledgerJournalTrans.AccountType =str2enum(LedgerJournalACType,cells.item(row,1).value().bstr());

ledgerJournalTrans.TransDate = today();
ledgerJournalTrans.AccountNum = cells.item(row, 2).value().bStr();
ledgerJournalTrans.Txt = cells.item(row, 3).value().bStr();
ledgerJournalTrans.AmountCurDebit = cells.item(row, 4).value().double();
ledgerJournalTrans.AmountCurCredit = cells.item(row, 5).value().double();
ledgerJournalTrans.CurrencyCode = cells.item(row, 6).value().bStr();
// ledgerJournalTrans.OffsetAccount = offsetAccount;
ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Bank;
if(ledgerJournalTrans.validateWrite())
{
ledgerJournalTrans.insert();
}
if(ledgerJournalTrans.AccountType==LedgerJournalACType::Project)
{
ledgerJournalTrans_Project.clear();
ledgerJournalTrans_Project.initValue();
ledgerJournalTrans_Project.CategoryId = cells.item(row, 7).value().bStr();
ledgerJournalTrans_Project.RefRecId = ledgerJournalTrans.RecId;
ledgerJournalTrans_Project.LinePropertyId = cells.item(row, 8).value().bStr();
ledgerJournalTrans_Project.ProjId = ledgerJournalTrans.AccountNum;
if(ledgerJournalTrans.AmountCurCredit)
{
ledgerJournalTrans_Project.CostPrice = ledgerJournalTrans.AmountCurCredit;
}
else if(ledgerJournalTrans.AmountCurDebit)
{
ledgerJournalTrans_Project.CostPrice = ledgerJournalTrans.AmountCurDebit;
ledgerJournalTrans_Project.Qty = 1;
ledgerJournalTrans_Project.SalesCurrencyId = ledgerJournalTrans.currencyCode();
if(ledgerJournalTrans_Project.validateWrite())
{
ledgerJournalTrans_Project.insert();
}
}
}
info(strfmt('Journal Id:%1',ledgerJournalTable.JournalNum));
//Post the Journal
// ledgerJournalCheckPost= ledgerJournalCheckPost::newLedgerJournalTable(ledgerJournalTable,NoYes::Yes);
// ledgerJournalCheckPost.run();
ttscommit;

type = cells.item(row+1, 1).value().variantType();
}

while (type != COMVariantType::VT_EMPTY);

info('done');

}
catch(Exception::Error)

{

workbooks.close();

CodeAccessPermission::revertAssert();
application.quit();

ttsabort;

}

workbooks.close();

CodeAccessPermission::revertAssert();

application.quit();

}

static void Journal(Args _args)

{

str mBusinessUnit,mLOB, mCLOB, mDept, mLocation, mOrigin,mProject, mEmployee,mIntercompany;

str oBusinessUnit,oLOB, oCLOB, oDept, oLocation, oOrigin, oProject, oEmployee,oIntercompany;

str invoice;

str TDSgroup,salesTaxGroup,itemSalesTax;

date documentdate;

Voucher voucher;

TaxOnItem TaxOnItem;

TaxGroupData TaxGroupData;

real TaxAmount = 0, TaxAmount_1 = 0;

TaxValue TaxValue = 0, TaxValue_1 = 0;

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

Name name;

FileName filename;

str path;

str filetype;

int i;

real Debit,Credit;

NoYes NoYes;

FileIoPermission perm;

DocuValue docuvalue;

#define.FileMode('W')

TransDate transdate;

str Currency,accountType,Accountnumb,text,OffsetAccounttype,offsetAccountnum;

ledgerJournalTrans ledgerJournalTrans;

ledgerJournalTable ledgerJournalTable;

Numbersequencetable numSeqTable;

NumberSeq numberseq;

LedgerJournalACType LedgerJournalACType;

container cont1,cont2,ledgerDimension,offSetAcctPattern;

DimensionDefault DimensionDefault; //EDT

DimensionDynamicAccount ledgerDim, offsetledgerDim;

int row = 0,cnt;

filename = "D:\\jourimport1sample-APINV-DEV(1).xlsx";

perm = new FileIOPermission(filename, #FileMode);

perm.assert();

application = SysExcelApplication::construct();

workbooks = application.workbooks();

try

{

workbooks.open(filename);

}

catch (Exception::Error)

{

throw error("@SYS19358");

}

workbook = workbooks.item(1);

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1);

cells = worksheet.cells();

row = 1;

startLengthyOperation();

//progress.setCaption("@KPM1054");

//progress.setAnimation(#AviUpdate);

{

row++;

//progress.setText(strfmt("@KPM1055", row));

transdate = cells.item(row, 1).value().date();

Currency = cells.item(row, 2).value().bStr();

accountType = cells.item(row, 3).value().bStr();

AccountNumb = strFmt("%1", cells.item(row, 4).value().bStr());

switch(cells.item(row, 4).value().variantType())

{

case COMVariantType::VT_BSTR:

AccountNumb = strFmt("%1", cells.item(row, 4).value().bStr());

break;

case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:

AccountNumb = strFmt("%1", any2int(cells.item(row, 4).value().double()));

break;

case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:

AccountNumb = strFmt("%1", cells.item(row, 4).value().int());

break;

case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:

AccountNumb = strFmt("%1", cells.item(row, 4).value().uLong());

break;

case COMVariantType::VT_EMPTY:

AccountNumb = '';

break;

default:

throw error(strfmt("@KPM1056", cells.item(row+1, 1).value().variantType()));

}

mbusinessUnit = cells.item(row, 5).value().bStr();

mLOB = cells.item(row, 6).value().bStr();

mCLOB = cells.item(row, 7).value().bStr();

mDept = cells.item(row, 8).value().bStr();

mLocation = cells.item(row, 9).value().bStr();

mOrigin = cells.item(row, 10).value().bStr();

mProject = cells.item(row, 11).value().bStr();

mEmployee = cells.item(row, 12).value().bStr();

mIntercompany = cells.item(row, 13).value().bStr();

//invoice = cells.item(row, 14).value().bStr();

Text = cells.item(row, 15).value().bStr();

Debit = any2real(cells.item(row, 16).value().double());

Credit = any2real(cells.item(row, 17).value().double());

OffsetAccounttype = cells.item(row, 18).value().bStr();

switch(cells.item(row, 19).value().variantType())

{

case COMVariantType::VT_BSTR:

offsetAccountnum = strFmt("%1", cells.item(row, 19).value().bStr());

break;

case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:

offsetAccountnum = strFmt("%1", any2int(cells.item(row, 19).value().double()));

break;

case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:

offsetAccountnum = strFmt("%1", cells.item(row, 19).value().int());

break;

case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:

offsetAccountnum = strFmt("%1", cells.item(row, 19).value().uLong());

break;

case COMVariantType::VT_EMPTY:

offsetAccountnum = '';

break;

default:

throw error(strfmt("@KPM1056", cells.item(row, 19).value().variantType()));

}

obusinessUnit = cells.item(row, 20).value().bStr();

oLOB = cells.item(row, 21).value().bStr();

oCLOB = cells.item(row, 22).value().bStr();

oDept = cells.item(row, 23).value().bStr();

oLocation = cells.item(row, 24).value().bStr();

oOrigin = cells.item(row, 25).value().bStr();

oProject = cells.item(row, 26).value().bStr();

oEmployee = cells.item(row, 27).value().bStr();

oIntercompany = cells.item(row, 28).value().bStr();

TDSgroup = cells.item(row, 29).value().bStr();

salesTaxGroup = cells.item(row, 30).value().bStr();

itemSalesTax = cells.item(row, 31).value().bStr();

documentdate = cells.item(row, 32).value().date();

//In Excel cell should be in Text format

try

{

ttsbegin;

voucher = '';

ledgerJournalTrans.clear();

ledgerJournalTrans.initValue();

ledgerJournalTrans.JournalNum = "INMF-00485";//ledgerJournalTable.JournalNum;

ledgerJournalTrans.TransDate = transdate;

ledgerJournalTrans.Approved = NoYes::Yes;

ledgerJournalTrans.Approver = HcmWorker::userId2Worker(curuserid());

select firstOnly numSeqTable

where numSeqTable.RecId == LedgerJournalName::find("GenJN").NumberSequenceTable;

if (numSeqTable && !voucher)

{

numberseq = numberseq::newGetVoucherFromCode(numSeqTable.NumberSequence);

voucher = numberseq.voucher();

}

ledgerJournalTrans.Voucher = voucher;

ledgerJournalTrans.AccountType = str2enum(LedgerJournalACType, accountType);

// Main account dimensions

cont1=conNull();

cont2=conNull();

ledgerDimension =conNull();

cnt=0;

//Account type

if(mBusinessUnit != '')

{

cnt++;

cont2+=['BU',mBusinessUnit];

}

if(mLOB != '')

{

cnt++;

cont2+=['LOB',mLOB];

}

if(mCLOB != '')

{

cnt++;

cont2+=['CLOB',mCLOB];

}

if(mDept != '')

{

cnt++;

cont2+=['Department',mDept];

}

if(mLocation != '')

{

cnt++;

cont2+=['Location',mLocation];

}

if(mOrigin != '')

{

cnt++;

cont2+=['Origin',mOrigin];

}

if(mProject != '')

{

cnt++;

cont2+=['Projects',mProject];

}

if(mEmployee != '')

{

cnt++;

cont2+=['Worker',mEmployee]; //Employee

}

if(mIntercompany != '')

{

cnt++;

cont2+=['Intercompany',mIntercompany];

}

if(ledgerJournalTrans.AccountType == LedgerJournalACType::Ledger)

{

cont1+=['MainAccount',AccountNumb,cnt];

cont1+=cont2;

ledgerDim = AxdDimensionUtil::getLedgerAccountId(cont1);

if(ledgerDim==0)

{

offSetAcctPattern = [AccountNumb,AccountNumb];

ledgerDim = AxdDimensionUtil::getLedgerAccountId( offSetAcctPattern);

}

ledgerJournalTrans.LedgerDimension = ledgerDim;

}

else

{

ledgerDim = DimensionStorage::getDynamicAccount( AccountNumb,ledgerJournalTrans.AccountType);

ledgerDimension +=cnt;

ledgerDimension +=cont2;

DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

ledgerJournalTrans.LedgerDimension = ledgerDim;

LedgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans,LedgerDimension));

ledgerJournalTrans.DefaultDimension = DimensionDefault;

}

ledgerJournalTrans.Txt = Text;

ledgerJournalTrans.CurrencyCode = Currency;

ledgerJournalTrans.AmountCurDebit = Debit;

ledgerJournalTrans.AmountCurCredit = Credit;

if(offsetaccountType)

{

ledgerJournalTrans.OffsetAccountType = str2enum(LedgerJournalACType, offsetaccountType);

}

else

{

ledgerJournalTrans.OffsetAccountType = LedgerJournalACType::Ledger;

}

cont1=conNull();

cont2=conNull();

ledgerDimension =conNull();

cnt=0;

//Offset Account Type

if(oBusinessUnit != '')

{

cnt++;

cont2+=['BU',oBusinessUnit];

}

if(oLOB != '')

{

cnt++;

cont2+=['LOB',oLOB];

}

if(oCLOB != '')

{

cnt++;

cont2+=['CLOB',oCLOB];

}

if(oDept != '')

{

cnt++;

cont2+=['Department',oDept];

}

if(oLocation != '')

{

cnt++;

cont2+=['Location',oLocation];

}

if(oOrigin != '')

{

cnt++;

cont2+=['Origin',oOrigin];

}

if(oProject != '')

{

cnt++;

cont2+=['Projects',oProject];

}

if(oEmployee != '')

{

cnt++;

cont2+=['Worker',oEmployee];

}

if(oIntercompany != '')

{

cnt++;

cont2+=['Intercompany',oIntercompany];

}

if (ledgerJournalTrans.OffsetAccountType == LedgerJournalACType::Ledger && offsetAccountnum)

{

cont1+=['MainAccount',offsetAccountnum,cnt];

cont1+=cont2;

offsetledgerDim =AxdDimensionUtil::getLedgerAccountId(cont1);

if(offsetledgerDim == 0)

{

offSetAcctPattern = [offsetAccountnum,offsetAccountnum];

offsetledgerDim = AxdDimensionUtil::getLedgerAccountId( offSetAcctPattern);

}

ledgerJournalTrans.OffsetLedgerDimension = offsetledgerDim;

}

else

{

if(offsetAccountnum)

{

offsetledgerDim = DimensionStorage::getDynamicAccount(offsetAccountnum,ledgerJournalTrans.OffsetAccountType);

ledgerDimension +=cnt;

ledgerDimension +=cont2;

DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

ledgerJournalTrans.OffsetLedgerDimension = offsetledgerDim;

LedgerJournalTrans.modifiedField(fieldNum(LedgerJournalTrans,OffsetLedgerDimension));

ledgerJournalTrans.OffsetDefaultDimension = DimensionDefault;

}

ledgerJournalTrans.TaxGroup = salesTaxGroup;

ledgerJournalTrans.TaxItemGroup = itemSalesTax;

//tax1 = Tax::calcTaxAmount(ledgerJournalTrans.TaxGroup, ledgerJournalTrans.TaxItemGroup, Systemdateget(), ledgerJournalTrans.CurrencyCode, ledgerJournalTrans.AmountCurCredit, TaxModuleType::);

// TaxLedgerJournalCalculate::totalTaxAmountSingleLine(TaxJournalCall::Journal, _ledgerJournalTrans, 0, 0);

//TaxAmount_1 = 0;

//if(ledgerJournalTrans.TaxItemGroup && ledgerJournalTrans.TaxGroup) //&& ledgerJournalTrans.AmountCurCredit != 0)

//{

//while select TaxOnItem

//where TaxOnItem.TaxItemGroup == ledgerJournalTrans.TaxItemGroup

//{

//if(TaxOnItem)

//{

//while select TaxGroupData

//where TaxGroupData.TaxGroup == ledgerJournalTrans.TaxGroup

//&& TaxGroupData.TaxCode == TaxOnItem.TaxCode

//{

//if(TaxGroupData)

//{

//TaxAmount = 0;

//TaxValue = TaxData::find(TaxOnItem.TaxCode, Systemdateget(), 0).TaxValue;

//TaxValue_1 += TaxValue;

//if(ledgerJournalTrans.AmountCurCredit != 0)

//TaxAmount = (ledgerJournalTrans.AmountCurCredit* TaxValue)/100;

//else if(ledgerJournalTrans.AmountCurDebit != 0)

//TaxAmount = (ledgerJournalTrans.AmountCurCredit* TaxValue)/100;

//TaxAmount_1 += TaxAmount;

//}

ledgerJournalTrans.DocumentDate = documentdate;

ledgerJournalTrans.TDSGroup_IN = TDSgroup;

if (ledgerJournalTrans.validateWrite())

{

ledgerJournalTrans.insert();

info(strFmt('journal inserted %1 - %2',ledgerJournalTable.JournalNum,row));

}

ttscommit;

type = cells.item(row+1, 1).value().variantType();

}

catch(Exception::Error)

{

info(strFmt('Catched an error in row: %1',row));

}

while (type != COMVariantType::VT_EMPTY);

application.quit();

endLengthyOperation();

}

excel format :

Microsoft Dynamics AX Technical Blog

Popular Posts

Friday, January 20, 2017

About splitters in Ax 2012

Wednesday, January 18, 2017

Importing General journals through excel in Ax 2009

X++ code for Importing general journals through excel in ax 2012

Export a copy of the standard user acceptance testing (UAT) database

Live traffic Feed