1 PACKAGE WSM_log_PVT AUTHID CURRENT_USER AS
2 /* $Header: WSMVLOGS.pls 120.0 2005/06/21 04:03 sthangad noship $ */
3
4 -- FND_LOG_LEVELS
5 -- --------------
6 --
7 -- LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
8 -- LEVEL_ERROR CONSTANT NUMBER := 5;
9 -- LEVEL_EXCEPTION CONSTANT NUMBER := 4;
10 -- LEVEL_EVENT CONSTANT NUMBER := 3;
11 -- LEVEL_PROCEDURE CONSTANT NUMBER := 2;
12 -- LEVEL_STATEMENT CONSTANT NUMBER := 1;
13 --
14 -- FND_LOG.G_CURRENT_RUNTIME_LEVEL; --> Will give the current runtime log level....
15 --
16 -- Logging Level Value Meaning
17 -- ---------------------------------------------------------------------------------------------------------------------------------------------------------
18 --
19 -- OFF 0 Logging is disabled. <<----- For administrative use only (Java layer only) ----->>
20 --
21 -- STATEMENT 1 Low severity message offering maximum detail. <<----- Internal development and support teams ----->>
22 --
23 -- "Obtained Connection from Pool"
24 -- "Got request parameter"
25 -- "Set Cookie with name, value"
26 --
27 -- PROCEDURE 2 Logging message called upon entry and/or exit from a method. <<------ Internal development and support teams ------>>
28 --
29 -- "Calling PL/SQL proc xyz"
30 -- "Returning from PL/SQL proc xyz"
31 --
32 -- EVENT 3 A significant milestone in the normal execution path of an application. <<------ Internal development and support teams ------>>
33 --
34 -- "User authenticated successfully"
35 -- "Retrieved user preferences successfully"
36 -- "Menu rendering completed"
37 --
38 -- EXCEPTION 4 A lower-level API is returning a failure code or exception,
39 -- but the error does not necessarily indicate a problem at the level of the calling code. <<----- Internal development and support teams ------->>
40 --
41 -- "Profile not found"
42 -- "Region not found"
43 -- "Network routine could not connect; retrying"
44 --
45 -- ERROR 5 An error message to the end-user. <<------ Customer sysadmins, internal development and support teams ------>>
46 --
47 -- "Invalid username or password."
48 -- "User entered a duplicate value for field."
49 --
50 -- UNEXPECTED 6 An unexpected situation occurred which is likely to indicate or cause
51 -- instabilities in the runtime behavior, and which the system administrator
52 -- needs to take action on. <<------ Customer sysadmins, internal development and support teams ----------->>
53 --
54 -- "Out of memory"
55 -- "Required file not found"
56 -- "Data integrity error"
57 -- "Network integrity error"
58 -- "Configuration error; required property not set, cannot access configuration file."
59 -- "Failed to place new order due to DB SQLException."
60 -- "Failed to obtain Connection for processing request. "
61 --
62 --
63 -- FND_MSG_PUB levels
64 -- -------------------
65 --
66 -- FND_PROFILE.VALUE('FND_AS_MSG_LEVEL_THRESHOLD') ); --> will give the current msg logging level..
67 --
68 -- G_MSG_LVL_UNEXP_ERROR CONSTANT NUMBER := 60;
69 -- G_MSG_LVL_ERROR CONSTANT NUMBER := 50;
70 -- G_MSG_LVL_SUCCESS CONSTANT NUMBER := 40;
71 -- G_MSG_LVL_DEBUG_HIGH CONSTANT NUMBER := 30;
72 -- G_MSG_LVL_DEBUG_MEDIUM CONSTANT NUMBER := 20;
73 -- G_MSG_LVL_DEBUG_LOW CONSTANT NUMBER := 10;
74 --
75 --
76 -- 1. For all messages we should consider
77 -- a. Alert Category
78 -- i. Product
79 -- ii. System
80 -- iii. NULL
81 -- b. Alert Severity
82 -- i. Critical
83 -- ii. Error
84 -- iii. Warning
85 -- iv. NULL
86 -- c. Log Severity
87 -- i. Unexpected
88 -- ii. Error
89 -- iii. Exception
90 -- iv. Event
91 -- v. Procedure
92 -- vi. Statement
93 --
94 -- 2. For now, the only alertable message is, WSM_GENERIC_ERROR - 'An unexpected error has occurred.
95 -- Please contact your System Administrator' with 'Alert Category = System' and 'Alert Severity = Error'
96 --
97 -- 3. Message Categories
98 -- (A1) SQL error: SQLERRM
99 -- (A2) SQL error: generic error message WSM_GENERIC_ERROR
100 -- (B) Functional error: translated message in wsmmsg.ldt
101 -- (C1) Functional warning: translated message in wsmmsg.ldt
102 -- (C2) Warning, defaulting: translated message WSM_DEFAULTING_WARNING
103 -- (D) Progress, high level: for example "starting LBJ worker..."
104 -- (E) Progress, detailed level: for example, number of rows inserted,
105 -- calling a procedure, etc.
106 -- (F) Statement level info
107 --
108 -- 4. Places to log
109 -- a. Messaging: put message into FND_MSG_PUB. Controlled by 'FND: Message Level Threshold'.
110 -- The called procedure will write message into FND_MSG_PUB by calling FND_MSG_PUB.Add
111 --
112 -- Profile = 60 will write (A2)
113 -- Profile = 50 will write above + All (B)
114 -- Profile = 40 will write above + (C1)
115 -- Profile = 30 will write above + (C2)
116 --
117 -- b. FND Logging: put message into FND_LOG table
118 --
119 -- Profile = Unexpected Error, log (A2) + (A1)
120 -- Profile = Expected Error, log above + All (B)
121 -- Profile = Exception, log above + (C1) + (C2)
122 -- Profile = Event, log above + (D)
123 -- Profile = Procedure, log above + (E)
124 -- Profile = Statement, log above + (F)
125 --
126 -- c. Write into WIE: this is for interface only. We will use the same profile 'FND: Message Level Threshold' to
127 -- control this to log the (A2) + (B) + (C1) + (C2)
128 --
129 -- d. Concurrent Logging: FND_FILE.put_line, this is also for interface only.
130 --
131 -- We will ignore MRP_DEBUG profile and only log (A2) + (B). The concurrent log will only have high level translated information like:
132 -- i. Inside the manager, 'Start PROGRAM_NAME'
133 -- ii. When launching any request inside the manager 'Launching <concurrent program> in concurrent request REQUEST_ID'
134 -- iii. At the end, 'End PROGRAM_NAME'
135 -- iv. Inside the worker, 'Start PROGRAM_NAME'
136 -- v. In case of error only, show the record info like
137 -- 'The above error occurred when processing the record with header HEADER_ID and job JOB_NAME' after the actual error.
138 -- vi. When launching any request inside the worker 'Launching <concurrent program> in concurrent request REQUEST_ID'
139 -- vii. At the end, 'Total number of records = TOTAL_NO_REC, Successful records = SUC_NO_REC and Errored records = ERR_NO_REC'.
140 -- viii. Finally, if there are warnings, 'Please check WSM_INTERFACE_ERRORS for warnings during this request'
141 -- ix. At the end, 'End PROGRAM_NAME'
142 --
143 -- 5. Every message in FND LOG of level unexpected or error, should have a corresponding message at level
144 -- unexpected or error with all the debug details. Module will have the program name, stmt no etc.
145 -- 6. We can define the g_error_msg_tbl in WSMPUTIL as global PL/SQL table and use it in our interfaces.
146 -- 7. Due to the auto logging done by the logging framework we can have duplicate messages in the FND_LOG table in some situations.
147 -- 8. Store FND_LOG.CURRENT_RUNTIME_LEVEL by creating global variables in each package
148
149 -- Parameter PL/SQL tables..
150 TYPE param_rec_type is record(paramName VARCHAR2(255), paramValue VARCHAR2(255));
151 TYPE param_tbl_type is table of param_rec_type index by binary_integer;
152
153 -- PL/SQL table for WIE..
154 TYPE error_msg_tbl_type is table of WSM_INTERFACE_ERRORS%ROWTYPE index by binary_integer;
155 g_error_msg_tbl WSM_log_PVT.error_msg_tbl_type;
156
157
158 TYPE token_rec_type IS RECORD
159 (
160 TokenName varchar2(2000),
161 TokenValue varchar2(2000)
162 );
163
164 TYPE token_rec_tbl is table of token_rec_type index by binary_integer;
165
166
167 -- This procedure will be invoked to log the parameters...
168 PROCEDURE LogProcParams ( p_module_name IN varchar2 ,
169 p_param_tbl IN WSM_log_PVT.param_tbl_type,
170 p_fnd_log_level IN number
171 );
172
173 -- This procedure is to Log a message..(a non-translated logging message or a transalated message)
174 -- When the message name is passed, the message will be logged into
175 -- FND_MSG_PUB and WIE table if logging is enabled
176 -- If p_msg_text is not null, the message is considered as a non-translated
177 -- and will be logged into FND_LOG_MESSAGES table alone
178 PROCEDURE LogMessage ( p_module_name IN varchar2 ,
179 p_msg_name IN varchar2 DEFAULT NULL,
180 p_msg_appl_name IN VARCHAR2 DEFAULT NULL,
181 p_msg_text IN varchar2 DEFAULT NULL,
182 p_stmt_num IN NUMBER DEFAULT NULL,
183 p_msg_tokens IN token_rec_tbl ,
184 -- pass 1 to p_wsm_warning if the message is a a warning message (purely for WIE purposes..)
185 p_wsm_warning IN NUMBER DEFAULT NULL,
186 p_fnd_msg_level IN NUMBER DEFAULT NULL,
187 p_fnd_log_level IN NUMBER ,
188 p_run_log_level IN NUMBER
189 );
190
191 -- This procedure is to handle any ORA exception and will be invoked from WHEN OTHERS exception handler
192 PROCEDURE handle_others ( p_module_name IN varchar2,
193 p_stmt_num IN NUMBER,
194 p_fnd_log_level IN NUMBER,
195 p_run_log_level IN NUMBER
196 );
197
198 -- This procedure is to write the messages in the PL/SQL table g_error_msg_tbl to the database ...
199 PROCEDURE WriteToWIE;
200
201 -- This procedure will be used to update the g_error_msg_tbl with
202 -- error messages populated by the other product API's
203 Procedure update_errtbl (p_start_index IN NUMBER,
204 p_end_index IN NUMBER
205 );
206
207 -- Global variable indicating interface code.
208 -- When set to 1, the PL/SQL table g_error_msg_tbl will be filled with the messages
209 -- which will be then written to the WSM_INTERFACE_ERRORS table by the interface program by invoking WriteToWIE
210 g_write_to_WIE NUMBER := 0;
211 g_header_id NUMBER;
212 g_txn_id NUMBER;
213
214 -- Populate Interface information...
215 -- This will also set the Global variable g_conc_log_enabled to 1.
216 -- When g_conc_log_enabled is set to 1 all event and above log messages at event level and above will
217 -- be logged to the log file and the based on MSG_LEVEL messages will be added to the WIE global PL/SQL
218 -- table..
219 Procedure PopulateIntfInfo ( p_header_id IN NUMBER,
220 p_txn_id IN NUMBER
221 );
222
223
224 END WSM_log_PVT;
[Home] [Help]