[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct tp.sr_instance_id,
sd.publisher_id,
sd.publisher_name,
sd.publisher_site_id,
sd.publisher_site_name,
sd.customer_name,
sd.customer_id,
sd.customer_site_name,
sd.customer_site_id,
sd.inventory_item_id,
sd.item_name,
sd.tp_quantity,
sd.tp_uom_code,
sd.receipt_date,
sd.bucket_type,
sd.receipt_date,
sd.receipt_date,
mis.organization_id,
tp2.organization_code,
nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR),
--mis.DELIVERY_CALENDAR_CODE,
map2.tp_key, -- supplier_aps_id
map3.tp_key, -- supplier_site aps id
item1.ROUNDING_CONTROL_TYPE
FROM msc_sup_dem_entries_v sd,
msc_trading_partner_maps map1,
msc_trading_partners tp,
msc_system_items item,
msc_company_relationships cr,
msc_trading_partner_maps map2,
msc_trading_partner_maps map3,
msc_item_suppliers mis,
msc_trading_partners tp2,
msc_system_items item1
WHERE sd.plan_id = -1
AND sd.publisher_order_type = SUPPLY_COMMIT
AND sd.bucket_Type = G_DAY
AND map1.tp_key = tp.partner_id
AND map1.map_type = 2 -- company
AND map1.company_key = sd.customer_site_id
AND sd.customer_id = 1 --OEM
AND tp.partner_type = 3
AND tp.company_id is null
AND item.plan_id = -1
AND sd.inventory_item_id = item.inventory_item_id
AND item.sr_instance_id = tp.sr_instance_id
AND item.organization_id = tp.sr_tp_id
--AND sd.quantity > 0
AND nvl(item.abc_class_name,-1) = nvl(nvl(p_abc_class, item.abc_class_name),-1)
AND nvl(item.planner_code,-1) = nvl(nvl(p_planner, item.planner_code),-1)
AND item.inventory_item_id = nvl(p_item_id,item.inventory_item_id)
AND sd.publisher_id = nvl(p_sce_supplier_id,sd.publisher_id)
AND sd.publisher_site_id = nvl(p_sce_supplier_site_id,sd.publisher_site_id)
AND trunc(sd.receipt_date) between nvl(trunc(p_horizon_start_date),sd.receipt_date)
and nvl(trunc(p_horizon_end_date),sd.receipt_date)
and cr.object_id = sd.publisher_id
and cr.subject_id = sd.customer_id
and cr.relationship_type = 2
and map2.map_type = 1
and map2.company_key = cr.relationship_id
and map3.map_type = 3
and map3.company_key = sd.publisher_site_id
and mis.plan_id = item.plan_id
and mis.sr_instance_id = item.sr_instance_id
and mis.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID
and mis.SUPPLIER_ID = map2.tp_key
and mis.SUPPLIER_SITE_ID = map3.tp_key
and mis.using_organization_id = -1
and mis.sr_instance_id = tp2.sr_instance_id
and mis.organization_id = tp2.sr_tp_id
and tp2.partner_type = 3
and item1.plan_id = mis.plan_id
and item1.sr_instance_id = mis.sr_instance_id
and item1.organization_id = mis.organization_id
and item1.inventory_item_id = mis.inventory_item_id
UNION
SELECT distinct tp.sr_instance_id,
sd.publisher_id,
sd.publisher_name,
sd.publisher_site_id,
sd.publisher_site_name,
sd.customer_name,
sd.customer_id,
sd.customer_site_name,
sd.customer_site_id,
sd.inventory_item_id,
sd.item_name,
sd.tp_quantity,
sd.tp_uom_code,
sd.receipt_date,
sd.bucket_type,
mcd.week_start_date,
mcd.next_date-1,
mis.organization_id,
tp2.organization_code,
nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR),
--mis.DELIVERY_CALENDAR_CODE,
map2.tp_key,
map3.tp_key,
item1.ROUNDING_CONTROL_TYPE
FROM msc_sup_dem_entries_v sd,
msc_trading_partner_maps map1,
msc_trading_partners tp,
msc_system_items item,
MSC_CAL_WEEK_START_DATES mcd,
msc_company_relationships cr,
msc_trading_partner_maps map2,
msc_trading_partner_maps map3,
msc_item_suppliers mis,
msc_trading_partners tp2,
msc_system_items item1
WHERE sd.plan_id = -1
AND sd.publisher_order_type = SUPPLY_COMMIT
AND sd.bucket_Type = G_WEEK
AND map1.tp_key = tp.partner_id
AND map1.map_type = 2 -- company
AND map1.company_key = sd.customer_site_id
AND sd.customer_id = 1 --OEM
AND tp.partner_type = 3
AND tp.company_id is null
AND item.plan_id = -1
AND sd.inventory_item_id = item.inventory_item_id
AND item.sr_instance_id = tp.sr_instance_id
AND item.organization_id = tp.sr_tp_id
--AND sd.quantity > 0
AND nvl(item.abc_class_name,-1) = nvl(nvl(p_abc_class, item.abc_class_name),-1)
AND nvl(item.planner_code,-1) = nvl(nvl(p_planner, item.planner_code),-1)
AND item.inventory_item_id = nvl(p_item_id,item.inventory_item_id)
AND sd.publisher_id = nvl(p_sce_supplier_id,sd.publisher_id)
AND sd.publisher_site_id = nvl(p_sce_supplier_site_id,sd.publisher_site_id)
AND trunc(sd.receipt_date) between nvl(trunc(p_horizon_start_date),sd.receipt_date)
and nvl(trunc(p_horizon_end_date),sd.receipt_date)
AND mcd.sr_instance_id = decode(mis.delivery_calendar_code,null,p_cal_sr_instance_id
,tp.sr_instance_id)
AND mcd.CALENDAR_CODE = nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR)
AND mcd.EXCEPTION_SET_ID = -1
AND to_char(sd.receipt_date,'J') between to_char(mcd.WEEK_START_DATE,'J')
and to_char(mcd.NEXT_DATE,'J')
AND to_char(sd.receipt_date,'J') < TO_CHAR(mcd.NEXT_DATE,'J')
and cr.object_id = sd.publisher_id
and cr.subject_id = sd.customer_id
and cr.relationship_type = 2
and map2.map_type = 1
and map2.company_key = cr.relationship_id
and map3.map_type = 3
and map3.company_key = sd.publisher_site_id
and mis.plan_id = -1
and mis.sr_instance_id = item.sr_instance_id
and mis.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID
and mis.SUPPLIER_ID = map2.tp_key
and mis.SUPPLIER_SITE_ID = map3.tp_key
and mis.using_organization_id = -1
and mis.sr_instance_id = tp2.sr_instance_id
and mis.organization_id = tp2.sr_tp_id
and tp2.partner_type = 3
and item1.plan_id = mis.plan_id
and item1.sr_instance_id = mis.sr_instance_id
and item1.organization_id = mis.organization_id
and item1.inventory_item_id = mis.inventory_item_id
UNION
SELECT distinct tp.sr_instance_id,
sd.publisher_id,
sd.publisher_name,
sd.publisher_site_id,
sd.publisher_site_name,
sd.customer_name,
sd.customer_id,
sd.customer_site_name,
sd.customer_site_id,
sd.inventory_item_id,
sd.item_name,
sd.tp_quantity,
sd.tp_uom_code,
sd.receipt_date,
sd.bucket_type,
mpd.period_start_date,
mpd.next_Date-1,
mis.organization_id,
tp2.organization_code,
nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR),
--mis.DELIVERY_CALENDAR_CODE,
map2.tp_key,
map3.tp_key,
item1.ROUNDING_CONTROL_TYPE
FROM msc_sup_dem_entries_v sd,
msc_trading_partner_maps map1,
msc_trading_partners tp,
msc_system_items item,
MSC_PERIOD_START_DATES mpd,
msc_company_relationships cr,
msc_trading_partner_maps map2,
msc_trading_partner_maps map3,
msc_item_suppliers mis,
msc_trading_partners tp2,
msc_system_items item1
WHERE sd.plan_id = -1
AND sd.publisher_order_type = SUPPLY_COMMIT
AND sd.bucket_Type = G_MONTH
AND map1.tp_key = tp.partner_id
AND map1.map_type = 2 -- company
AND map1.company_key = sd.customer_site_id
AND sd.customer_id = 1 --OEM
AND tp.partner_type = 3
AND tp.company_id is null
AND item.plan_id = -1
AND sd.inventory_item_id = item.inventory_item_id
AND item.sr_instance_id = tp.sr_instance_id
AND item.organization_id = tp.sr_tp_id
--AND sd.quantity > 0
AND nvl(item.abc_class_name,-1) = nvl(nvl(p_abc_class, item.abc_class_name),-1)
AND nvl(item.planner_code,-1) = nvl(nvl(p_planner, item.planner_code),-1)
AND item.inventory_item_id = nvl(p_item_id,item.inventory_item_id)
AND sd.publisher_id = nvl(p_sce_supplier_id,sd.publisher_id)
AND sd.publisher_site_id = nvl(p_sce_supplier_site_id,sd.publisher_site_id)
AND trunc(sd.receipt_date) between nvl(trunc(p_horizon_start_date),sd.receipt_date)
and nvl(trunc(p_horizon_end_date),sd.receipt_date)
AND mpd.sr_instance_id = decode(mis.delivery_calendar_code,null,p_cal_sr_instance_id
,tp.sr_instance_id)
AND mpd.CALENDAR_CODE = nvl(mis.delivery_calendar_code,G_MSC_X_DEF_CALENDAR)
AND mpd.EXCEPTION_SET_ID = -1
AND to_char(sd.receipt_date,'J') between to_char(mpd.PERIOD_START_DATE,'J')
and to_char(mpd.NEXT_DATE,'J')
AND to_char(sd.receipt_date,'J') < TO_CHAR(mpd.NEXT_DATE,'J')
and cr.object_id = sd.publisher_id
and cr.subject_id = sd.customer_id
and cr.relationship_type = 2
and map2.map_type = 1
and map2.company_key = cr.relationship_id
and map3.map_type = 3
and map3.company_key = sd.publisher_site_id
and mis.plan_id = -1
and mis.sr_instance_id = item.sr_instance_id
--and mis.ORGANIZATION_ID = item.ORGANIZATION_ID
and mis.INVENTORY_ITEM_ID = item.INVENTORY_ITEM_ID
and mis.SUPPLIER_ID = map2.tp_key
and mis.SUPPLIER_SITE_ID = map3.tp_key
and mis.using_organization_id = -1
and mis.sr_instance_id = tp2.sr_instance_id
and mis.organization_id = tp2.sr_tp_id
and tp2.partner_type = 3
and item1.plan_id = mis.plan_id
and item1.sr_instance_id = mis.sr_instance_id
and item1.organization_id = mis.organization_id
and item1.inventory_item_id = mis.inventory_item_id
;
SELECT sum(sd.tp_quantity)
FROM msc_sup_dem_entries_v sd,
msc_trading_partner_maps map1,
msc_trading_partners tp,
msc_system_items item
WHERE sd.plan_id = -1
AND sd.publisher_order_type = SUPPLY_COMMIT
AND map1.tp_key = tp.partner_id
AND map1.map_type = 2 -- company
AND map1.company_key = sd.customer_site_id
AND sd.customer_id = 1 --OEM
AND tp.partner_type = 3
AND tp.company_id is null
AND item.plan_id = -1
AND sd.inventory_item_id = item.inventory_item_id
AND item.sr_instance_id = tp.sr_instance_id
AND item.organization_id = tp.sr_tp_id
--AND sd.quantity > 0
AND nvl(item.abc_class_name,-1) = nvl(nvl(p_abc_class, item.abc_class_name),-1)
AND nvl(item.planner_code,-1) = nvl(nvl(p_planner, item.planner_code),-1)
AND item.inventory_item_id = nvl(p_item_id,item.inventory_item_id)
AND sd.publisher_id = nvl(p_sce_supplier_id,sd.publisher_id)
AND sd.publisher_site_id = nvl(p_sce_supplier_site_id,sd.publisher_site_id)
AND trunc(sd.receipt_date) between nvl(trunc(p_horizon_start_date),sd.receipt_date)
and nvl(trunc(p_horizon_end_date),sd.receipt_date)
AND sd.bucket_Type = p_bucket_type;
insert_row_count Number := 0;
SELECT distinct sr_tp_id, organization_code
FROM msc_trading_partners
WHERE sr_instance_id = p_sr_instance_id
AND partner_type = 3
AND company_id is null; -- if type is 3 (org) then use sr_tp_id
SELECT distinct mis.organization_id, tp.organization_code
FROM msc_item_suppliers mis, msc_trading_partners tp
WHERE plan_id = -1
AND mis.sr_instance_id = p_sr_instance_id
AND mis.supplier_id = p_aps_supplier_id
AND mis.supplier_site_id = p_aps_supplier_site_id
AND mis.inventory_item_id = p_item_id
AND mis.using_organization_id = -1
AND tp.sr_instance_id = mis.sr_instance_id
AND tp.sr_tp_id = mis.organization_id;
select msc_collection_s.nextval
into l_refresh_number
from dual;
select sr_instance_id
into G_CAL_INSTANCE_ID
from msc_calendar_dates
where calendar_code = G_MSC_X_DEF_CALENDAR
and rownum = 1;
SELECT distinct c.company_id
INTO l_map_supplier_id
FROM msc_trading_partner_maps map1,
msc_company_relationships rel,
msc_companies c,
msc_trading_partners tp
WHERE rel.relationship_type =2 AND --supplier
rel.object_id = c.company_id AND
rel.subject_id = 1 AND --other company (OEM)
rel.relationship_id = map1.company_key AND
map1.tp_key = tp.partner_id AND
map1.map_type = 1 AND --company
tp.partner_id = p_supplier_id AND
tp.partner_type = 1;
SELECT distinct s.company_site_id
INTO l_map_supplier_site_id
FROM msc_trading_partner_maps map1,
msc_companies c,
msc_company_sites s,
msc_trading_partners tp,
msc_trading_partner_sites tps
WHERE map1.map_type = 3 AND
map1.tp_key = tps.partner_site_id AND
tps.partner_site_id = p_supplier_site_id AND
tp.partner_id = tps.partner_id AND
tp.partner_type = 1 AND
map1.company_key = s.company_site_id AND
s.company_id = c.company_id;
DELETE msc_supplier_capacities
WHERE plan_id = -1
AND inventory_item_id = nvl(p_item_id , inventory_item_id)
AND supplier_id = nvl(p_supplier_id , supplier_id)
AND supplier_site_id = nvl(p_supplier_site_id , supplier_site_id)
AND using_organization_id = -1 ;
LOG_MESSAGE( 'Delete all record ' ||sql%rowcount);
LOG_MESSAGE('No record to be deleted in msc_supplier_capacities.');
DELETE msc_supplier_capacities
WHERE plan_id = -1
AND inventory_item_id = nvl(p_item_id , inventory_item_id)
AND supplier_id = nvl(p_supplier_id , supplier_id)
AND supplier_site_id = nvl(p_supplier_site_id , supplier_site_id)
AND using_organization_id = -1
AND (((from_date between nvl(l_horizon_start,from_date) and nvl(l_horizon_end,from_date))
AND (to_date between nvl(l_horizon_start,to_date) and nvl(l_horizon_end,to_date)))
OR (collected_flag in (1, 2)));
LOG_MESSAGE('Delete based on horizon_date ' ||sql%rowcount);
LOG_MESSAGE('No record to be deleted in msc_supplier_capacities for overwrite_all option.');
SELECT distinct tp.partner_id
INTO l_aps_supplier_id
FROM msc_trading_partner_maps map1,
msc_company_relationships rel,
msc_item_suppliers sup,
msc_trading_partners tp
WHERE rel.relationship_type =2 AND --supplier
rel.object_id = l_sce_supplier_id AND
rel.subject_id = 1 AND --other company (OEM)
rel.relationship_id = map1.company_key AND
map1.tp_key = tp.partner_id AND
map1.map_type = 1 AND --company
sup.plan_id = -1 AND
sup.inventory_item_id = l_item_id AND
tp.partner_id = sup.supplier_id AND
tp.partner_type = 1;
SELECT distinct tps.partner_site_id
INTO l_aps_supplier_site_id
FROM msc_trading_partner_maps map1,
msc_companies c,
msc_company_sites s,
msc_trading_partners tp,
msc_trading_partner_sites tps
WHERE map1.map_type = 3 AND
map1.tp_key = tps.partner_site_id AND
tp.partner_id = tps.partner_id AND
map1.company_key = s.company_site_id AND
s.company_site_id = l_sce_supplier_site_id AND
s.company_id = c.company_id AND
c.company_id = l_sce_supplier_id AND
nvl(tp.company_id,1) = 1;
SELECT distinct c.company_id
INTO l_map_supplier_id
FROM msc_trading_partner_maps map1,
msc_company_relationships rel,
msc_companies c,
msc_trading_partners tp
WHERE rel.relationship_type =2 AND --supplier
rel.object_id = c.company_id AND
rel.subject_id = 1 AND --other company (OEM)
rel.relationship_id = map1.company_key AND
map1.tp_key = tp.partner_id AND
map1.map_type = 1 AND --company
tp.partner_id = l_aps_supplier_id AND
tp.partner_type = 1;
SELECT distinct s.company_site_id
INTO l_map_supplier_site_id
FROM msc_trading_partner_maps map1,
msc_companies c,
msc_company_sites s,
msc_trading_partners tp,
msc_trading_partner_sites tps
WHERE map1.map_type = 3 AND
map1.tp_key = tps.partner_site_id AND
tps.partner_site_id = l_aps_supplier_site_id AND
tp.partner_id = tps.partner_id AND
tp.partner_type = 1 AND
map1.company_key = s.company_site_id AND
s.company_id = c.company_id;
| Now update back the current data that are receiving from exchange
-------------------------------------------------------------------------*/
begin
update msc_supplier_capacities
set last_update_login = null
where plan_id = -1
and last_update_login = -999;
update msc_supplies
set last_update_login = null
where plan_id = -1
and order_type = 5
and last_update_login = -999;
SELECT distinct tp.sr_tp_id, tp.organization_code
INTO l_mod_org_id, l_mod_org_code
FROM msc_trading_partners tp
WHERE tp.sr_instance_id = p_sr_instance_id AND
tp.modeled_supplier_id = p_supplier_id AND
tp.modeled_supplier_site_id = p_supplier_site_id AND
tp.partner_type = 3 AND
tp.company_id is null;
select to_char(sysdate, 'MON') into l_month from dual;
select to_char(sysdate, 'YYYY') into l_year from dual;
select to_char(sysdate, 'W') into l_week from dual;
--select last_day(l_from_date) into l_to_date from dual;
--select p_receipt_date + 6 into l_to_date from dual;
SELECT transaction_id,from_date, to_date, capacity
FROM msc_supplier_capacities
WHERE plan_id = -1
AND sr_instance_id = p_sr_instance_id
AND organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
AND using_organization_id = -1
AND nvl(last_update_login,-1) <> -999
AND from_date <= nvl(p_horizon_end_date, from_date)
AND nvl(to_date,p_horizon_start_date) >= nvl(p_horizon_start_date,to_date)
UNION
/*-----------------------------------------------------------------------
this statement will take care where p_horizon_start_date is null
and to_date is null
-----------------------------------------------------------------------*/
SELECT transaction_id,from_date, to_date, capacity
FROM msc_supplier_capacities
WHERE plan_id = -1
AND sr_instance_id = p_sr_instance_id
AND organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
AND using_organization_id = -1
AND nvl(last_update_login,-1) <> -999
AND from_date <= nvl(p_horizon_start_date, from_date)
AND nvl(to_date,p_horizon_end_date) >= nvl(p_horizon_end_date,to_date)
ORDER BY transaction_id;
IF l_from_date < p_horizon_start_date -- update the from_date,
then insert later when l_to_date > p_horizon_end_date
Also works for p_horizon_end_date is null -- just update the to_date
-------------------------------------------------------------------------*/
UPDATE msc_supplier_capacities
set to_date = p_horizon_start_date -1
WHERE plan_id = -1
and transaction_id = l_trx_id;
--dbms_output.put_line('INSERT ' || l_to_date || ' cap ' || l_original_capacity);
insert_capacity(p_sr_instance_id,
p_organization_id,
p_supplier_id,
p_supplier_site_id,
p_item_id,
p_horizon_end_date + 1,
l_to_date,
l_original_capacity,
p_refresh_number);
UPDATE msc_supplier_capacities
set from_date = p_horizon_end_date + 1
WHERE plan_id = -1
and transaction_id = l_trx_id;
UPDATE msc_supplier_capacities
set from_date = p_horizon_end_date + 1
WHERE plan_id = -1
and transaction_id = l_trx_id;
IF l_from_date >= p_horizon_start_date -- just update from_date
IF l_from_date < p_horizon_start_date -- update the from_date, then insert later
IF l_from_date = p_horizon_end_date -- just update the from_date
Also works for p_horizon_start_date is null -- just update the from_date
-------------------------------------------------------------------------*/
--dbms_output.put_line('HZ ' || p_horizon_start_date || p_horizon_end_date);
UPDATE msc_supplier_capacities
set from_date = p_horizon_end_date + 1,
to_date = null
WHERE plan_id = -1
and transaction_id = l_trx_id;
insert_capacity (p_sr_instance_id,
p_organization_id,
p_supplier_id,
p_supplier_site_id,
p_item_id,
l_from_date,
p_horizon_start_date - 1,
l_original_capacity,
p_refresh_number);
delete msc_supplier_capacities
where transaction_id = l_trx_id;
UPDATE msc_supplier_capacities
set to_date = p_horizon_start_date -1
where plan_id = -1
and transaction_id = l_trx_id;
insert_capacity(p_sr_instance_id,
p_organization_id,
p_supplier_id,
p_supplier_site_id,
p_item_id,
p_date,
p_date,
p_capacity,
p_refresh_number);
PROCEDURE Insert_Capacity(p_sr_instance_id IN Number,
p_organization_id IN Number,
p_supplier_id IN Number,
p_supplier_site_id IN Number,
p_item_id IN Number,
p_from_date IN Date,
p_to_date IN Date,
p_capacity IN Number,
p_refresh_number In Number) IS
l_nextid Number;
select count(*)
into l_exist
FROM msc_supplier_capacities
WHERE plan_id = -1
AND sr_instance_id = p_sr_instance_id
AND organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
AND from_date = p_from_date
AND to_date = p_to_date
AND using_organization_id = -1
AND nvl(last_update_login,-1) = -999;
--LOG_MESSAGE( 'update qty ' || p_capacity);
UPDATE msc_supplier_capacities
set capacity = capacity + p_capacity
WHERE plan_id = -1
AND sr_instance_id = p_sr_instance_id
AND organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
AND from_date = p_from_date
AND to_date = p_to_date
AND using_organization_id = -1
AND nvl(last_update_login,-1) = -999;
LOG_MESSAGE('insert capacity');
select msc_supplier_capacities_s.nextval
into l_nextid
from dual;
insert into msc_supplier_capacities (
transaction_id,
plan_id,
organization_id,
sr_instance_id,
supplier_id,
supplier_site_id,
inventory_item_id,
from_date,
to_date,
capacity,
using_organization_id,
refresh_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
status,
applied,
collected_flag,
last_update_login)
values (
l_nextid,
-1, --plan_id
p_organization_id, --organization_id,
p_sr_instance_id,
p_supplier_id,
p_supplier_site_id, --p_supplier_site_id,
p_item_id,
p_from_date, --from_date,
p_to_date, --to_date
p_capacity, --capacity,
-1, --using_organization_id,
p_refresh_number, --refresh_number,
sysdate, --last_update_date,
l_user_id, --last_updated_by,
sysdate, --creation_date,
l_user_id, --created_by,
null, --status
null, --applied
3, --1, --collected_flag
-999); -- to distinguish the data from receive from exchange
LOG_MESSAGE('Error in insert capacity ' || sqlerrm);
LOG_MESSAGE( 'Insert_capacity' || sqlerrm);
END INSERT_CAPACITY;
PROCEDURE Update_Capacity(p_sr_instance_id IN Number,
p_organization_id IN Number,
p_supplier_id IN Number,
p_supplier_site_id IN Number,
p_item_id IN Number,
p_from_date IN Date,
p_to_date IN Date,
p_capacity IN Number,
p_transaction_id IN Number) IS
l_exist Number := 0;
SELECT 1 into l_exist from dual
WHERE exists (SELECT 1
from msc_supplier_capacities
where plan_id = -1
and sr_instance_id = p_sr_instance_id
and transaction_id = p_transaction_id
and inventory_item_id = p_item_id
and organization_id = p_organization_id
and supplier_id = p_supplier_id
and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
and trunc(from_date) = trunc(p_from_date)
and trunc(nvl(to_date,from_date)) = trunc(p_to_date)
and nvl(last_update_login,-1) <> -999);
LOG_MESSAGE('update sc and override the collected/manually data');
update msc_supplier_capacities
set capacity = p_capacity,
from_date = p_from_date,
to_date = p_to_date,
collected_flag = 3, -- bug 5208105
last_update_login = -999
where plan_id = -1
and sr_instance_id = p_sr_instance_id
and transaction_id = p_transaction_id
and inventory_item_id = p_item_id
and organization_id = p_organization_id
and supplier_id = p_supplier_id
and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
and trunc(from_date) = trunc(p_from_date)
and trunc(nvl(to_date,from_date)) = trunc(p_to_date)
and nvl(last_update_login,-1) <> -999;
update msc_supplier_capacities
set capacity = capacity + p_capacity,
from_date = p_from_date,
to_date = p_to_date,
collected_flag = 3, -- bug 5208105
last_update_login = -999
where plan_id = -1
and sr_instance_id = p_sr_instance_id
and transaction_id = p_transaction_id
and inventory_item_id = p_item_id
and organization_id = p_organization_id
and supplier_id = p_supplier_id
and nvl(supplier_site_id,-1) = nvl(p_supplier_site_id,-1)
and trunc(from_date) = trunc(p_from_date)
and trunc(nvl(to_date,from_date)) = trunc(p_to_date)
and last_update_login = -999;
LOG_MESSAGE('Error in update capacity' || sqlerrm);
END UPDATE_CAPACITY;
SELECT 'CP' ||
substr(p_mod_org_code,1,instr(p_mod_org_code,':')-1) ||
'-' || substr(p_mod_org_code,instr(p_mod_org_code,':')+1,7)
INTO l_mps_designator
FROM dual;
SELECT designator_id, designator
INTO l_mps_designator_id, l_mps_designator
FROM msc_designators
WHERE sr_instance_id = p_sr_instance_id
AND organization_id = p_mod_org_id
AND designator_type = 2
AND designator = l_mps_designator;
Insert_MPS_Designator(p_sr_instance_id,
p_organization_id,
p_supplier_id,
p_supplier_site_id,
l_mps_designator,
p_refresh_number,
l_mps_designator_id
);
select designator
into l_mps_designator
from msc_designators
where designator_id = p_mps_designator_id;
Insert_Supply_Schedule(p_sr_instance_id,
p_organization_id,
p_supplier_id,
p_supplier_site_id,
l_mps_designator_id,
p_item_id,
p_date,
p_capacity,
p_refresh_number);
PROCEDURE Insert_MPS_Designator(p_sr_instance_id IN Number,
p_organization_id IN Number,
p_supplier_id In Number,
p_supplier_site_id In Number,
p_mps_designator IN Varchar2,
p_refresh_number IN Number,
p_mps_designator_id OUT NOCOPY Number) IS
l_nextid Number;
LOG_MESSAGE('insert mps designator');
select msc_designators_s.nextval
into p_mps_designator_id
from dual;
select partner_name
into l_supplier_name
from msc_trading_partners
where partner_id = p_supplier_id
and partner_type = 1;
select tp_site_code
into l_supplier_site_name
from msc_trading_partner_sites
where partner_id = p_supplier_id
and partner_site_id = p_supplier_site_id;
insert into msc_designators (
designator_id,
designator,
organization_id,
sr_instance_id,
designator_type,
mps_relief,
inventory_atp_flag,
description,
organization_selection,
production,
disable_date,
refresh_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
collected_flag)
values (
p_mps_designator_id,
p_mps_designator,
p_organization_id,
p_sr_instance_id,
2,
2,
2,
substr(l_desc,1,50),
1,
2,
null,
p_refresh_number,
sysdate,
l_user_id,
sysdate,
l_user_id,
null);
LOG_MESSAGE('Error in insert mps designator ' || sqlerrm);
END INSERT_MPS_DESIGNATOR;
PROCEDURE Insert_Supply_schedule(p_sr_instance_id IN Number,
p_organization_id IN Number,
p_supplier_id IN Number,
p_supplier_site_id IN Number,
p_mps_designator_id IN Number,
p_item_id IN Number,
p_date IN Date,
p_capacity IN Number,
p_refresh_number IN Number) IS
l_nextid Number;
DELETE msc_supplies
WHERE plan_id = -1
AND sr_instance_id = p_sr_instance_id
AND organization_id = p_organization_id
AND schedule_designator_id = p_mps_designator_id
AND inventory_item_id = p_item_id
AND order_type = 5
AND nvl(last_update_login,-1) <> -999;
LOG_MESSAGE('insert msc supplies');
select msc_supplies_s.nextval
into l_nextid
from dual;
insert into msc_supplies (plan_id,
transaction_id,
organization_id,
sr_instance_id,
inventory_item_id,
schedule_designator_id,
new_schedule_date,
order_type,
--supplier_id,
--supplier_site_id,
new_order_quantity,
firm_planned_type,
refresh_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
values (-1,
l_nextid,
p_organization_id, --organization_id,
p_sr_instance_id,
p_item_id,
p_mps_designator_id,
p_date,
5, --planned order
--p_supplier_id,
--p_supplier_site_id, --p_supplier_site_id,
p_capacity, --capacity,
2, --firm planned type
p_refresh_number, --refresh_number,
sysdate, --last_update_date,
l_user_id, --last_updated_by,
sysdate, --creation_date,
l_user_id,
-999);
LOG_MESSAGE('Error in insert supply schedule ' || sqlerrm);
END INSERT_SUPPLY_SCHEDULE;