[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_action_code NUMBER := 0; -- for insert
SELECT mtrl.header_id
,mtrl.line_id
,mtrl.inventory_item_id
,mtrl.from_subinventory_code
,mtrl.to_subinventory_code
,mtrl.date_required
,mtrl.created_by
,mtrh.move_order_type
,mtrl.quantity_detailed
,mtrl.quantity
FROM mtl_item_locations_kfv milk,
mtl_system_items_b_kfv msibk,
csp_moveorder_lines cmol,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrl.line_status in (3,7)
AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
)
AND mtrl.organization_id = p_org_id
and mtrh.header_id = mtrl.header_id
--AND nvl(quantity_detailed, 0) < quantity
AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
AND milk.inventory_location_id(+) = mtrl.from_locator_id
AND milk.organization_id(+) = mtrl.organization_id
AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
AND msibk.organization_id(+) = mtrl.organization_id
AND cmol.line_id = mtrl.line_id
ORDER BY mtrl.header_id, mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
SELECT mtrl.header_id
,mtrl.line_id
,mtrl.inventory_item_id
,mtrl.from_subinventory_code
,mtrl.to_subinventory_code
,mtrl.date_required
,mtrl.created_by
,mtrh.move_order_type
,mtrl.quantity_detailed
,mtrl.quantity
FROM mtl_item_locations_kfv milk,
mtl_system_items_b_kfv msibk,
csp_moveorder_lines cmol,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrl.line_status in (3,7)
AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
)
AND mtrl.organization_id = p_org_id
and mtrh.header_id = mtrl.header_id
--AND nvl(quantity_detailed, 0) < quantity
AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
AND milk.inventory_location_id(+) = mtrl.from_locator_id
AND milk.organization_id(+) = mtrl.organization_id
AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
AND msibk.organization_id(+) = mtrl.organization_id
AND cmol.line_id = mtrl.line_id
ORDER BY mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
SELECT mtrl.header_id
,mtrl.line_id
,mtrl.inventory_item_id
,mtrl.from_subinventory_code
,mtrl.to_subinventory_code
,mtrl.date_required
,mtrl.created_by
,mtrh.move_order_type
,mtrl.quantity_detailed
,mtrl.quantity
FROM mtl_item_locations_kfv milk,
mtl_system_items_b_kfv msibk,
csp_moveorder_lines cmol,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrl.line_status in (3,7)
AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
)
AND mtrl.organization_id = p_org_id
and mtrh.header_id = mtrl.header_id
--AND nvl(quantity_detailed, 0) < quantity
AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
AND milk.inventory_location_id(+) = mtrl.from_locator_id
AND milk.organization_id(+) = mtrl.organization_id
AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
AND msibk.organization_id(+) = mtrl.organization_id
AND cmol.line_id = mtrl.line_id
ORDER BY mtrl.to_subinventory_code, mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
SELECT mtrl.header_id
,mtrl.line_id
,mtrl.inventory_item_id
,mtrl.from_subinventory_code
,mtrl.to_subinventory_code
,mtrl.date_required
,mtrl.created_by
,mtrh.move_order_type
,mtrl.quantity_detailed
,mtrl.quantity
FROM mtl_item_locations_kfv milk,
mtl_system_items_b_kfv msibk,
csp_moveorder_lines cmol,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrl.line_status in (3,7)
AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
)
AND mtrl.organization_id = p_org_id
and mtrh.header_id = mtrl.header_id
--AND nvl(quantity_detailed, 0) < quantity
AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
AND milk.inventory_location_id(+) = mtrl.from_locator_id
AND milk.organization_id(+) = mtrl.organization_id
AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
AND msibk.organization_id(+) = mtrl.organization_id
AND cmol.line_id = mtrl.line_id
ORDER BY mtrl.date_Required, mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
SELECT mtrl.header_id
,mtrl.line_id
,mtrl.inventory_item_id
,mtrl.from_subinventory_code
,mtrl.to_subinventory_code
,mtrl.date_required
,mtrl.created_by
,mtrh.move_order_type
,mtrl.quantity_detailed
,mtrl.quantity
FROM mtl_item_locations_kfv milk,
mtl_system_items_b_kfv msibk,
csp_moveorder_lines cmol,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrl.line_status in (3,7)
AND mtrl.transaction_type_id = 64 -- Subinventory Transfer = 64, Account Transfer = 63
AND ( (p_move_order_type = 1 and mtrh.move_order_type = 1) -- Move Order Requistion
OR (p_move_order_type = 2 and mtrh.move_order_type = 2) -- Replenishment Move Orders
OR (p_move_order_type = 3 and mtrh.move_order_type in (1,2)) -- Replenishment and Requisition move orders
)
AND mtrl.organization_id = p_org_id
and mtrh.header_id = mtrl.header_id
--AND nvl(quantity_detailed, 0) < quantity
AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
AND milk.inventory_location_id(+) = mtrl.from_locator_id
AND milk.organization_id(+) = mtrl.organization_id
AND msibk.inventory_item_id(+) = mtrl.inventory_item_id
AND msibk.organization_id(+) = mtrl.organization_id
AND cmol.line_id = mtrl.line_id
ORDER BY mtrl.created_by, mtrl.from_subinventory_code, milk.concatenated_segments, msibk.concatenated_segments;
SELECT transaction_temp_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,move_order_line_id
,inventory_item_id
,revision
,transaction_quantity
,transaction_uom
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_line_id
--AND transfer_subinventory = decode(l_to_sub, null, transfer_subinventory, l_to_sub)
--AND subinventory_code = decode(l_from_sub, null, subinventory_code, l_from_sub)
AND transaction_type_id = 64
AND organization_id = p_org_id;
SELECT mtrl.header_id
,mtrl.line_id
FROM mtl_txn_request_headers mtrh
,mtl_txn_request_lines mtrl
WHERE mtrl.header_id = mtrh.header_id
AND mtrl.line_status = 7
AND mtrh.move_order_type = 2
AND mtrl.from_subinventory_code = nvl(p_from_subinventory, mtrl.from_subinventory_code)
AND mtrl.to_subinventory_code = nvl(p_to_subinventory, mtrl.to_subinventory_code)
AND mtrl.date_required = nvl(p_date_required, mtrl.date_required)
AND mtrl.created_by = nvl(p_created_by, mtrl.created_by)
AND mtrl.header_id = nvl(p_move_order_header_id, mtrl.header_id)
AND mtrl.organization_id = mtrh.organization_id
AND mtrh.organization_id = p_org_id
ORDER BY mtrl.header_id, mtrl.line_id;
SELECT Sysdate INTO l_today FROM dual;
SELECT line_id
INTO l_replen_line_id
FROM CSP_MOVEORDER_LINES
WHERE line_id = mo_replen_rec.line_id;
SELECT header_id
INTO l_replen_header_id
FROM CSP_MOVEORDER_HEADERS
WHERE HEADER_ID = mo_replen_rec.header_id;
p_action_code => l_action_code,/* 0 = insert, 1 = update, 2 = delete */
p_header_id => mo_replen_rec.header_id,
p_created_by => l_user_id,
p_CREATION_DATE => l_today,
p_LAST_UPDATED_BY => l_user_id,
p_LAST_UPDATE_DATE => l_today,
p_LAST_UPDATE_LOGIN => l_login_id,
p_carrier => null,
p_shipment_method => null,
p_autoreceipt_flag => 'Y',
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_location_id => null,
p_party_site_id => null,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
p_LAST_UPDATED_BY => l_user_id,
p_LAST_UPDATE_DATE => l_today,
p_LAST_UPDATED_LOGIN => l_login_id,
p_HEADER_ID => mo_replen_rec.header_id,
p_CUSTOMER_PO => null,
p_INCIDENT_ID => null,
p_TASK_ID => null,
p_TASK_ASSIGNMENT_ID => null,
p_COMMENTS => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
SELECT mtl_material_transactions_s.nextval
INTO l_txn_header_id
FROM dual;
SELECT serial_number_control_code into l_serial_control
FROM mtl_system_items
WHERE inventory_item_id = mo_line_rec.inventory_item_id
AND organization_id = p_org_id;
l_trolin_rec.last_update_date := SYSDATE;
l_trolin_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_trolin_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
INV_Trolin_Util.Update_Row(l_trolin_rec);
/* update mtl_txn_request_lines
set quantity_detailed = l_detailed_qty
where line_id = mo_line_rec.line_id; */
SELECT count(1)
INTO l_cpll_rows
FROM csp_picklist_lines
WHERE transaction_temp_id = txn_temp_rec.transaction_temp_id;
SELECT csp_picklist_headers_s1.nextval
INTO l_picklist_header_id
FROM dual;
p_LAST_UPDATED_BY => l_user_id,
p_LAST_UPDATE_DATE => l_today,
p_LAST_UPDATE_LOGIN => l_login_id,
p_ORGANIZATION_ID => p_org_id,
p_PICKLIST_NUMBER => l_picklist_header_id,
p_PICKLIST_STATUS => 1, -- open
p_DATE_CREATED => l_today,
p_DATE_CONFIRMED => null,
p_ATTRIBUTE_CATEGORY => null,
p_ATTRIBUTE1 => null,
p_ATTRIBUTE2 => null,
p_ATTRIBUTE3 => null,
p_ATTRIBUTE4 => null,
p_ATTRIBUTE5 => null,
p_ATTRIBUTE6 => null,
p_ATTRIBUTE7 => null,
p_ATTRIBUTE8 => null,
p_ATTRIBUTE9 => null,
p_ATTRIBUTE10 => null,
p_ATTRIBUTE11 => null,
p_ATTRIBUTE12 => null,
p_ATTRIBUTE13 => null,
p_ATTRIBUTE14 => null,
p_ATTRIBUTE15 => null,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
SELECT csp_picklist_lines_s1.nextval
INTO l_picklist_line_id
FROM dual;
,p_action_code => l_action_code /* 0 = insert, 1 = update, 2 = delete */
,px_PICKLIST_LINE_ID => l_picklist_line_id
,p_CREATED_BY => txn_temp_rec.created_by
,p_CREATION_DATE => txn_temp_rec.creation_date
,p_LAST_UPDATED_BY => txn_temp_rec.last_updated_by
,p_LAST_UPDATE_DATE => txn_temp_rec.last_update_date
,p_LAST_UPDATE_LOGIN => txn_temp_rec.last_update_login
,p_PICKLIST_LINE_NUMBER => l_line_number
,p_PICKLIST_HEADER_ID => l_picklist_header_id
,p_LINE_ID => mo_line_rec.line_id
,p_INVENTORY_ITEM_ID => txn_temp_rec.inventory_item_id
,p_UOM_CODE => txn_temp_rec.transaction_uom
,p_REVISION => txn_temp_rec.revision
,p_QUANTITY_PICKED => txn_temp_rec.transaction_quantity
,p_TRANSACTION_TEMP_ID => txn_temp_rec.transaction_temp_id
,p_ATTRIBUTE_CATEGORY => null
,p_ATTRIBUTE1 => null
,p_ATTRIBUTE2 => null
,p_ATTRIBUTE3 => null
,p_ATTRIBUTE4 => null
,p_ATTRIBUTE5 => null
,p_ATTRIBUTE6 => null
,p_ATTRIBUTE7 => null
,p_ATTRIBUTE8 => null
,p_ATTRIBUTE9 => null
,p_ATTRIBUTE10 => null
,p_ATTRIBUTE11 => null
,p_ATTRIBUTE12 => null
,p_ATTRIBUTE13 => null
,p_ATTRIBUTE14 => null
,p_ATTRIBUTE15 => null
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT picklist_line_id, picklist_header_id, line_id, inventory_item_id,
quantity_picked, transaction_temp_id
FROM csp_picklist_lines
WHERE picklist_header_id = p_picklist_header_id;
SELECT item_serial_control_code, item_lot_control_code
FROM mtl_material_transactions_temp
WHERE organization_id = p_organization_id
AND transaction_temp_id = l_transaction_temp_id;
SELECT transaction_temp_id, serial_transaction_temp_id, lot_number,
transaction_quantity, primary_quantity
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT transaction_temp_id, fm_serial_number, to_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_temp_id_ref;
SELECT * FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = l_temp_id_ref;
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_PICKLIST_LINE_ID => l_picklist_line_rec.picklist_line_id,
p_ORGANIZATION_ID => p_organization_id,
p_INVENTORY_ITEM_ID => l_picklist_line_rec.inventory_item_id,
p_QUANTITY => 1,
p_LOT_NUMBER => l_lot_number_rec.lot_number,
p_SERIAL_NUMBER => l_msnt_tbl(l_tbl_index).fm_serial_number,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_PICKLIST_LINE_ID => l_picklist_line_rec.picklist_line_id,
p_ORGANIZATION_ID => p_organization_id,
p_INVENTORY_ITEM_ID => l_picklist_line_rec.inventory_item_id,
p_QUANTITY => 1,
p_LOT_NUMBER => l_lot_number_rec.lot_number,
p_SERIAL_NUMBER => l_fm_prefix||lpad(to_char(l_index),l_number_length, '0'),
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data );
l_msnt_tbl(l_tbl_index).last_update_date := sysdate;
csp_pp_util.insert_msnt(
x_return_status => l_return_status
,p_msnt_tbl => l_msnt_tbl
,p_msnt_tbl_size => 1
);
delete from mtl_serial_numbers_temp
where transaction_temp_id = l_temp_id_to_del
and fm_serial_number = l_fm_serial_to_del
and to_serial_number = nvl(l_to_serial_to_del, to_serial_number);
select organization_id into l_check_existence
from mtl_parameters
where organization_id = p_organization_id;
SELECT picklist_header_id INTO l_check_existence
FROM csp_picklist_headers
WHERE organization_id = p_organization_id
AND picklist_header_id = p_picklist_header_id;
Update_Misc_MMTT (
P_Api_Version_Number => p_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => fnd_api.g_false,
p_validation_level => FND_API.G_VALID_LEVEL_NONE,
p_transaction_temp_id => l_picklist_line_rec.transaction_temp_id,
p_organization_id => p_organization_id,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data );
p_LAST_UPDATED_BY => G_USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATE_LOGIN => G_LOGIN_ID,
p_PICKLIST_LINE_ID => l_picklist_line_rec.picklist_line_id,
p_ORGANIZATION_ID => p_organization_id,
p_INVENTORY_ITEM_ID => l_picklist_line_rec.inventory_item_id,
p_QUANTITY => l_lot_number_rec.transaction_quantity,
p_LOT_NUMBER => l_lot_number_rec.lot_number,
p_SERIAL_NUMBER => null,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data
);
Procedure Update_Misc_MMTT (
-- Start of Comments
-- Procedure : Update_Misc_MMTT
-- Purpose : This procedure updates the transaction source type, transaction type, and transaction action of the
-- a mmtt temp table to 13 (Inventory), 2 (Inventory sub transfer) and 2 (Subinventory transfer), respectively.
-- This procedure also updates the item_lot_control_code and the item_serial_control_code to that in the
-- mtl_system_items table.
--
-- History :
-- UserID Date Comments
-- ----------- -------- --------------------------
-- klou 04/25/00 Created.
--
-- NOTES:
--
--End of Comments
P_Api_Version_Number IN NUMBER
,P_Init_Msg_List IN VARCHAR2
,P_Commit IN VARCHAR2
,p_validation_level IN NUMBER
,p_transaction_temp_id IN NUMBER
,p_organization_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(50) := 'Update_Misc_MMTT';
SELECT TRANSACTION_HEADER_ID ,
TRANSACTION_TEMP_ID ,
SOURCE_CODE ,
SOURCE_LINE_ID ,
TRANSACTION_MODE ,
LOCK_FLAG ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
INVENTORY_ITEM_ID ,
REVISION ,
ORGANIZATION_ID ,
SUBINVENTORY_CODE ,
LOCATOR_ID ,
TRANSACTION_QUANTITY ,
PRIMARY_QUANTITY ,
TRANSACTION_UOM ,
TRANSACTION_COST ,
TRANSACTION_TYPE_ID ,
TRANSACTION_ACTION_ID ,
TRANSACTION_SOURCE_TYPE_ID ,
TRANSACTION_SOURCE_ID ,
TRANSACTION_SOURCE_NAME ,
TRANSACTION_DATE ,
ACCT_PERIOD_ID ,
DISTRIBUTION_ACCOUNT_ID ,
TRANSACTION_REFERENCE ,
REQUISITION_LINE_ID ,
REQUISITION_DISTRIBUTION_ID ,
REASON_ID ,
LOT_NUMBER ,
LOT_EXPIRATION_DATE ,
SERIAL_NUMBER ,
RECEIVING_DOCUMENT ,
DEMAND_ID ,
RCV_TRANSACTION_ID ,
MOVE_TRANSACTION_ID ,
COMPLETION_TRANSACTION_ID ,
WIP_ENTITY_TYPE ,
SCHEDULE_ID ,
REPETITIVE_LINE_ID ,
EMPLOYEE_CODE ,
PRIMARY_SWITCH ,
SCHEDULE_UPDATE_CODE ,
SETUP_TEARDOWN_CODE ,
ITEM_ORDERING ,
NEGATIVE_REQ_FLAG ,
OPERATION_SEQ_NUM ,
PICKING_LINE_ID ,
TRX_SOURCE_LINE_ID ,
TRX_SOURCE_DELIVERY_ID ,
PHYSICAL_ADJUSTMENT_ID ,
CYCLE_COUNT_ID ,
RMA_LINE_ID ,
CUSTOMER_SHIP_ID ,
CURRENCY_CODE ,
CURRENCY_CONVERSION_RATE ,
CURRENCY_CONVERSION_TYPE ,
CURRENCY_CONVERSION_DATE ,
USSGL_TRANSACTION_CODE ,
VENDOR_LOT_NUMBER ,
ENCUMBRANCE_ACCOUNT ,
ENCUMBRANCE_AMOUNT ,
SHIP_TO_LOCATION ,
SHIPMENT_NUMBER ,
TRANSFER_COST ,
TRANSPORTATION_COST ,
TRANSPORTATION_ACCOUNT ,
FREIGHT_CODE ,
CONTAINERS ,
WAYBILL_AIRBILL ,
EXPECTED_ARRIVAL_DATE ,
TRANSFER_SUBINVENTORY ,
TRANSFER_ORGANIZATION ,
TRANSFER_TO_LOCATION ,
NEW_AVERAGE_COST ,
VALUE_CHANGE ,
PERCENTAGE_CHANGE ,
MATERIAL_ALLOCATION_TEMP_ID ,
DEMAND_SOURCE_HEADER_ID ,
DEMAND_SOURCE_LINE ,
DEMAND_SOURCE_DELIVERY ,
ITEM_SEGMENTS ,
ITEM_DESCRIPTION ,
ITEM_TRX_ENABLED_FLAG ,
ITEM_LOCATION_CONTROL_CODE ,
ITEM_RESTRICT_SUBINV_CODE ,
ITEM_RESTRICT_LOCATORS_CODE ,
ITEM_REVISION_QTY_CONTROL_CODE ,
ITEM_PRIMARY_UOM_CODE ,
ITEM_UOM_CLASS ,
ITEM_SHELF_LIFE_CODE ,
ITEM_SHELF_LIFE_DAYS ,
ITEM_LOT_CONTROL_CODE ,
ITEM_SERIAL_CONTROL_CODE ,
ITEM_INVENTORY_ASSET_FLAG ,
ALLOWED_UNITS_LOOKUP_CODE ,
DEPARTMENT_ID ,
DEPARTMENT_CODE ,
WIP_SUPPLY_TYPE ,
SUPPLY_SUBINVENTORY ,
SUPPLY_LOCATOR_ID ,
VALID_SUBINVENTORY_FLAG ,
VALID_LOCATOR_FLAG ,
LOCATOR_SEGMENTS ,
CURRENT_LOCATOR_CONTROL_CODE ,
NUMBER_OF_LOTS_ENTERED ,
WIP_COMMIT_FLAG ,
NEXT_LOT_NUMBER ,
LOT_ALPHA_PREFIX ,
NEXT_SERIAL_NUMBER ,
SERIAL_ALPHA_PREFIX ,
SHIPPABLE_FLAG ,
POSTING_FLAG ,
REQUIRED_FLAG ,
PROCESS_FLAG ,
ERROR_CODE ,
ERROR_EXPLANATION ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
MOVEMENT_ID ,
RESERVATION_QUANTITY ,
SHIPPED_QUANTITY ,
TRANSACTION_LINE_NUMBER ,
TASK_ID ,
TO_TASK_ID ,
SOURCE_TASK_ID ,
PROJECT_ID ,
SOURCE_PROJECT_ID ,
PA_EXPENDITURE_ORG_ID ,
TO_PROJECT_ID ,
EXPENDITURE_TYPE ,
FINAL_COMPLETION_FLAG ,
TRANSFER_PERCENTAGE ,
TRANSACTION_SEQUENCE_ID ,
MATERIAL_ACCOUNT ,
MATERIAL_OVERHEAD_ACCOUNT ,
RESOURCE_ACCOUNT ,
OUTSIDE_PROCESSING_ACCOUNT ,
OVERHEAD_ACCOUNT ,
FLOW_SCHEDULE ,
COST_GROUP_ID ,
DEMAND_CLASS ,
QA_COLLECTION_ID ,
KANBAN_CARD_ID ,
OVERCOMPLETION_TRANSACTION_ID ,
OVERCOMPLETION_PRIMARY_QTY ,
OVERCOMPLETION_TRANSACTION_QTY ,
--PROCESS_TYPE , --removed 01/13/00. process_type does not exist in the mmtt table.
END_ITEM_UNIT_NUMBER ,
SCHEDULED_PAYBACK_DATE ,
LINE_TYPE_CODE ,
PARENT_TRANSACTION_TEMP_ID ,
PUT_AWAY_STRATEGY_ID ,
PUT_AWAY_RULE_ID ,
PICK_STRATEGY_ID ,
PICK_RULE_ID ,
COMMON_BOM_SEQ_ID ,
COMMON_ROUTING_SEQ_ID ,
COST_TYPE_ID ,
ORG_COST_GROUP_ID ,
MOVE_ORDER_LINE_ID ,
TASK_GROUP_ID ,
PICK_SLIP_NUMBER ,
RESERVATION_ID ,
TRANSACTION_STATUS ,
STANDARD_OPERATION_ID ,
TASK_PRIORITY ,
-- ADDED by phegde 02/23
WMS_TASK_TYPE ,
PARENT_LINE_ID
--SOURCE_LOT_NUMBER
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND organization_id = p_organization_id;
SAVEPOINT Update_Misc_MMTT_PUB;
select organization_id into l_check_existence
from mtl_parameters
where organization_id = p_organization_id;
SELECT nvl(lot_control_code, 1), nvl(serial_number_control_code,1)
INTO l_item_lot_control_code, l_item_serial_control_code
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = l_csp_mtltxn_rec.inventory_item_id
AND organization_id = l_csp_mtltxn_rec.organization_id;
CSP_Material_Transactions_PVT.Update_material_transactions(
P_Api_Version_Number => p_api_version_number,
P_Init_Msg_List => p_init_msg_list,
P_Commit => fnd_api.g_false,
p_validation_level => l_validation_level,
P_CSP_Rec => l_csp_mtltxn_rec,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
Rollback to Update_Misc_MMTT_PUB;
Rollback to Update_Misc_MMTT_PUB;
END Update_Misc_MMTT;
SELECT sum(quantity_picked) qty_det,
line_id
FROM csp_picklist_lines
WHERE picklist_header_id = p_picklist_header_id
GROUP BY line_id;
SELECT organization_id into l_check_existence
FROM mtl_parameters
WHERE organization_id = p_organization_id;
l_trolin_rec.last_update_date := SYSDATE;
l_trolin_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_trolin_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
INV_Trolin_Util.Update_Row(l_trolin_rec);
Select SAFETY_FACTOR
From CSP_SAFETY_FACTORS
Where EXPOSURES = p_Exp
And SERVICE_LEVEL = p_SL;
Select MIN(Exposures) , MAX(Exposures)
From CSP_SAFETY_FACTORS;
Select SAFETY_FACTOR
From CSP_SAFETY_FACTORS
Where EXPOSURES = p_Exp
And SERVICE_LEVEL = p_SL;
Select MIN(Exposures) , MAX(Exposures)
From CSP_SAFETY_FACTORS;
Select SAFETY_FACTOR
From CSP_SAFETY_FACTORS
Where EXPOSURES = p_Exp
And SERVICE_LEVEL = p_SL;
Select MIN(Exposures) , MAX(Exposures)
From CSP_SAFETY_FACTORS;
Select SAFETY_FACTOR
From CSP_SAFETY_FACTORS
Where EXPOSURES = p_Exp
And SERVICE_LEVEL = p_SL;
Select MIN(Exposures) , MAX(Exposures)
From CSP_SAFETY_FACTORS;
Select SAFETY_FACTOR
From CSP_SAFETY_FACTORS
Where EXPOSURES = p_Exp
And SERVICE_LEVEL = p_SL;
Select MIN(Exposures) , MAX(Exposures)
From CSP_SAFETY_FACTORS;
select select_id
, select_name
, from_table
, where_clause
from jtf_objects_vl
where object_code = p_object_type_code;
'SELECT '||l_rec.select_name||' FROM '||l_rec.from_table||' WHERE ';
l_stmt := l_stmt||l_rec.select_id||' = :object_id';
select Name
from JTF_OBJECTS_VL
where OBJECT_CODE =p_object_type_code;
SELECT CSP_RS_CUST_RELATIONS_s1.nextval into l_sequence_number from dual;
SELECT distinct waybill,
name
FROM wsh_new_deliveries wnd,
wsh_delivery_Assignments wda,
wsh_delivery_details wdd
WHERE wnd.delivery_id = wda.delivery_id
AND wdd.delivery_detail_id = wda.delivery_Detail_id
AND wdd.source_line_id = p_order_line_id
AND wdd.source_code = 'OE';
SELECT rsl.shipment_line_status_code,
sum(rsl.quantity_received),
rsl.unit_of_measure
FROM po_Requisition_lines_all prl,
oe_order_lines_all oola,
rcv_shipment_lines rsl
WHERE prl.requisition_line_id = rsl.requisition_line_id
AND oola.source_document_line_id = prl.requisition_line_id
AND oola.source_document_type_id = 10
AND oola.line_id = p_order_line_id
group by oola.line_id, rsl.shipment_line_status_code, rsl.unit_of_measure;
SELECT meaning
INTO G_STATUS_MEANING
FROM FND_LOOKUP_VALUES LV
WHERE lookup_type = 'SHIPMENT LINE STATUS'
AND lookup_code = l_status
AND LANGUAGE = USERENV('LANG')
AND VIEW_APPLICATION_ID = 201
AND SECURITY_GROUP_ID = fnd_global.lookup_security_group(LV.LOOKUP_TYPE,
LV.VIEW_APPLICATION_ID);
SELECT meaning
INTO G_STATUS_MEANING
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = p_flow_status_code
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT meaning
INTO G_STATUS_MEANING
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = p_flow_status_code
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT sum(decode(released_status, 'Y', 1, 0)), sum(1)
INTO l_released_count, l_total_count
FROM wsh_delivery_details
WHERE source_line_id = p_order_line_id
AND source_code = 'OE';
SELECT meaning
INTO G_STATUS_MEANING
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED'
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT meaning
INTO G_STATUS_MEANING
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = 'PICKED_PARTIAL'
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
SELECT meaning
INTO G_STATUS_MEANING
FROM fnd_lookup_values lv
WHERE lookup_type = 'LINE_FLOW_STATUS'
AND lookup_code = l_status
AND LANGUAGE = userenv('LANG')
AND VIEW_APPLICATION_ID = 660
AND SECURITY_GROUP_ID = fnd_global.Lookup_Security_Group(lv.lookup_type,
lv.view_application_id);
select CONTACT_COMM_PREF,CONTACT_NAME
from csf_po_contact_points_v
where INCIDENT_ID = p_incident_id;