Delete AR Transaction Using API:
Before delete AR transaction "Allow Transaction Deletion" flag should be Y.
Allow Transaction Deletion:
Enable the Allow transaction deletion option if you want to let users delete transactions from Oracle Receivables after the transactions have been saved.
You will update this option at Operating Unit level.
Check in database table:
SELECT invoice_deletion_flag
FROM ar_system_parameters_all ab
WHERE org_id = 201; -- Operating Unit org_id
If value of 'invoice_deletion_flag' is Y, its mean you are enable to delete AR Transaction, otherwise you need to do setup for this.
Setup:
Navigation: Receivable Responsibility --> Setup --> System --> System Options
Select 'Trans and Customers' Tab:
Click on "Allow Transaction Deletion" button and Save it.
Check in database table.
To delete AR Transaction, follow below steps:
Step 1) Create a staging table to stored data related to AR transaction.
CREATE TABLE xx_delete_ar_transaction
AS
SELECT customer_trx_id,
trx_date,
trx_number,
org_id,
creation_date,
created_by,
'N' record_status
FROM ra_customer_trx_all
WHERE org_id = 201
AND trx_number IN ('123000', '124000');
Step 2) Call API in PLSQL block to Delete AR Transactions:
DECLARE
CURSOR lcur_ar_trans
IS
SELECT ROWID,
customer_trx_id,
trx_date,
trx_number,
org_id,
creation_date,
created_by
FROM xx_delete_ar_transaction
WHERE record_status = 'N';
lc_msg_data VARCHAR2(4000);
ln_msg_count NUMBER;
lc_ret_status VARCHAR2(1) := 'S';
v_message_tbl arp_trx_validate.message_tbl_type;
ln_user_id NUMBER;
ln_responsibility_id NUMBER;
ln_application_id NUMBER;
ln_msg_index NUMBER;
BEGIN
--
ln_user_id := 0;
ln_application_id := 0;
ln_responsibility_id := 0;
-- Get application and responsibility id's
BEGIN
SELECT application_id, responsibility_id
INTO ln_application_id, ln_responsibility_id
FROM fnd_responsibility_tl
WHERE responsibility_name = 'Responsibility Name'; -- Enter valid Responsibility Name
EXCEPTION
WHEN others THEN
lc_ret_status := 'E';
dbms_output.put_line('Error at getting Responsibility id: '||SQLERRM);
END;
-- Get User ID
BEGIN
SELECT user_id
INTO ln_user_id
FROM fnd_user
WHERE user_name = 'ABC007'; -- Enter valid User Name
EXCEPTION
WHEN others THEN
lc_ret_status := 'E';
dbms_output.put_line('Error at getting User id: '||SQLERRM);
END;
--
FOR lreq_ar IN lcur_ar_trans
LOOP
-- Variable initialization:
ln_msg_count := NULL;
lc_msg_data := NULL;
--
dbms_output.put_line ('--> TRX Number: '|| lreq_ar.trx_number);
-- Set Org Context for the session
apps.mo_global.set_policy_context ('S', lreq_ar.org_id);
-- Set Oracle applications context
apps.fnd_global.apps_initialize (ln_user_id, ln_responsibility_id, ln_application_id);
-- Call API
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 => lreq_ar.customer_trx_id,
p_return_status => lc_ret_status,
p_msg_count => ln_msg_count,
p_msg_data => lc_msg_data,
p_errors => v_message_tbl);
IF lc_ret_status <> 'S' THEN
dbms_output.put_line ('--> Status: '||lc_ret_status);
BEGIN
UPDATE xx_delete_ar_transaction
SET record_status = lc_ret_status
WHERE ROWID = lreq_ar.ROWID;
EXCEPTION
WHEN others THEN
dbms_output.put_line('Error at update staging table: '||SQLERRM);
END;
FOR i IN 1 .. ln_msg_count
LOOP
apps.fnd_msg_pub.get (i,
apps.fnd_api.g_false,
lc_msg_data,
ln_msg_index
);
dbms_output.put_line ('Error: '||lc_msg_data);
END LOOP;
ELSE
dbms_output.put_line ('AR Transaction Successfully Deleted.');
END IF;
dbms_output.put_line ('--------------------');
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Error in main block: ' || SQLERRM);
END;
/
Before delete AR transaction "Allow Transaction Deletion" flag should be Y.
Allow Transaction Deletion:
Enable the Allow transaction deletion option if you want to let users delete transactions from Oracle Receivables after the transactions have been saved.
You will update this option at Operating Unit level.
Check in database table:
SELECT invoice_deletion_flag
FROM ar_system_parameters_all ab
WHERE org_id = 201; -- Operating Unit org_id
If value of 'invoice_deletion_flag' is Y, its mean you are enable to delete AR Transaction, otherwise you need to do setup for this.
Setup:
Navigation: Receivable Responsibility --> Setup --> System --> System Options
Select 'Trans and Customers' Tab:
Click on "Allow Transaction Deletion" button and Save it.
Check in database table.
To delete AR Transaction, follow below steps:
Step 1) Create a staging table to stored data related to AR transaction.
CREATE TABLE xx_delete_ar_transaction
AS
SELECT customer_trx_id,
trx_date,
trx_number,
org_id,
creation_date,
created_by,
'N' record_status
FROM ra_customer_trx_all
WHERE org_id = 201
AND trx_number IN ('123000', '124000');
Step 2) Call API in PLSQL block to Delete AR Transactions:
DECLARE
CURSOR lcur_ar_trans
IS
SELECT ROWID,
customer_trx_id,
trx_date,
trx_number,
org_id,
creation_date,
created_by
FROM xx_delete_ar_transaction
WHERE record_status = 'N';
lc_msg_data VARCHAR2(4000);
ln_msg_count NUMBER;
lc_ret_status VARCHAR2(1) := 'S';
v_message_tbl arp_trx_validate.message_tbl_type;
ln_user_id NUMBER;
ln_responsibility_id NUMBER;
ln_application_id NUMBER;
ln_msg_index NUMBER;
BEGIN
--
ln_user_id := 0;
ln_application_id := 0;
ln_responsibility_id := 0;
-- Get application and responsibility id's
BEGIN
SELECT application_id, responsibility_id
INTO ln_application_id, ln_responsibility_id
FROM fnd_responsibility_tl
WHERE responsibility_name = 'Responsibility Name'; -- Enter valid Responsibility Name
EXCEPTION
WHEN others THEN
lc_ret_status := 'E';
dbms_output.put_line('Error at getting Responsibility id: '||SQLERRM);
END;
-- Get User ID
BEGIN
SELECT user_id
INTO ln_user_id
FROM fnd_user
WHERE user_name = 'ABC007'; -- Enter valid User Name
EXCEPTION
WHEN others THEN
lc_ret_status := 'E';
dbms_output.put_line('Error at getting User id: '||SQLERRM);
END;
--
FOR lreq_ar IN lcur_ar_trans
LOOP
-- Variable initialization:
ln_msg_count := NULL;
lc_msg_data := NULL;
--
dbms_output.put_line ('--> TRX Number: '|| lreq_ar.trx_number);
-- Set Org Context for the session
apps.mo_global.set_policy_context ('S', lreq_ar.org_id);
-- Set Oracle applications context
apps.fnd_global.apps_initialize (ln_user_id, ln_responsibility_id, ln_application_id);
-- Call API
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 => lreq_ar.customer_trx_id,
p_return_status => lc_ret_status,
p_msg_count => ln_msg_count,
p_msg_data => lc_msg_data,
p_errors => v_message_tbl);
IF lc_ret_status <> 'S' THEN
dbms_output.put_line ('--> Status: '||lc_ret_status);
BEGIN
UPDATE xx_delete_ar_transaction
SET record_status = lc_ret_status
WHERE ROWID = lreq_ar.ROWID;
EXCEPTION
WHEN others THEN
dbms_output.put_line('Error at update staging table: '||SQLERRM);
END;
FOR i IN 1 .. ln_msg_count
LOOP
apps.fnd_msg_pub.get (i,
apps.fnd_api.g_false,
lc_msg_data,
ln_msg_index
);
dbms_output.put_line ('Error: '||lc_msg_data);
END LOOP;
ELSE
dbms_output.put_line ('AR Transaction Successfully Deleted.');
END IF;
dbms_output.put_line ('--------------------');
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ('Error in main block: ' || SQLERRM);
END;
/
No comments:
Post a Comment