How to open Attachments in Oracle Apps

Sample Code to open BSA Attachment.

PROCEDURE WHEN_MOUSE_DOUBLECLICK AS
lv_trg_item VARCHAR2(2000):= :SYSTEM.TRIGGER_ITEM;
l_doc_id NUMBER := :AGREE_CNTR.HEADER_ID;
l_bsa_doc_type VARCHAR2(2) := OE_CONTRACTS_UTIL.get_G_BSA_DOC_TYPE();
l_layout_template_id NUMBER := :AGREE_CNTR.LAYOUT_TEMPLATE_ID;

gfm_agent VARCHAR2(255);
l_media_id NUMBER:=Null;
l_url VARCHAR2(2000);
CURSOR cur_att (cp_header_id NUMBER) IS
SELECT
media_id
FROM
FND_ATTACHED_DOCS_FORM_VL
WHERE function_name=decode(0,1,null,'OEXOEBSO')
AND function_Type=decode(0,1,null,'O')
AND (security_type=4 OR publish_flag='Y')
AND ((entity_name= 'OE_ORDER_HEADERS'
AND pk1_value = cp_header_id ))
AND category_description = 'Signed Agreement';
BEGIN
IF lv_trg_item ='AGREE_CNTR.TEMPLATE' THEN
IF :AGREE_CNTR.STATUS = 'Executed' THEN
gfm_agent := fnd_web_config.gfm_agent;
OPEN cur_att(l_doc_id);
FETCH cur_att INTO l_media_id;
CLOSE cur_att;
IF l_media_id IS NOT NULL THEN
l_url := fnd_gfm.construct_download_URL(gfm_agent,l_media_id, FALSE);
l_url := replace(replace(l_url,'&','%38'),'%','%37');
fnd_utilities.open_url ( l_url);
END IF;
RETURN;
ELSE
l_bsa_doc_type := wfa_html.conv_special_url_chars(l_bsa_doc_type);
l_doc_id := wfa_html.conv_special_url_chars(l_doc_id);
l_layout_template_id := wfa_html.conv_special_url_chars(l_layout_template_id);
fnd_function.execute(function_name=> 'ONT_PRINT',
other_params =>
'docId='|| l_doc_id
|| '&docType='|| l_bsa_doc_type
|| '&layoutTemplateId='|| l_layout_template_id );
RETURN;
END IF;
END IF;
END WHEN_MOUSE_DOUBLECLICK;

Special Character Conversion.

Oracle apps : wf_html.conv_special_url_chars

Another way of doing is :utl_i18n.escape_reference(''String'','us7ascii');


FUNCTION conv_special_url_chars (p_url_token IN VARCHAR2) RETURN VARCHAR2
IS
c_unreserved constant varchar2(72) :=
'-_.!*''()~ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
c_reserved constant varchar2(72) := '%">^{}<[]`|/#?&=$:;+';
l_tmp varchar2(32767) := '';
l_onechar varchar2(4);
l_byte_len integer;
i integer;
l_str varchar2(48);

BEGIN
if p_url_token is NULL then
return NULL;
end if;
for i in 1 .. length(p_url_token) loop
l_onechar := substr(p_url_token,i,1);
--Extracting out each character to be replaced.
if instr(c_unreserved, l_onechar) > 0 then
--Check if it is part of the ASCII unreserved
--excluded from encoding just append to the URL
--string
l_tmp := l_tmp || l_onechar;

elsif l_onechar = ' ' then
--Space encoded as '%20'
l_tmp := l_tmp || '%20';

elsif instr(c_reserved,l_onechar) >0 then
--If it is any of the reserved characters in ascii
--replace with equivalent HEX
l_onechar := REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(
REPLACE(l_onechar,
'%','%25'),
' ','%20'),
'"','%22'),
'>','%3E'),
'^','%5E'),
'{','%7B'),
'}','%7D'),
'<','%3C'),
'[','%5B'),
']','%5D'),
'`','%60'),
'|','%7C'),
'/','%2F'),
'#','%23'),
'?','%3F'),
'&','%26'),
'=','%3D'),
'$','%24'),
':','%3A'),
';','%3B'),
'+','%2B'),
'''','%27');
l_tmp := l_tmp || l_onechar;
else
--For multibyte
-- 1. Obtain length for each character
-- 2. ascii(l_char)decimal representation in the database
-- character set
-- 3. Change it to the format model :
-- to_char(ascii(l_onechar),'FM0X')
-- 4. Add to the already encoded string.
-- characters
l_byte_len := lengthb(l_onechar);
if l_byte_len = 1 then
l_tmp := l_tmp || '%' ||
substr(to_char(ascii(l_onechar),'FM0X'),1,2);
elsif l_byte_len = 2 then
l_str := to_char(ascii(l_onechar),'FM0XXX');
l_tmp := l_tmp
|| '%' || substr(l_str,1,2)
|| '%' || substr(l_str,3,2);
elsif l_byte_len = 3 then
l_str := to_char(ascii(l_onechar),'FM0XXXXX');
l_tmp := l_tmp
|| '%' || substr(l_str,1,2)
|| '%' || substr(l_str,3,2)
|| '%' || substr(l_str,5,2);
elsif l_byte_len = 4 then
l_str := to_char(ascii(l_onechar),'FM0XXXXXXX');
l_tmp := l_tmp
|| '%' || substr(l_str,1,2)
|| '%' || substr(l_str,3,2)
|| '%' || substr(l_str,5,2)
|| '%' || substr(l_str,7,2);
else -- maximum precision
wf_core.raise('WFENG_PRECESSION_EXCEED');
end if;
end if;
end loop;
return l_tmp;
exception
when others then
Wf_Core.Context('wfa_html', 'conv_special_url_chars',
p_url_token);
wfa_html.Error;
END conv_special_url_chars;

SQL for Business Events

SELECT owner_name, NAME, status
FROM wf_events
WHERE upper(NAME) LIKE '%' || upper('&event_name') || '%'
AND status = 'ENABLED'
/

Self Service: Profile Option.

For JSP page personalization or about page set profile "Personalize Self-Service Defn" to Yes

Oracle Application : Session Timeout Profile name "ICX:Session Timeout"