[Home] [Help]
PACKAGE BODY: APPS.CSM_UNDO_PKG
Source
1 PACKAGE BODY CSM_UNDO_PKG AS
2 /* $Header: csmucudb.pls 120.2 2011/01/10 10:27:49 saradhak noship $ */
3
4 error EXCEPTION;
5
6
7 /*** Globals ***/
8 g_object_name CONSTANT VARCHAR2(30) := 'CSM_UNDO_PKG';
9 g_pub_name CONSTANT VARCHAR2(30) := 'CSM_CLIENT_UNDO_REQUEST';
10 g_debug_level NUMBER; -- debug level
11
12 /* Select all inq records */
13 CURSOR c_client_undo( b_user_name VARCHAR2, b_tranid NUMBER) is
14 SELECT *
15 FROM CSM_CLIENT_UNDO_REQUEST_INQ
16 WHERE tranid$$ = b_tranid
17 AND clid$$cs = b_user_name;
18 /***
19 This procedure is called by APPLY_CLIENT_CHANGES when an inserted record is to be processed.
20 ***/
21 PROCEDURE APPLY_UNDO
22 (
23 p_record IN c_client_undo%ROWTYPE,
24 p_error_msg OUT NOCOPY VARCHAR2,
25 x_return_status IN OUT NOCOPY VARCHAR2
26 )
27 IS
28
29
30 /*CURSOR c_get_undo_inq ( c_user_name VARCHAR2, c_tranid NUMBER,c_pk1_value NUMBER)
31 IS
32 SELECT SEQNO$$
33 FROM CSM_CLIENT_UNDO_REQUEST_INQ
34 WHERE tranid$$ = c_tranid
35 AND clid$$cs = c_user_name
36 AND PK1_VALUE = c_pk1_value;
37 */
38 CURSOR c_get_ta_from_inq(c_task_assignment_id NUMBER, c_user_name VARCHAR2)
39 IS
40 SELECT INQ.TASK_ASSIGNMENT_ID,INQ.TRANID$$,INQ.SEQNO$$,DMLTYPE$$, TASK_ID
41 FROM CSM_TASK_ASSIGNMENTS_INQ INQ
42 WHERE INQ.TASK_ASSIGNMENT_ID = c_task_assignment_id
43 AND INQ.CLID$$CS = c_user_name;
44
45 CURSOR c_get_ta_from_acc(c_task_assignment_id NUMBER,c_user_id NUMBER)
46 IS
47 SELECT ACCESS_ID
48 FROM CSM_TASK_ASSIGNMENTS_ACC acc
49 WHERE ACC.USER_ID = c_user_id
50 AND ACC.TASK_ASSIGNMENT_ID = c_task_assignment_id;
51
52 CURSOR c_get_ta_from_base(c_task_assignment_id NUMBER)
53 IS
54 SELECT TASK_ID
55 FROM JTF_TASK_ASSIGNMENTS b
56 WHERE B.TASK_ASSIGNMENT_ID = c_task_assignment_id;
57
58 CURSOR c_get_taa_from_inq(c_task_assignment_id NUMBER, c_user_name VARCHAR2)
59 IS
60 SELECT INQ.ASSIGNMENT_AUDIT_ID,INQ.TRANID$$,INQ.SEQNO$$,INQ.DMLTYPE$$
61 FROM CSM_TASK_ASSIGNMENTS_AUDIT_INQ INQ
62 WHERE INQ.ASSIGNMENT_ID = c_task_assignment_id
63 AND INQ.CLID$$CS = c_user_name;
64
65 CURSOR c_get_task_from_inq(c_task_id NUMBER,c_user_name VARCHAR2)
66 IS
67 SELECT INQ.TRANID$$,INQ.SEQNO$$,DMLTYPE$$,SOURCE_OBJECT_TYPE_CODE,SOURCE_OBJECT_ID
68 FROM CSM_TASKS_INQ inq
69 WHERE inq.TASK_ID = c_task_id
70 AND inq.CLID$$CS = c_user_name;
71
72 CURSOR c_get_task_from_acc(c_task_id NUMBER,c_user_id NUMBER)
73 IS
74 SELECT ACCESS_ID
75 FROM CSM_TASKS_ACC acc
76 WHERE acc.TASK_ID = c_task_id
77 AND acc.USER_ID = c_user_id;
78
79 CURSOR c_get_task_from_base(c_task_id NUMBER)
80 IS
81 SELECT SOURCE_OBJECT_TYPE_CODE,SOURCE_OBJECT_ID
82 FROM JTF_TASKS_B b
83 WHERE b.TASK_ID = c_task_id;
84
85 CURSOR c_get_incident_from_inq(c_incident_id NUMBER,c_user_name VARCHAR2)
86 IS
87 SELECT INQ.TRANID$$,INQ.SEQNO$$,DMLTYPE$$
88 FROM CSM_INCIDENTS_ALL_INQ inq
89 WHERE inq.INCIDENT_ID = c_incident_id
90 AND inq.CLID$$CS = c_user_name;
91
92 CURSOR c_get_incident_from_acc(c_incident_id NUMBER,c_user_id NUMBER)
93 IS
94 SELECT ACCESS_ID
95 FROM CSM_INCIDENTS_ALL_ACC acc
96 WHERE acc.USER_ID = c_user_id
97 AND acc.INCIDENT_ID = c_incident_id;
98
99 CURSOR c_get_debrief_header(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
100 IS
101 SELECT ACCESS_ID,INQ.DEBRIEF_HEADER_ID,INQ.TRANID$$,INQ.SEQNO$$
102 FROM CSM_DEBRIEF_HEADERS_ACC acc,
103 CSM_DEBRIEF_HEADERS_INQ inq
104 WHERE acc.USER_ID(+) = c_user_id
105 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
106 AND inq.DEBRIEF_HEADER_ID = acc.DEBRIEF_HEADER_ID(+)
107 AND inq.CLID$$CS = c_user_name;
108
109
110 CURSOR c_get_debrief_expenses(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
111 IS
112 SELECT ACCESS_ID,INQ.DEBRIEF_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
113 FROM CSM_DEBRIEF_LINES_ACC acc,
114 CSF_M_DEBRIEF_EXPENSES_INQ inq
115 WHERE acc.USER_ID(+) = c_user_id
116 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
117 AND inq.DEBRIEF_LINE_ID = acc.DEBRIEF_LINE_ID(+)
118 AND inq.CLID$$CS = c_user_name;
119
120 CURSOR c_get_debrief_labor(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
121 IS
122 SELECT ACCESS_ID,INQ.DEBRIEF_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
123 FROM CSM_DEBRIEF_LINES_ACC acc,
124 CSF_M_DEBRIEF_LABOR_INQ inq
125 WHERE acc.USER_ID(+) = c_user_id
126 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
127 AND inq.DEBRIEF_LINE_ID = acc.DEBRIEF_LINE_ID(+)
128 AND inq.CLID$$CS = c_user_name;
129
130 CURSOR c_get_debrief_parts(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
131 IS
132 SELECT ACCESS_ID,INQ.DEBRIEF_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
133 FROM CSM_DEBRIEF_LINES_ACC acc,
134 CSF_M_DEBRIEF_PARTS_INQ inq
135 WHERE acc.USER_ID(+) = c_user_id
136 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
137 AND inq.DEBRIEF_LINE_ID = acc.DEBRIEF_LINE_ID(+)
138 AND inq.CLID$$CS = c_user_name;
139
140 CURSOR c_get_req_header(c_task_assg_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
141 IS
142 SELECT ACCESS_ID,INQ.REQUIREMENT_HEADER_ID,INQ.TRANID$$,INQ.SEQNO$$
143 FROM CSM_REQ_HEADERS_ACC acc,
144 CSM_REQ_HEADERS_INQ inq
145 WHERE acc.USER_ID(+) = c_user_id
146 AND inq.TASK_ASSIGNMENT_ID = c_task_assg_id
147 AND inq.REQUIREMENT_HEADER_ID = acc.REQUIREMENT_HEADER_ID(+)
148 AND inq.CLID$$CS = c_user_name;
149
150 CURSOR c_get_req_line(c_req_header_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
151 IS
152 SELECT ACCESS_ID,INQ.REQUIREMENT_LINE_ID,INQ.TRANID$$,INQ.SEQNO$$
153 FROM CSM_REQ_LINES_ACC acc,
154 CSM_REQ_LINES_INQ inq
155 WHERE acc.USER_ID (+) = c_user_id
156 AND inq.REQUIREMENT_HEADER_ID = c_req_header_id
157 AND inq.REQUIREMENT_LINE_ID = acc.REQUIREMENT_LINE_ID (+)
158 AND inq.CLID$$CS = c_user_name;
159
160 CURSOR c_get_notes(c_task_id NUMBER,c_incident_id NUMBER,c_debrief_header_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
161 IS
162 SELECT ACCESS_ID,INQ.JTF_NOTE_ID,INQ.TRANID$$,INQ.SEQNO$$, INQ.DMLTYPE$$
163 FROM CSM_NOTES_ACC acc,
164 CSF_M_NOTES_INQ inq
165 WHERE acc.USER_ID (+) = c_user_id
166 AND inq.JTF_NOTE_ID = acc.JTF_NOTE_ID (+)
167 AND inq.CLID$$CS = c_user_name
168 AND (
169 ( inq.SOURCE_OBJECT_CODE = 'TASK' AND inq.SOURCE_OBJECT_ID = c_task_id )
170 OR ( inq.SOURCE_OBJECT_CODE = 'SR' AND inq.SOURCE_OBJECT_ID = c_incident_id)
171 OR ( inq.SOURCE_OBJECT_CODE = 'SD' AND inq.SOURCE_OBJECT_ID = c_debrief_header_id)
172 );
173
174 CURSOR c_get_lobs(c_task_id NUMBER,c_incident_id NUMBER,c_user_id NUMBER,c_user_name VARCHAR2)
175 IS
176 SELECT ACCESS_ID,INQ.FILE_ID,INQ.TRANID$$,INQ.SEQNO$$, INQ.DMLTYPE$$
177 FROM CSM_FND_LOBS_ACC acc,
178 CSF_M_LOBS_INQ inq
179 WHERE acc.USER_ID (+) = c_user_id
180 AND inq.FILE_ID = acc.FILE_ID (+)
181 AND inq.CLID$$CS = c_user_name
182 AND (
183 ( inq.ENTITY_NAME = 'JTF_TASKS_B' AND inq.PK1_VALUE = c_task_id )
184 OR ( inq.ENTITY_NAME = 'CS_INCIDENTS' AND inq.PK1_VALUE = c_incident_id)
185 );
186
187 CURSOR c_get_user_id (c_user_name VARCHAR2)
188 IS
189 SELECT USER_ID
190 FROM ASG_USER
191 WHERE USER_NAME = c_user_name;
192
193 --Pub items and object declarations
194
195
196 l_ta_obj_name VARCHAR2(30) := 'CSM_TASK_ASSIGNMENT_PKG'; -- package name
197 l_ta_pub_name VARCHAR2(30) := 'CSM_TASK_ASSIGNMENTS';
198
199 l_taa_obj_name VARCHAR2(30) := 'CSM_TA_AUDIT_PKG'; -- package name
200 l_taa_pub_name VARCHAR2(30) := 'CSM_TASK_ASSIGNMENTS_AUDIT';
201
202 l_task_obj_name VARCHAR2(30) := 'CSM_TASKS_PKG'; -- package name
203 l_task_pub_name VARCHAR2(30) := 'CSM_TASKS';
204
205 l_sr_obj_name VARCHAR2(30) := 'CSM_SERVICE_REQUEST_PKG';
206 l_sr_pub_name VARCHAR2(30) := 'CSM_INCIDENTS_ALL';
207
208 l_dbh_obj_name VARCHAR2(30) := 'CSM_DEBRIEF_HEADERS_PKG';
209 l_dbh_pub_name VARCHAR2(30) := 'CSM_DEBRIEF_HEADERS';
210
211 l_dble_obj_name VARCHAR2(30) := 'CSM_DEBRIEF_EXPENSES_PKG';
212 l_dble_pub_name VARCHAR2(30) := 'CSF_M_DEBRIEF_EXPENSES';
213
214 l_dbll_obj_name VARCHAR2(30) := 'CSM_DEBRIEF_LABOR_PKG';
215 l_dbll_pub_name VARCHAR2(30) := 'CSF_M_DEBRIEF_LABOR';
216
217 l_dblp_obj_name VARCHAR2(30) := 'CSM_DEBRIEF_PARTS_PKG';
218 l_dblp_pub_name VARCHAR2(30) := 'CSF_M_DEBRIEF_PARTS';
219
220 l_notes_obj_name VARCHAR2(30) := 'CSM_NOTES_PKG';
221 l_notes_pub_name VARCHAR2(30) := 'CSF_M_NOTES';
222
223 l_reqh_obj_name VARCHAR2(30) := 'CSM_REQUIREMENTS_PKG';
224 l_reqh_pub_name VARCHAR2(30) := 'CSM_REQ_HEADERS';
225
226 l_reql_obj_name VARCHAR2(30) := 'CSM_REQUIREMENTS_PKG';
227 l_reql_pub_name VARCHAR2(30) := 'CSM_REQ_LINES';
228
229 l_lobs_obj_name VARCHAR2(30) := 'CSM_LOBS_PKG';
230 l_lobs_pub_name VARCHAR2(30) := 'CSF_M_LOBS';
231
232 l_undo_pub_name VARCHAR2(30) := 'CSM_CLIENT_UNDO_REQUEST';
233
234 l_access_id NUMBER;
235 l_mark_dirty BOOLEAN;
236 l_incident_id NUMBER;
237 l_debrief_header_id NUMBER;
238 l_tran_id NUMBER;
239 l_user_name VARCHAR2(100);
240 l_user_id NUMBER;
241 l_task_assignment_id NUMBER;
242 l_task_assignment_id_tmp NUMBER;
243 l_task_id NUMBER;
244 l_debrief_line_id NUMBER;
245 l_req_header_id NUMBER;
246 l_req_line_id NUMBER;
247 l_note_id NUMBER;
248 l_process_status VARCHAR2(1);
249 l_error_msg VARCHAR2(4000);
250 l_markdirty_all VARCHAR2(1):= 'Y';
251 l_sequence NUMBER;
252 l_dml_type VARCHAR2(1) := '';
253 l_source_object_type VARCHAR2(240);
254
255 type t_curs is ref cursor;
256 cur t_curs;
257
258 r_pub_item_store Varchar2(100);
259
260 BEGIN
261 --set variables from the record
262 l_user_name := p_record.CLID$$CS;
263 l_tran_id := p_record.TRANID$$;
264 l_sequence := p_record.SEQNO$$;
265 l_task_assignment_id := p_record.PK1_VALUE;
266
267 l_error_msg := 'Deferring Record As UNDO Called For';
268
269 OPEN c_get_user_id(l_user_name);
270 FETCH c_get_user_id INTO l_user_id;
271 CLOSE c_get_user_id;
272
273 IF l_user_id Is NULL OR l_user_name IS NULL THEN
274 CSM_UTIL_PKG.log( g_object_name || '.APPLY_UNDO:'
275 || ' Task Assignment ID :' || l_task_assignment_id
276 || 'UNDO Failed.User Invalid',
277 g_object_name || '.APPLY_UNDO',FND_LOG.LEVEL_ERROR );
278 x_return_status := FND_API.G_RET_STS_SUCCESS;
279 RETURN;
280 END IF;
281
282
283 --Check Task Assignment ID
284 OPEN c_get_ta_from_inq (l_task_assignment_id ,l_user_name );
285 FETCH c_get_ta_from_inq INTO l_task_assignment_id_tmp,l_tran_id,l_sequence,l_dml_type,l_task_id;
286 CLOSE c_get_ta_from_inq;
287
288 IF l_dml_type = 'U' THEN
289 OPEN c_get_ta_from_acc (l_task_assignment_id ,l_user_id );
290 FETCH c_get_ta_from_acc INTO l_access_id;
291 CLOSE c_get_ta_from_acc;
292 --Check if the task assignment has been changed to another user before this UNDO
293 IF l_access_id IS NULL THEN
294 l_markdirty_all := 'N';
295 CSM_UTIL_PKG.log( g_object_name || '.APPLY_UNDO:'
296 || ' Task Assignment ID :' || l_task_assignment_id
297 || 'is no longer assigned to the user. UNDO Failed',
298 g_object_name || '.APPLY_UNDO',FND_LOG.LEVEL_ERROR );
299 x_return_status := FND_API.G_RET_STS_SUCCESS;
300 ELSE
301 --Task Assignment Markdirty
302 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => 'CSM_TASK_ASSIGNMENTS',
303 p_accessid => l_access_id,
304 p_userid => l_user_id,
305 p_dml => asg_download.upd,
306 p_timestamp => sysdate);
307
308 END IF;
309 CSM_UTIL_PKG.REJECT_RECORD
310 ( l_user_name
311 , l_tran_id
312 , l_sequence
313 , l_task_assignment_id
314 , l_ta_obj_name
315 , l_ta_pub_name
316 , l_error_msg
317 , l_process_status
318 );
319 ELSIF l_dml_type = 'I' THEN --insert
320 --Reject the Insert record
321 CSM_UTIL_PKG.REJECT_RECORD
322 ( l_user_name
323 , l_tran_id
324 , l_sequence
325 , l_task_assignment_id
326 , l_ta_obj_name
327 , l_ta_pub_name
328 , l_error_msg
329 , l_process_status
330 );
331 ELSIF l_dml_type IS NULL THEN
332
333 OPEN c_get_ta_from_base (l_task_assignment_id );
334 FETCH c_get_ta_from_base INTO l_task_id;
335 CLOSE c_get_ta_from_base;
336 END IF;
337
338
339 OPEN cur FOR 'select DISTINCT STORE from '||asg_base.G_OLITE_SCHEMA||'.c$inq c_inq
340 WHERE CLID$$CS ='''||l_user_name||''' AND TRANID$$ = '||l_tran_id
341 ||' AND EXISTS(SELECT 1 FROM ASG_PUB_ITEM WHERE ITEM_ID = c_inq.STORE) ' ;
342 LOOP
343 FETCH cur INTO r_pub_item_store;
344 EXIT WHEN cur%NOTFOUND;
345
346 IF r_pub_item_store = l_taa_pub_name AND l_task_assignment_id IS NOT NULL THEN
347 --Task Assignment Audit Markdirty
348 FOR r_get_taa_from_inq IN c_get_taa_from_inq(l_task_assignment_id, l_user_name) LOOP
349 IF r_get_taa_from_inq.ASSIGNMENT_AUDIT_ID IS NOT NULL THEN
350 --Reject the record as it not needed for upload anymore
351 CSM_UTIL_PKG.REJECT_RECORD
352 ( l_user_name
353 , r_get_taa_from_inq.TRANID$$
354 , r_get_taa_from_inq.SEQNO$$
355 , r_get_taa_from_inq.ASSIGNMENT_AUDIT_ID
356 , l_taa_obj_name
357 , l_taa_pub_name
358 , l_error_msg
359 , l_process_status
360 );
361
362 END IF;
363 END LOOP;
364 ELSIF r_pub_item_store = l_task_pub_name AND l_task_id IS NOT NULL THEN
365 l_access_id := NULL;
366 l_tran_id := NULL;
367 l_sequence := NULL;
368 l_source_object_type := NULL;
369 l_dml_type := NULL;
370 --Task Markdirty
371 OPEN c_get_task_from_inq(l_task_id , l_user_name);
372 FETCH c_get_task_from_inq INTO l_tran_id,l_sequence,l_dml_type,l_source_object_type,l_incident_id;
373 CLOSE c_get_task_from_inq;
374 IF l_dml_type = 'U' THEN
375 OPEN c_get_task_from_acc (l_task_id ,l_user_id );
376 FETCH c_get_task_from_acc INTO l_access_id;
377 CLOSE c_get_task_from_acc;
378 --Check if the task assignment has been changed to another user before this UNDO
379 IF l_markdirty_all = 'Y' AND l_access_id IS NOT NULL THEN
380 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_task_pub_name,
381 p_accessid => l_access_id,
382 p_userid => l_user_id,
383 p_dml => asg_download.upd,
384 p_timestamp => sysdate);
385 END IF; --Task Markdirty
386 CSM_UTIL_PKG.REJECT_RECORD
387 ( l_user_name
388 , l_tran_id
389 , l_sequence
390 , l_task_id
391 , l_task_obj_name
392 , l_task_pub_name
393 , l_error_msg
394 , l_process_status
395 );
396 ELSIF l_dml_type = 'I' THEN --insert
397 --Reject the Insert record
398 CSM_UTIL_PKG.REJECT_RECORD
399 ( l_user_name
400 , l_tran_id
401 , l_sequence
402 , l_task_id
403 , l_task_obj_name
404 , l_task_pub_name
405 , l_error_msg
406 , l_process_status
407 );
408 ELSIF l_dml_type IS NULL THEN
409
410 OPEN c_get_task_from_base (l_task_id );
411 FETCH c_get_task_from_base INTO l_source_object_type,l_incident_id;
412 CLOSE c_get_task_from_base;
413 END IF;
414
415 ELSIF r_pub_item_store =l_sr_pub_name AND l_source_object_type = 'SR' AND l_incident_id IS NOT NULL THEN --Process incidents
416 l_access_id := NULL;
417 l_tran_id := NULL;
418 l_sequence := NULL;
419 l_dml_type := NULL;
420 --Incident Markdirty
421 OPEN c_get_incident_from_inq(l_incident_id,l_user_name);
422 FETCH c_get_incident_from_inq INTO l_tran_id,l_sequence,l_dml_type;
423 CLOSE c_get_incident_from_inq;
424 IF l_dml_type = 'U' THEN
425 OPEN c_get_incident_from_acc (l_incident_id ,l_user_id );
426 FETCH c_get_incident_from_acc INTO l_access_id;
427 CLOSE c_get_incident_from_acc;
428
429 IF l_markdirty_all = 'Y' AND l_access_id IS NOT NULL THEN
430 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_sr_pub_name,
431 p_accessid => l_access_id,
432 p_userid => l_user_id,
433 p_dml => asg_download.upd,
434 p_timestamp => sysdate);
435 END IF;
436 --Reject the record as it not needed for upload anymore
437 CSM_UTIL_PKG.REJECT_RECORD
438 ( l_user_name
439 , l_tran_id
440 , l_sequence
441 , l_incident_id
442 , l_sr_obj_name
443 , l_sr_pub_name
444 , l_error_msg
445 , l_process_status
446 );
447 ELSIF l_dml_type = 'I' THEN --insert
448 --Reject the Insert record
449 CSM_UTIL_PKG.REJECT_RECORD
450 ( l_user_name
451 , l_tran_id
452 , l_sequence
453 , l_incident_id
454 , l_sr_obj_name
455 , l_sr_pub_name
456 , l_error_msg
457 , l_process_status
458 );
459
460 END IF;
461 ELSIF r_pub_item_store =l_dbh_pub_name AND l_task_assignment_id IS NOT NULL THEN
462 l_access_id := NULL;
463 l_tran_id := NULL;
464 l_sequence := NULL;
465
466 --Debrief Header Markdirty
467 OPEN c_get_debrief_header(l_task_assignment_id , l_user_id,l_user_name);
468 FETCH c_get_debrief_header INTO l_access_id,l_debrief_header_id,l_tran_id,l_sequence;
469 CLOSE c_get_debrief_header;
470 IF l_debrief_header_id IS NOT NULL THEN
471 IF l_markdirty_all = 'Y' AND l_access_id IS NOT NULL THEN
472 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_dbh_pub_name,
473 p_accessid => l_access_id,
474 p_userid => l_user_id,
475 p_dml => asg_download.upd,
476 p_timestamp => sysdate);
477 END IF;
478 --Reject the record as it not needed for upload anymore
479 CSM_UTIL_PKG.REJECT_RECORD
480 ( l_user_name
481 , l_tran_id
482 , l_sequence
483 , l_debrief_header_id
484 , l_dbh_obj_name
485 , l_dbh_pub_name
486 , l_error_msg
487 , l_process_status
488 );
489
490 END IF;
491 ELSIF r_pub_item_store =l_dble_pub_name AND l_task_assignment_id IS NOT NULL THEN
492 --Debrief Expense Markdirty
493 FOR r_get_debrief_expenses IN c_get_debrief_expenses(l_task_assignment_id, l_user_id,l_user_name) LOOP
494 IF r_get_debrief_expenses.DEBRIEF_LINE_ID IS NOT NULL THEN
495 IF l_markdirty_all = 'Y' AND r_get_debrief_expenses.ACCESS_ID IS NOT NULL THEN
496 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_dble_pub_name,
497 p_accessid => r_get_debrief_expenses.ACCESS_ID,
498 p_userid => l_user_id,
499 p_dml => asg_download.upd,
500 p_timestamp => sysdate);
501 END IF;
502 --Reject the record as it not needed for upload anymore
503 CSM_UTIL_PKG.REJECT_RECORD
504 ( l_user_name
505 , r_get_debrief_expenses.TRANID$$
506 , r_get_debrief_expenses.SEQNO$$
507 , r_get_debrief_expenses.DEBRIEF_LINE_ID
508 , l_dble_obj_name
509 , l_dble_pub_name
510 , l_error_msg
511 , l_process_status
512 );
513
514 END IF;
515 END LOOP;
516 ELSIF r_pub_item_store =l_dbll_pub_name AND l_task_assignment_id IS NOT NULL THEN
517 --Debrief Labor Markdirty
518 FOR r_get_debrief_labor IN c_get_debrief_labor(l_task_assignment_id, l_user_id,l_user_name) LOOP
519 IF r_get_debrief_labor.DEBRIEF_LINE_ID IS NOT NULL THEN
520 IF l_markdirty_all = 'Y' AND r_get_debrief_labor.ACCESS_ID IS NOT NULL THEN
521 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_dbll_pub_name,
522 p_accessid => r_get_debrief_labor.ACCESS_ID,
523 p_userid => l_user_id,
524 p_dml => asg_download.upd,
525 p_timestamp => sysdate);
526 END IF;
527 --Reject the record as it not needed for upload anymore
528 CSM_UTIL_PKG.REJECT_RECORD
529 ( l_user_name
530 , r_get_debrief_labor.TRANID$$
531 , r_get_debrief_labor.SEQNO$$
532 , r_get_debrief_labor.DEBRIEF_LINE_ID
533 , l_dbll_obj_name
534 , l_dbll_pub_name
535 , l_error_msg
536 , l_process_status
537 );
538
539 END IF;
540 END LOOP;
541 ELSIF r_pub_item_store =l_dblp_pub_name AND l_task_assignment_id IS NOT NULL THEN
542 --Debrief Parts Markdirty
543 FOR r_get_debrief_parts IN c_get_debrief_parts(l_task_assignment_id, l_user_id,l_user_name) LOOP
544 IF r_get_debrief_parts.DEBRIEF_LINE_ID IS NOT NULL THEN
545 IF l_markdirty_all = 'Y' AND r_get_debrief_parts.ACCESS_ID IS NOT NULL THEN
546 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_dblp_pub_name,
547 p_accessid => r_get_debrief_parts.ACCESS_ID,
548 p_userid => l_user_id,
549 p_dml => asg_download.upd,
550 p_timestamp => sysdate);
551 END IF;
552 --Reject the record as it not needed for upload anymore
553 CSM_UTIL_PKG.REJECT_RECORD
554 ( l_user_name
555 , r_get_debrief_parts.TRANID$$
556 , r_get_debrief_parts.SEQNO$$
557 , r_get_debrief_parts.DEBRIEF_LINE_ID
558 , l_dblp_obj_name
559 , l_dblp_pub_name
560 , l_error_msg
561 , l_process_status
562 );
563
564 END IF;
565 END LOOP;
566 ELSIF r_pub_item_store =l_reqh_pub_name AND l_task_assignment_id IS NOT NULL THEN
567 --Debrief Requirement Header Markdirty
568 l_access_id := NULL;
569 l_tran_id := NULL;
570 l_sequence := NULL;
571
572 OPEN c_get_req_header(l_task_assignment_id , l_user_id, l_user_name);
573 FETCH c_get_req_header INTO l_access_id,l_req_header_id,l_tran_id,l_sequence;
574 CLOSE c_get_req_header;
575 IF l_req_header_id IS NOT NULL THEN
576 IF l_markdirty_all = 'Y' AND l_access_id IS NOT NULL THEN
577 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_reqh_pub_name,
578 p_accessid => l_access_id,
579 p_userid => l_user_id,
580 p_dml => asg_download.upd,
581 p_timestamp => sysdate);
582 END IF;
583 --Reject the record as it not needed for upload anymore
584 CSM_UTIL_PKG.REJECT_RECORD
585 ( l_user_name
586 , l_tran_id
587 , l_sequence
588 , l_req_header_id
589 , l_reqh_obj_name
590 , l_reqh_pub_name
591 , l_error_msg
592 , l_process_status
593 );
594
595 END IF;
596 ELSIF r_pub_item_store =l_reql_pub_name AND l_req_header_id IS NOT NULL THEN
597 --Debrief Requriement line Markdirty
598 FOR r_get_req_line IN c_get_req_line(l_req_header_id , l_user_id,l_user_name) LOOP
599 IF r_get_req_line.requirement_line_id IS NOT NULL THEN
600 IF l_markdirty_all = 'Y' AND r_get_req_line.ACCESS_ID IS NOT NULL THEN
601 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_reql_pub_name,
602 p_accessid => r_get_req_line.ACCESS_ID,
603 p_userid => l_user_id,
604 p_dml => asg_download.upd,
605 p_timestamp => sysdate);
606 END IF;
607 --Reject the record as it not needed for upload anymore
608 CSM_UTIL_PKG.REJECT_RECORD
609 ( l_user_name
610 , r_get_req_line.TRANID$$
611 , r_get_req_line.SEQNO$$
612 , r_get_req_line.requirement_line_id
613 , l_reql_obj_name
614 , l_reql_pub_name
615 , l_error_msg
616 , l_process_status
617 );
618
619 END IF;
620 END LOOP;
621 ELSIF r_pub_item_store =l_notes_pub_name THEN
622 --Notes Markdirty
623 FOR r_get_notes IN c_get_notes(l_task_id, l_incident_id, l_debrief_header_id, l_user_id,l_user_name) LOOP
624 IF r_get_notes.jtf_note_id IS NOT NULL THEN
625 IF l_markdirty_all = 'Y' AND r_get_notes.access_id IS NOT NULL THEN
626 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_notes_pub_name,
627 p_accessid => r_get_notes.access_id,
628 p_userid => l_user_id,
629 p_dml => asg_download.upd,
630 p_timestamp => sysdate);
631 END IF;
632 --Reject the record as it not needed for upload anymore
633 CSM_UTIL_PKG.REJECT_RECORD
634 ( l_user_name
635 , r_get_notes.TRANID$$
636 , r_get_notes.SEQNO$$
637 , r_get_notes.jtf_note_id
638 , l_notes_obj_name
639 , l_notes_pub_name
640 , l_error_msg
641 , l_process_status
642 );
643
644 END IF;
645 END LOOP;
646 ELSIF r_pub_item_store =l_lobs_pub_name THEN
647 --Notes Markdirty
648 FOR r_get_lobs IN c_get_lobs(l_task_id, l_incident_id, l_user_id,l_user_name) LOOP
649 IF r_get_lobs.file_id IS NOT NULL THEN
650 IF l_markdirty_all = 'Y' AND r_get_lobs.access_id IS NOT NULL THEN
651 l_mark_dirty := asg_Download.mark_dirty (p_pub_item => l_lobs_pub_name,
652 p_accessid => r_get_lobs.access_id,
653 p_userid => l_user_id,
654 p_dml => asg_download.upd,
655 p_timestamp => sysdate);
656 END IF;
657 --Reject the record as it not needed for upload anymore
658 CSM_UTIL_PKG.REJECT_RECORD
659 ( l_user_name
660 , r_get_lobs.TRANID$$
661 , r_get_lobs.SEQNO$$
662 , r_get_lobs.FILE_ID
663 , l_lobs_obj_name
664 , l_lobs_pub_name
665 , l_error_msg
666 , l_process_status
667 );
668
669 END IF;
670 END LOOP;
671
672 END IF;--Pub item
673 END LOOP;
674
675 -- success
676 x_return_status := FND_API.G_RET_STS_SUCCESS;
677
678 EXCEPTION WHEN OTHERS THEN
679 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_UNDO:' ||g_object_name || '.APPLY_UNDO',
680 FND_LOG.LEVEL_EXCEPTION );
681 x_return_status := FND_API.G_RET_STS_ERROR;
682 END APPLY_UNDO;
683
684
685
686 /***
687 This procedure is called by APPLY_CLIENT_CHANGES for every record in in-queue that needs to be processed.
688 ***/
689 PROCEDURE APPLY_RECORD
690 (
691 p_record IN c_client_undo%ROWTYPE,
692 p_error_msg out nocopy VARCHAR2,
693 x_return_status IN out nocopy VARCHAR2
694 ) IS
695 l_rc BOOLEAN;
696 l_access_id NUMBER;
697 BEGIN
698 /*** initialize return status and message list ***/
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700 FND_MSG_PUB.INITIALIZE;
701
702 CSM_UTIL_PKG.LOG('Entering CSM_UNDO_PKG.APPLY_RECORD for PK1 Value ' || p_record.PK1_VALUE ,
703 'CSM_UNDO_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE);
704
705 IF p_record.dmltype$$='I' THEN
706 -- Process insert
707 APPLY_UNDO
708 (
709 p_record,
710 p_error_msg,
711 x_return_status
712 );
713 ELSE --Delete and update is not supported for this PI
714 -- invalid dml type
715 CSM_UTIL_PKG.LOG
716 ( 'Invalid DML type: ' || p_record.dmltype$$ || ' is not supported for this entity'
717 || ' for Undo Request ID ' || p_record.PK1_VALUE ,'CSM_UNDO_PKG.APPLY_RECORD',FND_LOG.LEVEL_ERROR);
718
719 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
720 (
721 p_message => 'CSM_DML_OPERATION'
722 , p_token_name1 => 'DML'
723 , p_token_value1 => p_record.dmltype$$
724 );
725
726 x_return_status := FND_API.G_RET_STS_ERROR;
727 END IF;
728
729 CSM_UTIL_PKG.LOG('Leaving CSM_UNDO_PKG.APPLY_RECORD for Undo Request ID ' || p_record.PK1_VALUE ,
730 'CSM_UNDO_PKG.APPLY_RECORD',FND_LOG.LEVEL_PROCEDURE);
731 EXCEPTION WHEN OTHERS THEN
732 fnd_msg_pub.Add_Exc_Msg( g_object_name, 'APPLY_RECORD', sqlerrm);
733 p_error_msg := CSM_UTIL_PKG.GET_ERROR_MESSAGE_TEXT
734 (
735 p_api_error => TRUE
736 );
737 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_RECORD: ' || sqlerrm
738 || ' for Undo Request ID ' || p_record.PK1_VALUE ,'CSM_UNDO_PKG.APPLY_RECORD',FND_LOG.LEVEL_EXCEPTION);
739
740 x_return_status := FND_API.G_RET_STS_ERROR;
741
742 END APPLY_RECORD;
743
744 /***
745 This procedure is called by CSM_SERVICEP_WRAPPER_PKG when publication item CSM_CLIENT_UNDO_REQUEST
746 is dirty. This happens when a mobile field service device executed DML on an updatable table and did
747 a fast sync. This procedure will insert the data that came from mobile into the backend tables using
748 public APIs.
749 ***/
750 PROCEDURE APPLY_CLIENT_CHANGES
751 (
752 p_user_name IN VARCHAR2,
753 p_tranid IN NUMBER,
754 p_debug_level IN NUMBER,
755 x_return_status IN out nocopy VARCHAR2
756 ) IS
757
758 l_process_status VARCHAR2(1);
759 l_error_msg VARCHAR2(4000);
760
761 BEGIN
762 CSM_UTIL_PKG.LOG('Entering CSM_UNDO_PKG.APPLY_CLIENT_CHANGES ',
763 'CSM_UNDO_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
764 g_debug_level := p_debug_level;
765 x_return_status := FND_API.G_RET_STS_SUCCESS;
766
767 /*** loop through all the records in inqueue ***/
768 FOR r_client_undo_rec IN c_client_undo( p_user_name, p_tranid) LOOP
769 --SAVEPOINT save_rec ;
770 /*** apply record ***/
771 APPLY_RECORD
772 (
773 r_client_undo_rec
774 , l_error_msg
775 , l_process_status
776 );
777
778 /*** was record processed successfully? ***/
779 IF l_process_status = FND_API.G_RET_STS_SUCCESS THEN
780 /*** Yes -> Reject record from inqueue ***/
781 CSM_UTIL_PKG.REJECT_RECORD
782 (
783 p_user_name,
784 p_tranid,
785 r_client_undo_rec.seqno$$,
786 r_client_undo_rec.REQUEST_ID,
787 g_object_name,
788 g_pub_name,
789 l_error_msg,
790 l_process_status
791 );
792 /*** was delete successful? ***/
793 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
794 /*** no -> rollback ***/
795 CSM_UTIL_PKG.LOG
796 ( 'Reject record failed, rolling back to savepoint'
797 || ' for PK ' || r_client_undo_rec.REQUEST_ID ,'CSM_UNDO_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
798 ROLLBACK TO save_rec;
799 x_return_status := FND_API.G_RET_STS_ERROR;
800 END IF;
801 ELSIF l_process_Status <> FND_API.G_RET_STS_SUCCESS THEN
802 /*** Record was not applied successfully -> defer and reject records ***/
803 csm_util_pkg.log( 'Record not processed successfully, deferring and rejecting record'
804 || ' for PK ' || r_client_undo_rec.REQUEST_ID ,'CSM_UNDO_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
805
806 CSM_UTIL_PKG.REJECT_RECORD
807 (
808 p_user_name
809 , p_tranid
810 , r_client_undo_rec.seqno$$
811 , r_client_undo_rec.REQUEST_ID
812 , g_object_name
813 , g_pub_name
814 , l_error_msg
815 , l_process_status
816 );
817
818 /*** Was defer successful? ***/
819 IF l_process_status <> FND_API.G_RET_STS_SUCCESS THEN
820 /*** no -> rollback ***/
821 CSM_UTIL_PKG.LOG
822 ( 'Reject record failed, No rolling back to savepoint'
823 || ' for PK ' || r_client_undo_rec.REQUEST_ID ,'CSM_UNDO_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_ERROR); -- put PK column here
824 ROLLBACK TO save_rec;
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 END IF;
827 END IF;
828
829 END LOOP;
830
831 CSM_UTIL_PKG.LOG('Leaving CSM_UNDO_PKG.APPLY_CLIENT_CHANGES',
832 'CSM_UNDO_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
833
834 EXCEPTION WHEN OTHERS THEN
835 /*** catch and log exceptions ***/
836 CSM_UTIL_PKG.log( 'Exception in ' || g_object_name || '.APPLY_CLIENT_CHANGES: ' || sqlerrm
837 ,'CSM_UNDO_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_EXCEPTION);
838
839 x_return_status := FND_API.G_RET_STS_ERROR;
840
841 END APPLY_CLIENT_CHANGES;
842
843
844 END CSM_UNDO_PKG;