Ax Technical: Import Journals using Excel x++ code Ax 2012

class HM_GeneralJournalLineImport
{
Dialog dialog;
DialogField dialogfield;
Filename filename;

#AviFiles
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
COMVariantType typeModule;
COM excelCOM;
NumberSeq numberSeq;
NumberSequenceTable numSeqTable;

str Name, num,text, currency,businessunit,account,accountType, department,offsetaccount,OffsetAccounttype, transType;
int64 costcenter;
real debit, credit;
container Account1, offsetaccount1;
str acc;
date transdate;
boolean check;

LedgerJournalName ledgerJournalName;
LedgerJournalTable ledgerJournalTable;
LedgerJournalTrans ledgerJournalTrans;
LedgerJournalTrans_Asset ledgerJournalTrans_Asset;

container cont1,cont2,offSetAcctPattern;
int cnt;
DimensionDynamicAccount ledgerDim, offsetledgerDim;
LedgerJournalAC AccountNumb, offsetAccountnum;

container ledgerDimension;
DimensionDefault DimensionDefault;
LedgerJournalACType LedgerJournalACType;
//AssetTransTypeJournal assetTransTypeJournal;
boolean ret;

AsciiIo importFile;
List list;
ListIterator listIterator;

//Comma
//CommaTextIo commaTextIo;
//container containFromRead;
ledgerJournalTransTaxExtensionIN ledgerJournalTransTaxExtensionIN;
}

public void run(Args _args)
{
boolean validate;
ledgerJournalTable = _args.record() as ledgerJournalTable;
dialog = new dialog('Excel Import');
check = true;
dialogfield = dialog.addField(extendedTypeStr(FilenameOpen), 'File Name');

if(dialog.run())
{
filename =(dialogfield.value());
application = SysExcelApplication::construct();
workbooks = application.workbooks();

if(filename)
{
validate = this.validate(); //for validating Excel financial dimensions whether it is valid or not
if(validate)
{
this.DataImport();
}
application.quit();
}
}
}

//validating dimensions
public boolean validate()
{
CustTable custTable;
VendTable vendTable;
ProjTable projTable;
MainAccount mainAccount;
str mBusinessUnit,mUnit,mProject,mWorker,mVendor,mCustomer,mIntercompany;
str oBusinessUnit,oUnit,oProject,oWorker,oVendor,oCustomer,oIntercompany;
Name accountName;
boolean mret,oret;
container mAccPattern,oAccPattern;
SysOperationProgress progress = new SysOperationProgress();
int row = 0;
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;
progress.setCaption("Journal import");
progress.setAnimation(#AviUpdate);

do
{
row++;
progress.setText(strfmt("Validating journal row - %1", row));
try
{
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('Unhandled variant type (%1).', cells.item(row+1, 1).value().variantType()));
}

accountName = cells.item(row, 5).value().bStr();
mbusinessUnit = cells.item(row, 6).value().bStr();
mUnit = cells.item(row, 7).value().bStr();
mProject = cells.item(row, 8).value().bStr();
mWorker = cells.item(row, 9).value().bStr();
mVendor = cells.item(row, 10).value().bStr();
mCustomer = cells.item(row, 11).value().bStr();
mIntercompany = cells.item(row, 12).value().bStr();

maccPattern = connull();
maccPattern = [7,'BusinessUnit',mbusinessUnit,'Unit',mUnit,'Project',mProject,'Vendor',mVendor,'Customer',mCustomer,'Worker',mWorker,'Intercompany',mIntercompany];
mret = this.checkMainAccountDimensions(maccPattern);

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

switch(cells.item(row, 20).value().variantType())
{
case COMVariantType::VT_BSTR:
offsetAccountnum = strFmt("%1", cells.item(row, 20).value().bStr());
break;
case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
offsetAccountnum = strFmt("%1", any2int(cells.item(row, 20).value().double()));
break;
case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
offsetAccountnum = strFmt("%1", cells.item(row, 20).value().int());
break;
case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
offsetAccountnum = strFmt("%1", cells.item(row, 20).value().uLong());
break;
case COMVariantType::VT_EMPTY:
offsetAccountnum = '';
break;
default:
throw error(strfmt('Unhandled variant type (%1).', cells.item(row, 20).value().variantType()));
}
obusinessUnit = cells.item(row, 21).value().bStr();
oUnit = cells.item(row, 22).value().bStr();
oProject = cells.item(row, 23).value().bStr();
oWorker = cells.item(row, 24).value().bStr();
oVendor = cells.item(row, 25).value().bStr();
oCustomer = cells.item(row, 26).value().bStr();
oIntercompany = cells.item(row, 27).value().bStr();

oAccPattern = conNull();
oAccPattern = [7,'BusinessUnit',obusinessUnit,'Unit',oUnit,'Project',oProject,'Vendor',oVendor,'Customer',oCustomer,'Worker',oWorker,'Intercompany',oIntercompany];
oret = this.checkoffsetDimensions(oAccPattern);

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

return (mret && oret) ? true : false ;
}

public void DataImport()
{
str mBusinessUnit,mUnit,mProject,mWorker,mVendor,mCustomer,mIntercompany;
str oBusinessUnit,oUnit,oProject,oWorker,oVendor,oCustomer,oIntercompany;
str invoice;
str TDSgroup,salesTaxGroup,itemSalesTax;
date documentdate;
Voucher voucher;
NoYes noyes;
container mCnt;
Name accountName,postingProfile,termsofPayment;
ExchRate exchRate;
boolean _selectTriangulation = true;
UnknownNoYes triangulationResult,triangulation;

//Creating of new Vouchers
LedgerJournalTrans ledgerjourDebit,ledgerJourCredit;

SysOperationProgress progress = new SysOperationProgress();
int row = 0;
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;
progress.setCaption("Journal import");
progress.setAnimation(#AviUpdate);

do
{
row++;
progress.setText(strfmt("Journals inserting row - %1", row));
transdate = cells.item(row, 1).value().date();
//voucher = 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('Unhandled variant type (%1).', cells.item(row+1, 1).value().variantType()));
}
accountName = cells.item(row, 5).value().bStr();
mbusinessUnit = cells.item(row, 6).value().bStr();
mUnit = cells.item(row, 7).value().bStr();
mProject = cells.item(row, 8).value().bStr();
mWorker = cells.item(row, 9).value().bStr();
mVendor = cells.item(row, 10).value().bStr();
mCustomer = cells.item(row, 11).value().bStr();
mIntercompany = cells.item(row, 12).value().bStr();
postingProfile = 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());
Currency = cells.item(row, 18).value().bStr();
OffsetAccounttype = cells.item(row, 19).value().bStr();

switch(cells.item(row, 20).value().variantType())
{
case COMVariantType::VT_BSTR:
offsetAccountnum = strFmt("%1", cells.item(row, 20).value().bStr());
break;
case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
offsetAccountnum = strFmt("%1", any2int(cells.item(row, 20).value().double()));
break;
case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
offsetAccountnum = strFmt("%1", cells.item(row, 20).value().int());
break;
case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
offsetAccountnum = strFmt("%1", cells.item(row, 20).value().uLong());
break;
case COMVariantType::VT_EMPTY:
offsetAccountnum = '';
break;
default:
throw error(strfmt('Unhandled variant type (%1).', cells.item(row, 20).value().variantType()));
}
obusinessUnit = cells.item(row, 21).value().bStr();
oUnit = cells.item(row, 22).value().bStr();
oProject = cells.item(row, 23).value().bStr();
oWorker = cells.item(row, 24).value().bStr();
oVendor = cells.item(row, 25).value().bStr();
oCustomer = cells.item(row, 26).value().bStr();
oIntercompany = cells.item(row, 27).value().bStr();

TDSgroup = cells.item(row, 28).value().bStr();
salesTaxGroup = cells.item(row, 29).value().bStr();
itemSalesTax = cells.item(row, 30).value().bStr();
exchRate = any2real(cells.item(row, 31).value().double());
documentdate = cells.item(row, 32).value().date();
termsofPayment = cells.item(row, 33).value().bStr();

try
{
ttsbegin;

ledgerJournalTrans.clear();
ledgerJournalTrans.initValue();
ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
ledgerJournalTrans.TransDate = transdate;
ledgerJournalTrans.Approved = NoYes::Yes;
ledgerJournalTrans.Approver = HcmWorker::userId2Worker(curuserid());
select firstOnly numSeqTable
where numSeqTable.RecId == LedgerJournalName::find(ledgerJournalTable.JournalName).NumberSequenceTable;
/*if (numSeqTable && !voucher)
{
//numberseq = numberseq::newGetVoucherFromCode(numSeqTable.NumberSequence);
voucher = new JournalVoucherNum(JournalTableData::newTable(ledgerJournalTable)).getNew(false);
//voucher = numberseq.voucher();
}*/

select sum(AmountCurDebit) from ledgerjourDebit

where ledgerjourDebit.JournalNum == ledgerJournalTable.JournalNum;

select sum(AmountCurCredit) from ledgerJourCredit

where ledgerJourCredit.journalnum == ledgerJournalTable.journalnum;

if(ledgerjourDebit.AmountCurDebit == ledgerJourCredit.AmountCurCredit)//!voucher &&

{

voucher = new

JournalVoucherNum(JournalTableData::newTable(ledgerJournalTable)).getNew(false);

}

else if(offsetAccountnum)

{

voucher = new JournalVoucherNum(JournalTableData::newTable(ledgerJournalTable)).getNew(false);

}

ledgerJournalTrans.Voucher = voucher;
ledgerJournalTrans.AccountType = str2enum(LedgerJournalACType, accountType);
//ledgerJournalTrans.PostingProfile

// Main account dimensions
cont1=conNull();
cont2=conNull();
ledgerDimension =conNull();
cnt=0;

//Account type
if(mBusinessUnit != '')
{
cnt++;
cont2+=['BusinessUnit',mBusinessUnit];
}
if(mUnit != '')
{
cnt++;
cont2+=['Unit',mUnit];
}
if(mProject != '')
{
cnt++;
cont2+=['Project',mProject];
}
if(mWorker != '')
{
cnt++;
cont2+=['Worker',mWorker];
}
if(mVendor != '')
{
cnt++;
cont2+=['Vendor',mVendor];
}
if(mCustomer != '')
{
cnt++;
cont2+=['Customer',mCustomer];
}
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.PostingProfile = postingProfile;
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+=['BusinessUnit',oBusinessUnit];
}
if(oUnit != '')
{
cnt++;
cont2+=['Unit',oUnit];
}
if(oProject != '')
{
cnt++;
cont2+=['Project',oProject];
}
if(oWorker != '')
{
cnt++;
cont2+=['Worker',oWorker];
}
if(oVendor != '')
{
cnt++;
cont2+=['Vendor',oVendor];
}
if(oCustomer != '')
{
cnt++;
cont2+=['Customer',oCustomer];
}
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;
ledgerJournalTrans.DocumentDate = documentdate;
ledgerJournalTrans.TDSGroup_IN = TDSgroup;
ledgerJournalTrans.Invoice = invoice;
ledgerJournalTrans.Payment = termsofPayment;

/*exchRate = ExchRates::findExchRateDate(CompanyInfo::find().CurrencyCode);
ledgerJournalTrans.ExchRate = exchRates.ExchRate;
exchRates = ExchRates::findExchRateDate(CompanyInfo::find().SecondaryCurrencyCode);
ledgerJournalTrans1.SecondaryExchRate_ABC = exchRates.ExchRate; */

//Exchange rate
if (_selectTriangulation)
{
triangulation = Currency::noYes2UnknownNoYes(Currency::triangulation(currency,
transdate));
}
else
{
triangulation = Currency::noYes2UnknownNoYes(ledgerJournalTrans.Triangulation);
}
if (triangulation == UnknownNoYes::Yes && !Currency::findEuroCurrencyCode())
{
throw error("@SYS70752");
}
ledgerJournalTrans.exchRate = Currency::exchRate(currency,
transdate,
UnknownNoYes::No);

ledgerJournalTrans.exchRateSecond = Currency::exchRateSecond(currency,
transdate,
UnknownNoYes::No);
ledgerJournalTrans.Triangulation = Currency::unknownNoYes2Noyes(triangulation);
//

ledgerJournalTrans.ReverseEntry = str2enum(NoYes,cells.item(row,34).value().bStr());
if(ledgerJournalTrans.ReverseEntry==NoYes::Yes)
{
ledgerJournalTrans.ReverseDate = cells.item(row,35).value().date();
}

if (ledgerJournalTrans.validateWrite())
{
ledgerJournalTrans.insert();
}

//Fixed assets--- if any incase FA
if(ledgerJournalTrans.Accounttype == FA)
{
if(ledgerJournalTrans.RecId)
{
ledgerJournalTrans_asset.RefRecId = ledgerJournalTrans.RecId;
ledgerJournalTrans_asset.AssetId = ledgerJournalTrans.getAssetId();
ledgerJournalTrans_Asset.Company = ledgerJournalTrans.getAssetCompany();
ledgerJournalTrans_asset.BookId = valMod;
ledgerJournalTrans_asset.TransType = AssetTransTypeJournal::Acquisition;
else
ledgerJournalTrans_asset.TransType = AssetTransTypeJournal::Depreciation;
ledgerJournalTrans_asset.insert();
}

ttscommit;
ttsBegin;
delete_from ledgerJournalTransTaxExtensionIN
where ledgerJournalTransTaxExtensionIN.LedgerJournalTrans == ledgerJournalTrans.RecId;

if(ledgerJournalTrans.recid)
{
ledgerJournalTransTaxExtensionIN.initValue();
ledgerJournalTransTaxExtensionIN.TaxModelDocLineExtensionIN::init(ledgerJournalTrans);
ledgerJournalTransTaxExtensionIN.LedgerJournalTrans = ledgerJournalTrans.RecId;
ledgerJournalTransTaxExtensionIN.insert();
}
ttsCommit;

type = cells.item(row+1, 1).value().variantType();
}
catch(Exception::Error)
{
info(strFmt('Catched an error in row: %1',row));
break;
}
info(strFmt('journal inserted %1 - %2',ledgerJournalTable.JournalNum,row));
}

while (type != COMVariantType::VT_EMPTY);

application.quit();
}

public boolean checkMainAccountDimensions(container accPattern)
{
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
int containerElementIndex;
int attributeCount, attributeIndex;
str attributeName, attributeValue;

//boolean check = true;
containerElementIndex = 1;
// Get attribute count
attributeCount = conPeek(accPattern, containerElementIndex);
containerElementIndex++;
// Get attributes
for (attributeIndex = 1; attributeIndex <= attributeCount; attributeIndex++)
{
dimensionAttributeValue = null;
// Get attribute name
attributeName = conPeek(accPattern, containerElementIndex);
containerElementIndex++;
// Validate the Financial Dimenion that was passed in.
dimensionAttribute = AxdDimensionUtil::validateFinancialDimension(attributeName);
// Get attribute value
attributeValue = conPeek(accPattern, containerElementIndex);
containerElementIndex++;
// Validate the Financial Dimenion Value that was passed in.
dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimensionAttribute, attributeValue, false, true);

if (!dimensionAttributeValue)
{
if(attributeValue)
{
warning(strfmt('Dimension Name = %1, Dimension Value %2 does not exists',attributeName, attributeValue));
check = false;
}
}
}

return check;

}

public boolean checkoffsetDimensions(container accPattern)
{
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
int containerElementIndex;
int attributeCount, attributeIndex;
str attributeName, attributeValue;
//boolean check=true ;

containerElementIndex = 1;
// Get attribute count
attributeCount = conPeek(accPattern, containerElementIndex);
containerElementIndex++;
// Get attributes
for (attributeIndex = 1; attributeIndex <= attributeCount; attributeIndex++)
{
dimensionAttributeValue = null;
// Get attribute name
attributeName = conPeek(accPattern, containerElementIndex);
containerElementIndex++;
// Validate the Financial Dimenion that was passed in.
dimensionAttribute = AxdDimensionUtil::validateFinancialDimension(attributeName);
// Get attribute value
attributeValue = conPeek(accPattern, containerElementIndex);
containerElementIndex++;
// Validate the Financial Dimenion Value that was passed in.
dimensionAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValueNoError(dimensionAttribute, attributeValue, false, true);

if (!dimensionAttributeValue)
{
if(attributeValue)
{
warning(strfmt('Dimension Name = %1, Dimension Value %2 does not exists',attributeName, attributeValue));
check = false;
}
}
}

return check;

}

Ax Technical

Tuesday, 6 December 2016

Import Journals using Excel x++ code Ax 2012

No comments:

Post a Comment