Wednesday, July 31, 2013

Keys table EJB | script to correct counter values

When you do migration from one version of commerce to the next or any other scenarios or due to data load scenarios some times the keys table counters are messed and causes lot of errors in application server logs when using those corresponding EJB's.

A lot of discretion is warranted when doing stuff to the keys table e.g. counter should be greater than the lower bound and lesser than the upper bound
You can generate the SQL statements from the script below and run them against the corresponding commerce schema where the issue was found.

SQL Script to see which tables might need to be fixed:

select 'select max(' || columnname||')+1 - (select counter from keys where tablename = '''|| tablename||'''), '''|| tablename || ''' from ' || tablename from keys

 SQL script to do the actual updates:
select concat(concat(concat('update keys set counter=',concat(concat(
concat(concat('(select max(',columnname),')+1 from ' ),tablename),') where tablename=''')),tablename),''' and counter <= '|| '(select max('||columnname||') from '|| tablename||')' ||';') from keys

Monday, July 29, 2013

Abstract Task command multi store esites model

This is my 100th blog and am happy to share my leanings. Please do leave a comment, if you have any ideas or questions or you like reading any specific areas.
 It is very important to understand the classic java abstract classes and methods and it is very useful when implementing task commands in multiple e-site scenario's, specially for tax/shipping/environment fee and other country specific, site specific entities.

Command Reg entries:  Making sure common command is registered to store 0 so all stores can pick up that command and store specific customization, can be implemented and registered for specific store e.g. 10151 below.

            interfacename="com.custom.commerce.order.commands.CustomCalculateTaxTaskCmd"
        classname="com.custom.commerce.order.commands.CustomUSCalculateTaxTaskCmdTaskCmdImpl"
        description="US store implementation of tax command" />
   
            interfacename="com.custom.commerce.order.commands.CustomCalculateTaxTaskCmd"
        classname="com.custom.commerce.order.commands.CustomNonUSCalculateTaxTaskCmdTaskCmdImpl"
        description="Non US store implementation of tax command" />



Creating abstract Task command with abstract methods:

public interface CustomCalculateTaxTaskCmd extends TaskCommand {
    public void seTaxId(String taxId);
}
public abstract class CustomCalculateTaxTaskCmdTaskCmdImplextends TaskCommandImpl
        implements CustomCalculateTaxTaskCmd{
                protected String taxId;
                public void setTaxId(taxId) {
                       this.taxId=taxId;
                }
                public void performExecute() throws ECException {
                            super.performExecute();
                            initialize();
                            customFilter();
                }
               private void initialize() throws Exception {
                //Implement common initialization of access beans and any common params
               }
               protected void  filter1() {
                     //custom filter
               }
               protected void  filter2() {
                     //custom filter code
               }
.....
               protected void  filter5() {
                     //custom filter code
               }
              protected abstract void customFilter() throws Exception;
              
}
//Custom implementation extending abstract command where the abstract method customFilter() is implemented and filter1 and filter2 methods are implemented in the abstract command
public CustomUSCalculateTaxTaskCmdTaskCmdImpl extends CustomCalculateTaxTaskCmdTaskCmdImplextends {
                public void performExecute() throws ECException {
                           super.performExecute();
                }
                protected void customFilter() throws Exception {
                           filter1();
                           filter2();
                }
}
//Custom implementation extending abstract command, where the abstract method customFilter() is implemented and filter1() and filter2() methods are implemented in the abstract command
public CustomNonUSCalculateTaxTaskCmdTaskCmdImpl extends CustomCalculateTaxTaskCmdTaskCmdImpl {
                public void performExecute() throws ECException {
                           super.performExecute();
                }
                protected void customFilter() throws Exception {
                           filter1();
                           filter5();
                }
}

Saturday, July 20, 2013

ATP inventory concepts | Order Management System!!


This blog does not give any coding example but folks working on WCS implementations, who work on Inventory and ATP and It is one of the most important concepts of an eCommerce site. Managing supply and demand and making sure the inventory is correctly updated on order placement is very important for running any business.

Important Inventory and ATP terminology and concepts: These are high level concepts and I am sure back-end systems such as SAP, Oracle, Sterling order management systems use these terms and each one of them could have slight variants.

  • Supply is all the inventory available.
  • Demand is all the orders placed and not yet fulfilled.--Reservations/Allocated Orders/Future expected demand.
  • Supply Type or Inventory buckets, this is gives a clear picture of Inventory availability and it could be moved between multiple buckets such (QA hold or Intransit or) . This is very important in calculating available inventory for consumption.
  • Demand Type is the group of quantities committed across various commitment channels.
  • Demand Supply Matching: This is also called available to sell calculation, Supply with a sufficient lifespan     along with demand.
  • Simple example of Available to Sell calculation
      • (Sum of All checked supply types) – (sum of all corresponding demand type)
      • Available To Sell = (Onhand + Intransit +  Qty in PO) – (Open Order + Reserved + Allocated)
  • Availability on hand, back order, preorder (Future Inventory).
  • Managing inventory at the plant level or distribution level.
  • Available-to-promise (ATP) enables to decide the availability of a item for current and future demand. (simplest version is supply-demand is ATP)
  • Based on threshold and Inventory availability -- Availability high\low\future inventory.
  • Inventory sourcing at the plant level or pool level.
  • Order fulfillment process, there are multiple demand types -Open/Reserved/Scheduled/Allocated.
  • ATP Monitor versus Inventory Monitor.
  • ATP Monitoring  modes:
    • Activity Based: Raises an event in real time every time an item goes      above or below one of the thresholds.
    • Quick Sync: Re-sends the most recently published inventory availability information.
    • Full Sync: Monitors all of the items regardless of activity and publishes the inventory information for all of the items.


ResolveParameter class, an Important commerce tool !!

ResolveParameter is a very important class when implementing custom code in commerce and I am sure, it is a very important tool for commerce developers doing custom implementations. When a custom implementation requires to post multiple name/value pairs of the same type, this class is really useful.
e.g. URL params
catentryId_12343=value1,catentryId_22343=value2,catentryId_32343=value3
orderItemStatus_10000=value10001,orderitemsStatus_10004=value

//resolve params in request properties, returns the hashtable of name, value pairs.
public void setRequestProperties(TypedProperty reqProperties) throws ECException {
ihshCatentry_id  = ResolveParameter.resolveValues(OrderConstants.EC_CATENTRY_ID,  requestProperties, false);
}


Couple of other methods from ResolveParameter that I find useful from time to time.

/*
* returns the order item status from the hash table using the resolve parameter method.
*/
protected String getOrderItemStatus(Integer i, Hashtable hashOrderItemStatus )
  throws InvalidParameterValueException
{
String methodName = "getOrderItemStatus";
  String orderItemStatus;
  try
  {
  orderItemStatus = ResolveParameter.getString(i, hashOrderItemStatus);
  } catch (NumberFormatException ex) {
    throw new InvalidParameterValueException("orderItemId",
      ex.getMessage());
  }
  return orderItemStatus;
}    
/*
* returns the order item item id from the hash table using the resolve parameter method.
*/
protected Long getOrderItemId(Integer i, Hashtable hashOrderitemId )
  throws InvalidParameterValueException
{

  Long orderItemId;
  try
  {
    orderItemId = ResolveParameter.getLong(i, hashOrderitemId);
  } catch (NumberFormatException ex) {
    throw new InvalidParameterValueException("orderItemId",
      ex.getMessage());
  }
  return orderItemId;
}