[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_card_flag IN VARCHAR2 DEFAULT 'N',
P_x_trxn_extension_id IN OUT NOCOPY NUMBER,
X_return_status OUT NOCOPY VARCHAR2,
X_msg_count OUT NOCOPY NUMBER,
X_msg_data OUT NOCOPY VARCHAR2)
IS
--R12 CC Encryption
L_credit_card_rec IBY_FNDCPT_SETUP_PUB.CreditCard_rec_Type;
select arm.payment_channel_code
into l_pmt_channel_code
from ar_receipt_methods arm
where arm.receipt_method_id = p_receipt_method_id;
Select INVOICE_TO_ORG_ID,ORG_ID into l_invoice_to_org_id,l_org_id
from oe_order_lines_all where header_id = p_header_id
and line_id = p_line_id;
select invoice_to_org_id,org_id into l_invoice_to_org_id,l_org_id
from oe_order_headers_all where header_id = p_header_id;
Select hca.party_id, acct_site.cust_account_id,acct_site.party_site_id
Into l_party_id, l_cust_account_id,l_party_site_id
From HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_CUST_ACCOUNTS_ALL HCA
Where SITE.SITE_USE_ID = p_site_use_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
SELECT ifapc.instrument_type
INTO l_instrument_type
FROM iby_fndcpt_all_pmt_channels_v ifapc
WHERE ifapc.payment_channel_code = l_pmt_channel_code;
SELECT INSTRUMENT_ID into
l_instrument_id from
IBY_FNDCPT_PAYER_ASSGN_INSTR_V
where INSTR_ASSIGNMENT_ID = p_payment_trx_id;
IF p_update_card_flag = 'Y' AND p_instrument_id is not null THEN
L_credit_card_rec.expiration_date := p_exp_date;
oe_debug_pub.add('Before call to Update_Card API....');
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 => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data,
p_card_instrument => l_credit_card_rec,
x_response => l_response_code
);
oe_debug_pub.add('Result error code in Update_Card exp'||l_response_code.result_code);
oe_debug_pub.add('Result error code in Update_Card unxc'||l_response_code.result_code);
oe_debug_pub.add('Update_Card Successful....');
select instr_assignment_id into l_assign_id
from IBY_EXTN_INSTR_DETAILS_V where trxn_extension_id = p_x_trxn_extension_id;
UPDATE oe_payments
SET credit_card_approval_code = NULL
WHERE header_id = p_header_id
AND line_id = p_line_id;
Procedure Update_Payment_Trxn
(p_header_id IN NUMBER,
P_line_id IN NUMBER,
p_cust_id IN NUMBER,
P_site_use_id IN NUMBER,
p_payment_trx_id IN NUMBER,
p_payment_type_code IN VARCHAR2,
p_payment_number IN NUMBER, --New
p_card_number IN VARCHAR2,
P_card_code IN VARCHAR2,
p_card_holder_name IN VARCHAR2,
p_exp_date IN DATE,
p_instrument_security_code IN VARCHAR2,
--Bug 7460481 starts
P_credit_card_approval_code IN VARCHAR2 DEFAULT NULL,
P_credit_card_approval_date IN DATE DEFAULT NULL,
--Bug 7460481 ends
p_instrument_id IN NUMBER DEFAULT NULL,
p_instrument_assignment_id IN NUMBER DEFAULT NULL,
p_receipt_method_id IN NUMBER,
p_update_card_flag IN VARCHAR2 DEFAULT 'N',
p_trxn_extension_id IN OUT NOCOPY NUMBER, --bug 4885313
X_return_status OUT NOCOPY VARCHAR2,
X_msg_count OUT NOCOPY NUMBER,
X_msg_data OUT NOCOPY VARCHAR2)
IS
--R12 CC Encryption
L_return_status VARCHAR2(30);
oe_debug_pub.add('Entering OE_PAYMENT_TRXN_UTIL.Update_Payment_Trxn...');
select arm.payment_channel_code
into l_pmt_channel_code
from ar_receipt_methods arm
where arm.receipt_method_id = p_receipt_method_id;
SELECT ifapc.instrument_type
INTO l_instrument_type
FROM iby_fndcpt_all_pmt_channels_v ifapc
WHERE ifapc.payment_channel_code = l_pmt_channel_code;
Select INVOICE_TO_ORG_ID,ORG_ID into l_invoice_to_org_id,l_org_id
from oe_order_lines_all where header_id = p_header_id and line_id = p_line_id;
select invoice_to_org_id,org_id into l_invoice_to_org_id,l_org_id
from oe_order_headers_all where header_id = p_header_id;
Select hca.party_id, acct_site.cust_account_id,acct_site.party_site_id
Into l_party_id, l_cust_account_id,l_party_site_id
From HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_CUST_ACCOUNTS_ALL HCA
Where SITE.SITE_USE_ID = p_site_use_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
SELECT INSTRUMENT_ID into
l_instrument_id from
IBY_FNDCPT_PAYER_ASSGN_INSTR_V
where INSTR_ASSIGNMENT_ID = p_payment_trx_id;
IF p_update_card_flag = 'Y' AND l_instrument_id is not null THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('Before calling 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 => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data,
p_card_instrument => l_credit_card_rec,
x_response => l_response_code
);
oe_debug_pub.add('Result error code in Update_Card exp'||l_response_code.result_code);
oe_debug_pub.add('Result error code in Update_Card unxc'||l_response_code.result_code);
oe_debug_pub.add('Update_Card Successful....');
END IF; --update card flag
--
IF l_debug_level > 0 THEN
oe_debug_pub.add('Calling update transaction extension...');
--if the Security code is null to the payments API to update
--the value appropriately.
IF p_instrument_security_code is not null then
l_trxn_attribs.Instrument_Security_Code := p_instrument_security_code;
oe_debug_pub.add('Values passed to Update_trxn_extn');
SELECT AUTHORIZATION_CODE into
l_approval_code FROM IBY_TRXN_EXT_AUTHS_V
WHERE TRXN_EXTENSION_ID = p_trxn_extension_id
--12694453 AND INITIATOR_EXTENSION_ID = p_trxn_extension_id; -- bug 9145261/9335940
/*SELECT nvl(settled_flag, 'N'),instrument_id,card_number
INTO l_settled_flag,l_old_instrument_id,l_old_card_number
FROM iby_trxn_extensions_v
WHERE trxn_extension_id = p_trxn_extension_id;*/
SELECT nvl(settled_flag, 'N'),instrument_id,card_number
INTO l_settled_flag,l_old_instrument_id,l_old_card_number
FROM IBY_EXTN_INSTR_DETAILS_V iextn,
IBY_EXTN_SETTLEMENTS_V iset
WHERE
iextn.trxn_extension_id= iset.trxn_extension_id (+) AND
iextn.trxn_extension_id = p_trxn_extension_id;
--call update_transaction API for this trxn_extension_id
IF (l_approval_code IS NOT NULL AND
NOT OE_GLOBALS.Equal(l_approval_code,FND_API.G_MISS_CHAR))
OR l_settled_flag = 'Y' THEN
IF l_settled_flag = 'N' THEN
-- need to re-authorize if the authorization has expired.
-- effective_auth_amount of 0 indicates auth has expired.
-- the auth would be valid if authorization_amount is equal to
-- effective_auth_amount
BEGIN
SELECT effective_auth_amount
INTO l_effective_auth_amount
FROM iby_trxn_ext_auths_v
WHERE trxn_extension_id = p_trxn_extension_id
--12694453 AND INITIATOR_EXTENSION_ID = p_trxn_extension_id -- bug 9335940/9145261
AND nvl(authorization_amount,0) > 0
AND authorization_status=0;
-- update oe_payments table
p_trxn_extension_id := l_trxn_extension_id;
select trxn_ref_number2
into l_trxn_ref_number2
from IBY_EXTN_INSTR_DETAILS_V
where trxn_extension_id = p_trxn_extension_id;
--as the old trxn extension id was deleted
p_trxn_extension_id := l_trxn_extension_id ;
--So can call update transaction API to update the required details for this
--trxn extension id.
ELSE
--Bug 7460481 starts
IF p_payment_type_code = 'CREDIT_CARD'
THEN
IF p_credit_card_approval_code IS NOT NULL THEN
L_trxn_attribs.VoiceAuth_flag := 'Y';
IBY_Fndcpt_Trxn_Pub.Update_Transaction_Extension
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data,
P_payer => l_payer,
p_entity_id => p_trxn_extension_id,
P_trxn_attribs => l_trxn_attribs,
x_response => l_response_code,
p_pmt_channel => L_pmt_channel_code,
p_instr_assignment => l_assign_id);
oe_debug_pub.add('After calling update transaction extension...');
oe_debug_pub.add('Result error code in Update_Transaction_Extension'||l_response_code.result_code);
oe_debug_pub.add('Result error code in Update_Transaction_Extension'||l_response_code.result_code);
oe_debug_pub.add('Update_Transaction_Extension assignment Successful....');
oe_debug_pub.add('After calling Update_Transaction_Extension');
oe_debug_pub.add('Bill to has changed....Need to delete this trxn id as context has changed!');
oe_debug_pub.add('Before calling Delete Transaction Extension API...');
IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension
(
p_api_version => 1.0,
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data,
p_commit => FND_API.G_FALSE,
P_payer => l_payer,
p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE,
X_response => l_response_code,
p_entity_id => p_trxn_extension_id);
oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
oe_debug_pub.add('Delete_Transaction_Extension Successful....');
oe_debug_pub.add('After calling Delete_Transaction_Extension');
--as the old trxn extension id was deleted
p_trxn_extension_id := l_trxn_extension_id ;
oe_debug_pub.add('Exiting Update_Payment_Trxn.....');
END Update_Payment_Trxn;
Select INVOICE_TO_ORG_ID,ORG_ID into l_invoice_to_org_id,l_org_id
from oe_order_lines_all where header_id = p_header_id and line_id = p_line_id;
select invoice_to_org_id,ORG_ID into l_invoice_to_org_id,l_org_id
from oe_order_headers_all where header_id = p_header_id;
Select hca.party_id, acct_site.cust_account_id
Into l_party_id, l_cust_account_id
From HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_CUST_ACCOUNTS_ALL HCA
Where SITE.SITE_USE_ID = p_site_use_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
select instrument_id, instrument_type, instr_assignment_id
into l_instrument_id, l_instrument_type, l_instr_assignment_id
from IBY_EXTN_INSTR_DETAILS_V
where trxn_extension_id = p_trxn_extension_id;
Select 'Y'
Into l_exists_assignment
From IBY_FNDCPT_PAYER_ASSGN_INSTR_V
Where party_id = l_party_id
And instr_assignment_id = l_instr_assignment_id
And rownum = 1;
SELECT trxn_extension_id
INTO l_trxn_extension_id
FROM oe_payments
WHERE header_id = p_header_id;
Select account_number
Into x_bank_account_number
From IBY_EXTN_INSTR_DETAILS_V
Where trxn_extension_id = l_trxn_extension_id;
/*Select itev.card_number,
itev.card_holder_name,
--itev.card_expirydate,
itev.masked_card_expirydate, --PADSS
itev.card_issuer_code,
itev.authorized_flag,
itev.instrument_security_code,
itev.instrument_id,
itev.instr_assignment_id
into
x_credit_card_number,
x_credit_card_holder_name,
x_credit_card_expiration_date,
x_credit_card_code,
l_authorized,
x_instrument_security_code,
x_instrument_id,
x_instrument_assignment_id
FROM
IBY_TRXN_EXTENSIONS_V ITEV
WHERE ITEV.TRXN_EXTENSION_ID = l_trxn_extension_id;*/
Select itev.card_number,
itev.card_holder_name,
--itev.card_expirydate,
itev.masked_card_expirydate, --PADSS
itev.card_issuer_code,
iauth.authorized_flag,
itev.instrument_security_code,
itev.instrument_id,
itev.instr_assignment_id
into
x_credit_card_number,
x_credit_card_holder_name,
x_credit_card_expiration_date,
x_credit_card_code,
l_authorized,
x_instrument_security_code,
x_instrument_id,
x_instrument_assignment_id
FROM
IBY_EXTN_INSTR_DETAILS_V ITEV,
IBY_EXTN_AUTHORIZATIONS_V IAUTH
WHERE
itev.TRXN_EXTENSION_ID= IAUTH.TRXN_EXTENSION_ID (+) AND
ITEV.TRXN_EXTENSION_ID = l_trxn_extension_id;
select encrypted
into l_encrypted
from iby_creditcard
where instrid=x_instrument_id;
PROCEDURE Delete_Payment_Trxn
(p_header_id IN NUMBER,
p_line_id IN NUMBER,
p_payment_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_trxn_extension_id IN NUMBER,
P_site_use_id IN NUMBER
)
IS
L_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
oe_debug_pub.add('Entering Delete_Payment_Trxn...');
Select ORG_ID into l_org_id
from oe_order_lines_all where line_id = p_line_id and header_id = p_header_id;
/*select payment_channel_code,settled_flag,authorized_flag
into l_payment_channel_code,l_settled_flag,l_authorized_flag -- bug 5194228
from iby_trxn_extensions_v where trxn_Extension_id=p_trxn_extension_id;*/
select payment_channel_code,nvl(settled_flag,'N'),nvl(authorized_flag,'N')
into l_payment_channel_code,l_settled_flag,l_authorized_flag -- bug 5194228
from IBY_EXTN_INSTR_DETAILS_V itev ,
IBY_EXTN_AUTHORIZATIONS_V iauth,
IBY_EXTN_SETTLEMENTS_V iset
where itev.trxn_extension_id=iauth.trxn_extension_id (+)
and itev.trxn_extension_id=iset.trxn_extension_id (+)
and itev.trxn_extension_id=p_trxn_extension_id;
select ifpai.acct_site_use_id into l_site_use_id
from iby_fndcpt_payer_assgn_instr_v ifpai,
IBY_EXTN_INSTR_DETAILS_V itev where
ifpai.instr_assignment_id = itev.instr_assignment_id and
itev.trxn_extension_id = p_trxn_extension_id;
select iepa.acct_site_use_id into l_site_use_id from
iby_external_payers_all iepa, iby_fndcpt_tx_extensions ifte
where iepa.ext_payer_id = ifte.ext_payer_id and
ifte.trxn_extension_id = p_trxn_extension_id;
Select hca.party_id, acct_site.cust_account_id
Into l_party_id, l_cust_account_id
From HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_CUST_ACCOUNTS_ALL HCA
Where SITE.SITE_USE_ID = l_site_use_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
oe_debug_pub.add('Before calling Delete Transaction Extension API...');
IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension
(
p_api_version => 1.0,
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data,
p_commit => FND_API.G_FALSE,
P_payer => l_payer,
p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE,
X_response => l_response_code,
p_entity_id => p_trxn_extension_id);
oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
oe_debug_pub.add('Delete_Transaction_Extension Successful....');
oe_debug_pub.add('After calling Delete_Transaction_Extension');
/* DELETE oe_payments
WHERE trxn_extension_id = p_trxn_extension_id
AND header_id = p_header_id
AND nvl(line_id,-1) = nvl(p_line_id,-1)
;*/ --Commented for CC Reversal ER bug# 16595030
UPDATE oe_order_lines_ALL
SET payment_type_code=null
WHERE 1=1
AND header_id = p_header_id
AND nvl(line_id,-1) = nvl(p_line_id,-1)
;
SELECT 'Y'
INTO l_header_payment_exists
FROM oe_payments
WHERE header_id =p_header_id
AND line_id is null --Added for CC Reversal ER bug# 16595030
AND PAYMENT_COLLECTION_EVENT='INVOICE'
AND PAYMENT_TYPE_CODE='CREDIT_CARD';
oe_debug_pub.add('Delete_Payment_Trxn error....exc');
oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);
oe_debug_pub.add('Delete_Payment_Trxn error....unxc');
oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);
oe_debug_pub.add('Delete_Payment_Trxn error....others');
oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);
END Delete_Payment_Trxn;
select settled_flag
into l_Settled_flag
from IBY_EXTN_SETTLEMENTS_V
where trxn_Extension_id = p_trxn_extension_id;
SELECT invoice_to_org_id, org_id
INTO l_invoice_to_org_id,l_org_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT invoice_to_org_id, org_id
INTO l_invoice_to_org_id,l_org_id
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT hca.party_id,acctsite.cust_account_id
INTO l_party_id, l_cust_account_id
FROM hz_cust_acct_sites_all acctsite, hz_cust_site_uses_all site, hz_cust_accounts_all hca
WHERE SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCTSITE.CUST_ACCT_SITE_ID
AND ACCTSITE.cust_account_id = HCA.cust_account_id
AND SITE.SITE_USE_ID = l_invoice_to_org_id;
SELECT transactional_curr_code
INTO l_currency_code
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT trxn_extension_id
INTO l_trxn_extension_id
FROM OE_PAYMENTS
WHERE HEADER_ID = p_header_id and line_id is null
AND nvl(payment_collection_event,'PREPAY') = 'INVOICE';
SELECT trxn_extension_id
INTO l_trxn_extension_id
FROM OE_PAYMENTS
WHERE line_id = p_line_id
AND header_id = p_header_id
AND payment_type_code <> 'COMMITMENT';
SELECT nvl(REVERSAL_ALLOWED,'N'), nvl(AUTHORIZATION_AMOUNT,0)
INTO l_reversal_allowed, l_authorized_amt
FROM IBY_TRXN_EXT_AUTHS_V
WHERE TRXN_EXTENSION_ID = l_trxn_extension_id
AND nvl(authorization_amount,0) > 0
AND authorization_status=0;
SELECT ACCT_SITE_USE_ID
INTO
l_old_invoice_to_org_id
FROM iby_external_payers_all
WHERE ext_payer_id in
(SELECT ext_payer_id
FROM iby_fndcpt_tx_extensions
WHERE trxn_extension_id =l_trxn_extension_id)
AND rownum<2;
SELECT nvl(VOICE_AUTHORIZATION_FLAG,'N')
INTO l_voice_auth
FROM iby_fndcpt_tx_extensions
WHERE TRXN_EXTENSION_ID = l_trxn_extension_id;
SELECT nvl(settled_flag, 'N')
INTO l_settled_flag
FROM IBY_EXTN_SETTLEMENTS_V
WHERE trxn_extension_id = l_trxn_extension_id;
UPDATE oe_order_headers_all
SET payment_amount = 0
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
, lock_control = lock_control + 1
WHERE header_id = p_header_id
AND payment_type_code = 'CREDIT_CARD';
/* UPDATE oe_payments
SET trxn_extension_id = p_new_trxn_extension_id
WHERE header_id = p_header_id
AND trxn_extension_id=l_trxn_extension_id;