[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(arg_plan_id, -1, sysdate, trunc(curr_start_date)),
DECODE(arg_plan_id, -1, sysdate+365, trunc(curr_cutoff_date))
FROM msc_plans
WHERE plan_id = arg_plan_id;
Select plan_type
FROM msc_plans
WHERE plan_id = arg_plan_id;
SELECT cal.calendar_date
FROM msc_calendar_dates cal,
msc_trading_partners tp
WHERE tp.sr_tp_id = arg_plan_organization_id
AND tp.sr_instance_id = arg_plan_instance_id
AND tp.calendar_exception_set_id = cal.exception_set_id
AND tp.partner_type = 3
AND tp.calendar_code = cal.calendar_code
AND tp.sr_instance_id = cal.sr_instance_id
AND cal.calendar_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
ORDER BY cal.calendar_date;
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
6, to_char(md.inventory_item_id),
7, nvl(to_char(md.customer_site_id), '-99'),
8, nvl(to_char(md.zone_id),'-99'),
9, decode(md.zone_id, null, '-99',
to_char(md.customer_id)||':'||
to_char(md.zone_id)),
2, nvl(to_char(md.ship_to_site_id),'-99'),
3, nvl(to_char(md.bill_id),'-99'),
10, nvl(md.demand_class,'-99'), '-1') ship_to,
decode(md.origination_type,6, SO_ORIGINAL, 30, SO_ORIGINAL,
ORIGINAL) row_type,
decode(md.origination_type,6, SO_ORIGINAL_OFF, 30, SO_ORIGINAL_OFF,
ORIGINAL_OFF) offset,
md.using_assembly_demand_date new_date,
md.using_assembly_demand_date old_date,
sum(md.using_requirement_quantity) new_quantity,
sum(nvl(md.UNMET_QUANTITY,0)) unmet_quantity
FROM msc_form_query list,
msc_demands md
WHERE md.plan_id = list.number4
AND md.inventory_item_id = list.number1
AND md.organization_id = list.number2
AND md.sr_instance_id = list.number3
AND list.query_id = item_list_id
and md.origination_type in (7, 29, 6, 30)
and trunc(md.using_assembly_demand_date) <= l_plan_end_date
and list.number7 = NODE_GL_FORECAST_ITEM -- Select only GF
and (md.original_item_id is null or
md.original_item_id = md.inventory_item_id)
GROUP BY
list.number5,
list.number6,
list.number3,
decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
6, to_char(md.inventory_item_id),
7, nvl(to_char(md.customer_site_id), '-99'),
8, nvl(to_char(md.zone_id),'-99'),
9, decode(md.zone_id, null, '-99',
to_char(md.customer_id)||':'||
to_char(md.zone_id)),
2, nvl(to_char(md.ship_to_site_id),'-99'),
3, nvl(to_char(md.bill_id),'-99'),
10, nvl(md.demand_class,'-99'), '-1'),
decode(md.origination_type,6, SO_ORIGINAL, 30, SO_ORIGINAL,
ORIGINAL),
decode(md.origination_type,6, SO_ORIGINAL_OFF, 30, SO_ORIGINAL_OFF,
ORIGINAL_OFF),
md.using_assembly_demand_date
UNION ALL
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
decode(list.number8, 6, to_char(forecast.inventory_item_id),
2, nvl(to_char(forecast.ship_id),'-99'),
3, nvl(to_char(forecast.bill_id),'-99'),
10, nvl(forecast.demand_class,'-99'),
MSC_GLOBAL_FORECASTING.get_ship_to(
list.number8,
forecast.plan_id,
forecast.sales_order_id)) ship_to,
CONSUMED row_type,
CONSUMED_OFF offset,
forecast.consumption_date new_date,
forecast.consumption_date old_date,
sum(nvl(forecast.consumed_qty,0)) new_quantity,
0 old_quantity
FROM msc_form_query list,
msc_forecast_updates forecast
where forecast.organization_id = list.number2
AND forecast.plan_id = list.number4
AND forecast.inventory_item_id = list.number1
AND forecast.sr_instance_id = list.number3
AND list.query_id = item_list_id
and trunc(forecast.consumption_date) <= l_plan_end_date
and list.number7 = NODE_GL_FORECAST_ITEM -- Select only GF
GROUP BY
list.number5,
list.number6,
list.number3,
decode(list.number8, 6, to_char(forecast.inventory_item_id),
2, nvl(to_char(forecast.ship_id),'-99'),
3, nvl(to_char(forecast.bill_id),'-99'),
10, nvl(forecast.demand_class,'-99'),
MSC_GLOBAL_FORECASTING.get_ship_to(
list.number8,
forecast.plan_id,
forecast.sales_order_id)),
CONSUMED, CONSUMED_OFF,
forecast.consumption_date
UNION ALL -- substitution in
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
6, to_char(md.inventory_item_id),
7, nvl(to_char(md.customer_site_id), '-99'),
8, nvl(to_char(md.zone_id),'-99'),
9, decode(md.zone_id, null, '-99',
to_char(md.customer_id)||':'||
to_char(md.zone_id)),
2, nvl(to_char(md.ship_to_site_id),'-99'),
3, nvl(to_char(md.bill_id),'-99'),
10, nvl(md.demand_class,'-99'), '-1') ship_to,
decode(md.origination_type, 6, SO_SUBS_IN, 30, SO_SUBS_IN,
FCST_SUBS_IN) row_type,
decode(md.origination_type, 6, SO_SUBS_IN_OFF, 30, SO_SUBS_IN_OFF,
FCST_SUBS_IN_OFF) offset,
md.using_assembly_demand_date new_date,
md.using_assembly_demand_date old_date,
sum(nvl(md.using_requirement_quantity,0)) new_quantity,
sum(nvl(md.UNMET_QUANTITY,0)) unmet_quantity
FROM msc_form_query list,
msc_demands md
WHERE md.plan_id = list.number4
AND md.inventory_item_id = list.number1
AND md.organization_id = list.number2
AND md.sr_instance_id = list.number3
AND list.query_id = item_list_id
and md.origination_type in (7, 29, 6, 30)
and trunc(md.using_assembly_demand_date) <= l_plan_end_date
and md.original_item_id <> md.inventory_item_id
and list.number7 = NODE_GL_FORECAST_ITEM -- Select only GF
GROUP BY
list.number5,
list.number6,
list.number3,
decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
6, to_char(md.inventory_item_id),
7, nvl(to_char(md.customer_site_id), '-99'),
8, nvl(to_char(md.zone_id),'-99'),
9, decode(md.zone_id, null, '-99',
to_char(md.customer_id)||':'||
to_char(md.zone_id)),
2, nvl(to_char(md.ship_to_site_id),'-99'),
3, nvl(to_char(md.bill_id),'-99'),
10, nvl(md.demand_class,'-99'), '-1'),
decode(md.origination_type, 6, SO_SUBS_IN, 30, SO_SUBS_IN,
FCST_SUBS_IN),
decode(md.origination_type, 6, SO_SUBS_IN_OFF, 30, SO_SUBS_IN_OFF,
FCST_SUBS_IN_OFF),
md.using_assembly_demand_date
UNION ALL -- substitution out
SELECT list.number5 item_id,
list.number6 org_id,
list.number3 inst_id,
decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
6, to_char(list.number1),
7, nvl(to_char(md.customer_site_id), '-99'),
8, nvl(to_char(md.zone_id),'-99'),
9, decode(md.zone_id, null, '-99',
to_char(md.customer_id)||':'||
to_char(md.zone_id)),
2, nvl(to_char(md.ship_to_site_id),'-99'),
3, nvl(to_char(md.bill_id),'-99'),
10, nvl(md.demand_class,'-99'), '-1') ship_to,
decode(md.origination_type, 6, SO_SUBS_OUT, 30, SO_SUBS_OUT,
FCST_SUBS_OUT) row_type,
decode(md.origination_type, 6, SO_SUBS_OUT_OFF, 30, SO_SUBS_OUT_OFF,
FCST_SUBS_OUT_OFF) offset,
md.using_assembly_demand_date new_date,
md.using_assembly_demand_date old_date,
sum(nvl(md.using_requirement_quantity,0)) new_quantity,
sum(nvl(md.UNMET_QUANTITY,0)) unmet_quantity
FROM msc_form_query list,
msc_demands md
WHERE md.plan_id = list.number4
AND md.original_item_id = list.number1
AND md.organization_id = list.number2
AND md.sr_instance_id = list.number3
AND list.query_id = item_list_id
and md.origination_type in (7, 29, 6, 30)
and trunc(md.using_assembly_demand_date) <= l_plan_end_date
and md.original_item_id <> md.inventory_item_id
and list.number7 = NODE_GL_FORECAST_ITEM -- Select only GF
GROUP BY
list.number5,
list.number6,
list.number3,
decode(list.number8, 4, nvl(to_char(md.customer_id), '-99'),
6, to_char(list.number1),
7, nvl(to_char(md.customer_site_id), '-99'),
8, nvl(to_char(md.zone_id),'-99'),
9, decode(md.zone_id, null, '-99',
to_char(md.customer_id)||':'||
to_char(md.zone_id)),
2, nvl(to_char(md.ship_to_site_id),'-99'),
3, nvl(to_char(md.bill_id),'-99'),
10, nvl(md.demand_class,'-99'), '-1'),
decode(md.origination_type, 6, SO_SUBS_OUT, 30, SO_SUBS_OUT,
FCST_SUBS_OUT) ,
decode(md.origination_type, 6, SO_SUBS_OUT_OFF, 30, SO_SUBS_OUT_OFF,
FCST_SUBS_OUT_OFF) ,
md.using_assembly_demand_date
UNION ALL
--------------------------------------------------------------------
-- This select will ensure that all selected items get into cursor
-- even though they do not have any activity
---------------------------------------------------------------------
SELECT list.number5,
list.number6,
list.number3,
'0',
ORIGINAL,
ORIGINAL_OFF,
to_date(1, 'J'),
to_date(1, 'J'),
0,
0
FROM msc_form_query list
WHERE list.query_id = item_list_id
ORDER BY
1, 2,4,7;
INSERT INTO msc_material_plans(
query_id,
organization_id,
sr_instance_id,
plan_id,
plan_organization_id,
plan_instance_id,
inventory_item_id,
horizontal_plan_type,
horizontal_plan_type_text,
item_segments, -- store ship_to_level
bucket_type,
bucket_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
quantity1, quantity2, quantity3, quantity4,
quantity5, quantity6, quantity7, quantity8,
quantity9, quantity10, quantity11, quantity12,
quantity13)
VALUES (
item_list_id,
p_org_id,
p_inst_id,
arg_plan_id,
arg_plan_organization_id,
arg_plan_instance_id,
p_item_id,
1,
'GLOBAL',
p_ship_to,
1,
var_dates(bkt),
SYSDATE,
-1,
SYSDATE,
-1,
bkt_data.qty1(bkt),
bkt_data.qty2(bkt),
bkt_data.qty3(bkt),
bkt_data.qty4(bkt),
bkt_data.qty5(bkt),
bkt_data.qty6(bkt),
bkt_data.qty7(bkt),
bkt_data.qty8(bkt),
bkt_data.qty9(bkt),
bkt_data.qty10(bkt),
bkt_data.qty11(bkt),
bkt_data.qty12(bkt),
bkt_data.qty13(bkt));
INSERT INTO msc_material_plans(
query_id,
organization_id,
sr_instance_id,
plan_id,
plan_organization_id,
plan_instance_id,
inventory_item_id,
horizontal_plan_type,
horizontal_plan_type_text,
item_segments, -- store ship_to_level
bucket_type,
bucket_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
quantity1, quantity2, quantity3, quantity4,
quantity5, quantity6, quantity7, quantity8,
quantity9, quantity10, quantity11, quantity12,
quantity13)
VALUES (
item_list_id,
p_org_id,
p_inst_id,
arg_plan_id,
arg_plan_organization_id,
arg_plan_instance_id,
p_item_id,
10,
'GLOBAL',
p_ship_to,
1,
sysdate,
SYSDATE,
-1,
SYSDATE,
-1,
ep_bucket_cells_tab(0),
ep_bucket_cells_tab(1),
ep_bucket_cells_tab(2),
ep_bucket_cells_tab(3),
ep_bucket_cells_tab(4),
-- ep_bucket_cells_tab(0) - ep_bucket_cells_tab(2),
ep_bucket_cells_tab(5),
ep_bucket_cells_tab(6),
ep_bucket_cells_tab(7),
ep_bucket_cells_tab(8),
ep_bucket_cells_tab(9),
ep_bucket_cells_tab(10),
ep_bucket_cells_tab(11),
ep_bucket_cells_tab(12));
select mps.ship_to
from msc_plan_schedules mps
where mps.plan_id = p_plan_id
and mps.organization_id =-1
and mps.ship_to is not null;
select mde.update_type
from msc_designators mde,
msc_demands md
where md.plan_id = p_plan_id
and md.organization_id = p_org
and md.sr_instance_id = p_inst
and md.inventory_item_id = p_item
and md.schedule_designator_id = mde.designator_id;
select organization_id, sr_instance_id
from msc_plan_organizations
where plan_id = p_plan_id;
INSERT INTO msc_form_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1, -- item_id
number2,
number3,
number4, -- plan_id
number5, -- displayed item_id
number6, -- displayed org_id
number7, -- node type
number8, -- ship_to_level
char1,
char2)
SELECT p_query_id,
sysdate,
1,
sysdate,
1,
1,
inventory_item_id,
organization_id,
sr_instance_id,
p_plan_id,
inventory_item_id,
p_display_org_id,
p_node_type,
nvl(p_ship_to_level,0),
p_display_org,
item_name
FROM msc_system_items
where plan_id = p_plan_id
and organization_id = p_org_id(a)
and sr_instance_id = p_inst_id(a)
and inventory_item_id = p_item_id(b);
INSERT INTO msc_form_query (
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
number1, -- item_id
number2,
number3,
number4, -- plan_id
number5, -- displayed item_id
number6, -- displayed org_id
number7, -- node type
number8, -- ship_to_level
char1,
char2)
SELECT p_query_id,
sysdate,
1,
sysdate,
1,
1,
p_item_id(b),
-1, -- organization_id,
p_inst_id(1),
p_plan_id,
p_item_id(b),
-1, -- displayed org_id
p_node_type,
nvl(p_ship_to_level,0),
p_all_org_string,
msc_get_name.item_name(p_item_id(b), null,null,null)
FROM dual;
sql_stmt := 'INSERT INTO msc_form_query ( '||
'query_id, '||
'last_update_date, '||
'last_updated_by, '||
'creation_date, '||
'created_by, '||
'last_update_login, '||
'number1) ' ||
'SELECT distinct :p_query_id,' ||
' sysdate, '||
' 1, '||
' sysdate, '||
' 1, '||
' 1, ';
' FROM msc_forecast_updates mfu, msc_demands md'||
' WHERE mfu.plan_id = :p_plan_id' ||
' AND mfu.sr_instance_id = :p_inst_id'||
' and mfu.organization_id =:p_org_id '||
' and mfu.inventory_item_id = :p_item_id' ||
' and mfu.plan_id = md.plan_id' ||
' and mfu.consumed_qty > 0'||
' and trunc(mfu.consumption_date) BETWEEN '''||
p_start_date||''' AND '''|| p_end_date|| '''';
select to_char(md.customer_id),
to_char(md.customer_site_id),
to_char(md.zone_id)
from msc_demands md
where plan_id = p_plan_id
and demand_id = p_sales_order_id;