Query to find the Request Group of a Concurrent Program


Many a times we need to find out the Request Group of a Concurrent Program. In such cases the below query will be a useful one.

SELECT
  RG.APPLICATION_ID "Request Group Application ID",
  RG.REQUEST_GROUP_ID "Request Group - Group ID",
  RG.REQUEST_GROUP_NAME,
  RG.DESCRIPTION,
  rgu.unit_application_id,
  rgu.request_group_id "Request Group Unit - Group ID",
  rgu.request_unit_id,cp.concurrent_program_id,
  cp.concurrent_program_name,
  cpt.user_concurrent_program_name,
  DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
  fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  FND_CONCURRENT_PROGRAMS_TL CPT
WHERE rg.request_group_id = rgu.request_group_id
  AND rgu.request_unit_id = cp.concurrent_program_id
  AND cp.concurrent_program_id = cpt.concurrent_program_id
  AND cpt.user_concurrent_program_name =’<Your_Concurrent_Program_Name>’;
Advertisements

Finding SQL Command on Java Page


Here are the steps to view the SQL Statements from Java User Interface (UI) Pages. For the purpose of this exercise, we will look at the query used to retrieve Bank Accounts attached to a Customer Site.

1. Update the following Profile Option Values to YES at user level

  • FND: Diagnostics
  • Personalize Self-Service Defn

2. Go to the Java UI Page where the SQL Query executes. In this example we go to Customers > Customer Sites.

3. Click on the link ‘About this Page’ in the bottom Left Hand Side of the page.

4. Navigate to the Page Tab on the Top Left Hand Side of the page.

5. In the Business Component References Details Section (You may need to expand this section), Go to the View Objects Sub Section.

6. Find the Object that describes the piece of information that you want to find the query for. In this case it’s Bank Accounts. The relevant Object has been highlighted (I searched the HTML for bank to find this).

7. Click on the link.

The full Query used can be cut and pasted into a SQL Editor and the query run.

Note: You will have to find the Bind Variables passed to the query in order to do this!! This may be found by looking up one of the previous queries.

Queries for Value Sets


Queries for Value Sets

Value Sets based on table:
This Query gives details of value sets that are based on a oracle application tables.
select ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffvt.value_column_name ,
    ffvt.meaning_column_name ,
    ffvt.id_column_name ,
    ffvt.application_table_name ,
    ffvt.additional_where_clause
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_validation_tables ffvt
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id;
Independent Value set Details:
This query gives details of independent FND Value sets i.e. Values are static and these are not derived from any application table.
SELECT ffvs.flex_value_set_id ,
    ffvs.flex_value_set_name ,
    ffvs.description set_description ,
    ffvs.validation_type,
    ffv.flex_value_id ,
    ffv.flex_value ,
    ffvt.flex_value_meaning ,
    ffvt.description value_description
FROM fnd_flex_value_sets ffvs ,
    fnd_flex_values ffv ,
    fnd_flex_values_tl ffvt
WHERE
    ffvs.flex_value_set_id     = ffv.flex_value_set_id
    and ffv.flex_value_id      = ffvt.flex_value_id
    AND ffvt.language          = USERENV('LANG');
Thanks
Dibyajyoti Koch
Have a nice Day!

API’s to Create User,Reset Password and Add Responsibility


API’s to Create User,Reset Password and Add Responsibility

I have created few queries using Oracle provided package:’FND_USER_PKG’. These queries might be very useful when you donot have the Oracle Apps front end access or you like to get in done through backend.

Using the below query, you can create a User in Oracle application.Just pass username, password and email id as parameters and it will create a user.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_password varchar2(30):='&Enter_Password';
v_session_id integer := userenv('sessionid');
v_email varchar2(30):=upper('&Enter_Email_Id');
begin
  fnd_user_pkg.createuser (
  x_user_name => v_user_name,
  x_owner => null,
  x_unencrypted_password => v_password,
  x_session_number => v_session_id,
  x_start_date => sysdate,
  x_end_date => null,
  x_email_address => v_email
  );
  commit;
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  ROLLBACK;
end;

May a times we forgot the apps password. Then you can use the below query to resent the password just in few seconds.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_new_password varchar2(30):='&Enter_New_Password';
v_status boolean;
begin
 v_status:= fnd_user_pkg.ChangePassword (
    username => v_user_name,
    newpassword => v_new_password
  );
  if v_status =true then
  dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
  commit;
  else
  DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
  END if;
end;

Use the below query to add a responsibility to a user. The advantage here is that you donot require system administrator responsibility access to add a responsibility.

declare
v_user_name varchar2(30):=upper('&Enter_User_Name');
v_resp varchar2(30):='&Enter_Responsibility';
v_resp_key varchar2(30);
v_app_short_name varchar2(50);
begin
  select
    r.responsibility_key ,
    a.application_short_name
  into v_resp_key,v_app_short_name
  from fnd_responsibility_vl r,
    fnd_application_vl a
  where
    r.application_id =a.application_id
    and upper(r.responsibility_name) = upper(v_resp);

  fnd_user_pkg.AddResp (
  username => v_user_name,
  resp_app => v_app_short_name,
  resp_key => v_resp_key,
  security_group => 'STANDARD',
  description => null,
  start_date => sysdate,
  end_date => null
  );
  commit;
  DBMS_OUTPUT.put_line ('Responsibility:'||v_resp||' '||'is added to the User:'||v_user_name);
EXCEPTION
when others then
  DBMS_OUTPUT.put_line ('Unable to add the responsibility due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
  rollback;
end;

Query to find Concurrent Requests submitted by a particular user


Query to find Concurrent Requests submitted by a particular user

SELECT
    user_concurrent_program_name,
    responsibility_name,
    request_date,
    argument_text,
    request_id,
    phase_code,
    status_code,
    logfile_name,
    outfile_name,
    output_file_type
FROM
    fnd_concurrent_requests fcr,
    fnd_concurrent_programs_tl fcp,
    fnd_responsibility_tl fr,
    fnd_user fu
WHERE
    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
    and fcr.responsibility_id = fr.responsibility_id
    and fcr.requested_by = fu.user_id
    and user_name = upper(:user_name)
ORDER BY REQUEST_DATE DESC;

Steps To Register Unix Shell Script As A Concurrent Program


Steps To Register Unix Shell Script As A Concurrent Program

We can register an Unix shell script in our oracle application through a concurrent program. Here are the steps.

Step 1:

Copy the .prog script in ASCII mode to the bin directory of your application top directory. For example, call the script XXSHELL.prog and place it under $XXCUST_TOP/bin

step 2:

Check the file permissions. Sometimes it is required to give full permission to the script.

step 3:

Make a symbolic link from your script to $FND_TOP/bin/fndcpesr For example, if the script is called XXSHELL.prog , then use this:

cd $XXCUST_TOP/bin

ln -s $FND_TOP/bin/fndcpesr XXSHELL

This link should be named the same as your script without the .prog extension. Put the link for your script in the same directory where the script is located.

step 4:

Register the concurrent program, using an execution method of ‘Host’. Use the name of your script without the .prog extension as the name of the executable.

For the example above: Use XXSHELL as executable name.

Note:

Your script will be passed at least 4 parameters, from $1 to $4.

$1 = orauser/pwd
$2 = userid(apps)
$3 = username,
$4 = request_id

Any other parameters you define will be passed in as $5 and higher. Make sure your script returns an exit status also.

fndcpesr is a standard utility available in $FND_TOP directory. It is mainly used by the application to parse the above four arguments to the shell scripts.

Attachment in Oracle Application


Attachment in Oracle Application

What is attachment in oracle application?

The attachments feature in oracle application enables users to link unstructured data, such as images, word-processing documents, spreadsheets, or text to their application data. For example, users can link images to items or video to operations as operation instructions.

Where to find an attachment?

There is an attachment icon in the oracle application toolbar that indicates whether the Attachments feature is enabled in a form block. When the button is dimmed, the Attachment feature is not available. When the Attachment feature is enabled in a form block, the icon becomes a solid paper clip. The icon switches to a paper clip holding a paper when the Attachment feature is enabled in a form lock and the current record has at least one attachment.

Attachment types:

An attached document can be:

1] Short Text

Text stored in the database containing less than 2000 characters.

2] Long Text

Text stored in the database containing 2000 characters or more.

3] Image

An image that Oracle Forms can display, including: bmp, cals, jfif, jpeg, gif, pcd, pcx, pict, ras, and tif.

4] OLE Object

An OLE Object that requires other OLE server applications to view, such as Microsoft Word or Microsoft Excel.

5] Web Page

A URL reference to a web page which you can view with your web browser.

Tables Involved:

For Importing Attachments in oracle application one has to populate following tables.

1. FND_DOCUMENTS

2. FND_ATTACHED_DOCUMENTS

3. FND_DOCUMENTS_TL

4. FND_DOCUMENT_DATATYPES.

5. FND_DOCUMENT_CATEGORIES

6. FND_DOCUMENTS_LONG_TEXT (Long text type attachment).

7. FND_DOCUMENTS_SHORT_TEXT (Short text type attachment).

8. FND_DOCUMENTS_LONG_RAW

9. FND_LOBS (File type attachments).

FND_DOCUMENTS:

FND_DOCUMENTS stores language-independent information about a document. For example, each row contains a document identifier, a category identifier, the method of security used for the document (SECURITY_TYPE, where 1=Organization,2=Set of Books, 3=Business unit,4=None), the period in which the document is active, and a flag to indicate whether or not the document can be shared outside of the security type (PUBLISH_FLAG).

Other specifications in this table include: datatype (DATATYPE_ID, where 1=short text,2=long text, 3=image, 4=OLE object), image type, and storage type (STORAGE_TYPE, where 1=stored in the database, 2=stored in the file system).

The document can be referenced by many application entities and changed only in the define document form (USAGE_TYPE=S); it can be used as a fill-in-the-blanks document, where each time you use a template, you make a copy of it (USAGE_TYPE=T); or it can be used only one time (USAGE_TYPE=O).Images and OLE Objects cannot be used as templates.

FND_ATTACHED_DOCUMENTS:

FND_ATTACHED_DOCUMENTS stores information relating a document to an application entity.  For example, a record may link a document to a sales order or an item. Each row contains foreign keys to FND_DOCUMENTS and FND_DOCUMENT_ENTITIES. There is also a flag to indicate whether or not an attachment was created automatically.

FND_DOCUMENTS_TL:

FND_DOCUMENTS_TL stores translated information about the documents in FND_DOCUMENTS. Each row includes the document identifier, the language the row is translated to, the description of the document, the file in which the image is stored, and an identifier (MEDIA_ID) of the sub-table in which the document is saved (FND_DOCUMENTS_SHORT_TEXT, FND_DOCUMENTS_LONG_TEXT, or FND_DOCUMENTS_LONG_RAW).

FND_DOCUMENT_DATATYPES:

FND_DOCUMENT_DATATYPES stores the document datatypes that are supported. Initial values are: short text, long text, image, and OLE Object (DATATYPE_ID=1, 2, 3, or 4). Customers can add datatypes to handle documents stored outside of Oracle and use non-native Forms applications to view/edit their documents. The table uses a “duplicate record” model for handling multi-lingual needs. That is, for each category there will be one record with the same CATEGORY_ID and CATEGORY_NAME for each language.

FND_DOCUMENT_CATEGORIES:

FND_DOCUMENT_CATEGORIES stores information about the categories in which documents are classified. For example, documents may be considered “Bill of Material Comments”, “WIP Job Comments”, etc. Document categories are used to provide a measure of security on documents. Each form that enables the attachment feature lists which categories of documents can be viewed in the form. This table uses a “duplicate record” model for handling multi-lingual needs.

FND_DOCUMENTS_LONG_TEXT:

FND_DOCUMENTS_LONG_TEXT stores information about long text documents.

FND_DOCUMENTS_SHORT_TEXT:

FND_DOCUMENTS_SHORT_TEXT stores information about short text documents.

FND_DOCUMENTS_LONG_RAW:

FND_DOCUMENTS_LONG_RAW stores images and OLE Objects, such as Word Documents and Excel spreadsheets, in the database.

FND_DOCUMENT_ENTITIES:

FND_DOCUMENT_ENTITIES lists each entity to which attachments can be linked. For example, attachments can be linked to Items, Sales Orders, etc. Since the table uses a “duplicate record” model for handling multi-lingual needs, for each document entity there will be one record with the same DOCUMENT_ENTITY_ID and DATA_OBJECT_CODE for each language.

Queries:

1] To find all Long Text attachments:

SELECT
        FAD.SEQ_NUM "Seq Number",
        FDAT.USER_NAME "Data Type",
        FDCT.USER_NAME "Category User Name",
        FAD.ATTACHED_DOCUMENT_ID "Attached Document Id",
        FDET.USER_ENTITY_NAME "User Entity",
        FD.DOCUMENT_ID "Document Id",
        FAD.ENTITY_NAME "Entity Name",
        FD.MEDIA_ID "Media Id",
        FD.URL "Url",
        FDT.TITLE "Title",
        FDLT.LONG_TEXT "Attachment Text"
FROM
        FND_DOCUMENT_DATATYPES FDAT,
        FND_DOCUMENT_ENTITIES_TL FDET,
        FND_DOCUMENTS_TL FDT,
        FND_DOCUMENTS FD,
        FND_DOCUMENT_CATEGORIES_TL FDCT,
        FND_ATTACHED_DOCUMENTS   FAD,
        FND_DOCUMENTS_LONG_TEXT FDLT
WHERE
        FD.DOCUMENT_ID          = FAD.DOCUMENT_ID
        AND FDT.DOCUMENT_ID     = FD.DOCUMENT_ID
        AND FDCT.CATEGORY_ID    = FD.CATEGORY_ID
        AND FD.DATATYPE_ID      = FDAT.DATATYPE_ID
        AND FAD.ENTITY_NAME     = FDET.DATA_OBJECT_CODE
        AND FDLT.MEDIA_ID       = FD.MEDIA_ID
        AND FDAT.NAME           = 'LONG_TEXT';

2] To find all Short Text attachments:

SELECT
        FAD.SEQ_NUM "Seq Number",
        FDAT.USER_NAME "Data Type",
        FDCT.USER_NAME "Category User Name",
        FAD.ATTACHED_DOCUMENT_ID "Attached Document Id",
        FDET.USER_ENTITY_NAME "User Entity",
        FD.DOCUMENT_ID "Document Id",
        FAD.ENTITY_NAME "Entity Name",
        FD.MEDIA_ID "Media Id",
        FD.URL "Url",
        FDT.TITLE "Title",
        FDST.SHORT_TEXT "Attachment Text"
FROM
        FND_DOCUMENT_DATATYPES FDAT,
        FND_DOCUMENT_ENTITIES_TL FDET,
        FND_DOCUMENTS_TL FDT,
        FND_DOCUMENTS FD,
        FND_DOCUMENT_CATEGORIES_TL FDCT,
        FND_ATTACHED_DOCUMENTS   FAD,
        FND_DOCUMENTS_SHORT_TEXT FDST
WHERE
        FD.DOCUMENT_ID          = FAD.DOCUMENT_ID
        AND FDT.DOCUMENT_ID     = FD.DOCUMENT_ID
        AND FDCT.CATEGORY_ID    = FD.CATEGORY_ID
        AND FD.DATATYPE_ID      = FDAT.DATATYPE_ID
        AND FAD.ENTITY_NAME     = FDET.DATA_OBJECT_CODE
        AND FDST.MEDIA_ID       = FD.MEDIA_ID
        AND FDAT.NAME           = 'SHORT_TEXT';

Attachment upload through API:

Attachments can also be uploaded through an oracle provided API called  FND_ATTACHED_DOCUMENTS_PKG.

It consist of three procedures

1)  Insert Row

2)  Update Row

3)  Lock Row

Names of these procedures are self explanatory. insert row is used to insert a new row for attachment data, update row is used to update existing row for a particular row and Lock Row is used to lock a existing row for further modification.

 

Query to fetch Responsibilities attached to a User


Query to fetch Responsibilities attached to a user:

The below query will fetch the responsibilities assigned to a particular user.

SELECT
    fu.user_id,
    fu.user_name,
    fr.responsibility_name,
    fr.description,
    fa.application_name

FROM fnd_user fu,
     fnd_user_resp_groups g,
     fnd_application_tl fa,
     fnd_responsibility_tl fr

WHERE
     g.user_id(+) = fu.user_id
     AND g.responsibility_application_id = fa.application_id
     AND fa.application_id = fr.application_id
     AND g.responsibility_id = fr.responsibility_id
     AND fu.user_name  =UPPER('User_Name');

Oracle Application:Data Model


Oracle Application:Data Model

When we install Oracle Database by default system will creates SYS and SYSTEM schemas. These consist of all Data Dictionary Tables. Like this if we install Oracle Applications System will automatically creates schemas of all Modules (i.e. GL, AR, AP, etc.) with the respective module name as User and Password. Along with these schemas some special Schemas i.e. APPS, APPLSYS, APPLSYSPUB will be created for special purpose.

APPS Schema:

  • It is Public Schema.
  • The APPS schema is an ORACLE schema that has access to the complete Oracle Applications data model. It is analogous to the SYSTEM schema, which has access to the entire database.
  • AutoInstall creates the necessary grants and synonyms between the schemas.
  • Oracle Applications responsibilities connect to an APPS schema.
  • There is one APPS schema for every product installation group.
  • It consists of a collection of public synonym of all the objects of all the schemas in the Application database. All the Procedures, Functions and Packages created must be stored in this Schema.

APPLSYS Schema:

This is a special Schema consists of the files starts with FND, ALR, WF and AD.

APPLSYSPUB Schema:

This schema is a collection of public synonyms of all FND Tables, which are used for User verification. This is the Gate Way User ID of Oracle Applications.

Few Other Base Product Schemas:

  • GL ( General Ledger )
  • INV ( Inventory)
  • AP ( Accounts Payables)
  • APPLSYS ( Application Object Library)
  • ALR ( Alerts)

KFF vs. DFF: A Comparison


 KFF vs. DFF: A Comparison

A flexfield in Oracle Application is a field made up of sub–fields, or segments.  They are basically used to implement code structure or to capture additional information. There are two types of flexfields: key flexfields and descriptive flexfields. A key flexfield appears on your form as a normal text field with an appropriate prompt while a descriptive flexfield appears on your form as a two–character–wide text field with square brackets [ ] as its prompt.

Here is a brief comparison between KFF and DFF:

KFF vs. DFF