[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select COST_PER_WEIGHT_UNIT, COST_PER_VOLUME_UNIT,
SHIPMENT_WEIGHT_UOM, SHIPMENT_VOLUME_UOM,
WEIGHT_UOM, VOLUME_UOM
from msc_interorg_ship_methods
where from_organization_id = p_from_org_id
and sr_instance_id = p_from_inst_id
and to_organization_id = p_to_org_id
and sr_instance_id2 = p_to_inst_id
and ship_method = p_ship_method
and plan_id = p_plan_id;
select ms.new_schedule_date
from msc_supplies ms,
msc_single_lvl_peg mslp
where mslp.plan_id = p_plan_id
and mslp.pegging_type = 1 -- supply to parent supply
and mslp.parent_id = p_supply_id
and mslp.child_id = ms.transaction_id
and mslp.plan_id = ms.plan_id;
select intransit_time
from msc_interorg_ship_methods
where from_organization_id = p_from_org
and to_organization_id = p_to_org
and sr_instance_id = p_inst_id
and ship_method = p_ship_method
and plan_id = p_plan_id;
SELECT nvl(postprocessing_lead_time, 0)
FROM msc_system_items
WHERE plan_id = p_plan_id
AND sr_instance_id = p_inst_id
AND ORGANIZATION_ID = p_to_org
AND INVENTORY_ITEM_ID = p_item_id;
select new_ship_date, new_dock_date, new_schedule_date
from msc_supplies
where plan_id = p_plan_id
and sr_instance_id = p_inst_id
and transaction_id = p_transaction_id;
select CONVERSION_RATE
from MSC_WT_UOM_CONVERSIONS_VIEW
where inventory_item_id = p_item_id
and organization_id =p_org_id
and sr_instance_id = p_inst_id
and uom_code = p_uom_code;
select CONVERSION_RATE
from MSC_VL_UOM_CONVERSIONS_VIEW
where inventory_item_id = p_item_id
and organization_id =p_org_id
and sr_instance_id = p_inst_id
and uom_code = p_uom_code;
select msr.sourcing_rule_name
from msc_item_sourcing mis,
msc_sourcing_rules msr
where mis.plan_id = p_plan_id
and mis.inventory_item_id = p_item_id
and mis.source_organization_id = p_from_org_id
and mis.sr_instance_id = p_from_org_inst_id
and mis.organization_id = p_to_org_id
and mis.sr_instance_id2 = p_to_org_inst_id
and nvl(mis.rank,-1) = nvl(p_rank,nvl(mis.rank,-1))
and mis.circular_src = 1
and msr.sourcing_rule_id = mis.sourcing_rule_id;
select preference_key
from msc_user_preference_keys
where plan_type = v_plan_type
and number1 = p_lookup_code
and PREF_TAB = p_pref_tab
and prompt = p_lookup_type;
select name
from msc_drp_alloc_rules
where rule_id = p_rule_id;
PROCEDURE update_supply_row(p_plan_id number,
p_transaction_id number,
p_shipment_id number,
p_firm_flag number,
p_ship_date date,
p_dock_date date,
p_ship_method varchar2,
p_lead_time number) IS
cursor sup_c is
select msi.postprocessing_lead_time pp_lead_time,
ms.firm_planned_type firm_flag,
ms.firm_date,
ms.new_ship_date ship_date,
ms.new_dock_date dock_date,
ms.ship_method,
ms.intransit_lead_time lead_time,
ms.shipment_id,
decode( ms.firm_planned_type, 1,
nvl(ms.firm_quantity,ms.new_order_quantity),
null) firm_qty,
nvl(ms.firm_quantity,ms.new_order_quantity) new_firm_qty,
ms.sr_instance_id
from msc_supplies ms,
msc_system_items msi
where ms.plan_id = p_plan_id
and ms.transaction_id = p_transaction_id
and msi.inventory_item_id = ms.INVENTORY_ITEM_ID
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
and msi.plan_id = ms.plan_id;
update msc_supplies
set firm_planned_type = p_firm_flag,
firm_quantity = p_firm_qty,
firm_date = p_firm_date,
new_ship_date = nvl(p_ship_date,new_ship_date),
new_dock_date = nvl(p_dock_date,new_dock_date),
ship_method = nvl(p_ship_method,ship_method),
intransit_lead_time = nvl(p_lead_time,intransit_lead_time),
status = 0,
applied = 2,
shipment_id = p_shipment_id
where plan_id = p_plan_id
and transaction_id = p_transaction_id;
END update_supply_row;
2, --insert or update
g_supply_undo_rec(a).transaction_id,
p_plan_id,
g_supply_undo_rec(a).sr_instance_id,
NULL,
supply_Columns,
x_return_sts,
x_msg_count,
x_msg_data,
NULL);
supply_Columns.delete;
g_supply_undo_rec.delete;
select shipment_id
from msc_supplies
where plan_id = p_plan_id
and sr_instance_id = p_instance_id
and transaction_id = p_disposition_id;
select order_number
from msc_demands
where plan_id = p_plan_id
and sr_instance_id = p_instance_id
and disposition_id = p_transaction_id;
select mst.from_time,mst.to_time
from msc_shift_times mst,
msc_calendar_shifts mcs
where mcs.calendar_code = p_calendar_code
and mcs.sr_instance_id = p_instance_id
and mst.calendar_code = mcs.calendar_code
and mst.sr_instance_id = mcs.sr_instance_id
and mst.shift_num = mcs.shift_num;
select child_id
from msc_single_lvl_peg
where plan_id = p_plan_id
and pegging_type = 2 -- supply to parent demand
and parent_id = p_demand_id;
select source_organization_id,
sr_instance_id2,
min(avg_transit_lead_time),
max(avg_transit_lead_time)
from msc_item_sourcing mis
where mis.plan_id = p_plan_id
and mis.inventory_item_id = p_item_id
and mis.organization_id = p_org_id
and mis.sr_instance_id = p_inst_id
and mis.source_organization_id =
nvl(p_source_org_id, mis.source_organization_id)
and mis.sr_instance_id2 = nvl(p_source_inst_id,mis.sr_instance_id2)
group by source_organization_id, sr_instance_id2;
select nvl(fixed_lead_time,0)
from msc_system_items
where plan_id = p_plan_id
and inventory_item_id = p_item_id
and organization_id = p_org_id
and sr_instance_id = p_inst_id;
select inventory_item_id
from msc_components_sc_v
where plan_id = p_plan_id
and using_assembly_id = p_item_id
and organization_id = p_org_id
and sr_instance_id = p_inst_id;
select msc_form_query_s.nextval
into p_query_id
from dual;
insert into msc_form_query
(QUERY_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NUMBER1)
values (
p_query_id,
sysdate,
-1,
sysdate,
-1,
-1,
p_id(a));
p_list := ' select number1 '||
' from msc_form_query '||
' where query_id ='||p_query_id ;
select med.exception_detail_id
from msc_exception_details med
where med.plan_id = p_plan_id
and med.inventory_item_id = p_item_id
and med.organization_id = p_org_id
and med.sr_instance_id = p_inst_id
and med.exception_type = p_related_excp_type
and (med.date1
between(p_start_date - p_max_time - p_lt_window) and
(p_end_date - p_min_time +1) or
nvl(med.date2,med.date1)
between(p_start_date - p_max_time - p_lt_window) and
(p_end_date - p_min_time +1) or
(med.date1 < (p_start_date - p_max_time - p_lt_window) and
nvl(med.date2,med.date1) > (p_end_date - p_min_time +1)));
PROCEDURE update_exp_version(p_rowid rowid,
p_action_taken number) IS
TYPE numtab is table of Number index by binary_integer;
Select msie.plan_id,
msie.organization_id,
msie.sr_instance_id,
msie.inventory_item_id,
msie.exception_type,
msie.supplier_id,
msie.supplier_site_id,
msie.source_org_id
INTO p_plan_id, p_org_id, p_inst_id, p_item_id, p_excp_type,
p_supplier_id, p_supplier_site_id, p_source_org_id
From msc_exception_details med,
Msc_srp_item_exceptions msie
Where med.plan_id = msie.plan_id
And med.organization_id = msie.organization_id
And med.sr_instance_id = msie.sr_instance_id
And med.inventory_item_id = msie.inventory_item_id
And med.exception_type = msie.exception_type
And nvl(med.supplier_id,-23453) = msie.supplier_id
And nvl(med.supplier_site_id,-23453) = msie.supplier_site_id
And decode(med.exception_type, 43, med.number2,-23453) =
msie.source_org_id
and msie.exist = 1
and med.rowid = p_rowid
for update of msie.action_taken_date nowait;
Select med.exception_detail_id
BULK COLLECT INTO p_excp_id
From msc_exception_details med
Where med.plan_id = p_plan_id
And med.organization_id = p_org_id
And med.sr_instance_id = p_inst_id
And med.inventory_item_id = p_item_id
And med.exception_type = p_excp_type
And nvl(med.supplier_id,-23453) = p_supplier_id
And nvl(med.supplier_site_id,-23453) = p_supplier_site_id
And decode(med.exception_type, 43, med.number2,-23453) = p_source_org_id
for update of med.action_taken_date, med.action_taken nowait;
Update msc_exception_details
Set action_taken_date = p_action_taken_date,
action_taken = p_action_taken
Where plan_id = p_plan_id
AND exception_detail_id = p_excp_id(a);
Update msc_srp_item_exceptions
Set action_taken_date = p_action_taken_date
Where plan_id = p_plan_id
And organization_id = p_org_id
And sr_instance_id = p_inst_id
And inventory_item_id = p_item_id
And exception_type = p_excp_type
And supplier_id = p_supplier_id
And supplier_site_id = p_supplier_site_id
And source_org_id = p_source_org_id
and exist = 1;
END update_exp_version;
select plan_start_date
from msc_plans
where plan_id = p_plan_id;
Select med.exception_detail_id,
msie.action_taken_date,
msie.last_generated_date
BULK COLLECT INTO p_excp_id, p_action_date, p_gen_date
From msc_exception_details med,
Msc_srp_item_exceptions msie
Where med.plan_id = msie.plan_id
And med.organization_id = msie.organization_id
And med.sr_instance_id = msie.sr_instance_id
And med.inventory_item_id = msie.inventory_item_id
And med.exception_type = msie.exception_type
And nvl(med.supplier_id,-23453) = msie.supplier_id
And nvl(med.supplier_site_id,-23453) = msie.supplier_site_id
And decode(med.exception_type, 43, med.number2,-23453) =
msie.source_org_id
and msie.exist = 1
and msie.plan_id = p_plan_id;
Update msc_exception_details
Set action_taken_date = p_action_date(a),
first_generated_date = p_gen_date(a),
Action_taken = decode(p_action_date(a), null, 2, 1),
new_exception = decode(p_gen_date(a), p_plan_date, 1, 0)
Where plan_id = p_plan_id
And exception_detail_id = p_excp_id(a);