[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT simulation_plan_id
INTO x_plan_id
FROM AHL_SIMULATION_PLANS_VL
WHERE simulation_plan_id = p_simulation_plan_id;
SELECT simulation_plan_id
INTO x_plan_id
FROM AHL_SIMULATION_PLANS_VL
WHERE SIMULATION_PLAN_NAME = p_plan_name;
SELECT visit_task_number
FROM Ahl_Visit_Tasks_B
WHERE Visit_Id = p_visit_id
and visit_task_number = p_task_number;
SELECT MAX(visit_task_number)
FROM Ahl_Visit_Tasks_B
WHERE Visit_Id = p_visit_id;
SELECT ROW_ID,
SIMULATION_PLAN_ID,
SIMULATION_PLAN_NAME,
PRIMARY_PLAN_FLAG,
DESCRIPTION,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM ahl_simulation_plans_vl
WHERE simulation_plan_id = p_simulation_rec.simulation_plan_id;
SELECT 1 FROM
AHL_SIMULATION_PLANS_VL
WHERE simulation_plan_name = c_plan_name
AND primary_plan_flag = 'N';
SELECT AHL_SIMULATION_PLANS_B_S.NEXTVAL
FROM dual;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM ahl_simulation_plans_b
WHERE simulation_plan_id = x_id);
AHL_SIMULATION_PLANS_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_SIMULATION_PLAN_ID => l_simulation_plan_id,
X_PRIMARY_PLAN_FLAG => 'N',
X_OBJECT_VERSION_NUMBER => 1,
X_ATTRIBUTE_CATEGORY => l_simulation_plan_rec.attribute_category,
X_ATTRIBUTE1 => l_simulation_plan_rec.attribute1,
X_ATTRIBUTE2 => l_simulation_plan_rec.attribute2,
X_ATTRIBUTE3 => l_simulation_plan_rec.attribute3,
X_ATTRIBUTE4 => l_simulation_plan_rec.attribute4,
X_ATTRIBUTE5 => l_simulation_plan_rec.attribute5,
X_ATTRIBUTE6 => l_simulation_plan_rec.attribute6,
X_ATTRIBUTE7 => l_simulation_plan_rec.attribute7,
X_ATTRIBUTE8 => l_simulation_plan_rec.attribute8,
X_ATTRIBUTE9 => l_simulation_plan_rec.attribute9,
X_ATTRIBUTE10 => l_simulation_plan_rec.attribute10,
X_ATTRIBUTE11 => l_simulation_plan_rec.attribute11,
X_ATTRIBUTE12 => l_simulation_plan_rec.attribute12,
X_ATTRIBUTE13 => l_simulation_plan_rec.attribute13,
X_ATTRIBUTE14 => l_simulation_plan_rec.attribute14,
X_ATTRIBUTE15 => l_simulation_plan_rec.attribute15,
X_SIMULATION_PLAN_NAME => l_simulation_plan_rec.simulation_plan_name,
X_DESCRIPTION => l_simulation_plan_rec.description,
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);
PROCEDURE Update_Simulation_plan (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_simulation_plan_rec IN ahl_ltp_simul_plan_pub.Simulation_plan_Rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR primary_plan_cur(c_plan_id IN NUMBER)
IS
SELECT primary_plan_flag
FROM AHL_SIMULATION_PLANS_VL
WHERE simulation_plan_id = c_plan_id;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SIMULATION_PLAN';
SAVEPOINT update_simulation_plan;
AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.Update Simulation plan','+SMPNL+');
AHL_SIMULATION_PLANS_PKG.UPDATE_ROW
(
X_SIMULATION_PLAN_ID => l_Asimulation_plan_rec.simulation_plan_id,
X_PRIMARY_PLAN_FLAG => 'N',
X_SIMULATION_PLAN_NAME => l_Asimulation_plan_rec.simulation_plan_name,
X_DESCRIPTION => l_Asimulation_plan_rec.description,
X_OBJECT_VERSION_NUMBER => l_Asimulation_plan_rec.object_version_number+1,
X_ATTRIBUTE_CATEGORY => l_Asimulation_plan_rec.attribute_category,
X_ATTRIBUTE1 => l_Asimulation_plan_rec.attribute1,
X_ATTRIBUTE2 => l_Asimulation_plan_rec.attribute2,
X_ATTRIBUTE3 => l_Asimulation_plan_rec.attribute3,
X_ATTRIBUTE4 => l_Asimulation_plan_rec.attribute4,
X_ATTRIBUTE5 => l_Asimulation_plan_rec.attribute5,
X_ATTRIBUTE6 => l_Asimulation_plan_rec.attribute6,
X_ATTRIBUTE7 => l_Asimulation_plan_rec.attribute7,
X_ATTRIBUTE8 => l_Asimulation_plan_rec.attribute8,
X_ATTRIBUTE9 => l_Asimulation_plan_rec.attribute9,
X_ATTRIBUTE10 => l_Asimulation_plan_rec.attribute10,
X_ATTRIBUTE11 => l_Asimulation_plan_rec.attribute11,
X_ATTRIBUTE12 => l_Asimulation_plan_rec.attribute12,
X_ATTRIBUTE13 => l_Asimulation_plan_rec.attribute13,
X_ATTRIBUTE14 => l_Asimulation_plan_rec.attribute14,
X_ATTRIBUTE15 => l_Asimulation_plan_rec.attribute15,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
Ahl_Debug_Pub.debug( 'End of private api Update Simulation plan','+SMPLN+');
ROLLBACK TO update_simulation_plan;
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Update Simulation plan','+SMPLN+');
ROLLBACK TO update_simulation_plan;
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Update Simulation plan','+SMPLN+');
ROLLBACK TO update_simulation_plan;
p_procedure_name => 'UPDATE_SIMULATION_PLAN',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Update Simulation plan','+SMPLN+');
END Update_Simulation_plan;
PROCEDURE Delete_Simulation_plan (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
p_simulation_plan_rec IN ahl_ltp_simul_plan_pub.Simulation_plan_Rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR c_simulation_plan_cur
(c_simulation_plan_id IN NUMBER)
IS
SELECT simulation_plan_id,object_version_number,
primary_plan_flag
FROM ahl_simulation_plans_vl
WHERE simulation_plan_id = c_simulation_plan_id
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SELECT visit_id
FROM ahl_visits_b
WHERE simulation_plan_id = c_simulation_plan_id;
SELECT visit_task_id
FROM ahl_visit_tasks_vl
WHERE visit_id = C_VISIT_ID;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SIMULATION_PLAN';
SAVEPOINT delete_simulation_plan;
AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.Delete Simulation plan','+SMPLN+');
/* Added by mpothuku on 12/22/04 to delete the associated simulation visits.
*/
-- Get all the visits associated
OPEN get_simulation_visits_cur(l_simulation_plan_id);
AHL_SIMULATION_PLANS_PKG.DELETE_ROW (
X_SIMULATION_PLAN_ID => l_simulation_plan_id
);
Ahl_Debug_Pub.debug( 'End of private api Delete Simulation plan','+SMPLN+');
ROLLBACK TO delete_simulation_plan;
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Delete Simulation plan','+SMPLN+');
ROLLBACK TO delete_simulation_plan;
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Delete Simulation plan','+SMPLN+');
ROLLBACK TO delete_simulation_plan;
p_procedure_name => 'DELETE_SIMULATION_PLAN',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Delete Simulation plan','+SMPLN+');
END Delete_Simulation_plan;
SELECT visit_id,asso_primary_visit_id
FROM ahl_visits_vl
WHERE visit_number = c_visit_number
AND status_code ='PLANNING'
AND (ORGANIZATION_ID is NULL OR ORGANIZATION_ID IN ( SELECT organization_id
FROM org_organization_definitions
WHERE operating_unit = mo_global.get_current_org_id() ));
SELECT visit_id,asso_primary_visit_id
FROM ahl_visits_vl
WHERE visit_id = c_visit_id;
SELECT VISIT_ID,
VISIT_NAME,
ORGANIZATION_ID,
DEPARTMENT_ID,
STATUS_CODE,
START_DATE_TIME,
VISIT_TYPE_CODE,
SIMULATION_PLAN_ID,
ITEM_INSTANCE_ID,
INVENTORY_ITEM_ID,
ASSO_PRIMARY_VISIT_ID,
SIMULATION_DELETE_FLAG,
TEMPLATE_FLAG,
OUT_OF_SYNC_FLAG,
PROJECT_FLAG,
ITEM_ORGANIZATION_ID,
INV_LOCATOR_ID, --Added by sowsubra
PROJECT_ID,
VISIT_NUMBER,
DESCRIPTION,
SERVICE_REQUEST_ID,
SPACE_CATEGORY_CODE,
SCHEDULE_DESIGNATOR,
CLOSE_DATE_TIME,
PRICE_LIST_ID,
ESTIMATED_PRICE,
ACTUAL_PRICE,
OUTSIDE_PARTY_FLAG,
ANY_TASK_CHG_FLAG,
UNIT_SCHEDULE_ID,
OBJECT_VERSION_NUMBER,
PRIORITY_CODE,
PROJECT_TEMPLATE_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_VISITS_VL
WHERE visit_id = c_visit_id;
SELECT asso_primary_visit_id
FROM AHL_VISITS_VL
WHERE simulation_plan_id = c_plan_id
AND (asso_primary_visit_id = c_visit_id
OR NVL(asso_primary_visit_id, 0) = c_asso_visit_id);
SELECT VISIT_TASK_ID,
VISIT_TASK_NUMBER,
OBJECT_VERSION_NUMBER,
VISIT_ID,
PROJECT_TASK_ID,
COST_PARENT_ID,
MR_ROUTE_ID,
MR_ID,
DURATION,
UNIT_EFFECTIVITY_ID,
VISIT_TASK_NAME,
DESCRIPTION,
START_FROM_HOUR,
INVENTORY_ITEM_ID,
ITEM_ORGANIZATION_ID,
INSTANCE_ID,
PRIMARY_VISIT_TASK_ID,
SUMMARY_TASK_FLAG,
ORIGINATING_TASK_ID,
SERVICE_REQUEST_ID,
TASK_TYPE_CODE,
DEPARTMENT_ID,
PRICE_LIST_ID,
STATUS_CODE,
ACTUAL_COST,
ESTIMATED_PRICE,
ACTUAL_PRICE,
STAGE_ID,
START_DATE_TIME,
END_DATE_TIME,
QUANTITY, -- Added by rnahata for Issue 105
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_VISIT_TASKS_VL
WHERE visit_id = c_visit_id
AND STATUS_CODE <> 'DELETED';
SELECT space_id,space_assignment_id
FROM ahl_space_assignments
WHERE visit_id = c_visit_id;
SELECT b.VISIT_TASK_ID
FROM AHL_VISIT_TASKS_B a, AHL_VISIT_TASKS_B b
WHERE a.visit_task_id = x_visit_task_id
AND a.visit_task_number = b.visit_task_number
AND b.visit_id = x_new_visit_id;
SELECT VISIT_TASK_ID ,
PARENT_TASK_ID,
--SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_TASK_LINKS
WHERE visit_task_id in (SELECT VISIT_TASK_ID
FROM AHL_VISIT_TASKS_B
WHERE visit_id = x_visit_id);
SELECT STAGE_ID,
STAGE_NUM,
VISIT_ID,
DURATION,
OBJECT_VERSION_NUMBER,
STAGE_NAME,
--SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM ahl_vwp_stages_vl s
WHERE visit_id = c_visit_id
ORDER BY stage_num;
SELECT VISIT_NUMBER,ASSO_PRIMARY_VISIT_ID FROM AHL_VISITS_B WHERE
VISIT_ID IN (SELECT DISTINCT VISIT_ID FROM AHL_VISIT_TASKS_B WHERE
Unit_Effectivity_Id = c_unit_id)
--The following condition is necessary since the summary task may already have been
--added to the current visit which will have the same UE as the planned task
and visit_id <> c_visit_id
and simulation_plan_id = c_plan_id
and status_code not in ('CANCELLED','DELETED');
select ue.title ue_title, ue.part_number, ue.serial_number, MR.title mr_title from ahl_unit_effectivities_v ue,ahl_mr_headers_v MR where MR.mr_header_id = ue.mr_header_id
and ue.unit_effectivity_id = c_unit_id;
select ue.mr_header_id, ue.cs_incident_id,ue.csi_item_instance_id
from ahl_unit_effectivities_b ue
where ue.unit_effectivity_id = c_unit_id;
SELECT mr.title ue_title,
mtl.concatenated_segments part_number,
csi.serial_number serial_number,
mr.title mr_title
FROM ahl_mr_headers_vl mr,
mtl_system_items_kfv mtl,
csi_item_instances csi
WHERE mr.mr_header_id = c_mr_header_id
AND csi.instance_id = c_item_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND csi.inv_master_organization_id = mtl.organization_id ;
SELECT (cit.name || '-' || cs.incident_number) ue_title,
mtl.concatenated_segments part_number,
csi.serial_number serial_number,
null mr_title
FROM cs_incident_types_vl cit,
cs_incidents_all_b cs,
mtl_system_items_kfv mtl,
csi_item_instances csi
WHERE cs.incident_id = cs_incident_id
AND cit.incident_type_id = cs.incident_type_id
AND csi.instance_id = c_item_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND csi.inv_master_organization_id = mtl.organization_id ;
SELECT visit_number
FROM ahl_visits_vl
WHERE visit_id = c_visit_id;
SELECT ahlv.visit_id from ahl_visits_b ahlv, ahl_simulation_plans_b ahlsp
where ahlv.visit_id = c_visit_id
and ahlv.simulation_plan_id = ahlsp.simulation_plan_id
and ahlsp.primary_plan_flag = 'Y';
SELECT *
FROM Ahl_Visit_tasks_vl
WHERE visit_task_id = c_task_id;
SELECT NewStage.Stage_Id
FROM ahl_vwp_stages_b OldStage, ahl_vwp_stages_b NewStage
WHERE OldStage.Stage_Id = c_old_stage_id
AND NewStage.visit_id = c_new_visit_id
AND NewStage.Stage_Num = OldStage.Stage_Num;
SELECT simulation_plan_id,primary_plan_flag
INTO l_simulation_plan_id, l_plan_flag
FROM AHL_SIMULATION_PLANS_VL
WHERE simulation_plan_id = p_plan_id;
SELECT object_version_number INTO l_plan_ovn_number FROM
AHL_SIMULATION_PLANS_VL WHERE simulation_plan_id = p_plan_id;
SELECT Ahl_Visits_B_S.NEXTVAL INTO l_visit_id
FROM dual;
SELECT MAX(visit_number) INTO l_visit_number
FROM Ahl_Visits_B;
ahl_visits_pkg.Insert_Row
(
X_ROWID => l_rowid,
X_VISIT_ID => l_visit_id,
X_VISIT_NUMBER => l_visit_number+1,
X_VISIT_TYPE_CODE => l_visit_detail_rec.visit_type_code,
X_SIMULATION_PLAN_ID => p_plan_id,
X_ITEM_INSTANCE_ID => l_visit_detail_rec.item_instance_id,
X_ITEM_ORGANIZATION_ID => l_visit_detail_rec.item_organization_id,
X_INVENTORY_ITEM_ID => l_visit_detail_rec.inventory_item_id,
X_ASSO_PRIMARY_VISIT_ID => nvl(l_visit_detail_rec.asso_primary_visit_id,l_pvisit_id),
X_SIMULATION_DELETE_FLAG => NVL(l_visit_detail_rec.simulation_delete_flag,'N'),
X_TEMPLATE_FLAG => l_visit_detail_rec.template_flag,
X_OUT_OF_SYNC_FLAG => l_visit_detail_rec.out_of_sync_flag,
X_PROJECT_FLAG => l_visit_detail_rec.project_flag,
X_PROJECT_ID => l_visit_detail_rec.project_id,
X_SERVICE_REQUEST_ID => l_visit_detail_rec.service_request_id,
X_SPACE_CATEGORY_CODE => l_visit_detail_rec.space_category_code,
X_SCHEDULE_DESIGNATOR => l_visit_detail_rec.schedule_designator,
X_ATTRIBUTE_CATEGORY => l_visit_detail_rec.attribute_category,
X_ATTRIBUTE1 => l_visit_detail_rec.attribute1,
X_ATTRIBUTE2 => l_visit_detail_rec.attribute2,
X_ATTRIBUTE3 => l_visit_detail_rec.attribute3,
X_ATTRIBUTE4 => l_visit_detail_rec.attribute4,
X_ATTRIBUTE5 => l_visit_detail_rec.attribute5,
X_ATTRIBUTE6 => l_visit_detail_rec.attribute6,
X_ATTRIBUTE7 => l_visit_detail_rec.attribute7,
X_ATTRIBUTE8 => l_visit_detail_rec.attribute8,
X_ATTRIBUTE9 => l_visit_detail_rec.attribute9,
X_ATTRIBUTE10 => l_visit_detail_rec.attribute10,
X_ATTRIBUTE11 => l_visit_detail_rec.attribute11,
X_ATTRIBUTE12 => l_visit_detail_rec.attribute12,
X_ATTRIBUTE13 => l_visit_detail_rec.attribute13,
X_ATTRIBUTE14 => l_visit_detail_rec.attribute14,
X_ATTRIBUTE15 => l_visit_detail_rec.attribute15,
X_OBJECT_VERSION_NUMBER => 1,
X_ORGANIZATION_ID => l_visit_detail_rec.organization_id,
X_DEPARTMENT_ID => l_visit_detail_rec.department_id,
X_STATUS_CODE => l_visit_detail_rec.status_code,
X_START_DATE_TIME => l_visit_detail_rec.start_date_time,
X_CLOSE_DATE_TIME => l_visit_detail_rec.close_date_time,
X_VISIT_NAME => l_visit_detail_rec.visit_name,--'COPY FROM PLAN',
X_DESCRIPTION => l_visit_detail_rec.description,
X_PRICE_LIST_ID => l_visit_detail_rec.price_list_id,
X_ESTIMATED_PRICE => l_visit_detail_rec.estimated_price,
X_ACTUAL_PRICE => l_visit_detail_rec.actual_price,
X_OUTSIDE_PARTY_FLAG => l_visit_detail_rec.outside_party_flag,
X_ANY_TASK_CHG_FLAG => l_visit_detail_rec.any_task_chg_flag,
X_PRIORITY_CODE => l_visit_detail_rec.priority_code,
X_PROJECT_TEMPLATE_ID => l_visit_detail_rec.project_template_id,
X_UNIT_SCHEDULE_ID => l_visit_detail_rec.unit_schedule_id,
X_INV_LOCATOR_ID => l_visit_detail_rec.inv_locator_id, /*Added by sowsubra*/
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_vwp_stages_B_S.NEXTVAL into l_stage_id
FROM dual;
AHL_DEBUG_PUB.debug( 'visit call insert stage:'||l_stage_id);
Ahl_VWP_Stages_Pkg.Insert_Row (
X_ROWID => l_rowid,
X_VISIT_ID => l_visit_id,
X_STAGE_ID => l_stage_id,
X_STAGE_NUM => l_stage_rec.Stage_Num,
X_STAGE_NAME => l_stage_rec.Stage_Name,
X_DURATION => l_stage_rec.Duration,
X_OBJECT_VERSION_NUMBER => 1,
X_ATTRIBUTE_CATEGORY => l_stage_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_stage_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_stage_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_stage_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_stage_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_stage_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_stage_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_stage_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_stage_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_stage_rec.ATTRIBUTE9 ,
X_ATTRIBUTE10 => l_stage_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_stage_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_stage_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_stage_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_stage_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_stage_rec.ATTRIBUTE15,
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_Visit_Tasks_B_S.NEXTVAL INTO
l_visit_task_id FROM dual;
AHL_DEBUG_PUB.debug( 'visit call insert task:'||l_visit_task_id);
Split the query to select MR and SR details seperately
*/
/*
OPEN c_ue_details (l_visit_task_rec.unit_effectivity_id);
Ahl_Visit_Tasks_Pkg.INSERT_ROW (
X_ROWID => l_rowid,
X_VISIT_TASK_ID => l_visit_task_id,
X_VISIT_TASK_NUMBER => l_visit_task_rec.visit_task_number,
X_OBJECT_VERSION_NUMBER => 1,
X_VISIT_ID => l_visit_id,
X_PROJECT_TASK_ID => l_visit_task_rec.project_task_id,
X_COST_PARENT_ID => null,--l_visit_task_rec.cost_parent_id,
X_MR_ROUTE_ID => l_visit_task_rec.mr_route_id,
X_MR_ID => l_visit_task_rec.mr_id,
X_DURATION => l_visit_task_rec.duration,
X_UNIT_EFFECTIVITY_ID => l_visit_task_rec.unit_effectivity_id,
X_START_FROM_HOUR => l_visit_task_rec.start_from_hour,
X_INVENTORY_ITEM_ID => l_visit_task_rec.inventory_item_id,
X_ITEM_ORGANIZATION_ID => l_visit_task_rec.item_organization_id,
X_INSTANCE_ID => l_visit_task_rec.instance_id,
X_PRIMARY_VISIT_TASK_ID => l_primary_visit_task_id,
X_SUMMARY_TASK_FLAG => l_visit_task_rec.summary_task_flag,
X_ORIGINATING_TASK_ID => null,--l_visit_task_rec.originating_task_id,
X_SERVICE_REQUEST_ID => l_visit_task_rec.service_request_id,
X_DEPARTMENT_ID => l_visit_task_rec.department_id,
X_TASK_TYPE_CODE => l_visit_task_rec.task_type_code,
X_PRICE_LIST_ID => l_visit_task_rec.price_list_id,
X_STATUS_CODE => l_visit_task_rec.status_code,
X_ESTIMATED_PRICE => l_visit_task_rec.estimated_price,
X_ACTUAL_PRICE => l_visit_task_rec.actual_price,
X_ACTUAL_COST => l_visit_task_rec.actual_cost,
X_STAGE_ID => null,--l_visit_task_rec.stage_id,
-- Added cxcheng POST11510-- No Calculation Need for Sim---------
X_START_DATE_TIME => l_visit_task_rec.start_date_time,
X_END_DATE_TIME => l_visit_task_rec.end_date_time,
X_ATTRIBUTE_CATEGORY => l_visit_task_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_visit_task_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_visit_task_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_visit_task_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_visit_task_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_visit_task_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_visit_task_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_visit_task_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_visit_task_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_visit_task_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_visit_task_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_visit_task_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_visit_task_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_visit_task_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_visit_task_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_visit_task_rec.ATTRIBUTE15,
X_VISIT_TASK_NAME => l_visit_task_rec.visit_task_name,
X_DESCRIPTION => l_visit_task_rec.description,
X_QUANTITY => l_visit_task_rec.quantity, -- Added by rnahata for Issue 105
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 );
UPDATE AHL_VISIT_TASKS_B SET
cost_parent_id = l_cost_parent_id,
originating_task_id = l_originating_task_id,
stage_id = l_stage_id
where visit_task_id = l_new_task_id;
INSERT INTO AHL_TASK_LINKS
(
TASK_LINK_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
VISIT_TASK_ID,
PARENT_TASK_ID,
--SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
values
(
ahl_task_links_s.nextval,
1,
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
Fnd_Global.USER_ID,
l_new_task_id,
l_new_parent_task_id,
--l_task_link_rec.SECURITY_GROUP_ID,
l_task_link_rec.ATTRIBUTE_CATEGORY,
l_task_link_rec.ATTRIBUTE1,
l_task_link_rec.ATTRIBUTE2,
l_task_link_rec.ATTRIBUTE3,
l_task_link_rec.ATTRIBUTE4,
l_task_link_rec.ATTRIBUTE5,
l_task_link_rec.ATTRIBUTE6,
l_task_link_rec.ATTRIBUTE7,
l_task_link_rec.ATTRIBUTE8,
l_task_link_rec.ATTRIBUTE9,
l_task_link_rec.ATTRIBUTE10,
l_task_link_rec.ATTRIBUTE11,
l_task_link_rec.ATTRIBUTE12,
l_task_link_rec.ATTRIBUTE13,
l_task_link_rec.ATTRIBUTE14,
l_task_link_rec.ATTRIBUTE15
);
SELECT AHL_SPACE_ASSIGNMENTS_S.NEXTVAL INTO l_space_assignment_id
FROM dual;
INSERT INTO AHL_SPACE_ASSIGNMENTS
(
SPACE_ASSIGNMENT_ID,
SPACE_ID,
VISIT_ID,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_space_assignment_id,
l_space_id,
l_visit_id,
1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SYSDATE,
Fnd_Global.user_id,
SYSDATE,
Fnd_Global.user_id,
Fnd_Global.login_id
);
SELECT *
FROM ahl_visit_tasks_vl
WHERE visit_id = c_visit_id;
SELECT space_assignment_id
FROM ahl_space_assignments
WHERE visit_id = c_visit_id;
SELECT COUNT(*) FROM Ahl_Task_Links L ,Ahl_Visit_Tasks_B T
WHERE (T.VISIT_TASK_ID = L.VISIT_TASK_ID OR T.VISIT_TASK_ID = L.PARENT_TASK_ID)
AND T.VISIT_TASK_ID = x_id;
SELECT 'X' from ahl_visit_tasks_b where unit_effectivity_id = c_ue_id
AND visit_id <> c_visit_id;
SELECT 'X' from ahl_visit_tasks_b where
originating_task_id = c_originating_task_id and task_type_code = 'UNPLANNED';
TYPE delete_unit_effectivity_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_delete_unit_effectivity_tbl delete_unit_effectivity_tbl;
/* Added by mpothuku on 12/28/04 to delete the links */
-- If a task being deleted has associated Children Tasks, tasks that define it as a parent,
-- the association must be removed.
OPEN c_links (l_visit_task_id);
DELETE Ahl_Task_Links
WHERE VISIT_TASK_ID = l_visit_task_id
OR PARENT_TASK_ID = l_visit_task_id;
/* Change by mpothuku on 02/03/05 to delete the unit effectivities for Unplanned tasks before removing the association */
IF(l_task_rec.TASK_TYPE_CODE = 'SUMMARY' AND l_task_rec.mr_id is not null
AND l_task_rec.originating_task_id is null) THEN
-- Find out if the UE is associated with any other Active Visits
-- Ideally if any are found they should be Simulation Visits only
OPEN check_summary_task_unplanned(l_task_rec.visit_task_id);
l_delete_unit_effectivity_tbl(ue_count) := l_task_rec.UNIT_EFFECTIVITY_ID;
AHL_VISIT_TASKS_PKG.DELETE_ROW (
X_VISIT_TASK_ID => l_visit_task_id);
if(l_delete_unit_effectivity_tbl.count > 0) THEN
for ue_count in 0..l_delete_unit_effectivity_tbl.count -1
LOOP
IF(l_delete_unit_effectivity_tbl(ue_count) is not null) THEN
AHL_UMP_UNPLANNED_PVT.DELETE_UNIT_EFFECTIVITY
(
P_API_VERSION => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_UNIT_EFFECTIVITY_ID => l_delete_unit_effectivity_tbl(ue_count)
);
DELETE FROM ahl_space_assignments
WHERE space_assignment_id = l_space_assignment_id;
/* Added by mpothuku on 12/28/04 to delete the links */
--Remove the stages before the visit is deleted
ahl_vwp_visits_stages_pvt.delete_stages
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_module_type => NULL,
p_visit_id => p_visit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
AHL_VISITS_PKG.DELETE_ROW (
X_VISIT_ID => p_visit_id);
PROCEDURE Toggle_Simulation_Delete (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_visit_id IN NUMBER,
p_visit_object_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR visit_detail_cur(c_visit_id IN NUMBER)
IS
SELECT VISIT_ID,
OBJECT_VERSION_NUMBER,
SIMULATION_DELETE_FLAG
FROM AHL_VISITS_VL
WHERE VISIT_ID = c_visit_id;
l_api_name CONSTANT VARCHAR2(30) := 'TOGGLE_SIMULATION_DELETE';
l_simulation_delete_flag VARCHAR2(1);
SAVEPOINT toggle_simulation_delete;
AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.toggle simulation delete','+SMPNL+');
l_simulation_delete_flag;
IF l_simulation_delete_flag = 'N' THEN
UPDATE AHL_VISITS_B
SET SIMULATION_DELETE_FLAG = 'Y',
-- mpothuku start on 12/22/04
OBJECT_VERSION_NUMBER = l_object_version_number + 1
-- mpothuku End
WHERE visit_id = p_visit_id;
UPDATE AHL_VISITS_B
SET SIMULATION_DELETE_FLAG = 'N',
--Added by mpothuku on 12/22/04
OBJECT_VERSION_NUMBER = l_object_version_number + 1
-- mpothuku End
WHERE visit_id = p_visit_id;
Ahl_Debug_Pub.debug( 'End of private api Toggle Simulation Delete','+SMPLN+');
ROLLBACK TO toggle_simulation_delete;
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Toggle Simulation Delete','+SMPLN+');
ROLLBACK TO toggle_simulation_delete;
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Toggle Simulation Delete','+SMPLN+');
ROLLBACK TO toggle_simulation_delete;
p_procedure_name => 'TOGGLE_SIMULATION_DELETE',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Toggle Simulation Delete','+SMPLN+');
END Toggle_Simulation_Delete;
SELECT simulation_plan_id,
object_version_number,
primary_plan_flag
FROM AHL_SIMULATION_PLANS_VL
WHERE SIMULATION_PLAN_ID = c_plan_id;
SELECT visit_id,object_version_number
FROM AHL_VISITS_VL
WHERE SIMULATION_PLAN_ID = c_plan_id;
SELECT visit_id FROM
AHL_VISITS_VL
WHERE SIMULATION_PLAN_ID = c_plan_id;
AHL_SIMULATION_PLANS_PKG.DELETE_ROW (
X_SIMULATION_PLAN_ID => p_plan_id);
ROLLBACK TO toggle_simulation_delete;
SELECT VISIT_ID,
VISIT_NUMBER,
VISIT_TYPE_CODE,
SIMULATION_PLAN_ID,
ITEM_INSTANCE_ID,
ITEM_ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ASSO_PRIMARY_VISIT_ID,
SIMULATION_DELETE_FLAG,
TEMPLATE_FLAG,
OUT_OF_SYNC_FLAG,
PROJECT_FLAG,
PROJECT_ID,
SERVICE_REQUEST_ID,
SPACE_CATEGORY_CODE,
SCHEDULE_DESIGNATOR,
PRIORITY_CODE,
PROJECT_TEMPLATE_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OBJECT_VERSION_NUMBER,
ORGANIZATION_ID,
DEPARTMENT_ID,
STATUS_CODE,
START_DATE_TIME,
CLOSE_DATE_TIME,
PRICE_LIST_ID,
ESTIMATED_PRICE,
ACTUAL_PRICE,
OUTSIDE_PARTY_FLAG,
ANY_TASK_CHG_FLAG,
UNIT_SCHEDULE_ID,
VISIT_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
INV_LOCATOR_ID --Added by sowsubra
FROM AHL_VISITS_VL
WHERE VISIT_ID = c_visit_id
AND SIMULATION_PLAN_ID = c_plan_id;
SELECT simulation_plan_id
FROM AHL_SIMULATION_PLANS_VL
WHERE simulation_plan_id = c_plan_id
AND primary_plan_flag = 'Y';
SELECT
ATSK.VISIT_TASK_ID,
ATSK.VISIT_TASK_NUMBER,
ATSK.OBJECT_VERSION_NUMBER,
ATSK.VISIT_ID,
ATSK.PROJECT_TASK_ID,
ATSK.COST_PARENT_ID,
ATSK.MR_ROUTE_ID,
ATSK.MR_ID,
ATSK.DURATION,
ATSK.UNIT_EFFECTIVITY_ID,
ATSK.VISIT_TASK_NAME,
ATSK.DESCRIPTION,
ATSK.START_FROM_HOUR,
ATSK.INVENTORY_ITEM_ID,
ATSK.ITEM_ORGANIZATION_ID,
ATSK.INSTANCE_ID,
ATSK.PRIMARY_VISIT_TASK_ID,
ATSK.SUMMARY_TASK_FLAG,
ATSK.ORIGINATING_TASK_ID,
ATSK.SERVICE_REQUEST_ID,
ATSK.TASK_TYPE_CODE,
ATSK.DEPARTMENT_ID,
ATSK.PRICE_LIST_ID,
ATSK.STATUS_CODE,
ATSK.ACTUAL_COST,
ATSK.ESTIMATED_PRICE,
ATSK.ACTUAL_PRICE,
ATSK.STAGE_ID,
ATSK.START_DATE_TIME,
ATSK.END_DATE_TIME,
ATSK.QUANTITY, -- Added by rnahata for Issue 105
ATSK.ATTRIBUTE_CATEGORY,
ATSK.ATTRIBUTE1,
ATSK.ATTRIBUTE2,
ATSK.ATTRIBUTE3,
ATSK.ATTRIBUTE4,
ATSK.ATTRIBUTE5,
ATSK.ATTRIBUTE6,
ATSK.ATTRIBUTE7,
ATSK.ATTRIBUTE8,
ATSK.ATTRIBUTE9,
ATSK.ATTRIBUTE10,
ATSK.ATTRIBUTE11,
ATSK.ATTRIBUTE12,
ATSK.ATTRIBUTE13,
ATSK.ATTRIBUTE14,
ATSK.ATTRIBUTE15,
MTSB.CONCATENATED_SEGMENTS ITEM_NAME,
CSIS.SERIAL_NUMBER SERIAL_NUMBER
FROM ahl_visit_tasks_vl ATSK,
MTL_SYSTEM_ITEMS_B_KFV MTSB,
CSI_ITEM_INSTANCES CSIS
WHERE visit_id = c_visit_id and
ATSK.INSTANCE_ID = CSIS.INSTANCE_ID (+) and
ATSK. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+) AND
ATSK. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+) AND
STATUS_CODE <> 'DELETED';
SELECT visit_task_id
FROM ahl_visit_tasks_vl
WHERE visit_id = c_visit_id
AND visit_task_id = c_visit_task_id
AND status_code <> 'DELETED';
SELECT visit_task_id
FROM ahl_visit_tasks_vl
WHERE visit_id = c_visit_id;
SELECT primary_visit_task_id
FROM ahl_visit_tasks_vl
WHERE visit_id = c_visit_id
AND primary_visit_task_id = c_visit_task_id
AND status_code <> 'DELETED' ;
CURSOR get_tasks_delete_csr(x_id IN NUMBER)
IS
SELECT visit_task_id,object_version_number,visit_task_number
FROM Ahl_Visit_Tasks_VL
WHERE VISIT_ID = x_id AND NVL(STATUS_CODE,'X') <> 'DELETED'
AND ((TASK_TYPE_CODE = 'SUMMARY' AND ORIGINATING_TASK_ID IS NULL)
OR TASK_TYPE_CODE = 'UNASSOCIATED'
OR (TASK_TYPE_CODE = 'SUMMARY' AND MR_ID IS NULL));
SELECT space_assignment_id
FROM ahl_space_assignments
WHERE visit_id = c_visit_id;
SELECT COUNT(*) FROM Ahl_Task_Links L ,Ahl_Visit_Tasks_B T
WHERE (T.VISIT_TASK_ID = L.VISIT_TASK_ID OR T.VISIT_TASK_ID = L.PARENT_TASK_ID)
AND T.VISIT_TASK_ID = x_id;
SELECT VISIT_TASK_ID ,
PARENT_TASK_ID,
--SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM AHL_TASK_LINKS
WHERE visit_task_id in ( SELECT VISIT_TASK_ID
FROM AHL_VISIT_TASKS_B
WHERE visit_id = x_visit_id);
SELECT b.VISIT_TASK_ID,b.VISIT_TASK_NUMBER
FROM AHL_VISIT_TASKS_B a, AHL_VISIT_TASKS_B b
WHERE a.visit_task_id = x_visit_task_id
AND a.visit_task_number = b.visit_task_number
AND b.visit_id = x_new_visit_id;
SELECT STAGE_ID,
STAGE_NUM,
VISIT_ID,
DURATION,
OBJECT_VERSION_NUMBER,
STAGE_NAME,
--SECURITY_GROUP_ID
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM ahl_vwp_stages_vl s
WHERE visit_id = c_visit_id
ORDER BY stage_num;
SELECT NewStage.Stage_Id, NewStage.Stage_Name
FROM ahl_vwp_stages_vl OldStage, ahl_vwp_stages_vl NewStage
WHERE OldStage.Stage_Id = c_old_stage_id
AND NewStage.visit_id = c_new_visit_id
AND NewStage.Stage_Num = OldStage.Stage_Num;
SELECT VISIT_NUMBER FROM AHL_VISITS_B ahlv ,AHL_SIMULATION_PLANS_B ahlp WHERE
VISIT_ID IN (SELECT DISTINCT VISIT_ID FROM AHL_VISIT_TASKS_B WHERE
Unit_Effectivity_Id = c_unit_id)
and visit_id <> c_visit_id
and ahlv.simulation_plan_id = ahlp.simulation_plan_id
and ahlp.primary_plan_flag = 'Y'
--The following condition is necessary since the summary task may already have been
--added to the current visit which will have the same UE as the planned task
and status_code not in ('CANCELLED','DELETED');
select ue.title ue_title, ue.part_number, ue.serial_number, MR.title mr_title from ahl_unit_effectivities_v ue,ahl_mr_headers_v MR where MR.mr_header_id = ue.mr_header_id
and ue.unit_effectivity_id = c_unit_id;
select ue.mr_header_id, ue.cs_incident_id,ue.csi_item_instance_id
from ahl_unit_effectivities_b ue
where ue.unit_effectivity_id = c_unit_id;
SELECT mr.title ue_title,
mtl.concatenated_segments part_number,
csi.serial_number serial_number,
mr.title mr_title
FROM ahl_mr_headers_vl mr,
mtl_system_items_kfv mtl,
csi_item_instances csi
WHERE mr.mr_header_id = c_mr_header_id
AND csi.instance_id = c_item_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND csi.inv_master_organization_id = mtl.organization_id ;
SELECT (cit.name || '-' || cs.incident_number) ue_title,
mtl.concatenated_segments part_number,
csi.serial_number serial_number,
null mr_title
FROM cs_incident_types_vl cit,
cs_incidents_all_b cs,
mtl_system_items_kfv mtl,
csi_item_instances csi
WHERE cs.incident_id = cs_incident_id
AND cit.incident_type_id = cs.incident_type_id
AND csi.instance_id = c_item_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND csi.inv_master_organization_id = mtl.organization_id ;
SELECT *
FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SELECT *
FROM Ahl_Visit_tasks_vl
WHERE visit_task_id = c_task_id;
SELECT prim.visit_task_id, prim.visit_task_number FROM
ahl_visit_tasks_b prim, ahl_visit_tasks_b sim
WHERE
sim.visit_task_id = c_simulation_task_id and
prim.visit_task_id = sim.primary_visit_task_id;
SELECT organization_id,department_id,start_date_time
FROM ahl_visits_vl
WHERE VISIT_ID = c_visit_id;
SELECT subinventory_code, CONCATENATED_SEGMENTS
FROM mtl_item_locations_kfv
WHERE inventory_location_id = p_inv_locator_id
-- jaramana on Feb 14, 2008 for bug 6819370
-- Removed null check on segment19 and segment20
AND organization_id = p_org_id;
SELECT ds.inv_locator_id, mtl.subinventory_code, mtl.CONCATENATED_SEGMENTS
FROM ahl_department_shifts_v ds, hr_organization_units hou, mtl_item_locations_kfv mtl
WHERE hou.organization_id = p_org_id
AND hou.name = ds.organization_name
AND ds.department_id = p_dept_id
AND hou.organization_id = mtl.organization_id
AND ds.inv_locator_id = mtl.inventory_location_id;
l_tasks_delete_rec get_tasks_delete_csr%ROWTYPE;
SELECT simulation_plan_id,
primary_plan_flag,
object_version_number INTO
l_simulation_plan_id,l_primary_plan_flag, l_s_object_number
FROM AHL_SIMULATION_PLANS_VL
WHERE simulation_plan_id = p_plan_id
AND primary_plan_flag = 'N';
SELECT VISIT_ID,VISIT_NUMBER, a.SIMULATION_PLAN_ID INTO
l_primary_visit_id, l_primary_visit_number,
l_simulation_plan_id
FROM ahl_visits_vl a, ahl_simulation_plans_vl b
WHERE a.visit_id = l_simul_visit_rec.asso_primary_visit_id
and a.simulation_plan_id = b.simulation_plan_id
and b.primary_plan_flag = 'Y';
AHL_DEBUG_PUB.debug( 'before update id :'||l_primary_visit_id);
AHL_DEBUG_PUB.debug( 'before update number:'||l_primary_visit_number);
IF l_simul_visit_rec.simulation_delete_flag = 'Y' THEN --Remove the Primary Visit
/* Modified by mpothuku on 01/25/05 to delete the primary visit if the Simulation Flag is delete */
/*
Fnd_message.SET_NAME('AHL','AHL_LTP_VISIT_REMOVED');
AHL_DEBUG_PUB.debug( 'after else update id :'||l_primary_visit_id);
AHL_DEBUG_PUB.debug( 'after else update number:'||l_primary_visit_number);
l_prim_visit_rec.SIMULATION_DELETE_FLAG := 'N';
AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
(
p_visit_id => l_primary_visit_id,
p_flag => 'Y',
x_return_status => l_return_status
);
OPEN get_tasks_delete_csr (l_primary_visit_id);
FETCH get_tasks_delete_csr INTO l_tasks_delete_rec;
EXIT WHEN get_tasks_delete_csr%NOTFOUND;
IF l_tasks_delete_rec.visit_task_id IS NOT NULL THEN
/* Added by mpothuku on 01/11/04 */
l_sim_prim_visit_task_id := null;
OPEN check_prim_visit_task_cur(p_visit_id,l_tasks_delete_rec.visit_task_id);
AHL_VWP_TASKS_PVT.Delete_Task
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
p_validation_level => p_validation_level,
p_module_type => NULL,
p_visit_task_id => l_tasks_delete_rec.visit_task_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
CLOSE get_tasks_delete_csr;
CLOSE get_tasks_delete_csr;
AHL_VWP_TASKS_PVT.Update_Task
(
p_api_version => p_api_version,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
--passing null here as we dont want the OrigtaskId,
--to be picked up as the value we are passing at this point.
p_module_type => null,
p_x_task_rec => l_prim_visit_task_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
1. Either the task is deleted from primary visit after copying to simulation visit Or
2. The task is created in the Simulation visit.
*/
THEN
/* Added by mpothuku on 01/11/04 to insert new row into the PrimaryVisit */
SELECT Ahl_Visit_Tasks_B_S.NEXTVAL INTO
l_visit_task_id FROM dual;
AHL_DEBUG_PUB.debug( 'visit call insert new task created in simulation:'||l_simul_visit_task_rec.visit_task_number);
Ahl_Visit_Tasks_Pkg.INSERT_ROW
(
X_ROWID => l_rowid,
X_VISIT_TASK_ID => l_visit_task_id,
X_VISIT_TASK_NUMBER => l_visit_task_number,
X_OBJECT_VERSION_NUMBER => 1,
X_VISIT_ID => l_primary_visit_id,
X_PROJECT_TASK_ID => l_simul_visit_task_rec.project_task_id,
X_COST_PARENT_ID => null,
X_MR_ROUTE_ID => l_simul_visit_task_rec.mr_route_id,
X_MR_ID => l_simul_visit_task_rec.mr_id,
X_DURATION => l_simul_visit_task_rec.duration,
X_UNIT_EFFECTIVITY_ID => l_simul_visit_task_rec.unit_effectivity_id,
X_START_FROM_HOUR => l_simul_visit_task_rec.start_from_hour,
X_INVENTORY_ITEM_ID => l_simul_visit_task_rec.inventory_item_id,
X_ITEM_ORGANIZATION_ID => l_simul_visit_task_rec.item_organization_id,
X_INSTANCE_ID => l_simul_visit_task_rec.instance_id,
X_PRIMARY_VISIT_TASK_ID => null,
X_SUMMARY_TASK_FLAG => l_simul_visit_task_rec.summary_task_flag,
X_ORIGINATING_TASK_ID => null,
X_SERVICE_REQUEST_ID => l_simul_visit_task_rec.service_request_id,
X_TASK_TYPE_CODE => l_simul_visit_task_rec.task_type_code,
X_PRICE_LIST_ID => null,
X_STATUS_CODE => l_simul_visit_task_rec.status_code,
X_ESTIMATED_PRICE => null,
X_ACTUAL_PRICE => null,
X_ACTUAL_COST => null,
X_STAGE_ID => null,
-- Added cxcheng POST11510-- No Calculation Need for Sim---------
X_START_DATE_TIME => l_simul_visit_task_rec.start_date_time,
X_END_DATE_TIME => l_simul_visit_task_rec.end_date_time,
X_ATTRIBUTE_CATEGORY => l_simul_visit_task_rec.attribute_category,
X_ATTRIBUTE1 => l_simul_visit_task_rec.attribute1,
X_ATTRIBUTE2 => l_simul_visit_task_rec.attribute2,
X_ATTRIBUTE3 => l_simul_visit_task_rec.attribute3,
X_ATTRIBUTE4 => l_simul_visit_task_rec.attribute4,
X_ATTRIBUTE5 => l_simul_visit_task_rec.attribute5,
X_ATTRIBUTE6 => l_simul_visit_task_rec.attribute6,
X_ATTRIBUTE7 => l_simul_visit_task_rec.attribute7,
X_ATTRIBUTE8 => l_simul_visit_task_rec.attribute8,
X_ATTRIBUTE9 => l_simul_visit_task_rec.attribute9,
X_ATTRIBUTE10 => l_simul_visit_task_rec.attribute10,
X_ATTRIBUTE11 => l_simul_visit_task_rec.attribute11,
X_ATTRIBUTE12 => l_simul_visit_task_rec.attribute12,
X_ATTRIBUTE13 => l_simul_visit_task_rec.attribute13,
X_ATTRIBUTE14 => l_simul_visit_task_rec.attribute14,
X_ATTRIBUTE15 => l_simul_visit_task_rec.attribute15,
X_VISIT_TASK_NAME => l_simul_visit_task_rec.visit_task_name,
X_DESCRIPTION => l_simul_visit_task_rec.description,
X_DEPARTMENT_ID => l_simul_visit_task_rec.department_id,
X_QUANTITY => l_simul_visit_task_rec.quantity, -- Added by rnahata for Issue 105
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
);
/* Need to update the simulation_visit's primary_visit_task_id with the Id thats generated here */
UPDATE ahl_visit_tasks_b
SET primary_visit_task_id = l_visit_task_id
WHERE visit_task_id = l_simul_visit_task_rec.visit_task_id;
AHL_DEBUG_PUB.debug( 'After insertion of simulation visit task:'||l_simul_visit_task_rec.visit_task_id);
AHL_VWP_TASKS_PVT.Update_Task
(
p_api_version => p_api_version,
p_init_msg_list => Fnd_Api.g_false,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
--passing LTP here as we want the OrigtaskId,
--to be picked up as the value we are passing.
p_module_type => 'LTP',
p_x_task_rec => l_prim_visit_task_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
/* Added by mpothuku on 01/11/04 to delete the existing links */
OPEN c_links (l_exist_prim_visit_task_id);
DELETE Ahl_Task_Links
WHERE VISIT_TASK_ID = l_exist_prim_visit_task_id
OR PARENT_TASK_ID = l_exist_prim_visit_task_id;
INSERT INTO AHL_TASK_LINKS
(
TASK_LINK_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
VISIT_TASK_ID,
PARENT_TASK_ID,
--SECURITY_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
values
(
ahl_task_links_s.nextval,
1,
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
Fnd_Global.USER_ID,
l_new_task_id,
l_new_parent_task_id,
--l_task_link_rec.SECURITY_GROUP_ID,
l_task_link_rec.ATTRIBUTE_CATEGORY,
l_task_link_rec.ATTRIBUTE1,
l_task_link_rec.ATTRIBUTE2,
l_task_link_rec.ATTRIBUTE3,
l_task_link_rec.ATTRIBUTE4,
l_task_link_rec.ATTRIBUTE5,
l_task_link_rec.ATTRIBUTE6,
l_task_link_rec.ATTRIBUTE7,
l_task_link_rec.ATTRIBUTE8,
l_task_link_rec.ATTRIBUTE9,
l_task_link_rec.ATTRIBUTE10,
l_task_link_rec.ATTRIBUTE11,
l_task_link_rec.ATTRIBUTE12,
l_task_link_rec.ATTRIBUTE13,
l_task_link_rec.ATTRIBUTE14,
l_task_link_rec.ATTRIBUTE15
);
AHL_DEBUG_PUB.debug( 'visit call update stage:'||l_stage_rec.stage_id);
Ahl_VWP_Stages_Pkg.Update_Row (
X_VISIT_ID => l_primary_visit_id,
X_STAGE_ID => l_new_stage_id,
X_STAGE_NUM => l_stage_rec.Stage_Num,
X_STAGE_NAME => l_stage_rec.Stage_Name,
X_DURATION => l_stage_rec.Duration,
X_OBJECT_VERSION_NUMBER => l_stage_rec.object_version_number+1,
X_ATTRIBUTE_CATEGORY => l_stage_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_stage_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_stage_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_stage_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_stage_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_stage_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_stage_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_stage_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_stage_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_stage_rec.ATTRIBUTE9 ,
X_ATTRIBUTE10 => l_stage_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_stage_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_stage_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_stage_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_stage_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_stage_rec.ATTRIBUTE15,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
AHL_DEBUG_PUB.debug( 'before delete simulation visit:'||p_visit_id);
/* Only if Simulation Flag is not set we ought to delete the Simulation Visit otherwise it
anyway be deleted in process Visit
*/
Remove_Visits_FR_Plan
(
p_api_version => p_api_version,
p_init_msg_list => FND_API.g_false,--p_init_msg_list,
p_commit => FND_API.g_false, --p_commit,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_id => p_visit_id,
p_plan_id => null,
p_v_ovn => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
PROCEDURE Delete_Simul_Visits (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
p_visit_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
--
IS
-- Get visits belongs to simulation plans
CURSOR Get_simul_visits_cur (C_VISIT_ID IN NUMBER)
IS
SELECT vt.visit_id, vt.visit_number,
vt.asso_primary_visit_id
FROM ahl_visits_vl vt, ahl_simulation_plans_vl sp
WHERE vt.simulation_plan_id = sp.simulation_plan_id
AND sp.primary_plan_flag = 'N'
AND vt.asso_primary_visit_id = C_VISIT_ID;
SELECT visit_task_id
FROM ahl_visit_tasks_vl
WHERE visit_id = C_VISIT_ID;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SIMUL_VISITS';
SAVEPOINT Delete_Simul_Visits;
AHL_DEBUG_PUB.debug( 'enter ahl_ltp_simul_plan_pvt.Delete Simul Visits','+SMPNL+');
Ahl_Debug_Pub.debug( 'End of private api Delete_Simul_Visits','+SMPLN+');
ROLLBACK TO Delete_Simul_Visits;
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt. Delete Simul Visits','+SMPLN+');
ROLLBACK TO Delete_Simul_Visits;
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt.Delete Simul Visits','+SMPLN+');
ROLLBACK TO Delete_Simul_Visits;
p_procedure_name => 'DELETE_SIMUL_VISITS',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_ltp_simul_plan_pvt. Delete Simul Visits','+SMPLN+');
END Delete_Simul_Visits;