77:
78: l_error_msg := 'Entering ' || g_object_name || '.APPLY_INSERT'
79: || ' for PK ' || to_char( p_record.FILE_ID);
80:
81: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
82: jtm_message_log_pkg.Log_Msg
83: ( v_object_id => p_record.file_id
84: , v_object_name => g_object_name
85: , v_message => l_error_msg
82: jtm_message_log_pkg.Log_Msg
83: ( v_object_id => p_record.file_id
84: , v_object_name => g_object_name
85: , v_message => l_error_msg
86: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
87: END IF;
88:
89: -- if debrief_header_id is not passed get the debrief header id
90: IF ( (p_record.pk1_value IS NULL) AND
116: x_return_status := FND_API.G_RET_STS_ERROR;
117: p_error_msg := 'Duplicate Record: File id ' || to_char(p_record.file_id)
118: || ' already exists in fnd_lobs table';
119:
120: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
121: jtm_message_log_pkg.Log_Msg
122: ( v_object_id => p_record.file_id
123: , v_object_name => g_object_name
124: , v_message => p_error_msg
121: jtm_message_log_pkg.Log_Msg
122: ( v_object_id => p_record.file_id
123: , v_object_name => g_object_name
124: , v_message => p_error_msg
125: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
126: END IF;
127:
128: CLOSE l_lobs_fileid_csr;
129: RETURN;
179:
180: l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT'
181: || ' for PK ' || to_char (p_record.FILE_ID );
182:
183: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
184: jtm_message_log_pkg.Log_Msg
185: ( v_object_id => p_record.file_id
186: , v_object_name => g_object_name
187: , v_message => l_error_msg
184: jtm_message_log_pkg.Log_Msg
185: ( v_object_id => p_record.file_id
186: , v_object_name => g_object_name
187: , v_message => l_error_msg
188: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
189: END IF;
190:
191:
192: EXCEPTION
195: l_error_msg := 'Exception occurred in ' || g_object_name
196: || '.APPLY_INSERT:' || ' ' || sqlerrm
197: || ' for PK ' || to_char (p_record.FILE_ID );
198:
199: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
200: jtm_message_log_pkg.Log_Msg
201: ( v_object_id => p_record.file_id
202: , v_object_name => g_object_name
203: , v_message => l_error_msg
200: jtm_message_log_pkg.Log_Msg
201: ( v_object_id => p_record.file_id
202: , v_object_name => g_object_name
203: , v_message => l_error_msg
204: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
205: END IF;
206:
207: fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
208:
213:
214: l_error_msg := 'Leaving ' || g_object_name || '.APPLY_INSERT'
215: || ' for PK ' || to_char (p_record.FILE_ID );
216:
217: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
218: jtm_message_log_pkg.Log_Msg
219: ( v_object_id => p_record.file_id
220: , v_object_name => g_object_name
221: , v_message => l_error_msg
218: jtm_message_log_pkg.Log_Msg
219: ( v_object_id => p_record.file_id
220: , v_object_name => g_object_name
221: , v_message => l_error_msg
222: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
223: END IF;
224:
225: x_return_status := FND_API.G_RET_STS_ERROR;
226: END APPLY_INSERT;
243: x_return_status := FND_API.G_RET_STS_SUCCESS;
244: FND_MSG_PUB.INITIALIZE;
245:
246:
247: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
248: jtm_message_log_pkg.Log_Msg
249: ( v_object_id => p_record.file_id
250: , v_object_name => g_object_name
251: , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
248: jtm_message_log_pkg.Log_Msg
249: ( v_object_id => p_record.file_id
250: , v_object_name => g_object_name
251: , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
252: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
253: END IF;
254:
255: l_error_msg := 'Processing ' || g_object_name || ' for PK '
256: || to_char (p_record.FILE_ID) || ' ' || 'DMLTYPE = '
255: l_error_msg := 'Processing ' || g_object_name || ' for PK '
256: || to_char (p_record.FILE_ID) || ' ' || 'DMLTYPE = '
257: || p_record.dmltype$$ ;
258:
259: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
260: jtm_message_log_pkg.Log_Msg
261: ( v_object_id => p_record.file_id
262: , v_object_name => g_object_name
263: , v_message => l_error_msg
260: jtm_message_log_pkg.Log_Msg
261: ( v_object_id => p_record.file_id
262: , v_object_name => g_object_name
263: , v_message => l_error_msg
264: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
265: END IF;
266:
267: IF p_record.dmltype$$='I' THEN
268: -- Process insert
281: jtm_message_log_pkg.Log_Msg
282: ( v_object_id => p_record.file_id
283: , v_object_name => g_object_name
284: , v_message => l_error_msg
285: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
286:
287: p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
288: (
289: p_message => 'CSL_DML_OPERATION'
301: jtm_message_log_pkg.Log_Msg
302: ( v_object_id => p_record.file_id
303: , v_object_name => g_object_name
304: , v_message => l_error_msg
305: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
306:
307: p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
308: (
309: p_message => 'CSL_DML_OPERATION'
314: x_return_status := FND_API.G_RET_STS_ERROR;
315: ELSE
316: -- invalid dml type
317:
318: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
319: jtm_message_log_pkg.Log_Msg
320: ( v_object_id => p_record.task_assignment_id
321: , v_object_name => g_object_name
322: , v_message => 'Invalid DML type: ' || p_record.dmltype$$
319: jtm_message_log_pkg.Log_Msg
320: ( v_object_id => p_record.task_assignment_id
321: , v_object_name => g_object_name
322: , v_message => 'Invalid DML type: ' || p_record.dmltype$$
323: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
324: END IF;
325:
326: p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
327: (
332:
333: x_return_status := FND_API.G_RET_STS_ERROR;
334: END IF;
335:
336: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
337: jtm_message_log_pkg.Log_Msg
338: ( v_object_id => p_record.file_id
339: , v_object_name => g_object_name
340: , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
337: jtm_message_log_pkg.Log_Msg
338: ( v_object_id => p_record.file_id
339: , v_object_name => g_object_name
340: , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
341: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
342: END IF;
343:
344:
345: EXCEPTION WHEN OTHERS THEN
344:
345: EXCEPTION WHEN OTHERS THEN
346: /*** defer record when any process exception occurs ***/
347:
348: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
349: jtm_message_log_pkg.Log_Msg
350: ( v_object_id => p_record.file_id
351: , v_object_name => g_object_name
352: , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
349: jtm_message_log_pkg.Log_Msg
350: ( v_object_id => p_record.file_id
351: , v_object_name => g_object_name
352: , v_message => 'Exception occurred in APPLY_RECORD:' || fnd_global.local_chr(10) || sqlerrm
353: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
354: END IF;
355:
356: fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
357: p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
358: (
359: p_api_error => TRUE
360: );
361:
362: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
363: jtm_message_log_pkg.Log_Msg
364: ( v_object_id => p_record.file_id
365: , v_object_name => g_object_name
366: , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
363: jtm_message_log_pkg.Log_Msg
364: ( v_object_id => p_record.file_id
365: , v_object_name => g_object_name
366: , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
367: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
368: END IF;
369:
370: x_return_status := FND_API.G_RET_STS_ERROR;
371: END APPLY_RECORD;
396: jtm_message_log_pkg.Log_Msg
397: ( v_object_id => null
398: , v_object_name => g_object_name
399: , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
400: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
401: );
402:
403: /*** loop through CSL_LOBS records in inqueue ***/
404: FOR r_FND_LOBS IN C_FND_LOBS( p_user_name, p_tranid) LOOP
415:
416: /*** was record processed successfully? ***/
417: IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
418: /*** Yes -> delete record from inqueue ***/
419: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
420: jtm_message_log_pkg.Log_Msg
421: ( v_object_id => r_fnd_lobs.task_assignment_id
422: , v_object_name => g_object_name
423: , v_message => 'Record successfully processed, deleting from inqueue'
420: jtm_message_log_pkg.Log_Msg
421: ( v_object_id => r_fnd_lobs.task_assignment_id
422: , v_object_name => g_object_name
423: , v_message => 'Record successfully processed, deleting from inqueue'
424: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
425: END IF;
426:
427:
428: CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
439:
440: /*** was delete successful? ***/
441: IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
442: /*** no -> rollback ***/
443: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
444: jtm_message_log_pkg.Log_Msg
445: ( v_object_id => r_FND_LOBS.task_assignment_id
446: , v_object_name => g_object_name
447: , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
444: jtm_message_log_pkg.Log_Msg
445: ( v_object_id => r_FND_LOBS.task_assignment_id
446: , v_object_name => g_object_name
447: , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
448: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
449: END IF;
450:
451: ROLLBACK TO save_rec;
452: END IF;
455: IF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
456: /*** Record was not processed successfully or delete failed
457: -> defer and reject record ***/
458:
459: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
460: jtm_message_log_pkg.Log_Msg
461: ( v_object_id => r_FND_LOBS.task_assignment_id
462: , v_object_name => g_object_name
463: , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
460: jtm_message_log_pkg.Log_Msg
461: ( v_object_id => r_FND_LOBS.task_assignment_id
462: , v_object_name => g_object_name
463: , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
464: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
465: END IF;
466:
467: CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
468: (
479:
480: /*** Was defer successful? ***/
481: IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
482: /*** no -> rollback ***/
483: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
484: jtm_message_log_pkg.Log_Msg
485: ( v_object_id => r_FND_LOBS.task_assignment_id
486: , v_object_name => g_object_name
487: , v_message => 'Defer record failed, rolling back to savepoint'
484: jtm_message_log_pkg.Log_Msg
485: ( v_object_id => r_FND_LOBS.task_assignment_id
486: , v_object_name => g_object_name
487: , v_message => 'Defer record failed, rolling back to savepoint'
488: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
489: END IF;
490:
491: ROLLBACK TO save_rec;
492: END IF;
494:
495: END LOOP;
496:
497:
498: IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
499: jtm_message_log_pkg.Log_Msg
500: ( v_object_id => null
501: , v_object_name => g_object_name
502: , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
499: jtm_message_log_pkg.Log_Msg
500: ( v_object_id => null
501: , v_object_name => g_object_name
502: , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
503: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
504: END IF;
505:
506:
507: EXCEPTION WHEN OTHERS THEN
505:
506:
507: EXCEPTION WHEN OTHERS THEN
508: /*** catch and log exceptions ***/
509: IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
510: jtm_message_log_pkg.Log_Msg
511: ( v_object_id => null
512: , v_object_name => g_object_name
513: , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
510: jtm_message_log_pkg.Log_Msg
511: ( v_object_id => null
512: , v_object_name => g_object_name
513: , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || fnd_global.local_chr(10) || sqlerrm
514: , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
515: END IF;
516:
517: x_return_status := FND_API.G_RET_STS_ERROR;
518: END APPLY_CLIENT_CHANGES;