[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_LAST_UPDATE_DATE DATE := SYSDATE;
G_LAST_UPDATED_BY NUMBER(15) := FND_GLOBAL.user_id;
G_LAST_UPDATE_LOGIN NUMBER(15) := FND_GLOBAL.login_id;
PROCEDURE Delete_Visit_Type (
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_type_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_Visit_Type';
SAVEPOINT Delete_Visit_Type;
fnd_log.string(fnd_log.level_statement,L_DEBUG,'Visit Type id of the record to be deleted '|| p_visit_type_id);
DELETE FROM AHL_VST_TYP_STG_TYP_ASOC
WHERE VISIT_TYPE_STAGE_ID IN (SELECT VISIT_TYPE_STAGE_ID
FROM AHL_VISIT_TYPE_STAGES_B AVTSB
WHERE VISIT_TYPE_ID = p_visit_type_id);
DELETE FROM AHL_VST_TYP_STAGE_LINKS
WHERE OBJECT_ID IN (SELECT VISIT_TYPE_STAGE_ID
FROM AHL_VISIT_TYPE_STAGES_B AVTSB
WHERE VISIT_TYPE_ID = p_visit_type_id)
OR SUBJECT_ID IN(SELECT VISIT_TYPE_STAGE_ID
FROM AHL_VISIT_TYPE_STAGES_B AVTSB
WHERE VISIT_TYPE_ID = p_visit_type_id);
DELETE FROM AHL_VISIT_TYPE_STAGES_TL
WHERE VISIT_TYPE_STAGE_ID IN (SELECT VISIT_TYPE_STAGE_ID
FROM AHL_VISIT_TYPE_STAGES_B AVTSB
WHERE VISIT_TYPE_ID = p_visit_type_id);
DELETE FROM AHL_VISIT_TYPE_STAGES_B
WHERE VISIT_TYPE_ID = p_visit_type_id;
DELETE FROM AHL_VISIT_TYPES_TL
WHERE VISIT_TYPE_ID = p_visit_type_id;
DELETE FROM AHL_VISIT_TYPES_B
WHERE VISIT_TYPE_ID = p_visit_type_id;
ROLLBACK TO Delete_Visit_Type;
AHL_DEBUG_PUB.debug('Delete_Visit_Type: In g_exc_unexpected_error block ' || ' x_msg_count ' || x_msg_count );
ROLLBACK TO Delete_Visit_Type;
AHL_DEBUG_PUB.debug('Delete_Visit_Type: In g_exc_unexpected_error block ' || ' x_msg_count ' || x_msg_count );
ROLLBACK TO Delete_Visit_Type;
AHL_DEBUG_PUB.debug('Delete_Visit_Type: In OTHERS block ' || ' x_msg_count ' || x_msg_count );
END Delete_Visit_Type;
Select COUNT(VISIT_TYPE_STAGE_ID)
FROM AHL_VISIT_TYPE_STAGES_B AVTSB
WHERE VISIT_TYPE_ID = c_visit_type_id;
SELECT AVTSB.STAGE_NUMBER,
COUNT(VISIT_TYP_STAGE_TYP_ASOC_ID) as Count
FROM AHL_VST_TYP_STG_TYP_ASOC AVTSTA,
AHL_VISIT_TYPE_STAGES_B AVTSB
WHERE AVTSB.VISIT_TYPE_STAGE_ID = AVTSTA.VISIT_TYPE_STAGE_ID(+)
AND AVTSB.VISIT_TYPE_ID = c_visit_type_id
group by AVTSB.STAGE_NUMBER;
SELECT AVTV.visit_type_code,
AVTV.visit_type_name,
AMHB.name mc_name,
AVTV.linked_visit_type_id,
AVTV.component_visit_flag
FROM AHL_VISIT_TYPES_VL AVTV,
AHL_MC_HEADERS_B AMHB
WHERE AVTV.visit_type_id = c_visit_type_id
AND AVTV.mc_id = AMHB.mc_header_id (+);
Delete_Visit_Type (
p_api_version => 1.0,
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_type_id => L_linked_vst_typ_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,'Return Status after calling Delete_Visit_Type = '|| l_return_status);
UPDATE AHL_VISIT_TYPES_B
SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = G_LAST_UPDATE_DATE,
LAST_UPDATED_BY = G_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = G_LAST_UPDATE_LOGIN,
STATUS_CODE = 'COMPLETE',
LINKED_VISIT_TYPE_ID = null
WHERE VISIT_TYPE_ID = p_visit_type_id
and object_version_number = p_obj_version_num;
SELECT VISIT_TYPE_STAGE_ID
FROM AHL_VISIT_TYPE_STAGES_B
WHERE VISIT_TYPE_ID = c_visit_type_id;
SELECT AHL_VISIT_TYPE_STAGES_B_S.nextVal
FROM dual;
SELECT AVTV.visit_type_name,
AMHB.name mc_name
FROM AHL_VISIT_TYPES_VL AVTV,
AHL_MC_HEADERS_B AMHB
WHERE AVTV.linked_visit_type_id = c_par_visit_type_id
AND AVTV.mc_id = AMHB.mc_header_id (+);
SELECT component_visit_flag
FROM AHL_VISIT_TYPES_B
WHERE visit_type_id = c_visit_type_id;
INSERT INTO
AHL_VISIT_TYPES_B
(VISIT_TYPE_ID ,
VISIT_TYPE_CODE ,
SERVICE_CATEGORY_CODE,
STATUS_CODE ,
ESTIMATED_DURATION ,
MC_ID ,
LINKED_VISIT_TYPE_ID ,
TRANSIT_TYPE_FLAG ,
--MANESING::Component Maintenance Planning Project, 05-Jul-2011, added component visit flag
COMPONENT_VISIT_FLAG ,
SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15)
SELECT
p_visit_type_id ,
VISIT_TYPE_CODE ,
SERVICE_CATEGORY_CODE,
'DRAFT' ,
ESTIMATED_DURATION ,
MC_ID ,
p_par_visit_type_id ,
TRANSIT_TYPE_FLAG ,
--MANESING::Component Maintenance Planning Project, 05-Jul-2011, select component visit flag also
COMPONENT_VISIT_FLAG ,
SECURITY_GROUP_ID ,
1 ,
G_LAST_UPDATE_DATE ,
G_LAST_UPDATED_BY ,
G_CREATION_DATE ,
G_CREATED_BY ,
G_LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM AHL_VISIT_TYPES_B
WHERE VISIT_TYPE_ID = p_par_visit_type_id;
INSERT INTO
AHL_VISIT_TYPES_TL
(VISIT_TYPE_ID ,
DESCRIPTION ,
LANGUAGE ,
SOURCE_LANG ,
SECURITY_GROUP_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN
)
SELECT
p_visit_type_id ,
DESCRIPTION ,
LANGUAGE ,
SOURCE_LANG ,
SECURITY_GROUP_ID ,
G_LAST_UPDATE_DATE ,
G_LAST_UPDATED_BY ,
G_CREATION_DATE ,
G_CREATED_BY ,
G_LAST_UPDATE_LOGIN
FROM AHL_VISIT_TYPES_TL
WHERE visit_type_id = p_par_visit_type_id;
/* MANESING::Component Maintenance Planning Project, 05-Jul-2011, following insertions to Stage related
* tables are not needed for Component Visit Types as there won't be any Stage associated with them.
*/
OPEN get_visit_type_dtls_csr (p_visit_type_id);
INSERT INTO
AHL_VISIT_TYPE_STAGES_B
(VISIT_TYPE_STAGE_ID ,
VISIT_TYPE_ID ,
DURATION ,
STAGE_NUMBER ,
SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
)
SELECT
L_STAGE_ID ,
p_visit_type_id ,
DURATION ,
STAGE_NUMBER ,
SECURITY_GROUP_ID ,
1 ,
G_LAST_UPDATE_DATE ,
G_LAST_UPDATED_BY ,
G_CREATION_DATE ,
G_CREATED_BY ,
G_LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM AHL_VISIT_TYPE_STAGES_B
WHERE VISIT_TYPE_STAGE_ID = stage_rec.VISIT_TYPE_STAGE_ID;
INSERT INTO
AHL_VISIT_TYPE_STAGES_TL
(VISIT_TYPE_STAGE_ID ,
STAGE_NAME ,
LANGUAGE ,
SOURCE_LANG ,
SECURITY_GROUP_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN
)
SELECT
L_STAGE_ID ,
STAGE_NAME ,
LANGUAGE ,
SOURCE_LANG ,
SECURITY_GROUP_ID ,
G_LAST_UPDATE_DATE ,
G_LAST_UPDATED_BY ,
G_CREATION_DATE ,
G_CREATED_BY ,
G_LAST_UPDATE_LOGIN
FROM AHL_VISIT_TYPE_STAGES_TL
WHERE VISIT_TYPE_STAGE_ID = stage_rec.VISIT_TYPE_STAGE_ID;
INSERT INTO
AHL_VST_TYP_STG_TYP_ASOC
(VISIT_TYP_STAGE_TYP_ASOC_ID ,
VISIT_TYPE_STAGE_ID ,
STAGE_TYPE_CODE ,
SECURITY_GROUP_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
)
SELECT
AHL_VST_TYP_STG_TYP_ASOC_S.nextVal,
L_STAGE_ID ,
STAGE_TYPE_CODE ,
SECURITY_GROUP_ID ,
G_LAST_UPDATE_DATE ,
G_LAST_UPDATED_BY ,
G_CREATION_DATE ,
G_CREATED_BY ,
G_LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
FROM AHL_VST_TYP_STG_TYP_ASOC
WHERE VISIT_TYPE_STAGE_ID = stage_rec.VISIT_TYPE_STAGE_ID;
INSERT INTO
AHL_VST_TYP_STAGE_LINKS
(VISIT_TYPE_STAGE_LINK_ID ,
OBJECT_ID ,
SUBJECT_ID ,
RELATION_TYPE ,
SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15
)
SELECT
AHL_VST_TYP_STAGE_LINKS_S.nextVal ,
STAGES2.VISIT_TYPE_STAGE_ID ,
STAGES3.VISIT_TYPE_STAGE_ID ,
LINKS.RELATION_TYPE ,
LINKS.SECURITY_GROUP_ID ,
1 ,
G_LAST_UPDATE_DATE ,
G_LAST_UPDATED_BY ,
G_CREATION_DATE ,
G_CREATED_BY ,
G_LAST_UPDATE_LOGIN ,
LINKS.ATTRIBUTE_CATEGORY ,
LINKS.ATTRIBUTE1 ,
LINKS.ATTRIBUTE2 ,
LINKS.ATTRIBUTE3 ,
LINKS.ATTRIBUTE4 ,
LINKS.ATTRIBUTE5 ,
LINKS.ATTRIBUTE6 ,
LINKS.ATTRIBUTE7 ,
LINKS.ATTRIBUTE8 ,
LINKS.ATTRIBUTE9 ,
LINKS.ATTRIBUTE10 ,
LINKS.ATTRIBUTE11 ,
LINKS.ATTRIBUTE12 ,
LINKS.ATTRIBUTE13 ,
LINKS.ATTRIBUTE14 ,
LINKS.ATTRIBUTE15
FROM AHL_VST_TYP_STAGE_LINKS LINKS,
AHL_VISIT_TYPE_STAGES_B Stages1,
AHL_VISIT_TYPE_STAGES_B Stages2,
AHL_VISIT_TYPE_STAGES_B Stages3
WHERE LINKS.OBJECT_ID = Stages1.VISIT_TYPE_STAGE_ID
AND Stages1.VISIT_TYPE_ID = p_par_visit_type_id
--Object id and Subject Id to be inserted in the new record have to be of the newly created stages.
--So these ids of the Stages having the same stage number as the old stages are taken
AND Stages2.VISIT_TYPE_ID = p_visit_type_id
AND Stages2.STAGE_NUMBER = (SELECT STAGE_NUMBER
FROM AHL_VISIT_TYPE_STAGES_B
WHERE VISIT_TYPE_STAGE_ID = LINKS.OBJECT_ID)
AND Stages3.VISIT_TYPE_ID = p_visit_type_id
AND Stages3.STAGE_NUMBER = (SELECT STAGE_NUMBER
FROM AHL_VISIT_TYPE_STAGES_B
WHERE VISIT_TYPE_STAGE_ID = LINKS.SUBJECT_ID);
FUNCTION Get_Last_Update_Date (
p_visit_type_id IN NUMBER
)
RETURN DATE
IS
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Last_Update_Date';
L_MAX_UPDATE_DATE DATE;
CURSOR GET_MAX_UPDATE_DATE(C_VISIT_TYPE_ID NUMBER) IS
SELECT MAX(LAST_UPDATE_DATE) MAX_LUD FROM(
SELECT VST_TYP.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM AHL_VISIT_TYPES_B VST_TYP
WHERE VST_TYP.VISIT_TYPE_ID = C_VISIT_TYPE_ID
UNION ALL
SELECT MAX(STAGES.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM AHL_VISIT_TYPE_STAGES_B STAGES, AHL_VISIT_TYPES_B VST_TYP
WHERE STAGES.VISIT_TYPE_ID = VST_TYP.VISIT_TYPE_ID
AND VST_TYP.VISIT_TYPE_ID = C_VISIT_TYPE_ID
UNION ALL
SELECT MAX(LINKS.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM AHL_VST_TYP_STAGE_LINKS LINKS, AHL_VISIT_TYPE_STAGES_B STAGES, AHL_VISIT_TYPES_B VST_TYP
WHERE VST_TYP.VISIT_TYPE_ID = STAGES.VISIT_TYPE_ID
AND STAGES.VISIT_TYPE_STAGE_ID = LINKS.OBJECT_ID
AND VST_TYP.VISIT_TYPE_ID = C_VISIT_TYPE_ID
UNION ALL
SELECT MAX(STG_TYP.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM AHL_VST_TYP_STG_TYP_ASOC STG_TYP, AHL_VISIT_TYPE_STAGES_B STAGES, AHL_VISIT_TYPES_B VST_TYP
WHERE VST_TYP.VISIT_TYPE_ID = STAGES.VISIT_TYPE_ID
AND STAGES.VISIT_TYPE_STAGE_ID = STG_TYP.VISIT_TYPE_STAGE_ID
AND VST_TYP.VISIT_TYPE_ID = C_VISIT_TYPE_ID) QRSLT;
OPEN GET_MAX_UPDATE_DATE(P_VISIT_TYPE_ID);
FETCH GET_MAX_UPDATE_DATE INTO L_MAX_UPDATE_DATE;
CLOSE GET_MAX_UPDATE_DATE;
RETURN L_MAX_UPDATE_DATE;
END Get_Last_Update_Date;