[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT /*+ append parallel(OPI_DBI_INV_CCA_STG) */
INTO opi_dbi_inv_cca_stg (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
positive_adjustment_qty,
negative_adjustment_qty,
item_unit_cost,
item_adj_unit_cost,
hit_miss_pos,
hit_miss_neg,
exact_match)
SELECT/*+ full(ich) use_hash(ica,ich,irm,iwi,icd,icmb,iwm,msi,gl_join)
parallel(ica) parallel(ich) parallel(irm) parallel(iwi) parallel(icd)
parallel(icmb) parallel(iwm) parallel(msi) parallel(gl_join) */
iwm.mtl_organization_id,
msi.inventory_item_id,
-1,
nvl(iwi.whse_abccode,to_char(-1)),
to_char(-1),
ica.cycle_id||'-'||ica.seq_no,
C_PRER12_SOURCE,
trunc(ich.last_update_date) approval_date,
msi.primary_uom_code,
ica.frozen_qty1,
CASE WHEN ica.var_qty1 > 0 THEN
ica.var_qty1
ELSE 0
END,
CASE WHEN ica.var_qty1 < 0 THEN
-1*ica.var_qty1
ELSE 0
END,
opi_dbi_inv_cca_opm_pkg.get_unit_cost(ica.item_id,ica.whse_code,iwm.orgn_code,trunc(icd.creation_date)),
abs(gl_join.amount_base/ica.var_qty1),
irm.percent_warn,
irm.percent_warn,
decode(ica.var_qty1,0,1,0)
FROM
ic_cycl_adt ica,
ic_cycl_hdr ich,
ic_rank_mst irm,
ic_whse_inv iwi,
ic_cycl_dtl icd,
ic_item_mst_b icmb,
ic_whse_mst iwm,
mtl_system_items_b msi,
(SELECT /*+ use_hash(gsl,itc) parallel(gsl) parallel(itc) */
gsl.amount_base amount_base,
itc.whse_code whse_code,
itc. item_id item_id,
itc.lot_id lot_id,
itc.location location,
itc.doc_id doc_id,
itc.line_id line_id
FROM
gl_subr_led gsl,
ic_tran_cmp itc
WHERE
gsl.line_id = itc.line_id and
gsl.doc_id = itc.doc_id and
gsl.doc_type = 'PICY' and
gsl.acct_ttl_type = 1500 and
gsl.creation_date >= s_global_start_date and
itc.doc_type = gsl.doc_type and
itc.gl_posted_ind = 1 and
itc.trans_qty <> 0
) gl_join
WHERE
gl_join.item_id = ica.item_id and
gl_join.whse_code = ica.whse_code and
gl_join.lot_id = ica.lot_id and
gl_join.location = ica.location and
gl_join.doc_id = ica.cycle_id and
ica.cycle_id = ich.cycle_id and
ica.cycle_id = icd.cycle_id and
ica.whse_code = icd.whse_code and
ica.item_id = icd.item_id and
ica.lot_id = icd.lot_id and
ica.location = icd.location and
ica.count_no = icd.count_no and
ica.item_id = iwi.item_id and
ica.whse_code = iwi.whse_code and
ica.whse_code = iwm.whse_code and
ica.item_id = icmb.item_id and
irm.whse_code(+) = iwi.whse_code and
irm.abc_code(+) = iwi.whse_abccode and
ich.last_update_date >= s_global_start_date and
ich.delete_mark = 1 and
msi.segment1 = icmb.item_no and
msi.organization_id = iwm.mtl_organization_id;
the sysdate inserted in the log. Item Count date is checked for > '1-Jan-1970'
to eliminate all cycle count info for items for which no count qty was entered in
the cycle count form and the cycle count posted.
R12 Changes
----------
The upper bounds will be removed.
History:
Date Author Action
07/04/04 Vedhanarayanan G Defined procedure.
07/04/05 Vishal Gupta Refer R12 Changes above.
*/
PROCEDURE extract_exact_matches_init
IS
l_proc_name VARCHAR2 (40);
INSERT /*+ append parallel(OPI_DBI_INV_CCA_STG) */
INTO opi_dbi_inv_cca_stg (
organization_id,
inventory_item_id,
cycle_count_header_id,
abc_class_id,
subinventory_code,
cycle_count_entry_id,
source,
approval_date,
uom_code,
system_inventory_qty,
positive_adjustment_qty,
negative_adjustment_qty,
item_unit_cost,
item_adj_unit_cost,
hit_miss_pos,
hit_miss_neg,
exact_match)
SELECT /*+ full(ich) use_hash(ica, ich, irm, iwi, icd, icmb, iwm, msi)
parallel(ica) parallel(ich) parallel(irm) parallel(iwi) parallel(icd)
parallel(icmb) parallel(iwm) parallel(msi) */
iwm.mtl_organization_id,
msi.inventory_item_id,
-1,
nvl(iwi.whse_abccode,to_char(-1)),
to_char(-1),
ica.cycle_id||'-'||ica.seq_no,
C_PRER12_SOURCE,
trunc(ich.last_update_date) approval_date,
msi.primary_uom_code,
ica.frozen_qty1,
0,
0,
opi_dbi_inv_cca_opm_pkg.get_unit_cost(ica.item_id,ica.whse_code,iwm.orgn_code,
trunc(icd.creation_date)),
0,
NULL,
NULL,
1
FROM
ic_cycl_adt ica,
ic_cycl_hdr ich,
ic_rank_mst irm,
ic_whse_inv iwi,
ic_whse_mst iwm,
ic_cycl_dtl icd,
ic_item_mst_b icmb,
mtl_system_items_b msi
WHERE
ica.cycle_id = ich.cycle_id and
ica.cycle_id = icd.cycle_id and
ica.item_id = icd.item_id and
ica.whse_code = icd.whse_code and
ica.location = icd.location and
ica.count_no = icd.count_no and
ica.lot_id = icd.lot_id and
ica.item_id = iwi.item_id and
ica.whse_code = iwi.whse_code and
ica.whse_code = iwm.whse_code and
ica.item_id = icmb.item_id and
ica.var_qty1 = 0 and
ica.item_count_dt > g_CY_ZERO_DATE and
ich.last_update_date >= s_global_start_date and
irm.whse_code(+) = iwi.whse_code and
irm.abc_code(+) = iwi.whse_abccode and
ich.delete_mark = 1 and
msi.segment1 = icmb.item_no and
msi.organization_id = iwm.mtl_organization_id;