How to set profile option from backend

Syntax:
FND_PROFILE.SAVE(<Profile_Option_Name>, <Profile_Option_Value>, <Level SITE/APPL/RESP/USER>, <Level_Value>, <Level_Value_App_id>);

FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'APPL', 321532);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'RESP', 321532, 345234);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', 123321);

Query to know a responsibility is related to which operating unit

SELECT FRV.RESPONSIBILITY_NAME,
       PROFILE_OPTION_VALUE ORG_ID
  FROM FND_PROFILE_OPTIONS_VL FPO,
       FND_PROFILE_OPTION_VALUES FPOV,
       FND_RESPONSIBILITY_VL FRV
 WHERE FPO.PROFILE_OPTION_NAME = 'ORG_ID'
   AND FPOV.APPLICATION_ID = FPO.APPLICATION_ID
   AND FPOV.PROFILE_OPTION_ID = FPO.PROFILE_OPTION_ID
   AND FRV.RESPONSIBILITY_ID = FPOV.LEVEL_VALUE
   AND FPOV.LEVEL_ID = 10003
   AND FRV.RESPONSIBILITY_NAME = :resp_name

How to use dynamic SQL Statement for the Date Parameter in Scheduled Concurrent Requests

Cause: If we schedule a concurrent requests which has a date parameter and value of this date parameter is coming through the dynamic SQL statement or value set then we can't see the correct value to the request parameter as per the dynamic SQL statement or value set. It will take the same value each time which we have passed first time.

Example: Date parameter details of your Request.
     Seq             : 1
     Parameter    : Test Date Time
     Description  : Test Date Time
     Enabled      : Make sure it is checked

   Under Validation Section :

     Value Set    : FND_STANDARD_DATETIME
     Default Type : SQL Statement
     Default Value: SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') FROM DUAL

  Under Display Section :

     Display Size : 11
     Prompt       : Test Date Time
     Token        : Test_Date_Time

Submit the request only once and take a note of request id's.

When you will check these request id in FND_CONCURRENT_REQUESTS table then the value of ARGUMENT1 will be the same each time. But it should be different for every request id.

Solution :
Below are the steps which we have to follow to use dynamic SQL statement or a Value set  for the date parameter in scheduled concurrent requests.

1. Create the following customer procedure under APPS schema that will be used to update the date parameter (ARGUMENT1) in FND_CONCURRENT_REQUESTS table for each run with same query used in the parameters screen for the concurrent program:

CREATE OR REPLACE PROCEDURE XX_CUSTOM_INCREMENT AS
PARAM    VARCHAR2(20);
BEGIN
  SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS')
  INTO   PARAM
  FROM   DUAL;

  FND_RESUB_PRIVATE.SET_PARAMETER(1,PARAM);
END;

2. Navigate to "Concurrent: Program > Define" screen and Query for program.
3. Enter in the "Incrementor" field the procedure name created in step 1: XX_CUSTOM_INCREMENT.
4. Save Changes
5. Cancel previous scheduled request and schedule a new one.

Script to get Scheduled Concurrent Requests

select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31),
fnd_conc_release_classes c
where c.class_type = 'S'
and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;

Script to get that query in your program which is taking much time..

You can get that query which is taking much time only then when your program is running.
In the below query you need to pass the request id of your program.

SELECT SQL_TEXT,SQL_FULLTEXT
FROM V$SQL
WHERE SQL_ID IN (SELECT SQL_ID
                                   FROM V$SESSION
                                   WHERE PADDR IN (select addr
                                                                      from v$process
                                                                       where spid =(select oracle_process_id
                                                                       from fnd_concurrent_requests
                                                                       where request_id=&p_request_id; 

Script to assign a Concurrent Program to a Request Group..

DECLARE
g_message VARCHAR2(2000);
BEGIN
DECLARE
    p_short_name        VARCHAR2(200) := 'PAXAARPT';                     -- Program Short Name
    p_application       VARCHAR2(200) := 'Projects';                     -- Program Application Short Name
    p_request_group     VARCHAR2(200) := 'GL Concurrent Program Group';  -- Request Group Name
    p_group_application VARCHAR2(200) := 'SQLGL';                        -- Request Group Application Short Name
BEGIN
   
    IF fnd_program.program_in_group (program_short_name => p_short_name , program_application => p_application , request_group => p_request_group , group_application => p_group_application )
   
    THEN
        fnd_program.remove_from_group (program_short_name => p_short_name , program_application => p_application , request_group => p_request_group , group_application => p_group_application );
        g_message := 'Program ' || p_short_name || ' is already in request group ' || p_request_group;
        dbms_output.put_line (g_message);
        fnd_program.add_to_group (program_short_name => p_short_name , program_application => p_application , request_group => p_request_group , group_application => p_group_application );
        g_message := 'Program ' || p_short_name || ' is added into request group ' || p_request_group;
        dbms_output.put_line (g_message);
   
    ELSE
        fnd_program.add_to_group (program_short_name => p_short_name , program_application => p_application , request_group => p_request_group , group_application => p_group_application );
        g_message := 'Program ' || p_short_name || ' is added into request group ' || p_request_group;
        dbms_output.put_line (g_message);
   
    END IF;
    COMMIT;

EXCEPTION

WHEN OTHERS THEN
    dbms_output.put_line (fnd_program.message);
    dbms_output.put_line (SUBSTR (sqlerrm, 1, 100));
END;
END;
/

Script to Add a Request Set to a Request Group..

DECLARE
  g_message VARCHAR2(2000);
BEGIN
  DECLARE
    p_short_name        VARCHAR2(200) := 'Payroll Activity Report';       -- Request Set Short Name
    p_application       VARCHAR2(200) := 'PAY';                           -- Request Set Application Short Name
    p_request_group     VARCHAR2(200) := 'System Administrator Reports';  -- Request Group Name
    p_group_application VARCHAR2(200) := 'FND';                           -- Request Group Application Short Name
  BEGIN

      IF fnd_set.set_in_group ( request_set => p_short_name , set_application => p_application , request_group => p_request_group , group_application => p_group_application ) THEN
         fnd_set.remove_set_from_group ( request_set => p_short_name , set_application => p_application , request_group => p_request_group , group_application => p_group_application );
        g_message := 'Request Set ' || p_short_name || ' is already in request group ' || p_request_group;
        dbms_output.put_line (g_message);
        fnd_set.add_set_to_group(request_set => p_short_name, set_application => p_application , request_group => p_request_group , group_application => p_group_application );
        g_message := 'Request Set ' || p_short_name || ' is added into request group ' || p_request_group;
        dbms_output.put_line (g_message);
      ELSE
        fnd_set.add_set_to_group (request_set => p_short_name , set_application => p_application , request_group => p_request_group , group_application => p_group_application );
        g_message := 'Request Set ' || p_short_name || ' is added into request group ' || p_request_group;
        dbms_output.put_line (g_message);
      END IF;

    COMMIT;
  EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line (fnd_program.message);
    dbms_output.put_line (SUBSTR (sqlerrm, 1, 100));
  END;
END;

Query to get lock TABLES detail

SELECT DO.OWNER,
               DO.OBJECT_NAME,
               DO.OBJECT_TYPE,
               VS.SID        ,
               VS.SERIAL#    ,
               VS.STATUS     ,
               VS.OSUSER     ,
               VS.MACHINE    ,
               VS.SID
   FROM V$LOCKED_OBJECT VLB,
               V$SESSION VS            ,
               DBA_OBJECTS DO
WHERE VS.SID   = VLB.SESSION_ID
     AND VLB.OBJECT_ID = DO.OBJECT_ID;

Script to Create Oracle Directory entry in DBA_DIRECTORIES table

DECLARE
l_directory_name VARCHAR2 (2000);
l_directory_path VARCHAR2 (2000);
BEGIN
l_directory_name := 'CESDIR072917';
l_directory_path :=
'/dba/u01/app/applmgr/common/xxx/admin/out/xxx';

EXECUTE IMMEDIATE 'create or replace directory '
|| l_directory_name
|| ' as '''
|| l_directory_path
|| '''';

EXECUTE IMMEDIATE 'grant read on directory '
|| l_directory_name
|| ' to public';

COMMIT;
END;
/

To Delete the existing directory :-

DROP DIRECTORY l_directory_name;

Script to get PO and Invoice related details

SELECT A.ORG_ID "ORG ID",
         E.SEGMENT1 "VENDOR NUM",
         E.VENDOR_NAME "SUPPLIER NAME",
         UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
         F.VENDOR_SITE_CODE "VENDOR SITE CODE",
         F.ADDRESS_LINE1 "ADDRESS",
         F.CITY "CITY",
         F.COUNTRY "COUNTRY",
         TO_CHAR(TRUNC(D.CREATION_DATE)) "PO Date",
         D.SEGMENT1 "PO NUM",
         D.TYPE_LOOKUP_CODE "PO Type",
         C.QUANTITY_ORDERED "QTY ORDERED",
         C.QUANTITY_CANCELLED "QTY CANCELLED",
         G.ITEM_ID "ITEM ID" ,
         G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
         G.UNIT_PRICE "UNIT PRICE",
         (NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO Line Amount",
       (SELECT DECODE(PH.APPROVED_FLAG, 'Y', 'Approved') FROM PO.PO_HEADERS_ALL PH WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID)"PO Approved?",
         A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
         A.INVOICE_AMOUNT "INVOICE AMOUNT",
         TO_CHAR(TRUNC(A.INVOICE_DATE)) "INVOICE DATE",
         A.INVOICE_NUM "INVOICE NUMBER",
       (SELECT DECODE(X.MATCH_STATUS_FLAG, 'A', 'Approved')
        FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
        WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)"Invoice Approved?",
       A.AMOUNT_PAID,
         H.AMOUNT,
         H.CHECK_ID,
         H.INVOICE_PAYMENT_ID "Payment Id",
         I.CHECK_NUMBER "Cheque Number",
         TO_CHAR(TRUNC(I.CHECK_DATE)) "PAYMENT DATE"
  FROM AP_INVOICES_ALL A,
       AP_INVOICE_DISTRIBUTIONS_ALL B,
       PO_DISTRIBUTIONS_ALL C,
       PO_HEADERS_ALL D,
       AP_SUPPLIERS E,
       PO_VENDOR_SITES_ALL F,
         PO_LINES_ALL G,
         AP_INVOICE_PAYMENTS_ALL H,
         AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
  AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
  AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
  AND E.VENDOR_ID (+) = D.VENDOR_ID
  AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
  AND D.PO_HEADER_ID = G.PO_HEADER_ID
  AND C.PO_LINE_ID = G.PO_LINE_ID
  AND A.INVOICE_ID = H.INVOICE_ID
  AND H.CHECK_ID = I.CHECK_ID
  AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
  AND C.PO_HEADER_ID IS NOT NULL
  AND A.PAYMENT_STATUS_FLAG = 'Y'
  AND D.TYPE_LOOKUP_CODE != 'BLANKET'

Script to update the Menu Entries

DECLARE
     G_LAST_UPDATE_DATE DATE := SYSDATE;
     G_LAST_UPDATED_BY   NUMBER ;
     G_LAST_UPDATE_LOGIN NUMBER ;
     G_APPLICATION_ID    NUMBER ;
     G_RESPONSIBILITY_ID NUMBER ;
     L_FUNCTION_ID   NUMBER;
   
    CURSOR C_UPDATE_MENU
    IS
SELECT FMEV.MENU_ID,
       FMEV.ENTRY_SEQUENCE,
       FMEV.SUB_MENU_ID,
       FMEV.GRANT_FLAG,
       FMEV.PROMPT,
       FMEV.DESCRIPTION,
       FM.MENU_NAME
 FROM  FND_MENU_ENTRIES_VL FMEV,
       FND_FORM_FUNCTIONS FF,
       FND_MENUS FM
 WHERE FMEV.MENU_ID = FM.MENU_ID
   AND FMEV.FUNCTION_ID = FF.FUNCTION_ID
   AND FF.FUNCTION_NAME = 'BNE_CREATE_DOCUMENT'
   AND FM.MENU_NAME LIKE 'XX%';
 
  BEGIN
 
    SELECT USER_ID
      INTO G_LAST_UPDATED_BY
      FROM FND_USER
     WHERE USER_NAME      = 'ORACLE12.1.0';
 
  G_LAST_UPDATE_LOGIN := G_LAST_UPDATED_BY;
 
    SELECT FUNCTION_ID
      INTO L_FUNCTION_ID
      FROM FND_FORM_FUNCTIONS
     WHERE FUNCTION_NAME = 'BNE_ADI_CREATE_DOCUMENT';
   
    FOR C_UPDATE_MENU_REC IN C_UPDATE_MENU
    LOOP
      BEGIN
        FND_MENU_ENTRIES_PKG.UPDATE_ROW( X_MENU_ID               =>   C_UPDATE_MENU_REC.MENU_ID,
                                         X_ENTRY_SEQUENCE        =>   C_UPDATE_MENU_REC.ENTRY_SEQUENCE,
                                         X_SUB_MENU_ID           =>   C_UPDATE_MENU_REC.SUB_MENU_ID,
                                         X_FUNCTION_ID           =>   L_FUNCTION_ID,
                                         X_GRANT_FLAG            =>   C_UPDATE_MENU_REC.GRANT_FLAG,
                                         X_PROMPT                =>   C_UPDATE_MENU_REC.PROMPT,
                                         X_DESCRIPTION           =>   C_UPDATE_MENU_REC.DESCRIPTION,
                                         X_LAST_UPDATE_DATE      =>   G_LAST_UPDATE_DATE,
                                         X_LAST_UPDATED_BY       =>   G_LAST_UPDATED_BY,
                                         X_LAST_UPDATE_LOGIN     =>   G_LAST_UPDATE_LOGIN
                                       );
      EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('Failed the update Menu '||C_UPDATE_MENU_REC.MENU_NAME);
      END;
    END LOOP;
    COMMIT;
  --  Compiling Security Profile
  DECLARE
  L_REQUEST_ID  NUMBER;
  BEGIN
      SELECT APPLICATION_ID,RESPONSIBILITY_ID
      INTO G_APPLICATION_ID,G_RESPONSIBILITY_ID
      FROM FND_RESPONSIBILITY_TL
     WHERE RESPONSIBILITY_NAME = 'System Administrator';

 Fnd_Global.apps_initialize(G_LAST_UPDATED_BY,G_RESPONSIBILITY_ID,G_APPLICATION_ID);

     L_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(
                                           APPLICATION => 'FND',
                                           PROGRAM     => 'FNDSCMPI',
                                           ARGUMENT1   => 'No');
    DBMS_OUTPUT.PUT_LINE ('Request ID: '||L_REQUEST_ID);                                      
  EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('Error While compiling Security Profile');
  END;
 
  END; 
/
 

Script to Update the form Function

DECLARE
  G_LAST_UPDATE_DATE DATE := SYSDATE;
  G_LAST_UPDATED_BY   NUMBER ;
  G_LAST_UPDATE_LOGIN NUMBER ;
   
    CURSOR C_UPDATE_FUNCTION
    IS
      SELECT FUNCTION_ID,
        WEB_HOST_NAME,
        WEB_AGENT_NAME,
        WEB_HTML_CALL,
        WEB_ENCRYPT_PARAMETERS,
        WEB_SECURED,
        WEB_ICON,
        OBJECT_ID,
        REGION_APPLICATION_ID,
        FORM_ID,
        REGION_CODE,
        FUNCTION_NAME,
        APPLICATION_ID,
        PARAMETERS,
        TYPE,
        USER_FUNCTION_NAME,
        DESCRIPTION,
        MAINTENANCE_MODE_SUPPORT,
        CONTEXT_DEPENDENCE,
        JRAD_REF_PATH
      FROM FND_FORM_FUNCTIONS_VL
      WHERE FUNCTION_NAME LIKE 'XX%';
  BEGIN
  SELECT USER_ID
  INTO G_LAST_UPDATED_BY
  FROM FND_USER
  WHERE USER_NAME      = 'ORACLE12.1.0';
  G_LAST_UPDATE_LOGIN := G_LAST_UPDATED_BY;
 
 
    FOR C_UPDATE_FUNCTION_REC IN C_UPDATE_FUNCTION
    LOOP
      BEGIN
        FND_FORM_FUNCTIONS_PKG.UPDATE_ROW ( X_FUNCTION_ID              =>  C_UPDATE_FUNCTION_REC.FUNCTION_ID,
                                               X_WEB_HOST_NAME            =>  C_UPDATE_FUNCTION_REC.WEB_HOST_NAME,
                                            X_WEB_AGENT_NAME           =>  C_UPDATE_FUNCTION_REC.WEB_AGENT_NAME,
                                            X_WEB_HTML_CALL            =>  'BneApplicationService',  
                                            X_WEB_ENCRYPT_PARAMETERS   =>  C_UPDATE_FUNCTION_REC.WEB_ENCRYPT_PARAMETERS,
                                            X_WEB_SECURED              =>  C_UPDATE_FUNCTION_REC.WEB_SECURED,
                                            X_WEB_ICON                 =>  C_UPDATE_FUNCTION_REC.WEB_ICON,
                                            X_OBJECT_ID                =>  C_UPDATE_FUNCTION_REC.OBJECT_ID,
                                            X_REGION_APPLICATION_ID    =>  C_UPDATE_FUNCTION_REC.REGION_APPLICATION_ID,
                                            X_REGION_CODE              =>  C_UPDATE_FUNCTION_REC.REGION_CODE,
                                            X_FUNCTION_NAME            =>  C_UPDATE_FUNCTION_REC.FUNCTION_NAME,
                                            X_APPLICATION_ID           =>  C_UPDATE_FUNCTION_REC.APPLICATION_ID,
                                            X_FORM_ID                  =>  C_UPDATE_FUNCTION_REC.FORM_ID,
                                            X_PARAMETERS               =>  C_UPDATE_FUNCTION_REC.PARAMETERS,
                                            X_TYPE                     =>  C_UPDATE_FUNCTION_REC.TYPE,
                                            X_USER_FUNCTION_NAME       =>  C_UPDATE_FUNCTION_REC.USER_FUNCTION_NAME,
                                            X_DESCRIPTION              =>  C_UPDATE_FUNCTION_REC.DESCRIPTION,
                                            X_LAST_UPDATE_DATE         =>  G_LAST_UPDATE_DATE,
                                            X_LAST_UPDATED_BY          =>  G_LAST_UPDATED_BY,
                                            X_LAST_UPDATE_LOGIN        =>  G_LAST_UPDATE_LOGIN,
                                            X_MAINTENANCE_MODE_SUPPORT =>  C_UPDATE_FUNCTION_REC.MAINTENANCE_MODE_SUPPORT,
                                            X_CONTEXT_DEPENDENCE       =>  C_UPDATE_FUNCTION_REC.CONTEXT_DEPENDENCE,
                                            X_JRAD_REF_PATH            =>  C_UPDATE_FUNCTION_REC.JRAD_REF_PATH
                                         );
      EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE ('Failed the update function '||C_UPDATE_FUNCTION_REC.FUNCTION_NAME);
      END;
    END LOOP;
   COMMIT;
  END;
  /

Dividing pipe delimited string into columns

Example :-

     String   -   CI-CT-WESTPORT-06880.02
Column1   -   CI
Column2   -   CT
Column3   -   WESTPORT
Column4   -    06880.02


Solution :-

 SELECT REGEXP_SUBSTR('CI-CT-WESTPORT-06880.02', '[^-]+', 1, 1) COLUMN1,
                REGEXP_SUBSTR('CI-CT-WESTPORT-06880.02', '[^-]+', 1, 2) COLUMN2,
                REGEXP_SUBSTR('CI-CT-WESTPORT-06880.02', '[^-]+', 1, 3) COLUMN3,
                REGEXP_SUBSTR('CI-CT-WESTPORT-06880.02', '[^-]+', 1, 4) COLUMN4
    FROM DUAL;


You can use regexp_substr only for (10g +).