1 SET UP REQUIREMENTS

1..1 Document Category Setup

Document Category Define the type of attachment .An attachment can be from : Short Text, Long Text,

Image, File, Web Page.

This Document gives illustration of attaching Short Text, Long Text,File Type of Attachments.

Steps for Setting up Document Category are :

Log Into Application Developer Responsibility
Navigate to Attachement => Document Categories.
Create an record for Desired Attachement Category ( Mostly Standard Document Categories

are available .

Invoice Internal is the Standard Document category for Invoices.

Internal to Purchase Order is the Standard Document category for Purchase Orders.

Click on Assignments.
Category Assignment Screen opens. This Screen enables an attachement to be viewed in an Form

Form Function. Assign the Document Category to the Desired Form / Form Function.

In this Document ,

Invoices have been assigned to Invoices Overview Form / Invoices Function

Thus , the Invoices Attachements can be viewed in Invoices Overview Form in AP Application.

Purchase Orders have been assigned to Purchase Orders Summary Function

Thus , the Purchase Order can be viewed in Purchase Orders Summary Function in PO

Application.

1..2 Document Entity Setup

Document Entity defines the Table Name, which contains the Key Values for the Attachements,

For Invoices and Purchase Orders , Standard Document Entities have been defined.

For AP Invoices , AP_INVOICES is Standard Document Entitiy,

Base Table is FND_DOCUMENT_ENTITIES_VL

pk1_column of this table contains INVOICE_ID.

For Purchase Order Headers , PO_HEADERS is Standard Document Entitiy,

Base Table is FND_DOCUMENT_ENTITIES_VL

pk1_column of this table contains PO_HEADER_ID.

2 PL/SQL Code For Adding Attachments

2..1 Invoice Attachments

2..1.1 Attaching text to invoices

DECLARE

p_out VARCHAR2(100);

p_doc_id NUMBER;

p_attach NUMBER;

p_media_id NUMBER;

p_text LONG := 'This can be a Long text having a length more than 4000 char';

ln_number NUMBER;

BEGIN

SELECT (FND_ATTACHED_DOCUMENTS_S.NEXTVAL+1000)

INTO ln_number

FROM dual;

FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW

(

X_Rowid => p_out,

X_attached_document_id => ln_number,

X_document_id => p_doc_id,

X_creation_date => SYSDATE,

X_created_by =>1318,

X_last_update_date =>SYSDATE ,

X_last_updated_by =>1318,

X_last_update_login =>1318,

X_seq_num =>700,

X_entity_name => 'AP_INVOICES',

X_column1 => '',

X_pk1_value =>10807,

X_pk2_value => '',

X_pk3_value => '',

X_pk4_value => '',

X_pk5_value => '',

X_automatically_added_flag => 'N',

X_datatype_id=>2,

X_category_id=>327,

X_usage_type => 'S',

X_language => 'US',

X_media_id => p_media_id,

X_security_type => 2,

X_publish_flag => 'Y',

X_storage_type => 1

);

INSERT INTO FND_DOCUMENTS_LONG_TEXT

(

media_id,

long_text

)

VALUES

(

p_media_id,

p_text

);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

dbms_output.put_line('An exception occurred');

dbms_output.put_line(sqlcode || sqlerrm);

END;

X_last_updated_by	User Id from FND_USER
X_created_by	User Id from FND_USER
X_last_updated_by	User Id from FND_USER
X_last_update_login	User Id from FND_USER
X_seq_num	Any Number
X_pk1_value	INVIOCE_ID of Target Invoices in AP_INVOICES/ AP_INVOICES_ALL
X_entity_name	From the Setup
X_datatype_id	Data type id for Long Text Data Type in FND_DOCUMENT_DATATYPES
X_category_id	291 is the Category Id For Invoice Internal Category
X_usage_type	Look up code from Lookup ATCHMT_DOCUMENT_TYPE
program_name	Oracle Reads this Program Name to Attach the Document

2..1.2 Attaching JPEG/WORD Documents to invoices

1. Create a schema table to be used for storing the Document

CREATE TABLE blobtab (index_col NUMBER, blob_col BLOB) .

INSERT INTO BLOBTAB (INDEX_COL, BLOB_COL) VALUES (2,EMPTY_BLOB ());

2. Place the JPEG/BMP/WORD Document in an Drectory accessible to Oracle.

This can be found by querying in ALL_DIRECTORIES.

3. Exceute the Folowing PL/SQL Code .

DECLARE

p_out VARCHAR2 (100);

p_doc_id NUMBER;

p_attach NUMBER;

p_media_id NUMBER;

ln_number NUMBER;

file_s BFILE := BFILENAME ('XXI_OM_UTL', 'test.jpg');

/* BFILENAME Function takes Directory Name and File Name as Input Parameters */

src_offset INTEGER := 1;

dest_offset INTEGER := 1;

p_blob BLOB;

asize INTEGER;

BEGIN

SELECT (fnd_attached_documents_s.NEXTVAL + 1000)

INTO ln_number

FROM DUAL;

SELECT blob_col

INTO p_blob

FROM blobtab

WHERE index_col = 2

FOR UPDATE;

DBMS_LOB.FILEOPEN (file_s, DBMS_LOB.file_readonly);

asize := DBMS_LOB.GETLENGTH (file_s);

DBMS_LOB.LOADBLOBFROMFILE (p_blob, file_s, asize, dest_offset, src_offset);

FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW

( x_rowid => p_out,

x_attached_document_id => ln_number,

x_document_id => p_doc_id,

x_creation_date => SYSDATE,

x_created_by => 1318,

x_last_update_date => SYSDATE,

x_last_updated_by => 1318,

x_last_update_login => 1318,

x_seq_num => 900,

x_entity_name => 'AP_INVOICES',

x_column1 => '',

x_pk1_value => 10807,

x_pk2_value => '',

x_pk3_value => '',

x_pk4_value => '',

x_pk5_value => '',

x_automatically_added_flag => 'N',

x_datatype_id => 6,

x_category_id => 291,

x_usage_type => 'S',

x_language => 'US',

x_media_id => p_media_id,

x_security_type => 2,

x_publish_flag => 'Y',

x_storage_type => 1,

x_file_name => 'XYZ'

);

INSERT INTO fnd_lobs

(file_id, file_name, file_content_type, upload_date,

file_data, file_format, program_name

)

VALUES (p_media_id, 'XYZ', 'text/plain', SYSDATE,

p_blob, 'text', 'FNDATTCH'

);

COMMIT;

DBMS_LOB.fileclose (file_s);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('An exception occurred');

DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);

END;

X_last_updated_by	User Id from FND_USER
X_created_by	User Id from FND_USER
X_last_updated_by	User Id from FND_USER
X_last_update_login	User Id from FND_USER
X_seq_num	Any Number
X_pk1_value	INVIOCE_ID of Target Invoice in AP_INVOICES/ AP_INVOICES_ALL
X_entity_name	From the Setup
X_datatype_id	Data type id for File Data Type in FND_DOCUMENT_DATATYPES
X_category_id	291 is the Category Id For Invoice Internal Category
X_usage_type	Look up code from Lookup ATCHMT_DOCUMENT_TYPE
program_name	Oracle Reads this Program Name to Attach the Document

2..2 Purchase Order Attachements

2..2.1 Attaching text to Purchase Orders

DECLARE

p_out VARCHAR2 (100);

p_doc_id NUMBER;

p_attach NUMBER;

p_media_id NUMBER;

p_text LONG := 'This can be a Long text having a length more than 2000 char';

ln_number NUMBER;

BEGIN

SELECT (fnd_attached_documents_s.NEXTVAL + 1000)

INTO ln_number

FROM DUAL;

FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW

( x_rowid => p_out,

x_attached_document_id => ln_number,

x_document_id => p_doc_id,

x_creation_date => SYSDATE,

x_created_by => 1318,

x_last_update_date => SYSDATE,

x_last_updated_by => 1318,

x_last_update_login => 1318,

x_seq_num => 200,

x_entity_name => 'PO_HEADERS',

x_column1 => '',

x_pk1_value => 1,

x_pk2_value => '',

x_pk3_value => '',

x_pk4_value => '',

x_pk5_value => '',

x_automatically_added_flag => 'N',

x_datatype_id => 2,

x_category_id => 39,

x_usage_type => 'S',

x_language => 'US',

x_media_id => p_media_id,

x_security_type => 2,

x_publish_flag => 'Y',

x_storage_type => 1

);

INSERT INTO fnd_documents_long_text

(media_id, long_text

)

VALUES (p_media_id, p_text

);

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('An exception occurred');

DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);

END;

X_last_updated_by	User Id from FND_USER
X_created_by	User Id from FND_USER
X_last_updated_by	User Id from FND_USER
X_last_update_login	User Id from FND_USER
X_seq_num	Any Number
X_pk1_value	PO_HEADER_ID of Target PO in PO_HEADERS_ALL
X_entity_name	From the Setup
X_datatype_id	Data type id for Long Text Type in FND_DOCUMENT_DATATYPES
X_category_id	39 is Category Id for Internal for Purchase Orders in FND_DOCUMENT_CATEGORIES_L
X_usage_type	Look up code from Lookup ATCHMT_DOCUMENT_TYPE

2..2.2 Attaching JPEG/WORD Documents to Purchase Orders

1. Create a schema table to be used for storing the Document

CREATE TABLE blobtab(index_col NUMBER , blob_col BLOB) .

INSERT INTO BLOBTAB(INDEX_COL,BLOB_COL) VALUES(3,EMPTY_BLOB());

2. Place the JPEG/BMP/WORD Document in an Drectory accessible to Oracle.

This can be found by querying in ALL_DIRECTORIES.

3. Execute the Folowing PL/SQL Code .

DECLARE

p_out VARCHAR2 (100);

p_doc_id NUMBER;

p_attach NUMBER;

p_media_id NUMBER;

ln_number NUMBER;

file_s BFILE := BFILENAME ('XXI_OM_UTL', 'test.jpg');

src_offset INTEGER := 1;

dest_offset INTEGER := 1;

p_blob BLOB;

asize INTEGER;

BEGIN

SELECT (fnd_attached_documents_s.NEXTVAL + 1000)

INTO ln_number

FROM DUAL;

SELECT blob_col

INTO p_blob

FROM blobtab

WHERE index_col = 3

FOR UPDATE;

DBMS_LOB.fileopen (file_s, DBMS_LOB.file_readonly);

asize := DBMS_LOB.getlength (file_s);

DBMS_LOB.loadblobfromfile (p_blob, file_s, asize, dest_offset, src_offset);

FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW

( x_rowid => p_out,

x_attached_document_id => ln_number,

x_document_id => p_doc_id,

x_creation_date => SYSDATE,

x_created_by => 1318,

x_last_update_date => SYSDATE,

x_last_updated_by => 1318,

x_last_update_login => 1318,

x_seq_num => 100,

x_entity_name => 'PO_HEADERS',

x_column1 => '',

x_pk1_value => 1,

x_pk2_value => '',

x_pk3_value => '',

x_pk4_value => '',

x_pk5_value => '',

x_automatically_added_flag => 'N',

x_datatype_id => 6,

x_category_id => 39,

x_usage_type => 'S',

x_language => 'US',

x_media_id => p_media_id,

x_security_type => 2,

x_publish_flag => 'Y',

x_storage_type => 1,

x_file_name => 'XYZ'

);

INSERT INTO fnd_lobs

(file_id, file_name, file_content_type, upload_date,

file_data, file_format, program_name

)

VALUES (p_media_id, 'XYZ', 'text/plain', SYSDATE,

p_blob, 'text', 'FNDATTCH'

);

COMMIT;

DBMS_LOB.fileclose (file_s);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line ('An exception occurred');

DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);

END;

X_last_updated_by	User Id from FND_USER
X_created_by	User Id from FND_USER
X_last_updated_by	User Id from FND_USER
X_last_update_login	User Id from FND_USER
X_seq_num	Any Number
X_pk1_value	PO_HEADER_ID of Target PO in PO_HEADERS_ALL
X_entity_name	From the Setup
X_datatype_id	Data type id for File Data Type in FND_DOCUMENT_DATATYPES
X_category_id	39 is Category Id for Internal for Purchase Orders in FND_DOCUMENT_CATEGORIES_L
X_usage_type	Look up code from Lookup ATCHMT_DOCUMENT_TYPE
program_name	Oracle Reads this Program Name to Attach the Document