Dynamic Query creation and execution

X++ code to create dynamic query :
EcoResProductTranslation   ecores;
    InventTable inventtable;
     Query query = new Query();
    QueryRun    qr;
    QueryBuildDataSource    invent,ecorestrans;
    //SysTableLookup sysTableLookup = sysTableLookup::newParameters(tableNum(EcoResProductTranslation), this);
    ecorestrans = query.addDataSource(tableNum(EcoResProductTranslation));
    invent  =   ecorestrans.addDataSource(tableNum(InventTable));
    invent.addLink(fieldNum(InventTable,Product),fieldNum(EcoResProductTranslation,Product));
    qr=new QueryRun(query);
    while(qr.next())
    {
        ecores = qr.get(tableNum(EcoResProductTranslation));
        inventtable = qr.get(tableNum(InventTable));
        info(strFmt("%1-%2",inventtable.ItemId,ecores.Name));
    }
    pause;

Below is the select query statement
EcoResProduct   ecores;
    EcoResProductTranslation    ecorestrans;
    InventTable inventtable;
    while select * from ecorestrans
    join *  from inventtable   where  inventtable.Product   ==  ecorestrans.Product
    {
       info(strFmt("%1-%2",inventtable.ItemId,ecorestrans.Name));
    } 

How to attach Payment Schedule to Project

1.    Create a new project . Click Manage >> Onaccount - transaction >>Click New

2.     Create an on-account for the project.  - Assign description and sales price. 

3.      Assign payment schedule to project contract.  Go to Funding sources tab in contract , click Details. Assign the Payment schedule in Payment tab 

4.      Create invoice proposal - select - Project Contract and Project , you get the Onaccount line. 

Click OK on that form . 

On Invoice Proposal Form, Details section click Payment Schedule.   Check payment schedule 

6.      Post the invoice proposal

Click OK

7.      Go to the customer specified on Project Contract – Funding source, open transactions for that customer, find the transaction posted

Click Open

Observe the payment schedule generated in CustTransOpen

AX 5.0,4.0 Reports

http://www.slideshare.net/magive249/report-microsoft-dynamics-ax

Batch Process - Sample Codes

public class InventRelOrderPick_AutoProcessSalesBatch extends RunBaseBatch {     InventRelOrderPick_AutoProcessSalesData     inventRelOrderPick_AutoProcessSalesData;     #DEFINE.CurrentVersion(1) }	public boolean validate(Object _calledFrom = null) {     if (false)         return checkFailed("");     return true; }
public boolean canGoBatchJournal() {     return true; }	public static InventRelOrderPick_AutoProcessSalesBatch construct() {     return new InventRelOrderPick_AutoProcessSalesBatch(); }
public container pack() {     //return conNull();     return [#CurrentVersion]; }	public static void main(Args _args) {     InventRelOrderPick_AutoProcessSalesBatch autoProcessSales;     autoProcessSales = InventRelOrderPick_AutoProcessSalesBatch::construct();     autoProcessSales.run(); }
/// <summary> ///    Contains the code that does the actual job of the class. /// </summary> public void run() {     MenuFunction menuFunction;     #OCCRetryCount     if (! this.validate())         throw error("");     try     {         ttsbegin;         //inventRelOrderPick_AutoProcessSalesData = inventRelOrderPick_AutoProcessSalesData::construct();         menuFunction = new MenuFunction(menuItemActionStr(InventRelOrderPick_AutoProcessSalesData), MenuItemType::Action);         menuFunction.run();         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;         }     } }	public boolean unpack(container packedClass) {     boolean         ret;     Version         version = RunBase::getVersion(packedClass);     container       base;     ;     switch (version)     {         case #CurrentVersion:         {             [version, base] = packedClass;             ret = true;             break;         }         default :             return false;     }     return ret; }

======================================================================

class InventRelOrderPick_AutoProcessSalesData  extends InventReleaseOrderPickingForm {     Map     numberOfOrderLinesDeliverableMap;     Set     salesLineRecIdSet;     Map     qtyWMSOrderMap;     Map     qtyRemainToDeliverMap; }

protected boolean allOrdersDeliverable(     ItemId _itemId,     InventDim _inventDim,     CustAccount _custId = "",     date _dateReq = systemDateGet()) {     PdsSellableDays     pdsDays;     PdsExpiredPhysical  expiredPhysical;     InventSum           inventSum = this.findInventOnHandQty(_itemId, _inventDim);     if(#PdsShelfEnabled)     {         pdsDays = PdsCustSellableDays::findValidRec(_itemId, _custId, _inventDim).SellableDays;         expiredPhysical = conPeek(inventSum.pdsCustExpiredPhysical(pdsDays, _dateReq, _inventDim), 1);     }     if (inventSum.AvailPhysical - expiredPhysical < inventSum.OnOrder)     {         return false;     }     return true; }

void bldInventReleaseOrderPickingTmp() {     #avifiles     SalesTable              salesTable;     SalesLine               salesLine;     InventDim               inventDimSalesLine;     InventDimParm           inventDimParm;     InventQty               qtyPhysicalInvent;     InventQty               qtyOnPickWMSTotal;     InventQty               qtyReservedOrdered;     InventQty               qtyRemainToDeliver;     Query                   query1,query = new Query(queryStr(SalesReleaseOrderPickingAuto));     QueryRun                queryRun;     QueryBuildDataSource    qbdsSalesLine;     QueryBuildRange         qbrDates, qbrShipComplete, qbrCreditHoldStatus, qbrPastDueStatus, qbrSalesId;     ttsbegin;     delete_from inventReleaseOrderPickingTmp;     qbdsSalesLine = query.dataSourceTable(tableNum(SalesLine));     if (qbdsSalesLine)     {         qbrDates = qbdsSalesLine.findRange(fieldNum(SalesLine,ConfirmedDlv));         if (qbrDates)         {             dateRange = qbrDates.value();         }     }     numberOfOrderLinesDeliverableMap = new Map(Types::String,Types::Integer);     salesLineRecIdSet                = new Set(Types::Int64);     queryRun = new queryRun(query);     while (queryRun.next())     {         salesLine = queryRun.get(tableNum(salesLine));         salesLineRecIdSet.add(salesLine.RecId);         qtyOnPickWMSTotal   = 0;         qtyRemainToDeliver  = 0;         inventDimSalesLine = this.findInventDim(salesLine.InventDimId);         inventDimParm.data(this.initInventDimParm(inventDimSalesLine));         qtyPhysicalInvent = this.findQtyPhysicalInvent(salesLine.ItemId,inventDimSalesLine);         if (qtyPhysicalInvent > 0 && deductUnReserved)         {             qtyOnPickWMSTotal   = this.findQtyOnPick(salesLine.ItemId,inventDimSalesLine);             qtyPhysicalInvent    -= qtyOnPickWMSTotal;         }         if (qtyPhysicalInvent > 0)         {             qtyReservedOrdered = this.findQtyReservedOrdered(salesLine.InventTransId);             qtyRemainToDeliver = this.findQtyRemainToDeliver(salesLine, qtyReservedOrdered);             if (qtyRemainToDeliver > 0)             {                 inventReleaseOrderPickingTmp.TransRecId             = salesLine.RecId;                 inventReleaseOrderPickingTmp.TransType              = InventReleaseOrderPickingType::Sales;                 inventReleaseOrderPickingTmp.OrderId                = salesLine.SalesId;                 inventReleaseOrderPickingTmp.CustClassificationId   = salesLine.custClassification();                 inventReleaseOrderPickingTmp.AllOrdersDeliverable   = qtyReservedOrdered ? false : this.allOrdersDeliverable(                                                                                                         salesLine.ItemId,                                                                                                         inventDimSalesLine,                                                                                                         salesLine.CustAccount,                                                                                                         salesLine.ReceiptDateConfirmed                                                                                                             ? salesLine.ReceiptDateConfirmed                                                                                                             : salesLine.ReceiptDateRequested);                 inventReleaseOrderPickingTmp.NeedAllocation         = this.needAllocation(salesLine.ItemId, inventDimSalesLine,salesLine);                 inventReleaseOrderPickingTmp.ProductionOrderExist   = this.productionOrderLineExist(salesLine.ItemId, inventDimSalesLine.InventDimId, dateRange);                 inventReleaseOrderPickingTmp.TransferOrderExist     = this.transferOrderExist(salesLine.ItemId, inventDimSalesLine.InventDimId,dateRange);                 inventReleaseOrderPickingTmp.ShipComplete           = salesLine.ShipComplete;                 inventReleaseOrderPickingTmp.insert();                 this.updateNumberOfOrderLinesDeliverableMap(inventReleaseOrderPickingTmp.OrderId,inventReleaseOrderPickingTmp.AllOrdersDeliverable);             }         }     }     this.deliverPossiblePctCalc();     ttscommit; }

/// <summary> /// Builds the temporary table /// </summary>  public void buildData(boolean  _deductUnreserved) {     InventReleaseOrderPickingTmp    inventReleaseOrderPickingTmplocal;     select firstonly inventReleaseOrderPickingTmplocal;     this.parmDeductUnreserved(_deductUnreserved);     this.parmInventReleaseOrderPickingTmp(inventReleaseOrderPickingTmplocal);     this.bldInventReleaseOrderPickingTmp();     //All orders that have status reserved physical     this.updateReservedPhysical(inventReleaseOrderPickingTmplocal);     //Pick for release     this.pickForRelease(inventReleaseOrderPickingTmplocal);     //Clear activate now     this.updateClearActivateNow(inventReleaseOrderPickingTmplocal); }

private query createOrderLinesPossibleQuery() {     Query                   query;     QueryBuildDataSource    dsSalesLine;     QueryBuildDataSource    dsReleaseOrderPickingTmp;     query = new Query(queryStr(SalesReleaseOrderPickingAuto));     dsSalesLine = query.dataSourceTable(tableNum(SalesLine));     dsReleaseOrderPickingTmp = dsSalesLine.addDataSource(tableNum(InventReleaseOrderPickingTmp));     dsReleaseOrderPickingTmp.joinMode(JoinMode::ExistsJoin);     dsReleaseOrderPickingTmp.relations(false);     dsReleaseOrderPickingTmp.addLink(fieldNum(SalesLine, SalesId), fieldNum(InventReleaseOrderPickingTmp, OrderId));     return query; }

/// <summary> /// Calculates the delivery percent possible for the whole order and updates this amount in the /// temporary table. /// </summary> protected void deliverPossiblePctCalc() {     Counter                         numberOfOrderLines;     Counter                         numberOfOrderLinesDeliverable;     Percent                         deliverPossiblePct;     InventReleaseOrderPickingTmp    releaseOrderPickingTmp;     SalesLine                       salesLineLocal;     InventTable                     inventTable;     InventTransOriginSalesLine      inventTransOriginSalesLine;     InventTrans                     inventTrans;     InventDim                       inventDimSalesLine;     SysQueryRun                     queryRun;     InventQty                       qtyPhysicalInvent;     InventQty                       qtyRemainToDeliver;     InventQty                       qtyReservedOrdered;     releaseOrderPickingTmp.linkPhysicalTableInstance(inventReleaseOrderPickingTmp);     queryRun =  new SysQueryRun(this.createOrderLinesPossibleQuery());     while (queryRun.next())     {         salesLineLocal = queryRun.get(salesLineLocal.TableId);         if (!salesLineRecIdSet.in(salesLineLocal.RecId))         {             inventDimSalesLine = this.findInventDim(salesLineLocal.InventDimId);             qtyPhysicalInvent = this.findQtyPhysicalInvent(salesLineLocal.ItemId,inventDimSalesLine);             if (qtyPhysicalInvent > 0)             {                 qtyReservedOrdered = this.findQtyReservedOrdered(salesLineLocal.InventTransId);                 qtyRemainToDeliver = this.findQtyRemainToDeliver(salesLineLocal, qtyReservedOrdered);                 if (qtyRemainToDeliver > 0)                 {                     this.updateNumberOfOrderLinesDeliverableMap(salesLineLocal.SalesId, this.allOrdersDeliverable(salesLineLocal.ItemId,inventDimSalesLine));                 }             }         }     }     while select count(RecId) from inventReleaseOrderPickingTmp         group by OrderId     {         // find total number of order lines found deliverable (AllOrderDeliverable == Yes)         numberOfOrderLinesDeliverable = numberOfOrderLinesDeliverableMap.lookup(inventReleaseOrderPickingTmp.OrderId);        if (numberOfOrderLinesDeliverable)         {             // find total number of possible order lines for this order             select count(RecId) from salesLineLocal                 where salesLineLocal.SalesId                                 == inventReleaseOrderPickingTmp.OrderId                    && salesLineLocal.SalesStatus                             == SalesStatus::Backorder                    && salesLineLocal.Blocked                                 == NoYes::No                    && salesLineLocal.DeliveryType                            != TradeLineDlvType::DropShip                    &&(salesLineLocal.SalesType                               == SalesType::Subscription                    || salesLineLocal.SalesType                               == SalesType::Sales                    || salesLineLocal.SalesType                               == SalesType::ItemReq)                exists join inventTransOriginSalesLine                 where inventTransOriginSalesLine.SalesLineDataAreaId    == salesLineLocal.DataAreaId                    && inventTransOriginSalesLine.SalesLineInventTransId == salesLineLocal.InventTransId                    exists join inventTrans                         where inventTrans.InventTransOrigin             == inventTransOriginSalesLine.InventTransOrigin                           && (inventTrans.StatusIssue                   == StatusIssue::ReservPhysical                           ||  inventTrans.StatusIssue                   == StatusIssue::ReservOrdered                           ||  inventTrans.StatusIssue                   == StatusIssue::OnOrder)                         notexists join inventTable                             where inventTable.ItemId                    == inventTrans.ItemId                               &&  inventTable.ItemType                  == ItemType::Service;             numberOfOrderLines  = any2int(salesLineLocal.RecId);             // calculate the possible deliverypct for the entire order             deliverPossiblePct  = numberOfOrderLinesDeliverable * 100 / minOne(numberOfOrderLines);             update_recordset releaseOrderPickingTmp                 setting DeliverPossiblePct = deliverPossiblePct                 where releaseOrderPickingTmp.OrderId == inventReleaseOrderPickingTmp.OrderId;         }     } }

protected InventQty findQtyRemainToDeliver(SalesLine _salesLine, InventQty _qtyReservedOrdered ) {     InventQty           qtyRemainToDeliver;     if (!qtyRemainToDeliverMap)     {         qtyRemainToDeliverMap   = new Map(Types::String, Types::Real);     }     if (qtyRemainToDeliverMap.exists(_salesLine.InventTransId))     {         qtyRemainToDeliver = qtyRemainToDeliverMap.lookup(_salesLine.InventTransId);     }     else     {         qtyRemainToDeliver = _salesLine.RemainInventPhysical - _qtyReservedOrdered - this.findQtyWMSOrder(_salesLine)- _salesLine.pickedInTotalInventUnit();         qtyRemainToDeliverMap.insert(_salesLine.InventTransId, qtyRemainToDeliver);     }     return qtyRemainToDeliver; }

protected InventQty findQtyWMSOrder(SalesLine _salesLine) {     InventQty           qtyWMSOrder;     if (!qtyWMSOrderMap)     {         qtyWMSOrderMap = new Map(Types::String, Types::Real);     }     if (qtyWMSOrderMap.exists(_salesLine.InventTransId))     {         qtyWMSOrder = qtyWMSOrderMap.lookup(_salesLine.InventTransId);     }     else     {         qtyWMSOrder = InventTrans::qtyWMSOrder(InventTransOriginSalesLine::findInventTransOriginId(_salesLine.DataAreaId, _salesLine.InventTransId));         qtyWMSOrderMap.insert(_salesLine.InventTransId, qtyWMSOrder);     }     return -qtyWMSOrder; }

protected boolean needAllocation(ItemId _itemId, InventDim _inventDim, Common _salesLine = null) {     SalesLine salesLineLocal = _salesLine as SalesLine;     if (!super(_itemId, _inventDim)      || salesLineLocal.InventDeliverNow == salesLineLocal.qtyActivateRemainder())     {         return false;     }     return true; }

protected void new() {     super(); }

private void pickForRelease(inventReleaseOrderPickingTmp    _inventReleaseOrderPickingTmp) {     inventReleaseOrderPickingTmp        inventReleaseOrderPickingTmpRel;     SalesLine                           SalesLineLocal,salesLineLocal2;     SalesTable                          salesTable;     SalesFormLetter_PickingList         salesFormLetter;     WMSOrderTrans                       wmsOrderTrans;     inventReleaseOrderPickingTmpRel.linkPhysicalTableInstance(inventReleaseOrderPickingTmp);     ttsBegin;     while select forupdate salesLineLocal             where salesLineLocal.SalesStatus == SalesStatus::Backorder             && salesLineLocal.PdsCWInventDeliverNow < salesLineLocal.QtyOrdered             && salesLineLocal.ShipComplete == NoYes::No             join salesTable order by salesTable.salesId             where salesTable.salesid == salesLineLocal.salesid             exists join inventReleaseOrderPickingTmpRel                 where inventReleaseOrderPickingTmpRel.TransRecId == salesLineLocal.RecId     {             salesFormLetter = salesFormLetter::construct(DocumentStatus::PickingList);             salesFormLetter.update(salesTable,systemDateGet(),SalesUpdate::DeliverNow,AccountOrder::None,NoYes::No,NoYes::No);     }     ttsCommit; }

/// <summary> /// Clears values that are entered in the <c>ActivateNow</c> field for each <c>SalesLine</c> record. /// </summary> /// <param name="_inventReleaseOrderPickingTmp"> /// An <c>InventReleaseOrderPickingTmp</c> record. /// </param> /// <remarks> /// This method is used by the <c>SalesReleaseOrderPicking</c> form. /// </remarks>  public void updateClearActivateNow(inventReleaseOrderPickingTmp _inventReleaseOrderPickingTmp) {     SalesLine                       salesLineLocal;     InventMovement                  movement;     InventUpd_Reservation           reservation;     InventQty                       reservQty;     InventReleaseOrderPickingTmp    inventReleaseOrderPickingTmplocal;     PdsCWInventQty                  cwReservQty;     ttsbegin;     inventReleaseOrderPickingTmplocal.linkPhysicalTableInstance(inventReleaseOrderPickingTmp);     if (#PdsCatchWeightEnabled)     {         while select forupdate salesLineLocal            where salesLineLocal.SalesStatus      == SalesStatus::Backorder &&                     (salesLineLocal.InventDeliverNow != 0 ||                      salesLineLocal.SalesDeliverNow  != 0 ||                      salesLineLocal.PdsCWInventDeliverNow != 0)             exists join inventReleaseOrderPickingTmplocal                where inventReleaseOrderPickingTmplocal.TransRecId == salesLineLocal.RecId         {             movement    = InventMovement::construct(salesLineLocal);             reservQty = salesLineLocal.InventDeliverNow;             cwReservQty = salesLineLocal.PdsCWInventDeliverNow;             reservation = InventUpd_Reservation::newMovement(movement, reservQty, true);             reservation.updateNow();             salesLineLocal.InventDeliverNow = 0;             salesLineLocal.SalesDeliverNow  = 0;             salesLineLocal.PdsCWInventDeliverNow = 0;             salesLineLocal.update();         }     }     else     {         while select forupdate salesLineLocal             where salesLineLocal.SalesStatus      == SalesStatus::Backorder &&                     (salesLineLocal.InventDeliverNow != 0 ||                     salesLineLocal.SalesDeliverNow  != 0)             exists join inventReleaseOrderPickingTmplocal                 where inventReleaseOrderPickingTmplocal.TransRecId == salesLineLocal.RecId         {             movement    = InventMovement::construct(salesLineLocal);             reservQty = salesLineLocal.InventDeliverNow;             reservation = InventUpd_Reservation::newMovement(movement, reservQty, true);             reservation.updateNow();             salesLineLocal.InventDeliverNow = 0;             salesLineLocal.SalesDeliverNow  = 0;             salesLineLocal.update();         }     }     ttscommit; }

protected void updateNumberOfOrderLinesDeliverableMap(SalesId _salesId, NoYes _allOrdersDeliverable) {     Counter              numberOfOrderLinesDeliverable;     if (numberOfOrderLinesDeliverableMap.exists(_salesId))     {         numberOfOrderLinesDeliverable = numberOfOrderLinesDeliverableMap.lookup(_salesId);     }     else     {         numberOfOrderLinesDeliverable = 0;         numberOfOrderLinesDeliverableMap.insert(_salesId,numberOfOrderLinesDeliverable);     }     if (_allOrdersDeliverable)     {         numberOfOrderLinesDeliverableMap.insert(_salesId,numberOfOrderLinesDeliverable+1);     } }

public void updateReservedPhysical(inventReleaseOrderPickingTmp _inventReleaseOrderPickingTmp) {     SalesLine                       salesLinelocal;     InventTableModule               inventTableModule;     InventQty                       inventQty;     InventReleaseOrderPickingTmp    inventReleaseOrderPickingTmpLocal;     PdsCWInventQty                  cwQty;     ttsbegin;     inventReleaseOrderPickingTmpLocal.linkPhysicalTableInstance(_inventReleaseOrderPickingTmp);     while select forupdate salesLinelocal         where salesLinelocal.SalesStatus == SalesStatus::Backorder         join UnitId from inventTableModule             where inventTableModule.ItemId     == salesLinelocal.ItemId &&                   inventTableModule.ModuleType == ModuleInventPurchSales::Invent             exists join inventReleaseOrderPickingTmpLocal             where inventReleaseOrderPickingTmpLocal.TransRecId == salesLinelocal.RecId     {         if (salesLinelocal.InventTransId && salesLinelocal.isStocked())         {             inventQty = -InventTransIdSumSingleStatus::qtyReservPhysicalChildTypeNone(                  InventTransOriginSalesLine::findInventTransOriginId(salesLinelocal.DataAreaId, salesLinelocal.InventTransId));             cwQty = -InventTransIdSumSingleStatus::pdsCWQtyReservPhysicalChildTypeNone(                  InventTransOriginSalesLine::findInventTransOriginId(salesLinelocal.DataAreaId, salesLinelocal.InventTransId));         }         else         {             inventQty = 0;             cwQty = 0;         }         salesLinelocal.InventDeliverNow  = inventQty;         salesLinelocal.PdsCWInventDeliverNow = cwQty;         salesLinelocal.SalesDeliverNow   = UnitOfMeasureConverter::convert(             salesLinelocal.InventDeliverNow,             UnitOfMeasure::unitOfMeasureIdBySymbol(inventTableModule.UnitId),             UnitOfMeasure::unitOfMeasureIdBySymbol(salesLinelocal.SalesUnit),             NoYes::Yes,             InventTable::itemProduct(salesLinelocal.ItemId));         salesLinelocal.update();     }     ttscommit; }

static server public boolean checkOverActivate(     SalesLine       _salesLine,     InventQty       _activateNow,     boolean         _deductUnreserved,     PdsCWInventQty  _cwActivateNow) {     return InventReleaseOrderPickingForm::checkOverActivate(_salesLine, _activateNow, _deductUnreserved, _cwActivateNow); }

public static InventRelOrderPick_AutoProcessSalesData construct() {     return new InventRelOrderPick_AutoProcessSalesData(); }

[SysObsoleteAttribute('The release for pick button must always be enabled. Remove the call to the method.', true)] static client void enableUpdateJournalButtons(     SalesLine                   _salesLine,     FormFunctionButtonControl   buttonUpdatePickingList) { }

public static void main(Args _args) {     InventReleaseOrderPickingTmp    inventReleaseOrderPickingTmplocal;     inventRelOrderPick_AutoProcessSalesData inventRelOrderPick_AutoProcessSalesData;     inventRelOrderPick_AutoProcessSalesData = InventRelOrderPick_AutoProcessSalesData::construct();     inventRelOrderPick_AutoProcessSalesData.buildData(false); }