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();
}
No comments:
Post a Comment