APPS.GML_BATCH_OM_UTIL SQL Statements

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

Line: 48

     Select *
     Into l_res_rec
     From gml_batch_so_reservations
     Where so_line_id = p_so_line_rec.so_line_id
       and batch_line_id = p_batch_line_rec.batch_line_id;

Line: 55

     Select *
     Into l_res_rec
     From gml_batch_so_reservations
     Where so_line_id = p_so_line_rec.so_line_id;

Line: 61

     Select *
     Into l_res_rec
     From gml_batch_so_reservations
     Where batch_line_id = p_batch_line_rec.batch_line_id;

Line: 67

     Select *
     Into l_res_rec
     From gml_batch_so_reservations
     Where batch_res_id = p_gme_om_reservation_rec.batch_res_id;

Line: 88

 PROCEDURE insert_reservation
 (
    P_Gme_om_reservation_rec IN    GML_BATCH_OM_UTIL.gme_om_reservation_rec
  , X_return_status          OUT   NOCOPY VARCHAR2
  , X_msg_cont               OUT   NOCOPY NUMBER
  , X_msg_data               OUT   NOCOPY VARCHAR2
 ) IS
 l_batch_res_id        NUMBER;

Line: 98

  GMI_RESERVATION_UTIL.Println(' In insert reservations');

Line: 99

  /* insert gme_om_reservation_rec into gml_batch_so_reservations table */
  select gml_so_reservation_s.nextval
  into l_batch_res_id
  from dual;

Line: 103

  Insert Into gml_batch_so_reservations
  (
        batch_res_id
      , batch_id
      , batch_line_id
      , so_line_id
      , order_id
      , rule_id
      , delivery_detail_id
      , mo_line_id
      , item_id
      , reserved_qty
      , reserved_qty2
      , qty_uom
      , qty2_uom
      , whse_code
      , organization_id
      , allocated_ind
      , batch_type
      , delete_mark
      , created_by
      , creation_date
      , last_updated_by
      , last_update_date
  )
  Values
  (
        l_batch_res_id
      , p_gme_om_reservation_rec.batch_id
      , p_gme_om_reservation_rec.batch_line_id
      , p_gme_om_reservation_rec.so_line_id
      , p_gme_om_reservation_rec.order_id
      , p_gme_om_reservation_rec.rule_id
      , p_gme_om_reservation_rec.delivery_detail_id
      , p_gme_om_reservation_rec.mo_line_id
      , p_gme_om_reservation_rec.item_id
      , p_gme_om_reservation_rec.reserved_qty
      , p_gme_om_reservation_rec.reserved_qty2
      , p_gme_om_reservation_rec.uom1
      , p_gme_om_reservation_rec.uom2
      , p_gme_om_reservation_rec.whse_code
      , p_gme_om_reservation_rec.organization_id
      , 0
      , p_gme_om_reservation_rec.batch_type
      , 0
      , fnd_global.user_id
      , sysdate
      , fnd_global.user_id
      , sysdate
  );

Line: 153

  GMI_RESERVATION_UTIL.Println(' In insert reservations, new batch_res_id '||l_batch_res_id);

Line: 173

 END insert_reservation;

Line: 175

 PROCEDURE update_reservation
 (
    P_Gme_om_reservation_rec IN    GML_BATCH_OM_UTIL.gme_om_reservation_rec
  , X_return_status          OUT   NOCOPY VARCHAR2
  , X_msg_cont               OUT   NOCOPY NUMBER
  , X_msg_data               OUT   NOCOPY VARCHAR2
 ) IS
 BEGIN
  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 184

  update gml_batch_so_reservations
  Set
        batch_id                = p_gme_om_reservation_rec.batch_id
      , batch_line_id           = p_gme_om_reservation_rec.batch_line_id
      , so_line_id              = p_gme_om_reservation_rec.so_line_id
      , order_id                = p_gme_om_reservation_rec.order_id
      , delivery_detail_id      = p_gme_om_reservation_rec.delivery_detail_id
      , mo_line_id              = p_gme_om_reservation_rec.mo_line_id
      , reserved_qty            = p_gme_om_reservation_rec.reserved_qty
      , reserved_qty2           = p_gme_om_reservation_rec.reserved_qty2
      , qty_uom                 = p_gme_om_reservation_rec.uom1
      , qty2_uom                = p_gme_om_reservation_rec.uom2
      , whse_code               = p_gme_om_reservation_rec.whse_code
      , organization_id         = p_gme_om_reservation_rec.organization_id
      , batch_type              = p_gme_om_reservation_rec.batch_type
      , delete_mark             = p_gme_om_reservation_rec.delete_mark
      , last_updated_by         = fnd_global.user_id
      , last_update_date        = sysdate
  Where batch_res_id = p_gme_om_reservation_rec.batch_res_id;

Line: 203

 END update_reservation;

Line: 205

 PROCEDURE delete_reservation
 (
    P_Batch_res_id           IN    NUMBER default null
  , P_Batch_line_id          IN    NUMBER default null
  , P_Batch_id               IN    NUMBER default null
  , X_return_status          OUT   NOCOPY VARCHAR2
  , X_msg_cont               OUT   NOCOPY NUMBER
  , X_msg_data               OUT   NOCOPY VARCHAR2
 ) IS
 BEGIN
   x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 217

     update gml_batch_so_reservations
     set delete_mark = 1
     Where batch_res_id = p_batch_res_id;

Line: 222

     update gml_batch_so_reservations
     set delete_mark = 1
     Where batch_line_id = p_batch_line_id;

Line: 227

     update gml_batch_so_reservations
     set delete_mark = 1
     Where batch_id = p_batch_id;

Line: 231

 END delete_reservation;

Line: 246

     Select *
     Into l_history_row
     from gml_batch_so_alloc_history
     Where alloc_rec_id = p_alloc_history_rec.alloc_rec_id;

Line: 271

 PROCEDURE insert_alloc_history
 (
    P_alloc_history_rec      IN    GML_BATCH_OM_UTIL.alloc_history_rec
  , X_return_status          OUT   NOCOPY VARCHAR2
  , X_msg_cont               OUT   NOCOPY NUMBER
  , X_msg_data               OUT   NOCOPY VARCHAR2
 ) IS
 l_alloc_rec_id        NUMBER;

Line: 281

  /* insert gme_om_reservation_rec into gml_batch_so_reservations table */
  GMI_RESERVATION_UTIL.println('inserting alloc history');

Line: 283

  select gml_so_alloc_history_s.nextval
  into l_alloc_rec_id
  from dual;

Line: 286

  Insert Into gml_batch_so_alloc_history
  (   Alloc_rec_id
    , Batch_res_id
    , Batch_id
    , Trans_id
    , line_id
    , Lot_id
    , Location
    , Whse_code
    , Rule_id
    , Failure_reason
    , batch_trans_id
    , batch_line_id
    , delete_mark
    , CREATION_DATE
    , CREATED_BY
    , LAST_UPDATED_DATE
    , LAST_UPDATED_BY
   )
  Values
  (
        l_alloc_rec_id
      , p_alloc_history_rec.batch_res_id
      , p_alloc_history_rec.batch_id
      , p_alloc_history_rec.trans_id
      , p_alloc_history_rec.so_line_id
      , p_alloc_history_rec.lot_id
      , p_alloc_history_rec.location
      , p_alloc_history_rec.whse_code
      , p_alloc_history_rec.rule_id
      , p_alloc_history_rec.failure_reason
      , p_alloc_history_rec.batch_trans_id
      , p_alloc_history_rec.batch_line_id
      , 0
      , sysdate
      , fnd_global.user_id
      , sysdate
      , fnd_global.user_id
  );

Line: 331

 END insert_alloc_history;

Line: 357

     Select count(*)
     Into l_exist
     From gml_batch_so_reservations
     Where batch_id = p_batch_id
      and  delete_mark = 0
      and  (reserved_qty > 0 or allocated_ind = 1)
      ;

Line: 370

     Select count(*)
     Into l_exist
     From gml_batch_so_reservations
     Where batch_line_id = p_batch_line_id
      and  delete_mark = 0
      and  (reserved_qty > 0 or allocated_ind = 1)
      ;

Line: 383

     Select count(*)
     Into l_exist
     From gml_batch_so_reservations
     Where so_line_id = p_so_line_id
      and  delete_mark = 0
      and  (reserved_qty > 0 or allocated_ind = 1)
      ;

Line: 395

     Select count(*)
     Into l_exist
     From gml_batch_so_reservations
     Where delivery_detail_id = p_delivery_detail_id
      and  delete_mark = 0
      and  (reserved_qty > 0 or allocated_ind = 1)
      ;

Line: 446

  Select  decode(site_use_id, null, 0, site_use_id) site_use_id
       ,  decode(customer_id, null, 0, customer_id) customer_id
       ,  decode(item_id, null, 0, item_id)         item_id
       ,  decode(allocation_class, null, ' ', allocation_class) allocation_class
       ,  rule_assign_id
       ,  rule_id
  From gml_batch_so_rule_assignments
  Where whse_code = p_gme_om_config_assign.whse_code
    and (item_id = p_gme_om_config_assign.item_id
         or item_id is null )
    and (allocation_class = p_gme_om_config_assign.allocation_class
         or allocation_class is null)
    and (customer_id = p_gme_om_config_assign.customer_id
         or customer_id is null)
    and (site_use_id = p_gme_om_config_assign.site_use_id
         or site_use_id is null)
    and delete_mark = 0
  Order by
    1 desc
  , 2 desc
  , 3 desc
  , 4 desc
  ;

Line: 490

  /*Select  rule_id
       ,  rule_assign_id
  Into l_rule_id
    ,  l_rule_assign_id
    ,  nvl(item_id, 0)
    ,  nvl(allocation_class, '0')
    ,  nvl(customer_id, 0)
    ,  nvl(site_use_id, 0)
  From gml_batch_so_rule_assignments
  Where whse_code = p_gme_om_config_assign.whse_code
    and (item_id = nvl(p_gme_om_config_assign.item_id,0)
         or item_id is null )
    and (allocation_class = nvl(p_gme_om_config_assign.allocation_class,'0')
         or allocation_class is null)
    and (customer_id = nvl(p_gme_om_config_assign.customer_id,0)
         or customer_id is null)
    and (site_use_id = nvl(p_gme_om_config_assign.site_use_id,0)
         or site_use_id is null)
    and delete_mark = 0
  Order by
    site_use_id desc
  , customer_id desc
  , item_id desc
  , allocation_class desc
  ;*/

Line: 525

  /*Select item_id
     ,   allocation_class
     ,   customer_id
     ,   site_use_id
  Into l_rule_assign_rec.item_id
     , l_rule_assign_rec.allocation_class
     , l_rule_assign_rec.customer_id
     , l_rule_assign_rec.site_use_id
  From gml_batch_so_rule_assignments
  Where rule_assign_id = l_rule_assign_id;

Line: 540

  l_where_clause := 'delete_mark = 0 and whse_code = ';

Line: 572

      'SELECT count(*) FROM gml_batch_so_rule_assignments WHERE '
      || l_where_clause ;

Line: 608

 Select distinct so_line_id
 From gml_batch_so_reservations
 Where batch_line_id = p_batch_line_id;

Line: 613

 Select sold_to_org_id
   ,    ship_to_org_id
   ,    inventory_item_id
   ,    ship_from_org_id
 From oe_order_lines_all
 Where line_id = p_so_line_id;

Line: 624

 Select ic.alloc_class
    ,   ic.item_id
 From ic_item_mst ic
    , mtl_system_items mtl
 Where ic.item_no = mtl.segment1
   and mtl.inventory_item_id = p_inv_item_id
   and mtl.organization_id = p_org_id;

Line: 633

 Select whse_code
 From ic_whse_mst
 Where mtl_organization_id = p_org_id;

Line: 647

  l_cust_site.delete;

Line: 784

  Select *
  Into l_rule_rec
  From gml_batch_so_rules
  Where rule_id = l_rule_id;