[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 04/15/2002 Venkat Ramana Bug#2317115 Modified the select statement |
| in the function co_orgn_whse_valid. |
| 05/06/2002 Sastry/Ravi BUG#2354190/2354168 Modified the code in |
| Procedure Validate_Inventory_Posting. |
| 30/Apr/2002 B.Ravishanker Bug#2340824 Modified code in the function |
| whse_locations_valid and |
| Validate_Inventory_Posting procedure to |
| display the correct error message when the |
| to_location is invalid (Marked for Purged).|
| 07/02/2002 Jalaj Srivastava Bug 2483656
| Modified to enable creation of journals |
| through inventory APIs
| 11/11/2002 Joe DiIorio Bug 2643440
| 11.5.1J - added nocopy. |
| 21/11/2002 Sastry Bug 2665243 Modified elsif condition |
| in procedure validate_inventory_posting. |
| 09/05/2003 Sastry BUG 2861715 Modified code in Procedure |
| Validate_Inventory_Posting. |
| 10/09/2003 James Bernard Bug 3127824 Modified code in |
| validate_inventory_posting so that user is |
| allowed to move qty from source whse even |
| when on hand qty is NULL.Also modifed code |
| so that negative qty not allowed message is|
| displayed when onhand qty is going negative|
| for IC$ALLOWNEGINV is zero. |
| 09/10/2003 James Bernard Bug 3171345 Added code to set lot status of|
| source whse to default lot status if the |
| destination whse also does not have any |
| lot status. If destination whse has a lot |
| status then the source whse will have lot |
| status of the destination whse. This is |
| is done if the onhand qty in source whse |
| is NULL while doing TRN transactions. |
+==========================================================================+
Body end of comments
*/
/* Global variables */
G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMIVQTY';
SELECT
count(*)
FROM
ic_adjs_jnl a, ic_jrnl_mst j
WHERE
a.item_id = p_item_id AND
a.lot_id = p_lot_id AND
a.whse_code = p_whse_code AND
a.location = p_location AND
a.journal_id = j.journal_id AND
j.posted_ind = 0 AND
j.delete_mark = 0 AND
p_lot_status <> a.lot_status;
SELECT
count(*)
FROM
ic_adjs_jnl a, ic_jrnl_mst j
WHERE
a.item_id = p_item_id AND
a.lot_id = p_lot_id AND
a.journal_id = j.journal_id AND
j.posted_ind = 0 AND
j.delete_mark = 0 AND
p_qc_grade <> a.qc_grade;
SELECT 1
INTO l_count
FROM sy_orgn_mst co,
sy_orgn_mst org
WHERE co.orgn_code = p_qty_rec.co_code AND
co.delete_mark = 0 AND
org.orgn_code = p_qty_rec.orgn_code AND
org.co_code = p_qty_rec.co_code AND
org.delete_mark=0;
SELECT fw.loct_ctl, tw.loct_ctl
INTO x_from_loct_ctl, x_to_loct_ctl
FROM ic_whse_mst fw,
ic_whse_mst tw,
sy_orgn_mst fo,
sy_orgn_mst toc
WHERE fw.whse_code = l_from_whse AND
tw.whse_code = l_to_whse AND
fo.orgn_code = fw.orgn_code AND
toc.orgn_code = tw.orgn_code AND
fw.delete_mark = 0 AND
tw.delete_mark = 0 AND
fo.delete_mark = 0 AND
toc.delete_mark = 0;
SELECT 1
INTO l_count
FROM ic_loct_mst
WHERE whse_code = l_from_whse
AND location = l_from_location
AND delete_mark = 0;
SELECT 1
INTO l_count
FROM ic_loct_mst
WHERE whse_code = l_to_whse
AND location = l_to_location
AND delete_mark = 0;
SELECT sum(loct_onhand),sum(loct_onhand2)
FROM ic_loct_inv
WHERE item_id = p_ic_item_mst_row.item_id
AND lot_id = p_ic_lots_mst_row.lot_id;
SELECT lot_status
FROM ic_item_mst
WHERE item_id = p_ic_item_mst_row.item_id;
OR p_ic_item_mst_row.delete_mark = 1
OR p_ic_item_mst_row.noninv_ind = 1
OR p_ic_item_mst_row.inactive_ind =1 AND GMIGUTL.IC$API_ALLOW_INACTIVE=0
THEN
FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');
OR 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');
ic_loct_inv using GMIVDBL.ic_loct_inv_select which uses a
rownum? to restrict rows to 1.
************************************************************* */
OPEN Cur_get_onhand_for_grade;
SELECT nvl(sum(a.qty),0)
INTO l_other_lines_qty
FROM ic_jrnl_mst j, ic_adjs_jnl a
WHERE j.orgn_code = l_qty_rec.orgn_code
AND j.journal_no = nvl(l_qty_rec.journal_no,GMIGAPI.prev_journal_no)
AND a.journal_id = j.journal_id
AND a.line_type <> -1
AND a.item_id = p_ic_item_mst_row.item_id
AND a.lot_id = nvl(p_ic_lots_mst_row.lot_id,0)
AND a.whse_code = l_qty_rec.from_whse_code
AND a.location = nvl(l_qty_rec.from_location,GMIGUTL.IC$DEFAULT_LOCT);
SELECT co_code INTO l_from_whse_co_code
FROM sy_orgn_mst
WHERE orgn_code = (SELECT orgn_code
FROM ic_whse_mst
WHERE whse_code = l_qty_rec.from_whse_code);
x_ic_jrnl_mst_row.last_update_date := SYSDATE;
x_ic_jrnl_mst_row.last_updated_by := l_user_id;
x_ic_jrnl_mst_row.delete_mark := 0;
x_ic_jrnl_mst_row.last_update_login := NULL;
x_ic_jrnl_mst_row.program_update_date := NULL;
x_ic_adjs_jnl_row1.last_update_date := SYSDATE;
x_ic_adjs_jnl_row1.last_updated_by := l_user_id;
x_ic_adjs_jnl_row1.last_update_login := NULL;
x_ic_adjs_jnl_row1.program_update_date := NULL;