The following lines contain the word 'select', 'insert', 'update' or 'delete':
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,
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.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.inventory_item_id = sd1.inventory_item_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);
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.customer_item_name,
sd1.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.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);
SELECT sd1.transaction_id, -- need customer info only
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.customer_item_name,
sd1.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,
msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP3, --exception type
sd1.supplier_id, --p_company_id
sd1.supplier_site_id, --p_company_site_id
sd1.inventory_item_id, --p_inventory_item_id
null, --p_supplier_company_id
null, --p_supplier_company_site_id
sd1.publisher_id, --p_customer_company_id
sd1.publisher_site_id, --p_customer_company_site_id
sd1.key_date),
msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP4,
sd1.publisher_id,
sd1.publisher_site_id,
sd1.inventory_item_id,
sd1.supplier_id,
sd1.supplier_site_id,
null,
null,
sd1.key_date)
FROM msc_sup_dem_entries sd1
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER -- PO
AND trunc(sysdate) > trunc(sd1.key_date);
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.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.inventory_item_id = sd1.inventory_item_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);
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.customer_item_name,
sd1.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.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);
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 distinct sd.customer_id,
sd.customer_name,
sd.customer_site_id,
sd.customer_site_name,
sd.customer_item_name,
sd.customer_item_description,
sd.publisher_id,
sd.publisher_name,
sd.publisher_site_id,
sd.publisher_site_name,
sd.inventory_item_id,
sd.item_name,
sd.item_description,
sd.supplier_item_name
FROM msc_sup_dem_entries sd
WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
AND sd.supplier_id = sd.publisher_id
AND sd.supplier_site_id = sd.publisher_site_id
AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1);
SELECT distinct sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description,
sd.publisher_id,
sd.publisher_name,
sd.publisher_site_id,
sd.publisher_site_name,
sd.inventory_item_id,
sd.item_name,
sd.item_description,
sd.customer_item_name
FROM msc_sup_dem_entries sd
WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
AND sd.customer_id = sd.publisher_id
AND sd.customer_site_id = sd.publisher_site_id
AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1);
select plan_id,
inventory_item_id,
company_id,
company_site_id,
exception_group,
exception_type,
count(*)
from msc_x_exception_details
where plan_id =msc_x_netting_pkg.G_PLAN_ID
and version = 'X'
and exception_type in (3,4)
group by plan_id,
inventory_item_id,
company_id,
company_site_id,
exception_group,
exception_type;
l_inserted_record Number;
l_inserted_record := 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_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP1,
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_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP2,
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_EARLY_ORDER,
msc_x_netting_pkg.G_EXCEP23,
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_EARLY_ORDER,
msc_x_netting_pkg.G_EXCEP24,
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);
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_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP2,
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_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP1,
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_EARLY_ORDER,
msc_x_netting_pkg.G_EXCEP24,
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_EARLY_ORDER,
msc_x_netting_pkg.G_EXCEP23,
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);
l_inserted_record := l_inserted_record + 1;
l_inserted_record := l_inserted_record + 1;
update msc_x_exception_details
set version = 'X'
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (3,4);
update msc_item_exceptions
set version = version + 1
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (3,4);
update msc_x_exception_details
set version = null,
date1 = b_po_receipt_date(j),
date2 = b_po_ship_date(j),
number1 = b_tp_po_qty(j)
where exception_detail_id = l_exception_detail_id1;
msc_x_netting_pkg.update_exceptions_summary(b_supplier_id(j),
b_supplier_site_id(j),
b_item_id(j),
l_exception_type,
l_exception_group);
l_inserted_record := l_inserted_record + 1;
update msc_x_exception_details
set version = null,
date1 = b_po_receipt_date(j),
date2 = b_po_ship_date(j),
number1 = b_po_qty(j)
where exception_detail_id = l_exception_detail_id2;
msc_x_netting_pkg.update_exceptions_summary(b_publisher_id(j),
b_publisher_site_id(j),
b_item_id(j),
l_exception_type,
l_exception_group);
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_LATE_ORDER) || ':' || l_inserted_record);
delete msc_x_exception_details
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (3,4)
and version = 'X';
update msc_item_exceptions
set exception_count = u_count(i)
where plan_id = u_plan_id(i)
and company_id = u_company_id(i)
and company_site_id = u_company_site_id(i)
and inventory_item_id = u_inventory_item_id(i)
and exception_type = u_exception_type(i)
and exception_group = u_exception_group(i)
and version = 0;
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
l_inserted_record Number;
l_inserted_record := 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_EARLY_ORDER,
msc_x_netting_pkg.G_EXCEP23,
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_EARLY_ORDER,
msc_x_netting_pkg.G_EXCEP24,
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_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP1,
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_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP2,
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);
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_EARLY_ORDER,
msc_x_netting_pkg.G_EXCEP24,
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_EARLY_ORDER,
msc_x_netting_pkg.G_EXCEP23,
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_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP2,
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_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP1,
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);
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_EARLY_ORDER) || ':' || l_inserted_record);
l_updated Number;
l_inserted_record Number;
l_inserted_record := 0;
select nvl(sum(sd.quantity),-999999)
into l_sum
from msc_sup_dem_entries sd
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = b_publisher_id(j)
and sd.publisher_site_id = b_publisher_site_id(j)
and sd.customer_id = b_customer_id(j)
and sd.customer_site_id = b_customer_site_id(j)
and sd.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST;
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP19,
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);
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP20,
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);
select nvl(sum(sd.tp_quantity),0), --supplier centric (look at the tp qty)
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.quantity),0)
into l_tp_total_forecast, l_total_forecast, l_posting_total_forecast
from msc_sup_dem_entries sd
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = b_customer_id(j)
and sd.publisher_site_id = b_customer_site_id(j)
and sd.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
and sd.supplier_id = b_publisher_id(j)
and sd.supplier_site_id = b_publisher_site_id(j)
and trunc(sd.key_date) between l_start_date and l_end_date
and sd.last_refresh_number <= l_cutoff_ref_num;
select nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0)
into l_total_sales_fsct, l_tp_total_sales_fsct, l_posting_total_sales_fsct
from msc_sup_dem_entries sd
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = b_publisher_id(j)
and sd.publisher_site_id = b_publisher_site_id(j)
and sd.customer_id = b_customer_id(j)
and sd.customer_site_id = b_customer_site_id(j)
and sd.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
and trunc(sd.key_date) between l_start_date and l_end_date
and sd.last_refresh_number <= l_cutoff_ref_num;
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP21,
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);
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP22,
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;
l_inserted_record := l_inserted_record + 1;
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP21,
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);
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP22,
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;
l_inserted_record := l_inserted_record + 1;
select nvl(sum(sd.quantity),-999999)
into l_sum
from msc_sup_dem_entries sd
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = b_publisher_id(j)
and sd.publisher_site_id = b_publisher_site_id(j)
and sd.supplier_id = b_supplier_id(j)
and sd.supplier_site_id = b_supplier_site_id(j)
and sd.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST;
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP21,
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);
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP22,
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);
select nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0) ,
nvl(sum(sd.quantity),0)
into l_total_forecast, l_tp_total_forecast, l_posting_total_forecast
from msc_sup_dem_entries sd
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = b_publisher_id(j)
and sd.publisher_site_id = b_publisher_site_id(j)
and sd.supplier_id = b_supplier_id(j)
and sd.supplier_site_id = b_supplier_site_id(j)
and sd.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
and trunc(sd.key_date) between l_start_date and l_end_date
and sd.last_refresh_number <= l_cutoff_ref_num;
select nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.quantity),0)
into l_tp_total_sales_fsct, l_total_sales_fsct, l_posting_total_sales_fsct
from msc_sup_dem_entries sd
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_id = b_supplier_id(j)
and sd.publisher_site_id = b_supplier_site_id(j)
and sd.customer_id = b_publisher_id(j)
and sd.customer_site_id = b_publisher_site_id(j)
and sd.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
and trunc(sd.key_date) between l_start_date and l_end_date
and sd.last_refresh_number <= l_cutoff_ref_num;
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP19,
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);
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP20,
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;
l_inserted_record := l_inserted_record + 1;
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP19,
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);
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_FORECAST_MISMATCH,
msc_x_netting_pkg.G_EXCEP20,
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;
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_FORECAST_MISMATCH) || ':' || l_inserted_record);