Using TCA APIs to Create Customer Information

Share

Oracle has provided TCA APIs, written in PL/SQL Procedures/Packages, that can be put to very good use for any Application that interfaces with Oracle Ebusiness Suite. We just need to be careful in the order in which these APIs need to be called, and the parameters that need to be passed for successful creation of customer related data.

Briefly, here is how we can call the APIs

– First, Creation the Location Record

p_location_rec.country := p_country;
p_location_rec.address1 := p_addr1;
p_location_rec.address2 := p_addr2;
p_location_rec.city := p_city;
p_location_rec.county := v_county;
p_location_rec.postal_code := p_zip;
p_location_rec.state := p_state;
p_location_rec.created_by_module := ‘ISTORE’;

hz_location_v2pub.create_location(
‘T’,
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data);

– If the location record has been successfully created, Create the Party Site

p_party_site_rec.party_id := x_party_id; –<
p_party_site_rec.location_id := x_location_id; –<
p_party_site_rec.identifying_address_flag := ‘Y’;
p_party_site_rec.created_by_module := ‘ISTORE’;

hz_party_site_v2pub.create_party_site(
‘T’,
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data);

– If the Party Site record has been successfully created, Create the Acct Site

p_cust_acct_site_rec.cust_account_id := p_ship_to_cust_acct_id; –<
p_cust_acct_site_rec.party_site_id := x_party_site_id; –<
p_cust_acct_site_rec.language := ‘US’;
p_cust_acct_site_rec.created_by_module := ‘ISTORE’;

hz_cust_account_site_v2pub.create_cust_acct_site(
‘T’,
p_cust_acct_site_rec,
x_cust_acct_site_id,
x_return_status,
x_msg_count,
x_msg_data);

– If the Party Site record has been successfully created, Ship To info

p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id; –<
p_cust_site_use_rec.site_use_code := ‘SHIP_TO’;
p_cust_site_use_rec.created_by_module := ‘ISTORE’;
p_cust_site_use_rec.primary_flag := p_primary_flag;

hz_cust_account_site_v2pub.create_cust_site_use(
‘T’,
p_cust_site_use_rec,
p_customer_profile_rec,
”,
”,
x_site_use_id,
x_return_status,
x_msg_count,
x_msg_data);

Additionally, for the same address one can create the Bill To Address, by passing ‘BILL_TO’ as the site_use_code to the above API.

Request for support by sending an email to support@ibizsoftinc.com

Oracle ERP Solutions on the iPhone

Oracle ERP Solutions for ERP implementors enables sales, operational, service executives to query on the Order Status, Item Status and other such information within the Oracle E-Business Suite. Oracle ERP Solutions for iPhone features a highly intuitive user interface for ‘mobile’ executives allowing to browse, search and provide the right status information of an Order, Item and other such information.

The Oracle ERP Solutions application requires only the username, password, hostname and port number of the server hosting the Oracle E-Business Suite to login. The screenshot below illustrates this screen:

If the credentials entered by the user shown in the screenshot above are accurate, then the user can query the Order Status or the Item Status using this application.A screenshot that shows the Order Status screen is shown below:

On entering a valid Order Number and the Order Date, the ERP Solutions application will whether the Order is ‘In Process’ or if it has been ‘Closed’. Similarly Item Status allows us to query the status of an Item from the Inventory. The screenshot pertaining to this functionality is shown below:

The ERP Solutions application on the iPhone will allow executives to check Order Status and/or Item Status on-the-fly using their mobile phones and will avoid the need to log on their laptop/desktop computers and run programs/queries in order to get the latest status on an Order or an Item.

Oracle R12.1.1 installation

Installation of Oracle Applications – R12.1.1 for Windows

Step – 1 PrerequisiteOperating System

• Windows 2003 Server

Software

• Oracle Apps R12.1.1 installation CD or files downloaded from Oracle edelivery
• Cygwin
• Microsoft Visual Studio 2008
• stopapps.bat and stopdb.bat files

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.