The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(sum(nvl(amount,0)),0)
from ar_adjustments
where customer_trx_id in (select customer_trx_id
from ra_customer_trx,
fv_finance_charge_controls fcc
where related_customer_trx_id = v_process_inv_id
and cust_trx_type_id in (select cust_trx_type_id
from ra_cust_trx_types
where type = 'DM')
and interface_header_attribute3 = fcc.charge_type
and fcc.set_of_books_id = v_sob_id)
and set_of_books_id = v_sob_id
and status = 'A'
and receivables_trx_id = v_rec_activity
and apply_date between v_begin_date and v_end_date;
select nvl(sum(nvl(amount,0)),0) sum_adjustments,
nvl(related_customer_trx_id, aa.customer_trx_id) id,
bill_to_customer_id
from ar_adjustments aa,
ra_customer_trx rct
where (aa.customer_trx_id = rct.customer_trx_id
or aa.customer_trx_id in (select customer_trx_id
from ra_customer_trx
where related_customer_trx_id = aa.customer_trx_id))
and aa.set_of_books_id = p_sob_id
and aa.status = 'A'
and aa.receivables_trx_id = v_rec_activity
and aa.apply_date between v_begin_date and v_end_date
group by nvl(related_customer_trx_id, aa.customer_trx_id),
bill_to_customer_id;
DELETE from fv_1099c
WHERE set_of_books_id = v_sob_id;
SELECT to_date('01/01/'|| v_year, 'MM/DD/YYYY')
INTO v_begin_date
FROM DUAL;
SELECT to_date('12/31/'|| v_year, 'MM/DD/YYYY')
INTO v_end_date
FROM DUAL;
SELECT hzp.party_name,hzp.tax_reference
INTO v_cust_name, v_tax_ref
FROM hz_parties hzp, hz_cust_accounts hzca
WHERE hzca.cust_account_id = v_customer_id
AND hzca.party_id = hzp.party_id;
SELECT address1, address2, address3, address4, city, state,
postal_code, country, province
INTO v_address1, v_address2, v_address3, v_address4, v_city,
v_state, v_postal_code, v_country, v_province
FROM hz_locations hzl, hz_cust_acct_sites hzcas, hz_party_sites hzps
WHERE hzcas.cust_account_id = v_customer_id
AND hzcas.party_site_id = hzps.party_site_id
AND hzps.location_id = hzl.location_id;
SELECT trx_number
INTO v_trx_number
FROM ra_customer_trx
WHERE customer_trx_id = v_process_inv_id;
SELECT max(apply_date)
INTO v_apply_date
FROM ar_adjustments
WHERE customer_trx_id = v_process_inv_id;
SELECT max(apply_date)
INTO v_apply_date
FROM ar_adjustments
WHERE customer_trx_id in (select customer_trx_id
from ra_customer_trx
where related_customer_trx_id =
v_process_inv_id)
AND set_of_books_id = v_sob_id
AND apply_date between v_begin_date and v_end_date
AND receivables_trx_id = v_rec_activity;
INSERT INTO fv_1099c
(set_of_books_id,
creditors_tin,
customer_id,
date_canceled,
customer_name,
tax_id,
trx_number,
amount,
finance_charge_amount,
address1,
address2,
address3,
address4,
city,
state,
postal_code,
province,
country,
reportable_flag,
org_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login )
values
(v_sob_id,
v_creditors_tin,
v_customer_id,
v_apply_date,
v_cust_name,
v_tax_ref,
v_trx_number,
decode(v_include_charges,'Y',(v_adjustment_rec.sum_adjustments*-1),
((v_adjustment_rec.sum_adjustments - v_debit_memo_sum)*-1)),
decode(v_include_charges,'Y',(v_debit_memo_sum*-1),0),
v_address1,
v_address2,
v_address3,
v_address4,
v_city,
v_state,
v_postal_code,
v_province,
v_country,
'Y',
v_org_id,
SYSDATE,
v_user_id,
SYSDATE,
v_user_id,
v_login_id );