Requirement:- We have an issue, 2 users created the AR Invoice with the Same Invoice number and same gl_date and Unable to open the form as it is saying "Duplicate invoice number cannot be completed or voided in Oracle".
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 = 103 AND customer_trx_id = '2800613' ;
/
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';
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 := 0;
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 = 50331
and Language ='US';
apps.fnd_global.apps_initialize (v_user, v_res, v_app);
----------------------------------------------------------------------------
xv_ret_status := NULL;
xv_msg_count := NULL;
xv_msg_data := NULL;
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;
/
No comments:
Post a Comment