[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT WRS.REPORT_SET_ID
INTO WSH_PICK_LIST.G_SEED_DOC_SET
FROM WSH_REPORT_SET_LINES WRSL,
WSH_REPORT_SETS WRS
WHERE WRS.NAME = 'Pick Slip Report'
AND WRS.REPORT_SET_ID = WRSL.REPORT_SET_ID ;
SELECT DECODE(MO_PICK_CONFIRM_REQUIRED, 2, 'Y', 'N')
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = c_org_id;
SELECT distinct printer_name,conc_program_name
FROM wsh_pr_workers
WHERE batch_id = p_batch_id
AND organization_id = p_organization_id
AND type = 'PRINTER';
SELECT source_header_number
FROM wsh_delivery_details
WHERE source_header_id = l_header_id
AND rownum = 1;
WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.delete;
l_null_ids.delete; -- ensure that the table is empty since no ids should be passed
fnd_msg_pub.delete_msg(); -- Clear Msg Buffer
SELECT wts.STOP_ID
FROM wsh_trips wt, wsh_trip_stops wts, wsh_delivery_assignments_v wda,
wsh_delivery_legs wdl, wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = v_dd_id
AND wdd.organization_id = v_org_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdl.delivery_id = wda.delivery_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wts.trip_id = wt.trip_id;
SELECT count (wdd.delivery_detail_id), delivery_id
FROM wsh_delivery_details wdd, wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id = ( SELECT delivery_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = c_delivery_detail_id )
AND wdd.released_status NOT IN ('R', 'X', 'N')
AND wdd.pickable_flag = 'Y'
AND wdd.container_flag = 'N'
GROUP BY delivery_id;
g_trolin_tbl.delete;
l_trolin_val_tbl.delete;
g_trolin_delivery_ids.delete;
g_del_detail_ids.delete;
l_rel_delivery_detail_id.delete;
g_xdock_delivery_ids.delete;
g_xdock_detail_ids.delete;
l_attr_tab.delete;
l_group_match_seq_tbl.delete;
g_trolin_tbl(l_count).last_updated_by := l_user_id;
g_trolin_tbl(l_count).last_update_date := l_date;
g_trolin_tbl(l_count).last_update_login := l_login_id;
select WSH_DELIVERY_GROUP_S.nextval into l_group_match_seq_tbl(i).delivery_group_id from dual;
g_trolin_tbl(l_count).operation := INV_GLOBALS.G_OPR_UPDATE;
UPDATE wsh_delivery_details
SET released_status = 'Y',
batch_id = p_batch_id,
last_updated_by = l_user_id,
last_update_date = l_date,
last_update_login = l_login_id
WHERE delivery_detail_id = l_rel_delivery_detail_id(i);
WSH_INTEGRATION.DBI_Update_Detail_Log (
p_delivery_detail_id_tab => l_rel_delivery_detail_id,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs );
WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_INTEGRATION.DBI_Update_Detail_Log');
WSH_DEBUG_SV.log(l_module_name,'l_count_x_relstatus_details updated ',l_count_x_relstatus_details);
fnd_msg_pub.delete_msg();
WSH_UTIL_CORE.PrintMsg('Delete Line '||g_trolin_tbl(i).line_id);
g_trolin_tbl.delete(i);
l_trolin_val_tbl.delete(i);
g_del_detail_ids.delete(i);
g_trolin_delivery_ids.delete(i);
fnd_msg_pub.delete_msg();
l_detail_tab.delete; -- DBI Project
WSH_DEBUG_SV.logmsg(l_module_name, 'UPDATE WITH TROLIN.LINE_ID ' || G_TROLIN_TBL(I).LINE_ID );
UPDATE wsh_delivery_details
SET move_order_line_id = g_trolin_tbl(i).line_id,
released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE,
batch_id = p_batch_id,
last_updated_by = l_user_id,
last_update_date = l_date,
last_update_login = l_login_id,
replenishment_status = NULL -- bug# 6719369 (replenishment project)
WHERE delivery_detail_id = g_del_detail_ids(i);
WSH_UTIL_CORE.PrintMsg('Delete Line '||g_trolin_tbl(i).line_id);
g_trolin_tbl.delete(i);
l_trolin_val_tbl.delete(i);
g_del_detail_ids.delete(i);
g_trolin_delivery_ids.delete(i);
WSH_UTIL_CORE.PrintMsg('Cannot update delivery detail: ' || WSH_PR_CRITERIA.release_table(i).delivery_detail_id);
WSH_INTEGRATION.DBI_Update_Detail_Log (
p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs );
WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_INTEGRATION.DBI_Update_Detail_Log');
SELECT ship_method_code, intmed_ship_to_location_id
FROM wsh_new_deliveries
WHERE delivery_id = c_dlvy_id;
l_appended_del_tbl.delete;
l_cc_line_groups.delete;
l_cc_group_info.delete;
l_cc_failed_records.delete;
l_id_tab_temp.delete;
l_id_tab_temp.delete;
UPDATE wsh_new_deliveries
SET intmed_ship_to_location_id = NULL
WHERE delivery_id = l_delivery_ids_tbl(i);
UPDATE wsh_new_deliveries
SET ship_method_code = NULL,
carrier_id = NULL,
mode_of_transport = NULL,
service_level = NULL
WHERE delivery_id = l_delivery_ids_tbl(i);
l_del_details_tbl.delete;
UPDATE wsh_new_deliveries
SET intmed_ship_to_location_id = NULL
WHERE delivery_id = l_delivery_ids_tbl(i);
UPDATE wsh_new_deliveries
SET ship_method_code = NULL,
carrier_id = NULL,
mode_of_transport = NULL,
service_level = NULL
WHERE delivery_id = l_delivery_ids_tbl(i);
l_del_details_tbl.delete;
SELECT DISTINCT ORGANIZATION_ID, MO_HEADER_ID
FROM WSH_PR_WORKERS
WHERE BATCH_ID = c_batch_id
AND PROCESSED = 'N'
ORDER BY ORGANIZATION_ID;
SELECT pa_sc_batch_id, delivery_id, organization_id, pickup_location_id, ap_level, sc_rule_id,ROWID -- Bug # 9369504 : added rowid
FROM wsh_pr_workers
WHERE batch_id = p_batch_id
AND type = 'PS'
ORDER BY 1,3,2;
SELECT ship_confirm_rule_id, creation_date
FROM wsh_picking_batches
WHERE batch_id = c_batch_id;
g_demand_table.delete;
WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.delete ;
WSH_INV_INTEGRATION_GRP.G_ORGTAB.delete ;
WSH_INV_INTEGRATION_GRP.G_ORGSUBTAB.delete ;
l_del_details_tbl.delete;
fnd_msg_pub.delete_msg();
g_trolin_tbl.DELETE(l_counter);
l_pick_release_stat.DELETE(i);
WSH_PR_PICK_SLIP_NUMBER.g_print_ps_table.DELETE;
fnd_msg_pub.delete_msg(); -- Clear Msg Buffer
fnd_msg_pub.delete_msg();
g_trolin_tbl.delete;
l_pick_release_stat.delete;
UPDATE WSH_PR_WORKERS
SET detailed_count = detailed_count + l_total_detailed_count
WHERE batch_id = WSH_PICK_LIST.G_BATCH_ID
AND organization_id = batch_rec.organization_id
AND type = 'DOC';
WSH_DEBUG_SV.log(l_module_name, 'No of rows updated for type DOC with detailed_count ' || l_total_detailed_count, sql%rowcount);
INSERT INTO WSH_PR_WORKERS (
batch_id,
type,
organization_id,
printer_name,
conc_program_name
)
SELECT
WSH_PICK_LIST.G_BATCH_ID,
'PRINTER',
batch_rec.organization_id,
WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i).printer_name,
WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i).conc_program_name
FROM dual
WHERE NOT EXISTS (
SELECT 'x'
FROM wsh_pr_workers
WHERE batch_id = WSH_PICK_LIST.G_BATCH_ID
AND type = 'PRINTER'
AND organization_id = batch_rec.organization_id
AND printer_name = WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i).printer_name
AND conc_program_name=WSH_INV_INTEGRATION_GRP.G_PRINTERTAB(i).conc_program_name);
SELECT 'x'
INTO l_dummy
FROM wsh_pr_workers
WHERE rowid = crec.rowid
AND processed = 'N'
FOR UPDATE NOWAIT;
UPDATE wsh_pr_workers
SET processed = 'Y'
WHERE rowid= crec.rowid;
SELECT DISTINCT WND.ORGANIZATION_ID
FROM WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_LEGS WLG,
WSH_TRIP_STOPS WTS
WHERE WTS.TRIP_ID = v_trip_id
AND WTS.STOP_ID = WLG.PICK_UP_STOP_ID
AND WLG.DELIVERY_ID = WND.DELIVERY_ID
AND nvl(WND.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
AND v_trip_stop_id = 0
AND v_delivery_id = 0
AND v_order_hdr_id = 0
-- Bug 11669468: Included -2 in IN clause
AND v_del_detail_id in (0,-1,-2)
AND (v_from_loc_id = -1
OR WND.ORGANIZATION_ID IN (SELECT organization_id
FROM hr_organization_units hr,
wsh_locations wl
WHERE wl.wsh_location_id = v_from_loc_id
AND wl.location_source_code = 'HR'
AND wl.source_location_id =
hr.location_id))
UNION ALL
SELECT DISTINCT WND.ORGANIZATION_ID
FROM WSH_NEW_DELIVERIES WND,
WSH_DELIVERY_LEGS WLG,
WSH_TRIP_STOPS WTS
WHERE WTS.STOP_ID = WLG.PICK_UP_STOP_ID
AND WLG.DELIVERY_ID = WND.DELIVERY_ID
AND WTS.STOP_ID = v_trip_stop_id
AND nvl(WND.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
AND v_delivery_id = 0
AND v_order_hdr_id = 0
-- Bug 11669468: Included -2 in IN clause
AND v_del_detail_id in (0,-1,-2)
UNION ALL
SELECT WND.ORGANIZATION_ID
FROM WSH_NEW_DELIVERIES WND
WHERE WND.DELIVERY_ID = v_delivery_id
AND nvl(WND.SHIPMENT_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
UNION ALL
SELECT DISTINCT ORGANIZATION_ID
FROM WSH_DELIVERY_DETAILS
WHERE SOURCE_CODE = 'OE'
AND RELEASED_STATUS IN ('R', 'B', 'X') -- Added For Bug-2722194 (Non-Assigned and
-- Ready to release Lines -> Base Bug-2687090)
AND SOURCE_HEADER_ID = v_order_hdr_id
-- Bug 11669468: Included -2 in IN clause
AND v_del_detail_id in (0,-1,-2)
AND v_delivery_id = 0
UNION ALL
SELECT DISTINCT ORGANIZATION_ID
FROM WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = v_del_detail_id
AND nvl(LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
AND v_delivery_id = 0
UNION ALL
SELECT DISTINCT WDD.ORGANIZATION_ID
FROM WSH_DELIVERY_DETAILS WDD
WHERE v_delivery_id = 0
AND nvl(WDD.LINE_DIRECTION , 'O') IN ('O', 'IO') -- J Inbound Logistics
-- Bug 11669468: Included -2 in IN clause
AND v_del_detail_id in (0, -1,-2)
AND WDD.BATCH_ID = v_batch_id;
SELECT wsp.ship_confirm_rule_id, wsp.autopack_level
FROM wsh_shipping_parameters wsp, wsh_delivery_details wdd
WHERE wdd.batch_id = v_batch_id
AND wsp.organization_id = wdd.organization_id
GROUP BY wsp.ship_confirm_rule_id, wsp.autopack_level;
SELECT DISTINCT wda.delivery_id, wdd.organization_id,
wdd.ship_from_location_id
FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd,
wsh_shipping_parameters wsp
WHERE wdd.batch_id = v_batch_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.organization_id = wsp.organization_id
AND NVL(wsp.ship_confirm_rule_id, 0) = NVL(v_sc_rule_id, 0)
AND NVL(wsp.autopack_level, 0) = NVL(v_ap_level, 0)
AND NVL(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO'); -- J Inbound Logistics
SELECT DISTINCT wda.delivery_id, wdd.organization_id,
wdd.ship_from_location_id
FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
WHERE wdd.batch_id = v_batch_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND NVL(wdd.LINE_DIRECTION , 'O') IN ('O', 'IO'); -- J Inbound Logistics
SELECT distinct id FROM wsh_tmp;
SELECT wdd.delivery_detail_id, wda.delivery_id, wda.parent_delivery_detail_id, wdd.organization_id,
wdd.line_direction, wdd.gross_weight, wdd.net_weight, wdd.volume,
wnd.planned_flag, wnd.batch_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd
WHERE wdd.batch_id = l_batch_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.released_status = 'B'
AND wdd.replenishment_status IS NULL -- replenishment2, select only back order delivery lines.
AND wda.delivery_id = wnd.delivery_id(+);
SELECT organization_id
FROM wsh_pr_workers
WHERE batch_id = l_batch_id
AND type = 'DOC';
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.batch_id = l_batch_id
AND wdd.released_status in ('S','Y')
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.organization_id = l_organization_id
AND wda.delivery_id IS NULL;
SELECT COUNT(*)
FROM wsh_pr_workers
WHERE batch_id = l_batch_id
AND type = 'PICK'
AND PROCESSED = 'N';
SELECT organization_id, mo_header_id, DETAILED_COUNT tot_detailed
FROM wsh_pr_workers
WHERE batch_id = l_batch_id
AND type = 'DOC'
ORDER BY organization_id;
SELECT DISTINCT wpr.pa_sc_batch_id, wscr.ac_defer_interface_flag
FROM wsh_pr_workers wpr,
wsh_ship_confirm_rules wscr
WHERE wpr.batch_id = l_batch_id
AND wpr.type = 'PS'
AND wpr.sc_rule_id = wscr.SHIP_CONFIRM_RULE_ID
AND NVL(EFFECTIVE_START_DATE, sysdate) <= sysdate
AND NVL(EFFECTIVE_END_DATE, sysdate ) >= sysdate;
SELECT DISTINCT wpr.batch_id, wpb.creation_date,
wscr.ac_close_trip_flag, wscr.ac_intransit_flag
FROM wsh_picking_batches wpb, wsh_ship_confirm_rules wscr, wsh_pr_workers wpr
WHERE wpr.batch_id = l_pickrel_batch_id
AND wpr.type = 'PS'
AND wpb.batch_id = wpr.pa_sc_batch_id
AND wpb.ship_confirm_rule_id = wscr.ship_confirm_rule_id
AND NVL(wscr.EFFECTIVE_START_DATE, sysdate) <= sysdate
AND NVL(wscr.EFFECTIVE_END_DATE, sysdate ) >= sysdate;
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 = c_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 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 = c_sc_batch_id and
wst.trip_id = wtp.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')
UNION (
SELECT wtp2.trip_id, wst2.stop_sequence_number,
wst2.stop_id, wst2.stop_location_id
FROM wsh_new_deliveries wnd2,
wsh_delivery_legs wlg2,
wsh_trip_stops wst2,
wsh_trips wtp2
WHERE wnd2.delivery_id = wlg2.delivery_id and
wlg2.drop_off_stop_id = wst2.stop_id and
wnd2.status_code = 'CO' and
wnd2.batch_id = c_sc_batch_id and
wst2.trip_id = wtp2.trip_id and
wst2.status_code = 'OP' AND
NOT EXISTS (
select '1' from wsh_exceptions we where
we.delivery_id = wnd2.delivery_id AND
we.severity = 'ERROR' AND
we.status = 'OPEN' AND
we.EXCEPTION_NAME = 'WSH_SC_REQ_EXPORT_COMPL'))
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 = l_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;
SELECT DISTINCT wda.delivery_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_new_deliveries wnd,
wsh_shipping_parameters wsp
WHERE wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.batch_id = l_batch_id
AND wdd.released_status = 'Y'
AND wda.delivery_id = wnd.delivery_id
AND wda.delivery_id IS NOT NULL
AND wnd.planned_flag = 'N'
AND wnd.organization_id = wsp.organization_id
AND NVL(wsp.appending_limit, 'N') <> 'N';
SELECT DISTINCT pa_sc_batch_id
FROM wsh_pr_workers
WHERE batch_id = l_batch_id
AND type = 'PS'
AND NVL(ap_level,0) > 0;
SELECT organization_id, mo_header_id
FROM wsh_pr_workers
WHERE batch_id = l_batch_id
AND type = 'WMS'
AND processed = 'N' --WMS High Vol Support, Added this AND cond and ORDER BY clause
ORDER BY organization_id;
SELECT wdd.delivery_detail_id, wdd.move_order_line_id
FROM wsh_delivery_details wdd,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE wdd.batch_id = l_batch_id
AND wdd.organization_id = l_organization_id
AND wdd.move_order_line_id IS NOT NULL
AND wdd.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id
AND mtrh.header_id = l_mo_header_id
AND mtrh.move_order_type <> 6
AND wdd.released_status = WSH_DELIVERY_DETAILS_PKG.C_RELEASED_TO_WAREHOUSE; -- auto pick confirm = 'N' for these lines
SELECT delivery_id
FROM wsh_new_deliveries
WHERE batch_id = l_batch_id
AND organization_id = p_organization_id;
SELECT initial_pickup_location_id
FROM wsh_new_deliveries
WHERE delivery_id = p_delivery_id;
SELECT request_id
FROM FND_CONCURRENT_REQUESTS
WHERE parent_request_id = p_parent_request_id
AND NVL(is_sub_request, 'N') = 'Y';
select NVL(wpgr.delivery_flag, 'N')
from wsh_pick_grouping_rules wpgr
where wpgr.pick_grouping_rule_id = p_psgr_id
and sysdate between trunc(nvl(wpgr.start_date_active, sysdate)) and
nvl(wpgr.end_date_active, trunc(sysdate)+1);
select wsp.organization_id
from wsh_shipping_parameters wsp, wsh_tmp wt
where wt.id = wsp.organization_id
and wsp.print_pick_slip_mode = 'I'
and rownum = 1;
select wsp.organization_id
from wsh_shipping_parameters wsp
where wsp.organization_id = p_org_id
and wsp.print_pick_slip_mode = 'I';
select wsp.organization_id
from wsh_shipping_parameters wsp, wsh_tmp wt
where wt.id = wsp.organization_id
and (wsp.print_pick_slip_mode = 'I'
OR exists
(select '1'
from wsh_pick_grouping_rules wpgr
where wpgr.pick_grouping_rule_id = wsp.pick_grouping_rule_id
and sysdate between trunc(nvl(wpgr.start_date_active, sysdate)) and
nvl(wpgr.end_date_active, trunc(sysdate)+1)
AND wpgr.delivery_flag = 'Y'))
AND rownum = 1;
select wsp.organization_id
from wsh_shipping_parameters wsp
where wsp.organization_id = p_org_id
and (wsp.print_pick_slip_mode = 'I'
OR exists
(select '1'
from wsh_pick_grouping_rules wpgr
where wpgr.pick_grouping_rule_id = wsp.pick_grouping_rule_id
and sysdate between trunc(nvl(wpgr.start_date_active, sysdate)) and
nvl(wpgr.end_date_active, trunc(sysdate)+1)
AND wpgr.delivery_flag = 'Y'));
SELECT DISTINCT organization_id
FROM wsh_delivery_details
WHERE released_status = 'B'
AND replenishment_status = 'R'
AND batch_id = c_batch_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PR_PICK_SLIP_NUMBER.DELETE_PS_TBL',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_PR_PICK_SLIP_NUMBER.Delete_ps_tbl (x_api_status => l_return_status,
x_error_message => l_message);
WSH_UTIL_CORE.PrintMsg('Error occurred in WSH_PR_PICK_SLIP_NUMBER.Delete_ps_tbl');
select count(*) into l_temp_num from wsh_Delivery_details where batch_id = p_batch_id;
select count(*) into l_temp_num from wsh_Delivery_details where batch_id = p_batch_id;
DELETE FROM wsh_tmp;
INSERT INTO wsh_tmp (id) VALUES(l_organization_tab(i));
DELETE FROM wsh_tmp;
l_organization_tab.delete;
WSH_INV_INTEGRATION_GRP.G_PRINTERTAB.delete ;
WSH_INV_INTEGRATION_GRP.G_ORGTAB.delete ;
WSH_INV_INTEGRATION_GRP.G_ORGSUBTAB.delete ;
l_trohdr_rec.last_updated_by := l_user_id;
l_trohdr_rec.last_update_date := l_date;
l_trohdr_rec.last_update_login := l_login_id;
fnd_msg_pub.delete_msg();
INSERT INTO WSH_PR_WORKERS (
BATCH_ID,
TYPE,
MO_HEADER_ID,
ORGANIZATION_ID,
PROCESSED)
VALUES (
p_batch_id,
'WMS',
l_trohdr_rec.header_id,
l_organization_id,
'N');
WSH_UTIL_CORE.PrintMsg('Error occurred in trying to insert WMS rec type in worker table');
INSERT INTO WSH_PR_WORKERS (
BATCH_ID,
TYPE,
MO_HEADER_ID,
MO_START_LINE_NUMBER,
ORGANIZATION_ID,
PROCESSED)
VALUES (
p_batch_id,
'PICK',
l_trohdr_rec.header_id,
1, -- initialize to 1 for move order line number
l_organization_id,
'N');
WSH_UTIL_CORE.PrintMsg('Error occurred in trying to insert PICK rec type in worker table');
p_mode => 'SUMMARY', -- insert in worker table
p_wms_org => l_org_info.wms_org,
p_mo_header_id => l_trohdr_rec.header_id,
p_inv_item_id => NULL,
p_enforce_ship_set_and_smc => l_org_info.enforce_ship_set_and_smc,
p_print_flag => l_print_cursor_flag,
p_express_pick => l_org_info.express_pick_flag,
p_batch_id => p_batch_id,
x_worker_count => l_worker_records,
x_smc_worker_count => l_smc_records,
x_dd_count => l_dd_records,
x_api_status => l_return_status);
INSERT INTO WSH_PR_WORKERS (
BATCH_ID,
TYPE,
MO_HEADER_ID,
ORGANIZATION_ID,
DETAILED_COUNT,
PROCESSED)
VALUES (
p_batch_id,
'DOC',
l_trohdr_rec.header_id,
l_organization_id,
0,
'N');
WSH_UTIL_CORE.PrintMsg('Error occurred in trying to insert DOC rec type in worker table');
l_del_details_tbl.delete;
l_del_ids_tab.DELETE;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERY_ACTIONS.PROCESS_CARRIER_SELECTION',WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_NEW_DELIVERY_ACTIONS.PROCESS_CARRIER_SELECTION (
p_delivery_id_tab => l_del_ids_tab,
p_batch_id => p_batch_id,
p_form_flag => 'N',
p_caller => 'WSH_PICK_RELEASE',
p_organization_id => i, -- Organization_id
x_return_message => l_message,
x_return_status => l_return_status );
WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR OR WARNING OCCURRED WHILE PROCESSING CARRIER SELECTION ( PICK SLIP ) ' );
l_del_details_tbl.delete;
l_mo_lines_tbl.delete;
l_carton_grouping_tbl.delete;
l_attr_tab.delete;
l_group_match_seq_tbl.delete;
select WSH_DELIVERY_GROUP_S.nextval into l_group_match_seq_tbl(i).delivery_group_id from dual;
UPDATE wsh_pr_workers
SET processed = 'Y'
WHERE batch_id = p_batch_id
AND organization_id = v_wms_org_rec.organization_id
AND type = 'WMS';
WSH_DEBUG_SV.logmsg(l_module_name, 'NO LINES SELECTED FOR ORG ' ||TO_CHAR(crec.organization_id));
WSH_DEBUG_SV.logmsg(l_module_name, 'DELETE MOVE ORDER HEADER' );
l_trohdr_rec.operation := INV_GLOBALS.G_OPR_DELETE;
l_trolin_tbl.delete;
l_trolin_val_tbl.delete;
fnd_msg_pub.delete_msg();
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_AUTOCREATE.Delete_Empty_Deliveries' ,WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DELIVERY_AUTOCREATE.Delete_Empty_Deliveries(g_batch_id,l_return_status);
WSH_DEBUG_SV.log(l_module_name,'WSH_DELIVERY_AUTOCREATE.Delete_Empty_Deliveries l_return_status',l_return_status);
UPDATE wsh_new_deliveries
SET batch_id = NULL
WHERE batch_id = g_batch_id;
UPDATE wsh_delivery_details
SET batch_id = NULL
WHERE batch_id = g_batch_id
AND ( (released_status in ('R','X'))
OR ( released_status = 'B' and nvl(REPLENISHMENT_STATUS,'C') <> 'R') );
WSH_UTIL_CORE.PrintMsg('Pick selection is completed');
errbuf := 'Pick selection completed successfully';
errbuf := 'Pick selection is completed with warning';
errbuf := 'Pick selection is completed with error';
WSH_DEBUG_SV.log(l_module_name,'BEFORE INSERTING l_ap_level', l_ap_level);
WSH_PICKING_BATCHES_PKG.Insert_Row (
X_Rowid => l_tmp_row_id,
X_Batch_Id => l_del_batch_id,
P_Creation_Date => NULL,
P_Created_By => NULL,
P_Last_Update_Date => NULL,
P_Last_Updated_By => NULL,
P_Last_Update_Login => NULL,
X_Name => l_del_batch_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 => NULL,
P_Customer_Id => NULL,
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 => NULL,
P_Ship_From_Location_Id => NULL,
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 => NULL,
P_Ship_Confirm_Rule_Id => l_sc_rule_id,
P_Autopack_Flag=> l_act_ap_flag,
P_Autopack_Level=> l_act_ap_level,
P_TASK_PLANNING_FLAG=> NULL,
P_Non_Picking_Flag => 'Y',
/* Enhancement */
p_RegionID => NULL,
p_ZoneID => NULL,
p_categoryID => NULL,
p_categorySetID => NULL,
p_acDelivCriteria => NULL,
p_RelSubinventory => NULL,
-- deliveryMerge
p_append_flag => 'N',
p_task_priority => NULL,
p_allocation_method => NULL,
p_crossdock_criteria_id => NULL,
p_client_id => NULL -- LSP PROJECT
);
INSERT INTO WSH_PR_WORKERS (
BATCH_ID,
TYPE,
PA_SC_BATCH_ID,
DELIVERY_ID,
ORGANIZATION_ID,
PICKUP_LOCATION_ID,
AP_LEVEL,
SC_RULE_ID,
PROCESSED
)
VALUES (
p_batch_id,
'PS',
l_del_batch_id,
l_ps_delivery_tab(i),
l_ps_org_tab(i),
l_ps_pick_loc_tab(i),
l_act_ap_level,
l_sc_rule_id,
'N'
);
UPDATE wsh_picking_batches
SET ship_confirm_rule_id = l_sc_rule_id,
autopack_flag = DECODE(l_act_ap_level,0,'N','Y'),
autopack_level = l_act_ap_level
WHERE batch_id = p_batch_id;
INSERT INTO WSH_PR_WORKERS (
BATCH_ID,
TYPE,
PA_SC_BATCH_ID,
DELIVERY_ID,
ORGANIZATION_ID,
PICKUP_LOCATION_ID,
AP_LEVEL,
SC_RULE_ID,
PROCESSED
)
VALUES (
p_batch_id,
'PS',
p_batch_id,
l_ps_delivery_tab(i),
l_ps_org_tab(i),
l_ps_pick_loc_tab(i),
l_act_ap_level,
l_sc_rule_id,
'N'
);
l_sc_id_tab.delete;
DELETE FROM wsh_pr_workers
WHERE batch_id = p_batch_id;
DELETE FROM wsh_pr_header_holds
WHERE batch_id = p_batch_id;
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PR_PICK_SLIP_NUMBER.delete_pick_slip_numbers' ,WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_PR_PICK_SLIP_NUMBER.Delete_Pick_Slip_Numbers ( p_batch_id => p_batch_id );
WSH_DEBUG_SV.logmsg(l_module_name,'Return from WSH_PR_PICK_SLIP_NUMBER.delete_pick_slip_numbers');
errbuf := 'Pick selection is completed with error';
errbuf := 'Pick selection is completed with warning';
SELECT name, --Added bug 7316707
organization_id,
sch_start_days,
sch_start_hours,
sch_end_days,
sch_end_hours,
req_start_days,
req_start_hours,
req_end_days,
req_end_hours,
from_scheduled_ship_date,
to_scheduled_ship_date,
from_requested_date,
to_requested_date,
backorders_only_flag,
document_set_id,
existing_rsvs_only_flag,
shipment_priority_code,
order_header_id,
order_type_id,
ship_from_location_id,
ship_method_code,
customer_id,
ship_to_location_id,
pick_from_subinventory,
pick_from_locator_id,
default_stage_subinventory,
default_stage_locator_id,
autodetail_pr_flag,
auto_pick_confirm_flag,
ship_set_number,
inventory_item_id,
pick_grouping_rule_id,
pick_sequence_rule_id,
project_id,
task_id,
include_planned_lines,
autocreate_delivery_flag,
ship_confirm_rule_id,
autopack_flag,
autopack_level,
task_planning_flag,
region_id,
zone_id,
category_id,
category_set_id,
ac_delivery_criteria,
rel_subinventory,
allocation_method, -- X-dock
crossdock_criteria_id, -- X-dock
append_flag, --Bug 8225893
task_priority,
client_id, -- LSP PROJECT
dynamic_replenishment_flag --Bug 10306215: Dynamic replenishment project missing code
FROM wsh_picking_rules
WHERE picking_rule_id = p_ruleID
AND sysdate BETWEEN NVL(start_date_active, SYSDATE) AND NVL(END_DATE_ACTIVE, SYSDATE+1);
SELECT calendar_code
FROM wsh_calendar_assignments
WHERE calendar_type = 'SHIPPING' AND
organization_id = p_orgID AND
enabled_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_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_batch_prefix,
X_Name => l_batch_name,
P_Backorders_Only_Flag => v_RuleInfo.BACKORDERS_ONLY_FLAG,
P_Document_Set_Id => v_RuleInfo.DOCUMENT_SET_ID,
P_Existing_Rsvs_Only_Flag => NVL(v_RuleInfo.EXISTING_RSVS_ONLY_FLAG, 'N'),
P_Shipment_Priority_Code => v_RuleInfo.SHIPMENT_PRIORITY_CODE,
P_Ship_Method_Code => v_RuleInfo.SHIP_METHOD_CODE,
P_Customer_Id => v_RuleInfo.CUSTOMER_ID,
P_Order_Header_Id => v_RuleInfo.ORDER_HEADER_ID,
P_Ship_Set_Number => v_RuleInfo.SHIP_SET_NUMBER,
P_Inventory_Item_Id => v_RuleInfo.INVENTORY_ITEM_ID,
P_Order_Type_Id => v_RuleInfo.ORDER_TYPE_ID,
P_From_Requested_Date => l_reqStartDate,
P_To_Requested_Date => l_reqEndDate,
P_From_Scheduled_Ship_Date => l_schStartDate,
P_To_Scheduled_Ship_Date => l_schEndDate,
P_Ship_To_Location_Id => v_RuleInfo.SHIP_TO_LOCATION_ID,
P_Ship_From_Location_Id => v_RuleInfo.SHIP_FROM_LOCATION_ID,
P_Trip_Id => NULL,
P_Delivery_Id => NULL,
P_Include_Planned_Lines => v_RuleInfo.INCLUDE_PLANNED_LINES,
P_Pick_Grouping_Rule_Id => v_RuleInfo.PICK_GROUPING_RULE_ID,
P_pick_sequence_rule_id => v_RuleInfo.PICK_SEQUENCE_RULE_ID,
P_Autocreate_Delivery_Flag => v_RuleInfo.AUTOCREATE_DELIVERY_FLAG,
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 => v_RuleInfo.AUTODETAIL_PR_FLAG,
P_Carrier_Id => NULL,
P_Trip_Stop_Id => NULL,
P_Default_stage_subinventory => v_RuleInfo.DEFAULT_STAGE_SUBINVENTORY,
P_Default_stage_locator_id => v_RuleInfo.DEFAULT_STAGE_LOCATOR_ID,
P_Pick_from_subinventory => v_RuleInfo.PICK_FROM_SUBINVENTORY,
P_Pick_from_locator_id => v_RuleInfo.PICK_FROM_LOCATOR_ID,
P_Auto_pick_confirm_flag => v_RuleInfo.AUTO_PICK_CONFIRM_FLAG,
P_Delivery_Detail_ID => NULL,
P_Project_ID => v_RuleInfo.PROJECT_ID,
P_Task_ID => v_RuleInfo.TASK_ID,
P_Organization_Id => v_RuleInfo.ORGANIZATION_ID,
P_Ship_Confirm_Rule_Id => v_RuleInfo.SHIP_CONFIRM_RULE_ID,
P_Autopack_Flag => v_RuleInfo.AUTOPACK_FLAG,
P_Autopack_Level => v_RuleInfo.AUTOPACK_LEVEL,
P_Task_Planning_Flag => v_RuleInfo.TASK_PLANNING_FLAG,
p_regionID => v_RuleInfo.REGION_ID,
p_zoneId => v_RuleInfo.ZONE_ID,
p_categoryID => v_RuleInfo.CATEGORY_ID,
p_categorySetID => v_RuleInfo.CATEGORY_SET_ID,
p_acDelivCriteria => v_RuleInfo.AC_DELIVERY_CRITERIA,
p_RelSubinventory => v_RuleInfo.REL_SUBINVENTORY,
p_actual_departure_date => l_actual_departure_date,
p_allocation_method => nvl(v_RuleInfo.ALLOCATION_METHOD,'I'),
p_crossdock_criteria_id => v_RuleInfo.CROSSDOCK_CRITERIA_ID,
p_append_flag => v_RuleInfo.APPEND_FLAG, --Bug 8225893,
p_task_priority => v_RuleInfo.TASK_PRIORITY,
p_client_id => v_RuleInfo.CLIENT_ID, -- LSP PROJECT
p_dynamic_replenishment_flag => v_RuleInfo.DYNAMIC_REPLENISHMENT_FLAG); --Bug 10306215
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');
errbuf := 'Pick Selection List Generation SRS completed with warning';
SELECT container_name, container_flag , organization_id
FROM WSH_DELIVERY_DETAILS
WHERE delivery_detail_id = l_del_detail_id;
select status_code from wsh_trips
where trip_id = c_trip_id;
select status_code from wsh_trip_stops
where stop_id = c_stop_id;
select status_code, organization_id from wsh_new_deliveries
where delivery_id = c_delivery_id;
select released_status from wsh_delivery_details
where delivery_detail_id = c_delivery_detail_id;
select distinct p.organization_id
from wsh_delivery_details d, wsh_shipping_parameters p
where d.organization_id = p.organization_id
and NVL(p.autopack_level,0) = 0
and d.batch_id = c_batch_id;
select distinct p.organization_id, p.ship_confirm_rule_id,
p.autocreate_deliveries_flag, m.mo_pick_confirm_required
from wsh_delivery_details d, wsh_shipping_parameters p, mtl_parameters m
where d.organization_id = p.organization_id and
m.organization_id = d.organization_id
and (ship_confirm_rule_id IS NULL
or NVL(p.autocreate_deliveries_flag, 'N') <> 'Y'
or NVL(m.mo_pick_confirm_required,0) <> 2)
and d.batch_id = c_batch_id;
select 1 from wsh_delivery_assignments_v a, wsh_delivery_details d
where d.organization_id = c_org_id
and d.batch_id = c_batch_id
and d.delivery_detail_id = a.delivery_detail_id
and a.delivery_id is null
and rownum = 1;
select mo_pick_confirm_required
from mtl_parameters
where organization_id = c_org_id;
SELECT 1
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE wda.delivery_detail_id = p_detail_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NULL
AND NVL(wdd.ignore_for_planning, 'N') = 'N';
SELECT
Document_Set_Id,
Include_Planned_Lines,
Pick_Grouping_Rule_Id,
Pick_Sequence_Rule_Id,
Autocreate_Delivery_Flag,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Autodetail_Pr_Flag,
Default_Stage_Subinventory,
Default_Stage_Locator_Id,
Pick_From_Subinventory,
Pick_From_locator_Id,
Auto_Pick_Confirm_Flag,
Organization_Id,
Ship_Confirm_Rule_Id,
Autopack_Flag,
Autopack_Level,
Task_Planning_Flag,
ac_Delivery_Criteria,
append_flag,
task_priority,
actual_departure_date,
allocation_method,
crossdock_criteria_id,
Non_Picking_Flag,
dynamic_replenishment_flag,
Order_Header_Id,
Order_Type_Id,
Existing_Rsvs_Only_Flag,
client_id -- LSP PROJECT
FROM wsh_picking_batches
WHERE batch_id = x_batch_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_id,
P_Creation_Date => NULL,
P_Created_By => NULL,
P_Last_Update_Date => NULL,
P_Last_Updated_By => NULL,
P_Last_Update_Login => NULL,
X_Name => l_batch_name,
P_Backorders_Only_Flag => 'M', -- consider only replenishment completed delivery detail lines.
P_Document_Set_Id => l_batch_rec.Document_Set_Id,
P_Existing_Rsvs_Only_Flag => l_batch_rec.Existing_Rsvs_Only_Flag,
P_Shipment_Priority_Code => NULL,
P_Ship_Method_Code => NULL,
P_Customer_Id => NULL,
P_Order_Header_Id => l_batch_rec.Order_Header_Id,
P_Ship_Set_Number => NULL,
P_Inventory_Item_Id => NULL,
P_Order_Type_Id => l_batch_rec.Order_Type_Id,
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 => NULL,
P_Ship_From_Location_Id => NULL,
P_Trip_Id => NULL,
P_Delivery_Id => NULL,
P_Include_Planned_Lines => 'Y', -- Bug 6908504 (replenishment
-- project): Replenishment Completed dd's may be associated to deliveries.
P_Pick_Grouping_Rule_Id => l_batch_rec.Pick_Grouping_Rule_Id,
P_pick_sequence_rule_id => l_batch_rec.pick_sequence_rule_id,
P_Autocreate_Delivery_Flag => l_batch_rec.Autocreate_Delivery_Flag,
P_Attribute_Category => l_batch_rec.Attribute_Category,
P_Attribute1 => l_batch_rec.Attribute1,
P_Attribute2 => l_batch_rec.Attribute2,
P_Attribute3 => l_batch_rec.Attribute3,
P_Attribute4 => l_batch_rec.Attribute4,
P_Attribute5 => l_batch_rec.Attribute5,
P_Attribute6 => l_batch_rec.Attribute6,
P_Attribute7 => l_batch_rec.Attribute7,
P_Attribute8 => l_batch_rec.Attribute8,
P_Attribute9 => l_batch_rec.Attribute9,
P_Attribute10 => l_batch_rec.Attribute10,
P_Attribute11 => l_batch_rec.Attribute11,
P_Attribute12 => l_batch_rec.Attribute12,
P_Attribute13 => l_batch_rec.Attribute13,
P_Attribute14 => l_batch_rec.Attribute14,
P_Attribute15 => l_batch_rec.Attribute15,
P_Autodetail_Pr_Flag => l_batch_rec.Autodetail_Pr_Flag,
P_Carrier_Id => NULL,
P_Trip_Stop_Id => NULL,
P_Default_stage_subinventory => l_batch_rec.Default_stage_subinventory,
P_Default_stage_locator_id => l_batch_rec.Default_stage_locator_id,
P_Pick_from_subinventory => l_batch_rec.Pick_from_subinventory,
P_Pick_from_locator_id => l_batch_rec.Pick_from_locator_id ,
P_Auto_pick_confirm_flag => l_batch_rec.Auto_pick_confirm_flag,
P_Delivery_Detail_ID => -1,
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 => l_batch_rec.autopack_flag,
P_Autopack_Level => l_batch_rec.autopack_level,
P_TASK_PLANNING_FLAG => l_batch_rec.task_planning_flag,
P_Non_Picking_Flag => l_batch_rec.non_picking_flag,
/* Enhancement */
p_RegionID => NULL,
p_ZoneID => NULL,
p_categoryID => NULL,
p_categorySetID => NULL,
p_acDelivCriteria => l_batch_rec.ac_Delivery_Criteria,
p_RelSubinventory => NULL,
-- deliveryMerge
p_append_flag => l_batch_rec.append_flag,
p_task_priority => l_batch_rec.task_priority,
p_actual_departure_date => l_batch_rec.actual_departure_date,
p_allocation_method => l_batch_rec.allocation_method,
p_crossdock_criteria_id => l_batch_rec.crossdock_criteria_id,
P_dynamic_replenishment_flag => l_batch_rec.dynamic_replenishment_flag,
p_selected_batch_id => p_batch_id, --Bug# 8492625 :Need to store the original picking batch id value.
p_client_id => l_batch_rec.client_id -- LSP PROJECT
);
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_id,
P_Creation_Date => NULL,
P_Created_By => NULL,
P_Last_Update_Date => NULL,
P_Last_Updated_By => NULL,
P_Last_Update_Login => NULL,
X_Name => l_batch_name,
P_Backorders_Only_Flag => 'I',
P_Document_Set_Id => NULL,
P_Existing_Rsvs_Only_Flag => NULL,
P_Shipment_Priority_Code => NULL,
P_Ship_Method_Code => NULL,
P_Customer_Id => NULL,
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 => NULL,
P_Ship_From_Location_Id => NULL,
P_Trip_Id => l_trip_id,
P_Delivery_Id => l_delivery_id,
P_Include_Planned_Lines => 'Y',
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 => l_stop_id,
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 => l_detail_id,
P_Project_ID => NULL,
P_Task_ID => NULL,
P_Organization_Id => NULL,
P_Ship_Confirm_Rule_Id => l_ship_confirm_rule_id,
P_Autopack_Flag => l_autopack_flag,
P_Autopack_Level => l_autopack_level,
P_TASK_PLANNING_FLAG => l_task_planning_flag,
P_Non_Picking_Flag => l_Non_Picking_Flag,
/* Enhancement */
p_RegionID => NULL,
p_ZoneID => NULL,
p_categoryID => NULL,
p_categorySetID => NULL,
p_acDelivCriteria => NULL,
p_RelSubinventory => NULL,
-- deliveryMerge
p_append_flag => 'N',
p_task_priority => NULL,
p_actual_departure_date => NULL,
p_allocation_method => 'I', --X-dock
p_crossdock_criteria_id => NULL, -- X-dock
p_client_id => NULL -- LSP PROJECT
);
update wsh_delivery_details
set batch_id = l_batch_id
where released_status in ('R', 'B', 'X')
and nvl(replenishment_status,'C') = 'C' --bug# 6719369 (replenishment project)
and delivery_detail_id = p_detail_ids(i);
SELECT SUM(mr.primary_reservation_quantity - nvl(mr.detailed_quantity,0)),
SUM(mr.SECONDARY_RESERVATION_QUANTITY - nvl(mr.SECONDARY_DETAILED_QUANTITY,0))
FROM mtl_reservations mr
WHERE mr.demand_source_header_id = c_demand_source_header_id
AND mr.demand_source_line_id = c_demand_source_line_id
AND nvl(mr.subinventory_code, nvl(WSH_PR_CRITERIA.g_from_subinventory,'-99')) = nvl(WSH_PR_CRITERIA.g_from_subinventory,nvl(mr.subinventory_code,'-99'))
AND nvl(mr.locator_id, nvl(WSH_PR_CRITERIA.g_from_locator,'-99')) = nvl(WSH_PR_CRITERIA.g_from_locator,nvl(mr.locator_id,-99))
AND nvl(mr.staged_flag, 'N') <> 'Y'
AND mr.primary_reservation_quantity - nvl(mr.detailed_quantity,0) > 0
AND mr.demand_source_type_id IN (2,8) -- Bug 4046748
AND mr.supply_source_type_id = 13; -- Bug 4046748;
SELECT SUM(mr.primary_reservation_quantity - nvl(mr.detailed_quantity,0)),
SUM(mr.SECONDARY_RESERVATION_QUANTITY - nvl(mr.SECONDARY_DETAILED_QUANTITY,0))
FROM mtl_reservations mr
WHERE mr.demand_source_header_id = c_demand_source_header_id
AND mr.demand_source_line_id = c_demand_source_line_id
AND ((nvl(mr.staged_flag, 'N') <> 'Y'
AND (nvl(mr.subinventory_code, nvl(WSH_PR_CRITERIA.g_from_subinventory,'-99')) <> nvl(WSH_PR_CRITERIA.g_from_subinventory,nvl(mr.subinventory_code,'-99'))
OR nvl(mr.locator_id, nvl(WSH_PR_CRITERIA.g_from_locator,-99)) <> nvl(WSH_PR_CRITERIA.g_from_locator,nvl(mr.locator_id,-99))))
OR (nvl(mr.staged_flag, 'N') = 'Y'))
AND mr.primary_reservation_quantity - nvl(mr.detailed_quantity,0) > 0
AND mr.demand_source_type_id IN (2,8)
AND mr.supply_source_type_id = 13;
SELECT sum(wdd.requested_quantity)
-- muom
,sum(wdd.requested_quantity2)
INTO l_unallocated_quantity
-- muom
,l_unallocated_quantity2
FROM wsh_delivery_details wdd
WHERE wdd.released_status = 'S'
AND wdd.source_code = 'OE'
AND wdd.source_line_id = p_demand_source_line_id
AND NOT EXISTS
( SELECT 'X'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = wdd.move_order_line_id );
SELECT organization_code
FROM
ORG_ORGANIZATION_DEFINITIONS
WHERE organization_id = org_id;