[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct sd.customer_id,
sd.customer_site_id,
sd.supplier_id,
sd.supplier_site_id,
sd.inventory_item_id,
itm.organization_id,
itm.supplier_id,
itm.supplier_site_id,
itm.sr_instance_id
FROM msc_sup_dem_entries_v sd,
msc_item_suppliers itm,
MSC_X_ITEM_SUPPLIERS_GTT iut,
MSC_X_ITEM_ORGS_GTT iot,
MSC_X_ITEM_SITES_GTT ist
WHERE sd.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
AND sd.publisher_order_type = 15 -- ASN
AND sd.vmi_flag = 1
AND sd.customer_site_id = iot.company_key
AND sd.supplier_id = iut.object_id
AND sd.supplier_site_id = ist.company_key
AND itm.inventory_item_id = sd.inventory_item_id
--AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
AND itm.vmi_flag = 1
AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
AND sd.plan_id = itm.plan_id
AND ( (nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)) or
(itm.vmi_refresh_flag = 1
AND (itm.replenishment_method = 2 OR itm.replenishment_method = 4)
)
)
UNION /* Bug 3737298 : added UNION so that VMI Engine generates MATERIAL SHORTAGE Exception even
if there does not exist any data in msc_sup_dem_entries. */
SELECT distinct 1 ,
iot.company_key ,
iut.object_id ,
ist.company_key,
itm.inventory_item_id ,
itm.organization_id,
itm.supplier_id,
itm.supplier_site_id,
itm.sr_instance_id
FROM msc_item_suppliers itm,
msc_trading_partners tp2 ,
msc_trading_partner_sites tps,
MSC_X_ITEM_SUPPLIERS_GTT iut,
MSC_X_ITEM_ORGS_GTT iot,
MSC_X_ITEM_SITES_GTT ist
WHERE itm.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
AND itm.organization_id = iot.sr_tp_id
AND itm.supplier_id = iut.tp_key
AND iut.tp_key = tp2.partner_id
AND tp2.sr_instance_id = itm.sr_instance_id
AND tp2.partner_type = 1
AND tps.partner_id = tp2.partner_id
AND tps.sr_instance_id = tp2.sr_instance_id
AND tp2.partner_type = tps.partner_type
AND itm.supplier_site_id = ist.tp_key
AND ist.tp_key = tps.partner_site_id
--and ( itm.supplier_site_id is not null or rownum = 1)
AND itm.vmi_flag = 1
AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
AND itm.inventory_item_id NOT IN ( select distinct sd.inventory_item_id
FROM msc_sup_dem_entries_v sd,
msc_item_suppliers itm,
MSC_X_ITEM_SUPPLIERS_GTT iut,
MSC_X_ITEM_ORGS_GTT iot,
MSC_X_ITEM_SITES_GTT ist
WHERE sd.plan_id = -1
AND sd.publisher_order_type IN (15, 9, 13, 16, 20) -- ASN , Onhand, PO, Rcpt, Req
AND sd.vmi_flag = 1
AND sd.customer_site_id = iot.company_key
AND sd.supplier_id = iut.object_id
AND sd.supplier_site_id = ist.company_key
AND itm.inventory_item_id = sd.inventory_item_id
AND itm.vmi_flag = 1
AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
AND sd.plan_id = itm.plan_id)
;
SELECT distinct sd.supplier_id,
sd.supplier_site_id,
sd.publisher_id,
sd.publisher_site_id,
sd.inventory_item_id,
itm.organization_id,
itm.supplier_id,
itm.supplier_site_id,
itm.sr_instance_id
FROM msc_sup_dem_entries_v sd,
msc_item_suppliers itm,
MSC_X_ITEM_SUPPLIERS_GTT iut,
MSC_X_ITEM_ORGS_GTT iot,
MSC_X_ITEM_SITES_GTT ist
WHERE sd.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
AND sd.publisher_order_type IN (9, 13, 16, 20) -- Onhand, PO, Rcpt, Req
AND sd.vmi_flag = 1
AND sd.customer_site_id = iot.company_key
AND sd.supplier_id = iut.object_id
AND sd.supplier_site_id = ist.company_key
AND itm.inventory_item_id = sd.inventory_item_id
AND sd.plan_id = itm.plan_id
--AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
AND itm.vmi_flag = 1
AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
AND ( (nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)) or
(itm.vmi_refresh_flag = 1
AND (itm.replenishment_method = 2 OR itm.replenishment_method = 4)
)
);
SELECT SUM( DECODE( sd.publisher_id
, sd.supplier_id, sd.tp_quantity
, sd.primary_quantity
)
)
FROM msc_sup_dem_entries sd
WHERE sd.inventory_item_id = p_inventory_item_id
AND sd.supplier_site_id = p_supplier_site_id
AND sd.supplier_id = p_supplier_id
AND sd.plan_id = p_plan_id
AND sd.publisher_order_type = 15 -- ASN
AND sd.customer_id = p_customer_id
AND sd.customer_site_id = p_customer_site_id
AND sd.vmi_flag = 1
-- AND sd.RECEIPT_DATE <= p_time_fence_end_date
;
SELECT sd.primary_quantity
FROM msc_sup_dem_entries sd
WHERE sd.inventory_item_id = p_inventory_item_id
AND sd.publisher_site_id = p_customer_site_id
AND sd.plan_id = p_plan_id
AND sd.publisher_order_type = 9 -- ALLOCATED_ONHAND
AND sd.supplier_site_id = p_supplier_site_id
AND sd.supplier_id = p_supplier_id
AND sd.vmi_flag = 1
ORDER BY sd.key_date desc
;
SELECT SUM(sd.primary_quantity)
FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = p_customer_site_id
AND sd.inventory_item_id = p_inventory_item_id
AND sd.publisher_order_type = 16 -- SHIPMENT_RECEIPT
AND sd.plan_id = p_plan_id
AND sd.supplier_id = p_supplier_id
AND sd.supplier_site_id = p_supplier_site_id
AND sd.vmi_flag = 1
-- AND sd.RECEIPT_DATE <= SYSDATE
;
SELECT SUM(sd.primary_quantity)
FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = p_customer_site_id
AND sd.inventory_item_id = p_inventory_item_id
AND sd.publisher_order_type = 20 -- REQUISITION
AND sd.plan_id = p_plan_id
AND sd.supplier_id = p_supplier_id
AND sd.supplier_site_id = p_supplier_site_id
AND sd.vmi_flag = 1
-- AND sd.RECEIPT_DATE
-- BETWEEN SYSDATE AND p_time_fence_end_date
;
SELECT SUM(sd.primary_quantity)
FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = p_customer_site_id
AND sd.inventory_item_id = p_inventory_item_id
AND sd.publisher_order_type = 13 -- PURCHASE_ORDER
AND sd.plan_id = p_plan_id
AND sd.supplier_id = p_supplier_id
AND sd.supplier_site_id = p_supplier_site_id
AND sd.vmi_flag = 1
-- AND sd.RECEIPT_DATE
-- BETWEEN SYSDATE AND p_time_fence_end_date
;
SELECT sd.primary_quantity
FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = p_customer_site_id
AND sd.inventory_item_id = p_inventory_item_id
AND sd.publisher_order_type = 19 -- REPLENISHMENT
AND sd.plan_id = p_plan_id
AND sd.supplier_id = p_supplier_id
AND sd.supplier_site_id = p_supplier_site_id
AND sd.vmi_flag = 1
;
SELECT itm.min_minmax_quantity
, itm.max_minmax_quantity
, itm.processing_lead_time
, itm.enable_vmi_auto_replenish_flag
, itm.min_minmax_days
, itm.max_minmax_days
, itm.fixed_order_quantity
, mvt.average_daily_demand
, itm.vmi_refresh_flag
, itm.replenishment_method
FROM msc_item_suppliers itm
, msc_vmi_temp mvt
WHERE itm.inventory_item_id = p_inventory_item_id
AND itm.plan_id = p_plan_id
AND itm.sr_instance_id = p_sr_instance_id
AND itm.organization_id = p_organization_id
AND itm.supplier_id = p_supplier_id
AND itm.supplier_site_id = p_supplier_site_id
and mvt.plan_id (+) = itm.plan_id
and mvt.inventory_item_id (+) = itm.inventory_item_id
and mvt.organization_id (+) = itm.organization_id
and mvt.sr_instance_id (+) = itm.sr_instance_id
and mvt.supplier_site_id (+) = itm.supplier_site_id
and mvt.supplier_id (+) = itm.supplier_id
and NVL (mvt.using_organization_id(+), 1) = NVL(itm.using_organization_id, -1)
and mvt.vmi_type (+) = 1 -- supplier facing vmi
ORDER BY itm.using_organization_id DESC
;
SELECT DISTINCT sd.customer_name,
sd.customer_site_name,
sd.customer_item_name,
sd.customer_item_description,
sd.supplier_name,
sd.supplier_site_name,
sd.item_name,
sd.item_description,
sd.supplier_item_name
FROM msc_sup_dem_entries_v sd
WHERE sd.inventory_item_id = p_inventory_item_id
AND sd.plan_id = p_plan_id
AND sd.customer_site_id = p_organization_id
AND sd.supplier_id = p_supplier_id
AND sd.supplier_site_id = p_supplier_site_id
AND sd.publisher_order_type = 15 -- ASN
AND sd.vmi_flag = 1
;
SELECT DISTINCT sd.supplier_name,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description,
sd.customer_name,
sd.customer_site_name,
sd.item_name,
sd.item_description,
sd.customer_item_name
FROM msc_sup_dem_entries_v sd
WHERE sd.inventory_item_id = p_inventory_item_id
AND sd.plan_id = p_plan_id
AND sd.customer_site_id = p_organization_id
AND sd.supplier_id = p_supplier_id
AND sd.supplier_site_id = p_supplier_site_id
AND sd.publisher_order_type IN (9, 13, 16, 20)
AND sd.vmi_flag = 1
;
SELECT distinct 'My Company' ,
tp.ORGANIZATION_CODE,
null,
null,
tp2.partner_name,
tps.TP_SITE_CODE ,
msi.item_name,
msi.description,
itm.supplier_item_name
FROM msc_item_suppliers itm,
msc_trading_partners tp ,
msc_trading_partners tp2 ,
msc_trading_partner_sites tps ,
msc_system_items msi
WHERE tp.sr_tp_id = p_tx_org_id
AND tp.sr_instance_id = p_tx_instance_id
AND tp.partner_type = 3
AND tp2.partner_id = p_tx_supplier_id
AND tp2.sr_instance_id = p_tx_instance_id
AND tp2.partner_type = 1
AND tps.partner_id = tp2.partner_id
and tps.sr_instance_id = tp2.sr_instance_id
AND tp2.partner_type = tps.partner_type
AND tps.partner_site_id = p_tx_supplier_site_id
and (p_tx_supplier_site_id is not null or rownum = 1)
AND itm.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
AND itm.organization_id = tp.sr_tp_id
AND itm.sr_instance_id = tp.sr_instance_id
AND itm.inventory_item_id = p_tx_item_id
AND itm.vmi_flag = 1
AND NVL(itm.enable_vmi_auto_replenish_flag, 'N') = 'N'
AND msi.inventory_item_id = itm.inventory_item_id
AND msi.sr_instance_id = itm.sr_instance_id
AND msi.plan_id = itm.plan_id
AND msi.ORGANIZATION_ID = itm.organization_id
;
print_debug_info ( ' delete obsolete exception');
MSC_X_NETTING_PKG.delete_obsolete_exceptions
(l_publisher_id,
l_publisher_site_id, --owning org
l_customer_id,
l_customer_site_id,
null, --l_supplier_id,
null, --l_supplier_site_id,
MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE,
MSC_X_NETTING_PKG.G_EXCEP9,
MSC_X_NETTING_PKG.G_EXCEP29,
l_item_id,
null, --l_start_date,
null,
MSC_X_NETTING_PKG.vmi
);
print_debug_info ( ' Number of obsolete exceptions deleted of type 9_29 = ' || SQL%ROWCOUNT);
-- bug# 4501946 : added delete procedure for exception type = 10, 30
MSC_X_NETTING_PKG.delete_obsolete_exceptions(l_customer_id,
l_customer_site_id,
null, --customer_id
null, --customer_site_id
l_publisher_id, --supplier_id
l_publisher_site_id, --supplier_site_id
MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE,
MSC_X_NETTING_PKG.G_EXCEP10,
MSC_X_NETTING_PKG.G_EXCEP30,
l_item_id,
null,
null,
MSC_X_NETTING_PKG.VMI);
print_debug_info ( ' Number of obsolete exceptions deleted of type 10,30= ' || SQL%ROWCOUNT);
MSC_X_NETTING_PKG.update_exceptions_summary(l_publisher_id,
l_publisher_site_id,
l_item_id,
l_exception_type,
l_exception_group);
print_debug_info ( ' exception summary updated');
-- bug# 4501946 : removed delete procedure from here and called in the beginning ----
MSC_X_NETTING_PKG.update_exceptions_summary(l_customer_id,
l_customer_site_id,
l_item_id,
l_exception_type,
l_exception_group);
MSC_X_NETTING_PKG.update_exceptions_summary(l_publisher_id,
l_publisher_site_id,
l_item_id,
l_exception_type,
l_exception_group);
print_debug_info ( ' exception summary updated');
-- bug# 4501946 : removed delete procedure from here and called in the beginning ----
-- print_debug_info ( ' obsolete exception deleted');
MSC_X_NETTING_PKG.update_exceptions_summary(l_customer_id,
l_customer_site_id,
l_item_id,
l_exception_type,
l_exception_group);
print_debug_info ( ' exception summary updated');
print_debug_info ( ' delete obsolete exception');
MSC_X_NETTING_PKG.delete_obsolete_exceptions(l_publisher_id,
l_publisher_site_id, --owning org
null, --l_customer_id,
null, --l_customer_site_id,
l_supplier_id,
l_supplier_site_id,
MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE,
MSC_X_NETTING_PKG.G_EXCEP10,
MSC_X_NETTING_PKG.G_EXCEP30,
l_item_id,
null, --l_start_date,
null,
MSC_X_NETTING_PKG.VMI);
print_debug_info ( ' Number of obsolete exceptions deleted = ' || SQL%ROWCOUNT);
-- bug# 4501946 : added delete procedure for exception type 9, 29 ----
MSC_X_NETTING_PKG.delete_obsolete_exceptions(l_supplier_id,
l_supplier_site_id,
l_publisher_id, --customer_id
l_publisher_site_id, --customer_site_id
null, --supplier_id
null, --supplier_site_id
MSC_X_NETTING_PKG.G_MATERIAL_SHORTAGE,
MSC_X_NETTING_PKG.G_EXCEP9,
MSC_X_NETTING_PKG.G_EXCEP29,
l_item_id,
null,
null,
MSC_X_NETTING_PKG.VMI);
print_debug_info ( ' Number of obsolete exceptions deleted of type 9, 29 = ' || SQL%ROWCOUNT);
MSC_X_NETTING_PKG.update_exceptions_summary(l_publisher_id,
l_publisher_site_id,
l_item_id,
l_exception_type,
l_exception_group);
print_debug_info ( ' exception summary updated');
-- bug# 4501946 : removed delete procedure from here and called in the beginning ----
MSC_X_NETTING_PKG.update_exceptions_summary(l_supplier_id,
l_supplier_site_id,
l_item_id,
l_exception_type,
l_exception_group);
MSC_X_NETTING_PKG.update_exceptions_summary(l_publisher_id,
l_publisher_site_id,
l_item_id,
l_exception_type,
l_exception_group);
print_debug_info ( ' exception summary updated');
-- bug# 4501946 : removed delete procedure from here and called in the beginning ----
MSC_X_NETTING_PKG.update_exceptions_summary(l_supplier_id,
l_supplier_site_id,
l_item_id,
l_exception_type,
l_exception_group);
print_debug_info ( ' exception summary updated');
--dbms_output.put_line('Update the magic number');
update msc_x_exception_details
set last_update_login = null
where plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
and exception_type in (9, 10, 29, 30)
and nvl(last_update_login,-1) = MSC_X_NETTING_PKG.G_MAGIC_NUMBER;
--Update the last_update_login back to null to ensure accurate archival
--when exceptions are generated in the next round
update msc_item_exceptions ex
set ex.last_update_login = null,
ex.last_update_date = sysdate
where ex.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
and ex.version = 0
and exception_type in (9, 10, 29, 30)
and nvl(ex.last_update_login,-1) = MSC_X_NETTING_PKG.G_MAGIC_NUMBER;
--update the if the count is 0 to older version
update msc_item_exceptions ex
set ex.version = version + 1,
ex.last_update_date = sysdate
where ex.plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
and ex.version = 0
and exception_type in (9, 10, 29, 30)
and ex.exception_count = 0;
delete msc_item_exceptions ex
where plan_id = MSC_X_NETTING_PKG.G_PLAN_ID
and exception_type in (9, 10, 29, 30)
and version > 20;
delete msc_item_exceptions
where plan_id = G_PLAN_ID
and version = 0
and exception_count = 0;