Release Hold API In Order Management. 


CREATE OR REPLACE PROCEDURE  XX_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER) AS
       vreturnstatus   VARCHAR2(2);
       vmsgcount       NUMBER;
       vmsgdata        VARCHAR2(200);
       l_order_tbl           oe_holds_pvt.order_tbl_type;
       i               NUMBER;
BEGIN

       l_order_tbl(1).header_id := P_HEADER_ID;
       l_order_tbl(1).line_id := P_LINE_ID;
     
--   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('534');

      apps.oe_holds_pub.Release_Holds (
      p_api_version        =>1.0,
      p_init_msg_list      =>apps.fnd_api.g_false,
      p_commit             =>apps.fnd_api.g_false,
      p_validation_level   =>apps.fnd_api.g_valid_level_full,
      p_order_tbl          =>l_order_tbl,
      p_hold_id            => 1,
      p_release_reason_code=> 'EXPIRE',
      p_release_comment    => 'TESTING'   ,
      x_return_status      => vreturnstatus,
      x_msg_count          => vmsgcount,
      x_msg_data           => vmsgdata);
   
      dbms_output.put_line (vreturnstatus);
   
        FOR j IN 1..OE_MSG_PUB.count_msg
            LOOP
                OE_MSG_PUB.get
                ( p_msg_index     => j
                , p_encoded       => 'F'
                , p_data          => vmsgdata
                , p_msg_index_out => i
                );
                dbms_output.put_line('Error: ' || j || ':' || vmsgdata);
            END LOOP;

END XX_RELEASE_HOLD_PRC ;


CREATE OR REPLACE PACKAGE BODY  XX_RELEASE_HOLD_PKG AS
PROCEDURE  XX_RELEASE_HOLD_PRC( P_HEADER_ID NUMBER , P_LINE_ID NUMBER) AS
       vreturnstatus   VARCHAR2(2);
       vmsgcount       NUMBER;
       vmsgdata        VARCHAR2(200);
       l_order_tbl           oe_holds_pvt.order_tbl_type;
       i               NUMBER;
BEGIN

       l_order_tbl(1).header_id := P_HEADER_ID;
       l_order_tbl(1).line_id := P_LINE_ID;
     
--   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('534');

      apps.oe_holds_pub.Release_Holds (
      p_api_version        =>1.0,
      p_init_msg_list      =>apps.fnd_api.g_false,
      p_commit             =>apps.fnd_api.g_false,
      p_validation_level   =>apps.fnd_api.g_valid_level_full,
      p_order_tbl          =>l_order_tbl,
      p_hold_id            => 1,
      p_release_reason_code=> 'EXPIRE',
      p_release_comment    => 'TESTING'   ,
      x_return_status      => vreturnstatus,
      x_msg_count          => vmsgcount,
      x_msg_data           => vmsgdata);
   
      dbms_output.put_line (vreturnstatus);
   
        FOR j IN 1..OE_MSG_PUB.count_msg
            LOOP
                OE_MSG_PUB.get
                ( p_msg_index     => j
                , p_encoded       => 'F'
                , p_data          => vmsgdata
                , p_msg_index_out => i
                );
                dbms_output.put_line('Error: ' || j || ':' || vmsgdata);
            END LOOP;

END XX_RELEASE_HOLD_PRC ;

FUNCTION xx_credit_limit_order_prc (p_cust_id NUMBER,P_header_id NUMBER)
  RETURN VARCHAR2
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
       v_api_return_status   VARCHAR2 (1);
       vholdid            NUMBER;
       vreturnstatus   VARCHAR2(200);
       vmsgcount       NUMBER;
       vmsgdata        VARCHAR2(2000);
       presultout      VARCHAR2(2000);
       preturnstatus   VARCHAR2(100);
       l_order_tbl           oe_holds_pvt.order_tbl_type;
       l_include_tax_flag   VARCHAR2(1) := 'Y';
       l_order_value     NUMBER;
       l_order_tax_value  NUMBER;
       l_credit_limit   NUMBER;
       l_total_amount   NUMBER;
        l_return       VARCHAR2 (1);
        l_hold_cnt     NUMBER;
        l_FLOW_STATUS_CODE VARCHAR2(40);
        l_order_total  NUMBER;
        l_last_update_date VARCHAR2(100);

   BEGIN
    l_return := 'N';


SELECT COUNT(*)
INTO l_hold_cnt
FROM oe_order_holds_all ooh,OE_HOLD_SOURCES_ALL ohs,oe_hold_definitions ohd
WHERE 1=1
AND ooh.HOLD_SOURCE_ID = ohs.HOLD_SOURCE_ID
AND ohs.hold_id = ohd.hold_id
AND ooh.header_id =p_header_id
AND ohd.name ='Credit Check Failure';
--and ooh.RELEASED_FLAG ='N';

/* select credit limit for the particular bill to customer id */
 SELECT NVL(MIN(hcp.OVERALL_CREDIT_LIMIT),0)
INTO l_credit_limit
FROM HZ_CUST_PROFILE_AMTS hcp
WHERE 1=1
AND hcp.OVERALL_CREDIT_LIMIT IS NOT NULL
AND hcp.CUST_ACCOUNT_ID =p_cust_id
AND hcp.SITE_USE_ID IN (SELECT ool.invoice_to_org_id
      FROM oe_order_lines_v ool
      WHERE 1=1
      AND ool.header_id =p_header_id);

/* select status for the particular order in booked status */
SELECT DISTINCT ooha.FLOW_STATUS_CODE
INTO l_FLOW_STATUS_CODE
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';

/* select last_update_date for the particular order in booked status */
SELECT DISTINCT TO_CHAR(ooha.last_update_date,'mmddyyyyHH24MI')
INTO l_last_update_date
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';

/* select amount due for the particular bill to customer id */
SELECT NVL(SUM(amount_due_remaining),0)
INTO l_total_amount
FROM ar_payment_schedules_all
WHERE 1=1
AND CLASS ='INV'
AND status='OP'
AND AMOUNT_DUE_REMAINING > 0
AND customer_id = p_cust_id;

/* select line total + tax for the particular bill to customer id where line is closed or awaiting shipping*/
SELECT NVL(SUM(NVL(oola.UNIT_SELLING_PRICE,0)*NVL(ORDERED_QUANTITY,0)+NVL(jst.tax_amount,0)),0)
INTO l_order_total
FROM oe_order_headers_all ooha,oe_order_lines_all oola,JA_IN_SO_TAX_LINES jst
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.org_id = oola.org_id
AND ooha.header_id = jst.header_id
AND oola.line_id = jst.line_id
AND ooha.header_id != p_header_id
AND ooha.SOLD_TO_ORG_ID =p_cust_id
AND TO_CHAR(ooha.order_number) NOT IN
( SELECT rct.INTERFACE_HEADER_ATTRIBUTE1
  FROM RA_CUSTOMER_TRX_ALL rct
  WHERE 1=1
  AND bill_to_customer_id =p_cust_id)
AND (oola.FLOW_STATUS_CODE ='AWAITING_SHIPPING' OR oola.FLOW_STATUS_CODE ='CLOSED');

/* select line total for the particular line */
    SELECT    SUM(NVL(unit_selling_price,0)
                    * NVL(ordered_quantity,0))
    INTO        l_order_value
    FROM        OE_ORDER_LINES_ALL
        WHERE    1=1
        AND HEADER_ID = p_header_id;

     /* select tax total for the particular line*/
        SELECT     SUM(NVL(tax_amount,0))
    INTO        l_order_tax_value
    FROM        JA_IN_SO_TAX_LINES
        WHERE    1=1
        AND HEADER_ID = p_header_id;


       l_order_value :=l_order_value+l_order_tax_value;

       l_total_amount :=l_total_amount+l_order_value+l_order_total;

/* If total amount is greater than credit limit then return Y*/
      IF l_total_amount >l_credit_limit + 1000 AND l_hold_cnt = 0 AND l_FLOW_STATUS_CODE ='BOOKED' --1000 Rs amount added by Vipul due to CR comes PF CSC_NOV_03.doc.
      AND l_last_update_date >= TO_CHAR(SYSDATE,'mmddyyyyHH24')||LPAD(SUBSTR(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI'),-2)-5,2,0)
       THEN

     l_return := 'Y';
    ELSE
     l_return := 'N';
END IF;

 RETURN l_return;
   EXCEPTION
      WHEN OTHERS THEN
    RETURN l_return;
   END;

END;




CREATE OR REPLACE PACKAGE XX_RELEASE_HOLD_PKG AS
FUNCTION xx_credit_limit_order (P_CUST_ID NUMBER,P_HEADER_ID NUMBER);
PROCEDURE  XX_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER);
END XX_RELEASE_HOLD_PKG;


FUNCTION xx_credit_limit_order (p_cust_id NUMBER,P_header_id NUMBER)
  RETURN VARCHAR2
   AS
      PRAGMA AUTONOMOUS_TRANSACTION;
       v_api_return_status   VARCHAR2 (1);
       vholdid            NUMBER;
       vreturnstatus   VARCHAR2(200);
       vmsgcount       NUMBER;
       vmsgdata        VARCHAR2(2000);
       presultout      VARCHAR2(2000);
       preturnstatus   VARCHAR2(100);
       l_order_tbl           oe_holds_pvt.order_tbl_type;
       l_include_tax_flag   VARCHAR2(1) := 'Y';
       l_order_value     NUMBER;
       l_order_tax_value  NUMBER;
       l_credit_limit   NUMBER;
       l_total_amount   NUMBER;
        l_return       VARCHAR2 (1);
        l_hold_cnt     NUMBER;
        l_FLOW_STATUS_CODE VARCHAR2(40);
        l_order_total  NUMBER;
        l_last_update_date VARCHAR2(100);

   BEGIN
    l_return := 'N';


SELECT COUNT(*)
INTO l_hold_cnt
FROM oe_order_holds_all ooh,OE_HOLD_SOURCES_ALL ohs,oe_hold_definitions ohd
WHERE 1=1
AND ooh.HOLD_SOURCE_ID = ohs.HOLD_SOURCE_ID
AND ohs.hold_id = ohd.hold_id
AND ooh.header_id =p_header_id
AND ohd.name ='Credit Check Failure';
--and ooh.RELEASED_FLAG ='N';

/* select credit limit for the particular bill to customer id */
 SELECT NVL(MIN(hcp.OVERALL_CREDIT_LIMIT),0)
INTO l_credit_limit
FROM HZ_CUST_PROFILE_AMTS hcp
WHERE 1=1
AND hcp.OVERALL_CREDIT_LIMIT IS NOT NULL
AND hcp.CUST_ACCOUNT_ID =p_cust_id
AND hcp.SITE_USE_ID IN (SELECT ool.invoice_to_org_id
      FROM oe_order_lines_v ool
      WHERE 1=1
      AND ool.header_id =p_header_id);

/* select status for the particular order in booked status */
SELECT DISTINCT ooha.FLOW_STATUS_CODE
INTO l_FLOW_STATUS_CODE
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';

/* select last_update_date for the particular order in booked status */
SELECT DISTINCT TO_CHAR(ooha.last_update_date,'mmddyyyyHH24MI')
INTO l_last_update_date
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';

/* select amount due for the particular bill to customer id */
SELECT NVL(SUM(amount_due_remaining),0)
INTO l_total_amount
FROM ar_payment_schedules_all
WHERE 1=1
AND CLASS ='INV'
AND status='OP'
AND AMOUNT_DUE_REMAINING > 0
AND customer_id = p_cust_id;

/* select line total + tax for the particular bill to customer id where line is closed or awaiting shipping*/
SELECT NVL(SUM(NVL(oola.UNIT_SELLING_PRICE,0)*NVL(ORDERED_QUANTITY,0)+NVL(jst.tax_amount,0)),0)
INTO l_order_total
FROM oe_order_headers_all ooha,oe_order_lines_all oola,JA_IN_SO_TAX_LINES jst
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.org_id = oola.org_id
AND ooha.header_id = jst.header_id
AND oola.line_id = jst.line_id
AND ooha.header_id != p_header_id
AND ooha.SOLD_TO_ORG_ID =p_cust_id
AND TO_CHAR(ooha.order_number) NOT IN
( SELECT rct.INTERFACE_HEADER_ATTRIBUTE1
  FROM RA_CUSTOMER_TRX_ALL rct
  WHERE 1=1
  AND bill_to_customer_id =p_cust_id)
AND (oola.FLOW_STATUS_CODE ='AWAITING_SHIPPING' OR oola.FLOW_STATUS_CODE ='CLOSED');

/* select line total for the particular line */
    SELECT    SUM(NVL(unit_selling_price,0)
                    * NVL(ordered_quantity,0))
    INTO        l_order_value
    FROM        OE_ORDER_LINES_ALL
        WHERE    1=1
        AND HEADER_ID = p_header_id;

     /* select tax total for the particular line*/
        SELECT     SUM(NVL(tax_amount,0))
    INTO        l_order_tax_value
    FROM        JA_IN_SO_TAX_LINES
        WHERE    1=1
        AND HEADER_ID = p_header_id;


       l_order_value :=l_order_value+l_order_tax_value;

       l_total_amount :=l_total_amount+l_order_value+l_order_total;

/* If total amount is greater than credit limit then return Y*/
      IF l_total_amount >l_credit_limit + 1000 AND l_hold_cnt = 0 AND l_FLOW_STATUS_CODE ='BOOKED' --1000 Rs amount added by Vipul due to CR comes PF CSC_NOV_03.doc.
      AND l_last_update_date >= TO_CHAR(SYSDATE,'mmddyyyyHH24')||LPAD(SUBSTR(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI'),-2)-5,2,0)
       THEN

     l_return := 'Y';
    ELSE
     l_return := 'N';
END IF;

 RETURN l_return;
   EXCEPTION
      WHEN OTHERS THEN
    RETURN l_return;
   END;
 
 
/
CREATE OR REPLACE PACKAGE XX_RELEASE_HOLD_PKG AS
FUNCTION xx_credit_limit_order_prc (P_CUST_ID NUMBER,P_HEADER_ID NUMBER) RETURN VARCHAR2;
PROCEDURE  XX_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER);
END XX_RELEASE_HOLD_PKG;
/