[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
arp_util.debug('selecting the default remit to address.');
SELECT acct_site.cust_acct_site_id,
loc.address1, loc.address2, loc.address3,
loc.address4, loc.city, loc.state,
loc.province, loc.postal_code,
loc.country
INTO pg_remit_to_address_rec
FROM ra_remit_tos rt,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc
WHERE rt.state = 'DEFAULT'
AND rt.country = 'DEFAULT'
AND rt.address_id = acct_site.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND rt.status = 'A'
AND NVL( acct_site.status, 'A' ) = 'A';
/* BugFix:2107873 Modified the Following SELECT statement so that
the Remit_to Country will be picked Up from fnd_territories_vl instead
of from hz_locations */
CURSOR remit_to IS
SELECT acct_site.cust_acct_site_id,
loc.address1, loc.address2,
loc.address3, loc.address4,
loc.city, loc.state,
loc.province, loc.postal_code,
territory.territory_short_name --loc.country
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
fnd_territories_vl territory,
ra_remit_tos rt
WHERE NVL( acct_site.status, 'A' ) = 'A'
AND acct_site.cust_acct_site_id = rt.address_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND rt.status = 'A'
AND rt.country = l_match_country
AND loc.country = territory.territory_code
AND (
l_match_state = NVL( rt.state, l_match_state )
OR
(
l_match_state IS NULL AND
rt.state IS NULL
)
OR (
l_match_state IS NULL AND
l_match_postal_code <= NVL( rt.postal_code_high,
l_match_postal_code ) AND
l_match_postal_code >= NVL( rt.postal_code_low,
l_match_postal_code ) AND
(
postal_code_low IS NOT NULL
OR postal_code_high IS NOT NULL
)
)
)
AND (
(
l_match_postal_code <= NVL( rt.postal_code_high,
l_match_postal_code ) AND
l_match_postal_code >= NVL( rt.postal_code_low,
l_match_postal_code )
)
OR (
l_match_postal_code IS NULL AND
rt.postal_code_low IS NULL AND
rt.postal_code_high IS NULL
)
)
ORDER BY rt.state,
rt.postal_code_low,
rt.postal_code_high;
| ELSE get the match values by selecting based on the address or |
| the site use depending on which ID has been specified. |
+-------------------------------------------------------------------------*/
IF (
p_match_state ||
p_match_country ||
p_match_postal_code IS NULL
)
THEN
/*------------------------------------------+
| Get the address information to match |
| if the address_id was specified |
+------------------------------------------*/
IF ( p_match_address_id IS NOT NULL )
THEN
arp_util.debug('getting address Info. based on address_id');
SELECT loc.state,
loc.country,
loc.postal_code
INTO l_match_state,
l_match_country,
l_match_postal_code
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc
WHERE acct_site.cust_acct_site_id = p_match_address_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id;
SELECT loc.state,
loc.country,
loc.postal_code
INTO l_match_state,
l_match_country,
l_match_postal_code
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses su
WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
AND su.site_use_id = p_match_site_use_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id;
| Select the remit to information based on the match criteria |
+---------------------------------------------------------------*/
arp_util.debug('selecting remit to information');
| IF no remit to address was selected above, |
| THEN use the default remit to address. |
| |
| IF no default remit to address exists, |
| THEN the procedure will raise a NO_DATA_FOUND exception. |
+-------------------------------------------------------------*/
IF ( remit_to%NOTFOUND )
THEN
get_default_remit_to(
p_remit_to_address_id,
l_remit_to_address_rec
);
SELECT arm.name payment_method_name,
arm.receipt_method_id,
arm.payment_channel_code, --ajay bug 1081390
arc.creation_method_code
FROM ar_receipt_methods arm,
ra_cust_receipt_methods rcrm,
ar_receipt_method_accounts arma,
ce_bank_acct_uses_all aba,
ce_bank_accounts cba,
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 rcrm.customer_id = p_customer_id
AND arma.org_id = aba.org_id
AND arma.remit_bank_acct_use_id = aba.bank_acct_use_id
AND aba.bank_account_id = cba.bank_account_id
AND bp.branch_party_id = cba.bank_branch_id /*Bug3348454*/
AND p_trx_date <= NVL(bp.end_date,p_trx_date) /*Bug3348454*/
AND
(
/* Bug-3770337-PM - Remove NVl condition */
rcrm.site_use_id = p_site_use_id
OR
(
p_site_use_id IS NULL
AND rcrm.site_use_id IS NULL
)
)
AND rcrm.primary_flag = 'Y'
AND (
cba.currency_code =
p_currency_code OR
cba.receipt_multi_currency_flag = 'Y'
)
-- added following condition for Bug 602458:
--Removing the join condition based on currency_code as part of bug fix 5346710
/*AND ( arc.creation_method_code = 'MANUAL' or
( arc.creation_method_code = 'AUTOMATIC' and
p_currency_code in (
select currency_code from
IBY_FNDCPT_PAYER_ASSGN_INSTR_V
where party_id = get_party_id(p_customer_id)
))*/
-- 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))
/* 19-APR-2000 J Rautiainen BR Implementation. Added union to default BR
* payment method. */
UNION
SELECT arm.name payment_method_name,
arm.receipt_method_id,
arm.payment_channel_code,
arc.creation_method_code
FROM ar_receipt_methods arm,
ra_cust_receipt_methods rcrm,
ar_receipt_classes arc,
ar_system_parameters sys
WHERE arm.receipt_method_id = rcrm.receipt_method_id
AND arm.receipt_class_id = arc.receipt_class_id
AND arc.creation_method_code = 'BR'
AND NVL(sys.bills_receivable_enabled_flag,'N') = 'Y'
AND rcrm.customer_id = p_customer_id
AND
(
/* Bug-3770337-PM - Remove NVl condition */
rcrm.site_use_id = p_site_use_id
OR
(
p_site_use_id IS NULL
AND rcrm.site_use_id IS NULL
)
)
AND rcrm.primary_flag = 'Y'
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));
* Moved select statement to cursor. Also removed NO_DATA_FOUND
* Exception handler and added the logic in the IF statement below */
OPEN payment_method_cur;
| 16-AUG-99 Debbie Jancis Acct masking project. Modified select |
| to get masked bank acct num if profile |
| is set. |
+===========================================================================*/
FUNCTION check_bank_default(
p_trx_date IN
ra_customer_trx.trx_date%type,
p_customer_id IN
ra_customer_trx.customer_trx_id%type,
p_site_use_id IN
hz_cust_site_uses.site_use_id%type,
p_currency_code IN
fnd_currencies.currency_code%type,
p_customer_bank_account_id OUT NOCOPY
ce_bank_accounts.bank_account_id%type,
p_bank_account_num OUT NOCOPY
ce_bank_accounts.bank_account_num%type,
p_bank_name OUT NOCOPY
ce_bank_branches_v.bank_name%type,
p_bank_branch_name OUT NOCOPY
ce_bank_branches_v.bank_branch_name%type,
p_bank_branch_id OUT NOCOPY
ce_bank_branches_v.branch_party_id%TYPE
) RETURN BOOLEAN IS
l_customer_bank_account_id
ce_bank_accounts.bank_account_id%type;
SELECT abb.bank_name,
abb.bank_branch_name,
abb.branch_party_id
INTO
p_bank_name,
p_bank_branch_name,
p_bank_branch_id
FROM
ce_bank_branches_v abb
WHERE
abb.branch_party_id = arp_global.CC_BANK_BRANCH_ID;
SELECT SUBSTRB( l_remit_to_address_rec.address1,
1, 25) ||
DECODE( l_remit_to_address_rec.address2,
NULL, NULL,
', ') ||
NVL(
SUBSTRB( l_remit_to_address_rec.address2,
1, 25),
SUBSTRB( l_remit_to_address_rec.address1,
26, 25)
) || ','||' '||
l_remit_to_address_rec.city ||
','||' '||
NVL( l_remit_to_address_rec.state,
l_remit_to_address_rec.province) ||
' '|| l_remit_to_address_rec.postal_code ||
' '|| l_remit_to_address_rec.country
INTO p_concatenated_address
FROM dual;
select org_id
into l_org_id
from ra_cust_trx_types
where cust_trx_type_id = p_cust_trx_type_id;
select name, billing_cycle_id
into l_cust_term_name, l_billing_cycle_id
from ra_terms
where term_id = l_cust_term_id;
SELECT tl.term_id,
NVL(
t_su.name,
NVL(
t_cp1.name,
t_cp2.name
)
),
arpt_sql_func_util.get_First_Due_Date( tl.term_id,
p_trx_date),
count(*)
INTO l_cust_term_id,
l_cust_term_name,
l_term_due_date,
l_number_of_due_dates
FROM ra_terms t_su,
ra_terms t_cp1,
ra_terms t_cp2,
ra_terms_lines tl,
hz_customer_profiles cp1,
hz_customer_profiles cp2,
hz_cust_site_uses su
WHERE p_customer_id = cp1.cust_account_id(+)
AND su.site_use_id = p_site_use_id
AND cp2.cust_account_id = p_customer_id
AND su.site_use_id = cp1.site_use_id(+)
AND cp2.site_use_id IS NULL
AND su.payment_term_id = t_su.term_id(+)
AND cp1.standard_terms = t_cp1.term_id(+)
AND cp2.standard_terms = t_cp2.term_id(+)
AND NVL(
t_su.term_id,
NVL(
t_cp1.term_id,
t_cp2.term_id
)
) = tl.term_id
AND p_trx_date BETWEEN t_su.start_date_active(+)
AND NVL(t_su.end_date_active(+),
p_trx_date)
AND p_trx_date BETWEEN t_cp1.start_date_active(+)
AND NVL(t_cp1.end_date_active(+),
p_trx_date)
AND p_trx_date BETWEEN t_cp2.start_date_active(+)
AND NVL(t_cp2.end_date_active(+),
p_trx_date)
GROUP BY tl.term_id,
t_su.name,
t_cp1.name,
t_cp2.name
-- Guarantees cannot have split term terms
HAVING 1 = DECODE(p_class,
'GUAR', COUNT(*),
1 );
SELECT COUNT(*),
arpt_sql_func_util.get_First_Due_Date(
p_type_term_id,
p_trx_date)
INTO l_number_of_due_dates,
l_term_due_date
FROM ra_terms_lines
WHERE term_id = p_type_term_id;
SELECT
rat.name,
rat.term_id
INTO l_cust_term_name,
l_cust_term_id
FROM ra_terms rat,
ra_cust_trx_types ctt
WHERE ctt.cust_trx_type_id=p_cust_trx_type_id
AND ctt.default_term=rat.term_id(+);
SELECT COUNT(*),
arpt_sql_func_util.get_First_Due_Date(
l_cust_term_id,
p_trx_date)
INTO l_number_of_due_dates,
l_term_due_date
FROM ra_terms_lines
WHERE term_id = l_cust_term_id;
Select payment_channel_name
into l_payment_channel_name
from iby_fndcpt_all_pmt_channels_v pmt_cv
where pmt_cv.instrument_type not in ('MANUAL', 'PINLESSDEBITCARD')
and pmt_cv.payment_channel_code = p_payment_channel_code;
Select party_id
into l_party_id
from hz_cust_accounts
where cust_account_id = p_cust_account_id ;
SELECT instrument_type
INTO l_instrument_type
FROM iby_fndcpt_pmt_chnnls_b
WHERE payment_channel_code = p_payment_channel_code
AND instrument_type in ('CREDITCARD', 'BANKACCOUNT');
SELECT decode (nvl(u.instrument_type, p.instrument_type)
, 'BANKACCOUNT', b.masked_bank_account_num
, 'CREDITCARD', c.masked_cc_number
, NULL) instrument
into l_instrument
FROM
iby_creditcard c,
iby_ext_bank_accounts b,
iby_fndcpt_pmt_chnnls_b p,
iby_fndcpt_pmt_chnnls_tl pt,
iby_fndcpt_tx_extensions x,
iby_pmt_instr_uses_all u,
fnd_application a
WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+))
AND (DECODE(u.instrument_type, 'CREDITCARD',u.instrument_id, NULL) = c.instrid(+))
AND (DECODE(u.instrument_type, 'BANKACCOUNT',u.instrument_id, NULL) = b.ext_bank_account_id(+))
AND (x.payment_channel_code = p.payment_channel_code)
AND (x.origin_application_id = a.application_id)
AND (P.payment_channel_code = pt.payment_channel_code)
AND (PT.LANGUAGE = USERENV('LANG'))
AND trxn_extension_id = p_payment_trxn_extension_id
AND nvl(u.instrument_type, p.instrument_type) = l_instrument_type;
select bank_name,bank_branch_name,instr_assignment_id,account_number
into p_bank_name,p_branch_name,p_instr_assign_id,p_instr_number
from iby_trxn_extensions_v
where trxn_extension_id = p_payment_trxn_extension_id
and payment_channel_code = p_payment_channel_code;