XXFTC_OM_ORDER_RELEASE

create or replace
PACKAGE      XXFTC_OM_ORDER_RELEASE AS

/************************************************************************************************/

/***     FILE NAME :           XXFTC_OM_ORDER_RELEASE.pks                                ***/

/***     AUTHOR         :           Prasad Potluri                                            ***/

/***     VERSION        :           1.0                                                       ***/

/***     PURPOSE        :           procedure to release hold for order                       ***/

/***                                                                  ***/

/***     PARAMETERS     :           order header id                                           ***/

/***     DATE           :           04/June/2009                                              ***/

/**************************************************************************************************/

 TYPE T_TAB IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;

  PROCEDURE XXFTC_OM_ORDER_RELEASE_PROC (

                            errbuf              OUT      VARCHAR2,
                            retcode             OUT      NUMBER,
                            p_customer_id       IN       NUMBER,
                            p_customer_ship_to  IN       NUMBER,
                            p_warehouse         IN       NUMBER,
                            p_report_only       IN       VARCHAR2,
                            p_order_id          IN        XXFTC_OM_ORDER_RELEASE.T_TAB,
                            p_control_number    IN       VARCHAR2,
                            p_reason_code       IN       VARCHAR2,
                p_invoice_notes     IN       VARCHAR2,
                p_request_id        OUT      NUMBER
                            );

PROCEDURE XXFTC_RELEASE_MODIFIER_PROC(p_tab IN XXFTC_OM_ORDER_RELEASE.T_TAB);

PROCEDURE XXFTC_REPRICE_ORDER_PROC(p_ord_tab IN XXFTC_OM_ORDER_RELEASE.T_TAB);

END XXFTC_OM_ORDER_RELEASE;
/



create or replace
PACKAGE BODY XXFTC_OM_ORDER_RELEASE AS
/************************************************************************************************/
/***     FILE NAME    :           XXFTC_OM_ORDER_RELEASE.pkb                                ***/
/***     AUTHOR         :           Prasad Potluri                                            ***/
/***     VERSION        :           1.0                                                       ***/
/***     PURPOSE        :           procedure to release hold for order                       ***/
/***                                                                              ***/
/***     PARAMETERS     :           order header id                                           ***/
/***     DATE           :           04/June/2009                                              ***/
/**************************************************************************************************/

--+----------------------------------------------------------+
--+ Function Used to get the weight for the open order line  +
--+----------------------------------------------------------+
   FUNCTION xxftc_open_orders_weight (p_line_id IN NUMBER)
      RETURN NUMBER
   IS
      v_open_orders_weight   NUMBER;
   BEGIN
      SELECT NVL (SUM (mtr.reservation_quantity * NVL (mtl.unit_weight, 0)),
                  0)
        INTO v_open_orders_weight
        FROM oe_order_lines oel, mtl_system_items_b mtl, mtl_reservations mtr
       WHERE oel.inventory_item_id = mtl.inventory_item_id
         AND oel.flow_status_code NOT IN
                ('CANCELLED',
                 'SHIPPED',
                 'CLOSED',
                 'AWAITING_RETURN',
                 'AWAITING_FULFILLMENT',
                 'AWAITING_RECEIPT'
                )
         AND mtl.organization_id = oel.ship_from_org_id
         AND mtl.organization_id = mtr.organization_id
         AND mtl.inventory_item_id = mtr.inventory_item_id
         AND mtr.demand_source_line_id = oel.line_id
         AND oel.line_id = p_line_id;

      RETURN v_open_orders_weight;
END xxftc_open_orders_weight;

PROCEDURE XXFTC_OM_ORDER_RELEASE_PROC (
               errbuf              OUT      VARCHAR2,
                           retcode             OUT      NUMBER,
               p_customer_id       IN       NUMBER,
               p_customer_ship_to  IN       NUMBER,
               p_warehouse         IN       NUMBER,
               p_report_only       IN       VARCHAR2,
               p_order_id          IN        XXFTC_OM_ORDER_RELEASE.T_TAB,
               p_control_number    IN       VARCHAR2,
               p_reason_code       IN       VARCHAR2,
               p_invoice_notes     IN       VARCHAR2,
               p_request_id        OUT      NUMBER
               ) IS

l_order_tbl              OE_HOLDS_PVT.order_tbl_type;
l_release_rec             OE_Hold_Sources_Pvt.Hold_Release_REC;
l_return_status          VARCHAR2(30);
l_msg_data               VARCHAR2(256);
l_msg_count              NUMBER;
l_msg_dummy              VARCHAR2(200);
l_output                 VARCHAR2(2000);
L_DEBUG_POINT            VARCHAR2(100);
l_error_message          VARCHAR2(2000);
l_oe_hold_id             NUMBER;
l_hold_id                NUMBER;
L_ORG                    NUMBER :=fnd_profile.value('org_id');
l_reason_code            VARCHAR2(100);
l_comment                VARCHAR2(100);
l_party_name             VARCHAR2(200);
l_customer_number        VARCHAR2(30);
l_ship_thresold        VARCHAR2(200);
l_update_user_name     NUMBER;
l_instance_name        VARCHAR2(100);
l_party_id             NUMBER;
l_truckload_limit      VARCHAR2(50);
l_ord_party_name       VARCHAR2(1000);
l_ord_customer_number  VARCHAR2(30);
l_customer_location    VARCHAR2(500);
l_warehouse_code       VARCHAR2(100);
L_TOTAL_WEIGHT         NUMBER;
L_ORDER_WEIGHT         NUMBER;
L_REQUEST_ID           NUMBER;
l_cust_id              NUMBER;
L_CONTROL_NUMBER       VARCHAR2(1000);
L_SHIP_TO_I            NUMBER;
l_ord_header_id        NUMBER;
l_ord_number           NUMBER;
l_report_type          VARCHAR2(50);
L_COUNTRY              VARCHAR2(50);
L_LINE_WEIGHT          NUMBER;
l_order_header_id      NUMBER;
L_RELEASED_BY          NUMBER;
l_released_by_name     VARCHAR2(50);
l_list_price           NUMBER;
l_selling_price        NUMBER;
l_diff                 NUMBER;
l_percentage           NUMBER;
l_request1             BOOLEAN;
L_SEND_EMAIL           VARCHAR2(200);
l_request_date           VARCHAR2(30);
v_template             VARCHAR2(30);
L_REQUEST_ID1          NUMBER;
l_crd_oe_hold_id       NUMBER;
l_crd_oe_hold_sts      VARCHAR2(100);
v_date                 VARCHAR2(20):=TO_CHAR(SYSDATE,'MMDDYY');
l_ORDERED_QUANTITY       NUMBER;
l_reservation_quantity   NUMBER;
l_shipping_instructions  VARCHAR2(1000);
l_tld_status             VARCHAR2(100);
l_cust_item_number       VARCHAR2(200);

 l_result                         BOOLEAN;
 l_phase                          VARCHAR2(200);
 l_status                         VARCHAR2(200);
 l_dev_phase                      VARCHAR2(200);
 l_dev_status                     VARCHAR2(200);
 l_error_mesg                     VARCHAR2(2000);
 l_item_number                    VARCHAR2(100);
 l_line_number                    VARCHAR2(50);

 l_l_user_name                    fnd_user.user_name%TYPE:=fnd_profile.value('USERNAME');
 l_l_cc_email                     VARCHAR2(240):=FND_PROFILE.VALUE('XXFTC_TLD_SUPERVISOR_EMAIL');

errm VARCHAR2(2000);




--+-------------------------------------------------------------------------------------------------------+
--+ Cursor used to get the eligible open order against customer number,ship to location and warehouse     +
--+-------------------------------------------------------------------------------------------------------+



CURSOR c_release_orders IS
SELECT DISTINCT oeh.header_id,oeh.order_number,oel.line_id,oel.line_number
  FROM oe_order_headers oeh,oe_order_lines oel
 WHERE oel.SOLD_TO_ORG_ID=p_customer_id
   AND oel.SHIP_TO_ORG_ID=p_customer_ship_to
   AND oel.SHIP_FROM_ORG_ID=p_warehouse
   AND oel.header_id=oeh.header_id
   AND oel.FLOW_STATUS_CODE NOT IN ('CANCELLED', 'SHIPPED','CLOSED','AWAITING_RETURN','AWAITING_FULFILLMENT', 'AWAITING_RECEIPT');


   CURSOR c_curr_ord(p_header_id IN NUMBER) IS
SELECT DISTINCT oel.line_id,oel.line_number
                    FROM oe_order_lines_all oel,
                         oe_order_headers_all oeh,
                         fnd_lookup_values fl,
                         wsh_carrier_services wc
                   WHERE oeh.header_id = oel.header_id
             AND oeh.header_id=p_header_id
                     AND oel.flow_status_code NOT IN
                            ('CANCELLED',
                             'SHIPPED',
                             'CLOSED',
                             'AWAITING_RETURN',
                             'AWAITING_FULFILLMENT',
                             'AWAITING_RECEIPT'
                            )
                     AND fl.lookup_type = 'FREIGHT_TERMS'
                     AND fl.lookup_code = oeh.freight_terms_code
                     AND wc.ship_method_code = oeh.shipping_method_code
                     AND fl.attribute1 || '-' || wc.attribute3 NOT IN
                                                        ('Y-Y', 'Y-N', 'N-Y');

 CURSOR c_total_wt
      IS
         SELECT DISTINCT oel.line_id
                    FROM oe_order_lines_all oel,
                         oe_order_headers_all oeh,
                         fnd_lookup_values fl,
                         wsh_carrier_services wc
                   WHERE oel.sold_to_org_id = p_customer_id
                     AND oel.ship_to_org_id = p_customer_ship_to
                     AND oel.ship_from_org_id = NVL(p_warehouse,oel.ship_from_org_id)
                     AND oeh.header_id = oel.header_id
                     AND oel.flow_status_code NOT IN
                            ('CANCELLED',
                             'SHIPPED',
                             'CLOSED',
                             'AWAITING_RETURN',
                             'AWAITING_FULFILLMENT',
                             'AWAITING_RECEIPT'
                            )
                     AND fl.lookup_type = 'FREIGHT_TERMS'
                     AND fl.lookup_code = oeh.freight_terms_code
                     AND wc.ship_method_code = oeh.shipping_method_code
                     AND fl.attribute1 || '-' || wc.attribute3 NOT IN
                                                        ('Y-Y', 'Y-N', 'N-Y');
CURSOR c_eligible_lines(p_header_id IN NUMBER) IS
SELECT DISTINCT oel.line_id,oel.line_number,oeh.header_id,oeh.order_number
                    FROM oe_order_lines_all oel,
                         oe_order_headers_all oeh,
                         fnd_lookup_values fl,
                         wsh_carrier_services wc
                   WHERE oel.header_id=oeh.header_id
                     AND oeh.header_id=p_header_id
                     AND oel.flow_status_code NOT IN
                            ('CANCELLED',
                             'SHIPPED',
                             'CLOSED',
                             'AWAITING_RETURN',
                             'AWAITING_FULFILLMENT',
                             'AWAITING_RECEIPT'
                            )
                     AND fl.lookup_type = 'FREIGHT_TERMS'
                     AND fl.lookup_code = oeh.freight_terms_code
                     AND wc.ship_method_code = oeh.shipping_method_code
                     AND fl.attribute1 || '-' || wc.attribute3 NOT IN
                                                        ('Y-Y', 'Y-N', 'N-Y');

BEGIN

       --+---------------------------+
       --+ Set the org id            +
       --+---------------------------+

     MO_GLOBAL.set_policy_context('S', L_ORG);



       BEGIN
        SELECT hl.country
      INTO l_country
      FROM hz_cust_site_uses_all hza,
           hz_cust_acct_sites_all hzc,
           hz_party_sites hzp,
           hz_locations hl
     WHERE hza.cust_acct_site_id=hzc.cust_acct_site_id
       AND hzc.party_site_id=hzp.party_site_id
       AND hzp.location_id=hl.location_id
       AND hza.site_use_id=p_customer_ship_to;
       EXCEPTION
                  WHEN NO_DATA_FOUND
                  THEN
                     l_country := '';
                     l_error_message :=
                           'l_release_hold_flag is null for an order header id'
                        || l_order_header_id;
                  WHEN OTHERS
                  THEN
                     l_error_message :=
                           'others error for order l_release_hold_flag'
                        || l_order_header_id
                        || SUBSTR (SQLERRM, 1, 100);
            END;

--IF l_country='US' THEN





                          BEGIN
                SELECT hp.party_name,hc.account_number
                  INTO l_party_name,l_customer_number
                  FROM hz_parties hp,hz_cust_accounts hc
                 WHERE hp.party_id=hc.party_id
                   AND hc.cust_account_id=p_customer_id;
                          EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                      l_party_name:='';



                                  WHEN OTHERS THEN

                                       l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);


                               END;

                   fnd_file.put_line(fnd_file.LOG,'Customer Name :'||l_party_name);

                   fnd_file.put_line(fnd_file.LOG,'');

                   fnd_file.put_line(fnd_file.LOG,'Customer Number :'||l_customer_number);

                   fnd_file.put_line(fnd_file.LOG,'');

          --+-----------------------------------------------------------------------------+
          --+ Get the party id                                                            +
          --+-----------------------------------------------------------------------------+
            BEGIN
               SELECT hp.party_id
                 INTO l_party_id
                 FROM hz_parties hp, hz_cust_accounts hc
                WHERE hc.cust_account_id = p_customer_id
                  AND hc.party_id = hp.party_id;
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  l_party_id := NULL;
                  l_error_message :=
                        'l_party_id is null for an order header id'
                     || l_order_header_id;
               WHEN OTHERS
               THEN
                  l_error_message :=
                        'others error for order l_party_id'
                     || l_order_header_id
                     || SUBSTR (SQLERRM, 1, 100);
            END;


          BEGIN
                  SELECT attribute6
                    INTO l_ship_thresold
                    FROM hz_cust_site_uses_all
                   WHERE site_use_id = p_customer_ship_to
             AND site_use_code='SHIP_TO';
               EXCEPTION
                  WHEN NO_DATA_FOUND
                  THEN
                     l_ship_thresold := '';
                     l_error_message :=
                           'l_release_hold_flag is null for an order header id'
                        || l_order_header_id;
                  WHEN OTHERS
                  THEN
                     l_error_message :=
                           'others error for order l_release_hold_flag'
                        || l_order_header_id
                        || SUBSTR (SQLERRM, 1, 100);
               END;

           IF l_ship_thresold IS NOT NULL  THEN

                  l_truckload_limit:=l_ship_thresold;

               ELSE



               BEGIN
                  SELECT attribute2
                    INTO l_truckload_limit
                    FROM hz_parties
                   WHERE party_id = l_party_id;
               EXCEPTION
                  WHEN NO_DATA_FOUND
                  THEN
                     l_truckload_limit := '';
                     l_error_message :=
                           'l_release_hold_flag is null for an order header id'
                        || l_order_header_id;
                  WHEN OTHERS
                  THEN
                     l_error_message :=
                           'others error for order l_release_hold_flag'
                        || l_order_header_id
                        || SUBSTR (SQLERRM, 1, 100);
               END;

           END IF;   -- IF l_ship_thresold IS NOT NULL  THEN



           l_total_weight:=0;

            FOR j IN 1..p_order_id.COUNT LOOP


              l_order_header_id:=p_order_id(j);


                  BEGIN
                        SELECT NVL (SUM (  mtr.reservation_quantity
                                         * NVL (mtl.unit_weight, 0)
                                        ),
                                    0
                                   )
                          INTO l_order_weight
                          FROM oe_order_lines_all oel,
                               mtl_system_items_b mtl,
                               mtl_reservations mtr,
                               oe_order_headers_all oeh
                         WHERE oel.inventory_item_id = mtl.inventory_item_id
                           AND oel.flow_status_code NOT IN
                                  ('CANCELLED',
                                   'SHIPPED',
                                   'CLOSED',
                                   'AWAITING_RETURN',
                                   'AWAITING_FULFILLMENT',
                                   'AWAITING_RECEIPT'
                                  )
                           AND mtl.organization_id = oel.ship_from_org_id
                           AND mtl.organization_id = mtr.organization_id
                           AND mtl.inventory_item_id = mtr.inventory_item_id
                           AND mtr.demand_source_line_id = oel.line_id
                           AND oel.header_id = oeh.header_id
                           AND oeh.header_id = l_order_header_id;
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_order_weight := 0;
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;

                l_total_weight:=l_total_weight+l_order_weight;


          END LOOP;









    IF p_report_only='N'  THEN





        FOR j IN 1..p_order_id.COUNT LOOP


              l_order_header_id:=p_order_id(j);


               --+-----------------------------------------------------+
               --+ get the credit check hold id for trailer load hold  +
               --+-----------------------------------------------------+

                            BEGIN
                SELECT order_hold_id
                  INTO l_crd_oe_hold_id
                  FROM oe_order_holds_all ohl,
                       oe_hold_sources_all osl,
                       oe_hold_definitions od
                 WHERE ohl.header_id = l_order_header_id
                   AND ohl.hold_source_id = osl.hold_source_id
                   AND osl.hold_id = od.hold_id
                   AND od.NAME = 'Credit Check Failure'
                   AND ohl.released_flag = 'N'
                   AND ROWNUM=1;
                             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                      l_crd_oe_hold_id:=NULL;

                      l_error_message:='l_oe_hold_id is null for an order header id';


                                  WHEN OTHERS THEN

                                       l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);


                               END;

                   IF l_crd_oe_hold_id IS NOT NULL THEN

                                  l_crd_oe_hold_sts:='On Credit Check Hold';

                               ELSE

                                  l_crd_oe_hold_sts:='No Credit Check Hold';

                               END IF;




              --+-----------------------------------------------------+
              --+ Open the cursor to get the orders to release hold   +
              --+-----------------------------------------------------+

              FOR r_eligible_lines IN c_eligible_lines(l_order_header_id)
              LOOP





     --+-----------------------------------------------------+
     --+ get the hold id for trailer load hold               +
     --+-----------------------------------------------------+

                            BEGIN
                SELECT order_hold_id
                  INTO l_oe_hold_id
                  FROM oe_order_holds_all ohl,
                       oe_hold_sources_all osl,
                       oe_hold_definitions od
                 WHERE ohl.header_id = r_eligible_lines.header_id
                   AND ohl.line_id=r_eligible_lines.line_id
                   AND ohl.hold_source_id = osl.hold_source_id
                   AND osl.hold_id = od.hold_id
                   AND od.NAME = 'Trailer Load Line Hold'
                   AND ohl.released_flag = 'N';
                             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                      l_oe_hold_id:=NULL;

                      l_error_message:='l_oe_hold_id is null for an order header id';


                                  WHEN OTHERS THEN

                                       l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);


                               END;

                    --+-----------------------------------------------------+
                                --+ Check trailer load hold exist or not for order      +
                                --+-----------------------------------------------------+


          IF l_oe_hold_id IS NOT NULL THEN

          IF l_crd_oe_hold_id IS NOT NULL THEN

          l_tld_status:='ON TLD Hold';

          ELSE

          l_tld_status:='Released TLD Hold';

          END IF;




           IF p_invoice_notes IS NOT NULL THEN

            UPDATE oe_order_headers_all
         SET attribute1=p_invoice_notes
         WHERE header_id=r_eligible_lines.header_id;

         COMMIT;
            END IF;




        IF p_control_number IS NOT NULL THEN

            UPDATE oe_order_headers_all
         SET attribute7=p_control_number
         WHERE header_id=r_eligible_lines.header_id;

         COMMIT;
            END IF;

        IF p_reason_code IS NOT NULL THEN

            UPDATE oe_order_headers_all
         SET attribute8=p_reason_code
         WHERE header_id=r_eligible_lines.header_id;

         COMMIT;
            END IF;



          --+-----------------------------------------------------------------------------+
                  --+ Open the loop to get the eligible order lines                               +
                  --+-----------------------------------------------------------------------------+







              BEGIN
            SELECT hp.party_name, hc.account_number,
                               hu.LOCATION, org.organization_code,oel.unit_list_price,oel.unit_selling_price
                          INTO l_party_name, l_customer_number,
                               l_customer_location, l_warehouse_code,l_list_price,l_selling_price
                          FROM hz_parties hp,
                               hz_cust_accounts hc,
                               oe_order_lines oel,
                               hz_cust_acct_sites hzc,
                               hz_cust_site_uses hu,
                               org_organization_definitions org
                         WHERE hc.party_id = hp.party_id
                           AND hc.cust_account_id = oel.sold_to_org_id
                           AND oel.line_id = r_eligible_lines.line_id
                           AND hc.cust_account_id = hzc.cust_account_id
                           AND hzc.cust_acct_site_id = hu.cust_acct_site_id
                           AND hu.site_use_id = oel.ship_to_org_id
                           AND hu.site_use_code = 'SHIP_TO'
                           AND org.organization_id = oel.ship_from_org_id;
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_party_name := '';
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;

                     BEGIN
                        SELECT NVL (SUM (mtr.reservation_quantity * NVL (mtl.unit_weight, 0)),
                          0)
            INTO l_order_weight
            FROM oe_order_lines_all oel, mtl_system_items_b mtl, mtl_reservations mtr
               WHERE oel.inventory_item_id = mtl.inventory_item_id
             AND oel.flow_status_code NOT IN
                ('CANCELLED',
                 'SHIPPED',
                 'CLOSED',
                 'AWAITING_RETURN',
                 'AWAITING_FULFILLMENT',
                 'AWAITING_RECEIPT'
                )
             AND mtl.organization_id = oel.ship_from_org_id
             AND mtl.organization_id = mtr.organization_id
             AND mtl.inventory_item_id = mtr.inventory_item_id
             AND mtr.demand_source_line_id = oel.line_id
             AND oel.line_id = r_eligible_lines.line_id;
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_order_weight := 0;
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;





             BEGIN
                        SELECT attribute7,header_id,order_number,shipping_instructions
                          INTO l_control_number,l_ord_header_id,l_ord_number,l_shipping_instructions
                          FROM oe_order_headers_all
                         WHERE header_id=r_eligible_lines.header_id;
                EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_control_number := '';
               l_shipping_instructions:='';
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;



                        BEGIN
                           SELECT NVL(mtr.reservation_quantity,0)
                            INTO l_reservation_quantity
                             FROM oe_order_lines_all oel,
                                  mtl_system_items_b mtl,
                                  mtl_reservations mtr
                            WHERE oel.inventory_item_id = mtl.inventory_item_id
                  AND mtl.organization_id = oel.ship_from_org_id
                              AND mtl.organization_id = mtr.organization_id
                              AND mtl.inventory_item_id = mtr.inventory_item_id
                              AND mtr.demand_source_line_id = oel.line_id
                              AND oel.line_id = r_eligible_lines.line_id;
                         EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                                 l_reservation_quantity:=0;
                             -- l_ord_user_id := NULL;
                              l_error_message :=
                                    'l_ORDERED_QUANTITY is null for an order header id'
                                 || l_order_header_id;
                           WHEN OTHERS
                           THEN
                              l_error_message :=
                                    'others error for order l_party_id'
                                 || l_order_header_id
                                 || SUBSTR (SQLERRM, 1, 100);
                        END;

                      BEGIN
                        SELECT NVL(ORDERED_QUANTITY,0)
                          INTO  l_ORDERED_QUANTITY
                          FROM oe_order_lines_all
                          WHERE line_id= r_eligible_lines.line_id;
                      EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                                 l_ORDERED_QUANTITY:=0;
                             -- l_ord_user_id := NULL;
                              l_error_message :=
                                    'l_ORDERED_QUANTITY is null for an order header id'
                                 || l_order_header_id;
                           WHEN OTHERS
                           THEN
                              l_error_message :=
                                    'others error for order l_party_id'
                                 || l_order_header_id
                                 || SUBSTR (SQLERRM, 1, 100);
                        END;









             BEGIN
                        SELECT mtl.segment1,oel.line_number||'.'||oel.shipment_number
              INTO l_item_number,l_line_number
                          FROM oe_order_lines_all oel,mtl_system_items_b mtl
                         WHERE oel.inventory_item_id = mtl.inventory_item_id
               AND mtl.organization_id = oel.ship_from_org_id
               AND oel.line_id=r_eligible_lines.line_id;
                EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_item_number := '';
                           l_error_message :=
                                 'l_item_number is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_item_number'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;


             l_released_by:=fnd_profile.value('user_id');


                 BEGIN
             SELECT user_name
               INTO l_released_by_name
               FROM fnd_user
               WHERE user_id=l_released_by;
                  EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_released_by_name := '';
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;


             BEGIN
                        SELECT mci.customer_item_number
              INTO l_cust_item_number
              FROM oe_order_lines_all oel,
                   mtl_customer_item_xrefs_v mci,
                   mtl_system_items_b mtl
             WHERE mci.customer_id=oel.sold_to_org_id
               AND oel.line_id=r_eligible_lines.line_id
               AND mtl.organization_id = oel.ship_from_org_id
               AND mtl.segment1=mci.concatenated_segments
               AND mtl.inventory_item_id=oel.inventory_item_id;
             EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_cust_item_number :='';
                           l_error_message :=
                                 'l_cust_item_number is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;




             l_diff:=l_list_price-l_selling_price;


                 l_percentage:=l_diff/l_list_price*100;

               --+-----------------------------------------------------------------------------+
               --+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert        +
               --+-----------------------------------------------------------------------------+
                     BEGIN
                        INSERT INTO xxftc_order_header_hold_tbl
                             VALUES (p_customer_id,
                                     TO_CHAR(r_eligible_lines.order_number),l_line_number,
                                     r_eligible_lines.header_id, l_party_name,
                                     l_customer_number, l_customer_location,
                                     l_truckload_limit, l_order_weight,
                                     l_total_weight, l_warehouse_code,l_list_price,l_selling_price,l_diff,ROUND(l_percentage,2),l_tld_status,l_released_by_name,'',l_control_number,l_crd_oe_hold_sts,l_item_number,
                     l_ORDERED_QUANTITY,
                     l_reservation_quantity,
                     l_shipping_instructions,
                     l_cust_item_number);
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for inserting into xxftc_order_header_hold_tbl'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;

               --+-----------------------------------------------------+
                               --+ Get the trailer load hold id                        +
                               --+-----------------------------------------------------+




                    BEGIN
                      SELECT hold_id
                                    INTO l_hold_id
                                    FROM OE_HOLD_DEFINITIONS
                                   WHERE NAME = 'Trailer Load Line Hold';
                                 EXCEPTION
                         WHEN OTHERS THEN
                             l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);


                             END;

                 BEGIN
                       SELECT LOOKUP_CODE,MEANING
                     INTO l_reason_code,l_comment
                     FROM fnd_lookup_values
                    WHERE LOOKUP_TYPE='RELEASE_REASON'
                      AND MEANING='TLD Threshold Reached';
                               EXCEPTION
                        WHEN OTHERS THEN
                              l_error_message:='others error for l_reason_code,l_comment'||SUBSTR(SQLERRM,1,100);


                            END;

                                IF l_crd_oe_hold_id IS NULL THEN


                                     l_order_tbl(1).line_id := r_eligible_lines.line_id;--p_line_id;
                     l_order_tbl(1).header_id := r_eligible_lines.header_id;--p_header_id;


                   l_debug_point:='010';

                   --+-----------------------------------------------------------------+
                               --+ Call the OE_Holds_PUB.Release_Holds to release hold for order   +
                               --+-----------------------------------------------------------------+



                   Oe_Holds_Pub.release_holds(
                    p_order_tbl => l_order_tbl,
                    p_hold_id => l_hold_id,
                    p_release_reason_code => l_reason_code,
                    p_release_comment => l_comment,
                    x_return_status => l_return_status,
                    x_msg_count => l_msg_count,
                    x_msg_data => l_msg_data
                               );

                         dbms_output.put_line(l_return_status);

                       IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
                         l_error_message:='error for order header id while calling holds API'||l_msg_data;




                       END IF;   -- IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN

               END IF;  --IF l_crd_oe_hold_id IS NULL THEN




               fnd_file.put_line(fnd_file.LOG,'Order Number :');

                   fnd_file.put_line(fnd_file.LOG,'');

               BEGIN
                      UPDATE oe_order_headers
                       SET attribute10=''
                     WHERE header_id=r_eligible_lines.header_id;
                         EXCEPTION
                  WHEN OTHERS THEN
                               l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);
                          END;


               COMMIT;
                       END IF;  -- IF l_oe_hold_id IS NOT NULL THEN
                 END LOOP;  --FOR r_eligible_lines IN c_eligible_lines
        END LOOP;     -- FOR j IN 1..p_order_id.COUNT LOOP

  ELSIF p_report_only='Y' THEN







            FOR j IN 1..p_order_id.COUNT LOOP



               l_order_header_id:=p_order_id(j);


             --+-----------------------------------------------------+
               --+ get the credit check hold id for trailer load hold  +
               --+-----------------------------------------------------+

                            BEGIN
                SELECT order_hold_id
                  INTO l_crd_oe_hold_id
                  FROM oe_order_holds_all ohl,
                       oe_hold_sources_all osl,
                       oe_hold_definitions od
                 WHERE ohl.header_id = l_order_header_id
                   AND ohl.hold_source_id = osl.hold_source_id
                   AND osl.hold_id = od.hold_id
                   AND od.NAME = 'Credit Check Failure'
                   AND ohl.released_flag = 'N'
                   AND ROWNUM=1;
                             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                      l_crd_oe_hold_id:=NULL;

                      l_error_message:='l_oe_hold_id is null for an order header id';


                                  WHEN OTHERS THEN

                                       l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);


                               END;

                   IF l_crd_oe_hold_id IS NOT NULL THEN

                                  l_crd_oe_hold_sts:='On Credit Check Hold';

                               ELSE

                                  l_crd_oe_hold_sts:='No Credit Check Hold';

                               END IF;




              --+-----------------------------------------------------+
              --+ Open the cursor to get the orders to release hold   +
              --+-----------------------------------------------------+

              FOR r_eligible_lines IN c_eligible_lines(l_order_header_id)
              LOOP





     --+-----------------------------------------------------+
     --+ get the hold id for trailer load hold               +
     --+-----------------------------------------------------+

                            BEGIN
                SELECT order_hold_id
                  INTO l_oe_hold_id
                  FROM oe_order_holds_all ohl,
                       oe_hold_sources_all osl,
                       oe_hold_definitions od
                 WHERE ohl.header_id = r_eligible_lines.header_id
                   AND ohl.line_id=r_eligible_lines.line_id
                   AND ohl.hold_source_id = osl.hold_source_id
                   AND osl.hold_id = od.hold_id
                   AND od.NAME = 'Trailer Load Line Hold'
                   AND ohl.released_flag = 'N';
                             EXCEPTION
                     WHEN NO_DATA_FOUND THEN
                      l_oe_hold_id:=NULL;

                      l_error_message:='l_oe_hold_id is null for an order header id';


                                  WHEN OTHERS THEN

                                       l_error_message:='others error for order header id'||SUBSTR(SQLERRM,1,100);


                               END;

                    --+-----------------------------------------------------+
                                --+ Check trailer load hold exist or not for order      +
                                --+-----------------------------------------------------+


          IF l_oe_hold_id IS NOT NULL THEN




       /* IF p_control_number IS NOT NULL THEN

            UPDATE oe_order_headers_all
         SET attribute7=p_control_number
         WHERE header_id=r_eligible_lines.header_id;

         commit;
            END IF;

        IF p_reason_code IS NOT NULL THEN

            UPDATE oe_order_headers_all
         SET attribute8=p_reason_code
         WHERE header_id=r_eligible_lines.header_id;

         commit;
            END IF;*/



          --+-----------------------------------------------------------------------------+
                  --+ Open the loop to get the eligible order lines                               +
                  --+-----------------------------------------------------------------------------+







              BEGIN
            SELECT hp.party_name, hc.account_number,
                               hu.LOCATION, org.organization_code,oel.unit_list_price,oel.unit_selling_price
                          INTO l_party_name, l_customer_number,
                               l_customer_location, l_warehouse_code,l_list_price,l_selling_price
                          FROM hz_parties hp,
                               hz_cust_accounts hc,
                               oe_order_lines oel,
                               hz_cust_acct_sites hzc,
                               hz_cust_site_uses hu,
                               org_organization_definitions org
                         WHERE hc.party_id = hp.party_id
                           AND hc.cust_account_id = oel.sold_to_org_id
                           AND oel.line_id = r_eligible_lines.line_id
                           AND hc.cust_account_id = hzc.cust_account_id
                           AND hzc.cust_acct_site_id = hu.cust_acct_site_id
                           AND hu.site_use_id = oel.ship_to_org_id
                           AND hu.site_use_code = 'SHIP_TO'
                           AND org.organization_id = oel.ship_from_org_id;
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_party_name := '';
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;

                     BEGIN
                        SELECT NVL (SUM (mtr.reservation_quantity * NVL (mtl.unit_weight, 0)),
                          0)
            INTO l_order_weight
            FROM oe_order_lines oel, mtl_system_items_b mtl, mtl_reservations mtr
               WHERE oel.inventory_item_id = mtl.inventory_item_id
             AND oel.flow_status_code NOT IN
                ('CANCELLED',
                 'SHIPPED',
                 'CLOSED',
                 'AWAITING_RETURN',
                 'AWAITING_FULFILLMENT',
                 'AWAITING_RECEIPT'
                )
             AND mtl.organization_id = oel.ship_from_org_id
             AND mtl.organization_id = mtr.organization_id
             AND mtl.inventory_item_id = mtr.inventory_item_id
             AND mtr.demand_source_line_id = oel.line_id
             AND oel.line_id = r_eligible_lines.line_id;
                     EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_order_weight := 0;
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;





             BEGIN
                        SELECT attribute7,header_id,order_number,shipping_instructions
                          INTO l_control_number,l_ord_header_id,l_ord_number,l_shipping_instructions
                          FROM oe_order_headers
                         WHERE header_id=r_eligible_lines.header_id;
                EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_update_user_name := '';
               l_shipping_instructions:='';
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;





                        BEGIN
                           SELECT NVL(mtr.reservation_quantity,0)
                            INTO l_reservation_quantity
                             FROM oe_order_lines_all oel,
                                  mtl_system_items_b mtl,
                                  mtl_reservations mtr
                            WHERE oel.inventory_item_id = mtl.inventory_item_id
                  AND mtl.organization_id = oel.ship_from_org_id
                              AND mtl.organization_id = mtr.organization_id
                              AND mtl.inventory_item_id = mtr.inventory_item_id
                              AND mtr.demand_source_line_id = oel.line_id
                              AND oel.line_id = r_eligible_lines.line_id;
                         EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                                 l_reservation_quantity:=0;
                             -- l_ord_user_id := NULL;
                              l_error_message :=
                                    'l_ORDERED_QUANTITY is null for an order header id'
                                 || l_order_header_id;
                           WHEN OTHERS
                           THEN
                              l_error_message :=
                                    'others error for order l_party_id'
                                 || l_order_header_id
                                 || SUBSTR (SQLERRM, 1, 100);
                        END;

                      BEGIN
                        SELECT NVL(ORDERED_QUANTITY,0)
                          INTO  l_ORDERED_QUANTITY
                          FROM oe_order_lines_all
                          WHERE line_id= r_eligible_lines.line_id;
                      EXCEPTION
                           WHEN NO_DATA_FOUND
                           THEN
                                 l_ORDERED_QUANTITY:=0;
                              --l_ord_user_id := NULL;
                              l_error_message :=
                                    'l_ORDERED_QUANTITY is null for an order header id'
                                 || l_order_header_id;
                           WHEN OTHERS
                           THEN
                              l_error_message :=
                                    'others error for order l_party_id'
                                 || l_order_header_id
                                 || SUBSTR (SQLERRM, 1, 100);
                        END;





             BEGIN
                        SELECT mtl.segment1,oel.line_number||'.'||oel.shipment_number
              INTO l_item_number,l_line_number
                          FROM oe_order_lines_all oel,mtl_system_items_b mtl
                         WHERE oel.inventory_item_id = mtl.inventory_item_id
               AND mtl.organization_id = oel.ship_from_org_id
               AND oel.line_id=r_eligible_lines.line_id;
                EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_item_number := '';
                           l_error_message :=
                                 'l_item_number is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_item_number'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;



             l_released_by:=fnd_profile.value('user_id');

              BEGIN
             SELECT user_name
               INTO l_released_by_name
               FROM fnd_user
               WHERE user_id=l_released_by;
                  EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_released_by_name := '';
                           l_error_message :=
                                 'l_party_id is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;


             BEGIN
                        SELECT mci.customer_item_number
              INTO l_cust_item_number
              FROM oe_order_lines_all oel,
                   mtl_customer_item_xrefs_v mci,
                   mtl_system_items_b mtl
             WHERE mci.customer_id=oel.sold_to_org_id
               AND oel.line_id=r_eligible_lines.line_id
               AND mtl.organization_id = oel.ship_from_org_id
               AND mtl.segment1=mci.concatenated_segments
               AND mtl.inventory_item_id=oel.inventory_item_id;
             EXCEPTION
                        WHEN NO_DATA_FOUND
                        THEN
                           l_cust_item_number :='';
                           l_error_message :=
                                 'l_cust_item_number is null for an order header id'
                              || l_order_header_id;
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for order l_party_id'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;





             l_diff:=l_list_price-l_selling_price;

             l_percentage:=l_diff/l_list_price*100;

             l_released_by_name:='';



               --+-----------------------------------------------------------------------------+
               --+ Insert all orders in to xxftc_order_header_hold_tbl to send an alert        +
               --+-----------------------------------------------------------------------------+
                     BEGIN
                        INSERT INTO xxftc_order_header_hold_tbl
                             VALUES (p_customer_id,
                                     TO_CHAR(r_eligible_lines.order_number),l_line_number,
                                     r_eligible_lines.header_id, l_party_name,
                                     l_customer_number, l_customer_location,
                                     l_truckload_limit, l_order_weight,
                                     l_total_weight, l_warehouse_code,l_list_price,l_selling_price,l_diff,ROUND(l_percentage,2),'On TLD Hold',l_released_by_name,'',l_control_number,l_crd_oe_hold_sts,l_item_number,
                     l_ORDERED_QUANTITY,
                     l_reservation_quantity,
                     l_shipping_instructions,
                     l_cust_item_number);
                     EXCEPTION
                        WHEN OTHERS
                        THEN
                           l_error_message :=
                                 'others error for inserting into xxftc_order_header_hold_tbl'
                              || l_order_header_id
                              || SUBSTR (SQLERRM, 1, 100);
                     END;

              END IF;  -- IF l_oe_hold_id IS NOT NULL THEN
                 END LOOP;  --FOR r_eligible_lines IN c_eligible_lines
            END LOOP;     -- FOR j IN 1..p_order_id.COUNT LOOP







         END IF;  --IF p_report_only='Y' THEN



                        BEGIN
                   SELECT instance_name
                 INTO l_instance_name
                 FROM v$instance
                 WHERE ROWNUM=1;
                 EXCEPTION
                           WHEN OTHERS
                           THEN
                              l_error_message :=
                                    'others error for order header id'
                                 || l_order_header_id
                                 || SUBSTR (SQLERRM, 1, 100);
                           END;




                           --l_send_email:='mmanier@falkentire.com';   -- Commented By Bvengana
               BEGIN
               SELECT email_address INTO l_send_email
               FROM  fnd_user
               WHERE user_name=l_l_user_name;
                           EXCEPTION
               WHEN OTHERS THEN
                            l_send_email:=NULL;
               END;






       --+------------------------------------------------------------+
               --+submit the request to get the mail content                  +
               --+------------------------------------------------------------+
             BEGIN
                  l_report_type:='RELEASE';
          v_template:='XXFTCALRTMAIL';



                   l_request1 := fnd_request.add_layout (template_appl_name => 'XXFTC',
            template_code => v_template,
            template_language => 'en',
             template_territory => 'US',
            output_format => 'EXCEL');



     l_request_id :=
                  fnd_request.submit_request (application      => 'XXFTC',
                                              program          => 'XXFTCALRTMAIL',
                                              description      => NULL,
                                              start_time       => SYSDATE,
                                              sub_request      => FALSE,
                                              argument1        => p_customer_id,
                          argument2        => l_report_type
                                             );

           COMMIT;


               p_request_id:=l_request_id;




            IF p_report_only='N' THEN

         l_result := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request_id
                            ,30
                            ,0
                            ,l_phase
                            ,l_status
                            ,l_dev_phase
                            ,l_dev_status
                            ,l_error_mesg);
            COMMIT;


       --+------------------------------------------------------------+
       --+submit the request to send the mail through shell program   +
       --+------------------------------------------------------------+


         /* SELECT to_char(sysdate+3/60/24,'DD-MON-YYYY HH24:MI:SS') INTO l_request_date FROM dual;*/




              l_request_id1 := FND_REQUEST.SUBMIT_REQUEST(APPLICATION =>  'XXFTC'
                              ,PROGRAM     => 'XXFTCXMLMAIL'
                          ,DESCRIPTION => NULL
                          ,START_TIME  => SYSDATE
                          ,SUB_REQUEST => FALSE
                          ,ARGUMENT1   => l_request_id
                          ,ARGUMENT2   => l_send_email||','||l_l_cc_email
                              ,ARGUMENT3   => 'TLD Hold Release Notification'||'-'||l_instance_name
                          ,ARGUMENT4   => 'RELEASE_TLD_HOLD'||v_date
                          );

           END IF;
       END;






--END IF;   --IF l_country='US' THEN

EXCEPTION
WHEN OTHERS THEN
     l_error_message:='Other error in XXFTC_OM_ORDER_RELEASE_PROC'||SUBSTR(SQLERRM,1,50);


END XXFTC_OM_ORDER_RELEASE_PROC;

PROCEDURE XXFTC_RELEASE_MODIFIER_PROC(p_tab IN XXFTC_OM_ORDER_RELEASE.T_TAB)
IS

CURSOR c_price(p_order_header_id NUMBER)
  IS
    SELECT DISTINCT ooh.header_id headerid, ooh.cust_po_number custponum,ool.calculate_price_flag,ool.line_id
    FROM   oe_order_headers_all ooh
          ,oe_order_lines_all   ool
    WHERE  ooh.header_id        = ool.header_id
    AND    ool.calculate_price_flag='Y'
    AND    ooh.header_id     = p_order_header_id;


v_header_count NUMBER;
v_header_list VARCHAR2(32000);
v_line_count NUMBER;
v_line_List VARCHAR2(32000);
v_price_level VARCHAR2(32000);
v_return_status VARCHAR2(32000);
v_msg_count NUMBER;
v_msg_data VARCHAR2(32000);
l_org NUMBER     := fnd_profile.VALUE ('org_id');
l_user_id NUMBER:=fnd_profile.VALUE ('user_id');
l_order_header_id NUMBER;
v_count NUMBER:=0;
l_resp_id NUMBER;
l_appl_id NUMBER;
BEGIN

        mo_global.set_policy_context('S',l_org);



 FOR i IN 1..p_tab.COUNT LOOP
 l_order_header_id:=p_tab(i);
FOR l_price IN c_price(l_order_header_id)
LOOP
    BEGIN
       UPDATE oe_order_lines_all
       SET    attribute20='N'
       WHERE  header_id=l_price.headerid
       AND line_id=l_price.line_id;

     COMMIT;

    END;


END LOOP;

SELECT COUNT(*) INTO v_count
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID=l_order_header_id
AND calculate_price_flag='Y';

v_header_count := 1;
v_header_list := TO_CHAR(l_order_header_id);
v_price_level :='ORDER';


 -- CALL TO PROCESS ORDER Check the return status and then commit.
 IF v_count >0 THEN



 OE_ORDER_ADJ_PVT.price_action( p_header_count => v_header_count,
                                p_header_list => v_header_list,
                    p_line_count => v_line_count,
                    p_line_list => v_line_list,
                p_price_level => v_price_level,
                x_return_status => v_return_status,
                x_msg_count => v_msg_count,
                x_msg_data => v_msg_data );

 IF v_return_status <> 'S'
         THEN
   NULL;
 END IF;


 COMMIT;



END IF;
END LOOP;


END XXFTC_RELEASE_MODIFIER_PROC;

PROCEDURE XXFTC_REPRICE_ORDER_PROC(p_ord_tab IN XXFTC_OM_ORDER_RELEASE.T_TAB) IS

v_header_count NUMBER;
v_header_list VARCHAR2(32000);
v_line_count NUMBER;
v_line_List VARCHAR2(32000);
v_price_level VARCHAR2(32000);
v_return_status VARCHAR2(32000);
v_msg_count NUMBER;
v_msg_data VARCHAR2(32000);
l_org NUMBER     := fnd_profile.VALUE ('org_id');
l_user_id NUMBER:=fnd_profile.VALUE ('user_id');
l_order_header_id NUMBER;
v_count NUMBER:=0;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_ord_header_id NUMBER;

BEGIN

mo_global.set_policy_context('S',l_org);

FOR i IN 1..p_ord_tab.COUNT LOOP
 l_ord_header_id:=p_ord_tab(i);

v_header_count := 1;
v_header_list := TO_CHAR(l_ord_header_id);
v_price_level :='ORDER';

OE_ORDER_ADJ_PVT.price_action( p_header_count => v_header_count,
                                p_header_list => v_header_list,
                    p_line_count => v_line_count,
                    p_line_list => v_line_list,
                p_price_level => v_price_level,
                x_return_status => v_return_status,
                x_msg_count => v_msg_count,
                x_msg_data => v_msg_data );

 IF v_return_status <> 'S'
         THEN
   NULL;
 END IF;


 COMMIT;

END LOOP;


END XXFTC_REPRICE_ORDER_PROC;






END XXFTC_OM_ORDER_RELEASE;