[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lot_number,lot_qty,serial_number
FROM wms_device_requests
WHERE relation_id = p_relation_id
AND task_id = p_txn_temp_id
AND business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
AND task_summary = 'N';
SELECT COUNT(*),SUM(lot_qty) INTO l_count_child_rec,l_total_lot_qty
FROM wms_device_requests
WHERE relation_id = p_relation_id
AND task_id = p_txn_temp_id
AND business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
AND task_summary = 'N';
SELECT 1 INTO l_lot_cnt FROM dual WHERE exists
(SELECT lot_number FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_txn_temp_id
AND lot_number = l_child_rec.lot_number );
SELECT TRANSACTION_QUANTITY INTO l_orig_lot_qty
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_txn_temp_id
AND lot_number = l_child_rec.lot_number;
SELECT 1 INTO l_serial_cnt FROM dual WHERE exists
(SELECT fm_serial_number--What if RANGE serials ??
FROM mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_txn_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND msnt.fm_serial_number = l_child_rec.serial_number);
SELECT 1 INTO l_serial_cnt FROM dual WHERE exists
(SELECT fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_txn_temp_id
AND msnt.fm_serial_number = l_child_rec.serial_number);
l_LAST_UPDATE_DATE DATE;
l_last_updated_by NUMBER ;
SELECT relation_id -- all following are passed by WCS
, task_id
, task_summary
, business_event_id
, transaction_quantity
, transfer_sub_code
, transfer_loc_id
, lpn_id
, xfer_lpn_id
, device_status
, reason_id
, organization_id--Its NOT NULL Column
, status_code
, status_msg
, lot_number
, lot_qty
, serial_number
, device_id
FROM wms_device_requests
WHERE business_event_id IN (wms_device_integration_pvt.wms_be_load_confirm,wms_device_integration_pvt.wms_be_task_confirm)
ORDER BY relation_id ASC,task_id ASC, task_summary desc;
SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
SELECT
DEVICE_ID
,responsibility_application_id
,responsibility_id
INTO
l_device_id
,l_resp_application_id
,l_resp_id
FROM wms_device_requests_hist
WHERE request_id = nvl(l_rec.relation_id,-1)
--nvl,to handle ERROR: when WCS does NOT pass relation_id or task_id
AND task_id = nvl(l_rec.task_id,-1)
AND task_summary = 'Y'
AND business_event_id IN (wms_device_integration_pvt.wms_be_pick_release,wms_device_integration_pvt.wms_be_wip_pick_release,wms_device_integration_pvt.wms_be_mo_task_alloc)
AND ROWNUM<2;
INSERT INTO wms_device_requests_hist(request_id
, relation_id -- parent_request_id
, task_id
, business_event_id
, transaction_quantity
, transfer_sub_code
, transfer_loc_id
, lpn_id
, xfer_lpn_id
, device_status
, reason_id
, task_summary
, organization_id
, device_id
, request_date
, requested_by
, status_code
, status_msg
, responsibility_application_id
, responsibility_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, lot_number
, lot_qty
, serial_number
) VALUES (l_request_id
, l_rec.relation_id
, l_rec.task_id
, l_rec.business_event_id
, l_rec.transaction_quantity
, l_rec.transfer_sub_code
, l_rec.transfer_loc_id
, l_rec.lpn_id
, l_rec.xfer_lpn_id
, l_rec.device_status
, l_rec.reason_id
, l_rec.task_summary
, l_rec.organization_id
, l_device_id
, SYSDATE
, fnd_global.USER_ID
, nvl(l_rec.status_code,'E') --Bug#4535546.Added nvl
, l_rec.status_msg
, l_resp_application_id
, l_resp_id
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, l_rec.lot_number
, l_rec.lot_qty
, l_rec.serial_number
);
SELECT mtl_material_transactions_s.nextval
INTO l_new_txn_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
,TRANSFER_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
,ORG_COST_GROUP_ID
,COST_TYPE_ID
,TRANSACTION_STATUS
,STANDARD_OPERATION_ID
,TASK_PRIORITY
,WMS_TASK_TYPE
,PARENT_LINE_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
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,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
,OPERATION_PLAN_ID
,INTRANSIT_ACCOUNT
,FOB_POINT
,MOVE_ORDER_HEADER_ID
,SERIAL_ALLOCATED_FLAG
)
(SELECT
TRANSACTION_HEADER_ID
,l_new_txn_temp_id
,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
,p_txn_qty
,p_pri_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
,TRANSFER_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
,ORG_COST_GROUP_ID
,COST_TYPE_ID
,TRANSACTION_STATUS
,STANDARD_OPERATION_ID
,TASK_PRIORITY
,WMS_TASK_TYPE
,decode(PARENT_LINE_ID, NULL,NULL,l_new_txn_temp_id) -- Take care of BULK parent
,NULL
,NULL
,wms_task_status
,NULL
,NULL
,NULL
,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
,TRANSACTION_BATCH_ID
,TRANSACTION_BATCH_SEQ
,NULL
,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
,OPERATION_PLAN_ID
,INTRANSIT_ACCOUNT
,FOB_POINT
,MOVE_ORDER_HEADER_ID
,SERIAL_ALLOCATED_FLAG
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_txn_temp_id);
fnd_message.set_name('WMS', 'WMS_INSERT_ALLOCATION');
trace('SPLIT_MMTT:INSERTED new MMTT; new temp id : ' ||l_new_txn_temp_id );
PROCEDURE update_wdr_for_error_rec(p_task_id IN NUMBER
,p_relation_id IN NUMBER) IS
l_status_msg VARCHAR2(240);
UPDATE wms_device_requests
SET status_code = 'E',
status_msg = l_status_msg
WHERE business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
AND task_id = p_task_id
AND relation_id = p_relation_id;
END update_wdr_for_error_rec;
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 transaction_header_id
=(SELECT transaction_header_id
from mtl_material_transactions_temp
WHERE transaction_temp_id=p_temp_id)
AND (transfer_lpn_id=p_lpn OR content_lpn_id=p_lpn)
AND cost_group_id = p_cost_group_id);
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 1
INTO l_so_cnt
FROM dual
WHERE exists
(SELECT 1
FROM wms_license_plate_numbers
WHERE lpn_id=p_lpn
AND organization_id=p_org_id
AND lpn_context = 11 --bug 9712391 : changed to check for LPN's that are staged /Check only closed delivery lines
);
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,Nvl(wlc.revision,'-999'))); --bug 2495592
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,Nvl(wlc.revision,'-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;
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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
);
trace('lpn_match: after update qty tree for lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
trace('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
);
trace('lpn_match: after update qty tree without lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree back without lpn 1st time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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
);
trace('lpn_match: after update qty tree back for lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
trace('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
);
trace('lpn_match: after update qty tree back without lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
trace('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;
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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
);
trace('lpn_match: update qty tree with lpn 2nd time: l_att:' || l_att);
trace('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
);
trace('lpn_match: update qty tree without lpn 2nd time:l_att:'||l_att);
trace('lpn_match: calling update qty tree back without lpn 2nd time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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
);
trace('lpn_match: update qty tree back with lpn 2nd time: l_att:' || l_att);
trace('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
);
trace('lpn_match: update qty tree back without lpn 2nd time:l_att:'||l_att);
trace('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')
);
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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
);
trace('lpn_match: update qty tree 3rd time for lpn l_att:'||l_att||' for lot:'||l_mtlt_lot_number);
trace('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
);
trace('lpn_match: after update without lpn 3rd time l_att:'|| l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree back 3rd time without lpn 3rd time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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
);
trace('lpn_match: update qty tree back 3rd time for lpn l_att:'||l_att||' for lot:'||l_mtlt_lot_number);
trace('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
);
trace('lpn_match: after update qty tree back without lpn 3rd time l_att:'|| l_att||' for lot:'||l_mtlt_lot_number);
trace('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')
);
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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
);
trace('lpn_match: update qty tree with lpn 4th time: l_att:' || l_att);
trace('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
);
trace('lpn_match: update qty tree without lpn 4th time:l_att:'||l_att);
trace('lpn_match: calling update qty tree without lpn 4th time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
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
);
trace('lpn_match: update qty tree back with lpn 4th time: l_att:' || l_att);
trace('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
);
trace('lpn_match: update qty tree back without lpn 4th time:l_att:'||l_att);
trace('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;
l_last_updated_by NUMBER;
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_txn_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 = l_txn_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT relation_id -- parent_request_id
, task_id
, task_summary
, business_event_id
, Nvl(transaction_quantity,0) transaction_quantity -- Modified for Bug#8721738
, transfer_sub_code
, transfer_loc_id
, lpn_id
, xfer_lpn_id
, device_status
, reason_id
, organization_id--Its NOT NULL Column
FROM wms_device_requests
WHERE business_event_id IN
(wms_device_integration_pvt.wms_be_task_confirm, wms_device_integration_pvt.wms_be_load_confirm)
AND device_status = 'S'
AND task_summary = 'Y'
ORDER BY xfer_lpn_id;
CURSOR c_mtlt_update (p_relation_id NUMBER, p_temp_id NUMBER) IS
SELECT lot_number,lot_qty FROM wms_device_requests
WHERE relation_id = p_relation_id
AND task_id = p_temp_id
AND task_summary = 'N'
AND business_event_id IN
(wms_device_integration_pvt.wms_be_task_confirm,
wms_device_integration_pvt.wms_be_load_confirm);
CURSOR c_update_xfer_lpns_context IS
SELECT wlpn.lpn_id,organization_id FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_context <> wms_container_pub.lpn_context_pregenerated
--to avoid LPNS that have been unpacked by TM for non-LPN ctrld sub
AND wlpn.lpn_id IN
(SELECT wdr.xfer_lpn_id
FROM wms_device_requests wdr,
wms_device_requests_hist wdrh,
wms_dispatched_tasks wdt
WHERE wdr.business_event_id = wms_device_integration_pvt.WMS_BE_TASK_CONFIRM
AND wdr.task_id = wdt.transaction_temp_id
AND wdt.task_type IN (4,5,7) -- ONLY for Replenishment, MO Xfer,Staging TASKS
AND wdr.status_code = 'S'
AND wdr.device_status = 'S'
AND wdr.task_summary = 'Y'
AND wdr.task_summary = wdrh.task_summary
and wdrh.request_id = wdr.relation_id
AND wdr.task_id = wdrh.task_id
AND wdr.transaction_quantity > 0
AND wdrh.business_event_id IN (wms_device_integration_pvt.wms_be_pick_release,wms_device_integration_pvt.wms_be_wip_pick_release,wms_device_integration_pvt.wms_be_mo_task_alloc)
AND wdrh.TASK_TYPE_ID = 1);
SELECT transaction_temp_id, organization_id, transfer_lpn_id, content_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = l_txn_hdr_id;
CURSOR c_open_period_check IS SELECT distinct organization_id, task_id
FROM wms_device_requests
WHERE business_event_id = wms_device_integration_pvt.WMS_BE_TASK_CONFIRM
AND status_code = 'S'
AND device_status = 'S'
AND task_summary = 'Y';
SELECT mtl_material_transactions_s.NEXTVAL INTO l_txn_hdr_id FROM DUAL;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT task_id,lpn_id INTO l_txn_temp_id,l_orig_lpn_id
FROM wms_device_requests_hist
WHERE request_id = l_rec.relation_id
AND task_id = l_rec.task_id
AND business_event_id IN (wms_device_integration_pvt.wms_be_pick_release,wms_device_integration_pvt.wms_be_wip_pick_release,wms_device_integration_pvt.wms_be_mo_task_alloc)
AND ROWNUM <2;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT
transaction_header_id,
inventory_item_id,
move_order_line_id,
primary_quantity,
transaction_quantity,
transfer_subinventory,
transfer_to_location,
revision,
transaction_source_type_id,
transaction_action_id,
subinventory_code,
locator_id,
last_updated_by,
transaction_uom,
transaction_type_id,
cost_group_id
INTO
l_orig_txn_hdr_id,
l_inventory_item_id,
l_move_order_line_id,
l_pr_qty,
l_mmtt_txn_qty,
l_xfer_sub_code,
l_xfer_loc_id,
l_rev,
l_tran_source_type_id,
l_tran_action_id,
l_sub_code,
l_loc_id,
l_last_updated_by,
l_transaction_uom,
l_tran_type_id,
l_cost_group_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_txn_temp_id;
--update wdr for error_code and mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT
msi.lpn_controlled_flag
INTO
l_lpn_controlled_flag
FROM
mtl_secondary_inventories msi
WHERE msi.organization_id = l_rec.organization_id
AND msi.secondary_inventory_name = l_rec.transfer_sub_code
AND sysdate <= nvl(msi.disable_date,sysdate);
--update wdr for error_code and mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT 1 INTO l_count FROM DUAL WHERE exists
( SELECT 1
FROM mtl_item_locations_kfv
WHERE organization_id = l_rec.organization_id
AND inventory_location_id = l_rec.transfer_loc_id
AND sysdate < nvl(disable_date,sysdate+1)
);
--update wdr for error_code and mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT lpn_context
INTO l_pick_lpn_context
FROM wms_license_plate_numbers WHERE
lpn_id = l_rec.lpn_id
AND organization_id = l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT lpn_context,license_plate_number
INTO l_lpn_context, l_xfer_lpn
FROM wms_license_plate_numbers WHERE
lpn_id = l_rec.xfer_lpn_id
AND organization_id = l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT lot_control_code,serial_number_control_code,primary_uom_code
INTO l_lot_code,l_serial_code,l_primary_uom
FROM mtl_system_items
WHERE organization_id = l_rec.organization_id
AND inventory_item_id = l_inventory_item_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT bremp.resource_id role_id
, t.wms_task_type
, t.standard_operation_id
, t.operation_plan_id
INTO l_per_res_id
, l_wms_task_type
, l_std_op_id
, l_operation_plan_id
FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
WHERE t.transaction_temp_id = l_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;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT employee_id INTO l_person_id
FROM fnd_user WHERE user_id = fnd_global.user_id;
SELECT count(*) into l_wdt_count from wms_dispatched_tasks where transaction_temp_id = l_rec.task_id;
trace('Inserting the WDT as there is no existing wdt record'||l_wdt_count); --BUG 11841884
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
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, operation_plan_id
, move_order_line_id
)
VALUES (
wms_dispatched_tasks_s.NEXTVAL
, l_rec.task_id --transaction_temp_id
, l_rec.organization_id
, NVL(l_std_op_id, 2)
, l_person_id
, SYSDATE
, SYSDATE
, l_per_res_id
, l_g_task_active
, SYSDATE
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, l_wms_task_type
, l_operation_plan_id
, l_move_order_line_id
);
SELECT 1 INTO l_wf
FROM MTL_TRANSACTION_REASONS
WHERE reason_id=l_rec.reason_id
AND workflow_name is not null
AND workflow_name<>' '
AND workflow_process is not null
AND workflow_process<>' ';
--Update the request_id for child records if they exist.
IF (l_lot_code >1 OR ( l_serial_code > 1 AND l_serial_code <> 6 ) ) THEN--LOT OR/AND SERIAL ITEMS -- 14028129
IF (l_debug = 1) THEN
trace('device_confirmation:validating lot/serial substitution');
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET content_lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
-- Later update LPN context For this case of picked_from_LPN to Packing
-- context AS it will be entirely nested
ELSIF l_lpn_match=2 THEN
if l_rec.lpn_id = l_rec.xfer_lpn_id then
--Error
IF (l_debug = 1) THEN
trace('error out,Can not move the entire LPN');
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--Update MTLT/MSNT
IF (l_qty_discrepancy_flag <> 0) AND l_rec.transaction_quantity <> 0 THEN --means qty_discrepancy
--we do not want to error out in case qty_picked is 0 and
--lot/serial info in child record is NOT provided
IF (l_lot_code >1 OR ( l_serial_code >1 AND l_serial_code <> 6 ) ) THEN --LOT OR/AND SERIAL ITEMS -- 14028129
IF (l_debug = 1) THEN
trace('device_confirmation:validating lot/serial substitution');
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT COUNT(*)
INTO l_mmtt_count
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id <> l_rec.task_id
AND mmtt.move_order_line_id = l_move_order_line_id;
SELECT mtrl.txn_source_line_id
INTO l_source_line_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_move_order_line_id;
trace('Other MMTT lines exist too. Delete MMTT and UPDATE move ORDER line');
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_txn_temp_id;
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 = l_txn_temp_id);
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_txn_temp_id;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_txn_temp_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_pr_qty-- this diff is zero
WHERE line_id = l_move_order_line_id; -- 14028129*/
--Bug#7634524.Delete the WDT we just inserted.
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_rec.task_id
AND person_id = l_person_id ; --Bug 6987801 -- 14028129
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_txn_temp_id;
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 = l_txn_temp_id);
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_txn_temp_id;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_txn_temp_id;
-- Update the context to whatever it started with
-- before processing the record with zero qty
--
/* updated as part of the bug 4411819
--using wms_container_pvt.Modify_LPN() API instead
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context -- this still has
--the original value of lpn context
WHERE lpn_id = l_rec.xfer_lpn_id;
-- Clean up code. Have to delete MMTT, MTLT, MSNT, WDT, if picked less
-- and update move order line
IF (l_debug = 1) THEN
trace('Deleteing all unpicked lot/serials from MTLT/MSNT');
--delete all unpicked lot/serials from MTLT/MSNT
IF l_lot_code >1 THEN
IF (l_serial_code >1 AND l_serial_code<>6) THEN
DELETE FROM mtl_serial_numbers_temp msnt
WHERE transaction_temp_id IN
(SELECT msnt.transaction_temp_id
FROM mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
WHERE mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
AND mtlt.transaction_temp_id =l_txn_temp_id)
AND msnt.fm_serial_number NOT IN
(SELECT serial_number FROM wms_device_requests
WHERE relation_id = l_rec.relation_id
AND task_id = l_txn_temp_id
AND business_event_id = wms_device_integration_pvt.WMS_BE_TASK_CONFIRM
AND task_summary = 'N');
--Update qty in MTLT by qty passed in the child
-- records for corresponding lots
--In the child record, txn quantity is total
--quantity of the parent record. It is the
--lot_qty column which keeps correct lot qty for child record.
for l_mtlt_update in c_mtlt_update(l_rec.relation_id,l_txn_temp_id)
loop
l_mtlt_pr_qty :=l_mtlt_update.lot_qty;
from_quantity => l_mtlt_update.lot_qty,
from_unit => l_transaction_uom,
to_unit => l_primary_uom,
from_name => null,
to_name => null);
trace('l_mtlt_pr_qty::'||l_mtlt_pr_qty||'mtlt_lot_TXN_QTY:::'||l_mtlt_update.lot_qty);
update mtl_transaction_lots_temp set
TRANSACTION_QUANTITY = l_mtlt_update.lot_qty,
PRIMARY_QUANTITY = l_mtlt_pr_qty
WHERE transaction_temp_id = l_txn_temp_id
AND lot_number = l_mtlt_update.lot_number;
/*9908576-It is possible that some lots are totally missing. Let's delete them from MTLT*/
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_txn_temp_id
AND NOT EXISTS (
SELECT 1 FROM wms_device_requests wdr
WHERE wdr.relation_id = l_rec.relation_id
AND wdr.task_id = l_txn_temp_id
AND wdr.business_event_id in ( wms_device_integration_pvt.WMS_BE_TASK_CONFIRM,wms_device_integration_pvt.WMS_BE_LOAD_CONFIRM)
AND wdr.task_summary = 'N'
AND wdr.task_id = mtlt.transaction_temp_id
AND wdr.lot_number = mtlt.lot_number
);
trace ('Deleted '||SQL%ROWCOUNT||' records from MTLT');
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_txn_temp_id
AND msnt.fm_serial_number NOT IN
( SELECT wdr.serial_number FROM
wms_device_requests wdr
WHERE relation_id = l_rec.relation_id
AND task_id = l_txn_temp_id
AND task_summary = 'N'
AND business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.WMS_BE_load_CONFIRM));
UPDATE mtl_txn_request_lines
SET quantity_detailed = l_rec.transaction_quantity
WHERE line_id = l_move_order_line_id; -- 14028129*/
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET content_lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET content_lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update MMTT for qty/loc/sub disc
ll_pr_qty := l_pr_qty ;
select dual_uom_control,secondary_uom_code
into l_dual_uom_control,l_secondary_uom
FROM mtl_system_items_b
where inventory_item_id = l_inventory_item_id
and organization_id = l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET transfer_subinventory = ll_xfer_sub_code
, transfer_to_location = ll_xfer_loc_id
, primary_quantity = ll_pr_qty
, secondary_transaction_quantity = ll_sec_qty
, transaction_quantity = ll_mmtt_txn_qty
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id = l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET transfer_subinventory = ll_xfer_sub_code
, transfer_to_location = ll_xfer_loc_id
, primary_quantity = ll_pr_qty
, transaction_quantity = ll_mmtt_txn_qty
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id = l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT 1
INTO l_wf
FROM MTL_TRANSACTION_REASONS
WHERE reason_id=l_rec.reason_id
and workflow_name is not null
and workflow_name<>' '
and workflow_process is not null
and workflow_process<>' ';
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_txn_request_lines
SET quantity_detailed = l_rec.transaction_quantity
WHERE line_id = l_move_order_line_id;
DELETE FROM wms_dispatched_tasks WHERE transaction_temp_id = l_new_txn_temp_id ;
trace ('NO WDT TO DELETE for temp_id : '||l_new_txn_temp_id );
trace('Calling INV_TRX_UTIL_PUB.delete_transaction ' );
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_new_txn_temp_id
, p_update_parent => FALSE
);
UPDATE mtl_material_transactions_temp
SET transaction_date = sysdate
,transaction_status = 3
,transaction_header_id = l_txn_hdr_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id
AND l_rec.business_event_id <> wms_device_integration_pvt.wms_be_load_confirm;
UPDATE wms_dispatched_tasks
SET status = l_g_task_loaded
,last_update_date = Sysdate
,loaded_time = Sysdate
,last_updated_by = fnd_global.user_id
WHERE transaction_temp_id = l_rec.task_id;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE wms_device_requests
SET status_code = 'S',
status_msg = null
WHERE business_event_id in (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
AND task_id = l_rec.task_id
AND relation_id = l_rec.relation_id;
trace('Need to update the account period in MMTT');
UPDATE mtl_material_transactions_temp
SET acct_period_id = l_period_id
WHERE transaction_temp_id = l_open_period_check.task_id
AND organization_id = l_open_period_check.organization_id;
trace('Done with all records: Insert WDT History ONLY for LPNs to be DROPPED');
UPDATE mtl_material_transactions_temp mmtt
set transfer_lpn_id = NULL , lpn_id = NULL
WHERE mmtt.transaction_temp_id = l_mmtt_csr.transaction_temp_id;
, p_delete_mmtt_flag => 'N'
, p_txn_header_id => l_txn_hdr_id
, p_transfer_lpn_id => NVL(l_mmtt_csr.transfer_lpn_id, l_mmtt_csr.content_lpn_id)
);
--Step6 Update LPN Context apporpriately for processed LPNs
--For droped LPN tasks ONLY:
-- Replenishment task = (reside IN inv) wms_container_pub.lpn_context_inv
-- so taks = Handled by TM (Do NOT do anything)
-- All successful LPNs are already Loaded in the loop above for each call
--Update the LPN context to Reside in INV for all LPNs for Replenishment
--tasks. For SO tasks, it is handled in the TM
-- the cursor c_update_xfer_lpns_context ensure that LPNs for
-- replenishment tasks are here only
FOR l_update_xfer_lpns_context IN c_update_xfer_lpns_context loop
--In case LPN is getting transferred, both lpn_id and
--xfer_lpn_id are same, so getting updated correctly
--as part of the bug 4411819, replaced
--wms_container_pub.modify_lpn_wrapper to wms_container_pvt.Modify_LPN
l_lpn.lpn_id := l_update_xfer_lpns_context.lpn_id;
l_lpn.organization_id := l_update_xfer_lpns_context.organization_id;
trace(' device_confirmation:Delete requested rows from WDR');
delete from wms_device_requests;--since temp table is session specific
UPDATE wms_device_requests_hist
SET status_code = 'E',resubmit_date = sysdate,
status_msg= 'g_expected_error'
WHERE request_id = l_new_request_id
AND BUSINESS_EVENT_ID IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm);
UPDATE wms_device_requests_hist
SET status_code = 'E',resubmit_date = Sysdate,
status_msg='g_unexpected_error'
WHERE request_id = l_new_request_id
AND BUSINESS_EVENT_ID IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm);