Convert Date into Week Rage

Below is an example of how to display dates into week Range in Oracle
For E.g. 22-Nov-08 is in the date range 17-Nov-08 to 23-Nov-08

Firstly lets see how to get week of the year. Following query can be used to get this

 SELECT to_char(sysdate,'WW') FROM Dual; 

Now lets get the week range

SELECT    TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW') - 1) * 7), 'DD-MON-RR')
       || ' to '
       || TO_CHAR (TRUNC (SYSDATE, 'IYYY') + ((TO_CHAR (SYSDATE, 'WW')) * 7)-1, 'DD-MON-RR')
FROM   Dual;

The output of above query for date 29-Nov-2008 is 24-NOV-08 to 30-NOV-08

API to Create Sales Order(SO) Reservations

Below is the code that can be used to create SO reservations

DECLARE
   p_rsv                     inv_reservation_global.mtl_reservation_rec_type;
   p_dummy_sn                inv_reservation_global.serial_number_tbl_type;
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2(240);
   x_rsv_id                  NUMBER;
   x_dummy_sn                inv_reservation_global.serial_number_tbl_type;
   x_status                  VARCHAR2(1);
   x_qty                     NUMBER;
BEGIN
--   fnd_global.APPS_Initialize(28270,53073,660);
   dbms_application_info.set_client_info(5283);
   --p_user_id, p_resp_id,  p_resp_appl_id
   --p_rsv.reservation_id            := NULL; -- cannot know
   p_rsv.requirement_date            := Sysdate+2;
   p_rsv.organization_id             := 5343; --mtl_parameters.organization id
   p_rsv.inventory_item_id           := 949729;--mtl_system_items.Inventory_item_id;
   p_rsv.demand_source_type_id       := inv_reservation_global.g_source_type_oe; -- which is 2
   p_rsv.demand_source_name          := NULL;
   p_rsv.demand_source_header_id     := 1334166 ; --mtl_sales_orders.sales_order_id
   p_rsv.demand_source_line_id       := 4912468 ; -- oe_order_lines.line_id
   p_rsv.primary_uom_code            := 'EA';
   p_rsv.primary_uom_id              := NULL;
   p_rsv.reservation_uom_code        := 'EA';
   p_rsv.reservation_uom_id          := NULL;
   p_rsv.reservation_quantity        := 10;
   p_rsv.primary_reservation_quantity := 10;
   p_rsv.supply_source_type_id       := inv_reservation_global.g_source_type_inv;
   
   inv_reservation_pub.create_reservation
   (
        p_api_version_number       =>       1.0
      , x_return_status            =>       x_status
      , x_msg_count                =>       x_msg_count
      , x_msg_data                 =>       x_msg_data
      , p_rsv_rec                  =>       p_rsv
      , p_serial_number            =>       p_dummy_sn
      , x_serial_number            =>       x_dummy_sn
      , x_quantity_reserved        =>       x_qty
      , x_reservation_id           =>       x_rsv_id
   );
   dbms_output.put_line('Return status    = '||x_status);
   dbms_output.put_line('msg count        = '||to_char(x_msg_count));
   dbms_output.put_line('msg data         = '||x_msg_data);
   dbms_output.put_line('Quantity reserved = '||to_char(x_qty));
   dbms_output.put_line('Reservation id   = '||to_char(x_rsv_id));
   IF x_msg_count >=1 THEN
     FOR I IN 1..x_msg_count
     LOOP
       dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
       fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
     END LOOP;
     
   END IF;
COMMIT;
END;
/

Similary we have update_reservations, relieve_reservations, delete_reservations API to respectively update, relieve or delete reservations.