Set up for OAF pages development

This post helps you in setting up the OAF page development environment.

Pre-Requisites to Setup OAF development environment
-------------------------------------------------------------------------------
1) download Jdeveloper OAF Patch for respective Oracle eBiz Instance.
2) download dbc files from unix server for respective Oracle eBiz Instance you are going to work with.

Detailed instructions
----------------------------------

1) Find your Oracle eBiz Version by logging into your apps -- Click About this page link at bottom of the page -- Click Technology components tab -- you will see the screen below

Note down your OA Framework version. In my case it is 12.1.3.

So i need to download the Jdeveloper OAF patch for this version(12.1.3) in the below link.
How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.x (Doc ID 416708.1)

you will find the table like below shown



click the respective patch and download it.

Extract the downloaded .zip file into any location.
C:\p7523554_R12_GENERIC

you will find 3 folders. (jdevbin, jdevdoc, jdevhome)

Set the JDEV_USER_HOME environment variable
----------------------------------------------------
Right click my computer -> Properties -> Advanced Tab -> Environment variables



Variable name : JDEV_USER_HOME
Variable value will be DRIVE_LOCATION:\PATCH_NAME\jdevhome\jdev


You have almost completed the setup.

Downloading dbc(database connectivity file) 
-----------------------------------------------
This file used to connect the Jdeveloper to your Oracle eBiz Instance.
This file will be available with your DBA's or $FND_SECURE top.

FTP the .dbc file to "C:\p7523554_R12_GENERIC\jdevhome\jdev\dbc_files\secure"

dbc file contains information like below

#DB Settings
#Mon May 02 18:26:56 IST 2012

GUEST_USER_PWD=GUEST/ORACLE
APPL_SERVER_ID=A1E590D05E00456FE04400000000000021673182782189486806263446144422
FND_JDBC_BUFFER_DECAY_INTERVAL=300
APPS_JDBC_DRIVER_TYPE=THIN

FND_JDBC_BUFFER_MIN=1
GWYUID=APPLSYSPUB/PUB
FND_JDBC_BUFFER_MAX=5
APPS_JDBC_URL=
FND_JDBC_STMT_CACHE_SIZE=100
TWO_TASK=POC12
JDBC\:oracle.jdbc.maxCachedBufferSize=358400
JDBC\:processEscapes=true
FND_MAX_JDBC_CONNECTIONS=500
FND_JDBC_USABLE_CHECK=false
FNDNAM=APPS
FND_JDBC_PLSQL_RESET=false
DB_PORT=1553
FND_JDBC_CONTEXT_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5

Now start your OAF page development.

Terminologies We Should Know

As a Oracle apps Technical Consultant, These terminologies are often used by everyone.
Listing the terminologies over here

MIDDLEWARE:
Software provides services beyond those provided by OS.
It is integral modern information technology based on XML,SOAP,Web Services and SOA.
Middleware sits "in the middle" between application software . Middleware enables interoperability between applications that run on different OS.
example : EAI

EAI : Enterprise Application Integration
SCM, CRM, BI and other type of applications cannot communicate with one another in order to shared data or business rules.EAI is the process of linking such applications within single organization together in order to simplify and automate business process.
for 'N' application
N*(N-1)
------    connections are required.
  2   

EDI: Electronic Data Interchange
Structured transmission of data between organizations by electronic means. It is used to transfer electronic documents or business data from one computer system to another computer system, i.e. from one trading partner to another trading partner without human intervention.

TIBCO:
People tell TIBCO is a Software/Company name/Middleware. Which one is correct?
Everything is correct.They are leading provider of business integration solutions delivering infrastructure software that enables to seamlessly integrate business systems in real time.
They call the technology as "Information Bus" or TIB middleware.

SOAP: Simple Object Access Protocol
A better way to communicate in internet is using HTTP becoz HTTP is supported by all browsers and servers.SOAP provides a way to communicate between applications running on different operating systems, with different technologies and programming languages in distributed systems.

SOAP Message Structure
A SOAP message is an ordinary XML document containing the following elements
<?xml version="1.0"?>
<soap:Envelope
xmlns:soap="http://www.w3.org/2001/12/soap-envelope"
soap:encodingStyle="http://www.w3.org/2001/12/soap-encoding">

<soap:Header>
...
</soap:Header>

<soap:Body>
...
  <soap:Fault>
  ...
  </soap:Fault>
</soap:Body>

</soap:Envelope>

Yet to update this post.

Orders Autobook

There could be certain case where client require to book the orders in bulk just by running single concurrent program. There's a public API provided by oracle to perform various operations in the sales order in the order management system.

Public API Name is : OE_ORDER_PUB
There are record type to hold the order header, line level informations.
Header_Rec_Type
Header_Tbl_Type
Header_Val_Rec_Type
Header_Val_Tbl_Type
Header_Adj_Rec_Type
Header_Adj_Tbl_Type
Line_Rec_Type
Line_Tbl_Type
Line_Val_Rec_Type
Line_Val_Tbl_Typ

Procedure Name : PROCESS_ORDER

Process Order supports action requests that allow users to execute a variety
of actions such as booking, hold application and removal.

OE_ORDER_PUB.PROCESS_ORDER
   (
      p_api_version_number      =>   1.0,
      p_init_msg_list           =>   FND_API.G_TRUE,
      p_action_commit           =>   FND_API.G_FALSE,
      x_return_status           =>   x_return_status,
      x_msg_count               =>   v_msg_count,
      x_msg_data                =>   v_msg_data,
      p_header_rec              =>   p_header_rec,
      p_header_val_rec          =>   p_header_val_rec,
      p_header_scredit_tbl      =>   p_header_scredit_tbl,
      p_line_tbl                =>   p_line_tbl,
      p_line_adj_tbl            =>   p_line_adj_tbl,
      p_line_scredit_tbl        =>   p_line_scredit_tbl,
      p_action_request_tbl      =>   p_action_request_tbl,
      x_header_rec              =>   x_header_rec,
      x_header_val_rec          =>   v_header_val_rec,
      x_header_adj_tbl          =>   v_header_adj_tbl,
      x_header_adj_val_tbl      =>   v_header_adj_val_tbl,
      x_header_price_att_tbl    =>   v_header_price_att_tbl,
      x_header_adj_att_tbl      =>   v_header_adj_att_tbl,
      x_header_adj_assoc_tbl    =>   v_header_adj_assoc_tbl,
      x_header_scredit_tbl      =>   v_header_scredit_tbl,
      x_header_scredit_val_tbl  =>   v_header_scredit_val_tbl,
      x_line_tbl                =>   x_line_tbl,
      x_line_val_tbl            =>   v_line_val_tbl,
      x_line_adj_tbl            =>   v_line_adj_tbl,
      x_line_adj_val_tbl        =>   v_line_adj_val_tbl,
      x_line_price_att_tbl      =>   v_line_price_att_tbl,
      x_line_adj_att_tbl        =>   v_line_adj_att_tbl,
      x_line_adj_assoc_tbl      =>   v_line_adj_assoc_tbl,
      x_line_scredit_tbl        =>   v_line_scredit_tbl,
      x_line_scredit_val_tbl    =>   v_line_scredit_val_tbl,
      x_lot_serial_tbl          =>   v_lot_serial_tbl,
      x_lot_serial_val_tbl      =>   v_lot_serial_val_tbl,
      x_action_request_tbl      =>   v_action_request_tbl
   );

LONG RAW to BLOB Conversion

We all know there's a function called TO_LOB which converts LONG to BLOB object.

But we might experience this rare case LONG_RAW to BLOB. Yes i encountered this problem when i want to execute this query in oracle apps 11i Oracle 9 database.

SELECT TRUNC(SUM(DBMS_LOB.GETLENGTH(image))/1024/1024,2)||' MB'  bytes

FROM   per_images

Error:  ORA-00997: illegal use of LONG datatype

I was wondering how come the query is executing in R12 not in 11i..then i cross checked it

In oracle apps R12 the IMAGE column data type in PER_IMAGES table is  BLOB

whereas In oracle apps 11i the IMAGE column data type in PER_IMAGES table is LONG RAW

So the above query will not work in 11i..so i want to convert this long_raw to blob data type.

I tried this

SELECT TRUNC(SUM(DBMS_LOB.GETLENGTH(TO_LOB(image)))/1024/1024,2)||' MB'  bytes

FROM   per_images

ORA-00932: inconsistent datatypes: expected - got BINARY

Note: If the size is less than 32k you will get the value.

Solution to get the length of the image column is:

CREATE TABLE XX_PER_IMAGES_TEMP (IMAGE BLOB);

INSERT INTO XX_PER_IMAGES_TEMP

                                 SELECT

                                   TO_LOB(IMAGE)

                                 FROM

                                    PER_IMAGES;

SELECT TRUNC(SUM(DBMS_LOB.GETLENGTH(image))/1024/1024,2) image_size

FROM

XX_PER_IMAGES_TEMP;

Hope this post helps you on how to convert. comments please.

REGEXP_INSTR function for oracle 9i

I created a procedure which used all these functions in Oracle 10g
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_INSTR

Now i want to use the same procedure in Oracle 9i . Then i realized we don't have the above functions in Oracle 9i database. Then googled for string functions available in Oracle 9i. I come to know there's a package called OWA_PATTERN which provides few pattern matching facility. It didn't fulfill my needs. so my further googling gives me a link in which i found function script for REGEXP_LIKE & REGEXP_REPLACE.

so,i started writting code for REGEXP_INSTR.

CREATE OR REPLACE FUNCTION REGEXP_INSTR(LC_IN_STR VARCHAR2,LC_PATTERN VARCHAR2)
return number is
i NUMBER;
LC_LENGTH_STR number;
ln_number_pos NUMBER;
begin
  LC_LENGTH_STR:=length(LC_IN_STR);
 for i in 1..LC_LENGTH_STR
 LOOP
    ln_number_pos:=OWA_PATTERN.AMATCH(lc_in_str,i,lc_pattern);
    EXIT when ln_number_pos>0;
 end LOOP;
 return ln_number_pos-1;
end;
/

The Function Usage Example :
1) SELECT regexp_instr(banner,'[0-9]')
    FROM v$version where banner='Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production';
      Returns : 7

2)SELECT regexp_instr(banner,'[A-Z]')
   FROM v$version where banner='Oracle9i Enterprise Edition Release   9.2.0.6.0 - 64bit Production';
      Returns : 1