APPS.GMI_ALLOCATE_INVENTORY_PVT SQL Statements

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

Line: 130

    Select
           header_id,
           sold_to_org_id,
           schedule_ship_date,
           ship_to_org_id,
           org_id,
           preferred_grade
    From oe_order_lines_all
    Where line_id =  p_allocation_rec.line_id;

Line: 141

SELECT *
  FROM gmi_auto_allocation_batch
 WHERE batch_id = p_batch_id;

Line: 546

    GMI_Reservation_Util.PrintLn('(Alloc PVT) Select A ') ;

Line: 549

      'SELECT sum(loct_onhand),sum(loct_onhand2),
            sum(commit_qty),sum(commit_qty2),
            sum(loct_onhand) + sum(commit_qty),
            lot_no,sublot_no,lot_id,lot_status,
            lot_created,location,expire_date,qc_grade
            FROM ic_item_inv_v
       WHERE ' || l_where_clause  ||
       ' GROUP BY lot_no,sublot_no,lot_id,lot_status,
                  lot_created,location,expire_date,qc_grade' ||
       ' HAVING sum(loct_onhand) + sum(commit_qty) > 0 ' ||
       ' ORDER BY ' || l_order_by;

Line: 566

    GMI_Reservation_Util.PrintLn('(Alloc PVT) Select B ') ;

Line: 571

      'SELECT sum(loct_onhand),sum(loct_onhand2),
            sum(commit_qty),sum(commit_qty2),
            sum(loct_onhand) + sum(commit_qty),
            lot_no,sublot_no,lot_id,lot_status,
            lot_created,location,expire_date,qc_grade
            FROM ic_item_inv_v
       WHERE ' || l_where_clause  ||
       ' GROUP BY lot_no,sublot_no,lot_id,lot_status,
                  lot_created,location,expire_date,qc_grade' ||
       ' HAVING sum(loct_onhand) + sum(commit_qty) > 0 ' ||
       ' ORDER BY ' || l_order_by
         using l_allocation_rec.prefqc_grade;

Line: 588

    GMI_Reservation_Util.PrintLn('(Alloc PVT) Select B after query') ;

Line: 593

    GMI_Reservation_Util.PrintLn('(Alloc PVT) Select C ') ;

Line: 597

      'SELECT sum(loct_onhand),sum(loct_onhand2),
            sum(commit_qty),sum(commit_qty2),
            sum(loct_onhand) + sum(commit_qty),
            lot_no,sublot_no,lot_id,lot_status,
            lot_created,location,expire_date,qc_grade
            FROM ic_item_inv_v
       WHERE ' || l_where_clause  ||
       ' GROUP BY lot_no,sublot_no,lot_id,lot_status,
                  lot_created,location,expire_date,qc_grade' ||
       ' HAVING sum(loct_onhand) + sum(commit_qty) > 0 '||
       ' ORDER BY ' || l_order_by;

Line: 850

           GMI_Reservation_Util.PrintLn('(Alloc PVT) inserting trans ' );

Line: 851

           GMI_SHIPPING_UTIL.print_debug(l_tran_rec,'inserting');

Line: 1033

SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */ *
FROM ic_tran_pnd
WHERE item_id = p_ic_item_mst.item_id AND
    line_id = p_allocation_rec.line_id AND
    lot_id  = 0 AND
    doc_type= 'OMSO' AND
    completed_ind = 0 AND
    delete_mark = 0 AND
    location = IC$DEFAULT_LOCT;

Line: 1125

    /*This is a UPDATE scenario so ..........
    Set up parameters in readiness for updating the pending transaction
    ====================================================================*/
    l_tran_rec.trans_id  := l_default_trans.trans_id;

Line: 1161

  /* dbms_output.put_line('UPDATE the existing default LOT txn'); */

Line: 1162

    GMI_TRANS_ENGINE_PUB.update_pending_transaction
                         (p_api_version      => 1.0,
                          p_init_msg_list    => FND_API.G_TRUE,
                          p_commit           => FND_API.G_FALSE,
                          p_validation_level => FND_API.G_VALID_LEVEL_NONE,
                          p_tran_rec         => l_tran_rec,
                          x_tran_row         => l_tran_row,
                          x_return_status    => l_return_status,
                          x_msg_count        => l_msg_count,
                          x_msg_data         => l_msg_data
                         );

Line: 1249

SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */
  sum(trans_qty)
FROM
  ic_tran_pnd
WHERE
  doc_id        = p_doc_id AND
  line_id       = p_line_id AND
  doc_type      = 'OMSO' AND
  lot_id        > 0 AND
--  completed_ind = 0 AND
  delete_mark   = 0;

Line: 1264

SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */
  sum(trans_qty)
FROM
  ic_tran_pnd
WHERE
  doc_id        = p_doc_id AND
  line_id       = p_line_id AND
  location      <>IC$DEFAULT_LOCT AND
  doc_type      = 'OMSO' AND
-- completed_ind = 0 AND
  delete_mark   = 0;

Line: 1279

SELECT /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */
  sum(trans_qty)
FROM
  ic_tran_pnd
WHERE
  doc_id        = p_doc_id AND
  line_id       = p_line_id AND
  doc_type      = 'OMSO' AND
--  completed_ind = 0 AND
  delete_mark   = 0;

Line: 1370

SELECT  /*+ INDEX (ic_tran_pnd, ic_tran_pndi3) */
  sum(trans_qty)
FROM
  ic_tran_pnd
WHERE
  doc_id        = p_doc_id AND
  line_id       = p_line_id AND
  doc_type      = 'OMSO' AND
  ((lot_id       > 0) OR
  (location     <>IC$DEFAULT_LOCT)) AND
  staged_ind    <> 1 AND
  doc_type      = 'OMSO' AND
  completed_ind = 0 AND
  delete_mark   = 0;