API To Create Users And Assign Responsibilities To Users

Below is the code to create users and adding responsibilities to the users through API

DECLARE
  v_user_name := 'test_user';
  v_session_id := sid;
  /*Pass here sid from Below  query-
  SELECT username
  ,      sid
  ,      serial#
  ,      status
  FROM   v$session
  WHERE  username = 'APPS'; */
BEGIN
  FOR i IN 1..99
    LOOP
      fnd_user_pkg.createuser
        (x_user_name => v_user_name||i
        ,x_owner => ''
        ,x_unencrypted_password => 'welcome9'
        ,x_session_number => v_session_id
        ,x_start_date => SYSDATE - 9
        ,x_end_date => SYSDATE + 99
        ,x_last_logon_date => SYSDATE - 9
        ,x_description => 'API User Creation'
        ,x_password_date => SYSDATE - 99
        ,x_password_accesses_left => 9999
        ,x_password_lifespan_accesses => 9999
        ,x_password_lifespan_days => 9999
        ,x_email_address => 'testuser@gmail.com'
        ,x_fax => ''
        ,x_customer_id => ''
        ,x_supplier_id => '');

      fnd_user_pkg.addresp
        (username => v_user_name||i
        ,resp_app => 'SYSADMIN'
        ,resp_key => 'SYSTEM_ADMINISTRATOR'
        ,security_group => 'STANDARD'
        ,description => 'Auto Assignment'
        ,start_date => SYSDATE - 99
        ,end_date => SYSDATE + 9999);
    END LOOP;
END;

Query to Find Responsibilities for a Given Concurrent Programs

SELECT DISTINCT fcpl.user_concurrent_program_name
,          fcp.concurrent_program_name
,          fapp.application_name
,          frg.request_group_name
,          fnrtl.responsibility_name
FROM fnd_request_groups frg
,          fnd_application_tl fapp
,          fnd_request_group_units frgu
,          fnd_concurrent_programs fcp
,          fnd_concurrent_programs_tl fcpl
,          fnd_responsibility fnr
,          fnd_responsibility_tl fnrtl
WHERE frg.application_id =fapp.application_id
AND   frg.application_id = frgu.application_id
AND   frg.request_group_id = frgu.request_group_id
AND   frg.request_group_id = fnr.request_group_id
AND   frg.application_id = fnr.application_id
AND   fnr.responsibility_id = fnrtl.responsibility_id
AND   frgu.request_unit_id = fcp.concurrent_program_id
AND   frgu.unit_application_id = fcp.application_id
AND   fcp.concurrent_program_id = fcpl.concurrent_program_id
AND   fcpl.user_concurrent_program_name like 'Sales%Order%Ackn%'
AND   fnrtl.language = 'us'
AND   fapp.language = 'us'

Onhand Quantity On a Given Date

Recentlry i have worked on one report, The main purpose of the report is , based on item, oraganizarion and subiventory it need to display the onhand qty for a gievn date. i used below code in formula column of report to calculate onhand qty on a given perticular date.

FUNCTION CF_LAST_BALFormula RETURN NUMBER IS
   v_onhand_qty   NUMBER;
   v_last_qty     NUMBER;
   v_target_qty   NUMBER;
BEGIN
   SELECT SUM(transaction_quantity)
   INTO   v_onhand_qty
   FROM   mtl_onhand_quantities_detail
   WHERE  inventory_item_id = :inventory_item_id
   AND    organization_id   = :organization_id
   AND    subinventory_code = :Subinventory;
  
   SELECT NVL(SUM(Transaction_quantity),0)
   INTO   v_last_qty
   FROM   mtl_material_transactions
   WHERE  inventory_item_id = :inventory_item_id
   AND    organization_id   = :organization_id
   AND    subinventory_code = :Subinventory
   AND trunc(NVL(transaction_date,creation_date))>= TRUNC(TO_DATE(:p_last_date,'RRRR/MM/DD'));

      v_target_qty:=(v_onhand_qty)-(v_last_qty);

   RETURN(v_target_qty);
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

Creation Of Master Organization

Today we will see creation of master organization.

Inventory Responsibility --> setup --> Organizations -->Organizations

1. 

Click on New

Enter Organization name and location and save your work.


Enter Name as "Inventory Organization" under organization classification and check the "Enabled" check box.

1. 

 Click On others button and select "Accounting Information"and OK.

1. 

Enter ledger ,legal entity and operating nit information and click on OK -- OK --Save.

1. 

Click On others button and select "Inventory Information"and OK.

1. 

Enter Proper information in "Organization Parameters" window . Its a 6 tabbed region window.

In Inventory Parameter tab -- > Master organization Name = Your newly created organization name.

1. 

Costing Information Tab --> costing method = Standard

1. 

Revision,Lot,Serial tab --> Starting serial number = 0009

1. 

Other Accounts Tab --> Enter accounts information. you will get these accounts information from your finance team.

1. 

Click on save . You will get one pop up message. read thoroughly and click on OK.


In later session we will discuss all the options available in inventory parameters window in detail.


Provide me comments if you like my post.