Find the Personalization applied on a Oracle Apps Form

SELECT
ffv.form_id ,
ffv.form_name ,
ffv.user_form_name ,
ffv.description "Form Description",
ffcr.SEQUENCE ,
ffcr.description "Personalization Rule Name",
ffcr.TRIGGER_EVENT,
ffcr.TRIGGER_OBJECT,
ffcr.CONDITION,
ffcr.ENABLED "ENABLED OR NOT"
FROM
fnd_form_vl ffv,
fnd_form_custom_rules ffcr
WHERE
ffv.form_name = ffcr.form_name
ffcr.form_name = FORM_NAME  -- GIVE THE FORM NAME
ORDER BY ffv.form_name,Ffcr.Sequence;

Oracle Applications 11i/R12 eBusiness Suite Architecture

Oracle Applications 11i/R12 eBusiness Suite is a framework used  the n-tier architecture (the architecture made for the web)  that supports Oracle Application products.

  The sample 3-Tier architecture which oracle is used is like as :

Oracle Applications R12 Architecture  

 

 

 

  

Database tier 

    

     The Database tier contains the Oracle Database Server which store all the data needed by Oracle Applications. The database stores the Oracle Applications online help as well. The database tier contains the Oracle data server files and Oracle Applications database executables that physically store the tables, indexes, and other database objects for your system.The database server communicates with middle tier and does not communicate directly with the desktop tier. 

Note:Database Version in 11i (11.5.9 & 11.5.10) was 9i Rel 2 where as in Release 12 its 10g R2 (10.2.0.2)

Application (or middle) tier 

The application tier played various role like hosting the various servers and service groups that process the business logic, and managing communication between the desktop tier and the database tier. 

it includes 

·                    Web Server:  listens for HTTP requests and provides an HTTP response to the client. The HTTP response usually consists of an HTML document, but can also be a raw file, an image or some other type of document (defined by MIME-types);

·                      Concurrent Processing Server: server which run reporting programs and data updating programs (periodically or ad hoc). These programs (named concurrent programs) run on this server in background in order not to interfere with the normal operations. When a request is made a row is inserted into FND_CONCURRENT_REQUEST table. The concurrent managers read these requests in the table and start the relevant concurrent programs;

·                    Administration Server:  contains scripts which are used to administer the Oracle Applications eBusiness Suite. From this server the following operations can be performed:

§      Applying patches (using adpatch);

§      Maintaining Oracle Applications (using adadmin);

§      Upgrading Oracle Applications from an earlier release to  11.5.x (using adaimgr);

·                    Forms server: includes the Forms listener and the Forms Runtime Engine. The Forms Runtime Engine maintains a connection to the database on behalf of the Java client. It uses the same Forms, Menus, and Libraries files that are used for running in client/server mode. No application code changes are required to deploy a legacy client/server application to the Internet;

·                    Reports Server:   run Oracle Applications reports and is always installed on the same node as the Concurrent Processing Server. However the reports are administered separately from the concurrent processing reports; 

·                    Discoverer Server (optional):  is installed if Oracle Discoverer will be used. 

Note:Application Tier consist of iAS(1.0.2.2.2) & Developer 6i (Forms & Reports) but in Applications R12 it is build on Fusion Middleware (10g Web Server and 10g Forms & Reports).

 INSTANCE_TOP added in Release 12 for configuration and log files.It uses DBI (Daily Business Intelligence) Though you can still integrate 10.1.2 discoverer with R12.

 Desktop tier    

    A client could access the Oracle Applications only by using a Web Browser which must have Oracle JInitiator installed on it. Oracle JInitiator is the Oracle Java Virtual Machine used to run Oracle Forms applets.The Forms client applet is a general-purpose presentation applet that supports all Oracle Applications Forms-based products, including those with customizations and extensions. The Forms client applet is packaged as a collection of Java Archive (JAR) files. The JAR files contain all Java classes required to run  Oracle Applications forms.

Oracle JInitiator will no longer be required to run Oracle Forms in E-Business SuiteRelease12. Oracle Forms in Release 12 will run directly in the  Sun Java2 Standard Edition plug-in.

Backend Process

When user makes a request from browser by typing URL, then the request hit to oracle webserver and these requests are recorded in logs.Webserver checks with http request there is no cookie attached (Cookie is

message given by webserver to identify client) and hence user is new it passes login page to User.User types his Username/Password in login window and click submit button Webserver checks the username/password that is needs to authenticate against database FND_USER table, so it needs some user to check client’s username password in database. It uses GUEST/ORACLE user to authenticate user in database. This information is always located in FND_TOP /secure/ <conte xt_nam e>.dbc Once user is authenticated it checks against FND_RESPONSIBILITY for

authorization about users responsibilities and assigned responsibilities back to user.

When webserver tries to connect to database for Pl/Sql or any other type of connection it asks IAS_ORACLE_HO ME/Apache/m odplsql/cfg to fulfill request and connection string information is stored inw dbsvr.app file.User submits his requests for batch processing or something else etc. These

requests are fulfilled by concurrent managers.

For   Discoverer Server we are the  (.dbc) file to connect from server it always on  (FND_TOP /secure)

Directory .

Set Multi_Org at Report level & Parse Special Characters like (¿ * @ ? % # )

In oracle Application 11i sometime we need to Set multi_org at Report level

Possible Solution:
declare user parameter  in Report Builder

P_CONC_REQUEST_ID

/*This is one of
the bind variable when ever we submit a request in SRS window that request ID
will be passed to this bind variable, based on this request id we can execute
concurrent request process. Without this find variable we can’t use SER_EXIT Procedure
after that we can  apply the  triggers to the report */


Before Report Trigger
function BeforeReport return boolean is
begin
  SRW.USER_EXIT('FND SRWINIT');
  /* FND SRWINIT will initialize user profile values according to that profile values data will be retrieved from database */
  return (TRUE);
end;


After report Trigger
function AfterReport return boolean is
begin
  SRW.USER_EXIT('FND SRWEXIT'); /* FND SRWEXIT freeze the  memory which is occupied by user profile values*/
  return (TRUE);
end;

                      Report changes for Special characters errors as "Invalid Character"

Possible Solution:
open the rdf in report builder and go to property palette of report ,
In xml settings property you will find this prolog value setting

Change into
<?xml version="1.0" encoding="iso-8859-1"?>

#iso-8859-1 is normally used for Western European and parse the special characters like (¿ * @ ? % #)

Create XML File using PL/Sql Procedure and UTL File Package

/*Define package Specification*/

CREATE OR REPLACE Package print_xml
As
   Procedure Openxmlfile(P_Dir_Name Varchar2,
                       --P_Transaction_Id Mtl_Material_Transactions.Transaction_Id%Type,
                       P_Transaction_Date1 Date,
                       P_Transaction_Date2 Date,
                       P_Transaction_Source_Type_Name Mtl_Txn_Source_Types.Transaction_Source_Type_Name%Type);

end print_xml;

/*Define package body*/

CREATE OR REPLACE Package Body print_xml
as

  v_FILENAME varchar2(30);
  f_XML_FILE UTL_FILE.file_type;
 
  /* you can generate the XML either using Transaction_Id or Transaction Source I am sing source */
 
  Procedure Openxmlfile(P_Dir_Name Varchar2,
                       --P_Transaction_Id Mtl_Material_Transactions.Transaction_Id%Type,
                       P_Transaction_Date1 Date,
                       P_Transaction_Date2 Date,
                       P_Transaction_Source_Type_Name Mtl_Txn_Source_Types.Transaction_Source_Type_Name%Type)
                       is
   V_Record_Data Varchar2(4000) := Null;
  
   v_TRANSACTION_ID  MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID%TYPE:=NULL;
   V_Transaction_Source_Type_Name Mtl_Txn_Source_Types.Transaction_Source_Type_Name%Type:=Null;
   V_Transaction_Date Date:=Null;
    V_Transaction_Name Varchar2(1000):=Null;
   v_ATTRIBUTE1 varchar2(1000):= null;
   v_INVENTORY_ITEM_ID mtl_system_items_b.INVENTORY_ITEM_ID%type:=NULL;
  V_Attribute3 Mtl_System_Items_B.Description%Type:= Null;
    V_SUBINVENTORY_CODE MTL_MATERIAL_TRANSACTIONS.SUBINVENTORY_CODE%TYPE:=NULL;
    V_TRANSACTION_UOM MTL_MATERIAL_TRANSACTIONS.TRANSACTION_UOM%TYPE:=NULL;
V_REVISION  MTL_MATERIAL_TRANSACTIONS.REVISION%TYPE:=NULL;
V_Transaction_Quantity  Mtl_Material_Transactions.Transaction_Quantity%Type:= Null;
V_Currency_Code  Mtl_Material_Transactions.Currency_Code%Type:=Null;
V_SEGMENT1  varchar2(1000):=NULL;

/*define the cursor to select the colmns you want to see in xml format only*/

    cursor xml_cursor
     is
     SELECT
Mt.Transaction_Id
,Mts.Transaction_Source_Type_Name
,mt.Transaction_Date
,Gcc.Segment1||'.' ||Gcc.Segment2||'.'||Gcc.Segment3||'.'||Gcc.Segment4||'.'||Gcc.Segment5||'.'||Gcc.Segment6||'.'||Gcc.Segment7 Transaction_Name
,Msib.Segment1||'-'||Msib.Segment2||'-'||Msib.Segment3 Item
,MSIB.INVENTORY_ITEM_ID
,Msib.Description Description
,MT.SUBINVENTORY_CODE
,MT.TRANSACTION_UOM
,MT.REVISION
,MT.TRANSACTION_QUANTITY
,Mt.Currency_Code
,ML.SEGMENT1||'.'||PPMV.PROJECT_NAME||'.'||PTMV.TASK_NUMBER Locator
FROM
MTL_MATERIAL_TRANSACTIONS MT,
MTL_SYSTEM_ITEMS_B MSIB,
MTL_TRANSACTION_TYPES MTT,
MTL_TXN_SOURCE_TYPES MTS ,
MTL_ITEM_LOCATIONS ML,
PJM_PROJECTS_MTLL_V PPMV,
PJM_TASKS_MTLL_V PTMV
,gl_code_combinations gcc
WHERE MT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MT.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND MT.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND MT.TRANSACTION_SOURCE_TYPE_ID=MTS.TRANSACTION_SOURCE_TYPE_ID
AND MT.LOCATOR_ID=ML.INVENTORY_LOCATION_ID
AND ML.segment19=PPMV.PROJECT_ID
AND ML.segment20=PTMV.TASK_ID
And Mt.Distribution_Account_Id=Gcc.Code_Combination_Id
AND MTS.TRANSACTION_SOURCE_TYPE_NAME=P_Transaction_Source_Type_Name
And To_Char(Mt.Transaction_Date,'DD-MON-YYYY') >=P_Transaction_Date1
And To_Char(Mt.Transaction_Date,'DD-MON-YYYY') <=P_Transaction_Date2 ;
--And Mt.Transaction_Id=P_Transaction_Id;
  
   BEGIN
  
    v_FILENAME := 'PRINT_XML'||TO_CHAR(SYSDATE, 'DDMMYYYYHH24MI') || '.xml';  /*Give the XML file name */

    f_XML_FILE := UTL_FILE.fopen(p_dir_name, v_FILENAME, 'W');

    V_Record_Data := '<?xml version="1.0" encoding="iso-8859-1"?>';
   
    UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);

    UTL_FILE.put_line(f_XML_FILE, '<ITEM_DATA>');
    open XML_cursor;
    loop
      fetch XML_cursor 
      into
              V_Transaction_Id
              ,V_Transaction_Source_Type_Name
              ,V_TRANSACTION_DATE
              ,V_Transaction_Name
             , v_ATTRIBUTE1
            , V_Inventory_Item_Id
              ,V_Attribute3
             ,V_SUBINVENTORY_CODE
              ,V_TRANSACTION_UOM
             ,V_REVISION 
              ,V_Transaction_Quantity
              ,V_Currency_Code 
              ,V_SEGMENT1 ;
     EXIT WHEN XML_cursor%NOTFOUND;

      UTL_FILE.put_line(f_XML_FILE, ' <ITEM_DETAILS>');
      UTL_FILE.put_line(f_XML_FILE,
                        '<TRANSACTION_ID>'||V_Transaction_Id||'</TRANSACTION_ID>');
                       
                        UTL_FILE.put_line(f_XML_FILE,
                        '<SOURCE>'||V_Transaction_Source_Type_Name||'</SOURCE>');
                       
                        UTL_FILE.put_line(f_XML_FILE,
                        '<TRANSACTION_DATE>'||V_Transaction_Date||'</TRANSACTION_DATE>');
                       
                        UTL_FILE.put_line(f_XML_FILE,
                        '<TRANSACTION_NAME>'||V_Transaction_Name||'</TRANSACTION_NAME>');
                       
                         Utl_File.Put_Line(F_Xml_File,
                        '<ITEM>'||V_Attribute1 || '</ITEM>');
                       
                        Utl_File.Put_Line(F_Xml_File,
                        '<ITEM_ID>'||v_INVENTORY_ITEM_ID|| '</ITEM_ID>');
                       
                        UTL_FILE.put_line(f_XML_FILE,
                        '<DESCRIPTION>'||V_Attribute3||'</DESCRIPTION>');
                       
                        UTL_FILE.put_line(f_XML_FILE,
                        '<SUBINVENTORY_CODE>'||V_SUBINVENTORY_CODE||'</SUBINVENTORY_CODE>');
                       
                        UTL_FILE.put_line(f_XML_FILE,
                        '<UOM>'||V_TRANSACTION_UOM||'</UOM>');
                       
                         UTL_FILE.put_line(f_XML_FILE,
                        '<REVISION>'||V_REVISION|| '</REVISION>');
                       
                        UTL_FILE.put_line(f_XML_FILE,
                        '<QUANTITY>'||V_TRANSACTION_QUANTITY||'</QUANTITY>');
                       
                        UTL_FILE.put_line(f_XML_FILE,
                        '<CURRENCY_CODE>'||V_Currency_Code||'</CURRENCY_CODE>');
                       
                        UTL_FILE.put_line(f_XML_FILE,
                        '<LOCATOR>'||V_SEGMENT1||'</LOCATOR>');


      UTL_FILE.put_line(f_XML_FILE, ' </ITEM_DETAILS>');

    end loop;
   
    UTL_FILE.put_line(f_XML_FILE, '</ITEM_DATA>');

    Utl_File.Fclose(f_XML_FILE);
   
     close XML_cursor;

  EXCEPTION
    WHEN UTL_FILE.INTERNAL_ERROR THEN
      raise_application_error(-20500,
                              'Cannot open file :' || v_FILENAME ||
                              ', internal error; code:' || sqlcode ||
                              ',message:' || sqlerrm);
    WHEN UTL_FILE.INVALID_OPERATION THEN
      raise_application_error(-20501,
                              'Cannot open file :' || v_FILENAME ||
                              ', invalid operation; code:' || sqlcode ||
                              ',message:' || sqlerrm);
    WHEN UTL_FILE.INVALID_PATH THEN
      raise_application_error(-20502,
                              'Cannot open file :' || v_FILENAME ||
                              ', invalid path; code:' || sqlcode ||
                              ',message:' || sqlerrm);
    WHEN UTL_FILE.WRITE_ERROR THEN
      raise_application_error(-20503,
                              'Cannot write to file :' || v_FILENAME ||
                              ', write error; code:' || sqlcode ||
                              ',message:' || sqlerrm);
end;
end print_xml;



/*Calling Method*/

 Begin
PRINT_XML.OpenXmlFile('path','date1(from_date)','date2(to_date)','Source');
End;

QUERY TO FIND THE FULL PATH FOR TOP AVAILABLE ON SERVER

Select Distinct Variable_Name  "TOP_NAME"
,Value  "PATH"
From Fnd_Env_Context Fec ,
Fnd_Concurrent_Processes Fcp,
fnd_concurrent_requests fcr
Where 1=1
And Fec.Concurrent_Process_Id=Fcp.Concurrent_Process_Id
And Fec.Concurrent_Process_Id=Fcr.Controlling_Manager
And Fec.Variable_Name ='&TOP_NAME' ; --AU_TOP FND_TOP CUSTOM_TOP ETC...

Common TNS/Database Connectivity Issuses and Possible solutions

you must check the following criteria shoulde be satisfied
If you are using the Oracle tool like TOAD, SQL Developer,Discoverer Desktop or Report Builder,Form Builder to work properly

1.    The  environmental variable (TNS_HOME) sould be set properly I mean it should point to the
 oracle home  directory where you placed your TNS file only.
Ex: %ORACLE_HOME%/network/admin

2.    The TNS Entry should be in the proper format
SID=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=DATABASE_SERVER_NAME/IP)(PORT=PORT_NUMBER))
            (CONNECT_DATA=
                (SID=DATABASE SID)
            )
        )

3.    Verify  Oracle Database version 10g or 11g is installed on your computer.

Go to Start->Run->type “cmd” and press enter

enter the command  "TNSPING" it shold return a message like

If the command returns no output: it means  database is not installed , or the PATH variable is not set.


Required Environmental Variables for Oracle

Make Sure that the Following environmental variables are added at the SYSTEM Level:

ORACLE_HOME-  C:\DEVSUITE_HOME  # The location where Database is installed

PATH - %ORACLE_HOME%\bin

TNS_ADMIN - %ORACLE_HOME%/network/admin


                                                          Common TNS Errors
                                                       
                                                TNS-03505 Failed to resolve name
Cause:
 SID is not present in the tnsnames.ora file.

 Possible Solution:
 Go to Start->Run->type “cmd” and press enter
 enter the command "tnsping SID_NAME"

Make sure that you have added the tns entry on the correct tnsnames file.
the tnsnames file should be in oracle home specified location Ex..C:\%ORACLE_HOME%\network\admin)

                              TNS-12545: Connect failed because target host or object does not exist
Causes:
1.    The server is not reachable.
2.    Wrong TNSNAMES entry.
3.    A wrong SID is mentioned in the TNSNAMES file.

 Possible Solution:
 1.    Do a telnet on the database server and the database port.

If the TNS Entry is like

ODEV=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=TEST)(PORT=1521))
(CONNECT_DATA=
(SID = ODEV)
)
)

Here, the Database server name is TEST and the port is 1521.
Go to Start->Run->type “cmd” and press enter
 enter the command "telnet TEST 1521"

The command should return a blank window.IF not , check the server name and connectivity to the server.

2.    Check the TNS Entry also.

Normally  TNS Entry should be the specified format which I mentioned at the statring of this post.
Please use the Following as a template and create your own TNS Entry and replace it with the one in the TNSNAMES.ora File.

                                    ORA-12154: TNS: Could not resolve service name
Cause:     
The problem seems related to the listner means the  client was able to connect to the listener,
but the listener rejected the connection because the Servie_name/SID in the TNS entry is not recognized.
                     
Possible Solution:
Check the TNS Entry as mentioned in the above step. If you are getting the error in a tool like toad or report builder or pl/sql developer,
form bilder do a tnsping and confirm if you get a OK message or not!!.

Script to upload data into oracle database from flat file using Sql Loader

OPTIONS (SKIP = 1)  # optional if you want to skip the first line of your file
LOAD DATA
INFILE '/path where you placed yor flat file on server/'
APPEND INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(Column1,
colmn2
enter the columns your required for your file
)


save file (.ctl) and always place to the bin folder ex: CUSTOM_TOP/BIN
REGISTER A CONCURRENT PROGRAM FOR THE SCRIPT AND SELECT EXECUTION METHOD AS "Sql Loader"
AND RUN THE PROGRAM FROM srs WINDOW