Sample code to attach file at Invoice level in Oracle APPS

DECLARE l_rowid rowid; l_attached_document_id NUMBER; l_document_id NUMBER; l_media_id NUMBER; l_description fnd_documents_tl.description%TYPE; l_seq_num NUMBER; l_datatype_id NUMBER; CURSOR c_invoice_attachment IS SELECT fad_l.seq_num ,fl_l.file_id media_id ,fl_l.file_data media_data_blob ,fad_l.category_id ,fl_l.file_id ,fl_l.file_name ,fl_l.file_content_type ,fl_l.file_data ,fl_l.file_format ,fdd.datatype_id ,fdd.name document_type ,fdd.user_name ,aia.invoice_id pk1_value ,fdt_l.description FROM fnd_attached_documents fad_l ,fnd_documents fd_l ,fnd_documents_tl fdt_l ,fnd_document_categories_tl fdct_l ,fnd_lobs fl_l ,fnd_document_datatypes fdd ,xx_hdr_tab gen ,ap_invoices_all aia WHERE 1 = 1 AND fad_l.document_id = fd_l.document_id AND fad_l.document_id = fdt_l.document_id AND fdct_l.category_id = fd_l.category_id AND fd_l.media_id = fl_l.file_id AND fad_l.entity_name = 'XX_HDR_TAB' AND fdt_l.language = USERENV('LANG') AND fdct_l.language = USERENV('LANG') AND fd_l.datatype_id = fdd.datatype_id AND fdd.language = USERENV('LANG') AND fad_l.pk1_value = '90' AND fad_l.pk1_value = gen.xx_hdr_id AND gen.invoice_num = aia.invoice_num ; BEGIN FOR i IN c_invoice_attachment LOOP insert into xx_test1 values(xx_test1_s.nextval, 'i.document_type: '||i.document_type); IF i.document_type = 'WEB_PAGE' THEN l_media_id := null; ELSE SELECT fnd_lobs_s.nextval INTO l_media_id FROM dual; END IF; insert into xx_test1 values(xx_test1_s.nextval, 'l_media_id: '||l_media_id); If i.document_type IN ('WEB_PAGE', 'FILE') THEN SELECT fnd_documents_s.NEXTVAL INTO l_document_id FROM dual; SELECT fnd_attached_documents_s.NEXTVAL INTO l_attached_document_id FROM dual; SELECT nvl(max(seq_num),0) + 10 INTO l_seq_num FROM fnd_attached_documents WHERE pk1_value = i.pk1_value --UNIQUE ID AND entity_name = 'AP_INVOICES'; insert into xx_test1 values(xx_test1_s.nextval, 'l_seq_num: '||l_seq_num); fnd_documents_pkg.insert_row (X_ROWID => l_rowid ,X_DOCUMENT_ID => l_document_id ,X_CREATION_DATE => SYSDATE ,X_CREATED_BY => fnd_global.user_id ,X_LAST_UPDATE_DATE => SYSDATE ,X_LAST_UPDATED_BY => fnd_global.user_id ,X_LAST_UPDATE_LOGIN => 0 ,X_DATATYPE_ID => i.datatype_id --5 -- Web Page ,X_CATEGORY_ID => 291--l_category_id -- Invoice Internal ,X_SECURITY_TYPE => 2 ,X_PUBLISH_FLAG => 'Y' ,X_USAGE_TYPE => 'O' ,X_LANGUAGE => 'US' ,X_DESCRIPTION => i.description ,X_FILE_NAME => i.file_name ,X_MEDIA_ID => l_media_id -- R12 ,X_URL => i.user_name ); fnd_documents_pkg.insert_tl_row (X_DOCUMENT_ID => l_document_id ,X_CREATION_DATE => sysdate ,X_CREATED_BY => fnd_global.user_id ,X_LAST_UPDATE_DATE => sysdate ,X_LAST_UPDATED_BY => fnd_global.user_id ,X_LAST_UPDATE_LOGIN => fnd_global.login_id ,X_LANGUAGE => 'US' ,X_DESCRIPTION => i.description ,X_TITLE => i.file_name ); 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 => fnd_global.user_id ,X_LAST_UPDATE_DATE => sysdate ,X_LAST_UPDATED_BY => fnd_global.user_id ,X_LAST_UPDATE_LOGIN => fnd_global.login_id ,X_SEQ_NUM => l_seq_num ,X_ENTITY_NAME => 'AP_INVOICES' ,X_COLUMN1 => null ,X_PK1_VALUE => i.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 => i.datatype_id ,X_CATEGORY_ID => 291--l_category_id -- Invoice Internal ,X_SECURITY_TYPE => 2 ,X_PUBLISH_FLAG => 'Y' ,X_LANGUAGE => 'US' ,X_DESCRIPTION => i.description ,X_FILE_NAME => i.file_name ,X_MEDIA_ID => l_media_id -- R12 ,X_URL => i.user_name ,X_TITLE => i.description ); IF l_media_id IS NOT NULL THEN INSERT INTO FND_LOBS (file_id ,file_name ,file_content_type ,file_data ,upload_date ,expiration_date ,program_name ,program_tag ,language ,oracle_charset ,file_format ) VALUES (l_media_id ,i.file_name ,i.file_content_type ,i.file_data ,sysdate ,null ,null ,null ,'US' ,'UTF8' ,'binary' ); END IF; commit; END IF; END LOOP; END;