How to create Attachment link through OAF Personalization
OAF Page for
Customization = /oracle/apps/per/selfservice/extrainfotype/webui/EitPG
1) Define Entity
a) Go to Applciation Developer
a) Enter Entity Data
Table := XX_PER_ARREAR_UPLOAD_STG
Entity Id := PERSON_ID
Entity Name := PERSON_ID
Prompt := PERSON_ID
Application := Human Resources
1) 2) OAF Personalization to create Attachment Link
a) a) Add Attachment Link Attribute
c) Entity Primary Key mapping with VO Attribute
d) Fields added as shown below
------------------------------------------------------------------------------------------------------------------
Below Steps need to perform at Database
1)
CREATE TABLE XX_PER_ARREAR_UPLOAD_STG
(PERSON_ID NUMBER,
MEDIA_ID VARCHAR2(100),
FILE_NAME VARCHAR2(240),
message varcha2(2000)
);
6) 2) Create Procedures :- XX_PER_ARREAR_FILE_UPLOAD
3) 3) Check correct oracle directory and define in procedure
8) 4) Run the Scripts to upload file
dPProcedure :- XX_PER_ARREAR_FILE_UPLOAD
CREATE OR REPLACE PROCEDURE XX_PER_ARREAR_FILE_UPLOAD
(P_EMP_NO VARCHAR2)
IS
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_category_id NUMBER;
l_pk1_value fnd_attached_documents.pk1_value%TYPE ;
----<Primary Key information that uniquely identifies the product (such as the product_ID)>;
l_description fnd_documents_tl.description%TYPE := 'Arrear Attachment';
l_filename VARCHAR2 (240) := p_emp_no||'.xls';
--l_file_path VARCHAR2 (240) := 'test';
---'SALE_INVOICE_PATH'; --Server Directory Path for upload files
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) := 'PERSON_ID';
l_oracle_directory varchar2(100):= 'ECX_UTL_LOG_DIR_OBJ';
l_content_type varchar2(100):= 'application/vnd.ms-excel';
--application/vnd.ms-excel--application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
--- <entity_name>;
l_category_name VARCHAR2 (100) := 'Miscellaneous';--XX_ARREAR_FILE_UPLOAD';
--<category_name>;
l_message varchar2(2000);
BEGIN
bEGIN
select DISTINCT PERSON_ID INTO l_pk1_value from per_all_people_f
where employee_number =P_EMP_NO;
END;
insert into XX_PER_ARREAR_UPLOAD_STG (person_id) values(l_pk1_value);
--fnd_global.apps_initialize (<userid>, <applid>,<appluserid>);
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 = l_pk1_value AND entity_name = l_entity_name;
-- Select User_id
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_name = 'TECHHARDS'; -- <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
--FATEMP
SELECT category_id
INTO l_category_id
FROM apps.fnd_document_categories_vl
WHERE user_name = 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 (l_oracle_directory, l_filename);
--dbms_output.put_line('fils:'||fils);
-- 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, /*'text/plain',*/ l_content_type,
--'application/pdf',--
SYSDATE,
NULL, 'FNDATTCH', NULL, EMPTY_BLOB ()
,'US', /*'UTF8'*/
/* 'AR8MSWIN1256' */ 'WE8MSWIN1252',--'AR8ISO8859P6',
'binary'
/*'text'*/
)
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 => -1,
x_datatype_id => l_short_datatype_id, -- FILE
x_security_id => NULL,
--<security ID defined in your Attchments, Usaully SOB ID/ORG_ID..>,
x_publish_flag => /*'N'*/ 'Y',
--This flag allow the file to share across multiple organization
x_category_id => l_category_id,
x_security_type => /*1*/ 4,
x_usage_type => /*'S'*/ 'O',
x_language => 'US',
x_description => l_filename,
x_file_name => l_filename,
x_media_id => l_media_id
);
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 => -1,
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*/ 4,
x_security_id => NULL,
--<security ID defined in your Attchmentsssss, SOB ID/ORG_ID..>,
x_publish_flag => 'Y',
x_language => 'US',
x_description => l_filename, --l_description,
x_file_name => l_filename,
x_media_id => l_media_id
);
update XX_PER_ARREAR_UPLOAD_STG
set media_id = l_media_id,
file_name = l_filename,
message='SUCCESSFUL'
where person_id =l_pk1_value;
COMMIT;
DBMS_OUTPUT.put_line ('MEDIA ID CREATED IS ' || l_media_id);
Exception when others then
l_message := SQLERRM;
update XX_PER_ARREAR_UPLOAD_STG
set media_id = l_media_id,
file_name = l_filename,message = l_message
where person_id =l_pk1_value;
END;
--=========================================================================
# OAF , # OAF Personalization , # Add Attachment Link through Personalization , # Oracle Apps , # EBS
No comments:
Post a Comment