Interesting EBS Profiles and Env Variables

FND_MDI_TITLE_USER_DESC: To show user description on MDI window Title.
FND_CORPORATE_BRANDING_IMAGE: To Customize corporate branding image for Oracle Applications.
FND_COLOR_SCHEME:To change Java Color Scheme.
FND_LOOK_AND_FEEL:To change Java Look and Feel, to generic or to oracle.

Variables:

(

cd $VARIABLE_NAME  to go to path define in variable.

echo $APPLCSF  to see variable value

)

APPLLOG - Path location for concurrent programs log files.

APPLOUT - Path location for concurrent programs output files.

APPLCSF - Home for APPLLOG and APPLOUT subdirectories.

Migrating Discoverer Workbooks between DEV and Production

Imagine you are developing a new Workbook in Discoverer, after you are sure it works fine, you ask your DBA to move the BA and the Workbook to Production, so BA goes through well but workbook does not, because it tell you there is an error like this:
“Cannot join tables used in the workbook. Item dependency “” not found in the EUL”

what next?, besides the possible finger pointing, most of the cases this is a timestamp issue.

if you have Discoverer 9.0.4 you can review the
Chapter 3 "About copying EULs and EUL objects by exporting and importing"
Oracle® Discoverer Administrator
Administration Guide
10g (9.0.4)
Part No. B10270-01

for others version is pretty similar but you have to check the proper guide.

AME and the new Responsibilities-Roles Setup

You go to system Administrator responsibility and assign to yourself or to someone else a desired responsibility, so you think, the job was done; but surprise even the user is able to select such responsibility, it does not work ... why? this is because Oracle change the way some responsibilities can be assigned, probably it is going to be the future for all of them.

So here are the steps to assign the AME responsibilities to any user.

by the way, What is AME?
From http://www.oracle.com/: AME is a self-service web application, which lets users define business rules governing who should approve transactions that originate in other Oracle applications.
I think it stands for Approval Management Engine.

The new approach Oracle is using to assign those resp. are call Role Based Access Control o RBAC.

Be aware this could not work in you own environment, it depends on your patch level, it should be AME.B.


- Approvals Management Business Analyst 

and
- Approvals Management Administrator 

are the responsibilities to be assigned to the user.

Be sure profile AME:Installed is set to YES.

1. Log on into EBS and use resp. "User Management "
2. Click in Users function.
3. Look for the user name
4. Click on the update link or icon
5. Click on the assign roles button
6. In the LOv look for Approval%
7. Five roles should be displayed
8. Select all of them if this is the case, if not, just select what you need.
9. Now enter a justification and date for each row.
-------------------------
10. switch to responsibility Functional Administrator
11. Click on Create Grant button
12. Type a Grant Name a enter a date
13. Select grant type as 'Specific User' in the Security Context region.
14. select the user name in the Grantee field .
15. now select AME Transaction Types in the object field.
---------------------------
16. click Next.
17. select all rows in Data Context type
---------------------------
18. click Next.
19. Select AME Calling Applications in the SET field.
---------------------------
20. click Next, and review it.
21. click summit.


Reference:
- Oracle Approvals Management Implementation Guide Release 11i.
- AME.B 11i/R12 TOI Doc.


If you can see any function in User management Responsibility, it is because you need the role 'Security Administrator' , so better use SYSADMIN.

There is an error in external function HR FastFormula

well, how Fastformula works?


1. Logon using US Super User HRSM Manager.

2. Go to Other Definitions > Formula Function.

And look for the function with the problem.

Definition is the actual Database Package name and the function.

3. Describe the code:
FUNCTION get_plan_year
( -- Input values --
p_date_earned IN DATE
)
RETURN DATE
IS
--
l_out_date DATE;
--
BEGIN
select start_date
i nto l_out_date
from BEN_YR_PERD
where p_date_earned between start_date and end_date;
RETURN l_out_date;
END get_plan_year;
-----------------------------------------------------------
END amri_ff_ben;

4. To see the register parameters for that function click in the button Context Usages:

also using next query we can see the parameter in the back end:
SELECT context_id
,context_name
,data_type
,fl.meaning
FROM ff_contexts, hr_lookups fl
WHERE fl.lookup_code = data_type
AND fl.lookup_type = 'DATA_TYPE'
and context_name = 'DATE_EARNED'

Parameters button do not have anything define in this case, because there are not any user defined parameter.

5. Find the Fast Formula definition.

Total Compensation > Basic > Write Formulas > query up formula you need.

6. Press Edit button. (sample script)

/************************************************************************

FORMULA NAME: AGE_AS_OF_PLAN_YEAR

FORMULA TYPE : Age Determination Date

DESCRIPTION : The Age determination date is 01-MAR-YYYY.

*************************************************************************

Change History:Name Date Description

----------------------------------------------------------------------------------------

Initial Version

**************************************************************************

FORMULA TEXT: Logic in simple terms.*

***************************************************************************/

/*=========== DATABASE ITEM DEFAULTS BEGIN =========*/

/*=========== DATABASE ITEM DEFAULTS ENDS==========*/

/*============ INPUT VALUES DEFAULT BEGIN ===========*/

/*============== INPUT VALUES DEFAUT ENDS ==========*/

/*================= INPUTS SECTION BEGIN =============*/

/*================== INPUTS SECTION ENDS ============*/

/*================ FORMULA SECTION BEGIN ============*/

DEFAULT FOR l_plan_date is '01-JAN-1951'(date)
l_plan_date = GET_PLAN_YEAR() /*this is the call to the external function*/
return l_plan_date

/*================ FORMULA SECTION END ==============*/

7. if you change the PLSQL, then you have to compile the formula.

to find the invalid plsql use following query:

sELECT object_type, object_name, status, last_ddl_time
FROM all_objects
WHERE owner = 'APPS'
and status != 'VALID'
AND object_type LIKE 'PACKA%%'
AND object_name LIKE 'FFP_%'
--OR object_name LIKE 'FFW_%'

but do not compile using sql*plus instea use the program:

$FF_TOP/bin/FFXBCP apps/apps 0 Y %% %%

or

$FF_TOP/bin/FFXBCP apps/apps 0 Y %% %formula_group%

or

Processes and Reports -> Submit Processes and Reports and Select Bulk Compile of Formulas

good luck.

OPM: ic_tran_cmp_vw1 mapping for PO receipts

ic_tran_cmp_vw1 is a view inside Oracle Process Manufacturing, through it, you are able to see all completed transactions no matter what their source is, next is the mapping to link PORC transactions in that view with Oracle Purchasing Tables.

-----------------------------------------------------------
IC_TRAN_CMP_VW1.ITEM_ID = IC_ITEM_MST.ITEM_ID

Note that this item_id is not same as Item_id in PO and INV and OM instead link it using ic_item_mst.item_no = mtl_system_items.segment1,
-----------------------------------------------------------
IC_TRAN_CMP_VW1.LINE_ID = RCV_TRANSACTIONS.TRANSACTION_ID (where TRANSACTION_TYPE is equal to 'DELIVER')

also

IC_TRAN_CMP_VW1.LINE_ID = RCV_LOT_TRANSACTIONS.TRANSACTION_ID

Note LINE_ID could be repeated several times depending on how many lot+sublote the item was received
------------------------------------------------------------
IC_TRAN_CMP_VW1.doc_type='PORC' Purchasing source

IC_TRAN_CMP_VW1.TRANS_QTY > 0 To avoid negative transactions generate through the correction transaction in Oracle Purchasing.
------------------------------------------------------------
C_TRAN_CMP_VW1.DOC_ID = SHIPMENT_HEADER_ID on any PO table
same thing as LINE_ID
-------------------------------------------------------------

the end.

Installing Jdeveloper on win XP for OA Framework 11.5.10

1. Review installation steps OAEXT_README.txt in the ZIP distribution file.

2. Open file gs_setup.htm, see location in the below image.

3.- Configure the JDEV_USER_HOME environment variable using Windows XP or Windows 2000 .
My example: C:\installjdev\jdevhome\jdev

4.-Obtaining a Database Connection File.

• Log on into your application tier where OA framework was installed, and go to
  

$ cd $FND_TOP/secure

• Get connection file .dbc
  

5.- Place this file in the \dbc_files\secure directory.


My example: C:\installjdev\jdevhome\jdev\dbc_files\secure

6.-Assigning ToolBox Responsibilities.

connect to oracle application and as system administartor assign following responsiblities to the jdeveloper user or users.

• OA Framework ToolBox Tutorial (responsibility key is FWK_TBX_TUTORIAL).
• OA Framework ToolBox Tutorial Labs (responsibility key is FWK_TOOLBOX_TUTORIAL_LABS).

7.-Launching JDeveloper and Configuring the Database Connection and User.

create a shortcut: jdevbin\jdev\bin\jdevw.exe, e.g. C:\installjdev\jdevbin\jdev\bin\jdevw.exe

you can put it in to your desktop for easy access.

further information:





Metalink account required.
Oracle Application Framework Support Guidelines for Customers
Oracle Application Framework Support Guidelines for Customers Release 12

Oracle Application Framework Release 11i Documentation Roadmap









Oracle Application Framework Development FAQ Release 11i (11.5.10)









Oracle Application Framework Development FAQ Release 12

Oracle E-Business Suite Technology

http://blogs.oracle.com/schan/2006/12/21


from the above link:


BI Publisher (Tim Dexter)Tim is the one Oracle blogger who seems to work even harder than I do. His blog is packed with tips, screenshots, code samples, FAQs, and advice for BI Publisher users. If you use his product, this blog is a must-read.
In Depth Apps - Oracle eBusiness Suite (Gareth Roberts)Another excellent blog from an Oracle insider, with a wonderful array of useful tips, references (check out his terrific Apps Acronym Listing), and other practical advice.
Irregular Enterprise (Dennis Howlett)As an ERP industry observer, Dennis's blog is provocative, insightful, challenging, and never happy with the status quo. My kind of writer. I might not agree with everything Dennis says, but he never fails to keep my attention.
Oracle Business Intelligence Blog (Abhinav Agarwal, Gerald Bellot, Keith Laker)Interesting insights into the present and near-future of BI from Oracle insiders
Oracle Applications DBA (Aviad Elbaz)Packed with screenshots, useful tips, and other information from a DBA who clearly enjoys working with Oracle products. Great stuff.
OracleApps Epicenter (Sanjit Anand)Subtitled, "Odyssey of an OracleApps Consultant," this blog has an eclectic mix of news items, conceptual overviews of functional Apps areas, and useful tips.
Oracle Security Blog (Integrigy, Stephen Kost)Ever relevant and sometimes-provocative comments on security topics relating to Oracle products (... and yes, Stephen, lots of Oracle staff read your blog. We're not allowed to comment on most security-related topics but we're reading and thinking about your comments; keep up the good work)
ORCLville (Floyd Teter)Floyd's a key member of the OAUG Fusion Council, and -- amongst other things -- is involved in managing the E-Business Suite for NASA's Jet Propulsion Lab. He's grappling with all of the deployment and implementation questions common to Apps users, and isn't shy about sharing his thoughts and opinions.
Usable Apps Blog (Anna Wichansky)This one's pure mind-candy. Oracle's Applications User Experience group influences and shapes the look-and-feel of Oracle's products, including the E-Business Suite and our upcoming Fusion Applications, naturally. You'll get tantalizing glimpses into the future of Oracle's products through articles that are equally engaging and scholarly (not surprising, given the number of PhDs in this group). Hats off to all of these bloggers, and Happy New Year to all of our readers.

Install correct Jdeveloper version

to know the jdeveloper version you have to install, use http://hostname:port/OA_HTML/OAInfo.jsp

the go to Metalink and look for Note 416708.1

Using TraceAnalyzer instead of TKPROF

As you could know already TKPROF ($ tkprof sys=no explain=apps/) does not take into account bind variables. Therefore, you can not see those values in the output file. If you want to see them, you could use TraceAnalyzer.



1. Download and install the trace Analizae rprogram for your current instance.

2. Using it as follow
Launch: SQL*Plus
connect as APPS/****** (0r user you used to create the raw file)
Run:
SQL> @trcanlzr.sql dbname_ora_11216_SGALARZA.trc
and enjoy the output.

for some reason it only show the BIN values for just some queries, looks like only for more expensive queries.

How To Generate A SQL Trace In OA Framework For Oracle Applications

Applies to:
Oracle Applications Framework - Version: 11.5 to 11.5.10Information in this document applies to any platform.OA Framework

Goal
The steps below provide instructions on how to generate a SQL trace from Oracle Applications. An example where this can be useful is when SQL Exception messages occur in OA Framework screens e.g. oracle.jbo.SQLStmtException


Solution
Please perform the following steps to create a SQL trace of the actions done in the Framework pages:
1. Set profile 'FND: Diagnostics' to Yes at user level.
2. Login to Personal Home Page as that user and select the 'Diagnostics' icon at the top of the page.
3. Select `Set Trace Level? and click Go
4. Select the desired trace level and click Save
5. Write down the trace id number(s).
6. Perform the activity that you want to trace
7. Return to the 'Diagnostics' page.
8. Select `Set Trace Level' and click Go
9. Select 'Disable Trace' and click Go.
10. Write down the trace id number(s) if different.
11. Go to user_dump_dest for your database and collect the raw trace file(s) suffixes by the trace id number(s) you have recorded.
12. Exit Applications.

Note: you can identify the user_dump_dest directory in your environment by running the following SQL:

SQL> select name, value from v$parameter where name like 'user%';