[Home] [Help]
PACKAGE BODY: APPS.BIS_BIA_MSGLOG_PKG
Source
1 PACKAGE BODY BIS_BIA_MSGLOG_PKG AS
2 /* $Header: BISPMVRB.pls 120.0 2005/06/01 15:35:25 appldev noship $ */
3
4 PROCEDURE Get_Sql ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
5 x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 l_sqltext VARCHAR2(20000);
9 l_sql_stmt VARCHAR2(20000);
10 l_org VARCHAR2(20000);
11 l_cat VARCHAR2(20000);
12 l_prod VARCHAR2(20000);
13 l_org_where VARCHAR2(20000);
14 l_cat_where VARCHAR2(20000);
15 l_prod_where VARCHAR2(20000);
16
17 l_temp VARCHAR2(240);
18 l_currency VARCHAR2(240);
19 l_period_type VARCHAR2(30);
20 l_bklg_amt VARCHAR2(20);
21 l_g_currency VARCHAR2(15) := '''FII_GLOBAL1''';
22 l_cur_suffix VARCHAR2(1);
23
24 l_custom_rec BIS_QUERY_ATTRIBUTES;
25
26 l_span NUMBER;
27 l_item_cat_flag NUMBER; -- 0 for product and 1 for product category; 3 for no item dimension
28
29 pname VARCHAR2(1024) := NULL;
30 pvalue VARCHAR2(1024) := NULL;
31 message varchar2(1024) := '%';
32 session_id_value varchar2(1024) := null;
33 object_key_value varchar2(1024) := null;
34 bis_prefix varchar2(4) := 'bis';
35
36
37 BEGIN
38
39 bis_prefix := bis_prefix || '.';
40
41
42
43 FOR i IN 1..p_param.count LOOP
44 pname := p_param(i).parameter_name;
45 pvalue := p_param(i).parameter_value;
46
47 if pname = 'BIS_LOG_MESSAGE' and upper(pvalue) <> 'ALL' then
48 message := '%' || pvalue || '%';
49 end if;
50
51 if pname = 'BIS_ICX_SESSION_ID' then
52 session_id_value := pvalue;
53 end if;
54
55 if pname = 'BIS_OBJECT_KEY' then
56 object_key_value := bis_prefix || pvalue || '.%.TIME' ;
57 end if;
58
59 END LOOP;
60
61
62
63
64 l_sql_stmt := '
65 SELECT /*+ index(a , fnd_log_messages_u1) */
66 b.PROGRESS_NAME BIS_LOG_MODULE,
67 a.MESSAGE_TEXT BIS_LOG_MESSAGE_TEXT,
68 b.DURATION_TXT BIS_LOG_DURATION,
69 b.START_DATE_TXT BIS_LOG_START,
70 b.END_DATE_TXT BIS_LOG_END
71 FROM FND_LOG_MESSAGES a ,
72 (SELECT /*+ ordered */
73 max(timestamp) over( partition by msg.session_id,module) max_timestamp,
74 max(log_sequence) over(partition by msg.session_id,module) max_seq,
75 min(timestamp) over( partition by msg.session_id,module) min_timestamp,
76 min(log_sequence) over(partition by msg.session_id,module) min_seq,
77 msg.session_id,
78 substr(MODULE, instr(MODULE, '''|| bis_prefix ||''') + 4 , instr(MODULE, ''.'', 1, 2 ) - instr(MODULE, '''|| bis_prefix ||''') - 4 ) OBJECT_KEY,
79 substr(MODULE, instr(MODULE, '''|| bis_prefix ||''') + 4, instr(MODULE, ''__'') - instr(MODULE, '''|| bis_prefix ||''') - 4 ) OBJECT_NAME,
80 substr(MODULE, instr(MODULE, ''__'', 1, 2) + 2 , instr(MODULE, ''.'', -1, 2) - instr(MODULE, ''__'', 1, 2) -2 ) OBJECT_TYPE,
81 substr(MODULE, instr(MODULE, ''.'', -1, 2) + 1, instr(MODULE, ''.'', -1, 1) - instr(MODULE, ''.'', -1, 2) -1 ) PROGRESS_NAME,
82 substr(MESSAGE_TEXT, 1, instr(MESSAGE_TEXT,''#'', 1, 1) -1 ) START_DATE_TXT,
83 substr(MESSAGE_TEXT, instr(MESSAGE_TEXT,''#'', 1, 1) + 1, instr(MESSAGE_TEXT,''#'', 1, 2) - instr(MESSAGE_TEXT,''#'', 1, 1) -1) END_DATE_TXT,
84 substr(MESSAGE_TEXT, instr(MESSAGE_TEXT,''#'', 1, 2) + 1, length(MESSAGE_TEXT)) DURATION_TXT
85 FROM FND_LOG_TRANSACTION_CONTEXT ctx, fnd_log_messages msg
86 where msg.session_id = :BIS_ICX_SESSION_ID
87 and msg.log_level = 6
88 and msg.module like &BIS_OBJECT_KEY
89 AND ctx.TRANSACTION_CONTEXT_ID = msg.TRANSACTION_CONTEXT_ID
90 AND ctx.creation_date > sysdate - 0.5
91 AND msg.session_id = ctx.session_id
92 and ctx.session_id = :BIS_ICX_SESSION_ID
93 and msg.timestamp between sysdate - 0.5 and sysdate
94 ) b
95 where a.session_id = b.session_id
96 and b.START_DATE_TXT is not null
97 and b.max_seq >= a.log_sequence
98 and b.min_seq <= a.log_sequence
99 and b.max_timestamp >= a.timestamp
100 and b.min_timestamp <= a.timestamp
101 and a.module like '''|| bis_prefix ||'''||b.object_key||''.''|| b.PROGRESS_NAME ||''.%''
102 and substr(MODULE, instr(MODULE, ''.'', -1, 1) +1, length(MODULE) - instr(MODULE, ''.'', -1, 1) ) <> ''TIME''
103 and upper(a.message_text) like UPPER(:BIS_MSG_CRT)
104 &ORDER_BY_CLAUSE NULLS LAST';
105
106
107 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
108 x_custom_output := bis_query_attributes_tbl();
109 x_custom_sql := l_sql_stmt;
110
111 l_custom_rec.attribute_name := ':BIS_ICX_SESSION_ID';
112 l_custom_rec.attribute_value := session_id_value;
113 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
114 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.INTEGER_BIND;
115 x_custom_output.extend;
116 x_custom_output(1) := l_custom_rec;
117
118 l_custom_rec.attribute_name := ':BIS_MSG_CRT';
119 l_custom_rec.attribute_value := message;
120 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
121 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
122 x_custom_output.extend;
123 x_custom_output(2) := l_custom_rec;
124
125 END GET_SQL ;
126 END; -- Package Body BIS_BIA_MSGLOG_PKG