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;