APPS.XXFTC_REC_RES_BCKFILL_PKG_copy_PROD_01132012

CREATE OR REPLACE PACKAGE APPS.XXFTC_REC_RES_BCKFILL_PKG IS
/*************************************************************************************************************************
 *                                                 - COPYRIGHT NOTICE -
 *
 *************************************************************************************************************************
 *******************************************************************************
 * Project   : FALKEN Tire project
 * Application       : Order Managment
 * Title          : This is a Package, which will create reservation -backorder fill for sales order lines.
 * Program Name       :
 * $Revision       : 1.0 $
 * Utility          : SQL*Plus
 * Created by       : Bhanu Vengana
 * Creation Date      : 02-06-2009
 * Called By             : N/A
 * Parameters            : N/A
 * Related documents  :
 * Tables/views accessed:
 *
 *
 * Change History  :
 *
 *===================================================================================================
 * Date       |Name     |Remarks
 *===================================================================================================
 *02-06-2009    |Bhanu Vengana                     |Initial Version (1.0)
 *****************************************************************************************************/
g_organization_id               ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_ID%TYPE:=NULL;
g_request_id                    NUMBER;
PROCEDURE XXFTC_REC_RES_BCKFILL_IMPORT(P_ERRBUF            OUT VARCHAR2
                                       ,P_REDCODE          OUT NUMBER
           ,P_ORGANIZATION_ID  IN NUMBER);
PROCEDURE XXFTC_BCKFILL_STG_LOAD;
PROCEDURE XXFTC_BCKFILL_RES_LOAD;
PROCEDURE XXFTC_BCKFILL_REPORT_CALL;
PROCEDURE XXFTC_REL_PARCEL_HOLD;
END XXFTC_REC_RES_BCKFILL_PKG;
/





---------------------------------------------------------------------------






CREATE OR REPLACE PACKAGE BODY APPS.XXFTC_REC_RES_BCKFILL_PKG IS
/*************************************************************************************************************************
 *                                                 - COPYRIGHT NOTICE -
 *
 *************************************************************************************************************************
 *******************************************************************************
 * Project            : FALKEN Tire project
 * Application                : Order Managment
 * Title                : This is a Package, which will create reservation -backorder fill for sales order lines.
 * Program Name                :
 * $Revision                : 1.0 $
 * Utility                : SQL*Plus
 * Created by                : Bhanu Vengana
 * Creation Date            : 02-06-2009
 * Called By                : N/A
 * Parameters               : N/A
 * Related documents        :
 * Tables/views accessed:
 *
 *
 * Change History        :
 *
 *===================================================================================================
 * Date             |Name                    |Remarks
 *===================================================================================================
 *02-06-2009    |Bhanu Vengana                        |Initial Version (1.0)
 *****************************************************************************************************/
PROCEDURE XXFTC_REC_RES_BCKFILL_IMPORT(P_ERRBUF            OUT VARCHAR2
                                       ,P_REDCODE          OUT NUMBER
                       ,P_ORGANIZATION_ID  IN NUMBER) IS
BEGIN
    g_organization_id:=P_ORGANIZATION_ID;
    XXFTC_BCKFILL_STG_LOAD;
    XXFTC_BCKFILL_RES_LOAD;
    XXFTC_BCKFILL_REPORT_CALL;
    XXFTC_REL_PARCEL_HOLD;
END XXFTC_REC_RES_BCKFILL_IMPORT;
--************************************************************************
--                  Procedure XXFTC_BCKFILL_STG_LOAD
--************************************************************************
PROCEDURE XXFTC_BCKFILL_STG_LOAD IS
v_bck_inventory_item_id                  oe_order_lines.inventory_item_id%TYPE;
v_bck_ship_from_org_id                   oe_order_lines.ship_from_org_id%TYPE;
v_bck_fill_trans_qty                     mtl_onhand_quantities.transaction_quantity%TYPE;
v_bck_fill_resv_qty                      mtl_reservations.reservation_quantity%TYPE;
v_bck_fill_qty                           mtl_onhand_quantities.transaction_quantity%TYPE:=NULL;
v_bck_segment1                           mtl_system_items_b.segment1%TYPE;
v_bck_sqlerrm                            VARCHAR2(4000):=NULL;
v_bck_sqlcode                            NUMBER:=NULL;
v_bck_m_request_id                       NUMBER:=fnd_global.conc_request_id;
v_bck_user_id                            NUMBER:=fnd_profile.value('USER_ID');
v_bck_login_id                           NUMBER:=fnd_profile.value('LOGIN_ID');
v_bck_current_date                       DATE:=SYSDATE;
--************************************************************************
--                  Cursor declaration
--************************************************************************
CURSOR cur_item_bckfill IS SELECT DISTINCT oola.inventory_item_id
                              ,oola.ship_from_org_id
                FROM oe_order_headers ooha
                     ,oe_order_lines oola
                     ,oe_order_types_v ootv
                     ,hz_cust_accounts hcaa
                     ,hz_parties hp
                     ,hz_cust_site_uses hcsua                 -- ADDED 26/06/09 (MD50 V7)
                    -- ,mtl_item_sub_inventories misi
                   --  ,mtl_secondary_inventories msi
                WHERE ooha.header_id=oola.header_id
                AND ooha.order_type_id=ootv.order_type_id
                AND ooha.sold_to_org_id=hcaa.cust_account_id
                AND hcaa.party_id=hp.party_id
                AND hcsua.site_use_id=ooha.ship_to_org_id         -- ADDED 26/06/09 (MD50 V7)
                --AND oola.inventory_item_id=misi.inventory_item_id
                --AND oola.ship_from_org_id=misi.organization_id
                --AND msi.secondary_inventory_name=misi.secondary_inventory   -- ADDED 17/06/09 (MD50 V6.1)
                           -- AND msi.organization_id=misi.organization_id              -- ADDED 17/06/09 (MD50 V6.1)
                            --AND msi.availability_type=1                              -- ADDED 17/06/09 (MD50 V6.1)
                            --AND msi.reservable_type=1
                --AND ooha.flow_status_code =DECODE(ootv.name,'Holding','ENTERED','BOOKED')  (Added 05/08/09)
                AND ooha.flow_status_code  IN ('ENTERED','BOOKED')
                AND oola.flow_status_code NOT IN ('CANCELLED','CLOSED')
                --AND ootv.name IN ('Holding','Standard Order')  --ADDED 17/08/09
                AND ootv.name NOT IN ('Return','Warranty Return') --ADDED 17/08/09
                AND (hcsua.attribute4='Y'                         -- ADDED 26/06/09 (MD50 V7)
                         OR NVL(hcsua.attribute4,'NULL')='NULL' AND hp.attribute9='Y')
                AND oola.ship_from_org_id IN
                     (SELECT organization_id       -- ADDED 17/06/09 (MD50 V6.1)
                      FROM org_organization_definitions
                      WHERE ORGANIZATION_CODE IN (SELECT flex_value
                                         FROM fnd_flex_values
                                  WHERE FLEX_VALUE_SET_ID=(SELECT flex_value_set_id
                                               FROM FND_FLEX_VALUE_SETS
                                               WHERE FLEX_VALUE_SET_NAME
                                               ='XXFTC_RR_WAREHOUSE_SELECTION')))
                               AND ootv.transaction_type_id IN (SELECT DISTINCT transaction_type_id
                                             FROM oe_transaction_types_all
                                             WHERE attribute3='N'
                                             AND order_category_code NOT IN ('RETURN'))  -- ADDED BVENGANA 25-10-09 (MD50 Excluding ReS from Order Types)
                               AND oola.ship_from_org_id=g_organization_id;
BEGIN
g_request_id:=v_bck_m_request_id;
--************************************************************************
--                  Open cursor
--************************************************************************
OPEN cur_item_bckfill;
LOOP
FETCH cur_item_bckfill INTO v_bck_inventory_item_id
                            ,v_bck_ship_from_org_id;
EXIT WHEN cur_item_bckfill%NOTFOUND;
--************************************************************************
--                 Getting Item number
--************************************************************************
          BEGIN
          SELECT  DISTINCT segment1 INTO v_bck_segment1
          FROM mtl_system_items_b
          WHERE inventory_item_id = v_bck_inventory_item_id
          AND organization_id=v_bck_ship_from_org_id;
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
            v_bck_segment1:=NULL;
            fnd_file.put_line(fnd_file.LOG,'Item id :'||v_bck_inventory_item_id||' not exist in data base');
          WHEN TOO_MANY_ROWS THEN
            v_bck_segment1:=NULL;
            fnd_file.put_line(fnd_file.LOG,'Item id :'||v_bck_inventory_item_id||' contain item number more then one in data base');
          WHEN OTHERS THEN
            v_bck_segment1:=NULL;
            v_bck_sqlerrm:=SQLERRM;
            v_bck_sqlcode:=SQLCODE;
            fnd_file.put_line(fnd_file.LOG,'Item id :'||v_bck_inventory_item_id||' contain error while getting item id: '||v_bck_sqlerrm);
          END;
--************************************************************************
--                 Getting Item Qty
--************************************************************************
          BEGIN
--************************************************************************
--                 Getting Transaction Item Qty
--************************************************************************
                  BEGIN
                 SELECT NVL(SUM(moq.transaction_quantity),0) INTO v_bck_fill_trans_qty
             FROM     mtl_onhand_quantities moq
                    ,mtl_secondary_inventories msi
             WHERE moq.subinventory_code=msi.secondary_inventory_name
             AND moq.organization_id=msi.organization_id
             AND moq.inventory_item_id=v_bck_inventory_item_id
             AND moq.organization_id=v_bck_ship_from_org_id
             AND msi.availability_type=1
             AND  msi.reservable_type=1;
                      EXCEPTION
              WHEN NO_DATA_FOUND THEN
                    v_bck_fill_trans_qty:=NULL;
                    fnd_file.put_line(fnd_file.LOG,'Item Number :'||v_bck_segment1||' Qty not exist in data base');
              WHEN TOO_MANY_ROWS THEN
                v_bck_fill_trans_qty:=NULL;
                fnd_file.put_line(fnd_file.LOG,'Item Number :'||v_bck_segment1||' contain qty more then one in data base');
              WHEN OTHERS THEN
                v_bck_fill_trans_qty:=NULL;
                v_bck_sqlerrm:=SQLERRM;
                v_bck_sqlcode:=SQLCODE;
                fnd_file.put_line(fnd_file.LOG,'Item Number :'||v_bck_segment1||' contain error while getting qty: '||v_bck_sqlerrm);
              END;
--************************************************************************
--                 Getting Reservation Item Qty
--************************************************************************
                       BEGIN
                 SELECT SUM(reservation_quantity) INTO v_bck_fill_resv_qty
             FROM     mtl_reservations
             WHERE inventory_item_id=v_bck_inventory_item_id
             AND organization_id=v_bck_ship_from_org_id;
                      EXCEPTION
              WHEN NO_DATA_FOUND THEN
                    v_bck_fill_resv_qty:=NULL;
                    fnd_file.put_line(fnd_file.LOG,'Item Number :'||v_bck_segment1||' Qty not exist in data base');
              WHEN TOO_MANY_ROWS THEN
                v_bck_fill_resv_qty:=NULL;
                fnd_file.put_line(fnd_file.LOG,'Item Number :'||v_bck_segment1||' contain qty more then one in data base');
              WHEN OTHERS THEN
                v_bck_fill_resv_qty:=NULL;
                v_bck_sqlerrm:=SQLERRM;
                v_bck_sqlcode:=SQLCODE;
                fnd_file.put_line(fnd_file.LOG,'Item Number :'||v_bck_segment1||' contain error while getting qty: '||v_bck_sqlerrm);
              END;
v_bck_fill_qty:=v_bck_fill_trans_qty-v_bck_fill_resv_qty;
          END;
--************************************************************************
--    Checking if qty is more then 0 insert into staging table else don't insert into staging table
--************************************************************************
         IF v_bck_fill_qty>0 THEN
--************************************************************************
--                 Inserting into staging table
--************************************************************************
     INSERT INTO  xxftc_rec_reserv_stg
         (item_number
          ,inventory_item_id
          ,organization_id
          ,warehouse
          ,item_qty
          ,request_id
          ,creation_date
          ,created_by
          ,last_update_date
          ,last_updated_by
          ,last_update_login
          )
          VALUES
          (v_bck_segment1
          ,v_bck_inventory_item_id
          ,v_bck_ship_from_org_id
          ,NULL
          ,v_bck_fill_qty
          ,v_bck_m_request_id
          ,v_bck_current_date
          ,v_bck_user_id
          ,v_bck_current_date
          ,v_bck_user_id
          ,v_bck_login_id
          );
         END IF;
END LOOP;
CLOSE cur_item_bckfill;
COMMIT;
END XXFTC_BCKFILL_STG_LOAD;
--************************************************************************
--                  Procedure XXFTC_BCKFILL_RES_LOAD
--************************************************************************
PROCEDURE XXFTC_BCKFILL_RES_LOAD IS
--************************************************************************
--                  Variable declaration
--************************************************************************
v_rrs_item_number             xxftc_rec_reserv_stg.item_number%TYPE;
v_rrs_inventory_item_id       xxftc_rec_reserv_stg.inventory_item_id%TYPE;
v_rrs_organization_id         xxftc_rec_reserv_stg.organization_id%TYPE;
v_rrs_warehouse               xxftc_rec_reserv_stg.warehouse%TYPE;
v_rrs_item_qty                xxftc_rec_reserv_stg.item_qty%TYPE;
v_rrs_rec_item_qty            xxftc_rec_reserv_stg.item_qty%TYPE;
v_rrs_request_id              xxftc_rec_reserv_stg.request_id%TYPE;
v_main_header_id              oe_order_headers.header_id%TYPE;
v_main_order_number           oe_order_headers.order_number%TYPE;
v_main_order_type_id          oe_order_headers.order_type_id%TYPE;
v_main_sold_to_org_id         oe_order_headers.sold_to_org_id%TYPE;
v_main_line_id                oe_order_lines.line_id%TYPE;
v_main_line_number            oe_order_lines.line_number%TYPE;
v_main_ship_from_org_id       oe_order_lines.ship_from_org_id%TYPE;
v_main_ordered_item           oe_order_lines.ordered_item%TYPE;
v_main_ordered_quantity       oe_order_lines.ordered_quantity%TYPE;
v_main_order_qty_uom          oe_order_lines.order_quantity_uom%TYPE;
v_main_total_qty              oe_order_lines.ordered_quantity%TYPE:=0;
v_main_reservation_qty        mtl_reservations.reservation_quantity%TYPE;
v_main_shipment_number        oe_order_lines.shipment_number%TYPE;
v_main_v_name                 oe_order_types_v.name%TYPE;
v_main_v_attribute3           hz_cust_site_uses_all.attribute3%TYPE;
v_main_v_attribute8           hz_parties.attribute8%TYPE;
v_main_v_ordered_date         oe_order_headers.ordered_date%TYPE;
v_main_reservation_id         mtl_reservations.reservation_id%TYPE;
v_main_demd_sor_hdr_id        mtl_sales_orders.sales_order_id%TYPE;
v_main_tra_sou_type_id        mtl_txn_source_types.transaction_source_type_id%TYPE;
v_main_tra_sou_type_name      mtl_txn_source_types.transaction_source_type_name%TYPE:='Sales order';
v_main_supply_tra_name        mtl_txn_source_types.transaction_source_type_name%TYPE:='Inventory';
v_main_supply_tra_id          mtl_txn_source_types.transaction_source_type_id%TYPE;
v_main_sqlerrm                VARCHAR2(4000):=NULL;
v_main_sqlcode                NUMBER:=NULL;
v_main_user_id                NUMBER:=fnd_profile.value('USER_ID');
v_main_login_id               NUMBER:=fnd_profile.value('LOGIN_ID');
v_main_current_date           DATE:=SYSDATE;
v_main_seq_num                NUMBER:=0;
v_main_cre_val                NUMBER;
v_main_update_val             NUMBER;
v_main_debug_point            VARCHAR2(50);
x_rowid                       VARCHAR2(4000);
x_reservation_id              NUMBER;
v_main_count_val              NUMBER:=NULL;
v_main_sour_code              VARCHAR2(10):='OE';
--************************************************************************
--                  Added latest
--************************************************************************
l_rsv_rec                     INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
l_serial_number               INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_api_version              NUMBER      := 1.0;
l_init_msg_list              VARCHAR2(2) := FND_API.G_TRUE;
l_partial_reservation_flag    VARCHAR2(2) := FND_API.G_TRUE;
l_force_reservation_flag      VARCHAR2(2) := FND_API.G_FALSE;
l_validation_flag             VARCHAR2(2) := FND_API.G_TRUE;
l_partial_reservation_exists  BOOLEAN     := FALSE;
l_return_status              VARCHAR2(2);
l_msg_count              NUMBER      := 0;
l_msg_data                    VARCHAR2(255);
l_quantity_reserved           NUMBER := 0;
l_reservation_id              NUMBER := 0;
l_rsv_upt_rec                INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
l_serial_upt_rec             INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_return_upt_status         VARCHAR2(2);
l_msg_upt_count             NUMBER      := 0;
l_msg_upt_data               VARCHAR2(255);
--************************************************************************
--                  Cursor declaration
--************************************************************************
CURSOR cur_main_load IS SELECT item_number
                 ,inventory_item_id
                 ,organization_id
                 ,warehouse
                 ,item_qty
                 ,item_qty
                 ,request_id
                        FROM  xxftc_rec_reserv_stg
            WHERE request_id=g_request_id
            ORDER BY item_number;
CURSOR cur_sales_order(p_inventory_item_id IN NUMBER,p_organization_id IN NUMBER) IS
                            SELECT DISTINCT ooha.header_id
                       ,ooha.order_number
                   ,ooha.order_type_id
                   ,ooha.sold_to_org_id
                   ,oola.line_id
                   ,oola.line_number
                   ,oola.ship_from_org_id
                   ,oola.ordered_item
                   ,oola.ordered_quantity
                   ,oola.order_quantity_uom
                   ,oola.shipment_number
                   ,ootv.name
                   ,hcsua.attribute3
                   ,hp.attribute8
                   ,ooha.ordered_date
            FROM oe_order_headers ooha
                 ,oe_order_lines oola
                 ,oe_order_types_v ootv
                 ,hz_cust_accounts hcaa
                 ,hz_parties hp
                 ,hz_cust_site_uses hcsua                 -- ADDED 26/06/09 (MD50 V7)
                 ,wsh_delivery_details wdd                    -- ADDED 17/06/09 (MD50 V1)
            WHERE ooha.header_id=oola.header_id
            AND ooha.order_type_id=ootv.order_type_id
            AND ooha.sold_to_org_id=hcaa.cust_account_id
            AND hcaa.party_id=hp.party_id
            AND hcsua.site_use_id=ooha.ship_to_org_id         -- ADDED 26/06/09 (MD50 V7)
            AND oola.line_id=wdd.source_line_id  (+)          -- ADDED 17/06/09 (MD50 V6.1)
                AND NVL(wdd.released_status,'#1') IN ('R','B',DECODE(ootv.name,'Holding','#1'),DECODE(ooha.flow_status_code,'ENTERED','#1'))  -- ADDED 17/06/09 (MD50 V6.1) -- (Added 05-08-09)
            AND oola.inventory_item_id=p_inventory_item_id
            --AND oola.flow_status_code not in ('ENTERED','CANCELLED','CLOSED')  (Changed MD50 V6)
            --AND ooha.flow_status_code ='BOOKED'                                (Changed MD50 V6)
            --AND ooha.flow_status_code =DECODE(ootv.name,'Holding','ENTERED','BOOKED') (Added 05-08-09)
            AND ooha.flow_status_code IN ('ENTERED','BOOKED')
            AND oola.flow_status_code NOT IN ('CANCELLED','CLOSED')
            AND oola.ship_from_org_id=p_organization_id
            --AND ootv.name IN ('Holding','Standard Order')  --ADDED 17/08/09
            AND ootv.name NOT IN ('Return','Warranty Return') --ADDED 17/08/09
            AND (hcsua.attribute4='Y'                         -- ADDED 26/06/09 (MD50 V7)
                 OR NVL(hcsua.attribute4,'NULL')='NULL' AND hp.attribute9='Y')
                        AND ootv.transaction_type_id IN (SELECT DISTINCT transaction_type_id
                                             FROM oe_transaction_types_all
                                             WHERE attribute3='N'
                                             AND order_category_code NOT IN ('RETURN'))  -- ADDED BVENGANA 25-10-09 (MD50 Excluding ReS from Order Types)
            ORDER BY DECODE(ootv.name,'Holding','A','B'),NVL(TO_NUMBER(hcsua.attribute3),TO_NUMBER(hp.attribute8)),ooha.ordered_date
            ;
BEGIN
v_main_debug_point:='1';
--************************************************************************
--                  Getting demand transaction_source_type_id
--************************************************************************
             BEGIN
           SELECT transaction_source_type_id INTO v_main_tra_sou_type_id
           FROM mtl_txn_source_types
           WHERE transaction_source_type_name=v_main_tra_sou_type_name;
              EXCEPTION
          WHEN NO_DATA_FOUND THEN
               v_main_tra_sou_type_id:=NULL;
                   fnd_file.put_line(fnd_file.LOG,'No data while getting  demad transaction_source_type_id');
              WHEN TOO_MANY_ROWS THEN
               v_main_tra_sou_type_id:=NULL;
             fnd_file.put_line(fnd_file.LOG,'Too many rows while getting demand transaction_source_type_id');
              WHEN OTHERS THEN
               v_main_tra_sou_type_id:=NULL;
               v_main_sqlerrm:=SQLERRM;
                   v_main_sqlcode:=SQLCODE;
             fnd_file.put_line(fnd_file.LOG,'Error while getting demand transaction_source_type_id: '||v_main_sqlerrm);
         END;
--************************************************************************
--                  Getting supply transaction_source_type_id
--************************************************************************
             BEGIN
           SELECT transaction_source_type_id INTO v_main_supply_tra_id
           FROM mtl_txn_source_types
           WHERE transaction_source_type_name=v_main_supply_tra_name;
              EXCEPTION
          WHEN NO_DATA_FOUND THEN
                v_main_supply_tra_id:=NULL;
                    fnd_file.put_line(fnd_file.LOG,'No data while getting supply transaction_source_type_id');
              WHEN TOO_MANY_ROWS THEN
                v_main_supply_tra_id:=NULL;
             fnd_file.put_line(fnd_file.LOG,'Too many rows while getting supply transaction_source_type_id');
              WHEN OTHERS THEN
               v_main_supply_tra_id:=NULL;
               v_main_sqlerrm:=SQLERRM;
                   v_main_sqlcode:=SQLCODE;
             fnd_file.put_line(fnd_file.LOG,'Error while getting supply transaction_source_type_id: '||v_main_sqlerrm);
         END;
--************************************************************************
--                  Open main cursor
--************************************************************************
OPEN cur_main_load;
LOOP
FETCH cur_main_load INTO  v_rrs_item_number
                  ,v_rrs_inventory_item_id
              ,v_rrs_organization_id
              ,v_rrs_warehouse
              ,v_rrs_item_qty
              ,v_rrs_rec_item_qty
              ,v_rrs_request_id;
EXIT WHEN cur_main_load%NOTFOUND;
--************************************************************************
--                  Open sales cursor
--************************************************************************
      OPEN cur_sales_order(v_rrs_inventory_item_id,v_rrs_organization_id);
      LOOP
      FETCH cur_sales_order INTO  v_main_header_id
                       ,v_main_order_number
                   ,v_main_order_type_id
                   ,v_main_sold_to_org_id
                   ,v_main_line_id
                   ,v_main_line_number
                   ,v_main_ship_from_org_id
                   ,v_main_ordered_item
                   ,v_main_ordered_quantity
                   ,v_main_order_qty_uom
                   ,v_main_shipment_number
                   ,v_main_v_name
                   ,v_main_v_attribute3
                   ,v_main_v_attribute8
                   ,v_main_v_ordered_date;
      EXIT WHEN cur_sales_order%NOTFOUND;
--************************************************************************
--                  Checking delivery lines for each sales order line
--************************************************************************
             BEGIN
            SELECT COUNT(*) INTO v_main_count_val
        FROM wsh_delivery_details
        WHERE source_line_id=v_main_line_id
        AND SOURCE_CODE=v_main_sour_code;
             EXCEPTION
         WHEN OTHERS THEN
         v_main_count_val:=NULL;
         v_main_sqlerrm:=SQLERRM;
             v_main_sqlcode:=SQLCODE;
             fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',contain error while getting delivery lines count: '||v_main_sqlerrm);
         END;
IF v_main_count_val < 2 THEN
--************************************************************************
--                  Getting demand_source_header_id
--************************************************************************
v_main_debug_point:='3';
             BEGIN
         SELECT DISTINCT sales_order_id INTO v_main_demd_sor_hdr_id
         FROM mtl_sales_orders
         WHERE segment1=v_main_order_number;
             EXCEPTION
          WHEN NO_DATA_FOUND THEN
                v_main_demd_sor_hdr_id:=NULL;
                    fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',contain no data while getting demand_source_header_id');
              WHEN TOO_MANY_ROWS THEN
                v_main_demd_sor_hdr_id:=NULL;
             fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',contain too many rows while getting demand_source_header_id');
              WHEN OTHERS THEN
               v_main_demd_sor_hdr_id:=NULL;
               v_main_sqlerrm:=SQLERRM;
                   v_main_sqlcode:=SQLCODE;
             fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',contain error while getting demand_source_header_id: '||v_main_sqlerrm);
         END;
--************************************************************************
--                  Checking already reservation exist or not  (Getting reservation id)
--************************************************************************
v_main_debug_point:='4';
              BEGIN
              SELECT DISTINCT reservation_id,NVL(reservation_quantity ,0)
                     INTO v_main_reservation_id,v_main_reservation_qty
          FROM mtl_reservations
          WHERE demand_source_line_id=v_main_line_id;
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
                      v_main_reservation_id:=0;
              WHEN TOO_MANY_ROWS THEN
             fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',contain too many rows while getting reservation information: ');
              WHEN OTHERS THEN
               v_main_sqlerrm:=SQLERRM;
                   v_main_sqlcode:=SQLCODE;
             fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',contain error while getting reservation information: '||v_main_sqlerrm);
              END;
--************************************************************************
--                 If reservation dose not exist creating new record
--************************************************************************
   IF  v_main_reservation_id =0 THEN
   x_reservation_id:=TO_NUMBER(NULL);
   IF v_rrs_item_qty!=0 THEN
--************************************************************************
--                 Validating qty
--************************************************************************
        IF     v_rrs_item_qty >=v_main_ordered_quantity  THEN
           v_rrs_item_qty:=(v_rrs_item_qty - v_main_ordered_quantity);
        ELSE
           v_main_ordered_quantity:=v_rrs_item_qty;
               v_rrs_item_qty :=0;
        END IF;
--************************************************************************
--                  Calling MTL_RESERVATIONS_PKG.INSERT_ROW API
--************************************************************************
v_main_debug_point:='5';
              BEGIN
          /*MTL_RESERVATIONS_PKG.INSERT_ROW( x_rowid                      =>     x_rowid
                      ,x_reservation_id                 =>     x_reservation_id
                      ,x_requirement_date               =>     v_main_current_date
                      ,x_organization_id                =>     v_rrs_organization_id
                      ,x_inventory_item_id              =>     v_rrs_inventory_item_id
                      ,x_demand_source_type_id          =>     v_main_tra_sou_type_id
                      ,x_demand_source_name             =>     NULL
                      ,x_demand_source_header_id        =>     v_main_demd_sor_hdr_id
                      ,x_demand_source_line_id          =>     v_main_line_id
                      ,x_demand_source_delivery         =>     NULL
                      ,x_primary_uom_code               =>     v_main_order_qty_uom
                      ,x_primary_uom_id                 =>     NULL
                      ,x_secondary_uom_code             =>     NULL
                      ,x_secondary_uom_id               =>     NULL
                      ,x_reservation_uom_code           =>     v_main_order_qty_uom
                      ,x_reservation_uom_id             =>     NULL
                      ,x_reservation_quantity           =>     v_main_ordered_quantity
                      ,x_primary_reservation_quantity   =>     v_main_ordered_quantity
                      ,x_second_reservation_quantity    =>     NULL
                      ,x_detailed_quantity              =>     NULL
                      ,x_secondary_detailed_quantity    =>     NULL
                      ,x_autodetail_group_id            =>     NULL
                      ,x_external_source_code           =>     NULL
                      ,x_external_source_line_id        =>     NULL
                      ,x_supply_source_type_id          =>     v_main_supply_tra_id
                      ,x_supply_source_header_id        =>     NULL
                      ,x_supply_source_line_id          =>     NULL
                      ,x_supply_source_line_detail      =>     NULL
                      ,x_supply_source_name             =>     NULL
                      ,x_revision                       =>     NULL
                      ,x_subinventory_code              =>     NULL
                      ,x_subinventory_id                =>     NULL
                      ,x_locator_id                     =>     NULL
                      ,x_lot_number                     =>     NULL
                      ,x_lot_number_id                  =>     NULL
                      ,x_serial_number                  =>     NULL
                      ,x_serial_number_id               =>     NULL
                      ,x_partial_quantities_allowed     =>     NULL
                      ,x_auto_detailed                  =>     NULL
                      ,x_pick_slip_number               =>     NULL
                      ,x_lpn_id                         =>     NULL
                      ,x_last_update_date               =>     v_main_current_date
                      ,x_last_updated_by                =>     v_main_user_id
                      ,x_creation_date                  =>     v_main_current_date
                      ,x_created_by                     =>     v_main_user_id
                      ,x_last_update_login              =>     v_main_login_id
                      ,x_request_id                     =>     NULL
                      ,x_program_application_id         =>     NULL
                      ,x_program_id                     =>     NULL
                      ,x_program_update_date            =>     NULL
                      ,x_attribute_category             =>     NULL
                      ,x_attribute1                     =>     NULL
                      ,x_attribute2                     =>     NULL
                      ,x_attribute3                     =>     NULL
                      ,x_attribute4                     =>     NULL
                      ,x_attribute5                     =>     NULL
                      ,x_attribute6                     =>     NULL
                      ,x_attribute7                     =>     NULL
                      ,x_attribute8                     =>     NULL
                      ,x_attribute9                     =>     NULL
                      ,x_attribute10                    =>     NULL
                      ,x_attribute11                    =>     NULL
                      ,x_attribute12                    =>     NULL
                      ,x_attribute13                    =>     NULL
                      ,x_attribute14                    =>     NULL
                      ,x_attribute15                    =>     NULL
                      ,x_ship_ready_flag                =>     NULL
                    );*/
--************************************************************************
--                  Added latest
--************************************************************************
      l_rsv_rec.organization_id              :=   v_rrs_organization_id;
      l_rsv_rec.inventory_item_id            :=   v_rrs_inventory_item_id;
      l_rsv_rec.requirement_date             :=   v_main_current_date;
      l_rsv_rec.demand_source_type_id          :=   INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE;
      l_rsv_rec.supply_source_type_id         :=   INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
      l_rsv_rec.primary_reservation_quantity  :=   v_main_ordered_quantity;
      l_rsv_rec.primary_uom_code              :=   v_main_order_qty_uom;
      l_rsv_rec.demand_source_header_id       :=   v_main_demd_sor_hdr_id;
      l_rsv_rec.demand_source_line_id         :=   v_main_line_id;
      l_rsv_rec.reservation_uom_code          :=   v_main_order_qty_uom;
      l_rsv_rec.reservation_quantity          :=   v_main_ordered_quantity;
      l_rsv_rec.demand_source_name            :=   NULL;
      l_rsv_rec.subinventory_code             :=   NULL;
      l_rsv_rec.supply_source_header_id       :=   NULL ;
      l_rsv_rec.supply_source_line_id         :=   NULL ;
      l_rsv_rec.supply_source_name            :=   NULL ;
      l_rsv_rec.supply_source_line_detail     :=   NULL ;
      l_rsv_rec.lot_number                    :=   NULL ;
      l_rsv_rec.serial_number                 :=   NULL ;
      l_rsv_rec.ship_ready_flag               :=   NULL ;
      l_rsv_rec.attribute15                   :=   NULL ;
      l_rsv_rec.attribute14                   :=   NULL ;
      l_rsv_rec.attribute13                   :=   NULL ;
      l_rsv_rec.attribute12                   :=   NULL ;
      l_rsv_rec.attribute11                   :=   NULL ;
      l_rsv_rec.attribute10                   :=   NULL ;
      l_rsv_rec.attribute9                    :=   NULL ;
      l_rsv_rec.attribute8                    :=   NULL ;
      l_rsv_rec.attribute7                    :=   NULL ;
      l_rsv_rec.attribute6                    :=   NULL ;
      l_rsv_rec.attribute5                    :=   NULL ;
      l_rsv_rec.attribute4                    :=   NULL ;
      l_rsv_rec.attribute3                    :=   NULL ;
      l_rsv_rec.attribute2                    :=   NULL ;
      l_rsv_rec.attribute1                    :=   NULL ;
      l_rsv_rec.attribute_category            :=   NULL ;
      l_rsv_rec.lpn_id                        :=   NULL ;
      l_rsv_rec.pick_slip_number              :=   NULL ;
      l_rsv_rec.lot_number_id                 :=   NULL ;
      l_rsv_rec.locator_id                    :=   NULL ;
      l_rsv_rec.subinventory_id               :=   NULL ;
      l_rsv_rec.revision                      :=   NULL ;
      l_rsv_rec.external_source_line_id       :=   NULL ;
      l_rsv_rec.external_source_code          :=   NULL ;
      l_rsv_rec.autodetail_group_id           :=   NULL ;
      l_rsv_rec.reservation_uom_id            :=   NULL ;
      l_rsv_rec.primary_uom_id                :=   NULL ;
      l_rsv_rec.demand_source_delivery        :=   NULL ;
INV_RESERVATION_PUB.CREATE_RESERVATION(
                  p_api_version_number     => l_api_version
                , p_init_msg_lst         => l_init_msg_list
                , p_rsv_rec             => l_rsv_rec
                , p_serial_number         => l_serial_number
                , p_partial_reservation_flag => l_partial_reservation_flag
                , p_force_reservation_flag     => l_force_reservation_flag
                , p_partial_rsv_exists     => l_partial_reservation_exists
                , p_validation_flag         => l_validation_flag
                , x_serial_number         => l_serial_number
                , x_return_status         => l_return_status
                , x_msg_count         => l_msg_count
                , x_msg_data         => l_msg_data
                , x_quantity_reserved     => l_quantity_reserved
                , x_reservation_id         => l_reservation_id);
IF l_msg_count > 1 THEN
        FOR I IN 1 .. l_msg_count
        LOOP
        fnd_file.put_line(fnd_file.LOG,'Create APT Error- Order Line Id:'||v_main_line_id||I || '. ' || SUBSTR(FND_MSG_PUB.GET(P_ENCODED => FND_API.G_FALSE), 1, 255) );
        END LOOP;
END IF;
             EXCEPTION
           WHEN OTHERS THEN
                   v_main_sqlerrm:=SQLERRM;
                   v_main_sqlcode:=SQLCODE;
             fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',Getting error while calling inser row api : '||
                 v_main_sqlcode||':'||v_main_sqlerrm);
             END;
--************************************************************************
--                 Checking record created in data base or not
--************************************************************************
         BEGIN
     SELECT DISTINCT 1
          INTO v_main_cre_val
          FROM mtl_reservations
          WHERE demand_source_line_id=v_main_line_id
          AND demand_source_header_id=v_main_demd_sor_hdr_id;
         EXCEPTION
     WHEN OTHERS THEN
          v_main_cre_val:=0;
     END;
--************************************************************************
--                 If record created in data base then inserting into custom table
--************************************************************************
IF  v_main_cre_val = 1 THEN
--************************************************************************
--                  Incresing seq num for each sales order line
--************************************************************************
v_main_seq_num:=v_main_seq_num+1;
--************************************************************************
--                  Incresing into report custom table
--************************************************************************
     BEGIN
         INSERT INTO xxftc_rec_reserv_rpt
           (item_number
        ,warehouse
        ,request_id
        ,seq_num
        ,tot_qty_received
        ,qty_received
        ,qty_reserved
        ,so_header_no
        ,so_line_no
        ,creation_date
        ,created_by
        ,last_update_date
        ,last_updated_by
        ,last_update_login)
        VALUES
        (v_rrs_item_number
                 ,v_rrs_warehouse
                 ,v_rrs_request_id
         ,v_main_seq_num
         ,v_rrs_rec_item_qty
         ,DECODE(v_rrs_item_qty,0,v_main_ordered_quantity,(v_rrs_item_qty+v_main_ordered_quantity))
         ,v_main_ordered_quantity
                 ,v_main_order_number
                 ,v_main_line_number||'.'||v_main_shipment_number
                 ,v_main_current_date
         ,v_main_user_id
         ,v_main_current_date
         ,v_main_user_id
         ,v_main_login_id
        );
         EXCEPTION
           WHEN OTHERS THEN
                   v_main_sqlerrm:=SQLERRM;
                   v_main_sqlcode:=SQLCODE;
             fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',Getting error while inserting into report table : '||
                 v_main_sqlcode||':'||v_main_sqlerrm);
     END;
--************************************************************************
--                 If record created not in data base then displying error in log file
--************************************************************************
ELSIF v_main_cre_val = 0 THEN
       v_rrs_item_qty :=v_rrs_item_qty+v_main_ordered_quantity;
       fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ', ERROR while creating reservation for this line using the reservation API.');
END IF;
      END IF;
      END IF;
--************************************************************************
--                 If reservation  exist updating old record
--************************************************************************
   IF  v_main_reservation_id <>0 AND v_main_ordered_quantity  <> v_main_reservation_qty  THEN
   IF v_rrs_item_qty!=0 THEN
--************************************************************************
--                 Validating qty
--************************************************************************
   IF v_main_ordered_quantity  > v_main_reservation_qty THEN
      IF v_rrs_item_qty >=(v_main_ordered_quantity-v_main_reservation_qty) THEN
         v_rrs_item_qty:=v_rrs_item_qty-(v_main_ordered_quantity-v_main_reservation_qty);
      ELSE
         v_main_ordered_quantity :=v_main_reservation_qty+v_rrs_item_qty;
     v_rrs_item_qty :=0;
      END IF;
--************************************************************************
--                  Calling MTL_RESERVATIONS_PKG.UPDATE_ROW API
--************************************************************************
v_main_debug_point:='6';
             BEGIN
             /*MTL_RESERVATIONS_PKG.UPDATE_ROW (
                       x_reservation_id                 =>     v_main_reservation_id
                      ,x_requirement_date               =>     v_main_current_date
                      ,x_organization_id                =>     v_rrs_organization_id
                      ,x_inventory_item_id              =>     v_rrs_inventory_item_id
                      ,x_demand_source_type_id          =>     v_main_tra_sou_type_id
                      ,x_demand_source_name             =>     NULL
                      ,x_demand_source_header_id        =>     v_main_demd_sor_hdr_id
                      ,x_demand_source_line_id          =>     v_main_line_id
                      ,x_demand_source_delivery         =>     NULL
                      ,x_primary_uom_code               =>     v_main_order_qty_uom
                      ,x_primary_uom_id                 =>     NULL
                      ,x_secondary_uom_code             =>     NULL
                      ,x_secondary_uom_id               =>     NULL
                      ,x_reservation_uom_code           =>     v_main_order_qty_uom
                      ,x_reservation_uom_id             =>     NULL
                      ,x_reservation_quantity           =>     v_main_ordered_quantity
                      ,x_primary_reservation_quantity   =>     v_main_ordered_quantity
                      ,x_second_reservation_quantity    =>     NULL
                      ,x_detailed_quantity              =>     NULL
                      ,x_secondary_detailed_quantity    =>     NULL
                      ,x_autodetail_group_id            =>     NULL
                      ,x_external_source_code           =>     NULL
                      ,x_external_source_line_id        =>     NULL
                      ,x_supply_source_type_id          =>     v_main_supply_tra_id
                      ,x_supply_source_header_id        =>     NULL
                      ,x_supply_source_line_id          =>     NULL
                      ,x_supply_source_line_detail      =>     NULL
                      ,x_supply_source_name             =>     NULL
                      ,x_revision                       =>     NULL
                      ,x_subinventory_code              =>     NULL
                      ,x_subinventory_id                =>     NULL
                      ,x_locator_id                     =>     NULL
                      ,x_lot_number                     =>     NULL
                      ,x_lot_number_id                  =>     NULL
                      ,x_serial_number                  =>     NULL
                      ,x_serial_number_id               =>     NULL
                      ,x_partial_quantities_allowed     =>     NULL
                      ,x_auto_detailed                  =>     NULL
                      ,x_pick_slip_number               =>     NULL
                      ,x_lpn_id                         =>     NULL
                      ,x_last_update_date               =>     v_main_current_date
                      ,x_last_updated_by                =>     v_main_user_id
                      ,x_last_update_login              =>     v_main_login_id
                      ,x_request_id                     =>     NULL
                      ,x_program_application_id         =>     NULL
                      ,x_program_id                     =>     NULL
                      ,x_program_update_date            =>     NULL
                      ,x_attribute_category             =>     NULL
                      ,x_attribute1                     =>     NULL
                      ,x_attribute2                     =>     NULL
                      ,x_attribute3                     =>     NULL
                      ,x_attribute4                     =>     NULL
                      ,x_attribute5                     =>     NULL
                      ,x_attribute6                     =>     NULL
                      ,x_attribute7                     =>     NULL
                      ,x_attribute8                     =>     NULL
                      ,x_attribute9                     =>     NULL
                      ,x_attribute10                    =>     NULL
                      ,x_attribute11                    =>     NULL
                      ,x_attribute12                    =>     NULL
                      ,x_attribute13                    =>     NULL
                      ,x_attribute14                    =>     NULL
                      ,x_attribute15                    =>     NULL
                      ,x_ship_ready_flag                =>     NULL
                           );*/
--************************************************************************
--                  Added latest
--************************************************************************
      l_rsv_upt_rec.organization_id          :=   v_rrs_organization_id;
      l_rsv_upt_rec.inventory_item_id                :=   v_rrs_inventory_item_id;
      l_rsv_upt_rec.requirement_date                 :=   v_main_current_date;
      l_rsv_upt_rec.demand_source_type_id      :=   INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE;
      l_rsv_upt_rec.supply_source_type_id         :=   INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
      l_rsv_upt_rec.primary_reservation_quantity  :=   v_main_ordered_quantity;
      l_rsv_upt_rec.primary_uom_code              :=   v_main_order_qty_uom;
      l_rsv_upt_rec.demand_source_header_id       :=   v_main_demd_sor_hdr_id ;
      l_rsv_upt_rec.demand_source_line_id         :=   v_main_line_id ;
      l_rsv_upt_rec.reservation_uom_code          :=   v_main_order_qty_uom ;
      l_rsv_upt_rec.reservation_quantity          :=   v_main_ordered_quantity ;
      l_rsv_upt_rec.reservation_id                :=   v_main_reservation_id;
      l_rsv_rec.supply_source_header_id           :=   NULL ;
      l_rsv_rec.supply_source_line_id             :=   NULL ;
      l_rsv_rec.supply_source_name                :=   NULL ;
      l_rsv_rec.supply_source_line_detail         :=   NULL ;
      l_rsv_rec.lot_number                        :=   NULL ;
      l_rsv_rec.serial_number                     :=   NULL ;
      l_rsv_rec.ship_ready_flag                   :=   NULL ;
      l_rsv_rec.attribute15              :=   NULL ;
      l_rsv_rec.attribute14              :=   NULL ;
      l_rsv_rec.attribute13              :=   NULL ;
      l_rsv_rec.attribute12              :=   NULL ;
      l_rsv_rec.attribute11              :=   NULL ;
      l_rsv_rec.attribute10              :=   NULL ;
      l_rsv_rec.attribute9              :=   NULL ;
      l_rsv_rec.attribute8              :=   NULL ;
      l_rsv_rec.attribute7              :=   NULL ;
      l_rsv_rec.attribute6              :=   NULL ;
      l_rsv_rec.attribute5              :=   NULL ;
      l_rsv_rec.attribute4              :=   NULL ;
      l_rsv_rec.attribute3              :=   NULL ;
      l_rsv_rec.attribute2              :=   NULL ;
      l_rsv_rec.attribute1              :=   NULL ;
      l_rsv_rec.attribute_category          :=   NULL ;
      l_rsv_rec.lpn_id                  :=   NULL ;
      l_rsv_rec.pick_slip_number          :=   NULL ;
      l_rsv_rec.lot_number_id              :=   NULL ;
      l_rsv_rec.locator_id              :=   NULL ;
      l_rsv_rec.subinventory_id              :=   NULL ;
      l_rsv_rec.revision              :=   NULL ;
      l_rsv_rec.external_source_line_id          :=   NULL ;
      l_rsv_rec.external_source_code          :=   NULL ;
      l_rsv_rec.autodetail_group_id          :=   NULL ;
      l_rsv_rec.reservation_uom_id          :=   NULL ;
      l_rsv_rec.primary_uom_id              :=   NULL ;
      l_rsv_rec.demand_source_delivery          :=   NULL ;
      inv_reservation_pub.update_reservation
                  (
                     p_api_version_number            =>  l_api_version
                   , p_init_msg_lst                  =>  fnd_api.g_false
                   , p_original_rsv_rec              =>  l_rsv_upt_rec
                   , p_to_rsv_rec                    =>  l_rsv_upt_rec
                   , p_original_serial_number        =>  l_serial_upt_rec
                   , p_to_serial_number              =>  l_serial_upt_rec
                   , p_validation_flag               =>  fnd_api.g_true    -- Added by Bvengana 14/04/10
                                   , p_check_availability            =>  fnd_api.g_true    -- Added by Bvengana 14/04/10
                                   , p_over_reservation_flag         =>  0                 -- Added by Bvengana 14/04/10
                   , x_return_status                 =>  l_return_upt_status
                   , x_msg_count                     =>  l_msg_upt_count
                   , x_msg_data                      =>  l_msg_upt_data
                   );
    IF l_msg_upt_count > 1 THEN
            FOR I IN 1 .. l_msg_upt_count
            LOOP
            fnd_file.put_line(fnd_file.LOG,'Update APT Error- Order Line Id:'||v_main_line_id||I || '. ' || SUBSTR(FND_MSG_PUB.GET(P_ENCODED => FND_API.G_FALSE), 1, 255) );
            END LOOP;
    END IF;
             EXCEPTION
           WHEN OTHERS THEN
                   v_main_sqlerrm:=SQLERRM;
                   v_main_sqlcode:=SQLCODE;
             fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ', ERROR while updating reservation for this line using the reservation API. '||
                 v_main_sqlcode||':'||v_main_sqlerrm);
             END;
--************************************************************************
--                 Checking record updated in data base or not
--************************************************************************
         BEGIN
     SELECT DISTINCT 1
          INTO v_main_update_val
          FROM mtl_reservations
          WHERE demand_source_line_id=v_main_line_id
          AND demand_source_header_id=v_main_demd_sor_hdr_id
          AND reservation_quantity=v_main_ordered_quantity;
         EXCEPTION
     WHEN OTHERS THEN
          v_main_update_val:=0;
     END;
IF  v_main_update_val = 1 THEN
--************************************************************************
--                  Incresing seq num for each sales order line
--************************************************************************
v_main_seq_num:=v_main_seq_num+1;
--************************************************************************
--                  Incresing into report custom table
--************************************************************************
     BEGIN
         INSERT INTO xxftc_rec_reserv_rpt
           (item_number
        ,warehouse
        ,request_id
        ,seq_num
        ,tot_qty_received
        ,qty_received
        ,qty_reserved
        ,so_header_no
        ,so_line_no
        ,creation_date
        ,created_by
        ,last_update_date
        ,last_updated_by
        ,last_update_login)
        VALUES
        (v_rrs_item_number
                 ,v_rrs_warehouse
                 ,v_rrs_request_id
         ,v_main_seq_num
         ,v_rrs_rec_item_qty
         ,DECODE(v_rrs_item_qty,0,(v_main_ordered_quantity-v_main_reservation_qty)
                 ,(v_rrs_item_qty+(v_main_ordered_quantity-v_main_reservation_qty)))
         ,(v_main_ordered_quantity-v_main_reservation_qty)
                 ,v_main_order_number
                 ,v_main_line_number||'.'||v_main_shipment_number
                 ,v_main_current_date
         ,v_main_user_id
         ,v_main_current_date
         ,v_main_user_id
         ,v_main_login_id
        );
         EXCEPTION
           WHEN OTHERS THEN
                   v_main_sqlerrm:=SQLERRM;
                   v_main_sqlcode:=SQLCODE;
             fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',Getting error while inserting into report table : '||
                 v_main_sqlcode||':'||v_main_sqlerrm);
     END;
     fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||','||v_rrs_item_qty);
--************************************************************************
--                 If record created not in data base then displying error in log file
--************************************************************************
ELSIF v_main_update_val = 0 THEN
       v_rrs_item_qty :=v_rrs_item_qty+v_main_reservation_qty;
       fnd_file.put_line(fnd_file.LOG,'Order number :'||v_main_order_number||
                             ',Line number :'||v_main_line_number||
                             ',Getting error while calling updating row api');
END IF;
    END IF;
    END IF;
    END IF;
v_main_debug_point:='7';
END IF;
    END LOOP;
    CLOSE cur_sales_order;
END LOOP;
CLOSE cur_main_load;
fnd_file.put_line(fnd_file.LOG,'API program debug point total is 7 :'||v_main_debug_point);
COMMIT;
END XXFTC_BCKFILL_RES_LOAD;
--************************************************************************
--                  Procedure xxftc_report_call
--************************************************************************
PROCEDURE xxftc_bckfill_report_call IS
--************************************************************************
--                  Variable declaration
--************************************************************************
v_rpt_request_id                     NUMBER;
v_rpt_sqlerrm                        VARCHAR2(4000):=NULL;
v_rpt_sqlcode                        NUMBER:=NULL;
v_rpt_result                         BOOLEAN;
v_rpt_phase                          VARCHAR2(200);
v_rpt_status                         VARCHAR2(200);
v_rpt_dev_phase                      VARCHAR2(200);
v_rpt_dev_status                     VARCHAR2(200);
v_rpt_error_mesg                     VARCHAR2(2000);
v_rpt_m_request_id                   NUMBER:=fnd_global.conc_request_id;
v_rpt_val                            NUMBER;
v_rpt1_request_id                    NUMBER;
v_rpt1_date                          VARCHAR2(20):=TO_CHAR(SYSDATE,'MMDDYY');
v_rpt1_instance_name                 VARCHAR2(20);
BEGIN
--************************************************************************
--                  Getting Instance Name
--************************************************************************
        BEGIN
        SELECT instance_name INTO v_rpt1_instance_name
    FROM v$instance;
    EXCEPTION
       WHEN OTHERS THEN
       v_rpt1_instance_name:=NULL;
    END;
--************************************************************************
--                  Getting count of records from xxftc_rec_reserv_rpt
--************************************************************************
        BEGIN
        SELECT COUNT(*) INTO v_rpt_val
        FROM xxftc_rec_reserv_rpt
        WHERE request_id=v_rpt_m_request_id;
    EXCEPTION
    WHEN OTHERS THEN
                v_rpt_val:=0;
    END;
    IF v_rpt_val > 0 THEN
        BEGIN
    v_rpt_request_id    := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'XXFTC'
                              ,PROGRAM     => 'XXFTCRESBCKFILLRPT'
                              ,DESCRIPTION => NULL
                              ,START_TIME  => SYSDATE
                              ,SUB_REQUEST => FALSE
                              ,ARGUMENT1  => v_rpt_m_request_id
                              );
    COMMIT;
    v_rpt_result := FND_CONCURRENT.WAIT_FOR_REQUEST(v_rpt_request_id
                            ,30
                            ,0
                            ,v_rpt_phase
                            ,v_rpt_status
                            ,v_rpt_dev_phase
                            ,v_rpt_dev_status
                            ,v_rpt_error_mesg);
    COMMIT;
        /*v_rpt1_request_id    := FND_REQUEST.SUBMIT_REQUEST(APPLICATION =>  'XXFTC'
                              ,PROGRAM     => 'XXFTCTEXTMAIL'
                          ,DESCRIPTION => NULL
                          ,START_TIME  => SYSDATE
                          ,SUB_REQUEST => FALSE
                          ,ARGUMENT1   => v_rpt_request_id
                          ,ARGUMENT2   => 'bvengana@osius.com,barnold@osius.com'
                              ,ARGUMENT3   => 'Inventory Sweep - Reserve (Backorder fill) '||v_rpt1_date||' - '||v_rpt1_instance_name
                                                  ,ARGUMENT4   => 'Inventory_Sweep_Reserve_Report_'||v_rpt1_date
                          );
    COMMIT;*/
    EXCEPTION
    WHEN OTHERS THEN
        v_rpt_sqlerrm:=SQLERRM;
        v_rpt_sqlcode:=SQLCODE;
        fnd_file.put_line(fnd_file.LOG,'Error while calling the reservation report : '
                                       ||v_rpt_sqlerrm);
    END;
    ELSE
    fnd_file.put_line(fnd_file.LOG,'**************************');
    fnd_file.put_line(fnd_file.LOG,'No reservations performed.');
    fnd_file.put_line(fnd_file.LOG,'**************************');
    END IF;
--************************************************************************
--                 Deleteing data from custom tables
--************************************************************************
        BEGIN
        DELETE FROM xxftc_rec_reserv_stg WHERE request_id=v_rpt_m_request_id;
    DELETE FROM xxftc_rec_reserv_rpt WHERE request_id=v_rpt_m_request_id;
    COMMIT;
    END;
END xxftc_bckfill_report_call;
--************************************************************************
--                  PROCEDURE XXFTC_REL_PARCEL_HOLD
--************************************************************************
PROCEDURE XXFTC_REL_PARCEL_HOLD IS
--************************************************************************
--                  Variable declaration
--************************************************************************
v_v1_rpt_request_id                     NUMBER;
v_v1_rpt_sqlerrm                        VARCHAR2(4000):=NULL;
v_v1_rpt_sqlcode                        NUMBER:=NULL;
v_v1_rpt_result                         BOOLEAN;
v_v1_rpt_phase                          VARCHAR2(200);
v_v1_rpt_status                         VARCHAR2(200);
v_v1_rpt_dev_phase                      VARCHAR2(200);
v_v1_rpt_dev_status                     VARCHAR2(200);
v_v1_rpt_error_mesg                     VARCHAR2(2000);
BEGIN
        BEGIN
    v_v1_rpt_request_id    := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'XXFTC'
                              ,PROGRAM     => 'XXFTCOMKEWHLD'
                              ,DESCRIPTION => NULL
                              ,START_TIME  => SYSDATE
                              ,SUB_REQUEST => FALSE
                              );
    COMMIT;
    /*v_v1_rpt_result := FND_CONCURRENT.WAIT_FOR_REQUEST(v_v1_rpt_request_id
                            ,30
                            ,0
                            ,v_v1_rpt_phase
                            ,v_v1_rpt_status
                            ,v_v1_rpt_dev_phase
                            ,v_v1_rpt_dev_status
                            ,v_v1_rpt_error_mesg);
        COMMIT;*/
    EXCEPTION
    WHEN OTHERS THEN
        v_v1_rpt_sqlerrm:=SQLERRM;
        v_v1_rpt_sqlcode:=SQLCODE;
        fnd_file.put_line(fnd_file.LOG,'Error while releaseing the Parcel hold : '
                                       ||v_v1_rpt_sqlerrm);
    END;
END XXFTC_REL_PARCEL_HOLD;
END XXFTC_REC_RES_BCKFILL_PKG;
/
===========================================================================