[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(subinventory_type,1)
INTO l_sub_type
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT count(ROWID)
INTO l_rec_count
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP;
SELECT count(ROWID)
INTO l_rec_count
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type = G_ROW_TP_ALL_TASK;
SELECT count(ROWID)
INTO l_rec_count
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type = G_ROW_TP_GROUP_TASK;
SELECT count(ROWID)
INTO l_rec_count
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type = G_ROW_TP_LPN_TASK;
SELECT wlpn.lpn_id,NVL(wlpn.parent_lpn_id,0),NVL(mmtt.lpn_id,0),NVL(mmtt.content_lpn_id,0),mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt ,
wms_license_plate_numbers wlpn
WHERE wlpn.organization_id = p_org_id
AND wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.outermost_lpn_id <> wlpn.lpn_id
AND mmtt.organization_id = p_org_id
AND mmtt.wms_task_type <> WMS_GLOBALS.g_wms_task_type_putaway
AND ( mmtt.content_lpn_id= wlpn.lpn_id OR mmtt.lpn_id = wlpn.lpn_id );
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.lpn_id=null
WHERE mmtt.transaction_temp_id = l_temp_id
AND mmtt.lpn_id = l_par_lpn_id
AND mmtt.content_lpn_id = l_lpn_id;
* The rows that has to be deleted depends on the i/p parameter
* p_del_type
*
* p_del_type Action
* ------------------------------------------------------------
* 1 -- Delete tasks belonging to the group which is passed as input.
* 2 -- Delete dummy rows which has been inserted for LPNs without contents
* 3 -- Deletes all the records in the temp table.
* 4 -- Delete processed tasks belonging to the group which is passed as input.
* This Funciton will delete both the group_tasks and all_task
* rows.
* It will return the number of rows deleted
* -1 in case of failure.
* @param p_group_id Group ID for which the records has to be deleted
* @ RETURN NUMBER
**/
FUNCTION Remove_Tasks_In_Group(
p_del_type IN NUMBER
,p_group_id IN NUMBER )
RETURN NUMBER
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
SELECT ROWID
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type = G_ROW_TP_LPN_TASK;
DELETE FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE group_id = p_group_id;
DELETE FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE ROWID = l_rowid_tab(i);
DELETE FROM WMS_PUTAWAY_GROUP_TASKS_GTMP;
DELETE FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE group_id = p_group_id
AND (PROCESS_FLAG = 'Y' OR ROW_TYPE = G_ROW_TP_GROUP_TASK);
* table WMS_PUTAWAY_GROUP_TASKS_GTMP and insert the newly created tasks
* during a partial putaway
* @param p_group_id Group ID for which the records has to be deleted
**/
-- Added for Bug 8827145
PROCEDURE Update_Tasks_In_Group(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_rec_count OUT NOCOPY NUMBER
, p_organization_id IN NUMBER
, p_drop_type IN VARCHAR2
, p_lpn_id IN NUMBER
, p_group_id IN NUMBER
, p_emp_id IN NUMBER)
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
l_proc_name VARCHAR2(30) := 'Update_Tasks_In_Group:';
SAVEPOINT update_gtmp_sp;
FOR gtmp_rec IN (SELECT DISTINCT
mmtt.organization_id,
mmtt.transaction_temp_id,
mmtt.transaction_header_id,
mtrl.lpn_id,
mmtt.cartonization_id, --INTO LPN ID
mmtt.inventory_item_id,
msik.concatenated_segments item, --Item
mtrl.lot_number,
mmtt.revision,
mmtt.transaction_quantity,
mmtt.transaction_uom,
inv_project.get_locsegs (mmtt.locator_id, mmtt.organization_id ) LOCATOR,
msik.primary_uom_code,
NVL(mmtt.transfer_subinventory,mmtt.subinventory_code) dest_subinventory,
NVL(mmtt.transfer_to_location, mmtt.locator_id) dest_locator,
NVL (msik.revision_qty_control_code, 1) revision_qty_control_code,
NVL (msik.lot_control_code, 1) lot_control_code,
NVL (msik.serial_number_control_code, 1) serial_number_control_code,
NVL (msik.restrict_subinventories_code, 2) restrict_subinventories_code,
NVL (msik.restrict_locators_code, 2) restrict_locators_code,
NVL (msik.location_control_code, 1) location_control_code,
NVL (msik.allowed_units_lookup_code, 2) allowed_units_lookup_code,
NVL (mtrl.backorder_delivery_detail_id, 0) backorder_delivery_detail_id,
NVL (mtrl.crossdock_type, 0) crossdock_type,
NVL (mmtt.wip_supply_type, 0) wip_supply_type,
mmtt.subinventory_code from_subinventory,
mmtt.locator_id from_locator,
mmtt.transfer_subinventory,
mmtt.transfer_to_location,
mmtt.transfer_organization,
mmtt.transaction_action_id,
mtrl.REFERENCE,
mtrl.line_id,
mtrl.project_id,
mtrl.task_id,
mtrl.txn_source_id,
mmtt.primary_quantity,
mmtt.secondary_transaction_quantity, -- 9037915
mmtt.secondary_uom_code -- 9037915
FROM mtl_material_transactions_temp mmtt
,mtl_txn_request_lines mtrl
,wms_dispatched_tasks wdt
--,mtl_item_locations milk
,mtl_system_items_kfv msik
,mtl_txn_request_headers mtrh
WHERE wdt.organization_id = p_organization_id
AND wdt.person_id = Decode(p_drop_type, g_dt_drop_all,p_emp_id,wdt.person_id)
AND wdt.status = 4
AND wdt.task_type = 2
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id
AND NVL (mmtt.wms_task_type, 0) <> -1
AND mtrl.lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id)
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = 6
AND mmtt.organization_id = msik.organization_id
AND mmtt.inventory_item_id = msik.inventory_item_id
AND mmtt.transaction_temp_id NOT IN (SELECT TRANSACTION_TEMP_ID FROM WMS_PUTAWAY_GROUP_TASKS_GTMP)) LOOP
IF (l_debug = 1) THEN
DEBUG('Calling activate_operation_instance with ...',l_proc_name,9);
INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
(
ORGANIZATION_ID
,TRANSACTION_TEMP_ID
,TRANSACTION_HEADER_ID
,LPN_ID
,LPN_NAME
,LPN_CONTEXT
,PARENT_LPN_ID
,PARENT_LPN_NAME
,OUTERMOST_LPN_ID
,OUTERMOST_LPN_NAME
,CONSOLIDATED_LPN_ID
,CONSOLIDATED_LPN_NAME
,INTO_LPN_ID
,INTO_LPN_NAME
,DROP_TYPE
,DROP_ORDER
,INVENTORY_ITEM_ID
,ITEM
,GROUP_ID
,LOT_NUMBER
,REVISION
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,LOCATOR
,PRIMARY_UOM_CODE
,DEST_SUBINVENTORY
,DEST_LOCATOR
,REVISION_QTY_CONTROL_CODE
,LOT_CONTROL_CODE
,SERIAL_NUMBER_CONTROL_CODE
,RESTRICT_SUBINVENTORIES_CODE
,RESTRICT_LOCATORS_CODE
,LOCATION_CONTROL_CODE
,ALLOWED_UNITS_LOOKUP_CODE
,BACKORDER_DELIVERY_DETAIL
,CROSSDOCK_TYPE
,WIP_SUPPLY_TYPE
,FROM_SUBINVENTORY
,FROM_LOCATOR
,TRANSFER_SUBINVENTORY
,TRANSFER_TO_LOCATION
,TRANSFER_ORGANIZATION
,TRANSACTION_ACTION_ID
,REFERENCE
,LOC_DROPPING_ORDER
,SUB_DROPPING_ORDER
,ROW_TYPE
,MOVE_ORDER_LINE_ID
,PROJECT_ID
,TASK_ID
,TXN_SOURCE_ID
,PRIMARY_QUANTITY
,LPN_LEVEL
,SECONDARY_QUANTITY -- 9037915
,SECONDARY_UOM -- 9037915
)
SELECT DISTINCT
gtmp_rec.organization_id,
gtmp_rec.transaction_temp_id,
gtmp_rec.transaction_header_id,
gtmp_rec.lpn_id,
wln.license_plate_number, --lpn_name
wln.lpn_context,
wln.parent_lpn_id,
NULL,
wln.outermost_lpn_id, --Outermost LPN ID
(SELECT license_plate_number FROM wms_license_plate_numbers WHERE lpn_id=wln.outermost_lpn_id), --Outermost LPN Name
TO_NUMBER(NULL), --Consolidated LPN ID
NULL, --Consolidated LPN Name
gtmp_rec.cartonization_id, --INTO LPN ID
NULL, --INTO LPN Name
G_DT_ITEM_DROP, --Drop Type
p_group_id, --Drop Order
gtmp_rec.inventory_item_id,
gtmp_rec.item, --Item
p_group_id, --Group ID
gtmp_rec.lot_number,
gtmp_rec.revision,
gtmp_rec.transaction_quantity,
gtmp_rec.transaction_uom,
gtmp_rec.LOCATOR, --inv_project.get_locsegs (milk.inventory_location_id, milk.organization_id ) LOCATOR,
gtmp_rec.primary_uom_code,
gtmp_rec.dest_subinventory,
gtmp_rec.dest_locator,
gtmp_rec.revision_qty_control_code,
gtmp_rec.lot_control_code,
gtmp_rec.serial_number_control_code,
gtmp_rec.restrict_subinventories_code,
gtmp_rec.restrict_locators_code,
gtmp_rec.location_control_code,
gtmp_rec.allowed_units_lookup_code,
gtmp_rec.backorder_delivery_detail_id,
gtmp_rec.crossdock_type,
gtmp_rec.wip_supply_type,
gtmp_rec.from_subinventory,
gtmp_rec.from_locator,
gtmp_rec.transfer_subinventory,
gtmp_rec.transfer_to_location,
gtmp_rec.transfer_organization,
gtmp_rec.transaction_action_id,
gtmp_rec.REFERENCE,
to_number(null), -- milk.dropping_order,
to_number(NULL), -- msi.dropping_order,
G_ROW_TP_ALL_TASK,
gtmp_rec.line_id,
gtmp_rec.project_id,
gtmp_rec.task_id,
gtmp_rec.txn_source_id,
gtmp_rec.primary_quantity,
(SELECT level
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND lpn_id=gtmp_rec.lpn_id
AND ROWNUM=1
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id),
gtmp_rec.secondary_transaction_quantity, -- 9037915
gtmp_rec.secondary_uom_code -- 9037915
FROM wms_license_plate_numbers wln
WHERE wln.organization_id = p_organization_id
AND wln.lpn_id = gtmp_rec.lpn_id;
FOR grp_rec IN (SELECT MIN(ROWID) row_id
,MIN(drop_order) drop_order
,SUM(transaction_quantity) transaction_quantity
,SUM(primary_quantity) primary_quantity
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type = G_ROW_TP_ALL_TASK
AND drop_type = G_DT_ITEM_DROP
AND group_id=p_group_id
AND NOT (crossdock_type > 1
AND backorder_delivery_detail > 0
AND wip_supply_type = 1
)
GROUP BY lpn_id
,inventory_item_id
,transaction_uom
,revision
,lot_number
,dest_locator
,into_lpn_id) LOOP
INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
(
ORGANIZATION_ID
,TRANSACTION_TEMP_ID
,TRANSACTION_HEADER_ID
,LPN_ID
,LPN_NAME
,LPN_CONTEXT
,PARENT_LPN_ID
,PARENT_LPN_NAME
,OUTERMOST_LPN_ID
,OUTERMOST_LPN_NAME
,CONSOLIDATED_LPN_ID
,CONSOLIDATED_LPN_NAME
,INTO_LPN_ID
,INTO_LPN_NAME
,DROP_TYPE
,DROP_ORDER
,INVENTORY_ITEM_ID
,ITEM
,GROUP_ID
,LOT_NUMBER
,REVISION
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,LOCATOR
,PRIMARY_UOM_CODE
,DEST_SUBINVENTORY
,DEST_LOCATOR
,REVISION_QTY_CONTROL_CODE
,LOT_CONTROL_CODE
,SERIAL_NUMBER_CONTROL_CODE
,RESTRICT_SUBINVENTORIES_CODE
,RESTRICT_LOCATORS_CODE
,LOCATION_CONTROL_CODE
,ALLOWED_UNITS_LOOKUP_CODE
,BACKORDER_DELIVERY_DETAIL
,CROSSDOCK_TYPE
,WIP_SUPPLY_TYPE
,FROM_SUBINVENTORY
,FROM_LOCATOR
,TRANSFER_SUBINVENTORY
,TRANSFER_TO_LOCATION
,TRANSFER_ORGANIZATION
,TRANSACTION_ACTION_ID
,REFERENCE
,LOC_DROPPING_ORDER
,SUB_DROPPING_ORDER
,ROW_TYPE
,MOVE_ORDER_LINE_ID
,PROJECT_ID
,TASK_ID
,TXN_SOURCE_ID
,PRIMARY_QUANTITY
,PROCESS_FLAG
,WIP_JOB
,WIP_LINE
,WIP_DEPT
,WIP_OP_SEQ
,WIP_ENTITY_TYPE
,WIP_START_DATE
,WIP_SCHEDULE
,WIP_ASSEMBLY
,SECONDARY_QUANTITY -- 9037915
,SECONDARY_UOM -- 9037915
)
SELECT
ORGANIZATION_ID
,TRANSACTION_TEMP_ID
,TRANSACTION_HEADER_ID
,LPN_ID
,LPN_NAME
,LPN_CONTEXT
,PARENT_LPN_ID
,PARENT_LPN_NAME
,OUTERMOST_LPN_ID
,OUTERMOST_LPN_NAME
,CONSOLIDATED_LPN_ID
,CONSOLIDATED_LPN_NAME
,INTO_LPN_ID
,INTO_LPN_NAME
,DROP_TYPE
,grp_rec.drop_order
,INVENTORY_ITEM_ID
,ITEM
,p_group_id
,LOT_NUMBER
,REVISION
,grp_rec.transaction_quantity
,TRANSACTION_UOM
,LOCATOR
,PRIMARY_UOM_CODE
,DEST_SUBINVENTORY
,DEST_LOCATOR
,REVISION_QTY_CONTROL_CODE
,LOT_CONTROL_CODE
,SERIAL_NUMBER_CONTROL_CODE
,RESTRICT_SUBINVENTORIES_CODE
,RESTRICT_LOCATORS_CODE
,LOCATION_CONTROL_CODE
,ALLOWED_UNITS_LOOKUP_CODE
,BACKORDER_DELIVERY_DETAIL
,CROSSDOCK_TYPE
,WIP_SUPPLY_TYPE
,FROM_SUBINVENTORY
,FROM_LOCATOR
,TRANSFER_SUBINVENTORY
,TRANSFER_TO_LOCATION
,TRANSFER_ORGANIZATION
,TRANSACTION_ACTION_ID
,REFERENCE
,LOC_DROPPING_ORDER
,SUB_DROPPING_ORDER
,G_ROW_TP_GROUP_TASK
,MOVE_ORDER_LINE_ID
,PROJECT_ID
,TASK_ID
,TXN_SOURCE_ID
,grp_rec.primary_quantity
,PROCESS_FLAG
,WIP_JOB
,WIP_LINE
,WIP_DEPT
,WIP_OP_SEQ
,WIP_ENTITY_TYPE
,WIP_START_DATE
,WIP_SCHEDULE
,WIP_ASSEMBLY
,SECONDARY_QUANTITY -- 9037915
,SECONDARY_UOM -- 9037915
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE ROWID = grp_rec.row_id;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_txn_header_id
FROM dual;
UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
SET transaction_header_id = l_txn_header_id
WHERE group_id = p_group_id
RETURNING transaction_temp_id
BULK COLLECT INTO l_txn_temp_id_tab;
DEBUG(sql%rowcount || ' Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP with txn_header_id = ' || l_txn_header_id ||
' and group_id = ' || p_group_id
,l_proc_name,9);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET transaction_header_id = l_txn_header_id
WHERE transaction_temp_id = l_txn_temp_id_tab(i);
DEBUG(sql%rowcount || ' Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP with txn_header_id= ' || l_txn_header_id ,l_proc_name,9);
ROLLBACK TO update_gtmp_sp;
ROLLBACK TO update_gtmp_sp;
END Update_Tasks_In_Group;
* This function will update the transaction_header_id of the
* group(both in the global temp table and in MMTT) with same
* value.
* It will also stamp a common group_id for the group.
* @ RETURN NUMBER
**/
FUNCTION Sync_Group_Tasks
RETURN NUMBER
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
SELECT drop_order
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type = G_ROW_TP_GROUP_TASK;
SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
INTO l_txn_header_id
FROM dual;
UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
SET transaction_header_id = l_txn_header_id
,group_id = l_drop_order
WHERE drop_order = l_drop_order
RETURNING transaction_temp_id
BULK COLLECT INTO l_txn_temp_id_tab;
DEBUG(sql%rowcount || ' Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP with txn_header_id = ' || l_txn_header_id ||
' and group_id = ' || l_drop_order
,l_proc_name,9);
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
SET transaction_header_id = l_txn_header_id
WHERE transaction_temp_id = l_txn_temp_id_tab(i);
DEBUG(sql%rowcount || ' Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP with txn_header_id= ' || l_txn_header_id ,l_proc_name,9);
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id;
SELECT MIN(drop_order)
INTO l_min_drop_order
FROM wms_putaway_group_tasks_gtmp
WHERE lpn_id = p_lpn_id
OR lpn_id IN ( SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
);
/* UPDATE wms_putaway_group_tasks_gtmp
SET consolidated_lpn_id = p_lpn_id
,consolidated_lpn_name = l_lpn_name
,drop_order = p_drop_order
WHERE lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
);*/
UPDATE wms_putaway_group_tasks_gtmp
SET consolidated_lpn_id = p_lpn_id
,consolidated_lpn_name = p_lpn_name
,drop_order = l_min_drop_order
WHERE lpn_id = l_lpn_id_tab(i);
DEBUG('Updated the consolidated LPN columns for the children ',l_proc_name,9);
* This function will return the number of rows updated as
* Item Drop. This will retun -1 in case of a failure.
* @ RETURN NUMBER
**/
FUNCTION Mark_Item_Drop(p_lpn_id IN NUMBER)
RETURN NUMBER
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
SELECT parent_lpn_id
INTO l_parent_lpn_id
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE lpn_id = p_lpn_id
AND ROWNUM < 2;
UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
SET drop_type = G_DT_ITEM_DROP
WHERE lpn_id = p_lpn_id;
* This will return the number of rows inserted as output.
* This will return -1 in case of failure.
* @ RETURN NUMBER
**/
FUNCTION Insert_Group_Tasks (
l_rowid_tab IN rowid_tab
,l_txn_qty_tab IN num_tab
,l_pri_qty_tab IN num_tab
,l_drop_order_tab IN num_tab
,l_show_message_tab IN num_tab --R12
,l_error_code_tab IN varchar240_tab
,l_drop_lpn_option_tab IN num_tab
,l_consolidation_method_id_tab IN num_tab )
RETURN NUMBER
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
l_proc_name VARCHAR2(30) := 'Insert_Group_Tasks:';
INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
(
ORGANIZATION_ID
,TRANSACTION_TEMP_ID
,TRANSACTION_HEADER_ID
,LPN_ID
,LPN_NAME
,LPN_CONTEXT
,PARENT_LPN_ID
,PARENT_LPN_NAME
,OUTERMOST_LPN_ID
,OUTERMOST_LPN_NAME
,CONSOLIDATED_LPN_ID
,CONSOLIDATED_LPN_NAME
,INTO_LPN_ID
,INTO_LPN_NAME
,DROP_TYPE
,DROP_ORDER
,INVENTORY_ITEM_ID
,ITEM
,GROUP_ID
,LOT_NUMBER
,REVISION
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,LOCATOR
,PRIMARY_UOM_CODE
,DEST_SUBINVENTORY
,DEST_LOCATOR
,REVISION_QTY_CONTROL_CODE
,LOT_CONTROL_CODE
,SERIAL_NUMBER_CONTROL_CODE
,RESTRICT_SUBINVENTORIES_CODE
,RESTRICT_LOCATORS_CODE
,LOCATION_CONTROL_CODE
,ALLOWED_UNITS_LOOKUP_CODE
,BACKORDER_DELIVERY_DETAIL
,CROSSDOCK_TYPE
,WIP_SUPPLY_TYPE
,FROM_SUBINVENTORY
,FROM_LOCATOR
,TRANSFER_SUBINVENTORY
,TRANSFER_TO_LOCATION
,TRANSFER_ORGANIZATION
,TRANSACTION_ACTION_ID
,REFERENCE
,LOC_DROPPING_ORDER
,SUB_DROPPING_ORDER
,ROW_TYPE
,MOVE_ORDER_LINE_ID
,PROJECT_ID
,TASK_ID
,TXN_SOURCE_ID
,PRIMARY_QUANTITY
,PROCESS_FLAG
,WIP_JOB
,WIP_LINE
,WIP_DEPT
,WIP_OP_SEQ
,WIP_ENTITY_TYPE
,WIP_START_DATE
,WIP_SCHEDULE
,WIP_ASSEMBLY
,SECONDARY_QUANTITY --OPM Convergence
,SECONDARY_UOM --OPM Convergence
,show_message -- R12
,error_code --R12
,drop_lpn_option --R12
,consolidation_method_id --R12
,sub_lpn_controlled_flag --R12
)
SELECT
ORGANIZATION_ID
,TRANSACTION_TEMP_ID
,TRANSACTION_HEADER_ID
,LPN_ID
,LPN_NAME
,LPN_CONTEXT
,PARENT_LPN_ID
,PARENT_LPN_NAME
,OUTERMOST_LPN_ID
,OUTERMOST_LPN_NAME
,CONSOLIDATED_LPN_ID
,CONSOLIDATED_LPN_NAME
,INTO_LPN_ID
,INTO_LPN_NAME
,DROP_TYPE
,l_drop_order_tab(i)
,INVENTORY_ITEM_ID
,ITEM
,GROUP_ID
,LOT_NUMBER
,REVISION
,l_txn_qty_tab(i)
,TRANSACTION_UOM
,LOCATOR
,PRIMARY_UOM_CODE
,DEST_SUBINVENTORY
,DEST_LOCATOR
,REVISION_QTY_CONTROL_CODE
,LOT_CONTROL_CODE
,SERIAL_NUMBER_CONTROL_CODE
,RESTRICT_SUBINVENTORIES_CODE
,RESTRICT_LOCATORS_CODE
,LOCATION_CONTROL_CODE
,ALLOWED_UNITS_LOOKUP_CODE
-- ,BACKORDER_DELIVERY_DETAIL
,DECODE(backorder_delivery_detail,NULL,NULL,0,0,get_grouping(backorder_delivery_detail)) --Added for bug 5286880
,CROSSDOCK_TYPE
,WIP_SUPPLY_TYPE
,FROM_SUBINVENTORY
,FROM_LOCATOR
,TRANSFER_SUBINVENTORY
,TRANSFER_TO_LOCATION
,TRANSFER_ORGANIZATION
,TRANSACTION_ACTION_ID
,REFERENCE
,LOC_DROPPING_ORDER
,SUB_DROPPING_ORDER
,G_ROW_TP_GROUP_TASK
,MOVE_ORDER_LINE_ID
,PROJECT_ID
,TASK_ID
,TXN_SOURCE_ID
,l_pri_qty_tab(i)
,PROCESS_FLAG
,WIP_JOB
,WIP_LINE
,WIP_DEPT
,WIP_OP_SEQ
,WIP_ENTITY_TYPE
,WIP_START_DATE
,WIP_SCHEDULE
,WIP_ASSEMBLY
,SECONDARY_QUANTITY --OPM Convergence
,SECONDARY_UOM --OPM Convergence
,l_show_message_tab(i) --R12
,l_error_code_tab(i) --R12
,l_drop_lpn_option_tab(i) --R12
,l_consolidation_method_id_tab(i) --R12
,sub_lpn_controlled_flag --R12
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE ROWID = l_rowid_tab(i);
END Insert_Group_Tasks;
* This function will insert the group_task rows
* This will insert one group task row per group.
*
*
* This will return the number of groups created as
* output.This will return -1 in case of failure.
* @ RETURN NUMBER
**/
FUNCTION Group_Consolidated_Drop_Tasks
RETURN NUMBER
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
SELECT min(ROWID)
,SUM(transaction_quantity)
,SUM(primary_quantity)
,drop_order
,SUM(secondary_quantity) --OPM Convergence
,MAX(show_message) --R12
,MAX(error_code) --R12
,MAX(drop_lpn_option) --R12: This value should be unique per group
,MAX(consolidation_method_id) --R12: This value should be unique per group
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type IN (G_ROW_TP_ALL_TASK, G_ROW_TP_LPN_TASK)
AND drop_type = G_DT_CONSOLIDATED_DROP
GROUP BY drop_order;
/* Note: Not passing l_sec_qty_tab as input to insert_group_tasks
since only l_rowid_tab is used in this function to fetch
the corresponding records from the gtmp table. Infact all
the other tables are not needed to pass as inputs to this
Function
*/
l_cur_count := Insert_Group_Tasks (
l_rowid_tab => l_rowid_tab
,l_txn_qty_tab => l_txn_qty_tab
,l_pri_qty_tab => l_pri_qty_tab
,l_drop_order_tab => l_drop_order_tab
,l_show_message_tab=> l_show_message_tab --R12
,l_error_code_tab => l_error_code_tab --R12
,l_drop_lpn_option_tab => l_drop_lpn_option_tab --R12
,l_consolidation_method_id_tab => l_consolidation_method_id_tab); --R12
DEBUG( l_cur_count|| ' Group task Row(s) inserted in WMS_PUTAWAY_GROUP_TASKS_GTMP '
,l_proc_name,9);
SELECT MIN(ROWID)
,MIN(drop_order)
,SUM(transaction_quantity)
,SUM(primary_quantity)
,SUM(secondary_quantity) --OPM Convergence
,MAX(show_message)
,MAX(error_code)
,MAX(drop_lpn_option) --R12: This value should be unique per group
,MAX(consolidation_method_id) --R12: This value should be unique per group
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type = G_ROW_TP_ALL_TASK
AND drop_type = G_DT_ITEM_DROP
AND NOT (crossdock_type > 1
AND backorder_delivery_detail > 0
AND wip_supply_type = 1
)
GROUP BY lpn_id
,inventory_item_id
,transaction_uom
,revision
,lot_number
,dest_locator
,into_lpn_id
--,backorder_delivery_detail; --R12
SELECT MIN(ROWID)
,MIN(drop_order)
,SUM(transaction_quantity)
,SUM(primary_quantity)
,SUM(secondary_quantity) --OPM Convergence
,MAX(show_message)
,MAX(error_code)
,MAX(drop_lpn_option) --R12: This value should be unique per group
,MAX(consolidation_method_id) --R12: This value should be unique per group
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type = G_ROW_TP_ALL_TASK
AND drop_type = G_DT_ITEM_DROP
AND crossdock_type > 1
AND backorder_delivery_detail > 0
AND wip_supply_type = 1
GROUP BY lpn_id
,inventory_item_id
,transaction_uom
,revision
,lot_number
,dest_locator
,into_lpn_id
,BACKORDER_DELIVERY_DETAIL
,WIP_JOB
,WIP_LINE
,WIP_DEPT
,WIP_OP_SEQ
,WIP_ENTITY_TYPE
,WIP_START_DATE
,WIP_SCHEDULE
,WIP_ASSEMBLY;
SELECT lpn_id
,inventory_item_id
,transaction_uom
,revision
,lot_number
,dest_locator
,BACKORDER_DELIVERY_DETAIL
,into_lpn_id
,drop_order
,WIP_JOB
,WIP_LINE
,WIP_DEPT
,WIP_OP_SEQ
,WIP_ENTITY_TYPE
,WIP_START_DATE
,WIP_SCHEDULE
,WIP_ASSEMBLY
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE drop_type = G_DT_ITEM_DROP
AND row_type = G_ROW_TP_GROUP_TASK;
/* Note: OPM Convergence Not passing l_sec_qty_tabl to insert_group_tasks,
since this table will not be used in that function
*/
l_cur_count := Insert_Group_Tasks (
l_rowid_tab => l_rowid_tab
,l_txn_qty_tab => l_txn_qty_tab
,l_pri_qty_tab => l_pri_qty_tab
,l_drop_order_tab => l_drop_order_tab
,l_show_message_tab=> l_show_message_tab --R12
,l_error_code_tab => l_error_code_tab --R12
,l_drop_lpn_option_tab => l_drop_lpn_option_tab --R12
,l_consolidation_method_id_tab => l_consolidation_method_id_tab); --R12
DEBUG( l_cur_count|| ' Group task Row(s) inserted in WMS_PUTAWAY_GROUP_TASKS_GTMP '
,l_proc_name,9);
l_cur_count := Insert_Group_Tasks (
l_rowid_tab => l_rowid_tab
,l_txn_qty_tab => l_txn_qty_tab
,l_pri_qty_tab => l_pri_qty_tab
,l_drop_order_tab => l_drop_order_tab
,l_show_message_tab=> l_show_message_tab
,l_error_code_tab => l_error_code_tab
,l_drop_lpn_option_tab => l_drop_lpn_option_tab --R12
,l_consolidation_method_id_tab => l_consolidation_method_id_tab); --R12
DEBUG( l_cur_count|| ' Group task Row(s) inserted in WMS_PUTAWAY_GROUP_TASKS_GTMP for crossdocked to WIP ISSUE tasks'
,l_proc_name,9);
BEGIN -- This will create link between 'Group Task' which was inserteed above with its corresponding 'All Task' rows
OPEN c_group_tasks_cursor;
UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
SET drop_order = l_out_drop_order_tab(i)
WHERE drop_type = G_DT_ITEM_DROP
AND lpn_id = l_lpn_id_tab(i)
AND inventory_item_id = l_inventory_item_id_tab(i)
AND transaction_uom = l_transaction_uom_tab(i)
AND NVL(revision,'@@@') = NVL(l_revision_tab(I), '@@@')
AND NVL(lot_number,'@@@') = NVL(l_lot_number_tab(i),'@@@')
AND NVL(dest_locator,-999) = NVL(l_dest_locator_tab(i),-999)
--AND NVL(BACKORDER_DELIVERY_DETAIL,-999) = NVL(l_bk_dl_detail_tab(i),-999) --Commented for bug 5286880
AND Decode(BACKORDER_DELIVERY_DETAIL,NULL,-999,0,0,Decode(row_type,g_row_tp_all_task,get_grouping(backorder_delivery_detail),backorder_delivery_detail)) = NVL(l_bk_dl_detail_tab(i),-999)--bug12399417
AND NVL(into_lpn_id,-999) = NVL(l_into_lpn_id_tab(i),-999)
AND NVL(wip_job,'@@@') = NVL(l_job_tab(i),'@@@')
AND NVL(wip_line,'@@@') = NVL(l_line_tab(i),'@@@')
AND NVL(wip_dept,'@@@') = NVL(l_dept_tab(i),'@@@')
AND NVL(wip_op_seq,-999) = NVL(l_operation_seq_num_tab(i),-999)
AND NVL(wip_entity_type,-999) = NVL(l_wip_entity_type_tab(i),-999)
AND NVL(wip_start_date,SYSDATE) = NVL(l_start_date_tab(i),SYSDATE)
AND NVL(wip_schedule,'@@@') = NVL(l_schedule_tab(i),'@@@')
AND NVL(wip_assembly,'@@@') = NVL(l_assembly_tab(i),'@@@');
DEBUG(sql%rowcount || ' Group task Row(s) updated in WMS_PUTAWAY_GROUP_TASKS_GTMP '
,l_proc_name,9);
* update it with childs dest sub/loc
* else if it is different from childs
* mark item drop for the parent
*
* This will return the number of rows processed as
* output.This will return -1 in case of failure.
* @ RETURN NUMBER
**/
FUNCTION Populate_Drop_Type
RETURN NUMBER
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
SELECT DISTINCT
lpn_level
FROM wms_putaway_group_tasks_gtmp
WHERE drop_type = G_DT_CONSOLIDATED_DROP
ORDER BY lpn_level DESC;
SELECT DISTINCT
lpn_id
,parent_lpn_id
,dest_subinventory
,dest_locator
,into_lpn_id
,into_lpn_name
,row_type
,crossdock_type
,backorder_delivery_detail
,wip_supply_type
,WIP_JOB
,WIP_LINE
,WIP_DEPT
,WIP_OP_SEQ
,WIP_ENTITY_TYPE
,WIP_START_DATE
,WIP_SCHEDULE
,WIP_ASSEMBLY
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE drop_type = G_DT_CONSOLIDATED_DROP
AND lpn_level = v_lpn_level;
SELECT ROWID
,dest_subinventory
,dest_locator
,into_lpn_id
,into_lpn_name
,crossdock_type
,backorder_delivery_detail
,wip_supply_type
,WIP_JOB
,WIP_LINE
,WIP_DEPT
,WIP_OP_SEQ
,WIP_ENTITY_TYPE
,WIP_START_DATE
,WIP_SCHEDULE
,WIP_ASSEMBLY
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE lpn_id = v_parent_lpn_id
AND drop_type = G_DT_CONSOLIDATED_DROP
AND ROWNUM < 2;
SELECT wpgtt.ROWID
,ROWNUM
,wpgtt.lpn_id lpn_id
FROM wms_putaway_group_tasks_gtmp wpgtt
ORDER BY wpgtt.sub_dropping_order,
wpgtt.dest_subinventory,
wpgtt.loc_dropping_order,
wpgtt.LOCATOR,
wpgtt.inventory_item_id,
wpgtt.revision,
wpgtt.lot_number;
SELECT
wlpn1.license_plate_number lpn_name
,wlpn2.lpn_id parent_lpn_id
,wlpn2.license_plate_number parent_lpn_name
,wlpn3.lpn_id outermost_lpn_id
,wlpn3.license_plate_number outermost_lpn_name
INTO
l_lpn_name_tab(i)
,l_parent_lpn_id_tab(i)
,l_parent_lpn_name_tab(i)
,l_outermost_lpn_id_tab(i)
,l_outermost_lpn_name_tab(i)
FROM
wms_license_plate_numbers wlpn1
,wms_license_plate_numbers wlpn2
,wms_license_plate_numbers wlpn3
WHERE
wlpn1.lpn_id = l_wpgtt_lpn_id_tab(i)
AND wlpn1.outermost_lpn_id = wlpn3.lpn_id
AND wlpn2.lpn_id(+) = wlpn1.parent_lpn_id;
UPDATE wms_putaway_group_tasks_gtmp
SET drop_order = l_drop_order
,lpn_name = l_lpn_name_tab(i)
,parent_lpn_id = l_parent_lpn_id_tab(i)
,parent_lpn_name = l_parent_lpn_name_tab(i)
,outermost_lpn_id = l_outermost_lpn_id_tab(i)
,outermost_lpn_name = l_outermost_lpn_name_tab(i)
WHERE ROWID = l_rowid_tab(i);
SELECT COUNT(DISTINCT NVL(transfer_to_location,locator_id))
,COUNT(DISTINCT cartonization_id)
INTO l_loc_count
,l_to_lpn_count
FROM mtl_material_transactions_temp mmtt
,mtl_txn_request_lines mtrl
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mtrl.lpn_id = l_lpn_id_tab(i);
SELECT 1
INTO l_qty_disc_count
FROM dual
WHERE EXISTS (
SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl
,mtl_txn_request_headers mtrh
WHERE 1 = 1
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = 6
AND mtrl.lpn_id = l_lpn_id_tab(i)
AND mtrl.line_status = 7 --BUG 3352572
AND ((NOT exists
(SELECT 1
FROM mtl_material_transactions_temp mmtt1
WHERE mtrl.line_status = 7
AND mmtt1.move_order_line_id = mtrl.line_id)
) OR
(( (NVL(quantity,0) - NVL(quantity_delivered,0) ) )
> (SELECT SUM(transaction_quantity)
FROM mtl_material_transactions_temp mmtt
WHERE mtrl.line_status = 7
AND mmtt.move_order_line_id = mtrl.line_id
))
)
);
SELECT COUNT(DISTINCT BACKORDER_DELIVERY_DETAIL)
,COUNT(DISTINCT WIP_JOB)
,COUNT(DISTINCT WIP_LINE)
,COUNT(DISTINCT WIP_DEPT)
,COUNT(DISTINCT WIP_OP_SEQ)
,COUNT(DISTINCT WIP_ENTITY_TYPE)
,COUNT(DISTINCT WIP_START_DATE)
,COUNT(DISTINCT WIP_SCHEDULE)
,COUNT(DISTINCT WIP_ASSEMBLY)
INTO l_del_detail_id_count
,l_wip_job_count
,l_wip_line_count
,l_wip_dept_count
,l_wip_op_seq_count
,l_wip_entity_type_count
,l_wip_start_date_count
,l_wip_schedule_count
,l_wip_assembly_count
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE row_type = G_ROW_TP_ALL_TASK
AND crossdock_type > 1
AND backorder_delivery_detail > 0
AND wip_supply_type = 1
AND lpn_id = l_lpn_id_tab(i);
UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
SET dest_subinventory = l_dest_sub_tab(i)
,dest_locator = l_dest_loc_tab(i)
,into_lpn_id = l_into_lpn_id_tab(i)
,into_lpn_name = l_into_lpn_name_tab(i)
,crossdock_type = l_crossdock_type_tab(i)
,backorder_delivery_detail = l_bk_dl_detail_tab(i)
,wip_supply_type = l_wip_supply_type_tab(i)
,WIP_JOB = l_job_tab(i)
,WIP_LINE = l_line_tab(i)
,WIP_DEPT = l_dept_tab(i)
,WIP_OP_SEQ = l_operation_seq_num_tab(i)
,WIP_ENTITY_TYPE = l_wip_entity_type_tab(i)
,WIP_START_DATE = l_start_date_tab(i)
,WIP_SCHEDULE = l_schedule_tab(i)
,WIP_ASSEMBLY = l_assembly_tab(i)
WHERE ROWID = l_rowid;
DEBUG('Parent Dest loc for LPN ' || l_lpn_id_tab(i) || ' updated with dest_locator ' || l_dest_loc_tab(i) ,l_proc_name,9);
DEBUG('Parent Dest LPN for LPN ' || l_lpn_id_tab(i) || ' updated with into_lpn ' || l_into_lpn_id_tab(i) ,l_proc_name,9);
DEBUG('Parent crossdock_type for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_crossdock_type_tab(i) ,l_proc_name,9);
DEBUG('Parent backorder_delivery_detail for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_bk_dl_detail_tab(i) ,l_proc_name,9);
DEBUG('Parent wip_supply_type for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_wip_supply_type_tab(i) ,l_proc_name,9);
DEBUG('Parent WIP_JOB for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_job_tab(i) ,l_proc_name,9);
DEBUG('Parent WIP_LINE for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_line_tab(i) ,l_proc_name,9);
DEBUG('Parent WIP_DEPT for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_dept_tab(i) ,l_proc_name,9);
DEBUG('Parent WIP_OP_SEQ for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_operation_seq_num_tab(i) ,l_proc_name,9);
DEBUG('Parent WIP_ENTITY_TYPE for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_wip_entity_type_tab(i) ,l_proc_name,9);
DEBUG('Parent WIP_START_DATE for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_start_date_tab(i) ,l_proc_name,9);
DEBUG('Parent WIP_SCHEDULE for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_schedule_tab(i) ,l_proc_name,9);
DEBUG('Parent WIP_ASSEMBLY for LPN ' || l_lpn_id_tab(i) || ' updated with ' || l_assembly_tab(i) ,l_proc_name,9);
UPDATE mtl_material_transactions_temp
SET cartonization_id = NULL
WHERE transaction_temp_id IN (SELECT gtmp.transaction_temp_id
FROM wms_putaway_group_tasks_gtmp gtmp
WHERE gtmp.consolidation_method_id = 1 --???MDC
AND gtmp.drop_type = g_dt_item_drop);
DEBUG('Num of MMTT rows updated:' ||SQL%rowcount,l_proc_name,4);
UPDATE wms_putaway_group_tasks_gtmp
SET consolidation_method_id = 0
WHERE consolidation_method_id = 1
AND drop_type = g_dt_item_drop;
DEBUG('Num of GTMP rows updated:' ||SQL%rowcount,l_proc_name,4);
* This method will also insert the dummy records for those
* LPN which doesnt have contents
* Drop all scenario is also taken care while inserting
* For drop all case, we will always start with the outermost lpn id.
* because we should be suggesting the consolidated lpn wherever possible
* @param p_org_id Organization ID
* @param p_drop_type Drop Type
* @param p_emp_id Employee ID
* @param p_lpn_id LPN ID
* @ RETURN NUMBER
**/
FUNCTION Populate_Group_Tasks(
p_org_id IN NUMBER,
p_drop_type IN VARCHAR2,
p_emp_id IN NUMBER,
p_lpn_id IN NUMBER,
p_item_drop_flag IN VARCHAR2)
RETURN NUMBER
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
SELECT lpn_id
,parent_lpn_id
,LEVEL
,lpn_context
FROM wms_license_plate_numbers wln
START WITH LPN_ID = p_lpn_id
CONNECT BY PRIOR wln.lpn_id = wln.parent_lpn_id;
SELECT lpn_id
,parent_lpn_id
,LEVEL
,lpn_context
FROM wms_license_plate_numbers wln
START WITH LPN_ID IN (
SELECT DISTINCT wln2.outermost_lpn_id
FROM wms_dispatched_tasks wdt
,mtl_txn_request_lines mtrl
,mtl_material_transactions_temp mmtt
,mtl_txn_request_headers mtrh
,wms_license_plate_numbers wln2
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.move_order_type = 6
AND mtrl.line_status = 7
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id
AND wdt.STATUS = 4
AND wdt.person_id = p_emp_id
AND wln2.lpn_id = mtrl.lpn_id
)
CONNECT BY PRIOR wln.lpn_id = wln.parent_lpn_id;
SELECT ROWID
,transaction_temp_id
,organization_id
,Nvl(crossdock_type,0)
,Nvl(wip_supply_type,0)
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE 1 = 1
AND row_type = G_ROW_TP_ALL_TASK
AND crossdock_type > 1
AND backorder_delivery_detail > 0;
BEGIN --Block to insert the 'All Task' rows
/* bug10354084 */
IF p_drop_type = G_DT_DROP_ALL THEN
OPEN c_dropall_lpn_cursor;
SELECT 1
INTO l_has_contents
FROM wms_lpn_contents
WHERE parent_lpn_id = l_lpn_id_tab(i)
AND ROWNUM < 2;
INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
(
ORGANIZATION_ID
,LPN_ID
,PARENT_LPN_ID
,DROP_TYPE
,ROW_TYPE
,LPN_LEVEL
,LPN_CONTEXT
,WIP_SUPPLY_TYPE
)
VALUES
(
p_org_id
,l_lpn_id_tab(i)
,l_parent_lpn_id_tab(i)
,G_DT_CONSOLIDATED_DROP
,G_ROW_TP_LPN_TASK
,l_lpn_level_tab(i)
,l_lpn_context_tab(I)
,0
);
DEBUG('Inserted dummy row for lpn id ' || l_lpn_id_tab(i),l_proc_name,9);
INSERT INTO WMS_PUTAWAY_GROUP_TASKS_GTMP
(
ORGANIZATION_ID
,TRANSACTION_TEMP_ID
,TRANSACTION_HEADER_ID
,LPN_ID
,LPN_NAME
,LPN_CONTEXT
,PARENT_LPN_ID
,PARENT_LPN_NAME
,OUTERMOST_LPN_ID
,OUTERMOST_LPN_NAME
,CONSOLIDATED_LPN_ID
,CONSOLIDATED_LPN_NAME
,INTO_LPN_ID
,INTO_LPN_NAME
,DROP_TYPE
,DROP_ORDER
,INVENTORY_ITEM_ID
,ITEM
,GROUP_ID
,LOT_NUMBER
,REVISION
,TRANSACTION_QUANTITY
,TRANSACTION_UOM
,LOCATOR
,PRIMARY_UOM_CODE
,DEST_SUBINVENTORY
,DEST_LOCATOR
,REVISION_QTY_CONTROL_CODE
,LOT_CONTROL_CODE
,SERIAL_NUMBER_CONTROL_CODE
,RESTRICT_SUBINVENTORIES_CODE
,RESTRICT_LOCATORS_CODE
,LOCATION_CONTROL_CODE
,ALLOWED_UNITS_LOOKUP_CODE
,BACKORDER_DELIVERY_DETAIL
,CROSSDOCK_TYPE
,WIP_SUPPLY_TYPE
,FROM_SUBINVENTORY
,FROM_LOCATOR
,TRANSFER_SUBINVENTORY
,TRANSFER_TO_LOCATION
,TRANSFER_ORGANIZATION
,TRANSACTION_ACTION_ID
,REFERENCE
,LOC_DROPPING_ORDER
,SUB_DROPPING_ORDER
,ROW_TYPE
,MOVE_ORDER_LINE_ID
,PROJECT_ID
,TASK_ID
,TXN_SOURCE_ID
,PRIMARY_QUANTITY
,LPN_LEVEL
,SECONDARY_QUANTITY --OPM Convergence
,SECONDARY_UOM --OPM Convergence
,show_message --R12
,error_code --R12
,error_explanation --R12
)
SELECT DISTINCT
mmtt.organization_id,
mmtt.transaction_temp_id,
mmtt.transaction_header_id,
mtrl.lpn_id,
NULL, --lpn_name
l_lpn_context_tab(i),
--TO_NUMBER(NULL), --Parent LPN ID
l_parent_lpn_id_tab(i), --Parent LPN ID
NULL, --Parent LPN Name
TO_NUMBER(NULL), --Outermost LPN ID
NULL, --Outermost LPN Name
TO_NUMBER(NULL), --Consolidated LPN ID
NULL, --Consolidated LPN Name
mmtt.cartonization_id, --INTO LPN ID
NULL, --INTO LPN Name
decode(p_item_drop_flag,'Y',G_DT_ITEM_DROP,G_DT_CONSOLIDATED_DROP), --Drop Type
ROWNUM, --Drop Order
mmtt.inventory_item_id,
msik.concatenated_segments item, --Item
TO_NUMBER(NULL), --Group ID
mtrl.lot_number,
mmtt.revision,
mmtt.transaction_quantity,
mmtt.transaction_uom,
NULL LOCATOR, --inv_project.get_locsegs (milk.inventory_location_id, milk.organization_id ) LOCATOR,
msik.primary_uom_code,
--DECODE (transaction_action_id, 2, mmtt.transfer_subinventory,mmtt.subinventory_code) dest_subinventory,
--DECODE (transaction_action_id, 2, mmtt.transfer_to_location, mmtt.locator_id) dest_locator,
NVL(mmtt.transfer_subinventory,mmtt.subinventory_code) dest_subinventory,
NVL(mmtt.transfer_to_location, mmtt.locator_id) dest_locator,
NVL (msik.revision_qty_control_code, 1),
NVL (msik.lot_control_code, 1),
NVL (msik.serial_number_control_code, 1),
NVL (msik.restrict_subinventories_code, 2),
NVL (msik.restrict_locators_code, 2),
NVL (msik.location_control_code, 1),
NVL (msik.allowed_units_lookup_code, 2),
NVL (mtrl.backorder_delivery_detail_id, 0),
NVL (mtrl.crossdock_type, 0),
NVL (mmtt.wip_supply_type, 0),
mmtt.subinventory_code from_subinventory,
mmtt.locator_id from_locator,
mmtt.transfer_subinventory,
mmtt.transfer_to_location,
mmtt.transfer_organization,
mmtt.transaction_action_id,
mtrl.REFERENCE,
to_number(null), -- milk.dropping_order,
to_number(NULL), -- msi.dropping_order,
G_ROW_TP_ALL_TASK,
mtrl.line_id,
mtrl.project_id,
mtrl.task_id,
mtrl.txn_source_id,
mmtt.primary_quantity,
l_lpn_level_tab(i),
mmtt.secondary_transaction_quantity, --OPM Convergence
mmtt.secondary_uom_code, --OPM Convergence
--R12: Change Management: If suggested loc = wlpn.loc, should show iser wanring message
Decode(wln.locator_id,NVL(mmtt.transfer_to_location, mmtt.locator_id),1,0) show_message,
mmtt.error_code,
mmtt.error_explanation
--R12 End
FROM mtl_material_transactions_temp mmtt
,mtl_txn_request_lines mtrl
,wms_dispatched_tasks wdt
--,mtl_item_locations milk
,mtl_system_items_kfv msik
,mtl_txn_request_headers mtrh
,wms_license_plate_numbers wln
--,mtl_secondary_inventories msi
WHERE wdt.organization_id = p_org_id
-- kajain
-- added the decode since a lpn loaded by someone
-- should be eligible to be dropped by some other user
-- also added a check to make sure that wdt.status = 4
AND wdt.person_id = Decode(p_drop_type, g_dt_drop_all,p_emp_id,wdt.person_id)
AND wdt.status = 4
AND wdt.task_type = 2
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id
AND NVL (mmtt.wms_task_type, 0) <> -1
AND mtrl.lpn_id = l_lpn_id_tab(i)
AND wln.organization_id = p_org_id
AND mtrl.lpn_id = wln.lpn_id
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = 6
--AND NVL(mmtt.transfer_to_location,mmtt.locator_id) = milk.inventory_location_id(+)
--AND msi.organization_id(+) = mmtt.organization_id
--AND NVL(mmtt.transfer_subinventory,mmtt.subinventory_code) = msi.secondary_inventory_name(+)
--AND mmtt.organization_id = milk.organization_id(+)
AND mmtt.organization_id = msik.organization_id
AND mmtt.inventory_item_id = msik.inventory_item_id;
DEBUG('Inserted ' || SQL%rowcount || ' all task rows for lpn id ' || l_lpn_id_tab(i),l_proc_name,9);
END; --Block to insert the 'All Task' rows
UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
SET WIP_ENTITY_TYPE = l_wip_entity_type_tab(i),
BACKORDER_DELIVERY_DETAIL = l_wip_entity_type_tab(i)
WHERE transaction_temp_id = l_tempid_tab(i);
UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
SET wip_supply_type = 0
,backorder_delivery_detail = 0
,crossdock_type = 0
WHERE transaction_temp_id = l_tempid_tab(i);
UPDATE WMS_PUTAWAY_GROUP_TASKS_GTMP
SET WIP_JOB = l_job_tab(i)
,WIP_LINE = l_line_tab(i)
,WIP_DEPT = l_dept_tab(i)
,WIP_OP_SEQ = l_operation_seq_num_tab(i)
,WIP_ENTITY_TYPE = l_wip_entity_type_tab(i)
,WIP_START_DATE = l_start_date_tab(i)
,WIP_SCHEDULE = l_schedule_tab(i)
,WIP_ASSEMBLY = l_assembly_tab(i)
,BACKORDER_DELIVERY_DETAIL = l_wip_entity_type_tab(i)
WHERE ROWID = l_rowid_tab(i)
AND Nvl(crossdock_type,1) = 2
AND wip_supply_type = 1;
SELECT /*+ ORDERED USE_NL(mtrl mmtt) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
mmtt.transaction_temp_id
FROM ( SELECT lpn_id FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
) wlpn,
mtl_txn_request_lines mtrl,
mtl_material_transactions_temp mmtt
WHERE mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_status = 7
AND mtrl.lpn_id = wlpn.lpn_id ;
SELECT /*+ ORDERED USE_NL(mtrl mmtt) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
mmtt.transaction_temp_id
FROM ( SELECT lpn_id FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
) wlpn,
mtl_txn_request_lines mtrl,
mtl_material_transactions_temp mmtt
WHERE mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_status = 7
AND mtrl.lpn_id = wlpn.lpn_id ;
* After call to ATF API the new dest sub/loc/LPN will be updated back to the
* global temp table.
*
* It returns the number of rows for which the plan is activated
* will return -1 in case of failure.
* @param x_return_status Return status of the function - Success, Error, Unexpected Error, Warning etc.,
* @param x_msg_count Count of messages in the stack
* @param x_msg_data Actual message if the count = 1 else it will be null.
*
* @ RETURN number
**/
FUNCTION Activate_Plan_For_Drop(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 )
RETURN NUMBER
IS
l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
SELECT ROWID
,transaction_temp_id
,show_message
,error_code
FROM wms_putaway_group_tasks_gtmp
WHERE row_type = G_ROW_TP_ALL_TASK;
SELECT NVL(mmtt.transfer_subinventory,mmtt.subinventory_code) dest_subinventory
,NVL(mmtt.transfer_to_location, mmtt.locator_id) dest_locator
,mmtt.cartonization_id into_lpn_id
,wlpn.license_plate_number into_lpn_name
,msi.dropping_order sub_dropping_order
,milk.dropping_order loc_dropping_order
,Nvl(msi.lpn_controlled_flag,2) lpn_controlled_flag
FROM mtl_material_transactions_temp mmtt
,wms_license_plate_numbers wlpn
,mtl_secondary_inventories msi
,mtl_item_locations_kfv milk
WHERE mmtt.transaction_temp_id = v_transaction_temp_id
AND mmtt.cartonization_id = wlpn.lpn_id(+)
AND NVL(mmtt.transfer_to_location,mmtt.locator_id) = milk.inventory_location_id(+)
AND msi.organization_id(+) = mmtt.organization_id
AND NVL(mmtt.transfer_subinventory,mmtt.subinventory_code) = msi.secondary_inventory_name(+)
AND mmtt.organization_id = milk.organization_id(+);
DEBUG('Doing a BULK update of temp table with the ATF suggested dest sub/loc/LPN ' ,l_proc_name,9);
UPDATE wms_putaway_group_tasks_gtmp
SET dest_subinventory = l_dest_sub_tab(i)
,dest_locator = l_dest_loc_tab(i)
,into_lpn_id = l_into_lpn_id_tab(i)
,into_lpn_name = l_into_lpn_name_tab(i)
,locator = inv_project.get_locsegs (l_dest_loc_tab(i), organization_id)
,sub_dropping_order = l_sub_drop_order_tab(i)
,loc_dropping_order = l_loc_drop_order_tab(i)
,show_message = l_show_message_tab(i) --R12
,error_code = l_error_code_tab(i) --???
,drop_lpn_option = l_drop_lpn_option_tab(i) --R12
,consolidation_method_id = l_consolidation_method_id_tab(i) --R12
,sub_lpn_controlled_flag = l_lpn_controlled_flag_tab(i) --R12
WHERE ROWID = l_rowid_tab(i);
DEBUG('Done a BULK update of this set of acivated MMTTs. rows = ' || SQL%ROWCOUNT ,l_proc_name,9);
SELECT lpn_id
BULK COLLECT
INTO l_lpn_id_tab
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id IN ( SELECT DISTINCT lpn_id
FROM wms_putaway_group_tasks_gtmp
WHERE row_type = G_ROW_TP_ALL_TASK
AND lpn_context <> 3
)
FOR UPDATE NOWAIT;
SELECT DISTINCT
lpn_id
,lpn_name
,lpn_level
FROM WMS_PUTAWAY_GROUP_TASKS_GTMP
WHERE consolidated_lpn_id IS NULL
AND drop_type = G_DT_CONSOLIDATED_DROP
ORDER BY lpn_level ASC;
BEGIN -- Delete Existing rows in the temp table
l_progress := '40';
END; -- Delete Existing rows in the temp table
SELECT parent_lpn_id
INTO l_parent_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
l_lpn_id_tab.delete;
l_lpn_name_tab.delete;
l_lpn_level_tab.delete;
BEGIN --Delete dummy rows
l_progress := '940';
END; --Delete dummy rows
SELECT /*+ ORDERED USE_NL(WLPN MTRL MMTT) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
MMTT.TRANSACTION_TEMP_ID
FROM
( SELECT LPN_ID
FROM WMS_LICENSE_PLATE_NUMBERS START WITH LPN_ID = p_lpn_id CONNECT BY PRIOR LPN_ID = PARENT_LPN_ID ) WLPN,
MTL_TXN_REQUEST_LINES MTRL,
MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
AND LINE_STATUS = 7
AND MTRL.LPN_ID = WLPN.LPN_ID ;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
,mtl_txn_request_lines mtrl
WHERE mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_id = p_mo_line_id
AND mtrl.line_status = 7
AND mtrl.organization_id = p_org_id;
SELECT NVL(employee_id, -1)
INTO l_emp_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
,mtl_txn_request_lines mtrl
,wms_dispatched_tasks wdt
,(SELECT lpn_id FROM wms_license_plate_numbers /*Bug5723418.*/
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
) wlpn
WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
-- BUG 5075410: WDT MOL is not being kept in sync when split.
-- Querying through WDT->MMTT->MTRL should be enough
-- AND wdt.move_order_line_id = mtrl.line_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_status = 7
AND mtrl.organization_id = p_org_id
AND mtrl.lpn_id = wlpn.lpn_id;
SELECT transaction_temp_id
FROM wms_putaway_group_tasks_gtmp
WHERE group_id = p_group_id
AND row_type = G_ROW_TP_ALL_TASK;
SELECT mmtt.transaction_temp_id
FROM wms_dispatched_tasks wdt
,mtl_txn_request_lines mtrl
,mtl_material_transactions_temp mmtt
,mtl_txn_request_headers mtrh
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.move_order_type = 6
AND mtrl.line_status = 7
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id
AND ((p_group_id = -2
AND Nvl(mtrl.wms_process_flag,1) <> 2 --RCV would set this to 2 for mol being processed
AND Nvl(mmtt.wms_task_type,2) <> -1 --RCV would set this to -1 for MMTT being processed
AND Nvl(mmtt.transaction_status,2) <> 3 --WIP would set this to 3 for MMTT being processed
) OR
(p_group_id = -1))
AND wdt.STATUS = 4
AND wdt.task_type = 2
AND mtrl.lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = l_outermost_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND ((mmtt.operation_plan_id IS NOT NULL
AND exists (SELECT 1
FROM wms_op_operation_instances wooi
WHERE wooi.source_task_id = mmtt.transaction_temp_id
AND wooi.operation_status = 2
AND wooi.operation_type_id = 2))
OR
(mmtt.operation_plan_id IS NULL))
AND p_drop_all <> 'Y'
UNION ALL
SELECT mmtt.transaction_temp_id
FROM wms_dispatched_tasks wdt
,mtl_txn_request_lines mtrl
,mtl_material_transactions_temp mmtt
,mtl_txn_request_headers mtrh
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.move_order_type = 6
AND mtrl.line_status = 7
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id
AND ((p_group_id = -2
AND Nvl(mtrl.wms_process_flag,1) <> 2 --RCV would set this to 2 for mol being processed
AND Nvl(mmtt.wms_task_type,2) <> -1 --RCV would set this to -1 for MMTT being processed
AND Nvl(mmtt.transaction_status,2) <> 3 --WIP would set this to 3 for MMTT being processed
) OR
(p_group_id = -1))
AND wdt.task_type = 2
AND wdt.STATUS = 4
AND wdt.person_id = p_emp_id
AND ((mmtt.operation_plan_id IS NOT NULL
AND exists (SELECT 1
FROM wms_op_operation_instances wooi
WHERE wooi.source_task_id = mmtt.transaction_temp_id
AND wooi.operation_status = 2
AND wooi.operation_type_id = 2))
OR
(mmtt.operation_plan_id IS NULL))
AND p_drop_all = 'Y';
SELECT outermost_lpn_id
, lpn_context
INTO l_outermost_lpn_id
, l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT lpn_context
INTO l_lpn_cxt
FROM wms_license_plate_numbers
WHERE lpn_id= p_lpn_id ;
SELECT lpn_context
INTO l_parent_lpn_cxt
FROM wms_license_plate_numbers
WHERE lpn_id= p_parent_lpn_id ;
--delete the task and close the MOL.
IF (l_debug = 1) THEN
debug('Inside the begin, before selection from mmtt',l_proc_name,1);
SELECT mmtt.operation_plan_id
, mmtt.transaction_uom
, mmtt.transaction_quantity
, mmtt.move_order_line_id
, mmtt.inventory_item_id
, mtrl.uom_code
, mtrl.wms_process_flag
INTO l_op_plan_id
, l_mmtt_uom
, l_mmtt_qty
, l_mol_id
, l_mmtt_item_id
, l_mol_uom
, l_wms_process_flag
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE mmtt.transaction_temp_id = l_tempid_tab(i)
AND mmtt.move_order_line_id = mtrl.line_id;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
--Delete WDT
BEGIN
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_tempid_tab(i);
debug('# of WDT deleted:'||SQL%rowcount,l_proc_name,1);
debug('DELETE MMTT/MTLT and close/reduce MOL for:'||l_tempid_tab(i),l_proc_name,1);
--update MOL
UPDATE mtl_txn_request_lines mol
SET quantity = quantity - l_mmtt_qty_mol_uom
, quantity_detailed = quantity_detailed - l_mmtt_qty_mol_uom
, line_status = Decode(quantity-Nvl(quantity_delivered,0)-l_mmtt_qty_mol_uom
,0
,inv_globals.g_to_status_closed
,line_status)
WHERE line_id = l_mol_id;
debug('# of MOL updated/closed:'||SQL%rowcount,l_proc_name,1);
--delete MTLT
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_tempid_tab(i);
--delete MMTT
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_tempid_tab(i);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = l_mol_id;
debug('No MOL updated. SQLERRM:'||Sqlerrm,l_proc_name,1);
debug('# of MOL with process_flag = 2 updated:'||SQL%rowcount,l_proc_name,1);
-- AND then delete the dispatched task. Otherwise, we can just
-- call complete_operation_instance
IF (p_call_type = g_ct_inspect_b4_tm) THEN
IF (l_debug = 1) THEN
DEBUG('Calling DELETE_DISPATCHED_TASK ...',l_proc_name,9);
WMS_OP_RUNTIME_PVT_APIS.delete_dispatched_task
( p_source_task_id => l_tempid_tab(i)
,p_wms_task_type => WMS_GLOBALS.G_WMS_TASK_TYPE_INSPECT
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
DEBUG('Error in Delete_dispatched_tasks ' ,l_proc_name,1);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id IN (SELECT move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_tempid_tab(i))
AND wms_process_flag = 2;
debug('No MOL updated. SQLERRM:'||Sqlerrm,l_proc_name,1);
debug('# of MOL with process_flag = 2 updated:'||SQL%rowcount,l_proc_name,1);
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id <> p_lpn_id
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT lpn_context,subinventory_code,locator_id
INTO x_lpn_context,l_lpn_sub,l_lpn_loc
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT DISTINCT 'Y'
, wooi.last_updated_by
INTO l_drop_active
, l_userid
FROM mtl_txn_request_lines mtrl,
mtl_material_transactions_temp mmtt,
wms_op_operation_instances wooi ,
(SELECT wlpn.lpn_id /*5723418*/
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id) wlpn
WHERE mtrl.lpn_id = wlpn.lpn_id
AND mmtt.move_order_line_id = mtrl.line_id
AND wooi.source_task_id = mmtt.transaction_temp_id
AND wooi.operation_status = 2
AND wooi.operation_type_id = 2
AND wooi.last_updated_by <> p_user_id;
SELECT user_name
INTO l_username
FROM fnd_user
WHERE user_id = l_userid;
SELECT 'Y','Y'
INTO l_content_lpn,l_lpn_has_contents
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id
AND ROWNUM<2;
SELECT 'Y' INTO x_loaded_status FROM dual where exists
(SELECT 1 from
wms_dispatched_tasks W,
mtl_material_transactions_temp t,
mtl_txn_request_lines M
WHERE m.lpn_id = p_lpn_id
AND m.organization_id = p_org_id
AND m.line_status <> 5
AND m.quantity-Nvl(m.quantity_delivered,0)>0
AND t.move_order_line_id = m.line_id
AND t.organization_id = p_org_id
AND w.organization_id = p_org_id
AND t.transaction_temp_id = w.transaction_temp_id
AND w.status = 4);
FOR x IN (SELECT Sum(primary_quantity) wlc_qty,inventory_item_id,organization_id
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id AND organization_id = p_org_id
GROUP BY inventory_item_id,organization_id) LOOP
l_mmtt_qty := 0;
SELECT Sum(mmtt.primary_quantity) INTO l_mmtt_qty
FROM wms_dispatched_tasks W,
mtl_material_transactions_temp mmtt,
mtl_txn_request_lines M
WHERE m.lpn_id = p_lpn_id
AND m.organization_id = x.organization_id
AND m.line_status <> 5
AND m.quantity-Nvl(m.quantity_delivered,0)>0
AND mmtt.move_order_line_id = m.line_id
AND mmtt.organization_id = x.organization_id
AND w.organization_id = x.organization_id
AND mmtt.transaction_temp_id = w.transaction_temp_id
AND mmtt.inventory_item_id = x.inventory_item_id
AND w.status = 4;
SELECT 'Z' INTO x_loaded_status FROM dual where exists -- End of changes for the bug 5768339.
(SELECT 1 FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
AND mmtt.organization_id = wdt.organization_id
AND wdt.status = 4
AND wdt.organization_id = p_org_id
AND (
((mmtt.lpn_id = p_lpn_id) AND (mmtt.transfer_lpn_id IS NOT NULL))
OR
((mmtt.content_lpn_id = p_lpn_id) AND (mmtt.transfer_lpn_id IS NOT NULL))
));
SELECT COUNT(1) INTO l_invalid_cnt
FROM Mtl_material_transactions_temp mmtt,
Wms_License_Plate_Numbers wlpn
WHERE mmtt.organization_id=p_org_id
AND mmtt.wms_task_type <> WMS_GLOBALS.g_wms_task_type_putaway
AND wlpn.outermost_lpn_id = p_lpn_id
AND wlpn.organization_id = p_org_id
AND mmtt.allocated_lpn_id = wlpn.lpn_id
AND NOT EXISTS ( SELECT 1 FROM Wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.status >= G_TASK_STATUS_LOADED ) ;
SELECT COUNT(1) INTO l_invalid_cnt
FROM wms_loaded_quantities_v wlqv
WHERE wlqv.organization_id = p_org_id
AND NVL(wlqv.content_lpn_id , wlqv.lpn_id) = p_lpn_id ;
SELECT 'Y','Y'
INTO l_content_lpn,l_lpn_has_contents
FROM wms_lpn_contents
WHERE parent_lpn_id = l_lpn_id
AND ROWNUM<2;
SELECT 'Y' INTO x_loaded_status FROM dual where exists
(select 1 from
WMS_DISPATCHED_TASKS W, MTL_MATERIAL_TRANSACTIONS_TEMP T
WHERE T.lpn_id = l_lpn_id
AND t.organization_id = p_org_id
AND w.organization_id = p_org_id
AND T.transaction_temp_id = W.transaction_temp_id
AND W.STATUS = 4
AND W.TASK_TYPE = WMS_GLOBALS.g_wms_task_type_putaway); --Bug#6920388.Added putaway check
SELECT lpn_context INTO l_lpn_context
FROM Wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id=p_lpn_id
AND wlpn.organization_id=p_org_id;
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT 'Y' INTO l_is_content_lpn
FROM wms_lpn_contents
WHERE parent_lpn_id = l_lpn_id
AND ROWNUM<2;
select count(1) INTO l_wlc_without_mmtt from
(SELECT lpn_id FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id ) wlpn ,
wms_lpn_contents wlc
where
wlc.parent_lpn_id = wlpn.lpn_id
and not exists (select 1 from mtl_material_transactions_temp mmtt
where mmtt.organization_id = wlc.organization_id
AND mmtt.inventory_item_id = wlc.inventory_item_id
AND (mmtt.lpn_id = wlc.parent_lpn_id or mmtt.content_lpn_id = wlc.parent_lpn_id));
fnd_msg_pub.delete_msg(i);
l_ret := inv_trx_util_pub.insert_line_trx(
p_trx_hdr_id => p_trx_header_id
, p_item_id => '0'
, p_org_id => p_org_id
, p_trx_action_id => inv_globals.g_action_containerpack
, p_subinv_code => p_sub_code
, p_locator_id => p_loc_id
, p_trx_type_id => inv_globals.g_type_container_pack
, p_trx_src_type_id => inv_globals.g_sourcetype_inventory
, p_trx_qty => 1
, p_pri_qty => 1
, p_uom => 'Ea'
, p_user_id => p_user_id
, p_from_lpn_id => NULL
, p_cnt_lpn_id => p_from_lpn_id
, p_xfr_lpn_id => p_to_lpn_id
, x_trx_tmp_id => l_txn_temp_id
, x_proc_msg => x_msg_data
, p_project_id => p_project_id
, p_task_id => p_task_id);
l_ret := inv_trx_util_pub.insert_line_trx(
p_trx_hdr_id => p_trx_header_id
, p_item_id => '-1'
, p_org_id => p_org_id
, p_trx_action_id => inv_globals.g_action_containerunpack
, p_subinv_code => p_sub_code
, p_locator_id => p_loc_id
, p_trx_type_id => inv_globals.g_type_container_unpack
, p_trx_src_type_id => inv_globals.g_sourcetype_inventory
, p_trx_qty => 1
, p_pri_qty => 1
, p_uom => 'Ea'
, p_user_id => p_user_id
, p_from_lpn_id => p_from_lpn_id
, p_cnt_lpn_id => p_to_lpn_id
, p_xfr_lpn_id => NULL
, x_trx_tmp_id => l_txn_temp_id
, x_proc_msg => x_msg_data
, p_project_id => p_project_id
, p_task_id => p_task_id);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = p_trx_header_id
, transaction_batch_id = p_batch_id
, transaction_batch_seq = p_batch_seq
WHERE transaction_temp_id = l_txn_temp_id;
END IF; -- Update batch id
SELECT parent_lpn_id,lpn_context
INTO l_parent_lpn_id,l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_from_lpn_id;
SELECT lpn_context,subinventory_code,locator_id
INTO l_to_context,l_to_sub,l_to_loc
FROM wms_license_plate_numbers
WHERE lpn_id = p_to_lpn_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_trx_header_id
FROM dual;
l_ret := inv_trx_util_pub.insert_line_trx
(
p_trx_hdr_id => l_trx_header_id
, p_item_id => '0'
, p_org_id => p_org_id
, p_trx_action_id => 2
, p_subinv_code => p_sub_code
, p_tosubinv_code => l_to_sub
, p_locator_id => p_loc_id
, p_tolocator_id => l_to_loc
, p_trx_type_id => 2
, p_trx_src_type_id => 13
, p_trx_qty => 1
, p_pri_qty => 1
, p_uom => 'Ea'
, p_user_id => p_user_id
, p_from_lpn_id => NULL
, p_cnt_lpn_id => p_from_lpn_id
, p_xfr_lpn_id => NULL
, x_trx_tmp_id => l_txn_temp_id
, x_proc_msg => x_msg_data
, p_project_id => p_project_id
, p_task_id => p_task_id);
debug('Error inserting MMTT ID: ' ||
l_txn_temp_id,9);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_trx_header_id
, transaction_batch_id = l_trx_header_id
, transaction_batch_seq = 2
WHERE transaction_temp_id = l_txn_temp_id;
l_group_id := insert_rti
(p_from_org => p_org_id
,p_lpn_id => p_from_lpn_id
,p_to_org => p_org_id -- same as from lpn
,p_to_sub => l_to_sub
,p_to_loc => l_to_loc
,p_xfer_lpn_id => p_from_lpn_id
,p_first_time => 1
,p_mobile_txn => 'Y'
,p_txn_mode_code => 'ONLINE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
debug('Error inserting RTI');
debug('RTI inserted with group_id:'|| l_group_id, 9);
inv_rcv_integration_apis.insert_wlpni
(p_api_version => 1.0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => p_org_id
,p_lpn_id => p_from_lpn_id
,p_license_plate_number => NULL
,p_lpn_group_id => l_group_id
,p_parent_lpn_id => p_to_lpn_id
);
SELECT /*+ ORDERED INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */ 1
INTO l_discrepancy
FROM
(SELECT wlpn.lpn_id /*5723418*/
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_from_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id) wlpn,
mtl_txn_request_lines mtrl -- Bug 14372071
WHERE mtrl.lpn_id = wlpn.lpn_id
AND mtrl.line_status = 7
AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) <> Nvl(mtrl.quantity_detailed,0)
AND mtrl.organization_id = p_org_id;
fnd_msg_pub.delete_msg(i);
SELECT mmtt.transaction_temp_id
bulk collect INTO l_mmtt_ids
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.move_order_line_id IN
( SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl ,
(SELECT wlpn.lpn_id /* 5723418 */
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_from_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id) wlpn
WHERE mtrl.organization_id = p_org_id
AND mtrl.lpn_id = wlpn.lpn_id );
SELECT employee_id
INTO l_emp_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT employee_id
INTO l_emp_id
FROM fnd_user
WHERE user_id = p_user_id;
PROCEDURE update_mo(
MoLineId NUMBER,
ReferenceId NUMBER,
Reference VARCHAR2,
Reference_type_code NUMBER,
lpn_id NUMBER,
wms_process_flag NUMBER,
inspect_status NUMBER)
IS
BEGIN
UPDATE mtl_txn_request_lines
SET Reference_id = decode(ReferenceId,-9999,referenceId,NULL,NULL,referenceId),
Reference = decode(Reference,'-9999',reference,NULL,NULL,reference),
Reference_type_code = decode(Reference_type_code,'-9999',reference_type_code,NULL,NULL,reference_type_code),
lpn_id = decode(lpn_id,-9999,lpn_id,NULL,NULL,lpn_id),
wms_process_flag = decode(wms_process_flag,-9999,wms_process_flag,NULL,NULL,wms_process_flag),
inspection_status = decode(inspect_status,-9999,inspect_status,NULL,null,inspect_status)
WHERE line_id = MoLineid;
FUNCTION insert_rti(p_from_org IN NUMBER
,p_lpn_id IN NUMBER
,p_to_org IN NUMBER
,p_to_sub IN VARCHAR2
,p_to_loc IN NUMBER
,p_xfer_lpn_id IN NUMBER
,p_first_time IN NUMBER
,p_mobile_txn IN VARCHAR2
,p_txn_mode_code IN VARCHAR2
,x_return_status OUT nocopy VARCHAR2
,x_msg_count OUT nocopy NUMBER
,x_msg_data OUT nocopy VARCHAR2 )
RETURN NUMBER
IS
l_rowid VARCHAR2(40);
DEBUG('Entering...', 'insert_rti', 9);
debug(' (p_to_sub => ' || p_to_sub, 'insert_rti', 9);
debug(' p_from_org => ' || p_from_org, 'insert_rti', 9);
debug(' p_to_org => ' || p_to_org, 'insert_rti', 9);
debug(' p_to_loc => ' || p_to_loc, 'insert_rti', 9);
debug(' p_lpn_id => ' || p_lpn_id, 'insert_rti', 9);
debug(' p_xfer_lpn_id => ' || p_xfer_lpn_id, 'insert_rti', 9);
debug(' p_first_time => ' || p_first_time, 'insert_rti', 9);
debug(' p_mobile_txn => ' || p_mobile_txn, 'insert_rti', 9);
debug(' p_txn_mode_code => ' || p_txn_mode_code||')', 'insert_rti', 9);
debug('Calling init_startup_values','insert_rti',9);
debug(' p_org_id => '||l_from_org, 'insert_rti',9);
SELECT TO_NUMBER(hoi.org_information1)
INTO inv_rcv_common_apis.g_po_startup_value.sob_id
FROM hr_organization_information hoi
WHERE hoi.organization_id = l_from_org
AND (hoi.org_information_context || '') = 'Accounting Information';
,'insert_rti', 9);
debug('Date validation failed','insert_rti',9);
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_interface_transaction_id
FROM dual;
DEBUG('Calling rcv_trx_interface_insert_pkg.insert_row',
'insert_rti', 9);
SELECT org_information2
INTO l_operating_unit_id
FROM hr_organization_information
WHERE organization_id = l_to_org
AND org_information_context || '' ='Accounting Information'; --
rcv_trx_interface_insert_pkg.insert_row
(
x_rowid => l_rowid
,x_interface_transaction_id => l_interface_transaction_id
,x_group_id => l_group_id
,x_last_update_date => l_sysdate
,x_last_updated_by => l_user_id
,x_creation_date => l_sysdate
,x_created_by => l_user_id
,x_last_update_login => l_login_id
,x_transaction_type => 'TRANSFER'
,x_transaction_date => l_sysdate
,x_processing_status_code => 'PENDING'
,x_processing_mode_code => l_txn_mode_code
,x_processing_request_id => NULL
,x_transaction_status_code => 'PENDING'
,x_category_id => NULL
,x_quantity => 0
,x_unit_of_measure => 'X'
,x_interface_source_code => 'RCV'
,x_interface_source_line_id => NULL
,x_inv_transaction_id => NULL
,x_item_id => NULL
,x_item_description => NULL
,x_item_revision => NULL
,x_uom_code => NULL
,x_employee_id => l_user_id
,x_auto_transact_code => NULL
,x_shipment_header_id => NULL
,x_shipment_line_id => NULL
,x_ship_to_location_id => NULL
,x_primary_quantity => NULL
,x_primary_unit_of_measure => NULL
,x_receipt_source_code => NULL
,x_vendor_id => NULL
,x_vendor_site_id => NULL
,x_from_organization_id => l_from_org
,x_to_organization_id => l_to_org
,x_routing_header_id => NULL
,x_routing_step_id => NULL
,x_source_document_code => NULL
,x_parent_transaction_id => NULL
,x_po_header_id => NULL
,x_po_revision_num => NULL
,x_po_release_id => NULL
,x_po_line_id => NULL
,x_po_line_location_id => NULL
,x_po_unit_price => NULL
,x_currency_code => NULL
,x_currency_conversion_type => NULL
,x_currency_conversion_rate => NULL
,x_currency_conversion_date => NULL
,x_po_distribution_id => NULL
,x_requisition_line_id => NULL
,x_req_distribution_id => NULL
,x_charge_account_id => NULL
,x_substitute_unordered_code => NULL
,x_receipt_exception_flag => NULL
,x_accrual_status_code => NULL
,x_inspection_status_code => NULL
,x_inspection_quality_code => NULL
,x_destination_type_code => 'RECEIVING'
,x_deliver_to_person_id => NULL
,x_location_id => NULL
,x_deliver_to_location_id => NULL
,x_subinventory => l_to_sub
,x_locator_id => l_to_loc
,x_wip_entity_id => NULL
,x_wip_line_id => NULL
,x_department_code => NULL
,x_wip_repetitive_schedule_id=> NULL
,x_wip_operation_seq_num => NULL
,x_wip_resource_seq_num => NULL
,x_bom_resource_id => NULL
,x_shipment_num => NULL
,x_freight_carrier_code => NULL
,x_bill_of_lading => NULL
,x_packing_slip => NULL
,x_shipped_date => NULL
,x_expected_receipt_date => NULL
,x_actual_cost => NULL
,x_transfer_cost => NULL
,x_transportation_cost => NULL
,x_transportation_account_id => NULL
,x_num_of_containers => NULL
,x_waybill_airbill_num => NULL
,x_vendor_item_num => NULL
,x_vendor_lot_num => NULL
,x_rma_reference => NULL
,x_comments => NULL
,x_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_ship_head_attribute_category => NULL
,x_ship_head_attribute1 => NULL
,x_ship_head_attribute2 => NULL
,x_ship_head_attribute3 => NULL
,x_ship_head_attribute4 => NULL
,x_ship_head_attribute5 => NULL
,x_ship_head_attribute6 => NULL
,x_ship_head_attribute7 => NULL
,x_ship_head_attribute8 => NULL
,x_ship_head_attribute9 => NULL
,x_ship_head_attribute10 => NULL
,x_ship_head_attribute11 => NULL
,x_ship_head_attribute12 => NULL
,x_ship_head_attribute13 => NULL
,x_ship_head_attribute14 => NULL
,x_ship_head_attribute15 => NULL
,x_ship_line_attribute_category => NULL
,x_ship_line_attribute1 => NULL
,x_ship_line_attribute2 => NULL
,x_ship_line_attribute3 => NULL
,x_ship_line_attribute4 => NULL
,x_ship_line_attribute5 => NULL
,x_ship_line_attribute6 => NULL
,x_ship_line_attribute7 => NULL
,x_ship_line_attribute8 => NULL
,x_ship_line_attribute9 => NULL
,x_ship_line_attribute10 => NULL
,x_ship_line_attribute11 => NULL
,x_ship_line_attribute12 => NULL
,x_ship_line_attribute13 => NULL
,x_ship_line_attribute14 => NULL
,x_ship_line_attribute15 => NULL
,x_ussgl_transaction_code => NULL
,x_government_context => NULL
,x_reason_id => NULL
,x_destination_context => NULL
,x_source_doc_quantity => NULL
,x_source_doc_unit_of_measure=> NULL
,x_lot_number_cc => NULL
,x_serial_number_cc => NULL
,x_qa_collection_id => NULL
,x_country_of_origin_code => NULL
,x_oe_order_header_id => NULL
,x_oe_order_line_id => NULL
,x_customer_item_num => NULL
,x_customer_id => NULL
,x_customer_site_id => NULL
,x_put_away_rule_id => NULL
,x_put_away_strategy_id => NULL
,x_lpn_id => l_lpn_id
,x_transfer_lpn_id => l_xfer_lpn_id
,x_cost_group_id => NULL
,x_mmtt_temp_id => NULL
,x_mobile_txn => p_mobile_txn
,x_transfer_cost_group_id => NULL
,x_secondary_quantity => NULL
,x_secondary_unit_of_measure => NULL
,p_org_id => l_operating_unit_id --
);
UPDATE
rcv_transactions_interface
SET
validation_flag = 'Y'
,lpn_group_id = l_group_id
WHERE ROWID = l_rowid;
DEBUG('Quiting with group_id = '||l_group_id, 'insert_rti', 9);
'insert_rti',
9);
debug(SQLCODE, 'insert_rti', 9);
END insert_rti;
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE parent_lpn_id = p_from_lpn_id;
SELECT parent_lpn_id
,subinventory_code
,locator_id
INTO p_parent_lpn_id
,l_lpn_sub
,l_lpn_loc
FROM wms_license_plate_numbers
WHERE lpn_id = p_from_lpn_id;
SELECT rcv_interface_groups_s.NEXTVAL
INTO l_group_id
FROM DUAL;
inv_rcv_integration_apis.insert_wlpni(
p_api_version => 1.0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => p_organization_id
,p_lpn_id => p_from_lpn_id
,p_license_plate_number => NULL
,p_lpn_group_id => l_group_id
,p_parent_lpn_id => l_to_lpn_id);
inv_rcv_integration_apis.insert_wlpni(
p_api_version => 1.0
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => p_organization_id
,p_lpn_id => l_lpn_id
,p_license_plate_number => NULL
,p_lpn_group_id => l_group_id
,p_parent_lpn_id => p_to_lpn_id);
SELECT lpn_id
FROM wms_license_plate_numbers
WHERE parent_lpn_id = p_from_lpn_id;
SELECT
wlpnc.inventory_item_id
,wlpnc.organization_id
,wlpnc.revision
,wlpnc.lot_number
,wlpnc.quantity
,wlpnc.uom_code
,wlpnc.secondary_quantity -- Added for Bug 10301152
,wlpnc.secondary_uom_code -- Added for Bug 10301152
,msi.serial_number_control_code
,msi.primary_uom_code
FROM
wms_lpn_contents wlpnc, mtl_system_items msi
WHERE
wlpnc.parent_lpn_id = p_from_lpn_id
AND wlpnc.inventory_item_id = msi.inventory_item_id
AND wlpnc.organization_id = msi.organization_id;
SELECT employee_id
INTO l_emp_id
FROM fnd_user
WHERE user_id = l_user_id;
SELECT
lpn_context from_context
,organization_id organization_id
,subinventory_code
,locator_id
INTO
l_from_context
,l_org_id
,l_sub_code
,l_loc_id
FROM
wms_license_plate_numbers
WHERE
lpn_id = p_from_lpn_id;
SELECT
lpn_context
,subinventory_code
,locator_id
INTO
l_into_context
,l_to_sub
,l_to_loc
FROM
wms_license_plate_numbers
WHERE
lpn_id = p_into_lpn_id;
SELECT /*+ ORDERED INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */ 1
INTO l_count
FROM
(SELECT wlpn.lpn_id /*5723418*/
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_from_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn,
mtl_txn_request_lines mtrl -- Bug 14372071
WHERE mtrl.lpn_id = wlpn.lpn_id
AND mtrl.line_status = 7
AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) <> Nvl(mtrl.quantity_detailed,0)
AND mtrl.organization_id = l_org_id;
fnd_msg_pub.delete_msg(i);
SELECT
mmtt.transaction_temp_id
BULK COLLECT INTO
l_txn_tmp_id_tb
FROM mtl_material_transactions_temp mmtt
WHERE -- suggestion mmtts?
( ( mmtt.transaction_source_type_id = 1 AND
mmtt.transaction_action_id = 27) OR
( mmtt.transaction_source_type_id = 7 AND
mmtt.transaction_action_id = 12) OR
( mmtt.transaction_source_type_id = 12 AND
mmtt.transaction_action_id = 27) OR
( mmtt.transaction_source_type_id = 13 AND
mmtt.transaction_action_id = 12) OR
( mmtt.transaction_source_type_id = 4 AND
mmtt.transaction_action_id = 27)) AND
mmtt.organization_id = l_org_id AND
mmtt.move_order_line_id --BUG 3435079: use org_id for performace reason
IN ( SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl,
( SELECT wlpn.lpn_id /*5723418*/
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_from_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
WHERE mtrl.organization_id = l_org_id
--BUG 3435079: use org_id for performace reason
AND mtrl.lpn_id = wlpn.lpn_id );
DEBUG('Unexpected Exception Raised in Bulk Select'
,'transfer_contents', 9);
SELECT
mmtt.transaction_temp_id
BULK COLLECT INTO
l_txn_tmp_id_tb
FROM mtl_material_transactions_temp mmtt
WHERE -- suggestion mmtts?
mmtt.organization_id = l_org_id AND
mmtt.move_order_line_id --BUG 3435079: use org_id for performace reason
IN ( SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl ,
(SELECT wlpn.lpn_id /*5723418*/
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_from_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
WHERE mtrl.organization_id = l_org_id AND --BUG 3435079: use org_id for performace reason
mtrl.lpn_id = wlpn.lpn_id );
DEBUG('Unexpected Exception Raised in Bulk Select'
,'transfer_contents', 9);
SELECT COUNT(lpn_content_id)
INTO l_count
FROM wms_lpn_contents
WHERE parent_lpn_id = p_from_lpn_id;
' entries of wlpnc. Inserting rti...','transfer_contents',9);
l_return_status := insert_rti
(p_from_org => l_org_id
,p_lpn_id => p_from_lpn_id
,p_to_org => l_org_id
,p_to_sub => l_to_sub
,p_to_loc => l_to_loc
,p_xfer_lpn_id => p_into_lpn_id
,p_first_time => 1
,p_mobile_txn => 'Y'
,p_txn_mode_code => 'ONLINE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
l_return_status := insert_rti
(p_from_org => l_org_id
,p_lpn_id => p_from_lpn_id
,p_to_org => l_org_id
,p_to_sub => l_sub_code
,p_to_loc => l_loc_id
,p_xfer_lpn_id => p_into_lpn_id
,p_first_time => 1
,p_mobile_txn => 'Y'
,p_txn_mode_code => 'ONLINE'
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
DEBUG('xxx: Error inserting_rti','transfer_contents',9);
UPDATE
mtl_txn_request_lines
SET
wms_process_flag = 2
WHERE
lpn_id IN (SELECT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_from_lpn_id
CONNECT BY PRIOR wlpn.lpn_id =
wlpn.parent_lpn_id);
SELECT lpn_id
bulk collect INTO l_lpn_ids
FROM wms_license_plate_numbers
WHERE parent_lpn_id = p_from_lpn_id;
l_serial_numbers.DELETE;
SELECT serial_number
bulk collect INTO l_serial_numbers
FROM mtl_serial_numbers
WHERE lpn_id = p_from_lpn_id
AND inventory_item_id = l_wlpnc_rec.inventory_item_id
AND Nvl(lot_number, '@@@') = Nvl(l_wlpnc_rec.lot_number, '@@@')
AND Nvl(revision, '@@@') = Nvl(l_wlpnc_rec.revision,'@@@');
SELECT lpn_id
bulk collect INTO l_lpn_ids
FROM wms_license_plate_numbers
WHERE parent_lpn_id = p_from_lpn_id;
UPDATE
mtl_material_transactions_temp
SET
lpn_id = p_into_lpn_id
WHERE
lpn_id = p_from_lpn_id;
UPDATE
mtl_txn_request_lines
SET
lpn_id = p_into_lpn_id
WHERE
lpn_id = p_from_lpn_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_hdr_id
FROM dual;
l_return_status := inv_trx_util_pub.insert_line_trx
(
p_trx_hdr_id => l_hdr_id
, p_item_id => '0'
, p_org_id => l_org_id
, p_trx_action_id => 2
, p_subinv_code => l_sub_code
, p_tosubinv_code => l_to_sub
, p_locator_id => l_loc_id
, p_tolocator_id => l_to_loc
, p_trx_type_id => 2
, p_trx_src_type_id => 13
, p_trx_qty => 1
, p_pri_qty => 1
, p_uom => 'Ea'
, p_user_id => l_user_id
, p_from_lpn_id => NULL
, p_cnt_lpn_id => p_from_lpn_id
, p_xfr_lpn_id => NULL
, x_trx_tmp_id => l_txn_tmp_id
, x_proc_msg => x_msg_data
, p_project_id => p_project_id
, p_task_id => p_task_id);
debug('Error inserting MMTT ID: ' ||
l_txn_tmp_id,'transfer_contents',9);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_hdr_id
, transaction_batch_id = l_hdr_id
, transaction_batch_seq = l_batch_seq
WHERE transaction_temp_id = l_txn_tmp_id;
SELECT lpn_id
bulk collect INTO l_lpn_ids
FROM wms_license_plate_numbers
WHERE parent_lpn_id = p_from_lpn_id;
DEBUG('Inserting MMTT for lpn: ' || l_lpn_ids(i),
'transfer_contents',
9);
l_return_Status := inv_trx_util_pub.insert_line_trx
(
p_trx_hdr_id => l_hdr_id
, p_item_id => -1
, p_org_id => l_org_id
, p_trx_action_id => inv_globals.g_action_containersplit
, p_subinv_code => l_sub_code
, p_locator_id => l_loc_id
, p_trx_type_id => inv_globals.g_type_container_split
, p_trx_src_type_id => inv_globals.g_sourcetype_inventory
, p_trx_qty => 1
, p_pri_qty => 1
, p_uom => 'X'
, p_user_id => l_user_id --??
, p_from_lpn_id => p_from_lpn_id
, p_cnt_lpn_id => l_lpn_ids(i)
, p_xfr_lpn_id => p_into_lpn_id
, x_trx_tmp_id => l_txn_tmp_id
, x_proc_msg => x_msg_data );
debug('Error inserting MMTT', 'transfer_contents', 9);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_hdr_id
, transaction_batch_id = l_hdr_id
, transaction_batch_seq = l_batch_seq
WHERE transaction_temp_id = l_txn_tmp_id;
debug('Inserting MMTT...', 'transfer_contents', 9);
l_return_status := inv_trx_util_pub.insert_line_trx
(
p_trx_hdr_id => l_hdr_id
, p_item_id => l_wlpnc_rec.inventory_item_id
, p_revision => l_wlpnc_rec.revision
, p_org_id => l_org_id
, p_trx_action_id => inv_globals.g_action_containersplit
, p_subinv_code => l_sub_code
, p_locator_id => l_loc_id
, p_trx_type_id => inv_globals.g_type_container_split
, p_trx_src_type_id => inv_globals.g_sourcetype_inventory
, p_trx_qty => l_wlpnc_rec.quantity
, p_pri_qty => l_prim_qty
, p_uom => l_wlpnc_rec.uom_code
, p_user_id => l_user_id --??
, p_from_lpn_id => p_from_lpn_id
, p_xfr_lpn_id => p_into_lpn_id
, x_trx_tmp_id => l_txn_tmp_id
, p_secondary_trx_qty => l_wlpnc_rec.secondary_quantity -- Added for Bug 10301152
, p_secondary_uom => l_wlpnc_rec.secondary_uom_code -- Added for Bug 10301152
, x_proc_msg => x_msg_data);
debug('Error inserting MMTT', 'transfer_contents', 9);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_hdr_id
, transaction_batch_id = l_hdr_id
, transaction_batch_seq = l_batch_seq
WHERE transaction_temp_id = l_txn_tmp_id;
debug('MMTT:' || l_txn_tmp_id || 'Inserted', 'transfer_contents', 9);
l_return_status := inv_trx_util_pub.insert_lot_trx
( p_trx_tmp_id => l_txn_tmp_id
,p_user_id => l_user_id
,p_lot_number => l_wlpnc_rec.lot_number
,p_trx_qty => l_wlpnc_rec.quantity
,p_pri_qty => l_prim_qty
,x_ser_trx_id => l_ser_trx_id
,x_proc_msg => x_msg_data
,p_secondary_qty => l_wlpnc_rec.secondary_quantity -- Added for Bug 14671760
,p_secondary_uom => l_wlpnc_rec.secondary_uom_code); -- Added for Bug 14671760
debug('Error inserting MTLT', 'transfer_contents', 9);
l_serial_numbers.DELETE;
SELECT serial_number
bulk collect INTO l_serial_numbers
FROM mtl_serial_numbers
WHERE lpn_id = p_from_lpn_id
AND inventory_item_id = l_wlpnc_rec.inventory_item_id
AND Nvl(lot_number, '@@@') = Nvl(l_wlpnc_rec.lot_number, '@@@')
AND Nvl(revision, '@@@') = Nvl(l_wlpnc_rec.revision,'@@@');
l_return_status := inv_trx_util_pub.insert_ser_trx
(p_trx_tmp_id => l_txn_tmp_id
,p_user_id => l_user_id
,p_fm_ser_num=> l_serial_numbers(i)
,p_to_ser_num=> l_serial_numbers(i)
,x_proc_msg => x_msg_data );
debug('Error inserting MSNT', 'transfer_contents', 9);
UPDATE
mtl_material_transactions_temp
SET
lpn_id = p_into_lpn_id
WHERE
lpn_id = p_from_lpn_id;
UPDATE
mtl_txn_request_lines
SET
lpn_id = p_into_lpn_id
WHERE
lpn_id = p_from_lpn_id;
SELECT mtrl.line_id
, mtrl.quantity
, NVL(mtrl.quantity_detailed, 0)
, NVL(mtrl.quantity_delivered, 0)
, mtrl.uom_code
, mtrl.secondary_quantity --OPM Convergence
, NVL(mtrl.secondary_quantity_detailed, 0) --OPM Convergence
, NVL(mtrl.secondary_quantity_delivered, 0) --OPM Convergence
, mtrl.secondary_uom_code --OPM Convergence
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id IN (SELECT DISTINCT gtmp.move_order_line_id
FROM wms_putaway_group_tasks_gtmp gtmp
WHERE gtmp.group_id = p_group_id
AND gtmp.transaction_header_id = p_txn_header_id
AND gtmp.row_type = g_row_tp_all_task)
AND mtrl.line_status <> 5 ; -- 9037915
SELECT mtrl.line_id
, mtrl.quantity
, NVL(mtrl.quantity_detailed, 0)
, NVL(mtrl.quantity_delivered, 0)
, mtrl.uom_code
, mtrl.secondary_quantity --OPM Convergence
, NVL(mtrl.secondary_quantity_detailed, 0) --OPM Convergence
, NVL(mtrl.secondary_quantity_delivered, 0) --OPM Convergence
, mtrl.secondary_uom_code --OPM Convergence
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id IN (SELECT DISTINCT gtmp.move_order_line_id
FROM wms_putaway_group_tasks_gtmp gtmp
WHERE gtmp.group_id = p_group_id
AND gtmp.transaction_header_id = p_txn_header_id
AND gtmp.row_type = g_row_tp_all_task)
AND Nvl(mtrl.lot_number,'&*_') = Nvl(v_lot_number,'&*_')
AND Nvl(mtrl.inspection_status,-1) = Nvl(v_inspect_status,-1);
SELECT transaction_temp_id
, lpn_id
, move_order_line_id
, inventory_item_id
, revision
, lot_number
, transaction_quantity
, transaction_uom
, txn_source_id
, backorder_delivery_detail
, crossdock_type
, wip_supply_type
, secondary_quantity -- OPM Convergence
, secondary_uom -- 9037915
, inspection_status
, primary_uom_code
FROM wms_putaway_group_tasks_gtmp
WHERE group_id = p_group_id
AND transaction_header_id = p_txn_header_id
AND row_type = G_ROW_TP_ALL_TASK
AND ( (v_disc = 'N') --no qty idsc
OR
(v_disc = 'Y' AND p_drop_type = G_DT_ITEM_DROP
AND process_flag = 'Y' --item drop and qty disc
)
);
SELECT mmtt.transaction_temp_id
, mtrl.lpn_id
, mtrl.line_id
, mmtt.inventory_item_id
, mmtt.revision
, mtrl.lot_number
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mtrl.txn_source_id
, mtrl.backorder_delivery_detail_id
, mtrl.crossdock_type
, mmtt.wip_supply_type
, mmtt.secondary_transaction_quantity --OPM Convergence
, mmtt.secondary_uom_code -- 9037915
, mtrl.inspection_status
, msi.primary_uom_code
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
, mtl_system_items_kfv msi
, ( /*5723418*/
SELECT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id
) wlpn
WHERE mtrh.organization_id = p_organization_id
AND mtrh.move_order_type = 6
AND mtrl.header_id = mtrh.header_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_status = 7
AND mtrl.lpn_id = wlpn.lpn_id
AND msi.inventory_item_id = mtrl.inventory_item_id
AND msi.organization_id = mtrl.organization_id;
SELECT msn.serial_number
, 'N'
, msn.inspection_status
, msn.lot_number
FROM mtl_serial_numbers_interface msni
, mtl_serial_numbers msn
, rcv_serials_supply rss
WHERE msni.transaction_interface_id = p_msni_txn_interface_id
AND msn.serial_number BETWEEN msni.fm_serial_number AND Nvl(msni.to_serial_number, msni.fm_serial_number)
AND Length(msn.serial_number) = Length(msni.fm_serial_number)
AND msn.inventory_item_id = p_item_id
AND msn.current_organization_id = p_organization_id
AND msn.lpn_id = p_lpn_id
AND rss.serial_num = msn.serial_number
AND rss.supply_type_code = 'RECEIVING'
ORDER BY msn.serial_number,msn.lot_number;
SELECT serial_number
, 'N'
FROM mtl_serial_numbers
WHERE inventory_item_id = v_item_id
AND lpn_id = v_lpn_id
AND (
(p_revision IS NOT NULL and revision = p_revision)
OR
(p_revision IS NULL)
)
AND (
(p_lot_number IS NOT NULL and lot_number = p_lot_number)
OR
(p_lot_number IS NULL)
)
AND group_mark_id = -4936;
l_ser_intf_id NUMBER; --Updated txn_interface_id for MSNI
l_del_count NUMBER; --No. of rows deleted in the temp table
SELECT employee_id
INTO l_emp_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT transaction_quantity
, transaction_uom
, primary_quantity
, primary_uom_code
, lot_control_code
, serial_number_control_code
, secondary_quantity --OPM Convergence
, secondary_uom --OPM Convergence
INTO l_sug_grp_quantity
, l_grp_uom_code
, l_grp_prm_quantity
, l_primary_uom_code
, l_lot_control_code
, l_serial_control_code
, l_grp_sec_quantity
, l_grp_sec_uom
FROM wms_putaway_group_tasks_gtmp
WHERE group_id = p_group_id
AND transaction_header_id = p_txn_header_id
AND row_type = G_ROW_TP_GROUP_TASK;
SELECT count(1)
INTO l_lpn_serials_cnt
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND inventory_item_id = p_item_id;
SELECT count(1), Nvl(msn.inspection_status,-1), Nvl(msn.lot_number,'&*_')
bulk collect INTO l_msni_qty_tbl, l_msni_inspect_status_tbl, l_msni_lot_tbl
FROM mtl_serial_numbers_interface msni, mtl_serial_numbers msn
WHERE msni.transaction_interface_id = p_msni_txn_interface_id
AND msn.serial_number BETWEEN msni.fm_serial_number AND Nvl(msni.to_serial_number,msni.fm_serial_number)
AND Length(msn.serial_number) = Length(msni.fm_serial_number)
GROUP BY msn.inspection_status, msn.lot_number;
UPDATE wms_putaway_group_tasks_gtmp
SET process_flag = 'Y'
WHERE group_id = p_group_id
AND transaction_header_id = p_txn_header_id
AND move_order_line_id = l_mol_line_id;
SELECT SUM(mmtt.primary_quantity),
SUM(mmtt.secondary_transaction_quantity) -- 9037915
INTO l_prim_qty_consumable,
l_sec_qty_consumable
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE mmtt.transaction_header_id = p_txn_header_id
AND mtrl.line_id = l_mol_line_id
AND mtrl.line_id = mmtt.move_order_line_id;
UPDATE wms_putaway_group_tasks_gtmp
SET process_flag = 'Y'
WHERE group_id = p_group_id
AND transaction_header_id = p_txn_header_id
AND move_order_line_id = l_mol_line_id;
SELECT NVL(lpn_controlled_flag, 1)
, NVL(subinventory_type, 1)
INTO l_lpn_controlled_flag
, l_drop_sub_type
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_txn_header_id
FROM DUAL;
DELETE FROM mtl_serial_numbers_interface
WHERE transaction_interface_id = p_msni_txn_interface_id;
debug('Number of MSNI deleted: '||SQL%rowcount,l_proc_name,9);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_dummy_temp_id
FROM dual;
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_product_transaction_id
FROM sys.dual;
SELECT expiration_date
, status_id
INTO l_lot_expiration_date
, l_lot_status_id
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND inventory_item_id = p_item_id
AND organization_id = p_organization_id;
inv_rcv_integration_apis.insert_mtli(
p_api_version => 1.0
, p_init_msg_lst => FND_API.G_FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_transaction_interface_id => l_dummy_temp_id --l_mmtt_temp_id
, p_lot_number => p_lot_number
, p_transaction_quantity => l_mmtt_qty_tbl(i)
, p_primary_quantity => l_mmtt_prm_qty
, p_organization_id => p_organization_id
, p_inventory_item_id => p_item_id
, p_expiration_date => l_lot_expiration_date
, p_status_id => l_lot_status_id
, x_serial_transaction_temp_id => l_serial_temp_id
, p_product_transaction_id => l_product_transaction_id
, p_product_code => 'RCV'
, p_att_exist => 'Y'
, p_update_mln => 'N'
, p_secondary_quantity => l_mmtt_sec_qty); --OPM CONVERGENCE
' insert_mtli returns g_exc_error', l_proc_name, 1);
' insert_mtli returns g_unexp_error', l_proc_name, 1);
DEBUG('complete_putaway_wrapper: Inserted MTLI intf_txn_id: ' || l_transaction_interface_id ||
', ser_temp_id : ' || l_serial_temp_id || ' , prod_txn_id: ' || l_product_transaction_id);
END IF; --END Insert MTLI for receiving LPN
--On matching the serial, we need to update the transaction_interface_id
--with the transaction_temp_id (for a serial and non-lot controlled item)
--or with the serial_transaction_temp_id of the MTLI (for lot and serial controlled item)
l_cur_ser_number := l_ser_num_tbl(j);
-- INSERT MSNI HERE
l_result := wms_task_dispatch_put_away.insert_msni_helper
(p_txn_if_id => l_ser_intf_id
, p_serial_number => l_cur_ser_number
, p_org_id => p_organization_id
, p_item_id => p_item_id
, p_product_txn_id => l_product_transaction_id
);
debug('Failure while Inserting MSNI records - lot and serial controlled item',l_proc_name);
DEBUG('Serials not entered from UI. Setting DISC flag to N so complete_putaway will insert the interface records',l_proc_name);
DEBUG('Serials entered from UI. Setting DISC flag to Y so that complete_putaway will not insert the interface records',l_proc_name);
SELECT count(1)
INTO l_mmtt_count
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_mol_line_id_tbl(i);
SELECT license_plate_number
INTO l_to_lpn_name
FROM wms_license_plate_numbers
WHERE lpn_id = l_mol_lpn_id_tbl(i);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_new_txn_header_id
, transaction_batch_id = l_new_txn_header_id
, transaction_batch_seq = l_batch_seq --BUG 3306988
WHERE transaction_temp_id = l_mmtt_temp_id;
--Need to update the drop_off_time on WDT for all the tasks
l_progress := '228';
UPDATE wms_dispatched_tasks
SET drop_off_time = l_drop_off_time
WHERE transaction_temp_id = l_mmtt_temp_id;
SELECT count(DISTINCT operation_plan_id) INTO l_lms_rec_count
FROM mtl_material_transactions_temp mmtt
WHERE transaction_header_id = p_txn_header_id;
SELECT operation_plan_id INTO x_lms_operation_plan_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_txn_header_id
AND ROWNUM = 1;
Update_Tasks_In_Group(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => l_msg_data
, x_rec_count => l_del_count
, p_organization_id => p_organization_id
, p_drop_type => p_drop_type
, p_lpn_id => p_lpn_id
, p_group_id => p_group_id
, p_emp_id => l_emp_id );
DEBUG('complete_putaway_wrapper: After calling Update_Tasks_In_Group');
DEBUG('status returned by Update_Tasks_In_Group ' || x_return_status, 4);
DEBUG('complete_putaway_wrapper: Encountered g_exc_error while calling Update_Tasks_In_Group;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
DEBUG('complete_putaway_wrapper: Encountered g_exc_unexp_error while calling Update_Tasks_In_Group;'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
SELECT lpn_context, subinventory_code, locator_id
INTO l_lpn_context, l_lpn_sub, l_lpn_loc_id
FROM wms_license_plate_numbers
WHERE organization_id = p_organization_id
AND lpn_id = p_lpn_id;
SELECT 1, lpn_id, subinventory_code, locator_id, lpn_context,outermost_lpn_id
INTO l_count, l_into_lpn_id, l_into_lpn_sub, l_into_lpn_loc_id, l_into_lpn_context,
l_into_outermost_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_into_lpn
AND organization_id = p_organization_id
FOR UPDATE OF lpn_id NOWAIT;
select NVL(count(1),0)
into l_lpn_has_material
FROM wms_lpn_contents
WHERE parent_lpn_id IN (SELECT wlpn1.lpn_id
FROM wms_license_plate_numbers wlpn1
START WITH wlpn1.lpn_id = l_into_outermost_lpn_id
CONNECT BY PRIOR wlpn1.lpn_id = wlpn1.parent_lpn_id);
SELECT 1
INTO l_count
FROM dual
WHERE EXISTS
(SELECT 'INTO_LPN_EXISTS'
FROM wms_license_plate_numbers wlpn
WHERE wlpn.organization_id = p_organization_id
AND wlpn.lpn_id <> p_lpn_id
AND wlpn.lpn_id = l_into_lpn_id
AND (wlpn.lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_PREGENERATED
OR (wlpn.lpn_context = l_lpn_context
AND (
( l_lpn_has_material = 0
--NOT EXISTS (SELECT 'LPN_HAS_MATERIAL'
--FROM wms_lpn_contents
--WHERE parent_lpn_id IN (SELECT wlpn1.lpn_id
-- FROM wms_license_plate_numbers wlpn1
-- START WITH wlpn1.lpn_id = wlpn.outermost_lpn_id
-- CONNECT BY PRIOR wlpn1.lpn_id = wlpn1.parent_lpn_id)
--)
)
OR
/** Bug:5650113
While performing Manual Load by Item by Item, mmtt.lpn_id
is stamped with from lpn_id not with into_lpn_id and
because of this no rows is getting fetched for the query
'LOADED_BY_SAME_USER'.
So, modified mmmt.lpn_id to mtrl.lpn_id and introduced join
with mmmt and mtrl table.
*/
(EXISTS (SELECT 'LOADED_BY_SAME_USER'
FROM mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt,--5650113
mtl_txn_request_lines mtrl--5650113
WHERE mmtt.organization_id = p_organization_id
AND mmtt.transaction_temp_id = wdt.transaction_temp_id
AND wdt.organization_id = p_organization_id
AND wdt.task_type = 2
AND wdt.status = 4
AND wdt.person_id = p_employee_id
AND mtrl.line_id = mmtt.move_order_line_id--5650113
AND mtrl.lpn_id IN (SELECT lpn_id--5650113
FROM wms_license_plate_numbers
START WITH lpn_id = wlpn.outermost_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
)
)
)
)
)
AND inv_material_status_grp.is_status_applicable('TRUE',
NULL,
INV_GLOBALS.G_TYPE_CONTAINER_PACK,
NULL,
NULL,
p_organization_id,
NULL,
wlpn.subinventory_code,
wlpn.locator_id,
NULL,
NULL,
'Z') = 'Y'
AND inv_material_status_grp.is_status_applicable('TRUE',
NULL,
INV_GLOBALS.G_TYPE_CONTAINER_PACK,
NULL,
NULL,
p_organization_id,
NULL,
wlpn.subinventory_code,
wlpn.locator_id,
NULL,
NULL,
'L') = 'Y');
SELECT 'N'
INTO l_allowed
FROM wsh_delivery_assignments_v wda1
, wsh_delivery_assignments_v wda2
, wsh_delivery_details wdd
WHERE wda1.delivery_detail_id = p_backorder_delivery_detail_id
AND wda2.delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id = l_into_lpn_id
AND Nvl(wda1.delivery_id,-1) <> Nvl(wda2.delivery_id,-2);
SELECT wpgt2.BACKORDER_DELIVERY_DETAIL
INTO l_bo_d_frm_id
FROM wms_putaway_group_tasks_gtmp wpgt1 , wms_putaway_group_tasks_gtmp wpgt2
WHERE wpgt1.backorder_delivery_detail = p_backorder_delivery_detail_id
AND wpgt2.transaction_header_id = wpgt1.transaction_header_id
AND wpgt2.row_type = 'All Task'
AND wpgt1.row_type = 'Group Task'
AND ROWNUM = 1;
SELECT wda.delivery_detail_id
INTO l_bo_d_to_id
FROM wsh_delivery_assignments_v wda ,
wsh_delivery_details wdd
WHERE wdd.lpn_id = l_into_lpn_id
AND wda.PARENT_DELIVERY_DETAIL_ID = wdd.delivery_detail_id
AND ROWNUM = 1;
SELECT MAX(mtrl.backorder_delivery_detail_id)
INTO l_bo_d_to_id
FROM mtl_txn_request_lines mtrl
WHERE EXISTS (SELECT 1
FROM rcv_transactions_interface rti, mtl_material_transactions_temp mmtt
WHERE rti.transfer_lpn_id = l_into_lpn_id
AND rti.mmtt_temp_id = mmtt.transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id
AND mmtt.inventory_item_id = mtrl.inventory_item_id
AND mmtt.organization_id = mtrl.organization_id
AND TRANSACTION_STATUS_CODE <> 'ERROR');
select count(DISTINCT inventory_item_id),count(DISTINCT lot_number),count(DISTINCT revision)
into l_item_count,l_lot_count,l_rev_count
from WMS_PUTAWAY_GROUP_TASKS_GTMP wpgtg
where lpn_id = p_lpn_id
and drop_type='ID'
and row_type = 'Group Task'
and not exists
(select 1
from wms_license_plate_numbers wln
where wln.parent_lpn_id = p_lpn_id);
select sum(primary_quantity) INTO l_qty_gtmp
from WMS_PUTAWAY_GROUP_TASKS_GTMP
where lpn_id = p_lpn_id
and row_type = 'Group Task'
and drop_type = 'ID';
select sum(primary_quantity) into l_qty_wlc --12679402
from wms_lpn_contents
where parent_lpn_id = p_lpn_id;
SELECT 1 INTO l_count
FROM DUAL
WHERE EXISTS (
SELECT 'INTO_LPN_EXISTS'
FROM wms_license_plate_numbers wlpn
WHERE wlpn.organization_id = p_organization_id
AND wlpn.lpn_id = l_into_lpn_id
AND (wlpn.lpn_context = 5 OR
((wlpn.lpn_context = 1 AND l_lpn_context IN (1,2) ) OR
wlpn.lpn_context IN (1,3,11) AND l_lpn_context = 3)--BUG 3463634 --BUG#11769042(Adding LPN context 11)
AND (p_lpn_id = wlpn.lpn_id OR --BUG 3368408
(NOT EXISTS (SELECT 'LOADED'
FROM mtl_material_transactions_temp mmtt,
wms_dispatched_tasks wdt
WHERE mmtt.organization_id = p_organization_id
AND mmtt.transaction_temp_id = wdt.transaction_temp_id
AND wdt.organization_id = p_organization_id
AND wdt.task_type = 2
AND wdt.status = 4
AND mmtt.lpn_id IN (SELECT wlpn2.lpn_id
FROM wms_license_plate_numbers wlpn2
START WITH wlpn2.lpn_id = wlpn.outermost_lpn_id
CONNECT BY PRIOR wlpn2.lpn_id = wlpn2.parent_lpn_id
)
)
)
)
AND inv_material_status_grp.is_status_applicable
('TRUE',NULL,inv_globals.g_type_container_pack,
NULL,NULL,p_organization_id,NULL,wlpn.subinventory_code,
wlpn.locator_id,NULL,NULL,'Z') = 'Y'
AND inv_material_status_grp.is_status_applicable
('TRUE',NULL,inv_globals.g_type_container_pack,NULL,
NULL,p_organization_id,NULL,wlpn.subinventory_code,
wlpn.locator_id,NULL,NULL,'L') = 'Y'
)
);
SELECT lpn_controlled_flag,reservable_type,Nvl(subinventory_type,1)
INTO l_lpn_controlled_flag,l_reservable_type,l_lpn_sub_type
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = l_into_lpn_sub
AND ((Nvl(subinventory_type,1)=1 AND
NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE))
OR
(Nvl(subinventory_type,1)=2 AND
(trunc(disable_date + (300*365)) >= trunc(SYSDATE)
OR TO_CHAR(disable_date, 'YYYY/MM/DD') = '1700/01/01')))
AND (((Nvl(subinventory_type,1)=1) AND
inv_ui_item_sub_loc_lovs.validate_lpn_sub
(p_organization_id,
secondary_inventory_name,
p_lpn_id) = 'Y') OR
Nvl(subinventory_type,1)=2);
SELECT Nvl(subinventory_type,1)
INTO l_sub_type
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_sub
AND ((Nvl(subinventory_type,1)=1 AND
NVL(disable_date, TRUNC(SYSDATE + 1)) > TRUNC(SYSDATE))
OR
(Nvl(subinventory_type,1)=2 AND
(trunc(disable_date + (300*365)) >= trunc(SYSDATE)
OR TO_CHAR(disable_date,'YYYY/MM/DD') = '1700/01/01')));
SELECT
project_id
,task_id
INTO
l_into_lpn_project_id
,l_into_lpn_task_id
FROM
mtl_item_locations
WHERE
inventory_location_id = l_into_lpn_loc_id;
SELECT
project_id
,task_id
INTO
l_into_lpn_project_id
,l_into_lpn_task_id
FROM
mtl_txn_request_lines mtrl, (SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = l_into_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
) wlpn2
WHERE
mtrl.lpn_id= wlpn2.lpn_id
AND ROWNUM = 1;
debug('unexpected exception in select project/task id',
'validate_into_lpn', 9);
SELECT
/*+ ORDERED USE_NL(mtrl mmtt) INDEX(MTRL MTL_TXN_REQUEST_LINES_N7) */
mmtt.transaction_temp_id
FROM
(
SELECT
lpn_id,
organization_id
FROM
wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
wlpn,
mtl_txn_request_lines mtrl,
mtl_material_transactions_temp mmtt
WHERE
(
mtrl.line_id = mmtt.move_order_line_id
)
AND wlpn.organization_id = l_org_id
AND mtrl.lpn_id = wlpn.lpn_id
UNION --mmtt with out moveorder line_id stamped
SELECT
mmtt.transaction_temp_id
FROM
(
SELECT
lpn_id,
organization_id
FROM
wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
wlpn,
mtl_material_transactions_temp mmtt
WHERE
(
wlpn.lpn_id = mmtt.lpn_id
OR wlpn.lpn_id = mmtt.content_lpn_id
)
AND wlpn.organization_id = l_org_id ;
SELECT
parent_lpn_id lpn_id,
inventory_item_id
FROM
wms_lpn_contents
WHERE
parent_lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id ;
SELECT
1
FROM
(
SELECT
NVL(SUM(quantity-NVL(quantity_delivered,0)),0) mtrl_quantity,
inventory_item_id,
uom_code
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
GROUP BY
inventory_item_id,
uom_code
)
mtrl ,
(
SELECT
NVL(SUM(quantity),0) wlc_quantity ,
inventory_item_id,
uom_code
FROM
wms_lpn_contents
WHERE
parent_lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
GROUP BY
inventory_item_id,
uom_code
)
wlc
WHERE
mtrl.mtrl_quantity <> wlc .wlc_quantity
AND mtrl.inventory_item_id = wlc.inventory_item_id
AND mtrl.uom_code = wlc.uom_code
AND rownum <2;
SELECT
1
FROM
wms_lpn_contents wlc
WHERE
parent_lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND NOT EXISTS
(
SELECT
1
FROM
mtl_txn_request_lines
WHERE
lpn_id = wlc.parent_lpn_id
AND organization_id = wlc.organization_id
AND inventory_item_id = wlc.inventory_item_id
AND line_status = 7
)
AND rownum <2 ;
SELECT
1
FROM
wms_license_plate_numbers wlpn,
mtl_txn_request_lines mtrl
WHERE
wlpn.lpn_id = mtrl.lpn_id
AND
(
NVL(wlpn.SUBINVENTORY_CODE,-1) <> NVL(mtrl.FROM_SUBINVENTORY_CODE,-1)
OR NVL(wlpn.locator_id, -1) <> NVL(mtrl.FROM_LOCATOR_ID,-1)
)
AND wlpn.lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND wlpn.organization_id = l_org_id
AND mtrl.line_status = 7
AND rownum <2;
SELECT
1
FROM
mtl_txn_request_lines mtrl,
wms_lpn_contents wlc
WHERE
mtrl.lpn_id = wlc.parent_lpn_id
AND mtrl.lot_number IS NOT NULL
AND wlc.lot_number IS NOT NULL
AND mtrl.inventory_item_id = wlc.inventory_item_id
AND NVL(mtrl.lot_number,'@') <> NVL(wlc.lot_number,'@')
AND wlc.organization_id = l_org_id
AND wlc.parent_lpn_id = l_lpn_id
AND wlc.inventory_item_id = l_item_id
AND rownum <2 ;
SELECT
organization_id
INTO
l_org_id
FROM
wms_license_plate_numbers
WHERE
lpn_id = p_lpn_id ;
UPDATE
wms_lpn_contents
SET
source_name = NULL,
source_header_id =NULL,
last_updated_by = l_user_id,
last_update_date = sysdate
WHERE
parent_lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id
);
DELETE FROM rcv_transactions_interface
WHERE lpn_id IN ( p_lpn_id )
OR transfer_lpn_id IN ( p_lpn_id )
AND from_organization_id = l_org_id
AND transaction_type <> 'SHIP'
AND processing_status_code <> 'WSH_INTERFACED';
UPDATE
mtl_txn_request_lines
SET
quantity_delivered = 0,
quantity_detailed = 0,
line_status = 7,
wms_process_flag = 1
WHERE
line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
);
DELETE
FROM
wms_op_plan_instances
WHERE
source_task_id IN
(
SELECT
parent_line_id
FROM
mtl_material_transactions_temp
WHERE
move_order_line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
)
) ;
DELETE
FROM
wms_op_operation_instances
WHERE
source_task_id IN
(
SELECT
transaction_temp_id
FROM
mtl_material_transactions_temp
WHERE
move_order_line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
)
) ;
DELETE
FROM
wms_dispatched_tasks
WHERE
transaction_temp_id IN
(
SELECT
transaction_temp_id
FROM
mtl_material_transactions_temp
WHERE
move_order_line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
)
);
DELETE
FROM
mtl_material_transactions_temp
WHERE
transaction_temp_id IN
(
SELECT
parent_line_id
FROM
mtl_material_transactions_temp
WHERE
move_order_line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
)
) ;
--Delete MSNI
DELETE
FROM
mtl_serial_numbers_interface
WHERE
product_transaction_id IN
(
SELECT
interface_transaction_id
FROM
rcv_transactions_interface
WHERE
lpn_id IN (p_lpn_id)
OR transfer_lpn_id IN (p_lpn_id)
);
DELETE
FROM
rcv_serials_interface
WHERE
interface_transaction_id IN
(
SELECT
interface_transaction_id
FROM
rcv_transactions_interface
WHERE
lpn_id IN (p_lpn_id)
OR transfer_lpn_id IN (p_lpn_id)
);
DELETE
FROM
mtl_serial_numbers_temp
WHERE
transaction_temp_id IN
(
SELECT
transaction_temp_id
FROM
mtl_material_transactions_temp
WHERE
move_order_line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
)
);
--Delete MSNT based on Serial_transaction temp of MTLT
DELETE
FROM
mtl_serial_numbers_temp
WHERE
transaction_temp_id IN
(
SELECT
serial_transaction_temp_id
FROM
mtl_transaction_lots_temp
WHERE
transaction_temp_id IN
(
SELECT
transaction_temp_id
FROM
mtl_material_transactions_temp
WHERE
move_order_line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
)
)
);
--delete MTLI
DELETE
FROM
mtl_transaction_lots_interface
WHERE
lot_number IN
(
SELECT
lot_number
FROM
wms_lpn_contents
WHERE
parent_lpn_id IN (p_lpn_id)
);
DELETE
FROM
rcv_lots_interface
WHERE
interface_transaction_id IN
(
SELECT
interface_transaction_id
FROM
rcv_transactions_interface
WHERE
lpn_id IN (p_lpn_id)
OR transfer_lpn_id IN (p_lpn_id)
);
DELETE
FROM
mtl_transaction_lots_temp
WHERE
transaction_temp_id IN
(
SELECT
transaction_temp_id
FROM
mtl_material_transactions_temp
WHERE
move_order_line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
)
);
DELETE
FROM
mtl_material_transactions_temp
WHERE
transaction_temp_id IN
(
SELECT
transaction_temp_id
FROM
mtl_material_transactions_temp
WHERE
move_order_line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
)
) ;
DELETE
FROM
rcv_transactions_interface
WHERE
lpn_id IN (p_lpn_id)
OR transfer_lpn_id IN (p_lpn_id)
AND from_organization_id = l_org_id
AND transaction_type <> 'SHIP'
AND PROCESSING_STATUS_CODE <> 'WSH_INTERFACED';
UPDATE
mtl_txn_request_lines
SET
quantity_delivered = 0,
quantity_detailed = 0,
line_status = 7,
wms_process_flag = 1
WHERE
line_id IN
(
SELECT
line_id
FROM
mtl_txn_request_lines
WHERE
lpn_id IN
(
SELECT
lpn_id
FROM
wms_license_plate_numbers
START WITH lpn_id IN (p_lpn_id)
CONNECT BY PRIOR lpn_id = parent_lpn_id
)
AND organization_id = l_org_id
AND line_status = 7
AND quantity-NVL(quantity_delivered,0) > 0
);
UPDATE
wms_lpn_contents
SET
source_header_id = NULL,
source_name = NULL
WHERE
parent_lpn_id IN (p_lpn_id);
UPDATE
mtl_serial_numbers
SET
group_mark_id = NULL,
line_mark_id = NULL
WHERE
serial_number IN
(
SELECT
serial_number
FROM
mtl_serial_numbers
WHERE
lpn_id IN (p_lpn_id)
);
SELECT
DISTINCT
Nvl(mol.backorder_delivery_detail_id,-1) back_order_delivery_detail_id,
Nvl(mol.crossdock_type,1) crossdock_type,
Nvl(mmtt.wip_supply_type,0) wip_supply_type
bulk collect INTO
l_backorder_delivery_ids,
l_crossdock_types,
l_wip_supply_types
FROM
mtl_txn_request_lines mol,
mtl_material_transactions_temp mmtt ,
(SELECT wlpn.lpn_id /*5723418*/
FROM wms_license_plate_numbers wlpn
START WITH wlpn.lpn_id = p_lpn_id
CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
WHERE mol.lpn_id = wlpn.lpn_id
AND mol.organization_id = p_organization_id
AND mol.line_id = mmtt.move_order_line_id
AND mmtt.organization_id = p_organization_id
ORDER BY wip_supply_type DESC;
PROCEDURE update_loc_suggested_capacity(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_organization_id IN NUMBER
, p_lpn_id IN NUMBER
, p_location_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_loc_suggested_capacity';
SELECT mmtt.inventory_item_id
, mmtt.locator_id
, mmtt.transaction_quantity
, mmtt.transaction_uom
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl ,
(SELECT lpn_id /*5723418*/
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id) wlpn
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mmtt.organization_id = p_organization_id
AND mtrl.organization_id = p_organization_id
AND mtrl.lpn_id = wlpn.lpn_id
AND NVL(mmtt.wms_task_type, 0) <> -1;
debug('***Calling WMSPUTLB.update_loc_suggested_capacity***');
SAVEPOINT update_capacity_sp;
debug('Call INV_LOC_WMS_UTILS.update_loc_suggested_capacity API');
inv_loc_wms_utils.update_loc_suggested_capacity(
x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_organization_id => p_organization_id
, p_inventory_location_id => p_location_id
, p_inventory_item_id => l_item_id
, p_primary_uom_flag => 'N'
, p_transaction_uom_code => l_uom_code
, p_quantity => l_quantity
);
debug('Success returned from update_loc_suggested_capacity API');
debug('Failure returned from update_loc_suggested_capacity API');
debug('***End of update_loc_suggested_capacity***');
ROLLBACK TO update_capacity_sp;
'Exiting update_loc_suggested_capacity - Execution error: ' || l_progress || ' '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
);
ROLLBACK TO update_capacity_sp;
'Exiting update_loc_suggested_capacity - Unexpected error: ' || l_progress || ' '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
);
ROLLBACK TO update_capacity_sp;
'Exiting update_loc_suggested_capacity - Others exception: ' || l_progress || ' '
|| TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:DD:SS')
);
END update_loc_suggested_capacity;
SELECT NVL(backorder_delivery_detail_id,-1) , nvl(crossdock_type,1)
FROM mtl_txn_request_lines
WHERE lpn_id = v_lpn_id
AND line_status = 7 ; --9505772 need only open lines.
g_line_rows.delete; --This table will hold So lines
g_grouping_rows.delete;
l_non_so_lines.delete;
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT mmtt.transaction_temp_id,
mmtt.parent_line_id,
mmtt.operation_plan_id,
mol.line_id move_order_line_id,
mmtt.operation_seq_num,
mmtt.repetitive_line_id,
mmtt.primary_quantity,
mmtt.inventory_item_id,
mol.crossdock_type,
mol.backorder_delivery_detail_id
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mol
WHERE mmtt.move_order_line_id(+) = mol.line_id
AND mmtt.wms_task_type=2
AND mol.organization_id = mmtt.organization_id(+)
AND mol.organization_id=p_org_id
AND mol.lpn_id = p_lpn
AND mol.LINE_STATUS <> 5
AND EXISTS (SELECT 1 FROM mtl_txn_request_lines
WHERE lpn_id = p_lpn
AND BACKORDER_DELIVERY_DETAIL_ID IS NOT NULL);
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT 'Y','Y'
INTO l_content_lpn,l_lpn_has_contents
FROM wms_lpn_contents
WHERE parent_lpn_id = l_lpn_id
AND ROWNUM<2;
FOR wooi IN (SELECT operation_instance_id
FROM wms_op_operation_instances
WHERE source_task_id = l_mmtt_rec.transaction_temp_id
) LOOP
wms_op_runtime_pvt_apis.delete_operation_instance
( p_operation_instance_id => wooi.operation_instance_id
, x_return_status => x_return_status
, x_msg_data => l_x_msg_data
, x_msg_count => l_x_msg_count
);
debug(l_progress||l_error_code || ' Error in delete_operation_instance ' ,l_proc_name,1);
FOR wopi IN (SELECT OP_PLAN_INSTANCE_ID
FROM wms_op_plan_instances
WHERE source_task_id = l_mmtt_rec.parent_line_id
) LOOP
l_progress := 110;
wms_op_runtime_pvt_apis.delete_plan_instance
( p_op_plan_instance_id => wopi.OP_PLAN_INSTANCE_ID
, x_return_status => x_return_status
, x_msg_data => l_x_msg_data
, x_msg_count => l_x_msg_count
);
debug(l_progress||l_error_code || ' Error in delete_plan_instance ' ,l_proc_name,1);
inv_trx_util_pub.delete_transaction
( x_return_status => x_return_status
, x_msg_data => l_x_msg_data
, x_msg_count => l_x_msg_count
, p_transaction_temp_id => l_mmtt_rec.transaction_temp_id
, p_update_parent => true
);
debug(l_progress||' Error in delete_transaction ' ,l_proc_name,1);
UPDATE mtl_txn_request_lines SET quantity_detailed=NULL WHERE line_id=l_mmtt_rec.move_order_line_id;