public void processReport()
{
CompanyInfo companyInfo;
List itemIdList = new List(Types::String), itemGroupList = new List(Types::String);
ALM_InventFormulaDynParameter _ALM_InventFormulaDynParameter;
InventTableModule _inventTableModule;
InventTableExpanded _InventTableExpanded;
contract = this.parmDataContract() as Alm_InventFormulaContract;
fromDate = contract.parmFromDate();
toDate = contract.parmToDate();
itemIdList = contract.parmItemId();
itemGroupList = contract.parmItemGroup();
testItemId = itemIdList.toString();
testItemId = strRem(testItemId,"<");
testItemId = strRem(testItemId,">");
testItemGroup = itemGroupList.toString();
testItemGroup = strRem(testItemGroup,"<");
testItemGroup = strRem(testItemGroup,">");
//deleting data in stagging table
ttsbegin;
delete_from inventFormulaStagging where inventFormulaStagging.CreatedBy == curUserId();
ttscommit;
while select _ALM_InventFormulaDynParameter
{
if(_ALM_InventFormulaDynParameter.ALM_ColumnSource == ALM_ColumnSource::InventTransaction)
{
if(_ALM_InventFormulaDynParameter.InventTransPostingType == InventTransPostingType::Financial)
this.insertingIntoStaggingInventTrans(_ALM_InventFormulaDynParameter);
else if(_ALM_InventFormulaDynParameter.InventTransPostingType == InventTransPostingType::Physical)
this.insertingIntoStaggingInventTransPhysical(_ALM_InventFormulaDynParameter);
}
else if(_ALM_InventFormulaDynParameter.ALM_ColumnSource == ALM_ColumnSource::SalesTransaction)
this.insertingIntoStaggingCustomer(_ALM_InventFormulaDynParameter);
}
select firstonly forupdate inventFormulaStagging;
{
if(inventFormulaStagging)
{
inventFormulaStagging.CompanyName = CompanyInfo::find().Name;
inventFormulaStagging.CompanyLogo = Formletter::companyLogo();
ttsbegin;
inventFormulaStagging.update();
ttscommit;
}
}
update_recordset inventFormulaStagging
setting ItemName = _InventTableExpanded.ProductName,
UOM = _inventTableModule.UnitId,
ItemGroup = _InventTableExpanded.ItemGroupId
join ProductName,ItemGroupId from _InventTableExpanded
where _InventTableExpanded.ItemId == inventFormulaStagging.ItemId
join UnitId from _inventTableModule
where _inventTableModule.ItemId == _InventTableExpanded.ItemId && _inventTableModule.ModuleType == ModuleInventPurchSales::Invent;
while select ItemId,count(RecId) from inventFormulaStagging
group by inventFormulaStagging.ItemId
where inventFormulaStagging.CreatedBy == curUserId()
{
this.updateOpenBalance(inventFormulaStagging.ItemId,0);
}
}
public void insertingIntoStaggingInventTransPhysical(ALM_InventFormulaDynParameter _ALM_InventFormulaDynParameter)
{
Alm_InventFormulaView inventFormulaView;
ALM_InventFormulaSalesView inventFormulaSalesView;
InventTransOrigin inventTransOrigin;
InventTransPosting inventTransPosting;
DimensionAttributeValueCombination combination;
RetailTransactionSalesTrans salesTrans;
InventTable inventTable;
InventTrans inventTrans;
TransDate openBalDate = fromDate - 1;
Query query;
QueryBuildDataSource qbds;
QueryBuildFieldList fldList_InventFormulaStagging;
Map fieldMapping;
query = new Query();
qbds = query.addDataSource(tableNum(ALM_InventFormulaViewPhysical));
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, DatePhysical)).value(SysQuery::range(fromDate,toDate));
if(testItemId)
{
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ItemId)).value(testItemId);
}
//if(testItemGroup)
//{
// qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ItemGroup)).value(testItemGroup);
//}
if(_ALM_InventFormulaDynParameter.Account !="")
{
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, MainAccountValue)).value(_ALM_InventFormulaDynParameter.Account);
}
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, InventTransPostingType)).value(SysQuery::value( _ALM_InventFormulaDynParameter.InventTransPostingType));
if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType != ALM_InventFromulaInventTransType::All)
{
if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType == ALM_InventFromulaInventTransType::InventTransaction)
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ReferenceCategory)).value(SysQuery::value( InventTransType::InventTransaction));
if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType == ALM_InventFromulaInventTransType::ProdLine)
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ReferenceCategory)).value(SysQuery::value( InventTransType::ProdLine));
if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType == ALM_InventFromulaInventTransType::Purch)
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ReferenceCategory)).value(SysQuery::value( InventTransType::Purch));
if(_ALM_InventFormulaDynParameter.ALM_InventFromulaInventTransType == ALM_InventFromulaInventTransType::Sales)
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, ReferenceCategory)).value(SysQuery::value( InventTransType::Sales));
}
if(_ALM_InventFormulaDynParameter.ALM_InventFormulaDynInOutStatus != ALM_InventFormulaDynInOutStatus::Both)
{
if (_ALM_InventFormulaDynParameter.ALM_InventFormulaDynInOutStatus == ALM_InventFormulaDynInOutStatus::Purchased)
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, StatusReceipt)).value(SysQuery::value( StatusReceipt::Purchased));
else if(_ALM_InventFormulaDynParameter.ALM_InventFormulaDynInOutStatus == ALM_InventFormulaDynInOutStatus::Sold)
qbds.addRange(fieldNum(ALM_InventFormulaViewPhysical, StatusIssue)).value(SysQuery::value( StatusIssue::Sold));
}
qbds.addGroupByField(fieldNum(ALM_InventFormulaViewPhysical, ItemId));
qbds.fields().clearFieldList();
fldList_InventFormulaStagging = qbds.fields();
fldList_InventFormulaStagging = qbds.fields();
fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, ItemId));
//fldList_InventFormulaStagging.addField(fieldNum(Alm_InventFormulaView, Account));
fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, CostAmountPosted), SelectionField::Sum);
fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, CostAmountAdjustment), SelectionField::Sum);
fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, defaultvalue), SelectionField::Sum);
fldList_InventFormulaStagging.addField(fieldNum(ALM_InventFormulaViewPhysical, Qty), SelectionField::Sum);
fldList_InventFormulaStagging.dynamic(QueryFieldListDynamic::No);
fieldMapping = new Map(Types::String, Types::Container);
fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, ItemId), [qbds.uniqueid(), fieldstr(ALM_InventFormulaViewPhysical, ItemId)]);
fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, CostAmount), [qbds.uniqueid(), SysComputedColumn::sum(fieldstr(ALM_InventFormulaViewPhysical, CostAmountPosted))]);
fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, CostAmountAdj), [qbds.uniqueid(), SysComputedColumn::sum(fieldstr(ALM_InventFormulaViewPhysical, CostAmountAdjustment))]);
fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, Sequence), [qbds.uniqueid(), SysComputedColumn::sum(fieldstr(ALM_InventFormulaViewPhysical, defaultvalue))]);
fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, Qty), [qbds.uniqueid(), SysComputedColumn::sum(fieldstr(ALM_InventFormulaViewPhysical, Qty))]);
// fieldmapping.insert(fieldstr(ALM_InventFormulaDynStagging, Account), [qbds.uniqueid(), "'dfsdfdsf' as Account"]);
query::insert_recordset(inventFormulaStagging, fieldMapping, query);
update_recordset inventFormulaStagging
setting Account = _ALM_InventFormulaDynParameter.Account,
Sequence = _ALM_InventFormulaDynParameter.Sequence,
Description = _ALM_InventFormulaDynParameter.Description,
CostAmount = inventFormulaStagging.CostAmount + inventFormulaStagging.CostAmountAdj,
ALM_InventFormulaDynInOut = _ALM_InventFormulaDynParameter.ALM_InventFormulaDynInOut
where inventFormulaStagging.Sequence <= -1 && inventFormulaStagging.CreatedBy == curUserId();
}
