The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION update_quantity_transaction
(p_cmp_tran_rec IN cmp_tran_typ)
RETURN BOOLEAN
IS
l_cmp_tran_rec cmp_tran_typ;
SELECT gem5_trans_id_s.nextval INTO l_cmp_tran_rec.trans_id FROM dual;
IF NOT Update_movement(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
IF NOT Update_lot_status(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
IF NOT Update_QC_grade(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
, 'Update_Quantity_Transaction'
);
END Update_Quantity_Transaction;
FUNCTION Update_Movement
(p_cmp_tran_rec IN cmp_tran_typ)
RETURN BOOLEAN
IS
l_cmp_tran_rec cmp_tran_typ;
IF NOT insert_ic_tran_cmp(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
IF NOT Update_ic_loct_inv(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
IF NOT Update_ic_summ_inv(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
, 'Update_Movement'
);
END Update_Movement;
FUNCTION update_lot_status
(p_cmp_tran_rec IN cmp_tran_typ)
RETURN BOOLEAN
IS
l_cmp_tran_rec cmp_tran_typ;
IF NOT insert_ic_tran_cmp(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
IF NOT Update_ic_loct_inv_LOT_STATUS(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
, 'Update_Lot_Status'
);
END Update_Lot_Status;
FUNCTION Update_Qc_Grade
(p_cmp_tran_rec IN cmp_tran_typ)
RETURN BOOLEAN
IS
l_cmp_tran_rec cmp_tran_typ;
IF NOT insert_ic_tran_cmp(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
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;
IF NOT Update_ic_summ_inv_QC_GRADE(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
, 'Update_Qc_Grade'
);
END Update_Qc_Grade;
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
);
, 'insert_ic_tran_cmp'
);
END Insert_Ic_Tran_Cmp;
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;
IF NOT Insert_Ic_Loct_Inv(p_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
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;
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;
, 'Update_ic_loct_inv'
);
END Update_Ic_Loct_Inv;
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'))
);
, 'insert_ic_loct_inv'
);
END Insert_Ic_Loct_Inv;
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;
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;
SELECT gem5_summ_inv_id_s.nextval INTO l_summ_inv_id FROM dual;
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
);
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);
, 'Update_ic_summ_inv'
);
END Update_Ic_Summ_Inv;
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;
IF NOT Insert_Ic_Loct_Inv(p_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
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;
IF NOT Update_ic_summ_inv(l_cmp_tran_rec)
THEN
RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
, 'Update_ic_loct_inv_Lot_Status'
);
END Update_Ic_Loct_Inv_Lot_Status;
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;
IF NOT Update_ic_summ_inv(l_cmp_tran_rec)
THEN
CLOSE location_inv;
, 'Update_ic_summ_inv_Qc_Grade'
);
END Update_Ic_Summ_Inv_Qc_Grade;