[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Visit (
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_x_Visit_Rec IN OUT NOCOPY visit_rec_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
PROCEDURE Delete_Visit (
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
);
SELECT COUNT(*)
FROM Ahl_Visit_Tasks_B
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') <> 'DELETED';
SELECT COUNT(*)
FROM Ahl_Visit_Tasks_B
WHERE VISIT_ID = x_id
AND UNIT_EFFECTIVITY_ID IS NOT NULL
AND NVL(STATUS_CODE,'X') <> 'DELETED';
SELECT MIN(T1.due_date)
FROM ahl_unit_effectivities_vl T1, ahl_visit_tasks_b T2
WHERE T1.unit_effectivity_id = T2.unit_effectivity_id
AND T1.due_date IS NOT NULL AND T2.visit_id = x_id;
IF p_x_visit_rec.SIMULATION_DELETE_FLAG = Fnd_Api.G_MISS_CHAR THEN
p_x_visit_rec.SIMULATION_DELETE_FLAG := NULL;
p_x_visit_rec.SIMULATION_DELETE_FLAG := p_x_visit_rec.SIMULATION_DELETE_FLAG;
SELECT Ahl_Visits_B_S.NEXTVAL
FROM dual;
SELECT 1 FROM Ahl_Visits_VL
WHERE Visit_id = x_id;
SELECT MAX(visit_number)
FROM Ahl_Visits_B;
SELECT MAX(visit_task_number)
FROM Ahl_Visit_Tasks_B
WHERE Visit_Id = p_visit_id;
SELECT STATUS_CODE FROM AHL_VISITS_B
WHERE VISIT_ID = x_visit_id;
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Update_Visit');
Update_Visit
(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_Visit_rec => p_x_Visit_tbl(i),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Update_Visit - l_return_status : '||l_return_status);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Delete_Visit');
Delete_Visit
(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_Visit_id => p_x_visit_tbl(i).visit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Delete_Visit - l_return_status : '||l_return_status);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Delete_Visit');
Delete_Visit
(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_Visit_id => p_x_visit_tbl(i).visit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Delete_Visit - l_return_status : '|| l_return_status);
select flight_number
from ahl_unit_schedules
where unit_schedule_id=x_id;
/* SELECT AVTS.VISIT_ID , AVTS.VISIT_NUMBER, AVTS.VISIT_NAME,
AVTS.ORGANIZATION_ID , HROU.NAME ORGANIZATION_NAME, AVTS.DEPARTMENT_ID ,
BDPT.DESCRIPTION DEPARTMENT_NAME , AVTS.OBJECT_VERSION_NUMBER,
AVTS.START_DATE_TIME,
AVTS.STATUS_CODE, FLVT1.MEANING STATUS_MEAN, AVTS.TEMPLATE_FLAG,
AVTS.ITEM_INSTANCE_ID , CSIS.SERIAL_NUMBER , AVTS.INVENTORY_ITEM_ID ,
AVTS.ITEM_ORGANIZATION_ID , MTSB.CONCATENATED_SEGMENTS ITEM_DESCRIPTION,
AVTS.VISIT_TYPE_CODE , FLVT.MEANING VISIT_TYPE_MEAN, AVTS.SIMULATION_PLAN_ID,
ASPV.SIMULATION_PLAN_NAME, NVL(ASPV.PRIMARY_PLAN_FLAG,'Y') ,
AVTS.SPACE_CATEGORY_CODE, FLVT2.MEANING SPACE_CATEGORY_MEAN,
AVTS.SERVICE_REQUEST_ID,
AVTS.CLOSE_DATE_TIME, CSAB.INCIDENT_NUMBER, UC.NAME UNIT_NAME,
AVTS.PRIORITY_CODE,
FLVT3.MEANING PRIORITY_MEAN, AVTS.PROJECT_TEMPLATE_ID,
PA.NAME PROJECT_TEMPLATE_NAME ,
AVTS.UNIT_SCHEDULE_ID, AVTS.ASSO_PRIMARY_VISIT_ID
FROM AHL_VISITS_VL AVTS, AHL_SIMULATION_PLANS_VL ASPV,
CSI_ITEM_INSTANCES CSIS, HR_ALL_ORGANIZATION_UNITS HROU,
BOM_DEPARTMENTS BDPT, MTL_SYSTEM_ITEMS_B_KFV MTSB,
FND_LOOKUP_VALUES_VL FLVT, FND_LOOKUP_VALUES_VL FLVT1,
FND_LOOKUP_VALUES_VL FLVT2, FND_LOOKUP_VALUES_VL FLVT3,
PA_PROJECTS_ALL PA, AHL_UNIT_CONFIG_HEADERS UC, CS_INCIDENTS_ALL_B CSAB
WHERE AVTS.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID(+)
AND AVTS.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID(+)
AND AVTS.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
AND AVTS.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND AVTS.SIMULATION_PLAN_ID = ASPV.SIMULATION_PLAN_ID(+)
AND AVTS. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND AVTS. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
AND FLVT.LOOKUP_TYPE(+) ='AHL_PLANNING_VISIT_TYPE'
AND FLVT.LOOKUP_CODE(+) = AVTS.VISIT_TYPE_CODE
AND FLVT1.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_STATUS'
AND FLVT1.LOOKUP_CODE(+) = AVTS.STATUS_CODE
AND FLVT2.LOOKUP_TYPE(+) = 'AHL_LTP_SPACE_CATEGORY'
AND FLVT2.LOOKUP_CODE(+) = AVTS.SPACE_CATEGORY_CODE
AND FLVT3.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_PRIORITY'
AND FLVT3.LOOKUP_CODE(+) = AVTS.PRIORITY_CODE
AND PA.PROJECT_ID(+) = AVTS.PROJECT_TEMPLATE_ID
AND AVTS.SERVICE_REQUEST_ID = CSAB.INCIDENT_ID(+)
AND AVTS.TEMPLATE_FLAG = 'N' AND AVTS.STATUS_CODE <> 'DELETED'
AND UC.active_end_date is null
AND VISIT_ID = x_id;*/
SELECT AVTS.VISIT_ID , AVTS.VISIT_NUMBER,
AVTSTL.VISIT_NAME,AVTS.ORGANIZATION_ID ,
AVTS.START_DATE_TIME, AVTS.CLOSE_DATE_TIME,AVTS.VISIT_TYPE_CODE ,
AVTS.DEPARTMENT_ID ,AVTS.STATUS_CODE,AVTS.OBJECT_VERSION_NUMBER,
HROU.NAME ORGANIZATION_NAME,
BDPT.DESCRIPTION DEPARTMENT_NAME ,
FLVT1.MEANING STATUS_MEAN,
AVTS.TEMPLATE_FLAG,AVTS.ITEM_INSTANCE_ID ,
AVTS.INVENTORY_ITEM_ID,AVTS.ITEM_ORGANIZATION_ID,
AVTS.SIMULATION_PLAN_ID,AVTS.SERVICE_REQUEST_ID,
AVTS.PRIORITY_CODE,AVTS.SPACE_CATEGORY_CODE,
AVTS.PROJECT_TEMPLATE_ID,AVTS.UNIT_SCHEDULE_ID,AVTS.ASSO_PRIMARY_VISIT_ID,
CSIS.SERIAL_NUMBER ,
MTSB.CONCATENATED_SEGMENTS ITEM_DESCRIPTION,
FLVT.MEANING VISIT_TYPE_MEAN,FLVT3.MEANING
PRIORITY_MEAN,FLVT2.MEANING SPACE_CATEGORY_MEAN,
ASPVTL.SIMULATION_PLAN_NAME, NVL(ASPV.PRIMARY_PLAN_FLAG,'Y') ,
CSAB.INCIDENT_NUMBER, UC.NAME UNIT_NAME
,PA.NAME PROJECT_TEMPLATE_NAME,
AVTS.INV_LOCATOR_ID -- Added by sowsubra
FROM AHL_VISITS_B AVTS,AHL_VISITS_TL AVTSTL,
AHL_SIMULATION_PLANS_B ASPV,AHL_SIMULATION_PLANS_TL ASPVTL,
CSI_ITEM_INSTANCES CSIS, HR_ALL_ORGANIZATION_UNITS_TL HROU,
BOM_DEPARTMENTS BDPT, MTL_SYSTEM_ITEMS_B_KFV MTSB,
FND_LOOKUP_VALUES FLVT, FND_LOOKUP_VALUES FLVT1,
FND_LOOKUP_VALUES FLVT2, FND_LOOKUP_VALUES FLVT3,
PA_PROJECTS_ALL PA,
AHL_UNIT_CONFIG_HEADERS UC, CS_INCIDENTS_ALL_B CSAB
WHERE AVTS.VISIT_ID = AVTSTL.VISIT_ID
AND AVTSTL.LANGUAGE = USERENV('LANG')
AND ASPV.SIMULATION_PLAN_ID = ASPVTL.SIMULATION_PLAN_ID(+)
AND ASPVTL.LANGUAGE(+) = USERENV('LANG')
AND AVTS.ITEM_INSTANCE_ID = UC.CSI_ITEM_INSTANCE_ID(+)
AND AVTS.ITEM_INSTANCE_ID = CSIS.INSTANCE_ID(+)
AND AVTS.ORGANIZATION_ID = HROU.ORGANIZATION_ID(+)
AND HROU.LANGUAGE(+) = USERENV('LANG')
AND AVTS.DEPARTMENT_ID = BDPT.DEPARTMENT_ID(+)
AND AVTS.SIMULATION_PLAN_ID = ASPV.SIMULATION_PLAN_ID(+)
AND AVTS. INVENTORY_ITEM_ID = MTSB.INVENTORY_ITEM_ID(+)
AND AVTS. ITEM_ORGANIZATION_ID = MTSB.ORGANIZATION_ID(+)
AND FLVT.LOOKUP_TYPE(+) ='AHL_PLANNING_VISIT_TYPE'
AND FLVT.LOOKUP_CODE(+) = AVTS.VISIT_TYPE_CODE
AND FLVT.LANGUAGE(+) = userenv('LANG')
AND FLVT1.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_STATUS'
AND FLVT1.LOOKUP_CODE(+) = AVTS.STATUS_CODE
AND FLVT1.LANGUAGE(+) = userenv('LANG')
AND FLVT2.LOOKUP_TYPE(+) = 'AHL_LTP_SPACE_CATEGORY'
AND FLVT2.LOOKUP_CODE(+) = AVTS.SPACE_CATEGORY_CODE
AND FLVT2.LANGUAGE(+) = userenv('LANG')
AND FLVT3.LOOKUP_TYPE(+) = 'AHL_VWP_VISIT_PRIORITY'
AND FLVT3.LOOKUP_CODE(+) = AVTS.PRIORITY_CODE
AND FLVT3.LANGUAGE(+) = userenv('LANG')
AND PA.PROJECT_ID = AVTS.PROJECT_TEMPLATE_ID
AND AVTS.SERVICE_REQUEST_ID = CSAB.INCIDENT_ID(+)
AND AVTS.TEMPLATE_FLAG = 'N'
AND AVTS.STATUS_CODE <> 'DELETED'
AND UC.active_end_date is null
AND AVTS.VISIT_ID = x_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT WORKORDER_NAME, WORKORDER_ID FROM AHL_WORKORDERS
WHERE MASTER_WORKORDER_FLAG = 'Y' AND VISIT_ID = x_id
/*B6512777 - sowsubra - there is no task associated with visit master wo, hence included the check below to get the visit master wo name*/
AND VISIT_TASK_ID IS NULL;
SELECT name FROM PA_PROJECTS_ALL
WHERE project_id = p_proj_temp_id;
SELECT name, UNIT_CONFIG_HEADER_ID FROM ahl_unit_config_headers
WHERE CSI_ITEM_INSTANCE_ID = x_instance_id
AND active_end_date is null;
SELECT SUBINVENTORY_CODE, CONCATENATED_SEGMENTS
FROM mtl_item_locations_kfv
WHERE inventory_location_id = p_inv_locator_id;
SELECT MEANING INTO l_project_flag
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_CODE = visit_rec.project_flag
AND LOOKUP_TYPE = 'AHL_YES_NO_TYPE';
SELECT SIMULATION_PLAN_NAME INTO l_simulation_plan_name
FROM AHL_SIMULATION_PLANS_VL
WHERE SIMULATION_PLAN_ID = visit_rec.simulation_plan_id;
l_visit_rec.last_update_date := visit_rec.last_update_date;
SELECT Ahl_Visits_B_S.NEXTVAL
FROM dual;
SELECT 1 FROM Ahl_Visits_VL
WHERE Visit_id = x_id;
SELECT MAX(visit_number) FROM Ahl_Visits_B;
/* SELECT uc.name ,
csis.serial_number ,
csis.instance_id,
mtl.inventory_item_id ,
mtl.inventory_org_id
FROM ahl_mtl_items_ou_v mtl,
ahl_unit_config_headers uc,
csi_item_instances csis
WHERE uc.name = p_unit_name
AND uc.csi_item_instance_id=csis.instance_id
AND AHL_UTIL_UC_PKG.GET_UC_STATUS_CODE(uc.UNIT_CONFIG_HEADER_ID) in ('COMPLETE','INCOMPLETE','DEACTIVATE_QUARANTINE','QUARANTINE')
AND (uc.active_end_date IS NULL OR uc.active_end_date > SYSDATE)
AND csis.inventory_item_id = mtl.inventory_item_id
AND csis.inv_master_organization_id = mtl.inventory_org_id
AND csis.serial_number IS NOT NULL
AND csis.ACTIVE_START_DATE <= sysdate AND (csis.ACTIVE_END_DATE >= sysdate OR csis.ACTIVE_END_DATE IS NULL); */
/* SELECT uc.name ,
csis.serial_number ,
csis.instance_id,
mtl.inventory_item_id ,
--mtl.inventory_org_id
csis.inv_master_organization_id
FROM mtl_system_items_kfv mtl,
ahl_unit_config_headers uc,
csi_item_instances csis
WHERE uc.name = p_unit_name
AND uc.csi_item_instance_id=csis.instance_id
AND upper(AHL_UTIL_UC_PKG.GET_UC_STATUS_CODE(uc.UNIT_CONFIG_HEADER_ID))in ('COMPLETE','INCOMPLETE','DEACTIVATE_QUARANTINE','QUARANTINE')
AND (uc.active_end_date IS NULL OR uc.active_end_date > SYSDATE)
AND csis.inventory_item_id = mtl.inventory_item_id
AND csis.inv_master_organization_id = mtl.organization_id
AND csis.serial_number IS NOT NULL
AND csis.ACTIVE_START_DATE <= sysdate AND (csis.ACTIVE_END_DATE >= sysdate OR csis.ACTIVE_END_DATE
IS NULL)
AND csis.inv_master_organization_id IN ( SELECT mp.master_organization_id FROM org_organization_definitions org
, mtl_parameters mp WHERE org.organization_id = mp.organization_id
AND NVL(operating_unit, mo_global.get_current_org_id())
= mo_global.get_current_org_id()); */
SELECT uc.name ,
csis.serial_number ,
csis.instance_id,
mtl.inventory_item_id ,
csis.inv_master_organization_id
FROM mtl_system_items mtl,
ahl_unit_config_headers uc,
csi_item_instances csis
WHERE uc.name = p_unit_name
AND uc.csi_item_instance_id=csis.instance_id
-- Fix by jaramana on June 27, 2006 for Bug 5360066
-- AND upper(AHL_UTIL_UC_PKG.GET_UC_STATUS(uc.UNIT_CONFIG_HEADER_ID))in ('COMPLETE','INCOMPLETE','DEACTIVATE_QUARANTINE','QUARANTINE')
AND AHL_UTIL_UC_PKG.GET_UC_STATUS_CODE(uc.UNIT_CONFIG_HEADER_ID)in ('COMPLETE','INCOMPLETE','DEACTIVATE_QUARANTINE','QUARANTINE')
AND (uc.active_end_date IS NULL OR uc.active_end_date > SYSDATE)
AND csis.inventory_item_id = mtl.inventory_item_id
AND csis.inv_master_organization_id = mtl.organization_id
AND csis.serial_number IS NOT NULL
AND csis.ACTIVE_START_DATE <= sysdate
AND (csis.ACTIVE_END_DATE >= sysdate OR csis.ACTIVE_END_DATE IS NULL)
AND csis.inv_master_organization_id IN
( SELECT mp.master_organization_id
FROM inv_organization_info_v org, mtl_parameters mp
WHERE org.organization_id = mp.organization_id
AND NVL(operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id()
);
SELECT status_id
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinv_code
AND organization_id = p_org_id;
SELECT mil.inventory_location_id
from mtl_item_locations mil, mtl_item_locations_kfv milk
where mil.organization_id = p_org_id
and mil.subinventory_code = p_subinv_code
and milk.concatenated_segments = p_loc_seg
and mil.segment19 is NULL
and mil.segment20 is NULL
and mil.inventory_location_id = milk.inventory_location_id;
SELECT SIMULATION_PLAN_ID INTO l_simulation_plan_id
FROM AHL_SIMULATION_PLANS_VL WHERE primary_plan_flag = 'Y';
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Ahl_Visits_Pkg.Insert_Row');
Ahl_Visits_Pkg.Insert_Row (
X_ROWID => l_rowid,
X_VISIT_ID => l_Visit_rec.VISIT_ID,
X_VISIT_NUMBER => l_visit_number,
X_VISIT_TYPE_CODE => l_Visit_rec.VISIT_TYPE_CODE,
X_SIMULATION_PLAN_ID => l_Visit_rec.SIMULATION_PLAN_ID,
X_ITEM_INSTANCE_ID => l_Visit_rec.ITEM_INSTANCE_ID,
X_INVENTORY_ITEM_ID => l_Visit_rec.INVENTORY_ITEM_ID,
X_ITEM_ORGANIZATION_ID => l_Visit_rec.ITEM_ORGANIZATION_ID,
X_ASSO_PRIMARY_VISIT_ID => l_Visit_rec.ASSO_PRIMARY_VISIT_ID,
X_SIMULATION_DELETE_FLAG => 'N',
X_TEMPLATE_FLAG => 'N',
X_OUT_OF_SYNC_FLAG => l_Visit_rec.OUT_OF_SYNC_FLAG,
X_PROJECT_FLAG => 'Y',
X_PROJECT_ID => l_Visit_rec.PROJECT_ID,
X_SERVICE_REQUEST_ID => l_Visit_rec.SERVICE_REQUEST_ID,
X_SPACE_CATEGORY_CODE => l_Visit_rec.SPACE_CATEGORY_CODE,
X_SCHEDULE_DESIGNATOR => NULL,
X_ATTRIBUTE_CATEGORY => l_Visit_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => l_Visit_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => l_Visit_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => l_Visit_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => l_Visit_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => l_Visit_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => l_Visit_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => l_Visit_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => l_Visit_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => l_Visit_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => l_Visit_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => l_Visit_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => l_Visit_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => l_Visit_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => l_Visit_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => l_Visit_rec.ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => 1,
X_ORGANIZATION_ID => l_Visit_rec.ORGANIZATION_ID,
X_DEPARTMENT_ID => l_Visit_rec.DEPARTMENT_ID,
X_STATUS_CODE => l_Visit_rec.STATUS_CODE,
X_START_DATE_TIME => l_visit_rec.START_DATE,
X_CLOSE_DATE_TIME => l_visit_rec.PLAN_END_DATE,
X_PRICE_LIST_ID => NULL,
X_ESTIMATED_PRICE => NULL,
X_ACTUAL_PRICE => NULL,
X_OUTSIDE_PARTY_FLAG => 'N',
X_ANY_TASK_CHG_FLAG => 'N',
X_VISIT_NAME => l_Visit_rec.VISIT_NAME,
X_DESCRIPTION => l_Visit_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,
X_PRIORITY_CODE => l_visit_rec.priority_code,
X_PROJECT_TEMPLATE_ID => l_visit_rec.proj_template_id,
X_UNIT_SCHEDULE_ID => l_Visit_rec.unit_schedule_id,
X_INV_LOCATOR_ID => l_Visit_rec.inv_locator_id /*Added by sowsubra*/
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Ahl_Visits_Pkg.Insert_Row');
UPDATE ahl_visits_b
SET close_date_time = l_date_time
WHERE visit_id = l_Visit_rec.visit_id ;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_visit_id;
SELECT * FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_visit_id AND NVL(STATUS_CODE,'X') <> 'DELETED';
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
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;
IF UPPER(c_visit_rec.status_code) = 'DELETED' THEN
Fnd_Message.SET_NAME('AHL','AHL_VWP_INVALID_STATUS');
Ahl_Visits_Pkg.Insert_Row (
X_ROWID => l_rowid,
X_VISIT_ID => l_Visit_ID,
X_VISIT_NUMBER => l_visit_number,
X_VISIT_TYPE_CODE => c_Visit_rec.VISIT_TYPE_CODE,
X_SIMULATION_PLAN_ID => c_Visit_rec.simulation_plan_id,
X_ITEM_INSTANCE_ID => c_Visit_rec.item_instance_id,
X_INVENTORY_ITEM_ID => c_visit_rec.INVENTORY_ITEM_ID,
X_ITEM_ORGANIZATION_ID => c_Visit_rec.ITEM_ORGANIZATION_ID,
X_ASSO_PRIMARY_VISIT_ID => c_Visit_rec.asso_primary_visit_id,
X_SIMULATION_DELETE_FLAG=> 'N',
X_TEMPLATE_FLAG => c_Visit_rec.TEMPLATE_FLAG,
X_OUT_OF_SYNC_FLAG => NULL,
X_PROJECT_FLAG => c_Visit_rec.PROJECT_FLAG,
X_PROJECT_ID => NULL,
X_SERVICE_REQUEST_ID => c_Visit_rec.SERVICE_REQUEST_ID,
X_SCHEDULE_DESIGNATOR => c_Visit_rec.SCHEDULE_DESIGNATOR,
X_SPACE_CATEGORY_CODE => c_Visit_rec.SPACE_CATEGORY_CODE,
X_ATTRIBUTE_CATEGORY => c_visit_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_visit_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_visit_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_Visit_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_Visit_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_Visit_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_Visit_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_Visit_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_Visit_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_Visit_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_Visit_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_Visit_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_Visit_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_Visit_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_Visit_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_Visit_rec.ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => 1,
X_ORGANIZATION_ID => l_Visit_rec.ORGANIZATION_ID,
X_DEPARTMENT_ID => l_Visit_rec.DEPARTMENT_ID,
X_STATUS_CODE => 'PLANNING',
X_START_DATE_TIME => l_Visit_rec.START_DATE,
X_CLOSE_DATE_TIME => l_Visit_rec.PLAN_END_DATE,
X_PRICE_LIST_ID => c_Visit_rec.PRICE_LIST_ID,
X_ESTIMATED_PRICE => l_estimate_price,
X_ACTUAL_PRICE => l_actual_price,
X_OUTSIDE_PARTY_FLAG => c_Visit_rec.OUTSIDE_PARTY_FLAG,
X_ANY_TASK_CHG_FLAG => 'N',
X_VISIT_NAME => c_Visit_rec.VISIT_NAME,
X_DESCRIPTION => c_Visit_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,
X_PRIORITY_CODE => c_Visit_rec.PRIORITY_CODE,
X_PROJECT_TEMPLATE_ID => c_Visit_rec.PROJECT_TEMPLATE_ID,
X_UNIT_SCHEDULE_ID => c_Visit_rec.UNIT_SCHEDULE_ID,
X_INV_LOCATOR_ID => l_Visit_rec.inv_locator_id --Added by sowsubra
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before AHL_VISIT_TASKS_PKG.INSERT_ROW');
AHL_VISIT_TASKS_PKG.INSERT_ROW
( X_ROWID => l_rowid,
X_VISIT_TASK_ID => c_task_rec.visit_task_id,
X_VISIT_TASK_NUMBER => c_task_rec.visit_task_number,
X_OBJECT_VERSION_NUMBER => 1,
X_VISIT_ID => l_visit_id,
X_PROJECT_TASK_ID => NULL,
X_COST_PARENT_ID => c_task_rec.cost_parent_id,
X_MR_ROUTE_ID => c_task_rec.MR_ID,
X_MR_ID => c_task_rec.MR_ROUTE_ID,
X_DURATION => c_task_rec.duration,
X_UNIT_EFFECTIVITY_ID => c_task_rec.UNIT_EFFECTIVITY_ID,
X_START_FROM_HOUR => c_task_rec.start_from_hour,
X_INVENTORY_ITEM_ID => c_task_rec.inventory_item_id,
X_ITEM_ORGANIZATION_ID => c_task_rec.item_organization_id,
X_INSTANCE_ID => c_task_rec.instance_id,
X_PRIMARY_VISIT_TASK_ID => c_task_rec.primary_visit_task_id,
X_ORIGINATING_TASK_ID => c_task_rec.originating_task_id,
X_SERVICE_REQUEST_ID => c_task_rec.service_request_id,
X_TASK_TYPE_CODE => c_task_rec.task_type_code,
X_DEPARTMENT_ID => l_task_department_id,
X_SUMMARY_TASK_FLAG => c_task_rec.SUMMARY_TASK_FLAG,
X_PRICE_LIST_ID => c_task_rec.PRICE_LIST_ID,
X_STATUS_CODE => 'PLANNING',
X_ESTIMATED_PRICE => l_estimate_price,
X_ACTUAL_PRICE => l_actual_price,
X_ACTUAL_COST => c_task_rec.ACTUAL_COST,
X_STAGE_ID => c_task_rec.STAGE_ID,
X_START_DATE_TIME => NULL,
X_END_DATE_TIME => NULL,
X_ATTRIBUTE_CATEGORY => c_task_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_task_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_task_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_task_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_task_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_task_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_task_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_task_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_task_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_task_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_task_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_task_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_task_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_task_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_task_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_task_rec.ATTRIBUTE15,
X_VISIT_TASK_NAME => c_task_rec.visit_task_name,
X_DESCRIPTION => c_task_rec.description,
X_QUANTITY => c_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 );
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VISIT_TASKS_PKG.INSERT_ROW ');
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before inserting into AHL_TASK_LINKS');
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
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After inserting into AHL_TASK_LINKS');
PROCEDURE Update_Visit (
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_x_visit_rec IN OUT NOCOPY Visit_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Define local Variables
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Visit';
SELECT * FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
SELECT * FROM Ahl_Visit_Tasks_VL
WHERE VISIT_ID = x_id
and NVL(status_code, 'X') not in('DELETED', 'CANCELLED');
SELECT count(*) FROM AHL_WORKORDERS
WHERE VISIT_ID = x_id
AND MASTER_WORKORDER_FLAG = 'Y'
AND STATUS_CODE = 17;
SELECT * FROM AHL_WORKORDERS
WHERE VISIT_ID = x_id
AND MASTER_WORKORDER_FLAG = 'Y'
AND STATUS_CODE = 17;
SELECT * FROM AHL_WORKORDERS
WHERE VISIT_TASK_ID = x_task_id
AND STATUS_CODE = 17;
SELECT status_id
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinv_code
AND organization_id = p_org_id;
SELECT mil.inventory_location_id
from mtl_item_locations mil, mtl_item_locations_kfv milk
where mil.organization_id = p_org_id
and mil.subinventory_code = p_subinv_code
and milk.concatenated_segments = p_loc_seg
and mil.segment19 is NULL
and mil.segment20 is NULL
and mil.inventory_location_id = milk.inventory_location_id;
SAVEPOINT Update_Visit;
p_validation_mode => Jtf_Plsql_Api.g_update,
x_return_status => l_return_status
);
UPDATE AHL_VISIT_TASKS_B
SET DEPARTMENT_ID = NULL,
OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
WHERE VISIT_TASK_ID = c_task_rec.visit_task_id
AND DEPARTMENT_ID IS NOT NULL;
fnd_log.string(fnd_log.level_statement,L_DEBUG,'VISIT - Before calling AHL_PRD_WORKORDER_PVT.update_job API');
AHL_PRD_WORKORDER_PVT.update_job
( p_api_version =>1.0,
p_init_msg_list =>fnd_api.g_false,
p_commit =>fnd_api.g_false,
p_validation_level =>p_validation_level,
p_default =>fnd_api.g_false,
p_module_type =>'API',
x_return_status =>l_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data,
p_wip_load_flag =>'Y',
p_x_prd_workorder_rec =>l_workorder_rec,
P_X_PRD_WORKOPER_TBL =>l_workoper_tbl
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'VISIT - After calling AHL_PRD_WORKORDER_PVT.update_job API - l_return_status : '||l_return_status);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'TASK - To update task start and end date time');
fnd_log.string(fnd_log.level_statement,L_DEBUG,'TASK - Before calling AHL_PRD_WORKORDER_PVT.update_job');
AHL_PRD_WORKORDER_PVT.update_job
( p_api_version =>1.0,
p_init_msg_list =>fnd_api.g_false,
p_commit =>fnd_api.g_false,
p_validation_level =>p_validation_level,
p_default =>fnd_api.g_false,
p_module_type =>'API',
x_return_status =>l_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data,
p_wip_load_flag =>'Y',
p_x_prd_workorder_rec =>l_workorder_rec,
P_X_PRD_WORKOPER_TBL =>l_workoper_tbl
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'TASK - After calling AHL_PRD_WORKORDER_PVT.update_job - l_return_status : '||l_return_status);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Ahl_Visits_Pkg.UPDATE_ROW');
Ahl_Visits_Pkg.UPDATE_ROW (
X_VISIT_ID => l_Visit_rec.VISIT_ID,
X_VISIT_NUMBER => c_visit_rec.VISIT_NUMBER,
X_VISIT_TYPE_CODE => l_Visit_rec.VISIT_TYPE_CODE,
X_SIMULATION_PLAN_ID => c_Visit_rec.SIMULATION_PLAN_ID,
X_ITEM_INSTANCE_ID => c_Visit_rec.ITEM_INSTANCE_ID,
X_ITEM_ORGANIZATION_ID => c_Visit_rec.ITEM_ORGANIZATION_ID,
X_INVENTORY_ITEM_ID => c_Visit_rec.INVENTORY_ITEM_ID,
X_ASSO_PRIMARY_VISIT_ID => c_Visit_rec.ASSO_PRIMARY_VISIT_ID,
X_SIMULATION_DELETE_FLAG => c_Visit_rec.SIMULATION_DELETE_FLAG,
X_TEMPLATE_FLAG => c_Visit_rec.TEMPLATE_FLAG,
X_OUT_OF_SYNC_FLAG => c_Visit_rec.OUT_OF_SYNC_FLAG,
X_PROJECT_FLAG => 'Y',
X_PROJECT_ID => c_Visit_rec.PROJECT_ID,
X_SERVICE_REQUEST_ID => l_Visit_rec.SERVICE_REQUEST_ID,
X_SPACE_CATEGORY_CODE => l_Visit_rec.SPACE_CATEGORY_CODE,
X_SCHEDULE_DESIGNATOR => c_Visit_rec.SCHEDULE_DESIGNATOR,
X_ATTRIBUTE_CATEGORY => c_Visit_rec.ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1 => c_Visit_rec.ATTRIBUTE1,
X_ATTRIBUTE2 => c_Visit_rec.ATTRIBUTE2,
X_ATTRIBUTE3 => c_Visit_rec.ATTRIBUTE3,
X_ATTRIBUTE4 => c_Visit_rec.ATTRIBUTE4,
X_ATTRIBUTE5 => c_Visit_rec.ATTRIBUTE5,
X_ATTRIBUTE6 => c_Visit_rec.ATTRIBUTE6,
X_ATTRIBUTE7 => c_Visit_rec.ATTRIBUTE7,
X_ATTRIBUTE8 => c_Visit_rec.ATTRIBUTE8,
X_ATTRIBUTE9 => c_Visit_rec.ATTRIBUTE9,
X_ATTRIBUTE10 => c_Visit_rec.ATTRIBUTE10,
X_ATTRIBUTE11 => c_Visit_rec.ATTRIBUTE11,
X_ATTRIBUTE12 => c_Visit_rec.ATTRIBUTE12,
X_ATTRIBUTE13 => c_Visit_rec.ATTRIBUTE13,
X_ATTRIBUTE14 => c_Visit_rec.ATTRIBUTE14,
X_ATTRIBUTE15 => c_Visit_rec.ATTRIBUTE15,
X_OBJECT_VERSION_NUMBER => l_Visit_rec.OBJECT_VERSION_NUMBER + 1,
X_ORGANIZATION_ID => l_Visit_rec.ORGANIZATION_ID,
X_DEPARTMENT_ID => l_Visit_rec.DEPARTMENT_ID,
X_STATUS_CODE => l_Visit_rec.STATUS_CODE,
X_START_DATE_TIME => l_date_time_start,
X_CLOSE_DATE_TIME => l_date_time_end,
X_PRICE_LIST_ID => c_Visit_rec.PRICE_LIST_ID,
X_ESTIMATED_PRICE => c_Visit_rec.ESTIMATED_PRICE,
X_ACTUAL_PRICE => c_Visit_rec.ACTUAL_PRICE,
X_OUTSIDE_PARTY_FLAG => c_Visit_rec.OUTSIDE_PARTY_FLAG,
X_ANY_TASK_CHG_FLAG => c_Visit_rec.ANY_TASK_CHG_FLAG,
X_VISIT_NAME => l_Visit_rec.VISIT_NAME,
X_DESCRIPTION => l_Visit_rec.DESCRIPTION,
X_PRIORITY_CODE => l_visit_rec.PRIORITY_CODE,
X_PROJECT_TEMPLATE_ID => l_visit_rec.PROJ_TEMPLATE_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
X_UNIT_SCHEDULE_ID => c_Visit_rec.unit_schedule_id,
X_INV_LOCATOR_ID => l_visit_rec.INV_LOCATOR_ID --Added by sowsubra
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Ahl_Visits_Pkg.UPDATE_ROW');
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_SPACE_ASSIGN_PVT.Delete_Space_assignment');
AHL_LTP_SPACE_ASSIGN_PVT.Delete_Space_assignment
( p_api_version => 1.0,
p_init_msg_list => FND_API.g_false,
p_commit => FND_API.g_false,
p_validation_level => FND_API.g_valid_level_full,
p_space_assign_rec => l_Space_Assignment_Rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_SPACE_ASSIGN_PVT.Delete_Space_assignment - l_return_status : '||l_return_status);
UPDATE AHL_VISITS_B
SET ACTUAL_PRICE = NULL,
ESTIMATED_PRICE = NULL,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE VISIT_ID = l_Visit_rec.visit_id;
UPDATE AHL_VISIT_TASKS_B
SET ACTUAL_PRICE = NULL,
ESTIMATED_PRICE = NULL,
OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
/*AHL_DEBUG_PUB.Debug( l_full_name ||': VISIT UPDATED - Before calling AHL_LTP_REQST_MATRL_PVT.Process_Planned_Materials');
AHL_VWP_RULES_PVT.Update_Visit_Task_Flag
(
p_visit_id => l_Visit_rec.visit_id,
p_flag => 'Y',
x_return_status => l_return_status);
ROLLBACK TO Update_Visit;
ROLLBACK TO Update_Visit;
ROLLBACK TO Update_Visit;
END Update_Visit;
PROCEDURE Delete_Task (
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 := Null,
p_Visit_Task_Id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Task';
'At the start of Delete_Visit -> Delete Task'
);
UPDATE
AHL_VISIT_TASKS_B
SET
ORIGINATING_TASK_ID=NULL,
OBJECT_VERSION_NUMBER = object_version_number + 1
WHERE
ORIGINATING_TASK_ID = p_Visit_Task_Id and NVL(STATUS_CODE,'X') <> 'DELETED';
UPDATE
AHL_VISIT_TASKS_B
SET
COST_PARENT_ID = NULL,
OBJECT_VERSION_NUMBER = object_version_number + 1
WHERE
COST_PARENT_ID = p_Visit_Task_Id AND NVL(STATUS_CODE,'X') <> 'DELETED';
UPDATE
AHL_VISIT_TASKS_B
SET
PRIMARY_VISIT_TASK_ID = NULL,
OBJECT_VERSION_NUMBER = object_version_number + 1
WHERE
PRIMARY_VISIT_TASK_ID = p_Visit_Task_Id AND NVL(STATUS_CODE,'X') <> 'DELETED';
DELETE
AHL_TASK_LINKS
WHERE
VISIT_TASK_ID = p_Visit_Task_Id
OR
PARENT_TASK_ID = p_Visit_Task_Id;
AHL_VWP_PROJ_PROD_PVT.Delete_Task_To_project(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_task_id => p_Visit_Task_Id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'Failed to delete project task association for task '||p_visit_task_id
);
'At the end of Delete_Visit -> Delete Task'
);
END Delete_Task;*/
PROCEDURE Delete_Visit (
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
-- Define local Variables
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Visit';
l_soft_delete_flag VARCHAR2(1) := 'N';
SELECT * FROM Ahl_Visits_VL
WHERE VISIT_ID = x_id;
/* SELECT 'X' FROM DUAL
WHERE exists (select 'X' from AHL_SCHEDULE_MATERIALS_V where VISIT_ID = x_visit_id);*/
SELECT 'x' FROM ahl_schedule_materials
WHERE status <> 'DELETED'
AND visit_id = x_visit_id;
SELECT workorder_id, object_version_number, status_code
FROM AHL_WORKORDERS
WHERE VISIT_ID = x_visit_id
AND MASTER_WORKORDER_FLAG = 'Y'
AND VISIT_TASK_ID IS NULL;
SELECT workorder_id, object_version_number, status_code
FROM AHL_WORKORDERS
WHERE VISIT_ID = x_visit_id
AND MASTER_WORKORDER_FLAG = 'Y'
AND VISIT_TASK_ID IS NULL
AND STATUS_CODE not in ('22','7'); -- deleted, cancelled
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));
SAVEPOINT Delete_Visit;
Fnd_Message.SET_NAME('AHL','AHL_VWP_STATUS_NOT_DELETE');
l_soft_delete_flag := 'Y';
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits');
AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits
(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => p_validation_level,
p_visit_id => p_visit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_LTP_SIMUL_PLAN_PVT.delete_simul_visits - l_return_status : '||l_return_status);
fnd_log.string (fnd_log.level_statement,L_DEBUG,'Errors from delete_simul_visits: ' || x_msg_count );
fnd_log.string (fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_TASKS_PVT.Delete_Task - visit_task_id : '||l_tasks_rec.visit_task_id);
l_soft_delete_flag := 'Y'; -- If tasks are set only soft delete needs to be done.
AHL_VWP_TASKS_PVT.Delete_Task
( p_api_version => p_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => p_validation_level,
p_module_type => NULL,
p_visit_task_id => l_tasks_rec.visit_task_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
fnd_log.string (fnd_log.level_statement,L_DEBUG,'After calling Delete_Task - l_return_status : '||l_return_status);
l_soft_delete_flag := 'Y';
l_prd_workorder_rec.STATUS_CODE:='22'; --Deleted Status Refer DLD to Verify.
fnd_log.string (fnd_log.level_statement,L_DEBUG,'before calling AHL_PRD_WORKORDER_PVT.update_job - Workorder Id : '||l_prd_workorder_rec.workorder_id);
AHL_PRD_WORKORDER_PVT.update_job
( p_api_version =>1.0,
p_init_msg_list =>fnd_api.g_false,
p_commit =>fnd_api.g_false,
p_validation_level =>p_validation_level,
p_default =>fnd_api.g_false,
p_module_type =>NULL,
x_return_status =>l_return_status,
x_msg_count =>x_msg_count,
x_msg_data =>x_msg_data,
p_wip_load_flag =>'Y',
p_x_prd_workorder_rec =>l_prd_workorder_rec,
p_x_prd_workoper_tbl =>l_workorder_tbl
);
fnd_log.string (fnd_log.level_statement,L_DEBUG,'after calling AHL_PRD_WORKORDER_PVT.update_job - l_return_status : '||l_return_status);
IF l_soft_delete_flag = 'Y' THEN
-- Update the project status to 'Rejected'
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string (fnd_log.level_statement,L_DEBUG,'before calling AHL_VWP_PROJ_PROD_PVT.Update_project');
AHL_VWP_PROJ_PROD_PVT.Update_project
( p_api_version => p_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => p_validation_level,
p_module_type => 'DEL',
p_visit_id => p_visit_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
fnd_log.string (fnd_log.level_statement,L_DEBUG,'after calling AHL_VWP_PROJ_PROD_PVT.Update_project - l_return_status : '||l_return_status);
Fnd_Message.SET_NAME('AHL','AHL_VWP_PRJ_UPDATE_FAILED'); -- Failed to update job
fnd_log.string (fnd_log.level_statement,L_DEBUG,'before calling AHL_VWP_PROJ_PROD_PVT.Delete_Project ');
AHL_VWP_PROJ_PROD_PVT.Delete_Project
( p_api_version => p_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_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 => x_msg_count,
x_msg_data => x_msg_data);
fnd_log.string (fnd_log.level_statement,L_DEBUG,'after calling AHL_VWP_PROJ_PROD_PVT.Delete_Project - l_return_status : '||l_return_status);
END IF; -- soft delete flag
IF l_soft_delete_flag = 'N' THEN
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
fnd_log.string (fnd_log.level_statement,L_DEBUG,'before calling AHL_VWP_VISITS_STAGES_PVT.DELETE_STAGES ');
AHL_VWP_VISITS_STAGES_PVT.DELETE_STAGES
( p_api_version => p_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => p_validation_level,
p_module_type => NULL,
p_visit_id => l_visit_rec.visit_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
fnd_log.string (fnd_log.level_statement,L_DEBUG,'after calling AHL_VWP_VISITS_STAGES_PVT.DELETE_STAGES - l_return_status : '||l_return_status );
DELETE FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id;
AHL_VISITS_PKG.Delete_Row( x_visit_id => p_visit_id);
UPDATE AHL_VISITS_B
SET STATUS_CODE = 'DELETED',
SIMULATION_PLAN_ID = NULL,
OBJECT_VERSION_NUMBER =OBJECT_VERSION_NUMBER + 1
WHERE VISIT_ID = l_visit_rec.visit_id;
END IF; -- soft delete flag
ROLLBACK TO Delete_Visit;
ROLLBACK TO Delete_Visit;
ROLLBACK TO Delete_Visit;
END Delete_Visit;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_id;
SELECT * FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_id
AND NVL(STATUS_CODE,'X') <> 'DELETED';
SELECT A.VISIT_TASK_ID
FROM AHL_VISIT_TASKS_VL A, AHL_WORKORDERS B
WHERE A.VISIT_TASK_ID = B.VISIT_TASK_ID
AND A.VISIT_ID = p_visit_id
AND NVL(A.STATUS_CODE,'X') <> 'DELETED'
-- Balaji added statuses complete_no_charge and deleted to the list of statuses to be
-- checked for.
-- COMPELTE_NO_CHARGE - 5, DELETED - 22
--('4','7','12','18')
AND B.STATUS_CODE NOT IN ('4','5','7','12','18','22')
AND A.TASK_TYPE_CODE <> 'SUMMARY';
SELECT UE.unit_effectivity_id
FROM ahl_unit_effectivities_b UE, ahl_visit_tasks_b VT
WHERE UE.unit_effectivity_id = VT.unit_effectivity_id
AND nvl(UE.status_code,'x') NOT IN
('ACCOMPLISHED','DEFERRED','TERMINATED','CANCELLED')
AND (VT.task_type_code = 'UNPLANNED' OR VT.task_type_code = 'PLANNED')
AND VT.visit_id = p_visit_id
AND NVL(VT.STATUS_CODE,'X') <> 'DELETED';
SELECT A.visit_task_id,workorder_id,wip_entity_id,a.object_version_number
FROM ahl_visit_tasks_vl a, ahl_workorders b
WHERE a.visit_task_id = b.visit_task_id
AND a.visit_id = C_VISIT_ID
AND NVL(A.STATUS_CODE,'X') <> 'DELETED'
AND B.STATUS_CODE <> '7';
SELECT visit_task_id,object_version_number
FROM ahl_visit_tasks_vl
WHERE visit_id = c_visit_id
AND mr_id IS NULL
AND task_type_code = 'SUMMARY'
AND NVL(STATUS_CODE,'X') <> 'DELETED';
UPDATE ahl_visit_tasks_b
SET actual_cost = l_actual_cost,
object_version_number = l_wip_entity_rec.object_version_number + 1
WHERE visit_task_id = l_wip_entity_rec.visit_task_id;
UPDATE ahl_visit_tasks_b
SET actual_cost = l_actual_cost,
object_version_number = l_summ_task_rec.object_version_number + 1
WHERE visit_task_id = l_summ_task_rec.visit_task_id;
/* Call Update_Project procedure to update project status to CLOSED
AHL_VWP_PROJ_PROD_PVT.Update_Project (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_id => p_visit_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);*/
UPDATE AHL_VISITS_B
SET STATUS_CODE = 'CLOSED',
SIMULATION_PLAN_ID = NULL,
OBJECT_VERSION_NUMBER = c_visit_rec.object_version_number + 1
WHERE VISIT_ID = p_visit_id;
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'CLOSED',
OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
SELECT * FROM AHL_VISITS_VL
WHERE VISIT_ID = x_visit_id;
SELECT * FROM AHL_VISIT_TASKS_VL
WHERE VISIT_ID = x_visit_id
AND NVL(STATUS_CODE,'X') <> 'DELETED';
SELECT distinct service_request_id FROM AHL_VISIT_TASKS_B
WHERE VISIT_ID = x_visit_id
AND service_request_id IS NOT NULL
AND NVL(STATUS_CODE,'X') <> 'DELETED';
SELECT 'X' FROM DUAL
WHERE exists ( select a.visit_id
from ahl_visits_b a, ahl_visit_tasks_b b
where a.visit_id <> x_visit_id
and a.visit_id = b.visit_id
and b.visit_id <> x_visit_id
AND NVL(a.STATUS_CODE,'X') not in ('DELETED','CLOSED')
AND b.service_request_id = x_sr_id);
SELECT object_version_number, incident_number
FROM cs_incidents_all_b
WHERE INCIDENT_ID = x_sr_id;
UPDATE AHL_VISIT_TASKS_B
SET UNIT_EFFECTIVITY_ID = NULL,
OBJECT_VERSION_NUMBER = object_version_number + 1
WHERE VISIT_ID = p_visit_id
AND UNIT_EFFECTIVITY_ID is not null;
UPDATE AHL_VISIT_TASKS_B
SET STATUS_CODE = 'CANCELLED',
OBJECT_VERSION_NUMBER = c_task_rec.object_version_number + 1
WHERE VISIT_TASK_ID = c_task_rec.visit_task_id;
UPDATE AHL_VISITS_B
SET STATUS_CODE = 'CANCELLED',
OBJECT_VERSION_NUMBER = c_visit_rec.object_version_number + 1
WHERE VISIT_ID = p_visit_id;
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling AHL_VWP_PROJ_PROD_PVT.Update_Project');
AHL_VWP_PROJ_PROD_PVT.Update_Project(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_visit_id => p_visit_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling AHL_VWP_PROJ_PROD_PVT.Update_Project - x_return_status : '||x_return_status);
Fnd_Message.SET_NAME('AHL','AHL_VWP_PRJ_UPDATE_FAILED'); -- Failed to update job
'Cant update the project to Rejected status');
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling CS_ServiceRequest_PUB.Update_Status - SR Id : '||l_service_request_id);
CS_ServiceRequest_PUB.Update_Status
( p_api_version => 2.0,
p_init_msg_list => p_init_msg_list,
p_commit => FND_API.G_FALSE,
p_resp_appl_id => NULL,
p_resp_id => NULL,
p_user_id => NULL,
p_login_id => NULL,
p_status_id => 1,
p_closed_date => NULL,
p_audit_comments => NULL,
p_called_by_workflow => FND_API.G_FALSE,
p_workflow_process_id => NULL,
p_comments => NULL,
p_public_comment_flag => FND_API.G_FALSE,
p_validate_sr_closure => 'N',
p_auto_close_child_entities => 'N',
p_request_id => NULL,
p_request_number => c_sr_ovn_rec.incident_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_object_version_number => c_sr_ovn_rec.object_version_number,
-- p_status => 'OPEN',
x_interaction_id => l_interaction_id
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling CS_ServiceRequest_PUB.Update_Status - l_return_status : '||l_return_status);
SELECT * FROM Ahl_Visits_VL
WHERE VISIT_ID = x_visit_id;
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Update_Visit');
Update_Visit
(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_Visit_rec => p_x_visit_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Update_Visit - l_return_status : '||l_return_status);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Before calling Update_Visit');
Update_Visit
(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => Fnd_Api.g_false,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_Visit_rec => p_x_visit_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
fnd_log.string(fnd_log.level_statement,L_DEBUG,'After calling Update_Visit - l_return_status : '||l_return_status);
PROCEDURE DELETE_FLIGHT_ASSOC(
p_unit_schedule_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
)
IS
BEGIN
SAVEPOINT delete_flight_assoc;
UPDATE AHL_VISITS_B
SET UNIT_SCHEDULE_ID = NULL
WHERE UNIT_SCHEDULE_ID = p_unit_schedule_id ;
ROLLBACK TO delete_flight_assoc;
ROLLBACK TO delete_flight_assoc;
ROLLBACK TO delete_flight_assoc;
END DELETE_FLIGHT_ASSOC;
SELECT visit_task_number,instance_id
FROM ahl_visit_tasks_vl
WHERE visit_id = x_visit_id
AND NVL(STATUS_CODE,'X') NOT IN ('DELETED','RELEASED')
AND TASK_TYPE_CODE <> 'SUMMARY';