Oracle In Me

OAF Basic points

2011-03-29T00:16:00.000-07:00

Development Hierarchy

Workspace (JWS)
Java Project (JPR)

Server side components
BC4J Package
Application Module (AM)
View Object (VO)
Entity Object (EO)

Webui side components
Page
Region -- Controller for the region
Item

Ignore Pricing for Order Lines-Custom Hook

2011-03-22T07:39:00.000-07:00

This is an enhancement to Oracle Advanced Pricing functionality and is released as a part of 11.5.10 and R12 releases. This functionality can be used to improve the performance of the system by avoiding lines from going through pricing engin if not required.

The Ignore Pricing custom hook provides users an opportunity to inform pricing engine to ignore such lines that could result in substantial performance gain

Implementing QP_custom_ignore.Ignore_itemline_for_pricing
If you intend to use ignore_itemline_for_pricing you must create a package body for qp_custom_ignore with the procedure ignore_itemline_for_pricing. Refer the package specification for further information on the package

Patches delivering the QP_CUSTOM_IGNORE.ignore_itemline_for_pricing functionality
Release 11.5.10 – Please apply patch 8203943
Release 12.0 - Please apply patch 8266809:R12.QP.A

How to incorporate the logic of viewing the concurrent request output from the custom form?

2010-10-18T07:52:00.000-07:00

If the requirement is to incorporate the logic of viewing the concurrent request output from the custom form instead of viewing the output through standard view request form, apply the below approach.

1. Attach FNDCONC.pll file to the custom form.
2. In the WHEN-BUTTON-PRESS trigger of the object in the custom form call the following package
EDITOR_PKG.REPORT(req_id, save_output_flag);

Vendors with no Invoices

2010-10-05T08:35:00.000-07:00

select segment1 VENDOR#,

vendor_name NAME,
vendor_site_code SITE_CODE,
end_date_active VEND_INACTIVE,
inactive_date SITE_INACTIVE
from po_vendors vend,
po_vendor_sites_all sites,
ap_invoices_all inv
where vend.vendor_id = sites.vendor_id
and sites.vendor_site_id = inv.vendor_site_id(+)
and inv.vendor_site_id is null;

Fetch Profile values at different levels

2010-10-05T08:09:00.000-07:00

This query will give the values of the profile option set at different level. Use the profile option name or user profile option name which you see in the front end.

SELECT user_profile_option_name,SUBSTR(e.profile_option_name,1,25) PROFILE
,DECODE(a.level_id,10001,'Site',10002,'Application',10003,'Resp',10004,'User') "Level"
,DECODE(a.level_id,10001,'Site',10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue
,NVL(a.profile_option_value,'Is Null') Value
,SUBSTR(a.last_update_date,1,25) UPDATED_DATE
FROM fnd_profile_option_values a
, fnd_responsibility_tl b
, fnd_application c
,fnd_user d
, fnd_profile_options e
, fnd_profile_options_tl f
WHERE 1=1
AND f.user_profile_option_name in ('&User_Profile_Name')
AND e.profile_option_id = a.profile_option_id AND a.level_value = b.responsibility_id (+)
AND a.level_value = c.application_id (+) AND a.level_value = d.user_id (+)
AND f.PROFILE_OPTION_NAME(+)=e.profile_option_name
ORDER BY e.profile_option_name;

Reference

Adding New Processing constraint in Order Management

2010-09-29T05:34:00.000-07:00

Add New business constraint in the Processing Constraint form (Setup : Rules : Security)

Entity : Choose the entity on which the constraint needs to be applied.
Constraints Block : Define the action or the constraint.
Condition Block : Define the condition upon which the Constraint needs to be fired.
Applicable To Block : Define the responsibilities to which the constrain is applicable.

Validation Template : New template needs to be defined in the Validation template form before attaching the same to the Processing constraint. (Setup : Rules : Security)

Validation Template Type can be of 3 types : API, Workflow or Table
1. API - Type the custom PLSQL package in the Package field and the procedure within the package in the procedure field. The procedure needs to be declared as below

Procedure xx_procedure_name ( p_application_id IN NUMBER,

p_entity_short_name in VARCHAR2,
p_validation_entity_short_name in VARCHAR2,
p_validation_tmplt_short_name in VARCHAR2,
p_record_set_tmplt_short_name in VARCHAR2,
p_scope in VARCHAR2,
p_result OUT NOCOPY NUMBER ) IS

All the IN parameters are passed by the system internally. The OUT parameter accepts two results 1 (TRUE) or 0 (FALSE). Based on the result from the validation Template, the condition is true or false.

2. Workflow : Enter the Item Type, Process Name, Status and the Result expected for the condition to be TRUE.

3. Table Type : Specify the Column Name, the Validation Operator and the string to which the column value needs to be compared to.

Wrapping PL/SQL Source Code

2010-07-27T03:25:00.000-07:00

Wrapping is the process of hiding PL/SQL source code. Wrapping helps to protect your source code from business competitors and others who might misuse it.

You can wrap PL/SQL source code with either the wrap utility or DBMS_DDL subprograms.

Guidelines for Wrapping
Wrap only the body of a package or object type, not the specification
Wrap code only after you have finished editing it.
Wrapped code cannot be unwrapped. To change wrapped PL/SQL code, edit the original source file and then wrap it again.

Limitations of Wrapping
Wrapping does not hide the source code for triggers
Wrapping is not a secure method for hiding passwords
Wrapping does not detect syntax or semantic errors

1. To run the wrap utility
wrap iname=input_file [ oname=output_file ]

2. DBMS_DDL.WRAP

Generating report using Java Concurrent Program

2010-06-25T03:11:00.000-07:00

While browsing through a standard Cocurrent program, found a interesting and unusual way of generating report using Java Concurrent program.

Program Name : CLE: AP Purchasing Ledger Report
Short Name : CLEF224PVR
Java code called : JCP4XDODataEngine.java
Data template attached in Data Definition : CLEF224PVR.xml
RTF Template representing the design : CLEF224PVR.rtf

The java program calls the data template which has the main SQL query. This file is a xml file and it internally also refers to database package. Later this is processed with RTF template to generate the output. Besides check out the java program to know how to write into log file and access concurrent program parameters and other context values.

How to create a VPD (Virtual Private Database) Policy on a table or view in 10g

2010-06-23T08:37:00.000-07:00

Standard Package DBMS_RLS can be used to add, delete, enable, disable, refresh VPD policies.

dbms_rls.add_policy('APPS','PO_HEADERS_V','accesscontrol_PO_HEADERS','system','f_policy_po,sec_relevant_cols=>'ORDER_AMOUNT',sec_relevant_cols_opt => dbms_rls.ALL_ROWS);

Parameter 1 - object owner
parameter 2 - Object Table/ View Name
Parameter 3 - Policy Name
parameter 4 - schema of the policy function
Parameter 5 - database function which returns a where clause as string (the function will have the logic to restrict the records to be fetched)
sec_relevant_cols - the column of which the data is restricted from viewing to the user
sec_relevant_cols - setting it to dbms_rls.ALL_ROWS would display all the rows to a user with blank data shown for the restricted column for the records which are non viewable.

Free Java Book

2010-06-18T07:36:00.001-07:00

Click here to get free book on Java

How to add a column to LOV in OA Framework?

2010-06-18T07:24:00.000-07:00

A. You extend the base view object, and add additional column to this view object's query
B. Using personalizations, add a new field to the specific LOV Page. This can be done by using "create new item" in OA Framework Personalizations.
C. Map the new field that was created above to view object column, by specifying the source view object and souce column.

To know more how to Extend VO in OAF click here

How to remove substitution/extension to a VO object in OAF

2010-06-18T07:16:00.000-07:00

There are three ways, but firstly you must understand the basics of how Oracle stores OA Framework substitutions. When you load the substitutions using jpxImporter, Oracle creates a site level personalization, which gets stored in the database. Now to rollback/remove/disable viewobject substitutions
Option A. Delete personalization, using jdr_util.delete_documents
Alternately use "Functional Administrator" responsibility to delete the personalization
Option B. Disable personalization that corresponds to substitution, again using Functional Administrator
Option C. Set profile "Disable personalization" to Yes at site level

The common thing about all three options above is that they all pertain to removal of personalizations that were created as result of view object substitution in OA Framework.

How to check standard VO Object has any extension?

2010-06-16T08:46:00.000-07:00

Use the following query to find if there is any extension to a given VO Object. Give VO name as parameter to the query.

SELECT JA.* FROM JDR_PATHS JP, JDR_ATTRIBUTES JA
WHERE JP.PATH_DOCID = JA.ATT_COMP_DOCID
AND JP.PATH_NAME = 'VO_OBJECT_NAME'

In the attribute name column against customize column you would find the extensions.

Virtual Private Database

2010-06-14T05:18:00.000-07:00

Learn VPD through following links

10g
11g

How to extend OAF Controller

2010-05-12T03:20:00.000-07:00

Steps to extend controller object to customize the same. 1. Open the controller class after decoding the same in Jdev.
2. Create a new class file by righclicking on the project.
3. Mention the name of the new extended class in Name. The new extended class package in the package. And the original controller class file package in the Extends.
4. After the custom logic is added in the newly extended controller, move the java file to the server, compile the file to generate the class file for the same.
javac .java
5. Bounce the server.
6. Unlike VO and EO extensions, CO extension will not be called unless the same is mentioned in the OAF personalization. Enable the personalization.
7. Select "Complete View" and "Expand All" Option in personalization page.
8. Mention the extended controller file name in the personalization link of the component. Test now if the extended logic is reflected.

Kool Technology Syntax link

2010-05-07T07:45:00.000-07:00

Please refer to this cool link which briefs though syntaz accross various languages.

XML Publisher - Developing reports printed on Pre-Printed Stationary

2010-04-28T23:06:00.000-07:00

Click on this beautiful article by Mr Darshan Bhavsar, explaining how to develop XML Publisher Reports to be printed on Pre-Printed Stationary.

Oracle Apps Query - List of Users to whom a given responsibility is assigned

2010-04-22T08:14:00.000-07:00

Select fu.user_name, fu.start_date user_start_date,fu.end_date user_end_date, fr.responsibility_name, fur.start_date resp_start_date,fur.end_date resp_end_date from FND_USER_RESP_GROUPS_ALL fur, fnd_user fu, fnd_responsibility_vl fr
where sysdate between fur.start_date and nvl(fur.end_date,sysdate)
and sysdate between fu.start_date and nvl(fu.end_date,sysdate)
and fu.user_id = fur.user_id
and fur.responsibility_id = fr.responsibility_id
and fur.responsibility_application_id = fr.application_id
and upper(fr.responsibility_name) = :resp_name

Oracle Apps Query - List the Employees assigned to a given organization

2010-04-22T07:59:00.000-07:00

Select ppf.EMPLOYEE_NUMBER, ppt.USER_PERSON_TYPE, ppf.FIRST_NAME, ppf.LAST_NAME, ppf.sex, ppf.START_DATE, ppf.ORIGINAL_DATE_OF_HIRE , ppf.EMAIL_ADDRESS, GSB.NAME SET_OF_BOOK_NAME, GCC.CONCATENATED_SEGMENTS EXPENSE_REPORT_COSTING, pa.STYLE ADDRESS_STYLE, pa.ADDRESS_LINE1, pa.ADDRESS_TYPE, pa.COUNTRY, pa.POSTAL_CODE , pa.REGION_1, pa.REGION_2, pa.TOWN_OR_CITY, ppo.NAME ORGANIZATION_NAME, HL.LOCATION_CODE, HL.DESCRIPTION LOCATION_DESCRIPTION, PJ.NAME JOB_NAME, PPG.GROUP_NAME PEOPLE_GROUP_NAME , PPA.EMPLOYMENT_CATEGORY,PPA.assignment_category, ppf2.full_name DIRECT_MANAGER
from PER_ALL_PEOPLE_f ppf
, PER_ALL_ASSIGNMENTS_f ppa
, PER_ALL_ORGANIZATION_UNITS ppo
, PER_PERSON_TYPES ppt
, GL_SETS_OF_BOOKS GSB
, PER_ADDRESSES PA
, PER_JOBS PJ
, PAY_PEOPLE_GROUPS PPG
, HR_LOCATIONS HL
, PER_ALL_PEOPLE_f ppf2
, GL_CODE_COMBINATIONS_KFV GCC
where ppa.person_id = ppf.person_id
and ppf2.person_id(+) = ppa.supervisor_id
and sysdate between ppf2.EFFECTIVE_START_DATE(+) and nvl(ppf2.EFFECTIVE_END_DATE(+),sysdate)
AND GCC.CODE_COMBINATION_ID(+) = PPA.DEFAULT_CODE_COMB_ID
and ppt.PERSON_TYPE_ID = ppf.PERSON_TYPE_ID
and sysdate between ppf.EFFECTIVE_START_DATE and nvl(ppf.EFFECTIVE_END_DATE,sysdate)
and sysdate between ppa.EFFECTIVE_START_DATE and nvl(ppa.EFFECTIVE_END_DATE,sysdate)
and ppo.organization_id(+) = ppa.organization_id
and gsb.set_of_books_id(+) = ppa.set_of_books_id
and pa.person_id = ppf.person_id
AND PPG.PEOPLE_GROUP_ID = PPA.PEOPLE_GROUP_ID
AND PPA.LOCATION_ID = HL.LOCATION_ID(+)
AND PPA.JOB_ID = PJ.JOB_ID(+)
AND ppo.name in (:org_name);

Oracle Apps Query - Lists the Responsibilities to which a CP is attached

2010-04-22T07:42:00.000-07:00

--Lists the responsibilities to which a CP is attached
SELECT RESPONSIBILITY_NAME FROM FND_RESPONSIBILITY_VL FR, FND_REQUEST_GROUPS FRG
WHERE FR.REQUEST_GROUP_ID = FRG.REQUEST_GROUP_ID
AND FR.REQUEST_GROUP_ID IN (SELECT REQUEST_GROUP_ID FROM FND_REQUEST_GROUP_UNITS FRU, FND_CONCURRENT_PROGRAMS_VL FC
WHERE FRU.REQUEST_UNIT_ID =FC.CONCURRENT_PROGRAM_ID
AND FC.USER_CONCURRENT_PROGRAM_NAME = :ConProgram_Name)

Workflow Notification Template Customization

2010-03-25T08:23:00.000-07:00

Item Key : WFMAIL
Item Key Name : System mailer
Purpose : Message templates which will be used by other workflow notifications.

Create a custom version of the Standard System Mailer Workflow

1. Open the System: Mailer
2. Save it as XXSYSML
3. Open its wft in textpad and search and repace WFMAIL by XXSYSML
4. Now open this XXSYSML.wft file and change the Name of Workflow from "System: Mailer" to "XX System: Mailer"
5. Now you can modify the templates within this workflow as desired.
6. Now, go back to your PO Approval Workflow and attach a new attribute of name for FYI, #WFM_OPEN_MAIL_FYI
7. Make the value of this attribute point to custom version of "System: Mailer" template

Setting Up Notification Mailers

2010-03-19T03:51:00.000-07:00

Please click here for Setting Up Notification Mailers

How does one enable trace for all actions that occur for a user?

2010-02-25T06:15:00.000-08:00

The instructions for enabling the event trace follow:
a. First enable trace for a specific user:
- Bring up the Core application - System Administrator responsibility
- Move to Profile/System
- Check off the USER box - and enter your username that you are using when getting the error
- Search on the following profile option - 'Initialization SQL Statement - Custom'

Please set this at the user level with the following string:
BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET
TRACEFILE_IDENTIFIER='||''''|| '4269824.999' || '''' || ' EVENTS =' || '''' || ' 10046 TRACE NAME
CONTEXT FOREVER, LEVEL 12 '|| ''''); END;

(Cut and paste the above sql statement into the User Level profile option: "Initialization SQL - Custom")
This must be one complete line of text - so may be best to copy the string into notepad prior to putting it into the profile.

b. Once the profile is set, please change responsibilities and directly go and reproduce the problem.
To locate the trace file, use the following sql in sqlplus:
SQL> select name, value from v$parameter where name like 'user_dump_dest';

c. Note that a similar technique can be used for enabling trace with binds for a user in SQL*Plus. The commands would look like the following:
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='577' EVENTS ='10046 TRACE NAME CONTEXT FOREVER,LEVEL 12';
SQL> select 'x' from dual; -- or any SQL that you want to trace
SQL> alter session set sql_trace = false;

Embed Oracle Applications Framework regions in a notification message

2010-02-25T05:39:00.000-08:00

If you are using Oracle Applications and you have Oracle Applications Framework set up in Oracle JDeveloper for custom development, you can embed Oracle Applications Framework regions in a notification message. To embed a region, define a message attribute whose value is a Java Server Page (JSP) call that references the region.

The message attribute representing the region must be of type document and must have a source of Send. You can assign the attribute any appropriate internal name, display name, and description.

Specify the value for the attribute in the following format:
"JSP:/OA_HTML/OA.jsp?OAFunc="

Click here to know further

Useful list of scripts for OA Framework

2010-02-17T06:20:00.000-08:00

This document helps the developer to get the scripts for the Apps Customization development. This will have all the OAFramework related components download from the server and upload the components from the laptop.

Click here to know more