[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning
BULK COLLECT INTO lv_exception_grp
from mfg_lookups
where lookup_type = 'MSC_X_EXCEPTION_GROUP'
order by lookup_code;
select meaning
BULK COLLECT INTO lv_exception_type
from mfg_lookups
where lookup_type = 'MSC_X_EXCEPTION_TYPE'
order by lookup_code;
select nvl(max(last_refresh_number),0)
into l_max_refresh_number
from msc_sup_dem_entries
where last_refresh_number > G_ZERO;
Delete_Exec_Order_Dependency(l_max_refresh_number);
DELETE_EXCEP (); --added for bug#6729356
that need to be deleted are not removed from the table (msc_sup_dem_entries).
Instead the quantity is set to zero and the transaction id is updated
to ensure that the exceptions for the items deleted are recomputed
The current netting engine will only consider this (quantity = 0)
for non-execution entities exceptions.
-----------------------------------------------------------*/
IF (p_changed_order in (to_char(1), 'Y')) THEN
select status
into l_changed_order_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP8
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = l_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP8
and sr_instance_id = G_SR_INSTANCE_ID;
select status
into l_late_order_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP1
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = l_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP1
and sr_instance_id = G_SR_INSTANCE_ID;
select status
into l_material_shortage_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP2
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = l_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP2
and sr_instance_id = G_SR_INSTANCE_ID;
select status
into l_response_required_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP3
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = l_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP3
and sr_instance_id = G_SR_INSTANCE_ID;
select status
into l_forecast_mismatch_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP5
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = l_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP5
and sr_instance_id = G_SR_INSTANCE_ID;
select status
into l_early_order_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP6
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = l_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP6
and sr_instance_id = G_SR_INSTANCE_ID;
select status
into l_material_excess_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP7
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = l_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP7
and sr_instance_id = G_SR_INSTANCE_ID;
select status
into l_forecast_accuracy_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP9
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = l_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP9
and sr_instance_id = G_SR_INSTANCE_ID;
select status
into l_performance_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP10
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = l_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP10
and sr_instance_id = G_SR_INSTANCE_ID;
--dbms_output.put_line('update item ' || l_refreshnum);
update_item(l_refreshnum);
delete /*+ PARALLEL(sd) */ from msc_sup_dem_entries sd
where sd.plan_id = G_PLAN_ID
and sd.quantity = 0
and nvl(sd.last_update_login,-1) = -99
and sd.last_refresh_number <= l_max_refresh_number ;
DELETE from msc_serial_numbers msn
WHERE NVL(msn.disable_date,sysdate+1)<=sysdate OR
serial_txn_id not in (select transaction_id from msc_sup_dem_entries);
update msc_x_exception_details
set version = null, last_update_login = null
where plan_id = -1
and version = 'CURRENT'
and exception_group in (1,2,3,4,5,6,7,8,9,10);
update msc_x_exception_details
set version = null, last_update_login = null
where plan_id = -1
and version = 'CURRENT'
and exception_group in (1,2,3,4,5,6,7,8,9,10);
select status
into l_potential_late_order_refnum
from msc_plan_org_status
where plan_id = G_PLAN_ID
and organization_id = G_GROUP4
and sr_instance_id = G_SR_INSTANCE_ID;
update msc_plan_org_status
set status = p_max_refresh_number,
status_date = sysdate
where plan_id = G_PLAN_ID
and organization_id = G_GROUP4
and sr_instance_id = G_SR_INSTANCE_ID;
select 1 into l_ret_flag
from dual
where exists ( select 1
from msc_plan_org_status
where plan_id = G_PLAN_ID
and sr_instance_id = G_SR_INSTANCE_ID
and organization_id = p_org_id
);
select count(*)
into l_count
from msc_plan_org_status
where plan_id = -1
and organization_id in (G_GROUP1,G_GROUP2,G_GROUP3,
G_GROUP4,G_GROUP5,G_GROUP6,
G_GROUP7,G_GROUP8,G_GROUP9,G_GROUP10);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP1, G_SR_INSTANCE_ID, 0, sysdate);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP2, G_SR_INSTANCE_ID, 0, sysdate);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP3, G_SR_INSTANCE_ID, 0, sysdate);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP4, G_SR_INSTANCE_ID, 0, sysdate);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP5, G_SR_INSTANCE_ID, 0, sysdate);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP6, G_SR_INSTANCE_ID, 0, sysdate);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP7, G_SR_INSTANCE_ID, 0, sysdate);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP8, G_SR_INSTANCE_ID, 0, sysdate);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP9, G_SR_INSTANCE_ID, 0, sysdate);
insert into msc_plan_org_status(plan_id, organization_id, sr_instance_id,status, status_date)
values (G_PLAN_ID, G_GROUP10, G_SR_INSTANCE_ID, 0, sysdate);
select nvl(max(sd.last_refresh_number),-1)
into l_max_rf_num
from msc_sup_dem_entries sd
where sd.plan_id = G_PLAN_ID
and sd.publisher_id = p_company_id
and sd.publisher_site_id = p_company_site_id
and sd.inventory_item_id = p_item_id
and trunc(sd.key_date) >= trunc(sysdate);
select nvl(max(sd.last_refresh_number),-1)
into l_max_rf_num
from msc_sup_dem_entries sd
where sd.plan_id = G_PLAN_ID
and sd.publisher_id = p_company_id
and sd.publisher_site_id = p_company_site_id
and sd.inventory_item_id = p_item_id
and trunc(sd.key_date) >= trunc(sysdate);
select nvl(max(sd.last_refresh_number),-1)
into l_max_rf_num
from msc_sup_dem_entries sd
where sd.plan_id = G_PLAN_ID
and sd.publisher_id = p_company_id
and sd.publisher_site_id = p_company_site_id
and sd.inventory_item_id = p_item_id
and ((trunc(sd.ship_date) >= trunc(sysdate)) or sd.publisher_order_type = 15);
/* Bug# 4303597 -- added OR condition so that refresh_number of updated ASN is selected */
elsif (p_role = BUYER) then
select nvl(max(sd.last_refresh_number),-1)
into l_max_rf_num
from msc_sup_dem_entries sd
where sd.plan_id = G_PLAN_ID
and sd.publisher_id = p_company_id
and sd.publisher_site_id = p_company_site_id
and sd.inventory_item_id = p_item_id;
PROCEDURE Delete_Item(l_type in varchar2, l_key in varchar2) IS
cursor get_notification_c (l_type in varchar2, l_key in varchar2) IS
select notification_id
from wf_item_activity_statuses
where item_type = l_type
and item_key like l_key
union
select notification_id
from wf_item_activity_statuses_h
where item_type = l_type
and item_key like l_key;
update wf_notifications set
end_date = sysdate
where notification_id = l_notification_id;
update wf_items set
end_date = sysdate
where item_type = l_type
and item_key like l_key;
update wf_item_activity_statuses set
end_date = sysdate
where item_type = l_type
and item_key like l_key;
update wf_item_activity_statuses_h set
end_date = sysdate
where item_type = l_type
and item_key like l_key;
END Delete_Item;
PROCEDURE delete_wf_notification(p_type in varchar2, p_key in varchar2) IS
cursor get_notification_c (p_type In Varchar2,
p_key in varchar2) IS
select (max(notification_id))
from wf_item_activity_statuses
where item_type = p_type
and item_key like p_key || '%'
and notification_id is not null;
FND_FILE.PUT_LINE(FND_FILE.LOG,'inside PROCEDURE delete_wf_notification : l_notification_id = '||l_notification_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in delete wf nid '||sqlerrm);
END delete_wf_notification;
select meaning
into l_message_type
from mfg_lookups
where lookup_type = 'MSC_X_EXCEPTION_TYPE'
and lookup_code = p_exception_code;
select meaning
into l_message_group
from mfg_lookups
where lookup_type = 'MSC_X_EXCEPTION_GROUP'
and lookup_code = p_exception_group;
PROCEDURE UPDATE_EXCEPTIONS_SUMMARY( p_company_id IN Number,
p_company_site_id IN Number,
p_item_id IN Number,
p_exception_type IN Number,
p_exception_group IN Number) IS
l_exception_exists Number;
update_item_exception(p_company_id,
p_company_site_id,
p_item_id,
p_exception_type,
p_exception_group);
END UPDATE_EXCEPTIONS_SUMMARY;
select msc_x_exception_details_s.nextval
into l_ex_dtl_id
from dual;
insert into msc_x_exception_details ( exception_detail_id,
exception_type,
exception_type_name,
exception_group,
exception_group_name,
number3,
date1,
date2,
date3,
order_creation_date1,
order_creation_date2,
transaction_id1,
transaction_id2,
number1,
number2,
threshold,
lead_time,
item_min_qty,
item_max_qty,
version,
plan_id,
sr_instance_id,
company_id,
company_name,
company_site_id,
company_site_name,
inventory_item_id,
item_name,
item_description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
customer_id,
customer_name,
customer_site_id,
customer_site_name,
customer_item_name,
supplier_id,
supplier_name,
supplier_site_id,
supplier_site_name,
supplier_item_name,
order_number,
release_number,
line_number,
end_order_number,
end_order_rel_number,
end_order_line_number
, replenishment_method
)
values (l_ex_dtl_id,
p_exception_type,
p_exception_type_name,
p_exception_group,
p_exception_group_name,
p_quantity3,
trunc(p_actual_date),
trunc(p_tp_actual_date),
trunc(p_other_date),
trunc(p_creation_date),
trunc(p_tp_creation_date),
p_trx_id1,
p_trx_id2,
p_quantity1,
p_quantity2,
p_threshold,
p_lead_time,
p_item_min_qty,
p_item_max_qty,
'CURRENT',
G_PLAN_ID,
G_SR_INSTANCE_ID,
p_company_id,
p_company_name,
p_company_site_id,
p_company_site_name,
p_item_id,
p_item_name,
p_item_description,
sysdate,
l_user_id,
G_MAGIC_NUMBER,
sysdate,
l_user_id,
p_customer_id,
p_customer_name,
p_customer_site_id,
p_customer_site_name,
p_customer_item_name,
p_supplier_id,
p_supplier_name,
p_supplier_site_id,
p_supplier_site_name,
p_supplier_item_name,
p_order_number,
p_release_number,
p_line_number,
p_end_order_number,
p_end_order_rel_number,
p_end_order_line_number
, p_replenishment_method
);
PROCEDURE UPDATE_ITEM_EXCEPTION( p_company_id IN Number,
p_company_site_id IN Number,
p_item_id IN Number,
p_exception_type IN Number,
p_exception_group IN Number) IS
BEGIN
--dbms_output.put_line('Update msc_item_exceptions');
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count + 1,
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_exception_type
and ex.exception_group = p_exception_group
and ex.version = 0;
END UPDATE_ITEM_EXCEPTION;
insert into msc_item_exceptions( plan_id,
sr_instance_id,
company_id,
company_site_id,
organization_id,
inventory_item_id,
version,
exception_type,
exception_group,
exception_count,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
values (G_PLAN_ID,
G_SR_INSTANCE_ID,
p_company_id,
p_company_site_id,
p_company_site_id,
p_item_id,
l_version,
p_exception_type,
p_exception_group,
1,
sysdate,
-1,
sysdate,
-1,
G_MAGIC_NUMBER);
select 1 into l_ret_flag
from dual
where exists ( select 1
from msc_item_exceptions ex
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_exception_type
and ex.exception_group = p_exception_group
and ex.version = 0
--and nvl(ex.last_update_login,-1) = G_MAGIC_NUMBER
);
SELECT sum(sd.tp_quantity)
INTO l_total_qty
FROM msc_sup_dem_entries sd
WHERE sd.plan_id = G_PLAN_ID
AND sd.publisher_id = p_company_id
AND sd.publisher_site_id = p_company_site_id
AND sd.inventory_item_id = p_item_id
AND sd.customer_id = p_tp_id
AND sd.customer_site_id = p_tp_site_id
AND sd.publisher_order_type = SALES_ORDER
AND sd.end_order_number = p_order_number
AND nvl(sd.end_order_rel_number, -1) = nvl(p_release_number, -1)
AND nvl(sd.end_order_line_number, -1) = nvl(p_line_number, -1);
SELECT 1
INTO l_return_code
FROM dual
WHERE EXISTS (SELECT sd.order_number
FROM msc_sup_dem_entries sd
WHERE sd.plan_id = G_PLAN_ID
AND sd.publisher_id = p_company_id
AND sd.publisher_site_id = p_company_site_id
AND sd.publisher_order_type = SALES_ORDER
AND sd.end_order_number = p_order_number
AND nvl(sd.end_order_rel_number, -1) =
nvl(p_release_number, -1)
AND nvl(sd.end_order_line_number, -1) =
nvl(p_line_number, -1)
AND sd.customer_id = p_tp_id
AND sd.customer_site_id = p_tp_site_id
AND sd.inventory_item_id = p_item_id);
SELECT 1
INTO l_return_code
FROM dual
WHERE EXISTS (SELECT sd.order_number
FROM msc_sup_dem_entries sd
WHERE sd.plan_id = G_PLAN_ID
AND sd.publisher_id = p_company_id
AND sd.publisher_site_id = p_company_site_id
AND sd.publisher_order_type = PURCHASE_ORDER
AND sd.order_number = p_end_order_number
AND nvl(sd.release_number, -1) =
nvl(p_end_order_rel_number, -1)
AND nvl(sd.line_number, -1) =
nvl(p_end_order_line_number, -1)
AND sd.supplier_id = p_tp_id
AND sd.supplier_site_id = p_tp_site_id
AND sd.inventory_item_id = p_item_id);
SELECT 1
INTO l_return_code
FROM dual
WHERE EXISTS (SELECT sd.order_number
FROM msc_sup_dem_entries sd
WHERE sd.plan_id = G_PLAN_ID
AND sd.publisher_id = p_company_id
AND sd.publisher_site_id = p_company_site_id
AND sd.publisher_order_type = SHIPMENT_RECEIPT
AND sd.end_order_number = p_order_number
AND nvl(sd.end_order_rel_number, -1) =
nvl(p_release_number, -1)
AND nvl(sd.end_order_line_number, -1) =
nvl(p_line_number, -1)
AND sd.supplier_id = p_tp_id
AND sd.supplier_site_id = p_tp_site_id
AND sd.inventory_item_id = p_item_id);
SELECT ed.exception_detail_id
INTO l_exception_detail_id
FROM msc_x_exception_details ed
WHERE ed.plan_id = G_PLAN_ID
AND ed.inventory_item_id = p_item_id
AND ed.company_id = p_company_id
AND ed.company_site_id = p_company_site_id
AND ed.exception_type = p_exception_type
AND ed.transaction_id1 = p_trx_id1;
SELECT ed.exception_detail_id
INTO l_exception_detail_id
FROM msc_x_exception_details ed
WHERE ed.plan_id = G_PLAN_ID
AND ed.inventory_item_id = p_item_id
AND ed.company_id = p_company_id
AND ed.company_site_id = p_company_site_id
AND ed.exception_type = p_exception_type
AND ed.transaction_id1 = p_trx_id1
AND ed.transaction_id2 = p_trx_id2;
SELECT 1
INTO l_ret_flag
FROM dual
WHERE EXISTS (SELECT 'x'
FROM msc_x_exception_details
WHERE plan_id = G_PLAN_ID
AND company_id = p_company_id
AND company_site_id = p_company_site_id
AND inventory_item_id = p_item_id
AND exception_type = p_exception_type
AND transaction_id1 = p_trx_id);
PROCEDURE DELETE_EXEC_ORDER_DEPENDENCY (p_refresh_number IN Number) IS
CURSOR delete_entry_c IS
SELECT distinct sd.transaction_id,
med.company_id,
med.company_site_id,
med.inventory_item_id,
med.customer_id,
med.customer_site_id,
med.supplier_id,
med.supplier_site_id,
med.exception_group,
med.exception_type,
med.exception_detail_id
FROM msc_sup_dem_entries sd, msc_x_exception_details med
WHERE sd.plan_id = G_PLAN_ID
AND sd.quantity = 0
AND nvl(sd.last_update_login,-1) = -99
AND sd.last_refresh_number >= p_refresh_number
AND med.plan_id = sd.plan_id
AND med.transaction_id1 = sd.transaction_id
UNION
SELECT distinct sd.transaction_id,
med.company_id,
med.company_site_id,
med.inventory_item_id,
med.customer_id,
med.customer_site_id,
med.supplier_id,
med.supplier_site_id,
med.exception_group,
med.exception_type,
med.exception_detail_id
FROM msc_sup_dem_entries sd, msc_x_exception_details med
WHERE sd.plan_id = G_PLAN_ID
AND sd.quantity = 0
AND nvl(sd.last_update_login, -1) = -99
AND sd.last_refresh_number >= p_refresh_number
AND med.plan_id = sd.plan_id
AND med.transaction_id2 = sd.transaction_id;
open delete_entry_c;
fetch delete_entry_c into l_transaction_id,
l_company_id,
l_company_site_id,
l_item_id,
l_customer_id,
l_customer_site_id,
l_supplier_id,
l_supplier_site_id,
l_exception_group,
l_exception_type,
l_exception_detail_id;
exit when delete_entry_c%NOTFOUND;
delete_wf_notification(l_item_type, l_item_key);
delete msc_x_exception_details
where plan_id = G_PLAN_ID
and company_id = l_company_id
and company_site_id = l_company_site_id
and inventory_item_id = l_item_id
and exception_type = l_exception_type
and exception_detail_id = l_exception_detail_id;
update msc_item_exceptions
set exception_count = exception_count - l_row,
last_update_date = sysdate
where plan_id = G_PLAN_ID
and company_id = l_company_id
and company_site_id = l_company_site_id
and inventory_item_id = l_item_id
and exception_type = l_exception_type;
close delete_entry_c;
END DELETE_EXEC_ORDER_DEPENDENCY;
PROCEDURE DELETE_EXCEP IS
l_company_id Number;
CURSOR delete_excep_c (p_days in number)IS
SELECT company_id,
company_site_id,
inventory_item_id,
customer_id,
customer_site_id,
supplier_id,
supplier_site_id,
exception_group,
exception_type,
exception_detail_id
FROM msc_x_exception_details
WHERE nvl(plan_id,G_PLAN_ID) = G_PLAN_ID --6603563
and trunc(last_update_date ) < trunc(sysdate) - p_days;
open delete_excep_c(p_days);
fetch delete_excep_c into l_company_id,
l_company_site_id,
l_item_id,
l_customer_id,
l_customer_site_id,
l_supplier_id,
l_supplier_site_id,
l_exception_group,
l_exception_type,
l_exception_detail_id;
exit when delete_excep_c%NOTFOUND;
delete_wf_notification(l_item_type, l_item_key);
delete msc_x_exception_details
where exception_detail_id = l_exception_detail_id;
update msc_item_exceptions
set exception_count = exception_count - l_row,
last_update_date = sysdate
where plan_id = G_PLAN_ID
and company_id = l_company_id
and company_site_id = l_company_site_id
and inventory_item_id = l_item_id
and exception_type = l_exception_type;
close delete_excep_c;
END DELETE_EXCEP;
DELETE /*+ PARALLEL(sd) */ from msc_sup_dem_entries sd
WHERE sd.plan_id = G_PLAN_ID
AND sd.quantity = 0
AND sd.publisher_order_type in
(PURCHASE_ORDER,SALES_ORDER,ASN,SHIPMENT_RECEIPT)
AND nvl(last_update_login,-1) = -99
AND sd.last_refresh_number >= p_refresh_number;
PROCEDURE delete_obsolete_exceptions(p_company_id IN Number,
p_company_site_id IN Number,
p_customer_id in Number,
p_customer_site_id In Number,
p_supplier_id IN Number,
p_supplier_site_id IN Number,
p_exception_group IN Number,
p_curr_exc_type in Number,
p_obs_exc_type in Number,
p_item_id in Number,
p_bkt_start_date in Date,
p_bkt_end_date in Date,
p_type In Number,
p_transaction_id1 In Number,
p_transaction_id2 IN number
) IS
l_exception_detail_id Number;
delete_wf_notification(l_item_type, l_item_key);
delete_wf_notification(l_item_type, l_item_key);
delete_wf_notification(l_item_type, l_item_key);
delete from msc_x_exception_details ex
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_curr_exc_type
and ex.transaction_id1 = p_transaction_id1
and ex.transaction_id2 = p_transaction_id2
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - l_row,
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_curr_exc_type
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
delete from msc_x_exception_details ex
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_obs_exc_type
and ex.transaction_id1 = p_transaction_id1
and ex.transaction_id2 = p_transaction_id2
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - l_row,
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_obs_exc_type
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
delete from msc_x_exception_details ex
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_curr_exc_type
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - l_row,
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_curr_exc_type
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
delete from msc_x_exception_details ex
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_obs_exc_type
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - l_row,
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_obs_exc_type
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
delete from msc_x_exception_details ex
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_curr_exc_type
and ex.date1 = p_bkt_start_date
and ex.date2 = p_bkt_end_date
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - l_row,
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_curr_exc_type
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
delete from msc_x_exception_details ex
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and nvl(ex.customer_id,-1) = nvl(p_customer_id,-1)
and nvl(ex.customer_site_id,-1) = nvl(p_customer_site_id,-1)
and nvl(ex.supplier_id,-1) = nvl(p_supplier_id, -1)
and nvl(ex.supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_obs_exc_type
and ex.date1 = p_bkt_start_date
and ex.date2 = p_bkt_end_date
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - l_row,
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = p_company_id
and ex.company_site_id = p_company_site_id
and ex.inventory_item_id = p_item_id
and ex.exception_type = p_obs_exc_type
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.delete_obsolete_exceptions');
END delete_obsolete_exceptions;
update msc_item_exceptions ex
set ex.last_update_login = null,
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
--and ex.version = 0
and exception_group in (1,2,3,4,5,6,7,8,9,10)
and nvl(ex.last_update_login,-1) = G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.version = version + 1,
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
--and ex.version = 0
and exception_group in (1,2,3,4,5,6,7,8,9,10)
and ex.exception_count = 0;
delete msc_item_exceptions ex
where plan_id = G_PLAN_ID
and exception_group in (1,2,3,4,5,6,7,8,9,10)
and version > 20;
insert into msc_x_exception_details (
exception_detail_id,
exception_type,
exception_type_name,
exception_group,
exception_group_name,
date1,
date2,
date3,
date4,
date5,
order_creation_date1,
order_creation_date2,
transaction_id1,
transaction_id2,
number1,
number2,
number3,
threshold,
lead_time,
item_min_qty,
item_max_qty,
version,
plan_id,
sr_instance_id,
company_id,
company_name,
company_site_id,
company_site_name,
inventory_item_id,
item_name,
item_description,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
customer_id,
customer_name,
customer_site_id,
customer_site_name,
customer_item_name,
supplier_id,
supplier_name,
supplier_site_id,
supplier_site_name,
supplier_item_name,
order_number,
release_number,
line_number,
end_order_number,
end_order_rel_number,
end_order_line_number,
exception_basis
)
values (
msc_x_exception_details_s.nextval,
a_exception_type(i),
a_exception_type_name(i),
a_exception_group(i),
a_exception_group_name(i),
trunc(a_date1(i)),
trunc(a_date2(i)),
trunc(a_date3(i)),
trunc(a_date4(i)),
trunc(a_date5(i)),
trunc(a_creation_date(i)),
trunc(a_tp_creation_date(i)),
a_trx_id1(i),
a_trx_id2(i),
a_number1(i),
a_number2(i),
a_number3(i),
a_threshold(i),
a_lead_time(i),
a_item_min_qty(i),
a_item_max_qty(i),
'CURRENT',
G_PLAN_ID,
G_SR_INSTANCE_ID,
a_company_id(i),
a_company_name(i),
a_company_site_id(i),
a_company_site_name(i),
a_item_id(i),
a_item_name(i),
a_item_desc(i),
sysdate,
fnd_global.user_id,
G_MAGIC_NUMBER,
sysdate,
fnd_global.user_id,
a_customer_id(i),
a_customer_name(i),
a_customer_site_id(i),
a_customer_site_name(i),
a_customer_item_name(i),
a_supplier_id(i),
a_supplier_name(i),
a_supplier_site_id(i),
a_supplier_site_name(i),
a_supplier_item_name(i),
a_order_number(i),
a_release_number(i),
a_line_number(i),
a_end_order_number(i),
a_end_order_rel_number(i),
a_end_order_line_number(i),
a_exception_basis(i)
);
update msc_item_exceptions
set exception_count = exception_count + 1,
last_update_date = sysdate
where plan_id = G_PLAN_ID
and company_id = a_company_id(i)
and company_site_id = a_company_site_id(i)
and inventory_item_id = a_item_id(i)
and exception_type = a_exception_type(i)
and exception_group = a_exception_group(i)
and version = 0;
insert into msc_item_exceptions( plan_id,
sr_instance_id,
company_id,
company_site_id,
organization_id,
inventory_item_id,
version,
exception_type,
exception_group,
exception_count,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
values (G_PLAN_ID,
G_SR_INSTANCE_ID,
a_company_id(i),
a_company_site_id(i),
-1,
a_item_id(i),
0,
a_exception_type(i),
a_exception_group(i),
1,
sysdate,
-1,
sysdate,
-1,
G_MAGIC_NUMBER
);
PROCEDURE add_to_delete_tbl ( p_company_id in number,
p_company_site_id in number,
p_customer_id in number,
p_customer_site_id in number,
p_supplier_id in number,
p_supplier_site_id in number,
p_item_id in number,
p_group in number,
p_type in number,
p_trxid1 in number,
p_trxid2 in number,
p_date1 in date,
p_date2 in date,
t_company_list IN OUT NOCOPY number_arr,
t_company_site_list IN OUT NOCOPY number_arr,
t_customer_list IN OUT NOCOPY number_arr,
t_customer_site_list IN OUT NOCOPY number_arr,
t_supplier_list IN OUT NOCOPY number_arr,
t_supplier_site_list IN OUT NOCOPY number_arr,
t_item_list IN OUT NOCOPY number_arr,
t_group_list IN OUT NOCOPY number_arr,
t_type_list IN OUT NOCOPY number_arr,
t_trxid1_list IN OUT NOCOPY number_arr,
t_trxid2_list IN OUT NOCOPY number_arr,
t_date1_list IN OUT NOCOPY date_arr,
t_date2_list IN OUT NOCOPY date_arr) IS
l_counter Number := 0;
MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.add_to_delete_tbl');
END add_to_delete_tbl;
delete msc_x_exception_details
where inventory_item_id = t_item_list(i)
and company_id = t_company_list(i)
and company_site_id = t_company_site_list(i)
and nvl(customer_id,-1) = nvl(t_customer_list(i),-1)
and nvl(customer_site_id,-1) = nvl(t_customer_site_list(i),-1)
and nvl(supplier_id,-1) = nvl(t_supplier_list(i),-1)
and nvl(supplier_site_id ,-1) = nvl(t_supplier_site_list(i),-1)
and exception_group = t_group_list(i)
and exception_type = t_type_list(i)
and transaction_id1 = t_trxid1_list(i)
and nvl(transaction_id2,-1) = nvl(t_trxid2_list(i),-1)
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER
and nvl(LAST_UPDATE_LOGIN,0) <> -99;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - t_count1(i),
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = t_company_list(i)
and ex.company_site_id = t_company_site_list(i)
and ex.inventory_item_id = t_item_list(i)
and ex.exception_group = t_group_list(i)
and ex.exception_type = t_type_list(i)
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
delete msc_x_exception_details
where inventory_item_id = t_item_list(i)
and company_id = t_company_list(i)
and company_site_id = t_company_site_list(i)
and nvl(customer_id,-1) = nvl(t_customer_list(i),-1)
and nvl(customer_site_id,-1) = nvl(t_customer_site_list(i),-1)
and nvl(supplier_id,-1) = nvl(t_supplier_list(i),-1)
and nvl(supplier_site_id ,-1) = nvl(t_supplier_site_list(i),-1)
and exception_group = t_group_list(i)
and exception_type = t_type_list(i)
and transaction_id1 is null
and transaction_id2 is null
and t_date1_list(i) is not null
and t_date2_list(i) is not null
and date1 = t_date1_list(i)
and date2 = t_date2_list(i)
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - t_count2(i),
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = t_company_list(i)
and ex.company_site_id = t_company_site_list(i)
and ex.inventory_item_id = t_item_list(i)
and ex.exception_group = t_group_list(i)
and ex.exception_type = t_type_list(i)
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
delete msc_x_exception_details
where inventory_item_id = t_item_list(i)
and company_id = t_company_list(i)
and company_site_id = t_company_site_list(i)
and nvl(customer_id,-1) = nvl(t_customer_list(i),-1)
and nvl(customer_site_id,-1) = nvl(t_customer_site_list(i),-1)
and nvl(supplier_id,-1) = nvl(t_supplier_list(i),-1)
and nvl(supplier_site_id ,-1) = nvl(t_supplier_site_list(i),-1)
and exception_group = t_group_list(i)
and exception_type = t_type_list(i)
and transaction_id1 is null
and transaction_id2 is null
and t_date1_list(i) is null
and t_date2_list(i) is null
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - t_count3(i),
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = t_company_list(i)
and ex.company_site_id = t_company_site_list(i)
and ex.inventory_item_id = t_item_list(i)
and ex.exception_group = t_group_list(i)
and ex.exception_type = t_type_list(i)
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
delete msc_x_exception_details
where inventory_item_id = t_item_list(i)
and company_id = t_company_list(i)
and company_site_id = t_company_site_list(i)
and nvl(customer_id,-1) = nvl(t_customer_list(i),-1)
and nvl(customer_site_id,-1) = nvl(t_customer_site_list(i),-1)
and nvl(supplier_id,-1) = nvl(t_supplier_list(i),-1)
and nvl(supplier_site_id ,-1) = nvl(t_supplier_site_list(i),-1)
and exception_group = msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER
and exception_type = msc_x_netting_pkg.G_EXCEP13
and transaction_id1 = t_trxid1_list(i)
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
update msc_item_exceptions ex
set ex.exception_count = ex.exception_count - t_count1(i),
ex.last_update_date = sysdate
where ex.plan_id = G_PLAN_ID
and ex.company_id = t_company_list(i)
and ex.company_site_id = t_company_site_list(i)
and ex.inventory_item_id = msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER
and ex.exception_group = msc_x_netting_pkg.G_EXCEP13
and ex.exception_type = t_type_list(i)
and ex.version = 0
and nvl(last_update_login,-1) <> G_MAGIC_NUMBER;
delete_wf_notification(l_item_type, l_item_key);
PROCEDURE update_item (p_refresh_number in Number) IS
cursor update_item_c1 IS
select distinct publisher_id,
publisher_site_id,
customer_id,
customer_site_id,
inventory_item_id,
item_name,
item_description,
customer_item_name,
supplier_item_name
from msc_sup_dem_entries sd
where plan_id = -1
and publisher_order_type in (3,14)
and last_refresh_number > p_refresh_number
and exists (select 1
from msc_x_exception_details
where plan_id = -1
and inventory_item_id = sd.inventory_item_id
and exception_type in (5,25)
and company_id = sd.publisher_id
and company_site_id = sd.publisher_site_id
and customer_id = sd.customer_id
and customer_site_id = sd.customer_site_id)
union all
select distinct supplier_id,
supplier_site_id,
publisher_id,
publisher_site_id,
inventory_item_id,
item_name,
item_description,
customer_item_name,
supplier_item_name
from msc_sup_dem_entries sd
where plan_id = -1
and publisher_order_type = 2
and last_refresh_number > p_refresh_number
and exists (select 1
from msc_x_exception_details
where plan_id = -1
and inventory_item_id = sd.inventory_item_id
and exception_type in (5,25)
and company_id = sd.supplier_id
and company_site_id = sd.supplier_site_id
and customer_id = sd.publisher_id
and customer_site_id = sd.publisher_site_id);
cursor update_item_c2 IS
select distinct publisher_id,
publisher_site_id,
supplier_id,
supplier_site_id,
inventory_item_id,
item_name,
item_description,
customer_item_name,
supplier_item_name
from msc_sup_dem_entries sd
where plan_id = -1
and publisher_order_type = 2
and last_refresh_number > p_refresh_number
and exists (select 1
from msc_x_exception_details
where plan_id = -1
and inventory_item_id = sd.inventory_item_id
and exception_type in (6,26)
and company_id = sd.publisher_id
and company_site_id = sd.publisher_site_id
and supplier_id = sd.supplier_id
and supplier_site_id = sd.supplier_site_id)
union all
select distinct customer_id,
customer_site_id,
publisher_id,
publisher_site_id,
inventory_item_id,
item_name,
item_description,
customer_item_name,
supplier_item_name
from msc_sup_dem_entries sd
where plan_id = -1
and publisher_order_type in (3,14)
and last_refresh_number > p_refresh_number
and exists (select 1
from msc_x_exception_details
where plan_id = -1
and inventory_item_id = sd.inventory_item_id
and exception_type in (6,26)
and company_id = sd.customer_id
and company_site_id = sd.customer_site_id
and supplier_id = sd.publisher_id
and supplier_site_id = sd.publisher_site_id);
open update_item_c1;
fetch update_item_c1 bulk collect into
b_publisher_id,
b_publisher_site_id,
b_customer_id,
b_customer_site_id,
b_item_id,
b_item_name,
b_item_desc,
b_customer_item_name,
b_supplier_item_name;
close update_item_c1;
update msc_x_exception_details
set item_name = b_item_name(j),
item_description = b_item_desc(j),
customer_item_name = b_customer_item_name(j),
supplier_item_name = b_supplier_item_name(j)
where plan_id = -1
and company_id = b_publisher_id(j)
and company_site_id = b_publisher_site_id(j)
and customer_id = b_customer_id(j)
and customer_site_id = b_customer_site_id(j)
and inventory_item_id = b_item_id(j)
and exception_type in (5,25);
open update_item_c2;
fetch update_item_c2 bulk collect into
b_publisher_id,
b_publisher_site_id,
b_supplier_id,
b_supplier_site_id,
b_item_id,
b_item_name,
b_item_desc,
b_customer_item_name,
b_supplier_item_name;
close update_item_c2;
update msc_x_exception_details
set item_name = b_item_name(j),
item_description = b_item_desc(j),
customer_item_name = b_customer_item_name(j),
supplier_item_name = b_supplier_item_name(j)
where plan_id = -1
and company_id = b_publisher_id(j)
and company_site_id = b_publisher_site_id(j)
and supplier_id = b_supplier_id(j)
and supplier_site_id = b_supplier_site_id(j)
and inventory_item_id = b_item_id(j)
and exception_type in (6,26);
MSC_SCE_LOADS_PKG.LOG_MESSAGE('Error in MSC_X_NETTING_PKG.update_item');
END UPDATE_ITEM;