, , , , ,

SQL Queries for checking Profile Option Values

The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level

1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’
SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,’Site’,
10002,’Application’,
10003,’Resp’,
10004,’User’) Option_Level,
decode(pov.level_id,
10001,’Site’,
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,’Is Null’) Profile_option_Value
FROM 
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro1.user_profile_option_name like (‘%Ledger%’)
and  pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like ‘%General%Ledger%’ /* comment this line  if you need to check profiles for all responsibilities */
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;
2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.
SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,’Site’,
10002,’Application’,
10003,’Resp’,
10004,’User’) Option_Level,
decode(pov.level_id,
10001,’Site’,
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,’Is Null’) Profile_option_Value
FROM 
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like ‘%General%Ledger%’
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;

Similarly, you can tweak the above queries to obtain Profile Option Values set for a particular User or a particular application.

/by
, , , , , , ,

Oracle Database Links (DB Link)

A database link is a path through which a remote user in another database can connect to any other database. Once created the database link exists as an object in the user schema.

 Type of DB Links
There are 3 types of DB links. They are as follows:

1. PRIVATE: When the DB links is created, it is created under Private mode as default. The Private DBLINK is only available to the user who has created it. It is not possible for a user to grant access on a private DBLINK to other users.

2. PUBLIC: The Public DBLINK is available to all the users and all users can have the access without any restrictions.

 3. SHARED: Shared database link uses share the server connection to support database link connection. If there are multiple concurrent database link access into a remote database, shared database link can be used to reduce the number of server connections required. Without the shared clause each database link connection requires a separate connection to the remote database.

 Types of Logins:
In dblink we can use 2 types of login. They are as follows:

1. DEFAULT LOGIN: The User name and Password is same in both the databases.
Syntax
======
CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING
Code: (Text)
Create public database link daslink connect to current_user using ‘ORCL’


2. EXPLICIT LOGIN: The User Name and Password is different in both the databases.
Syntax
======
CREATE [PUBLIC|SHARED] DATABASE LINK CONNECT TOIDENTIFIED BY USING
Code: (text)
CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘ORCL’

 Note: To create the public DBLINK the user must have create public database link system privileges.

/by
, , , , , ,

Assign item to organization by ego_item_pub.assign_item_to_org API

This post will cover API ego_item_pub.assign_item_to_org used to assign item to an organization.
Step by step demonstration of code.
Please download attached file to use as sample code. This sample code assign single item at a time to single organization you can modify it for multiple items or multiple organizations at a time.
This API assigns a specific item to any inventory organization. The item to be assigned must already assigned to parent organization before running this API.

Input parameters.
P_param1                   pass inventory item id.
P_organization_id      organization id to which you want to assign item
P_organization_code organization code of specific organization


Variables need to be assign values by you.
l_user_id                     your used id
l_resp_id                     your responsibility id
l_application_id          application id

Get this information from fnd_responsibility table.

Running attached code I recommend to run it initially for some items and verify that these items are assigned to specific organizations.

Download.
/by
, , , , ,

Registering Custom Table in Apps

To register your custom table under FND.

1. Input is your custom table name. Execute these  4 queries
2. Spool the records
3. Execute the spooled records in apps
4. Commit;

select ‘EXEC ‘||’AD_DD.REGISTER_TABLE(”XXCUST”, ”’||TABLE_NAME||”’,”T”,8,10,90);’ from all_tables
where table_name = :TABLE_NAME
/
select ‘EXEC ‘||’AD_DD.REGISTER_COLUMN(”XXCUST”, ”’||TABLE_NAME||”’,”’||COLUMN_NAME||”’,’||COLUMN_ID||’,”’||DATA_TYPE||”’,’||DATA_LENGTH||’,”’||NULLABLE||”’,”N”);’ from all_tab_columns
where table_name = :TABLE_NAME
ORDER BY COLUMN_ID
/
select ‘EXEC ‘||’AD_DD.REGISTER_PRIMARY_KEY(”XXCUST”,”’||INDEX_NAME||”’,”’||TABLE_NAME||”’,”’||ITYP_NAME||”’,”S”,”Y”,”Y”);’
FROM ALL_INDEXES
WHERE table_name = :TABLE_NAME
and uniqueness = ‘UNIQUE’
/
select ‘EXEC ‘||’AD_DD.REGISTER_PRIMARY_KEY_COLUMN(”XXCUST”,”’||A.INDEX_NAME||”’,”’||A.TABLE_NAME||”’,”’||A.COLUMN_NAME||”’,’||A.COLUMN_POSITION||’);’
FROM ALL_IND_COLUMNS A, ALL_INDEXES B
WHERE A.TABLE_NAME = :TABLE_NAME
AND A.INDEX_NAME = B.INDEX_NAME
AND B.UNIQUENESS = ‘UNIQUE’
/

To delete the registered Tables, columns
select ‘EXEC ‘||’AD_DD.DELETE_TABLE(”XXCUST”, ”’||TABLE_NAME);’ from all_tables
where table_name = :TABLE_NAME
/
select ‘EXEC ‘||’AD_DD.REGISTER_COLUMN(”XXCUST”, ”’||TABLE_NAME||”’,”’||COLUMN_NAME);’ from all_tab_columns
where table_name = :TABLE_NAME
ORDER BY COLUMN_ID
/

/by
, , , ,

Calculating Weekdays between two dates

Create this function:

CREATE OR REPLACE FUNCTION totworkdays (fromdate DATE, todate DATE)
   RETURN NUMBER IS
   totalsundays     NUMBER;
   totalsaturdays   NUMBER;
begin
   totalsundays
        := NEXT_DAY (todate – 7, ‘sunday’)
           – NEXT_DAY (fromdate – 1, ‘sunday’);
   totalsaturdays
      :=   NEXT_DAY (todate – 7, ‘saturday’)
         – NEXT_DAY (fromdate – 1, ‘saturday’);

   RETURN (todate – fromdate – (totalsundays + totalsaturdays) / 7 – 1);
END totworkdays;

Call this function as follows:

declare
lv_tot_work_days number;
begin
lv_tot_work_days := totworkdays (’01-jan-2009′, ’31-jan-2009′);
dbms_output.put_line(‘Total Work Days: ‘||lv_tot_work_days);
end;

/by