How to use Profile Option In Oracle E-Business Suite Reports

Salam Alaikum,

   Many times we must use the profile options in our developed reports in Oracle E-Business Suite, and in order to do so we must add a simple code to the custom report (in the RDF File).

  In Oracle R12 and with the introduction of Multi Org Feature, we must initialize in the custom report in order to initialize the global variables in the context of the user running the report.

*if you need to check out a list of the most used profile options and a simple description about them you can check the below link:

https://technicaloverwhelmed.blogspot.com/2018/01/oracle-apps-e-business-suite-list-of.html

   The API SRW.USER_EXIT('FND SRWINIT') needs to called in the BeforeReport Trigger.

&

   The API SRW.USER_EXIT('FND SRWEXIT') needs to called in the AfterReport Trigger.

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

Oracle Apps (E-Business Suite) List of Profile Options

Salam Alaikum,

   I share with you today a simple bit of information that can be very useful about some profile options.

   In the next post i will talk about how to use them in Oracle Reports.

Variable	Description
USERNAME	Your user's current Oracle Application Object Library username.
USER_ID	Your user's current Oracle Application Object Library user ID.
RESP_ID	Your user's current responsibility ID.
APPL_SHRT_ NAME	The short name of the application connected to your user's current responsibility.
RESP_APPL_ID	The application ID of the application connected to your user's current responsibility.
FORM_NAME	The name of the current form. Not available for concurrent programs.
FORM_ID	The form ID of the current form. Not available for concurrent programs.
FORM_APPL_ NAME	The name of the application for which the current form is registered. Not available for concurrent programs.
FORM_APPL_ID	The application ID of the application for which the current form is registered. Not available for concurrent programs.
LOGON_DATE	Your user's logon date for the current session.
LAST_LOGON_ DATE	Your user's logon date for the previous session.
LOGIN_ID	Your user's Sign-On Audit login ID in Oracle Application Object Library.
CONC_ REQUEST_ID	The request ID associated with a particular instance of your running current program. You can only use this profile option in a concurrent program. You use this profile option to fill the REQUEST_ID Who column.
CONC_ PROGRAM_ID	The program ID associated with a running current program. You can only use this profile option in a concurrent program. You use this profile option to fill the PROGRAM_ID Who column.
CONC_ PROGRAM_ APPLICATION_ ID	The application ID associated with a running current program. You can only use this profile option in a concurrent program. You use this profile option to fill the PROGRAM_APPLICATION_ID Who column.
CONC_LOGIN_ ID	The login ID associated with a running concurrent program. You can only use this profile option in a concurrent program. You can use this profile option to fill the LAST_UPDATE_LOGIN Who column.
CONC_PRINT_ OUTPUT	The value Yes or No that you enter in the Print Output field when you register a concurrent program. You can use the routine afpoput() from your concurrent programs to change the value of this profile option for a particular instance of your running concurrent program. This profile option determines whether the concurrent managers print the concurrent program's output to the printer.
CONC_PRINT_ STYLE	The print style of your concurrent program's output that you enter in the Print Style field when you register a concurrent program. You can use the routine afpoput() from your concurrent programs to change the value of this profile option for a particular instance of your running concurrent program.

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

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.  

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.