CREATE or REPPLACE PROCEDURE UpdateReservation

AS

        -- Common Declarations

        l_api_version       NUMBER      := 1.0;

        l_init_msg_list     VARCHAR2(2) := FND_API.G_TRUE;

        x_return_status     VARCHAR2(2);

        x_msg_count          NUMBER      := 0;

        x_msg_data          VARCHAR2(255);

        -- WHO columns

        l_user_id    NUMBER := -1;

        l_resp_id    NUMBER := -1;

        l_application_id  NUMBER := -1;

        l_row_cnt    NUMBER := 1;

        l_user_name    VARCHAR2(30) := 'MFG';

        l_resp_name    VARCHAR2(50) := 'Manufacturing and Distribution Manager';    

        -- API specific declarations

        l_rsv_rec                   INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;

        l_new_rsv_rec               INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;

        l_serial_number             INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;

        l_new_serial_number         INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;

        l_validation_flag           VARCHAR2(2) := FND_API.G_TRUE;  

        -- Load required serial numbers that are reserved

        CURSOR c_serials IS

        SELECT msn.inventory_item_id , msn.serial_number

        FROM mtl_system_items_b msi, mtl_serial_numbers msn, mtl_parameters mp

        WHERE msi.organization_id = mp.organization_id

        AND msi.organization_id = msn.current_organization_id

        AND msi.inventory_item_id = msn.inventory_item_id

        AND msn.group_mark_id IS NOT NULL

        AND msi.serial_number_control_code not in (1, 6)  -- item is not serial controlled / controlled at sales order issue

        AND msi.segment1 = 'SU_TEST_STS3'

        AND mp.organization_code = 'M1'

        AND msn.serial_number BETWEEN '' AND ''

        ORDER BY msn.serial_number DESC;

        -- Load reservation for this item

        CURSOR c_item_reservations IS

        SELECT msi.organization_id, msi.inventory_item_id, res.reservation_id, res.reservation_quantity, res.demand_source_name

        FROM mtl_system_items_b msi, mtl_parameters mp, mtl_reservations res

        WHERE msi.segment1 = 'SU_TEST_STS3'

        AND mp.organization_code = 'M1'

        AND msi.organization_id = mp.organization_id

        AND res.organization_id = msi.organization_id

        AND res.inventory_item_id = msi.inventory_item_id;

BEGIN

  -- Get the user_id

  SELECT user_id

  INTO l_user_id

  FROM fnd_user

  WHERE user_name = l_user_name;

  -- Get the application_id and responsibility_id

  SELECT application_id, responsibility_id

  INTO l_application_id, l_resp_id

  FROM fnd_responsibility_vl

  WHERE responsibility_name = l_resp_name;

  FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);

  dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );

        -- Get the first row

        FOR ir IN c_item_reservations LOOP

            l_rsv_rec.reservation_id       := ir.reservation_id;

            l_rsv_rec.demand_source_name   := ir.demand_source_name;

            l_rsv_rec.reservation_quantity := ir.reservation_quantity; 

            -- Update Demand Source Name, reservation qty for reservations that exist for this item

            l_new_rsv_rec.reservation_id       := ir.reservation_id;

            l_new_rsv_rec.demand_source_name   := ir.demand_source_name; --||'_0723';

            l_new_rsv_rec.reservation_quantity := 10;   -- new reservation quantity 

            BEGIN

                  -- Initialize Serials to be updated / reserved

                  FOR ser IN c_serials LOOP

                      l_serial_number(l_row_cnt).inventory_item_id := ser.inventory_item_id;

                      l_serial_number(l_row_cnt).serial_number     := ser.serial_number;

                      l_new_serial_number(l_row_cnt).inventory_item_id := ser.inventory_item_id;

                      l_new_serial_number(l_row_cnt).serial_number     := ser.serial_number + 10;

                      l_row_cnt := l_row_cnt + 1;

                  END LOOP;

             EXCEPTION

              WHEN NO_DATA_FOUND THEN

                dbms_output.put_line('Item not serial controlled / serials not provided');

             END; 

            -- call API to update all the reservations for this item

            DBMS_OUTPUT.PUT_LINE('=======================================================');

            DBMS_OUTPUT.PUT_LINE('Calling INV_RESERVATION_PUB.Update_Reservation');

            INV_RESERVATION_PUB.UPDATE_RESERVATION(

                    P_API_VERSION_NUMBER      =>   l_api_version

                  , P_INIT_MSG_LST        =>        l_init_msg_list

                  , X_RETURN_STATUS        =>   x_return_status

                  , X_MSG_COUNT          =>   x_msg_count

                  , X_MSG_DATA         =>   x_msg_data

                  , P_ORIGINAL_RSV_REC       =>   l_rsv_rec

                  , P_TO_RSV_REC       =>   l_new_rsv_rec

                  , P_ORIGINAL_SERIAL_NUMBER =>   l_serial_number

                  , P_TO_SERIAL_NUMBER       =>   l_new_serial_number

                  , P_VALIDATION_FLAG       =>   l_validation_flag

                  , P_CHECK_AVAILABILITY     =>   FND_API.G_FALSE

            );     

           DBMS_OUTPUT.PUT_LINE('=======================================================');

           DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);

           IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN

              DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);

           END IF;

           IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN

              DBMS_OUTPUT.PUT_LINE('Reservation ID           :'||l_new_rsv_rec.reservation_id);

              DBMS_OUTPUT.PUT_LINE('Demand Source Name (old) :'||l_rsv_rec.demand_source_name);

              DBMS_OUTPUT.PUT_LINE('Demand Source Name (new) :'||l_new_rsv_rec.demand_source_name);

              DBMS_OUTPUT.PUT_LINE('Reservation Qty  (old)   :'||l_rsv_rec.reservation_quantity);

              DBMS_OUTPUT.PUT_LINE('Reservation Qty  (new)   :'||l_new_rsv_rec.reservation_quantity);

          END IF;

          DBMS_OUTPUT.PUT_LINE('=======================================================');

      END LOOP;

EXCEPTION

        WHEN OTHERS THEN

          DBMS_OUTPUT.PUT_LINE('Exception Occured :');

          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);

          DBMS_OUTPUT.PUT_LINE('=======================================================');

 	END UpdateReservation;