[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT credit_class credit_class_code
FROM igs_fi_credits_all crd, igs_fi_cr_types ct
WHERE crd.credit_id = cp_n_credit_id AND
ct.credit_type_id=crd.credit_type_id;
SELECT credit_number
FROM igs_fi_credits_all
WHERE credit_id = cp_n_credit_id;
SELECT rec_installed, rec_gl_ccid, rec_account_cd, accounting_method
FROM igs_fi_control_all;
SELECT a.invoice_id,
a.fee_type,
a.fee_cal_type,
a.fee_ci_sequence_number,
ci.start_dt,
ci.end_dt,
b.rec_gl_ccid inv_rec_gl_ccid,
b.rec_account_cd inv_rec_account_cd,
b.rev_gl_ccid inv_rev_gl_ccid,
b.rev_account_cd inv_rev_account_cd,
c.rec_gl_ccid ftci_rec_gl_ccid,
c.rec_account_cd ftci_rec_account_cd,
c.ret_gl_ccid ftci_ret_gl_ccid,
c.ret_account_cd ftci_ret_account_cd
FROM igs_fi_inv_int_all a,
igs_fi_invln_int_all b,
igs_fi_f_typ_ca_inst c,
igs_ca_inst ci
WHERE a.invoice_id = b.invoice_id
AND a.transaction_type = 'RETENTION'
AND b.error_account = 'Y'
AND a.fee_type = c.fee_type
AND a.fee_cal_type = c.fee_cal_type
AND a.fee_ci_sequence_number = c.fee_ci_sequence_number
And ci.cal_type=c.fee_cal_type
And ci.sequence_number = c.fee_ci_sequence_number
ORDER BY a.fee_type, a.fee_cal_type, a.fee_ci_sequence_number;
SELECT app.invoice_id ,
app.application_id ,
inv.fee_type ,
inv.fee_cal_type ,
inv.fee_ci_sequence_number ,
app.dr_gl_code_ccid app_dr_gl_code_ccid,
app.dr_account_cd app_dr_account_cd,
app.cr_gl_code_ccid app_cr_gl_code_ccid,
app.cr_account_cd app_cr_account_cd,
ftci.rec_gl_ccid ftci_rec_gl_ccid,
ftci.rec_account_cd ftci_rec_account_cd,
ftci.ret_gl_ccid ftci_ret_gl_ccid,
ftci.ret_account_cd ftci_ret_account_cd,
ci.start_dt,
ci.end_dt
FROM igs_fi_applications app,
igs_fi_inv_int_all inv,
igs_fi_f_typ_ca_inst ftci,
igs_ca_inst ci
WHERE app.invoice_id = inv.invoice_id
AND inv.transaction_type = 'RETENTION'
AND inv.fee_type = ftci.fee_type
AND inv.fee_cal_type = ftci.fee_cal_type
AND inv.fee_ci_sequence_number = ftci.fee_ci_sequence_number
AND ((app.dr_gl_code_ccid IS NULL AND app.dr_account_cd IS NULL)
OR (app.cr_gl_code_ccid IS NULL AND app.cr_account_cd IS NULL)
)
And ci.cal_type=ftci.fee_cal_type
And ci.sequence_number = ftci.fee_ci_sequence_number
ORDER BY inv.fee_type, inv.fee_cal_type, inv.fee_ci_sequence_number;
SELECT ROWID, inv.*
FROM igs_fi_invln_int_all inv
WHERE invoice_id = cp_n_invoice_id;
SELECT ROWID, app.*
FROM igs_fi_applications app
WHERE invoice_id = cp_n_invoice_id;
SELECT ROWID, crd.*
FROM igs_fi_cr_activities crd
WHERE credit_id = cp_n_credit_id;
SELECT rowid, accounted_cr, accounted_dr, code_combination_id
FROM gl_interface
WHERE reference23 = cp_v_ref_23
AND reference30 = cp_v_ref_30;
SELECT ROWID, post.*
FROM igs_fi_posting_int_all post
WHERE source_transaction_id = cp_n_source_tran_id
AND source_transaction_type = cp_v_source_tran_type;
ret_invoice_tab.DELETE;
igs_fi_invln_int_pkg.update_row( x_rowid => rec_invoice_details.rowid,
x_invoice_id => rec_invoice_details.invoice_id,
x_line_number => rec_invoice_details.line_number,
x_invoice_lines_id => rec_invoice_details.invoice_lines_id,
x_attribute2 => rec_invoice_details.attribute2,
x_chg_elements => rec_invoice_details.chg_elements,
x_amount => rec_invoice_details.amount,
x_unit_attempt_status => rec_invoice_details.unit_attempt_status,
x_eftsu => rec_invoice_details.eftsu,
x_credit_points => rec_invoice_details.credit_points,
x_attribute_category => rec_invoice_details.attribute_category,
x_attribute1 => rec_invoice_details.attribute1,
x_s_chg_method_type => rec_invoice_details.s_chg_method_type,
x_description => rec_invoice_details.description,
x_attribute3 => rec_invoice_details.attribute3,
x_attribute4 => rec_invoice_details.attribute4,
x_attribute5 => rec_invoice_details.attribute5,
x_attribute6 => rec_invoice_details.attribute6,
x_attribute7 => rec_invoice_details.attribute7,
x_attribute8 => rec_invoice_details.attribute8,
x_attribute9 => rec_invoice_details.attribute9,
x_attribute10 => rec_invoice_details.attribute10,
x_rec_account_cd => l_v_upd_rec_account_cd,
x_rev_account_cd => l_v_upd_rev_account_cd,
x_rec_gl_ccid => l_n_upd_rec_gl_ccid,
x_rev_gl_ccid => l_n_upd_rev_gl_ccid,
x_org_unit_cd => rec_invoice_details.org_unit_cd,
x_posting_id => rec_invoice_details.posting_id,
x_attribute11 => rec_invoice_details.attribute11,
x_attribute12 => rec_invoice_details.attribute12,
x_attribute13 => rec_invoice_details.attribute13,
x_attribute14 => rec_invoice_details.attribute14,
x_attribute15 => rec_invoice_details.attribute15,
x_attribute16 => rec_invoice_details.attribute16,
x_attribute17 => rec_invoice_details.attribute17,
x_attribute18 => rec_invoice_details.attribute18,
x_attribute19 => rec_invoice_details.attribute19,
x_attribute20 => rec_invoice_details.attribute20,
x_error_string => rec_invoice_details.error_string,
x_error_account => 'N',
x_location_cd => rec_invoice_details.location_cd,
x_uoo_id => rec_invoice_details.uoo_id,
x_gl_date => rec_invoice_details.gl_date,
x_gl_posted_date => rec_invoice_details.gl_posted_date,
x_posting_control_id => rec_invoice_details.posting_control_id,
x_mode => 'R',
x_unit_type_id => rec_invoice_details.unit_type_id,
x_unit_level => rec_invoice_details.unit_level
);
UPDATE gl_interface
SET code_combination_id = NVL(code_combination_id, l_n_upd_rec_gl_ccid)
WHERE rowid = rec_gl_interface.rowid;
UPDATE gl_interface
SET code_combination_id = NVL(code_combination_id, l_n_upd_rev_gl_ccid)
WHERE rowid = rec_gl_interface.rowid;
igs_fi_posting_int_pkg.update_row (
x_rowid => rec_posting_int.ROWID,
x_posting_control_id => rec_posting_int.posting_control_id,
x_posting_id => rec_posting_int.posting_id,
x_batch_name => rec_posting_int.batch_name,
x_accounting_date => rec_posting_int.accounting_date,
x_transaction_date => rec_posting_int.transaction_date,
x_currency_cd => rec_posting_int.currency_cd,
x_dr_account_cd => NVL(rec_posting_int.dr_account_cd, l_v_upd_rec_account_cd),
x_cr_account_cd => NVL(rec_posting_int.cr_account_cd, l_v_upd_rev_account_cd),
x_dr_gl_code_ccid => rec_posting_int.dr_gl_code_ccid,
x_cr_gl_code_ccid => rec_posting_int.cr_gl_code_ccid,
x_amount => rec_posting_int.amount,
x_source_transaction_id => rec_posting_int.source_transaction_id,
x_source_transaction_type => rec_posting_int.source_transaction_type,
x_status => rec_posting_int.status,
x_orig_appl_fee_ref => rec_posting_int.orig_appl_fee_ref,
x_mode => 'R');
igs_fi_applications_pkg.update_row(x_rowid => rec_application_details.rowid,
x_application_id => rec_application_details.application_id,
x_application_type => rec_application_details.application_type,
x_invoice_id => rec_application_details.invoice_id,
x_credit_id => rec_application_details.credit_id,
x_credit_activity_id => rec_application_details.credit_activity_id,
x_amount_applied => rec_application_details.amount_applied,
x_apply_date => rec_application_details.apply_date,
x_link_application_id => rec_application_details.link_application_id,
x_dr_account_cd => rec_application_details.dr_account_cd,
x_cr_account_cd => l_v_upd_cr_account_cd,
x_dr_gl_code_ccid => rec_application_details.dr_gl_code_ccid,
x_cr_gl_code_ccid => l_n_upd_cr_gl_code_ccid,
x_applied_invoice_lines_id => rec_application_details.applied_invoice_lines_id,
x_appl_hierarchy_id => rec_application_details.appl_hierarchy_id,
x_posting_id => rec_application_details.posting_id,
x_gl_date => rec_application_details.gl_date,
x_gl_posted_date => rec_application_details.gl_posted_date,
x_posting_control_id => rec_application_details.posting_control_id);
UPDATE gl_interface
SET code_combination_id = NVL(code_combination_id, l_n_upd_cr_gl_code_ccid)
WHERE rowid = rec_gl_interface.rowid;
UPDATE gl_interface
SET code_combination_id = NVL(code_combination_id, l_n_upd_cr_gl_code_ccid)
WHERE rowid = rec_gl_interface.rowid;
igs_fi_posting_int_pkg.update_row (
x_rowid => rec_posting_int.ROWID,
x_posting_control_id => rec_posting_int.posting_control_id,
x_posting_id => rec_posting_int.posting_id,
x_batch_name => rec_posting_int.batch_name,
x_accounting_date => rec_posting_int.accounting_date,
x_transaction_date => rec_posting_int.transaction_date,
x_currency_cd => rec_posting_int.currency_cd,
x_dr_account_cd => l_v_upd_dr_account_cd,
x_cr_account_cd => l_v_upd_cr_account_cd,
x_dr_gl_code_ccid => rec_posting_int.dr_gl_code_ccid,
x_cr_gl_code_ccid => rec_posting_int.cr_gl_code_ccid,
x_amount => rec_posting_int.amount,
x_source_transaction_id => rec_posting_int.source_transaction_id,
x_source_transaction_type => rec_posting_int.source_transaction_type,
x_status => rec_posting_int.status,
x_orig_appl_fee_ref => rec_posting_int.orig_appl_fee_ref,
x_mode => 'R');
igs_fi_cr_activities_pkg.update_row(x_rowid => rec_cr_act_details.rowid,
x_credit_activity_id => rec_cr_act_details.credit_activity_id,
x_credit_id => rec_cr_act_details.credit_id,
x_status => rec_cr_act_details.status,
x_transaction_date => rec_cr_act_details.transaction_date,
x_amount => rec_cr_act_details.amount,
x_dr_account_cd => l_v_upd_dr_account_cd,
x_cr_account_cd => rec_cr_act_details.cr_account_cd,
x_dr_gl_ccid => l_n_upd_dr_gl_ccid,
x_cr_gl_ccid => rec_cr_act_details.cr_gl_ccid,
x_bill_id => rec_cr_act_details.bill_id,
x_bill_number => rec_cr_act_details.bill_number,
x_bill_date => rec_cr_act_details.bill_date,
x_posting_id => rec_cr_act_details.posting_id,
x_posting_control_id => rec_cr_act_details.posting_control_id,
x_gl_date => rec_cr_act_details.gl_date,
x_gl_posted_date => rec_cr_act_details.gl_posted_date);
UPDATE gl_interface
SET code_combination_id = NVL(code_combination_id, l_n_upd_dr_gl_ccid)
WHERE rowid = rec_gl_interface.rowid;
igs_fi_posting_int_pkg.update_row (
x_rowid => rec_posting_int.ROWID,
x_posting_control_id => rec_posting_int.posting_control_id,
x_posting_id => rec_posting_int.posting_id,
x_batch_name => rec_posting_int.batch_name,
x_accounting_date => rec_posting_int.accounting_date,
x_transaction_date => rec_posting_int.transaction_date,
x_currency_cd => rec_posting_int.currency_cd,
x_dr_account_cd => NVL(rec_posting_int.dr_account_cd, l_v_upd_dr_account_cd),
x_cr_account_cd => rec_posting_int.cr_account_cd,
x_dr_gl_code_ccid => rec_posting_int.dr_gl_code_ccid,
x_cr_gl_code_ccid => rec_posting_int.cr_gl_code_ccid,
x_amount => rec_posting_int.amount,
x_source_transaction_id => rec_posting_int.source_transaction_id,
x_source_transaction_type => rec_posting_int.source_transaction_type,
x_status => rec_posting_int.status,
x_orig_appl_fee_ref => rec_posting_int.orig_appl_fee_ref,
x_mode => 'R');