[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT mtl_material_transactions_s.nextval
INTO txn_header_id
FROM dual;
SELECT primary_uom_code
, decode(lot_control_code , 1 , 'N' , 'Y')
, decode(serial_number_control_code , 1 , 'N' , 'Y')
INTO primary_uom
, lot_control
, serial_control
FROM mtl_system_items
WHERE organization_id = P_Organization_ID
AND inventory_item_id = P_Item_ID;
INSERT INTO mtl_transactions_interface
( transaction_header_id
, transaction_interface_id
, source_code
, source_header_id
, source_line_id
, transaction_mode
, lock_flag
, process_flag
, validation_required
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, transaction_uom
, primary_quantity
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_date
, acct_period_id
, reason_id
, transfer_organization
, transfer_subinventory
, transfer_locator
, transaction_reference
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
) VALUES
( P_Txn_Header_ID
, txn_xface_id
, 'PJM MASS TRANSFER' /* Source Code */
, 0 /* Source Header ID */
, 0 /* Source Line ID */
, P_Process_Mode
, NULL /* Lock Flag */
, 1 /* Process Flag */
, 1 /* Validation Required */
, sysdate
, user_id
, sysdate
, user_id
, login_id
, P_Item_ID
, P_Revision
, P_Organization_ID
, P_Subinventory_Code
, P_From_Locator_ID
, P_Txn_Quantity
, primary_uom
, P_Txn_Quantity
, 67 /* Transaction Type ID - Project Transfer */
, 2 /* Transaction Action ID - Subinventory Transfer */
, 13 /* Trasaction Source Type ID - Inventory */
, P_Txn_Date
, P_Acct_Period_ID
, P_Txn_Reason_ID
, P_Organization_ID
, P_Subinventory_Code
, P_To_Locator_ID
, P_Txn_Reference
, P_DFF.Category
, P_DFF.Attr1
, P_DFF.Attr2
, P_DFF.Attr3
, P_DFF.Attr4
, P_DFF.Attr5
, P_DFF.Attr6
, P_DFF.Attr7
, P_DFF.Attr8
, P_DFF.Attr9
, P_DFF.Attr10
, P_DFF.Attr11
, P_DFF.Attr12
, P_DFF.Attr13
, P_DFF.Attr14
, P_DFF.Attr15
);
INSERT INTO mtl_transaction_lots_interface
( transaction_interface_id
, serial_transaction_temp_id
, source_code
, source_line_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, transaction_quantity
, primary_quantity
, lot_number
)
SELECT mti.transaction_interface_id
, ser_txn_id
, mti.source_code
, mti.source_line_id
, mti.last_update_date
, mti.last_updated_by
, mti.creation_date
, mti.created_by
, mti.last_update_login
, mti.transaction_quantity
, mti.primary_quantity
, P_Lot_Number
FROM mtl_transactions_interface mti
WHERE mti.transaction_interface_id = txn_xface_id;
INSERT INTO mtl_serial_numbers_interface
( transaction_interface_id
, source_code
, source_line_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, fm_serial_number
, to_serial_number
)
SELECT ser_txn_id
, mti.source_code
, mti.source_line_id
, mti.last_update_date
, mti.last_updated_by
, mti.creation_date
, mti.created_by
, mti.last_update_login
, msn.serial_number
, msn.serial_number
FROM mtl_transactions_interface mti
, mtl_serial_numbers msn
WHERE mti.transaction_interface_id = txn_xface_id
AND msn.inventory_item_id = mti.inventory_item_id
AND msn.current_organization_id = mti.organization_id
AND msn.current_subinventory_code = mti.subinventory_code
AND msn.current_locator_id = mti.locator_id
AND nvl(msn.lot_number , '') =
nvl(P_Lot_Number , '')
AND msn.current_status = 3;
SELECT DISTINCT moq.inventory_item_id
FROM mtl_onhand_quantities_detail moq
, mtl_item_locations mil
WHERE moq.organization_id = P_Organization_ID
AND mil.organization_id = moq.organization_id
AND mil.inventory_location_id = moq.locator_id
AND mil.project_id = P_From_Project_ID
AND nvl(mil.task_id , 0) = nvl(P_From_Task_ID , 0);
SELECT DISTINCT moq.inventory_item_id
FROM mtl_onhand_quantities_detail moq
, mtl_item_locations mil
, mtl_item_categories mic
WHERE mic.organization_id = P_Organization_ID
AND mic.category_set_id = P_Category_Set_ID
AND mic.category_id = P_Category_ID
AND moq.organization_id = mic.organization_id
AND moq.inventory_item_id = mic.inventory_item_id
AND mil.organization_id = moq.organization_id
AND mil.inventory_location_id = moq.locator_id
AND mil.project_id = P_From_Project_ID
AND nvl(mil.task_id , 0) = nvl(P_From_Task_ID , 0);
SELECT P_Item_ID FROM DUAL
WHERE P_Item_ID is not null;
SELECT moq.subinventory_code
, moq.locator_id
, moq.lot_number
, moq.revision
, (-1) * sum(moq.transaction_quantity) txn_quantity
FROM mtl_onhand_quantities_detail moq
, mtl_item_locations mil
WHERE moq.organization_id = P_Organization_ID
AND moq.inventory_item_id = P_Item_ID
AND mil.organization_id = moq.organization_id
AND mil.inventory_location_id = moq.locator_id
AND mil.project_id = P_From_Project_ID
AND nvl(mil.task_id , 0) = nvl(P_From_Task_ID , 0)
AND NOT EXISTS (
SELECT 'Expired lot'
FROM mtl_lot_numbers
WHERE organization_id = moq.organization_id
AND inventory_item_id = moq.inventory_item_id
AND lot_number = moq.lot_number
AND expiration_date < sysdate )
GROUP BY moq.subinventory_code , moq.locator_id , moq.lot_number , moq.revision
HAVING sum(moq.transaction_quantity) > 0;
SELECT p.organization_id
, p.acct_period_id
, p.from_project_id
, p.to_project_id
, p.transfer_date
, p.transfer_mode
, p.inventory_item_id
, p.category_set_id
, p.category_id
, p.transfer_reason_id
, p.transfer_reference
, p.process_mode
, p.attribute_category
, p.attribute1
, p.attribute2
, p.attribute3
, p.attribute4
, p.attribute5
, p.attribute6
, p.attribute7
, p.attribute8
, p.attribute9
, p.attribute10
, p.attribute11
, p.attribute12
, p.attribute13
, p.attribute14
, p.attribute15
FROM pjm_mass_transfers p
WHERE p.mass_transfer_id = P_Transfer_ID;
SELECT from_task_id
, to_task_id
FROM pjm_mass_transfer_tasks
WHERE mass_transfer_id = P_Transfer_ID;