[Home] [Help]
PACKAGE BODY: APPS.CSL_REQUIREMENTS_PKG
Source
1 PACKAGE BODY CSL_REQUIREMENTS_PKG AS
2 /* $Header: cslvreqb.pls 115.14 2002/11/08 14:00:12 asiegers ship $ */
3
4
5 error EXCEPTION;
6
7 /*** Globals ***/
8 g_object_name CONSTANT VARCHAR2(30) := 'CSL_REQUIREMENTS_PKG'; -- package name
9 g_pub_name CONSTANT VARCHAR2(30) := 'CSP_REQUIREMENT_LINES'; -- publication item name
10 g_pub_name2 CONSTANT VARCHAR2(30) := 'CSP_REQUIREMENT_HEADERS'; -- publication item name
11 g_debug_level NUMBER; -- debug level
12
13 /***
14 Cursor to retrieve all requirement lines from the requirement line inqueue that
15 have a requirement header record in the requirement header inqueue.
16 ***/
17 CURSOR c_requirements ( b_user_name VARCHAR2, b_tranid NUMBER) is
18 SELECT reql.*
19 FROM CSL_CSP_REQUIREMENT_LNS_inq reql, CSL_CSP_REQUIREMENT_HDR_inq reqh
20 WHERE reql.tranid$$ = b_tranid
21 AND reql.clid$$cs = b_user_name
22 AND reql.requirement_header_id = reqh.requirement_header_id;
23
24 /***
25 Cursor to retrieve all requirement lines from the requirement line inqueue that
26 have no requirement header record in the requirement header inqueue but have one in the backend.
27 This one is executed after all requirement lines with headers have been deleted from the inqueue.
28 The requirement lines without header remain then.
29 ***/
30 CURSOR c_requirements_no_headers ( b_user_name VARCHAR2, b_tranid NUMBER) is
31 SELECT *
32 FROM CSL_CSP_REQUIREMENT_LNS_inq
33 WHERE tranid$$ = b_tranid
34 AND clid$$cs = b_user_name;
35
36 /***
37 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
38 If p_get_inqueue_header = TRUE => fetch requirement header from the requirement header inqueue.
39 If p_get_inqueue_header = FALSE => fetch requirement header from Apps.
40 ***/
41 PROCEDURE APPLY_INSERT
42 (
43 p_record IN c_requirements%ROWTYPE,
44 p_user_name IN VARCHAR2,
45 p_tranid IN NUMBER,
46 p_get_inqueue_header IN BOOLEAN,
47 p_error_msg OUT NOCOPY VARCHAR2,
48 x_return_status IN OUT NOCOPY VARCHAR2
49 ) IS
50
51 l_header_rec CSP_PARTS_REQUIREMENT.HEADER_REC_TYPE;
52 l_line_rec CSP_PARTS_REQUIREMENT.LINE_REC_TYPE;
53 l_line_table CSP_PARTS_REQUIREMENT.LINE_TBL_TYPE;
54
55 l_s_org_id NUMBER := 207;
56 l_d_org_id NUMBER := 204;
57 l_item_id NUMBER := 155;
58 l_quantity NUMBER := 7;
59 l_uom VARCHAR2(3) := 'Ea';
60 l_msg_count NUMBER;
61 l_msg_data VARCHAR2(240);
62
63 -- Cursor to retrieve requirement header from the inqueue
64 CURSOR c_get_requirement_from_inq ( b_user_name VARCHAR2, b_tranid NUMBER, b_requirement_header_id NUMBER) is
65 SELECT *
66 FROM CSL_CSP_REQUIREMENT_HDR_inq
67 WHERE tranid$$ = b_tranid
68 AND clid$$cs = b_user_name
69 AND requirement_header_id = b_requirement_header_id;
70
71 r_get_requirement_from_inq c_get_requirement_from_inq%ROWTYPE;
72
73 -- Cursor to retrieve requirement header from Apps
74 CURSOR c_get_requirement_from_apps (b_requirement_header_id NUMBER) is
75 SELECT *
76 FROM CSP_REQUIREMENT_HEADERS
77 WHERE requirement_header_id = b_requirement_header_id;
78
79 r_get_requirement_from_apps c_get_requirement_from_apps%ROWTYPE;
80
81 BEGIN
82
83 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
84 jtm_message_log_pkg.Log_Msg
85 ( v_object_id => p_record.requirement_line_id -- put PK column here
86 , v_object_name => g_object_name
87 , v_message => 'Entering ' || g_object_name || '.APPLY_INSERT'
88 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
89 END IF;
90
91 IF p_get_inqueue_header THEN
92
93 -- Open cursor to retrieve requirement header from requirement header inqueue
94 OPEN c_get_requirement_from_inq (p_user_name, p_tranid, p_record.REQUIREMENT_HEADER_ID);
95 FETCH c_get_requirement_from_inq INTO r_get_requirement_from_inq;
96 CLOSE c_get_requirement_from_inq ;
97
98 -- Initialization of the requirement header with inqueue header record
99 l_header_rec.REQUIREMENT_HEADER_ID := r_get_requirement_from_inq.REQUIREMENT_HEADER_ID;
100 l_header_rec.REQUISITION_NUMBER := r_get_requirement_from_inq.REQUIREMENT_HEADER_ID;
101 l_header_rec.ORDER_TYPE_ID := fnd_profile.value('CSP_ORDER_TYPE');
102 l_header_rec.SHIP_TO_LOCATION_ID := r_get_requirement_from_inq.SHIP_TO_LOCATION_ID;
103 l_header_rec.NEED_BY_DATE := r_get_requirement_from_inq.NEED_BY_DATE;
104 l_header_rec.DEST_ORGANIZATION_ID := r_get_requirement_from_inq.DESTINATION_ORGANIZATION_ID;
105 l_header_rec.DEST_SUBINVENTORY := r_get_requirement_from_inq.DESTINATION_SUBINVENTORY;
106 l_header_rec.OPERATION := CSP_PARTS_REQUIREMENT.G_OPR_CREATE;
107 l_header_rec.RESOURCE_TYPE := r_get_requirement_from_inq.RESOURCE_TYPE;
108 l_header_rec.RESOURCE_ID := r_get_requirement_from_inq.RESOURCE_ID;
109
110 ELSE
111
112 -- Open cursor to retrieve requirement header from requirement header inqueue
113 OPEN c_get_requirement_from_apps (p_record.REQUIREMENT_HEADER_ID);
114 FETCH c_get_requirement_from_apps INTO r_get_requirement_from_apps;
115 CLOSE c_get_requirement_from_apps;
116
117 -- Initialization of the requirement header with Apps header record
118 l_header_rec.REQUIREMENT_HEADER_ID := r_get_requirement_from_apps.REQUIREMENT_HEADER_ID;
119 l_header_rec.REQUISITION_NUMBER := r_get_requirement_from_apps.REQUIREMENT_HEADER_ID;
120 l_header_rec.ORDER_TYPE_ID := fnd_profile.value('CSP_ORDER_TYPE');
121 l_header_rec.SHIP_TO_LOCATION_ID := r_get_requirement_from_apps.SHIP_TO_LOCATION_ID;
122 l_header_rec.NEED_BY_DATE := r_get_requirement_from_apps.NEED_BY_DATE;
123 l_header_rec.DEST_ORGANIZATION_ID := r_get_requirement_from_apps.DESTINATION_ORGANIZATION_ID;
124 l_header_rec.DEST_SUBINVENTORY := r_get_requirement_from_apps.DESTINATION_SUBINVENTORY;
125 l_header_rec.OPERATION := CSP_PARTS_REQUIREMENT.G_OPR_CREATE;
126 l_header_rec.RESOURCE_TYPE := r_get_requirement_from_apps.RESOURCE_TYPE;
127 l_header_rec.RESOURCE_ID := r_get_requirement_from_apps.RESOURCE_ID;
128
129 END IF;
130
131 -- Initialization of the requirement line
132 l_line_rec.REQUIREMENT_LINE_ID := p_record.REQUIREMENT_LINE_ID;
133 l_line_rec.LINE_NUM := p_record.REQUIREMENT_LINE_ID;
134 l_line_rec.INVENTORY_ITEM_ID := p_record.INVENTORY_ITEM_ID;
135 l_line_rec.QUANTITY := p_record.REQUIRED_QUANTITY;
136 l_line_rec.ORDERED_QUANTITY := p_record.REQUIRED_QUANTITY;
137 l_line_rec.UNIT_OF_MEASURE := p_record.UOM_CODE;
138
139 l_line_table(1) := l_line_rec;
140
141 CSP_PARTS_REQUIREMENT.Process_Requirement
142 ( P_API_VERSION => 1
143 , PX_HEADER_REC => l_header_rec
144 , PX_LINE_TABLE => l_line_table
145 , P_CREATE_ORDER_FLAG => 'Y'
146 , X_RETURN_STATUS => x_return_status
147 , X_MSG_COUNT => l_msg_count
148 , X_MSG_DATA => l_msg_data
149 );
150
151 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
152 /*** exception occurred in API -> return errmsg ***/
153 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
154 (
155 p_api_error => TRUE
156 );
157 END IF;
158
159 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
160 jtm_message_log_pkg.Log_Msg
161 ( v_object_id => p_record.requirement_line_id -- put PK column here
162 , v_object_name => g_object_name
163 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
164 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
165 END IF;
166
167 EXCEPTION WHEN OTHERS THEN
168 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
169 jtm_message_log_pkg.Log_Msg
170 ( v_object_id => p_record.requirement_line_id -- put PK column here
171 , v_object_name => g_object_name
172 , v_message => 'Exception occurred in APPLY_INSERT:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
173 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
174 END IF;
175
176 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_INSERT', sqlerrm);
177 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
178 (
179 p_api_error => TRUE
180 );
181
182 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
183 jtm_message_log_pkg.Log_Msg
184 ( v_object_id => p_record.requirement_line_id -- put PK column here
185 , v_object_name => g_object_name
186 , v_message => 'Leaving ' || g_object_name || '.APPLY_INSERT'
187 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
188 END IF;
189
190 x_return_status := FND_API.G_RET_STS_ERROR;
191 END APPLY_INSERT;
192
193 /***
194 This procedure is called by APPLY_CLIENT_CHANGES when an updated record is to be processed.
195 ***/
196 PROCEDURE APPLY_UPDATE
197 (
198 p_record IN c_requirements%ROWTYPE,
199 p_error_msg OUT NOCOPY VARCHAR2,
200 x_return_status IN OUT NOCOPY VARCHAR2
201 ) IS
202 BEGIN
203 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
204 jtm_message_log_pkg.Log_Msg
205 ( v_object_id => p_record.requirement_line_id -- put PK column here
206 , v_object_name => g_object_name
207 , v_message => 'Entering ' || g_object_name || '.APPLY_UPDATE'
208 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
209 END IF;
210
211 -- No update possible so return success
212 x_return_status := FND_API.G_RET_STS_SUCCESS;
213
214 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
215 /*** exception occurred in API -> return errmsg ***/
216 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
217 (
218 p_api_error => TRUE
219 );
220 END IF;
221
222 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
223 jtm_message_log_pkg.Log_Msg
224 ( v_object_id => p_record.requirement_line_id -- put PK column here
225 , v_object_name => g_object_name
226 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
227 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
228 END IF;
229
230 EXCEPTION WHEN OTHERS THEN
231 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
232 jtm_message_log_pkg.Log_Msg
233 ( v_object_id => p_record.requirement_line_id -- put PK column here
234 , v_object_name => g_object_name
235 , v_message => 'Exception occurred in APPLY_UPDATE:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
236 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
237 END IF;
238
239 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_UPDATE', sqlerrm);
240 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
241 (
242 p_api_error => TRUE
243 );
244
245 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
246 jtm_message_log_pkg.Log_Msg
247 ( v_object_id => p_record.requirement_line_id -- put PK column here
248 , v_object_name => g_object_name
249 , v_message => 'Leaving ' || g_object_name || '.APPLY_UPDATE'
250 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
251 END IF;
252
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 END APPLY_UPDATE;
255
256 /***
257 This procedure is called by APPLY_CLIENT_CHANGES for every record in inqueue that needs to be processed.
258 ***/
259 PROCEDURE APPLY_RECORD
260 (
261 p_record IN c_requirements%ROWTYPE
262 , p_user_name IN VARCHAR2
263 , p_tranid IN NUMBER
264 , p_get_inqueue_header IN BOOLEAN
265 , p_error_msg OUT NOCOPY VARCHAR2
266 , x_return_status IN OUT NOCOPY VARCHAR2
267 ) IS
268 BEGIN
269 /*** initialize return status and message list ***/
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
273 jtm_message_log_pkg.Log_Msg
274 ( v_object_id => p_record.requirement_line_id -- put PK column here
275 , v_object_name => g_object_name
276 , v_message => 'Entering ' || g_object_name || '.APPLY_RECORD'
277 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
278 END IF;
279
280 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
281 jtm_message_log_pkg.Log_Msg
282 ( v_object_id => p_record.requirement_line_id -- put PK column here
283 , v_object_name => g_object_name
284 , v_message => 'Processing requirement = ' || p_record.requirement_line_id /* put PK column here */ || FND_GLOBAL.LOCAL_CHR(10) ||
285 'DMLTYPE = ' || p_record.dmltype$$
286 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
287 END IF;
288
289 IF p_record.dmltype$$='I' THEN
290 -- Process insert
291 APPLY_INSERT
292 (
293 p_record,
294 p_user_name,
295 p_tranid,
296 p_get_inqueue_header,
297 p_error_msg,
298 x_return_status
299 );
300 ELSIF p_record.dmltype$$='U' THEN
301 -- Process update
302 APPLY_UPDATE
303 (
304 p_record,
305 p_error_msg,
306 x_return_status
307 );
308 ELSIF p_record.dmltype$$='D' THEN
309 -- Process delete; not supported for this entity
310 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
311 jtm_message_log_pkg.Log_Msg
312 ( v_object_id => p_record.requirement_line_id -- put PK column here
313 , v_object_name => g_object_name
314 , v_message => 'Delete is not supported for this entity'
315 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
316 END IF;
317
318 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
319 (
320 p_message => 'CSL_DML_OPERATION'
321 , p_token_name1 => 'DML'
322 , p_token_value1 => p_record.dmltype$$
323 );
324
325 x_return_status := FND_API.G_RET_STS_ERROR;
326 ELSE
327 -- invalid dml type
328 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
329 jtm_message_log_pkg.Log_Msg
333 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
330 ( v_object_id => p_record.requirement_line_id -- put PK column here
331 , v_object_name => g_object_name
332 , v_message => 'Invalid DML type: ' || p_record.dmltype$$
334 END IF;
335
336 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
337 (
338 p_message => 'CSL_DML_OPERATION'
339 , p_token_name1 => 'DML'
340 , p_token_value1 => p_record.dmltype$$
341 );
342
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 END IF;
345
346 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
347 jtm_message_log_pkg.Log_Msg
348 ( v_object_id => p_record.requirement_line_id -- put PK column here
349 , v_object_name => g_object_name
350 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
351 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
352 END IF;
353 EXCEPTION WHEN OTHERS THEN
354 /*** defer record when any process exception occurs ***/
355 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
356 jtm_message_log_pkg.Log_Msg
357 ( v_object_id => p_record.requirement_line_id -- put PK column here
358 , v_object_name => g_object_name
359 , v_message => 'Exception occurred in APPLY_RECORD:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
360 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
361 END IF;
362
363 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
364 p_error_msg := CSL_SERVICEL_WRAPPER_PKG.GET_ERROR_MESSAGE_TEXT
365 (
366 p_api_error => TRUE
367 );
368
369 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
370 jtm_message_log_pkg.Log_Msg
371 ( v_object_id => p_record.requirement_line_id -- put PK column here
372 , v_object_name => g_object_name
373 , v_message => 'Leaving ' || g_object_name || '.APPLY_RECORD'
374 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
375 END IF;
376
377 x_return_status := FND_API.G_RET_STS_ERROR;
378 END APPLY_RECORD;
379
380 /***
381 This procedure is called by PROCESS_REQS and deletes all requirement headers from the inqueue,
382 for a given user and transaction.
383 ***/
384 PROCEDURE DELETE_REQ_HEADERS_FROM_INQ
385 (
386 p_user_name IN VARCHAR2,
387 p_tranid IN NUMBER,
388 x_return_status IN OUT NOCOPY VARCHAR2
389 ) IS
390
391 l_error_msg VARCHAR2(4000);
392
393 /***
394 Cursor to retrieve all requirement headers for this user_name and tranid.
395 This one is to be executed after all requirement lines with headers have been deleted from the inqueue.
396 ***/
397 CURSOR c_get_req_headers_from_inq ( b_user_name VARCHAR2, b_tranid NUMBER) is
398 SELECT *
399 FROM CSL_CSP_REQUIREMENT_HDR_inq
400 WHERE tranid$$ = b_tranid
401 AND clid$$cs = b_user_name;
402
403 BEGIN
404
405 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
406 jtm_message_log_pkg.Log_Msg
407 ( v_object_id => null
408 , v_object_name => g_object_name
409 , v_message => 'Entering ' || g_object_name || '.DELETE_REQ_HEADERS_FROM_INQ'
410 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
411 END IF;
412
413 -- Loop through this cursor to delete all requirement headers from the requirement header inqueue
414 FOR r_get_req_headers_from_inq IN c_get_req_headers_from_inq ( p_user_name, p_tranid) LOOP
415
416 -- Delete the requirement header from the requirement header inqueue.
417 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
418 (
419 p_user_name,
420 p_tranid,
421 r_get_req_headers_from_inq.seqno$$,
422 r_get_req_headers_from_inq.requirement_header_id, -- put PK column here
423 g_object_name,
424 g_pub_name2,
425 l_error_msg,
426 x_return_status
427 );
428
429 /*** was delete successful? ***/
430 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
431 /*** no -> rollback ***/
432 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
433 jtm_message_log_pkg.Log_Msg
434 ( v_object_id => r_get_req_headers_from_inq.requirement_header_id -- put PK column here
435 , v_object_name => g_object_name
436 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
437 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
438 END IF;
439 ROLLBACK TO save_rec;
440 END IF;
441
442 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
443 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
444 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
445 jtm_message_log_pkg.Log_Msg
446 ( v_object_id => r_get_req_headers_from_inq.requirement_header_id -- put PK column here
447 , v_object_name => g_object_name
448 , v_message => 'Record not processed successfully, deferring and rejecting record'
449 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
450 END IF;
451
452 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
453 (
454 p_user_name
458 , g_object_name
455 , p_tranid
456 , r_get_req_headers_from_inq.seqno$$
457 , r_get_req_headers_from_inq.requirement_header_id -- put PK column here
459 , g_pub_name2
460 , l_error_msg
461 , x_return_status
462 );
463
464 /*** Was defer successful? ***/
465 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
466 /*** no -> rollback ***/
467 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
468 jtm_message_log_pkg.Log_Msg
469 ( v_object_id => r_get_req_headers_from_inq.requirement_header_id -- put PK column here
470 , v_object_name => g_object_name
471 , v_message => 'Defer record failed, rolling back to savepoint'
472 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
473 END IF;
474 ROLLBACK TO save_rec;
475 END IF;
476 END IF;
477 END LOOP;
478
479 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
480 jtm_message_log_pkg.Log_Msg
481 ( v_object_id => null
482 , v_object_name => g_object_name
483 , v_message => 'Leaving ' || g_object_name || '.DELETE_REQ_HEADERS_FROM_INQ'
484 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
485 END IF;
486
487 EXCEPTION WHEN OTHERS THEN
488 /*** catch and log exceptions ***/
489 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
490 jtm_message_log_pkg.Log_Msg
491 ( v_object_id => null
492 , v_object_name => g_object_name
493 , v_message => 'Exception occurred in DELETE_REQ_HEADERS_FROM_INQ:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
494 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
495 END IF;
496
497 x_return_status := FND_API.G_RET_STS_ERROR;
498 END DELETE_REQ_HEADERS_FROM_INQ;
499
500 /***
501 This procedure is called by APPLY_CLIENT_CHANGES and processes all inqueue requirement lines,
502 that have a requirement header in the requirement header inqueue.
503 ***/
504 PROCEDURE PROCESS_REQS
505 (
506 p_user_name IN VARCHAR2,
507 p_tranid IN NUMBER,
508 x_return_status IN OUT NOCOPY VARCHAR2
509 ) IS
510
511 l_error_msg VARCHAR2(4000);
512
513 BEGIN
514
515 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
516 jtm_message_log_pkg.Log_Msg
517 ( v_object_id => null
518 , v_object_name => g_object_name
519 , v_message => 'Entering ' || g_object_name || '.Process_Reqs'
520 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
521 END IF;
522
523 /*** loop through c_requirements records in inqueue ***/
524 FOR r_requirements IN c_requirements ( p_user_name, p_tranid) LOOP
525
526 SAVEPOINT save_rec;
527
528 /*** apply record ***/
529 APPLY_RECORD
530 (
531 r_requirements
532 , p_user_name
533 , p_tranid
534 , true -- requirement line has a header record in the inqueue which should be fetched.
535 , l_error_msg
536 , x_return_status
537 );
538
539 /*** was record processed successfully? ***/
540 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
541 /*** Yes -> delete record from inqueue ***/
542 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
543 jtm_message_log_pkg.Log_Msg
544 ( v_object_id => r_requirements.requirement_line_id -- put PK column here
545 , v_object_name => g_object_name
546 , v_message => 'Record successfully processed, deleting from inqueue'
547 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
548 END IF;
549
550 -- Delete the requirement line from the requirement line inqueue.
551 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
552 (
553 p_user_name,
554 p_tranid,
555 r_requirements.seqno$$,
556 r_requirements.requirement_line_id, -- put PK column here
557 g_object_name,
558 g_pub_name,
559 l_error_msg,
560 x_return_status
561 );
562
563 /*** was delete successful? ***/
564 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
565 /*** no -> rollback ***/
566 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
567 jtm_message_log_pkg.Log_Msg
568 ( v_object_id => r_requirements.requirement_line_id -- put PK column here
569 , v_object_name => g_object_name
570 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
571 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
572 END IF;
573 ROLLBACK TO save_rec;
574 END IF;
575 END IF;
576
577 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
578 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
579 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
580 jtm_message_log_pkg.Log_Msg
581 ( v_object_id => r_requirements.requirement_line_id -- put PK column here
582 , v_object_name => g_object_name
583 , v_message => 'Record not processed successfully, deferring and rejecting record'
587 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
584 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
585 END IF;
586
588 (
589 p_user_name
590 , p_tranid
591 , r_requirements.seqno$$
592 , r_requirements.requirement_line_id -- put PK column here
593 , g_object_name
594 , g_pub_name
595 , l_error_msg
596 , x_return_status
597 );
598
599 /*** Was defer successful? ***/
600 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
601 /*** no -> rollback ***/
602 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
603 jtm_message_log_pkg.Log_Msg
604 ( v_object_id => r_requirements.requirement_line_id -- put PK column here
605 , v_object_name => g_object_name
606 , v_message => 'Defer record failed, rolling back to savepoint'
607 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
608 END IF;
609 ROLLBACK TO save_rec;
610 END IF;
611 END IF;
612 END LOOP;
613
614 -- Call delete procedure to delete all requirement headers from requirement header inqueue.
615 DELETE_REQ_HEADERS_FROM_INQ(p_user_name, p_tranid, x_return_status);
616
617 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
618 jtm_message_log_pkg.Log_Msg
619 ( v_object_id => null
620 , v_object_name => g_object_name
621 , v_message => 'Leaving ' || g_object_name || '.Process_Reqs'
622 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
623 END IF;
624
625 EXCEPTION WHEN OTHERS THEN
626 /*** catch and log exceptions ***/
627 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
628 jtm_message_log_pkg.Log_Msg
629 ( v_object_id => null
630 , v_object_name => g_object_name
631 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
632 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
633 END IF;
634
635 x_return_status := FND_API.G_RET_STS_ERROR;
636 END PROCESS_REQS;
637
638 /***
639 This procedure is called by APPLY_CLIENT_CHANGES and processes all inqueue requirement lines,
640 that have no requirement header in the requirement header inqueue (but have on in Apps).
641 ***/
642 PROCEDURE PROCESS_REQS_NO_HEADERS
643 (
644 p_user_name IN VARCHAR2,
645 p_tranid IN NUMBER,
646 x_return_status IN OUT NOCOPY VARCHAR2
647 ) IS
648
649 l_error_msg VARCHAR2(4000);
650
651 BEGIN
652
653 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
654 jtm_message_log_pkg.Log_Msg
655 ( v_object_id => null
656 , v_object_name => g_object_name
657 , v_message => 'Entering ' || g_object_name || '.Process_Reqs_No_Headers'
658 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
659 END IF;
660
661 /*** loop through c_requirements_no_headers records in inqueue ***/
662 FOR r_requirements IN c_requirements_no_headers ( p_user_name, p_tranid) LOOP
663
664 SAVEPOINT save_rec;
665
666 /*** apply record ***/
667 APPLY_RECORD
668 (
669 r_requirements
670 , p_user_name
671 , p_tranid
672 , false -- requirement line has no header record in the inqueue but only in apps and this should be fetched.
673 , l_error_msg
674 , x_return_status
675 );
676
677 /*** was record processed successfully? ***/
678 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
679 /*** Yes -> delete record from inqueue ***/
680 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
681 jtm_message_log_pkg.Log_Msg
682 ( v_object_id => r_requirements.requirement_line_id -- put PK column here
683 , v_object_name => g_object_name
684 , v_message => 'Record successfully processed, deleting from inqueue'
685 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
686 END IF;
687
688 CSL_SERVICEL_WRAPPER_PKG.DELETE_RECORD
689 (
690 p_user_name,
691 p_tranid,
692 r_requirements.seqno$$,
693 r_requirements.requirement_line_id, -- put PK column here
694 g_object_name,
695 g_pub_name,
696 l_error_msg,
697 x_return_status
698 );
699
700 /*** was delete successful? ***/
701 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
702 /*** no -> rollback ***/
703 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
704 jtm_message_log_pkg.Log_Msg
705 ( v_object_id => r_requirements.requirement_line_id -- put PK column here
706 , v_object_name => g_object_name
707 , v_message => 'Deleting from inqueue failed, rolling back to savepoint'
708 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
709 END IF;
710 ROLLBACK TO save_rec;
711 END IF;
712 END IF;
713
714 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
715 /*** Record was not processed successfully or delete failed -> defer and reject record ***/
716 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
720 , v_message => 'Record not processed successfully, deferring and rejecting record'
717 jtm_message_log_pkg.Log_Msg
718 ( v_object_id => r_requirements.requirement_line_id -- put PK column here
719 , v_object_name => g_object_name
721 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
722 END IF;
723
724 CSL_SERVICEL_WRAPPER_PKG.DEFER_RECORD
725 (
726 p_user_name
727 , p_tranid
728 , r_requirements.seqno$$
729 , r_requirements.requirement_line_id -- put PK column here
730 , g_object_name
731 , g_pub_name
732 , l_error_msg
733 , x_return_status
734 );
735
736 /*** Was defer successful? ***/
737 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
738 /*** no -> rollback ***/
739 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
740 jtm_message_log_pkg.Log_Msg
741 ( v_object_id => r_requirements.requirement_line_id -- put PK column here
742 , v_object_name => g_object_name
743 , v_message => 'Defer record failed, rolling back to savepoint'
744 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
745 END IF;
746 ROLLBACK TO save_rec;
747 END IF;
748 END IF;
749 END LOOP;
750
751 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
752 jtm_message_log_pkg.Log_Msg
753 ( v_object_id => null
754 , v_object_name => g_object_name
755 , v_message => 'Leaving ' || g_object_name || '.Process_Reqs_No_Headers'
756 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
757 END IF;
758
759 EXCEPTION WHEN OTHERS THEN
760 /*** catch and log exceptions ***/
761 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR THEN
762 jtm_message_log_pkg.Log_Msg
763 ( v_object_id => null
764 , v_object_name => g_object_name
765 , v_message => 'Exception occurred in APPLY_CLIENT_CHANGES:' || FND_GLOBAL.LOCAL_CHR(10) || sqlerrm
766 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
767 END IF;
768
769 x_return_status := FND_API.G_RET_STS_ERROR;
770
771 END PROCESS_REQS_NO_HEADERS;
772
773 /***
774 This procedure is called by CSL_SERVICEL_WRAPPER_PKG when publication item CSP_REQUIREMENT_LINES
775 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
776 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
777 public APIs.
778 ***/
779 PROCEDURE APPLY_CLIENT_CHANGES
780 (
781 p_user_name IN VARCHAR2,
782 p_tranid IN NUMBER,
783 p_debug_level IN NUMBER,
784 x_return_status IN OUT NOCOPY VARCHAR2
785 ) IS
786
787 l_process_status VARCHAR2(1);
788 l_error_msg VARCHAR2(4000);
789
790 BEGIN
791
792 g_debug_level := p_debug_level;
793 x_return_status := FND_API.G_RET_STS_SUCCESS;
794
795 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
796 jtm_message_log_pkg.Log_Msg
797 ( v_object_id => null
798 , v_object_name => g_object_name
799 , v_message => 'Entering ' || g_object_name || '.Apply_Client_Changes'
800 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
801 END IF;
802
803 -- First process all requirement lines that have a requirement header. Delete them after processing.
804 PROCESS_REQS (p_user_name, p_tranid, x_return_status);
805
809 IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
806 -- Then process all remaining requirement lines (no requirement header). These already have a requirement header in Apps.
807 PROCESS_REQS_NO_HEADERS(p_user_name, p_tranid, x_return_status);
808
810 jtm_message_log_pkg.Log_Msg
811 ( v_object_id => null
812 , v_object_name => g_object_name
813 , v_message => 'Leaving ' || g_object_name || '.Apply_Client_Changes'
814 , v_level_id => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
815 END IF;
816
817 END APPLY_CLIENT_CHANGES;
818 END CSL_REQUIREMENTS_PKG;