[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_task(
p_org_id IN NUMBER
, p_user_id IN NUMBER
, p_eqp_ins IN VARCHAR2
, p_temp_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_org_id NUMBER;
/* SELECT resource_id INTO l_mac_res_id
FROM bom_resource_equipments
WHERE organization_id=l_org_id
AND inventory_item_id=l_eqp_id;
SELECT resource_id INTO l_per_res_id
FROM bom_resource_employees
WHERE organization_id=l_org_id
AND person_id=l_emp_id;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_task_id
FROM DUAL;
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, priority
, operation_plan_id
, move_order_line_id
)
(SELECT l_task_id
, transaction_temp_id
, organization_id
, NVL(standard_operation_id, 2)
, l_person_id
, SYSDATE
, SYSDATE
, l_eqp_id
, l_eqp_ins
, l_per_res_id
, l_mac_res_id
, 4
, SYSDATE
, SYSDATE
, l_person_id
, SYSDATE
, l_person_id
, NVL(wms_task_type, 1)
, task_priority
, operation_plan_id
, move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id);
fnd_message.set_name('WMS', 'WMS_TD_INSERT_TASK');
END insert_task;
SELECT wdat.device_id
, wdat.assignment_temp_id
, wdb.subinventory_code
FROM wms_device_assignment_temp wdat, wms_devices_b wdb
WHERE wdat.assignment_temp_id >= p_current_device_temp_id
AND wdat.employee_id = p_emp_id
AND wdat.device_id = wdb.device_id
ORDER BY wdat.assignment_temp_id;
SELECT wdat.device_id
, wdat.assignment_temp_id
, wdb.subinventory_code
FROM wms_device_assignment_temp wdat, wms_devices_b wdb
WHERE wdat.assignment_temp_id < p_current_device_temp_id
AND wdat.employee_id = p_emp_id
AND wdat.device_id = wdb.device_id
ORDER BY wdat.assignment_temp_id;
SELECT COUNT(*) tsk
INTO l_tsks
FROM wms_dispatched_tasks wdt
WHERE wdt.person_id = l_user_id
AND wdt.organization_id = l_org_id
AND wdt.task_type IN(1, 3, 4, 5, 6)
AND wms_express_pick_task.is_express_pick_task(task_id) = 'S'
AND wdt.status <= 3
AND ((wdt.task_type = 3
AND exists
(SELECT NVL(cycle_count_entry_id,-1)
--Bug 3808770- Added the table mtl_cycle_count_headers in the from clause
FROM mtl_cycle_count_entries mcce,mtl_cycle_count_headers mcch
WHERE mcce.cycle_count_entry_id = wdt.transaction_temp_id
AND mcce.organization_id = wdt.organization_id
AND mcce.subinventory = nvl(p_sign_on_zone,mcce.subinventory) --Added bug3771517
AND mcce.entry_status_code in (1,3)
--Bug 3808770 Added the following conditions to select only those tasks whose cycle count is not disabled.
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND NVL(mcch.disable_date,sysdate+1)> sysdate
--End of fix for Bug 3808770
)
)
OR (wdt.task_type IN (1, 4, 5, 6)
AND EXISTS
(SELECT Nvl(mmtt.transaction_temp_id, -1)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
AND mmtt.subinventory_code = nvl(p_sign_on_zone,mmtt.subinventory_code) --Added bug3771517
AND mmtt.organization_id = wdt.organization_id))
);
SELECT count(*) TSK
INTO l_tsks
FROM wms_dispatched_tasks wdt
WHERE wdt.person_id = l_user_id
AND wdt.organization_id = l_org_id
AND wdt.task_type IN (1, 3, 4, 5, 6)
AND wdt.status <= 3
AND ((wdt.task_type = 3
AND exists
(SELECT NVL(cycle_count_entry_id,-1)
FROM mtl_cycle_count_entries mcce ,mtl_cycle_count_headers mcch
WHERE mcce.cycle_count_entry_id = wdt.transaction_temp_id
AND mcce.subinventory = nvl(p_sign_on_zone,mcce.subinventory) --Added bug3771517
AND mcce.organization_id = wdt.organization_id
and mcce.entry_status_code in (1,3)
--Bug 3808770 Added the following conditions to select only those tasks whose cycle count is not disabled.
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND NVL(mcch.disable_date,sysdate+1)> sysdate
--End of fix for Bug 3808770
)
)
OR (wdt.task_type IN (1, 4, 5, 6)
AND EXISTS
(SELECT Nvl(mmtt.transaction_temp_id, -1)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
AND mmtt.subinventory_code = nvl(p_sign_on_zone,mmtt.subinventory_code) --Added bug3771517
AND mmtt.organization_id = wdt.organization_id))
);
SELECT wt.task_type
INTO x_task_type
FROM (SELECT wdt.task_type
, wdt.priority
, wdt.task_id task_id
, sub.picking_order sub_picking_order
, loc.picking_order loc_picking_order
FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
WHERE wdt.person_id = l_user_id
AND wdt.organization_id = l_org_id
AND wdt.status <= 3
AND wdt.task_type IN (1, 4, 5, 6)
AND WMS_EXPRESS_PICK_TASK.IS_EXPRESS_PICK_TASK(wdt.TASK_ID) = 'S'
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.subinventory_code = nvl(p_sign_on_zone,mmtt.subinventory_code) --Added bug3771517
AND sub.organization_id = mmtt.organization_id
AND sub.secondary_inventory_name = mmtt.subinventory_code
AND loc.organization_id = mmtt.organization_id
AND loc.inventory_location_id = mmtt.locator_id
UNION
SELECT wdt.task_type
, wdt.priority
, wdt.task_id task_id
, sub.picking_order sub_picking_order
, loc.picking_order loc_picking_order
--Bug 3808770 -Added the table mtl_cycle_count_headers in the FROM clause
FROM mtl_cycle_count_entries mcce, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
,mtl_cycle_count_headers mcch
WHERE wdt.person_id = l_user_id
AND wdt.organization_id = l_org_id
AND wdt.status <= 3
AND wdt.task_type = 3
AND WMS_EXPRESS_PICK_TASK.IS_EXPRESS_PICK_TASK(wdt.TASK_ID) = 'S'
AND wdt.transaction_temp_id = mcce.cycle_count_entry_id
AND sub.organization_id = mcce.organization_id
AND mcce.subinventory = nvl(p_sign_on_zone,mcce.subinventory) --Added bug3771517
AND sub.secondary_inventory_name = mcce.subinventory
AND loc.organization_id = mcce.organization_id
AND loc.inventory_location_id = mcce.locator_id
--Bug 3808770 Added the following conditions to select only those tasks whose cycle count is not disabled.
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND NVL(mcch.disable_date,sysdate+1)> sysdate ) wt
--End of fix for Bug 3808770
WHERE ROWNUM = 1
order by wt.priority,wt.sub_picking_order, wt.loc_picking_order, wt.task_id ;
SELECT wt.task_type
INTO x_task_type
FROM (SELECT wdt.task_type
, wdt.priority
, wdt.task_id task_id
, sub.picking_order sub_picking_order
, loc.picking_order loc_picking_order
FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
WHERE wdt.person_id = l_user_id
AND wdt.organization_id = l_org_id
AND wdt.status <= 3
AND wdt.task_type IN (1, 4, 5, 6)
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND sub.organization_id = mmtt.organization_id
AND sub.secondary_inventory_name = mmtt.subinventory_code
AND mmtt.subinventory_code = nvl(p_sign_on_zone,mmtt.subinventory_code) --Added bug3771517
AND loc.organization_id = mmtt.organization_id
AND loc.inventory_location_id = mmtt.locator_id
UNION
SELECT wdt.task_type
, wdt.priority
, wdt.task_id task_id
, sub.picking_order sub_picking_order
, loc.picking_order loc_picking_order
--Bug 3808770 -Added the table mtl_cycle_count_headers in the FROM clause
FROM mtl_cycle_count_entries mcce, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
, mtl_cycle_count_headers mcch
WHERE wdt.person_id = l_user_id
AND wdt.organization_id = l_org_id
AND wdt.status <= 3
AND wdt.task_type = 3
AND wdt.transaction_temp_id = mcce.cycle_count_entry_id
AND sub.organization_id = mcce.organization_id
AND mcce.subinventory = nvl(p_sign_on_zone,mcce.subinventory) --Added bug3771517
AND sub.secondary_inventory_name = mcce.subinventory
AND loc.organization_id = mcce.organization_id
AND loc.inventory_location_id = mcce.locator_id
--Bug 3808770 Added the following conditions to select only those tasks whose cycle count is not disabled.
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND NVL(mcch.disable_date,sysdate+1)> sysdate )wt
--End of fix for Bug 3808770
WHERE ROWNUM = 1
order by wt.priority,wt.sub_picking_order, wt.loc_picking_order, wt.task_id;
SELECT wdat.assignment_temp_id
, wd.subinventory_code
INTO l_assignment_temp_id
, l_zone
FROM wms_device_assignment_temp wdat, wms_devices_vl wd
WHERE wdat.device_id = l_device_id
AND wdat.device_id = wd.device_id
AND employee_id = l_emp_id;
SELECT transaction_temp_id
, task_type
, device_invoked
INTO l_task_id
, l_task_type_n
, l_device_invoked
FROM wms_dispatched_tasks
WHERE person_id = l_user_id
AND organization_id = l_org_id
AND device_id = l_temp_device_id
AND task_type IN(1, 3, 4, 5, 6)
AND status <= 3
AND ROWNUM = 1
ORDER BY 1;
UPDATE wms_dispatched_tasks
SET device_invoked = 'Y'
, device_request_id = l_request_id
WHERE transaction_temp_id = l_task_id;
SELECT bremp.resource_id role_id
, t.wms_task_type
, t.standard_operation_id
, t.operation_plan_id
, t.move_order_line_id
INTO l_per_res_id
, l_wms_task_type
, l_std_op_id
, l_operation_plan_id
, l_move_order_line_id
FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
WHERE t.transaction_temp_id = task_rec.task_id
AND t.standard_operation_id = bsor.standard_operation_id
AND bsor.resource_id = bremp.resource_id
AND bremp.resource_type = 2
AND ROWNUM < 2;
SELECT bremp.resource_id role_id
, 3
, mcce.standard_operation_id
INTO l_per_res_id
, l_wms_task_type
, l_std_op_id
--Bug 3808770- Added the table mtl_cycle_count_headers in the FROM clause
FROM mtl_cycle_count_entries mcce, bom_std_op_resources bsor, bom_resources bremp,mtl_cycle_count_headers mcch
WHERE mcce.cycle_count_entry_id = task_rec.task_id
AND mcce.standard_operation_id = bsor.standard_operation_id
AND bsor.resource_id = bremp.resource_id
AND bremp.resource_type = 2
AND ROWNUM < 2
AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND NVL(mcch.disable_date,sysdate+1)> sysdate;
SELECT resource_id
INTO l_mac_res_id
FROM bom_resource_equipments
WHERE inventory_item_id = l_eqp_id
AND ROWNUM < 2;
SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
INTO l_txn_hdr_id
FROM DUAL;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_hdr_id
WHERE transaction_temp_id = task_rec.task_id;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM DUAL;
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, priority
, operation_plan_id
, move_order_line_id
)
VALUES (
l_next_task_id
, task_rec.task_id
, l_org_id
, NVL(l_std_op_id, 2)
, l_user_id
, SYSDATE
, SYSDATE
, l_eqp_id
, l_eqp_ins
, l_per_res_id
, l_mac_res_id
, 3
, SYSDATE
, SYSDATE
, l_emp_id
, SYSDATE
, l_emp_id
, l_wms_task_type
, task_rec.task_priority
, l_operation_plan_id
, l_move_order_line_id
);
mydebug('next_task: After Insert into WMSDT');
UPDATE wms_dispatched_tasks
SET device_id = l_temp_device_id
, device_invoked = 'N'
WHERE task_id = l_next_task_id;
UPDATE wms_dispatched_tasks
SET device_invoked = 'Y'
, device_request_id = l_request_id
WHERE task_id = l_next_task_id;
DELETE FROM wms_skip_task_exceptions
WHERE task_id = task_rec.task_id
AND task_id IN(
SELECT wste.task_id
FROM wms_skip_task_exceptions wste, mtl_parameters mp
WHERE ABS((SYSDATE - wste.creation_date) * 24 * 60) > mp.skip_task_waiting_minutes
AND wste.task_id = task_rec.task_id
AND wste.organization_id = mp.organization_id);
SELECT task_type
INTO x_task_type
FROM wms_dispatched_tasks
WHERE device_id = x_avail_device_id
AND device_invoked = 'Y'
AND person_id = l_user_id
AND status <= 3
AND task_type IN(1, 3, 4, 5, 6);
SELECT wdat.device_id
, wdat.assignment_temp_id
, wdb.subinventory_code
FROM wms_device_assignment_temp wdat, wms_devices_b wdb
WHERE wdat.assignment_temp_id >= p_current_device_temp_id
AND wdat.employee_id = p_emp_id
AND wdat.device_id = wdb.device_id
ORDER BY wdat.assignment_temp_id;
SELECT wdat.device_id
, wdat.assignment_temp_id
, wdb.subinventory_code
FROM wms_device_assignment_temp wdat, wms_devices_b wdb
WHERE wdat.assignment_temp_id < p_current_device_temp_id
AND wdat.employee_id = p_emp_id
AND wdat.device_id = wdb.device_id
ORDER BY wdat.assignment_temp_id;
SELECT wdat.assignment_temp_id
, wd.subinventory_code
INTO l_assignment_temp_id
, l_zone
FROM wms_device_assignment_temp wdat, wms_devices_vl wd
WHERE wdat.device_id = l_device_id
AND wdat.device_id = wd.device_id
AND employee_id = l_emp_id;
SELECT transaction_temp_id
, task_type
, device_invoked
INTO l_task_id
, l_task_type_n
, l_device_invoked
FROM wms_dispatched_tasks
WHERE person_id = l_user_id
AND organization_id = l_org_id
AND device_id = l_temp_device_id
AND task_type IN(1, 3, 4, 5, 6)
AND status <= 3
AND ROWNUM = 1
ORDER BY 1;
UPDATE wms_dispatched_tasks
SET device_invoked = 'Y'
, device_request_id = l_request_id
WHERE transaction_temp_id = l_task_id;
SELECT bremp.resource_id role_id
, t.wms_task_type
, t.standard_operation_id
, t.operation_plan_id
, t.move_order_line_id
INTO l_per_res_id
, l_wms_task_type
, l_std_op_id
, l_operation_plan_id
, l_move_order_line_id
FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
WHERE t.transaction_temp_id = task_rec.task_id
AND t.standard_operation_id = bsor.standard_operation_id
AND bsor.resource_id = bremp.resource_id
AND bremp.resource_type = 2
AND ROWNUM < 2;
SELECT
bremp.resource_id role_id,
3,
mcce.standard_operation_id
INTO
l_per_res_id,
l_wms_task_type,
l_std_op_id
FROM
mtl_cycle_count_entries mcce,
bom_std_op_resources bsor,
bom_resources bremp
where mcce.cycle_count_entry_id = task_rec.task_id
and mcce.standard_operation_id = bsor.standard_operation_id
and bsor.resource_id = bremp.resource_id
and bremp.resource_type = 2
and rownum<2;
SELECT resource_id
INTO l_mac_res_id
FROM bom_resource_equipments
WHERE inventory_item_id = l_eqp_id
AND ROWNUM < 2;
SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
INTO l_txn_hdr_id
FROM DUAL;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_hdr_id
WHERE transaction_temp_id = task_rec.task_id;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM DUAL;
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, priority
, operation_plan_id
, move_order_line_id
)
VALUES (
l_next_task_id
, task_rec.task_id
, l_org_id
, NVL(l_std_op_id, 2)
, l_user_id
, SYSDATE
, SYSDATE
, l_eqp_id
, l_eqp_ins
, l_per_res_id
, l_mac_res_id
, 3
, SYSDATE
, SYSDATE
, l_emp_id
, SYSDATE
, l_emp_id
, l_wms_task_type
, task_rec.task_priority
, l_operation_plan_id
, l_move_order_line_id
);
mydebug('next_CP_task: After Insert into WMSDT');
UPDATE wms_dispatched_tasks
SET device_id = l_temp_device_id
, device_invoked = 'N'
WHERE task_id = l_next_task_id;
UPDATE wms_dispatched_tasks
SET device_invoked = 'Y'
, device_request_id = l_request_id
WHERE task_id = l_next_task_id;
DELETE FROM wms_skip_task_exceptions
WHERE task_id = task_rec.task_id
AND task_id IN(
SELECT wste.task_id
FROM wms_skip_task_exceptions wste, mtl_parameters mp
WHERE ABS((SYSDATE - wste.creation_date) * 24 * 60) > mp.skip_task_waiting_minutes
AND wste.task_id = task_rec.task_id
AND wste.organization_id = mp.organization_id);
SELECT task_type
INTO x_task_type
FROM wms_dispatched_tasks
WHERE device_id = x_avail_device_id
AND device_invoked = 'Y'
AND person_id = l_user_id
AND status <= 3
AND task_type IN(1, 3, 4, 5, 6);
SELECT mtlt.lot_number
, mtlt.transaction_quantity
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id;
SELECT NVL(content_lpn_id, 0)
, NVL(lpn_id, 0)
, transfer_lpn_id
, transfer_subinventory
, transfer_to_location
, transaction_type_id
, transaction_header_id
INTO l_content_lpn_id
, l_mmtt_from_lpn_id
, l_transfer_lpn_id
, l_orig_sub
, l_orig_loc
, l_tran_type_id
, l_orig_txn_header_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
UPDATE mtl_material_transactions_temp
SET transfer_subinventory = p_sub
, transfer_to_location = l_loc
WHERE transaction_temp_id = p_temp_id;
SELECT msi.reservable_type
INTO l_sub_reservable_type
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = p_sub
AND msi.organization_id = p_org_id;
SELECT mmtt.inventory_item_id
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.revision
INTO l_item_id
, l_qty
, l_uom
, l_from_sub
, l_from_loc
, l_rev
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT msi.serial_number_control_code
, msi.lot_control_code
INTO l_serial_code
, l_lot_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_item_id
AND msi.organization_id = p_org_id;
SELECT 1
INTO l_exist_lpn
FROM DUAL
WHERE EXISTS(SELECT 1
FROM wms_license_plate_numbers
WHERE license_plate_number = l_lpn
AND organization_id = p_org_id);
SELECT lpn_id
INTO l_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = l_lpn
AND organization_id = p_org_id;
UPDATE mtl_material_transactions_temp mmtt
SET transaction_status = 3
, transaction_header_id = p_txn_hdr_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
, transaction_batch_id = p_txn_hdr_id
WHERE mmtt.transaction_temp_id = p_temp_id;
UPDATE mtl_material_transactions_temp mmtt
SET transaction_status = 3
, transaction_header_id = p_txn_hdr_id
, transfer_lpn_id = NULL
, last_update_date = SYSDATE
, last_updated_by = p_user_id
, transaction_batch_id = p_txn_hdr_id
WHERE mmtt.transaction_temp_id = p_temp_id;
UPDATE mtl_material_transactions_temp mmtt
SET transaction_status = 3
, transaction_header_id = p_txn_hdr_id
, last_update_date = SYSDATE
, last_updated_by = p_user_id
, transaction_batch_id = p_txn_hdr_id
, transaction_batch_seq = p_temp_id
WHERE mmtt.transaction_temp_id = p_temp_id;
UPDATE mtl_material_transactions_temp mmtt
SET transaction_status = 3
, transaction_header_id = p_txn_hdr_id
, transfer_lpn_id = NULL
, last_update_date = SYSDATE
, last_updated_by = p_user_id
, transaction_batch_id = p_txn_hdr_id
, transaction_batch_seq = p_temp_id
WHERE mmtt.transaction_temp_id = p_temp_id;
mydebug('complete_pick: WIP issue: update txn qty and primary qty to -ve');
UPDATE mtl_material_transactions_temp
SET transaction_quantity = -1 * ABS(transaction_quantity)
, primary_quantity = -1 * ABS(primary_quantity)
WHERE transaction_temp_id = p_temp_id
AND organization_id = p_org_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = -1 * ABS(transaction_quantity)
, primary_quantity = -1 * ABS(primary_quantity)
, process_flag = 'W'
WHERE transaction_temp_id = p_temp_id
AND organization_id = p_org_id;
UPDATE wms_exceptions
SET transaction_header_id = p_txn_hdr_id
WHERE transaction_header_id = l_orig_txn_header_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
SELECT vendor_serial_number, vendor_lot_number, parent_serial_number
, origination_date, end_item_unit_number, territory_code, time_since_new, cycles_since_new
, time_since_overhaul, cycles_since_overhaul, time_since_repair, cycles_since_repair
, time_since_visit, cycles_since_visit, time_since_mark, cycles_since_mark
, number_of_repairs, serial_attribute_category, c_attribute1, c_attribute2
, c_attribute3, c_attribute4, c_attribute5, c_attribute6, c_attribute7
, c_attribute8, c_attribute9, c_attribute10, c_attribute11, c_attribute12
, c_attribute13, c_attribute14, c_attribute15, c_attribute16, c_attribute17, c_attribute18
, c_attribute19, c_attribute20, d_attribute1, d_attribute2, d_attribute3
, d_attribute4, d_attribute5, d_attribute6, d_attribute7, d_attribute8
, d_attribute9, d_attribute10, n_attribute1, n_attribute2, n_attribute3
, n_attribute4, n_attribute5, n_attribute6, n_attribute7, n_attribute8
, n_attribute9, n_attribute10
FROM mtl_serial_numbers
WHERE serial_number = v_serial_number
AND inventory_item_id = v_inventory_item_id;
SELECT expiration_date
INTO l_exp_date
FROM mtl_lot_numbers
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id
AND lot_number = l_lot;
mydebug('process_lot_serial: Inserting Lots');
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_qty
, primary_quantity = l_pr_qty
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_temp_id
AND lot_number = l_lot;
mydebug('process_lot_serial: After lot update');
SELECT NVL(serial_transaction_temp_id, 0)
INTO l_lot_ser_seq
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_temp_id
AND lot_number = l_lot;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_ser_seq
FROM DUAL;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_qty
, primary_quantity = l_pr_qty
, serial_transaction_temp_id = l_ser_seq
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_temp_id
AND lot_number = l_lot;
INSERT INTO mtl_serial_numbers_temp
( TRANSACTION_TEMP_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, fm_serial_number
, to_serial_number
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, end_item_unit_number
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, serial_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
)
VALUES (
l_ser_seq
, Sysdate
, l_user_id
, Sysdate
, l_user_id
, l_fm_serial
, l_to_serial
, l_msn_attribute_rec.vendor_serial_number
, l_msn_attribute_rec.vendor_lot_number
, l_msn_attribute_rec.parent_serial_number
, l_msn_attribute_rec.origination_date
, l_msn_attribute_rec.end_item_unit_number
, l_msn_attribute_rec.territory_code
, l_msn_attribute_rec.time_since_new
, l_msn_attribute_rec.cycles_since_new
, l_msn_attribute_rec.time_since_overhaul
, l_msn_attribute_rec.cycles_since_overhaul
, l_msn_attribute_rec.time_since_repair
, l_msn_attribute_rec.cycles_since_repair
, l_msn_attribute_rec.time_since_visit
, l_msn_attribute_rec.cycles_since_visit
, l_msn_attribute_rec.time_since_mark
, l_msn_attribute_rec.cycles_since_mark
, l_msn_attribute_rec.number_of_repairs
, l_msn_attribute_rec.serial_attribute_category
, l_msn_attribute_rec.c_attribute1
, l_msn_attribute_rec.c_attribute2
, l_msn_attribute_rec.c_attribute3
, l_msn_attribute_rec.c_attribute4
, l_msn_attribute_rec.c_attribute5
, l_msn_attribute_rec.c_attribute6
, l_msn_attribute_rec.c_attribute7
, l_msn_attribute_rec.c_attribute8
, l_msn_attribute_rec.c_attribute9
, l_msn_attribute_rec.c_attribute10
, l_msn_attribute_rec.c_attribute11
, l_msn_attribute_rec.c_attribute12
, l_msn_attribute_rec.c_attribute13
, l_msn_attribute_rec.c_attribute14
, l_msn_attribute_rec.c_attribute15
, l_msn_attribute_rec.c_attribute16
, l_msn_attribute_rec.c_attribute17
, l_msn_attribute_rec.c_attribute18
, l_msn_attribute_rec.c_attribute19
, l_msn_attribute_rec.c_attribute20
, l_msn_attribute_rec.d_attribute1
, l_msn_attribute_rec.d_attribute2
, l_msn_attribute_rec.d_attribute3
, l_msn_attribute_rec.d_attribute4
, l_msn_attribute_rec.d_attribute5
, l_msn_attribute_rec.d_attribute6
, l_msn_attribute_rec.d_attribute7
, l_msn_attribute_rec.d_attribute8
, l_msn_attribute_rec.d_attribute9
, l_msn_attribute_rec.d_attribute10
, l_msn_attribute_rec.n_attribute1
, l_msn_attribute_rec.n_attribute2
, l_msn_attribute_rec.n_attribute3
, l_msn_attribute_rec.n_attribute4
, l_msn_attribute_rec.n_attribute5
, l_msn_attribute_rec.n_attribute6
, l_msn_attribute_rec.n_attribute7
, l_msn_attribute_rec.n_attribute8
, l_msn_attribute_rec.n_attribute9
, l_msn_attribute_rec.n_attribute10
);
/*INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
)
VALUES (
l_ser_seq
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_fm_serial
, l_to_serial
);*/
mydebug('process_lot_serial: Inserting Serials Only');
INSERT INTO mtl_serial_numbers_temp
( TRANSACTION_TEMP_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, fm_serial_number
, to_serial_number
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, end_item_unit_number
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, serial_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
)
VALUES (
l_temp_id
, Sysdate
, l_user_id
, Sysdate
, l_user_id
, l_fm_serial
, l_to_serial
, l_msn_attribute_rec.vendor_serial_number
, l_msn_attribute_rec.vendor_lot_number
, l_msn_attribute_rec.parent_serial_number
, l_msn_attribute_rec.origination_date
, l_msn_attribute_rec.end_item_unit_number
, l_msn_attribute_rec.territory_code
, l_msn_attribute_rec.time_since_new
, l_msn_attribute_rec.cycles_since_new
, l_msn_attribute_rec.time_since_overhaul
, l_msn_attribute_rec.cycles_since_overhaul
, l_msn_attribute_rec.time_since_repair
, l_msn_attribute_rec.cycles_since_repair
, l_msn_attribute_rec.time_since_visit
, l_msn_attribute_rec.cycles_since_visit
, l_msn_attribute_rec.time_since_mark
, l_msn_attribute_rec.cycles_since_mark
, l_msn_attribute_rec.number_of_repairs
, l_msn_attribute_rec.serial_attribute_category
, l_msn_attribute_rec.c_attribute1
, l_msn_attribute_rec.c_attribute2
, l_msn_attribute_rec.c_attribute3
, l_msn_attribute_rec.c_attribute4
, l_msn_attribute_rec.c_attribute5
, l_msn_attribute_rec.c_attribute6
, l_msn_attribute_rec.c_attribute7
, l_msn_attribute_rec.c_attribute8
, l_msn_attribute_rec.c_attribute9
, l_msn_attribute_rec.c_attribute10
, l_msn_attribute_rec.c_attribute11
, l_msn_attribute_rec.c_attribute12
, l_msn_attribute_rec.c_attribute13
, l_msn_attribute_rec.c_attribute14
, l_msn_attribute_rec.c_attribute15
, l_msn_attribute_rec.c_attribute16
, l_msn_attribute_rec.c_attribute17
, l_msn_attribute_rec.c_attribute18
, l_msn_attribute_rec.c_attribute19
, l_msn_attribute_rec.c_attribute20
, l_msn_attribute_rec.d_attribute1
, l_msn_attribute_rec.d_attribute2
, l_msn_attribute_rec.d_attribute3
, l_msn_attribute_rec.d_attribute4
, l_msn_attribute_rec.d_attribute5
, l_msn_attribute_rec.d_attribute6
, l_msn_attribute_rec.d_attribute7
, l_msn_attribute_rec.d_attribute8
, l_msn_attribute_rec.d_attribute9
, l_msn_attribute_rec.d_attribute10
, l_msn_attribute_rec.n_attribute1
, l_msn_attribute_rec.n_attribute2
, l_msn_attribute_rec.n_attribute3
, l_msn_attribute_rec.n_attribute4
, l_msn_attribute_rec.n_attribute5
, l_msn_attribute_rec.n_attribute6
, l_msn_attribute_rec.n_attribute7
, l_msn_attribute_rec.n_attribute8
, l_msn_attribute_rec.n_attribute9
, l_msn_attribute_rec.n_attribute10
);
/* INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
)
VALUES (
l_temp_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_fm_serial
, l_to_serial
);*/
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn
AND inventory_item_id = p_item_id
AND NVL(lot_number, -999) = NVL(p_lot, -999);
SELECT mtlt.primary_quantity
, mtlt.lot_number
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id;
t_lpn_lot_qty_table.DELETE;
SELECT 1
, lpn_context
INTO l_lpn_exists
, l_lpn_context
FROM wms_license_plate_numbers wlpn
WHERE wlpn.organization_id = p_org_id
AND wlpn.lpn_id = p_lpn;
SELECT 1
INTO l_loaded
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE (transfer_lpn_id = p_lpn
OR content_lpn_id = p_lpn));
SELECT w.subinventory_code
, inv_project.get_locsegs(w.locator_id, w.organization_id)
, w.license_plate_number
, w.locator_id
, w.lpn_context
INTO l_sub
, l_loc
, l_from_lpn
, l_loc_id
, l_lpn_context
FROM wms_license_plate_numbers w
WHERE w.lpn_id = p_lpn
AND w.locator_id IS NOT NULL;
SELECT COUNT(*)
INTO l_sub_active
FROM mtl_secondary_inventories
WHERE NVL(disable_date, SYSDATE + 1) > SYSDATE
AND organization_id = p_org_id
AND secondary_inventory_name = l_sub;
SELECT COUNT(*)
INTO l_loc_active
FROM mtl_item_locations_kfv
WHERE NVL(disable_date, SYSDATE + 1) > SYSDATE
AND organization_id = p_org_id
AND subinventory_code = l_sub
AND inventory_location_id = l_loc_id;
SELECT locator_id,organization_id INTO l_locator_id, l_organization_id
from mtl_material_transactions_temp
where transaction_temp_id = p_temp_id;
select nvl(project_id ,-999) , nvl(task_id ,-999)
into l_mmtt_proj_id , l_mmtt_task_id
from mtl_item_locations
where inventory_location_id = l_locator_id
and organization_id = l_organization_id ;
select nvl(project_id, -999) , nvl(task_id ,-999)
into l_mil_proj_id , l_mil_task_id
from mtl_item_locations
where inventory_location_id = l_loc_id
and organization_id = p_org_id ;
SELECT 1
INTO l_so_cnt
FROM wms_license_plate_numbers
WHERE lpn_context = 11
AND lpn_id = p_lpn
AND organization_id = p_org_id;
SELECT primary_uom_code
, lot_control_code
, serial_number_control_code
INTO l_primary_uom
, l_lot_code
, l_serial_code
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT mmtt.transfer_subinventory
INTO l_xfr_sub_code
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT 1
INTO l_item_cnt
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn
AND wlc.organization_id = p_org_id
AND wlc.inventory_item_id = p_item_id
AND NVL(wlc.revision, '-999') = NVL(p_rev, '-999'));
SELECT 1
INTO l_item_cnt
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wms_lpn_contents wlc, mtl_transaction_lots_temp mtlt
WHERE wlc.parent_lpn_id = p_lpn
AND wlc.organization_id = p_org_id
AND wlc.inventory_item_id = p_item_id
AND NVL(wlc.revision, '-999') = NVL(p_rev, '-999')
AND(mtlt.transaction_temp_id = p_temp_id
AND mtlt.lot_number = wlc.lot_number));
SELECT allocated_lpn_id
INTO l_allocated_lpn_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT COUNT(DISTINCT inventory_item_id)
, COUNT(DISTINCT lot_number)
, COUNT(DISTINCT revision)
, COUNT(DISTINCT cost_group_id)
INTO l_item_cnt2
, l_lot_cnt
, l_rev_cnt
, l_cg_cnt
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn
AND organization_id = p_org_id;
SELECT COUNT(*)
INTO l_lpn_include_lpn
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn
AND organization_id = p_org_id;
SELECT
1,
wlc.quantity,
wlc.uom_code
INTO
l_lot_match,
l_wlc_quantity,
l_wlc_uom_code
FROM
wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn
AND wlc.inventory_item_id = p_item_id
AND wlc.organization_id = p_org_id
AND nvl(wlc.revision,'-999') = nvl(p_rev,'-999')
AND wlc.lot_number = l_mtlt_lot_number;
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: after update qty tree for lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('lpn_match: calling update qty tree with lpn 1st time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: after update qty tree without lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('lpn_match: calling update qty tree back without lpn 1st time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: after update qty tree back for lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('lpn_match: calling update qty tree back with lpn 1st time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: after update qty tree back without lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('lpn_match: calling update qty tree back without lpn 1st time failed ');
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number IN(
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn
AND inventory_item_id = p_item_id
AND NVL(revision, '-999') = NVL(p_rev, '-999'));
SELECT COUNT(fm_serial_number)
INTO l_total_serial_cnt
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
/*SELECT SUM ( INV_Convert.INV_UM_Convert(wlc.inventory_item_id,
null,
wlc.quantity,
wlc.uom_code,
p_uom,
null,
null) )
INTO l_lpn_qty
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn
AND wlc.inventory_item_id = p_item_id
AND Nvl(wlc.revision, '-999') = Nvl(p_rev, '-999'); -- bug fix 2123096 */
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree with lpn 2nd time: l_att:' || l_att);
mydebug('lpn_match: calling update qty tree with lpn 2nd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree without lpn 2nd time:l_att:' || l_att);
mydebug('lpn_match: calling update qty tree back without lpn 2nd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree back with lpn 2nd time: l_att:' || l_att);
mydebug('lpn_match: calling update qty tree with lpn 2nd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree back without lpn 2nd time:l_att:' || l_att);
mydebug('lpn_match: calling update qty tree back without lpn 2nd time failed ');
SELECT primary_quantity
, transaction_uom
INTO l_mmtt_qty
, l_txn_uom
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND msnt.fm_serial_number IN(
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn
AND inventory_item_id = p_item_id
AND NVL(revision, '-999') = NVL(p_rev, '-999'));
SELECT
1,
wlc.quantity,
wlc.uom_code
INTO
l_lot_match,
l_wlc_quantity,
l_wlc_uom_code
FROM
wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn
AND wlc.inventory_item_id = p_item_id
AND wlc.organization_id = p_org_id
AND nvl(wlc.revision,'-999') = nvl(p_rev,'-999')
AND wlc.lot_number = l_mtlt_lot_number;
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree 3rd time for lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('lpn_match: calling update qty tree with lpn 3rd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: after update without lpn 3rd time l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('lpn_match: calling update qty tree back 3rd time without lpn 3rd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree back 3rd time for lpn l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('lpn_match: calling update qty tree with lpn 3rd time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: after update qty tree back without lpn 3rd time l_att:' || l_att || ' for lot:' || l_mtlt_lot_number);
mydebug('lpn_match: calling update qty tree back without lpn 3rd time failed ');
SELECT COUNT(fm_serial_number)
INTO l_total_serial_cnt
FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number IN(
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn
AND inventory_item_id = p_item_id
AND NVL(revision, '-999') = NVL(p_rev, '-999'));
/*SELECT
quantity,
uom_code
INTO
l_exist_qty,
l_lpn_uom
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn
AND organization_id = p_org_id
AND inventory_item_id = p_item_id
AND Nvl(cost_group_id,'-999') = Nvl(p_cost_group_id,'-999')
AND Nvl(revision,'-999') = Nvl(p_rev,'-999');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mmtt_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree with lpn 4th time: l_att:' || l_att);
mydebug('lpn_match: calling update qty tree with lpn 4th time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mmtt_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree without lpn 4th time:l_att:' || l_att);
mydebug('lpn_match: calling update qty tree without lpn 4th time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mmtt_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree back with lpn 4th time: l_att:' || l_att);
mydebug('lpn_match: calling update qty tree back with lpn 4th time failed ');
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => inv_quantity_tree_pub.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => NVL(p_rev, NULL)
, p_lot_number => NULL
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mmtt_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
mydebug('lpn_match: update qty tree back without lpn 4th time:l_att:' || l_att);
mydebug('lpn_match: calling update qty tree back without lpn 4th time failed ');
SELECT COUNT(fm_serial_number)
INTO l_total_serial_cnt
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id;
SELECT 1
INTO l_allocate_serial_flag
FROM mtl_serial_numbers_temp msnt,
mtl_serial_numbers msn
WHERE msnt.transaction_temp_id = l_temp_serial_trans_temp
AND msn.serial_number = msnt.fm_serial_number
AND msn.lpn_id = p_lpn
AND ROWNUM = 1;
SELECT 1
INTO l_allocate_serial_flag
FROM mtl_material_transactions_temp mmtt,
mtl_serial_numbers_temp msnt,
mtl_serial_numbers msn
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.organization_id = p_org_id
AND mmtt.inventory_item_id = p_item_id
AND msnt.transaction_temp_id = mmtt.transaction_temp_id
AND msn.serial_number = msnt.fm_serial_number
AND msn.inventory_item_id = mmtt.inventory_item_id
AND msn.lpn_id = p_lpn
AND ROWNUM = 1;
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => b_is_lot_control
, p_is_serial_control => b_is_serial_control
--, p_demand_source_type_id => NULL
, p_revision => p_rev
, p_lot_number => p_lot
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => b_is_lot_control
, p_is_serial_control => b_is_serial_control
--, p_demand_source_type_id => NULL
, p_revision => p_rev
, p_lot_number => p_lot
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_packing
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = p_lpn;
SELECT decode(mmtt.transaction_type_id, 35,'N',51,'N','Y')
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
WHERE (mmtt.transaction_temp_id = txn_temp_id OR mmtt.parent_line_id = txn_temp_id)
AND mmtt.move_order_line_id = mol.line_id
AND mol.line_status = inv_globals.g_to_status_cancel_by_source
AND ROWNUM = 1;
, p_lots_to_delete IN VARCHAR2
, p_mmtt_to_update IN VARCHAR2
, p_serial_number IN VARCHAR2
, x_out_lpn OUT NOCOPY NUMBER
) IS
l_temp_id NUMBER;
SELECT mmtt2.transaction_temp_id
, mmtt1.transaction_uom
, mmtt2.transaction_uom
, mmtt2.transaction_quantity
, mmtt2.primary_quantity
FROM mtl_material_transactions_temp mmtt1, mtl_material_transactions_temp mmtt2
WHERE mmtt1.transaction_temp_id = p_temp_id
AND mmtt1.transaction_header_id = l_txn_hdr_id
AND mmtt1.organization_id = p_org_id
AND mmtt2.transaction_header_id = mmtt1.transaction_header_id
AND mmtt2.organization_id = mmtt1.organization_id
AND mmtt2.transaction_temp_id <> mmtt1.transaction_temp_id
AND mmtt2.move_order_line_id = mmtt1.move_order_line_id
AND NVL(mmtt2.reservation_id, 0) = NVL(mmtt1.reservation_id, 0)
AND mmtt2.inventory_item_id = mmtt1.inventory_item_id
AND mmtt2.subinventory_code = mmtt1.subinventory_code
AND mmtt2.locator_id = mmtt1.locator_id
AND NVL(mmtt2.transfer_lpn_id, 0) = NVL(mmtt1.transfer_lpn_id, 0)
AND NVL(mmtt2.content_lpn_id, 0) = NVL(mmtt1.content_lpn_id, 0)
AND NVL(mmtt2.lpn_id, 0) = NVL(mmtt1.lpn_id, 0);
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_transaction_header_id;
SELECT transfer_subinventory
, transfer_to_location
INTO l_to_sub
, l_to_loc
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT 1
INTO l_to_lpn_exists
FROM wms_license_plate_numbers
WHERE license_plate_number = p_to_lpn
AND organization_id = p_org_id;
SELECT 0
INTO l_loaded
FROM DUAL
WHERE NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE content_lpn_id = p_from_lpn_id
AND transaction_temp_id<>p_temp_id);
SELECT lpn_id
, lpn_context
INTO l_to_lpn_id
, l_to_lpn_context
FROM wms_license_plate_numbers
WHERE license_plate_number = p_to_lpn
AND organization_id = p_org_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_packing
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_to_lpn_id;
inv_utilities.parse_vector(vector_in => p_mmtt_to_update, delimiter => ':', table_of_strings => l_tabtype);
mydebug('load_pick : MMTT about to update' || l_tabtype(l_counter) || ':');
mydebug('load_pick: getting outermost LPN for selected lpn ON PKLP');
SELECT parent_lpn_id
INTO l_content_parent_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_from_lpn_id
AND organization_id = p_org_id;
mydebug('load_pick: outermost LPN for the selected LPN::' || l_content_parent_lpn_id);
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_packing
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_content_lpn_id;
UPDATE mtl_material_transactions_temp
SET transfer_lpn_id = l_transfer_lpn_id
, content_lpn_id = l_content_lpn_id
, lpn_id = l_lpn_id
WHERE transaction_temp_id = l_tabtype(l_counter);
UPDATE wms_dispatched_tasks
SET status = 4
, loaded_time = SYSDATE
, --loaded_time=to_date(to_char(sysdate,'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH:MI:SS'),
last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_tabtype(l_counter);
SELECT transaction_header_id
, --last_updated_by,Bug 2672785:Wrong value being fetched for user_id
inventory_item_id
, lot_number
, transaction_type_id
, transaction_source_type_id
INTO l_txn_hdr_id
, --l_user_id,
l_item_id
, l_lot
, l_tran_type_id
, l_tran_source_type_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = x_temp_id;
SELECT 1
INTO l_wf
FROM mtl_transaction_reasons
WHERE reason_id = p_qty_rsn_id
AND workflow_name IS NOT NULL
AND workflow_name <> ' '
AND workflow_process IS NOT NULL
AND workflow_process <> ' ';
inv_utilities.parse_vector(vector_in => p_lots_to_delete, delimiter => ':', table_of_strings => l_tabtype);
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE inventory_item_id = l_item_id
AND current_organization_id = p_org_id
AND serial_number IN(SELECT fm_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE lot_number = l_tabtype(l_counter)
AND transaction_temp_id = p_temp_id));
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN(SELECT transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE lot_number = l_tabtype(l_counter)
AND transaction_temp_id = p_temp_id);
DELETE FROM mtl_transaction_lots_temp
WHERE lot_number = l_tabtype(l_counter)
AND transaction_temp_id = p_temp_id;
UPDATE mtl_serial_numbers msn
SET msn.group_mark_id = NULL
WHERE msn.inventory_item_id = l_item_id
AND msn.current_organization_id = p_org_id
AND msn.group_mark_id = p_temp_id
AND msn.serial_number IN(SELECT msnt.fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id);
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number IN(
SELECT msn.serial_number
FROM mtl_serial_numbers msn
WHERE msn.inventory_item_id = l_item_id
AND msn.current_organization_id = p_org_id
AND msn.group_mark_id IS NULL);
SELECT 1
INTO l_bulk_pick_flag
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = p_temp_id);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity + l_mmtt2_txn_qty
, primary_quantity = primary_quantity + l_mmtt2_primary_qty
WHERE transaction_temp_id = p_temp_id;
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = p_temp_id
WHERE transaction_temp_id = l_mmtt2_txn_temp_id;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = p_temp_id
WHERE transaction_temp_id = l_mmtt2_txn_temp_id;
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_mmtt2_txn_temp_id;
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_mmtt2_txn_temp_id;
SELECT transaction_uom
,transaction_quantity
,move_order_line_id
INTO l_mmtt_transaction_uom
,l_transaction_qty
, l_move_order_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
mydebug('load_pick: others exception encounted in selecting
mmtt transaction uom code ');
SELECT uom_code INTO l_mol_uom
FROM mtl_txn_request_lines
WHERE line_id = l_move_order_line_id;
mydebug('load_pick: others exception enchounted in selecting move order uom code');
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
update mtl_txn_request_lines
set quantity_detailed = quantity_detailed - l_mol_delta_qty
where line_id = l_move_order_line_id;
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
update mtl_material_transactions_temp
set transaction_temp_id=p_temp_id
where transaction_temp_id=x_temp_id; --Added bug 3765153
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
PROCEDURE insert_mmtt_pack(
p_temp_id IN NUMBER
, p_lpn_id IN NUMBER
, p_transfer_lpn IN VARCHAR2
, p_container_item_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_mmtt_rec inv_mo_line_detail_util.g_mmtt_rec;
mydebug('insert_mmtt_pack: No LPN was passed');
SELECT transfer_cost_group_id
, organization_id
, transfer_subinventory
, transfer_to_location
INTO l_cost_group_id
, l_org_id
, l_sub
, l_loc
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id;
/* SELECT COUNT(*) INTO l_exist_lpn
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE license_plate_number=l_lpn
AND organization_id=l_org_id;*/
SELECT 1
INTO l_exist_lpn
FROM DUAL
WHERE EXISTS(SELECT 1
FROM wms_license_plate_numbers
WHERE license_plate_number = l_lpn
AND organization_id = l_org_id);
mydebug('insert_mmtt_pack: Creating LPN');
mydebug('insert_mmtt_pack: Successful');
mydebug('insert_mmtt_pack: Not Successful');
mydebug('insert_mmtt_pack: Not Successful2');
SELECT lpn_id
INTO l_transfer_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = l_lpn
AND organization_id = l_org_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_temp_id
FROM DUAL;
INSERT INTO mtl_material_transactions_temp
(
transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, operation_plan_id
, transaction_batch_id
, transaction_batch_seq
)
(SELECT transaction_header_id
, l_new_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, transfer_subinventory
, transfer_to_location
, 1
, 1
, transaction_uom
, transaction_cost
, inv_globals.g_type_container_pack
, 50
, 13
, NULL
, NULL
, SYSDATE
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, NULL
, task_group_id
, pick_slip_number
, NULL
, transaction_status
, transfer_cost_group_id
, lpn_id
, l_transfer_lpn_id
, l_lpn_id
, operation_plan_id
, transaction_header_id
, l_new_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id);
END insert_mmtt_pack;
SELECT lpn_id INTO l_sug_lpn_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE license_plate_number=l_sug_lpn_name
AND organization_id=l_org_id;
SELECT lpn_id
INTO l_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = l_lpn_name
AND organization_id = l_org_id;
/* SELECT COUNT(*) INTO l_exist_lpn
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE license_plate_number=l_lpn_name
AND organization_id=l_org_id
AND lpn_context<>wms_container_pub.lpn_context_packing;*/
SELECT 1
INTO l_exist_lpn
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wms_license_plate_numbers
WHERE license_plate_number = l_lpn_name
AND organization_id = l_org_id
AND lpn_context <> wms_container_pub.lpn_context_packing);
/* SELECT COUNT(*) INTO l_exist_lpn2
FROM wms_license_plate_numbers w, wms_lpn_contents c
WHERE w.license_plate_number=l_sug_lpn_name
AND w.organization_id=l_org_id
AND w.lpn_id=c.parent_lpn_id;*/
SELECT 1
INTO l_exist_lpn2
FROM DUAL
WHERE EXISTS(SELECT 1
FROM wms_license_plate_numbers w, wms_lpn_contents c
WHERE w.license_plate_number = l_sug_lpn_name
AND w.organization_id = l_org_id
AND w.lpn_id = c.parent_lpn_id);
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND NVL(lot_number, 'NONE') = NVL(v_lot_number, 'NONE');
SELECT lot_number
, quantity
, uom_code
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id;
SELECT *
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_temp_id
AND lot_number = v_lot_number;
mydebug('multiple_lpn_pick: getting outermost LPN for selected lpn ON PKLP');
SELECT parent_lpn_id,subinventory_code,locator_id
INTO l_content_parent_lpn_id,l_lpn_sub,l_lpn_loc
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id
AND organization_id = p_org_id;
mydebug('multiple_lpn_pick: outermost LPN for the selected LPN::' || l_content_parent_lpn_id);
SELECT transfer_cost_group_id
, transfer_subinventory
, transfer_to_location
, lot_number
, transaction_quantity
, transaction_uom
, inventory_item_id
, last_updated_by
, move_order_line_id
INTO l_cost_group_id
, l_sub
, l_loc
, l_lot
, l_orig_qty
, l_uom
, l_item_id
, l_user_id
, l_mo_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_temp_id
FROM DUAL;
INSERT INTO mtl_material_transactions_temp
(
transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, cartonization_id
, standard_operation_id
, wms_task_type
, task_priority
, container_item_id
, operation_plan_id
)
(SELECT transaction_header_id
, l_new_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, l_lpn_sub --, subinventory_code changed to LPN's sub bug3765153
, l_lpn_loc --, locator_id changed to LPN's sub bug3765153
, l_qty
, l_pr_qty
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, transfer_cost_group_id
, l_temp_lpn_id
, l_transfer_lpn_id
, l_content_lpn_id
, cartonization_id
, standard_operation_id
, wms_task_type
, task_priority
, container_item_id
, operation_plan_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - l_qty
, primary_quantity = primary_quantity - l_pr_qty
WHERE transaction_temp_id = l_temp_id;
SELECT lot_control_code
, serial_number_control_code
INTO l_lot_code
, l_serial_code
FROM mtl_system_items
WHERE organization_id = l_org_id
AND inventory_item_id = l_item_id;
mydebug('multiple_lpn_pick: Inserting Lots');
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_temp_id
FROM DUAL;
inv_rcv_common_apis.insert_mtlt(l_mtlt_rec);
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_new_serial_temp_id
WHERE transaction_temp_id = l_lot_serial_temp_id
AND fm_serial_number = l_serial_number
AND to_serial_number = l_serial_number;
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity - l_mtlt_rec.primary_quantity
, transaction_quantity = transaction_quantity - l_mtlt_rec.transaction_quantity
WHERE transaction_temp_id = p_temp_id
AND lot_number = l_lpn_lot_number;
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = l_new_temp_id
WHERE transaction_temp_id = p_temp_id
AND lot_number = l_lpn_lot_number;
/* Moved the selection of serial temp id here */
IF (l_serial_code > 1
AND l_serial_code <> 6) THEN
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_temp_id
FROM DUAL;
inv_rcv_common_apis.insert_mtlt(l_mtlt_rec);
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity - l_mtlt_rec.primary_quantity
, transaction_quantity = transaction_quantity - l_mtlt_rec.transaction_quantity
WHERE transaction_temp_id = p_temp_id
AND lot_number = l_lpn_lot_number;
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
)
VALUES (
l_new_serial_temp_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_serial_number
, l_serial_number
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_temp_id
FROM DUAL;
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = l_new_temp_id
, serial_transaction_temp_id = NVL(l_new_serial_temp_id, serial_transaction_temp_id)
WHERE transaction_temp_id = p_temp_id
AND lot_number = l_lpn_lot_number;
/* AS: Need to insert Serial Numbers here also*/
IF (l_serial_code > 1
AND l_serial_code <> 6) THEN
IF (l_debug = 1) THEN
mydebug('multiple_lpn_pick: Lot/Ser controlled, allocate to serial OFF');
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
)
VALUES (
l_new_serial_temp_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_serial_number
, l_serial_number
);
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_new_temp_id
WHERE transaction_temp_id = p_temp_id
AND fm_serial_number = l_serial_number
AND to_serial_number = l_serial_number;
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
)
VALUES (
l_new_temp_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_serial_number
, l_serial_number
);
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM DUAL;
mydebug('multiple_lpn_pick: Before Insert into WMSDT');
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, loaded_time
, operation_plan_id
, move_order_line_id
)
(SELECT l_next_task_id
, l_new_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, 4
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, SYSDATE
, operation_plan_id
, move_order_line_id
--to_date(to_char(sysdate,'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH:MI:SS')
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_temp_id);
mydebug('multiple_lpn_pick: Update WMSDT as loaded');
SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda
, wsh_delivery_details wdd
, mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_lpn_id = l_lpn_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.organization_id = mmtt.organization_id;
SELECT lpn_id
, lpn_context
, outermost_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_pick_to_lpn;
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = l_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda
, wsh_delivery_details wdd
, mtl_material_transactions_temp mmtt
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.organization_id = mmtt.organization_id
AND mmtt.transaction_temp_id = p_temp_id
AND mmtt.organization_id = p_organization_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id = l_lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd.organization_id = p_organization_id;
SELECT NVL(mil.project_id, -1)
, NVL(mil.task_id, -1)
FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
WHERE mil.inventory_location_id = mmtt.transfer_to_location
AND mil.organization_id = mmtt.organization_id
AND mmtt.transfer_lpn_id = p_pick_to_lpn
AND mmtt.organization_id = p_organization_id;
SELECT NVL(mil.project_id, -1)
, NVL(mil.task_id, -1)
FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
WHERE mil.inventory_location_id = mmtt.transfer_to_location
AND mil.organization_id = mmtt.organization_id
AND mmtt.organization_id = p_organization_id
AND mmtt.transaction_temp_id = p_temp_id;
SELECT mol.carton_grouping_id
FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.organization_id = mol.organization_id
AND mmtt.move_order_line_id = mol.line_id;
SELECT DISTINCT mol.carton_grouping_id
FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.organization_id = mol.organization_id
AND mmtt.move_order_line_id = mol.line_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_packing
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = pick_to_lpn_rec.lpn_id;
SELECT mtrh.move_order_type
, mmtt.transaction_type_id
, mmtt.wip_entity_type
INTO l_mmtt_mo_type
, l_mmtt_txn_type_id
, l_mmtt_wip_entity_type
FROM mtl_txn_request_headers mtrh
, mtl_txn_request_lines mtrl
, mtl_material_transactions_temp mmtt
WHERE mtrh.header_id = mtrl.header_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mmtt.transaction_temp_id = p_temp_id;
SELECT mtrh.move_order_type
, mmtt.wip_entity_type
INTO l_mo_type_in_lpn
, l_wip_entity_type_in_lpn
FROM mtl_txn_request_headers mtrh
, mtl_txn_request_lines mtrl
, mtl_material_transactions_temp mmtt
WHERE mtrh.header_id = mtrl.header_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND ROWNUM < 2;
SELECT mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.inventory_item_id
, mmtt.operation_plan_id
INTO l_xfr_sub
, l_xfr_to_location
, l_item_id
, l_operation_plan_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT lpn_controlled_flag
INTO l_lpn_controlled_flag
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = l_xfr_sub;
SELECT COUNT(*)
INTO l_count
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id <> p_temp_id
AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND ( NVL(mmtt.transfer_subinventory, 0) <> l_xfr_sub
OR
NVL(mmtt.transfer_to_location, 0) <> l_xfr_to_location
);
SELECT 1
INTO l_count
FROM DUAL
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
, mtl_secondary_inventories msi
WHERE mmtt.transaction_temp_id <> p_temp_id
AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND msi.organization_id = p_organization_id
AND msi.secondary_inventory_name = mmtt.transfer_subinventory
AND msi.lpn_controlled_flag = wms_globals.g_lpn_controlled_sub
);
SELECT COUNT(1)
INTO l_count
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id <> p_temp_id
AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND mmtt.operation_plan_id <> l_operation_plan_id;
SELECT wdd.delivery_detail_id INTO l_line_rows(1)
FROM wsh_delivery_details wdd
, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.organization_id = mmtt.organization_id;
SELECT wdd.delivery_detail_id INTO l_line_rows(2)
FROM wsh_delivery_details wdd
, mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.organization_id = mmtt.organization_id
AND rownum<2;
SELECT transaction_header_id
, subinventory_code
, locator_id
, transfer_subinventory
, transfer_to_location
, revision
, transaction_quantity
, primary_quantity
, transaction_uom
, inventory_item_id
, last_updated_by
, move_order_line_id
, reservation_id
INTO l_txn_header_id
, l_sug_sub
, l_sug_loc
, l_to_sub
, l_to_loc
, l_sug_rev
, l_orig_txn_qty
, l_orig_primary_qty
, l_sug_uom
, l_item_id
, l_user_id
, l_mo_line_id
, l_reservation_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id;
SELECT lot_control_code
, serial_number_control_code
INTO l_lot_code
, l_serial_code
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = l_item_id;
SELECT 1
INTO l_to_lpn_exists
FROM wms_license_plate_numbers
WHERE license_plate_number = p_to_lpn
AND organization_id = p_org_id;
SELECT lpn_id
, lpn_context
INTO l_to_lpn_id
, l_to_lpn_context
FROM wms_license_plate_numbers
WHERE license_plate_number = p_to_lpn
AND organization_id = p_org_id;
SELECT subinventory_code,locator_id --Bug#4339517 . Added locator_id
INTO l_res_sub,l_res_loc --Bug#4339517 . Added l_res_loc
FROM mtl_reservations
WHERE reservation_id = l_reservation_id
AND organization_id = p_org_id
AND inventory_item_id = l_item_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_temp_id
FROM DUAL;
INSERT INTO mtl_material_transactions_temp
(
transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, cartonization_id
, standard_operation_id
, wms_task_type
, task_priority
, container_item_id
, operation_plan_id
)
(SELECT transaction_header_id
, l_new_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, l_qty
, l_pr_qty
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, l_to_reservation_id -- This is the new reservation ID
, transaction_status
, transfer_cost_group_id
, NULL -- lpn_id is null for loose
, l_to_lpn_id -- transfer_lpn_id is toLPN for loose
, NULL -- content_lpn_id is NULL for loose
, cartonization_id
, standard_operation_id
, wms_task_type
, task_priority
, container_item_id
, operation_plan_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - l_qty
, primary_quantity = primary_quantity - l_pr_qty
, lpn_id = null --bug 4046834
, content_lpn_id = null
, transfer_lpn_id = null
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_temp_id;
mydebug('multiple_pick: Inserting Lots');
SELECT *
INTO l_mtlt_rec
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_temp_id
AND lot_number = p_lot;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_temp_id
FROM DUAL;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_new_serial_temp_id
WHERE transaction_temp_id = l_mtlt_rec.serial_transaction_temp_id
AND fm_serial_number = l_fm_serial
AND to_serial_number = l_to_serial;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_temp_id
FROM DUAL;
mydebug('multiple_pick: Inserting into MTLT');
inv_rcv_common_apis.insert_mtlt(l_mtlt_rec);
UPDATE mtl_transaction_lots_temp
SET primary_quantity = primary_quantity - l_mtlt_rec.primary_quantity
, transaction_quantity = transaction_quantity - l_mtlt_rec.transaction_quantity
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_temp_id
AND lot_number = p_lot;
mydebug('multiple_pick: After lot update');
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_quantity = 0
AND lot_number = p_lot
AND transaction_temp_id = p_temp_id;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = l_new_temp_id
WHERE transaction_temp_id = l_temp_id
AND fm_serial_number = l_fm_serial
AND to_serial_number = l_to_serial;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM DUAL;
mydebug('multiple_pick: Before Insert into WMSDT');
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, loaded_time
, operation_plan_id
, move_order_line_id
)
(SELECT l_next_task_id
, l_new_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, 3
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, SYSDATE
, operation_plan_id
, move_order_line_id
--to_date(to_char(sysdate,'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH:MI:SS')
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_temp_id);
this IS the CASE, we DO NOT need TO INSERT INTO mtlt OR msnt */
l_lpn_exact_match := 'Y';
SELECT subinventory_code
, locator_id
INTO l_act_sub
, l_act_loc
FROM wms_license_plate_numbers
WHERE lpn_id = l_from_lpn_id;
mydebug('multiple_pick: getting outermost LPN for selected lpn ON PKLP');
SELECT parent_lpn_id
INTO l_content_parent_lpn_id
FROM wms_license_plate_numbers
WHERE lpn_id = l_from_lpn_id
AND organization_id = p_org_id;
mydebug('multiple_pick: outermost LPN for the selected LPN::' || l_content_parent_lpn_id);
UPDATE mtl_material_transactions_temp
SET content_lpn_id = l_from_lpn_id
, transfer_lpn_id = l_to_lpn_id
, lpn_id = l_lpn_id
, subinventory_code = l_act_sub
, locator_id = l_act_loc
, transaction_uom = p_act_uom
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_local_temp_id;
UPDATE mtl_material_transactions_temp
SET transfer_lpn_id = l_to_lpn_id
, lpn_id = l_from_lpn_id
, content_lpn_id = NULL
, subinventory_code = l_act_sub
, locator_id = l_act_loc
, transaction_uom = p_act_uom
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_local_temp_id;
SELECT NVL(serial_transaction_temp_id, 0)
INTO l_lot_ser_seq
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_local_temp_id
AND lot_number = l_lot;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_temp_id
FROM DUAL;
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
)
VALUES (
l_new_serial_temp_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_fm_serial
, l_to_serial
);
UPDATE mtl_serial_numbers
SET group_mark_id = l_txn_header_id
WHERE inventory_item_id = l_item_id
AND current_organization_id = p_org_id
AND serial_number IN(SELECT fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_new_serial_temp_id);
SELECT count(MSNT.transaction_temp_id)
INTO l_serial_count
FROM Mtl_Serial_Numbers_Temp MSNT
WHERE MSNT.fm_serial_number = l_fm_serial;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_qty
, primary_quantity = l_pr_qty
, serial_transaction_temp_id = l_new_serial_temp_id
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_local_temp_id
AND lot_number = p_lot;
mydebug('multiple_pick: MTLT did not get updated. Lot number NOT passed');
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
, serial_prefix
)
VALUES (
l_local_temp_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_fm_serial
, l_to_serial
, l_serial_prefix
);
UPDATE mtl_serial_numbers
SET group_mark_id = l_txn_header_id
WHERE inventory_item_id = l_item_id
AND current_organization_id = p_org_id
AND serial_number IN(SELECT fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_local_temp_id);
SELECT count(MSNT.transaction_temp_id)
INTO l_serial_count
FROM Mtl_Serial_Numbers_Temp MSNT
WHERE MSNT.fm_serial_number = l_fm_serial;
UPDATE mtl_material_transactions_temp
SET transfer_lpn_id = l_to_lpn_id
, lpn_id = NULL
, content_lpn_id = NULL
, subinventory_code = l_act_sub
, locator_id = l_act_loc
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_local_temp_id;
SELECT NVL(serial_transaction_temp_id, 0)
INTO l_lot_ser_seq
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_local_temp_id
AND lot_number = l_lot;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_temp_id
FROM DUAL;
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
)
VALUES (
l_new_serial_temp_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_fm_serial
, l_to_serial
);
UPDATE mtl_serial_numbers
SET group_mark_id = l_txn_header_id
WHERE inventory_item_id = l_item_id
AND current_organization_id = p_org_id
AND serial_number IN(SELECT fm_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_new_serial_temp_id);
SELECT count(MSNT.transaction_temp_id)
INTO l_serial_count
FROM Mtl_Serial_Numbers_Temp MSNT
WHERE MSNT.fm_serial_number = l_fm_serial;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_qty
, primary_quantity = l_pr_qty
, serial_transaction_temp_id = l_new_serial_temp_id
, last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_local_temp_id
AND lot_number = p_lot;
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
, serial_prefix
)
VALUES (
l_local_temp_id
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_fm_serial
, l_to_serial
, NVL(l_serial_prefix, 1)
);
UPDATE mtl_serial_numbers
SET group_mark_id = l_txn_header_id
WHERE inventory_item_id = l_item_id
AND current_organization_id = p_org_id
AND serial_number IN(SELECT fm_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_local_temp_id);
SELECT count(MSNT.transaction_temp_id)
INTO l_serial_count
FROM Mtl_Serial_Numbers_Temp MSNT
WHERE MSNT.fm_serial_number = l_fm_serial;
mydebug('multiple_pick: Lot or serial controlled. Do not updated wmsdt AS loaded');
mydebug('multiple_pick: Plain item. Update WMSDT as loaded');
UPDATE wms_dispatched_tasks
SET status = 4
, loaded_time = SYSDATE
, --to_date(to_char(sysdate,'DD-MON-YYYY HH:MI:SS'),'DD-MON-YYYY HH:MI:SS'),
last_update_date = SYSDATE
, last_updated_by = l_user_id
WHERE transaction_temp_id = l_local_temp_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_packing
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_to_lpn_id;
/* SELECT COUNT(*) INTO l_exist FROM wms_license_plate_numbers
WHERE license_plate_number=p_lpn;*/
SELECT 1
INTO l_exist
FROM DUAL
WHERE EXISTS(SELECT 1
FROM wms_license_plate_numbers
WHERE license_plate_number = p_lpn);
SELECT lpn_id
INTO p_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_lpn;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = l_txn_header_id
AND mmtt.organization_id = l_org_id;
SELECT serial_number
, organization_id
, inventory_item_id
, transaction_temp_id, lpn_id
FROM wms_wsh_wdd_gtemp WHERE lpn_id = lpnid_in_msn;
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = l_xfrlpnid
CONNECT BY PRIOR lpn_id = parent_lpn_id;
SELECT WDD.DELIVERY_DETAIL_ID,
MTRL.SHIP_SET_ID,
MTRL.SHIP_MODEL_ID,
WRD.primary_quantity WRD_PRI_QUANTITY,
Nvl(WDD.requested_quantity,0) REQUESTED_QUANTITY,
WRD.primary_uom,
wrd.repl_type,
wrd.repl_level, -- TO DECIDE WHETHER TO PICK_RELEASE DEMAND OR allocate repl MO FOR LEVEL > 1
wdd.batch_id,
wrd.demand_type_id
FROM
wms_replenishment_details WRD,
MTL_TXN_REQUEST_LINES MTRL,
WSH_DELIVERY_DETAILS WDD
WHERE WRD.ORGANIZATION_ID = P_ORG_ID
AND WRD.SOURCE_LINE_ID = p_mo_line_id
AND wrd.demand_type_id <> 4 -- true only for first level of REPL
AND WRD.SOURCE_LINE_ID = MTRL.LINE_ID
AND WRD.SOURCE_HEADER_ID = MTRL.HEADER_ID
AND WRD.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
AND WRD.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID
AND WRD.DEMAND_LINE_DETAIL_ID = WDD.delivery_detail_id
AND WRD.ORGANIZATION_ID = WDD.organization_id
ORDER BY wdd.batch_id, DEMAND_SORT_ORDER;
SELECT wrd.demand_header_id,
wrd.demand_line_id,
WRD.primary_quantity WRD_PRI_QUANTITY,
wrd.repl_level, -- TO DECIDE WHETHER TO PICK_RELEASE DEMAND OR allocate repl MO FOR LEVEL > 1
wrd.demand_type_id
FROM
wms_replenishment_details WRD,
MTL_TXN_REQUEST_LINES MTRL
WHERE WRD.ORGANIZATION_ID = P_ORG_ID
AND WRD.SOURCE_LINE_ID = p_mo_line_id
AND wrd.demand_type_id = 4 -- true for multi level repl (>1)
AND WRD.SOURCE_LINE_ID = MTRL.LINE_ID
AND WRD.SOURCE_HEADER_ID = MTRL.HEADER_ID
AND WRD.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
AND WRD.INVENTORY_ITEM_ID = MTRL.inventory_item_id
ORDER BY DEMAND_SORT_ORDER;
SELECT
WDD.DELIVERY_DETAIL_ID,
wdd.requested_quantity AS PRIMARY_QUANTITY, -- this is always stored in primary UOM
wdd.requested_quantity_uom,
-- get for sort_attribute1
To_number(DECODE(l_release_sequence_rule_id,
null,
null,
DECODE(g_ordered_psr(1).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
wms_replenishment_pvt.GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
wms_replenishment_pvt.GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute1,
-- get for sort_attribute2
To_number(DECODE(l_release_sequence_rule_id,
null,
null,
DECODE(g_ordered_psr(2).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
wms_replenishment_pvt.GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
wms_replenishment_pvt.GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute2,
-- get for sort_attribute3
To_number(DECODE(l_release_sequence_rule_id,
null,
null,
DECODE(g_ordered_psr(3).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
wms_replenishment_pvt.GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
wms_replenishment_pvt.GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute3,
-- get for sort_attribute4
To_number(DECODE(l_release_sequence_rule_id,
null,
null,
DECODE(g_ordered_psr(4).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
NULL),
'INVOICE_VALUE',
wms_replenishment_pvt.GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
wms_replenishment_pvt.GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute4,
-- get for sort_attribute5
To_number(DECODE(l_release_sequence_rule_id,
null,
null,
DECODE(g_ordered_psr(5).attribute_name,
'ORDER_NUMBER',
DECODE(L_ORDER_ID_SORT,
'ASC',
To_number(wdd.source_header_number),
'DESC',
(-1 * To_number(wdd.SOURCE_HEADER_NUMBER)),
null),
'SHIPMENT_PRIORITY',
DECODE(WDD.SHIPMENT_PRIORITY_CODE,
'High',
20,
'Standard',
10,
null),
'INVOICE_VALUE',
wms_replenishment_pvt.GET_SORT_INVOICE_VALUE(WDD.SOURCE_HEADER_ID,
L_INVOICE_VALUE_SORT),
'SCHEDULE_DATE',
DECODE(L_SCHEDULE_DATE_SORT,
'ASC',
(WDD.DATE_SCHEDULED -
TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS')),
'DESC',
(TO_DATE('01-01-1700 23:59:59',
'DD-MM-YYYY HH24:MI:SS') -
WDD.DATE_SCHEDULED),
null),
'TRIP_STOP_DATE',
wms_replenishment_pvt.GET_SORT_TRIP_STOP_DATE(wdd.delivery_detail_id,
L_TRIP_STOP_DATE_SORT),
NULL))) as sort_attribute5
FROM
WSH_DELIVERY_DETAILS wdd
WHERE
wdd.source_code = 'OE'
AND wdd.requested_quantity > 0
And WDD.ORGANIZATION_ID = L_ORG_ID
AND WDD.INVENTORY_ITEM_ID = P_ITEM_ID
-- original status demand lines only
AND wdd.released_status in ('R','B') and replenishment_status is NULL
AND wdd.subinventory IS NULL -- since push_repl conc program does not consider forward pick sub either
-- these demands should not be part of WRD
AND NOT EXISTS (select wrd.demand_line_detail_id
from WMS_REPLENISHMENT_DETAILS wrd
where wrd.demand_line_detail_id = wdd.delivery_detail_id
and wrd.demand_line_id = wdd.source_line_id
and wrd.organization_id = wdd.organization_id)
ORDER BY organization_id, sort_attribute1, sort_attribute2, sort_attribute3, sort_attribute4, sort_attribute5;
SELECT organization_id, TRANSACTION_TEMP_ID, MOVE_ORDER_LINE_ID, INVENTORY_ITEM_ID, PRIMARY_QUANTITY
FROM mtl_material_transactions_temp mmtt
WHERE TRANSACTION_HEADER_ID = l_txn_header_id
AND mmtt.organization_id = l_org_id
ORDER BY move_order_line_id asc,primary_quantity desc ;
mydebug('pick_drop: Creating final row for packing. Calling insert_mmtt_pack');
wms_task_dispatch_gen.insert_mmtt_pack(
p_temp_id => p_temp_id
, p_lpn_id => p_from_lpn_id
, p_transfer_lpn => p_drop_lpn
, p_container_item_id => 0
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
mydebug('pick_drop: Insert MMTT pack Unexpected error');
mydebug('pick_drop: Insert MMTT pack error');
mydebug('pick_drop: After call to insert_mmtt_pack');
SELECT lpn_id
, content_lpn_id
, transfer_lpn_id
, subinventory_code
, locator_id
, transfer_subinventory
, transfer_to_location
, transaction_type_id
, move_order_line_id
, transaction_source_type_id
, inventory_item_id
, transaction_action_id
INTO l_lpn_id
, l_content_lpn_id
, l_transfer_lpn_id
, l_from_sub
, l_from_loc
, l_transfer_sub
, l_transfer_loc
, l_tran_type_id
, l_mo_line_id
, l_tran_source_type_id
, l_inventory_item_id
, l_tran_action_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id
AND organization_id = l_org_id;
SELECT LPN_CONTROLLED_FLAG
INTO l_is_transfer_sub_lpn
FROM mtl_secondary_inventories
WHERE ORGANIZATION_ID = p_org_id
AND SECONDARY_INVENTORY_NAME = l_transfer_sub;
SELECT 1
INTO l_cnt
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE parent_line_id = l_temp_id);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_header_id
, lpn_id = l_lpn_id
, transfer_lpn_id = l_transfer_lpn_id
, transaction_status = 3
WHERE parent_line_id = l_temp_id
AND organization_id = l_org_id;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_header_id
, lpn_id = l_content_lpn_id
, transfer_lpn_id = l_content_lpn_id
, content_lpn_id = NULL
, transaction_status = 3
WHERE parent_line_id = l_temp_id
AND organization_id = l_org_id;
UPDATE wms_license_plate_numbers
SET subinventory_code = l_transfer_sub
, locator_id = l_transfer_loc
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_content_lpn_id
AND organization_id = l_org_id;
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id;
mydebug('pick_drop: Need to update the account period in MMTT');
UPDATE mtl_material_transactions_temp
SET acct_period_id = l_period_id
WHERE transaction_header_id = l_txn_header_id
AND organization_id = l_org_id;
SELECT 1
INTO l_wf
FROM mtl_transaction_reasons
WHERE reason_id = l_loc_reason_id
AND workflow_name IS NOT NULL
AND workflow_name <> ' '
AND workflow_process IS NOT NULL
AND workflow_process <> ' ';
mydebug('pick_drop: Insert WDT History');
, p_delete_mmtt_flag => 'N'
, p_txn_header_id => l_txn_header_id
, p_transfer_lpn_id => NVL(l_transfer_lpn_id, l_content_lpn_id)
);
SELECT lpn_id
INTO l_xfrlpnid
FROM wms_license_plate_numbers
WHERE license_plate_number = p_drop_lpn;
SELECT count(transaction_temp_id)
INTO l_check_tasks
FROM mtl_material_transactions_temp mmtt
WHERE transfer_lpn_id = l_transfer_lpn_id
AND transaction_temp_id <> l_temp_id ;
UPDATE mtl_material_transactions_temp
SET transaction_date = SYSDATE
WHERE transaction_header_id = l_txn_header_id;
SELECT COUNT(wdd2.lpn_id)
INTO l_shipped_wdd_count_in_lpn
FROM wsh_delivery_details wdd1
, wsh_delivery_details wdd2
, wsh_delivery_assignments_v wda
, wms_license_plate_numbers wlpn
WHERE wdd1.released_status = 'C'
AND wda.delivery_detail_id = wdd1.delivery_detail_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wdd2.lpn_id = wlpn.lpn_id
AND wlpn.outermost_lpn_id = l_content_lpn_id;
DELETE wms_wsh_wdd_gtemp;
INSERT INTO wms_wsh_wdd_gtemp
(SERIAL_NUMBER
, organization_id
, INVENTORY_ITEM_ID
, transaction_temp_id
, LPN_ID)
SELECT serial_number
, current_organization_id
, inventory_item_id
, group_mark_id
, lpn_id
FROM mtl_serial_numbers
WHERE lpn_id = rec_child_lpns_csr.lpn_id;
mydebug('Repl MMTT records selected - Count :'||l_drop_lpn_item_tbl.COUNT() );
SELECT 1, demand_type_id INTO l_exists_in_wrd, l_demand_type_id
FROM wms_replenishment_details wrd
WHERE WRD.ORGANIZATION_ID = P_ORG_ID
AND WRD.SOURCE_LINE_ID = L_DROP_LPN_ITEM_TBL(CNT).MOVE_ORDER_LINE_ID
AND WRD.INVENTORY_ITEM_ID = L_DROP_LPN_ITEM_TBL(CNT).INVENTORY_ITEM_ID
AND ROWNUM = 1;
DELETE FROM WMS_REPLENISHMENT_DETAILS
WHERE organization_id = p_org_id
AND demand_type_id = 4
AND demand_header_id = l_demand_header_id
AND demand_line_id = l_demand_line_id;
wms_replenishment_pvt.update_wdd_repl_status
(p_deliv_detail_id => l_delivery_detail_id
, p_repl_status => 'C' -- for completed status
, x_return_status => l_return_status
);
DELETE FROM WMS_REPLENISHMENT_DETAILS
WHERE organization_id = p_org_id
AND DEMAND_LINE_DETAIL_ID= l_delivery_detail_id;
-- the wms_replenishment_pvt.update_delivery_detail() API that insert the newly created
-- record with RR status in the WRD table AND updates the qty to l_remaining_mmtt_qty
-- for the old delivery_detail record in the WRD table
-- So after calling for the split-line action, I need to call shipping
-- AND mark the original delivery detail line to 'RC' and delete this WRD record
l_detail_id_tab.DELETE;
-- At this point the new delivery is already inserted in the WRD table.
-- AND the qty for original delivery has been updated
-- TO l_remaining_mmtt_qty IN WRD as above api calls
-- wms_replenishment_pvt.update_delivery_detail() internally.
-- SO JUST MARK original delivery replenishment_status RC
-- and then delete the original record from the WRD table
wms_replenishment_pvt.update_wdd_repl_status
(p_deliv_detail_id => l_delivery_detail_id
, p_repl_status => 'C' -- for completed status
, x_return_status => l_return_status
);
-- delete the original demand as it was marked RC
-- nwely created delviery will be in WRD as 'RR'
DELETE FROM WMS_REPLENISHMENT_DETAILS
WHERE DEMAND_LINE_DETAIL_ID= l_delivery_detail_id;
select pick_sequence_rule_id
INTO l_release_sequence_rule_id
from wsh_shipping_parameters
where organization_id = l_org_id;
-- delete old value
g_ordered_psr.DELETE;
wms_replenishment_pvt.update_wdd_repl_status
(p_deliv_detail_id => l_delivery_detail_id
, p_repl_status => 'C' -- for completed status
, x_return_status => l_return_status
);
-- the wms_replenishment_pvt.update_delivery_detail()
-- API but in this case since original
-- delivery_detial was NOT tracked in the WRD table
-- to start with, nothing happens there. In shipping,
-- we have a new split WDD though
-- So after calling for the split-line action, I need to call shipping
-- AND mark the original delivery detail line to 'RC' and delete this WRD record
l_detail_id_tab.DELETE;
-- At this point the new delivery is already inserted in the WRD table.
-- AND the qty for original delivery has been updated
-- TO l_remaining_mmtt_qty IN WRD as above api calls
-- wms_replenishment_pvt.update_delivery_detail() internally.
-- SO JUST MARK original delviery replenishment_status RC
-- and then delete the original record from the WRD table
wms_replenishment_pvt.update_wdd_repl_status
(p_deliv_detail_id => l_delivery_detail_id
, p_repl_status => 'C' -- for completed status
, x_return_status => l_return_status
);
-- in the WRD table. So no need to delete WRD
L_REMAINING_MMTT_QTY := 0;
UPDATE mtl_serial_numbers
SET mtl_serial_numbers.group_mark_id = rec_msn_stg_mov_csr.transaction_temp_id
WHERE mtl_serial_numbers.serial_number = rec_msn_stg_mov_csr.serial_number
AND mtl_serial_numbers.current_organization_id = rec_msn_stg_mov_csr.organization_id
AND mtl_serial_numbers.inventory_item_id = rec_msn_stg_mov_csr.inventory_item_id
AND mtl_serial_numbers.lpn_id = rec_msn_stg_mov_csr.lpn_id;
l_detail_id_tab.DELETE;
-- Since inserted records has alredy been ordered by batch_id
END LOOP;
l_batch_id_tab.DELETE;
l_pick_rel_tab.DELETE;
SELECT COUNT(*)
INTO l_mmtt_rowcnt
FROM mtl_material_transactions_temp m
WHERE m.cartonization_id IS NOT NULL
AND m.cartonization_id = l_lpn_id
AND parent_line_id IS NULL;
SELECT COUNT(*)
INTO l_wdt_rowcnt
FROM mtl_material_transactions_temp m, wms_dispatched_tasks t
WHERE m.cartonization_id = l_lpn_id
AND t.transaction_temp_id = m.transaction_temp_id
AND t.status = 4;
SELECT bremp.resource_id role_id
, t.wms_task_type
, t.standard_operation_id
, t.operation_plan_id
, t.move_order_line_id
INTO l_per_res_id
, l_wms_task_type
, l_std_op_id
, l_operation_plan_id
, l_move_order_line_id
FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
WHERE t.transaction_temp_id = task_rec.task_id
AND t.standard_operation_id = bsor.standard_operation_id
AND bsor.resource_id = bremp.resource_id
AND bremp.resource_type = 2
AND ROWNUM < 2;
SELECT resource_id
INTO l_mac_res_id
FROM bom_resource_equipments
WHERE inventory_item_id = l_eqp_id
AND ROWNUM < 2;
select breqp.resource_id equip_type_id
INTO l_mac_res_id
from mtl_material_transactions_temp t,
bom_std_op_resources bsor,
bom_resources breqp
where t.transaction_temp_id = task_rec.task_id
and t.standard_operation_id = bsor.standard_operation_id
and bsor.resource_id = breqp.resource_id
and breqp.resource_type = 1
and rownum<2;
SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
INTO l_txn_hdr_id
FROM DUAL;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_hdr_id
WHERE transaction_temp_id = task_rec.task_id;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM DUAL;
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, priority
, operation_plan_id
, move_order_line_id
)
VALUES (
l_next_task_id
, task_rec.task_id
, l_org_id
, NVL(l_std_op_id, 2)
, l_user_id
, SYSDATE
, SYSDATE
, l_eqp_id
, l_eqp_ins
, l_per_res_id
, l_mac_res_id
, 3
, SYSDATE
, SYSDATE
, l_emp_id
, SYSDATE
, l_emp_id
, l_wms_task_type
, task_rec.task_priority
, l_operation_plan_id
, l_move_order_line_id
);
mydebug('pick_by_label: After Insert into WMSDT');
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_pick_slip_id;
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM
mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_pick_slip_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT 1
INTO l_mmtt_rowcnt
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_pick_slip_id
AND parent_line_id is NULL);
SELECT 1
INTO l_wdt_rowcnt
FROM DUAL
WHERE EXISTS(SELECT 1
FROM wms_dispatched_tasks t
WHERE t.transaction_temp_id = l_pick_slip_id);
SELECT bremp.resource_id role_id
, t.wms_task_type
, t.standard_operation_id
, t.operation_plan_id
, t.move_order_line_id
, t.inventory_item_id --Bug#6009436
INTO l_per_res_id
, l_wms_task_type
, l_std_op_id
, l_operation_plan_id
, l_move_order_line_id
, l_item_id --Bug6009436
FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
WHERE t.transaction_temp_id = l_pick_slip_id
AND t.standard_operation_id = bsor.standard_operation_id
AND bsor.resource_id = bremp.resource_id
AND bremp.resource_type = 2
AND t.organization_id = l_org_id --Bug # 3704626
AND ROWNUM < 2;
SELECT resource_id
INTO l_mac_res_id
FROM bom_resource_equipments
WHERE inventory_item_id = l_eqp_id
AND ROWNUM < 2;
select breqp.resource_id equip_type_id
INTO l_mac_res_id
from mtl_material_transactions_temp t,
bom_std_op_resources bsor,
bom_resources breqp
where t.transaction_temp_id = task_rec.task_id
and t.standard_operation_id = bsor.standard_operation_id
and bsor.resource_id = breqp.resource_id
and breqp.resource_type = 1
and rownum<2;
SELECT wms_dispatched_tasks_s.NEXTVAL
INTO l_next_task_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
INTO l_txn_hdr_id
FROM DUAL;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_hdr_id
WHERE transaction_temp_id = l_pick_slip_id;
SELECT msi.serial_number_control_code
, msi.lot_control_code
INTO l_serial_ctrl_code
, l_lot_ctrl_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = l_item_id
AND msi.organization_id =p_sign_on_org_id ;
UPDATE MTL_SERIAL_NUMBERS msn
SET GROUP_MARK_ID=l_txn_hdr_id
WHERE msn.current_organization_id=p_sign_on_org_id
AND msn.inventory_item_id= l_item_id
AND msn.SERIAL_NUMBER BETWEEN l_fm_serial_number AND
l_to_serial_number;
UPDATE mtl_serial_numbers_temp
SET group_header_id= l_txn_hdr_id
WHERE transaction_temp_id in ( SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id= l_pick_slip_id );
UPDATE MTL_SERIAL_NUMBERS msn
SET GROUP_MARK_ID=l_txn_hdr_id
WHERE msn.current_organization_id=p_sign_on_org_id
AND msn.inventory_item_id= l_item_id
AND msn.SERIAL_NUMBER BETWEEN l_fm_serial_number AND
l_to_serial_number;
UPDATE mtl_serial_numbers_temp
SET group_header_id= l_txn_hdr_id
WHERE transaction_temp_id=l_pick_slip_id ;
mydebug('manual_pick: Updated MSNT');
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, operation_plan_id
, move_order_line_id
)
VALUES (
l_next_task_id
, l_pick_slip_id
, l_org_id
, NVL(l_std_op_id, 2)
, l_user_id
, SYSDATE
, SYSDATE
, l_eqp_id
, l_eqp_ins
, l_per_res_id
, l_mac_res_id
, 3
, SYSDATE
, SYSDATE
, l_emp_id
, SYSDATE
, l_emp_id
, l_wms_task_type
, l_operation_plan_id
, l_move_order_line_id
);
mydebug('manual_pick: After Insert into WMSDT');
SELECT COUNT(*)
INTO l_mmtt_rowcnt
FROM mtl_material_transactions_temp m
WHERE m.cartonization_id IS NOT NULL
AND m.cartonization_id = l_lpn_id
AND parent_line_id IS NULL;
SELECT COUNT(*)
INTO l_wdt_rowcnt
FROM mtl_material_transactions_temp m, wms_dispatched_tasks t
WHERE m.cartonization_id = l_lpn_id
AND t.transaction_temp_id = m.transaction_temp_id
AND t.status = 4;
SELECT lpn_context
, organization_id
INTO lpn_cont
, l_org_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_lpn;
SELECT reservable_type
INTO l_reservable_type
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
PROCEDURE delete_allocation
(
p_temp_id IN NUMBER,
p_lot_control_code IN NUMBER,
p_serial_control_code IN NUMBER,
p_serial_allocate_flag IN VARCHAR2,
p_item_id IN NUMBER,
p_org_id IN NUMBER
)
IS
l_fm_serial_number VARCHAR2(30);
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id;
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM
mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE inventory_item_id = p_item_id
AND current_organization_id = p_org_id
AND serial_number BETWEEN l_fm_serial_number AND
l_to_serial_number;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id);
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id;
END delete_allocation;
SELECT mmtt.transaction_header_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id
, mmtt.organization_id
, mmtt.revision
, mmtt.lot_number
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.move_order_line_id
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id)
UNION ALL
SELECT mmtt.transaction_header_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id
, mmtt.organization_id
, mmtt.revision
, mmtt.lot_number
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.move_order_line_id
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = p_temp_id;
SELECT mtrh.move_order_type
, mtrl.txn_source_id
, mtrl.txn_source_line_id
, mtrl.reference_id
, mtrl.quantity
, mtrl.uom_code
, nvl(mtrl.quantity_delivered,0)
, mtrl.line_status --bug3139307
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_mo_line_id
AND mtrh.header_id = mtrl.header_id;
SELECT COUNT(*)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_mo_line_id
AND mmtt.transaction_temp_id <> l_txn_temp_id
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id);
inv_trx_util_pub.delete_transaction(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => l_txn_temp_id
, p_update_parent => FALSE --Added bug 3765153
);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_txn_qty
WHERE line_id = l_mo_line_id;
DELETE FROM wms_dispatched_tasks WHERE transaction_temp_id = p_temp_id;
/*Need to delete the MMTT as the suggest_alternate-location
procedure in WMSTRSAB.pls would set the MMTT transaction qty. primary
transaction qty to zero before calling pick release. When Pick rellease
backorders the mo line it does not clean up the taks. This
has to be done here.*/
INV_TRX_UTIL_PUB.delete_transaction(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => l_txn_temp_id
, p_update_parent => FALSE --Added bug 3765153
);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_delivered
, line_status = 5
WHERE line_id = l_mo_line_id;
inv_trx_util_pub.delete_transaction(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => l_txn_temp_id
, p_update_parent => FALSE --Added bug3765153
);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_txn_qty,
line_status = 5
WHERE line_id = l_mo_line_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_delivered
WHERE line_id = l_mo_line_id;
inv_trx_util_pub.delete_transaction(
x_return_status => x_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_transaction_temp_id => l_txn_temp_id
);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_delivered
WHERE line_id = l_mo_line_id;
SELECT negative_inv_receipt_code
INTO l_negative_rcpt_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT COUNT(*)
INTO x_lot_num_lov_count
FROM mtl_lot_numbers_all_v mln, mtl_transaction_lots_temp mtlt
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE(p_lot_number || '%')
AND mtlt.lot_number = mln.lot_number
AND mtlt.transaction_temp_id = p_txn_temp_id
AND inv_material_status_grp.is_status_applicable(
p_wms_installed
, NULL
, p_transaction_type_id
, NULL
, NULL
, p_organization_id
, p_item_id
, NULL
, NULL
, mln.lot_number
, NULL
, 'O'
) = 'Y';
SELECT COUNT(*)
INTO x_lot_num_lov_count
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers_all_v mln, mtl_transaction_lots_temp mtlt, wms_lpn_contents wlc
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE(p_lot_number || '%')
AND moq.lot_number = mln.lot_number
AND moq.inventory_item_id = mln.inventory_item_id
AND moq.organization_id = mln.organization_id
AND mtlt.lot_number = mln.lot_number
AND mtlt.transaction_temp_id = p_txn_temp_id
AND moq.containerized_flag = 1
AND wlc.parent_lpn_id = p_lpn_id
AND wlc.lot_number = mln.lot_number
AND wlc.inventory_item_id = p_item_id
AND wlc.organization_id = p_organization_id
AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1)
AND inv_material_status_grp.is_status_applicable(
p_wms_installed
, NULL
, p_transaction_type_id
, NULL
, NULL
, p_organization_id
, p_item_id
, NULL
, NULL
, mln.lot_number
, NULL
, 'O'
) = 'Y';
SELECT COUNT(*)
INTO x_lot_num_lov_count
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers_all_v mln, mtl_transaction_lots_temp mtlt
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE(p_lot_number || '%')
AND moq.lot_number = mln.lot_number
AND moq.inventory_item_id = mln.inventory_item_id
AND moq.organization_id = mln.organization_id
AND mtlt.lot_number = mln.lot_number
AND mtlt.transaction_temp_id = p_txn_temp_id
AND moq.containerized_flag = 2
AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1)
AND inv_material_status_grp.is_status_applicable(
p_wms_installed
, NULL
, p_transaction_type_id
, NULL
, NULL
, p_organization_id
, p_item_id
, NULL
, NULL
, mln.lot_number
, NULL
, 'O'
) = 'Y';
SELECT COUNT(*)
INTO x_lot_num_lov_count
FROM mtl_onhand_quantities_detail moq, mtl_lot_numbers_all_v mln, mtl_transaction_lots_temp mtlt, wms_lpn_contents wlc
WHERE mln.organization_id = p_organization_id
AND mln.inventory_item_id = p_item_id
AND mln.lot_number LIKE(p_lot_number || '%')
AND moq.lot_number = mln.lot_number
AND moq.inventory_item_id = mln.inventory_item_id
AND moq.organization_id = mln.organization_id
AND mtlt.lot_number = mln.lot_number
AND mtlt.transaction_temp_id = p_txn_temp_id
AND moq.containerized_flag = 1
AND wlc.parent_lpn_id = p_lpn_id
AND wlc.lot_number = mln.lot_number
AND wlc.inventory_item_id = p_item_id
AND wlc.organization_id = p_organization_id
AND moq.subinventory_code = NVL(p_subinventory_code, moq.subinventory_code)
AND NVL(moq.locator_id, -1) = NVL(NVL(p_locator_id, moq.locator_id), -1)
AND inv_material_status_grp.is_status_applicable(
p_wms_installed
, NULL
, p_transaction_type_id
, NULL
, NULL
, p_organization_id
, p_item_id
, NULL
, NULL
, mln.lot_number
, NULL
, 'O'
) = 'Y';
SELECT mmtt.transaction_type_id
, mmtt.organization_id
, mmtt.inventory_item_id
INTO l_transaction_type_id
, l_org_id
, l_item_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT wms_task_type
INTO l_wms_task_type
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT mtrh.move_order_type
INTO l_mmtt_mo_type
FROM mtl_txn_request_headers mtrh, -- mo header for the new task
mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
WHERE mtrh.header_id = mtrl.header_id
AND mtrl.line_id = mmtt.move_order_line_id
AND mmtt.transaction_temp_id = p_temp_id;
SELECT msi.lpn_controlled_flag
, mmtt.transfer_lpn_id
, mmtt.transfer_subinventory
INTO l_lpn_controlled_flag
, l_xfr_lpn_id
, l_orig_xfr_sub
FROM mtl_material_transactions_temp mmtt, mtl_secondary_inventories msi
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.organization_id = msi.organization_id
AND msi.secondary_inventory_name = p_confirmed_drop_sub;
SELECT 1
INTO l_count
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id <> p_temp_id
AND mmtt.transfer_lpn_id = l_xfr_lpn_id
AND mmtt.transfer_subinventory <> p_confirmed_drop_sub
AND mmtt.transfer_subinventory <> l_orig_xfr_sub);
SELECT mmtt.transaction_temp_id
, mmtt.inventory_item_id
, mmtt.subinventory_code
, mmtt.locator_id
, NVL(mmtt.content_lpn_id, mmtt.lpn_id)
, mmtt.transfer_lpn_id
, mmtt.transaction_uom
, mmtt.transaction_quantity
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_txn_hdr_id
AND mmtt.organization_id = p_org_id
AND mmtt.transaction_quantity > 0
AND mmtt.parent_line_id is NULL --Added bug3765153 to ensure only parent line are picked
ORDER BY mmtt.transaction_quantity DESC;
SELECT mmtt.transaction_temp_id
, mmtt.transaction_uom
, mmtt.transaction_quantity
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = p_temp_id
AND mmtt.organization_id = p_org_id
ORDER BY mmtt.transaction_quantity DESC;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.parent_line_id = p_parent_line_id; --Added bug3765153 to determine and back order unpicked lines
SELECT mtl_material_transactions_s.NEXTVAL
INTO x_new_txn_hdr_id
FROM DUAL;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_header_id = x_new_txn_hdr_id
, mmtt.transfer_lpn_id = l_transfer_lpn_id
, mmtt.lpn_id = l_lpn_id
, mmtt.parent_line_id = l_parent_txn_temp_id --Modified from NULL bug3765153
, mmtt.subinventory_code = l_parent_sub_code
, mmtt.locator_id = l_parent_loc_id
, mmtt.transaction_uom = l_parent_uom
, mmtt.last_update_date = SYSDATE
, mmtt.last_updated_by = p_user_id
WHERE mmtt.transaction_temp_id = l_child_txn_temp_id;
select mtl_material_transactions_s.NEXTVAL
into l_new_temp_id
from dual; --Added bug3765153
INSERT INTO mtl_material_transactions_temp
(
transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, cartonization_id
, standard_operation_id
, wms_task_type
, task_priority
, container_item_id
, operation_plan_id
, parent_line_id
)
(SELECT transaction_header_id
, l_new_temp_id --Changed from mtl_material_transactions_s.NEXTVAL bug3765153
, source_code
, source_line_id
, transaction_mode
, lock_flag
, SYSDATE
, last_updated_by
, SYSDATE
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, l_child_pri_qty - l_parent_pri_qty
, l_child_txn_qty - l_parent_txn_qty
, l_parent_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, cartonization_id
, standard_operation_id
, wms_task_type
, task_priority
, container_item_id
, operation_plan_id
, parent_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_child_txn_temp_id);
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_header_id = x_new_txn_hdr_id
, mmtt.transaction_quantity = l_parent_txn_qty
, mmtt.primary_quantity = l_parent_pri_qty
, mmtt.parent_line_id = NULL --l_parent_txn_temp_id --Modified from NULL bug3765153
, mmtt.transfer_lpn_id = l_transfer_lpn_id
, mmtt.lpn_id = l_lpn_id
, mmtt.subinventory_code = l_parent_sub_code
, mmtt.locator_id = l_parent_loc_id
, mmtt.transaction_uom = l_parent_uom
, mmtt.last_update_date = SYSDATE
, mmtt.last_updated_by = p_user_id
WHERE mmtt.transaction_temp_id = l_child_txn_temp_id;
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, loaded_time
, operation_plan_id
, move_order_line_id
)
(SELECT wms_dispatched_tasks_s.NEXTVAL
, mmtt.transaction_temp_id
, mmtt.organization_id
, wdt.user_task_type
, wdt.person_id
, wdt.effective_start_date
, wdt.effective_end_date
, wdt.equipment_id
, wdt.equipment_instance
, wdt.person_resource_id
, wdt.machine_resource_id
, 4
, wdt.dispatched_time
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, wdt.task_type
, SYSDATE
, mmtt.operation_plan_id
, mmtt.move_order_line_id
FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
WHERE wdt.transaction_temp_id = p_temp_id
AND mmtt.transaction_header_id = x_new_txn_hdr_id);
DELETE FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id IN(SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_txn_hdr_id
AND mmtt.organization_id = p_org_id);
DELETE FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = p_txn_hdr_id
AND mmtt.organization_id = p_org_id;
update mtl_material_transactions_temp
set parent_line_id = null
where transaction_header_id = x_new_txn_hdr_id;
SELECT 1
INTO l_wf
FROM mtl_transaction_reasons
WHERE reason_id = p_rsn_id
AND workflow_name IS NOT NULL
AND workflow_name <> ' '
AND workflow_process IS NOT NULL
AND workflow_process <> ' ';
select mmtt.subinventory_code,
mmtt.locator_id,
mmtt.revision,
mmtt.lpn_id,
null,
null
INTO l_sub,l_loc,l_rev,l_lpn,l_ser,l_lot
from mtl_material_Transactions_temp mmtt
where mmtt.inventory_item_id = p_inventory_item_id
and mmtt.organization_id = p_organization_id
and mmtt.transfer_lpn_id = p_transfer_lpn_id
and mmtt.content_lpn_id is null
and decode(p_revision_control,2,mmtt.revision,1,'~~') = nvl(p_revision,'~~')
and rownum<2;
select mmtt.subinventory_code,
mmtt.locator_id,
mmtt.revision,
mmtt.lpn_id,
null,
mtlt.lot_number
INTO l_sub,l_loc,l_rev,l_lpn,l_ser,l_lot
from mtl_material_Transactions_temp mmtt,
mtl_transaction_lots_temp mtlt
where mmtt.inventory_item_id = p_inventory_item_id
and mmtt.organization_id = p_organization_id
and mmtt.transfer_lpn_id = p_transfer_lpn_id
and mmtt.content_lpn_id is null
and decode(p_revision_control,2,mmtt.revision,1,'~~') = nvl(p_revision,'~~')
and mmtt.transaction_temp_id = mtlt.transaction_temp_id
and mtlt.lot_number = p_lot_number
and rownum<2;
inv_cost_group_update.proc_get_costgroup(
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_subinventory_code => p_subinventory_code,
p_locator_id => p_locator_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_serial_number => null,
p_containerized_flag => null,
p_lpn_id => p_lpn_id,
p_transaction_action_id => null,
x_cost_group_id => l_cur_cost_group_id,
x_return_status => x_return_status);
inv_cost_group_update.proc_get_costgroup(
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_subinventory_code => l_sub,
p_locator_id => l_loc,
p_revision => l_rev,
p_lot_number => l_lot,
p_serial_number => l_ser,
p_containerized_flag => null,
p_lpn_id => l_lpn,
p_transaction_action_id => null,
x_cost_group_id => l_exist_cost_group_id,
x_return_status => x_return_status);
SELECT lpn_id,
lpn_context,
subinventory_code,
locator_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_drop_lpn
AND organization_id = p_organization_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_material_transactions_temp temp
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = temp.move_order_line_id
AND wdd.organization_id = temp.organization_id
AND temp.transfer_lpn_id = p_pick_lpn_id
AND temp.organization_id = p_organization_id ;
SELECT wda.delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wms_license_plate_numbers lpn
WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.lpn_id = lpn.lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND lpn.outermost_lpn_id = l_lpn_id
AND wdd.organization_id = p_organization_id ;
/* UPDATE wms_license_plate_numbers
SET lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
WHERE lpn_id = drop_lpn_rec.lpn_id;*/
SELECT delivery_detail_id
INTO l_line_rows(1)
FROM wsh_delivery_details
WHERE lpn_id = drop_lpn_rec.lpn_id
AND rownum = 1 ;
SELECT wdd.delivery_detail_id
INTO l_line_rows(2)
FROM wsh_delivery_details wdd, Mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = wdd.move_order_line_id
AND wdd.organization_id = mmtt.organization_id
AND mmtt.organization_id= p_organization_id
AND mmtt.transfer_lpn_id= p_pick_lpn_id
AND rownum = 1 ;
SELECT wda.delivery_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_material_transactions_temp temp
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = temp.move_order_line_id
AND wdd.organization_id = temp.organization_id
AND temp.transfer_lpn_id = p_pick_lpn_id
AND temp.organization_id = p_organization_id;
SELECT /*+ index(wda WSH_DELIVERY_ASSIGNMENTS_N1) ORDERED USE_NL (WDA WDD WLPN) */ wlpn.outermost_lpn_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
wms_license_plate_numbers wlpn
WHERE wda.delivery_id = l_delivery_id_c
AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = p_organization_id
AND wdd.lpn_id = wlpn.lpn_id
AND wlpn.subinventory_code = l_drop_sub_c
AND wlpn.locator_id = l_drop_loc_c
AND wlpn.lpn_context = 11
ORDER BY wda.CREATION_DATE DESC ;
Select transfer_subinventory, transfer_to_location into l_drop_sub,
l_drop_loc
from mtl_material_transactions_temp
where transfer_lpn_id = p_pick_lpn_id
AND organization_id = p_organization_id;
SELECT license_plate_number INTO x_lpn_number FROM
wms_license_plate_numbers WHERE lpn_id = l_lpn_id;