APPS.GMIVILC SQL Statements

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

Line: 45

 |    Validate an item conversion record and set up the row for insertion   |
 |    into the database                                                     |
 |                                                                          |
 | PARAMETERS                                                               |
 |    p_api_version      IN NUMBER           - API Version                  |
 |    p_init_msg_list    IN VARCHAR2         - Msg List initialization Ind  |
 |    p_commit           IN VARCHAR2         - Commit Indicator             |
 |    p_validation_level IN VARCHAR2         - Validation Level indicator   |
 |    x_return_status    OUT VARCHAR2        - return Status                |
 |    x_msg_count        OUT NUMBER          - Number of Messages returned  |
 |    x_msg_data         OUT VARCHAR2        - Messages in encoded format   |
 |    p_item_cnv_rec     IN item_cnv_rec_typ - Item Conversion details      |
 |                                                                          |
 | RETURNS                                                                  |
 |    None                                                                  |
 |                                                                          |
 | HISTORY                                                                  |
 |                                                                          |
 | 02-May-2001 A. Mundhe	Bug 1741321 - Added code to validate primary and |
 |                         secondary UOM.                                   |
 |                                                                          |
 | 03-Jul-2002 A. Mundhe   Bug 2446245 - Modified the code such that        |
 |                         typefactor rev is not recalculated unnecessarily |
 |                         causing decimal precision issues.                |
 | 11-Nov-2002 J. DiIorio  Bug 2643440 - 11.5.1J - added nocopy.            |
 +==========================================================================+
*/
PROCEDURE Validate_Lot_Conversion
( p_api_version      IN  NUMBER
, p_validation_level IN  NUMBER :=FND_API.G_VALID_LEVEL_FULL
, p_item_cnv_rec     IN  GMIGAPI.conv_rec_typ
, p_ic_item_mst_row  IN  ic_item_mst%ROWTYPE
, p_ic_lots_mst_row  IN  ic_lots_mst%ROWTYPE
, x_ic_item_cnv_row  OUT NOCOPY ic_item_cnv%ROWTYPE
, x_return_status    OUT NOCOPY VARCHAR2
, x_msg_count        OUT NOCOPY NUMBER
, x_msg_data         OUT NOCOPY VARCHAR2
)
IS
l_api_name    CONSTANT VARCHAR2 (30) :='Validate Conversion';

Line: 104

/*  Bug#2880585 - New cursors for insert checks */


X_count                NUMBER;

Line: 111

  SELECT lot_id
  FROM  ic_lots_mst
  WHERE item_id = p_ic_item_mst_row.item_id and
        lot_no = p_item_cnv_rec.lot_no and
        sublot_no = p_item_cnv_rec.sublot_no;

Line: 118

  SELECT count(*)
  FROM  ic_tran_cmp
  WHERE item_id = p_ic_item_mst_row.item_id and
        lot_id = x_lot_id;

Line: 124

  SELECT count(*)
  FROM   ic_tran_pnd
  WHERE  item_id = p_ic_item_mst_row.item_id and
         lot_id = x_lot_id;

Line: 130

    SELECT count(1)
    FROM   ic_jrnl_mst m, ic_adjs_jnl a
    WHERE  m.journal_id = a.journal_id
    AND  a.item_id = p_ic_item_mst_row.item_id
    AND  a.lot_id = x_lot_id;

Line: 150

	 p_ic_item_mst_row.delete_mark = 1
  THEN
    FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');

Line: 167

     p_ic_lots_mst_row.delete_mark = 1 OR
     p_ic_lots_mst_row.inactive_ind = 1 AND GMIGUTL.IC$API_ALLOW_INACTIVE = 0
  THEN
    FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_NO');

Line: 221

  SELECT from_type.um_type, from_type.std_um,
           to_type.um_type,   to_type.std_um,
         item_type.um_type, item_type.std_um
  INTO   l_from_type, l_from_std, l_to_type, l_to_std,
         l_item_type, l_item_std
  FROM
  		 sy_uoms_typ from_type,
  		 sy_uoms_mst from_std,
  		 sy_uoms_typ to_type,
  		 sy_uoms_mst to_std,
		 sy_uoms_typ item_type,
		 sy_uoms_mst item_std
  WHERE
         from_type.um_type=from_std.um_type AND
		 from_std.um_code=l_rec_from_um AND
		 from_type.delete_mark=0 AND
		 from_std.delete_mark=0 AND
         item_type.um_type=item_std.um_type AND
		 item_std.um_code=p_ic_item_mst_row.item_um AND
		 item_type.delete_mark=0 AND
		 item_std.delete_mark=0 AND
         to_type.um_type=to_std.um_type AND
		 to_std.um_code=l_rec_to_um AND
		 to_type.delete_mark=0 AND
		 to_std.delete_mark=0;

Line: 395

/*  Set up PL/SQL record for insertion into IC_ITEM_CNV */

  x_ic_item_cnv_row.item_id          := p_ic_item_mst_row.item_id;

Line: 401

  x_ic_item_cnv_row.last_update_date := SYSDATE;

Line: 402

  x_ic_item_cnv_row.last_updated_by  := GMIGUTL.DEFAULT_USER_ID;

Line: 404

  x_ic_item_cnv_row.delete_mark      := 0;

Line: 409

  x_ic_item_cnv_row.last_update_login := GMIGUTL.DEFAULT_LOGIN;