In R12 instead of Bank Account ID, Bank account use id has to be passed.
---Custom Table
CREATE TABLE XXVIR.XXVIRTU_DM_RCPT_TBL
(
OPERATING_UNIT VARCHAR2(200 BYTE),
RECEIPT_METHOD VARCHAR2(200 BYTE),
RECEIPT_NO VARCHAR2(50 BYTE),
RECEIPT_DATE DATE,
GL_DATE DATE,
CUSTOMER_NUMBER VARCHAR2(200 BYTE),
CUSTOMER_LOCATION VARCHAR2(40 BYTE),
CURRENCY VARCHAR2(3 BYTE),
RECEIPT_AMOUNT NUMBER,
BANK_ACCOUNT_ID NUMBER,
ORG_ID NUMBER,
PROCESS VARCHAR2(1 BYTE) DEFAULT 'N',
ERROR_DESCRIPTION VARCHAR2(4000 BYTE),
CUSTOMER_ID NUMBER,
SITE_ID NUMBER,
RECEIPT_METHOD_ID NUMBER,
BANK_ACCOUNT_USE_ID NUMBER,
ATTRIBUTE1 VARCHAR2(150 BYTE),
ATTRIBUTE2 VARCHAR2(150 BYTE)
)
--Validation procedure ---
l_error_flag VARCHAR2 (1) := 'N';
l_description VARCHAR2 (4000) := NULL;
l_customer_id ar_customers.customer_id%TYPE := NULL;
l_site_use_id hz_cust_site_uses_all.site_use_id%TYPE := NULL;
--
l_attribute_rec_type ar_receipt_api_pub.attribute_rec_type;
l_receipt_method_id NUMBER;
l_cr_id INTEGER;
l_org_id NUMBER;
l_location VARCHAR2 (40);
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_process VARCHAR2 (1) := 'N';
l_error_description VARCHAR2 (4000) := NULL;
l_error_message VARCHAR2 (255) := NULL;
BEGIN
fnd_file.put_line (fnd_file.LOG, 'Validation started !!!');
FOR rec IN (SELECT a.ROWID r_id, a.*
FROM xxvirtu_dm_rcpt_tbl a
WHERE a.process = 'N')
LOOP
l_error_flag := 'N';
l_description := NULL;
l_customer_id := NULL;
l_site_use_id := NULL;
l_receipt_method_id := NULL;
l_org_id := NULL;
l_location :=NULL;
fnd_file.put_line (fnd_file.LOG, '---------------');
fnd_file.put_line (fnd_file.LOG, 'Receipt No. ' || rec.receipt_no);
BEGIN
SELECT organization_id
INTO l_org_id
FROM hr_operating_units
WHERE NAME = rec.operating_unit;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'E';
l_description := 'Invalid Org.';
END;
BEGIN
SELECT hca.cust_account_id
INTO l_customer_id
FROM hz_cust_accounts hca
--WHERE account_number = rec.customer_number;
WHERE trim (account_name) = trim (rec.customer_number);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'E';
l_description := 'Invalid customer.';
END;
BEGIN
SELECT site_use_id, LOCATION
INTO l_site_use_id, l_location
FROM hz_cust_site_uses_all
WHERE cust_acct_site_id IN (SELECT cust_acct_site_id
FROM hz_cust_acct_sites_all
WHERE cust_account_id = l_customer_id)
AND site_use_code = 'BILL_TO'
AND trim (LOCATION) = trim(rec.customer_location)
AND org_id = l_org_id;
--AND primary_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'E';
l_description := l_description || ' Invalid site';
END;
BEGIN
SELECT receipt_method_id
INTO l_receipt_method_id
FROM ar_receipt_methods
WHERE NAME = rec.receipt_method;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_flag := 'E';
l_description := l_description || ' Invalid Receipt Method';
END;
fnd_file.put_line (fnd_file.LOG, l_description);
UPDATE xxvirtu_dm_rcpt_tbl
SET process = DECODE (l_error_flag, 'N', 'V', l_error_flag),
error_description = l_description,
customer_id = l_customer_id,
site_id = l_site_use_id,
org_id = l_org_id,
--customer_location = l_location,
receipt_method_id = l_receipt_method_id
WHERE ROWID = rec.r_id;
END LOOP;
COMMIT;
END;
---Upload procedure
CREATE OR REPLACE PROCEDURE XX_rcpt_upload_prc (
retcode NUMBER,
error_buf VARCHAR2,
pi_operating_unit IN VARCHAR2
)
AS
l_error_flag VARCHAR2 (1) := 'N';
l_description VARCHAR2 (4000) := NULL;
l_customer_id ar_customers.customer_id%TYPE := NULL;
l_site_use_id hz_cust_site_uses_all.site_use_id%TYPE := NULL;
l_location VARCHAR2 (40);
--
l_attribute_rec_type ar_receipt_api_pub.attribute_rec_type;
l_cr_id INTEGER;
l_process VARCHAR2 (1) := 'N';
l_error_description VARCHAR2 (4000) := NULL;
l_error_message VARCHAR2 (4000) := NULL;
i NUMBER := 0;
l_org_id NUMBER;
l_return_status VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (4000);
l_msg_index_out NUMBER;
l_dummy_cnt NUMBER;
l_loop_cnt NUMBER;
BEGIN
SELECT organization_id
INTO l_org_id
FROM hr_operating_units
WHERE NAME = pi_operating_unit;
fnd_global.apps_initialize (user_id => fnd_global.user_id,
resp_id => fnd_global.resp_id,
resp_appl_id => fnd_global.resp_appl_id
);
mo_global.set_org_context (l_org_id, NULL, 'AR');
mo_global.init ('AR');
mo_global.set_policy_context ('S', l_org_id);
FOR rec_1 IN (SELECT x.ROWID r_id, x.*
FROM xxvirtu_dm_rcpt_tbl x
WHERE x.process = 'V'
AND operating_unit = pi_operating_unit)
--AND receipt_no = 'PYMNT0005286')
LOOP
fnd_file.put_line (fnd_file.LOG, 'Receipt creation started !!!');
--i:=10;
l_cr_id := NULL;
l_attribute_rec_type := NULL;
l_return_status := NULL;
l_msg_count := NULL;
l_msg_data := NULL;
l_location := NULL;
l_process := 'Y';
l_error_description := NULL;
l_error_message := NULL;
fnd_file.put_line (fnd_file.LOG, '---------------');
fnd_file.put_line (fnd_file.LOG, 'Receipt No. ' || rec_1.receipt_no);
fnd_file.put_line (fnd_file.LOG, 'rec_1.org_id' || rec_1.org_id);
l_attribute_rec_type.attribute2 := '000000';
ar_receipt_api_pub.create_cash
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_receipt_number => rec_1.receipt_no,
p_amount => rec_1.receipt_amount,
p_receipt_date => rec_1.receipt_date,
p_gl_date => rec_1.gl_date,
p_receipt_method_id => rec_1.receipt_method_id,
p_customer_id => rec_1.customer_id,
--p_exchange_rate_type => 'User',
--p_exchange_rate =>1,
--p_customer_number => rec_1.customer_number,
p_customer_site_use_id => rec_1.site_id,
p_location => rec_1.customer_location,
p_remittance_bank_account_id => rec_1.bank_account_use_id,
-- 10061,
p_org_id => rec_1.org_id,
p_usr_currency_code => NULL,
p_currency_code => rec_1.currency,
p_attribute_rec => l_attribute_rec_type,
p_cr_id => l_cr_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
xxvirtu_common_pkg.writelog ('l_return_status :' || l_return_status);
IF NVL (l_return_status, 'E') != 'S'
THEN
l_process := 'E';
IF l_msg_count > 0
THEN
l_loop_cnt := 1;
LOOP
fnd_msg_pub.get (p_msg_index => l_loop_cnt,
p_data => l_msg_data,
p_encoded => fnd_api.g_false,
p_msg_index_out => l_dummy_cnt
);
IF l_return_status = 'E' OR l_return_status = 'U'
THEN
l_msg_data := CONCAT ('ERROR >>> ', l_msg_data);
IF LENGTH (l_error_message || l_msg_data) < 3000
THEN
l_error_message := l_error_message || l_msg_data;
END IF;
END IF;
l_loop_cnt := l_loop_cnt + 1;
EXIT WHEN l_loop_cnt > l_msg_count;
END LOOP;
xxvirtu_common_pkg.writelog ('l_error_message :'
|| l_error_message
);
END IF;
ROLLBACK;
ELSE
l_process := 'Y';
fnd_file.put_line (fnd_file.LOG, 'Receipt Created!!!');
fnd_file.put_line (fnd_file.LOG, 'Receipt ID :' || l_cr_id);
xxvirtu_common_pkg.writelog ('l_error_message :' || l_error_message);
xxvirtu_common_pkg.writelog ('l_error_message :' || l_error_message);
END IF;
UPDATE xxvirtu_dm_rcpt_tbl
SET process = l_process,
error_description = l_error_message
WHERE ROWID = rec_1.r_id;
COMMIT;
END LOOP;
END;
