Oracle Application 11.5.10 Installation on Linux Adv. Server

Operating System: Red Hat Enterprise Linux AS 3.0

First Node
Hardware Specification

Brand & Model: Dell Optiplex GX270
CPU: Pentium 4, Speed: 2.8GHz
HDD: 200GB
RAM: 2 GB

Required Packages:

compat-libstdc++-devel-7.3-2.96.122
libstdc++-devel-3.2.3-20
compat-gcc-7.3-2.96.122
compat-gcc-c++-7.3-2.96.122
compat-db-4.0.14-5
openmotif21-2.1.30-8
setarch-1.3-1
compat-libstdc++-7.3-2.96.122

Oracle Applications 11i (11.5.10) Installation:

Group creation
# groupadd dba

User Creation

User Name: appltest
Password: appltest
User ID: 501
Primary group: dba
Full Name: appltest
Login Shell: /bi/bash
Home Directory: /home/appltest


Stage Area Creation: (Enter the following commands)

# cd /applest
# mkdir Stage11i
# cd Stage11i
# mkdir oraDB
# mkdir oradata
# mkdir ora8i
# mkdir oraApps
# mkdir StartCD

# cd StartCD
# mkdir Disk1
# cd ..
# cd oraDB
# mkdir Disk1
# cd ..
# cd oradata
# mkdir Disk1
# mkdir Disk2
# mkdir Disk3
# mkdir Disk4
# cd ..
# cd ora8i
# mkdir Disk1
# cd ..
# cd oraApps
# mkdir Disk1
# mkdir Disk2

Oracle Applications 11i – RDBMS - Disk1
# mount /mnt/cdrom
# cd /appltest/Stage11i/oraDB/Disk1
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom


Oracle Applications 11i – Database - Disk1
# mount /mnt/cdrom
# cd /appltest/Stage11i/oradata/Disk1
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Oracle Applications 11i – Database – Disk2
# mount /mnt/cdrom
# cd /appltest/Stage11i/oradata/Disk2
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom


Oracle Applications 11i – Database – Disk3
# mount /mnt/cdrom
# cd /appltest/Stage11i/oradata/Disk3
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom


Oracle Applications 11i – Database – Disk4
# mount /mnt/cdrom
# cd /appltest/Stage11i/oradata/Disk4
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom


Oracle Applications 11i – Tools - Disk1
# mount /mnt/cdrom
# cd /appltest/Stage11i/ora8i/Disk1
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom


Oracle Applications 11i – APPL_TOP - Disk1
# mount /mnt/cdrom
# cd /appltest/Stage11i/oraApps/Disk1
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom


Oracle Applications 11i – APPL_TOP – Disk2
# mount /mnt/cdrom
# cd /appltest/Stage11i/oraApps/Disk2
# cp –R /mnt/cdrom/* .
# umount /mnt/cdrom
# eject cdrom

Download the latest Rapid Install Patch 4132885, p4132885_11i_GENERIC.zip from http://metalink.oracle.com
This patch contains the latest Rapid Install Wizard, version 11.5.10.33

Copy the p4132885_11i_GENERIC.zip file from CD to the 11i stage directory
# cp -r /mnt/cdrom/* /appltest/Stage11i/StartCD/Disk1

# unzip -o p4132885_11i_GENERIC.zip -d /appltest/Stage11i/StartCD/Disk1

Execute the following command to show the latest Rapid Install Wizard version 11.5.10.33
# cd /appltest/Stage11i/StartCD/Disk1/startCD/Disk1/rapidwiz
# sh RapidWizVersion
Version 11.5.10.33
#

Download the Patch 3006854, p3006854_9204_Linux.zip from http://metalink.oracle.com
Copy p3006854_9204_Linux.zip file to /opt/Patch
# cd /opt/Patch
# unzip p3006854_9204_Linux.zip
# cd 3006854
# sh rhel3_pre_install.sh
Patch Installed
#

Login as root
Go to startCD
# cd /appltest/Stage11i/StartCD/Disk1/startCD/Disk1/rapidwiz
# sh rapidwiz

**Provide your feedback on this post.

Enabling DFF In Oracle Standard Forms

1. Identify Descriptive fields used in a form .



Go to Help -> Diagnostic -> Examine ->Block($DESCRIPTIVE FLEXFIELD)

2. Open Descriptive flexfield segment




Go to Setup ->Flexfields ->Descriptive ->Segments


3. Enable Descriptive Flexfield.




·Define Code , name & description for new descriptive flexfield
·Un-check Freeze Flexfield Definition

4. Define Segments for a flexfield

.Define Segment Name and Attibute.
.Define Valuset if any.

5. Compile the Flexfield

.Check Freeze Flexfield Definition.
.Compile the Flexfield to see effect on standard oracle form.

6. Flexfield on form

Code Snippet for querying responsibilities and users in 11i Applications

1. During a migration project we were required to copy the responsibilities created in test instances to production. For this scenario, I have used the below query to find out the request groups and menus used by these responsibilities.

SELECT DISTINCT rtl.responsibility_name,
(SELECT DISTINCT application_name
FROM fnd_application_tl
WHERE application_id = r.application_id
AND source_lang = 'US') application,
(SELECT DISTINCT user_menu_name FROM fnd_menus_tl
WHERE menu_id = r.menu_id AND source_lang = 'US') menu, rg.request_group_name, DECODE (rule_type,'F', 'FUNCTION', 'M', 'MENU' ) exclusion_type, DECODE (rule_type, 'F', (SELECT DISTINCT user_function_name FROM applsys.fnd_form_functions_tl
WHERE function_id = fff.action_id AND source_lang = 'US'),
'M', (SELECT DISTINCT menu_name
FROM fnd_menus
WHERE menu_id = fff.action_id) )
exclusion_name
FROM fnd_responsibility_tl rtl,fnd_responsibility r, fnd_request_groups rg, applsys.fnd_resp_functions fff
WHERE rtl.responsibility_id = r.responsibility_id
AND (r.request_group_id = rg.request_group_id(+)
AND r.group_application_id = rg.application_id(+))
AND r.responsibility_id = fff.responsibility_id(+)
AND UPPER (rtl.responsibility_name) = UPPER ('&responsibility_name')
AND rtl.source_lang = 'US';

2. I use the below query to find out the responsibilities assigned to user in 11i with start and end dates.

SELECT fu.user_name, fr.responsibility_key, fur.start_date, fur.end_date
FROM apps.fnd_responsibility fr, apps.fnd_user fu, apps.fnd_user_resp_groups_direct fur WHERE fur.responsibility_id = fr.responsibility_id
AND fur.responsibility_application_id = fr.application_id
AND fur.user_id = fu.user_idORDER BY user_name

Customizations in Oracle Applications - Purchase, Inventory and Payables

Part A: Purchasing/Inventory

1. Item – Customer- Approved Supplier Trigger for PO
2. Item – Customer- Approved Supplier Trigger for RFQ
3. DFF Copy from Requisition to PO
4. DFF Copy from Requisition to RFQ
5. Quotation Workflow trigger

Part B: Accounts Payable

1.Prepayment Trigger


**Please don't implement any of the customisation's on to your application untill aware of the architecture/ process flow. These changes are done as per client's needs and may not fit into your process. This post is only for reference purpose.


Part A: Purchase/Invenotry

1. Item – Customer- Approved Supplier Trigger for PO

Business Requirement: A particular Item is approved for a particular Supplier by a particular Customer.

Customization: Customer field added in Approved Supplier List line level DFF. Trigger GS_DWE_PO_ASL_CUST_TRG_PO written.

Result: While making POs this trigger enforces that only the Item-Supplier combination which is approved by a particular Customer are chosen. If the same is not done, then it raises an application error.

TRIGGER GS_DWE_PO_ASL_CUST_TRG_PO
before insert or Update on PO_LINES_ALL
for each row
Declare

Cursor C1 is
Select c.vendor_id ASL_VENDOR_ID,
c.vendor_site_id ASL_VENDOR_SITE_ID,
c.item_id,
c.ATTRIBUTE10 as pasl_attribute,
a.VENDOR_ID PO_VENDOR_ID,
a.VENDOR_SITE_ID PO_VENDOR_SITE_ID,
a.ATTRIBUTE14 as poha_attribute
from PO_HEADERS_ALL a,
--PO_LINES_ALL b,
PO_APPROVED_SUPPLIER_LIST c
Where c.ITEM_ID = :New.ITEM_ID
And c.ATTRIBUTE_CATEGORY = 'Customer'
And a.ATTRIBUTE_CATEGORY = 'Header Details'
And a.ATTRIBUTE14 = c.ATTRIBUTE10
And a.PO_HEADER_ID = :NEW.PO_HEADER_ID;

Cur_Row C1%ROWTYPE;

Begin

Open C1;

LOOP
Fetch C1 into Cur_Row;

Exit when C1%NOTFOUND;

If (Cur_Row.ASL_VENDOR_ID <> Cur_Row.PO_VENDOR_ID)
Then
Raise_application_error(-20001,'Item-Supplier is not approved for this customer selected in DFF');
--exit;
Elsif ((Cur_Row.ASL_VENDOR_ID = Cur_Row.PO_VENDOR_ID) and (nvl(Cur_Row.ASL_VENDOR_SITE_ID,0) <> Cur_Row.PO_VENDOR_SITE_ID))
Then
Raise_application_error(-20001,'Item-Supplier Site is not approved for this customer selected in DFF');
-- exit;
Else
null;
End if;


END LOOP;
Close C1;

End;


2. Item – Customer- Approved Supplier Trigger for RFQ

Business Requirement: A particular Item is approved for a particular Supplier by a particular Customer.

Customization: Customer field added in Approved Supplier List line level DFF. Trigger gs_approved_supp_trg_rfq written.

Result: While making POs this trigger enforces that only the Item-Supplier combination which is approved by a particular Customer are chosen. If the same is not done, then it raises an application error.


TRIGGER gs_approved_supp_trg_rfq
before insert or update on PO_RFQ_VENDORS
for each row
Declare
Customer_Supplier_Error exception;
cursor c12 is

select
c.vendor_id as poasl_ven_id
,c.vendor_site_id as poasl_ven_site_id
,c.item_id as poasl_item_id
,c.attribute10 as poasl_cust_name
,a.attribute14 as poha_cust_name
,:NEW.vendor_id as porfq_ven_id
,:NEW.VENDOR_SITE_ID as porfq_ven_site_id
from
po_headers_all a,
po_lines_all b,
PO_APPROVED_SUPPLIER_LIST c,
po_rfq_vendors d
where
a.ATTRIBUTE_CATEGORY = 'Header Details'
and
c.ATTRIBUTE_CATEGORY = 'Customer'
and
a.attribute14=c.attribute10
and
b.item_id=c.item_id
and
a.po_header_id=:NEW.po_header_id
and
a.po_header_id=b.po_header_id
--and
-- d.po_header_id = :NEW.po_header_id
;


cur_row C12%ROWTYPE;

Begin

Open C12;

LOOP
Fetch C12 into cur_row;

Exit when C12%NOTFOUND;

if(cur_row.poasl_ven_id !=cur_row.porfq_ven_id) then
--Raise_application_error(-20001,'Item-Supplier is not approved for this customer selected in DFF');
Raise Customer_Supplier_Error;
elsif((cur_row.poasl_ven_id =cur_row.porfq_ven_id) and (nvl(cur_row.poasl_ven_site_id,0)!= cur_row.porfq_ven_site_id)) then
--Raise_application_error(-20001,'Item-Supplier is not approved for this customer selected in DFF');
Raise Customer_Supplier_Error;
else
null;
end if;

END LOOP;
CLOSE C12;

EXCEPTION
WHEN Customer_Supplier_Error THEN
FND_MESSAGE.set_name('PO', 'GS_INVALID_CUSTOMER_SUPPLIER');
FND_MESSAGE.set_token('MSG', 'Supplier not approved for the selected item and customer combination');
FND_MESSAGE.raise_error;

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END;


3. DFF Copy from Requisition to PO

Business Requirement: We usually capture lot of information at header and line level DFF. Some of this information is repetitive in the Purchase cycle (Requisition to PO and Requisition-RFQ-Quotation-PO) and thus should be copied automatically.

Customization: Trigger gs_dff_copy_req2po written.

Result: As soon as the PO is auto created from the requisition, the required DFF fields are copied.

TRIGGER gs_dff_copy_req2po
after insert or update
on
PO_DISTRIBUTIONS_ALL
for each row
Declare

cursor DFFCOPY is

select distinct
a.REQUISITION_HEADER_ID as req_header_id
,TRIM(a.ATTRIBUTE_CATEGORY) as req_attribute_category
,TRIM(a.ATTRIBUTE10) as req_billable_status
,TRIM(a.ATTRIBUTE11) as req_project_number
,TRIM(a.ATTRIBUTE14) as req_customer_name
,d.PO_HEADER_ID as po_header_id

from


PO_REQUISITION_HEADERS_ALL a
,PO_REQUISITION_LINES_ALL b
,PO_REQ_DISTRIBUTIONS_ALL c
,PO_HEADERS_ALL d


where
((c.DISTRIBUTION_ID=:NEW.REQ_DISTRIBUTION_ID) OR (a.SEGMENT1=:NEW.REQ_HEADER_REFERENCE_NUM))
and
c.REQUISITION_LINE_ID=b.REQUISITION_LINE_ID
and
b.REQUISITION_HEADER_ID=a.REQUISITION_HEADER_ID
and
d.PO_HEADER_ID=:NEW.PO_HEADER_ID
;


/***** Declare cursor variabeles *****/

cur_row DFFCOPY%ROWTYPE;

BEGIN
OPEN DFFCOPY;
LOOP
FETCH DFFCOPY INTO cur_row;
EXIT WHEN DFFCOPY%NOTFOUND;

update
PO_HEADERS_ALL f
set
f.ATTRIBUTE_CATEGORY=cur_row.req_attribute_category
,f.ATTRIBUTE10=cur_row.req_billable_status
,f.ATTRIBUTE11=cur_row.req_project_number
,f.ATTRIBUTE14=cur_row.req_customer_name

where
f.PO_HEADER_ID=cur_row.PO_HEADER_ID
and
f.ATTRIBUTE_CATEGORY IS NULL
and
f.ATTRIBUTE15 IS NULL
;

END LOOP;
CLOSE DFFCOPY;

END;


4. DFF Copy from Requisition to RFQ

Business Requirement: We usually capture lot of information at header and line level DFF. Some of this information is repetitive in the Purchase cycle (Requisition to PO and Requisition-RFQ-Quotation-PO) and thus should be copied automatically.

Customization: Trigger gs_dff_copy_req2rfq written.

Result: As soon as the RFQ is auto created from the requisition, the required DFF fields are copied.

CREATE OR REPLACE TRIGGER gs_dff_copy_req2rfq
after insert or update
on
PO_LINE_LOCATIONS_ALL
for each row
Declare
cursor DFFCOPY is

select distinct
PORHA.REQUISITION_HEADER_ID as req_header_id
,TRIM(PORHA.ATTRIBUTE_CATEGORY) as req_attrib_cat
,TRIM(PORHA.ATTRIBUTE10) as req_billable_status
,TRIM(PORHA.ATTRIBUTE11) as req_project_number
,TRIM(PORHA.ATTRIBUTE14) as req_customer_name
,POHA.PO_HEADER_ID as po_header_id
from
PO_REQUISITION_HEADERS_ALL PORHA
,PO_REQUISITION_LINES_ALL PORLA
,PO_REQ_DISTRIBUTIONS_ALL PORDA
,PO_HEADERS_ALL POHA
where
POHA.PO_HEADER_ID=:NEW.PO_HEADER_ID
and
PORLA.LAST_UPDATE_DATE = POHA.CREATION_DATE
and
PORHA.REQUISITION_HEADER_ID = PORLA.REQUISITION_HEADER_ID
and
POHA.type_lookup_code = 'RFQ'
and
PORLA.ON_RFQ_FLAG = 'Y'
;

/***** Declare cursor variabeles *****/

cur_row DFFCOPY%ROWTYPE;

BEGIN
OPEN DFFCOPY;
LOOP
FETCH DFFCOPY INTO cur_row;
EXIT WHEN DFFCOPY%NOTFOUND;

update
PO_HEADERS_ALL a
set
a.ATTRIBUTE_CATEGORY=cur_row.REQ_ATTRIB_CAT
,a.ATTRIBUTE10=cur_row.req_billable_status
,a.ATTRIBUTE11=cur_row.req_project_number
,a.ATTRIBUTE14=cur_row.req_customer_name
where
a.PO_HEADER_ID=cur_row.PO_HEADER_ID
and
a.ATTRIBUTE_CATEGORY IS NULL
and
a.ATTRIBUTE15 IS NULL
;

END LOOP;
CLOSE DFFCOPY;

END;

5. Quotation Workflow trigger

This trigger checks if the Quotation from which a PO is being made is approved or not, if it is not then it raises an application error and blocks the creation of the PO.

The main query which selects a particular quotation number if it has been approved is pasted below. This query uses the wf_notifications table to pick out the quotations which have been approved.

select
to_number(substr(wfn.subject,instr(wfn.subject,'(')+1,instr(wfn.subject,')')-instr(wfn.subject,'(')-1)) as wf_qt_number
from
wf_notifications wfn
,po_headers_all poha

where
wfn.MESSAGE_TYPE='QTAPPRV'
and
substr(wfn.subject,1,1)='Q'
and
wfn.MESSAGE_NAME='APPROVER_TO_REQUESTOR_COMPLETE'
and
wfn.STATUS='CLOSED'
and
to_number(substr(wfn.subject,instr(wfn.subject,'(')+1,instr(wfn.subject,')')-instr(wfn.subject,'(')-1)) in
(select
poha.segment1
from
po_headers_all poha
where
poha.TYPE_LOOKUP_CODE='QUOTATION'
and
po_header_id in (select
poha.from_header_id
from
po_headers_all poha,
po_lines_all pola
where
poha.po_header_id=pola.po_HEADER_ID
and
poha.TYPE_LOOKUP_CODE in ('STANDARD', 'BLANKET')
and
poha.FROM_TYPE_LOOKUP_CODE='QUOTATION')
)


Part B: Accounts Payable

Business Requirement:

Payment should only be made to supplier when the PO has been ‘Accepted’ by him.Advance Payment should not be more than the same mentioned in Payment Terms. Also, ability to pay more than that amount should lie with few people (user) only.

Customization: Trigger GS_PREPAYMENT written.

Result:
1. Payment can only be made once PO is accepted by Supplier.
2. Prepayment cannot be done above the amount mentioned in Payment Terms. Only specified user can pay more than the amount mentioned in payment terms.

1. Prepayment Trigger

(to be added: check if invoice amount is more than agreed advance from payment terms)

TRIGGER GS_PREPAYMENT
BEFORE INSERT OR UPDATE ON AP_INVOICES_ALL
FOR EACH ROW
DECLARE

PREPAY_INVOICE_PO_ERROR exception;
PREPAY_INVOICE_CONFIRM_ERROR exception;
PREPAY_INV_UNAUTH_USER_ERROR exception;
PREPAY_INV_BYPASS_ERROR exception;

P_PO_Header_Id number;
P_Vendor_Type PO_VENDORS.VENDOR_TYPE_LOOKUP_CODE%TYPE;
P_User_Name FND_USER.USER_NAME%TYPE;

BEGIN


Select VENDOR_TYPE_LOOKUP_CODE
Into P_Vendor_Type
From PO_VENDORS a
Where a.VENDOR_ID = :New.VENDOR_ID;

If P_Vendor_Type = 'EMPLOYEE' Then
return;
End if;

Select User_Name
Into P_User_Name
From FND_USER a
Where a.USER_ID = :New.Created_By;



If :New.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' and :New.PO_HEADER_ID is null
Then
Raise PREPAY_INVOICE_PO_ERROR;
End if;

If :New.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' and :New.PO_HEADER_ID is not null Then
Select count(1)
into P_PO_Header_Id
from po_acceptances_v a
where a.ACCEPTED_FLAG = 'Y'
and a.PO_HEADER_ID = :New.PO_HEADER_ID;

If P_PO_Header_Id <> 1 Then
If nvl(:New.Attribute1,'x') = 'Y' and P_User_Name = 'PARVEZ' Then -- Hardcode. Pls change accordingly
return;
Elsif nvl(:New.Attribute1,'x') <> 'Y' and P_User_Name <> 'PARVEZ' Then
Raise PREPAY_INVOICE_CONFIRM_ERROR;
Elsif nvl(:New.Attribute1,'x') = 'Y' and P_User_Name <> 'PARVEZ' Then
Raise PREPAY_INV_UNAUTH_USER_ERROR;
Elsif nvl(:New.Attribute1,'x') <> 'Y' and P_User_Name = 'PARVEZ' Then
Raise PREPAY_INV_BYPASS_ERROR;
End if;
Else
return;
End If;

End if;

EXCEPTION
WHEN PREPAY_INVOICE_PO_ERROR THEN
FND_MESSAGE.set_name('SQLAP', 'GS_INVALID_PREPAYMENT');
FND_MESSAGE.set_token('MSG', 'PO Number required for creating the Prepayment invoice');
FND_MESSAGE.raise_error;

WHEN PREPAY_INVOICE_CONFIRM_ERROR THEN
FND_MESSAGE.set_name('SQLAP', 'GS_PREPAYMENT_PO_NOT_CONFIRM');
FND_MESSAGE.set_token('MSG', 'PO not confirmed. Prepayment can not be processed');
FND_MESSAGE.raise_error;

WHEN PREPAY_INV_UNAUTH_USER_ERROR THEN
FND_MESSAGE.set_name('SQLAP', 'GS_INVALID_PREPAYMENT_USER');
FND_MESSAGE.set_token('MSG', 'You are unauthorized for bypassing PO confirmation');
FND_MESSAGE.raise_error;

WHEN PREPAY_INV_BYPASS_ERROR THEN
FND_MESSAGE.set_name('SQLAP', 'GS_PREPAYMENT_BYPASS_PO');
FND_MESSAGE.set_token('MSG', 'Please set Bypass PO Confirmation to Yes before proceeding');
FND_MESSAGE.raise_error;

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END GS_PREPAYMENT;