[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT employee_id
FROM fnd_user
WHERE user_id = v_user_id ;
SELECT *
FROM gmo_opert_cert_header
WHERE organization_id = v_org_id
AND object_id = v_object_id
AND object_type = v_object_type;
SELECT *
FROM gmo_opert_cert_detail
WHERE header_id = v_header_id ;
SELECT HOU.BUSINESS_GROUP_ID
-- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION1), TO_NUMBER(NULL)) SET_OF_BOOKS_ID,
-- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3), TO_NUMBER(NULL)) OPERATING_UNIT,
-- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION2), null) LEGAL_ENTITY
FROM HR_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI2
WHERE HOU.organization_id = V_org_id
AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information';
SELECT COUNT(*) into l_cert_count
FROM gmo_opert_cert_detail
WHERE header_id = l_opert_cert_hdr.header_id
AND qualification_type = 1 -- 1 is certification
AND FROM_DATE <= l_eff_date
AND nvl(TO_DATE, sysdate) >= l_eff_date
AND qualification_id NOT IN (SELECT certification_id
FROM OTA_CERT_ENROLLMENTS oce
WHERE oce.certification_status_code ='CERTIFIED'
AND oce.completion_date <= SYSDATE
AND oce.business_group_id = l_business_group_id
AND oce.person_id = l_employ_id );
SELECT COUNT(*) into l_comp_count
FROM gmo_opert_cert_detail ocd
WHERE ocd.header_id = l_opert_cert_hdr.header_id
AND ocd.qualification_type = 2 -- 2 is competence
AND FROM_DATE <= l_eff_date
AND nvl(TO_DATE, sysdate) >= l_eff_date
AND ocd.qualification_id NOT IN (SELECT competence_id
FROM per_competence_elements pce
WHERE nvl(pce.effective_date_to, sysdate) >= SYSDATE
AND pce.effective_date_from <= SYSDATE
AND pce.PROFICIENCY_LEVEL_ID >= ocd.PROFICIENCY_LEVEL_ID
AND pce.business_group_id = l_business_group_id
AND pce.person_id = l_employ_id );
SELECT employee_id
FROM fnd_user
WHERE user_id = v_user_id ;
SELECT HOU.BUSINESS_GROUP_ID
-- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION1), TO_NUMBER(NULL)) SET_OF_BOOKS_ID,
-- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3), TO_NUMBER(NULL)) OPERATING_UNIT,
-- DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION2), null) LEGAL_ENTITY
FROM HR_ORGANIZATION_UNITS HOU, HR_ORGANIZATION_INFORMATION HOI2
WHERE HOU.organization_id = V_org_id
AND HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information';
SELECT ocd.Qualification_id , ocd.Qualification_type, ocd.PROFICIENCY_LEVEL_ID
FROM gmo_opert_cert_detail ocd
WHERE header_id = v_header_id
AND qualification_type = 1 -- 1 is certification
AND FROM_DATE <= v_date
AND nvl(TO_DATE,sysdate) >= v_date
AND qualification_id NOT IN (SELECT certification_id
FROM OTA_CERT_ENROLLMENTS oce
WHERE oce.certification_status_code ='CERTIFIED'
AND oce.completion_date <= SYSDATE
AND oce.business_group_id = l_business_group_id
AND oce.person_id = l_employ_id )
UNION
SELECT ocd.Qualification_id , ocd.Qualification_type, ocd.PROFICIENCY_LEVEL_ID
FROM gmo_opert_cert_detail ocd
WHERE ocd.header_id = v_header_id
AND ocd.qualification_type = 2 -- 2 is competence
AND FROM_DATE <= v_date
AND nvl(TO_DATE,sysdate) >= v_date
AND ocd.qualification_id NOT IN (SELECT competence_id
FROM per_competence_elements pce
WHERE nvl(pce.effective_date_to, sysdate) >= SYSDATE
AND pce.effective_date_from <= SYSDATE
AND pce.PROFICIENCY_LEVEL_ID >= ocd.PROFICIENCY_LEVEL_ID
AND pce.business_group_id = v_business_id
AND pce.person_id = l_employ_id );
gmo_cert_trans_detail_dbl.insert_row( l_trans_detail_id,l_operator_certificate_id,l_header_id,
get_rec.qualification_id,
get_rec.qualification_type,
get_rec.PROFICIENCY_LEVEL_ID, x_return_status);
Procedure Update_erecord(
p_ERECORD_ID IN NUMBER
,p_Operator_certificate_id IN NUMBER
,p_EVENT_KEY IN VARCHAR2
,p_EVENT_NAME IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2) IS
l_Operator_certificate_id NUMBER ;
update GMO_OPERATOR_CERT_TRANS
set erecord_id = p_erecord_id
where operator_certificate_id = l_operator_certificate_id
AND erecord_id IS NULL ;
END Update_erecord;
procedure update_cert_record(p_Operator_certificate_id IN NUMBER
,p_EVENT_KEY IN VARCHAR2
,p_EVENT_NAME IN VARCHAR2
,p_ERECORD_ID IN NUMBER
,p_user_key_label_token IN VARCHAR2
,p_user_key_value IN VARCHAR2
,p_transaction_id IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR CUR_GET_CERT_RECORD IS
SELECT EVENT_NAME,EVENT_KEY,USER_KEY_LABEL_TOKEN,USER_KEY_VALUE,ERECORD_ID,
Operator_certificate_id,TRANSACTION_ID
FROM GMO_OPERATOR_CERT_TRANS
WHERE operator_certificate_id = p_Operator_certificate_id;
update GMO_OPERATOR_CERT_TRANS
set EVENT_NAME = nvl(p_EVENT_NAME,get_rec.EVENT_NAME)
, EVENT_KEY = nvl(p_EVENT_KEY,get_rec.EVENT_KEY)
, USER_KEY_LABEL_TOKEN = nvl(p_user_key_label_token,get_rec.USER_KEY_LABEL_TOKEN)
, USER_KEY_VALUE = nvl(p_user_key_value,get_rec.USER_KEY_VALUE)
, ERECORD_ID = nvl(p_ERECORD_ID,get_rec.ERECORD_ID)
, TRANSACTION_ID = nvl(p_transaction_id,get_rec.TRANSACTION_ID)
, STATUS = 'S'
where operator_certificate_id = get_rec.operator_certificate_id ;
END update_cert_record;
,p_LAST_UPDATE_DATE IN DATE
,p_LAST_UPDATED_BY IN NUMBER
,p_LAST_UPDATE_LOGIN IN NUMBER
,x_return_Status OUT NOCOPY VARCHAR2 ) IS
l_return_status VARCHAR2(1) ;
l_LAST_UPDATE_DATE DATE;
l_LAST_UPDATED_BY NUMBER;
l_LAST_UPDATE_LOGIN NUMBER;
l_LAST_UPDATE_DATE := p_LAST_UPDATE_DATE ;
l_LAST_UPDATED_BY := p_LAST_UPDATED_BY ;
l_LAST_UPDATE_LOGIN := p_LAST_UPDATE_LOGIN ;
gmo_oper_cert_trans_dbl.INSERT_ROW( p_operator_certificate_id => l_operator_certificate_id
,p_header_id => l_header_id
,p_transaction_id => l_transaction_id
,p_user_id => l_user_id
,p_comments => l_comments
,p_overrider_id => l_overrider_id
,p_user_key_label_product => l_user_key_label_product
,p_user_key_label_token => l_user_key_label_token
,p_user_key_value =>l_user_key_value
,p_erecord_id => NULL
,p_trans_object_id => l_trans_object_id
,p_status => l_status
,p_event_name => l_event_name
,p_event_key => l_event_key
,p_creation_date => l_creation_date
,p_created_by => l_created_by
,p_last_update_date => l_last_update_date
,p_last_updated_by => l_last_updated_by
,P_LAST_UPDATE_LOGIN => l_LAST_UPDATE_LOGIN
,x_return_Status => l_return_status);
SELECT organization_id into l_org_id
FROM gmo_opert_cert_header
Where header_id = l_header_id ;
gmo_opert_cert_gtmp_dbl.INSERT_ROW (
p_ERECORD_ID => l_erecord_id
,p_operator_certificate_id => l_operator_certificate_id
,p_EVENT_KEY => l_event_key
,p_EVENT_NAME => l_event_name
,x_return_status => l_return_status ) ;