//Creation of Budget header and Lines through Class in AX 2012
Excel Template
BudgetCode,
BudgetModel,
AccountStructure,
Date,
Amount,
AmountType,
MainAccount,
BusinessUnit,Department,
CostCenter
class San_ImportBudget extends RunBase
{
// Packed variables
Filename importFileName;
container accountSegments;
container accountSegmentNames;
BudgetCode budgetCode;
BudgetModelId budgetModelId;
Name accountStructureName;
TransDate transDate;
AmountCur amount;
BudgetType budgetType;
MainAccountNum mainAccountNum;
// Dialog fields
DialogField dlgImportFileName;
#define.CurrentVersion(1)
#define.Version1(1)
#localmacro.CurrentList
importFileName
#endmacro
BudgetTransactionService budgetTransactionService;
BudgetTransaction budgetTransaction;
BudgetTransaction_BudgetTransHeader budgetTransaction_BudgetTransHeader;
str fileName;
str filePath;
str fileExtension;
}
public 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(),
213,
2,
1,
2,
1,
4);
case (COMVariantType::VT_EMPTY):
return "";
default:
throw error(strfmt("@SYS26908", _cv.variantType()));
}
return "";
}
public Object dialog()
{
DialogRunbase dialog = super();
#resAppl
#AviFiles
#Excel
;
dlgImportFileName = dialog.addField(ExtendedTypeStr(FileNameOpen), "File name");
dlgImportFileName.value(importFileName);
dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS,"@SYS100852","*.csv","Text","*.txt"]);
return dialog;
}
public boolean getFromDialog()
{
importFileName = dlgImportFileName.value();
return super();
}
public container pack()
{
return [#CurrentVersion,#CurrentList];
}
public boolean unpack(container packedClass)
{
Version version = RunBase::getVersion(packedClass);
;
switch (version)
{
case #CurrentVersion:
[version,#CurrentList] = packedClass;
break;
default:
return false;
}
return true;
}
static void main(Args args)
{
San_ImportBudget budgetImport = new San_ImportBudget();
if (budgetImport.prompt())
budgetImport.run();
}
public void run()
{
#OCCRetryCount
if (! this.validate())
throw error("");
try
{
ttsbegin;
[filePath, fileName, fileExtension] = fileNameSplit(importFileName);
switch (fileExtension)
{
case ".xls", ".xlsx" :
info("@SYS4010033");
this.ProcessImportXLSFile();
info("@SYS329919");
break;
default :
info(strFmt("Unable to process file of type %1",fileExtension));
info("File must be .xls or .xlsx");
break;
}
ttscommit;
}
catch (Exception::Deadlock)
{
retry;
}
catch (Exception::UpdateConflict)
{
if (appl.ttsLevel() == 0)
{
if (xSession::currentRetryCount() >= #RetryNum)
{
throw Exception::UpdateConflictNotRecovered;
}
else
{
retry;
}
}
else
{
throw Exception::UpdateConflict;
}
}
catch (Exception::Error)
{
ttsAbort;
info('Failed to post, reversing updates');
}
}
private void ProcessImportXLSFile()
{
SysExcelApplication excel;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
SysExcelCell cell;
COMVariantType type;
COMVariantType hdrtype;
container header;
int row = 1;
int totalColumnCount = 0;
int currentColumn;
excel = SysExcelApplication::construct();
excel.visible(false);
workbooks = excel.workbooks();
try
{
workbooks.open(importFileName);
}
catch (Exception::Error)
{
throw error(strFmt('@GLS101746', importFileName));
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
type = cells.item(row+1,1).value().variantType();
if (type == COMVariantType::VT_EMPTY || type == COMVariantType::VT_NULL || type == COMVariantType::VT_UNKNOWN)
{
error("@SYS102111");
}
else
{
// recover header column names
for (currentColumn=1; currentColumn < 99; currentColumn++)
{
hdrtype = cells.item(row,currentColumn).value().variantType();
if (hdrtype != COMVariantType::VT_EMPTY)
{
header += cells.item(row, currentColumn).value().bStr();
totalColumnCount++;
}
else
{
break;
}
}
type = cells.item(row+1, 1).value().variantType();
while (type != COMVariantType::VT_EMPTY && type != COMVariantType::VT_NULL && type != COMVariantType::VT_UNKNOWN)
{
row++;
accountSegments = conNull();
accountSegmentNames = conNull();
budgetCode = this.COMVariant2Str(cells.item(row, 1).value(), 0);
budgetModelId = this.COMVariant2Str(cells.item(row, 2).value(), 0);
accountStructureName = this.COMVariant2Str(cells.item(row, 3).value(), 0);
transDate = str2Date(this.COMVariant2Str(cells.item(row, 4).value(), 0),213);
amount = str2num(this.COMVariant2Str(cells.item(row, 5).value(), 2));
budgetType = str2enum(budgetType, this.COMVariant2Str(cells.item(row, 6).value(), 0));
mainAccountNum = this.COMVariant2Str(cells.item(row, 7).value(), 0);
//The remaining columns are account segments and there can be any number of them
for(currentColumn = 8; currentColumn <= totalColumnCount; currentColumn++)
{
//Read the remaining segment columns as strings and put into a container
accountSegments += this.COMVariant2Str(cells.item(row, currentColumn).value(), 0);
accountSegmentNames += conPeek(header, currentColumn);
}
this.createBudgetTransactionLine();
type = cells.item(row+1, 1).value().variantType();
}
BudgetTransactionService.create(budgetTransaction);
}
excel.quit();
}
public void createBudgetTransactionLine()
{
BudgetTransaction_BudgetTransLine budgetTransaction_BudgetTransLine;
AifBudgetAccount aifBudgetAccount;
AifDimensionAttributeValue aifDimensionAttributeValue;
int dimensionColumn;
if (!budgetTransactionService)
{
budgetTransactionService = BudgetTransactionService::construct();
budgetTransaction = new BudgetTransaction();
budgetTransaction_BudgetTransHeader = budgetTransaction.createBudgetTransHeader().addNew();
budgetTransaction_BudgetTransHeader.parmBudgetTransactionCode(budgetCode);
budgetTransaction_BudgetTransHeader.parmBudgetModelId(budgetModelId);
budgetTransaction_BudgetTransLine = budgetTransaction_BudgetTransHeader.createBudgetTransLine().addNew();
}
else
{
budgetTransaction_BudgetTransLine = budgetTransaction_BudgetTransHeader.parmBudgetTransLine().addNew();
}
// Populate the budget line
budgetTransaction_BudgetTransLine.parmDate(transDate);
budgetTransaction_BudgetTransLine.parmTransactionCurrencyAmount(amount);
budgetTransaction_BudgetTransLine.parmBudgetType(budgetType);
aifBudgetAccount = budgetTransaction_BudgetTransLine.createLedgerDimension();
aifBudgetAccount.parmAccountStructure(accountStructureName);
aifBudgetAccount.parmDisplayValue("1"); //Will be changed by system to write display value
// Main Account - required
aifDimensionAttributeValue = aifBudgetAccount.createValues().addNew();
aifDimensionAttributeValue.parmName('MainAccount');
aifDimensionAttributeValue.parmValue(mainAccountNum);
// variable dimension list - optional
if (conLen(accountSegments) > 0)
{
for (dimensionColumn = 1; dimensionColumn <= conLen(accountSegments); dimensionColumn++)
{
if(conPeek(accountSegments, dimensionColumn) != "")
{
aifDimensionAttributeValue = aifBudgetAccount.parmValues().addNew();
aifDimensionAttributeValue.parmName(conPeek(accountSegmentNames, dimensionColumn));
aifDimensionAttributeValue.parmValue(conPeek(accountSegments, dimensionColumn));
}
}
}
}
End
//Only budget transaction line upload - in within Budget Transaction entries Form
void clicked()
{
BudgetTransactionLine transLine;
super();
transLine.BudgetTransactionHeader = BudgetTransactionHeader.RecId;
transLine.Date = today();
transLine.LineNumber =1;
transLine.BudgetType = BudgetType::Expense;
transLine.LedgerDimension = 5637155864;
transLine.TransactionCurrency = "KWD";
transLine.editTransactionCurrencyAmount(true,100);
transLine.insert();
BudgetTransactionLine_Ds.editAccountStructure(true,transLine,"FCCI");
BudgetTransactionLine_Ds.research();
transLine.clear();
transLine.BudgetTransactionHeader = BudgetTransactionHeader.RecId;
transLine.LineNumber =2;
transLine.Date = today();
transLine.BudgetType = BudgetType::Expense;
transLine.LedgerDimension = 5637155859;
transLine.TransactionCurrency = "USD";
transLine.editTransactionCurrencyAmount(true,200);
transLine.insert();
BudgetTransactionLine_Ds.editAccountStructure(true,transLine,"FCCI");
BudgetTransactionLine_Ds.research();
transLine.clear();
transLine.BudgetTransactionHeader = BudgetTransactionHeader.RecId;
transLine.LineNumber =3;
transLine.Date = today();
transLine.BudgetType = BudgetType::Expense;
transLine.LedgerDimension = 5637155862;
transLine.TransactionCurrency = "USD";
transLine.editTransactionCurrencyAmount(true,300);
transLine.insert();
BudgetTransactionLine_Ds.editAccountStructure(true,transLine,"FCCI");
BudgetTransactionLine_Ds.research();
}
