[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'exists'
from msc_sup_dem_entries
where
plan_id = -1
and publisher_order_type = ALLOCATED_ONHAND
and customer_id = p_customer_id
and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
and inventory_item_id = p_inventory_item_id
and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
and vmi_flag = 1;
select round(nvl(primary_quantity,0),6) primary_quantity,
round(nvl(tp_quantity,0),6) tp_quantity,
--(quantity - nvl(quantity_in_process,0) - nvl(implemented_quantity,0)) available_quantity,
to_char(receipt_date, dformat) receipt_date,
item_name,
item_description,
owner_item_name,
owner_item_description,
customer_item_name,
customer_item_description,
supplier_item_name,
supplier_item_description,
new_order_placement_date rtf_start_date,
receipt_date rtf_end_date
from msc_sup_dem_entries
where plan_id = -1
and publisher_order_type = REPLENISHMENT
and customer_id = p_customer_id
and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
and inventory_item_id = p_inventory_item_id
and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
and release_status in (0,1)
and vmi_flag = 1;
select round(nvl(quantity_in_process,0), 6) qip, round(implemented_quantity,6) ip
from msc_sup_dem_entries
where plan_id = -1
and publisher_order_type = REPLENISHMENT
and customer_id = p_customer_id
and customer_site_id = p_customer_site_id
and inventory_item_id = p_inventory_item_id
and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
and release_status = 1
and vmi_flag = 1;
select round(sum(primary_quantity),6) primary_quantity,
round(sum(tp_quantity),6) tp_quantity
from msc_sup_dem_entries
where plan_id = -1
and publisher_order_type in (REQUISITION, PO)
and customer_id = p_customer_id
and customer_site_id = p_customer_site_id
and inventory_item_id = p_inventory_item_id
and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
and receipt_date <= nvl(l_rtf_end_date, receipt_date)
and vmi_flag = 1;
select nvl(round(primary_quantity,6),0) primary_quantity,
nvl(round(tp_quantity,6),0) tp_quantity,
to_char(new_schedule_date, dformat ) last_update_date,
item_name,
item_description,
owner_item_name,
owner_item_description,
customer_item_name,
customer_item_description,
supplier_item_name,
supplier_item_description
from msc_sup_dem_entries
where plan_id = -1
and publisher_order_type = c_onhand_type
and customer_id = p_customer_id
and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
and inventory_item_id = p_inventory_item_id
and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
and vmi_flag = 1
order by new_schedule_date desc;
select ((a.quantity * src_org.allocation_percent)/100) quantity1,
to_char(a.new_schedule_date, dformat ) last_update_date,
a.item_name,
a.item_description,
a.owner_item_name,
a.owner_item_description,
a.customer_item_name,
a.customer_item_description,
a.supplier_item_name,
a.supplier_item_description
from
msc_sup_dem_entries a,
msc_trading_partner_maps map,
msc_trading_partners tp,
msc_assignment_sets assignmentset,
msc_sr_assignments assignment,
msc_sr_receipt_org rec_org,
msc_sr_source_org src_org,
msc_trading_partner_maps map1,
msc_trading_partner_maps map2,
msc_company_sites site,
msc_companies cp,
msc_company_relationships rel
where plan_id = -1
and a.publisher_order_type = UNALLOCATED_ONHAND
and a.customer_id = p_customer_id
and a.customer_site_id = p_customer_site_id
and a.inventory_item_id = p_inventory_item_id
and a.customer_site_id = map.company_key
and map.map_type = 2
and map.tp_key = tp.partner_id
and assignmentset.assignment_set_name = 'dmt:Supplier Scheduling'
and assignmentset.assignment_set_id = assignment.assignment_set_id
and assignment.organization_id = tp.sr_tp_id
and assignment.sr_instance_id = tp.sr_instance_id
and assignment.inventory_item_id = a.inventory_item_id
and assignment.sourcing_rule_id = rec_org.sourcing_rule_id
and rec_org.sr_receipt_id = src_org.sr_receipt_id -- one to many: one item may have multiple suppliers
and src_org.source_partner_id = map1.tp_key
and map1.map_type = 1
and map1.company_key = rel.relationship_id
and rel.relationship_type = 2
and rel.object_id = cp.company_id --supplier company id in MSC_COMPANIES
and cp.company_id = p_supplier_id
and src_org.source_partner_site_id = map2.tp_key
and map2.map_type = 3
and map2.company_key = site.company_site_id
and site.company_site_id = p_supplier_site_id
order by a.new_schedule_date desc;
SELECT count(*) count,
round(SUM(primary_quantity),6) primary_quantity,
round(SUM(decode(publisher_id, supplier_id, tp_quantity, primary_quantity)),6) tp_quantity
from msc_sup_dem_entries
where plan_id = -1
and publisher_order_type = ASN
and supplier_id = p_supplier_id
and nvl(supplier_site_id, -99) = nvl(p_supplier_site_id, -99)
and inventory_item_id = p_inventory_item_id
and nvl(customer_id,-99) = nvl(p_customer_id,-99)
and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
and receipt_date <= nvl(l_rtf_end_date, receipt_date)
and vmi_flag = 1;
select order_number,
to_char(receipt_date, dformat) next_asn_date,
nvl(round(primary_quantity,6),0) primary_quantity,
nvl(round(decode(publisher_id, supplier_id, tp_quantity, primary_quantity),6),0) tp_quantity,
item_name,
item_description,
owner_item_name,
owner_item_description,
customer_item_name,
customer_item_description,
supplier_item_name,
supplier_item_description
from msc_sup_dem_entries
where plan_id = -1
and publisher_order_type = ASN
and supplier_id = p_supplier_id
and nvl(supplier_site_id, -99) = nvl(p_supplier_site_id, -99)
and inventory_item_id = p_inventory_item_id
and nvl(customer_id,-99) = nvl(p_customer_id,-99)
and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
and receipt_date <= nvl(l_rtf_end_date, receipt_date)
and vmi_flag = 1
order by receipt_date asc;
select to_char(new_schedule_date, dformat) last_delivery_date ,
nvl(round(primary_quantity,6),0) primary_quantity,
nvl(round(tp_quantity, 6),0) tp_quantity,
item_name,
item_description,
owner_item_name,
owner_item_description,
customer_item_name,
customer_item_description,
supplier_item_name,
supplier_item_description
from msc_sup_dem_entries
where plan_id = -1
and publisher_order_type = SHIPMENT_RECEIPT
and customer_id = p_customer_id
and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
and inventory_item_id = p_inventory_item_id
and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
and receipt_date <= nvl(l_rtf_end_date, receipt_date)
and vmi_flag = 1
order by receipt_date desc;
select round(sum(primary_quantity),6) primary_quantity,
round(sum(tp_quantity),6) tp_quantity
from msc_sup_dem_entries
where plan_id = -1
and publisher_order_type = SHIPMENT_RECEIPT
and customer_id = p_customer_id
and nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
and inventory_item_id = p_inventory_item_id
and nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
and nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99)
and receipt_date <= nvl(l_rtf_end_date, receipt_date)
and vmi_flag = 1;
SELECT min_minmax_quantity,min_minmax_days,
max_minmax_quantity, max_minmax_days, enable_vmi_auto_replenish_flag, vmi_replenishment_approval,
using_organization_id, uom_code,
--vmi_uom_code,
supplier_item_name,
--purchasing_unit_of_measure,
--vmi_unit_of_measure,
processing_lead_time,
--average_daily_demand AS average_daily_usage,
replenishment_method,fixed_order_quantity
FROM msc_item_suppliers
WHERE plan_id = -1
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and supplier_id = p_tp_supplier_id
and supplier_site_id = p_tp_supplier_site_id
and vmi_flag = 1
order by using_organization_id desc;
SELECT min_minmax_quantity, max_minmax_quantity,
inventory_planning_code, reorder_point, economic_order_quantity,
--decode(round(item.average_annual_demand/365), 0, -1,
--round(item.average_annual_demand/365))
--average_daily_usage,
uom_code,
item_name
FROM
msc_system_items item
WHERE plan_id = -1
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
select company_id
from msc_company_users
where
user_id = FND_GLOBAL.user_id;
select unit_of_measure
from msc_units_of_measure
where uom_code = l_customer_uom_code;
onhand_rec.last_update_date ||delim ;
unallocated_onhand_rec.last_update_date ||delim ;
SELECT nvl(msde.primary_quantity, 0.0), msde.receipt_date, msde.primary_uom
FROM msc_sup_dem_entries msde
WHERE msde.customer_id = p_customer_id
AND customer_site_id = p_customer_site_id
AND msde.supplier_id = p_supplier_id
AND msde.inventory_item_id = p_inventory_item_id
AND msde.publisher_order_type = SHIPMENT_RECEIPT
AND msde.receipt_date <= nvl(p_time_fence_end_date, msde.receipt_date)
AND msde.plan_id = -1
ORDER BY msde.receipt_date DESC;
SELECT nvl(msde.primary_quantity, 0.0), msde.primary_uom
FROM msc_sup_dem_entries msde
WHERE customer_id = p_customer_id
AND customer_site_id = p_customer_site_id
AND supplier_id = p_supplier_id
AND inventory_item_id = p_inventory_item_id
AND publisher_order_type = SHIPMENT_RECEIPT
AND plan_id = -1;
SELECT nvl(so.primary_quantity, 0), primary_uom
FROM msc_sup_dem_entries so
WHERE so.publisher_order_type = SALES_ORDER
AND so.customer_id = p_customer_id
AND so.customer_site_id = p_customer_site_id
AND so.supplier_id = p_supplier_id
AND so.inventory_item_id = p_inventory_item_id
AND so.internal_flag = INTERNAL_FLAG_SET
AND so.plan_id = -1
AND trunc(nvl(so.receipt_date,so.key_date)) <= trunc(nvl(p_time_fence_end_date,nvl(so.receipt_date,so.key_date)));
SELECT nvl(req.primary_quantity, 0), primary_uom
FROM msc_sup_dem_entries req
WHERE req.publisher_order_type = REQUISITION
AND req.customer_id = p_customer_id
AND req.customer_site_id = p_customer_site_id
AND req.supplier_id = p_supplier_id
AND req.inventory_item_id = p_inventory_item_id
AND req.internal_flag = INTERNAL_FLAG_SET
AND req.link_trans_id IS NULL
AND req.plan_id = -1
AND req.receipt_date <= nvl(p_time_fence_end_date, req.receipt_date);
SELECT nvl(msde.primary_quantity, 0.0), primary_uom
FROM msc_sup_dem_entries msde
WHERE customer_id = p_customer_id
AND customer_site_id = p_customer_site_id
AND supplier_id = p_supplier_id
AND inventory_item_id = p_inventory_item_id
AND publisher_order_type = SALES_ORDER
AND internal_flag is null
AND trunc(nvl(receipt_date,key_date)) <= trunc(nvl(p_time_fence_end_date,nvl(receipt_date,key_date)))
AND plan_id = -1;
SELECT order_number,
receipt_date AS next_asn_date,
primary_quantity AS primary_quantity, primary_uom
FROM msc_sup_dem_entries msde
WHERE msde.plan_id = -1
AND msde.publisher_order_type = ASN
AND msde.supplier_id = p_supplier_id
AND msde.inventory_item_id = p_inventory_item_id
AND msde.customer_id = p_customer_id
AND msde.customer_site_id = p_customer_site_id
AND trunc(nvl(msde.receipt_date,msde.key_date)) <= trunc(nvl(p_time_fence_end_date, nvl(msde.receipt_date,msde.key_date)))
ORDER BY msde.receipt_date DESC;
SELECT nvl(msde.primary_quantity, 0.0), primary_uom
FROM msc_sup_dem_entries msde
WHERE customer_id = p_customer_id
AND customer_site_id = p_customer_site_id
AND supplier_id = p_supplier_id
AND inventory_item_id = p_inventory_item_id
AND publisher_order_type = ASN
AND plan_id = -1
AND trunc(nvl(msde.receipt_date,msde.key_date)) <= trunc(nvl(p_time_fence_end_date, nvl(msde.receipt_date,msde.key_date)))
AND (p_asn_auto_expire = ASN_AUTO_EXPIRE_YES AND SYSDATE <= receipt_date OR
p_asn_auto_expire = ASN_AUTO_EXPIRE_NO);
SELECT msde.primary_quantity, msde.receipt_date, msde.quantity_in_process
INTO l_replenishment_quantity, l_replenishment_date, l_quantity_in_process
FROM msc_sup_dem_entries msde
WHERE customer_id = p_customer_id
AND customer_site_id = p_customer_site_id
AND supplier_id = p_supplier_id
AND inventory_item_id = p_inventory_item_id
AND publisher_order_type = REPLENISHMENT
AND plan_id = -1;
SELECT msi.vmi_minimum_units, msi.vmi_minimum_days, msi.vmi_maximum_units, msi.vmi_maximum_days,
msi.so_authorization_flag, msi.asn_autoexpire_flag,
msi.consigned_flag,msi.vmi_fixed_order_quantity, msi.uom_code, msi.item_name,
NVL(msi.preprocessing_lead_time, 0) + NVL(msi.full_lead_time, 0) + nvl(postprocessing_lead_time, 0) AS lead_time,
msi.forecast_horizon, NVL(msi.source_org_id,NOT_EXISTS)
INTO l_min_minmax_qty, l_min_minmax_days, l_max_minmax_qty, l_max_minmax_days,
l_so_authorization_flag, l_asn_auto_expire, l_consigned,
l_fixed_order_quantity,l_supplier_uom_code, l_item_name, l_lead_time,l_forecast_horizon,
l_source_org_id
FROM msc_system_items msi
WHERE msi.inventory_item_id = p_inventory_item_id
AND msi.organization_id = p_organization_id
AND msi.sr_instance_id = p_sr_instance_id
AND msi.plan_id = -1;
SELECT nvl(primary_quantity, 0), new_schedule_date, primary_uom
FROM msc_sup_dem_entries
WHERE plan_id = -1
AND publisher_order_type = ALLOCATED_ONHAND
AND inventory_item_id = p_inventory_item_id
AND customer_id = p_customer_id
AND customer_site_id = p_customer_site_id
AND supplier_id = p_supplier_id
AND plan_id = -1;
SELECT primary_quantity, new_schedule_date, primary_uom
INTO l_sum_unallocated_qty, l_unallocated_date, l_primary_uom
FROM msc_sup_dem_entries
WHERE plan_id = -1
AND publisher_order_type = UNALLOCATED_ONHAND
AND inventory_item_id = p_inventory_item_id
AND publisher_id = p_customer_id
AND publisher_site_id = p_customer_site_id
AND plan_id = -1
AND rownum = 1
ORDER BY new_schedule_date DESC;
* selected in separate cursors. At present for different allocated onhands we have different dates
* ( one of them is null at present in systest)
*/
-- Allocated Onhand
OPEN c1;
l_onhand_last_update_date DATE;
l_current_onhand_quantity, l_onhand_last_update_date);
|| delim || l_onhand_last_update_date
|| delim || l_intransit_count
|| delim || l_intransit_qty
|| delim || l_intransit_nextasn_ordernum
|| delim || l_intransit_nextdate
|| delim || l_intransit_nextasn_qty
|| delim || l_last_receipt_date
|| delim || l_last_receipt_qty
|| delim || l_graph_name
|| delim || l_inventory_planning_code
|| delim || l_reorder_point
|| delim || l_economic_order_quantity
|| delim || l_average_daily_usage
|| delim || l_customer_item_name
|| delim || l_customer_item_desc
|| delim || l_supplier_item_name
|| delim || l_supplier_item_desc
|| delim || l_implemented_quantity
|| delim || l_supplier_uom_code
|| delim || l_quantity_in_process
|| delim || l_total_receipt_quantity
|| delim || l_owner_item_name
|| delim || l_owner_item_desc
|| delim || nvl(l_min_minmax_quantity, 0)
|| delim || nvl(l_max_minmax_quantity, 0)
|| delim || l_supplier_to_customer_rate
|| delim || nvl(l_min_minmax_days, 0)
|| delim || nvl(l_max_minmax_days, 0)
|| delim || l_auto_replenish_flag
|| delim || l_release_method
|| delim || l_asn_auto_expire
|| delim || l_consigned
|| delim || l_replenishment_method;
SELECT 'exists'
FROM msc_sup_dem_entries
WHERE plan_id = -1
AND publisher_order_type = ALLOCATED_ONHAND
AND customer_id = p_customer_id
AND nvl(customer_site_id,-99) = nvl(p_customer_site_id,-99)
AND inventory_item_id = p_inventory_item_id
AND nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
AND nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99);
SELECT nvl(i.min_minmax_quantity,0) min_minmax_quantity, nvl(i.max_minmax_quantity,0) max_minmax_quantity,
i.processing_lead_time AS processing_lead_time
FROM msc_item_suppliers i, msc_items s, msc_trading_partners tp, msc_trading_partner_maps map_cust, msc_company_sites cust_site,
msc_trading_partner_maps map_supp, msc_company_sites supp_site, msc_trading_partner_maps map_rel,
msc_company_relationships rel
WHERE i.plan_id = -1
-- AND i.vmi_flag = 1
AND i.inventory_item_id = s.inventory_item_id
AND i.inventory_item_id = p_inventory_item_id
AND tp.sr_instance_id = i.sr_instance_id
AND tp.sr_tp_id = i.organization_id
AND tp.partner_id = map_cust.tp_key
AND map_cust.map_type = 2
AND map_cust.company_key = cust_site.company_site_Id
AND cust_site.company_id = p_customer_id
AND cust_site.company_site_id = p_customer_site_id
AND map_supp.tp_key = i.supplier_site_id
AND map_supp.map_type = 3
AND map_supp.company_key = supp_site.company_site_id
AND supp_site.company_site_id = p_supplier_site_id
AND supp_site.company_id = p_supplier_id
AND i.supplier_id = map_rel.tp_key
AND map_rel.map_type = 1
AND map_rel.company_key = rel.relationship_id
AND rel.relationship_type = 2
AND rel.subject_id = p_customer_id
AND rel.object_id = p_supplier_id;
SELECT sum(primary_quantity) as primary_quantity, trunc(key_date) as key_date, primary_uom
FROM msc_sup_dem_entries
WHERE plan_id = -1
AND publisher_order_type IN (ORDER_FORECAST,SALES_FORECAST,HISTORICAL_SALES)
AND inventory_item_id = p_inventory_item_id
AND customer_id = p_customer_id
AND customer_site_id = p_customer_site_id
AND supplier_id = p_supplier_id
group by key_date,primary_uom;
SELECT primary_quantity AS primary_quantity,
trunc(key_date) AS key_date,
bucket_type as bucket_type, primary_uom
FROM msc_sup_dem_entries
WHERE plan_id = -1
AND publisher_order_type = SAFETY_STOCK
AND publisher_id = p_customer_id
AND publisher_site_id = p_customer_site_id
AND inventory_item_id = p_inventory_item_id
ORDER BY trunc(key_date);
SELECT primary_quantity AS primary_quantity,
tp_quantity AS tp_quantity, trunc(key_date) AS key_date, primary_uom
FROM msc_sup_dem_entries
WHERE plan_id = -1
AND publisher_order_type = REQUISITION
AND customer_id = p_customer_id
AND customer_site_id = p_customer_site_id
AND inventory_item_id = p_inventory_item_id
AND nvl(supplier_id,-99) = nvl(p_supplier_id,-99)
AND nvl(supplier_site_id,-99) = nvl(p_supplier_site_id,-99);
SELECT msc_x_hz_ui_query_id_s.nextval INTO l_query_id FROM Dual;
INSERT INTO MSC_VMI_GRAPH
(query_id, graph_date, onhand, order_forecast, req, po, asn, receipt,
projected_onhand, supply_within_lead_time,
MIN, max, safety_stock, recommended_replenishment)
VALUES (l_query_id,p_graph(l_current_record).graph_date,p_graph(l_current_record).onhand,
p_graph(l_current_record).order_forecast,p_graph(l_current_record).req,p_graph(l_current_record).po,
p_graph(l_current_record).asn,p_graph(l_current_record).receipt,
p_graph(l_current_record).projected_onhand,p_graph(l_current_record).supply_within_lead_time,
p_graph(l_current_record).min,p_graph(l_current_record).max,p_graph(l_current_record).safety_stock,
p_graph(l_current_record).recommended_replenishment);
SELECT status_date
INTO l_start_date
FROM msc_plan_org_status
WHERE plan_id = -1
AND organization_id = -1
AND sr_instance_id = -1;
SELECT maps.company_key
INTO l_source_site_id
FROM msc_trading_partner_maps maps, msc_trading_partners tp
WHERE tp.partner_type = ASCP_TP_MAP_TYPE_ORG
AND tp.sr_instance_id = p_sr_instance_id
AND tp.sr_tp_id = p_source_org_id
AND tp.partner_id = maps.tp_key
AND maps.map_type = TP_MAP_TYPE_ORG;
select mrp_atp_schedule_temp_s.nextval
into l_session_id
from dual;
SELECT sum(primary_quantity),key_date
FROM msc_sup_dem_entries supdem
WHERE supdem.publisher_order_type in (20,13,15,16)
AND supdem.inventory_item_id = p_inventory_item_id
AND supdem.customer_id=p_customer_id
AND supdem.customer_site_id=p_customer_site_id
AND supdem.supplier_id=p_supplier_id
AND supdem.supplier_site_id=p_supplier_site_id
AND supdem.plan_id=p_plan_id
group by key_date
having (trunc(key_date)>trunc(sysdate) and trunc(key_date)<=trunc(sysdate+l_forecast_horizon) );
SELECT sum(primary_quantity),key_date
FROM msc_sup_dem_entries supdem
WHERE publisher_order_type =2
AND supdem.inventory_item_id=p_inventory_item_id
AND supdem.customer_id=p_customer_id
AND supdem.customer_site_id=p_customer_site_id
AND supdem.supplier_id=p_supplier_id
AND supdem.supplier_site_id=p_supplier_site_id
AND supdem.plan_id=p_plan_id
group by key_date
having (trunc(key_date)>trunc(sysdate) and trunc(key_date)<=trunc(sysdate+l_forecast_horizon) );
SELECT nvl(forecast_horizon,0) forecast_horizon, nvl(min_minmax_quantity,0) min_minmax_quantity, nvl(max_minmax_quantity,
0) max_minmax_quantity,nvl(min_minmax_days,0) min_minmax_days ,nvl(max_minmax_days,0) max_minmax_days,nvl(fixed_order_quantity,0) fixed_order_quantity, replenishment_method,
nvl(processing_lead_time,0) processing_lead_time
FROM msc_item_suppliers mis
WHERE mis.inventory_item_id = p_inventory_item_id
AND mis.plan_id = p_plan_id
AND mis.sr_instance_id = p_sr_instance_id
AND mis.organization_id = p_organization_id
AND mis. supplier_id = p_tp_supplier_id
AND mis. supplier_site_id = p_tp_supplier_site_id
and vmi_flag = 1
order by using_organization_id desc;
SELECT primary_quantity
FROM msc_sup_dem_entries supdem
WHERE publisher_order_type =9
AND supdem.inventory_item_id=p_inventory_item_id
AND supdem.customer_id=p_customer_id
AND supdem.customer_site_id=p_customer_site_id
AND supdem.supplier_id=p_supplier_id
AND supdem.supplier_site_id=p_supplier_site_id
AND supdem.plan_id=p_plan_id;
SELECT distinct number1
FROM msc_plan_org_status
WHERE plan_id=-1
AND sr_instance_id=-1
AND organization_id=-1 ;
* Prior to populating the data delete all the records
*/
delete from msc_vmi_graph;
INSERT INTO msc_vmi_graph(query_id,graph_date,projected_onhand,min,max)
VALUES( v_index,(sysdate+v_index-1),t_table_pab(v_index), l_min_quantity,l_max_quantity);
INSERT INTO msc_vmi_graph(query_id,graph_date,projected_onhand,min,max)
VALUES(v_index,(sysdate+v_index-1), t_table_pab(v_index),l_min_quantity,l_fix_quantity);
INSERT INTO msc_vmi_graph(query_id,graph_date,projected_onhand,min,max)
VALUES(v_index, (sysdate+v_index-1),t_table_pab_days(v_index),l_min_days,l_max_days);
INSERT INTO msc_vmi_graph(query_id,graph_date,projected_onhand,min,max)
VALUES(v_index,(sysdate+v_index-1), t_table_pab_days(v_index),l_min_days,l_fix_days);
SELECT nvl(sum(primary_quantity),0)
FROM msc_sup_dem_entries supdem
WHERE publisher_order_type =2
AND supdem.inventory_item_id=p_inventory_item_id
AND supdem.customer_id=p_customer_id
AND supdem.customer_site_id=p_customer_site_id
AND supdem.supplier_id=p_supplier_id
AND supdem.supplier_site_id=p_supplier_site_id
AND supdem.plan_id=p_plan_id
AND (trunc(key_date)>=trunc(sysdate +v_index-1) and trunc(key_date)
select maps.company_key
into l_source_site_id
from msc_trading_partner_maps maps,
msc_trading_partners tp
where tp.partner_type = 3
and tp.sr_instance_id = p_sr_instance_id
and tp.sr_tp_id = p_source_org_id
and tp.partner_id = maps.tp_key
and maps.map_type = 2;
select mrp_atp_schedule_temp_s.nextval
into l_session_id
from dual;
SELECT using_organization_id,
nvl(average_daily_demand,0.0) average_daily_usage
FROM msc_vmi_temp
WHERE plan_id = -1
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and supplier_id = p_tp_supplier_id
and supplier_site_id = p_tp_supplier_site_id
and vmi_type = 1
order by using_organization_id desc;
SELECT nvl(average_daily_demand,0.0) average_daily_usage
FROM msc_vmi_temp
WHERE plan_id = -1
and sr_instance_id = p_sr_instance_id
and organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and vmi_type = 2;