The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
WORK_CLEARANCE_ID
,WORK_CLEARANCE_NAME
,LAST_ISOLATION_ID
,DESCRIPTION
,ORGANIZATION_ID
,PENDING_FLAG
,SCHEDULED_ESTAB_START_DATE
,SCHEDULED_ESTAB_END_DATE
,SCHEDULED_REESTAB_START_DATE
,SCHEDULED_REESTAB_END_DATE
,USER_DEFINED_STATUS_ID
,STATUS_TYPE
,APPROVED_BY
,COMPLETION_DATE
,PERMIT_REQUIRED_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE16
,ATTRIBUTE17
,ATTRIBUTE18
,ATTRIBUTE19
,ATTRIBUTE20
,ATTRIBUTE21
,ATTRIBUTE22
,ATTRIBUTE23
,ATTRIBUTE24
,ATTRIBUTE25
,ATTRIBUTE26
,ATTRIBUTE27
,ATTRIBUTE28
,ATTRIBUTE29
,ATTRIBUTE30
INTO
l_work_clearance_header_rec.WORK_CLEARANCE_ID
,l_work_clearance_header_rec.WORK_CLEARANCE_NAME
,l_work_clearance_header_rec.ISOLATION_ID
,l_work_clearance_header_rec.DESCRIPTION
,l_work_clearance_header_rec.ORGANIZATION_ID
,l_work_clearance_header_rec.PENDING_FLAG
,l_work_clearance_header_rec.SCHEDULED_ESTAB_START_DATE
,l_work_clearance_header_rec.SCHEDULED_ESTAB_END_DATE
,l_work_clearance_header_rec.SCHEDULED_REESTAB_START_DATE
,l_work_clearance_header_rec.SCHEDULED_REESTAB_END_DATE
,l_work_clearance_header_rec.USER_DEFINED_STATUS
,l_work_clearance_header_rec.SYSTEM_STATUS
,l_work_clearance_header_rec.APPROVED_BY
,l_work_clearance_header_rec.COMPLETION_DATE
,l_work_clearance_header_rec.PERMIT_REQUIRED
,l_work_clearance_header_rec.ATTRIBUTE_CATEGORY
,l_work_clearance_header_rec.ATTRIBUTE1
,l_work_clearance_header_rec.ATTRIBUTE2
,l_work_clearance_header_rec.ATTRIBUTE3
,l_work_clearance_header_rec.ATTRIBUTE4
,l_work_clearance_header_rec.ATTRIBUTE5
,l_work_clearance_header_rec.ATTRIBUTE6
,l_work_clearance_header_rec.ATTRIBUTE7
,l_work_clearance_header_rec.ATTRIBUTE8
,l_work_clearance_header_rec.ATTRIBUTE9
,l_work_clearance_header_rec.ATTRIBUTE10
,l_work_clearance_header_rec.ATTRIBUTE11
,l_work_clearance_header_rec.ATTRIBUTE12
,l_work_clearance_header_rec.ATTRIBUTE13
,l_work_clearance_header_rec.ATTRIBUTE14
,l_work_clearance_header_rec.ATTRIBUTE15
,l_work_clearance_header_rec.ATTRIBUTE16
,l_work_clearance_header_rec.ATTRIBUTE17
,l_work_clearance_header_rec.ATTRIBUTE18
,l_work_clearance_header_rec.ATTRIBUTE19
,l_work_clearance_header_rec.ATTRIBUTE20
,l_work_clearance_header_rec.ATTRIBUTE21
,l_work_clearance_header_rec.ATTRIBUTE22
,l_work_clearance_header_rec.ATTRIBUTE23
,l_work_clearance_header_rec.ATTRIBUTE24
,l_work_clearance_header_rec.ATTRIBUTE25
,l_work_clearance_header_rec.ATTRIBUTE26
,l_work_clearance_header_rec.ATTRIBUTE27
,l_work_clearance_header_rec.ATTRIBUTE28
,l_work_clearance_header_rec.ATTRIBUTE28
,l_work_clearance_header_rec.ATTRIBUTE30
FROM EAM_WORK_CLEARANCES ewc
WHERE ewc.WORK_CLEARANCE_ID = p_work_clearance_id
AND ewc.organization_id = p_organization_id;
* Procedure : INSERT_ROW
* Purpose : Procedure will perfrom an insert into the table
*********************************************************************/
PROCEDURE INSERT_ROW
( p_work_clearance_header_rec IN EAM_PROCESS_SAFETY_PUB.eam_clearance_header_rec_type
, x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_Status OUT NOCOPY VARCHAR2
)IS
BEGIN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Writing clearance rec for ' || p_work_clearance_header_rec.WORK_CLEARANCE_NAME); END IF;
INSERT INTO EAM_WORK_CLEARANCES(
WORK_CLEARANCE_ID
,WORK_CLEARANCE_NAME
,LAST_ISOLATION_ID
,DESCRIPTION
,ORGANIZATION_ID
,PENDING_FLAG
,SCHEDULED_ESTAB_START_DATE
,SCHEDULED_ESTAB_END_DATE
,SCHEDULED_REESTAB_START_DATE
,SCHEDULED_REESTAB_END_DATE
,USER_DEFINED_STATUS_ID
,STATUS_TYPE
,COMPLETION_DATE
,PERMIT_REQUIRED_FLAG
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ATTRIBUTE16
,ATTRIBUTE17
,ATTRIBUTE18
,ATTRIBUTE19
,ATTRIBUTE20
,ATTRIBUTE21
,ATTRIBUTE22
,ATTRIBUTE23
,ATTRIBUTE24
,ATTRIBUTE25
,ATTRIBUTE26
,ATTRIBUTE27
,ATTRIBUTE28
,ATTRIBUTE29
,ATTRIBUTE30
,APPROVED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN)
VALUES
( p_work_clearance_header_rec.WORK_CLEARANCE_ID
,p_work_clearance_header_rec.WORK_CLEARANCE_NAME
,p_work_clearance_header_rec.ISOLATION_ID
,p_work_clearance_header_rec.DESCRIPTION
,p_work_clearance_header_rec.ORGANIZATION_ID
,p_work_clearance_header_rec.PENDING_FLAG
,p_work_clearance_header_rec.SCHEDULED_ESTAB_START_DATE
,p_work_clearance_header_rec.SCHEDULED_ESTAB_END_DATE
,p_work_clearance_header_rec.SCHEDULED_REESTAB_START_DATE
,p_work_clearance_header_rec.SCHEDULED_REESTAB_END_DATE
,p_work_clearance_header_rec.USER_DEFINED_STATUS
,p_work_clearance_header_rec.SYSTEM_STATUS
,p_work_clearance_header_rec.COMPLETION_DATE
,p_work_clearance_header_rec.PERMIT_REQUIRED
,p_work_clearance_header_rec.ATTRIBUTE_CATEGORY
,p_work_clearance_header_rec.ATTRIBUTE1
,p_work_clearance_header_rec.ATTRIBUTE2
,p_work_clearance_header_rec.ATTRIBUTE3
,p_work_clearance_header_rec.ATTRIBUTE4
,p_work_clearance_header_rec.ATTRIBUTE5
,p_work_clearance_header_rec.ATTRIBUTE6
,p_work_clearance_header_rec.ATTRIBUTE7
,p_work_clearance_header_rec.ATTRIBUTE8
,p_work_clearance_header_rec.ATTRIBUTE9
,p_work_clearance_header_rec.ATTRIBUTE10
,p_work_clearance_header_rec.ATTRIBUTE11
,p_work_clearance_header_rec.ATTRIBUTE12
,p_work_clearance_header_rec.ATTRIBUTE13
,p_work_clearance_header_rec.ATTRIBUTE14
,p_work_clearance_header_rec.ATTRIBUTE15
,p_work_clearance_header_rec.ATTRIBUTE16
,p_work_clearance_header_rec.ATTRIBUTE17
,p_work_clearance_header_rec.ATTRIBUTE18
,p_work_clearance_header_rec.ATTRIBUTE19
,p_work_clearance_header_rec.ATTRIBUTE20
,p_work_clearance_header_rec.ATTRIBUTE21
,p_work_clearance_header_rec.ATTRIBUTE22
,p_work_clearance_header_rec.ATTRIBUTE23
,p_work_clearance_header_rec.ATTRIBUTE24
,p_work_clearance_header_rec.ATTRIBUTE25
,p_work_clearance_header_rec.ATTRIBUTE26
,p_work_clearance_header_rec.ATTRIBUTE27
,p_work_clearance_header_rec.ATTRIBUTE28
,p_work_clearance_header_rec.ATTRIBUTE29
,p_work_clearance_header_rec.ATTRIBUTE30
,decode(p_work_clearance_header_rec.APPROVED_BY, FND_API.G_MISS_CHAR, NULL, p_work_clearance_header_rec.APPROVED_BY)
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id);
END INSERT_ROW;
* Procedure : UPDATE_ROW
* Purpose : Procedure will perform an update on the table
*********************************************************************/
PROCEDURE UPDATE_ROW
( p_work_clearance_header_rec IN EAM_PROCESS_SAFETY_PUB. eam_clearance_header_rec_type
, x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_Status OUT NOCOPY VARCHAR2
)IS
BEGIN
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Updating Permit rec for ' || p_work_clearance_header_rec.WORK_CLEARANCE_NAME); END IF;
UPDATE EAM_WORK_CLEARANCES
SET WORK_CLEARANCE_NAME = p_work_clearance_header_rec.WORK_CLEARANCE_NAME
,DESCRIPTION =p_work_clearance_header_rec.DESCRIPTION
,LAST_ISOLATION_ID =p_work_clearance_header_rec.ISOLATION_ID
,PENDING_FLAG =p_work_clearance_header_rec.PENDING_FLAG
,SCHEDULED_ESTAB_START_DATE =p_work_clearance_header_rec.SCHEDULED_ESTAB_START_DATE
,SCHEDULED_ESTAB_END_DATE =p_work_clearance_header_rec.SCHEDULED_ESTAB_END_DATE
,SCHEDULED_REESTAB_START_DATE =p_work_clearance_header_rec.SCHEDULED_REESTAB_START_DATE
,SCHEDULED_REESTAB_END_DATE =p_work_clearance_header_rec.SCHEDULED_REESTAB_END_DATE
,STATUS_TYPE =p_work_clearance_header_rec.SYSTEM_STATUS
,USER_DEFINED_STATUS_ID =p_work_clearance_header_rec.USER_DEFINED_STATUS
,APPROVED_BY =p_work_clearance_header_rec.APPROVED_BY
,COMPLETION_DATE =p_work_clearance_header_rec.COMPLETION_DATE
,PERMIT_REQUIRED_FLAG =p_work_clearance_header_rec.PERMIT_REQUIRED
,ATTRIBUTE_CATEGORY =p_work_clearance_header_rec.ATTRIBUTE_CATEGORY
,ATTRIBUTE1 =p_work_clearance_header_rec.ATTRIBUTE1
,ATTRIBUTE2 =p_work_clearance_header_rec.ATTRIBUTE2
,ATTRIBUTE3 =p_work_clearance_header_rec.ATTRIBUTE3
,ATTRIBUTE4 =p_work_clearance_header_rec.ATTRIBUTE4
,ATTRIBUTE5 =p_work_clearance_header_rec.ATTRIBUTE5
,ATTRIBUTE6 =p_work_clearance_header_rec.ATTRIBUTE6
,ATTRIBUTE7 =p_work_clearance_header_rec.ATTRIBUTE7
,ATTRIBUTE8 =p_work_clearance_header_rec.ATTRIBUTE8
,ATTRIBUTE9 =p_work_clearance_header_rec.ATTRIBUTE9
,ATTRIBUTE10 =p_work_clearance_header_rec.ATTRIBUTE10
,ATTRIBUTE11 =p_work_clearance_header_rec.ATTRIBUTE11
,ATTRIBUTE12 =p_work_clearance_header_rec.ATTRIBUTE12
,ATTRIBUTE13 =p_work_clearance_header_rec.ATTRIBUTE13
,ATTRIBUTE14 =p_work_clearance_header_rec.ATTRIBUTE14
,ATTRIBUTE15 =p_work_clearance_header_rec.ATTRIBUTE15
,ATTRIBUTE16 =p_work_clearance_header_rec.ATTRIBUTE16
,ATTRIBUTE17 =p_work_clearance_header_rec.ATTRIBUTE17
,ATTRIBUTE18 =p_work_clearance_header_rec.ATTRIBUTE18
,ATTRIBUTE19 =p_work_clearance_header_rec.ATTRIBUTE19
,ATTRIBUTE20 =p_work_clearance_header_rec.ATTRIBUTE20
,ATTRIBUTE21 =p_work_clearance_header_rec.ATTRIBUTE21
,ATTRIBUTE22 =p_work_clearance_header_rec.ATTRIBUTE22
,ATTRIBUTE23 =p_work_clearance_header_rec.ATTRIBUTE23
,ATTRIBUTE24 =p_work_clearance_header_rec.ATTRIBUTE24
,ATTRIBUTE25 =p_work_clearance_header_rec.ATTRIBUTE25
,ATTRIBUTE26 =p_work_clearance_header_rec.ATTRIBUTE26
,ATTRIBUTE27 =p_work_clearance_header_rec.ATTRIBUTE27
,ATTRIBUTE28 =p_work_clearance_header_rec.ATTRIBUTE28
,ATTRIBUTE29 =p_work_clearance_header_rec.ATTRIBUTE29
,ATTRIBUTE30 =p_work_clearance_header_rec.ATTRIBUTE30
,LAST_UPDATE_DATE =SYSDATE
,LAST_UPDATED_BY =FND_GLOBAL.user_id
,LAST_UPDATE_LOGIN =FND_GLOBAL.login_id
WHERE WORK_CLEARANCE_ID = p_work_clearance_header_rec.WORK_CLEARANCE_ID
AND organization_id = p_work_clearance_header_rec.organization_id;
END UPDATE_ROW;
Insert_Row
( p_work_clearance_header_rec => p_work_clearance_header_rec
, x_mesg_token_Tbl => x_mesg_token_Tbl
, x_return_Status => l_return_status
);
ELSIF p_work_clearance_header_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
THEN
Update_Row
( p_work_clearance_header_rec => p_work_clearance_header_rec
, x_mesg_token_Tbl => x_mesg_token_Tbl
, x_return_Status => l_return_status
);
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Update status in clearance table'); END IF;
UPDATE EAM_WORK_CLEARANCES
SET STATUS_TYPE = l_to_status_type
WHERE ORGANIZATION_ID = l_organization_id
AND WORK_CLEARANCE_ID = l_clearance_id;
* Procedure : INSERT_CLEARANCE_HISTORY_ROW
* Purpose : Procedure will insert records for CLEARANCE history
*********************************************************************
PROCEDURE CLEARANCE_ PERMIT_HISTORY_ROW
( p_object_id IN NUMBER
, p_object_name IN VARCHAR2
, p_object_type IN NUMBER
, p_event_name IN VARCHAR2
, p_permit_status IN VARCHAR2
, p_event_desc IN VARCHAR2
, p_user_id IN NUMBER,
, p_event_date IN DATE
, x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_Status OUT NOCOPY VARCHAR2
);*/