[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select whse_code from
gmi_clos_warehouses
where inventory_close_id = P_sequence
order by whse_code;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'No Warehouse selected for Close');
Delete all rows from the
ic_perd_bal table for this warehouse.
**********************************************************/
FND_MESSAGE.set_name('GMI', 'ICCAL_DELETE_PERD_MSG');
l_iret := GMICCAL.delete_ic_perd_bal(P_fiscal_year, P_period, x_whse_code);
FND_MESSAGE.SET_NAME('GMI','IC_PERD_BAL_DELETE_ERR');
Insert into ic_perd_bal for this warehouse.
*********************************************************/
FND_MESSAGE.set_name('GMI', 'ICCAL_CREATE_PERD_MSG');
Update the status of the warehouse
********************************************************/
/* Debugging statements - Bug 3684980 */
FND_MESSAGE.set_name('GMI', 'ICCAL_UPDATEWHSE_STS_MSG');
l_iret := GMICCAL.whse_status_update(x_whse_code, P_fiscal_year,
P_period, P_close_ind);
FND_MESSAGE.SET_NAME('GMI','IC_WHSE_STATUS_UPDATE_ERR');
FND_MESSAGE.set_name('GMI', 'ICCAL_UPDATEWHSE_STS_MSG');
DELETE gmi_clos_warehouses
where inventory_close_id = P_sequence AND
whse_code = x_whse_code;
FND_MESSAGE.SET_NAME('GMI','IC_DELETE_CLOSE_ERROR');
Update the Status of the Period if required.
*********************************************/
/* Debugging statements - Bug 3684980 */
FND_MESSAGE.set_name('GMI', 'ICCAL_UPDATE_PERIOD_MSG');
l_iret := GMICCAL.period_status_update(P_orgn_code, p_fiscal_year,
P_period);
FND_MESSAGE.SET_NAME('GMI','IC_WHSE_STATUS_UPDATE_ERR');
FND_MESSAGE.set_name('GMI', 'ICCAL_UPDATE_PERIOD_MSG');
DELETE gmi_clos_warehouses
where inventory_close_id = P_sequence AND
whse_code = x_whse_code;
FND_MESSAGE.SET_NAME('GMI','IC_DELETE_CLOSE_ERROR');
DELETE gmi_clos_warehouses
where inventory_close_id = P_sequence AND
whse_code = x_whse_code;
FND_MESSAGE.SET_NAME('GMI','IC_DELETE_CLOSE_ERROR');
DELETE gmi_clos_warehouses
where inventory_close_id = P_sequence AND
whse_code = x_whse_code;
FND_MESSAGE.SET_NAME('GMI','IC_DELETE_CLOSE_ERROR');
inserting rows from the ic_perd_bal in conjuction
with the running of a preliminary or Final close.
This is the initial seeding of this table.
Then it calculates the usage and yield
SYNOPSIS:
icprded1_process.inventory_close(pfiscal_year,
pprd_id,
pperiod,
pwhse_code,
pop_code,
pprd_start_date,
pprd_end_date);
then no transactions will be inserted in ic_tran_cmp table.
It is not required to use doc_type in the where clause
because for TRNI and TRNR also based on the value of profile
option IC$MOVEDIFFSTAT the status might change.
======================================================*/
PROCEDURE inventory_close(pfiscal_year VARCHAR2,
pprd_id NUMBER,
pperiod NUMBER,
pwhse_code VARCHAR2,
pop_code NUMBER,
pprd_start_date DATE,
pprd_end_date DATE) IS
/* ================================================
Local Variable definitions and initialization:
===============================================*/
l_item_id item_srg_type := 0;
SELECT reason_code
FROM sy_reas_cds
WHERE flow_type = 0
AND delete_mark = 0
AND reason_code = v_reason_code;
SELECT v.item_id ,v.lot_id ,
whse_code ,location ,ROUND(loct_onhand,9) onhand,
ROUND(loct_onhand2,9) onhand2,
v.lot_status,v.qchold_res_code
from ic_loct_inv v, ic_item_mst_b m
WHERE whse_code = pwhse_code
AND noninv_ind = 0
AND v.item_id = m.item_id
AND v.delete_mark = 0
AND (nvl(v.loct_onhand,0) <> 0 OR nvl(v.loct_onhand2,0) <> 0);
SELECT lot_status
FROM ic_adjs_jnl
WHERE line_id = (select max(line_id) from ic_adjs_jnl
WHERE item_id = x_item_id
AND lot_id = x_lot_id
AND location = x_location
AND whse_code = x_whse_code
AND completed_ind = 1
AND Trunc(doc_date) BETWEEN Trunc(pprd_start_date)
AND Trunc(pprd_end_date));
SELECT p.item_id, lot_id, whse_code,
location, doc_type, line_type,
reason_code, trans_date, trans_id,
trans_qty, trans_qty2
FROM ic_tran_pnd p, ic_item_mst_b m
WHERE whse_code = uwhse_code
AND trans_date >= pprd_start_date
AND p.creation_date <= l_log_end_date
AND trans_qty <> 0
AND completed_ind = 1
AND p.delete_mark = 0
AND p.item_id = m.item_id
AND noninv_ind = 0
UNION ALL
SELECT c.item_id, lot_id, whse_code,
location, doc_type, line_type,
reason_code, trans_date, trans_id,
trans_qty, trans_qty2
FROM ic_tran_cmp c, ic_item_mst_b m
WHERE whse_code = uwhse_code
AND trans_date >= pprd_start_date
AND c.creation_date <= l_log_end_date
AND trans_qty <> 0
AND c.item_id = m.item_id
AND noninv_ind = 0
AND doc_type NOT IN ('STSI', 'GRDI',
'STSR', 'GRDR')
ORDER BY 1,2,3,4;
select sysdate into l_log_end_date from dual;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' Before insert into ic_perd_bal - '||
to_char(x_cur_time, 'hh24:mi:ss') );
INSERT INTO ic_perd_bal
(perd_bal_id, gl_posted_ind, period_id, fiscal_year, --bug#2230683
period, item_id, lot_id,
whse_code, location, loct_onhand, loct_onhand2,
loct_usage, loct_usage2, loct_yield, loct_yield2,
loct_value, lot_status, qchold_res_code,
log_end_date, creation_date, created_by, last_update_date,
last_updated_by)
VALUES(gmi_perd_bal_id_s.nextval,0, pprd_id, pfiscal_year, pperiod, get_loct_onhand_rec.item_id, get_loct_onhand_rec.lot_id,
get_loct_onhand_rec.whse_code, get_loct_onhand_rec.location, get_loct_onhand_rec.onhand,
get_loct_onhand_rec.onhand2, 0,0,0,0,0,
x_lot_status, get_loct_onhand_rec.qchold_res_code, l_log_end_date,
SYSDATE, pop_code, SYSDATE, pop_code);
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' After insert into ic_perd_bal - '||
to_char(x_cur_time, 'hh24:mi:ss') );
let's grab what we accumulated and update
the perpetual balances for this item, lot,
and location.
==========================================*/
UPDATE ic_perd_bal
SET loct_onhand = loct_onhand - ROUND(l_delta_qty, 9),
loct_onhand2 = loct_onhand2 - ROUND(l_delta_qty2, 9),
loct_usage = ROUND(l_usage_qty, 9),
loct_usage2 = ROUND(l_usage_qty2, 9),
loct_yield = ROUND(l_yield_qty, 9),
loct_yield2 = ROUND(l_yield_qty2, 9),
last_update_date = SYSDATE,
last_updated_by = pop_code
WHERE period_id = pprd_id
AND lot_id = l_prev_lot_id
AND whse_code = pwhse_code
AND location = l_prev_location
AND item_id = l_prev_item_id
AND fiscal_year = pfiscal_year
AND period = pperiod;
row does not exist so we have to insert it!
============================================*/
INSERT INTO ic_perd_bal
(perd_bal_id, gl_posted_ind, period_id, lot_id, --bug#2230683
whse_code, location, item_id,
fiscal_year, period, loct_onhand, loct_onhand2,
loct_usage, loct_usage2, loct_yield, loct_yield2,
loct_value, lot_status, qchold_res_code,
log_end_date, creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
VALUES
(gmi_perd_bal_id_s.nextval, 0, pprd_id, l_prev_lot_id,
pwhse_code, l_prev_location,
l_prev_item_id, pfiscal_year, pperiod,
ROUND((0 - l_delta_qty), 9),
ROUND((0 - l_delta_qty2), 9),
ROUND(l_usage_qty, 9),
ROUND(l_usage_qty2, 9),
ROUND(l_yield_qty, 9),
ROUND(l_yield_qty2, 9),
0, NULL, NULL, l_log_end_date, SYSDATE, pop_code,
SYSDATE,pop_code, NULL);