[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_item_location(
p_x_parts_rec In Out Nocopy ahl_parts_rec_type,
p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
x_return_status Out Nocopy Varchar2);
PROCEDURE Update_Material_Return (p_return_mtl_txn_id IN NUMBER,
p_workorder_id IN NUMBER,
p_Item_Instance_Id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2);
PROCEDURE update_csi_item_instance(
p_instance_rec IN csi_datastructures_pub.instance_rec,
p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
X_Return_Status Out NOCOPY Varchar2
);
update_item_location(
p_x_parts_rec => p_x_parts_rec_tbl(i),
p_x_csi_transaction_rec => l_csi_transaction_rec,
x_return_status => x_return_status);
AHL_PART_CHANGES_PKG.insert_row(
X_ROWID => l_row_id,
X_PART_CHANGE_ID => p_x_parts_rec_tbl(i).part_change_txn_id,
X_UNIT_CONFIG_HEADER_ID =>p_x_parts_rec_tbl(i).unit_config_header_id,
X_REMOVED_INSTANCE_ID => p_x_parts_rec_tbl(i).removed_instance_id,
--X_MC_RELATIONSHIP_ID => p_x_parts_rec_tbl(i).mc_relationship_id,
X_MC_RELATIONSHIP_ID => l_path_position_id,
X_REMOVAL_CODE => p_x_parts_rec_tbl(i).removal_code,
--X_STATUS_ID => p_x_parts_rec_tbl(i).Condition_id,
X_REMOVAL_REASON_ID => p_x_parts_rec_tbl(i).removal_reason_id,
X_INSTALLED_INSTANCE_ID => p_x_parts_rec_tbl(i).installed_instance_id,
X_WORKORDER_OPERATION_ID => p_x_parts_rec_tbl(i).workorder_operation_id,
X_OBJECT_VERSION_NUMBER => 1,
--X_COLLECTION_ID => p_x_parts_rec_tbl(i).collection_id,
--X_WORKORDER_MTL_TXN_ID => p_x_parts_rec_tbl(i).material_txn_id,
--X_NON_ROUTINE_WORKORDER_ID => null,
X_REMOVAL_DATE => p_x_parts_rec_tbl(i).removal_date,
X_INSTALLATION_DATE => p_x_parts_rec_tbl(i).Installation_Date,
X_ISSUE_MTL_TXN_ID => l_issue_mtl_txn_id,
X_RETURN_MTL_TXN_ID => null,
X_PART_CHANGE_TYPE => l_part_change_type,
X_CREATION_DATE => sysdate,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_ATTRIBUTE_CATEGORY =>null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 =>null,
X_ATTRIBUTE3 =>null,
X_ATTRIBUTE4 =>null,
X_ATTRIBUTE5 =>null,
X_ATTRIBUTE6 =>null,
X_ATTRIBUTE7 =>null,
X_ATTRIBUTE8 =>null,
X_ATTRIBUTE9 =>null,
X_ATTRIBUTE10 =>null,
X_ATTRIBUTE11 =>null,
X_ATTRIBUTE12 =>null,
X_ATTRIBUTE13 =>null,
X_ATTRIBUTE14 =>null,
X_ATTRIBUTE15 =>null,
X_QUANTITY => l_part_change_qty);
select 'x'
from ahl_unit_config_headers
where unit_config_header_id = p_uc_header_id
and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
select workorder_operation_id
from ahl_workorder_operations
where workorder_id= p_wo_id
and operation_sequence_num = p_op_seq_num;
select job_status_code, organization_id , wip_entity_id
--from ahl_workorders_v
from ahl_workorder_tasks_v
where workorder_id = p_wo_id;
select status_code
from ahl_visits_vl
where visit_id = p_visit_id;
select inventory_location_id
from mtl_item_locations_kfv
where concatenated_segments = p_locator_code
and organization_id =p_org_id;
select cii.wip_job_id,
(select wip_entity_name
from wip_entities
where wip_entity_id = cii.wip_job_id) wip_job_name
from csi_item_instances cii
where cii.instance_id= l_item_instance_id
--and location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
and cii.location_type_code = 'WIP'
and trunc(sysdate) < trunc(nvl(cii.active_end_date, sysdate+1))
and cii.quantity > 0;
select inventory_item_id, instance_number
from csi_item_instances
where instance_id= p_item_instance_id
and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
Select 'x'
From mtl_system_items_b
where inventory_item_id = p_inventory_item_id
and organization_id = p_org_id
and enabled_flag = 'Y';
SELECT inventory_item_id,
inv_master_organization_id,
lot_number,
quantity,
unit_of_measure,
inventory_revision,
serial_number
FROM csi_item_instances
WHERE instance_id = p_instance_id;
select unit_config_header_id
from ahl_unit_config_headers
where name = p_uc_name;
select instance_id
from csi_item_instances
where instance_number= p_instance_num;
select status_id
from mtl_material_statuses
where status_code = p_condition;
select reason_id
from mtl_transaction_reasons
where reason_name = p_Reason_Name;
select lookup_code
from fnd_lookup_values_vl
where meaning = p_meaning
and lookup_type= 'AHL_REMOVAL_CODE';
select lookup_code
from fnd_lookup_values_vl
where meaning = p_meaning
and lookup_type= 'REQUEST_PROBLEM_CODE';
select visit_id from ahl_visits_vl
where visit_number = p_visit_number;
select unit_config_header_id, unit_config_status_code,active_uc_status_code
from ahl_unit_config_headers
where name = p_uc_name
and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
select location_type_code, f.meaning
from csi_item_instances csi, csi_lookups f
where csi.location_type_code = f.lookup_code
and f.lookup_type = p_csi_location_type
and instance_id= p_item_instance_id
and TRUNC(SYSDATE) < TRUNC(NVL(active_end_date, SYSDATE+1));
SELECT
NVL(VST.ITEM_INSTANCE_ID, VTS.INSTANCE_ID)
FROM
AHL_WORKORDERS AWOS,
AHL_VISITS_B VST,
AHL_VISIT_TASKS_B VTS
WHERE
AWOS.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
VST.VISIT_ID = VTS.VISIT_ID AND
WORKORDER_ID = p_workorder_id;
AHL_Util_UC_Pkg.GetCSI_Transaction_ID('UC_UPDATE',l_transaction_type_id, l_return_val);
SELECT 'X'
FROM mtl_system_items_b mtl, csi_item_instances csi
WHERE csi.instance_id = p_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
AND mtl.serial_number_control_code = 1;
SELECT QUANTITY from csi_item_instances
WHERE INSTANCE_ID = p_instance_id;
select instance_number,object_Version_number
from csi_item_instances CII
where CII.instance_id = p_instance_id;
select wip_entity_id from ahl_workorders
where workorder_id = p_workorder_id;
SELECT C1.instance_id FROM CSI_ITEM_INSTANCES C1, CSI_ITEM_INSTANCES C2
WHERE C1.INV_MASTER_ORGANIZATION_ID= C2.INV_MASTER_ORGANIZATION_ID
AND C1.INVENTORY_ITEM_ID = C2.INVENTORY_ITEM_ID
AND NVL(C1.INVENTORY_REVISION,'x') = NVL(C2.INVENTORY_REVISION,'x')
AND NVL(C1.LOT_NUMBER,'x') = NVL(C2.LOT_NUMBER,'x')
AND C1.WIP_JOB_ID= p_wip_job_id
AND C1.unit_of_measure = C2.unit_of_measure
AND C2.instance_id = p_instance_id
AND C1.quantity > 0
AND C1.ACTIVE_START_DATE <= SYSDATE
AND ((C1.ACTIVE_END_DATE IS NULL) OR (C1.ACTIVE_END_DATE > SYSDATE));
update_item_location(
p_x_parts_rec => p_x_parts_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status);
update_item_location(
p_x_parts_rec => p_x_parts_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status
);
AHL_DEBUG_PUB.debug('update_item_location :: ' ||l_dest_instance_id);
/*update_item_location(
p_x_parts_rec => p_x_parts_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status
);
update_item_location(
p_x_parts_rec => p_x_parts_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status
);
update_item_location(
p_x_parts_rec => p_x_parts_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status);
SELECT 'X'
FROM mtl_system_items_b mtl, csi_item_instances csi
WHERE csi.instance_id = p_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
AND mtl.serial_number_control_code = 1;
SELECT QUANTITY from csi_item_instances
WHERE INSTANCE_ID = p_instance_id;
select instance_number,object_Version_number
from csi_item_instances CII
where CII.instance_id = p_instance_id;
select wip_entity_id from ahl_workorders
where workorder_id = p_workorder_id;
SELECT C1.instance_id FROM CSI_ITEM_INSTANCES C1, CSI_ITEM_INSTANCES C2
WHERE C1.INV_MASTER_ORGANIZATION_ID= C2.INV_MASTER_ORGANIZATION_ID
AND C1.INVENTORY_ITEM_ID = C2.INVENTORY_ITEM_ID
AND NVL(C1.INVENTORY_REVISION,'x') = NVL(C2.INVENTORY_REVISION,'x')
AND NVL(C1.LOT_NUMBER,'x') = NVL(C2.LOT_NUMBER,'x')
AND C1.WIP_JOB_ID= p_wip_job_id
AND C1.unit_of_measure = C2.unit_of_measure
AND C2.instance_id = p_instance_id
AND C1.quantity > 0
AND C1.ACTIVE_START_DATE <= SYSDATE
AND ((C1.ACTIVE_END_DATE IS NULL) OR (C1.ACTIVE_END_DATE > SYSDATE));
CSI_ITEM_INSTANCE_PUB. update_item_instance (
p_api_version =>1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_instance_rec => l_instance_rec
,p_ext_attrib_values_tbl=>l_extend_attrib_values_tbl
,p_party_tbl =>l_party_tbl
,p_account_tbl => l_account_tbl
,p_pricing_attrib_tbl => l_pricing_attrib_tbl
,p_org_assignments_tbl => l_org_assignments_tbl
,p_asset_assignment_tbl => l_asset_assignment_tbl
,p_txn_rec => p_x_csi_transaction_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status
);
AHL_DEBUG_PUB.debug('Sunil : Updated installed instance qty to : ' || p_x_parts_rec.Installed_Quantity);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status
);
AHL_DEBUG_PUB.debug('Sunil : Updated fake installed instance qty to : 0 ');
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status
);
AHL_DEBUG_PUB.debug('Sunil : Updated qty on config to : ' || l_instance_rec.quantity);
update_item_location(
p_x_parts_rec => p_x_parts_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status);
update_item_location(
p_x_parts_rec => p_x_parts_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status
);
AHL_DEBUG_PUB.debug('update_item_location :: ' ||l_dest_instance_id);
/*update_item_location(
p_x_parts_rec => p_x_parts_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status);
csi_ii_relationships_pub.update_relationship(
p_api_version => 1.0
,p_relationship_tbl => l_csi_relationship_tbl
,p_txn_rec => p_x_csi_transaction_rec
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
update_item_location(
p_x_parts_rec => p_x_parts_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_return_status => x_return_status);
Procedure Update_item_location(p_x_parts_rec IN out nocopy ahl_parts_rec_type,
p_x_csi_transaction_rec IN out nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
X_Return_Status Out NOCOPY Varchar2)
IS
--
l_wip_entity_type number;
Select AHL.wip_entity_id, WIP.entity_type
--FROM ahl_workorders_v AHL, wip_entities WIP
FROM ahl_search_workorders_v AHL, wip_entities WIP
WHERE AHL.workorder_id = p_wo_id
and WIP.wip_entity_id = AHL.wip_entity_id;
select object_Version_number
from csi_item_instances
where instance_id = p_instance_id;
select wip_location_id
from csi_install_parameters ;
CSI_ITEM_INSTANCE_PUB. update_item_instance (
p_api_version =>1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_instance_rec => l_instance_rec
,p_ext_attrib_values_tbl=>l_extend_attrib_values_tbl
,p_party_tbl =>l_party_tbl
,p_account_tbl => l_account_tbl
,p_pricing_attrib_tbl => l_pricing_attrib_tbl
,p_org_assignments_tbl => l_org_assignments_tbl
,p_asset_assignment_tbl => l_asset_assignment_tbl
,p_txn_rec => p_x_csi_transaction_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
END Update_item_location;
SELECT CSI.INVENTORY_ITEM_ID, AHL.organization_id
, CSI.inventory_revision revision, CSI.quantity, CSI.unit_of_measure,
CSI.serial_number, CSI.lot_number
--FROM ahl_workorders_v AHL, csi_item_instances CSI
FROM ahl_workorder_tasks_v AHL, csi_item_instances CSI
Where CSI.instance_id = p_instance_id
--and csi.inv_organization_id = ahl.organization_id
And AHL.workorder_id = p_wo_id;
AHL_DEBUG_PUB.debug('inside update part changes call');
AHL_PART_CHANGES_PKG.update_row(
X_PART_CHANGE_ID => p_x_parts_rec_tbl(i).part_change_txn_id,
X_UNIT_CONFIG_HEADER_ID=>p_x_parts_rec_tbl(i).unit_config_header_id,
X_REMOVED_INSTANCE_ID => p_x_parts_rec_tbl(i).removed_instance_id,
X_MC_RELATIONSHIP_ID => p_x_parts_rec_tbl(i).mc_relationship_id,
X_REMOVAL_CODE => p_x_parts_rec_tbl(i).removal_code,
X_STATUS_ID => p_x_parts_rec_tbl(i).Condition_id,
X_REMOVAL_REASON_ID => p_x_parts_rec_tbl(i).removal_reason_id,
X_INSTALLED_INSTANCE_ID => p_x_parts_rec_tbl(i).installed_instance_id,
X_WORKORDER_OPERATION_ID => p_x_parts_rec_tbl(i).workorder_operation_id,
X_OBJECT_VERSION_NUMBER => 2,
X_COLLECTION_ID => p_x_parts_rec_tbl(i).collection_id,
X_WORKORDER_MTL_TXN_ID => p_x_parts_rec_tbl(i).material_txn_id,
X_NON_ROUTINE_WORKORDER_ID => p_x_parts_rec_tbl(i).nonroutine_wo_id,
X_REMOVAL_DATE => p_x_parts_rec_tbl(i).removal_date,
X_INSTALLATION_DATE => p_x_parts_rec_tbl(i).INSTALLATION_DATE,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null
);
SELECT workorder_mtl_txn_id
FROM ahl_workorder_mtl_txns mt, ahl_workorder_operations woo
WHERE mt.WORKORDER_OPERATION_ID = woo.WORKORDER_OPERATION_ID
AND TRANSACTION_TYPE_ID = 35 -- issues.
AND woo.workorder_id = p_workorder_id
ORDER by mt.TRANSACTION_DATE DESC, mt.LAST_UPDATE_DATE DESC;
PROCEDURE Update_Material_Return (p_return_mtl_txn_id IN NUMBER,
p_workorder_id IN NUMBER,
p_Item_Instance_Id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
-- To get latest removal or swap parts change txn.
CURSOR ahl_part_chg_csr (p_Item_Instance_Id IN NUMBER,
p_workorder_id IN NUMBER) IS
SELECT pc.part_change_id, pc.object_version_number
FROM ahl_part_changes pc, ahl_workorder_operations woo
WHERE pc.WORKORDER_OPERATION_ID = woo.WORKORDER_OPERATION_ID
AND pc.removed_instance_id = p_item_instance_id
AND woo.workorder_id = p_workorder_id
AND pc.return_mtl_txn_id IS NULL
ORDER by pc.LAST_UPDATE_DATE DESC
FOR UPDATE OF return_mtl_txn_id;
UPDATE ahl_part_changes
SET return_mtl_txn_id = p_return_mtl_txn_id,
object_version_number = l_object_version_number + 1
WHERE CURRENT OF ahl_part_chg_csr;
END Update_Material_Return;
SELECT disp.WORKORDER_ID, disp.INSTANCE_ID, disp.CONDITION_ID,
DISP.QUANTITY, DISP.UOM, disp.WO_OPERATION_ID, disp.item_revision revision,
disp.serial_number, disp.lot_number,
csi.inventory_item_id, vst.organization_id, vst.inv_locator_id,
loc.subinventory_code, awo.operation_sequence_num
FROM ahl_prd_dispositions_b disp, csi_item_instances csi,
ahl_workorders wo, ahl_visits_b vst,
mtl_item_locations_kfv loc, ahl_workorder_operations awo
WHERE disp.instance_id = csi.instance_id
AND disp.part_change_id = p_part_change_id
AND disp.workorder_id = wo.workorder_id
AND wo.visit_id = vst.visit_id
AND vst.inv_locator_id = loc.inventory_location_id(+)
AND vst.organization_id = loc.organization_id(+)
AND awo.workorder_operation_id = disp.WO_OPERATION_ID;
SELECT employee_id
from FND_USER
WHERE user_id = p_user_id;
FND_MSG_PUB.Delete_Msg(i);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => l_csi_transaction_rec,
x_Return_Status => X_Return_Status
);
'serialized move: update_csi_item_instance returned error'
);
Select AHL.wip_entity_id,job_status_code
FROM ahl_search_workorders_v AHL
WHERE AHL.workorder_id = p_wo_id;
Select AHL.wip_entity_id,job_status_code
FROM ahl_search_workorders_v AHL
WHERE AHL.JOB_NUMBER = p_wo_number;
select instance_id,object_Version_number,serial_number,quantity,wip_job_id
from csi_item_instances CII
where CII.instance_id = p_instance_id
AND CII.ACTIVE_START_DATE <= SYSDATE
AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)));
select instance_id, object_Version_number,serial_number,quantity,wip_job_id
from csi_item_instances CII
where CII.instance_number = p_instance_number
AND CII.ACTIVE_START_DATE <= SYSDATE
AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)));
SELECT 'X'
FROM mtl_system_items_b mtl, csi_item_instances csi
WHERE csi.instance_id = p_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND NVL(csi.inv_organization_id, csi.inv_master_organization_id) = mtl.organization_id
AND mtl.serial_number_control_code = 1;
SELECT 'x' FROM ahl_workorders A,ahl_workorders B WHERE
A.wip_entity_id = p_from_wip_entity
AND B.wip_entity_id = p_to_wip_entity
AND A.visit_id = B.visit_id;
PROCEDURE update_csi_item_instance(
p_instance_rec IN csi_datastructures_pub.instance_rec,
p_x_csi_transaction_rec In Out Nocopy CSI_DATASTRUCTURES_PUB.transaction_rec,
X_Return_Status Out NOCOPY Varchar2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_csi_item_instance';
select wip_location_id
from csi_install_parameters ;*/
SELECT 'x'
FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
AND NVL(CI1.SERIAL_NUMBER,'x') = NVL(CI2.SERIAL_NUMBER,'x')
AND CI1.WIP_JOB_ID= CI2.WIP_JOB_ID
AND CI1.instance_id <> p_source_instance_id
AND CI2.instance_id = p_source_instance_id
AND CI1.LOCATION_TYPE_CODE='WIP'
AND CI1.INSTANCE_USAGE_CODE = 'IN_WIP'
AND CI1.unit_of_measure = CI2.unit_of_measure
AND CI1.ACTIVE_START_DATE <= SYSDATE
AND (CI1.ACTIVE_END_DATE IS NULL OR CI1.ACTIVE_END_DATE < SYSDATE)
AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CI1.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
CSI_ITEM_INSTANCE_PUB. update_item_instance (
p_api_version =>1.0
,p_commit => fnd_api.g_false
,p_init_msg_list => fnd_api.g_false
,p_validation_level => fnd_api.g_valid_level_full
,p_instance_rec => l_instance_rec
,p_ext_attrib_values_tbl=>l_extend_attrib_values_tbl
,p_party_tbl =>l_party_tbl
,p_account_tbl => l_account_tbl
,p_pricing_attrib_tbl => l_pricing_attrib_tbl
,p_org_assignments_tbl => l_org_assignments_tbl
,p_asset_assignment_tbl => l_asset_assignment_tbl
,p_txn_rec => p_x_csi_transaction_rec
,x_instance_id_lst => l_instance_id_lst
,x_return_status => x_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
END update_csi_item_instance;
SELECT instance_id,
instance_number,
inventory_item_id,
inv_master_organization_id,
lot_number,
quantity,
unit_of_measure,
install_date,
inventory_revision,
object_version_number,
wip_job_id,
location_id
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT instance_id,
instance_number,
inventory_item_id,
inv_master_organization_id,
lot_number,
quantity,
unit_of_measure,
install_date,
inventory_revision,
object_version_number,
wip_job_id,
location_id FROM CSI_ITEM_INSTANCES CII
WHERE INV_MASTER_ORGANIZATION_ID= p_inv_master_org_id
AND INVENTORY_ITEM_ID = p_inventory_item_id
AND WIP_JOB_ID= p_wip_job_id
AND instance_id <> p_source_instance_id
AND LOCATION_TYPE_CODE='WIP'
AND INSTANCE_USAGE_CODE='IN_WIP'
--AND LOCATION_ID= p_location_id
AND unit_of_measure = p_unit_of_measure
AND ACTIVE_START_DATE <= SYSDATE
AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
SELECT 'x'
FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
AND CI1.WIP_JOB_ID= p_wip_job_id
AND CI1.instance_id <> p_source_instance_id
AND CI2.instance_id = p_source_instance_id
AND CI1.LOCATION_TYPE_CODE='WIP'
--AND CI1.INSTANCE_USAGE_CODE IS NOT NULL
AND NVL(CI1.INSTANCE_USAGE_CODE,'x') <> 'UNUSABLE'
--AND CI1.quantity = 0
AND CI1.unit_of_measure = CI2.unit_of_measure
AND CI1.ACTIVE_START_DATE <= SYSDATE
AND CI1.ACTIVE_END_DATE IS NOT NULL AND CI1.ACTIVE_END_DATE < SYSDATE
AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CI1.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
SELECT 'x' from ahl_visit_tasks_b VST, ahl_workorders WO where
VST.instance_id = p_instance_id
AND VST.visit_task_id = WO.visit_task_id
AND VST.visit_id = WO.visit_id
AND WO.status_code NOT IN ('4','5','7','12','17','22');
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_Return_Status => X_Return_Status
);
'Item in destination: dest Non Serialized update_csi_item_instance returned error '
);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_Return_Status => X_Return_Status
);
'Item in destination: source Non Serialized update_csi_item_instance returned error '
);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_Return_Status => X_Return_Status
);
'Item in destination: dest Non Serialized update_csi_item_instance returned error '
);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_Return_Status => X_Return_Status
);
'Item in destination: source Non Serialized update_csi_item_instance returned error '
);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_Return_Status => X_Return_Status
);
'Item NOT in destination: Full Move update_csi_item_instance returned error '
);
update_csi_item_instance(
p_instance_rec => l_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_Return_Status => X_Return_Status
);
'Item NOT in destination: Partial Move/Update Source qty update_csi_item_instance returned error '
);
SELECT instance_id,
object_version_number,
INVENTORY_REVISION,
LOT_NUMBER,
LOCATION_ID,
INSTANCE_STATUS_ID
FROM csi_item_instances
WHERE instance_id = p_instance_id;
SELECT CI1.instance_id
FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
AND CI1.WIP_JOB_ID= p_wip_job_id
AND CI1.instance_id <> CI2.instance_id
AND CI2.instance_id = p_source_instance_id
AND CI1.LOCATION_TYPE_CODE='WIP'
AND CI1.INSTANCE_USAGE_CODE = 'IN_WIP'
--AND CI1.quantity = 0
AND CI1.unit_of_measure = CI2.unit_of_measure
AND CI1.ACTIVE_START_DATE <= SYSDATE
AND CI1.ACTIVE_END_DATE IS NOT NULL AND CI1.ACTIVE_END_DATE < SYSDATE
AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CI1.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
SELECT CI1.instance_id
FROM CSI_ITEM_INSTANCES CI1, CSI_ITEM_INSTANCES CI2
WHERE CI1.INV_MASTER_ORGANIZATION_ID= CI2.INV_MASTER_ORGANIZATION_ID
AND CI1.INVENTORY_ITEM_ID = CI2.INVENTORY_ITEM_ID
AND NVL(CI1.INVENTORY_REVISION,'x') = NVL(CI2.INVENTORY_REVISION,'x')
AND NVL(CI1.LOT_NUMBER,'x') = NVL(CI2.LOT_NUMBER,'x')
AND CI1.WIP_JOB_ID= p_wip_job_id
AND CI1.instance_id <> CI2.instance_id
AND CI2.instance_id = p_source_instance_id
AND CI1.LOCATION_TYPE_CODE='WIP'
AND NVL(CI1.INSTANCE_USAGE_CODE,'x') <> 'UNUSABLE'
--AND CI1.quantity = 0
AND CI1.unit_of_measure = CI2.unit_of_measure
AND CI1.ACTIVE_START_DATE <= SYSDATE
AND CI1.ACTIVE_END_DATE IS NOT NULL AND CI1.ACTIVE_END_DATE < SYSDATE
AND NOT EXISTS (SELECT 'x' FROM CSI_II_RELATIONSHIPS CIR
WHERE CIR.SUBJECT_ID = CI1.INSTANCE_ID
AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))
AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) );
update_csi_item_instance
(
p_instance_rec => l_dest_instance_rec,
p_x_csi_transaction_rec => p_x_csi_transaction_rec,
x_Return_Status => X_Return_Status
);
'Updating copied instance loc: update_csi_item_instance returned error '
);
SELECT
NVL(VST.ITEM_INSTANCE_ID, VTS.INSTANCE_ID)
FROM
AHL_WORKORDERS AWOS,
AHL_VISITS_B VST,
AHL_VISIT_TASKS_B VTS
WHERE
AWOS.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
VST.VISIT_ID = VTS.VISIT_ID AND
WORKORDER_ID = p_workorder_id;