[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select default_eam_class
into V_EAM_CLASS
from wip_eam_parameters
where organization_id = X_ORG_ID;
select primary_cost_method
into V_COST_METHOD
from mtl_parameters
where
organization_id = X_ORG_ID;
select wdcac.std_discrete_class, wdcac.repetitive_assy_class
into V_DISC_CLASS, V_REP_CLASS
from mtl_default_category_sets mdcs, mtl_item_categories mic,
wip_def_cat_acc_classes wdcac
where
mdcs.functional_area_id = V_PRODUCT_LINE and
mdcs.category_set_id = mic.category_set_id and
mic.organization_id = X_ORG_ID and
mic.inventory_item_id = X_ITEM_ID and
wdcac.organization_id = X_ORG_ID and
mic.category_id = wdcac.category_id and
wdcac.cost_group_id IS NULL;
select NVL(costing_group_id,1), wip_acct_class_code
into V_COST_GROUP_ID, V_PRJ_DEF_CLASS
from mrp_project_parameters mpp
where
mpp.project_id = X_PROJECT_ID and
mpp.organization_id = X_ORG_ID;
select DEFAULT_COST_GROUP_ID
into V_COST_GROUP_ID
from mtl_parameters
where organization_id = X_ORG_ID;
select NVL(costing_group_id,1), wip_acct_class_code
into V_COST_GROUP_ID, V_PRJ_DEF_CLASS
from mrp_project_parameters mpp
where
mpp.project_id = X_PROJECT_ID and
mpp.organization_id = X_ORG_ID;
Replacing hard-coding of COST_GROUP_ID with a select statement
that gets Cost Group ID from mtl_parameters. */
/* V_COST_GROUP_ID := 1; */
select default_cost_group_id
into V_COST_GROUP_ID
from mtl_parameters
where organization_id = X_ORG_ID;
select wdcac.std_discrete_class
into V_DISC_CLASS
from mtl_default_category_sets mdcs, mtl_item_categories mic,
wip_def_cat_acc_classes wdcac
where
mdcs.functional_area_id = V_PRODUCT_LINE and
mdcs.category_set_id = mic.category_set_id and
mic.organization_id = X_ORG_ID and
mic.inventory_item_id = X_ITEM_ID and
wdcac.organization_id = X_ORG_ID and
mic.category_id = wdcac.category_id and
wdcac.cost_group_id = V_COST_GROUP_ID;
SELECT wp.DEFAULT_DISCRETE_CLASS
INTO V_DISC_CLASS
FROM WIP_PARAMETERS wp
WHERE wp.ORGANIZATION_ID = X_ORG_ID;
select nvl(wac.disable_date, SYSDATE + 1) into V_DISABLE_DATE
from wip_accounting_classes wac
where
wac.organization_id = X_ORG_ID and
wac.class_type = DECODE(X_ENTITY_TYPE, 1, WIP_CONSTANTS.DISC_CLASS,
2, WIP_CONSTANTS.REP_CLASS,
4, WIP_CONSTANTS.DISC_CLASS,
6, WIP_CONSTANTS.DISC_CLASS) and
wac.class_code = X_CLASS;
select nvl(cwac.disable_date, SYSDATE + 1) into V_DISABLE_DATE
from cst_cg_wip_acct_classes cwac
where
cwac.organization_id = X_ORG_ID and
cwac.cost_group_id = X_COST_GROUP_ID and
cwac.class_code = X_CLASS;
select PRIMARY_COST_METHOD, PROJECT_REFERENCE_ENABLED
into v_primary_cost_method, v_project_reference_enabled
from mtl_parameters mp
where
mp.organization_id = X_ORG_ID;
select distinct class_code
into dummy
from cst_cg_wip_acct_classes ccwac
where
ccwac.organization_id = X_ORG_ID and
ccwac.class_code = X_CLASS and
nvl(ccwac.disable_date, SYSDATE + 1) > SYSDATE;
select count(*) into x_bom_rev_exists
from bom_bill_of_materials
where assembly_item_id = p_item_id
and organization_id = p_Org_id
and alternate_bom_designator is null ;
select revision_qty_control_code into x_rev_exists
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_Org_id ;
select count(*) into x_rtg_exists
from mtl_rtg_item_revisions
where inventory_item_id = p_item_id
and organization_id = p_Org_id
and implementation_date is not null;
select count(*) into x_rtg_exists
from mtl_rtg_item_revisions
where inventory_item_id = p_item_id
and organization_id = p_Org_id
AND effectivity_date <= p_eff_date
and implementation_date is not null;
Select PRIMARY_UOM_CODE INTO p_pri_uom
FROM mtl_system_items
WHERE inventory_item_Id = p_item_id
AND organization_Id = p_org_id;
SELECT SUM(quantity_in_queue)
+ SUM(quantity_running)
+ SUM(quantity_waiting_to_move)
+ SUM(quantity_rejected)
+ SUM(quantity_scrapped)
-- + SUM(quantity_completed)
-- These assemblies are in some other operation
INTO p_total_quantity
FROM wip_operations wop
WHERE
wop.organization_id = p_organization_id
AND wop.wip_entity_id = p_wip_entity_id
AND
Decode(Decode(Nvl(p_repetitive_schedule_id,-1),
-1, wip_constants.discrete,
wip_constants.repetitive),
wip_constants.discrete,0,
wip_constants.repetitive,wop.repetitive_schedule_id)
= Decode(Decode(Nvl(p_repetitive_schedule_id,-1),
-1, wip_constants.discrete,
wip_constants.repetitive),
wip_constants.discrete,0,
wip_constants.repetitive,p_repetitive_schedule_id);
SELECT NVL(p_total_quantity,0) + Nvl(wdj.quantity_completed,0)
INTO p_total_quantity
FROM wip_discrete_jobs wdj
WHERE
wdj.organization_id = p_organization_id
AND wdj.wip_entity_id = p_wip_entity_id;
SELECT NVL(p_total_quantity,0) + Nvl(wrs.quantity_completed,0)
INTO p_total_quantity
FROM wip_repetitive_schedules wrs
WHERE
wrs.organization_id = p_organization_id
AND wrs.wip_entity_id = p_wip_entity_id
AND wrs.repetitive_schedule_id = p_repetitive_schedule_id;