TIPS OF THE WEEK: Product Development Services team
Tips of the Week:
Description
Published by
Week
Important table informations
1. TO FIND APPLICATION DETAILS===============================
This will provide you application id along with shortname and base path of it:
SELECT * FROM fnd_application;
2. TO FIND PRODUCT INSTALLATION DETAILS========================================
SELECT application_id, product_version, status, patch_level FROM fnd_product_installations;
Note: Here application_id would should be id as per obtained from query no. 1, product_version could be ‘12.0.0’, STATUS, where “I” means installed, “L” means custom, “N” means not installed, and “S” means installed as shared product, and patch_level could be 'R12…..'
3. TO FIND THE TABLE DETAILS=============================
Instead of using fnd_tables, change to query to use all_tables
SELECT application_id, table_id, table_name, user_table_name, description FROM fnd_tables WHERE table_name LIKE UPPER('%&table_name%');
Note: Enter the table_name for which you want to find out details. You can put application_id to filter tables.
4. TO FIND TABLE COLUMNS=========================
Instead of fnd_columns modify the query to point to all_tab_columns
Make sure to enter the table name in CAPS only:
SELECT application_id, table_id, column_id, column_name, user_column_name, column_sequence, column_type, width, description FROM fnd_columns WHERE table_id = (SELECT table_id FROM fnd_tables WHERE table_name LIKE UPPER('&table_name'));
Note: Enter the table_name for which you want to find out column details.
5. TO FIND VIEWS DETAILS=========================SET LONG 1000
--change to point to all_views
SELECT application_id, view_id, view_name, description, text FROM fnd_views WHERE view_name LIKE UPPER('%&view_name%');
6. TO FIND VIEW COLUMNS========================
SELECT application_id, view_id, column_sequence, column_name FROM fnd_view_columns WHERE view_id = (SELECT view_id FROM fnd_views WHERE view_name LIKE UPPER('&view_name'));
Note: Enter the view_name for which you want to find out view column details.
7. TO FIND CURRENCY DETAILS===========================
SELECT currency_code, symbol, enabled_flag, currency_flag, description, precision, extended_precision, minimum_accountable_unit, start_date_active, end_date_active FROM fnd_currencies WHERE currency_code LIKE '%¤cy_code%';
Note: Here currency_code could be 'USD', 'GBP' etc.
8. TO FIND THE EXECUTABLE DETAILS==================================
SELECT application_id,executable_id,executable_name,execution_file_name,subroutine_name,icon_name,execution_file_pathFROM fnd_executablesWHERE application_id = &application_id AND executable_name LIKE '%&executable_name%';
Note: Here application_id could be id as per query no. 1 and executable_name could be 'APXPBFOR'should be the name of the executable for which you want to find the information
9. TO FIND INDEX DETAILS========================
Use the table all_indexes
SELECT dba.status,fnd.application_id,fnd.table_id,fnd.index_id, fnd.index_name,fnd.descriptionFROM fnd_indexes fnd, dba_indexes dba WHERE table_id = (SELECT table_id FROM fnd_tables WHERE table_name LIKE UPPER('&table_name')) and fnd.index_name = dba.index_name;
Note: Enter the table_name for which you want to find out index details.
10. TO FIND INDEX COLUMNS==========================
Use the table all_ind_columns
SELECT application_id,table_id,index_id,column_sequence,column_idFROM fnd_index_columns WHERE table_id = (SELECT table_id FROM fnd_tables WHERE table_name = UPPER('&table_name'));
Note: Enter the table_name for which y.ou want to find out index columns. If you are aware of index_id from query no. 9 above then specify that in the where clause instead of table_name.
11. TO KNOW PRIMARY KEY DETAILS===============================
Change the query to fetch the information from all_constraints
SELECT application_id,table_id,primary_key_id,primary_key_name,description,enabled_flagFROM fnd_primary_keysWHERE table_id = (SELECT table_id FROM fnd_tables WHERE table_name LIKE UPPER('&table_name'));
Note: Enter the table_name for which you want to find out primary key details.
12. TO FIND SEQUENCE DETAILS============================change the query to fetch the information from all_sequences
SELECT application_id, sequence_id,sequence_name,start_value,increment_by,min_value,max_value,cache_size,cycle_flag,order_flag,descriptionFROM fnd_sequencesWHERE sequence_name = UPPER('&sequence_name');
13. TO FIND PROFILE OPTION DETAILS==================================
The below query expects internal name of the profile option, which will be very tough to remember, change the query to fetch the information based on user profile option name
SELECT application_id,profile_option_id,profile_option_name, site_enabled_flag,resp_enabled_flag,user_enabled_flagFROM fnd_profile_optionsWHERE profile_option_name LIKE UPPER('%&profile_option_name%');
14. TO FIND FOLDERS DETAILS============================
Note: Here the NAME is completely case sensitive and hence needs to be passed as defined in FOLDER:
SELECT folder_id,object,name,public_flag,autoquery_flag,where_clause,order_byFROM fnd_foldersWHERE name like '%&name%';.
Praveen Gollu
13-Mar-09
First Party, Second Party, Third Party in Business World: 1. Company/Legal Entity ----> first party 2. Equity Holder/Share Holders ---------> Second Party 3. Creditors/Debenture Holders -------> Third Party First Party, Second Party, Third Party in General Insurance Business 1. Company/Legal Entity ----> Vehicle/Property 2. Equity Holder/Share Holders ---------> Owner 3. Creditors/Debenture Holders -------> Outsider (other than owner)
How do I define my Legal Entities?
In the real world a Legal Entity (LE) can enter into contracts, own cash (bank accounts), employ people, pay taxes, be sued and similar. In Oracle Financials Release 12, a whole new product; Legal Entity Configuration, was created to manage them. We allow you to define your real world Legal Entities and then map them to the E-Business Suite objects and structures. Transactions are stamped with an owning (first party) Legal Entity and that will be used to drive tax, accounting, intercompany and Legal Reporting.
So let’s look at the relationships LE have to other E-Business suite objects.
1- Accounting Structures
In the General Ledger Set Up a Legal Entity can be mapped to
A Single Ledger
One or more Balancing Segment Values (ex: Company Code) within a ledger.
2 - Operating Unit
There is no explicit mapping of Legal Entity to an OU, the relationship is derived from the ledger assigned to the OU and the Legal Entity mappings to ledgers as detailed above.
So how might you set up your LE in relation to your other set up in financials? There are two implementation models
1:Many
LE are mapped to the Balancing Segment Value (BSV, aka Company code) within a Ledger, so multiple LE are accounted for in a ledger.
An OU will have one Ledger assigned so transactions for many LE are processed and accounted in a single OU
1:1:1
A single LE is mapped to a Ledger
An OU will have one Ledger assigned
Therefore an OU only has one LE (that mesa it is easy to derive the LE given the OU)
So what model should you use?
That depends where the LE are registered.The 1:M model is recommended and preferred in the US, the 1:1:1 model is recommended for most non US regions
What is Cloning?
Cloning is the act of creating an identical copy of an already existing Oracle Applications system. The new system including component versions, operating system, and platform type must be initially identical to the existing system. There are cases where you can clone from one operating system version to another, if they are binary compatible. For example, if you have an existing single-node Oracle Applications system on Solaris 2.6, you could clone it to a node running Solaris 8, but not to a node running HP-UX. Why Do Users Need to Clone? You may want to clone an Oracle Applications system for several reasons, including: To create a copy of the production system to use for testing puropses. To periodically refresh a test system with a copy of a production system in order to keep the test system synchronized with the production system. To move an existing system to a different machine. Creating a stage area to reduce patching downtime. Simply copying all of the components will not provide you with a working Oracle Applications system. There are numerous configuration files in the file system and configuration information in the database that must be modified based on the system configuration
Tips of the Week:
Description
Published by
Week
Important table informations
1. TO FIND APPLICATION DETAILS===============================
This will provide you application id along with shortname and base path of it:
SELECT * FROM fnd_application;
2. TO FIND PRODUCT INSTALLATION DETAILS========================================
SELECT application_id, product_version, status, patch_level FROM fnd_product_installations;
Note: Here application_id would should be id as per obtained from query no. 1, product_version could be ‘12.0.0’, STATUS, where “I” means installed, “L” means custom, “N” means not installed, and “S” means installed as shared product, and patch_level could be 'R12…..'
3. TO FIND THE TABLE DETAILS=============================
Instead of using fnd_tables, change to query to use all_tables
SELECT application_id, table_id, table_name, user_table_name, description FROM fnd_tables WHERE table_name LIKE UPPER('%&table_name%');
Note: Enter the table_name for which you want to find out details. You can put application_id to filter tables.
4. TO FIND TABLE COLUMNS=========================
Instead of fnd_columns modify the query to point to all_tab_columns
Make sure to enter the table name in CAPS only:
SELECT application_id, table_id, column_id, column_name, user_column_name, column_sequence, column_type, width, description FROM fnd_columns WHERE table_id = (SELECT table_id FROM fnd_tables WHERE table_name LIKE UPPER('&table_name'));
Note: Enter the table_name for which you want to find out column details.
5. TO FIND VIEWS DETAILS=========================SET LONG 1000
--change to point to all_views
SELECT application_id, view_id, view_name, description, text FROM fnd_views WHERE view_name LIKE UPPER('%&view_name%');
6. TO FIND VIEW COLUMNS========================
SELECT application_id, view_id, column_sequence, column_name FROM fnd_view_columns WHERE view_id = (SELECT view_id FROM fnd_views WHERE view_name LIKE UPPER('&view_name'));
Note: Enter the view_name for which you want to find out view column details.
7. TO FIND CURRENCY DETAILS===========================
SELECT currency_code, symbol, enabled_flag, currency_flag, description, precision, extended_precision, minimum_accountable_unit, start_date_active, end_date_active FROM fnd_currencies WHERE currency_code LIKE '%¤cy_code%';
Note: Here currency_code could be 'USD', 'GBP' etc.
8. TO FIND THE EXECUTABLE DETAILS==================================
SELECT application_id,executable_id,executable_name,execution_file_name,subroutine_name,icon_name,execution_file_pathFROM fnd_executablesWHERE application_id = &application_id AND executable_name LIKE '%&executable_name%';
Note: Here application_id could be id as per query no. 1 and executable_name could be 'APXPBFOR'should be the name of the executable for which you want to find the information
9. TO FIND INDEX DETAILS========================
Use the table all_indexes
SELECT dba.status,fnd.application_id,fnd.table_id,fnd.index_id, fnd.index_name,fnd.descriptionFROM fnd_indexes fnd, dba_indexes dba WHERE table_id = (SELECT table_id FROM fnd_tables WHERE table_name LIKE UPPER('&table_name')) and fnd.index_name = dba.index_name;
Note: Enter the table_name for which you want to find out index details.
10. TO FIND INDEX COLUMNS==========================
Use the table all_ind_columns
SELECT application_id,table_id,index_id,column_sequence,column_idFROM fnd_index_columns WHERE table_id = (SELECT table_id FROM fnd_tables WHERE table_name = UPPER('&table_name'));
Note: Enter the table_name for which y.ou want to find out index columns. If you are aware of index_id from query no. 9 above then specify that in the where clause instead of table_name.
11. TO KNOW PRIMARY KEY DETAILS===============================
Change the query to fetch the information from all_constraints
SELECT application_id,table_id,primary_key_id,primary_key_name,description,enabled_flagFROM fnd_primary_keysWHERE table_id = (SELECT table_id FROM fnd_tables WHERE table_name LIKE UPPER('&table_name'));
Note: Enter the table_name for which you want to find out primary key details.
12. TO FIND SEQUENCE DETAILS============================change the query to fetch the information from all_sequences
SELECT application_id, sequence_id,sequence_name,start_value,increment_by,min_value,max_value,cache_size,cycle_flag,order_flag,descriptionFROM fnd_sequencesWHERE sequence_name = UPPER('&sequence_name');
13. TO FIND PROFILE OPTION DETAILS==================================
The below query expects internal name of the profile option, which will be very tough to remember, change the query to fetch the information based on user profile option name
SELECT application_id,profile_option_id,profile_option_name, site_enabled_flag,resp_enabled_flag,user_enabled_flagFROM fnd_profile_optionsWHERE profile_option_name LIKE UPPER('%&profile_option_name%');
14. TO FIND FOLDERS DETAILS============================
Note: Here the NAME is completely case sensitive and hence needs to be passed as defined in FOLDER:
SELECT folder_id,object,name,public_flag,autoquery_flag,where_clause,order_byFROM fnd_foldersWHERE name like '%&name%';.
Praveen Gollu
13-Mar-09
First Party, Second Party, Third Party in Business World: 1. Company/Legal Entity ----> first party 2. Equity Holder/Share Holders ---------> Second Party 3. Creditors/Debenture Holders -------> Third Party First Party, Second Party, Third Party in General Insurance Business 1. Company/Legal Entity ----> Vehicle/Property 2. Equity Holder/Share Holders ---------> Owner 3. Creditors/Debenture Holders -------> Outsider (other than owner)
How do I define my Legal Entities?
In the real world a Legal Entity (LE) can enter into contracts, own cash (bank accounts), employ people, pay taxes, be sued and similar. In Oracle Financials Release 12, a whole new product; Legal Entity Configuration, was created to manage them. We allow you to define your real world Legal Entities and then map them to the E-Business Suite objects and structures. Transactions are stamped with an owning (first party) Legal Entity and that will be used to drive tax, accounting, intercompany and Legal Reporting.
So let’s look at the relationships LE have to other E-Business suite objects.
1- Accounting Structures
In the General Ledger Set Up a Legal Entity can be mapped to
A Single Ledger
One or more Balancing Segment Values (ex: Company Code) within a ledger.
2 - Operating Unit
There is no explicit mapping of Legal Entity to an OU, the relationship is derived from the ledger assigned to the OU and the Legal Entity mappings to ledgers as detailed above.
So how might you set up your LE in relation to your other set up in financials? There are two implementation models
1:Many
LE are mapped to the Balancing Segment Value (BSV, aka Company code) within a Ledger, so multiple LE are accounted for in a ledger.
An OU will have one Ledger assigned so transactions for many LE are processed and accounted in a single OU
1:1:1
A single LE is mapped to a Ledger
An OU will have one Ledger assigned
Therefore an OU only has one LE (that mesa it is easy to derive the LE given the OU)
So what model should you use?
That depends where the LE are registered.The 1:M model is recommended and preferred in the US, the 1:1:1 model is recommended for most non US regions
What is Cloning?
Cloning is the act of creating an identical copy of an already existing Oracle Applications system. The new system including component versions, operating system, and platform type must be initially identical to the existing system. There are cases where you can clone from one operating system version to another, if they are binary compatible. For example, if you have an existing single-node Oracle Applications system on Solaris 2.6, you could clone it to a node running Solaris 8, but not to a node running HP-UX. Why Do Users Need to Clone? You may want to clone an Oracle Applications system for several reasons, including: To create a copy of the production system to use for testing puropses. To periodically refresh a test system with a copy of a production system in order to keep the test system synchronized with the production system. To move an existing system to a different machine. Creating a stage area to reduce patching downtime. Simply copying all of the components will not provide you with a working Oracle Applications system. There are numerous configuration files in the file system and configuration information in the database that must be modified based on the system configuration
No comments:
Post a Comment