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

The program can't start becuase MSVCR71.dll is missing from your computer Oracle Discoverer Desktop

After successful completion of Discoverer tools., we may not able to open the Admin and Desktop version due to the below error

Solution is: i have download the MSCVP71.dll from

http://www.dlldump.com/download-dll-files_new.php/dllfiles/M/MSVCR71.dll/7.10.3052.48/download.html

and pasted the same in my discoverer bin location., at times if we paste in C:\Windows\System32 doesn't work out.

So tried coping in Discoverer's BIN folder and that worked out.. !!!!

Delete Element and its link to Assignment

declare
CURSOR   rec_check is
select  peef.ELEMENT_ENTRY_ID,peef.EFFECTIVE_START_DATE,peef.OBJECT_VERSION_NUMBER,paaf.ASSIGNMENT_NUMBER
 from
pay_element_entries_f peef,
pay_element_types_f petf,
pay_element_links_f pelf,
per_all_assignments_f paaf
where petf.ELEMENT_TYPE_ID=pelf.ELEMENT_TYPE_ID
and peef.ELEMENT_LINK_ID=pelf.ELEMENT_LINK_ID
and peef.ELEMENT_TYPE_ID=petf.ELEMENT_TYPE_ID
and peef.ASSIGNMENT_ID=paaf.ASSIGNMENT_ID
and petf.ELEMENT_NAMe='Final Processing'
and  sysdate between paaf.effective_start_date and paaf.effective_end_date
and  sysdate between petf.effective_start_date and petf.effective_end_date
and  sysdate between peef.effective_start_date and peef.effective_end_date
and  sysdate between pelf.effective_start_date and pelf.effective_end_date;

v_object_version_number number:=1;
v_start_date date:=null;
v_end_date date:=null;
v_delete_wrg boolean ;
v_date date:=null;
begin
FOR cur in rec_check
loop
dbms_output.put_line(1);
begin
pay_element_entry_api.delete_element_entry
(p_validate=>false
,p_datetrack_delete_mode =>'ZAP'
,p_effective_date =>cur.EFFECTIVE_START_DATE---TO_DATE('22-06-2008','DD-MM-YYYY')--v_date
,p_element_entry_id =>cur.ELEMENT_ENTRY_ID
,p_object_version_number =>v_object_version_number
,p_effective_start_date =>v_start_date
,p_effective_end_date =>v_end_date
,p_delete_warning =>v_delete_wrg
);
dbms_output.put_line('Done');
EXCEPTION
when others then
dbms_output.put_line(sqlcode||sqlerrm||'Assignment'||cur.ASSIGNMENT_NUMBER);
end;
end loop;

end;


commit;