[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select serial_number
into l_serial_number
from mtl_serial_numbers
where current_organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and serial_number = p_from_serial_number;
inv_serial_number_pub.insert_range_serial(
p_api_version => 1.0
, p_validation_level => fnd_api.g_valid_level_full
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_from_serial_number => p_from_serial_number
, p_to_serial_number => p_to_serial_number
, p_initialization_date => null
, p_completion_date => null
, p_ship_date =>null
, p_revision =>null -- p_revision
, p_lot_number =>p_lot_number
, p_current_locator_id =>null
, p_subinventory_code =>null
, p_trx_src_id =>null
, p_unit_vendor_id =>null
, p_vendor_lot_number =>null
, p_vendor_serial_number =>null
, p_receipt_issue_type =>null
, p_txn_src_id =>null
, p_txn_src_name =>null
, p_txn_src_type_id =>null
, p_transaction_id =>null
, p_current_status =>1 -- current status, need to verify with osfm
, p_parent_item_id => null
, p_parent_serial_number =>null
, p_cost_group_id =>null
, p_transaction_action_id =>null
, p_transaction_temp_id =>null
, p_status_id =>1
, p_inspection_status =>null
, x_object_id =>x_object_id
, x_return_status =>x_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
);*/
inv_serial_number_pub.insertserial(
p_api_version =>1.0
, p_validation_level => fnd_api.g_valid_level_full
, p_inventory_item_id =>p_inventory_item_id
, p_organization_id =>p_organization_id
, p_serial_number =>p_from_serial_number
, p_current_status =>1
, p_group_mark_id =>null --
, p_lot_number =>p_lot_number
, x_return_status =>x_return_status
, x_msg_count =>x_msg_count
, x_msg_data =>x_msg_data
);
mydebug('Program insert_range_serial ' || x_return_status);
mydebug('Program insert_range_serial has failed with a user defined exception');
mydebug('Program insert_range_serial has failed with a Unexpected exception');
FND_MESSAGE.SET_TOKEN('PROG_NAME','insert_range_serial');
mydebug('End of the program insert_range_serial. Program has completed successfully ');
procedure delete_serial(x_return_status out nocopy VARCHAR2
, x_msg_count out nocopy NUMBER
, x_msg_data out nocopy VARCHAR2
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, p_from_serial_number VARCHAR2
, p_to_serial_number VARCHAR2
)
IS
BEGIN
delete from mtl_serial_numbers
where current_organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and serial_number between p_from_serial_number and p_to_serial_number;
END delete_serial;
procedure insert_serial(p_api_version NUMBER
, p_init_msg_list VARCHAR2
, p_commit VARCHAR2
, p_validation_level NUMBER
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, p_serial_number VARCHAR2
, p_initialization_date DATE
, p_completion_date DATE
, p_ship_date DATE
, p_revision VARCHAR2
, p_lot_number VARCHAR2
, p_current_locator_id NUMBER
, p_subinventory_code VARCHAR2
, p_trx_src_id NUMBER
, p_unit_vendor_id NUMBER
, p_vendor_lot_number VARCHAR2
, p_vendor_serial_number VARCHAR2
, p_receipt_issue_type NUMBER
, p_txn_src_id NUMBER
, p_txn_src_name VARCHAR2
, p_txn_src_type_id NUMBER
, p_transaction_id NUMBER
, p_current_status NUMBER
, p_parent_item_id NUMBER
, p_parent_serial_number VARCHAR2
, p_cost_group_id NUMBER
, p_transaction_action_id NUMBER
, p_transaction_temp_id NUMBER
, p_status_id 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 NUMBER
, p_owning_org_id NUMBER
, p_owning_tp_type NUMBER
, p_planning_org_id NUMBER
, p_planning_tp_type NUMBER
, p_wip_entity_id NUMBER
, p_operation_seq_num NUMBER
, p_intraoperation_step_type NUMBER
, p_attribute_category VARCHAR2
, p_attributes_tbl JTF_VARCHAR2_TABLE_1000
, p_serial_attribute_category VARCHAR2
, p_c_attributes_tbl JTF_VARCHAR2_TABLE_1000
, p_n_attributes_tbl JTF_NUMBER_TABLE
, p_d_attributes_tbl JTF_DATE_TABLE
, p_origination_date DATE
, p_territory_code VARCHAR2
)
as
ddp_attributes_tbl inv_lot_api_pub.char_tbl;
inv_serial_number_pub.insertserial(p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
p_inventory_item_id,
p_organization_id,
p_serial_number,
p_initialization_date,
p_completion_date,
p_ship_date,
p_revision,
p_lot_number,
p_current_locator_id,
p_subinventory_code,
p_trx_src_id,
p_unit_vendor_id,
p_vendor_lot_number,
p_vendor_serial_number,
p_receipt_issue_type,
p_txn_src_id,
p_txn_src_name,
p_txn_src_type_id,
p_transaction_id,
p_current_status,
p_parent_item_id,
p_parent_serial_number,
p_cost_group_id,
p_transaction_action_id,
p_transaction_temp_id,
p_status_id,
x_object_id,
x_return_status,
x_msg_count,
x_msg_data,
p_organization_type,
p_owning_org_id,
p_owning_tp_type,
p_planning_org_id,
p_planning_tp_type,
p_wip_entity_id,
p_operation_seq_num,
p_intraoperation_step_type);
procedure update_serial(p_api_version NUMBER
, p_init_msg_list VARCHAR2
, p_commit VARCHAR2
, p_validation_level NUMBER
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, p_serial_number VARCHAR2
, p_initialization_date DATE
, p_completion_date DATE
, p_ship_date DATE
, p_revision VARCHAR2
, p_lot_number VARCHAR2
, p_current_locator_id NUMBER
, p_subinventory_code VARCHAR2
, p_trx_src_id NUMBER
, p_unit_vendor_id NUMBER
, p_vendor_lot_number VARCHAR2
, p_vendor_serial_number VARCHAR2
, p_receipt_issue_type NUMBER
, p_txn_src_id NUMBER
, p_txn_src_name VARCHAR2
, p_txn_src_type_id NUMBER
, p_current_status NUMBER
, p_parent_item_id NUMBER
, p_parent_serial_number VARCHAR2
, p_serial_temp_id NUMBER
, p_last_status NUMBER
, p_status_id 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 NUMBER
, p_owning_org_id NUMBER
, p_owning_tp_type NUMBER
, p_planning_org_id NUMBER
, p_planning_tp_type NUMBER
, p_transaction_action_id NUMBER
, p_wip_entity_id NUMBER
, p_operation_seq_num NUMBER
, p_intraoperation_step_type NUMBER
, p_line_mark_id NUMBER
, p_attribute_category VARCHAR2
, p_attributes_tbl JTF_VARCHAR2_TABLE_1000
, p_serial_attribute_category VARCHAR2
, p_c_attributes_tbl JTF_VARCHAR2_TABLE_1000
, p_n_attributes_tbl JTF_NUMBER_TABLE
, p_d_attributes_tbl JTF_DATE_TABLE
, p_origination_date DATE
, p_territory_code VARCHAR2
)
as
ddp_attributes_tbl inv_lot_api_pub.char_tbl;
SAVEPOINT inv_update_serial;
mydebug('entering update_serial');
mydebug('calling updateserial');
inv_serial_number_pub.updateserial(p_api_version,
p_init_msg_list,
p_commit,
p_validation_level,
p_inventory_item_id,
p_organization_id,
p_serial_number,
p_initialization_date,
p_completion_date,
p_ship_date,
p_revision,
p_lot_number,
p_current_locator_id,
p_subinventory_code,
p_trx_src_id,
p_unit_vendor_id,
p_vendor_lot_number,
p_vendor_serial_number,
p_receipt_issue_type,
p_txn_src_id,
p_txn_src_name,
p_txn_src_type_id,
p_current_status,
p_parent_item_id,
p_parent_serial_number,
p_serial_temp_id,
p_last_status,
p_status_id,
x_object_id,
x_return_status,
x_msg_count,
x_msg_data,
p_organization_type,
p_owning_org_id,
p_owning_tp_type,
p_planning_org_id,
p_planning_tp_type,
p_transaction_action_id,
p_wip_entity_id,
p_operation_seq_num,
p_intraoperation_step_type,
p_line_mark_id);
mydebug('after calling updateSerial');
mydebug('Program insert_range_serial has failed with a user defined exception');
mydebug('Program insert_range_serial has failed with a Unexpected exception');
FND_MESSAGE.SET_TOKEN('PROG_NAME','insert_range_serial');
mydebug('calling validate_update_serial_att');
--validate and update the attributes.
inv_serial_number_pub.validate_update_serial_att
(x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_validation_status => l_validation_status,
p_serial_number => p_serial_number,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_serial_att_tbl => l_serial_attributes_tbl,
p_validate_only => FALSE
);
mydebug('After calling validate_update_serial_att');
mydebug('update_attributes');
update mtl_serial_numbers
set attribute1= ddp_attributes_tbl(1)
, attribute2= ddp_attributes_tbl(2)
, attribute3= ddp_attributes_tbl(3)
, attribute4= ddp_attributes_tbl(4)
, attribute5= ddp_attributes_tbl(5)
, attribute6= ddp_attributes_tbl(6)
, attribute7= ddp_attributes_tbl(7)
, attribute8= ddp_attributes_tbl(8)
, attribute9= ddp_attributes_tbl(9)
, attribute10= ddp_attributes_tbl(10)
, attribute11= ddp_attributes_tbl(11)
, attribute12= ddp_attributes_tbl(12)
, attribute13= ddp_attributes_tbl(13)
, attribute14= ddp_attributes_tbl(14)
, attribute15= ddp_attributes_tbl(15)
, attribute_category = p_attribute_category
WHERE current_organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and serial_number = p_serial_number;
ROLLBACK TO inv_update_serial;
ROLLBACK TO inv_update_serial;
ROLLBACK TO inv_update_serial;
ROLLBACK TO inv_update_serial;