[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 object_version_number
FROM csi_item_instances
WHERE instance_id = pc_instance_id;
SELECT mpi.physical_inventory_id physical_inventory_id,
mpi.physical_inventory_name physical_inventory_name,
mpit.tag_number tag_number
FROM mtl_physical_adjustments mpa,
mtl_physical_inventories mpi,
mtl_physical_inventory_tags mpit
WHERE mpa.physical_inventory_id = mpi.physical_inventory_id
AND mpa.physical_inventory_id = mpit.physical_inventory_id
AND mpa.adjustment_id = mpit.adjustment_id
AND mpa.adjustment_id = pc_physical_adjustment_id;
SELECT mcch.cycle_count_header_id cycle_count_header_id,
mcch.cycle_count_header_name cycle_count_header_name
FROM mtl_cycle_count_entries mcce, mtl_cycle_count_headers mcch
WHERE mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.cycle_count_entry_id = pc_cycle_count_entry_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');
debug('Update Serialized Item which is OUT NOCOPY Of Service');
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 := 1;
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.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.inventory_revision := l_mtl_item_tbl(j).revision;
l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
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.instance_usage_code := l_in_inventory;
l_update_instance_rec.active_end_date := NULL;
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.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_update_instance_rec.instance_status_id := l_src_instance_header_tbl(i).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;
IF NVL(l_update_instance_rec.instance_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR CSI_Item_Instance_vld_pvt.val_inst_ter_flag(l_update_instance_rec.instance_status_id) THEN
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('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;
debug('Update of Item instance that is '||l_src_instance_header_tbl(i).instance_usage_code);
debug('Update Item Instance is: '||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('Update Serialized Item which is :'||l_src_instance_header_tbl(i).instance_usage_code);
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 := 1;
l_update_instance_rec.inv_subinventory_name := l_mtl_item_tbl(j).subinventory_code;
l_update_instance_rec.mfg_serial_number_flag := 'Y';
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.inventory_revision := l_mtl_item_tbl(j).revision;
l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
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.instance_usage_code := l_in_inventory;
l_update_instance_rec.active_end_date := NULL;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_update_instance_rec.install_location_type_code := NULL;
l_update_instance_rec.install_location_id := NULL;
l_update_instance_rec.instance_status_id := l_src_instance_header_tbl(i).instance_status_id;
SELECT owner_party_id
INTO l_owner_party_id
FROM csi_item_instances
WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
l_party_tbl.delete;
SELECT owner_party_id
INTO l_owner_party_id
FROM csi_item_instances
WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
l_party_tbl.delete;
l_account_tbl.delete;
l_pricing_attrib_tbl.delete;
l_org_assignments_tbl.delete;
l_asset_assignment_tbl.delete;
IF NVL(l_update_instance_rec.instance_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR CSI_Item_Instance_vld_pvt.val_inst_ter_flag(l_update_instance_rec.instance_status_id) THEN
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('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;
debug('Update of Item instance that is '||l_src_instance_header_tbl(i).instance_usage_code); --code added for bug #5868111
debug('Update Item Instance is: '||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('Update the Non-Serialized, In-Inventory Item Instance record');
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 := NULL;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_update_instance_rec.instance_status_id := l_src_instance_header_tbl(i).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;
l_update_instance_rec.instance_status_id := FND_API.G_MISS_NUM;
IF NVL(l_update_instance_rec.instance_status_id, FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR CSI_Item_Instance_vld_pvt.val_inst_ter_flag(l_update_instance_rec.instance_status_id) THEN
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('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;
debug('Item Instance Updated: '||l_update_instance_rec.instance_id);
debug('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;
SELECT pod.po_header_id po_header_id,
pod.po_line_id po_line_id,
pol.line_num po_line_number,
poh.segment1 po_number,
pol.unit_price unit_price,
poh.currency_code currency_code
FROM po_distributions_all pod,
po_headers_all poh,
po_lines_all pol
WHERE pod.po_distribution_id = pc_po_distribution_id
AND pod.po_header_id = poh.po_header_id
AND pod.po_line_id = pol.po_line_id
AND poh.po_header_id = pol.po_header_id;
SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
SELECT object_version_number
FROM csi_item_instances
WHERE instance_id = pc_instance_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');
debug('Update Serialized Item which is :'||l_src_instance_header_tbl(i).instance_usage_code);
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 := 1;
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.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.inventory_revision := l_mtl_item_tbl(j).revision;
l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
l_update_instance_rec.instance_usage_code := l_in_inventory;
l_update_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
l_update_instance_rec.active_end_date := NULL;
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.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;
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('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;
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
debug('Update Serialized Item which is :'||l_src_instance_header_tbl(i).instance_usage_code);
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 := 1;
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.mfg_serial_number_flag := 'Y';
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.inventory_revision := l_mtl_item_tbl(j).revision;
l_update_instance_rec.lot_number := l_mtl_item_tbl(j).lot_number;
l_update_instance_rec.inv_locator_id := l_mtl_item_tbl(j).locator_id;
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.instance_usage_code := l_in_inventory;
l_update_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
l_update_instance_rec.active_end_date := NULL;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_update_instance_rec.install_location_type_code := NULL;
l_update_instance_rec.install_location_id := NULL;
SELECT owner_party_id
INTO l_owner_party_id
FROM csi_item_instances
WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
l_party_tbl.delete;
SELECT owner_party_id
INTO l_owner_party_id
FROM csi_item_instances
WHERE instance_id = l_src_instance_header_tbl(i).instance_id;
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('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;
debug('After update of Out of Enterprise Item Instance');
debug('Update Item Instance is: '||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 := 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 := NULL;
l_update_instance_rec.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_update_instance_rec.last_po_po_line_id := r_po_info.po_line_id; --5184815
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('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;
debug('After Update item instance');
debug('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;
SELECT instance_status_id
FROM csi_instance_statuses
WHERE name = FND_PROFILE.VALUE('CSI_DEFAULT_INSTANCE_STATUS');
SELECT mpi.physical_inventory_id physical_inventory_id,
mpi.physical_inventory_name physical_inventory_name,
mpit.tag_number tag_number
FROM mtl_physical_adjustments mpa,
mtl_physical_inventories mpi,
mtl_physical_inventory_tags mpit
WHERE mpa.physical_inventory_id = mpi.physical_inventory_id
AND mpa.physical_inventory_id = mpit.physical_inventory_id
AND mpa.adjustment_id = mpit.adjustment_id
AND mpa.adjustment_id = pc_physical_adjustment_id;
SELECT mcch.cycle_count_header_id cycle_count_header_id,
mcch.cycle_count_header_name cycle_count_header_name
FROM mtl_cycle_count_entries mcce, mtl_cycle_count_headers mcch
WHERE mcce.cycle_count_header_id = mcch.cycle_count_header_id
AND mcce.cycle_count_entry_id = pc_cycle_count_entry_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');
SELECT 'Y' INTO l_return_item
FROM sys.dual
WHERE EXISTS (
SELECT 1 FROM csi_item_instances
WHERE serial_number = l_mtl_item_tbl(j).serial_number
AND inventory_item_id = l_mtl_item_tbl(j).inventory_item_id
AND (instance_usage_code = 'RETURNED'
OR (instance_usage_code = 'IN_TRANSIT' AND active_end_date IS NOT NULL)));
ELSE -- Non Serialized Instances Found so Update
debug('Update source record for non seralized item');
debug('Update Source Non Serialized item: '||l_src_instance_header_tbl(i).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.active_end_date := NULL;
l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - abs(l_mtl_item_tbl(i).primary_quantity);
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;
debug('Before Update Non Serialized Item Instance');
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 Id: '||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 Non Serialzied Item Instance');
debug('You encountered an error in the csi_item_instance_pub.update_item_instance API '||l_msg_data);
IF l_src_instance_header_tbl.count = 1 THEN -- Serialized Records found so update
debug('Updating Serialized Item Instance');
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.object_version_number := l_src_instance_header_tbl(i).object_version_number;
l_update_instance_rec.quantity := 1;
l_update_instance_rec.active_end_date := NULL;
l_update_instance_rec.inv_subinventory_name := NULL;
l_update_instance_rec.inv_locator_id := NULL;
l_update_instance_rec.location_type_code := 'INTERNAL_SITE';
l_update_instance_rec.instance_usage_code := 'OUT_OF_SERVICE';
SELECT nvl(location_id,NULL)
INTO l_update_instance_rec.location_id
FROM hr_all_organization_units
WHERE organization_id = l_src_instance_header_tbl(i).vld_organization_id;
l_update_instance_rec.inv_organization_id := NULL;
l_update_instance_rec.active_end_date := l_sysdate;
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 of Serialized 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;
debug('After Update of Serialized Item Instance');
debug('Updated Item 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('Update source record for serial tagged item');
debug('Update Source serial tagged item: '||l_src_instance_header_tbl(i).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.active_end_date := NULL;
l_update_instance_rec.quantity := l_src_instance_header_tbl(i).quantity - 1;
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;
debug('Before Update Serial Tagged Item Instance');
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 Id: '||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 Serial Tagged Item Instance');
debug('Source Instance Update Completed. Check for destination Instance');
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 of Serial Tagged Item Instance');
csi_item_instance_pub.update_item_instance(l_api_version,
l_commit,
l_init_msg_list,
l_validation_level,
l_new_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 of Serialized Item Instance');
debug('Updated Item Instance: '||l_new_dest_instance_rec.instance_id);
debug('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;
select transaction_quantity
from mtl_material_transactions
where transaction_id = p_transaction_id;
l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
select transaction_quantity
from mtl_material_transactions
where transaction_id = p_transaction_id;
l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
SELECT
mmt.inventory_item_id inventory_item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinventory_code,
mmt.transfer_organization_id transfer_organization_id,
mmt.transfer_subinventory transfer_subinventory,
mmt.revision revision,
mmt.transaction_quantity transaction_quantity,
mmt.primary_quantity primary_quantity,
mmt.transaction_uom transaction_uom,
msib.primary_uom_code primary_uom_code,
mmt.transaction_type_id transaction_type_id,
mmt.transaction_action_id transaction_action_id,
mmt.transaction_source_id transaction_source_id,
mmt.transaction_source_type_id transaction_source_type_id,
mmt.transfer_locator_id transfer_locator_id,
mmt.locator_id locator_id,
mmt.source_project_id source_project_id,
mmt.source_task_id source_task_id,
mmt.project_id from_project_id,
mmt.task_id from_task_id,
mmt.to_project_id to_project_id,
mmt.to_task_id to_task_id,
mmt.transaction_date transaction_date,
mmt.last_updated_by last_updated_by,
mut.serial_number serial_number,
NULL lot_number,
msi.location_id subinv_location_id,
rt.po_distribution_id po_distribution_id,
haou.location_id hr_location_id,
mmt.shipment_number shipment_number,
mmt.trx_source_line_id trx_source_line_id,
mmt.move_order_line_id move_order_line_id,
msib.serial_number_control_code serial_number_control_code,
msib.lot_control_code lot_control_code,
msib.revision_qty_control_code revision_qty_control_code,
msib.comms_nl_trackable_flag comms_nl_trackable_flag,
msib.location_control_code location_control_code,
mmt.ship_to_location_id ship_to_location_id,
mmt.physical_adjustment_id physical_adjustment_id,
mmt.cycle_count_id cycle_count_id,
nvl(msib.eam_item_type,0) eam_item_type, --included for R12,eAM integration
mmt.rcv_transaction_id rcv_transaction_id,
mmt.transfer_transaction_id transfer_transaction_id
FROM
mtl_system_items_b msib,
mtl_serial_numbers msn,
mtl_unit_transactions mut,
mtl_secondary_inventories msi,
hr_all_organization_units haou,
rcv_transactions rt,
mtl_material_transactions mmt
WHERE
mmt.transaction_id = p_transaction_id AND
mmt.inventory_item_id = msib.inventory_item_id AND
mmt.organization_id = msib.organization_id AND
msib.lot_control_code <> 2 AND
mmt.rcv_transaction_id = rt.transaction_id(+) AND
mmt.organization_id = haou.organization_id(+) AND
mmt.subinventory_code = msi.secondary_inventory_name(+) AND
mmt.organization_id = msi.organization_id(+) AND
mmt.transaction_id = mut.transaction_id(+) AND
mut.inventory_item_id = msn.inventory_item_id(+) AND
mut.serial_number = msn.serial_number(+)
UNION ALL
SELECT
mmt.inventory_item_id inventory_item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinventory_code,
mmt.transfer_organization_id transfer_organization_id,
mmt.transfer_subinventory transfer_subinventory,
mmt.revision revision,
mtln.transaction_quantity transaction_quantity,
mtln.primary_quantity primary_quantity,
mmt.transaction_uom transaction_uom,
msib.primary_uom_code primary_uom_code,
mmt.transaction_type_id transaction_type_id,
mmt.transaction_action_id transaction_action_id,
mmt.transaction_source_id transaction_source_id,
mmt.transaction_source_type_id transaction_source_type_id,
mmt.transfer_locator_id transfer_locator_id,
mmt.locator_id locator_id,
mmt.source_project_id source_project_id,
mmt.source_task_id source_task_id,
mmt.project_id from_project_id,
mmt.task_id from_task_id,
mmt.to_project_id to_project_id,
mmt.to_task_id to_task_id,
mmt.transaction_date transaction_date,
mmt.last_updated_by last_updated_by,
mut.serial_number serial_number,
mtln.lot_number lot_number,
msi.location_id subinv_location_id,
rt.po_distribution_id po_distribution_id,
haou.location_id hr_location_id,
mmt.shipment_number shipment_number,
mmt.trx_source_line_id trx_source_line_id,
mmt.move_order_line_id move_order_line_id,
msib.serial_number_control_code serial_number_control_code,
msib.lot_control_code lot_control_code,
msib.revision_qty_control_code revision_qty_control_code,
msib.comms_nl_trackable_flag comms_nl_trackable_flag,
msib.location_control_code location_control_code,
mmt.ship_to_location_id ship_to_location_id,
mmt.physical_adjustment_id physical_adjustment_id,
mmt.cycle_count_id cycle_count_id,
nvl(msib.eam_item_type,0) eam_item_type, --included for R12,eAM integration
mmt.rcv_transaction_id rcv_transaction_id,
mmt.transfer_transaction_id transfer_transaction_id
FROM
mtl_system_items_b msib,
mtl_serial_numbers msn,
mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_secondary_inventories msi,
hr_all_organization_units haou,
rcv_transactions rt,
mtl_material_transactions mmt
WHERE
mmt.transaction_id = p_transaction_id AND
mmt.inventory_item_id = msib.inventory_item_id AND
mmt.organization_id = msib.organization_id AND
msib.lot_control_code = 2 AND
mmt.rcv_transaction_id = rt.transaction_id(+) AND
mmt.organization_id = haou.organization_id(+) AND
mmt.subinventory_code = msi.secondary_inventory_name(+) AND
mmt.organization_id = msi.organization_id(+) AND
mmt.transaction_id = mtln.transaction_id(+) AND
mtln.serial_transaction_id = mut.transaction_id(+) AND
mut.inventory_item_id = msn.inventory_item_id(+) AND
mut.serial_number = msn.serial_number(+);
x_mtl_item_tbl(i).last_updated_by := r_items.last_updated_by;
SELECT
mmt.inventory_item_id inventory_item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinventory_code,
mmt.transfer_organization_id transfer_organization_id,
mmt.transfer_subinventory transfer_subinventory,
mmt.revision revision,
mmt.transaction_quantity transaction_quantity,
mmt.primary_quantity primary_quantity,
mmt.transaction_uom transaction_uom,
msib.primary_uom_code primary_uom_code,
mmt.transaction_type_id transaction_type_id,
mmt.transaction_action_id transaction_action_id,
mmt.transaction_source_id transaction_source_id,
mmt.transaction_source_type_id transaction_source_type_id,
mmt.transfer_locator_id transfer_locator_id,
mmt.locator_id locator_id,
mmt.source_project_id source_project_id,
mmt.source_task_id source_task_id,
mmt.project_id from_project_id,
mmt.task_id from_task_id,
mmt.to_project_id to_project_id,
mmt.to_task_id to_task_id,
mmt.transaction_date transaction_date,
mmt.last_updated_by last_updated_by,
msn.serial_number serial_number,
NULL lot_number,
msi.location_id subinv_location_id,
rt.po_distribution_id po_distribution_id,
haou.location_id hr_location_id,
mmt.shipment_number shipment_number,
mmt.trx_source_line_id trx_source_line_id,
mmt.move_order_line_id move_order_line_id,
msib.serial_number_control_code serial_number_control_code,
-- msib.lot_control_code lot_control_code,---- Commented for bug#14835893
nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),lot_control_code) lot_control_code,-- Added for bug#14835893
msib.revision_qty_control_code revision_qty_control_code,
msib.comms_nl_trackable_flag comms_nl_trackable_flag,
msib.location_control_code location_control_code,
mmt.ship_to_location_id ship_to_location_id,
mmt.physical_adjustment_id physical_adjustment_id,
mmt.cycle_count_id cycle_count_id,
nvl(msib.eam_item_type,0) eam_item_type, --included for R12,eAM integration
mmt.rcv_transaction_id rcv_transaction_id,
mmt.transfer_transaction_id transfer_transaction_id
FROM
mtl_system_items_b msib,
mtl_serial_numbers msn,
mtl_unit_transactions mut,
mtl_secondary_inventories msi,
hr_all_organization_units haou,
rcv_transactions rt,
mtl_material_transactions mmt
WHERE
mmt.transaction_id = p_transaction_id AND
mmt.inventory_item_id = msib.inventory_item_id AND
mmt.organization_id = msib.organization_id AND
--msib.lot_control_code <> 2 AND
nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),msib.lot_control_code) <>2 AND -- Added for bug#14835893
mmt.rcv_transaction_id = rt.transaction_id(+) AND
mmt.organization_id = haou.organization_id(+) AND
mmt.subinventory_code = msi.secondary_inventory_name(+) AND
mmt.organization_id = msi.organization_id(+) AND
mmt.transaction_id = mut.transaction_id(+) AND
mut.inventory_item_id = msn.inventory_item_id(+) AND
mut.serial_number = msn.serial_number(+)
UNION ALL
SELECT
mmt.inventory_item_id inventory_item_id,
mmt.organization_id organization_id,
mmt.subinventory_code subinventory_code,
mmt.transfer_organization_id transfer_organization_id,
mmt.transfer_subinventory transfer_subinventory,
mmt.revision revision,
mtln.transaction_quantity transaction_quantity,
mtln.primary_quantity primary_quantity,
mmt.transaction_uom transaction_uom,
msib.primary_uom_code primary_uom_code,
mmt.transaction_type_id transaction_type_id,
mmt.transaction_action_id transaction_action_id,
mmt.transaction_source_id transaction_source_id,
mmt.transaction_source_type_id transaction_source_type_id,
mmt.transfer_locator_id transfer_locator_id,
mmt.locator_id locator_id,
mmt.source_project_id source_project_id,
mmt.source_task_id source_task_id,
mmt.project_id from_project_id,
mmt.task_id from_task_id,
mmt.to_project_id to_project_id,
mmt.to_task_id to_task_id,
mmt.transaction_date transaction_date,
mmt.last_updated_by last_updated_by,
msn.serial_number serial_number,
mtln.lot_number lot_number,
msi.location_id subinv_location_id,
rt.po_distribution_id po_distribution_id,
haou.location_id hr_location_id,
mmt.shipment_number shipment_number,
mmt.trx_source_line_id trx_source_line_id,
mmt.move_order_line_id move_order_line_id,
msib.serial_number_control_code serial_number_control_code,
-- msib.lot_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
msib.revision_qty_control_code revision_qty_control_code,
msib.comms_nl_trackable_flag comms_nl_trackable_flag,
msib.location_control_code location_control_code,
mmt.ship_to_location_id ship_to_location_id,
mmt.physical_adjustment_id physical_adjustment_id,
mmt.cycle_count_id cycle_count_id,
nvl(msib.eam_item_type,0) eam_item_type, --included for R12,eAM integration
mmt.rcv_transaction_id rcv_transaction_id,
mmt.transfer_transaction_id transfer_transaction_id
FROM
mtl_system_items_b msib,
mtl_serial_numbers msn,
mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_secondary_inventories msi,
hr_all_organization_units haou,
rcv_transactions rt,
mtl_material_transactions mmt
WHERE
mmt.transaction_id = p_transaction_id AND
mmt.inventory_item_id = msib.inventory_item_id AND
mmt.organization_id = msib.organization_id AND
-- msib.lot_control_code = 2 AND
nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),msib.lot_control_code) =2 AND -- Added for bug#14835893
mmt.rcv_transaction_id = rt.transaction_id(+) AND
mmt.organization_id = haou.organization_id(+) AND
mmt.subinventory_code = msi.secondary_inventory_name(+) AND
mmt.organization_id = msi.organization_id(+) AND
mmt.transaction_id = mtln.transaction_id(+) AND
mtln.serial_transaction_id = mut.transaction_id(+) AND
mut.inventory_item_id = msn.inventory_item_id(+) AND
mut.serial_number = msn.serial_number(+);
x_mtl_item_tbl(i).last_updated_by := r_items.last_updated_by;
SELECT DISTINCT asset_creation_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id =
(select organization_id
from mtl_system_items
where inventory_item_id=p_inventory_item_id
and rownum=1)
AND enabled_flag = 'Y'
AND nvl (start_date_active, l_sysdate) <= l_sysdate
AND nvl (end_date_active, l_sysdate+1) > l_sysdate;
SELECT asset_creation_code
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND enabled_flag = 'Y'
AND nvl (start_date_active, l_sysdate) <= l_sysdate
AND nvl (end_date_active, l_sysdate+1) > l_sysdate;
SELECT DISTINCT 'Y'
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id =
(select organization_id
from mtl_system_items
where inventory_item_id=P_inventory_item_id
and rownum =1)
AND enabled_flag = 'Y'
AND nvl (start_date_active, l_sysdate) <= l_sysdate
AND nvl (end_date_active, l_sysdate+1) > l_sysdate
AND comms_nl_trackable_flag = 'Y';
SELECT negative_inv_receipt_Code
FROM mtl_parameters
WHERE organization_id = pc_org_id;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
FUNCTION Init_Instance_Update_Rec RETURN CSI_DATASTRUCTURES_PUB.Instance_Rec IS
l_Instance_Rec CSI_DATASTRUCTURES_PUB.Instance_Rec;
END Init_Instance_Update_Rec;
SELECT lookup_code
FROM CSI_Lookups
WHERE lookup_Type = 'CSI_PARTY_SOURCE_TABLE'
AND lookup_code = 'HZ_PARTIES';
SELECT IPA_Relation_Type_Code
FROM CSI_IPA_Relation_Types
WHERE Upper(IPA_Relation_Type_Code) = 'OWNER';
SELECT ctt.Transaction_Type_Id Transaction_Type_Id
FROM CSI_Txn_Types ctt,
FND_Application fa
WHERE ctt.Source_Transaction_Type = P_Txn_Type
AND fa.application_id = ctt.Source_Application_ID
AND fa.Application_Short_Name = P_App_Short_Name;
SELECT Source_Transaction_Type
FROM CSI_Txn_Types
WHERE Transaction_Type_Id = P_Txn_Id;
SELECT lookup_code
FROM csi_lookups
WHERE lookup_type = 'CSI_INST_LOCATION_SOURCE_CODE'
AND lookup_code = upper(P_Location_Meaning);
SELECT src_status_id
FROM csi_txn_sub_types
WHERE transaction_type_id = p_transaction_id
AND default_flag = 'Y';
FUNCTION Get_Fnd_Employee_Id(P_Last_Updated IN NUMBER) RETURN NUMBER IS
l_employee_id NUMBER;
SELECT employee_id
FROM fnd_user
WHERE user_id = p_last_updated;
SELECT IPA_Relation_Type_Code
FROM CSI_IPA_Relation_Types
WHERE Upper(IPA_Relation_Type_Code) = 'OWNER';
SELECT transaction_type_id
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
l_trx_error_rec.error_stage := csi_inv_trxs_pkg.g_ib_update;
csi_txn_errors_pkg.insert_row(
px_transaction_error_id => x_transaction_error_id,
p_transaction_id => fnd_api.g_miss_num,
p_message_id => l_trx_error_rec.message_id,
p_error_text => l_trx_error_rec.error_text,
p_source_type => l_trx_error_rec.source_type,
p_source_id => l_trx_error_rec.source_id,
p_processed_flag => l_trx_error_rec.processed_flag,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1,
p_transaction_type_id => l_trx_error_rec.transaction_type_id ,
p_source_group_ref => l_trx_error_rec.source_group_ref,
p_source_group_ref_id => l_trx_error_rec.source_group_ref_id ,
p_source_header_ref => l_trx_error_rec.source_header_ref ,
p_source_header_ref_id => l_trx_error_rec.source_header_ref_id ,
p_source_line_ref => l_trx_error_rec.source_line_ref ,
p_source_line_ref_id => l_trx_error_rec.source_line_ref_id ,
p_source_dist_ref_id1 => l_trx_error_rec.source_dist_ref_id1 ,
p_source_dist_ref_id2 => l_trx_error_rec.source_dist_ref_id2 ,
p_inv_material_transaction_id => l_trx_error_rec.inv_material_transaction_id,
p_error_stage => l_trx_error_rec.error_stage,
p_message_string => l_trx_error_rec.message_string,
p_instance_id => l_trx_error_rec.instance_id,
p_inventory_item_id => l_trx_error_rec.inventory_item_id,
p_serial_number => l_trx_error_rec.serial_number,
p_lot_number => l_trx_error_rec.lot_number,
p_transaction_error_date => l_trx_error_rec.transaction_error_date,
p_src_serial_num_ctrl_code => l_trx_error_rec.src_serial_num_ctrl_code,
p_src_location_ctrl_code => l_trx_error_rec.src_location_ctrl_code,
p_src_lot_ctrl_code => l_trx_error_rec.src_lot_ctrl_code,
p_src_rev_qty_ctrl_code => l_trx_error_rec.src_rev_qty_ctrl_code,
p_dst_serial_num_ctrl_code => l_trx_error_rec.dst_serial_num_ctrl_code,
p_dst_location_ctrl_code => l_trx_error_rec.dst_location_ctrl_code,
p_dst_lot_ctrl_code => l_trx_error_rec.dst_lot_ctrl_code,
p_dst_rev_qty_ctrl_code => l_trx_error_rec.dst_rev_qty_ctrl_code,
p_comms_nl_trackable_flag => l_trx_error_rec.comms_nl_trackable_flag
);
csi_txn_errors_pkg.insert_row(
px_transaction_error_id => x_transaction_error_id,
p_transaction_id => fnd_api.g_miss_num,
p_message_id => l_trx_error_rec.message_id,
p_error_text => SQLERRM,
p_source_type => l_trx_error_rec.source_type,
p_source_id => l_trx_error_rec.source_id,
p_processed_flag => l_trx_error_rec.processed_flag,
p_created_by => fnd_global.user_id,
p_creation_date => SYSDATE,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => SYSDATE,
p_last_update_login => fnd_global.conc_login_id,
p_object_version_number => 1,
p_transaction_type_id => l_trx_error_rec.transaction_type_id ,
p_source_group_ref => l_trx_error_rec.source_group_ref,
p_source_group_ref_id => l_trx_error_rec.source_group_ref_id ,
p_source_header_ref => l_trx_error_rec.source_header_ref ,
p_source_header_ref_id => l_trx_error_rec.source_header_ref_id ,
p_source_line_ref => l_trx_error_rec.source_line_ref ,
p_source_line_ref_id => l_trx_error_rec.source_line_ref_id ,
p_source_dist_ref_id1 => l_trx_error_rec.source_dist_ref_id1 ,
p_source_dist_ref_id2 => l_trx_error_rec.source_dist_ref_id2 ,
p_inv_material_transaction_id => l_trx_error_rec.inv_material_transaction_id,
p_error_stage => l_trx_error_rec.error_stage,
p_message_string => l_trx_error_rec.message_string,
p_instance_id => l_trx_error_rec.instance_id,
p_inventory_item_id => l_trx_error_rec.inventory_item_id,
p_serial_number => l_trx_error_rec.serial_number,
p_lot_number => l_trx_error_rec.lot_number,
p_transaction_error_date => l_trx_error_rec.transaction_error_date,
p_src_serial_num_ctrl_code => l_trx_error_rec.src_serial_num_ctrl_code,
p_src_location_ctrl_code => l_trx_error_rec.src_location_ctrl_code,
p_src_lot_ctrl_code => l_trx_error_rec.src_lot_ctrl_code,
p_src_rev_qty_ctrl_code => l_trx_error_rec.src_rev_qty_ctrl_code,
p_dst_serial_num_ctrl_code => l_trx_error_rec.dst_serial_num_ctrl_code,
p_dst_location_ctrl_code => l_trx_error_rec.dst_location_ctrl_code,
p_dst_lot_ctrl_code => l_trx_error_rec.dst_lot_ctrl_code,
p_dst_rev_qty_ctrl_code => l_trx_error_rec.dst_rev_qty_ctrl_code,
p_comms_nl_trackable_flag => l_trx_error_rec.comms_nl_trackable_flag
);
SELECT 'Y' redeploy_flag
FROM csi_transactions ct
,csi_item_instances_h ciih
,csi_item_instances cii
WHERE ct.transaction_id = ciih.transaction_id
AND ciih.instance_id = cii.instance_id
AND cii.inventory_item_id = p_inventory_item_id
AND cii.serial_number = p_serial_number
AND ct.transaction_date < NVL(p_transaction_date, SYSDATE)
AND ct.transaction_type_id IN (l_out_of_sev, l_proj_insev,
l_issue_hz, l_misc_issue_hz) ;*/
SELECT /*+ ordered */
'Y' redeploy_flag
FROM csi_item_instances cii
,csi_item_instances_h ciih
,csi_transactions ct
WHERE ct.transaction_id = ciih.transaction_id
AND ciih.instance_id = cii.instance_id
AND cii.inventory_item_id = p_inventory_item_id
AND cii.serial_number = p_serial_number
AND ct.transaction_date < NVL(p_transaction_date, SYSDATE)
AND ct.transaction_type_id IN (l_out_of_sev, l_proj_insev,
l_issue_hz, l_misc_issue_hz) ;
SELECT transaction_id,
inventory_item_id,
transaction_quantity,
source_code,
transaction_action_id,
transaction_type_id,
transaction_source_type_id,
ship_to_location_id
FROM mtl_material_transactions
WHERE transaction_id = p_transaction_id;
SELECT type_class,
transaction_source_type_id,
nvl(location_required_flag,'N') location_required_flag
FROM mtl_trx_types_view
WHERE transaction_type_id = pc_transaction_type_id;
SELECT 'Y' INTO l_returned_item
FROM sys.dual
WHERE EXISTS (
SELECT 1 FROM csi_item_instances
WHERE serial_number = l_mtl_item_tbl(table_index).serial_number
AND inventory_item_id = l_mtl_item_tbl(table_index).inventory_item_id
AND (instance_usage_code = 'RETURNED'
OR (instance_usage_code = 'IN_TRANSIT' AND active_end_date IS NOT NULL)));