[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER,
x_org_id IN NUMBER ,
x_emplmnt_category_code IN VARCHAR2
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_PE_TYP_INSTANCES_ALL
WHERE rowid = x_rowid;
IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
Close cur_old_ref_values;
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
IF (p_action = 'UPDATE') THEN
new_references.creation_date := old_references.creation_date;
new_references.last_update_date := x_last_update_date;
new_references.last_updated_by := x_last_updated_by;
new_references.last_update_login := x_last_update_login;
PROCEDURE After_Insert_update AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
pkpatel 25-APR-2003 Bug 2908851
Tuned the cursors c_get_others and c_get_active. Removed the unnecessary join with
igs_lookups_view and changed igs_pe_typ_instances to igs_pe_typ_instances_all
pkpatel 4-MAY-2003 Bug 2989307
Removed the existence of active records for person types other than OTHER
(reverse chronological order - newest change first)
***************************************************************/
CURSOR c_get_others(cp_system_type igs_pe_person_types.system_type%TYPE) IS
SELECT pti.rowid
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types pt
WHERE pt.system_type = cp_system_type --'OTHER'
AND pti.person_type_code = pt.person_type_code
AND pti.person_id = new_references.person_id;
SELECT pti.type_instance_id
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types pt
WHERE pt.system_type <> cp_system_type --'OTHER'
AND pti.person_type_code = pt.person_type_code
AND pti.person_id = new_references.person_id;
delete_row(lv_rowid);
END After_Insert_update;
SELECT ppt.system_type
FROM igs_pe_person_types ppt
WHERE ppt.person_type_code = l_person_type_code;
SELECT 'X'
FROM igs_pe_typ_instances_all pti, igs_pe_person_types ppt
WHERE pti.person_id = cp_person_id AND
pti.person_type_code = ppt.person_type_code AND
ppt.system_type = cp_system_type AND
(pti.end_date is NULL OR (pti.end_date IS NOT NULL AND trunc(pti.end_date) > trunc(SYSDATE)));
Procedure after_insertupdate2 AS
/*************************************************************
Created By :IDK
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
pkpatel 20-sep-2004 Bug 3690856 (removed the cursor c_get_prospect since its not used. Removed igs_lookups_view join in th cursor c_get_applicant. Made cursors parameterized.
sykrishn IDOPA2 Commented part which prevents PROSPECT and STUDENT to coexist
(reverse chronological order - newest change first)
***************************************************************/
CURSOR c_get_applicant(cp_system_type igs_pe_person_types.system_type%TYPE,
cp_person_id igs_pe_typ_instances_all.person_id%TYPE,
cp_course_cd igs_pe_typ_instances_all.nominated_course_cd%TYPE) IS
SELECT pti.rowid,pti.*
FROM igs_pe_typ_instances_all pti,
igs_pe_person_types pt
WHERE pt.system_type = cp_system_type --'APPLICANT'
AND pti.person_type_code = pt.person_type_code
AND pti.person_id = cp_person_id
AND pti.nominated_course_cd = cp_course_cd
AND pti.end_date IS NULL;
SELECT pt.system_type
FROM igs_pe_person_types pt
WHERE pt.person_type_code = cp_person_type_code;
igs_pe_typ_instances_pkg.update_row(
X_ROWID => c_appl_rec.rowid,
X_PERSON_ID => c_appl_rec.PERSON_ID,
X_COURSE_CD => c_appl_rec.COURSE_CD,
X_TYPE_INSTANCE_ID => c_appl_rec.TYPE_INSTANCE_ID,
X_PERSON_TYPE_CODE => c_appl_rec.PERSON_TYPE_CODE,
X_CC_VERSION_NUMBER => c_appl_rec.CC_VERSION_NUMBER,
X_FUNNEL_STATUS => c_appl_rec.FUNNEL_STATUS,
X_ADMISSION_APPL_NUMBER => c_appl_rec.ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD => c_appl_rec.NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER => c_appl_rec.NCC_VERSION_NUMBER,
X_SEQUENCE_NUMBER => c_appl_rec.SEQUENCE_NUMBER,
X_START_DATE => c_appl_rec.START_DATE,
X_END_DATE => SYSDATE,
X_CREATE_METHOD => c_appl_rec.CREATE_METHOD,
X_ENDED_BY => c_appl_rec.ENDED_BY,
X_END_METHOD => 'CREATE_STUDENT',
X_MODE => 'R',
X_EMPLMNT_CATEGORY_CODE => c_appl_rec.EMPLMNT_CATEGORY_CODE);
END after_insertupdate2;
PROCEDURE before_insert AS
/*************************************************************
Created By : prabhat.patel
Date Created By :
Purpose :Bug No 2389552. The Person Type code should have the value that is not closed.
Since at many places the closed indicator is not checked, the Active person type code is being passed explicitly.
Know limitations, enhancements or remarks
Change History
Who When What
pathipat 17-JUL-2002 Added check for system person types with more than one mapping
for Bug No: 2464771
pkpatel 3-APR-2003 Bug No: 2859277
Closed the cursor person_type_cur in else condition.
asbala 12-SEP-03 Changed igs_lookups_view to igs_lookup_values in CURSOR meaning_cur
(reverse chronological order - newest change first)
***************************************************************/
CURSOR system_type_cur IS
SELECT system_type
FROM igs_pe_person_types pt
WHERE pt.person_type_code = new_references.person_type_code;
SELECT person_type_code
FROM igs_pe_person_types pt
WHERE pt.system_type = cp_system_type AND
pt.closed_ind = cp_closed_ind;
SELECT meaning
FROM igs_lookup_values
WHERE lookup_code = cp_system_type AND
lookup_type = cp_lookup_type;
END before_insert;
Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
SELECT rowid
FROM igs_pe_typ_instances_all
WHERE type_instance_id = x_type_instance_id
FOR UPDATE NOWAIT;
SELECT rowid
FROM igs_pe_typ_instances_all
WHERE person_id = x_person_id
AND admission_appl_number = x_admission_appl_number
AND nominated_course_cd = x_nominated_course_cd
AND sequence_number = x_sequence_number ;
SELECT rowid
FROM igs_pe_typ_instances_all
WHERE person_type_code = x_person_type_code ;
SELECT rowid
FROM igs_pe_typ_instances_all
WHERE course_cd = x_course_cd
AND cc_version_number = x_version_number ;
SELECT rowid
FROM igs_pe_typ_instances_all
WHERE person_id = x_person_id ;
SELECT setup_data_element_id, person_type_code, data_element,
value, required_ind
FROM igs_pe_stup_data_emt
WHERE person_type_code = p_person_type_code
AND NVL(required_ind, 'S') IN ('M');
SELECT table_name, column_name
FROM igs_pe_data_element
WHERE UPPER(data_element) = UPPER(p_data_element) ;
SELECT meaning
FROM IGS_LOOKUP_VALUES
WHERE lookup_type=cp_lookup_type AND
lookup_code=cp_lookup_code;
SELECT meaning
FROM ar_lookups
WHERE lookup_type = cp_lookup_type
AND lookup_code = cp_lookup_code;
lvc_SQLStmt := 'SELECT 1 FROM ';
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER ,
x_org_id IN NUMBER ,
x_emplmnt_category_code IN VARCHAR2
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
pkpatel 5-JUL-2002 Bug No 2389552
Added the call to the procedure before_insert
rrengara 4-JAN-2002 Added code logic for the bug 2168915
(reverse chronological order - newest change first)
***************************************************************/
BEGIN
Set_Column_Values (
p_action,
x_rowid,
x_person_id,
x_course_cd,
x_type_instance_id,
x_person_type_code,
x_cc_version_number,
x_funnel_status,
x_admission_appl_number,
x_nominated_course_cd,
x_ncc_version_number,
x_sequence_number,
x_start_date,
x_end_date,
x_create_method,
x_ended_by,
x_end_method,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_org_id,
x_emplmnt_category_code
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
Null;
before_insert;
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
Null;
ELSIF (p_action = 'DELETE') THEN
Null;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF Get_PK_For_Validation (
new_references.type_instance_id) THEN
Fnd_Message.Set_name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
Check_Constraints;
ELSIF (p_action = 'VALIDATE_DELETE') THEN
NULL;
Added Business Event logic for INSERT, UPDATE and DELETE cases respectively
(reverse chronological order - newest change first)
***************************************************************/
CURSOR get_usr_id_cur(cp_person_id fnd_user.person_party_id%type) IS
SELECT user_id
FROM fnd_user
WHERE person_party_id = cp_person_id;
SELECT system_type
FROM igs_pe_person_types pt
WHERE pt.person_type_code = cp_person_type;
l_person_type_w_other varchar2(30) ; -- to hold the person_type value during insert of a person type for the first time.
l_person_id_w_other number; -- to hold the person_id value during insert of a person type for the first time.
SELECT MAX(NVL(end_date,TO_DATE('4712/12/31','YYYY/MM/DD'))) FROM igs_pe_typ_instances_all pti
WHERE pti.person_id = cp_person_id
AND pti.rowid <> cp_rowid
AND SYSDATE BETWEEN pti.start_date and NVL(pti.end_date, SYSDATE)
AND pti.person_type_code IN
(select person_type_code from igs_pe_person_types pt where system_type =cp_system_type) ;
IF (p_action = 'INSERT') THEN
After_Insert_Update;
after_insertupdate2;
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to After Update.
After_Insert_Update;
ELSIF (p_action = 'DELETE') THEN
--Call all the procedures related to After Delete.
-- cursor when action is Delete
OPEN get_usr_id_cur(old_references.person_id);
procedure INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
x_PERSON_ID IN NUMBER,
x_COURSE_CD IN VARCHAR2,
x_TYPE_INSTANCE_ID IN OUT NOCOPY NUMBER,
x_PERSON_TYPE_CODE IN VARCHAR2,
x_CC_VERSION_NUMBER IN NUMBER,
x_FUNNEL_STATUS IN VARCHAR2,
x_ADMISSION_APPL_NUMBER IN NUMBER,
x_NOMINATED_COURSE_CD IN VARCHAR2,
x_NCC_VERSION_NUMBER IN NUMBER,
x_SEQUENCE_NUMBER IN NUMBER,
x_START_DATE IN DATE,
x_END_DATE IN DATE,
x_CREATE_METHOD IN VARCHAR2,
x_ENDED_BY IN NUMBER,
x_END_METHOD IN VARCHAR2,
X_MODE in VARCHAR2 ,
X_ORG_ID in NUMBER ,
X_EMPLMNT_CATEGORY_CODE IN VARCHAR2
)
AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
cursor C is select ROWID from IGS_PE_TYP_INSTANCES_ALL
where TYPE_INSTANCE_ID= X_TYPE_INSTANCE_ID;
X_LAST_UPDATE_DATE DATE ;
X_LAST_UPDATED_BY NUMBER ;
X_LAST_UPDATE_LOGIN NUMBER ;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
select IGS_PE_TYPE_INSTANCES_S.NEXTVAL INTO x_type_instance_id FROM DUAL;
p_action=>'INSERT',
x_rowid=>X_ROWID,
x_person_id=>X_PERSON_ID,
x_course_cd=>X_COURSE_CD,
x_type_instance_id=>X_TYPE_INSTANCE_ID,
x_person_type_code=>X_PERSON_TYPE_CODE,
x_cc_version_number=>X_CC_VERSION_NUMBER,
x_funnel_status=>X_FUNNEL_STATUS,
x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
x_ncc_version_number=>X_NCC_VERSION_NUMBER,
x_sequence_number=>X_SEQUENCE_NUMBER,
x_start_date=>X_START_DATE,
x_end_date=>X_END_DATE,
x_create_method=>X_CREATE_METHOD,
x_ended_by=>X_ENDED_BY,
x_end_method=>X_END_METHOD,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_BY,
x_last_update_date=>X_LAST_UPDATE_DATE,
x_last_updated_by=>X_LAST_UPDATED_BY,
x_last_update_login=>X_LAST_UPDATE_LOGIN,
x_org_id=>igs_ge_gen_003.get_org_id,
x_emplmnt_category_code => x_emplmnt_category_code
);
insert into IGS_PE_TYP_INSTANCES_ALL (
PERSON_ID
,COURSE_CD
,TYPE_INSTANCE_ID
,PERSON_TYPE_CODE
,CC_VERSION_NUMBER
,FUNNEL_STATUS
,ADMISSION_APPL_NUMBER
,NOMINATED_COURSE_CD
,NCC_VERSION_NUMBER
,SEQUENCE_NUMBER
,START_DATE
,END_DATE
,CREATE_METHOD
,ENDED_BY
,END_METHOD
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID
,EMPLMNT_CATEGORY_CODE
) values (
NEW_REFERENCES.PERSON_ID
,NEW_REFERENCES.COURSE_CD
,NEW_REFERENCES.TYPE_INSTANCE_ID
,NEW_REFERENCES.PERSON_TYPE_CODE
,NEW_REFERENCES.CC_VERSION_NUMBER
,NEW_REFERENCES.FUNNEL_STATUS
,NEW_REFERENCES.ADMISSION_APPL_NUMBER
,NEW_REFERENCES.NOMINATED_COURSE_CD
,NEW_REFERENCES.NCC_VERSION_NUMBER
,NEW_REFERENCES.SEQUENCE_NUMBER
,NEW_REFERENCES.START_DATE
,NEW_REFERENCES.END_DATE
,NEW_REFERENCES.CREATE_METHOD
,NEW_REFERENCES.ENDED_BY
,NEW_REFERENCES.END_METHOD
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_LAST_UPDATE_LOGIN
,NEW_REFERENCES.ORG_ID
,NEW_REFERENCES.EMPLMNT_CATEGORY_CODE
);
p_action => 'INSERT' ,
x_rowid => X_ROWID );
end INSERT_ROW;
cursor c1 is select
PERSON_ID
, COURSE_CD
, PERSON_TYPE_CODE
, CC_VERSION_NUMBER
, FUNNEL_STATUS
, ADMISSION_APPL_NUMBER
, NOMINATED_COURSE_CD
, NCC_VERSION_NUMBER
, SEQUENCE_NUMBER
, START_DATE
, END_DATE
, CREATE_METHOD
, ENDED_BY
, END_METHOD
, EMPLMNT_CATEGORY_CODE
from IGS_PE_TYP_INSTANCES_ALL
where ROWID = X_ROWID
for update nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_ROWID in VARCHAR2,
x_PERSON_ID IN NUMBER,
x_COURSE_CD IN VARCHAR2,
x_TYPE_INSTANCE_ID IN NUMBER,
x_PERSON_TYPE_CODE IN VARCHAR2,
x_CC_VERSION_NUMBER IN NUMBER,
x_FUNNEL_STATUS IN VARCHAR2,
x_ADMISSION_APPL_NUMBER IN NUMBER,
x_NOMINATED_COURSE_CD IN VARCHAR2,
x_NCC_VERSION_NUMBER IN NUMBER,
x_SEQUENCE_NUMBER IN NUMBER,
x_START_DATE IN DATE,
x_END_DATE IN DATE,
x_CREATE_METHOD IN VARCHAR2,
x_ENDED_BY IN NUMBER,
x_END_METHOD IN VARCHAR2,
X_MODE in VARCHAR2 ,
X_EMPLMNT_CATEGORY_CODE IN VARCHAR2
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
X_LAST_UPDATE_DATE DATE ;
X_LAST_UPDATED_BY NUMBER ;
X_LAST_UPDATE_LOGIN NUMBER ;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
p_action=>'UPDATE',
x_rowid=>X_ROWID,
x_person_id=>X_PERSON_ID,
x_course_cd=>X_COURSE_CD,
x_type_instance_id=>X_TYPE_INSTANCE_ID,
x_person_type_code=>X_PERSON_TYPE_CODE,
x_cc_version_number=>X_CC_VERSION_NUMBER,
x_funnel_status=>X_FUNNEL_STATUS,
x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
x_ncc_version_number=>X_NCC_VERSION_NUMBER,
x_sequence_number=>X_SEQUENCE_NUMBER,
x_start_date=>X_START_DATE,
x_end_date=>X_END_DATE,
x_create_method=>X_CREATE_METHOD,
x_ended_by=>X_ENDED_BY,
x_end_method=>X_END_METHOD,
x_creation_date=>X_LAST_UPDATE_DATE,
x_created_by=>X_LAST_UPDATED_BY,
x_last_update_date=>X_LAST_UPDATE_DATE,
x_last_updated_by=>X_LAST_UPDATED_BY,
x_last_update_login=>X_LAST_UPDATE_LOGIN,
x_emplmnt_category_code => X_EMPLMNT_CATEGORY_CODE
);
update IGS_PE_TYP_INSTANCES_ALL set
PERSON_ID = NEW_REFERENCES.PERSON_ID,
COURSE_CD = NEW_REFERENCES.COURSE_CD,
PERSON_TYPE_CODE = NEW_REFERENCES.PERSON_TYPE_CODE,
CC_VERSION_NUMBER = NEW_REFERENCES.CC_VERSION_NUMBER,
FUNNEL_STATUS = NEW_REFERENCES.FUNNEL_STATUS,
ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADMISSION_APPL_NUMBER,
NOMINATED_COURSE_CD = NEW_REFERENCES.NOMINATED_COURSE_CD,
NCC_VERSION_NUMBER = NEW_REFERENCES.NCC_VERSION_NUMBER,
SEQUENCE_NUMBER = NEW_REFERENCES.SEQUENCE_NUMBER,
START_DATE = NEW_REFERENCES.START_DATE,
END_DATE = NEW_REFERENCES.END_DATE,
CREATE_METHOD = NEW_REFERENCES.CREATE_METHOD,
ENDED_BY = NEW_REFERENCES.ENDED_BY,
END_METHOD = NEW_REFERENCES.END_METHOD,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
EMPLMNT_CATEGORY_CODE = X_EMPLMNT_CATEGORY_CODE
where ROWID = X_ROWID;
p_action => 'UPDATE' ,
x_rowid => X_ROWID
);
end UPDATE_ROW;
cursor c1 is select ROWID from IGS_PE_TYP_INSTANCES_ALL
where TYPE_INSTANCE_ID= X_TYPE_INSTANCE_ID
;
INSERT_ROW (
X_ROWID,
X_PERSON_ID,
X_COURSE_CD,
X_TYPE_INSTANCE_ID,
X_PERSON_TYPE_CODE,
X_CC_VERSION_NUMBER,
X_FUNNEL_STATUS,
X_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER,
X_SEQUENCE_NUMBER,
X_START_DATE,
X_END_DATE,
X_CREATE_METHOD,
X_ENDED_BY,
X_END_METHOD,
X_MODE,
X_ORG_ID,
X_EMPLMNT_CATEGORY_CODE);
UPDATE_ROW (
X_ROWID,
X_PERSON_ID,
X_COURSE_CD,
X_TYPE_INSTANCE_ID,
X_PERSON_TYPE_CODE,
X_CC_VERSION_NUMBER,
X_FUNNEL_STATUS,
X_ADMISSION_APPL_NUMBER,
X_NOMINATED_COURSE_CD,
X_NCC_VERSION_NUMBER,
X_SEQUENCE_NUMBER,
X_START_DATE,
X_END_DATE,
X_CREATE_METHOD,
X_ENDED_BY,
X_END_METHOD,
X_MODE,
X_EMPLMNT_CATEGORY_CODE);
procedure DELETE_ROW (
X_ROWID in VARCHAR2,
x_mode IN VARCHAR2
) AS
/*************************************************************
Created By :
Date Created By :
Purpose :
Know limitations, enhancements or remarks
Change History
Who When What
(reverse chronological order - newest change first)
***************************************************************/
begin
Before_DML (
p_action => 'DELETE',
x_rowid => X_ROWID
);
delete from IGS_PE_TYP_INSTANCES_ALL
where ROWID = X_ROWID;
p_action => 'DELETE',
x_rowid => X_ROWID
);
end DELETE_ROW;