Script to load or convert Notes/Attachments associtaed with items

Script to load or convert Notes/Attachments associtaed with items

-- Script to load or convert Notes/Attachments associated with items.
DECLARE
l_doc_category_id NUMBER;
l_document_id NUMBER;
l_attached_document_id NUMBER;
ll_media_id NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
BEGIN
-- Select User_id
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_name ='XXX';
-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE name ='SHORT_TEXT';
-- Select Category id for "Vendor/To Supplier" Attachments
SELECT category_id
INTO l_doc_category_id
FROM apps.fnd_document_categories
WHERE name = 'Vendor';
-- Select nexvalues of document id, attached document id and
-- l_media_id
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL,
apps.fnd_documents_short_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id,
l_media_id
FROM DUAL;
INSERT INTO apps.fnd_documents
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
datatype_id,
category_id,
security_type,
security_id,
publish_flag,
usage_type
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
l_short_datatype_id, -- Datatype for 'SHORT_TEXT'
l_doc_category_id, -- Category_id
1, -- 'Organization' Level Security
352, -- Organization id for Inventory Item Master Org
'Y', -- Publish_flag
'O' -- Usage_type of 'One Time'
);
INSERT INTO apps.fnd_documents_tl
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
language,
description,
media_id,
translated
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
'AMERICAN', -- language
'EXTENDED DESCRIPTION', -- description
l_media_id, -- media_id
'Y' -- translated
);
INSERT INTO apps.fnd_attached_documents
(attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
seq_num,
entity_name,
pk1_value,
pk2_value,
automatically_added_flag
)
VALUES
(l_attached_document_id,
l_document_id,
SYSDATE,
lcl_fnd_user_id,
SYSDATE,
lcl_fnd_user_id,
20, -- Sequence Number of attachment.
'MTS_SYSTEM_ITEMS', -- Entity_name Table Name assoicated with attachment
352, -- Organization id for Inventory Item Master Org
567, -- Inventory Item Id
'N' -- Automatically_added_flag
);

INSERT INTO apps.fnd_documents_short_text
(media_id,
short_text
)
VALUES
(lcl_media_id,
'Write your Short Text Here' -- Notes/Attachments text
);
COMMIT;
END;
/