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

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

Custom File Attached – object.

 


1.  

  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

 

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