DBA Data[Home] [Help]

PACKAGE BODY: APPS.FND_LOG_REPOSITORY

Source


1 package body FND_LOG_REPOSITORY as
2 /* $Header: AFUTLGRB.pls 120.12.12010000.3 2010/03/17 18:18:29 pferguso ship $ */
3 
4    --
5    -- PRIVATE TYPES, VARIABLES
6    --
7 
8    /*
9    **  For INIT Procedure
10    */
11    type MODULE_TAB_TYPE   is table of varchar2(255) index by binary_integer;
12 
13    MODULE_TAB         MODULE_TAB_TYPE;
14    TABLE_SIZE         binary_integer := 0;  /* the size of above tables*/
15 
16    AFLOG_ENABLED_X   BOOLEAN         := TRUE;/* default should never be used.*/
17    AFLOG_FILENAME_X  VARCHAR2(255)   := NULL;
18    AFLOG_LEVEL_X     NUMBER          := 6;/* default should never be used.*/
19    AFLOG_MODULE_X    VARCHAR2(2000)  := NULL;
20    AFLOG_BUFFER_SIZE_X      NUMBER   := 1000;
21    AFLOG_BUFFER_MODE_X      NUMBER   := 0;
22    SESSION_ID_X      NUMBER          := NULL;
23    USER_ID_X         NUMBER          := NULL;
24 
25    SELF_INITED_X     BOOLEAN         := FALSE;
26 
27    TXN_SESSION	     NUMBER	     := NULL;
28    TXN_MACHINE	     VARCHAR2(64)    := NULL;
29    TXN_PROCESS       VARCHAR2(9)     := NULL;
30    TXN_PROGRAM       VARCHAR2(48)    := NULL;
31    TXN_INSTANCE      VARCHAR2(16)  := NULL;
32 
33    /* For Buffered Mode */
34    G_BUFFER_POS	     NUMBER        := 1;
35    MODULE_TABLE FND_TABLE_OF_VARCHAR2_255;
36    LOG_LEVEL_TABLE FND_TABLE_OF_NUMBER;
37    MESSAGE_TEXT_TABLE FND_TABLE_OF_VARCHAR2_4000;
38    SESSION_ID_TABLE FND_TABLE_OF_NUMBER;
39    USER_ID_TABLE FND_TABLE_OF_NUMBER;
40    TIMESTAMP_TABLE FND_TABLE_OF_DATE;
41    LOG_SEQUENCE_TABLE FND_TABLE_OF_NUMBER;
42    ENCODED_TABLE FND_TABLE_OF_VARCHAR2_1;
43    NODE_TABLE varchar2(60) := NULL;
44    NODE_IP_ADDRESS_TABLE varchar2(30) := NULL;
45    PROCESS_ID_TABLE varchar2(120) := NULL;
46    JVM_ID_TABLE varchar2(120) := NULL;
47    THREAD_ID_TABLE FND_TABLE_OF_VARCHAR2_120;
48    AUDSID_TABLE FND_TABLE_OF_NUMBER;
49    DB_INSTANCE_TABLE FND_TABLE_OF_NUMBER;
50    TRANSACTION_CONTEXT_ID_TABLE FND_TABLE_OF_NUMBER;
51 
52    /*
53    **  For GET_CONTEXT Function
54    **     Types for storing context info before calling autonomous
55    **     logging procedures, like
56    **     FND_LOG_REPOSITORY.String_Unchecked_Internal2
57    */
58    type CONTEXT_REC is record (
59            a_col   varchar2(30),
60            a_val    varchar2(4000) );
61    type CONTEXT_ARRAY is table of CONTEXT_REC
62          index by binary_integer;
63 
64    /* For Proxy Alerting */
65    G_PRX_CHILD_TRANS_CONTEXT_ID NUMBER;
66    G_PRX_SESSION_ID fnd_log_messages.session_id%TYPE;
67    G_PRX_USER_ID fnd_log_messages.user_id%TYPE;
68    G_PRX_SESSION_MODULE fnd_log_exceptions.session_module%TYPE;
69    G_PRX_SESSION_ACTION fnd_log_exceptions.session_action%TYPE;
70    G_PRX_MODULE fnd_log_messages.module%TYPE;
71    G_PRX_NODE fnd_log_messages.node%TYPE;
72    G_PRX_NODE_IP_ADDRESS fnd_log_messages.node_ip_address%TYPE;
73    G_PRX_PROCESS_ID fnd_log_messages.process_id%TYPE;
74    G_PRX_JVM_ID fnd_log_messages.jvm_id%TYPE;
75    G_PRX_THREAD_ID fnd_log_messages.thread_id%TYPE;
76    G_PRX_AUDSID fnd_log_messages.audsid%TYPE;
77    G_PRX_DB_INSTANCE fnd_log_messages.db_instance%TYPE;
78 
79    /*
80     * For Proxy Alerting: Index values to use within CONTEXT_ARRAY.
81     * This is mostly for code readability in order to avoid passing
82     * many separate parameters.
83     *
84     * Procedure INIT_CHILD_CONTEXT will look for different context
85     * values from the context array as follows:
86     *
87     * E.g. for child context user id:
88     * l_user_id := p_context_array(CCI_USER_ID).a_val;
89     */
90    CCI_USER_ID CONSTANT NUMBER := 1;
91    CCI_RESP_APPL_ID CONSTANT NUMBER := 2;
92    CCI_RESPONSIBILITY_ID CONSTANT NUMBER := 3;
93    CCI_SECURITY_GROUP_ID CONSTANT NUMBER := 4;
94    CCI_COMPONENT_TYPE CONSTANT NUMBER := 5;
95    CCI_COMPONENT_APPL_ID CONSTANT NUMBER := 6;
96    CCI_COMPONENT_ID CONSTANT NUMBER := 7;
97    CCI_SESSION_ID CONSTANT NUMBER := 8;
98    CCI_SESSION_ACTION CONSTANT NUMBER := 9;
99    CCI_SESSION_MODULE CONSTANT NUMBER := 10;
100    CCI_MODULE CONSTANT NUMBER := 11;
101    CCI_NODE CONSTANT NUMBER := 12;
102    CCI_NODE_IP_ADDRESS CONSTANT NUMBER := 13;
103    CCI_PROCESS_ID CONSTANT NUMBER := 14;
104    CCI_JVM_ID CONSTANT NUMBER := 15;
105    CCI_THREAD_ID CONSTANT NUMBER := 16;
106    CCI_AUDSID CONSTANT NUMBER := 17;
107    CCI_DB_INSTANCE CONSTANT NUMBER := 18;
108 
109    /* fnd_log_enabled_tracing BOOLEAN := FALSE; */
110 
111 /***For debugging purpose */
112  PROCEDURE DEBUG(p_msg IN VARCHAR2) is
113  l_num number;
114   l_msg 		VARCHAR2(100);
115  --config_file UTL_FILE.FILE_TYPE;
116  begin
117     ------Debug using file system
118     --config_file := UTL_FILE.FOPEN ('/slot03/oracle/oam12devdb/9.2.0/appsutil/outbound/oam12dev', 'debugFC.txt', 'A');
119     --l_msg  := dbms_utility.get_time || '   ' || p_msg;
120     --UTL_FILE.PUT_LINE(config_file, l_msg);
121     --UTL_FILE.fclose(config_file);
122 
123     ------Debug using DB
124     --insert into DEBUG_FND_LOG_REPOSITORY(MSG) values(p_msg);
125     --commit;
126     --dbms_output.put_line(p_msg);
127     l_msg := null;
128  end DEBUG;
129 
130 
131 
132    /**
133     *  Private function for checking if alerting is enabled.
134     *  This method checks if Alerting is enabled at severity level defined by
135     *   input parameter p_msg_sev. If not, it returns false.
136     *         If yes, it checks if limit for maximum number of alerts has
137     *  been reached. If yes - it returns false
138     *
139     *  Argeuments
140     *      p_msg_sev  Raised Alert severity.
141     */
142    FUNCTION IS_ALERTING_ENABLED(p_msg_sev IN VARCHAR2) return boolean is
143       l_retu boolean;
144       l_sys_al_level varchar2(80);  /*fnd_profile_options.PROFILE_OPTION_NAME%TYPE*/
145       l_alertCount number;
146       l_pr_al_count number;
147    begin
148       l_retu := FALSE;
149 	if (p_msg_sev IS NULL)THEN
150            return l_retu;
151       end if;
152 
153       l_sys_al_level := fnd_profile.value('OAM_ENABLE_SYSTEM_ALERT');
154       DEBUG('IS_ALERTING_ENABLED::l_sys_al_level' || l_sys_al_level || ' p_msg_sev' || p_msg_sev );
155       --Check Valid profile value
156 	if ((l_sys_al_level <> '00_NONE')  AND (l_sys_al_level <> '10_CRITICAL')
157        AND(l_sys_al_level <> '20_ERROR') AND (l_sys_al_level <> '30_WARNING'))THEN
158            return l_retu;
159       end if;
160 
161 
162 
163 	if (l_sys_al_level = '00_NONE')THEN
164            return l_retu;
165       end if;
166 
167       l_sys_al_level := substr(l_sys_al_level, 4);
168       DEBUG('IS_ALERTING_ENABLED: Pr OAM_ENABLE_SYSTEM_ALERT enable. Next Check :sys_al_level' || l_sys_al_level);
169       DEBUG('IS_ALERTING_ENABLED::p_msg_sev' || p_msg_sev);
170 
171 
172 	if (l_sys_al_level >= p_msg_sev)THEN
173         l_pr_al_count := fnd_profile.value('OAM_MAX_SYSTEM_ALERT');
174         select count(*) into l_alertCount from FND_LOG_UNIQUE_EXCEPTIONS
175             where STATUS = 'N';
176         DEBUG('IS_ALERTING_ENABLED: can log msg Sevrity lower Next chek:l_pr_al_count' || l_pr_al_count);
177         DEBUG('IS_ALERTING_ENABLED::l_alertCount' || l_alertCount);
178         if (l_alertCount < l_pr_al_count)  then
179            l_retu := TRUE;
180         end if;
181       end if;
182 
183         if(l_retu = true) then
184            DEBUG('IS_ALERTING_ENABLED:Can log :return true');
185         else
186            DEBUG('IS_ALERTING_ENABLED:Can Not log:return false');
187         end if;
188       return l_retu;
189    end IS_ALERTING_ENABLED;
190 
191 
192    /*
193    ** FND_LOG_REPOSITORY.INIT_TRANSACTION_INTERNAL
194    **
195    ** Description:
196    ** Initializes a log transaction.  A log transaction
197    ** corresponds to an instance or invocation of a single
198    ** component.  (e.g. A concurrent request, service process,
199    ** open form, ICX function)
200    **
201    */
202 
203    PROCEDURE INIT_TRANSACTION_INTERNAL(
204                P_TRANSACTION_TYPE            IN VARCHAR2 DEFAULT NULL,
205                P_TRANSACTION_ID              IN NUMBER   DEFAULT NULL,
206                P_COMPONENT_TYPE              IN VARCHAR2 DEFAULT NULL,
207                P_COMPONENT_APPL_ID           IN VARCHAR2 DEFAULT NULL,
208                P_COMPONENT_ID                IN NUMBER   DEFAULT NULL,
209                P_SESSION_ID                  IN NUMBER   DEFAULT NULL,
210                P_USER_ID                     IN NUMBER   DEFAULT NULL,
211                P_RESP_APPL_ID                IN NUMBER   DEFAULT NULL,
212                P_RESPONSIBILITY_ID           IN NUMBER   DEFAULT NULL,
213                P_SECURITY_GROUP_ID           IN NUMBER   DEFAULT NULL,
214 	       P_PARENT_CONTEXT_ID	     IN NUMBER 	 DEFAULT NULL)  is
215    pragma AUTONOMOUS_TRANSACTION;
216 	l_transaction_context_id number;
217      begin
218        insert into FND_LOG_TRANSACTION_CONTEXT
219          (TRANSACTION_CONTEXT_ID,
220           SESSION_ID,
221           TRANSACTION_TYPE,
222           TRANSACTION_ID,
223           USER_ID,
224           RESP_APPL_ID,
225           RESPONSIBILITY_ID,
226           SECURITY_GROUP_ID,
227           COMPONENT_TYPE,
228           COMPONENT_APPL_ID,
229           COMPONENT_ID,
230           CREATION_DATE,
231 	  PARENT_CONTEXT_ID
232          ) values
233 	 (FND_LOG_TRANSACTION_CTX_ID_S.nextval,
234           nvl(P_SESSION_ID, -1),
235           P_TRANSACTION_TYPE,
236           nvl(P_TRANSACTION_ID, -1),
237           nvl(P_USER_ID, -1),
238           nvl(P_RESP_APPL_ID, -1),
239           nvl(P_RESPONSIBILITY_ID, -1),
240           nvl(P_SECURITY_GROUP_ID, -1),
241           P_COMPONENT_TYPE,
242           nvl(P_COMPONENT_APPL_ID, -1),
243           nvl(P_COMPONENT_ID, -1),
244           sysdate,
245 	  P_PARENT_CONTEXT_ID
246          ) RETURNING TRANSACTION_CONTEXT_ID into l_transaction_context_id;
247 
248        if (p_parent_context_id is null) then
249 	 FND_LOG.G_TRANSACTION_CONTEXT_ID := l_transaction_context_id;
250        else
251 	 -- called for a proxy so set the child transaction context id
252 	 G_PRX_CHILD_TRANS_CONTEXT_ID := l_transaction_context_id;
253        end if;
254 
255        commit;
256      end INIT_TRANSACTION_INTERNAL;
257 
258 
259    /*
260    **   GET_CONTEXT- Gathers context info within the session,
261    **   before calling autonomous logging procedures, like
262    **   FND_LOG_REPOSITORY.String_Unchecked_Internal2
263    */
264    PROCEDURE GET_CONTEXT (SESSION_ID    IN NUMBER DEFAULT NULL,
265                  USER_ID         IN NUMBER   DEFAULT NULL,
266                  NODE            IN VARCHAR2 DEFAULT NULL,
267                  NODE_IP_ADDRESS IN VARCHAR2 DEFAULT NULL,
268                  PROCESS_ID      IN VARCHAR2 DEFAULT NULL,
269                  JVM_ID          IN VARCHAR2 DEFAULT NULL,
270                  THREAD_ID       IN VARCHAR2 DEFAULT NULL,
271                  AUDSID          IN NUMBER   DEFAULT NULL,
272                  DB_INSTANCE     IN NUMBER   DEFAULT NULL,
273 		 CONTEXT_OUT     OUT NOCOPY  CONTEXT_ARRAY) is
274    begin
275       -- Populate l_context column names
276       -- Not really used right now, except to clarify.  May be helpful if
277       -- Get_Context becomes public.
278 /*
279       CONTEXT_OUT(1).a_col := 'SESSION_ID';
280       CONTEXT_OUT(2).a_col := 'USER_ID';
281       CONTEXT_OUT(3).a_col := 'NODE';
282       CONTEXT_OUT(4).a_col := 'NODE_IP_ADDRESS';
283       CONTEXT_OUT(5).a_col := 'PROCESS_ID';
284       CONTEXT_OUT(6).a_col := 'JVM_ID';
285       CONTEXT_OUT(7).a_col := 'THREAD_ID';
286       CONTEXT_OUT(8).a_col := 'AUDSID';
287       CONTEXT_OUT(9).a_col := 'DB_INSTANCE';
288       CONTEXT_OUT(10).a_col := 'TRANSACTION_CONTEXT_ID';
289       CONTEXT_OUT(11).a_col := 'SESSION_MODULE';
290       CONTEXT_OUT(12).a_col := 'SESSION_ACTION';
291 */
292 
293       if (TXN_SESSION is NULL) then
294         TXN_SESSION := userenv('SESSIONID');
295         TXN_INSTANCE := userenv('INSTANCE');
296         begin
297           select substrb(machine,1,60), process, program
298             into TXN_MACHINE, TXN_PROCESS, TXN_PROGRAM
299             from v$session
303           null;
300             where audsid = TXN_SESSION;
301         exception
302         when others then
304         end;
305       end if;
306 
307       /* 1. SESSION_ID */
308       if (SESSION_ID IS NULL) then
309         if SELF_INITED_X then
310           CONTEXT_OUT(1).a_val := SESSION_ID_X;
311         else
312           CONTEXT_OUT(1).a_val := icx_sec.g_session_id;
313         end if;
314       else
315         CONTEXT_OUT(1).a_val := SESSION_ID;
316       end if;
317 
318       /* 2. USER_ID */
319       if (USER_ID IS NULL) then
320         if SELF_INITED_X then
321           CONTEXT_OUT(2).a_val := USER_ID_X;
322         else
323           CONTEXT_OUT(2).a_val := fnd_profile.value('USER_ID');
324         end if;
325       else
326         CONTEXT_OUT(2).a_val := USER_ID;
327       end if;
328 
329       /* 3. NODE */
330       if (NODE IS NULL) then
331          CONTEXT_OUT(3).a_val := TXN_MACHINE;
332       else
333          CONTEXT_OUT(3).a_val := NODE;
334       end if;
335 
336       /* 4. NODE_IP_ADDRESS */
337       if (NODE_IP_ADDRESS IS NULL) then
338          CONTEXT_OUT(4).a_val := null;
339       else
340          CONTEXT_OUT(4).a_val := NODE_IP_ADDRESS;
341       end if;
342 
343 
344       /* 5. PROCESS_ID */
345       if (PROCESS_ID IS NULL) then
346          CONTEXT_OUT(5).a_val := TXN_PROCESS;
347       else
348          CONTEXT_OUT(5).a_val := PROCESS_ID;
349       end if;
350 
351       /* 6. JVM_ID */
352       if (JVM_ID IS NULL) then
353          if ( (INSTR(LOWER(TXN_PROGRAM), 'java') <> 0) OR
354               (INSTR(LOWER(TXN_PROGRAM),  'jre') <> 0) ) then
355             CONTEXT_OUT(6).a_val := TXN_PROCESS;
356          else
357             CONTEXT_OUT(6).a_val := null;
358          end if;
359       else
360          CONTEXT_OUT(6).a_val := JVM_ID;
361       end if;
362 
363       /* 7. THREAD_ID */
364       if (THREAD_ID IS NULL) then
365          CONTEXT_OUT(7).a_val := null;
366       else
367          CONTEXT_OUT(7).a_val := THREAD_ID;
368       end if;
369 
370       /* 8. AUDSID */
371       if (AUDSID IS NULL) then
372          CONTEXT_OUT(8).a_val := TXN_SESSION;
373       else
374          CONTEXT_OUT(8).a_val := AUDSID;
375       end if;
376 
377       /* 9. DB_INSTANCE */
378       if (DB_INSTANCE IS NULL) then
379          CONTEXT_OUT(9).a_val := TXN_INSTANCE;
380       else
381          CONTEXT_OUT(9).a_val := DB_INSTANCE;
382       end if;
383 
384       /* 10. TRANSACTION_CONTEXT_ID */
385       if (FND_LOG.G_TRANSACTION_CONTEXT_ID is NULL) THEN
386 
387         /* create a new transaction context on the fly */
388         FND_LOG.G_TRANSACTION_CONTEXT_ID := init_trans_int_with_context(
389                                                 fnd_global.conc_request_id,
390                                                 fnd_global.form_id,
391                                                 fnd_global.form_appl_id,
392                                                 fnd_global.conc_process_id,
393                                                 fnd_global.conc_queue_id,
394                                                 fnd_global.queue_appl_id,
395                                                 icx_sec.g_session_id,
396                                                 fnd_global.user_id,
397                                                 fnd_global.resp_appl_id,
398                                                 fnd_global.resp_id,
399                                                 fnd_global.security_group_id
400                                                 );
401 
402 
403       end if;
404 
405       CONTEXT_OUT(10).a_val := FND_LOG.G_TRANSACTION_CONTEXT_ID;
406 
407    end GET_CONTEXT;
408 
409    /*
410    **   GET_TRANSACTION_CONTEXT- Gathers transaction context info within
411    **   the session, before calling autonomous procedures, like
412    **   FND_LOG_REPOSITORY.Init_Transaction_Internal
413    */
414    PROCEDURE GET_TRANSACTION_CONTEXT ( SESSION_ID   IN NUMBER   DEFAULT NULL,
415                  USER_ID                     IN NUMBER   DEFAULT NULL,
416                  RESP_APPL_ID                IN NUMBER   DEFAULT NULL,
417                  RESPONSIBILITY_ID           IN NUMBER   DEFAULT NULL,
418                  SECURITY_GROUP_ID           IN NUMBER   DEFAULT NULL,
419 		 CONTEXT_OUT		     OUT NOCOPY  CONTEXT_ARRAY) is
420    begin
421       -- Populate CONTEXT_OUT column names
422       -- Not really used right now, except to clarify.  May be helpful if
423       -- Get_Transaction_Context becomes public.
424 /*
425       CONTEXT_OUT(1).a_col := 'SESSION_ID';
426       CONTEXT_OUT(2).a_col := 'USER_ID';
427       CONTEXT_OUT(3).a_col := 'RESP_APPL_ID';
428       CONTEXT_OUT(4).a_col := 'RESPONSIBILITY_ID';
429       CONTEXT_OUT(5).a_col := 'SECURITY_GROUP_ID';
430 */
431       /* 1. SESSION_ID */
432       if (SESSION_ID is NOT NULL) then
433         CONTEXT_OUT(1).a_val := SESSION_ID;
434       elsif ((SESSION_ID_X is NOT NULL) and
435           (SESSION_ID_X <> -1 )) then
436         CONTEXT_OUT(1).a_val := SESSION_ID_X;
437       else
438         CONTEXT_OUT(1).a_val := icx_sec.g_session_id;
439       end if;
440 
441 
442       /* 2. USER_ID */
443       if (USER_ID is NOT NULL) then
444         CONTEXT_OUT(2).a_val := USER_ID;
445       elsif ((USER_ID_X is NOT NULL) and
446              (USER_ID_X <> -1)) then
447         CONTEXT_OUT(2).a_val := USER_ID_X;
448       elsif (FND_GLOBAL.user_id is NOT NULL) then
449         CONTEXT_OUT(2).a_val := FND_GLOBAL.user_id;
450       else
451         CONTEXT_OUT(2).a_val := -1;
455       if (RESP_APPL_ID is NOT NULL) then
452       end if;
453 
454       /* 3. RESP_APPL_ID */
456         CONTEXT_OUT(3).a_val := RESP_APPL_ID;
457       else
458          CONTEXT_OUT(3).a_val := FND_GLOBAL.resp_appl_id;
459       end if;
460 
461       /* 4. RESP_ID */
462       if (RESPONSIBILITY_ID is NOT NULL) then
463         CONTEXT_OUT(4).a_val := RESPONSIBILITY_ID;
464       else
465         CONTEXT_OUT(4).a_val := FND_GLOBAL.resp_id;
466       end if;
467 
468       /* 5. SECURITY_GROUP_ID */
469       if (SECURITY_GROUP_ID is NOT NULL) then
470         CONTEXT_OUT(5).a_val := SECURITY_GROUP_ID;
471       else
472         CONTEXT_OUT(5).a_val := FND_GLOBAL.security_group_id;
473       end if;
474 
475    end GET_TRANSACTION_CONTEXT;
476 
477    /*
478    ** SELF_INIT- Initialize the logging system automatically.  Note that
479    ** in the case of Forms initialization, this routine does not obviate the
480    ** need for the INIT routine to be called by AOL, because when the
481    ** code is self inited, the user/resp context may not exist (so only site
482    ** level profiles would exist).  This self initialization will allow the
483    ** site level profiles to be acted upon, and later the actual
484    ** INIT call will allow the user level profiles to control logging.
485    */
486    PROCEDURE SELF_INIT  is
487    begin
488       INIT;
489       SELF_INITED_X := TRUE;
490    end;
491 
492 
493    /*
494    **  Determines whether logging is enabled or disabled for this module
495    **  and level.
496    */
497    function CHECK_ACCESS_INTERNAL(MODULE_IN IN VARCHAR2,
498                                   LEVEL_IN  IN NUMBER) return BOOLEAN is
499    begin
500       if(NOT SELF_INITED_X) then
501          SELF_INIT;
502       end if;
503 
504       if (NOT AFLOG_ENABLED_X) then
505          return FALSE;
506       end if;
507       if (LEVEL_IN < AFLOG_LEVEL_X) then
508          return FALSE;
509       end if;
510       if(TABLE_SIZE = 0) then
511          return TRUE;  /* If no module is specified, log for all modules*/
512       end if;
513       for IDX in 1..TABLE_SIZE loop
514          if UPPER(MODULE_IN) like MODULE_TAB(IDX)  then
515             return TRUE;
516          end if;
517       end loop;
518       return FALSE;
519    end;
520 
521    /*
522    **  Private - for ATG only.
523    **  POST_EXCEPTION
524    **  Description:
525    **  Inserts extended exception information into FND_LOG_EXCEPTIONS and
526    **  posts the exception / unexpected error to the Business Event System
527    **
528    **  Arguments:
529    **      Module      - Module name (See FND_LOG standards)
530    **      Message_Id  - The unique identifier of the message from
531    **                    FND_LOG_MESSAGES.Log_Sequence
532    */
533    FUNCTION POST_EXCEPTION ( P_MODULE                 IN VARCHAR2,
534                              P_LOG_SEQUENCE           IN NUMBER,
535                              P_MESSAGE_APP            IN VARCHAR2 DEFAULT NULL,
536                              P_MESSAGE_NAME           IN VARCHAR2 DEFAULT NULL)
537                                                         return BOOLEAN is
538        l_msg_text           varchar2(2000);
539        l_enc_msg            varchar2(2000) := null;
540        l_msg_app            varchar2(50);
541        l_msg_name           varchar2(30);
542        l_base_lang          varchar2(4);  -- Base language_code for install
543        l_msg_cat            varchar2(10); -- Message category
544        l_msg_sev            varchar2(10); -- Message severity
545        l_cur_lang           varchar2(64);
546        l_cur_date_lang      varchar2(64);
547        l_cur_sort           varchar2(64);
548        l_ex_id              number;
549        l_txn_id             number;
550        l_session_action     varchar2(32);
551        l_session_module     varchar2(48);
552        l_is_new_alert       boolean := false;
553        l_occ_count         number;
554        l_max_occ_count     number;
555        l_transaction_type varchar2(30);
556 
557    pragma AUTONOMOUS_TRANSACTION;
558 
559    begin
560 
561         select MESSAGE_TEXT, TRANSACTION_CONTEXT_ID
562             into l_enc_msg, l_txn_id
563             from FND_LOG_MESSAGES
564             where LOG_SEQUENCE = P_LOG_SEQUENCE;
565 
566 
567 
568         --8609702
569         begin
570           select transaction_type
571           into l_transaction_type
572           from fnd_log_transaction_context
573           where transaction_context_id = l_txn_id;
574 
575           if (l_transaction_type = 'UNKNOWN') then
576             return false;
577           end if;
578 
579         exception
580           when others then return false;
581         end;
582 
583 
584 
585 
586 
587 
588         if ((P_MESSAGE_APP IS NOT NULL) and
589             (P_MESSAGE_NAME IS NOT NULL)) then
590             l_msg_app := P_MESSAGE_APP;
591             l_msg_name := P_MESSAGE_NAME;
592         else
593           FND_MESSAGE.PARSE_ENCODED(l_enc_msg,
594                                     l_msg_app,
595                                     l_msg_name);
596         end if;
597 
598 		l_msg_name := UPPER(l_msg_name);
599 
600         select LANGUAGE_CODE
601           into l_base_lang
602           from FND_LANGUAGES
603           where INSTALLED_FLAG = 'B';
604 
605         /**
606          * Added Check for Proxy Alerting. If child context is set use
607          * sesion module and action for child instead of current session.
608          */
612             from v$session
609         if (G_PRX_CHILD_TRANS_CONTEXT_ID is null) then
610           select module, action
611             into l_session_module, l_session_action
613 	    where audsid = userenv('SESSIONID');
614 	else
615 	  l_session_module := G_PRX_SESSION_MODULE;
616 	  l_session_action := G_PRX_SESSION_ACTION;
617       	end if;
618 
619         begin
620           select CATEGORY, SEVERITY
621             into l_msg_cat, l_msg_sev
622             from FND_NEW_MESSAGES fnm,
623                  FND_APPLICATION  fa
624            where fnm.APPLICATION_ID = fa.APPLICATION_ID
625              and fa.APPLICATION_SHORT_NAME = l_msg_app
626              and fnm.MESSAGE_NAME = l_msg_name
627              and fnm.LANGUAGE_CODE = l_base_lang;
628         exception
629           when others then
630              FND_MESSAGE.SET_NAME ('FND', 'SQL-Generic error');
631              FND_MESSAGE.SET_TOKEN ('ERRNO', sqlcode, FALSE);
632              FND_MESSAGE.SET_TOKEN ('REASON', sqlerrm, FALSE);
633              FND_MESSAGE.SET_TOKEN ('ROUTINE',
634                                 'FND_LOG_REPOSITORY.POST_EXCEPTION', FALSE);
635 
636              rollback;
637              return FALSE;
638         end;
639 
640 	if ((l_msg_cat IS NULL) or (l_msg_sev IS NULL) or (IS_ALERTING_ENABLED(l_msg_sev) = FALSE)) THEN
641            rollback;
642            return FALSE;
643         end if;
644 
645 
646         /* Here we need to  insert the translated message text into MESSAGE_TEXT        */
647         /* First we will save the current language, then switch our session to English, */
648         /* retrieve the English message text, then switch back to the original language */
649         select value
650          into l_cur_lang
651          from v$nls_parameters
652          where parameter = 'NLS_LANGUAGE';
653         select value
654          into l_cur_date_lang
655          from v$nls_parameters
656          where parameter = 'NLS_DATE_LANGUAGE';
657         select value
658          into l_cur_sort
659          from v$nls_parameters
660          where parameter = 'NLS_SORT';
661 
662         dbms_session.set_nls('NLS_LANGUAGE', 'AMERICAN');
663 
664         fnd_message.set_encoded(l_enc_msg);
665 
666         l_msg_text := fnd_message.get;
667 
668         dbms_session.set_nls('NLS_LANGUAGE', '"' || l_cur_lang || '"');
669         dbms_session.set_nls('NLS_DATE_LANGUAGE', '"' || l_cur_date_lang || '"');
670         dbms_session.set_nls('NLS_SORT', '"' || l_cur_sort || '"');
671 
672 
673         /* Unique exception enhancement: Check fnd_log_unique_exceptions table for a row */
674         /* with this same message. If one exists already, increment the exception count  */
675         /* If this is the first one, insert a new row into fnd_log_unique_exceptions     */
676         begin
677           l_is_new_alert := false;
678           select unique_exception_id, count
679             into l_ex_id, l_occ_count
680             from fnd_log_unique_exceptions
681             where encoded_message = l_enc_msg
682             and status in ('N', 'O');
683 
684           /** Check if limit for occrrences has been reached **/
685           l_max_occ_count := fnd_profile.value('OAM_MAX_OCCURRENCES_PER_ALERT');
686           if(l_occ_count >= l_max_occ_count) then
687              rollback;
688              DEBUG('Not Logging occ l_occ_count = l_max_occ_count'|| l_max_occ_count);
689              return FALSE;
690           end if;
691 
692           update fnd_log_unique_exceptions flue
693              set flue.count = flue.count + 1
694              where flue.unique_exception_id = l_ex_id;
695 
696         exception
697           when no_data_found then
698             select fnd_log_unique_exception_s.nextval
699               into l_ex_id
700               from dual;
701 
702 		insert into fnd_log_unique_exceptions (
703 		  UNIQUE_EXCEPTION_ID,
704 		  ENCODED_MESSAGE,
705 		  ENGLISH_MESSAGE,
706 		  STATUS,
707 		  COUNT,
708 		  SEVERITY,
709 		  CATEGORY,
710 		  CREATED_BY,
711 		  CREATION_DATE,
712 		  LAST_UPDATED_BY,
713 		  LAST_UPDATE_DATE,
714 		  LAST_UPDATE_LOGIN
715 		 )
716            values (
717               l_ex_id,
718               l_enc_msg,
719               l_msg_text,
720               'N',
721               1,
722               l_msg_sev,
723               l_msg_cat,
724               USER_ID_X,
725               sysdate,
726               USER_ID_X,
727               sysdate,
728               USER_ID_X);
729           l_is_new_alert := true;
730            DEBUG('Logged Alert'|| l_ex_id);
731 
732         end;
733 
734 
735         /* Log extended exception information in FND_LOG_EXCEPTIONS */
736         insert into FND_LOG_EXCEPTIONS (
737            LOG_SEQUENCE,
738            SESSION_MODULE,
739            SESSION_ACTION,
740            UNIQUE_EXCEPTION_ID,
741            ACKNOWLEDGED,
742 	       MESSAGE_TEXT,
743 	       TRANSACTION_CONTEXT_ID
744         ) values
745         (
746            P_LOG_SEQUENCE,
747            substrb(l_session_module,1,48),
748            substrb(l_session_action,1,32),
749            l_ex_id,
750            'N',
751 	       l_msg_text,
752 	       l_txn_id
753         );
754 
755         DEBUG('Logging occ P_LOG_SEQUENCE' || P_LOG_SEQUENCE);
756 
757         /* Always Post exception to Business Event System */
758         WF_EVENT.RAISE('oracle.apps.fnd.system.exception',
759                          to_char(P_LOG_SEQUENCE) );
760 
761         commit;
762         return TRUE;
763 
764 
768              FND_MESSAGE.SET_TOKEN ('ERRNO', sqlcode, FALSE);
765    exception
766           when others then
767              FND_MESSAGE.SET_NAME ('FND', 'SQL-Generic error');
769              FND_MESSAGE.SET_TOKEN ('REASON', sqlerrm, FALSE);
770              FND_MESSAGE.SET_TOKEN ('ROUTINE',
771                                  'FND_LOG_REPOSITORY.POST_EXCEPTION', FALSE);
772          rollback;
773          return FALSE;
774 
775    end POST_EXCEPTION;
776 
777    /*
778    **  Writes the message to the log file for the spec'd level and module
779    **  without checking if logging is enabled at this level.  This
780    **  routine is only to be called from the AOL implementations of
781    **  the AFLOG interface, in languages like JAVA or C.
782    **  If the SESSION_ID and/or USER_ID is not passed, it defaults to the
783    **  value that was passed upon INIT.
784    */
785    PROCEDURE STRING_UNCHECKED_INTERNAL(LOG_LEVEL IN NUMBER,
786                     MODULE        IN VARCHAR2,
787                     MESSAGE_TEXT  IN VARCHAR2,
788                     SESSION_ID    IN NUMBER   DEFAULT NULL,
789                     USER_ID       IN NUMBER   DEFAULT NULL,
790                     CALL_STACK    IN VARCHAR2 DEFAULT NULL,
791                     ERR_STACK     IN VARCHAR2 DEFAULT NULL) is
792 
793       SESSION_ID_Z  NUMBER;
794       USER_ID_Z     NUMBER;
795 
796    pragma AUTONOMOUS_TRANSACTION;
797    begin
798       if(NOT SELF_INITED_X) then
799          SELF_INIT;
800       end if;
801 
802       if (SESSION_ID is not NULL) then
803          SESSION_ID_Z := SESSION_ID;
804       else
805          SESSION_ID_Z := SESSION_ID_X;
806       end if;
807 
808       if (USER_ID is not NULL) then
809          USER_ID_Z := USER_ID;
810       else
811          USER_ID_Z := USER_ID_X;
812       end if;
813 
814       if (AFLOG_FILENAME_X is not NULL) then
815          null; /* Eventually we will want to add code that will log to a */
816                /* file if they set the filename, but for now we will just */
817 	       /* log to table */
818       end if;
819 
820 
821       INSERT INTO FND_LOG_MESSAGES (
822            ECID_ID,
823            ECID_SEQ,
824            CALLSTACK,
825            ERRORSTACK,
826            MODULE,
827            LOG_LEVEL,
828            MESSAGE_TEXT,
829            SESSION_ID,
830            USER_ID,
831            TIMESTAMP,
832            LOG_SEQUENCE
833       ) values
834       (
835            SYS_CONTEXT('USERENV', 'ECID_ID'),
836            SYS_CONTEXT('USERENV', 'ECID_SEQ'),
837            CALL_STACK,
838            ERR_STACK,
839            SUBSTRB(MODULE,1,255),
840            LOG_LEVEL,
841            SUBSTRB(MESSAGE_TEXT, 1, 4000),
842            SESSION_ID_Z,
843            USER_ID_Z,
844            SYSDATE,
845            FND_LOG_MESSAGES_S.NEXTVAL
846       );
847 
848       commit;
849    end;
850 
851    /* Clears in memory buffered messages */
852    PROCEDURE DELETE_BUFFERED_TABLES is
853    begin
854            TIMESTAMP_TABLE.delete;
855            LOG_SEQUENCE_TABLE.delete;
856            MODULE_TABLE.delete;
857            LOG_LEVEL_TABLE.delete;
858            MESSAGE_TEXT_TABLE.delete;
859            SESSION_ID_TABLE.delete;
860            USER_ID_TABLE.delete;
861            ENCODED_TABLE.delete;
862            THREAD_ID_TABLE.delete;
863            AUDSID_TABLE.delete;
864            DB_INSTANCE_TABLE.delete;
865            TRANSACTION_CONTEXT_ID_TABLE.delete;
866    end;
867 
868    /* Flushes any buffered messages */
869    FUNCTION FLUSH return NUMBER is
870    l_log_seq NUMBER := NULL;
871    begin
872        if (G_BUFFER_POS > 1) then
873            l_log_seq := BULK_INSERT_PVT(MODULE_TABLE,
874                                 LOG_LEVEL_TABLE,
875                                 MESSAGE_TEXT_TABLE,
876                                 SESSION_ID_TABLE,
877                                 USER_ID_TABLE,
878                                 TIMESTAMP_TABLE,
879                                 LOG_SEQUENCE_TABLE,
880                                 ENCODED_TABLE,
881                                 NODE_TABLE,
882                                 NODE_IP_ADDRESS_TABLE,
883                                 PROCESS_ID_TABLE,
884                                 JVM_ID_TABLE,
885                                 THREAD_ID_TABLE,
886                                 AUDSID_TABLE,
887                                 DB_INSTANCE_TABLE,
888                                 TRANSACTION_CONTEXT_ID_TABLE,
889                                 (G_BUFFER_POS - 1) );
890 
891            l_log_seq := LOG_SEQUENCE_TABLE(G_BUFFER_POS - 1);
892 
893            DELETE_BUFFERED_TABLES;
894            G_BUFFER_POS := 1;
895         end if;
896            return l_log_seq;
897         exception
898           when others then
899             AFLOG_BUFFER_MODE_X := 0;
900             G_BUFFER_POS := 1;
901             DELETE_BUFFERED_TABLES;
902             STR_UNCHKED_INT_WITH_CONTEXT(6, 'fnd.plsql.fnd_log_repository', 'Buffered Logging Failed! ' ||
903                                             'Please report to Oracle Development. sqlcode=' || sqlcode ||
904                                             '; sqlerrm=' || sqlerrm);
905         return l_log_seq;
906    end FLUSH;
907 
908    /*
909    ** Private- Flushes any buffered messages, and resets to non-buffered mode
910    */
911    PROCEDURE RESET_BUFFERED_MODE is
912      l_count NUMBER := 0;
913    begin
914      l_count := FLUSH;
915      AFLOG_BUFFER_MODE_X := 0;
916    end RESET_BUFFERED_MODE;
917 
921    PROCEDURE SET_BUFFERED_MODE is
918    /*
919    ** Enables buffered mode based on AFLOG_BUFFER_MODE Profile
920    */
922      l_buffer_size NUMBER := NULL;
923      l_buffer_mode NUMBER := NULL;
924    begin
925 
926     if ( AFLOG_ENABLED_X = TRUE) then
927 
928       l_buffer_size := TO_NUMBER(FND_PROFILE.VALUE('AFLOG_BUFFER_SIZE'));
929       l_buffer_mode := TO_NUMBER(FND_PROFILE.VALUE('AFLOG_BUFFER_MODE'));
930 
931       if ( l_buffer_size > -1 ) then
932          AFLOG_BUFFER_SIZE_X := l_buffer_size;
933       end if;
934 
935       if ( l_buffer_mode > -1 ) then
936          AFLOG_BUFFER_MODE_X := l_buffer_mode;
937       end if;
938 
939       if ( AFLOG_BUFFER_MODE_X > 0 ) then
940 	MODULE_TABLE := FND_TABLE_OF_VARCHAR2_255();
941 	LOG_LEVEL_TABLE := FND_TABLE_OF_NUMBER();
942 	MESSAGE_TEXT_TABLE := FND_TABLE_OF_VARCHAR2_4000();
943 	SESSION_ID_TABLE := FND_TABLE_OF_NUMBER();
944 	USER_ID_TABLE := FND_TABLE_OF_NUMBER();
945 	TIMESTAMP_TABLE := FND_TABLE_OF_DATE();
946 	LOG_SEQUENCE_TABLE := FND_TABLE_OF_NUMBER();
947 	ENCODED_TABLE := FND_TABLE_OF_VARCHAR2_1();
948 	THREAD_ID_TABLE := FND_TABLE_OF_VARCHAR2_120();
949 	AUDSID_TABLE := FND_TABLE_OF_NUMBER();
950 	DB_INSTANCE_TABLE := FND_TABLE_OF_NUMBER();
951 	TRANSACTION_CONTEXT_ID_TABLE := FND_TABLE_OF_NUMBER();
952       end if;
953 
954     end if;
955    end SET_BUFFERED_MODE;
956 
957    /*
958    **  Private -- Should only be called by STR_UNCHKED_INT_WITH_CONTEXT
959    **  Writes the message to the log file for the spec'd level and module
960    **  without checking if logging is enabled at this level.
961    */
962    FUNCTION STRING_UNCHECKED_INTERNAL2(LOG_LEVEL IN NUMBER,
963                     MODULE          IN VARCHAR2,
964                     MESSAGE_TEXT    IN VARCHAR2,
965                     LOG_SEQUENCE    IN NUMBER,
966                     ENCODED         IN VARCHAR2 DEFAULT 'N',
967                     SESSION_ID      IN NUMBER   DEFAULT NULL,
968                     USER_ID         IN NUMBER,
969                     NODE            IN VARCHAR2 DEFAULT NULL,
970                     NODE_IP_ADDRESS IN VARCHAR2 DEFAULT NULL,
971                     PROCESS_ID      IN VARCHAR2 DEFAULT NULL,
972                     JVM_ID          IN VARCHAR2 DEFAULT NULL,
973                     THREAD_ID       IN VARCHAR2 DEFAULT NULL,
974                     AUDSID          IN NUMBER   DEFAULT NULL,
975                     DB_INSTANCE     IN NUMBER   DEFAULT NULL,
976                     TRANSACTION_CONTEXT_ID IN NUMBER DEFAULT NULL,
977                     CALL_STACK      IN VARCHAR2 DEFAULT NULL,
978                     ERR_STACK       IN VARCHAR2 DEFAULT NULL) return NUMBER is
979    pragma AUTONOMOUS_TRANSACTION;
980    l_log_seq NUMBER := NULL;
981    cur_time DATE := NULL;
982    first_buf_time DATE := NULL;
983 
984    begin
985 
986    /* Only buffer log_level < 4 message, i.e. no error messages */
987    if (AFLOG_BUFFER_MODE_X > 0 and LOG_LEVEL < 4) then
988 
989      if (G_BUFFER_POS > TIMESTAMP_TABLE.COUNT) then
990        TIMESTAMP_TABLE.extend(AFLOG_BUFFER_SIZE_X);
991        LOG_SEQUENCE_TABLE.extend(AFLOG_BUFFER_SIZE_X);
992        MODULE_TABLE.extend(AFLOG_BUFFER_SIZE_X);
993        LOG_LEVEL_TABLE.extend(AFLOG_BUFFER_SIZE_X);
994        MESSAGE_TEXT_TABLE.extend(AFLOG_BUFFER_SIZE_X);
995        SESSION_ID_TABLE.extend(AFLOG_BUFFER_SIZE_X);
996        USER_ID_TABLE.extend(AFLOG_BUFFER_SIZE_X);
997        ENCODED_TABLE.extend(AFLOG_BUFFER_SIZE_X);
998        THREAD_ID_TABLE.extend(AFLOG_BUFFER_SIZE_X);
999        AUDSID_TABLE.extend(AFLOG_BUFFER_SIZE_X);
1000        DB_INSTANCE_TABLE.extend(AFLOG_BUFFER_SIZE_X);
1001        TRANSACTION_CONTEXT_ID_TABLE.extend(AFLOG_BUFFER_SIZE_X);
1002      end if;
1003 
1004      /* This is the default Sequenced mode for AFLOG_BUFFER_MODE */
1005      /* if ( AFLOG_BUFFER_MODE_X = 2 ) then */
1006      -- Better to always do this so log_level>=4 messages are in sequence
1007           select FND_LOG_MESSAGES_S.NEXTVAL
1008             into  LOG_SEQUENCE_TABLE(G_BUFFER_POS)
1009             from dual;
1010      /* end if; */
1011 
1012        TIMESTAMP_TABLE(G_BUFFER_POS) := SYSDATE;
1013        MODULE_TABLE(G_BUFFER_POS) := MODULE;
1014        LOG_LEVEL_TABLE(G_BUFFER_POS) := LOG_LEVEL;
1015        MESSAGE_TEXT_TABLE(G_BUFFER_POS) := MESSAGE_TEXT;
1016        SESSION_ID_TABLE(G_BUFFER_POS) := SESSION_ID;
1017        USER_ID_TABLE(G_BUFFER_POS) := USER_ID;
1018        ENCODED_TABLE(G_BUFFER_POS) := ENCODED;
1019 
1020        NODE_TABLE := NODE;
1021        NODE_IP_ADDRESS_TABLE := NODE_IP_ADDRESS;
1022        PROCESS_ID_TABLE := PROCESS_ID;
1023        JVM_ID_TABLE := JVM_ID;
1024 
1025        THREAD_ID_TABLE(G_BUFFER_POS) := THREAD_ID;
1026        AUDSID_TABLE(G_BUFFER_POS) := AUDSID;
1027        DB_INSTANCE_TABLE(G_BUFFER_POS) := DB_INSTANCE;
1028        TRANSACTION_CONTEXT_ID_TABLE(G_BUFFER_POS) := TRANSACTION_CONTEXT_ID;
1029        G_BUFFER_POS := G_BUFFER_POS + 1;
1030 
1031        /* Flush if buffering for > 5 mins
1032        if (G_BUFFER_POS > 1) then
1033          cur_time := SYSDATE;
1034          first_buf_time := TIMESTAMP_TABLE(1);
1035 	 if ( ((cur_time - first_buf_time)*24*60) > 5) then
1036 	   l_log_seq := FLUSH;
1037          end if;
1038        end if;
1039        */
1040 
1041        /* Flush if buffer >  AFLOG_BUFFER_SIZE_X */
1042        if (G_BUFFER_POS > AFLOG_BUFFER_SIZE_X) then
1043    	   l_log_seq := FLUSH;
1044        end if;
1045    else
1046 
1047 
1048       INSERT INTO FND_LOG_MESSAGES (
1049 	 ECID_ID,
1050          ECID_SEQ,
1051          CALLSTACK,
1052          ERRORSTACK,
1053          MODULE,
1054          LOG_LEVEL,
1055          MESSAGE_TEXT,
1056          SESSION_ID,
1057          USER_ID,
1058 	 TIMESTAMP,
1062          NODE_IP_ADDRESS,
1059          LOG_SEQUENCE,
1060          ENCODED,
1061          NODE,
1063          PROCESS_ID,
1064          JVM_ID,
1065          THREAD_ID,
1066          AUDSID,
1067          DB_INSTANCE,
1068          TRANSACTION_CONTEXT_ID
1069       ) values
1070       (
1071 	 SYS_CONTEXT('USERENV', 'ECID_ID'),
1072          SYS_CONTEXT('USERENV', 'ECID_SEQ'),
1073          CALL_STACK,
1074 	 ERR_STACK,
1075          SUBSTRB(MODULE,1,255),
1076          LOG_LEVEL,
1077          SUBSTRB(MESSAGE_TEXT, 1, 4000),
1078          SESSION_ID,
1079          nvl(USER_ID, -1),
1080 	 SYSDATE,
1081          FND_LOG_MESSAGES_S.NEXTVAL,
1082          ENCODED,
1083          substrb(NODE,1,60),
1084          substrb(NODE_IP_ADDRESS,1,30),
1085          substrb(PROCESS_ID,1,120),
1086          substrb(JVM_ID,1,120),
1087          substrb(THREAD_ID,1,120),
1088          AUDSID,
1089          DB_INSTANCE,
1090          TRANSACTION_CONTEXT_ID
1091       ) returning log_sequence into l_log_seq;
1092       commit;
1093    end if;
1094       return l_log_seq;
1095    end;
1096 
1097    /*
1098    **  Gathers context information within the same session, then
1099    **  calls the private, autonmous procedure STRING_UNCHECKED_INTERNAL2,
1100    **  passing context information to be logged in AFLOG tables
1101    **
1102    **  A wrapper API that calls String_Unchecked_Internal2 using the
1103    **  context values from internal cache of the context values.
1104    **  This routine is only to be called from the AOL implementations of
1105    **  the AFLOG interface, in languages like JAVA or C.
1106    **  If the SESSION_ID and/or USER_ID is not passed, it defaults to the
1107    **  value that was passed upon INIT.
1108    **  (NOTE: Recommend use FUNCTION STR_UNCHKED_INT_WITH_CONTEXT(..) instead
1109    */
1110    PROCEDURE STR_UNCHKED_INT_WITH_CONTEXT(LOG_LEVEL IN NUMBER,
1111                     MODULE          IN VARCHAR2,
1112                     MESSAGE_TEXT    IN VARCHAR2,
1113                     ENCODED         IN VARCHAR2 DEFAULT 'N',
1114                     SESSION_ID      IN NUMBER   DEFAULT NULL,
1115                     USER_ID         IN NUMBER   DEFAULT NULL,
1116                     NODE            IN VARCHAR2 DEFAULT NULL,
1117                     NODE_IP_ADDRESS IN VARCHAR2 DEFAULT NULL,
1118                     PROCESS_ID      IN VARCHAR2 DEFAULT NULL,
1119                     JVM_ID          IN VARCHAR2 DEFAULT NULL,
1120                     THREAD_ID       IN VARCHAR2 DEFAULT NULL,
1121                     AUDSID          IN NUMBER   DEFAULT NULL,
1122                     DB_INSTANCE     IN NUMBER   DEFAULT NULL,
1123                     CALL_STACK      IN VARCHAR2 DEFAULT NULL,
1124                     ERR_STACK       IN VARCHAR2 DEFAULT NULL) is
1125      l_seq NUMBER;
1126    begin
1127      l_seq := STR_UNCHKED_INT_WITH_CONTEXT(LOG_LEVEL,
1128                     MODULE,
1129                     MESSAGE_TEXT,
1130                     ENCODED,
1131                     SESSION_ID,
1132                     USER_ID,
1133                     NODE,
1134                     NODE_IP_ADDRESS,
1135                     PROCESS_ID,
1136                     JVM_ID,
1137                     THREAD_ID,
1138                     AUDSID,
1139                     DB_INSTANCE,
1140                     CALL_STACK,
1141                     ERR_STACK
1142 	      );
1143    end;
1144 
1145    /*
1146    **  Gathers context information within the same session, then
1147    **  calls the private, autonmous procedure STRING_UNCHECKED_INTERNAL2,
1148    **  passing context information to be logged in AFLOG tables
1149    **
1150    **  A wrapper API that calls String_Unchecked_Internal2 using the
1151    **  context values from internal cache of the context values.
1152    **  This routine is only to be called from the AOL implementations of
1153    **  the AFLOG interface, in languages like JAVA or C.
1154    **  If the SESSION_ID and/or USER_ID is not passed, it defaults to the
1155    **  value that was passed upon INIT.
1156    **
1157    **  Returns the log_sequence of the logged message- needed for Attachments
1158    */
1159    FUNCTION STR_UNCHKED_INT_WITH_CONTEXT(LOG_LEVEL IN NUMBER,
1160                     MODULE          IN VARCHAR2,
1161                     MESSAGE_TEXT    IN VARCHAR2,
1162                     ENCODED         IN VARCHAR2 DEFAULT 'N',
1163                     SESSION_ID      IN NUMBER   DEFAULT NULL,
1164                     USER_ID         IN NUMBER   DEFAULT NULL,
1165                     NODE            IN VARCHAR2 DEFAULT NULL,
1166                     NODE_IP_ADDRESS IN VARCHAR2 DEFAULT NULL,
1167                     PROCESS_ID      IN VARCHAR2 DEFAULT NULL,
1168                     JVM_ID          IN VARCHAR2 DEFAULT NULL,
1169                     THREAD_ID       IN VARCHAR2 DEFAULT NULL,
1170                     AUDSID          IN NUMBER   DEFAULT NULL,
1171                     DB_INSTANCE     IN NUMBER   DEFAULT NULL,
1172                     CALL_STACK      IN VARCHAR2 DEFAULT NULL,
1173                     ERR_STACK       IN VARCHAR2 DEFAULT NULL) RETURN NUMBER is
1174       l_context  CONTEXT_ARRAY;
1175       l_encoded  VARCHAR2(1) := null;
1176       l_log_sequence  number;
1177       l_posted        boolean := FALSE;
1178       l_module   varchar2(256);
1179    begin
1180 
1181       /* check for null values */
1182       if message_text is null then
1183          return -1;
1184       end if;
1185 
1186       if module is null then
1187          l_module := 'MODULE_UNKNOWN';
1188       else
1189          l_module := MODULE;
1190       end if;
1191 
1192       if log_level is null then
1193         return -1;
1194       end if;
1195 
1196 
1197       if(NOT SELF_INITED_X) then
1198          SELF_INIT;
1199       end if;
1203                    USER_ID         => USER_ID,
1200 
1201       GET_CONTEXT (
1202                    SESSION_ID      => SESSION_ID,
1204                    NODE            => NODE,
1205                    NODE_IP_ADDRESS => NODE_IP_ADDRESS,
1206                    PROCESS_ID      => PROCESS_ID,
1207                    JVM_ID          => JVM_ID,
1208                    THREAD_ID       => THREAD_ID,
1209                    AUDSID          => AUDSID,
1210                    DB_INSTANCE     => DB_INSTANCE,
1211 		   CONTEXT_OUT     => l_context);
1212 
1213       if (upper(ENCODED) in ('Y', 'N')) then
1214          l_encoded := ENCODED;
1215       else
1216          l_encoded := 'N';
1217       end if;
1218 
1219       if (AFLOG_FILENAME_X is not NULL) then
1220          null; /* Eventually we will want to add code that will log to a */
1221                /* file if they set the filename, but for now we will just */
1222                /* log to table */
1223       end if;
1224 
1225       /**
1226        * Added for proxy alerting. Check if child context is set. If yes,
1227        * use the child transaction context Id.
1228        */
1229       if (G_PRX_CHILD_TRANS_CONTEXT_ID is not null) then
1230 	if G_PRX_MODULE is null then
1231           l_module := 'MODULE_UNKNOWN';
1232         else
1233           l_module := G_PRX_MODULE;
1234         end if;
1235         l_context(1).a_val := G_PRX_SESSION_ID;
1236         l_context(2).a_val := G_PRX_USER_ID;
1237 	l_context(3).a_val := G_PRX_NODE;
1238         l_context(4).a_val := G_PRX_NODE_IP_ADDRESS;
1239         l_context(5).a_val := G_PRX_PROCESS_ID;
1240         l_context(6).a_val := G_PRX_JVM_ID;
1241         l_context(7).a_val := G_PRX_THREAD_ID;
1242 	if (G_PRX_AUDSID is null) then
1243 	  l_context(8).a_val := -1;
1244 	else
1245   	  l_context(8).a_val := G_PRX_AUDSID;
1246 	end if;
1247         l_context(9).a_val := G_PRX_DB_INSTANCE;
1248 	l_context(10).a_val := G_PRX_CHILD_TRANS_CONTEXT_ID;
1249       end if;
1250 
1251       l_log_sequence := STRING_UNCHECKED_INTERNAL2(LOG_LEVEL => LOG_LEVEL,
1252                     MODULE          => l_module,
1253                     MESSAGE_TEXT    => MESSAGE_TEXT,
1254                     LOG_SEQUENCE    => l_log_sequence,
1255                     ENCODED         => l_encoded,
1256                     SESSION_ID      => to_number(l_context(1).a_val),
1257                     USER_ID         => to_number(l_context(2).a_val),
1258                     NODE            => l_context(3).a_val,
1259                     NODE_IP_ADDRESS => l_context(4).a_val,
1260                     PROCESS_ID      => l_context(5).a_val,
1261                     JVM_ID          => l_context(6).a_val,
1262                     THREAD_ID       => l_context(7).a_val,
1263                     AUDSID          => l_context(8).a_val,
1264                     DB_INSTANCE     => l_context(9).a_val,
1265                     TRANSACTION_CONTEXT_ID => l_context(10).a_val,
1266                     CALL_STACK      => CALL_STACK,
1267                     ERR_STACK       => ERR_STACK);
1268 
1269       /* Unexpected errors are posted as exceptions */
1270       if (l_encoded = 'Y') and (LOG_LEVEL = FND_LOG.LEVEL_UNEXPECTED) then
1271          l_posted :=  POST_EXCEPTION(P_MODULE         => l_module,
1272                                      P_LOG_SEQUENCE   => l_log_sequence);
1273       end if;
1274 
1275       return l_log_sequence;
1276 
1277       end;
1278 
1279    /**
1280     * Inserts a empty BLOB for the P_LOG_SEQUENCE
1281     */
1282    PROCEDURE INSERT_BLOB(P_LOG_SEQUENCE IN NUMBER, PCHARSET IN VARCHAR2,
1283 		PMIMETYPE IN VARCHAR2, PENCODING IN VARCHAR2, PLANG IN VARCHAR2,
1284 		PFILE_EXTN IN VARCHAR2, PDESC IN VARCHAR2) is
1285    pragma AUTONOMOUS_TRANSACTION;
1286      begin
1287           INSERT INTO FND_LOG_ATTACHMENTS fla
1288           (
1289                 LOG_SEQUENCE,
1290 		CHARSET,
1291 		MIMETYPE,
1292 		ENCODING,
1293 		LANGUAGE,
1294 		FILE_EXTN,
1295 		DESCRIPTION,
1296                 CONTENT
1297           ) values
1298           (
1299                 P_LOG_SEQUENCE,
1300 		PCHARSET,
1301 		PMIMETYPE,
1302 		PENCODING,
1303 		PLANG,
1304 		PFILE_EXTN,
1305 		PDESC,
1306                 EMPTY_BLOB()
1307           );
1308           commit;
1309      end;
1310 
1311    /**
1312     * For AOL/J Internal use ONLY!
1313     * Returns a BLOB for the P_LOG_SEQUENCE
1314     *
1315     * Called from Client and Server PL/SQL
1316     */
1317    PROCEDURE GET_BLOB_INTERNAL(P_LOG_SEQUENCE IN NUMBER,
1318                         LOG_BLOB OUT NOCOPY BLOB,
1319                         P_CHARSET IN VARCHAR2 DEFAULT 'ascii',
1320                         P_MIMETYPE IN VARCHAR2 DEFAULT 'text/html',
1321                         P_ENCODING IN VARCHAR2 DEFAULT NULL,
1322                         P_LANG IN VARCHAR2 DEFAULT NULL,
1323                         P_FILE_EXTN IN VARCHAR2 DEFAULT 'txt',
1324 			P_DESC IN VARCHAR2 DEFAULT NULL) is
1325       l_log_sequence NUMBER := -1;
1326    begin
1327       if ( P_LOG_SEQUENCE is NULL or P_LOG_SEQUENCE < 0 ) then
1328          LOG_BLOB := NULL;
1329          return;
1330       end if;
1331 
1332       select content
1333         into LOG_BLOB
1334         from FND_LOG_ATTACHMENTS fla
1335         where fla.log_sequence = P_LOG_SEQUENCE for UPDATE;
1336       return;
1337 
1338       EXCEPTION
1339   	WHEN NO_DATA_FOUND THEN
1340         begin
1341 	  select flm.log_sequence
1342             into l_log_sequence
1343             from fnd_log_messages flm
1344             where flm.log_sequence = P_LOG_SEQUENCE;
1345 
1346 	  -- If log_sequence does not exist in fnd_log_messages
1347           -- its an invalid log_sequence, return NULL
1348 	  EXCEPTION
1352         END;
1349             WHEN NO_DATA_FOUND THEN
1350 	      LOG_BLOB := NULL;
1351 	      return;
1353 
1354 	-- If log_sequence exists create attachment
1355 	INSERT_BLOB(P_LOG_SEQUENCE, P_CHARSET, P_MIMETYPE, P_ENCODING, P_LANG, P_FILE_EXTN, P_DESC);
1356 
1357         select content
1358           into LOG_BLOB
1359           from FND_LOG_ATTACHMENTS fla
1360           where fla.log_sequence = P_LOG_SEQUENCE for UPDATE;
1361       end GET_BLOB_INTERNAL;
1362 
1363    /*
1364    ** FND_LOG_REPOSITORY.METRIC_INTERNAL
1365    ** Description:
1366    **  Private -- Should only be called by METRIC_INTERNAL_WITH_CONTEXT
1367    **  Writes a metric value out to the FND tables in an autonomous
1368    **  transaction.
1369    */
1370    PROCEDURE METRIC_INTERNAL(MODULE        IN VARCHAR2,
1371                     METRIC_CODE            IN VARCHAR2,
1372                     METRIC_SEQUENCE        IN NUMBER,
1373                     TYPE                   IN VARCHAR2,
1374                     STRING_VALUE           IN VARCHAR2,
1375                     NUMBER_VALUE           IN NUMBER,
1376                     DATE_VALUE             IN DATE,
1377                     TRANSACTION_CONTEXT_ID IN NUMBER,
1378                     SESSION_MODULE         IN VARCHAR2,
1379                     SESSION_ACTION         IN VARCHAR2,
1380                     NODE                   IN VARCHAR2,
1381                     NODE_IP_ADDRESS        IN VARCHAR2,
1382                     PROCESS_ID             IN VARCHAR2,
1383                     JVM_ID                 IN VARCHAR2,
1384                     THREAD_ID              IN VARCHAR2,
1385                     AUDSID                 IN VARCHAR2,
1386                     DB_INSTANCE            IN VARCHAR2) is
1387    pragma AUTONOMOUS_TRANSACTION;
1388      begin
1389        insert into FND_LOG_METRICS
1390         (MODULE,
1391          METRIC_CODE,
1392          METRIC_SEQUENCE,
1393          TYPE,
1394          STRING_VALUE,
1395          NUMBER_VALUE,
1396          DATE_VALUE,
1397          TIME,
1398          EVENT_KEY,
1399          TRANSACTION_CONTEXT_ID,
1400          SESSION_MODULE,
1401          SESSION_ACTION,
1402          NODE,
1403          NODE_IP_ADDRESS,
1404          PROCESS_ID,
1405          JVM_ID,
1406          THREAD_ID,
1407          AUDSID,
1408          DB_INSTANCE
1409         ) values
1410         (SUBSTRB(MODULE,1,255),
1411          METRIC_CODE,
1412          METRIC_SEQUENCE,
1413          TYPE,
1414          STRING_VALUE,
1415          NUMBER_VALUE,
1416          DATE_VALUE,
1417          SYSDATE,
1418          null,
1419          TRANSACTION_CONTEXT_ID,
1420          substrb(SESSION_MODULE,1,48),
1421          substrb(SESSION_ACTION,1,32),
1422          substrb(NODE,1,60),
1423          substrb(NODE_IP_ADDRESS,1,30),
1424          substrb(PROCESS_ID,1,120),
1425          substrb(JVM_ID,1,120),
1426          substrb(THREAD_ID,1,120),
1427          AUDSID,
1428          DB_INSTANCE
1429          );
1430        commit;
1431      end METRIC_INTERNAL;
1432 
1433 
1434    /*
1435    **  Convert the string into date format, store in global variable
1436    */
1437    PROCEDURE METRIC_STRING_TO_DATE(DATE_VC IN VARCHAR2 DEFAULT NULL) is
1438    begin
1439 
1440       if (DATE_VC IS NOT NULL) then
1441          FND_LOG_REPOSITORY.G_METRIC_DATE := FND_CONC_DATE.STRING_TO_DATE(DATE_VC);
1442          if (FND_LOG_REPOSITORY.G_METRIC_DATE IS NULL) then
1443             select SYSDATE
1444             into FND_LOG_REPOSITORY.G_METRIC_DATE
1445             from dual;
1446          end if;
1447       else
1448          select SYSDATE
1449          into FND_LOG_REPOSITORY.G_METRIC_DATE
1450          from dual;
1451       end if;
1452 
1453    end METRIC_STRING_TO_DATE;
1454 
1455 
1456    /*
1457    **  Gathers context information within the same session, then
1458    **  calls the private, autonmous procedure METRIC_INTERNAL,
1459    **  passing context information to be logged in AFLOG tables
1460    **
1461    **  A wrapper API that calls Metric_Internal using the
1462    **  context values from internal cache of the context values.
1463    **  This routine is only to be called from the AOL implementations of
1464    **  the AFLOG interface, in languages like JAVA or C.
1465    **  If the SESSION_ID is not passed, it defaults to the value that
1466    **  was passed upon INIT.
1467    */
1468    PROCEDURE METRIC_INTERNAL_WITH_CONTEXT(MODULE IN VARCHAR2,
1469                     METRIC_CODE            IN VARCHAR2,
1470                     METRIC_VALUE_STRING    IN VARCHAR2 DEFAULT NULL,
1471                     METRIC_VALUE_NUMBER    IN NUMBER   DEFAULT NULL,
1472                     METRIC_VALUE_DATE      IN DATE     DEFAULT NULL,
1473                     SESSION_ID             IN NUMBER   DEFAULT NULL,
1474                     NODE                   IN VARCHAR2 DEFAULT NULL,
1475                     NODE_IP_ADDRESS        IN VARCHAR2 DEFAULT NULL,
1476                     PROCESS_ID             IN VARCHAR2 DEFAULT NULL,
1477                     JVM_ID                 IN VARCHAR2 DEFAULT NULL,
1478                     THREAD_ID              IN VARCHAR2 DEFAULT NULL,
1479                     AUDSID                 IN NUMBER   DEFAULT NULL,
1480                     DB_INSTANCE            IN NUMBER   DEFAULT NULL) is
1481       l_context          CONTEXT_ARRAY;
1482       l_metric_sequence  number;
1483       l_type             varchar2(1);
1484       l_metric_value_date date;
1485 
1486       begin
1487       if(NOT SELF_INITED_X) then
1488          SELF_INIT;
1489       end if;
1490 
1491       GET_CONTEXT (
1492                    SESSION_ID      => SESSION_ID,
1493                    USER_ID         => -1,
1494                    NODE            => NODE,
1498                    THREAD_ID       => THREAD_ID,
1495                    NODE_IP_ADDRESS => NODE_IP_ADDRESS,
1496                    PROCESS_ID      => PROCESS_ID,
1497                    JVM_ID          => JVM_ID,
1499                    AUDSID          => AUDSID,
1500                    DB_INSTANCE     => DB_INSTANCE,
1501 		   CONTEXT_OUT     => l_context);
1502 
1503       select module, action
1504           into l_context(11).a_val, l_context(12).a_val
1505           from v$session
1506           where audsid = TXN_SESSION;
1507 
1508       select FND_LOG_METRICS_S.NEXTVAL
1509         into l_metric_sequence
1510         from dual;
1511 
1512       if (METRIC_VALUE_STRING is NOT NULL) then
1513         l_type := 'S';
1514       elsif (METRIC_VALUE_NUMBER is NOT NULL) then
1515         l_type := 'N';
1516       else
1517         l_type := 'D';
1518         if (METRIC_VALUE_DATE is NULL) then
1519            l_metric_value_date := FND_LOG_REPOSITORY.G_METRIC_DATE;
1520         else
1521            l_metric_value_date := METRIC_VALUE_DATE;
1522         end if;
1523 
1524       end if;
1525 
1526       if (AFLOG_FILENAME_X is not NULL) then
1527          null; /* Eventually we will want to add code that will log to a */
1528                /* file if they set the filename, but for now we will just */
1529                /* log to table */
1530       end if;
1531 
1532       METRIC_INTERNAL(MODULE               => MODULE,
1533                     METRIC_CODE            => METRIC_CODE,
1534                     METRIC_SEQUENCE        => l_metric_sequence,
1535                     TYPE                   => l_type,
1536                     STRING_VALUE           => METRIC_VALUE_STRING,
1537                     NUMBER_VALUE           => METRIC_VALUE_NUMBER,
1538                     DATE_VALUE             => l_metric_value_date,
1539                     TRANSACTION_CONTEXT_ID => l_context(10).a_val,
1540                     SESSION_MODULE         => l_context(11).a_val,
1541                     SESSION_ACTION         => l_context(12).a_val,
1542                     NODE                   => l_context(3).a_val,
1543                     NODE_IP_ADDRESS        => l_context(4).a_val,
1544                     PROCESS_ID             => l_context(5).a_val,
1545                     JVM_ID                 => l_context(6).a_val,
1546                     THREAD_ID              => l_context(7).a_val,
1547                     AUDSID                 => l_context(8).a_val,
1548                     DB_INSTANCE            => l_context(9).a_val);
1549 
1550       end METRIC_INTERNAL_WITH_CONTEXT;
1551 
1552    /*
1553    ** FND_LOG_REPOSITORY.METRICS_EVENT_INTERNAL
1554    ** Description:
1555    **  Private -- Should only be called by METRICS_EVENT_INT_WITH_CONTEXT
1556    **  Posts the pending metrics for the current component
1557    **  session to the Business Event system and updates the pending
1558    **  metrics with the event key in an autonomous transaction. The
1559    **  metrics will be bundled in an XML message included in the
1560    **  event.  The event will be named:
1561    **  "oracle.apps.fnd.system.metrics"
1562    **
1563    ** Arguments:
1564    **     CONTEXT_ID - Context id to post metrics for
1565    */
1566 
1567    PROCEDURE METRICS_EVENT_INTERNAL(CONTEXT_ID IN NUMBER) IS
1568       l_event_key number;
1569       pragma AUTONOMOUS_TRANSACTION;
1570 
1571    cnt    number;
1572 
1573    begin
1574 
1575       /*
1576          2983052: Check for rows in FND_LOG_METRICS
1577          If no metrics actually logged, don't raise an event.
1578       */
1579       select count(1)
1580         into cnt
1581         from FND_LOG_METRICS
1582         where TRANSACTION_CONTEXT_ID = CONTEXT_ID;
1583 
1584       if cnt = 0 then
1585         return;
1586       end if;
1587 
1588       select FND_METRICS_EVENT_KEY_S.nextval
1589         into l_event_key
1590         from dual;
1591 
1592       update FND_LOG_METRICS
1593          set EVENT_KEY = l_event_key
1594        where EVENT_KEY is NULL
1595          and TRANSACTION_CONTEXT_ID = CONTEXT_ID;
1596 
1597       begin
1598          WF_EVENT.RAISE('oracle.apps.fnd.system.metrics',
1599                         to_char(l_event_key) );
1600          commit;
1601       exception
1602          when others then
1603             FND_MESSAGE.SET_NAME ('FND', 'SQL-Generic error');
1604             FND_MESSAGE.SET_TOKEN ('ERRNO', sqlcode, FALSE);
1605             FND_MESSAGE.SET_TOKEN ('REASON', sqlerrm, FALSE);
1606             FND_MESSAGE.SET_TOKEN ('ROUTINE',
1607                                     'FND_LOG_REPOSITORY.METRIC_EVENT_INTERNAL', FALSE);
1608             rollback;
1609       end;
1610 
1611    end METRICS_EVENT_INTERNAL;
1612 
1613    /*
1614    ** FND_LOG_REPOSITORY.METRICS_EVENT_INT_WITH_CONTEXT
1615    ** Description:
1616    **  A wrapper API that calls Metrics_Event_Internal using the
1617    **  context values from internal cache of the context values.
1618    **  This routine is only to be called from the AOL implementations of
1619    **  the AFLOG interface, in languages like JAVA or C.
1620    **
1621    ** Arguments:
1622    **     CONTEXT_ID - Context id to post metrics for
1623    */
1624    PROCEDURE METRICS_EVENT_INT_WITH_CONTEXT (CONTEXT_ID IN NUMBER DEFAULT NULL)IS
1625       l_context_id number;
1626 
1627    begin
1628       if CONTEXT_ID is NOT NULL then
1629          l_context_id := CONTEXT_ID;
1630       else
1631          l_context_id := FND_LOG.G_TRANSACTION_CONTEXT_ID;
1632       end if;
1633 
1634       METRICS_EVENT_INTERNAL(l_context_id);
1635 
1636    end METRICS_EVENT_INT_WITH_CONTEXT;
1637 
1638 
1639 
1640    /*
1641    ** FND_LOG_REPOSITORY.INIT_TRANS_INT_WITH_CONTEXT
1642    ** Description:
1646    ** the AFLOG interface, in languages like JAVA or C.
1643    ** A wrapper API that calls Init_Transaction_Internal using the
1644    ** context values from internal cache of the context values.
1645    ** This routine is only to be called from the AOL implementations of
1647    ** If the SESSION_ID and/or USER_ID is not passed, it defaults to the
1648    ** value that was passed upon INIT.
1649    **
1650    ** Initializes a log transaction.  A log transaction
1651    ** corresponds to an instance or invocation of a single
1652    ** component.  (e.g. A concurrent request, service process,
1653    ** open form, ICX function)
1654    **
1655    ** This routine should be called only after
1656    ** FND_GLOBAL.INITIALIZE, since some of the context information
1657    ** is retrieved from FND_GLOBAL.
1658    **
1659    ** Arguments:
1660    **   CONC_REQUEST_ID       - Concurrent request id
1661    **   FORM_ID               - Form id
1662    **   FORM_APPLICATION_ID   - Form application id
1663    **   CONCURRENT_PROCESS_ID - Service process id
1664    **   CONCURRENT_QUEUE_ID   - Service queue id
1665    **   QUEUE_APPLICATION_ID  - Service queue application id
1666    **   SOA_INSTANCE_ID       - SOA instance id
1667    **
1668    ** Use only the arguments that apply to the caller.
1669    ** Any argument that does not apply should be passed as NULL
1670    ** i.e. when calling from a form, pass in FORM_ID and FORM_APPLICATION_ID
1671    ** and leave all other parameters NULL.
1672    **
1673    ** Returns:
1674    **   ID of the log transaction context
1675    **
1676    */
1677    FUNCTION INIT_TRANS_INT_WITH_CONTEXT (CONC_REQUEST_ID             IN NUMBER DEFAULT NULL,
1678                                          FORM_ID                     IN NUMBER DEFAULT NULL,
1679                                          FORM_APPLICATION_ID         IN NUMBER DEFAULT NULL,
1680                                          CONCURRENT_PROCESS_ID       IN NUMBER DEFAULT NULL,
1681                                          CONCURRENT_QUEUE_ID         IN NUMBER DEFAULT NULL,
1682                                          QUEUE_APPLICATION_ID        IN NUMBER DEFAULT NULL,
1683                                          SESSION_ID                  IN NUMBER DEFAULT NULL,
1684                                          USER_ID                     IN NUMBER DEFAULT NULL,
1685                                          RESP_APPL_ID                IN NUMBER DEFAULT NULL,
1686                                          RESPONSIBILITY_ID           IN NUMBER DEFAULT NULL,
1687                                          SECURITY_GROUP_ID           IN NUMBER DEFAULT NULL,
1688 					 SOA_INSTANCE_ID             IN NUMBER DEFAULT NULL)
1689                                                           return NUMBER is
1690 
1691       l_context                 context_array;
1692       l_transaction_type        varchar2(30);
1693       l_transaction_id          number;
1694       l_component_appl_id       number;
1695       l_component_type          varchar2(30);
1696       l_component_id            number;
1697       l_transaction_context_id  number;
1698       l_form_id                 number;
1699       l_form_application_id     number;
1700 
1701       begin
1702       if(NOT SELF_INITED_X) then
1703          SELF_INIT;
1704       end if;
1705 
1706       GET_TRANSACTION_CONTEXT (
1707                      SESSION_ID          => SESSION_ID,
1708                      USER_ID             => USER_ID,
1709                      RESP_APPL_ID        => RESP_APPL_ID,
1710                      RESPONSIBILITY_ID   => RESPONSIBILITY_ID,
1711                      SECURITY_GROUP_ID   => SECURITY_GROUP_ID,
1712 		     CONTEXT_OUT	 => l_context);
1713 
1714       -- concurrent processes
1715       -- order is important here, must check for a concurrent process before
1716       -- a concurrent request.
1717       if concurrent_process_id is not null and
1718              (concurrent_process_id > 0 or concurrent_process_id = -999) then
1719 
1720         -- ignore this value, the real value will be along soon...
1721         if  concurrent_process_id = -999 then
1722             return null;
1723         end if;
1724 
1725         -- see if a transaction context exists for this process
1726         begin
1727           select transaction_context_id
1728             into l_transaction_context_id
1729             from fnd_log_transaction_context
1730             where transaction_type = 'SERVICE'
1731             and transaction_id = concurrent_process_id;
1732 
1733           return l_transaction_context_id;
1734 
1735         exception
1736              when no_data_found then
1737                -- create a new transaction context
1738                l_transaction_type  := 'SERVICE';
1739                l_transaction_id    := concurrent_process_id;
1740                l_component_id      := concurrent_queue_id;
1741                l_component_appl_id := queue_application_id;
1742                l_component_type    := 'SERVICE_INSTANCE';
1743         end;
1744 
1745       -- concurrent requests
1746       elsif conc_request_id is not null and conc_request_id > 0 then
1747 
1748         -- see if a transaction context exists for this request
1749         begin
1750           select transaction_context_id
1751             into l_transaction_context_id
1752             from fnd_log_transaction_context
1753             where transaction_type = 'REQUEST'
1754             and transaction_id = conc_request_id;
1755 
1756           return l_transaction_context_id;
1757 
1758         exception
1759              when no_data_found then
1760                -- create a new transaction context
1761                l_transaction_type  := 'REQUEST';
1762                l_transaction_id    := conc_request_id;
1763                l_component_id      := FND_GLOBAL.conc_program_id;
1764                l_component_appl_id := FND_GLOBAL.prog_appl_id;
1765                l_component_type    := 'CONCURRENT_PROGRAM';
1769       elsif form_id is not null and (form_id > 0 or form_id = -999) then
1766         end;
1767 
1768       -- forms
1770 
1771         l_transaction_id := TXN_SESSION; -- using AUDSID as the transaction_id
1772 
1773          if form_id = -999 then
1774             l_form_id := null;
1775             l_form_application_id := null;
1776          else
1777             l_form_id := form_id;
1778             l_form_application_id := form_application_id;
1779          end if;
1780 
1781         -- see if a transaction context exists for this form
1782         begin
1783           select transaction_context_id
1784             into l_transaction_context_id
1785             from fnd_log_transaction_context
1786             where transaction_type = 'FORM'
1787             and transaction_id = l_transaction_id;
1788 
1789           return l_transaction_context_id;
1790 
1791         exception
1792              when no_data_found then
1793                -- create a new transaction context for this form
1794                l_transaction_type  := 'FORM';
1795                l_component_id      := l_form_id;
1796                l_component_appl_id := l_form_application_id;
1797                l_component_type    := 'FORM';
1798         end;
1799 
1800 
1801       -- SOA instance
1802       elsif soa_instance_id is not null and soa_instance_id > 0 then
1803 
1804         -- see if a transaction context exists for this instance
1805         begin
1806           select transaction_context_id
1807             into l_transaction_context_id
1808             from fnd_log_transaction_context
1809             where transaction_type = 'SOA_INSTANCE'
1810             and transaction_id = soa_instance_id;
1811 
1812           return l_transaction_context_id;
1813 
1814         exception
1815              when no_data_found then
1816                -- create a new transaction context
1817                l_transaction_type  := 'SOA_INSTANCE';
1818                l_transaction_id    := soa_instance_id;
1819                l_component_id      := null;
1820                l_component_appl_id := null;
1821                l_component_type    := 'SOA_INSTANCE';
1822         end;
1823 
1824 
1825       -- ICX sessions, ICX transactions
1826       elsif icx_sec.g_session_id is not null and icx_sec.g_session_id > 0 then
1827 
1828         -- see if a transaction context exists for this session
1829         begin
1830           l_component_id := null;
1831 
1832 	  -- Check for finer ICX Transaction
1833           if icx_sec.g_transaction_id is not null and icx_sec.g_transaction_id > 0 then
1834 	    begin
1835           	select transaction_context_id
1836             	  into l_transaction_context_id
1837             	  from fnd_log_transaction_context
1838             	  where transaction_type = 'ICX'
1839             	  and transaction_id = icx_sec.g_transaction_id
1840 		  and session_id = icx_sec.g_session_id
1841 		  and user_id = to_number(l_context(2).a_val)
1842 		  and resp_appl_id = to_number(l_context(3).a_val)
1843 		  and responsibility_id = to_number(l_context(4).a_val)
1844 		  and security_group_id = to_number(l_context(5).a_val)
1845 	          and rownum = 1; -- there maybe previous duplicate rows
1846           	return l_transaction_context_id;
1847 
1848         	exception
1849              	  when no_data_found then
1850                	  -- create a new transaction context
1851 		  null;
1852          /*
1853                	  begin
1854                     select function_id
1855                       into l_component_id
1856                       from icx_transactions
1857                       where transaction_id = icx_sec.g_transaction_id;
1858                     exception
1859                       when others then
1860                         l_component_id := null;
1861                   end;
1862 	  */
1863             end;
1864 
1865 	  else
1866 	  -- Check for coarser ICX Session
1867             begin
1868                 select transaction_context_id
1869                   into l_transaction_context_id
1870                   from fnd_log_transaction_context
1871                   where transaction_type = 'ICX'
1872                   and session_id = icx_sec.g_session_id
1873 		  and transaction_id = -1
1874                   and user_id = to_number(l_context(2).a_val)
1875                   and resp_appl_id = to_number(l_context(3).a_val)
1876                   and responsibility_id = to_number(l_context(4).a_val)
1877                   and security_group_id = to_number(l_context(5).a_val)
1878 	          and rownum = 1; -- there maybe previous duplicate rows
1879                 return l_transaction_context_id;
1880 
1881                 exception
1882                   when no_data_found then
1883                   -- create a new transaction context
1884 		  null;
1885 	  /*
1886                   begin
1887                     select function_id
1888                       into l_component_id
1889                       from icx_sessions
1890                       where session_id = icx_sec.g_session_id;
1891                     exception
1892                       when others then
1893                         l_component_id := null;
1894                   end;
1895 	  */
1896             end;
1897           end if;
1898 
1899           l_transaction_type  := 'ICX';
1900           l_transaction_id    := icx_sec.g_transaction_id;
1901           l_component_appl_id := null;
1902           l_component_type    := 'FUNCTION';
1903         end;
1904 
1905       -- if none of the above, check for 'UNKNOWN' context
1906       else
1907 
1908         begin
1909           select transaction_context_id
1910             into l_transaction_context_id
1911             from fnd_log_transaction_context
1912             where transaction_type = 'UNKNOWN'
1913             and session_id = -1
1914             and transaction_id = -1
1915             and user_id = to_number(l_context(2).a_val)
1916             and resp_appl_id = to_number(l_context(3).a_val)
1917             and responsibility_id = to_number(l_context(4).a_val)
1918             and security_group_id = to_number(l_context(5).a_val)
1919 	    and rownum = 1; -- there maybe previous duplicate rows
1920           return l_transaction_context_id;
1921         exception
1922           when no_data_found then
1923             l_transaction_type := 'UNKNOWN';
1924             l_transaction_id := -1;
1925             l_component_type := null;
1926             l_component_appl_id := -1;
1927             l_component_id := -1;
1928 	end;
1929 
1930       end if;
1931 
1932 
1933      INIT_TRANSACTION_INTERNAL(
1934                P_TRANSACTION_TYPE            => l_transaction_type,
1935                P_TRANSACTION_ID              => l_transaction_id,
1936                P_COMPONENT_TYPE              => l_component_type,
1937                P_COMPONENT_APPL_ID           => l_component_appl_id,
1938                P_COMPONENT_ID                => l_component_id,
1939                P_SESSION_ID                  => to_number(l_context(1).a_val),
1940                P_USER_ID                     => to_number(l_context(2).a_val),
1941                P_RESP_APPL_ID                => to_number(l_context(3).a_val),
1942                P_RESPONSIBILITY_ID           => to_number(l_context(4).a_val),
1943                P_SECURITY_GROUP_ID           => to_number(l_context(5).a_val));
1944 
1945    return FND_LOG.G_TRANSACTION_CONTEXT_ID;
1946 
1947    end INIT_TRANS_INT_WITH_CONTEXT;
1948 
1949 
1950    /*
1951    ** Internal- This routine initializes the logging system from the
1952    ** profiles.  AOL will normally call this routine to initialize the
1953    ** system so the API consumer should not need to call it.
1954    ** The SESSION_ID is a unique identifier (like the ICX_SESSION id)
1955    ** The USER_ID is the name of the apps user.
1956    */
1957    PROCEDURE INIT(SESSION_ID   IN NUMBER default NULL,
1958                   USER_ID      IN NUMBER default NULL) is
1959         POS       NUMBER;
1960         NEXTPOS   NUMBER;
1961 		DATA_SIZE NUMBER;
1962 
1963    begin
1964       if(SESSION_ID is NULL) then
1965          SESSION_ID_X := icx_sec.g_session_id;
1966       else
1967          SESSION_ID_X := SESSION_ID;
1968       end if;
1969 
1970       if(USER_ID is NULL) then
1971          USER_ID_X    := to_number(FND_PROFILE.VALUE('USER_ID'));
1972       else
1973          USER_ID_X := USER_ID;
1974       end if;
1975 
1976       if(USER_ID_X is NULL) then
1977          USER_ID_X    := -1;
1978       end if;
1979 
1980 
1981       if (SUBSTR(FND_PROFILE.VALUE('AFLOG_ENABLED'), 1, 1) = 'Y') then
1982          AFLOG_ENABLED_X     := TRUE;
1983          AFLOG_FILENAME_X    := SUBSTR(FND_PROFILE.VALUE('AFLOG_FILENAME'), 1,
1984                                  255);
1985          AFLOG_LEVEL_X       := TO_NUMBER(FND_PROFILE.VALUE('AFLOG_LEVEL'));
1986          AFLOG_MODULE_X      := UPPER(SUBSTR(
1987                                    FND_PROFILE.VALUE('AFLOG_MODULE'),
1988                                    1, 2000));
1989       else
1990          AFLOG_ENABLED_X     := FALSE;
1991          AFLOG_FILENAME_X    := NULL;
1992          AFLOG_LEVEL_X       := NULL;
1993          AFLOG_MODULE_X      := NULL;
1994       end if;
1995 
1996       /* Set up the global level in the log package so it won't have */
1997       /* to call through in order to find out whether logging is */
1998       /* enabled at this level. */
1999       if (AFLOG_ENABLED_X) then
2000 		FND_LOG.G_CURRENT_RUNTIME_LEVEL := AFLOG_LEVEL_X;
2001 
2002 		/* Tracing disabled for initial 12.0 release
2003         if (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_EXCEPTION) then
2004           DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.trace_all_exceptions);
2005           fnd_log_enabled_tracing := true;
2006         elsif (fnd_log_enabled_tracing) then
2007           DBMS_TRACE.CLEAR_PLSQL_TRACE;
2008           fnd_log_enabled_tracing := false;
2009 		end if;
2010 		*/
2011       else
2012 		FND_LOG.G_CURRENT_RUNTIME_LEVEL := 99999;
2013 		/*
2014 	    if (fnd_log_enabled_tracing) then
2015           DBMS_TRACE.CLEAR_PLSQL_TRACE;
2016           fnd_log_enabled_tracing := false;
2017 		end if;
2018 		*/
2019       end if;
2020 
2021       /* Store away the module list in the module table */
2022       if(AFLOG_MODULE_X is null) then
2023          TABLE_SIZE := 0;
2024       else
2025          POS := 1;
2026          TABLE_SIZE := 0;
2027          DATA_SIZE := LENGTH(AFLOG_MODULE_X);
2028          while POS <= DATA_SIZE loop
2029             NEXTPOS := INSTR(AFLOG_MODULE_X, ',', POS);
2030             if(NEXTPOS = 0) then
2031                NEXTPOS := DATA_SIZE + 1;
2032             end if;
2033             TABLE_SIZE := TABLE_SIZE + 1;
2034             MODULE_TAB(TABLE_SIZE) := UPPER(LTRIM(RTRIM(
2035                 SUBSTR(AFLOG_MODULE_X, POS, NEXTPOS - POS))))||'%';
2036             POS := NEXTPOS+1; /* Advance past the comma */
2037          end loop;
2038       end if;
2039 
2040       SELF_INITED_X := TRUE;
2041 
2042       /* Deferred Init: All initialization is now deferred to GET_CONTEXT */
2043       FND_LOG.G_TRANSACTION_CONTEXT_ID := null;
2044 
2045    exception
2046      when OTHERS then
2047         /* Make sure that an exception here does not stop Apps initialization */
2048         null;
2049    end;
2050 
2051    /**
2052     *  Private procedure called from AppsLog.java if buffering messages
2053     *  for Bulk logging. AppsLog.java can buffer (if setAsynchMode is enabled)
2054     *  messages with the context returned from this procedure (to preserve
2055     *  the context and sequence) and periodically flushes by calling BULK_INSERT_PVT().
2056     */
2057    PROCEDURE GET_BULK_CONTEXT_PVT (
2058                                 LOG_SEQUENCE_OUT OUT NOCOPY NUMBER,
2059                                 TIMESTAMP_OUT    OUT NOCOPY DATE,
2060                                 DBSESSIONID_OUT  OUT NOCOPY NUMBER,
2061                                 DBINSTANCE_OUT   OUT NOCOPY NUMBER,
2062                                 TXN_ID_OUT       OUT NOCOPY NUMBER
2063                                 ) is
2064      l_context  CONTEXT_ARRAY;
2065    begin
2066 
2067      if(NOT SELF_INITED_X) then
2068        SELF_INIT;
2069        if(FND_LOG.G_TRANSACTION_CONTEXT_ID is null) then
2070 	  GET_CONTEXT(CONTEXT_OUT => l_context);
2071        end if;
2072      end if;
2073 
2074      select FND_LOG_MESSAGES_S.NEXTVAL
2075 	into LOG_SEQUENCE_OUT
2076 	from dual;
2077 
2078      TIMESTAMP_OUT := sysdate;
2079      DBSESSIONID_OUT := TXN_SESSION;
2080      DBINSTANCE_OUT := TXN_INSTANCE;
2081      TXN_ID_OUT := FND_LOG.G_TRANSACTION_CONTEXT_ID;
2082 
2083    end GET_BULK_CONTEXT_PVT;
2084 
2085    /**
2086     *  Private function for Bulk logging messages
2087     */
2088    FUNCTION BULK_INSERT_PVT(MODULE_IN IN FND_TABLE_OF_VARCHAR2_255,
2089                         LOG_LEVEL_IN IN FND_TABLE_OF_NUMBER,
2090                         MESSAGE_TEXT_IN IN FND_TABLE_OF_VARCHAR2_4000,
2091                         SESSION_ID_IN IN FND_TABLE_OF_NUMBER,
2092                         USER_ID_IN IN FND_TABLE_OF_NUMBER,
2093                         TIMESTAMP_IN IN FND_TABLE_OF_DATE,
2094                         LOG_SEQUENCE_IN IN FND_TABLE_OF_NUMBER,
2095                         ENCODED_IN IN FND_TABLE_OF_VARCHAR2_1,
2096                         NODE_IN IN varchar2,
2097                         NODE_IP_ADDRESS_IN IN varchar2,
2098                         PROCESS_ID_IN IN varchar2,
2099                         JVM_ID_IN IN varchar2,
2100                         THREAD_ID_IN IN FND_TABLE_OF_VARCHAR2_120,
2101                         AUDSID_IN IN FND_TABLE_OF_NUMBER,
2102                         DB_INSTANCE_IN IN FND_TABLE_OF_NUMBER,
2103 			TRANSACTION_CONTEXT_ID_IN IN FND_TABLE_OF_NUMBER,
2104 			SIZE_IN IN NUMBER) RETURN NUMBER is
2105   pragma AUTONOMOUS_TRANSACTION;
2106       l_node     varchar2(60);
2107       l_node_ip_address varchar2(30);
2108       l_process_id varchar2(120);
2109       l_jvm_id   varchar2(120);
2110       l_posted   boolean := FALSE;
2111       i          NUMBER;
2112   begin
2113 
2114     if(NOT SELF_INITED_X) then
2115        SELF_INIT;
2116     end if;
2117 
2118     l_node := substrb(NODE_IN,1,60);
2119     l_node_ip_address := substrb(NODE_IP_ADDRESS_IN,1,30);
2120     l_process_id := substrb(nvl(PROCESS_ID_IN, TXN_PROCESS),1,120);
2121     l_jvm_id := substrb(JVM_ID_IN,1,120);
2122 
2123     FORALL i IN 1..SIZE_IN
2124       INSERT INTO FND_LOG_MESSAGES (
2125          MODULE,
2126          LOG_LEVEL,
2127          MESSAGE_TEXT,
2128          SESSION_ID,
2129          USER_ID,
2130          TIMESTAMP,
2131          LOG_SEQUENCE,
2132          ENCODED,
2133          NODE,
2134          NODE_IP_ADDRESS,
2135          PROCESS_ID,
2136          JVM_ID,
2137          THREAD_ID,
2138          AUDSID,
2139          DB_INSTANCE,
2140          TRANSACTION_CONTEXT_ID
2141       ) values
2142       (
2143          MODULE_IN(i),
2144          LOG_LEVEL_IN(i),
2145          MESSAGE_TEXT_IN(i),
2146          SESSION_ID_IN(i),
2147          nvl(USER_ID_IN(i), -1),
2148          nvl(TIMESTAMP_IN(i), sysdate),
2149          nvl(LOG_SEQUENCE_IN(i), FND_LOG_MESSAGES_S.NEXTVAL),
2150          ENCODED_IN(i),
2151          l_node,
2152          l_node_ip_address,
2153          l_process_id,
2154          l_jvm_id,
2155          substrb(THREAD_ID_IN(i),1,120),
2156          AUDSID_IN(i),
2157          DB_INSTANCE_IN(i),
2158          TRANSACTION_CONTEXT_ID_IN(i)
2159       );
2160 
2161     commit;
2162 
2163 
2164     /* Typically there won't be any UNEXPECTED messages logged using this Function */
2165     /* Unexpected errors are posted as exceptions */
2166 /*
2167     FOR i IN 1..SIZE_IN LOOP
2168       if (ENCODED_IN(i) = 'Y') and (LOG_LEVEL_IN(i) = FND_LOG.LEVEL_UNEXPECTED) then
2169          l_posted :=  POST_EXCEPTION(P_MODULE         => MODULE_IN(i),
2170                                      P_LOG_SEQUENCE   => LOG_SEQUENCE_IN(i),
2171                                      P_SESSION_MODULE => NULL,
2172                                      P_SESSION_ACTION => NULL);
2173       end if;
2174     END LOOP;
2175 */
2176     return SIZE_IN;
2177   end BULK_INSERT_PVT;
2178 
2179 /*============================================================================
2180  * Proxy Alerting related Procedures - Start
2181  *===========================================================================*/
2182  /** For Debugging Only
2183  PROCEDURE DUMP_CC is
2184 
2185  BEGIN
2186    DEBUG('=============================================');
2187    DEBUG('G_PRX_CHILD_TRANS_CONTEXT_ID: ' || G_PRX_CHILD_TRANS_CONTEXT_ID);
2188    DEBUG('G_PRX_SESSION_ID: ' || G_PRX_SESSION_ID);
2189    DEBUG('G_PRX_USER_ID: ' || G_PRX_USER_ID);
2190    DEBUG('G_PRX_SESSION_MODULE: ' || G_PRX_SESSION_MODULE);
2191    DEBUG('G_PRX_SESSION_ACTION: ' || G_PRX_SESSION_ACTION);
2192    DEBUG('G_PRX_MODULE: ' || G_PRX_MODULE);
2193    DEBUG('G_PRX_NODE: ' || G_PRX_NODE);
2194    DEBUG('G_PRX_NODE_IP_ADDRESS: ' || G_PRX_NODE_IP_ADDRESS);
2195    DEBUG('G_PRX_PROCESS_ID: ' || G_PRX_PROCESS_ID);
2196    DEBUG('G_PRX_JVM_ID: ' || G_PRX_JVM_ID);
2197    DEBUG('G_PRX_THREAD_ID: ' || G_PRX_THREAD_ID);
2198    DEBUG('G_PRX_AUDSID: ' || G_PRX_AUDSID);
2199    DEBUG('G_PRX_DB_INSTANCE: ' || G_PRX_DB_INSTANCE);
2200  END DUMP_CC;
2201  */
2202 
2203  /**
2204   * Fetches context information for the given concurrent request ID and
2205   * places them in the given CONTEXT_ARRAY output variable.
2206   */
2207  PROCEDURE FETCH_CONTEXT_FOR_CONC_REQ(
2208 	p_request_id IN NUMBER,
2209 	p_info_type IN VARCHAR2 DEFAULT 'ALL',
2210 	p_context_array OUT NOCOPY CONTEXT_ARRAY) is
2211 
2212  BEGIN
2213    --
2214    -- Fetch basic transaction context information for the given request id
2215    --
2216    if (p_info_type = 'ALL') then
2217      begin
2218        	select fcr.requested_by, fcr.responsibility_application_id,
2219 	       fcr.responsibility_id, fcr.security_group_id,
2220 	       'CONCURRENT_PROGRAM', fcr.program_application_id,
2221 	       fcr.concurrent_program_id
2222 	  into
2223 	    p_context_array(CCI_USER_ID).a_val,
2224 	    p_context_array(CCI_RESP_APPL_ID).a_val,
2225 	    p_context_array(CCI_RESPONSIBILITY_ID).a_val,
2226 	    p_context_array(CCI_SECURITY_GROUP_ID).a_val,
2227 	    p_context_array(CCI_COMPONENT_TYPE).a_val,
2228 	    p_context_array(CCI_COMPONENT_APPL_ID).a_val,
2229 	    p_context_array(CCI_COMPONENT_ID).a_val
2230 	  from fnd_concurrent_requests fcr
2231 	  where fcr.request_id = p_request_id;
2232      exception
2233 	when no_data_found then
2234 	    p_context_array(CCI_USER_ID).a_val := null;
2235 	    p_context_array(CCI_RESP_APPL_ID).a_val := null;
2236 	    p_context_array(CCI_RESPONSIBILITY_ID).a_val := null;
2237 	    p_context_array(CCI_SECURITY_GROUP_ID).a_val := null;
2238 	    p_context_array(CCI_COMPONENT_TYPE).a_val := null;
2239 	    p_context_array(CCI_COMPONENT_APPL_ID).a_val := null;
2240 	    p_context_array(CCI_COMPONENT_ID).a_val := null;
2241      end;
2242    end if;
2243    --
2244    -- Attempt to fetch additional info that we might be able to get
2245    --
2246    if (p_info_type = 'ALL' or p_info_type = 'ADDITIONAL') then
2247      begin
2248 	select fcr.requested_by, fcr.oracle_session_id, fcr.os_process_id,
2249 		gv.module, gv.action, '-1'
2250 	  into
2251 	    p_context_array(CCI_USER_ID).a_val,
2252 	    p_context_array(CCI_AUDSID).a_val,
2253 	    p_context_array(CCI_PROCESS_ID).a_val,
2254 	    p_context_array(CCI_SESSION_MODULE).a_val,
2255 	    p_context_array(CCI_SESSION_ACTION).a_val,
2256 	    p_context_array(CCI_SESSION_ID).a_val
2257 	  from fnd_concurrent_requests fcr,
2258 	       gv$session gv
2259 	  where fcr.request_id = p_request_id
2260 		and fcr.oracle_session_id = gv.audsid (+);
2261      exception
2262 	when no_data_found then
2263 	   p_context_array(CCI_USER_ID).a_val := null;
2264 	   p_context_array(CCI_AUDSID).a_val := null;
2265 	   p_context_array(CCI_PROCESS_ID).a_val := null;
2266 	   p_context_array(CCI_SESSION_MODULE).a_val := null;
2267 	   p_context_array(CCI_SESSION_ACTION).a_val := null;
2268 	   p_context_array(CCI_SESSION_ID).a_val := null;
2269      end;
2270    end if;
2271    --
2272    -- Also set some additional variables that we won't be able to fetch
2273    --
2274    p_context_array(CCI_MODULE).a_val := null;
2275    p_context_array(CCI_NODE).a_val := null;
2276    p_context_array(CCI_NODE_IP_ADDRESS).a_val := null;
2277    p_context_array(CCI_JVM_ID).a_val := null;
2278    p_context_array(CCI_THREAD_ID).a_val := null;
2279    p_context_array(CCI_DB_INSTANCE).a_val := null;
2280 
2281  END FETCH_CONTEXT_FOR_CONC_REQ;
2282 
2283   /**
2284    * Initializes a child transaction context using the given information.
2285    *
2286    * If p_transaction_id and p_transaction_type are provided, the procedure
2287    * attempts to fetch additional transaction context.
2288    *
2292    * E.g. for child context user id:
2289    * Otherwise, Procedure INIT_CHILD_CONTEXT will look for transaction context
2290    * values from the context array as follows:
2291    *
2293    * l_user_id := p_child_context_array(CCI_USER_ID).a_val;
2294    */
2295   PROCEDURE INIT_CHILD_CONTEXT (
2296 	p_parent_context_id IN NUMBER,
2297 	p_transaction_id IN NUMBER DEFAULT NULL,
2298 	p_transaction_type IN VARCHAR2 DEFAULT NULL
2299 	--p_child_context_array IN CONTEXT_ARRAY
2300 	) is
2301     l_child_context_exists boolean := TRUE;
2302     l_context_array CONTEXT_ARRAY;
2303     l_child_context_fetched_all boolean := FALSE;
2304   BEGIN
2305     --
2306     -- Initialize child transaction context if necessary
2307     --
2308     if (p_transaction_id is not null and p_transaction_type is not null) then
2309       begin
2310         --
2311         -- Check if child context already exists
2312         --
2313         select transaction_context_id into G_PRX_CHILD_TRANS_CONTEXT_ID
2314 	  from fnd_log_transaction_context
2315 	  where transaction_id = p_transaction_id
2316 	  and transaction_type = p_transaction_type
2317 	  and parent_context_id = p_parent_context_id;
2318       exception
2319 	when no_data_found then
2320 	  l_child_context_exists := FALSE;
2321       end;
2322 
2323       if (l_child_context_exists = FALSE) then
2324 	--
2325 	-- Today we only have a usecase for REQUEST but we can
2326         -- plug-in for other transaction types in the future
2327         -- as we understand more use cases
2328 	--
2329 	if (p_transaction_type = 'REQUEST') then
2330 	  fetch_context_for_conc_req(
2331 		p_request_id => p_transaction_id,
2332 		p_info_type => 'ALL',
2333 		p_context_array => l_context_array);
2334 	  l_child_context_fetched_all := TRUE;
2335 	end if;
2336 
2337 	--
2338 	-- Here, call init_transaction_internal to create a new
2339 	-- row for child context in fnd_log_transaction_context
2340 	--
2341 	init_transaction_internal(
2342 	  p_transaction_type => p_transaction_type,
2343 	  p_transaction_id => p_transaction_id,
2344 	  p_component_type => l_context_array(CCI_COMPONENT_TYPE).a_val,
2345 	  p_component_appl_id => to_number(l_context_array(CCI_COMPONENT_APPL_ID).a_val),
2346 	  p_component_id => to_number(l_context_array(CCI_COMPONENT_ID).a_val),
2347 	  p_session_id => to_number(l_context_array(CCI_SESSION_ID).a_val),
2348 	  p_user_id => to_number(l_context_array(CCI_USER_ID).a_val),
2349 	  p_resp_appl_id => to_number(l_context_array(CCI_RESP_APPL_ID).a_val),
2350 	  p_responsibility_id => to_number(l_context_array(CCI_RESPONSIBILITY_ID).a_val),
2351 	  p_security_group_id => to_number(l_context_array(CCI_SECURITY_GROUP_ID).a_val),
2352 	  p_parent_context_id => p_parent_context_id);
2353       end if;
2354 /*
2355     elsif (p_child_context_array is not null) then
2356 	--
2357 	-- Transaction Id and type were not provided, so we simply
2358 	-- initialize a new transaction context based on the
2359 	-- information provided.
2360 	--
2361 	init_transaction_internal(
2362 	  p_transaction_type => 'UNKNOWN',
2363 	  p_transaction_id => -1,
2364 	  p_component_type => p_child_context_array(CCI_COMPONENT_TYPE),
2365 	  p_component_appl_id => to_number(p_child_context_array(CCI_COMPONENT_APPL_ID).a_val),
2366 	  p_component_id => to_number(p_child_context_array(CCI_COMPONENT_ID).a_val),
2367 	  p_session_id => to_number(p_child_context_array(CCI_SESSION_ID).a_val),
2368 	  p_user_id => to_number(p_child_context_array(CCI_USER_ID).a_val),
2369 	  p_resp_appl_id => to_number(p_child_context_array(CCI_RESP_APPL_ID).a_val),
2370 	  p_responsibility_id => to_number(p_child_context_array(CCI_RESPONSIBILITY_ID).a_val),
2371 	  p_security_group_id => to_number(p_child_context_array(CCI_SECURITY_GROUP_ID).a_val),
2372 	  p_parent_context_id => p_parent_context_id);
2373 */
2374     end if;
2375 
2376     --
2377     -- Now fetch additional context context such as
2378     -- session_action, session_module, etc if we already havent done so
2379     --
2380     if (p_transaction_type = 'REQUEST' and
2381 	l_child_context_fetched_all = FALSE) then
2382 	fetch_context_for_conc_req(
2383 		p_request_id => p_transaction_id,
2384 		p_info_type => 'ADDITIONAL',
2385 		p_context_array => l_context_array);
2386     end if;
2387 
2388     --
2389     -- Set the globals for the additional context information
2390     -- if available.
2391     --
2392     if (p_transaction_type is not null and p_transaction_id is not null) then
2393       G_PRX_SESSION_ID := to_number(l_context_array(CCI_SESSION_ID).a_val);
2394       G_PRX_USER_ID := to_number(l_context_array(CCI_USER_ID).a_val);
2395       G_PRX_SESSION_MODULE := l_context_array(CCI_SESSION_MODULE).a_val;
2396       G_PRX_SESSION_ACTION := l_context_array(CCI_SESSION_ACTION).a_val;
2397       G_PRX_MODULE := l_context_array(CCI_MODULE).a_val;
2398       G_PRX_NODE := l_context_array(CCI_NODE).a_val;
2399       G_PRX_NODE_IP_ADDRESS := l_context_array(CCI_NODE_IP_ADDRESS).a_val;
2400       G_PRX_PROCESS_ID := l_context_array(CCI_PROCESS_ID).a_val;
2401       G_PRX_JVM_ID := l_context_array(CCI_JVM_ID).a_val;
2402       G_PRX_THREAD_ID := l_context_array(CCI_THREAD_ID).a_val;
2403       G_PRX_AUDSID := to_number(l_context_array(CCI_AUDSID).a_val);
2404       G_PRX_DB_INSTANCE := to_number(l_context_array(CCI_DB_INSTANCE).a_val);
2405 /*
2406     elsif (p_child_context_array is not null) then
2407       G_PRX_SESSION_ID := to_number(p_child_context_array(CCI_SESSION_ID).a_val);
2408       G_PRX_USER_ID := to_number(p_child_context_array(CCI_USER_ID).a_val);
2409       G_PRX_SESSION_MODULE := p_child_context_array(CCI_SESSION_MODULE).a_val;
2410       G_PRX_SESSION_ACTION := p_child_context_array(CCI_SESSION_ACTION).a_val;
2411       G_PRX_MODULE := p_child_context_array(CCI_MODULE).a_val;
2412       G_PRX_NODE := l_context_array(CCI_NODE).a_val;
2413       G_PRX_NODE_IP_ADDRESS := p_child_context_array(CCI_NODE_IP_ADDRESS).a_val;
2414       G_PRX_PROCESS_ID := p_child_context_array(CCI_PROCESS_ID).a_val;
2415       G_PRX_JVM_ID := p_child_context_array(CCI_JVM_ID).a_val;
2416       G_PRX_THREAD_ID := p_child_context_array(CCI_THREAD_ID).a_val;
2417       G_PRX_AUDSID := to_number(p_child_context_array(CCI_AUDSID).a_val);
2418       G_PRX_DB_INSTANCE := to_number(p_child_context_array(CCI_DB_INSTANCE).a_val);
2419 */
2420     end if;
2421   END INIT_CHILD_CONTEXT;
2422 
2423 
2424   /**
2425    * API for setting a child context (for proxy alerting) for the given
2426    * concurrent request ID.
2427    *
2428    * This API will first initialize the proxy context (i.e. the current
2429    * transaction context) if not already initialized. It will then
2430    * initialize the child transaction context for the given concurrent
2431    * request ID if it has not been initialized already.
2432    */
2433   PROCEDURE SET_CHILD_CONTEXT_FOR_CONC_REQ (
2434 	p_request_id IN NUMBER ) is
2435     l_context CONTEXT_ARRAY;
2436   BEGIN
2437     -- Initialize the parent (current) transaction context if not
2438     -- already initialized.
2439     if(NOT SELF_INITED_X) then
2440       SELF_INIT;
2441     end if;
2442 
2443     if(FND_LOG.G_TRANSACTION_CONTEXT_ID is null) then
2444       GET_CONTEXT(CONTEXT_OUT => l_context);
2445     end if;
2446 
2447 
2448     -- Now, initialize the child transaction context if not already
2449     -- initialized.
2450     if (G_PRX_CHILD_TRANS_CONTEXT_ID is null) then
2451       INIT_CHILD_CONTEXT(
2452 	p_parent_context_id => fnd_log.g_transaction_context_id,
2453 	p_transaction_id => p_request_id,
2454 	p_transaction_type => 'REQUEST');
2455     end if;
2456 
2457     --DUMP_CC;
2458   END SET_CHILD_CONTEXT_FOR_CONC_REQ;
2459 
2460   /**
2461    * This API clears the G_CHILD_TRANSACTION_CONTEXT_ID variable
2462    * along with any other globals associated with the child
2463    * context for proxy alerting.
2464    */
2465   PROCEDURE CLEAR_CHILD_CONTEXT is
2466 
2467   BEGIN
2468     G_PRX_CHILD_TRANS_CONTEXT_ID := null;
2469     G_PRX_SESSION_MODULE := null;
2470     G_PRX_SESSION_ACTION := null;
2471     G_PRX_MODULE := null;
2472     G_PRX_NODE := null;
2473     G_PRX_NODE_IP_ADDRESS := null;
2474     G_PRX_PROCESS_ID := null;
2475     G_PRX_JVM_ID := null;
2476     G_PRX_THREAD_ID := null;
2477     G_PRX_AUDSID := null;
2478     G_PRX_DB_INSTANCE := null;
2479     G_PRX_SESSION_ID := null;
2480     G_PRX_USER_ID := null;
2481 
2482     --DUMP_CC;
2483   END CLEAR_CHILD_CONTEXT;
2484 
2485 /*============================================================================
2486  * Proxy Alerting related Procedures - End
2487  *===========================================================================*/
2488 
2489 
2490 
2491 /**
2492  * Log a message directly without checking if logging is enabled.
2493  * Requires a transaction_context_id of a transaction_context that
2494  * has already been created. This allows messages to be logged
2495  * to multiple contexts within the same session.
2496  *
2497  * This function should only be called by internal ATG procedures.
2498  *
2499  */
2500 FUNCTION STRING_UNCHECKED_TO_CONTEXT(LOG_LEVEL       IN NUMBER,
2501 				     MODULE          IN VARCHAR2,
2502 				     MESSAGE_TEXT    IN VARCHAR2,
2503 				     TRANSACTION_CONTEXT_ID IN NUMBER,
2504 				     ENCODED         IN VARCHAR2 DEFAULT 'N',
2505 				     SESSION_ID      IN NUMBER   DEFAULT NULL,
2506 				     USER_ID         IN NUMBER   DEFAULT NULL,
2507 				     NODE            IN VARCHAR2 DEFAULT NULL,
2508 				     NODE_IP_ADDRESS IN VARCHAR2 DEFAULT NULL,
2509 				     PROCESS_ID      IN VARCHAR2 DEFAULT NULL,
2510 				     JVM_ID          IN VARCHAR2 DEFAULT NULL,
2511 				     THREAD_ID       IN VARCHAR2 DEFAULT NULL,
2512 				     AUDSID          IN NUMBER   DEFAULT NULL,
2513 				     DB_INSTANCE     IN NUMBER   DEFAULT NULL,
2514 				     CALL_STACK      IN VARCHAR2 DEFAULT NULL,
2515 				     ERR_STACK       IN VARCHAR2 DEFAULT NULL) return NUMBER is
2516 
2517       l_log_sequence  number;
2518       saved_context   number;
2519 
2520     begin
2521 
2522       if transaction_context_id is null then
2523          return -1;
2524       end if;
2525 
2526       if(NOT SELF_INITED_X) then
2527          SELF_INIT;
2528       end if;
2529 
2530       saved_context := FND_LOG.G_TRANSACTION_CONTEXT_ID;
2531       FND_LOG.G_TRANSACTION_CONTEXT_ID := TRANSACTION_CONTEXT_ID;
2532 
2533 
2534       l_log_sequence := STR_UNCHKED_INT_WITH_CONTEXT(LOG_LEVEL       => LOG_LEVEL,
2535 						     MODULE          => MODULE,
2536 						     MESSAGE_TEXT    => MESSAGE_TEXT,
2537 						     ENCODED         => ENCODED,
2538 						     SESSION_ID      => SESSION_ID,
2539 						     USER_ID         => USER_ID,
2540 						     NODE            => NODE,
2541 						     NODE_IP_ADDRESS => NODE_IP_ADDRESS,
2542 						     PROCESS_ID      => PROCESS_ID,
2543 						     JVM_ID          => JVM_ID,
2544 						     THREAD_ID       => THREAD_ID,
2545 						     AUDSID          => AUDSID,
2546 						     DB_INSTANCE     => DB_INSTANCE,
2547 						     CALL_STACK      => CALL_STACK,
2548 						     ERR_STACK       => ERR_STACK);
2549 
2550 
2551       FND_LOG.G_TRANSACTION_CONTEXT_ID := saved_context;
2552 
2553       return l_log_sequence;
2554 
2555       end;
2556 
2557 
2558 
2559 end FND_LOG_REPOSITORY;