[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.creation_date,
msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP11,
sd.supplier_id,
sd.supplier_site_id,
sd.inventory_item_id,
null,
null,
sd.publisher_id,
sd.publisher_site_id,
sd.key_date),
msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP31,
sd.publisher_id,
sd.publisher_site_id,
sd.inventory_item_id,
sd.supplier_id,
sd.supplier_site_id,
null,
null,
sd.key_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 msc_x_netting_pkg.does_so_exist(sd.order_number,
sd.release_number,
sd.line_number,
sd.supplier_id,
sd.supplier_site_id,
sd.publisher_id,
sd.publisher_site_id,
sd.inventory_item_id ) = 0
AND sd.creation_date < sysdate
AND nvl(sd.acceptance_required_flag,'Y') = 'Y';
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.supplier_item_name,
sd.supplier_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.customer_id,
sd.customer_name,
sd.customer_site_id,
sd.customer_site_name,
sd.customer_item_name,
sd.customer_item_description,
sd.creation_date ,
msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP12,
sd.publisher_id,
sd.publisher_site_id,
sd.inventory_item_id,
null,
null,
sd.customer_id,
sd.customer_site_id,
sd.key_date),
msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP32,
sd.customer_id,
sd.customer_site_id,
sd.inventory_item_id,
sd.publisher_id,
sd.publisher_site_id,
null,
null,
sd.key_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.SALES_ORDER
AND msc_x_netting_pkg.does_po_exist(sd.end_order_number,
sd.end_order_rel_number,
sd.end_order_line_number,
sd.customer_id,
sd.customer_site_id,
sd.publisher_id,
sd.publisher_site_id,
sd.inventory_item_id) = 0
AND sd.creation_date < sysdate;
SELECT sd.transaction_id,
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
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.last_refresh_number > p_refresh_number;
SELECT distinct sd2.transaction_id --po trx-id
FROM msc_sup_dem_entries sd1,
msc_sup_dem_entries sd2
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.plan_id = sd2.plan_id
AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER --SO
AND sd1.publisher_id = p_supplier_id
AND sd1.publisher_site_id = p_supplier_site_id
AND sd1.inventory_item_id = p_item_id
AND sd1.inventory_item_id = sd2.inventory_item_id
AND sd1.end_order_number = p_order_number
AND nvl(sd1.end_order_rel_number, -1) =
nvl(p_release_number, -1)
AND nvl(sd1.end_order_line_number, -1) =
nvl(p_line_number, -1)
AND sd2.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER --PO
AND nvl(sd1.order_number, -1) =
nvl(sd2.end_order_number, -1)
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);
select sd.transaction_id,
sd.publisher_order_type,
sd.publisher_id,
sd.publisher_site_id
FROM msc_sup_dem_entries sd
START WITH sd.transaction_id = p_transaction_id
CONNECT BY sd.end_order_number = PRIOR sd.order_number
AND
(
sd.end_order_line_number IS NOT NULL AND
sd.end_order_line_number = PRIOR sd.line_number
OR
sd.end_order_line_number IS NULL AND
sd.publisher_id = sd.end_order_publisher_id AND
sd.publisher_site_id = sd.end_order_publisher_site_id AND
sd.inventory_item_id = PRIOR sd.inventory_item_id
OR
sd.end_order_line_number IS NULL AND
sd.publisher_site_id <> sd.end_order_publisher_site_id
)
AND nvl(sd.release_number, -1) = nvl(PRIOR sd.end_order_rel_number, -1)
AND ( (sd.end_order_publisher_id IS NOT NULL AND
sd.end_order_type IS NOT NULL AND
sd.end_order_publisher_id = PRIOR sd.publisher_id AND
sd.end_order_publisher_site_id = PRIOR sd.publisher_site_id AND
sd.end_order_type = PRIOR sd.publisher_order_type)
OR
(sd.end_order_publisher_id IS NULL AND
sd.end_order_type IS NOT NULL AND
sd.publisher_id = PRIOR sd.publisher_id AND
sd.publisher_site_id = PRIOR sd.publisher_site_id)
);
SELECT distinct sd1.transaction_id,
sd1.supplier_id,
sd1.supplier_name,
sd1.supplier_site_id,
sd1.supplier_site_name,
sd1.supplier_item_name,
sd1.key_date,
sd1.ship_date,
sd1.receipt_date,
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.creation_date,
msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP13,
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,
msc_sup_dem_entries sd2
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.plan_id = sd2.plan_id
AND sd1.inventory_item_id = p_item_id
AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND sd1.transaction_id = p_transaction_id -- po trxid
AND sd1.publisher_id = sd2.customer_id
AND sd1.publisher_id = p_company_id
AND sd1.publisher_site_id = sd2.customer_site_id
AND sd1.publisher_site_id = p_company_site_id
AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
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 trunc(sd2.key_date) > trunc(sd1.key_date) +
msc_pmf_pkg.get_threshold(msc_x_netting_pkg.G_EXCEP13,
sd1.publisher_id,
sd1.publisher_site_id,
sd1.inventory_item_id,
sd1.supplier_id,
sd1.supplier_site_id,
null,
null,
sd1.key_date);
SELECT sd1.transaction_id,
sd2.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_id,
sd1.supplier_name,
sd1.supplier_site_id,
sd1.supplier_site_name,
sd1.supplier_item_name,
sd1.supplier_item_description,
sd1.quantity,
sd2.quantity,
sd1.key_date,
sd1.ship_date,
sd1.receipt_date,
sd2.key_date,
sd2.ship_date,
sd2.receipt_date,
sd1.end_order_number,
sd1.end_order_rel_number,
sd1.end_order_line_number,
sd2.order_number,
sd2.release_number,
sd2.line_number,
sd1.order_number,
sd1.release_number,
sd1.line_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.plan_id = sd2.plan_id
AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND sd1.publisher_id = sd2.customer_id
AND sd1.publisher_site_id = sd2.customer_site_id
AND sd2.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
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 (sd2.last_refresh_number > p_refresh_number OR
sd1.last_refresh_number > p_refresh_number)
ORDER BY sd1.publisher_id, sd1.publisher_site_id, sd1.supplier_id, sd1.supplier_site_id, sd1.inventory_item_id; ----lowest level of the pegging (which is the so)
SELECT distinct sd1.transaction_id, --SO trx-id
sd1.publisher_id,
sd1.publisher_name,
sd1.publisher_site_id,
sd1.publisher_site_name,
sd1.supplier_item_name,
sd1.inventory_item_id,
sd1.item_name,
sd1.item_description,
sd1.key_date,
sd1.ship_date,
sd1.receipt_date,
sd1.creation_date,
sd1.quantity,
sd1.primary_quantity,
sd1.tp_quantity,
sd1.order_number,
sd1.release_number,
sd1.line_number,
sd1.end_order_number,
sd1.end_order_rel_number,
sd1.end_order_line_number,
sd1.customer_id,
sd1.customer_name,
sd1.customer_site_id,
sd1.customer_site_name,
sd1.customer_item_name,
sd1.customer_item_description
FROM msc_sup_dem_entries sd1
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.inventory_item_id = P_ITEM_ID
AND sd1.publisher_id = p_customer_id /* sbala P_SUPPLIER_ID */
AND sd1.publisher_site_id = p_customer_site_id /* sbala P_SUPPLIER_SITE_ID */
AND sd1.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
/* sbala msc_x_netting_pkg.PURCHASE_ORDER */
AND sd1.order_number = P_ORDER_NUMBER
AND nvl(sd1.release_number,'-1') = nvl(P_RELEASE_NUMBER,'-1')
AND nvl(sd1.line_number, '-1') = nvl(P_LINE_NUMBER,'-1');
SELECT SUP.TRANSACTION_ID -- the 1st level of po transaction id in ASCP
FROM msc_supplies sup,
msc_companies c,
msc_company_sites s,
msc_trading_partners t,
msc_trading_partner_maps m
WHERE sup.plan_id = P_PLAN_ID
AND decode(instr(sup.order_number,'('),0, sup.order_number,substr(sup.order_number, 1, instr(sup.order_number,'(') - 1)) = P_ORDER_NUMBER
AND sup.purch_line_num = P_LINE_NUMBER
AND sup.inventory_item_id = P_ITEM_ID
AND sup.order_type = 1
AND c.company_id = P_CUST_ID
AND s.company_site_id = P_CUST_SITE_ID
AND t.sr_tp_id = sup.organization_id
AND t.sr_instance_id = sup.sr_instance_id
AND t.partner_type = 3
AND m.tp_key = t.partner_id
AND m.map_type = 2
AND s.company_site_id = m.company_key
AND c.company_id = s.company_id;
SELECT distinct p.pegging_id,
p.sr_instance_id,
p.organization_id,
p.inventory_item_id,
p.transaction_id,
p.disposition_id,
p.supply_type,
p.demand_id
FROM msc_full_pegging p
WHERE p.plan_id = P_PLAN_ID
START WITH p.transaction_id = P_TRANSACTION_ID
CONNECT BY p.pegging_id = PRIOR p.prev_pegging_id
AND p.plan_id = PRIOR p.plan_id
AND p.sr_instance_id = PRIOR p.sr_instance_id
ORDER BY p.pegging_id desc;
CURSOR get_delete_row (p_transaction_id IN NUMBER) IS
SELECT company_id, company_site_id, customer_id, customer_site_id,
supplier_id, supplier_site_id, inventory_item_id,
transaction_id1, transaction_id2
FROM msc_x_exception_details
WHERE plan_id = -1
AND exception_type in (msc_x_netting_pkg.G_EXCEP50,msc_x_netting_pkg.G_EXCEP51)
AND transaction_id2 = p_transaction_id;
CURSOR delete_old_exception (p_refresh_number IN Number) IS
SELECT distinct sd1.transaction_id,
sd1.publisher_id,
sd1.publisher_site_id,
sd1.inventory_item_id,
sd1.supplier_id,
sd1.supplier_site_id
FROM msc_sup_dem_entries sd1,
msc_trading_partners tp,
msc_trading_partner_maps map,
msc_item_suppliers itm,
msc_trading_partner_maps map2,
msc_trading_partner_maps map3,
msc_company_relationships r
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER --
AND map.map_type = 2
AND map.company_key = sd1.publisher_site_id
AND map.tp_key = tp.partner_id
AND itm.plan_id = sd1.plan_id
AND itm.organization_id = tp.sr_tp_id
AND itm.sr_instance_id = tp.sr_instance_id
AND tp.partner_type = 3
AND itm.supplier_id = map2.tp_key
AND nvl(itm.supplier_site_id, map3.tp_key) = map3.tp_key
AND map2.map_type = 1
AND map2.company_key = r.relationship_id
AND r.subject_id = 1
AND r.object_id = sd1.supplier_id
AND r.relationship_type = 2
AND map3.map_type = 3
AND map3.company_key = sd1.supplier_site_id --supplier's lead time
AND itm.inventory_item_id = sd1.inventory_item_id;
SELECT distinct 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,
nvl(itm.processing_lead_time,0)
FROM msc_sup_dem_entries sd1,
msc_trading_partners tp,
msc_trading_partner_maps map,
msc_item_suppliers itm,
msc_trading_partner_maps map2,
msc_trading_partner_maps map3,
msc_company_relationships r
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER --
AND map.map_type = 2
AND map.company_key = sd1.publisher_site_id
AND map.tp_key = tp.partner_id
AND itm.plan_id = sd1.plan_id
AND itm.organization_id = tp.sr_tp_id
AND itm.sr_instance_id = tp.sr_instance_id
AND tp.partner_type = 3
AND itm.supplier_id = map2.tp_key
AND nvl(itm.supplier_site_id, map3.tp_key) = map3.tp_key
AND map2.map_type = 1
AND map2.company_key = r.relationship_id
AND r.subject_id = 1
AND r.object_id = sd1.supplier_id
AND r.relationship_type = 2
AND map3.map_type = 3
AND map3.company_key = sd1.supplier_site_id --supplier's lead time
AND itm.inventory_item_id = sd1.inventory_item_id
AND sd1.last_refresh_number > p_refresh_number
ORDER BY sd1.publisher_id, sd1.publisher_site_id, sd1.supplier_id, sd1.supplier_site_id, sd1.inventory_item_id;
SELECT distinct 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,
nvl(itm.processing_lead_time,0)
FROM msc_sup_dem_entries sd1,
msc_trading_partners tp,
msc_trading_partner_maps map,
msc_item_suppliers itm,
msc_trading_partner_maps map2,
msc_trading_partner_maps map3,
msc_company_relationships r
WHERE sd1.plan_id = msc_x_netting_pkg.G_PLAN_ID
AND sd1.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER --
AND map.map_type = 2
AND map.company_key = sd1.publisher_site_id
AND map.tp_key = tp.partner_id
AND itm.plan_id = sd1.plan_id
AND itm.organization_id = tp.sr_tp_id
AND itm.sr_instance_id = tp.sr_instance_id
AND tp.partner_type = 3
AND itm.supplier_id = map2.tp_key
AND nvl(itm.supplier_site_id, map3.tp_key) = map3.tp_key
AND map2.map_type = 1
AND map2.company_key = r.relationship_id
AND r.subject_id = 1
AND r.object_id = sd1.supplier_id
AND r.relationship_type = 2
AND map3.map_type = 3
AND map3.company_key = sd1.supplier_site_id
AND itm.inventory_item_id = sd1.inventory_item_id
AND not exists (SELECT * FROM msc_x_exception_details d
WHERE d.exception_type = msc_x_netting_pkg.G_EXCEP3
AND d.transaction_id1 = sd1.transaction_id)
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;
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.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
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.SALES_ORDER
AND not exists (SELECT * FROM msc_x_exception_details d
WHERE d.exception_type in (3,12)
AND d.transaction_id1 = sd1.transaction_id)
AND sd1.last_refresh_number > p_refresh_number
ORDER BY sd1.publisher_id, sd1.publisher_site_id, sd1.customer_id, sd1.customer_site_id, sd1.inventory_item_id ;
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 (11,12,31,32)
group by plan_id,
inventory_item_id,
company_id,
company_site_id,
exception_group,
exception_type;
l_inserted_record Number;
l_inserted_record := 0;
update msc_x_exception_details
set version = 'X'
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (11,12,31,32);
update msc_item_exceptions
set version = version + 1
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (11,12,31,32);
update msc_x_exception_details
set version = null,
threshold = b_threshold1(j),
date1 = b_po_receipt_date(j),
date2 = b_po_ship_date(j),
number1 = b_tp_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,
threshold = b_threshold2(j),
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;
update msc_x_exception_details
set version = null,
threshold = b_threshold1(j),
date1 = b_so_ship_date(j),
date2 = b_so_receipt_date(j),
number1 = b_so_qty(j)
where exception_detail_id = l_exception_detail_id1;
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;
update msc_x_exception_details
set version = null,
threshold = b_threshold2(j),
date1 = b_so_ship_date(j),
date2 = b_so_receipt_date(j),
number1 = b_tp_qty(j)
where exception_detail_id = l_exception_detail_id2;
msc_x_netting_pkg.update_exceptions_summary(b_customer_id(j),
b_customer_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_RESPONSE_REQUIRED) || ':' || l_inserted_record);
delete msc_x_exception_details
where plan_id = msc_x_netting_pkg.G_PLAN_ID
and exception_type in (11,12,31,32)
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),
null,
null,
b_supplier_id(m),
b_supplier_site_id(m),
b_item_id(j),
msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP13,
b_source_trx_id(j),
b_trx_id2(m),
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;
select plan_id
into l_plan_order_at_risk_id
from msc_plans
where compile_designator = l_plan_order_at_risk;
open get_delete_row(b_trx_id2(j));
fetch get_delete_row BULK COLLECT INTO
d_company_id,
d_company_site_id,
d_customer_id,
d_customer_site_id,
d_supplier_id,
d_supplier_site_id,
d_item_id,
d_trx_id1,
d_trx_id2;
CLOSE get_delete_row;
msc_x_netting_pkg.add_to_delete_tbl(
d_company_id(d),
d_company_site_id(d),
d_customer_id(d),
d_customer_site_id(d),
d_supplier_id(d),
d_supplier_site_id(d),
d_item_id(d),
msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP50,
d_trx_id1(d),
d_trx_id2(d),
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(
d_company_id(d),
d_company_site_id(d),
d_customer_id(d),
d_customer_site_id(d),
d_supplier_id(d),
d_supplier_site_id(d),
d_item_id(d),
msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP51,
d_trx_id1(d),
d_trx_id2(d),
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(
k_supplier_id(k),
k_supplier_site_id(k),
k_customer_id(k),
k_customer_site_id(k),
k_supplier_id(k),
k_supplier_site_id(k),
k_item_id(k),
msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP50,
k_so_trx_id(k),
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);
l_inserted_record := l_inserted_record + 1;
SELECT sup.order_number,
sup.purch_line_num,
s1.company_id,
s1.company_site_id, --- cp cust id --- po owner
sup.inventory_item_id,
sd.transaction_id,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.inventory_item_id,
sd.item_name,
sd.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.customer_id,
sd.customer_name,
sd.customer_site_id,
sd.customer_site_name,
sd.customer_item_name,
sd.customer_item_description
INTO l_ascp_po_order_number,
l_ascp_po_line,
l_cp_company_id,
l_cp_company_site_id,
l_cp_item_id,
l_po_trx_id,
l_supplier_id,
l_supplier_name,
l_supplier_site_id,
l_supplier_site_name,
l_supplier_item_name,
l_item_id,
l_item_name,
l_item_desc,
l_po_key_date,
l_po_ship_date,
l_po_receipt_date,
l_posting_po_qty,
l_po_qty,
l_tp_po_qty,
l_order_number,
l_release_number,
l_line_number,
l_customer_id,
l_customer_name,
l_customer_site_id,
l_customer_site_name,
l_customer_item_name,
l_customer_item_desc
FROM msc_supplies sup,
msc_companies c1,
msc_company_sites s1,
msc_trading_partners t1,
msc_trading_partner_maps m1,
msc_trading_partners t2,
msc_trading_partner_maps m2,
msc_companies c2,
msc_company_sites s2,
msc_company_relationships rel,
msc_trading_partner_maps m3,
msc_sup_dem_entries sd
WHERE sup.transaction_id = l_peg_trx_id
AND sup.plan_id = l_plan_order_at_risk_id
AND sup.sr_instance_id = l_peg_sr_instance_id
AND sup.organization_id = l_peg_org_id
AND sup.inventory_item_id = l_peg_item_id
AND sup.order_type = msc_x_netting_pkg.ASCP_PURCHASE_ORDER
-- getting the org
AND t1.sr_tp_id = sup.organization_id
AND t1.sr_instance_id = sup.sr_instance_id
AND t1.partner_type = 3 --org
AND m1.tp_key = t1.partner_id
AND m1.map_type = 2
AND s1.company_site_id = m1.company_key
AND s1.company_id = c1.company_id
-- getting the supplier
AND rel.relationship_type = 2 --supplier
AND rel.object_id = c2.company_id -- supplier
AND rel.subject_id = c1.company_id --1
AND rel.relationship_id = m2.company_key
AND m2.tp_key = t2.partner_id
AND m2.map_type = 1 -- supp
AND t2.partner_id = sup.supplier_id
AND t2.partner_type = 1 -- supplier
-- getting the suppliersite
AND m3.tp_key = sup.supplier_site_id
AND m3.map_type = 3 --supp site
AND s2.company_site_id = m3.company_key
AND s2.company_id = c2.company_id
-- join to cp to get PO
AND sd.publisher_order_type = msc_x_netting_pkg.PURCHASE_ORDER
AND sd.inventory_item_id = sup.inventory_item_id
AND sup.order_number = sd.order_number || sd.release_number
AND nvl(sd.line_number,'-1') = nvl(purch_line_num, '-1')
AND sd.publisher_id = s1.company_id
AND sd.publisher_site_id = s1.company_site_id
AND sd.supplier_id = s2.company_id
AND sd.supplier_site_id = s2.company_site_id;
msc_x_netting_pkg.add_to_delete_tbl(
l_customer_id,
l_customer_site_id,
l_customer_id,
l_customer_site_id,
l_supplier_id,
l_supplier_site_id,
l_item_id,
msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP51,
l_po_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);
l_inserted_record := l_inserted_record + 1;
SELECT dem.order_number,
dem.sales_order_line_id, --sbala
dem.reservation_id,
s1.company_site_id, --- cp supplier_site_id -- so owner
c2.company_id,
s2.company_site_id,
dem.inventory_item_id,
sd.transaction_id,
sd.supplier_id,
sd.supplier_name,
sd.supplier_site_id,
sd.supplier_site_name,
sd.supplier_item_name,
sd.inventory_item_id,
sd.item_name,
sd.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.customer_id,
sd.customer_name,
sd.customer_site_id,
sd.customer_site_name,
sd.customer_item_name,
sd.customer_item_description
INTO l_ascp_demand_order_number,
l_ascp_so_line_id,
l_ascp_reservation_id,
l_cp_org_id,
l_cp_customer_id,
l_cp_customer_site_id,
l_cp_item_id,
l_so_trx_id,
l_supplier_id,
l_supplier_name,
l_supplier_site_id,
l_supplier_site_name,
l_supplier_item_name,
l_item_id,
l_item_name,
l_item_desc,
l_so_key_date,
l_so_ship_date,
l_so_receipt_date,
l_posting_so_qty,
l_so_qty,
l_tp_so_qty,
l_order_number,
l_release_number,
l_line_number,
l_customer_id,
l_customer_name,
l_customer_site_id,
l_customer_site_name,
l_customer_item_name,
l_customer_item_desc
FROM msc_demands dem,
msc_companies c1,
msc_company_sites s1,
msc_trading_partners t1,
msc_trading_partner_maps m1,
msc_trading_partners t2,
msc_trading_partner_maps m2,
msc_companies c2,
msc_company_sites s2,
msc_company_relationships rel,
msc_trading_partner_maps m3,
msc_sup_dem_entries sd,
msc_sales_orders mso
WHERE dem.demand_id = l_demand_id
AND dem.plan_id = l_plan_order_at_risk_id
AND dem.sr_instance_id = l_peg_sr_instance_id
AND dem.organization_id = l_peg_org_id
AND dem.inventory_item_id = l_peg_item_id
AND dem.origination_type in (msc_x_netting_pkg.ASCP_SALES_ORDER,
msc_x_netting_pkg.ASCP_SALES_ORDER_MDS)
--- sbala AND dem.customer_id is not null
-- getting the org
AND t1.sr_tp_id = dem.organization_id
AND t1.sr_instance_id = dem.sr_instance_id
AND t1.partner_type = 3 --org
AND m1.tp_key = t1.partner_id
AND m1.map_type = 2
AND s1.company_site_id = m1.company_key
AND s1.company_id = c1.company_id
-- getting the customer
AND rel.relationship_type = 1 --cust
AND rel.object_id = c2.company_id -- cust
AND rel.subject_id = c1.company_id --1
AND rel.relationship_id = m2.company_key
AND m2.tp_key = t2.partner_id
AND m2.map_type = 1 --cust
AND t2.partner_id = dem.customer_id
AND t2.partner_type = 2 -- cust
----sbala AND t2.sr_instance_id = dem.sr_instance_id
-- getting the customer site
AND m3.tp_key = dem.customer_site_id
AND m3.map_type = 3 --cust site
AND s2.company_site_id = m3.company_key
AND s2.company_id = c2.company_id
-- join to cp to get SO
AND dem.sr_instance_id = mso.sr_instance_id
AND dem.organization_id = mso.organization_id
AND dem.inventory_item_id = mso.inventory_item_id
AND dem.sales_order_line_id = mso.demand_source_line
AND sd.publisher_order_type = msc_x_netting_pkg.SALES_ORDER
AND sd.inventory_item_id = mso.inventory_item_id
AND sd.order_number = mso.sales_order_number
AND sd.line_number = mso.demand_source_line
AND sd.publisher_id = s1.company_id
AND sd.publisher_site_id = s1.company_site_id;
msc_x_netting_pkg.add_to_delete_tbl(
l_supplier_id,
l_supplier_site_id,
l_customer_id,
l_customer_site_id,
l_supplier_id,
l_supplier_site_id,
l_item_id,
msc_x_netting_pkg.G_POTENTIAL_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP50,
l_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);
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_POTENTIAL_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP14,
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_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_POTENTIAL_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP15,
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 count(*)
into l_count
FROM msc_x_exception_details d
WHERE d.exception_type = msc_x_netting_pkg.G_EXCEP4
AND d.transaction_id1 = b_trx_id1(j);
l_inserted_record := l_inserted_record + 1;
SELECT count(*)
INTO l_count
FROM msc_x_exception_details d
WHERE d.exception_type = msc_x_netting_pkg.G_EXCEP3
AND d.transaction_id1 = b_trx_id1(j);
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_POTENTIAL_LATE_ORDER,
msc_x_netting_pkg.G_EXCEP16,
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);
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_POTENTIAL_LATE_ORDER) || ':' || l_inserted_record);