[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
p_update_status_method => NULL,
P_PROGRAM_CONTROL_ARG1 => NULL,
P_PROGRAM_CONTROL_ARG2 => NULL,
P_PROGRAM_CONTROL_ARG3 => NULL,
P_PROGRAM_CONTROL_ARG4 => NULL,
P_PROGRAM_CONTROL_ARG5 => NULL,
P_PROGRAM_CONTROL_ARG6 => NULL
,X_RETURN_STATUS => lX_RETURN_STATUS
,X_MSG_DATA => lX_MSG_DATA
,X_MSG_COUNT => lX_MSG_COUNT
,X_ORGANIZATION_ID => lX_ORGANIZATION_ID
,X_SUBINVENTORY => lX_SUBINVENTORY
,X_SUBINVENTORY_STATUS => lX_SUBINVENTORY_STATUS
,X_LOCATOR => lX_LOCATOR
,X_LOCATOR_STATUS => lX_LOCATOR_STATUS
,X_LPN_ID => lX_LPN_ID
,X_LPN_STATUS => lX_LPN_STATUS
,X_INVENTORY_ITEM_ID => lX_INVENTORY_ITEM_ID
,X_REVISION => lX_REVISION
,X_LOT_NUMBER => lX_LOT_NUMBER
,X_LOT_STATUS => lX_LOT_STATUS
,X_QUANTITY => lX_QUANTITY
,X_UOM_CODE => lX_UOM_CODE
,X_PRIMARY_QUANTITY => lX_PRIMARY_QUANTITY
,X_TRANSACTION_QUANTITY => lX_TRANSACTION_QUANTITY
,X_RESERVATION_ID => lX_RESERVATION_ID
);
p_update_status_method IN VARCHAR2 DEFAULT NULL,
P_PROGRAM_CONTROL_ARG1 IN VARCHAR2 DEFAULT NULL,
P_PROGRAM_CONTROL_ARG2 IN VARCHAR2 DEFAULT NULL,
P_PROGRAM_CONTROL_ARG3 IN VARCHAR2 DEFAULT NULL,
P_PROGRAM_CONTROL_ARG4 IN VARCHAR2 DEFAULT NULL,
P_PROGRAM_CONTROL_ARG5 IN VARCHAR2 DEFAULT NULL,
P_PROGRAM_CONTROL_ARG6 IN VARCHAR2 DEFAULT NULL,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_ORGANIZATION_ID OUT NOCOPY NUMBER,
X_SUBINVENTORY OUT NOCOPY VARCHAR2,
X_SUBINVENTORY_STATUS OUT NOCOPY NUMBER,
X_LOCATOR OUT NOCOPY NUMBER,
X_LOCATOR_STATUS OUT NOCOPY NUMBER,
X_LPN_ID OUT NOCOPY NUMBER,
X_LPN_STATUS OUT NOCOPY NUMBER,
X_INVENTORY_ITEM_ID OUT NOCOPY NUMBER,
X_REVISION OUT NOCOPY VARCHAR2,
X_LOT_NUMBER OUT NOCOPY VARCHAR2,
X_LOT_STATUS OUT NOCOPY NUMBER,
X_QUANTITY OUT NOCOPY NUMBER,
X_UOM_CODE OUT NOCOPY VARCHAR2,
X_PRIMARY_QUANTITY OUT NOCOPY NUMBER,
X_TRANSACTION_QUANTITY OUT NOCOPY NUMBER,
X_RESERVATION_ID OUT NOCOPY NUMBER
)
IS
-- defining input variables and initializing them to null;
L_update_status_method VARCHAR2(250) := NULL;
SELECT reason_name
INTO l_reason_name
FROM mtl_transaction_reasons
WHERE reason_id = p_reason_id;
SELECT task_id
INTO l_task_id
FROM wms_dispatched_tasks
WHERE transaction_temp_id=l_transaction_temp_id;
SELECT subinventory_code, locator_id, transfer_organization,
wms_task_type, lpn_id, content_lpn_id, transfer_lpn_id,
inventory_item_id, revision, lot_number, serial_number,
primary_quantity, item_primary_uom_code,
transaction_quantity, transaction_uom,
transaction_header_id, transaction_action_id, transaction_source_type_id,
transaction_source_id,
reservation_id, move_order_line_id
INTO l_destination_subinventory, l_destination_locator,l_destination_organization_id,
l_task_type_id, l_lpn_id, l_content_lpn_id, l_dest_lpn_id,
l_inventory_item_id, l_revision, l_lot_number, l_serial_number,
l_primary_quantity, l_primary_uom,
l_transaction_quantity, l_transaction_uom,
l_transaction_header_id, l_transaction_action_id, l_transaction_source_type_id,
l_transaction_source,
l_reservation_id, l_mo_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id;
mdebug('after select from mmtt ');
SELECT status_id
INTO l_content_lpn_status
FROM wms_license_plate_numbers
WHERE lpn_id = l_content_lpn_id
AND organization_id = p_source_organization_id;
SELECT license_plate_number, parent_lpn_id, outermost_lpn_id, status_id
INTO l_lpn_n, l_source_parent_lpn_id, l_source_outermost_lpn_id,
l_lpn_status
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id
AND organization_id = p_source_organization_id;
SELECT status_id
INTO l_source_parent_lpn_status
FROM wms_license_plate_numbers
WHERE lpn_id = l_source_parent_lpn_id
AND organization_id = p_source_organization_id;
SELECT status_id
INTO l_source_outermost_lpn_status
FROM wms_license_plate_numbers
WHERE lpn_id = l_source_outermost_lpn_id
AND organization_id = p_source_organization_id;
SELECT parent_lpn_id, outermost_lpn_id, status_id
INTO l_dest_parent_lpn_id, l_dest_outermost_lpn_id,
l_dest_lpn_status
FROM wms_license_plate_numbers
WHERE lpn_id = l_dest_lpn_id
AND organization_id = p_source_organization_id;
SELECT status_id
INTO l_dest_parent_lpn_status
FROM wms_license_plate_numbers
WHERE lpn_id = l_dest_parent_lpn_id
AND organization_id = p_source_organization_id;
SELECT status_id
INTO l_dest_outermost_lpn_status
FROM wms_license_plate_numbers
WHERE lpn_id = l_dest_outermost_lpn_id
AND organization_id = p_source_organization_id;
SELECT status_id
INTO l_source_subinventory_status
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_source_subinventory
AND organization_id = p_source_organization_id;
SELECT status_id
INTO l_destination_subinventory_st
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_destination_subinventory
AND organization_id = l_destination_organization_id;
SELECT status_id
INTO l_source_locator_status
FROM mtl_item_locations
WHERE inventory_location_id = l_source_locator
AND organization_id = p_source_organization_id;
SELECT status_id
INTO l_destination_locator_status
FROM mtl_item_locations
WHERE inventory_location_id = l_destination_locator
AND organization_id = l_destination_organization_id;
SELECT status_id
INTO l_serial_number_status
FROM mtl_serial_numbers
WHERE serial_number = l_serial_number
AND inventory_item_id = l_inventory_item_id;
SELECT status_id
INTO l_lot_status
FROM mtl_lot_numbers
WHERE lot_number = l_lot_number
AND inventory_item_id = l_inventory_item_id
AND organization_id = p_source_organization_id;
SELECT concatenated_segments
INTO l_inventory_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = p_source_organization_id;
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = l_user_id;
select organization_code
INTO l_source_organization_n
from mtl_parameters
where organization_id=p_source_organization_id;
select concatenated_segments
INTO l_source_locator_n
from mtl_item_locations_kfv
where inventory_location_id = l_source_locator
and organization_id = p_source_organization_id;
IF p_update_status_method IS NOT NULL THEN
l_update_status_method := p_update_status_method;
mdebug('L_update_status_method: '||l_update_status_method);
mdebug('Before Select WORKFLOW_NAME, WORKFLOW_PROCESS ');
SELECT WORKFLOW_NAME, WORKFLOW_PROCESS
INTO l_workflow_name, l_workflow_process
FROM MTL_TRANSACTION_REASONS
WHERE REASON_ID = P_REASON_ID ;
SELECT WMS_DISPATCHED_TASKS_S.nextval INTO l_sequence_number FROM DUAL ;
aname => 'PW_UPDATE_STATUS_METHOD',
avalue => L_UPDATE_STATUS_METHOD);
UPDATE wms_exceptions
SET
wf_item_key = l_item_key
WHERE
transaction_header_id = l_transaction_header_id;
mdebug('before select temp id');
SELECT subinventory_code,locator_id, move_order_line_id
INTO lp_sub_code,lp_locator_id, lp_line_num
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=lp_mmtt_id;
SELECT transaction_temp_id
INTO l_transaction_temp_id
FROM wms_dispatched_tasks
WHERE task_id=p_tsk_id;
SELECT inventory_item_id, subinventory_code, transaction_uom, locator_id
INTO l_inventory_item_id, l_subinventory_code, l_transaction_uom, l_locator_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id;
mdebug('After the 2 select statements');
- updates the value from column quantity_detailed
in the table mtl_txn_request_lines
to quantity_detailed less quantity_picked
- updates the values from columns reservation_quantity
and primary_quantity
in the table mtl_material_transaction_temp
to reservation_quantity less quantity_picked and
primary_quantity less quantity picked respectively
- updates reservation_quantity and primary_reservation_quantity
in the table mtl_reservations
to reservation_quantity less quantity_picked
and primary_reservation_quantity less quantity_picked
- creates a new row in the table mtl_reservations. This row
acts as a cycle count request.
where . Note: The only way to check that this row is
created is to query the table with the organization_id,
inventory_item_id and demand_source_header_id=9 (for cycle
count request).
*/
PROCEDURE WMS_Inadequate_Quantity (itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2)
IS
-- local variables
l_workflow_name VARCHAR2(250)
; l_item_key VARCHAR2(250)
SELECT transaction_temp_id
INTO lp_mmtt_id
FROM wms_dispatched_tasks
WHERE task_id=lp_task_id;
SELECT subinventory_code,locator_id, move_order_line_id
INTO lp_sub_code,lp_locator_id, lp_line_num
FROM mtl_material_transactions_temp
WHERE transaction_temp_id=lp_mmtt_id;