Document Of Records Upload

Use the Below Prgram for Document Upload.

The primary Key value(l_pk1_value)is the vaule attached to the title of the Document for each Employee.

DECLARE

l_rowid ROWID;

l_attached_document_id NUMBER;

l_document_id NUMBER;

l_media_id NUMBER;

l_category_id NUMBER;

l_pk1_value number:=40582;---apps.fnd_attached_documents.pk1_value%TYPE;--:=;

l_description VARCHAR2(240):='Test Attachment';

l_filename VARCHAR2(240) ;--:= 'Sankara.pdf';

l_seq_num NUMBER;

l_blob_data BLOB;

l_blob BLOB;

l_bfile BFILE;

l_byte NUMBER;

l_fnd_user_id NUMBER;

l_short_datatype_id NUMBER;

x_blob BLOB;

fils BFILE;

blob_length INTEGER;

l_entity_name VARCHAR2(100) := 'R_DOCUMENT_EXTRA_INFO';

l_category_name VARCHAR2(100) := 'Documents of Record';

BEGIN

--fnd_global.apps_initialize (4659, 50773,800);

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 =40582 AND entity_name = l_entity_name;

-- Select User_id

SELECT user_id

INTO l_fnd_user_id

FROM apps.fnd_user

WHERE employee_id=81399;--83269;--user_name = ;

-- 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 = 'FILE';

-- Select Category id for Attachments

SELECT category_id

INTO l_category_id

FROM apps.fnd_document_categories_TL

WHERE USER_NAME ='Documents of Record';-- l_category_name;

-- 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_long_text_s.NEXTVAL

INTO l_document_id,

l_attached_document_id

--l_media_id

FROM DUAL;

SELECT MAX (file_id) + 1

INTO l_media_id

FROM fnd_lobs;

fils := BFILENAME ( 'XXHR_FND_BLOBS','Sankara Rao Dasari- Employment Visa.pdf');

-- Obtain the size of the blob file

DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);

blob_length := DBMS_LOB.getlength (fils);

DBMS_LOB.fileclose (fils);

-- Insert a new record into the table containing the

-- filename you have specified and a LOB LOCATOR.

-- Return the LOB LOCATOR and assign it to x_blob.

/*INSERT INTO fnd_lobs

(file_id, file_name, file_content_type, upload_date,

expiration_date, program_name, program_tag, file_data,

LANGUAGE, oracle_charset, file_format

)

VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf

SYSDATE,

NULL, 'FNDATTCH', NULL, EMPTY_BLOB (), --l_blob_data,

'US', 'UTF8', 'binary'

)

RETURNING file_data

INTO x_blob;*/

INSERT INTO fnd_lobs

(file_id, file_name, file_content_type, upload_date,

expiration_date, program_name, program_tag, file_data,

LANGUAGE, oracle_charset, file_format

)

VALUES (l_media_id, l_filename, 'application/pdf',--'text/plain',--application/pdf

SYSDATE,

NULL, 'FNDATTCH', NULL,EMPTY_BLOB (), --l_blob_data,

NULL, 'UTF8', 'binary'

)

RETURNING file_data

INTO x_blob;

-- Load the file into the database as a BLOB

DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);

DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);

DBMS_LOB.loadfromfile (x_blob, fils, blob_length);

-- Close handles to blob and file

DBMS_LOB.CLOSE (x_blob);

DBMS_LOB.CLOSE (fils);

DBMS_OUTPUT.put_line ('FND_LOBS File Id Created is ' || l_media_id);

COMMIT;

-- This package allows user to share file across multiple orgs or restrict to single org

fnd_documents_pkg.insert_row

(x_rowid => l_rowid,

x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,-- fnd_profile.value('USER_ID')

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_datatype_id => l_short_datatype_id, -- FILE

X_security_id =>NULL,

x_publish_flag => 'N', --This flag allow the file to share across multiple organization

x_category_id => l_category_id,

x_security_type => 1,

x_usage_type => 'S',

x_language => 'US',

x_description => l_filename,--l_description,

x_file_name => l_filename,

x_media_id => l_media_id

);

commit;

fnd_documents_pkg.insert_tl_row

(x_document_id => l_document_id,

x_creation_date => SYSDATE,

x_created_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_language => 'US',

x_description => l_filename--l_description

);

COMMIT;

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 => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_date => SYSDATE,

x_last_updated_by => l_fnd_user_id,--fnd_profile.VALUE('USER_ID'),

x_last_update_login => fnd_profile.VALUE('LOGIN_ID'),

x_seq_num => l_seq_num,

x_entity_name => l_entity_name,

x_column1 => NULL,

x_pk1_value => l_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 => 6,

x_category_id => l_category_id,

x_security_type => 1,

X_security_id =>NULL,

x_publish_flag => 'Y',

x_language => 'US',

x_description => l_filename,--l_description,

x_file_name => l_filename,

x_media_id => l_media_id

);

COMMIT;

DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);

dbms_output.put_line ('attachment_document id is l_attached_document_id '||l_attached_document_id);

dbms_output.put_line ('document id is l_document_id '||l_document_id);

dbms_output.put_line (' l_media_id '||l_media_id);

EXCEPTION

WHEN OTHERS

THEN

dbms_output.put_line('Failed '||sqlerrm);

commit;

end;

,

Image Upload -Employee

Image Upload:

Step 1:

Create a Directory To Download the Files SYS User:

CREATE OR REPLACE DIRECTORY FND_BLOBS AS '/usr/tmp/FND_LOBS[GMM1] '

Grant Access to Apps User

GRANT READ, WRITE ON DIRECTORY FND_BLOBS TO APPS;

Check the Privilege with below command,

SELECT * FROM dba_tab_privs WHERE table_name= ‘FND_BLOBS’;

Run the below Query in Apps Schema[GMM2] ,

CREATE OR REPLACE PROCEDURE XX_IMAGE_UPLOAD

AS

   CURSOR CUR_PER

   IS

      SELECT * FROM PER_ALL_PEOPLE_F  where  employee_number = 200290

      and trunc(sysdate) between effective_start_date and effective_end_date

      and business_group_id =1274;

   V_IMAGE_NAME    VARCHAR2 (240);

   V_DSTN_FILE     BLOB;

   V_SRC_FILE      BFILE;

   V_FILE_EXISTS   INTEGER := 0;

   V_AMT           INTEGER ;

BEGIN

   FOR I IN CUR_PER

   LOOP

      BEGIN

         V_IMAGE_NAME := '200290'||’.jpg’;--Image Name

         V_SRC_FILE := BFILENAME (‘XXHR_FND_BLOBS’, V_IMAGE_NAME);--File Loaction and Name

         V_FILE_EXISTS := DBMS_LOB.FILEEXISTS (V_SRC_FILE);

        

          V_AMT := DBMS_LOB.GETLENGTH(V_SRC_FILE) ;

         

         IF V_FILE_EXISTS = 1

         THEN

            DBMS_LOB.CREATETEMPORARY (V_DSTN_FILE, TRUE, DBMS_LOB.SESSION);

           

            DBMS_LOB.FILEOPEN (V_SRC_FILE, DBMS_LOB.FILE_READONLY);

            DBMS_LOB.LOADFROMFILE (V_DSTN_FILE,

                                   V_SRC_FILE,

                                    V_AMT,1,1 );

            COMMIT;

            DBMS_LOB.FILECLOSE (V_SRC_FILE);

            INSERT INTO APPS.PER_IMAGES (IMAGE_ID,

                                         PARENT_ID,

                                         TABLE_NAME,

                                         IMAGE)

                 VALUES (PER_IMAGES_S.NEXTVAL,

                         I.PERSON_ID,

                         'PER_PEOPLE_F',

                         V_DSTN_FILE);

                          DBMS_OUTPUT.put_line (

               'Uploaded the  image for the employee code : '

               || I.employee_number);

         END IF;

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

               'Unable to upload image for the employee code : '

               || I.employee_number||sqlerrm);

      END;

   END LOOP;

   COMMIT;

END;

/

declare

begin

XX_IMAGE_UPLOAD;

end;

SET SERVEROUTPUT ON

---

 [GMM1]Directory Path in Apps/DB Tier

 [GMM2]

Document Of Records Download IN Oralce HRMS

Document Download:

Step 1:

Create a Directory To Download the Files SYS User:

CREATE OR REPLACE DIRECTORY FND_BLOBS AS '/usr/tmp/FND_LOBS'

(Directory Path in Apps/DB Tier)

Grant Access to Apps User

GRANT READ, WRITE ON DIRECTORY FND_BLOBS TO APPS;

Check the Privilege with below command,

SELECT * FROM dba_tab_privs WHERE table_name= ‘FND_BLOBS’;

Run the below Query in Apps Schema[GMM2] ,

DECLARE

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blob_len INTEGER;

l_file_name varchar2(100);

BEGIN

-- Get LOB locator

FOR rec IN (SELECT L.file_data

  INTO   l_blob

  FROM fnd_attached_documents ad,  fnd_documents d, fnd_lobs l

 WHERE ad.pk1_value =41402(Unique Key Refers to the document for each Employee)

  and  d.document_id = ad.document_id

   AND ad.ENTITY_NAME LIKE 'R_DOCUMENT_EXTRA_INFO'(Entity Name Is the Document Reference Name)

 AND l.file_id = d.media_id)

 LOOP

l_blob_len := DBMS_LOB.getlength(rec.l_blob);

-- Open the destination file.

l_file := UTL_FILE.fopen('FND_BLOBS',rec.L.file_data,'w', 32767);

-- Read chunks of the BLOB and write them to the file

-- until complete.

WHILE l_pos < l_blob_len LOOP

DBMS_LOB.read(rec.l_blob, l_amount, l_pos, l_buffer);

UTL_FILE.put_raw(l_file, l_buffer, TRUE);

UTL_FILE.fflush (l_file);

l_pos := l_pos + l_amount;

END LOOP;

-- Close the file.

UTL_FILE.fclose(l_file);

END LOOP;

EXCEPTION

WHEN OTHERS THEN

-- Close the file if something goes wrong.

IF UTL_FILE.is_open(l_file) THEN

UTL_FILE.fclose(l_file);

END IF;

RAISE;

END;

Output directory path in Apps

/u01/(instance_name)/inst/apps/xyz/logs/appl/conc/out/FND_BLOBS

Commit and Check the Path File will Be downloaded in the specified Location.

DECLARE

l_file UTL_FILE.FILE_TYPE;

l_buffer RAW(32767);

l_amount BINARY_INTEGER := 32767;

l_pos INTEGER := 1;

l_blob BLOB;

l_blob_len INTEGER;

l_file_name varchar2(100);

BEGIN

-- Get LOB locator

FOR rec IN (SELECT L.file_data

  INTO   l_blob

  FROM fnd_attached_documents ad,  fnd_documents d, fnd_lobs l

 WHERE ad.pk1_value =41402

  and  d.document_id = ad.document_id

   AND ad.ENTITY_NAME LIKE 'R_DOCUMENT_EXTRA_INFO'

 AND l.file_id = d.media_id)

 LOOP

l_blob_len := DBMS_LOB.getlength(rec.l_blob);

-- Open the destination file.

l_file := UTL_FILE.fopen('/u01/instance name/inst/apps/xyz/logs/appl/conc/out/FCP_BLOBS',rec.L.file_data,'w', 32767);

-- Read chunks of the BLOB and write them to the file

-- until complete.

WHILE l_pos < l_blob_len LOOP

DBMS_LOB.read(rec.l_blob, l_amount, l_pos, l_buffer);

UTL_FILE.put_raw(l_file, l_buffer, TRUE);

UTL_FILE.fflush (l_file);

l_pos := l_pos + l_amount;

END LOOP;

-- Close the file.

UTL_FILE.fclose(l_file);

END LOOP;

EXCEPTION

WHEN OTHERS THEN

-- Close the file if something goes wrong.

IF UTL_FILE.is_open(l_file) THEN

UTL_FILE.fclose(l_file);

END IF;

RAISE;

END;

---

Commit And Check the Directory