[Home] [Help]
129: -- SETUP DEBUGGING
130: --
131: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
132: IF l_debug_on IS NULL THEN
133: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
134: END IF;
135:
136: IF (g_user_debug = 1) THEN
137: l_debug_on := TRUE;
138: END IF;
139:
140:
141: IF l_debug_on THEN
142: WSH_DEBUG_SV.push(l_module_name);
143: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
144: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
145: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
146: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
139:
140:
141: IF l_debug_on THEN
142: WSH_DEBUG_SV.push(l_module_name);
143: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
144: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
145: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
146: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
147: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
140:
141: IF l_debug_on THEN
142: WSH_DEBUG_SV.push(l_module_name);
143: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
144: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
145: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
146: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
147: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
148: WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
141: IF l_debug_on THEN
142: WSH_DEBUG_SV.push(l_module_name);
143: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
144: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
145: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
146: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
147: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
148: WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
149: WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
142: WSH_DEBUG_SV.push(l_module_name);
143: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
144: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
145: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
146: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
147: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
148: WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
149: WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
150: WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
143: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
144: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
145: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
146: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
147: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
148: WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
149: WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
150: WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
151: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
144: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
145: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
146: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
147: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
148: WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
149: WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
150: WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
151: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
152: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
145: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
146: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
147: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
148: WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
149: WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
150: WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
151: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
152: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
153: END IF;
146: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
147: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
148: WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
149: WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
150: WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
151: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
152: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
153: END IF;
154:
147: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
148: WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
149: WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
150: WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
151: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
152: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
153: END IF;
154:
155:
148: WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
149: WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
150: WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
151: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
152: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
153: END IF;
154:
155:
156: IF l_debug_on THEN
153: END IF;
154:
155:
156: IF l_debug_on THEN
157: WSH_DEBUG_SV.logmsg(l_module_name,'Calling fnd_global.apps_initialize with p_user_id,p_resp_id,p_resp_appl_id');
158: END IF;
159:
160: fnd_global.apps_initialize(user_id => p_user_id,
161: resp_id => p_resp_id,
163:
164:
165:
166: IF l_debug_on THEN
167: WSH_DEBUG_SV.logmsg(l_module_name,'Calling FTE_BULKLOAD_PKG.GET_UPLOAD_DIR to get the file directory');
168: END IF;
169:
170:
171: x_src_filedir := FTE_BULKLOAD_PKG.GET_UPLOAD_DIR;
171: x_src_filedir := FTE_BULKLOAD_PKG.GET_UPLOAD_DIR;
172:
173:
174: IF l_debug_on THEN
175: WSH_DEBUG_SV.log(l_module_name,'x_src_filedir = ',x_src_filedir);
176: END IF;
177:
178:
179: IF l_debug_on THEN
176: END IF;
177:
178:
179: IF l_debug_on THEN
180: WSH_DEBUG_SV.logmsg(l_module_name,'Calling FND_REQUEST.SUBMIT_REQUEST to submoit the request to the concurrent program');
181: END IF;
182:
183:
184: x_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'FTE',
191: argument3 => x_src_filedir,
192: argument4 => p_user_debug);
193:
194: IF l_debug_on THEN
195: WSH_DEBUG_SV.log(l_module_name,'x_request_id = ',x_request_id);
196: END IF;
197:
198:
199: x_error_msg_text := fnd_message.get;
200:
201: commit;
202:
203: IF l_debug_on THEN
204: WSH_DEBUG_SV.log(l_module_name, 'x_error_msg_text', x_error_msg_text);
205: WSH_DEBUG_SV.log(l_module_name, 'x_src_filedir', x_src_filedir);
206: WSH_DEBUG_SV.log(l_module_name, 'x_request_id', x_request_id);
207: WSH_DEBUG_SV.log(l_module_name, 'p_user_id', p_user_id);
208: WSH_DEBUG_SV.log(l_module_name, 'p_resp_id', p_resp_id);
201: commit;
202:
203: IF l_debug_on THEN
204: WSH_DEBUG_SV.log(l_module_name, 'x_error_msg_text', x_error_msg_text);
205: WSH_DEBUG_SV.log(l_module_name, 'x_src_filedir', x_src_filedir);
206: WSH_DEBUG_SV.log(l_module_name, 'x_request_id', x_request_id);
207: WSH_DEBUG_SV.log(l_module_name, 'p_user_id', p_user_id);
208: WSH_DEBUG_SV.log(l_module_name, 'p_resp_id', p_resp_id);
209: WSH_DEBUG_SV.log(l_module_name, 'p_resp_appl_id', p_resp_appl_id);
202:
203: IF l_debug_on THEN
204: WSH_DEBUG_SV.log(l_module_name, 'x_error_msg_text', x_error_msg_text);
205: WSH_DEBUG_SV.log(l_module_name, 'x_src_filedir', x_src_filedir);
206: WSH_DEBUG_SV.log(l_module_name, 'x_request_id', x_request_id);
207: WSH_DEBUG_SV.log(l_module_name, 'p_user_id', p_user_id);
208: WSH_DEBUG_SV.log(l_module_name, 'p_resp_id', p_resp_id);
209: WSH_DEBUG_SV.log(l_module_name, 'p_resp_appl_id', p_resp_appl_id);
210: WSH_DEBUG_SV.pop(l_module_name);
203: IF l_debug_on THEN
204: WSH_DEBUG_SV.log(l_module_name, 'x_error_msg_text', x_error_msg_text);
205: WSH_DEBUG_SV.log(l_module_name, 'x_src_filedir', x_src_filedir);
206: WSH_DEBUG_SV.log(l_module_name, 'x_request_id', x_request_id);
207: WSH_DEBUG_SV.log(l_module_name, 'p_user_id', p_user_id);
208: WSH_DEBUG_SV.log(l_module_name, 'p_resp_id', p_resp_id);
209: WSH_DEBUG_SV.log(l_module_name, 'p_resp_appl_id', p_resp_appl_id);
210: WSH_DEBUG_SV.pop(l_module_name);
211: END IF;
204: WSH_DEBUG_SV.log(l_module_name, 'x_error_msg_text', x_error_msg_text);
205: WSH_DEBUG_SV.log(l_module_name, 'x_src_filedir', x_src_filedir);
206: WSH_DEBUG_SV.log(l_module_name, 'x_request_id', x_request_id);
207: WSH_DEBUG_SV.log(l_module_name, 'p_user_id', p_user_id);
208: WSH_DEBUG_SV.log(l_module_name, 'p_resp_id', p_resp_id);
209: WSH_DEBUG_SV.log(l_module_name, 'p_resp_appl_id', p_resp_appl_id);
210: WSH_DEBUG_SV.pop(l_module_name);
211: END IF;
212:
205: WSH_DEBUG_SV.log(l_module_name, 'x_src_filedir', x_src_filedir);
206: WSH_DEBUG_SV.log(l_module_name, 'x_request_id', x_request_id);
207: WSH_DEBUG_SV.log(l_module_name, 'p_user_id', p_user_id);
208: WSH_DEBUG_SV.log(l_module_name, 'p_resp_id', p_resp_id);
209: WSH_DEBUG_SV.log(l_module_name, 'p_resp_appl_id', p_resp_appl_id);
210: WSH_DEBUG_SV.pop(l_module_name);
211: END IF;
212:
213: RETURN;
206: WSH_DEBUG_SV.log(l_module_name, 'x_request_id', x_request_id);
207: WSH_DEBUG_SV.log(l_module_name, 'p_user_id', p_user_id);
208: WSH_DEBUG_SV.log(l_module_name, 'p_resp_id', p_resp_id);
209: WSH_DEBUG_SV.log(l_module_name, 'p_resp_appl_id', p_resp_appl_id);
210: WSH_DEBUG_SV.pop(l_module_name);
211: END IF;
212:
213: RETURN;
214:
220: --
221: -- Debug Statements
222: --
223: IF l_debug_on THEN
224: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.BULK_LOAD_DTT IS ' ||l_error_text);
225: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||l_error_text,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
226: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
227: END IF;
228:
221: -- Debug Statements
222: --
223: IF l_debug_on THEN
224: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.BULK_LOAD_DTT IS ' ||l_error_text);
225: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||l_error_text,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
226: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
227: END IF;
228:
229: RETURN;
222: --
223: IF l_debug_on THEN
224: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.BULK_LOAD_DTT IS ' ||l_error_text);
225: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||l_error_text,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
226: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
227: END IF;
228:
229: RETURN;
230:
301: -- SETUP DEBUGGING
302: --
303: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
304: IF l_debug_on IS NULL THEN
305: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
306: END IF;
307:
308: IF (g_user_debug = 1) THEN
309: l_debug_on := TRUE;
313: --
314: -- Debug Statements
315: --
316: IF l_debug_on THEN
317: WSH_DEBUG_SV.push(l_module_name);
318: WSH_DEBUG_SV.logmsg(l_module_name,'-------LOAD_DTT_FILE-------');
319: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
320: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
321: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
314: -- Debug Statements
315: --
316: IF l_debug_on THEN
317: WSH_DEBUG_SV.push(l_module_name);
318: WSH_DEBUG_SV.logmsg(l_module_name,'-------LOAD_DTT_FILE-------');
319: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
320: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
321: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
322: WSH_DEBUG_SV.log(l_module_name,'p_src_filedir',p_src_filedir);
315: --
316: IF l_debug_on THEN
317: WSH_DEBUG_SV.push(l_module_name);
318: WSH_DEBUG_SV.logmsg(l_module_name,'-------LOAD_DTT_FILE-------');
319: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
320: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
321: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
322: WSH_DEBUG_SV.log(l_module_name,'p_src_filedir',p_src_filedir);
323: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
316: IF l_debug_on THEN
317: WSH_DEBUG_SV.push(l_module_name);
318: WSH_DEBUG_SV.logmsg(l_module_name,'-------LOAD_DTT_FILE-------');
319: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
320: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
321: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
322: WSH_DEBUG_SV.log(l_module_name,'p_src_filedir',p_src_filedir);
323: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
324: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
317: WSH_DEBUG_SV.push(l_module_name);
318: WSH_DEBUG_SV.logmsg(l_module_name,'-------LOAD_DTT_FILE-------');
319: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
320: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
321: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
322: WSH_DEBUG_SV.log(l_module_name,'p_src_filedir',p_src_filedir);
323: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
324: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
325: END IF;
318: WSH_DEBUG_SV.logmsg(l_module_name,'-------LOAD_DTT_FILE-------');
319: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
320: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
321: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
322: WSH_DEBUG_SV.log(l_module_name,'p_src_filedir',p_src_filedir);
323: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
324: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
325: END IF;
326:
319: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
320: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
321: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
322: WSH_DEBUG_SV.log(l_module_name,'p_src_filedir',p_src_filedir);
323: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
324: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
325: END IF;
326:
327:
320: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
321: WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
322: WSH_DEBUG_SV.log(l_module_name,'p_src_filedir',p_src_filedir);
323: WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
324: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
325: END IF;
326:
327:
328: --
328: --
329: -- Debug Statements
330: --
331: IF l_debug_on THEN
332: WSH_DEBUG_SV.logmsg(l_module_name,'Check If this is the first time by calling function FIRST_TIME');
333: END IF;
334:
335: IF (FIRST_TIME) THEN
336:
337: --
338: -- Debug Statements
339: --
340: IF l_debug_on THEN
341: WSH_DEBUG_SV.logmsg(l_module_name,'This is the first time calling FTE_BULK_DTT_PKG.READ_DTT_FILE');
342: WSH_DEBUG_SV.logmsg(l_module_name,'with parameters: p_src_filedir,p_src_filename,p_load_id');
343: END IF;
344:
345: FND_FILE.PUT_LINE(FND_FILE.log, 'Calling READ_DTT_FILE');
338: -- Debug Statements
339: --
340: IF l_debug_on THEN
341: WSH_DEBUG_SV.logmsg(l_module_name,'This is the first time calling FTE_BULK_DTT_PKG.READ_DTT_FILE');
342: WSH_DEBUG_SV.logmsg(l_module_name,'with parameters: p_src_filedir,p_src_filename,p_load_id');
343: END IF;
344:
345: FND_FILE.PUT_LINE(FND_FILE.log, 'Calling READ_DTT_FILE');
346:
353: --
354: -- Debug Statements
355: --
356: IF l_debug_on THEN
357: WSH_DEBUG_SV.logmsg(l_module_name,'Back from FTE_BULK_DTT_PKG.READ_DTT_FILE');
358: WSH_DEBUG_SV.log(l_module_name,'x_status',x_status);
359: END IF;
360:
361: END IF;
354: -- Debug Statements
355: --
356: IF l_debug_on THEN
357: WSH_DEBUG_SV.logmsg(l_module_name,'Back from FTE_BULK_DTT_PKG.READ_DTT_FILE');
358: WSH_DEBUG_SV.log(l_module_name,'x_status',x_status);
359: END IF;
360:
361: END IF;
362:
364: --
365: -- Debug Statements
366: --
367: IF l_debug_on THEN
368: WSH_DEBUG_SV.logmsg(l_module_name,'The file Reading and upload was successful');
369: END IF;
370:
371: --
372: -- Concurrent Manager expects 0 for success.
377: --
378: -- Debug Statements
379: --
380: IF l_debug_on THEN
381: WSH_DEBUG_SV.logmsg(l_module_name,'Errors occurred during the upload');
382: END IF;
383:
384: p_retcode := 2;
385: p_errbuf := 'COMPLETED WITH ERRORS. ' || p_errbuf || '. Please Check Logs for more details.';
389: --
390: -- Debug Statements
391: --
392: IF l_debug_on THEN
393: WSH_DEBUG_SV.pop(l_module_name);
394: END IF;
395: --
396: RETURN;
397:
406: --
407: -- Debug Statements
408: --
409: IF l_debug_on THEN
410: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.LOAD_DTT_FILE IS ' ||l_error_text);
411: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||l_error_text,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
412: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
413: END IF;
414:
407: -- Debug Statements
408: --
409: IF l_debug_on THEN
410: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.LOAD_DTT_FILE IS ' ||l_error_text);
411: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||l_error_text,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
412: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
413: END IF;
414:
415: RETURN;
408: --
409: IF l_debug_on THEN
410: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.LOAD_DTT_FILE IS ' ||l_error_text);
411: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||l_error_text,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
412: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
413: END IF;
414:
415: RETURN;
416:
760: -- SETUP DEBUGGING
761: --
762: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
763: IF l_debug_on IS NULL THEN
764: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
765: END IF;
766:
767: IF (g_user_debug = 1) THEN
768: l_debug_on := TRUE;
769: END IF;
770:
771:
772: IF l_debug_on THEN
773: WSH_DEBUG_SV.push(l_module_name);
774: WSH_DEBUG_SV.logmsg(l_module_name,'---------------READ_DTT_FILE------------');
775: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
776: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
777: WSH_DEBUG_SV.log(l_module_name,'p_source_file_directory',p_source_file_directory);
770:
771:
772: IF l_debug_on THEN
773: WSH_DEBUG_SV.push(l_module_name);
774: WSH_DEBUG_SV.logmsg(l_module_name,'---------------READ_DTT_FILE------------');
775: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
776: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
777: WSH_DEBUG_SV.log(l_module_name,'p_source_file_directory',p_source_file_directory);
778: WSH_DEBUG_SV.log(l_module_name,'p_source_file_name',p_source_file_name);
771:
772: IF l_debug_on THEN
773: WSH_DEBUG_SV.push(l_module_name);
774: WSH_DEBUG_SV.logmsg(l_module_name,'---------------READ_DTT_FILE------------');
775: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
776: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
777: WSH_DEBUG_SV.log(l_module_name,'p_source_file_directory',p_source_file_directory);
778: WSH_DEBUG_SV.log(l_module_name,'p_source_file_name',p_source_file_name);
779: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
772: IF l_debug_on THEN
773: WSH_DEBUG_SV.push(l_module_name);
774: WSH_DEBUG_SV.logmsg(l_module_name,'---------------READ_DTT_FILE------------');
775: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
776: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
777: WSH_DEBUG_SV.log(l_module_name,'p_source_file_directory',p_source_file_directory);
778: WSH_DEBUG_SV.log(l_module_name,'p_source_file_name',p_source_file_name);
779: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
780: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
773: WSH_DEBUG_SV.push(l_module_name);
774: WSH_DEBUG_SV.logmsg(l_module_name,'---------------READ_DTT_FILE------------');
775: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
776: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
777: WSH_DEBUG_SV.log(l_module_name,'p_source_file_directory',p_source_file_directory);
778: WSH_DEBUG_SV.log(l_module_name,'p_source_file_name',p_source_file_name);
779: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
780: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
781: END IF;
774: WSH_DEBUG_SV.logmsg(l_module_name,'---------------READ_DTT_FILE------------');
775: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
776: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
777: WSH_DEBUG_SV.log(l_module_name,'p_source_file_directory',p_source_file_directory);
778: WSH_DEBUG_SV.log(l_module_name,'p_source_file_name',p_source_file_name);
779: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
780: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
781: END IF;
782:
775: WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
776: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
777: WSH_DEBUG_SV.log(l_module_name,'p_source_file_directory',p_source_file_directory);
778: WSH_DEBUG_SV.log(l_module_name,'p_source_file_name',p_source_file_name);
779: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
780: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
781: END IF;
782:
783:
776: WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
777: WSH_DEBUG_SV.log(l_module_name,'p_source_file_directory',p_source_file_directory);
778: WSH_DEBUG_SV.log(l_module_name,'p_source_file_name',p_source_file_name);
779: WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
780: WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
781: END IF;
782:
783:
784: FND_FILE.PUT_LINE(FND_FILE.log, 'READ_DTT_FILE');
800: --
801: -- Input file name is null raise an error
802: --
803: IF l_debug_on THEN
804: WSH_DEBUG_SV.logmsg(l_module_name,'Input file name is null raise FTE_DIST_NULL_FILE_NAME exception');
805: END IF;
806:
807: RAISE FTE_DIST_NULL_FILE_NAME;
808: --
808: --
809: -- Debug Statements
810: --
811: IF l_debug_on THEN
812: WSH_DEBUG_SV.pop(l_module_name);
813: END IF;
814: --
815: RETURN;
816: END IF;
820: --
821: -- Take off the file extension
822: --
823: IF l_debug_on THEN
824: WSH_DEBUG_SV.logmsg(l_module_name,'Take off the file extension');
825: END IF;
826:
827: FND_FILE.PUT_LINE(FND_FILE.log, 'Removing File Extension from file');
828:
833: --
834: IF (length(l_dtt_file_name) <> 8) THEN
835:
836: IF l_debug_on THEN
837: WSH_DEBUG_SV.logmsg(l_module_name,'Input file name is not 8 chars in length raise FTE_DIST_INV_FILENAME_LGTH exception');
838: END IF;
839:
840: RAISE FTE_DIST_INV_FILENAME_LGTH;
841:
842: --
843: -- Debug Statements
844: --
845: IF l_debug_on THEN
846: WSH_DEBUG_SV.pop(l_module_name);
847: END IF;
848: --
849: RETURN;
850: END IF;
852:
853: FND_FILE.PUT_LINE(FND_FILE.log, 'Retrieving the download file id');
854: --
855: IF l_debug_on THEN
856: WSH_DEBUG_SV.log(l_module_name,'Get the download file id open cursor c_get_file_info l_dtt_file_name = ',l_dtt_file_name);
857: END IF;
858: --
859: -- Get the download file id
860: --
871: --
872: -- No download took place for this file
873: --
874: IF l_debug_on THEN
875: WSH_DEBUG_SV.logmsg(l_module_name,'Download file id is null, RAISE FTE_DIST_NO_FILE_DOWNLOAD exception');
876: END IF;
877: --
878: RAISE FTE_DIST_NO_FILE_DOWNLOAD;
879: --
877: --
878: RAISE FTE_DIST_NO_FILE_DOWNLOAD;
879: --
880: IF l_debug_on THEN
881: WSH_DEBUG_SV.pop(l_module_name);
882: END IF;
883: --
884: RETURN;
885: END IF;
889: -- There is no template associated with the file
890: --
891: --
892: IF l_debug_on THEN
893: WSH_DEBUG_SV.logmsg(l_module_name,'There is no template associated with the file, RAISE FTE_DIST_NO_FILE_TEMPLATE exception');
894: END IF;
895: --
896: RAISE FTE_DIST_NO_FILE_TEMPLATE;
897: --
895: --
896: RAISE FTE_DIST_NO_FILE_TEMPLATE;
897: --
898: IF l_debug_on THEN
899: WSH_DEBUG_SV.pop(l_module_name);
900: END IF;
901: --
902: RETURN;
903: END IF;
908: -- The download must not have completed correctly
909: -- for this file
910: --
911: IF l_debug_on THEN
912: WSH_DEBUG_SV.logmsg(l_module_name,'l_download_date is null, The download must not have completed correctly for this file, RAISE FTE_DIST_NO_FILE_DOWNLOAD_DATE exception');
913: END IF;
914: --
915: RAISE FTE_DIST_NO_FILE_DOWNLOAD_DATE;
916: --
914: --
915: RAISE FTE_DIST_NO_FILE_DOWNLOAD_DATE;
916: --
917: IF l_debug_on THEN
918: WSH_DEBUG_SV.pop(l_module_name);
919: END IF;
920: --
921: RETURN;
922: END IF;
926: -- This file has already been uploaded successfull
927: -- doing it again makes no sense raise an error
928: --
929: IF l_debug_on THEN
930: WSH_DEBUG_SV.logmsg(l_module_name,'l_upload_date is not null, This file has already been uploaded successfully doing it again makes no sense raise an error RAISE FTE_DIST_FILE_UPLOAD_DONE_PREV exception');
931: END IF;
932: --
933: RAISE FTE_DIST_FILE_UPLOAD_DONE_PREV;
934: --
932: --
933: RAISE FTE_DIST_FILE_UPLOAD_DONE_PREV;
934: --
935: IF l_debug_on THEN
936: WSH_DEBUG_SV.pop(l_module_name);
937: END IF;
938: --
939: RETURN;
940: END IF;
940: END IF;
941:
942:
943: IF l_debug_on THEN
944: WSH_DEBUG_SV.log(l_module_name,'For return distance column OPEN c_get_ret_col_info,g_ret_dist_col_name = ',g_ret_dist_col_name);
945: WSH_DEBUG_SV.log(l_module_name,'l_template_id = ',l_template_id);
946: END IF;
947: --
948: -- For return distance column
941:
942:
943: IF l_debug_on THEN
944: WSH_DEBUG_SV.log(l_module_name,'For return distance column OPEN c_get_ret_col_info,g_ret_dist_col_name = ',g_ret_dist_col_name);
945: WSH_DEBUG_SV.log(l_module_name,'l_template_id = ',l_template_id);
946: END IF;
947: --
948: -- For return distance column
949: --
955: CLOSE c_get_ret_col_info;
956:
957:
958: IF l_debug_on THEN
959: WSH_DEBUG_SV.log(l_module_name,'See if it is enabled OPEN c_get_ret_enabled, l_ret_dist_col_id = ',l_ret_dist_col_id);
960: END IF;
961: --
962: -- See if it is enabled
963: --
979: -- we will not be able to parse the file
980: --
981: --
982: IF l_debug_on THEN
983: WSH_DEBUG_SV.logmsg(l_module_name,'l_ret_dist_enabled_flag = Y, The column is enabled but there is no start or length or both we will not be able to parse the file RAISE FTE_DIST_RET_DIST_COL_NO_DATA exception');
984: END IF;
985: --
986: RAISE FTE_DIST_RET_DIST_COL_NO_DATA;
987: --
985: --
986: RAISE FTE_DIST_RET_DIST_COL_NO_DATA;
987: --
988: IF l_debug_on THEN
989: WSH_DEBUG_SV.pop(l_module_name);
990: END IF;
991: --
992: RETURN;
993: END IF;
1008: --
1009: RAISE FTE_DIST_INV_TEMP_DIST_UOM;
1010: --
1011: IF l_debug_on THEN
1012: WSH_DEBUG_SV.pop(l_module_name);
1013: END IF;
1014: --
1015: RETURN;
1016: END IF;
1029: --
1030: RAISE FTE_DIST_NO_DISTANCE_UOM;
1031: --
1032: IF l_debug_on THEN
1033: WSH_DEBUG_SV.pop(l_module_name);
1034: END IF;
1035: --
1036: RETURN;
1037: END IF;
1041: END IF;
1042:
1043:
1044: IF l_debug_on THEN
1045: WSH_DEBUG_SV.log(l_module_name,'Get the return time column g_ret_time_col_name = ',g_ret_time_col_name);
1046: WSH_DEBUG_SV.log(l_module_name,'l_template_id = ',l_template_id);
1047: END IF;
1048: --
1049: -- Get the return time column
1042:
1043:
1044: IF l_debug_on THEN
1045: WSH_DEBUG_SV.log(l_module_name,'Get the return time column g_ret_time_col_name = ',g_ret_time_col_name);
1046: WSH_DEBUG_SV.log(l_module_name,'l_template_id = ',l_template_id);
1047: END IF;
1048: --
1049: -- Get the return time column
1050: --
1056: CLOSE c_get_ret_col_info;
1057:
1058:
1059: IF l_debug_on THEN
1060: WSH_DEBUG_SV.log(l_module_name,' See if it is enabled OPEN c_get_ret_enabled, l_ret_time_col_id = ',l_ret_time_col_id);
1061: END IF;
1062: --
1063: -- See if it is enabled
1064: --
1077: -- we will not be able to parse the file
1078: --
1079: --
1080: IF l_debug_on THEN
1081: WSH_DEBUG_SV.logmsg(l_module_name,'l_ret_time_enabled_flag = Y, The column is enabled but there is no start or length or both we will not be able to parse the file RAISE FTE_DIST_RET_TIME_COL_NO_DATA exception');
1082: END IF;
1083: --
1084: RAISE FTE_DIST_RET_TIME_COL_NO_DATA;
1085: --
1083: --
1084: RAISE FTE_DIST_RET_TIME_COL_NO_DATA;
1085: --
1086: IF l_debug_on THEN
1087: WSH_DEBUG_SV.pop(l_module_name);
1088: END IF;
1089: --
1090: RETURN;
1091: END IF;
1136: -- parse the data
1137: --
1138: --
1139: IF l_debug_on THEN
1140: WSH_DEBUG_SV.logmsg(l_module_name,'l_ret_time_enabled_flag and l_ret_dist_enabled_flag <> Y, Neither column is enabled Therefore we cannot parse the data, RAISE FTE_DIST_NO_RET_COL_ENABLED exception');
1141: END IF;
1142: --
1143: RAISE FTE_DIST_NO_RET_COL_ENABLED;
1144: --
1142: --
1143: RAISE FTE_DIST_NO_RET_COL_ENABLED;
1144: --
1145: IF l_debug_on THEN
1146: WSH_DEBUG_SV.pop(l_module_name);
1147: END IF;
1148: --
1149: RETURN;
1150: END IF;
1188:
1189:
1190:
1191: IF l_debug_on THEN
1192: WSH_DEBUG_SV.log(l_module_name,'l_conv_flag',l_conv_flag);
1193: END IF;
1194:
1195:
1196: IF l_debug_on THEN
1193: END IF;
1194:
1195:
1196: IF l_debug_on THEN
1197: WSH_DEBUG_SV.log(l_module_name,'Get the download lines OPEN c_get_download_lines, l_download_file_id = ',l_download_file_id);
1198: END IF;
1199:
1200: FND_FILE.PUT_LINE(FND_FILE.log, 'Retrieving the download lines '||l_download_file_id);
1201: --
1213: --
1214: -- There are no download lines in the table?
1215: --
1216: IF l_debug_on THEN
1217: WSH_DEBUG_SV.logmsg(l_module_name,'There are no download lines in the table RAISE FTE_DIST_NO_DOWNLOAD_LINES exception');
1218: END IF;
1219: --
1220: RAISE FTE_DIST_NO_DOWNLOAD_LINES;
1221: --
1219: --
1220: RAISE FTE_DIST_NO_DOWNLOAD_LINES;
1221: --
1222: IF l_debug_on THEN
1223: WSH_DEBUG_SV.pop(l_module_name);
1224: END IF;
1225: --
1226: RETURN;
1227: END IF;
1236:
1237: l_number_of_table_lines := l_origin_id_tab.COUNT;
1238:
1239: IF l_debug_on THEN
1240: WSH_DEBUG_SV.log(l_module_name,'l_number_of_table_lines = ',l_number_of_table_lines);
1241: END IF;
1242:
1243: FND_FILE.PUT_LINE(FND_FILE.log, 'Number of lines in table = '||to_char(l_number_of_table_lines));
1244:
1256: --
1257: BEGIN
1258:
1259: IF l_debug_on THEN
1260: WSH_DEBUG_SV.logmsg(l_module_name,'Open the file Set the full file name for utl_file');
1261: END IF;
1262: --
1263: -- Set the full file name for utl_file
1264: --
1272: l_load_ctr := 0;
1273:
1274:
1275: IF l_debug_on THEN
1276: WSH_DEBUG_SV.logmsg(l_module_name,'Start a loop to read the file contents one line at a time');
1277: END IF;
1278: --
1279: -- Start a loop to read the file
1280: --
1281: LOOP
1282:
1283:
1284: IF l_debug_on THEN
1285: WSH_DEBUG_SV.logmsg(l_module_name,'Get the next line out of the file');
1286: END IF;
1287: --
1288: -- Get the next line out of the file
1289: --
1295: -- Check that the line contains something
1296: --
1297: --
1298: IF l_debug_on THEN
1299: WSH_DEBUG_SV.logmsg(l_module_name,'check the file length');
1300: END IF;
1301: l_line_length := 0;
1302: l_line_length := NVL(LENGTH(l_line),0);
1303:
1304: IF (l_line_length <= 0) THEN
1305: RAISE FTE_DIST_INV_LINE_LENGTH;
1306: --
1307: IF l_debug_on THEN
1308: WSH_DEBUG_SV.pop(l_module_name);
1309: END IF;
1310: --
1311: RETURN;
1312: ELSE
1318: IF (l_line_length <= 0) THEN
1319: RAISE FTE_DIST_INV_LINE_LENGTH;
1320: --
1321: IF l_debug_on THEN
1322: WSH_DEBUG_SV.pop(l_module_name);
1323: END IF;
1324: --
1325: RETURN;
1326: END IF;
1335:
1336: FND_FILE.PUT_LINE(FND_FILE.log, 'File Line #'||to_char(l_line_ctr)||': '||l_line);
1337:
1338: IF l_debug_on THEN
1339: WSH_DEBUG_SV.logmsg(l_module_name,'File Line #'||to_char(l_line_ctr)||': '||l_line);
1340: END IF;
1341:
1342: --
1343: -- Get the distance if applicable
1353: --
1354: l_ret_dist_val := RTRIM(LTRIM(REPLACE(substr(l_line,l_ret_dist_start_pos, l_ret_dist_length),FND_GLOBAL.local_chr(9),' '),' '),' ');
1355:
1356: IF l_debug_on THEN
1357: WSH_DEBUG_SV.logmsg(l_module_name,'Converting distance value to a number');
1358: END IF;
1359:
1360: FND_FILE.PUT_LINE(FND_FILE.log, 'Converting distance value to a number');
1361: --
1405: --
1406: l_colon_check := instr(l_ret_time_val,':');
1407:
1408: IF l_debug_on THEN
1409: WSH_DEBUG_SV.log(l_module_name,'check HMMIN return time for a colon, l_colon_check = ',l_colon_check);
1410: END IF;
1411:
1412: IF (l_colon_check <= 0) THEN
1413: --
1415: --
1416: RAISE FTE_DIST_INV_TIME_FORMAT;
1417: --
1418: IF l_debug_on THEN
1419: WSH_DEBUG_SV.pop(l_module_name);
1420: END IF;
1421: --
1422: RETURN;
1423: END IF;
1424:
1425:
1426: --
1427: IF l_debug_on THEN
1428: WSH_DEBUG_SV.log(l_module_name,'converting HR:MIN to Minutes');
1429: END IF;
1430:
1431: l_ret_mins := to_number(substr(l_ret_time_val, (instr(l_ret_time_val,':')+1),LENGTH(l_ret_time_val)));
1432: l_ret_hrs := to_number(substr(l_ret_time_val,1,(instr(l_ret_time_val,':')-1)));
1432: l_ret_hrs := to_number(substr(l_ret_time_val,1,(instr(l_ret_time_val,':')-1)));
1433: l_ret_time := ((l_ret_hrs * 60) + l_ret_mins);
1434:
1435: IF l_debug_on THEN
1436: WSH_DEBUG_SV.log(l_module_name,'END OF converting HR:MIN to Hours');
1437: END IF;
1438: ELSIF (l_conv_flag = 'HMHR') THEN
1439:
1440: -- AXE
1444: --
1445: l_colon_check := instr(l_ret_time_val,':');
1446:
1447: IF l_debug_on THEN
1448: WSH_DEBUG_SV.log(l_module_name,'check HMHR return time for a colon, l_colon_check = ',l_colon_check);
1449: END IF;
1450:
1451: IF (l_colon_check <= 0) THEN
1452: --
1454: --
1455: RAISE FTE_DIST_INV_TIME_FORMAT;
1456: --
1457: IF l_debug_on THEN
1458: WSH_DEBUG_SV.pop(l_module_name);
1459: END IF;
1460: --
1461: RETURN;
1462: END IF;
1461: RETURN;
1462: END IF;
1463:
1464: IF l_debug_on THEN
1465: WSH_DEBUG_SV.log(l_module_name,'converting HR:MIN to Hours');
1466: END IF;
1467:
1468: l_ret_mins := to_number(substr(l_ret_time_val, (instr(l_ret_time_val,':')+1),LENGTH(l_ret_time_val)));
1469: l_ret_hrs := to_number(substr(l_ret_time_val,1,(instr(l_ret_time_val,':')-1)));
1469: l_ret_hrs := to_number(substr(l_ret_time_val,1,(instr(l_ret_time_val,':')-1)));
1470: l_ret_time := ((l_ret_mins /60) + l_ret_hrs);
1471:
1472: IF l_debug_on THEN
1473: WSH_DEBUG_SV.log(l_module_name,'END OF converting HR:MIN to Hours');
1474: END IF;
1475: END IF;
1476: ELSE
1477: l_ret_time := to_number(l_ret_time_val);
1492: --
1493: l_load_ctr := l_load_ctr + 1;
1494:
1495: IF l_debug_on THEN
1496: WSH_DEBUG_SV.log(l_module_name,'l_load_ctr',l_load_ctr);
1497: END IF;
1498:
1499: l_location_origin_id(l_load_ctr) := l_origin_id_tab(l_line_ctr);
1500: l_location_destination_id(l_load_ctr) := l_destination_id_tab(l_line_ctr);
1514: l_location_request_id(l_load_ctr) := p_load_id;
1515:
1516: IF (l_load_ctr = 250) THEN
1517: IF l_debug_on THEN
1518: WSH_DEBUG_SV.logmsg(l_module_name,'we are going to insert 250 at a time into the global temp table insert into fte_distance_loader_tmp');
1519: END IF;
1520: --
1521: FND_FILE.PUT_LINE(FND_FILE.log, 'Inserting 250 records into temp table');
1522: --
1561: --
1562: -- Clear the tables for the next pass
1563: --
1564: IF l_debug_on THEN
1565: WSH_DEBUG_SV.logmsg(l_module_name,'Clearing the temp tables for the next pass');
1566: END IF;
1567:
1568: l_location_origin_id.DELETE;
1569: l_location_destination_id.DELETE;
1591: END LOOP;
1592:
1593:
1594: IF l_debug_on THEN
1595: WSH_DEBUG_SV.logmsg(l_module_name,'Close the file');
1596: END IF;
1597: --
1598: -- End of reading the file - close it out
1599: --
1652: -- Debug Statements
1653: --
1654: --
1655: IF l_debug_on THEN
1656: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TIME_FORMAT FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
1657: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TIME_FORMAT');
1658: WSH_DEBUG_SV.pop(l_module_name);
1659: END IF;
1660: --
1653: --
1654: --
1655: IF l_debug_on THEN
1656: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TIME_FORMAT FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
1657: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TIME_FORMAT');
1658: WSH_DEBUG_SV.pop(l_module_name);
1659: END IF;
1660: --
1661: RETURN;
1654: --
1655: IF l_debug_on THEN
1656: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TIME_FORMAT FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
1657: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TIME_FORMAT');
1658: WSH_DEBUG_SV.pop(l_module_name);
1659: END IF;
1660: --
1661: RETURN;
1662:
1712: -- Debug Statements
1713: --
1714: --
1715: IF l_debug_on THEN
1716: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_LINE_LENGTH FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
1717: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_LINE_LENGTH');
1718: WSH_DEBUG_SV.pop(l_module_name);
1719: END IF;
1720: --
1713: --
1714: --
1715: IF l_debug_on THEN
1716: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_LINE_LENGTH FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
1717: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_LINE_LENGTH');
1718: WSH_DEBUG_SV.pop(l_module_name);
1719: END IF;
1720: --
1721: RETURN;
1714: --
1715: IF l_debug_on THEN
1716: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_LINE_LENGTH FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
1717: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_LINE_LENGTH');
1718: WSH_DEBUG_SV.pop(l_module_name);
1719: END IF;
1720: --
1721: RETURN;
1722:
1729: IF (utl_file.is_open(l_dtt_file)) THEN
1730: utl_file.fclose(l_dtt_file);
1731: END IF;
1732: IF l_debug_on THEN
1733: WSH_DEBUG_SV.logmsg(l_module_name,'Finished reading file');
1734: END IF;
1735:
1736: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Finished Reading File.');
1737: x_return_status := -1;
1784: CLOSE c_get_distance_uom;
1785: END IF;
1786:
1787: IF l_debug_on THEN
1788: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR UTL_FILE.INVALID_PATH FTE_BULK_DTT_PKG.READ_DTT_FILE ' ||x_return_message);
1789: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UTL_FILE.INVALID_PATH');
1790: WSH_DEBUG_SV.pop(l_module_name);
1791: END IF;
1792: --
1785: END IF;
1786:
1787: IF l_debug_on THEN
1788: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR UTL_FILE.INVALID_PATH FTE_BULK_DTT_PKG.READ_DTT_FILE ' ||x_return_message);
1789: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UTL_FILE.INVALID_PATH');
1790: WSH_DEBUG_SV.pop(l_module_name);
1791: END IF;
1792: --
1793: RETURN;
1786:
1787: IF l_debug_on THEN
1788: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR UTL_FILE.INVALID_PATH FTE_BULK_DTT_PKG.READ_DTT_FILE ' ||x_return_message);
1789: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UTL_FILE.INVALID_PATH');
1790: WSH_DEBUG_SV.pop(l_module_name);
1791: END IF;
1792: --
1793: RETURN;
1794:
1845: --
1846: -- Debug Statements
1847: --
1848: IF l_debug_on THEN
1849: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.READ_DTT_FILE IS ' ||sqlerrm);
1850: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1851: WSH_DEBUG_SV.logmsg(l_module_name,'OFFENDING_LINE: ' ||l_line);
1852: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1853: END IF;
1846: -- Debug Statements
1847: --
1848: IF l_debug_on THEN
1849: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.READ_DTT_FILE IS ' ||sqlerrm);
1850: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1851: WSH_DEBUG_SV.logmsg(l_module_name,'OFFENDING_LINE: ' ||l_line);
1852: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1853: END IF;
1854:
1847: --
1848: IF l_debug_on THEN
1849: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.READ_DTT_FILE IS ' ||sqlerrm);
1850: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1851: WSH_DEBUG_SV.logmsg(l_module_name,'OFFENDING_LINE: ' ||l_line);
1852: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1853: END IF;
1854:
1855: RAISE;
1848: IF l_debug_on THEN
1849: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.READ_DTT_FILE IS ' ||sqlerrm);
1850: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1851: WSH_DEBUG_SV.logmsg(l_module_name,'OFFENDING_LINE: ' ||l_line);
1852: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1853: END IF;
1854:
1855: RAISE;
1856:
1865: --
1866: IF (l_location_origin_id.COUNT > 0) THEN
1867:
1868: IF l_debug_on THEN
1869: WSH_DEBUG_SV.logmsg(l_module_name,'insert the last few lines that did not sum upto 250 into the Global temp table');
1870: END IF;
1871:
1872: FND_FILE.PUT_LINE(FND_FILE.log, 'Inserting remainder of records into temp table');
1873:
1908: --
1909: -- Clean the local tables
1910: --
1911: IF l_debug_on THEN
1912: WSH_DEBUG_SV.logmsg(l_module_name,'Clean the temp tables');
1913: END IF;
1914: --
1915: l_location_origin_id.DELETE;
1916: l_location_destination_id.DELETE;
1939:
1940: FND_FILE.PUT_LINE(FND_FILE.log, 'Number of lines in the table matched the number of lines in the file, inserting and/or updating into the location mileages table');
1941:
1942: IF l_debug_on THEN
1943: WSH_DEBUG_SV.logmsg(l_module_name,'Number of lines in the table matched the number of lines in the file, inserting and/or updating into the location mileages table');
1944: END IF;
1945: --
1946:
1947:
2033: --
2034: FND_FILE.PUT_LINE(FND_FILE.log,'Clean up the query collections');
2035: --
2036: IF l_debug_on THEN
2037: WSH_DEBUG_SV.logmsg(l_module_name,'Clean up the query collections');
2038: END IF;
2039:
2040: --
2041: -- Clean up the query collections
2066:
2067: FND_FILE.PUT_LINE(FND_FILE.log,'Open the cursor, c_get_merge_data, to get the records to update and/or insert');
2068: --
2069: IF l_debug_on THEN
2070: WSH_DEBUG_SV.logmsg(l_module_name,'Open the cursor, c_get_merge_data, to get the records to update and/or insert');
2071: END IF;
2072:
2073: --
2074: -- Open the cursor to get the records to update and/or insert
2110:
2111: FND_FILE.PUT_LINE(FND_FILE.log,'There are no rows left from the cursor - exit the loop');
2112: --
2113: IF l_debug_on THEN
2114: WSH_DEBUG_SV.logmsg(l_module_name,'There are no rows left from the cursor - exit the loop');
2115: END IF;
2116: --
2117: -- There are no rows left from the cursor - exit the loop
2118: --
2134:
2135: FND_FILE.PUT_LINE(FND_FILE.log,'cleaning the temp update tables');
2136: --
2137: IF l_debug_on THEN
2138: WSH_DEBUG_SV.logmsg(l_module_name,'cleaning the temp update tables');
2139: END IF;
2140:
2141: l_u_origin_id_tab.DELETE;
2142: l_u_destination_id_tab.DELETE;
2156:
2157: FND_FILE.PUT_LINE(FND_FILE.log,'cleaning the temp insert tables');
2158: --
2159: IF l_debug_on THEN
2160: WSH_DEBUG_SV.logmsg(l_module_name,'cleaning the temp insert tables');
2161: END IF;
2162:
2163: l_i_origin_id_tab.DELETE;
2164: l_i_destination_id_tab.DELETE;
2193: --
2194: FND_FILE.PUT_LINE(FND_FILE.log, 'checking for insert and update records');
2195: --
2196: IF l_debug_on THEN
2197: WSH_DEBUG_SV.logmsg(l_module_name,'checking for insert and update records');
2198: END IF;
2199:
2200: IF ((l_old_origin_id_tab(kk) is null) AND
2201: (l_old_destination_id_tab(kk) is null)) THEN
2202:
2203: FND_FILE.PUT_LINE(FND_FILE.log, 'getting insert records');
2204: --
2205: IF l_debug_on THEN
2206: WSH_DEBUG_SV.logmsg(l_module_name,'getting insert records');
2207: END IF;
2208: --
2209: -- Must be for an insert
2210: --
2237:
2238: FND_FILE.PUT_LINE(FND_FILE.log, 'getting update records');
2239: --
2240: IF l_debug_on THEN
2241: WSH_DEBUG_SV.logmsg(l_module_name,'getting update records');
2242: END IF;
2243:
2244: --
2245: -- must be an update
2271: --
2272: RAISE FTE_DIST_ORIG_DEST_LOAD_ERR;
2273: --
2274: IF l_debug_on THEN
2275: WSH_DEBUG_SV.pop(l_module_name);
2276: END IF;
2277: --
2278: RETURN;
2279: END IF;
2289:
2290: FND_FILE.PUT_LINE(FND_FILE.log, 'Bulk update of mileage records');
2291: --
2292: IF l_debug_on THEN
2293: WSH_DEBUG_SV.logmsg(l_module_name,'Bulk update of mileage records');
2294: END IF;
2295:
2296: --
2297: -- Update records exist, doing a bulk update
2322:
2323: FND_FILE.PUT_LINE(FND_FILE.log, 'Bulk insert of mileage records');
2324: --
2325: IF l_debug_on THEN
2326: WSH_DEBUG_SV.logmsg(l_module_name,'Bulk insert of mileage records');
2327: END IF;
2328:
2329: --
2330: -- Insert records exist, doing a bulk insert
2383:
2384: FND_FILE.PUT_LINE(FND_FILE.log, 'Updating the Download files table with upload information');
2385: --
2386: IF l_debug_on THEN
2387: WSH_DEBUG_SV.logmsg(l_module_name,'Updating the Download files table with upload information');
2388: END IF;
2389: --
2390: --
2391: -- Update FTE_MILE_DOWNLOAD_FILES with the upload date
2403:
2404: FND_FILE.PUT_LINE(FND_FILE.log, 'Deleting data from the Download lines table');
2405: --
2406: IF l_debug_on THEN
2407: WSH_DEBUG_SV.logmsg(l_module_name,'Deleting data from the Download lines table');
2408: END IF;
2409: --
2410: --
2411: -- Delete the lines
2420: FND_FILE.PUT_LINE(FND_FILE.log, 'Returning with success');
2421:
2422: --
2423: IF l_debug_on THEN
2424: WSH_DEBUG_SV.logmsg(l_module_name,'Returning with success');
2425: END IF;
2426: --
2427: x_return_status := -1;
2428: x_return_message := null;
2428: x_return_message := null;
2429:
2430: --
2431: IF l_debug_on THEN
2432: WSH_DEBUG_SV.pop(l_module_name);
2433: END IF;
2434: --
2435: RETURN;
2436:
2441: --
2442: -- issue a commit to clear the temp table??
2443: --
2444: IF l_debug_on THEN
2445: WSH_DEBUG_SV.logmsg(l_module_name,'The file has less lines than when it was created, i.e. lines from the file have been deleted, thus we cannot load, RAISE FTE_DIST_LESS_FILE_LINES exception');
2446: END IF;
2447: --
2448: RAISE FTE_DIST_LESS_FILE_LINES;
2449: --
2447: --
2448: RAISE FTE_DIST_LESS_FILE_LINES;
2449: --
2450: IF l_debug_on THEN
2451: WSH_DEBUG_SV.pop(l_module_name);
2452: END IF;
2453: --
2454: RETURN;
2455:
2458: -- There are too many lines in the file i.e. lines have been added
2459: -- we cannot load this file.
2460: --
2461: IF l_debug_on THEN
2462: WSH_DEBUG_SV.logmsg(l_module_name,'There are too many lines in the file i.e. lines have been added we cannot load this file RAISE FTE_DIST_MANY_FILE_LINES exception');
2463: END IF;
2464: --
2465: RAISE FTE_DIST_MANY_FILE_LINES;
2466: --
2464: --
2465: RAISE FTE_DIST_MANY_FILE_LINES;
2466: --
2467: IF l_debug_on THEN
2468: WSH_DEBUG_SV.pop(l_module_name);
2469: END IF;
2470: --
2471: RETURN;
2472:
2525: -- Debug Statements
2526: --
2527: --
2528: IF l_debug_on THEN
2529: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TEMP_TIME_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2530: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TEMP_TIME_UOM');
2531: WSH_DEBUG_SV.pop(l_module_name);
2532: END IF;
2533: --
2526: --
2527: --
2528: IF l_debug_on THEN
2529: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TEMP_TIME_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2530: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TEMP_TIME_UOM');
2531: WSH_DEBUG_SV.pop(l_module_name);
2532: END IF;
2533: --
2534: RETURN;
2527: --
2528: IF l_debug_on THEN
2529: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TEMP_TIME_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2530: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TEMP_TIME_UOM');
2531: WSH_DEBUG_SV.pop(l_module_name);
2532: END IF;
2533: --
2534: RETURN;
2535:
2585: -- Debug Statements
2586: --
2587: --
2588: IF l_debug_on THEN
2589: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_TIME_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2590: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_TIME_UOM');
2591: WSH_DEBUG_SV.pop(l_module_name);
2592: END IF;
2593: --
2586: --
2587: --
2588: IF l_debug_on THEN
2589: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_TIME_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2590: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_TIME_UOM');
2591: WSH_DEBUG_SV.pop(l_module_name);
2592: END IF;
2593: --
2594: RETURN;
2587: --
2588: IF l_debug_on THEN
2589: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_TIME_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2590: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_TIME_UOM');
2591: WSH_DEBUG_SV.pop(l_module_name);
2592: END IF;
2593: --
2594: RETURN;
2595:
2645: -- Debug Statements
2646: --
2647: --
2648: IF l_debug_on THEN
2649: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TEMP_DIST_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2650: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TEMP_DIST_UOM');
2651: WSH_DEBUG_SV.pop(l_module_name);
2652: END IF;
2653: --
2646: --
2647: --
2648: IF l_debug_on THEN
2649: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TEMP_DIST_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2650: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TEMP_DIST_UOM');
2651: WSH_DEBUG_SV.pop(l_module_name);
2652: END IF;
2653: --
2654: RETURN;
2647: --
2648: IF l_debug_on THEN
2649: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TEMP_DIST_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2650: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TEMP_DIST_UOM');
2651: WSH_DEBUG_SV.pop(l_module_name);
2652: END IF;
2653: --
2654: RETURN;
2655:
2706: -- Debug Statements
2707: --
2708: --
2709: IF l_debug_on THEN
2710: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_DISTANCE_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2711: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_DISTANCE_UOM');
2712: WSH_DEBUG_SV.pop(l_module_name);
2713: END IF;
2714: --
2707: --
2708: --
2709: IF l_debug_on THEN
2710: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_DISTANCE_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2711: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_DISTANCE_UOM');
2712: WSH_DEBUG_SV.pop(l_module_name);
2713: END IF;
2714: --
2715: RETURN;
2708: --
2709: IF l_debug_on THEN
2710: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_DISTANCE_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2711: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_DISTANCE_UOM');
2712: WSH_DEBUG_SV.pop(l_module_name);
2713: END IF;
2714: --
2715: RETURN;
2716:
2767: -- Debug Statements
2768: --
2769: --
2770: IF l_debug_on THEN
2771: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_ORIG_DEST_LOAD_ERR FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2772: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_ORIG_DEST_LOAD_ERR');
2773: WSH_DEBUG_SV.pop(l_module_name);
2774: END IF;
2775: --
2768: --
2769: --
2770: IF l_debug_on THEN
2771: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_ORIG_DEST_LOAD_ERR FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2772: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_ORIG_DEST_LOAD_ERR');
2773: WSH_DEBUG_SV.pop(l_module_name);
2774: END IF;
2775: --
2776: RETURN;
2769: --
2770: IF l_debug_on THEN
2771: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_ORIG_DEST_LOAD_ERR FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2772: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_ORIG_DEST_LOAD_ERR');
2773: WSH_DEBUG_SV.pop(l_module_name);
2774: END IF;
2775: --
2776: RETURN;
2777:
2828: -- Debug Statements
2829: --
2830: --
2831: IF l_debug_on THEN
2832: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_LESS_FILE_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2833: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_LESS_FILE_LINES');
2834: WSH_DEBUG_SV.pop(l_module_name);
2835: END IF;
2836: --
2829: --
2830: --
2831: IF l_debug_on THEN
2832: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_LESS_FILE_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2833: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_LESS_FILE_LINES');
2834: WSH_DEBUG_SV.pop(l_module_name);
2835: END IF;
2836: --
2837: RETURN;
2830: --
2831: IF l_debug_on THEN
2832: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_LESS_FILE_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2833: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_LESS_FILE_LINES');
2834: WSH_DEBUG_SV.pop(l_module_name);
2835: END IF;
2836: --
2837: RETURN;
2838:
2888: -- Debug Statements
2889: --
2890: --
2891: IF l_debug_on THEN
2892: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_MANY_FILE_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2893: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_MANY_FILE_LINES');
2894: WSH_DEBUG_SV.pop(l_module_name);
2895: END IF;
2896: --
2889: --
2890: --
2891: IF l_debug_on THEN
2892: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_MANY_FILE_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2893: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_MANY_FILE_LINES');
2894: WSH_DEBUG_SV.pop(l_module_name);
2895: END IF;
2896: --
2897: RETURN;
2890: --
2891: IF l_debug_on THEN
2892: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_MANY_FILE_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2893: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_MANY_FILE_LINES');
2894: WSH_DEBUG_SV.pop(l_module_name);
2895: END IF;
2896: --
2897: RETURN;
2898:
2949: -- Debug Statements
2950: --
2951: --
2952: IF l_debug_on THEN
2953: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NULL_FILE_NAME FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2954: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NULL_FILE_NAME');
2955: WSH_DEBUG_SV.pop(l_module_name);
2956: END IF;
2957: --
2950: --
2951: --
2952: IF l_debug_on THEN
2953: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NULL_FILE_NAME FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2954: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NULL_FILE_NAME');
2955: WSH_DEBUG_SV.pop(l_module_name);
2956: END IF;
2957: --
2958: RETURN;
2951: --
2952: IF l_debug_on THEN
2953: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NULL_FILE_NAME FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2954: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NULL_FILE_NAME');
2955: WSH_DEBUG_SV.pop(l_module_name);
2956: END IF;
2957: --
2958: RETURN;
2959:
3010: -- Debug Statements
3011: --
3012: --
3013: IF l_debug_on THEN
3014: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_FILENAME_LGTH FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3015: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_FILENAME_LGTH');
3016: WSH_DEBUG_SV.pop(l_module_name);
3017: END IF;
3018: --
3011: --
3012: --
3013: IF l_debug_on THEN
3014: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_FILENAME_LGTH FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3015: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_FILENAME_LGTH');
3016: WSH_DEBUG_SV.pop(l_module_name);
3017: END IF;
3018: --
3019: RETURN;
3012: --
3013: IF l_debug_on THEN
3014: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_FILENAME_LGTH FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3015: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_FILENAME_LGTH');
3016: WSH_DEBUG_SV.pop(l_module_name);
3017: END IF;
3018: --
3019: RETURN;
3020:
3069: -- Debug Statements
3070: --
3071: --
3072: IF l_debug_on THEN
3073: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_DOWNLOAD FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3074: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_DOWNLOAD');
3075: WSH_DEBUG_SV.pop(l_module_name);
3076: END IF;
3077: --
3070: --
3071: --
3072: IF l_debug_on THEN
3073: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_DOWNLOAD FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3074: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_DOWNLOAD');
3075: WSH_DEBUG_SV.pop(l_module_name);
3076: END IF;
3077: --
3078: RETURN;
3071: --
3072: IF l_debug_on THEN
3073: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_DOWNLOAD FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3074: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_DOWNLOAD');
3075: WSH_DEBUG_SV.pop(l_module_name);
3076: END IF;
3077: --
3078: RETURN;
3079:
3126: --
3127: -- Debug Statements
3128: --
3129: IF l_debug_on THEN
3130: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_TEMPLATE FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3131: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_TEMPLATE');
3132: WSH_DEBUG_SV.pop(l_module_name);
3133: END IF;
3134: --
3127: -- Debug Statements
3128: --
3129: IF l_debug_on THEN
3130: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_TEMPLATE FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3131: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_TEMPLATE');
3132: WSH_DEBUG_SV.pop(l_module_name);
3133: END IF;
3134: --
3135: RETURN;
3128: --
3129: IF l_debug_on THEN
3130: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_TEMPLATE FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3131: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_TEMPLATE');
3132: WSH_DEBUG_SV.pop(l_module_name);
3133: END IF;
3134: --
3135: RETURN;
3136:
3185: --
3186: -- Debug Statements
3187: --
3188: IF l_debug_on THEN
3189: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_DOWNLOAD_DATE FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3190: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_DOWNLOAD_DATE');
3191: WSH_DEBUG_SV.pop(l_module_name);
3192: END IF;
3193: --
3186: -- Debug Statements
3187: --
3188: IF l_debug_on THEN
3189: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_DOWNLOAD_DATE FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3190: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_DOWNLOAD_DATE');
3191: WSH_DEBUG_SV.pop(l_module_name);
3192: END IF;
3193: --
3194: RETURN;
3187: --
3188: IF l_debug_on THEN
3189: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_DOWNLOAD_DATE FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3190: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_DOWNLOAD_DATE');
3191: WSH_DEBUG_SV.pop(l_module_name);
3192: END IF;
3193: --
3194: RETURN;
3195:
3243: --
3244: -- Debug Statements
3245: --
3246: IF l_debug_on THEN
3247: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_FILE_UPLOAD_DONE_PREV FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3248: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_FILE_UPLOAD_DONE_PREV');
3249: WSH_DEBUG_SV.pop(l_module_name);
3250: END IF;
3251: --
3244: -- Debug Statements
3245: --
3246: IF l_debug_on THEN
3247: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_FILE_UPLOAD_DONE_PREV FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3248: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_FILE_UPLOAD_DONE_PREV');
3249: WSH_DEBUG_SV.pop(l_module_name);
3250: END IF;
3251: --
3252: RETURN;
3245: --
3246: IF l_debug_on THEN
3247: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_FILE_UPLOAD_DONE_PREV FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3248: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_FILE_UPLOAD_DONE_PREV');
3249: WSH_DEBUG_SV.pop(l_module_name);
3250: END IF;
3251: --
3252: RETURN;
3253:
3301: --
3302: -- Debug Statements
3303: --
3304: IF l_debug_on THEN
3305: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_DOWNLOAD_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3306: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_DOWNLOAD_LINES');
3307: WSH_DEBUG_SV.pop(l_module_name);
3308: END IF;
3309: --
3302: -- Debug Statements
3303: --
3304: IF l_debug_on THEN
3305: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_DOWNLOAD_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3306: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_DOWNLOAD_LINES');
3307: WSH_DEBUG_SV.pop(l_module_name);
3308: END IF;
3309: --
3310: RETURN;
3303: --
3304: IF l_debug_on THEN
3305: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_DOWNLOAD_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3306: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_DOWNLOAD_LINES');
3307: WSH_DEBUG_SV.pop(l_module_name);
3308: END IF;
3309: --
3310: RETURN;
3311:
3359: --
3360: -- Debug Statements
3361: --
3362: IF l_debug_on THEN
3363: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_RET_DIST_COL_NO_DATA FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3364: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_RET_DIST_COL_NO_DATA');
3365: WSH_DEBUG_SV.pop(l_module_name);
3366: END IF;
3367: --
3360: -- Debug Statements
3361: --
3362: IF l_debug_on THEN
3363: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_RET_DIST_COL_NO_DATA FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3364: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_RET_DIST_COL_NO_DATA');
3365: WSH_DEBUG_SV.pop(l_module_name);
3366: END IF;
3367: --
3368: RETURN;
3361: --
3362: IF l_debug_on THEN
3363: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_RET_DIST_COL_NO_DATA FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3364: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_RET_DIST_COL_NO_DATA');
3365: WSH_DEBUG_SV.pop(l_module_name);
3366: END IF;
3367: --
3368: RETURN;
3369:
3416: --
3417: -- Debug Statements
3418: --
3419: IF l_debug_on THEN
3420: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_RET_TIME_COL_NO_DATA FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3421: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_RET_TIME_COL_NO_DATA');
3422: WSH_DEBUG_SV.pop(l_module_name);
3423: END IF;
3424: --
3417: -- Debug Statements
3418: --
3419: IF l_debug_on THEN
3420: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_RET_TIME_COL_NO_DATA FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3421: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_RET_TIME_COL_NO_DATA');
3422: WSH_DEBUG_SV.pop(l_module_name);
3423: END IF;
3424: --
3425: RETURN;
3418: --
3419: IF l_debug_on THEN
3420: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_RET_TIME_COL_NO_DATA FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3421: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_RET_TIME_COL_NO_DATA');
3422: WSH_DEBUG_SV.pop(l_module_name);
3423: END IF;
3424: --
3425: RETURN;
3426:
3473: --
3474: -- Debug Statements
3475: --
3476: IF l_debug_on THEN
3477: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_RET_COL_ENABLED FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3478: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_RET_COL_ENABLED');
3479: WSH_DEBUG_SV.pop(l_module_name);
3480: END IF;
3481: --
3474: -- Debug Statements
3475: --
3476: IF l_debug_on THEN
3477: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_RET_COL_ENABLED FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3478: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_RET_COL_ENABLED');
3479: WSH_DEBUG_SV.pop(l_module_name);
3480: END IF;
3481: --
3482: RETURN;
3475: --
3476: IF l_debug_on THEN
3477: WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_RET_COL_ENABLED FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3478: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_RET_COL_ENABLED');
3479: WSH_DEBUG_SV.pop(l_module_name);
3480: END IF;
3481: --
3482: RETURN;
3483:
3490: --
3491: -- Debug Statements
3492: --
3493: IF l_debug_on THEN
3494: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.READ_DTT_FILE IS ' ||sqlerrm);
3495: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3496: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3497: END IF;
3498:
3491: -- Debug Statements
3492: --
3493: IF l_debug_on THEN
3494: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.READ_DTT_FILE IS ' ||sqlerrm);
3495: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3496: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3497: END IF;
3498:
3499:
3492: --
3493: IF l_debug_on THEN
3494: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.READ_DTT_FILE IS ' ||sqlerrm);
3495: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3496: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3497: END IF;
3498:
3499:
3500: RETURN;
3547: -- SETUP DEBUGGING
3548: --
3549: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3550: IF l_debug_on IS NULL THEN
3551: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3552: END IF;
3553:
3554: IF (g_user_debug = 1) THEN
3555: l_debug_on := TRUE;
3556: END IF;
3557:
3558:
3559: IF l_debug_on THEN
3560: WSH_DEBUG_SV.push(l_module_name);
3561: WSH_DEBUG_SV.logmsg(l_module_name,'---------------FIRST_TIME------------');
3562: END IF;
3563:
3564:
3557:
3558:
3559: IF l_debug_on THEN
3560: WSH_DEBUG_SV.push(l_module_name);
3561: WSH_DEBUG_SV.logmsg(l_module_name,'---------------FIRST_TIME------------');
3562: END IF;
3563:
3564:
3565: FND_FILE.PUT_LINE(FND_FILE.log, 'FIRST_TIME');
3569: IF (req_data IS NULL) THEN
3570: FND_FILE.PUT_LINE(FND_FILE.log, 'FIRST_TIME returning TRUE');
3571: --
3572: IF l_debug_on THEN
3573: WSH_DEBUG_SV.pop(l_module_name);
3574: END IF;
3575: --
3576: RETURN TRUE;
3577: ELSE
3577: ELSE
3578: FND_FILE.PUT_LINE(FND_FILE.log, 'FIRST_TIME returning FALSE');
3579: --
3580: IF l_debug_on THEN
3581: WSH_DEBUG_SV.pop(l_module_name);
3582: END IF;
3583: --
3584: RETURN FALSE;
3585: END IF;
3592: --
3593: -- Debug Statements
3594: --
3595: IF l_debug_on THEN
3596: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.FIRST_TIME IS ' ||sqlerrm);
3597: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3598: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3599: END IF;
3600:
3593: -- Debug Statements
3594: --
3595: IF l_debug_on THEN
3596: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.FIRST_TIME IS ' ||sqlerrm);
3597: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3598: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3599: END IF;
3600:
3601: RETURN FALSE;
3594: --
3595: IF l_debug_on THEN
3596: WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.FIRST_TIME IS ' ||sqlerrm);
3597: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3598: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3599: END IF;
3600:
3601: RETURN FALSE;
3602: