[Home] [Help]
46: , mis.organization_id
47: , mis.sr_instance_id
48: , mis.supplier_id
49: , mis.supplier_site_id
50: FROM msc_item_suppliers mis
51: WHERE mis.plan_id = -1
52: AND mis.vmi_flag = 1
53: ;
54:
65: || '/' || forecast_item.sr_instance_id
66: || '/' || forecast_item.supplier_id
67: || '/' || forecast_item.supplier_site_id
68: );
69: UPDATE msc_item_suppliers
70: SET vmi_refresh_flag = 0
71: WHERE plan_id = forecast_item.plan_id
72: AND inventory_item_id = forecast_item.inventory_item_id
73: AND organization_id = forecast_item.organization_id
112: its.sr_instance_id,
113: its.supplier_id,
114: its.supplier_site_id
115: FROM msc_sup_dem_entries sd1,
116: msc_item_suppliers its,
117: msc_trading_partners tp,
118: msc_trading_partner_maps map,
119: msc_trading_partner_maps map2,
120: msc_trading_partner_maps map3,
153: its.sr_instance_id,
154: its.supplier_id,
155: its.supplier_site_id
156: FROM msc_sup_dem_entries sd1,
157: msc_item_suppliers its,
158: msc_trading_partners tp,
159: msc_trading_partner_maps map,
160: msc_trading_partner_maps map2,
161: msc_trading_partner_maps map3,
203: its.plan_id,
204: its.sr_instance_id,
205: its.supplier_id,
206: its.supplier_site_id
207: FROM msc_item_suppliers its,
208: MSC_X_ITEM_SUPPLIERS_GTT iut,
209: MSC_X_ITEM_ORGS_GTT iot,
210: MSC_X_ITEM_SITES_GTT ist
211: WHERE its.plan_id = -1
293:
294: v_retval := FND_INSTALLATION.GET_APP_INFO(
295: 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
296:
297: -- performance fix (bug 4898923), create index on msc_item_suppliers for vmi enabled items
298: -- create_index_item_sup_vmi (v_applsys_schema);
299:
300: -- do netting for each item/org/plan/sr_instance_id/supplier/supplier_site combination
301:
399: END LOOP; -- item/org/plan/sr_instance_id combination
400: print_debug_info('End of loop for combinations with no transaction data in CP');
401: CLOSE c_netting_items_notx;
402:
403: -- drop the msc_item_suppliers_n1 index
404: --drop_index_item_sup_vmi (v_applsys_schema);
405:
406: print_user_info('End of VMI replenishment engine');
407:
548:
549: CURSOR c_vmi_replenishment_approval
550: IS
551: SELECT vmi_replenishment_approval
552: FROM msc_item_suppliers
553: WHERE inventory_item_id = p_inventory_item_id
554: AND organization_id = p_organization_id
555: AND plan_id = p_plan_id
556: AND sr_instance_id = p_sr_instance_id
568: p_supplier_id in number,
569: p_supplier_site_id in NUMBER
570: ) is
571: SELECT enable_vmi_auto_replenish_flag
572: FROM MSC_ITEM_SUPPLIERS
573: WHERE organization_id = p_organization_id
574: AND inventory_item_id = p_inventory_item_id
575: AND plan_id = p_plan_id
576: AND sr_instance_id = p_sr_instance_id
1329: , p_supplier_site_id IN NUMBER
1330: , p_sr_instance_id IN NUMBER
1331: ) IS
1332: SELECT mis.supplier_item_name
1333: FROM msc_item_suppliers mis
1334: WHERE mis.inventory_item_id = p_inventory_item_id
1335: AND mis.organization_id = p_organization_id
1336: AND mis.supplier_id = p_supplier_id
1337: AND mis.supplier_site_id = p_supplier_site_id
1360: , mvt.average_daily_demand
1361: , mis.vmi_refresh_flag
1362: , mis.processing_lead_time
1363: , mis.replenishment_method
1364: FROM msc_item_suppliers mis
1365: , msc_vmi_temp mvt
1366: WHERE mis.inventory_item_id = p_inventory_item_id
1367: AND mis.organization_id = p_organization_id
1368: AND mis.plan_id = p_plan_id
2566: l_vmi_auto_replenish_flag VARCHAR2(100);
2567:
2568: CURSOR c_vmi_flag IS
2569: SELECT vmi_flag, enable_vmi_auto_replenish_flag
2570: FROM msc_item_suppliers
2571: WHERE inventory_item_id = p_inventory_item_id
2572: AND organization_id = p_organization_id
2573: AND plan_id = p_plan_id
2574: AND sr_instance_id = p_sr_instance_id
2616: l_vmi_auto_replenish_flag VARCHAR2(100);
2617:
2618: CURSOR c_vmi_flag IS
2619: SELECT vmi_flag, enable_vmi_auto_replenish_flag
2620: FROM msc_item_suppliers
2621: WHERE inventory_item_id = p_inventory_item_id
2622: AND organization_id = p_organization_id
2623: AND plan_id = p_plan_id
2624: AND sr_instance_id = p_sr_instance_id
2708: );
2709:
2710: CURSOR c_vmi_replenishment_approval IS
2711: SELECT vmi_replenishment_approval
2712: FROM msc_item_suppliers
2713: WHERE inventory_item_id = l_inventory_item_id
2714: AND organization_id = l_organization_id
2715: AND plan_id = l_plan_id
2716: AND sr_instance_id = l_sr_instance_id
2810: );
2811:
2812: CURSOR c_vmi_replenishment_approval IS
2813: SELECT vmi_replenishment_approval
2814: FROM msc_item_suppliers
2815: WHERE inventory_item_id = l_inventory_item_id
2816: AND organization_id = l_organization_id
2817: AND plan_id = l_plan_id
2818: AND sr_instance_id = l_sr_instance_id
3563: )
3564: select distinct r.object_id, map1.tp_key
3565: from msc_trading_partner_maps map1,
3566: msc_company_relationships r,
3567: msc_item_suppliers its
3568: where map1.map_type = 1
3569: AND map1.company_key = r.relationship_id
3570: AND map1.tp_key = its.supplier_id
3571: AND r.relationship_type = 2
3574: AND its.vmi_flag = 1
3575: AND its.enable_vmi_auto_replenish_flag = 'Y';
3576:
3577: l_cnt := SQL%ROWCOUNT;
3578: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Rows inserted into msc_item_suppliers_temp: ' || l_cnt);
3579: -- Create Temp table for Item Supplier Orgs
3580:
3581: Insert into MSC_X_ITEM_ORGS_GTT(
3582: company_key,
3584: )
3585: select distinct map2.company_key,tp.sr_tp_id
3586: from msc_trading_partner_maps map2,
3587: msc_trading_partners tp,
3588: msc_item_suppliers its
3589: where map2.map_type = 2
3590: AND map2.tp_key = tp.partner_id
3591: AND tp.partner_type = 3
3592: AND tp.sr_tp_id = its.organization_id
3605: tp_key
3606: )
3607: select distinct map3.company_key, map3.tp_key
3608: from msc_trading_partner_maps map3,
3609: msc_item_suppliers its
3610: where map3.map_type = 3
3611: AND map3.tp_key = its.supplier_site_id
3612: AND its.plan_id = -1
3613: AND its.vmi_flag = 1