API to create or update ITEMS

Overview:

    Using below PL/SQL you will create or update Inventory Item in Oracle Apps.

    API Name:  Ego_Item_pub.process_item

DECLARE

    ln_inventory_item_id NUMBER;

    ln_organization_id     NUMBER;

    lc_return_status        VARCHAR2 (4000);

    lc_msg_data              VARCHAR2 (4000);

    ln_msg_count            NUMBER;

    x_message_list error_handler.error_tbl_type;

BEGIN

    fnd_global.apps_initialize (user_id          => 77767,

                                           resp_id          => 87987,

                                           resp_appl_id  => 208);

    ego_item_pub.process_item (p_api_version       => 1.0

                              ,p_init_msg_list        => 'T'

                              ,p_commit                => 'T'

                              ,p_transaction_type  => 'CREATE'    -- UPDATE for Updating item

                              ,p_segment1            => 'XX_TEST1'           -- Item Code

                              ,p_description          => 'XX Oracle Test Item1' -- Item Description

                              ,p_long_description  => 'XX Oracle Test Item1' — ITEM LONG DESCRIPTION

                              ,p_organization_id   => 230 -- Organization ID

                              ,p_apply_template    => 'ALL'

                              ,p_template_name    => '@Activity' -- SELECT * FROM mtl_item_templates_vl

                              ,p_item_type            => 'P'

                              ,p_inventory_item_status_code => 'Active'

                              ,p_approval_status   => 'A'

                               -- Below are Out parameters

                              ,x_inventory_item_id => ln_inventory_item_id

                              ,x_organization_id     => ln_organization_id

                              ,x_return_status       => lc_return_status

                              ,x_msg_count           => ln_msg_count

                              ,x_msg_data             => lc_msg_data);

    IF l_return_status = fnd_api.g_ret_sts_success THEN

        dbms_output.put_line ('Item is Created Successfully, Inventory Item ID : ' || ln_inventory_item_id||'Organization ID:'||ln_organization_id);

        COMMIT;

    ELSE

        dbms_output.put_line ('Item Creation is Failed');

        error_handler.get_message_list (x_message_list => x_message_list);

   

        FOR i IN 1 .. x_message_list.count

        LOOP

            dbms_output.put_line (x_message_list (i).message_text);

        END LOOP;

   

        ROLLBACK;

    END IF;

          --

EXCEPTION

    WHEN OTHERS THEN

        FOR i IN 1 .. ln_msg_count

        LOOP

        dbms_output.put_line (substr (fnd_msg_pub.get (p_encoded => fnd_api.g_false), 1, 255));

        dbms_output.put_line ('message is: ' || lc_msg_data);

        END LOOP;

END;

/

Return multiple rows from PL/SQL stored Function

Overview:

A function always return a value using the RETURN statement.

Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE.

 This article help you to show how to return multiple rows or values from Function.

 See below example:

For Example:

1) Create Object with Emp_id and First_name columns:

    CREATE TYPE XX_emp_obj IS OBJECT ( Emp_id NUMBER, First_name VARCHAR2(20) );

2) Create Table type depend on Object Type:

    CREATE TYPE XX_emp_tab IS TABLE OF XX_emp_obj;

3) Create PL/SQL stored Function, in this function use below Select script for display records:

        SELECT person_id, last_name

        FROM per_all_people_f

        WHERE rownum < 5;

    Function:

    CREATE OR REPLACE FUNCTION XX_get_emps

    RETURN XX_emp_tab

    IS

          l_emp_tab XX_emp_tab := XX_emp_tab();

          ln_num NUMBER :=0;

    BEGIN

       FOR lrec IN (SELECT person_id, last_name FROM per_all_people_f WHERE rownum < 5)

       LOOP

           l_emp_tab.EXTEND;

           ln_num := ln_num +1;

           l_emp_tab(ln_num ) := XX_emp_obj(lrec .person_id, lrec .last_name);

       END LOOP;

    RETURN l_emp_tab;

    END;

    /

4) After created function, call that function is Select statement:

    SELECT * FROM TABLE (XX_get_emps);

     -- (XX_get_emps -- function name).

5) Or call in PL/SQL block:

    SET SERVEROUTPUT ON;

    DECLARE

        CURSOR lcur_emp 

        IS

           SELECT empl_id, first_name 

           FROM TABLE(XX_get_emps);

    BEGIN

        dbms_output.put_line('EmpID: '||'         '||'FirstName: ');

        FOR lrec IN lcur_emp

        LOOP

               dbms_output.put_line(lrec.empl_id||'         '||lrec.first_name);

        END LOOP;

    END;

    /

** If you need to Drop above created objects then use below SQL scripts:

   1) DROP TYPE XX_emp_obj;

   2) DROP TYPE XX_emp_tab;

   3) DROP FUNCTION XX_get_emps;

Delete duplicate rows from Table in SQL

Below example shows how to delete duplicate records from Table using DELETE statement in SQL:

For Example:

1) Create table: XX_EMP

CREATE TABLE xx_emp (employee_id NUMBER, first_name VARCHAR2(60), salary NUMBER);

2) Insert records in XX_EMP table:

    i)  INSERT INTO xx_emp (employee_id, first_name, salary)

        VALUES (101, 'ABC', 20000);

   ii)  INSERT INTO xx_emp (employee_id, first_name, salary)

        VALUES (101, 'ABC', 20000);

   iii) INSERT INTO xx_emp (employee_id, first_name, salary)

        VALUES (102, 'XYZ', 20000);

  COMMIT;

3) Use SELECT statement to display inserted records:

    SELECT * FROM xx_emp;

    Output:

     --------------------------------------------------------------

     EMPLOYEE_ID         FIRST_NAME          SALARY

     --------------------------------------------------------------

     101                           ABC                       20000

     101                           ABC                       20000

     102                           XYZ                        20000

     --------------------------------------------------------------

4) Use DELETE script to delete Duplicate records from Table: XX_EMP:

    DELETE FROM xx_emp

    WHERE rowid NOT IN ( SELECT max(rowid) 
                                        FROM xx_emp
                                        GROUP BY employee_id, first_name, salary
                                      );

    COMMIT;

5) Use SELECT statement to check – duplicate records deleted or Not from XX_EMP table:

    SELECT * FROM xx_emp;

    Output:

     --------------------------------------------------------------

     EMPLOYEE_ID         FIRST_NAME          SALARY

     --------------------------------------------------------------

     101                           ABC                       20000

     102                            XYZ                        20000

     --------------------------------------------------------------

Reset Application User password using API

Overview:

  Reset User password via back-end using API : fnd_user_pkg.ChangePassword

For Example:

  SET SERVEROUTPUT ON;

  DECLARE

        lc_user_name         VARCHAR2(30)   :=  UPPER('JES009');

        lc_new_password    VARCHAR2(30)   :=  'welcome123';

        lc_status                BOOLEAN;

BEGIN

        lc_status := fnd_user_pkg.ChangePassword ( username =>  lc_user_name, 

                                                                           newpassword  => lc_new_password 

                                                                         );

       COMMIT;

END;

/

Delete Responsibility from User, Disable user, Enable user and Change UserName using API

Overview:

    This document helps you for below points:

           1) Delete Responsibility from USER using API

           2) Disable User using API

           3) Enable User using API

           4) Change User name using API    

1) Delete Responsibility from USER using API

     Detach a responsibility which is already assigned to User

     If any of the username or application short name or responsibility key or

     security group is not valid, exception raised with error message.

  

    Input (Mandatory)

       username:        User Name

       resp_app:         Application Short Name

       resp_key:          Responsibility Key

       security_group: Security Group Key

          For example: Below example assign responsibility to FND USER

 

        DECLARE

             lc_user_name        VARCHAR2(90) := 'XX007MN';

             lc_resp_app           VARCHAR2(40) := 'SYSADMIN';

             lc_resp_key            VARCHAR2(40) := 'SYSTEM_ADMINISTRATOR';

             lc_security_group   VARCHAR2(40) := 'STANDARD';

        BEGIN

          FND_USER_PKG.DelResp(username         => lc_user_name,

                                                 resp_app           => lc_resp_app,

                                                 resp_key           => lc_resp_key,

                                                 security_group  => lc_security_group

                              );

            COMMIT;

                dbms_output.put_line('Successfully Responsibility: ' || lc_resp_key || ' delete from User: '||lc_user_name);

        EXCEPTION

               WHEN OTHERS THEN

                    dbms_output.put_line('Unable to delete responsibility from User due to' || SQLCODE || ' ' || SUBSTR(SQLERRM, 1, 100));

            ROLLBACK;

        END;

        /

Note: Replace variable values with your values.

-----------------------------------------------------------------

2) Disable User using API

   This document helps you to Disable particular User.

   --

   For Example:

  

      DECLARE

        lc_user_name VARCHAR2(50) := 'XX007MN';

      BEGIN

          FND_USER_PKG.DisableUser(username => lc_user_name);

          COMMIT;

           dbms_output.put_line('Successfully Disable User: '||lc_user_name);

      EXCEPTION

        WHEN OTHERS THEN

        dbms_output.put_line('Unable to disable User due to' || SQLCODE || ' ' || SUBSTR(SQLERRM, 1, 100));

      END;

      /

Note: Replace variable values with your values.

-----------------------------------------------------------------

3) Enable User using API

   This document helps you to Enable particular User.

  

   For Example:

      DECLARE

        lc_user_name  VARCHAR2(50) := 'XX007MN';

        ld_start_date DATE         := to_Date('01-JAN-2018');

        ld_end_date   DATE         := to_Date('20-DEC-2045');

       

      BEGIN

        FND_USER_PKG.EnableUser(username   => lc_user_name,

                                                   start_date   => ld_start_date,

                                                    end_date    => ld_end_date);

        COMMIT;

           dbms_output.put_line('Successfully Enable User: '||lc_user_name);

      EXCEPTION

        WHEN OTHERS THEN

        dbms_output.put_line('Unable to Enable User due to' || SQLCODE || ' ' || SUBSTR(SQLERRM, 1, 100));

      END;

      /

     
Note: Replace variable values with your values.

-----------------------------------------------------------------

4) Change User name using API:

   Using below API you will changer User Name.

       Input (Mandantory)

         x_old_user_name:     Old User Name

         x_new_user_name:     New User Name

                      

    For Example:

        DECLARE

             lc_old_user_name   VARCHAR2(50)    := 'XX007MN';

             lc_new_user_name   VARCHAR2(50)   := 'XX10LMN';

        BEGIN

              FND_USER_PKG.change_user_name(x_old_user_name => lc_old_user_name,

                                         x_new_user_name => lc_new_user_name

                                        );

          COMMIT;

           dbms_output.put_line('Successfully changed UserName from: '||lc_old_user_name|| ' to: '||lc_new_user_name);

        EXCEPTION

          WHEN OTHERS THEN

            dbms_output.put_line('Unable to change User name due to' || SQLCODE || ' ' || SUBSTR(SQLERRM, 1, 100));

        END;

        /

Note: Replace variable values with your values.

After completed script check in Base table:

SELECT * FROM fnd_user WHERE user_name = 'XX10LMN';