eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OE_RESERVE_CONC SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 58


       SELECT primary_uom_code
       INTO   l_primary_uom
       FROM   mtl_system_items_b
       WHERE inventory_item_id = p_inventory_item_id
       AND    organization_id = NVL(p_ship_from_org_id,
                       OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID',p_org_id)); --4759251

Line: 67


             OE_DEBUG_PUB.Add('Error in selecting Primary UOM code',1);

Line: 200


  SELECT inventory_item_id,
         ordered_qty,
         ordered_qty2, --INVCONV
         ship_from_org_id,
         subinventory,
         corrected_qty,
         corrected_qty2 -- INVCONV
  FROM oe_rsv_set_details
  WHERE reservation_set_id = p_reservation_set_id
  AND   line_id = p_line_id;

Line: 258


         OE_MSG_PUB.update_msg_context(
              p_entity_code                => 'LINE'
             ,p_entity_id                  => p_x_rsv_tbl(I).line_id
             ,p_header_id                  => p_x_rsv_tbl(I).header_id
             ,p_line_id                    => p_x_rsv_tbl(I).line_id
             ,p_orig_sys_document_ref      => p_x_rsv_tbl(I).orig_sys_document_ref
             ,p_orig_sys_document_line_ref => p_x_rsv_tbl(I).orig_sys_line_ref
             ,p_change_sequence            => p_x_rsv_tbl(I).change_sequence
             ,p_source_document_id         => p_x_rsv_tbl(I).source_document_id
             ,p_source_document_line_id    => p_x_rsv_tbl(I).source_document_line_id
             ,p_order_source_id            => p_x_rsv_tbl(I).order_source_id
             ,p_source_document_type_id    => p_x_rsv_tbl(I).source_document_type_id
             );

Line: 346


Procedure Name : Update_Reservation_Set
Description    : To update the process_flag of the processed set
--------------------------------------------------------------------- */
Procedure Update_Reservation_Set
(p_reservation_set_id IN NUMBER,
 x_return_status OUT NOCOPY VARCHAR2)
IS
  l_request_id NUMBER;

Line: 359


      oe_debug_pub.add(  'ENTERING UPDATE_RESERVATION_SET ' , 1 ) ;

Line: 363


   UPDATE oe_reservation_sets
   SET process_flag = 'Y',
       reservation_request_id = l_request_id,
       program_update_date = sysdate,
       last_update_login = FND_GLOBAL.CONC_LOGIN_ID,
       last_updated_by = FND_GLOBAL.USER_ID,
       last_update_date = sysdate
   WHERE reservation_set_id = p_reservation_set_id;

Line: 377


      oe_debug_pub.add(  'EXITING UPDATE_RESERVATION_SET ' , 1 ) ;

Line: 383


        OE_DEBUG_PUB.Add('In others error...in Proc Update_Reservation_Set for rsv set tbl',1);

Line: 387


END Update_Reservation_Set;

Line: 392


Description    : Inserts simulated or reserved records
                 into oe_reservation_sets and oe_rsv_set_details table.
--------------------------------------------------------------------- */
Procedure Create_Reservation_Set(p_rsv_tbl IN OE_RESERVE_CONC.Rsv_Tbl_Type,
                                 p_reserve_set_name       IN VARCHAR2,
                                 p_rsv_request_id         IN NUMBER DEFAULT NULL,
                                 p_simulation_request_id  IN NUMBER DEFAULT NULL,
                                 x_return_status OUT NOCOPY VARCHAR2)
IS
  l_set_id  NUMBER;

Line: 402


  l_delete_set_id NUMBER;

Line: 419


         SELECT reservation_set_id into l_set_id
         FROM oe_reservation_sets
              WHERE reservation_set_name = P_reserve_set_name;

Line: 424


         DELETE oe_rsv_set_details
         WHERE reservation_set_id = l_set_id;

Line: 427


         DELETE oe_reservation_sets
         WHERE reservation_set_id = l_set_id;

Line: 435


         SELECT oe_reservation_sets_s.nextval INTO g_set_id FROM dual;

Line: 445


      Insert Into oe_reservation_sets
         (Reservation_Set_Id
         ,Reservation_Set_Name
         ,Reservation_request_id
         ,process_Flag
         ,Simulation_Request_id
         ,Creation_date
         ,created_by
         ,program_update_date
         ,program_application_id
         ,program_id
         ,last_update_login
         ,last_updated_by
         ,last_update_date)
      Values
        ( g_set_id
        , P_reserve_set_name
        , p_rsv_request_id
        , l_process_flag
        , p_simulation_request_id
        , sysdate
        , FND_GLOBAL.USER_ID
        , sysdate
        , g_program_application_id
        , g_program_id
        , FND_GLOBAL.CONC_LOGIN_ID
        , FND_GLOBAL.USER_ID
        , sysdate);

Line: 480


      INSERT INTO oe_rsv_set_details
      (Reservation_set_id
       ,Line_id
       ,header_id
       ,inventory_item_id
       ,ordered_qty
       ,ordered_qty2 -- INVCONV
       ,ordered_qty_uom
       ,ordered_qty_uom2 -- INVCONV
       ,derived_qty
       ,derived_qty2 -- INVCONV
       ,derived_qty_uom
       ,corrected_qty
       ,corrected_qty2 -- INVCONV
       ,ship_from_org_id
       ,subinventory
       ,creation_date
       ,created_by
       ,last_update_date
       ,last_updated_by
       ,last_update_login
       ,program_application_id
       ,program_id
       ,program_update_date)
     Values
       (g_set_id
       ,p_rsv_tbl(I).line_id
       ,p_rsv_tbl(I).header_id
       ,p_rsv_tbl(I).inventory_item_id
       ,p_rsv_tbl(I).ordered_qty
       ,p_rsv_tbl(I).ordered_qty2 -- INVCONV
       ,p_rsv_tbl(I).ordered_qty_uom
       ,p_rsv_tbl(I).ordered_qty_uom2 -- INVCONV
       ,p_rsv_tbl(I).derived_reserved_qty
       ,p_rsv_tbl(I).derived_reserved_qty2 -- INVCONV
       ,p_rsv_tbl(I).reserved_qty_UOM
       ,NVL(p_rsv_tbl(I).corrected_reserved_qty,p_rsv_tbl(I).derived_reserved_qty)
       ,NVL(p_rsv_tbl(I).corrected_reserved_qty2,p_rsv_tbl(I).derived_reserved_qty2) -- INVCONV
       ,p_rsv_tbl(I).ship_from_org_id
       ,p_rsv_tbl(I).subinventory
       ,sysdate
       ,FND_GLOBAL.USER_ID
       ,sysdate
       ,FND_GLOBAL.USER_ID
       ,FND_GLOBAL.CONC_LOGIN_ID
       ,g_program_application_id
       ,g_program_id
       ,sysdate);

Line: 858


    SELECT msi.lot_control_code
    INTO   l_lot_control_code
    FROM   mtl_system_items msi
    WHERE  msi.inventory_item_id = p_inventory_item_id
    AND    msi.organization_id   = p_ship_from_org_id;

Line: 1002


      SELECT primary_uom_code
      INTO   l_primary_uom
      FROM   mtl_system_items_b
      WHERE inventory_item_id = p_inventory_item_id
      AND    organization_id = NVL(p_ship_from_org_id,
                       OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID',p_rsv_tbl(1).org_id)); --4759251

Line: 1011


           OE_DEBUG_PUB.Add('Error in selecting Primary UOM code',1);

Line: 1352


            OE_MSG_PUB.update_msg_context(
              p_entity_code                => 'LINE'
             ,p_entity_id                  => p_x_rsv_tbl(I).line_id
             ,p_header_id                  => p_x_rsv_tbl(I).header_id
             ,p_line_id                    => p_x_rsv_tbl(I).line_id
             ,p_orig_sys_document_ref      => p_x_rsv_tbl(I).orig_sys_document_ref
             ,p_orig_sys_document_line_ref => p_x_rsv_tbl(I).orig_sys_line_ref
             ,p_change_sequence            => p_x_rsv_tbl(I).change_sequence
             ,p_source_document_id         => p_x_rsv_tbl(I).source_document_id
             ,p_source_document_line_id    => p_x_rsv_tbl(I).source_document_line_id
             ,p_order_source_id            => p_x_rsv_tbl(I).order_source_id
             ,p_source_document_type_id    => p_x_rsv_tbl(I).source_document_type_id
             );

Line: 1743


         SELECT 'Reservation Exists'
         INTO l_dummy
         FROM MTL_RESERVATIONS
         WHERE DEMAND_SOURCE_LINE_ID = p_line_rec.line_id;

Line: 1774


       SELECT 'Staging Exists'
       INTO   l_dummy
       FROM   WSH_DELIVERY_DETAILS
       WHERE  SOURCE_LINE_ID = p_line_rec.line_id
       AND    SOURCE_CODE = 'OE' -- Added for bug 3286756
       AND    RELEASED_STATUS IN ('Y', 'C');

Line: 2088


 p_selected_ids                 IN VARCHAR2   DEFAULT NULL,
 p_dummy3                       IN VARCHAR2   DEFAULT NULL,
 p_partial_preference           IN VARCHAR2   DEFAULT 'N'
)IS

l_stmt                          VARCHAR2(4000) :=NULL;

Line: 2125


SELECT reservation_set_id FROM oe_reservation_sets
WHERE reservation_set_name = p_reserve_set_name;

Line: 2129


SELECT process_flag FROM oe_reservation_sets
WHERE reservation_set_name = p_reserve_set_name;

Line: 2151


	SELECT created_by INTO l_created_by
	from OE_RESERVATION_SETS
	WHERE reservation_set_name = p_reserve_set_name;

Line: 2355


   SELECT FND_DATE.Canonical_To_Date(p_request_date_low),
          FND_DATE.Canonical_To_Date(p_request_date_high),
          FND_DATE.Canonical_To_Date(p_schedule_ship_date_low),
          FND_DATE.Canonical_To_Date(p_schedule_ship_date_high),
          FND_DATE.Canonical_To_Date(p_schedule_arrival_date_low),
          FND_DATE.Canonical_To_Date(p_schedule_arrival_date_high),
          FND_DATE.Canonical_To_Date(p_ordered_date_low),
          FND_DATE.Canonical_To_Date(p_ordered_date_high)
   INTO   l_request_date_low,
          l_request_date_high,
          l_schedule_ship_date_low,
          l_schedule_ship_date_high,
          l_schedule_arrival_date_low,
          l_schedule_arrival_date_high,
          l_ordered_date_low,
          l_ordered_date_high
   FROM   DUAL;

Line: 2384


      l_stmt := 'SELECT Line_id FROM  OE_Reservation_Sets_V rset WHERE '||
               'rset.reservation_set_name=:reservation_set_name';

Line: 2389


      l_stmt := 'SELECT Line_id, l.org_id FROM  OE_ORDER_LINES l, OE_ORDER_HEADERS_ALL h ,MTL_SYSTEM_ITEMS msi ';

Line: 2462


      IF p_selected_ids IS NOT NULL THEN  --Pack J
         --l_stmt := l_stmt ||' AND l.line_id IN(:selected_ids)';  --p_selected_ids

Line: 2465


        l_stmt := l_stmt ||' AND l.line_id IN(SELECT line_id FROM
                     oe_rsv_set_details WHERE reservation_set_id=:set_id)';

Line: 2469


         l_stmt := l_stmt ||' AND NOT Exists (SELECT 1 FROM mtl_reservations mrs '
                          ||' WHERE l.line_id = mrs.demand_source_line_id)';

Line: 2472


         l_stmt := l_stmt ||' AND (NOT Exists (SELECT 1 FROM mtl_reservations mrs '
                          ||' WHERE l.line_id = mrs.demand_source_line_id)'
                          ||' OR l. ordered_quantity > (select sum(INV_CONVERT.INV_UM_CONVERT( '
                          ||' l.inventory_item_id, 5, reservation_quantity, reservation_uom_code,'
                          ||' l.order_quantity_uom,  NULL, NULL)) from mtl_reservations '
                          ||' where demand_source_line_id = l.line_id))';

Line: 2569


      IF p_selected_ids IS NOT NULL THEN
        DBMS_SQL.BIND_VARIABLE(l_cursor_id,':set_id',p_selected_ids);

Line: 2765


                  lines were selected',1);

Line: 2783


         Update_Reservation_Set
                     (p_reservation_set_id => l_set_id
                     ,x_return_status => l_return_status);

Line: 2880


            l_temp_rsv_tbl.DELETE;

Line: 2945


   IF p_selected_ids IS NOT NULL THEN
      DELETE FROM oe_rsv_set_details
      WHERE reservation_set_id = p_selected_ids;