[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT trunc(b.bkt_start_date), trunc(b.bkt_end_date), b.bucket_type
FROM msc_plan_buckets b
WHERE b.plan_id = msc_x_netting_pkg.G_PLAN_ID;
select sd.publisher_id,
sd.publisher_site_id,
sd.supplier_id,
sd.supplier_site_id,
sd.customer_id,
sd.customer_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.SUPPLY_COMMIT,
sd.publisher_name,
sd.publisher_site_name,
sd.supplier_name,
sd.supplier_site_name,
sd.customer_name,
sd.customer_site_name,
null,
null,
null,
null,
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
nvl(max(sd.last_refresh_number),0)
from msc_sup_dem_entries sd,
msc_cp_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = bkt.supplier_id
and sd.publisher_site_id = bkt.supplier_site_id
and sd.customer_id = bkt.customer_id
and sd.customer_site_id = bkt.customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
group by sd.publisher_id,
sd.publisher_site_id,
sd.supplier_id,
sd.supplier_site_id,
sd.customer_id,
sd.customer_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
sd.publisher_name,
sd.publisher_site_name,
sd.supplier_name,
sd.supplier_site_name,
sd.customer_name,
sd.customer_site_name
UNION ALL
select sd.supplier_id,
sd.supplier_site_id,
sd.supplier_id,
sd.supplier_site_id,
sd.publisher_id,
sd.publisher_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.ORDER_FORECAST,
sd.supplier_name,
sd.supplier_site_name,
sd.supplier_name,
sd.supplier_site_name,
sd.publisher_name,
sd.publisher_site_name,
null,
null,
null,
null,
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
nvl(max(sd.last_refresh_number),0)
from msc_sup_dem_entries sd,
msc_cp_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = bkt.customer_id
and sd.publisher_site_id = bkt.customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
and sd.supplier_id = bkt.supplier_id
and sd.supplier_site_id = bkt.supplier_site_id
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
group by sd.supplier_id,
sd.supplier_site_id,
sd.supplier_id,
sd.supplier_site_id,
sd.publisher_id,
sd.publisher_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
sd.supplier_name,
sd.supplier_site_name,
sd.supplier_name,
sd.supplier_site_name,
sd.publisher_name,
sd.publisher_site_name
order by 1,2,3,4,5,6,7,8,9;
select sd.publisher_id,
sd.publisher_site_id,
sd.customer_id,
sd.customer_site_id,
sd.supplier_id,
sd.supplier_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.ORDER_FORECAST,
sd.publisher_name,
sd.publisher_site_name,
sd.customer_name,
sd.customer_site_name,
sd.supplier_name,
sd.supplier_site_name,
null,
null,
null,
null,
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
nvl(max(sd.last_refresh_number),0)
from msc_sup_dem_entries sd,
msc_cp_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = bkt.customer_id
and sd.publisher_site_id = bkt.customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
and sd.supplier_id = bkt.supplier_id
and sd.supplier_site_id = bkt.supplier_site_id
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
group by sd.publisher_id,
sd.publisher_site_id,
sd.customer_id,
sd.customer_site_id,
sd.supplier_id,
sd.supplier_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
sd.publisher_name,
sd.publisher_site_name,
sd.customer_name,
sd.customer_site_name,
sd.supplier_name,
sd.supplier_site_name
UNION ALL
select sd.customer_id,
sd.customer_site_id,
sd.customer_id,
sd.customer_site_id,
sd.publisher_id,
sd.publisher_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.SUPPLY_COMMIT,
sd.customer_name,
sd.customer_site_name,
sd.customer_name,
sd.customer_site_name,
sd.publisher_name,
sd.publisher_site_name,
null,
null,
null,
null,
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
nvl(max(sd.last_refresh_number),0)
from msc_sup_dem_entries sd,
msc_cp_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = bkt.supplier_id
and sd.publisher_site_id = bkt.supplier_site_id
and sd.customer_id = bkt.customer_id
and sd.customer_site_id = bkt.customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
group by sd.customer_id,
sd.customer_site_id,
sd.customer_id,
sd.customer_site_id,
sd.publisher_id,
sd.publisher_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
sd.customer_name,
sd.customer_site_name,
sd.customer_name,
sd.customer_site_name,
sd.publisher_name,
sd.publisher_site_name
order by 1,2,3,4,5,6,7,8,9;
select nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.ORDER_FORECAST
from msc_sup_dem_entries sd,
msc_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = p_customer_id
and sd.publisher_site_id = p_customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
and sd.supplier_id = p_supplier_id
and sd.supplier_site_id = p_supplier_site_id
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
and sd.last_refresh_number <= p_cutoff_ref_num
group by bkt.bucket_index, bkt.bkt_start_date, bkt.bkt_end_date
UNION ALL
select nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.SUPPLY_COMMIT
from msc_sup_dem_entries sd,
msc_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = p_supplier_id
and sd.publisher_site_id = p_supplier_site_id
and sd.customer_id = p_customer_id
and sd.customer_site_id = p_customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = p_item_id
and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
and sd.last_refresh_number <= p_cutoff_ref_num
group by bkt.bucket_index, bkt.bkt_start_date, bkt.bkt_end_date
order by 4,5,6;
SELECT distinct sd1.transaction_id,
sd1.publisher_id,
sd1.publisher_name,
sd1.publisher_site_id,
sd1.publisher_site_name,
sd1.inventory_item_id,
sd1.item_name,
sd1.item_description,
sd1.supplier_item_name,
sd1.supplier_item_description,
sd1.key_date,
sd1.ship_date,
sd1.receipt_date,
sd1.quantity,
sd1.primary_quantity,
sd1.tp_quantity,
sd1.order_number,
sd1.release_number,
sd1.line_number,
sd1.customer_id,
sd1.customer_name,
sd1.customer_site_id,
sd1.customer_site_name,
sd1.customer_item_name,
sd1.customer_item_description,
sd1.creation_date,
sd1.last_refresh_number,
sd2.transaction_id,
sd2.key_date,
sd2.ship_date,
sd2.receipt_date,
sd2.quantity,
sd2.primary_quantity,
sd2.tp_quantity,
sd2.order_number,
sd2.release_number,
sd2.line_number,
sd2.supplier_id,
sd2.supplier_name,
sd2.supplier_site_id,
sd2.supplier_site_name,
sd2.creation_date,
sd2.last_refresh_number
FROM msc_sup_dem_entries sd1,
msc_sup_dem_entries sd2
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
AND sd2.plan_id = sd1.plan_id
AND sd2.inventory_item_id = sd1.inventory_item_id
AND sd2.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND sd2.supplier_id = sd1.publisher_id
AND sd2.supplier_site_id = sd1.publisher_site_id
AND sd2.publisher_id = sd1.customer_id
AND sd2.publisher_site_id = sd1.customer_site_id
AND sd1.end_order_number = sd2.order_number
AND nvl(sd1.end_order_rel_number,-1) =
nvl(sd2.release_number,-1)
AND nvl(sd1.end_order_line_number,-1) =
nvl(sd2.line_number,-1)
AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
order by sd1.publisher_id, sd1.publisher_site_id,
sd1.customer_id, sd1.customer_site_id, sd1.inventory_item_id,
sd2.order_number, sd2.release_number, sd2.line_number, sd1.key_date desc;
SELECT distinct sd1.transaction_id,
sd1.publisher_id,
sd1.publisher_name,
sd1.publisher_site_id,
sd1.publisher_site_name,
sd1.inventory_item_id,
sd1.item_name,
sd1.item_description,
sd2.customer_item_name,
sd2.customer_item_description,
sd1.key_date,
sd1.ship_date,
sd1.receipt_date,
sd1.quantity,
sd1.primary_quantity,
sd1.tp_quantity,
sd1.order_number,
sd1.release_number,
sd1.line_number,
sd1.supplier_id,
sd1.supplier_name,
sd1.supplier_site_id,
sd1.supplier_site_name,
sd1.supplier_item_name,
sd1.supplier_item_description,
sd1.creation_date,
sd1.last_refresh_number,
sd2.transaction_id,
sd2.key_date,
sd2.ship_date,
sd2.receipt_date,
sd2.quantity,
sd2.primary_quantity,
sd2.tp_quantity,
sd2.order_number,
sd2.release_number,
sd2.line_number,
sd2.customer_id,
sd2.customer_name,
sd2.customer_site_id,
sd2.customer_site_name,
sd2.creation_date,
sd2.last_refresh_number
FROM msc_sup_dem_entries sd1,
msc_sup_dem_entries sd2
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND sd2.plan_id = sd1.plan_id
AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
AND sd2.customer_id = sd1.publisher_id
AND sd2.customer_site_id = sd1.publisher_site_id
AND sd2.publisher_id = sd1.supplier_id
AND sd2.publisher_site_id = sd1.supplier_site_id
AND sd2.inventory_item_id = sd1.inventory_item_id
AND sd1.order_number = sd2.end_order_number
AND nvl(sd1.release_number,-1) =
nvl(sd2.end_order_rel_number,-1)
AND nvl(sd1.line_number,-1) =
nvl(sd2.end_order_line_number,-1)
AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
order by sd1.publisher_id, sd1.publisher_site_id,
sd1.supplier_id, sd1.supplier_site_id, sd1.inventory_item_id,
sd1.order_number, sd1.release_number, sd1.line_number, sd2.key_date desc;
select sd.publisher_id,
sd.publisher_site_id,
sd.supplier_id,
sd.supplier_site_id,
sd.customer_id,
sd.customer_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.SUPPLY_COMMIT,
sd.publisher_name,
sd.publisher_site_name,
sd.supplier_name,
sd.supplier_site_name,
sd.customer_name,
sd.customer_site_name,
null,
null,
null,
null,
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
nvl(max(sd.last_refresh_number),0)
from msc_sup_dem_entries sd,
msc_cp_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = bkt.supplier_id
and sd.publisher_site_id = bkt.supplier_site_id
and sd.customer_id = bkt.customer_id
and sd.customer_site_id = bkt.customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
group by sd.publisher_id,
sd.publisher_site_id,
sd.supplier_id,
sd.supplier_site_id,
sd.customer_id,
sd.customer_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
sd.publisher_name,
sd.publisher_site_name,
sd.supplier_name,
sd.supplier_site_name,
sd.customer_name,
sd.customer_site_name
UNION ALL
select sd.supplier_id,
sd.supplier_site_id,
sd.supplier_id,
sd.supplier_site_id,
sd.publisher_id,
sd.publisher_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.ORDER_FORECAST,
sd.supplier_name,
sd.supplier_site_name,
sd.supplier_name,
sd.supplier_site_name,
sd.publisher_name,
sd.publisher_site_name,
null,
null,
null,
null,
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
nvl(max(sd.last_refresh_number),0)
from msc_sup_dem_entries sd,
msc_cp_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = bkt.customer_id
and sd.publisher_site_id = bkt.customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
and sd.supplier_id = bkt.supplier_id
and sd.supplier_site_id = bkt.supplier_site_id
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
group by sd.supplier_id,
sd.supplier_site_id,
sd.supplier_id,
sd.supplier_site_id,
sd.publisher_id,
sd.publisher_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
sd.supplier_name,
sd.supplier_site_name,
sd.supplier_name,
sd.supplier_site_name,
sd.publisher_name,
sd.publisher_site_name
order by 1,2,3,4,5,6,7,8,9;
select sd.publisher_id,
sd.publisher_site_id,
sd.customer_id,
sd.customer_site_id,
sd.supplier_id,
sd.supplier_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.ORDER_FORECAST,
sd.publisher_name,
sd.publisher_site_name,
sd.customer_name,
sd.customer_site_name,
sd.supplier_name,
sd.supplier_site_name,
null,
null,
null,
null,
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
nvl(max(sd.last_refresh_number),0)
from msc_sup_dem_entries sd,
msc_cp_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = bkt.customer_id
and sd.publisher_site_id = bkt.customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
and sd.publisher_order_type = msc_x_netting_pkg.ORDER_FORECAST
and sd.supplier_id = bkt.supplier_id
and sd.supplier_site_id = bkt.supplier_site_id
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
group by sd.publisher_id,
sd.publisher_site_id,
sd.customer_id,
sd.customer_site_id,
sd.supplier_id,
sd.supplier_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
sd.publisher_name,
sd.publisher_site_name,
sd.customer_name,
sd.customer_site_name,
sd.supplier_name,
sd.supplier_site_name
UNION ALL
select sd.customer_id,
sd.customer_site_id,
sd.customer_id,
sd.customer_site_id,
sd.publisher_id,
sd.publisher_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
msc_x_netting_pkg.SUPPLY_COMMIT,
sd.customer_name,
sd.customer_site_name,
sd.customer_name,
sd.customer_site_name,
sd.publisher_name,
sd.publisher_site_name,
null,
null,
null,
null,
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0),
nvl(max(sd.last_refresh_number),0)
from msc_sup_dem_entries sd,
msc_cp_plan_buckets bkt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = bkt.supplier_id
and sd.publisher_site_id = bkt.supplier_site_id
and sd.customer_id = bkt.customer_id
and sd.customer_site_id = bkt.customer_site_id
and nvl(sd.base_item_id,sd.inventory_item_id) = bkt.inventory_item_id
and sd.publisher_order_type = msc_x_netting_pkg.SUPPLY_COMMIT
and sd.plan_id = bkt.plan_id
and trunc(sd.key_date) between bkt.bkt_start_date and bkt.bkt_end_date
group by sd.customer_id,
sd.customer_site_id,
sd.customer_id,
sd.customer_site_id,
sd.publisher_id,
sd.publisher_site_id,
nvl(sd.base_item_id,sd.inventory_item_id),
bkt.bucket_index,
bkt.bkt_start_date,
bkt.bkt_end_date,
sd.customer_name,
sd.customer_site_name,
sd.customer_name,
sd.customer_site_name,
sd.publisher_name,
sd.publisher_site_name
order by 1,2,3,4,5,6,7,8,9;
SELECT distinct sd1.transaction_id,
sd1.publisher_id,
sd1.publisher_name,
sd1.publisher_site_id,
sd1.publisher_site_name,
sd1.inventory_item_id,
sd1.item_name,
sd1.item_description,
sd2.supplier_item_name,
sd2.supplier_item_description,
sd1.key_date,
sd1.ship_date,
sd1.receipt_date,
sd1.quantity,
sd1.primary_quantity,
sd1.tp_quantity,
sd1.order_number,
sd1.release_number,
sd1.line_number,
sd1.customer_id,
sd1.customer_name,
sd1.customer_site_id,
sd1.customer_site_name,
sd1.customer_item_name,
sd1.customer_item_description,
sd1.creation_date,
sd1.last_refresh_number,
sd2.transaction_id,
sd2.key_date,
sd2.ship_date,
sd2.receipt_date,
sd2.quantity,
sd2.primary_quantity,
sd2.tp_quantity,
sd2.order_number,
sd2.release_number,
sd2.line_number,
sd2.supplier_id,
sd2.supplier_name,
sd2.supplier_site_id,
sd2.supplier_site_name,
sd2.creation_date,
sd2.last_refresh_number
FROM msc_sup_dem_entries sd1,
msc_sup_dem_entries sd2
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
AND sd2.plan_id = sd1.plan_id
AND sd2.inventory_item_id = sd1.inventory_item_id
AND sd2.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND sd2.supplier_id = sd1.publisher_id
AND sd2.supplier_site_id = sd1.publisher_site_id
AND sd2.publisher_id = sd1.customer_id
AND sd2.publisher_site_id = sd1.customer_site_id
AND sd1.end_order_number = sd2.order_number
AND nvl(sd1.end_order_rel_number,-1) =
nvl(sd2.release_number,-1)
AND nvl(sd1.end_order_line_number,-1) =
nvl(sd2.line_number,-1)
AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
order by sd1.publisher_id, sd1.publisher_site_id,
sd1.customer_id, sd1.customer_site_id, sd1.inventory_item_id,
sd2.order_number, sd2.release_number, sd2.line_number, sd1.key_date desc;
SELECT distinct sd1.transaction_id,
sd1.publisher_id,
sd1.publisher_name,
sd1.publisher_site_id,
sd1.publisher_site_name,
sd1.inventory_item_id,
sd1.item_name,
sd1.item_description,
sd2.customer_item_name,
sd2.customer_item_description,
sd1.key_date,
sd1.ship_date,
sd1.receipt_date,
sd1.quantity,
sd1.primary_quantity,
sd1.tp_quantity,
sd1.order_number,
sd1.release_number,
sd1.line_number,
sd1.supplier_id,
sd1.supplier_name,
sd1.supplier_site_id,
sd1.supplier_site_name,
sd1.supplier_item_name,
sd1.supplier_item_description,
sd1.creation_date,
sd1.last_refresh_number,
sd2.transaction_id,
sd2.key_date,
sd2.ship_date,
sd2.receipt_date,
sd2.quantity,
sd2.primary_quantity,
sd2.tp_quantity,
sd2.order_number,
sd2.release_number,
sd2.line_number,
sd2.customer_id,
sd2.customer_name,
sd2.customer_site_id,
sd2.customer_site_name,
sd2.creation_date,
sd2.last_refresh_number
FROM msc_sup_dem_entries sd1,
msc_sup_dem_entries sd2
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND sd2.plan_id = sd1.plan_id
AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
AND sd2.customer_id = sd1.publisher_id
AND sd2.customer_site_id = sd1.publisher_site_id
AND sd2.publisher_id = sd1.supplier_id
AND sd2.publisher_site_id = sd1.supplier_site_id
AND sd2.inventory_item_id = sd1.inventory_item_id
AND sd1.order_number = sd2.end_order_number
AND nvl(sd1.release_number,-1) =
nvl(sd2.end_order_rel_number,-1)
AND nvl(sd1.line_number,-1) =
nvl(sd2.end_order_line_number,-1)
AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
order by sd1.publisher_id, sd1.publisher_site_id,
sd1.supplier_id, sd1.supplier_site_id, sd1.inventory_item_id,
sd1.order_number, sd1.release_number, sd1.line_number, sd2.key_date desc;
l_updated Number;
l_insert Number := 0;
l_inserted_record Number := 0;
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP5,
null,
null,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_insert := 1;
l_insert := 0;
--dbms_output.put_line('2 no insert with previous line l_pair = 1' || b_bkt_start_date(j));
l_insert := 1;
l_insert := 0;
l_insert := 1;
l_insert := 1;
IF ( ((j > 1 and l_insert = 1) OR (b_bucket_index.COUNT = 1)) and
((l_tp_total_demand - l_total_supply) > (l_tp_total_demand * l_threshold1/100)))
and (greatest(b_refresh_number(j),b_refresh_number(j-1)) > p_refresh_number)
THEN --- Bug# 4629582
--======================================================
-- clean up the opposite exception and its complement
--======================================================
msc_x_netting_pkg.add_to_delete_tbl(
l_publisher_id, --b_publisher_id(j),
l_publisher_site_id, --b_publisher_site_id(j),
l_customer_id, --b_customer_id(j),
l_customer_site_id, --b_customer_site_id(j),
null,
null,
l_item_id, --b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP25,
null,
null,
l_start_date,
l_end_date,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_inserted_record := l_inserted_record + 1;
Loop for the last record if require to insert
------------------------------------------------------------------*/
IF ( j > 1 and l_pair = 0 and j = b_bucket_index.COUNT) THEN
l_insert := 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, '5:insert' || 'demand ' || l_tp_total_demand || 'sup ' || l_total_supply);
l_inserted_record := l_inserted_record + 1;
l_insert := 0;
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP6,
null,
null,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_insert := 1;
l_insert := 0;
l_insert := 1;
l_insert := 0;
l_insert := 1;
l_insert := 1;
IF ( ((j > 1 and l_insert = 1 ) OR (b_bucket_index.COUNT = 1)) and
((l_total_demand - l_tp_total_supply ) > (l_total_demand * l_threshold1/100)))
and (greatest(b_refresh_number(j),b_refresh_number(j-1)) > p_refresh_number)
THEN
--------------------------------------------------------
-- clean up the opposite exception and its complement
--------------------------------------------------------
msc_x_netting_pkg.add_to_delete_tbl(
l_publisher_id, --b_publisher_id(j),
l_publisher_site_id, --b_publisher_site_id(j),
null,
null,
l_supplier_id, --b_supplier_id(j),
l_supplier_site_id, --b_supplier_site_id(j),
l_item_id, --b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP26,
null,
null,
l_start_date,
l_end_date,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_inserted_record := l_inserted_record + 1;
Loop for the last record if require to insert
------------------------------------------------------------------*/
IF ( j > 1 and l_pair = 0 and j = b_bucket_index.COUNT) THEN
l_pair := 0;
l_insert := 1;
l_inserted_record := l_inserted_record + 1;
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP7,
b_trx_id1(j),
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_publisher_id(j),
b_publisher_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP8,
b_trx_id2(j),
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP27,
b_trx_id1(j),
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_publisher_id(j),
b_publisher_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP28,
b_trx_id2(j),
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
select transaction_id, receipt_date, ship_date, order_number, line_number, release_number
into l_last_so_trx_id, b_so_receipt_date(j), b_so_ship_date(j), b_end_order_number(j),
b_end_order_line_number(j), b_end_order_rel_number(j)
from msc_sup_dem_entries
where publisher_id = b_publisher_id(j)
and publisher_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 publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1)
and key_date = (select max(key_date)
from msc_sup_dem_entries
where publisher_id = b_publisher_id(j)
and publisher_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 publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1));
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP7,
l_last_so_trx_id,
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_publisher_id(j),
b_publisher_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP8,
b_trx_id2(j),
l_last_so_trx_id,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP27,
l_last_so_trx_id,
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_publisher_id(j),
b_publisher_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP28,
b_trx_id2(j),
l_last_so_trx_id,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_inserted_record := l_inserted_record + 1;
l_inserted_record := l_inserted_record + 1;
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP8,
b_trx_id1(j),
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_supplier_id(j),
b_supplier_site_id(j),
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP7,
b_trx_id2(j),
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP28,
b_trx_id1(j),
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_supplier_id(j),
b_supplier_site_id(j),
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP27,
b_trx_id2(j),
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
select transaction_id, receipt_date, ship_date, order_number, line_number, release_number, last_refresh_number
into l_last_so_trx_id, b_so_receipt_date(j), b_so_ship_date(j), b_end_order_number(j),
b_end_order_line_number(j), b_end_order_rel_number(j), b_so_last_refnum(j)
from msc_sup_dem_entries
where publisher_id = b_supplier_id(j)
and publisher_site_id = b_supplier_site_id(j)
and customer_id = b_publisher_id(j)
and customer_site_id = b_publisher_site_id(j)
and inventory_item_id = b_item_id(j)
and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1)
and key_date = (select max(key_date)
from msc_sup_dem_entries
where publisher_id = b_supplier_id(j)
and publisher_site_id = b_supplier_site_id(j)
and customer_id = b_publisher_id(j)
and customer_site_id = b_publisher_site_id(j)
and inventory_item_id = b_item_id(j)
and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1) );
SELECT max(last_refresh_number)
into b_so_last_refnum(j)
from msc_sup_dem_entries
where publisher_id = b_supplier_id(j)
and publisher_site_id = b_supplier_site_id(j)
and customer_id = b_publisher_id(j)
and customer_site_id = b_publisher_site_id(j)
and inventory_item_id = b_item_id(j)
and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP8,
b_trx_id1(j),
l_last_so_trx_id,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_supplier_id(j),
b_supplier_site_id(j),
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP7,
l_last_so_trx_id,
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP28,
b_trx_id1(j),
l_last_so_trx_id,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_supplier_id(j),
b_supplier_site_id(j),
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP27,
l_last_so_trx_id,
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_inserted_record := l_inserted_record + 1;
l_inserted_record := l_inserted_record + 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_MATERIAL_SHORTAGE) || ':' || l_inserted_record);
l_updated Number;
l_insert Number := 0;
l_inserted_record Number := 0;
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP25,
null,
null,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_insert := 1;
l_insert := 0;
--dbms_output.put_line('2 no insert with previous line l_pair = 1' || b_bkt_start_date(j));
l_insert := 1;
l_insert := 0;
l_insert := 1;
l_insert := 1;
IF (((j > 1 and l_insert = 1) OR (b_bucket_index.COUNT = 1)) and
((l_total_supply - l_tp_total_demand) > (l_tp_total_demand * l_threshold2/100)))
and (greatest(b_refresh_number(j),b_refresh_number(j-1)) > p_refresh_number)
THEN
--======================================================
-- clean up the opposite exception and its complement
--======================================================
msc_x_netting_pkg.add_to_delete_tbl(
l_publisher_id, --b_publisher_id(j),
l_publisher_site_id, --b_publisher_site_id(j),
l_customer_id, --b_customer_id(j),
l_customer_site_id, --b_customer_site_id(j),
null,
null,
l_item_id, --b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP5,
null,
null,
l_start_date,
l_end_date,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_inserted_record := l_inserted_record + 1;
Loop for the last record if require to insert
------------------------------------------------------------------*/
IF ( j > 1 and l_pair = 0 and j = b_bucket_index.COUNT) THEN
l_insert := 1;
l_inserted_record := l_inserted_record + 1;
l_insert := 0;
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP26,
null,
null,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_insert := 1;
l_insert := 0;
l_insert := 1;
l_insert := 0;
l_insert := 1;
l_insert := 1;
IF (((j > 1 and l_insert = 1 ) OR (b_bucket_index.COUNT = 1)) and
((l_tp_total_supply - l_total_demand) > (l_total_demand * l_threshold2/100)))
and (greatest(b_refresh_number(j),b_refresh_number(j-1)) > p_refresh_number)
THEN
--------------------------------------------------------
-- clean up the opposite exception and its complement
--------------------------------------------------------
msc_x_netting_pkg.add_to_delete_tbl(
l_publisher_id, --b_publisher_id(j),
l_publisher_site_id, --b_publisher_site_id(j),
null,
null,
l_supplier_id, --b_supplier_id(j),
l_supplier_site_id, --b_supplier_site_id(j),
l_item_id, --b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP6,
null,
null,
l_start_date,
l_end_date,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_inserted_record := l_inserted_record + 1;
Loop for the last record if require to insert
------------------------------------------------------------------*/
IF ( j > 1 and l_pair = 0 and j = b_bucket_index.COUNT) THEN
l_pair := 0;
l_insert := 1;
l_inserted_record := l_inserted_record + 1;
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP27,
b_trx_id1(j),
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_publisher_id(j),
b_publisher_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP28,
b_trx_id2(j),
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP7,
b_trx_id1(j),
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_publisher_id(j),
b_publisher_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP8,
b_trx_id2(j),
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
select transaction_id, receipt_date, ship_date, order_number, line_number, release_number
into l_last_so_trx_id, b_so_receipt_date(j), b_so_ship_date(j), b_end_order_number(j),
b_end_order_line_number(j), b_end_order_rel_number(j)
from msc_sup_dem_entries
where publisher_id = b_publisher_id(j)
and publisher_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 publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1)
and key_date = (select max(key_date)
from msc_sup_dem_entries
where publisher_id = b_publisher_id(j)
and publisher_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 publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1));
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP27,
l_last_so_trx_id,
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_publisher_id(j),
b_publisher_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP28,
b_trx_id2(j),
l_last_so_trx_id,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP7,
l_last_so_trx_id,
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_customer_id(j),
b_customer_site_id(j),
null,
null,
b_publisher_id(j),
b_publisher_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP8,
b_trx_id2(j),
l_last_so_trx_id,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_inserted_record := l_inserted_record + 1;
l_inserted_record := l_inserted_record + 1;
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP28,
b_trx_id1(j),
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_supplier_id(j),
b_supplier_site_id(j),
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP27,
b_trx_id2(j),
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP8,
b_trx_id1(j),
b_trx_id2(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_supplier_id(j),
b_supplier_site_id(j),
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP7,
b_trx_id2(j),
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
select transaction_id, receipt_date, ship_date, order_number, line_number, release_number, last_refresh_number
into l_last_so_trx_id, b_so_receipt_date(j), b_so_ship_date(j), b_end_order_number(j),
b_end_order_line_number(j), b_end_order_rel_number(j), b_so_last_refnum(j)
from msc_sup_dem_entries
where publisher_id = b_supplier_id(j)
and publisher_site_id = b_supplier_site_id(j)
and customer_id = b_publisher_id(j)
and customer_site_id = b_publisher_site_id(j)
and inventory_item_id = b_item_id(j)
and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1)
and key_date = (select max(key_date)
from msc_sup_dem_entries
where publisher_id = b_supplier_id(j)
and publisher_site_id = b_supplier_site_id(j)
and customer_id = b_publisher_id(j)
and customer_site_id = b_publisher_site_id(j)
and inventory_item_id = b_item_id(j)
and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1) );
SELECT max(last_refresh_number)
into b_so_last_refnum(j)
from msc_sup_dem_entries
where publisher_id = b_supplier_id(j)
and publisher_site_id = b_supplier_site_id(j)
and customer_id = b_publisher_id(j)
and customer_site_id = b_publisher_site_id(j)
and inventory_item_id = b_item_id(j)
and publisher_order_type = msc_x_netting_pkg.SALES_ORDER
and end_order_number = b_order_number(j)
and NVL(end_order_line_number,-1) = nvl(b_line_number(j),-1)
and nvl(end_order_rel_number,-1) = nvl(b_release_number(j),-1);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP28,
b_trx_id1(j),
l_last_so_trx_id,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_supplier_id(j),
b_supplier_site_id(j),
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_EXCESS,
msc_x_netting_pkg.G_EXCEP27,
l_last_so_trx_id,
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP8,
b_trx_id1(j),
l_last_so_trx_id,
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
msc_x_netting_pkg.add_to_delete_tbl(
b_supplier_id(j),
b_supplier_site_id(j),
b_publisher_id(j),
b_publisher_site_id(j),
null,
null,
b_item_id(j),
msc_x_netting_pkg.G_MATERIAL_SHORTAGE,
msc_x_netting_pkg.G_EXCEP7,
l_last_so_trx_id,
b_trx_id1(j),
null,
null,
t_company_list,
t_company_site_list,
t_customer_list,
t_customer_site_list,
t_supplier_list,
t_supplier_site_list,
t_item_list,
t_group_list,
t_type_list,
t_trxid1_list,
t_trxid2_list,
t_date1_list,
t_date2_list);
l_inserted_record := l_inserted_record + 1;
l_inserted_record := l_inserted_record + 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Total exceptions inserted for the group ' ||
msc_x_netting_pkg.get_message_group(msc_x_netting_pkg.G_MATERIAL_EXCESS) || ':' || l_inserted_record);