Fndload scripts for WebAdi in R12

--To download Web ADI Integrators,use the below commands

FNDLOAD apps/$apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_TEST_INTG.ldt BNE_INTEGRATORS INTEGRATOR_ASN="PER" INTEGRATOR_CODE="<INTEGRATOR_CODE>"

--To upload Web ADI Integrators, use the below commands

FNDLOAD apps/$apps 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_TEST_INTG.ldt

Update Custom WebADI:

 After moving the custom webadi’s from one instance to another instance, whenever you query for your integrator,update option is disabled for that particular custom Integrators.

 To get enabled ‘Update’ use the below query.

UPDATE bne_integrators_b

SET source  = 'C'

WHERE integrator_code IN ('INTEGRATOR_CODE');

 

Responsibilities under user in Oracle Apps

SELECT r.responsibility_key,a.application_name, fa.APPLICATION_SHORT_NAME,u.user_name
    FROM fnd_user u,
         fnd_user_resp_groups ur,
         fnd_responsibility r,
         fnd_application_tl a,
         fnd_application fa
   WHERE  ur.responsibility_id = r.responsibility_id
         AND u.user_id = ur.user_id
         AND fa.APPLICATION_ID = a.APPLICATION_ID
         and R.APPLICATION_ID = a.APPLICATION_ID
         AND upper(u.user_name) = UPPER ('HCM')
         and a.LANGUAGE = 'US'
ORDER BY 1;

Apps initialize in R12

FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite

FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API.

utilize the following query to get the respective values

SELECT fnd.user_id ,
  fresp.responsibility_id,
  fresp.application_id,
  fresp.responsibility_name
FROM fnd_user fnd ,
  fnd_responsibility_tl fresp
WHERE fnd.user_name = 'HCM'
AND fresp.responsibility_name LIKE 'US%';

l_user_id is the fnd user ID
l_resp_id is the responsibility ID
l_resp_appl_id is the responsibility application ID.

EXEC fnd_global.APPS_INITIALIZE(l_user_id,l_resp_id,l_resp_appl_id);
  (OR)
BEGIN
  fnd_global.APPS_INITIALIZE(l_user_id,l_resp_id,l_resp_appl_id);
mo_global.init('appl_shrt_name');
--Ex :: exec  mo_global.init('AR');
END;

/

Another option is Help > Diagnostics > Examine and get the values from $profile session values.

To Initialize session

SELECT fnd_global.session_id,USERENV('sessionid') FROM dual;

INSERT INTO FND_SESSIONS VALUES
  (USERENV('sessionid'),TRUNC(sysdate)
  );

Script To Reset Password for Oracle Apps from Backend

DECLARE
  v_user_name    VARCHAR2(30):=upper('HCM'); --HCM is username of my oracle Apps.
  v_new_password VARCHAR2(30):='welcome1';
  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;

Oracle Global Temporary Tables

GLOBAL TEMPORARY

The data in a global temporary table is private,The data in a temporary table is visible only to the session that inserts the data into the table.

A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below)

ON COMMIT

The ON COMMIT clause is relevant only if you are creating a temporary table. This clause specifies whether the data in the temporary table persists for the duration of a transaction or a session.

DELETE ROWS

Specify DELETE ROWS for a transaction-specific temporary table (this is the default). Oracle will truncate the table (delete all its rows) after each commit.

PRESERVE ROWS

Specify PRESERVE ROWS for a session-specific temporary table. Oracle will truncate the table (delete all its rows) when you terminate the session.

Temporary Table Example

The following statement creates a temporary table flight_schedule for use in an automated airline reservation scheduling system. Each client has its own session and can store temporary schedules. The temporary schedules are deleted at the end of the session.

 Ex:
   CREATE GLOBAL TEMPORARY TABLE flight_schedule (
   startdate DATE,
   enddate DATE,
   cost NUMBER)
   ON COMMIT PRESERVE ROWS;
  
 Ex:
  CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  column1  NUMBER,
  column2  NUMBER)
  ON COMMIT DELETE ROWS;
 
 Ex:Using Dynamic SQL creating global temp table
  BEGIN
  EXECUTE immediate 'CREATE GLOBAL TEMPORARY TABLE xx.xx_temp_table   
  (booking_id                     NUMBER, 
   name                           VARCHAR2(100))
  ON COMMIT PRESERVE ROWS';
  EXCEPTION
  WHEN OTHERS THEN
  NULL;
  END;
  / 
 
 Ex:Using Dynamic SQL Drop global temp table
  BEGIN
  EXECUTE immediate 'drop table xx.xx_temp_table';
  EXCEPTION
  WHEN OTHERS THEN
  NULL;
  END;
  /