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.GMI_TRANS_ENGINE_PVT SQL Statements

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

Line: 8


 *          DELETE_PENDING_TRANSACTION                         *
 *          UPDATE_PENDING_TRANSACTION                         *
 *          UPDATE_PENDING_TO_COMPLETED                        *
 *          CREATE_COMPLETED_TRANSACTION                       *
 *                                                             *
 * Use      This is the top level of the private layer for the *
 *          Inventory Transaction Processor.                   *
 *                                                             *
 * History                                                     *
 *          Written by Harminder Verdding, OPM Development     *
 *                                                             *
 * 23-May-00 P.J.Schofield for B1294915. Added Completed       *
 *          Transaction support and also logic for XFER        *
 *          pending transactions                               *
 * 13-OCT-00 Jalaj Srivastava Bug 1427922.
 *           Grade and status txns can happen in closed
 *           periods.
 *           Added logic for grade changes                     *
 * 14-JUN-01 H Verdding Bug 1834369 .
 *           Encapsulated Validate Trans Date Logic With new
 *           Function CHECK_PERIOD_CLOSE.
 * 24-AUG-01 Added line_detail_id BUG#1675561
 *	     Added NVL(p_tran_rec.trans_date,SYSDATE)  for     *
 *           creation_date in COMPLETED_TRANSACTION_BUILD per  *
 *           Karen's request.                                  *
 * 03-OCT-01 H Verdding Bug 2025933
 *           Added Fetch to get noninv Value For Item          *
 *================================================
 *   Joe DiIorio 10/22/2001 11.5.1H BUG#2064443
 *   Added reason code assigment.
 *================================================
 *   Joe DiIorio 04/08/2002 11.5.1I BUG#2248778
 *   Added Whse code to message ic_api_txn_post_closed.
 *   Jatinder 4/11/2002 - removed extra comments character
 *   which were causing the compilation error.
 *   Thomas Daniel 04/18/2002 11.5.1I BUG#2322973
 *   In the close_period_check function added code to invoke the
 *   trans date validate routine with the sysdate only if the
 *   period was closed for the passed in trans date. Also added
 *   specific messaging to the return codes from trans date validate.
 *
 *  VRA Srinivas  26/Apr/2002 BUG#2341493
 *  Changed the code to not to insert into IC_TRAN_CMP when
 *  DOC_TYPE is STSI and STATUS_CTL is No Inventory.
 *  Jalaj Srivastava 07/24/02 Bug 2483644
 *  Modified create completed transaction to accept/process
 *  journal transactions doc types also.
 *  Jalaj Srivastava Bug 2519568
 *  Removed DML code for ic_summ_inv since, now ic_summ_inv is
 *  a view created from the data in ic_loct_inv and ic_tran_pnd
 *  Joe DiIorio      Bug 2643440  11.5.1J
 *  Added nocopy.
 *  Joe DiIorio      Bug 3090255  11.5.10L  08/15/2003
 *  Added field intorder_posted_ind.
 *  Jeff Baird       Bug #3409615  02/05/2004
 *  Added who columns to update of ic_lots_mst.
 *  Jeff Baird       Bug #3434156  02/10/2004
 *  Corrected column name in above fix.
 *  V.Anitha         BUG#3526733   14-APR-2004
 *  Added colum reverse_id column in create_completed_transactions
 *  and COMPLETED_TRANSACTION_BUILD procedures.
 *  Teresa Wong      B3415691 	   6/7/2004
 *  Enhancement for Serono (Pls refer to B3599127)
 *  Modified update_pending_to_completed
 *  to ensure the completed transaction reflected
 *  the lot status and qc grade of the lot in ic_loct_inv
 *  at the time the transaction took place
 *********************************************************************
*/
/* Global variables */
G_PKG_NAME  CONSTANT  VARCHAR2(30):='GMI_TRANS_ENGINE_PVT';

Line: 141


  /* Call the IC_TRAN_PND INSERT procedure to insert this record. */

  IF NOT GMI_TRAN_PND_DB_PVT.INSERT_IC_TRAN_PND
    ( p_tran_row => l_tran_row, x_tran_row => x_tran_row)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line: 221


  SELECT
    status_ctl
  FROM
    ic_item_mst
  WHERE
    item_id = pitem_id;

Line: 294


    /*  Update Inventory Balances ( Actuals).  */
 /* ***********************************************************
    Jalaj Srivastava Bug 2519568
    Removed DML code for ic_summ_inv since, now ic_summ_inv is
    a view created from the data in ic_loct_inv and ic_tran_pnd
    *********************************************************** */


/*  Jalaj Srivastava Bug 1427922 */
/*  Added logic for grade txns and to update ic_lots_mst for change in gradeREM  */

 --Jalaj Srivastava Bug 2483644
 --Now journal txns would also be posted using these APIs
IF (substr(l_tran_rec.doc_type,1,3) = 'GRD') THEN

    /*  Update ic_lots_mst with the new grade */

    update ic_lots_mst set
      qc_grade         = l_tran_rec.qc_grade,
      last_update_date = SYSDATE,
      last_updated_by  = p_tran_rec.user_id
    where  item_id     = l_tran_rec.item_id
    and    lot_id      = l_tran_rec.lot_id
    and    delete_mark = 0;

Line: 325


    /*  Call the IC_TRAN_CMP INSERT procedure to insert this record. */

  IF p_table_name = 'IC_TRAN_CMP'
  THEN
    --BEGIN Bug# 2341493 VRA Srinivas  26/04/2002
    --Do not insert into ic_tran_cmp if the status control
    --of the item is set to 'No Inventory'
    OPEN Cur_status_ctl(p_tran_rec.item_id);

Line: 343


    	/*  Call the IC_TRAN_CMP INSERT procedure to insert this record. */
    	l_tran_cmp.trans_id      	    := p_tran_rec.trans_id;

Line: 370


    	l_tran_cmp.last_update_date   := SYSDATE;

Line: 371


    	l_tran_cmp.last_updated_by    := p_tran_rec.user_id;

Line: 379


    	IF NOT GMI_TRAN_CMP_PVT.INSERT_IC_TRAN_CMP
      	( p_tran_row => l_tran_cmp, x_tran_row => x_tran_row)
    	THEN
      		RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line: 389


    /*  Call the IC_TRAN_PND INSERT procedure to insert this record. */
    l_tran_pnd.trans_id      	    := p_tran_rec.trans_id;

Line: 416


    l_tran_pnd.last_update_date   := SYSDATE;

Line: 417


    l_tran_pnd.last_updated_by    := p_tran_rec.user_id;

Line: 421


    l_tran_pnd.delete_mark        := 0;

Line: 432


    IF NOT GMI_TRAN_PND_DB_PVT.INSERT_IC_TRAN_PND
      ( p_tran_row => l_tran_pnd, x_tran_row => l_tran_pnd)
    THEN
      RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line: 465


    x_tran_row.last_update_date   := l_tran_pnd.last_update_date;

Line: 466


    x_tran_row.last_updated_by    := l_tran_pnd.op_code;

Line: 521


PROCEDURE DELETE_PENDING_TRANSACTION
( p_api_version      IN  NUMBER
, p_init_msg_list    IN  VARCHAR2 := FND_API.G_FALSE
, p_commit           IN  VARCHAR2 := FND_API.G_FALSE
, p_validation_level IN  VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
, p_tran_rec         IN  GMI_TRANS_ENGINE_PUB.ictran_rec
, x_tran_row         OUT NOCOPY IC_TRAN_PND%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) := 'DELETE_PENDING_TRANSACTION';

Line: 545


  SAVEPOINT delete_pending_transaction;

Line: 612


  /*  Call the IC_TRAN_PND DELETE procedure to Logically Delete this record. */

  IF NOT GMI_TRAN_PND_DB_PVT.DELETE_IC_TRAN_PND
    ( p_tran_row => l_tran_row )
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line: 632


      ROLLBACK TO delete_pending_transaction;

Line: 640


      ROLLBACK TO delete_pending_transaction;

Line: 647


      ROLLBACK TO delete_pending_transaction;

Line: 659


END DELETE_PENDING_TRANSACTION;

Line: 661


PROCEDURE UPDATE_PENDING_TRANSACTION
( p_api_version      IN  NUMBER
, p_init_msg_list    IN  VARCHAR2 := FND_API.G_FALSE
, p_commit           IN  VARCHAR2 := FND_API.G_FALSE
, p_validation_level IN  VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
, p_tran_rec         IN  GMI_TRANS_ENGINE_PUB.ictran_rec
, x_tran_row         OUT NOCOPY IC_TRAN_PND%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) := 'UPDATE_PENDING_TRANSACTION';

Line: 688


  SAVEPOINT update_pending_transaction;

Line: 751


      /*  Update Previous Demand  */

      l_tran_fetch_rec.trans_qty  := l_tran_rec.trans_qty
                                  - l_tran_fetch_rec.trans_qty;

Line: 762


  /*  Call the IC_TRAN_PND INSERT procedure to insert this record. */
  /*  Call Create_pending_transaction to build ic_tran_pnd%rowtype; */

Line: 772


  IF NOT GMI_TRAN_PND_DB_PVT.UPDATE_IC_TRAN_PND
    ( p_tran_row => l_tran_row)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line: 790


      ROLLBACK TO update_pending_transaction;

Line: 798


      ROLLBACK TO update_pending_transaction;

Line: 805


      ROLLBACK TO update_pending_transaction;

Line: 816


END UPDATE_PENDING_TRANSACTION;

Line: 818


PROCEDURE UPDATE_PENDING_TO_COMPLETED
( p_api_version      IN  NUMBER
, p_init_msg_list    IN  VARCHAR2 := FND_API.G_FALSE
, p_commit           IN  VARCHAR2 := FND_API.G_FALSE
, p_validation_level IN  VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
, p_tran_rec         IN  GMI_TRANS_ENGINE_PUB.ictran_rec
, x_tran_row         OUT NOCOPY IC_TRAN_PND%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) := 'UPDATE_PENDING_TO_TRANSACTION';

Line: 851


  SELECT
	lot_status
  FROM
	ic_loct_inv
  WHERE
	item_id = v_item_id
	AND whse_code = v_whse
	AND lot_id = v_lot_id
	AND location = v_location;

Line: 862


  SELECT
	DECODE(i.lot_id, 0, NULL, m.qc_grade)
  FROM
	ic_loct_inv i,
	ic_lots_mst m
  WHERE
	m.lot_id = i.lot_id
	AND i.lot_id = v_lot_id;

Line: 952


  IF NOT gmivdbl.ic_item_mst_select (
	p_ic_item_mst_row     => l_item_mst_rec,
	x_ic_item_mst_row     => l_item_mst_rec
  ) THEN
	RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line: 983


  IF NOT GMI_TRAN_PND_DB_PVT.UPDATE_IC_TRAN_PND ( p_tran_row => l_tran_row)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line: 1025


END UPDATE_PENDING_TO_COMPLETED;

Line: 1064


  x_tran_row.delete_mark     	:= 0; /* Always 0 */

Line: 1066


  x_tran_row.last_update_date	:= SYSDATE;

Line: 1067


  x_tran_row.last_updated_by 	:= p_tran_rec.user_id;

Line: 1117


/* NC 8/16/02 Commenting the below line. There's no need to update the
creation date when completing the pending transaction. The creation_date
should always reflect the date when the transaction is created.The DML
update statement had already been commented in GMI_TRAN_PND_DB_PVT.update_ic_tran_pnd for bug#2385934)
x_tran_row.creation_date     	:= NVL(p_tran_rec.trans_date,SYSDATE);

Line: 1136


x_tran_row.delete_mark      	:= 0; /* NVL(p_tran_rec.delete_mark,0);*/

Line: 1138


x_tran_row.last_update_date  	:= SYSDATE;

Line: 1139


x_tran_row.last_updated_by   	:= p_tran_rec.user_id;

Line: 1180


SELECT noninv_ind
FROM   ic_item_mst
WHERE  item_id = p_item_id;