set SERVEROUTPUT ON SIZE 1000000
set headsep off
set pagesize 0
set trimspool on
set lines 240
spool E:\prebill.csv
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;
/
Requirement: There is a lookup in fnd lookups table, business wants that lookup to be modified by them, adding lookup values, modifying and disabling them.
Instead of giving the FND_LOOKUPS function, we want to give them the AR Lookups function for them to modify the lookup values.
Below is the Anonymous block, we have written to get the values from FND Lookups table to AR Lookups table.
DECLARE
ln_rowid VARCHAR2 (1000);
ln_rowid1 VARCHAR2 (1000);
BEGIN
fnd_lookup_types_pkg.insert_row (x_rowid => ln_rowid,
x_lookup_type => 'XX_PO_BOXES',
x_security_group_id => 0,
x_view_application_id => 222,
x_application_id => 222,
x_customization_level => 'U',
x_meaning => 'Lookup for PO Boxes',
x_description => 'Lookup for PO Boxes',
x_creation_date => SYSDATE,
x_created_by => 0,
x_last_update_date => SYSDATE,
x_last_updated_by => 0,
x_last_update_login => -1
);
COMMIT;
for i in (select lookup_code,
start_date_active,
end_date_active,
tag,
meaning,
description
from fnd_lookup_values_vl
where lookup_type = 'XX_PO_BOXES'
)
loop
fnd_lookup_values_pkg.insert_row (x_rowid => ln_rowid1,
x_lookup_type => 'XX_PO_BOXES',
x_security_group_id => 0,
x_view_application_id => 222,
x_lookup_code => i.lookup_code,
x_tag => i.tag,
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_enabled_flag => 'Y',
x_start_date_active => i.start_date_active,
x_end_date_active => i.end_date_active,
x_territory_code => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_meaning => i.meaning,
x_description => i.description,
x_creation_date => SYSDATE,
x_created_by => 0,
x_last_update_date => SYSDATE,
x_last_updated_by => 0,
x_last_update_login => -1
);
COMMIT;
end loop;
EXCEPTION WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No Data Found');
WHEN OTHERS THEN
NULL;
END;
/
