[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(begin_date)
FROM ic_cldr_hdr
WHERE begin_date <= trans_date
AND delete_mark = 0
AND UPPER(orgn_code) = UPPER(lp_co_code);
SELECT fiscal_year
FROM ic_cldr_hdr
WHERE begin_date = l_begin_date
AND delete_mark = 0
AND orgn_code = UPPER(lp_co_code);
SELECT MIN(Period_end_date)
FROM ic_cldr_dtl
WHERE TRUNC(period_end_date, 'DD') >=
TRUNC(trans_date, 'DD')
AND fiscal_year = l_fiscal_yr
AND UPPER(orgn_code) = UPPER(lp_co_code);
SELECT closed_period_ind, period
FROM ic_cldr_dtl
WHERE fiscal_year = l_fiscal_yr
AND period_end_date = l_period_date
AND UPPER(orgn_code) = UPPER(lp_co_code);
SELECT whse_code
FROM ic_whse_sts
WHERE whse_code = UPPER(pwhse_code)
AND fiscal_year = l_fiscal_yr
AND period = l_period
AND close_whse_ind <> 3;
SELECT whse_code
FROM ic_whse_sts
WHERE whse_code = UPPER(pwhse_code)
AND fiscal_year = l_fiscal_yr
AND period = l_period;
SELECT whse_code
FROM ic_whse_mst
where whse_code = UPPER(pwhse_code)
AND delete_mark = 0;
SELECT orgn_code
FROM ic_whse_mst
where whse_code = UPPER(pwhse_code);
delete_ic_perd_bal
DESCRIPTION:
This PL/SQL function is responsible for
deleting rows from the ic_perd_bal in conjuction
with the rerunning of a preliminary close.
SYNOPSIS:
iret := GMICCAL.delete_ic_perd_bal(pfiscal_yr, pperiod,
pwhse_code);
delete_ic_perd_bal was missing a return value.
added return 0 to function.
============================================= */
FUNCTION delete_ic_perd_bal(pfiscal_year VARCHAR2,
pperiod NUMBER,
pwhse_code VARCHAR2)
RETURN NUMBER IS
/* ========================================*/
BEGIN
DELETE from ic_perd_bal
WHERE fiscal_year = UPPER(pfiscal_year)
AND period = pperiod
AND whse_code = UPPER(pwhse_code);
there were no rows to delete dude!
=================================== */
RETURN 0;
END delete_ic_perd_bal;
insert_ic_perd_bal
DESCRIPTION:
This PL/SQL function is responsible for
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.
SYNOPSIS:
iret := GMICCAL.insert_ic_perd_bal(pwhse_code);
>= 0 The number of rows inserted.
============================================= */
FUNCTION insert_ic_perd_bal(pfiscal_year VARCHAR2,
pper_id NUMBER,
pperiod NUMBER,
pwhse_code VARCHAR2,
pop_code NUMBER)
RETURN NUMBER IS
/* ========================================*/
BEGIN
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)
SELECT gmi_perd_bal_id_s.nextval, 0, pper_id, pfiscal_year,
pperiod, item_id, lot_id,
whse_code, location, ROUND(loct_onhand, 9),
ROUND(loct_onhand2, 9), 0,0,0,0,0,
lot_status, qchold_res_code, SYSDATE,
SYSDATE, pop_code, SYSDATE, pop_code
FROM ic_loct_inv
WHERE whse_code = pwhse_code
AND delete_mark = 0 ;
END insert_ic_perd_bal;
SELECT reason_code
FROM sy_reas_cds
WHERE flow_type = 0
AND delete_mark = 0
AND reason_code = v_reason_code;
SELECT 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
WHERE whse_code = UPPER(pwhse_code)
AND trans_date >= pprd_start_date
AND creation_date <= plog_end_date
AND trans_qty <> 0
AND completed_ind = 1
AND delete_mark = 0
UNION
SELECT 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
WHERE whse_code = UPPER(pwhse_code)
AND trans_date >= pprd_start_date
AND creation_date <= plog_end_date
AND trans_qty <> 0
AND doc_type NOT IN ('STSI', 'GRDI',
'STSR', 'GRDR')
ORDER BY 1,2,3,4;
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, --bug#2230683
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, SYSDATE, SYSDATE, pop_code,
SYSDATE,pop_code, NULL);
whse_status_update
DESCRIPTION:
This PL/SQL function is responsible for
updating the warehouse status
as the result of either an inventory calendar
preliminary or final close of a warehouse.
SYNOPSIS:
iret := GMICCAL.whse_status_update(pwhse_code,
pperiod, pclose_type);
-30 Update warehouse status error.
HISTORY:
Sastry 05/17/2002 BUG#2356476
Modified the Update statement to update the columns
last_updated_by,last_update_date and last_update_login.
============================================= */
FUNCTION whse_status_update(pwhse_code VARCHAR2,
pfiscal_year VARCHAR2,
pperiod NUMBER,
pclose_type NUMBER) RETURN NUMBER IS
/* ================================================ */
BEGIN
-- BEGIN BUG#2356476 Sastry
-- Also update last_updated_by,last_update_date and last_update_login.
UPDATE ic_whse_sts
SET log_end_date = SYSDATE,
close_whse_ind = pclose_type,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE fiscal_year = pfiscal_year
AND period = pperiod
AND whse_code = UPPER(pwhse_code);
RETURN INVCAL_WHSESTS_UPDATE_ERR;
END whse_status_update;
period_status_update
DESCRIPTION:
This PL/SQL function is responsible for
updating the Inventory Calendar Period status
as the result of either an inventory calendar
preliminary or final close of a warehouse.
SYNOPSIS:
iret := GMICCAL.period_status_update(pco_code,
pfiscal_year, pperiod, pclose_type);
-31 Update period status error.
============================================= */
FUNCTION period_status_update(pco_code VARCHAR2,
pfiscal_year VARCHAR2,
pperiod NUMBER) RETURN NUMBER IS
/* Local Variables:
================ */
l_whse_code whse_type := NULL;
SELECT s.whse_code
FROM ic_whse_sts s, ic_whse_mst w,
sy_orgn_mst o
WHERE o.co_code = UPPER(pco_code)
AND w.orgn_code = o.orgn_code
AND s.whse_code = w.whse_code
AND s.fiscal_year = pfiscal_year
AND s.period = pperiod
AND s.close_whse_ind <> 3;
UPDATE ic_cldr_dtl
SET closed_period_ind = l_close_type,
last_update_date = SYSDATE
WHERE orgn_code = pco_code
AND fiscal_year = pfiscal_year
AND period = pperiod;
RETURN INVCAL_PRDSTS_UPDATE_ERR;
END period_status_update;
SELECT orgn_code
FROM sy_orgn_mst
WHERE orgn_code = UPPER(porgn_code)
AND delete_mark = 0;
SELECT UPPER(co_code)
FROM sy_orgn_mst
WHERE orgn_code = UPPER(porgn_code)
AND delete_mark = 0;