[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cal_type , start_dt , end_dt
FROM igs_ca_inst
WHERE cal_type = cp_cal_type
AND sequence_number = cp_seq_num;
SELECT meaning
FROM igs_lookup_values
WHERE lookup_code = cp_code
AND lookup_type = 'YES_NO';
SELECT 'X'
FROM igs_pe_persid_group
WHERE group_id = cp_group_id
AND closed_ind = 'N'
AND TRUNC(create_dt) <= TRUNC(SYSDATE);
SELECT hz.party_number person_number,
nvl(pp.person_name, hz.party_name) full_name
FROM hz_parties hz, hz_person_profiles pp
WHERE hz.party_id = cp_n_person_id
AND pp.party_id = cp_n_person_id;
SELECT 'X'
FROM igs_ca_type t , igs_ca_inst i , igs_ca_stat s
WHERE t.closed_ind = 'N'
AND t.s_cal_cat = 'FEE'
AND t.cal_type = cp_cal_type
AND t.cal_type = i.cal_type
AND i.sequence_number = cp_ci_seq_num
AND i.cal_status = s.cal_status
AND s.s_cal_status = 'ACTIVE';
SELECT person_id
FROM igs_pe_prsid_grp_mem
WHERE group_id = cp_person_grp
AND (start_date <= TRUNC(SYSDATE) OR start_date IS NULL)
AND (end_date >= TRUNC(SYSDATE) OR end_date IS NULL)
ORDER BY 1;
SELECT party_id person_id
FROM hz_parties
WHERE party_id = cp_person_id
AND party_type IN ('ORGANIZATION','PERSON');
SELECT *
FROM igs_fi_inv_int
WHERE person_id = cp_person_id
AND fee_cal_type = cp_fee_cal_type
AND fee_ci_sequence_number = cp_fee_ci_seq_no
AND transaction_type <>'REFUND'
AND NVL(waiver_flag,'N') <> 'Y'
AND ((cp_fee_type1 IS NULL AND cp_fee_type2 IS NULL AND cp_fee_type3 IS NULL) OR
(cp_fee_type1 IS NOT NULL AND fee_type = cp_fee_type1) OR
(cp_fee_type2 IS NOT NULL AND fee_type = cp_fee_type2) OR
(cp_fee_type3 IS NOT NULL AND fee_type = cp_fee_type3)
);
SELECT *
FROM igs_fi_inv_wav_det_v
WHERE invoice_id = cp_inv_id
AND balance_type = cp_bal_type
AND ((end_dt IS NOT NULL AND end_dt > cp_rel_dt) OR (end_dt IS NULL));
/** For every charge found for a person , inserting a waiver record in the table
igs_fi_inv_wav_det if Test Mode is NO.
This record is created for an identified charge and the balance type passed in
the parameter p_c_bal_type **/
IF p_c_test_flag = 'Y' THEN
igs_fi_inv_wav_det_pkg.before_dml
(p_action => 'INSERT',
x_rowid => l_rowid,
x_invoice_id => l_rec_per_chg.invoice_id,
x_balance_type => p_c_bal_type,
x_start_dt => l_start_dt,
x_end_dt => l_end_dt
);
igs_fi_inv_wav_det_pkg.insert_row
(x_rowid => l_rowid,
x_invoice_id => l_rec_per_chg.invoice_id,
x_balance_type => p_c_bal_type,
x_start_dt => l_start_dt,
x_end_dt => l_end_dt,
x_mode => 'R'
) ;
If start date of the waiver record is greater than release date then delete the
waiver record.
If start date of waiver record is less than or equal to release date then
update the end date to release date.
Note : For releasing a waiver only those waiver records are considered whose
end date is either null or is greater than the release date.
This is so because waiver records with end date less than release date will
already be used for waiver before the release date **/
FOR l_chg_wav IN c_chg_wav(l_rec_per_chg.invoice_id,
p_c_bal_type,
l_release_dt)
LOOP
/** Logging message that for this charge the release is being done **/
/* Removed the logging of subaccount related information, as a part of Bug # 2564643 */
l_msg_str := RPAD(l_rec_per_chg.invoice_number,62) ||
RPAD(l_rec_per_chg.fee_type,12) ||
LPAD(TO_CHAR(l_rec_per_chg.invoice_amount),22);
igs_fi_inv_wav_det_pkg.delete_row
(x_rowid => l_chg_wav.row_id
);
igs_fi_inv_wav_det_pkg.update_row
(x_rowid => l_chg_wav.row_id,
x_invoice_id => l_chg_wav.invoice_id,
x_balance_type => l_chg_wav.balance_type,
x_start_dt => l_chg_wav.start_dt,
x_end_dt => l_release_dt,
x_mode => 'R'
) ;