Upload Lookup Values by Using Script

Upload Lookup Values By Using Script

Advertisements

SQL Query to Get Diffrence in Values for Profile Options assigned on USER Level

I got this Query from Gareth Roberts BLOG …Very Very Useful during implementation specially if there are some custom profile options .

I used to work for a home appliances Manufacturing Organization . we used some custom profile options to setup level of security for Sales Manage, Salesman and Warehouse User. Some Time we faced issues and the and discovered that it’s wrong profile option assignment.

/* Formatted on 2009/08/19 08:41 (Formatter Plus v4.8.8) */
SELECT *
FROM (WITH prof_di AS
(SELECT ‘USER’ level_name, fu.user_name level_value,
fpo.profile_option_id, fpot.user_profile_option_name,
fpo.profile_option_name, fpov.profile_option_value
FROM fnd_user fu,
fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot
WHERE fu.user_id = fpov.level_value
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpot.LANGUAGE = ‘US’
AND fpov.level_id = 10004
AND fu.user_name = ‘SYSADMIN’), /*User Name Working Fine*/
prof_gr AS
(SELECT ‘USER’ level_name, fu.user_name level_value,
fpo.profile_option_id, fpot.user_profile_option_name,
fpo.profile_option_name, fpov.profile_option_value
FROM fnd_user fu,
fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot
WHERE fu.user_id = fpov.level_value
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.profile_option_name = fpot.profile_option_name
AND fpot.LANGUAGE = ‘US’
AND fpov.level_id = 10004
AND fu.user_name = ‘AHMADBILAL’) /*User Name Where we are facing some issue*/
SELECT pd.profile_option_id, pd.user_profile_option_name,
pd.profile_option_name, pd.profile_option_value d_value,
pg.profile_option_value g_value,
DECODE (pd.profile_option_value,
pg.profile_option_value, ‘EQUAL’,
‘DIFF’
) status
FROM prof_di pd, prof_gr pg
WHERE pd.profile_option_name = pg.profile_option_name(+)
UNION
SELECT pg.profile_option_id, pg.user_profile_option_name,
pg.profile_option_name, pd.profile_option_value d_value,
pg.profile_option_value g_value,
DECODE (pg.profile_option_value,
pd.profile_option_value, ‘EQUAL’,
‘DIFF’
) status
FROM prof_di pd, prof_gr pg
WHERE pg.profile_option_name = pd.profile_option_name(+))
WHERE status != ‘EQUAL’;

How to Migrate Forms Personalization from One Instance to Other Instance.

It’s very normal during implementation that we need to move all personalization from TEST / DEVELOPMENT to PRODUCTION there are two way (best of my Knowledge)

Method 1
Open TEST instance navigate to form where you did personalization, simultaneously open PRODUCTION instance start copy pasting….. Sounds pathetic (at least to me)
Method 2
Use FNDLOAD (purely a DBA task)
Download for a specific form
FNDLOAD / 0 Y DOWNLOAD
$FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES form_name=

Download all personalization
FNDLOAD / 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES

Upload
FNDLOAD / 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct

For more information you can visit this LINK
http://oracle.anilpassi.com/oracle-fndload-script-examples.html

How can one recover ? If one the apps password changed through FNDCPASS when application and database is running

Nice Article Written and Shared by Fouad Hasan Bandarkar

How to Turn On “About This Page” in Oracle R12 Web Forms

Set Following Profile Options as Required at User or Site

Personalize Self-Service Defn = YES

FND: Personalization Region Link Enabled = YES

Disable Self-Service Personal = NO (Only at Site)

FND: Diagnostics = Yes

Bounce the Apache Server

Login to Application you can see the About This Page Link on all web pages

By Using About This Page link you can have almost all information of technology Stack

Steps for Manual Cloning……

1) Restored the backup from the tape to the source destination.

2) Changed the ownership of all the directories.

chown -R appltest:dba *

chown -R oratest:dba * — Incase its multi-user.

3) Created a control file in the name of control.sql

In the source database … issue conmmand … alter database backup control file to trace;

We would find the trace file in udump.

Modify he contents of the control.sql to match the structure in the destination.

4) Changed the environment file to have the necessary settings and set the environment. Once you source th .env … it woulgive you the errors where it does not match and you may make the changes accordingly in various files.

.env file … please make sure that we change all the .env files. Even the ones in 8.0.6 and iAS Oracle Home along with the Appl_Top.

Also make the changes in the listener.ora, tnsnames.ora and sqlnet.ora.

Rename the folders too.

Make the changes in the .xml file.

Changes required would be in the hostname, Location, DB name, OS owners of Application and Database.

5) Source the environment file

6) Change the initSID.ora for correct control file location and bdump,cdump,udump

7) Rename the existing control and log files.

mv cntrl01.dbf cntrl01.dbf.bak

mv log01a.dbf log01a.dbf.bak

8) sqlplus “/as sysdba”

startup nomount

@control.sql

This will create the control file.

9) alter database open resetlogs;

10) lsnrctl start DBNAME

Sinc ewe have made the changes in the listener file we would not have any difficulties.

11) Run AutoConfig on the DBTIER.

12) Run the following in order to clean the nodes in the database.

The Databse would have the entries for the Source DB.

If you query: select node_name, node_id, server_id from fnd_nodes; — you would see that.

For cleaning:

sqlplus apps/apps

EXEC FND_CONC_CLONE.SETUP_CLEAN;

commit;

TO make sure that its cleaned up, you can also test with the following query:

select session_cookie_domain from icx_parameters;

This would provide no results.

Shutdown the DB Listener and DB … Start the DB and DB Listener.

Re-run AutoConfig on the DB node.

13) Run AutoConfig on the Application tier as we have made the changes on the Application side files.

14) Get the application services up and check the URL.

15) If you do face issues in getting the managers up and running … shutdown only the application and run cmclean.sql (available on metalink)

If you still face any issues … go to profile — system — query for %GSM% — Modify to NO … Save the changes and bounce the application.

The application and the Database should work fine after this.

Thanks To Mr. Fouad for sharing .

Accounting Entries of Accrue on receipt (APO) in Oracle Application?

Accounting entries for purchase orders with destinations type expense, item type expense and with accrue on receipt option is true.

Sequence of actions and accounting entries

Action

Create, Approve, Reserve PO

Accounting entries

Budget a/c DR @ PO price (base + Non Recoverable Tax calculated for the (received quantity * PO line unit price))

Action

Invoice Validation after matching

Accounting entries

Recoverable Tax calculated for the (received quantity * PO line unit price) and Non Recoverable Tax calculated for the (received quantity * PO line unit price) DR @ tax amount

Action

Create Accounting for invoice

Accounting entries

AP Expense accrual a/c DR @ PO price excluding tax and tax DR @ tax rate separately

AP Liability a/c CR @ invoice price

REC Tax and Non Recoverable Tax calculated for the (received quantity * PO line unit price) CR @ tax amount

Action

Receive the PO

Accounting entries

Receiving Inspection a/c DR @ PO price (base + Non Recoverable Tax calculated for the (received quantity * PO line unit price))

AP Expense accrual a/c CR @ PO price (base+ Non Recoverable Tax calculated for the (received quantity * PO line unit price))

Action

Deliver the PO

Accounting entries

Expense Charge a/c DR @ PO price (base + Non Recoverable Tax calculated for the (received quantity * PO line unit price))

Receiving Inspection a/c CR @ PO price (base + Non Recoverable Tax calculated for the (received quantity * PO line unit price))

Budget a/c CR @ PO price (base + Non Recoverable Tax calculated for the (received quantity * PO line unit price))

Reference

Accounting Flow against Purchase Orders, Receipts and Matched Invoices.

Authors: Manighandan Venkatraman & Asha Ravada