Oracle EBS Function: Get Inventory Item On-Hand Quantity

CREATE OR REPLACE FUNCTION xx_onhand_qty (

    p_inv_item_id  IN  VARCHAR2,

    p_org_id       IN  NUMBER,

    p_sub_inv      IN  VARCHAR2,

    p_loc_id       IN  NUMBER

) RETURN NUMBER IS

    x_return_status        VARCHAR2(50);

    x_msg_count            VARCHAR2(50);

    x_msg_data             VARCHAR2(50);

    v_item_id              NUMBER;

    v_organization_id      NUMBER;

    v_qoh                  NUMBER;

    v_rqoh                 NUMBER;

    v_atr                  NUMBER;

    v_att                  NUMBER;

    v_qr                   NUMBER;

    v_qs                   NUMBER;

    v_lot_control_code     BOOLEAN;

    v_serial_control_code  BOOLEAN;

    l_qty                  NUMBER;

    l_qty_avai_reserve     NUMBER := 0;

    l_qty_avai_transact    NUMBER := 0;

    l_reserved_qty         NUMBER := 0;

BEGIN

    SELECT

        inventory_item_id,

        mp.organization_id

    INTO

        v_item_id,

        v_organization_id

    FROM

        mtl_system_items_b  msib,

        mtl_parameters      mp

    WHERE

            msib.inventory_item_id = p_inv_item_id

        AND msib.organization_id = mp.organization_id

        AND msib.organization_id = p_org_id; -- :organization_code;

    BEGIN

        SELECT

            nvl(SUM(reservation_quantity), 0)

        INTO l_reserved_qty

        FROM

            mtl_reservations

        WHERE

                inventory_item_id = p_inv_item_id

            AND organization_id = p_org_id

            AND subinventory_code = p_sub_inv

            AND locator_id = p_loc_id;

    EXCEPTION

        WHEN OTHERS THEN

            l_reserved_qty := 0;

            dbms_output.put_line('ERROR: ' || sqlerrm);

    END;

    v_qoh := NULL;

    v_rqoh := NULL;

    v_atr := NULL;

    v_lot_control_code := false;

    v_serial_control_code := false;

    fnd_client_info.set_org_context(1);

    inv_quantity_tree_pub.query_quantities(p_api_version_number => 1.0, p_init_msg_lst => 'F',

                                          x_return_status => x_return_status,

                                          x_msg_count => x_msg_count,

                                          x_msg_data => x_msg_data,

                                          p_organization_id => v_organization_id,

                                          p_inventory_item_id => v_item_id,

                                          p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,

                                          p_is_revision_control => false,

                                          p_is_lot_control => v_lot_control_code,

                                          p_is_serial_control => v_serial_control_code,

                                          p_revision => NULL,                          -- p_revision,

                                          p_lot_number => NULL,                        -- p_lot_number,

                                          p_lot_expiration_date => sysdate,

                                          p_subinventory_code => nvl(p_sub_inv, NULL),                 -- p_subinventory_code,

                                          p_locator_id => nvl(p_loc_id, NULL),                        -- p_locator_id,

                                          p_onhand_source => 3,

                                          x_qoh => v_qoh,                    -- Quantity on-hand

                                          x_rqoh => v_rqoh,         --reservable quantity on-hand

                                          x_qr => v_qr,

                                          x_qs => v_qs,

                                          x_att => v_att,               -- available to transact

                                          x_atr => v_atr                 -- available to reserve

                                          );

    l_qty := nvl(v_qoh, 0); --On Hand qty

    l_qty_avai_reserve := nvl(v_atr, 0); --Available to Reserve

    l_qty_avai_transact := nvl(v_att, 0); --Available to Transact

    RETURN nvl((l_qty_avai_transact - l_reserved_qty), 0);

    dbms_output.put_line('On-Hand Quantity: ' || v_qoh);

    dbms_output.put_line('Available to reserve: ' || v_atr);

    dbms_output.put_line('Quantity Reserved: ' || v_qr);

    dbms_output.put_line('Quantity Suggested: ' || v_qs);

    dbms_output.put_line('Available to Transact: ' || v_att);

EXCEPTION

    WHEN OTHERS THEN

        RETURN NULL;

        dbms_output.put_line('ERROR: ' || sqlerrm);

END xx_onhand_qty; 

/

--Testing Script: get onhand qty--

select XX_ONHAND_QTY(8334013,85,'ZONEA',147167) from dual;  --Item_id,Organization_id,Sub_inv,Locator_id

Oracle EBS - Procedure to create duplicate sales order based on existing sales order using Oracle API.

Requirement: We need to create duplicate Sales Order Based on Existing Sales Order using Oracle API.

CREATE OR REPLACE PROCEDURE xx_create_so_prc (

    p_order_num IN NUMBER

) AS

    l_return_status               VARCHAR2(1000);

    l_msg_count                   NUMBER;

    l_msg_data                    VARCHAR2(1000);

    p_api_version_number          NUMBER := 1.0;

    p_init_msg_list               VARCHAR2(10) := fnd_api.g_false;

    p_return_values               VARCHAR2(10) := fnd_api.g_false;

    p_action_commit               VARCHAR2(10) := fnd_api.g_false;

    x_return_status               VARCHAR2(1);

    x_msg_count                   NUMBER;

    x_msg_data                    VARCHAR2(100);

    l_header_rec                  oe_order_pub.header_rec_type;

    l_line_tbl                    oe_order_pub.line_tbl_type;

    l_action_request_tbl          oe_order_pub.request_tbl_type;

    l_header_adj_tbl              oe_order_pub.header_adj_tbl_type;

    l_line_adj_tbl                oe_order_pub.line_adj_tbl_type;

    l_header_scr_tbl              oe_order_pub.header_scredit_tbl_type;

    l_line_scredit_tbl            oe_order_pub.line_scredit_tbl_type;

    l_request_rec                 oe_order_pub.request_rec_type;

    x_header_rec                  oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

    p_old_header_rec              oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;

    p_header_val_rec              oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

    p_old_header_val_rec          oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;

    p_header_adj_tbl              oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

    p_old_header_adj_tbl          oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;

    p_header_adj_val_tbl          oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

    p_old_header_adj_val_tbl      oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;

    p_header_price_att_tbl        oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

    p_old_header_price_att_tbl    oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;

    p_header_adj_att_tbl          oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

    p_old_header_adj_att_tbl      oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;

    p_header_adj_assoc_tbl        oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

    p_old_header_adj_assoc_tbl    oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;

    p_header_scredit_tbl          oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

    p_old_header_scredit_tbl      oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;

    p_header_scredit_val_tbl      oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

    p_old_header_scredit_val_tbl  oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;

    x_line_val_tbl                oe_order_pub.line_val_tbl_type;

    x_line_adj_tbl                oe_order_pub.line_adj_tbl_type;

    x_line_adj_val_tbl            oe_order_pub.line_adj_val_tbl_type;

    x_line_price_att_tbl          oe_order_pub.line_price_att_tbl_type;

    x_line_adj_att_tbl            oe_order_pub.line_adj_att_tbl_type;

    x_line_adj_assoc_tbl          oe_order_pub.line_adj_assoc_tbl_type;

    x_line_scredit_tbl            oe_order_pub.line_scredit_tbl_type;

    x_line_scredit_val_tbl        oe_order_pub.line_scredit_val_tbl_type;

    x_lot_serial_tbl              oe_order_pub.lot_serial_tbl_type;

    x_lot_serial_val_tbl          oe_order_pub.lot_serial_val_tbl_type;

    x_action_request_tbl          oe_order_pub.request_tbl_type;

    p_line_tbl                    oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

    p_old_line_tbl                oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;

    p_line_val_tbl                oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

    p_old_line_val_tbl            oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;

    p_line_adj_tbl                oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

    p_old_line_adj_tbl            oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;

    p_line_adj_val_tbl            oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

    p_old_line_adj_val_tbl        oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;

    p_line_price_att_tbl          oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

    p_old_line_price_att_tbl      oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;

    p_line_adj_att_tbl            oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

    p_old_line_adj_att_tbl        oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;

    p_line_adj_assoc_tbl          oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

    p_old_line_adj_assoc_tbl      oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;

    p_line_scredit_tbl            oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

    p_old_line_scredit_tbl        oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;

    p_line_scredit_val_tbl        oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

    p_old_line_scredit_val_tbl    oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;

    p_lot_serial_tbl              oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

    p_old_lot_serial_tbl          oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;

    p_lot_serial_val_tbl          oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

    p_old_lot_serial_val_tbl      oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;

    p_action_request_tbl          oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl;

    x_header_val_rec              oe_order_pub.header_val_rec_type;

    x_header_adj_tbl              oe_order_pub.header_adj_tbl_type;

    x_header_adj_val_tbl          oe_order_pub.header_adj_val_tbl_type;

    x_header_price_att_tbl        oe_order_pub.header_price_att_tbl_type;

    x_header_adj_att_tbl          oe_order_pub.header_adj_att_tbl_type;

    x_header_adj_assoc_tbl        oe_order_pub.header_adj_assoc_tbl_type;

    x_header_scredit_tbl          oe_order_pub.header_scredit_tbl_type;

    x_header_scredit_val_tbl      oe_order_pub.header_scredit_val_tbl_type;

    x_debug_file                  VARCHAR2(100);

    l_line_tbl_index              NUMBER;

    l_msg_index_out               NUMBER(10);

    CURSOR c_hdr IS

    SELECT

        transactional_curr_code,

        sold_to_org_id,

        price_list_id,

        sold_from_org_id,

        salesrep_id,

        order_type_id

    FROM

        oe_order_headers_all

    WHERE

        order_number = p_order_num--1219754

        ;

    CURSOR c_line IS

    SELECT

        line_number,

        ordered_quantity,

        ship_from_org_id,

        inventory_item_id,

        schedule_ship_date

    FROM

        oe_order_lines_all

    WHERE

        header_id = (

            SELECT

                header_id

            FROM

                oe_order_headers_all

            WHERE

                order_number = p_order_num

        )--28755419

    ORDER BY

        line_number;

BEGIN

    fnd_global.apps_initialize(user_id => 26406, resp_id => 21623, resp_appl_id => 660);

    mo_global.init('ONT');

    mo_global.set_policy_context('S', 102);

    oe_msg_pub.initialize;

    oe_debug_pub.initialize;

    x_debug_file := oe_debug_pub.set_debug_mode('FILE');

    oe_debug_pub.setdebuglevel(5);

    FOR i IN c_hdr LOOP

        l_header_rec := oe_order_pub.g_miss_header_rec;

        l_header_rec.operation := oe_globals.g_opr_create;

        l_header_rec.transactional_curr_code := i.transactional_curr_code;

        l_header_rec.pricing_date := sysdate;

        l_header_rec.sold_to_org_id := i.sold_to_org_id;

        l_header_rec.price_list_id := i.price_list_id;

        l_header_rec.ordered_date := sysdate;

        l_header_rec.sold_from_org_id := i.sold_from_org_id;

        l_header_rec.salesrep_id := i.salesrep_id;

        l_header_rec.order_type_id := i.order_type_id;

        FOR j IN c_line LOOP

            l_line_tbl_index := 1;

            l_line_tbl(j.line_number) := oe_order_pub.g_miss_line_rec;

            l_line_tbl(j.line_number).operation := oe_globals.g_opr_create;

            l_line_tbl(j.line_number).ordered_quantity := j.ordered_quantity;

            l_line_tbl(j.line_number).ship_from_org_id := j.ship_from_org_id;

            l_line_tbl(j.line_number).inventory_item_id := j.inventory_item_id;

            l_line_tbl(j.line_number).schedule_ship_date := j.schedule_ship_date;

        END LOOP;

    END LOOP;

    oe_order_pub.process_order(p_api_version_number => 1.0, p_init_msg_list => fnd_api.g_false,

                              p_return_values => fnd_api.g_false,

                              p_action_commit => fnd_api.g_false,

                              x_return_status => l_return_status,

                              x_msg_count => l_msg_count,

                              x_msg_data => l_msg_data,

                              p_header_rec => l_header_rec,

                              p_line_tbl => l_line_tbl,

                              p_action_request_tbl => l_action_request_tbl

                                -- OUT PARAMETERS

                              ,

                              x_header_rec => x_header_rec,

                              x_header_val_rec => x_header_val_rec,

                              x_header_adj_tbl => x_header_adj_tbl,

                              x_header_adj_val_tbl => x_header_adj_val_tbl,

                              x_header_price_att_tbl => x_header_price_att_tbl,

                              x_header_adj_att_tbl => x_header_adj_att_tbl,

                              x_header_adj_assoc_tbl => x_header_adj_assoc_tbl,

                              x_header_scredit_tbl => x_header_scredit_tbl,

                              x_header_scredit_val_tbl => x_header_scredit_val_tbl,

                              x_line_tbl => p_line_tbl,

                              x_line_val_tbl => x_line_val_tbl,

                              x_line_adj_tbl => x_line_adj_tbl,

                              x_line_adj_val_tbl => x_line_adj_val_tbl,

                              x_line_price_att_tbl => x_line_price_att_tbl,

                              x_line_adj_att_tbl => x_line_adj_att_tbl,

                              x_line_adj_assoc_tbl => x_line_adj_assoc_tbl,

                              x_line_scredit_tbl => x_line_scredit_tbl,

                              x_line_scredit_val_tbl => x_line_scredit_val_tbl,

                              x_lot_serial_tbl => x_lot_serial_tbl,

                              x_lot_serial_val_tbl => x_lot_serial_val_tbl,

                              x_action_request_tbl => l_action_request_tbl);

    dbms_output.put_line('Order Header_ID : ' || x_header_rec.header_id);

    FOR i IN 1..l_msg_count LOOP

        oe_msg_pub.get(p_msg_index => i, p_encoded => fnd_api.g_false, p_data => l_msg_data, p_msg_index_out => l_msg_index_out);

        dbms_output.put_line('message : ' || l_msg_data);

        dbms_output.put_line('message index : ' || l_msg_index_out);

    END LOOP;

   -- Check the return status

    IF l_return_status = fnd_api.g_ret_sts_success THEN

        dbms_output.put_line('Order Created Successfull');

    ELSE

        dbms_output.put_line('Orcer Creation Failed');

    END IF;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        dbms_output.put_line('Error: ' || sqlerrm);

END xx_create_so_prc;

/

--Testing Scripts:--

--Need to pass existing order number based on what we need to create a new order--

exec XX_CREATE_SO_PRC(1219754);  

--Get the latest created Sales Order Number--

select *

from(

select HEADER_ID,ORDER_NUMBER from 

oe_order_headers_all

where trunc(creation_date) =trunc(sysdate)

order by creation_date desc)

where rownum=1

;

Oracle EBS - How to add responsibility to user using PLSQL Script

 --Add responsibility

DECLARE

    v_user_name    VARCHAR2(30):= '&USER_NAME';

    lc_resp_appl_short_name   VARCHAR2(100)    := 'SYSADMIN';

    lc_responsibility_key          VARCHAR2(100)    := 'SYSTEM_ADMINISTRATOR';

    lc_security_group_key        VARCHAR2(100)    := 'STANDARD';

    ld_resp_start_date                DATE                        := TO_DATE(sysdate);

    ld_resp_end_date                 DATE                        := NULL;

 

BEGIN

     fnd_user_pkg.addresp

     (   username           => v_user_name,

        resp_app             => lc_resp_appl_short_name,

        resp_key             => lc_responsibility_key,

        security_group  => lc_security_group_key,

        description         => NULL,

        start_date           => ld_resp_start_date,

        end_date            => ld_resp_end_date

    );

 

COMMIT;

 

EXCEPTION

            WHEN OTHERS THEN

                        ROLLBACK;

                        DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

Oracle EBS - How to reset oracle application password using PLSQL script

 --Reset Password

DECLARE

  v_user_name    VARCHAR2(30):= '&USER_NAME';

  v_new_password VARCHAR2(30):= 'welcome';

  v_status       BOOLEAN;

BEGIN

  update fnd_user set user_guid = null where user_name = v_user_name;

 

  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name,

                                              newpassword => v_new_password

                                            );

  IF v_status =TRUE THEN

    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);

    COMMIT;

  ELSE

    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    ROLLBACK;

  END IF;

END;