APPS.OE_PAYMENT_TRXN_UTIL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 23

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;

Line: 74

			select arm.payment_channel_code
			into l_pmt_channel_code
			from ar_receipt_methods arm
			where arm.receipt_method_id = p_receipt_method_id;

Line: 96

			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;

Line: 101

			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;

Line: 127

		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;

Line: 173

	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;

Line: 205

				SELECT INSTRUMENT_ID into
				l_instrument_id from
				IBY_FNDCPT_PAYER_ASSGN_INSTR_V
				where INSTR_ASSIGNMENT_ID = p_payment_trx_id;

Line: 251

			IF p_update_card_flag = 'Y' AND p_instrument_id is not null THEN
				L_credit_card_rec.expiration_date := p_exp_date;

Line: 256

					oe_debug_pub.add('Before call to Update_Card API....');

Line: 261

				  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
				  );

Line: 275

						oe_debug_pub.add('Result error code in Update_Card exp'||l_response_code.result_code);

Line: 280

						oe_debug_pub.add('Result error code in Update_Card unxc'||l_response_code.result_code);

Line: 285

						oe_debug_pub.add('Update_Card Successful....');

Line: 472

		select instr_assignment_id into l_assign_id
		from IBY_TRXN_EXTENSIONS_V where trxn_extension_id = p_x_trxn_extension_id;

Line: 571

                          UPDATE oe_payments
                          SET    credit_card_approval_code = NULL
                          WHERE  header_id = p_header_id
                          AND    line_id = p_line_id;

Line: 644

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);

Line: 716

		oe_debug_pub.add('Entering OE_PAYMENT_TRXN_UTIL.Update_Payment_Trxn...');

Line: 723

		select arm.payment_channel_code
		into l_pmt_channel_code
		from ar_receipt_methods arm
		where arm.receipt_method_id = p_receipt_method_id;

Line: 738

	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;

Line: 750

		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;

Line: 754

		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;

Line: 768

		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;

Line: 822

			SELECT INSTRUMENT_ID into
			l_instrument_id from
			IBY_FNDCPT_PAYER_ASSGN_INSTR_V
			where INSTR_ASSIGNMENT_ID = p_payment_trx_id;

Line: 900

		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..');

Line: 910

			  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
			  );

Line: 925

					oe_debug_pub.add('Result error code in Update_Card exp'||l_response_code.result_code);

Line: 930

					oe_debug_pub.add('Result error code in Update_Card unxc'||l_response_code.result_code);

Line: 935

					oe_debug_pub.add('Update_Card Successful....');

Line: 939

		END IF; --update card flag

Line: 1079

		--
		IF l_debug_level > 0 THEN
			oe_debug_pub.add('Calling update transaction extension...');

Line: 1090

			--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;

Line: 1100

			oe_debug_pub.add('Values passed to Update_trxn_extn');

Line: 1115

			SELECT AUTHORIZATION_CODE into
			l_approval_code FROM IBY_TRXN_EXT_AUTHS_V
			WHERE TRXN_EXTENSION_ID = p_trxn_extension_id;

Line: 1128

			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;

Line: 1145

		--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
					AND    nvl(authorization_amount,0) > 0
					AND    authorization_status=0;

Line: 1245

				       -- update oe_payments table
				       p_trxn_extension_id := l_trxn_extension_id;

Line: 1267

                                  select trxn_ref_number2
                                  into   l_trxn_ref_number2
                                  from   iby_trxn_extensions_v
                                  where  trxn_extension_id = p_trxn_extension_id;

Line: 1335

					--as the old trxn extension id was deleted
					p_trxn_extension_id := l_trxn_extension_id ;

Line: 1349

		--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';

Line: 1368

			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);

Line: 1385

				oe_debug_pub.add('After calling update transaction extension...');

Line: 1390

					oe_debug_pub.add('Result error code in Update_Transaction_Extension'||l_response_code.result_code);

Line: 1395

					oe_debug_pub.add('Result error code in Update_Transaction_Extension'||l_response_code.result_code);

Line: 1400

					oe_debug_pub.add('Update_Transaction_Extension assignment Successful....');

Line: 1401

					oe_debug_pub.add('After calling Update_Transaction_Extension');

Line: 1409

			oe_debug_pub.add('Bill to has changed....Need to delete this trxn id as context has changed!');

Line: 1410

			oe_debug_pub.add('Before calling Delete Transaction Extension API...');

Line: 1423

		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);

Line: 1437

				oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);

Line: 1444

				oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);

Line: 1451

				oe_debug_pub.add('Delete_Transaction_Extension Successful....');

Line: 1452

				oe_debug_pub.add('After calling Delete_Transaction_Extension');

Line: 1523

			--as the old trxn extension id was deleted
			p_trxn_extension_id := l_trxn_extension_id ;

Line: 1537

		oe_debug_pub.add('Exiting Update_Payment_Trxn.....');

Line: 1576

END Update_Payment_Trxn;

Line: 1615

	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;

Line: 1619

	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;

Line: 1632

	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;

Line: 1656

	select 	instrument_id, instrument_type, instr_assignment_id
	into	l_instrument_id, l_instrument_type, l_instr_assignment_id
	from 	iby_trxn_extensions_v
	where	trxn_extension_id = p_trxn_extension_id;

Line: 1674

	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;

Line: 1911

		SELECT trxn_extension_id
		INTO l_trxn_extension_id
		FROM oe_payments
		WHERE header_id = p_header_id;

Line: 1923

			Select account_number
			Into x_bank_account_number
			From iby_trxn_extensions_v
			Where trxn_extension_id = l_trxn_extension_id;

Line: 1929

			Select  itev.card_number,
				itev.card_holder_name,
				itev.card_expirydate,
				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;

Line: 2071

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;

Line: 2103

		oe_debug_pub.add('Entering Delete_Payment_Trxn...');

Line: 2107

		Select ORG_ID into l_org_id
		from oe_order_lines_all where line_id = p_line_id and header_id = p_header_id;

Line: 2118

		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;

Line: 2140

				select ifpai.acct_site_use_id into l_site_use_id
				from iby_fndcpt_payer_assgn_instr_v ifpai,
				iby_trxn_extensions_v itev where
				ifpai.instr_assignment_id = itev.instr_assignment_id and
				itev.trxn_extension_id = p_trxn_extension_id;

Line: 2149

				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;

Line: 2165

	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;

Line: 2195

			oe_debug_pub.add('Before calling Delete Transaction Extension API...');

Line: 2201

			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);

Line: 2215

					oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);

Line: 2222

					oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);

Line: 2229

					oe_debug_pub.add('Delete_Transaction_Extension Successful....');

Line: 2230

					oe_debug_pub.add('After calling Delete_Transaction_Extension');

Line: 2244

		oe_debug_pub.add('Delete_Payment_Trxn error....exc');

Line: 2245

		oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);

Line: 2260

		oe_debug_pub.add('Delete_Payment_Trxn error....unxc');

Line: 2261

		oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);

Line: 2276

		oe_debug_pub.add('Delete_Payment_Trxn error....others');

Line: 2277

		oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);

Line: 2299

END Delete_Payment_Trxn;

Line: 2315

			select settled_flag
			into l_Settled_flag
			from iby_trxn_extensions_v
			where trxn_Extension_id = p_trxn_extension_id;