[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT WIDTH , precision , column_type ,scale
FROM FND_COLUMNS
WHERE table_id IN
( SELECT TABLE_ID
FROM FND_TABLES
WHERE table_name = p_table_name AND APPLICATION_ID = 8405)
AND column_name = p_param_name
AND APPLICATION_ID = 8405;
SELECT party_id
FROM hz_parties hzp
WHERE hzp.party_id = p_person_id;
SELECT code_id
FROM IGS_AD_CODE_CLASSES
WHERE class = 'STATUS' AND
code_id = p_estatus;
SELECT code_id
FROM IGS_AD_CODE_CLASSES
WHERE class = 'LEVEL' AND
code_id = p_elevel;
SELECT code_id
FROM IGS_AD_CODE_CLASSES
WHERE class = 'SPECIAL_GROUP1' AND
code_id = p_spcl_grp1;
SELECT code_id
FROM IGS_AD_CODE_CLASSES
WHERE class = 'SPECIAL_GROUP2' AND
code_id = p_spcl_grp2;
SELECT admission_application_type, admission_cat, s_admission_process_type
FROM igs_ad_ss_appl_typ
WHERE admission_application_type = p_app_type;
SELECT adm_cal_type
FROM IGS_AD_PRD_AD_PRC_CA
WHERE adm_cal_type = p_adm_cal_type AND
adm_ci_sequence_number = p_adm_cal_seq AND
admission_cat = p_admission_cat AND
s_admission_process_type = p_s_admission_process_type;
SELECT code_id
FROM IGS_AD_CODE_CLASSES
WHERE class = 'SYS_APPL_SOURCE' AND
code_id = p_app_source_id AND
SYSTEM_STATUS IN ('WEB_STAFF', 'WEB_APPL');
SELECT cal.cal_type, cal.sequence_number,cal.description
FROM IGS_CA_INST cal,
igs_ca_stat cstat
WHERE cal.cal_type = p_cal_type AND
cal.sequence_number = p_sequence_number AND
end_dt > sysdate AND
cstat.cal_status = cal.cal_status AND
cstat.s_cal_status = 'ACTIVE';
SELECT ci1.CAL_TYPE
FROM
igs_ca_inst ci1,
igs_ca_inst_rel cir,
igs_ca_inst ci2,
igs_ca_type cat1,
igs_ca_type cat2,
igs_ca_stat cstat
WHERE
ci1.CAL_TYPE = p_adm_cal_type AND
ci1.sequence_number = p_adm_sequence_number AND
ci2.CAL_TYPE = p_acad_cal_type AND
ci2.sequence_number = p_acad_sequence_number AND
cir.sub_cal_type = ci1.CAL_TYPE AND
cir.sub_ci_sequence_number = ci1.sequence_number AND
ci2.CAL_TYPE = cir.sup_cal_type AND
ci2.sequence_number = cir.sup_ci_sequence_number AND
cat1.CAL_TYPE = ci1.CAL_TYPE AND
cat1.S_CAL_CAT = 'ADMISSION' AND
cat2.CAL_TYPE = ci2.CAL_TYPE AND
cat2.S_CAL_CAT = 'ACADEMIC' AND
ci2.end_dt > sysdate AND
ci1.end_dt > sysdate AND
cstat.cal_status = ci1.cal_status AND
cstat.cal_status = ci2.cal_status AND
cstat.s_cal_status = 'ACTIVE';
SELECT S_ADMISSION_STEP_TYPE
FROM IGS_AD_PRCS_CAT_STEP apcs
WHERE admission_cat = p_admission_cat AND
s_admission_process_type = p_s_adm_process_type AND
s_admission_step_type = 'LATE-APP' AND
mandatory_step_ind = 'Y';
INSERT INTO IGS_SS_ADM_APPL_STG (SS_ADM_APPL_ID,
PERSON_ID,
APPL_DATE,
ACAD_CAL_TYPE,
ACAD_CAL_SEQ_NUMBER,
ADM_CAL_TYPE,
ADM_CAL_SEQ_NUMBER,
ADMISSION_CAT,
S_ADM_PROCESS_TYPE,
ENTRY_STATUS,
ENTRY_LEVEL,
SPCL_GRP_1,
SPCL_GRP_2,
APPLY_FOR_FINAID,
FINAID_APPLY_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ADMISSION_APPLICATION_TYPE,
DESCRIPTION,
APP_SOURCE_ID,
APPL_FEE_AMT)
VALUES (IGS_SS_ADM_APPL_S.NEXTVAL,
l_person_id,
l_appl_date,
l_acad_cal_type,
l_acad_cal_seq_number,
l_adm_cal_type,
l_adm_cal_seq_number,
l_admission_cat,
l_s_adm_process_type,
l_entry_status,
l_entry_level,
l_spcl_gr1,
l_spcl_gr2,
l_apply_for_finaid,
l_finaid_apply_date,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
l_admission_application_type,
l_description,
l_apsource_id,
l_application_fee_amount
) RETURNING SS_ADM_APPL_ID INTO x_ss_adm_appl_id;
SELECT ss_adm_appl_id, person_id, admission_cat,s_adm_process_type, admission_application_type
FROM IGS_SS_ADM_APPL_STG
WHERE ss_adm_appl_id = p_ss_adm_appl_id;
SELECT sch_apl_to_id
FROM IGS_AD_SCHL_APLY_TO
WHERE sch_apl_to_id = p_sch_apl_to_id;
SELECT location_cd
FROM IGS_AD_LOCATION
WHERE location_cd = p_location_cd;
SELECT attendance_type
FROM IGS_EN_ATD_TYPE
WHERE attendance_type = p_attendance_type;
SELECT attendance_mode
FROM IGS_EN_ATD_MODE
WHERE attendance_mode = p_attendance_mode;
SELECT acopv.ACAD_CAL_TYPE
FROM IGS_PS_OFR_PAT_OFERPAT_V acopv,
IGS_AD_LOCATION loc,
IGS_EN_ATD_MODE atd_mode,
IGS_EN_ATD_TYPE atd_type
WHERE
(IGS_AD_GEN_013.ADMS_GET_COO_CRV(
acopv.course_cd,
acopv.version_number,
acopv.s_admission_process_type, 'Y') = 'Y') AND
(IGS_AD_GEN_013.ADMS_GET_ACAI_COO (
acopv.course_cd,
acopv.version_number,
acopv.location_cd,
acopv.attendance_mode,
acopv.attendance_type,
acopv.acad_cal_type,
acopv.acad_ci_sequence_number,
acopv.adm_cal_type,
acopv.adm_ci_sequence_number,
acopv.admission_cat,
acopv.s_admission_process_type,
'Y',
trunc(sysdate),
'Y')= 'Y') AND
acopv.admission_cat = p_admission_cat AND
acopv.s_admission_process_type = p_s_adm_process_type AND
acopv.location_cd = NVL(p_location_cd,acopv.location_cd )AND
acopv.attendance_mode = NVL(p_attendance_mode,acopv.attendance_mode) AND
acopv.attendance_type = NVL(p_attendance_type,acopv.attendance_type);
SELECT S_ADMISSION_STEP_TYPE
FROM IGS_AD_PRCS_CAT_STEP apcs
WHERE admission_cat = p_admission_cat AND
s_admission_process_type = p_s_adm_process_type AND
s_admission_step_type = 'PREF-LIMIT' AND
mandatory_step_ind = 'Y';
SELECT SS_ADMAPPL_PGM_ID
FROM IGS_SS_APP_PGM_STG
WHERE SS_ADM_APPL_ID = p_ss_adm_appl_id;
INSERT INTO IGS_SS_APP_PGM_STG (SS_ADMAPPL_PGM_ID,
SS_ADM_APPL_ID,
PERSON_ID,
PREFERENCE_NUMBER,
SCH_APL_TO_ID,
LOCATION_CD,
ATTENDANCE_TYPE,
ATTENDANCE_MODE,
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,
ATTRIBUTE31,
ATTRIBUTE32,
ATTRIBUTE33,
ATTRIBUTE34,
ATTRIBUTE35,
ATTRIBUTE36,
ATTRIBUTE37,
ATTRIBUTE38,
ATTRIBUTE39,
ATTRIBUTE40,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
VALUES (IGS_SS_ADMAPPL_PGM_S.NEXTVAL,
l_ss_adm_appl_id,
l_person_id,
l_preference_number,
l_sch_apl_to_id,
l_location_cd,
l_attendance_type,
l_attendance_mode,
l_attribute_category,
l_attribute1,
l_attribute2,
l_attribute3,
l_attribute4,
l_attribute5,
l_attribute6,
l_attribute7,
l_attribute8,
l_attribute9,
l_attribute10,
l_attribute11,
l_attribute12,
l_attribute13,
l_attribute14,
l_attribute15,
l_attribute16,
l_attribute17,
l_attribute18,
l_attribute19,
l_attribute20,
l_attribute21,
l_attribute22,
l_attribute23,
l_attribute24,
l_attribute25,
l_attribute26,
l_attribute27,
l_attribute28,
l_attribute29,
l_attribute30,
l_attribute31,
l_attribute32,
l_attribute33,
l_attribute34,
l_attribute35,
l_attribute36,
l_attribute37,
l_attribute38,
l_attribute39,
l_attribute40,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID) RETURNING SS_ADMAPPL_PGM_ID INTO x_ss_admappl_pgm_id;
PROCEDURE INSERT_STG_FEE_REQ_DET (
p_api_version IN NUMBER, -- standard Public API IN params
p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
p_commit IN VARCHAR2 default FND_API.G_FALSE,
p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2, -- standard Public API OUT params
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_SS_ADM_APPL_ID IN NUMBER, -- Staging table related params
p_PERSON_ID IN NUMBER,
p_APPLICANT_FEE_TYPE IN NUMBER,
p_APPLICANT_FEE_STATUS IN NUMBER,
p_FEE_DATE IN DATE,
p_FEE_PAYMENT_METHOD IN NUMBER,
p_FEE_AMOUNT IN NUMBER,
p_REFERENCE_NUM IN VARCHAR2
) AS
/*************************************************************
Created By : Pranay Fotedar
Date Created By : 28-Apr-2006
Purpose : Creation of Fee Records on submission
Change History
Who When What
pfotedar 2006/05/05 Added Validation for Fee Payment Method
(reverse chronological order - newest change first)
***************************************************************/
l_api_version CONSTANT NUMBER := '1.0';
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_STG_FEE_REQ_DET';
SELECT CC.CODE_ID
FROM IGS_AD_CODE_CLASSES CC
WHERE CC.CODE_ID = p_APPL_FEE_TYPE
AND CC.CLASS = 'SYS_FEE_TYPE'
AND NVL(CLOSED_IND,'N') = 'N'
AND CC.CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SELECT CC.CODE_ID
FROM IGS_AD_CODE_CLASSES CC
WHERE CC.CODE_ID = p_APPL_FEE_STATUS
AND CC.CLASS = 'SYS_FEE_STATUS'
AND NVL(CLOSED_IND,'N') = 'N'
AND CC.CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SELECT CC.CODE_ID
FROM IGS_AD_CODE_CLASSES CC
WHERE CC.CODE_ID = p_APPL_FEE_PAY_MET
AND CC.CLASS = 'SYS_FEE_PAY_METHOD'
AND NVL(CLOSED_IND,'N') = 'N'
AND CC.CLASS_TYPE_CODE='ADM_CODE_CLASSES';
SAVEPOINT INSERT_STG_FEE_REQ_DET_PUB;
INSERT INTO IGS_SS_APP_REQ_STG (
SS_APP_REQ_ID,
SS_ADM_APPL_ID,
PERSON_ID,
APPLICANT_FEE_TYPE,
APPLICANT_FEE_STATUS,
FEE_DATE,
FEE_PAYMENT_METHOD,
FEE_AMOUNT,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REFERENCE_NUM
)
VALUES
(
IGS_SS_APP_REQ_STG_S.NEXTVAL,
l_SS_ADM_APPL_ID,
l_PERSON_ID,
l_APPLICANT_FEE_TYPE,
l_APPLICANT_FEE_STATUS,
l_FEE_DATE,
l_FEE_PAYMENT_METHOD,
l_FEE_AMOUNT,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
l_REFERENCE_NUM
);
ROLLBACK TO INSERT_STG_FEE_REQ_DET_PUB;
ROLLBACK TO INSERT_STG_FEE_REQ_DET_PUB;
END INSERT_STG_FEE_REQ_DET;