How to build dynamic query for Budget Amount in X++ using Classes

Dear Friends,

public AmountCur getBudgetAmount(Transdate _fromDate,Transdate _toDate,container _mainAccount,container _dim = conNull())

{

QueryBuildDataSource    qbdsLine,qbdsHeader,qbdsViewActual,qbdsAttrActual,qbdsViewMain,qbdsAttrMain;    QueryRun                qRun;    BudgetTransactionLine   budgetLine;    AmountCur               amount;    #define.ProductLines(‘ProductLines’)    #define.MainAccount(‘MainAccount’)    #define.Fy(“FY2015”)    #define.Main(“Main”)    ;    queryBudget = new Query();    qbdsHeader = queryBudget.addDataSource(tableNum(BudgetTransactionHeader));    qbrHeader = qbdsHeader.addRange(fieldNum(BudgetTransactionHeader,BudgetModelId));    qbrHeader.value(#Fy);
qbdsLine = qbdsHeader.addDataSource(tableNum(BudgetTransactionLine));    qbdsLine.addSelectionField(fieldNum(BudgetTransactionLine,TransactionCurrencyAmount),SelectionField::Sum);    qbdsLine.addLink(fieldNum(BudgetTransactionHeader,RecId),fieldNum(BudgetTransactionLine,BudgetTransactionHeader));    qbdsLine.joinMode(joinMode::InnerJoin);    qbrLine = qbdsLine.addRange(fieldNum(BudgetTransactionLine,Date));
qbrLine.value(SysQuery::range(_fromdate,_toDate)); //set Value Date
qbdsViewActual = qbdsLine.addDataSource(tableNum(DimensionAttributeLevelValueAllView));    qbdsViewActual.joinMode(joinMode::ExistsJoin);    qbdsViewActual.addLink(fieldNum(BudgetTransactionLine,ledgerdimension),fieldNum(DimensionAttributeLevelValueAllView,ValueCombinationRecId));    qbrViewActual = qbdsViewActual.addRange(fieldNum(DimensionAttributeLevelValueAllView,DisplayValue));

if (_dim != conNull())

{

qbrViewActual.value(con2Str(_dim)); //set Value Dimension

}
qbdsAttrActual = qbdsViewActual.addDataSource(tableNum(DimensionAttribute));    qbdsAttrActual.joinMode(joinMode::ExistsJoin);    qbdsAttrActual.addLink(fieldNum(DimensionAttributeLevelValueAllView,DimensionAttribute),fieldNum(DimensionAttribute,RecId));    qbrAttrActual = qbdsAttrActual.addRange(fieldNum(DimensionAttribute,Name));    qbrAttrActual.value(#ProductLines);
qbdsViewMain = qbdsLine.addDataSource(tableNum(DimensionAttributeLevelValueAllView));    qbdsViewMain.name(#Main);    qbdsViewMain.joinMode(joinMode::ExistsJoin);    qbdsViewMain.addLink(fieldNum(BudgetTransactionLine,ledgerdimension),fieldNum(DimensionAttributeLevelValueAllView,ValueCombinationRecId));    qbrViewMain = qbdsViewMain.addRange(fieldNum(DimensionAttributeLevelValueAllView,DisplayValue));    qbrViewMain.value(con2Str(_mainAccount));//set Value MainAccount
qbrViewMain = qbdsAttrActual.addRange(fieldNum(DimensionAttributeLevelValueAllView,DisplayValue));    qbdsAttrMain = qbdsViewMain.addDataSource(tablenum(DimensionAttribute));    qbdsAttrMain.joinMode(joinMode::ExistsJoin);    qbdsAttrMain.addLink(fieldNum(DimensionAttributeLevelValueAllView,DimensionAttribute),fieldNum(DimensionAttribute,RecId),#Main);
qbrAttrMain = qbdsAttrActual.addRange(fieldNum(DimensionAttribute,Name));    qbrAttrMain.value(#MainAccount);
qRun = new QueryRun(queryBudget);

while(qRun.next())

{

budgetLine =  qRun.get(tableNum(BudgetTransactionLine));        amount = budgetLine.TransactionCurrencyAmount;

}
return amount;

}

 

Thanks

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s