1 PACKAGE BODY gme_debug AS
2 /* $Header: GMEUDBGB.pls 120.5 2011/09/14 13:48:58 adeshmuk ship $ */
3 g_debug VARCHAR2 (5)
4 := NVL (fnd_profile.VALUE ('AFLOG_LEVEL')
5 ,-1);
6 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_DEBUG';
7
8 /*
9 REM *********************************************************************
10 REM *
11 REM * FILE: GMEUDBGB.pls
12 REM * PURPOSE: Package Body for the GME debug utilities
13 REM * AUTHOR: Olivier DABOVAL, OPM Development
14 REM * DATE: 27th MAY 2001
15 REM *
16 REM * PROCEDURE log_initialize
17 REM * PROCEDURE log
18 REM *
19 REM *
20 REM * HISTORY:
21 REM * ========
22 REM * 27-Jun-2001 Olivier DABOVAL
23 REM * Created
24 REM * 05/03/03 Bharati Satpute Bug 2804440 Added WHEN OTHERS
25 REM * exception which were not defined
26 REM *
27 REM * 08-OCT-2004 Shrikant Nene Bug#3865212
28 REM * As part of this bug removed the AND delete_mark = V_delete_mark
29 REM * From the cursor for display_inventory_txns_gtmp
30 REM * Also added subinventory field in the actual put_line statement
34 REM * Modifed log_initialize so that debug logs are not writtern
31 REM * in the above procedure, as it contains the original trans_id
32 REM *
33 REM * 13-SEP-2011 Archana Mundhe Bug 12947764
35 REM * when FND Debug log enabled is set to No
36 REM **********************************************************************
37 */
38
39 --========================================================================
40 -- PROCEDURE : Log_Initialize PUBLIC
41 -- COMMENT : Initializes the log facility. It should be called from
42 -- the top level procedure of each concurrent program
43 --=======================================================================--
44 PROCEDURE log_initialize (
45 p_file_name IN VARCHAR2 DEFAULT '0'
46 ,p_override IN NUMBER DEFAULT 0)
47 IS
48 l_location VARCHAR2 (500);
49 LOG UTL_FILE.file_type;
50 l_api_name CONSTANT VARCHAR2 (30) := 'log_initialize';
51
52 CURSOR c_get_1st_location
53 IS
54 SELECT NVL (SUBSTRB (VALUE, 1, INSTR (VALUE, ',') - 1), VALUE)
55 FROM v$parameter
56 WHERE NAME = 'utl_file_dir';
57 BEGIN
58 -- Bug 6789809
59 -- Set debug log mode to off and do not proceed with initialize if
60 -- FND Debug log enabled is set to No.
61 -- Setting g_log_mode to 'OFF' will prevent procdure putline from executing.
62 IF(NVL(fnd_profile.VALUE ('AFLOG_ENABLED'),-1) = 'N') THEN
63 g_log_mode := 'OFF';
64 ELSE
65 G_LOG_LEVEL := TO_NUMBER(FND_PROFILE.Value('AFLOG_LEVEL'));
66 IF G_LOG_LEVEL IS NULL THEN
67 G_LOG_MODE := 'OFF';
68 ELSE
69 IF (TO_NUMBER (fnd_profile.VALUE ('CONC_REQUEST_ID') ) > 0) THEN
70 g_log_mode := 'SRS';
71 ELSIF p_file_name <> '0' THEN
72 g_log_mode := 'LOG';
73 ELSE
74 g_log_mode := 'SQL';
75 END IF;
76 END IF;
77
78 IF (g_log_mode <> 'OFF' AND p_file_name <> '0')
79 AND (g_file_name <> p_file_name OR p_override = 1) THEN
80 IF (fnd_global.user_id > 0) THEN
81 g_log_username := fnd_global.user_name;
82 ELSE
83 g_log_username := 'GME_NO_USER';
84 END IF;
85
86 OPEN c_get_1st_location;
87
88 FETCH c_get_1st_location
89 INTO g_log_location;
90
91 CLOSE c_get_1st_location;
92
93 LOG :=
94 UTL_FILE.fopen (g_log_location
95 , g_log_username || p_file_name
96 ,'w'
97 ,32767);
98 UTL_FILE.put_line (LOG
99 , 'Log file opened at '
100 || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
101 || ' With log mode: '
102 || g_log_mode);
103 UTL_FILE.fflush (LOG);
104 UTL_FILE.fclose (LOG);
105 g_file_name := p_file_name;
106 END IF;
107 END IF;
108 EXCEPTION
109 WHEN OTHERS THEN
110 fnd_msg_pub.add_exc_msg ('GME_DEBUG', 'LOG_INITIALIZE');
111
112 IF g_debug IS NOT NULL THEN
113 gme_debug.put_line ('Error in ' || SQLERRM);
114 END IF;
115 END log_initialize;
116
117 --========================================================================
118 -- PROCEDURE : Log PUBLIC
119 -- PARAMETERS: p_level IN priority of the message - from
120 -- highest to lowest:
121 -- -- G_LOG_UNEXPECTED
122 -- -- G_LOG_ERROR
123 -- -- G_LOG_EXCEPTION
124 -- -- G_LOG_EVENT
125 -- -- G_LOG_PROCEDURE
126 -- -- G_LOG_STATEMENT
127 -- p_msg IN message to be print on the log
128 -- file
129 -- COMMENT : Add an entry to the log
130 --=======================================================================--
131 PROCEDURE put_line (
132 p_msg IN VARCHAR2
133 ,p_priority IN NUMBER DEFAULT 100
134 ,p_file_name IN VARCHAR2 DEFAULT '0')
135 IS
136 LOG UTL_FILE.file_type;
137 l_file_name VARCHAR2 (50);
138 l_api_name CONSTANT VARCHAR2 (30) := 'PUT_LINE';
139
140 CURSOR c_get_1st_location
141 IS
142 SELECT NVL (SUBSTR (VALUE, 1, INSTR (VALUE, ',') - 1), VALUE)
143 FROM v$parameter
144 WHERE NAME = 'utl_file_dir';
145 BEGIN
146 IF ( (g_log_mode <> 'OFF') AND (NVL (p_priority, 100) >= g_debug) ) THEN
147 IF g_log_mode = 'LOG' THEN
148 IF p_file_name = '0' THEN
149 l_file_name := g_file_name;
150 ELSE
151 l_file_name := p_file_name;
152 END IF;
153
154 LOG :=
155 UTL_FILE.fopen (g_log_location
156 , g_log_username || l_file_name
157 ,'a'
158 ,32767);
159 UTL_FILE.put_line (LOG, p_msg);
160 UTL_FILE.fflush (LOG);
161 UTL_FILE.fclose (LOG);
162 ELSIF (g_log_mode = 'SQL') THEN
163 -- SQL*Plus session: uncomment the next line during unit test
164 --DBMS_OUTPUT.put_line(p_msg);
165 NULL;
166 ELSE
167 -- Concurrent request
171 --Bug2804440
168 fnd_file.put_line (fnd_file.LOG, p_msg);
169 END IF;
170 END IF;
172 EXCEPTION
173 WHEN OTHERS THEN
174 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
175 END put_line;
176
177 PROCEDURE display_messages (p_msg_count IN NUMBER)
178 IS
179 MESSAGE VARCHAR2 (2000);
180 dummy NUMBER;
181 l_api_name CONSTANT VARCHAR2 (30) := 'DISPLAY_MESSAGES';
182 BEGIN
183 FOR i IN 1 .. p_msg_count LOOP
184 fnd_msg_pub.get (p_msg_index => i
185 ,p_data => MESSAGE
186 ,p_encoded => 'F'
187 ,p_msg_index_out => dummy);
188 gme_debug.put_line ('Message ' || TO_CHAR (i) || ' ' || MESSAGE);
189 END LOOP;
190 --Bug2804440
191 EXCEPTION
192 WHEN OTHERS THEN
193 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
194 END display_messages;
195
196 PROCEDURE display_resource_gtmp (
197 p_batchstep_resource_id IN NUMBER
198 ,p_batchstep_id IN NUMBER
199 ,p_batch_id IN NUMBER
200 ,p_delete_mark IN NUMBER DEFAULT 0)
201 IS
202 l_resource_ids gme_common_pvt.number_tab;
203 l_resources NUMBER;
204 i NUMBER;
205 l_api_name CONSTANT VARCHAR2 (30) := 'display_resource_gtmp';
206
207 CURSOR get_batchstep_resource_id (v_batchstep_id IN NUMBER)
208 IS
209 SELECT batchstep_resource_id
210 FROM gme_batch_step_resources
211 WHERE batchstep_id = v_batchstep_id
212 ORDER BY 1;
213
214 CURSOR get_temp_table (
215 v_doc_id IN NUMBER
216 ,v_line_id IN NUMBER
217 ,v_delete_mark IN NUMBER)
218 IS
219 SELECT *
220 FROM gme_resource_txns_gtmp
221 WHERE (doc_id = v_doc_id OR v_doc_id IS NULL)
222 AND (line_id = v_line_id OR v_line_id IS NULL)
223 AND delete_mark = v_delete_mark
224 ORDER BY doc_id, line_id, resources, poc_trans_id;
225 BEGIN
226 gme_debug.put_line ('Resource transactions temp table');
227 gme_debug.put_line
228 ('organization_id/line_id/trans_id/trans_date/resources/resource_usage/trans_um/overrided_protected_ind/completed_ind/action_code/reason_id');
229
230 IF p_batchstep_id IS NOT NULL THEN
231 OPEN get_batchstep_resource_id (p_batchstep_id);
232
233 FETCH get_batchstep_resource_id
234 BULK COLLECT INTO l_resource_ids;
235
236 CLOSE get_batchstep_resource_id;
237 ELSE
238 l_resource_ids (1) := p_batchstep_resource_id;
239 END IF;
240
241 l_resources := l_resource_ids.COUNT;
242
243 IF l_resources = 0 THEN
244 l_resources := 1;
245 END IF;
246
247 i := 1;
248
249 WHILE i <= l_resources LOOP
250 FOR rec IN get_temp_table (p_batch_id
251 ,l_resource_ids (i)
252 ,p_delete_mark) LOOP
253 gme_debug.put_line (rec.organization_id ||'/'
254 || rec.line_id
255 || '/'
256 || rec.poc_trans_id
257 || '/'
258 || TO_CHAR (rec.trans_date
259 ,'MM/DD/YYYY HH24:MI:SS')
260 || '/'
261 || rec.resources
262 || '/'
263 || rec.resource_usage
264 || '/'
265 || rec.trans_um
266 || '/'
267 || rec.overrided_protected_ind
268 || '/'
269 || rec.completed_ind
270 || '/'
271 || rec.action_code
272 || '/'
273 || rec.reason_id);
274 END LOOP;
275
276 i := i + 1;
277 END LOOP;
278 --Bug2804440
279 EXCEPTION
280 WHEN OTHERS THEN
281 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
282 --End Bug2804440
283 END display_resource_gtmp;
284
285 PROCEDURE display_exceptions_gtmp (
286 p_organization_id IN NUMBER
287 ,p_material_detail_id IN NUMBER
288 ,p_batch_id IN NUMBER)
289 IS
290 l_api_name CONSTANT VARCHAR2 (30) := 'display_exceptions_gtmp';
291
292 CURSOR get_temp_table (
293 v_organization_id IN NUMBER
294 ,v_batch_id IN NUMBER
295 ,v_material_detail_id IN NUMBER)
296 IS
297 SELECT *
298 FROM gme_exceptions_gtmp
299 WHERE organization_id = v_organization_id
300 AND (batch_id = v_batch_id OR v_batch_id IS NULL)
301 AND ( material_detail_id = v_material_detail_id
302 OR v_material_detail_id IS NULL)
303 ORDER BY batch_id, material_detail_id;
304 BEGIN
305 gme_debug.put_line ('Exceptions temp table');
306 gme_debug.put_line
307 ('batch_id/material_detail_id/Pend MO/Pend Rsv/onhand/ATT/ATR/TRANSACTED/EXCEPTION');
308
309 FOR rec IN get_temp_table (p_organization_id
310 ,p_batch_id
311 ,p_material_detail_id) LOOP
312 gme_debug.put_line ( rec.batch_id
313 || '/'
314 || rec.material_detail_id
315 || '/'
316 || rec.pending_move_order_ind
317 || '/'
318 || rec.pending_reservations_ind
319 || '/'
320 || rec.onhand_qty
321 || '/'
322 || rec.att
323 || '/'
324 || rec.atr
325 || '/'
326 || rec.transacted_qty
327 || '/'
328 || rec.exception_qty);
329 END LOOP;
330 EXCEPTION
331 WHEN OTHERS THEN
332 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
333 END display_exceptions_gtmp;
334
335 PROCEDURE display_inventory_gtmp
336 ( p_material_detail_id IN NUMBER
337 ,p_batch_id IN NUMBER
338 ,p_delete_mark IN NUMBER DEFAULT 0
339 ) IS
340
341 BEGIN
342 NULL ;
343 END display_inventory_gtmp;
344
345 /*###############################################################
346 # NAME
347 # dump_temp_txns_exceptions
348 # SYNOPSIS
349 # proc dump_temp_txns_exceptions
350 # DESCRIPTION
351 # This procedure is used to retrieve all temporary txns
352 # created by release/complete or IB processes.
353 ###############################################################*/
354 PROCEDURE dump_temp_txns_exceptions IS
355 BEGIN
356 IF (g_debug IS NOT NULL) THEN
357 gme_debug.put_line('***** Txns created automatically by release/complete/IB etc. *****');
358 FOR get_rec IN (SELECT * FROM mtl_material_transactions_temp WHERE transaction_header_id = gme_common_pvt.g_transaction_header_id) LOOP
359 gme_debug.put_line('Batch_id = '||get_rec.transaction_source_id||' material_detail_id = '||get_rec.trx_source_line_id||
360 ' inventory_item_id = '||get_rec.inventory_item_id||' revision = '||get_rec.revision||
361 ' subinventory = '||get_rec.subinventory_code||' locator_id = '||get_rec.locator_id||
362 ' transaction_quantity = '||get_rec.transaction_quantity||' transaction_uom = '||get_rec.transaction_uom||
363 ' sec_transaction_qty = '||get_rec.secondary_transaction_quantity);
364 END LOOP;
365 gme_debug.put_line('***** End Txns created automatically by release/complete/IB etc. ***** ');
366 gme_debug.put_line('***** Exceptions created by release/complete/IB etc. *****');
367 FOR get_rec IN (SELECT * FROM gme_exceptions_gtmp) LOOP
368 gme_debug.put_line('Batch_id = '||get_rec.batch_id||' material_detail_id = '||get_rec.material_detail_id||
369 ' transacted_qty = '||get_rec.transacted_qty||' exception_qty = '||get_rec.exception_qty||
370 ' pending_move_order = '||get_rec.pending_move_order_ind||' pending_rsv_ind = '||get_rec.pending_reservations_ind);
371 END LOOP;
372 gme_debug.put_line('***** End Exceptions created by release/complete/IB etc. *****');
373 END IF;
374 END dump_temp_txns_exceptions;
375
376 END gme_debug;
[Home] [Help]