[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*Procedure to log error message to the x_updated_tasks rec*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE log_error(p_transaction_number IN NUMBER DEFAULT NULL ,
p_task_table IN WMS_TASK_MGMT_PUB.task_tab_type ,
p_error_msg IN VARCHAR2 ,
x_updated_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type) IS
BEGIN
IF p_transaction_number is NOT NULL THEN
x_updated_tasks(1).RESULT := 'E';
x_updated_tasks(1).ERROR := p_error_msg;
x_updated_tasks(i).RESULT := 'E';
x_updated_tasks(i).ERROR := p_error_msg;
SELECT lpn_context,
organization_id,
inventory_item_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id=c_lpn_id;
SELECT wdd.delivery_detail_id,
wdd.move_order_line_id
FROM wsh_delivery_details wdd,
mtl_material_transactions_temp mmtt
WHERE wdd.move_order_line_id=mmtt.move_order_line_id
AND wdd.organization_id =mmtt.organization_id
AND (mmtt.transfer_lpn_id =c_lpn_id
OR mmtt.cartonization_id = c_lpn_id);
SELECT wdd.delivery_detail_id,
wdd.move_order_line_id
FROM wsh_delivery_details wdd,
mtl_material_transactions_temp mmtt
WHERE wdd.move_order_line_id=mmtt.move_order_line_id
AND wdd.organization_id =mmtt.organization_id
AND mmtt.cartonization_id = c_lpn_id
AND ROWNUM = 1;*/
SELECT delivery_id
FROM wsh_delivery_assignments
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT '1'
FROM mtl_material_transactions_temp
WHERE parent_line_id = p_transaction_number
AND rownum = 1;
SELECT wdd.delivery_detail_id,
wdd.move_order_line_id
INTO l_temp_del_detail_id,
l_move_order_line_id
FROM wsh_delivery_details wdd,
mtl_material_transactions_temp mmtt
WHERE wdd.move_order_line_id = mmtt.move_order_line_id
AND mmtt.transaction_temp_id = p_task_table(i).transaction_number;
SELECT delivery_id
INTO l_temp_del_id
FROM wsh_delivery_assignments
WHERE delivery_detail_id = l_del_det_id_tab(i);
/*This procedure checks if the various attributes that are passed can be updated
on the provided task*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE modify_single_task( p_task_rec IN WMS_TASK_MGMT_PUB.task_output_rectype,
P_new_task_status IN NUMBER DEFAULT NULL,
P_new_task_priority IN NUMBER DEFAULT NULL,
P_new_task_type IN VARCHAR2 DEFAULT NULL,
P_new_carton_lpn_id IN NUMBER DEFAULT NULL,
p_new_operation_plan_id IN NUMBER DEFAULT NULL,
p_output_task_rec OUT NOCOPY WMS_TASK_MGMT_PUB.task_output_rectype,
p_op_plan_rec IN WMS_TASK_MGMT_PUB.op_plan_rec)
IS
l_lpn VARCHAR2(30);
SELECT standard_operation_id
FROM BOM_STANDARD_OPERATIONS_V
WHERE wms_task_type = p_std_task_type
AND operation_code = p_operation_code
AND organization_id = p_org_id ;
SELECT operation_plan_detail_id
, operation_sequence
, is_in_inventory
, operation_type
FROM WMS_OP_PLAN_DETAILS
WHERE operation_plan_id = v_operation_plan_id
AND operation_sequence = (SELECT MIN(operation_sequence)
FROM WMS_OP_PLAN_DETAILS
WHERE operation_plan_id=v_operation_plan_id);
SELECT OPERATION_PLAN_ID,
SYSTEM_TASK_TYPE,
ACTIVITY_TYPE_ID,
PLAN_TYPE_ID
FROM WMS_OP_PLANS_B
WHERE operation_plan_id=v_operation_plan_id;
SELECT STATUS FROM wms_op_plan_instances
WHERE ((
source_task_id in
(SELECT parent_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id =v_source_task_id
)) OR (source_task_id =v_source_task_id )); ---contains parent only
SELECT COUNT(*)
INTO l_count
FROM WMS_DISPATCHED_TASKS wdt1
WHERE wdt1.status = 9
AND wdt1.TRANSACTION_TEMP_ID =p_task_rec.TRANSACTION_NUMBER
AND EXISTS
(SELECT 1
FROM MTL_MOBILE_LOGIN_HIST MMLH,
WMS_DISPATCHED_TASKS wdt
WHERE wdt1.TRANSACTION_TEMP_ID = wdt.TRANSACTION_TEMP_ID
AND MMLH.USER_ID = wdt1.LAST_UPDATED_BY
AND MMLH.LOGOFF_DATE IS NULL
AND MMLH.EVENT_MESSAGE IS NULL
);
l_msg := 'Active task can be updated if tasks assigned to user is logged off the system';
SELECT COUNT(*)
INTO l_count
FROM mtl_material_transactions_temp mmtt,
WMS_DISPATCHED_TASKS wdt
WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
AND wdt.transaction_temp_id =p_task_rec.TRANSACTION_NUMBER
AND wdt.status = 3
AND EXISTS
(SELECT 1
FROM WMS_DISPATCHED_TASKS wdt2
WHERE wdt2.person_id = p_task_rec.PERSON_ID
AND wdt2.status = 9
AND wdt2.task_method IS NOT NULL
AND wdt2.transaction_temp_id IN
(SELECT transaction_temp_id
FROM mtl_material_transactions_temp mmtt1
WHERE
DECODE(wdt.TASK_METHOD, 'CARTON', mmtt1.cartonization_id, 'PICK_SLIP', mmtt1.pick_slip_number, 'DISCRETE', mmtt1.pick_slip_number, mmtt1.transaction_source_id) =
DECODE(wdt.TASK_METHOD, 'CARTON', mmtt.cartonization_id, 'PICK_SLIP', mmtt.pick_slip_number, 'DISCRETE', mmtt.pick_slip_number, mmtt.transaction_source_id)
)
);
l_msg := 'Dispatcher task can be updated only if any of the task in group is not in Active status';
l_msg := 'Inbound task priority cannot be updated';
SELECT license_plate_number
INTO l_lpn
FROM wms_license_plate_numbers
WHERE lpn_id =P_new_carton_lpn_id;
SELECT PLAN_TYPE_ID
INTO l_operation_plan_type_id
FROM wms_op_plans_b
WHERE OPERATION_PLAN_ID=p_task_rec.OPERATION_PLAN_ID;
UPDATE wms_op_plan_instances
SET OPERATION_PLAN_ID=l_op_plan_b_rec.OPERATION_PLAN_ID,
SYSTEM_TASK_TYPE =l_op_plan_b_rec.SYSTEM_TASK_TYPE ,
ACTIVITY_TYPE_ID =l_op_plan_b_rec.ACTIVITY_TYPE_ID ,
PLAN_TYPE_ID =l_op_plan_b_rec.PLAN_TYPE_ID
WHERE ((source_task_id IN
(SELECT parent_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=p_task_rec.transaction_number
))
OR (source_task_id = p_task_rec.transaction_number)); --update parent records only
UPDATE wms_op_operation_instances
SET OPERATION_TYPE =l_operation_plan_detail_rec.OPERATION_TYPE ,
OPERATION_PLAN_DETAIL_ID=l_operation_plan_detail_rec.OPERATION_PLAN_DETAIL_ID ,
OPERATION_SEQUENCE =l_operation_plan_detail_rec.OPERATION_SEQUENCE ,
--ACTIVITY_TYPE=l_operation_plan_detail_rec.ACTIVITY_TYPE ,
IS_IN_INVENTORY =l_operation_plan_detail_rec.IS_IN_INVENTORY
WHERE ((source_task_id IN
(SELECT TRANSACTION_TEMP_ID
FROM mtl_material_transactions_temp
WHERE parent_line_id=p_task_rec.transaction_number
))
OR ( source_task_id =p_task_rec.transaction_number));--update all the children records only
SELECT parent_line_id
INTO l_src_parent_id
FROM mtl_material_transactions_temp
WHERE TRANSACTION_TEMP_ID=p_task_rec.transaction_number;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.OPERATION_PLAN_ID=p_new_operation_plan_id
WHERE mmtt.PARENT_LINE_ID = l_src_tran_id;--update child record
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.OPERATION_PLAN_ID =p_new_operation_plan_id
WHERE mmtt.TRANSACTION_TEMP_ID = l_src_tran_id;--update parent record
ELSE --update the mmtt with new plan
/*UPDATE mtl_material_transactions_temp
SET operation_plan_id = p_new_operation_plan_id
WHERE transaction_temp_id = p_task_rec.transaction_number;*/
SELECT 1 ,
mmtt.wms_task_type ,
parent_line_id
INTO l_task_exists ,
l_task_type ,
l_parent_line_id
FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt
WHERE mmtt.transaction_temp_id=p_transaction_number;
SELECT count(1)
INTO l_task_exists
FROM Mtl_Cycle_Count_Entries mcce
WHERE mcce.cycle_count_entry_id = p_transaction_number
AND mcce.entry_status_code IN( 1 , 3) ;
SELECT wdt.status
INTO l_wdt_status
FROM Wms_Dispatched_Tasks wdt
WHERE wdt.transaction_temp_id = p_transaction_number ;
SELECT MAX(wdt.status )
INTO l_wdt_status
FROM Wms_Dispatched_Tasks wdt
WHERE wdt.transaction_temp_id in
(SELECT transaction_temp_id --To check the child tasks
FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt
WHERE mmtt.parent_line_id = p_transaction_number
UNION SELECT mmtt2.transaction_temp_id --To check the siblings
FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt1 ,
MTL_MATERIAL_TRANSACTIONS_TEMP mmtt2
WHERE mmtt1.transaction_temp_id = p_transaction_number
AND mmtt2.parent_line_id = mmtt1.parent_line_id
UNION SELECT mmtt2.transaction_temp_id ---To check the parent task.
FROM MTL_MATERIAL_TRANSACTIONS_TEMP mmtt1 ,
MTL_MATERIAL_TRANSACTIONS_TEMP mmtt2
WHERE mmtt1.transaction_temp_id = p_transaction_number
AND mmtt2.transaction_temp_id = mmtt1.parent_line_id
) ;
SELECT lpn_context,
organization_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id=c_lpn_id;
SELECT wdd.delivery_detail_id,
wdd.move_order_line_id
FROM wsh_delivery_details wdd,
mtl_material_transactions_temp mmtt
WHERE wdd.move_order_line_id=mmtt.move_order_line_id
AND wdd.organization_id =mmtt.organization_id
AND mmtt.transfer_lpn_id =c_lpn_id
AND ROWNUM < 2;
SELECT wdd.delivery_detail_id,
wdd.move_order_line_id
FROM wsh_delivery_details wdd,
mtl_material_transactions_temp mmtt
WHERE wdd.move_order_line_id=mmtt.move_order_line_id
AND wdd.organization_id =mmtt.organization_id
AND mmtt.cartonization_id = c_lpn_id
AND ROWNUM = 1;
SELECT delivery_id
FROM wsh_delivery_assignments
WHERE delivery_detail_id = p_delivery_detail_id;
SELECT '1'
FROM mtl_material_transactions_temp
WHERE parent_line_id = p_transaction_number
AND rownum = 1;
SELECT wdd.delivery_detail_id,
wdd.move_order_line_id
INTO l_temp_del_detail_id,
l_move_order_line_id
FROM wsh_delivery_details wdd,
mtl_material_transactions_temp mmtt
WHERE wdd.move_order_line_id = mmtt.move_order_line_id
AND mmtt.transaction_temp_id = p_task_table(i).transaction_number;
SELECT delivery_id
INTO l_temp_del_id
FROM wsh_delivery_assignments
WHERE delivery_detail_id = l_del_det_id_tab(i);
SELECT mtrl.carton_grouping_id
INTO l_temp_carton_grp_id
FROM mtl_txn_request_lines mtrl
WHERE line_id = l_move_order_line_id_tab(i);
SELECT transaction_uom ,
inventory_item_id ,
primary_quantity ,
transaction_quantity ,
item_primary_uom_code ,
transaction_uom ,
organization_id
INTO l_mmtt_transaction_uom ,
l_mmtt_inventory_item_id ,
l_mmtt_primary_quantity ,
l_mmtt_transaction_quantity ,
l_mmtt_item_primary_uom_code ,
l_mmtt_transaction_uom ,
l_mmtt_organization_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT lot_control_code ,
serial_number_control_code
INTO l_lot_control_code ,
l_serial_control_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_mmtt_inventory_item_id
AND organization_id = l_mmtt_organization_id;
SELECT sum(transaction_quantity)
INTO l_mtlt_transaction_qty
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT sum(1)
INTO l_msnt_transaction_qty
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
);
SELECT sum(transaction_quantity)
INTO l_mtlt_transaction_qty
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT sum(1)
INTO l_msnt_transaction_qty
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT transaction_temp_id ,
wms_task_status ,
allocated_lpn_id ,
transaction_uom ,
inventory_item_id ,
primary_quantity ,
transaction_quantity ,
move_order_line_id ,
move_order_header_id ,
item_primary_uom_code ,
transaction_uom ,
organization_id ,
wms_task_type ,
parent_line_id
INTO l_mmtt_id ,
l_mmtt_task_status ,
l_mmtt_allocated_lpn_id ,
l_mmtt_transaction_uom ,
l_mmtt_inventory_item_id ,
l_mmtt_primary_quantity ,
l_mmtt_transaction_quantity ,
l_orig_mol_id ,
l_mmtt_mol_header_id ,
l_mmtt_item_primary_uom_code ,
l_mmtt_transaction_uom ,
l_mmtt_organization_id ,
l_task_type ,
l_parent_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id FOR UPDATE NOWAIT;
SELECT count(1)
INTO l_task_exists
FROM mtl_cycle_count_entries mcce
WHERE mcce.cycle_count_entry_id = p_transaction_temp_id
AND mcce.entry_status_code IN( 1 , 3) ;
INSERT
INTO mtl_transaction_lots_temp
(
TRANSACTION_TEMP_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
ERROR_CODE ,
SERIAL_TRANSACTION_TEMP_ID ,
GROUP_HEADER_ID ,
PUT_AWAY_RULE_ID ,
PICK_RULE_ID ,
DESCRIPTION ,
VENDOR_NAME ,
SUPPLIER_LOT_NUMBER ,
ORIGINATION_DATE ,
DATE_CODE ,
GRADE_CODE ,
CHANGE_DATE ,
MATURITY_DATE ,
STATUS_ID ,
RETEST_DATE ,
AGE ,
ITEM_SIZE ,
COLOR ,
VOLUME ,
VOLUME_UOM ,
PLACE_OF_ORIGIN ,
BEST_BY_DATE ,
LENGTH ,
LENGTH_UOM ,
RECYCLED_CONTENT ,
THICKNESS ,
THICKNESS_UOM ,
WIDTH ,
WIDTH_UOM ,
CURL_WRINKLE_FOLD ,
LOT_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 ,
VENDOR_ID ,
TERRITORY_CODE ,
SUBLOT_NUM ,
SECONDARY_QUANTITY ,
SECONDARY_UNIT_OF_MEASURE ,
QC_GRADE ,
REASON_CODE ,
PRODUCT_CODE ,
PRODUCT_TRANSACTION_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
)
SELECT p_new_transaction_temp_id --TRANSACTION_TEMP_ID
,
sysdate --LAST_UPDATE_DATE
,
FND_GLOBAL.USER_ID ,
sysdate --CREATION_DATE
,
FND_GLOBAL.USER_ID ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
p_transaction_qty_to_split --TRANSACTION_QUANTITY
,
p_primary_qty_to_split --PRIMARY_QUANTITY
,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
ERROR_CODE ,
SERIAL_TRANSACTION_TEMP_ID ,
GROUP_HEADER_ID ,
PUT_AWAY_RULE_ID ,
PICK_RULE_ID ,
DESCRIPTION ,
VENDOR_NAME ,
SUPPLIER_LOT_NUMBER ,
ORIGINATION_DATE ,
DATE_CODE ,
GRADE_CODE ,
CHANGE_DATE ,
MATURITY_DATE ,
STATUS_ID ,
RETEST_DATE ,
AGE ,
ITEM_SIZE ,
COLOR ,
VOLUME ,
VOLUME_UOM ,
PLACE_OF_ORIGIN ,
BEST_BY_DATE ,
LENGTH ,
LENGTH_UOM ,
RECYCLED_CONTENT ,
THICKNESS ,
THICKNESS_UOM ,
WIDTH ,
WIDTH_UOM ,
CURL_WRINKLE_FOLD ,
LOT_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 ,
VENDOR_ID ,
TERRITORY_CODE ,
SUBLOT_NUM ,
SECONDARY_QUANTITY ,
SECONDARY_UNIT_OF_MEASURE ,
QC_GRADE ,
REASON_CODE ,
PRODUCT_CODE ,
PRODUCT_TRANSACTION_ID ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM mtl_transaction_lots_temp
WHERE rowid = p_row_id;
SELECT rowid,
msnt.*
FROM mtl_serial_numbers_temp msnt
WHERE transaction_temp_id = p_orig_transaction_temp_id
ORDER BY fm_serial_number;
UPDATE mtl_serial_numbers_temp
SET transaction_temp_id = p_new_transaction_temp_id ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE rowid = msnt.rowid;
UPDATE mtl_serial_numbers msn
SET msn.group_mark_id = p_new_transaction_temp_id ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE msn.inventory_item_id = p_inventory_item_id
AND serial_number = msnt.fm_serial_number
AND current_organization_id = p_organization_id;
SELECT rowid,
mtlt.*
FROM mtl_transaction_lots_temp mtlt
WHERE transaction_temp_id = p_orig_transaction_temp_id
ORDER BY lot_number;
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = p_new_transaction_temp_id ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE rowid = mtlt.rowid;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_serial_txn_temp_id
FROM dual;
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = l_new_serial_txn_temp_id ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE transaction_temp_id = p_new_transaction_temp_id
AND lot_number = mtlt.lot_number;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_txn_remaining_qty_mtlt ,
primary_quantity = l_prim_remaining_qty_mtlt ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE rowid = mtlt.rowid;
INSERT
INTO wms_dispatched_tasks
(
op_plan_instance_id ,
task_method ,
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 ,
loaded_time ,
drop_off_time ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
task_type ,
priority ,
task_group_id ,
device_id ,
device_invoked ,
device_request_id ,
suggested_dest_subinventory ,
suggested_dest_locator_id ,
operation_plan_id ,
move_order_line_id ,
transfer_lpn_id
)
SELECT op_plan_instance_id ,
task_method ,
p_new_task_id --task_id
,
p_new_transaction_temp_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 ,
loaded_time ,
drop_off_time ,
l_sysdate --last_update_date
,
FND_GLOBAL.USER_ID ,
l_sysdate --creation_date
,
FND_GLOBAL.USER_ID ,
last_update_login ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
task_type ,
priority ,
task_group_id ,
device_id ,
device_invoked ,
device_request_id ,
suggested_dest_subinventory ,
suggested_dest_locator_id ,
operation_plan_id ,
p_new_mol_id ,
transfer_lpn_id
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_orig_transaction_temp_id;
INSERT
INTO mtl_material_transactions_temp
(
currency_conversion_date ,
shipment_number ,
org_cost_group_id ,
cost_type_id ,
transaction_status ,
standard_operation_id ,
task_priority ,
wms_task_type ,
parent_line_id ,
source_lot_number ,
transfer_cost_group_id ,
lpn_id ,
transfer_lpn_id ,
wms_task_status ,
content_lpn_id ,
container_item_id ,
cartonization_id ,
pick_slip_date ,
rebuild_item_id ,
rebuild_serial_number ,
rebuild_activity_id ,
rebuild_job_name ,
organization_type ,
transfer_organization_type ,
owning_organization_id ,
owning_tp_type ,
xfr_owning_organization_id ,
transfer_owning_tp_type ,
planning_organization_id ,
planning_tp_type ,
xfr_planning_organization_id ,
transfer_planning_tp_type ,
secondary_uom_code ,
secondary_transaction_quantity ,
allocated_lpn_id ,
schedule_number ,
scheduled_flag ,
class_code ,
schedule_group ,
build_sequence ,
bom_revision ,
routing_revision ,
bom_revision_date ,
routing_revision_date ,
alternate_bom_designator ,
alternate_routing_designator ,
transaction_batch_id ,
transaction_batch_seq ,
operation_plan_id ,
intransit_account ,
fob_point ,
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 ,
ship_to_location ,
move_order_header_id ,
serial_allocated_flag ,
trx_flow_header_id ,
logical_trx_type_code ,
original_transaction_temp_id ,
vendor_lot_number ,
encumbrance_account ,
encumbrance_amount ,
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_qty ,
overcompletion_primary_qty ,
overcompletion_transaction_id ,
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 ,
move_order_line_id ,
task_group_id ,
pick_slip_number ,
reservation_id ,
common_bom_seq_id ,
common_routing_seq_id ,
ussgl_transaction_code
)
SELECT currency_conversion_date ,
shipment_number ,
org_cost_group_id ,
cost_type_id ,
transaction_status ,
standard_operation_id ,
task_priority ,
wms_task_type ,
parent_line_id ,
source_lot_number ,
transfer_cost_group_id ,
lpn_id ,
transfer_lpn_id ,
wms_task_status ,
content_lpn_id ,
container_item_id ,
cartonization_id ,
pick_slip_date ,
rebuild_item_id ,
rebuild_serial_number ,
rebuild_activity_id ,
rebuild_job_name ,
organization_type ,
transfer_organization_type ,
owning_organization_id ,
owning_tp_type ,
xfr_owning_organization_id ,
transfer_owning_tp_type ,
planning_organization_id ,
planning_tp_type ,
xfr_planning_organization_id ,
transfer_planning_tp_type ,
secondary_uom_code ,
secondary_transaction_quantity ,
allocated_lpn_id ,
schedule_number ,
scheduled_flag ,
class_code ,
schedule_group ,
build_sequence ,
bom_revision ,
routing_revision ,
bom_revision_date ,
routing_revision_date ,
alternate_bom_designator ,
alternate_routing_designator ,
transaction_batch_id ,
transaction_batch_seq ,
operation_plan_id ,
intransit_account ,
fob_point ,
p_new_transaction_header_id --TRANSACTION_HEADER_ID
,
p_new_transaction_temp_id --TRANSACTION_TEMP_ID
,
source_code ,
source_line_id ,
transaction_mode ,
lock_flag ,
l_sysdate --LAST_UPDATE_DATE
,
FND_GLOBAL.USER_ID ,
l_sysdate --CREATION_DATE
,
FND_GLOBAL.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 ,
p_transaction_qty_to_split --TRANSACTION_QUANTITY
,
p_primary_qty_to_split --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 ,
ship_to_location ,
move_order_header_id ,
serial_allocated_flag ,
trx_flow_header_id ,
logical_trx_type_code ,
original_transaction_temp_id ,
vendor_lot_number ,
encumbrance_account ,
encumbrance_amount ,
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_qty ,
overcompletion_primary_qty ,
overcompletion_transaction_id ,
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 ,
move_order_line_id ,
task_group_id ,
pick_slip_number ,
reservation_id ,
common_bom_seq_id ,
common_routing_seq_id ,
ussgl_transaction_code
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_orig_transaction_temp_id;
p_module => 'wms_delete_tasks',
p_level => 11);
SELECT attribute1 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute_category ,
created_by ,
creation_date ,
date_required ,
from_locator_id ,
from_subinventory_code ,
from_subinventory_id ,
header_id ,
inventory_item_id ,
last_updated_by ,
last_update_date ,
last_update_login ,
line_id ,
line_number ,
line_status ,
lot_number ,
organization_id ,
program_application_id ,
program_id ,
program_update_date ,
project_id ,
quantity ,
quantity_delivered ,
quantity_detailed ,
reason_id ,
REFERENCE ,
reference_id ,
reference_type_code ,
request_id ,
revision ,
serial_number_end ,
serial_number_start ,
status_date ,
task_id ,
to_account_id ,
to_locator_id ,
to_subinventory_code ,
to_subinventory_id ,
transaction_header_id ,
uom_code ,
transaction_type_id ,
transaction_source_type_id ,
txn_source_id ,
txn_source_line_id ,
txn_source_line_detail_id ,
to_organization_id ,
primary_quantity ,
pick_strategy_id ,
put_away_strategy_id ,
unit_number ,
ship_to_location_id ,
from_cost_group_id ,
to_cost_group_id ,
lpn_id ,
to_lpn_id ,
inspection_status ,
pick_methodology_id ,
container_item_id ,
carton_grouping_id ,
wms_process_flag ,
pick_slip_number ,
pick_slip_date ,
ship_set_id ,
ship_model_id ,
model_quantity ,
required_quantity
FROM mtl_txn_request_lines
WHERE line_id = p_line_id;
l_trolin_rec.last_updated_by := l_implicit_rec.last_updated_by;
l_trolin_rec.last_update_date := l_implicit_rec.last_update_date;
l_trolin_rec.last_update_login := l_implicit_rec.last_update_login;
l_trolin_rec.program_update_date := l_implicit_rec.program_update_date;
/*PROCEDURE delete_transaction()
This procedure checks for lot/serial control-deletes MTLT, MSNT,
Unmarks the serials, checks if a task record exists in wms_dispatched_tasks and deletes it.
*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE delete_transaction( x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_data OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
p_transaction_temp_id NUMBER ,
p_update_parent BOOLEAN ) IS
l_inventory_item_id NUMBER;
exc_not_deleted EXCEPTION;
SELECT msi.inventory_item_id ,
msi.lot_control_code ,
msi.serial_number_control_code,
mmtt.parent_line_id
FROM mtl_system_items msi,
mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_transaction_temp_id
AND msi.inventory_item_id = mmtt.inventory_item_id
AND msi.organization_id = mmtt.organization_id;
IF l_parent_line_id IS NOT NULL AND p_update_parent THEN
IF l_debug = 1 THEN
debug_print( 'Child Record... Updating the Parent: TxnTempID = ' || l_parent_line_id);
RAISE exc_not_deleted;
SELECT count (*)
INTO l_msn_count
FROM mtl_serial_numbers
WHERE group_mark_id IN
(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
);
UPDATE mtl_serial_numbers
SET group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
WHERE group_mark_id IN
(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
);
RAISE exc_not_deleted;
SELECT count(*)
INTO l_msnt_count
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
);
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id
);
RAISE exc_not_deleted;
SELECT count(*)
INTO l_msn_count
FROM mtl_serial_numbers
WHERE group_mark_id = p_transaction_temp_id ;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
WHERE group_mark_id = p_transaction_temp_id ;
RAISE exc_not_deleted;
SELECT count (*)
INTO l_msnt_count
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id;
RAISE exc_not_deleted;
debug_print( 'Records deleted in MSNT = ' || SQL%ROWCOUNT);
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
RAISE exc_not_deleted;
debug_print( 'Records deleted in MTLT = ' || SQL%ROWCOUNT);
SELECT count (*)
INTO l_wdt_count
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_transaction_temp_id;
DELETE wms_dispatched_tasks WHERE transaction_temp_id = p_transaction_temp_id;
debug_print( 'Records deleted in WDT = ' || SQL%ROWCOUNT);
RAISE exc_not_deleted;
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
RAISE exc_not_deleted;
debug_print( 'Records deleted in MMTT = ' || SQL%ROWCOUNT);
WHEN exc_not_deleted THEN
IF l_debug =1 THEN
debug_print('In the exception for could not delete a record. Returning staus as E');
END delete_transaction;
/*PROCEDURE delete_details()
This procedure calls inv_reservation_pub.update_reservation() to udpate/delete reservation and subsequently
calls delete_transactions.
*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE delete_details( p_transaction_temp_id IN NUMBER ,
p_move_order_line_id IN NUMBER ,
p_reservation_id IN NUMBER ,
p_transaction_quantity IN NUMBER ,
p_primary_trx_qty IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
exc_not_deleted EXCEPTION;
SAVEPOINT delete_details;
debug_print('delete_mo: Error occurred while deleting MMTT');
ROLLBACK TO delete_details;
RAISE exc_not_deleted ;
SELECT 1,
primary_uom_code
INTO l_ato_item,
l_primary_uom
FROM mtl_system_items
WHERE replenish_to_order_flag = 'Y'
AND bom_item_type = 4
AND inventory_item_id = l_mtl_reservation_tbl(1).inventory_item_id
AND organization_id = l_mtl_reservation_tbl(1).organization_id;
debug_print( 'call inv_reservation_pub.update_reservation = ');
inv_reservation_pub.update_reservation( p_api_version_number => 1.0 ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
p_original_rsv_rec => l_mtl_reservation_rec ,
p_to_rsv_rec => l_mtl_reservation_tbl(1) ,
p_original_serial_number => l_original_serial_number ,
p_to_serial_number => l_to_serial_number);
debug_print('delete_mo: Error occurred while updating reservations');
ROLLBACK TO delete_details;
RAISE exc_not_deleted ;
inv_reservation_pub.update_reservation( p_api_version_number => 1.0 ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
p_original_rsv_rec => l_mtl_reservation_rec ,
p_to_rsv_rec => l_mtl_reservation_tbl(1) ,
p_original_serial_number => l_original_serial_number ,
p_to_serial_number => l_to_serial_number);
debug_print( 'x_return_status from inv_reservation_pub.update_reservation ' || x_return_status);
debug_print('delete_mo: Error occurred while updating reservations');
ROLLBACK TO delete_details;
RAISE exc_not_deleted ;
debug_print( 'call delete_transaction ' );
delete_transaction( x_return_status => x_return_status ,
x_msg_data => x_msg_data ,
x_msg_count => x_msg_count ,
p_transaction_temp_id => p_transaction_temp_id ,
p_update_parent => FALSE);
debug_print('delete_mo: Error occurred while deleting MMTT');
ROLLBACK TO delete_details;
RAISE exc_not_deleted ;
debug_print('delete_mo: Error occurred while deleting MMTT');
ROLLBACK TO delete_details;
WHEN exc_not_deleted THEN
IF l_debug =1 THEN
debug_print('In the exception for could not delete a record. Returning staus as E');
ROLLBACK TO delete_details;
END delete_details;
exc_not_deleted EXCEPTION;
SELECT delivery_detail_id,
oe_header_id ,
oe_line_id ,
released_status
FROM wsh_inv_delivery_details_v
WHERE move_order_line_id = p_mo_line_rec.line_id
AND move_order_line_id IS NOT NULL
AND released_status = 'S';
debug_print('Calling Update Shipping Attributes');
wsh_interface.update_shipping_attributes( p_source_code => 'INV' ,
p_changed_attributes => l_shipping_attr ,
x_return_status => x_return_status );
debug_print( 'Updated Shipping Attributes - Return Status = ' || x_return_status);
RAISE exc_not_deleted ;
RAISE exc_not_deleted ;
WHEN exc_not_deleted THEN
IF l_debug =1 THEN
debug_print('In the exception for could not delete a record. Returning staus as E');
This procedure calls wsh_interface.update_shipping_attributes() for MO of type pick wave, else calls
wip_picking_pub.unallocate_material for MO of type WIP
*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE backorder( p_line_id IN NUMBER ,
p_transaction_temp_id IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_mo_line_rec inv_move_order_pub.trolin_rec_type;
exc_not_deleted EXCEPTION;
SELECT 'N'
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM wms_dispatched_tasks wdt,
mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_mo_line_rec.line_id
AND wdt.transaction_temp_id = nvl(mmtt.parent_line_id, mmtt.transaction_temp_id)
AND wdt.status IN (4,9)
);
SELECT mtrh.move_order_type
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_mo_line_rec.line_id
AND mtrh.header_id = mtrl.header_id;
SELECT mmtt.transaction_temp_id ,
ABS(mmtt.primary_quantity) primary_quantity ,
ABS(mmtt.transaction_quantity) transaction_quantity ,
mmtt.reservation_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = p_line_id
AND NOT EXISTS
(SELECT 1
FROM mtl_material_transactions_temp t
WHERE t.parent_line_id = mmtt.transaction_temp_id
)
FOR UPDATE NOWAIT;
SELECT transaction_quantity
INTO l_transaction_quantity
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id;
delete_details( x_return_status => x_return_status ,
x_msg_data => x_msg_data ,
x_msg_count => x_msg_count ,
p_transaction_temp_id => l_mmtt_info.transaction_temp_id ,
p_move_order_line_id => p_line_id ,
p_reservation_id => l_mmtt_info.reservation_id ,
p_transaction_quantity => l_mmtt_info.transaction_quantity ,
p_primary_trx_qty => l_mmtt_info.primary_quantity );
RAISE exc_not_deleted ;
RAISE exc_not_deleted ;
UPDATE mtl_txn_request_lines
SET line_status = 5 ,
quantity_detailed = NVL(quantity_delivered,0) ,
quantity = NVL(quantity_delivered,0)
WHERE line_id = p_line_id;
RAISE exc_not_deleted ;
RAISE exc_not_deleted ;
WHEN exc_not_deleted THEN
IF l_debug =1 THEN
debug_print('In the exception for could not delete a record. Returning staus as E');
/*PROCEDURE DELETE_OUTBOUND_TASKS()
This is the proceudre called from the DELETE_TASKS API when the task is an Outbound task.
Deletes Sales Orders, Internal Orders and WIP Pick tasks.
*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE DELETE_OUTBOUND_TASKS (p_task_rec IN task_record_type,
x_task_rec OUT NOCOPY task_record_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_transaction_number NUMBER ;
l_update_parent BOOLEAN := FALSE ; -- No need to call update_parent_mmtt
exc_not_deleted EXCEPTION;
SELECT mmtt.transaction_temp_id ,
mmtt.parent_line_id --For checking bulk task
,
mmtt.inventory_item_id ,
mmtt.move_order_line_id ,
mmtt.transaction_uom ,
mmtt.primary_quantity ,
mmtt.transaction_quantity ,
mmtt.wms_task_type
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = l_transaction_number
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_temp_id ,
mmtt.parent_line_id --For checking bulk task
,
mmtt.inventory_item_id ,
mmtt.move_order_line_id ,
mmtt.transaction_uom ,
mmtt.primary_quantity ,
mmtt.transaction_quantity ,
mmtt.wms_task_type
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = l_transaction_number
AND mmtt.parent_line_id <> mmtt.transaction_temp_id;
SELECT mtrl.uom_code ,
mtrl.transaction_source_type_id ,
mtrl.transaction_type_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_transaction_record.move_order_line_id;
SELECT COUNT(*)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_transaction_record.move_order_line_id
AND mmtt.transaction_temp_id <> l_transaction_record.transaction_temp_id
AND NOT EXISTS
(SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id
);
SELECT mtrh.move_order_type
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_transaction_record.move_order_line_id
AND mtrh.header_id = mtrl.header_id;
debug_print('IN DELETE_OUTBOUND_TASKS');
debug_print ( 'delete_so: l_progress: ' || l_progress );
debug_print ( 'delete_so: l_transaction_record.transaction_temp_id ' || l_transaction_record.transaction_temp_id );
debug_print ( 'delete_so: l_transaction_record.parent_line_id ' || l_transaction_record.parent_line_id );
debug_print ( 'delete_so: l_transaction_record.inventory_item_id ' || l_transaction_record.inventory_item_id );
debug_print ( 'delete_so: l_transaction_record.move_order_line_id ' || l_transaction_record.move_order_line_id );
debug_print ( 'delete_so: l_transaction_record.transaction_uom ' || l_transaction_record.transaction_uom );
debug_print ( 'delete_so: l_transaction_record.primary_quantity ' || l_transaction_record.primary_quantity );
debug_print ( 'delete_so: l_transaction_record.transaction_quantity ' || l_transaction_record.transaction_quantity );
debug_print ( 'delete_so: l_transaction_record.wms_task_type ' || l_transaction_record.wms_task_type );
debug_print ( 'delete_so: l_progress: ' || l_progress );
debug_print('delete_so: l_move_order_rec.uom_code :'|| l_move_order_rec.uom_code);
debug_print('delete_so: l_move_order_rec.transaction_source_type_id :'|| l_move_order_rec.transaction_source_type_id);
debug_print('delete_so: l_move_order_rec.transaction_type_id :'|| l_move_order_rec.transaction_type_id);
debug_print ( 'delete_so: l_progress: ' || l_progress );
debug_print( 'delete_so: Number of MMTTs other than this MMTT : ' || l_other_mmtt_count);
debug_print('delete_so: Other MMTT lines exist too. So cant close MO Line');
debug_print ( 'delete_so: l_progress: ' || l_progress );
debug_print( 'delete_so: Before we update MO and delete MMTT, we need to update reservation ');
SELECT nvl(mmtt.reservation_id,-1) ,
mr.primary_reservation_quantity ,
mr.reservation_quantity ,
mr.primary_uom_code ,
mr.reservation_uom_code
INTO l_reservation_id ,
l_pri_rsv_qty ,
l_rsv_qty ,
l_pri_rsv_uom ,
l_rsv_uom
FROM mtl_material_transactions_temp mmtt ,
mtl_reservations mr
WHERE mmtt.transaction_temp_id = l_transaction_record.transaction_temp_id
AND mr.reservation_id = mmtt.reservation_id ;
debug_print('delete_so: l_reservation_id:'||l_reservation_id || ' ,l_pri_rsv_qty :' ||l_pri_rsv_qty||',l_rsv_qty :'||l_rsv_qty );
debug_print('delete_so: MMTT.pri_qty:'||l_transaction_record.primary_quantity ||' ,l_pri_rsv_uom :'||l_pri_rsv_uom||',l_rsv_uom :'||l_rsv_uom );
debug_print( 'delete_so: Calling update_reservation api : ' );
inv_reservation_pub.update_reservation( p_api_version_number => 1.0 ,
p_init_msg_lst => fnd_api.g_false ,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
p_original_rsv_rec => l_old_upd_resv_rec ,
p_to_rsv_rec => l_new_upd_resv_rec ,
p_original_serial_number => l_upd_dummy_sn ,
p_to_serial_number => l_upd_dummy_sn ,
p_validation_flag => fnd_api.g_true );
debug_print( 'delete_so: return of update_reservation api : ' || x_return_status);
debug_print( 'delete_so: Error updating reservation ' );
RAISE exc_not_deleted ;
debug_print( 'delete_so: There is no reservation for this MMTT ' );
debug_print( 'delete_so: OTHERS EXCEPTION !!!! while Updating reservation ' );
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_transaction_record.transaction_temp_id ,
p_update_parent => l_update_parent );
debug_print ( 'delete_so: l_progress: ' || l_progress );
debug_print('delete_so: Error occurred while deleting MMTT');
RAISE exc_not_deleted ;
debug_print('delete_mo: Error occurred while deleting MMTT');
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_transaction_record.transaction_quantity
WHERE line_id = l_transaction_record.move_order_line_id;
debug_print ( 'delete_so: error updateing MTRL::: ');
RAISE exc_not_deleted ;
debug_print ( 'delete_so: l_mo_type::: ' || l_mo_type);
debug_print('delete_so: Error occurred while backordering WDD');
RAISE exc_not_deleted ;
debug_print ( 'delete_so: l_progress: ' || l_progress );
debug_print('delete_so: Just one MMTT line exists. Close MO');
SELECT count(*)
INTO l_wdt_count
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_transaction_number;
DELETE
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_transaction_number;
debug_print ( 'delete_so: l_progress: ' || l_progress );
debug_print ( 'delete_so: error deleting WDT::: ');
RAISE exc_not_deleted ;
debug_print('delete_so: Unexpected error occurrend while calling BackOrder API');
RAISE exc_not_deleted ;
debug_print('delete_so: Unexpected error occurred while calling BackOrder API');
debug_print('delete_so: Now calling delete transaction for parent line');
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_transaction_record.parent_line_id ,
p_update_parent => l_update_parent );
debug_print('delete_so: Error occurred while deleting parent line in MMTT');
RAISE exc_not_deleted ;
debug_print('delete_so: Error occurred while deleting MMTT');
WHEN exc_not_deleted THEN
IF l_debug =1 THEN
debug_print('In the exception, could not delete a record. Returning staus as E');
debug_print('delete_so: In the When Others Exception, Rolling back.');
END DELETE_OUTBOUND_TASKS;
/*PROCEDURE DELETE_MO_TASKS()
This is the proceudre called from the DELETE_TASKS API when the task is a Move Order.
Deletes Replenishment, MO Xfer, MO Issue tasks
*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE DELETE_MO_TASKS (p_task_rec IN task_record_type,
x_task_rec OUT NOCOPY task_record_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_transaction_number NUMBER ;
l_update_parent BOOLEAN := FALSE ; -- No need to call update_parent_mmtt
exc_not_deleted EXCEPTION;
SELECT mmtt.transaction_temp_id ,
mmtt.parent_line_id --For checking bulk task
,
mmtt.inventory_item_id ,
mmtt.move_order_line_id ,
mmtt.transaction_uom ,
mmtt.primary_quantity ,
mmtt.wms_task_type
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = l_transaction_number
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_temp_id ,
mmtt.parent_line_id --For checking bulk task
,
mmtt.inventory_item_id ,
mmtt.move_order_line_id ,
mmtt.transaction_uom ,
mmtt.primary_quantity ,
mmtt.wms_task_type
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = l_transaction_number
AND mmtt.parent_line_id <> mmtt.transaction_temp_id;
SELECT mtrl.uom_code ,
mtrl.transaction_source_type_id ,
mtrl.transaction_type_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_transaction_record.move_order_line_id;
SELECT COUNT(*)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_transaction_record.move_order_line_id
AND mmtt.transaction_temp_id <> l_transaction_record.transaction_temp_id
AND NOT EXISTS
(SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id
);
debug_print('IN DELETE_MO_TASKS');
debug_print ( 'delete_mo: l_progress: ' || l_progress );
debug_print ( 'delete_mo: l_progress: ' || l_progress );
debug_print ( 'delete_mo: l_transaction_record.transaction_temp_id ' || l_transaction_record.transaction_temp_id );
debug_print ( 'delete_mo: l_transaction_record.parent_line_id ' || l_transaction_record.parent_line_id );
debug_print ( 'delete_mo: l_transaction_record.inventory_item_id ' || l_transaction_record.inventory_item_id );
debug_print ( 'delete_mo: l_transaction_record.move_order_line_id ' || l_transaction_record.move_order_line_id );
debug_print ( 'delete_mo: l_transaction_record.transaction_uom ' || l_transaction_record.transaction_uom );
debug_print ( 'delete_mo: l_transaction_record.primary_quantity ' || l_transaction_record.primary_quantity );
debug_print ( 'delete_mo: l_transaction_record.wms_task_type ' || l_transaction_record.wms_task_type );
debug_print ( 'delete_mo: l_progress: ' || l_progress );
debug_print('delete_mo: l_move_order_rec.uom_code :'|| l_move_order_rec.uom_code);
debug_print('delete_mo: l_move_order_rec.transaction_source_type_id :'|| l_move_order_rec.transaction_source_type_id);
debug_print('delete_mo: l_move_order_rec.transaction_type_id :'|| l_move_order_rec.transaction_type_id);
debug_print ( 'delete_mo: l_progress: ' || l_progress );
debug_print( 'delete_mo: Number of MMTTs other than this MMTT : ' || l_other_mmtt_count);
debug_print('delete_mo: Other MMTT lines exist too. So cant close MO Line');
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_transaction_record.transaction_temp_id ,
p_update_parent => l_update_parent );
debug_print ( 'delete_mo: l_progress: ' || l_progress );
debug_print('delete_mo: Error occurred while deleting MMTT');
RAISE exc_not_deleted ;
debug_print('delete_mo: Error occurred while deleting MMTT');
debug_print ( 'delete_mo: l_progress: ' || l_progress );
debug_print ('delete_mo: l_transaction_record.move_order_line_id' || l_transaction_record.move_order_line_id );
UPDATE mtl_txn_request_lines
SET quantity_detailed = nvl(quantity_delivered,0),
line_status = 5 ,
last_update_date = SYSDATE
WHERE line_id = l_transaction_record.move_order_line_id;
debug_print ( 'delete_mo: error updating MTRL::: ');
RAISE exc_not_deleted ;
debug_print ( 'delete_mo: before calling delete transaction::: ');
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_transaction_record.transaction_temp_id ,
p_update_parent => l_update_parent );
debug_print('delete_mo: Error occurred while deleting MMTT');
RAISE exc_not_deleted ;
debug_print('delete_mo: Error occurred while deleting MMTT');
debug_print ( 'delete_mo: l_progress: ' || l_progress );
WHEN exc_not_deleted THEN
IF l_debug =1 THEN
debug_print('In the exception for could not delete a record. Returning staus as E');
debug_print('delete_mo: In the When Others Exception, Rolling back.');
END DELETE_MO_TASKS;
/*PROCEDURE DELETE_INBOUND_TASKS
This is the proceudre called from the DELETE_TASKS API when the task is a Putaway Task.
*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE DELETE_INBOUND_TASKS ( p_task_rec IN task_record_type,
x_task_rec OUT NOCOPY task_record_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_other_mmtt_count NUMBER;
l_update_parent BOOLEAN := FALSE ; -- No need to call update_parent_mmtt
exc_not_deleted EXCEPTION;
SELECT mmtt.transaction_temp_id ,
mmtt.parent_line_id ,
mmtt.organization_id ,
mmtt.inventory_item_id ,
mmtt.move_order_line_id ,
mmtt.transaction_uom ,
mmtt.primary_quantity ,
mmtt.transaction_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = l_transaction_number
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_temp_id ,
mmtt.parent_line_id ,
mmtt.organization_id ,
mmtt.inventory_item_id ,
mmtt.move_order_line_id ,
mmtt.transaction_uom ,
mmtt.primary_quantity ,
mmtt.transaction_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = l_transaction_number
AND mmtt.parent_line_id <> mmtt.transaction_temp_id;
SELECT mtrl.uom_code ,
mtrl.transaction_source_type_id ,
mtrl.transaction_type_id ,
backorder_delivery_detail_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_transaction_record.move_order_line_id;
SELECT COUNT(*)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_transaction_record.move_order_line_id
AND mmtt.transaction_temp_id <> l_transaction_record.transaction_temp_id
AND NOT EXISTS
(SELECT 1
FROM mtl_material_transactions_temp t1
WHERE t1.parent_line_id = mmtt.transaction_temp_id
);
SELECT status ,
orig_dest_sub_code ,
orig_dest_loc_id
FROM WMS_OP_PLAN_INSTANCES
WHERE source_task_id = l_transaction_record.parent_line_id;
debug_print('IN DELETE_INBOUND_TASKS');
debug_print ( 'delete_inbound_tasks: l_progress: ' || l_progress );
debug_print ( 'delete_inbound_tasks: l_transaction_record.transaction_temp_id ' || l_transaction_record.transaction_temp_id );
debug_print ( 'delete_inbound_tasks: l_transaction_record.parent_line_id ' || l_transaction_record.parent_line_id );
debug_print ( 'delete_inbound_tasks: l_transaction_record.inventory_item_id ' || l_transaction_record.inventory_item_id );
debug_print ( 'delete_inbound_tasks: l_transaction_record.move_order_line_id ' || l_transaction_record.move_order_line_id );
debug_print ( 'delete_inbound_tasks: l_transaction_record.transaction_uom ' || l_transaction_record.transaction_uom );
debug_print ( 'delete_inbound_tasks: l_transaction_record.primary_quantity ' || l_transaction_record.primary_quantity );
debug_print ( 'delete_inbound_tasks: l_transaction_record.transaction_quantity ' || l_transaction_record.transaction_quantity );
RAISE exc_not_deleted ;
debug_print ( 'delete_inbound_tasks: l_progress: ' || l_progress );
debug_print ( 'delete_inbound_tasks: l_progress: ' || l_progress );
debug_print('delete_inbound_tasks: l_move_order_rec.uom_code :'|| l_move_order_rec.uom_code);
debug_print('delete_inbound_tasks: l_move_order_rec.transaction_source_type_id :'|| l_move_order_rec.transaction_source_type_id);
debug_print('delete_inbound_tasks: l_move_order_rec.transaction_type_id :'|| l_move_order_rec.transaction_type_id);
debug_print('delete_inbound_tasks: l_move_order_rec.backorder_delivery_detail_id :'|| l_move_order_rec.backorder_delivery_detail_id);
DELETE
FROM wms_op_plan_instances
WHERE source_task_id = l_transaction_record.parent_line_id;
debug_print('Count not delete the Plan Instance of this task');
RAISE exc_not_deleted ;
DELETE
FROM wms_op_operation_instances
WHERE source_task_id = l_transaction_record.transaction_temp_id;
debug_print('Count not update the operation instance of this task');
RAISE exc_not_deleted ;
delete_transaction( x_return_status => l_return_status ,
x_msg_data => x_msg_data ,
x_msg_count => x_msg_count ,
p_transaction_temp_id => l_transaction_record.transaction_temp_id ,p_update_parent => l_update_parent );
debug_print('delete_inbound_tasks: Error while deleting MMTT/MTLT/MSNT/WDT');
RAISE exc_not_deleted ;
debug_print('delete_inbound_tasks: Error occurred while deleting MMTT');
DELETE
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE transaction_temp_id = l_transaction_record.parent_line_id ;
debug_print('Count not delete the parent mmtt');
RAISE exc_not_deleted ;
debug_print('delete_inbound_tasks: Error while reverting locator capacity');
RAISE exc_not_deleted ;
debug_print( 'delete_inbound_tasks: Number of MMTTs other than this MMTT : ' || l_other_mmtt_count);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_transaction_record.primary_quantity
WHERE line_id = l_transaction_record.move_order_line_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = null
WHERE line_id = l_transaction_record.move_order_line_id;
debug_print('Could not update the move order line.');
RAISE exc_not_deleted ;
WHEN exc_not_deleted THEN
IF l_debug =1 THEN
debug_print('In the exception for could not delete a record. Returning staus as E');
debug_print('delete_inbound_tasks:In the When Others Exception, Rolling back.' );
END DELETE_INBOUND_TASKS;
/*PROCEDURE DELETE_CC_TASKS
This is the proceudre called from the DELETE_TASKS API when the task is a Cycle Count Entry.
This update the entry to a Rejected Status and deletes the corresponding task record if it exists.
*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE DELETE_CC_TASKS ( p_task_rec IN task_record_type,
x_task_rec OUT NOCOPY task_record_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_cc_entry_id NUMBER ;
l_update_cyc NUMBER;
exc_not_deleted EXCEPTION;
SELECT cycle_count_header_id
INTO l_cyc_header_id
FROM mtl_cycle_count_entries
WHERE cycle_count_entry_id = l_cc_entry_id ;
UPDATE mtl_cycle_count_entries
SET entry_status_code = 4
WHERE cycle_count_entry_id = l_cc_entry_id ;
debug_print('delete_cc_tasks: In Delete Cycle Count Tasks');
debug_print('delete_cc_tasks: Value of cycle count header:'||l_cyc_header_id);
debug_print('delete_cc_tasks: Value of cycle count entry:'||l_cc_entry_id);
debug_print('delete_cc_tasks: Could not update the cycle count entry.');
RAISE exc_not_deleted ;
SELECT count(task_id)
INTO l_cyc_task
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_cc_entry_id
AND task_type = 3;
DELETE
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_cc_entry_id
AND task_type = 3;
debug_print('delete_cc_tasks: Could not delete the cycle count task.');
RAISE exc_not_deleted;
END IF; --Rowcount for wdt delete
WHEN exc_not_deleted THEN
IF l_debug =1 THEN
debug_print('delete_cc_tasks: In the exception for could not delete a record. Returning staus as E');
x_task_rec.ERROR := 'Could not delete the cycle count task.';
debug_print('delete_cc_tasks: In the When Others Exception, Rolling back.' );
END DELETE_CC_TASKS;
PROCEDURE delete_tasks ( p_transaction_number IN NUMBER DEFAULT NULL ,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
p_wms_task IN WMS_TASK_MGMT_PUB.task_tab_type ,
x_undeleted_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 )
Parameter Description
p_transaction_number This corrsponds to the task_id that user is trying to delete.
P_wms_task This correspinds to the set of tasks that user is trying to delete.
P_Commit This parameter decides whether to commit the changes or not.
X_undeleted_tasks This parameter contains the set of undeleted tasks.
X_return_status This parameter gives the return status of delete_task API.
'S' = Success, 'U' = Unexpected Error, 'E' = Error.
X_msg_count This gives the count of messages logged during the task deletion process.
X_msg_data This gives the descrption of the messages that got logged during the task deletion process.
-------------------------------------------------------------------------------------------------------------------*/
PROCEDURE delete_tasks ( p_transaction_number IN NUMBER DEFAULT NULL ,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
p_wms_task IN WMS_TASK_MGMT_PUB.task_tab_type ,
x_undeleted_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_task_table WMS_TASK_MGMT_PUB.task_tab_type ;
debug_print('IN DELETE_TASKS' );
debug_print('Values passed to delete_tasks p_transaction_number:'|| p_transaction_number );
SELECT 1,
wms_task_type
INTO l_task_exists,
l_task_type
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_ret_task_table(i).transaction_number FOR UPDATE NOWAIT;
SELECT count(cycle_count_entry_id)
INTO l_task_exists
FROM mtl_cycle_count_entries
WHERE cycle_count_entry_id = l_ret_task_table(i).transaction_number ;
SELECT 1
INTO l_lock_cyc_rec
FROM mtl_cycle_count_entries
WHERE cycle_count_entry_id = l_ret_task_table(i).transaction_number FOR UPDATE NOWAIT;
DELETE_CC_TASKS(p_task_rec => l_task_rec, x_task_rec => x_task_rec, x_return_status => x_return_status);
DELETE_OUTBOUND_TASKS(p_task_rec => l_task_rec, x_task_rec => x_task_rec, x_return_status => x_return_status);
DELETE_INBOUND_TASKS(p_task_rec => l_task_rec, x_task_rec => x_task_rec, x_return_status => x_return_status);
DELETE_MO_TASKS(p_task_rec => l_task_rec, x_task_rec => x_task_rec, x_return_status => x_return_status);
FND_MESSAGE.SET_NAME('WMS', 'WMS_TASK_DELETE_ERROR');
x_undeleted_tasks(undel_task).transaction_number:= x_task_rec.transaction_number;
x_undeleted_tasks(undel_task).RESULT := x_task_rec.status;
x_undeleted_tasks(undel_task).error := l_msg;
x_undeleted_tasks(undel_task).transaction_number:= l_ret_task_table(i).transaction_number ; --6888354 Bug
x_undeleted_tasks(undel_task).RESULT := l_ret_task_table(i).RESULT;
x_undeleted_tasks(undel_task).error := l_msg;
debug_print('In the Unexpected Error of Delete_Tasks, Rolling back.' );
debug_print('In the When Others Exception of Delete_Tasks, Rolling back.' );
END DELETE_TASKS;
p_transaction_number This corrsponds to the task_id that user is trying to update
P_query_name This correspinds to name of any saved query from the WMS control board.
This is used for querying multiple tasks
P_Commit This parameter decides whether to commit the changes or not.
X_task_tab PL/SQL table which contains the task/tasks queried by the user.
X_return_status This parameter gives the return status of query_task API.
'S' = Success, 'U' = Unexpected Error, 'E' = Error.
X_msg_count This gives the count of messages logged during the query task process.
X_msg_data This gives the descrption of the messages that got logged during the query task process.
*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE query_task( p_transaction_number IN NUMBER DEFAULT NULL
,p_query_name IN VARCHAR2
,x_task_tab OUT NOCOPY task_tab_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_mmtt_rec mtl_material_transactions_temp%ROWTYPE;
SELECT 1 FROM mtl_material_transactions_temp mmtt,mtl_txn_request_lines mtrl
WHERE mtrl.backorder_delivery_detail_id IS NOT NULL
AND mtrl.line_id = mmtt.move_order_line_id
AND transaction_temp_id = p_transaction_number;
SELECT 1 FROM wms_dispatched_tasks_history wdth,mtl_txn_request_lines mtrl
WHERE mtrl.backorder_delivery_detail_id IS NOT NULL
AND mtrl.line_id = wdth.move_order_line_id
AND transaction_id = p_transaction_number;
SELECT *
INTO l_mmtt_rec
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_number;
/* SELECT * BULK COLLECT
INTO x_task_tab
FROM wms_waveplan_tasks_temp
WHERE transaction_temp_id = p_transaction_number;*/
--Replacing the above by selecting columns from the table.
SELECT
TASK_ID,
TRANSACTION_TEMP_ID,
PARENT_LINE_ID,
INVENTORY_ITEM_ID,
ITEM,
ITEM_DESCRIPTION,
UNIT_WEIGHT,
WEIGHT_UOM_CODE,
DISPLAY_WEIGHT,
UNIT_VOLUME,
VOLUME_UOM_CODE,
DISPLAY_VOLUME,
TIME_ESTIMATE,
ORGANIZATION_ID,
ORGANIZATION_CODE,
REVISION,
SUBINVENTORY,
LOCATOR_ID,
LOCATOR,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_TYPE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_LINE_ID,
TO_ORGANIZATION_ID,
TO_ORGANIZATION_CODE,
TO_SUBINVENTORY,
TO_LOCATOR_ID,
TO_LOCATOR,
TRANSACTION_UOM,
TRANSACTION_QUANTITY,
USER_TASK_TYPE_ID,
USER_TASK_TYPE,
PERSON_ID,
PERSON_ID_ORIGINAL,
PERSON,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PERSON_RESOURCE_ID,
PERSON_RESOURCE_CODE,
MACHINE_RESOURCE_ID,
MACHINE_RESOURCE_CODE,
EQUIPMENT_INSTANCE,
STATUS_ID,
STATUS_ID_ORIGINAL,
STATUS,
CREATION_TIME,
DISPATCHED_TIME,
LOADED_TIME,
DROP_OFF_TIME,
MMTT_LAST_UPDATE_DATE,
MMTT_LAST_UPDATED_BY,
WDT_LAST_UPDATE_DATE,
WDT_LAST_UPDATED_BY,
PRIORITY,
PRIORITY_ORIGINAL,
TASK_TYPE_ID,
TASK_TYPE,
MOVE_ORDER_LINE_ID,
PICK_SLIP_NUMBER,
CARTONIZATION_ID,
ALLOCATED_LPN_ID,
CONTAINER_ITEM_ID,
CONTENT_LPN_ID,
TO_LPN_ID,
CONTAINER_ITEM ,
CARTONIZATION_LPN,
ALLOCATED_LPN ,
CONTENT_LPN,
TO_LPN,
REFERENCE ,
REFERENCE_ID,
CUSTOMER_ID,
CUSTOMER ,
SHIP_TO_LOCATION_ID ,
SHIP_TO_STATE ,
SHIP_TO_COUNTRY,
SHIP_TO_POSTAL_CODE,
DELIVERY_ID ,
DELIVERY ,
SHIP_METHOD ,
CARRIER_ID ,
CARRIER ,
SHIPMENT_DATE ,
SHIPMENT_PRIORITY,
WIP_ENTITY_TYPE,
WIP_ENTITY_ID,
ASSEMBLY_ID,
ASSEMBLY ,
LINE_ID,
LINE,
DEPARTMENT_ID,
DEPARTMENT,
SOURCE_HEADER,
LINE_NUMBER,
OPERATION_PLAN_ID,
OPERATION_PLAN ,
RESULT ,
ERROR ,
IS_MODIFIED ,
EXPANSION_CODE ,
FROM_LPN ,
FROM_LPN_ID,
NUM_OF_CHILD_TASKS,
OPERATION_SEQUENCE,
OP_PLAN_INSTANCE_ID,
PLANS_TASKS ,
TRANSACTION_SET_ID,
PICKED_LPN_ID,
PICKED_LPN,
LOADED_LPN,
LOADED_LPN_ID,
DROP_LPN ,
SECONDARY_TRANSACTION_QUANTITY,
SECONDARY_TRANSACTION_UOM ,
PRIMARY_PRODUCT,
LOAD_SEQ_NUMBER
BULK COLLECT
INTO x_task_tab
FROM wms_waveplan_tasks_temp
WHERE transaction_temp_id = p_transaction_number;
SELECT *
INTO l_mcce_rec
FROM mtl_cycle_count_entries
WHERE cycle_count_entry_id = p_transaction_number;
SELECT cycle_count_header_name
INTO v_cycle_count_name
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = l_mcce_rec.cycle_count_header_id;
/* SELECT * BULK COLLECT
INTO x_task_tab
FROM wms_waveplan_tasks_temp
WHERE transaction_temp_id = p_transaction_number;*/
--Replacing the above by selecting columns from the table.
SELECT
TASK_ID,
TRANSACTION_TEMP_ID,
PARENT_LINE_ID,
INVENTORY_ITEM_ID,
ITEM,
ITEM_DESCRIPTION,
UNIT_WEIGHT,
WEIGHT_UOM_CODE,
DISPLAY_WEIGHT,
UNIT_VOLUME,
VOLUME_UOM_CODE,
DISPLAY_VOLUME,
TIME_ESTIMATE,
ORGANIZATION_ID,
ORGANIZATION_CODE,
REVISION,
SUBINVENTORY,
LOCATOR_ID,
LOCATOR,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_TYPE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_LINE_ID,
TO_ORGANIZATION_ID,
TO_ORGANIZATION_CODE,
TO_SUBINVENTORY,
TO_LOCATOR_ID,
TO_LOCATOR,
TRANSACTION_UOM,
TRANSACTION_QUANTITY,
USER_TASK_TYPE_ID,
USER_TASK_TYPE,
PERSON_ID,
PERSON_ID_ORIGINAL,
PERSON,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PERSON_RESOURCE_ID,
PERSON_RESOURCE_CODE,
MACHINE_RESOURCE_ID,
MACHINE_RESOURCE_CODE,
EQUIPMENT_INSTANCE,
STATUS_ID,
STATUS_ID_ORIGINAL,
STATUS,
CREATION_TIME,
DISPATCHED_TIME,
LOADED_TIME,
DROP_OFF_TIME,
MMTT_LAST_UPDATE_DATE,
MMTT_LAST_UPDATED_BY,
WDT_LAST_UPDATE_DATE,
WDT_LAST_UPDATED_BY,
PRIORITY,
PRIORITY_ORIGINAL,
TASK_TYPE_ID,
TASK_TYPE,
MOVE_ORDER_LINE_ID,
PICK_SLIP_NUMBER,
CARTONIZATION_ID,
ALLOCATED_LPN_ID,
CONTAINER_ITEM_ID,
CONTENT_LPN_ID,
TO_LPN_ID,
CONTAINER_ITEM ,
CARTONIZATION_LPN,
ALLOCATED_LPN ,
CONTENT_LPN,
TO_LPN,
REFERENCE ,
REFERENCE_ID,
CUSTOMER_ID,
CUSTOMER ,
SHIP_TO_LOCATION_ID ,
SHIP_TO_STATE ,
SHIP_TO_COUNTRY,
SHIP_TO_POSTAL_CODE,
DELIVERY_ID ,
DELIVERY ,
SHIP_METHOD ,
CARRIER_ID ,
CARRIER ,
SHIPMENT_DATE ,
SHIPMENT_PRIORITY,
WIP_ENTITY_TYPE,
WIP_ENTITY_ID,
ASSEMBLY_ID,
ASSEMBLY ,
LINE_ID,
LINE,
DEPARTMENT_ID,
DEPARTMENT,
SOURCE_HEADER,
LINE_NUMBER,
OPERATION_PLAN_ID,
OPERATION_PLAN ,
RESULT ,
ERROR ,
IS_MODIFIED ,
EXPANSION_CODE ,
FROM_LPN ,
FROM_LPN_ID,
NUM_OF_CHILD_TASKS,
OPERATION_SEQUENCE,
OP_PLAN_INSTANCE_ID,
PLANS_TASKS ,
TRANSACTION_SET_ID,
PICKED_LPN_ID,
PICKED_LPN,
LOADED_LPN,
LOADED_LPN_ID,
DROP_LPN ,
SECONDARY_TRANSACTION_QUANTITY,
SECONDARY_TRANSACTION_UOM ,
PRIMARY_PRODUCT,
LOAD_SEQ_NUMBER
BULK COLLECT
INTO x_task_tab
FROM wms_waveplan_tasks_temp
WHERE transaction_temp_id = p_transaction_number;
SELECT *
INTO l_wdth_rec
FROM wms_dispatched_tasks_history
WHERE transaction_id = p_transaction_number;
SELECT
TASK_ID,
TRANSACTION_TEMP_ID,
PARENT_LINE_ID,
INVENTORY_ITEM_ID,
ITEM,
ITEM_DESCRIPTION,
UNIT_WEIGHT,
WEIGHT_UOM_CODE,
DISPLAY_WEIGHT,
UNIT_VOLUME,
VOLUME_UOM_CODE,
DISPLAY_VOLUME,
TIME_ESTIMATE,
ORGANIZATION_ID,
ORGANIZATION_CODE,
REVISION,
SUBINVENTORY,
LOCATOR_ID,
LOCATOR,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_TYPE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_LINE_ID,
TO_ORGANIZATION_ID,
TO_ORGANIZATION_CODE,
TO_SUBINVENTORY,
TO_LOCATOR_ID,
TO_LOCATOR,
TRANSACTION_UOM,
TRANSACTION_QUANTITY,
USER_TASK_TYPE_ID,
USER_TASK_TYPE,
PERSON_ID,
PERSON_ID_ORIGINAL,
PERSON,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PERSON_RESOURCE_ID,
PERSON_RESOURCE_CODE,
MACHINE_RESOURCE_ID,
MACHINE_RESOURCE_CODE,
EQUIPMENT_INSTANCE,
STATUS_ID,
STATUS_ID_ORIGINAL,
STATUS,
CREATION_TIME,
DISPATCHED_TIME,
LOADED_TIME,
DROP_OFF_TIME,
MMTT_LAST_UPDATE_DATE,
MMTT_LAST_UPDATED_BY,
WDT_LAST_UPDATE_DATE,
WDT_LAST_UPDATED_BY,
PRIORITY,
PRIORITY_ORIGINAL,
TASK_TYPE_ID,
TASK_TYPE,
MOVE_ORDER_LINE_ID,
PICK_SLIP_NUMBER,
CARTONIZATION_ID,
ALLOCATED_LPN_ID,
CONTAINER_ITEM_ID,
CONTENT_LPN_ID,
TO_LPN_ID,
CONTAINER_ITEM ,
CARTONIZATION_LPN,
ALLOCATED_LPN ,
CONTENT_LPN,
TO_LPN,
REFERENCE ,
REFERENCE_ID,
CUSTOMER_ID,
CUSTOMER ,
SHIP_TO_LOCATION_ID ,
SHIP_TO_STATE ,
SHIP_TO_COUNTRY,
SHIP_TO_POSTAL_CODE,
DELIVERY_ID ,
DELIVERY ,
SHIP_METHOD ,
CARRIER_ID ,
CARRIER ,
SHIPMENT_DATE ,
SHIPMENT_PRIORITY,
WIP_ENTITY_TYPE,
WIP_ENTITY_ID,
ASSEMBLY_ID,
ASSEMBLY ,
LINE_ID,
LINE,
DEPARTMENT_ID,
DEPARTMENT,
SOURCE_HEADER,
LINE_NUMBER,
OPERATION_PLAN_ID,
OPERATION_PLAN ,
RESULT ,
ERROR ,
IS_MODIFIED ,
EXPANSION_CODE ,
FROM_LPN ,
FROM_LPN_ID,
NUM_OF_CHILD_TASKS,
OPERATION_SEQUENCE,
OP_PLAN_INSTANCE_ID,
PLANS_TASKS ,
TRANSACTION_SET_ID,
PICKED_LPN_ID,
PICKED_LPN,
LOADED_LPN,
LOADED_LPN_ID,
DROP_LPN ,
SECONDARY_TRANSACTION_QUANTITY,
SECONDARY_TRANSACTION_UOM ,
PRIMARY_PRODUCT,
LOAD_SEQ_NUMBER
BULK COLLECT
INTO x_task_tab
FROM wms_waveplan_tasks_temp
WHERE transaction_temp_id = p_transaction_number;
SELECT *
INTO l_mmt_rec
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_number;
SELECT
TASK_ID,
TRANSACTION_TEMP_ID,
PARENT_LINE_ID,
INVENTORY_ITEM_ID,
ITEM,
ITEM_DESCRIPTION,
UNIT_WEIGHT,
WEIGHT_UOM_CODE,
DISPLAY_WEIGHT,
UNIT_VOLUME,
VOLUME_UOM_CODE,
DISPLAY_VOLUME,
TIME_ESTIMATE,
ORGANIZATION_ID,
ORGANIZATION_CODE,
REVISION,
SUBINVENTORY,
LOCATOR_ID,
LOCATOR,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_TYPE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_LINE_ID,
TO_ORGANIZATION_ID,
TO_ORGANIZATION_CODE,
TO_SUBINVENTORY,
TO_LOCATOR_ID,
TO_LOCATOR,
TRANSACTION_UOM,
TRANSACTION_QUANTITY,
USER_TASK_TYPE_ID,
USER_TASK_TYPE,
PERSON_ID,
PERSON_ID_ORIGINAL,
PERSON,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PERSON_RESOURCE_ID,
PERSON_RESOURCE_CODE,
MACHINE_RESOURCE_ID,
MACHINE_RESOURCE_CODE,
EQUIPMENT_INSTANCE,
STATUS_ID,
STATUS_ID_ORIGINAL,
STATUS,
CREATION_TIME,
DISPATCHED_TIME,
LOADED_TIME,
DROP_OFF_TIME,
MMTT_LAST_UPDATE_DATE,
MMTT_LAST_UPDATED_BY,
WDT_LAST_UPDATE_DATE,
WDT_LAST_UPDATED_BY,
PRIORITY,
PRIORITY_ORIGINAL,
TASK_TYPE_ID,
TASK_TYPE,
MOVE_ORDER_LINE_ID,
PICK_SLIP_NUMBER,
CARTONIZATION_ID,
ALLOCATED_LPN_ID,
CONTAINER_ITEM_ID,
CONTENT_LPN_ID,
TO_LPN_ID,
CONTAINER_ITEM ,
CARTONIZATION_LPN,
ALLOCATED_LPN ,
CONTENT_LPN,
TO_LPN,
REFERENCE ,
REFERENCE_ID,
CUSTOMER_ID,
CUSTOMER ,
SHIP_TO_LOCATION_ID ,
SHIP_TO_STATE ,
SHIP_TO_COUNTRY,
SHIP_TO_POSTAL_CODE,
DELIVERY_ID ,
DELIVERY ,
SHIP_METHOD ,
CARRIER_ID ,
CARRIER ,
SHIPMENT_DATE ,
SHIPMENT_PRIORITY,
WIP_ENTITY_TYPE,
WIP_ENTITY_ID,
ASSEMBLY_ID,
ASSEMBLY ,
LINE_ID,
LINE,
DEPARTMENT_ID,
DEPARTMENT,
SOURCE_HEADER,
LINE_NUMBER,
OPERATION_PLAN_ID,
OPERATION_PLAN ,
RESULT ,
ERROR ,
IS_MODIFIED ,
EXPANSION_CODE ,
FROM_LPN ,
FROM_LPN_ID,
NUM_OF_CHILD_TASKS,
OPERATION_SEQUENCE,
OP_PLAN_INSTANCE_ID,
PLANS_TASKS ,
TRANSACTION_SET_ID,
PICKED_LPN_ID,
PICKED_LPN,
LOADED_LPN,
LOADED_LPN_ID,
DROP_LPN ,
SECONDARY_TRANSACTION_QUANTITY,
SECONDARY_TRANSACTION_UOM ,
PRIMARY_PRODUCT,
LOAD_SEQ_NUMBER
BULK COLLECT
INTO x_task_tab
FROM wms_waveplan_tasks_temp
WHERE transaction_temp_id = p_transaction_number;
SELECT REPLACE(field_name,'FIND_TASKS.','') field_name,
decode(field_value,'Y','T','N','F',field_value) field_value
BULK COLLECT
INTO l_query_tab
FROM wms_saved_queries
WHERE query_name = p_query_name
ORDER BY 1;
SELECT DISTINCT organization_id
INTO l_organization_id
FROM wms_saved_queries
WHERE query_name = p_query_name;
/* SELECT * BULK COLLECT
INTO x_task_tab
FROM wms_waveplan_tasks_temp;*/
SELECT
TASK_ID,
TRANSACTION_TEMP_ID,
PARENT_LINE_ID,
INVENTORY_ITEM_ID,
ITEM,
ITEM_DESCRIPTION,
UNIT_WEIGHT,
WEIGHT_UOM_CODE,
DISPLAY_WEIGHT,
UNIT_VOLUME,
VOLUME_UOM_CODE,
DISPLAY_VOLUME,
TIME_ESTIMATE,
ORGANIZATION_ID,
ORGANIZATION_CODE,
REVISION,
SUBINVENTORY,
LOCATOR_ID,
LOCATOR,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_TYPE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_LINE_ID,
TO_ORGANIZATION_ID,
TO_ORGANIZATION_CODE,
TO_SUBINVENTORY,
TO_LOCATOR_ID,
TO_LOCATOR,
TRANSACTION_UOM,
TRANSACTION_QUANTITY,
USER_TASK_TYPE_ID,
USER_TASK_TYPE,
PERSON_ID,
PERSON_ID_ORIGINAL,
PERSON,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
PERSON_RESOURCE_ID,
PERSON_RESOURCE_CODE,
MACHINE_RESOURCE_ID,
MACHINE_RESOURCE_CODE,
EQUIPMENT_INSTANCE,
STATUS_ID,
STATUS_ID_ORIGINAL,
STATUS,
CREATION_TIME,
DISPATCHED_TIME,
LOADED_TIME,
DROP_OFF_TIME,
MMTT_LAST_UPDATE_DATE,
MMTT_LAST_UPDATED_BY,
WDT_LAST_UPDATE_DATE,
WDT_LAST_UPDATED_BY,
PRIORITY,
PRIORITY_ORIGINAL,
TASK_TYPE_ID,
TASK_TYPE,
MOVE_ORDER_LINE_ID,
PICK_SLIP_NUMBER,
CARTONIZATION_ID,
ALLOCATED_LPN_ID,
CONTAINER_ITEM_ID,
CONTENT_LPN_ID,
TO_LPN_ID,
CONTAINER_ITEM ,
CARTONIZATION_LPN,
ALLOCATED_LPN ,
CONTENT_LPN,
TO_LPN,
REFERENCE ,
REFERENCE_ID,
CUSTOMER_ID,
CUSTOMER ,
SHIP_TO_LOCATION_ID ,
SHIP_TO_STATE ,
SHIP_TO_COUNTRY,
SHIP_TO_POSTAL_CODE,
DELIVERY_ID ,
DELIVERY ,
SHIP_METHOD ,
CARRIER_ID ,
CARRIER ,
SHIPMENT_DATE ,
SHIPMENT_PRIORITY,
WIP_ENTITY_TYPE,
WIP_ENTITY_ID,
ASSEMBLY_ID,
ASSEMBLY ,
LINE_ID,
LINE,
DEPARTMENT_ID,
DEPARTMENT,
SOURCE_HEADER,
LINE_NUMBER,
OPERATION_PLAN_ID,
OPERATION_PLAN ,
RESULT ,
ERROR ,
IS_MODIFIED ,
EXPANSION_CODE,
FROM_LPN ,
FROM_LPN_ID,
NUM_OF_CHILD_TASKS,
OPERATION_SEQUENCE,
OP_PLAN_INSTANCE_ID,
PLANS_TASKS ,
TRANSACTION_SET_ID,
PICKED_LPN_ID,
PICKED_LPN,
LOADED_LPN,
LOADED_LPN_ID,
DROP_LPN ,
SECONDARY_TRANSACTION_QUANTITY,
SECONDARY_TRANSACTION_UOM ,
PRIMARY_PRODUCT,
LOAD_SEQ_NUMBER
BULK COLLECT
INTO x_task_tab
FROM wms_waveplan_tasks_temp;
SELECT *
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_ttemp_id
ORDER BY lot_number;
select inventory_item_id, organization_id
from mtl_material_transactions_temp
where transaction_temp_id = p_txn_temp_id;
select lot_divisible_flag
from mtl_system_items
where inventory_item_id = p_item_id
and organization_id = p_organization_id;
new_task_table.delete;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_transaction_header_id
FROM dual;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_transaction_temp_id
FROM dual;
SELECT wms_dispatched_tasks_s.NEXTVAL INTO l_new_task_id FROM dual;
SELECT status
INTO l_mmtt_task_status
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_source_transaction_number;
UPDATE mtl_material_transactions_temp
SET primary_quantity = primary_quantity - l_split_uom_quantities(i).primary_quantity ,
transaction_quantity = transaction_quantity - l_split_uom_quantities(i).transaction_quantity ,
last_updated_by = FND_GLOBAL.USER_ID
WHERE transaction_temp_id = p_source_transaction_number;
print_msg(l_procedure_name, ' Updated original txn. temp id :'||p_source_transaction_number);
print_msg(l_procedure_name, 'Inserting Lot/Serial details of the new tasks in X_RESULTANT_TASK_DETAILS');
SELECT MIN(FM_SERIAL_NUMBER) ,
MAX(FM_SERIAL_NUMBER) ,
MAX(status_id)
INTO x_resultant_task_details(l_index).from_serial_number ,
x_resultant_task_details(l_index).to_serial_number ,
x_resultant_task_details(l_index).serial_status_id
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_mtlt_row.serial_transaction_temp_id;
SELECT MIN(FM_SERIAL_NUMBER) ,
MAX(FM_SERIAL_NUMBER) ,
MAX(status_id) ,
COUNT(*)
INTO x_resultant_task_details(l_index).from_serial_number ,
x_resultant_task_details(l_index).to_serial_number ,
x_resultant_task_details(l_index).serial_status_id ,
x_resultant_task_details(l_index).number_of_serials
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = new_task_table(i).transaction_temp_id;
, x_updated_tasks OUT NOCOPY WMS_TASKS_PUB.TASK_TABLE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2 );
p_transaction_number This corresponds to the task_id that user is trying to update
P_task_table This corresponds to the set of tasks that user is trying to update
P_new_task_status This corresponds to the new status to which user wants to update the task/set of tasks
P_new_task_priority This corresponds to new task priority which user wants to assign to the tasks/set of tasks.
P_new_task_tyoe This corresponds to the new task type which user wants to update on the task/set of tasks.
P_new_carton_lpn_id This is the carton_lpn_id which user wants to update on task/set of tasks.
P_new_operation_plan_id This is the new operation plan id which user wants to update on task/set of tasks.
P_person_id This the user to which task will be queued, if the task status is getting changed to 'Queued' state.
P_Commit This parameter decides whether to commit the changes or not.
X_updated_tasks This is a table of records, which contain the updated tasks. If the tasks could not be updated, the Result column in the table is updated with 'E' and the Error column is updated with the error message
X_return_status This parameter gives the return status of Modify_task API. 'S' = Success, 'U' = Unexpected Error, 'E' = Error.
X_msg_count This gives the count of messages logged during the task updation process.
X_msg_data This gives the description of the messages that got logged during the task updation process.
*/
-------------------------------------------------------------------------------------------------------------------
PROCEDURE modify_task ( p_transaction_number IN NUMBER DEFAULT NULL ,
p_task_table IN WMS_TASK_MGMT_PUB.task_tab_type ,
p_new_task_status IN NUMBER DEFAULT NULL ,
p_new_task_priority IN NUMBER DEFAULT NULL ,
p_new_task_type IN VARCHAR2 DEFAULT NULL ,
p_new_carton_lpn_id IN NUMBER DEFAULT NULL ,
p_new_operation_plan_id IN NUMBER DEFAULT NULL ,
p_person_id IN NUMBER DEFAULT NULL ,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE ,
x_updated_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
temp_task_tab WMS_TASK_MGMT_PUB.task_tab_type;
SELECT system_task_type ,
organization_id ,
enabled_flag ,
activity_type_id ,
common_to_all_org,
plan_type_id
FROM wms_op_plans_b
WHERE operation_plan_id=c_op_plan_id;
SELECT '1'
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_number FOR UPDATE NOWAIT;
SELECT '1'
FROM mtl_cycle_count_entries
WHERE cycle_count_entry_id = p_transaction_number FOR UPDATE NOWAIT;
l_updated_tasks WMS_TASK_MGMT_PUB.task_tab_type;
log_error(p_transaction_number => p_transaction_number, p_task_table => p_task_table, p_error_msg => l_msg, x_updated_tasks => x_updated_tasks);
x_updated_tasks => x_updated_tasks);
x_wms_task => l_updated_tasks,
x_return_status => p_return_sts);
l_valid_task_counter := l_updated_tasks.count;
FOR i IN 1..l_updated_tasks.count
LOOP--Loop starts
IF (nvl(l_updated_tasks(i).RESULT,'X')<> 'E') THEN
temp_task_tab.delete;-- := NULL; --flush the temporary table
WMS_TASK_MGMT_PUB.query_task( p_transaction_number => l_updated_tasks(i).transaction_number,
p_query_name => NULL,
x_task_tab => temp_task_tab,
x_return_status => p_return_sts,
x_msg_count => p_msg_count,
x_msg_data => p_msg_data);
x_updated_tasks(i) := l_updated_tasks(i);
x_updated_tasks(i).RESULT := 'E';
x_updated_tasks(i).ERROR := l_msg;
x_updated_tasks(i) := temp_task_tab(1);--assigning the queried results to output table
x_updated_tasks(i) := l_updated_tasks(i);
x_updated_tasks(i).RESULT := 'E';
x_updated_tasks(i).ERROR := l_msg;
IF (l_updated_tasks.count = l_valid_task_counter)THEN
FOR i in 1..l_updated_tasks.count
LOOP
IF ((l_updated_tasks(1).task_type_id <> 1) OR (l_updated_tasks(i).task_type_id <> l_updated_tasks(1).task_type_id )) THEN
fnd_message.set_name('WMS', 'WMS_CARTONIZATION_ERROR');--new message
p_task_table => l_updated_tasks,
p_error_msg => l_msg,
x_updated_tasks => x_updated_tasks);
check_cartonization(p_task_table => l_updated_tasks,
p_new_carton_lpn_id => p_new_carton_lpn_id,
x_error_msg => l_error_msg,
x_return_status => p_return_sts);
FOR i IN 1..l_updated_tasks.count
LOOP
x_updated_tasks(i).RESULT := 'E';
x_updated_tasks(i).ERROR := l_error_msg;
FOR i in 1..x_updated_tasks.count
LOOP
IF (nvl(x_updated_tasks(i).RESULT,'X')<> 'E') THEN
--Lock the mmtt/mcee record
BEGIN
IF (x_updated_tasks(i).task_type_id <> 3) THEN
OPEN c_lock_mmtt(x_updated_tasks(i).transaction_number);
OPEN c_lock_mcce(x_updated_tasks(i).transaction_number);
x_updated_tasks(i).RESULT := 'E';
x_updated_tasks(i).ERROR := l_err_msg;
p_temp_task_rec := x_updated_tasks(i);
p_output_task_rec => l_updated_tasks(i),
p_op_plan_rec => l_op_plan_rec);
--Copy values from l_updated_tasks to x_updated_tasks
x_updated_tasks(i).RESULT := nvl(l_updated_tasks(i).RESULT,x_updated_tasks(i).RESULT);
x_updated_tasks(i).ERROR := nvl(l_updated_tasks(i).ERROR,x_updated_tasks(i).ERROR);
x_updated_tasks(i).status_id := nvl(l_updated_tasks(i).status_id,x_updated_tasks(i).status_id);
x_updated_tasks(i).operation_plan_id := nvl(l_updated_tasks(i).operation_plan_id,x_updated_tasks(i).operation_plan_id);
x_updated_tasks(i).cartonization_id := nvl(l_updated_tasks(i).cartonization_id,x_updated_tasks(i).cartonization_id);
x_updated_tasks(i).priority := nvl(l_updated_tasks(i).priority,x_updated_tasks(i).priority);
x_updated_tasks(i).user_task_type_id := nvl(l_updated_tasks(i).user_task_type_id,x_updated_tasks(i).user_task_type_id);
IF (nvl(x_updated_tasks(i).RESULT,'X') = 'E') THEN
x_msg_count:= x_msg_count + 1;
x_msg_data := x_msg_data || x_updated_tasks(i).ERROR ;
FOR i IN 1..x_updated_tasks.count
LOOP
IF (nvl(x_updated_tasks(i).RESULT,'X')<> 'E') THEN
IF p_new_task_status = 2 THEN
l_transaction_num_table(l_transaction_num_table.COUNT+1) := x_updated_tasks(i).transaction_number;
l_organization_id_table(l_organization_id_table.COUNT+1) := x_updated_tasks(i).organization_id;
l_usertask_type_id_table(l_usertask_type_id_table.COUNt+1) := x_updated_tasks(i).user_task_type_id;
l_person_resource_id_tabe(l_person_resource_id_tabe.COUNt+1) := x_updated_tasks(i).person_resource_id;
l_machine_resource_id_table(l_machine_resource_id_table.COUNT+1) := x_updated_tasks(i).machine_resource_id;
l_status_id_table(l_status_id_table.COUNT+1) := x_updated_tasks(i).status_id;
l_priority_table(l_priority_table.COUNT+1) := x_updated_tasks(i).priority;
l_task_type_id_table(l_task_type_id_table.COUNT+1) := x_updated_tasks(i).task_type_id;
l_move_order_line_id_table(l_move_order_line_id_table.COUNt+1) := x_updated_tasks(i).move_order_line_id;
l_to_lpn_id_table(l_to_lpn_id_table.COUNT+1) := x_updated_tasks(i).to_lpn_id;
l_operation_plan_id_table(l_operation_plan_id_table.COUNT+1) := x_updated_tasks(i).operation_plan_id;
-- l_dispatched_time_table(l_dispatched_time_table.COUNt+1) := x_updated_tasks(i).dispatched_time;
IF x_updated_tasks(i).task_type_id <> 3 then
--for inbound and outbound tasks,update mmtt
l_mmtt_cartonization_id_table(l_mmtt_cartonization_id_table.COUNt+1) := x_updated_tasks(i).cartonization_id;
l_mmtt_transaction_num_table(l_mmtt_transaction_num_table.COUNT+1) := x_updated_tasks(i).transaction_number;
l_mmtt_usertask_type_id_table(l_mmtt_usertask_type_id_table.COUNt+1) := x_updated_tasks(i).user_task_type_id;
l_mmtt_status_id_table(l_mmtt_status_id_table.COUNT+1) := x_updated_tasks(i).status_id;
l_mmtt_priority_table(l_mmtt_priority_table.COUNT+1) := x_updated_tasks(i).priority;
l_mmtt_operation_plan_id_table(l_mmtt_operation_plan_id_table.COUNT+1):= x_updated_tasks(i).operation_plan_id;
l_mcce_transaction_num_table(l_mcce_transaction_num_table.COUNT+1) := x_updated_tasks(i).transaction_number;
l_mcce_usertask_type_id_table(l_mcce_usertask_type_id_table.COUNt+1) := x_updated_tasks(i).user_task_type_id;
l_mcce_priority_table(l_mcce_priority_table.COUNT+1) := x_updated_tasks(i).priority;
--If the x_updated_tasks(i).status_id is 2 ,then update wdt also.
--wdt does not have cartonization_id.
--Also this needs to be done only if wdt was existing before.
IF (x_updated_tasks(i).status_id = 2 AND (nvl(p_new_task_status,-99) <> 2) )THEN
l_wdt_transaction_num_table(l_wdt_transaction_num_table.COUNT+1) := x_updated_tasks(i).transaction_number;
l_wdt_usertask_type_id_table(l_wdt_usertask_type_id_table.COUNt+1) := x_updated_tasks(i).user_task_type_id;
l_wdt_status_id_table(l_wdt_status_id_table.COUNT+1) := x_updated_tasks(i).status_id;
l_wdt_priority_table(l_wdt_priority_table.COUNT+1) := x_updated_tasks(i).priority;
l_wdt_operation_plan_id_table(l_wdt_operation_plan_id_table.COUNT+1) := x_updated_tasks(i).operation_plan_id;
l_wdt_del_trns_num_table(l_wdt_del_trns_num_table.COUNT+1) := x_updated_tasks(i).transaction_number;
INSERT
INTO wms_dispatched_tasks
(
task_id ,
transaction_temp_id ,
organization_id ,
user_task_type ,
person_id ,
effective_start_date,
effective_end_date ,
person_resource_id ,
machine_resource_id ,
status ,
dispatched_time ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
task_type ,
priority ,
move_order_line_id ,
operation_plan_id ,
transfer_lpn_id
)
VALUES
(
wms_dispatched_tasks_s.NEXTVAL ,
l_transaction_num_table(i) ,
l_organization_id_table(i) ,
l_usertask_type_id_table(i) ,
p_person_id ,
sysdate ,
sysdate ,
l_person_resource_id_tabe(i) ,
l_machine_resource_id_table(i) ,
l_status_id_table(i) ,
sysdate ,
sysdate ,
FND_GLOBAL.USER_ID ,
sysdate ,
FND_GLOBAL.USER_ID ,
l_task_type_id_table(i) ,
l_priority_table(i) ,
l_move_order_line_id_table(i) ,
l_operation_plan_id_table(i) ,
l_to_lpn_id_table(i) );
UPDATE mtl_material_transactions_temp
SET wms_task_status = l_mmtt_status_id_table(i) ,
operation_plan_id = l_mmtt_operation_plan_id_table(i),
cartonization_id = l_mmtt_cartonization_id_table(i) ,
task_priority = l_mmtt_priority_table(i) ,
standard_operation_id = l_mmtt_usertask_type_id_table(i)
WHERE transaction_temp_id = l_mmtt_transaction_num_table(i);
UPDATE mtl_cycle_count_entries
SET task_priority = l_mcce_priority_table(i) ,
standard_operation_id = l_mcce_usertask_type_id_table(i)
WHERE cycle_count_entry_id = l_mcce_transaction_num_table(i);
UPDATE wms_dispatched_tasks
SET status = l_wdt_status_id_table(i) ,
operation_plan_id = l_wdt_operation_plan_id_table(i),
priority = l_wdt_priority_table(i) ,
user_task_type = l_wdt_usertask_type_id_table(i)
WHERE transaction_temp_id = l_wdt_transaction_num_table(i);
DELETE from wms_dispatched_tasks
WHERE transaction_temp_id = l_wdt_del_trns_num_table(i);
, x_undeleted_tasks OUT NOCOPY WMS_TASKS_PUB.TASK_TABLE
, x_unprocessed_crossdock_tasks
OUT NOCOPY WMS_TASKS_PUB.TASK_TABLE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2 );
X_unprocessed_crossdockdeleted_tasks
This parameter contains the set of tasks that could not be cancelled.
X_return_status This parameter gives the return status of cancel_task API. 'S' = Success, 'U' = Unexpected Error, 'E' = Error.
X_msg_count This gives the count of messages logged during the task deletion process.
X_msg_data This gives the descrption of the messages that got logged during the task deletion process.
*/
-------------------------------------------------------------------------------------------------------------
PROCEDURE cancel_task(
p_transaction_number IN NUMBER DEFAULT NULL,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_wms_task IN WMS_TASK_MGMT_PUB.task_tab_type,
x_unprocessed_crossdock_tasks OUT NOCOPY WMS_TASK_MGMT_PUB.task_tab_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_val_ret_status VARCHAR2(10) ;