API to apply HOLD at Sale Order in Oracle APPS R12

/*API to apply HOLD at Sale Order in Oracle APPS R12*/ 

CREATE OR replace PROCEDURE Xx_om_hold_new ( retcode OUT VARCHAR2,

                                            errbuff OUT VARCHAR2,
                                            p_order_number IN NUMBER,
                                            p_user_id      IN NUMBER )
IS
  l_hold_source_rec oe_holds_pvt.hold_source_rec_type;
  l_file_val      VARCHAR2(100);
  l_error_message VARCHAR2(2000);
  l_msg_index_out NUMBER(10);
  x_return_status VARCHAR2(2000);
  x_msg_count     NUMBER;
  x_msg_data      VARCHAR2(2000);
  l_count         NUMBER := 0;
  CURSOR c1 IS
    SELECT   ooh.order_number,
             ooh.header_id,
             ooh.org_id,
             ooh.created_by user_id
    FROM     oe_order_headers_all ooh,
             so_order_types_all sot,
             ar_customers ac,
             ar_payment_schedules_all aps,
             xxk_order_ship_to_bill_to xosb
    WHERE    1 = 1
    AND      ooh.order_type_id = sot.order_type_id
    AND      ac.customer_id = ooh.sold_to_org_id
    AND      ac.customer_id = aps.customer_id
    AND      ooh.booked_flag = 'Y'
    AND      ooh.org_id = aps.org_id
    AND      ooh.order_number = xosb.order_number
    AND      (
                      Upper(sot.name) NOT LIKE '%TESTING%'
             OR       Upper(sot.name) NOT LIKE '%RETURN%' )
    AND      ac.customer_category_code IN ('DEALER',
                                           'AUTHORIZED DEALER',
                                           'AUTHORIZED RETAILER',
                                           'AUTHORIZED CHANNEL PARTNER',
                                           'DEALER DIRECT')
    AND      ooh.org_id = <<org_id>>
    AND      trunc(ooh.ordered_date) > '01-APR-20'
    AND      ooh.order_number = p_order_number
    AND      (
                      upper(xosb.ship_to_address1
                               || xosb.ship_to_address2
                               || xosb.ship_to_address3
                               || xosb.ship_to_address4
                               || xosb.ship_to_address5) LIKE '%INDIA%'
             OR       upper(xosb.ship_to_address1
                               || xosb.ship_to_address2
                               || xosb.ship_to_address3
                               || xosb.ship_to_address4
                               || xosb.ship_to_address5) LIKE '%IN%' )
             /* AND NOT EXISTS (
SELECT
1
FROM
oe_order_holds_all oh
WHERE
oh.header_id = ooh.header_id
AND ( nvl(oh.released_flag, 'N') = 'N'
OR nvl(oh.released_flag, 'Y') = 'Y' )
)*/
    GROUP BY ooh.order_number,
             ooh.header_id,
             ooh.org_id,
             ooh.created_by
    ORDER BY 1,
             3;

BEGIN
  FOR i IN c1
  LOOP
    -- Source the Environment variables
    fnd_global.Apps_initialize(user_id => p_user_id --FND_GLOBAL.USER_ID
                               ,
                               resp_id => fnd_global.resp_id,
                               resp_appl_id => fnd_global.resp_appl_id);
    oe_debug_pub.initialize;
    oe_debug_pub.Setdebuglevel(5); -- Use 5 for the most debuging output
    oe_debug_pub.debug_on;
    l_file_val := oe_debug_pub.Set_debug_mode('FILE');
    fnd_file.Put_line(fnd_file.output, '.');
    fnd_file.Put_line(fnd_file.output, ' Log File Name and Location :- '
    || oe_debug_pub.g_dir
    || '/'
    || oe_debug_pub.g_file);
    fnd_file.Put_line(fnd_file.output, '.');
    mo_global.Set_policy_context('S', i.org_id);
    l_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
    l_hold_source_rec.hold_id := <<hold_id>>;        --'Advance not Received but Order Released'  , --ln_hold_id ;
    l_hold_source_rec.hold_entity_code := 'O';       -- order level hold [Order Management Responsibility=> Setup > Quickcodes > Order Management => LookUP = HOLD_ENTITY_DESC]
    l_hold_source_rec.hold_entity_id := i.header_id; -- header_id of the order
    l_hold_source_rec.header_id := i.header_id;      -- header_id of the order
    --   l_hold_source_rec.line_id                                := I.line_id;    -- line_id of the order
    x_return_status := NULL;
    x_msg_data := NULL;
    x_msg_count := NULL;
    oe_debug_pub.ADD('Just Before calling OE_Holds_PUB.Apply_Holds:');
    fnd_file.Put_line(fnd_file.output, 'Just before calling OE_Holds_PUB.Apply_Holds:');
    oe_holds_pub.Apply_holds(p_api_version => 1.0,
                             p_init_msg_list => fnd_api.g_true,
                             p_commit => fnd_api.g_true,
                             p_hold_source_rec => l_hold_source_rec,
                             x_return_status => x_return_status,
                             x_msg_count => x_msg_count,
                             x_msg_data => x_msg_data);
    oe_debug_pub.ADD('Just After calling OE_Holds_PUB.Apply_Holds:');
    fnd_file.Put_line(fnd_file.output, 'Just after calling OE_Holds_PUB.Apply_Holds:');
    fnd_file.Put_line(fnd_file.output, 'x_msg_data:'
    || x_msg_data);
    -- Check Return Status
    IF x_return_status <> fnd_api.g_ret_sts_success THEN
      FOR i IN 1..x_msg_count
      LOOP
        apps.fnd_msg_pub.Get(p_msg_index => i,
                             p_encoded => fnd_api.g_false,
                             p_data => x_msg_data,
                             p_msg_index_out => l_msg_index_out );
        IF l_error_message IS NULL THEN
          l_error_message := Substr(x_msg_data, 1, 250);
        ELSE
          l_error_message := l_error_message
          || ' /'
          || Substr(x_msg_data, 1, 250);
        END IF;
      END LOOP;
      fnd_file.Put_line(fnd_file.output, '*****************************************');
      fnd_file.Put_line(fnd_file.output, 'API Error : '
      || l_error_message);
      fnd_file.Put_line(fnd_file.output, '*****************************************');
      ROLLBACK;
    ELSE
      fnd_file.Put_line(fnd_file.output, '*****************************************');
      fnd_file.Put_line(fnd_file.output, 'Hold Applied Successfully on: '
      || i.order_number);
      fnd_file.Put_line(fnd_file.output, '*****************************************');
      COMMIT;
    END IF;
    l_count := l_count + 1;
  END LOOP;
  fnd_file.Put_line(fnd_file.output, 'No of Record Updated. :'
  || l_count);
EXCEPTION
WHEN OTHERS THEN
  fnd_file.Put_line(fnd_file.output, 'Error is '
  || SQLCODE
  || '?'
  || SQLERRM);
END xx_om_hold_new;

-------------------------*****-------------------------

For applying HOLD at Sales Order Line, I was facing issue with above code, therefore updated it as below:

DECLARE
    l_file            VARCHAR2(100);
    l_return_status   VARCHAR2(30);
    l_msg_data        VARCHAR2(4000);
    l_msg_count       NUMBER;
    l_msg_index       NUMBER;
    l_data            VARCHAR2(2000);
    l_hold_source_rec oe_holds_pvt.hold_source_rec_type;
    x_debug_file      VARCHAR2(100);
    l_msg_index_out   NUMBER(10);
    l_org             NUMBER := 105;
    l_file_val        VARCHAR2(100);
    l_order_tbl       oe_holds_pvt.order_tbl_type;
BEGIN
    fnd_global.Apps_initialize(user_id => fnd_global.user_id, resp_id =>
    fnd_global.resp_id, resp_appl_id => fnd_global.resp_appl_id);

    dbms_output.ENABLE(1000000);

    oe_debug_pub.initialize;

    oe_debug_pub.Setdebuglevel(5);

    oe_debug_pub.debug_on;

    l_file_val := oe_debug_pub.Set_debug_mode('FILE');

    dbms_output.Put_line('.');

    dbms_output.Put_line(' … Log File Name and Location :- '
                         ||oe_debug_pub.g_dir
                         ||'/'
                         ||oe_debug_pub.g_file);

    dbms_output.Put_line('.');

    mo_global.Set_policy_context('S', l_org);

    l_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;

    l_order_tbl.DELETE;

    L_order_tbl(1).header_id := 9196875;

    L_order_tbl(1).line_id := 86045495;

    l_hold_source_rec.hold_id := 2075; --hold_id

    --l_hold_source_rec.hold_entity_code := 'O'; --order level hold
    --l_hold_source_rec.hold_entity_id := 86045495; --header_id of the order  9196875  86045495
    --l_hold_source_rec.header_id := 9196875; --header_id of the order
    l_return_status := NULL;

    l_msg_data := NULL;

    l_msg_count := NULL;

    oe_debug_pub.ADD('Just Before calling OE_Holds_PUB.Apply_Holds:');

    dbms_output.Put_line('Just before calling OE_Holds_PUB.Apply_Holds:');

    oe_holds_pub.Apply_holds (p_api_version => 1.0,
    p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true,
    p_order_tbl => l_order_tbl, p_hold_id => 2075,
    --p_hold_source_rec => l_hold_source_rec,
    x_return_status => l_return_status, x_msg_count => l_msg_count,
    x_msg_data => l_msg_data);

    oe_debug_pub.ADD('Just After calling OE_Holds_PUB.Apply_Holds:');

    dbms_output.Put_line('Just after calling OE_Holds_PUB.Apply_Holds:');

    --Check Return Status
    IF l_return_status = fnd_api.g_ret_sts_success THEN
      oe_debug_pub.ADD('success');

      dbms_output.Put_line('success:');

      COMMIT;
    ELSIF l_return_status IS NULL THEN
      dbms_output.Put_line('Status is null');
    ELSE
      oe_debug_pub.ADD('failure');

      dbms_output.Put_line('failure:'
                           ||Nvl(l_msg_data, 'nothingkdkd'));

      ROLLBACK;
    END IF;

    --Display Return Status
    oe_debug_pub.ADD('process ORDER ret status IS: '
                     || l_return_status);

    fnd_file.Put_line(fnd_file.output, 'process ORDER ret status IS: '
                                       ||l_return_status);

    oe_debug_pub.ADD('process ORDER msg data IS: '
                     || l_msg_data);

    fnd_file.Put_line(fnd_file.output, 'process ORDER msg data IS: '
                                       ||l_msg_data);

    oe_debug_pub.ADD('process ORDER msg COUNT IS: '
                     || l_msg_count);

    fnd_file.Put_line(fnd_file.output, 'process ORDER msg COUNT IS: '
                                       ||l_msg_count);

    oe_debug_pub.debug_off;

    dbms_output.Put_line('Error is nothing .');
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.Put_line('Error is '
                           ||SQLCODE
                           ||'—'
                           ||SQLERRM);
END;