[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT location_id
INTO l_location_id
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND secondary_inventory_name = p_subinventory_code;
SELECT location_id
INTO l_location_id
FROM hr_organization_units
WHERE organization_id = p_organization_id;
SELECT *
INTO l_sub_type_rec
FROM csi_txn_sub_types
WHERE transaction_type_id = p_txn_type_id
AND sub_type_id = p_sub_type_id;
SELECT decode(p_reference_type,'P','Parent','N','Non Source','S','Source')
INTO l_reference_code
FROM sys.dual;
SELECT serial_number_control_code,
lot_control_code,
location_control_code,
revision_qty_control_code,
nvl(comms_nl_trackable_flag,'N'),
nvl(shippable_item_flag,'N'),
nvl(inventory_item_flag,'N'),
nvl(stock_enabled_flag,'N'),
bom_item_type
INTO l_serial_code,
l_lot_code,
l_locator_code,
l_revision_code,
l_ib_trackable_flag,
l_shippable_flag,
l_inv_item_flag,
l_stockable_flag,
l_bom_item_type
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT negative_inv_receipt_code
INTO x_negative_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
p_api_name => 'update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_u_instance_rec,
p_party_tbl => l_u_parties_tbl,
p_account_tbl => l_u_pty_accts_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_ext_attrib_values_tbl => l_u_ea_values_tbl,
p_pricing_attrib_tbl => l_u_pricing_tbl,
p_asset_assignment_tbl => l_u_assets_tbl,
p_txn_rec => p_txn_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* for an update based on the availability on the instance record */
/* ---------------------------------------------------------------------*/
PROCEDURE build_instance_rec(
p_sub_type_rec IN csi_txn_sub_types%rowtype,
p_item_attr_rec IN csi_process_txn_pvt.item_attr_rec,
p_instance_rec IN csi_process_txn_grp.txn_instance_rec,
p_dest_location_rec IN csi_process_txn_grp.dest_location_rec,
x_instance_rec OUT NOCOPY csi_datastructures_pub.instance_rec,
x_process_mode OUT NOCOPY varchar2,
x_return_status OUT NOCOPY varchar2)
IS
l_instance_rec csi_datastructures_pub.instance_rec;
l_process_mode := 'UPDATE';
l_assets_tbl(c_ind).update_status := p_instance_asset_tbl(l_ind).update_status;
SELECT location_type_code
INTO l_location_type_code
FROM csi_item_instances
WHERE instance_id = p_instance_rec.instance_id;
SELECT instance_party_id ,
object_version_number
INTO p_parties_tbl(l_ind).instance_party_id,
p_parties_tbl(l_ind).object_version_number
FROM csi_i_parties
WHERE instance_id = p_instance_rec.instance_id
AND relationship_type_code = 'OWNER'
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT ip_account_id,
object_version_number
INTO p_pty_accts_tbl(l_a_ind).ip_account_id,
p_pty_accts_tbl(l_a_ind).object_version_number
FROM csi_ip_accounts
WHERE instance_party_id = p_pty_accts_tbl(l_a_ind).instance_party_id
AND relationship_type_code = 'OWNER'
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT instance_ou_id ,
object_version_number
INTO p_org_units_tbl(l_ind).instance_ou_id,
p_org_units_tbl(l_ind).object_version_number
FROM csi_i_org_assignments
WHERE instance_id = p_instance_rec.instance_id
AND relationship_type_code = p_org_units_tbl(l_ind).relationship_type_code
AND operating_unit_id = p_org_units_tbl(l_ind).operating_unit_id
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT instance_asset_id,
object_version_number
INTO p_assets_tbl(l_ind).instance_asset_id,
p_assets_tbl(l_ind).object_version_number
FROM csi_i_assets
WHERE instance_id = p_instance_rec.instance_id
AND fa_asset_id = p_assets_tbl(l_ind).fa_asset_id
AND fa_book_type_code = p_assets_tbl(l_ind).fa_book_type_code
AND rownum = 1;
SELECT instance_party_id,
object_version_number,
party_id,
relationship_type_code
FROM csi_i_parties
WHERE instance_id = p_instance_rec.instance_id
AND relationship_type_code <> 'OWNER'
AND contact_flag = 'N'
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT pricing_attribute_id,
pricing_context,
object_version_number
FROM csi_i_pricing_attribs
WHERE instance_id = p_instance_rec.instance_id
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT instance_ou_id,
operating_unit_id,
relationship_type_code,
object_version_number
FROM csi_i_org_assignments
WHERE instance_id = p_instance_rec.instance_id
AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
AND nvl(active_end_date, sysdate+1);
SELECT instance_party_id,
object_version_number
INTO l_instance_party_id,
l_object_version_number
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND relationship_type_code = 'OWNER';
SELECT object_version_number
INTO l_u_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_u_instance_rec.instance_id;
p_api_name => 'update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_u_instance_rec,
p_party_tbl => l_u_parties_tbl,
p_account_tbl => l_u_pty_accts_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_ext_attrib_values_tbl => l_u_ea_values_tbl,
p_pricing_attrib_tbl => l_u_pricing_tbl,
p_asset_assignment_tbl => l_u_assets_tbl,
p_txn_rec => p_transaction_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* check if a destination instance is found , if found then update
the destination instance otherwise create
*/
csi_process_txn_pvt.get_dest_instance_id(
p_in_out_flag => p_in_out_flag,
p_sub_type_rec => p_sub_type_rec,
p_instance_rec => p_instance_rec,
p_dest_location_rec => p_dest_location_rec,
p_item_attr_rec => p_item_attr_rec,
x_instance_id => l_dest_instance_id,
x_return_status => l_return_status);
SELECT object_version_number,
quantity
INTO l_instance_rec.object_version_number,
l_dest_instance_qty
FROM csi_item_instances
WHERE instance_id = l_dest_instance_id;
l_parties_tbl.DELETE;
l_pty_accts_tbl.DELETE;
l_current_procedure := 'update_item_instance';
p_api_name => 'update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => p_transaction_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('Destination Instance Updated successfully. Instance ID: '||l_dest_instance_id);
IF l_process_mode = 'UPDATE' THEN
debug('Source Instance marked for updation.');
SELECT active_end_date
INTO l_active_end_date
FROM csi_item_instances
WHERE instance_id = l_instance_rec.instance_id;
SELECT object_version_number
INTO l_instance_rec.object_version_number
FROM csi_item_instances
WHERE instance_id = l_instance_rec.instance_id;
l_current_procedure := 'update_item_instance';
p_api_name => 'update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => p_transaction_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('update instance successful. instance id : '||l_instance_rec.instance_id);
SELECT quantity,
object_version_number,
serial_number,
nvl(mfg_serial_number_flag, 'N'),
active_end_date,
owner_party_account_id
INTO l_src_instance_qty,
l_object_version_number,
l_serial_number,
l_mfg_serial_number_flag,
l_active_end_date,
l_owner_party_account_id
FROM csi_item_instances
WHERE instance_id = l_instance_rec.instance_id;
debug('serialized at so issue item. trying to update the source instance.');
SELECT instance_party_id ,
object_version_number
INTO l_u_parties_tbl(l_up_ind).instance_party_id,
l_u_parties_tbl(l_up_ind).object_version_number
FROM csi_i_parties
WHERE instance_id = l_u_instance_rec.instance_id
AND relationship_type_code =
l_u_parties_tbl(l_up_ind).relationship_type_code;
ELSE /* this is exclusively for the non serialized source instance update */
debug('nsrl: source update');
SELECT 'Y'
INTO l_config_return
FROM sys.dual
WHERE exists (SELECT relationship_id
FROM csi_ii_relationships
WHERE object_id = l_u_instance_rec.instance_id
AND relationship_type_code = 'COMPONENT-OF');
debug('nsrl: non rma - source update just decrement');
SELECT object_version_number
INTO l_object_version_number
FROM csi_item_instances
WHERE instance_id = l_u_instance_rec.instance_id;
l_current_procedure := 'update_item_instance';
p_api_name => 'update_item_instance');
/* source instance update for srl at so issue and non serial */
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_u_instance_rec,
p_party_tbl => l_u_parties_tbl,
p_account_tbl => l_u_pty_accts_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_ext_attrib_values_tbl => l_u_ea_values_tbl,
p_pricing_attrib_tbl => l_u_pricing_tbl,
p_asset_assignment_tbl => l_u_assets_tbl,
p_txn_rec => p_transaction_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('source instance updated successfully. instance_id :'||l_u_instance_rec.instance_id);
SELECT quantity,
object_version_number
INTO l_dest_instance_qty,
l_object_version_number
FROM csi_item_instances
WHERE instance_id = l_dest_instance_id;
l_parties_tbl.DELETE;
l_pty_accts_tbl.DELETE;
l_current_procedure := 'update_item_instance';
p_api_name => 'update_item_instance');
/* non serial destination instance update */
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_u_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => p_transaction_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('destination instance updated successfully. instance_id: '||l_dest_instance_id);
END IF; -- destination instance found/not [CREATE/UPDATE]
SELECT object_version_number,
quantity,
active_end_date
INTO l_object_version_number,
l_src_instance_qty,
l_active_end_date
FROM csi_item_instances
WHERE instance_id = l_src_instance_id;
l_u_parties_tbl.DELETE;
l_u_pty_accts_tbl.DELETE;
l_u_org_units_tbl.DELETE;
l_u_ea_values_tbl.DELETE;
l_u_pricing_tbl.DELETE;
l_u_assets_tbl.DELETE;
p_api_name => 'update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_u_instance_rec,
p_party_tbl => l_u_parties_tbl,
p_account_tbl => l_u_pty_accts_tbl,
p_org_assignments_tbl => l_u_org_units_tbl,
p_ext_attrib_values_tbl => l_u_ea_values_tbl,
p_pricing_attrib_tbl => l_u_pricing_tbl,
p_asset_assignment_tbl => l_u_assets_tbl,
p_txn_rec => p_transaction_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('source instance updated successfully. instance id : '||l_u_instance_rec.instance_id);
SELECT instance_id,
object_version_number,
active_end_date,
location_type_code,
instance_usage_code
INTO l_returned_instance_id,
l_object_version_number,
l_active_end_date,
l_location_type_code,
l_instance_usage_code
FROM csi_item_instances
WHERE inventory_item_id = l_instance_rec.inventory_item_id
AND serial_number = l_instance_rec.serial_number;
p_api_name => 'update_item_instance');
csi_item_instance_pub.update_item_instance(
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_init_msg_list => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_instance_rec => l_instance_rec,
p_party_tbl => l_parties_tbl,
p_account_tbl => l_pty_accts_tbl,
p_org_assignments_tbl => l_org_units_tbl,
p_ext_attrib_values_tbl => l_ea_values_tbl,
p_pricing_attrib_tbl => l_pricing_tbl,
p_asset_assignment_tbl => l_assets_tbl,
p_txn_rec => p_transaction_rec,
x_instance_id_lst => l_instance_ids_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
debug('returned product updated successfully. instance id: '||l_instance_rec.instance_id);
END IF; -- l_process_mode = 'UPDATE'
SELECT cii.active_end_date
FROM csi_item_instances cii
WHERE cii.instance_id = p_instance_id
AND cii.active_end_date is not null
AND EXISTS (
SELECT 'X' from csi_ii_relationships cir
WHERE cir.subject_id = p_instance_id
AND cir.relationship_type_code = 'COMPONENT-OF'
AND sysdate BETWEEN nvl(cir.active_start_date, sysdate-1)
AND nvl(cir.active_end_date, sysdate+1) );
select cil.lock_id,cil.lock_status,
cil.config_inst_rev_num
into l_lock_id,l_lock_status,
l_locked_inst_rev_num
from CSI_ITEM_INSTANCE_LOCKS cil
where cil.instance_id = p_instance_id
and cil.lock_status <> 0;
select config_inst_hdr_id,config_inst_item_id,config_inst_rev_num,
instance_usage_code,active_end_date
into l_instance_inst_hdr_id,l_instance_inst_item_id,
l_instance_inst_rev_num,l_instance_usage_code,l_instance_end_date
from CSI_ITEM_INSTANCES
where instance_id = p_instance_id;
Update CSI_T_TXN_LINE_DETAILS
Set changed_instance_id = p_instance_id
,overriding_csi_txn_id = p_csi_txn_rec.transaction_id
Where config_inst_hdr_id = l_instance_inst_hdr_id
and config_inst_item_id = l_instance_inst_item_id
and config_inst_rev_num = l_locked_inst_rev_num
and nvl(processing_status,'$#$') = 'SUBMIT';
l_unlock_inst_tbl.DELETE;