[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
MSN_UPDATE_FIRST_PASS BOOLEAN := TRUE;
PROCEDURE insertserial
(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_serial_number IN VARCHAR2
, p_current_status IN NUMBER
, p_group_mark_id IN NUMBER
, p_lot_number IN VARCHAR2
, p_initialization_date IN DATE DEFAULT SYSDATE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_organization_type IN NUMBER DEFAULT NULL
, p_owning_org_id IN NUMBER DEFAULT NULL
, p_owning_tp_type IN NUMBER DEFAULT NULL
, p_planning_org_id IN NUMBER DEFAULT NULL
, p_planning_tp_type IN NUMBER DEFAULT NULL
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'insertSerial';
SAVEPOINT apiinsertserial_apipub;
SELECT 1
INTO item_count
FROM mtl_parameters
WHERE organization_id = p_organization_id;
fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
SELECT serial_number_control_code
, eam_item_type
INTO l_serial_control_code
, eam_item
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
SELECT mtl_gen_object_id_s.NEXTVAL
INTO x_object_id
FROM DUAL;
INSERT INTO mtl_serial_numbers
(
inventory_item_id
, serial_number
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, current_status
, current_organization_id
, group_mark_id
, gen_object_id
, lot_number
, initialization_date
, organization_type
, owning_organization_id
, owning_tp_type
, planning_organization_id
, planning_tp_type
)
VALUES (
p_inventory_item_id
, p_serial_number
, SYSDATE
, l_userid
, SYSDATE
, l_userid
, l_loginid
, l_current_status
, p_organization_id
, p_group_mark_id
, x_object_id
, p_lot_number
, p_initialization_date
, NVL(p_organization_type, 2)
, NVL(p_owning_org_id, p_organization_id)
, NVL(p_owning_tp_type, 2)
, NVL(p_planning_org_id, p_organization_id)
, NVL(p_planning_tp_type, 2)
);
ROLLBACK TO apiinsertserial_apipub;
ROLLBACK TO apiinsertserial_apipub;
ROLLBACK TO apiinsertserial_apipub;
fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
END insertserial;
PROCEDURE insertserial(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_serial_number IN VARCHAR2
, p_initialization_date IN DATE
, p_completion_date IN DATE
, p_ship_date IN DATE
, p_revision IN VARCHAR2
, p_lot_number IN VARCHAR2
, p_current_locator_id IN NUMBER
, p_subinventory_code IN VARCHAR2
, p_trx_src_id IN NUMBER
, p_unit_vendor_id IN NUMBER
, p_vendor_lot_number IN VARCHAR2
, p_vendor_serial_number IN VARCHAR2
, p_receipt_issue_type IN NUMBER
, p_txn_src_id IN NUMBER
, p_txn_src_name IN VARCHAR2
, p_txn_src_type_id IN NUMBER
, p_transaction_id IN NUMBER
, p_current_status IN NUMBER
, p_parent_item_id IN NUMBER
, p_parent_serial_number IN VARCHAR2
, p_cost_group_id IN NUMBER
, p_transaction_action_id IN NUMBER
, p_transaction_temp_id IN NUMBER
, p_status_id IN NUMBER
, x_object_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_organization_type IN NUMBER DEFAULT NULL
, p_owning_org_id IN NUMBER DEFAULT NULL
, p_owning_tp_type IN NUMBER DEFAULT NULL
, p_planning_org_id IN NUMBER DEFAULT NULL
, p_planning_tp_type IN NUMBER DEFAULT NULL
--Serial Tracking in WIP project
, p_wip_entity_id IN NUMBER DEFAULT NULL
, p_operation_seq_num IN NUMBER DEFAULT NULL
, p_intraoperation_step_type IN NUMBER DEFAULT NULL
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'insertSerial';
l_status_rec inv_material_status_pub.mtl_status_update_rec_type;
SELECT serial_attribute_category
, fnd_date.date_to_canonical(origination_date)
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, fnd_date.date_to_canonical(d_attribute1)
, fnd_date.date_to_canonical(d_attribute2)
, fnd_date.date_to_canonical(d_attribute3)
, fnd_date.date_to_canonical(d_attribute4)
, fnd_date.date_to_canonical(d_attribute5)
, fnd_date.date_to_canonical(d_attribute6)
, fnd_date.date_to_canonical(d_attribute7)
, fnd_date.date_to_canonical(d_attribute8)
, fnd_date.date_to_canonical(d_attribute9)
, fnd_date.date_to_canonical(d_attribute10)
, TO_CHAR(n_attribute1)
, TO_CHAR(n_attribute2)
, TO_CHAR(n_attribute3)
, TO_CHAR(n_attribute4)
, TO_CHAR(n_attribute5)
, TO_CHAR(n_attribute6)
, TO_CHAR(n_attribute7)
, TO_CHAR(n_attribute8)
, TO_CHAR(n_attribute9)
, TO_CHAR(n_attribute10)
, status_id
, territory_code
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND p_serial_number BETWEEN fm_serial_number AND NVL(to_serial_number, fm_serial_number);
SAVEPOINT apiinsertserial_apipub;
inv_trx_util_pub.TRACE('In insertserial() procedure. ', 'INV_SERIAL_NUMBER_PUB', 9);
SELECT serial_number_control_code
INTO l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT mtl_gen_object_id_s.NEXTVAL
INTO x_object_id
FROM DUAL;
INSERT INTO mtl_serial_numbers
(
inventory_item_id
, serial_number
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, initialization_date
, completion_date
, ship_date
, current_status
, revision
, lot_number
, fixed_asset_tag
, reserved_order_id
, parent_item_id
, parent_serial_number
, original_wip_entity_id
, original_unit_vendor_id
, vendor_serial_number
, vendor_lot_number
, last_txn_source_type_id
, last_transaction_id
, last_receipt_issue_type
, last_txn_source_name
, last_txn_source_id
, descriptive_text
, current_subinventory_code
, current_locator_id
, current_organization_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, group_mark_id
, line_mark_id
, lot_line_mark_id
, end_item_unit_number
, gen_object_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, cost_group_id
, organization_type
, owning_organization_id
, owning_tp_type
, planning_organization_id
, planning_tp_type
, wip_entity_id
, operation_seq_num
, intraoperation_step_type
)
SELECT inventory_item_id
, serial_number
, SYSDATE
, l_userid
, SYSDATE
, l_userid
, l_loginid
, request_id
, program_application_id
, program_id
, program_update_date
, initialization_date
, completion_date
, ship_date
, current_status
, revision
, lot_number
, fixed_asset_tag
, reserved_order_id
, parent_item_id
, parent_serial_number
, original_wip_entity_id
, original_unit_vendor_id
, vendor_serial_number
, vendor_lot_number
, last_txn_source_type_id
, p_transaction_id
, last_receipt_issue_type
, p_txn_src_name
, p_txn_src_id
, descriptive_text
, p_subinventory_code
, p_current_locator_id
, p_organization_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, group_mark_id
, line_mark_id
, lot_line_mark_id
, end_item_unit_number
, x_object_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, inv_cost_group_pub.g_cost_group_id
, NVL(p_organization_type, 2)
, NVL(p_owning_org_id, p_organization_id)
, NVL(p_owning_tp_type, 2)
, NVL(p_planning_org_id, p_organization_id)
, NVL(p_planning_tp_type, 2)
, wip_entity_id
, operation_seq_num
, intraoperation_step_type
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND current_organization_id = g_transfer_org_id
AND inventory_item_id = p_inventory_item_id
AND NOT EXISTS(
SELECT NULL
FROM mtl_serial_numbers sn
WHERE sn.serial_number = p_serial_number
AND sn.current_organization_id = p_organization_id
AND sn.inventory_item_id = p_inventory_item_id);
SELECT status_id
INTO l_status_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
INSERT INTO mtl_serial_numbers
(
inventory_item_id
, serial_number
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, initialization_date
, completion_date
, ship_date
, current_status
, revision
, lot_number
, fixed_asset_tag
, reserved_order_id
, parent_item_id
, parent_serial_number
, original_wip_entity_id
, original_unit_vendor_id
, vendor_serial_number
, vendor_lot_number
, last_txn_source_type_id
, last_transaction_id
, last_receipt_issue_type
, last_txn_source_name
, last_txn_source_id
, descriptive_text
, current_subinventory_code
, current_locator_id
, current_organization_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, group_mark_id
, line_mark_id
, lot_line_mark_id
, end_item_unit_number
, gen_object_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, cost_group_id
, organization_type
, owning_organization_id
, owning_tp_type
, planning_organization_id
, planning_tp_type
, wip_entity_id
, operation_seq_num
, intraoperation_step_type
)
VALUES (
p_inventory_item_id
, p_serial_number
, SYSDATE
, l_userid
, SYSDATE
, l_userid
, l_loginid
, NULL
, NULL
, NULL
, NULL
, p_initialization_date
, p_completion_date
, p_ship_date
, p_current_status
, p_revision
, p_lot_number
, NULL
, NULL
, p_parent_item_id
, p_parent_serial_number
, p_trx_src_id
, p_unit_vendor_id
, p_vendor_serial_number
, p_vendor_lot_number
, p_txn_src_type_id
, p_transaction_id
, p_receipt_issue_type
, p_txn_src_name
, p_txn_src_id
, g_serial_attributes_tbl(31).column_value
, p_subinventory_code
, p_current_locator_id
, p_organization_id
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, x_object_id
, g_serial_attributes_tbl(1).column_value
, fnd_date.canonical_to_date(g_serial_attributes_tbl(2).column_value)
, g_serial_attributes_tbl(3).column_value
, g_serial_attributes_tbl(4).column_value
, g_serial_attributes_tbl(5).column_value
, g_serial_attributes_tbl(6).column_value
, g_serial_attributes_tbl(7).column_value
, g_serial_attributes_tbl(8).column_value
, g_serial_attributes_tbl(9).column_value
, g_serial_attributes_tbl(10).column_value
, g_serial_attributes_tbl(11).column_value
, g_serial_attributes_tbl(12).column_value
, g_serial_attributes_tbl(13).column_value
, g_serial_attributes_tbl(14).column_value
, g_serial_attributes_tbl(15).column_value
, g_serial_attributes_tbl(16).column_value
, g_serial_attributes_tbl(17).column_value
, g_serial_attributes_tbl(18).column_value
, g_serial_attributes_tbl(19).column_value
, g_serial_attributes_tbl(20).column_value
, g_serial_attributes_tbl(21).column_value
, g_serial_attributes_tbl(22).column_value
, fnd_date.canonical_to_date(g_serial_attributes_tbl(23).column_value)
, fnd_date.canonical_to_date(g_serial_attributes_tbl(24).column_value)
, fnd_date.canonical_to_date(g_serial_attributes_tbl(25).column_value)
, fnd_date.canonical_to_date(g_serial_attributes_tbl(26).column_value)
, fnd_date.canonical_to_date(g_serial_attributes_tbl(27).column_value)
, fnd_date.canonical_to_date(g_serial_attributes_tbl(28).column_value)
, fnd_date.canonical_to_date(g_serial_attributes_tbl(29).column_value)
, fnd_date.canonical_to_date(g_serial_attributes_tbl(30).column_value)
, fnd_date.canonical_to_date(g_serial_attributes_tbl(31).column_value)
, fnd_date.canonical_to_date(g_serial_attributes_tbl(32).column_value)
, TO_NUMBER(g_serial_attributes_tbl(33).column_value)
, TO_NUMBER(g_serial_attributes_tbl(34).column_value)
, TO_NUMBER(g_serial_attributes_tbl(35).column_value)
, TO_NUMBER(g_serial_attributes_tbl(36).column_value)
, TO_NUMBER(g_serial_attributes_tbl(37).column_value)
, TO_NUMBER(g_serial_attributes_tbl(38).column_value)
, TO_NUMBER(g_serial_attributes_tbl(39).column_value)
, TO_NUMBER(g_serial_attributes_tbl(40).column_value)
, TO_NUMBER(g_serial_attributes_tbl(41).column_value)
, TO_NUMBER(g_serial_attributes_tbl(42).column_value)
, l_status_id
, g_serial_attributes_tbl(44).column_value
, inv_cost_group_pub.g_cost_group_id
, NVL(p_organization_type, 2)
, NVL(p_owning_org_id, p_organization_id)
, NVL(p_owning_tp_type, 2)
, NVL(p_planning_org_id, p_organization_id)
, NVL(p_planning_tp_type, 2)
, p_wip_entity_id
, p_operation_seq_num
, p_intraoperation_step_type
);
l_status_rec.update_method := inv_material_status_pub.g_update_method_auto;
inv_material_status_pkg.insert_status_history(l_status_rec);
ROLLBACK TO apiinsertserial_apipub;
ROLLBACK TO apiinsertserial_apipub;
fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertSerial');
END insertserial;
PROCEDURE insert_range_serial(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_from_serial_number IN VARCHAR2
, p_to_serial_number IN VARCHAR2
, p_initialization_date IN DATE
, p_completion_date IN DATE
, p_ship_date IN DATE
, p_revision IN VARCHAR2
, p_lot_number IN VARCHAR2
, p_current_locator_id IN NUMBER
, p_subinventory_code IN VARCHAR2
, p_trx_src_id IN NUMBER
, p_unit_vendor_id IN NUMBER
, p_vendor_lot_number IN VARCHAR2
, p_vendor_serial_number IN VARCHAR2
, p_receipt_issue_type IN NUMBER
, p_txn_src_id IN NUMBER
, p_txn_src_name IN VARCHAR2
, p_txn_src_type_id IN NUMBER
, p_transaction_id IN NUMBER
, p_current_status IN NUMBER
, p_parent_item_id IN NUMBER
, p_parent_serial_number IN VARCHAR2
, p_cost_group_id IN NUMBER
, p_transaction_action_id IN NUMBER
, p_transaction_temp_id IN NUMBER
, p_status_id IN NUMBER
, p_inspection_status IN NUMBER
, x_object_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_organization_type IN NUMBER DEFAULT NULL
, p_owning_org_id IN NUMBER DEFAULT NULL
, p_owning_tp_type IN NUMBER DEFAULT NULL
, p_planning_org_id IN NUMBER DEFAULT NULL
, p_planning_tp_type IN NUMBER DEFAULT NULL
, p_rcv_serial_flag IN VARCHAR2 DEFAULT NULL
) IS
l_from_ser_number NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'insert_range_serial';
SAVEPOINT sp_insert_range_serial;
SELECT current_status
, NVL(group_mark_id, -1)
INTO l_current_status
, l_group_mark_id
FROM mtl_serial_numbers
WHERE serial_number = l_cur_serial_number
AND inventory_item_id = p_inventory_item_id;
* When the status is 4 (out of stores) then update the status do not insert
*/
IF (l_current_status = 1
AND l_group_mark_id = -1)
OR(l_current_status = 4
AND l_group_mark_id = -1)
OR(l_current_status = 6
AND l_group_mark_id = -1) THEN
-- Bug 5385315, Update the current_organization_id to p_organization_id
-- in mtl_serial_numbers while updating the current_status from 4 to 1.
IF (p_current_status = 1 AND l_current_status = 4) THEN
-- pre-defined serial, update status
UPDATE mtl_serial_numbers
SET current_status = p_current_status
, inspection_status = p_inspection_status
, lpn_id = null --bug 5152103
, current_organization_id = p_organization_id
WHERE serial_number = l_cur_serial_number
AND inventory_item_id = p_inventory_item_id;
UPDATE mtl_serial_numbers
SET current_status = p_current_status
, inspection_status = p_inspection_status
-- , lpn_id = decode(p_current_status,1,decode(current_status,4,null,lpn_id),lpn_id) --bug 5152103
WHERE serial_number = l_cur_serial_number
AND inventory_item_id = p_inventory_item_id;
inv_serial_number_pub.insertserial(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_serial_number => l_cur_serial_number
, p_initialization_date => p_initialization_date
, p_completion_date => p_completion_date
, p_ship_date => p_ship_date
, p_revision => p_revision
, p_lot_number => p_lot_number
, p_current_locator_id => p_current_locator_id
, p_subinventory_code => p_subinventory_code
, p_trx_src_id => p_trx_src_id
, p_unit_vendor_id => p_unit_vendor_id
, p_vendor_lot_number => p_vendor_lot_number
, p_vendor_serial_number => p_vendor_serial_number
, p_receipt_issue_type => p_receipt_issue_type
, p_txn_src_id => p_txn_src_id
, p_txn_src_name => p_txn_src_name
, p_txn_src_type_id => p_txn_src_type_id
, p_transaction_id => p_transaction_id
, p_current_status => p_current_status
, p_parent_item_id => p_parent_item_id
, p_parent_serial_number => p_parent_serial_number
, p_cost_group_id => p_cost_group_id
, p_transaction_action_id => p_transaction_action_id
, p_transaction_temp_id => p_transaction_temp_id
, p_status_id => p_status_id
, x_object_id => l_object_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_type => p_organization_type
, p_owning_org_id => p_owning_org_id
, p_owning_tp_type => p_owning_tp_type
, p_planning_org_id => p_planning_org_id
, p_planning_tp_type => p_planning_tp_type
);
* routines, then updates to the serial number are retained
*/
IF (NVL(p_rcv_serial_flag, 'N') <> 'Y') THEN
UPDATE mtl_serial_numbers
SET inspection_status = p_inspection_status
, lot_number = p_lot_number
, revision = p_revision
, current_organization_id = p_organization_id
, organization_type = NVL(p_organization_type, 2)
, owning_organization_id = NVL(p_owning_org_id, p_organization_id)
, owning_tp_type = NVL(p_owning_tp_type, 2)
, planning_organization_id = NVL(p_planning_org_id, p_organization_id)
, planning_tp_type = NVL(p_planning_tp_type, 2)
WHERE serial_number = l_cur_serial_number
AND inventory_item_id = p_inventory_item_id;
UPDATE mtl_serial_numbers
SET lot_number = p_lot_number
, revision = p_revision
WHERE serial_number = l_cur_serial_number
AND inventory_item_id = p_inventory_item_id
AND current_status IN(1, 4, 5, 6);
ROLLBACK TO sp_insert_range_serial;
ROLLBACK TO sp_insert_range_serial;
ROLLBACK TO sp_insert_range_serial;
END insert_range_serial;
PROCEDURE updateserial(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_serial_number IN VARCHAR2
, p_initialization_date IN DATE
, p_completion_date IN DATE
, p_ship_date IN DATE
, p_revision IN VARCHAR2
, p_lot_number IN VARCHAR2
, p_current_locator_id IN NUMBER
, p_subinventory_code IN VARCHAR2
, p_trx_src_id IN NUMBER
, p_unit_vendor_id IN NUMBER
, p_vendor_lot_number IN VARCHAR2
, p_vendor_serial_number IN VARCHAR2
, p_receipt_issue_type IN NUMBER
, p_txn_src_id IN NUMBER
, p_txn_src_name IN VARCHAR2
, p_txn_src_type_id IN NUMBER
, p_current_status IN NUMBER
, p_parent_item_id IN NUMBER
, p_parent_serial_number IN VARCHAR2
, p_serial_temp_id IN NUMBER
, p_last_status IN NUMBER
, p_status_id IN NUMBER
, x_object_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_organization_type IN NUMBER DEFAULT NULL
, p_owning_org_id IN NUMBER DEFAULT NULL
, p_owning_tp_type IN NUMBER DEFAULT NULL
, p_planning_org_id IN NUMBER DEFAULT NULL
, p_planning_tp_type IN NUMBER DEFAULT NULL
, p_transaction_action_id IN NUMBER DEFAULT NULL
, p_wip_entity_id IN NUMBER DEFAULT NULL
, p_operation_seq_num IN NUMBER DEFAULT NULL
, p_intraoperation_step_type IN NUMBER DEFAULT NULL
, p_line_mark_id IN NUMBER DEFAULT NULL
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'updateSerial';
SELECT serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, territory_code
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_serial_temp_id
AND p_serial_number BETWEEN fm_serial_number AND NVL(to_serial_number, fm_serial_number);
SAVEPOINT apiupdateserial_apipub;
invtrace('*** Inside UpdateSerial ****');
UPDATE mtl_serial_numbers
SET current_status = decode(p_current_status, null,
decode(p_wip_entity_id, null, current_status, decode(current_status, 6, 1, current_status)),
decode(p_wip_entity_id, null, p_current_status,decode(p_current_status, 6, 1, p_current_status)) )
, initialization_date = initialization_date
, completion_date = p_completion_date
, ship_date = p_ship_date
, revision = p_revision
, lot_number = p_lot_number
, -- do not update group mark id for staging transfer vipartha
-- group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, NULL)
group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, l_group_mark_id)
, line_mark_id = p_line_mark_id
, lot_line_mark_id = NULL
, current_organization_id = p_organization_id
, organization_type = NVL(p_organization_type, 2)
, owning_organization_id = NVL(p_owning_org_id, p_organization_id)
, owning_tp_type = NVL(p_owning_tp_type, 2)
, planning_organization_id = NVL(p_planning_org_id, p_organization_id)
, planning_tp_type = NVL(p_planning_tp_type, 2)
, current_locator_id = p_current_locator_id
, current_subinventory_code = p_subinventory_code
, original_wip_entity_id = p_trx_src_id
, original_unit_vendor_id = p_unit_vendor_id
, vendor_lot_number = p_vendor_lot_number
, vendor_serial_number = p_vendor_serial_number
, last_receipt_issue_type = p_receipt_issue_type
, last_txn_source_id = p_txn_src_id
, last_txn_source_type_id = p_txn_src_type_id
, last_txn_source_name = p_txn_src_name
, last_update_date = SYSDATE
, last_updated_by = l_userid
, parent_item_id = p_parent_item_id
, parent_serial_number = p_parent_serial_number
, origination_date = l_origination_date
, c_attribute1 = l_c_attribute1
, c_attribute2 = l_c_attribute2
, c_attribute3 = l_c_attribute3
, c_attribute4 = l_c_attribute4
, c_attribute5 = l_c_attribute5
, c_attribute6 = l_c_attribute6
, c_attribute7 = l_c_attribute7
, c_attribute8 = l_c_attribute8
, c_attribute9 = l_c_attribute9
, c_attribute10 = l_c_attribute10
, c_attribute11 = l_c_attribute11
, c_attribute12 = l_c_attribute12
, c_attribute13 = l_c_attribute13
, c_attribute14 = l_c_attribute14
, c_attribute15 = l_c_attribute15
, c_attribute16 = l_c_attribute16
, c_attribute17 = l_c_attribute17
, c_attribute18 = l_c_attribute18
, c_attribute19 = l_c_attribute19
, c_attribute20 = l_c_attribute20
, d_attribute1 = l_d_attribute1
, d_attribute2 = l_d_attribute2
, d_attribute3 = l_d_attribute3
, d_attribute4 = l_d_attribute4
, d_attribute5 = l_d_attribute5
, d_attribute6 = l_d_attribute6
, d_attribute7 = l_d_attribute7
, d_attribute8 = l_d_attribute8
, d_attribute9 = l_d_attribute9
, d_attribute10 = l_d_attribute10
, n_attribute1 = l_n_attribute1
, n_attribute2 = l_n_attribute2
, n_attribute3 = l_n_attribute3
, n_attribute4 = l_n_attribute4
, n_attribute5 = l_n_attribute5
, n_attribute6 = l_n_attribute6
, n_attribute7 = l_n_attribute7
, n_attribute8 = l_n_attribute8
, n_attribute9 = l_n_attribute9
, n_attribute10 = l_n_attribute10
, territory_code = l_territory_code
, cost_group_id = inv_cost_group_pub.g_cost_group_id
, wip_entity_id = p_wip_entity_id
, operation_seq_num = p_operation_seq_num
, intraoperation_step_type = p_intraoperation_step_type
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number
AND DECODE(current_status, 6, 1, current_status) = DECODE(p_last_status, 6, 1, p_last_status);
UPDATE mtl_serial_numbers
SET current_status = decode(p_current_status, null,
decode(p_wip_entity_id, null, current_status, decode(current_status, 6, 1, current_Status)),
decode(p_wip_entity_id, null, p_current_status, decode(p_current_status, 6, 1, p_current_status)))
, initialization_date = initialization_date
, completion_date = p_completion_date
, ship_date = p_ship_date
, revision = p_revision
, lot_number = p_lot_number
, -- do not update group mark id for staging transfer vipartha
--group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, NULL)
group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, l_group_mark_id)
, line_mark_id = p_line_mark_id
, lot_line_mark_id = NULL
, current_organization_id = p_organization_id
, current_locator_id = p_current_locator_id
, current_subinventory_code = p_subinventory_code
, original_wip_entity_id = p_trx_src_id
, original_unit_vendor_id = p_unit_vendor_id
, vendor_lot_number = p_vendor_lot_number
, vendor_serial_number = p_vendor_serial_number
, last_receipt_issue_type = p_receipt_issue_type
, last_txn_source_id = p_txn_src_id
, last_txn_source_type_id = p_txn_src_type_id
, last_txn_source_name = p_txn_src_name
, last_update_date = SYSDATE
, last_updated_by = l_userid
, parent_item_id = p_parent_item_id
, parent_serial_number = p_parent_serial_number
, wip_entity_id = p_wip_entity_id
, operation_seq_num = p_operation_seq_num
, intraoperation_step_type = p_intraoperation_step_type
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number
AND DECODE(current_status, 6, 1, current_status) = DECODE(p_last_status, 6, 1, p_last_status);
UPDATE mtl_serial_numbers
SET current_status = decode(p_current_status, null,
decode(p_wip_entity_id, null, current_status, decode(current_status, 6, 1, current_Status)),
decode(p_wip_entity_id, null, p_current_status, decode(p_current_status, 6, 1, p_current_status)))
, initialization_date = initialization_date
, completion_date = p_completion_date
, ship_date = p_ship_date
, revision = p_revision
, lot_number = p_lot_number
, -- do not update group mark id for staging transfer vipartha
--group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, NULL)
group_mark_id = DECODE(p_transaction_action_id, 28, group_mark_id, l_group_mark_id)
, line_mark_id = p_line_mark_id
, lot_line_mark_id = NULL
, current_organization_id = p_organization_id
, current_locator_id = p_current_locator_id
, current_subinventory_code = p_subinventory_code
, original_wip_entity_id = p_trx_src_id
, original_unit_vendor_id = p_unit_vendor_id
, vendor_lot_number = p_vendor_lot_number
, vendor_serial_number = p_vendor_serial_number
, last_receipt_issue_type = p_receipt_issue_type
, last_txn_source_id = p_txn_src_id
, last_txn_source_type_id = p_txn_src_type_id
, last_txn_source_name = p_txn_src_name
, last_update_date = SYSDATE
, last_updated_by = l_userid
, parent_item_id = p_parent_item_id
, parent_serial_number = p_parent_serial_number
, wip_entity_id = p_wip_entity_id
, operation_seq_num = p_operation_seq_num
, intraoperation_step_type = p_intraoperation_step_type
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number
AND DECODE(current_status, 6, 1, current_status) = DECODE(p_last_status, 6, 1, p_last_status);
ROLLBACK TO apiupdateserial_apipub;
fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'updateSerial');
END updateserial;
PROCEDURE insertunittrx(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_serial_number IN VARCHAR2
, p_current_locator_id IN NUMBER
, p_subinventory_code IN VARCHAR2
, p_transaction_date IN DATE
, p_txn_src_id IN NUMBER
, p_txn_src_name IN VARCHAR2
, p_txn_src_type_id IN NUMBER
, p_transaction_id IN NUMBER
, p_transaction_action_id IN NUMBER
, p_transaction_temp_id IN NUMBER
, p_receipt_issue_type IN NUMBER
, p_customer_id IN NUMBER
, p_ship_id IN NUMBER
, p_status_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 CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'insertSerial';
SELECT serial_attribute_category
, fnd_date.date_to_canonical(origination_date)
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, fnd_date.date_to_canonical(d_attribute1)
, fnd_date.date_to_canonical(d_attribute2)
, fnd_date.date_to_canonical(d_attribute3)
, fnd_date.date_to_canonical(d_attribute4)
, fnd_date.date_to_canonical(d_attribute5)
, fnd_date.date_to_canonical(d_attribute6)
, fnd_date.date_to_canonical(d_attribute7)
, fnd_date.date_to_canonical(d_attribute8)
, fnd_date.date_to_canonical(d_attribute9)
, fnd_date.date_to_canonical(d_attribute10)
, TO_CHAR(n_attribute1)
, TO_CHAR(n_attribute2)
, TO_CHAR(n_attribute3)
, TO_CHAR(n_attribute4)
, TO_CHAR(n_attribute5)
, TO_CHAR(n_attribute6)
, TO_CHAR(n_attribute7)
, TO_CHAR(n_attribute8)
, TO_CHAR(n_attribute9)
, TO_CHAR(n_attribute10)
, status_id
, territory_code
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND LPAD(p_serial_number,30) BETWEEN LPAD(fm_serial_number,30) AND LPAD(NVL(to_serial_number, fm_serial_number),30);
SAVEPOINT apiinsertserial_apipub;
INSERT INTO mtl_unit_transactions
(
transaction_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, serial_number
, inventory_item_id
, organization_id
, subinventory_code
, locator_id
, transaction_date
, transaction_source_id
, transaction_source_type_id
, transaction_source_name
, receipt_issue_type
, customer_id
, ship_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
)
SELECT p_transaction_id
, l_sys_date
, l_userid
, creation_date
, created_by
, l_loginid
, p_serial_number
, p_inventory_item_id
, p_organization_id
, p_subinventory_code
, p_current_locator_id
, p_transaction_date
, p_txn_src_id
, p_txn_src_type_id
, p_txn_src_name
, p_receipt_issue_type
, p_customer_id
, p_ship_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND current_organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
** insert statement gets executed the mtl_serial_number is
** table is already updated with the organization_id of the
** delivered org and status from the TM, so there will be an entry always exist
** ing for the where condition specified in the exists clasue
** for mtl_serial_number table
** So the insert statement will always fail.
*/
--and not exists
-- ( select NULL
-- from mtl_serial_numbers sn
-- where sn.serial_number = p_serial_number
-- and sn.current_organization_id = p_organization_id
-- and sn.inventory_item_id = p_inventory_item_id);
INSERT INTO mtl_unit_transactions
(
transaction_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, serial_number
, inventory_item_id
, organization_id
, subinventory_code
, locator_id
, transaction_date
, transaction_source_id
, transaction_source_type_id
, transaction_source_name
, receipt_issue_type
, customer_id
, ship_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, product_code
, product_transaction_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
)
SELECT p_transaction_id
, l_sys_date
, l_userid
, creation_date
, l_userid
, l_loginid
, p_serial_number
, p_inventory_item_id
, p_organization_id
, p_subinventory_code
, p_current_locator_id
, p_transaction_date
, p_txn_src_id
, p_txn_src_type_id
, p_txn_src_name
, p_receipt_issue_type
, p_customer_id
, p_ship_id
, g_serial_attributes_tbl(1).column_value
, l_date2
, g_serial_attributes_tbl(3).column_value
, g_serial_attributes_tbl(4).column_value
, g_serial_attributes_tbl(5).column_value
, g_serial_attributes_tbl(6).column_value
, g_serial_attributes_tbl(7).column_value
, g_serial_attributes_tbl(8).column_value
, g_serial_attributes_tbl(9).column_value
, g_serial_attributes_tbl(10).column_value
, g_serial_attributes_tbl(11).column_value
, g_serial_attributes_tbl(12).column_value
, g_serial_attributes_tbl(13).column_value
, g_serial_attributes_tbl(14).column_value
, g_serial_attributes_tbl(15).column_value
, g_serial_attributes_tbl(16).column_value
, g_serial_attributes_tbl(17).column_value
, g_serial_attributes_tbl(18).column_value
, g_serial_attributes_tbl(19).column_value
, g_serial_attributes_tbl(20).column_value
, g_serial_attributes_tbl(21).column_value
, g_serial_attributes_tbl(22).column_value
, l_date23
, l_date24
, l_date25
, l_date26
, l_date27
, l_date28
, l_date29
, l_date30
, l_date31
, l_date32
, l_num33
, l_num34
, l_num35
, l_num36
, l_num37
, l_num38
, l_num39
, l_num40
, l_num41
, l_num42
, p_status_id
, g_serial_attributes_tbl(44).column_value
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, product_code
, product_transaction_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND LPAD(p_serial_number,30) BETWEEN LPAD(fm_serial_number,30) AND LPAD(NVL(to_serial_number, fm_serial_number),30);
INSERT INTO mtl_unit_transactions
(
transaction_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, serial_number
, inventory_item_id
, organization_id
, subinventory_code
, locator_id
, transaction_date
, transaction_source_id
, transaction_source_type_id
, transaction_source_name
, receipt_issue_type
, customer_id
, ship_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
)
SELECT p_transaction_id
, SYSDATE
, l_userid
, SYSDATE
, l_userid
, l_loginid
, p_serial_number
, p_inventory_item_id
, p_organization_id
, p_subinventory_code
, p_current_locator_id
, p_transaction_date
, p_txn_src_id
, p_txn_src_type_id
, p_txn_src_name
, p_receipt_issue_type
, p_customer_id
, p_ship_id
, g_serial_attributes_tbl(1).column_value
, l_date2
, g_serial_attributes_tbl(3).column_value
, g_serial_attributes_tbl(4).column_value
, g_serial_attributes_tbl(5).column_value
, g_serial_attributes_tbl(6).column_value
, g_serial_attributes_tbl(7).column_value
, g_serial_attributes_tbl(8).column_value
, g_serial_attributes_tbl(9).column_value
, g_serial_attributes_tbl(10).column_value
, g_serial_attributes_tbl(11).column_value
, g_serial_attributes_tbl(12).column_value
, g_serial_attributes_tbl(13).column_value
, g_serial_attributes_tbl(14).column_value
, g_serial_attributes_tbl(15).column_value
, g_serial_attributes_tbl(16).column_value
, g_serial_attributes_tbl(17).column_value
, g_serial_attributes_tbl(18).column_value
, g_serial_attributes_tbl(19).column_value
, g_serial_attributes_tbl(20).column_value
, g_serial_attributes_tbl(21).column_value
, g_serial_attributes_tbl(22).column_value
, l_date23
, l_date24
, l_date25
, l_date26
, l_date27
, l_date28
, l_date29
, l_date30
, l_date31
, l_date32
, l_num33
, l_num34
, l_num35
, l_num36
, l_num37
, l_num38
, l_num39
, l_num40
, l_num41
, l_num42
, p_status_id
, g_serial_attributes_tbl(44).column_value
, msn.time_since_new
, msn.cycles_since_new
, msn.time_since_overhaul
, msn.cycles_since_overhaul
, msn.time_since_repair
, msn.cycles_since_repair
, msn.time_since_visit
, msn.cycles_since_visit
, msn.time_since_mark
, msn.cycles_since_mark
, msn.number_of_repairs
FROM mtl_serial_numbers msn
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number;
/*bug 2756040 Update MSN also with values from MSNT in case of
receipt transaction or intransit receipt txn
(transaction_action_id = 12 or 27) */
IF (p_transaction_action_id IN(12, 27, 31)) THEN
IF (l_debug = 1) THEN
invtrace('transaction_action_id = ' || p_transaction_action_id
|| ' org _id ' || p_organization_id || 'item ' ||
p_inventory_item_id);
UPDATE mtl_serial_numbers
SET serial_attribute_category = g_serial_attributes_tbl(1).column_value
, origination_date = l_date2
, c_attribute1 = g_serial_attributes_tbl(3).column_value
, c_attribute2 = g_serial_attributes_tbl(4).column_value
, c_attribute3 = g_serial_attributes_tbl(5).column_value
, c_attribute4 = g_serial_attributes_tbl(6).column_value
, c_attribute5 = g_serial_attributes_tbl(7).column_value
, c_attribute6 = g_serial_attributes_tbl(8).column_value
, c_attribute7 = g_serial_attributes_tbl(9).column_value
, c_attribute8 = g_serial_attributes_tbl(10).column_value
, c_attribute9 = g_serial_attributes_tbl(11).column_value
, c_attribute10 = g_serial_attributes_tbl(12).column_value
, c_attribute11 = g_serial_attributes_tbl(13).column_value
, c_attribute12 = g_serial_attributes_tbl(14).column_value
, c_attribute13 = g_serial_attributes_tbl(15).column_value
, c_attribute14 = g_serial_attributes_tbl(16).column_value
, c_attribute15 = g_serial_attributes_tbl(17).column_value
, c_attribute16 = g_serial_attributes_tbl(18).column_value
, c_attribute17 = g_serial_attributes_tbl(19).column_value
, c_attribute18 = g_serial_attributes_tbl(20).column_value
, c_attribute19 = g_serial_attributes_tbl(21).column_value
, c_attribute20 = g_serial_attributes_tbl(22).column_value
, d_attribute1 = l_date23
, d_attribute2 = l_date24
, d_attribute3 = l_date25
, d_attribute4 = l_date26
, d_attribute5 = l_date27
, d_attribute6 = l_date28
, d_attribute7 = l_date29
, d_attribute8 = l_date30
, d_attribute9 = l_date31
, d_attribute10 = l_date32
, n_attribute1 = l_num33
, n_attribute2 = l_num34
, n_attribute3 = l_num35
, n_attribute4 = l_num36
, n_attribute5 = l_num37
, n_attribute6 = l_num38
, n_attribute7 = l_num39
, n_attribute8 = l_num40
, n_attribute9 = l_num41
, n_attribute10 = l_num42
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id;
ROLLBACK TO apiinsertserial_apipub;
fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertUnitTrx');
END insertunittrx;
PROCEDURE validate_update_serial_att
(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_validation_status OUT NOCOPY VARCHAR2,
p_serial_number IN VARCHAR2,
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_serial_att_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type,
p_validate_only IN BOOLEAN
) IS
l_attributes_name VARCHAR2(50) := 'Serial Attributes';
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Entered...');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:p_inventory_item_id='||p_inventory_item_id);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:p_organization_id='||p_organization_id);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:p_serial_number='||p_serial_number);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_enabled_attributes='||l_enabled_attributes);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:'||g_serial_attributes_tbl(j).COLUMN_NAME);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:'||g_serial_attributes_tbl(j).COLUMN_NAME||':'||g_serial_attributes_tbl(j).COLUMN_VALUE);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling inv_lot_sel_attr.get_default...');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_attributes_default_count='||l_attributes_default_count);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:g_serial_attributes_tbl(j).COLUMN_VALUE='||g_serial_attributes_tbl(j).COLUMN_VALUE);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_attributes_default(i).COLUMN_VALUE='||l_attributes_default(i).column_value);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_dflex.get_flexfield...');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_dflex.get_context...');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_context_value='||l_context_value);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_context is null, attr enabaled=0');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_flex_descval.set_context_value...');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_flex_descval.clear_column_values...');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_flex_descval.set_column_values SERIAL_ATTRIBUTE_CATEGORY='||l_context_value);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:g_serial_attributes_tbl.COUNT='||g_serial_attributes_tbl.COUNT);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_dflex.get_segments...');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:v_colName='||v_colName);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:'||g_serial_attributes_tbl(k).Column_name);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:set_column_value='||g_serial_attributes_tbl(k).column_value);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:set_column_value='||g_serial_attributes_tbl(k).column_value);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:set_column_value='||g_serial_attributes_tbl(k).column_value);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:'||g_serial_attributes_tbl(k).COLUMN_NAME||':'||g_serial_attributes_tbl(k).COLUMN_VALUE);
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Calling fnd_flex_descval.validate_desccols...');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_status is TRUE');
invtrace('VALIDATE_UPDATE_SERIAL_ATT:l_status is FALSE');
-- if validation passed then update the attributes.
IF (l_debug = 1) THEN
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Updating the Attributes...');
UPDATE mtl_serial_numbers
SET serial_attribute_category = g_serial_attributes_tbl(1).COLUMN_VALUE
, origination_date = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(2).COLUMN_VALUE))
, c_attribute1 = g_serial_attributes_tbl(3).COLUMN_VALUE
, c_attribute2 = g_serial_attributes_tbl(4).COLUMN_VALUE
, c_attribute3 = g_serial_attributes_tbl(5).COLUMN_VALUE
, c_attribute4 = g_serial_attributes_tbl(6).COLUMN_VALUE
, c_attribute5 = g_serial_attributes_tbl(7).COLUMN_VALUE
, c_attribute6 = g_serial_attributes_tbl(8).COLUMN_VALUE
, c_attribute7 = g_serial_attributes_tbl(9).COLUMN_VALUE
, c_attribute8 = g_serial_attributes_tbl(10).COLUMN_VALUE
, c_attribute9 = g_serial_attributes_tbl(11).COLUMN_VALUE
, c_attribute10 = g_serial_attributes_tbl(12).COLUMN_VALUE
, c_attribute11 = g_serial_attributes_tbl(13).COLUMN_VALUE
, c_attribute12 = g_serial_attributes_tbl(14).COLUMN_VALUE
, c_attribute13 = g_serial_attributes_tbl(15).COLUMN_VALUE
, c_attribute14 = g_serial_attributes_tbl(16).COLUMN_VALUE
, c_attribute15 = g_serial_attributes_tbl(17).COLUMN_VALUE
, c_attribute16 = g_serial_attributes_tbl(18).COLUMN_VALUE
, c_attribute17 = g_serial_attributes_tbl(19).COLUMN_VALUE
, c_attribute18 = g_serial_attributes_tbl(20).COLUMN_VALUE
, c_attribute19 = g_serial_attributes_tbl(21).COLUMN_VALUE
, c_attribute20 = g_serial_attributes_tbl(22).COLUMN_VALUE
, d_attribute1 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(23).COLUMN_VALUE))
, d_attribute2 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(24).COLUMN_VALUE))
, d_attribute3 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(25).COLUMN_VALUE))
, d_attribute4 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(26).COLUMN_VALUE))
, d_attribute5 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(27).COLUMN_VALUE))
, d_attribute6 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(28).COLUMN_VALUE))
, d_attribute7 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(29).COLUMN_VALUE))
, d_attribute8 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(30).COLUMN_VALUE))
, d_attribute9 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(31).COLUMN_VALUE))
, d_attribute10 = fnd_date.canonical_to_date(fnd_date.date_to_canonical(g_serial_attributes_tbl(32).COLUMN_VALUE))
, n_attribute1 = to_number(g_serial_attributes_tbl(33).COLUMN_VALUE)
, n_attribute2 = to_number(g_serial_attributes_tbl(34).COLUMN_VALUE)
, n_attribute3 = to_number(g_serial_attributes_tbl(35).COLUMN_VALUE)
, n_attribute4 = to_number(g_serial_attributes_tbl(36).COLUMN_VALUE)
, n_attribute5 = to_number(g_serial_attributes_tbl(37).COLUMN_VALUE)
, n_attribute6 = to_number(g_serial_attributes_tbl(38).COLUMN_VALUE)
, n_attribute7 = to_number(g_serial_attributes_tbl(39).COLUMN_VALUE)
, n_attribute8 = to_number(g_serial_attributes_tbl(40).COLUMN_VALUE)
, n_attribute9 = to_number(g_serial_attributes_tbl(41).COLUMN_VALUE)
, n_attribute10 = to_number(g_serial_attributes_tbl(42).COLUMN_VALUE)
, status_id = Nvl(to_number(g_serial_attributes_tbl(43).COLUMN_VALUE),status_id)
, territory_code = g_serial_attributes_tbl(44).COLUMN_VALUE
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number
AND current_organization_id = p_organization_id;
invtrace('VALIDATE_UPDATE_SERIAL_ATT:Exitting...');
END validate_update_serial_att;
PROCEDURE update_msn
(x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_trxdate IN DATE,
p_transaction_temp_id IN NUMBER,
p_rev IN VARCHAR2,
p_lotnum IN VARCHAR2,
p_orgid IN NUMBER,
p_locid IN NUMBER, -- :lii,
p_subinv IN VARCHAR2,
p_trxsrctypid IN NUMBER,
p_trxsrcid IN NUMBER,
p_trx_act_id IN NUMBER,
p_vendid IN NUMBER, -- :i_vendor_idi,
p_venlot IN VARCHAR2,
p_receipt_issue_type IN NUMBER,
p_trxsname IN VARCHAR2,
p_lstupdby IN NUMBER,
p_parent_item_id IN NUMBER, -- :parent_item_i,
p_parent_ser_num IN VARCHAR2, -- :parent_sn_i,
p_ser_ctrl_code IN NUMBER,
p_xfr_ser_ctrl_code IN NUMBER,
p_trx_qty IN NUMBER,
p_invitemid IN NUMBER,
p_f_ser_num IN VARCHAR2,
p_t_ser_num IN VARCHAR2,
x_serial_updated OUT NOCOPY NUMBER
) IS
l_acct_prof_value VARCHAR2(1) := '';
SELECT current_status
INTO l_last_status
FROM mtl_serial_numbers
WHERE inventory_item_id = p_invitemid
AND serial_number = p_f_ser_num;
x_serial_updated := abs(p_trx_qty);
IF( MSN_UPDATE_FIRST_PASS ) THEN
MSN_UPDATE_FIRST_PASS := FALSE;
IF msn_update_first_pass THEN
l_status_after_p1 := l_last_status;
UPDATE MTL_SERIAL_NUMBERS MSN
SET msn.current_status = l_to_status,
msn.initialization_date = l_init_date,
msn.completion_date = null,
msn.SHIP_DATE = NULL,
msn.REVISION = NULL,
msn.LOT_NUMBER = NULL,
msn.GROUP_MARK_ID = NULL,
msn.LINE_MARK_ID = NULL,
msn.LOT_LINE_MARK_ID = NULL,
msn.CURRENT_ORGANIZATION_ID = p_orgid,
msn.CURRENT_LOCATOR_ID = NULL,
msn.CURRENT_SUBINVENTORY_CODE = NULL,
msn.ORIGINAL_WIP_ENTITY_ID = NULL,
msn.ORIGINAL_UNIT_VENDOR_ID = NULL,
msn.VENDOR_LOT_NUMBER = NULL,
msn.LAST_RECEIPT_ISSUE_TYPE = p_receipt_issue_type,
msn.LAST_TXN_SOURCE_ID =NULL,
msn.LAST_TXN_SOURCE_TYPE_ID = NULL,
msn.LAST_TXN_SOURCE_NAME = NULL,
msn.LAST_UPDATE_DATE = l_sys_date,
msn.LAST_UPDATED_BY = p_lstupdby,
msn.PARENT_ITEM_ID = p_parent_item_id, -- :parent_item_i,
msn.PARENT_SERIAL_NUMBER = p_parent_ser_num, -- :parent_sn_i,
msn.PREVIOUS_STATUS = l_status_after_p1, -- l_last_status, -- p_last_status,
msn.STATUS_ID = NULL,
msn.ORGANIZATION_TYPE = 2,
msn.OWNING_ORGANIZATION_ID = p_orgid,
msn.OWNING_TP_TYPE = 2,
msn.PLANNING_ORGANIZATION_ID = p_orgid,
msn.PLANNING_TP_TYPE = 2
WHERE
msn.INVENTORY_ITEM_ID = p_invitemid
AND msn.SERIAL_NUMBER BETWEEN p_f_ser_num AND p_t_ser_num
AND decode( msn.CURRENT_STATUS, 6, 1, msn.CURRENT_STATUS ) = l_status_after_p1 -- l_last_status -- p_last_status
AND Nvl(msn.owning_tp_type,2) <> 1
AND Nvl(msn.owning_organization_id,msn.current_organization_id) = msn.current_organization_id
AND inv_serial_number_pub.valsn(
p_trxsrctypid, -- trx_src_typ_id IN NUMBER,
p_trx_act_id, -- trx_action_id IN NUMBER,
p_rev, -- revision IN VARCHAR2,
p_subinv, -- curr_subinv_code IN VARCHAR2,
p_locid, -- :lii, -- locator_id IN NUMBER,
p_invitemid, -- item IN NUMBER,
p_orgid, -- curr_org_id IN NUMBER,
p_lotnum, -- lot IN VARCHAR2,
msn.serial_number, -- curr_ser_num IN VARCHAR2,
p_ser_ctrl_code, -- ser_num_ctrl_code IN NUMBER,
p_xfr_ser_ctrl_code,
p_trx_qty, -- trx_qty IN NUMBER,
l_acct_prof_value, -- acct_prof_value IN VARCHAR2,
l_mask, -- P_mask IN VARCHAR2,
msn.current_status, /* db_current_status IN NUMBER, */
msn.current_organization_id, -- db_current_organization_id IN NUMBER,
msn.revision, -- db_revision IN VARCHAR2,
msn.lot_number, -- db_lot_number IN VARCHAR2,
msn.current_subinventory_code, -- db_current_subinventory_code IN VARCHAR2,
msn.current_locator_id, -- db_current_locator_id IN NUMBER,
decode( nvl( msn.original_wip_entity_id, -1 ), -1, -1 , 1 ), -- db_wip_ent_id_ind IN NUMBER,
msn.last_txn_source_type_id -- db_lst_txn_src_typ_id IN NUMBER
) = l_to_status;
UPDATE MTL_SERIAL_NUMBERS msn
SET
msn.CURRENT_STATUS = l_to_status, -- p_current_status,
msn.COMPLETION_DATE = NVL( msn.COMPLETION_DATE, p_trxdate ),
msn.SHIP_DATE = DECODE( l_to_status, 3, NULL, NVL( msn.SHIP_DATE, p_trxdate ) ),
msn.REVISION = DECODE( l_last_status, 3, msn.REVISION, p_rev ),
msn.LOT_NUMBER = DECODE( l_last_status, 3, msn.LOT_NUMBER, p_lotnum ),
msn.CURRENT_ORGANIZATION_ID = p_orgid,
msn.CURRENT_LOCATOR_ID = p_locid, -- :lii,
msn.CURRENT_SUBINVENTORY_CODE = p_subinv,
msn.ORIGINAL_WIP_ENTITY_ID = decode( p_trxsrctypid, 5, p_trxsrcid, 2, NULL, msn.ORIGINAL_WIP_ENTITY_ID ),
msn.ORIGINAL_UNIT_VENDOR_ID = NVL( msn.ORIGINAL_UNIT_VENDOR_ID, p_vendid ), -- :i_vendor_idi),
msn.VENDOR_LOT_NUMBER = NVL( msn.VENDOR_LOT_NUMBER,p_venlot ),
msn.LAST_RECEIPT_ISSUE_TYPE = p_receipt_issue_type,
msn.LAST_TXN_SOURCE_ID = p_trxsrcid,
msn.LAST_TXN_SOURCE_TYPE_ID = p_trxsrctypid,
msn.LAST_TXN_SOURCE_NAME = p_trxsname,
msn.GROUP_MARK_ID = NULL,
msn.LINE_MARK_ID = NULL,
msn.LOT_LINE_MARK_ID = NULL,
msn.LAST_UPDATE_DATE = l_sys_date,
msn.LAST_UPDATED_BY = p_lstupdby,
msn.PARENT_ITEM_ID = p_parent_item_id, -- :parent_item_i,
msn.PARENT_SERIAL_NUMBER = p_parent_ser_num, -- :parent_sn_i,
msn.COST_GROUP_ID = l_cg_id,
msn.ORGANIZATION_TYPE = 2,
msn.OWNING_ORGANIZATION_ID = p_orgid,
msn.OWNING_TP_TYPE = 2,
msn.PLANNING_ORGANIZATION_ID = p_orgid,
msn.PLANNING_TP_TYPE = 2
WHERE
msn.INVENTORY_ITEM_ID = p_invitemid
AND msn.SERIAL_NUMBER BETWEEN p_f_ser_num AND p_t_ser_num
AND decode( msn.CURRENT_STATUS, 6, 1, msn.CURRENT_STATUS ) = l_last_status
AND Nvl(msn.owning_organization_id,msn.current_organization_id) = msn.current_organization_id
AND Nvl(msn.owning_tp_type,2) <> 1
AND inv_serial_number_pub.valsn(
p_trxsrctypid, -- trx_src_typ_id IN NUMBER,
p_trx_act_id, -- trx_action_id IN NUMBER,
p_rev, -- revision IN VARCHAR2,
p_subinv, -- curr_subinv_code IN VARCHAR2,
p_locid, -- :lii, -- locator_id IN NUMBER,
p_invitemid, -- item IN NUMBER,
p_orgid, -- curr_org_id IN NUMBER,
p_lotnum, -- lot IN VARCHAR2,
msn.serial_number, -- curr_ser_num IN VARCHAR2,
p_ser_ctrl_code, -- ser_num_ctrl_code IN NUMBER,
nvl(p_xfr_ser_ctrl_code,1), -- p_xfr_ser_ctrl_code IN NUMBER
p_trx_qty, -- trx_qty IN NUMBER,
l_acct_prof_value, -- acct_prof_value IN VARCHAR2,
l_mask, -- P_mask IN VARCHAR2,
msn.current_status,
msn.current_organization_id, -- db_current_organization_id IN NUMBER,
msn.revision, -- db_revision IN VARCHAR2,
msn.lot_number, -- db_lot_number IN VARCHAR2,
msn.current_subinventory_code, -- db_current_subinventory_code IN VARCHAR2,
msn.current_locator_id, -- db_current_locator_id IN NUMBER,
decode( nvl( msn.original_wip_entity_id, -1 ), -1, -1 , 1 ), -- db_wip_ent_id_ind IN NUMBER,
msn.last_txn_source_type_id -- db_lst_txn_src_typ_id IN NUMBER
) > 0;
invtrace( 'updated=' || to_char( l_upd_count ));
invtrace( ' Updated not the same as the transaction. trx qty: ' || l_qty);
x_msg_data := 'Can only update ' || to_char( l_upd_count ) || ' of ' ||
to_char( l_qty ) || '. Rejecting update';
invtrace( ' Updated same as the transaction. Success');
x_serial_updated := l_upd_count;
invtrace( ' Unexpected error in update_msn API');
invtrace( ' Error in update_msn API');
END update_msn;
PROCEDURE insertRangeUnitTrx(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_fm_serial_number IN VARCHAR2,
p_to_serial_number IN VARCHAR2,
p_current_locator_id IN NUMBER,
p_subinventory_code IN VARCHAR2,
p_transaction_date IN DATE,
p_txn_src_id IN NUMBER,
p_txn_src_name IN VARCHAR2,
p_txn_src_type_id IN NUMBER,
p_transaction_id IN NUMBER,
p_transaction_action_id IN NUMBER,
p_transaction_temp_id IN NUMBER,
p_receipt_issue_type IN NUMBER,
p_customer_id IN NUMBER,
p_ship_id IN NUMBER,
p_status_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 CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30):= 'insertRangeUnitTrx';
select SERIAL_ATTRIBUTE_CATEGORY
, fnd_date.date_to_canonical(ORIGINATION_DATE )
, C_ATTRIBUTE1
, C_ATTRIBUTE2
, C_ATTRIBUTE3
, C_ATTRIBUTE4
, C_ATTRIBUTE5
, C_ATTRIBUTE6
, C_ATTRIBUTE7
, C_ATTRIBUTE8
, C_ATTRIBUTE9
, C_ATTRIBUTE10
, C_ATTRIBUTE11
, C_ATTRIBUTE12
, C_ATTRIBUTE13
, C_ATTRIBUTE14
, C_ATTRIBUTE15
, C_ATTRIBUTE16
, C_ATTRIBUTE17
, C_ATTRIBUTE18
, C_ATTRIBUTE19
, C_ATTRIBUTE20
, fnd_date.date_to_canonical(D_ATTRIBUTE1 )
, fnd_date.date_to_canonical(D_ATTRIBUTE2 )
, fnd_date.date_to_canonical(D_ATTRIBUTE3 )
, fnd_date.date_to_canonical(D_ATTRIBUTE4 )
, fnd_date.date_to_canonical(D_ATTRIBUTE5 )
, fnd_date.date_to_canonical(D_ATTRIBUTE6 )
, fnd_date.date_to_canonical(D_ATTRIBUTE7)
, fnd_date.date_to_canonical(D_ATTRIBUTE8)
, fnd_date.date_to_canonical( D_ATTRIBUTE9)
, fnd_date.date_to_canonical(D_ATTRIBUTE10 )
, to_char(N_ATTRIBUTE1 )
, to_char(N_ATTRIBUTE2)
, to_char(N_ATTRIBUTE3)
, to_char(N_ATTRIBUTE4)
, to_char(N_ATTRIBUTE5)
, to_char(N_ATTRIBUTE6)
, to_char(N_ATTRIBUTE7)
, to_char(N_ATTRIBUTE8)
, to_char( N_ATTRIBUTE9)
, to_char(N_ATTRIBUTE10)
, STATUS_ID
, TERRITORY_CODE
, TIME_SINCE_NEW
, CYCLES_SINCE_NEW
, TIME_SINCE_OVERHAUL
, CYCLES_SINCE_OVERHAUL
, TIME_SINCE_REPAIR
, CYCLES_SINCE_REPAIR
, TIME_SINCE_VISIT
, CYCLES_SINCE_VISIT
, TIME_SINCE_MARK
, CYCLES_SINCE_MARK
, NUMBER_OF_REPAIRS
from mtl_serial_numbers_temp
where transaction_temp_id = p_transaction_temp_id
and fm_serial_number = p_fm_serial_number and to_serial_number = p_to_serial_number;
invtrace('Inside InsertRangeUnitTrx');
SAVEPOINT apiinsertserial_apipub;
INSERT INTO mtl_unit_transactions
(
transaction_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, serial_number
, inventory_item_id
, organization_id
, subinventory_code
, locator_id
, transaction_date
, transaction_source_id
, transaction_source_type_id
, transaction_source_name
, receipt_issue_type
, customer_id
, ship_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
)
SELECT p_transaction_id
, l_sys_date
, l_userid
, msn.creation_date
, msn.created_by
, l_loginid
, msn.serial_number
, p_inventory_item_id
, p_organization_id
, p_subinventory_code
, p_current_locator_id
, p_transaction_date
, p_txn_src_id
, p_txn_src_type_id
, p_txn_src_name
, p_receipt_issue_type
, p_customer_id
, p_ship_id
, msn.serial_attribute_category
, msn.origination_date
, msn.c_attribute1
, msn.c_attribute2
, msn.c_attribute3
, msn.c_attribute4
, msn.c_attribute5
, msn.c_attribute6
, msn.c_attribute7
, msn.c_attribute8
, msn.c_attribute9
, msn.c_attribute10
, msn.c_attribute11
, msn.c_attribute12
, msn.c_attribute13
, msn.c_attribute14
, msn.c_attribute15
, msn.c_attribute16
, msn.c_attribute17
, msn.c_attribute18
, msn.c_attribute19
, msn.c_attribute20
, msn.d_attribute1
, msn.d_attribute2
, msn.d_attribute3
, msn.d_attribute4
, msn.d_attribute5
, msn.d_attribute6
, msn.d_attribute7
, msn.d_attribute8
, msn.d_attribute9
, msn.d_attribute10
, msn.n_attribute1
, msn.n_attribute2
, msn.n_attribute3
, msn.n_attribute4
, msn.n_attribute5
, msn.n_attribute6
, msn.n_attribute7
, msn.n_attribute8
, msn.n_attribute9
, msn.n_attribute10
, msn.status_id
, msn.territory_code
, msn.time_since_new
, msn.cycles_since_new
, msn.time_since_overhaul
, msn.cycles_since_overhaul
, msn.time_since_repair
, msn.cycles_since_repair
, msn.time_since_visit
, msn.cycles_since_visit
, msn.time_since_mark
, msn.cycles_since_mark
, msn.number_of_repairs
FROM mtl_serial_numbers msn
WHERE msn.serial_number between p_fm_serial_number and p_to_serial_number
AND msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_inventory_item_id;
** insert statement gets executed the mtl_serial_number is
** table is already updated with the organization_id of the
** delivered org and status from the TM, so there will be an entry always exist
** ing for the where condition specified in the exists clasue
** for mtl_serial_number table
** So the insert statement will always fail.
*/
--and not exists
-- ( select NULL
-- from mtl_serial_numbers sn
-- where sn.serial_number = p_serial_number
-- and sn.current_organization_id = p_organization_id
-- and sn.inventory_item_id = p_inventory_item_id);
SELECT count(*)
into l_upd_count
FROM mtl_serial_numbers msn, mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_transaction_temp_id
AND lpad(msn.serial_number, 30) between lpad(msnt.fm_serial_number,30) AND LPAD(NVL(msnt.to_serial_number, msnt.fm_serial_number),30)
AND Lpad(msnt.fm_serial_number,30) = l_fm_serial_number
AND Lpad(msnt.to_serial_number,30) = l_to_serial_number;
invtrace('insert into mut with tempid = ' ||
p_transaction_temp_id);
INSERT INTO mtl_unit_transactions
(
transaction_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, serial_number
, inventory_item_id
, organization_id
, subinventory_code
, locator_id
, transaction_date
, transaction_source_id
, transaction_source_type_id
, transaction_source_name
, receipt_issue_type
, customer_id
, ship_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, product_code
, product_transaction_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
)
SELECT p_transaction_id
, l_sys_date
, l_userid
, l_sys_date
, l_userid
, l_loginid
, msn.serial_number
, p_inventory_item_id
, p_organization_id
, p_subinventory_code
, p_current_locator_id
, p_transaction_date
, p_txn_src_id
, p_txn_src_type_id
, p_txn_src_name
, p_receipt_issue_type
, p_customer_id
, p_ship_id
, g_serial_attributes_tbl(1).column_value
, l_date2
, g_serial_attributes_tbl(3).column_value
, g_serial_attributes_tbl(4).column_value
, g_serial_attributes_tbl(5).column_value
, g_serial_attributes_tbl(6).column_value
, g_serial_attributes_tbl(7).column_value
, g_serial_attributes_tbl(8).column_value
, g_serial_attributes_tbl(9).column_value
, g_serial_attributes_tbl(10).column_value
, g_serial_attributes_tbl(11).column_value
, g_serial_attributes_tbl(12).column_value
, g_serial_attributes_tbl(13).column_value
, g_serial_attributes_tbl(14).column_value
, g_serial_attributes_tbl(15).column_value
, g_serial_attributes_tbl(16).column_value
, g_serial_attributes_tbl(17).column_value
, g_serial_attributes_tbl(18).column_value
, g_serial_attributes_tbl(19).column_value
, g_serial_attributes_tbl(20).column_value
, g_serial_attributes_tbl(21).column_value
, g_serial_attributes_tbl(22).column_value
, l_date23
, l_date24
, l_date25
, l_date26
, l_date27
, l_date28
, l_date29
, l_date30
, l_date31
, l_date32
, l_num33
, l_num34
, l_num35
, l_num36
, l_num37
, l_num38
, l_num39
, l_num40
, l_num41
, l_num42
, p_status_id
, g_serial_attributes_tbl(44).column_value
, l_time_since_new
, l_cycles_since_new
, l_time_since_overhaul
, l_cycles_since_overhaul
, l_time_since_repair
, l_cycles_since_repair
, l_time_since_visit
, l_cycles_since_visit
, l_time_since_mark
, l_cycles_since_mark
, l_number_of_repairs
, msnt.product_code
, msnt.product_transaction_id
, msnt.attribute_category
, msnt.attribute1
, msnt.attribute2
, msnt.attribute3
, msnt.attribute4
, msnt.attribute5
, msnt.attribute6
, msnt.attribute7
, msnt.attribute8
, msnt.attribute9
, msnt.attribute10
, msnt.attribute11
, msnt.attribute12
, msnt.attribute13
, msnt.attribute14
, msnt.attribute15
FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn
WHERE msnt.transaction_temp_id = p_transaction_temp_id
AND msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_inventory_item_id
AND lpad(msn.serial_number,30) between lpad(msnt.fm_serial_number,30) AND lpad(msnt.to_serial_number,30)
AND lpad(msnt.fm_serial_number,30) = l_fm_serial_number
AND lpad(msnt.to_serial_number,30) = l_to_serial_number;
INSERT INTO mtl_unit_transactions
(
transaction_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, serial_number
, inventory_item_id
, organization_id
, subinventory_code
, locator_id
, transaction_date
, transaction_source_id
, transaction_source_type_id
, transaction_source_name
, receipt_issue_type
, customer_id
, ship_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, product_code
, product_transaction_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
)
SELECT p_transaction_id
, l_sys_date
, l_userid
, l_sys_date
, l_userid
, l_loginid
, msn.serial_number
, p_inventory_item_id
, p_organization_id
, p_subinventory_code
, p_current_locator_id
, p_transaction_date
, p_txn_src_id
, p_txn_src_type_id
, p_txn_src_name
, p_receipt_issue_type
, p_customer_id
, p_ship_id
, g_serial_attributes_tbl(1).column_value
, l_date2
, g_serial_attributes_tbl(3).column_value
, g_serial_attributes_tbl(4).column_value
, g_serial_attributes_tbl(5).column_value
, g_serial_attributes_tbl(6).column_value
, g_serial_attributes_tbl(7).column_value
, g_serial_attributes_tbl(8).column_value
, g_serial_attributes_tbl(9).column_value
, g_serial_attributes_tbl(10).column_value
, g_serial_attributes_tbl(11).column_value
, g_serial_attributes_tbl(12).column_value
, g_serial_attributes_tbl(13).column_value
, g_serial_attributes_tbl(14).column_value
, g_serial_attributes_tbl(15).column_value
, g_serial_attributes_tbl(16).column_value
, g_serial_attributes_tbl(17).column_value
, g_serial_attributes_tbl(18).column_value
, g_serial_attributes_tbl(19).column_value
, g_serial_attributes_tbl(20).column_value
, g_serial_attributes_tbl(21).column_value
, g_serial_attributes_tbl(22).column_value
, l_date23
, l_date24
, l_date25
, l_date26
, l_date27
, l_date28
, l_date29
, l_date30
, l_date31
, l_date32
, l_num33
, l_num34
, l_num35
, l_num36
, l_num37
, l_num38
, l_num39
, l_num40
, l_num41
, l_num42
, p_status_id
, g_serial_attributes_tbl(44).column_value
, l_time_since_new
, l_cycles_since_new
, l_time_since_overhaul
, l_cycles_since_overhaul
, l_time_since_repair
, l_cycles_since_repair
, l_time_since_visit
, l_cycles_since_visit
, l_time_since_mark
, l_cycles_since_mark
, l_number_of_repairs
, msnt.product_code
, msnt.product_transaction_id
, msnt.attribute_category
, msnt.attribute1
, msnt.attribute2
, msnt.attribute3
, msnt.attribute4
, msnt.attribute5
, msnt.attribute6
, msnt.attribute7
, msnt.attribute8
, msnt.attribute9
, msnt.attribute10
, msnt.attribute11
, msnt.attribute12
, msnt.attribute13
, msnt.attribute14
, msnt.attribute15
FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn
WHERE msnt.transaction_temp_id = p_transaction_temp_id
AND msn.current_organization_id = p_organization_id
AND msn.inventory_item_id = p_inventory_item_id
AND lpad(msn.serial_number,30) = lpad(msnt.fm_serial_number,30)
AND lpad(msnt.fm_serial_number,30) = l_fm_serial_number;
INSERT INTO mtl_unit_transactions
(
transaction_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, serial_number
, inventory_item_id
, organization_id
, subinventory_code
, locator_id
, transaction_date
, transaction_source_id
, transaction_source_type_id
, transaction_source_name
, receipt_issue_type
, customer_id
, ship_id
, serial_attribute_category
, origination_date
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, status_id
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
)
SELECT p_transaction_id
, SYSDATE
, l_userid
, SYSDATE
, l_userid
, l_loginid
, msn.serial_number
, p_inventory_item_id
, p_organization_id
, p_subinventory_code
, p_current_locator_id
, p_transaction_date
, p_txn_src_id
, p_txn_src_type_id
, p_txn_src_name
, p_receipt_issue_type
, p_customer_id
, p_ship_id
, g_serial_attributes_tbl(1).column_value
, l_date2
, g_serial_attributes_tbl(3).column_value
, g_serial_attributes_tbl(4).column_value
, g_serial_attributes_tbl(5).column_value
, g_serial_attributes_tbl(6).column_value
, g_serial_attributes_tbl(7).column_value
, g_serial_attributes_tbl(8).column_value
, g_serial_attributes_tbl(9).column_value
, g_serial_attributes_tbl(10).column_value
, g_serial_attributes_tbl(11).column_value
, g_serial_attributes_tbl(12).column_value
, g_serial_attributes_tbl(13).column_value
, g_serial_attributes_tbl(14).column_value
, g_serial_attributes_tbl(15).column_value
, g_serial_attributes_tbl(16).column_value
, g_serial_attributes_tbl(17).column_value
, g_serial_attributes_tbl(18).column_value
, g_serial_attributes_tbl(19).column_value
, g_serial_attributes_tbl(20).column_value
, g_serial_attributes_tbl(21).column_value
, g_serial_attributes_tbl(22).column_value
, l_date23
, l_date24
, l_date25
, l_date26
, l_date27
, l_date28
, l_date29
, l_date30
, l_date31
, l_date32
, l_num33
, l_num34
, l_num35
, l_num36
, l_num37
, l_num38
, l_num39
, l_num40
, l_num41
, l_num42
, p_status_id
, g_serial_attributes_tbl(44).column_value
, l_time_since_new
, l_cycles_since_new
, l_time_since_overhaul
, l_cycles_since_overhaul
, l_time_since_repair
, l_cycles_since_repair
, l_time_since_visit
, l_cycles_since_visit
, l_time_since_mark
, l_cycles_since_mark
, l_number_of_repairs
FROM mtl_serial_numbers msn
WHERE inventory_item_id = p_inventory_item_id
AND serial_number between p_fm_serial_number AND p_to_serial_number;
/*bug 2756040 Update MSN also with values from MSNT in case of
receipt transaction or intransit receipt txn
(transaction_action_id = 12 or 27) */
IF (p_transaction_action_id IN(12, 27, 31)) THEN
IF (l_debug = 1) THEN
invtrace('transaction_action_id = ' || p_transaction_action_id
|| ' org _id ' || p_organization_id || 'item ' ||
p_inventory_item_id);
UPDATE mtl_serial_numbers
SET serial_attribute_category = g_serial_attributes_tbl(1).column_value
, origination_date = l_date2
, c_attribute1 = g_serial_attributes_tbl(3).column_value
, c_attribute2 = g_serial_attributes_tbl(4).column_value
, c_attribute3 = g_serial_attributes_tbl(5).column_value
, c_attribute4 = g_serial_attributes_tbl(6).column_value
, c_attribute5 = g_serial_attributes_tbl(7).column_value
, c_attribute6 = g_serial_attributes_tbl(8).column_value
, c_attribute7 = g_serial_attributes_tbl(9).column_value
, c_attribute8 = g_serial_attributes_tbl(10).column_value
, c_attribute9 = g_serial_attributes_tbl(11).column_value
, c_attribute10 = g_serial_attributes_tbl(12).column_value
, c_attribute11 = g_serial_attributes_tbl(13).column_value
, c_attribute12 = g_serial_attributes_tbl(14).column_value
, c_attribute13 = g_serial_attributes_tbl(15).column_value
, c_attribute14 = g_serial_attributes_tbl(16).column_value
, c_attribute15 = g_serial_attributes_tbl(17).column_value
, c_attribute16 = g_serial_attributes_tbl(18).column_value
, c_attribute17 = g_serial_attributes_tbl(19).column_value
, c_attribute18 = g_serial_attributes_tbl(20).column_value
, c_attribute19 = g_serial_attributes_tbl(21).column_value
, c_attribute20 = g_serial_attributes_tbl(22).column_value
, d_attribute1 = l_date23
, d_attribute2 = l_date24
, d_attribute3 = l_date25
, d_attribute4 = l_date26
, d_attribute5 = l_date27
, d_attribute6 = l_date28
, d_attribute7 = l_date29
, d_attribute8 = l_date30
, d_attribute9 = l_date31
, d_attribute10 = l_date32
, n_attribute1 = l_num33
, n_attribute2 = l_num34
, n_attribute3 = l_num35
, n_attribute4 = l_num36
, n_attribute5 = l_num37
, n_attribute6 = l_num38
, n_attribute7 = l_num39
, n_attribute8 = l_num40
, n_attribute9 = l_num41
, n_attribute10 = l_num42
WHERE serial_number between p_fm_serial_number and p_to_serial_number
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id;
ROLLBACK TO apiinsertserial_apipub;
fnd_msg_pub.add_exc_msg('INV_SERIAL_NUMBER_PUB', 'insertRangeUnitTrx');
END insertRangeUnitTrx;