[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
customer_trx_id,
to_number(interface_line_attribute10||interface_line_attribute11) lsm_id,
xsi.id xsi_id,
xls.tld_id tld_id,
xls.til_id til_id,
cnr.id cnr_id,
lln.id lln_id,
cnr.consolidated_invoice_number,
sty.name sty_name,
lsm.amount lsm_amount
FROM ra_customer_trx_lines_all trx,
okl_cnsld_ar_strms_b lsm,
okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_hdrs_b cnr,
okl_xtl_sell_invs_b xls,
okl_ext_sell_invs_b xsi,
okl_strm_type_v sty
WHERE trx.line_type <> 'TAX'
AND trx.interface_line_context = 'OKL_CONTRACTS'
AND (trx.interface_line_attribute10||trx.interface_line_attribute11)
= to_char(lsm.id)
AND lln.cnr_id = cnr.id
AND lln.id = lsm.lln_id
AND lsm.receivables_invoice_id = -99999
AND xls.lsm_id = lsm.id
AND xsi.id = xls.xsi_id_details
AND xsi.trx_status_code = 'PROCESSED'
AND sty.id = lsm.sty_id
AND cnr.org_id = trx.org_id
;
SELECT due_date, trx.trx_number
FROM ar_payment_schedules_all ps,
ra_customer_trx_all trx
WHERE ps.customer_trx_id = p_cust_trx_id
AND trx.customer_trx_id = ps.customer_trx_id;
SELECT SUM(NVL( extended_amount ,0))
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_cust_trx_id AND
LINE_TYPE = 'TAX';
lx_last_updated_by okl_ext_sell_invs_v.last_updated_by%TYPE := Fnd_Global.USER_ID;
lx_last_update_login okl_ext_sell_invs_v.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
customer_trx_id_tbl.delete;
lsm_id_tbl.delete;
xsi_id_tbl.delete;
tld_id_tbl.delete;
til_id_tbl.delete;
cnr_id_tbl.delete;
lln_id_tbl.delete;
cons_inv.delete;
sty_name.delete;
lsm_amount_tbl.delete;
tax_amount_tbl.delete;
update okl_cnsld_ar_hdrs_b
set amount = 0
where id = cnr_id_tbl(indx);
update okl_cnsld_ar_hdrs_b
set amount = amount + nvl(lsm_amount_tbl(indx),0) + nvl( tax_amount_tbl(indx), 0),
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
where id = cnr_id_tbl(indx);
update okl_cnsld_ar_lines_b
set tax_amount = nvl(tax_amount,0)+ nvl(tax_amount_tbl(indx),0),
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
where id = lln_id_tbl(indx);
update okl_cnsld_ar_strms_b
set tax_amount = nvl(tax_amount_tbl(indx),0),
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
where id = lsm_id_tbl(indx);
end if; -- Update Tax amounts
due_date_tbl.delete;
trx_number_tbl.delete;
UPDATE okl_cnsld_ar_hdrs_b
SET due_date = due_date_tbl(indx),
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
where id = cnr_id_tbl(indx);
update okl_ext_sell_invs_b
set receivables_invoice_id = customer_trx_id_tbl(indx),
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
where id = xsi_id_tbl(indx);
update okl_txd_ar_ln_dtls_b
set receivables_invoice_id = customer_trx_id_tbl(indx),
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
where id = tld_id_tbl(indx);
update okl_txl_ar_inv_lns_b
set receivables_invoice_id = customer_trx_id_tbl(indx),
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
where id = til_id_tbl(indx);
update okl_cnsld_ar_strms_b
set receivables_invoice_id = customer_trx_id_tbl(indx),
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_application_id = lx_program_application_id,
program_id = lx_program_id
where id = lsm_id_tbl(indx);