User can able to delete the AR transaction in below cases are met:
- The invoice in the incomplete status.
- The invoice doesn’t posted.
- This is no receipt applied to the invoice.
Note:
- You might miss your invoice document sequence.
- Based on company policy, whether you can keep the document or remove from system.
If the Invoice in ‘complete‘ status, create a Credit Memo against this invoice and nullify the transaction.
AR invoice transaction using this package ‘ar_invoice_api_pub.delete_transaction’ in R12
Step 1: Incomplete the invoice.
— For R12 ——
–AR_TRANSACTION_GRP.INCOMPLETE_TRANSACTION
SET SERVEROUTPUT ON;
DECLARE
l_return_status VARCHAR2 (100);
l_message_count NUMBER;
l_message_data VARCHAR2 (1000);
lv_error_message VARCHAR2 (800);
BEGIN
mo_global.set_policy_context ('S', 800);
fnd_global.apps_initialize(0,51234,222);
ar_transaction_grp.incomplete_transaction
(p_api_version => '1.0',
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_none,
p_customer_trx_id => 198364532,
x_return_status => l_return_status,
x_msg_count => l_message_count,
x_msg_data => l_message_data
);
COMMIT;
IF l_return_status IN ('E', 'U')
THEN
FOR i IN 1 .. l_message_count
LOOP
lv_error_message :=
lv_error_message || '--' || fnd_msg_pub.get (i, 'F');
DBMS_OUTPUT.put_line ('l_return_status- ' || l_return_status);
END LOOP;
DBMS_OUTPUT.put_line ( 'API Failed. Error:'
|| SUBSTR (lv_error_message, 1, 800)
);
ELSE
DBMS_OUTPUT.put_line ('AR Invoice Incompleted successfully');
DBMS_OUTPUT.put_line ('lv_return_status-' || l_return_status);
END IF;
END ;Step 2: Check the following Prerequisites:
- Check if ‘Allow Transaction Deletion’ flag is Yes, If “No”, check the flag for respective operating unit.
Navigation : Receivables -> Setup -> System -> System Options
>> Query for respective operating unit
>> Click on ‘Trans and Customers’ Tab > Check for ‘Allow Transaction Deletion’ flag. - Transaction Should be incomplete.
- There should be no activity against the transaction like it must neither be applied, printed or posted to GL etc.
Step 3: Sample script for invoice deletion
------------------- Create Custom Table ---- For your Deleting Data Set --------------
CREATE TABLE OEAG.XXERP_AR_DELETE_TRANS
AS
SELECT CUSTOMER_TRX_ID,
TRX_DATE,
TRX_NUMBER,
BILL_TO_CUSTOMER_ID,
BILL_TO_SITE_USE_ID,
ORG_ID,
CREATION_DATE,
CREATED_BY,
'N' V_RET_STATUS,
'N' V_MSG_COUNT,
'N' V_MSG_DATA,
'N' V_MESSAGE_TBL
FROM RA_CUSTOMER_TRX_ALL
WHERE ORG_ID = 800
AND CUST_TRX_TYPE_ID = 1265
AND TRX_DATE = '20-MAR-2019';
---------------- START --- AR INVOICE DATA DELETE PROGRAM ---------------------
SET SERVEROUTPUT ON;
DECLARE
CURSOR cur_all_trx
IS
SELECT ROWID,
NULL PARTY_NUMBER,
RCT.ORG_ID,
RCT.CUSTOMER_TRX_ID,
RCT.TRX_NUMBER
FROM OEAG.XXERP_AR_DELETE_TRANS RCT
WHERE NVL (V_RET_STATUS, 0) <> 'S'
AND TRX_NUMBER = '900918263';
xv_msg_data VARCHAR2 (4000) := NULL;
xv_msg_count NUMBER := 0;
v_msg_index NUMBER := 0;
xv_ret_status VARCHAR2 (1) := NULL;
v_message_tbl arp_trx_validate.message_tbl_type;
v_res VARCHAR2 (4000) := NULL;
v_res_name VARCHAR2 (4000) := NULL;
v_app VARCHAR2 (4000) := NULL;
v_user NUMBER := 1110;
BEGIN
DBMS_OUTPUT.put_line ('Detele Transaction...');
FOR c_rec IN cur_all_trx
LOOP
DBMS_OUTPUT.put_line (' Transaction No.: ' || c_rec.trx_number);
DBMS_OUTPUT.put_line (' Transaction ID : ' || c_rec.customer_trx_id);
DBMS_OUTPUT.put_line (' Org ID : ' || c_rec.org_id);
----------------------------------------------------------------------------
---- Setting the org context for the particular session
apps.mo_global.set_policy_context ('S', c_rec.org_id);
-- apps.mo_global.init('AR');
SELECT application_id, responsibility_id
INTO v_app, v_res
FROM fnd_responsibility_tl
WHERE responsibility_id = 51234;
---- Setting the oracle applications context for the particular session
apps.fnd_global.apps_initialize (v_user, v_res, v_app);
----------------------------------------------------------------------------
xv_ret_status := NULL;
xv_msg_count := NULL;
xv_msg_data := NULL;
--update the Allow Transaction Deletion to Yes to Delete (As mentioned above, better to do it from application)
UPDATE ar_system_parameters_all
SET invoice_deletion_flag = 'Y'
WHERE org_id = c_rec.org_id;
ar_invoice_api_pub.delete_transaction (
p_api_name => 'Delete_Transaction',
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_customer_trx_id => c_rec.customer_trx_id,
p_return_status => xv_ret_status,
p_msg_count => xv_msg_count,
p_msg_data => xv_msg_data,
p_errors => v_message_tbl);
UPDATE OEAG.XXERP_AR_DELETE_TRANS
SET v_ret_status = xv_ret_status
WHERE ROWID = c_rec.ROWID;
UPDATE OEAG.XXERP_AR_DELETE_TRANS
SET v_msg_count = xv_msg_count
WHERE ROWID = c_rec.ROWID;
IF xv_ret_status <> 'S'
THEN
DBMS_OUTPUT.put_line (' Status: ' || xv_ret_status);
UPDATE OEAG.XXERP_AR_DELETE_TRANS
SET v_msg_data = v_ret_status
WHERE ROWID = c_rec.ROWID;
FOR i IN 1 .. xv_msg_count
LOOP
apps.fnd_msg_pub.get (i,
apps.fnd_api.g_false,
xv_msg_data,
v_msg_index);
DBMS_OUTPUT.put_line (' Error : ' || xv_msg_data);
END LOOP;
DBMS_OUTPUT.put_line (' ' || xv_msg_data);
ELSE
DBMS_OUTPUT.put_line (' Deleted.');
-- Revert back to the original value for the deletion flag
UPDATE ar_system_parameters_all
SET invoice_deletion_flag = 'N'
WHERE org_id = c_rec.org_id;
END IF;
DBMS_OUTPUT.put_line ('--------------------');
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error : ' || SQLERRM);
END;
/Note: User can able to delete the transaction front end also, after completing Step 2.
Recent Comments