DBA Data[Home] [Help]

APPS.GMI_CMP_TRAN_PVT SQL Statements

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

Line: 63

FUNCTION update_quantity_transaction
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS
l_cmp_tran_rec cmp_tran_typ;
Line: 77

  SELECT gem5_trans_id_s.nextval INTO l_cmp_tran_rec.trans_id FROM dual;
Line: 92

    IF NOT Update_movement(l_cmp_tran_rec)
    THEN
      RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 98

    IF NOT Update_lot_status(l_cmp_tran_rec)
    THEN
      RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 104

    IF NOT Update_QC_grade(l_cmp_tran_rec)
    THEN
      RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 123

                             , 'Update_Quantity_Transaction'
                            );
Line: 128

END Update_Quantity_Transaction;
Line: 154

FUNCTION Update_Movement
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS
l_cmp_tran_rec   cmp_tran_typ;
Line: 166

  IF NOT insert_ic_tran_cmp(l_cmp_tran_rec)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 172

  IF NOT Update_ic_loct_inv(l_cmp_tran_rec)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 178

  IF NOT Update_ic_summ_inv(l_cmp_tran_rec)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 195

                             , 'Update_Movement'
                            );
Line: 200

END Update_Movement;
Line: 225

FUNCTION update_lot_status
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS
l_cmp_tran_rec   cmp_tran_typ;
Line: 237

  IF NOT insert_ic_tran_cmp(l_cmp_tran_rec)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 243

  IF NOT Update_ic_loct_inv_LOT_STATUS(l_cmp_tran_rec)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 260

                             , 'Update_Lot_Status'
                            );
Line: 266

END Update_Lot_Status;
Line: 291

FUNCTION Update_Qc_Grade
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS
l_cmp_tran_rec   cmp_tran_typ;
Line: 303

  IF NOT insert_ic_tran_cmp(l_cmp_tran_rec)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 311

    UPDATE ic_lots_mst
    SET
      qc_grade = p_cmp_tran_rec.qc_grade
    , last_updated_by  = p_cmp_tran_rec.user_id
    , last_update_date = SYSDATE
    WHERE
        item_id = p_cmp_tran_rec.item_id
    AND lot_id  = p_cmp_tran_rec.lot_id;
Line: 322

  IF NOT Update_ic_summ_inv_QC_GRADE(l_cmp_tran_rec)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 339

                             , 'Update_Qc_Grade'
                            );
Line: 344

END Update_Qc_Grade;
Line: 368

FUNCTION Insert_Ic_Tran_Cmp
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS

BEGIN

  INSERT INTO ic_tran_cmp
  ( item_id
  , line_id
  , trans_id
  , co_code
  , orgn_code
  , whse_code
  , lot_id
  , location
  , doc_id
  , doc_type
  , doc_line
  , line_type
  , reason_code
  , creation_date
  , trans_date
  , trans_qty
  , trans_qty2
  , qc_grade
  , lot_status
  , trans_stat
  , trans_um
  , trans_um2
  , op_code
  , gl_posted_ind
  , event_id
  , text_code
  , last_update_date
  , created_by
  , last_updated_by
  )
  VALUES
  ( p_cmp_tran_rec.item_id
  , p_cmp_tran_rec.line_id
  , p_cmp_tran_rec.trans_id
  , p_cmp_tran_rec.co_code
  , p_cmp_tran_rec.orgn_code
  , p_cmp_tran_rec.whse_code
  , p_cmp_tran_rec.lot_id
  , p_cmp_tran_rec.location
  , p_cmp_tran_rec.doc_id
  , p_cmp_tran_rec.doc_type
  , p_cmp_tran_rec.doc_line
  , p_cmp_tran_rec.line_type
  , p_cmp_tran_rec.reason_code
  , SYSDATE
  , p_cmp_tran_rec.trans_date
  , p_cmp_tran_rec.trans_qty
  , p_cmp_tran_rec.trans_qty2
  , p_cmp_tran_rec.qc_grade
  , p_cmp_tran_rec.lot_status
  , p_cmp_tran_rec.trans_stat
  , p_cmp_tran_rec.trans_um
  , p_cmp_tran_rec.trans_um2
  , p_cmp_tran_rec.user_id
  , p_cmp_tran_rec.gl_posted_ind
  , p_cmp_tran_rec.event_id
  , p_cmp_tran_rec.text_code
  , SYSDATE
  , p_cmp_tran_rec.user_id
  , p_cmp_tran_rec.user_id

  );
Line: 448

                             , 'insert_ic_tran_cmp'
                            );
Line: 453

END Insert_Ic_Tran_Cmp;
Line: 477

FUNCTION Update_Ic_Loct_Inv
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS

CURSOR location_inv IS
SELECT
  *
FROM
  ic_loct_inv
WHERE
    item_id   = p_cmp_tran_rec.item_id
AND whse_code = p_cmp_tran_rec.whse_code
AND lot_id    = p_cmp_tran_rec.lot_id
AND location  = p_cmp_tran_rec.location
FOR UPDATE;
Line: 508

    IF NOT Insert_Ic_Loct_Inv(p_cmp_tran_rec)
    THEN
      RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 516

  UPDATE ic_loct_inv
  SET
    loct_onhand      = loct_onhand + p_cmp_tran_rec.trans_qty
  , loct_onhand2     = loct_onhand2 + p_cmp_tran_rec.trans_qty2
  , last_updated_by  = p_cmp_tran_rec.user_id
  , last_update_date = SYSDATE
  WHERE
      item_id   = p_cmp_tran_rec.item_id
  AND whse_code = p_cmp_tran_rec.whse_code
  AND lot_id    = p_cmp_tran_rec.lot_id
  AND location  = p_cmp_tran_rec.location;
Line: 532

    UPDATE ic_loct_inv
    SET
      lot_status       = p_cmp_tran_rec.lot_status
    , last_updated_by  = p_cmp_tran_rec.user_id
    , last_update_date = SYSDATE
    WHERE
        item_id   = p_cmp_tran_rec.item_id
    AND whse_code = p_cmp_tran_rec.whse_code
    AND lot_id    = p_cmp_tran_rec.lot_id
    AND location  = p_cmp_tran_rec.location;
Line: 556

                             , 'Update_ic_loct_inv'
                            );
Line: 561

END Update_Ic_Loct_Inv;
Line: 585

FUNCTION Insert_Ic_Loct_Inv
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS

BEGIN

  INSERT INTO Ic_Loct_Inv
  ( item_id
  , whse_code
  , lot_id
  , location
  , loct_onhand
  , loct_onhand2
  , lot_status
  , qchold_res_code
  , delete_mark
  , text_code
  , last_updated_by
  , created_by
  , last_update_date
  , creation_date
  , last_update_login
  )
  VALUES
  ( p_cmp_tran_rec.item_id
  , p_cmp_tran_rec.whse_code
  , p_cmp_tran_rec.lot_id
  , p_cmp_tran_rec.location
  , 0
  , NULL
  , p_cmp_tran_rec.lot_status
  , NULL
  , 0
  , p_cmp_tran_rec.text_code
  , p_cmp_tran_rec.user_id
  , p_cmp_tran_rec.user_id
  , SYSDATE
  , SYSDATE
  , TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
  );
Line: 636

                             , 'insert_ic_loct_inv'
                            );
Line: 641

END Insert_Ic_Loct_Inv;
Line: 665

FUNCTION Update_Ic_Summ_Inv
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS
-- Cursors
CURSOR lot_status IS
SELECT
  *
FROM
  ic_lots_sts
WHERE
  lot_status = p_cmp_tran_rec.lot_status;
Line: 679

SELECT
  *
FROM
  ic_summ_inv
WHERE
    item_id   = p_cmp_tran_rec.item_id
AND whse_code = p_cmp_tran_rec.whse_code
AND ( qc_grade  = p_cmp_tran_rec.qc_grade
      OR qc_grade is NULL)
FOR UPDATE;
Line: 751

    SELECT gem5_summ_inv_id_s.nextval INTO l_summ_inv_id FROM dual;
Line: 759

    INSERT INTO ic_summ_inv
    ( summ_inv_id
    , item_id
    , whse_code
    , qc_grade
    , onhand_qty
    , onhand_qty2
    , onhand_prod_qty
    , onhand_prod_qty2
    , onhand_order_qty
    , onhand_order_qty2
    , onhand_ship_qty
    , onhand_ship_qty2
    , onpurch_qty
    , onpurch_qty2
    , onprod_qty
    , onprod_qty2
    , committedsales_qty
    , committedsales_qty2
    , committedprod_qty
    , committedprod_qty2
    , intransit_qty
    , intransit_qty2
    , last_updated_by
    , created_by
    , last_update_date
    , creation_date
    , last_update_login
    , program_application_id
    , program_id
    , program_update_date
    , request_id
    )
    VALUES
    ( l_summ_inv_id
    , p_cmp_tran_rec.item_id
    , p_cmp_tran_rec.whse_code
    , p_cmp_tran_rec.qc_grade
    , 0
    , NULL
    , 0
    , NULL
    , 0
    , NULL
    , 0
    , NULL
    , 0
    , NULL
    , 0
    , NULL
    , 0
    , NULL
    , 0
    , NULL
    , 0
    , NULL
    , p_cmp_tran_rec.user_id
    , p_cmp_tran_rec.user_id
    , SYSDATE
    , SYSDATE
    , TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
    , NULL
    , NULL
    , NULL
    , NULL
    );
Line: 829

  UPDATE ic_summ_inv
  SET
    onhand_qty        = onhand_qty        + l_qty
  , onhand_qty2       = onhand_qty2       + l_qty2
  , onhand_prod_qty   = onhand_prod_qty   + l_prod_qty
  , onhand_prod_qty2  = onhand_prod_qty2  + l_prod_qty2
  , onhand_order_qty  = onhand_order_qty  + l_order_qty
  , onhand_order_qty2 = onhand_order_qty2 + l_order_qty2
  , onhand_ship_qty   = onhand_ship_qty   + l_ship_qty
  , onhand_ship_qty2  = onhand_ship_qty2  + l_ship_qty2
  , last_updated_by   = p_cmp_tran_rec.user_id
  , last_update_date  = SYSDATE
  WHERE
      item_id   = p_cmp_tran_rec.item_id
  AND whse_code = p_cmp_tran_rec.whse_code
  AND (qc_grade  = p_cmp_tran_rec.qc_grade
        OR qc_grade is NULL);
Line: 859

                             , 'Update_ic_summ_inv'
                            );
Line: 865

END Update_Ic_Summ_Inv;
Line: 891

FUNCTION Update_Ic_Loct_Inv_Lot_Status
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS
-- Cursors
CURSOR location_inv IS
SELECT
  loct_onhand
, loct_onhand2
FROM
  ic_loct_inv
WHERE
    item_id   = p_cmp_tran_rec.item_id
AND whse_code = p_cmp_tran_rec.whse_code
AND lot_id    = p_cmp_tran_rec.lot_id
AND location  = p_cmp_tran_rec.location;
Line: 928

    IF NOT Insert_Ic_Loct_Inv(p_cmp_tran_rec)
    THEN
      RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 945

    UPDATE ic_loct_inv
    SET
      lot_status = l_cmp_tran_rec.lot_status
    , last_updated_by  = p_cmp_tran_rec.user_id
    , last_update_date = SYSDATE
    WHERE
	item_id   = p_cmp_tran_rec.item_id
    AND whse_code = p_cmp_tran_rec.whse_code
    AND lot_id    = p_cmp_tran_rec.lot_id
    AND location  = p_cmp_tran_rec.location;
Line: 958

  IF NOT Update_ic_summ_inv(l_cmp_tran_rec)
  THEN
    RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
Line: 975

                             , 'Update_ic_loct_inv_Lot_Status'
                            );
Line: 981

END Update_Ic_Loct_Inv_Lot_Status;
Line: 1006

FUNCTION Update_Ic_Summ_Inv_Qc_Grade
(p_cmp_tran_rec  IN cmp_tran_typ)
RETURN BOOLEAN
IS
-- Cursors
CURSOR location_inv IS
SELECT
  whse_code,
  lot_status
, SUM(loct_onhand)
, SUM(loct_onhand2)
FROM
  ic_loct_inv
WHERE
    item_id  = p_cmp_tran_rec.item_id
AND lot_id   = p_cmp_tran_rec.lot_id
GROUP BY
  whse_code,lot_status
ORDER BY
  whse_code;
Line: 1062

    IF NOT Update_ic_summ_inv(l_cmp_tran_rec)
    THEN
      CLOSE location_inv;
Line: 1084

                             , 'Update_ic_summ_inv_Qc_Grade'
                            );
Line: 1090

END Update_Ic_Summ_Inv_Qc_Grade;