The following lines contain the word 'select', 'insert', 'update' or 'delete':
select location_id, party_id
from hz_party_sites
where party_site_id = c_party_site_id;
select ord_oneclick_id, enabled_flag
from ibe_ord_oneclick
where party_id = c_party_id and cust_account_id = c_cust_id;
if (p_operation_code = 'UPDATE') then
l_card_instrument.expiration_date := p_exp_date;
if (p_operation_code = 'DELETE') then
l_card_instrument.inactive_date := SYSDATE;
select count(*) into l_primary_card_present
from IBY_FNDCPT_PAYER_ASSGN_INSTR_V
where party_id = p_party_id
and order_of_preference = 1
and cust_account_id is null
and org_id is null
and acct_site_use_id is null
and instrument_type = 'CREDITCARD'
and payment_function = 'CUSTOMER_PAYMENT'
and card_number is not null;
select nvl(max(order_of_preference),0)+1 into l_order_of_preference
from IBY_FNDCPT_PAYER_ASSGN_INSTR_V
where party_id = p_party_id
and cust_account_id is null
and org_id is null
and acct_site_use_id is null
and instrument_type = 'CREDITCARD'
and payment_function = 'CUSTOMER_PAYMENT'
and card_number is not null;
/* select nvl(max(order_of_preference),0)+1 into l_order_of_preference
from IBY_FNDCPT_PAYER_ASSGN_INSTR_V
where party_id = p_party_id;
if (p_operation_code = 'UPDATE' or p_operation_code = 'DELETE') then
IBY_FNDCPT_SETUP_PUB.Update_Card(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_card_instrument => l_card_instrument
,x_response => lx_response);
if l_oneclick_enabled = 'Y' and p_operation_code = 'DELETE' then
IF (IBE_UTIL.G_DEBUGON = l_true) THEN
ibe_util.debug('Entering the loop :'||p_operation_code);
IBE_UTIL.debug('call process_credit_card - Going to call ibe_ord_oneclick_pvt.Update_Settings()');
ibe_ord_oneclick_pvt.Update_Settings(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_party_id => p_party_id
,p_acct_id => p_cust_id
,p_assignment_id => l_assignment_attr.Assignment_Id);
SELECT payment_id, party_id, cust_account_id, org_id
FROM ibe_ord_oneclick_all;
SELECT instr_assignment_id
FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
WHERE instr_assignment_id = c_pmt_id
AND party_id = c_party_id
AND org_id = c_org_id
AND cust_account_id = c_cust_accnt_id
AND acct_site_use_id IS NULL
AND instrument_type = 'CREDITCARD'
AND payment_function = 'CUSTOMER_PAYMENT';
SELECT instrument_id
FROM IBY_UPG_INSTRUMENTS
WHERE bank_account_id = c_pmt_id
and rownum <2 ;
SELECT instr_assignment_id
FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
WHERE instrument_id = c_instr_id
AND party_id = c_party_id
AND cust_account_id = c_cust_accnt_id
AND org_id = c_org_id
AND acct_site_use_id IS NULL;
print_debug_log('Original payment id before update: '||l_oneclick_payment_id);
print_debug_log('New payment id(actually assignment_id) after update: '||l_instr_assignment_id);
UPDATE IBE_ORD_ONECLICK_ALL
SET PAYMENT_ID = l_instr_assignment_id
WHERE party_id = rec_oneclick_data.party_id
AND cust_account_id = rec_oneclick_data.cust_account_id
AND org_id = rec_oneclick_data.org_id;
SELECT user_name, preference_value
FROM fnd_user_preferences
WHERE module_name = 'IBE'
AND preference_name = 'PRIMARY_CARD';
SELECT INSTR_ASSIGNMENT_ID, order_of_preference
FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
WHERE instrument_id = c_instrument_id
AND party_id = c_party_id
AND org_id IS NULL
AND cust_account_id IS NULL
AND acct_site_use_id IS NULL;
Select INSTRUMENT_PAYMENT_USE_ID
from IBY_PMT_INSTR_USES_ALL
where INSTRUMENT_PAYMENT_USE_ID = c_assignment_id;
select EXT_PAYER_ID
from IBY_EXTERNAL_PAYERS_ALL
where party_id = c_party_id
AND org_id IS NULL
AND cust_account_id IS NULL
AND acct_site_use_id IS NULL;
SELECT customer_id, person_party_id
FROM fnd_user
WHERE user_name = c_user_name;
SELECT instrument_id into l_instrument_id
FROM IBY_UPG_INSTRUMENTS
WHERE bank_account_id = rec_get_users_w_primary_cc_set.preference_value
and rownum < 2;
UPDATE IBY_PMT_INSTR_USES_ALL
SET order_of_preference = 1
WHERE INSTRUMENT_PAYMENT_USE_ID = l_INSTRUMENT_PAYMENT_USE_ID;
print_debug_log('Updated IBY_PMT_INSTR_USES_ALL');
print_debug_log(' INSTRUMENT_PAYMENT_USE_ID Record updated: '||l_instrument_assignment_id);
UPDATE iby_pmt_instr_uses_all
SET order_of_preference = order_of_preference + 1,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number + 1
WHERE instrument_payment_use_id IN
(
SELECT instrument_payment_use_id
FROM iby_pmt_instr_uses_all
WHERE (ext_pmt_party_id = l_party_to_use)
AND (payment_flow = 'FUNDS_CAPTURE')
START WITH order_of_preference = l_order_of_preference
AND (ext_pmt_party_id = l_party_to_use)
AND (payment_flow = 'FUNDS_CAPTURE')
CONNECT BY order_of_preference = PRIOR (order_of_preference + 1)
AND (ext_pmt_party_id = PRIOR ext_pmt_party_id)
AND (payment_flow = PRIOR payment_flow) );
print_debug_log('Going for direct inserts into IBY_EXTERNAL_PAYERS_ALL');
select IBY_EXTERNAL_PAYERS_ALL_S.Nextval into l_ext_payer_id
from dual;
INSERT INTO IBY_EXTERNAL_PAYERS_ALL(
EXT_PAYER_ID ,
PAYMENT_FUNCTION ,
PARTY_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER )
VALUES(
l_ext_payer_id ,
'CUSTOMER_PAYMENT' ,
l_party_to_use ,
fnd_global.USER_ID ,
SYSDATE ,
fnd_global.USER_ID ,
SYSDATE ,
fnd_global.USER_ID ,
1 );
Select ext_payer_id into l_ext_payer_id_verify
from IBY_EXTERNAL_PAYERS_ALL
where ext_payer_id = l_ext_payer_id;
print_debug_log('Successfully inserted into IBY_EXTERNAL_PAYERS_ALL');
print_debug_log('Record ID inserted : '||l_ext_payer_id_verify);
print_debug_log('Doing a direct insert into IBY_PMT_INSTR_USES_ALL');
INSERT INTO IBY_PMT_INSTR_USES_ALL(
INSTRUMENT_PAYMENT_USE_ID ,
PAYMENT_FLOW ,
EXT_PMT_PARTY_ID ,
INSTRUMENT_TYPE ,
INSTRUMENT_ID ,
PAYMENT_FUNCTION ,
ORDER_OF_PREFERENCE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER ,
START_DATE )
VALUES(
l_instrument_assignment_id,
'FUNDS_CAPTURE' ,
l_party_to_use ,
'CREDITCARD' ,
l_instrument_id ,
'CUSTOMER_PAYMENT' ,
1 ,
fnd_global.USER_ID ,
SYSDATE ,
fnd_global.USER_ID ,
SYSDATE ,
fnd_global.USER_ID ,
1 ,
SYSDATE );
print_debug_log('Inserted a record in IBY_PMT_INSTR_USES_ALL');
print_debug_log('Inserted a record for l_instrument_assignment_use_id '||l_instrument_assignment_id);
print_debug_log('Inserted a record for l_instrument_id '||l_instrument_id);