Oracle PLSQL - Need to Reassign the PR from one user to another user

 DECLARE

    x_errbuf           VARCHAR2(2000);

    x_retcode          VARCHAR2(2000);

    p_curnt_apprvr     VARCHAR2(200) := 'RYANH';  --From User Name

    p_new_apprvr       VARCHAR2(200) := 'DCHAU'; --To User Name

    p_requisition_no   VARCHAR2(200) := '2040807';

    p_comment          VARCHAR2(200) := 'INC0621049';

    p_workflow_type    VARCHAR2(200) := 'REQAPPRV';

    p_org_id           NUMBER;

    lc_wf_item_key     po_requisition_headers_all.wf_item_key%TYPE;

    ln_notification_id wf_notifications.notification_id%TYPE;

    lc_nid_flag        VARCHAR2(1) := 'Y';

    l_userid           NUMBER := 42520;  --'RPERABATHULA' --DBA User id

    l_user             VARCHAR2(50);

    l_current_appr     VARCHAR2(50);

    l_new_appr         VARCHAR2(50);

   

    CURSOR cur_nid (

        p_recipient_role wf_notifications.recipient_role%TYPE,

        p_message_type   wf_notifications.message_type%TYPE

    ) IS

    SELECT

        notification_id,

        item_key

    FROM

        wf_notifications

    WHERE

            recipient_role = p_recipient_role

        AND status = 'OPEN'

        AND message_type = p_message_type;

 

    CURSOR cur_req (

        p_wf_key wf_notifications.item_key%TYPE

    ) IS

    SELECT

        segment1

    FROM

        po_requisition_headers_all

    WHERE

        wf_item_key = p_wf_key;

 

BEGIN

    apps.fnd_profile.get('USER_ID', l_userid);

    BEGIN -- added by prabha -scr 25601

        SELECT

            description

        INTO l_current_appr

        FROM

            fnd_user

        WHERE

            user_name = p_curnt_apprvr;

 

        SELECT

            description

        INTO l_new_appr

        FROM

            fnd_user

        WHERE

            user_name = p_new_apprvr;

 

        SELECT

            description

        INTO l_user

        FROM

            fnd_user

        WHERE

            user_id = l_userid;

 

    EXCEPTION --added by prabha scr 25602

        WHEN OTHERS THEN

            NULL;

    END;

 

    IF p_requisition_no IS NOT NULL THEN

        dbms_output.put_line('start' || lc_nid_flag);

       

        dbms_output.put_line(rpad('CURRENT APPROVER', 35, ' ')

                                        || rpad('NEW APPROVER', 35, ' ')

                                        || rpad('REQUISITION NUMBER', 25, ' ')

                                        || rpad('NOTES', 50, ' ')

                                        || rpad('UPDATED BY', 35, ' ')

                                        || rpad('DATE UPDATED', 15, ' '));

        dbms_output.put_line(rpad(l_current_appr, 35, ' ')

                                        || rpad(l_new_appr, 35, ' ')

                                        || rpad(p_requisition_no, 25, ' ')

                                        || rpad(nvl(p_comment, ' '), 50, ' ')

                                        || rpad(l_user, 35, ' ')

                                        || rpad(sysdate, 15, ' '));

       

 

        BEGIN

            SELECT

                wf_item_key

            INTO lc_wf_item_key

            FROM

                po_requisition_headers_all

            WHERE

                    segment1 = p_requisition_no

                AND authorization_status <> 'APPROVED' --Only need to pick the Un Approved PR

                ;

 

        EXCEPTION

            WHEN no_data_found THEN

                lc_wf_item_key := NULL;

        END;

 

        BEGIN

            SELECT

                notification_id

            INTO ln_notification_id

            FROM

                wf_notifications

        --WHERE  CONTEXT LIKE '%' || lc_wf_item_key || '%'  --commented by prabha SCR25601

            WHERE

                    user_key = p_requisition_no  -- added by prabha SCR 25602

                AND recipient_role = p_curnt_apprvr

                AND status = 'OPEN'

                AND message_type = p_workflow_type;

 

        EXCEPTION

            WHEN no_data_found THEN

                lc_nid_flag := 'N';

  

                dbms_output.put_line('Error: No Notifications found for the given Requisition#. ' || p_requisition_no);

       

        END;

 

        IF lc_nid_flag = 'Y' THEN

            BEGIN

                fnd_wf_notification.forward(ln_notification_id -- nid

                , p_new_apprvr -- new_role

                , p_comment -- forward_comment

                , p_curnt_apprvr -- user

                , 0 -- cnt

                );

            EXCEPTION

                WHEN OTHERS THEN

--                    fnd_file.put_line(fnd_file.log, 'Error: ' || sqlerrm);

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

            END;

        --<start> else part added by prabha SCR 25602

            dbms_output.put_line('if yes' || lc_nid_flag);

        ELSE

            dbms_output.put_line('if no' || lc_nid_flag);

            BEGIN

                SELECT

                    notification_id

                INTO ln_notification_id

                FROM

                    wf_notifications

                WHERE

                    subject LIKE '%'

                                 || p_requisition_no

                                 || '%'

                    AND recipient_role = p_curnt_apprvr

                    AND status = 'OPEN'

                    AND message_type = p_workflow_type;

 

                dbms_output.put_line(ln_notification_id);

            EXCEPTION

                WHEN no_data_found THEN

                    lc_nid_flag := 'N';

                    dbms_output.put_line('expn' || lc_nid_flag);

                 

                     dbms_output.put_line('Error: No Notifications found for the given Requisition even with subject#. ' ||

                    p_requisition_no);

            END;

 

            BEGIN

                fnd_wf_notification.forward(ln_notification_id -- nid

                , p_new_apprvr -- new_role

                , p_comment -- forward_comment

                , p_curnt_apprvr -- user

                , 0 -- cnt

                );

            EXCEPTION

                WHEN OTHERS THEN

--                    fnd_file.put_line(fnd_file.log, 'Error: ' || sqlerrm);

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

            END;

        --<end> of scr25602

        END IF; -- lc_nid_flag = 'Y'

    ELSIF p_requisition_no IS NULL THEN

 

                                       

        dbms_output.put_line(rpad('CURRENT APPROVER', 35, ' ')

                                        || rpad('NEW APPROVER', 35, ' ')

                                        || rpad('REQUISITION NUMBER', 25, ' ')

                                        || rpad('NOTES', 50, ' ')

                                        || rpad('UPDATED BY', 35, ' ')

                                        || rpad('DATE UPDATED', 15, ' '));

 

        FOR rec_nid IN cur_nid(p_curnt_apprvr, p_workflow_type) LOOP

            FOR i IN cur_req(rec_nid.item_key) LOOP

                BEGIN

                    fnd_wf_notification.forward(rec_nid.notification_id -- nid

                    , p_new_apprvr -- new_role

                    , p_comment -- forward_comment

                    , p_curnt_apprvr -- user

                    , 0 -- cnt

                    );

                EXCEPTION

                    WHEN OTHERS THEN

--                        fnd_file.put_line(fnd_file.log, 'Error: ' || sqlerrm);

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

                END;

                dbms_output.put_line(rpad(l_current_appr, 35, ' ')

                                                || rpad(l_new_appr, 35, ' ')

                                                || rpad(i.segment1, 25, ' ')

                                                || rpad(nvl(p_comment, ' '), 50, ' ')

                                                || rpad(l_user, 35, ' ')

                                                || rpad(sysdate, 15, ' '));

            END LOOP;

        END LOOP;

 

    END IF; -- p_requisition_no

    COMMIT; -- SCR16372 added commit and below exception.

EXCEPTION

    WHEN OTHERS THEN

 

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

END;

Oracle EBS - XDO Loader Script to Download & Upload BI Publisher Data Template and XML Template

 --Download XML file --

java oracle.apps.xdo.oa.util.XDOLoader \

DOWNLOAD \

-DB_USERNAME apps \

-DB_PASSWORD apps \

-JDBC_CONNECTION db.test.com:1605/ebs_BSA \

-LOB_TYPE DATA_TEMPLATE  \

-APPS_SHORT_NAME XXGT \

-LOB_CODE MVL_NOTF_PO_BUY_RCVD_PRCNTGE \

-OUTPUT_PATH /tmp/30oct25/MVL_NOTF_PO_BUY_RCVD_PRCNTGE.xml

 

--Download XLS Template file--

java oracle.apps.xdo.oa.util.XDOLoader \

DOWNLOAD \

-DB_USERNAME apps \

-DB_PASSWORD apps \

-JDBC_CONNECTION db.test.com:1605/ebs_BSA \

-LOB_TYPE TEMPLATE  \

-APPS_SHORT_NAME XXGT \

-LOB_CODE MVL_NOTF_PO_BUY_RCVD_PRCNTGE \

-OUTPUT_PATH /tmp/30oct25/MVL_NOTF_PO_BUY_RCVD_PRCNTGE.xls

 

 

--Download RTF Template file--

java oracle.apps.xdo.oa.util.XDOLoader \

DOWNLOAD \

-DB_USERNAME apps \

-DB_PASSWORD apps \

-JDBC_CONNECTION db.test.com:1605/ebs_BSA \

-LOB_TYPE TEMPLATE  \

-APPS_SHORT_NAME XXGT \

-LOB_CODE MVL_NOTF_PO_BUY_RCVD_PRCNTGE \

-OUTPUT_PATH /tmp/30oct25/MVL_NOTF_PO_BUY_RCVD_PRCNTGE.rtf

Oracle EBS - How to Enable Debug log on user's session

  1) As System Administrator responsibility

       Navigation: Profile -> System

           a) In the FIND window,

                  Enter User Name: User submitting the request
                  Search Profile: FND: Diag%

           b) Set the following profile option at User level:
                  FND: Diagnostics -> Yes

           c) Click the FIND flashlight and search Profile: FND: Debug%

               Set the following profile options at USER level:

                  FND: Debug Log Enabled =  Yes
                  FND: Debug Log Module = %
                  FND: Debug Log Level = Statement

                  FND: Debug Log Mode = Asynchronous with Cross-Tier Sequencing (SITE level)

  2) Run the following query to get the current log sequence
          
      SELECT MAX(log_sequence)
      FROM FND_LOG_MESSAGES;
    
  3) Perform the action that is giving problems.

  4) Run the following query to obtain the FND Messages while replicating the error in step 3:

      SELECT *
      FROM fnd_log_messages
      WHERE log_sequence > &max_log_sequence_from_step_2
      ORDER by log_sequence;

  5) Upload to the SR results of step #4 in an EXCEL spreadsheet, formatted with column headings for readability.

   6) After completion, reset step 1c "FND: Debug Log Enabled" back to "No" to avoid impact on performance.

Oracle EBS - R12 - Inventory - Oracle API to Update Lot Origin Date of Item Lot Numbers

 -- R12 - INV - Sample Script to Update Lot Number Lot Origin Date usinginv_lot_api_pub.update_inv_lot

DECLARE

    l_object_id      NUMBER;

    l_return_status  VARCHAR2(1);

    l_msg_count      NUMBER;

    l_msg_data       VARCHAR2(4000);

    l_expire_date    DATE;

    x_lot_rec        mtl_lot_numbers%rowtype;

    l_lot_rec        mtl_lot_numbers%rowtype;

    l_source         NUMBER;

    l_api_version    NUMBER := 1.0;

    l_init_msg_list  VARCHAR2(1000) := fnd_api.g_false;

    l_commit         VARCHAR2(1000) := fnd_api.g_false;

    CURSOR c_item_info IS

    SELECT

        inventory_item_id,

        organization_id,

        lot_number,

        creation_date,

        origination_date,

        c_attribute1

    FROM

        mtl_lot_numbers

    WHERE

            1 = 1

        AND trunc(origination_date) BETWEEN trunc(TO_DATE('03/03/24', 'MM/DD/YY')) AND trunc(TO_DATE('03/05/24', 'MM/DD/YY'))

        AND trunc(creation_date) != trunc(origination_date)

        AND organization_id IN ( 85, 97 )

--AND TRUNC(CREATION_DATE) >= SYSDATE-150

--and C_ATTRIBUTE1='WMOTCWRJAB'

--and c_attribute1 is not null

        AND lot_number = '1710010' --'1542009' --'1423007'

        ;

BEGIN

-- initialization required for R12

    mo_global.set_policy_context('S', 102);

    mo_global.init('INV');

-- Initialization for Organization_id

    inv_globals.set_org_id(102);

-- initialize environment

    fnd_global.apps_initialize(user_id => 11445,-----------------------------------> pass in user_id User Id=11445 ,

                              resp_id => 20634,

                              resp_appl_id => 401);

    

    FOR i IN c_item_info LOOP

    

    l_lot_rec.inventory_item_id:=i.inventory_item_id;

    l_lot_rec.organization_id:=i.organization_id;

    l_lot_rec.lot_number:=i.lot_number;

    l_lot_rec.origination_date:=i.creation_date;

    

        dbms_output.put_line('Calling inv_lot_api_pub.update_inv_lot API to Update Lot Numbers');

        dbms_output.put_line('*********************************************');

        inv_lot_api_pub.update_inv_lot(x_return_status => l_return_status, x_msg_count => l_msg_count,

                                      x_msg_data => l_msg_data,

                                      x_lot_rec => x_lot_rec,

                                      p_lot_rec => l_lot_rec,

                                      p_source => l_source,

                                      p_api_version => 1.0,

                                      p_init_msg_list => l_init_msg_list,

                                      p_commit => l_commit);

        dbms_output.put_line('The Status Returned by the API is => ' || l_return_status);

        IF l_return_status = fnd_api.g_ret_sts_success THEN

            COMMIT;

        ELSE

            ROLLBACK;

        END IF;

        dbms_output.put_line('x_msg_count :' || l_msg_count);

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

    END LOOP;

END;

Oracle EBS - SQL Query to find Cloned Date, Version Details, URL of Oracle Applications

Subject: SQL Query to find Cloned Date, Version Details, URL of Oracle Applications

Query to get Instance Version details

SELECT  product, version, status FROM  product_component_version;  

Query to  get cloned date of an oracle instance

SELECT resetlogs_time FROM   v$database; 

Query to get the front end URL from back-end

SELECT home_url FROM   icx_parameters;   

Oracle EBS - Not able to create Payment in Past Date

 

Issue: 

While Creating Payment and Enter Payment Date for Past Open Period getting below error message

Error:

APP-SQLAP-10026: The Payment Date must be on or after the System Date.

Resolution:

Navigation: Payable Responsibility > Setup > Payable Option > Payment Tab > Check All Pre-Date

Oracle EBS: How to Create Request Group and Assign Request Group to Responsibility

  

Step1: Create a Request Group.

Navigate: System Administrator Responsibility > Security > Responsibility > Request

Group Name: TLM OM Inquiry

Application: Order Management

Type: Program

Name: Telamon SO Cancelled Item Details Report

 

Step2: Assign Request Group to Responsibility.

Navigate: System Administrator Responsibility > Security > Responsibility > Define

Query Responsibility Name: TLM Order Management Inquiry

Add Request Group: TLM OM Inquiry

Save it.

 

 

Oracle EBS - Order Management Sales Order Line Need to Split using Oracle API

set serveroutput on;

DECLARE

 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 ;

 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);

 p_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;

 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_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;

 X_DEBUG_FILE VARCHAR2(100);

 l_line_tbl_index NUMBER;

 l_msg_index_out NUMBER(10);

BEGIN

 dbms_output.enable(1000000);

 fnd_global.apps_initialize(<user_id>,<resp_id>,<app_id>); -- pass in user_id, responsibility_id, and application_id

 oe_msg_pub.initialize;

 oe_debug_pub.initialize;

 X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');

 oe_debug_pub.SetDebugLevel(5); -- Use 5 for the most debuging output, I warn  you its a lot of data

 dbms_output.put_line('START OF NEW DEBUG');

--This is to UPDATE order line

 l_line_tbl_index :=1; -- First Existing Line Update

-- Changed attributes

 l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

 l_header_rec.header_id := <header_id>; -- header_id of the order

 l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;

 l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;

 l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE; --** For the Existing line Operation is Update

 l_line_tbl(l_line_tbl_index).split_by := <user_id>; -- Pass user_id who is splitting the line

 l_line_tbl(l_line_tbl_index).split_action_code := 'SPLIT';

 l_line_tbl(l_line_tbl_index).header_id := <header_id>; -- header_id of the order

 l_line_tbl(l_line_tbl_index).line_id := <line_id>; -- line_id of the order line which needs to split

 l_line_tbl(l_line_tbl_index).ordered_quantity := <order_qty>; -- new ordered quantity of the line which you want to split (Original qty = 100 and now want to split as 70 and 30)

 l_line_tbl(l_line_tbl_index).change_reason := 'MISC'; -- Enter the reason code for this Split

 l_line_tbl_index :=2; --For New Split Line

 l_line_tbl(l_line_tbl_index ) := OE_ORDER_PUB.G_MISS_LINE_REC;

 l_line_tbl(l_line_tbl_index ).operation := OE_GLOBALS.G_OPR_CREATE; --** For the Splited line Operation is Create

 l_line_tbl(l_line_tbl_index ).split_by := 'USER'; -- Enter user(Bug 25793299)

 l_line_tbl(l_line_tbl_index ).split_action_code := 'SPLIT';

 l_line_tbl(l_line_tbl_index ).split_from_line_id := <line_id>; -- line_id of  original line from which the line is splitted

 l_line_tbl(l_line_tbl_index ).inventory_item_id := <item_id>; -- inventory item id

 l_line_tbl(l_line_tbl_index ).ordered_quantity := 30; -- Enter the remaining qty after Split in ordered quantity (Enter 30 as you entered as 70 at top)

-- CALL TO PROCESS ORDER

 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 => p_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('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);

-- Retrieve messages

 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 is: ' || l_msg_data);

  DBMS_OUTPUT.PUT_LINE('message index is: ' || 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('SO Line Qty Update Sucessful');

 ELSE

  dbms_output.put_line('AO Line Qty update Failed');

 END IF;

END;

/

Commit;