static void Esh_ReadExcel(Args _args)
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row=1;
Str Emp_Id,Emp_Name,Emp_Profile;
FileName filename;
Esh_ExcelUpload esh_ExcelUpload; //table buffer
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "D:\\ExcelUpload.xlsx";
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();
do
{
//read data from Excel
row++;
Emp_Id = cells.item(row, 1).value().bStr();
Emp_Name = cells.item(row, 2).value().bStr();
Emp_Profile = cells.item(row, 3).value().bStr();
info(strfmt('%1 - %2', Emp_Id, Emp_Name,Emp_Profile));
type = cells.item(row+1, 1).value().variantType();
//insert data into table
esh_ExcelUpload.clear();
esh_ExcelUpload.Emp_Id = Emp_Id;
esh_ExcelUpload.Emp_Name = Emp_Name;
esh_ExcelUpload.Emp_Profile = Emp_Profile;
esh_ExcelUpload.insert();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
int row=1;
Str Emp_Id,Emp_Name,Emp_Profile;
FileName filename;
Esh_ExcelUpload esh_ExcelUpload; //table buffer
;
application = SysExcelApplication::construct();
workbooks = application.workbooks();
//specify the file path that you want to read
filename = "D:\\ExcelUpload.xlsx";
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();
do
{
//read data from Excel
row++;
Emp_Id = cells.item(row, 1).value().bStr();
Emp_Name = cells.item(row, 2).value().bStr();
Emp_Profile = cells.item(row, 3).value().bStr();
info(strfmt('%1 - %2', Emp_Id, Emp_Name,Emp_Profile));
type = cells.item(row+1, 1).value().variantType();
//insert data into table
esh_ExcelUpload.clear();
esh_ExcelUpload.Emp_Id = Emp_Id;
esh_ExcelUpload.Emp_Name = Emp_Name;
esh_ExcelUpload.Emp_Profile = Emp_Profile;
esh_ExcelUpload.insert();
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
Importing Journals from Excel using X++ in AX 2009
class DIPL_ImportVendInvoiceJournalLines
{
Dialog dialog;
DialogField dialogfield;
Filename filename;
#AviFiles
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
COMVariantType typeModule;
NumberSeq numberSeq;
NumberSequenceTable numSeqTable;
str Name, num,text, currency,businessunit,account,accountType, department,offsetaccount,OffsetAccounttype;
int64 costcenter;
real debit, credit;
container Account1, offsetaccount1;
str acc;
date transdate;
LedgerJournalName ledgerJournalName;
LedgerJournalTable ledgerJournalTable;
LedgerJournalTrans ledgerJournalTrans;
container cont1,cont2,offSetAcctPattern;
int cnt;
LedgerJournalAC AccountNumb, offsetAccountnum;
container ledgerDimension;
LedgerJournalACType LedgerJournalACType;
boolean ret;
}
--------------------------------------------------------------------------------------------------------------------------
public void DataImport()
{
str mSBU,mDepartment,mCostCenter,mPurpose;
str oSBU,oDepartment,oCostCenter,oPurpose;
str invoice;
str TDSgroup,salesTaxGroup,itemSalesTax;
date documentdate;
Voucher voucher;
SysOperationProgress progress = new SysOperationProgress();
int row = 0;
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
ledgerJournalName = ledgerjournalname::find(ledgerjournaltable.JournalName);
row = 1;
do
{
row++;
transdate = cells.item(row, 1).value().date();
Currency = cells.item(row, 2).value().bStr();
accountType = cells.item(row, 3).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()));
}
mSBU = cells.item(row, 5).value().bStr();
mDepartment = cells.item(row, 6).value().bStr();
mCostCenter = cells.item(row, 7).value().bStr();
mPurpose = cells.item(row, 8).value().bStr();
invoice = cells.item(row, 9).value().bStr();
Text = cells.item(row, 10).value().bStr();
Debit = any2real(cells.item(row, 11).value().double());
Credit = any2real(cells.item(row, 12).value().double());
OffsetAccounttype = cells.item(row, 13).value().bStr();
switch(cells.item(row, 14).value().variantType())
{
case COMVariantType::VT_BSTR:
offsetAccountnum = strFmt("%1", cells.item(row, 14).value().bStr());
break;
case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
offsetAccountnum = strFmt("%1", any2int(cells.item(row, 14).value().double()));
break;
case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
offsetAccountnum = strFmt("%1", cells.item(row, 14).value().int());
break;
case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
offsetAccountnum = strFmt("%1", cells.item(row, 14).value().uLong());
break;
case COMVariantType::VT_EMPTY:
offsetAccountnum = '';
break;
default:
throw error(strfmt('Unhandled variant type (%1).', cells.item(row, 1).value().variantType()));
}
oSBU = cells.item(row, 15).value().bStr();
oDepartment = cells.item(row, 16).value().bStr();
oCostCenter = cells.item(row, 17).value().bStr();
oPurpose = cells.item(row, 18).value().bStr();
TDSgroup = cells.item(row, 19).value().bStr();
salesTaxGroup = cells.item(row, 20).value().bStr();
itemSalesTax = cells.item(row, 21).value().bStr();
documentdate = cells.item(row, 22).value().date();
//In Excel cell should be in Text format
try
{
ttsbegin;
ledgerJournalTrans.clear();
ledgerJournalTrans.initValue();
ledgerJournalTrans.JournalNum = ledgerJournalTable.JournalNum;
ledgerJournalTrans.TransDate = transdate;
ledgerJournalTrans.Dimension[1] = mSBU;
ledgerJournalTrans.Dimension[2] = mDepartment;
ledgerJournalTrans.Dimension[3] = mCostCenter;
ledgerJournalTrans.Dimension[4] = mPurpose;
//select firstOnly numSeqTable
// where numSeqTable.RecId == ledgerJournalName.NumberSequenceTable;
if (!voucher)
{
numberseq = numberseq::newGetVoucherFromCode(ledgerJournalName.VoucherSeries);
voucher = numberseq.voucher();
}
ledgerJournalTrans.Voucher = voucher;
ledgerJournalTrans.AccountType = str2enum(LedgerJournalACType, accountType);
ledgerJournalTrans.AccountNum = AccountNumb;
ledgerJournalTrans.Txt = Text;
ledgerJournalTrans.CurrencyCode = Currency;
ledgerJournalTrans.AmountCurDebit = Debit;
ledgerJournalTrans.AmountCurCredit = Credit;
ledgerJournalTrans.OffsetAccountType = str2enum(LedgerJournalACType, offsetaccountType);
ledgerJournalTrans.OffsetAccount = offsetAccountnum;
ledgerJournalTrans.Invoice = invoice;
ledgerJournalTrans.TaxGroup = salesTaxGroup;
ledgerJournalTrans.TaxItemGroup = itemSalesTax;
ledgerJournalTrans.DocumentDate = documentdate;
ledgerJournalTrans.TDSGroup_IN = TDSgroup;
if (ledgerJournalTrans.validateWrite())
{
ledgerJournalTrans.insert();
}
ttscommit;
type = cells.item(row+1, 1).value().variantType();
}
catch(Exception::Error)
{
info(strFmt('Catched an error in row: %1',row));
}
info(strFmt('journal inserted %1',ledgerJournalTable.JournalNum));
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
-------------------------------------------------------------------------------------------------------------------------
public void run(Args _args)
{
;
LedgerJournalTable = _args.record();
dialog = new dialog('Excel Import');
dialogfield = dialog.addField(TypeId(FilenameOpen), 'File Name');
dialog.run();
if(dialog.run())
{
filename =(dialogfield.value());
}
application = SysExcelApplication::construct();
workbooks = application.workbooks();
if(filename)
{
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error('File cannot be opened.');
}
this.DataImport();
}
}
-------------------------------------------------------------------------------------------------------------------------
public static void main(Args args)
{
DIPL_ImportVendInvoiceJournalLines import = new DIPL_ImportVendInvoiceJournalLines();
import.run(args);
}
--------------------------------------------------------------------------------------------------------------------------
void clicked()
{
FormRun callerForm;
Args args = new Args();
DIPL_ImportVendInvoiceJournalLines DIPL_ImportVendInvoiceJournalLines = new DIPL_ImportVendInvoiceJournalLines();
;
args.record(ledgerjournaltable);
DIPL_ImportVendInvoiceJournalLines.run(args);
callerForm = element.args().caller();
callerForm.dataSource().refresh();
callerForm.dataSource().reread();
callerForm.dataSource().research();
}
Importing Invoice Journal lines From CSV file using X++ AX 2012
class KPMG_VendInvoiceJournalLineImport
{
Dialog dialog;
DialogField dialogfield;
Filename filename;
#AviFiles
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
COMVariantType typeModule;
NumberSeq numberSeq;
NumberSequenceTable numSeqTable;
str Name, num,text, currency,businessunit,account,accountType, department,offsetaccount,OffsetAccounttype;
int64 costcenter;
real debit, credit;
container Account1, offsetaccount1;
str acc;
date transdate;
LedgerJournalName ledgerJournalName;
LedgerJournalTable ledgerJournalTable;
LedgerJournalTrans ledgerJournalTrans;
container cont1,cont2,offSetAcctPattern;
int cnt;
DimensionDynamicAccount ledgerDim, offsetledgerDim;
LedgerJournalAC AccountNumb, offsetAccountnum;
container ledgerDimension;
DimensionDefault DimensionDefault;
LedgerJournalACType LedgerJournalACType;
boolean ret;
AsciiIo importFile;
List list;
ListIterator listIterator;
//Comma
CommaTextIo commaTextIo;
container containFromRead;
ledgerJournalTransTaxExtensionIN ledgerJournalTransTaxExtensionIN;
}
--------------------------------------------------------------------------------------------------------------------------
public void CSV_DataImport()
{
str mCostCenter,mEmployee,mIntercompany,mProject;
str oCostCenter,oEmployee,oIntercompany,oProject;
str invoice;
str TDSgroup,salesTaxGroup,itemSalesTax;
date documentdate;
Voucher voucher;
container mCnt;
container record, recordData;
int cols;
recordData = commaTextIo.read();
While(recordData)
{
transdate = str2Date(conPeek(recordData,1),231);
Currency = strFmt("%1", conPeek(recordData,2));
accountType = strFmt("%1", conPeek(recordData,3));
AccountNumb = strFmt("%1", conPeek(recordData,4));
mCostCenter = strFmt("%1", conPeek(recordData,5));
mEmployee = strFmt("%1", conPeek(recordData,6));
mIntercompany = strFmt("%1", conPeek(recordData,7));
mProject = strFmt("%1", conPeek(recordData,8));
invoice = strFmt("%1", conPeek(recordData,9));
Text = strFmt("%1", conPeek(recordData,10));
Debit = any2real(conPeek(recordData,11));
Credit = any2real(conPeek(recordData,12));
OffsetAccounttype = strFmt("%1", conPeek(recordData,13));
offsetAccountnum = strFmt("%1",conPeek(recordData,14));
oCostCenter = strFmt("%1", conPeek(recordData,15));
oEmployee = strFmt("%1", conPeek(recordData,16));
oIntercompany = strFmt("%1", conPeek(recordData,17));
oProject = strFmt("%1", conPeek(recordData,18));
TDSgroup = strFmt("%1",conPeek(recordData,19));
salesTaxGroup = strFmt("%1",conPeek(recordData,20));
itemSalesTax = strFmt("%1",conPeek(recordData,21));
documentdate = str2DateDMY(conPeek(recordData,22));
//In Excel cell should be in Text format
try
{
ttsbegin;
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 = numberseq.voucher();
}
ledgerJournalTrans.Voucher = voucher;
ledgerJournalTrans.AccountType = str2enum(LedgerJournalACType, accountType);
cont1=conNull();
cont2=conNull();
ledgerDimension =conNull();
//Account type
if(mCostCenter != '')
{
cnt++;
cont2+=['Costcentre',mCostCenter];
}
if(mEmployee != '')
{
cnt++;
cont2+=['Employee',mEmployee];
}
if(mIntercompany != '')
{
cnt++;
cont2+=['Intercompany',mIntercompany];
}
if(mProject != '')
{
cnt++;
cont2+=['Project_Customer_Property_HIPE',mProject];
}
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(oCostCenter != '')
{
cnt++;
cont2+=['Costcentre',oCostCenter];
}
if(oEmployee != '')
{
cnt++;
cont2+=['Employee',oEmployee];
}
if(oIntercompany != '')
{
cnt++;
cont2+=['Intercompany',oIntercompany];
}
if(oProject != '')
{
cnt++;
cont2+=['Project_Customer_Property_HIPE',oProject];
}
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;
if (ledgerJournalTrans.validateWrite())
{
ledgerJournalTrans.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;
}
catch(Exception::Error)
{
info(strFmt('Catched an error in row: %1'));
}
info(strFmt('journal inserted %1',ledgerJournalTable.JournalNum));
}
}
------------------------------------------------------------------------------------------------------------------------
public void DataImport()
{
str mCostCenter,mEmployee,mIntercompany,mProject;
str oCostCenter,oEmployee,oIntercompany,oProject;
str invoice;
str TDSgroup,salesTaxGroup,itemSalesTax;
date documentdate;
Voucher voucher;
container mCnt;
SysOperationProgress progress = new SysOperationProgress();
int row = 0;
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 1;
do
{
row++;
transdate = cells.item(row, 1).value().date();
Currency = cells.item(row, 2).value().bStr();
accountType = cells.item(row, 3).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()));
}
mCostCenter = cells.item(row, 5).value().bStr();
mEmployee = cells.item(row, 6).value().bStr();
mIntercompany = cells.item(row, 7).value().bStr();
mProject = cells.item(row, 8).value().bStr();
invoice = cells.item(row, 9).value().bStr();
Text = cells.item(row, 10).value().bStr();
Debit = any2real(cells.item(row, 11).value().double());
Credit = any2real(cells.item(row, 12).value().double());
OffsetAccounttype = cells.item(row, 13).value().bStr();
switch(cells.item(row, 14).value().variantType())
{
case COMVariantType::VT_BSTR:
offsetAccountnum = strFmt("%1", cells.item(row, 14).value().bStr());
break;
case COMVariantType::VT_DECIMAL, COMVariantType::VT_R4, COMVariantType::VT_R8:
offsetAccountnum = strFmt("%1", any2int(cells.item(row, 14).value().double()));
break;
case COMVariantType::VT_I1, COMVariantType::VT_I2, COMVariantType::VT_I4:
offsetAccountnum = strFmt("%1", cells.item(row, 14).value().int());
break;
case COMVariantType::VT_UI1, COMVariantType::VT_UI2, COMVariantType::VT_UI4:
offsetAccountnum = strFmt("%1", cells.item(row, 14).value().uLong());
break;
case COMVariantType::VT_EMPTY:
offsetAccountnum = '';
break;
default:
throw error(strfmt('Unhandled variant type (%1).', cells.item(row, 1).value().variantType()));
}
oCostCenter = cells.item(row, 15).value().bStr();
oEmployee = cells.item(row, 16).value().bStr();
oIntercompany = cells.item(row, 17).value().bStr();
oProject = cells.item(row, 18).value().bStr();
TDSgroup = cells.item(row, 19).value().bStr();
salesTaxGroup = cells.item(row, 20).value().bStr();
itemSalesTax = cells.item(row, 21).value().bStr();
documentdate = cells.item(row, 22).value().date();
//In Excel cell should be in Text format
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 = numberseq.voucher();
}
ledgerJournalTrans.Voucher = voucher;
ledgerJournalTrans.AccountType = str2enum(LedgerJournalACType, accountType);
// Main account dimensions
cont1=conNull();
cont2=conNull();
ledgerDimension =conNull();
//Account type
if(mCostCenter != '')
{
cnt++;
cont2+=['Costcentre',mCostCenter];
}
if(mEmployee != '')
{
cnt++;
cont2+=['Employee',mEmployee];
}
if(mIntercompany != '')
{
cnt++;
cont2+=['Intercompany',mIntercompany];
}
if(mProject != '')
{
cnt++;
cont2+=['Project_Customer_Property_HIPE',mProject];
}
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(oCostCenter != '')
{
cnt++;
cont2+=['Costcentre',oCostCenter];
}
if(oEmployee != '')
{
cnt++;
cont2+=['Employee',oEmployee];
}
if(oIntercompany != '')
{
cnt++;
cont2+=['Intercompany',oIntercompany];
}
if(oProject != '')
{
cnt++;
cont2+=['Project_Customer_Property_HIPE',oProject];
}
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;
if (ledgerJournalTrans.validateWrite())
{
ledgerJournalTrans.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));
}
info(strFmt('journal inserted %1',ledgerJournalTable.JournalNum));
}
while (type != COMVariantType::VT_EMPTY);
application.quit();
}
public void run(Args _args)
{
ledgerJournalTable = _args.record() as ledgerJournalTable;
dialog = new dialog('Excel Import');
dialogfield = dialog.addField(extendedTypeStr(FilenameOpen), 'File Name');
dialog.run();
if(dialog.run())
{
filename =(dialogfield.value());
importFile = new AsciiIo(filename, 'R');
commaTextIo = new CommaTextIO(filename,'R');
if(importFile)
{
this.CSV_DataImport();
}
}
}
--------------------------------------------------------------------------------------------------------------------------
public static void main(Args args)
{
KPMG_VendInvoiceJournalLineImport import = new KPMG_VendInvoiceJournalLineImport();
// import.run();
}
Comments
Post a Comment