In Oracle Apps R12, the process of applying on-account
credits to customer invoices can be complex. Oracle provides the AR_CM_API_PUB.APPLY_ON_ACCOUNT
API to facilitate this process. This API is particularly useful when there is a
need for programmatic application of credit memos (CM) to invoices.
API: AR_CM_API_PUB.APPLY_ON_ACCOUNT API
Staging Table Design:
CREATE TABLE XX_AR_CM_STG
(
TRANSACTION_ID NUMBER,
OPERATING_UNIT VARCHAR2(240),
ORG_ID NUMBER,
CM_NUMBER VARCHAR2(50), -- Credit Memo Number
INVOICE_NUMBER VARCHAR2(50), -- Invoice Number
APPLIED_AMOUNT NUMBER, -- Amount to be applied
APPLIED_DATE DATE,
CT_REFERENCE VARCHAR2(240),
CUSTOMER_NUMBER VARCHAR2(50), -- Customer number
STATUS VARCHAR2(1), -- NULL - Not Processed, E - Error, S - Successfully Processed
ERROR_MESSAGE VARCHAR2(4000), -- Error message in case of failure
LAST_UPDATE_DATE DATE DEFAULT SYSDATE,
LAST_UPDATED_BY NUMBER
);
Procedure:
create or replace PROCEDURE xx_apply_cm_to_invoice
AS
l_return_status VARCHAR2 (20);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (4000);
l_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
l_cm_trx_id ra_customer_trx_all.customer_trx_id%TYPE;
l_user_id NUMBER := fnd_global.user_id; -- Set current user ID
l_resp_id NUMBER := fnd_global.resp_id; -- Set current RESP ID
l_resp_appl_id NUMBER := fnd_global.resp_appl_id; -- Set current RESP APPL ID
l_error_message VARCHAR2 (4000); -- To capture error messages
k_api_version CONSTANT NUMBER := 1;
k_init_msg_list CONSTANT VARCHAR2 (1) := fnd_api.g_false;
k_comments ar_receivable_applications.comments%TYPE;
k_commit CONSTANT VARCHAR2 (1) := fnd_api.g_false;
l_acctd_amount_applied_from ar_receivable_applications_all.acctd_amount_applied_from%TYPE;
l_acctd_amount_applied_to ar_receivable_applications_all.acctd_amount_applied_to%TYPE;
l_cm_app_rec ar_cm_api_pub.cm_app_rec_type;
l_out_rec_application_id NUMBER;
l_msg_index_OUT number;
CURSOR c1 IS
SELECT cm_number,
invoice_number,
applied_amount,
org_id,
applied_date,
ct_reference,
status,
error_message
FROM xx_ar_cm_stg
WHERE NVL (status, 'N') = 'N';
BEGIN
mo_global.init ('AR');
mo_global.set_policy_context ('S', 101);
fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
FOR rec IN c1
LOOP
-- Initialize variables to NULL at the beginning of each iteration
l_return_status := NULL;
l_msg_count := NULL;
l_msg_data := NULL;
l_trx_id := NULL;
l_cm_trx_id := NULL;
l_error_message := NULL; -- Initialize error message to NULL
BEGIN
-- Get transaction ID for the Invoice
BEGIN
SELECT customer_trx_id
INTO l_trx_id
FROM ra_customer_trx_all
WHERE trx_number = rec.invoice_number
AND org_id = rec.org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message := 'Invoice number not found';
WHEN OTHERS
THEN
l_error_message :=
'Error fetching Invoice ID: ' || SQLERRM;
END;
-- Get transaction ID for the Credit Memo
IF l_error_message IS NULL
THEN -- Only proceed if no error from previous block
BEGIN
SELECT customer_trx_id
INTO l_cm_trx_id
FROM ra_customer_trx_all
WHERE trx_number = rec.cm_number AND org_id = rec.org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message := 'Credit Memo number not found';
WHEN OTHERS
THEN
l_error_message :=
'Error fetching Credit Memo ID: ' || SQLERRM;
END;
END IF;
-- Apply the credit memo if no errors so far
IF l_error_message IS NULL
THEN
l_cm_app_rec.cm_customer_trx_id := l_cm_trx_id;
l_cm_app_rec.cm_trx_number := NULL; -- credit memo number
l_cm_app_rec.inv_customer_trx_id := l_trx_id;
l_cm_app_rec.inv_trx_number := NULL; -- invoice number
l_cm_app_rec.installment := NULL;
l_cm_app_rec.applied_payment_schedule_id := NULL;
l_cm_app_rec.amount_applied := rec.applied_amount;
l_cm_app_rec.apply_date := rec.applied_date;
l_cm_app_rec.gl_date := rec.applied_date;
l_cm_app_rec.inv_customer_trx_line_id := NULL;
l_cm_app_rec.inv_line_number := NULL;
l_cm_app_rec.show_closed_invoices := NULL;
l_cm_app_rec.ussgl_transaction_code := NULL;
l_cm_app_rec.attribute_category := NULL;
l_cm_app_rec.attribute1 := NULL;
l_cm_app_rec.attribute2 := NULL;
l_cm_app_rec.attribute3 := NULL;
l_cm_app_rec.attribute4 := NULL;
l_cm_app_rec.attribute5 := NULL;
l_cm_app_rec.attribute6 := NULL;
l_cm_app_rec.attribute7 := NULL;
l_cm_app_rec.attribute8 := NULL;
l_cm_app_rec.attribute9 := NULL;
l_cm_app_rec.attribute10 := NULL;
l_cm_app_rec.attribute11 := NULL;
l_cm_app_rec.attribute12 := NULL;
l_cm_app_rec.attribute13 := NULL;
l_cm_app_rec.attribute14 := NULL;
l_cm_app_rec.attribute15 := NULL;
l_cm_app_rec.global_attribute_category := NULL;
l_cm_app_rec.global_attribute1 := NULL;
l_cm_app_rec.global_attribute2 := NULL;
l_cm_app_rec.global_attribute3 := NULL;
l_cm_app_rec.global_attribute4 := NULL;
l_cm_app_rec.global_attribute5 := NULL;
l_cm_app_rec.global_attribute6 := NULL;
l_cm_app_rec.global_attribute7 := NULL;
l_cm_app_rec.global_attribute8 := NULL;
l_cm_app_rec.global_attribute9 := NULL;
l_cm_app_rec.global_attribute10 := NULL;
l_cm_app_rec.global_attribute11 := NULL;
l_cm_app_rec.global_attribute12 := NULL;
l_cm_app_rec.global_attribute12 := NULL;
l_cm_app_rec.global_attribute14 := NULL;
l_cm_app_rec.global_attribute15 := NULL;
l_cm_app_rec.global_attribute16 := NULL;
l_cm_app_rec.global_attribute17 := NULL;
l_cm_app_rec.global_attribute18 := NULL;
l_cm_app_rec.global_attribute19 := NULL;
l_cm_app_rec.global_attribute20 := NULL;
l_cm_app_rec.comments := rec.ct_reference;
l_cm_app_rec.called_from := null;
ar_cm_api_pub.apply_on_account (
p_api_version => k_api_version,
p_init_msg_list => k_init_msg_list,
p_commit => k_commit,
p_cm_app_rec => l_cm_app_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_out_rec_application_id => l_out_rec_application_id,
x_acctd_amount_applied_from =>
l_acctd_amount_applied_from,
x_acctd_amount_applied_to => l_acctd_amount_applied_to);
dbms_output.put_line('l_return_status: ' || l_return_status);
dbms_output.put_line('l_msg_count: ' || l_msg_count);
dbms_output.put_line('l_msg_data: ' || l_msg_data);
-- Check return status of API call
IF l_return_status <> 'S'
THEN
-- Fetch error message if API failed
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_OUT => l_msg_index_OUT);
EXIT WHEN l_msg_data IS NULL;
END LOOP;
l_error_message := l_msg_data;
END IF;
FOR i IN 1 .. l_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => i,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_OUT => l_msg_index_OUT);
dbms_output.put_line('Error: '|| l_msg_data);
EXIT WHEN l_msg_data IS NULL;
END LOOP;
END IF;
-- Update the staging table with either success or error
UPDATE xx_ar_cm_stg
SET status = l_return_status,
error_message = l_error_message, -- Update with error message if any
last_update_date = SYSDATE,
last_updated_by = l_user_id
WHERE cm_number = rec.cm_number
AND invoice_number = rec.invoice_number;
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('ERROR_STACK: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
dbms_output.put_line('ERROR_BACKTRACE: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
l_error_message := 'Unhandled exception: ' || SQLERRM;
-- Single update for unhandled exceptions
UPDATE xx_ar_cm_stg
SET status = 'E',
error_message = l_error_message,
last_update_date = SYSDATE,
last_updated_by = l_user_id
WHERE cm_number = rec.cm_number
AND invoice_number = rec.invoice_number;
END;
END LOOP;
END xx_apply_cm_to_invoice;
Execution:
set SERVEROUTPUT on;
BEGIN
XX_APPLY_CM_TO_INVOICE();
--rollback;
END;
/
Error Handling:
The AR_CM_API_PUB.APPLY_ON_ACCOUNT API provides error
messages through the FND_MSG_PUB package. In the above code, we loop through
any returned messages and store them in the staging table for troubleshooting.
Comments
Post a Comment