[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
list.number8,
cap.supplier_id,
cap.supplier_site_id,
AVAILABLE_HOURS,
cal.calendar_date,
null,
cap.capacity
FROM
msc_trading_partners mtp,
msc_calendar_dates cal,
msc_supplier_capacities cap,
msc_item_suppliers items,
msc_form_query list
WHERE
cap.supplier_id = list.number2
AND NVL(cap.supplier_site_id,-1) = NVL(list.number5,-1)
AND cap.plan_id = g_designator
AND cap.inventory_item_id = list.number8
AND cap.capacity > 0
AND items.plan_id = cap.plan_id
AND items.sr_instance_id = g_inst_id
AND items.supplier_id = cap.supplier_id
AND items.inventory_item_id = cap.inventory_item_id
AND nvl(items.supplier_site_id, -1) = NVL(list.number5,-1)
AND cal.calendar_date BETWEEN trunc(cap.from_date) AND trunc(nvl(cap.to_date,g_cutoff_date))
AND cal.calendar_date >= decode(g_plan_type, 4, trunc(g_plan_start_date+2), nvl(trunc(items.supplier_lead_time_date+1),trunc(g_plan_start_date+2)))
AND cal.calendar_date <= trunc(g_cutoff_date)
AND (((items.delivery_calendar_code is not null and cal.seq_num IS NOT NULL)
or (items.delivery_calendar_code is null and g_plan_type <> 4))
or (g_plan_type = 4 and cal.seq_num is not null ))
AND mtp.sr_tp_id = g_org_id
AND mtp.sr_instance_id = g_inst_id
AND cal.calendar_code = nvl(items.delivery_calendar_code,mtp.calendar_code)
AND cal.exception_set_id = mtp.calendar_exception_set_id
AND cal.sr_instance_id = mtp.sr_instance_id
AND list.query_id = g_item_list_id
UNION ALL
-- ============================================
-- Supplier Requirements
-- ============================================
SELECT
list.number8,
DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_id,
M_PLANNED_ARRIVAL, mr.source_supplier_id,
mr.supplier_id),
DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_site_id,
M_PLANNED_ARRIVAL, mr.source_supplier_site_id,
mr.supplier_site_id),
decode(mr.order_type,
M_PLANNED_ORDER, PLANNED_ORDER,
M_PLANNED_ARRIVAL, PLANNED_ORDER,
M_PURCHASE_ORDER, PURCHASE_ORDER,
M_PURCHASE_REQ, PURCHASE_REQ),
decode(g_use_sup_req,
0,mr.new_dock_date,
1,msr.consumption_date),
null,
decode(g_use_sup_req,
0, sum(mr.new_order_quantity),
1, sum(msr.consumed_quantity)
)
FROM msc_form_query list,
msc_supplies mr,
msc_supplier_requirements msr,
msc_trading_partner_sites mtp
WHERE mr.order_type in (M_PLANNED_ORDER, M_PURCHASE_ORDER, M_PURCHASE_REQ, M_PLANNED_ARRIVAL)
AND mr.disposition_status_type <> 2
AND decode(mtp.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) <= trunc(g_cutoff_date)
AND decode(mtp.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) >= trunc(g_plan_start_date+1)
AND mr.plan_id = g_designator
AND DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_id,
M_PLANNED_ARRIVAL,mr.source_supplier_id,
mr.supplier_id) = list.number2
AND DECODE(mr.order_type,
M_PLANNED_ORDER,NVL(mr.source_supplier_site_id,-1),
M_PLANNED_ARRIVAL,NVL(mr.source_supplier_site_id,-1),
NVL(mr.supplier_site_id,-1)) = NVL(list.number5,-1)
AND mr.inventory_item_id = list.number1
AND list.query_id = g_item_list_id
AND mtp.partner_site_id =
DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_site_id,
M_PLANNED_ARRIVAL,mr.source_supplier_site_id,
mr.supplier_site_id)
AND mr.plan_id = msr.plan_id(+)
AND mr.sr_instance_id = msr.sr_instance_id(+)
AND mr.transaction_id = msr.supply_id(+)
GROUP BY list.number8,
DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_id,
M_PLANNED_ARRIVAL, mr.source_supplier_id,
mr.supplier_id),
DECODE(mr.order_type,M_PLANNED_ORDER,mr.source_supplier_site_id,
M_PLANNED_ARRIVAL, mr.source_supplier_site_id,
mr.supplier_site_id),
DECODE(mr.order_type,
M_PLANNED_ORDER, PLANNED_ORDER,
M_PLANNED_ARRIVAL, PLANNED_ORDER,
M_PURCHASE_ORDER, PURCHASE_ORDER,
M_PURCHASE_REQ, PURCHASE_REQ),
DECODE(g_use_sup_req,
0,mr.new_dock_date,
1,msr.consumption_date)
/*
UNION ALL
-- ============================================
-- Purchase Orders Consumption
-- ============================================
SELECT
list.number8,
mr.supplier_id,
mr.supplier_site_id,
PO_CONSUMPTION,
mr.new_dock_date,
null,
to_number(
decode ( mr.order_type,
M_PURCHASE_ORDER, decode ( l_dock_date_prof,
PROMISE_DATE, decode ( mr.promised_date,
NULL, mr.new_order_quantity,
0),
0 ),
0)) po_consumption_quantity
FROM msc_form_query list,
msc_supplies mr,
msc_trading_partner_sites mtp
WHERE mr.order_type = M_PURCHASE_ORDER
AND mr.disposition_status_type <> 2
AND decode(mtp.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) <= trunc(g_cutoff_date)
AND decode(mtp.shipping_control,'BUYER',mr.new_ship_date,mr.new_dock_date) >= trunc(g_plan_start_date+1)
AND mr.plan_id = g_designator
AND mr.supplier_id = list.number2
AND NVL(mr.supplier_site_id,-1) = NVL(list.number5,-1)
AND mr.inventory_item_id = list.number1
AND list.query_id = g_item_list_id
AND mtp.partner_site_id = mr.supplier_site_id
*/
UNION ALL
SELECT
list.number8,
list.number2,
list.number5,
AVAILABLE_HOURS,
g_plan_start_date,
null,
0
FROM msc_form_query list
WHERE list.query_id = g_item_list_id
ORDER BY 1,2,3,5,4;
SELECT DECODE(g_designator, -1, trunc(sysdate), trunc(curr_start_date)) - 1,
DECODE(g_designator, -1, trunc(sysdate+365), trunc(curr_cutoff_date))
FROM msc_plans
WHERE plan_id = g_designator;
SELECT cal.calendar_date
FROM msc_trading_partners tp,
msc_calendar_dates cal
WHERE tp.sr_tp_id = g_org_id
AND tp.sr_instance_id = g_inst_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 p_start_date AND p_end_date
ORDER BY cal.calendar_date;
SELECT msc_supplier_plans_s.nextval
INTO g_query_id
FROM dual;
SELECT partner_name
INTO v_supplier_name
FROM msc_trading_partners
WHERE partner_id = p_sup_id;
SELECT item_name
INTO v_item_name
FROM msc_items
WHERE inventory_item_id = p_item_id;
INSERT INTO msc_supplier_plans(
query_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
sr_instance_id,
supplier_id,
supplier_site_id,
inventory_item_id,
supplier_name,
item_name,
bucket_type,
bucket_date,
quantity1, quantity2, quantity3, quantity4,
quantity5, quantity6, quantity7, quantity8,
quantity9)
VALUES (
g_query_id,
SYSDATE,
-1,
SYSDATE,
-1,
-1,
NULL,
NULL,
p_sup_id,
p_sup_site_id,
p_item_id,
v_supplier_name,
v_item_name,
g_bucket_type,
g_dates(bkt),
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));
select plan_type,
decode(enforce_sup_cap_constraints,1,1,0),
decode(daily_material_constraints,1, 1, 0) --ascp_supplier_constraints
from msc_plans
where plan_id = p_plan_id;
select decode(bom_item_type, 4, NVL(base_item_id, inventory_item_id),
inventory_item_id )
from msc_system_items
where plan_id = p_plan_id
and inventory_item_id = v_item_id;
select distinct i.inventory_item_id
from msc_system_items i,
msc_item_suppliers s
where i.base_item_id = p_base_item
and i.plan_id = p_plan_id
and i.bom_item_type = 4
and i.inventory_item_id = s.inventory_item_id
and i.plan_id = s.plan_id
and i.sr_instance_id = s.sr_instance_id
and i.organization_id = s.organization_id
and s.supplier_id = l_supplier_id;
select 1
from msc_form_query
where number8 = p_base_item
and query_id = p_query_id;
sql_stmt1 := 'INSERT INTO msc_form_query ( '||
'query_id, '||
'last_update_date, '||
'last_updated_by, '||
'creation_date, '||
'created_by, '||
'last_update_login, '||
'number1, '|| -- store for standards - inv_item_id, for ato -model_id
'number2, '||
'number5, '||
'number7, '||
'number8, '|| -- store inv_item_id
'char1, '||
'char2) '||
' SELECT distinct
'|| p_query_id || ', '||
'sysdate, '||
'1, '||
'sysdate, '||
'1, '||
'1, ';
null; -- do not insert anything in msc_form_query
select meaning,tp_site_code
from msc_trading_partner_sites,mfg_lookups
where partner_site_id=p_supplier_site_id
and lookup_type = 'MSC_ORDER_DATE_TYPE'
and lookup_code = decode(shipping_control,'BUYER',1,2);