SQL Script to find list of tables updated today

DECLARE 
    sql_st VARCHAR2(1000);
    no_of_records NUMBER;
BEGIN 
    dbms_output.put_line ('Table|No of Records Changed');
    dbms_output.put_line ('------|----------------------------------');
    FOR C1 in (SELECT table_name 
FROM all_tables a 
WHERE  table_name not lIKE '%$%' 
AND table_name not lIKE '%_A' 
AND exists (SELECT 1 
FROM all_tab_columns b 
WHERE a.table_name = b.table_name 
AND b.column_name = 'LAST_UPDATE_DATE'
)
order by table_name) 
    LOOP 
    no_of_records := 0;
    sql_st := 'Select count(1) from '|| c1.table_name ||' where last_update_date > TRUNC(SYSDATE)';
    BEGIN
            EXECUTE IMMEDIATE sql_st INTO no_of_records;
            dbms_output.put_line (c1.table_name||'|'||no_of_records);
    EXCEPTION 
        WHEN OTHERS THEN 
        dbms_output.put_line (c1.table_name ||'|'|| SQLERRM);
    END;
END Loop;
EXCEPTION 
WHEN OTHERS THEN 
    dbms_output.put_line ('Exception : '||SQLERRM);
END;

Script to allow user to view workflow submitted for all

 

SELECT Text from wf_resources  WHERE Name = 'WF_ADMIN_ROLE'

If above SQL returns * , you can see all workflow submitted by others in Workflow Admin > Status Monitor Area.

If this is restricted for only your user, use below update statement to allow for all. 

UPDATE wf_resources set text='*' where name='WF_ADMIN_ROLE'

Script to Change FND User Password from DB

DECLARE
  lv_user_name    VARCHAR2(100):= 'SANDEEP';
  lv_owner        VARCHAR2(10) := 'CUST';
  lv_password     VARCHAR2(20) := 'Oracle123';
 ln_user_id         NUMBER;
BEGIN
  /*Create FND User*/
   BEGIN
                          SELECT user_id
                          INTO ln_user_id
                          FROM fnd_user
                          WHERE user_name=lv_user_name;
    EXCEPTION
                        WHEN no_data_found THEN
                        dbms_output.put_line('Did not create user('||lv_user_name||') : '||sqlerrm);
    END;

    fnd_user_pkg.updateuser
                                    ( x_user_name                                                =>lv_user_name,
                                      x_owner                                                        =>lv_owner,
                                      x_unencrypted_password                             =>lv_password
                        );
 
    IF ln_user_id IS NOT NULL THEN
      dbms_output.put_line('Successfully Updated  user password  : '||lv_user_name);
      dbms_output.put_line('user id = '||ln_user_id);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Error in  Create user :'||lv_user_name||'>>'||SQLERRM);
END;

List of Programs in Request Set

--SQL – List of Request Set Programs
SELECT USER_REQUEST_SET_NAME,
  a.REQUEST_SET_ID,
  Sequence,
  b.CONCURRENT_PROGRAM_ID,
  c.USER_CONCURRENT_PROGRAM_NAME
FROM FND_REQUEST_SETS_VL a,
  FND_REQUEST_SET_PROGRAMS b,
  FND_CONCURRENT_PROGRAMS_VL c
WHERE 1=1
--AND USER_REQUEST_SET_NAME LIKE '%RequestSetname%'
AND a.REQUEST_SET_ID        = B.REQUEST_SET_ID
AND b.concurrent_program_id = c.concurrent_program_id
ORDER BY USER_REQUEST_SET_NAME,sequence ;

Script to find Form Personlizations

SELECT FORM_NAME,
  FUNCTION_NAME,
  Description,
  SEQUENCE,
  CONDITION
FROM FND_FORM_CUSTOM_RULES
WHERE ENABLED = 'Y';

SQL to find OAF personalizations

SELECT JDR_MDS_INTERNAL.GETDOCUMENTNAME(PATH_DOCID) DOCUMENT_ID,
  jdr_mds_internal.exportDocumentAsXML(jdr_mds_internal.getDocumentName(path_docid)) personalization_xml
FROM jdr_paths,
  jdr_attributes
WHERE PATH_DOCID = ATT_COMP_DOCID
AND path_name    = SUBSTR(att_value,INSTR(att_value, '/', -1, 1)+1)
AND att_comp_seq = 0
AND ATT_NAME     = 'customizes'
AND ATT_VALUE    = ATT_VALUE
AND jdr_mds_internal.getDocumentName(path_docid) LIKE '%/responsibility/53621%'


This would return all OAF personalization for responsibility id 53621.

Regular Expression for Date Format DD-MON-YYYY HH24:MI:SS

Use below regular expression to validate if given string is in specific date format.

select COUNT(1)  from DUAL where regexp_like('12-JUN-2016 23:34:59','^(\d){2}-[A-Z]{3}-(\d){4}\s(\d){2}:(\d){2}:(\d){2}$')
1

select COUNT(1)  from DUAL where regexp_like('121-JUN-2016 23:34:59','^(\d){2}-[A-Z]{3}-(\d){4}\s(\d){2}:(\d){2}:(\d){2}$')
0


=======================

^          : Starting String
$          : Ending String
{n}      : Execute last rule n time
[A-Z]   : All CAP Alphabets check
\d         :  digit check
\s          : Space

Script to find Template and associated JSP file

SELECT 

ida.access_name,
ida.file_name,
DECODE(ida.site_id, 1, 'All', s.msite_name) site_name,
ida.default_msite,
ida.language_code,
ida.default_language
FROM IBE_DSP_ATTACHMENTS_V ida, ibe_msites_tl s
WHERE m.site_id = s.msite_id and file_name = ‘ibeSeededPage.jsp’; 

Once identify the target JSP page use above SQL to find the associated program access name.

Create a custom version of seeded JSP (i.e. xxskpibeSeededPage.jsp)

Login to iStore Administrator Responsibility >> Advanced >> Template Manager 

Search the program access name with the out of above SQL 

Update >> Add new file >> provide custom file name for all countries and US language. 

Now place the custom file at OA_HTML file path and compile JSP (Click here for compilation and migrations)

SQL - Menu and Functions assigned to Responsibilities

SELECT a.seq,
  a.ENTRY_SEQUENCE,
  (SELECT menu_name FROM fnd_menus WHERE menu_id = a.menu_id
  ) menu,
  (SELECT menu_name FROM fnd_menus WHERE menu_id = a.sub_menu_id
  ) sub_menu,
  (SELECT FUNCTION_NAME
  FROM fnd_form_functions
  WHERE FUNCTION_ID = a.FUNCTION_ID
  ) function_name ,
  prompt,
  description
FROM
  (SELECT c.*
  FROM
    (SELECT level seq,
      ENTRY_SEQUENCE,
      x.menu_id,
      sub_menu_id,
      function_id,
      prompt,
      description
    FROM fnd_menu_entries_vl x
      START WITH x.menu_id =
      (SELECT menu_id
      FROM fnd_menus
      WHERE menu_id=
        (SELECT menu_id
        FROM fnd_responsibility_vl
        WHERE responsibility_name=:Responsibility_Name
        )
      )
      CONNECT BY prior sub_menu_id = x.menu_id
    ) c
  WHERE NOT EXISTS
    (SELECT 1
    FROM FND_RESP_FUNCTIONS
    WHERE responsibility_id =
      (SELECT responsibility_id
      FROM fnd_responsibility_tl
      WHERE responsibility_name=:Responsibility_Name
      )
    AND action_id = c.menu_id
    AND rule_type ='M'
    )
  AND NOT EXISTS
    (SELECT 1
    FROM FND_RESP_FUNCTIONS
    WHERE responsibility_id =
      (SELECT responsibility_id
      FROM fnd_responsibility_tl
      WHERE responsibility_name=:Responsibility_Name
      )
    AND action_id = c.function_id
    AND rule_type ='F'
    )
  ) A
ORDER BY 1,2,3

SQL - Concurrent Programs

Concurrent Program Executable Details- 
SELECT application_name,
  user_concurrent_program_name,
  meaning Program_Type,
  output_file_type output_format,
  --  concurrent_program_id,
  --  cp.application_id,--ce.*,
  executable_name,
  execution_file_name
FROM FND_CONCURRENT_PROGRAMS_VL cp ,
  FND_EXECUTABLES_FORM_V ce ,
  fnd_lookup_values flv
WHERE cp.executable_id       = ce.executable_id
AND ce.execution_method_code = lookup_code
AND LOOKUP_TYPE              = 'CP_EXECUTION_METHOD_CODE'
AND user_concurrent_program_name LIKE '%ProgramNAME%'

List of Concurrent Program  assigned to Responsibilities -

SELECT DISTINCT USER_CONCURRENT_PROGRAM_NAME,

  CONCURRENT_PROGRAM_NAME,
  z.responsibility_name,
  REQUEST_GROUP_NAME,
  application_name,
  REQUEST_GROUP_CODE
FROM FND_REQUEST_GROUP_UNITS x ,
  FND_CONCURRENT_PROGRAMS_VL y ,
  FND_RESPONSIBILITY_VL Z,
  FND_REQUEST_GROUPS FRG,
  FND_APPLICATION_VL fa
WHERE request_unit_id    = y.concurrent_program_id
AND unit_application_id  = y.application_id
AND X.REQUEST_GROUP_ID   = Z.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID   = Z.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = Z.REQUEST_GROUP_ID
AND fa.application_id    = frg.application_id
AND Y.USER_CONCURRENT_PROGRAM_NAME LIKE '%ProgramName%'
  -- AND responsibility_name LIKE '%'
ORDER BY 1,2;

SQL - Find Last accessed date of responsibility by USER

SELECT FU.USER_NAME,

  FRT.RESPONSIBILITY_NAME,

  MAX(FL.START_TIME),

  MAX(fl.login_id)

FROM FND_LOGINS FL,

  FND_LOGIN_RESPONSIBILITIES FLR,

  FND_USER FU,

  FND_RESPONSIBILITY_TL FRT

WHERE FL.LOGIN_ID         = FLR.LOGIN_ID

AND FL.USER_ID            = FU.USER_ID

AND FU.USER_NAME          ='OA_LEARNINIG'

AND FRT.RESPONSIBILITY_ID = FLR.RESPONSIBILITY_ID

GROUP BY FU.USER_NAME,

  FRT.RESPONSIBILITY_NAME

ORDER BY 3 DESC ;

PN –  System captures login date only from core forms. 

SQL - Transaction Control Flag Validation at Project/Task Level

FUNCTION OAL_TXN_CONTROL(p_project_id NUMBER,p_task_id NUMBER, P_EXPENDITURE_TYPE VARCHAR2,P_EXPENDITURE_CATEGORY VARCHAR2,P_EXPENDITURE_ITEM_DATE DATE)
RETURN VARCHAR2
AS
    lv_txn_flag VARCHAR2(10);
    ln_count    NUMBER;
    LD_DATE DATE;
    LV_CHRGEFLAG VARCHAR2(10);
    LN_ACTIVE    NUMBER;
    lv_exp varchar2(100);
lv_txn_level VARCHAR2(10);
  BEGIN
     
   
SELECT limit_to_txn_controls_flag
      INTO lv_txn_flag
      FROM PA_TASKS A
      WHERE PROJECT_ID = P_PROJECT_ID
      AND task_id      = P_task_id ;

      IF LV_TXN_FLAG   = 'Y' THEN -- IF 100
   
        SELECT COUNT(1)
                INTO ln_count
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1);
       
IF LN_COUNT      <> 0 THEN -- IF type exists -- IF 110
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <>0 THEN  -- IF 120
            RETURN 'TRUE';
          ELSE
            RETURN 'T';
          END IF;   -- IF 120

        ELSE -- not exists 110
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE TASK_ID = P_task_id
              AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         
 IF LN_COUNT <> 0 THEN -- IF 120
             RETURN 'TRUE';
          ELSE
             RETURN 'T';
          END IF; -- 120
        END IF; -- 110
      ELSE
-- check if any record exists in control table else call PA level vaidation
SELECT COUNT(1)
                INTO ln_count        
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id;

IF ln_count <> 0 THEN
        SELECT COUNT(1)
                INTO ln_count
         
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;

        IF LN_COUNT      <> 0 THEN -- IF type exists -- 210
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE TASK_ID = P_task_id
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <> 0 THEN  -- 220
            RETURN 'T';
          ELSE
            RETURN 'TRUE';
          END IF;  -- 220
   
ELSE -- not exists
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE TASK_ID = P_task_id
          AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         
          IF ln_count <> 0 THEN  -- 220
            RETURN 'T';
          ELSE
            RETURN 'TRUE';
          END IF;  -- 220

        END IF; --210
ELSE -- Project Level Validations
SELECT limit_to_txn_controls_flag
 INTO lv_txn_flag
 FROM PA_PROJECTS_ALL A
 WHERE PROJECT_ID = P_PROJECT_ID;
 IF LV_TXN_FLAG   = 'Y' THEN
   
        SELECT COUNT(1)
                INTO ln_count
         
        FROM pa_transaction_controls pc
        WHERE project_id = P_project_id
AND task_id is NULL
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1);
       
IF LN_COUNT      <> 0 THEN -- IF type exists
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE project_id = P_project_id
AND task_id is NULL
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <>0 THEN
            RETURN 'TRUE';
          ELSE
            RETURN 'P';
          END IF;

        ELSE -- not exists
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE project_id = P_project_id
AND task_id is NULL
              AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'Y'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         

          IF ln_count <>0 THEN
            RETURN 'TRUE';
          ELSE
            RETURN 'P';
          END IF;

        END IF;

      ELSE
   
        SELECT COUNT(1)
                INTO ln_count
         
        FROM pa_transaction_controls pc
       WHERE project_id = P_project_id
AND task_id is NULL
        AND EXPENDITURE_TYPE = P_expenditure_type
        AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1);
       
IF LN_COUNT      <> 0 THEN -- IF type exists
          SELECT count(1)
        INTO
        ln_count
        FROM pa_transaction_controls pc
        WHERE project_id = P_project_id
AND task_id is NULL
          AND EXPENDITURE_TYPE = P_EXPENDITURE_TYPE
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
       
       
          IF ln_count <>0 THEN
            RETURN 'P';
          ELSE
            RETURN 'TRUE';
          END IF;

        ELSE -- not exists
          SELECT COUNT(1)
          INTO ln_count
          FROM pa_transaction_controls pc
          WHERE project_id = P_project_id
AND task_id is NULL
          AND EXPENDITURE_CATEGORY = P_EXPENDITURE_CATEGORY
          AND CHARGEABLE_FLAG= 'N'
          AND P_expenditure_item_date BETWEEN pc.start_date_active AND NVL(pc.end_date_active,SYSDATE+1) ;
         
 IF ln_count <>0 THEN
            RETURN 'P';
          ELSE
            RETURN 'TRUE';
          END IF;

        END IF;
    END IF;
END IF;
END IF;  

END ;


Return Values - 

• TRUE - No Failure
• P - Failed at Project Level transaction control 
• T - Failed at Task Level transaction control 

SQL - Concurrent Program Executable Details

SELECT
  application_name,
  user_concurrent_program_name,
  meaning Program_Type,
  output_file_type output_format,
  --  concurrent_program_id,
  --  cp.application_id,--ce.*,
  executable_name,
  execution_file_name
FROM
  FND_CONCURRENT_PROGRAMS_VL cp ,
  FND_EXECUTABLES_FORM_V ce ,
  fnd_lookup_values flv
WHERE
  cp.executable_id           = ce.executable_id
AND ce.execution_method_code = lookup_code
AND lookup_type              = 'CP_EXECUTION_METHOD_CODE'
AND user_concurrent_program_name LIKE '%Program Name%'
 ;

What is Virtual Private Database (VPD)

VPD is new Oracle Database feature to enable policies on database objects ( i.e Table, Synonyms, View etc) This is used to control data access for security reasons.

 

What actually VPD does >> this append additional where condition to objects based on defined policies.  

 

How to use VPD>> below are the steps used to implement VPD in system.

1)     Identify the object:  Lest take APPS.PO_HEADERS_ALL (Synonym) for VPD testing.  

2)     Create the function: Create a function ( or package.function) which will return the required filter condition

3)     Add to policy – Use standard oracle API to create a policy for created function ( step 2)

  

Example –

Step#1) Assume PO_HEDAERS_ALL has 100 records. Will update DFF Column (Attribute1) =’Sandeep’ for any 10 records.

Requirement is  to restrict data having DFF value as ‘Sandeep’ for schema ‘xxxxx’.

 

Step#2) Created function Sandeep

 

 

 

 

 
 

 

 Step#3) 

 

 

 

 

 

 

  

Now run the below statement –

 

APPS Schema

Select count (1) from PO_Headers_all  

>> 100 rows selected

 

XXXXX Schema

Select count (1) from PO_Headers_all  

>> 10 rows selected

 

APPSQUERY Schema

Select count (1) from PO_Headers_all  

>> 100 rows selected

 

 

VPD is one of the major changes in MOAC concepts in R12.

 

SQL to find XML Files (RTF/Data Template/RTF ) Details

Given SQL is to fetch the files details uploaded through XML Publisher Administrator Responsibility

 

SELECT

 a.application_short_name,

  a.data_source_code,

  b.lob_code,

  b.lob_type,

  b.file_name,

  b.last_update_date

FROM

   XDO_DS_DEFINITIONS_VL a,

   XDO_LOBS b

WHERE a.data_source_code    =b.lob_code

AND a.application_short_name=’XXXXX’

AND a.data_source_code      =:ConCurrentProgram or Data Defintion Code;

;

 
 

Concurrent Program Status

/* Query to check program status and completion time*/


SELECT fcr.REQUEST_ID,
  FU.USER_NAME,
  f.responsibility_name,
  fcr.USER_CONCURRENT_PROGRAM_NAME,
  DECODE(fcr.PHASE_CODE,'C','Completed','R','Running','E','Error','P','Pending','Other') PHASE,
  DECODE(fcr.STATUS_CODE,'C','Normal','E','Error','G','Warning','X','Terminated','Q','Sacheduled',fcr.STATUS_CODE)STATUS ,
  TO_CHAR(fcr.ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') STARTED_AT,
  TO_CHAR(fcr.ACTUAL_COMPLETION_DATE,'DD-MON-YYYY HH24:MI:SS') ENDS_ON,
  ROUND((NVL(fcr.ACTUAL_COMPLETION_DATE,SYSDATE)-fcr.ACTUAL_START_DATE)*24*60) "Duration (mins)",
  fcr.argument_text,fo.file_type,fo.file_size,fo.file_name,LOGFILE_NAME, LOGFILE_NODE_NAME, OUTFILE_NAME,OUTFILE_NODE_NAME
FROM FND_CONCURRENT_REQUESTS fcr1,
  FND_CONC_REQ_SUMMARY_V FCR,
  FND_USER FU,
  fnd_conc_req_outputs fo,
  fnd_responsibility_vl f
WHERE FCR.REQUESTED_BY=FU.USER_ID
AND fcr.request_id = fo.concurrent_request_id(+)
and f.responsibility_id =  fcr.responsibility_id
and fcr1.REQUEST_ID = fcr.REQUEST_ID
AND USER_CONCURRENT_PROGRAM_NAME like ('%Programname%')
--and user_name like '%SANDEEP%'
ORDER BY NVL(fcr.ACTUAL_START_DATE,SYSDATE-1000) DESC;

Update MIME type

There are few file formats available in oracle EBS to support concurrent program output.

Each file format is having standard Mime Type associated and being stored in FND_MIME_TYPES_TL Table, which can be updated to any other mime type using below script –

UPDATE fnd_mime_types_tl

SET    mime_type = 'application/vnd.ms-excel'

,      description = 'Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language'

,      last_updated_by = 0

,      last_update_date = sysdate

WHERE  file_format_code = 'PCL'

AND   mime_type = 'application/vnd.hp-PCL';

COMMIT;

Assign Responsibility using database

Requirement – To assign any responsibility to a fnd user using database. This is useful if we does not have sysadmin responsibility.

Solution  - Following API can be used to assign any responsibility to a user

  fnd_user_resp_groups_api.Insert_Assignment

                                    ( user_id                                   =>ln_user_id,

                                    responsibility_id                      => c1.responsibility_id,

                                    responsibility_application_id =>c1.responsibility_application_id,

                                    start_date                               =>SYSDATE,

                                    end_date                                 =>SYSDATE+1,

                                    description                              =>'null');

Example – Assign system administrator responsibility to user sandeep

DECLARE
  ln_user_id      NUMBER;

  CURSOR curr_add_resp
  is
    SELECT distinct responsibility_id,
      responsibility_application_id,
      security_group_id
    from fnd_user_resp_groups
    WHERE responsibility_id IN
      (SELECT responsibility_id
      from fnd_responsibility_vl
      WHERE responsibility_name IN ('System Administrator','Functional Administrator')
      )  
  ;
BEGIN  
              /* Fetch user id for fnd user */
              SELECT user_id
              INTO ln_user_id
              FROM fnd_user
              WHERE user_name='SKP';
             
              /*Responsibility Assignments */
              FOR c1 IN  curr_add_resp LOOP
                        BEGIN
                          fnd_user_resp_groups_api.Insert_Assignment
                                                            ( user_id =>ln_user_id,
                                                            responsibility_id => c1.responsibility_id,
                                                            responsibility_application_id =>c1.responsibility_application_id,
                                                            start_date =>sysdate,
                                                            end_date =>SYSDATE+100,
                                                            description =>'null');
                        EXCEPTION
                        WHEN OTHERS THEN
                          dbms_output.put_line('Error in Responsibility Assignment'||SQLERRM);
                        END;
               END LOOP;
EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line('Error in Responsibility Assignment'||SQLERRM);
END;   

Create FNDUSER From database

Requirement – To create FNDUSER without having access to application.

Solution  - Following API can be used to create a FND USER.

fnd_user_pkg.createuser

                                    ( x_user_name =>lv_user_name,

                                      x_owner        =>lv_owner,

                                      x_unencrypted_password     =>lv_password );

Example – create FNDUSER “Sandeep” using database.

DECLARE

  lv_user_name    VARCHAR2(100):= 'SANDEEP';

  lv_owner        VARCHAR2(10) := 'CUST';

  lv_password     VARCHAR2(20) := 'sandeep'; 
 ln_user_id         NUMBER;

BEGIN

  /*Create FND User*/ 

    fnd_user_pkg.createuser

                                    ( x_user_name                                                =>lv_user_name,

                                      x_owner                                                        =>lv_owner,

                                      x_unencrypted_password                             =>lv_password

                        );

    BEGIN

                          SELECT user_id

                          INTO ln_user_id

                          FROM fnd_user

                          WHERE user_name=lv_user_name;  

    EXCEPTION

                        WHEN no_data_found THEN

                          dbms_output.put_line('Did not create user('||lv_user_name||') : '||sqlerrm);

    END;

    IF ln_user_id IS NOT NULL THEN

      dbms_output.put_line('Successfully created user : '||lv_user_name);

      dbms_output.put_line('user id = '||ln_user_id);

    END IF;

EXCEPTION

    WHEN OTHERS THEN

    dbms_output.put_line('Error in  Create user :'||lv_user_name||'>>'||SQLERRM);

END;

Setting Org Context in R12 ( MOAC Context )

Oracle Apps tables ending with ALL holds transaction data for multiple org ( Operating Units).
In 11i we had views on these tables. It requires setting context in order to fetch data from these views.This  is for security concerns as these objects holds transaction details.

This is one of major difference in application architecture between 11i and R12.
In R12 oracle uses VPD (Virtual Private Database) to secure these transactional data.We shall discuss about VPD and MOAC concepts in other post.  

In order to retrieve data from transactional objects, set policy context first ( as below ) -

BEGIN
      MO_GLOBAL.set_policy_context ('MODE' char(1),Org_ID number);
END;

MODE - This is  either "S" - For Single Operating Unit
                                 "M" - For Multiple Operating Unit

Org_ID - Operating unit (Value from column ORG_ID in all transactional objects)
                This is mandatory for "S" mode.

Example -


BEGIN
      MO_GLOBAL.set_policy_context ('S' ,204);
END;