The following script can be used to create a Basic Debit memo. The API that is used to create an AR Invoice is – “ar_invoice_api_pub.create_invoice“. The Cust_trx_type_id decides whether the invoice getting created is a Standard Invoice or a Credit Memo or a Debit memo. Based on this value, the API also checks the sign of the amount on the Line level that is being passed.
--Script to create Debit memo.
DECLARE
l_index NUMBER := 0;
l_line_index NUMBER := 0;
l_dist_index NUMBER := 0;
l_cm_customer_trx_id NUMBER := 0;
--
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
l_trx_line_id NUMBER;
l_return_status VARCHAR2 (1);
l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
l_msg_data VARCHAR2 (2000);
l_resp_id NUMBER;
l_appl_id NUMBER;
l_user_name VARCHAR2 (200);
g_user_id fnd_user.user_id%TYPE;
l_line_number NUMBER := 0;
l_msg_count NUMBER;
l_ou_id hr_operating_units.organization_id%TYPE;
l_batch_source_id ra_batch_sources_all.batch_source_id%TYPE;
l_cust_trx_type_id ra_cust_trx_types_all.cust_trx_type_id%TYPE;
l_cust_account_id hz_cust_accounts.cust_account_id%TYPE;
l_code_combination_id gl_code_combinations_kfv.code_combination_id%TYPE;
BEGIN
DBMS_OUTPUT.put_line ('Start processing...');
l_user_name := 'Phani Adivi'; --- enter your name here
-- Get User ID
BEGIN
SELECT user_id
INTO g_user_id
FROM fnd_user
WHERE user_name = l_user_name;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error during deriving User ID and error is: ' || SQLERRM);
END;
--Get Responsibility ID
BEGIN
SELECT RESPONSIBILITY_ID
INTO l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = 'RECEIVABLES_MANAGER'; --- enter the responsibility name that has access to create Invoices
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error during deriving Responsibility ID and error is: '
|| SQLERRM);
END;
--Get Application ID
BEGIN
SELECT application_id
INTO l_appl_id
FROM fnd_application
WHERE application_short_name = 'AR';
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error during deriving application ID and error is: '
|| SQLERRM);
END;
-- Get Operating Unit ID
BEGIN
SELECT organization_id
INTO l_ou_id
FROM hr_operating_units
WHERE name = 'enter OU Name'; --- enter the OU name in which this invoice is being created in
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error during deriving Operating Unit ID: ' || SQLERRM);
END;
--Get Batch Source ID
BEGIN
SELECT batch_source_id
INTO l_batch_source_id
FROM ra_batch_sources_all
WHERE name = 'Source Name'; -- enter the Source name
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error while deriving Batch Source ID: ' || SQLERRM);
END;
-- Get Customer Trx Type ID
BEGIN
SELECT cust_trx_type_id
INTO l_cust_trx_type_id
FROM ra_cust_trx_types_all
WHERE name = 'Debit Memo'
AND org_id = l_ou_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error during deriving Customer Transaction Type and error is: '
|| SQLERRM);
END;
-- Get Customer Account ID
BEGIN
SELECT cust_account_id
INTO l_cust_account_id
FROM hz_cust_accounts s
WHERE account_name = 'Account Name'; -- enter the Account name of the Customer for which this invoice is being created for
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error during deriving Customer Account ID and error is: '
|| SQLERRM);
END;
--Get Code Combination Id
BEGIN
SELECT code_combination_id
INTO l_code_combination_id
FROM gl_code_combinations_kfv
WHERE concatenated_segments = 'concatenated_string'; -- get the concatenated segment information - this will be shown on the distributions screen
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error during deriving the code combination id: ' || SQLERRM);
END;
mo_global.init ('AR');
mo_global.set_policy_context ('S', l_ou_id);
fnd_global.apps_initialize (user_id => g_user_id,
resp_id => l_resp_id,
resp_appl_id => l_appl_id);
-- Prepare Credit Memo;
l_batch_source_rec.batch_source_id := l_batch_source_id;
l_cm_customer_trx_id := ra_customer_trx_s.NEXTVAL;
l_index := l_index + 1;
--
l_trx_header_tbl (l_index).org_id := l_ou_id;
l_trx_header_tbl (l_index).trx_header_id := l_cm_customer_trx_id;
--l_trx_header_tbl (l_index).trx_number := 2120191; -- Enter the transaction number if the automatic numbering is not enabled
l_trx_header_tbl (l_index).trx_date := SYSDATE; --TO_DATE('30-NOV-2018', 'DD-MON-RRRR');
l_trx_header_tbl (l_index).gl_date := TO_DATE ('30-NOV-2018', 'DD-MON-RRRR');
l_trx_header_tbl (l_index).trx_currency := 'USD';
l_trx_header_tbl (l_index).cust_trx_type_id := l_cust_trx_type_id;
l_trx_header_tbl (l_index).bill_to_customer_id := l_cust_account_id;
l_trx_header_tbl (l_index).printing_option := 'NOT';
l_trx_header_tbl (l_index).reference_number := 21220191;
-- Prepare Credit Memo Lines
l_trx_line_id := ra_customer_trx_lines_s.NEXTVAL;
l_line_index := l_line_index + 1;
l_line_number := l_line_number + 1;
l_dist_index := l_dist_index + 1;
--
l_trx_lines_tbl (l_line_index).trx_header_id := l_cm_customer_trx_id;
l_trx_lines_tbl (l_line_index).trx_line_id := l_trx_line_id;
l_trx_lines_tbl (l_line_index).line_number := l_line_number;
l_trx_lines_tbl (l_line_index).quantity_invoiced := 1;
l_trx_lines_tbl (l_line_index).unit_selling_price := 12;
l_trx_lines_tbl (l_line_index).line_type := 'LINE';
l_trx_lines_tbl (l_line_index).taxable_flag := 'N';
--
l_trx_dist_tbl (l_dist_index).trx_dist_id := RA_CUST_TRX_LINE_GL_DIST_S.NEXTVAL;
l_trx_dist_tbl (l_dist_index).trx_line_id := l_trx_line_id;
l_trx_dist_tbl (l_dist_index).ACCOUNT_CLASS := 'REV';
l_trx_dist_tbl (l_dist_index).percent := 100;
l_trx_dist_tbl (l_dist_index).code_combination_id := l_code_combination_id;
l_return_status := NULL;
--Standard API call
ar_invoice_api_pub.create_invoice (
p_api_version => 1.0,
p_commit => fnd_api.g_false,
p_batch_source_rec => l_batch_source_rec,
p_trx_header_tbl => l_trx_header_tbl,
p_trx_lines_tbl => l_trx_lines_tbl,
p_trx_dist_tbl => l_trx_dist_tbl,
p_trx_salescredits_tbl => l_trx_salescredits_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
DBMS_OUTPUT.put_line ('l_return_status is: ' || l_return_status);
FOR cr_rec
IN (SELECT trx_header_id,
trx_line_id,
error_message,
invalid_value
FROM ar_trx_errors_gt)
LOOP
l_msg_data := NULL;
l_msg_data :=
'API Create CM/DM:'
|| cr_rec.trx_header_id
|| '-'
|| cr_rec.trx_line_id
|| '-'
|| cr_rec.error_message;
DBMS_OUTPUT.put_line ('Message is: ' || l_msg_data);
END LOOP;
--
COMMIT;
END;
