Use following API for
Delete AR invoices.
AR invoice Data delete script from the AR Base table using 'ar_invoice_api_pub.delete_transaction' in Oracle APPS EBS R12
Step 1: incomplete the invoices
-- For R12 ------ AR_TRANSACTION_GRP.INCOMPLETE_
using this script to
------------------------------------------ START -- Incomplete the Invoices ---------------------------------------------
DECLARE
IS
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', 201);
--fnd_global.apps_initialize(2373,50603,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 => 2424,
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 Incomplete sucessfully');
DBMS_OUTPUT.put_line ('lv_return_status-' || l_return_status);
END IF;
END ;
--------------------------------------- END -- Incomplete the Invoices ---------------------------------------------
Step 2: Check the following Prerequisites :
1. 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' > Check for 'Allow Transaction Deletion' flag.
2. Transaction Should be incomplete.
3. 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
Step 3(a): Please make a custom table for your Deleting AP Invoice Data.
-------------------- Create Custom Table ---- For your Deleting Data Set --------------
CREATE TABLE XX_DELETE_TRANSACTION
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 = 101
AND CUST_TRX_TYPE_ID = 4000 -- MIG LOCAL INVOICE
AND TRUNC (CREATION_DATE) = '04-FEB-18';
-- AND TRX_NUMBER = 'JMFL/IFL/066/17';
--DROP TABLE FROM XX_DELETE_TRANSACTION;
----------------------------------------------------------
Step 3(B) :
---------------- START --- AR INVOICE DATA DELETE PROGRAM ---------------------
DECLARE
CURSOR cur_all_trx
IS
SELECT ROWID,
NULL PARTY_NUMBER,
RCT.ORG_ID,
RCT.CUSTOMER_TRX_ID,
RCT.TRX_NUMBER
FROM XX_DELETE_TRANSACTION RCT
WHERE NVL (V_RET_STATUS, 0) <> 'S'
AND TRX_NUMBER = 'JMFL/IFL/066/17';
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 = 50778;
---- 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 xx_delete_transaction
SET v_ret_status = xv_ret_status
WHERE ROWID = c_rec.ROWID;
UPDATE xx_delete_transaction
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 xx_delete_transaction
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;
---------------- END --- AR INVOICE DATA DELETE PROGRAM ----------------------
No comments:
Post a Comment