[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lengthb(p_credit_card_num)
INTO len_credit_card_num
FROM dual;
SELECT substrb(p_credit_card_num,i,1)
INTO l_cc_num_char(i)
FROM dual;
| PROCEDURE insert_ext_bank_branch |
| |
| DESCRIPTION |
| Inserts a bank branch via IBY - CE - TCA api. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_standard.debug |
| iby_ext_bankacct_pub.create_ext_bank |
| iby_ext_bankacct_pub.create_ext_bank_branch |
| |
| ARGUMENTS : IN: p_bank_name Bank Name |
| p_branch_name Bank Branch Name |
| p_bank_number Bank Number |
| p_bank_num Bank Branch Number |
| p_end_date Inactive on |
| p_description Description |
| |
| OUT: |
| x_bank_party_id Bank Party ID |
| x_branch_party_id Branch Party ID |
| |
| RETURNS : NONE |
| |
| MODIFICATION HISTORY |
| 31-AUG-2005 Surendra Rajan Created. |
+===========================================================================*/
PROCEDURE insert_ext_bank_branch(
p_bank_name IN ce_bank_branches_v.bank_name%TYPE,
p_branch_name IN ce_bank_branches_v.bank_branch_name%TYPE,
p_bank_number IN ce_bank_branches_v.bank_number%TYPE,
p_branch_number IN ce_bank_branches_v.branch_number%TYPE,
p_end_date IN ce_bank_branches_v.end_date%TYPE DEFAULT NULL,
p_description IN ce_bank_branches_v.description%TYPE DEFAULT NULL,
x_bank_party_id OUT NOCOPY ce_bank_branches_v.bank_party_id%TYPE,
x_branch_party_id OUT NOCOPY ce_bank_branches_v.branch_party_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2) IS -- bug 5594142
l_profile_value VARCHAR2(30);
arp_standard.debug('arp_ext_bank_pkg.insert_ext_bank_branch(+)');
arp_standard.debug('arp_ext_bank_pkg.insert_ext_bank_branch(-)');
arp_standard.debug('exception in arp_ext_bank_pkg.insert_ext_bank_branch');
END insert_ext_bank_branch;
| PROCEDURE insert_bank_account |
| |
| DESCRIPTION |
| Inserts a bank account via iby api. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_standard.debug |
| iby_ext_bankacct_pub.create_ext_bank_acc |
| |
| ARGUMENTS : IN: |
| p_bank_account_name Account Name |
| p_bank_account_num Account Number |
| p_bank_party_id Bank Party ID |
| p_branch_party_id Branch Party ID |
| p_customer_id Customer ID |
| p_description Description |
| |
| OUT: |
| x_bank_account_id Bank Account ID |
| |
| RETURNS : NONE |
| |
| MODIFICATION HISTORY |
| 01-Sep-2005 Surendra Rajan Created |
| |
+===========================================================================*/
procedure insert_bank_account(
p_bank_account_name in iby_ext_bank_accounts_v.bank_account_name%type,
p_bank_account_num in iby_ext_bank_accounts_v.bank_account_number%type,
p_bank_party_id in iby_ext_bank_accounts_v.bank_party_id%type,
p_branch_party_id in iby_ext_bank_accounts_v.branch_party_id%type,
p_customer_id in iby_ext_bank_accounts_v.primary_acct_owner_party_id%type,
p_description in iby_ext_bank_accounts_v.description%type,
p_currency_code in iby_ext_bank_accounts_v.currency_code%type DEFAULT NULL,
p_start_date in DATE,
x_bank_account_id OUT NOCOPY iby_ext_bank_accounts_v.bank_account_id%type,
x_return_status OUT NOCOPY VARCHAR2 -- bug 5594142
) IS
l_bank_account_id iby_ext_bank_accounts_v.bank_account_id%TYPE;
arp_standard.debug('arp_ext_bank_pkg.insert_bank_account(+)');
SELECT party_id INTO l_party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_customer_id;
arp_standard.debug('arp_ext_bank_pkg.insert_bank_account(-)');
arp_standard.debug('exception in arp_ext_bank_pkg.insert_bank_account');
END insert_bank_account;
SELECT bank_party_id,branch_party_id
FROM ce_bank_branches_V
WHERE branch_number = l_routing_number;
Insert_bank_account(
-- IN parameters
p_bank_account_name => l_account_name,
p_bank_account_num => p_account_number,
p_bank_party_id => l_bank_party_id,
p_branch_party_id => l_branch_party_id,
p_customer_id => p_customer_id,
p_description => p_description,
p_currency_code => p_currency_code,
p_start_date => p_start_date,
-- OUT parameters
x_bank_account_id => l_bank_account_id,
x_return_status => l_return_status);
Insert_ext_bank_branch(
-- IN parameters
p_bank_name => p_routing_number,
p_branch_name => p_routing_number,
p_bank_number => p_routing_number,
p_branch_number => p_routing_number,
p_description => p_description,
-- OUT parameters
x_bank_party_id => l_bank_party_id,
x_branch_party_id => l_branch_party_id,
x_return_status => l_return_status);
Insert_bank_account(
-- IN parameters
p_bank_account_name => l_account_name,
p_bank_account_num => p_account_number,
p_bank_party_id => l_bank_party_id,
p_branch_party_id => l_branch_party_id,
p_customer_id => p_customer_id,
p_description => p_description,
p_currency_code => p_currency_code,
p_start_date => p_start_date,
-- OUT parameters
x_bank_account_id => l_bank_account_id,
x_return_status => l_return_status);
SELECT count(*) INTO l_count FROM iby_fndcpt_payer_assgn_instr_v
WHERE cust_account_id = p_customer_id
AND bank_acct_num_hash1 = iby_security_pkg.get_hash(p_account_number, 'F')
AND bank_acct_num_hash2 = iby_security_pkg.get_hash(p_account_number, 'T')
AND branch_number = p_routing_number;
SELECT party_id INTO l_party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_customer_id;
insert_acct_instr_assignment(
p_party_id => l_party_id,
p_customer_id => p_customer_id,
p_instr_id => l_bank_account_id,
p_assignment_start_date => p_start_date,
x_instr_assign_id => l_assign_id,
x_return_status => l_return_status);
| PROCEDURE insert_acct_instr_assignment |
| |
| DESCRIPTION |
| Creates an instrument assignment to the given customer with the |
| bank account. |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_standard.debug |
| IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment |
| |
| ARGUMENTS : IN: |
| p_party_id Party Id |
| p_customer_id Customer Id[Cust Account Id] |
| p_instr_id Instrument Id[Bank Account Id] |
| OUT: |
| x_instr_assign_id Instrument Assign Id |
| |
| RETURNS : NONE |
| |
| MODIFICATION HISTORY |
| 01-Aug-2008 Thirumalaisamy Created |
| |
+===========================================================================*/
PROCEDURE insert_acct_instr_assignment(
p_party_id IN NUMBER,
p_customer_id IN NUMBER,
p_instr_id IN NUMBER,
p_assignment_start_date IN DATE,
x_instr_assign_id OUT NOCOPY iby_fndcpt_payer_assgn_instr_v.INSTR_ASSIGNMENT_ID%TYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_payer_context_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
arp_standard.debug('arp_ext_bank_pkg.insert_acct_instr_assignment(+)');
arp_standard.debug('arp_ext_bank_pkg.insert_acct_instr_assignment(-)');
arp_standard.debug('exception in arp_ext_bank_pkg.insert_acct_instr_assignment');
END insert_acct_instr_assignment;
SELECT cust_RECEIPT_METHOD_ID, NVL(site_use_id, -1)
FROM ra_cust_receipt_methods rm
WHERE rm.customer_id = :p_customer_id
AND rm.SITE_USE_ID = NVL(:p_site_use_id, -1)
AND :p_as_of_date BETWEEN rm.start_date AND NVL(rm.end_date, :p_as_of_date ) ';
SELECT cust_RECEIPT_METHOD_ID, NVL(site_use_id, -1)
FROM ra_cust_receipt_methods rm
WHERE rm.customer_id = :p_customer_id
AND rm.SITE_USE_ID IS NULL
AND :p_as_of_date BETWEEN rm.start_date AND NVL(rm.end_date, :p_as_of_date ) ';
cc_only_stmt VARCHAR2(4000) := ' AND EXISTS ( SELECT 1 FROM ar_receipt_methods ba
WHERE ba.RECEIPT_METHOD_ID = rm.RECEIPT_METHOD_ID
AND ba.payment_channel_code = ''CREDIT_CARD'' ) '; /* Bug 14319639 */
/* bug 6121157 Function "process_cust_pay_method()" added to check if the customer already has a payment method associated else it will insert a record in ra_cust_receipt_methods. */
/*===========================================================================+
| FUNCTION process_cust_pay_method |
| |
| DESCRIPTION |
| Check if the customer already has a payment method associated else it |
| will insert a record in ra_cust_receipt_methods. |
| |
| SCOPE - PUBLIC |
| |
| |
| ARGUMENTS : IN: p_pay_method_id |
| p_customer_id |
| p_site_use_id |
| p_as_of_date |
| |
| |
| RETURNS : Number |
| |
| MODIFICATION HISTORY |
| 15-Jun-2007 Deep Gaurab Created. |
+===========================================================================*/
FUNCTION process_cust_pay_method (
p_pay_method_id IN NUMBER,
p_customer_id IN NUMBER,
p_site_use_id IN NUMBER DEFAULT null,
p_as_of_date IN DATE DEFAULT TRUNC(SYSDATE) ) RETURN NUMBER IS
l_cust_pay_method_id NUMBER;
SELECT
RA_CUST_RECEIPT_METHODS_S.NEXTVAL
INTO
l_cust_pay_method_id
FROM
dual;
INSERT INTO ra_cust_receipt_methods
(customer_id,
receipt_method_id,
primary_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
program_application_id,
site_use_id,
start_date,
cust_receipt_method_id)
VALUES
(p_customer_id, -- Customer Id
p_pay_method_id, -- Receipt Method Id
l_primary_flag, -- Primary Flag
SYSDATE, -- Creation Date
pg_user_id, -- Created By
SYSDATE, -- Last Update Date
pg_user_id, -- Last Updated By
pg_prog_appl_id, -- Program Application Id
p_site_use_id, -- Site use Id
TRUNC(p_as_of_date), -- Start Date
l_cust_pay_method_id);
SELECT NVL(DEFAULT_COUNTRY, 'US') INTO g_default_country
FROM AR_SYSTEM_PARAMETERS;