[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id, sr_instance_id
FROM msc_plans
WHERE plan_id = arg_plan_id;
SELECT mab.bkt_start_date, mab.bkt_end_date
FROM msc_allocation_buckets mab
WHERE mab.plan_id = arg_plan_id
and mab.organization_id = g_org_id
and mab.sr_instance_id = g_inst_id
order by mab.bucket_index ;
SELECT
SUPPLY_OFF offset,
'total' char1, -- only shown in total
1 sequence,
nvl(ms.firm_date,ms.new_schedule_date) new_date,
sum(nvl(ms.firm_quantity,ms.new_order_quantity)) quantity,
0 quantity2,
0 sub_inst_id
FROM msc_supplies ms,
msc_form_query mfq
WHERE ms.plan_id = mfq.number4
AND ms.inventory_item_id = mfq.number1
AND ms.organization_id = mfq.number2
AND ms.sr_instance_id = mfq.number3
AND mfq.query_id = arg_query_id
AND (mfq.number6 <> -1 or
( mfq.number6 = -1 and ms.order_type <> 51)) -- no internal shipments in all org
GROUP BY
nvl(ms.firm_date,ms.new_schedule_date)
UNION ALL
SELECT UNC_DEMAND_OFF offset,
nvl(decode(md.demand_source_type, 8, -- internal sales order
to_char(md.source_organization_id),
decode(arg_group_by, -- other demand types
GY_CUSTOMER, to_char(md.customer_id),
GY_CUSTOMER_SITE, to_char(md.customer_site_id),
GY_DEMAND_CLASS, md.demand_class)),
nvl(to_char(md.source_organization_id), '-2')) char1,
decode(md.demand_source_type, 8, --internal sales order
9, -- displayed as an org
decode(decode(arg_group_by,
GY_CUSTOMER, to_char(md.customer_id),
GY_CUSTOMER_SITE, to_char(md.customer_site_id),
GY_DEMAND_CLASS, md.demand_class),
null, -- if no customer_id/...
decode(md.source_organization_id, null, 8, 9),
arg_group_by)) sequence,
nvl(md.old_using_assembly_demand_date,
md.using_assembly_demand_date) new_date,
SUM(DECODE(md.origination_type, 29,nvl(md.probability,1),1) *
nvl(old_using_requirement_quantity,
using_requirement_quantity)) quantity,
sum(md.unmet_quantity) quantity2,
decode(md.customer_id, null, md.source_org_instance_id, 0) sub_inst_id
FROM msc_demands md,
msc_form_query mfq
WHERE md.plan_id = mfq.number4
AND md.inventory_item_id = mfq.number1
AND md.organization_id = mfq.number2
AND md.sr_instance_id = mfq.number3
AND md.origination_type in (1,24,29,30)
AND mfq.query_id = arg_query_id
AND nvl(md.customer_id,-1) = nvl(arg_customer_id,
nvl(md.customer_id,-1))
AND nvl(md.customer_site_id,-1) = nvl(arg_customer_site_id,
nvl(md.customer_site_id,-1))
AND (arg_customer_list_id is null or
( arg_customer_list_id is not null and
((md.customer_id, md.customer_site_id) in (
select source_type, object_type
from msc_pq_types
where query_id = arg_customer_list_id
and object_type <> 0) or
md.customer_id in (
select source_type
from msc_pq_types
where query_id = arg_customer_list_id
and object_type = 0) or
md.customer_id is null)
)
)
GROUP BY
nvl(md.old_using_assembly_demand_date,
md.using_assembly_demand_date),
nvl(decode(md.demand_source_type, 8,
to_char(md.source_organization_id),
decode(arg_group_by,
GY_CUSTOMER, to_char(md.customer_id),
GY_CUSTOMER_SITE, to_char(md.customer_site_id),
GY_DEMAND_CLASS, md.demand_class)),
nvl(to_char(md.source_organization_id), '-2')),
decode(md.demand_source_type, 8,
9,
decode(decode(arg_group_by,
GY_CUSTOMER, to_char(md.customer_id),
GY_CUSTOMER_SITE, to_char(md.customer_site_id),
GY_DEMAND_CLASS, md.demand_class),
null,
decode(md.source_organization_id, null, 8, 9),
arg_group_by)),
decode(md.customer_id, null, md.source_org_instance_id, 0)
UNION ALL
SELECT
SUGG_ALLOC_OFF offset,
nvl(decode(md.demand_source_type, 8, -- internal sales order
to_char(md.source_organization_id),
decode(arg_group_by, -- other demand types
GY_CUSTOMER, to_char(md.customer_id),
GY_CUSTOMER_SITE, to_char(md.customer_site_id),
GY_DEMAND_CLASS, md.demand_class)),
nvl(to_char(md.source_organization_id), '-2')) char1,
decode(md.demand_source_type, 8, --internal sales order
9, -- displayed in last rows
decode(decode(arg_group_by,
GY_CUSTOMER, to_char(md.customer_id),
GY_CUSTOMER_SITE, to_char(md.customer_site_id),
GY_DEMAND_CLASS, md.demand_class),
null,
decode(md.source_organization_id, null, 8, 9),
arg_group_by)) sequence,
ms.new_schedule_date new_date,
SUM(mslp.quantity) quantity,
0 quantity2,
decode(md.customer_id, null, md.source_org_instance_id, 0) sub_inst_id
FROM msc_demands md,
msc_supplies ms,
msc_single_lvl_peg mslp,
msc_form_query mfq
WHERE ms.organization_id = mfq.number2
AND ms.sr_instance_id = mfq.number3
AND ms.plan_id = mfq.number4
AND ms.inventory_item_id = mfq.number1
AND mfq.query_id = arg_query_id
and mslp.plan_id = ms.plan_id
and mslp.pegging_type = 2 -- supply to parent demand
and mslp.child_id = ms.transaction_id
and md.plan_id = mslp.plan_id
and md.demand_id = mslp.parent_id
AND nvl(md.customer_id,-1) = nvl(arg_customer_id,
nvl(md.customer_id,-1))
AND nvl(md.customer_site_id,-1) = nvl(arg_customer_site_id,
nvl(md.customer_site_id,-1))
AND (arg_customer_list_id is null or
( arg_customer_list_id is not null and
((md.customer_id, md.customer_site_id) in (
select source_type, object_type
from msc_pq_types
where query_id = arg_customer_list_id
and object_type <> 0) or
md.customer_id in (
select source_type
from msc_pq_types
where query_id = arg_customer_list_id
and object_type = 0) or
md.customer_id is null)
)
)
GROUP BY ms.new_schedule_date,
nvl(decode(md.demand_source_type, 8,
to_char(md.source_organization_id),
decode(arg_group_by,
GY_CUSTOMER, to_char(md.customer_id),
GY_CUSTOMER_SITE, to_char(md.customer_site_id),
GY_DEMAND_CLASS, md.demand_class)),
nvl(to_char(md.source_organization_id), '-2')),
decode(md.demand_source_type, 8,
9,
decode(decode(arg_group_by,
GY_CUSTOMER, to_char(md.customer_id),
GY_CUSTOMER_SITE, to_char(md.customer_site_id),
GY_DEMAND_CLASS, md.demand_class),
null,
decode(md.source_organization_id, null, 8, 9),
arg_group_by)),
decode(md.customer_id, null, md.source_org_instance_id, 0)
UNION ALL
SELECT
FIRM_ALLOC_OFF offset,
to_char(ms.organization_id) char1,
9 sequence,
nvl(ms.firm_ship_date,ms.new_ship_date) new_date,
sum(nvl(ms.firm_quantity,ms.new_order_quantity)) quantity,
0 quantity2,
ms.sr_instance_id sub_inst_id
FROM msc_supplies ms,
msc_form_query mfq
WHERE ms.plan_id = mfq.number4
AND ms.inventory_item_id = mfq.number1
AND ms.source_organization_id = mfq.number2
AND ms.source_sr_instance_id = mfq.number3
AND mfq.query_id = arg_query_id
AND ms.firm_planned_type = 1
AND ms.source_organization_id <> ms.organization_id
GROUP BY to_char(ms.organization_id),
nvl(ms.firm_ship_date,ms.new_ship_date),
ms.sr_instance_id
UNION ALL
--------------------------------------------------------------------
-- This select will ensure that all selected items get into cursor
-- even though they do not have any activity
---------------------------------------------------------------------
SELECT UNC_DEMAND_OFF offset,
'dummy' char1,
-1 sequence,
to_date(1, 'J') new_date,
0 quantity,
0 quantity2,
0 sub_inst_id
FROM msc_form_query mfq
WHERE mfq.query_id = arg_query_id
ORDER BY 3,2,7,4;
INSERT INTO msc_drp_hori_plans(
query_id,
organization_id,
sr_instance_id,
inventory_item_id,
row_type,
char1,
sub_org_id,
weight, -- store sub_inst_id
horizontal_plan_type,
bucket_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
quantity)
VALUES (
arg_query_id,
arg_org_id,
arg_instance_id,
arg_item_id,
row_header_type(a), -- row_type
p_text,
p_id, -- store customer_id, customer_site_id, priority, to org id
p_sub_inst_id,
p_sequence,
var_dates(bkt),
SYSDATE,
-1,
SYSDATE,
-1,
decode(p_sequence, 1,
total_row(bkt + (a-1)*g_num_of_buckets),
row_detail(bkt + (a-1)*g_num_of_buckets)));
select ship_method,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.source_organization_id = p_source_org
and mis.sr_instance_id2 = p_source_inst
and mis.organization_id = p_org_id
and mis.sr_instance_id = p_inst_id
order by rank,allocation_percent desc,avg_transit_lead_time;
select msc_supplies_s.nextval into l_transaction_id from dual;
insert into msc_supplies(
transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
inventory_item_id,
organization_id,
sr_instance_id,
plan_id,
new_schedule_date,
order_type,
new_order_quantity,
new_dock_date,
new_ship_date,
status,
applied,
firm_planned_type,
firm_date,
firm_ship_date,
firm_quantity,
source_organization_id,
source_sr_instance_id,
ship_method,
intransit_lead_time,
ship_calendar,
intransit_calendar,
receiving_calendar)
values (
l_transaction_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id,
p_item_id,
p_org_id,
p_inst_id,
p_plan_id,
p_due_date,
51,
0,
p_dock_date,
p_ship_date,
0,
2,
1,
p_due_date,
p_ship_date,
p_allocate_qty,
p_source_org,
p_source_inst,
p_ship_method,
p_lead_time,
p_ship_calendar,
p_deliver_calendar,
p_receive_calendar);
1, --insert or update
l_transaction_id,
p_plan_id,
p_inst_id,
NULL,
supply_columns,
x_return_sts,
x_msg_count,
x_msg_data,
null);
sql_stmt := 'INSERT INTO msc_form_query ( '||
'query_id, '||
'last_update_date, '||
'last_updated_by, '||
'creation_date, '||
'created_by, '||
'last_update_login, '||
'number1, '|| -- item_id
'number2, '|| -- org_id
'number3, '|| -- inst_id
'number4, '|| -- plan_id
'number5, '|| -- displayed item_id
'number6, '|| -- displayed org_id
'number7, '|| -- node type
'number8, '|| -- org sequence
'char1, '||
'char2) '||
' SELECT DISTINCT :p_query_id, '||
'sysdate, '||
'1, '||
'sysdate, '||
'1, '||
'1, '||
'msi.inventory_item_id, '||
'msi.organization_id, '||
'msi.sr_instance_id, '||
'msi.plan_id, '||
'-1,'||
' :p_org_id, '||
' 0, ' || -- NODE_REGULAR_ITEM
' 1, '|| -- org seq
' :p_org_code,'||
'mic.category_name '||
' FROM msc_system_items msi, msc_item_categories mic' ||
' WHERE mic.organization_id = msi.organization_id ' ||
'AND mic.sr_instance_id = msi.sr_instance_id ' ||
'AND mic.inventory_item_id = msi.inventory_item_id ' ||
'AND mic.category_set_id = :p_category_set '||
'AND mic.category_name = :p_category_name '||
'AND msi.plan_id = :p_plan_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)
SELECT distinct p_query_id,
sysdate,
1,
sysdate,
1,
1,
mslp.parent_id
from msc_supplies ms,
msc_supplies parent,
msc_single_lvl_peg mslp,
msc_form_query mfq
where ms.organization_id = mfq.number2
AND ms.sr_instance_id = mfq.number3
and ms.plan_id = mfq.number4
and ms.inventory_item_id = mfq.number1
and mslp.plan_id = ms.plan_id
and mslp.pegging_type = 1 -- supply to parent supply
and mslp.child_id = ms.transaction_id
and mslp.parent_id = parent.transaction_id
and mslp.plan_id = parent.plan_id
and parent.order_type = 51
and ms.new_schedule_date between p_start_date and p_end_date
and mfq.query_id = p_item_query_id;
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,
md.demand_id
from msc_supplies ms,
msc_supplies parent,
msc_demands md,
msc_single_lvl_peg mslp,
msc_form_query mfq
where ms.organization_id = mfq.number2
AND ms.sr_instance_id = mfq.number3
and ms.plan_id = mfq.number4
and ms.inventory_item_id = mfq.number1
and mslp.plan_id = ms.plan_id
and mslp.pegging_type = 1 -- supply to parent supply
and mslp.child_id = ms.transaction_id
and mslp.parent_id = parent.transaction_id
and mslp.plan_id = parent.plan_id
and parent.order_type = 2
and md.plan_id = parent.plan_id
and md.disposition_id = parent.transaction_id
and ms.new_schedule_date between p_start_date and p_end_date
and mfq.query_id = p_item_query_id;
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,
mslp.parent_id
from msc_supplies ms,
msc_single_lvl_peg mslp,
msc_demands md,
msc_form_query mfq
where ms.organization_id = mfq.number2
AND ms.sr_instance_id = mfq.number3
and ms.plan_id = mfq.number4
and ms.inventory_item_id = mfq.number1
and mslp.plan_id = ms.plan_id
and mslp.pegging_type = 2 -- supply to parent demand
and mslp.child_id = ms.transaction_id
and md.plan_id = mslp.plan_id
and md.demand_id = mslp.parent_id
and md.origination_type in (24,29,30)
and nvl(md.demand_source_type,0) <> 8
and ms.new_schedule_date between p_start_date and p_end_date
and mfq.query_id = p_item_query_id;