[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Select_Deliveries (
p_input_info IN WSH_BATCH_PROCESS.Select_Criteria_Rec,
p_batch_rec IN OUT NOCOPY WSH_PICKING_BATCHES%ROWTYPE,
x_selected_del_tab OUT NOCOPY WSH_BATCH_PROCESS.Del_Info_Tab,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_sc_SELECT VARCHAR2(3000) := NULL;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Select_Deliveries';
x_selected_del_tab.delete;
l_sc_SELECT := l_sc_SELECT || ' wnd.delivery_id , wnd.organization_id, wnd.initial_pickup_location_id ';
l_sc_EXISTS := l_sc_EXISTS ||' SELECT wdd.delivery_detail_id ';
l_sc_NOT_EXISTS := l_sc_NOT_EXISTS || ' SELECT wdd2.delivery_detail_id ';
p_batch_rec.selected_batch_id := p_input_info.pr_batch_id;
l_sc_EXISTS_BOL := l_sc_EXISTS_BOL ||' SELECT wdi.document_instance_id ';
l_sc_FINAL := 'SELECT ' ||l_sc_SELECT||' FROM '||l_sc_FROM||' WHERE '||l_sc_WHERE ;
x_selected_del_tab(x_selected_del_tab.count+1).delivery_id := v_delivery_id;
x_selected_del_tab(x_selected_del_tab.count).organization_id := v_organization_id;
x_selected_del_tab(x_selected_del_tab.count).initial_pickup_location_id := v_initial_pickup_location_id;
WSH_DEBUG_SV.log(l_module_name, to_char(x_selected_del_tab.count)||' deliveries fetched to be processed');
wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
END Select_Deliveries;
SELECT delivery_id,
status_code,
planned_flag,
initial_pickup_date,
organization_id,
ship_method_code,
initial_pickup_location_id
FROM wsh_new_deliveries
WHERE delivery_id = c_delivery_id and
status_code = 'OP' AND
NVL(auto_sc_exclude_flag, 'N')= 'N' FOR UPDATE NOWAIT;
SELECT wt.ship_method_code
FROM wsh_delivery_legs dlg,
wsh_trip_stops st,
wsh_trips wt
WHERE dlg.delivery_id = x_delivery_id
AND st.stop_id = dlg.pick_up_stop_id
AND st.stop_location_id = x_initial_loc_id
AND st.trip_id = wt.trip_id
AND wt.ship_method_code IS NOT NULL
AND rownum = 1;
l_tmp_del_tab.delete;
UPDATE WSH_NEW_DELIVERIES SET BATCH_ID = p_sc_batch_id
WHERE delivery_id = p_delivery_id;
SELECT a.stop_id self_stop_id, b.trip_id, c.stop_id other_stop_id
FROM wsh_trip_stops a, wsh_trips b, wsh_trip_stops c
WHERE a.trip_id = b.trip_id AND
a.stop_id = c_stop_id AND
c.trip_id = b.trip_id AND
a.stop_id <> C.stop_id
FOR UPDATE NOWAIT;
l_action_prms.action_code := 'UPDATE-STATUS';
SELECT ship_confirm_rule_id, creation_date, actual_departure_date
FROM wsh_picking_batches
WHERE batch_id = p_sc_batch_id FOR UPDATE NOWAIT;
SELECT DISTINCT wtp.trip_id, wst.stop_sequence_number, wst.stop_id, wst.stop_location_id
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wlg,
wsh_trip_stops wst,
wsh_trips wtp
WHERE wnd.delivery_id = wlg.delivery_id AND
wlg.pick_up_stop_id = wst.stop_id AND
wnd.status_code = 'CO' AND
wnd.batch_id = p_sc_batch_id AND
wtp.trip_id = wst.trip_id AND
wst.status_code = 'OP' AND
NOT EXISTS (
select '1' from wsh_exceptions we where
we.delivery_id = wnd.delivery_id AND
we.severity = 'ERROR' AND
we.status = 'OPEN' AND
we.EXCEPTION_NAME = 'WSH_SC_REQ_EXPORT_COMPL')
ORDER BY wtp.trip_id, wst.stop_sequence_number, wst.stop_id ;
select wsto.trip_id, wsto.stop_sequence_number, wsto.stop_id , wsto.stop_location_id
from wsh_trip_stops wsto
, wsh_new_deliveries wnd
, wsh_delivery_legs wlg
, wsh_trip_stops wst
where wnd.batch_id = p_sc_batch_id
and wnd.status_code = 'CO'
and wnd.delivery_id = wlg.delivery_id
and wlg.pick_up_stop_id = wst.stop_id
and wnd.INITIAL_PICKUP_LOCATION_ID = wst.STOP_LOCATION_ID
and wst.status_code in ( 'OP' , 'AR' )
and NOT EXISTS (
select '1' from wsh_exceptions we
where we.delivery_id = wnd.delivery_id
AND we.severity = 'ERROR'
AND we.status = 'OPEN'
AND we.EXCEPTION_NAME = 'WSH_SC_REQ_EXPORT_COMPL')
and wsto.trip_id = wst.trip_id
ORDER BY 1, 2, 3;
SELECT wts.stop_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_picking_batches wpb
WHERE p_batch_id IS NOT NULL
AND wnd.batch_id = p_batch_id
AND wdl.delivery_id = wnd.delivery_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wts.stop_location_id = wnd.initial_pickup_location_id
AND wpb.batch_id = wnd.batch_id
AND wts.status_code = 'CL'
AND rownum = 1;
l_sc_confirmed_dels.delete;
l_stops_to_close.delete;
l_stop_location_ids.delete;
select status_code into l_status_code from wsh_new_deliveries where delivery_id = p_del_tab(i).delivery_id;
SELECT delivery_id,
status_code,
planned_flag,
initial_pickup_date,
organization_id,
ship_method_code
FROM wsh_new_deliveries
WHERE delivery_id = c_delivery_id and
status_code = 'OP' AND
NVL(auto_ap_exclude_flag, 'N')= 'N' FOR UPDATE NOWAIT;
l_tmp_del_tab.delete;
UPDATE WSH_NEW_DELIVERIES SET AP_BATCH_ID = p_ap_batch_id
WHERE delivery_id = p_delivery_id;
x_packed_del_tab.delete;
l_selected_del_tab WSH_BATCH_PROCESS.Del_Info_Tab;
l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
WSH_SELECT_ERR EXCEPTION;
l_delivery_ids.delete;
l_select_criteria.process_mode := G_SHIP_CONFIRM;
l_select_criteria.organization_id := p_organization_id;
l_select_criteria.pr_batch_id := p_pr_batch_id;
l_select_criteria.ap_batch_id := p_ap_batch_id;
l_select_criteria.delivery_name_lo := p_delivery_name_lo;
l_select_criteria.delivery_name_hi := p_delivery_name_hi;
l_select_criteria.bol_number_lo := p_bol_number_lo;
l_select_criteria.bol_number_hi := p_bol_number_hi;
l_select_criteria.planned_flag := p_planned_flag;
l_select_criteria.ship_from_loc_id := p_ship_from_loc_id;
l_select_criteria.ship_to_loc_id := p_ship_to_loc_id;
l_select_criteria.intmed_ship_to_loc_id := p_intmed_ship_to_loc_id;
l_select_criteria.pooled_ship_to_loc_id := p_pooled_ship_to_loc_id;
l_select_criteria.customer_id := p_customer_id;
l_select_criteria.ship_method_code := p_ship_method_code;
l_select_criteria.fob_code := p_fob_code;
l_select_criteria.freight_terms_code := p_freight_terms_code;
l_select_criteria.pickup_date_lo := p_pickup_date_lo;
l_select_criteria.pickup_date_hi := p_pickup_date_hi;
l_select_criteria.dropoff_date_lo := p_dropoff_date_lo;
l_select_criteria.dropoff_date_hi := p_dropoff_date_hi;
l_select_criteria.log_level := l_log_level;
Select_Deliveries(
p_input_info => l_select_criteria,
p_batch_rec => l_batch_rec,
x_selected_del_tab => l_selected_del_tab,
x_return_status => l_return_status);
raise WSH_SELECT_ERR;
IF l_selected_del_tab.count > 0 THEN
-- deliveryMerge
FOR i in l_selected_del_tab.FIRST .. l_selected_del_tab.LAST LOOP
l_delivery_ids(l_delivery_ids.count+1) := l_selected_del_tab(i).delivery_id;
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_rowid,
X_Batch_Id => l_batch_rec.batch_id,
P_Creation_Date => SYSDATE,
P_Created_By => l_user_id,
P_Last_Update_Date => SYSDATE,
P_Last_Updated_By => l_user_id,
P_Last_Update_Login => l_login_id,
P_batch_name_prefix => p_sc_batch_prefix,
X_Name => l_batch_rec.name,
P_Backorders_Only_Flag => NULL,
P_Document_Set_Id => NULL,
P_Existing_Rsvs_Only_Flag => NULL,
P_Shipment_Priority_Code => NULL,
P_Ship_Method_Code => l_batch_rec.ship_method_code,
P_Customer_Id => l_batch_rec.customer_id,
P_Order_Header_Id => NULL,
P_Ship_Set_Number => NULL,
P_Inventory_Item_Id => NULL,
P_Order_Type_Id => NULL,
P_From_Requested_Date => NULL,
P_To_Requested_Date => NULL,
P_From_Scheduled_Ship_Date => NULL,
P_To_Scheduled_Ship_Date => NULL,
P_Ship_To_Location_Id => l_batch_rec.ship_to_location_id,
P_Ship_From_Location_Id => l_batch_rec.ship_from_location_id,
P_Trip_Id => NULL,
P_Delivery_Id => NULL,
P_Include_Planned_Lines => NULL,
P_Pick_Grouping_Rule_Id => NULL,
P_pick_sequence_rule_id => NULL,
P_Autocreate_Delivery_Flag => NULL,
P_Attribute_Category => NULL,
P_Attribute1 => NULL,
P_Attribute2 => NULL,
P_Attribute3 => NULL,
P_Attribute4 => NULL,
P_Attribute5 => NULL,
P_Attribute6 => NULL,
P_Attribute7 => NULL,
P_Attribute8 => NULL,
P_Attribute9 => NULL,
P_Attribute10 => NULL,
P_Attribute11 => NULL,
P_Attribute12 => NULL,
P_Attribute13 => NULL,
P_Attribute14 => NULL,
P_Attribute15 => NULL,
P_Autodetail_Pr_Flag => NULL,
P_Carrier_Id => NULL,
P_Trip_Stop_Id => NULL,
P_Default_stage_subinventory => NULL,
P_Default_stage_locator_id => NULL,
P_Pick_from_subinventory => NULL,
P_Pick_from_locator_id => NULL,
P_Auto_pick_confirm_flag => NULL,
P_Delivery_Detail_ID => NULL,
P_Project_ID => NULL,
P_Task_ID => NULL,
P_Organization_Id => l_batch_rec.organization_id,
P_Ship_Confirm_Rule_Id => l_batch_rec.ship_confirm_rule_id,
P_Autopack_Flag => NULL,
P_Autopack_Level => NULL,
P_Task_Planning_Flag => NULL,
P_Non_Picking_Flag => l_batch_rec.non_picking_flag,
p_regionID => NULL,
p_zoneId => NULL,
p_categoryID => NULL,
p_categorySetID => NULL,
p_acDelivCriteria => NULL,
p_RelSubinventory => NULL,
p_actual_departure_date => l_batch_rec.actual_departure_date,
p_allocation_method => NULL,
p_crossdock_criteria_id => NULL,
p_append_flag => NULL,
p_task_priority => NULL,
p_Delivery_Name_Lo => l_batch_rec.delivery_name_lo,
p_Delivery_Name_Hi => l_batch_rec.delivery_name_hi,
p_Bol_Number_Lo => l_batch_rec.bol_number_lo,
p_Bol_Number_Hi => l_batch_rec.bol_number_hi,
p_Intmed_Ship_To_Loc_Id => l_batch_rec.intmed_ship_to_loc_id,
p_Pooled_Ship_To_Loc_Id => l_batch_rec.pooled_ship_to_loc_id,
p_Fob_Code => l_batch_rec.fob_code,
p_Freight_Terms_Code => l_batch_rec.freight_terms_code,
p_Pickup_Date_Lo => l_batch_rec.pickup_date_lo,
p_Pickup_Date_Hi => l_batch_rec.pickup_date_hi,
p_Dropoff_Date_Lo => l_batch_rec.dropoff_date_lo,
p_Dropoff_Date_Hi => l_batch_rec.dropoff_date_hi,
p_Planned_Flag => l_batch_rec.planned_flag,
p_Selected_Batch_Id => l_batch_rec.selected_batch_id);
WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries selected to be ship confirmed are: ');
FOR k in 1 .. l_selected_del_tab.count LOOP
WSH_DEBUG_SV.logmsg(l_module_name, ' delivery: ' || l_selected_del_tab(k).delivery_id);
p_del_tab => l_selected_del_tab,
p_sc_batch_id => l_batch_rec.batch_id,
p_log_level => l_log_level,
x_confirmed_del_tab => l_confirmed_del_tab,
x_results_summary => l_results_summary,
x_return_status => l_return_status,
p_commit => FND_API.G_TRUE); -- BugFix #4001135
WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
/* no deliveries selected */
WSH_UTIL_CORE.PrintDateTime;
WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count) ||' deliveries selected to be ship confirmed');
WSH_UTIL_CORE.PrintMsg('No Lines were selected for Ship Confirmation because Allow Future Ship Date Parameter is disabled and Actual Ship Date is greater than current system date');
WHEN WSH_SELECT_ERR THEN
l_completion_status := 'WARNING';
WSH_UTIL_CORE.PrintMsg('Failed to select deliveries for the batch');
l_selected_del_tab WSH_BATCH_PROCESS.Del_Info_Tab;
l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
WSH_SELECT_ERR EXCEPTION;
l_packed_del_tab.delete;
l_select_criteria.process_mode := G_AUTO_PACK;
l_select_criteria.organization_id := p_organization_id;
l_select_criteria.pr_batch_id := p_pr_batch_id;
l_select_criteria.delivery_name_lo := p_delivery_name_lo;
l_select_criteria.delivery_name_hi := p_delivery_name_hi;
l_select_criteria.bol_number_lo := p_bol_number_lo;
l_select_criteria.bol_number_hi := p_bol_number_hi;
l_select_criteria.planned_flag := p_planned_flag;
l_select_criteria.ship_from_loc_id := p_ship_from_loc_id;
l_select_criteria.ship_to_loc_id := p_ship_to_loc_id;
l_select_criteria.intmed_ship_to_loc_id := p_intmed_ship_to_loc_id;
l_select_criteria.pooled_ship_to_loc_id := p_pooled_ship_to_loc_id;
l_select_criteria.customer_id := p_customer_id;
l_select_criteria.ship_method_code := p_ship_method_code;
l_select_criteria.fob_code := p_fob_code;
l_select_criteria.freight_terms_code := p_freight_terms_code;
l_select_criteria.pickup_date_lo := p_pickup_date_lo;
l_select_criteria.pickup_date_hi := p_pickup_date_hi;
l_select_criteria.dropoff_date_lo := p_dropoff_date_lo;
l_select_criteria.dropoff_date_hi := p_dropoff_date_hi;
l_select_criteria.log_level := l_log_level;
Select_Deliveries(
p_input_info => l_select_criteria,
p_batch_rec => l_batch_rec,
x_selected_del_tab => l_selected_del_tab,
x_return_status => l_return_status);
raise WSH_SELECT_ERR;
IF l_selected_del_tab.count > 0 THEN
-- insert new record in table WSH_PICKING_BATCHES, with NON_PICKING_FLAG = 'Y',
-- SHIP_CONFIRM_RULE_ID =
-- required fields for auto pack batch
l_batch_rec.non_picking_flag := 'Y';
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_rowid,
X_Batch_Id => l_batch_rec.batch_id,
P_Creation_Date => SYSDATE,
P_Created_By => l_user_id,
P_Last_Update_Date => SYSDATE,
P_Last_Updated_By => l_user_id,
P_Last_Update_Login => l_login_id,
P_batch_name_prefix => p_ap_batch_prefix,
X_Name => l_batch_rec.name,
P_Backorders_Only_Flag => NULL,
P_Document_Set_Id => NULL,
P_Existing_Rsvs_Only_Flag => NULL,
P_Shipment_Priority_Code => NULL,
P_Ship_Method_Code => l_batch_rec.ship_method_code,
P_Customer_Id => l_batch_rec.customer_id,
P_Order_Header_Id => NULL,
P_Ship_Set_Number => NULL,
P_Inventory_Item_Id => NULL,
P_Order_Type_Id => NULL,
P_From_Requested_Date => NULL,
P_To_Requested_Date => NULL,
P_From_Scheduled_Ship_Date => NULL,
P_To_Scheduled_Ship_Date => NULL,
P_Ship_To_Location_Id => l_batch_rec.ship_to_location_id,
P_Ship_From_Location_Id => l_batch_rec.ship_from_location_id,
P_Trip_Id => NULL,
P_Delivery_Id => NULL,
P_Include_Planned_Lines => NULL,
P_Pick_Grouping_Rule_Id => NULL,
P_pick_sequence_rule_id => NULL,
P_Autocreate_Delivery_Flag => NULL,
P_Attribute_Category => NULL,
P_Attribute1 => NULL,
P_Attribute2 => NULL,
P_Attribute3 => NULL,
P_Attribute4 => NULL,
P_Attribute5 => NULL,
P_Attribute6 => NULL,
P_Attribute7 => NULL,
P_Attribute8 => NULL,
P_Attribute9 => NULL,
P_Attribute10 => NULL,
P_Attribute11 => NULL,
P_Attribute12 => NULL,
P_Attribute13 => NULL,
P_Attribute14 => NULL,
P_Attribute15 => NULL,
P_Autodetail_Pr_Flag => NULL,
P_Carrier_Id => NULL,
P_Trip_Stop_Id => NULL,
P_Default_stage_subinventory => NULL,
P_Default_stage_locator_id => NULL,
P_Pick_from_subinventory => NULL,
P_Pick_from_locator_id => NULL,
P_Auto_pick_confirm_flag => NULL,
P_Delivery_Detail_ID => NULL,
P_Project_ID => NULL,
P_Task_ID => NULL,
P_Organization_Id => l_batch_rec.organization_id,
P_Ship_Confirm_Rule_Id => NULL,
P_Autopack_Flag => l_batch_rec.autopack_flag,
P_Autopack_Level => l_batch_rec.autopack_level,
P_Task_Planning_Flag => NULL,
P_Non_Picking_Flag => l_batch_rec.non_picking_flag,
p_regionID => NULL,
p_zoneId => NULL,
p_categoryID => NULL,
p_categorySetID => NULL,
p_acDelivCriteria => NULL,
p_RelSubinventory => NULL,
p_actual_departure_date => NULL,
p_allocation_method => NULL,
p_crossdock_criteria_id => NULL,
p_append_flag => NULL,
p_task_priority => NULL,
p_Delivery_Name_Lo => l_batch_rec.delivery_name_lo,
p_Delivery_Name_Hi => l_batch_rec.delivery_name_hi,
p_Bol_Number_Lo => l_batch_rec.bol_number_lo,
p_Bol_Number_Hi => l_batch_rec.bol_number_hi,
p_Intmed_Ship_To_Loc_Id => l_batch_rec.intmed_ship_to_loc_id,
p_Pooled_Ship_To_Loc_Id => l_batch_rec.pooled_ship_to_loc_id,
p_Fob_Code => l_batch_rec.fob_code,
p_Freight_Terms_Code => l_batch_rec.freight_terms_code,
p_Pickup_Date_Lo => l_batch_rec.pickup_date_lo,
p_Pickup_Date_Hi => l_batch_rec.pickup_date_hi,
p_Dropoff_Date_Lo => l_batch_rec.dropoff_date_lo,
p_Dropoff_Date_Hi => l_batch_rec.dropoff_date_hi,
p_Planned_Flag => l_batch_rec.planned_flag,
p_Selected_Batch_Id => l_batch_rec.selected_batch_id);
WSH_DEBUG_SV.logmsg(l_module_name, 'Deliveries selected to be auto packed are: ');
FOR k in 1 .. l_selected_del_tab.count LOOP
WSH_DEBUG_SV.logmsg(l_module_name, ' delivery: ' || l_selected_del_tab(k).delivery_id);
p_del_tab => l_selected_del_tab,
p_ap_batch_id => l_batch_rec.batch_id,
p_auto_pack_level => p_auto_pack_level,
p_log_level => l_log_level,
x_packed_del_tab => l_packed_del_tab,
x_results_summary => l_results_summary,
x_return_status => l_return_status,
p_commit => FND_API.G_TRUE); -- BugFix #4001135
WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
/* not deliveries selected */
WSH_UTIL_CORE.PrintDateTime;
WSH_UTIL_CORE.PrintMsg(to_char(l_selected_del_tab.count)|| ' deliveries selected for auto packing');
WHEN WSH_SELECT_ERR THEN
l_completion_status := 'WARNING';
WSH_UTIL_CORE.PrintMsg('Failed to select deliveries for the batch');
PROCEDURE Select_Delivery_Lines (
p_select_criteria IN WSH_BATCH_PROCESS.Select_Criteria_Rec,
p_autocreate_deliveries IN VARCHAR2,
x_selected_det_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
x_return_status OUT NOCOPY VARCHAR2)
IS
--TCA View Changes Start
CURSOR get_customer_name(c_customer_id NUMBER) IS
SELECT substrb ( party.party_name, 1, 50 ) customer_name
FROM hz_parties Party, hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id AND
cust_acct.cust_account_id = c_customer_id;
l_sc_SELECT VARCHAR2(3000) := NULL;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Select_Delivery_Lines';
x_selected_det_tbl.delete;
l_sc_SELECT := l_sc_SELECT || ' wdd.delivery_detail_id ';
l_sc_SELECT := l_sc_SELECT || ' wdd.delivery_detail_id ';
IF p_select_criteria.delivery_lines_status is not NULL THEN
IF p_select_criteria.delivery_lines_status = 'ALL' THEN
l_sc_WHERE := l_sc_WHERE ||'AND wdd.released_status in (''X'', ''R'', ''S'', ''Y'') ';
IF p_select_criteria.delivery_lines_status = 'K' THEN
l_sc_WHERE := l_sc_WHERE || ' AND wdd.move_order_line_id IS NULL ';
ELSIF p_select_criteria.delivery_lines_status = 'S' THEN
l_sc_WHERE := l_sc_WHERE || ' AND wdd.move_order_line_id IS NOT NULL ';
p_lookup_code => p_select_criteria.delivery_lines_status,
p_lookup_type => 'WSH_PD_DEL_LINE_STATUS' );
WSH_UTIL_CORE.PrintMsg(' Delivery Lines Status: '|| p_select_criteria.delivery_lines_status);
IF p_select_criteria.organization_id IS NOT NULL THEN
l_sc_WHERE := l_sc_WHERE ||'AND wdd.organization_id = :x_organization_id ';
FND_MESSAGE.SET_TOKEN('ORGANIZATION_NAME', WSH_UTIL_CORE.Get_Org_Name(p_select_criteria.organization_id));
WSH_UTIL_CORE.PrintMsg(' Organization ID: '|| p_select_criteria.organization_id);
IF p_select_criteria.scheduled_ship_date_lo IS NOT NULL OR p_select_criteria.scheduled_ship_date_hi IS NOT NULL THEN
IF p_select_criteria.scheduled_ship_date_lo IS NOT NULL AND p_select_criteria.scheduled_ship_date_hi IS NOT NULL THEN
l_scheduled_ship_date_lo := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_lo);
l_scheduled_ship_date_hi := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_hi);
ELSIF p_select_criteria.scheduled_ship_date_lo IS NOT NULL THEN
l_scheduled_ship_date_lo := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_lo);
l_scheduled_ship_date_hi := fnd_date.canonical_to_date(p_select_criteria.scheduled_ship_date_hi);
IF p_select_criteria.ship_to_loc_id IS NOT NULL THEN
l_sc_WHERE := l_sc_WHERE || 'AND wdd.ship_to_location_id = :x_ship_to_loc_id ';
p_location_id => p_select_criteria.ship_to_loc_id,
p_format => 'NEW UI CODE'),
1, 80);
WSH_UTIL_CORE.PrintMsg(' Ship to Location ID: '|| to_char(p_select_criteria.ship_to_loc_id));
IF p_select_criteria.source_code IS NOT NULL THEN
IF p_select_criteria.source_code = 'BOTH' THEN
l_sc_WHERE := l_sc_WHERE ||'AND wdd.source_code in (''OE'', ''OKE'') ';
p_lookup_code => p_select_criteria.source_code,
p_lookup_type => 'WSH_PD_SOURCE_SYSTEM' );
WSH_UTIL_CORE.PrintMsg(' Source System: '|| p_select_criteria.source_code);
IF p_select_criteria.customer_id IS NOT NULL THEN
l_sc_WHERE := l_sc_WHERE || 'AND wdd.customer_id = :x_customer_id ';
OPEN get_customer_name(p_select_criteria.customer_id);
WSH_UTIL_CORE.PrintMsg(' Customer ID: '|| p_select_criteria.customer_id);
IF p_select_criteria.ship_method_code IS NOT NULL THEN
l_sc_WHERE := l_sc_WHERE || 'AND wdd.ship_method_code = :x_ship_method_code ';
p_lookup_code => p_select_criteria.ship_method_code,
p_lookup_type => 'SHIP_METHOD' );
WSH_UTIL_CORE.PrintMsg(' Ship Method Code: '|| p_select_criteria.ship_method_code);
FND_MESSAGE.SET_TOKEN('LOG_LEVEL', to_char(p_select_criteria.log_level));
WSH_UTIL_CORE.PrintMsg(' Log Level: '|| p_select_criteria.log_level);
l_sc_FINAL := 'SELECT ' ||l_sc_SELECT||' FROM '||l_sc_FROM||' WHERE '||l_sc_WHERE ;
IF p_select_criteria.log_level > 0 OR l_debug_on THEN
-- print SELECT statement if deubg is turned on
i := 1;
WSH_DEBUG_SV.log(l_module_name,' x_organization_id', to_char(p_select_criteria.organization_id));
WSH_DEBUG_SV.log(l_module_name,' x_source_code', p_select_criteria.source_code);
WSH_DEBUG_SV.log(l_module_name,' x_ship_to_loc_id', to_char(p_select_criteria.ship_to_loc_id));
WSH_DEBUG_SV.log(l_module_name,' x_customer_id', to_char(p_select_criteria.customer_id));
WSH_DEBUG_SV.log(l_module_name,' x_ship_method_code', p_select_criteria.ship_method_code);
WSH_DEBUG_SV.log(l_module_name,' x_released_status', p_select_criteria.delivery_lines_status);
IF p_select_criteria.delivery_lines_status IN ('K', 'S') THEN
l_line_status := 'S';
l_line_status := p_select_criteria.delivery_lines_status;
WSH_DEBUG_SV.log(l_module_name, 'p_select_criteria.delivery_lines_status',
p_select_criteria.delivery_lines_status);
IF p_select_criteria.delivery_lines_status IS NOT NULL AND
p_select_criteria.delivery_lines_status <> 'ALL' THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_released_status', l_line_status); -- Bug 5624475 and ECO 5676263
IF p_select_criteria.organization_id IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_organization_id', p_select_criteria.organization_id);
IF p_select_criteria.scheduled_ship_date_lo IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_scheduled_ship_date_lo', l_scheduled_ship_date_lo);
IF p_select_criteria.scheduled_ship_date_hi IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_scheduled_ship_date_hi', l_scheduled_ship_date_hi);
IF p_select_criteria.source_code IS NOT NULL
AND p_select_criteria.source_code <> 'BOTH' THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_source_code', p_select_criteria.source_code);
IF p_select_criteria.ship_to_loc_id IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_to_loc_id', p_select_criteria.ship_to_loc_id);
IF p_select_criteria.customer_id IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_customer_id', p_select_criteria.customer_id);
IF p_select_criteria.ship_method_code IS NOT NULL THEN
DBMS_SQL.BIND_VARIABLE(v_CursorID,':x_ship_method_code', p_select_criteria.ship_method_code);
x_selected_det_tbl(i) := v_delivery_detail_id_tbl(i);
WSH_DEBUG_SV.logmsg(l_module_name,' Fetched delivery line '|| to_char(x_selected_det_tbl(i)));
WSH_DEBUG_SV.logmsg(l_module_name, to_char(x_selected_det_tbl.count)||' delivery lines fetched to be processed');
wsh_util_core.default_handler('WSH_BATCH_PROCESS.Select_Deliveries');
END Select_Delivery_Lines;
p_selected_det_tbl IN WSH_UTIL_CORE.Id_Tab_Type,
p_append_flag IN VARCHAR2,
p_ac_del_criteria IN VARCHAR2,
x_appended_det_num OUT NOCOPY NUMBER,
x_autocreate_del_det_num OUT NOCOPY NUMBER,
x_appended_del_num OUT NOCOPY NUMBER,
x_new_del_num OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_delivery_detail_tbl WSH_UTIL_CORE.Id_Tab_Type;
l_appended_det_tbl.delete;
l_unappended_det_tbl.delete;
p_delivery_detail_tbl => p_selected_det_tbl,
p_append_flag => l_append_flag,
p_group_by_header => l_ac_del_criteria,
p_commit => FND_API.G_FALSE,
p_lock_rows => FND_API.G_TRUE,
p_check_fte_compatibility => FND_API.G_TRUE,
x_appended_det_tbl => l_appended_det_tbl,
x_unappended_det_tbl => l_unappended_det_tbl,
x_appended_del_tbl => l_appended_del_tbl,
x_return_status => l_return_status);
l_unappended_det_tbl.delete;
l_unappended_det_tbl := p_selected_det_tbl;
SELECT substrb ( party.party_name, 1, 50 ) customer_name
FROM hz_parties Party, hz_cust_accounts cust_acct
WHERE cust_acct.party_id = party.party_id AND
cust_acct.cust_account_id = c_customer_id;
SELECT appending_limit
FROM wsh_shipping_parameters
WHERE organization_id = c_organization_id;
l_select_criteria WSH_BATCH_PROCESS.Select_Criteria_Rec;
l_selected_det_tbl WSH_UTIL_CORE.Id_Tab_Type;
WSH_SELECT_ERR EXCEPTION;
l_selected_det_tbl_tmp WSH_UTIL_CORE.Id_Tab_Type;
g_selected_det_count number :=0;
WSH_UTIL_CORE.PrintMsg('Scheduled Ship Date parameters are not applicable for entity Deliveries Only. Please do not enter Sheduled Ship Date range when selecting entity Deliveries Only.');
FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DELIVERIES_SELECTED');
WSH_UTIL_CORE.PrintMsg(to_char(l_del_num)|| ' deliveries selected for processing');
WSH_DEBUG_SV.logmsg(l_module_name,'Calling Select_Deliveries_Lines ',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_UTIL_CORE.PrintMsg('Pick Up Date parameters are not applicable for entity Delivery Lines Only. Please do not enter Pick Up Date range when selecting entity Delivery Lines Only.');
WSH_UTIL_CORE.PrintMsg('Drop Off Date parameters are not applicable for entity Delivery Lines Only. Please do not enter Drop Off Date range when selecting entity Delivery Lines Only.');
l_select_criteria.delivery_lines_status := 'ALL';
l_select_criteria.delivery_lines_status := p_delivery_lines_status;
l_select_criteria.scheduled_ship_date_lo := p_scheduled_ship_date_lo;
l_select_criteria.scheduled_ship_date_hi := p_scheduled_ship_date_hi;
l_select_criteria.source_code := 'BOTH';
l_select_criteria.source_code := p_source_system;
l_select_criteria.organization_id := p_organization_id;
l_select_criteria.customer_id := p_customer_id;
l_select_criteria.ship_to_loc_id := p_ship_to_loc_id;
l_select_criteria.ship_method_code := p_ship_method_code;
l_select_criteria.log_level := 0;
l_select_criteria.log_level := p_log_level;
Select_Delivery_Lines(
p_select_criteria => l_select_criteria,
p_autocreate_deliveries => l_autocreate_deliveries,
x_selected_det_tbl => l_selected_det_tbl,
x_return_status => l_return_status);
WSH_DEBUG_SV.logmsg(l_module_name,'Return status from Select_Deliveries_Lines is '|| l_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
raise WSH_SELECT_ERR;
IF l_selected_det_tbl.count > 0 THEN
--Perf Bug 5215740
l_processed := 0;
l_selected_det_tbl_tmp(l_inner_loop_count) := l_selected_det_tbl(l_processed);
(l_processed = l_selected_det_tbl.count)) then
Exit inner_loop;
WSH_DEBUG_SV.logmsg(l_module_name, 'Delivery lines selected to be processed are: ');
FOR k in 1 .. l_selected_det_tbl_tmp.count LOOP
WSH_DEBUG_SV.logmsg(l_module_name, ' delivery lines: ' || l_selected_det_tbl_tmp(k));
p_selected_det_tbl => l_selected_det_tbl_tmp,
p_append_flag => l_append_deliveries,
p_ac_del_criteria => l_ac_del_criteria,
x_appended_det_num => l_appended_det_num,
x_autocreate_del_det_num => l_autocreate_del_det_num,
x_appended_del_num => l_appended_del_num,
x_new_del_num => l_new_del_num,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
g_selected_det_count := g_selected_det_count + l_selected_det_tbl_tmp.count;
WSH_DEBUG_SV.logmsg(l_module_name,'l_selected_det_tbl_tmp.count = '|| l_selected_det_tbl_tmp.count ,WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DEBUG_SV.logmsg(l_module_name,'g_selected_det_count = '|| g_selected_det_count ,WSH_DEBUG_SV.C_PROC_LEVEL);
exit when l_processed = l_selected_det_tbl.count;
l_selected_det_tbl_tmp.delete;
FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_SELECTED');
FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(g_selected_det_count));
WSH_UTIL_CORE.PrintMsg(to_char(g_selected_det_count)|| ' delivery lines selected for processing');
FND_MESSAGE.SET_NAME('WSH', 'WSH_PD_DETAILS_SELECTED');
FND_MESSAGE.SET_TOKEN('NUMBER_OF_DETAILS', to_char(l_selected_det_tbl.count));
WSH_UTIL_CORE.PrintMsg(to_char(l_selected_det_tbl.count)|| ' delivery lines selected for processing');
WHEN WSH_SELECT_ERR THEN
WSH_UTIL_CORE.PrintMsg('Error Messages: ' || l_msg_data);
WSH_UTIL_CORE.PrintMsg('Failed to select delivery lines for processing');