[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sd.transaction_id, -- need customer info only
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,
sd.customer_item_description,
sd.key_date,
sd.ship_date,
sd.receipt_date,
sd.quantity,
sd.primary_quantity,
sd.tp_quantity,
sd.order_number,
sd.release_number,
sd.line_number,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description,
sd.last_update_date
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.PURCHASE_ORDER
AND sd.quantity = 0
AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
AND (sd.last_update_login is NULL OR nvl(sd.last_update_login,-99) <>-99) --added for Bug #6729356
AND NOT EXISTS ---- Fix for Bug # 6144881
(
SELECT order_number
FROM msc_sup_dem_history sdh
WHERE sdh.quantity_old is null
AND sdh.order_number = sd.order_number
AND nvl(sdh.line_number, -99) = nvl(sd.line_number, -99)
AND nvl(sdh.release_number,-99) = nvl(sd.release_number, -99)
AND sdh.quantity_new =
(select sum(quantity)
from msc_sup_dem_entries
where publisher_order_type = 15
and end_order_number = sd.order_number
and nvl(end_order_line_number,-99) = nvl(sd.line_number, -99)
and nvl(end_order_rel_number, -99) = nvl(sd.release_number, -99)));
SELECT distinct sd1.transaction_id,
sd2.history_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.quantity,
sd1.primary_quantity,
sd1.tp_quantity,
sd1.supplier_id,
sd1.supplier_name,
sd1.supplier_site_id,
sd1.supplier_site_name,
sd1.supplier_item_name,
sd1.supplier_item_description,
sd1.order_number,
sd1.release_number,
sd1.line_number,
sd1.key_date,
sd2.key_date_new,
sd2.key_date_old
FROM msc_sup_dem_entries sd1,
msc_sup_dem_history 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 = sd1.publisher_order_type
AND sd2.item_name = sd1.item_name
AND sd2.publisher_name = sd1.publisher_name
AND sd2.publisher_site_name = sd1.publisher_site_name
AND sd2.supplier_name = sd1.supplier_name
AND sd2.supplier_site_name = sd1.supplier_site_name
AND sd2.order_number = sd1.order_number
AND nvl(sd2.release_number, -1) = nvl(sd1.release_number, -1)
AND nvl(sd2.line_number, -1) = nvl(sd1.line_number, -1)
AND trunc(sd2.key_date_old) <> trunc(sd2.key_date_new)
AND trunc(sd1.key_date)=trunc(sd2.key_date_new)
AND sd2.key_date_old is not null
AND sd2.last_refresh_number_new = sd1.last_refresh_number
AND sd1.transaction_id = sd2.transaction_id
AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
UNION
SELECT distinct sd1.transaction_id,
sd2.history_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.quantity,
sd1.primary_quantity,
sd1.tp_quantity,
sd1.supplier_id,
sd1.supplier_name,
sd1.supplier_site_id,
sd1.supplier_site_name,
sd1.supplier_item_name,
sd1.supplier_item_description,
sd1.order_number,
sd1.release_number,
sd1.line_number,
sd1.key_date,
sd2.key_date_new,
sd2.key_date_old
FROM msc_sup_dem_entries sd1,
msc_sup_dem_history 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 = sd1.publisher_order_type
AND sd2.item_name = sd1.item_name
AND sd2.publisher_name = sd1.publisher_name
AND sd2.publisher_site_name = sd1.publisher_site_name
AND sd2.supplier_name = sd1.supplier_name
AND sd2.supplier_site_name = sd1.supplier_site_name
AND sd2.order_number = sd1.order_number
AND nvl(sd2.release_number, -1) = nvl(sd1.release_number, -1)
AND nvl(sd2.line_number, -1) = nvl(sd1.line_number, -1)
AND trunc(sd1.key_date) <> trunc(sd2.key_date_new)
AND sd1.transaction_id = sd2.transaction_id
AND nvl(sd1.last_refresh_number,-1) > nvl(p_refresh_number,-1)
order by 1,2 desc;
SELECT sd.transaction_id, -- need customer info only
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,
sd.customer_item_description,
sd.key_date,
sd.ship_date,
sd.receipt_date,
sd.quantity,
sd.primary_quantity,
sd.tp_quantity,
sd.order_number,
sd.release_number,
sd.line_number,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description,
sd.creation_date,
sd.last_update_date
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.PURCHASE_ORDER
AND sd.acceptance_required_flag = 'R'
AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1);
SELECT distinct sd.publisher_id, --require distinct bug# 2381227 (duplicate exceptions)
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,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description
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 trunc(sd.key_date) between
add_months(trunc(sysdate), -3) and trunc(sysdate);
SELECT 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_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description
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.ORDER_FORECAST
AND trunc(sd.key_date) between
add_months(trunc(sysdate), -3) and trunc(sysdate);
SELECT distinct 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,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description
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.HISTORICAL_SALES
AND trunc(sd.key_date) between
add_months(trunc(sysdate), -3) and trunc(sysdate);
SELECT distinct 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,
sd.order_number,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description
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.PURCHASE_ORDER
AND trunc(sd.key_date) between
add_months(trunc(sysdate), -3) and trunc(sysdate);
SELECT sd1.primary_quantity, sd1.tp_quantity,sd1.quantity,
sd2.primary_quantity, sd2.tp_quantity,sd2.quantity
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_id = p_company_id
AND sd1.publisher_site_id = p_org_id
AND sd1.inventory_item_id = p_item_id
AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND sd1.order_number = p_order_number
AND sd2.plan_id = sd1.plan_id
AND sd2.publisher_id = sd1.supplier_id
AND sd2.publisher_site_id = sd1.supplier_site_id
AND sd2.publisher_order_type = msc_x_netting_pkg.ASN
AND sd2.end_order_number = sd1.order_number
AND nvl(sd2.end_order_rel_number,-1) = nvl(sd1.release_number,-1)
AND nvl(sd2.end_order_line_number, -1) = nvl(sd1.line_number,-1)
AND trunc(sd2.key_date) <= trunc(sd1.key_date)
AND trunc(sd1.key_date) between
add_months(trunc(sysdate),-3) and trunc(sysdate);
SELECT distinct sd.publisher_id,
sd.publisher_site_id,
sd.inventory_item_id,
sd.supplier_id,
sd.supplier_site_id
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.PURCHASE_ORDER
AND trunc(sd.key_date) between
add_months(trunc(sysdate), -3) and trunc(sysdate);
SELECT distinct 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,
sd.key_date,
sd.ship_date,
sd.receipt_date,
sd.quantity,
sd.tp_quantity,
sd.order_number,
sd.release_number,
sd.line_number,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description
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.PURCHASE_ORDER
AND sd.publisher_id = p_publisher_id
AND sd.publisher_site_id = p_publisher_site_id
AND sd.inventory_item_id = p_item_id
AND trunc(sd.key_date) between
add_months(trunc(sysdate), -3) and trunc(sysdate);
SELECT sd3.key_date --sd3.ship_date --sd2.new_schedule_date
--the load also populate the ship date; therefore
SELECT sd2.key_date --sd2.ship_date --sd2.new_schedule_date
-- the load also populate the ship date; therefore
SELECT sd1.key_date --sd1.ship_date --sd2.new_schedule_date
-- the load also populate the ship date; therefore
SELECT distinct 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,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.supplier_item_description
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.HISTORICAL_SALES
AND trunc(sd.key_date) between
add_months(trunc(sysdate), -3)and trunc(sysdate);
SELECT distinct
sd2.publisher_id,
sd1.publisher_name,
sd2.publisher_site_id,
sd1.publisher_site_name,
sd2.inventory_item_id,
sd1.item_name,
sd1.item_description,
sd2.customer_item_name,
sd2.supplier_id,
sd1.supplier_name,
sd2.supplier_site_id,
sd1.supplier_site_name,
sd1.supplier_item_name,
sd1.supplier_item_description
FROM msc_sup_dem_history sd1,
msc_sup_dem_entries sd2,
msc_item_suppliers itm,
msc_trading_partners part,
msc_trading_partner_maps map
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.publisher_order_type = msc_x_netting_pkg.ALLOCATED_ONHAND
AND sd1.item_name is not null
AND sd1.quantity_new = 0
AND sd1.quantity_new <> sd1.quantity_old
AND sd2.plan_id = sd1.plan_id
AND sd2.publisher_order_type = sd1.publisher_order_type
AND sd2.item_name = sd1.item_name
AND sd2.publisher_name = sd1.publisher_name
AND sd2.publisher_site_name = sd1.publisher_site_name
AND sd2.supplier_name = sd1.supplier_name
AND sd2.supplier_site_name = sd1.supplier_site_name
AND sd2.new_schedule_date = sd1.new_schedule_date_new
AND map.map_type = 2
AND map.company_key = sd2.publisher_site_id
AND map.tp_key = part.partner_id
AND itm.plan_id = sd1.plan_id
AND itm.sr_instance_id = part.sr_instance_id
AND itm.organization_id = part.sr_tp_id
AND itm.inventory_item_id = sd2.inventory_item_id
AND itm.supplier_id = NVL(sd2.supplier_id, itm.supplier_id)
AND itm.supplier_site_id = NVL(sd2.supplier_site_id, itm.supplier_site_id)
AND itm.vmi_flag = 1
AND trunc(sd1.new_schedule_date_new) between
add_months(trunc(sysdate), -3)and trunc(sysdate);
select distinct sd.transaction_id,
sd.publisher_id,
sd.publisher_site_id,
sd.inventory_item_id,
sd.supplier_id,
sd.supplier_site_id
from msc_sup_dem_entries sd,
msc_x_exception_details dt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
and dt.plan_id = sd.plan_id
and dt.company_id = sd.supplier_id
and dt.company_site_id = sd.supplier_site_id
and dt.inventory_item_id = sd.inventory_item_id
and dt.customer_id = sd.publisher_id
and dt.customer_site_id = sd.publisher_site_id
and dt.order_number = sd.order_number
and dt.line_number = sd.line_number
and dt.release_number = sd.release_number
and dt.transaction_id1 = sd.transaction_id
and dt.exception_type = p_type
and dt.version is null;
SELECT ed.exception_detail_id
FROM msc_x_exception_details ed
WHERE ed.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND ed.inventory_item_id = p_item_id
AND ed.company_id = p_supplier_id
AND ed.company_site_id = p_supplier_site_id
AND ed.exception_type = 34
AND ed.order_number = p_order_number;
select distinct sd.transaction_id,
sd.publisher_id,
sd.publisher_site_id,
sd.inventory_item_id,
sd.supplier_id,
sd.supplier_site_id
from msc_sup_dem_entries sd,
msc_x_exception_details dt
where sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
and sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND nvl(sd.last_refresh_number,-1) > nvl(p_refresh_number,-1)
and dt.plan_id = sd.plan_id
and dt.company_id = sd.publisher_id
and dt.company_site_id = sd.publisher_site_id
and dt.inventory_item_id = sd.inventory_item_id
and dt.supplier_id = sd.supplier_id
and dt.supplier_site_id = sd.supplier_site_id
and dt.order_number = sd.order_number
and dt.line_number = sd.line_number
and dt.release_number = sd.release_number
and dt.transaction_id1 = sd.transaction_id
and dt.exception_type = p_type
and dt.version is null;
b_po_last_update_date msc_x_netting_pkg.date_arr;
l_inserted_record Number := 0;
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_CHANGED_ORDER,
msc_x_netting_pkg.G_EXCEP33,
b_trx_id1(j),
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);
| There is no option for the user to delete this type of
| exception when the exception is viewed.
| Delete the exception if it is 1 months old (30 days).
------------------------------------------------------------------*/
begin
delete msc_x_exception_details
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type = 33
and exception_group = msc_x_netting_pkg.G_CHANGED_ORDER
and company_id = b_supplier_id(j)
and company_site_id = b_supplier_site_id(j)
and inventory_item_id = b_item_id(j)
and trunc(creation_date ) < trunc(sysdate) - 30;
update msc_item_exceptions
set exception_count = exception_count - l_row,
last_update_date = sysdate
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type = 33
and exception_group = msc_x_netting_pkg.G_CHANGED_ORDER
and company_id = b_supplier_id(j)
and company_site_id = b_supplier_site_id(j)
and inventory_item_id = b_item_id(j)
and version = 0;
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_CHANGED_ORDER,
msc_x_netting_pkg.G_EXCEP34,
b_trx_id1(j),
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 number3
into l_old_history_id
from msc_x_exception_details
where exception_detail_id = l_exception_detail_id;
--fix to update the LAST_UPDATED_BY , check this in MSC_SCE_LOADS_PKG when trying to delete the record...
update msc_x_exception_details
set number3 = b_history_id(j),
date1 = b_receipt_date(j),
date2 = l_reschedule_date,
number1 = b_po_qty(j),--updating the date entered by the user in the exception
number2 = b_po_qty(j),
LAST_UPDATE_LOGIN=-99
where exception_detail_id = l_exception_detail_id;
l_inserted_record := l_inserted_record + 1;--updating the count of the records that are shown in the log.
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_CHANGED_ORDER,
msc_x_netting_pkg.G_EXCEP49,
b_trx_id1(j),
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);
b_po_last_update_date;
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_RESPONSE_REQUIRED,
msc_x_netting_pkg.G_EXCEP11,
b_trx_id1(j),
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_RESPONSE_REQUIRED,
msc_x_netting_pkg.G_EXCEP31,
b_trx_id1(j),
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);
b_po_last_update_date(j),
null,
null,
null,
l_exception_basis,
a_company_id,
a_company_name,
a_company_site_id,
a_company_site_name,
a_item_id,
a_item_name,
a_item_desc,
a_exception_type,
a_exception_type_name,
a_exception_group,
a_exception_group_name,
a_trx_id1,
a_trx_id2,
a_customer_id,
a_customer_name,
a_customer_site_id,
a_customer_site_name,
a_customer_item_name,
a_supplier_id,
a_supplier_name,
a_supplier_site_id,
a_supplier_site_name,
a_supplier_item_name,
a_number1,
a_number2,
a_number3,
a_threshold,
a_lead_time,
a_item_min_qty,
a_item_max_qty,
a_order_number,
a_release_number,
a_line_number,
a_end_order_number,
a_end_order_rel_number,
a_end_order_line_number,
a_creation_date,
a_tp_creation_date,
a_date1,
a_date2,
a_date3,
a_date4,
a_date5,
a_exception_basis);
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_CHANGED_ORDER) || ':' || l_inserted_record);
l_inserted_record Number := 0;
delete msc_x_exception_details
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (35,36);
update msc_item_exceptions
set version = version + 1,
last_update_date = sysdate
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (35,36);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
select nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0)
into l_total_historical_sales,
l_tp_total_historical_sales,
l_p_total_historical_sales
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.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.HISTORICAL_SALES
and sd.supplier_id = b_supplier_id(j)
and sd.supplier_site_id = b_supplier_site_id(j)
and sd.customer_id = sd.publisher_id
and sd.customer_site_id = sd.publisher_site_id
and trunc(sd.new_schedule_date) between
add_months(trunc(sysdate),-3) and trunc(sysdate);
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.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
and sd.supplier_id = b_supplier_id(j)
and sd.supplier_site_id = b_supplier_site_id(j)
and sd.customer_id = sd.publisher_id
and sd.customer_site_id = sd.publisher_site_id
and trunc(sd.key_date) between
add_months(trunc(sysdate),-3) and trunc(sysdate);
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_ACCURACY) || ':' || l_inserted_record);
l_inserted_record Number := 0;
delete msc_x_exception_details
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (39,40,43,44,45,46,47,48);
update msc_item_exceptions
set version = version + 1,
last_update_date = sysdate
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (39,40,43,44,45,46,47,48);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
msc_x_netting_pkg.delete_wf_notification(l_item_type, l_item_key);
SELECT add_months(trunc(sysdate),-i) into l_date from dual;
select nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0)
into l_historical_sales, l_tp_historical_sales, l_posting_historical_sales
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.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.HISTORICAL_SALES
and sd.supplier_id = b_supplier_id(j)
and sd.supplier_site_id = b_supplier_site_id(j)
and sd.customer_id = sd.publisher_id
and sd.customer_site_id = sd.publisher_site_id
and trunc(sd.new_schedule_date) between trunc(l_date)
and add_months(trunc(sysdate),-(i-1)) -1;
select nvl(sum(sd.primary_quantity),0), nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0)
into l_forecast, l_tp_forecast, l_posting_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.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.SALES_FORECAST
and sd.supplier_id = b_supplier_id(j)
and sd.supplier_site_id = b_supplier_site_id(j)
and sd.customer_id = sd.publisher_id
and sd.customer_site_id = sd.publisher_site_id
and trunc(sd.key_date) between trunc(l_date)
and add_months(trunc(sysdate),-(i-1)) -1;
l_inserted_record := l_inserted_record + 1;
l_inserted_record := l_inserted_record + 1;
l_inserted_record := l_inserted_record + 1;
l_inserted_record := l_inserted_record + 1;
select nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0)
into l_total_historical_sales,
l_tp_total_historical_sales,
l_p_total_historical_sales
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.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.HISTORICAL_SALES
and sd.supplier_id = b_supplier_id(j)
and sd.supplier_site_id = b_supplier_site_id(j)
and sd.customer_id = sd.publisher_id
and sd.customer_site_id = sd.publisher_site_id
and trunc(sd.new_schedule_date) between
add_months(trunc(sysdate),-3) and trunc(sysdate);
select nvl(sum(sd.primary_quantity),0),
nvl(sum(sd.tp_quantity),0),
nvl(sum(sd.quantity),0)
into l_total_onhand, l_tp_total_onhand, l_posting_total_onhand
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.inventory_item_id = b_item_id(j)
and sd.publisher_order_type = msc_x_netting_pkg.ALLOCATED_ONHAND
and sd.supplier_id = b_supplier_id(j)
and sd.supplier_site_id = b_supplier_site_id(j)
and sd.customer_id = sd.publisher_id
and sd.customer_site_id = sd.publisher_site_id
and trunc(sd.new_schedule_date) between
add_months(trunc(sysdate),-3) and trunc(sysdate);
l_inserted_record := l_inserted_record + 1;
l_inserted_record := l_inserted_record + 1;
SELECT count(*)
INTO l_stock_out
FROM msc_sup_dem_history sd
WHERE sd.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd.publisher_order_type = msc_x_netting_pkg.ALLOCATED_ONHAND
AND sd.publisher_name = b_publisher_name(j)
AND sd.publisher_site_name = b_publisher_site_name(j)
AND sd.publisher_item_name = b_item_name(j)
AND sd.quantity_new <> sd.quantity_old
AND sd.quantity_new = 0
AND trunc(sd.new_schedule_date_new) between
add_months(trunc(sysdate), -3)and trunc(sysdate);
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_PERFORMANCE) || ':' || l_inserted_record);