[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
mis.plan_id
, mis.inventory_item_id
, mis.organization_id
, mis.sr_instance_id
, mis.supplier_id
, mis.supplier_site_id
FROM msc_item_suppliers mis
WHERE mis.plan_id = -1
AND mis.vmi_flag = 1
;
UPDATE msc_item_suppliers
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
AND supplier_id = forecast_item.supplier_id
AND supplier_site_id = forecast_item.supplier_site_id
;
SELECT distinct its.inventory_item_id,
its.organization_id,
its.plan_id,
its.sr_instance_id,
its.supplier_id,
its.supplier_site_id
FROM msc_sup_dem_entries sd1,
msc_item_suppliers its,
msc_trading_partners tp,
msc_trading_partner_maps map,
msc_trading_partner_maps map2,
msc_trading_partner_maps map3,
msc_company_relationships r
WHERE sd1.plan_id = -1
-- Alloc onhand, po, asn, recpt, req
AND sd1.publisher_order_type in (9, 13, 15, 16, 20)
AND nvl(sd1.last_refresh_number,-1) > p_last_max_refresh_number
AND sd1.inventory_item_id = its.inventory_item_id
AND its.vmi_flag = 1 -- only look at vmi enabled items
AND its.plan_id = sd1.plan_id
-- get org_id
AND map.map_type = 2
AND map.company_key = sd1.customer_site_id
AND map.tp_key = tp.partner_id
AND tp.partner_type = 3
AND its.organization_id = tp.sr_tp_id
AND its.sr_instance_id = tp.sr_instance_id
-- get supplier_id
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 its.supplier_id = map2.tp_key
-- get supplier_site_id
AND its.supplier_site_id = map3.tp_key
AND map3.map_type = 3
AND map3.company_key = sd1.supplier_site_id
UNION
SELECT distinct its.inventory_item_id,
its.organization_id,
its.plan_id,
its.sr_instance_id,
its.supplier_id,
its.supplier_site_id
FROM msc_sup_dem_entries sd1,
msc_item_suppliers its,
msc_trading_partners tp,
msc_trading_partner_maps map,
msc_trading_partner_maps map2,
msc_trading_partner_maps map3,
msc_company_relationships r
WHERE sd1.plan_id = -1
-- Alloc onhand, po, asn, recpt, req
AND sd1.publisher_order_type in (9, 13, 15, 16, 20)
-- AND nvl(sd1.last_refresh_number,-1) > p_last_max_refresh_number
AND sd1.inventory_item_id = its.inventory_item_id
AND its.vmi_flag = 1 -- only look at vmi enabled items
AND its.plan_id = sd1.plan_id
-- get org_id
AND map.map_type = 2
AND map.company_key = sd1.customer_site_id
AND map.tp_key = tp.partner_id
AND tp.partner_type = 3
AND its.organization_id = tp.sr_tp_id
AND its.sr_instance_id = tp.sr_instance_id
-- get supplier_id
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 its.supplier_id = map2.tp_key
-- get supplier_site_id
AND its.supplier_site_id = map3.tp_key
AND map3.map_type = 3
AND map3.company_key = sd1.supplier_site_id
-- there is no new data, but average daily demand changed
AND its.vmi_refresh_flag = 1
AND (its.replenishment_method = 2 OR its.replenishment_method = 4)
;
SELECT distinct its.inventory_item_id,
its.organization_id,
its.plan_id,
its.sr_instance_id,
its.supplier_id,
its.supplier_site_id
FROM msc_item_suppliers its,
MSC_X_ITEM_SUPPLIERS_GTT iut,
MSC_X_ITEM_ORGS_GTT iot,
MSC_X_ITEM_SITES_GTT ist
WHERE its.plan_id = -1
AND its.vmi_flag = 1 -- only look at vmi enabled items
AND its.enable_vmi_auto_replenish_flag = 'Y'
AND its.supplier_id = iut.tp_key
AND its.organization_id = iot.sr_tp_id
AND its.supplier_site_id = ist.tp_key
AND NOT EXISTS ( SELECT 1 FROM MSC_SUP_DEM_ENTRIES SD
WHERE SD.PLAN_ID = -1
AND SD.INVENTORY_ITEM_ID = ITS.INVENTORY_ITEM_ID
AND SD.PUBLISHER_ORDER_TYPE IN (9, 13, 15, 16, 20)
AND SD.CUSTOMER_SITE_ID = iot.COMPANY_KEY
AND SD.SUPPLIER_ID = iut.OBJECT_ID
AND SD.SUPPLIER_SITE_ID = ist.COMPANY_KEY) ;
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( -1,
-1,
-1,
l_curr_max_refresh_number,
sysdate
, p_supplier_time_fence
);
update msc_plan_org_status
set status = l_curr_max_refresh_number,
status_date = sysdate
, number1 = p_supplier_time_fence
where plan_id = -1
and organization_id = -1
and sr_instance_id = -1;
SELECT mpc.name
FROM MSC_PARTNER_CONTACTS mpc
WHERE mpc.partner_id = p_partner_id
AND mpc.partner_site_id = p_partner_site_id
AND mpc.sr_instance_id = p_sr_instance_id
AND mpc.partner_type = 1 -- supplier
;
SELECT DISTINCT mp.user_name
FROM msc_planners mp
, msc_system_items msi
WHERE msi.plan_id = p_plan_id
AND msi.organization_id = p_site_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 msi.item_name
FROM msc_system_items msi
WHERE msi.plan_id = p_plan_id
AND msi.organization_id = p_organization_id
AND msi.inventory_item_id = p_inventory_item_id
AND msi.sr_instance_id = p_sr_instance_id
;
SELECT mc.company_name
FROM msc_companies mc
WHERE mc.company_id = p_company_id
;
SELECT mcs.company_site_name
FROM msc_company_sites mcs
WHERE mcs.company_id = p_company_id
AND mcs.company_site_id = p_company_site_id
;
SELECT count(1)
FROM wf_local_roles
WHERE name = p_role_name
;
SELECT vmi_replenishment_approval
FROM msc_item_suppliers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND plan_id = p_plan_id
AND sr_instance_id = p_sr_instance_id
AND supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
ORDER BY using_organization_id DESC
;
SELECT enable_vmi_auto_replenish_flag
FROM MSC_ITEM_SUPPLIERS
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND plan_id = p_plan_id
AND sr_instance_id = p_sr_instance_id
AND supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id
ORDER BY using_organization_id DESC;
/* -------------- Delete old replenishment records ------------------- */
open c_enable_auto_repl_flag(p_organization_id,
p_inventory_item_id,
p_plan_id,
p_sr_instance_id,
p_supplier_id,
p_supplier_site_id);
SELECT sd.transaction_id, rowid
INTO l_del_transaction_id, l_del_rowid
FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = p_inventory_item_id
AND sd.publisher_order_type = REPLENISHMENT
AND sd.plan_id = p_plan_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.supplier_site_id = l_sce_supplier_site_id;
print_debug_info(' delete obsolete records with workflow key = ' || l_del_wf_key);
/* delete repl record */
print_debug_info(' delete obsolete replenishment record with transaction id = '
|| l_del_transaction_id);
DELETE FROM msc_sup_dem_entries
WHERE ROWID = l_del_rowid;
SELECT msc_sup_dem_entries_s.nextval
INTO l_rep_transaction_id FROM DUAL;
SELECT msc_x_seller_role_s.NEXTVAL INTO l_seller_role_seq FROM dual;
SELECT msc_x_vmi_role_s.NEXTVAL INTO l_vmi_role_seq FROM dual;
END IF; -- end else delete old repl record
SELECT SUM( DECODE( sd.publisher_id
, sd.supplier_id, sd.tp_quantity
, sd.primary_quantity
)
)
FROM msc_sup_dem_entries sd
WHERE sd.inventory_item_id = l_inventory_item_id
AND sd.supplier_site_id = l_sce_supplier_site_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.plan_id = l_plan_id
AND sd.publisher_order_type = ASN
AND sd.customer_id = OEM_COMPANY_ID
AND sd.customer_site_id = l_sce_organization_id
AND sd.RECEIPT_DATE <= l_time_fence_end_date
AND sd.vmi_flag = 1
;
SELECT sd.primary_quantity
FROM msc_sup_dem_entries sd
WHERE sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_site_id = l_sce_organization_id
AND sd.plan_id = l_plan_id
AND sd.publisher_order_type = ALLOCATED_ONHAND
AND sd.supplier_site_id = l_sce_supplier_site_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.vmi_flag = 1
ORDER BY sd.key_date desc
;
SELECT sd.transaction_id, sd.primary_quantity
FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = REPLENISHMENT
AND sd.plan_id = l_plan_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.supplier_site_id = l_sce_supplier_site_id
;
SELECT SUM(sd.primary_quantity)
FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = SHIPMENT_RECEIPT
AND sd.plan_id = l_plan_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.supplier_site_id = l_sce_supplier_site_id
AND sd.RECEIPT_DATE <= SYSDATE
AND sd.vmi_flag = 1
;
SELECT SUM(sd.primary_quantity)
FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = REQUISITION
AND sd.plan_id = l_plan_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.supplier_site_id = l_sce_supplier_site_id
-- AND sd.receipt_date BETWEEN SYSDATE AND l_time_fence_end_date
AND TRUNC(sd.receipt_date) <= TRUNC(l_time_fence_end_date)
AND sd.vmi_flag = 1
;
SELECT SUM(sd.primary_quantity)
FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = PURCHASE_ORDER
AND sd.plan_id = l_plan_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.supplier_site_id = l_sce_supplier_site_id
-- AND sd.RECEIPT_DATE BETWEEN SYSDATE AND l_time_fence_end_date
AND TRUNC(sd.RECEIPT_DATE) <= TRUNC(l_time_fence_end_date)
AND sd.vmi_flag = 1
;
SELECT ml.meaning
FROM mfg_lookups ml
WHERE lookup_type = 'MSC_X_ORDER_TYPE'
AND ml.lookup_code = p_publisher_order_type
;
SELECT mi.item_name, mi.description
FROM msc_items mi
WHERE mi.inventory_item_id = p_inventory_item_id
;
SELECT mis.supplier_item_name
FROM msc_item_suppliers mis
WHERE mis.inventory_item_id = p_inventory_item_id
AND mis.organization_id = p_organization_id
AND mis.supplier_id = p_supplier_id
AND mis.supplier_site_id = p_supplier_site_id
AND mis.sr_instance_id = p_sr_instance_id
AND mis.plan_id = p_plan_id
ORDER BY using_organization_id DESC
;
SELECT mis.fixed_lot_multiplier
, mis.minimum_order_quantity
, mis.maximum_order_quantity
, mis.min_minmax_quantity
, mis.max_minmax_quantity
, mis.min_minmax_days
, mis.max_minmax_days
, mis.fixed_order_quantity
, mvt.average_daily_demand
, mis.vmi_refresh_flag
, mis.processing_lead_time
, mis.replenishment_method
FROM msc_item_suppliers mis
, msc_vmi_temp mvt
WHERE mis.inventory_item_id = p_inventory_item_id
AND mis.organization_id = p_organization_id
AND mis.plan_id = p_plan_id
AND mis.sr_instance_id = p_sr_instance_id
AND mis.supplier_site_id = p_supplier_site_id
AND mis.supplier_id = p_supplier_id
and mvt.plan_id = mis.plan_id
and mvt.inventory_item_id = mis.inventory_item_id
and mvt.organization_id = mis.organization_id
and mvt.sr_instance_id = mis.sr_instance_id
and mvt.supplier_site_id = mis.supplier_site_id
and mvt.supplier_id = mis.supplier_id
and NVL(mvt.using_organization_id, -1) = NVL(mis.using_organization_id, -1)
and mvt.vmi_type = 1 -- supplier facing vmi
ORDER BY mis.using_organization_id DESC
;
SELECT si.uom_code
, si.rounding_control_type
FROM msc_system_items si
WHERE si.inventory_item_id = l_inventory_item_id
AND si.organization_id = l_organization_id
AND si.plan_id = l_plan_id
AND si.sr_instance_id = l_sr_instance_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
, inventory_item_id
, comments
, publisher_order_type
, supplier_id
, supplier_name
, supplier_site_id
, supplier_site_name
, customer_id
, customer_name
, customer_site_id
, customer_site_name
, line_code
, bucket_type
, order_number
, end_order_number
, new_dock_date
, posting_party_id
, new_ship_date
, new_order_placement_date
, release_number
, line_number
, end_order_rel_number
, end_order_line_number
, publisher_address_id
, carrier_code
, vehicle_number
, container_type
, container_qty
, tracking_number
, end_order_type
, end_order_publisher_id
, ship_to_address
, ship_from_party_id
, ship_to_party_id
, ship_to_party_address_id
, ship_from_party_address_id
, owning_site_id
, owning_site_name
, end_order_publisher_site_id
, end_order_publisher_site_name
, end_order_publisher_name
, item_name
, owner_item_name
, customer_item_name
, supplier_item_name
, publisher_order_type_desc
, tp_order_type_desc
, designator
, category_name
, context
, unit_number
, ship_method
, project_number
, task_number
, planning_group
, ship_from_address
, publisher_address
, customer_address
, supplier_address
, 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
, posting_party_name
, new_schedule_end_date
, attachment_url
, promise_ship_date
, inventory_status
, release_status
, last_refresh_number
, serial_number
, bill_of_lading_number
, bucket_type_desc
, ship_from_party_site_id
, ship_from_party_name
, ship_from_party_site_name
, ship_to_party_site_id
, ship_to_party_name
, ship_to_party_site_name
, receipt_date
, quantity_in_process
, implemented_quantity
, vmi_flag
, item_description
, customer_item_description
, supplier_item_description
, owner_item_description
) VALUES
(
l_rep_transaction_id
, l_plan_id -- plan_id
, l_sr_instance_id -- sr_instance_id
, OEM_COMPANY_ID -- publisher_id
, l_sce_organization_id -- publisher_site_id
, l_customer_name -- publisher_name
, l_customer_site_name -- publisher_site_name
, SYSDATE -- new_schedule_date
, l_inventory_item_id -- inventory_item_id
, null -- comments
, REPLENISHMENT -- publisher_order_type
, l_sce_supplier_id -- supplier_id
, l_supplier_name -- supplier_name
, l_sce_supplier_site_id -- supplier_site_id
, l_supplier_site_name -- supplier_site_name
, OEM_COMPANY_ID -- customer_id
, l_customer_name -- customer_name
, l_sce_organization_id -- customer_site_id
, l_customer_site_name -- customer_site_name
, null -- line_code
, null -- bucket_type
, null -- order_number
, null -- end_order_number
, null -- new_dock_date
, null -- posting_party_id
, null -- new_ship_date
, SYSDATE -- new_order_placement_date
, null -- release_number
, null -- line_number
, null -- end_order_rel_number
, null -- end_order_line_number
, null -- publisher_address_id
, null -- carrier_code
, null -- vehicle_number
, null -- container_type
, null -- container_qty
, null -- tracking_number
, null -- end_order_type
, null -- end_order_publisher_id
, null -- ship_to_address
, null -- ship_from_party_id
, null -- ship_to_party_id
, null -- ship_to_party_address_id
, null -- ship_from_party_address_id
, l_supplier_site_id -- owning_site_id
, null -- owning_site_name
, null -- end_order_publisher_site_id
, null -- end_order_publisher_site_name
, null -- end_order_publisher_name
, l_item_name -- item_name
, l_customer_item_name -- owner_item_name
, l_customer_item_name -- customer_item_name
, l_supplier_item_name -- supplier_item_name
, l_publisher_order_type_desc -- publisher_order_type_desc
, null -- tp_order_type_desc
, null -- designator
, null -- category_name
, null -- context
, null -- unit_number
, null -- ship_method
, null -- project_number
, null -- task_number
, null -- planning_group
, null -- ship_from_address
, null -- publisher_address
, null -- customer_address
, null -- supplier_address
, 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_customer_uom_code -- l_customer_uom_code
, l_order_quantity -- l_customer_order_quantity
, l_customer_uom_code -- primary_uom
, l_order_quantity -- l_customer_order_quantity
, l_customer_uom_code -- tp_uom_code
, l_order_quantity -- tp_quantity
, l_item_description -- pub_item_description
, l_item_description -- tp_item_description
, null -- posting_party_name
, null -- new_schedule_end_date
, null -- attachment_url
, null -- promise_ship_date
, null -- inventory_status
, UNRELEASED -- release_status
, null -- l_last_refresh_number
, null -- serial_number
, null -- bill_of_lading_number
, null -- bucket_type_desc
, null -- ship_from_party_site_id
, null -- ship_from_party_name
, null -- ship_from_party_site_name
, null -- ship_to_party_site_id
, null -- ship_to_party_name
, null -- ship_to_party_site_name
, l_time_fence_end_date -- receipt_date
, 0 -- quantity_in_process
, 0 -- implemented_quantity
, 1 -- vmi_flag
, l_item_description -- item_description
, l_item_description -- customer_item_description
, l_item_description -- supplier_item_description
, l_item_description -- owner_item_description
);
-- update the existing record
UPDATE msc_sup_dem_entries sd
SET
transaction_id = l_rep_transaction_id
, uom_code = l_customer_uom_code
, quantity = l_order_quantity -- l_customer_order_quantity
, primary_uom = l_customer_uom_code
, primary_quantity = l_order_quantity -- l_customer_order_quantity
, tp_uom_code = l_customer_uom_code -- l_supplier_uom_code
, tp_quantity = l_order_quantity -- l_supplier_order_quantity
, sd.new_schedule_date = SYSDATE
, sd.release_status = UNRELEASED
, sd.new_dock_date = l_time_fence_end_date
, sd.publisher_name = l_customer_name
, sd.publisher_site_name = l_customer_site_name
, sd.supplier_name = l_supplier_name
, sd.supplier_site_name = l_supplier_site_name
, sd.receipt_date = l_time_fence_end_date
, sd.quantity_in_process = 0
, sd.implemented_quantity = 0
, sd.last_updated_by = FND_GLOBAL.USER_ID
, sd.last_update_date = SYSDATE
, sd.last_update_login = FND_GLOBAL.LOGIN_ID
, sd.customer_id = OEM_COMPANY_ID
, sd.customer_name = l_customer_name
, sd.customer_site_id = l_sce_organization_id
, sd.customer_site_name = l_customer_site_name
, sd.new_order_placement_date = SYSDATE
, sd.publisher_order_type_desc = l_publisher_order_type_desc
, sd.vmi_flag = 1
, sd.pub_item_description = l_item_description
, sd.tp_item_description = l_item_description
, sd.item_description = l_item_description
, sd.customer_item_description = l_item_description
, sd.supplier_item_description = l_item_description
, sd.owner_item_description = l_item_description
WHERE sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = REPLENISHMENT
AND sd.plan_id = l_plan_id
AND sd.supplier_site_id = l_sce_supplier_site_id
AND sd.supplier_id = l_sce_supplier_id
;
print_debug_info(' updated old replenishment record');
DELETE FROM msc_sup_dem_entries sd
WHERE sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = REPLENISHMENT
AND sd.plan_id = l_plan_id
AND sd.supplier_site_id = l_sce_supplier_site_id
AND sd.supplier_id = l_sce_supplier_id
;
print_debug_info(' no supply shortage, old replenishment record deleted');
l_updated_return_code VARCHAR2(100);
l_updated_err_buf VARCHAR2(100);
SELECT
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_NAME,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.APPLICATION_NAME
INTO l_user_id,
l_user_name,
l_resp_name,
l_application_name
FROM dual;
select mp.employee_id
into l_employee_id
from msc_system_items si,
msc_planners mp
where si.organization_id = l_organization_id
and si.inventory_item_id = l_inventory_item_id
and si.plan_id = l_plan_id
and si.sr_instance_id = l_sr_instance_id
and mp.sr_instance_id = si.sr_instance_id
and mp.organization_id = si.organization_id
and mp.planner_code = si.planner_code;
-- insert a row into msc_po_requisitions_interface with status = 'approved'
INSERT INTO msc_po_requisitions_interface
(
-- line_type_id -- Amount or Quantity based (Bug #4589288)
last_updated_by
, last_update_date
, last_update_login
, creation_date
, created_by
, item_id
, quantity
, need_by_date
, interface_source_code
, deliver_to_location_id
, deliver_to_requestor_id
, destination_type_code
, preparer_id
, source_type_code
, authorization_status
, uom_code
, batch_id
, charge_account_id
, group_code
, item_revision
, destination_organization_id
, autosource_flag
, org_id
, source_organization_id
, suggested_vendor_id
, suggested_vendor_site_id
, suggested_vendor_site
, project_id
, task_id
, end_item_unit_number
, project_accounting_context
, sr_instance_id
, vmi_flag
)
SELECT
-- 1, -- Quantity based
si.last_updated_by,
SYSDATE, -- last_update_date
si.last_update_login,
SYSDATE, -- creation_date
l_user_id, -- created_by
si.sr_inventory_item_id, -- item_id
l_order_quantity, -- quantity
sd.receipt_date, -- need_by_date
'MSC', -- interface_source_code
tps2.sr_tp_site_id, -- deliver_to_location_id
l_employee_id, --mp.employee_id, -- deliver_to_requestor_id
'INVENTORY', -- destination_type_code
l_employee_id, --mp.employee_id, -- preparer_id
'VENDOR', -- source_type_code
'APPROVED', -- authorization_status
l_customer_uom_code, -- l_uom_code --si.uom_code, --
NULL, -- batch_id
decode(si.inventory_asset_flag,
'Y', tp.material_account,
nvl(si.expense_account, tp.expense_account)),
si.inventory_item_id, -- group_code
si.revision, -- item_revision
si.organization_id, -- destination_organization_id
'P', -- autosource_flag
tp.operating_unit, -- org_id
NULL, -- source_organization_id
tplid.sr_tp_id, -- suggested_vendor_id
tps.sr_tp_site_id, -- suggested_vendor_site_id
tps.tp_site_code, -- suggested_vendor_site
NULL, --sd.project_number, -- project_id
NULL, --sd.task_number, -- task_id
NULL, --sd.unit_number, -- end_item_unit_number
NULL, --DECODE(sd.project_number, NULL, 'N', 'Y'), -- project_accounting_context
si.sr_instance_id, -- sr_instance_id
1 -- vmi_flag
FROM msc_sup_dem_entries sd,
msc_system_items si,
msc_trading_partners tp,
msc_tp_id_lid tplid,
msc_trading_partner_sites tps,
msc_trading_partner_sites tps2
, MSC_TP_SITE_ID_LID mtsil
WHERE sd.transaction_id = l_rep_transaction_id -- l_req_transaction_id
AND sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = REPLENISHMENT
AND sd.plan_id = l_plan_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.supplier_site_id = l_sce_supplier_site_id
AND si.organization_id = l_organization_id
AND si.inventory_item_id = sd.inventory_item_id
AND si.plan_id = sd.plan_id
AND si.sr_instance_id = l_sr_instance_id
AND tplid.tp_id = l_supplier_id
AND tplid.partner_type = 1
AND tplid.sr_instance_id = l_sr_instance_id
AND tps.partner_site_id = l_supplier_site_id
AND tps.partner_type = 1
AND tps.partner_id = tplid.tp_id
AND tps2.sr_tp_id = si.organization_id
AND tps2.sr_instance_id = l_sr_instance_id
AND tps2.partner_type = 3
AND tp.sr_tp_id = si.organization_id
AND tp.sr_instance_id = l_sr_instance_id
AND tp.partner_type = 3
and mtsil.sr_instance_id = l_sr_instance_id
and mtsil.tp_site_id = l_supplier_site_id
and mtsil.partner_type = 1 -- supplier ;
print_debug_info(' ' || l_loaded_reqs || ' rows inserted into msc_po_requisitions_interface');
print_debug_info(' no rows inserted into msc_po_requisitions_interface');
SELECT DECODE(ai.m2a_dblink,NULL,' ', '@' || ai.m2a_dblink)
, instance_id
, instance_code
, a2m_dblink
INTO l_dblink
, l_instance_id
, l_instance_code
, l_a2m_dblink
FROM msc_apps_instances ai
WHERE ai.instance_id = l_sr_instance_id;
UPDATE msc_sup_dem_entries sd
SET sd.release_status = RELEASED
,sd.quantity_in_process = l_order_quantity
WHERE sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = REPLENISHMENT
AND sd.plan_id = l_plan_id
AND sd.supplier_site_id = l_sce_supplier_site_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.transaction_id = l_rep_transaction_id
AND sd.release_status = UNRELEASED;
print_debug_info(' updated status of replenishment record to RELEASED');
print_debug_info(' Error when call Requistion Import or update replenishment record = '
|| sqlerrm
);
DELETE MSC_PO_REQUISITIONS_INTERFACE
WHERE sr_instance_id= l_sr_instance_id;
print_debug_info(' deleted related data in MSC_PO_REQUISITIONS_INTERFACE');
SELECT vmi_flag, enable_vmi_auto_replenish_flag
FROM msc_item_suppliers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND plan_id = p_plan_id
AND sr_instance_id = p_sr_instance_id
AND supplier_id = NVL(p_supplier_id, supplier_id)
AND supplier_site_id = NVL(p_supplier_site_id, supplier_site_id)
ORDER BY using_organization_id DESC
;
SELECT vmi_flag, enable_vmi_auto_replenish_flag
FROM msc_item_suppliers
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND plan_id = p_plan_id
AND sr_instance_id = p_sr_instance_id
AND supplier_id = NVL(p_supplier_id, supplier_id)
AND supplier_site_id = NVL(p_supplier_site_id, supplier_site_id)
ORDER BY using_organization_id DESC
;
SELECT vmi_replenishment_approval
FROM msc_item_suppliers
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND plan_id = l_plan_id
AND sr_instance_id = l_sr_instance_id
AND supplier_id = l_supplier_id
AND supplier_site_id = l_supplier_site_id
ORDER BY using_organization_id DESC
;
SELECT vmi_replenishment_approval
FROM msc_item_suppliers
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND plan_id = l_plan_id
AND sr_instance_id = l_sr_instance_id
AND supplier_id = l_supplier_id
AND supplier_site_id = l_supplier_site_id
ORDER BY using_organization_id DESC
;
UPDATE msc_sup_dem_entries sd
SET release_status = REJECTED
WHERE sd.publisher_site_id = l_sce_organization_id
AND sd.inventory_item_id = l_inventory_item_id
AND sd.publisher_order_type = REPLENISHMENT
AND sd.plan_id = l_plan_id
AND sd.sr_instance_id = l_sr_instance_id
AND sd.supplier_site_id = l_sce_supplier_site_id
AND sd.supplier_id = l_sce_supplier_id
AND sd.transaction_id = l_rep_transaction_id
-- AND sd.release_status = UNRELEASED
;
SELECT cr.object_id
FROM msc_trading_partner_maps map
, msc_company_relationships cr
WHERE map.map_type = p_map_type
AND map.tp_key = p_tp_key
AND map.company_key = cr.relationship_id
AND cr.relationship_type = 2
;
SELECT map.company_key
FROM msc_trading_partner_maps map
, msc_trading_partners tp
WHERE map.map_type = p_map_type
AND tp.partner_id = map.tp_key
AND tp.sr_tp_id = p_tp_key
AND tp.sr_instance_id = p_sr_instance_id
;
SELECT map.company_key
FROM msc_trading_partner_maps map
WHERE map.map_type = p_map_type
AND map.tp_key = p_tp_key
;
SELECT map.tp_key
FROM msc_trading_partner_maps map
, msc_company_relationships cr
WHERE map.map_type = p_map_type
AND cr.object_id = p_company_key
AND map.company_key = cr.relationship_id
AND cr.relationship_type = 2
AND cr.subject_id = OEM_COMPANY_ID
;
SELECT tp.sr_tp_id
FROM msc_trading_partner_maps map
, msc_trading_partners tp
WHERE map.map_type = p_map_type
AND tp.partner_id = map.tp_key
AND map.company_key= p_company_key
;
SELECT map.tp_key
FROM msc_trading_partner_maps map
WHERE map.map_type = p_map_type
AND map.company_key = p_company_key
;
SELECT tp.sr_instance_id
FROM msc_trading_partner_maps map
, msc_trading_partners tp
WHERE map.map_type = ORGANIZATION_MAPPING
AND tp.partner_id = map.tp_key
AND map.company_key= p_customer_site_id
;
SELECT mcs.company_site_name
FROM msc_company_sites mcs
WHERE mcs.company_id = p_company_id
AND mcs.company_site_id = p_company_site_id
-- AND mcs.sr_instance_id = p_sr_instance_id
;
SELECT mcu.company_id
FROM msc_company_users mcu
WHERE mcu.user_id = FND_GLOBAL.USER_ID
;
select tp.sr_instance_id
into l_sr_instance_id
from msc_trading_partner_maps maps,
msc_trading_partners tp
where maps.tp_key = tp.partner_id
and maps.company_key = p_customer_site_id
and maps.map_type = 2
and tp.partner_type = 3;
SELECT
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_NAME,
FND_GLOBAL.RESP_NAME,
FND_GLOBAL.APPLICATION_NAME
INTO l_user_id,
l_user_name,
l_resp_name,
l_application_name
FROM dual;
select mp.employee_id
into l_employee_id
from msc_system_items si,
msc_planners mp
WHERE si.organization_id = l_aps_customer_site_id
AND si.inventory_item_id = p_item_id
AND si.plan_id = VMI_PLAN_ID
AND si.sr_instance_id = l_sr_instance_id
AND mp.sr_instance_id = si.sr_instance_id
AND mp.organization_id = si.organization_id
AND mp.planner_code = si.planner_code;
INSERT INTO msc_po_requisitions_interface(
-- line_type_id -- Amount or Quantity based
last_updated_by
, last_update_date
, last_update_login
, creation_date
, created_by
, item_id
, quantity
, need_by_date
, interface_source_code
, deliver_to_location_id
, deliver_to_requestor_id
, destination_type_code
, preparer_id
, source_type_code
, authorization_status
, uom_code
, batch_id
, charge_account_id
, group_code
, item_revision
, destination_organization_id
, autosource_flag
, org_id
, source_organization_id
, suggested_vendor_id
, suggested_vendor_site_id
, suggested_vendor_site
, project_id
, task_id
, end_item_unit_number
, project_accounting_context
, sr_instance_id
, vmi_flag
)
SELECT
-- 1, -- Quantity based
si.last_updated_by,
SYSDATE, -- last_update_date
si.last_update_login,
SYSDATE, -- creation_date
l_user_id, -- created_by
si.sr_inventory_item_id, -- item_id
p_quantity, -- quantity
l_need_by_date, -- need_by_date
'MSC', -- interface_source_code
tps2.sr_tp_site_id, -- deliver_to_location_id
l_employee_id, --mp.employee_id, -- deliver_to_requestor_id
'INVENTORY', -- destination_type_code
l_employee_id, --mp.employee_id, -- preparer_id
'VENDOR', -- source_type_code
'APPROVED', -- authorization_status
nvl(p_uom_code,si.uom_code), ---l_customer_uom_code, -- l_uom_code, --si.uom_code, --
to_number(NULL), -- batch_id
decode(si.inventory_asset_flag,
'Y', tp.material_account,
nvl(si.expense_account, tp.expense_account)),
si.inventory_item_id, -- group_code
si.revision, -- item_revision
si.organization_id, -- destination_organization_id
'P', -- autosource_flag
tp.operating_unit, -- org_id
to_number(NULL), -- source_organization_id
tplid.sr_tp_id, -- suggested_vendor_id
tps.sr_tp_site_id, -- suggested_vendor_site_id
tps.tp_site_code, -- suggested_vendor_site
to_number(NULL), -- project_id
to_number(NULL), -- task_id
to_char(NULL), -- end_item_unit_number
to_char(NULL), -- project_accounting_context
si.sr_instance_id, -- sr_instance_id
1 -- vmi_flag
FROM msc_system_items si,
msc_trading_partners tp,
msc_tp_id_lid tplid,
msc_trading_partner_sites tps,
msc_trading_partner_sites tps2
, MSC_TP_SITE_ID_LID mtsil
WHERE si.organization_id = l_aps_customer_site_id
AND si.inventory_item_id = p_item_id
AND si.plan_id = VMI_PLAN_ID
AND si.sr_instance_id = l_sr_instance_id
AND tp.sr_tp_id = si.organization_id
AND tp.sr_instance_id = l_sr_instance_id
AND tp.partner_type = 3
AND tplid.tp_id = l_aps_supplier_id
AND tplid.partner_type = 1
AND tplid.sr_instance_id = l_sr_instance_id
AND tps.partner_site_id = l_aps_supplier_site_id
AND tps.partner_id = l_aps_supplier_id
AND tps.partner_type = 1
AND tps2.sr_tp_id = si.organization_id
AND tps2.sr_instance_id = l_sr_instance_id
AND tps2.partner_type = 3
and mtsil.sr_instance_id = l_sr_instance_id
and mtsil.tp_site_id = l_aps_supplier_site_id
and mtsil.partner_type = 1 -- supplier
--AND NVL(mtsil.operating_unit, -1) = NVL(tp.operating_unit, -1) -- (bug # 4589288)
AND mtsil.sr_tp_site_id= tps.sr_tp_site_id
and rownum = 1
UNION ALL
SELECT
-- 1, -- Quantity based
si.last_updated_by,
SYSDATE, -- last_update_date
si.last_update_login,
SYSDATE, -- creation_date
l_user_id, -- created_by
si.sr_inventory_item_id, -- item_id
p_quantity, -- quantity
l_need_by_date, -- need_by_date
'MSC', -- interface_source_code
tps.sr_tp_site_id, -- deliver_to_location_id
l_employee_id, --mp.employee_id, -- deliver_to_requestor_id
'INVENTORY', -- destination_type_code
l_employee_id, --mp.employee_id, -- preparer_id
'INVENTORY', -- source_type_code
'APPROVED', -- authorization_status
nvl(p_uom_code,si.uom_code), ---l_customer_uom_code, -- l_uom_code, --si.uom_code, --
to_number(NULL), -- batch_id
decode( si.inventory_asset_flag,
'Y', tp.material_account,
nvl(si.expense_account, tp.expense_account)),
--si.expense_account, -- charge_account_id
si.inventory_item_id, -- group_code
si.revision, -- item_revision
si.organization_id, -- destination_organization_id
'P', -- autosource_flag
tp.operating_unit, -- tp.operating_unit, -- org_id
p_supplier_id, -- source_organization_id
to_number(NULL), -- suggested_vendor_id
to_number(NULL), -- suggested_vendor_site_id
to_char(NULL), -- suggested_vendor_site
to_number(NULL), -- project_id
to_number(NULL), -- task_id
to_char(NULL), -- end_item_unit_number
to_char(NULL), -- project_accounting_context
si.sr_instance_id, -- sr_instance_id
2 -- vmi_flag
FROM msc_system_items si,
msc_trading_partners tp,
msc_trading_partner_sites tps
WHERE si.organization_id = l_aps_customer_site_id
AND si.inventory_item_id = p_item_id
AND si.plan_id = VMI_PLAN_ID
AND si.sr_instance_id = l_sr_instance_id
AND tp.sr_tp_id = si.organization_id
AND tp.sr_instance_id = l_sr_instance_id
AND tp.partner_type = 3
AND tps.sr_instance_id = tp.sr_instance_id
AND tps.sr_tp_id = tp.sr_tp_id
AND tps.partner_type = tp.partner_type
and si.inventory_planning_code=7 --Bug 4700809, only the CVMI items contain 7 as the value of this flag.
AND rownum = 1;
print_debug_info(' inserted into msc_po_requisitions_interface, number of inserted rows = '
|| l_loaded_reqs);
print_debug_info(' no record inserted into msc_po_requisitions_interface');
SELECT DECODE(ai.m2a_dblink,NULL,' ', '@' || ai.m2a_dblink)
, instance_id
, instance_code
, a2m_dblink
INTO l_dblink
, l_instance_id
, l_instance_code
, l_a2m_dblink
FROM msc_apps_instances ai
WHERE ai.instance_id = l_sr_instance_id;
print_debug_info(' delete records in MSC_PO_REQUISITIONS_INTERFACE' || l_fnd_request_id);
DELETE MSC_PO_REQUISITIONS_INTERFACE --ut
WHERE sr_instance_id= l_sr_instance_id; --ut
Insert into MSC_X_ITEM_SUPPLIERS_GTT(
object_id,
tp_key
)
select distinct r.object_id, map1.tp_key
from msc_trading_partner_maps map1,
msc_company_relationships r,
msc_item_suppliers its
where map1.map_type = 1
AND map1.company_key = r.relationship_id
AND map1.tp_key = its.supplier_id
AND r.relationship_type = 2
AND r.subject_id = 1
AND its.plan_id = -1
AND its.vmi_flag = 1
AND its.enable_vmi_auto_replenish_flag = 'Y';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows inserted into msc_item_suppliers_temp: ' || l_cnt);
Insert into MSC_X_ITEM_ORGS_GTT(
company_key,
sr_tp_id
)
select distinct map2.company_key,tp.sr_tp_id
from msc_trading_partner_maps map2,
msc_trading_partners tp,
msc_item_suppliers its
where map2.map_type = 2
AND map2.tp_key = tp.partner_id
AND tp.partner_type = 3
AND tp.sr_tp_id = its.organization_id
AND tp.sr_instance_id = its.sr_instance_id
AND its.plan_id = -1
AND its.vmi_flag = 1
AND its.enable_vmi_auto_replenish_flag = 'Y';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows inserted into msc_item_orgs_temp: ' || l_cnt);
Insert into MSC_X_ITEM_SITES_GTT(
company_key,
tp_key
)
select distinct map3.company_key, map3.tp_key
from msc_trading_partner_maps map3,
msc_item_suppliers its
where map3.map_type = 3
AND map3.tp_key = its.supplier_site_id
AND its.plan_id = -1
AND its.vmi_flag = 1
AND its.enable_vmi_auto_replenish_flag = 'Y';
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows inserted into msc_item_sites_temp: ' || l_cnt);