Script to set profile value at different levels

 Set profile value at SITE Level

declare 
   v_check            boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
   --exception
   --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'SITE'              
                                         , x_level_value              => null              
                                         , x_level_value_app_id   => null) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

 Set profile value at Application Level

declare 
   v_check           boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
   v_appl_name    varchar2(4)   := 'AR';
   v_appl_id         number;
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
     
   select application_id
     into v_appl_id
     from fnd_application
    where application_short_name = 'AR';
    --exception
    --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'APPL'               
                                         , x_level_value              => v_appl_id              
                                         , x_level_value_app_id   => null) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

 Set profile value at Responsibility Level

declare 
   v_check            boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
   v_resp_name    varchar2(240)   := 'Purchasing Super User';
   v_resp_id         number;
   v_resp_app_id number;
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
     
    select responsibility_id      
         , application_id   
      into v_resp_id      
         , v_resp_app_id   
    from fnd_responsibility_tl  
    where responsibility_name = v_resp_name ;
    --exception
    --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'RESP'               
                                         , x_level_value              => v_resp_id             
                                         , x_level_value_app_id   => v_resp_app_id) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;



 Set profile value at User Level

declare 
   v_check           boolean;
   v_profile_name varchar2(240) := 'HZ: Generate Party Number';
   v_profile           varchar2(240);
   v_value             varchar2(1)   := 'Y';
   v_user_name    varchar2(240)   := 'XX1234';
   v_user_id         number;
begin 
   --begin
   select profile_option_name
     into v_profile
     from fnd_profile_options_tl
    where language = 'US'
      and user_profile_option_name = v_profile_name ;
     
    select user_id       
      into v_user_id       
      from fnd_user  
     where user_name = v_user_name ;
    --exception
    --end;
     
   v_check := fnd_profile.save( x_name                     => v_profile              
                                         , x_value                      => v_value
                                         , x_level_name             => 'USER'               
                                         , x_level_value              => v_user_id             
                                         , x_level_value_app_id   => null) ; 
   if v_check then
      dbms_output.put_line('Profile '||v_profile_name||' updated with '||v_value);
      commit;
   else
      dbms_output.put_line('Error while updating Profile '||v_profile_name||' with value '||v_value);
   end if;
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm);
end;

Script To Delete Transaction(s)

Prerequisites :
 Check if 'Allow Transaction Deletion' flag is Yes, if no, check the flag for respective operating unit.
 Navigation : REceivables -> Setup -> System -> System Options
              >> Query for respective operating unit > Click on 'Trans and Customers' > Check for 'Allow Transaction Deletion' flag.
-------------------------------------------------------------------------------------------------------------
declare
   
    cursor cur_all_trx
    is            
    select to_number(hz.party_number) party_number,rct.org_id,rct.customer_trx_id.rct.trx_number
      from ra_customer_trx_all     rct
          ,hz_cust_accounts ha
          ,hz_parties hz
     where trx_number = 'I0061887'
       and ha.account_number = rct.interface_header_attribute4
       and hz.party_id = ha.party_id;
   
    v_msg_data      varchar2(4000)  := null;
    v_msg_count     number          := 0;
    v_msg_index     number          := 0;
    v_ret_status    varchar2(1)     := null;   
    v_message_tbl   arp_trx_validate.message_tbl_type;
    v_res           NUMBER := 1234 ;
    v_res_name      varchar2(240)  ;
    v_app           NUMBER := 5678 ;
    v_user          number := 9101 ;
begin
    dbms_output.put_line('Detele Transaction...');
   
    for c_rec in cur_all_trx loop
        dbms_output.put_line('   Transaction No.: '||c_rec.trx_number);
        dbms_output.put_line('   Transaction ID : '||c_rec.customer_trx_id);
        dbms_output.put_line('   Org ID         : '||c_rec.org_id);
        ----------------------------------------------------------------------------
        ---- Setting the org context for the particular session
        apps.mo_global.set_policy_context('S', c_rec.org_id);
        -- apps.mo_global.init('AR');
       
        select application_id
              ,responsibility_id
          into v_app
              ,v_res
          from fnd_responsibility_tl
         where responsibility_name = v_res_name ;

        ---- Setting the oracle applications context for the particular session
        apps.fnd_global.apps_initialize(v_user,v_res,v_app);    
        ----------------------------------------------------------------------------       
        v_ret_status := null;
        v_msg_count  := null;
        v_msg_data   := null;
      
        --update the Allow Transaction Deletion to Yes to Delete (As mentioned above, better to do it from application)
        update ar_system_parameters_all
           set invoice_deletion_flag ='Y'
         where  org_id = c_rec.org_id;

        ar_invoice_api_pub.delete_transaction(
                                                 p_api_name             => 'Delete_Transaction',
                                                 p_api_version          => 1.0,
                                                 p_init_msg_list        => fnd_api.g_true,
                                                 p_commit               => fnd_api.g_true,
                                                 p_validation_level     => fnd_api.g_valid_level_full,
                                                 p_customer_trx_id      => c_rec.customer_trx_id,
                                                 p_return_status        => v_ret_status,
                                                 p_msg_count            => v_msg_count,
                                                 p_msg_data             => v_msg_data,
                                                 p_errors               => v_message_tbl
                                             );

        if v_ret_status <> 'S' then
            dbms_output.put_line( '   Status: '||v_ret_status);
            for i in 1 .. v_msg_count loop
                apps.fnd_msg_pub.get( i, apps.fnd_api.g_false, v_msg_data, v_msg_index);
                dbms_output.put_line( '   Error : '||v_msg_data);
            end loop;
            dbms_output.put_line ('   '||v_msg_data);
        else
            dbms_output.put_line ('   Deleted.');
           
            -- Revert back to the original value for the deletion flag
            update ar_system_parameters_all
               set invoice_deletion_flag ='N'
             where  org_id = c_rec.org_id;

        end if;      
       
        dbms_output.put_line('--------------------');
        commit;       
    end loop;       
exception
    when others then
        dbms_output.put_line('Error : '||sqlerrm);
end; 
-----------------------------------------------------------------------------------------------------------

Note :- You cannot delete transactions that have activity against them (completed , Credit Notes which are applied against any Invoice)

View Other Users Request Output

R11

System Administrator à Profile à System

Search for profile à 'Concurrent: Report Access Level'

Level	Value	Result
Site	User	The user can only view the requests submitted by him
Responsibility	Responsibility	That user can also review the log and report output files from all requests submitted from the current responsibility
User	Responsibility	Any user of that responsibility can also view the log and report output files from all requests submitted by any other user of that responsibility

R12

Unfortunately in R12 profile 'Concurrent: Report Access Level' does not work, it is a bit more complex process to achieve this

In R12 this was replaced by Role Based Access Control. The UMX Role Based Access Control (RBAC) is to control who can view request output files.

Note :-  You should have SYSADMIN user access.

Functional Developer -> Core Services

 >> Search for 'Concurrent Requests'

Click on 'Concurrent Requests' and then on tab 'Object Instance Set'

Ø  Click Create Instance Set

 Ø  Give Name/Code/Description

Ø  For Predicate

è Enter below code if you want to see request output for all concurrent programs . 

&TABLE_ALIAS.request_id in(

select  cr.request_id

  from apps.fnd_concurrent_requests cr, apps.fnd_concurrent_programs cp

where cr.concurrent_program_id = cp.concurrent_program_id

     and cr.program_application_id = cp.application_id)

è Modify the above query based on your requirement.

n  Add and cp.concurrent_program_name  if you want to give access only for particular program name, etc.

Ø  'Apply' it and it will give the Confirmation with Code you have entered.

 >>> Now login as a SYSADMIN User.

Ø  Goto User Management > Role & Role Inheritance

è Click on 'Create Role'

      Ø  Category – Miscellaneous / Application – Application Object Library

      Ø  Role Code (Prefix UMX| will be added whatever you provide example : UMX|AKTEST)

Ø  Display Name/ Description

Ø  'Apply'

Ø  Click on 'Create Grant'

Ø  Provide Name / Description

Ø  Data Security ->  Object -> 'Concurrent Requests'

Ø  Click on 'Next'

Ø  Data Context Type -> 'Instance Set'

Ø  Instance Set  -> Instance you Created above, AKTEST

Ø  Click 'Next'

Ø  Set -> Set -> 'Request Operations'

 

Ø  Click on 'Next' -> Preview and then 'Finish'

Ø  It should give you the message for successful creation of Grant.

Known Issue

 

Sometimes it will give below Error. Just try 3-4 times, it should be fine. Sorry didn't dig much into it to find the exact reason behind it. Error Page

You have encountered an unexpected error. Please contact the System Administrator for assistance.

Goto 'User tab 

Ø  Search for the user you want to assign the grant.

Ø  Click  'Update'

Ø  Click 'Assign Role'

Ø  Search for Role you have created above 'View All Concurrent Requests outputs' (Code - UMX|AKTEST)

Ø  Give Justification

Ø  You can put active date from past to view previous requests outputs.

   Ø  DONE... :)

* Now assigned user can see the output of other user's request from the responsibility from  which that request had been launched.

>> You can also use the below script to assign the grant to a user.

begin

     wf_local_synch.PropagateUserRole(p_user_name => 'USERNAME'

                                                               ,p_role_name => 'UMX|AKTEST ');

     commit;

End;

Script to Give Grant (Read Only) for all Objects to a particular Schema

-- Please give the User Name in v_user variable

declare
cursor cur_grants(p_user varchar2)
is
    select 'GRANT '
               || decode(db.object_type,'TABLE','SELECT',
               'VIEW','SELECT',
               'EXECUTE')
                || ' ON '
                || decode(db.owner,'PUBLIC','',db.owner || '.')
                || '"'
                || db.object_name
                || '"'
                || ' TO '||p_user sql_stmt
     from all_objects db
   where db.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE',  'VIEW','FUNCTION')
   union
    select 'GRANT '
              || decode (ao2.object_type, 'TABLE', 'SELECT',
              'VIEW', 'SELECT',
              'EXECUTE')
              || ' ON '
              || decode(ao.owner,'PUBLIC','',ao.owner || '.')
              || '"'
              || ao.object_name
              || '"'
              || ' TO '||p_user sql_stmt
    from all_objects ao
           , all_objects ao2
           , dba_synonyms ds
  where ao.object_type = 'SYNONYM'
      and ao.object_name = ds.synonym_name
      and ao2.object_name = ds.table_name
      and ao2.object_type in ('TABLE','PACKAGE','PACKAGE BODY','PROCEDURE',  'VIEW','FUNCTION')
;

v_user varchar2(20) := 'USER_NAME';

begin

for i in cur_grants(v_user) loop

     execute immediate i.sql_stmt ;

     dbms_output.put_line('Command : '||i.sql_stmt);

end loop;

exception
     when others then
          dbms_output.put_line('Error: '||sqlerrm);
end;
/

Script to create Synonym for all Objects for a particular Schema

-- Please give the User Name in v_user variable

declare
cursor cur_synonym(p_user varchar2)
is
select 'CREATE SYNONYM '||p_user||'.'
           || db.object_name
           || ' FOR APPS.'
           || db.object_name sql_stmt
  from dba_objects db
where db.owner = 'APPS'
;

v_user varchar2(20) := 'USER_NAME';

begin

for i in cur_synonym(v_user) loop

     execute immediate i.sql_stmt ;

     dbms_output.put_line('Command : '||i.sql_stmt);

end loop;

exception
     when others then
          dbms_output.put_line('Error: '||sqlerrm);
end;
/