Install Oracle E-Business Suite Release 12 on MS Windows

In this article i will explain how to install Oracle EBS R12 in MS Windows. I am going to explain two methods 1) EBS R12 on Windows 2003 Server
2) EBS R12 on Windows XP.
I will strongly  recommend to install R12 on Windows 2003 server. It is more reliable and will provide you maximum functionality.

Oracle E-Business Suite Release 12 on Windows 2003 Server.

My Hardware & Software Specifications:  Booting and Running speed is excellent by using following hardware.
- Intel Pentium Core 2 DUO, CPU 2.0 GHz
- 4 GB of RAM
- 250 GB Hard Drive
- Windows 2003 Server with Service Pack 2

- R12 Stage  Down load from http://edelivery.oracle.com
- MKS Tool Kit   licenced  download from http://webstore.mkssoftware.com
- Visual C++ 8.0 licenced (Included in Microsoft Visual Studio 2005)

Installation Steps:
1) Install Windows 2003 Server with SP2
Make sure you have installed Network driver successfully.
2) Set ‘Computer Name’ .



- Right click on ‘My Computer’ > Properties > ‘Computer Name’ > Change
- Set ‘Computer Name’ to r12   (you can other give any name)



3) Set the domain
- Click on More
- Set a ‘Primary DNS Suffix of this Computer’ to   oracle.com    you can choose any other name (myoreacle.com)


 

4) Add a new entry in C:\windows\system32\drivers\etc\hosts as follows:



replace localhost  with   r12.oracle.com

5) From the command prompt, make sure you can do the following:  
        C:\> ping   r12.oracle.com

6) Install Visual C++ 8.0 (Which is included in Microsoft Visual Studio 2005) in ‘C:\VS8′  Directory name should not contain spaces

7) Download ‘MKS toolkit’ and install in c:\mksnt\

8- Copy  link.exe  ,   cl.exe  from  c:\vs8\vs\bin to c:\mksnt\mksnt\

9) Copy which.exe from   c:\mksnt\mksnt\ to c:\vs8\lib\

10) Copy GNUMAKE.EXE  to c:\windows\system32   

11) Restart Computer

 12) Set Up the Stage Area:
Stage Area (c:\stage12) requires a 32 GB hard disk space.  Make sure stage12 name should not contain spaces and should have read/write permissions.
Extract the zip files which have been downloaded from (http://edelivery.oracle.com). Nothing special to do since the extracted files will create the stage area directory structure by itself. You should see the following structure under ‘C:\stage12′ once you are done with the files extraction: Make sure there should not be spaces stage directory name.
- startCD
- oraAppDB
- oraApps
- oraAS
- oraDB


13) Start the installation:
Run rapidwiz from following path
C:\stage12\startCD\Disk1\rapidwiz> RapidWiz.cmd

For ‘VC++’ and ‘mks’ I have provided the following paths:
s_MSDEVdir=C:\VS8\VC
s_MKSdir=C:\mksnt\mksnt


14) Follow the wizard and sit back and relax until installation completed.

15) After installation complete open the browser and write following URL    r12.oracle.com:8000

Sample Code and Output- Run Work Flow API

To get a notification/email that looks similar to below, all you need to do is to call a PL/SQL API
Sample Below Desired Notification from API is shown below

To generate such Notification, call the API

STEP 1
RUN BELOW SQL and DO COMMIT;
xx_dev>>SET serveroutput on;
DECLARE
  n_not_id INTEGER;
BEGIN
  xx_notifications_api_pkg.send_notification(
     x_email_address       => 'PASSIA'
    ,x_user_name           => ''
    ,x_notification_api_id => n_not_id
    ,x_message_type        => 'TEXT_AND_QUERY'
    ,x_process_short_code  => 'DEMO-'
    ,x_message_subject     => 'ANIL TESTING ANOTHER ONE Customer Run Id 100329'
    ,x_message_text        => 'ANIL Customer Customers in AR on ' ||
    to_char(SYSDATE,'DD-Mon-RRRR HH24:MI'));

  xx_notifications_api_pkg.add_query(
     x_notification_api_id => n_not_id
    ,x_query_title_text    => 'List of TCA Customers Created During Last 24Hrs'
    ,x_from_clause         => 'hz_parties'
    ,x_where_clause        => 'creation_date > sysdate - 1'
    ,x_bind_values         => NULL
    ,x_column_title_1      => 'Party Number'
    ,x_column_name_1       => 'PARTY_NUMBER'
    ,x_column_title_2      => 'Creation Date'
    ,x_column_name_2       => 'CREATION_DATE'
    ,x_column_title_3      => 'Org System Ref'
    ,x_column_name_3       => 'orig_system_reference');

  xx_notifications_api_pkg.add_query(
     x_notification_api_id => n_not_id
    ,x_query_title_text    => 'Second List of TCA Parties in Last 48Hrs'
    ,x_from_clause         => 'hz_parties'
    ,x_where_clause        => 'creation_date > sysdate -2'
    ,x_bind_values         => NULL
    ,x_column_title_1      => 'Party Number'
    ,x_column_name_1       => 'PARTY_NUMBER'
    ,x_column_title_2      => 'Party Name'
    ,x_column_name_2       => 'PARTY_NAME'
    ,x_column_title_3      => 'Org System Ref'
    ,x_column_name_3       => 'orig_system_reference');
  dbms_output.put_line('n_not_id: ' || n_not_id);
COMMIT ;
END;
/

The serveroutput displays message below
n_not_id: 1000

PL/SQL procedure successfully completed.


STEP 2
R
UN BELOW THE BACKGROUND PROCESS AS BELOW
Ideally this will be scheduled to run every 15minutes or so on Production.
Hence notifications will be sent out every 15minutes by email/worklist


Step 3.
Item Key used by this wf api internally will use value in parameter x_process_short_code concatenated with Reference Number returned.
If you wish to send notification related to PO Number 1032, then pass parameter 
x_process_short_code=>'PO-Num-1032'

Issue:- If concurrent manager is not running then start this using command below
adcmctl.sh start apps appspassword




Workflow Basics

Overview:
This article will illustrate how to create or define workflow attributes, notifications, messages, roles or users, functions, processes and last but not the least, how to launch a workflow from PL/SQL. The workflow concepts are better explained using an example.

Business Requirement:
When an item is created in inventory, workflow needs to be launched and it should collect the details of the item created and sends a notification to group of users along with the details and link to master item form

Process flow: When an item is created it will create/insert a record in MTL_SYSTEM_ITEMS_B so create a database trigger on the table and launch workflow from that trigger. All you need to do is create the workflow, create the trigger, pl/sql package, roles and finally create an item in inventory.
· Open WFSTD and save as new workflow
· Create Attributes
· Create Functions
· Create Notification
· Create Messages
· Create Roles
· Create database trigger
· Create PL/SQL Package
1)Open WFSTD and save as new workflow:


 alt

Navigation: File >> Open


 alt



Click Browse then navigate to Workflow installation directory

Navigation: Workflow Installation Directory\ WF\DATA\US\WFSTD

Now Click File >Save as, Enter “ErpSchools Demo” and click OK

Right click on WFSTD and select New Item type

Enter the fields as below


Internal Name: ERP_DEMO

Display Name: ErpSchools Demo

Description: ErpSchools Demo

alt

Now you will see ErpSchools Demo icon in the Navigator

 
alt

Expand the node to see attributes, processes, notifications, functions, Events, Messages and lookup types.

alt

Double click on Process to open up the properties window as shown below

alt

Enter the fields

Internal Name: ERPSCHOOLS_PROCESS

Display Name: ErpSchools Process

Description: ErpSchools Process

 alt

alt

Double click ErpSchools Process Icon

alt

2) Create Workflow Attributes:

Navigation: Window menu > Navigator

alt

Right click on Attributes and click New Attribute

alt

Enter the fields

Internal Name: ERP_ITEM_NUMBER

Display Name: Item Number

Description: Item Number

Type: Text

 Default Value: Value Not Assigned

 
alt

Click Apply and then OK

Create one more attribute

Right click on Attributes and click New Attribute

alt

Enter the attribute fields

Internal Name: ERP_SEND_ITEM_FORM_LINK

Display Name: Send Item Form Link

Description: Send Item Form Link

Type: Form

Value: INVIDITM

 alt

Click Apply and then OK

3) Create Workflow Function:

Right click and then click on New Function

alt

Properties window will open as shown below

alt

Change/Enter the fields as below

Change Item Type to Standard from ErpSchools Demo

Select Internal Name as Start

Remaining fields will be populated automatically

 
alt

Click Apply then OK

alt

Again Right click on white space and click New Function 

alt

Change the properties as below

Item Type: Standard

Internal Name: END

alt

Click Apply and then OK

alt

Right click on white space and then click New Function

alt

Enter the fields

Internal Name: ERP_GET_DETAILS

Display Name: Get New Inventory Item Details

Description: Get New Inventory Item Details

Function Name: erpschools_demo_pkg.get_item_details

alt

Click Apply and then OK

alt

4) Create Workflow Notifications:

Right click on white space and then click New Notification

alt

Enter fields

Internal Name: ERP_SEND_ITEM_DET

Display  Name: Send Item Detials

Description: Send Item Detials

Message: Sned Item Details Message

alt

Click Apply and then OK

5) Create Workflow Messages:

Right click on Message and click New

alt

Properties window will pop up as show below

alt

Enter the fields

Internal Name: ERP_SEND_ITEM_DET_MSG

Display Name: Send Item Details Message

Description: Send Item Details Message

alt


Go to Body Tab and enter as shown below
alt

Click Apply and then OK

Navigation: Window Menu > Navigator

Select Item Form Link Attribute

alt


Drag and drop both attributes to “Send Item Details Message”

6)Create Roles:
Adhoc roles can be created through PL/SQL from database or they can be created from Applications using User Management Responsibility. If you use PL/SQL to create roles make sure you give all user names and role names in UPPER case to avoid some problems
· Script to Create a Adhoc Role
· Script to Add user to existing Adhoc Role
· Script to Remove user from existing Adhoc Role
· Using Adhoc roles in workflow notifications
· Adhoc Roles Tables
Script to Create a Adhoc Role
DECLARE
lv_role varchar2(100) := ‘ERPSCHOOLS_DEMO_ROLE’;
lv_role_desc varchar2(100) := ‘ ERPSCHOOLS_DEMO_ROLE’;
BEGIN
wf_directory.CreateAdHocRole(lv_role,
lv_role_desc,
NULL,
NULL,
‘Role Demo for erpschool users’,
‘MAILHTML’,
‘NAME1 NAME2′, –USER NAME SHOULD BE IN CAPS
NULL,
NULL,
‘ACTIVE’,
NULL);
dbms_output.put_line(‘Created Role’ ||’ ‘||lv_role);
End;
/
Script to Add user to already existing Adhoc Role
DECLARE
v_role_name varchar2(100);
v_user_name varchar2(100);
BEGIN
v_role_name := ‘ERPSCHOOLS_DEMO_ROLE’;
v_user_name := ‘NAME3′;
WF_DIRECTORY.AddUsersToAdHocRole(v_role_name, v_user_name);
–USER NAMES SHOULD BE in CAPS
END;
Script to Remove user from existing Adhoc Role
DECLARE
v_role_name varchar2(100);
v_user_name varchar2(100);
BEGIN
v_role_name := ‘ERPSCHOOLS_DEMO_ROLE’;
v_user_name := ‘NAME3′;
WF_DIRECTORY.RemoveUsersFromAdHocRole(v_role_name, v_user_name); –USER NAMES in CAPS
END;
Using Adhoc roles in workflow notifications:
Navigation: File > Load Roles from Database
Select roles you want to use and then click OK.
Open the notification properties and then navigate to node tab, select performer as the role you just created and loaded from database.
Tables:
· WF_ROLES
· WF_USER_ROLES
· WF_LOCAL_ROLES
· WF_USER_ROLE_ASSIGNMENTS
7) Launching workflow from PL/SQL:
First create a database trigger as below to call a PL/SQL procedure from which you kick off the workflow.
·         Create Database Trigger
CREATE OR REPLACE TRIGGER “ERP_SCHOOLS_DEMO_TRIGGER” AFTER INSERT ON INV.MTL_SYSTEM_ITEMS_B REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
lv_id                 NUMBER          := :NEW.inventory_item_id;
lv_item_segment1      VARCHAR2(100)   := :NEW.segment1;
lv_itemtype           VARCHAR2(80)    := :NEW.item_type;
lv_user_id            NUMBER          := -1;
lv_itemkey            VARCHAR2(10);
lv_orgid              NUMBER          :=2;
error_msg             VARCHAR2(2000);
error_code            NUMBER;
BEGIN
lv_user_id := fnd_global.user_id;
lv_orgid := fnd_global.org_id;
lv_itemkey := 1132; – This should be unique value
ERP_DEMO.LAUNCH_WORKFLOW(‘ERP_DEMO’
,lv_itemkey
,’ERPSCHOOLS_PROCESS’ –process name
,lv_id
,lv_orgid
,lv_item_segment1
);
EXCEPTION
WHEN OTHERS THEN
error_code := SQLCODE;
error_msg  := SQLERRM(SQLCODE);
RAISE_APPLICATION_ERROR(-20150,error_msg);
END;
/
·         Create PL/SQL Package to kickoff workflow
CREATE OR REPLACE PACKAGE APPS.ERP_DEMO IS
PROCEDURE LAUNCH_WORKFLOW
(
itemtype        IN VARCHAR2,
itemkey         IN VARCHAR2,
process         IN VARCHAR2,
item_id         IN NUMBER,
org_id          IN NUMBER,
item_segment1   IN VARCHAR2
);
END ERP_DEMO;
/
CREATE OR REPLACE PACKAGE BODY APPS.ERP_DEMO IS
PROCEDURE LAUNCH_WORKFLOW(
itemtype         IN VARCHAR2,
itemkey          IN VARCHAR2,
process          IN VARCHAR2,
item_id          IN NUMBER,
org_id           IN NUMBER,
item_segment1    IN VARCHAR2
)
IS
v_master_form_link varchar2(5000);
v_item_number varchar2(100);
error_code varchar2(100);
error_msg varchar2(5000);
BEGIN
v_add_item_id := ‘ ITEM_ID=”‘ || item_id || ‘”‘;
v_item_number := item_segment1;
v_master_form_link := v_master_form_link || v_add_item_id;
WF_ENGINE.Threshold := -1;
WF_ENGINE.CREATEPROCESS(itemtype, itemkey, process);
– Get the value of attribute assigned in workflow
v_master_form_link := wf_engine.getitemattrtext(
itemtype => itemtype
,itemkey  => itemkey
,aname    => ‘ERP_SEND_ITEM_FORM_LINK’);
- assign values to variables so that you can usethe attributes
v_master_form_link varchar2(5000) := v_master_form_link||’:#RESP_KEY=”INVENTORY” #APP_SHORT_NAME=”INV” ORG_MODE=”Y” ‘;
v_master_form_link := v_master_form_link || v_add_item_id;
–set the attribute values in workflow so that you can use them in notifications
WF_ENGINE.SetItemAttrText(itemtype, itemkey, ‘MASTERFORM’, v_master_form_link);
WF_ENGINE.SetItemAttrText(itemtype, itemkey, ‘ERP_ITEM_NUMBER’, item_segment1);
– start the workflow process
WF_ENGINE.STARTPROCESS(itemtype, itemkey);
EXCEPTION WHEN OTHERS THEN
error_code := SQLCODE;
error_msg  := SQLERRM(SQLCODE);
– add dbms or fnd_output messages as required
END LAUNCH_WORKFLOW;
– This procedure will just put the item number into workflow attribute ERP_ITEM_NUMBER
PROCEDURE GET_ITEM_DETAILS(
itemtype        IN VARCHAR2,
itemkey         IN VARCHAR2,
actid           IN NUMBER,
funcmode        IN VARCHAR2,
resultout       OUT NOCOPY VARCHAR2
)
IS
v_GET_ITEM_NUMBER VARCHAR2(1000);
BEGIN
SELECT SEGMENT1 INTO V_GET_ITEM_NUMBER FROM MTL_SYSTEM_ITEMS_B WHERE ROWNUM =1;
WF_ENGINE.SetItemAttrText(itemtype, itemkey, ‘ERP_ITEM_NUMBER’,v_GET_ITEM_NUMBER );
– you can use the get function as below.
–v_GET_ITEM_NUMBER := wf_engine.getitemattrtext(
–                                        itemtype => itemtype
–                                        ,itemkey  => itemkey
–                                        ,aname    => ‘X_ATTRIBUTE’);
resultout:=’COMPLETE:’||’Y';
exception when others then
dbms_output.put_line(‘Entered Exception’);
fnd_file.put_line(fnd_file.log,’Entered Exception’);
END GET_ITEM_DETAILS;
END ERP_DEMO;
/




 

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...