[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
|| skharida 26-Jun-2006 Bug# 5208136 -Modified update_refund_rec, removed the obsoleted columns
|| from igs_fi_refunds_pkg.update_row.
|| abshriva 12-MAY-2006 Bug 5217319 Amount precision change in create_ap_int_rec
|| agairola 20-Feb-2006 Bug 5046245: Commenting out of the Vendor API
|| shtatiko 30-APR-2003 Enh# 2831569, Modified validate_parameters
*****************************************************************/
g_v_other CONSTANT VARCHAR2(10) := 'OTHER';
SELECT rfnd.rowid, rfnd.*
FROM igs_fi_refunds rfnd;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_n_party_id;
PROCEDURE update_refund_rec(p_r_rfnd_rec c_refunds%ROWTYPE) AS
/****************************************************************
|| Created By : [email protected]
|| Created On : 18-FEB-2003
|| Purpose : Updates the Refund transaction status
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_REFUNDS
|| (reverse chronological order - newest change first)
*****************************************************************/
BEGIN
igs_fi_refunds_pkg.update_row(x_rowid => p_r_rfnd_rec.rowid,
x_refund_id => p_r_rfnd_rec.refund_id,
x_voucher_date => p_r_rfnd_rec.voucher_date,
x_person_id => p_r_rfnd_rec.person_id,
x_pay_person_id => p_r_rfnd_rec.pay_person_id,
x_dr_gl_ccid => p_r_rfnd_rec.dr_gl_ccid,
x_cr_gl_ccid => p_r_rfnd_rec.cr_gl_ccid,
x_dr_account_cd => p_r_rfnd_rec.dr_account_cd,
x_cr_account_cd => p_r_rfnd_rec.cr_account_cd,
x_refund_amount => p_r_rfnd_rec.refund_amount,
x_fee_type => p_r_rfnd_rec.fee_type,
x_fee_cal_type => p_r_rfnd_rec.fee_cal_type,
x_fee_ci_sequence_number => p_r_rfnd_rec.fee_ci_sequence_number,
x_source_refund_id => p_r_rfnd_rec.source_refund_id,
x_invoice_id => p_r_rfnd_rec.invoice_id,
x_transfer_status => p_r_rfnd_rec.transfer_status,
x_reversal_ind => p_r_rfnd_rec.reversal_ind,
x_reason => p_r_rfnd_rec.reason,
x_attribute_category => p_r_rfnd_rec.attribute_category,
x_attribute1 => p_r_rfnd_rec.attribute1,
x_attribute2 => p_r_rfnd_rec.attribute2,
x_attribute3 => p_r_rfnd_rec.attribute3,
x_attribute4 => p_r_rfnd_rec.attribute4,
x_attribute5 => p_r_rfnd_rec.attribute5,
x_attribute6 => p_r_rfnd_rec.attribute6,
x_attribute7 => p_r_rfnd_rec.attribute7,
x_attribute8 => p_r_rfnd_rec.attribute8,
x_attribute9 => p_r_rfnd_rec.attribute9,
x_attribute10 => p_r_rfnd_rec.attribute10,
x_attribute11 => p_r_rfnd_rec.attribute11,
x_attribute12 => p_r_rfnd_rec.attribute12,
x_attribute13 => p_r_rfnd_rec.attribute13,
x_attribute14 => p_r_rfnd_rec.attribute14,
x_attribute15 => p_r_rfnd_rec.attribute15,
x_attribute16 => p_r_rfnd_rec.attribute16,
x_attribute17 => p_r_rfnd_rec.attribute17,
x_attribute18 => p_r_rfnd_rec.attribute18,
x_attribute19 => p_r_rfnd_rec.attribute19,
x_attribute20 => p_r_rfnd_rec.attribute20,
x_gl_date => p_r_rfnd_rec.gl_date,
x_reversal_gl_date => p_r_rfnd_rec.reversal_gl_date);
END update_refund_rec;
SELECT displayed_field
FROM ap_lookup_codes
WHERE lookup_type = cp_lookup_type
AND lookup_code = cp_lookup_code;
|| gmaheswa 19-nov-2003 Bug 3227107 address changes. Modified cursor c_hz_site to select dates
|| from igs_pe_hz_pty_sites instead of hz_party_sites.
|| (reverse chronological order - newest change first)
******************************************************************/
-- Cursor for selecting an Active Pay to Usage address for a party
CURSOR c_hz_site(cp_n_party_id hz_parties.party_id%TYPE) IS
SELECT ps.location_id
FROM hz_party_sites ps,
hz_party_site_uses psu,
igs_pe_hz_pty_sites ips
WHERE ps.party_site_id = ips.party_site_id(+)
AND ps.party_id = cp_n_party_id
AND (ps.status = g_v_ind_a AND
(SYSDATE BETWEEN NVL(ips.start_date,SYSDATE) AND NVL(ips.end_date,SYSDATE)) )
AND psu.party_site_id = ps.party_site_id
AND psu.site_use_type = g_v_pay_to
AND psu.status = g_v_ind_a;
SELECT ap_org_id, dflt_supplier_site_name
FROM igs_fi_control;
SELECT party_number
FROM hz_parties
WHERE party_id = cp_n_party_id;
SELECT group_cd, closed_ind
FROM igs_pe_all_persid_group_v
WHERE group_id = cp_n_pers_grp_id;
SELECT displayed_field meaning, inactive_date
FROM po_lookup_codes
WHERE lookup_type = cp_v_lookup_type
AND lookup_code = cp_v_lookup_code;
SELECT user_defined_vendor_num_code
FROM financials_system_params_all
WHERE ((org_id = g_n_org_id) OR (org_id IS NULL AND g_n_org_id IS NULL));
SELECT name,
start_date_active,
end_date_active
FROM ap_terms
WHERE term_id = cp_n_term_id;
SELECT multi_org_flag
FROM fnd_product_groups;
SELECT api_person_id
FROM igs_pe_alt_pers_id
WHERE pe_person_id = cp_n_party_id
AND person_id_type = g_v_sup_num
AND sysdate BETWEEN start_dt AND NVL(end_dt,sysdate);
SELECT a.rowid, a.*
FROM igs_fi_party_vendrs a
WHERE a.party_id = cp_n_party_id;
SELECT 'x'
FROM po_vendors
WHERE vendor_id = cp_n_vendor_id
AND TRUNC(SYSDATE) <= TRUNC(NVL(END_DATE_ACTIVE,sysdate));
SELECT 'x'
FROM po_vendor_sites_all po
WHERE ((po.org_id = g_n_org_id) OR (po.org_id IS NULL AND g_n_org_id IS NULL))
AND po.vendor_site_id = cp_vendor_site_id
AND TRUNC(SYSDATE) <= TRUNC(NVL(po.inactive_date, sysdate));
SELECT party_type,
party_number,
party_name,
person_first_name,
person_last_name
FROM hz_parties
WHERE party_id = cp_n_party_id;
SELECT substr (address1,1,35) address1,
substr (address2,1,35) address2,
substr (address3,1,35) address3,
substr (address4,1,35) address4,
substr (city,1,25) city,
substr (state,1,25) state,
substr (postal_code,1,20) postal_code,
substr (province,1,25) province,
substr (county,1,25) county,
substr (country,1,25) country
FROM hz_locations
WHERE location_id = cp_n_location_id;
ap_po_vendors_apis_pkg.update_vendor_site(p_vendor_site_code => NULL,
p_vendor_site_id => l_c_prty_rel.vendor_site_id,
p_address_line1 => l_c_hz_loc_addr.address1,
p_address_line2 => l_c_hz_loc_addr.address2,
p_address_line3 => l_c_hz_loc_addr.address3,
p_address_line4 => l_c_hz_loc_addr.address4,
p_city => l_c_hz_loc_addr.city,
p_state => l_c_hz_loc_addr.state,
p_zip => l_c_hz_loc_addr.postal_code,
p_province => l_c_hz_loc_addr.province,
p_county => l_c_hz_loc_addr.county,
p_country => l_c_hz_loc_addr.country,
p_area_code => null,
p_phone => null,
p_fax_area_code => null,
p_fax => null,
p_email_address => null,
x_status => l_v_site_status,
x_exception_msg => l_v_msg); */
ap_po_vendors_apis_pkg.insert_new_vendor_site(p_vendor_site_code => g_v_dflt_sup_site,
p_vendor_id => l_c_prty_rel.vendor_id,
p_org_id => g_n_org_id,
p_address_line1 => l_c_hz_loc_addr.address1,
p_address_line2 => l_c_hz_loc_addr.address2,
p_address_line3 => l_c_hz_loc_addr.address3,
p_address_line4 => l_c_hz_loc_addr.address4,
p_city => l_c_hz_loc_addr.city,
p_state => l_c_hz_loc_addr.state,
p_zip => l_c_hz_loc_addr.postal_code,
p_province => l_c_hz_loc_addr.province,
p_county => l_c_hz_loc_addr.county,
p_country => l_c_hz_loc_addr.country,
p_area_code => null,
p_phone => null,
p_fax_area_code => null,
p_fax => null,
p_email_address => null,
p_purchasing_site_flag => null,
p_pay_site_flag => 'Y',
p_rfq_only_site_flag => null,
x_vendor_site_id => l_n_vendor_site_id,
x_status => l_v_site_status,
x_exception_msg => l_v_msg);
igs_fi_party_vendrs_pkg.update_row(x_rowid => l_c_prty_rel.rowid,
x_party_id => l_c_prty_rel.party_id,
x_vendor_id => l_c_prty_rel.vendor_id,
x_vendor_site_id => l_n_vendor_site_id);
ap_po_vendors_apis_pkg.insert_new_vendor(p_vendor_name => l_v_vendor_name,
p_taxpayer_id => null,
p_tax_registration_id => null,
p_women_owned_flag => null,
p_small_business_flag => null,
p_minority_group_lookup_code => null,
p_vendor_type_lookup_code => g_v_supplier_type,
p_supplier_number => l_v_sup_num,
x_vendor_id => l_n_vendor_id,
x_status => l_v_vendor_status,
x_exception_msg => l_v_msg); */
ap_po_vendors_apis_pkg.insert_new_vendor_site(p_vendor_site_code => g_v_dflt_sup_site,
p_vendor_id => l_n_vendor_id,
p_org_id => g_n_org_id,
p_address_line1 => l_c_hz_loc_addr.address1,
p_address_line2 => l_c_hz_loc_addr.address2,
p_address_line3 => l_c_hz_loc_addr.address3,
p_address_line4 => l_c_hz_loc_addr.address4,
p_city => l_c_hz_loc_addr.city,
p_state => l_c_hz_loc_addr.state,
p_zip => l_c_hz_loc_addr.postal_code,
p_province => l_c_hz_loc_addr.province,
p_county => l_c_hz_loc_addr.county,
p_country => l_c_hz_loc_addr.country,
p_area_code => null,
p_phone => null,
p_fax_area_code => null,
p_fax => null,
p_email_address => null,
p_purchasing_site_flag => null,
p_pay_site_flag => 'Y',
p_rfq_only_site_flag => null,
x_vendor_site_id => l_n_vendor_site_id,
x_status => l_v_site_status,
x_exception_msg => l_v_msg); */
igs_fi_party_vendrs_pkg.insert_row(x_rowid => l_v_rowid,
x_party_id => p_n_party_id,
x_vendor_id => l_n_vendor_id,
x_vendor_site_id => l_n_vendor_site_id);
SELECT AP_INVOICES_INTERFACE_S.NEXTVAL
FROM dual;
INSERT INTO ap_invoices_interface(invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
terms_id,
source,
pay_group_lookup_code,
gl_date,
accts_pay_code_combination_id,
exclusive_payment_flag,
org_id,
terms_date)
VALUES(l_n_inv_id,
p_n_refund_id,
l_v_inv_lkp,
p_d_vchr_date,
p_n_vendor_id,
p_n_vendor_site_id,
l_n_rfnd_amt,
g_v_cur_code,
p_n_terms_id,
g_v_stdnt_system,
p_v_grp_code,
p_d_gl_date,
p_gl_cr_ccid,
g_v_pay_rfnd_vchr,
g_n_org_id,
p_d_vchr_date);
INSERT INTO ap_invoice_lines_interface(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
accounting_date,
amount,
dist_code_combination_id,
org_id)
VALUES(l_n_inv_id,
NULL,
1,
g_v_item,
p_d_gl_date,
l_n_rfnd_amt,
p_gl_dr_ccid,
g_n_org_id);
SELECT rfnd.rowid, rfnd.*
FROM igs_fi_refunds rfnd
WHERE rfnd.person_id = cp_n_party_id
AND rfnd.transfer_status = g_v_todo
AND rfnd.source_refund_id IS NOT NULL
ORDER BY rfnd.pay_person_id
FOR UPDATE NOWAIT;
SELECT rfnd.rowid, rfnd.*
FROM igs_fi_refunds rfnd
WHERE rfnd.refund_id = cp_n_refund_id
FOR UPDATE NOWAIT;
SELECT rfnd.rowid, rfnd.*
FROM igs_fi_refunds rfnd
WHERE rfnd.person_id = cp_n_party_id
AND rfnd.transfer_status = g_v_todo
AND rfnd.source_refund_id IS NULL
ORDER BY rfnd.pay_person_id
FOR UPDATE NOWAIT;
update_refund_rec(l_c_rfnd_rec);
update_refund_rec(l_c_rfnd_org);
update_refund_rec(l_c_rfnd_rec);
update_refund_rec(l_c_rfnd_rec);
SELECT DISTINCT person_id
FROM igs_fi_refunds
WHERE transfer_status = g_v_todo;