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