[Home] [Help]
PACKAGE BODY: APPS.FND_SYSTEM_ALERT_INTG_UTIL
Source
1 PACKAGE BODY FND_SYSTEM_ALERT_INTG_UTIL AS
2 /* $Header: AFOAMSAINGB.pls 120.4 2005/09/28 13:47:03 ravmohan noship $ */
3
4 /**
5 * This function will return sql for creating xml.
6 * param p_logSEQ in - Log Sequence
7 **/
8 function GET_SQL(p_logSEQ in varchar2) return varchar2
9 is
10 retu varchar2(5000);
11 begin
12 retu := 'SELECT fpg.APPLICATIONS_SYSTEM_NAME, fpg.RELEASE_NAME'
13 || ' , flue.severity, flue.status, fnd_date.DATE_TO_DISPLAYDT(flm.timestamp, ''GMT'') TIMESTAMP_GMT, flue.category, flue.english_message'
14 || ' , flm.log_sequence, flue.unique_exception_id'
15 || ' , fltc.component_type, fltc.component_id, fac.component_name, nvl(fac.display_name,''UNKNOWN'') COMP_DISPLAY_NAME, fac.description COMP_DESCRIPTION'
16 || ' , fltc.component_appl_id, fa.application_short_name COMPONENT_APPL_SHORT_NAME, fat.application_name COMPONENT_APPL_NAME, flm.module'
17 || ' , fltc.user_id, fu.user_name, fltc.responsibility_id, fr.responsibility_key, frt.responsibility_name'
18 || ' , fltc.resp_appl_id, fra.application_short_name RESP_APPL_SHORT_NAME, frat.application_name RESP_APPL_NAME'
19 || ' , flm.audsid, gvi.instance_name, flm.db_instance, fle.session_module, fle.session_action'
20 || ' , fltc.session_id, flm.node, flm.node_ip_address, fltc.security_group_id, fsg.security_group_key, fsgt.security_group_name'
21 || ' , flm.process_id, flm.thread_id, flm.jvm_id'
22 || ' , fltc.transaction_context_id, fltc.transaction_type, fltc.transaction_id'
23 || ' FROM'
24 || ' fnd_log_transaction_context fltc, fnd_log_messages flm, fnd_log_attachments flattach'
25 || ' , fnd_log_exceptions fle, fnd_log_unique_exceptions flue, fnd_user fu, fnd_app_components_vl fac'
26 || ' , fnd_application fa, fnd_application_tl fat, fnd_responsibility fr, fnd_responsibility_tl frt'
27 || ' , fnd_application fra, fnd_application_tl frat, fnd_security_groups fsg, fnd_security_groups_tl fsgt'
28 || ' , gv$instance gvi, fnd_product_groups fpg'
29 || ' WHERE '
30 || ' fltc.transaction_context_id = flm.transaction_context_id and flm.log_sequence = fle.log_sequence'
31 || ' and fle.unique_exception_id = flue.unique_exception_id and flm.user_id = fu.user_id (+) '
32 || ' and fltc.component_type = fac.component_type (+) and fltc.component_appl_id = fac.application_id (+)'
33 || ' and fltc.component_id = fac.component_id (+) and fltc.component_appl_id = fa.application_id (+)'
34 || ' and fltc.responsibility_id = fr.responsibility_id (+) and fltc.resp_appl_id = fra.application_id (+)'
35 || ' and fltc.security_group_id = fsg.security_group_id (+) and flm.db_instance = gvi.instance_number (+)'
36 || ' and flm.log_sequence = flattach.log_sequence (+) and fa.application_id = fat.application_id (+)'
37 || ' and fat.language (+) = userenv(''LANG'') and fr.responsibility_id = frt.responsibility_id (+)'
38 || ' and frt.language (+) = userenv(''LANG'') and fra.application_id = frat.application_id (+)'
39 || ' and frat.language (+) = userenv(''LANG'') and fsg.security_group_id = fsgt.security_group_id (+)'
40 || ' and fsgt.language (+) = userenv(''LANG'') AND flm.log_sequence = ' || p_logSEQ;
41 -- || ' and fpg.ROW_NUM=1';
42
43
44 return retu;
45 end GET_SQL;
46
47
48 /**
49 * This is the generate function used for business event
50 *
51 * This function will return the System alert exception in XML format.
52 * param p_event_name in - Workflow Business Event Name
53 * param p_event_key in - Workflow Business Event Key
54 * param wf_parameter_list_t in - Event parameter List
55 * param errbuf out type - If any error occurs it will have error message
56 * else null
57 **/
58 function GET_EXCPETION_DETAILS(p_event_name in varchar2
59 , p_event_key in varchar2
60 , p_parameter_list in wf_parameter_list_t default null) return clob
61 is
62 queryCtx DBMS_XMLquery.ctxType;
63 result CLOB;
64 lSQL VARCHAR2(5000);
65 begin
66 -- set up the query context...!
67 lSQL := GET_SQL(p_event_key);
68 queryCtx := DBMS_XMLQuery.newContext(lSQL);
69 DBMS_XMLQuery.setRowTag(queryCtx,'OCCURANCE'); -- sets the row tag name
70 DBMS_XMLQuery.setRowSetTag(queryCtx,'ALERT'); -- sets rowset tag name
71
72 -- get the result..!
73 result := DBMS_XMLQuery.getXML(queryCtx);
74 DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
75
76 return result;
77 end GET_EXCPETION_DETAILS;
78
79
80
81
82
83
84 /**
85 * Debug methods
86 **/
87 procedure fdebug(msg in varchar2)
88 IS
89 l_msg VARCHAR2(1000);
90 BEGIN
91 --l_msg := dbms_utility.get_time || ' ' || msg;
92 ---dbms_output.put_line(dbms_utility.get_time || ' ' || msg);
93 ---fnd_file.put_line( fnd_file.log, dbms_utility.get_time || ' ' || msg);
94 l_msg := 'm';
95 END fdebug;
96
97 procedure printClobOut(result IN OUT NOCOPY CLOB) is
98 config_file UTL_FILE.FILE_TYPE;
99 xmlstr varchar2(32767);
100 line varchar2(2000);
101 begin
102 config_file := UTL_FILE.FOPEN ('/slot03/oracle/oam12devdb/9.2.0/appsutil/outbound/oam12dev', 'sqlQ.txt', 'W');
103 xmlstr := dbms_lob.SUBSTR(result,32767);
104 loop
105 exit when xmlstr is null;
106 --line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
107 --dbms_output.put_line('| '||line);
108 UTL_FILE.PUT_LINE(config_file, line);
109 --xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
110 end loop;
111 UTL_FILE.fclose(config_file);
112 end;
113
114 procedure writeString(msg in varchar2) is
115 config_file UTL_FILE.FILE_TYPE;
116 startIndex number;
117 lineLength number;
118 line varchar2(255);
119 begin
120 config_file := UTL_FILE.FOPEN ('/slot03/oracle/oam12devdb/9.2.0/appsutil/outbound/oam12dev', 'sqlQ.txt', 'W');
121 startIndex :=1;
122 lineLength := 255;
123 line := substr(msg, startIndex, lineLength);
124
125 loop
126 exit when line is null;
127 ---dbms_output.put_line(line);
128 UTL_FILE.PUT_LINE(config_file, line);
129 startIndex := startIndex + lineLength;
130 line := substr(msg, startIndex, lineLength);
131 end loop;
132
133 UTL_FILE.fclose(config_file);
134 end;
135
136
137 /**
138 * For Testing all API's
139 * After testing it will put the Apps mode in the original state.
140 **/
141 procedure TEST
142 is
143 result CLOB;
144 begin
145 fdebug('Testing: GET_EXCPETION_DETAILS');
146 writeString(GET_SQL('499635'));
147 result := GET_EXCPETION_DETAILS('test', '586182');
148 -- Now you can use the result to put it in tables/send as messages..
149 -- Comment out lines in printclob
150 printClobOut(result);
151 end;
152
153
154
155 END FND_SYSTEM_ALERT_INTG_UTIL;