Create Invoice PO base on Packing Slip/Product Receipt by X++

This code will created pending invoice base on VendPackingSlipJour data and posting that’s pending invoice at the end of code.

PurchFormLetter             purchFormLetter;

    PurchTable                  purchTable;
    PurchLine                   purchLine;

    VendPackingSlipJour         vendPackingSlipJour;
    VendPackingSlipTrans        vendPackingSlipTrans;

    VendInvoiceInfoTable        vendInvoiceInfoTable;
    VendInvoiceInfoLine         vendInvoiceInfoLine;
    VendInvoiceInfoSubTable     vendInvoiceInfoSubTable;
    VendInvoiceInfoSubLine      vendInvoiceInfoSubLine;

    PurchId                     purchId;
    PackingSlipId               packingSlipId;
    ;

    purchId = '9001-PO-16-000745';
    packingSlipId = '9001-PO-16-000745-2';

    ttsBegin;
    purchTable = PurchTable::find(purchId);

    while select vendPackingSlipJour
        where vendPackingSlipJour.PurchId == purchTable.PurchId
        && vendPackingSlipJour.PackingSlipId == packingSlipId
    {

        //Generate Pending Invoice Header
        vendInvoiceInfoTable.clear();
        vendInvoiceInfoTable.initValue();
        vendInvoiceInfoTable.initFromPurchTable(purchTable);

        vendInvoiceInfoTable.DocumentOrigin  = DocumentOrigin::Manual;
        vendInvoiceInfoTable.CurrencyCode = purchTable.CurrencyCode;
        vendInvoiceInfoTable.DeliveryName = purchTable.DeliveryName;
        vendInvoiceInfoTable.Num = "INV-" + vendPackingSlipJour.PackingSlipId; //add invoice number in here
        vendInvoiceInfoTable.PurchName = purchTable.PurchName;
        vendInvoiceInfoTable.VendInvoiceSaveStatus = VendInvoiceSaveStatus::Pending;
        vendInvoiceInfoTable.DocumentDate = systemDateGet();
        vendInvoiceInfoTable.LastMatchVariance = LastMatchVarianceOptions::OK;
        vendInvoiceInfoTable.ParmJobStatus = ParmJobStatus::Waiting;
        vendInvoiceInfoTable.Approved = NoYes::Yes;
        vendInvoiceInfoTable.Approver = DirPersonUser::currentWorker();

        vendInvoiceInfoTable.DefaultDimension = vendInvoiceInfoTable.copyDimension(purchTable.DefaultDimension);

        vendInvoiceInfoTable.defaultField(fieldNum(VendInvoiceInfoTable,RemittanceLocation),null,purchTable);
        vendInvoiceInfoTable.defaultField(fieldNum(VendInvoiceInfoTable,FixedDueDate),null,purchTable);
        vendInvoiceInfoTable.defaultField(fieldNum(VendInvoiceInfoTable,ExchRate),null,purchTable);
        vendInvoiceInfoTable.defaultField(fieldNum(VendInvoiceInfoTable,TransDate),null,purchTable);
        vendInvoiceInfoTable.defaultField(fieldNum(VendInvoiceInfoTable,PaymMode),null,purchTable);

        vendInvoiceInfoTable.insert();

        //Generate Vend Invoice Info reference
        if(vendInvoiceInfoTable)
        {
            vendInvoiceInfoSubTable.clear();
            vendInvoiceInfoSubTable.initValue();
            vendInvoiceInfoSubTable.defaultRow();

            vendInvoiceInfoSubTable.ParmId = vendInvoiceInfoTable.ParmId;
            vendInvoiceInfoSubTable.OrigPurchId = vendInvoiceInfoTable.PurchId;
            vendInvoiceInfoSubTable.PurchName = vendInvoiceInfoTable.PurchName;
            vendInvoiceInfoSubTable.TableRefId = vendInvoiceInfoTable.TableRefId;

            vendInvoiceInfoSubTable.insert();
        }

        //select all packing slip line
        while select vendPackingSlipTrans
            where vendPackingSlipTrans.PackingSlipId == vendPackingSlipJour.PackingSlipId
            && vendPackingSlipTrans.VendPackingSlipJour == vendPackingSlipJour.RecId
        {
            //Generate Pending Invoice Line
            purchLine = vendPackingSlipTrans.purchLine();
            vendInvoiceInfoLine.clear();
            vendInvoiceInfoLine.initValue();
            vendInvoiceInfoLine.defaultRow(null,purchLine);
            vendInvoiceInfoLine.initFromPurchLine(purchLine);

            vendInvoiceInfoLine.DeliveryName = vendInvoiceInfoTable.DeliveryName;
            vendInvoiceInfoLine.ParmId = vendInvoiceInfoTable.ParmId;
            vendInvoiceInfoLine.TableRefId = vendInvoiceInfoTable.TableRefId;
            vendInvoiceInfoLine.currencyCode = vendInvoiceInfoTable.CurrencyCode;
            vendInvoiceInfoLine.LineNum = any2int(purchLine.LineNumber);

            vendInvoiceInfoLine.InvoiceAccount = vendInvoiceInfoTable.InvoiceAccount;
            vendInvoiceInfoLine.InventDimId = vendPackingSlipTrans.InventDimId;
            vendInvoiceInfoLine.OrderAccount  = vendInvoiceInfoTable.OrderAccount;
            vendInvoiceInfoLine.ItemId = vendPackingSlipTrans.ItemId;
            vendInvoiceInfoLine.InventTransId = vendPackingSlipTrans.InventTransId;

            vendInvoiceInfoLine.DocumentOrigin = DocumentOrigin::Manual;

            vendInvoiceInfoLine.ReceiveNow = vendPackingSlipTrans.Qty;
            vendInvoiceInfoLine.RemainBefore = vendPackingSlipTrans.Qty;
            vendInvoiceInfoLine.RemainBeforeInvent = vendPackingSlipTrans.Qty;

            vendInvoiceInfoLine.PurchPrice = purchLine.PurchPrice;
            vendInvoiceInfoLine.InventNow = vendPackingSlipTrans.Qty;
            vendInvoiceInfoLine.LineAmount = (purchLine.LineAmount / purchLine.QtyOrdered) * vendPackingSlipTrans.Qty;

            vendInvoiceInfoLine.DefaultDimension = purchLine.DefaultDimension;

            vendInvoiceInfoLine.insert();

            //Generate Vend Invoice Info reference from packing slip
            if(vendInvoiceInfoLine.RecId)
            {
                vendInvoiceInfoSubLine.clear();
                vendInvoiceInfoSubLine.initValue();
                vendInvoiceInfoSubLine.defaultRow();
                vendInvoiceInfoSubLine.ParmId = vendInvoiceInfoTable.ParmId;
                vendInvoiceInfoSubLine.LineRefRecId = vendInvoiceInfoLine.RecId;
                vendInvoiceInfoSubLine.ReceiveNow = vendPackingSlipTrans.Qty;
                vendInvoiceInfoSubLine.InventNow = vendPackingSlipTrans.Qty;
                vendInvoiceInfoSubLine.JournalRefRecId = vendPackingSlipTrans.RecId;
                vendInvoiceInfoSubLine.JournalRefTableId = vendPackingSlipTrans.TableId;
                vendInvoiceInfoSubLine.DocumentId = vendPackingSlipTrans.PackingSlipId;
                vendInvoiceInfoSubLine.insert();
            }
        }

        //Posting pending invoice invoice
        purchFormLetter = PurchFormLetter_Invoice::newFromSavedInvoice(vendInvoiceInfoTable);
        purchFormLetter.update(vendInvoiceInfoTable.purchTable(),vendInvoiceInfoTable.Num);
    }

    ttsCommit;

    info(strFmt('Purchase Order %1 invoiced',purchTable.PurchId));

Unretrieve field InventDimId on InventOnHanItem Form

Capture

Add code :
qbsSum.addGroupByField(fieldNum(InventSum,InventDimId));

in the “modifyQuery” method on the “InventDimCtrl_Frm_OnHand” class

Example code

query.clearGroupBy();
qbsSum.addGroupByField(fieldNum(InventSum,ItemId));
qbsSum.addGroupByField(fieldNum(InventSum,InventDimId));

Result:
Capture

Get tax amount through x++

static void TIDgetTax(Args _args)
{
    TaxAmount           tax;
    CustInvoiceTrans    CustInvoiceTrans;
    ;

    CustInvoiceTrans =  CustInvoiceTrans::findRecId(5637155209);
    tax = Tax::calcTaxAmount(CustInvoiceTrans.TaxGroup, CustInvoiceTrans.TaxItemGroup, CustInvoiceTrans.InvoiceDate,
                            CustInvoiceTrans.CurrencyCode, CustInvoiceTrans.LineAmount, TaxModuleType::FreeTxtInvoice);

    info(strfmt('%1', tax));
}

Consuming webservices (SOAP) from AX example code

public static container UserInfoByIDRequest(str _storeName, str _idType, str _idNo, str _extRef,str _username = "",str _password = "")
{
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.Summary summary;
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.RemittanceClient svcClient;
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.UserInfoByIDRequest request;
    TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.UserInfoByIDResponse response;

    CLRObject clrObject;
    System.Exception ex;
    str errorCode, errorMesg;
    container  returnCon;
    boolean    canConnectAPI;
    str        errorCanConnectAPI;

    new InteropPermission(InteropKind::ClrInterop).assert();
    try
    {
        clrObject = CLRInterop::getType("TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.RemittanceClient");
        svcClient = AifUtil::createServiceClient(clrObject);

        request = new TIDIDSSIMPONI.ServiceReferences.TIDIDSSR.UserInfoByIDRequest();

        //if no username or password provided, get from AX
        if(!_username || !_password)
        {
            _username = TIDIDSSimponiWebConsumeMethod::getUserNamePasswordStore(_storeName).IPMS_GaleriUserName;
            _password = TIDIDSSimponiWebConsumeMethod::getUserNamePasswordStore(_storeName).IPMS_GaleriPassword;
        }

        request.set_Username(_username);
        request.set_Password(_password);
        request.set_StoreName(_storeName);
        request.set_IdType(_idType);
        request.set_IdNo(_idNo);
        request.set_ExtRef(_extRef);

        //consuming webservice API
        response = svcClient.GetUserInfoByID(request);
        summary = response.get_Summary();

        //unpack to variable
        errorCode = summary.get_ErrorCode();
        errorMesg = summary.get_ErrorMessage();

        //packing all variable to container and return it
        returnCon += errorCode;
        returnCon += errorMesg;



        //sucess connect to webAPI
        canConnectAPI = true;

    }
    catch(Exception::CLRError)
    {
        ex = CLRInterop::getLastException();
        info(ex.ToString());

        canConnectAPI = false;
        errorCanConnectAPI =ex.ToString();
    }

    return [canConnectAPI, errorCanConnectAPI, returnCon];
}

Only foreign key constraints are allowed on this table.

Best Practice Error

Only foreign key constraints are allowed on this table

Capture

How to rectify this best practice error.

For Custom Table

  1. Export table to XPO
  2. Edit it with text editor, and change
    • from: EnforceFKRelation 1
    • to: EnforceFKRelation 0
  3. delete table in AX, synchronize
  4. import  xpo file

Capture

For Standard  Table

Some custom relation table may have some error on standard table, we can fix this  best practice error using same trick as above, but for this one lets check on XPO file and change REFERENCETYPE value.

  1. Export table to XPO
  2. Edit it with text editor, and change
    • from: REFERENCETYPE  NORMAL
    • to: REFERENCETYPE PKFK
  3. delete table in AX, synchronize
  4. import  xpo file

Capture.

Running Batch Job from X++ example code

static void TID_ExecuteBatchJobDistribution(Args _args)
{
    BatchHeader header;
    SysRecurrenceData sysRecurrenceData;
    Batch batch;
    BatchJob batchJob;
    RetailConnScheduleRunner _RetailConnScheduleRunner; // Class extends RunBaseBatch
    BatchInfo processBatchInfo;
    BatchRetries noOfRetriesOnFailure = 4;
    ;

    // Setup the RunBaseBatch Job
    header = BatchHeader::construct();
    _RetailConnScheduleRunner = new RetailConnScheduleRunner();
    processBatchInfo = _RetailConnScheduleRunner.batchInfo();
    processBatchInfo.parmRetriesOnFailure(noOfRetriesOnFailure);
    processBatchInfo.parmCaption(_RetailConnSchedule.Name); // Description Batch Job
    processBatchInfo.parmGroupId('RTL'); // Batch Gorup
    processBatchInfo.parmBatchExecute(NoYes::Yes);
    header.addTask(_RetailConnScheduleRunner);

    // Set the recurrence data
    sysRecurrenceData = SysRecurrence::defaultRecurrence();
    SysRecurrence::setRecurrenceStartDateTime(sysRecurrenceData, DateTimeUtil::addSeconds(DateTimeUtil::utcNow(), 20)); // Set range of recurrence
    SysRecurrence::setRecurrenceNoEnd(sysRecurrenceData);
    SysRecurrence::setRecurrenceUnit(sysRecurrenceData, SysRecurrenceUnit::Minute); // Set reccurence pattern
    header.parmRecurrenceData(sysRecurrenceData);
    // Set the batch alert configurations
    header.parmAlerts(NoYes::No, NoYes::Yes, NoYes::No, NoYes::Yes, NoYes::Yes);
    header.save();

    // Update the frequency to run the job to every two minutes
    ttsbegin;
    select forupdate batchJob
    join batch
    where batchJob.RecId == batch.BatchJobId
    && batch.ClassNumber == classnum(RetailConnScheduleRunner);

    sysRecurrenceData = batchJob.RecurrenceData;
    sysRecurrenceData = conpoke(sysRecurrenceData, 8, [10]);
    batchJob.RecurrenceData = sysRecurrenceData;
    batchJob.update();
    ttscommit;
}

marking inventTrans example code

public static void FNDMarkInventTrans(InventTransId _inventTransId,InventTransId _refInventTransId,InventQty _qtyTomark)
{
    InventTrans issueInventTrans;
    TmpInventTransMark tmpInventTransMask;
    Map mapMarkNow;
    container con;
    real qty;
    Map mapTmp;
    MapEnumerator mapEnumerator;

    InventTransOriginId issueInventTransOriginId =
        InventTransOrigin::findByInventTransId(_inventTransId).RecId;

    InventTransOriginId receiptInventTransOriginId =
        InventTransOrigin::findByInventTransId(_refInventTransId).RecId;

    InventQty qtyToMark = _qtyTomark;

    ttsBegin;

    issueInventTrans = InventTrans::findByInventTransOrigin(
        issueInventTransOriginId);

    [con, qty] = TmpInventTransMark::packTmpMark(
        InventTransOrigin::find(issueInventTransOriginId),
        issueInventTrans.inventDim(),
        issueInventTrans.Qty);

    mapTmp = Map::create(con);
    mapEnumerator = mapTmp.getEnumerator();
    while (mapEnumerator.moveNext())
    {
        tmpInventTransMask = mapEnumerator.currentValue();

        if (tmpInventTransMask.InventTransOrigin == receiptInventTransOriginId)
        {
            tmpInventTransMask.QtyMarkNow = qtyToMark;
            tmpInventTransMask.QtyRemain -= tmpInventTransMask.QtyMarkNow;
            mapMarkNow = new Map(Types::Int64, Types::Record);
            mapMarkNow.insert(tmpInventTransMask.RecId, tmpInventTransMask);

            TmpInventTransMark::updateTmpMark(
                issueInventTransOriginId,
                issueInventTrans.inventDim(),
                -qtyToMark,
                mapMarkNow.pack());
        }
    }

    ttsCommit;
}

reference code :
https://community.dynamics.com/ax/b/dynamicsaxnotesonthecuffs/archive/2013/01/08/marking-from-code-part-2

get Available budget example code

source code from form : BudgetControlStatistics
Capture

example code :

  static void TIDF_getBudgetAvailable(Args _args)
{
    
    //class and table
    BudgetControlStatisticsManager budgetControlStatsManager = new BudgetControlStatisticsManager();
    
    //table
    BudgetTmpControlStatistics  currentBudgetTmpControlStatistics;
    BudgetCycle                 budgetCycle;
    BudgetCycleTimeSpan         budgetCycleTimeSpan;
    FiscalCalendar              fiscalCalendar;
    DimensionAttributeValueCombination dimensionAttributeValueCombination;
    DimensionHierarchy          dimensionHierarchy;
    
    //variable
    DimensionValue  dim1;
    container       conDimValue;
    MainAccountNum  mainAccountNum;
    RecId           ledgerDimension;
    
    //financial dimension
    mainAccountNum = "6100-1001";
    dim1           = "5102";
    
    select firstonly dimensionAttributeValueCombination 
    where 
        dimensionAttributeValueCombination.DisplayValue == strFmt("%1-%2",mainAccountNum,dim1)
    &&  dimensionAttributeValueCombination.LedgerDimensionType == LedgerDimensionType::BudgetControl;
    
    //budget cycle
    fiscalCalendar   = fiscalCalendar::findByCalendarId("2016");
    BudgetCycleTimeSpan = budgetCycleTimeSpan::findByNameAndCalendar("SEM",fiscalCalendar.RecId);
    BudgetCycle =  budgetCycle::findBudgetCycleByDate(BudgetCycleTimeSpan.RecId,today());
    
    currentBudgetTmpControlStatistics = budgetControlStatsManager.fillBudgetTmpControlStatistics(Ledger::current(),
                                                                                                'ALL',
                                                                                                BudgetControlStatisticsPeriodOption::FiscalPeriod,
                                                                                                BudgetBalanceType::Accumulated,
                                                                                                BudgetCycle.RecId,
                                                                                                dimensionAttributeValueCombination.RecId,
                                                                                                0,
                                                                                                0,
                                                                                                true,
                                                                                                NoYes::No,
                                                                                                true);
    
   while select  currentBudgetTmpControlStatistics
   {
        info(strFmt("%1 -- %2 ",currentBudgetTmpControlStatistics.PeriodStartDate,currentBudgetTmpControlStatistics.TotalFundsAvailableAmountMST));
   }

}

result :
Capture

Auto Approve Workflow by Code x++

Example code :

static void TIDF_autoApproveWFOvertime(Args _args)
{
    WorkflowWorkItemTable WorkflowWorkItemTable;

    while select WorkflowWorkItemTable
    where
        WorkflowWorkItemTable.UserId == '32501112'
    &&  workflowWorkItemTable.Type == WorkflowWorkItemType::WorkItem
    &&  workflowWorkItemTable.Status == WorkflowWorkItemStatus::Pending
    &&  WorkflowWorkItemTable.RefTableId == tableNum(HrsOvertime)
    {
        WorkflowWorkItemActionManager::dispatchWorkItemAction(
                                    WorkflowWorkItemTable,
                                    "Auto Approve by ADMIN", // comment
                                    '32501112', // << user ID
                                    WorkflowWorkItemActionType::Complete,
                                    "HRSOvertimeApprove", // << menu item action approval workflow
                                    false); //is not web Menu Item
    }

}

Capture

get USER ID and Security Role from HcmWorker

Example Code :

display Notes TIDgetUserIdAndRoles()
{
    DirPerson               DirPerson;
    DirPersonUser           DirPersonUser;
    SecurityUserRole        SecurityUserRole;
    SecurityRole            SecurityRole;
    Notes                   returnNotes;
    str value;
    ;


    DirPerson       = DirPerson::find(this.Person);
    DirPersonUser   = DirPersonUser::findWorker(this.RecId);
    returnNotes      = strFmt("User ID : %1 \n\n ",DirPersonUser.User);
    returnNotes      = strFmt("%1Security Roles : \n",returnNotes);
    
    while select * from SecurityUserRole 
        where SecurityUserRole.User == DirPersonUser.User
    join SecurityRole
        where SecurityRole.RecId == SecurityUserRole.SecurityRole
    {
        if(subStr(SecurityRole.Name,1,1) == '@')
        {
            value = SysLabel::labelId2String2(SecurityRole.Name, companyinfo::languageId());
            returnNotes = strFmt("%1 %2\n",returnNotes,value);
        }
        else
        {
            returnNotes = strFmt("%1 %2\n",returnNotes, SecurityRole.Name);
        }
    }
    
    return returnNotes;
}

Job upload production order

//coded by fanddy
static void uploadProductionOrder(Args _args)
{
    // Progress Bar Variables
    #AviFiles
    SysOperationProgress        _ProgressBar = new SysOperationProgress();

    // Excel Variables
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    str                     Filename;
    COMVariantType          type;
    int                     row;
    FilenameFilter filenameFilter = ['*.xlsx,*.xls'];

    //variable
    ProdId              prodId;
    ItemId              itemid;
    Qty                 qtySched;
    TransDate           StartDate,endDate,deliveryDate,bomDate;
    InventSiteId        inventSiteId;
    InventLocationId    inventLocationId;
    ProdStatus          prodStatus;
    ProdBackStatus      prodBackStatus;
    ProdReservation     prodReservation;
    BOMId               bomId;

    //tables
    ProdTable       prodtable;
    InventTable     inventTable;
    InventDim       inventDim;
      // convert into str from excel cell value
    str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
    {
        switch (_cv.variantType())
        {
            case (COMVariantType::VT_BSTR):      return _cv.bStr();
            case (COMVariantType::VT_R4):        return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
            case (COMVariantType::VT_R8):        return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
            case (COMVariantType::VT_DECIMAL):   return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
            case (COMVariantType::VT_DATE):      return date2str(_cv.date(),123,2,1,2,1,4);
            case (COMVariantType::VT_EMPTY):     return '';
            default:
            throw error(strfmt('@SYS26908', _cv.variantType()));
        }
        return '';
    }




    filename = WinAPI::getOpenFileName(0, filenameFilter, '', '');
    if (filename=='')
    {
        warning('Filename must be specified correctly.');
        return;
    }

    //Filename = "C:\\prodorder.xls";
    application = SysExcelApplication::construct();
    workbooks   = application.workbooks();

    // gets the default ExchangeType RecId
    try
    {
        workbooks.open(Filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }

    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();
    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();
    row         = 1;

    // Start Progress Bar
    _ProgressBar.setCaption("Upload Production Order...");
    _ProgressBar.setAnimation(#AviTransfer);
    ttsBegin;
    do
    {
        //Incrementing the row line to next Row
        row++;

        _ProgressBar.setText(strFmt('Processing %1...', row));
        prodId              = COMVariant2Str(cells.item(row,1).value());
        itemid              = COMVariant2Str(cells.item(row,2).value());
        qtySched            = cells.item(row,3).value().double();
        startDate           = cells.item(row,4).value().date();
        endDate             = cells.item(row,5).value().date();
        inventSiteId        = COMVariant2Str(cells.item(row,6).value());
        inventLocationId    = COMVariant2Str(cells.item(row,7).value());
        prodStatus          = str2enum(ProdStatus,COMVariant2Str(cells.item(row,8).value()));
        prodBackStatus      = str2enum(ProdBackStatus,COMVariant2Str(cells.item(row,9).value()));
        deliveryDate        = cells.item(row,10).value().date();
        prodReservation     = str2enum(prodReservation,COMVariant2Str(cells.item(row,11).value()));
        bomDate             = cells.item(row,12).value().date();
        bomId               = COMVariant2Str(cells.item(row,13).value());


        // Initialize InventTable
        inventTable.clear();
        inventTable = inventTable::find(itemid);

        // Initialize the base values
        prodtable.clear();
        prodtable.initValue();
        prodtable.initFromInventTable(inventTable);
        prodtable.ProdId                = prodId;
        prodtable.ItemId                = inventTable.ItemId;
        prodtable.QtySched              = qtySched;
        prodtable.SchedStart            = StartDate;
        prodtable.SchedEnd              = endDate;
        prodtable.DlvDate               = deliveryDate;
        prodtable.RemainInventPhysical  = qtySched;
        prodtable.BOMDate               = today();
        prodtable.ProdStatus            = prodStatus;
        prodtable.BackorderStatus       = prodBackStatus;
        prodtable.Reservation           = prodReservation;

        // Initialize InventDim (Obrigatory)
        inventDim.clear();
        inventDim.InventSiteId          = inventSiteId;
        inventDim.InventLocationId      = inventLocationId;
        inventDim                       = inventDim::findOrCreate(inventDim);
        prodtable.InventDimId           = inventDim.inventDimId;

        // Set the active BOM and Route
        if(!bomId)
        {
            prodtable.BOMId = BOMVersion::findActive(prodtable.ItemId,
                                                        prodtable.BOMDate,
                                                        prodtable.QtySched,
                                                        inventDim).BOMId;
        }
        if(!prodtable.RouteId)
        {
            prodtable.RouteId = RouteVersion::findActive(prodtable.ItemId,
                                                         prodtable.BOMDate,
                                                         prodtable.QtySched,
                                                         inventDim).RouteId;
        }

        // Initialize BOMVersion
        prodtable.initBOMVersion();
        // Initialize RouteVersion
        prodtable.initRouteVersion();
        //Use ProdTableType class to create the production order
        prodtable.type().insert();
        

        print (strFmt('Line number %1 - Production order %2 - Item ID %3', row,prodtable.ProdId, itemId));

        type = cells.item(row+1, 1).value().variantType();
    }
    while (type != COMVariantType::VT_EMPTY);
    
    ttsCommit;


    // quits the application
    application.quit();

    info('Upload success');
}

check user workflow hierarchy by code x++

i found a nice code for checking user workflow hierarchy approver and solved the related error.

static void navax_workflowHierarchyTester(Args _args)
{
    WorkflowTypeName                    workflowTemplateName = 'TIDEmploymentLeave';
    //TrvExpTrans                         trvExpTrans;
    //TrvExpNumber                        trvExpNum = '000015'; //Expense id

    HcmEmploymentLeave                  hcmEmploymentLeave;
    str                                 nodeId = '23511014'; //Starting worker id
    WorkflowHierarchyLevel              level = 0;
    WorkflowContext                     workflowContext;
    SysWorkflowTable                    workflowTable;
    WorkflowLimitHierarchyProvider      workflowLimitHierarchyProvider;
    WorkflowHierarchyProviderNode       workflowHierarchyProviderNode;
    HRPWorkerLimit                      workerLimit = new HRPWorkerLimit();
    HcmWorker                           hcmWorker;
    DirPersonUser                       dirPersonUser;
    RefRecId                            hcmPositionRecId;
    HcmPosition                         hcmPosition;
    container                           spendingCon, approvalCon;
    UserId                              userId;

    select firstOnly workflowTable
        where workflowTable.TemplateName == workflowTemplateName;

    select firstOnly hcmEmploymentLeave
        where hcmEmploymentLeave.HRSLeaveReqId ==  &quot;LV-00635&quot; ;

    workflowContext = WorkflowContext::newWorkflowContext(
        curext(),
        tableNum(hcmEmploymentLeave),
        hcmEmploymentLeave.RecId,
        workflowTable.WorkflowCorrelationId);

    workflowLimitHierarchyProvider = new WorkflowLimitHierarchyProvider();

    //Level 1
    while (nodeId &amp;&amp; level &lt; 20) //20 is just a fall back. In case it goes into an endless loop.
    {
        workflowHierarchyProviderNode   = workflowLimitHierarchyProvider.getNextNode(nodeId, level, workflowContext);
        nodeId                          = workflowHierarchyProviderNode.getnodeId();
        hcmWorker                       = HcmWorker::findByPersonnelNumber(nodeId);
        userId                          = DirPersonUser::findParty(hcmWorker.Person).User;
        hcmPositionRecId                = HcmWorker::getPrimaryPosition(hcmWorker.RecId);
        hcmPosition                     = HcmPosition::find(hcmPositionRecId);

        spendingCon = workerLimit.getWorkerSigningLimit(
            hcmWorker.RecId,
            SourceDocumentRelationType::ExpenseReport,
            HRPLimitType::Spending,
            hcmPosition.PositionId);

        approvalCon = workerLimit.getWorkerSigningLimit(
            hcmWorker.RecId,
            SourceDocumentRelationType::ExpenseReport,
            HRPLimitType::Approval,
            hcmPosition.PositionId);

        info(strFmt(&quot;UserId: %1 | WorkerId: %2 | PositionId: %3 | ReportsToPosition: %4 | SpendingLimit: %5 | ApprovalLimit: %6&quot;,
            userId,
            hcmWorker.PersonnelNumber,
            hcmPosition.PositionId,
            hcmPosition.reportsToPosition(),
            con2Str(spendingCon),
            con2Str(approvalCon)
            ));

        level++;
    }
}

source :
https://community.dynamics.com/ax/b/dynamicsnavax/archive/2015/06/08/workflow-hierarchy-assignment-common-errors-explained-and-tester-job-ax-2012

Job upload fixed assets master data with value models example code

Job

static void TIDF_uploadFAwithValueModels(Args _args)
{
    AssetId             assetId;
    AssetGroupId        assetGroupId;
    AssetName           assetName;
    AssetLocationId     assetLocationId;
    AssetServiceLife    assetServiceLife;
    AssetLifeTimeRest   assetLifeTimeRest;
    AssetPostingProfile assetPostingProfile;
    AssetBookId         assetBookId;
    TransDate           depreciationStartDate,LastDepreciationDate,AcquisitionDate;
    RecId               recIdDefaultDimension;
    AssetLongDescription AssetLongDescription;

    AxAssetTable        axAssetTable;

    //table
    AssetTable          assetTable;
    AssetGroup          assetGroup;
    AssetLocation       assetLocation;
    AssetBook           assetBook;
    AssetLedger         assetLedger;

    container   financialDimensionFromExcel;
    container   conDimensionName;

    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename;
    ;

    startLengthyOperation();
    //variable yang diisi manual
    //C:\#TECTURA\Master Data
    filename              = "C:\\#TECTURA\\Master Data\\FA Master_Budi00.xlsx";
    assetPostingProfile   = "FA POSTING";
    depreciationStartDate = today();
    LastDepreciationDate  = today();
    AcquisitionDate       = today();

    try
    {
        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();
            conDimensionName = TIDgetDimensionName();

            row = 5;

            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //financial dimension
                financialDimensionFromExcel = conNull();
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,7).value().bStr();
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,8).value().bStr();
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,9).value().bStr();
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,10).value().bStr();
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,11).value().bStr();
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,12).value().bStr();
                //generateDefaultDimension
                recIdDefaultDimension       = TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);

                //variable
                assetId           = TIDComVariant2STR(cells.item(row,2).value());
                assetGroupId      = TIDComVariant2STR(cells.item(row,1).value());
                assetLocationId   = TIDComVariant2STR(cells.item(row,6).value());
                assetName         = TIDComVariant2STR(cells.item(row,3).value());
                assetServiceLife  = str2num(TIDComVariant2STR(cells.item(row,4).value()));
                assetLifeTimeRest = str2num(TIDComVariant2STR(cells.item(row,5).value()));
                assetBookId          = TIDComVariant2STR(cells.item(row,13).value());
                AssetLongDescription = TIDComVariant2STR(cells.item(row,14).value());
                //table
                assetTable      = assetTable::find(assetId);
                assetGroup      = AssetGroup::find(assetGroupId);
                assetLocation   = AssetLocation::find(assetLocationId);

                //validation
                if(!assetGroup || !assetLocation)
                {
                    throw error(strFmt("Row : %1 ,Asset group or asset location not existed, please check again",row));
                }
                //create asset Table
                if(!assetTable)
                {
                    axAssetTable    = new AxAssetTable();
                    axAssetTable.parmAssetId(assetId);
                    axAssetTable.parmAssetGroup(assetGroupId);
                    axAssetTable.parmName(assetName);
                    axAssetTable.parmLocation(assetLocationId);
                    axAssetTable.parmLocationMemo(AssetLongDescription);
                    axAssetTable.save();
                }

                //create assetBook
                assetBook = assetBook::find(assetId,assetBookId);
                if(!assetBook)
                {
                    assetBook.clear();
                    assetBook.initValue();
                    assetBook.AssetId               = assetId;
                    assetBook.BookId                = assetBookId;
                    assetBook.PostingProfile        = assetPostingProfile;
                    assetBook.ServiceLife           = assetServiceLife;
                    assetBook.LifeTime              = assetServiceLife*12;
                    assetBook.LifeTimeRest          = assetLifeTimeRest;
                    assetBook.DepreciationStartDate = depreciationStartDate;
                    assetBook.LastDepreciationDate  = LastDepreciationDate;
                    assetBook.AcquisitionDate       = AcquisitionDate;
                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.insert();
                }
                else
                {
                    assetBook.selectForUpdate(true);
                    assetBook.ServiceLife           = assetServiceLife;
                    assetBook.LifeTime              = assetServiceLife*12;
                    assetBook.LifeTimeRest          = assetLifeTimeRest;
                    assetBook.DepreciationStartDate = depreciationStartDate;
                    assetBook.LastDepreciationDate  = LastDepreciationDate;
                    assetBook.AcquisitionDate       = AcquisitionDate;
                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.update();
                }


                print strFmt('FA Group : %1 FAID : %2 Name : %3',assetGroupId,assetId,assetName);
                row++;
            }
            ttsCommit;
            info("Success");
            application.quit();
        }
    }
    catch(Exception::Error)
    {
        info("Fixed Asset upload Error");
        application.quit();
    }
}

TIDGetDimensionName

public static container TIDgetDimensionName()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &&
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}

TIDCreateDefaultDimension

static DimensionDefault TIDcreateDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                    result;
    int                                 i;
    DimensionAttribute                  dimensionAttribute;
    DimensionAttributeValue             dimensionAttributeValue;
    //_attr is dimension name in table DimensionAttribute
    container               conAttr =   _attr;
    container               conValue = _value;
    str                     dimValue;

    for (i = 1; i <= conLen(conAttr); i++)
    {
        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

        if (dimensionAttribute.RecId == 0)
        {
            continue;
        }

        dimValue = conPeek(conValue,i);

        if (dimValue != "")
        {
            // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
            dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

            // Add the dimensionAttibuteValue to the default dimension
            valueSetStorage.addItem(dimensionAttributeValue);
        }
    }
    result = valueSetStorage.save();
    return result;
}

TIDComVariant2STR

public static str TIDComVariant2STR(COMVariant _variant)
{
    str valueStr;
    ;

    switch(_variant.variantType())
    {
        case COMVariantType::VT_EMPTY   :
            valueStr = '';
            break;

        case COMVariantType::VT_BSTR    :

            valueStr = _variant.bStr();
            break;

        case COMVariantType::VT_R4      :
        case COMVariantType::VT_R8      :

            if(_variant.double())
            {
                valueStr = strFmt("@SYS311964",
                                    num2Str0(_variant.double(), 0),
                                    num2str(_variant.double(),
                                    0,
                                    numOfDec(_variant.double()),
                                    1,
                                    0));
            }
            break;

        default                         :
            throw error(strfmt("@SYS26908",
                                _variant.variantType()));
    }

    return valueStr;
}

Add AOT Fields to AOT table via X++

static void TIDF_addFieldAOT(Args _args)
{
    #AOT
    #TreeNodeSysNodeType
    
    TreeNode  tableNode = TreeNode::findNode(#TablesPath).AOTfindChild('TIDProdPlanOverViewTmp');
    AOTTableFieldList lst;
    int                i;
    ;
    
    lst = tableNode.AOTfindChild("fields");
    for(i=1;i<=31;i++)
    {
        lst.addDate(strFmt("date_%1",i));
    }
    tableNode.AOTsave();
    
}

source :
http://www.junctionsolutions.com/dynamicsax/creating-pivot-table-forms-and-reports-in-ax/

Filter DataSets Field Lookup Enterprise Portal example code

on AOT datasets fields method, add this code below :

void dataSetLookup(SysDataSetLookup sysDataSetLookup)
{
    List list = new List(Types::String);
    Query query = new Query();

    // Add the table to the query.
    query.addDataSource(tableNum(HcmLeaveType));

    //filter query
    query.dataSourceTable(tableNum(HcmLeaveType)).addRange(fieldNum(HcmLeaveType,ValidToCredit)).value(SysQuery::value(NoYes::Yes));

    /* uncomment if the relation is not based on reference recid
    // Specify the fields to use for the lookup.
    list.addEnd(fieldStr(HcmLeaveType,LeaveTypeId));
    list.addEnd(fieldStr(HcmLeaveType,Description));

    // Supply the set of lookup fields.
    sysDataSetLookup.parmLookupFields(list);

    // Specify the field that is returned from the lookup.
    sysDataSetLookup.parmSelectField(fieldStr(HcmLeaveType,LeaveTypeId));
    sysDataSetLookup.parmHideSelectField(true);
    */ 

    // Pass the query to the SysDataSetLookup so that the query is used.
    sysDataSetLookup.parmQuery(query);
}

preview after lookup filtered by code :
Capture

reference on MSDN :
https://msdn.microsoft.com/en-us/library/hh830903.aspx

example custom code submitManager class for both Rich Client and EP (Enterprise Portal) x++

differentiate the rich client and ep using the menu items name.
always remember to do INCREMENTAL CIL every time you modify any of workflow classses

public void submit(Args _args)
{
    WorkflowComment         note = "";
    WorkflowSubmitDialog    workflowSubmitDialog;
    WorkflowCorrelationId   workflowCorrelationId;
    recId                   recId;
    workflowTypeName        workflowTypeName = workflowtypestr("TIDEmploymentLeave");
    WorkflowComment         initialNote = "";
    HcmEmploymentLeave      HcmEmploymentLeave;
    FormDataSource          HcmEmploymentLeave_ds;
    EPWorkflowControlContext workflowControlContext;
    str                     menuItemName = _args.menuItemName();


    //from rich client
    if(menuItemName == menuitemactionstr(TIDEmploymentLeaveSubmitMenuItem) || menuItemName == menuitemActionStr(HRSLeaveCreditSubmitMenuItem))
    {
        // Workflow is starting from the Windows client. This can be determined
        // because the menu item for submitting the workflow on the Windows
        // client was chosen by the user.

        // Opens the submit to workflow dialog.
        workflowSubmitDialog = WorkflowSubmitDialog::construct(_args.caller().getActiveWorkflowConfiguration());
        workflowSubmitDialog.run();

        if (workflowSubmitDialog.parmIsClosedOK())
        {
            // Find what record from the Work Orders table is being submitted to workflow.
            recId = _args.record().RecId;

            // Get comments from the submit to workflow dialog.
            initialNote = workflowSubmitDialog.parmWorkflowComment();

            try
            {
                // Update the record in the FCMWorkOrders table that is being submitted to workflow.
                // The record is moved to the 'submitted' state.
                ttsBegin;
                HcmEmploymentLeave = _args.record();
                HcmEmploymentLeave.selectForUpdate(true);
                HcmEmploymentLeave.Status = HRSLeaveStatus::Submitted;
                HcmEmploymentLeave.update();
                // Activate the workflow.
                workflowCorrelationId = Workflow::activateFromWorkflowType(workflowTypeName, recId, initialNote, NoYes::No);

                 ttsCommit;

                // Updates the workflow button to diplay Actions instead of Submit.
                _args.caller().updateWorkflowControls();
            }

            catch(exception::Error)
            {
                info("Error on workflow activation.");
            }
        }
    }
    //from EP
    else
    {
         // Workflow is starting from Enterprise Portal.

         // Retrieve the workflow controller context from the arguments passed in.
         workflowControlContext = _args.caller();

         // Find what record from the Work Orders table is being submitted to workflow.
         recId = _args.record().RecId;

         // Get comments from the submit to workflow dialog.
         initialNote = workflowControlContext.getWorkflowComment();

        try
        {
            ttsBegin;
            workflowCorrelationId = Workflow::activateFromWorkflowType(workflowTypeName, recId, initialNote, NoYes::Yes);
            HcmEmploymentLeave = _args.record();
            HcmEmploymentLeave.selectForUpdate(true);
            HcmEmploymentLeave.Status = HRSLeaveStatus::Submitted;
            HcmEmploymentLeave.update();
            ttsCommit;
        }
        catch(exception::Error)
        {
            info("Error on workflow activation.");
        }
    }
}

convert null to 0 type BIGINT (INT64 on AX) when outer join on view using SysComputedColumn

public static server str DefaultDimension()
{

    str field = SysComputedColumn::returnField(tableStr(HRSJobPositionDefaultDimension),
                    identifierStr(HcmPositionDefaultDimension),
                    fieldStr(HcmPositionDefaultDimension, DefaultDimension));
    
    return SysComputedColumn::if(
        SysComputedColumn::isNullExpression(field),
        SysComputedColumn::cast("0",'BIGINT'),  
        SysComputedColumn::cast(field,'BIGINT'));
}

add new default dimension (financial dimension) on new table and form example tutorial

This example tutorial will guide you to add new default dim on table and forms.

1. Add new field with type Int64 and set the properties.
Capture

2. Add relation on table relation and set the properties.
Capture

3. on class declaration form , add code below :

public class FormRun extends ObjectRun
{
    DimensionDefaultingController   dimensionDefaultingController;
}

4. add tab page Financial Dimension on form design , and set properties like img below
Capture

5. on TabFinancialDimensions , override method pageActivated

public void pageActivated()
{
    dimensionDefaultingController.pageActivated();

    super();
}

6. on init form, like code below :

public void init()
{
    super();
    dimensionDefaultingController = DimensionDefaultingController::constructInTabWithValues(true, true, true, 0, this, TabFinancialDimensions, "@SYS138487", curext());
    dimensionDefaultingController.parmAttributeValueSetDataSource(HRSJobPowerPlan_ds, fieldStr(HRSJobPowerPlan, DefaultDimension));
}

7. finnaly, on datasource form method override method :
on active()

public int active()
{
    int ret;

    ret = super();
    dimensionDefaultingController.activated();

    return ret;
}

on write()

public void write()
{
    dimensionDefaultingController.writing();
    super();
}

on delete()

public void delete()
{
    dimensionDefaultingController.deleted();
    super();
}

8. preview example form:
Capture

calculate field on view with SysComputedColumn example code

public static server str getOutstanding()
{
    return strFmt("Sum(%1) + Sum(%2)",
            SysComputedColumn::returnField(
            tableStr(TIDIndentOrderUninvoiceTrans),
            identifierStr(TIDIndentOrderPaymentTrans),
            fieldStr(TIDIndentOrderPaymentTrans, amountTendered)),
            SysComputedColumn::returnField(
            tableStr(TIDIndentOrderUninvoiceTrans) ,
            identifierStr(TIDIndentOrderPaymentTrans),
            fieldStr(TIDIndentOrderPaymentTrans, grossAmount)));
}

add group by and ranges to Queries Forms example code

on form class declaration

public class FormRun extends ObjectRun
{
    QueryBuildRange             indentTransDateRange;
    QueryBuildRange             invoiceDateRange;

    QueryBuildRange             customerRange;
    QueryBuildRange             storeRange;
}

on init form method

public void init()
{
    super();

    indentTransDateRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldnum(TIDIndentOrderUninvoiceTrans, PaymentTransDate));

    invoiceDateRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldnum(TIDIndentOrderUninvoiceTrans, InvoicePostingDate));

    customerRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tablenum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldnum(TIDIndentOrderUninvoiceTrans, CustAccount));

    storeRange = TIDIndentOrderUninvoiceTrans_ds.query().dataSourceTable(tableNum(TIDIndentOrderUninvoiceTrans))
    .addRange(fieldNum(TIDIndentOrderUninvoiceTrans, Store));

    TransactionDate.dateValue(systemdateGet());
}

on execute Query datasource form

public void executeQuery()
{
    this.Query().clearGroupBy();

    indentTransDateRange.value(strFmt('<%1',(TransactionDate.dateValue() + 1)));
    invoiceDateRange.value(strFmt('>%1',TransactionDate.dateValue()));

    customerRange.value(strFmt(customer.text()+'*'));
    storeRange.value(strFmt(StoreId.text()+'*'));

    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,Store));
    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,CustAccount));
    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,SalesId));
    this.Query().DataSourceNo(1).addGroupByField(fieldNum(TIDIndentOrderUninvoiceTrans,GrossAmount));
    this.Query().DataSourceNo(1).addSelectionField(fieldNum(TIDIndentOrderUninvoiceTrans,SumOfamountTendered),SelectionField::Sum);
    this.Query().DataSourceNo(1).addSelectionField(fieldNum(TIDIndentOrderUninvoiceTrans,OutstandingAmount),SelectionField::Sum);

    super();
}

Capture

Formating cells when export to excel example code

private void setInvoiceHeader(SysExcelWorkSheet    xlsWorkSheet,CustInvoiceJour custInvoiceJour)
{
    xlsWorkSheet.cells().item(row,1).value("FK");
    xlsWorkSheet.cells().item(row,2).value(str2num(CustTable::find(custInvoiceJour.InvoiceAccount).TECFakturPajakTypeId));
    xlsWorkSheet.cells().range(strFmt("B%1:B%1",row)).numberFormat("00");
    xlsWorkSheet.cells().item(row,3).value(enum2int(custInvoiceJour.TECIsSubtitute));
    xlsWorkSheet.cells().item(row,4).value(str2num(strFmt("%1", strRem(custInvoiceJour.TECFakturPajakListId, ".-"))));
    xlsWorkSheet.cells().range(strFmt("D%1:D%1",row)).numberFormat("0000000000000");
    xlsWorkSheet.cells().item(row,5).value(mthOfYr(custInvoiceJour.InvoiceDate));
    xlsWorkSheet.cells().item(row,6).value(year(custInvoiceJour.InvoiceDate));
    xlsWorkSheet.cells().item(row,7).value(date2str(custInvoiceJour.InvoiceDate, 213, 1, 4, 1, 4, 4));
    xlsWorkSheet.cells().item(row,8).value(str2num(strFmt("%1", strRem(custInvoiceJour.custTable_InvoiceAccount().TECNPWP, ".-"))));
    xlsWorkSheet.cells().range(strFmt("H%1:H%1",row)).numberFormat("00000000000000");
    xlsWorkSheet.cells().item(row,9).value(CustTable::find(custInvoiceJour.InvoiceAccount).name());
    xlsWorkSheet.cells().item(row,10).value(strRTrim(CustTable::find(custInvoiceJour.InvoiceAccount).postalAddress().Street));
    xlsWorkSheet.cells().item(row,11).value(round(custInvoiceJour.SalesBalance,0));
    xlsWorkSheet.cells().range(strFmt("K%1:K%1",row)).numberFormat("0");
    xlsWorkSheet.cells().item(row,12).value(round(custInvoiceJour.SumTax,0));
    xlsWorkSheet.cells().range(strFmt("L%1:L%1",row)).numberFormat("0");
    xlsWorkSheet.cells().item(row,13).value(0);
    xlsWorkSheet.cells().item(row,14).value("");
    xlsWorkSheet.cells().item(row,15).value(0);
    xlsWorkSheet.cells().item(row,16).value(0);
    xlsWorkSheet.cells().item(row,17).value(0);
    xlsWorkSheet.cells().item(row,18).value(0);
    xlsWorkSheet.cells().item(row,19).value(custInvoiceJour.InvoiceId);
    row++;
    totalCount++;
}

Capture

more example and source :
http://patrikluca.blogspot.cz/2010/04/export-to-excel-with-x-code-sequel.html

Job upload Fixed Asset Journal

static void TIDF_UploadFAJournal(Args _args)
{
    container                       accEntryPattern;
    container                       offSetEntryPattern;
    //for default dimension
    container                       financialDimensionFromExcel;
    DimensionDefault                defaultDimension;
    int                             countLedger;
    int                             countCon;

    AssetBookTable                  _AssetBookTable;
    AssetBookId                     _AssetBookId;
    LedgerJournalTrans_Asset        _LedgerJournalTrans_Asset;
    LedgerJournalTrans              _LedgerJournalTrans;
    LedgerJournalTable              _LedgerJournalTable;

    AssetBookTableDerived           _AssetBookTableDerived;
    AssetBookTableDerivedJournal    _AssetBookTableDerivedJournal;
    AssetTransTypeJournal           _AssetTransTypeJournal;
    AssetTransType                  _AssetTransType;

    LedgerJournalACType             LedgerJournalACType;
    ledgerJournalName               ledgerJournalName;
    journalID                       JournalID;
    LedgerJournalId                 _journalId;
    axLedgerJournalTable            axLedgerJournalTable;
    axledgerJournalTrans            axLedgerJournalTrans;
    LedgerJournalType               JournalType = ledgerJournalType::Daily;


    TransDate                       _TransDate;
    LedgerTransType                 _LedgerTransType;
    str                             AccountNum;
    LedgerJournalACType             _AccountType;
    CurrencyCode                    _CurrencyCode;
    real                            _ExhcangeRate;
    real                            _AmountCurDebit;
    real                            _AmountCurCredit;
    str                             _InvoiceId;
    TransTxt                        _TransTxt;
    DueDate                         _DueDate;
    DocumentNum                     _DoucumentNum;
    DocumentDate                    _DocumentDate;
    str                             Branch;
    str                             Departement;
    str                             Product;
    str                             Project;
    TaxGroupJournal                 _TaxJournal;
    TaxGroupJournal                 _TaxItemJournal;
    NoYes                           _Transferred;
    BankTransactionType             _BankTransactionType;
    TaxCode                         _TaxCode;
    LedgerJournalACType             _OffsetAccountType;
    str                             _OffsetAcountNum;
    VendPostingProfile              _PostingProfile;


    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename, numSeq;
    str                                         strAccount,strAccountOffset;
    Name                                        dimensionName1,dimensionName2,dimensionName3,dimensionName4;
    str                                         MainAcc,Dim1,Dim2,Dim3,Dim4,OffsetAcc;
    str                                         AccountType,OffsetAccountType;
    str                                         PayReff,BankTransType,DocNo,PostingProfile,noInvoice, transType;
    TransDate                                   DocDate;
    str                                         StrDate;

    boolean         _return = true;
    container dimensionAttrName = Global::TIDgetDimensionName();
    _OffsetAcountNum    = '9900-0000';
    startLengthyOperation();
    filename = @"C:\\#Tectura#\fanddy\UploadFAJournal";//FA Dep Commercial.xlsx";//Depreciation commercial
    //filename = _filePath;
    try
    {
        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();

            row = 6;

            ttsBegin;

            while (cells.item(row,5).value().bStr() != '' )
            {
                //financial dimension
                financialDimensionFromExcel = conNull();
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,10).value().bStr();
                if(cells.item(row,10).value().bStr() != '' ) {countLedger++;}
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,11).value().bStr();
                if(cells.item(row,11).value().bStr() != '' ) {countLedger++;}
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,12).value().bStr();
                if(cells.item(row,12).value().bStr() != '' ) {countLedger++;}
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,13).value().bStr();
                if(cells.item(row,13).value().bStr() != '' ) {countLedger++;}
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,14).value().bStr();
                if(cells.item(row,14).value().bStr() != '' ) {countLedger++;}
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,15).value().bStr();
                if(cells.item(row,15).value().bStr() != '' )  {countLedger++;}

                //start from A
                _journalId          = cells.item(row,1).value().bStr();
                _TransDate          = cells.item(row,2).value().date();
                AccountNum          = cells.item(row,3).value().bStr();
                transType           = cells.item(row,4).value().bStr();
                _AssetBookId        = cells.item(row,5).value().bStr();
                _CurrencyCode       = cells.item(row,6).value().bStr();
                _ExhcangeRate       = cells.item(row,7).value().double();
                _AmountCurDebit     = cells.item(row,8).value().double();
                _AmountCurCredit    = cells.item(row,9).value().double();

                //_TransDate          = str2Date(StrDate, 123);
                defaultDimension    = TIDcreateDefaultDimension(dimensionAttrName,financialDimensionFromExcel);

                offSetEntryPattern =
                [   _OffsetAcountNum,
                    _OffsetAcountNum,
                    countLedger
                ];
                countCon = 1;
                while(countCon <= countLedger)
                {
                    offSetEntryPattern += conPeek(dimensionAttrName,countCon);
                    offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                    countCon++;
                }

                _AssetTransType             = str2enum(_AssetTransType, transType);
                _AssetTransTypeJournal      = str2enum(_AssetTransTypeJournal, transType);

                _LedgerJournalTable         = LedgerJournalTable::find(_journalId);
                if (_LedgerJournalTable)
                {
                    _LedgerJournalTrans.clear();
                    _LedgerJournalTrans.JournalNum          = _journalId;
                    _LedgerJournalTrans.TransDate           = _TransDate;
                    _LedgerJournalTrans.AccountType         = LedgerJournalACType::FixedAssets;
                    _LedgerJournalTrans.LedgerDimension     = DimensionStorage::getDynamicAccount(AccountNum, LedgerJournalACType::FixedAssets);
                    _LedgerJournalTrans.DefaultDimension    = defaultDimension;
                    _LedgerJournalTrans.CurrencyCode        = _CurrencyCode;
                    _LedgerJournalTrans.ExchRate            = _ExhcangeRate;
                    _LedgerJournalTrans.PostingProfile      = _PostingProfile;

                    if(_AmountCurDebit != 0)
                    {
                        _LedgerJournalTrans.AmountCurDebit  = _AmountCurDebit;
                    }

                    if(_AmountCurCredit != 0)
                    {
                        _LedgerJournalTrans.AmountCurCredit = _AmountCurCredit;
                    }

                    if(_OffsetAcountNum != "")
                    {
                        _LedgerJournalTrans.OffsetAccountType       = LedgerJournalACType::Ledger;
                        _LedgerJournalTrans.OffsetLedgerDimension   = AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern);
                    }

                    _LedgerJournalTrans.Voucher = NumberSeq::newGetNumFromId(_LedgerJournalTable.ledgerJournalName().NumberSequenceTable).num();
                    _LedgerJournalTrans.insert();

                    if (_LedgerJournalTrans)
                    {
                        if (_AssetBookId)
                        {
                            _AssetBookTable = AssetBookTable::find(_AssetBookId);

                            if (_AssetBookTable)
                            {
                                _LedgerJournalTrans_Asset.clear();
                                _LedgerJournalTrans_Asset.RefRecId      = _LedgerJournalTrans.RecId;
                                _LedgerJournalTrans_Asset.AssetId       = AccountNum;
                                _LedgerJournalTrans_Asset.TransType     = _AssetTransTypeJournal;
                                _LedgerJournalTrans_Asset.BookId        = _AssetBookTable.BookId;
                                _LedgerJournalTrans_Asset.insert();
                            }



                            select firstOnly _AssetBookTableDerived
                                where _AssetBookTableDerived.BookId == _AssetBookId
                                   && _AssetBookTableDerived.AssetTransType == _AssetTransType;

                            if (_AssetBookTableDerived)
                            {
                                _AssetBookTableDerivedJournal.clear();
                                _AssetBookTableDerivedJournal.RefRecId  = _LedgerJournalTrans.RecId;
                                _AssetBookTableDerivedJournal.AssetBookId = _AssetBookTableDerived.BookIdDerived;

                                if (_LedgerJournalTrans.AmountCurCredit > 0)
                                {
                                    _AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurCredit;
                                }
                                else if (_LedgerJournalTrans.AmountCurDebit > 0)
                                {
                                    _AssetBookTableDerivedJournal.AmountCur = _LedgerJournalTrans.AmountCurDebit;
                                }

                                _AssetBookTableDerivedJournal.insert();
                            }

                        }
                    }
                    print strFmt('Row Number %1 - Asset ID : %2', row,AccountNum);
                    row++;
                }
            }

            info(strFmt('Imported  %1 Items, Journal Numbers : %2',row ,_journalId));
             ttsCommit;
        }
    }
    catch(Exception::Error)
    {
        _return = false;
    }

    application.quit();
}

Job upload General Journal example code 2

static void TIDF_UploadJournal(Args _args)
{
    container   accEntryPattern;
    container   offSetEntryPattern;
    container   financialDimensionFromExcel;
    container   conDimensionName;
    int         countCon;
    int         countLedger;
    RecId       recIdDefaultDimension;

    DimensionDynamicAccount parmLedgerDimension;
    DimensionDynamicAccount parmOffsetLedgerDimension;

    LedgerJournalTable   ledgerJournalTable;
    LedgerJournalACType  LedgerJournalACType;
    ledgerJournalName    ledgerJournalName;
    journalID            JournalID;
    axLedgerJournalTable axLedgerJournalTable;
    axledgerJournalTrans axLedgerJournalTrans;
    LedgerJournalType JournalType = ledgerJournalType::Daily;


    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename, numSeq;

    boolean         _return = true;
    ;

    startLengthyOperation();
    filename = "C:\\test.xlsx";
    JournalID = "G0615-0040";// <<< JOURNALNUM
    //filename = _filePath;

    try
    {
        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();

            row = 5;

            ledgerJournalTable = ledgerJournalTable::find(JournalID);
            conDimensionName = Global::TIDgetDimensionName();

            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //empty container
                financialDimensionFromExcel = conNull();
                countLedger = 0;

                //Create Trans ...
                axLedgerJournalTrans = new axLedgerJournalTrans();
                axLedgerJournalTrans.parmJournalNum(JournalID);
                //start from A
                axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date());
                axLedgerJournalTrans.parmAccountType(str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr()));
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,17).value().bStr();
                if(cells.item(row,17).value().bStr() != "") {countLedger++;}
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,18).value().bStr();
                if(cells.item(row,18).value().bStr() != "") {countLedger++;}
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,19).value().bStr();
                if(cells.item(row,19).value().bStr() != "") {countLedger++;}
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,20).value().bStr();
                if(cells.item(row,20).value().bStr() != "") {countLedger++;}
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,21).value().bStr();
                if(cells.item(row,21).value().bStr() != "") {countLedger++;}
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,22).value().bStr();
                if(cells.item(row,22).value().bStr() != "")  {countLedger++;}

                //ledger Dimension
                if(axLedgerJournalTrans.parmAccountType() == LedgerJournalACType::Ledger)
                {
                    accEntryPattern =
                    [   cells.item(row, 3).value().bStr(),
                        cells.item(row, 3).value().bStr(),
                        countLedger
                    ];

                    countCon = 1;
                    while(countCon <= countLedger)
                    {
                        accEntryPattern += conPeek(conDimensionName,countCon);
                        accEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                        countCon++;
                    }
                    parmLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern));
                }
                else
                {
                    parmLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 3).value().bStr(),axLedgerJournalTrans.parmAccountType());
                }
                AxLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, LedgerDimension), false);
                axLedgerJournalTrans.parmLedgerDimension(parmLedgerDimension);

                //offset dimension
                axLedgerJournalTrans.parmOffsetAccountType(str2Enum(LedgerJournalACType, cells.item(row,8).value().bStr()));
                if(axLedgerJournalTrans.parmOffsetAccountType() == LedgerJournalACType::Ledger)
                {
                    offSetEntryPattern =
                    [   cells.item(row, 9).value().bStr(),
                        cells.item(row, 9).value().bStr(),
                        countLedger
                    ];

                    countCon = 1;
                    while(countCon <= countLedger)
                    {
                        offSetEntryPattern += conPeek(conDimensionName,countCon);
                        offSetEntryPattern += conPeek(financialDimensionFromExcel,countCon);
                        countCon++;
                    }
                    parmOffsetLedgerDimension = axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern));
                }
                else
                {
                    parmOffsetLedgerDimension = DimensionStorage::accountNum2LedgerDimension(cells.item(row, 9).value().bStr(),axLedgerJournalTrans.parmAccountType());
                }
                
                axLedgerJournalTrans.clearField(fieldNum(ledgerJournalTrans, offsetLedgerDimension), false);
                axLedgerJournalTrans.parmOffsetLedgerDimension(parmOffsetLedgerDimension);
                if(cells.item(row,5).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double());
                }

                if(cells.item(row,6).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double());
                }

                axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr());
                axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr());
                axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double());
                axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr());
                axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr());
                axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr());
                axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr());
                axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date());
                //
                axLedgerJournalTrans.parmPostingProfile(cells.item(row,23).value().bStr());

                recIdDefaultDimension = Global::TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
                axLedgerJournalTrans.parmDefaultDimension(recIdDefaultDimension);
                axLedgerJournalTrans.parmOffsetDefaultDimension(recIdDefaultDimension);

                axLedgerJournalTrans.save();
                row++;
            }
               ttsCommit;

            info('Success !!');
            application.quit();
        }
    }
    catch(Exception::Error)
    {
        info("Journal Upload Error");
        application.quit();
    }

}

Job upload fixed asset ( AssetTable and AssetBook) example code

static void TIDF_uploadFAwithValueModels(Args _args)
{
    AssetId             assetId;
    AssetGroupId        assetGroupId;
    AssetName           assetName;
    AssetLocationId     assetLocationId;
    AssetServiceLife    assetServiceLife;
    AssetLifeTimeRest   assetLifeTimeRest;
    AssetPostingProfile assetPostingProfile;
    AssetBookId         assetBookId;
    TransDate           depreciationStartDate,LastDepreciationDate,AcquisitionDate;
    RecId               recIdDefaultDimension;
    
    AxAssetTable        axAssetTable;
    
    //table
    AssetTable          assetTable;
    AssetGroup          assetGroup;
    AssetLocation       assetLocation;
    AssetBook           assetBook;
    AssetLedger         assetLedger;
    
    container   financialDimensionFromExcel;
    container   conDimensionName;
    
    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename;
    ;
    
    startLengthyOperation();
    //variable yang diisi manual
    filename              = "C:\\FA master.xlsx";
    assetPostingProfile   = "FA POSTING";
    depreciationStartDate = today();
    LastDepreciationDate  = today();
    AcquisitionDate       = today();
    
    try
    {
        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();
            conDimensionName = TIDgetDimensionName();

            row = 5;
            
            ttsBegin;
            while (cells.item(row,2).value().bStr() != "")
            {
                //financial dimension
                financialDimensionFromExcel = conNull();
                //dimension value 1
                financialDimensionFromExcel += cells.item(row,7).value().bStr();
                //dimension value 2
                financialDimensionFromExcel += cells.item(row,8).value().bStr();
                //dimension value 3
                financialDimensionFromExcel += cells.item(row,9).value().bStr();
                //dimension value 4
                financialDimensionFromExcel += cells.item(row,10).value().bStr();
                //dimension value 5
                financialDimensionFromExcel += cells.item(row,11).value().bStr();
                //dimension value 6
                financialDimensionFromExcel += cells.item(row,12).value().bStr();
                //generateDefaultDimension
                recIdDefaultDimension       = TIDcreateDefaultDimension(conDimensionName,financialDimensionFromExcel);
                
                //variable
                assetId           = TIDComVariant2STR(cells.item(row,2).value());
                assetGroupId      = TIDComVariant2STR(cells.item(row,1).value());
                assetLocationId   = TIDComVariant2STR(cells.item(row,6).value());
                assetName         = TIDComVariant2STR(cells.item(row,3).value());
                assetServiceLife  = str2num(TIDComVariant2STR(cells.item(row,4).value()));
                assetLifeTimeRest = str2num(TIDComVariant2STR(cells.item(row,5).value()));
                assetBookId       = TIDComVariant2STR(cells.item(row,13).value());
                //table
                assetTable      = assetTable::find(assetId);
                assetGroup      = AssetGroup::find(assetGroupId);
                assetLocation   = AssetLocation::find(assetLocationId);
                
                //validation
                if(!assetGroup || !assetLocation)
                {
                    throw error(strFmt("Row : %1 ,Asset group or asset location not existed, please check again",row));
                }
                //create asset Table
                if(!assetTable)
                {
                    axAssetTable    = new AxAssetTable();
                    axAssetTable.parmAssetId(assetId);
                    axAssetTable.parmAssetGroup(assetGroupId);
                    axAssetTable.parmName(assetName);
                    axAssetTable.parmLocation(assetLocationId);
                    axAssetTable.save();
                }
                
                //create assetBook
                assetBook = assetBook::find(assetId,assetBookId);
                if(!assetBook)
                {
                    assetBook.clear();
                    assetBook.initValue();
                    assetBook.AssetId               = assetId;
                    assetBook.BookId                = assetBookId;
                    assetBook.PostingProfile        = assetPostingProfile;
                    assetBook.ServiceLife           = assetServiceLife;
                    assetBook.LifeTime              = assetServiceLife*12;
                    assetBook.LifeTimeRest          = assetLifeTimeRest;
                    assetBook.DepreciationStartDate = depreciationStartDate;
                    assetBook.LastDepreciationDate  = LastDepreciationDate;
                    assetBook.AcquisitionDate       = AcquisitionDate;
                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.insert();
                }
                else
                {
                    assetBook.selectForUpdate(true);
                    assetBook.ServiceLife           = assetServiceLife;
                    assetBook.LifeTime              = assetServiceLife*12;
                    assetBook.LifeTimeRest          = assetLifeTimeRest;
                    assetBook.DepreciationStartDate = depreciationStartDate;
                    assetBook.LastDepreciationDate  = LastDepreciationDate;
                    assetBook.AcquisitionDate       = AcquisitionDate;
                    assetBook.DefaultDimension      = recIdDefaultDimension;
                    assetBook.update();
                }
                
              
                row++;
            }
            ttsCommit;
            application.quit();
        }
    }    
    catch(Exception::Error)
    {
        info("Fixed Asset upload Error");
        application.quit();
    }
}

TIDGetDimensionName

public static container TIDgetDimensionName()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &&
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}

TIDcreateDefaultDimension

static DimensionDefault TIDcreateDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
    DimensionAttributeValueSetStorage   valueSetStorage = new DimensionAttributeValueSetStorage();
    DimensionDefault                    result;
    int                                 i;
    DimensionAttribute                  dimensionAttribute;
    DimensionAttributeValue             dimensionAttributeValue;
    //_attr is dimension name in table DimensionAttribute
    container               conAttr =   _attr;
    container               conValue = _value;
    str                     dimValue;

    for (i = 1; i <= conLen(conAttr); i++)
    {
        dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

        if (dimensionAttribute.RecId == 0)
        {
            continue;
        }

        dimValue = conPeek(conValue,i);

        if (dimValue != "")
        {
            // _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
            dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

            // Add the dimensionAttibuteValue to the default dimension
            valueSetStorage.addItem(dimensionAttributeValue);
        }
    }
    result = valueSetStorage.save();
    return result;
}

TIDComVariant2STR

public static str TIDComVariant2STR(COMVariant _variant)
{
    str valueStr;
    ;

    switch(_variant.variantType())
    {
        case COMVariantType::VT_EMPTY   :
            valueStr = '';
            break;

        case COMVariantType::VT_BSTR    :

            valueStr = _variant.bStr();
            break;

        case COMVariantType::VT_R4      :
        case COMVariantType::VT_R8      :

            if(_variant.double())
            {
                valueStr = strFmt("@SYS311964",
                                    num2Str0(_variant.double(), 0),
                                    num2str(_variant.double(),
                                    0,
                                    numOfDec(_variant.double()),
                                    1,
                                    0));
            }
            break;

        default                         :
            throw error(strfmt("@SYS26908",
                                _variant.variantType()));
    }

    return valueStr;
}

source :
http://krishhdax.blogspot.com/2012/05/ax2012-import-fixed-assets-table.html
https://axblog4u.wordpress.com/2012/09/06/tip-comvarianttype-for-real-values-in-dynamics-ax/

Convert null date from outer join on view to maxDate with SysComputedColumn

Example Code :

public static server str ReleasePostingDate()
{

    return SysComputedColumn::if(
            SysComputedColumn::isNullExpression(SysComputedColumn::returnField(
            tableStr(TIDAPUninvoiceConsignmentItem) ,
            identifierStr(LedgerJournalTableRealease),
            fieldStr(LedgerJournalTable, PostedDateTime))),
            SysComputedColumn::cast(strFmt("'%1'",DateTimeUtil::date(DateTimeUtil::maxValue())),'NVARCHAR'),
            SysComputedColumn::cast(SysComputedColumn::returnField(
            tableStr(TIDAPUninvoiceConsignmentItem) ,
            identifierStr(LedgerJournalTableRealease),
            fieldStr(LedgerJournalTable, PostedDateTime)),'NVARCHAR')
            );
}

create Address , Phone , Email for Worker example code

Static void WorkerHireAddressUpdate(HCMworker _worker,StreetName _address = "",str _Phone = "",str _email = "")
{
    
    HcmWorker                   hcmWorker;
    DirPersonRecId              _dirPersonRecid;
    DirPartyPostalAddressView   addressView;
    dirPartyContactInfoView     contactView;
    DirParty                    dirParty;
    container                   roles;
    
    HcmWorker = _worker;
    _dirPersonRecid = HcmWorker.Person;
    roles  = [LogisticsLocationRole::findBytype(LogisticsLocationRoleType::Home).RecId];
    
    //create address
    DirParty = DirParty::constructFromPartyRecId(_dirPersonRecid);
    if(_address)
    {
        //addressView.LocationName  
        //addressView.City    = 
        //addressView.State   = 
        //addressView.ZipCode =
        
        addressView.CountryRegionId = LogisticsAddressCountryRegion::findByISOCode(SysCountryRegionCode::countryInfo(curext())).CountryRegionId;
        addressView.Street  = _address;
        addressView.IsPrimary   = NoYes::Yes;
        addressView.Party   = _dirPersonRecid;
        DirParty.createOrUpdatePostalAddress(addressView,roles);
    }
    
    //create primary email
    if(_email)
    {
        contactView.clear();
        contactView.LocationName                = 'Primary Email';
        contactView.Locator                     = _email;
        contactView.Type                        = LogisticsElectronicAddressMethodType::Email;
        contactView.Party                       = _dirPersonRecid;
        contactView.IsPrimary                   = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }
    
    //create primary phone
    if(_Phone)
    {
        contactView.clear();
        contactView.LocationName                = 'Primary Phone';
        contactView.Locator                     = _Phone;
        contactView.Type                        = LogisticsElectronicAddressMethodType::Phone;
        contactView.Party                       = _dirPersonRecid;
        contactView.IsPrimary                   = NoYes::Yes;
        dirParty.createOrUpdateContactInfo(contactView);
    }

}

Get all financial dimension value and description from DimensionAttribute

Capture

static void TIDF_getDimensionValue(Args _args)
{
    DimensionAttribute              dimAttr;
    DimensionAttributeViewContract  dimAttrViewContract;
    Query                           q;
    QueryRun                        qR;
    QueryBuildDataSource            qbds;
    Common                          common;

    dimAttr = DimensionAttribute::findByName("1. STORE");


    dimAttrViewContract =  DimensionAttribute::getViewContract(dimAttr.RecId);

    q = new Query();

    changecompany (dimAttr.company())
    {
        qbds = q.addDataSource(dimAttrViewContract.parmViewId());
        qbds.addSortField(dimAttrViewContract.parmValueFieldId(),SortOrder::Ascending);

         // Apply ranges on language ID fields.
        DimensionAttribute::addTranslViewRangesToBackingEntityQuery(dimAttr.RecId, qbds);

        if (DimensionCache::instance().dimensionAttributeHasCategorization(dimAttr.RecId))
        {
            if (dimAttr.Type == DimensionAttributeType::MainAccount)
            {
                qbds.addRange(dimAttrViewContract.parmCategoryFieldId()).value(queryValue(LedgerChartOfAccounts::current()));
            }
            else if (dimAttr.Type == DimensionAttributeType::CustomList)
            {
                qbds = qbds.addDataSource(tablenum(DimensionAttributeDirCategory), 'DimAttDirCat');
                qbds.relations(true);
                qbds.joinMode(JoinMode::InnerJoin);
                qbds.fetchMode(QueryFetchMode::One2One);
                qbds.addLink(dimAttrViewContract.parmCategoryFieldId(), fieldnum(DimensionAttributeDirCategory, DirCategory));
                qbds.addRange(fieldnum(DimensionAttributeDirCategory, DimensionAttribute)).value(strfmt('%1', dimAttr.RecId));
            }
        }
    }

    qR = new QueryRun(q);
    while(qR.next())
    {
        common = qR.get(dimAttrViewContract.parmViewId());

        info(strFmt("%1 %2",common.getFieldValue(dimAttrViewContract.parmValueFieldName()), common.getFieldValue(dimAttrViewContract.parmNameFieldName())));
    }

}

Auto settle from custTransOpen when create journal payment lines via x++

example code :

public static void autoSettlementJournalFromPAYDetails(JournalId    _journalNum)
{
    CustVendOpenTransManager manager;
    CustTrans                custTrans;
    CustTransOpen            custTransOpen;
    LedgerJournalTrans       ledgerJournalTrans;
    AxLedgerJournalTable     axLedgerJournalTable;
    AxLedgerJournalTrans     axLedgerJournalTrans;
    ExchangeRateHelper       exchangeRateHelper;
    AmountCur                totalSettleAmount;
    AmountCur                amountCurDebit,amountCurCredit;
    HRSPayJournalDetails     hrsPayJournalDetails;
    HRSPayJournalTable       hrsPayJournalTable;
    
    int                      Row;


    AxLedgerJournalTable = AxLedgerJournalTable::newLedgerJournalTable(LedgerJournalTable::find(_journalNum));
    ttsBegin;
    //details journal lines from Pay Journal Details
    while select hrsPayJournalTable
    join hrsPayJournalDetails
    where
        hrsPayJournalTable.HRSPayJournalId == hrsPayJournalDetails.HRSPayJournalId
    &&  HRSPayJournalTable.JournalNum      == _journalNum
    {
        CustTrans    = CustTrans::findFromInvoice(hrsPayJournalDetails.ReferenceNumber,HRSPayJournalDetails.AccountNum);
        CustTransOpen  = CustTransOpen::findRefId(CustTrans.RecId);

        if(CustTransOpen)
        {

           //Create Trans ...
            axLedgerJournalTrans = new axLedgerJournalTrans();
            axLedgerJournalTrans.parmJournalNum(AxLedgerJournalTable.ledgerJournalTable().JournalNum);

            axLedgerJournalTrans.parmTransDate(today());
            axLedgerJournalTrans.parmDue(today());
            axLedgerJournalTrans.parmAccountType(LedgerJournalACType::Cust);
            axLedgerJournalTrans.parmLedgerDimension(DimensionStorage::getDynamicAccount(hrsPayJournalDetails.AccountNum, LedgerJournalACType::Cust));
            axLedgerJournalTrans.parmAmountCurCredit(hrsPayJournalDetails.Credit);
            axLedgerJournalTrans.parmAmountCurDebit(hrsPayJournalDetails.Debit);
            axLedgerJournalTrans.save();

            manager = CustVendOpenTransManager::construct(axLedgerJournalTrans.ledgerJournalTrans());
            manager.updateTransMarked(custTransOpen,true);
            if(hrsPayJournalDetails.Debit)
            {
                manager.updateSettleAmount(custTransOpen,hrsPayJournalDetails.Debit);
            }
            else
            {
                manager.updateSettleAmount(custTransOpen,hrsPayJournalDetails.Credit);
            }

            //update after settle
            ledgerJournalTrans = axLedgerJournalTrans.ledgerJournalTrans();
            ledgerJournalTrans.selectForUpdate(true);
            ledgerJournalTrans.SettleVoucher =  SettlementType::SelectedTransact;
            ledgerJournalTrans.update();
            Row++;
        }

    }
    ttsCommit;
    
    info(strFmt("Total Lines settled : %1",Row));

}

Join ItemName from (EcoResProductTranslation ) and search name from InventTable to OnHand Form for search matter

it is common for user to search item based on name. but currently on AX 2012, onHand form can’t provide search based on item name or search name. It will take a little modification from technical side.

example how to do it :

1. you can join directly on form datasource or create view like example (i’m using the view that already created for another purpose)
Capture

2. on InventSumTable add relations like this.
Capture

3. then go to form onHand (InventOnHandItem), and add the view to datasource
Capture

4. open class InventDimCtrl_Frm_OnHand and modify method modifyQuery , on class declaration parameter, add new parameter FormDataSource with default null parameter. then define new variable QueryBuildDataSource.
Capture

5. on the last line of the method modifyQuery , add code like example :

if(_tidInventLookUp)
{
    qbsInventLookup = query.dataSourceName(_tidInventLookUp.name());

    //filter current company language
    qbr = SysQuery::findOrCreateRange(qbsInventLookup, fieldnum(TIDInventLookup, LanguageId));
    qbr.value(queryValue(CompanyInfo::languageId()));

    qbsInventLookup.addGroupByField(fieldNum(TIDInventLookup,Name));
    qbsInventLookup.addGroupByField(fieldNum(TIDInventLookup,NameAlias));

}

5. then back to the form, on datasource inventSUm, on executeQuery , modify the code from

void executeQuery()
{
    element.inventDimSetupObject().modifyQuery(inventSum_DS,inventDim_DS);

    super();
}

to

void executeQuery()
{
    element.inventDimSetupObject().modifyQuery(inventSum_DS,inventDim_DS,TIDInventLookUp_ds);

    super();
}

6. drag the field from newly add datasource on form to the grid :
Capture

7. Voila done, example result, :
Capture

note: remember to GROUPBY on code if you add new field to the form, or the field on form will show “UNRETRIEVED”

Change properties form control from listPage class example code

public void initializeQuery(Query _query)
{
    // ListPageLabelChange =>
    Common              externalRecord;
    FormDataSource      frmDs;
    FormRun             formRun;
    FormControl         frmCtrl;
    // ListPageLabelChange <=
    
    _query.dataSourceTable(tableNum(HRSBenefitEntitlement)).addRange(fieldNum(HRSBenefitEntitlement,Employment)).value(SysQuery::value(HcmEmployment::findByWorkerLegalEntity(HcmWorker::userId2Worker(curUserId()),CompanyInfo::find().RecId).RecId));
    
    super(_query);
    
     // ListPageLabelChange =>
    externalRecord = this.listPage().activeRecord(_query.dataSourceTable(tableNum(HRSBenefitEntitlement)).name());//No intrisic function for form DS?
    if(externalRecord.isFormDataSource())
    {
        frmDs   = externalRecord.dataSource();
        formRun = frmDs.formRun();
        if(formRun)
        {
            frmCtrl = formRun.design().controlName(formControlStr(TIDBenefitEntitlementListPage,HRSBenefitEntitlement_Entitlement));
            if(frmCtrl)
            {
                frmCtrl.userPromptText("newName");
            }
        }
    }
    // ListPageLabelChange <=
}

clear usage data if no changes on form.

ODBC from x++ example code

public client static void processMDBData(str _StrDSN, str _StrTableName, date _DateFrom, date _DateTo)
{
    #AviFiles
    SysOperationProgress    progress = new SysOperationProgress();
    OdbcConnection          C;
    Statement               S;
    LoginProperty           LP = new LoginProperty();
    ResultSet               RS;
    HcmPersonnelNumberId    _EmplID;
    TimeHour24              _Time;
    str                     strquery,strDSN,tableName;
    Date                    _Date;
    int                     Idx;
    HRSTMAttendanceTemp     attDataTemp;

    ;

    try
    {   
        strDSN    = _StrDSN;
        tableName = _StrTableName;

        ttsbegin;
        LP.setDSN(_strDSN);
        LP.setServer("S-AOS1TEST");

        C   = new OdbcConnection(LP);
        S   = C.createStatement();


        strquery    = strFmt("SELECT * FROM %1 where dateLog >= #%2# AND dateLog <= #%3#",tableName,_DateFrom,_DateTo);
        RS  = S.executeQuery(strquery);
        while (RS.next())
        {
            //Progress.incCount();
            _EmplID = Rs.getString(1);
            _Date   = RS.getDate(2);
            _Time   = str2time(RS.getString(3));
            Idx     = str2int(rs.getString(4));
            
            //insert to temp table
            if(_EmplID == '') continue;
            select forupdate attDataTemp
            where attDataTemp.PersonnelNumber == _EmplID && attDataTemp.Time == _Time
                                  && attDataTemp.TransDate == _Date;
            if(!attDataTemp)
            {
                attDataTemp.clear();
                attDataTemp.PersonnelNumber      = _EmplID;
                attDataTemp.Time        = _Time;
                attDataTemp.TransDate   = _Date;
                attDataTemp.Idx         = Idx;
                attDataTemp.insert();
            }
            else
            {
                attDataTemp.Idx         = Idx;
                attDataTemp.ClockId     = '';
                attDataTemp.update();
            }

            Progress.setText(strfmt("Upload Id %1 - %2 %3", _EmplID, _date, time2str(_time, 1, 1)));
        }
        ttsCommit;

   }
   catch
   {
       ttsAbort;
       return;
   }
}

example code on custom class ApprovalResubmitActionMgr

example code :

public static void main(Args _args)
{
    recID                           recID               = _args.record().RecId;
    tableId                         tableId             = _args.record().TableId;
    HcmEmploymentLeave              HcmEmploymentLeave  = _args.record();
    WorkflowWorkItemTable           workItem            = _args.caller().getActiveWorkflowWorkItem();
    str                             menuItemName        = _args.menuItemName();
    WorkflowWorkItemActionDialog    workflowWorkItemActionDialog;
    WorkflowSubmitDialog            workflowSubmitDialog;
     
    Debug::assert(tableId == tablenum(HcmEmploymentLeave));
    Debug::assert(recId != 0);

    // The method has not been called correctly.
    if (tableId != tablenum(HcmEmploymentLeave) ||
        recId == 0)
    {
        throw error(strfmt("@SYS19306", funcname()));
    }

    // The journal has an active workflow.
    if (workItem.RecId > 0)
    {
        try
        {
            // The journal does support workflow approvals.
            workflowWorkItemActionDialog = WorkflowWorkItemActionDialog::construct( workItem,
                                                                                    WorkflowWorkItemActionType::Resubmit,
                                                                                    new MenuFunction(_args.menuItemName(),_args.menuItemType()));
            workflowWorkItemActionDialog.run();
            if (workflowWorkItemActionDialog.parmIsClosedOK())
            {

                workItem    = _args.caller().getActiveWorkflowWorkItem();
                WorkflowWorkItemActionManager::dispatchWorkItemAction(  workItem,
                                                                    workflowWorkItemActionDialog.parmWorkflowComment(),
                                                                    workflowWorkItemActionDialog.parmTargetUser(),
                                                                    WorkflowWorkItemActionType::Resubmit,
                                                                    _args.menuItemName(),
                                                                    false);
              
            }
        }

        catch(Exception::Error)
        {
            throw error(strfmt("Leave %1 can't be submitted", HcmEmploymentLeave.HRSLeaveReqId));
        }
    }
    // Make the form refresh its common workflow UI controls.
    _args.caller().updateWorkflowControls();
}

get Standard Cost Price Item Based on Activation date and InventSiteId

public static Price TIDgetSTDCostItemPrice(ItemId _itemId,TransDate _activationDate,InventSiteId _inventSiteId)
{
    InventDim       InventDimLoc;
    InventItemPrice inventItemPriceLoc;
    
    InventDimLoc.clear();
    InventDimLoc.InventSiteId = _inventSiteId;
    InventDimLoc = InventDim::findOrCreate(InventDimLoc);
    
    inventItemPriceLoc = InventItemPrice::findCurrent(_itemId,CostingVersionPriceType::Cost,InventDimLoc.inventDimId,
                                                      _activationDate,_inventSiteId,InventItemCostingType::Standard);
    
    return inventItemPriceLoc.Price;
}

send email via x++ example code

static boolean sendEmail(str toAddress, str theSubject, str body, str fromAddress)
{
    SysEmailParameters parameters = SysEmailParameters::find();

    System.Net.Mail.SmtpClient            mailClient;
    System.Net.Mail.MailMessage           mailMessage;
    System.Net.Mail.MailAddress           mailFrom;
    System.Net.Mail.MailAddress           mailTo;
    System.Net.Mail.MailAddressCollection mailToCollection;
    System.Net.Mail.MailAddressCollection mailCCCollection;
    System.Net.Mail.AttachmentCollection  mailAttachementCollection;
    System.Net.Mail.Attachment            mailAttachment;
    System.Net.NetworkCredential          mailCredentials;

    System.Exception                      e;
    List            toList;
    ListEnumerator  le;


    SMTPRelayServerName mailServer;
    SMTPPortNumber mailPortNumber;
    SMTPUserName mailUserName;
    SMTPPassword mailPassword;

    InteropPermission interopPermission;
    ;

    try
    {
        interopPermission = new InteropPermission(InteropKind::ComInterop);
        interopPermission.assert();

        if (parameters.SMTPRelayServerName)
        {
            mailServer = parameters.SMTPRelayServerName;
        }
        else
        {
            mailServer = parameters.SMTPServerIPAddress;
        }

        mailPortNumber  = parameters.SMTPPortNumber;
        mailUserName    = parameters.SMTPUserName;
        mailPassword    = SysEmailParameters::password();
        mailCredentials = new System.Net.NetworkCredential(mailUserName,mailPassword);


        mailClient = new System.Net.Mail.SmtpClient(mailServer, mailPortNumber);
        mailFrom = new System.Net.Mail.MailAddress(parameters.SMTPUserName); //fromAddress);

        toList = strSplit(toAddress,';');
        le = toList.getEnumerator();
        le.moveNext();

        mailTo  = new System.Net.Mail.MailAddress(strLTrim(strRTrim(le.current())));
        mailMessage = new System.Net.Mail.MailMessage(mailFrom, mailTo);

        mailToCollection = mailMessage.get_To();
        while(le.moveNext())
        {
            mailToCollection.Add(strLTrim(strRTrim(le.current())));
        }


        mailMessage.set_From(mailFrom);
        mailMessage.set_ReplyTo(mailFrom);
        mailMessage.set_Priority(System.Net.Mail.MailPriority::High);
        mailMessage.set_Subject(theSubject);
        mailMessage.set_IsBodyHtml(true);
        mailMessage.set_Body(body);

        if(parameters.TIDUsingSSLorTSL)
        {
            mailClient.set_EnableSsl(true);
            mailClient.set_UseDefaultCredentials(false);
            mailClient.set_Credentials(mailCredentials);
        }
        else
        {
            mailClient.set_EnableSsl(false);
        }


        mailClient.Send(mailMessage);

        mailMessage.Dispose();

        CodeAccessPermission::revertAssert();

        //info("Email has been send.");
    }
    catch (Exception::CLRError)
    {
        e = ClrInterop::getLastException();
        while (e)
        {
            info(e.get_Message());
            e = e.get_InnerException();
        }
        CodeAccessPermission::revertAssert();
        return false;
    }
}

source : my senior’s code

find Physical Inventory Qty by date

i modified code originated from DAXDUDE ,
i added inventQty = inventSumDateDim.postedQty()-InventSumDateDim.deductedQty()+InventSumDateDim.postedQty() to get physical inventory

static void TIDF_getInventPhysicalbyDate(Args _args)
{
    InventDimParm inventDimParmCrit;
    InventSumDateDim inventSumDateDim;
    InventDim inventDim = InventDim::find('AllBlank');
    InventQty inventQty;
    ;

    // Set the dimensions
    inventDimParmCrit.initFromInventDim(inventDim);
    inventSumDateDim = inventSumDateDim::newParameters(today(), '010410003', inventDim, inventDimParmCrit);
    inventQty =  inventSumDateDim.receivedQty()-InventSumDateDim.deductedQty()+InventSumDateDim.postedQty();

    info(strFmt("%1",inventQty));
}

source : http://daxdude.blogspot.com/2012/11/ax-2012-find-on-hand-inventory-by-date.html

get LedgerDimension combination RecId example code

source : http://kiwiaxguy.blogspot.com/2014/07/setting-ledgerdimension-field-on.html

i modify some code from the source because the dimension name is hardcoded,

on class global :

public static container getDimensionNameTID()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &amp;&amp;
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}

getLedgerDimension example code :

public static RecId getLedgerDimensionCombination(MainAccountNum _ledgerAccount, container dimValue)
{
    DimensionServiceProvider DimensionServiceProvider = new DimensionServiceProvider();
    LedgerAccountContract LedgerAccountContract = new LedgerAccountContract();
    DimensionAttributeValueContract   ValueContract;
    List ListValueContract = new List(Types::Class);
    dimensionAttributeValueCombination dimensionAttributeValueCombination;
    DimensionStorage          dimStorage;
    container dimensionName = Global::getDimensionNameTID();
    int       loopDimValue = 1;

    if(conLen(dimValue) > 0)
    {
        while(loopDimValue <= conLen(dimValue))
        {
            ValueContract = new DimensionAttributeValueContract();
            ValueContract.parmName(conPeek(dimensionName,loopDimValue)) ;
            ValueContract.parmValue(conPeek(dimValue,loopDimValue));
            ListValueContract.addEnd(ValueContract);
            loopDimValue++;
        }

        LedgerAccountContract.parmMainAccount(_ledgerAccount);
        LedgerAccountContract.parmValues(ListValueContract);
        dimStorage = DimensionServiceProvider::buildDimensionStorageForLedgerAccount(LedgerAccountContract);
        dimensionAttributeValueCombination = DimensionAttributeValueCombination::find(dimStorage.save());
        return dimensionAttributeValueCombination.RecId;
    }
    else
    {
        return 0;
    }

}

example code on how to use it :

static void TIDF_getLedgerDim(Args _args)
{
    DimensionValue       dim1,dim2;
    container conDimValue;
    MainAccountNum       mainAccountNum;

    mainAccountNum = "6100-1001";
    dim1           = "5102";
    dim2           = "OPERATIO";
    conDimValue = [dim1,dim2];

    info(strFmt("%1",getLedgerDimensionCombination(mainAccountNum,conDimValue)));


}

Init field value from datasource , show on Enterprise Portal AXBoundField

on AOT dataset method init() :
Capture

X++ code on init method example:

public void init()
{
    super();
    DNLLeaveRequest.DNLEmplRecId  = DNLEmployeeTable::findByUserId().RecId;
    DNLLeaveRequest.LeaveDateFrom = today();
    DNLLeaveRequest.LeaveDateTo   = today();
}

or you can code it on Tables\initvalue()

XML code on visual studio for EP pages :

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="TIDDNLLeaveRequestEP.ascx.cs" Inherits="TIDDNLLeaveRequestEP" %>
<dynamics:AxDataSource ID="AxDataSource1" runat="server" 
    DataSetName="TIDLeaveRequestDS" ProviderView="DNLLeaveRequest" 
    oninitializeddatasetrun="AxDataSource1_InitializedDataSetRun">
</dynamics:AxDataSource>
<dynamics:AxForm ID="AxForm1" runat="server" DataSourceID="AxDataSource1" 
    DataKeyNames="RecId,Added,AdvanceUsed,BlankLeave,carryused,ContactPhone,createdBy,createdDateTime,dataAreaId,DNLEmplRecId,DNLLeaveTypeRecId,IdApproval,IdPersonSubstitute,LeaveDateFrom,LeaveDateTo,modifiedBy,modifiedDateTime,Remarks,RequestDate,State,StatusFlag,SubmittedBy,SubmittedDateTime,TableId,TotalLeaveDays,TravellingLocation,getEmplName**,GetEntitlement**,getExpiration**,getLeaveDesc**,mostRecentComment**,requiredActionDueDate**,titleFields**" 
    DataMember="DNLLeaveRequest_Current" AutoGenerateInsertButton="True" 
    DefaultMode="Insert" UpdateOnPostBack="true" oninit="AxForm1_Init"  >
    <dynamics:AxMultiColumn ID="AxMultiColumn1" runat="server">
    
 
    
 
        <dynamics:AxColumn ID="AxColumn2" runat="server">
            <dynamics:AxGroup ID="groupLeaveRequest" runat="server" FormID="AxForm1" 
                Caption="Leave Request" CaptionAlign="Top" HorizontalAlign="Left" 
                Width="300px">
                <Fields>
                    <dynamics:AxReferenceBoundField DataField="DNLEmplRecId" 
                        DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest" 
                        SortExpression="DNLEmplRecId" Mandatory="True">
                    </dynamics:AxReferenceBoundField>
                    <dynamics:AxReferenceBoundField AutoPostBack="True" 
                        DataField="DNLLeaveTypeRecId" DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" SortExpression="DNLLeaveTypeRecId" 
                        Mandatory="True">
                    </dynamics:AxReferenceBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="LeaveDateFrom" 
                        DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" SortExpression="LeaveDateFrom">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="LeaveDateTo" 
                        DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" SortExpression="LeaveDateTo" 
                        Mandatory="True" >
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="Remarks" 
                        DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest" 
                        SortExpression="Remarks">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField AutoPostBack="True" DataField="TotalLeaveDays" 
                        DataSet="TIDLeaveRequestDS" DataSetView="DNLLeaveRequest" 
                        SortExpression="TotalLeaveDays">
                    </dynamics:AxBoundField>
                    <dynamics:AxBoundField DataField="GetEntitlement**" DataSet="TIDLeaveRequestDS" 
                        DataSetView="DNLLeaveRequest" ReadOnly="True">
                    </dynamics:AxBoundField>
                </Fields>
            </dynamics:AxGroup>
        </dynamics:AxColumn>
    </dynamics:AxMultiColumn>
</dynamics:AxForm>

C# code on visual studio for EP pages

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.DataVisualization.Charting;
using System.Web.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls;
using Microsoft.Dynamics.Framework.Portal.UI.WebControls.WebParts;
using Microsoft.Dynamics.AX.Framework.Portal.Data;
using Microsoft.Dynamics.AX.Framework.Services.Client;
using Microsoft.Dynamics.Framework.BusinessConnector.Session;
using Microsoft.Dynamics.Framework.BusinessConnector.Adapter;
using Microsoft.Dynamics.Framework.Portal;
using Microsoft.Dynamics.Framework.BusinessConnector.Proxy;

public partial class TIDDNLLeaveRequestEP : System.Web.UI.UserControl
{
    DateTime dateFrom;
    DateTime dateTo;
    Int64 recIdLeaveType;
   
    

    #region customMethod
    private ISession AxSession
    {
        get 
        {
            AxBaseWebPart webPart = AxBaseWebPart.GetWebpart(this);
            return webPart == null ? null : webPart.Session;
        }
    }
    static AxBoundField GetField(DataControlFieldCollection fields, string name)
    {
        foreach (DataControlField field in fields)
        {
            // Is this the field being searched for?
            AxBoundField boundField = field as AxBoundField;
            if (boundField != null && String.Compare(boundField.DataField, name, true) == 0)
            {
                return boundField;
            }

        }
        // Nothing found, so return null.
        return null;
    }

    private DataSetViewRow CurrentRow
    {
        get
        {
            try
            {
                DataSetView dataSetView;
                dataSetView = this.AxDataSource1.GetDataSet().DataSetViews["DNLLeaveRequest"];
                return (dataSetView == null) ? null : dataSetView.GetCurrent();
            }
            catch (System.Exception)
            {
                return null;
            }
        }
    }

    #endregion customMethod

    protected void AxForm1_Init(object sender, EventArgs e)
    {
        //this.loadFirst();
        this.AxForm1.DefaultMode = DetailsViewMode.Insert;
        this.AxForm1.AutoGenerateInsertButton = true;

        AxBoundField axbfNoNPK;
        axbfNoNPK = GetField(groupLeaveRequest.Fields, "DNLEmplRecId");
        axbfNoNPK.ReadOnly = true; 
    }


    protected void Page_Load(object sender, EventArgs e)
    {

    }
 
   
    protected void AxDataSource1_InitializedDataSetRun(object sender, AxDataSourceDataSetEventArgs e)
    {
        e.DataSet.Init();
    }
}

preview :
Capture

error: The data source is not embedded within a (parent) data source.

This error will occur when you add new dataSource with function “addDataSource(tableNum(MyTable))” but not specify which it’s parent datasource

so example code before :

qbsInventDim = _query.addDataSource(tableNum(WMSLocation));

code after :

qbsInventDim = _query.dataSourceTable(tableNum(InventDim)).addDataSource(tableNum(WMSLocation))

Print Query-Based SSRS via X++ example

code example :

public void PrintPenarikanBarang(TECTradeInId tradeInCode)
{
    SrsReportRunController                  reportRunController;
    Map                                     queryContracts;
    MapEnumerator                           mapEnum;
    Query                                   query;
    QueryBuildRange                         range;

    SrsReportDataContract                   contract;
    TECTradeInPenarikanBarangReportContract     rdpContract;

    if(TECTradeInTable.TECTradeInCode)
    {
        reportRunController = new SrsReportRunController();
        reportRunController.parmReportName(ssrsReportStr(TECPenarikanBarangTradeIn ,Report));
        reportRunController.parmLoadFromSysLastValue(false);

        // Set printer settings (print to file, format, filename, etc).
        contract    = reportRunController.parmReportContract();

        rdpContract = contract.parmRdpContract() as TECTradeInPenarikanBarangReportContract;

        queryContracts = contract.parmQueryContracts();
        mapEnum = queryContracts.getEnumerator();
        while(mapEnum.moveNext())
        {
            query = mapEnum.currentValue();
            range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(TECTradeInTable)),fieldNum(TECTradeInTable, TECTradeInCode));
            range.value(queryValue(tradeInCode));
        }

        reportRunController.runReport();

    }

}

Modify Query on Report DP Class example

[SysEntryPointAttribute]
public void processReport()
{
    //query class
    QueryRun            qr;
    Query               q;
    QueryBuildDataSource qbds;
    //table object
    InventSum           inventSum;
    WMSLocation         wmsLocation;
    RetailStoreTable    retailStoreTable;
    TIDLocType          tidLocType;
    //
    str                 headerName;

    contract = this.parmDataContract() as TIDStoreDisplayReportContract;
    parmShowQty = contract.parmShowQty();
    parmFilterItem = contract.parmStoreDisplayFilterItem();

    q = this.parmQuery();

    q.dataSourceTable(tableNum(WMSLocation)).addRange(fieldNum(WMSLocation,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
    switch(parmFilterItem)
    {
        case TIDStoreDisplayFilterItem::OnlyDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::Yes));
            headerName = "Only Display Item";
            break;
        case TIDStoreDisplayFilterItem::NoDisplay :
            qbds = q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::NoExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "No Display Item";
            break;
        case TIDStoreDisplayFilterItem::NotInDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::No));
            headerName = "Not in Display Item";
            break;
        case TIDStoreDisplayFilterItem::HaveDisplay :
            qbds =  q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::ExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "Have Display Item";
            break;
        default :
            headerName = "Store Display Item";
            break;
    }

    // only display > 0
    q.dataSourceTable(tableNum(InventSum)).addRange(fieldNum(InventSum,AvailPhysical)).value(queryValue(">0"));

    qr = new QueryRun(q);

    while(qr.next())
    {
        inventSum   = qr.get(tableNum(inventSum));
        wmsLocation = qr.get(tableNum(wmsLocation));
        tidLocType  = qr.get(tableNum(TIDLocType));

        tempTable.clear();
        tempTable.headerName        = headerName;
        tempTable.ItemId            = inventSum.ItemId;
        tempTable.EcoResDescription = TIDItemDisplayCustom::getItemDescription(inventSum.ItemId);
        tempTable.EcoResProductName = TIDItemDisplayCustom::getItemName(inventSum.ItemId);
        tempTable.NameAlias         = TIDItemDisplayCustom::getItemModel(inventSum.ItemId);
        tempTable.AvailPhysical     = inventSum.AvailPhysical;
        select firstOnly retailStoreTable where retailStoreTable.inventLocation == wmsLocation.inventLocationId;
        tempTable.Store             = retailStoreTable.StoreNumber;
        tempTable.WMSLocationId     = wmsLocation.wMSLocationId;
        tempTable.IsDisplay         = tidLocType.TIDIsDisplay;
        tempTable.insert();

    }

}

get currency exchange rate using AX Class

static CurrencyExchangeRate getExchRate(CurrencyCode fromCurrency, CurrencyCode toCurrency, TransDate exchangeDate, RefRecId exchangeRateTypeRecId)
{
    ExchangeRateHelper exchangeRateHelper;
    ;

    exchangeRateHelper = ExchangeRateHelper::construct();
    exchangeRateHelper.parmLedgerRecId(Ledger::current());
    exchangeRateHelper.parmFromCurrency(fromCurrency);
    exchangeRateHelper.parmToCurrency(toCurrency);
    exchangeRateHelper.parmExchangeDate(exchangeDate);
    exchangeRateHelper.parmExchangeRateTypeRecId(exchangeRateTypeRecId);

    return ExchangeRateHelper::displayStoredExchangeRate_Static(exchangeRateHelper.getExchangeRate1(),
           ExchangeRateCurrencyPair::getExchangeRateDisplayFactor(fromCurrency, toCurrency, exchangeRateTypeRecId, true));
}

Export License Key using job

static void OutputLicenseToText(Args _args)
{
    #define.licenseVersion(2)
    #define.KeywordLen(20)
    #define.keywordLicense('License')
    #define.keywordProperties('Properties')
    #define.keywordCodes('Codes')
    #define.keywordCodeLine('CodeLine')
    #define.keywordDate('Date')
    #define.keywordSerial('Serial')
    #define.keywordValue('Value')
    #define.blank('')
    #define.space1(' ')
    #define.space2('  ')
    #define.space3('   ')
    #define.spaceHash(' #')
    #define.OutputFilename(@'C:\OutputLicenseKeys.txt')

    #define.keywordInfo(1)
    #define.keywordWarning(2)

    SysConfig           sysConfig;
    SysLicenseCodeSort  sysLicenseCodeSort;
    container           fileOut;
    int                 i;
    System.IO.StreamWriter  sw;
    InteropPermission perm = new InteropPermission(InteropKind::ClrInterop);
    ;

    fileOut += "LicenseVersion "    + strfmt("%1", #licenseVersion);
    fileOut += #blank;
    fileOut += #keywordLicense      + #spaceHash + xSysConfig::find(ConfigType::LicenseName,0).Value;
    fileOut += #blank;
    fileOut += #space1  + #keywordProperties;
    fileOut += #space2  + "Name"            + #spaceHash    + xSysConfig::find(ConfigType::LicenseName,0).Value;
    fileOut += #space2  + #keywordSerial    + #spaceHash    + xSysConfig::find(ConfigType::SerialNo,0).Value;
    fileOut += #space2  + #keywordDate      + #spaceHash    + xSysConfig::find(ConfigType::LicenseName,1).Value;
    fileOut += #space1  + "EndProperties";
    fileOut += #blank;
    fileOut += #space1  + #keywordCodes;

    // Build CodeLines
    while select sysConfig
        where sysConfig.configType  == ConfigType::AccessCodes   &&
              sysConfig.value       != #blank
        join sysLicenseCodeSort
        order by SortIdx
        where sysLicenseCodeSort.Id == sysConfig.id
    {
        fileOut += #space2  + #keywordCodeLine  + #spaceHash    + int2str(sysConfig.id + 1);
        fileOut += #space3  + #keywordValue     + #spaceHash    + sysConfig.value;
        fileOut += #space2  + "EndCodeLine";
        fileOut += #blank;
    }

    fileOut += #blank;
    fileOut += #space2  + "EndCodes";
    fileOut += #space1  + "EndLicense";

    // Begin file output
    perm.assert();

    sw = new System.IO.StreamWriter(#OutputFilename);

    for (i=1; i<=conLen(fileOut); i++)
    {
        sw.WriteLine(conPeek(fileOut, i));
    }

    sw.Flush();
    sw.Close();
    sw.Dispose();

    CodeAccessPermission::revertAssert();

    info("License successfully output to " + #OutputFilename);
}

source : http://alexondax.blogspot.com/2011/01/how-to-export-license-keys-that-are.html

get exchange rate example code

static void TIDF_ExchangeRate(Args _args)
{
    ExchangeRate     exchangeRate;
    ExchangeRateType ExchangeRateType; 
    ExchangeRateCurrencyPair exchangeRateCurrencyPair;
    real             exchRate;
    
    CurrencyCode fromCurrency  = "USD";
    CurrencyCode toCurrency    = "IDR";
    TransDate    transDate     = today();
    
    
    select firstonly exchangeRateCurrencyPair 
    where 
        exchangeRateCurrencyPair.ExchangeRateType == Ledger::find(Ledger::current()).TECFakturPajakAdjustExchangeRateType
    &&  exchangeRateCurrencyPair.FromCurrencyCode == fromCurrency 
    &&  exchangeRateCurrencyPair.ToCurrencyCode   == toCurrency;
    exchRate = exchangeRate::findByDate(exchangeRateCurrencyPair.RecId,transDate).ExchangeRate;
    info(strFmt("%1",exchRate/100));
    
}

Change caption form via code example

public void init()
{
    super();

    element.updateDesign(InventDimFormDesignUpdate::Init);

    //add by fanddy
    if(element.args().record().TableId == tableNum(TECCustInOutClaimTable))
    {
        TECCustInOutClaimTable = element.args().record();
        element.design().caption(strFmt("Customer Claim Line : %1, Receipt Number : %2",TECCustInOutClaimTable.TECCustInOutClaimId,TECCustInOutClaimTable.RetailReceiptId));
    }

    element.disableNewButton();


}

fix budget exceeded still can submit Ledger Journal WorkFlow , ledgerJournalCheckPost

on class LedgerJournalWFApprSubmitToWF \ main

//add by fanddy, fix Bugs exceed budget still can submit on 30/03/2015
    LedgerJournalCheckPost ledgerJournalCheckPost;
	FormDataSource fds;
    //end add by fanddy


    Debug::assert(tableId == tableNum(LedgerJournalTable));
    Debug::assert(recId != 0);

    // The method has not been called correctly.
    if (tableId != tableNum(LedgerJournalTable) ||
        recId == 0)
    {
        throw error(strFmt("@SYS19306", funcName()));
    }
    //add by fanddy, fix Bugs exceed budget still can submit on 30/03/2015
    else
    {
        ledgerJournalCheckPost= ledgerJournalCheckPost::newLedgerJournalTable(ledgerJournalTable,NoYes::No,NoYes::Yes);
        ledgerJournalCheckPost.run();

        if(!ledgerJournalCheckPost.getParmCheckJournalTID())
        {
            throw error("Can't submit this journal");
        }
		//refresh form dataSource , fix error when submit must restore form dataSource 
		fds = args.record().dataSource();
		fds.research(true);
		fds.refresh();
    }
    //end add by fanddy

i did some modify on class LedgerJournalCheckPost for returning checkJournal result.
on LedgerJournalCheckPost\run method

//edited by fanddy
checkResultTID = this.checkJournal();

set default dimension value

public static RecId getNewDefaultDimension(RecId defaultDimension, Name dimName, str 255 dimValue)
{
    container c;
    RecId newdefaultDimension;
    int i;
    ;

    c = AxdDimensionUtil::getDimensionAttributeValueSetValue(defaultDimension);
    i = conFind(c, dimName);
    if (!i && !dimValue)
        return defaultDimension;
    if (i)
    {
        c = conDel(c, i+1, 1);
        c = conDel(c, i, 1);
    }
    if (dimValue)
    {
        c += dimName;
        c += dimValue;
    }

    c = conDel(c, 1, 1);
    c = conIns(c, 1, conLen(c) / 2);
    newdefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(c);

    return newdefaultDimension;
}

get dimension name , return container

public static container getDimensionName()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId;
    DimensionAttributeValue         dimAttributeValue;
    container                       DimensionName;

    dimensionSetId      = DimensionCache::getDimensionAttributeSetForLedger();

    while select dimAttr order by Name
                where dimAttr.Type != DimensionAttributeType::MainAccount
            join RecId from dimAttrSetItem
                where dimAttrSetItem.DimensionAttribute     == dimAttr.RecId &amp;&amp;
                      dimAttrSetItem.DimensionAttributeSet  == dimensionSetId
            {
                dimensionName += dimAttr.Name;

            }

    return DimensionName;
}

Custom Number Sequence Reference Example

1. Create new field as picture below

Capture1

2. Go to the form, right click and choose restore to reload new field we added before. On the field method, add code below
 

public Common lookupReference(FormReferenceControl _formReferenceControl)
{
    return NumberSequenceTable::lookupReference(_formReferenceControl);
}

 
Capture2

3. Add reference group for the field at the form as picture below , and you are done.

Capture3

4. Just run the form for testing.

Capture4

Custom lookup example 2

public void lookup()
{
   // super();

    SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(SalesTable), this);
    Query query = new Query();
    QueryBuildDataSource qbds,qbds2,qbds3;

    qbds = query.addDataSource(tablenum(SalesTable));
    qbds.addOrderByField(fieldNum(SalesTable,SalesId));
    qbds.addOrderByField(fieldNum(SalesTable,SalesName));
    qbds.addOrderByField(fieldNum(SalesTable,SalesType));
    qbds.addOrderByField(fieldNum(SalesTable,CustAccount));

    qbds.addGroupByField(fieldNum(SalesTable,SalesId));
    qbds.addGroupByField(fieldNum(SalesTable,SalesName));
    qbds.addGroupByField(fieldNum(SalesTable,SalesType));
    qbds.addGroupByField(fieldNum(SalesTable,CustAccount));
    
    qbds2 = qbds.addDataSource(tableNum(TECDeliveryOrderTable));
    qbds2.joinMode(JoinMode::NoExistsJoin);
    qbds2.relations(false);
    qbds2.addLink(fieldNum(SalesTable,SalesId),fieldNum(TECDeliveryOrderTable,TIDUnlockSalesId));

    qbds3 = qbds.addDataSource(tableNum(SalesLine));
    qbds3.joinMode(JoinMode::InnerJoin);
    qbds3.relations(false);
    qbds3.addLink(fieldNum(SalesTable,SalesId),fieldNum(SalesLine,SalesId));
    qbds3.addRange(fieldNum(SalesLine,ItemId)).value(InventParameters::find().TIDItemIdServices);


    sysTableLookup.addLookupfield(fieldnum(SalesTable, SalesId),true);
    sysTableLookup.addLookupfield(fieldNum(SalesTable,SalesName));
    sysTableLookup.addLookupfield(fieldNum(SalesTable,SalesType));
    sysTableLookup.addLookupfield(fieldNum(SalesTable,CustAccount));
    sysTableLookup.addLookupMethod("customerName");
    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

}

Modify AOT items properties via code x++

TreeNode    objTreeNode;
 
objTreeNode = TreeNode::findNode(@"\Menu Items\Display\");
 
if (objTreeNode)
{
    objTreeNode = objTreeNode.AOTfirstChild();
    /*
    change objTreeNode object properties
    */
    while (objTreeNode)
    {            
        objTreeNode.AOTsetProperties("PROPERTIES\n ViewUserLicense  #" + 'TASK' + "\n  MaintainUserLicense  #" + 'TASK' + "\n ENDPROPERTIES\n");
        objTreeNode.AOTsave();
        objTreeNode = objTreeNode.AOTnextSibling();
    }
}

source : http://dotnetxperience.blogspot.com/2012/10/modify-aot-items-from-code-ax-2012.html

Job example using COMVariant

static void TIDF_UploadUnitConversion(Args _args)
{
    //coded by fanddy
    // for upload Unit Conversion Purpose
    SysExcelApplication     application;
    SysExcelWorkbooks       workbooks;
    SysExcelWorkbook        workbook;
    SysExcelWorksheets      worksheets;
    SysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    COMVariantType          type;
    int                     row;

    UnitOfMeasureConversion UnitOfMeasureConversion;
    //table's field data
    ItemId                  itemid;
    UnitOfMeasureSymbol     unitTo;
    unitOfMeasureSymbol     unitFrom;
    Factor                  factor;



    Filename                fileName = 'C:\\#TECTURA#\\Master Data\\UploadFormula\\UnitConversionrev4.xlsx';

    int                     loop;
    int                     lastSerial = 1;


    application = SysExcelApplication::construct();
    workbooks   = application.workbooks();

    try
    {
        workbooks.open(fileName);
    }
    catch
    {
        throw error(strFmt("File: %1 cannot be opened", fileName));
    }

    workbook    = workbooks.item(1);
    worksheets  = workbook.worksheets();

    worksheet   = worksheets.itemFromNum(1);
    cells       = worksheet.cells();

    row         = 2;
    type        = cells.item(row, 1).value().variantType();

    ttsBegin;
    while (type != COMVariantType::VT_EMPTY)
    {
        itemid      = cells.item(row, 1).value().bStr();
        unitFrom    = cells.item(row, 2).value().bStr();
        unitTo      = cells.item(row, 3).value().bStr();
        factor      = cells.item(row, 4).value().double();

        UnitOfMeasureConversion.clear();
        UnitOfMeasureConversion.Product             = EcoResProduct::findByProductNumber(itemid).RecId;
        UnitOfMeasureConversion.FromUnitOfMeasure   = UnitOfMeasure::findBySymbol(unitFrom).RecId;
        UnitOfMeasureConversion.ToUnitOfMeasure     = UnitOfMeasure::findBySymbol(unitTo).RecId;
        UnitOfMeasureConversion.Factor              = factor;
        UnitOfMeasureConversion.Numerator           = 1;
        UnitOfMeasureConversion.Denominator         = 1;

        UnitOfMeasureConversion.insert();
        row++;
        type    = cells.item(row, 1).value().variantType();
    }
    ttsCommit;
    application.quit();

    info(strFmt('Upload Success'));
}

Create Loyalty Point Customer via X++


static void TIDF_TestCreateLoyaltyPointCustomer(Args _args)
{
    //table
    RetailLoyaltyCustTable      retailLoyaltyCustTable;
    RetailLoyaltyMSRCardTable   retailLoyaltyMSRCardTable;
    RetailLoyaltyMSRCardTrans   retailLoyaltyMSRCardTrans;
    AxRetailLoyaltyMSRCardTable AxRetailLoyaltyMSRCardTable;
    RetailLoyaltySchemesTable   RetailLoyaltySchemesTable;
    
    //field
    RetailLoyaltyCustId     retailLoyaltyCustId;
    CustAccount             custAccount;
    RetailLoyaltySchemeId   retailLoyaltySchemeId;
    RetailLoyaltyCardId     retailLoyaltyCardId;
    RetailLoyaltyPoints     retailLoyaltyPoints;
    
    retailLoyaltyCustId = "XXTESTXX03";
    custAccount         = "010203";
    retailLoyaltySchemeId = "2";
    retailLoyaltyCardId = "XXTESTXX10";
    retailLoyaltyPoints = 50;

    //save header
    retailLoyaltyCustTable.clear();
    retailLoyaltyCustTable.initValue();
    retailLoyaltyCustTable.accountNum = custAccount;
    retailLoyaltyCustTable.initFromCustTable(CustTable::find(custAccount));
    retailLoyaltyCustTable.loyaltyCustId = retailLoyaltyCustId ;
    retailLoyaltyCustTable.insert();

    //save lines
    AxRetailLoyaltyMSRCardTable =  new AxRetailLoyaltyMSRCardTable();
    AxRetailLoyaltyMSRCardTable.parmLoyaltyCustId(retailLoyaltyCustTable.loyaltyCustId);
    AxRetailLoyaltyMSRCardTable.parmLinkId(retailLoyaltyCustTable.loyaltyCustId);

    RetailLoyaltySchemesTable = RetailLoyaltySchemesTable::find(retailLoyaltySchemeId);
    if(RetailLoyaltySchemesTable)
    {
        AxRetailLoyaltyMSRCardTable.parmLoyaltySchemeId(RetailLoyaltySchemesTable.loyaltySchemeId);
    }

    AxRetailLoyaltyMSRCardTable.parmLoyaltyTender(RetailLoyaltyTenderTypeBase::AsCardTender);
    AxRetailLoyaltyMSRCardTable.parmCardNumber(retailLoyaltyCardId);
    AxRetailLoyaltyMSRCardTable.save();
    
    //upload points.
    retailLoyaltyMSRCardTrans.clear();
    retailLoyaltyMSRCardTrans.loyaltySchemeId = RetailLoyaltySchemesTable.loyaltySchemeId;
    retailLoyaltyMSRCardTrans.loyaltyCustId   = retailLoyaltyCustId;
    retailLoyaltyMSRCardTrans.cardNumber      = AxRetailLoyaltyMSRCardTable.retailLoyaltyMSRCardTable().cardNumber;
    retailLoyaltyMSRCardTrans.entryType       = RetailLoyaltyEntryTypeBase::Sale;
    retailLoyaltyMSRCardTrans.expirationDate  = maxDate();
    retailLoyaltyMSRCardTrans.points          = retailLoyaltyPoints;
    //include store or staff or any
    //retailLoyaltyMSRCardTrans.storeId   
    
    retailLoyaltyMSRCardTrans.insert();


}

lookup using view example x++

 
Capture
 

public void lookup()
{

     QueryBuildDataSource queryBuildDataSource;
    //super();
    //coded by fanddy, 
     SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(TIDInventLookup), this);

     Query query = new Query();

     queryBuildDataSource = query.addDataSource(tableNum(TIDInventLookup));
     sysTableLookup.addLookupfield(fieldnum(TIDInventLookup,NameAlias));
     sysTableLookup.addLookupfield(fieldnum(TIDInventLookup,DisplayProductNumber));
     sysTableLookup.addLookupfield(fieldnum(TIDInventLookup,Name));
     sysTableLookup.parmQuery(query);
     sysTableLookup.performFormLookup();

}

Fixed Asset Projection Depreciation Expense example code

private void runWithAssetGroupId(NoYes parmendOfYearOnly,AssetGroupId assetGroupId)
{
    TmpDateSum      tmpDateSum;
    AssetTable      assetTable;
    AssetBook       assetBook;
    AssetBookTable  assetBookTable;
    //main account
    AssetLedgerAccounts  assetLedgerAccounts;
    MainAccountNum       mainAccountNum;


    while select assetTable
    join assetBook
    join assetBookTable
    where
        assetTable.AssetGroup                   == assetGroupId
    &&  assetTable.AssetId                      == assetBook.AssetId
    &&  assetBook.BookId                        == assetBookTable.BookId
    &&  assetBook.Depreciation                  == NoYes::Yes
    &&  assetBookTable.CurrentOperationsTax     == CurrentOperationsTax
    &&  assetBook.Status                        == AssetStatus::Open
    {

        lineTMP.clear();
        assetLedgerAccounts = AssetLedgerAccounts::find(assetBook.BookId,AssetParameters::find().PostingProfile,enum2int(AssetTransType::Depreciation),TableGroupAll::GroupId,assetTable.AssetGroup);
        mainAccountNum =  MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.OffsetLedgerDimension).MainAccount).MainAccountId;

        //Method calculation provided by AX
        tmpDateSum.setTmpData(assetBook.initDepreciationProfile());
        //set header line
        lineTMP.AssetGroupId = assetTable.AssetGroup;
        lineTMP.AssetId      = assetTable.AssetId;
        lineTMP.MainAccount  = mainAccountNum;
        lineTMP.rGrossFa     = assetBook.AcquisitionPrice;

        //cut off date
        while select tmpDateSum
        where
            tmpDateSum.TmpNumerals01 != 0
        &&  tmpDateSum.TransDate <= endYearDate
        {
            lineTMP.Period       = tmpDateSum.TransDate;
            lineTMP.rAccDepr     = tmpDateSum.TmpNumerals01;
            lineTMP.rDeprExp     = tmpDateSum.Total;
            lineTMP.rNetBookvalue = tmpDateSum.TmpNumerals02;
            lineTMP.insert();
        }

    }


}

Speeding up Sales Agreement Release Order process

When we create SO from sales agreement, we will face a problem that occur on standard version of AX 2012 if number of sales agreement record are thousands and we only want to choose let’s say 2 or 3 line from sales agreement line to Sales Line.
 
we can choose records from agreement line by fill the quantity, and the problem is standard AX still will loop all records no matter the quantity is fill or not. and all thosands will going through process that not needed.
 
Class : SalesAutoCreate\Create

void  create()
{
    #OCCRetryCount

    try
    {
        setprefix("@SYS55110");

        ttsbegin;

        while (this.recordExist())
        {

            this.setCust();

            setprefix(#PreFixField(CustTable,AccountNum));

            this.setSalesTable();

            this.setSalesLine();

            setprefix(#PreFixField(SalesLine,ItemId));

            this.nextRecord();
        }

        this.endUpdate();

        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;
        }
    }

}

i tried debug and i found the issue that made sales agreement release order process become so slow. and made a modification as below.
Class : SalesAutoCreate_ReleaseFromAgreement\New

protected void new(Common  _releaseOrderLine,
                   Object  _callBackClass     = null,
                   Common  _releaseOrderTable = null)
{
    //modify by fanddy
    //purpose : speeding up release agreement from BMP
    SalesCreateReleaseOrderLineTmp  TIDSalesCreateReleaseOrderLineTmp;

    releaseOrderLine     = _releaseOrderLine  as SalesCreateReleaseOrderLineTmp;
    delete_from releaseOrderLine where releaseOrderLine.SalesQty < 1;
    //end modify fanddy

    releaseOrderTable    = _releaseOrderTable as SalesCreateReleaseOrderTableTmp;
    this.firstRecord();

    createFromAgreementLine     = AgreementLineQuantityCommitment::find(releaseOrderLine.AgreementLineQuantityCommitment, false);
    createFromSalesAgreement    = SalesAgreementHeader::find(createFromAgreementLine.Agreement ? createFromAgreementLine.Agreement : releaseOrderTable.AgreementHeader);

    currentSalesId       = '';
    firstRecord          = true;

    super(_releaseOrderLine,_callBackClass);
    // <GEERU>
    countryRegion_RU = SysCountryRegionCode::isLegalEntityInCountryRegion([#isoRU]);
    // </GEERU>

 

Voila !! Now your when you create an SO with 2-3 SO lines originated from thousands line data of sales agreement, the process has been boosted !!

print report via x++ AX 2012

SrsReportRunController          controller = new SrsReportRunController();
SysUserLicenseCountRDPContract  rdpContract = new SysUserLicenseCountRDPContract();
SRSPrintDestinationSettings     settings;
 
// Define report and report design to use
controller.parmReportName(ssrsReportStr(SysUserLicenseCountReport, Report));
// Use execution mode appropriate to your situation
controller.parmExecutionMode(SysOperationExecutionMode::ScheduledBatch);
// Suppress report dialog
controller.parmShowDialog(false);
 
// Explicitly provide all required parameters
rdpContract.parmReportStateDate(systemDateGet());
controller.parmReportContract().parmRdpContract(rdpContract);
 
// Change print settings as needed
settings = controller.parmReportContract().parmPrintSettings();
settings.printMediumType(SRSPrintMediumType::File);
settings.fileFormat(SRSReportFileFormat::Excel);
settings.fileName(@'\\share\UserLicenseCount.xlsx');
 
// Execute the report
controller.startOperation();

Upload a LedgerJournalTrans Fixed Asset type without insert to LedgerJournalTrans_Asset table

an error will occured when you try validate ledgerJournalTrans.

how to fix this :

static void TIDF_fixBugsUploadFixedAssets(Args _args)
{
    ledgerjournaltrans ledgerJournalTrans;
    LedgerJournalTrans_Asset    cekExist;
    LedgerJournalTrans_Asset    LedgerJournalTrans_Asset;
    
    while select  ledgerJournalTrans 
    where 
        ledgerJournalTrans.JournalNum == 'GEN-000007'
    {
        select cekExist where cekExist.RefRecId == ledgerJournalTrans.RecId;
        
         if (ledgerJournalTrans.isFixedAssetsTransaction() && !cekExist)
            {
                ledgerJournalTrans_Asset.clear();
                ledgerJournalTrans_Asset.initValue();

                ledgerJournalTrans_Asset.RefRecId  = ledgerJournalTrans.RecId;
                ledgerJournalTrans_Asset.AssetId   = ledgerJournalTrans.getAssetId();
                ledgerJournalTrans_Asset.Company   = ledgerJournalTrans.getAssetCompany();
                ledgerJournalTrans_Asset.TransType = AssetTransTypeJournal::Acquisition;
                ledgerJournalTrans_Asset.BookId    = "ALL";

                if (!ledgerJournalTrans_Asset.validateWrite())
                    throw Exception::Error;

                ledgerJournalTrans_Asset.insert();
            }
    }


}

source : http://www.cnblogs.com/Fandyx/p/3343688.html

Modify Query Ranges on Report Controller Class

Create a enum and set it on menu item properties as example below.
 
Capture
 
override method preRunModifyContract on report controller class as example code below.

protected void preRunModifyContract()
{
    //modify the parameter value of the contract
   // if( element.args().parmEnumType() == EnumNum( NoYes ) )
    if(this.parmArgs().parmEnumType() == enumNum(TIDSalesDailyRecon))
    {
       switch(this.parmArgs().parmEnum())
       {
           case  TIDSalesDailyRecon::SalesInvoice :
               SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(RetailTransactionPaymentTrans),fieldNum(RetailTransactionPaymentTrans, RecId),SysQuery::value(5637149251));
               break;
           case  TIDSalesDailyRecon::SalesReturn :
               SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(RetailTransactionPaymentTrans),fieldNum(RetailTransactionPaymentTrans, RecId),SysQuery::value(this.parmArgs().record().RecId));
               break;
           case TIDSalesDailyRecon::SalesExIndent :
               break;
       }
    }
}

 
this.parmArgs().record().RecId value is come from :

 SrsReportRunController  controller;
 Args = new Args()

 controller = new SrsReportRunController();
 args.record(TABLES_record);
 controller.parmReportName(ssrsReportStr(REPORT_NAME, REPORT_DESIGN_NAME));

 controller.startOperation();

 
source :
1. http://www.dynamics101.com/2014/01/using-controller-class-developing-ssrs-reports-microsoft-dynamics-ax-2012/
2. http://dynamics.folio3.com/x-tips-and-tricks-passing-parameters-microsoft-dynamics-ax/

add mainAccount to LedgerJournalReport

add this code below to LedgerJournalDP\InsertLedgerJournalTmp

 if(_accountOffsetAccount ==  AccountOffsetaccount::Account)
    {
        switch(_ledgerJournalTrans.AccountType)
        {
            case LedgerJournalACType::Ledger :
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId;
                    break;
            case LedgerJournalACType::Bank :
                    select * from dimAttrValueCombo
                    join Name from bankAccountTable
                    where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue
                    && dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name);
                    break;
            case LedgerJournalACType::Vend :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    VendLedgerAccounts   = VendLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::Cust :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    CustLedgerAccounts = CustLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::FixedAssets :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId;
                    assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,_ledgerJournalTrans.PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId;
                    break;
        }
    }
    else
    {
        switch(_ledgerJournalTrans.AccountType)
        {
            case LedgerJournalACType::Ledger :
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId;
                    break;
            case LedgerJournalACType::Bank :
                    select * from dimAttrValueCombo
                    join Name from bankAccountTable
                    where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue
                    && dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name);
                    break;
            case LedgerJournalACType::Vend :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    VendLedgerAccounts   = VendLedgerAccounts::find(VendParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::Cust :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    CustLedgerAccounts = CustLedgerAccounts::find(CustParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::FixedAssets :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId;
                    assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,AssetParameters::find().PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId;
                    break;
        }
    }

Filter Query Form DataSource using DynaLink

example code on form VendPurchaseOrderJournal , datasource VendPurchOrderJournal method init()

public void init()
{
    QueryBuildDataSource  queryDataSourceLink;
    super();

    if (element.args().dataset() == tablenum(PurchReqLine))
    {
        this.query().dataSourceTable(tablenum(VendPurchOrderJour)).clearDynalinks();

        queryDataSourceLink = this.query().dataSourceName(identifierstr(VendPurchOrderJour));
        queryDataSourceLink.relations(true);
        queryDataSourceLink.addDynalink(fieldnum(VendPurchOrderJour, PurchId),
                                        element.args().record(),
                                        fieldnum(PurchReqLine, PurchId));
    }

    //add by fanddy , for inqueries confirm PO
    if (element.args().dataset() == tablenum(TECPOGroupHeader))
    {
        this.query().dataSourceTable(tablenum(VendPurchOrderJour)).clearDynalinks();

        queryDataSourceLink = this.query().dataSourceName(identifierstr(VendPurchOrderJour));
        queryDataSourceLink.relations(true);
        queryDataSourceLink.addDynalink(fieldnum(VendPurchOrderJour, TIDPOGroupId),
                                        element.args().record(),
                                        fieldnum(TECPOGroupHeader, POGroupId));
    }
    //END
}

add filter to form datasource method:ExecuteQuery example code

public void executeQuery()
{
    ;
    TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).clearRanges();
    if(statusFilter.valueStr() == enum2str(TEC_AllDraftPosted::Draft))
        TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Draft));
    if(statusFilter.valueStr() == enum2str(TEC_AllDraftPosted::Posted))
        TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Posted));
    //add by fanddy
    if(statusFilter.valueStr() == enum2str(TEC_AllDraftPosted::Confirmed))
        TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,Status)).value(sysquery::value(TECPOGroupStatus::Confirmed));
    //END add by fanddy
    if(CreatorFilter.value() == true)
        TECPOGroupHeader_ds.query().dataSourceTable(tableNum(TECPOGroupHeader)).addRange(fieldnum(TECPOGroupHeader,CreatedBy)).value(sysquery::value(curUserId()));
    super();
}

get Dimension Value from DefaultDimension

public static str 255 getDimensionValueTID(RecId defaultDimension, Name dimName)
{
    DimensionAttributeValueSetStorage dimStorage;
    ;

    dimStorage = DimensionAttributeValueSetStorage::find(defaultDimension);

    return dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName(dimName).RecId);
}

 
how to use :
 
getDimensionValueTID(_ledgerJournalTrans.DefaultDimension, 'BusinessUnit')

Global Variable in X++ AX 2012

some circumtances i must use global variable.

read :
http://msdn.microsoft.com/en-us/library/aa891830.aspx

more example how to use :

SysGlobalCache globalCache;
globalCache = ClassFactory.globalCache();
globalCache.set(strFmt("%1%2",curUserId(),"WhatEver"), 0, "ANY VALUE");

on other class.

SysGlobalCache globalCache;
globalCache = ClassFactory.globalCache();
info(strFmt("%1",globalCache.get(strFmt("%1%2",curUserId(),"WhatEver"), 0)));

Auto Convert field from Excel to X++

public static str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
{
    switch (_cv.variantType())
    {

        case (COMVariantType::VT_BSTR): return _cv.bStr();
        case (COMVariantType::VT_R4): return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);
        case (COMVariantType::VT_R8): return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);
        case (COMVariantType::VT_DECIMAL): return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);
        case (COMVariantType::VT_DATE): return date2str(_cv.date(),123,2,1,2,1,4);
        case (COMVariantType::VT_EMPTY): return '';

        default: throw error(strfmt('@SYS26908', _cv.variantType()));
    }
    return '';
}

how to use example :
YourClass::COMVariant2Str(cells.item(row,17).value());

Create Journal Upload with Batch Form

Capture

just duplicate class tutorial_runBaseBatch, then modify the following class :

on class declaration :

public class JournalUploadExcextends RunBaseBatch
{
    DialogField         dfFileName;
    FileName            fileName  ;

    #define.CurrentVersion(1)
    #define.Version1(1)
    #localmacro.CurrentList
        fileName
    #endmacro
}

on dialog() :

public Object dialog()
{
    #File
    DialogRunbase       dialog = super();
    #resAppl
    ;
    dfFileName = dialog.addFieldValue(extendedTypeStr(FileNameOpen),fileName);
    dialog.filenameLookupFilter(["All files", #AllFiles]);

    return dialog;
}

on method getFromDialog() :

public boolean getFromDialog()
{
    //do what ever you want in this class. i call upload journal method with PathFile as parameter
    JournalUploadExc::JournalUpload(dfFileName.value());
    return super();
}

voila.. !! done.. !!

Job Upload General Journal from excel

public static boolean JournalUpload(str _filePath)
{
    container            accEntryPattern;
    container            offSetEntryPattern;

    LedgerJournalACType LedgerJournalACType;
    ledgerJournalName ledgerJournalName;
    journalID JournalID;
    axLedgerJournalTable axLedgerJournalTable;
    axledgerJournalTrans axLedgerJournalTrans;
    LedgerJournalType JournalType = ledgerJournalType::Daily;


    SysExcelApplication                         application = SysExcelApplication::construct();
    SysExcelWorkbooks                           workbooks   = application.workbooks();
    SysExcelWorkbook                            workbook;
    SysExcelWorksheets                          workSheets;
    SysExcelWorksheet                           workSheet;
    SysExcelCells                               cells;
    SysExcelCell                                cell;
    int                                         row;
    str                                         filename, numSeq;

    boolean         _return = true;
    ;


    startLengthyOperation();
    //filename = "C:\\GJCashBank30April2013.xlsx";
    filename = _filePath;

    try
    {
        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();

            row = 5;

            ttsBegin;
            axLedgerJournalTable = new axLedgerJournalTable();
            axLedgerJournalTable.parmJournalName("GenJrn");
            axLedgerJournalTable.parmJournalType(JournalType);
            axLedgerJournalTable.parmName("General Journal");
            axLedgerJournalTable.save();
            JournalID = axLedgerJournalTable.parmJournalNum();

            while (cells.item(row,2).value().bStr() != "")
            {
                //Create Trans ...
                axLedgerJournalTrans = new axLedgerJournalTrans();
                axLedgerJournalTrans.parmJournalNum(JournalID);

                //start from A
                axLedgerJournalTrans.parmTransDate(cells.item(row,1).value().date());
                axLedgerJournalTrans.parmAccountType(  str2Enum(LedgerJournalACType, cells.item(row,2).value().bStr()));

                if(cells.item(row,2).value().bStr() == "Ledger")
                {

                    accEntryPattern =
                    [   cells.item(row, 3).value().bStr() + "-" + "Disp",
                        cells.item(row, 3).value().bStr(),
                        4,
                        "BusinessUnit",
                        cells.item(row,17).value().bStr(),
                        "Department",
                        cells.item(row,18).value().bStr(),
                        "ProductCode",
                        cells.item(row,19).value().bStr(),
                        "ProjectCode",
                        cells.item(row,20).value().bStr()
                    ];
                    axLedgerJournalTrans.parmLedgerDimension(AxdDimensionUtil::getLedgerAccountId(accEntryPattern));
                }

                if(cells.item(row,5).value().double() != 0)
                {
                    axLedgerJournalTrans.parmAmountCurDebit(cells.item(row,5).value().double());
                }

                if(cells.item(row,6).value().double() != 0)
                {
                        axLedgerJournalTrans.parmAmountCurCredit(cells.item(row,6).value().double());
                }

                axLedgerJournalTrans.parmtxt(cells.item(row,7).value().bStr());
                axLedgerJournalTrans.parmOffsetAccountType(  str2Enum(LedgerJournalACType,cells.item(row,6).value().bStr()));

                if(cells.item(row,8).value().bStr() == "Ledger")
                {
                    offSetEntryPattern =
                    [   cells.item(row, 9).value().bStr() + "-" + "Disp",
                        cells.item(row, 9).value().bStr(),
                        4,
                        "BusinessUnit",
                        cells.item(row,17).value().bStr(),
                        "Department",
                        cells.item(row,18).value().bStr(),
                        "ProductCode",
                        cells.item(row,19).value().bStr(),
                        "ProjectCode",
                        cells.item(row,20).value().bStr()
                    ];
                    axLedgerJournalTrans.parmOffsetLedgerDimension(AxdDimensionUtil::getLedgerAccountId(offSetEntryPattern));
                }
                //
                axLedgerJournalTrans.parmCurrencyCode(cells.item(row,10).value().bStr());
                axLedgerJournalTrans.parmExchRate(cells.item(row,11).value().double());
                axLedgerJournalTrans.parmInvoice(cells.item(row,12).value().bStr());
                axLedgerJournalTrans.parmPaymReference(cells.item(row,13).value().bStr());
                axLedgerJournalTrans.parmBankTransType(cells.item(row,14).value().bStr());
                axLedgerJournalTrans.parmDocumentNum(cells.item(row,15).value().bStr());
                axLedgerJournalTrans.parmDocumentDate(cells.item(row,16).value().date());
                //

                axLedgerJournalTrans.save();
                row++;

                ttsCommit;
            }

            info('Imported  Items');
        }
    }
    catch(Exception::Error)
    {
        _return = false;
    }

    return _return;
}

File Path X++ AX 2012

static void  main(Args  _args)
{
    FileName            fileName  ;
    container           c;
    FileIOPermission    permission;
    TextBuffer          textBuffer;
    str                 readText;
    str                 readLine;
    int                 etgline;
    int                 i = 0;
    Dialog              dialog = new Dialog("Journal Upload Excel");
    DialogField         dfFileName;
    str 100            filename2;
    container       con;
    ;
    #File

    dfFileName = dialog.addField(extendedTypeStr(FileNameOpen));
    dialog.filenameLookupFilter(["All files", #AllFiles]);

    if (dialog.run())
    {
        fileName = dfFileName.value();
        info(filename);

   }
}

Create Purchase Order via X++

void clicked()
{
    PurchTable  purchTable;
    PurchLine   purchLine;
    InventDim   inventDim;
    NumberSeq   numberSeq;

    //add by fanddy
    AxPurchTable    axPurchTable;
    AxPurchLine     axPurchLine;
    PurchFormLetter purchformLetter;
    //END

   //create PO Header
   purchTable.clear();
   purchTable.initFromVendTable(VendTable::find(TECPOGroupHeader.VendAccount));
   purchTable.DefaultDimension = InventTable::find(POGroupLine.ItemId).DefaultDimension;

   axPurchTable = axPurchTable::newPurchTable(purchTable);
   axPurchTable.parmPurchId(NumberSeq::newGetNum(PurchParameters::numRefPurchId()).num());
   axPurchTable.parmPurchaseType(PurchaseType::Purch);
   axPurchTable.parmDocumentStatus(DocumentStatus::PurchaseOrder);
   axPurchTable.parmAccountingDate(systemDateGet());
   axPurchTable.parmDeliveryDate(systemDateGet());
   axPurchTable.parmPurchStatus(PurchStatus::Backorder);
   axPurchTable.save();

   info(strFmt('Create PO %1', Purchtable.PurchId));

   //create PO Line
   purchLine.clear();
   purchLine.initValue();
   purchLine.initFromPurchTable(purchTable);
   purchLine.initFromInventTable(InventTable::find(POGroupLine2.ItemId));
   axPurchLine = AxPurchLine::newPurchLine(purchLine);
   axpurchLine.parmItemId('YOUR ITEMID');
   axPurchLine.parmPurchQty(YOUR QTY);
   axPurchLine.parmPurchPrice(YOUR PRICE);
   axPurchLine.parmInventDimId('YOUR INVENTDIMID');
   axPurchLine.save();

}

Auto Allocate Charges before auto Confirm Purchase Order

static void AutoAllocateCharges_beforeConfirmPO(Args _args)
{
    PurchTable purchTable;
    PurchFormLetter purchFormLetter;
    Args args = new Args();
    MarkupAllocation markupAllocation;

    select firstOnly purchTable where purchTable.PurchId=='1016-PO-14-000191';
    args.record(purchTable);
    //auto allocate charges
    markupAllocation = MarkupAllocation::newMarkupAllocation(args.record());
    markupAllocation.run(args.parmObject());
    
    purchFormLetter = PurchFormLetter::construct(DocumentStatus::PurchaseOrder);
    purchFormLetter.update(purchTable, purchTable.PurchId);
}

Modify Controller Class for Query Based Report SSRS

public void prePromptModifyContract()
{
    //add a range in the report query  
      SrsReportHelper::addParameterValueRangeToQuery(this.getFirstQuery(),tableNum(SSRSReportDemo),fieldNum(SSRSReportDemo, RecId),SysQuery::value(this.parmArgs().record().RecId));
}

source : https://community.dynamics.com/ax/b/dynamics101trainingcenterax/archive/2014/01/30/using-controller-class-in-developing-ssrs-reports-in-microsoft-dynamics-ax-2012.aspx

get product-category Descendants example

public FreeText getDecendants(ItemId _itemid)
{
    str Decendants;
    EcoResProductCategory ecoResProductCategory;
    EcoResCategory        ecoResCategory;

    EcoResCategory        ecoResCategoryAscendants;

    select firstOnly ecoResProductCategory
    where
        ecoResProductCategory.Product==EcoResProduct::findByProductNumber(_itemid).RecId;

    select firstOnly ecoResCategory where ecoResCategory.RecId==ecoResProductCategory.Category;
    Decendants = ecoResCategory.Name;
    while select * from ecoResCategoryAscendants
            order by ecoResCategoryAscendants.NestedSetRight
                where   ecoResCategoryAscendants.CategoryHierarchy == EcoResCategory.CategoryHierarchy
                        && ecoResCategoryAscendants.NestedSetLeft  < EcoResCategory.NestedSetLeft
                        && ecoResCategoryAscendants.NestedSetRight > EcoResCategory.NestedSetRight
    {
       Decendants = strFmt("%1-%2",ecoResCategoryAscendants.Name,Decendants);
    }


    return Decendants;
}

using “update_record” instead of “while select forupdate”

description on MSDN :
“This means that certain tasks may have improved performance by using the power of the SQL server.”

replace code below for a better perfomance.

MyTable myTableBuffer;
;
while select forUpdate mytableBuffer
{
mytableBuffer.field1 = field * 1.10;
}

with


MyTable myTableBuffer;
;
update_recordset myTableBuffer
setting field1 = field1 * 1.10;

source : http://msdn.microsoft.com/en-us/library/aa674382.aspx

How to print barcode Label , SSRS can’t print on Landscape Printer AX 2012r2

Hi there ,
after the report being save to PDF on local drive, i call adobe to print barcode,
i have to did that, because SSRS can’t print on landscape printer.

here is my example code

void clicked()
{
    //AIFD_PrintBarcode
    AI_PrintBarcodeController  aiBarcode = new AI_PrintBarcodeController();
    AI_PrintBarcodeContract    aiContract = new AI_PrintBarcodeContract();
    ProdJournalProd            prodJournalProdLoc;
    SRSPrintDestinationSettings  printSettings = new SRSPrintDestinationSettings();
    PrintJobSettings    printJobSettings = new PrintJobSettings();
    Dialog              dialog = new Dialog();
    DialogField         dialogFileName;
    str                 adobeExe;
    str                 adobeParm;

    str                 fileName;
    int                 fileCount;

    //AIFD
    InteropPermission   permission;
    str errorMessage;
    //END
    real WeightInreal;
    permission = new InteropPermission(InteropKind::ClrInterop);
    permission.assert();

    super();
    try
    {
        prodJournalProdLoc = ProdJournalProd_ds.getFirst(true);

        while (prodJournalProdLoc)
        {
            fileCount++;
            aiBarcode.parmReportName(ssrsReportStr(AI_PrintBarcoderReport,Report));
            aiBarcode.parmShowDialog(false);
            aiContract.parmRecordId(prodJournalProdLoc.RecId);
            aiContract.parmDocumentTitle(&quot;x&quot;);

            //printSettings = aiBarcode.parmReportContract().parmPrintSettings();
            printSettings.printMediumType(SRSPrintMediumType::File);
            //printSettings.landscape(false);
            //printSettings.printerPageSettings('KRRLabelBarcode');
            //info(strFmt(&quot;%1&quot;,printSettings.printerPageSettings()));

            fileName = strFmt(&quot;D:\\barcode%1.pdf&quot;,fileCount);

            printSettings.fileFormat(SRSReportFileFormat::PDF);
            printSettings.fileName(fileName);
            printSettings.printerName(&quot;TOSHIBA B-SA4TP TS&quot;);
            printSettings.landscape(false);
            printSettings.overwriteFile(true);
            aiBarcode.parmReportContract().parmRdpContract(aiContract);
            aiBarcode.parmReportContract().parmPrintSettings(printSettings);

            aiBarcode.startOperation();

            WeightInReal = sleep(500);
            //printJobSettings.printerSettings('SysPrintForm');
            printJobSettings.setTarget(PrintMedium::Printer);
            printJobSettings.preferredTarget(PrintMedium::Printer);
            adobeExe = WinAPI::findExecutable(fileName);

            adobeParm = strFmt(' /t &quot;%1&quot; &quot;%2&quot; &quot;%3&quot; &quot;%4&quot;',
                               fileName,
                               &quot;TOSHIBA B-SA4TP TS&quot;,
                               printJobSettings.printerDriverName(),
                               printJobSettings.printerPortName());

            winAPI::shellExecute(adobeExe,  adobeParm);

            WeightInReal = sleep(500);

            prodJournalProdLoc = ProdJournalProd_ds.getNext();
         }
     CodeAccessPermission::revertAssert();
    }
    catch(Exception::CLRError)
    {
        errorMessage = AifUtil::getClrErrorMessage();

        CodeAccessPermission::revertAssert();

        throw error(errorMessage);
    }
    //END
}

Custom lookup example code x++ AX 2012

Example of custom lookup for itemid on PurchLine , form : PurchTable

public void lookup()
{
 SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(InventTable), this);
 Query query = new Query();
 QueryBuildDataSource queryBuildDataSource, queryBuildDataSource2;
 QueryBuildRange queryBuildRange, queryBuildRange2;
 QueryBuildLink queryBuildLink;
 BuyBackScheme BuyBackScheme;
 str FilterCategory;
 //super();

 queryBuildDataSource = query.addDataSource(tablenum(InventTable));
 //add by Fanddy
 if(InventBuyerGroup::find(PurchTable.ItemBuyerGroupId).InventOwnership == InventOwnership::MD)
 {
 queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(PdsApprovedVendorList));
 queryBuildDataSource.joinMode(JoinMode::InnerJoin);
 queryBuildDataSource.relations(true);

 queryBuildDataSource.addRange(fieldNum(PdsApprovedVendorList,PdsApprovedVendor)).value(PurchTable.OrderAccount);
 queryBuildDataSource.addRange(fieldNum(PdsApprovedVendorList,ValidFrom)).value(strFmt(“&lt;= %1″,DateTimeUtil::date(DateTimeUtil::removeTimeZoneOffset(PurchTable.createdDateTime,TimeZone::GMTPLUS0700_BANGKOK_HANOI_JAKARTA))));
 queryBuildDataSource.addRange(fieldNum(PdsApprovedVendorList,ValidTo)).value(strFmt(“&gt;= %1″,DateTimeUtil::date(DateTimeUtil::removeTimeZoneOffset(PurchTable.createdDateTime,TimeZone::GMTPLUS0700_BANGKOK_HANOI_JAKARTA))));

 }
 //END by fandd

 sysTableLookup.addLookupfield(fieldnum(InventTable, itemId));
 sysTableLookup.addLookupMethod(‘itemName’);
 sysTableLookup.addLookupMethod(‘itemGroupId’);
 sysTableLookup.addLookupfield(fieldnum(InventTable, NameAlias));
 sysTableLookup.addLookupMethod(tableMethodStr(InventTable,pdsDefaultOrderType));
 sysTableLookup.addLookupfield(fieldnum(InventTable, RetailType));
 sysTableLookup.parmQuery(query);
 sysTableLookup.performFormLookup();

}

happy daxing !

 

Create BOM Journal example via x++ AX 2012

 

static void createMovJournal(Args _args)
{
  InventJournalTable  journalTable;
  InventJournalTrans  journalTrans;
  JournalId           journalId;
  CustomReportFinishedConvertItem    convertItem;
  InventJournalCheckPost journalCheckPost = new InventJournalCheckPost();
  ;

  // Init JournalTable

  journalTable.clear();
  journalTable.initValue();
  journalTable.initFromInventJournalName(InventJournalName::find(InventParameters::find().BomJournalNameId));
  journalId = NumberSeq::newGetNum(InventParameters::numRefInventJournalId()).num();
  journalTable.JournalId = journalId;
  journalTable.insert();

  journalTrans.clear();
  journalTrans.initFromInventJournalTable(journalTable);
  journalTrans.initFromInventTable(InventTable::find(convertItem.ItemIdChild));
  journalTrans.InventDimId = convertItem.InventDimId;
  journalTrans.Qty = convertItem.Qty;
  journalTrans.PriceUnit        = InventTableModule::find(journalTrans.ItemId, ModuleInventPurchSales::Invent).pcsPrice();
  journalTrans.CostAmount       = journalTrans.Qty * journalTrans.PriceUnit;
  journalTrans.InventTransId = NumberSeq::newGetNum(InventParameters::numRefInventTransId()).num();
  journalTrans.TransDate = today();
  journalTrans.Voucher = NumberSeq::newGetNumFromId(journalTable.VoucherNumberSequenceTable).num();
  journalTrans.BOMLine = NoYes::No;
  journalTrans.insert();

  inventTransId    = journalTrans.InventTransId;

  journalTrans.clear();
  journalTrans.initFromInventJournalTable(journalTable);
  journalTrans.initFromInventTable(InventTable::find(convertItem.ItemId));
  journalTrans.InventDimId = convertItem.InventDimIdRAF;
  journalTrans.Qty = -1*convertItem.QtyConversion;
  journalTrans.PriceUnit        = InventTableModule::find(journalTrans.ItemId, ModuleInventPurchSales::Invent).pcsPrice();
  journalTrans.CostAmount       = journalTrans.Qty * journalTrans.PriceUnit;
  journalTrans.InventTransId = NumberSeq::newGetNum(InventParameters::numRefInventTransId()).num();
  journalTrans.TransDate  = today();
  journalTrans.Voucher = NumberSeq::newGetNumFromId(journalTable.VoucherNumberSequenceTable).num();
  journalTrans.BOMLine = NoYes::Yes;
  journalTrans.InventTransIdFather =  inventTransId;
  journalTrans.insert();
  //end edited by fanddy


  // Call the static method to post the journal
  if(InventJournalCheckPost::newPostJournal(journalTable).validate())
  InventJournalCheckPost::newPostJournal(journalTable).run();

}

source :

1.http://www.andesoft.net/creating-bom-journal-using-x-code-ax-2009-ax-2012/

2.http://learnax.blogspot.com/2010/01/x-code-to-create-and-post-inventory.html

get last WorkFlow Comment. AX 2012

just add this code to tables method and drag it to the grid

display public server str aiComment()
{
WorkflowTrackingStatusTable trackingStatusTable;
WorkflowTrackingTable trackingTable;
WorkflowTrackingCommentTable trackingCommentTable;
WorkflowComment commentLoc = “”, comment;
;

while select trackingStatusTable where trackingStatusTable.ContextRecId == this.RecId join trackingTable
where trackingTable.WorkflowTrackingStatusTable == trackingStatusTable.RecId join trackingCommentTable
where trackingCommentTable.WorkflowTrackingTable == trackingTable.RecId && trackingCommentTable.Comment != commentLoc
{
comment = trackingCommentTable.Comment;
}

return comment;
}

by : _fiant

one PurchReq Line multiple PO

got client that really what one PR line can posting to multiple.

class RequisitionReleaseStrategy\Main

add this code below

//AIFD
if(cekProses)
{
    ttsBegin;
    for (purchReqLine = fds.getFirst(processMarkedRows) ? fds.getFirst(processMarkedRows) : _args.record(); purchReqLine; purchReqLine = fds.getNext())
    {
        select forUpdate purchReqLineLoc where purchReqLineLoc.RecId==purchReqLine.RecId;
        purchReqLineLoc.PurchLineCreated = false;
        purchReqLineLoc.RequisitionStatus = PurchReqRequisitionStatus::Approved;
        if(purchReqLineLoc)
        {
            purchReqLineLoc.update();
        }
    }
    ttsCommit;
}

Query based Report , add parameter dateFrom and dateTo//

//your language script here
[SysEntryPointAttribute(false)]
public void processReport()
{
    Query q;
    QueryRun qr;
    TransDate dateTo,dateFrom;

    AITransporterPerformanceReportContract contract = this.parmDataContract() as AITransporterPerformanceReportContract;

    dateTo = contract.parmDateTo();
    dateFrom = contract.parmDateFrom();

    delete_from aiTmp;
    q = this.parmQuery();
    q.dataSourceTable(tablenum(CustPackingSlipJour)).addRange(fieldnum(CustPackingSlipJour, DeliveryDate)).value(queryRange( dateFrom, dateTo));
    qR = new QueryRun(q);
    while(qR.next())
    {
        // your logic
    }
}

Vendor or Customer , Invoicing or Delivery Address

display Addressing  InvoicingAddress()
{
    Addressing address;
    DirPartyRecId party;
 
    party = CustTable::find(custConfirmJour.InvoiceAccount).Party;
    address = DirParty::getPostalAddressByType(party, LogisticsLocationRoleType::Invoice);
 
    if (!address)
        address = DirParty::getPostalAddressByType(party, LogisticsLocationRoleType::Delivery);
 
    return address;
}

source : http://dynamicsuser.net/forums/t/56107.aspx

Column BackColor Grid Form AX 2012

override method displayOptions in gridForm.

public void displayOption(Common _record, FormRowDisplayOption _options)
{
   #define.Red(255,0,0)
   TableNameDataSource   tableNameLoc;

   tableNameLoc = _record.data();

   //your logic if while etc
    
   _options.backColor(WinAPI:RGB2int(#Red);
   _options.affectedElementsByControl(element.controlId(CONTROL_NAME_IN_FORM));
   
   super(_record,_options);
   
}

contributor : _le

Print Sales Invoice via job X++ ax 2012

protected void printReportPdf(Common _record)
{
    args args = new args();
    CustInvoiceJour custInvoiceJour = _record;
    SRSPrintDestinationSettings printSettings;
    SalesInvoiceController controller = new SalesInvoiceController();

    args.record(_record);

    pdfPath = WinAPI::getTempPath() + custInvoiceJour.InvoiceId + ".pdf";

    // imposta nome report
    controller.parmReportName(ssrsReportStr(SalesInvoice, Report));

    // get print settings from contract
    printSettings = controller.parmReportContract().parmPrintSettings();

    // set print medium
    printSettings.printMediumType(SRSPrintMediumType::File);
    printSettings.fileFormat(SRSReportFileFormat::PDF);
    printSettings.overwriteFile(true);
    printSettings.fileName(pdfPath);

    // forzo che non vengano cambiati i parametri di stampa che gli passo io
    controller.DEVparmLockDestinationProperties(true);

    // suppress the parameter dialog
    controller.parmShowDialog(false);
    controller.parmArgs(args);

    // start operation
    controller.startOperation();
}

source : http://daxldsoft.blogspot.com/2012/07/print-salesinvoice-packingslip-or.html

print Sales Picking List via Jobs X++ AX 2012

static void Job6(Args _args)
{
    WMSPickingRoute         wmsPickingRoute;
    WmsPickingList_OrderPickController  aiController = new WmsPickingList_OrderPickController();
    WmsPickingList_OrderPickContract    aiContract   = new WmsPickingList_OrderPickContract();
    SRSTmpDataStore                     srsTmpDataStore;
 
    select firstOnly wmsPickingRoute where wmsPickingRoute.RecId==5637144577;//iisi sendiri jngn malas
    
    srsTmpDataStore.Value = Global::buf2Con(wmsPickingRoute);
    ttsbegin;
    srsTmpDataStore.insert();
    ttscommit;
 
    aicontroller.parmReportName(ssrsReportStr(WmsPickingList_OrderPick,Report));
    aicontroller.parmShowDialog(false);
 
    aiController.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::Screen);
   // aiController.parmReportContract().parmPrintSettings().printerName(“Xerox WorkCentre 3210 PCL 6 (Copy 1)”);
 
    aiContract.parmPickingRouteId(srsTmpDataStore.recid);
    aiContract.parmDocumentTitle(“x”);
    aiContract.parmShowRegistered(true);
    aiContract.parmShowActivated(true);
    aiContract.parmShowStarted(true);
    aiContract.parmShowPicked(true);
    aiContract.parmShowStaged(true);
    aiContract.parmShowLoaded(true);
    aiContract.parmShowCancelled(false);
    aiContract.parmPrintCopyOriginal(PrintCopyOriginal::Original);
 
    aiController.parmReportContract().parmRdpContract(aiContract);
    //aicontroller.startOperation();
 
    //aiController.parmExecutionMode(SysOperationExecutionMode::Synchronous);
 
    // Run the report
    aiController.runReport();
}

get WorkFlow Approval Name

WHILE select workflowtrackingstatustable
join workflowtrackingtable
where workflowtrackingstatustable.ContextRecId == PurchTable.RecId //RecId of the record
&& workflowtrackingtable.TrackingContext == workflowtrackingcontext::WorkItem
&& workflowtrackingtable.TrackingType == workflowtrackingtype::Approval
&& workflowtrackingtable.WorkflowTrackingStatusTable == workflowtrackingstatustable.RecId
{
     info(strFmt(“%1″,SysWorkflowHelper::getDirPartyNameFromUserId(workflowtrackingtable.USER)));
}

or

display HcmWorkerName displayWorkflowApproverName()
{
    WorkflowTrackingTable tracking;
    WorkflowTrackingStatusTable trackingStatus;
    WorkflowWorkItemTable workitem;
    HcmWorkerName ret = "";
    UserInfo user;

    select tracking order by tracking.createdDateTime desc join trackingStatus
        where tracking.WorkflowTrackingStatusTable == trackingStatus.RecId
        && tracking.TrackingType != WorkflowTrackingType::Completion
        && ((tracking.TrackingContext == WorkflowTrackingContext::Step) || (tracking.TrackingContext == WorkflowTrackingContext::Task))
        && trackingStatus.ContextTableId==this.TableId && trackingStatus.ContextRecId == this.RecId;
    select workitem where workitem.RefRecId == this.RecId && workitem.RefTableId == this.TableId &&
        ((workitem.Status == WorkflowWorkItemStatus::Pending) || (workitem.Status == WorkflowWorkItemStatus::Delegated));

    return HcmWorker::find(HcmWorker::userId2Worker(workitem.UserId)).name();


}

fix bugs Remove Sales Line when the sales Line already pick,pack,or invoiced on AX2012

i don’t know this is a bug come from our own customize or bugs originated from AX 2012.
anyway we have to fix this. Our client found this bug and requesting to add some validation and disable Remove Button on sales line when already pick.

so how i did this :

go to form SalesTable,
locate button Remove , and set AutoDeclaration to Yes, so we can call this button and disable or enable it via code.
look for method “active” in datasource SalesLine.

add this code below :

//AIFD fix bug remove button
    InventTrans     inventTransLoc;
    boolean         cekStatus;
    WMSOrderTrans   wmsOrderTrans;
  //END


    //add this code before return ret;
    cekStatus = true;
    //cek status dari inventtrans
    while select inventTransLoc   where inventTransLoc.InventTransOrigin==
    InventTransOrigin::findByInventTransId(SalesLine.InventTransId).RecId
    {
        if(inventTransLoc.StatusIssue != StatusIssue::OnOrder)
        {
            cekStatus = false;
            break;
        }
    }

    //cek status dari picking list
    while select wmsOrderTrans where   wmsOrderTrans.inventTransId==SalesLine.InventTransId
    {
        cekStatus = false;
        break;
    }


    if( salesLine.SalesStatus == SalesStatus::Backorder &amp;&amp; cekStatus)
    {
        LineStripDelete.enabled(true);
    }
    else
    {
       LineStripDelete.enabled(false);
    }
    
    //END

voila… happy Daxing !!

add Department Name to grid form PurchReqTableListPage

On table purchReqTable,

create new method and add this

display public server Name aiDepartment()
{
    return HcmWorker::find(this.Originator).primaryDepartmentName();
}

on form PurchReqTableListPage, right click and choose Restore (to restore DataSource).
and add string to Grid, set datasource and method on properties.

voila !! done..

happy Daxing… !!

Create Number Sequence on Sales Module, custInvoiceJour

read more on
http://swathidynamicsax.blogspot.com/2012/07/simple-steps-to-create-number-sequence.html

My case is to create number sequence on Sales Module,
so i had to modify some code on there..

on table SalesParameters.

static client server NumberSequenceReference AI_numRefPackingListId()
{
    return NumberSeqReference::findReference(extendedTypeNum(AI_PackingListId));
}

add this code to class NumSeqModuleSalesOrder\LoadModule

//AIFD_ seq packingListId
datatype.parmDatatypeId(extendedTypeNum(AI_packingListId));
datatype.parmReferenceHelp(literalStr("Packing List Id setiap create Invoice"));
datatype.parmReferenceLabel(literalStr("Packing List Id"));
datatype.parmWizardIsManual(NoYes::No);
datatype.parmWizardIsChangeDownAllowed(NoYes::No);
datatype.parmWizardIsChangeUpAllowed(NoYes::No);
datatype.parmWizardHighest(999999);
datatype.parmSortField(66);
datatype.addParameterType(NumberSeqParameterType::DataArea, true, false);
this.create(datatype);
//END

and modify the code on job

static void AIFD_JobSequence(Args _args)
{
   NumberSeqModuleSalesOrder NumberSeqModuleSalesOrderLoc = new NumberSeqModuleSalesOrder();
   NumberSeqModuleSalesOrderLoc.load();
}

then run wizard number sequence and run this job

static void AIFD_TestRunSeqNum(Args _args)
{
   NumberSeq numberSeq;
   AI_PackingListId num;
   ;
   numberSeq = NumberSeq::newGetNum(SalesParameters::AI_numRefPackingListId());
   num = numberSeq.num();
   info(num);
}

done.. voila, i need to add my newly added number sequence to my customize CustInvoiceJour,
so when sales Invoice is created, my newly added number sequence is generated and automatically save in table CustInvoiceJour..

how i did this :

1. add new field on custInvoiceJour, i named it AI_PackingListId and set the extended data type to my new data type.

2. modify some code on class SalesInvoiceJournalCreateBase\initJournalHeader

NumberSeq numberSeqLoc;
AI_PackingListId numPackingListId;

numberSeqLoc = NumberSeq::newGetNum(SalesParameters::AI_numRefPackingListId());
numPackingListId = numberSeqLoc.num();

//add this code below custInvoiceJour.initFromSalesParmTable(salesParmTable);
custInvoiceJour.AI_PackingListId = numPackingListId;
//END

3. Last, Generate Incremental CIL , dont forget that 🙂
Voila.. done !!

happy Daxing !!

Send Email Proforma Sales Customize with Job.

class : SalesInvoiceController.OutputReport ()

//AIFD custom send email proforma testing
SrsReportEMailDataContract emailContract;
SRSPrintDestinationSettings         printSettings;
//END

//just example.. you can customize the parameter field Subject etc on email with your own parameter and conditions.
//add this code below at the bottom of class OutputReport
if(custInvoiceJour.isProforma())
{
    emailContract = new SrsReportEMailDataContract();
 
    emailContract.parmAttachmentFileFormat(SRSReportFileFormat::PDF);
    emailContract.parmSubject(“My Report”);
    emailContract.parmTo(“jimmi.tandiah@yahoo.com”);
 
    printSettings = this.parmReportContract().parmPrintSettings();
 
    printSettings.printMediumType(SRSPrintMediumType::Email);
    printSettings.parmEMailContract(emailContract);
    printSettings.fileFormat(SRSReportFileFormat::PDF);
 
    formLetterReport.getCurrentPrintSetting().parmPrintJobSettings(printSettings);
}

—————————-
Job example

static void AI_TestEmailProforma(Args _args)
{
    SalesFormLetter letter = SalesFormLetter::construct(DocumentStatus::Invoice);
    SalesTable sale = SalesTable::find(‘SO-14000599′);

    ttsBegin;
    letter.update(sale, systemDateGet(), SalesUpdate::All, AccountOrder::None, true, true,true);
    ttsCommit;
}

Refreshing Master Form Data Source from Child Form

//Example when modifying checkbox value and modify parent datasource,
// it will have an error unless if you refreshing parent datasource after you modify it,

public boolean modified()
{
//AIFD_ custom exchrate custtrans -> ledgerjournal trans
#Task
LedgerJournalTrans ledgerJournalTransLoc;
FormDataSource fds;

//END

boolean ret;

ret = super();

//AIFD_ custom exchrate custtrans -> ledgerjournal trans
ttsBegin;
if(this.checked())
{
select forUpdate LedgerJournalTransLoc where ledgerJournalTransLoc.RecId == originator.RecId;
LedgerJournalTransLoc.ExchRate = CustTrans.ExchRate;
ledgerJournalTransLoc.update();
}
else
{
select forUpdate LedgerJournalTransLoc where ledgerJournalTransLoc.RecId == originator.RecId;
LedgerJournalTransLoc.ExchRate = 100;
ledgerJournalTransLoc.update();

}

ttsCommit;

fds = element.args().record().dataSource();
fds.research(true);

fds.refresh();
//END

 

return ret;
}

Weight Detection PortComm

//Get PortComm number that already set from .txt file on local drive.
private str getPortComm()
{
    #file
    str getCOMport;
    boolean cekPortExist;
    AsciiIo file;
    Container data;
    int i;
    System.IO.FileInfo fileInfo;
    ;

    new InteropPermission(InteropKind::ClrInterop).assert();
    fileInfo = new System.IO.FileInfo(@”C:\portcomm.txt”);
    if(fileInfo.get_Exists())
    {
        file = new AsciiIo(@”C:\portcomm.txt”,#io_read);
        file.inFieldDelimiter(‘\t’);
        file.inRecordDelimiter(‘\n’);

        if(file)
        {
            while(file.status() == IO_Status::Ok)
            {  
                data = file.read();
                for(i = 1; i <= conlen(data); i++)
                {
                    getCOMport = any2str(conpeek(data, i));
                }
            }
        }

    }

    return strLTrim(getCOMport);
}

————————————

//Get Weight from PortComm
private real AI_getWeight()
{
    System.String netstring;
    System.String sample;

    int WeightInReal;
    int tryCount;
    str value;
    str value2;
    str portComm;
    System.IO.Ports.SerialPort port3;
    real weightFinal;

    tryCount = 0;

    portComm = this.getPortComm();
    if(portComm)
    {
        port3 = new System.IO.Ports.SerialPort();
        value = “0”;
        port3.set_PortName(portComm);
        port3.set_BaudRate(9600);
        port3.set_Parity(System.IO.Ports.Parity::None);
        port3.set_DataBits(8);
        port3.set_StopBits(System.IO.Ports.StopBits::One);
        port3.set_Handshake(System.IO.Ports.Handshake::None);

        try
        {
            port3.Open();
            while(tryCount<1)
            {
                WeightInReal = sleep(500);
                netstring = port3.ReadExisting();
                value = netstring;
                value2 = value;
                tryCount++;
            }
            port3.Close();
            port3.Dispose();
        }
        catch(Exception::CLRError)
        {
            info(“Wrong Port Comm Number”);
        }

        value2 = strReplace(value,”=”,””);
        value2 = strReplace(value2,”-“,””);
        value2 = strLTrim(value2);
        value2 = subStr(value2,1,6);
        weightFinal = str2num(value2);
    }
    else
    {
        info(“Check file portcomm.txt at C:\””);
    }

    if(!weightFinal)
    {
        info(“Please check your device, clarify it already turned on”);
    }
    return weightFinal;
}

—————————

on method button clicked, override.. some example here for you

void clicked()
{
    real _value;
    InventTable inventTableLoc;
    super();
    _value = this.AI_getWeight();
    
    Select * from inventTableLoc where inventTableLoc.ItemId == ProdJournalProd.ItemId;
    if(_value)
    {
        if(inventTableLoc.AISatuanPrimer!=”Kg”)
        {
            ProdJournalProd.AISatuanTertier = _value;
            ProdJournalProd_AISatuanTertier.enabled(False);
        }
        else
        {
            ProdJournalProd.AISatuanTertier =_value;
            ProdJournalProd_AISatuanTertier.enabled(True);
        }
    }
    ProdJournalProd_ds.executeQuery();
}

Location and Pallet Id Validation before posting form: ProdJournalTransBOM

void clicked()
{
    WMSPallet wmsPallet;

    select wmsPallet where wmsPallet.wMSPalletId==InventDimGrid_wMSPalletId.valueStr();
    if(wmsPallet.wMSLocationId != InventDimGrid_wMSLocationId.valueStr())
    {
        warning(strFmt(“Number of pallet %1 does not match in number of location %2″,InventDimGrid_wMSPalletId.valueStr(),InventDimGrid_wMSLocationId.valueStr()));
        error(“Posting has been canceled”);
    }
    else
    {
        super();
    }
}

Multi Print Report based on Selected Items on Grid

void clicked()
{
    //AIFD_PrintBarcode
    AI_PrintBarcodeController aiBarcode = new AI_PrintBarcodeController();
    AI_PrintBarcodeContract aiContract = new AI_PrintBarcodeContract();
    ProdJournalProd prodJournalProdLoc;

    super();

    prodJournalProdLoc = ProdJournalProd_ds.getFirst(true);

    while (prodJournalProdLoc)
    {
        aiBarcode.parmReportName(ssrsReportStr(AI_PrintBarcoderReport,Report));
        aiBarcode.parmShowDialog(false);
        aiContract.parmRecordId(prodJournalProdLoc.RecId);
        aiContract.parmDocumentTitle(“x”);
        aiBarcode.parmReportContract().parmRdpContract(aiContract);
        aiBarcode.startOperation();

        prodJournalProdLoc = ProdJournalProd_ds.getNext();
    }
    //END
}

Print SSRS Barcode in AX 2012

Hello there,
just working on project that need to print somekind of barcode label for Production’s Serial Number.

you need to convert your string to Barcode string :

__________________________________________________________

public BarCodeString ShowBarcode(str _text)
{
  Barcode barcode;

  barcode = Barcode::construct(BarcodeType::Code128);
  barcode.string(true, _text);
  barcode.encode();

  return barcode.barcodeStr();
}

_________________________________________________________

then do this on Visual Studio 😀

Untitled

 

result :

Untitled

Happy DAXing !!