Oracle MSCA- Mobile Supply Chain Applications

Please refer to the link below:


http://www.applikast.net/technical/msca/oracle-msca--mobile-supply-chain-applications

Oracle Applciations - Recommended Java Decompiler

https://sites.google.com/a/applikast.net/applikast/technical/tools/oracle-applciations---recommended-java-decompiler

XML Model Report - Oracle configurator

Please refer to the link below for the latest content:

http://www.applikast.net/technical/tools/xml-model-report---oracle-configurator

################################################################################
################################################################################
Developers who have worked on Oracle Configurator have always realised that there has been a requirement to understand the changes between existing repository models and the published version of those models in Oracle configurator.

There have unfortunately been no tools to identify such changes and to highlight them.

We had once hit a situation where our repository model was completely out of sync(corrupted) when compared to a published model. We needed to understand the exact changes(it would have been helpful to visually see the differences but unfortunately there was no mechanism to do so).

Desperation led to an invention and I ended up modifying the existing Model Report that Oracle provides.

This can be used to compare differences between two different models. These models could be both published models/repository model-published model/same model on two different instances(UAT and Prod).

ModelReport

Why did we modify the Model Report?

• There was no existing tool to check changes that were made to a Model.

Oracle e-business Code Migration Tool

Oracle e-business Code Migration tool :

For the latest content please refer to :

http://www.applikast.net/technical/tools/oracle-e-business-customization-installation-tool

Updating Supplier Name on Supplier

I recently was faced with an issue where I had to update the Supplier name & Alternate Supplier Name for a Supplier in Oracle Payables.

After wasting a lot of time and running thru thousands of lines of Oracle API code I figured out that the so called Supplier Public API AP_VENDOR_PUB_PKG.UPDATE_VENDOR DOES NOT UPDATE SUPPLIER NAME.

But I found that this API does update other attributes for a Supplier.

DECLARE

l_vendor_id NUMBER;

l_msg_count NUMBER;

l_msg_data VARCHAR2(4000);

l_return_status VARCHAR2(10);

l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;

BEGIN

FND_GLOBAL.apps_initialize(1198,20639,200);

l_vendor_id:= 99999;

l_vendor_rec.vendor_name:='COOL DUDE SUPPLIER';

l_vendor_rec.segment1:='1252';

AP_VENDOR_PUB_PKG.UPDATE_VENDOR(p_api_version =>'1.0',

p_init_msg_list => FND_API.G_TRUE ,

p_commit => FND_API.G_TRUE,

p_validation_level =>FND_API.G_VALID_LEVEL_FULL,

x_return_status =>l_return_status,

x_msg_count =>l_msg_count,

x_msg_data =>l_msg_data,

p_vendor_rec => l_vendor_rec,

p_vendor_id => l_vendor_id

);

FOR I IN 1..l_msg_count

LOOP

l_msg_data := SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);

dbms_output.put_line(l_msg_data);

END LOOP ;

end;

Now after struggling very hard I found that one does have an API to update Supplier Name: HZ_PARTY_V2PUB.update_organization. All you need is the PartyID of the Supplier.

Another interesting thing is to update the Supplier Alternate Name I had to pass that value to the

organization_name_phonetic field in the .

DECLARE

L_ORGANIZATION_REC hz_party_v2pub.organization_rec_type;

x_profile_id NUMBER;

l_vendor_id NUMBER;

l_party_id NUMBER;

l_object_version_number NUMBER;

l_msg_count NUMBER;

l_msg_data VARCHAR2(4000);

l_return_status VARCHAR2(10);

begin

SELECT aps.PARTY_ID, hzp.object_version_number

into l_party_id, l_object_version_number

FROM AP_SUPPLIERS aps, HZ_PARTIES hzp

WHERE

vendor_id =

l_vendor_id,

AND aps.party_id = hzp.party_id;

l_organization_rec.party_rec.party_id := l_party_id;

l_organization_rec.organization_name := l_suplier_name_tbl(i);

l_organization_rec.organization_name_phonetic := l_alt_name(i);

HZ_PARTY_V2PUB.update_organization (

p_init_msg_list => fnd_api.g_true,

p_organization_rec => l_organization_rec,

p_party_object_version_number => l_object_version_number,

x_profile_id => x_profile_id,

x_return_status => l_return_status,

x_msg_count => l_msg_count,

x_msg_data => l_msg_data);

FOR I IN 1..l_msg_count

LOOP

l_msg_data := l_msg_data||SUBSTR(FND_MSG_PUB.GET(p_encoded=>'T'),1,255);

dbms_output.put_line(l_msg_data);

END LOOP ;

exception

when others then

raise;

end;

NLS_LENGTH_SEMANTICS -BYTE/CHAR

Have you ever run into a problem when you are trying to insert data with special characters blowing up with ORA-12899: value too large for column.

The most surprising issue about this error is that encounter it when you might be trying to insert a string of length 25 into a table column of VARCHAR2(25) which should work like magic.

I will explain with an example:

Create a database Table abc_t.

CREATE TABLE abc_t (name_my VARCHAR2(25 BYTE));

Try inserting the 25 character value to abc_t.

INSERT INTO abc_t VALUES ('Höhenkirchen-Siegertsbrun');

This will blow up as though the length for the above string is 25 and column size is 25. Its because the byte size of the string above is 26 characters. The character causing the problem is ö .

Now one way to solve the problem would be to increase the size of the column to VARCHAR2(26). This is not a good fix.

The best solution to the problem is to define the table column

Create a database Table abc_t1.

CREATE TABLE abc_t (name_my VARCHAR2(25 CHAR));

Try inserting the 25 character value to abc_t.

INSERT INTO abc_t VALUES ('Höhenkirchen-Siegertsbrun');

This will work.

Now whats the difference. The difference is the way we defined the table VARCHAR2(25 BYTE/CHAR).

Now rather than taking care of this everytime when you define a database table you can set a Database Parameter called NLS_LENGTH_SEMANTICS.

You can query the value of this DB parameter using this query.

SELECT * FROM v$parameter WHERE name = Lower('NLS_LENGTH_SEMANTICS')

This value by default is set to BYTE so when you define a table column with VARCHAR2(25) it translates into VARCHAR2(25 BYTE).

But if you set this option to CHAR when you define a table column with VARCHAR2(25) it translates into VARCHAR2(25 CHAR).

Recommendation/Conclusion

For a System with Oracle Database where user will be keying in multilingual characters like French/Greek/German Characters one should set the NLS_LENGTH_SEMANTICS parameter to CHAR.

Free Code Migration Tool for Oracle Applications Release 12

Yes you read it correct. A free code migration tool for Oracle Applications Entity. Coming soon.

http://www.applikast.net/technical/tools/oracle-e-business-customization-installation-tool

Oracle OPM: Updating Batch Material Line based on the source Formula Line

I have seen quite a few times that we have a requirement of copying the DFF attributes on the Formula Line to the Material Line when the Batch containing this Material Line gets created.

One way is to create a trigger on the base table of Material Line and implement the necessary logic on ON-INSERT trigger. I personally hate triggers.

One neater way of doing it is to subscribe to Batch Created (oracle.apps.gme.batch.created).

Steps to create the Subscription for the event and execute the

DFF

copying logic thru a plsql function

Ø Go to the Workflow Administrator Webapplications -> Administrator Workflow.

Ø Open the Business Events page.

Ø Search for business event “oracle.apps.gme.batch.created”.

Ø Click on the subscription button. It displays the subscriptions page.

Ø Click on the Create Subscription button.

Ø Specify the following values for the fields in the page.

Subscriber Section

System:

Select

Triggering Event Section

Leave the Section as it is.

Execution Condition Section

Phase: 80

Status: Enabled.

Rule Data: Key

Action type Section

Action type: Custom

On Error: Skip to Next

Ø Click on Next Button:

Action Section

PL/SQL Rule Function: XX_UPDATE_MTL_LINE_DFF_PKG.update_mtl_line_dff_prc Priority: Normal

Documentation Section

Owner Name: Custom Application.

Documentation: XX.

Click on Apply Button.

The custom plsql procedure to copy the DFF will leverage the std Oracle Public API

GME_API_PUB.update_material_line

The sample plsql code below explains how the plsql procedure is hooked on with the plsql procedure via the subscription to the Business Event.

FUNCTION update_mtl_line_dff_prc( p_subscription_guid_in IN RAW

,p_event_inout IN OUT NOCOPY WF_EVENT_T) RETURN VARCHAR2

IS

TYPE l_tbl_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

l_material_detail_id_tbl l_tbl_num_type;

l_batch_header_rec gme_batch_header%ROWTYPE;

l_material_detail_rec gme_material_details%ROWTYPE;

x_material_detail_rec gme_material_details%ROWTYPE;

temp_material_detail_rec gme_material_details%ROWTYPE;

l_formula_orig_line_no FM_MATL_DTL.ATTRIBUTE1%TYPE;

l_batch_id gme_batch_header.batch_id%TYPE;

l_event_name_txt VARCHAR2 (2000);

l_event_key_num VARCHAR2 (2000);

x_message_count NUMBER := 0;

x_message_list VARCHAR2(4000) := NULL;

x_return_status VARCHAR2(1) := 'U';

l_msg_index_out NUMBER := 0;

MTL_LINE_EXC EXCEPTION;

BEGIN

l_event_name_txt := p_event_inout.geteventname ();

l_event_key_num := p_event_inout.geteventkey ();

l_batch_id := to_number(l_event_key_num);

SELECT *

INTO l_batch_header_rec

FROM gme_batch_header

WHERE batch_id = l_batch_id;

SELECT material_detail_id

BULK COLLECT INTO l_material_detail_id_tbl

FROM gme_material_details

WHERE batch_id = l_batch_id

AND LINE_TYPE = -1;

FOR I IN 1..l_material_detail_id_tbl.COUNT LOOP

l_material_detail_rec := temp_material_detail_rec;

SELECT FMD.ATTRIBUTE1

INTO l_formula_orig_line_no

FROM FM_MATL_DTL FMD

, GME_MATERIAL_DETAILS GMD

WHERE FMD.FORMULALINE_ID = GMD.FORMULALINE_ID

AND GMD.MATERIAL_DETAIL_ID = l_material_detail_id_tbl(I);

l_material_detail_rec.material_detail_id := l_material_detail_id_tbl(I);

l_material_detail_rec.attribute1 := l_formula_orig_line_no;

GME_API_PUB.update_material_line( p_batch_header_rec => l_batch_header_rec

,p_material_detail_rec => l_material_detail_rec

,p_locator_code => NULL

,p_org_code => NULL

,x_material_detail_rec => x_material_detail_rec

,x_message_count => x_message_count

,x_message_list => x_message_list

,x_return_status => x_return_status

,p_init_msg_list => fnd_api.g_true

,p_commit => fnd_api.g_false

,p_validate_flexfields => fnd_api.g_false);

IF x_return_status = FND_API.g_ret_sts_success THEN

COMMIT;

ELSE

IF x_message_count = 1 THEN

RAISE MTL_LINE_EXC;

ELSE

FOR j IN 1..x_message_count LOOP

FND_MSG_PUB.get( p_msg_index => j

,p_encoded => 'F'

,p_data => x_message_list

,p_msg_index_out => l_msg_index_out);

RAISE MTL_LINE_EXC;

END LOOP;

END IF;

END IF;

END LOOP;

RETURN 'SUCCESS';

EXCEPTION

WHEN OTHERS THEN

RETURN 'FAILURE';

RAISE;

END update_mtl_line_dff_prc;

RTF Template Labels get displayed as Junk Characters when generated as PDF

An RTF Layout Template includes the Greek/Turkish Symbol and is displayed correctly using Microsoft Word. Generating a PDF output causes these symbols to be converted in to inverted question mark '?'.

Thanks to Metalink I was able to figure out the root cause and solution. I have summarized what should be the fix for such issues.

These characters requires the Albany font files to be available within the Java Runtime

Environment (JRE). The question mark is an indication the that the XML Publisher generator

was not able to find the proper font file.

In Release12, Copy the ALB*.ttf font files to the $OA_JRE_TOP/lib/fonts and $AF_JRE_TOP/lib/fonts directory.

You can find the ALB*.ttf font files in the $FND_TOP/resource folder.

For me this file did the magic ALBANYWT.ttf

Converting the PLD to PLL and PLX

Generally one would always work on a PLL when it comes to working on Custom PLL and compile it to PLX.

I personally feel that working on a text version allows one to work on the Custom PLL much faster and especially when converting the text format to executable is just two commands.

For e.g. if you have a .pld file you can convert it to .plx using the following two step process.

Converting PLD to PLL

frmcmp_batch Module=.pld Userid=apps/apps@MYDEVDB parse=yes Module_Type=LIBRARY

Converting PLL to PLX

frmcmp_batch Module=.pll Userid= apps/apps@MYDEVDB compile_all=yes Module_Type=LIBRARY

Converting PLL to PLD

If you already have something in PLL format and want to convert it to text

frmcmp_batch Module=.pll Userid= apps/apps@MYDEVDB Script=YES Module_Type=LIBRARY Output_File= .pld

Just thought this might come in handy for the XML Publisher experts out there.

I quite have run into situation when my DBA friend missed out on installing the RTF Template as part of object migration and as I don't have access to the XML Publisher Administrator responsibility I cannot verify the same.

The queries below lets one verify from the backend whether the template and the Data Definition were uploaded by your System Administrators or not.

/* Query for Data Definition */

SELECT xddt.data_source_name "Data Source Name",
xddb.data_source_code "Code",
fat.application_name "Application Name"
FROM xdo_ds_definitions_b xddb,
xdo_ds_definitions_tl xddt,
fnd_application fa,
fnd_application_tl fat
WHERE xddb.data_source_code LIKE 'XX%'
AND xddt.data_source_code = xddb.data_source_code
AND xddt.LANGUAGE = 'US'
AND fa.application_short_name = xddb.application_short_name
AND fat.application_id = fa.application_id
AND fat.LANGUAGE = 'US'

/* Query for Templates */

SELECT xtt.template_name "Template Name",
xtb.template_code "Template Code",
fat.application_name "Application Name",
xddt.data_source_name "Data Definition",
xtb.template_type_code "Template Type",
xtb.default_language "Language",
xtb.default_territory "Territory",
xl.file_name "File Name",
xl.file_data "File"
FROM xdo_templates_tl xtt,
xdo_templates_b xtb,
fnd_application_tl fat,
xdo_ds_definitions_tl xddt,
xdo_lobs xl
WHERE (xtt.template_name LIKE '%' OR xtt.template_name LIKE 'XX%')
AND xtt.LANGUAGE = 'US'
AND xtb.template_code = xtt.template_code
AND fat.application_id = xtb.application_id
AND fat.LANGUAGE = 'US'
AND xddt.data_source_code = xtb.data_source_code
AND xddt.LANGUAGE = 'US'
AND xl.lob_code = xtb.template_code
AND NVL (xl.program, 'X') <> 'RTF2XSLParser 5.6.3'
AND xl.LANGUAGE = xtb.default_language
AND xl.territory = xtb.default_territory

Migrating Oracle e-business Business Events

To download Business Event System object definitions from a database in Oracle e-ebusiness to a flat XML file, you can either run the Workflow XML Loader manually, or, if you are using the standalone version of Oracle Workflow, you an use a script to run the loader.

To run the Workflow XML Loader manually, run java / jre against oracle.apps.fnd.wf.WFXLoad. 

You must specify your CLASSPATH pointing to the Java Runtime Environment, the directory containing the Workflow JAR files, the Oracle JDBC implementation, and the following Workflow JAR files:
• wfjava.jar - Workflow Java utilities
• wfapi.jar - Workflow Java APIs

java -classpath "$/rt.jar:$:$/wfjava.jar:$/wfapi.jar:$/jdbc/lib/classes111.zip:" oracle.apps.fnd.wf.WFXLoad [-d|-u]   <apps_username> <apps_pwd> <server:server_port:sid> thin US <filename> [EVENTS|SUBSCRIPTIONS] <event_name>

• Specify whether you want to upload(-u) or download(-d)
• Specify the apps user name and password
• Provide the server details i.e the servername/port/sid
• Provide the name of the file to which you want to download or from which you want to upload
• Specify whether you want to download subscriptions/events
• Specify the name of the event for download

Sample for downloading events:
adjava oracle.apps.fnd.wf.WFXLoad -d aapps **** myserver:1541:sid thin US ./xx_oracle_apps_po_rfq_event_RFQActiveEvent.wfx EVENTS xx.oracle.apps.po.rfq.event.RFQActiveEvent
Sample for downloading subscriptions:
adjava oracle.apps.fnd.wf.WFXLoad -d apps **** myserver:1541:sid thin US ./xx_oracle_apps_po_rfq_event_RFQActiveEventSubscription.wfx SUBSCRIPTIONS xx.oracle.apps.po.rfq.event.RFQActiveEvent
Sample for uploading events

adjava oracle.apps.fnd.wf.WFXLoad -u apps **** myserver:1554:sid thin US  xx_oracle_apps_po_rfq_event_RFQActiveEvent.wfx
Sample for uploading subscriptions
adjava oracle.apps.fnd.wf.WFXLoad -u apps **** myserver:1554: sid thin US  xx_oracle_apps_po_rfq_event_RFQActiveEventSubscription.wfx

How to create a host concurrent program in the Oracle e-business suite

How do I run a shell script as a concurrent program in Oracle e-business?

1: Write the script and call it .prog Place the script under the bin directory under your applications top directory. For example, call the script CUSTOM.prog and place it under $CUSTOM_TOP/bin 

2: Make a symbolic link from your script to $FND_TOP/bin/fndcpesr For example, if the script is called CUSTOM.prog use this: ln -s $FND_TOP/bin/fndcpesr CUSTOM This link should be named the same as your script without the .prog extension It should be in the same directory as the script. 

3: Register a concurrent program as described above, using an execution method of 'Host' Use the name of your script without the .prog extension as the name of the executable For the example above, you would use CUSTOM 

4: Your script will be passed at least 4 parameters, in $1 through $4 These will be: orauser/pwd, userid, username, request_id Any other parameters you define will be passed in $5 and higher. Make sure your script returns an exit status. 

A sample script is presented in the article Copying Output of Concurrent Program to a given directory

Running a java concurrent program from command line in Oracle e-business

If you have any java concurrent program that does not rely on  apps context related features.. There is a simple and easy way to run Oracle e-business java based concurrent programs from the command line.

java -Ddbcfile=<my_dbc_file > -Dreqeust.requestid=123456789 -Drequest.logfile=./myreq.log oracle.apps.fnd.cp.request.Run <concurrent_program_class>"TOKEN1=TOKEN1:TOKEN2=TOKEN2"

Let me explain:

1. The parameter -Ddbcfile=<my_dbc_file > is the dbc file for your environment. This is usually located in $FND_TOP/secure
2. Drequest.logfile represents the location of your log file. If you do not provide this value it would get defaulted.
3.  <concurrent_program_class> This represents the underlying java class that you would implement for you java concurrent program. This needs to implement the interface JavaConcurrentProgram and also needs to provide a concrete implementation of the method: public void runProgram(CpContext pCpContext)

An example would be :
java -Ddbcfile=$FND_TOP/secure/env.dbc -Dreqeust.requestid=123456789 -Drequest.logfile=./myreq.log oracle.apps.fnd.cp.request.Run oracle.apps.xx.fax.XxFaxDocument "TOKEN1=./fax:TOKEN2=12345
"

XDOLoader : Wrapper Tool

The attached tool is a wrapper on top of XDOLoader. This is a convenience tool to scan all files in the directory from which the command is run and uploads them to the database.
Download here (https://sites.google.com/a/applikast.net/applikast/file-cabinet/xdoload.pl?attredirects=0&d=1)
Also create a file called install.txt in your installation directory with the following contents and replace with the appropriate values:

db_sid=mydb
db_port=1561
db_server=localhost

Ensure that each entry remains on a different line in the text file. It is also important that the text file doesn't have any ^M characters.

Please note that the xml publisher files should follow the naming convention that is followed by XDOLoader DOWNLOAD. This helps the tool identify the Product short name/ lob code and the lob type as well. Once these are derived the only parameter that is needed to run xdoloader is the apps password that is passed as a commandline argument.
Sample files names when downloaded using XDOLoader download are:
BURSTING_FILE_AR_ARDLP_NON_SRS.xml
TEMPLATE_SOURCE_AR_ARDLP_NON_SRS_en.rtf

The file in the link needs to be downloaded and renamed as xdoload.pl

Once this is done you can run this in your unix shell as
perl xdoload.pl apps_pwd

You can put this in the /bin directory and create an alias so that you don't need to type perl xdoload.pl all the time.
eg. cp xdoload.pl $CUSTOM_TOP/bin
alias xdoload='perl $CUSTOM_TOP/bin/xdoload.pl'.
Then run the command using xdoload directly

This wrapper internally calls out to the XDOLoader class with the appropriate parameters as described in the post: Migrating BI Publisher Files: using XDOLoader FNDLOAD
Please do leave your feedback and comments it will help me provide something more meaningful going forward.

Migrating BI Publisher Files: using XDOLoader FNDLOAD

BI Publisher reports are typically made up of several components:

• Metadata that defines the details about the data definition and templates
• BI Publisher files i.e Data Definition Files/RTF Template/Bursting file etc
• RDF files in case of reports based BI Files
• Concurrent programs to run the report.

In Order to move the entire definition of the BI Publisher report from one instance to other you need to move each of the components described above. The tools namely XDOLoader and FNDLOAD are used to migrate these files across instances.

You can use the following commands to download :
• Metadata:
FNDLOAD apps/****0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXJW_ARDLP_NON_SRS.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=AR DATA_SOURCE_CODE=ARDLP_NON_SRS

• BI Publisher files i.e BI Publisher Data definition template/Bursting File/RTF Template for ARDLP_NON_SRS Data definition:

java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD ***** \
-JDBC_CONNECTION db_server:db_port:db_sid \
-APPS_SHORT_NAME AR \
-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct \
-DS_CODE ARDLP_NON_SRS

• Copy the RFD file if any from the instance directly

• Download the concurrent program using:
FNDLOAD apps/**** 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_ARDLP_NON_SRS.ldt PROGRAM CONCURRENT_PROGRAM_NAME="ARDLP_NON_SRS"

Once the files have all been downloaded you need to take them to the middle tier of another instance and upload them back. This can be done using the following commands:

• Metadata:

FNDLOAD apps/****0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXJW_ARDLP_NON_SRS.ldt

• Upload each of the xml publisher files using commands below:

o Bursting File:
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \ -DB_USERNAME apps \ -DB_PASSWORD **** \ -JDBC_CONNECTION db_server:db_port:instance_sid \ -LOB_TYPE BURSTING_FILE \ -APPS_SHORT_NAME AR \ -LOB_CODE ARDLP_NON_SRS \ -LANGUAGE en \ -TERRITORY US \ -NLS_LANG American_America.WE8ISO8859P1 \ -XDO_FILE_TYPE XML-BURSTING-FILE \ -FILE_CONTENT_TYPE 'text/html' \ -FILE_NAME ./xmlpub/BURSTING_FILE_AR_ARDLP_NON_SRS.xml

o RTF Template:
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \ -DB_USERNAME apps \ -DB_PASSWORD **** \ -JDBC_CONNECTION db_server:db_port:instance_sid \ -LOB_TYPE TEMPLATE_SOURCE \ -APPS_SHORT_NAME AR \ -LOB_CODE ARDLP_NON_SRS \ -LANGUAGE en \ -TERRITORY US \ -NLS_LANG American_America.WE8ISO8859P1 \ -XDO_FILE_TYPE RTF \ -FILE_CONTENT_TYPE 'text/html' \ -FILE_NAME ./xmlpub/TEMPLATE_SOURCE_AR_ARDLP_NON_SRS_en.rtf

o Data Definition Template:
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \ -DB_USERNAME apps \ -DB_PASSWORD **** \ -JDBC_CONNECTION :: \ -LOB_TYPE DATA_TEMPLATE \ -APPS_SHORT_NAME AR \ -LOB_CODE ARDLP_NON_SRS \ -LANGUAGE en \ -TERRITORY US \ -NLS_LANG American_America\.WE8ISO8859P1 \ -XDO_FILE_TYPE XML-DATA-TEMPLATE \ -FILE_CONTENT_TYPE 'text/html' \ -FILE_NAME DATA_TEMPLATE.xml

• Copy the RFD file if any to the reports/US folder directly. If it is customised then copy to /reports/US and then create a soft link in /reports/US directory.

• Upload the concurrent program using:
FNDLOAD apps/**** 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct .ldt

You can find a wrapper program to the xdoloader methods here:
XDOLoader : Wrapper Tool

iStore - The beginners guide

iStore for Beginners

What is iStore ?

Fully integrated with the Oracle E-Business Suite, Oracle iStore 11i is a powerful tool that allows businesses to create and manage online ecommerce sites. A highly configurable Java application, Oracle iStore employs Oracle foundation and back-end applications along with its own comprehensive Java and PL/SQL programming and logic to deliver a high-end  application that responds to the growing need for online commerce in business-to-business (B2B), business partner, and business-to-consumer (B2C) electronic scenarios. Oracle iStore lets businesses create, manage, and personalize powerful Internet storefronts in a secure and scalable environment.

Oracle iStore contains two user interfaces:

_ The Site Administration Application:

The Site Administration Application allows site administrators to create and maintain multiple sites, catalogs, and related business rules, as well as access business and operational reports.

_ The Customer Application:

This is the customer-facing application which features shopping carts and lists, cart sharing, a full range of order taking and tracking capabilities, e-mail notifications of user events, quote retrieval and updates, marketing, guided selling, and more.

Background / Overview

In this article we would concentrate on the Site Administration Application and in particular features that deal with the Customer Site Look and feel related features. I have chosen to concentrate on the look and feel related areas because this is where the developer usually comes into the picture and perhaps is the first pain point whenever someone starts off learning the iStore application. We would deal with the nuances of the iStore technical pieces in articles that are to come in the future.

Customer Application Display

Oracle iStore utilizes JavaServer Pages (JSPs), which combine Application Programming Interfaces (APIs) to call dynamic data and HTML to present static data. A robust PL/SQL layer interfaces with the Oracle database to provide consistent and accurate data storage and retrieval.

In the Customer Application, each web page is made up of an overall JSP (for example the seeded Site Home Page, ibeCZzpHome.jsp) that calls other JSPs to display the various areas of the page. Oracle iStore uses templates and other components to present the Customer Application.

Display Templates

The bulk of the Customer Application display is presented through the hundreds of Display Templates that are seeded in Oracle iStore. Display Templates are of various types, depending upon which areas of the Customer Application they are meant to display. For example, some templates display the shopping cart pages, some display the catalog pages, and other templates display the order tracking pages. Each template used in the online sites links to a JSP which provides the actual display content. By mapping your own JSPs to the seeded templates, you can provide your own customized content in the sites.

Section and Product Template Gallery

Using sections and their products organized into a hierarchal structure, your product catalog is presented through a powerful section and product catalog building tool. Display Templates for sections and products are presented in the Site Administration Application in WYSIWYG fashion, allowing you to easily select how your sections and products actually display to customers.

Bin Display

Oracle iStore’s Display Template functionality allows you to map bins with specific JSP content into the top, bottom, and sides of the catalog, shopping cart, and registration pages. Section page bins can be configured in two ways -- using fixed layout, where every catalog section page shows the mapped bins in the same location on every page, or using configurable layout, where bins can be placed on the page of a specific section and in a specific location.

Images, Messages, and HTML Content

Oracle iStore features flexible and reusable content tools. To present product images and HTML content in your specialty sites, you map seeded content components to seeded or new media objects, which are in turn linked to the content files (e.g., .gif, .htm) which you supply. To present messages, you utilize hundreds of seeded text messages, some of which are also reusable media objects. See the Implementing Content chapter of the Oracle iStore Implementation and Administration Guide for details. The seeded Customer Application text messages can be altered.

Content Repository

The Content Repository in the Site Administration Application allows you to view and upload the source files used in your site pages. The Content Repository page lists the source files in your Content Repository, and allows you to preview the files. It also allows access to other pages where you can upload and update source files.

In the Content Repository page you can:

_ View all source files that have been uploaded to the Content Repository

_ Delete source files from the Content Repository

_ Search for source files within the Content Repository

_ Access the Upload/Update Source File pages, where you can upload source

files for the repository

Content Components and Media Objects

Oracle iStore’s content components and media objects are reusable content placeholders in the seeded JSPs that allow you to define content for the Customer Application beyond that provided by section or product description information.

This content can be image or html files that you associate with a section or product in order to provide your customers with additional information. It also can be text messages used to prompt or guide the user.

Deep Dive – Catalog and configuring the catalog display

To implement iStore you would typically have to do the following:

·         You would create a customer site that any given customer has access to.

·         Each site has a catalog

·         Each catalog has sections

·         And each section displays products under it along with access to search functionality.

The catalog hierarchy is divided in the following:

·         Sections

o        Root Section: This is the top most section in the hierarchy and the Site gets associated with a root section. All sections are directly or indirectly children of the root section.

o        Navigable Section: This is defined to be any section under the root section(and root section included) that a user can browse and navigate to. Each navigable section can have subsections defined under it and can also have products.

o        Leaf Section:  This is a navigable section itself but cannot have any further navigable section defined under it it can contain only products

o       Featured Section: This is a special section which cannot be part of a browsing hierarchy. Each featured section is rendered automatically on the home page of each navigable section. Featured sections can contain only products and cannot have sub sections.

For a clear description of rules pertaining to each section type please refer to the administrative guide.

·         Section and Product Display Profile Options

Several profile options control how sections and products appear in the specialty

site sections. These are discussed in this section.

·         Setting the Number of Home Page Tabs and Subtabs

Top-level sections of a site’s root node become tabs across the top of the Site Home Page. The second-level sections become the subtabs across the top of the Site Home Page.

The number of tabs and subtabs is controlled by these profile options:

_ IBE: Number of Menu Tabs --- Specifies the number of menu tabs across the

top of the Site Home Page. 

_ IBE: Number of Menu Subtabs --- Specifies the number of menu subtabs below

the menu tabs on the Site Home Page.

·         Setting the Number of Child Sections Per Page

The profile option, IBE: Sections Per Page for Display, allows you to set the maximum number of subsections to display on a page before the subsections fall to a new page.

·         Displaying the Section Path

The profile option, IBE: Use Section Path, determines whether the section path displays in those catalog templates which can display a section path.

·         Displaying Bins with Seeded Content

Three bins with seeded JSP content are controlled by profile options when using Fixed Layout for sections. These bins are:

1. Browse Bin

2. Global Bin

3. Welcome Bin

·         Displaying a Set Number of Items in Leaf Sections

The profile option, IBE: Items Per Page for Display, specifies number of items to display in Leaf sections of the hierarchy (the ending sections of a node).

·         Displaying a Set Number of Items in Sections

The profile option, IBE: Items Per Section for Display, specifies the number of items to display in sections of the hierarchy that contain products.

·         Displaying a Set Number of Items in Multiple-Section Template

The profile option, IBE: Lines Per Section for Multiple Section Display, specifies the number of items to display per section in Display Templates that show multiple sections.

·         Section Templates Overview

The structure of your catalog sections as they display on Customer UI pages is determined by your layout and template choices for the sections. To aid your understanding of the options involved, following is a process flow for building section structure.

Process Flow for Building Section Structure

In this process, you associate each section to:

A Layout Template --- This choice determines whether all sections have the same bin locations (Fixed Layout) or whether each section can have separate bin locations (Configurable Layout).

For e.g with Fixed layout the Browser Bin would always be to the left and the welcome bin would always be to the right.

Fixed layout:

Configurable Layout

and ---

A Display Template --- This choice determines which section elements (name, description, content components) display for each section and how the section looks.

One template could show the section to display such that only the navigable subsections show up as links so that you can navigate the:

Alternately another template could show the sections along with the products in a products drop down:

You could alternately have a layout where the subsections and their products are listed as shown below:

These different layouts can be achieved with the help of templates.

Similarly there are different templates for rendering product information as well. You could for example have product information with product detail/ with picture/ with large picture so on and so forth.

There are several such templates that are shipped by default by Oracle. If none of these layouts suit your requirement you would need to go in for a customization.