Get On hand / Available to Transact / Available to Reserve Quantities in R12

DECLARE

v_api_return_status  VARCHAR2 (1);

v_qty_oh             NUMBER;

v_qty_res_oh         NUMBER;

v_qty_res            NUMBER;

v_qty_sug            NUMBER;

v_qty_att            NUMBER;

v_qty_atr            NUMBER;

v_msg_count          NUMBER;

v_msg_data           VARCHAR2(1000);

v_inventory_item_id  VARCHAR2(250) := '1234';

v_organization_id    VARCHAR2(10)  := '567';

BEGIN

inv_quantity_tree_grp.clear_quantity_cache;

DBMS_OUTPUT.put_line ('Transaction Mode');

DBMS_OUTPUT.put_line ('Onhand For the Item :'|| v_inventory_item_id );

DBMS_OUTPUT.put_line ('Organization        :'|| v_organization_id);

apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES

(p_api_version_number  => 1.0,

 p_init_msg_lst        => apps.fnd_api.g_false,

 x_return_status       => v_api_return_status,

 x_msg_count           => v_msg_count,

 x_msg_data            => v_msg_data,

 p_organization_id     => v_organization_id,

 p_inventory_item_id   => v_inventory_item_id,

 p_tree_mode           => apps.inv_quantity_tree_pub.g_transaction_mode,

 p_onhand_source       => 3,

 p_is_revision_control => FALSE,

 p_is_lot_control      => FALSE,

 p_is_serial_control   => FALSE,

 p_revision            => NULL,

 p_lot_number          => NULL,

 p_subinventory_code   => NULL,

 p_locator_id          => NULL,

 x_qoh                 => v_qty_oh,

 x_rqoh                => v_qty_res_oh,

 x_qr                  => v_qty_res,

 x_qs                  => v_qty_sug,

 x_att                 => v_qty_att,

 x_atr                 => v_qty_atr);

DBMS_OUTPUT.put_line ('on hand Quantity                :'|| v_qty_oh);

DBMS_OUTPUT.put_line ('Reservable quantity on hand     :'|| v_qty_res_oh);

DBMS_OUTPUT.put_line ('Quantity reserved               :'|| v_qty_res);

DBMS_OUTPUT.put_line ('Quantity suggested              :'|| v_qty_sug);

DBMS_OUTPUT.put_line ('Quantity Available To Transact  :'|| v_qty_att);

DBMS_OUTPUT.put_line ('Quantity Available To Reserve   :'|| v_qty_atr);

END;

Standard Function To Get Nextval for a sequence

Package Name : FND_SEQNUM
Function Name : GET_NEXT_AUTO_SEQ

So If Seq Name : XXX_ITEM_ID_S

Then next value can be fetched by simply using of

FND_SEQNUM.GET_NEXT_AUTO_SEQ ('XXX_ITEM_ID_S');

Available to Transact/Reserve quantity API IN 11i

DECLARE
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   l_item_id               NUMBER;
   l_organization_id       NUMBER;
   l_qty_on_hand           NUMBER;
   l_res_qty_on_hand       NUMBER;
   l_avail_to_tnsct        NUMBER;
   l_avail_to_reserve      NUMBER;
   l_qty_reserved          NUMBER;
   l_qty_suggested         NUMBER;
   l_lot_control_code      BOOLEAN;
   l_serial_control_code   BOOLEAN;
BEGIN
   -- Set the org context
    FND_GLOBAL.apps_initialize ( user_id => 70028, resp_id => 53774, resp_appl_id => 660,security_group_id => 0);
   inv_quantity_tree_grp.clear_quantity_cache; -- Clear Quantity cache  
   -- Set the variable values
   l_item_id := 1188623;
   l_organization_id := 344;
   l_lot_control_code := FALSE;  --Only When Lot number is passed  TRUE else FALSE
   l_serial_control_code := FALSE;
   -- Call API
   inv_quantity_tree_pub.query_quantities
               (p_api_version_number       => 1.0
               ,p_init_msg_lst             => NULL
               ,x_return_status            => x_return_status
               ,x_msg_count                => x_msg_count
               ,x_msg_data                 => x_msg_data
               ,p_organization_id          => l_organization_id
               ,p_inventory_item_id        => l_item_id
               ,p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode
               ,p_is_revision_control      => FALSE
               ,p_is_lot_control           => l_lot_control_code-- is_lot_control,
               ,p_is_serial_control        => l_serial_control_code
               ,p_revision                 => NULL              -- p_revision,
               ,p_lot_number               => NULL              -- p_lot_number,
               ,p_lot_expiration_date      => SYSDATE
               ,p_subinventory_code        => NULL              -- p_subinventory_code,
               ,p_locator_id               => NULL              -- p_locator_id,
               --,p_cost_group_id            => NULL          
               --,p_onhand_source            => NULL
               ,x_qoh                      => l_qty_on_hand     -- Quantity on-hand
               ,x_rqoh                     => l_res_qty_on_hand --reservable quantity on-hand
               ,x_qr                       => l_qty_reserved
               ,x_qs                       => l_qty_suggested
               ,x_att                      => l_avail_to_tnsct  -- available to transact
               ,x_atr                      => l_avail_to_reserve-- available to reserve
               );
   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || l_qty_on_hand);
   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || l_qty_reserved);
   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || l_qty_suggested);
   DBMS_OUTPUT.put_line ('Available to Transact: ' || l_avail_to_tnsct);
   DBMS_OUTPUT.put_line ('Available to Reserve: ' || l_avail_to_reserve);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;

Multi-Org or multiple organization access (MOAC) in R12

What is MOAC?

Multi-Org or multiple organization access (MOAC) is basically an ability to access multiple operating units from a single application responsibility.

Why it has been created?

Prior to R12, end users use to toggle / switch / change responsibilities in order to do transactions (like invoice / payment processing in AP) in different operating units. This is a very time consuming and inefficient way of recording transactions when you have 100s of operating units specially Internet based organizations who have worldwide operations in almost all the countries.

To address this, a new feature in R12 has been introduced in which user can switch between operating units within a responsibility something similar to “Change Organization” feature in inventory. Prior to R12, user would have to switch responsibilities in order to enter transactions in respective operating units (tagged to the responsibility).

What are its advantages?

• Multi-Org Access Control (MOAC) enables companies that have implemented a Shared Services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications responsibility.
• This increases the productivity of Shared Service Centers, as users no longer have to switch application responsibilities when processing transactions for multiple operating units at a time.
• Ability to view data from multiple operating units from a single responsibility, gives users more information. This enables them to make better decisions.

The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them.

SELECT   psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
FROM     PER_SECURITY_PROFILES psp,
         PER_SECURITY_ORGANIZATIONS pso,
         HR_OPERATING_UNITS hou
WHERE    pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
         AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

There are three Profile Options you need to be aware of related to Multi-Org that should be set at the Responsibility Level.

• MO: Security Profile– Always evaluated first.
• MO: Operating Unit– Secondary priority being evaluated after ‘MO: Security Profile’
• MO: Default Operating Unit– Sets the default Operating Unit for transactions when running under a Security Profile.

How it is done in R12?

In Release 12, one creates a Security Profile and assigns as many operating units as you required. One can tie that security profile to a single responsibility using a profile option called MO: Security Profile. For example, you could assign the security profile to the EMEA Payables responsibility to allow that responsibility to process invoices across all operating units.

In Release 12, define a security profile in HR using the Security profile form or the Global Security profile form, and assign all of the operating units that one would want a responsibility to access. The one needs to run a concurrent request called “Run Security List Maintenance” from HR which will make those security profile available and allow one to assign them to a responsibility via a profile option called MO: Security Profile.

One can define another profile option called MO: Default Operating Unit which is optional and allows one to specify a default operating unit that will be the default when you open different subledger application forms.

Know your Concurrent Program’s Performance

Know your Concurrent Program’s Performance

The below query will give you the time taken to execute the concurrent Programs with the latest concurrent programs with least execution time comes first.

select
      f.request_id ,
      pt.user_concurrent_program_name user_conc_program_name,
      f.actual_start_date start_on,
      f.actual_completion_date end_on,
      floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)
        || ' HOURS ' ||
        floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)
        || ' MINUTES ' ||
        round((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600 -
        (floor((((f.actual_completion_date-f.actual_start_date)
        *24*60*60) -
        floor(((f.actual_completion_date-f.actual_start_date)
        *24*60*60)/3600)*3600)/60)*60) ))
        || ' SECS ' time_difference,
      p.concurrent_program_name concurrent_program_name,
      decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase,
      f.status_code
from  apps.fnd_concurrent_programs p,
      apps.fnd_concurrent_programs_tl pt,
      apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
      and f.program_application_id = p.application_id
      and f.concurrent_program_id = pt.concurrent_program_id
      and f.program_application_id = pt.application_id
      AND pt.language = USERENV('Lang')
      and f.actual_start_date is not null
order by
      f.actual_start_date desc,
      f.actual_completion_date-f.actual_start_date ;

API scripts to Create/Update/delete the Category Set/Category Assignment for a Item

API scripts to Create/Update/delete the Category Set/Category Assignment for a Item
/*****************************************************************************
 Script to Create the Category for an item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);
   l_category_id                                     NUMBER;
   l_category_set_id                                 NUMBER;
   l_inventory_item_id                               NUMBER;
   l_organization_id                                 NUMBER;
BEGIN
   SELECT mcs_tl.category_set_id
     INTO l_category_set_id
     FROM mtl_category_sets_tl mcs_tl
    WHERE mcs_tl.category_set_name = '<category_set_name>';

   SELECT mcb.category_id
     INTO l_category_id
     FROM mtl_categories_b mcb
    WHERE mcb.segment1 = '<category_name>'
      AND mcb.structure_id = (SELECT mcs.structure_id
                                FROM mtl_category_sets_b mcs
                               WHERE mcs.category_set_id = l_category_set_id);

   SELECT organization_id
     INTO l_organization_id
     FROM mtl_parameters
    WHERE organization_code = '<organization_name>';

   SELECT inventory_item_id
     INTO l_inventory_item_id
     FROM mtl_system_items_b
    WHERE segment1 = '<Item_name>'
      AND organization_id = l_organization_id;

   inv_item_category_pub.create_category_assignment (p_api_version                 => 1.0
                                                   , p_init_msg_list               => fnd_api.g_true
                                                   , p_commit                      => fnd_api.g_true
                                                   , x_return_status               => x_return_status
                                                   , x_errorcode                   => x_error_code
                                                   , x_msg_count                   => x_msg_count
                                                   , x_msg_data                    => x_msg_data
                                                   , p_category_id                 => l_category_id
                                                   , p_category_set_id             => l_category_set_id
                                                   , p_inventory_item_id           => l_inventory_item_id
                                                   , p_organization_id             => l_organization_id
                                                    );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
      DBMS_OUTPUT.put_line ('*****************************************');
   END IF;
END;
/

/*****************************************************************************
 Script to Update the Category for an item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);
   l_category_id                                     NUMBER;
   l_category_set_id                                 NUMBER;
   l_inventory_item_id                               NUMBER;
   l_organization_id                                 NUMBER;
   l_old_category_id                                 NUMBER;
BEGIN
   SELECT mcs_tl.category_set_id
     INTO l_category_set_id
     FROM mtl_category_sets_tl mcs_tl
    WHERE mcs_tl.category_set_name = '<category_set_name>';

   SELECT mcb.category_id
     INTO l_category_id
     FROM mtl_categories_b mcb
    WHERE mcb.segment1 = '<category_name>'
      AND mcb.structure_id = (SELECT mcs_b.structure_id
                                FROM mtl_category_sets_b mcs_b
                               WHERE mcs_b.category_set_id = l_category_set_id);

   SELECT organization_id
     INTO l_organization_id
     FROM mtl_parameters
    WHERE organization_code = '<organization_id>';

   SELECT inventory_item_id
     INTO l_inventory_item_id
     FROM mtl_system_items_b
    WHERE segment1 = '<Item_name>'
      AND organization_id = l_organization_id;

   SELECT mcb.category_id
     INTO l_old_category_id
     FROM mtl_system_items_b msi
        , mtl_item_categories mic
        , mtl_categories_b mcb
        , mtl_category_sets mcs
    WHERE 1 = 1
      AND mic.inventory_item_id = msi.inventory_item_id
      AND mic.organization_id = msi.organization_id
      AND mic.category_id = mcb.category_id
      AND mic.category_set_id = mcs.category_set_id
      AND mcb.structure_id = mcs.structure_id
      AND msi.inventory_item_id = c_inventory_item_id
      AND msi.organization_id = c_orgnaization_id
      AND mcs.category_set_id = c_category_set_id;

   inv_item_category_pub.update_category_assignment (p_api_version                 => 1.0
                                                   , p_init_msg_list               => fnd_api.g_false
                                                   , p_commit                      => fnd_api.g_true
                                                   , x_return_status               => l_return_status
                                                   , x_errorcode                   => l_error_code
                                                   , x_msg_count                   => l_msg_count
                                                   , x_msg_data                    => l_msg_data
                                                   , p_category_id                 => l_category_id
                                                   , p_category_set_id             => l_category_set_id
                                                   , p_inventory_item_id           => l_inventory_item_id
                                                   , p_organization_id             => l_organization_id
                                                   , p_old_category_id             => l_old_category_id
                                                    );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Created Category Assiginment from Item id : ' || l_inventory_item_id || ' Successfully');
      DBMS_OUTPUT.put_line ('*****************************************');
   END IF;
END;
/

/*****************************************************************************
 Script to delete the categroy assignment for a item
*****************************************************************************/

DECLARE
   l_msg_index_out                                   NUMBER;
   l_error_message                                   VARCHAR2 (2000);
   x_return_status                                   VARCHAR2 (80);
   x_error_code                                      NUMBER;
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (250);

   CURSOR c_get_item_categories
   IS
      SELECT msi.segment1
           , msi.inventory_item_id
           , mcs.category_set_id
           , mcb.category_id
           , msi.organization_id
        FROM mtl_system_items_b msi
           , mtl_item_categories mic
           , mtl_categories_b mcb
           , mtl_category_sets mcs
       WHERE 1 = 1
         AND mic.inventory_item_id = msi.inventory_item_id
         AND mic.organization_id = msi.organization_id
         AND mic.category_id = mcb.category_id
         AND mic.category_set_id = mcs.category_set_id
         AND mcb.structure_id = mcs.structure_id
         AND msi.inventory_item_id = c_inventory_item_id
         AND msi.organization_id = c_orgnaization_id;
BEGIN
   FOR c_get_item_categories_rec IN c_get_item_categories
   LOOP
      inv_item_category_pub.delete_category_assignment (p_api_version                 => 1.0
                                                      , p_init_msg_list               => fnd_api.g_true
                                                      , p_commit                      => fnd_api.g_true
                                                      , x_return_status               => x_return_status
                                                      , x_errorcode                   => x_error_code
                                                      , x_msg_count                   => x_msg_count
                                                      , x_msg_data                    => x_msg_data
                                                      , p_category_id                 => c_get_item_categories_rec.category_id
                                                      , p_category_set_id             => c_get_item_categories_rec.category_set_id
                                                      , p_inventory_item_id           => c_get_item_categories_rec.inventory_item_id
                                                      , p_organization_id             => c_get_item_categories_rec.organization_id
                                                       );

      IF x_return_status <> fnd_api.g_ret_sts_success
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get (p_msg_index                   => i
                                , p_encoded                     => fnd_api.g_false
                                , p_data                        => x_msg_data
                                , p_msg_index_out               => l_msg_index_out
                                 );

            IF l_error_message IS NULL
            THEN
               l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
            ELSE
               l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line ('*****************************************');
         DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
         DBMS_OUTPUT.put_line ('*****************************************');
      ELSE
         DBMS_OUTPUT.put_line ('*****************************************');
         DBMS_OUTPUT.put_line (   'Removed Category Assiginment from Item : '
                               || c_get_item_categories_rec.segment1
                               || ' Successfully'
                              );
         DBMS_OUTPUT.put_line ('*****************************************');
      END IF;
   END LOOP;
END;
/
********************************************************************
 Bulk Update script to Update the Category on Item
********************************************************************
DECLARE
   CURSOR c_category
   IS
      SELECT mcb.category_id
           , mcs.category_set_id
           , msi.organization_id
           , msi.inventory_item_id
           , msi.segment1
        FROM mtl_system_items_b msi
           , mtl_item_categories mic
           , mtl_categories_b mcb
           , mtl_category_sets mcs
       WHERE 1 = 1
         AND mic.inventory_item_id = msi.inventory_item_id
         AND mic.organization_id = msi.organization_id
         AND mic.category_id = mcb.category_id
         AND mic.category_set_id = mcs.category_set_id
         AND mcb.structure_id = mcs.structure_id
         AND msi.segment1 = :p_segment1
          OR msi.inventory_item_id = :p_inventory_item_id
         AND msi.organization_id = :p_organization_id
         AND mcs.category_set_id = :p_category_set_id
         AND mcb.category_id = :p_category_id;

   l_msg_index_out                              NUMBER;
   l_error_message                              VARCHAR2(2000);
   x_return_status                              VARCHAR2(80);
   x_error_code                                 NUMBER;
   x_msg_count                                  NUMBER;
   x_msg_data                                   VARCHAR2(250);
   l_category_id                                NUMBER;
   l_category_set_id                            NUMBER;
   l_inventory_item_id                          NUMBER;
   l_organization_id                            NUMBER;
   l_old_category_id                            NUMBER;
BEGIN
   fnd_global.apps_initialize( 12247, 20634, 401);
   COMMIT;

   FOR z IN c_category
   LOOP
      inv_item_category_pub.update_category_assignment(
         p_api_version                             => 1.0
       , p_init_msg_list                           => fnd_api.g_false
       , p_commit                                  => fnd_api.g_true
       , x_return_status                           => x_return_status
       , x_errorcode                               => x_error_code
       , x_msg_count                               => x_msg_count
       , x_msg_data                                => x_msg_data
       , p_category_id                             => :p_new_category_id                                                    --3372
       , p_category_set_id                         => z.category_set_id
       , p_inventory_item_id                       => z.inventory_item_id
       , p_organization_id                         => z.organization_id
       , p_old_category_id                         => z.category_id);

      IF x_return_status <> fnd_api.g_ret_sts_success
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get(
               p_msg_index                               => i
             , p_encoded                                 => fnd_api.g_false
             , p_data                                    => x_msg_data
             , p_msg_index_out                           => l_msg_index_out);

            IF l_error_message IS NULL
            THEN
               l_error_message                                          := SUBSTR( x_msg_data, 1, 250);
            ELSE
               l_error_message                                          :=
                     l_error_message
                  || ' /'
                  || SUBSTR( x_msg_data, 1, 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line( '*****************************************');
         DBMS_OUTPUT.put_line(
               'API Error : '
            || l_error_message);
         DBMS_OUTPUT.put_line( '*****************************************');
      ELSE
         DBMS_OUTPUT.put_line( '*****************************************');
         DBMS_OUTPUT.put_line(
               'Created Category Assiginment from Item id : '
            || l_inventory_item_id
            || ' Successfully');
         DBMS_OUTPUT.put_line( '*****************************************');
      END IF;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(
            'UNEXP_ERROR IN MAIN : '
         || SUBSTR( SQLERRM, 1, 250));
END;
/