[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_cc_bill_to_site(
p_cc_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
x_cc_bill_to_site_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2);
SELECT lengthb(p_credit_card_number)
INTO l_len_credit_card_num
FROM dual;
SELECT to_number(substrb(p_credit_card_number,i,1))
INTO l_stripped_num_table(i)
FROM dual;
SELECT cc_issuer_range_id, r.card_issuer_code,
card_number_prefix, NVL(digit_check_flag,'N')
FROM iby_cc_issuer_ranges r, iby_creditcard_issuers_b i
WHERE (card_number_length = ci_card_len)
AND (INSTR(ci_card_number,card_number_prefix) = 1)
AND (r.card_issuer_code = i.card_issuer_code);
SELECT rm.receipt_method_id receipt_method_id, rm.payment_channel_code payment_channel_code,
rc.creation_status receipt_creation_status
FROM ar_system_parameters sp,
ar_receipt_classes rc,
ar_receipt_methods rm
WHERE rm.receipt_method_id = decode(p_payment_instrument, /* J Rautiainen ACH Implementation */
'BANK_ACCOUNT', sp.irec_ba_receipt_method_id, /* J Rautiainen ACH Implementation */
sp.irec_cc_receipt_method_id) /* J Rautiainen ACH Implementation */
AND rm.receipt_class_id = rc.receipt_class_id;
SELECT arm.receipt_method_id receipt_method_id, arm.payment_channel_code payment_channel_code,
arc.creation_status receipt_creation_status
FROM ar_receipt_methods arm,
ra_cust_receipt_methods rcrm,
ar_receipt_method_accounts arma,
ce_bank_acct_uses_ou_v aba,
ce_bank_accounts cba,
ar_receipt_classes arc
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.remit_bank_acct_use_id = aba.bank_acct_use_id
AND aba.bank_account_id = cba.bank_account_id
AND
(
NVL(rcrm.site_use_id,
p_siteuseid) = p_siteuseid
OR
(
p_siteuseid IS NULL
AND rcrm.site_use_id IS NULL
)
)
--Bug#6109909
--AND rcrm.primary_flag = 'Y'
AND (
cba.currency_code =
p_currcode OR
cba.receipt_multi_currency_flag = 'Y'
)
AND (
( p_payment_instrument = 'BANK_ACCOUNT'
--Bug 6024713: Choose 'NONE' if arm.payment_type_code is NULL
--Bug#6109909:
-- In 11i The 'PaymentMethod' in UI maps to 'payment_type_code' column of table ar_receipts_methods
-- and in R12, it maps to 'payment_channel_code' whose values are taken from IBY sources.
-- In R12, the 'payment_type_code' is 'NONE' for new records.
-- AND In R12, Here we are not handling the code for the other payment Methods like Bills Receivable, Debit Card etc..,
-- and nvl(arm.payment_type_code, 'NONE') <> 'CREDIT_CARD'
and arm.payment_channel_code <> 'CREDIT_CARD'
and arc.remit_flag = 'Y'
and arc.confirm_flag = 'N')
OR ( p_payment_instrument <> 'BANK_ACCOUNT'
--Bug#6109909
--and nvl(arm.payment_type_code, 'NONE') = 'CREDIT_CARD')
and arm.payment_channel_code = 'CREDIT_CARD')
)
-- Bug#6109909:
-- In R12,Currency code is not mandatory on the customer bank account and so removing the
-- below condition.
-- Observations for the below condition, if it requires in future:
-- a. The where caluse criteria 'party_id = p_customer_id' should be replaced
-- with 'cust_account_id = p_customer_id'
-- b. For 'AUTOMATIC' creation methods, Don't validate the currencyCode for
-- 'Credit Card' instrucment types. Here validate only for 'BankAccount'
/*
AND ( arc.creation_method_code = 'MANUAL' or
( arc.creation_method_code = 'AUTOMATIC' and
--Bug 4947418: Modified the following query as ar_customer_bank_accounts_v
--has been obsoleted in r12.
p_currcode in (select currency_code from
iby_fndcpt_payer_assgn_instr_v
where party_id=p_customer_id)))
*/
-- AND aba.set_of_books_id = arp_trx_global.system_info.system_parameters.set_of_books_id
AND TRUNC(nvl(aba.end_date,
p_trx_date)) >=
TRUNC(p_trx_date)
--Bug 6024713: Added TRUNC for the left side for the below 3 criterias
AND TRUNC(p_trx_date) between
TRUNC(nvl(
arm.start_date,
p_trx_date))
and TRUNC(nvl(
arm.end_date,
p_trx_date))
AND TRUNC(p_trx_date) between
TRUNC(nvl(
rcrm.start_date,
p_trx_date))
and TRUNC(nvl(
rcrm.end_date,
p_trx_date))
AND TRUNC(p_trx_date) between
TRUNC(arma.start_date)
and TRUNC(nvl(
arma.end_date,
p_trx_date))
ORDER BY rcrm.primary_flag DESC;
SELECT arm.receipt_method_id receipt_method_id,
arm.payment_channel_code payment_channel_code,
arc.creation_status receipt_creation_status
FROM ar_receipt_methods arm,
ar_receipt_method_accounts arma,
ce_bank_acct_uses_ou_v aba,
ce_bank_accounts cba,
ar_receipt_classes arc
WHERE arm.payment_channel_code = 'CREDIT_CARD'
AND arm.receipt_method_id = NVL( to_number(fnd_profile.VALUE('OIR_CC_PMT_METHOD')), arm.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
AND (cba.currency_code = p_currency_code OR cba.receipt_multi_currency_flag = 'Y')
AND TRUNC(nvl(aba.end_date,p_trx_date)) >= TRUNC(p_trx_date)
AND TRUNC(p_trx_date) BETWEEN TRUNC(nvl(arm.start_date, p_trx_date)) AND TRUNC(nvl(arm.end_date, p_trx_date))
AND TRUNC(p_trx_date) BETWEEN TRUNC(arma.start_date) AND TRUNC(nvl(arma.end_date, p_trx_date));
SELECT arm.receipt_method_id receipt_method_id,
arm.payment_channel_code payment_channel_code,
arc.creation_status receipt_creation_status
FROM ar_receipt_methods arm,
ar_receipt_method_accounts arma,
ce_bank_acct_uses_ou_v aba,
ce_bank_accounts cba,
ar_receipt_classes arc
WHERE NVL(arm.payment_channel_code,'NONE') <> 'CREDIT_CARD'
AND arm.receipt_method_id = NVL( to_number(fnd_profile.VALUE('OIR_BA_PMT_METHOD')), arm.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
AND (cba.currency_code = p_currency_code OR cba.receipt_multi_currency_flag = 'Y')
AND TRUNC(nvl(aba.end_date,p_trx_date)) >= TRUNC(p_trx_date)
AND TRUNC(p_trx_date) BETWEEN TRUNC(nvl(arm.start_date, p_trx_date)) AND TRUNC(nvl(arm.end_date, p_trx_date))
AND TRUNC(p_trx_date) BETWEEN TRUNC(arma.start_date) AND TRUNC(nvl(arma.end_date, p_trx_date));
SELECT customer_site_use_id, invoice_currency_code, exchange_rate,trx_number
FROM ar_payment_schedules
WHERE payment_schedule_id = p_payment_schedule_id;
select customer_id,customer_site_use_id,currency_code into l_customer_id,l_site_use_id,l_currency_code
from AR_IREC_PAYMENT_LIST_GT
where customer_id = p_customer_id
and customer_site_use_id = nvl(decode(p_site_use_id, -1, null, p_site_use_id), customer_site_use_id);
select currency_code into l_currency_code
from AR_IREC_PAYMENT_LIST_GT
group by currency_code;
| PUBLIC procedure update_expiration_date
|
| DESCRIPTION
| Updates credit card expiration date
| ----------------------------------------
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
|
| p_bank_account_id Credit Card bank account id
| p_expiration_date New expiration date
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 10-FEB-2001 O Steinmeier Created
|
| Removed code 'BANK_ACCOUNT_NUM = p_bank_account_num AND ' from select for bug # 9046643
| 06-Feb-2013 melapaku Bug16262617 - cannot remove end date entered via ireceivables pay function
| 01-Mar-2013 melapaku Bug16420473 - CANNOT END DATE BANK ACC WHICH IS ASSOCIATED AT ACC AND SITE LEVEL IN
| IRECEIVABLES
*=======================================================================*/
PROCEDURE update_expiration_date( p_bank_account_id IN NUMBER,
p_expiration_date IN DATE,
p_payment_instrument IN VARCHAR2,
p_branch_id IN iby_ext_bank_accounts.BRANCH_ID%TYPE,
p_bank_id IN iby_ext_bank_accounts.BANK_ID%TYPE,
p_bank_account_num IN iby_ext_bank_accounts.BANK_ACCOUNT_NUM%TYPE,
p_currency IN iby_ext_bank_accounts.CURRENCY_CODE%TYPE,
p_object_version_number IN iby_ext_bank_accounts.OBJECT_VERSION_NUMBER%TYPE,
x_return_status OUT NOCOPY VARCHAR,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_customer_id IN NUMBER,
p_customer_site_id IN NUMBER) IS
CURSOR instr_details(p_bank_account_id IN NUMBER,
l_party_id IN NUMBER,
l_customer_site_id IN NUMBER) IS
select org_id,instr_assignment_id,assignment_start_date,acct_site_use_id
from IBY_FNDCPT_PAYER_ASSGN_INSTR_V
where INSTRUMENT_ID = p_bank_account_id AND PARTY_ID = l_party_id
AND((l_customer_site_id IS NOT NULL AND ACCT_SITE_USE_ID = l_customer_site_id ) OR (l_customer_site_id IS NULL AND ACCT_SITE_USE_ID IS NULL));
l_procedure_name := '.update_expiration_date';
WRITE_DEBUG_AND_LOG('Input Parameters for update_expiration_date'||
'p_customer_site_id ' || p_customer_site_id ||
'p_object_version_number ' || p_object_version_number ||
'p_bank_account_id ' || p_bank_account_id ||
'p_currency ' || p_currency||
'p_expiration_date '||p_expiration_date||
'p_customer_id '||p_customer_id||
'p_branch_id '||p_branch_id
);
WRITE_DEBUG_AND_LOG('In CC expiration date update');
IBY_FNDCPT_SETUP_PUB.update_card(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_card_instrument => l_create_credit_card,
x_response => l_result_rec);
WRITE_DEBUG_AND_LOG('In BA expiration date update');
SELECT PARTY_ID INTO l_party_id FROM HZ_CUST_ACCOUNTS WHERE CUST_ACCOUNT_ID = p_customer_id;
select org_id,instr_assignment_id,assignment_start_date,acct_site_use_id
into l_org_id,l_instr_assignment_id,l_assignment_start_date,l_acct_site_use_id
from IBY_FNDCPT_PAYER_ASSGN_INSTR_V
where INSTRUMENT_ID = p_bank_account_id AND PARTY_ID = l_party_id
AND ACCT_SITE_USE_ID IS NULL;
SELECT amount_due_remaining, class, invoice_currency_code
INTO l_ps_balance, l_class, l_currency_code
FROM ar_payment_schedules
WHERE payment_schedule_id = p_payment_schedule_id;
SELECT bank.masked_bank_account_num bank_account_num_masked,
bank.bank_account_type account_type,
NULL expiry_month,
NULL expiry_year,
'0' credit_card_expired,
u.instrument_id bank_account_id,
bank.branch_id bank_branch_id,
bank.bank_account_name account_holder,
NULL cvv_code,
NULL conc_address,
NULL card_code,
NULL party_site_id,
u.instrument_payment_use_id instr_assignment_id,
bank.bank_id bank_party_id,
bank.branch_id branch_party_id,
bank.object_version_number
FROM hz_cust_accounts cust,
hz_party_preferences pp1,
iby_external_payers_all p,
iby_pmt_instr_uses_all u,
iby_ext_bank_accounts bank,
hz_organization_profiles bapr,
hz_organization_profiles brpr,
iby_account_owners ow
WHERE cust.cust_account_id = p_customer_id
AND pp1.party_id = cust.party_id
AND pp1.category = 'LAST_USED_PAYMENT_INSTRUMENT'
AND pp1.preference_code = 'INSTRUMENT_ID'
AND p.cust_account_id = p_customer_id
AND p.party_id = cust.party_id
AND ( (p.acct_site_use_id = p_customer_site_use_id) OR
(p.acct_site_use_id IS NULL AND decode(p_customer_site_use_id, -1, NULL, p_customer_site_use_id) IS NULL) )
AND u.ext_pmt_party_id = p.ext_payer_id
AND u.instrument_type = 'BANKACCOUNT'
AND u.payment_flow = 'FUNDS_CAPTURE'
AND u.instrument_id = pp1.value_number
AND nvl(TRUNC(bank.start_date),sysdate - 1) <= TRUNC(sysdate) -- Added for Bug# 16097315
AND nvl(TRUNC(u.start_date), sysdate - 1) <= TRUNC(sysdate) -- 16097315 to not fetch the instrument if start date is in future
AND nvl(trunc(u.end_date), sysdate+10) >= TRUNC(sysdate) -- 13601435, to avoid picking end dated bank account assignments
AND pp1.value_number = bank.ext_bank_account_id(+)
AND ( decode(bank.currency_code, NULL, 'Y', 'N')='Y' OR bank.currency_code = p_currency_code)
AND bank.bank_id = bapr.party_id(+)
AND bank.branch_id = brpr.party_id(+)
AND TRUNC(sysdate) BETWEEN nvl(TRUNC(bapr.effective_start_date), sysdate -1) AND nvl(TRUNC(bapr.effective_end_date), sysdate + 1)
AND TRUNC(sysdate) BETWEEN nvl(TRUNC(brpr.effective_start_date), sysdate -1) AND nvl(TRUNC(brpr.effective_end_date), sysdate + 1)
AND bank.ext_bank_account_id = ow.ext_bank_account_id(+)
AND ow.primary_flag(+) = 'Y'
AND nvl(TRUNC(bank.end_date), sysdate + 10) >= TRUNC(sysdate) --bug 9098662
UNION ALL
SELECT c.CARD_NUMBER bank_account_num_masked,
c.CARD_ISSUER_NAME account_type,
decode(sysoptions.supplemental_data_option,'Y','XX',to_char(to_date(c.CARD_EXPIRYDATE),'MM')) expiry_month,
decode(sysoptions.supplemental_data_option,'Y','XXXX',to_char(to_date(c.CARD_EXPIRYDATE),'YYYY')) expiry_year,
decode(c.CARD_EXPIRED_FLAG,'Y','1','0') credit_card_expired,
c.INSTRUMENT_ID bank_account_id,
1 bank_branch_id,
nvl(c.CARD_HOLDER_NAME, hzcc.party_name) account_holder,
NULL cvv_code,
arp_addr_pkg.format_address(loc.address_style, loc.address1, loc.address2, loc.address3, loc.address4, loc.city, loc.county, loc.state, loc.province, loc.postal_code, terr.territory_short_name) conc_address,
c.CARD_ISSUER_CODE card_code,
psu.party_site_id,
c.INSTR_ASSIGNMENT_ID,
NULL bank_party_id,
NULL branch_party_id,
NULL object_version_number
FROM hz_cust_accounts cust,
hz_party_preferences pp1,
iby_external_payers_all p,
IBY_FNDCPT_PAYER_ASSGN_INSTR_V c,
hz_parties hzcc,
hz_party_site_uses psu,
hz_party_sites hps,
hz_locations loc,
fnd_territories_vl terr,
(select ENCRYPT_SUPPLEMENTAL_CARD_DATA as supplemental_data_option from iby_sys_security_options) sysoptions
WHERE cust.cust_account_id = p_customer_id
AND cust.party_id = hzcc.party_id
AND pp1.party_id = hzcc.party_id
AND pp1.category = 'LAST_USED_PAYMENT_INSTRUMENT'
AND pp1.preference_code = 'INSTRUMENT_ID'
AND p.cust_account_id = p_customer_id
AND p.party_id = hzcc.party_id
AND ( (p.acct_site_use_id = p_customer_site_use_id) OR
(p.acct_site_use_id IS NULL AND decode(p_customer_site_use_id, -1, NULL, p_customer_site_use_id) IS NULL) )
AND c.INSTRUMENT_TYPE = 'CREDITCARD'
AND nvl(TRUNC(c.assignment_start_date), sysdate - 1) <= TRUNC(sysdate) -- Added for Bug#16097315
AND nvl(TRUNC(c.assignment_end_date), sysdate + 10) >= TRUNC(sysdate) -- bug 11832912
AND c.instrument_id = pp1.value_number
AND c.EXT_PAYER_ID = p.ext_payer_id
AND c.CARD_BILLING_ADDRESS_ID = psu.party_site_use_id(+)
AND psu.party_site_id = hps.party_site_id(+)
AND hps.location_id = loc.location_id(+)
AND loc.country = terr.territory_code(+);
SELECT
u.instrument_type instrument_type,
bank.masked_bank_account_num bank_account_num_masked,
bank.bank_account_type account_type,
null expiry_month,
null expiry_year,
'0' credit_card_expired,
u.instrument_id bank_account_id,
bank.branch_id bank_branch_id,
bank.bank_account_name account_holder,
null cvv_code,
null conc_address,
null card_code,
null party_site_id,
u.instrument_payment_use_id instr_assignment_id,
bank.bank_id bank_party_id,
bank.branch_id branch_party_id,
bank.object_version_number
FROM
hz_cust_accounts cust,
iby_external_payers_all p,
iby_pmt_instr_uses_all u,
iby_ext_bank_accounts bank,
hz_organization_profiles bapr,
hz_organization_profiles brpr,
iby_account_owners ow
WHERE
cust.cust_account_id = p_customer_id
AND p.cust_account_id = cust.cust_account_id
AND p.party_id = cust.party_id
AND (
(p.acct_site_use_id = p_customer_site_use_id)
OR
(p.acct_site_use_id IS NULL AND DECODE(p_customer_site_use_id, -1, NULL, p_customer_site_use_id) IS NULL)
)
AND u.ext_pmt_party_id = p.ext_payer_id
AND u.instrument_type='BANKACCOUNT'
AND u.payment_flow = 'FUNDS_CAPTURE'
AND u.instrument_id = bank.ext_bank_account_id(+)
AND nvl(TRUNC(bank.start_date),sysdate - 1) <= TRUNC(sysdate) -- Added for Bug#16097315
AND nvl(TRUNC(u.start_date), sysdate - 1) <= TRUNC(sysdate) -- Added for Bug#16097315
AND nvl(TRUNC(u.end_date), sysdate + 10) >= TRUNC(sysdate) -- bug 13601435 to avoid fetching end dated bank account assignments
AND ( decode(bank.currency_code, NULL, 'Y', 'N')='Y' OR bank.currency_code = p_currency_code)
AND bank.bank_id = bapr.party_id(+)
AND bank.branch_id = brpr.party_id(+)
AND TRUNC(sysdate) BETWEEN nvl(TRUNC(bapr.effective_start_date), sysdate -1) AND nvl(TRUNC(bapr.effective_end_date), sysdate + 1)
AND TRUNC(sysdate) BETWEEN nvl(TRUNC(brpr.effective_start_date), sysdate -1) AND nvl(TRUNC(brpr.effective_end_date), sysdate + 1)
AND bank.ext_bank_account_id = ow.ext_bank_account_id(+)
AND nvl(TRUNC(bank.end_date), sysdate + 10) >= TRUNC(sysdate) -- bug 13601435 to avoid fetching end dated bank account
AND ow.primary_flag(+) = 'Y'
AND nvl(TRUNC(ow.end_date), sysdate + 10) > TRUNC(sysdate);
SELECT
u.instrument_type instrument_type,
c.masked_cc_number bank_account_num_masked,
decode(i.card_issuer_code, NULL, ccunk.meaning, i.card_issuer_name) account_type,
null expiry_month,
null expiry_year,
'0' credit_card_expired,
u.instrument_id bank_account_id,
1 bank_branch_id,
NVL(c.chname,hzcc.party_name) account_holder,
NULL cvv_code,
arp_addr_pkg.format_address(loc.address_style, loc.address1, loc.address2, loc.address3, loc.address4, loc.city, loc.county, loc.state, loc.province, loc.postal_code, terr.territory_short_name) conc_address,
c.card_issuer_code card_code,
psu.party_site_id,
u.instrument_payment_use_id instr_assignment_id,
NULL bank_party_id,
NULL branch_party_id,
NULL object_version_number
FROM
fnd_lookup_values_vl ccunk,
iby_creditcard c,
iby_creditcard_issuers_vl i,
iby_external_payers_all p,
iby_pmt_instr_uses_all u,
hz_parties hzcc,
hz_cust_accounts cust,
hz_party_site_uses psu,
hz_party_sites hps,
hz_locations loc,
fnd_territories_vl terr
WHERE
cust.cust_account_id = p_customer_id
AND p.cust_account_id = cust.cust_account_id
AND p.party_id = cust.party_id
AND (
(p.acct_site_use_id = p_customer_site_use_id)
OR
(p.acct_site_use_id IS NULL AND DECODE(p_customer_site_use_id, -1, NULL, p_customer_site_use_id) IS NULL)
)
AND u.ext_pmt_party_id = p.ext_payer_id
AND u.instrument_type = 'CREDITCARD'
AND u.payment_flow = 'FUNDS_CAPTURE'
AND nvl(TRUNC(u.start_date), sysdate - 1) <= TRUNC(sysdate) -- Added for Bug#16097315
AND nvl(TRUNC(u.end_date), sysdate + 10) >= TRUNC(sysdate) -- bug 11832912
AND u.instrument_id = c.instrid(+)
AND nvl(c.inactive_date, sysdate + 10) > sysdate
AND c.card_issuer_code = i.card_issuer_code(+)
AND c.card_owner_id = hzcc.party_id(+)
AND c.addressid = psu.party_site_use_id(+)
AND psu.party_site_id = hps.party_site_id(+)
AND hps.location_id = loc.location_id(+)
AND loc.country = terr.territory_code(+)
AND ccunk.lookup_type = 'IBY_CARD_TYPES'
AND ccunk.lookup_code = 'UNKNOWN';
select to_char(to_number(to_char(sysdate,'MM'))) current_month,
to_char(sysdate,'YYYY') current_year
from dual;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_customer_id;
SELECT party_preference_id, object_version_number
FROM hz_party_preferences
WHERE party_id = p_party_id
AND category = 'LAST_USED_PAYMENT_INSTRUMENT'
AND preference_code = p_preference_code;
SELECT count(1) ca_exists
FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V IBY
WHERE IBY.instrument_id = p_instrument_id
AND IBY.CARD_HOLDER_NAME <> p_account_holder_name;
SELECT count(1) ba_exists
FROM iby_ext_bank_accounts_v ba
WHERE ba.branch_number = p_routing_number
AND ba.bank_account_number = p_bank_account_number
AND ROWNUM = 1
AND ba.bank_account_name <> p_account_holder_name;
select LOOKUP_CODE
from FND_LOOKUPS
where LOOKUP_TYPE = 'IBY_BANKACCT_TYPES'
and LOOKUP_CODE = UPPER(p_account_type);
SELECT ps.CUSTOMER_ID,
ps.CUSTOMER_SITE_USE_ID, -- Bug # 3828358
acct.ACCOUNT_NUMBER,
ps.CUSTOMER_TRX_ID,
ps.TRX_NUMBER,
ps.TRX_DATE,
ps.class,
ps.DUE_DATE,
ps.PAYMENT_SCHEDULE_ID,
ps.STATUS,
trm.name term_desc,
ARPT_SQL_FUNC_UTIL.Get_Number_Of_Due_Dates(ps.term_id) number_of_installments,
ps.terms_sequence_number,
ps.amount_line_items_original line_amount,
ps.tax_original tax_amount,
ps.freight_original freight_amount,
ps.receivables_charges_charged finance_charge,
ps.INVOICE_CURRENCY_CODE,
ps.AMOUNT_DUE_ORIGINAL,
ps.AMOUNT_DUE_REMAINING,
0 payment_amt,
0 service_charge,
0 discount_amount,
case WHEN ((trunc(ps.trx_date) - trunc(sysdate)) <= 0) then TRUNC(SYSDATE) else ps.trx_date end as receipt_date,
'' receipt_number,
ct.PURCHASE_ORDER AS PO_NUMBER,
NULL AS SO_NUMBER,
ct.printing_option,
ct.ATTRIBUTE_CATEGORY,
ct.ATTRIBUTE1,
ct.ATTRIBUTE2,
ct.ATTRIBUTE3,
ct.ATTRIBUTE4,
ct.ATTRIBUTE5,
ct.ATTRIBUTE6,
ct.ATTRIBUTE7,
ct.ATTRIBUTE8,
ct.ATTRIBUTE9,
ct.ATTRIBUTE10,
ct.ATTRIBUTE11,
ct.ATTRIBUTE12,
ct.ATTRIBUTE13,
ct.ATTRIBUTE14,
ct.ATTRIBUTE15,
ct.INTERFACE_HEADER_CONTEXT,
ct.INTERFACE_HEADER_ATTRIBUTE1,
ct.INTERFACE_HEADER_ATTRIBUTE2,
ct.INTERFACE_HEADER_ATTRIBUTE3,
ct.INTERFACE_HEADER_ATTRIBUTE4,
ct.INTERFACE_HEADER_ATTRIBUTE5,
ct.INTERFACE_HEADER_ATTRIBUTE6,
ct.INTERFACE_HEADER_ATTRIBUTE7,
ct.INTERFACE_HEADER_ATTRIBUTE8,
ct.INTERFACE_HEADER_ATTRIBUTE9,
ct.INTERFACE_HEADER_ATTRIBUTE10,
ct.INTERFACE_HEADER_ATTRIBUTE11,
ct.INTERFACE_HEADER_ATTRIBUTE12,
ct.INTERFACE_HEADER_ATTRIBUTE13,
ct.INTERFACE_HEADER_ATTRIBUTE14,
ct.INTERFACE_HEADER_ATTRIBUTE15,
sysdate LAST_UPDATE_DATE,
0 LAST_UPDATED_BY,
sysdate CREATION_DATE,
0 CREATED_BY,
0 LAST_UPDATE_LOGIN,
0 APPLICATION_AMOUNT,
0 CASH_RECEIPT_ID,
0 ORIGINAL_DISCOUNT_AMT,
ps.org_id,
ct.PAYING_CUSTOMER_ID,
ct.PAYING_SITE_USE_ID,
( decode( nvl(ps.AMOUNT_DUE_ORIGINAL,0),0,1,(ps.AMOUNT_DUE_ORIGINAL/abs(ps.AMOUNT_DUE_ORIGINAL)) ) *abs(nvl(ps.amount_in_dispute,0)) ) dispute_amt
FROM AR_PAYMENT_SCHEDULES ps,
RA_CUSTOMER_TRX ct,
HZ_CUST_ACCOUNTS acct,
RA_TERMS trm
WHERE ps.CLASS IN ('INV', 'DM', 'CB', 'DEP')
AND ps.customer_trx_id = ct.customer_trx_id
AND acct.cust_account_id = ps.customer_id
AND ps.status = 'OP'
AND ps.term_id = trm.term_id(+)
AND ( ps.payment_schedule_id = p_payment_schedule_id
OR p_payment_schedule_id IS NULL)
AND ps.customer_id = p_customer_id
AND ps.customer_site_use_id = nvl(decode(p_customer_site_use_id, -1, null, p_customer_site_use_id), ps.customer_site_use_id)
AND ps.invoice_currency_code = p_currency_code;
DELETE FROM AR_IREC_PAYMENT_LIST_GT
WHERE PAYMENT_SCHEDULE_ID = p_payment_schedule_id
AND CURRENCY_CODE = p_currency_code;
arp_standard.debug('Inserting: '||l_open_invoice_list_rec(trx).trx_number);
INSERT INTO AR_IREC_PAYMENT_LIST_GT
VALUES l_open_invoice_list_rec(trx);
( SELECT * FROM
(SELECT ps.CUSTOMER_ID,
DECODE(ps.CUSTOMER_SITE_USE_ID,null,-1,ps.CUSTOMER_SITE_USE_ID) as CUSTOMER_SITE_USE_ID,
acct.ACCOUNT_NUMBER,
ps.CUSTOMER_TRX_ID,
ps.TRX_NUMBER,
ps.TRX_DATE,
ps.class,
ps.DUE_DATE,
ps.PAYMENT_SCHEDULE_ID,
ps.STATUS,
trm.name term_desc,
ARPT_SQL_FUNC_UTIL.Get_Number_Of_Due_Dates(ps.term_id) number_of_installments,
ps.terms_sequence_number,
ps.amount_line_items_original line_amount,
ps.tax_original tax_amount,
ps.freight_original freight_amount,
ps.receivables_charges_charged finance_charge,
ps.INVOICE_CURRENCY_CODE,
ps.AMOUNT_DUE_ORIGINAL,
DECODE (ps.class, 'PMT', ar_irec_payments.get_pymt_amnt_due_remaining(ps.cash_receipt_id),ps.AMOUNT_DUE_REMAINING) as AMOUNT_DUE_REMAINING,
0 payment_amt,
0 service_charge,
0 discount_amount,
case WHEN ((trunc(ps.trx_date) - trunc(sysdate)) <= 0) then TRUNC(SYSDATE) else ps.trx_date end as receipt_date,
'' receipt_number,
ct.PURCHASE_ORDER AS PO_NUMBER,
NULL AS SO_NUMBER,
ct.printing_option,
ct.INTERFACE_HEADER_CONTEXT,
ct.INTERFACE_HEADER_ATTRIBUTE1,
ct.INTERFACE_HEADER_ATTRIBUTE2,
ct.INTERFACE_HEADER_ATTRIBUTE3,
ct.INTERFACE_HEADER_ATTRIBUTE4,
ct.INTERFACE_HEADER_ATTRIBUTE5,
ct.INTERFACE_HEADER_ATTRIBUTE6,
ct.INTERFACE_HEADER_ATTRIBUTE7,
ct.INTERFACE_HEADER_ATTRIBUTE8,
ct.INTERFACE_HEADER_ATTRIBUTE9,
ct.INTERFACE_HEADER_ATTRIBUTE10,
ct.INTERFACE_HEADER_ATTRIBUTE11,
ct.INTERFACE_HEADER_ATTRIBUTE12,
ct.INTERFACE_HEADER_ATTRIBUTE13,
ct.INTERFACE_HEADER_ATTRIBUTE14,
ct.INTERFACE_HEADER_ATTRIBUTE15,
ps.ATTRIBUTE_CATEGORY,
ps.ATTRIBUTE1,
ps.ATTRIBUTE2,
ps.ATTRIBUTE3,
ps.ATTRIBUTE4,
ps.ATTRIBUTE5,
ps.ATTRIBUTE6,
ps.ATTRIBUTE7,
ps.ATTRIBUTE8,
ps.ATTRIBUTE9,
ps.ATTRIBUTE10,
ps.ATTRIBUTE11,
ps.ATTRIBUTE12,
ps.ATTRIBUTE13,
ps.ATTRIBUTE14,
ps.ATTRIBUTE15,
sysdate LAST_UPDATE_DATE,
0 LAST_UPDATED_BY,
sysdate CREATION_DATE,
0 CREATED_BY,
0 LAST_UPDATE_LOGIN,
0 APPLICATION_AMOUNT,
ps.CASH_RECEIPT_ID,
0 ORIGINAL_DISCOUNT_AMT,
ps.org_id,
0 PAYING_CUSTOMER_ID,
0 PAYING_SITE_USE_ID,
0 dispute_amt
FROM AR_PAYMENT_SCHEDULES ps,
RA_CUSTOMER_TRX_ALL ct,
HZ_CUST_ACCOUNTS acct,
RA_TERMS trm
WHERE ps.customer_id = p_customer_id
AND ( ps.CLASS = 'CM'
OR
ps.CLASS = 'PMT'
)
AND ps.customer_trx_id = ct.customer_trx_id(+)
AND nvl(ps.customer_site_use_id,-1) = nvl(p_customer_site_use_id, nvl(ps.customer_site_use_id,-1))
AND acct.cust_account_id = ps.customer_id
AND ps.status = 'OP'
AND ps.invoice_currency_code = p_currency_code
AND ps.term_id = trm.term_id(+))
WHERE AMOUNT_DUE_REMAINING < 0);
INSERT INTO AR_IREC_PAYMENT_LIST_GT
VALUES l_credit_transactions_list_rec(trx);
| Calculate discount and service charge on the selected
| invoices and update the amounts
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
|
| KNOWN ISSUES
|
|
|
| NOTES
| This procedure acts on the rows inserted in the global
| temporary table by the create_invoice_pay_list procedure.
| It is session specific.
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-Jan-2003 krmenon Created
| 26-Apr-2004 vnb Added Customer and Customer Site as input params.
| 10-Jun-2004 vnb Bug # 3458134 - Check if the grace days for discount option is
| enabled while calculating discount
| 19-Jul-2004 vnb Bug # 2830823 - Added exception block to handle exceptions
| 31-Dec-2004 vnb Bug 4071551 - Removed redundant code
| 07-Jul-2005 rsinthre Bug 4437220 - Payment amount not changed when discount recalculated
| 22-Mar-2010 nkanchan Bug 8293098 - Service change based on credit card types
| 18-May-2011 rsinthre Bug 12542249 - DISCOUNT AMT NOT COMING CORRECTLY FOR INSTALLMENT PAYMENT TERMS
| 11-Oct-2012 melapaku Bug 14672025 - DISCOUNT CALCULATION IS WRONG FOR FUTURE DATED PAYMENTS.
| 19-Oct-2012 melapaku Bug 14781706 - FUTURE DATED PAYMENT INCLUDING CREDIT MEMO FAILS WITH
| APPLY DATE MUST BE GREATER RECEIPT DATE
+============================================================*/
PROCEDURE cal_discount_and_service_chrg (p_customer_id IN NUMBER,
p_site_use_id IN NUMBER DEFAULT NULL,
p_receipt_date IN DATE DEFAULT trunc(SYSDATE),
p_payment_type IN varchar2 DEFAULT NULL,
p_lookup_code IN varchar2 DEFAULT NULL) IS
--l_invoice_list ARI_SERVICE_CHARGE_PKG.INVOICE_LIST_TABTYPE;
SELECT payment_schedule_id,
receipt_date,
payment_amt as payment_amount,
amount_due_remaining,
discount_amount,
customer_id,
account_number,
customer_trx_id,
currency_code,
service_charge,
trx_date
FROM AR_IREC_PAYMENT_LIST_GT
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(decode(p_site_use_id, -1, null, p_site_use_id), customer_site_use_id)
AND trx_class in ('INV','DEP', 'DM', 'CB', 'CM') --Modified for Bug 14781706
FOR UPDATE;
l_debug_info := 'Update transaction list with discount and receipt date';
UPDATE AR_IREC_PAYMENT_LIST_GT
SET discount_amount = l_discount_amount,
receipt_date = trunc(l_receipt_date),
payment_amt = l_payment_amount
WHERE CURRENT OF invoice_list;
SELECT decode(country,null, bank_home_country,country) country_code,bank_party_id,branch_party_id
FROM ce_bank_branches_V
WHERE branch_number = l_routing_number;
SELECT decode(bank_name,null,routing_number,bank_name) bank_name,
decode(bank_name,null,routing_number,bank_name) branch_name
FROM ar_bank_directory
WHERE routing_number = l_routing_number;
SELECT bank_party_id,branch_party_id, branch_number
FROM ce_bank_branches_V
WHERE upper(bank_name) = upper(l_bank_name);
select INSTRUMENT_PAYMENT_USE_ID,ORDER_OF_PREFERENCE,START_DATE from IBY_PMT_INSTR_USES_ALL
where instrument_id = l_bank_account_id
and EXT_PMT_PARTY_ID = (select EXT_PAYER_ID from IBY_EXTERNAL_PAYERS_ALL
where CUST_ACCOUNT_ID = p_customer_id
and ACCT_SITE_USE_ID = p_customer_site_id);
select ext_bank_account_id into l_bank_account_id from iby_ext_bank_accounts where BANK_ACCOUNT_NUM = p_account_number and bank_id = l_bank_id and branch_id = l_branch_id;
SELECT COUNT(*) INTO l_count
FROM IBY_ACCOUNT_OWNERS
WHERE ACCOUNT_OWNER_PARTY_ID = p_payer_party_id
AND EXT_BANK_ACCOUNT_ID = l_bank_account_id;
| Creates/Updates Credit card bill to location with the given details
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 17-Aug-2005 rsinthre Created
+============================================================*/
PROCEDURE create_cc_bill_to_site(
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_TRUE,
p_cc_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
p_payer_party_id IN NUMBER,
x_cc_bill_to_site_id IN OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_location_id NUMBER(15,0);
select hps.location_id, hl.object_version_number from hz_party_sites hps, hz_locations hl where party_site_id = x_cc_bill_to_site_id
and hps.location_id = hl.location_id;
SELECT *
FROM ar_irec_payment_list_gt
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(decode(p_site_use_id, -1, null, p_site_use_id), customer_site_use_id)
AND ( trx_class = 'CM'
OR
trx_class = 'PMT'
);
SELECT *
FROM ar_irec_payment_list_gt
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(decode(p_site_use_id, -1, null, p_site_use_id), customer_site_use_id)
AND ( trx_class = 'INV' OR
trx_class = 'DM' OR
trx_class = 'GUAR' OR
trx_class = 'CB' OR
trx_class = 'DEP'
)
ORDER BY amount_due_remaining ASC;
SELECT COUNT(*)
INTO total_trx_count
FROM ar_irec_payment_list_gt;
SELECT COUNT(*)
INTO credit_trx_list_count
FROM ar_irec_payment_list_gt
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(decode(p_site_use_id, -1, null, p_site_use_id), customer_site_use_id)
AND ( trx_class = 'CM' OR trx_class = 'PMT' );
SELECT count(*)
INTO debit_trx_list_count
FROM ar_irec_payment_list_gt
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(decode(p_site_use_id, -1, null, p_site_use_id), customer_site_use_id)
AND ( trx_class = 'INV' OR
trx_class = 'DM' OR
trx_class = 'GUAR' OR
trx_class = 'CB' OR
trx_class = 'DEP'
);
| invoices that have been selected for payment and return the
| total service charge that is to be applied.
|
| HISTORY
| 26-APR-2004 vnb Bug # 3467287 - Added Customer and Customer Site
| as input parameters.
| 19-JUL-2004 vnb Bug # 2830823 - Added exception block to handle exceptions
| 21-SEP-2004 vnb Bug # 3886652 - Added customer site use id to ARI_SERVICE_CHARGE_PKG.INVOICE_LIST_TABTYPE
| 22-Mar-2010 nkanchan Bug 8293098 - Service change based on credit card types
|
+=====================================================================*/
FUNCTION get_service_charge ( p_customer_id IN NUMBER,
p_site_use_id IN NUMBER DEFAULT NULL,
p_payment_type IN varchar2 DEFAULT NULL,
p_lookup_code IN varchar2 DEFAULT NULL)
RETURN NUMBER IS
l_invoice_list ARI_SERVICE_CHARGE_PKG.INVOICE_LIST_TABTYPE;
SELECT payment_schedule_id,
payment_amt as payment_amount,
customer_id,
customer_site_use_id,
account_number,
customer_trx_id,
currency_code,
service_charge
FROM AR_IREC_PAYMENT_LIST_GT
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(decode(p_site_use_id, -1, null, p_site_use_id), customer_site_use_id)
AND trx_class IN ('INV','DM','CB','DEP');
l_debug_info := 'Update service charge in the Payment GT';
UPDATE ar_irec_payment_list_gt
SET service_charge = l_service_charge
WHERE payment_schedule_id = l_invoice_list(l_count).payment_schedule_id;
arp_standard.debug('Error - Cannot update '||l_count);
| invoices that have been selected for payment and return the
| total service charge that is to be applied.
|
| HISTORY
| 26-APR-2004 vnb Bug # 3467287 - Added Customer and Customer Site
| as input parameters.
| 19-JUL-2004 vnb Bug # 2830823 - Added exception block to handle exceptions
| 21-SEP-2004 vnb Bug # 3886652 - Added customer site use id to ARI_SERVICE_CHARGE_PKG.INVOICE_LIST_TABTYPE
|
+=====================================================================*/
PROCEDURE apply_service_charge ( p_customer_id IN NUMBER,
p_site_use_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2) IS
l_invoice_list ARI_SERVICE_CHARGE_PKG.INVOICE_LIST_TABTYPE;
SELECT payment_schedule_id,
payment_amt as payment_amount,
customer_id,
customer_site_use_id,
account_number,
customer_trx_id,
currency_code,
service_charge,
receipt_date
FROM AR_IREC_PAYMENT_LIST_GT
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(decode(p_site_use_id, -1, null, p_site_use_id), customer_site_use_id)
AND ( trx_class = 'INV' OR
trx_class = 'DM' OR
trx_class = 'GUAR' OR
trx_class = 'CB' OR
trx_class = 'DEP'
);
SELECT PARTY_ID FROM HZ_CUST_ACCOUNTS WHERE CUST_ACCOUNT_ID = p_customer_id;
SELECT site_use.site_use_id into l_site_use_id
FROM hz_cust_site_uses site_use,
hz_cust_acct_sites acct_site
WHERE acct_site.cust_account_id = p_customer_id
AND acct_site.status = 'A'
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND site_use.site_use_code = nvl('BILL_TO',site_use.site_use_code)
AND site_use.status = 'A'
AND site_use.primary_flag = 'Y';
select payment_schedule_id into p_payment_schedule_id
from AR_IREC_PAYMENT_LIST_GT
where customer_id = p_customer_id
and customer_site_use_id = nvl(l_site_use_id, customer_site_use_id);
select currency_code into l_receipt_currency_code
from AR_IREC_PAYMENT_LIST_GT
where customer_id = p_customer_id
and customer_site_use_id = nvl(l_site_use_id, customer_site_use_id);
-- If iRec set up is not to save CC then, if update of CC fails we should roll back even create.
-- So here the commit flag is controlled by that profile
if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'ARI_UTILITIES.save_payment_instrument_info is true');
select 'ARI_'||ar_payment_server_ord_num_s.nextval
into l_payment_server_order_num
from dual;
l_debug_info := 'Apply the receipt to the transactions selected:call apply_cash';
select pr.home_country into l_home_country
from ar_cash_receipts_all cr,
ce_bank_acct_uses bau,
ce_bank_accounts cba,
hz_parties bank,
hz_organization_profiles pr
where cr.cash_receipt_id = p_cash_receipt_id
AND cr.remit_bank_acct_use_id = bau.bank_acct_use_id
AND bau.bank_account_id = cba.bank_account_id
AND cba.bank_id = bank.party_id
AND bank.party_id = pr.party_id;
fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Before Calling IBY_FNDCPT_SETUP_PUB.Update_Card .....');
IBY_FNDCPT_SETUP_PUB.Update_Card
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_card_instrument => l_create_credit_card,
x_response => l_result_rec_type
);
fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'Inside Else,Save payment instr set to yes..before update CC');
IBY_FNDCPT_SETUP_PUB.Update_Card
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_card_instrument => l_create_credit_card,
x_response => l_result_rec_type
);
SAVEPOINT ARI_Update_CC_Bill_To_Site_PVT;
l_debug_info := 'CC billing site update required';
update_cc_bill_to_site(
p_cc_location_rec => l_cc_location_rec,
x_cc_bill_to_site_id => p_cc_bill_to_site_id ,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ROLLBACK TO ARI_Update_CC_Bill_To_Site_PVT;
SELECT cr.receipt_number,
cr.amount,
cr.currency_code,
rc.creation_status,
cr.org_id,cr.payment_trxn_extension_id,
cr.receipt_method_id
FROM ar_cash_receipts cr,
ar_receipt_methods rm,
ar_receipt_classes rc
WHERE cr.cash_receipt_id = p_cash_receipt_id
AND cr.receipt_method_id = rm.receipt_method_id
and rm.receipt_class_id = rc.receipt_class_id;
l_debug_info := 'update cash receipt with authorization code and payment server order id';
ARP_CASH_RECEIPTS_PKG.update_p(l_cr_rec, p_cash_receipt_id);
write_debug_and_log('CR rec updated with payment server auth code');
| based on the selected list .
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
| p_payment_schedule_id IN NUMBER
| p_customer_id IN NUMBER
| p_customer_site_id IN NUMBER
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 27-JUN-2003 yreddy Created
| 31-DEC-2004 vnb Bug 4071551 - Modified for avoiding redundant code
| 20-Jan-2005 vnb Bug 4117211 - Original discount amount column added for ease of resetting payment amounts
| 26-May-05 rsinthre Bug # 4392371 - OIR needs to support cross customer payment
| 08-Jul-2005 rsinthre Bug 4437225 - Disputed amount against invoice not displayed during payment
| 08-Jun-2010 nkanchan Bug # 9696274 - PAGE ERRORS OUT ON NAVIGATING 'PAY BELOW' RELATED CUSTOMER DATA
+============================================================*/
PROCEDURE create_transaction_list_record( p_payment_schedule_id IN NUMBER,
p_customer_id IN NUMBER,
p_customer_site_id IN NUMBER
) IS
l_query_period NUMBER(15);
select class, amount_due_remaining, cash_receipt_id, ps.CUSTOMER_ID, ct.PAYING_CUSTOMER_ID, ps.CUSTOMER_SITE_USE_ID,ct.PAYING_SITE_USE_ID, ps.customer_trx_id,
(decode( nvl(AMOUNT_DUE_ORIGINAL,0),0,1,(AMOUNT_DUE_ORIGINAL/abs(AMOUNT_DUE_ORIGINAL)) ) *abs(nvl(amount_in_dispute,0)) )
into l_trx_class, l_amount_due_remaining, l_cash_receipt_id, l_pay_for_cust_id, l_paying_cust_id, l_pay_for_cust_site_id, l_paying_cust_site_id, l_customer_trx_id, l_dispute_amount
from ar_payment_schedules ps, ra_customer_trx_all ct
where ps.CUSTOMER_TRX_ID = ct.CUSTOMER_TRX_ID(+)
and ps.payment_schedule_id = p_payment_schedule_id;
select -sum(app.amount_applied)
into l_amount_due_remaining
from ar_receivable_applications app
where nvl( app.confirmed_flag, 'Y' ) = 'Y'
and app.status = 'UNAPP'
and app.cash_receipt_id = l_cash_receipt_id;
INSERT INTO AR_IREC_PAYMENT_LIST_GT
( CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
ACCOUNT_NUMBER,
CUSTOMER_TRX_ID,
TRX_NUMBER,
PAYMENT_SCHEDULE_ID,
TRX_DATE,
DUE_DATE,
STATUS,
TRX_CLASS,
PO_NUMBER,
SO_NUMBER,
CURRENCY_CODE,
AMOUNT_DUE_ORIGINAL,
AMOUNT_DUE_REMAINING,
DISCOUNT_AMOUNT,
SERVICE_CHARGE,
PAYMENT_AMT,
PAYMENT_TERMS,
NUMBER_OF_INSTALLMENTS,
TERMS_SEQUENCE_NUMBER,
LINE_AMOUNT,
TAX_AMOUNT,
FREIGHT_AMOUNT,
FINANCE_CHARGES,
RECEIPT_DATE,
PRINTING_OPTION,
INTERFACE_HEADER_CONTEXT,
INTERFACE_HEADER_ATTRIBUTE1,
INTERFACE_HEADER_ATTRIBUTE2,
INTERFACE_HEADER_ATTRIBUTE3,
INTERFACE_HEADER_ATTRIBUTE4,
INTERFACE_HEADER_ATTRIBUTE5,
INTERFACE_HEADER_ATTRIBUTE6,
INTERFACE_HEADER_ATTRIBUTE7,
INTERFACE_HEADER_ATTRIBUTE8,
INTERFACE_HEADER_ATTRIBUTE9,
INTERFACE_HEADER_ATTRIBUTE10,
INTERFACE_HEADER_ATTRIBUTE11,
INTERFACE_HEADER_ATTRIBUTE12,
INTERFACE_HEADER_ATTRIBUTE13,
INTERFACE_HEADER_ATTRIBUTE14,
INTERFACE_HEADER_ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CASH_RECEIPT_ID,
ORIGINAL_DISCOUNT_AMT,
ORG_ID,
PAY_FOR_CUSTOMER_ID,
PAY_FOR_CUSTOMER_SITE_ID,
DISPUTE_AMT
)
SELECT l_paying_cust_id,
decode(l_paying_cust_site_id, null, -1,to_number(''), -1, l_paying_cust_site_id),
acct.ACCOUNT_NUMBER,
ps.CUSTOMER_TRX_ID,
ps.TRX_NUMBER,
ps.PAYMENT_SCHEDULE_ID,
ps.TRX_DATE,
ps.DUE_DATE,
ps.STATUS,
ps.class,
ct.PURCHASE_ORDER AS PO_NUMBER,
NULL AS SO_NUMBER,
ps.INVOICE_CURRENCY_CODE,
ps.AMOUNT_DUE_ORIGINAL,
l_amount_due_remaining,
l_discount_amount,
0,
DECODE(ps.class, 'PMT', l_amount_due_remaining, 'CM', l_amount_due_remaining,
ARI_UTILITIES.curr_round_amt(l_amount_due_remaining-l_discount_amount -l_dispute_amount,ps.INVOICE_CURRENCY_CODE)),
trm.name term_desc,
ARPT_SQL_FUNC_UTIL.Get_Number_Of_Due_Dates(ps.term_id) number_of_installments,
ps.terms_sequence_number,
ps.amount_line_items_original line_amount,
ps.tax_original tax_amount,
ps.freight_original freight_amount,
ps.receivables_charges_charged finance_charge,
case WHEN ((trunc(ps.trx_date) - trunc(sysdate)) <= 0) then TRUNC(SYSDATE) else ps.trx_date end as receipt_date,
ct.printing_option,
ct.INTERFACE_HEADER_CONTEXT,
ct.INTERFACE_HEADER_ATTRIBUTE1,
ct.INTERFACE_HEADER_ATTRIBUTE2,
ct.INTERFACE_HEADER_ATTRIBUTE3,
ct.INTERFACE_HEADER_ATTRIBUTE4,
ct.INTERFACE_HEADER_ATTRIBUTE5,
ct.INTERFACE_HEADER_ATTRIBUTE6,
ct.INTERFACE_HEADER_ATTRIBUTE7,
ct.INTERFACE_HEADER_ATTRIBUTE8,
ct.INTERFACE_HEADER_ATTRIBUTE9,
ct.INTERFACE_HEADER_ATTRIBUTE10,
ct.INTERFACE_HEADER_ATTRIBUTE11,
ct.INTERFACE_HEADER_ATTRIBUTE12,
ct.INTERFACE_HEADER_ATTRIBUTE13,
ct.INTERFACE_HEADER_ATTRIBUTE14,
ct.INTERFACE_HEADER_ATTRIBUTE15,
ct.ATTRIBUTE_CATEGORY,
ct.ATTRIBUTE1,
ct.ATTRIBUTE2,
ct.ATTRIBUTE3,
ct.ATTRIBUTE4,
ct.ATTRIBUTE5,
ct.ATTRIBUTE6,
ct.ATTRIBUTE7,
ct.ATTRIBUTE8,
ct.ATTRIBUTE9,
ct.ATTRIBUTE10,
ct.ATTRIBUTE11,
ct.ATTRIBUTE12,
ct.ATTRIBUTE13,
ct.ATTRIBUTE14,
ct.ATTRIBUTE15,
ps.cash_receipt_id,
l_discount_amount,
ps.org_id,
l_pay_for_cust_id,
--Bug 4062938 - Handling of transactions with no site id
decode(ps.customer_site_use_id, null, -1,ps.customer_site_use_id) as CUSTOMER_SITE_USE_ID,
(decode( nvl(ps.AMOUNT_DUE_ORIGINAL,0),0,1,(ps.AMOUNT_DUE_ORIGINAL/abs(ps.AMOUNT_DUE_ORIGINAL)) ) *abs(nvl(ps.amount_in_dispute,0)) )
FROM AR_PAYMENT_SCHEDULES ps,
RA_CUSTOMER_TRX_ALL ct,
HZ_CUST_ACCOUNTS acct,
RA_TERMS trm
WHERE ps.payment_schedule_id = p_payment_schedule_id
AND ps.CLASS IN ('INV', 'DM', 'GUAR', 'CB', 'DEP', 'CM', 'PMT' ) -- CCA - hikumar
AND ps.customer_trx_id = ct.customer_trx_id(+)
AND acct.cust_account_id = ps.customer_id
AND ps.term_id = trm.term_id(+);
SELECT arm.receipt_method_id receipt_method_id,
arc.creation_status receipt_creation_status
FROM ar_receipt_methods arm,
ar_receipt_method_accounts arma,
ce_bank_acct_uses_ou_v aba,
ce_bank_accounts cba,
ar_receipt_classes arc
WHERE arm.payment_channel_code = 'CREDIT_CARD'
AND arm.receipt_method_id = NVL( to_number(fnd_profile.VALUE('OIR_CC_PMT_METHOD')), arm.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
AND (cba.currency_code = p_currency_code OR cba.receipt_multi_currency_flag = 'Y')
AND TRUNC(nvl(aba.end_date,sysdate)) >= TRUNC(sysdate)
AND TRUNC(sysdate) BETWEEN TRUNC(nvl(arm.start_date, sysdate)) AND TRUNC(nvl(arm.end_date, sysdate))
AND TRUNC(sysdate) BETWEEN TRUNC(arma.start_date) AND TRUNC(nvl(arma.end_date, sysdate));
SELECT /*+ leading(rc) */ count(irec_cc_receipt_method_id)
INTO system_cc_payment_method
FROM ar_system_parameters sp,
ar_receipt_methods rm,
ar_receipt_method_accounts rma,
ce_bank_accounts cba,
ce_bank_acct_uses_ou_v ba,
ar_receipt_classes rc
WHERE sp.irec_cc_receipt_method_id = rm.receipt_method_id
AND rma.receipt_method_id = rm.receipt_method_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND ba.bank_account_id = cba.bank_account_id
AND ( cba.currency_code = p_currency_code
OR
cba.receipt_multi_currency_flag = 'Y' )
AND sysdate < nvl(ba.end_date, SYSDATE+1)
AND sysdate between rma.start_date and nvl(rma.end_date, SYSDATE)
AND sysdate between rm.start_date and NVL(rm.end_date, SYSDATE)
/* Commented for bug 12670265
AND (
save_payment_inst_info_wrapper(p_customer_id,p_customer_site_id) = 'true'
OR
-- If the one time payment is true , then ensure that the receipt
-- class is set for one step remittance.
rc.creation_status IN ('REMITTED','CLEARED')) */
and rc.receipt_class_id = rm.receipt_class_id;
SELECT count ( arm.receipt_method_id )
INTO customer_cc_payment_method
FROM ar_receipt_methods arm,
ra_cust_receipt_methods rcrm,
ar_receipt_method_accounts arma,
ce_bank_acct_uses_ou_v aba,
ce_bank_accounts cba,
ar_receipt_classes arc
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.remit_bank_acct_use_id = aba.bank_acct_use_id
AND aba.bank_account_id = cba.bank_account_id
AND ( NVL(rcrm.site_use_id,p_customer_site_id) = p_customer_site_id
OR
(p_customer_site_id is null and rcrm.site_use_id is null)
)
AND (
cba.currency_code = p_currency_code
OR
cba.receipt_multi_currency_flag = 'Y'
)
-- Bug#6109909
-- AND arm.payment_type_code = 'CREDIT_CARD'
AND arm.payment_channel_code = 'CREDIT_CARD'
AND arc.creation_method_code = 'AUTOMATIC'
-- AND aba.set_of_books_id = arp_trx_global.system_info.system_parameters.set_of_books_id
AND sysdate < NVL ( aba.end_date , sysdate+1)
AND sysdate between arm.start_date AND NVL(arm.end_date, sysdate)
AND sysdate between arma.start_date AND NVL(arma.end_date, sysdate)
/* Commented for bug 12670265
AND (
( save_payment_inst_info_wrapper(p_customer_id,p_customer_site_id) = 'true' )
OR
( -- If the one time payment is true , then ensure that the receipt
-- class is set for one step remittance.
arc.creation_status IN ('REMITTED','CLEARED')
)
) */
;
SELECT arm.receipt_method_id receipt_method_id,
arc.creation_status receipt_creation_status
FROM ar_receipt_methods arm,
ar_receipt_method_accounts arma,
ce_bank_acct_uses_ou_v aba,
ce_bank_accounts cba,
ar_receipt_classes arc
WHERE NVL(arm.payment_channel_code,'NONE') <> 'CREDIT_CARD'
AND arm.receipt_method_id = NVL( to_number(fnd_profile.VALUE('OIR_BA_PMT_METHOD')), arm.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
AND (cba.currency_code = p_currency_code OR cba.receipt_multi_currency_flag = 'Y')
AND TRUNC(nvl(aba.end_date,sysdate)) >= TRUNC(sysdate)
AND TRUNC(sysdate) BETWEEN TRUNC(nvl(arm.start_date, sysdate)) AND TRUNC(nvl(arm.end_date, sysdate))
AND TRUNC(sysdate) BETWEEN TRUNC(arma.start_date) AND TRUNC(nvl(arma.end_date, sysdate));
SELECT count(irec_ba_receipt_method_id) /* J Rautiainen ACH Implementation */
INTO system_bank_payment_method
FROM ar_system_parameters sp,
ar_receipt_methods rm,
ar_receipt_method_accounts rma,
ce_bank_acct_uses_ou_v ba,
ce_bank_accounts cba
WHERE sp.irec_ba_receipt_method_id = rm.receipt_method_id
AND rma.receipt_method_id = rm.receipt_method_id
AND rma.remit_bank_acct_use_id = ba.bank_acct_use_id
AND ba.bank_account_id = cba.bank_account_id
AND ( cba.currency_code = p_currency_code
OR cba.receipt_multi_currency_flag = 'Y')
AND sysdate < nvl(ba.end_date, SYSDATE+1)
AND sysdate between rma.start_date and nvl(rma.end_date, SYSDATE)
AND sysdate between rm.start_date and NVL(rm.end_date, SYSDATE);
SELECT count ( arm.receipt_method_id )
INTO customer_bank_payment_method
FROM ar_receipt_methods arm,
ra_cust_receipt_methods rcrm,
ar_receipt_method_accounts arma,
ce_bank_acct_uses_ou_v aba,
ce_bank_accounts cba,
ar_receipt_classes arc
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.remit_bank_acct_use_id = aba.bank_acct_use_id
AND aba.bank_account_id = cba.bank_account_id
AND ( NVL(rcrm.site_use_id,p_customer_site_id) = p_customer_site_id
OR
(p_customer_site_id is null and rcrm.site_use_id is null)
)
AND (
cba.currency_code = p_currency_code
OR
cba.receipt_multi_currency_flag = 'Y'
)
AND ( arc.remit_flag = 'Y'
and arc.confirm_flag = 'N'
)
AND (
arc.creation_method_code = 'MANUAL'
or
--Bug#6109909
( arm.payment_channel_code = 'BANK_ACCT_XFER'
and arc.creation_method_code = 'AUTOMATIC' )
)
-- AND aba.set_of_books_id = arp_trx_global.system_info.system_parameters.set_of_books_id
AND sysdate < NVL ( aba.end_date , sysdate+1)
AND sysdate between arm.start_date AND NVL(arm.end_date, sysdate)
AND sysdate between arma.start_date AND NVL(arma.end_date, sysdate) ;
SELECT CUSTOMER_ID, CUSTOMER_SITE_USE_ID
INTO l_customer_id, l_customer_site_use_id
FROM ar_payment_schedules
WHERE PAYMENT_SCHEDULE_ID = p_ps_id;
l_debug_info := 'Update transaction list with original discount and payment amount';
UPDATE AR_IREC_PAYMENT_LIST_GT
SET discount_amount = original_discount_amt,
payment_amt = amount_due_remaining - original_discount_amt - nvl(dispute_amt,0)
WHERE customer_id = p_customer_id
AND customer_site_use_id = nvl(decode(p_site_use_id, -1, null, p_site_use_id),customer_site_use_id);
| payment that has been selected for apply credit andd return the
| total amount dure remaining that can be applied.
|
| HISTORY
|
+=====================================================================*/
FUNCTION get_pymt_amnt_due_remaining ( p_cash_receipt_id IN NUMBER) RETURN NUMBER IS
l_amount_due_remaining NUMBER ;
select - sum(app.amount_applied) INTO l_amount_due_remaining
from ar_receivable_applications app
where nvl( app.confirmed_flag, 'Y' ) = 'Y'
AND app.status = 'UNAPP'
AND app.cash_receipt_id = p_cash_receipt_id;
| procedure update_cc_bill_to_site
|
| DESCRIPTION
| Creates/Updates Credit card bill to location with the given details
|
| PSEUDO CODE/LOGIC
|
| PARAMETERS
|
| KNOWN ISSUES
|
|
|
| NOTES
|
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 17-Aug-2005 rsinthre Created
+============================================================*/
PROCEDURE update_cc_bill_to_site(
p_cc_location_rec IN HZ_LOCATION_V2PUB.LOCATION_REC_TYPE,
x_cc_bill_to_site_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_location_id NUMBER(15,0);
select hps.location_id, hl.object_version_number
from hz_party_sites hps, hz_locations hl
where party_site_id = x_cc_bill_to_site_id
and hps.location_id = hl.location_id;
l_procedure_name := '.update_cc_bill_to_site';
l_debug_info := 'Call TCA update location - update_location - to update location for CC';
write_debug_and_log('Site_id_to_update'|| x_cc_bill_to_site_id);
write_debug_and_log('Loaction id to update:'|| l_location_id);
HZ_LOCATION_V2PUB.update_location(
p_init_msg_list => FND_API.G_TRUE,
p_location_rec => l_location_rec,
p_object_version_number => l_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END update_cc_bill_to_site;
| PROCEDURE update_invoice_payment_status
|
| DESCRIPTION
| This procedure will update the PAYMENT_APPROVAL column in ar_payment_schedules
| with the value p_inv_pay_status for the records in p_payment_schedule_id_list
|
| PARAMETERS
| p_payment_schedule_id_list IN Inv_list_table_type
| p_inv_pay_status IN VARCHAR2
|
| HISTORY
| 17-FEB-2007 abathini Created
|
+=====================================================================*/
PROCEDURE update_invoice_payment_status( p_payment_schedule_id_list IN Inv_list_table_type,
p_inv_pay_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_last_update_login NUMBER(15);
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login := FND_GLOBAL.LOGIN_ID;
l_last_update_date := sysdate;
l_last_updated_by := FND_GLOBAL.USER_ID;
UPDATE AR_PAYMENT_SCHEDULES set PAYMENT_APPROVAL = p_inv_pay_status,
LAST_UPDATE_DATE = l_last_update_date, LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login
where payment_schedule_id = p_payment_schedule_id_list(trx);
END update_invoice_payment_status;
| else, checks if the transactions selected by the user belongs
| to a same site. If yes, then return that site id else, returns -1.
|
| PARAMETERS
| p_session_id IN NUMBER
| p_customer_id IN NUMBER
|
| RETURN
| l_customer_site_use_id NUMBER
| HISTORY
| 29-Oct-2009 rsinthre Created
|
+=====================================================================*/
FUNCTION get_customer_site_use_id (p_session_id IN NUMBER,
p_customer_id IN NUMBER
)
RETURN NUMBER
IS
l_customer_site_use_id NUMBER;
SELECT DISTINCT pay_for_customer_site_id
FROM ar_irec_payment_list_gt
WHERE customer_id = p_customer_id;
SELECT usite.customer_site_use_id
INTO l_customer_site_use_id
FROM ar_irec_user_acct_sites_all usite,
hz_cust_site_uses hzcsite
WHERE
usite.session_id = p_session_id
AND usite.customer_id = p_customer_id
AND usite.user_id = FND_GLOBAL.user_id
AND hzcsite.site_use_id = usite.customer_site_use_id
AND hzcsite.primary_flag = 'Y'
AND hzcsite.site_use_code = 'BILL_TO'
AND hzcsite.status = 'A' ;
Check, if the selected transactions belong to a same site. If yes, then return that site id else return -1.
*/
OPEN get_cust_site_use_id_cur;
fnd_log.string(fnd_log.LEVEL_STATEMENT,G_PKG_NAME||l_procedure_name,'The selected transactions belong to more than one site');