ar receipt interface tables in oracle apps r12
In this post , We will discuss about ar receipt interface in
oracle apps to create the customer receipts from the external source. As such ,
Oracle has not provided any interface tables to migrate the customer receipts
from the External source or Module to the Oracle receivables but the good part
is oracle has provided the standard API to create ar receipts in oracle
receivables from the external system.’ ar_receipt_api_pub’ this is the api
oracle has provided in place of ar receipt interface tables in oracle apps r12.
This API process is so easy and fast as compared to the oracle interface
process. We don’t need to run the Import processes like in the interfaces for
Oracle Standard API’s. Here below is the complete ar receipt interface tables
in oracle apps r12 process.
Example of API using in place ar receipt interface tables in
oracle apps r12
Step1:- Create Staging table in which you will store the
External System Customer Receipt Data.
CREATE TABLE XXSTG_RECEIPT_TABLE
(
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)
);
Step2: - This method uses to validate all the values of the
staging table.
DECLARE
CURSOR C IS
SELECT * FROM XXSTG_RECEIPT_TABLE WHERE process = 'N';
--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 I IN C LOOP
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 = I.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 (I.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(I.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 = I.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 XXSTG_RECEIPT_TABLE
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_RECEIPT_upload_(
retcode NUMBER,
error_buf VARCHAR2,
P_ORG_ID 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 ORGANIZATION_ID= P_ORG_ID;
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 XXSTG_RECEIPT_TABLE x
WHERE x.process = 'V')
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_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,
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
);
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;
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);
END IF;
UPDATE XXSTG_RECEIPT_TABLE
SET process = l_process,
error_description = l_error_message
WHERE ROWID = rec_1.r_id;
COMMIT;
END LOOP;
END;
0 comments:
Post a Comment