These simple scripts could be used to create receipt for various testings such as bank account reconciliation, match receipts to customer invoices. Please perform following steps:
1. Create a sequence using
create sequence skm_receipt_number_s start with 1;
2. Create the following package. This package has receipt data too. Actually this should have been separated but due to laziness, i put it inside he package because i needed to verify a set data. You might need to modify RCT procedure and add list of receipts to be created.
create or replace package skm_pkg as
TYPE receipt_rec IS RECORD
( amount NUMBER
, customer_number VARCHAR2(30)
, receipt_method_name VARCHAR2(90)
, credit_card_code VARCHAR2(30)
, cust_id NUMBER
, cust_bank_id NUMBER
, cust_site_id NUMBER
, receipt_method_id NUMBER
, remit_bank_id NUMBER
) ;
TYPE receipt_table IS TABLE OF receipt_rec index by binary_integer;
procedure rct
( retcode varchar2
, errbuf varchar2
) ;
end;
/
show errors
create or replace package body skm_pkg as
PROCEDURE get_user_id ( p_user_name VARCHAR2
, p_user_id IN OUT NUMBER
, p_err_msg IN OUT VARCHAR2
) IS
BEGIN
p_err_msg := null;
SELECT user_id
INTO p_user_id
FROM fnd_user
WHERE user_name = p_user_name;
--
--
EXCEPTION
WHEN OTHERS THEN
p_user_id := -1;
p_err_msg := 'procedure Get_user_Id error:'||sqlerrm;
END;
PROCEDURE get_resp_id ( p_resp_key VARCHAR2
, p_resp_id IN OUT NUMBER
, p_resp_appl_id IN OUT NUMBER
, p_err_msg IN OUT VARCHAR2
) IS
BEGIN
SELECT responsibility_id
, application_id
INTO p_resp_id
, p_resp_appl_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key;
--
--
EXCEPTION
WHEN OTHERS THEN
p_resp_id := -1;
p_resp_appl_id := -1;
p_err_msg := 'procedure Get_resp_Id error:'||sqlerrm;
END;
PROCEDURE init
( p_user_name VARCHAR2
, p_resp_key VARCHAR2
, px_err_msg IN OUT VARCHAR2
) Is
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
BEGIN
px_err_msg := null;
get_user_id(p_user_name, l_user_id, px_err_msg);
IF px_err_msg IS NOT NULL
THEN
RETURN;
END IF;
--
get_resp_id(p_resp_key, l_resp_id, l_appl_id, px_err_msg);
IF px_err_msg IS NOT NULL
THEN
RETURN;
END IF;
--
--
FND_GLOBAL.apps_initialize(l_user_id, l_resp_id, l_appl_id);
dbms_output.put_line(l_user_id || ','|| l_resp_id || ',' || l_appl_id);
END;
PROCEDURE add_receipt
( p_rct_tbl IN OUT SKM_PKG.receipt_table
, amount NUMBER DEFAULT 10.39
, credit_card_code VARCHAR2 DEFAULT 'VI'
, customer_number VARCHAR2 DEFAULT '100000'
, receipt_method_name VARCHAR2 DEFAULT 'PAYPAL'
, cust_id NUMBER DEFAULT NULL
, cust_bank_id NUMBER DEFAULT NULL
, cust_site_id NUMBER DEFAULT NULL
, receipt_method_id NUMBER DEFAULT NULL
, remit_bank_id NUMBER DEFAULT 5701983
) IS
l_count NUMBER;
BEGIN
l_count := p_rct_tbl.COUNT + 1;
p_rct_tbl(l_count).amount := amount;
p_rct_tbl(l_count).customer_number := customer_number;
p_rct_tbl(l_count).receipt_method_name := receipt_method_name;
p_rct_tbl(l_count).credit_card_code := credit_card_code;
p_rct_tbl(l_count).cust_id := cust_id;
p_rct_tbl(l_count).cust_bank_id := cust_bank_id;
p_rct_tbl(l_count).cust_site_id := cust_site_id;
p_rct_tbl(l_count).receipt_method_id := receipt_method_id;
p_rct_tbl(l_count).remit_bank_id := remit_bank_id;
END;
---
---
---
PROCEDURE get_customer_info
( p_rct_rec IN OUT receipt_rec
, p_return_msg OUT VARCHAR2
) IS
BEGIN
--
-- Get Customer Id
--
p_return_msg := 'Cust ID:';
IF p_rct_rec.cust_id IS NULL
THEN
dbms_output.put_line('Cust Number:' || p_rct_rec.customer_number || ':');
SELECT cust_account_id
INTO p_rct_rec.cust_id
FROM hz_cust_accounts
WHERE account_number = NVL(p_rct_rec.customer_number,'8266552');
END IF;
--
-- Get Cust Site Id and Bank
--
dbms_output.put_line('Cust Id:'|| p_rct_rec.cust_id || ':');
p_return_msg := 'Bank ID:';
IF p_rct_rec.cust_site_id IS NULL OR
p_rct_rec.cust_bank_id IS NULL
THEN
select b.site_use_id
, c.bank_account_id
INTO p_rct_rec.cust_site_id
, p_rct_rec.cust_bank_id
from hz_cust_acct_sites_all a
, hz_cust_site_uses_all b
, ap_bank_accounts_all c
, ap_bank_account_uses_all d
where (p_rct_rec.cust_site_id IS NULL OR
p_rct_rec.cust_site_id = b.site_use_id)
AND a.cust_account_id = p_rct_rec.cust_id
and a.cust_acct_site_id = b.cust_acct_site_id
and a.status = 'A'
and b.status = 'A'
and b.site_use_code = 'BILL_TO'
and b.site_use_id = d.customer_site_use_id
and d.external_bank_account_id = c.bank_account_id
and (p_rct_rec.credit_card_code IS NULL OR
p_rct_rec.credit_card_code = vvps_transactions.get_cc_type(c.bank_account_num)
)
and rownum = 1;
END IF;
--
-- Get Receipt Method
--
p_return_msg := 'Receipt Method ID:';
IF p_rct_rec.receipt_method_id IS NULL
THEN
SELECT receipt_method_id
INTO p_rct_rec.receipt_method_id
FROM ar_receipt_methods
WHERE UPPER(name) = NVL(p_rct_rec.receipt_method_name,'CREDIT CARD')
AND rownum = 1;
END IF;
p_return_msg := NULL;
--
-- Get Remittance bank
--
SELECT bank_account_id
INTO p_rct_rec.remit_bank_id
FROM ar_receipt_method_accounts_all
WHERE receipt_method_id = p_rct_rec.receipt_method_id
AND SYSDATE BETWEEN NVL(start_date,SYSDATE-1)
AND NVL(end_date ,SYSDATE+1)
;
--
--
EXCEPTION
WHEN OTHERS THEN
p_return_msg := p_return_msg || sqlerrm;
dbms_output.put_line('Error:'|| p_return_msg);
END;
--
--
--
procedure rct
( retcode varchar2
, errbuf varchar2
) IS
l_cr_id number;
l_return_status VARCHAR2(30);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
i number;
l_loop_indx NUMBER;
l_rct_tbl SKM_PKG.receipt_table;
l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;
l_receipt_number VARCHAR2(30);
l_msg VARCHAR2(2000);
l_receipt_num_min VARCHAR2(60);
l_receipt_num_max VARCHAR2(60);
l_ret_status BOOLEAN;
BEGIN
l_receipt_num_min := null;
init('SMISRA','RECEIVABLES_MANAGER', l_msg_data);
dbms_lock.sleep(5);
arp_global.functional_currency := 'USD';
arp_global.set_of_books_id := 2;
dbms_lock.sleep(5);
l_attribute_rec.attribute_category := 'No';
l_attribute_rec.attribute1 := 'N';
add_receipt(l_rct_tbl, 12.42,null);
dbms_output.put_line('Total Recs:' || l_rct_tbl.count || ':');
FOR l_loop_indx in l_rct_tbl.FIRST..l_rct_tbl.LAST
LOOP
IF l_loop_indx < 6 OR l_loop_indx > 10
THEN
SELECT 'SKM-' || skm_receipt_number_s.nextval
INTO l_receipt_number
FROM dual;
END IF;
IF l_receipt_number is NULL
THEN
l_receipt_number := 'SKM-' || to_char(sysdate,'MMDD') || '-01';
END IF;
IF l_receipt_num_min IS NULL
THEN
l_receipt_num_min := l_receipt_number;
END IF;
l_receipt_num_max := l_receipt_number;
dbms_output.put_line('Loop Index :' || l_loop_indx || ':');
get_customer_info(l_rct_tbl(l_loop_indx), l_msg);
dbms_output.put_line('Receipt Method:' || l_rct_tbl(l_loop_indx).receipt_method_id || ':' );
dbms_output.put_line('Bank:' || l_rct_tbl(l_loop_indx).cust_bank_id || ':' );
ar_receipt_api_pub.Create_cash(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_usr_currency_code => NULL, --the translated currency code
p_currency_code => 'USD',
p_usr_exchange_rate_type => NULL,
p_exchange_rate_type => NULL,
p_exchange_rate => NULL,
p_exchange_rate_date => NULL,
p_amount => l_rct_tbl(l_loop_indx).amount,
p_factor_discount_amount => NULL,
p_receipt_number => l_receipt_number,
p_receipt_date => sysdate,
p_gl_date => TRUNC(SYSDATE),
p_maturity_date => NULL,
p_postmark_date => NULL,
p_customer_id => l_rct_tbl(l_loop_indx).cust_id,
p_customer_name => NULL,
p_customer_number => NULL,
p_customer_bank_account_id => l_rct_tbl(l_loop_indx).cust_bank_id,
p_customer_bank_account_num => NULL,
p_customer_bank_account_name => NULL,
p_location => NULL,
p_customer_site_use_id => l_rct_tbl(l_loop_indx).cust_site_id,
p_customer_receipt_reference => 'SKM-API',
p_override_remit_account_flag => NULL,
p_remittance_bank_account_id => l_rct_tbl(l_loop_indx).remit_bank_id,
p_remittance_bank_account_num => NULL,
p_remittance_bank_account_name => NULL,
p_deposit_date => sysdate,
p_receipt_method_id => l_rct_tbl(l_loop_indx).receipt_method_id,
p_receipt_method_name => NULL,
p_doc_sequence_value => NULL,
p_ussgl_transaction_code => NULL,
p_anticipated_clearing_date => NULL,
p_called_from => 'pl/sql Script',
p_attribute_rec => l_attribute_rec,
-- p_global_attribute_rec IN global_attribute_rec_type DEFAULT global_attribute_rec_const,
p_comments => 'Created for testing bank statement matching',
p_issuer_name => NULL,
p_issue_date => NULL,
p_issuer_bank_branch_id => NULL,
p_cr_id => l_cr_id
);
dbms_output.put_line('Status:'|| l_return_status);
dbms_output.put_line( 'CR Id:'|| l_cr_id);
IF fnd_msg_pub.count_msg > 0
THEN
FOR j in 1..FND_MSG_PUB.count_msg
LOOP
FND_MSG_PUB.get
( p_msg_index => j
, p_encoded => 'F'
, p_data => l_msg_data
, p_msg_index_out => i
);
dbms_output.put_line( 'Error: ' || j || ':' || l_msg_data);
END LOOP;
END IF;
END LOOP;
end;
end;
/
show errors
3. Now run the above package to create receipts
set serveroutput on size 10000
declare
l_retcode VARCHAR2(30);
l_errbuf VARCHAR2(2000);
begin
skm_pkg.rct(l_retcode, l_errbuf);
end;
/
Posts
No comments:
Post a Comment