Adding new field in InventValueReportTmpLine Table and InventValueReportTmpLine sysQuery
then add below lines in InventValueReportPopulateItem Class
protected Map mapReportLineToInventTable()
{
Map map;
map = new map(Types::Integer, Types::Integer);
map.insert(fieldNum(InventValueReportTmpLine, ResourceId), fieldNum(InventTable, ItemId));
map.insert(fieldNum(InventValueReportTmpLine, ShipmentMode), fieldNum(InventTable, ShipmentMode));//-- newly added for filtering
return map;
}
-----------------------------------
protected void buildTransactionsWithDynamicQuery()
q = new Query();
qbdsInventTable = q.addDataSource(tableNum(InventTable));
qbdsInventTable.orderMode(OrderMode::GroupBy);
qbdsInventTable.addGroupByField(fieldNum(InventTable, ItemType));
qbdsInventTable.addGroupByField(fieldNum(InventTable, ShipmentMode));//-- newly added for filtering
if (inventValueReport.ViewResourceId || inventValueReport.ViewResourceIdTotal)
{
qbdsInventTable.addGroupByField(fieldNum(InventTable, ItemId));
}
-----------------
protected void buildTransactionsWithDynamicQuery()
{
Query q;
QueryRun qr;
QueryBuildDataSource qbds;
QueryBuildDataSource qbdsInventTable;
QueryBuildDataSource qbdsInventView;
InventItemGroupItem inventItemGroupItem;
InventTable inventTable;
EcoResProductDimensionGroupProduct productDimensionGroupProduct;
EcoResStorageDimensionGroupItem storageDimensionGroupItem;
EcoResTrackingDimensionGroupItem trackingDimensionGroupItem;
InventValueReportView inventView;
InventDim inventDim, fetchedInventDim;
InventValueReportTmpLine tmpReportLine;
InventDimParm inventDimParmProduct;
InventDimParm inventDimParmStorage;
InventDimParm inventDimParmTracking;
InventDimParm financialInventDimParm;
InventDimParm populateInventDimParm;
InventDimParm inventDimParmFilter;
boolean updateFinancialInventDimParm;
boolean joinInventDim;
boolean joinProductDimensionGroupProduct;
boolean joinStorageDimensionGroupProduct;
boolean joinTrackingDimensionGroupProduct;
boolean joinInventItemGroupItem;
boolean isInventDimParmEqual = true;
// Group by dimensions (view || total)
populateInventDimParm = InventDimParm::orParms(inventValueReport.viewInventDimParm(), inventValueReport.totalInventDimParm());
joinInventDim = this.isRangeSetOnReportLine(this.MapReportLineToInventDim(), inventDimParmFilter) ||
populateInventDimParm.anyProductDimensionFlagSet() ||
populateInventDimParm.anyStorageDimensionFlagSet() ||
populateInventDimParm.anyTrackingDimensionFlagSet();
joinInventItemGroupItem = inventValueReport.ViewResourceGroup || inventValueReport.ViewResourceGroupTotal;
joinProductDimensionGroupProduct = populateInventDimParm.anyProductDimensionFlagSet();
joinStorageDimensionGroupProduct = populateInventDimParm.anyStorageDimensionFlagSet();
joinTrackingDimensionGroupProduct = populateInventDimParm.anyTrackingDimensionFlagSet();
q = new Query();
qbdsInventTable = q.addDataSource(tableNum(InventTable));
qbdsInventTable.orderMode(OrderMode::GroupBy);
qbdsInventTable.addGroupByField(fieldNum(InventTable, ItemType));
qbdsInventTable.addGroupByField(fieldNum(InventTable, ShipmentMode));//-- newly added for filtering
if (inventValueReport.ViewResourceId || inventValueReport.ViewResourceIdTotal)
{
qbdsInventTable.addGroupByField(fieldNum(InventTable, ItemId));
}
this.copyRangesFromContractQuery(this.MapReportLineToInventTable(), qbdsInventTable);
qbds = qbdsInventTable;
if (joinInventItemGroupItem)
{
qbds = this.joinInventItemGroupItem(qbds, qbdsInventTable);
}
if (joinProductDimensionGroupProduct)
{
qbds = this.joinProductDimensionGroupProduct(qbds, qbdsInventTable);
}
if (joinStorageDimensionGroupProduct)
{
qbds = this.joinStorageDimensionGroupProduct(qbds, qbdsInventTable);
}
if (joinTrackingDimensionGroupProduct)
{
qbds = this.joinTrackingDimensionGroupProduct(qbds, qbdsInventTable);
}
qbdsInventView = qbds.addDataSource(tableNum(InventValueReportView));
qbdsInventView.addLink(fieldNum(InventTable, ItemId), fieldNum(InventValueReportView, ItemId), qbdsInventTable.name());
switch (inventValueReport.SelectDateRange)
{
case InventValueReportSelectDateRange::TransDate:
qbdsInventView.addRange(fieldNum(InventValueReportView, TransDate)).value(queryRange(this.getActualFromDate(), dateMax()));
break;
case InventValueReportSelectDateRange::TransBeginTime:
qbdsInventView.addRange(fieldNum(InventValueReportView, TransBeginTime)).value(queryRange(this.getActualFromDate(), DateTimeUtil::maxValue()));
break;
}
qbdsInventView.orderMode(OrderMode::GroupBy);
if (this.parmResourceId())
{
qbdsInventView.addRange(fieldNum(InventValueReportView, ItemId)).value(queryValue(this.parmResourceId()));
}
if (joinInventDim)
{
qbds = this.joinInventDim(qbdsInventView, populateInventDimParm);
}
// select sum(field)
qbdsInventView.addSelectionField(fieldNum(InventValueReportView, Qty), SelectionField::Sum);
qbdsInventView.addSelectionField(fieldNum(InventValueReportView, Amount), SelectionField::Sum);
// InventView
qbdsInventView.addGroupByField(fieldNum(InventValueReportView, TransDate));
if (inventValueReport.DetailLevel == TotalTrans::Trans)
{
qbdsInventView.addGroupByField(fieldNum(InventValueReportView, Voucher));
qbdsInventView.addGroupByField(fieldNum(InventValueReportView, Reference));
}
// Group by dates regardless of DetailLevel value, so that the records with TransDate > ToDate could be deleted later, in the run() method
if (inventValueReportContracts.parmViewTransBeginTime() ||
inventValueReport.SelectDateRange == InventValueReportSelectDateRange::TransBeginTime)
{
qbdsInventView.addGroupByField(fieldNum(InventValueReportView, TransBeginTime));
}
qbdsInventView.addGroupByField(fieldNum(InventValueReportView, ReferenceCategory));
qbdsInventView.addGroupByField(fieldNum(InventValueReportView, InventTransPostingType));
qbdsInventView.addGroupByField(fieldNum(InventValueReportView, IsPosted));
if (inventValueReportContracts.parmViewCOGSFinancial() || inventValueReportContracts.parmViewCOGSTotal())
{
qbdsInventView.addGroupByField(fieldNum(InventValueReportView, PostingTypeOffset));
}
this.copyRangesFromContractQuery(this.mapReportLineToReportView(), qbdsInventView);
qr = new QueryRun(q);
while (qr.next())
{
if (qr.changed(tableNum(InventTable)))
{
inventTable = qr.get(tableNum(InventTable));
this.insertReportLines();
}
if (joinInventItemGroupItem && qr.changed(tableNum(InventItemGroupItem)))
{
inventItemGroupItem = qr.get(tableNum(InventItemGroupItem));
this.insertReportLines();
}
if (joinProductDimensionGroupProduct && qr.changed(tableNum(EcoResProductDimensionGroupProduct)))
{
productDimensionGroupProduct = qr.get(tableNum(EcoResProductDimensionGroupProduct));
inventDimParmProduct.data(EcoResProductDimGroupSetupCache::financialInventoryEnabled(productDimensionGroupProduct.ProductDimensionGroup));
updateFinancialInventDimParm = true;
}
if (joinStorageDimensionGroupProduct && qr.changed(tableNum(EcoResStorageDimensionGroupItem)))
{
storageDimensionGroupItem = qr.get(tableNum(EcoResStorageDimensionGroupItem));
inventDimParmStorage.data(EcoResStorageDimGroupSetupCache::financialInventoryEnabled(storageDimensionGroupItem.StorageDimensionGroup));
updateFinancialInventDimParm = true;
}
if (joinTrackingDimensionGroupProduct && qr.changed(tableNum(EcoResTrackingDimensionGroupItem)))
{
trackingDimensionGroupItem = qr.get(tableNum(EcoResTrackingDimensionGroupItem));
inventDimParmTracking.data(EcoResTrackingDimGroupSetupCache::financialInventoryEnabled(trackingDimensionGroupItem.TrackingDimensionGroup));
updateFinancialInventDimParm = true;
}
if (qr.changed(tableNum(InventValueReportView)))
{
inventView = qr.get(tableNum(InventValueReportView));
}
if (updateFinancialInventDimParm)
{
financialInventDimParm.data(inventDimParmProduct);
financialInventDimParm = InventDimParm::orParms(financialInventDimParm, inventDimParmStorage);
financialInventDimParm = InventDimParm::orParms(financialInventDimParm, inventDimParmTracking);
isInventDimParmEqual = InventDimParm::isInventDimParmEqual(inventDimParmFilter, InventDimParm::andParms(inventDimParmFilter, financialInventDimParm));
}
if (isInventDimParmEqual && joinInventDim && (!fetchedInventDim || qr.changed(tableNum(InventDim))))
{
fetchedInventDim = qr.get(tableNum(InventDim));
// Fields might not have been selected on the specified buffers, or might have been updated since selection
fetchedInventDim.checkInvalidFieldAccess(false);
// Clear non-financial dimensions (qr.next() is restoring the values)
inventDim.data(fetchedInventDim);
inventDim.clearNotSelectedDim(financialInventDimParm);
}
updateFinancialInventDimParm = false;
if (isInventDimParmEqual && (inventView.Qty || inventView.Amount))
{
// Update transaction lines
if (inventValueReport.DetailLevel == TotalTrans::Trans)
{
if (this.selectedDate(inventView) <= this.getActualToDate())
{
tmpReportLine = this.findReportLine(InventValueReportLineType::Transaction, inventTable, inventItemGroupItem, inventView, inventDim);
this.updateReportLineBalance(tmpReportLine, 1, inventTable, inventView);
this.updateReportLinePL(tmpReportLine, inventTable, inventView);
this.updateReportLine(tmpReportLine);
}
// Update beginning balance
if (inventValueReport.IncludeBeginningBalance)
{
tmpReportLine = this.findReportLine(InventValueReportLineType::BeginningBalance, inventTable, inventItemGroupItem, inventView, inventDim);
this.updateReportLineBalance(tmpReportLine, -1, inventTable, inventView);
this.updateReportLine(tmpReportLine);
}
}
else
{
// Update ending balance
tmpReportLine = this.findReportLine(InventValueReportLineType::EndingBalance, inventTable, inventItemGroupItem, inventView, inventDim);
if (this.selectedDate(inventView) > this.getActualToDate())
{
this.updateReportLineBalance(tmpReportLine, -1, inventTable, inventView);
}
else
{
this.updateReportLinePL(tmpReportLine, inventTable, inventView);
}
this.updateReportLine(tmpReportLine);
}
}
}
}
