Sunday, August 28, 2016

API to Create Position

Salam Alaikum,

   I share with you today the API used to create a position in HR.

   *Please note that the values in the API are hard-coded.



declare

l_true                                                  BOOLEAN     :=false;
l_obj                                                   NUMBER;
l_JOB_ID                                           NUMBER        :=614;  
l_ORGANIZATION_ID                    NUMBER        :=1379;
l_DATE_EFFECTIVE                       DATE        := to_date('01-DEC-2000', 'DD-MON-YYYY');
l_POSITION_ID                                NUMBER;
l_POSITION_DEFINITION_ID       NUMBER;
l_NAME                          VARCHAR2(30);
l_segment1           
VARCHAR2(30)    :='TEST POSITION';
l_segment2            VARCHAR2(30)    :='10';

begin

hr_position_api.create_position
(
        p_validate              =>    l_true
,       p_object_version_number        =>    l_obj
,    p_JOB_ID            =>    l_JOB_ID
,    p_ORGANIZATION_ID         =>    l_ORGANIZATION_ID
,    p_DATE_EFFECTIVE        =>    l_DATE_EFFECTIVE
,    p_POSITION_ID            =>    l_POSITION_ID
,     p_POSITION_DEFINITION_ID    =>    l_POSITION_DEFINITION_ID
,     p_NAME                 =>    l_NAME
,    p_segment1            =>    l_segment1
,    p_segment2            =>    l_segment2
,    p_location_id            =>    2694
);
end;

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

API to Create Organization

Salam Alaikum,

   I share with you today the API used to create organiztion in HR.

   *Please note that the values in the API are hard-coded.


DECLARE
l_date                    date           := to_date('01-jan-2000','DD-MON-YYYY');
l_true                    boolean     := false;
l_business_group_id        number        := 626;
l_language_code            varchar2(10)    := 'US';
l_name                    varchar2(80)    := 'NJ TEST ORG';
l_internal_external_flag    varchar2(80)    := 'INT';
l_organization_id         number;
l_object_version_number        number;
l_duplicate_org_warning        boolean;

BEGIN
hr_organization_api.create_organization
   (  p_validate                      =>     l_true
     ,p_effective_date                =>    l_date
     ,p_language_code                 =>    l_language_code
     ,p_business_group_id             =>    l_business_group_id
     ,p_date_from                     =>    l_date
     ,p_name                          =>    l_name
     ,p_internal_external_flag        =>    l_internal_external_flag
     ,p_organization_id               =>    l_organization_id
     ,p_object_version_number         =>    l_object_version_number
     ,P_duplicate_org_warning          =>    l_duplicate_org_warning
);
END; 

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

Wednesday, March 23, 2016

API to Create Person Extra Information record

Salam Alaikum,

   It is one very important and widely used API that i share with you today... It is the API used to upload Extra Information for a person in Oracle HR. It is very simple and straight forward, and as you know each Extra Information has different (attributes/attribute type) so you have to consider getting to know some the dependencies (if exists) between the attributes before uploading.

   That being said, i share one simple API, with three attributes only:

DECLARE
   l_true                       BOOLEAN := FALSE;
   l_PERSON_ID                  NUMBER := 31785;
   l_INFORMATION_TYPE           VARCHAR2 (2000) := 'LAST_NAME_DETAILS';
   l_pei_information_category   VARCHAR2 (2000) := 'LAST_NAME_DETAILS';
   l_PEI_INFORMATION1           VARCHAR2 (2000) := 'Test1';
   l_PEI_INFORMATION2           VARCHAR2 (2000) := 'Test2';
   l_PEI_INFORMATION3           VARCHAR (200) := TO_CHAR (TO_DATE ('01-JAN-1999', 'DD-MON-YYYY'),'YYYY/MM/DD HH24:MI:SS');
   -- Output Variables
   l_PERSON_EXTRA_INFO_ID       NUMBER;
   l_OBJ                        NUMBER;

   CURSOR csr_ovn
   IS
      SELECT MAX (object_version_number)
        FROM per_all_people_f
       WHERE person_id = l_person_id;
BEGIN
   OPEN csr_ovn;
   FETCH csr_ovn INTO l_obj;
   CLOSE csr_ovn;

   HR_PERSON_EXTRA_INFO_API.CREATE_PERSON_EXTRA_INFO (
      p_validate                   => l_true,
      p_person_id                  => l_person_id,
      p_INFORMATION_TYPE           => l_INFORMATION_TYPE,
      p_pei_information_category   => l_pei_information_category,
      p_PEI_INFORMATION1           => l_PEI_INFORMATION1,
      p_PEI_INFORMATION2           => l_PEI_INFORMATION2,
      p_PEI_INFORMATION3           => l_PEI_INFORMATION3,
      p_PERSON_EXTRA_INFO_ID       => l_PERSON_EXTRA_INFO_ID,
      p_object_version_number      => l_obj);
END;

*note: the P_PERSON_EXTRA_INFO_ID and P_OBJECT_VERSION_NUMBER are both out parameters.

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

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.

Tuesday, March 15, 2016

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.