[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT arps.customer_trx_id,ract.trx_date
INTO cust_trx_id,trans_date
FROM ar_payment_schedules arps, ra_customer_trx ract
WHERE arps.payment_schedule_id = pay_sched_id
AND ract.customer_trx_id = arps.customer_trx_id
AND rownum = row_number;
SELECT 'Y' approved_yes_no
INTO approved_amt
FROM ar_approval_user_limits araul,
gl_sets_of_books glsb,
ar_system_parameters arsp
WHERE araul.user_id = userid
AND araul.document_type = 'ADJ'
AND glsb.set_of_books_id = arsp.set_of_books_id
AND araul.currency_code = glsb.currency_code
AND araul.amount_to >= NVL(amt,0)
AND araul.amount_from <= NVL(amt,0)
AND rownum = row_number;
SELECT writeoff_perc_tolerance,
writeoff_amount_tolerance,
interest_writeoff_rectrx_id,
interest_writeoff_ccid,
interest_revenue_rectrx_id,
interest_revenue_ccid,
calculated_interest_ccid,
calculated_interest_rectrx_id
INTO perc_tol, amt_tol, writeoff_rectrx,
writeoff_ccid, rev_rectrx, rev_ccid,
calc_intr_ccid, calc_intr_rectx_id
FROM jl_br_ar_rec_met_accts_ext
WHERE receipt_method_id = rcpt_mthd
AND bank_acct_use_id = bnk_acct
AND rownum = row_number;
SELECT Sum(Amount)
INTO amount_adjusted
FROM ar_adjustments
WHERE payment_schedule_id = pay_sched_id;
SELECT global_attribute17, global_attribute16, global_attribute18
INTO trx_type, batch_source, receipt_method
FROM ar_system_parameters
WHERE rownum = row_number;
SELECT MAX (global_attribute7)
INTO interest_payment_date
FROM ar_payment_schedules
WHERE payment_schedule_id = pay_schd_id;
SELECT SUBSTR (global_attribute1, 1, 15),
fnd_number.canonical_to_number(SUBSTR (global_attribute2, 1, 38)),
fnd_number.canonical_to_number(SUBSTR (global_attribute3, 1, 15)),
SUBSTR (global_attribute4, 1, 30),
fnd_number.canonical_to_number(SUBSTR (global_attribute5, 1, 4)),
SUBSTR (global_attribute6, 1, 15),
fnd_number.canonical_to_number(SUBSTR (global_attribute7, 1, 38))
INTO interest_type, interest_rate_amount, interest_period, interest_formula,
interest_grace_days, penalty_type, penalty_rate_amount
FROM ra_customer_trx
WHERE customer_trx_id = cust_trx_id
AND rownum = row_number;
SELECT loc.city
INTO city
FROM ar_payment_schedules arps,
hz_cust_acct_sites ad,
hz_cust_site_uses hzsu,
ra_customer_trx ract,
-- ra_site_uses rasu,
hz_locations loc,
hz_party_sites pty
WHERE arps.payment_schedule_id = pay_sched_id
AND ract.customer_trx_id = arps.customer_trx_id
AND hzsu.site_use_id = ract.bill_to_site_use_id
AND ad.cust_acct_site_id = hzsu.cust_acct_site_id
AND ad.party_site_id = pty.party_site_id
AND loc.location_id = pty.location_id
AND rownum = row_number;
SELECT COUNT (*)
INTO tot_rec
FROM ar_receivable_applications
WHERE cash_receipt_id = cash_rcpt_id;
SELECT DECODE (amt_due_remain_char, '0', 'CL', 'OP')
INTO status
FROM dual
WHERE rownum = row_number;
SELECT DECODE (amt_due_remain_char, '0', gl_date, NULL)
INTO gl_date_closed
FROM dual
WHERE rownum = row_number;
SELECT DECODE (amt_due_remain_char, '0', gl_date, NULL)
INTO actual_date_closed
FROM dual
WHERE rownum = row_number;
SELECT COUNT (ext.receipt_method_id)
INTO total_rec
FROM jl_br_ar_rec_met_accts_ext ext,
ar_receipt_methods arm
WHERE arm.receipt_method_id = ext.receipt_method_id
AND arm.receipt_class_id = rcpt_class_id;
SELECT global_attribute1
INTO collection_method
FROM ar_receipt_classes
WHERE receipt_class_id = rcpt_class_id
AND rownum = row_number;
SELECT global_attribute12
INTO print_immediately_flag
FROM ar_system_parameters
WHERE rownum = row_number;
SELECT COUNT (rct.complete_flag)
INTO total_records
FROM ra_customer_trx rct, ra_cust_trx_types rctt
WHERE rct.complete_flag = 'Y'
AND NVL (rct.printing_count,0) = 0
AND NVL (rct.status_trx, 'VD') <> 'VD'
AND rctt.cust_trx_type_id = rct.cust_trx_type_id
AND rctt.type = 'INV';
SELECT type, default_status
INTO class, dfstatus
FROM ra_cust_trx_types_all
WHERE cust_trx_type_id = p_cust_trx_type_id
AND rownum = row_number;
SELECT fcc.meaning,
tn.meaning,
mtl.global_attribute3,
mtl.global_attribute4,
mtl.global_attribute5,
mtl.global_attribute6
INTO fcc_code,
tran_nat,
item_org,
item_ft,
fed_trib,
sta_trib
FROM mtl_system_items mtl,fnd_lookups fcc , fnd_lookups tn
WHERE fcc.lookup_code = SUBSTR(mtl.global_attribute1,1,25)
AND fcc.lookup_type = fcc_code_type
AND tn.lookup_code = substr(mtl.global_attribute2,1,25)
AND tn.lookup_type = tran_nat_type
AND mtl.organization_id = so_org_id
AND mtl.inventory_item_id = inv_item_id
AND rownum = row_number;
SELECT aml.global_attribute3,
aml.global_attribute4,
aml.global_attribute5,
aml.global_attribute6
INTO item_org,
item_ft,
fed_trib,
sta_trib
FROM ar_memo_lines aml
WHERE aml.memo_line_id = p_memo_line_id
AND rownum = row_number;
'select '||
seq_name||'.nextval seq_number '||
'from dual ',
dbms_sql.NATIVE);
SELECT global_attribute2
INTO bearer_tr_note
FROM ar_payment_schedules
WHERE payment_schedule_id = pay_sched_id
AND rownum = row_number;
SELECT acpc.global_attribute3,
acpc.global_attribute4,
acpc.global_attribute5,
acpc.global_attribute6,
acpc.global_attribute7,
acpc.global_attribute8,
acpc.global_attribute9
INTO interest_type,
interest_rate_amt,
interest_period,
interest_formula,
interest_grace,
penalty_type,
penalty_rate_amt
FROM hz_cust_profile_classes acpc,
hz_customer_profiles acp
WHERE acp.cust_account_id = bill_to_cust_id
AND acp.profile_class_id = acpc.profile_class_id
AND acp.site_use_id is null
AND rownum = row_number;
SELECT global_attribute1
INTO batch_id
FROM ra_batch_sources
WHERE batch_source_id = p_batch_source_id
AND rownum = row_number;
SELECT TO_NUMBER (global_attribute11),
TO_NUMBER (global_attribute12)
INTO base_amt,
base_rate
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = cust_trx_id
AND rownum = row_number;
SELECT global_attribute8
INTO iss_date
FROM ra_customer_trx
WHERE customer_trx_id = cust_trx_id
AND rownum = row_number;
SELECT rct.status_trx,
rctt.type
INTO status,
typ_class
FROM ra_customer_trx_all rct,
ra_cust_trx_types_all rctt
WHERE rct.cust_trx_type_id = rctt.cust_trx_type_id
AND rct.customer_trx_id = cust_trx_id
AND rownum = row_number;
SELECT class
INTO p_class
FROM ar_payment_schedules
WHERE trx_number = p_trx_number;
SELECT MAX(apply_date)
INTO x_apply_date
FROM ar_receivable_applications
WHERE applied_payment_schedule_id = p_applied_payment_schedule_id
AND application_type = 'CASH'
AND status = 'APP'
AND confirmed_flag = 'Y'
AND reversal_gl_date IS NULL
AND apply_date < p_apply_date
AND cash_receipt_id <> p_cash_receipt_id;
SELECT global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8
INTO p_main_amnt_rec,
p_base_int_calc,
p_calculated_interest,
p_received_interest,
p_int_diff_action,
p_int_writeoff_reason,
p_payment_date,
p_writeoff_date
FROM ar_receivable_applications
WHERE applied_payment_schedule_id = p_applied_payment_schedule_id
AND application_type = 'CASH'
AND status = 'APP'
AND confirmed_flag = 'Y'
AND reversal_gl_date IS NULL
AND apply_date = x_apply_date;
SELECT applied_payment_schedule_id,
nvl(global_attribute3,0) calculated_interest,
nvl(global_attribute4,0) received_interest,
apply_date
FROM ar_receivable_applications
where cash_receipt_id = p_cash_receipt_id;
SELECT 1
INTO revcode
FROM ar_receivable_applications
WHERE applied_payment_schedule_id = ps_rec.applied_payment_schedule_id
AND status = 'APP'
AND cash_receipt_id <> p_cash_receipt_id
AND apply_date between ps_rec.apply_date and sysdate
AND reversal_gl_date IS NULL;
SELECT 1
INTO revcode
FROM ar_adjustments
WHERE payment_schedule_id = ps_rec.applied_payment_schedule_id
AND associated_cash_receipt_id = p_cash_receipt_id
AND gl_posted_date IS NULL;
SELECT p_user_id,
sysdate,
p_user_id,
p_user_id,
sysdate,
p_amount,
sysdate,
p_receipt_date,
arsp.set_of_books_id,
'CHARGES',
decode( p_status,'Y','A','M'),
p_status ,
p_cash_receipt_id,
p_customer_trx_id,
p_pay_sched_id,
p_rectrx_id,
'ARXRWMAI',
decode( p_status ,'Y','Y','N'),
decode( p_status ,'Y',p_user_id,NULL),
-3,
p_amount
INTO p_adj_rec.LAST_UPDATED_BY,
p_adj_rec.LAST_UPDATE_DATE,
p_adj_rec.LAST_UPDATE_LOGIN,
p_adj_rec.CREATED_BY,
p_adj_rec.CREATION_DATE,
p_adj_rec.AMOUNT,
p_adj_rec.APPLY_DATE,
p_adj_rec.GL_DATE,
p_adj_rec.SET_OF_BOOKS_ID,
p_adj_rec.TYPE,
p_adj_rec.ADJUSTMENT_TYPE,
p_adj_rec.STATUS,
p_adj_rec.ASSOCIATED_CASH_RECEIPT_ID,
p_adj_rec.CUSTOMER_TRX_ID,
p_adj_rec.PAYMENT_SCHEDULE_ID,
p_adj_rec.RECEIVABLES_TRX_ID,
p_adj_rec.CREATED_FROM,
p_adj_rec.POSTABLE,
p_adj_rec.APPROVED_BY,
p_adj_rec.POSTING_CONTROL_ID,
p_adj_rec.ACCTD_AMOUNT
FROM ar_system_parameters arsp;
SELECT count(*)
INTO p_warehouse_count
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND line_type = 'LINE'
AND warehouse_id IS NULL
AND inventory_item_id IS NOT NULL;
SELECT distinct warehouse_id
INTO p_warehouse_id
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND line_type = 'LINE'
AND inventory_item_id IS NOT NULL
AND rownum = 1;
SELECT cust_trx_type_id
INTO p_void_trx_type_id
FROM ra_cust_trx_types ct
WHERE ct.global_attribute_category = l_category
AND ct.global_attribute6 = 'Y';
SELECT loc.city,
loc.state --Bug 2319552
INTO city,
state --Bug 2319552
FROM ar_payment_schedules arps,
hz_cust_acct_sites ad,
hz_cust_site_uses hzsu,
ra_customer_trx ract,
--ra_site_uses rasu,
hz_locations loc,
hz_party_sites pty
WHERE arps.payment_schedule_id = pay_sched_id
AND ract.customer_trx_id = arps.customer_trx_id
AND hzsu.site_use_id = ract.bill_to_site_use_id
AND ad.cust_acct_site_id = hzsu.cust_acct_site_id
AND ad.party_site_id = pty.party_site_id
AND loc.location_id = pty.location_id
AND rownum = row_number;
PROCEDURE update_doc_status(p_cash_receipt_id IN NUMBER) IS
Cursor c1(p_rec_id NUMBER) is SELECT receivable_application_id, global_attribute12
FROM ar_receivable_applications_all
WHERE cash_receipt_id = p_rec_id;
update jl_br_ar_collection_docs_all set document_status = 'SELECTED'
where document_id = to_number(rec.global_attribute12);
END update_doc_status;