Tuesday 18 September 2012

Oracle Attchments API

CREATE OR REPLACE PROCEDURE XX_ATTACH_LOAD (p_short_text IN Varchar2,
p_cat_id IN Number,
p_pk1_val IN Number,
p_entity_name IN Varchar2,
x_return_status OUT VARCHAR2,
x_return_msg OUT VARCHAR2
) AUTHID CURRENT_USER
/******************************************************************************/
IS
ln_attached_doc_id NUMBER;
lv_language Varchar2(10);
ln_media_id NUMBER;
ln_row_id Varchar2(100);
ln_doc_id NUMBER;
ln_user_id apps.FND_USER.USER_ID%TYPE;
BEGIN
/***************************************************************
To get the attached document seq id
****************************************************************/
SELECT FND_ATTACHED_DOCUMENTS_S.nextval
INTO ln_attached_doc_id
FROM dual;
SELECT USERENV('LANG')
INTO lv_language
FROM dual;
SELECT user_id
INTO ln_user_id
FROM fnd_user
WHERE UPPER (user_name) = 'FKI_INT';--'OMADM-920';
/***************************************************************
To get the Short Text media id
****************************************************************/
SELECT fnd_documents_short_text_s.nextval
INTO ln_media_id
FROM dual;
ln_row_id := NULL;
ln_doc_id := NULL;
/***************************************************************
Thiis procedure call will create the records in
a) fnd_documents_tl
b) fnd_documents
c) fnd_attached_documents
****************************************************************/
apps.fnd_attached_documents_pkg.insert_row(
X_rowid => ln_row_id,
X_attached_document_id => ln_attached_doc_id,
X_document_id => ln_doc_id,
X_creation_date => SYSDATE,
X_created_by => ln_user_id,
X_last_update_date => SYSDATE,
X_last_updated_by => ln_user_id,
X_last_update_login => -1,--ln_user_id,
X_seq_num => 10,
X_entity_name => p_entity_name, --'GMD_SAMPLES',
X_column1 => NULL,
X_pk1_value => p_pk1_val,
X_pk2_value => NULL,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_automatically_added_flag => 'N',
X_request_id => NULL,
X_program_application_id => NULL,
X_program_id => NULL,
X_program_update_date => NULL,
X_attribute_category => NULL,
X_attribute1 => NULL,
X_attribute2 => NULL,
X_attribute3 => NULL,
X_attribute4 => NULL,
X_attribute5 => NULL,
X_attribute6 => NULL,
X_attribute7 => NULL,
X_attribute8 => NULL,
X_attribute9 => NULL,
X_attribute10 => NULL,
X_attribute11 => NULL,
X_attribute12 => NULL,
X_attribute13 => NULL,
X_attribute14 => NULL,
X_attribute15 => NULL,
X_datatype_id => 1,
X_category_id => p_cat_id,
X_security_type => 4,
X_security_id => NULL,
X_publish_flag => 'Y',
X_image_type => NULL,
X_storage_type => NULL,
X_usage_type => 'O',
X_language => lv_language,
X_description => NULL,
X_file_name => NULL,
X_media_id => ln_media_id,
X_doc_attribute_category => NULL,
X_doc_attribute1 => NULL,
X_doc_attribute2 => NULL,
X_doc_attribute3 => NULL,
X_doc_attribute4 => NULL,
X_doc_attribute5 => NULL,
X_doc_attribute6 => NULL,
X_doc_attribute7 => NULL,
X_doc_attribute8 => NULL,
X_doc_attribute9 => NULL,
X_doc_attribute10 => NULL,
X_doc_attribute11 => NULL,
X_doc_attribute12 => NULL,
X_doc_attribute13 => NULL,
X_doc_attribute14 => NULL,
X_doc_attribute15 => NULL );
/***************************************************************
This inserts the related text as an attachment in short text
table
***************************************************************/
INSERT INTO
FND_DOCUMENTS_SHORT_TEXT( MEDIA_ID,
SHORT_TEXT)
VALUES ( ln_media_id,
p_short_text);
COMMIT;
x_return_status := 'S';
EXCEPTION
WHEN OTHERS THEN
x_return_status := 'E';
x_return_msg := 'Exception Occured : 'SQLCODE'-'SQLERRM;
END XX_ATTACH_LOAD;

No comments:

Post a Comment