[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sum(amount)
INTO l_amount
FROM ar_adjustments adj,
ra_cust_trx_types commit_ctt,
ra_customer_trx commit_trx
WHERE commit_ctt.cust_trx_type_id = commit_trx.cust_trx_type_id
AND commit_trx.customer_trx_id = p_commit_ct_id
AND commit_ctt.type = 'DEP'
AND adj.customer_trx_id = p_ct_id
AND adj.adjustment_type = 'C';
SELECT sum(amount), sum(NVL(line_adjusted,0)),
sum(NVL(tax_adjusted,0)), sum(NVL(freight_adjusted,0))
INTO p_amount,
p_line_amount,
p_tax_amount,
p_freight_amount
FROM ar_adjustments adj,
ra_cust_trx_types commit_ctt,
ra_customer_trx commit_trx
WHERE commit_ctt.cust_trx_type_id = commit_trx.cust_trx_type_id
AND commit_trx.customer_trx_id = p_commit_ct_id
AND commit_ctt.type = 'DEP'
AND adj.customer_trx_id = p_ct_id
AND adj.adjustment_type = 'C';
SELECT count(*)
INTO l_num_installments
FROM ar_payment_schedules ps
WHERE ps.customer_trx_id = p_ct_id;
SELECT count(decode(ctl.line_type,
'LINE', 1,
'CHARGES', 1,
'CB', 1,
null)),
count(decode(ctl.line_type,
'TAX', 1,
null)),
count(decode(ctl.line_type,
'FREIGHT', 1,
null))
INTO l_num_line_lines,
l_num_tax_lines,
l_num_frt_lines
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = p_ct_id;
select decode(nvl(sum(ps.amount_applied), 0),
0, 'N',
'Y')
into p_payment_exist_flag
from ar_payment_schedules ps
where customer_trx_id = p_ct_id;
SELECT sum(decode(ctl.line_type,
'LINE', ctl.extended_amount,
'CB', ctl.extended_amount,
'CHARGES', ctl.extended_amount,
null)),
sum(decode(ctl.line_type,
'TAX', ctl.extended_amount,
null)),
sum(decode(ctl.line_type,
'FREIGHT', ctl.extended_amount,
null)),
count(decode(ctl.line_type,
'LINE', 1,
'CB', 1,
'CHARGES', 1,
null)),
count(decode(ctl.line_type,
'TAX', 1,
null)),
count(decode(ctl.line_type,
'FREIGHT', 1,
null))
INTO l_cm_line_amount,
l_cm_tax_amount,
l_cm_frt_amount,
l_num_line_lines,
l_num_tax_lines,
l_num_frt_lines
FROM ra_customer_trx_lines ctl
WHERE ctl.customer_trx_id = p_ct_id;
SELECT
decode(count(*),
0, null,
1, substrb(min(decode(nvl(site_uses.primary_flag,'N'),
'Y','1',
'N','2')||to_char(site_uses.site_use_id)),
2),
decode(substrb(min(decode(nvl(site_uses.primary_flag,'N'),
'Y','1',
'N','2')||to_char(site_uses.site_use_id)),
1,1),
'1', substrb(min(decode(nvl(site_uses.primary_flag,'N'),
'Y','1',
'N','2')||to_char(site_uses.site_use_id)),
2),
null))
INTO l_parent_site_use_id
FROM hz_cust_site_uses site_uses,
hz_cust_acct_sites acct_site
WHERE site_uses.site_use_code = 'BILL_TO'
and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
and acct_site.cust_account_id = p_parent_customer_id;
SELECT cr.cust_account_id
INTO l_parent_customer_id
FROM hz_cust_acct_relate cr
WHERE cr.related_cust_account_id = p_bill_to_customer_id
AND cr.status = 'A'
AND cr.bill_to_flag = 'Y'
AND nvl(cr.customer_reciprocal_flag,'N') = 'N';
SELECT arm.name,
arm.receipt_method_id,
arc.creation_method_code,
arm.name,
arm.receipt_method_id,
arc.creation_method_code
INTO l_payment_method_name,
l_receipt_method_id,
l_creation_method_code,
p_payment_method_name,
p_receipt_method_id,
p_creation_method_code
FROM ar_receipt_methods arm,
ra_cust_receipt_methods rcrm,
ar_receipt_method_accounts arma,
ce_bank_accounts cba,
ce_bank_acct_uses aba,
ar_receipt_classes arc,
ce_bank_branches_v bp /*Bug3348454*/
WHERE arm.receipt_method_id = rcrm.receipt_method_id
AND arm.receipt_method_id = arma.receipt_method_id
AND arm.receipt_class_id = arc.receipt_class_id
AND arma.remit_bank_acct_use_id = aba.bank_acct_use_id
AND aba.bank_account_id = cba.bank_account_id
/*Bug3348454*/
AND cba.bank_branch_id = bp.branch_party_id
AND p_trx_date <= NVL(bp.end_date,p_trx_date)
/*Bug3348454*/
-- AND aba.set_of_books_id = pg_set_of_books_id
AND arm.receipt_method_id = p_crtrx_receipt_method_id
AND
(
( rcrm.customer_id = p_customer_id
AND
NVL(rcrm.site_use_id,
p_site_use_id) = p_site_use_id
)
OR
( rcrm.customer_id = nvl(p_parent_customer_id,
-88888)
AND
nvl(rcrm.site_use_id,
nvl(p_parent_site_use_id,
-88888)) = nvl(p_parent_site_use_id,
-88888)
)
)
AND (
cba.currency_code =
p_currency_code OR
cba.receipt_multi_currency_flag = 'Y'
)
-- AND aba.set_of_books_id = pg_set_of_books_id
/*Bug3348454*/
/*AND TRUNC(nvl(aba.end_date,
p_trx_date)) >=
TRUNC(p_trx_date)*/
AND TRUNC(nvl(cba.end_date,p_trx_date+1)) > TRUNC(p_trx_date)
AND p_trx_date between
TRUNC(nvl(
arm.start_date,
p_trx_date))
and TRUNC(nvl(
arm.end_date,
p_trx_date))
AND p_trx_date between
TRUNC(nvl(
rcrm.start_date,
p_trx_date))
and TRUNC(nvl(
rcrm.end_date,
p_trx_date))
AND p_trx_date between
TRUNC(arma.start_date)
and TRUNC(nvl(
arma.end_date,
p_trx_date))
AND rownum = 1;
Select 'Y' Into l_dummy
from ra_cust_trx_types cmctt
where cmctt.cust_trx_type_id = p_cm_trx_type_id
and
cmctt.accounting_affect_flag = nvl(p_inv_open_rec_flag,cmctt.accounting_affect_flag) and
cmctt.post_to_gl = (select post_to_gl from ra_cust_trx_types invctt
where invctt.cust_trx_type_id = p_inv_trx_type_id);
SELECT distinct acct_role.cust_account_role_id
INTO p_default_bill_to_contact_id
FROM hz_cust_account_roles acct_role,
hz_cust_site_uses site_uses
WHERE site_uses.site_use_id = l_bill_to_site_use_id
AND acct_role.cust_account_id = l_bill_to_customer_id
AND nvl(acct_role.cust_acct_site_id,site_uses.cust_acct_site_id)
= site_uses.cust_acct_site_id
AND ( acct_role.cust_account_role_id = p_crtrx_bill_to_contact_id
OR
nvl(acct_role.status,'I') = 'A'
)
AND acct_role.cust_account_role_id =
nvl(p_crtrx_bill_to_contact_id,
l_ship_to_contact_id);
SELECT s.salesrep_id
INTO l_primary_salesrep_id
FROM ra_salesreps s,
hz_cust_site_uses site_uses
WHERE s.salesrep_id = site_uses.primary_salesrep_id
AND site_uses.site_use_id = l_bill_to_site_use_id
AND p_trx_date BETWEEN nvl(start_date_active, p_trx_date)
AND nvl(end_date_active, p_trx_date);
SELECT s.salesrep_id
INTO l_primary_salesrep_id
FROM ra_salesreps s,
hz_cust_site_uses site_uses
WHERE s.salesrep_id = site_uses.primary_salesrep_id
AND site_uses.site_use_id = l_ship_to_site_use_id
AND p_trx_date BETWEEN nvl(start_date_active, p_trx_date)
AND nvl(end_date_active, p_trx_date);