Release Hold from Sales Order

CREATE OR REPLACE PROCEDURE Release_Hold_prc (
   p_order_number      IN     NUMBER,
   p_header_id         IN     NUMBER,
   x_hold_rel_status      OUT VARCHAR2)
IS
   ---------------------------
   -- PRIVATE VARIABLES
   ---------------------------
   l_msg_count             NUMBER := 0;
   l_msg_data              VARCHAR2 (500) := NULL;
   l_result_out            VARCHAR2 (50) := NULL; -- Pass or Fail Credit Check
   l_return_status         VARCHAR2 (500) := NULL;
   l_order_tbl             OE_HOLDS_PVT.order_tbl_type;
   l_order_tbl_init        OE_HOLDS_PVT.order_tbl_type;
   g_release_reason_code   VARCHAR2 (50) := 'XX_VALIDATION_PASS';
BEGIN
   l_order_tbl := l_order_tbl_init;

   l_order_tbl (1).header_id := p_header_id;

   DBMS_OUTPUT.put_line (
      'Calling the OE_HOLDS_PUB.Release_Holds API to Relese hold');

   -- Calling the API to release hold on order.
   OE_HOLDS_PUB.Release_Holds (
      p_api_version           => 1.0,
      p_init_msg_list         => FND_API.G_TRUE,
      p_commit                => FND_API.G_FALSE,
      p_validation_level      => NULL,
      p_order_tbl             => l_order_tbl,
      p_hold_id               => NULL,                            --l_hold_id,
      p_release_reason_code   => g_release_reason_code,
      p_release_comment       => 'Order is passed',
      x_return_status         => l_return_status,
      x_msg_count             => l_msg_count,
      x_msg_data              => l_msg_data);

   DBMS_OUTPUT.put_line ('Return Status is :: ' || l_return_status);
   DBMS_OUTPUT.put_line ('Msg Count is     :: ' || l_msg_count);
   DBMS_OUTPUT.put_line ('Msg Data is      :: ' || l_msg_data);

   --If API is Success return 'PASS' or return 'FAIL'.
   IF NVL (l_return_status, 'X') = FND_API.G_RET_STS_SUCCESS
   THEN
      x_hold_rel_status := l_return_status;
   ELSE
      x_hold_rel_status := l_return_status;

      DBMS_OUTPUT.put_line ('hold failure');


      FOR i IN 1 .. l_msg_count
      LOOP
         l_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F');

         DBMS_OUTPUT.put_line ('failure:' || l_msg_data);
      END LOOP;
   END IF;

   DBMS_OUTPUT.put_line ('Exiting from Release_Hold_prc');
EXCEPTION
   WHEN OTHERS
   THEN
      l_error := SQLERRM;
      DBMS_OUTPUT.put_line (
            'Error while releasing hold on Header ID :: '
         || p_header_id
         || ' due to : '
         || l_error);
END Release_Hold_prc;