[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select NVL(max(last_refresh_number), 0)
into l_curr_max_refresh_number
from msc_sup_dem_entries
where plan_id = -1;
select status
into l_last_max_refresh_number
from msc_plan_org_status
where plan_id = -1
and organization_id = -1
and sr_instance_id = -1;
insert into msc_plan_org_status (plan_id,
organization_id,
sr_instance_id,
status,
status_date,
number1)
values( CP_PLAN_ID,
-1,
-1,
l_curr_max_refresh_number,
sysdate,
p_replenish_time_fence);
SELECT msc_sup_dem_entries_s.nextval
INTO l_rep_transaction_id FROM DUAL;
update msc_plan_org_status
set status = l_curr_max_refresh_number,
status_date = sysdate
, number1 = p_replenish_time_fence
where plan_id = -1
and organization_id = -1
and sr_instance_id = -1;
SELECT msc_sup_dem_entries_s.nextval
INTO l_rep_transaction_id FROM DUAL;
print_debug_info(' before insert replenishment record, transaction ID = '
|| l_rep_transaction_id
);
INSERT INTO msc_sup_dem_entries
(
transaction_id
, plan_id
, sr_instance_id
, publisher_id
, publisher_site_id
, publisher_name
, publisher_site_name
, new_schedule_date
, key_date
, inventory_item_id
, publisher_order_type
, supplier_id
, supplier_name
, supplier_site_id
, supplier_site_name
, customer_id
, customer_name
, customer_site_id
, customer_site_name
, new_order_placement_date
, item_name
, owner_item_name
, customer_item_name
, supplier_item_name
, publisher_order_type_desc
, request_id
, program_id
, program_application_id
, program_update_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, uom_code
, quantity
, primary_uom
, primary_quantity
, tp_uom_code
, tp_quantity
, pub_item_description
, tp_item_description
, release_status
, receipt_date
, quantity_in_process
, implemented_quantity
, item_description
, customer_item_description
, supplier_item_description
, owner_item_description
) VALUES
(
l_rep_transaction_id,
CP_PLAN_ID,
l_sr_instance_id,
OEM_COMPANY_ID,
-1,
l_oem_company_name,
NULL,
sysdate,
sysdate,
l_item_id,
REPLENISHMENT,
OEM_COMPANY_ID,
l_oem_company_name,
NULL,
NULL,
l_cust_id,
l_customer_name,
l_cust_site_id,
l_customer_site_name,
SYSDATE,
l_item_name,
l_item_name,
NULL,
l_item_name,
msc_x_util.get_lookup_meaning('MSC_X_ORDER_TYPE', REPLENISHMENT),
FND_GLOBAL.CONC_REQUEST_ID, -- request_id
FND_GLOBAL.CONC_PROGRAM_ID, -- program_id
FND_GLOBAL.PROG_APPL_ID, -- program_application_id
null, -- program_update_date
FND_GLOBAL.USER_ID, -- created_by
SYSDATE, -- creation_date
FND_GLOBAL.USER_ID, -- last_updated_by
SYSDATE, -- last_update_date
FND_GLOBAL.LOGIN_ID, -- last_update_login
l_uom_code,
l_order_quantity,
l_uom_code,
l_order_quantity,
l_uom_code,
l_order_quantity,
l_item_description,
l_item_description,
UNRELEASED,
l_time_fence_end_date, -- Add receipt date
0,
0,
l_item_description,
NULL,
l_item_description,
l_item_description);
print_debug_info(' number of replenishment record inserted = '
|| SQL%ROWCOUNT
);
/* repl exists, update */
/* jguo added the following code to abort the previous Workflow
process */
-- find the WF key for the previous unclosed Workflow process
l_old_wf_key := TO_CHAR(l_item_id)
|| '-' || TO_CHAR(OEM_COMPANY_ID)
|| '-' || TO_CHAR(l_aps_customer_id)
|| '-' || TO_CHAR(l_aps_customer_site_id)
|| '-' || TO_CHAR(l_old_rep_transaction_id)
;
print_debug_info(' before update replenishment record, transaction ID = '
|| l_rep_transaction_id
);
/* update repl row */
UPDATE msc_sup_dem_entries sd
SET
transaction_id = l_rep_transaction_id
, uom_code = l_uom_code
, quantity = l_order_quantity
, primary_uom = l_uom_code
, primary_quantity = l_order_quantity
, tp_uom_code = l_uom_code
, tp_quantity = l_order_quantity
, new_schedule_date = SYSDATE
, key_date = SYSDATE
, receipt_date = l_time_fence_end_date --- SBALA
, release_status = UNRELEASED
, new_dock_date = NULL -- SBALA
, publisher_name = l_oem_company_name
, publisher_site_name = NULL
, supplier_name = l_oem_company_name
, supplier_site_name = NULL
, quantity_in_process = 0
, implemented_quantity = 0
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
, customer_id = l_cust_id
, customer_name = l_customer_name
, customer_site_id = l_cust_site_id
, customer_site_name = l_customer_site_name
, new_order_placement_date = SYSDATE
, publisher_order_type_desc =
msc_x_util.get_lookup_meaning('MSC_X_ORDER_TYPE',
REPLENISHMENT)
, pub_item_description = l_item_description
, tp_item_description = l_item_description
, item_description = l_item_description
, customer_item_description = NULL
, supplier_item_description = l_item_description
, owner_item_description = l_item_description
WHERE transaction_id = l_old_rep_transaction_id
AND sd.publisher_order_type = REPLENISHMENT
;
print_debug_info(' number of replenishment record updated = '
|| SQL%ROWCOUNT
);
select organization_code
into l_supplier_site_name
from msc_trading_partners
where partner_type = 3
and sr_instance_id = l_sr_instance_id
and sr_tp_id = l_source_org_id;
if(l_repl_row_found = SYS_YES) then /* repl exists, delete */
print_debug_info(' before delete replenishment record = '
|| l_rep_transaction_id
);
DELETE FROM msc_sup_dem_entries sd
WHERE sd.publisher_id = OEM_COMPANY_ID
AND sd.inventory_item_id = l_item_id
AND sd.publisher_order_type = REPLENISHMENT
AND sd.plan_id = CP_PLAN_ID
AND sd.customer_site_id = l_cust_site_id
AND sd.customer_id = l_cust_id;
print_debug_info(' number of replenishment record deleted = '
|| SQL%ROWCOUNT
);
SELECT
sup_dem.inventory_item_id,
msi.organization_id,
mtp.sr_instance_id,
nvl(sup_dem.customer_id, sup_dem.publisher_id),
nvl(sup_dem.customer_site_id, sup_dem.publisher_site_id),
sup_dem.publisher_order_type ,
sup_dem.transaction_id,
DECODE(sup_dem.publisher_order_type,
ALLOCATED_ONHAND, sup_dem.primary_quantity,
0),
DECODE(nvl(msi.consigned_flag, UNCONSIGNED),
UNCONSIGNED, DECODE(sup_dem.publisher_order_type,
UNALLOCATED_ONHAND, sup_dem.primary_quantity,
0),
0),
DECODE(sup_dem.publisher_order_type,
ASN, sup_dem.primary_quantity,
0),
DECODE(nvl(msi.consigned_flag, UNCONSIGNED),
UNCONSIGNED, DECODE(sup_dem.publisher_order_type,
SALES_ORDER,
DECODE(nvl(sup_dem.internal_flag, SYS_NO),
SYS_NO, sup_dem.primary_quantity,
0),
0),
0),
DECODE(nvl(msi.consigned_flag,UNCONSIGNED),
CONSIGNED, DECODE(sup_dem.publisher_order_type,
SALES_ORDER,
DECODE(sup_dem.internal_flag, SYS_YES,
sup_dem.primary_quantity,
0),
0),
0),
DECODE(nvl(msi.consigned_flag, UNCONSIGNED),
CONSIGNED, DECODE(sup_dem.publisher_order_type,
REQUISITION, DECODE(sup_dem.internal_flag,
SYS_YES, DECODE(
nvl(sup_dem.link_trans_id,
NOT_EXISTS),
NOT_EXISTS,
sup_dem.primary_quantity,
0),
0),
0),
0),
DECODE(sup_dem.publisher_order_type,
REPLENISHMENT, sup_dem.primary_quantity,
0),
nvl(msi.consigned_flag, UNCONSIGNED),
nvl(msi.vmi_minimum_units, -1),
nvl(msi.vmi_maximum_units, -1),
nvl(msi.vmi_minimum_days, -1),
nvl(msi.vmi_maximum_days, -1),
nvl(msi.vmi_fixed_order_quantity, -1),
-- nvl(msi.average_daily_demand, 0),
nvl(mvt.average_daily_demand, 0),
nvl(msi.fixed_lot_multiplier, -1),
nvl(msi.rounding_control_type, -1),
nvl(sup_dem.order_number, '-1'),
nvl(sup_dem.line_number, '-1'),
nvl(sup_dem.release_number, '-1'),
sup_dem.key_date,
nvl(sup_dem.receipt_date, sup_dem.key_date),
oem.company_name,
customer.company_name,
customer_site.company_site_name,
msi.item_name,
msi.description,
msi.uom_code,
sup_dem.primary_uom,
nvl(msi.asn_autoexpire_flag, SYS_NO),
nvl(msi.source_org_id, NOT_EXISTS),
msi.so_authorization_flag,
msi.planner_code, -- mp.user_name,
-- mpc.name,
msi.sr_inventory_item_id,
mtp.modeled_customer_id,
mtp.modeled_customer_site_id,
nvl(msi.full_lead_time, 0),
nvl(msi.preprocessing_lead_time, 0),
nvl(msi.postprocessing_lead_time, 0),
1
FROM
-- msc_partner_contacts mpc,
-- msc_planners mp,
msc_companies customer,
msc_company_sites customer_site,
msc_companies oem,
msc_system_items msi,
msc_sup_dem_entries sup_dem,
msc_sup_dem_entries sd,
msc_trading_partners mtp,
msc_trading_partner_maps map1,
msc_trading_partner_maps map2,
msc_company_relationships mcr
, msc_vmi_temp mvt
WHERE
-- mpc.partner_type (+)= 2 ---Customer
-- AND mpc.sr_instance_id (+)= mtp.sr_instance_id
-- AND mpc.partner_site_id (+)= mtp.modeled_customer_site_id
-- AND mpc.partner_id (+)= mtp.modeled_customer_id
-- AND mp.planner_code (+)= msi.planner_code
-- AND mp.organization_id (+)= msi.organization_id
-- AND mp.sr_instance_id (+)= msi.sr_instance_id
customer.company_id = customer_site.company_id
AND customer_site.company_site_id = map2.company_key
AND oem.company_id = mcr.subject_id
AND sup_dem.plan_id = sd.plan_id
AND sup_dem.inventory_item_id = sd.inventory_item_id
AND nvl(sup_dem.customer_id, sup_dem.publisher_id)
= nvl(sd.customer_id, sd.publisher_id)
AND nvl(sup_dem.customer_site_id, sup_dem.publisher_site_id) =
nvl(sd.customer_site_id,
sd.publisher_site_id)
AND sup_dem.publisher_order_type in
(UNALLOCATED_ONHAND, ALLOCATED_ONHAND,
SALES_ORDER, REQUISITION,
ASN,
REPLENISHMENT)
AND nvl(sup_dem.supplier_id, -1) = DECODE(
sup_dem.publisher_order_type,
UNALLOCATED_ONHAND, -1,
OEM_COMPANY_ID)
AND msi.inventory_planning_code = VMI_PLANNING_METHOD
AND msi.sr_instance_id = mtp.sr_instance_id
AND msi.organization_id = mtp.sr_tp_id
AND msi.inventory_item_id = sd.inventory_item_id
AND msi.plan_id = CP_PLAN_ID
AND mtp.partner_type = 3
AND map2.map_type = 3
AND map2.tp_key = mtp.modeled_customer_site_id
AND map1.map_type = 1
AND map1.tp_key = mtp.modeled_customer_id
AND map1.company_key = mcr.relationship_id
AND mcr.subject_id = OEM_COMPANY_ID
AND mcr.relationship_type = CUSTOMER_OF
AND nvl(sd.customer_id, -1) <> OEM_COMPANY_ID
AND DECODE(sd.publisher_order_type, UNALLOCATED_ONHAND,
sd.publisher_site_id, sd.customer_site_id) =
map2.company_key
AND DECODE(sd.publisher_order_type,
UNALLOCATED_ONHAND, sd.publisher_id,
sd.customer_id) = mcr.object_id
AND nvl(sd.supplier_id, -1) = DECODE(sd.publisher_order_type,
UNALLOCATED_ONHAND, -1,
OEM_COMPANY_ID)
AND sd.publisher_order_type in
(UNALLOCATED_ONHAND, ALLOCATED_ONHAND,
SALES_ORDER, REQUISITION,
ASN,
REPLENISHMENT)
AND sd.plan_id = CP_PLAN_ID
AND msi.vmi_refresh_flag in (REFRESHED, NOT_REFRESHED)
AND nvl(sd.last_refresh_number,-1) > DECODE(msi.vmi_refresh_flag,
NOT_REFRESHED,p_last_max_refresh_number,
-99)
and mvt.plan_id = msi.plan_id
and mvt.inventory_item_id = msi.inventory_item_id
and mvt.organization_id = msi.organization_id
and mvt.sr_instance_id = msi.sr_instance_id
and mvt.vmi_type = 2 -- customer facing vmi
UNION /* items with no data */
SELECT msi.inventory_item_id,
msi.organization_id,
msi.sr_instance_id,
mcr.object_id,
map2.company_key,
NOT_EXISTS, ---- no order type
0, -- transaction id
0, --- alloc on hand qty
0, --- unalloc on hand qty
0, --- ASN qty
0, --- Sales order qty
0, ---- int so qty
0, ---- int req qty
0, --- repl qty
nvl(msi.consigned_flag, UNCONSIGNED),
nvl(msi.vmi_minimum_units, -1),
nvl(msi.vmi_maximum_units, -1),
nvl(msi.vmi_minimum_days, -1),
nvl(msi.vmi_maximum_days, -1),
nvl(msi.vmi_fixed_order_quantity, -1),
-- nvl(msi.average_daily_demand, 0),
nvl(mvt.average_daily_demand, 0),
nvl(msi.fixed_lot_multiplier, -1),
NVL(msi.rounding_control_type, -1),
NULL, -- order number
NULL, -- line number
NULL, -- release number,
sysdate, --- key date
sysdate, ---- receipt date
oem.company_name,
customer.company_name,
customer_site.company_site_name,
msi.item_name,
msi.description,
msi.uom_code,
NULL, -- primary_uom
nvl(msi.asn_autoexpire_flag, SYS_NO),
nvl(msi.source_org_id, NOT_EXISTS),
msi.so_authorization_flag,
msi.planner_code, -- mp.user_name,
-- mpc.name,
msi.sr_inventory_item_id,
mtp.modeled_customer_id,
mtp.modeled_customer_site_id,
nvl(msi.full_lead_time, 0),
nvl(msi.preprocessing_lead_time, 0),
nvl(msi.postprocessing_lead_time, 0),
2
FROM
-- msc_partner_contacts mpc,
-- msc_planners mp,
msc_companies customer,
msc_company_sites customer_site,
msc_companies oem,
msc_system_items msi,
msc_trading_partners mtp,
msc_trading_partner_maps map1,
msc_trading_partner_maps map2,
msc_company_relationships mcr
, msc_vmi_temp mvt
WHERE
-- mpc.partner_type (+)= 2 ---Customer
-- AND mpc.sr_instance_id (+)= mtp.sr_instance_id
-- AND mpc.partner_site_id (+)= mtp.modeled_customer_site_id
-- AND mpc.partner_id (+)= mtp.modeled_customer_id
-- AND mp.planner_code (+)= msi.planner_code
-- AND mp.organization_id (+)= msi.organization_id
-- AND mp.sr_instance_id (+)= msi.sr_instance_id
customer.company_id = customer_site.company_id
AND customer_site.company_site_id = map2.company_key
AND oem.company_id = mcr.subject_id
AND map2.map_type = 3
AND map2.tp_key = mtp.modeled_customer_site_id
AND map1.map_type = 1
AND map1.company_key = mcr.relationship_id
AND mcr.subject_id = OEM_COMPANY_ID
AND mcr.relationship_type = CUSTOMER_OF
AND mtp.modeled_customer_id = map1.tp_key
AND mtp.modeled_customer_site_id is NOT NULL
AND mtp.modeled_customer_id is NOT NULL
AND mtp.partner_type = 3
AND msi.inventory_planning_code = VMI_PLANNING_METHOD
AND msi.sr_instance_id = mtp.sr_instance_id
AND msi.organization_id = mtp.sr_tp_id
AND msi.plan_id = CP_PLAN_ID
AND 0 = (select count(*) from
msc_sup_dem_entries txns
where txns.inventory_item_id = msi.inventory_item_id
and txns.plan_id = msi.plan_id
and DECODE(txns.publisher_order_type,
UNALLOCATED_ONHAND, txns.publisher_id,
txns.customer_id) = mcr.object_id
and DECODE(txns.publisher_order_type,
UNALLOCATED_ONHAND, txns.publisher_site_id,
txns.customer_site_id) = map2.company_key
AND txns.publisher_order_type IN
( ALLOCATED_ONHAND
, UNALLOCATED_ONHAND
, REQUISITION
, ASN
, SALES_ORDER
, REPLENISHMENT
)
)
and mvt.plan_id = msi.plan_id
and mvt.inventory_item_id = msi.inventory_item_id
and mvt.organization_id = msi.organization_id
and mvt.sr_instance_id = msi.sr_instance_id
and mvt.vmi_type = 2 -- customer facing vmi
ORDER BY 1, 2, 3, 4, 5;
select sysdate into l_curr_date from dual;
select maps.company_key
into l_source_site_id
from msc_trading_partner_maps maps,
msc_trading_partners tp
where tp.partner_type = 3
and tp.sr_instance_id = t_sr_instance_id(j)
and tp.sr_tp_id = t_source_org_id(j)
and tp.partner_id = maps.tp_key
and maps.map_type = 2;
select mrp_atp_schedule_temp_s.nextval
into l_session_id
from dual;
UPDATE msc_sup_dem_entries sd
SET release_status = REJECTED
WHERE sd.transaction_id = l_rep_transaction_id
;
SELECT msc_sup_dem_entries_s.nextval
INTO l_rep_transaction_id FROM DUAL;
SELECT mp.user_name
INTO l_supplier_contact
FROM msc_planners mp
, msc_system_items msi
WHERE msi.plan_id = -1 -- p_plan_id
AND msi.organization_id = p_customer_model_org_id
AND msi.inventory_item_id = p_inventory_item_id
AND msi.sr_instance_id = p_sr_instance_id
AND mp.sr_instance_id = msi.sr_instance_id
AND mp.organization_id = msi.organization_id
AND mp.planner_code = msi.planner_code
;
SELECT
sd.inventory_item_id
, mtp.sr_instance_id
, mtp.modeled_customer_id
, mtp.modeled_customer_site_id
, msi.uom_code
, msi.sr_inventory_item_id
, msi.organization_id
, msi.source_org_id
, sd.receipt_date
, msi.consigned_flag
, msi.item_name
, msi.description
, sd.customer_name
, sd.customer_site_name
, msi.uom_code
, nvl(msi.vmi_minimum_units, -1)
, nvl(msi.vmi_maximum_units, -1)
, nvl(msi.vmi_minimum_days, -1)
, nvl(msi.vmi_maximum_days, -1)
, nvl(mvt.average_daily_demand, 0)
, mtp.partner_name
FROM
msc_system_items msi,
msc_sup_dem_entries sd,
msc_trading_partners mtp,
msc_trading_partner_maps map1,
msc_trading_partner_maps map2,
msc_company_relationships mcr
, msc_vmi_temp mvt
WHERE
msi.inventory_planning_code = VMI_PLANNING_METHOD
AND msi.sr_instance_id = mtp.sr_instance_id
AND msi.organization_id = mtp.sr_tp_id
AND msi.inventory_item_id = sd.inventory_item_id
AND msi.plan_id = sd.plan_id
AND mtp.partner_type = 3
AND map2.map_type = 3
AND map2.tp_key = mtp.modeled_customer_site_id
AND map1.map_type = 1
AND map1.tp_key = mtp.modeled_customer_id
AND map1.company_key = mcr.relationship_id
AND mcr.subject_id = OEM_COMPANY_ID
AND mcr.relationship_type = CUSTOMER_OF
AND sd.customer_site_id = map2.company_key
AND sd.customer_id = mcr.object_id
AND sd.transaction_id = p_rep_transaction_id
AND sd.sr_instance_id = msi.sr_instance_id
and mvt.plan_id = msi.plan_id
and mvt.inventory_item_id = msi.inventory_item_id
and mvt.organization_id = msi.organization_id
and mvt.sr_instance_id = msi.sr_instance_id
and mvt.vmi_type = 2 -- customer facing vmi
;
SELECT
sd.inventory_item_id
, mtp.sr_instance_id
, mtp.modeled_customer_id
, mtp.modeled_customer_site_id
, msi.uom_code
, msi.sr_inventory_item_id
, msi.organization_id
, msi.source_org_id
, sd.key_date
, msi.consigned_flag
, sd.primary_quantity
, msi.item_name
, msi.description
-- , sd.customer_name
-- , sd.customer_site_name
, sd.publisher_name
, sd.publisher_site_name
, msi.uom_code
, nvl(msi.vmi_minimum_units, -1)
, nvl(msi.vmi_maximum_units, -1)
, nvl(msi.vmi_minimum_days, -1)
, nvl(msi.vmi_maximum_days, -1)
, nvl(mvt.average_daily_demand, 0)
, sd.ORDER_NUMBER --Consigned CVMI Enh
, sd.RELEASE_NUMBER
, sd.LINE_NUMBER
, sd.END_ORDER_NUMBER
, sd.END_ORDER_REL_NUMBER
, sd.END_ORDER_LINE_NUMBER
, mtp.partner_name
, sd.publisher_order_type_desc
FROM
msc_system_items msi,
msc_sup_dem_entries sd,
msc_trading_partners mtp,
msc_trading_partner_maps map1,
msc_trading_partner_maps map2,
msc_company_relationships mcr
, msc_vmi_temp mvt
WHERE
msi.inventory_planning_code = VMI_PLANNING_METHOD
AND msi.sr_instance_id = mtp.sr_instance_id
AND msi.organization_id = mtp.sr_tp_id
AND msi.inventory_item_id = sd.inventory_item_id
AND msi.plan_id = sd.plan_id
AND mtp.partner_type = 3
AND map2.map_type = 3
AND map2.tp_key = mtp.modeled_customer_site_id
AND map1.map_type = 1
AND map1.tp_key = mtp.modeled_customer_id
AND map1.company_key = mcr.relationship_id
AND mcr.subject_id = OEM_COMPANY_ID
AND mcr.relationship_type = CUSTOMER_OF
AND sd.publisher_site_id = map2.company_key
AND sd.publisher_id = mcr.object_id
AND sd.ref_header_id = p_header_id
-- AND sd.sr_instance_id = msi.sr_instance_id
AND sd.publisher_order_type = CONSUMPTION_ADVICE
AND sd.primary_quantity > 0
and mvt.plan_id = msi.plan_id
and mvt.inventory_item_id = msi.inventory_item_id
and mvt.organization_id = msi.organization_id
and mvt.sr_instance_id = msi.sr_instance_id
and mvt.vmi_type = 2 -- customer facing vmi
UNION
/* added so that Consumption advice load triggers Create/Update request for
drp_planned consigned item also*/
SELECT
sd.inventory_item_id
, mtp.sr_instance_id
, mtp.modeled_customer_id
, mtp.modeled_customer_site_id
, msi.uom_code
, msi.sr_inventory_item_id
, msi.organization_id
, msi.source_org_id
, sd.key_date
, msi.consigned_flag
, sd.primary_quantity
, msi.item_name
, msi.description
, sd.customer_name
, sd.customer_site_name
, msi.uom_code
, -1
, -1
, -1
, -1
, 0
, sd.ORDER_NUMBER --Consigned CVMI Enh
, sd.RELEASE_NUMBER
, sd.LINE_NUMBER
, sd.END_ORDER_NUMBER
, sd.END_ORDER_REL_NUMBER
, sd.END_ORDER_LINE_NUMBER
, mtp.partner_name
, sd.publisher_order_type_desc
FROM
msc_system_items msi,
msc_sup_dem_entries sd,
msc_trading_partners mtp,
msc_trading_partner_maps map1,
msc_trading_partner_maps map2,
msc_company_relationships mcr
WHERE
msi.inventory_planning_code = VMI_PLANNING_METHOD
AND msi.sr_instance_id = mtp.sr_instance_id
AND msi.organization_id = mtp.sr_tp_id
AND msi.inventory_item_id = sd.inventory_item_id
AND msi.plan_id = sd.plan_id
AND mtp.partner_type = 3
AND map2.map_type = 3
AND map2.tp_key = mtp.modeled_customer_site_id
AND map1.map_type = 1
AND map1.tp_key = mtp.modeled_customer_id
AND map1.company_key = mcr.relationship_id
AND mcr.subject_id = OEM_COMPANY_ID
AND mcr.relationship_type = CUSTOMER_OF
AND sd.publisher_site_id = map2.company_key
AND sd.publisher_id = mcr.object_id
AND sd.ref_header_id = p_header_id
AND sd.publisher_order_type = CONSUMPTION_ADVICE
AND sd.primary_quantity > 0
AND msi.drp_planned = 1 -- drp planned item
AND msi.consigned_flag = 1 -- consigned item
AND NOT EXISTS(SELECT mvt.inventory_item_id
FROM msc_vmi_temp mvt
WHERE mvt.plan_id = msi.plan_id
and mvt.inventory_item_id = msi.inventory_item_id
and mvt.organization_id = msi.organization_id
and mvt.sr_instance_id = msi.sr_instance_id
and mvt.vmi_type = 2 )
;
SELECT mpc.name
FROM msc_partner_contacts mpc
WHERE mpc.partner_type = 2 ---Customer
AND mpc.sr_instance_id = p_sr_instance_id
AND mpc.partner_site_id = p_customer_site_id
AND mpc.partner_id = p_customer_id
ORDER BY mpc.name
;
SELECT mp.user_name
FROM msc_planners mp
WHERE mp.planner_code = p_planner_code
AND mp.organization_id = p_modeled_customer_org_id
AND mp.sr_instance_id = p_sr_instance_id
ORDER BY mp.user_name
;
SELECT
msi.plan_id
, msi.inventory_item_id
, msi.organization_id
, msi.sr_instance_id
, mtp.modeled_customer_id
, mtp.modeled_customer_site_id
, msi.forecast_horizon
, msi.vmi_forecast_type
, mvt.average_daily_demand
FROM msc_system_items msi
, msc_trading_partners mtp
, msc_vmi_temp mvt
WHERE msi.inventory_planning_code = 7 -- (?)
AND msi.organization_id = mtp.sr_tp_id
AND msi.sr_instance_id = mtp.sr_instance_id
AND mtp.partner_type = 3 -- org
AND mtp.modeled_customer_id IS NOT NULL
AND mtp.modeled_customer_site_id IS NOT NULL
AND msi.plan_id = -1
and mvt.plan_id = msi.plan_id
and mvt.inventory_item_id = msi.inventory_item_id
and mvt.organization_id = msi.organization_id
and mvt.sr_instance_id = msi.sr_instance_id
and mvt.vmi_type = 2 -- customer facing vmi
;
UPDATE msc_system_items
SET vmi_refresh_flag = 0
WHERE plan_id = forecast_item.plan_id
AND inventory_item_id = forecast_item.inventory_item_id
AND organization_id = forecast_item.organization_id
AND sr_instance_id = forecast_item.sr_instance_id
;
print_debug_info( ' average daily demand and vmi refresh flag reset to 0, number of rows updated = '
|| SQL%ROWCOUNT
);