Auto Apply given Credit memos to the Invoices

Introduction

This Post explain steps required to Apply Credit Memos to the Invoices through API in Oracle EBS R12

Script to Apply Credit Memos to the Invoices through API

CREATE OR REPLACE PACKAGE BODY apps.xx_cn_cm_application_pkg
IS
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
pr_gl_date IN VARCHAR2
)
IS
v_count NUMBER (20) := 0;
v_date DATE := fnd_date.canonical_to_date (pr_gl_date);
v_end_date DATE;
v_period_status VARCHAR2 (20);
BEGIN
BEGIN
SELECT ps.end_date “Period End Date”,
DECODE (ps.closing_status,
‘O’, ‘Open’,
‘N’, ‘Never Opened’,
‘F’, ‘Future Enterable’,
‘C’, ‘Closed’,
‘Unknown’
) “Period Status”
INTO v_end_date,
v_period_status
FROM gl_period_statuses ps,
gl_sets_of_books sob,
fnd_application_vl fnd
WHERE ps.application_id IN (101)
AND sob.set_of_books_id = ps.set_of_books_id
AND fnd.application_id = ps.application_id
AND ps.adjustment_period_flag = ‘N’
AND sob.set_of_books_id = 2021
AND v_date BETWEEN TRUNC (ps.start_date) AND TRUNC (ps.end_date);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
printlog
(‘No data found while validating gl date passed in parameter’);
END;

–printlog(‘date passed b ‘||v_date);
IF v_period_status = ‘Open’
THEN
v_date := v_end_date;
ELSE
v_date := SYSDATE;
END IF;

SELECT COUNT (1)
INTO v_count
FROM xx_ar_interface_test sai
WHERE invoice_amount IS NOT NULL;

IF v_count > 0
THEN
with_amount (v_date);
ELSE
without_amount (v_date);
END IF;
END;

PROCEDURE without_amount (pr_gl_date IN VARCHAR2)
IS
CURSOR c1
IS
SELECT *
FROM xx_ar_interface_test sai;

–in Variables—-
v_cm_payment_schedule NUMBER;
— := 101502; — Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
v_inv_payment_schedule NUMBER;
— := 101500; — Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
v_amount_applied NUMBER;
— := 1000; — Amount of credit memo to apply to invoice
v_apply_date DATE := pr_gl_date;
—fnd_date.canonical_to_date(PR_GL_DATE);–’23-NOV-2017′;
v_gl_date DATE := pr_gl_date;
—fnd_date.canonical_to_date(PR_GL_DATE);–’23-NOV-2017′;
v_ussgl_transaction_code VARCHAR2 (1024);
— null, but check AR_RECEIVABLE_APPLICATIONS_ALL
v_null_flex VARCHAR2 (1024);
— null, unless you have flexfield segments to define
v_customer_trx_line_id NUMBER;
— null, but check AR_RECEIVABLE_APPLICATIONS_ALL
v_comments VARCHAR2 (240) := ‘Applied automatically’;
v_module_name VARCHAR2 (128) := ‘AR’;
— If null, validation won ‘t occur
v_module_version VARCHAR2 (128) := ‘ 1 ‘;
— If null, validation won’ t occur
–Out parameters—-
v_out_rec_application_id NUMBER;
v_acctd_amount_applied_from NUMBER;
v_acctd_amount_applied_to NUMBER;
—-Local Variables—
v_cm_due_amount NUMBER;
v_inv_due_amount NUMBER;
v_inv_count NUMBER;
v_cm_count NUMBER;
l_org NUMBER := 101;
x_err_flag VARCHAR (1);
x_err_msg VARCHAR2 (1000);
x_applied_chk NUMBER;
cm_due_amount NUMBER;
inv_due_amount NUMBER;
BEGIN
–DBMS_APPLICATION_INFO.set_client_info(l_org);
BEGIN
mo_global.set_policy_context (‘S’, l_org);
END;

— printlog(‘Date passed ‘||v_apply_date);
FOR i IN c1
LOOP
x_err_flag := ‘N’;
x_err_msg := NULL;
v_inv_due_amount := 0;
v_cm_due_amount := 0;
v_inv_count := 0;
v_cm_count := 0;
cm_due_amount := 0;
inv_due_amount := 0;

——————————————————————————-
——Validation to check invoice due amount remaining is not eqal to zero—–
——————————————————————————-
BEGIN
SELECT aps.amount_due_remaining
INTO inv_due_amount
FROM ar_payment_schedules_all aps, hz_cust_accounts hca
WHERE 1 = 1
AND hca.account_number = i.bill_customer_number
AND aps.customer_id = hca.cust_account_id
AND aps.trx_number = i.REFERENCE
AND aps.status = ‘OP’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘No data found while validating invoice due amount for reference number=’
|| i.REFERENCE
);
printlog
( ‘No data found while validating invoice due amount for reference number=’
|| i.REFERENCE
|| ‘invoice number=’
|| i.invoice_number
);
WHEN TOO_MANY_ROWS
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘Found too many rows while validating invoice due amount for reference number=’
|| i.REFERENCE
);
printlog
( ‘Found too many rows while validating invoice due amount for reference number=’
|| i.REFERENCE
|| ‘invoice number=’
|| i.invoice_number
);
END;

———————————————————————————–
——Validation to check credit memo due amount remaining is not eqal to zero—–
———————————————————————————–
BEGIN
SELECT -aps.amount_due_remaining
INTO cm_due_amount
FROM ar_payment_schedules_all aps, hz_cust_accounts hca
WHERE 1 = 1
AND hca.account_number = i.bill_customer_number
AND aps.customer_id = hca.cust_account_id
AND aps.trx_number = i.invoice_number
AND aps.status = ‘OP’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘No data found while validating credit memo due amount for invoice number=’
|| i.invoice_number
);
printlog
( ‘No data found while validating credit memo due amount for invoice number=’
|| i.invoice_number
|| ‘reference number=’
|| i.REFERENCE
);
WHEN TOO_MANY_ROWS
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘Found too many rows while validating creit memo due amount for invoice number=’
|| i.invoice_number
);
printlog
( ‘Found too many rows while validating credit memo due amount for invoice number=’
|| i.invoice_number
|| ‘reference number=’
|| i.REFERENCE
);
END;

IF inv_due_amount > 0 AND cm_due_amount > 0
THEN
SELECT COUNT (1)
INTO v_cm_count
FROM ar_payment_schedules_all aps
WHERE aps.trx_number = i.invoice_number;

SELECT COUNT (1)
INTO v_inv_count
FROM ar_payment_schedules_all aps, hz_cust_accounts hca
WHERE 1 = 1
AND hca.account_number = i.bill_customer_number
AND aps.customer_id = hca.cust_account_id
AND aps.trx_number = i.REFERENCE;

SELECT COUNT (1)
INTO x_applied_chk
FROM ra_customer_trx_all rcta1,
ra_customer_trx_all rcta2,
ar_receivable_applications_all araa,
ar_payment_schedules_all aps,
hz_cust_accounts hca
WHERE 1 = 1
AND aps.customer_id = hca.cust_account_id
AND rcta2.customer_trx_id = aps.customer_trx_id
AND rcta1.customer_trx_id = araa.customer_trx_id
AND rcta2.customer_trx_id = araa.applied_customer_trx_id
AND rcta1.org_id = l_org
AND rcta1.trx_number = i.invoice_number
AND rcta2.trx_number = i.REFERENCE;

IF x_applied_chk = 0
THEN
IF v_cm_count > 0 AND v_inv_count > 0
THEN
—————————————————
——Get credit memo due amount remaining ——-
—————————————————
BEGIN
SELECT -aps.amount_due_remaining,
aps.payment_schedule_id
INTO v_cm_due_amount,
v_cm_payment_schedule
FROM ar_payment_schedules_all aps,
hz_cust_accounts hca
WHERE 1 = 1
AND hca.account_number = i.bill_customer_number
AND aps.customer_id = hca.cust_account_id
AND aps.trx_number = i.invoice_number
AND aps.status = ‘OP’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘NO1- Matching Records for CM due amount Not found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
printlog
( ‘NO1- Matching Records for CM due amount Not found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
WHEN TOO_MANY_ROWS
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘TOO1- More than one matching record for CM due amount for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
printlog
( ‘TOO1- More than one matching record for CM due amount for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
END;

—————————————————
——Get invoice due amount remaining ———–
—————————————————
BEGIN
SELECT aps.amount_due_remaining, aps.payment_schedule_id
INTO v_inv_due_amount, v_inv_payment_schedule
FROM ar_payment_schedules_all aps,
hz_cust_accounts hca
WHERE 1 = 1
AND hca.account_number = i.bill_customer_number
AND aps.customer_id = hca.cust_account_id
AND aps.trx_number = i.REFERENCE
AND aps.status = ‘OP’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘NO2- Matching Records for invoice due amount Not found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
printlog
( ‘NO2- Matching Records for invoice due amount Not found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
WHEN TOO_MANY_ROWS
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘TOO2- More than one matching record for invoice due amount found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
printlog
( ‘TOO2- More than one matching record for invoice due amount found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
END;

IF v_cm_due_amount > v_inv_due_amount
THEN
v_amount_applied := v_inv_due_amount;
ELSE
v_amount_applied := v_cm_due_amount;
END IF;
— v_amount_applied := i.invoice_amount;
ELSE
printlog (‘Skiped out Invoices => ‘ || i.invoice_number);
x_err_flag := ‘Y’;
x_err_msg :=
( ‘Either credit memo’
|| i.invoice_number
|| ‘ or invoice ‘
|| i.REFERENCE
|| ‘ is not found in the database’
);
END IF;

—————————————————
——Applying Credit memos to the invoice ——-
—————————————————
IF x_err_flag = ‘N’
THEN
arp_process_application.cm_application
(p_cm_ps_id => v_cm_payment_schedule,
p_invoice_ps_id => v_inv_payment_schedule,
p_amount_applied => v_amount_applied,
p_apply_date => v_apply_date,
p_gl_date => v_gl_date,
p_ussgl_transaction_code => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_global_attribute_category => NULL,
p_global_attribute1 => NULL,
p_global_attribute2 => NULL,
p_global_attribute3 => NULL,
p_global_attribute4 => NULL,
p_global_attribute5 => NULL,
p_global_attribute6 => NULL,
p_global_attribute7 => NULL,
p_global_attribute8 => NULL,
p_global_attribute9 => NULL,
p_global_attribute10 => NULL,
p_global_attribute11 => NULL,
p_global_attribute12 => NULL,
p_global_attribute13 => NULL,
p_global_attribute14 => NULL,
p_global_attribute15 => NULL,
p_global_attribute16 => NULL,
p_global_attribute17 => NULL,
p_global_attribute18 => NULL,
p_global_attribute19 => NULL,
p_global_attribute20 => NULL,
p_customer_trx_line_id => v_customer_trx_line_id,
p_comments => v_comments,
p_module_name => v_module_name,
p_module_version => v_module_version,
p_out_rec_application_id => v_out_rec_application_id,
p_acctd_amount_applied_from => v_acctd_amount_applied_from,
p_acctd_amount_applied_to => v_acctd_amount_applied_to
);

IF v_out_rec_application_id IS NOT NULL
THEN
COMMIT;
printlog ( ‘Credit memo=’
|| i.invoice_number
|| ‘,’
|| ‘Invoice=’
|| i.REFERENCE
|| ‘,’
|| ‘CM amount=’
|| v_cm_due_amount
|| ‘,’
|| ‘Invoice amount=’
|| v_inv_due_amount
|| ‘,’
|| ‘Amount applied=’
|| v_amount_applied
);
ELSE
ROLLBACK;
END IF;
ELSE
printlog
(‘API Execution is not processed due to validation error’
);
END IF;
ELSE
x_err_flag := ‘Y’;
x_err_msg :=
( ‘Credit memo ‘
|| i.invoice_number
|| ‘already applied to Same Invoice number : ‘
|| i.REFERENCE
);
printlog ( ‘Credit memo ‘
|| i.invoice_number
|| ‘already applied to Same Invoice number : ‘
|| i.REFERENCE
);
END IF;
ELSE
x_err_flag := ‘Y’;
x_err_msg :=
(‘Please check that either credit memo amount or invoice due amount is zero or program ran into exception
for either invoice or credit memo—>Please check log’);
END IF;

UPDATE xx_ar_interface_test
SET error_flag = x_err_flag,
error_msg = x_err_msg
WHERE invoice_number = i.invoice_number AND REFERENCE = i.REFERENCE;

COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
— dbms_output.put_line(sqlerrm);
printlog (SQLERRM);
END;

PROCEDURE with_amount (pr_gl_date IN VARCHAR2)
IS
CURSOR c1
IS
SELECT *
FROM xx_ar_interface_test sai;

—-local Variables Declaration—–
v_cm_payment_schedule NUMBER;
— := 101502; — Payment Schedule ID of Credit Memo from APPS.AR_PAYMENT_SCHEDULES_ALL
v_inv_payment_schedule NUMBER;
— := 101500; — Payment Schedule ID of Invoice from APPS.AR_PAYMENT_SCHEDULES_ALL
v_amount_applied NUMBER;
— := 1000; — Amount of credit memo to apply to invoice
v_apply_date DATE := pr_gl_date;
—fnd_date.canonical_to_date(PR_GL_DATE);–’23-NOV-2017′;
v_gl_date DATE := pr_gl_date;
–fnd_date.canonical_to_date(PR_GL_DATE);–’23-NOV-2017′;
v_ussgl_transaction_code VARCHAR2 (1024);
— null, but check AR_RECEIVABLE_APPLICATIONS_ALL
v_null_flex VARCHAR2 (1024);
— null, unless you have flexfield segments to define
v_customer_trx_line_id NUMBER;
— null, but check AR_RECEIVABLE_APPLICATIONS_ALL
v_comments VARCHAR2 (240) := ‘Applied automatically’;
v_module_name VARCHAR2 (128) := ‘AR’;
— If null, validation won ‘t occur
v_module_version VARCHAR2 (128) := ‘ 1 ‘;
— If null, validation won’ t occur
—–Out parameters——–
v_out_rec_application_id NUMBER;
v_acctd_amount_applied_from NUMBER;
v_acctd_amount_applied_to NUMBER;
——Local Variables—–
v_cm_due_amount NUMBER;
v_inv_due_amount NUMBER;
v_inv_count NUMBER;
v_cm_count NUMBER;
l_org NUMBER := 101;
x_err_flag VARCHAR (1);
x_err_msg VARCHAR2 (1000);
cm_due_amount NUMBER;
BEGIN
—DBMS_APPLICATION_INFO.set_client_info(l_org);
BEGIN
mo_global.set_policy_context (‘S’, l_org);
END;

FOR i IN c1
LOOP
x_err_flag := ‘N’;
x_err_msg := NULL;
v_inv_due_amount := 0;
v_cm_due_amount := 0;
v_inv_count := 0;
v_cm_count := 0;
cm_due_amount := 0;

———————————————————————————-
——Validation to check credit memo due amount remaining is not eqal to zero—–
———————————————————————————–
BEGIN
SELECT -aps.amount_due_remaining
INTO cm_due_amount
FROM ar_payment_schedules_all aps, hz_cust_accounts hca
WHERE 1 = 1
AND hca.account_number = i.bill_customer_number
AND aps.customer_id = hca.cust_account_id
AND aps.trx_number = i.invoice_number
AND aps.status = ‘OP’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘No data found while validating creit memo due amount for invoice number=’
|| i.invoice_number
);
printlog
( ‘No data found while validating credit memo due amount for invoice number=’
|| i.invoice_number
|| ‘Reference=’
|| i.REFERENCE
);
WHEN TOO_MANY_ROWS
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘Found too many rows while validating credit memo due amount for invoice number=’
|| i.invoice_number
);
printlog
( ‘Found too many rows while validating credit memo due amount for invoice number=’
|| i.invoice_number
|| ‘Reference=’
|| i.REFERENCE
);
END;

IF i.invoice_amount > 0 AND cm_due_amount > 0
THEN
SELECT COUNT (1)
INTO v_cm_count
FROM ar_payment_schedules_all aps
WHERE aps.trx_number = i.invoice_number;

SELECT COUNT (1)
INTO v_inv_count
FROM ar_payment_schedules_all aps, hz_cust_accounts hca
WHERE 1 = 1
AND hca.account_number = i.bill_customer_number
AND aps.customer_id = hca.cust_account_id
AND aps.trx_number = i.REFERENCE;

IF v_cm_count > 0 AND v_inv_count > 0
THEN
—————————————————
——Get credit memo due amount remaining ——-
—————————————————
BEGIN
SELECT -aps.amount_due_remaining, aps.payment_schedule_id
INTO v_cm_due_amount, v_cm_payment_schedule
FROM ar_payment_schedules_all aps, hz_cust_accounts hca
WHERE 1 = 1
AND hca.account_number = i.bill_customer_number
AND aps.customer_id = hca.cust_account_id
AND aps.trx_number = i.invoice_number
AND aps.status = ‘OP’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘NO1- Matching Records for CM due amount Not found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
printlog
( ‘NO1- Matching Records for CM due amount Not found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
WHEN TOO_MANY_ROWS
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘TOO1- More than one matching record for CM due amount found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
printlog
( ‘TOO1- More than one matching record for CM due amount found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
END;

—————————————————
——Get Incoice due amount remaining ——-
—————————————————
BEGIN
SELECT aps.amount_due_remaining, aps.payment_schedule_id
INTO v_inv_due_amount, v_inv_payment_schedule
FROM ar_payment_schedules_all aps, hz_cust_accounts hca
WHERE 1 = 1
AND hca.account_number = i.bill_customer_number
AND aps.customer_id = hca.cust_account_id
AND aps.trx_number = i.REFERENCE
AND aps.status = ‘OP’;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘NO2- Matching Records for invoice due amount Not found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
printlog
( ‘NO2- Matching Records for invoice due amount Not found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
WHEN TOO_MANY_ROWS
THEN
x_err_flag := ‘Y’;
x_err_msg :=
( ‘TOO2- More than one matching record for invoice due amount found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
printlog
( ‘TOO2- More than one matching record for invoice due amount found for the credit memo=’
|| i.invoice_number
|| ‘and invoice=’
|| i.REFERENCE
);
END;

/* if v_cm_due_amount > v_inv_due_amount then

v_amount_applied := v_inv_due_amount;
else
v_amount_applied := v_cm_due_amount;
end if;*/
IF i.invoice_amount > v_inv_due_amount
THEN
x_err_flag := ‘Y’;
x_err_msg :=
‘Invoice amount(CM amount) given is greater than the invoice due amount’;
ELSE
v_amount_applied := i.invoice_amount;
END IF;
ELSE
printlog (‘Skipped out Invoices => ‘ || i.invoice_number);
x_err_flag := ‘Y’;
x_err_msg :=
( ‘Either credit memo’
|| i.invoice_number
|| ‘ or invoice ‘
|| i.REFERENCE
|| ‘ is not found in the database’
);
END IF;

—————————————————
——Applying credit memos to the invoice———
—————————————————
IF x_err_flag = ‘N’
THEN
arp_process_application.cm_application
(p_cm_ps_id => v_cm_payment_schedule,
p_invoice_ps_id => v_inv_payment_schedule,
p_amount_applied => v_amount_applied,
p_apply_date => v_apply_date,
p_gl_date => v_gl_date,
p_ussgl_transaction_code => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_global_attribute_category => NULL,
p_global_attribute1 => NULL,
p_global_attribute2 => NULL,
p_global_attribute3 => NULL,
p_global_attribute4 => NULL,
p_global_attribute5 => NULL,
p_global_attribute6 => NULL,
p_global_attribute7 => NULL,
p_global_attribute8 => NULL,
p_global_attribute9 => NULL,
p_global_attribute10 => NULL,
p_global_attribute11 => NULL,
p_global_attribute12 => NULL,
p_global_attribute13 => NULL,
p_global_attribute14 => NULL,
p_global_attribute15 => NULL,
p_global_attribute16 => NULL,
p_global_attribute17 => NULL,
p_global_attribute18 => NULL,
p_global_attribute19 => NULL,
p_global_attribute20 => NULL,
p_customer_trx_line_id => v_customer_trx_line_id,
p_comments => v_comments,
p_module_name => v_module_name,
p_module_version => v_module_version,
p_out_rec_application_id => v_out_rec_application_id,
p_acctd_amount_applied_from => v_acctd_amount_applied_from,
p_acctd_amount_applied_to => v_acctd_amount_applied_to
);

IF v_out_rec_application_id IS NOT NULL
THEN
— dbms_output.put_line(‘Committing.’);
COMMIT;
printlog ( ‘Credit memo=’
|| i.invoice_number
|| ‘,’
|| ‘Invoice=’
|| i.REFERENCE
|| ‘,’
|| ‘CM amount=’
|| v_cm_due_amount
|| ‘,’
|| ‘Invoice amount=’
|| v_inv_due_amount
|| ‘,’
|| ‘Amount applied=’
|| v_amount_applied
);
ELSE
ROLLBACK;
END IF;
ELSE
printlog
(‘API Execution is not processed due to validation error’);
END IF;
ELSE
x_err_flag := ‘Y’;
x_err_msg :=
(‘Please check that either credit memo amount or invoice due amount is zero or program ran into exception
for either invoice or credit memo—>Please check log’);
END IF;

UPDATE xx_ar_interface_test
SET error_flag = x_err_flag,
error_msg = x_err_msg
WHERE invoice_number = i.invoice_number AND REFERENCE = i.REFERENCE;

COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
printlog (SQLERRM);
END;

PROCEDURE printlog (p_text_mesg VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, p_text_mesg);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, ‘Error in “printlog” procedure’);
END;
END xx_cn_cm_application_pkg;

What we expect in the script:-

This script helps us to comprehend how Credit memos to apply Invoices details imports into oracle through API. It also demonstrates the required validation taking place, additional validation can be incorporated anytime in your package based on business requirements. Post validation valid records are being passed into APIs.

Summary

This Post described the script/logic required for load the Credit Memos to the Invoices using API in Oracle EBS R12.

Got any queries?

Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.