Oracle EBS (ERP): FORM

Upload/Attached/View/Delete any file in Oracle Forms.

Custom File Attached – object

Step 1. Button : Upload File.

Trigger :- WHEN-BUTTON-PRESSED

IF ( :xxrmc_document_upload_v.file_name_dispaly IS NULL

AND :xxrmc_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, 'XXRMC_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, 'XXRMC_DOCUMENT_UPLOAD_V.file_name');

:xxrmc_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('XXRMC_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

SELECT NVL(max(seq_num),0) + 10

INTO lv_seq_num

FROM fnd_attached_documents

WHERE entity_name = 'XXRMC_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 => :XXRMC_DOCUMENT_UPLOAD_V.row_id,

X_attached_document_id => lv_attached_document_id, --, :document_header.attached_document_id,

X_document_id => :XXRMC_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 => 'XXRMC_BILL', --:document_header.entity_name,

X_column1 =>null, -- :parameter.column1,

X_pk1_value => :XXRMC_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, --XXRMC_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 => :XXRMC_DOCUMENT_UPLOAD_V.FILE_NAME,

X_media_id => :XXRMC_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;

Step 2. Button : View.

Trigger :- WHEN-BUTTON-PRESSED

IF :XXRMC_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 (

:xxrmc_document_upload_v.file_name_dispaly IS NOT NULL

AND :xxrmc_document_upload_v.FILE_ID IS NOT NULL

) THEN

gfm_agent := fnd_web_config.gfm_agent;

l_gfm_id := :XXRMC_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;

Step 3. Button : Delete.

Trigger :- WHEN-BUTTON-PRESSED

IF :XXRMC_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 :XXRMC_DOCUMENT_UPLOAD_V.ATTACHED_DOCUMENT_ID IS NOT NULL ) THEN

BEGIN

fnd_attached_documents3_pkg.delete_row(:XXRMC_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('XXRMC_DOCUMENT_UPLOAD_V');

clear_block(no_validate);

execute_query;

END IF;

END delete_document;

------- Thanks BM & FA

CUSTOM.PLL IN ORACLE APPLICATION
Custom Library (custom.pll) allows to extend/customize Oracle Applications form(Oracle Form) without changing or modifying Oracle Applications code. Examples may include enforcing a new business rule, opening a form using zoom etc. Most of the things that we can do using custom.pll, we can achieve that using Forms Personalization. Since Custom.pll takes the full advantage of PL/SQL so it is having an edge over Forms Personalization for complex customizations.
CUSTOM.pll is used to add extensions to Oracle’s form Functionality. Some of the common scenarios where CUSTOM.pll can be used are:-

1. Enabling/Disabling the fields
2. Changing the List of Values in a LOV field at runtime
3. Defaulting values
4. Additional record level validations
5. Navigation to other screens
6. Enabling Special Menu
Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.
How to add code to this?
Open this pll using the Form builder and make changes to the program units.

How to compile this PLL?
Once you make changes you need to compile the pll. Use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special
While writing code inside custom.pll we should consider following things:
1. We should not run any SQL statement inside this, we can use record group.
2. We should not perform any DML operations, instead we should call database procedure and functions for the same.
For following Events call will go to CUSTOM Library:

WHEN–FORM–NAVIGATE
WHEN–NEW–FORM–INSTANCE
WHEN–NEW–BLOCK–INSTANCE
WHEN–NEW–RECORD–INSTANCE
WHEN–NEW–ITEM–INSTANCE
WHEN–VALIDATE–RECORD
SPECIALn (where n is a number between 1 and 45)
ZOOM
EXPORT
KEY–Fn (where n is a number between 1-8)
Custom Library contains Custom Package which is having two Functions and one procedure.
1] ZOOM_AVAILABLE:
This function allows you to specify if zooms exist for the current context. If zooms are available for this block, then return TRUE else return FALSE. This routine is called on a per-block basis within every Applications form from the WHEN-NEW-BLOCK-INSTANCE trigger. Therefore, any code that will enable Zoom must test the current form and block from which the call is being made. By default this routine must return FALSE.
Sample code1:

function zoom_available return Boolean is
form_name varchar2(30) := name_in(‘system.current_form’);
block_name varchar2(30) := name_in(‘system.cursor_block’);
begin
if (form_name = ‘DEMXXEOR’ and block_name = ‘ORDERS’) then
return TRUE;
else
return FALSE;
end if;
end zoom_available;
Sample code2:
function zoom_available return Boolean is
form_name varchar2(30) := name_in(‘system.current_form’);
block_name varchar2(30) := name_in(‘system.cursor_block’);
begin
if (form_name = ‘APXINWKB’ and block_name = ‘INV_SUM_FOLDER’)
then
return TRUE;
elsif (form_name = ‘APXINWKB’ and block_name = ‘LINE_SUM_FOLDER’)
then
return TRUE;
else
return FALSE;
end if;
end zoom_available;

2] STYLE:
This function returns a integer value. This function allows to override the execution style of Product specific events, but it doesn’t effect generic events like when-new-form-instance. Possible return values are:
1. custom.before
2. custom.after
3. custom.override
4. custom.standard
By default it returns custom.standard.
Sample code:

function custom.style(event_name varchar2) return integer is
begin
if event_name = ’MY_CUSTOM_EVENT’ then
return custom.override;
else
return custom.standard;
end if;
end style;

3] EVENT:
This procedure allows you to execute your code at specific events including:
–    ZOOM
–    WHEN-NEW-FORM-INSTANCE
–    WHEN-NEW-BLOCK-INSTANCE
–    WHEN-NEW-RECORD-INSTANCE
–    WHEN-NEW-ITEM-INSTANCE
–    WHEN-VALIDATE-RECORD
By default this routine must perform ‘null;’
Sample code:
procedure event(event_name varchar2) is
form_name varchar2(30) := name_in(’system.current_form’);
block_name varchar2(30) := name_in(’system.cursor_block’);
begin
if (form_name = ‘XXBI’ and block_name = ‘xxcc’) Then
if(event_name = ‘WHEN-NEW-FORM-INSTNACE’)
–Write your code here
elsif(event_name = ‘WHEN-VALIDATE-RECORD’)THEN
–Write your code here
else
null
end if;
end if;
end;

How to make the changes get affected?
Once you make all the necessary changes, compile the pll and generate the PLX file. Since the CUSTOM library is loaded once for a given session, a user must log out of the application and sign-on again before any changes will become apparent.
Forms Personalization: an alternative of custom.pll
In older versions, prior to 11i, Custom.PLL was most prominently used for adding additional features in the seeded form but the latest version of Oracle EBS comes with the feature called as Forms Personalization which allows even an end user to alter the seeded forms functionality using an user interface called the Personalization form.
Advantages of Forms Personalization over Custom.PLL:
•    Forms personalization can be used by an user with limited PL/SQL knowledge.
•    Changes take place immediately on reopening the form.
•    Anything which can be done using Custom.PLL can be done using Forms Personalization also.
•    Personalizations are stored in base tables related to Form Personalization.
•    CUSTOM.pll is a single file/entity, hence only one developer can make changes to CUSTOM.pll at any given point in time. This is not a restriction in Forms personalization.
•    Easy to disable/enable with click of a button.
•    Can be moved easily through FNDLOAD from one instance to other.
•    Can be restricted at site/responsibility/user level.
•    Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability

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

Oracle Forms Builder basic Setup

Oracle Apps CUSTOM.PLL

How to Oracle Apps Forms compile in R12.1.3 to R12.2.10