API code to Create, Release and Receive an OPM Inventory Transfer

Salam Alaikum,

   Following an example API to create, release and transfer in the Oracle Process Manufacturing (OPM). It calls three different API's. Naturally, the first one is to create and the second to release and the third to transfer, based on the Transfer_action.

DECLARE
   x_status         VARCHAR2 (1);
   x_count          NUMBER;
   x_msg_data       VARCHAR2 (2000);
   l_transfer_rec   GMIGXFR.TYPE_XFER_REC;
   x_out_rec        IC_XFER_MST%ROWTYPE;
   l_dummy          NUMBER;
   x_dummy_cnt      NUMBER;
BEGIN
   l_transfer_rec.transfer_action := 1;
   l_transfer_rec.transfer_no := NULL;
   l_transfer_rec.transfer_batch := 'TEST XFER';
   l_transfer_rec.orgn_code := 'PR1';
   l_transfer_rec.item_no := 'JGC_1';
   l_transfer_rec.lot_no := 'JGC_165';
   l_transfer_rec.sublot_no := NULL;
   l_transfer_rec.source_warehouse := 'PR1';
   l_transfer_rec.source_location := '1';
   l_transfer_rec.target_warehouse := 'PR2';
   l_transfer_rec.target_location := '1';
   -- date for testing period
   l_transfer_rec.scheduled_release_date := TO_DATE (SYSDATE, 'dd.mm.yyyy');
   l_transfer_rec.scheduled_receive_date := TO_DATE (SYSDATE, 'dd.mm.yyyy');
   l_transfer_rec.actual_release_date := TO_DATE (SYSDATE, 'dd.mm.yyyy');
   l_transfer_rec.actual_receive_date := TO_DATE (SYSDATE, 'dd.mm.yyyy');
   l_transfer_rec.cancel_date := NULL;
   l_transfer_rec.release_quantity1 := 1.0;
   l_transfer_rec.release_quantity2 := NULL;
   l_transfer_rec.reason_code := 'ADJ';
   l_transfer_rec.comments := 'test API XFER';
   l_transfer_rec.user_name := 'OPMUK';
   -- Creating
   GMIPXFR.Inventory_Transfer (
      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_xfer_rec           => l_transfer_rec,
      x_ic_xfer_mst_row    => x_out_rec,
      x_return_status      => x_status,
      x_msg_count          => x_count,
      x_msg_data           => x_msg_data);

   IF x_status != 'S'
   THEN
      DBMS_OUTPUT.put_line ('Error creating');

      IF x_count > 1
      THEN
         FOR l_dummy IN 1 .. x_count
         LOOP
            fnd_msg_pub.get (p_msg_index       => l_dummy,
                             p_data            => x_msg_data,
                             p_encoded         => fnd_api.g_false,
                             p_msg_index_out   => x_dummy_cnt);
            DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
         END LOOP;
      ELSE
         DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
      END IF;
   ELSE                                                          -- if success
      -- RELEASE
      l_transfer_rec.transfer_action := 2;
      l_transfer_rec.transfer_no := x_out_rec.transfer_no;
      DBMS_OUTPUT.put_line ('XFER no:' || l_transfer_rec.transfer_no);
      GMIPXFR.Inventory_Transfer (
         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_xfer_rec           => l_transfer_rec,
         x_ic_xfer_mst_row    => x_out_rec,
         x_return_status      => x_status,
         x_msg_count          => x_count,
         x_msg_data           => x_msg_data);

      IF x_status != 'S'
      THEN
         DBMS_OUTPUT.put_line ('Error releasing');

         IF x_count > 1
         THEN
            FOR l_dummy IN 1 .. x_count
            LOOP
               fnd_msg_pub.get (p_msg_index       => l_dummy,
                                p_data            => x_msg_data,
                                p_encoded         => fnd_api.g_false,
                                p_msg_index_out   => x_dummy_cnt);
               DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
         END IF;
      ELSE
         -- receive
         l_transfer_rec.transfer_action := 3;
         GMIPXFR.Inventory_Transfer (
            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_xfer_rec           => l_transfer_rec,
            x_ic_xfer_mst_row    => x_out_rec,
            x_return_status      => x_status,
            x_msg_count          => x_count,
            x_msg_data           => x_msg_data);

         IF x_status != 'S'
         THEN
            DBMS_OUTPUT.put_line ('Error receiving');

            IF x_count > 1
            THEN
               FOR l_dummy IN 1 .. x_count
               LOOP
                  fnd_msg_pub.get (p_msg_index       => l_dummy,
                                   p_data            => x_msg_data,
                                   p_encoded         => fnd_api.g_false,
                                   p_msg_index_out   => x_dummy_cnt);
                  DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
               END LOOP;
            ELSE
               DBMS_OUTPUT.put_line (SUBSTR (x_msg_data, 1, 255));
            END IF;
         END IF;
      END IF;
   END IF;
END;
/

  Hope you found it useful, feel free to leave a comment if have any note on the subject. 

Inventory Item Upload API

Salam Alaikum,

   I share with you here a very simple API that can be used to upload inventory items to the interface "MTL_SYSTEM_ITEMS_INTERFACE". And please note that after populating the interface you have to run the request "Import Items".

DECLARE
   CURSOR cur
   IS
      SELECT ITEM_CODE,
             ITEM_DESCRIPTION,
             UOM_CODE,
             TEMPLATE_ID
        FROM XXX_ITEMS_TEMP;
BEGIN
   fnd_global.apps_initialize (1150, 20634, 401);

   FOR rec IN cur
   LOOP
      INSERT INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE (SEGMENT1,
                                                   DESCRIPTION,
                                                   ORGANIZATION_ID,
                                                   PRIMARY_UOM_CODE,
                                                   TEMPLATE_ID,
                                                   CREATED_BY,
                                                   PROCESS_FLAG,
                                                   TRANSACTION_TYPE)
           VALUES (rec.ITEM_CODE,
                   rec.ITEM_DESCRIPTION,
                   102,
                   rec.UOM_CODE,
                   rec.TEMPLATE_ID,
                   1150,
                   1,
                   'CREATE');

      INSERT INTO APPS.MTL_SYSTEM_ITEMS_INTERFACE (SEGMENT1,
                                                   DESCRIPTION,
                                                   ORGANIZATION_ID,
                                                   PRIMARY_UOM_CODE,
                                                   TEMPLATE_ID,
                                                   CREATED_BY,
                                                   PROCESS_FLAG,
                                                   TRANSACTION_TYPE)
           VALUES (rec.ITEM_CODE,
                   rec.ITEM_DESCRIPTION,
                   101,
                   rec.UOM_CODE,
                   rec.TEMPLATE_ID,
                   1150,
                   1,
                   'CREATE');

      UPDATE XXX_ITEMS_TEMP2 t
         SET t.data_upload = 'Y'
       WHERE t.ITEM_CODE = rec.ITEM_CODE;
   END LOOP;
END;

Some notes regarding the Concurrent "Import Items":

1. Indicate whether to run the interface for all organizations in the item interface table. If you choose No, the interface runs only for the current organization and interface table rows for other organizations are ignored.
2. Indicate whether to validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they are not processed into Oracle Inventory. (You would choose No if you had previously run the item interface and responded Yes for Validate Items and No for Process Items, and now want to process your items).
3. Indicate whether to process items. If you choose Yes, all qualifying items in the interface table are inserted into Inventory. (You would choose No, along with Yes for Delete Processed Rows, to remove successfully processed rows from the interface table without performing any other processing).
4. Indicate whether to delete successfully processed items from the item interface tables
   (Choose No to leave all rows in the item interface tables for successfully processed items).

  Hope you found it useful, feel free to leave a comment if have any note on the subject.

Oracle Apps Shortcut Key (very useful)

Salam Alaikum,

   In Oracle Apps there are many shortcuts for some of the functionalities while working on oracle forms, it can be very useful for everyone who uses the system to know them. Here they are explained in this table:

Functionality	Shortcut
Clear Field	F5
Clear Form	F8
Clear Record	F6
Clear Block	F7
Delete Record	Ctrl+↑
Commit / Save	Ctrl+S
Down	↓
Duplicate Field	Shift+F5
Duplicate Record	Shift+F6
Edit	Ctrl+E
Enter Query Mode	F11
Execute Query	Ctrl+F11
Insert Record	Ctrl+↓
List of Values	Ctrl+L
Next Block	Shift+PgDn
Previous Block	Shift+PgUp
Next Record	↓
Previous Record	↑
Previous Field	Shift-Tab
Next Field	Tab
Print	Ctrl+P
Scroll Down	Page Down
Scroll Up	Page Up
Show Keys	Ctrl+K
Exit	F4

  Hope you found it useful, feel free to leave a comment if have any note on the subject.

How to add the "Duplicate Record" functionality to custom forms

Salam Alaikum,

    For those who are not familiar with the "Duplicate Record" functionality, it is a standard functionality added to many forms in Oracle Apps (E-Business Suite) to enhance user entry in many standeard forms. It is triggered once the user enters "Shift + F6" in any tabular data block.

   For a list of all Oracle Apps shortcuts please refer to this post:

http://technicaloverwhelmed.blogspot.com/2016/03/oracle-apps-shortcut-key-very-useful.html

   And as explained in "Oracle Applications Developer Giude R12" this functionality is disabled by default for the following reasons:

1. The special column ROW_ID is duplicated and must be exempted if it exists.
2. The record is marked as valid even though the items may contain time-sensitive data that is no longer valid.
3. Defaults are overridden
4. It doesnt make sense in many scenrios. 

  

 
   But in many cases... you might need to add to add it. It is actually pretty simple. And here are the steps to implement it:


1.    Add the following procedure to the form (you may want to place this code in the Program units), as the following example:

PACKAGE BODY xx_block_name
IS
   PROCEDURE KEY_DUPREC
   IS
      CURSOR new_primary_key
      IS
         SELECT block_name_S.NEXTVAL FROM sys.DUAL;
   BEGIN
     DUPLICATE_RECORD;

     OPEN new_primary_key;
     FETCH new_primary_key INTO :order.block_id;
     CLOSE new_primary_key;

     :order.status  : = 'New';         --- you can add any fixed valur for the new created record
     :order.row_id := null;            --- put null for the ROW_ID
   END KEY_DUPREC;
END xx_block_name;

2.    Create a trigger "KEY-DUPREC"on the data block level and call procedure created.

   Now, test and remeber to keep the client happy all the time :)

  Hope you found it useful guys, feel free to leave a comment if have any note on the subject.        

About the blog

Dears,

   As it obvious to many of you this blog, is only concerned about sharing some knowldedge about Oracle Application (especially Oracle E-Business suite R12) on multiple subjects and multiple areas that i have worked on. You can find many code example and queries that you can use and benefit from, each explained in every post.

   I am sorry for not being able to add more posts lately but i promise you i will do my best to get back to sharing my knowledge with you...

   Thanks for visiting, and please if you have any ideas or questions, don't hesitate to contact me, i will be glad to help if i can...