Pages

Saturday, November 16, 2013

AX Retail 2012 R2: Query product assortment in POS


An assortment is a collection of retail categories and, or products that are allocated to stores.  That means each stores can have different products.


Set up the assortment, process assortment and run product job

Do the following steps respectively: -

  • Set up: Retail > Common > Assortments
  • Process assortment: Retail > Periodic > Process assortments
  • Run Job: Retail > Periodic > Data distribution > Distribution schedule > run job "Product, prices, and bar codes"

Query assorted items in POS

In Store DB, the assortments makes us not able to query items directly from inventory table.  There are 3 views for getting assorted items (search items of each store by STORERECIDDATAAREAID): -

  • ASSORTEDINVENTITEMS results all assorted items including Product / Product master subtype.  

  • ASSORTEDINVENTDIMCOMBINATION results assorted items for only Product master subtype which consists of product variants.  

  • VARIANTSVIEW selects view ASSORTEDINVENTDIMCOMBINATION and show all inventory dimension details.  

Query assorted items for both Product/Product Master

Reference to my previous blog; AX Retail 2012 R2: Create New POS Form with Editable Quantity. I had to show items with both Product and Product Master subtype together with theirs inventory dimension details.  So, the view VARIANTSVIEW cannot serve what I need.  

So, I create a new view similar to ASSORTEDINVENTDIMCOMBINATION but union with items with no variant by set its InventDimId as "AllBlank".

ALTER VIEW [dbo].[PKAASSORTEDINVENTDIMCOMBINATION]
AS
(
SELECT RCT.RECID AS STORERECID, RAE.ITEMID, 
'AllBlank' AS INVENTDIMID, 
0 AS DISTINCTPRODUCTVARIANT, 
''AS RETAILVARIANTID, IVT.DATAAREAID
FROM            dbo.RETAILASSORTMENTEXPLODED AS RAE INNER JOIN
                         dbo.RETAILCHANNELTABLE AS RCT ON RAE.OMOPERATINGUNITID = RCT.OMOPERATINGUNITID
                         INNER JOIN
                         dbo.INVENTTABLE AS IVT ON RAE.ITEMID = IVT.ITEMID 
AND RAE.CHANNELDATAAREAID = IVT.DATAAREAID
WHERE (RAE.VALIDFROM <= GETUTCDATE()) AND (RAE.VALIDTO >= GETUTCDATE()) 
AND NOT EXISTS 
(SELECT 1 FROM dbo.INVENTDIMCOMBINATION AS IDC
WHERE RAE.ITEMID = IDC.ITEMID AND RAE.CHANNELDATAAREAID = IDC.DATAAREAID)
)
UNION 
(
SELECT STORERECID, ITEMID, INVENTDIMID, DISTINCTPRODUCTVARIANT, RETAILVARIANTID, DATAAREAID 
FROM dbo.ASSORTEDINVENTDIMCOMBINATION AIDC

)

And then, create a new view similar to VARIANTSVIEW but selects above view.  "AllBlank" will help to get blank dimension details itself.  

Thursday, November 14, 2013

Restore AX database in different domain


For the company which doesn't have AX development server, most of functional consultant colleagues have to prepare data migration in their VM Server.  And then, we will restore DB in our customer's server.  Of course, it's different domain.   These are SQL scripts that I always use after restore DB.  


Get Windows SID

First of all, check SID of Windows user who has System Administrator role (means current user who first open AX client after restoring).   
  • Check all users
    • wmic useraccount get name,sid
  • Check current user
    • whoami /user


Run SQL script to change Windows user info's 

There are 5 things to be changed: -

  • Network alias (user name) for Administrator 
  • Network alias (user name) for Business Connector Proxy 
  • Network domain 
  • SID for Administrator 
  • SID for Business Connector Proxy user


DECLARE @NETWORKALIAS nvarchar(80) 
DECLARE @NETWORKALIAS_BC nvarchar(80)
DECLARE @NETWORKDOMAIN nvarchar(255)
DECLARE @SID nvarchar(124) 
DECLARE @SID_BC nvarchar(124) 

---------- *** Change varibles here *** ----------------------
SET @NETWORKALIAS = 'Administrator'
SET @NETWORKALIAS_BC = 'Administrator'
SET @NETWORKDOMAIN = 'axtest'
SET @SID = 'S-1-5-21-3067842618-1207889276-857484016-500' 
SET @SID_BC = 'S-1-5-21-3067842618-1207889276-857484016-500' 
-------------------------------------------------------------- 

update USERINFO set
NETWORKALIAS = @NETWORKALIAS,
NETWORKDOMAIN = @NETWORKDOMAIN, 
SID = @SID
where ID = 'Admin'

select * from USERINFO
where ID = 'Admin'

update SysBCProxyUserAccount set
NETWORKALIAS = @NETWORKALIAS_BC,
NETWORKDOMAIN = @NETWORKDOMAIN, 
SID = @SID_BC


select * from SysBCProxyUserAccount



Run SQL script to update batch and server sessions 


  • Batch server - If there are configurations about batch server, we better change them in one step. 
  • Server Sessions - this is needed especially when deploy SSRS reports.  If don't update server sessions, it will try to reach old sessions and not able to deploy them 


SET NOCOUNT ON 
DECLARE @AOSID NVARCHAR(20), @FROMAOSID NVARCHAR(20), @AOSACCOUNT NVARCHAR(50), 
@SERVERID NVARCHAR(50), @FROMSERVERID NVARCHAR(50) 
---------- *** Change varibles here *** ----------------------
SET @SERVERID = '01@AX2012R2TEST' 
SET @FROMSERVERID = '01@VMSERVER'
SET @AOSACCOUNT = 'axtest\administrator'
--------------------------------------------------------------

Update BatchServerGroup set SERVERID = @SERVERID where SERVERID = @FROMSERVERID

Update BATCHSERVERCONFIG set SERVERID = @SERVERID where SERVERID = @FROMSERVERID

Update BATCH set SERVERID = @SERVERID where SERVERID = @FROMSERVERID

delete from SYSSERVERCONFIG 
delete from SYSSERVERSESSIONS 
delete from SYSCLIENTSESSIONS



Wednesday, November 13, 2013

AX Retail 2012 R2: POS cannot create customer order due to voucher already exists

In first version of AX 2012 R2, there is a bug when create customer order in POS with error: 

POSApp.CustomerOrderRecovery(): The order could not be saved at this time.Voucher <<voucher number>> is already used as at date <<dd/mm/yyyy>>.
Posting has been cancelled.

When check Voucher Transaction, above <<voucher number>> already exists. But when check the Status List of related number sequence, that number appears with "Free" status.  The Next number is also higher.   

It seems like after posting a Sales Order, that voucher has been posted but not released from number sequence list (Status is still Free).





Then, I provided 2 solutions to my colleague who supports that customer: 


Create RunBaseBatch class

It's such temporary solution, if the error rarely occurs.  It will open RunBaseBatch dialog for inputting number series of payment voucher (in Demo Database is Acc_30).  Then, it will lock number sequence status from "Free" to "Active" if it has been posted.     



private void update()
{
    //NumberSequenceTable     numberSequenceTable = NumberSequenceTable::findByNaturalKey('Acco_30');
    NumberSequenceTable     numberSequenceTable = NumberSequenceTable::find(ecoResNumberSequence);
    NumberSequenceList      numberSequenceList;
    GeneralJournalEntry     generalJournalEntry;
    boolean                 updateData;
    Num                     formattednumber;



    if (numberSequenceTable.RecId)
    {
        ttsBegin;
        while select forupdate numberSequenceList
        where numberSequenceList.NumberSequenceId == numberSequenceTable.RecId &&
              numberSequenceList.Status == NumStatus::Free &&
              numberSequenceList.NextRec < numberSequenceTable.NextRec
        {
            formattednumber = numberSequenceList.formattednumber();
            select generalJournalEntry
                where generalJournalEntry.SubledgerVoucher == formattednumber;
            if (generalJournalEntry.RecId)
            {
                numberSequenceList.Status = NumStatus::Active;
                numberSequenceList.update();
                updateData = true;
            }
        }
        ttsCommit;
    }
}

Release number sequence before generate new payment voucher 

Just in case, the error occurs too often.  I will check number sequence.  If it's in use, release it first.  Customize this in table CustParameters


client server static NumberSequenceReference numRefCustPaymVoucher()
{
      CustParameters::pkaCheckNumberSequenceNextRec(extendedTypeNum(CustPaymVoucher));
      return NumberSeqReference::findReference(extendedTypeNum(CustPaymVoucher));

client server static void pkaCheckNumberSequenceNextRec(extendedTypeId _edt)
{
    NumberSequenceTable             numberSequenceTable;
    RecId                           numberSequenceId = NumberSeqReference::findReference(_edt).NumberSequenceId;
    NumberSequenceList              numberSequenceList;
    Num                             formattedNumber;
    GeneralJournalEntry             generalJournalEntry;

    numberSequenceTable = NumberSequenceTable::find(numberSequenceId);

    if (numberSequenceTable.RecId)
    {
        //active unused number sequence
        ttsBegin;

        switch (_edt)
        {
            case extendedTypeNum(CustPaymVoucher) :  //Payment voucher
                while select forupdate numberSequenceList
                      where  numberSequenceList.NumberSequenceId == numberSequenceTable.RecId &&
                             numberSequenceList.Status           == NumStatus::Free &&
                             numberSequenceList.NextRec          < numberSequenceTable.NextRec
                {
                    formattedNumber = numberSequenceList.formattednumber();
                    select generalJournalEntry
                        where generalJournalEntry.SubledgerVoucher == formattednumber;
                    if (generalJournalEntry.RecId)
                    {
                        numberSequenceList.Status       = NumStatus::Active;
                        numberSequenceList.update();
                    }
                }

                break;
        }
        ttsCommit;
    }


However, I just use one of above solutions when not able to update CU6 (can't offline all outlets for update the hotfix).  I don't see this error with R2+CU6 version.       


AX Retail 2012 R2: Create New POS Form with Editable Quantity



Last blog, I explained about how to create new Retail POS transactions.  Now, I will show creating new POS form.  

Noted:  This customization is based on AX 2012 R2 + CU6 


Example from Tender Declaration 

With the requirement to have a new POS form for entering planned order quantity, It's been long time since I was .NET developer.  And because there's very short time, the only one way is, copying features from existing form. ;-)  

So, I found out that Tender Declaration is a good one having grid and editable quantity. 



This form is in "Retail SDK\POS Plug-ins\Services\Dialog\WinFormsTouch".    

Tip: How did I find this form?  Easily, search for "TenderDeclaration" in all files of entire solution.  That's the way I always use when develop POS.    



Noted:  I just mention this for guys who just get to know Retail POS development.  Not only install Retail POS SDK from AX Setup, you need to have DevExpress Winforms license. And once you first buy, you won't see the WinForms installer for MS Dynamics AX 2012 R2 (v2011.2.111) in the Download Manager, you need to request from the DevExpress Support Center.  


  

Where should the new form be?

Since only small form, I will simply create it in BlankOperations project.  You may decide to have a new project if there're too many objects and better have its default namespace under "Microsoft.Dynamics.Retail.Pos".  

Under BlankOperations, add new item "DevExpress Form v11.2"



In behind code, set the form to inherit from "LSRetailPosis.POSProcesses.frmTouchBase", to have a blank form as Retail POS pattern. 

namespace Microsoft.Dynamics.Retail.Pos.BlankOperations.WinFormsTouch
{
    public partial class POSTestForm : LSRetailPosis.POSProcesses.frmTouchBase
    {
        .....

Before design grid, you better drag TableLayoutPanel to form first, then you can have all object's size showing as percentage.  Set the Dock properties; Header = Top, TableLayoutPanel = Fill.     



Drag XtraGrid control to TableLayoutPanel, set position in the table by Column, ColumnSpan, Row, RowSpan as you want.  


     
I prefer to design grid columns by "Run Designer".  Reference field name is case-sensitive from Grid DataSource field.  




Grid DataSource

If grid is only for viewing, Grid DataSource can be a simple DataTable (System.Data.DataTable).   But this form is different because needs to be filled the quantity.  So, we will bind Grid DataSource using ViewModel sealed class.  See example in frmTenderCount.cs --> TenderViewModel: -

    /// <summary>
    /// Model of tender row for grid to bind to
    /// </summary>
    sealed internal class TenderViewModel
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="TenderViewModel"/> class.
        /// </summary>
        public TenderViewModel()
        {
            this.Enabled = true;
        }

        /// <summary>
        /// Gets or sets the type of the tender operation.
        /// </summary>
        /// <value>
        /// The type of the tender operation.
        /// </value>
        public PosisOperations TenderOperationType
        {
            get;
            set;
        }
        ....... 

Declare variable for Grid DataSource as list of ViewModel.  Load data from DataTable to the ViewModel and add to the list.  Then, bind the ViewModel list to GridControl. 

    private System.Data.DataTable yourDataTable;
    System.Collections.Generic.List<YourViewModel> gridSource;

    private void loadData()
        {
            try
            {
                yourDataTable = <<Function to get DataTable>>
                gridSource = new System.Collections.Generic.List<YourViewModel>();

                foreach (DataRow row in yourDataTable.Rows)
                {
                    YourViewModel viewRow = new YourViewModel()
                    {
                        ITEMID = (string)row["ITEMID"],
                        ITEMNAME = (string)row["ITEMNAME"],
                        COLOR = (string)row["COLOR"],
                        CONFIG = (string)row["CONFIG"],
                        ORDEREDQTY = (decimal)row["ORDEREDQTY"]),
                        UNITID = (string)row["UNITID"]
                    };
                    gridSource.Add(viewRow); 
                }


                grResult.DataSource = gridSource;
                ...... 


Entering quantity in Grid

Because Retail POS is touchscreen based.  It's not convenience to input data using keyboard.  We need to draw Quantity cell using event handler "CustomDrawCell".  See example: -

        private void gvTenders_CustomDrawCell(object sender, DevExpress.XtraGrid.Views.Base.RowCellCustomDrawEventArgs e)
        {
            string column = e.Column.FieldName;

            if (column == COLQTY || column == COLTOTAL)
            {
                // Determine the tender type
                PosisOperations operationTenderType = gridSource[e.RowHandle].TenderOperationType;

                // Draw the calculator icon in the quantity column if the tender type is not cash or currency
                bool drawIcon = (column == COLQTY) && (operationTenderType != PosisOperations.PayCash) && (operationTenderType != PosisOperations.PayCurrency);

                e.Appearance.FillRectangle(e.Cache, e.Bounds);
                DrawButton(e.Cache, e.Bounds, gridTenders.LookAndFeel.ActiveLookAndFeel.ActiveStyle, e.Appearance, GetButtonState(e.RowHandle, column), e.DisplayText, drawIcon);

                e.Handled = true;
            }

        }

Create event handler; KeyUp, MouseDown for controlling grid column click: -

        private void gvTenders_KeyUp(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Space)
            {
                switch (gvTenders.FocusedColumn.FieldName)
                {
                    case COLQTY:
                        OnQtyButtonClick(gvTenders.FocusedRowHandle);
                        break;
                    case COLTOTAL:
                        OnTotalButtonClick(gvTenders.FocusedRowHandle);
                        break;
                    default:
                        break;
                }
            }

        }

        private void gvTenders_MouseUp(object sender, MouseEventArgs e)
        {
            if (QtyPressedRowHandle != GridControl.InvalidRowHandle)
            {
                OnQtyButtonClick(QtyPressedRowHandle);
                QtyPressedRowHandle = GridControl.InvalidRowHandle;
            }

            if (TotalPressedRowHandle != GridControl.InvalidRowHandle)
            {
                OnTotalButtonClick(TotalPressedRowHandle);
                TotalPressedRowHandle = GridControl.InvalidRowHandle;
            }
        }


Input the quantity using frmInputNumpad dialog.  Then, update inputted quantity to Grid DataSource (ViewModel).  You can also modify the dialog, e.g. Integer+Positive --> set dialog EntryTypes properties as NumpadEntryTypes.IntegerPositive. 



        private void OnTotalButtonClick(int rowHandle)
        {
            if (!gridSource[rowHandle].Enabled)
            {
                return;
            }

            using (frmInputNumpad inputDialog = new frmInputNumpad())
            {
                inputDialog.EntryTypes = NumpadEntryTypes.Price;
                inputDialog.PromptText = LSRetailPosis.ApplicationLocalizer.Language.Translate(1443);
                inputDialog.CurrencyCode = gridSource[rowHandle].Currency;

                // The input dialog should allow negative values if it is a tender declaration
                inputDialog.AllowNegativeValues = (this.transaction.TransactionType == TenderCountTransaction.TypeOfTransaction.TenderDeclaration);
                inputDialog.EntryTypes = NumpadEntryTypes.IntegerPositive;

                LSRetailPosis.POSProcesses.POSFormsManager.ShowPOSForm(inputDialog);

                if (inputDialog.DialogResult == DialogResult.OK)
                {
                    decimal subTotal;
                    if (decimal.TryParse(inputDialog.InputText, out subTotal))
                    {
                        gridSource[rowHandle].Total = subTotal;
                        UpdateTotalAmount();

                        //Reseting the data source of denomination grid since user has altered the total amount
                        denominationDataSources[rowHandle] = null;
                    }
                }
            }
        }

Save Data from ViewModel list

When save data from ViewModel List to Store DB, we will loop it to ViewModel variable using "FindAll".  
                    
          List<TenderViewModel> cashTenders = gridSource.FindAll(tender => tender.TenderOperationType == PosisOperations.PayCash);

          foreach (TenderViewModel cashCount in cashTenders)
          {
              .....

View form using Blank Operation button

Create a Blank Operation button for calling the form.  Giving Operation number as you want.      



        /// <summary>

        /// Displays an alert message according operation id passed.

        /// </summary>

        /// <param name="operationInfo"></param>

        /// <param name="posTransaction"></param>        

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1062:Validate arguments of public methods", MessageId = "0", Justification = "Grandfather")]

        public void BlankOperation(IBlankOperationInfo operationInfo, IPosTransaction posTransaction)
        {
            // This country check can be removed when customizing the BlankOperations service.
            if (Functions.CountryRegion == SupportedCountryRegion.BR || Functions.CountryRegion == SupportedCountryRegion.HU)
            {
                return;
            }


            switch ((operationInfo.OperationId).ToUpperInvariant().Replace(" ", string.Empty))
            {
                #region Planned Order
                case "CREATEORDER":  //Create Planned Order
                    CreateOrder(operationInfo, true, Application);
                    break;
                default:
                    //default, just echo the operation number and parameter value 
                    break;
            }
        }

        private void CreateOrder(IBlankOperationInfo operationInfo, bool allowBlank, IApplication application)
        {
            using (WinFormsTouch.POSFormTest searchDialog = new WinFormsTouch.POSFormTest())
            {
                bool inputValid;
                do
                {
                    inputValid = true;

                    searchDialog.Application = application; 
                    application.ApplicationFramework.POSShowForm(searchDialog);

                    // Quit if cancel is pressed...
                    if (searchDialog.DialogResult == System.Windows.Forms.DialogResult.Cancel && allowBlank)
                    {
                        return;
                    }
                    else if (searchDialog.DialogResult == System.Windows.Forms.DialogResult.OK)
                    {
                        .....
                    }
                    else if (searchDialog.DialogResult == DialogResult.Cancel && !allowBlank)
                    {
                        inputValid = false;
                    }
                } while (!inputValid);
            }
        }

After compile BlankOperations.dll and replace it in Retail POS services directory (C:\Program Files (x86)\Microsoft Dynamics AX\60\Retail POS\Services).  Run Retail POS and click the new Blank Operation button.  You will see the new form with grid and editable quantity. 

  

Tuesday, November 12, 2013

AX Retail 2012 R2: Create New POS Transactions



When I got requirement to create another kind of transactions in Retail POS, e.g. planned order, and those data have to be pulled to Retail HQ for following processes. 

RetailTransactionTable is a good example to start.  These are some steps how I create a transaction table to support pulled data from Retail POS.  


Create transaction table in Retail HQ

The new table consists of Primary index with fields: Store, Terminal, Channel and TransactionId 



There are 3 fields needed for replication: Origin, Replicated, ReplicationCounterFromOrigin. 



In the transaction table, copy 2 methods from RetailTransactionTable: maxReplicationCounterFromOrigin and setMaxReplicationCounter.  Change variables matching the new and temporary tables. 



Create transaction table in Retail POS Store DB

I simply generate create table SQL script from AX database.  If transaction table has RecId field/index, delete it.  Rename ReplicationCounterFromOrigin to ReplicationCounter with IDENTITY increment.  Run the script in Store DB.       


Configure Retail Channel Schema

In Retail Channel Schema, read schema from Store DB to get new table and fields.      




Open Scheduler subjobs, search for RetailTransactionTable.  
  • "Copy scheduler subjob" to create a new subjob.  Change tables to new transaction.  Temporary table is TableNameX.  
  • "Transfer field list" to map transferred fields (Functions > Match fields).  Delete these fields (if there is); Origin, CreatedBy, CreatedDateTime, ModifiedBy, ModifiedDateTime, Partition, RecVersion.  Add new field to match ReplicationCounter and ReplicationCounterFromOrigin
  • "Create staging table" to generate the temporary table (TempDB type). 




In Scheduler Job, add the new subjob to P-Job.   




Run P-Job

Finally, in Distribution Schedule, run the P-Job.  The new table with Replication counter will show up.  




This, I only explain about transaction tables.  Next times, I will show about creating a simple transaction form in Retail POS.