GL to AP DRILL DOWN SQL -- 11i

select gjb.NAME,gjh.JE_CATEGORY,gjh.JE_SOURCE, (gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4) GL_COMBINATION, --gjl.ACCOUNTED_DR,gjl.ACCOUNTED_CR, ael.ACCOUNTED_DR,ael.ACCOUNTED_CR,ael.REFERENCE5 INVOICE_NUM,pv.VENDOR_NAME from gl_je_batches gjb, gl_je_headers gjh, gl_je_lines gjl, gl_code_combinations gcc, ap_ae_lines_all ael, po_vendors pv where gjb.JE_BATCH_ID=gjh.JE_BATCH_ID and gjh.JE_HEADER_ID=gjl.JE_HEADER_ID and gjl.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID and gjl.GL_SL_LINK_ID=ael.GL_SL_LINK_ID and ael.THIRD_PARTY_ID=pv.VENDOR_ID and gjb.JE_BATCH_ID=1549875--1549785 and gcc.segment4='2495'

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

Advance Pricing Qualifier Upload

CREATE OR REPLACE PACKAGE BODY APPS.XX_EXC_DAP_DSL_ORDER_PKG IS
--
--
FUNCTION XX_DAP_DSL_ORDER_EXCLUDE1(p_order_number IN NUMBER )
RETURN CHAR IS
--
lc_order_type VARCHAR2(50);
--
CURSOR lcu_order_type (p_order_number NUMBER)
IS
SELECT 'Y'
FROM oe_order_headers_all oeh,
oe_transaction_types_tl otl,
fnd_lookup_values flv
WHERE oeh.order_number = p_order_number
AND oeh.order_type_id = otl.transaction_type_id
AND otl.LANGUAGE = USERENV('LANG')
AND flv.lookup_type = 'XXP4U_DAP_DSL_EXCLUSION_LIST'
AND flv.meaning = otl.NAME
AND flv.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(flv.start_date_active, TRUNC(SYSDATE))
AND NVL(flv.end_date_active, TRUNC(SYSDATE));
BEGIN
--
OPEN lcu_order_type (p_order_number => p_order_number);
FETCH lcu_order_type INTO lc_order_type;
--
IF lcu_order_type%FOUND THEN
lc_order_type:='MATCH';
ELSE
lc_order_type:='NOMATCH';
END IF;
--
CLOSE lcu_order_type;
--
RETURN lc_order_type;
--
EXCEPTION
--
WHEN NO_DATA_FOUND THEN
--
RETURN 'NOMATCH';
--
WHEN OTHERS THEN
RETURN 'PLEASE CHECK THE SETUP-ERROR OCCURED';
END XX_DAP_DSL_ORDER_EXCLUDE1;
--
--
PROCEDURE XX_QUALIFIER_LOAD
(
p_qualifier_context IN VARCHAR2 ,
p_qualifier_attribute IN VARCHAR2 ,
p_comparison_operator_code IN VARCHAR2 ,
p_qualifier_attr_value IN VARCHAR2 ,
retcode OUT NUMBER ,
errbuff OUT VARCHAR2
)
AS
/* |
| Description: Procedure to update qualifiers for Existing Budgets
| This will be one time activity.
| This package also supports the Qualifier Attribute
| loading process.
+----------------------------------------------------------------------------*/
-- Local variables
ln_qual_cnt NUMBER := 1;
ln_msg_count NUMBER;
ln_error_location NUMBER;
lc_return_status VARCHAR2(100);
lc_msg_data VARCHAR2(4000);
lb_proceed BOOLEAN;
lt_qual_tbl ozf_offer_pvt.qualifiers_tbl_type;
fin_check_tbl ozf_offer_pvt.qualifiers_tbl_type;
ln_msg_index_out NUMBER(10);
ld_unique_header_line_cnt NUMBER(10);
lt_profile_value NUMBER ;
ln_count NUMBER:=0;
lc_header_value VARCHAR2(20):='N';
--
--------------------------------------------------
-------- Cursor for getting Budget Data --------
--------------------------------------------------
--
CURSOR LCU_ACT_BUDGET_NAME
IS
SELECT DISTINCT fund_name budget_name
from xxp4u.xxp4u_budget_upload_stg
where line_status='P' ;
--
CURSOR LCU_ACT_BUDGET_ID(p_budget_name VARCHAR2)
IS
SELECT list_header_id,NAME BUDGET_NUMBER ,description modifier_name
FROM qp_list_headers
WHERE ACTIVE_FLAG='Y'
AND UPPER(description)=UPPER(p_budget_name)
AND end_date_active IS NULL OR end_date_active >SYSDATE
GROUP BY list_header_id,NAME,description
ORDER BY NAME;
BEGIN
fnd_file.put_line (fnd_file.LOG,'Starting for Qualifier Update');
---------------------------------------
/* Block for capturing Profile values */
---------------------------------------
--lt_profile_value:=fnd_profile.VALUE('XXP4U_QUALIFIER_LOAD');
--
fnd_file.put_line (fnd_file.LOG,'Entering into loop for Updation');
--
FOR ACT_BUG IN LCU_ACT_BUDGET_NAME LOOP
FOR ACT_HDR_ID IN LCU_ACT_BUDGET_ID(ACT_BUG.budget_name) LOOP
BEGIN
/*--------------------------------------------------------------*/
-- Block to check wheather the Qualifier Exists for the Budget --
/*--------------------------------------------------------------*/
--
SELECT qq.list_header_id
INTO lc_header_value
FROM qp_qualifiers qq
WHERE qq.qualifier_attr_value = 'MATCH'--p_qualifier_attr_value
AND qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE37'--p_qualifier_attribute
AND qq.list_header_id=ACT_HDR_ID.list_header_id
GROUP BY qq.list_header_id;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
lc_header_value:='P';
--
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Problem in Section 11Y for header id'||ACT_HDR_ID.list_header_id);
--
END;
--
IF lc_header_value='P' THEN
--
fnd_file.put_line (fnd_file.LOG,'Calling the API...');
ln_count:=ln_count+1;
fnd_file.put_line (fnd_file.LOG,ACT_HDR_ID.BUDGET_NUMBER||
'- List_Header_id'||ACT_HDR_ID.list_header_id||
'- Budget Name'|| ACT_HDR_ID.modifier_name);
--
--For Attribute1
/* lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := p_qualifier_context; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := p_qualifier_attribute; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := p_qualifier_attr_value; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := p_comparison_operator_code;-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'
*/
lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := 'ORDER'; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE37'; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := 'MATCH'; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := 'NOT =';-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'
-------------------------------------
/* Call The API to upload qualifier */
-------------------------------------
ozf_offer_pvt.process_market_qualifiers(
p_init_msg_list => fnd_api.g_true,
p_api_version => 1.0,
p_commit => fnd_api.g_false,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_qualifiers_tbl => lt_qual_tbl,
x_error_location => ln_error_location
);
COMMIT;
ELSE
fnd_file.put_line (fnd_file.LOG,'For Budget Name :'||ACT_HDR_ID.modifier_name||'the qualifier already Exists');
END IF;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
retcode := 2;
fnd_file.put_line (fnd_file.LOG,'Problem in Setup'||SQLERRM);
fnd_file.put_line (fnd_file.LOG,'Successfully updated:'||ln_count||' Records');
END XX_QUALIFIER_LOAD;
--
PROCEDURE XX_QUALIFIER_1_LOAD
(
p_qualifier_context IN VARCHAR2 ,
p_qualifier_attribute IN VARCHAR2 ,
p_comparison_operator_code IN VARCHAR2 ,
p_qualifier_attr_value IN VARCHAR2 ,
retcode OUT NUMBER ,
errbuff OUT VARCHAR2
)
AS
/*
+-----------------------------------------------------------------------------
|
+-----------------------------------------------------------------------------
|
|
|
| Description: Procedure to update qualifiers for Existing Budgets
| This will be one time activity.
| This package also supports the Qualifier Attribute
| loading process.
+----------------------------------------------------------------------------*/
-- Local variables
ln_qual_cnt NUMBER := 1;
ln_msg_count NUMBER;
ln_error_location NUMBER;
lc_return_status VARCHAR2(100);
lc_msg_data VARCHAR2(4000);
lb_proceed BOOLEAN;
lt_qual_tbl ozf_offer_pvt.qualifiers_tbl_type;
fin_check_tbl ozf_offer_pvt.qualifiers_tbl_type;
fin_check_exc EXCEPTION;
ln_msg_index_out NUMBER(10);
ld_unique_header_line_cnt NUMBER(10);
lt_profile_value NUMBER;
ln_count NUMBER:=0;
CURSOR LCU_MODIFIER_ID
IS
SELECT list_header_id,NAME BUDGET_NUMBER ,description modifier_name
FROM qp_list_headers
WHERE ACTIVE_FLAG='Y'
--AND NAME IN('10023','10024','10025','10026','10027')
AND end_date_active IS NULL OR end_date_active >SYSDATE
GROUP BY list_header_id,NAME,description
ORDER BY NAME;

BEGIN
fnd_file.put_line (fnd_file.LOG,'Starting for Qualifier Update');
---------------------------------------
/* Block for capturing Profile values */
---------------------------------------
--lt_profile_value:=fnd_profile.VALUE('XXP4U_QUALIFIER_LOAD');
fnd_file.put_line (fnd_file.LOG,'Entering into loop for Updation');
FOR ACT_HDR_ID IN LCU_MODIFIER_ID LOOP
ln_count:=ln_count+1;
fnd_file.put_line (fnd_file.LOG,ACT_HDR_ID.BUDGET_NUMBER||
'- List_Header_id'||ACT_HDR_ID.list_header_id||
'- Budget Name'|| ACT_HDR_ID.modifier_name);
--For Attribute1
/*
lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := p_qualifier_context; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := p_qualifier_attribute; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := p_qualifier_attr_value; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := p_comparison_operator_code;-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'
*/
lt_qual_tbl(1).list_header_id := ACT_HDR_ID.list_header_id; -- Budget_header_id
lt_qual_tbl(1).list_line_id := -1; -- Line_id default -1
lt_qual_tbl(1).qualifier_context := 'ORDER'; --'ORDER'Qualifier Context
lt_qual_tbl(1).qualifier_attribute := 'QUALIFIER_ATTRIBUTE37'; --'QUALIFIER_ATTRIBUTE9'; -- Qualifier Attribute
lt_qual_tbl(1).qualifier_attr_value := 'MATCH'; --lt_profile_value ; -- Qualifier Attribute Value
lt_qual_tbl(1).operation := 'CREATE'; -- Mode('CREATE'/'DELETE')
lt_qual_tbl(1).comparison_operator_code := 'NOT =';-- Operator '='
lt_qual_tbl(1).qualifier_grouping_no := -1 ; -- Grouping Number '1'

-------------------------------------
/* Call The API to upload qualifier */
-------------------------------------
ozf_offer_pvt.process_market_qualifiers(
p_init_msg_list => fnd_api.g_true,
p_api_version => 1.0,
p_commit => fnd_api.g_false,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_qualifiers_tbl => lt_qual_tbl,
x_error_location => ln_error_location
);
COMMIT;
END LOOP;
fnd_file.put_line (fnd_file.LOG,'End for Qualifier Update');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG,'Problem in Setup'||SQLERRM);
fnd_file.put_line (fnd_file.LOG,'Successfully updated:'||ln_count||' Records');
retcode := 2;
END XX_QUALIFIER_1_LOAD;
--
END XX_EXC_DAP_DSL_ORDER_PKG;
/

How To Set Email Style Preference For All Users At Once?

Applies to: Oracle Workflow Cartridge - Version: 11.5.9 to 11.5.10Information in this document applies to any platform.

Goal
The goal of that Note is to explain how to bulk update the Email Style preference for all E-Business Suite users at once. The same can be used to update the Email Style for a group of users only, for instance the users who have the preference set to "Disabled" ...
Usually, each user must reset the Email Style preference through the "Preferences" page available from his Home Page.

Solution
1. Unfortunately, currently there is no seeded way to do this. An enhancement request has been logged for this under Bug 5748131 (NEED PLEASANT WAY TO BULK RESET NOTIFICATION PREFERENCE FROM DISABLED).It is under Oracle Development's review ...
2. Currently, the only workaround to change the Email Style of all users is to update the tables.Note that there are 2 tables to update :
FND_USER_PREFERENCES and WF_LOCAL_ROLES.
NB:You should backup those tables before performing the updates.
3. Updates for All Users would look like :
a.
update wf_local_roles set notification_preference='' where orig_system in ('FND_USR','PER');
b.
update fnd_user_preferences set preference_value='' where preference_name='MAILTYPE' and
module_name='WF' and user_name <> '-WF_DEFAULT-';

4.
Updates for Users having the preference set to "Disabled" would look like :
a.
update wf_local_rolesset notification_preference=''where orig_system in ('FND_USR','PER')and name in(select user_namefrom fnd_user_preferenceswhere preference_name='MAILTYPE'and module_name='WF'and preference_value='DISABLED');b. update fnd_user_preferencesset preference_value=''where preference_name='MAILTYPE'and module_name='WF'and preference_value='DISABLED';

The where clauses of above SQL can be modified to match your needs

NB : Possible values for are :

QUERY (corresponds to preference value "Do not send me mail")
MAILTEXT (corresponds to preference value "Plain text mail")
MAILATTH (corresponds to preference value "Plain text mail with HTML attachments") MAILHTML (corresponds to preference value "HTML mail with attachments")
MAILHTM2 (corresponds to preference value "HTML mail")
SUMMARY (corresponds to preference value "Plain text summary mail")
SUMHTL (corresponds to preference value "HTML summary mail")
DISABLED (corresponds to preference value "Disabled")

ReferencesNote 360541.1 - 11.5.10 Users notification preference becomes Disabled in table FND_USER_PREFERENCES table