Navigation : AP--> Journals-->Invoice Journal->
We create One Class::
Method1: ClassDeclaration:
public class kr_ImportAPInvJournal extends ObjectRun
{
}
Method2: Run
void run()
{
// 12-13-2012 change to use excel - Richard s
Amount amount;
LedgerJournalTable j;
LedgerJournalTrans t,ledgerJournalTrans;
LedgerPeriod p;
LedgerJournalTable ledgerjournaltable;
int k,i;
Dialog dlg;
DialogField dfFileName, dfJournalName, dfDate, dfAccountNum, dfOffsetAccount;
DialogField dfVendAccount, dfFreightAccount, dfFuelAccount;
DialogField dfProdLine;
FileNameOPen fileName;
LedgerJournalId jourId;
transDate tDate;
Voucher curVoucher;
str 60 customer;
// CustTable custTable;
// CustTrans custTrans;
// CustTransOpen custTransOpen;
InvoiceId invoiceId;
// CustAccount invoiceAccount;
CurrencyCode currencyCode;
AccountNum glAccount, glOffsetAccount, glFuelAccount='556001',glFreightAccount='556000', glVendAccount='8093064';
date docdate, duedate, transdate;
num docnum;
Amountmst amountmst, total;
AmountCur amountcur;
ExchRate exchrate;
LedgerJournalNameID journalName;
container con;
// ASCIIIO file;
// TextIO file;
str period;
str 100 txt;
int upd;
int iYear, iMonth;
SysExcelApplication ExcelApp;
boolean rowFound = true;
DialogField dialogFieldStartLineNum;
DialogField dialogFieldEndLineNum;
int lineNum = 2;
int lineNumEnd = 9;
str testStr;
counter readcounter = 0;
counter insertCounter = 0;
counter updateCounter = 0;
counter errCounter = 0;
Boolean errorEncounter = false;
Boolean ret = true;
//Begin XREN 1/10/2013
SalesLine salesLine;
SalesTable salesTable;
CustTable custTable;
real costCenter;
str costCenterStr;
str marketSegment;
str customerDim;
str productLine;
SalesId salesId;
Counter vendCount;
Amount vendAmount;
//StartJanardhan
PaymTermId paymTermId, Payment;
LedgerJournalEngine ledgerJournalEngine;
DueDate due;
VendTable vendTable;
PaymTerm paymTerm;
PaymMode paymMode;
//EndJanardhan
;
dlg = new Dialog('Upload AP Invoice Journal from Excel file');
dfFileName = dlg.addFieldValue(typeid(FileNameOpen),filename);
dfJournalName = dlg.addFieldValue(typeid(LedgerJournalNameId),journalName,"Jrnl (APIMPT)","Name of Journal");
dfVendAccount = dlg.addFieldValue(typeid(VendAccount), glVendAccount, "Vendor Account", "Vendore Account Number");
dfFreightAccount = dlg.addFieldValue(typeid(ledgerAccount), glFreightAccount, "Freight Account", "Freight Account Number");
dfFuelAccount = dlg.addFieldValue(typeid(LedgerAccount), glFuelAccount,"Fuel Account", "Fuel Account");
dialogFieldStartLineNum = dlg.addField(typeid(Integer),"Starting Liine Number","start loading records at line number specified");
dialogFieldStartLineNum.value(lineNum);
dialogFieldEndLineNum = dlg.addField(typeid(Integer),"Ending Liine Number","stop loading records at line number specified");
dialogFieldEndLineNum.value(lineNumEnd);
// dfDate = dlg.addFieldValue(typeid(transdate),tDate);
if (!dlg.run())
throw error('Cancelled by the user');
filename = dfFileName.value();
journalName = dfJournalName.value(); // 'APIMPT'; // -- or APINV
// jourId = dfJournalNumber.value(); // ---------- need to create journal
// use file for doc/invoice date
glVendAccount = dfVendAccount.value();
glfreightAccount = dfFreightAccount.value();
glFuelAccount = dfFuelAccount.value();
ttsbegin; //Xren 1/16/2013
// ------------------------ j = new LedgerJournalTable();
j = kr_ImportAPInvJournal::createLedgerJournalTable(journalName);
jourId = j.JournalNum;
// tDate = dfDate.value(); // ? for posting date?
// glAccount = dfAccountNum.value();
// glOffsetAccount = dfOffsetAccount.value();
/* file = new TextIO(filename,'rt'); -----change to use excel
// file.inFieldDelimiter('\t');
file.inFieldDelimiter(',');
file.inRecordDelimiter('\n');
info(strfmt('File %1',filename));
*/
if (dialogFieldStartLineNum.value() > 0)
{
lineNum = dialogFieldStartLineNum.value();
readCounter = lineNum - 1;
}
if (dialogFieldEndLineNum.value() > 0)
{
lineNumEnd = dialogFieldEndLineNum.value();
}
ExcelApp = SysExcelApplication::construct();
ExcelApp.workbooks().open(filename);
k = -1;
select PeriodStart, PeriodEnd, PeriodMonth, PeriodYear from p
where p.PeriodStart <= tDate
&& p.PeriodEnd >= tDate
&& p.PeriodMonth > 0
&& p.PeriodYear > 0;
iMonth = p.PeriodMonth;
iYear = p.PeriodYear;
try
{
while (rowFound && ExcelApp.activeSheet().cells().range(strfmt('A%1',lineNum)).item(lineNum))
{
// while (file.status() == IO_Status::Ok)
//begin looping through input file
// con = file.read();
// if (conlen(con) == 0)
// continue;
k++;
// if (k == 0)
// continue;
/* if (conlen(con) != 23)
{
warning(strfmt("line %1 skipped, found %2 columns", k, conlen(con)));
continue;
}
*/
// print k;
readcounter++;
if (lineNum > lineNumEnd)
{
rowfound = false;
continue;
}
if (ExcelApp.activeSheet().cells().item(lineNum, 1).value().variantType() == ComVariantType::VT_EMPTY &&
ExcelApp.activeSheet().cells().item(lineNum, 2).value().variantType() == ComVariantType::VT_EMPTY )
{
rowfound = false;
continue;
}
ttsbegin;
total += amountmst;
t.clear();
t.initValue();
t.LineNum = k;
t.journalnum = jourId; // '353830_010'; ---- need journalid will auto populate?
// transdate = str2date(conpeek(con,2),213);
transdate = ExcelApp.activeSheet().cells().item(lineNum, 2).value().date();
// t.TransDate = conpeek(con,2);
t.TransDate = transdate;
// t.Voucher = strfmt('GENROY%1%2.%3', substr(int2str(iyear), 3, 2), substr(int2str(iMonth + 100), 2, 2), substr(strfmt('%1', 100000 + k),2, 5)); // year, month
// first- create thevendor line
// t.AccountType = LedgerJournalACType::Ledger;
t.AccountType = LedgerJournalAcType::Vend;
// t.AccountNum = glAccount; //'471001';
t.AccountNum = glVendAccount;
t.OffsetAccountType = LedgerJournalACType::Ledger;
// t.OffsetAccount = glOffsetAccount; //'211060';
// t.Txt = conpeek(con, 2);
t.Txt = 'Unyson upload';
// amountcur = str2num(conpeek(con, 23));
amountcur = ExcelApp.activeSheet().cells().item(lineNum, 24).value().double();
amountcur = decRound(amountcur, 2);
amount = amountcur;
amountmst = amountmst+amount;
/*( if(amount >0)
{
t.AmountCurDebit = amount;
t.AmountCurCredit = 0;
}
else
{
t.AmountCurDebit = 0;
t.AmountCurCredit = -amount;
} */
t.AmountCurCredit = amount; // vendor account is always credit
t.currencyCode = 'USD';
// t.Invoice = conpeek(con,1);
t.Invoice = ExcelApp.activeSheet().cells().item(lineNum, 1).value().bstr();
t.Dimension[1]= curext();
// t.Dimension[4] = conpeek(con, 7); // customer
// t.Dimension[4] = CustTable::find(ExcelApp.activeSheet().cells().item(lineNum, 8).value().bstr()).Dimension[4]; //XREN 1/22/2013
// t.Dimension[5] = conpeek(con, 3); // no default so far
t.DocumentDate = ExcelApp.activeSheet().cells().item(lineNum, 9).value().date();
t.editReasonComment(true,ExcelApp.activeSheet().cells().item(lineNum, 10).value().bstr());
//Begin XREN 1/10/2013
costCenter = ExcelApp.activeSheet().cells().item(lineNum, 26).value().double();
salesId = ExcelApp.activeSheet().cells().item(lineNum, 6).value().bstr();
t.DocumentNum = salesId;
salesTable = SalesTable::find(salesId);
custTable = CustTable::find(ExcelApp.activeSheet().cells().item(lineNum, 8).value().bstr());
t.Dimension[2] = num2str(costCenter, 3, 0, 1, 0);
if (!t.Dimension[2])
{
throw error(strfmt("Line %1 does not have valid cost center %2", lineNum, t.Dimension[2]));
}
t.Dimension[3] = custTable.Dimension[3];
if (!t.Dimension[3])
{
t.Dimension[3] = salesTable.Dimension[3];
}
if (!t.Dimension[3])
{
warning(strfmt("Line %1 does not have Market segment", lineNum));
}
t.Dimension[4] = salesTable.Dimension[4];
if (!t.Dimension[4])
{
warning(strfmt("Line %1 does not have customer dimension asscoiated with sales order %2", lineNum, salesId));
}
t.Dimension[5] = salesTable.Dimension[5];
if (!t.Dimension[5])
{
select firstonly salesLine
where salesLine.SalesId == salesId
&& salesLine.Dimension[5] != "";
t.Dimension[5] = salesLine.Dimension[5];
}
if (!t.Dimension[5])
{
warning(strfmt("Line %1 does not have Product line associated with sales order %2", lineNum, salesId));
}
costCenterStr = t.Dimension[2];
marketSegment = t.Dimension[3];
customerDim = t.Dimension[4];
productLine = t.Dimension[5];
t.Approved = NoYes::Yes;
t.ApprovedBy = curUserId();
vendCount++;
vendAmount += t.AmountCurCredit;
//End
t.validateWrite();
// t.setSettleVoucher();
// _ledgerJournalTrans.Voucher = new JournalVoucherNum(JournalTableData::newTable(ledgerJournalTable)).getNew(false);
t.Voucher = new JournalVoucherNum(JournalTableData::newTable(j)).getNew(false);
curVoucher = t.Voucher;
t.insert();
//Start Janardhan
select vendTable where vendTable.dataAreaId == t.Company
&& LedgerJournalACType::Vend == t.AccountType
&& vendtable.AccountNum == t.AccountNum;
t.Payment = vendTable.PaymTermId;
paymTerm = PaymTerm::find(t.Payment);
t.Due = paymTerm.NumOfDays + t.TransDate;
t.PaymMode=vendTable.PaymMode;
t.Update();
//info(strfmt("Due date,Payment ,%1,%2",ledgerJournalTrans.Due, ledgerJournalTrans.Payment));
//End Janardhan
// now create the Freight entry
t.clear();
t.initValue();
k++;
t.LineNum = k;
t.journalnum = jourId;
t.AccountType = LedgerJournalACType::Ledger;
t.TransDate = transdate;
t.AccountNum = glFreightAccount;
t.OffsetAccountType = LedgerJournalACType::Ledger;
t.Txt = 'Unyson upload';
// amountcur = str2num(conpeek(con, 20));
amountcur = ExcelApp.activeSheet().cells().item(lineNum, 21).value().double();
amountcur = decRound(amountcur, 2);
amount = amountcur;
t.AmountCurDebit = amount;
t.currencyCode = 'USD';
t.Dimension[1]= curext();
// t.Dimension[4] = conpeek(con, 7); // customer
//XREN 1/23/2013 t.Dimension[4] = ExcelApp.activeSheet().cells().item(lineNum, 8).value().bstr();
t.Voucher = curVoucher;
t.DocumentDate = ExcelApp.activeSheet().cells().item(lineNum, 9).value().date();
t.editReasonComment(true,ExcelApp.activeSheet().cells().item(lineNum, 10).value().bstr());
//Begin XREN 1/10/2013
t.Dimension[2] = costCenterStr;
t.Dimension[3] = marketSegment;
t.Dimension[4] = customerDim;
t.Dimension[5] = productLine;
t.Approved = NoYes::Yes;
t.ApprovedBy = curUserId();
//End
t.validateWrite();
t.insert();
// now create the Fuel entry
t.clear();
t.initValue();
k++;
t.LineNum = k;
t.journalnum = jourId; // ---- need journalid will auto populate?
t.AccountType = LedgerJournalACType::Ledger;
t.TransDate = transdate;
t.AccountNum = glFuelAccount;
t.OffsetAccountType = LedgerJournalACType::Ledger;
t.Txt = 'Unyson upload';
// amountcur = str2num(conpeek(con, 21));
amountcur = ExcelApp.activeSheet().cells().item(lineNum, 22).value().double();
amountcur = decRound(amountcur, 2);
amount = amountcur;
t.AmountCurDebit = amount;
t.currencyCode = 'USD';
t.Dimension[1]= curext();
// t.Dimension[4] = conpeek(con, 7); // customer
//XREN 1/22/2013 t.Dimension[4] = ExcelApp.activeSheet().cells().item(lineNum, 8).value().bstr();
t.Voucher = curVoucher;
t.DocumentDate = ExcelApp.activeSheet().cells().item(lineNum, 9).value().date();
t.editReasonComment(true,ExcelApp.activeSheet().cells().item(lineNum, 10).value().bstr());
//Begin XREN 1/10/2013
t.Dimension[2] = costCenterStr;
t.Dimension[3] = marketSegment;
t.Dimension[4] = customerDim;
t.Dimension[5] = productLine;
t.Approved = NoYes::Yes;
t.ApprovedBy = curUserId();
//End
t.validateWrite();
t.insert();
lineNum++;
ttscommit;
}
}
catch
{
excelapp.workbooks().close();
excelapp.quit();
throw error(strfmt('Error occured at rcd %1 for item: %2', linenum, t.Invoice)); //Xren 1/16/2013
}
excelapp.displayAlerts(true);
excelapp.workbooks().close();
excelapp.quit();
ttscommit; //Xren 1/16/2013
info(strfmt("loaded %1 lines, total $%2 USD",readcounter, total));
info(strfmt("Vendor record: %1, Vendor amount: %2, Journal %3", vendCount, vendAmount, jourId));
}
//Method 3 : createLedgerJournalTable
Static LedgerJournalTable createLedgerJournalTable(LedgerJournalNameID _ledgerJournalNameID)
{
LedgerJournalTable journalTable;
;
journalTable.journalType = LedgerJournalType::VendInvoicePoolNotPosted ;
// journalTable.JournalName = PSAParameters::find().ExpenseJournalNameId;//Customized field
journalTable.JournalName = _ledgerJournalNameID;
journalTable.initFromLedgerJournalName(journalTable.JournalName);
// journalTable.JournalNum = JournalTableData::newTable(journalTable).nextJournalId();
journalTable.insert();
return journalTable;
}
//
Method 4: Main
static void Main(Args _args)
{
kr_ImportAPInvJournal krImportAPInvJournal;
krImportAPInvJournal = new kr_ImportAPInvJournal();
krImportAPInvJournal.run();
}
We create One Class::
Method1: ClassDeclaration:
public class kr_ImportAPInvJournal extends ObjectRun
{
}
Method2: Run
void run()
{
// 12-13-2012 change to use excel - Richard s
Amount amount;
LedgerJournalTable j;
LedgerJournalTrans t,ledgerJournalTrans;
LedgerPeriod p;
LedgerJournalTable ledgerjournaltable;
int k,i;
Dialog dlg;
DialogField dfFileName, dfJournalName, dfDate, dfAccountNum, dfOffsetAccount;
DialogField dfVendAccount, dfFreightAccount, dfFuelAccount;
DialogField dfProdLine;
FileNameOPen fileName;
LedgerJournalId jourId;
transDate tDate;
Voucher curVoucher;
str 60 customer;
// CustTable custTable;
// CustTrans custTrans;
// CustTransOpen custTransOpen;
InvoiceId invoiceId;
// CustAccount invoiceAccount;
CurrencyCode currencyCode;
AccountNum glAccount, glOffsetAccount, glFuelAccount='556001',glFreightAccount='556000', glVendAccount='8093064';
date docdate, duedate, transdate;
num docnum;
Amountmst amountmst, total;
AmountCur amountcur;
ExchRate exchrate;
LedgerJournalNameID journalName;
container con;
// ASCIIIO file;
// TextIO file;
str period;
str 100 txt;
int upd;
int iYear, iMonth;
SysExcelApplication ExcelApp;
boolean rowFound = true;
DialogField dialogFieldStartLineNum;
DialogField dialogFieldEndLineNum;
int lineNum = 2;
int lineNumEnd = 9;
str testStr;
counter readcounter = 0;
counter insertCounter = 0;
counter updateCounter = 0;
counter errCounter = 0;
Boolean errorEncounter = false;
Boolean ret = true;
//Begin XREN 1/10/2013
SalesLine salesLine;
SalesTable salesTable;
CustTable custTable;
real costCenter;
str costCenterStr;
str marketSegment;
str customerDim;
str productLine;
SalesId salesId;
Counter vendCount;
Amount vendAmount;
//StartJanardhan
PaymTermId paymTermId, Payment;
LedgerJournalEngine ledgerJournalEngine;
DueDate due;
VendTable vendTable;
PaymTerm paymTerm;
PaymMode paymMode;
//EndJanardhan
;
dlg = new Dialog('Upload AP Invoice Journal from Excel file');
dfFileName = dlg.addFieldValue(typeid(FileNameOpen),filename);
dfJournalName = dlg.addFieldValue(typeid(LedgerJournalNameId),journalName,"Jrnl (APIMPT)","Name of Journal");
dfVendAccount = dlg.addFieldValue(typeid(VendAccount), glVendAccount, "Vendor Account", "Vendore Account Number");
dfFreightAccount = dlg.addFieldValue(typeid(ledgerAccount), glFreightAccount, "Freight Account", "Freight Account Number");
dfFuelAccount = dlg.addFieldValue(typeid(LedgerAccount), glFuelAccount,"Fuel Account", "Fuel Account");
dialogFieldStartLineNum = dlg.addField(typeid(Integer),"Starting Liine Number","start loading records at line number specified");
dialogFieldStartLineNum.value(lineNum);
dialogFieldEndLineNum = dlg.addField(typeid(Integer),"Ending Liine Number","stop loading records at line number specified");
dialogFieldEndLineNum.value(lineNumEnd);
// dfDate = dlg.addFieldValue(typeid(transdate),tDate);
if (!dlg.run())
throw error('Cancelled by the user');
filename = dfFileName.value();
journalName = dfJournalName.value(); // 'APIMPT'; // -- or APINV
// jourId = dfJournalNumber.value(); // ---------- need to create journal
// use file for doc/invoice date
glVendAccount = dfVendAccount.value();
glfreightAccount = dfFreightAccount.value();
glFuelAccount = dfFuelAccount.value();
ttsbegin; //Xren 1/16/2013
// ------------------------ j = new LedgerJournalTable();
j = kr_ImportAPInvJournal::createLedgerJournalTable(journalName);
jourId = j.JournalNum;
// tDate = dfDate.value(); // ? for posting date?
// glAccount = dfAccountNum.value();
// glOffsetAccount = dfOffsetAccount.value();
/* file = new TextIO(filename,'rt'); -----change to use excel
// file.inFieldDelimiter('\t');
file.inFieldDelimiter(',');
file.inRecordDelimiter('\n');
info(strfmt('File %1',filename));
*/
if (dialogFieldStartLineNum.value() > 0)
{
lineNum = dialogFieldStartLineNum.value();
readCounter = lineNum - 1;
}
if (dialogFieldEndLineNum.value() > 0)
{
lineNumEnd = dialogFieldEndLineNum.value();
}
ExcelApp = SysExcelApplication::construct();
ExcelApp.workbooks().open(filename);
k = -1;
select PeriodStart, PeriodEnd, PeriodMonth, PeriodYear from p
where p.PeriodStart <= tDate
&& p.PeriodEnd >= tDate
&& p.PeriodMonth > 0
&& p.PeriodYear > 0;
iMonth = p.PeriodMonth;
iYear = p.PeriodYear;
try
{
while (rowFound && ExcelApp.activeSheet().cells().range(strfmt('A%1',lineNum)).item(lineNum))
{
// while (file.status() == IO_Status::Ok)
//begin looping through input file
// con = file.read();
// if (conlen(con) == 0)
// continue;
k++;
// if (k == 0)
// continue;
/* if (conlen(con) != 23)
{
warning(strfmt("line %1 skipped, found %2 columns", k, conlen(con)));
continue;
}
*/
// print k;
readcounter++;
if (lineNum > lineNumEnd)
{
rowfound = false;
continue;
}
if (ExcelApp.activeSheet().cells().item(lineNum, 1).value().variantType() == ComVariantType::VT_EMPTY &&
ExcelApp.activeSheet().cells().item(lineNum, 2).value().variantType() == ComVariantType::VT_EMPTY )
{
rowfound = false;
continue;
}
ttsbegin;
total += amountmst;
t.clear();
t.initValue();
t.LineNum = k;
t.journalnum = jourId; // '353830_010'; ---- need journalid will auto populate?
// transdate = str2date(conpeek(con,2),213);
transdate = ExcelApp.activeSheet().cells().item(lineNum, 2).value().date();
// t.TransDate = conpeek(con,2);
t.TransDate = transdate;
// t.Voucher = strfmt('GENROY%1%2.%3', substr(int2str(iyear), 3, 2), substr(int2str(iMonth + 100), 2, 2), substr(strfmt('%1', 100000 + k),2, 5)); // year, month
// first- create thevendor line
// t.AccountType = LedgerJournalACType::Ledger;
t.AccountType = LedgerJournalAcType::Vend;
// t.AccountNum = glAccount; //'471001';
t.AccountNum = glVendAccount;
t.OffsetAccountType = LedgerJournalACType::Ledger;
// t.OffsetAccount = glOffsetAccount; //'211060';
// t.Txt = conpeek(con, 2);
t.Txt = 'Unyson upload';
// amountcur = str2num(conpeek(con, 23));
amountcur = ExcelApp.activeSheet().cells().item(lineNum, 24).value().double();
amountcur = decRound(amountcur, 2);
amount = amountcur;
amountmst = amountmst+amount;
/*( if(amount >0)
{
t.AmountCurDebit = amount;
t.AmountCurCredit = 0;
}
else
{
t.AmountCurDebit = 0;
t.AmountCurCredit = -amount;
} */
t.AmountCurCredit = amount; // vendor account is always credit
t.currencyCode = 'USD';
// t.Invoice = conpeek(con,1);
t.Invoice = ExcelApp.activeSheet().cells().item(lineNum, 1).value().bstr();
t.Dimension[1]= curext();
// t.Dimension[4] = conpeek(con, 7); // customer
// t.Dimension[4] = CustTable::find(ExcelApp.activeSheet().cells().item(lineNum, 8).value().bstr()).Dimension[4]; //XREN 1/22/2013
// t.Dimension[5] = conpeek(con, 3); // no default so far
t.DocumentDate = ExcelApp.activeSheet().cells().item(lineNum, 9).value().date();
t.editReasonComment(true,ExcelApp.activeSheet().cells().item(lineNum, 10).value().bstr());
//Begin XREN 1/10/2013
costCenter = ExcelApp.activeSheet().cells().item(lineNum, 26).value().double();
salesId = ExcelApp.activeSheet().cells().item(lineNum, 6).value().bstr();
t.DocumentNum = salesId;
salesTable = SalesTable::find(salesId);
custTable = CustTable::find(ExcelApp.activeSheet().cells().item(lineNum, 8).value().bstr());
t.Dimension[2] = num2str(costCenter, 3, 0, 1, 0);
if (!t.Dimension[2])
{
throw error(strfmt("Line %1 does not have valid cost center %2", lineNum, t.Dimension[2]));
}
t.Dimension[3] = custTable.Dimension[3];
if (!t.Dimension[3])
{
t.Dimension[3] = salesTable.Dimension[3];
}
if (!t.Dimension[3])
{
warning(strfmt("Line %1 does not have Market segment", lineNum));
}
t.Dimension[4] = salesTable.Dimension[4];
if (!t.Dimension[4])
{
warning(strfmt("Line %1 does not have customer dimension asscoiated with sales order %2", lineNum, salesId));
}
t.Dimension[5] = salesTable.Dimension[5];
if (!t.Dimension[5])
{
select firstonly salesLine
where salesLine.SalesId == salesId
&& salesLine.Dimension[5] != "";
t.Dimension[5] = salesLine.Dimension[5];
}
if (!t.Dimension[5])
{
warning(strfmt("Line %1 does not have Product line associated with sales order %2", lineNum, salesId));
}
costCenterStr = t.Dimension[2];
marketSegment = t.Dimension[3];
customerDim = t.Dimension[4];
productLine = t.Dimension[5];
t.Approved = NoYes::Yes;
t.ApprovedBy = curUserId();
vendCount++;
vendAmount += t.AmountCurCredit;
//End
t.validateWrite();
// t.setSettleVoucher();
// _ledgerJournalTrans.Voucher = new JournalVoucherNum(JournalTableData::newTable(ledgerJournalTable)).getNew(false);
t.Voucher = new JournalVoucherNum(JournalTableData::newTable(j)).getNew(false);
curVoucher = t.Voucher;
t.insert();
//Start Janardhan
select vendTable where vendTable.dataAreaId == t.Company
&& LedgerJournalACType::Vend == t.AccountType
&& vendtable.AccountNum == t.AccountNum;
t.Payment = vendTable.PaymTermId;
paymTerm = PaymTerm::find(t.Payment);
t.Due = paymTerm.NumOfDays + t.TransDate;
t.PaymMode=vendTable.PaymMode;
t.Update();
//info(strfmt("Due date,Payment ,%1,%2",ledgerJournalTrans.Due, ledgerJournalTrans.Payment));
//End Janardhan
// now create the Freight entry
t.clear();
t.initValue();
k++;
t.LineNum = k;
t.journalnum = jourId;
t.AccountType = LedgerJournalACType::Ledger;
t.TransDate = transdate;
t.AccountNum = glFreightAccount;
t.OffsetAccountType = LedgerJournalACType::Ledger;
t.Txt = 'Unyson upload';
// amountcur = str2num(conpeek(con, 20));
amountcur = ExcelApp.activeSheet().cells().item(lineNum, 21).value().double();
amountcur = decRound(amountcur, 2);
amount = amountcur;
t.AmountCurDebit = amount;
t.currencyCode = 'USD';
t.Dimension[1]= curext();
// t.Dimension[4] = conpeek(con, 7); // customer
//XREN 1/23/2013 t.Dimension[4] = ExcelApp.activeSheet().cells().item(lineNum, 8).value().bstr();
t.Voucher = curVoucher;
t.DocumentDate = ExcelApp.activeSheet().cells().item(lineNum, 9).value().date();
t.editReasonComment(true,ExcelApp.activeSheet().cells().item(lineNum, 10).value().bstr());
//Begin XREN 1/10/2013
t.Dimension[2] = costCenterStr;
t.Dimension[3] = marketSegment;
t.Dimension[4] = customerDim;
t.Dimension[5] = productLine;
t.Approved = NoYes::Yes;
t.ApprovedBy = curUserId();
//End
t.validateWrite();
t.insert();
// now create the Fuel entry
t.clear();
t.initValue();
k++;
t.LineNum = k;
t.journalnum = jourId; // ---- need journalid will auto populate?
t.AccountType = LedgerJournalACType::Ledger;
t.TransDate = transdate;
t.AccountNum = glFuelAccount;
t.OffsetAccountType = LedgerJournalACType::Ledger;
t.Txt = 'Unyson upload';
// amountcur = str2num(conpeek(con, 21));
amountcur = ExcelApp.activeSheet().cells().item(lineNum, 22).value().double();
amountcur = decRound(amountcur, 2);
amount = amountcur;
t.AmountCurDebit = amount;
t.currencyCode = 'USD';
t.Dimension[1]= curext();
// t.Dimension[4] = conpeek(con, 7); // customer
//XREN 1/22/2013 t.Dimension[4] = ExcelApp.activeSheet().cells().item(lineNum, 8).value().bstr();
t.Voucher = curVoucher;
t.DocumentDate = ExcelApp.activeSheet().cells().item(lineNum, 9).value().date();
t.editReasonComment(true,ExcelApp.activeSheet().cells().item(lineNum, 10).value().bstr());
//Begin XREN 1/10/2013
t.Dimension[2] = costCenterStr;
t.Dimension[3] = marketSegment;
t.Dimension[4] = customerDim;
t.Dimension[5] = productLine;
t.Approved = NoYes::Yes;
t.ApprovedBy = curUserId();
//End
t.validateWrite();
t.insert();
lineNum++;
ttscommit;
}
}
catch
{
excelapp.workbooks().close();
excelapp.quit();
throw error(strfmt('Error occured at rcd %1 for item: %2', linenum, t.Invoice)); //Xren 1/16/2013
}
excelapp.displayAlerts(true);
excelapp.workbooks().close();
excelapp.quit();
ttscommit; //Xren 1/16/2013
info(strfmt("loaded %1 lines, total $%2 USD",readcounter, total));
info(strfmt("Vendor record: %1, Vendor amount: %2, Journal %3", vendCount, vendAmount, jourId));
}
//Method 3 : createLedgerJournalTable
Static LedgerJournalTable createLedgerJournalTable(LedgerJournalNameID _ledgerJournalNameID)
{
LedgerJournalTable journalTable;
;
journalTable.journalType = LedgerJournalType::VendInvoicePoolNotPosted ;
// journalTable.JournalName = PSAParameters::find().ExpenseJournalNameId;//Customized field
journalTable.JournalName = _ledgerJournalNameID;
journalTable.initFromLedgerJournalName(journalTable.JournalName);
// journalTable.JournalNum = JournalTableData::newTable(journalTable).nextJournalId();
journalTable.insert();
return journalTable;
}
//
Method 4: Main
static void Main(Args _args)
{
kr_ImportAPInvJournal krImportAPInvJournal;
krImportAPInvJournal = new kr_ImportAPInvJournal();
krImportAPInvJournal.run();
}
