Automating Pricing Agreement in iStore

I came across a unique requirement when we were implementing iStore for GE Security. iStore has a gap, wherein, the Pricing Agreement(s) related to customer accounts need to be applied manually, unlike Order Management where the Agreements are picked up automatically. It was challenge for us, at iBizSoft, to provide a similiar, if not same, kind of functionality for GE Secutity B2B customers.

We had a good look of how Pricing Agreements worked in Order Management and realized that for the same functionality to work in iStore, we needed to make sure that the same item(s), to which agreement is applied on, existed in the Price List that’s associated to or picked by iStore (using the getBestListPrice java api, that uses the inherent pricing engine). Secondly, we definitely knew that this involved customization the cart related page, since the price should get adjusted when the item is added to the cart.

This involved a change to the ibeCScpAddItem.jsp. Here is a snippet of the code that we added

if(cartId != null && !(cartId ==”") )
{
String cart_sql = “”;
String agree_sql = “”;
String v_item_id = “”;
String v_quote_line_id = “”;

BigDecimal v_accountId = RequestCtx.getAccountId();

cart_sql = ” select inventory_item_id, quote_line_id ” ;
cart_sql = cart_sql + ” from aso_quote_lines_all where quote_header_id = ‘” + cartId + “‘ “;

ResultSet rscart = null;
ResultSet rsagree = null;
ResultSet rsagree_2 = null;
java.sql.Statement stmt=null;
java.sql.Statement stmt_agree=null;
java.sql.Statement stmt_agree_2=null;

oracle.jdbc.driver.OracleConnection con = null;

con = (OracleConnection)TransactionScope.getConnection();

// Create a Statement object
stmt = con.createStatement();
stmt_agree = con.createStatement();
stmt_agree_2 = con.createStatement();

rscart = stmt.executeQuery(cart_sql);

//For every item in the Cart

while (rscart.next())
{

boolean reprice = true;

ShoppingCart SC = new ShoppingCart(cartId,cartDate);
ShoppingCartItem []SCIs = new ShoppingCartItem[1];
ShoppingCartItem currItem = new ShoppingCartItem();

v_item_id = rscart.getString(“inventory_item_id”);
v_quote_line_id = rscart.getString(“quote_line_id”);

currItem.setCartLineId(v_quote_line_id);

// Query for the Agreement ID associated to the Price List that has the Item added to the Cart

agree_sql = “SELECT agreement_id, a.price_list_id”;
agree_sql = agree_sql + ” FROM oe_agreements_vl a, qp_list_lines_v c”;
agree_sql = agree_sql + ” where a.sold_to_org_id = ” + v_accountId + ” “;
agree_sql = agree_sql + ” AND SYSDATE BETWEEN NVL(a.START_DATE_ACTIVE,SYSDATE-1) AND NVL(a.end_DATE_ACTIVE ,SYSDATE+1) and ” ;
agree_sql = agree_sql + ” product_attr_value = ‘” + v_item_id + “‘ “;
agree_sql = agree_sql + ” and list_header_id = a.price_list_id “;

rsagree = stmt_agree.executeQuery(agree_sql);

boolean hasagreement=false;

// Associate the AgreementId to the Item in the Cart

if (rsagree.next())
{
currItem.setAgreementId(rsagree.getString(“agreement_id”));
hasagreement=true;
}

// This query is used if there are Related Accounts

else
{
agree_sql = ” SELECT agreement_id FROM OE_AGREEMENTS_V a, qp_list_lines_v b “;
agree_sql = agree_sql + ” where sold_to_org_id in (select cust_account_id “;
//agree_sql = agree_sql + ” where sold_to_org_id = (select cust_account_id “;
agree_sql = agree_sql + ” from hz_cust_acct_relate_all where related_cust_account_id= ” + v_accountId + ” ) “;
agree_sql = agree_sql + ” AND NVL(a.END_DATE_ACTIVE,SYSDATE+1) > SYSDATE and product_attr_value = ‘” + v_item_id + “‘ and list_header_id = a.price_list_id “;

rsagree_2 = stmt_agree_2.executeQuery(agree_sql);

if (rsagree_2.next())
{
currItem.setAgreementId(rsagree_2.getString(“agreement_id”));
hasagreement=true;
}
}

SCIs[0] = currItem;
SC.setShoppingCartItems(SCIs);
Object txnLock = new Object();
boolean commitFlag = false;

// Reprice the Cart if there is an Agreement

if(hasagreement)
{
try
{
TransactionScope.begin(txnLock);
SC.updateAgreements(reprice);
commitFlag = true;

}
catch (FrameworkException e)
{
String errorMsg = IBEUtil.getMessages(e);
pageContext.setAttribute(“errMsg”, mm.getMessage(“IBE”, “IBE_AGRMT_ERR_SAVEPROB”) + ” : ” + errorMsg, PageContext.REQUEST_SCOPE);
}
finally
{
if (!commitFlag) TransactionScope.setRollbackOnly();
TransactionScope.end(txnLock);
}
}
} // end of while in aso_quote

TransactionScope.releaseConnection(con);
} // end of if cartId != null

// This is where the Code for Automating Agreement ENDs