[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT d.calendar_code, d.period_code
FROM cm_cldr_dtl d,
cm_cldr_hdr h,
sy_orgn_mst o,
ic_whse_mst w
WHERE
w.whse_code = cost_whse_v
AND w.orgn_code = o.orgn_code
AND o.co_code = h.co_code
AND h.cost_mthd_code = cost_mthd_code_v
AND h.calendar_code = d.calendar_code
AND d.start_date <= cost_date_v
AND d.end_date >= cost_date_v
AND h.delete_mark = 0
AND d.delete_mark = 0;
SELECT sum(cmpnt_cost)
FROM cm_cmpt_dtl
WHERE
item_id = item_id_v
AND whse_code = cost_whse_code_v
AND calendar_code = calendar_code_v
AND period_code = period_code_v
AND cost_mthd_code = cost_mthd_code_v
AND delete_mark = 0
GROUP BY
item_id, whse_code, calendar_code, period_code,
cost_mthd_code;
SELECT sum(loct_onhand)
FROM
ic_perd_bal perbal,
pmi_inv_calendar_v precal
WHERE (tr_date_vi-1) between precal.start_date and precal.end_date
and co_code_vi = precal.orgn_code
and precal.fiscal_year = perbal.fiscal_year
and precal.period = perbal.period
and perbal.item_id =item_id_vi
and perbal.whse_code=whse_code_vi
GROUP BY
perbal.fiscal_year, perbal.period, perbal.whse_code, perbal.item_id;
SELECT Meaning
FROM gem_lookups
WHERE
lookup_type = p_lookup_typ AND
lookup_code = p_lookup_cd AND
enabled_flag= 'Y' AND
start_date_active <= sysdate AND
(end_date_active IS NULL OR end_date_active >= sysdate);
SELECT item_id,
sum(loct_onhand)
FROM ic_loct_inv
WHERE p_item_id = item_id AND
(p_whse_code IS NULL OR (p_whse_code is not null and p_whse_code = whse_code)) AND
(p_location IS NULL OR (p_location is not null and p_location = location)) AND
(p_lot_id IS NULL OR (p_lot_id is not null and p_lot_id = lot_id))
group by item_id;
SELECT distinct line_type from fm_matl_dtl
WHERE item_id = item_id_vi
order by line_type desc;
SELECT (period_end_date + 1 - 1/(24 * 60 * 60)) INTO period_end_date_v
FROM
ic_cldr_dtl
WHERE
orgn_code = orgn_code_vi
AND fiscal_year = fiscal_year_vi
AND period = period_vi;
/* Need a cursor to select primary product from formula since
there can be blank lines? */
CURSOR primary_product_cur(formula_id_p fm_form_mst.formula_id%TYPE)
IS
SELECT item_id
FROM
fm_matl_dtl
WHERE
formula_id = formula_id_p
AND line_type = 1
ORDER BY
line_no;
/* Select fm_qty and fm_qty_um from costing formula */
CURSOR costing_pri_product_cur(formula_id_p fm_form_mst.formula_id%TYPE,
item_id_p ic_item_mst.item_id%TYPE)
IS
SELECT qty, item_um
FROM
fm_matl_dtl
WHERE
formula_id = formula_id_p
AND line_type = 1
AND item_id = item_id_p
ORDER BY
line_no;
/* Select primary product from fm_matl_dtl for the formula_id */
OPEN primary_product_cur(formula_id_vi);
SELECT plan_qty,item_um into pm_qty_v,pm_qty_um_v
FROM gme_material_details
WHERE batch_id = batch_id_vi
AND line_type =1
AND item_id = primary_product_v;
SELECT d.calendar_code, d.period_code
INTO cost_calendar_v, cost_period_v
FROM cm_cldr_dtl d,
cm_cldr_hdr h,
sy_orgn_mst o,
ic_whse_mst w
WHERE
w.whse_code = cost_whse_v
AND w.orgn_code = o.orgn_code
AND o.co_code = h.co_code
AND h.cost_mthd_code = cost_mthd_vi
AND h.calendar_code = d.calendar_code
AND d.start_date <= cost_date_v
AND d.end_date >= cost_date_v
AND h.delete_mark = 0
AND d.delete_mark = 0;
/* Now select the fmeff_id if available */
SELECT max(fmeff_id) INTO cost_fmeff_id_v
FROM cm_cmpt_dtl
WHERE item_id = primary_product_v
AND whse_code = cost_whse_v
AND calendar_code = cost_calendar_v
AND period_code = cost_period_v
AND cost_mthd_code = cost_mthd_vi
AND delete_mark = 0;
SELECT formula_id
INTO cost_formula_id_v
FROM fm_form_eff
WHERE fmeff_id = cost_fmeff_id_v;
SELECT item_um INTO pri_prod_um_v
FROM ic_item_mst
WHERE
item_id = primary_product_v;
SELECT formulaline_id, item_id, qty, 0 as scaled_qty, scale_type, item_um
FROM fm_matl_dtl
WHERE formula_id = formula_id_p
AND line_type in (1,2)
ORDER BY line_type;
SELECT formulaline_id, item_id, qty, 0 as scaled_qty, scale_type, item_um
FROM fm_matl_dtl
WHERE formula_id = formula_id_p
AND line_type = -1;
SELECT count(*) INTO num_fixed_items_v
FROM
fm_matl_dtl
WHERE
formula_id = cost_formula_id_v
AND scale_type = 0;
SELECT FND_PROFILE.VALUE('FM_YIELD_TYPE')
INTO fm_yield_type_v
FROM dual;
SELECT std_um INTO fm_yield_type_um_v
FROM
sy_uoms_typ
WHERE
um_type = fm_yield_type_v;
/* Select the fm_qty from the fm_matl_dtl table if the batch
formulaline_id is non-zero, otherwise fm_qty is 0 */
IF batch_formulaline_id_v <> 0 THEN
BEGIN
/* Should fetch only one row */
SELECT qty, line_type, scale_type, item_um
INTO fm_qty_v, fm_line_type_v, fm_scale_type_v,
fm_qty_um_v
FROM fm_matl_dtl
WHERE formula_id = formula_id_vi
AND item_id = batch_item_id_v
AND formulaline_id = batch_formulaline_id_v;
SELECT im.item_um
INTO prim_um_v
FROM ic_item_mst im
WHERE im.item_id = item_id_vi;
SELECT im.item_um,
um.um_type,
ut.std_um,
um.std_factor
INTO prim_um,
prim_um_type,
prim_std_um,
prim_stnd_factor
FROM sy_uoms_typ ut,sy_uoms_mst um, ic_item_mst im
WHERE im.item_id = item_id_vi
AND um.um_code = im.item_um
AND um.um_type = ut.um_type ;
SELECT um.um_type ,
ut.std_um ,
um.std_factor
INTO
from_um_type,
from_stnd_um,
from_stnd_factor
FROM sy_uoms_typ ut,sy_uoms_mst um
WHERE um.um_type = ut.um_type
and um.um_code = from_uom_vi ;
SELECT um.um_type ,
ut.std_um ,
um.std_factor
INTO
to_um_type,
to_stnd_um,
to_stnd_factor
FROM sy_uoms_typ ut,sy_uoms_mst um
WHERE um.um_type = ut.um_type
and um.um_code = to_uom_vi ;
SELECT type_factor
INTO type_cnv_factor_from
FROM ic_item_cnv
WHERE item_id = item_id_vi
and lot_id = lot_id_vi
and um_type = from_um_type
;
SELECT type_factor
INTO type_cnv_factor_to
FROM ic_item_cnv
WHERE item_id = item_id_vi
and lot_id = lot_id_vi
and um_type = to_um_type
;
SELECT type_factor
INTO type_cnv_factor_from
FROM ic_item_cnv
WHERE item_id = item_id_vi
and lot_id = lot_id_vi
and um_type = from_um_type
;
SELECT type_factor
INTO type_cnv_factor_to
FROM ic_item_cnv
WHERE item_id = item_id_vi
and lot_id = lot_id_vi
and um_type = to_um_type
;
SELECT cost_whse_code
FROM cm_whse_asc
WHERE whse_code = inv_whse_v
AND eff_start_date <= asc_date_v
AND eff_end_date >= asc_date_v
AND delete_mark = 0;
SELECT base_currency_code
FROM
gl_plcy_mst p,
sy_orgn_mst o,
ic_whse_mst w
WHERE
w.whse_code = whse_code_vi
AND w.orgn_code = o.orgn_code
AND o.co_code = p.co_code ;