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, Sales Man 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

Mass Feature Available in Oracle Purchasing R12

Click Link To Download

How to Get Approval Group assigned to specific Job and Position in Oracle Purchasing

How to Get Approval Group assigned to specific Job and Position in Oracle Purchasing

Remittance Advice by Email to Supplier by Using UTL_SMTP

Click on Link to Read Article

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 Get Supplier Bank Detail In R12

In Release 12, Payables Supplier Bank Information is stored somewhere else instead of PO_VENDORS table as in 11i. After going thru the guides, it seems that the bank information (such as bank account numbers, etc) for suppliers may be in TCA or in Cash Management Bank Model. Please provide the tables names where bank account information and other bank related information is stored?

By Using Following Queries you can get Required Information on Basis on Supplier Site Name

/* For Supplier Bank Account Number Use This One */

SELECT bank_account_name, bank_account_num, branch_id

FROM iby_ext_bank_accounts

WHERE ext_bank_account_id IN (

SELECT ext_bank_account_id

FROM iby_account_owners

WHERE account_owner_party_id IN (

SELECT party_id

FROM hz_party_sites

WHERE party_site_name LIKE

‘%UPC%’))

/* USE THIS QUERY TO GET BANK NAME AND BANK BRANCH NAME OF SUPPLIER BANK*/

SELECT BANKORGPROFILE.HOME_COUNTRY BANK_HOME_COUNTRY,

BANKORGPROFILE.PARTY_ID BANK_PARTY_ID,

BANKORGPROFILE.ORGANIZATION_NAME BANK_NAME,

BANKORGPROFILE.BANK_OR_BRANCH_NUMBER BANK_NUMBER,

BRANCHPARTY.PARTY_ID BRANCH_PARTY_ID,

BRANCHPARTY.PARTY_NAME BANK_BRANCH_NAME,

BRANCHPARTY.PARTY_ID

FROM HZ_ORGANIZATION_PROFILES BANKORGPROFILE,

HZ_CODE_ASSIGNMENTS BANKCA,

HZ_PARTIES BRANCHPARTY,

HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE,

HZ_CODE_ASSIGNMENTS BRANCHCA,

HZ_RELATIONSHIPS BRREL,

HZ_CODE_ASSIGNMENTS BRANCHTYPECA,

HZ_CONTACT_POINTS BRANCHCP,

HZ_CONTACT_POINTS EDICP

WHERE SYSDATE BETWEEN TRUNC (BANKORGPROFILE.EFFECTIVE_START_DATE)

AND NVL (TRUNC (BANKORGPROFILE.EFFECTIVE_END_DATE),

SYSDATE + 1

)

AND BANKCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BANKCA.CLASS_CODE IN (‘BANK’, ‘CLEARINGHOUSE’)

AND BANKCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BANKCA.STATUS = ‘A’ OR BANKCA.STATUS IS NULL)

AND BANKCA.OWNER_TABLE_ID = BANKORGPROFILE.PARTY_ID

AND BRANCHPARTY.PARTY_TYPE = ‘ORGANIZATION’

AND BRANCHPARTY.STATUS = ‘A’

AND BRANCHORGPROFILE.PARTY_ID = BRANCHPARTY.PARTY_ID

AND SYSDATE BETWEEN TRUNC (BRANCHORGPROFILE.EFFECTIVE_START_DATE)

AND NVL (TRUNC (BRANCHORGPROFILE.EFFECTIVE_END_DATE),

SYSDATE + 1

)

AND BRANCHCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BRANCHCA.CLASS_CODE IN (‘BANK_BRANCH’, ‘CLEARINGHOUSE_BRANCH’)

AND BRANCHCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BRANCHCA.STATUS = ‘A’ OR BRANCHCA.STATUS IS NULL)

AND BRANCHCA.OWNER_TABLE_ID = BRANCHPARTY.PARTY_ID

AND BANKORGPROFILE.PARTY_ID = BRREL.OBJECT_ID

AND BRREL.RELATIONSHIP_TYPE = ‘BANK_AND_BRANCH’

AND BRREL.RELATIONSHIP_CODE = ‘BRANCH_OF’

AND BRREL.STATUS = ‘A’

AND BRREL.SUBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRREL.SUBJECT_TYPE = ‘ORGANIZATION’

AND BRREL.OBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRREL.OBJECT_TYPE = ‘ORGANIZATION’

AND BRREL.SUBJECT_ID = BRANCHPARTY.PARTY_ID

AND BRANCHTYPECA.CLASS_CATEGORY(+) = ‘BANK_BRANCH_TYPE’

AND BRANCHTYPECA.PRIMARY_FLAG(+) = ‘Y’

AND BRANCHTYPECA.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND BRANCHTYPECA.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND BRANCHTYPECA.STATUS(+) = ‘A’

AND BRANCHCP.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND BRANCHCP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND BRANCHCP.CONTACT_POINT_TYPE(+) = ‘EFT’

AND BRANCHCP.STATUS(+) = ‘A’

AND EDICP.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND EDICP.OWNER_TABLE_ID(+) = BRANCHPARTY.PARTY_ID

AND EDICP.CONTACT_POINT_TYPE(+) = ‘EDI’

AND EDICP.STATUS(+) = ‘A’

AND BRANCHCA.OWNER_TABLE_ID = :IBY_BRANCH_ID /*USER BRANCH ID FROM ABOVE QUERY*/

If anyone have a better solution then please update

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

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 .

Period Close Checklist In Oracle Account Receivables

Complete manual payments and adjustments for the period (including cleanup of ‘Out of Balance’ batches)

Check and Complete final Receivables Interface from Order Management

Complete manual invoices, DEBIT Memos and credit memos

Run your final Auto Invoice process

Correct ALL Auto Invoice rejects and re-run

Clear all receipts from Cash Management

Apply receipts against Invoices and Debit Memos

Review the Unapplied Receipts Register

Reconcile receipts

Apply Credit memo to Debit Memo and Invoices

Approve or reject ALL Pending Adjustments

Complete or delete ALL ‘Incomplete’ invoices

Complete or delete ALL ‘Incomplete’ credit memos

Correct and post ALL open Lockbox batches (If Lock Box Implemented)

Print Dunning (Reminder) Letters (Optional)

Run Journal Entries Reports

Review Journal Entries reports for ‘unusual’ accounts and correct as needed

Run Period End Reports

Balance to the Aged Trial Balance

Post to the General Ledger using the period start date through the period end date – verify that ALL steps completed successfully.

Close the period – verify that there are no items in error

Open the next period and the next Future period

Post Journal Entries (In General Ledger)

Balance to the General Ledger (for the key accounts)

Reference

Why Don’t My AR and GL Numbers MATCH BY Cathy Cakebread

Metalink

Oracle Account Receivable user Guide

e business Suite Diagnostics Period / Year End Close Note:402237.1

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 enable multiple logins per Single user

Perform the following steps to enable the multiple logins per user id:

1. Login as Workflow Web Administrator Responsibility –> Click Administration.
2. Please note the user name in the “Workflow System Administrator” field.
3. Logout from Oracle Applications.
4. Now Login with the above mentioned user name.
5. Select Workflow Web administrator Responsibility –> Click Business Events.
6. Search for the Business event oracle.apps.icx.security.session.created
7. Click on the icon from update column
8. Set the status of the event to disable
9. Retest the multiple logins per user id.

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 add new Attachment Category to a Form (Sale Order)

How to add new Attachment Category to a Form (Sale Order)

Add Responsibility to your USER without having Application System Administrator Rights

By following procedure you can add responsibility to your USER with out having Application System Administrator Rights

begin
fnd_user_pkg.addresp(
‘&User_Name’, /*Application User Name */
‘&Responsablity_Application_Short_Name’, /*get from Query Below */
‘&Responsibility_Key’,/*get from Query Below */
‘&Security_Group’, /* Most of cases it is ‘STANDARD’ so you can hard code it */
‘&Description’, /* Any comments you want */
‘&Start_Date’, /* Sysdate From Today */
‘&End_Date’ ); /* Sysdate + 365 Rights for Next One Year*/
commit;

dbms_output.put_line(‘Responsibility Added Successfully’);
exception
when others then
dbms_output.put_line(‘ Responsibility is not added due to ‘ || SQLCODE || substr(SQLERRM, 1, 100));
Rollback;

end;

To get value for ‘Responsablity_Application_Short_Name’ and ‘Responsibility_Key’ Parameters you need to run following sql Statement by Using APPS User Name

SELECT FAV.APPLICATION_SHORT_NAME, FAV.APPLICATION_NAME,FRV.RESPONSIBILITY_KEY, FRV.RESPONSIBILITY_NAME

FROM FND_APPLICATION_VL FAV, FND_RESPONSIBILITY_VL FRV
WHERE FRV.APPLICATION_ID=FAV.APPLICATION_ID
ORDER BY FRV.RESPONSIBILITY_NAME

@Advanced Supply Chain Planner

EXAMPLE CODE 

To add Responsibility of @Advanced Supply Chain Planner to User Ahmad Bilal

BEGIN
fnd_user_pkg.addresp (‘AHMADBILAL’,
‘MSC’,
‘ADV_SUPPLY_CHAIN_PLANNING’,
‘STANDARD’,
‘Test By Script’,
SYSDATE,
SYSDATE + 100
);
COMMIT;
DBMS_OUTPUT.put_line (‘Responsibility Added Successfully’);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (   ‘ Responsibility is not added due to ‘
|| SQLCODE
|| SUBSTR (SQLERRM, 1, 100)
);
ROLLBACK;
END;

Supply Chain Footprint in Oracle Application

1. Supply Chain Planning

MRP/Supply Chain Planning

Demand Planning

Advanced Supply Chain Planning

Constraint and Inventory Optimization

Global Order Promising

Manufacturing Scheduling

Inventory Optimization

Collaborative Planning

Transportation Planning

Supply Chain Intelligence as an independent module

2. Order Management

Order Management

Configurator

iStore

TeleSales

Receivables

Advanced Pricing

Quoting & Proposals

Trade Management

Release Management

Sales Contracts

3. Procurement

Purchasing

Payables

iProcurement

Sourcing

iSupplier Portal

Procurement Contracts

1. Manufacturing

Discrete Manufacturing

- Project Manufacturing

- Flow Manufacturing

- Project Contracts

- Shop Floor Management

Process Manufacturing

1. Logistics

Inventory Management

Mobile Supply Chain Apps

Warehouse Management

Transportation Execution

6. Service

Tele Service

Depot Repair

Field Service

- Spares Management

- Advanced Scheduler

- Mobile Field Service

iSupport

Service Contracts

7. Maintenance Management

Enterprise Asset Management

Property Manager

What is Trading Community Architecture in Oracle Application 11i

Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications. These applications, as well as TCA itself, provide user interfaces, batch data entry functionality, and other features for you to view, create, and update Registry information (Source Overview Oracle® Trading Community Architecture User Guide)

The parties in TCA could be one of following four types:

1. Organization e.g. Dawlance
2. Person e.g. Ahmad Bilal
3. Group e.g. IEEE Consortium
4. Relationship e.g. Ahmad Bilal at Dawlance.

Following are main components of Oracle TCA

1. Contacts
2. Locations
3. Party Layer
   1. Sites
   2. Relationships
4. Account Layer
5. Customer Accounts

Main Tables in TCA

1. HZ_PARTIES
2. HZ_LOCATIONS
3. HZ_PARTY_SITES
4. HZ_CUST_ACCT_SITES_ALL
5. HZ_CUST_SITE_USES_ALL
6. HZ_CUST_CONTACT_POINTS

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.