Upload Lookup Values By Using Script
Category Archives: Oracle Application Object
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
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
Script to Clear Orders Stuck in Workflow @ Releasing
If you have some order lines in booked status and the ship line activity is in error instead of ‘Notified’ then use the following script (But check on Test Environment first). Pass the line_id for one of the line from problematic order and check if this will progress the line to ‘Awaiting Shipping’ and the work flow activity to Notified status.
set serveroutput on
Declare
l_line_id NUMBER := &line_id; /* Order Line Id*/
l_org_id NUMBER; /* Organization Id*/
l_count NUMBER;
l_activity_id NUMBER;
l_result VARCHAR2(30);
Begin
OE_Standard_WF.OEOL_SELECTOR
(p_itemtype => ‘OEOL’
,p_itemkey => to_char(l_line_id) /* Order Line Id*/
,p_actid => 12345
,p_funcmode => ‘SET_CTX’
,p_result => l_result
);
select activity_id
into l_activity_id
from wf_item_activity_statuses_v
where item_type = ‘OEOL’
and activity_name = ‘SHIP_LINE’
and item_key = to_char(l_line_id)
and activity_status_code = ‘ERROR’;
wf_item_activity_status.create_status(‘OEOL’,to_char(l_line_id),l_activity_id,wf_engine.eng_notified,wf_engine.eng_null,SYSDATE,null);
commit;
End;
/
How to start the Workflow Mailer Service Using Oracle Application Manager (OAM)
1. Login to OAM
2. Navigate to Workflow
3. Click on Service Components you will find a new page with item name “Workflow Notification Mailer” and Container “Workflow Mailer Service”
4. Under column container click on Workflow Agent Listener Service
5. From drop down list at the end of the line , choose start then click on Go button
6. Once service container are activated , all service components with startup mode automatic will be up and running
How to enable multiple logins per Single user
Perform the following steps to enable the multiple logins per user id:
- Login as Workflow Web Administrator Responsibility –> Click Administration.
- Please note the user name in the “Workflow System Administrator” field.
- Logout from Oracle Applications.
- Now Login with the above mentioned user name.
- Select Workflow Web administrator Responsibility –> Click Business Events.
- Search for the Business event oracle.apps.icx.security.session.created
- Click on the icon from update column
- Set the status of the event to disable
- Retest the multiple logins per user id.
How to restrict single session per user in Oracle EBS
How to Apply Security on Form By Using Customized Profile Option and Forms Personalization.
how-to-apply-security-on-form-by-using-customized-profile-option-and-forms-personalization
By using This Document you can create your own profile options and apply security or fulfill other requirements as well
How to Create Stock Locators By using API
How to add new Attachment Category to a Form (Sale Order)
How to Exract Service Activity LOV Detail
SELECT cs_txn_billing_oetxn_all.org_id, cs_transaction_types_vl.NAME,
cs_transaction_types_vl.description,
cs_txn_billing_types.billing_type,
cs_transaction_types_vl.revision_flag,
cs_transaction_types_vl.new_cp_return_required,
cs_transaction_types_vl.new_cp_status_code,
cs_transaction_types_vl.installed_cp_return_required,
cs_transaction_types_vl.installed_status_code,
cs_txn_billing_types.txn_billing_type_id,
cs_transaction_types_vl.no_charge_flag,
cs_txn_billing_oetxn_all.order_type_id, csd_repairs.repair_line_id,
cs_txn_billing_oetxn_all.line_type_id,
cs_transaction_types_vl.line_order_category_code line_category_code,
cs_bus_process_txns.transaction_type_id
FROM csd_repairs,
csd_repair_types_b,
cs_bus_process_txns,
cs_transaction_types_vl,
cs_txn_billing_types,
cs_txn_billing_oetxn_all
WHERE csd_repair_types_b.repair_type_id = csd_repairs.repair_type_id
AND cs_bus_process_txns.business_process_id =
csd_repair_types_b.business_process_id
AND cs_transaction_types_vl.transaction_type_id =
cs_bus_process_txns.transaction_type_id
AND (TO_DATE (SYSDATE) BETWEEN NVL
(cs_bus_process_txns.start_date_active,
TO_DATE (SYSDATE)
)
AND NVL (cs_bus_process_txns.end_date_active,
TO_DATE (SYSDATE)
)
)
AND cs_transaction_types_vl.transaction_type_id =
cs_txn_billing_types.transaction_type_id
AND cs_txn_billing_types.billing_type = ‘M’
AND cs_txn_billing_oetxn_all.txn_billing_type_id =
cs_txn_billing_types.txn_billing_type_id
— AND cs_transaction_types_vl.line_order_category_code = ‘RETURN’
AND NVL (cs_transaction_types_vl.depot_repair_flag, ‘N’) = ‘Y’
ORDER BY cs_transaction_types_vl.NAME
How to Change LOV Using Forms Personalization
How to Get Detail of Locks with Object Locked
SELECT VLO.OS_USER_NAME “OS USERNAME”, VLO.ORACLE_USERNAME “DB USER”,
VP.SPID “SPID”, AO.OWNER “OWNER”, AO.OBJECT_NAME “OBJECT LOCKED”,AO.OBJECT_TYPE,
DECODE (VLO.LOCKED_MODE,
1, ‘NO LOCK’,
2, ‘ROW SHARE’,
3, ‘ROW EXCLUSIVE’,
4, ‘SHARE’,
5, ‘SHARE ROW EXCL’,
6, ‘EXCLUSIVE’,
NULL
) “MODE OF LOCK”,
VS.STATUS “CURRENT STATUS”
FROM V$LOCKED_OBJECT VLO, ALL_OBJECTS AO, V$SESSION VS, V$PROCESS VP
WHERE VLO.OBJECT_ID = AO.OBJECT_ID
AND VS.STATUS <> ‘KILLED’
AND VLO.SESSION_ID = VS.SID
AND VS.PADDR = VP.ADDR;
How to Call Customized Function in Personalization
Regenerating Forms, Libraries, and Menus by using f60gen
Some times it is necessary to regenerate forms, libraries, or menus to fix issues with them, to synchronize the generated object with the underlying database object, or after applying patch. ADADMIN can be used for same
The f60gen utility can be used to manually regenerate forms, libraries, and menus.
The syntax for the f60gen command is as
f60gen module=<source form name> userid=APPS/<APPS password> output_file=<executable form name> module_type=form batch=yes compile_all=special
For Instance I want to generate sale order forms in ONT schema using f60gen syntax would be like
OEXOEORD.fmb form:
$cd $AU_TOP/forms/US
$f60gen module= OEXOEORD.fmb module_type=form \
output_file=$ONT_TOP/forms/US/OEXOEORD.fmx userid=APPS/APPS module_type=form batch=yes compile_all=special
Note:- Before generating the form, menu, or library, locate the source and generated files using the Linux command or by using WINSCP.
How to Check Sale Order Flexfieled Setup
Run following query by connecting User APPS
SELECT id_flex_structure_code structure_code,
DECODE (dynamic_inserts_allowed_flag,
‘Y’, ‘OK: Dynamic’,
‘ERROR: No Dynamic’
) dynamic,
DECODE (freeze_flex_definition_flag,
‘Y’, ‘OK: Freezed’,
‘ERROR: No Freezed’
) freeze,
segment_num seg_number, segment_name seg_name,
DECODE (required_flag,
‘Y’, ‘OK: Required’,
‘ERROR: No required’
) required,
DECODE (fvs.flex_value_set_name,
NULL, ‘NULL’,
fvs.flex_value_set_name
) value_set,
DECODE (fvs.validation_type,
‘N’, ‘OK: No validation’,
‘ERROR: ‘ || fvs.validation_type
) VALIDATION,
DECODE (fvs.uppercase_only_flag,
‘N’, ‘OK: No’,
‘ERROR: Uppercase Only’
) uppercase_only,
DECODE (alphanumeric_allowed_flag,
‘Y’, ‘OK: Allowed’,
‘ERROR: Not Allowed’
) alphanumeric,
DECODE (numeric_mode_enabled_flag,
‘N’, ‘OK:Not justified’,
‘ERROR: Justified’
) right_justify,
DECODE (format_type,
‘C’, ‘OK: Char’,
‘ERROR: ‘ || format_type
) format_type
FROM fnd_id_flex_structures_vl ffst,
fnd_id_flex_segments_vl ffsg,
fnd_flex_value_sets fvs
WHERE ffst.application_id = 401
AND ffst.id_flex_code = ‘MKTS’
AND ffst.enabled_flag = ‘Y’
AND ffst.application_id = ffsg.application_id
AND ffst.id_flex_code = ffsg.id_flex_code
AND ffst.id_flex_num = ffsg.id_flex_num
AND ffsg.enabled_flag = ‘Y’
AND fvs.flex_value_set_id(+) = ffsg.flex_value_set_id;
Oracle Application World 