Function for geting WF Notification History

CREATE OR REPLACE FUNCTION get_hist ( p_nid IN PLS_INTEGER
, p_disp_type IN VARCHAR2 DEFAULT WF_NOTIFICATION.doc_text ) RETURN VARCHAR2 AS


---------------------------------------------------------------------
-- This Function is created for returning the Notification History --
-- Para Meter : p_nid ( Notification ID) --
-- Created By : Ajit Raut Creation Date: 10-Feb-2009 --

-- Eg: select get_hist('9962') from dual --

---------------------------------------------------------------------
v_hist VARCHAR2(32767);
BEGIN
Wf_Notification.GetComments2 ( p_nid => p_nid

, p_display_type => NVL(p_disp_type,WF_NOTIFICATION.doc_text)
, p_hide_reassign => 'Y'
, p_hide_requestinfo => 'Y'
, p_action_history => v_hist );


RETURN v_hist;


END get_hist;

/

Send mail notification as HTML ATTACHMENT By Pl/Sql package

CREATE OR REPLACE PACKAGE Xxm_Send_Attachment_Email IS
/*******************************************************************************/
/* Procedure : XXM_SEND_ATTACHMENT_EMAIL */
/* Description : To send mail notification AS HTML ATTACHMENT */
/* Called By : */
/* */
/* Modificaton Log */
/* Date Author Description */
/* ---------- ------------------ --------------------------------------- */
/* 22-MAY-06 Ajit Raut Initial Release */
/* */
/*******************************************************************************/

PROCEDURE SEND_EMAIL (SUBJECT VARCHAR2
, TO_USERID VARCHAR2 := NULL
, V_BODY1 VARCHAR2 := NULL
, FROM_NAME VARCHAR2 := NULL
, TO_NAME VARCHAR2 := NULL
, CONTENT_TYPE VARCHAR2 := NULL);


END Xxm_Send_Attachment_Email;
/
----------------------------
CREATE OR REPLACE PACKAGE BODY Xxm_Send_Attachment_Email IS
/*******************************************************************************/
/* Procedure : XXM_SEND_ATTACHMENT_EMAIL */
/* Description : To send mail notification AS HTML ATTACHMENT */
/* Called By : */
/* */
/* Modificaton Log */
/* Date Author Description */
/* ---------- ------------------ --------------------------------------- */
/* 22-MAY-06 Ajit Raut Initial Release */
/* */
/*******************************************************************************/
PROCEDURE SEND_EMAIL(SUBJECT VARCHAR2
, TO_USERID VARCHAR2 := NULL
, V_BODY1 VARCHAR2 := NULL
, FROM_NAME VARCHAR2 := NULL
, TO_NAME VARCHAR2 := NULL
, CONTENT_TYPE VARCHAR2 := NULL) IS

c utl_smtp.connection;
from_userid VARCHAR2(40) :='ajitraut@yahoo.com';-- 'pil_ajit.raut@pusparun.co.in';--'webmaster@yourcompany.com';
send_user VARCHAR2(40);
from_domain VARCHAR2(200) :=SUBSTR(from_userid,INSTR(from_userid,'@')+1);-- SUBSTR('pil_ajit.raut@pusparun.co.in',INSTR('pil_ajit.raut@pusparun.co.in','@')+1);--SUBSTR(from_userid,INSTR(from_userid,'@')+1);
smtp_server VARCHAR2(50) :='10.1.1.143';-- 'YOUR_SERVER_NAME';
my_body VARCHAR2(32000);
phead2 VARCHAR2(1000);
phead22 VARCHAR2(1000);
v_body VARCHAR2(10000);
v_crlf VARCHAR2(2) :=CHR(13)||CHR(10);
v_lines VARCHAR2(1000);


PROCEDURE header(name VARCHAR2, value VARCHAR2) IS
BEGIN utl_smtp.write_data(c, name || ': ' || value || utl_tcp.CRLF);
END;
BEGIN
send_user := to_userid;
my_body := v_body1;
c := utl_smtp.open_connection(smtp_server); utl_smtp.helo(c, from_domain );
utl_smtp.mail(c, from_userid );
utl_smtp.rcpt(c, send_user );
utl_smtp.open_data(c);
header('From','"'||NVL(from_name,from_userid)||'" <'||from_userid||'>'); header('To','"'||NVL(to_name,to_userid)||'" <'||to_userid||'>');
header('Subject', subject ); header('Content-Type', NVL(content_type,'text/html'));
utl_smtp.write_data(c, utl_tcp.CRLF || my_body );
utl_smtp.close_data(c); utl_smtp.quit(c);
EXCEPTION WHEN utl_smtp.transient_error
OR utl_smtp.permanent_error
THEN utl_smtp.quit(c);
END SEND_EMAIL;
END Xxm_Send_Attachment_Email;
/

How to Set Printer in Pl/SQL Program

PROCEDURE submit_invoice_report_request(p_invoice_id IN NUMBER,
p_org_id IN NUMBER)
IS
ln_number NUMBER;
lb_boolean BOOLEAN;
PRAGMA autonomous_transaction;
BEGIN
lb_boolean:=fnd_request.set_print_options (NULL,
NULL,
1,
TRUE,
'N');
COMMIT;
ln_number:=fnd_request.submit_request('XBOL',
'XXMRAP0006',
NULL,
SYSDATE,
FALSE,
p_invoice_id,
p_org_id
);
COMMIT;
END;

Responsibility Attached to a USER

select b.RESPONSIBILITY_NAME,c.START_DATE,c.END_DATE
from
FND_USER_RESP_GROUPS_ALL a,
FND_RESPONSIBILITY_TL b,
fnd_user c
where
a.user_id=c.user_id and
a.responsibility_id=b.responsibility_id
and c.USER_NAME=:P_USER_NAME--PARAMETER SUPPLY USER NAME