Query to get Responsibility and Operating Unit association details

Get Org id from Responsibility Name

SELECT   frv.responsibility_name,

         fpov.profile_option_value org_id,

         hou.NAME

    FROM apps.hr_organization_units hou,

         apps.fnd_profile_options_vl fpo,

         apps.fnd_profile_option_values fpov,

         apps.fnd_responsibility_vl frv

   WHERE frv.responsibility_name = <Responsibility Name>

     AND fpov.level_value = frv.responsibility_id

     AND fpo.profile_option_id = fpov.profile_option_id

     AND fpo.user_profile_option_name = 'MO: Operating Unit'

     AND fpov.profile_option_id = fpo.profile_option_id

     AND hou.organization_id =TO_NUMBER(fpov.profile_option_value)
 ORDER BY frv.responsibility_name

GET RESPONSIBILITY Name  from Org id

--------------------------------------------------------

SELECT   frv.responsibility_name,

         fpov.profile_option_value org_id,

         hou.NAME

    FROM apps.fnd_profile_options_vl fpo,

         apps.fnd_responsibility_vl frv,

         apps.fnd_profile_option_values fpov,

         apps.hr_organization_units hou

   WHERE hou.NAME = <OU Name>

     AND fpov.profile_option_value = TO_CHAR (hou.organization_id)

     AND fpo.profile_option_id = fpov.profile_option_id

     AND fpo.user_profile_option_name = 'MO: Operating Unit'

     AND frv.responsibility_id = fpov.level_value

ORDER BY frv.responsibility_name

Submit Concurrent program Using TRIGGER

CREATE OR REPLACE TRIGGER chidam

  AFTER UPDATE ON student

  FOR EACH ROW

  WHEN (1 = 1)

DECLARE

  l_result     BOOLEAN;

  l_request_id NUMBER := 0;

BEGIN

  l_result     := fnd_request.set_mode(TRUE);

  l_request_id :=

  fnd_request.submit_request(application => 'XXONT',

                           program     => 'XXX_CONCU_SHORT_NAME',

                           argument1   => null);

END;

Submit a Concurrent Program/Request from PL/SQL

Oracle has provided the feasibility to submit a concurrent request from backend using "fnd_request.submit_request" API.

Before submitting the API we need to set the environment and this can be done using "fnd_global.apps_initialize"

Here is a sample code to submit a concurrent program from PL/SQL

Note:- This code is to submit a Concurrent Program, not the Request Set. To Submit the Request Set from the backend, We have a different API.

--

DECLARE

   l_responsibility_id     NUMBER;

   l_resp_application_id   NUMBER;

   l_security_group_id     NUMBER;

   l_user_id               NUMBER;

   l_request_id            NUMBER;

BEGIN

   --

   -- Get the apps environment variables --

   --

   SELECT user_id, responsibility_id, responsibility_application_id,

          security_group_id

     INTO l_user_id, l_responsibility_id, l_resp_application_id,

          l_security_group_id

     FROM fnd_user_resp_groups

    WHERE user_id = (SELECT user_id

                       FROM fnd_user

                      WHERE user_name = '&USER_NAME')

      AND responsibility_id =

             (SELECT responsibility_id

                FROM fnd_responsibility_vl

               WHERE responsibility_name = '&RESP_NAME');

   --

   --To set environment context.

   --

   apps.fnd_global.apps_initialize (l_user_id,

                                    l_responsibility_id,

                                    l_resp_application_id

                                   );

   --

   --Submitting Concurrent Request

   --

      l_request_id := 
fnd_request.submit_request(application => 'XXCUST', -- Application Short Name

            program     => 'XX_DEPT_DTLS', -- Program Short Name
            description => 'XX_DESCRIPTION', -- Any Description
            start_time  => SYSDATE, -- Start Time
            sub_request => FALSE, -- Subrequest Default False
            argument1   => 'ARG1' -- Parameters Starting
            );

   --

   COMMIT;

   --

   IF l_request_id = 0

   THEN

      DBMS_OUTPUT.put_line ('Concurrent request failed to submit');

   ELSE

      DBMS_OUTPUT.put_line ('Successfully Submitted the Concurrent Request: '||l_request_id);

   END IF;

   --

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Error While Submitting Concurrent Request '

                            || TO_CHAR (SQLCODE)| '-'|| SQLERRM

                           );

END;

/

Who Columns in Oracle Apps Table

Standard WHO Columns

The following are the Standard WHO Columns in most of the Oracle tables:

created_by
creation_date
last_update_date
last_updated_by
last_update_login

Use fnd_profile.VALUE ('USER_ID') for retrieving the user_id which will be used by created_by column.

Creation date and last_update_date will be normally SYSDATE.

last_updated_by is same as created_by

Use USERENV ('SESSIONID') for getting the last_update_login id.

Fetch DYNAMIC/RUNTIME Table Column Name with Values

CREATE TABLE APPS.STUDENT

(

ID_T NUMBER,

NAME_T VARCHAR2(50 BYTE),

MARK_T NUMBER

)

select * from student

ID_T NAME_T MARK_T
1 chidam 100
2 div 200
3 aksh 300


PLSQL Scripts

declare

  --v1.1 added

  l_cur    number;

  l_dtbl   dbms_sql.desc_tab;

  l_cnt    number;

  l_status number;

  l_val    varchar2(200);

begin

  l_cur := dbms_sql.open_cursor;

  dbms_sql.parse(l_cur, 'SELECT * from student', dbms_sql.native);

  dbms_sql.describe_columns(l_cur, l_cnt, l_dtbl);

  for i in 1 .. l_cnt

  loop

    dbms_sql.define_column(l_cur, i, l_val, 240);

  end loop;

  l_status := dbms_sql.execute(l_cur);

  while (dbms_sql.fetch_rows(l_cur) > 0) loop

    for i in 1 .. l_cnt loop

      dbms_sql.column_value(l_cur, i, l_val);

      dbms_output.put_line(l_dtbl(i).col_name || ' --> ' || l_val);

    end loop;

  end loop;

  dbms_sql.close_cursor(l_cur);

end;

OUTPUT

ID_T --> 1
NAME_T --> chidam
MARK_T --> 100

ID_T --> 2
NAME_T --> div
MARK_T --> 200

ID_T --> 3
NAME_T --> aksh
MARK_T --> 300

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

   AND ffvs.flex_value_set_name = 'XXX_COUNTRY_VS'

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')

   AND ffvs.flex_value_set_name = 'XXX_COUNTRY_VS'

WebADI Upload Download Command

WebADI LDT Command :-

1. Download è FNDLOAD apps/<password> 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_TST_1_XINTG.ldt BNE_INTEGRATORS INTEGRATOR_ASN=XXINV INTEGRATOR_CODE=XX_TST_1_XINTG

2. Upload è FNDLOAD apps/<password> 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_TST_1_XINTG.ldt

Form Function LDT Command :-

1. Download è FNDLOAD apps/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXINV_SSM_ITEM_UPL_WADI.ldt FUNCTION FUNCTION_NAME="XXINV_SSM_ITEM_UPL_WADI"

2. Upload è FNDLOAD apps/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXINV_SSM_ITEM_UPL_WADI.ldt