Forgotten Password

If you trying to understand how ‘Forgotten Password’ functionality works, then this article would be greatly beneficial to you. Specially, if your customer has asked you to send out two emails to two separate email addresses. This feature is not supported by any of the Oracle Self-service products, such as iStore, iSupport etc.
Forgotten Password in iStore comprises of two JSP files, viz
ibeCAcdPwdAssist.jsp
and
ibeCAcpPwdAssist.jsp
As you can see, the only difference in the names of these files are the letters ‘d’ and ‘p’. Digressing on this a bit, Oracle iStore has maintained the naming convention of calling all ‘Display’ related pages by adding a letter ‘d’ somewhere in its long, cryptic names and a letter ‘p’ is added to indicate that its a ‘Processing’ page.
ibeCACdPwdAssist.jsp displays the Username and Email Address fields and ibeCAcpPwdAssist.jsp processes this information based on very lengthy logic, as discussed below.
Search for the following line in ibeCAcpPwdAssist.jsp
email = resetPassword(username, email)
resetPassword is a method that resides in CustomUtil.java
One can decompile the class file residing in
$JAVA_TOP/oracle/apps/ibe/customer to know more about this code
resetPassword method calls the following methods, before sending out the Password to the user (using sendPassword method)
getPhoneAndEmail ==> has the following select statement
stringbuffer.append(”select email_address, phone_area_code, phone_number, “);stringbuffer.append(”phone_extension, phone_line_type, priority_of_use_code, “);stringbuffer.append(”contact_point_type from hz_contact_points “);stringbuffer.append(”where owner_table_name = ‘HZ_PARTIES’ “);stringbuffer.append(”and owner_table_id = :1 “);stringbuffer.append(”and status = ‘A’ “);
From the above statement we can decipher that the primary email_address, where the password will be sent to, must be present in the hz_contact_points table.
If the email address returned is not the same as the email address entered by the user (on ibeCAcdPwdAssist.jsp) then an email is not sent out.
IBEUtil.getStoreSiteProfile ==> checks for the value in SIGNON_PASSWORD_LENGTH profile option
IBEUtil.generatePassword ==> Random password is generate equal to the length from the Profile option
SecurityManager.changeIdentity(s2, new CredentialImpl(s4)) ==> Use to the update the user’s password, where s2 is the username and s4 is the newly generated password
When all the above methods are successful, call the following method to send an email to the user
sendPassword
sendPassword method in turn calls
IBE_WORKFLOW_PVT.NotifyForgetLogin that triggers a notification using the iStore Alert Workflow
That’s a summary of what actually happens behind the scene. I had this opportunity to modify CustomUtil.java, to let the customer send out two emails to two different email addresses. After spending hours, and updating email addresses in various hz tables, I realized that there is something about IBE_WORKFLOW_PVT.NotifyForgetLogin that prevents us from sending an email to the alternate email address.
If you turn on the logging for iStore, I got the following errors in the IBE Java log file
14:44:0:788 [CustomerUtil.sendPassword] Exit14:44:0:788 [CustomerUtil.resetPassword] new password is sent to user via email14:44:0:788 [CustomerUtil.sendPassword] Enter14:44:0:788 [CustomerUtil.send password] MAXLENGTH : 400014:44:0:788 [CustomerUtil.sendPassword] Call IBE_WORKFLOW.NotifyForgetLogin14:44:0:796 [CustomerUtil.sendPassword] SQLException caught: ORA-20002: 3122: Duplicate item IBEALERT/FORGETLOGIN-032708154403-MFRIDRICH’ could not be created.ORA-06512: at “APPS.IBE_WORKFLOW_PVT”, line 1744ORA-06512: at line 1
14:44:0:796 [CustomerUtil.resetPassword] SQLException caught: ORA-20002: 3122: Duplicate item ‘IBEALERT/FORGETLOGIN-032708154403-MFRIDRICH’ could not be created.ORA-06512: at “APPS.IBE_WORKFLOW_PVT”, line 1744ORA-06512: at line 1
So, basically, this means that we cannot call the resetPassword twice with a different email address. They workflow process prevents us from doing this. One might thinking what is the solution to this issue. Come with your own Java mailer processing using the native java methods sending email to the local smtp host server.
This has been explained in detail in another article.

Custom JSP integrated with Ebusiness Suite

We have been, very successfully, deploying custom JSPs that has the same functionality as Oracle iStore and have been able to integrate every functionality, offered by Oracle iStore, with Oracle Ebusiness Suite. We created an merchant UI which integrates 3 modules using a single sign in feature. Even today, Oracle iStore, Oracle iSupport and Oracle iPayment cannot be managed by the same administrator. We always felt the need to do this for some of the customers who wanted all the 3 modules implemented. And that’s when we launched eStorLite, eServiceLite, ePaymentLite. With a single sign on by the end user, we can allow the user to place orders into Order Management, create/view or update Service Requests and be able to place Credit Card orders.

For merchant UI, we provided the functionality to be able to manage the catalog and manage the look and feel of the Service Request creation page. We, deployed, the facility to setup Payees and Backend Payment system that’s very similar to the Oracle Payments. Using public pl/sql apis, end users are able to create customers and orders without any issues and the store or database administrators do not have to worry about the patches disrupting the day-to-day operation. We tested these APIs on 11.5.1 through 11.5.10 and now Release 12.

With less 100 JSPs, we feel this is a very viable option for any customer who do not wish to implement Oracle iStore or Oracle iSupport incurring very high costs in implemenation and maintenance thereafter.

One can write a generic API to put an Order on Hold. Oracle has provided a public API to achieve this task. This API can be called from any application by passing some important parameters such as the Order Header Id and the User Id.
OE_ORDER_PUB.PROCESS_ORDER with the parameters shown below can be wrapped around a PL/SQL API
l_action_request_tbl(1).request_type := OE_GLOBALS.G_APPLY_HOLD;

l_action_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;

l_action_request_tbl(1).entity_id := v_header_id;

l_action_request_tbl(1).param3 := v_header_id;

l_action_request_tbl(1).param4 := ‘Hold Text’;

l_action_request_tbl(1).date_param1 := sysdate + 10;
l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
/*****************CALL THE PROCESS ORDER API*************************************/
OE_Order_PUB.Process_Order(

p_api_version_number => l_api_version_number,

p_header_rec => l_header_rec,

p_line_tbl => l_line_tbl,

p_action_request_tbl => l_action_request_tbl,

— OUT variablesx_header_rec => l_header_rec_out,

x_header_val_rec => l_header_val_rec_out,

x_header_adj_tbl => l_header_adj_tbl_out,

x_header_adj_val_tbl => l_header_adj_val_tbl_out,

x_header_price_att_tbl => l_header_price_att_tbl_out,

x_header_adj_att_tbl => l_header_adj_att_tbl_out,

x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,

x_header_scredit_tbl => l_header_scredit_tbl_out,

x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,

x_line_tbl => l_line_tbl_out,x_line_val_tbl => l_line_val_tbl_out,

x_line_adj_tbl => l_line_adj_tbl_out,

x_line_adj_val_tbl => l_line_adj_val_tbl_out,

x_line_price_att_tbl => l_line_price_att_tbl_out,

x_line_adj_att_tbl => l_line_adj_att_tbl_out,

x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,

x_line_scredit_tbl => l_line_scredit_tbl_out,

x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,

x_lot_serial_tbl => l_lot_serial_tbl_out,

x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,

x_action_request_tbl => l_action_request_tbl_out,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data);

OS Commerce integrated with Ebusiness Suite

OS Commerce is one of the many Open Source Ecommerce UI that’s available in the market. There are plenty of Open Source ERPs available as well. However, integrating an Open Source UI with Oracle Ebusiness Suite does have its own challenges and its a very exciting project that we have undertaken.

Our developers are adept in PHP programming which has been used in developing OS Commerce. At the same time it is very important to understand how Oracle Ebusiness Suite works both in terms of UI and in terms of what can be leverage from the myriad of backend APIs. OS Commerce has the merchant UI and the end user UI, just as Oracle’s iStore module that is integrated to Oracle Ebusiness Suite. From our preliminary observation we notice that it would be a big challenge to modify the merchant UI and integrate it with Oracle Inventory. The bigger challenge is in displaying the price for the end user, since a merchant could have various rules that may drive the price, which uses the complex pricing engine. However, given the right amount of man hours with the right skills who understand both sides of the coin, it can surely be achieved.

To display the item information on the end user catalog, we need to use mtl_system_items_b and for simple price display, that does not involve modifiers and/or qualifier, we just have to make use of qp_list_headers_v and qp_list_lines_v. Easier said than done. To modify the code written in PHP for OS Commerce, did involve considerable amount of man hours. We also need to set up OS Commerce and PHP engine on the same Oracle Applicaton server. More about this later.

API to Attach documents to a Service Request

Came to know from Oracle Support that there is no public API that would take care of this requirment (refer to the Note : 394811.1 on http://metalink.oracle.com) that corroborates the fact.
Here is the custom API that we developed
CREATE OR REPLACE PROCEDURE LOADFILEINTOBLOB (p_filename IN VARCHAR2, p_content_type in varchar2, p_file_format in varchar2, p_incident_id in number, p_user_id in number) IS
out_blob BLOB;

in_file BFILE := BFILENAME(’IN_FILE_LOC’, p_filename);

blob_length INTEGER;

v_fnd_lobs_s NUMBER;

v_fnd_attached_docs_s NUMBER;

v_fnd_docs_s NUMBER;
BEGIN
— Obtain the size of the blob fileDBMS_LOB.FILEOPEN(in_file, DBMS_LOB.FILE_READONLY);

blob_length:=DBMS_LOB.GETLENGTH(in_file);

DBMS_LOB.FILECLOSE(in_file);
SELECT fnd_lobs_s.nextvalINTO v_fnd_lobs_s from dual;

— FROM fnd_lobs where file_name = p_filename;
SELECT fnd_attached_documents_s.NEXTVALINTO v_fnd_attached_docs_sFROM SYS.DUAL;
SELECT fnd_documents_s.NEXTVALINTO v_fnd_docs_sFROM DUAL;
— Insert a new record into the table containing the— filename you have specified and a LOB LOCATOR.— Return the LOB LOCATOR and assign it to out_blob.INSERT INTO fnd_lobs (file_id, file_name, file_content_type, file_data, file_format)VALUES (v_fnd_lobs_s, p_filename, p_content_type, EMPTY_BLOB(), p_file_format)RETURNING file_data INTO out_blob;
INSERT INTO fnd_documents_tl(document_id, creation_date, created_by, last_update_date,last_updated_by, last_update_login, LANGUAGE, description,file_name, media_id, doc_attribute15, source_lang)VALUES (v_fnd_docs_s, SYSDATE, p_user_id, SYSDATE,p_user_id, ”, ‘US’, ”,p_filename, v_fnd_lobs_s, v_fnd_docs_s, ‘US’);
DBMS_OUTPUT.PUT_LINE(’Media Id : ‘ v_fnd_lobs_s );
INSERT INTO fnd_attached_documents(attached_document_id, document_id, creation_date, created_by,last_update_date, last_updated_by, seq_num, entity_name,pk1_value, automatically_added_flag)VALUES (v_fnd_attached_docs_s, v_fnd_docs_s, SYSDATE, p_user_id,SYSDATE, p_user_id, 10, ‘CS_INCIDENTS’,p_incident_id, ‘N’);
DBMS_OUTPUT.PUT_LINE(’Doc Id : ‘ v_fnd_attached_docs_s );
— Load the image into the database as a BLOBDBMS_LOB.OPEN(in_file, DBMS_LOB.LOB_READONLY);DBMS_LOB.OPEN(out_blob, DBMS_LOB.LOB_READWRITE);DBMS_LOB.LOADFROMFILE(out_blob, in_file, blob_length);
— Close handles to blob and fileDBMS_LOB.CLOSE(out_blob);DBMS_LOB.CLOSE(in_file);
COMMIT;
DBMS_OUTPUT.PUT_LINE(’Successfully inserted the file’);
END;

Mapping the iStore JSP files to the Templates

Oracle iStore Administrator can find out the name of the JSP file that is mapped to a template. I have always wondered when Oracle iStore was being developed why they never thought of a UI wherein we can give the JSP file name and it shows up the template name.
Here is a query that can be very useful for Oracle iStore developers.
SELECT I.ACCESS_NAME,A.FILE_NAMEFROM JTF.JTF_AMV_ATTACHMENTS A, APPS.JTF_AMV_ITEMS_VL I,IBE.IBE_DSP_LGL_PHYS_MAP M, IBE.IBE_MSITES_TL SWHERE A.ATTACHMENT_ID = M.ATTACHMENT_ID AND I.ITEM_ID = M.ITEM_ID ANDM.MSITE_ID = S.MSITE_ID and i.access_name like ‘STORE_CART%ADD%%’

or you can give the name of the file to get back the Programmatic Access name (or the Template name)
SELECT I.ACCESS_NAME,A.FILE_NAMEFROM JTF.JTF_AMV_ATTACHMENTS A, APPS.JTF_AMV_ITEMS_VL I,IBE.IBE_DSP_LGL_PHYS_MAP M, IBE.IBE_MSITES_TL SWHERE A.ATTACHMENT_ID = M.ATTACHMENT_ID AND I.ITEM_ID = M.ITEM_ID ANDM.MSITE_ID = S.MSITE_ID and a.file_name like ‘%ViewA%’

Setting up Loopback Servlet in iPayment

Loopback servlet is an out of the box servlet that Oracle provides to do some preliminary tests. Setting up the servlet is a pretty easy task. One of the tasks that implementors forget is to add the following line of code to the zone.properties file. Additionally, set up a Sample Payment System and make sure it is the Default Payment system for Credit Cards.
Any operation (Authorization or Capture) you do using LoopBack via the iPayment UI (using the Operations tab) is updated in the iby_trxn_summaries_all table and other relevant iby tables.
Let’s take a quick look at the code of LoopBackServlet
package oracle.apps.iby.bep.loop;
import javax.servlet.ServletException;
import oracle.apps.fnd.common.VersionInfo;
import oracle.apps.iby.bep.BEPAdapter;
import oracle.apps.iby.bep.BEPServlet;
// Referenced classes of package oracle.apps.iby.bep.loop:
// LoopBackAdapter
public class LoopBackServlet extends BEPServlet{
public LoopBackServlet(){
m_loopAdapter = new LoopBackAdapter();
}
protected BEPAdapter makeAdapter()throws ServletException{
return m_loopAdapter;
}
public static final String RCS_ID = “$Header: LoopBackServlet.java 115.0 2001/05/07 15:30:19 pkm ship $”;public static final boolean RCS_ID_RECORDED = VersionInfo.recordClassVersion(”$Header: LoopBackServlet.java 115.0 2001/05/07 15:30:19 pkm ship $”, “oracle.apps.iby.bep.loop”);protected LoopBackAdapter m_loopAdapter;
}
LoopBackAdapter

R12 - Oracle Suppliers

In R12 Accounts Payables, you will notice this change right away, that there is no more supplier form. The Suppliers have gone to self-service now. This is not the only change in the supplier. The suppliers objects have moved from AP product to TCA (Trading Community Architecture) DataModel. Due to this, even the underlying tables have changed. Supplier information is no more stored in PO_VENDORS Table now.

3 new tables have been introduced.

• AP_SUPPLIERS
• AP_SUPPLIER_SITES_ALL
• AP_SUPPLIER_CONTACTS

But don’t panic as your customizations can still work as there are views created with names of PO_VENDORS, PO_VENDOR_SITES_ALL and PO_VENDOR_CONTACTS for backward compatibility.Being a part of the TCA, these tables are closely linked to the hz tables. Here is the list of few imp HZ Tables that are affected when a new supplier is added.

HZ_PARTIES - master table along with AP_SUPPLIERS instead of PO_VENDORSHZ_PARTY_SITES - master table for supplier sites along with AP_SUPPLIER_SITES_ALL instead of PO_VENDORS_SITES_ALLHZ_LOCATIONS - contains the party sites informationHZ_PARTY_USG_ASSIGNMENTS - stores party usagesHZ_ORGANIZATION_PROFILES - Captures additional Supplier information, e.g. credit scoring details of Supplier orthe Number of Employees working in Supplier OrganizationIBY_EXTERNAL_PAYEES_ALL - Captures Payment related details of the Supplier.POS_SUPPLIER_MAPPINGS - This table holds the mapping between the AP_SUPPLIERS.VENDOR_ID and HZ_PARTIES.PARTY_ID.This is useful in cases whereby two vendors effectively belong the same HZ_Party Record.

Functionally also, creating the supplier is different from 11i. Here are the steps to create a new supplier.

1. Hit the CREATE SUPPLIER button
2. Enter a unique supplier name (Organization Name) along with other optional other information like Alias, Tax Registration Number, D-U-N-S number.
3. If the Supplier Number Entry option in the Payables System Setup window is set to Automatic, Payables automatically enters a Supplier Number for you. If this option is set to Manual, you must enter a unique Supplier Number.
4. Click Apply. The system creates the supplier record and accesses the Suppliers: Quick Update page.
5. To create Supplier Sites, you will have to create the locations for that supplier. For that, click on the “Address Book” Button.
6. On the Address Book page, click the Create button.
7. Fill all the address details and address purpose. i.e. Purchasing, Payment or RFQ Only.
8. You can add as many locations as you wish.
9. When created, you can manage the addresses for other information.

10. The system only displays sites that are in your MOAC profile
11. The address status indicates whether the supplier has provided any updates for the address. Using iSupplier Portal’s Supplier Profile Management tools, suppliers can enter address book information online, creating any number of new addresses, modifying the details for existing addresses, and indicating how each address is used.
12. Suppliers can also inactivate addresses that are obsolete. Buyer administrators need to approve any changes in order to update the master supplier details.
13. Address statuses include:
• New. A supplier has created a new address.
• Change Pending. A supplier has changed the address details. Click the Update icon to review the changes that have been made. The page displays the original address details and the changes, indicated by a blue dot. Buyer administrators can approve or make additional modifications to the changes before approving or rejecting the change. If the supplier has indicated that the address should be removed, there is a status change from Active to Inactive.
• Current. There are no pending updates for the address.

14. Since suppliers are stored in TCA, the address details for the supplier may be used by other Oracle products so be careful if removing supplier addresses. If the address is inactivated, the system no longer associates it to any contacts, and any bank account assignments to the address are inactivated. Methods to inactivate addresses include:
• You can click the Remove button on the Address Book page. This sets the address status to inactive and sets the Inactive Date for every site that is associated with the address in all operating units to today’s date.
• You can update the address and set its status to Inactive. This changes the address status and does not inactivate any of the sites that are using the address.
• You can use the Manage Sites page to manually update the Inactive Date for each site.

How to apply Agreements in iStore

Agreements can be applied at the line level only after the item is added to the Cart, that’s the standard functionality in iStore. Additionally the end user will need to the Agreement code, just as they need to know the Promotion Code to apply it on the Order. This is one of the drawback one of our esteemed client failed to comprehend.
To workaround this gap, we added the following query to ibeCScpAddItem.jsp
SELECT agreement_id, a.price_list_id FROM oe_agreements_vl a, qp_list_lines_v c where a.sold_to_org_id = ” + v_accountId + ” AND SYSDATE BETWEEN NVL(a.START_DATE_ACTIVE,SYSDATE-1) AND NVL(a.end_DATE_ACTIVE ,SYSDATE+1) and product_attr_value = ‘” + v_item_id + “‘ and list_header_id = a.price_list_id ;
And called the following method to apply the agreementId to the active cart
currItem.setAgreementId(rsagree.getString(”agreement_id”));
One of the drawbacks that the client had to accept with this workaround was the fact the item had to be present in the PriceList associated to the store, it cannot merely be part of an agreement.

Associated an iStore User to an existing Account Number

If you are looking to link an iStore user to an existing Account number, you may wonder what is the big deal about it - entering the Party Id (which is equivalent to customer_id in the fnd_user table) in the Security –> Users form should suffice. If only that was the case.
There is one key table that requires to be populated and I reluctantly mention it here, that a simple insert into this table will do the magic for you.
CustAcctRole.assignAccount(conn, partyId, accountId, “A”);
Email us at support@ibizsoftinc.com to know more on how associate a user to an already existing account number.

Cancelling an Order from with in iStore

One of our customer was not convinced with the idea of Oracle’s out-of-the-box functionality that High Risk credit card orders or Authorizatoin Failed Ordres are put on Hold by Order Management. And this is done by the Credit Card Authorization API (technically iby_payment_adapter_pub a public API) which is called from with OE_ORDER_PUB API.
We came up with a solution for this customer where in we cancel the order completely from with in iStore if we do not get an Auth code for the Order. This was done by writting a PL/SQL wrapper around Oralce’s standard API, OE_Order_PUB.Process_Order.
Passing the following should do the trick
l_header_rec.cancelled_flag:=’Y';l_header_rec.header_id:=p_header_id;l_header_rec.flow_status_code= ‘CANCELLED’;l_header_rec.change_reason = ‘Cancel Order APPCON’;l_header_rec.operation:=OE_GLOBALS.G_OPR_UPDATE;
l_action_request_tbl(1).request_type := OE_GLOBALS.G_CANCEL_ORDER;l_action_request_tbl(1).entity_code := OE_GLOBALS.G_ENTITY_HEADER;