[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;
SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
SELECT serial_number_control_code,
--lot_control_code,
nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),lot_control_code) lot_control_code,-- Added for bug#14835893
revision_qty_control_code,
location_control_code,
comms_nl_trackable_flag
FROM mtl_system_items_b
WHERE inventory_item_id = pc_item_id
AND organization_id = pc_org_id;
l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_instance_rec.inv_subinventory_name := NULL;
l_update_instance_rec.inv_locator_id := NULL;
l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
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.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
l_update_instance_rec.instance_usage_code := l_in_transit;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
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);
debug('Instance Status - 4: '||l_update_instance_rec.instance_status_id);
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
debug('Before Update Item Instance - 5');
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;
debug('After Update Item Instance - 6');
debug('You are updating Instance: '||l_update_instance_rec.instance_id);
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_instance_rec.inv_subinventory_name := NULL;
l_update_instance_rec.inv_locator_id := 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.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id); --bug9833071
l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
l_update_instance_rec.instance_usage_code := l_in_transit; -- l_out_of_enterprise; Bug 9833071
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
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);
debug('Instance Status - 8: '||l_update_instance_rec.instance_status_id);
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
debug('Before Update Item Instance - 9');
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;
debug('After Update Item Instance - 10');
debug('You are updating Instance: '||l_update_instance_rec.instance_id);
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
IF j = 1 THEN -- Look for IN Transit Non Serial If not there create or Update only 1 time
l_instance_query_rec := csi_inv_trxs_pkg.init_instance_query_rec;
l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
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 + l_quantity;
l_update_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
l_update_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
l_update_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
l_update_dest_instance_rec.inv_organization_id := NULL;
l_update_dest_instance_rec.inv_subinventory_name := l_subinventory_name;
l_update_dest_instance_rec.inv_locator_id := l_locator_id;
l_update_dest_instance_rec.instance_usage_code := l_in_transit;
l_update_dest_instance_rec.active_end_date := NULL;
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);
debug('Before Update Item Instance-16');
debug('Instance Status-11: '||l_update_dest_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_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;
debug('After Update Item Instance-17');
debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
IF l_neg_code = 1 THEN -- Negative Records Allowed so Create/Update
debug('No records were found and Inventory Allows Negative Quantities so create a new Source Instance Record - 8');
debug('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
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;
debug('Before Update Source Item Instance - 13');
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);
debug('After Update Item Instance - 14');
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
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);
debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
debug('Before Update Item Instance - 23');
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;
debug('After Update Item Instance - 24');
debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
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;
SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
SELECT serial_number_control_code,
--lot_control_code,
nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),lot_control_code) lot_control_code,-- Added for bug#14835893
revision_qty_control_code,
location_control_code,
comms_nl_trackable_flag
FROM mtl_system_items_b
WHERE inventory_item_id = pc_item_id
AND organization_id = pc_org_id;
l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
So, mtl_unit_transactions will have the serial number which needs to be updated.
In 11510, This is handled in a different way in bug
*/
l_instance_query_rec.serial_number := l_mtl_item_tbl(j).serial_number;
debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
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_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
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.in_transit_order_line_id := NULL;
l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
l_update_instance_rec.instance_usage_code := l_in_inventory;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_update_instance_rec.lot_number := NULL;
l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
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);
debug('Instance Status - 9801681: '||l_update_instance_rec.instance_status_id);
debug('After you initialize the Update Record Values - 2');
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
debug('Before Update Item Instance - 4');
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;
debug('After Update Item Instance - 5');
debug('You are updating Instance: '||l_update_instance_rec.instance_id);
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
IF j = 1 THEN -- Update Source Since its Non Serialized 1 Time
debug('Update Source 1 time with Transaction Quantity');
debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
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;
debug('Before Update Item Instance - 6');
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);
debug('After Update Item Instance - 7');
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
END IF; -- End of J = 1 If to update Source 1 time
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 := 1;
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_update_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
l_update_dest_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
l_update_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
l_update_dest_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
l_update_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
l_update_dest_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
l_update_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
l_update_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
l_update_dest_instance_rec.instance_usage_code := l_in_inventory;
debug('Usage: '||l_update_dest_instance_rec.instance_usage_code);
debug('VLD Org: '||l_update_dest_instance_rec.vld_organization_id);
debug('INV Org: '||l_update_dest_instance_rec.inv_organization_id);
debug('Subinv Code: '||l_update_dest_instance_rec.inv_subinventory_name);
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);
debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
debug('Before Update Item Instance - 13');
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;
debug('After Update Item Instance - 14');
debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
debug('Update Serialized Item which is :'||l_dest_instance_header_tbl(i).instance_usage_code);
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 := 1;
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_update_dest_instance_rec.inv_master_organization_id := l_master_organization_id;
l_update_dest_instance_rec.inv_organization_id := l_mtl_item_tbl(j).organization_id;
l_update_dest_instance_rec.vld_organization_id := l_mtl_item_tbl(j).organization_id;
l_update_dest_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
l_update_dest_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
l_update_dest_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
l_update_dest_instance_rec.location_id := nvl(l_mtl_item_tbl(j).subinv_location_id,l_mtl_item_tbl(j).hr_location_id);
l_update_dest_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
l_update_dest_instance_rec.instance_usage_code := l_in_inventory;
debug('Usage: '||l_update_dest_instance_rec.instance_usage_code);
debug('VLD Org: '||l_update_dest_instance_rec.vld_organization_id);
debug('INV Org: '||l_update_dest_instance_rec.inv_organization_id);
debug('Subinv Code: '||l_update_dest_instance_rec.inv_subinventory_name);
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);
debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
debug('Before Update Item Instance - 13');
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;
debug('After update of Out of Enterprise Item Instance');
debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
debug('After Update Item Instance - 14');
debug('Instance being updated: '||l_src_instance_header_tbl(k).instance_id);
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.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(j).primary_quantity);
l_update_instance_rec.active_end_date := l_sysdate;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
debug('After you initialize the Update Record Values');
debug('Instance Updated: '||l_update_instance_rec.instance_id);
debug('End Date Passed in: '||to_char(l_update_instance_rec.active_end_date,'DD-MON-YYYY HH24:MI:SS'));
debug('Object Version: '||l_update_instance_rec.object_version_number);
debug('After you initialize the Update Record Values - 19');
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
debug('Before Update Item Instance - 21');
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);
debug('Instance Status - 9801681: '||l_update_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_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;
debug('After Update Item Instance - 22');
debug('You are updating Instance: '||l_update_instance_rec.instance_id);
debug('You are updating Serial Number: '||l_update_instance_rec.serial_number);
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
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);
debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
debug('Before Update Item Instance - 29');
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;
debug('After Update Item Instance - 30');
debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
debug('Instance being updated: '||l_src_instance_header_tbl(k).instance_id);
l_update_instance_rec := csi_inv_trxs_pkg.init_instance_update_rec;
l_update_instance_rec.instance_id := l_src_instance_header_tbl(k).instance_id;
l_update_instance_rec.active_end_date := l_sysdate;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(k).object_version_number;
debug('After you initialize the Update Record Values');
debug('Instance Updated: '||l_update_instance_rec.instance_id);
debug('End Date Passed in: '||to_char(l_update_instance_rec.active_end_date,'DD-MON-YYYY HH24:MI:SS'));
debug('Object Version: '||l_update_instance_rec.object_version_number);
debug('After you initialize the Update Record Values - 19');
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);
debug('Instance Status - 9801681: '||l_update_instance_rec.instance_status_id);
debug('Before Update Item Instance - 21');
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;
debug('After Update Item Instance - 22');
debug('You are updating Instance: '||l_update_instance_rec.instance_id);
debug('You are updating Serial Number: '||l_update_instance_rec.serial_number);
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
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);
debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
debug('Before Update Item Instance - 29');
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;
debug('After Update Item Instance - 30');
debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
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;
debug('Before Update Item Instance - 35');
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);
debug('Instance Status - 9801681: '||l_update_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_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);
debug('After Update Item Instance - 36');
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
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);
debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
debug('Before Update Item Instance - 42');
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;
debug('After Update Item Instance - 43');
debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
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;
SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
SELECT transfer_transaction_id,
transaction_action_id,
transaction_type_id,
transaction_source_type_id,
transaction_quantity
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT serial_number_control_code,
-- lot_control_code,
nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),lot_control_code) lot_control_code,-- Added for bug#14835893
revision_qty_control_code,
location_control_code,
comms_nl_trackable_flag
FROM mtl_system_items_b
WHERE inventory_item_id = pc_item_id
AND organization_id = pc_org_id;
SELECT haou.location_id hr_location_id,
msi.location_id subinv_location_id
FROM hr_all_organization_units haou,
mtl_secondary_inventories msi
WHERE haou.organization_id = pc_org_id
AND msi.organization_id = pc_org_id
AND msi.secondary_inventory_name = pc_subinv_name;
l_employee_id := csi_inv_trxs_pkg.get_fnd_employee_id(l_mtl_item_tbl(i).last_updated_by);
debug('The person who last updated this record: '||l_mtl_item_tbl(i).last_updated_by||' does not exist as a valid employee');
debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_instance_rec.inv_master_organization_id := l_master_organization_id;
l_update_instance_rec.inv_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
l_update_instance_rec.vld_organization_id := l_mtl_item_tbl(j).transfer_organization_id;
l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).transfer_subinventory;
l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).transfer_locator_id;
l_update_instance_rec.location_id := nvl(r_loc_ids.subinv_location_id,r_loc_ids.hr_location_id);
l_update_instance_rec.location_type_code := csi_inv_trxs_pkg.get_location_type_code('Inventory');
l_update_instance_rec.instance_usage_code := l_in_inventory;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
debug('After the update for Direct Shipment is set.');
debug('Transfer Org: '||l_update_instance_rec.inv_organization_id);
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
debug('Before Update Item Instance - 3');
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;
debug('After Update Item Instance - 4');
debug('You are updating Instance: '||l_update_instance_rec.instance_id);
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
debug('Source Recs found so update or unexpire existing Non Serial Instance ');
debug('You will update instance: '||l_src_instance_header_tbl(i).instance_id);
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;
debug('Before Update Source Item Instance - 10');
debug('Before Update Source Item Instance - 11');
debug('Before Update Source Item Instance - 11');
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);
debug('After Update Item Instance - 11');
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
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);
debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
debug('Before Update Item Instance - 19');
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;
debug('After Update Item Instance - 20');
debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
debug('Instance being updated: '||l_src_instance_header_tbl(i).instance_id);
l_update_instance_rec.instance_id := l_src_instance_header_tbl(i).instance_id;
l_update_instance_rec.active_end_date := l_sysdate;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
debug('After you initialize the Update Record Values');
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
debug('Before Update Item Instance - 25');
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;
debug('After Update Item Instance - 26');
debug('You are updating Instance: '||l_update_instance_rec.instance_id);
debug('You are updating Serial Number: '||l_update_instance_rec.serial_number);
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
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);
debug('Instance Status Id: '||l_update_dest_instance_rec.instance_status_id);
debug('Before Update Item Instance - 32');
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;
debug('After Update Item Instance - 33');
debug('You are updating Instance: '||l_update_dest_instance_rec.instance_id);
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;