OAF Personalization Document

Oracle HRMS Saudi Arabia Localization Setup Document

API to Assign Items from one Org to another org in Oracle Inventory

CREATE OR REPLACE PROCEDURE xx_assign_org_items
IS
   CURSOR c_items
   IS
      SELECT   organization_id,
               description,
               segment1,
               primary_uom_code,
               --template_name,
               weight_uom_code,
               unit_weight,
               'CREATE' transaction_type,
               --process_flag, set_process_id,
               inventory_item_id,
               item_type,
               atp_flag,
               atp_components_flag,
               bom_enabled_flag,
               customer_order_enabled_flag,
               customer_order_flag,
               enabled_flag,
               inspection_required_flag,
               internal_order_enabled_flag,
               internal_order_flag,
               inventory_asset_flag,
               inventory_item_flag,
               invoiceable_item_flag,
               invoice_enabled_flag,
               mtl_transactions_enabled_flag,
               purchasing_enabled_flag,
               purchasing_item_flag,
               receipt_required_flag,
               shippable_item_flag,
               so_transactions_flag,
               stock_enabled_flag,
               summary_flag
        FROM   mtl_system_items_b
       WHERE       organization_id = 83 --<From Org Id>
               AND inventory_item_status_code = 'Active';
           

   v_items   c_items%ROWTYPE;

   CURSOR c_wares
   IS

      SELECT   organization_id
        FROM   apps.org_organization_definitions
       WHERE   organization_id = 102; ---<To Org Id>

   v_wares   c_wares%ROWTYPE;
BEGIN
   OPEN c_items;

   LOOP
      FETCH c_items INTO   v_items;

      EXIT WHEN c_items%NOTFOUND;

      OPEN c_wares;

      LOOP
         FETCH c_wares INTO   v_wares;

         EXIT WHEN c_wares%NOTFOUND;

         --dbms_output.put_line( v_wares.organization_id || ' ' || v_items.description );
         BEGIN
            INSERT INTO mtl_system_items_interface (
                                                       organization_id,
                                                       description,
                                                       segment1,
                                                       primary_uom_code,
                                                       template_name,
                                                       weight_uom_code,
                                                       unit_weight,
                                                       transaction_type,
                                                       process_flag,
                                                       set_process_id,
                                                       inventory_item_id,
                                                       last_update_date,
                                                       item_type,
                                                       atp_flag,
                                                       atp_components_flag,
                                                       bom_enabled_flag,
                                                       customer_order_enabled_flag,
                                                       customer_order_flag,
                                                       enabled_flag,
                                                       inspection_required_flag,
                                                       internal_order_enabled_flag,
                                                       internal_order_flag,
                                                       inventory_asset_flag,
                                                       inventory_item_flag,
                                                       invoiceable_item_flag,
                                                       invoice_enabled_flag,
                                                       mtl_transactions_enabled_flag,
                                                       purchasing_enabled_flag,
                                                       purchasing_item_flag,
                                                       receipt_required_flag,
                                                       shippable_item_flag,
                                                       so_transactions_flag,
                                                       stock_enabled_flag,
                                                       summary_flag,
                                                       last_updated_by,
                                                       creation_date,
                                                       created_by,
                                                       last_update_login,
                                                       request_id,
                                                       program_application_id,
                                                       program_id,
                                                       program_update_date
                       )
              VALUES   (v_wares.organization_id,
                        v_items.description,
                        v_items.segment1,
                        v_items.primary_uom_code,
                        '',
                        v_items.weight_uom_code,
                        v_items.unit_weight,
                        'CREATE',
                        1,
                        1,
                        v_items.inventory_item_id,
                        SYSDATE,
                        v_items.item_type,
                        v_items.atp_flag,
                        v_items.atp_components_flag,
                        v_items.bom_enabled_flag,
                        v_items.customer_order_enabled_flag,
                        v_items.customer_order_flag,
                        v_items.enabled_flag,
                        v_items.inspection_required_flag,
                        v_items.internal_order_enabled_flag,
                        v_items.internal_order_flag,
                        v_items.inventory_asset_flag,
                        v_items.inventory_item_flag,
                        v_items.invoiceable_item_flag,
                        v_items.invoice_enabled_flag,
                        v_items.mtl_transactions_enabled_flag,
                        v_items.purchasing_enabled_flag,
                        v_items.purchasing_item_flag,
                        v_items.receipt_required_flag,
                        v_items.shippable_item_flag,
                        v_items.so_transactions_flag,
                        v_items.stock_enabled_flag,
                        v_items.summary_flag,
                        fnd_global.user_id,
                        SYSDATE,
                        fnd_global.user_id,
                        fnd_global.login_id,
                        fnd_global.conc_request_id,
                        fnd_global.prog_appl_id,
                        fnd_global.conc_program_id,
                        SYSDATE);

            IF SQL%NOTFOUND
            THEN
               DBMS_OUTPUT.put_line('Insert Failed in Header Level at segment : '
                                    || v_items.segment1
                                    || ' and organization_id : '
                                    || v_wares.organization_id);
            END IF;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line('Insert Failed in Header Level at segment : '
                                    || v_items.segment1
                                    || ' and organization_id : '
                                    || v_wares.organization_id);
         END;
      END LOOP;

      CLOSE c_wares;
   END LOOP;

   CLOSE c_items;
END xx_assign_org_items;
/

Note: Run "Import Items" Program from the destination Organization.

Oracle Inventory Item Master API

CREATE OR REPLACE PROCEDURE APPS.xx_item_master_upload_api
--(errbuf out varchar2, rectcode out varchar2)
AS
   l_verify_flag         VARCHAR2 (3);
   l_error_message       VARCHAR2 (2500);
   l_count               NUMBER (2);
   l_category_set_id     NUMBER (20);
   l_category_id         NUMBER (20);
   l_org_id              NUMBER (4);
   l_template_name       VARCHAR2 (250);
   l_uom                 VARCHAR2 (20);
   l_item_type           VARCHAR2 (20);
   l_organization_id     NUMBER (10);
   l_organization_code   VARCHAR2 (10);

   CURSOR c1
   IS
      SELECT *
        FROM xxx_item_master_stg;
BEGIN
   FOR c_rec IN c1
   LOOP
      l_verify_flag := 'Y';
      l_error_message := NULL;
      l_count := 0;

      BEGIN
         SELECT organization_id, organization_code
           INTO l_organization_id, l_organization_code
           FROM org_organization_definitions
          WHERE organization_code = 'ABC'  --<Enter Your Inventory Org Code>
      EXCEPTION
         WHEN OTHERS
         THEN
            l_verify_flag := 'N';
            l_error_message := 'INVALID ORGANIZATION';
      END;

      BEGIN
         SELECT COUNT (*)
           INTO l_count
           FROM mtl_system_items_b
          WHERE segment1 = c_rec.segment1
            AND organization_id = l_organization_id;

         IF l_count > 0
         THEN
            l_verify_flag := 'N';
            l_error_message := 'ITEM ALREADY EXISTING';
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      IF c_rec.segment1 IS NULL
      THEN
         l_verify_flag := 'N';
         l_error_message := 'ITEM SEGMENT SHOULD NOT BE NULL';
      END IF;

      IF c_rec.description IS NULL
      THEN
         l_verify_flag := 'N';
         l_error_message := 'INVALID DESCRIPTION';
      END IF;

      BEGIN
         SELECT template_name
           INTO l_template_name
           FROM mtl_item_templates
          WHERE template_id = c_rec.template_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_verify_flag := 'N';
            l_error_message := 'INVALID TEMPLATE NAME';
      END;

      BEGIN
         SELECT unit_of_measure
           INTO l_uom
           FROM mtl_units_of_measure
          WHERE uom_code = TRIM (c_rec.primary_unit_of_measure);
      /* Use Primary Unit Measure Code*/
      EXCEPTION
         WHEN OTHERS
         THEN
            l_verify_flag := 'N';
            l_error_message := 'INVALID UOM';
      END;

      IF l_verify_flag <> 'N'
      THEN
         BEGIN
            INSERT INTO mtl_system_items_interface
                        (segment1, description, primary_unit_of_measure,
                         process_flag, set_process_id, template_name,
                         organization_id, organization_code,
                         transaction_type, attribute_category,
                         attribute1, attribute2,
                         attribute3, attribute4,
                         attribute8, attribute9
                        )
                 VALUES (c_rec.segment1, c_rec.description, l_uom,
                         1, 1, l_template_name,
                         l_organization_id, l_organization_code,
                         'CREATE', c_rec.attribute_category,
                         c_rec.attribute1, c_rec.attribute2,
                         c_rec.attribute3, c_rec.attribute4,
                         c_rec.attribute8, c_rec.attribute9
                        );

            UPDATE xxx_item_master_stg
               SET verify_flag = 'Y'
             WHERE segment1 = c_rec.segment1;

            DBMS_OUTPUT.put_line ('ITEMS UPLOADED SUCCESSFULLY!');
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_message := SQLERRM;

               UPDATE xxx_item_master_stg
                  SET verify_flag = 'N',
                      error_message = l_error_message
                WHERE segment1 = c_rec.segment1;
         END;

         COMMIT;
      ELSE
         UPDATE xxx_item_master_stg
            SET verify_flag = 'N',
                error_message = l_error_message
          WHERE segment1 = c_rec.segment1;

         COMMIT;
      END IF;
   END LOOP;

   COMMIT;
END xx_item_master_upload_api;
/

Note:

After Items inserted in the Inventory Interface Table (mtl_system_items_interface), Run the concurrent Program  " Import Items". 

Oracle Time and Labor PPT

Upload Data into Oracle Table from Excel using Custom Oracle Web ADI

Load CSV File into Oracle Database Table using PL/SQL

Here I am going to show how to use UTL_FILE package and read the CSV from the Oracle directory and load into the target table.

Step1: Create Target Table Ex: "XX_EMP_TIME_OTL" add the fields as per your requirements.

Step2: Prepare the .CSV File as per the above table columns and save it in a Oracle directory.

>> create the excel sheet and save as <<XX_FILE.csv>>

Step3: Put the CSV file into the Oracle directory and if you do not have any directory then create it as like below:

CREATE OR REPLACE DIRECTORY ORA_DIR as '/oraebs/CLONE/apps/apps_st/appl' 
>>>----- Change the path as per your requirements -- this is oracle directory in the server<<<<<


Create the grants for the user, you are executing the  UTL_FILE:

GRANT READ, WRITE ON DIRECTORY ORA_DIR TO << Your User >>;

Step4:  Check if the user is having UTL_FILE privileges or not :




SELECT OWNER,

OBJECT_TYPE

FROM ALL_OBJECTS

WHERE OBJECT_NAME = 'UTL_FILE'

AND OWNER =<Your User>;

If the user is not having the privileges then grant “UTL_FILE” to user from SYS user:

GRANT EXECUTE ON UTL_FILE TO <Your User>;


Step 5:  Create the Procedure

CREATE or REPLACE PROCEDURE XX_LOAD_CSV
   AS
      F            UTL_FILE.FILE_TYPE;
      V_LINE       VARCHAR2 (1000);
      V_EMPNO      VARCHAR2 (30);
      V_DATE       VARCHAR2 (20);
      V_TIME_IN    VARCHAR2 (20);
      V_TIME_OUT   VARCHAR2 (20);
      v_error      VARCHAR2 (3000);
      v_count      NUMBER;
   BEGIN
      F := UTL_FILE.FOPEN ('ORA_DIR', 'time.csv', 'R');

      IF UTL_FILE.IS_OPEN (F)
      THEN
         LOOP
            BEGIN
               UTL_FILE.GET_LINE (F, V_LINE, 1000);

               IF V_LINE IS NULL
               THEN
                  EXIT;
               END IF;

               V_EMPNO :=
                  REGEXP_SUBSTR (V_LINE,
                                 '[^,]+',
                                 1,
                                 1);
               V_DATE :=
                  REGEXP_SUBSTR (V_LINE,
                                 '[^,]+',
                                 1,
                                 2);
               V_TIME_IN :=
                  REGEXP_SUBSTR (V_LINE,
                                 '[^,]+',
                                 1,
                                 3);
               V_TIME_OUT :=
                  REGEXP_SUBSTR (V_LINE,
                                 '[^,]+',
                                 1,
                                 4);

               INSERT INTO XX_EMP_TIME_OTL
                 VALUES   (V_EMPNO,
                           V_DATE,
                           V_TIME_IN,
                           V_TIME_OUT,
                           'OTL Regular Hours',
                           NULL,
                           NULL,
                           NULL,
                           0,
                           SYSDATE,
                           NULL);

               COMMIT;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  EXIT;
            END;
         END LOOP;

         DBMS_OUTPUT.put_line ('Insert Statement Completed Successfully:');

         BEGIN
            SELECT   COUNT ( * ) INTO v_count FROM XX_EMP_TIME_OTL;

            DBMS_OUTPUT.put_line (
               'Total Records Inserted into Table:' || v_count
            );

            INSERT INTO XX_IMPORT_HIST_CSV
              VALUES   ('TIME.CSV' || '_' || SYSDATE,
                        'XX_EMP_TIME_OTL',
                        v_count,
                        SYSDATE);

            COMMIT;
         END;
      END IF;

      UTL_FILE.FCLOSE (F);
   EXCEPTION
      WHEN OTHERS
      THEN
         v_error := SQLERRM;
         DBMS_OUTPUT.put_line ('Exception Error:' || v_error);

         INSERT INTO XX_CSV_LOG
           VALUES   ('XX_EMP_TIME_OTL',
                     V_ERROR,
                     'TIME_ENTRY',
                     SYSDATE);

         COMMIT;

   END XX_LOAD_CSV;


Step6: Execute the procedure.  Your data from the csv file is inserted into the target table in oracle database.