[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'x'
INTO l_dummy
FROM csi_item_instances
WHERE instance_id = p_item_instance_id
OR instance_number = l_instance_number;
SELECT terminated_flag -- service_order_allowed_flag Bug # 3945813 srramakr
INTO l_flag
FROM csi_instance_statuses
WHERE instance_status_id = p_instance_status_id;
IF upper(l_flag)='Y' -- check for Y while selecting terminated_flag
THEN
l_return_value := TRUE;
SELECT instance_number
INTO l_instance_number
FROM csi_item_instances
WHERE instance_number=p_instance_number;
ELSIF p_mode='UPDATE'
THEN
-- Added for eam integration
BEGIN
SELECT instance_number
INTO l_instance_number
FROM csi_item_instances
WHERE instance_number = p_instance_number
AND instance_id <> p_item_instance_id;
SELECT NVL(comms_nl_trackable_flag,'N') ,NVL(description,' ')
INTO l_temp_string,l_description
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id
AND enabled_flag = 'Y'
AND nvl (start_date_active, sysdate) <= sysdate
AND nvl (end_date_active, sysdate+1) > sysdate;
SELECT revision_qty_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND enabled_flag = 'Y'
AND nvl (start_date_active, sysdate) <= sysdate
AND nvl (end_date_active, sysdate+1) > sysdate;
SELECT 1
INTO l_dummy
FROM mtl_item_revisions
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND revision = p_revision;
SELECT 1
INTO l_dummy
FROM mtl_item_revisions
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND revision = p_revision;
PROCEDURE Update_Revision
(
p_inv_item_id IN NUMBER,
p_inv_org_id IN NUMBER,
p_revision IN VARCHAR2,
l_return_value IN OUT NOCOPY BOOLEAN,
p_rev_control_code IN NUMBER
) IS
l_dummy number; --varchar2(1);
SELECT revision_qty_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND enabled_flag = 'Y'
AND nvl (start_date_active, sysdate) <= sysdate
AND nvl (end_date_active, sysdate+1) > sysdate;
SELECT 1
INTO l_dummy
FROM mtl_item_revisions
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND revision = p_revision;
SELECT 1
INTO l_dummy
FROM mtl_item_revisions
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND revision = p_revision;
END Update_Revision;
select distinct inventory_item_id
from MTL_SYSTEM_ITEMS_B
where base_item_id = p_base_model
and inventory_item_id <> p_inv_item_id;
SELECT *
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inv_id
AND serial_number = p_ser_number;
SELECT serial_number_type, -- serial number uniqueness control
default_cost_group_id
INTO l_serial_type,
l_cst_grp_id
FROM mtl_parameters
WHERE organization_id = p_inv_org_id;
SELECT 'x'
,current_status
,gen_object_id
INTO l_temp
,l_status
,p_gen_object_id
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inv_item_id
AND serial_number = p_serial_number;
select count(*)
into l_exists
from mtl_unit_transactions
where inventory_item_id = p_inv_item_id
and serial_number = p_serial_number
and ROWNUM = 1;
csi_gen_utility_pvt.put_line('Calling INV API to update Serial Number...');
inv_serial_number_pub.updateserial(
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_inventory_item_id => p_inv_item_id
,p_organization_id => p_inv_org_id
,p_serial_number => p_serial_number
,p_initialization_date => l_ser_upd_csr.initialization_date
,p_completion_date => l_ser_upd_csr.completion_date
,p_ship_date => l_ser_upd_csr.ship_date
,p_revision => l_ser_upd_csr.revision
,p_lot_number => l_ser_upd_csr.lot_number
,p_current_locator_id => l_ser_upd_csr.current_locator_id
,p_subinventory_code => l_ser_upd_csr.current_subinventory_code
,p_trx_src_id => l_ser_upd_csr.original_wip_entity_id
,p_unit_vendor_id => l_ser_upd_csr.original_unit_vendor_id
,p_vendor_lot_number => l_ser_upd_csr.vendor_lot_number
,p_vendor_serial_number => l_ser_upd_csr.vendor_serial_number
,p_receipt_issue_type => l_ser_upd_csr.last_receipt_issue_type
,p_txn_src_id => l_ser_upd_csr.last_txn_source_id
,p_txn_src_name => l_ser_upd_csr.last_txn_source_name
,p_txn_src_type_id => l_ser_upd_csr.last_txn_source_type_id
,p_current_status => l_current_status
,p_parent_item_id => l_ser_upd_csr.parent_item_id
,p_parent_serial_number => l_ser_upd_csr.parent_serial_number
,p_serial_temp_id => NULL
,p_last_status => l_status
,p_status_id => NULL
,x_object_id => l_object_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_organization_type => NULL
,p_owning_org_id => NULL
,p_owning_tp_type => NULL
,p_planning_org_id => NULL
,p_planning_tp_type => NULL
,p_transaction_action_id => NULL
);
FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from inv_serial_number_pub.updateserial');
csi_gen_utility_pvt.put_line('Serial Number updated successfully in MTL_SERIAL_NUMBERS..');
select base_item_id
into l_base_item_id
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = p_inv_item_id
and organization_id = p_inv_org_id;
SELECT 'x'
INTO l_temp
FROM mtl_serial_numbers
WHERE inventory_item_id = base_rec.inventory_item_id
AND serial_number = p_serial_number;
SELECT inventory_item_id
INTO l_item_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND current_organization_id = p_inv_org_id;
SELECT 'x'
INTO l_temp
FROM mtl_serial_numbers s,
mtl_parameters p
WHERE s.current_organization_id = p.organization_id
AND s.serial_number = p_serial_number
AND p.serial_number_type = 3
AND ROWNUM = 1;
SELECT inventory_item_id
INTO l_item_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number;
inv_serial_number_pub.insertSerial(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_inventory_item_id => p_inv_item_id,
p_organization_id => p_inv_org_id,
p_serial_number => p_serial_number,
p_initialization_date => SYSDATE,
p_completion_date => SYSDATE, --NULL,
p_ship_date => NULL,
p_revision => NULL, --'A',
p_lot_number => l_lot_number, --NULL,
p_current_locator_id => NULL,
p_subinventory_code => NULL,
p_trx_src_id => NULL,
p_unit_vendor_id => NULL,
p_vendor_lot_number => NULL,
p_vendor_serial_number => NULL,
p_receipt_issue_type => NULL,
p_txn_src_id => NULL,
p_txn_src_name => NULL,
p_txn_src_type_id => NULL,
p_transaction_id => NULL,
p_current_status => l_current_status,
p_parent_item_id => NULL,
p_parent_serial_number => NULL,
p_cost_group_id => l_cst_grp_id,
p_transaction_action_id => NULL,
p_transaction_temp_id => NULL,
p_status_id => NULL,
x_object_id => l_object_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_type => NULL,
p_owning_org_id => NULL,
p_owning_tp_type => NULL,
p_planning_org_id => NULL,
p_planning_tp_type => NULL,
p_wip_entity_id => NULL,
p_operation_seq_num => NULL,
p_intraoperation_step_type => NULL );
FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from inv_serial_number_pub.insertserial');
SELECT serial_number_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND enabled_flag = 'Y'
AND nvl (start_date_active, sysdate) <= sysdate
AND nvl (end_date_active, sysdate+1) > sysdate;
SELECT 'x'
INTO l_found
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inv_item_id
AND serial_number = p_serial_number;
select distinct inventory_item_id
from MTL_SYSTEM_ITEMS_B
where base_item_id = p_base_model
and inventory_item_id <> p_inv_item_id;
SELECT serial_number_type -- serial number uniqueness control
INTO l_serial_type
FROM mtl_parameters
WHERE organization_id = p_inv_org_id;
SELECT 'x'
INTO l_temp
FROM csi_item_instances
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inv_item_id
AND instance_id <> l_instance_id
AND ROWNUM = 1;
select base_item_id
into l_base_item_id
from MTL_SYSTEM_ITEMS_B
where inventory_item_id = p_inv_item_id
and organization_id = p_inv_org_id;
select 'x'
into l_temp
from CSI_ITEM_INSTANCES
where serial_number = p_serial_number
and inventory_item_id in
(
select inventory_item_id
from MTL_SYSTEM_ITEMS_B
where base_item_id = l_base_item_id
and inventory_item_id <> p_inv_item_id
and organization_id = l_master_organization_id
);
SELECT 'x'
INTO l_temp
FROM csi_item_instances
WHERE serial_number = p_serial_number
AND last_vld_organization_id = p_inv_org_id
AND instance_id <> l_instance_id
AND ROWNUM = 1;
SELECT inventory_item_id
INTO l_item_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND current_organization_id = p_inv_org_id;
SELECT 'x'
INTO l_temp
FROM mtl_serial_numbers s,
mtl_parameters p
WHERE s.current_organization_id = p.organization_id
AND s.serial_number = p_serial_number
AND p.serial_number_type = 3
AND ROWNUM = 1;
SELECT 'x'
INTO l_temp
FROM csi_item_instances
WHERE serial_number = p_serial_number
AND instance_id <> l_instance_id
AND ROWNUM=1;
SELECT inventory_item_id
INTO l_item_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number;
SELECT lot_number_uniqueness -- lot number uniqueness control
INTO l_lot_type
FROM mtl_parameters
WHERE organization_id = p_inv_org_id;
SELECT 'x'
INTO l_temp
FROM CSI_ITEM_INSTANCES
WHERE inventory_item_id <> p_inv_item_id
AND lot_number = p_lot_number
AND instance_id <> p_instance_id;
SELECT 'x'
INTO l_temp
FROM MTL_LOT_NUMBERS
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND lot_number = p_lot_number;
inv_lot_api_pub.insertlot (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_inventory_item_id => p_inv_item_id,
p_organization_id => p_inv_org_id,
p_lot_number => p_lot_number,
p_expiration_date => l_expiration_date,
p_transaction_temp_id => NULL,
p_transaction_action_id => NULL,
p_transfer_organization_id => NULL,
x_object_id => l_object_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
FND_MESSAGE.Set_token('ERR_TEXT' , 'Error returned from inv_lot_api_pub.InsertLot Procedure');
SELECT nvl(csi_utl_pkg.get_lot_ctrl_code(p_txn_rec.inv_material_transaction_id),lot_control_code) lot_control_code -- Added for bug#14835893
-- lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND enabled_flag = 'Y'
AND nvl (start_date_active, sysdate) <= sysdate
AND nvl (end_date_active, sysdate+1) > sysdate;
SELECT negative_inv_receipt_code
FROM mtl_parameters
WHERE organization_id = p_inv_organization_id;
SELECT subject_id
INTO l_dummy
FROM csi_ii_relationships
WHERE object_id = p_instance_id
AND nvl(active_end_date,(sysdate+1)) > sysdate -- rajeevk Bug#5686753
and rownum < 2; -- srramakr Bug # 3647609
SELECT 'x'
INTO l_config_found
FROM csi_item_instances
WHERE config_inst_hdr_id = p_config_inst_hdr_id
AND config_inst_item_id = p_config_inst_item_id
AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
ELSIF p_validation_mode='UPDATE'
THEN
*/
l_config_found:=NULL;
SELECT 'x'
INTO l_config_found
FROM csi_item_instances
WHERE config_inst_hdr_id = p_config_inst_hdr_id
AND config_inst_item_id = p_config_inst_item_id
AND instance_id <> p_instance_id
AND (SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE));
SELECT primary_uom_code
INTO to_unit
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND enabled_flag = 'Y'
AND nvl (start_date_active, sysdate) <= sysdate
AND nvl (end_date_active, sysdate+1) > sysdate;
SELECT '1'
INTO l_dummy
FROM mtl_material_statuses
WHERE status_id = p_instance_condition_id;
SELECT '1'
INTO l_dummy
FROM csi_instance_statuses
WHERE instance_status_id = p_instance_status_id;
SELECT '1'
INTO l_dummy
FROM csi_systems_vl
WHERE system_id = p_system_id
AND ( (end_date_active is null) OR -- Fix for bug # 2783027
(end_date_active > sysdate) );
SELECT '1'
INTO l_dummy
FROM csi_lookups
WHERE lookup_code = UPPER(p_instance_type_code)
AND lookup_type = l_inst_lookup_type;
SELECT '1'
INTO l_dummy
FROM csi_lookups
WHERE lookup_code = UPPER(p_inst_usage_code)
AND lookup_type = l_usage_lookup_type;
SELECT '1'
INTO l_dummy
FROM csi_lookups
WHERE lookup_code = UPPER(p_operational_status)
AND lookup_type = l_operational_lookup_type;
SELECT '1'
INTO l_dummy
FROM fnd_currencies
WHERE currency_code = UPPER(p_currency_code);
/* This function checks if status is updateable */
/* by looking through the csi_instance_statuses */
/*---------------------------------------------------------*/
FUNCTION is_status_updateable
(
p_instance_status IN NUMBER,
p_current_status IN NUMBER
)
RETURN BOOLEAN IS
l_change_allowed VARCHAR2(1);
SELECT status_change_allowed_flag
INTO l_change_allowed
FROM csi_instance_statuses
WHERE instance_status_id = p_current_status;
END is_status_updateable;
select serial_number_control_code
into l_serial_code
from mtl_system_items
where inventory_item_id = p_instance_rec.inventory_item_id
and organization_id = p_instance_rec.vld_organization_id;
SELECT '1'
INTO l_count
FROM csi_item_instances a
-- ,csi_i_parties b -- Not required as we have the denormalized column in CII
-- WHERE a.instance_id = b.instance_id
WHERE a.inventory_item_id = p_instance_rec.inventory_item_id
AND a.inv_organization_id = p_instance_rec.inv_organization_id
AND a.inv_subinventory_name = p_instance_rec.inv_subinventory_name
--Added location_type_code for bug 5514442--
AND a.location_type_code = p_instance_rec.location_type_code
AND a.instance_id <> p_instance_rec.instance_id
AND a.rowid <> ( SELECT bb.rowid FROM csi_item_instances bb WHERE bb.instance_id = p_instance_rec.instance_id )
AND a.serial_number IS NULL
AND a.instance_usage_code NOT IN ('IN_RELATIONSHIP','RETURNED')
AND a.active_end_date IS NULL --code added for bug 5702911 --
AND (
(a.inventory_revision IS NULL AND p_instance_rec.inventory_revision IS NULL) OR
(a.inventory_revision IS NULL AND p_instance_rec.inventory_revision = FND_API.G_MISS_CHAR) OR
(a.inventory_revision = p_instance_rec.inventory_revision)
)
AND (
(a.lot_number IS NULL AND p_instance_rec.lot_number IS NULL) OR
(a.lot_number IS NULL AND p_instance_rec.lot_number = FND_API.G_MISS_CHAR) OR
(a.lot_number = p_instance_rec.lot_number)
)
AND (
(a.inv_locator_id IS NULL AND p_instance_rec.inv_locator_id IS NULL) OR
(a.inv_locator_id IS NULL AND p_instance_rec.inv_locator_id = FND_API.G_MISS_NUM) OR
(a.inv_locator_id = p_instance_rec.inv_locator_id)
)
AND (
(a.INSTANCE_STATUS_ID IS NULL AND p_instance_rec.INSTANCE_STATUS_ID IS NULL) OR
(a.INSTANCE_STATUS_ID IS NULL AND p_instance_rec.INSTANCE_STATUS_ID = FND_API.G_MISS_NUM) OR
(a.INSTANCE_STATUS_ID = p_instance_rec.INSTANCE_STATUS_ID)
)
AND a.owner_party_id = p_party_rec.party_id
AND a.owner_party_source_table = p_party_rec.party_source_table
AND a.unit_of_measure = p_instance_rec.unit_of_measure;
SELECT '1'
INTO l_dummy
FROM csi_lookups
WHERE lookup_code = UPPER(p_loc_source_table)
AND lookup_type = l_loc_lookup_type;
/* passed to the update_item_instance */
/*-----------------------------------------------------*/
PROCEDURE get_merge_rec (p_instance_rec IN OUT NOCOPY csi_datastructures_pub.instance_rec,
l_curr_instance_rec IN csi_datastructures_pub.instance_rec,
l_get_instance_rec OUT NOCOPY csi_datastructures_pub.instance_rec
)
IS
BEGIN
--
IF ( p_instance_rec.instance_id = fnd_api.g_miss_num )
THEN l_get_instance_rec.instance_id := l_curr_instance_rec.instance_id;
SELECT csi_item_instances_s.NEXTVAL
INTO l_instance_id
FROM sys.dual;
SELECT csi_item_instances_h_s.NEXTVAL
INTO l_csi_item_instance_h_id
FROM dual;
SELECT 'x'
INTO l_dummy
FROM csi_item_instances
WHERE instance_id = p_Instance_id
AND creation_complete_flag = 'Y';
SELECT object_id
INTO l_dummy
FROM csi_ii_relationships
WHERE subject_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND nvl(active_end_date,(sysdate+1)) > sysdate;
SELECT serial_number_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_inv_org_id
AND enabled_flag = 'Y'
AND nvl (start_date_active, sysdate) <= sysdate
AND nvl (end_date_active, sysdate+1) > sysdate;
SELECT 'x'
INTO l_found
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inv_item_id
AND serial_number = p_serial_number;
FUNCTION Update_Quantity
(
p_instance_id IN NUMBER ,
p_inv_organization_id IN NUMBER ,
p_quantity IN NUMBER ,
--p_serial_number IN VARCHAR2,
p_serial_control_code IN NUMBER ,
p_location_type_code IN VARCHAR2,
p_stack_err_msg IN BOOLEAN
)
RETURN BOOLEAN IS
l_quantity NUMBER;
SELECT negative_inv_receipt_code
FROM mtl_parameters
WHERE organization_id = p_inv_organization_id;
SELECT subject_id
INTO l_dummy
FROM csi_ii_relationships
WHERE object_id = p_instance_id;
END Update_Quantity;
SELECT party_id
INTO l_owner
FROM csi_i_parties
WHERE instance_id = p_instance_id
AND relationship_type_code ='OWNER'
AND (active_end_date >SYSDATE OR active_end_date IS NULL );
SELECT count(*)
INTO l_count
FROM csi_i_assets
WHERE instance_id = p_instance_id
AND (active_end_date >SYSDATE OR active_end_date IS NULL );
SELECT instance_id
INTO l_instance_id
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT active_end_date,
active_start_date
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT MAX(source_transaction_date)
INTO l_txn_date
FROM csi_inst_transactions_v
WHERE instance_id=p_instance_id
AND transaction_id <> p_transaction_id -- Bug 9081875
AND source_transaction_date>p_end_date;
SELECT
INSTANCE_ID ,
INSTANCE_NUMBER ,
EXTERNAL_REFERENCE ,
LAST_VLD_ORGANIZATION_ID ,
INVENTORY_ITEM_ID ,
INVENTORY_REVISION ,
INV_MASTER_ORGANIZATION_ID ,
SERIAL_NUMBER ,
MFG_SERIAL_NUMBER_FLAG ,
LOT_NUMBER ,
QUANTITY ,
UNIT_OF_MEASURE ,
ACCOUNTING_CLASS_CODE ,
INSTANCE_CONDITION_ID ,
INSTANCE_STATUS_ID ,
CUSTOMER_VIEW_FLAG ,
MERCHANT_VIEW_FLAG ,
SELLABLE_FLAG ,
SYSTEM_ID ,
INSTANCE_TYPE_CODE ,
ACTIVE_START_DATE ,
ACTIVE_END_DATE ,
LOCATION_TYPE_CODE ,
LOCATION_ID ,
INV_ORGANIZATION_ID ,
INV_SUBINVENTORY_NAME ,
INV_LOCATOR_ID ,
PA_PROJECT_ID ,
PA_PROJECT_TASK_ID ,
IN_TRANSIT_ORDER_LINE_ID ,
WIP_JOB_ID ,
PO_ORDER_LINE_ID ,
LAST_OE_ORDER_LINE_ID ,
LAST_OE_RMA_LINE_ID ,
LAST_PO_PO_LINE_ID ,
LAST_OE_PO_NUMBER ,
LAST_WIP_JOB_ID ,
LAST_PA_PROJECT_ID ,
LAST_PA_TASK_ID ,
LAST_OE_AGREEMENT_ID ,
INSTALL_DATE ,
MANUALLY_CREATED_FLAG ,
RETURN_BY_DATE ,
ACTUAL_RETURN_DATE ,
CREATION_COMPLETE_FLAG ,
COMPLETENESS_FLAG ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
OBJECT_VERSION_NUMBER ,
instance_usage_code , --Added for bug 2163942
install_location_type_code ,
install_location_id ,
source_code -- Added for bug 7156553, base bug 6990065
INTO
p_instance_rec.INSTANCE_ID ,
p_instance_rec.INSTANCE_NUMBER ,
p_instance_rec.EXTERNAL_REFERENCE ,
p_instance_rec.VLD_ORGANIZATION_ID ,
p_instance_rec.INVENTORY_ITEM_ID ,
p_instance_rec.INVENTORY_REVISION ,
p_instance_rec.INV_MASTER_ORGANIZATION_ID ,
p_instance_rec.SERIAL_NUMBER ,
p_instance_rec.MFG_SERIAL_NUMBER_FLAG ,
p_instance_rec.LOT_NUMBER ,
p_instance_rec.QUANTITY ,
p_instance_rec.UNIT_OF_MEASURE ,
p_instance_rec.ACCOUNTING_CLASS_CODE ,
p_instance_rec.INSTANCE_CONDITION_ID ,
p_instance_rec.INSTANCE_STATUS_ID ,
p_instance_rec.CUSTOMER_VIEW_FLAG ,
p_instance_rec.MERCHANT_VIEW_FLAG ,
p_instance_rec.SELLABLE_FLAG ,
p_instance_rec.SYSTEM_ID ,
p_instance_rec.INSTANCE_TYPE_CODE ,
p_instance_rec.ACTIVE_START_DATE ,
p_instance_rec.ACTIVE_END_DATE ,
p_instance_rec.LOCATION_TYPE_CODE ,
p_instance_rec.LOCATION_ID ,
p_instance_rec.INV_ORGANIZATION_ID ,
p_instance_rec.INV_SUBINVENTORY_NAME ,
p_instance_rec.INV_LOCATOR_ID ,
p_instance_rec.PA_PROJECT_ID ,
p_instance_rec.PA_PROJECT_TASK_ID ,
p_instance_rec.IN_TRANSIT_ORDER_LINE_ID ,
p_instance_rec.WIP_JOB_ID ,
p_instance_rec.PO_ORDER_LINE_ID ,
p_instance_rec.LAST_OE_ORDER_LINE_ID ,
p_instance_rec.LAST_OE_RMA_LINE_ID ,
p_instance_rec.LAST_PO_PO_LINE_ID ,
p_instance_rec.LAST_OE_PO_NUMBER ,
p_instance_rec.LAST_WIP_JOB_ID ,
p_instance_rec.LAST_PA_PROJECT_ID ,
p_instance_rec.LAST_PA_TASK_ID ,
p_instance_rec.LAST_OE_AGREEMENT_ID ,
p_instance_rec.INSTALL_DATE ,
p_instance_rec.MANUALLY_CREATED_FLAG ,
p_instance_rec.RETURN_BY_DATE ,
p_instance_rec.ACTUAL_RETURN_DATE ,
p_instance_rec.CREATION_COMPLETE_FLAG ,
p_instance_rec.COMPLETENESS_FLAG ,
p_instance_rec.CONTEXT ,
p_instance_rec.ATTRIBUTE1 ,
p_instance_rec.ATTRIBUTE2 ,
p_instance_rec.ATTRIBUTE3 ,
p_instance_rec.ATTRIBUTE4 ,
p_instance_rec.ATTRIBUTE5 ,
p_instance_rec.ATTRIBUTE6 ,
p_instance_rec.ATTRIBUTE7 ,
p_instance_rec.ATTRIBUTE8 ,
p_instance_rec.ATTRIBUTE9 ,
p_instance_rec.ATTRIBUTE10 ,
p_instance_rec.ATTRIBUTE11 ,
p_instance_rec.ATTRIBUTE12 ,
p_instance_rec.ATTRIBUTE13 ,
p_instance_rec.ATTRIBUTE14 ,
p_instance_rec.ATTRIBUTE15 ,
p_instance_rec.OBJECT_VERSION_NUMBER ,
p_instance_rec.instance_usage_code ,--Added for bug 2163942
p_instance_rec.install_location_type_code ,
p_instance_rec.install_location_id ,
p_instance_rec.source_code --Added for bug 7156553, base bug 6990065
FROM csi_item_instances
WHERE instance_id = p_item_instance_id;
SELECT attribute_level
INTO p_ATTRIBUTE_LEVEL
FROM csi_i_extended_attribs
WHERE attribute_id = p_ATTRIBUTE_ID;
SELECT '1'
INTO l_dummy
FROM mtl_system_items
WHERE inventory_item_id = p_INVENTORY_ITEM_ID
AND organization_id = p_ORGANIZATION_ID;
SELECT '1'
INTO l_dummy
FROM bom_bill_of_materials
WHERE assembly_item_id = p_INVENTORY_ITEM_ID
AND organization_id = p_ORGANIZATION_ID
AND alternate_bom_designator IS NULL; -- srramakr
SELECT '1'
INTO l_dummy
FROM csi_instance_statuses
WHERE instance_status_id = p_status_id
AND terminated_flag = 'Y';
SELECT '1'
INTO l_dummy
FROM csi_ii_relationships
WHERE object_id = p_instance_id
and ((active_end_date is null) or (active_end_date > sysdate))
and relationship_type_code = 'COMPONENT-OF'
and rownum < 2;
SELECT active_start_date,
active_end_date
FROM csi_item_instances
WHERE instance_id = p_instance_id
and ((active_end_date is null) OR (To_Date(active_end_date,'DD-MM-RRRR HH24:MI') >= To_Date(sysdate,'DD-MM-RRRR HH24:MI'))); -- Bug 8586745
SELECT active_end_date,
active_start_date
FROM csi_item_instances
WHERE instance_id = p_instance_id
and ((active_end_date is null) OR (To_Date(active_end_date,'DD-MM-RRRR HH24:MI') >= To_Date(sysdate,'DD-MM-RRRR HH24:MI'))); -- Bug 8586745
SELECT lookup_code
INTO l_location_source_table
FROM csi_lookups
WHERE lookup_code = upper(p_location_source_table)
AND lookup_type = l_location_lookup_type;
SELECT party_site_id
INTO l_dummy
FROM HZ_PARTY_SITES
WHERE party_site_id = p_location_id;
SELECT location_id
INTO l_dummy
FROM hz_locations
WHERE location_id = p_location_id;
SELECT vendor_site_id
INTO l_dummy
FROM po_vendor_sites_all
WHERE vendor_site_id = p_location_id;
SELECT location_id
INTO l_dummy
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT location_id
INTO l_dummy
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT location_id
INTO l_dummy
FROM hz_locations
WHERE location_id = p_location_id;
SELECT location_id
INTO l_dummy
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT location_id
INTO l_dummy
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT '1'
INTO l_temp_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = p_task_id;
SELECT '1'
INTO l_temp_id
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory AND
organization_id = p_organization_id;
SELECT '1'
INTO l_temp_id
FROM MTL_ITEM_LOCATIONS
WHERE inventory_location_id = p_locator_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory;
SELECT '1'
INTO l_temp_id
FROM wip_entities
WHERE wip_entity_id = p_wip_job_id;
SELECT '1'
INTO l_temp_id
FROM pa_tasks
WHERE project_id = p_project_id
AND task_id = p_task_id;
SELECT '1'
INTO l_temp_id
FROM oe_order_lines_all
WHERE line_id = p_sales_ord_line_id;
SELECT '1'
INTO l_temp_id
FROM po_lines_all
WHERE po_line_id = p_po_line_id;
SELECT object_id,subject_id
FROM csi_ii_relationships
WHERE ( subject_id=p_instance_id
OR object_id=p_instance_id)
AND relationship_type_code ='CONNECTED-TO'
AND SYSDATE BETWEEN NVL(active_start_date, SYSDATE) AND NVL(active_end_date, SYSDATE);
SELECT nvl(ib_item_instance_class,'X')
INTO l_link_type
FROM mtl_system_items_b
WHERE inventory_item_id=p_instance_header_tbl(l_link).inventory_item_id
AND organization_id=p_instance_header_tbl(l_link).vld_organization_id;
SELECT location_id,
location_type_code
INTO l_header_tbl(1).location_id,
l_header_tbl(1).location_type_code
FROM csi_item_instances
WHERE instance_id=l_header_tbl(1).instance_id;
SELECT location_id,
location_type_code
INTO l_header_tbl(2).location_id,
l_header_tbl(2).location_type_code
FROM csi_item_instances
WHERE instance_id=l_header_tbl(2).instance_id;
SELECT msg_id,
msg_code,
msg_status,
body_text,
creation_date,
description
FROM xnp_msgs
WHERE (msg_code like 'CSI%' OR msg_code like 'CSE%')
-- AND nvl(msg_status, 'READY') <> 'PROCESSED' -- COmmented for Bug 3987286
AND msg_status in ('READY','FAILED')
AND msg_creation_date > p_freeze_date
AND creation_date < l_min_creation_date -- Bug 14712665
AND recipient_name is null;
p_pending_txn_tbl.DELETE;
SELECT inv_material_transaction_id
FROM csi_txn_errors
WHERE inv_material_transaction_id IS NOT NULL
AND inv_material_transaction_id IN (p_transaction_id, p_transfer_transaction_id)
AND processed_flag IN ('R','E');
SELECT mut.transaction_id,
msi.lot_control_code,
msi.serial_number_control_code,
msi.primary_uom_code,
mmt.inventory_item_id,
mmt.organization_id,
mmt.transaction_date,
mmt.creation_date,
mmt.transfer_transaction_id,
mmt.transaction_type_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.transaction_quantity,
mmt.transaction_uom,
mmt.primary_quantity,
mmt.transaction_source_id,
mmt.trx_source_line_id,
NULL lot_number
FROM mtl_unit_transactions mut,
mtl_material_transactions mmt,
mtl_system_items msi
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_inventory_item_id
AND mmt.transaction_id = mut.transaction_id
AND msi.inventory_item_id = mut.inventory_item_id
AND msi.organization_id = mmt.organization_id
AND msi.comms_nl_trackable_flag = 'Y'
AND NOT EXISTS
(SELECT 1 FROM csi_transactions
WHERE inv_material_transaction_id = mmt.transaction_id)
UNION ALL
SELECT mut.transaction_id,
msi.lot_control_code,
msi.serial_number_control_code,
msi.primary_uom_code,
mmt.inventory_item_id,
mmt.organization_id,
mmt.transaction_date,
mmt.creation_date,
mmt.transfer_transaction_id,
mmt.transaction_type_id,
mmt.transaction_action_id,
mmt.transaction_source_type_id,
mmt.transaction_quantity,
mmt.transaction_uom,
mmt.primary_quantity,
mmt.transaction_source_id,
mmt.trx_source_line_id,
mtln.lot_number
FROM mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt,
mtl_system_items msi
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_inventory_item_id
AND mtln.organization_id = mut.organization_id
AND mtln.transaction_date = mut.transaction_date
AND mtln.serial_transaction_id = mut.transaction_id
AND mmt.transaction_id = mtln.transaction_id
AND msi.inventory_item_id = mut.inventory_item_id
AND msi.comms_nl_trackable_flag = 'Y'
AND NOT EXISTS
(SELECT 1 FROM csi_transactions
WHERE inv_material_transaction_id = mmt.transaction_id)
ORDER BY 1 desc, 2 desc;
select creation_date
into l_cur_mtl_txn_date
from MTL_MATERIAL_TRANSACTIONS
where transaction_id = l_cur_mtl_txn_id;
select min(transaction_id)
into l_min_txn_id
from csi_item_instances_h
where instance_id = p_instance_rec.instance_id
and creation_date = (select min(creation_date) from csi_item_instances_h
where instance_id = p_instance_rec.instance_id
);
select source_transaction_date
into l_src_txn_date
from csi_transactions
where transaction_id = l_min_txn_id;
select /*+ leading(CSI_INST_TRANSACTIONS_V.cii,CSI_INST_TRANSACTIONS_V.v) index(csi_inst_transactions_v.t CSI_TRANSACTIONS_U01) */ min(inv_material_transaction_id) INTO l_min_inv_mtl_txn_id
from CSI_INST_TRANSACTIONS_V
where instance_id=p_instance_rec.instance_id
AND inv_material_transaction_id is not NULL;
select /*+ leading(CSI_INST_TRANSACTIONS_V.cii,CSI_INST_TRANSACTIONS_V.v) index(csi_inst_transactions_v.t CSI_TRANSACTIONS_U01) */ max(inv_material_transaction_id) INTO l_max_inv_mtl_txn_id
from CSI_INST_TRANSACTIONS_V
where instance_id=p_instance_rec.instance_id
AND inv_material_transaction_id is not NULL;
SELECT creation_date
INTO l_err_mtl_txn_date
FROM mtl_material_transactions
WHERE transaction_id = l_err_mtl_txn_id;
SELECT 'Y'
INTO l_txn_found
FROM csi_transactions
WHERE inv_material_transaction_id in (l_mtl_txn_tbl(l_ind).transaction_id, l_xfer_mtl_txn_id)
AND rownum = 1;
select line.source_transaction_id,line.source_transaction_type_id
into l_src_txn_id,l_src_txn_type_id
from CSI_T_TXN_LINE_DETAILS det,
CSI_T_TRANSACTION_LINES line
where ( (det.instance_id = p_instance_rec.instance_id) OR
(det.inventory_item_id = p_instance_rec.inventory_item_id AND
det.serial_number = p_instance_rec.serial_number) )
and nvl(det.processing_status,'SUBMIT') = 'ERROR'
and det.creation_date < p_txn_rec.source_transaction_date
and det.txn_line_detail_id <> l_txn_line_detail_id
and line.transaction_line_id = det.transaction_line_id
and rownum = 1;
select hdr.order_number,line.line_number
into l_order_number,l_line_number
from oe_order_headers_all hdr
,oe_order_lines_all line
where line.line_id = l_src_txn_id
and hdr.header_id = line.header_id;
select count(*)
into l_recount
from CSI_II_FORWARD_SYNC_TEMP
where instance_id = p_instance_id
and nvl(process_flag,'N') <> l_process_flag
and ROWNUM = 1;
select creation_date
into l_mtl_txn_cr_date
from MTL_MATERIAL_TRANSACTIONS
where transaction_id = l_mtl_txn_id;
select count(*)
into l_recount
from CSI_II_FORWARD_SYNC_TEMP
where instance_id = p_instance_id
and nvl(process_flag,'N') <> l_process_flag
and nvl(mtl_txn_creation_date,l_def_cr_date) < l_mtl_txn_cr_date
and ROWNUM = 1;
select 'x'
into l_exists
from MTL_PARAMETERS
where organization_id = p_master_org_id
and master_organization_id = p_master_org_id;
l_option CZ_CF_API.INPUT_SELECTION;
l_html_pieces.DELETE;
SELECT constraint_type , message
FROM cz_config_messages
WHERE config_hdr_id = p_config_hdr_id
AND config_rev_nbr = p_config_rev_nbr;
l_selection_line_id NUMBER;
SELECT eam_item_type
INTO l_eam
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT ctl.source_transaction_table,
ctl.source_transaction_id
FROM csi_t_txn_line_details ctld,
csi_t_transaction_lines ctl
WHERE ctld.inventory_item_id = p_inventory_item_id
AND ctld.serial_number = p_serial_number
AND nvl(ctld.processing_status, 'SUBMIT') <> 'PROCESSED'
AND ctl.transaction_line_id = ctld.transaction_line_id;
SELECT '1'
FROM csi_instance_interface
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = p_serial_number
AND process_status <> 'P';
select flow_status_code
into l_oe_line_status
from OE_ORDER_LINES_ALL
where line_id = tld_rec.source_transaction_id;
SELECT 'Y'
FROM mtl_object_genealogy mog
WHERE mog.parent_object_type = 2
AND (mog.object_id = p_gen_object_id OR mog.parent_object_id = p_gen_object_id)
AND mog.object_type = 2
AND sysdate BETWEEN nvl(mog.start_date_active, sysdate-1)
AND nvl(mog.end_date_active, sysdate+1)
AND ROWNUM = 1;
select '1'
FROM mtl_unit_transactions mut,
mtl_material_transactions mmt
WHERE mut.inventory_item_id = p_item_id
AND mut.serial_number = p_srl_num
AND mmt.transaction_id = mut.transaction_id
AND mmt.transaction_id <> p_curr_txn_id
AND ROWNUM = 1
UNION --uncommented code for 6965008
select '1'
FROM mtl_unit_transactions mut,
mtl_transaction_lot_numbers mtln,
mtl_material_transactions mmt
WHERE mut.inventory_item_id = p_item_id
AND mut.serial_number = p_srl_num
AND mtln.organization_id = mut.organization_id
AND mtln.serial_transaction_id = mut.transaction_id
AND mmt.transaction_id = mtln.transaction_id
AND mmt.transaction_id <> p_curr_txn_id
AND ROWNUM = 1;
select count(*)
into l_rec_count
from EAM_WORK_ORDER_DETAILS ewod,
WIP_DISCRETE_JOBS wdj
where wdj.wip_entity_id = ewod.wip_entity_id
and wdj.organization_id = ewod.organization_id
and wdj.maintenance_object_type = 3
and wdj.maintenance_object_id = p_instance_rec.instance_id
and wdj.maintenance_object_source = 1
and ROWNUM = 1;
SELECT gen_object_id,current_status --changed for 6176621
INTO l_gen_object_id,
l_current_status
FROM mtl_serial_numbers
WHERE inventory_item_id = p_instance_rec.inventory_item_id
AND serial_number = p_old_serial_number;
select version_label
from CSI_I_VERSION_LABELS
where instance_id = p_instance_id
and date_time_stamp <= l_time_stamp
order by date_time_stamp desc;
SELECT mut.transaction_id,
mut.creation_date,
--msi.lot_control_code,
nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),msi.lot_control_code) lot_control_code, -- Added for bug#14835893
msi.serial_number_control_code,
msi.primary_uom_code
FROM mtl_unit_transactions mut,
mtl_system_items msi
WHERE mut.serial_number = p_serial_number
AND mut.inventory_item_id = p_inventory_item_id
AND msi.organization_id = mut.organization_id
AND msi.inventory_item_id = mut.inventory_item_id
-- need to add this because in a diff ou it this item may not be ib tracked
AND msi.comms_nl_trackable_flag = 'Y'
ORDER BY mut.creation_date desc, mut.transaction_id desc;
SELECT transaction_id,
lot_number
INTO l_mtl_txn_id,
l_lot_number
FROM mtl_transaction_lot_numbers
WHERE serial_transaction_id = unit_txn_rec.transaction_id;
SELECT inventory_item_id,
organization_id,
transaction_date,
creation_date,
transfer_transaction_id,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_quantity,
transaction_uom,
primary_quantity,
transaction_source_id,
trx_source_line_id
INTO l_mtl_txn_tbl(l_ind).inventory_item_id,
l_mtl_txn_tbl(l_ind).organization_id,
l_mtl_txn_tbl(l_ind).transaction_date,
l_mtl_txn_tbl(l_ind).creation_date,
l_mtl_txn_tbl(l_ind).transfer_transaction_id,
l_mtl_txn_tbl(l_ind).transaction_type_id,
l_mtl_txn_tbl(l_ind).transaction_action_id,
l_mtl_txn_tbl(l_ind).transaction_source_type_id,
l_mtl_txn_tbl(l_ind).transaction_quantity,
l_mtl_txn_tbl(l_ind).transaction_uom,
l_mtl_txn_tbl(l_ind).primary_quantity,
l_mtl_txn_tbl(l_ind).transaction_source_id,
l_mtl_txn_tbl(l_ind).trx_source_line_id
FROM mtl_material_transactions
WHERE transaction_id = l_mtl_txn_id;