static void Job34(Args _args)
{
InventJournalTrans InventJournalTrans;
while select forUpdate InventJournalTrans
where InventJournalTrans.JournalId == 'JOUR-00011'
{
//info("hi");
InventJournalTrans.TransDate = str2Date('30/11/2017',123);
InventJournalTrans.update();
}
info("done");
}
Wednesday, July 24, 2019
Journal number with dataarea id
static void Job29(Args _args)
{
VendTrans VendTrans;
VendSettlement VendSettlement;
ledgerJournalTrans ledgerJournalTrans;
while select VendTrans
join VendSettlement
join ledgerJournalTrans
where
vendtrans.voucher == ledgerJournalTrans.Voucher
&& VendSettlement.TransCompany == VendTrans.dataAreaId
&& VendSettlement.TransRecId == VendTrans.RecId
&& VendSettlement.AccountNum == VendTrans.AccountNum
{
info(ledgerJournalTrans.JournalNum);
info(VendTrans.dataAreaId);
}
}
{
VendTrans VendTrans;
VendSettlement VendSettlement;
ledgerJournalTrans ledgerJournalTrans;
while select VendTrans
join VendSettlement
join ledgerJournalTrans
where
vendtrans.voucher == ledgerJournalTrans.Voucher
&& VendSettlement.TransCompany == VendTrans.dataAreaId
&& VendSettlement.TransRecId == VendTrans.RecId
&& VendSettlement.AccountNum == VendTrans.AccountNum
{
info(ledgerJournalTrans.JournalNum);
info(VendTrans.dataAreaId);
}
}
Monthly Attendance Infolog
static void MonthlyAttendance(Args _args)
{
GOD_EmplMonthlyAttendanceTmp emplMonthlyAttendanceTmp;
select * from emplMonthlyAttendanceTmp
where emplMonthlyAttendanceTmp.EmplId == "120009"
//emplTable.PersonnelNumber
&& emplMonthlyAttendanceTmp.Type == "@SYS5921";
{
info(strFmt("%1",emplMonthlyAttendanceTmp.Day19));
}
}
{
GOD_EmplMonthlyAttendanceTmp emplMonthlyAttendanceTmp;
select * from emplMonthlyAttendanceTmp
where emplMonthlyAttendanceTmp.EmplId == "120009"
//emplTable.PersonnelNumber
&& emplMonthlyAttendanceTmp.Type == "@SYS5921";
{
info(strFmt("%1",emplMonthlyAttendanceTmp.Day19));
}
}
Tuesday, July 23, 2019
Vendor transaction amount
static void VendorAmount(Args _args)
{
vendTrans vendTrans;
vendTransOpen vendTransOpen;
select vendtrans
where vendTrans.AccountNum == 'V0191'
join sum(AmountCur)
from vendTransOpen
where vendTransOpen.RefRecId == vendTrans.RecId;
info(strFmt('%1',vendTransOpen.AmountCur));
/*
select CurrencyCode
from vendTrans
group by CurrencyCode
where vendTrans.AccountNum == _accountNum
join sum(AmountCur), sum(AmountMST)
from vendTransOpen
where vendTransOpen.RefRecId == vendTrans.RecId
*/
}
{
vendTrans vendTrans;
vendTransOpen vendTransOpen;
select vendtrans
where vendTrans.AccountNum == 'V0191'
join sum(AmountCur)
from vendTransOpen
where vendTransOpen.RefRecId == vendTrans.RecId;
info(strFmt('%1',vendTransOpen.AmountCur));
/*
select CurrencyCode
from vendTrans
group by CurrencyCode
where vendTrans.AccountNum == _accountNum
join sum(AmountCur), sum(AmountMST)
from vendTransOpen
where vendTransOpen.RefRecId == vendTrans.RecId
*/
}
Customer Credit Limit
static void CustCreditLimit(Args _args)
{
custCreditLimit custCreditLimit;
custCreditLimit = CustCreditLimit::construct(custtable::find('C-01977'));
//custCreditLimit.calcCreditAvailable()/custtable.CreditMax * 100) <= CustParameters::find().GOD_CreditLimitThresold )
info(strFmt('%1',custCreditLimit.calcCreditAvailable()));
info(strFmt('%1',custCreditLimit.getBalance()));
}
{
custCreditLimit custCreditLimit;
custCreditLimit = CustCreditLimit::construct(custtable::find('C-01977'));
//custCreditLimit.calcCreditAvailable()/custtable.CreditMax * 100) <= CustParameters::find().GOD_CreditLimitThresold )
info(strFmt('%1',custCreditLimit.calcCreditAvailable()));
info(strFmt('%1',custCreditLimit.getBalance()));
}
Print Sales Invoice
static void PrintSalesInvoice(Args _args)
{
Args salesArgs = new Args();
SalesInvoiceContract salesInvoiceContract;
SalesInvoiceController controller;
SrsReportRunImpl srsReportRunImpl;
str fileName;
CustInvoiceJour custInvoiceJour = _args.record();
// select custInvoiceJour where custInvoiceJour.InvoiceId=="GH-INV-000107";
salesArgs.record(custInvoiceJour);
controller = new SrsReportRunController();
salesInvoiceContract = new SalesInvoiceContract();
controller.parmReportName(ssrsReportStr(SalesInvoice,ReportGH));
controller.parmShowDialog(true);
controller.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::Printer);
// controller.parmReportContract().parmPrintSettings().printerName(@"\\espprn03\Follow Me - MFP");
salesInvoiceContract.parmRecordId(custInvoiceJour.RecId); // Record id must be passed otherwise the report will be empty
salesInvoiceContract.parmCountryRegionISOCode(SysCountryRegionCode::countryInfo()); // comment this code if tested in pre release
controller.parmReportContract().parmRdpContract(salesInvoiceContract);
controller.startOperation();
}
{
Args salesArgs = new Args();
SalesInvoiceContract salesInvoiceContract;
SalesInvoiceController controller;
SrsReportRunImpl srsReportRunImpl;
str fileName;
CustInvoiceJour custInvoiceJour = _args.record();
// select custInvoiceJour where custInvoiceJour.InvoiceId=="GH-INV-000107";
salesArgs.record(custInvoiceJour);
controller = new SrsReportRunController();
salesInvoiceContract = new SalesInvoiceContract();
controller.parmReportName(ssrsReportStr(SalesInvoice,ReportGH));
controller.parmShowDialog(true);
controller.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::Printer);
// controller.parmReportContract().parmPrintSettings().printerName(@"\\espprn03\Follow Me - MFP");
salesInvoiceContract.parmRecordId(custInvoiceJour.RecId); // Record id must be passed otherwise the report will be empty
salesInvoiceContract.parmCountryRegionISOCode(SysCountryRegionCode::countryInfo()); // comment this code if tested in pre release
controller.parmReportContract().parmRdpContract(salesInvoiceContract);
controller.startOperation();
}
Update Item Family
static void GOD_UpdateItemFamily(Args _args)
{
#AviFiles
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog= new Dialog("Excel Upoad");
Container excelCont[];
str valuestr;
int rowIdx;
Counter linesImported;
int lastRow,dimcount;
boolean ok = true;
NoYes noYes;
str 50 c1,c2,c12;
real c3,c4,c5;
str input;
container accEntryPattern;
InventItemBarcode inventItemBarcodeCreate;
NumberSeq numberSeq;
SysExcelApplication application;
SysExcelWorkBooks workBooks;
SysExcelWorkSheets workSheets;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
SysOperationProgress progress;
InventTable _InventTable,_InventTableupdate;
//GOD_ItemFamilyMaster _GOD_ItemFamilyMaster;
struct struct= new Struct();
DocuRef docuRef;
DocuValue docuValue;
System.String[] fileNames;
int fileCount, i;
str ImagefileName, trimmedFileName, fileNameWithExt;
BinData binData = new BinData();
str extention, path, nameOfFile;
container imageContainer;
str imageFilePathName,FileImageName;
#define.CurrentVersion(1)
#localmacro.CurrentList
filename
#endmacro
#Excel
#define.Star('*')
#define.Space(' ')
// convert into str from excel cell value
str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
//case COMVariantType::VT_R4 :
//case COMVariantType::VT_R8 :
//
//if(_cv.double())
//{
//valueStr = strFmt("@SYS311964",
//num2Str0(_cv.double(), 0),
//num2str(_cv.double(),
//0,
//numOfDec(_cv.double()),
//1,
//0));
//}
//return valuestr;
//break;
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
// Find last row from excel
int findLastRow(SysExcelWorkSheet _workSheet)
{
SysExcelRange range;
;
range = _workSheet.cells().range(#ExcelTotalRange);
try
{
// Finds the row where the first contents is found.
range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
}
catch (Exception::Error)
{
error("@SYS59926");
return 0;
}
if (range)
{
return range.row();
}
else
{
return 0;
}
}
;
dialogFilename = dialog.addField(extendedtypestr(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576", #XLSX, "@SYS28576", #XLS]);
dialog.filenameLookupTitle("Upload from Excel");
dialogFilename.value(filename);
if(!dialog.run())
return;
filename = dialogFilename.value();
//ttsbegin;
application = SysExcelApplication::construct();
workBooks = application.workbooks();
workBooks.open(filename,0,true);
workSheets = workBooks.item(1).worksheets();
input = "Sheet1";
workSheet = workSheets.itemFromNum(1);
cells = workSheet.cells();
lastRow = findLastRow(workSheet);
rowIdx = 2;
progress = new SysOperationProgress();
progress.setCaption("Excel Importing");
progress.setTotal(lastRow);
progress.setAnimation(#AviTransfer);
setprefix("Excel Import");
try
{
while (rowIdx <= lastRow)
{
setPrefix(strfmt("Excel Row: %1", rowIdx));
c1 = COMVariant2Str(cells.item(rowIdx,1).value());
c2 = COMVariant2Str(cells.item(rowIdx,2).value());
c3 = cells.item(rowIdx,3).value().double();
c4 = cells.item(rowIdx,4).value().double();
c5 = cells.item(rowIdx,5).value().double();
linesImported++;
progress.setText("Importing " + c12);
progress.setCount(linesImported);
rowIdx++;
dimcount =0;
select firstOnly _InventTable where _InventTable.ItemId == c1;
if (_InventTable)
{
ttsBegin;
select forUpdate _InventTableupdate
where _InventTableupdate.ItemId == _InventTable.ItemId;
//if (c2)
// select firstonly _GOD_ItemFamilyMaster where _GOD_ItemFamilyMaster.Code == c2;
//if(_GOD_ItemFamilyMaster)
//_InventTableupdate.GOD_FamilyCode = c2;
if (c3)
_InventTableupdate.grossDepth =c3;
if(c4)
_InventTableupdate.grossWidth = c4;
if(c5)
_InventTableupdate.grossHeight = c5;
_InventTableupdate.update();
ttsCommit;
}
}
}
catch
{
info(strFmt("Error at line %1",rowIdx-1));
}
application.quit();
application = null;
//ttscommit;
info(strFmt("done %1",rowIdx-2));
}
{
#AviFiles
FilenameOpen filename;
dialogField dialogFilename;
Dialog dialog= new Dialog("Excel Upoad");
Container excelCont[];
str valuestr;
int rowIdx;
Counter linesImported;
int lastRow,dimcount;
boolean ok = true;
NoYes noYes;
str 50 c1,c2,c12;
real c3,c4,c5;
str input;
container accEntryPattern;
InventItemBarcode inventItemBarcodeCreate;
NumberSeq numberSeq;
SysExcelApplication application;
SysExcelWorkBooks workBooks;
SysExcelWorkSheets workSheets;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
SysOperationProgress progress;
InventTable _InventTable,_InventTableupdate;
//GOD_ItemFamilyMaster _GOD_ItemFamilyMaster;
struct struct= new Struct();
DocuRef docuRef;
DocuValue docuValue;
System.String[] fileNames;
int fileCount, i;
str ImagefileName, trimmedFileName, fileNameWithExt;
BinData binData = new BinData();
str extention, path, nameOfFile;
container imageContainer;
str imageFilePathName,FileImageName;
#define.CurrentVersion(1)
#localmacro.CurrentList
filename
#endmacro
#Excel
#define.Star('*')
#define.Space(' ')
// convert into str from excel cell value
str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
//case COMVariantType::VT_R4 :
//case COMVariantType::VT_R8 :
//
//if(_cv.double())
//{
//valueStr = strFmt("@SYS311964",
//num2Str0(_cv.double(), 0),
//num2str(_cv.double(),
//0,
//numOfDec(_cv.double()),
//1,
//0));
//}
//return valuestr;
//break;
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
// Find last row from excel
int findLastRow(SysExcelWorkSheet _workSheet)
{
SysExcelRange range;
;
range = _workSheet.cells().range(#ExcelTotalRange);
try
{
// Finds the row where the first contents is found.
range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
}
catch (Exception::Error)
{
error("@SYS59926");
return 0;
}
if (range)
{
return range.row();
}
else
{
return 0;
}
}
;
dialogFilename = dialog.addField(extendedtypestr(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576", #XLSX, "@SYS28576", #XLS]);
dialog.filenameLookupTitle("Upload from Excel");
dialogFilename.value(filename);
if(!dialog.run())
return;
filename = dialogFilename.value();
//ttsbegin;
application = SysExcelApplication::construct();
workBooks = application.workbooks();
workBooks.open(filename,0,true);
workSheets = workBooks.item(1).worksheets();
input = "Sheet1";
workSheet = workSheets.itemFromNum(1);
cells = workSheet.cells();
lastRow = findLastRow(workSheet);
rowIdx = 2;
progress = new SysOperationProgress();
progress.setCaption("Excel Importing");
progress.setTotal(lastRow);
progress.setAnimation(#AviTransfer);
setprefix("Excel Import");
try
{
while (rowIdx <= lastRow)
{
setPrefix(strfmt("Excel Row: %1", rowIdx));
c1 = COMVariant2Str(cells.item(rowIdx,1).value());
c2 = COMVariant2Str(cells.item(rowIdx,2).value());
c3 = cells.item(rowIdx,3).value().double();
c4 = cells.item(rowIdx,4).value().double();
c5 = cells.item(rowIdx,5).value().double();
linesImported++;
progress.setText("Importing " + c12);
progress.setCount(linesImported);
rowIdx++;
dimcount =0;
select firstOnly _InventTable where _InventTable.ItemId == c1;
if (_InventTable)
{
ttsBegin;
select forUpdate _InventTableupdate
where _InventTableupdate.ItemId == _InventTable.ItemId;
//if (c2)
// select firstonly _GOD_ItemFamilyMaster where _GOD_ItemFamilyMaster.Code == c2;
//if(_GOD_ItemFamilyMaster)
//_InventTableupdate.GOD_FamilyCode = c2;
if (c3)
_InventTableupdate.grossDepth =c3;
if(c4)
_InventTableupdate.grossWidth = c4;
if(c5)
_InventTableupdate.grossHeight = c5;
_InventTableupdate.update();
ttsCommit;
}
}
}
catch
{
info(strFmt("Error at line %1",rowIdx-1));
}
application.quit();
application = null;
//ttscommit;
info(strFmt("done %1",rowIdx-2));
}
vacation balance
static void vacbalance(Args _args)
{
real vacBalance;
vacBalance = GOD_VacationCalculations::getVacationBalance('XN114',mkDate(1,1,2018),'AL30');
info(strFmt('%1',vacBalance));
}
{
real vacBalance;
vacBalance = GOD_VacationCalculations::getVacationBalance('XN114',mkDate(1,1,2018),'AL30');
info(strFmt('%1',vacBalance));
}
VacationBalanceUpload
static void VacationBalanceUpload(Args _args)
{
#AviFiles
FilenameOpen filename;
dialogField dialogFilename,DialogJournalType;
int jounaltype;
Dialog dialog= new Dialog("Excel Upoad");
Container excelCont[];
RecId offsetledger;
int rowIdx;
Counter linesImported;
int lastRow,dimcount;
boolean ok = true;
str c1,c2,c3,c5,c6,c7,c8,c9,c10;
real c4;
str input;
container accEntryPattern;
BudgetType budgetType;
SysExcelApplication application;
SysExcelWorkBooks workBooks;
SysExcelWorkSheets workSheets;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
SysOperationProgress progress;
struct struct= new Struct();
GOD_VacationBalances vacBal;
#define.CurrentVersion(1)
#localmacro.CurrentList
filename
#endmacro
#Excel
#define.Star('*')
#define.Space(' ')
// convert into str from excel cell value
str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
// Find last row from excel
int findLastRow(SysExcelWorkSheet _workSheet)
{
SysExcelRange range;
;
range = _workSheet.cells().range(#ExcelTotalRange);
try
{
// Finds the row where the first contents is found.
range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
}
catch (Exception::Error)
{
error("@SYS59926");
return 0;
}
if (range)
{
return range.row();
}
else
{
return 0;
}
}
;
dialogFilename = dialog.addField(extendedtypestr(FilenameOpen));
// DialogJournalType = dialog.addField(enumstr(God_JournalToUpload),"Journal Type");
dialog.filenameLookupFilter(["@SYS28576",#XLS,#Xlsx]);
dialog.filenameLookupTitle("Upload from Excel");
dialogFilename.value(filename);
if(!dialog.run())
return;
filename = dialogFilename.value();
// ttsbegin;
application = SysExcelApplication::construct();
workBooks = application.workbooks();
workBooks.open(filename,0,true);
workSheets = workBooks.item(1).worksheets();
// this.importExcel("Sheet1");
input = "Sheet1";
workSheet = workSheets.itemFromNum(1);//.itemFromName(input);
cells = workSheet.cells();
lastRow = findLastRow(workSheet);
rowIdx = 2;
progress = new SysOperationProgress();
progress.setCaption("Excel Importing");
progress.setTotal(lastRow);
progress.setAnimation(#AviTransfer);
setprefix("Excel Import");
select vacBal;
while (rowIdx <= lastRow)
{
setPrefix(strfmt("Excel Row: %1", rowIdx));
c1 = COMVariant2Str(cells.item(rowIdx,1).value());
c2 = COMVariant2Str(cells.item(rowIdx,2).value());
c3 = COMVariant2Str(cells.item(rowIdx,3).value());
c4 = cells.item(rowIdx,4).value().double();
c5 = COMVariant2Str(cells.item(rowIdx,5).value());
linesImported++;
vacBal.ContractNo = str2int(c1);
vacBal.EmplId = c2;
vacBal.UptoDate = str2Date(c3,123);
vacBal.VacationBalance = c4;
vacBal.VacationCode = c5;
vacBal.IsAnnual = NoYes::Yes;
vacBal.insert();
progress.setText("Importing " + c2);
progress.setCount(linesImported);
rowIdx++;
}
info("done");
}
{
#AviFiles
FilenameOpen filename;
dialogField dialogFilename,DialogJournalType;
int jounaltype;
Dialog dialog= new Dialog("Excel Upoad");
Container excelCont[];
RecId offsetledger;
int rowIdx;
Counter linesImported;
int lastRow,dimcount;
boolean ok = true;
str c1,c2,c3,c5,c6,c7,c8,c9,c10;
real c4;
str input;
container accEntryPattern;
BudgetType budgetType;
SysExcelApplication application;
SysExcelWorkBooks workBooks;
SysExcelWorkSheets workSheets;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
SysOperationProgress progress;
struct struct= new Struct();
GOD_VacationBalances vacBal;
#define.CurrentVersion(1)
#localmacro.CurrentList
filename
#endmacro
#Excel
#define.Star('*')
#define.Space(' ')
// convert into str from excel cell value
str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
switch (_cv.variantType())
{
case (COMVariantType::VT_BSTR):
return _cv.bStr();
case (COMVariantType::VT_R4):
return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_R8):
return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DECIMAL):
return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
case (COMVariantType::VT_DATE):
return date2str(_cv.date(),123,2,1,2,1,4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
// Find last row from excel
int findLastRow(SysExcelWorkSheet _workSheet)
{
SysExcelRange range;
;
range = _workSheet.cells().range(#ExcelTotalRange);
try
{
// Finds the row where the first contents is found.
range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
}
catch (Exception::Error)
{
error("@SYS59926");
return 0;
}
if (range)
{
return range.row();
}
else
{
return 0;
}
}
;
dialogFilename = dialog.addField(extendedtypestr(FilenameOpen));
// DialogJournalType = dialog.addField(enumstr(God_JournalToUpload),"Journal Type");
dialog.filenameLookupFilter(["@SYS28576",#XLS,#Xlsx]);
dialog.filenameLookupTitle("Upload from Excel");
dialogFilename.value(filename);
if(!dialog.run())
return;
filename = dialogFilename.value();
// ttsbegin;
application = SysExcelApplication::construct();
workBooks = application.workbooks();
workBooks.open(filename,0,true);
workSheets = workBooks.item(1).worksheets();
// this.importExcel("Sheet1");
input = "Sheet1";
workSheet = workSheets.itemFromNum(1);//.itemFromName(input);
cells = workSheet.cells();
lastRow = findLastRow(workSheet);
rowIdx = 2;
progress = new SysOperationProgress();
progress.setCaption("Excel Importing");
progress.setTotal(lastRow);
progress.setAnimation(#AviTransfer);
setprefix("Excel Import");
select vacBal;
while (rowIdx <= lastRow)
{
setPrefix(strfmt("Excel Row: %1", rowIdx));
c1 = COMVariant2Str(cells.item(rowIdx,1).value());
c2 = COMVariant2Str(cells.item(rowIdx,2).value());
c3 = COMVariant2Str(cells.item(rowIdx,3).value());
c4 = cells.item(rowIdx,4).value().double();
c5 = COMVariant2Str(cells.item(rowIdx,5).value());
linesImported++;
vacBal.ContractNo = str2int(c1);
vacBal.EmplId = c2;
vacBal.UptoDate = str2Date(c3,123);
vacBal.VacationBalance = c4;
vacBal.VacationCode = c5;
vacBal.IsAnnual = NoYes::Yes;
vacBal.insert();
progress.setText("Importing " + c2);
progress.setCount(linesImported);
rowIdx++;
}
info("done");
}
ExchangeRate USD to AED
static void TIDF_ExchangeRate(Args _args)
{
ExchangeRate exchangeRate;
ExchangeRateType ExchangeRateType;
ExchangeRateCurrencyPair exchangeRateCurrencyPair;
Amount4decimal exchRate;
CurrencyCode fromCurrency = "USD";
CurrencyCode toCurrency = "AED";
TransDate transDate = today();
select firstonly exchangeRateCurrencyPair
where
exchangeRateCurrencyPair.ExchangeRateType == 5637145326
&& exchangeRateCurrencyPair.FromCurrencyCode == fromCurrency
&& exchangeRateCurrencyPair.ToCurrencyCode == toCurrency;
exchRate = exchangeRate::findByDate(exchangeRateCurrencyPair.RecId,transDate).ExchangeRate;
info(strFmt("%1",exchRate/100));
}
{
ExchangeRate exchangeRate;
ExchangeRateType ExchangeRateType;
ExchangeRateCurrencyPair exchangeRateCurrencyPair;
Amount4decimal exchRate;
CurrencyCode fromCurrency = "USD";
CurrencyCode toCurrency = "AED";
TransDate transDate = today();
select firstonly exchangeRateCurrencyPair
where
exchangeRateCurrencyPair.ExchangeRateType == 5637145326
&& exchangeRateCurrencyPair.FromCurrencyCode == fromCurrency
&& exchangeRateCurrencyPair.ToCurrencyCode == toCurrency;
exchRate = exchangeRate::findByDate(exchangeRateCurrencyPair.RecId,transDate).ExchangeRate;
info(strFmt("%1",exchRate/100));
}
Job for item with out tracking dimension
static void KavinValidateTracDim(Args _args)
{
EcoResTrackingDimensionGroupItem trackDimGrop;
EcoResTrackingDimensionGroupFldSetup trackDimGrpFldSetup;
ItemId varItemId = '125';
FieldId observedDimension = fieldNum(InventDim, InventBatchId);
str label = fieldPName(InventDim, InventBatchId);
NoYes isActive;
InventTable inventTable;
;
while select inventTable
join trackDimGrop where trackDimGrop.ItemDataAreaId == inventTable.dataAreaId &&
trackDimGrop.ItemId == inventTable.ItemId //&& inventTable.ItemId == "125"
join trackDimGrpFldSetup where trackDimGrpFldSetup.TrackingDimensionGroup == trackDimGrop.TrackingDimensionGroup
&& trackDimGrpFldSetup.DimensionFieldId == observedDimension
&& trackDimGrpFldSetup.IsActive == NoYes::No
{
info(strFmt('%1 is not active for %2',label,inventTable.ItemId));
}
}
{
EcoResTrackingDimensionGroupItem trackDimGrop;
EcoResTrackingDimensionGroupFldSetup trackDimGrpFldSetup;
ItemId varItemId = '125';
FieldId observedDimension = fieldNum(InventDim, InventBatchId);
str label = fieldPName(InventDim, InventBatchId);
NoYes isActive;
InventTable inventTable;
;
while select inventTable
join trackDimGrop where trackDimGrop.ItemDataAreaId == inventTable.dataAreaId &&
trackDimGrop.ItemId == inventTable.ItemId //&& inventTable.ItemId == "125"
join trackDimGrpFldSetup where trackDimGrpFldSetup.TrackingDimensionGroup == trackDimGrop.TrackingDimensionGroup
&& trackDimGrpFldSetup.DimensionFieldId == observedDimension
&& trackDimGrpFldSetup.IsActive == NoYes::No
{
info(strFmt('%1 is not active for %2',label,inventTable.ItemId));
}
}
Vendor Account Statement Report Summary Total Enable
Goto GL - Setup - Journals - Journal Names - Print Amount Foreign Currency On Journal Report
Subscribe to:
Posts (Atom)