[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
l_update_source_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
SELECT 1
FROM hz_locations
WHERE location_id = pc_location_id;
SELECT 1
FROM hr_locations
WHERE location_id = pc_location_id;
l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_source_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
l_update_source_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
l_update_source_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
csi_t_gen_utility_pvt.add('Before Update Item Instance');
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_update_source_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
csi_t_gen_utility_pvt.add('After Update Item Instance');
csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
l_update_dest_instance_rec.active_end_date := NULL;
l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
l_update_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
csi_t_gen_utility_pvt.add('Before Update Item Instance - Neg Qty');
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_update_dest_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
l_update_instance_rec.inv_subinventory_name := NULL;
l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
l_update_instance_rec.inv_organization_id := NULL;
l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
l_update_instance_rec.inv_locator_id := NULL;
l_update_instance_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code(l_location_type);
l_update_instance_rec.pa_project_id := NULL;
l_update_instance_rec.pa_project_task_id := NULL;
l_update_instance_rec.instance_usage_code := l_in_service;
l_update_instance_rec.operational_status_code := 'IN_SERVICE';
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
csi_t_gen_utility_pvt.add('Before Update Item Instance');
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_update_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
l_upd_error_instance_id := l_update_instance_rec.instance_id;
csi_t_gen_utility_pvt.add('After Update Item Instance');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
SELECT 1
FROM hz_locations
WHERE location_id = pc_location_id;
SELECT 1
FROM hr_locations
WHERE location_id = pc_location_id;
l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
IF l_src_instance_header_tbl.count = 1 THEN -- Records found so update either Serialized or Non Serialized
IF l_src_instance_header_tbl(i).instance_usage_code in (l_out_of_service,
l_in_inventory,
l_in_wip,
l_installed,
l_in_service,
l_in_process) THEN
IF (l_debug > 0) THEN
csi_t_gen_utility_pvt.add('Source records found so decide which one to update using get_destination_instance procedure ');
l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
l_update_instance_rec.inventory_revision := l_mtl_item_tbl(j).revision;
l_update_instance_rec.quantity := 1;
l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
l_update_instance_rec.pa_project_id := NULL;
l_update_instance_rec.pa_project_task_id := NULL;
l_update_instance_rec.install_location_type_code := NULL;
l_update_instance_rec.install_location_id := NULL;
l_update_instance_rec.instance_usage_code := l_in_inventory;
l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
l_update_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
csi_t_gen_utility_pvt.add('Before Update Item Instance');
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_update_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
l_upd_error_instance_id := l_update_instance_rec.instance_id;
csi_t_gen_utility_pvt.add('After Update Item Instance');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
IF l_src_instance_header_tbl.count > 0 THEN -- Records found so update either Serialized or Non Serialized
csi_t_gen_utility_pvt.add('Since this is Non Serial decide what instances to decrement');
l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(oos).instance_id);
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_upd_src_dest_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
csi_t_gen_utility_pvt.add('After Update Item Instance');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(ins).instance_id);
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_upd_src_dest_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
csi_t_gen_utility_pvt.add('After Update Item Instance');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_upd_src_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
csi_t_gen_utility_pvt.add('Before Update Item Instance: '||l_src_instance_header_tbl(inser).instance_id);
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_upd_src_dest_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
csi_t_gen_utility_pvt.add('After Update Item Instance');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
csi_t_gen_utility_pvt.add('After Update Item Instance');
l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
l_update_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
l_update_instance_rec.active_end_date := NULL;
l_update_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
l_update_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
csi_t_gen_utility_pvt.add('Before Update Item Instance');
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_update_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
l_upd_error_instance_id := l_update_instance_rec.instance_id;
csi_t_gen_utility_pvt.add('After Update Item Instance');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
csi_t_gen_utility_pvt.add('After Update Item Instance');
l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
l_update_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
l_update_instance_rec.active_end_date := NULL;
l_update_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
l_update_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
csi_t_gen_utility_pvt.add('Before Update Item Instance');
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_update_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
l_upd_error_instance_id := l_update_instance_rec.instance_id;
csi_t_gen_utility_pvt.add('After Update Item Instance');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
l_update_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
l_update_dest_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
l_update_source_instance_rec CSI_DATASTRUCTURES_PUB.INSTANCE_REC;
SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
SELECT 1
FROM hz_locations
WHERE location_id = pc_location_id;
SELECT 1
FROM hr_locations
WHERE location_id = pc_location_id;
l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
csi_t_gen_utility_pvt.add('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
csi_t_gen_utility_pvt.add('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
l_update_source_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_source_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_source_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
l_update_source_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
csi_t_gen_utility_pvt.add('Before Update Item Instance - 80');
l_update_source_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
csi_t_gen_utility_pvt.add('Instance Status Id: '||l_update_source_instance_rec.instance_status_id);
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_update_source_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
l_upd_error_instance_id := l_update_source_instance_rec.instance_id;
csi_t_gen_utility_pvt.add('After Update Item Instance - Neg Qty');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
csi_t_gen_utility_pvt.add('You will update instance: '||l_dest_instance_header_tbl(i).instance_id);
l_update_dest_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_dest_instance_rec.instance_id := l_dest_instance_header_tbl(i).instance_id;
l_update_dest_instance_rec.quantity := l_dest_instance_header_tbl(i).quantity + abs(l_mtl_item_tbl(j).primary_quantity);
l_update_dest_instance_rec.active_end_date := NULL;
l_update_dest_instance_rec.object_version_number := l_dest_instance_header_tbl(i).object_version_number;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
l_update_dest_instance_rec.instance_status_id := nvl(csi_inv_trxs_pkg.get_default_status_id(l_txn_rec.transaction_type_id),r_id.instance_status_id);
csi_t_gen_utility_pvt.add('Before Update Transaction - Neg Qty');
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_update_dest_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
l_upd_error_instance_id := l_update_dest_instance_rec.instance_id;
csi_t_gen_utility_pvt.add('After Update Transaction - Neg Qty');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_instance_rec.lot_number := l_src_instance_header_tbl(i).lot_number;
l_update_instance_rec.inv_subinventory_name := NULL;
l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
l_update_instance_rec.inv_organization_id := NULL;
l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
l_update_instance_rec.inv_locator_id := NULL;
l_update_instance_rec.location_id := l_mtl_item_tbl(j).ship_to_location_id;
l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code(l_location_type);
l_update_instance_rec.pa_project_id := NULL;
l_update_instance_rec.pa_project_task_id := NULL;
l_update_instance_rec.instance_usage_code := l_in_service;
l_update_instance_rec.operational_status_code := 'IN_SERVICE';
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
csi_t_gen_utility_pvt.add('Before Update Item Instance');
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_update_instance_rec,
l_ext_attrib_values_tbl,
l_party_tbl,
l_account_tbl,
l_pricing_attrib_tbl,
l_org_assignments_tbl,
l_asset_assignment_tbl,
l_txn_rec,
l_instance_id_lst,
l_return_status,
l_msg_count,
l_msg_data);
l_upd_error_instance_id := l_update_instance_rec.instance_id;
csi_t_gen_utility_pvt.add('After Update Item Instance');
csi_t_gen_utility_pvt.add('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
x_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;