Oracle EBS (ERP): FORM

-- How to custom file/document to Upload/Attached/View/Delete in Oracle forms.

Custom File Attached – object.

Button : Upload File.

Trigger :- WHEN-BUTTON-PRESSED

IF ( :XXJG_document_upload_v.file_name_dispaly IS NULL

AND :XXJG_document_upload_v.document_type IS NOT NULL

) THEN

file_attached;

END IF;

PROCEDURE file_attached IS

depfld BOOLEAN;

exit_loop BOOLEAN := false;

a_value_chosen BOOLEAN := true;

button_choice NUMBER;

l_datatype_id NUMBER;

l_attached_document_id VARCHAR2(32);

l_file_id VARCHAR2(256);

l_url VARCHAR2(255);

l_file_upload_parameters VARCHAR2(1024);

access_id NUMBER;

l_server_url VARCHAR2(255);

l_search_document_url VARCHAR2(2000);

l_gfm_id NUMBER := NULL;

l_separator NUMBER;

l_username VARCHAR2(80);

l_web_agent VARCHAR2(240);

l_file_parameters VARCHAR2(1024);

l_object_name VARCHAR2(255);

l_language VARCHAR2(80);

l_parameters VARCHAR2(255);

l_language VARCHAR2(4);

l_file_name VARCHAR2(255);

lv_seq_num NUMBER;

l_user_id NUMBER := nvl(fnd_profile.value('USER_ID'), 1330);

l_resp_appl_id NUMBER := nvl(fnd_profile.value('RESP_APPL_ID'), 5000);

l_resp_id NUMBER := nvl(fnd_profile.value('RESP_ID'), 50820);

BEGIN

access_id := fnd_gfm.authorize(NULL);

fnd_profile.get('APPS_WEB_AGENT', l_server_url);

l_parameters := 'access_id='

|| access_id

|| '&l_server_url='

|| l_server_url;

fnd_function.execute(function_name => 'FND_FNDFLUPL', open_flag => 'Y', session_flag => 'Y', other_params => l_parameters);

fnd_message.set_name('FND', 'ATCHMT-FILE-UPLOAD-COMPLETE');

button_choice := fnd_message.question(button1 => 'YES', button2 => NULL, button3 => 'NO', default_btn => 1, cancel_btn => 3,

icon => 'question');

IF ( button_choice = 1 ) THEN

l_file_id := '';

copy(l_file_id, 'XXJG_DOCUMENT_UPLOAD_V.file_name');

l_gfm_id := fnd_gfm.get_file_id(access_id);

IF l_gfm_id IS NOT NULL THEN

---Copy(TO_CHAR(l_gfm_id), 'document_header.media_id');

BEGIN

SELECT

decode(instr(file_name, '/'), 0, file_name, substr(file_name, instr(file_name, '/') + 1))

INTO l_file_id

FROM

fnd_lobs

WHERE

file_id = l_gfm_id;

EXCEPTION

WHEN OTHERS THEN

l_file_id := NULL;

END;

IF l_file_id IS NOT NULL THEN

copy(l_file_id, 'XXJG_DOCUMENT_UPLOAD_V.file_name');

:XXJG_document_upload_v.media_id := l_gfm_id;

END IF;

ELSE

NULL;

END IF;

BEGIN

create_documents.insert_row;

EXCEPTION

WHEN OTHERS THEN

fnd_message.set_string(' Exception Details :' || sqlerrm);

fnd_message.show;

END;

go_block('XXJG_DOCUMENT_UPLOAD_V');

clear_block(no_validate);

execute_query;

END IF;

END;

package body create_documents IS

PROCEDURE insert_row IS

l_language varchar2(4);

l_file_name varchar2(255);

lv_seq_num NUMBER;

lv_attached_document_id NUMBER;

l_user_id NUMBER := NVL (fnd_profile.VALUE ('USER_ID'), 1330);

l_resp_appl_id NUMBER := NVL (fnd_profile.VALUE ('RESP_APPL_ID'), 5000);

l_resp_id NUMBER := NVL (fnd_profile.VALUE ('RESP_ID'), 50820);

BEGIN

SELECT NVL(max(seq_num),0) + 10

INTO lv_seq_num

FROM fnd_attached_documents

WHERE entity_name = 'XXJG_BILL'

AND pk1_value = '10';

EXCEPTION WHEN OTHERS THEN

lv_seq_num :=0;

END;

-- Set who columns

FND_STANDARD.SET_WHO;

-- Set the langauge variable

select USERENV('LANG')

into l_language

from dual;

SELECT FND_ATTACHED_DOCUMENTS_S.NEXTVAL

INTO lv_attached_document_id

FROM DUAL;

fnd_attached_documents_pkg.insert_row(

X_rowid => :XXJG_DOCUMENT_UPLOAD_V.row_id,

X_attached_document_id => lv_attached_document_id, --, :document_header.attached_document_id,

X_document_id => :XXJG_DOCUMENT_UPLOAD_V.DOCUMENT_ID, -- :document_header.document_id,

X_creation_date => sysdate, --:document_header.creation_date,

X_created_by => l_user_id, --:document_header.created_by,

X_last_update_date => sysdate , --:document_header.last_update_date,

X_last_updated_by => l_user_id , --:document_header.last_updated_by,

X_last_update_login =>l_resp_id ,-- :document_header.last_update_login,

X_seq_num => lv_seq_num , --:document_header.seq_num,

X_entity_name => 'XXJG_BILL', --:document_header.entity_name,

X_column1 =>null, -- :parameter.column1,

X_pk1_value => :XXJG_DOCUMENT_UPLOAD_V.UPLOAD_VALUE, --:parameter.pkey1,

X_pk2_value => null ,--:parameter.pkey2,

X_pk3_value =>null ,-- :parameter.pkey3,

X_pk4_value =>null ,-- :parameter.pkey4,

X_pk5_value =>null ,-- :parameter.pkey5,

X_automatically_added_flag => 'N', -- :document_header.automatically_added_flag,

X_request_id => null,

X_program_application_id => null,

X_program_id => null,

X_program_update_date => null,

X_attribute_category => null ,--:document_header.attribute_category,

X_attribute1 => null ,--:document_header.attribute1,

X_attribute2 => null ,--:document_header.attribute2,

X_attribute3 => null ,--:document_header.attribute3,

X_attribute4 => null ,--:document_header.attribute4,

X_attribute5 => null ,--:document_header.attribute5,

X_attribute6 => null ,-- :document_header.attribute6,

X_attribute7 => null ,--:document_header.attribute7,

X_attribute8 => null ,--:document_header.attribute8,

X_attribute9 => null ,--:document_header.attribute9,

X_attribute10 => null ,--:document_header.attribute10,

X_attribute11 => null ,--:document_header.attribute11,

X_attribute12 => null ,--document_header.attribute12,

X_attribute13 => null ,--:document_header.attribute13,

X_attribute14 => null ,--:document_header.attribute14,

X_attribute15 => null ,--:document_header.attribute15,

X_datatype_id => 6 ,--:document_header.datatype_id, --File

X_category_id => 1000809 , --:document_header.category_id, --XXJG_DOCUMENT_BILL_UPLOAD

X_security_type => 1 , --:document_header.security_type,

X_security_id => null , --:document_header.security_id,

X_publish_flag => 'Y' ,--:document_header.publish_flag,

X_image_type => null ,--:document_header.image_type,

X_storage_type => null,

X_usage_type => 'O' ,--:document_header.usage_type,

X_language => 'GB' , --l_language,

X_description => null ,--:document_header.document_description,

X_file_name => :XXJG_DOCUMENT_UPLOAD_V.FILE_NAME,

X_media_id => :XXJG_DOCUMENT_UPLOAD_V.media_id,

X_doc_attribute_category => null ,--:document_header.doc_attribute_category,

X_doc_attribute1 => null ,--:document_header.doc_attribute1,

X_doc_attribute2 => null ,--:document_header.doc_attribute2,

X_doc_attribute3 => null ,--:document_header.doc_attribute3,

X_doc_attribute4 => null ,--:document_header.doc_attribute4,

X_doc_attribute5 => null ,--:document_header.doc_attribute5,

X_doc_attribute6 => null ,--:document_header.doc_attribute6,

X_doc_attribute7 => null ,--:document_header.doc_attribute7,

X_doc_attribute8 => null ,--:document_header.doc_attribute8,

X_doc_attribute9 => null ,--:document_header.doc_attribute9,

X_doc_attribute10 => null ,--:document_header.doc_attribute10,

X_doc_attribute11 => null ,--:document_header.doc_attribute11,

X_doc_attribute12 => null ,--:document_header.doc_attribute12,

X_doc_attribute13 => null ,--:document_header.doc_attribute13,

X_doc_attribute14 => null ,--:document_header.doc_attribute14,

X_doc_attribute15 => null ,--:document_header.doc_attribute15,

X_url => null ,--:document_header.url,

X_title => null ,--:document_header.title,

X_orig_doc_id => null ,--:document_header.orig_doc_id,

X_orig_attach_doc_id =>null -- :document_header.orig_attach_doc_id

);

COMMIT;

END insert_row;

PROCEDURE update_row IS

l_language varchar2(4);

l_file_name varchar2(255);

BEGIN

NULL;

IF (:document_header.usage_type = 'T') THEN

-- user attempting to update a Template, which

-- means a new document must be created

DOCUMENT_TABLE_HANDLER3.insert_document('DOCUMENT_HEADER');

DOCUMENT_TABLE_HANDLER3.update_fnd_attached_docs('DOCUMENT_HEADER');

RETURN;

END IF;

FND_STANDARD.SET_WHO;

-- Set the language

select USERENV('LANG')

into l_language

from dual;

-- Document Management Integration changes

/* IF ( :document_header.datatype_id in (3,5,6) ) THEN

:document_header.file_name := :document_header.file_name_display ;

END IF; -- doing this in FNDATTCH.pll WHEN-VALIDATE-ITEM code instead */

-- call stored procedure to handle update. It may

-- require updating 3 tables (fnd_attached_documents,fnd_documents, and

-- fnd_documents_tl)

/* fnd_attached_documents_pkg.Update_Row(

X_Rowid => :document_header.row_id,

X_attached_document_id => :document_header.attached_document_id,

X_document_id => :document_header.document_id,

X_last_update_date => :document_header.last_update_date,

X_last_updated_by => :document_header.last_updated_by,

X_last_update_login => :document_header.last_update_login,

X_seq_num => :document_header.seq_num,

X_entity_name => :document_header.entity_name,

X_column1 => :parameter.column1,

X_pk1_value => :document_header.pk1_value,

X_pk2_value => :document_header.pk2_value,

X_pk3_value => :document_header.pk3_value,

X_pk4_value => :document_header.pk4_value,

X_pk5_value => :document_header.pk5_value,

X_automatically_added_flag

=> :document_header.automatically_added_flag,

X_request_id => :document_header.request_id,

X_program_application_id

=> :document_header.program_application_id,

X_program_id => :document_header.program_id,

X_program_update_date => :document_header.program_update_date,

X_Attribute_Category => :document_header.attribute_category,

X_Attribute1 => :document_header.attribute1,

X_Attribute2 => :document_header.attribute2,

X_Attribute3 => :document_header.attribute3,

X_Attribute4 => :document_header.attribute4,

X_Attribute5 => :document_header.attribute5,

X_Attribute6 => :document_header.attribute6,

X_Attribute7 => :document_header.attribute7,

X_Attribute8 => :document_header.attribute8,

X_Attribute9 => :document_header.attribute9,

X_Attribute10 => :document_header.attribute10,

X_Attribute11 => :document_header.attribute11,

X_Attribute12 => :document_header.attribute12,

X_Attribute13 => :document_header.attribute13,

X_Attribute14 => :document_header.attribute14,

X_Attribute15 => :document_header.attribute15,

X_datatype_id => :document_header.datatype_id,

X_category_id => :document_header.category_id,

X_security_type => :document_header.security_type,

X_security_id => :document_header.security_id,

X_publish_flag => :document_header.publish_flag,

X_image_type => :document_header.image_type,

X_storage_type => null,

X_usage_type => :document_header.usage_type,

X_start_date_active => :document_header.start_date_Active,

X_end_date_active => :document_header.end_date_active,

X_language => l_language,

X_description => :document_header.document_description,

X_file_name => :document_header.file_name,

X_media_id => :document_header.media_id,

X_doc_attribute_category => :document_header.doc_attribute_category,

X_doc_attribute1 => :document_header.doc_attribute1,

X_doc_attribute2 => :document_header.doc_attribute2,

X_doc_attribute3 => :document_header.doc_attribute3,

X_doc_attribute4 => :document_header.doc_attribute4,

X_doc_attribute5 => :document_header.doc_attribute5,

X_doc_attribute6 => :document_header.doc_attribute6,

X_doc_attribute7 => :document_header.doc_attribute7,

X_doc_attribute8 => :document_header.doc_attribute8,

X_doc_attribute9 => :document_header.doc_attribute9,

X_doc_attribute10 => :document_header.doc_attribute10,

X_doc_attribute11 => :document_header.doc_attribute11,

X_doc_attribute12 => :document_header.doc_attribute12,

X_doc_attribute13 => :document_header.doc_attribute13,

X_doc_attribute14 => :document_header.doc_attribute14,

X_doc_attribute15 => :document_header.doc_attribute15,

X_url => :document_header.url,

X_title => :document_header.title);

END update_row;

END create_documents;

2. Button : View.

Trigger :- WHEN-BUTTON-PRESSED

IF :XXJG_DOCUMENT_UPLOAD_V.FILE_NAME_DISPALY IS NOT NULL THEN

OPEN_DOCUMENT;

END IF;

PROCEDURE open_document IS

gfm_agent VARCHAR2(255);

l_url VARCHAR2(2000);

l_gfm_id NUMBER;

BEGIN

IF (

:XXJG_document_upload_v.file_name_dispaly IS NOT NULL

AND :XXJG_document_upload_v.FILE_ID IS NOT NULL

) THEN

gfm_agent := fnd_web_config.gfm_agent;

l_gfm_id := :XXJG_DOCUMENT_UPLOAD_V.FILE_ID;

l_url := fnd_gfm.construct_download_url(gfm_agent, l_gfm_id, false);

fnd_utilities.open_url(l_url);

RETURN;

END IF; -- end if filename not null.

END;

3. Button : Delete.

Trigger :- WHEN-BUTTON-PRESSED

IF :XXJG_DOCUMENT_UPLOAD_V.ATTACHED_DOCUMENT_ID IS NOT NULL THEN

DELETE_DOCUMENT;

END IF;

PROCEDURE delete_document IS

button_choice NUMBER;

BEGIN

fnd_message.set_name('FND', 'ATCHMT-DELETE-DOCUMENT');

button_choice := fnd_message.question(button1 => 'YES',

button2 => NULL,

button3 => 'NO',

default_btn => 1,

cancel_btn => 3,

icon => 'question'

);

-- check if document needs to be deleted

IF ( button_choice = 1 AND :XXJG_DOCUMENT_UPLOAD_V.ATTACHED_DOCUMENT_ID IS NOT NULL ) THEN

BEGIN

fnd_attached_documents3_pkg.delete_row(:XXJG_DOCUMENT_UPLOAD_V.ATTACHED_DOCUMENT_ID,

6, --file

'Y');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

fnd_message.set_string(' Exception Details :' || sqlerrm);

fnd_message.show;

END;

go_block('XXJG_DOCUMENT_UPLOAD_V');

clear_block(no_validate);

execute_query;

END IF;

END delete_document;

--BM

Oracle R12 How to custom file/document to Upload/Attached/View/Delete in Oracle forms.