[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT -- name,
backorders_only_flag,
autodetail_pr_flag,
auto_pick_confirm_flag,
pick_sequence_rule_id,
pick_grouping_rule_id,
include_planned_lines,
organization_id,
customer_id,
from_requested_date,
to_requested_date,
existing_rsvs_only_flag,
order_header_id,
inventory_item_id,
order_type_id,
from_scheduled_ship_date,
to_scheduled_ship_date,
shipment_priority_code,
ship_method_code,
ship_set_number,
ship_to_location_id,
default_stage_subinventory,
default_stage_locator_id,
pick_from_subinventory,
pick_from_locator_id,
task_id,
project_id,
ship_from_location_id,
autocreate_delivery_flag,
ship_confirm_rule_id,
autopack_flag,
autopack_level,
document_set_id,
task_planning_flag,
ac_delivery_criteria,
append_flag,
task_priority,
nvl(allocation_method,'I'), -- X-dock
crossdock_criteria_id, -- X-dock
dynamic_replenishment_flag, --bug# 6689448 (replenishment project)
rel_subinventory, --Bug8221008
client_id -- LSP PROJECT
FROM wsh_picking_rules
WHERE picking_rule_id = nvl(v_rule_id,picking_rule_id)
AND name = nvl(v_rule_name,name)
AND trunc(sysdate) BETWEEN nvl(start_date_active,trunc(sysdate)) AND nvl(end_date_active,trunc(sysdate) + 1);
SELECT transaction_type_id
FROM oe_transaction_types_tl
WHERE p_type_id IS NOT NULL
AND transaction_type_id = p_type_id
UNION All
SELECT transaction_type_id
FROM oe_transaction_types_tl
WHERE p_type_id IS NULL
AND name = p_type_name;
SELECT header_id
FROM oe_order_headers_all
WHERE p_header_id IS NOT NULL
AND header_id = p_header_id
UNION ALL
SELECT header_id
FROM oe_order_headers_all
WHERE p_header_id IS NULL
AND order_number = p_order_number
AND order_type_id = p_order_type_id;
SELECT set_id
FROM OE_SETS
WHERE p_ship_set_id IS NOT NULL
AND set_id = p_ship_set_id
UNION ALL
SELECT set_id
FROM OE_SETS
WHERE p_ship_set_id IS NULL
AND header_id = p_header_id
AND set_name = p_ship_set_number
AND set_type = 'SHIP_SET';
SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE delivery_detail_id = p_delivery_detail_id
AND container_flag IN ('N','Y'); -- R12 MDC
SELECT inventory_item_id
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = nvl(p_organization_id,organization_id)
AND rownum = 1;
SELECT secondary_inventory_name
FROM mtl_subinventories_trk_val_v
WHERE secondary_inventory_name = p_subinventory_name
AND organization_id = p_organization_id;
SELECT Inventory_location_id
FROM mtl_item_locations
WHERE Inventory_location_id = p_Locator_Id
AND subinventory_code = p_subinventory
AND organization_id = p_organization_id;
SELECT category_set_id
FROM mtl_category_sets
WHERE category_set_id = p_category_set_id
AND rownum = 1;
SELECT category_id
FROM mtl_item_categories
WHERE category_id = p_category_id
AND rownum = 1;
select location_id from wsh_ship_from_orgs_v
where organization_id = p_org_id;
select organization_id from wsh_ship_from_orgs_v
where location_id = p_location_id;
SELECT delivery_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id
AND delivery_type = 'STANDARD';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PICKING_BATCHES_PKG.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_PICKING_BATCHES_PKG.Insert_Row(
X_Rowid => l_batch_grp_rec.Row_Id,
X_Batch_Id => l_batch_grp_rec.Batch_Id,
P_Creation_Date => l_batch_grp_rec.Creation_Date,
P_Created_By => l_batch_grp_rec.Created_By,
P_Last_Update_Date => l_batch_grp_rec.Last_Update_Date,
P_Last_Updated_By => l_batch_grp_rec.Last_Updated_By,
P_Last_Update_Login => l_batch_grp_rec.Last_Update_Login,
p_batch_name_prefix => l_batch_name_prefix,
X_Name => l_batch_grp_rec.Name,
P_Backorders_Only_Flag => l_batch_grp_rec.Backorders_Only_Flag,
P_Document_Set_Id => l_batch_grp_rec.Document_Set_Id,
P_Existing_Rsvs_Only_Flag => l_batch_grp_rec.Existing_Rsvs_Only_Flag,
P_Shipment_Priority_Code => l_batch_grp_rec.Shipment_Priority_Code,
P_Ship_Method_Code => l_batch_grp_rec.Ship_Method_Code,
P_Customer_Id => l_batch_grp_rec.Customer_Id,
P_Order_Header_Id => l_batch_grp_rec.Order_Header_Id,
P_Ship_Set_Number => l_batch_grp_rec.Ship_Set_Id,
P_Inventory_Item_Id => l_batch_grp_rec.Inventory_Item_Id,
P_Order_Type_Id => l_batch_grp_rec.Order_Type_Id,
P_From_Requested_Date => l_batch_grp_rec.From_Requested_Date,
P_To_Requested_Date => l_batch_grp_rec.To_Requested_Date,
P_From_Scheduled_Ship_Date => l_batch_grp_rec.From_Scheduled_Ship_Date,
P_To_Scheduled_Ship_Date => l_batch_grp_rec.To_Scheduled_Ship_Date,
P_Ship_To_Location_Id => l_batch_grp_rec.Ship_To_Location_Id,
P_Ship_From_Location_Id => l_batch_grp_rec.Ship_From_Location_Id,
P_Trip_Id => l_batch_grp_rec.Trip_Id,
p_Delivery_Id => l_batch_grp_rec.Delivery_Id,
P_Include_Planned_Lines => l_batch_grp_rec.Include_Planned_Lines,
P_Pick_Grouping_Rule_Id => l_batch_grp_rec.Pick_Grouping_Rule_Id,
P_Pick_Sequence_Rule_Id => l_batch_grp_rec.Pick_Sequence_Rule_Id,
P_Autocreate_Delivery_Flag => l_batch_grp_rec.Autocreate_Delivery_Flag,
P_Attribute_Category => l_batch_grp_rec.Attribute_Category,
P_Attribute1 => l_batch_grp_rec.Attribute1,
P_Attribute2 => l_batch_grp_rec.Attribute2,
P_Attribute3 => l_batch_grp_rec.Attribute3,
P_Attribute4 => l_batch_grp_rec.Attribute4,
P_Attribute5 => l_batch_grp_rec.Attribute5,
P_Attribute6 => l_batch_grp_rec.Attribute6,
P_Attribute7 => l_batch_grp_rec.Attribute7,
P_Attribute8 => l_batch_grp_rec.Attribute8,
P_Attribute9 => l_batch_grp_rec.Attribute9,
P_Attribute10 => l_batch_grp_rec.Attribute10,
P_Attribute11 => l_batch_grp_rec.Attribute11,
P_Attribute12 => l_batch_grp_rec.Attribute12,
P_Attribute13 => l_batch_grp_rec.Attribute13,
P_Attribute14 => l_batch_grp_rec.Attribute14,
P_Attribute15 => l_batch_grp_rec.Attribute15,
P_Autodetail_Pr_Flag => l_batch_grp_rec.Autodetail_Pr_Flag,
-- Bug#: 3266659 - Setting carrier id to NULL
P_Carrier_Id => NULL,
P_Trip_Stop_Id => l_batch_grp_rec.Trip_Stop_Id,
P_Default_Stage_Subinventory => l_batch_grp_rec.Default_Stage_Subinventory,
P_Default_Stage_Locator_Id => l_batch_grp_rec.Default_Stage_Locator_Id,
P_Pick_From_Subinventory => l_batch_grp_rec.Pick_From_Subinventory,
P_Pick_From_locator_Id => l_batch_grp_rec.Pick_From_locator_Id,
P_Auto_Pick_Confirm_Flag => l_batch_grp_rec.Auto_Pick_Confirm_Flag,
P_Delivery_Detail_Id => l_batch_grp_rec.Delivery_Detail_Id,
P_Project_Id => l_batch_grp_rec.Project_Id,
P_Task_Id => l_batch_grp_rec.Task_Id,
P_Organization_Id => l_batch_grp_rec.Organization_Id,
P_Ship_Confirm_Rule_Id => l_batch_grp_rec.Ship_Confirm_Rule_Id,
P_Autopack_Flag => l_batch_grp_rec.Autopack_Flag,
P_Autopack_Level => l_batch_grp_rec.Autopack_Level,
P_Task_Planning_Flag => l_batch_grp_rec.Task_Planning_Flag,
-- Bug#: 3266659 - Removing non_picking_flag
-- P_Non_Picking_Flag => l_batch_grp_rec.Non_Picking_Flag,
p_categorySetID => l_batch_grp_rec.category_set_id,
p_categoryID => l_batch_grp_rec.category_id,
p_ship_set_smc_flag => l_batch_grp_rec.ship_set_smc_flag,
-- Bug#: 3266659 - Adding the columns like zone, region, delivery criteria, release
-- subinventory and append flag
p_regionID => l_batch_grp_rec.region_id,
p_zoneId => l_batch_grp_rec.zone_id,
p_acDelivCriteria => l_batch_grp_rec.ac_delivery_criteria,
p_RelSubinventory => l_batch_grp_rec.rel_subinventory,
p_append_flag => l_batch_grp_rec.append_flag,
p_task_priority => l_batch_grp_rec.task_priority,
p_actual_departure_date => l_batch_grp_rec.actual_departure_date,
p_allocation_method => l_batch_grp_rec.allocation_method, -- X-dock
p_crossdock_criteria_id => l_batch_grp_rec.crossdock_criteria_id, -- X-dock
p_dynamic_replenishment_flag => l_batch_grp_rec.dynamic_replenishment_flag, --bug# 6689448 (replenishment project)
p_client_id => l_batch_grp_rec.client_id -- LSP PROJECT :
);
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd,
wms_wp_wave_lines wpl,
wms_wp_wave_headers_vl wph
WHERE wdd.delivery_detail_id = wpl.delivery_detail_id
AND wpl.wave_header_id = c_pick_wave_header_id
AND wpl.wave_header_id=wph.wave_header_id
AND nvl(wpl.remove_from_wave_flag,'N') <> 'Y'
AND (wph.wave_Status in ('Planned','Created') or (wph.wave_status = 'Released' and Re_release_flag = 'Y'))
AND wdd.released_status in ('R', 'B', 'X')
AND NVL(wdd.replenishment_status,'C') = 'C';
savepoint update_batch_id_on_dd;
UPDATE wsh_delivery_details
SET batch_id = l_batch_id,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id
WHERE delivery_detail_id = l_dd_upd_tbl(i);
wsh_debug_sv.LOG(l_module_name, 'SQl updated',sql%rowcount);
wsh_debug_sv.LOG(l_module_name, 'Total number of delivery details selected',l_tot_fetch);
wsh_debug_sv.LOG(l_module_name, 'Total number of delivery details selected after locking',l_tot_upd_dds);
ROLLBACK TO update_batch_id_on_dd;