[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_disposition(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_module_type IN VARCHAR2 := NULL,
p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
-- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE update_item_location(p_workorder_id IN NUMBER,
p_instance_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2);
SELECT route_id,
item_instance_id,
scheduled_start_date,
job_status_code,
job_number,
organization_id
FROM ahl_workorders_v
WHERE workorder_id = p_workorder_id;
select WO.route_id route_id,
NVL(VTS.INSTANCE_ID, VST.ITEM_INSTANCE_ID) item_instance_id,
WDJ.SCHEDULED_START_DATE scheduled_start_date,
WO.status_code job_status_code,
WO.workorder_name job_number,
VST.ORGANIZATION_ID organization_id,
VST.VISIT_ID visit_id
from AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WDJ,
AHL_VISITS_VL VST,
AHL_VISIT_TASKS_VL VTS
where WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID and
WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID and
VST.VISIT_ID = VTS.VISIT_ID and
WO.WORKORDER_ID = p_workorder_id;
SELECT --'N' include_flag, Once the column include_flag is added, then replace 'N' with it.
--Refer enhancement bug 3502592
exclude_flag
FROM ahl_rt_oper_materials
WHERE rt_oper_material_id = c_rt_oper_material_id;
SELECT 'X'
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_inventory_item_id
AND organization_id = c_organization_id;
SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT object_id
FROM csi_ii_relationships
WHERE object_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE))
START WITH subject_id = c_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY subject_id = PRIOR object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT serial_number,
lot_number,
inventory_item_id,
last_vld_organization_id,
inv_master_organization_id
FROM csi_item_instances
WHERE instance_id = c_instance_id;
l_route_mtl_req_tbl.DELETE(i);
'The disposition record is deleted and i='||i||' because the exclude_flag=Y');
l_route_mtl_req_tbl.DELETE(i);
l_route_mtl_req_tbl.DELETE(i);
SELECT ahl_prd_dispositions_b_s.NEXTVAL
INTO l_disposition_id
FROM dual;
AHL_PRD_DISPOSITIONS_PKG.INSERT_ROW(
X_ROWID => l_dummy_rowid,
X_DISPOSITION_ID => l_disposition_id,
X_OBJECT_VERSION_NUMBER => 1.0,
X_WORKORDER_ID => p_workorder_id,
X_PART_CHANGE_ID => NULL,
X_PATH_POSITION_ID => l_route_mtl_req_tbl(i).position_path_id,
X_INVENTORY_ITEM_ID => l_inv_item_id,
X_ORGANIZATION_ID => l_disp_org_id,
X_ITEM_GROUP_ID => l_route_mtl_req_tbl(i).item_group_id,
X_CONDITION_ID => NULL,
X_INSTANCE_ID => l_installed_inst_id,
X_SERIAL_NUMBER => l_serial_number,
X_LOT_NUMBER => l_lot_number,
X_IMMEDIATE_DISPOSITION_CODE => NULL,
X_SECONDARY_DISPOSITION_CODE => NULL,
X_STATUS_CODE => NULL,
X_QUANTITY => l_route_mtl_req_tbl(i).quantity,
X_UOM => l_route_mtl_req_tbl(i).uom_code,
X_COLLECTION_ID => NULL,
X_PRIMARY_SERVICE_REQUEST_ID => NULL,
X_NON_ROUTINE_WORKORDER_ID => NULL,
X_WO_OPERATION_ID => NULL,
X_ITEM_REVISION => NULL,
--We may need to get the item_revision from ahl_rt_oper_materials later
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_COMMENTS => NULL,
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);
SELECT AHL_PRD_DISPOSITIONS_B_H_S.NEXTVAL
INTO l_disposition_h_id
FROM dual;
AHL_PRD_DISPOSITIONS_B_H_PKG.INSERT_ROW(
X_ROWID => l_dummy_rowid,
X_DISPOSITION_H_ID => l_disposition_h_id,
X_DISPOSITION_ID => l_disposition_id,
X_OBJECT_VERSION_NUMBER => 1.0,
X_WORKORDER_ID => p_workorder_id,
X_PART_CHANGE_ID => NULL,
X_PATH_POSITION_ID => l_route_mtl_req_tbl(i).position_path_id,
X_INVENTORY_ITEM_ID => l_inv_item_id,
X_ORGANIZATION_ID => l_disp_org_id,
X_ITEM_GROUP_ID => l_route_mtl_req_tbl(i).item_group_id,
X_CONDITION_ID => NULL,
X_INSTANCE_ID => l_installed_inst_id,
X_SERIAL_NUMBER => l_serial_number,
X_LOT_NUMBER => l_lot_number,
X_IMMEDIATE_DISPOSITION_CODE => NULL,
X_SECONDARY_DISPOSITION_CODE => NULL,
X_STATUS_CODE => NULL,
X_QUANTITY => l_route_mtl_req_tbl(i).quantity,
X_UOM => l_route_mtl_req_tbl(i).uom_code,
X_COLLECTION_ID => NULL,
X_PRIMARY_SERVICE_REQUEST_ID => NULL,
X_NON_ROUTINE_WORKORDER_ID => NULL,
X_WO_OPERATION_ID => NULL,
X_ITEM_REVISION => NULL,
--We may need to get the item_revision from ahl_rt_oper_materials later
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_COMMENTS => NULL,
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);
WHEN NO_DATA_FOUND THEN --This +100 is raised explicitly in INSERT_ROW
FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INSERT_FAILED' );
ELSIF p_x_disposition_rec.operation_flag = G_OP_UPDATE THEN
update_disposition( p_api_version,
l_init_msg_list,
l_commit,
p_validation_level,
p_module_type ,
p_x_disposition_rec,
p_mr_asso_tbl, -- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
x_return_status ,
x_msg_count,
x_msg_data);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After calling update_dispositon- x_msg_data: ' || x_msg_data
|| ' x_msg_count: ' || x_msg_count);
SELECT 'X'
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_item_org_id
AND serial_number_control_code = 1;
SELECT removed_instance_id, part_change_type
FROM ahl_part_changes
WHERE part_change_id = p_part_change_id;
SELECT 'Y'
FROM ahl_workorders awo, csi_item_instances csi
WHERE awo.wip_entity_id = csi.wip_job_id
AND awo.workorder_id = p_workorder_id
AND csi.instance_id = p_instance_id;
SELECT 'Y'
FROM ahl_workorders awo, csi_item_instances csi, ahl_prd_dispositions_b disp
WHERE awo.workorder_id = p_workorder_id
AND csi.wip_job_id = awo.wip_entity_id
AND csi.INVENTORY_ITEM_ID = disp.INVENTORY_ITEM_ID
AND csi.QUANTITY = disp.QUANTITY;
SELECT workorder_id
FROM ahl_workorders
WHERE master_workorder_flag = 'N'
AND wip_entity_id IN
(SELECT child_object_id
FROM wip_sched_relationships
START WITH parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = c_nr_wo_id)
CONNECT BY parent_object_id = PRIOR child_object_id
AND parent_object_type_id = PRIOR child_object_type_id
AND relationship_type = 1
)
AND status_code = G_WO_RELEASED_STATUS
ORDER BY workorder_id;
SELECT 'X'
FROM csi_item_instances
WHERE instance_id = c_instance_id
AND serial_number IS NOT NULL
FOR UPDATE OF instance_id;
Select AHL_PRD_DISPOSITIONS_B_S.NEXTVAL into p_x_disposition_rec.disposition_id from dual;
p_x_disposition_rec.last_updated_by := fnd_global.user_id;
p_x_disposition_rec.last_update_date := SYSDATE;
p_x_disposition_rec.last_update_login := fnd_global.login_id ;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before Insert_Row');
AHL_PRD_DISPOSITIONS_PKG.INSERT_ROW(
x_rowid => l_dummy_char,
x_disposition_id => p_x_disposition_rec.disposition_id,
x_object_version_number => p_x_disposition_rec.object_version_number,
x_workorder_id => p_x_disposition_rec.workorder_id ,
x_part_change_id => p_x_disposition_rec.part_change_id,
x_path_position_id => p_x_disposition_rec.path_position_id,
x_inventory_item_id => p_x_disposition_rec.inventory_item_id,
x_organization_id => p_x_disposition_rec.item_org_id,
x_item_group_id => p_x_disposition_rec.item_group_id,
x_condition_id => p_x_disposition_rec.condition_id,
x_instance_id => p_x_disposition_rec.instance_id,
x_serial_number => p_x_disposition_rec.serial_number,
x_lot_number => p_x_disposition_rec.lot_number,
x_immediate_disposition_code => p_x_disposition_rec.immediate_disposition_code ,
x_secondary_disposition_code => p_x_disposition_rec.secondary_disposition_code,
x_status_code => p_x_disposition_rec.status_code,
x_quantity => p_x_disposition_rec.quantity,
x_uom => p_x_disposition_rec.uom,
x_collection_id => p_x_disposition_rec.collection_id,
x_primary_service_request_id => p_x_disposition_rec.primary_service_request_id ,
x_non_routine_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
x_wo_operation_id => p_x_disposition_rec.wo_operation_id,
x_item_revision => p_x_disposition_rec.item_revision,
x_attribute_category => p_x_disposition_rec.attribute_category,
x_attribute1 => p_x_disposition_rec.attribute1,
x_attribute2 => p_x_disposition_rec.attribute2,
x_attribute3 => p_x_disposition_rec.attribute3,
x_attribute4 => p_x_disposition_rec.attribute4,
x_attribute5 => p_x_disposition_rec.attribute5,
x_attribute6 => p_x_disposition_rec.attribute6,
x_attribute7 => p_x_disposition_rec.attribute7,
x_attribute8 => p_x_disposition_rec.attribute8,
x_attribute9 => p_x_disposition_rec.attribute9,
x_attribute10 => p_x_disposition_rec.attribute10,
x_attribute11 => p_x_disposition_rec.attribute11,
x_attribute12 => p_x_disposition_rec.attribute12,
x_attribute13 => p_x_disposition_rec.attribute13,
x_attribute14 => p_x_disposition_rec.attribute14,
x_attribute15 => p_x_disposition_rec.attribute15,
x_comments => p_x_disposition_rec.comments,
x_creation_date => p_x_disposition_rec.creation_date ,
x_created_by => p_x_disposition_rec.created_by,
x_last_update_date => p_x_disposition_rec.last_update_date,
x_last_updated_by => p_x_disposition_rec.last_updated_by,
x_last_update_login => p_x_disposition_rec.last_update_login
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'AfterInsert_Row');
UPDATE AHL_PRD_DISPOSITIONS_B SET primary_service_request_id = p_x_disposition_rec.primary_service_request_id,
non_routine_workorder_id = p_x_disposition_rec.non_routine_workorder_id,
status_code = p_x_disposition_rec.status_code
WHERE disposition_id = p_x_disposition_rec.disposition_id;
Select AHL_PRD_DISPOSITIONS_B_H_S.NEXTVAL into l_disposition_h_id from dual;
AHL_PRD_DISPOSITIONS_B_H_PKG.INSERT_ROW(
x_rowid => l_dummy_char,
x_disposition_h_id => l_disposition_h_id,
x_disposition_id => p_x_disposition_rec.disposition_id,
x_object_version_number => p_x_disposition_rec.object_version_number,
x_workorder_id => p_x_disposition_rec.workorder_id ,
x_part_change_id => p_x_disposition_rec.part_change_id,
x_path_position_id => p_x_disposition_rec.path_position_id,
x_inventory_item_id => p_x_disposition_rec.inventory_item_id,
x_organization_id => p_x_disposition_rec.item_org_id,
x_item_group_id => p_x_disposition_rec.item_group_id,
x_condition_id => p_x_disposition_rec.condition_id,
x_instance_id => p_x_disposition_rec.instance_id,
x_serial_number => p_x_disposition_rec.serial_number,
x_lot_number => p_x_disposition_rec.lot_number,
x_immediate_disposition_code => p_x_disposition_rec.immediate_disposition_code ,
x_secondary_disposition_code => p_x_disposition_rec.secondary_disposition_code,
x_status_code => p_x_disposition_rec.status_code,
x_quantity => p_x_disposition_rec.quantity,
x_uom => p_x_disposition_rec.uom,
x_collection_id => p_x_disposition_rec.collection_id,
x_primary_service_request_id => p_x_disposition_rec.primary_service_request_id ,
x_non_routine_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
x_wo_operation_id => p_x_disposition_rec.wo_operation_id,
x_item_revision => p_x_disposition_rec.item_revision,
x_attribute_category => p_x_disposition_rec.attribute_category,
x_attribute1 => p_x_disposition_rec.attribute1,
x_attribute2 => p_x_disposition_rec.attribute2,
x_attribute3 => p_x_disposition_rec.attribute3,
x_attribute4 => p_x_disposition_rec.attribute4,
x_attribute5 => p_x_disposition_rec.attribute5,
x_attribute6 => p_x_disposition_rec.attribute6,
x_attribute7 => p_x_disposition_rec.attribute7,
x_attribute8 => p_x_disposition_rec.attribute8,
x_attribute9 => p_x_disposition_rec.attribute9,
x_attribute10 => p_x_disposition_rec.attribute10,
x_attribute11 => p_x_disposition_rec.attribute11,
x_attribute12 => p_x_disposition_rec.attribute12,
x_attribute13 => p_x_disposition_rec.attribute13,
x_attribute14 => p_x_disposition_rec.attribute14,
x_attribute15 => p_x_disposition_rec.attribute15,
x_comments => p_x_disposition_rec.comments,
x_creation_date => p_x_disposition_rec.creation_date ,
x_created_by => p_x_disposition_rec.created_by,
x_last_update_date => p_x_disposition_rec.last_update_date,
x_last_updated_by => p_x_disposition_rec.last_updated_by,
x_last_update_login => p_x_disposition_rec.last_update_login
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call AHL_PRD_PARTS_CHANGE_PVT.Update_Part_Condition with ' ||
' p_instance_id = ' || l_removed_instance_id ||
', and p_instance_condition_id = ' || p_x_disposition_rec.condition_id);
AHL_PRD_PARTS_CHANGE_PVT.Update_Part_Condition(p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_instance_id => l_removed_instance_id,
p_instance_condition_id => p_x_disposition_rec.condition_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from AHL_PRD_PARTS_CHANGE_PVT.Update_Part_Condition: ' ||
' x_return_status = ' || x_return_status);
UPDATE AHL_PRD_DISPOSITIONS_B
SET status_code = 'COMPLETE'
WHERE disposition_id = p_x_disposition_rec.disposition_id;
UPDATE AHL_PRD_DISPOSITIONS_B
SET status_code = 'COMPLETE'
WHERE disposition_id = p_x_disposition_rec.disposition_id;
PROCEDURE update_disposition(
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_module_type IN VARCHAR2 := NULL,
p_x_disposition_rec IN OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
-- Parameter added by jaramana on Oct 9, 2007 for ER 5883257
p_mr_asso_tbl IN AHL_PRD_NONROUTINE_PVT.MR_Association_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR disposition_csr(p_disposition_id IN NUMBER) IS
SELECT *
-- AnRaj: Changed query, Perf Bug#4908609,Issue#4
-- FROM ahl_prd_dispositions_v
FROM ahl_prd_dispositions_vl
WHERE disposition_id = p_disposition_id ;
SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
WHERE wo.workorder_id = p_workorder_id
AND wo.visit_id = vi.visit_id;
SELECT 'x' FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
UNION
SELECT 'x' FROM csi_item_instances csi
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id;
SELECT 'x' FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id
UNION
SELECT 'x' FROM csi_item_instances csi
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id;
SELECT 'x' FROM mtl_item_revisions
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND revision = p_revision;
SELECT 'Y'
FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK, AHL_UNIT_EFFECTIVITIES_B UE
WHERE WO.workorder_id = NVL(p_nr_workorder_id, -1)
AND TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND UE.UNIT_EFFECTIVITY_ID = TSK.UNIT_EFFECTIVITY_ID
AND UE.STATUS_CODE IS NULL;
SELECT 'X'
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_item_org_id
AND serial_number_control_code = 1;
SELECT removed_instance_id, part_change_type
FROM ahl_part_changes
WHERE part_change_id = p_part_change_id;
SELECT workorder_id
FROM ahl_workorders
WHERE master_workorder_flag = 'N'
AND wip_entity_id IN
(SELECT child_object_id
FROM wip_sched_relationships
START WITH parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = c_nr_wo_id)
CONNECT BY parent_object_id = PRIOR child_object_id
AND parent_object_type_id = PRIOR child_object_type_id
AND relationship_type = 1
)
AND status_code = G_WO_RELEASED_STATUS
ORDER BY workorder_id;
SELECT removed_instance_id
FROM ahl_part_changes
WHERE part_change_id = c_part_change_id;
SELECT 'Y'
FROM ahl_workorders awo, csi_item_instances csi
WHERE awo.wip_entity_id = csi.wip_job_id
AND awo.workorder_id = p_workorder_id
AND csi.instance_id = p_instance_id;
SELECT 'Y'
FROM ahl_workorders awo, csi_item_instances csi, ahl_prd_dispositions_b disp
WHERE awo.workorder_id = p_workorder_id
AND csi.wip_job_id = awo.wip_entity_id
AND csi.INVENTORY_ITEM_ID = disp.INVENTORY_ITEM_ID
AND csi.QUANTITY = disp.QUANTITY;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || 'update_disposition';
select 'x' from qa_plan_transactions_v
where mandatory_collection_flag = 1 and enabled_flag = 1
and plan_id = p_plan_id;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY || '', 'update Disposition_id' || p_x_disposition_rec.disposition_id );
FND_MESSAGE.Set_Name('AHL', 'AHL_PRD_DIS_UPDATE_TERMINATE'); --Cannot update a terminated disposition.
SELECT 'X' INTO l_dummy_char
FROM csi_item_instances
WHERE instance_id = p_x_disposition_rec.instance_id
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_WO_NOT_EDITABLE'); --Cannot Update Disposition Because Workorder is not editable.
--dbms_output.put_line(SubStr('Update_disp:allow change item', 1, 255));
p_x_disposition_rec.last_updated_by := fnd_global.user_id;
p_x_disposition_rec.last_update_date := SYSDATE;
p_x_disposition_rec.last_update_login := fnd_global.login_id;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before update_row');
AHL_PRD_DISPOSITIONS_PKG.UPDATE_ROW(
x_disposition_id => p_x_disposition_rec.disposition_id,
x_object_version_number => p_x_disposition_rec.object_version_number,
x_workorder_id => p_x_disposition_rec.workorder_id ,
x_part_change_id => p_x_disposition_rec.part_change_id,
x_path_position_id => p_x_disposition_rec.path_position_id,
x_inventory_item_id => p_x_disposition_rec.inventory_item_id,
x_organization_id => p_x_disposition_rec.item_org_id,
x_item_group_id => p_x_disposition_rec.item_group_id,
x_condition_id => p_x_disposition_rec.condition_id,
x_instance_id => p_x_disposition_rec.instance_id,
x_serial_number => p_x_disposition_rec.serial_number,
x_lot_number => p_x_disposition_rec.lot_number,
x_immediate_disposition_code => p_x_disposition_rec.immediate_disposition_code ,
x_secondary_disposition_code => p_x_disposition_rec.secondary_disposition_code,
x_status_code => p_x_disposition_rec.status_code,
x_quantity => p_x_disposition_rec.quantity,
x_uom => p_x_disposition_rec.uom,
x_collection_id => p_x_disposition_rec.collection_id,
x_primary_service_request_id => p_x_disposition_rec.primary_service_request_id ,
x_non_routine_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
x_wo_operation_id => p_x_disposition_rec.wo_operation_id,
x_item_revision => p_x_disposition_rec.item_revision,
x_attribute_category => p_x_disposition_rec.attribute_category,
x_attribute1 => p_x_disposition_rec.attribute1,
x_attribute2 => p_x_disposition_rec.attribute2,
x_attribute3 => p_x_disposition_rec.attribute3,
x_attribute4 => p_x_disposition_rec.attribute4,
x_attribute5 => p_x_disposition_rec.attribute5,
x_attribute6 => p_x_disposition_rec.attribute6,
x_attribute7 => p_x_disposition_rec.attribute7,
x_attribute8 => p_x_disposition_rec.attribute8,
x_attribute9 => p_x_disposition_rec.attribute9,
x_attribute10 => p_x_disposition_rec.attribute10,
x_attribute11 => p_x_disposition_rec.attribute11,
x_attribute12 => p_x_disposition_rec.attribute12,
x_attribute13 => p_x_disposition_rec.attribute13,
x_attribute14 => p_x_disposition_rec.attribute14,
x_attribute15 => p_x_disposition_rec.attribute15,
x_comments => p_x_disposition_rec.comments,
x_last_update_date => p_x_disposition_rec.last_update_date,
x_last_updated_by => p_x_disposition_rec.last_updated_by,
x_last_update_login => p_x_disposition_rec.last_update_login
);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'After update_row');
Select AHL_PRD_DISPOSITIONS_B_H_S.NEXTVAL into l_disposition_h_id from dual;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Before insert into history table');
AHL_PRD_DISPOSITIONS_B_H_PKG.INSERT_ROW(
x_rowid => l_dummy_char,
x_disposition_h_id => l_disposition_h_id,
x_disposition_id => p_x_disposition_rec.disposition_id,
x_object_version_number => p_x_disposition_rec.object_version_number,
x_workorder_id => p_x_disposition_rec.workorder_id ,
x_part_change_id => p_x_disposition_rec.part_change_id,
x_path_position_id => p_x_disposition_rec.path_position_id,
x_inventory_item_id => p_x_disposition_rec.inventory_item_id,
x_organization_id => p_x_disposition_rec.item_org_id,
x_item_group_id => p_x_disposition_rec.item_group_id,
x_condition_id => p_x_disposition_rec.condition_id,
x_instance_id => p_x_disposition_rec.instance_id,
x_serial_number => p_x_disposition_rec.serial_number,
x_lot_number => p_x_disposition_rec.lot_number,
x_immediate_disposition_code => p_x_disposition_rec.immediate_disposition_code ,
x_secondary_disposition_code => p_x_disposition_rec.secondary_disposition_code,
x_status_code => p_x_disposition_rec.status_code,
x_quantity => p_x_disposition_rec.quantity,
x_uom => p_x_disposition_rec.uom,
x_collection_id => p_x_disposition_rec.collection_id,
x_primary_service_request_id => p_x_disposition_rec.primary_service_request_id ,
x_non_routine_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
x_wo_operation_id => p_x_disposition_rec.wo_operation_id,
x_item_revision => p_x_disposition_rec.item_revision,
x_attribute_category => p_x_disposition_rec.attribute_category,
x_attribute1 => p_x_disposition_rec.attribute1,
x_attribute2 => p_x_disposition_rec.attribute2,
x_attribute3 => p_x_disposition_rec.attribute3,
x_attribute4 => p_x_disposition_rec.attribute4,
x_attribute5 => p_x_disposition_rec.attribute5,
x_attribute6 => p_x_disposition_rec.attribute6,
x_attribute7 => p_x_disposition_rec.attribute7,
x_attribute8 => p_x_disposition_rec.attribute8,
x_attribute9 => p_x_disposition_rec.attribute9,
x_attribute10 => p_x_disposition_rec.attribute10,
x_attribute11 => p_x_disposition_rec.attribute11,
x_attribute12 => p_x_disposition_rec.attribute12,
x_attribute13 => p_x_disposition_rec.attribute13,
x_attribute14 => p_x_disposition_rec.attribute14,
x_attribute15 => p_x_disposition_rec.attribute15,
x_comments => p_x_disposition_rec.comments,
x_creation_date => l_disposition_rec.creation_date ,
x_created_by => l_disposition_rec.created_by,
x_last_update_date => p_x_disposition_rec.last_update_date,
x_last_updated_by => p_x_disposition_rec.last_updated_by,
x_last_update_login => p_x_disposition_rec.last_update_login
);
update_item_location(p_workorder_id => p_x_disposition_rec.non_routine_workorder_id,
p_instance_id => p_x_disposition_rec.instance_id,
x_return_status => x_return_status);
UPDATE AHL_PRD_DISPOSITIONS_B
SET status_code = 'COMPLETE'
WHERE disposition_id = p_x_disposition_rec.disposition_id;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call AHL_PRD_PARTS_CHANGE_PVT.Update_Part_Condition with ' ||
' p_instance_id = ' || l_removed_instance_id ||
', and p_instance_condition_id = ' || p_x_disposition_rec.condition_id);
AHL_PRD_PARTS_CHANGE_PVT.Update_Part_Condition(p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_instance_id => l_removed_instance_id,
p_instance_condition_id => p_x_disposition_rec.condition_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'Returned from AHL_PRD_PARTS_CHANGE_PVT.Update_Part_Condition: ' ||
' x_return_status = ' || x_return_status);
END UPDATE_DISPOSITION;
SELECT removed_instance_id
FROM ahl_part_changes
WHERE part_change_id = p_part_change_id;
SELECT 'Y'
FROM ahl_workorders awo, csi_item_instances csi
WHERE awo.wip_entity_id = csi.wip_job_id
AND awo.workorder_id = p_workorder_id
AND csi.instance_id = p_instance_id;
SELECT workorder_id
FROM ahl_workorders
WHERE master_workorder_flag = 'N'
AND wip_entity_id IN
(SELECT child_object_id
FROM wip_sched_relationships
START WITH parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = c_nr_wo_id)
CONNECT BY parent_object_id = PRIOR child_object_id
AND parent_object_type_id = PRIOR child_object_type_id
AND relationship_type = 1
)
AND status_code = G_WO_RELEASED_STATUS
ORDER BY workorder_id;
SELECT scheduled_start_date
FROM ahl_workorders_v
WHERE workorder_id = p_workorder_id;
SELECT visit_id INTO l_visit_id FROM AHL_WORKORDERS WHERE workorder_id = p_disposition_rec.workorder_id;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, L_DEBUG_KEY, 'About to call update_item_location.');
update_item_location(p_workorder_id => l_sr_task_tbl(0).Nonroutine_wo_id,
p_instance_id => p_disposition_rec.instance_id,
x_return_status => x_return_status);
SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
WHERE wo.workorder_id = p_workorder_id
AND wo.visit_id = vi.visit_id;
SELECT instance_id FROM CSI_ITEM_INSTANCES
WHERE inventory_item_id = p_item_id AND serial_number = p_serial_num;
SELECT instance_id FROM CSI_ITEM_INSTANCES
WHERE inventory_item_id = p_item_id AND lot_number = p_lot_num;
SELECT inventory_item_id,
quantity,
unit_of_measure,
last_vld_organization_id,
inv_master_organization_id,
serial_number,
lot_number,
inventory_revision
from csi_item_instances
WHERE instance_id = p_instance_id;
END IF; -- END: Updated by rbhavsar Bug 6411059
SELECT 'X' FROM ahl_mc_path_positions pos
WHERE pos.path_position_id = p_path_position_id
AND EXISTS
(SELECT pos2.path_pos_common_id FROM ahl_prd_dispositions_b dis, ahl_mc_path_positions pos2
WHERE dis.path_position_id IS NOT NULL
AND dis.path_position_id = pos2.path_position_id
AND dis.workorder_id = p_workorder_id
AND nvl(dis.status_code, 'dummy') NOT IN ('COMPLETE', 'TERMINATED')
AND pos.path_pos_common_id = pos2.path_pos_common_id);
SELECT nvl(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)
FROM ahl_workorders wo, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS
WHERE workorder_id = p_workorder_id
and WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
AND VST.VISIT_ID=VTS.VISIT_ID;
SELECT 'x' FROM ahl_item_associations_b
WHERE inventory_item_id = p_Inventory_item_id
AND item_group_id = p_item_group_id;
SELECT 'x' FROM ahl_item_groups_b
WHERE item_group_id = p_item_group_id
AND status_code = 'COMPLETE' AND type_code = 'NON-TRACKED';
SELECT 'x' FROM csi_item_instances csi
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id;
SELECT 'x' FROM csi_item_instances csi
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id;
SELECT 'x' FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id
UNION
SELECT 'x' FROM csi_item_instances csi
WHERE lot_number = p_lot_number
AND inventory_item_id = p_inventory_item_id;
SELECT 'x' FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id
UNION
SELECT 'x' FROM csi_item_instances csi
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id;
SELECT quantity from csi_item_instances WHERE instance_id = p_instance_id;
SELECT unit_of_measure from csi_item_instances WHERE instance_id = p_instance_id;
SELECT 'X' FROM ahl_item_class_uom_v
WHERE uom_code = p_uom_code AND inventory_item_id = p_inventory_item_id;
SELECT 'X'
from MTL_UNITS_OF_MEASURE_TL
where uom_class = (
select distinct uom.uom_class
from MTL_UNITS_OF_MEASURE_TL uom
where uom.uom_code = ( select distinct primary_uom_code
from mtl_system_items
where inventory_item_id = p_inventory_item_id
)
)
and uom_code = p_uom_code;
SELECT 'x' FROM qa_results WHERE collection_id = p_collection_id;
SELECT inventory_item_id from csi_item_instances WHERE instance_id = p_instance_id;
SELECT 'x' FROM mtl_units_of_measure_vl
WHERE uom_code = p_uom;
SELECT 'x' FROM mtl_item_revisions
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND revision = p_revision;
SELECT REMOVED_INSTANCE_ID, INSTALLED_INSTANCE_ID, PART_CHANGE_TYPE
FROM AHL_PART_CHANGES
WHERE PART_CHANGE_ID = c_part_change_id;
SELECT NVL(MTL.comms_nl_trackable_flag, 'N') trackable_flag
FROM MTL_SYSTEM_ITEMS_KFV MTL
WHERE MTL.INVENTORY_ITEM_ID= p_inventory_item_id;
SELECT 'X'
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_item_org_id
AND serial_number_control_code = 1;
select 'x' from qa_plan_transactions_v
where mandatory_collection_flag = 1 and enabled_flag = 1
and plan_id = p_plan_id;
FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_PRD_DIS_PARENT_EMPTY'); --Cannot select a position whose parent is empty
SELECT instance_id from csi_item_instances
WHERE instance_number = p_instance_number;
SELECT item_group_id from ahl_item_groups_b
WHERE name = p_item_group_name AND status_code = 'COMPLETE';
SELECT inventory_item_id FROM MTL_SYSTEM_ITEMS_KFV
WHERE concatenated_segments = p_item_number;
SELECT status_id FROM mtl_material_statuses_vl
WHERE status_code = p_condition_meaning;
SELECT workorder_operation_id from ahl_workorder_operations
WHERE workorder_id = p_workorder_id AND operation_sequence_num = p_operation_seq;
SELECT incident_severity_id from cs_incident_severities_vl
WHERE name = p_severity_name
AND incident_subtype = 'INC'
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate)) and trunc(nvl(end_date_active,sysdate));
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = 'SERVICE TYPE'
AND enabled_flag = 'Y'
AND meaning = c_service_type
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE-1))
AND TRUNC(NVL(end_date_active,SYSDATE));
SELECT fl.lookup_code FROM fnd_lookup_values_vl FL
WHERE fl.meaning = p_problem_meaning
AND lookup_type = 'REQUEST_PROBLEM_CODE'
AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active,sysdate)) AND trunc(nvl(end_date_active,sysdate))
AND (( NOT EXISTS (SELECT 1 FROM CS_SR_PROB_CODE_MAPPING_V WHERE INCIDENT_TYPE_ID = FND_PROFILE.Value('AHL_PRD_SR_TYPE')
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))) )
OR ( EXISTS (SELECT 1 FROM CS_SR_PROB_CODE_MAPPING_V MAP WHERE MAP.INCIDENT_TYPE_ID = FND_PROFILE.Value('AHL_PRD_SR_TYPE')
AND MAP.INVENTORY_ITEM_ID IS NULL AND MAP.PROBLEM_CODE = FL.LOOKUP_CODE
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(MAP.START_DATE_ACTIVE,SYSDATE)) AND TRUNC(NVL(MAP.END_DATE_ACTIVE,SYSDATE)))));
SELECT fl.lookup_code
FROM fnd_lookup_values_vl FL
WHERE fl.meaning = p_problem_meaning
AND lookup_type = 'REQUEST_PROBLEM_CODE'
AND trunc(sysdate) BETWEEN trunc(nvl(start_date_active,sysdate))
AND trunc(nvl(end_date_active,sysdate)) ;
SELECT 'x' FROM qa_results WHERE collection_id = p_collection_id;
SELECT 'x' FROM AHL_WORKORDERS
WHERE workorder_id = p_workorder_id;
SELECT 'x' FROM ahl_mc_path_positions
WHERE path_position_id = p_path_position_id;
SELECT vi.organization_id from ahl_workorders wo, ahl_visits_b vi
WHERE wo.workorder_id = p_workorder_id
AND wo.visit_id = vi.visit_id;
SELECT 'x' FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT serial_number_control_code, revision_qty_control_code, lot_control_code
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT 'x' from csi_item_instances
where instance_id = p_instance_id;
SELECT 'x' from csi_item_instances csi, ahl_workorders wo
WHERE instance_id = p_instance_id
and wo.wip_entity_id = csi.wip_job_id
and csi.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
and trunc(sysdate) between trunc(nvl(csi.active_start_date, sysdate)) and trunc(nvl(csi.active_end_date, sysdate));
SELECT 'x' from ahl_prd_dispositions_b WHERE
workorder_id = p_workorder_id
and instance_id = p_instance_id
and nvl(status_code, ' ') NOT IN ('COMPLETE', 'TERMINATED');
SELECT nvl(VTS.INSTANCE_ID,VST.ITEM_INSTANCE_ID)
FROM ahl_workorders wo, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS
WHERE workorder_id = p_workorder_id
and WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
AND VST.VISIT_ID=VTS.VISIT_ID;
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_workorder_operations
WHERE workorder_operation_id = p_wo_operation_id
AND workorder_id = p_workorder_id;
SELECT removed_instance_id, installed_instance_id, NVL(part_change_type, 'X'), quantity
FROM ahl_part_changes
WHERE part_change_id = p_part_change_id;
SELECT SERIAL_NUMBER, INVENTORY_ITEM_ID, QUANTITY
FROM CSI_ITEM_INSTANCES
WHERE INSTANCE_ID = c_instance_id;
SELECT disp.status_code, disp.trackable_flag,
pc.return_mtl_txn_id mtl_txn_id,
pc.installed_part_change_id install_pc_id,
AHL_PRD_DISP_MTL_TXN_PVT.calculate_txned_qty(disp.disposition_id) mtl_txn_qty
FROM AHL_PRD_DISPOSITIONS_V disp, AHL_PART_CHANGES_V pc
WHERE disp.disposition_id = p_disposition_id
AND disp.part_change_id = pc.part_change_id (+);
SELECT disp.status_code,
decode(disp.instance_id, null, decode(disp.path_position_id, null, 'N', 'Y'), 'Y') trackable_flag ,
pc.return_mtl_txn_id mtl_txn_id,
pc.installed_part_change_id install_pc_id,
pc.part_change_type,
AHL_PRD_DISP_MTL_TXN_PVT.calculate_txned_qty(disp.disposition_id) mtl_txn_qty
FROM AHL_PRD_DISPOSITIONS_B disp,
AHL_PART_CHANGES_V pc
WHERE disp.disposition_id = p_disposition_id
AND disp.part_change_id = pc.part_change_id (+);
SELECT NVL(MTL.comms_nl_trackable_flag, 'N') trackable_flag
FROM MTL_SYSTEM_ITEMS_KFV MTL
WHERE MTL.INVENTORY_ITEM_ID= p_inv_item_id
AND MTL.organization_id =p_inv_org_id;
SELECT part_change_type
FROM AHL_PART_CHANGES
WHERE part_change_id = p_part_change_id;
SELECT rel.position_necessity_code
FROM AHL_MC_RELATIONSHIPS rel, CSI_II_RELATIONSHIPS CSI
WHERE csi.subject_id = p_instance_id
AND rel.relationship_id = TO_NUMBER(CSI.position_reference)
AND CSI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(CSI.ACTIVE_START_DATE, sysdate-1)) < TRUNC(sysdate)
AND TRUNC(nvl(CSI.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate);
SELECT B.position_necessity_code
FROM ahl_mc_path_position_nodes A,
ahl_mc_relationships B,
ahl_mc_headers_b C
WHERE A.path_position_id = c_path_position_id
and A.sequence = (select max(D.sequence)
from ahl_mc_path_position_nodes D
group by D.path_position_id
having D.path_position_id = c_path_position_id)
and A.mc_id = C.mc_id
and A.version_number = C.version_number
and C.mc_header_id = B.mc_header_id
and A.position_key = B.position_key;
SELECT 'X'
FROM fnd_lookups
WHERE lookup_type = 'AHL_IMMED_DISP_TYPE'
AND lookup_code = p_disp_code
AND lookup_code <> 'NULL';
SELECT 'X'
FROM fnd_lookups
WHERE lookup_type = 'AHL_SECND_DISP_TYPE'
AND lookup_code = p_disp_code
AND lookup_code <> 'NULL';
SELECT 'X'
FROM mtl_material_statuses
WHERE status_id = p_condition_id
AND enabled_flag = 1;
SELECT *
FROM AHL_PRD_DISPOSITIONS_V
WHERE DISPOSITION_ID = p_disp_id;
select B.condition_id,
COND.STATUS_CODE CONDITION_CODE,
B.immediate_disposition_code,
FND1.MEANING IMMEDIATE_TYPE,
B.secondary_disposition_code,
FND2.MEANING SECONDARY_TYPE,
B.part_change_id,
decode(B.instance_id, null, decode(B.path_position_id, null, 'N', 'Y'), 'Y') TRACKABLE_FLAG
from AHL_PRD_DISPOSITIONS_B B,
FND_LOOKUPS FND1,
FND_LOOKUPS FND2,
MTL_MATERIAL_STATUSES_VL COND
where FND1.LOOKUP_TYPE (+) = 'AHL_IMMED_DISP_TYPE'
AND B.immediate_disposition_code = FND1.LOOKUP_CODE (+)
AND FND2.LOOKUP_TYPE (+) = 'AHL_SECND_DISP_TYPE'
AND B.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
AND B.condition_id = COND.status_id (+)
AND B.disposition_id = p_disp_id;
SELECT nvl(comms_nl_trackable_flag, 'N') INTO l_trackable_flag
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_disposition_rec.inventory_item_id
AND organization_id = p_disposition_rec.item_org_id;
SELECT decode(type_code, 'TRACKED', 'Y', 'N') INTO l_trackable_flag
FROM ahl_item_groups_b
WHERE item_group_id = p_disposition_rec.item_group_id;
IF (p_disposition_rec.operation_flag = G_OP_UPDATE AND
p_disposition_rec.disposition_id IS NOT NULL) THEN
--2a) Fetch the existing state.
OPEN get_disp_rec_csr (p_disposition_rec.disposition_id);
END IF; --G_UPDATE
SELECT status_code from ahl_workorders where workorder_id = p_workorder_id;
SELECT VTS.INSTANCE_ID, VTS.VISIT_ID
FROM AHL_VISIT_TASKS_B VTS, AHL_WORKORDERS WO
WHERE WO.VISIT_TASK_ID = VTS.VISIT_TASK_ID AND
WO.WORKORDER_ID = p_workorder_id;
SELECT VST.ITEM_INSTANCE_ID
FROM AHL_VISITS_B VST
WHERE VST.VISIT_ID = c_visit_id;
SELECT csi_item_instance_id FROM ahl_unit_config_headers
WHERE unit_config_header_id = p_uc_header_id;
SELECT object_id
FROM csi_ii_relationships
START WITH subject_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY subject_id = PRIOR object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT 'x' FROM csi_ii_relationships
WHERE subject_id = p_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT sum (assoc.quantity)
FROM AHL_PRD_DISP_MTL_TXNS assoc,
AHL_WORKORDER_MTL_TXNS mtxn
WHERE assoc.disposition_id = p_disp_id
AND assoc.workorder_mtl_txn_id = mtxn.workorder_mtl_txn_id
AND mtxn.transaction_type_id = WIP_CONSTANTS.ISSCOMP_TYPE
GROUP BY assoc.disposition_id;
SELECT object_id
FROM csi_ii_relationships
START WITH subject_id = c_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
CONNECT BY subject_id = prior object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT CHILD_OBJECT_ID
FROM EAM_WO_RELATIONSHIPS
WHERE PARENT_OBJECT_ID = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = p_workorder_id)
AND PARENT_RELATIONSHIP_TYPE = 1
ORDER BY CHILD_OBJECT_ID;
SELECT WO.WORKORDER_ID, WO.WIP_ENTITY_ID, WO.VISIT_TASK_ID
FROM AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK
WHERE WIP_ENTITY_ID = c_wip_entity_id AND
WO.VISIT_TASK_ID = TSK.VISIT_TASK_ID AND
TSK.TASK_TYPE_CODE <> 'SUMMARY';
SELECT EAM.CHILD_OBJECT_ID
FROM EAM_WO_RELATIONSHIPS EAM, AHL_WORKORDERS WO, AHL_VISIT_TASKS_B TSK
WHERE EAM.CHILD_OBJECT_ID = WO.WIP_ENTITY_ID AND
TSK.VISIT_TASK_ID = WO.VISIT_TASK_ID AND
TSK.TASK_TYPE_CODE <> 'SUMMARY'
START WITH EAM.CHILD_OBJECT_ID = c_wip_entity_id
CONNECT BY PRIOR CHILD_OBJECT_ID = PARENT_OBJECT_ID AND
PARENT_RELATIONSHIP_TYPE = 1
ORDER BY LEVEL, EAM.CHILD_OBJECT_ID;
PROCEDURE update_item_location(p_workorder_id IN NUMBER,
p_instance_id IN NUMBER,
x_return_status OUT NOCOPY Varchar2)
IS
l_wip_entity_id NUMBER;
l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||'Update_item_location';
select workorder_id, status_code
FROM ahl_workorders
WHERE wip_entity_id = c_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 ;
AHL_Util_UC_Pkg.GetCSI_Transaction_ID('UC_UPDATE', l_transaction_type_id, l_return_val);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'About to call CSI_ITEM_INSTANCE_PUB.UPDATE_ITEM_INSTANCE');
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 => l_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);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name, 'Returned from CSI_ITEM_INSTANCE_PUB.UPDATE_ITEM_INSTANCE. x_return_status = ' || x_return_status);
END update_item_location;