Naresh DAX: Import Invoice Journal in Ax 2012

void clicked()
{
LedgerJournalTableExt ledgerJournalTableExt;
LedgerJournalTransExt ledgerJournalTransExt;
int WrkShtNum;
Dimension accountingRegion;
ledgerJournalTable ledgerJournalTableloc;
LedgerJournalTrans LedgerJournalTransloc;
COMVariantType type;
Name Dim1,dim2,dim3,dim4,dim5,AccountNum,LedgerAccountValue,
LedgerOffsetAccountValue,OffsetAccount,dim6,dim7,offdim1,
formatValue,offdim3,offdim4,offdim5,offdim6,offdim7;
LedgerJournalTransTaxExtensionIN LedgerJournalTransTaxExtensionIN;
LedgerDimensionAccount LedgerDim,DefDimension;
LedgerDimensionAccount OffsetledgerDim;
LedgerJournalACType OffsetAccountType;
TransDate transDate;
CurrencyCode currencyCode;
NoYes noYes;
numberSeq numberSeq;

name formatcheck(int val)
{
formatValue = "";
switch(excelcells.item(row, val).value().variantType())
{
case COMVariantType::VT_BSTR:
formatValue = strFmt("%1", excelcells.item(row, val).value().bStr());
break;
case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
formatValue = strFmt("%1", any2int(excelcells.item(row, val).value().double()));
break;
case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
formatValue = strFmt("%1", excelcells.item(row, val).value().int());
break;
case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
formatValue = strFmt("%1", excelcells.item(row, val).value().uLong());
break;
case COMVariantType::VT_EMPTY:
formatValue = '';
break;
default:
throw error(strfmt("Issue in file coloum type.", excelcells.item(row+1, 1).value().variantType()));
}
return formatValue;
}
;
excelApp = SysExcelApplication::construct();
startLengthyOperation();
if(StrLTrim(StrRTrim(OpenExcelFile.text()))=="")
{
ExcelCells = NULL;
excelWorksheet = NULL;
excelApp.quit();
throw error("Empty File Name");
}
else
{
WrkShtNum = WorkSheetNum.value();
excelApp.workbooks().open(OpenExcelFile.text());
excelWorksheet = excelApp.worksheets().itemFromNum(WrkShtNum);
excelCells = excelWorksheet.cells();
//Row = Element.FindFirstValidRowNum();
Row=1;
if(Row == 0)
{
ExcelCells = NULL;
excelWorksheet = NULL;
excelApp.quit();
throw error("ERROR: Excel upload aborted...");
}
ttsBegin;
ledgerJournalTableloc.JournalName = "APInv";
ledgerJournalTableloc.initFromLedgerJournalName(ledgerJournalTableloc.JournalName);
ledgerJournalTableloc.insert();

select ledgerJournalTableExt
where ledgerJournalTableExt.LedgerJournalTable == ledgerJournalTableloc.RecId;
if(!ledgerJournalTableExt)
{
ledgerJournalTableExt.LedgerJournalTable = ledgerJournalTableloc.RecId;
ledgerJournalTableExt.insert();
}
ttsCommit;
do
{
try
{
ttsBegin;
Row++;
if(row > 1)
{
transDate = excelcells.item(Row,1).value().date();
currencyCode = formatcheck(2);
if(transDate && currencyCode)
{
numberSeq = NumberSeq::newGetVoucherFromCode(NumberSequenceTable::find(ledgerJournalName::find(ledgerJournalTableloc.JournalName).NumberSequenceTable).NumberSequence);
LedgerJournalTransloc.voucher = numberseq.voucher();
LedgerJournalTransloc.initValue();
LedgerJournalTransloc.initFromLedgerJournalTable_LT(ledgerJournalTable);
LedgerJournalTransloc.TransDate = transDate ;
LedgerJournalTransloc.CurrencyCode = currencyCode;
LedgerJournalTransloc.Company = formatcheck(3);
LedgerJournalTransloc.AccountType = str2enum(OffsetAccountType,formatcheck(4));
LedgerJournalTransloc.JournalNum = LedgerJournalTableloc.JournalNum;
LedgerAccountValue = formatcheck(5);
Dim1 = formatcheck(6);
Dim2 = formatcheck(7);
Dim3 = formatcheck(8);
Dim4 = formatcheck(9);
Dim5 = formatcheck(10);
Dim6 = formatcheck(11);
DefDimension = element.DefaultDimension(Dim1, Dim2,Dim3,Dim4,Dim5,Dim6,"");
LedgerJournalTransloc.Invoice = formatcheck(12);
LedgerJournalTransloc.TaxGroup = formatcheck(13);
LedgerJournalTransloc.TaxItemGroup = formatcheck(14);
LedgerJournalTransloc.Txt = excelcells.item(Row,15).value().bStr();
LedgerJournalTransloc.AmountCurDebit = excelcells.item(Row,16).value().double();
LedgerJournalTransloc.AmountCurCredit = excelcells.item(Row,17).value().double();
LedgerJournalTransloc.OffsetCompany = formatcheck(18);
OffsetAccountType = str2enum(OffsetAccountType,formatcheck(19));
LedgerOffsetAccountValue = formatcheck(20);
LedgerJournalTransloc.DocumentDate = excelcells.item(Row,21).value().date();
LedgerJournalTransloc.Approved = NoYes::Yes;
LedgerJournalTransloc.Approver = HcmWorker::userId2Worker(curUserId());
LedgerJournalTransloc.Due = LedgerJournalTransloc.TransDate;
LedgerJournalTransloc.TransactionType = LedgerTransType::Vend;

if(LedgerAccountValue)
{
if(LedgerJournalTransloc.AccountType == LedgerJournalACType::Ledger)
{
ledgerDim = DimensionDefaultingService::serviceCreateLedgerDimension(DimensionStorage::getDefaultAccountForMainAccountNum(LedgerAccountValue), DefDimension);
LedgerJournalTransloc.LedgerDimension = DimensionAttributeValueCombination::find(ledgerDim).Recid;
}
else
{
LedgerJournalTransloc.DefaultDimension = DefDimension;

ledgerDim = DimensionStorage::getDynamicAccount(LedgerAccountValue, LedgerJournalTransloc.AccountType);
LedgerJournalTransloc.LedgerDimension = DimensionAttributeValueCombination::find(ledgerDim).Recid;

if(LedgerJournalTransloc.AccountType == LedgerJournalACType::cust)
{
LedgerJournalTransloc.PostingProfile = CustParameters::findByCompany(LedgerJournalTransloc.Company).PostingProfile;
}
if(LedgerJournalTransloc.AccountType == LedgerJournalACType::Vend)
{
LedgerJournalTransloc.PostingProfile = VendParameters::find().PostingProfile;
}
}
}

LedgerJournalTransloc.OffsetAccountType = OffsetAccountType;

if(LedgerOffsetAccountValue)
{
if(LedgerJournalTransloc.OffsetAccountType == LedgerJournalACType::Ledger)
{
OffsetledgerDim = DimensionDefaultingService::serviceCreateLedgerDimension(DimensionStorage::getDefaultAccountForMainAccountNum(LedgerOffsetAccountValue), DefDimension);
LedgerJournalTransloc.OffsetLedgerDimension = OffsetledgerDim;
}
else
{
OffsetledgerDim = DimensionStorage::getDynamicAccount(LedgerOffsetAccountValue, LedgerJournalTransloc.OffsetAccountType);
LedgerJournalTransloc.OffsetLedgerDimension = DimensionAttributeValueCombination::find(OffsetledgerDim).Recid;
LedgerJournalTransloc.OffsetDefaultDimension = DefDimension;
}
}

LedgerJournalTransloc.LineNum = LedgerJournalTrans::lastLineNum(LedgerJournalTransloc.JournalNum) + 1;
LedgerJournalTransloc.ExchRate = Currency::exchRate(LedgerJournalTransloc.CurrencyCode, LedgerJournalTransloc.TransDate);
LedgerJournalTransloc.insert();

ledgerJournalTransExt.LedgerJournalTrans = LedgerJournalTransloc.RecId;
ledgerJournalTransExt.insert();

ledgerJournalTransTaxExtensionIN.LedgerJournalTrans = LedgerJournalTransloc.RecId;
ledgerJournalTransTaxExtensionIN.TaxInformation_IN = TaxInformation_IN::findDefaultbyLocation(CompanyInfo::findByCompany_IN().PrimaryAddressLocation).RecId;
ledgerJournalTransTaxExtensionIN.insert();
}
}
ttsCommit;
}
catch
{
Error(strfmt("Upload Failed in row %1", row));
}
type = excelcells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);

info(strfmt("Journals %1 imported successfully",ledgerJournalTableloc.JournalNum));
excelapp.quit();

}
endLengthyOperation();
LedgerJournalTable_ds.executeQuery();
}

-------------------------------------------------------validated Excel-----------------------------------

int FindFirstValidRowNum()
{
int R = 1;
;
while(R < 28)
{
if(excelCells.item(R,1).value().bStr() == "TransDate"
&& excelCells.item(R,2).value().bStr() == "Currency"
&& excelCells.item(R,3).value().bStr() == "Company Code"
&& excelCells.item(R,4).value().bStr() == "AccountType"
&& excelCells.item(R,5).value().bStr() == "AccountNumber"
&& excelCells.item(R,6).value().bStr() == "ProfitCenter"
&& excelCells.item(R,7).value().bStr() == "Department"
&& excelCells.item(R,8).value().bStr() == "Offset account type"
&& excelCells.item(R,9).value().bStr() == "Offset account"
&& excelCells.item(R,10).value().bStr() == "Credit"
&& excelCells.item(R,11).value().bStr() == "Department"
&& excelCells.item(R,12).value().bStr() == "Divisons"
&& excelCells.item(R,13).value().bStr() == "BusinessUnit"
&& excelCells.item(R,14).value().bStr() == "Classes"
&& excelCells.item(R,15).value().bStr() == "Products"
&& excelCells.item(R,16).value().bStr() == "SubProducts"
&& excelCells.item(R,17).value().bStr() == "Invoice"
&& excelCells.item(R,18).value().bStr() == "Sales tax group"
&& excelCells.item(R,19).value().bStr() == "Item sales tax group"
&& excelCells.item(R,20).value().bStr() == "Description"
&& excelCells.item(R,21).value().bStr() == "Debit"
&& excelCells.item(R,22).value().bStr() == "Credit"
&& excelCells.item(R,23).value().bStr() == "Offset Company Code"
&& excelCells.item(R,24).value().bStr() == "OffsetAccount type"
&& excelCells.item(R,25).value().bStr() == "Offset account number"
&& excelCells.item(R,26).value().bStr() == "DocumentDate"
&& excelCells.item(R,27).value().bStr() == "Reverse"
&& excelCells.item(R,28).value().bStr() == "Reverse date")
return R+1;

R++;
}
error("ERROR: Not a valid ledger journal import excel file !!!");
return 0;
}

Friday, 28 September 2018

Import Invoice Journal in Ax 2012