every year, ax user finance must upload the budget, this code below is how to upload budget to ax,
this is the steps:
- fiscal calendar, check is the new year calendar is already created
- remember to check budg_3 number sequences, do not check the “continue”, reset every year
- create budget model 2017 at budget->setup->budget models
- check the session date before upload, recomended to set 1/1/[current budget year]
- goto system administrator -> periodic -> upload data -> budget 2017, pick the files, and upload
- goto budgeting -> common -> budget register entries
- check the uploaded budget, if theres wrong budget, edit it, or, delete all budget and reupload
- if the uploaded budget is ok, then, click update budget balances
- if completed, and there was a corrections, do the manual corrections in budgeting -> common -> draft budget revisions
below is the source code:
static client void uploadBudget(Filename filename, int start)
{
#WinApi
#File
SysExcelApplication application = SysExcelApplication::construct();
SysExcelWorkbooks workbooks = application.workbooks();
SysExcelWorkbook workbook;
SysExcelWorksheets workSheets;
SysExcelWorksheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
int i,a;
BudgetTransactionHeader budgetheader,budgetheadercek;
BudgetTransactionCode _budgettranscode;
AxBudgetTransactionLine _axbudgetline;
MainAccountNum ledgeracc;
str modelid1,budgettransnum,dept1,comp1;
//str 10 dept;
Ledger _ledger;
MainAccount _mainaccount;
RecId Ledgerdimensions;
date transdate;
Amount amt;
BudgetModelId _modelid;
DimensionFinancialTag _dimfin;
DimensionValue dept;
LedgerName _comp;
;
// startLengthyOperation();
if (!WinAPI::fileExists(filename))
throw error(strfmt("@SYS109820", filename));
if (workbooks.open(filename, false /*Update links*/, true /*Read only*/))
{
workbook = workbooks.item(1);
workSheets = workbook.worksheets();
workSheet = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
cells = workSheet.cells();
i = start + 1;
_comp = curExt();
select * from _ledger where _ledger.Name==_comp;
try
{
ttsBegin;
while (true)
{
// startLengthyOperation();
progress.setCaption('Progress');
progress.setText(strfmt("Row %1", i));
progress.setTotal(5500);
progress.setCount(i, 1);
ledgeracc='';
//budget account
ledgeracc=strLRTrim(cells.item(i,4).value().bStr());
if (ledgeracc=='')
{
ledgeracc=strLRTrim(num2str(cells.item(i,4).value().double(),20,0,0,0));
}
if(ledgeracc=='' || ledgeracc=='0')
break;
//Company, mendapatkan kode ledger company
//harusnya tidak usah, agar tidak overlap company
//gunakan curExt()
//if(comp1 != strLRTrim(cells.item(i,1).value().bStr()))
//{
// comp1=strLRTrim(cells.item(i,1).value().bStr());
// _comp=comp1;
// select * from _ledger where _ledger.Name==_comp;
//}
// info(int2str(i)+' '+ledgeracc+' - '+int642str(_ledger.RecId));
//dia akan cek main account,
select * from _mainaccount where _mainaccount.MainAccountId==ledgeracc && _mainaccount.LedgerChartOfAccounts==_ledger.RecId;
//budget date
transdate=cells.item(i,5).value().date();
//amount
amt=cells.item(i,7).value().double();
//dept
dept1=strLRTrim(cells.item(i,3).value().bStr());
if(dept1=='')
dept1=strLRTrim(num2str(cells.item(i,3).value().double(),20,0,0,0));
//model
modelid1=strLRTrim(cells.item(i,2).value().bStr());
if (modelid1=='')
modelid1=num2str(cells.item(i,2).value().double(),20,0,0,0);
if (
_modelid!=strLRTrim(modelid1)
)
{
_modelid=strLRTrim(modelid1);
//fungsi di bawah hanya ngecek apakah mode dan primary ledger sudah pernah diupload
//masalahnya dia hanya cek baris data awal saja
//jadi sebenarnya tidak berguna juga, jadi gunakan versi 2 saja
//versi 1
//if(i==3)
//{
// select firstonly * from budgetheadercek where budgetheadercek.BudgetModelId==_modelid &&
// budgetheadercek.PrimaryLedger==_ledger.RecId ;
// if (budgetheadercek)
// {
// info('Model ID '+_modelid+' already uploaded!');
// break;
// }
//}
select firstonly * from budgetheadercek where budgetheadercek.BudgetModelId==_modelid &&
budgetheadercek.PrimaryLedger==_ledger.RecId ;
if (!budgetheadercek)
//info('Model ID '+_modelid+' already uploaded!');
//break;
//do nothing lah, toh ga create budget baru dan cek line berapa
{
select _budgettranscode where _budgettranscode.Name=='ORI';
budgettransnum=NumberSeq::newGetNum(BudgetParameters::numRefBudgetTransactionId()).num();
budgetheader.BudgetModelId=_modelid;
budgetheader.BudgetSubModelId=_modelid;
budgetheader.BudgetModelDataAreaId=_comp;
budgetheader.BudgetTransactionCode=_budgettranscode.RecId;//5637144826;
budgetheader.BudgetTransactionType=BudgetTransactionType::OriginalBudget;
budgetheader.PrimaryLedger=_ledger.RecId;
budgetheader.Date=transdate;
budgetheader.TransactionStatus=BudgetTransactionStatus::Draft;
budgetheader.TransactionNumber=budgettransnum;
budgetheader.insert();
}
}
Ledgerdimensions=UploadClass::getLedgerDimension(ledgeracc, dept1);
_axbudgetline=new AxBudgetTransactionLine();
_axbudgetline.parmBudgetTransactionHeader(budgetheader.RecId);
_axbudgetline.parmAccountingCurrencyAmount(amt);
_axbudgetline.parmBudgetType(BudgetType::Expense);
_axbudgetline.parmDate(transdate);
_axbudgetline.parmLedgerDimension(Ledgerdimensions);
//entah kenapa iverson by pass kolom ini
_axbudgetline.parmTransactionCurrency('IDR');
_axbudgetline.parmTransactionCurrencyAmount(amt);
_axbudgetline.save();
i++;
}
ttsCommit;
info('Upload done');
}
catch(Exception::Error)
{
info(strFmt("Catch Error on line %1",i));
}
info('End of Job');
}
}
download link for xlsx template
download link for completed source code: