[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id, inventory_item_id, mo_start_line_number, mo_line_count,ROWID -- Bug # 9369504 : added rowid
FROM wsh_pr_workers
WHERE batch_id = l_batch_id
AND organization_id = l_organization_id
AND type = 'PICK'
AND processed = 'N'
AND l_mode = 'PICK'
UNION
SELECT organization_id, inventory_item_id, mo_start_line_number, mo_line_count,ROWID -- Bug # 9369504 : added rowid
FROM wsh_pr_workers
WHERE batch_id = l_batch_id
AND organization_id = l_organization_id
AND type = 'PICK'
AND processed = 'N'
AND inventory_item_id IS NULL
AND l_mode = 'PICK-SS'
ORDER BY 1, 2 DESC;
-- selected from cursors
v_count NUMBER;
v_last_update_date DATE; --Bug# 3248578
PROCEDURE Insert_RL_Row(
p_enforce_ship_set_and_smc IN VARCHAR2,
x_skip_detail OUT NOCOPY VARCHAR2, --Bug# 3248578
x_api_status OUT NOCOPY VARCHAR2
);
SELECT count(*)
INTO l_ss_count
FROM wsh_delivery_details
WHERE ship_set_id = p_ship_set_id
AND source_header_id = p_order_header_id
AND source_code = p_source_code
AND released_status IN ('R','N','B')
AND pickable_flag = 'Y';
WSH_DEBUG_SV.logmsg(l_module_name, 'THE DB COUNT FOR SS '||P_SHIP_SET_ID || ' DOES NOT MATCH WITH THE SELECTED RECORD COUNT');
release_table.delete(i);
SELECT count(*)
INTO l_smc_count
FROM wsh_delivery_details
WHERE top_model_line_id = p_top_model_line_id
AND source_header_id = p_order_header_id
AND ship_model_complete_flag = 'Y'
AND source_code = p_source_code
AND released_status IN ('R','N','B')
AND pickable_flag = 'Y';
WSH_DEBUG_SV.logmsg(l_module_name, 'THE DB COUNT FOR SMC '||P_TOP_MODEL_LINE_ID || ' DOES NOT MATCH WITH THE SELECTED RECORD COUNT');
release_table.delete(i);
CURSOR lock_for_update(v_del_detail_id IN NUMBER) IS
SELECT ROWID, LAST_UPDATE_DATE, --Bug# 3248578
-- Bug 4775539
REQUESTED_QUANTITY_UOM,
SRC_REQUESTED_QUANTITY_UOM,
SRC_REQUESTED_QUANTITY,
--muom
SRC_REQUESTED_QUANTITY2,
INVENTORY_ITEM_ID
FROM WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = v_del_detail_id
AND RELEASED_STATUS IN ('R','B','X')
AND MOVE_ORDER_LINE_ID IS NULL
FOR UPDATE NOWAIT;
SELECT wdd.SOURCE_LINE_ID, -- Start New Col Addition
wdd.SOURCE_HEADER_ID,
wdd.INVENTORY_ITEM_ID,
WDD.SHIPMENT_PRIORITY_CODE,
WDD.SOURCE_CODE SOURCE_CODE,
WDD.SOURCE_HEADER_NUMBER SOURCE_HEADER_NUMBER, -- End New Col Addition
wdd.organization_id,
wdd.move_order_line_id,
wdd.ship_from_location_id,
wdd.ship_method_code,
wdd.shipment_priority_code,
wdd.date_scheduled,
wdd.requested_quantity,
wdd.requested_quantity_uom,
wdd.preferred_grade,
wdd.requested_quantity2,
wdd.requested_quantity_uom2,
wdd.project_id,
wdd.task_id,
wdd.subinventory,
wdd.subinventory,
wdd.released_status,
wdd.ship_model_complete_flag,
wdd.top_model_line_id,
wdd.ship_set_id,
-- Standalone project Changes start
wdd.locator_id,
wdd.revision,
wdd.lot_number,
-- Standalone project Changes end
wda.delivery_id,
wdd.last_update_date,
wdd.client_id -- LSP PROJECT:
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda
WHERE wdd.delivery_detail_id = v_del_detail_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.date_scheduled IS NOT NULL
AND wdd.date_requested >= nvl(g_from_request_date, wdd.date_requested)
AND wdd.date_requested <= nvl(g_to_request_date, wdd.date_requested)
AND wdd.date_scheduled >= nvl(g_from_sched_ship_date, wdd.date_scheduled)
AND wdd.date_scheduled <= nvl(g_to_sched_ship_date, wdd.date_scheduled)
AND nvl(wdd.requested_quantity,0) > 0
AND wdd.released_status IN ('R','B','X')
-- bug 5166340: wdd subinventory needs to be compared
-- with g_RelSubInventory(subinventory field value)
-- not with g_from_subinventory(pick from subinventory field value)
-- AND nvl(wdd.subinventory, -99) = decode(g_from_subinventory, NULL,
-- nvl(wdd.subinventory, -99), g_from_subinventory)
AND nvl(wdd.subinventory, -99) = decode(g_RelSubInventory, NULL,
nvl(wdd.subinventory, -99), g_RelSubInventory)
AND nvl(wdd.project_id,0) = decode(g_project_id, 0, nvl(wdd.project_id,0), g_project_id)
AND nvl(wdd.task_id,0) = decode(g_task_id, 0, nvl(wdd.task_id,0), g_task_id)
AND nvl(wdd.ship_set_id,0) = decode(g_ship_set_number, 0, nvl(wdd.ship_set_id,0), g_ship_set_number)
AND nvl(wdd.shipment_priority_code,-99) = decode(g_shipment_priority, NULL,
nvl(wdd.shipment_priority_code,-99), g_shipment_priority)
AND wdd.organization_id = nvl(g_organization_id, wdd.organization_id)
AND wdd.ship_from_location_id = decode(g_ship_from_loc_id, -1, wdd.ship_from_location_id,
g_ship_from_loc_id)
AND (( wda.delivery_id IS NOT NULL AND ( g_include_planned_lines <> 'N' OR
wda.delivery_id = g_delivery_id OR
g_trip_id <> 0
)
) OR
( wda.delivery_id IS NULL AND g_delivery_id = 0 AND g_trip_id = 0 )
);
l_last_update_date DATE; --Bug# 3248578
OPEN lock_for_update(p_delivery_detail_id);
FETCH lock_for_update INTO l_rowid, l_last_update_date,
-- Bug 4775539
x_requested_qty_uom,x_src_requested_qty_uom,x_src_requested_qty,x_src_requested_qty2,x_inv_item_id; --muom
IF lock_for_update%NOTFOUND THEN
CLOSE lock_for_update;
WSH_DEBUG_SV.log(l_module_name,'l_last_update_date',l_last_update_date);
WSH_DEBUG_SV.log(l_module_name,'v_last_update_date',v_last_update_date);
IF l_last_update_date <> v_last_update_date THEN
-- Record has been changed during the Pick Release process
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'Record has been changed during the Pick Release process');
v_last_update_date,
v_client_id; -- LSP PROJECT :
IF lock_for_update%ISOPEN THEN
CLOSE lock_for_update;
WSH_DEBUG_SV.logmsg(l_module_name, 'CANNOT LOCK DELIVERY DETAIL FOR UPDATE: '|| P_DELIVERY_DETAIL_ID);
SELECT NAME,
BACKORDERS_ONLY_FLAG,
NVL(AUTODETAIL_PR_FLAG, NULL),
NVL(AUTO_PICK_CONFIRM_FLAG, NULL),
NVL(PICK_SEQUENCE_RULE_ID, ''),
NVL(PICK_GROUPING_RULE_ID, ''),
NVL(INCLUDE_PLANNED_LINES, 'N'),
NVL(ORGANIZATION_ID, ''),
NVL(CUSTOMER_ID, 0),
NVL(FROM_REQUESTED_DATE, NULL),
NVL(TO_REQUESTED_DATE, NULL),
NVL(EXISTING_RSVS_ONLY_FLAG, 'N'),
NVL(ORDER_HEADER_ID, 0),
NVL(INVENTORY_ITEM_ID, 0),
NVL(TRIP_ID, 0),
NVL(TRIP_STOP_ID, 0),
NVL(DELIVERY_ID, 0),
NVL(ORDER_TYPE_ID, 0),
NVL(FROM_SCHEDULED_SHIP_DATE, NULL),
NVL(TO_SCHEDULED_SHIP_DATE, NULL),
NVL(SHIPMENT_PRIORITY_CODE, ''),
NVL(SHIP_METHOD_CODE, ''),
NVL(SHIP_SET_NUMBER, 0),
NVL(DELIVERY_DETAIL_ID, 0),
NVL(SHIP_TO_LOCATION_ID, 0),
NVL(DEFAULT_STAGE_SUBINVENTORY, ''),
NVL(DEFAULT_STAGE_LOCATOR_ID,''),
NVL(PICK_FROM_SUBINVENTORY,''),
NVL(PICK_FROM_LOCATOR_ID,''),
NVL(TASK_ID,0),
NVL(PROJECT_ID,0),
NVL(SHIP_FROM_LOCATION_ID, -1),
NVL(AUTOCREATE_DELIVERY_FLAG, NULL),
SHIP_CONFIRM_RULE_ID,
NVL(AUTOPACK_FLAG, 'N'),
AUTOPACK_LEVEL,
NON_PICKING_FLAG,
NVL(ORDER_LINE_ID, 0),
NVL(DOCUMENT_SET_ID, '-1'),
TASK_PLANNING_FLAG,
--
-- rlanka : Pack J Enhancement
NVL(category_set_id, 0),
NVL(category_id, 0),
NVL(region_id, 0),
NVL(zone_id, 0),
NVL(ac_delivery_criteria, NULL),
NVL(rel_subinventory, NULL),
--
-- deliveryMerge
NVL(append_flag, NULL),
-- Bug #3266659 : Shipset/SMC Criteria
NVL(SHIP_SET_SMC_FLAG,'A'),
ACTUAL_DEPARTURE_DATE,
NVL(ALLOCATION_METHOD,'I'), --anxsharm, X-dock
CROSSDOCK_CRITERIA_ID , --anxsharm, X-dock
DYNAMIC_REPLENISHMENT_FLAG, --bug# 6689448 (replenishment project)
NVL(CLIENT_ID, 0) --LSP PROJECT
FROM WSH_PICKING_BATCHES
WHERE BATCH_ID = v_batch_id;
SELECT NAME,
BACKORDERS_ONLY_FLAG,
NVL(AUTODETAIL_PR_FLAG, NULL),
NVL(AUTO_PICK_CONFIRM_FLAG, NULL),
NVL(PICK_SEQUENCE_RULE_ID, ''),
NVL(PICK_GROUPING_RULE_ID, ''),
NVL(INCLUDE_PLANNED_LINES, 'N'),
NVL(ORGANIZATION_ID, ''),
NVL(CUSTOMER_ID, 0),
NVL(FROM_REQUESTED_DATE, NULL),
NVL(TO_REQUESTED_DATE, NULL),
NVL(EXISTING_RSVS_ONLY_FLAG, 'N'),
NVL(ORDER_HEADER_ID, 0),
NVL(INVENTORY_ITEM_ID, 0),
NVL(TRIP_ID, 0),
NVL(TRIP_STOP_ID, 0),
NVL(DELIVERY_ID, 0),
NVL(ORDER_TYPE_ID, 0),
NVL(FROM_SCHEDULED_SHIP_DATE, NULL),
NVL(TO_SCHEDULED_SHIP_DATE, NULL),
NVL(SHIPMENT_PRIORITY_CODE, ''),
NVL(SHIP_METHOD_CODE, ''),
NVL(SHIP_SET_NUMBER, 0),
NVL(DELIVERY_DETAIL_ID, 0),
NVL(SHIP_TO_LOCATION_ID, 0),
NVL(DEFAULT_STAGE_SUBINVENTORY, ''),
NVL(DEFAULT_STAGE_LOCATOR_ID,''),
NVL(PICK_FROM_SUBINVENTORY,''),
NVL(PICK_FROM_LOCATOR_ID,''),
NVL(TASK_ID,0),
NVL(PROJECT_ID,0),
NVL(SHIP_FROM_LOCATION_ID, -1),
NVL(AUTOCREATE_DELIVERY_FLAG, NULL),
SHIP_CONFIRM_RULE_ID,
NVL(AUTOPACK_FLAG, 'N'),
AUTOPACK_LEVEL,
NON_PICKING_FLAG,
NVL(ORDER_LINE_ID, 0),
NVL(DOCUMENT_SET_ID, '-1'),
TASK_PLANNING_FLAG,
--
-- rlanka : Pack J Enhancement
NVL(category_set_id, 0),
NVL(category_id, 0),
NVL(region_id, 0),
NVL(zone_id, 0),
NVL(ac_delivery_criteria, NULL),
NVL(rel_subinventory, NULL),
--
-- deliveryMerge
NVL(append_flag, NULL),
-- Bug #3266659 : Shipset/SMC Criteria
NVL(SHIP_SET_SMC_FLAG,'A'),
ACTUAL_DEPARTURE_DATE,
NVL(ALLOCATION_METHOD,'I'), --anxsharm, X-dock
CROSSDOCK_CRITERIA_ID, --anxsharm, X-dock
DYNAMIC_REPLENISHMENT_FLAG, --bug# 6689448 (replenishment project)
NVL(CLIENT_ID, 0)
FROM WSH_PICKING_BATCHES
WHERE BATCH_ID = v_batch_id
FOR UPDATE OF BATCH_ID NOWAIT;
SELECT HEADER_ID
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = v_line_id;
SELECT WDD.DELIVERY_DETAIL_ID
FROM wsh_delivery_assignments_v WDA,
WSH_DELIVERY_DETAILS WDD
WHERE WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG = 'N'
AND WDA.DELIVERY_ASSIGNMENT_ID IN
(SELECT WDA1.DELIVERY_ASSIGNMENT_ID
FROM wsh_delivery_assignments_v WDA1
START WITH WDA1.PARENT_DELIVERY_DETAIL_ID = v_del_detail_id
CONNECT BY PRIOR WDA1.DELIVERY_DETAIL_ID = WDA1.PARENT_DELIVERY_DETAIL_ID);
SELECT DELIVERY_DETAIL_ID -- If Launch PR from STF for del details, we stamp batch id
FROM WSH_DELIVERY_DETAILS -- on details. wsh_picking_batches has the detail as -1,
WHERE BATCH_ID = v_batch_id -- batch can have several delivery details
AND nvl(LINE_DIRECTION , 'O') IN ('O', 'IO')
AND CONTAINER_FLAG='Y'
UNION
SELECT DELIVERY_DETAIL_ID -- If use PR form then we do not stamp batch id in wdd,
FROM WSH_DELIVERY_DETAILS -- wsh_picking_batches has the delivery detail stamped
WHERE DELIVERY_DETAIL_ID = v_del_det -- we can specify only one detail per batch
AND nvl(LINE_DIRECTION , 'O') IN ('O', 'IO')
AND BATCH_ID IS NULL
AND CONTAINER_FLAG='Y';
-- BULK UPDATE ALL THE LINES WITH THE SAME BATCH ID.
--1) Get all the container item in the batch.
--2) For every container get the inner non container items
-- Stamp the non container items with same batch id
--3) If atleast one update then set g_del_detail_id = -1
-- g_del_detail_id used for building the dynamic SQL
IF g_del_detail_id <> 0 THEN
--{
--Cursor to get all the containers within the batch
OPEN get_batch_details(p_batch_id, g_del_detail_id);
UPDATE WSH_DELIVERY_DETAILS
SET BATCH_ID = p_batch_id
WHERE DELIVERY_DETAIL_ID = g_det_lst(i);
UPDATE WSH_DELIVERY_DETAILS
SET BATCH_ID = p_batch_id
WHERE DELIVERY_DETAIL_ID = g_del_detail_id;
-- Update picking batch setting request id and other who parameters
UPDATE WSH_PICKING_BATCHES
SET REQUEST_ID = g_request_id,
PROGRAM_APPLICATION_ID = g_application_id,
PROGRAM_ID = g_program_id,
PROGRAM_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = g_user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = g_login_id
WHERE BATCH_ID = p_batch_id
AND (REQUEST_ID IS NULL OR REQUEST_ID = g_request_id);
SELECT NAME,
NVL(ORDER_ID_PRIORITY, -1),
DECODE(ORDER_ID_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(INVOICE_VALUE_PRIORITY, -1),
DECODE(INVOICE_VALUE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(SCHEDULE_DATE_PRIORITY, -1),
DECODE(SCHEDULE_DATE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(SHIPMENT_PRI_PRIORITY, -1),
DECODE(SHIPMENT_PRI_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(TRIP_STOP_DATE_PRIORITY, -1),
DECODE(TRIP_STOP_DATE_SORT, 'A', 'ASC', 'D', 'DESC', '')
FROM WSH_PICK_SEQUENCE_RULES
WHERE PICK_SEQUENCE_RULE_ID = v_psr_id
AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)+1);
SELECT NVL(ORDER_NUMBER_FLAG,'N'), NVL(DELIVERY_FLAG,'N')
FROM WSH_PICK_GROUPING_RULES
WHERE PICK_GROUPING_RULE_ID = v_pgr_id
AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)+1);
-- of building the select statement
g_invoice_value_flag := 'Y';
-- Selection for unreleased lines, build the dynamic sql based on release batch parameters.
g_Unreleased_SQL := '';
'u', 'SELECT /*+ index(wdd WSH_DELIVERY_DETAILS_N9) */ ');
'u', 'SELECT ');
Process_Buffer(p_print_flag, 'u', ' WDD.LAST_UPDATE_DATE,'); --Bug# 3248578
/* muom: Deferred this check to Insert_RL_Row api
Process_Buffer(p_print_flag,
'u', ' AND NVL(WDD.REQUESTED_QUANTITY,0) > 0');
||'( SELECT DELIVERY_DETAIL_ID FROM '
||' WSH_DELIVERY_DETAILS WHERE BATCH_ID = :X_batch_id '
||' AND CONTAINER_FLAG = ''N'')'); -- ,p_batch_id);
'c', ' AND EXISTS ( SELECT 1 FROM WSH_NEW_DELIVERIES WDE1
WHERE WDE1.DELIVERY_ID = WDA.DELIVERY_ID
AND ( NVL(WDE1.PLANNED_FLAG,''N'') IN (''Y'',''F'' ) )) ))');
Process_Buffer(p_print_flag,'c', ' AND OTTT.LANGUAGE = (SELECT language_code FROM fnd_languages WHERE installed_flag = ''B'') ');
' AND WDD.ship_to_location_id IN (select location_id '
|| ' FROM wsh_region_locations WHERE region_id = :x_RegionID)'); -- ,g_RegionID);
' AND WDD.ship_to_location_id IN (select location_id '
|| ' FROM wsh_region_locations WHERE region_id IN '
|| ' (SELECT region_id FROM wsh_zone_regions WHERE '
|| ' parent_region_id = :x_ZoneID))'); -- ,g_ZoneID);
DBMS_SQL.Define_Column(v_CursorID, 31, v_last_update_date);
INSERT INTO WSH_PR_WORKERS (
batch_id,
type,
mo_header_id,
organization_id,
inventory_item_id,
mo_start_line_number,
mo_line_count,
processed
)
VALUES (
WSH_PICK_LIST.G_BATCH_ID,
'PICK',
p_mo_header_id,
v_org_id,
v_inventory_item_id,
l_count + 1,
v_count,
'N'
);
WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN CREATING SELECT STATEMENT');
release_table.delete;
SELECT 'Y'
INTO l_temp
FROM wsh_pr_workers
WHERE ROWID = v_rowid
AND processed = 'N'
FOR UPDATE NOWAIT;
UPDATE wsh_pr_workers
SET processed = 'Y'
WHERE ROWID= v_rowid;
DBMS_SQL.Column_Value(v_CursorID, 31, v_last_update_date);
Insert_RL_Row(p_enforce_ship_set_and_smc, l_skip_detail, g_return_status);
WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR OCCURRED IN INSERT_RL_ROW');
release_table.delete(g_rel_current_line - 1);
release_table.delete(g_rel_current_line - 1);
release_table.delete(g_rel_current_line - 1);
PROCEDURE Insert_RL_Row(
p_enforce_ship_set_and_smc IN VARCHAR2,
x_skip_detail OUT NOCOPY VARCHAR2, --Bug# 3248578
x_api_status OUT NOCOPY VARCHAR2
) IS
CURSOR c_order_line(v_line_id IN NUMBER) IS
SELECT ORDERED_QUANTITY,
ORDER_QUANTITY_UOM
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = v_line_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_RL_ROW';
WSH_DEBUG_SV.logmsg(l_module_name, 'INSERT INTO TABLE' );
WSH_DEBUG_SV.logmsg(l_module_name, 'UNEXPECTED ERROR IN INSERT_RL_ROW' );
END Insert_RL_Row;