Below given script you can use to insert / update short text attachment for AR invoice.
Kindly pass parameter TRX_Number value for which you want to add short text.
====================================================
DECLARE
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_user_id NUMBER;
l_category_id NUMBER;
l_pk1_value fnd_attached_documents.pk1_value%TYPE;
l_description fnd_documents_tl.description%TYPE;
l_seq_num NUMBER;
lv_short_text VARCHAR2(1000);
lv_bill_address1 VARCHAR2(1000);
lv_bill_address2 VARCHAR2(1000);
lv_bill_city VARCHAR2(1000);
lv_bill_state VARCHAR2(1000);
lv_bill_countryid VARCHAR2(1000);
lv_bill_postalcode VARCHAR2(1000);
l_title VARCHAR2(1000);
l_security_id NUMBER;
l_storage_type VARCHAR2(1000);
l_image_type VARCHAR2(1000);
l_start_date_active DATE;
l_end_date_active DATE;
l_usage_type VARCHAR2(1000);
lv_notes_cnt NUMBER;
lv_bill_company VARCHAR2(1000);
BEGIN
SELECT customer_trx_id into l_pk1_value
from ra_customer_trx_all
where trx_number=&TRX_NUMBER; --AR TRANSACTION NUMBER
SELECT user_id into l_user_id
from fnd_user
where user_name ='XXUSER';
BEGIN
SELECT COUNT(1)
INTO lv_notes_cnt
FROM fnd_attached_documents
WHERE entity_name = 'RA_CUSTOMER_TRX'
AND pk1_value = l_pk1_value;
EXCEPTION
WHEN OTHERS THEN
lv_notes_cnt := NULL;
END;
IF lv_notes_cnt = 0 THEN
l_description := 'Line level Notes';
BEGIN
SELECT fnd_documents_s.NEXTVAL INTO l_document_id FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
l_document_id := 1;
END;
BEGIN
SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;
EXCEPTION
WHEN OTHERS THEN
l_attached_document_id := 1;
END;
BEGIN
SELECT NVL(MAX(seq_num), 0) + 10
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value
AND entity_name = 'RA_CUSTOMER_TRX';
EXCEPTION
WHEN OTHERS THEN
l_seq_num := 1;
END;
BEGIN
SELECT category_id
INTO l_category_id
FROM fnd_document_categories_vl
WHERE name = 'MISC';
EXCEPTION
WHEN OTHERS THEN
l_category_id := NULL;
END;
fnd_documents_pkg.insert_row(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_user_id,
x_last_update_login => l_user_id,
x_datatype_id => 1,
x_category_id => l_category_id,
x_security_type => 2,
x_publish_flag => 'Y',
x_usage_type => 'O',
x_language => 'US',
x_description => l_description,
x_file_name => NULL,
x_title => 'Line level Notes',
x_media_id => l_media_id);
fnd_documents_pkg.insert_tl_row(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_user_id,
x_last_update_login => l_user_id,
x_language => 'US',
x_description => l_description
);
fnd_attached_documents_pkg.insert_row(x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => l_user_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_user_id,
x_last_update_login => l_user_id,
x_seq_num => l_seq_num,
x_entity_name => 'RA_CUSTOMER_TRX',
x_column1 => NULL,
x_pk1_value => l_pk1_value,
x_pk2_value => NULL,
x_pk3_value => NULL,
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => 'N',
x_datatype_id => 1,
x_category_id => l_category_id,
x_security_type => 2,
x_publish_flag => 'Y',
x_language => 'US',
x_description => l_description,
x_media_id => l_media_id);
DBMS_OUTPUT.PUT_LINE ('Media id '||l_media_id);
lv_short_text := 'Test for short text attachment INSERT';
INSERT INTO fnd_documents_short_text
VALUES
(l_media_id, lv_short_text, NULL);
COMMIT;
ELSIF lv_notes_cnt > 0 THEN
SELECT MAX(document_id),
category_id,
seq_num,
ROWID,
attached_document_id
INTO l_document_id,
l_category_id,
l_seq_num,
l_rowid,
l_attached_document_id
FROM fnd_attached_documents
WHERE entity_name = 'RA_CUSTOMER_TRX'
AND pk1_value = l_pk1_value
GROUP BY category_id, seq_num, ROWID, attached_document_id;
SELECT description,
title,
fd.media_id,
fd.security_id,
fd.storage_type,
fd.image_type,
fd.start_date_active,
fd.end_date_active,
fd.usage_type
INTO l_description,
l_title,
l_media_id,
l_security_id,
l_storage_type,
l_image_type,
l_start_date_active,
l_end_date_active,
l_usage_type
FROM fnd_documents_tl fdt, fnd_documents fd
WHERE fdt.document_id = fd.document_id
AND fdt.document_id = l_document_id;
---------------------
BEGIN
fnd_documents_pkg.update_row(x_document_id => l_document_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_user_id,
x_last_update_login => l_user_id,
x_datatype_id => 1,
x_category_id => l_category_id,
x_security_type => 2,
x_security_id => l_security_id,
x_publish_flag => 'Y',
x_image_type => l_image_type,
x_storage_type => l_storage_type,
x_usage_type => 'O',
x_start_date_active => l_start_date_active,
x_end_date_active => l_end_date_active,
x_language => 'US',
x_description => l_description,
x_file_name => NULL,
x_media_id => l_media_id,
x_title => l_title);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
fnd_documents_pkg.update_tl_row(x_document_id => l_document_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_user_id,
x_last_update_login => l_user_id,
x_language => 'US',
x_description => l_description,
x_title => l_title);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
BEGIN
fnd_attached_documents_pkg.update_row(x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_last_update_date => SYSDATE,
x_last_updated_by => l_user_id,
x_last_update_login => l_user_id,
x_seq_num => l_seq_num,
x_entity_name => 'RA_CUSTOMER_TRX',
x_column1 => NULL,
x_pk1_value => l_pk1_value,
x_pk2_value => NULL,
x_pk3_value => NULL,
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => 'N',
x_datatype_id => 1,
x_category_id => l_category_id,
x_security_type => 2,
x_publish_flag => 'Y',
x_usage_type => l_usage_type,
x_start_date_active => l_start_date_active,
x_end_date_active => l_end_date_active,
x_language => 'US',
x_description => l_description,
x_media_id => l_media_id,
x_title => l_title);
DBMS_OUTPUT.PUT_LINE ('Media id '||l_media_id);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
lv_short_text := 'Test for short text attachment UPDATE';
UPDATE fnd_documents_short_text SET short_text = lv_short_text WHERE media_id = l_media_id;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
====================================================================
CHECK FOR AR TRANSACTION ATTACHMENT POST SCRIPT RUN
