[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_ind IN VARCHAR2,
p_deferred_payment_option IGS_FI_HECS_PAY_OPTN.HECS_PAYMENT_OPTION%TYPE ,
p_upfront_payment_option IGS_FI_HECS_PAY_OPTN.HECS_PAYMENT_OPTION%TYPE ,
p_creation_dt IN OUT NOCOPY IGS_GE_S_LOG.creation_dt%TYPE ,
p_hecs_payment_type OUT NOCOPY FND_LOOKUP_Values.LOOKUP_CODE%TYPE ,
p_message_name OUT NOCOPY VARCHAR2)
RETURN BOOLEAN AS
/*----------------------------------------------------------------------------
|| Created By :
|| Created On :
|| Purpose :
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
uudayapr 12-12-2003 bug#3080983 made the modification to Cursor c_fadv to point to the table
IGS_FI_FEE_AS instead IGS_FI_FEE_ASS_DEBT_V view.
|| vvutukur 19-Dec-2002 Bug#2680885.Commented out cursor c_fpv which selects from igs_fi_fee_pay_v, which
|| is to be dropped.Instead, cursor c_fpv is redefined selecting 0 from dual. This
|| function will be obsoleted as part of HECS functionality obsoletion. As this got
|| deferred for Jan'03 scope,commented out this portion.
|| vvutukur 30-Aug-2002 Bug#2531390.Removed default value of p_update_ind parameter to avoid
|| gscc warning.
----------------------------------------------------------------------------*/
gv_other_detail VARCHAR2(255);
SELECT IGS_EN_STDNTPSHECSOP.*, rowid
FROM IGS_EN_STDNTPSHECSOP
WHERE person_id = cp_person_id AND
course_cd = cp_course_cd AND
TRUNC(cp_effective_dt) >= TRUNC(start_dt) AND
TRUNC(cp_effective_dt) <= TRUNC(NVL(end_dt, cp_effective_dt))
FOR UPDATE OF end_dt NOWAIT;
SELECT HECS_PAYMENT_OPTION
FROM IGS_FI_HECS_PAY_OPTN hpo,
IGS_FI_GOV_HEC_PA_OP ghpo
WHERE hpo.HECS_PAYMENT_OPTION = cp_payment_option AND
hpo.closed_ind = 'N' AND
hpo.GOVT_HECS_PAYMENT_OPTION = ghpo.GOVT_HECS_PAYMENT_OPTION AND
ghpo.s_hecs_payment_type = cp_hecs_payment_type;
SELECT ghpo.s_hecs_payment_type
FROM IGS_FI_HECS_PAY_OPTN hpo,
IGS_FI_GOV_HEC_PA_OP ghpo
WHERE hpo.HECS_PAYMENT_OPTION = cp_payment_option AND
hpo.GOVT_HECS_PAYMENT_OPTION = ghpo.GOVT_HECS_PAYMENT_OPTION;
SELECT SUM(fadv.transaction_amount)
FROM IGS_FI_FEE_AS fadv,
IGS_FI_FEE_TYPE ft
WHERE fadv.person_id = p_person_id
AND fadv.fee_cal_type = p_fee_cal_type
AND fadv.fee_ci_sequence_number = p_fee_ci_sequence_number
AND ((fadv.FEE_CAT = p_fee_cat) OR (fadv.FEE_CAT IS NULL AND p_fee_cat IS NULL))
AND ((fadv.course_cd = p_course_cd ) OR (fadv.course_cd IS NULL AND p_course_cd IS NULL))
AND fadv.FEE_TYPE = ft.FEE_TYPE
AND ft.s_fee_type = 'HECS'
AND fadv.logical_delete_dt IS NULL
GROUP BY fadv.person_id,fadv.fee_cal_type,fadv.fee_ci_sequence_number
HAVING SUM(fadv.transaction_amount) >0;
SELECT 0
FROM dual;
SELECT SUM(fpv.payment_amount)
FROM IGS_FI_FEE_PAY_V fpv,
IGS_FI_FEE_TYPE ft
WHERE fpv.person_id = p_person_id AND
fpv.fee_cal_type = p_fee_cal_type AND
fpv.fee_ci_sequence_number = p_fee_ci_sequence_number AND
fpv.FEE_CAT = p_fee_cat AND
fpv.course_cd = p_course_cd AND
fpv.payment_amount > 0 AND
fpv.FEE_TYPE = ft.FEE_TYPE AND
ft.s_fee_type = 'HECS';*/
SELECT tax_file_number,
tax_file_number_collected_dt,
tax_file_certificate_number
FROM IGS_EN_STDNTPSHECSOP
WHERE person_id = p_person_id AND
course_cd = p_course_cd AND
start_dt > cp_end_dt AND
(tax_file_number IS NOT NULL AND
tax_file_invalid_dt IS NULL) OR
tax_file_certificate_number IS NOT NULL
ORDER BY start_dt;
p_update_ind IS NULL THEN
Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
IF p_update_ind = 'Y' THEN
-- Validate the deferred payment option parameter
IF p_deferred_payment_option IS NULL THEN
p_message_name:= 'IGS_AD_ADMPRD_DTALIAS_EXISTS';
IF p_update_ind = 'Y' THEN
IF TRUNC(v_scho_rec.start_dt) <> TRUNC(p_effective_dt) THEN
-- end the current IGS_EN_STDNTPSHECSOP entry
IGS_EN_STDNTPSHECSOP_Pkg.Update_Row (
x_rowid => v_scho_rec.rowid,
x_person_id => v_scho_rec.person_id,
x_course_cd => v_scho_rec.course_cd,
x_start_dt => v_scho_rec.start_dt,
x_end_dt => p_effective_dt - 1,
x_hecs_payment_option => v_scho_rec.hecs_payment_option,
x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
x_safety_net_ind => v_scho_rec.safety_net_ind,
x_tax_file_number => v_scho_rec.tax_file_number,
x_tax_file_number_collected_dt => v_scho_rec.tax_file_number_collected_dt,
x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
x_tax_file_certificate_number => v_scho_rec.tax_file_certificate_number,
x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
x_mode => 'R'
);
IGS_EN_STDNTPSHECSOP_Pkg.Insert_Row (
x_rowid => lv_rowid,
x_person_id => v_scho_rec.person_id,
x_course_cd => v_scho_rec.course_cd,
x_start_dt => v_effective_dt,
x_end_dt => v_scho_rec.end_dt,
x_hecs_payment_option => p_upfront_payment_option,
x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
x_diff_hecs_ind_update_who => Null,
x_diff_hecs_ind_update_on => Null,
x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
x_safety_net_ind => 'Y',
x_tax_file_number => v_tax_file_number,
x_tax_file_number_collected_dt => v_tax_file_number_collected_dt,
x_tax_file_invalid_dt => Null,
x_tax_file_certificate_number => v_tax_file_certificate_number,
x_diff_hecs_ind_update_comment => Null,
x_mode => 'R'
);
IGS_EN_STDNTPSHECSOP_Pkg.Update_Row (
x_rowid => v_scho_rec.rowid,
x_person_id => v_scho_rec.person_id,
x_course_cd => v_scho_rec.course_cd,
x_start_dt => v_scho_rec.start_dt,
x_end_dt => v_scho_rec.end_dt,
x_hecs_payment_option => p_deferred_payment_option,
x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
x_safety_net_ind => 'N',
x_tax_file_number => v_tax_file_number,
x_tax_file_number_collected_dt => v_tax_file_number_collected_dt,
x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
x_tax_file_certificate_number => v_tax_file_certificate_number,
x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
x_mode => 'R'
);
IF p_update_ind = 'Y' THEN
IF TRUNC(v_scho_rec.start_dt) <> TRUNC(p_effective_dt) THEN
-- End the current student IGS_PS_COURSE hecs option entry
IGS_EN_STDNTPSHECSOP_Pkg.Update_Row (
x_rowid => v_scho_rec.rowid,
x_person_id => v_scho_rec.person_id,
x_course_cd => v_scho_rec.course_cd,
x_start_dt => v_scho_rec.start_dt,
x_end_dt => p_effective_dt - 1,
x_hecs_payment_option => v_scho_rec.hecs_payment_option,
x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
x_safety_net_ind => v_scho_rec.safety_net_ind,
x_tax_file_number => v_scho_rec.tax_file_number,
x_tax_file_number_collected_dt => v_scho_rec.tax_file_number_collected_dt,
x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
x_tax_file_certificate_number => v_scho_rec.tax_file_certificate_number,
x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
x_mode => 'R'
);
IGS_EN_STDNTPSHECSOP_Pkg.Insert_Row (
x_rowid => lv_rowid,
x_person_id => v_scho_rec.person_id,
x_course_cd => v_scho_rec.course_cd,
x_start_dt => v_effective_dt,
x_end_dt => v_scho_rec.end_dt,
x_hecs_payment_option => p_upfront_payment_option,
x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
x_diff_hecs_ind_update_who => Null,
x_diff_hecs_ind_update_on => Null,
x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
x_safety_net_ind => 'Y',
x_tax_file_number => v_tax_file_number,
x_tax_file_number_collected_dt => v_tax_file_number_collected_dt,
x_tax_file_invalid_dt => Null,
x_tax_file_certificate_number => v_tax_file_certificate_number,
x_diff_hecs_ind_update_comment => Null,
x_mode => 'R'
);
IGS_EN_STDNTPSHECSOP_Pkg.Update_Row (
x_rowid => v_scho_rec.rowid,
x_person_id => v_scho_rec.person_id,
x_course_cd => v_scho_rec.course_cd,
x_start_dt => v_scho_rec.start_dt,
x_end_dt => v_scho_rec.end_dt,
x_hecs_payment_option => p_upfront_payment_option,
x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
x_safety_net_ind => 'Y',
x_tax_file_number => v_scho_rec.tax_file_number,
x_tax_file_number_collected_dt => v_scho_rec.tax_file_number_collected_dt,
x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
x_tax_file_certificate_number => v_scho_rec.tax_file_certificate_number,
x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
x_mode => 'R'
);
SELECT s_person_id_type
FROM igs_pe_person_id_typ
WHERE person_id_type = cp_c_usr_alt_prs_id_typ AND
NVL(unique_ind,'N') = cp_c_unique AND
closed_ind = 'N';