Parameter Form
Submission role lookup
public void lookup()
{
//super();
SysTableLookup tableLookup;
Query qry;
QueryBuildDataSource qbds;
// SecurityRole SecurityRoleLoc;
qry = new Query();
qbds = qry.adddatasource(tableNum(SecurityRole));
qbds.addrange(fieldNum(SecurityRole,Name)).Value();
tableLookup = SysTableLookup::newParameters(tableNum(SecurityRole),this);
tableLookup.addLookupfield(fieldNum(SecurityRole,Name));
tableLookup.parmQuery(qry);
tableLookup.performFormLookup();
}
Approver role lookup:
public void lookup()
{
//super();
SysTableLookup tableLookup;
Query qry;
QueryBuildDataSource qbds;
//SecurityRole SecurityRoleLoc;
qry = new Query();
qbds = qry.adddatasource(tableNum(SecurityRole));
qbds.addrange(fieldNum(SecurityRole,Name)).Value();
tableLookup = SysTableLookup::newParameters(tableNum(SecurityRole),this);
tableLookup.addLookupfield(fieldNum(SecurityRole,Name));
tableLookup.parmQuery(qry);
tableLookup.performFormLookup();
}
Forms: Header and lines
Header form
public class FormRun extends ObjectRun
{
boolean roleExist;
}
public int active() //Form DS
{
int ret;
ret = super();
if (AweBudgetHeader.BudgetStatus == AweBudgetStatus::Approved
|| AweBudgetHeader.BudgetStatus == AweBudgetStatus::Posted)
{
AweBudgetHeader_ds.allowDelete(false);
}
else
{
AweBudgetHeader_ds.allowDelete(true);
}
return ret;
}
public void executeQuery() //Form DS level
{
AweBudgetStatus budgetStatus = str2enum(budgetStatus, AWEBudgetHeader_AWEBudgetStatus.valueStr());
//str2enum(JournalStatus,AWEJournalHeader_AWEStatus.valueStr());
super();
if(budgetStatus != AweBudgetStatus::All)
{
select * from AweBudgetHeader where AweBudgetHeader.BudgetStatus == budgetStatus;
}
}
void clicked() // Approved button
{
//super();
SecurityRole securityRole;
SecurityUserRole securityUserRole;
UserInfo userInfo;
str submitRole, ApproveRole, userRole,recid;
AweBudgetHeaderStagingTable budgetHeaderStagingTable;
int track = 0;
int64 recIdLoc;
budgetHeaderStagingTable = Awebudgetheader_ds.getFirst(true);
//submitRole = BudgetParameters::find().AweSubmissionRole;
ApproveRole = BudgetParameters::find().AweApproveRole;
while select securityRole
exists join securityUserRole
where securityRole.RecId == securityUserRole.SecurityRole &&
securityUserRole.User == curUserId()
{
userRole = securityRole.Name;
if (userRole == ApproveRole || userRole == 'System administrator')
{
roleExist = false;
while (budgetHeaderStagingTable.RecId != 0 && budgetHeaderStagingTable.BudgetStatus == AweBudgetStatus::Submitted)
{
recIdLoc = budgetHeaderStagingTable.RecId;
ttsBegin;
select forUpdate budgetHeaderStagingTable
where budgetHeaderStagingTable.RecId == recIdLoc;
budgetHeaderStagingTable.BudgetStatus = AweBudgetStatus::Approved;
//budgetHeaderStagingTable.SubmittedUser = curUserId();
//budgetHeaderStagingTable.SubmittedDateTime = DateTimeUtil::getSystemDateTime();
budgetHeaderStagingTable.Approved_RejectedBy = curUserId();
budgetHeaderStagingTable.Approved_RejectedDateTime = DateTimeUtil::getSystemDateTime();
budgetHeaderStagingTable.update();
ttsCommit;
budgetHeaderStagingTable = Awebudgetheader_ds.getNext();
track ++;
}
if (budgetHeaderStagingTable.RecId != 0 && budgetHeaderStagingTable.BudgetStatus != AweBudgetStatus::Submitted)
{
info("Status should be 'Submitted' to approve the Budget");
}
Awebudgetheader_ds.reread();
Awebudgetheader_ds.executeQuery();
Awebudgetheader_ds.refresh();
roleExist = true;
}
}
if (roleExist == true)
{
info(strFmt("Budget records are approved successfully: %1",track));
}
else
{
info("User - " +curUserId()+ " - don't have privilleges to approve the record");
}
}
public boolean modified() //unbounded field(filter)
{
boolean ret;
ret = super();
AweBudgetHeader_ds.executeQuery();
return ret;
}
void clicked() //Action MenuItem button
{
super();
AweBudgetHeader_ds.executeQuery();
}
void clicked() // Rejected button
{
//super();
SecurityRole securityRole;
SecurityUserRole securityUserRole;
UserInfo userInfo;
str submitRole, ApproveRole, userRole,recid;
AweBudgetHeaderStagingTable budgetHeaderStagingTable;
int track = 0;
int64 recIdLoc;
budgetHeaderStagingTable = Awebudgetheader_ds.getFirst(true);
//submitRole = BudgetParameters::find().AweSubmissionRole;
ApproveRole = BudgetParameters::find().AweApproveRole;
while select securityRole
exists join securityUserRole
where securityRole.RecId == securityUserRole.SecurityRole &&
securityUserRole.User == curUserId()
{
userRole = securityRole.Name;
if (userRole == ApproveRole || userRole == 'System administrator')
{
roleExist = false;
while (budgetHeaderStagingTable.RecId != 0 && budgetHeaderStagingTable.BudgetStatus == AweBudgetStatus::Submitted)
{
recIdLoc = budgetHeaderStagingTable.RecId;
ttsBegin;
select forUpdate budgetHeaderStagingTable
where budgetHeaderStagingTable.RecId == recIdLoc;
budgetHeaderStagingTable.BudgetStatus = AweBudgetStatus::Rejected;
budgetHeaderStagingTable.Approved_RejectedBy = curUserId();
budgetHeaderStagingTable.Approved_RejectedDateTime = DateTimeUtil::getSystemDateTime();
//budgetHeaderStagingTable.SubmittedUser = curUserId();
//budgetHeaderStagingTable.SubmittedDateTime = DateTimeUtil::getSystemDateTime();
budgetHeaderStagingTable.update();
ttsCommit;
budgetHeaderStagingTable = Awebudgetheader_ds.getNext();
track ++;
}
if (budgetHeaderStagingTable.RecId != 0 && budgetHeaderStagingTable.BudgetStatus != AweBudgetStatus::Submitted)
{
info("Budget status should be 'Submitted' state to allow reject ");
}
Awebudgetheader_ds.reread();
Awebudgetheader_ds.executeQuery();
Awebudgetheader_ds.refresh();
roleExist = true;
}
}
if (roleExist == true)
{
info(strFmt("Budget records are rejected successfully: %1",track));
}
else
{
info("User - " +curUserId()+ " - don't have privilleges to reject the record");
}
}
void clicked() //Submitted button
{
//super();
SecurityRole securityRole;
SecurityUserRole securityUserRole;
UserInfo userInfo;
str submitRole, ApproveRole, userRole,recid;
AweBudgetHeaderStagingTable budgetHeaderStagingTable;
int track = 0;
int64 recIdLoc;
budgetHeaderStagingTable = Awebudgetheader_ds.getFirst(true);
submitRole = BudgetParameters::find().AweSubmissionRole;
//ApproveRole = BudgetParameters::find().AweApproveRole;
while select securityRole
exists join securityUserRole
where securityRole.RecId == securityUserRole.SecurityRole &&
securityUserRole.User == curUserId()
{
userRole = securityRole.Name;
if (userRole == submitRole || userRole == 'System administrator')
{
roleExist = false;
while (budgetHeaderStagingTable.RecId != 0 && budgetHeaderStagingTable.BudgetStatus == AweBudgetStatus::Valid)
{
recIdLoc = budgetHeaderStagingTable.RecId;
ttsBegin;
select forUpdate budgetHeaderStagingTable
where budgetHeaderStagingTable.RecId == recIdLoc;
budgetHeaderStagingTable.BudgetStatus = AweBudgetStatus::Submitted;
budgetHeaderStagingTable.SubmittedUser = curUserId();
budgetHeaderStagingTable.SubmittedDateTime = DateTimeUtil::getSystemDateTime();
budgetHeaderStagingTable.update();
ttsCommit;
budgetHeaderStagingTable = Awebudgetheader_ds.getNext();
track ++;
}
if (budgetHeaderStagingTable.RecId != 0 && budgetHeaderStagingTable.BudgetStatus != AweBudgetStatus::Valid)
{
info("Status should be 'Valid' to submit the Budget");
}
Awebudgetheader_ds.reread();
Awebudgetheader_ds.executeQuery();
Awebudgetheader_ds.refresh();
roleExist = true;
}
}
if (roleExist == true)
{
info(strFmt("Budget records are submitted successfully: %1",track));
}
else
{
info("User - " +curUserId()+ " - don't have privilleges to submit record");
}
}
void clicked() // Posted button
{
AweBudgetHeaderStagingTable budgetHeaderTable;
super();
budgetHeaderTable = Awebudgetheader_ds.getFirst(1);
if (budgetHeaderTable.RecId == 0)
{
throw error("Please select at least one record to crate budget");
}
if (budgetHeaderTable.BudgetStatus != AweBudgetStatus::Approved)
{
throw error(strFmt("Budget can not be created due to %1", enum2str(budgetHeaderTable.BudgetStatus)));
}
while (budgetHeaderTable.RecId != 0)
{
//changeCompany(budgetHeaderTable.CompanyId)
//{
AweBudgetImportIntoStaging::construct().createBudget(budgetHeaderTable);
//}
budgetHeaderTable = Awebudgetheader_ds.getNext();
}
Awebudgetheader_ds.reread();
Awebudgetheader_ds.executeQuery();
Awebudgetheader_ds.refresh();
}
void clicked() //Validate button
{
AweBudgetHeaderStagingTable budgetHeader;
super();
budgetHeader = AweBudgetHeader_DS.getFirst(1);
if (budgetHeader.RecId ==0)
{
throw error("@AWE1280");
}
if(budgetHeader.BudgetStatus == AweBudgetStatus::NotProcessed || budgetHeader.BudgetStatus == AweBudgetStatus::Invalid )
{
if(budgetHeader.BudgetStatus == AweBudgetStatus::Valid)
throw error(strFmt("@AWE1279", budgetHeader.BudgetModelId));
if(budgetHeader.BudgetStatus == AweBudgetStatus::Posted)
throw error(strFmt("%1 Budget is already posted", budgetHeader.BudgetModelId));
}
while (budgetHeader.RecId != 0)
{
budgetHeader = AweBudgetHeader_DS.getNext();
}
AweBudgetHeader_DS.reread();
AweBudgetHeader_DS.executeQuery();
AweBudgetHeader_DS.refresh();
}
Line Form:
public class FormRun extends ObjectRun
{
RefRecId callerRecid;
}
public void displayOption(Common _record, FormRowDisplayOption _options)
{
super(_record, _options);
}
public void init()
{
super();
if(element.args().dataset() == tableNum(AweBudgetHeaderStagingTable))
{
callerRecid = element.args().record().getFieldValue("Recid");
}
}
public void executeQuery()
{
super();
select * from AweBudgetLinesStagingTable where AweBudgetLinesStagingTable.BudgetTransactionHeader == callerRecid;
}
Class: Import Excel data
class AweBudgetImportIntoStaging
{
AmountMST totals;
Voucher voucherold;
Voucher voucher;
int linenumber;
FilenameOpen filenameRead;
}
str COMVariant2Str(COMVariant _cv,
int _decimals = 1,
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,
false);
case COMVariantType::VT_EMPTY:
return "";
default:
throw error(strfmt("@SYS26908",
_cv.variantType()));
}
return "";
}
public void createBudget(AweBudgetHeaderStagingTable _budgetHeaderTable, Boolean _post = false)
{
AweBudgetLinesStagingTable aweBudgetLinesStagingTable, aweBudgetLinesStagingTableLoc, aweBudgetLinesStagingTableLocal;
BudgetTransactionHeader budgetTransactionHeader;
BudgetTransactionCode budgetTransactionCode;
str information;
NumberSeq numberSeq;
;
select firstFast firstOnly aweBudgetLinesStagingTableLocal
order by aweBudgetLinesStagingTableLocal.CompanyId
where aweBudgetLinesStagingTableLocal.BudgetTransactionHeader == _budgetHeaderTable.RecId &&
aweBudgetLinesStagingTableLocal.CompanyId == curext() &&
aweBudgetLinesStagingTableLocal.Posted == NoYes::Yes;
if (aweBudgetLinesStagingTableLocal)
{
throw Global::warning(strFmt('Budget is already posted for entity %1', curext()));
}
try
{
ttsBegin;
budgetTransactionHeader.clear();
budgetTransactionHeader.initValue();
budgetTransactionHeader.BudgetModelId = _budgetHeaderTable.BudgetModelId;
budgetTransactionHeader.BudgetTransactionType = _budgetHeaderTable.BudgetTransactionType;
budgetTransactionHeader.TransferSourceNumber = _budgetHeaderTable.SubmittedUser;
budgetTransactionHeader.BudgetModelDataAreaId = curext();
budgetTransactionHeader.Date = systemDateGet();
numberSeq = NumberSeq::newGetNum(BudgetParameters::numRefBudgetTransactionId());
voucher = numberSeq.num();
budgetTransactionHeader.TransactionNumber = voucher;
budgetTransactionHeader.insert();
numberSeq.used();
budgetTransactionHeader.reread();
budgetTransactionHeader.selectForUpdate();
select BudgetTransactionType, RecId from budgetTransactionCode where budgetTransactionCode.BudgetTransactionType == budgetTransactionHeader.BudgetTransactionType;
if (budgetTransactionCode.RecId)
{
budgetTransactionHeader.BudgetTransactionCode = budgetTransactionCode.RecId;
}
budgetTransactionHeader.TransactionStatus = BudgetTransactionStatus::Completed;
budgetTransactionHeader.update();
ttsCommit;
linenumber = 1;
ttsBegin;
while select * from aweBudgetLinesStagingTable
order by aweBudgetLinesStagingTable.CompanyId
where aweBudgetLinesStagingTable.BudgetTransactionHeader == _budgetHeaderTable.RecId && aweBudgetLinesStagingTable.CompanyId == curext()
{ this.createBudgetLines(budgetTransactionHeader,aweBudgetLinesStagingTable,_budgetHeaderTable);
}
ttsCommit;
if(budgetTransactionHeader.RecId)
{
select firstFast firstOnly aweBudgetLinesStagingTableLoc
order by aweBudgetLinesStagingTableLoc.CompanyId
where aweBudgetLinesStagingTableLoc.BudgetTransactionHeader == _budgetHeaderTable.RecId &&
aweBudgetLinesStagingTableLoc.Posted == NoYes::No;
if (!aweBudgetLinesStagingTableLoc)
{
this.updateStaging(_budgetHeaderTable, AweBudgetStatus::Posted);
information = strFmt("Budget %1 is created successfully for Entity %2", budgetTransactionHeader.TransactionNumber, curext());
}
else
{
information = strFmt("Budget %1 is created successfully for Entity %2", budgetTransactionHeader.TransactionNumber, curext());
}
}
else
{
this.updateStaging(_budgetHeaderTable, AweBudgetStatus::Error);
throw error(strFmt("Budget %1 is not created", _budgetHeaderTable.BudgetModelId));
}
info(information);
}
catch
{
this.updateStaging(_budgetHeaderTable, AweBudgetStatus::Error);
throw error(strFmt("Budget %1 could not created", _budgetHeaderTable.BudgetModelId));
}
}
public void createBudgetLines( BudgetTransactionHeader _budgetTransactionHeader,
AweBudgetLinesStagingTable budgetLines,
AweBudgetHeaderStagingTable _budgetHeaderTable
)
{
BudgetTransactionLine budgetTransactionLine;
MainAccountCategory mainAccountCategory;
MainAccount mainAccountLoc;
AweBudgetLinesStagingTable budgetLinesLoc;
BudgetType budgetType;
str budgetTypeStr;
date dt;
int i = 1;
int j = 12;
select *from mainAccountLoc where mainAccountLoc.MainAccountId == budgetLines.MainAccountNum && mainAccountLoc.Type == DimensionLedgerAccountType::ProfitAndLoss;
if (mainAccountLoc.recid)
{
select *from mainAccountCategory where mainAccountCategory.AccountCategoryRef == mainAccountLoc.AccountCategoryRef;
if (mainAccountCategory.RecId)
{
budgetTypeStr = enum2str(mainAccountCategory.AccountType);
}
}
budgetTransactionLine.BudgetType = str2enum(budgetType, budgetTypeStr);
budgetTransactionLine.Comment = budgetLines.Description;
budgetTransactionLine.LedgerDimension = budgetLines.LedgerDimension;
budgetTransactionLine.TransactionCurrency = _budgetHeaderTable.CurrencyCode;
budgetTransactionLine.BudgetTransactionHeader = _budgetTransactionHeader.RecId;
while (j >= i)
{
if (i == 1)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Jan;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Jan;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 01, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 2)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Feb;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Feb;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 02, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 3)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Mar;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Mar;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 03, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 4)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Apr;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Apr;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 04, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 5)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_May;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_May;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 05, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 6)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Jun;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Jun;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 06, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 7)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Jul;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Jul;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 07, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 8)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Aug;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Aug;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 08, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 9)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Sep;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Sep;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 09, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 10)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Oct;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Oct;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 10, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 11)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Nov;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Nov;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 11, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
if (i == 12)
{
budgetTransactionLine.AccountingCurrencyAmount = budgetLines.Amount_Dec;
budgetTransactionLine.TransactionCurrencyAmount = budgetLines.Amount_Dec;
budgetTransactionLine.LineNumber = linenumber;
dt = mkDate(01, 12, _budgetHeaderTable.BudgetYear);
budgetTransactionLine.Date = dt;
linenumber++;
}
budgetTransactionLine.doInsert();
i++;
}
ttsBegin;
select forUpdate budgetLinesLoc where budgetLinesLoc.RecId == budgetLines.RecId;
if (budgetLinesLoc.RecId)
{
budgetLinesLoc.Posted = NoYes::Yes;
budgetLinesLoc.doUpdate();
}
ttsCommit;
}
private FileName getFileName()
{
Filename filename;
Dialog dialog;
DialogField dialogField;
;
dialog = new Dialog("@SYS16423");
dialogfield = dialog.addField(extendedTypeStr(Filenameopen), "@SYS53125");
if (dialog.run())
{
filename = dialogfield.value();
}
return filename;
}
private AweBudgetHeaderStagingTable insertBudgetHeader(SysExcelCells _cells)
{
#Define.Yes('YES')
str reverseEntry,journaldDesc,jourNum;
AweBudgetHeaderStagingTable budgetHeaderTable;
BudgetTransactionType budgetTransactionType;
;
budgetHeaderTable.BatchFileRef = this.COMVariant2Str(_cells.item(4, 3).value(),0);
budgetHeaderTable.BudgetModelId = this.COMVariant2Str(_cells.item(5, 3).value(),0);
budgetHeaderTable.BudgetTransactionType = str2enum(budgetTransactionType,_cells.item(6,3).value().bStr());
budgetHeaderTable.BudgetYear = str2int(this.COMVariant2Str(_cells.item(7, 3).value(),0));
budgetHeaderTable.CurrencyCode = this.COMVariant2Str(_cells.item(8, 3).value(),0);
budgetHeaderTable.UserInfo = curUserId();
budgetHeaderTable.BudgetDateTime = DateTimeUtil::getSystemDateTime();
budgetHeaderTable.insert();
budgetHeaderTable.reread();
return budgetHeaderTable;
}
Public void run()
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
COMVariantType type;
FileName filename;
//CS- Code added to count journal lines by kambalh
int row,recordsCount,budgetLineCount = 0;
AweBudgetLinesStagingTable budgetLines;
RecordInsertList recordInsertlist = new RecordInsertList(tableNum(AweBudgetLinesStagingTable));
AweBudgetHeaderStagingTable budgetHeaderTable,budgetHeaderTableNew;
str batchFile;
AweBudgetStatus aweBudgetStatus;
Description journalDescription;
BudgetTransactionType budgetTransactionType;
;
try
{
filename = this.getFileName();
application = SysExcelApplication::construct();
application.displayAlerts(false);
workbooks = application.workbooks();
workbooks.open(filename);
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
ttsBegin;
batchFile = cells.item(4, 3).value().bStr();
budgetHeaderTable = this.insertBudgetHeader(cells);
row = row + 11;
do
{
row++;
if(row >= 12)
{
budgetLines.BudgetTransactionHeader = budgetHeaderTable.RecId;
budgetLines.Line = str2int(this.COMVariant2Str(cells.item(row, 1).value(),0));
budgetLines.CompanyId = cells.item(row,2).value().bStr();
budgetLines.MainAccountNum = this.COMVariant2Str(cells.item(row,3).value(),0);
budgetLines.LineOfBusinessId = cells.item(row,4).value().bStr();
budgetLines.Department = this.COMVariant2Str(cells.item(row, 5).value(),0);
budgetLines.Location = cells.item(row,6).value().bStr();
budgetLines.Product = this.COMVariant2Str(cells.item(row, 7).value(),0);
budgetLines.Project = strLRTrim(this.COMVariant2Str(cells.item(row, 8).value(),0));
budgetLines.Description = cells.item(row, 9).value().bstr();
budgetLines.BudgetTransactionType = str2enum(budgetTransactionType,cells.item(6,3).value().bStr());
budgetLines.Amount_Jan = cells.item(row, 10).value().double();
budgetLines.Amount_Feb = cells.item(row, 11).value().double();
budgetLines.Amount_Mar = cells.item(row, 12).value().double();
budgetLines.Amount_Apr = cells.item(row, 13).value().double();
budgetLines.Amount_May = cells.item(row, 14).value().double();
budgetLines.Amount_Jun = cells.item(row, 15).value().double();
budgetLines.Amount_Jul = cells.item(row, 16).value().double();
budgetLines.Amount_Aug = cells.item(row, 17).value().double();
budgetLines.Amount_Sep = cells.item(row, 18).value().double();
budgetLines.Amount_Oct = cells.item(row, 19).value().double();
budgetLines.Amount_Nov = cells.item(row, 20).value().double();
budgetLines.Amount_Dec = cells.item(row, 21).value().double();
budgetLines.Amount_Total = budgetLines.Amount_Jan + budgetLines.Amount_Feb + budgetLines.Amount_Mar + budgetLines.Amount_Apr
+ budgetLines.Amount_May + budgetLines.Amount_Jun + budgetLines.Amount_Jul + budgetLines.Amount_Aug
+ budgetLines.Amount_Sep + budgetLines.Amount_Oct + budgetLines.Amount_Nov +budgetLines.Amount_Dec;
recordInsertlist.add(budgetLines);
recordsCount++;
budgetLineCount++;
}
type = cells.item(row+1, 2).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
recordInsertlist.insertDatabase();
//CS- Code added to display appropriate info log of Journals created by kambalh
if(batchFile != this.COMVariant2Str(cells.item(row+1, 2).value(),0))
{
info(strFmt("Budget - %1, is inserted with total number of lines - %2",budgetHeaderTable.BatchFileRef ,budgetLineCount));
//info(strFmt("@AWE1286",AWEJournalHeaderStaging.JournalDescription,journalLineCount));
budgetLineCount = 0;
}
//CE- Code added to display appropriate info log of Journals created by kambalh
ttsCommit;
// info(strFmt("@AWE1286",cells.item(4, 4).value().bStr(),recordsCount)); //Code commented to display appropriate info log of Journals created by kambalh
application.workbooks().close();
application.quit();
application.finalize();
application = null;
}
catch
{
if(appl.ttsLevel() >= 1)
ttsAbort;
application.workbooks().close();
application.quit();
application.finalize();
application = null;
info("@AWE1285");
}
}
private void updateStaging(AweBudgetHeaderStagingTable _BudgetHeader,AweBudgetStatus status)
{
ttsBegin;
_BudgetHeader.selectForUpdate(true);
_BudgetHeader.BudgetStatus = status;
_BudgetHeader.doUpdate();
ttsCommit;
}
public void validateStagingBudget(AweBudgetHeaderStagingTable BudgetHeader)
{
AweBudgetLinesStagingTable budgetLines;
CustTable cusTable;
ProjTable projTable;
int currentLine,startLine;
boolean error = false,headerError, lineError;
boolean errorModelId, errorTrType, errorCurr, errorYear;
str errorMsg = '';
str headerErrorMsg = '';
BudgetModel budgetModel;
BudgetTransactionCode budgetTransactionCode;
MainAccount mainAccount;
//Currency currency;
CurrencyOnlineConversion currencyOnlineConversion;
DimensionAttributeLevelValueView attributeLevelValueView;
LedgerInterCompany ledgerInterCompany;
AWEFinDimMapping aWEFinDimMapping;
AweProductCategory aweProductCategory;
FiscalCalendarYear fiscalCalendarYear;
#Define.MainAccount('MainAccount')
#Define.Department('Department')
#Define.LineofBusiness('LineofBusiness')
#Define.Location('Location')
#Define.MarketingCampaign('MarketingCampaign')
#Define.Product('Product')
#Define.Project('Project')
;
if(BudgetHeader.BudgetStatus == AweBudgetStatus::NotProcessed || BudgetHeader.BudgetStatus == AweBudgetStatus::Invalid)
{
startLine = Global::infologLine();
ttsBegin;
if (BudgetHeader.BudgetModelId == "")
{
Global::error("Budget model should not be empty");
headerErrorMsg += "Budget model should not be empty";
headerErrorMsg += "\n";
headerError = true;
}
if (enum2str(BudgetHeader.BudgetTransactionType) == "")
{
Global::error("Budget type should not be empty");
headerErrorMsg += "Budget type should not be empty";
headerErrorMsg += "\n";
headerError = true;
}
while select RecId, ModelId from budgetModel where budgetModel.ModelId == BudgetHeader.BudgetModelId
if (budgetModel.RecId)
{
errorModelId = true;
}
if (!errorModelId)
{
Global::error(strFmt("Budget model %1 is invalid", BudgetHeader.BudgetModelId));
headerErrorMsg += strFmt("Budget model %1 is invalid", BudgetHeader.BudgetModelId);
headerErrorMsg += "\n";
headerError = true;
}
while select RecId, BudgetTransactionType from budgetTransactionCode where budgetTransactionCode.BudgetTransactionType == BudgetHeader.BudgetTransactionType
if (budgetTransactionCode.RecId)
{
errorTrType = true;
}
if (!errorTrType)
{
Global::error(strFmt("Budget transaction type %1 is invalid", BudgetHeader.BudgetTransactionType));
headerErrorMsg += strFmt("Budget transaction type %1 is invalid", BudgetHeader.BudgetTransactionType);
headerErrorMsg += "\n";
headerError = true;
}
while select RecId, CurrencyCode from currencyOnlineConversion where currencyOnlineConversion.CurrencyCode == BudgetHeader.CurrencyCode
if (currencyOnlineConversion.RecId)
{
errorCurr = true;
}
if (!errorCurr)
{
Global::error(strFmt("Budget currency %1 is invalid", BudgetHeader.CurrencyCode));
headerErrorMsg += strFmt("Budget currency %1 is invalid", BudgetHeader.CurrencyCode);
headerErrorMsg += "\n";
headerError = true;
}
while select RecId, Name from fiscalCalendarYear where fiscalCalendarYear.Name == int2str(BudgetHeader.BudgetYear)
if (fiscalCalendarYear.RecId)
{
errorYear = true;
}
if (!errorYear)
{
Global::error(strFmt("Budget Year %1 is invalid", BudgetHeader.BudgetYear));
headerErrorMsg += strFmt("Budget Year %1 is invalid", BudgetHeader.BudgetYear);
headerErrorMsg += "\n";
headerError = true;
}
while select forUpdate * from budgetLines
where budgetLines.BudgetTransactionHeader == BudgetHeader.RecId
{
try
{
errorMsg = '';
if(budgetLines.Department)
{
select firstonly1 recid from attributeLevelValueView
where attributeLevelValueView.DisplayValue == budgetLines.Department;
if(!attributeLevelValueView.RecId)
{
errorMsg += strFmt("@AWE1292",#Department,budgetLines.Department, budgetLines.CompanyId);
errorMsg += "\n";
lineError = true;
}
}
if(budgetLines.Product)
{
select firstonly1 recid from attributeLevelValueView
where attributeLevelValueView.DisplayValue == budgetLines.Product;
if(!attributeLevelValueView.RecId)
{
errorMsg += strFmt("@AWE1292",#Product, budgetLines.Product, budgetLines.CompanyId);
errorMsg += "\n";
lineError = true;
}
}
if(budgetLines.Project)
{
select firstonly1 recid from attributeLevelValueView
where attributeLevelValueView.DisplayValue == budgetLines.Project;
if(!attributeLevelValueView.RecId)
{
errorMsg += strFmt("@AWE1292",#Project, budgetLines.Project, budgetLines.CompanyId);
errorMsg += "\n";
lineError = true;
}
}
if (budgetLines.CompanyId && budgetLines.LineOfBusinessId && budgetLines.Location)
{
if(!AWEFinDimMapping::find(budgetLines.LineOfBusinessId, budgetLines.CompanyId, budgetLines.Location))
{
errorMsg += strFmt("@AWE1294",budgetLines.LineOfBusinessId,budgetLines.Location,budgetLines.CompanyId);
errorMsg += "\n";
lineError = true;
}
}
if (budgetLines.LineOfBusinessId && budgetLines.Product)
{
select firstfast firstonly AweProductCategory
where AweProductCategory.Value == budgetlines.Product
&& aweProductCategory.LOB == budgetLines.LineOfBusinessId;
if (!aweProductCategory)
{
errorMsg += strFmt("Line number %1, %2 Product is not available with combination of Line of Business %3", budgetlines.Line, budgetlines.Product, budgetLines.LineOfBusinessId);
errorMsg += "\n";
lineError = true;
}
}
mainAccount = MainAccount::findByMainAccountId(budgetLines.MainAccountNum);
if (mainAccount)
{
if ( mainAccount.Type == DimensionLedgerAccountType::ProfitAndLoss)
{
if(!budgetLines.Location)
{
errorMsg += strFmt("Location %1 can't be balnk when mainaccount type is ProfitAndLoss", mainAccount.Type);
errorMsg += "\n";
lineError = true;
}
else if(budgetLines.Location)
{
if(!AWEFinDimMapping::find(budgetLines.LineOfBusinessId, budgetLines.CompanyId, budgetLines.Location))
{
errorMsg += strFmt("@AWE1294",budgetLines.LineOfBusinessId,budgetLines.Location,budgetLines.CompanyId);
errorMsg += "\n";
lineError = true;
}
}
}
else //Else condition Added by Sac
{
errorMsg += strFmt('Main Account %1 should be Profit and Loss Account type.', mainAccount.Type);
errormsg += '\n';
lineError = true;
}
}
else
{
errorMsg += strFmt('%1Main Account is invalid.', budgetLines.MainAccountNum);
errormsg += '\n';
lineError = true;
}
if(budgetLines.LineOfBusinessId == '')
{
errorMsg += "@AWE1295";
errorMsg += "\n";
lineError = true;
}
if (errorMsg != '')
{
budgetLines.ErrorDescription = errorMsg;
budgetLines.Errored = NoYes::Yes;
error = true;
}
else
{
changeCompany(budgetLines.CompanyId)
{
budgetLines.LedgerDimension = AweBudgetImportIntoStaging::getLedgerDimension(budgetLines.MainAccountNum,
budgetLines.Department,
budgetLines.LineOfBusinessId,
budgetLines.Location,
budgetLines.Product,
budgetLines.Project);
}
budgetLines.Errored = NoYes::No;
}
budgetLines.update();
}
catch
{
for (currentLine = startLine + 1; currentLine <= Global::infologLine(); currentLine++)
{
errorMsg += subStr(errorMsg + "." +"\n"+ infolog.text(currentLine),1,1260);
}
budgetLines.ErrorDescription = errorMsg;
budgetLines.Errored = NoYes::Yes;
budgetLines.doUpdate();
error = true;
continue;
}
}
if(error || headerError || lineError)
{
this.updateStaging(BudgetHeader, AweBudgetStatus::Invalid);
if (headerError)
{
Global::error('The Budget Header Validation is failed');
}
if (errorMsg || lineError)
{
Global::error("Budget lines validation is failed");
}
BudgetHeader.selectForUpdate();
BudgetHeader.ErrorDescription = headerErrorMsg;
BudgetHeader.update();
}
else
{
this.updateStaging(BudgetHeader, AweBudgetStatus::Valid);
info("Budget has been validated successfully");
}
ttsCommit;
}
else
{
info(strFmt("%1 Budget is already valid", BudgetHeader.BudgetModelId));
}
}
server static AweBudgetImportIntoStaging construct()
{
return new AweBudgetImportIntoStaging();
}
public static RefRecId getLedgerDimension(DimensionValue attrValMainAccount,
DimensionValue attrValDepartment,
DimensionValue attrValLineofBusiness,
DimensionValue attrValLocation,
DimensionValue attrValProduct,
DimensionValue attrValProject,
DimensionValue attrValMarketingCampaign = nullValueFromType(Types::String))
{
#Define.MainAccount('MainAccount')
#Define.Department('Department')
#Define.LineofBusiness('LineofBusiness')
#Define.Location('Location')
#Define.MarketingCampaign('MarketingCampaign')
#Define.Product('Product')
#Define.Project('Project')
container ledgerDimensions,containerDIM;
int i;
Map map = new Map(Types::String,Types::String);
MapEnumerator mapEnum;
map.insert(#Department,attrValDepartment);
map.insert(#LineofBusiness,attrValLineofBusiness);
map.insert(#Location,attrValLocation);
map.insert(#MarketingCampaign,attrValMarketingCampaign);
map.insert(#Product,attrValProduct);
map.insert(#Project,attrValProject);
mapEnum = map.getEnumerator();
while(mapEnum.moveNext())
{
if(mapenum.currentValue() != '')
{
containerDIM += [mapenum.currentKey(),mapenum.currentValue()];
i++;
}
}
if(attrValMainAccount == '')
{
throw error("@AWE1287");
}
else
{
ledgerDimensions = [#MainAccount,attrValMainAccount];
ledgerDimensions += [i];
ledgerDimensions += containerDIM;
}
return AxdDimensionUtil::getLedgerAccountId(ledgerDimensions);
}
public static void main(Args _args)
{
AweBudgetImportIntoStaging aweBudgetImportIntoStaging;
FormControl FormControl;
;
aweBudgetImportIntoStaging = new aweBudgetImportIntoStaging();
aweBudgetImportIntoStaging.run();
FormControl = _args.callerFormControl();
FormControl.formRun().init();
}
No comments:
Post a Comment