1 PACKAGE BODY CSM_DEFERRED_TXNS_PKG AS
2 /*$Header: csmdftxb.pls 120.3.12020000.2 2012/08/10 09:40:42 pramosin ship $*/
3
4 /*** Globals ***/
5 g_object_name CONSTANT VARCHAR2(30) := 'CSM_DEFERRED_TXNS_PKG';
6 g_pub_name CONSTANT VARCHAR2(30) := 'CSM_DEFERRED_TRANSACTIONS';
7 g_debug_level NUMBER; -- debug level
8
9 PROCEDURE correct_inq_for_reapply(p_tracking_id IN NUMBER,
10 p_tranid IN NUMBER, p_client_id IN VARCHAR2)
11 IS
12 l_qry VARCHAR2(4000);
13 old_tranid NUMBER;
14 old_client_id varchar2(100);
15 old_seq NUMBER;
16 old_dml VARCHAR2(1);
17 inq_table VARCHAR2(100);
18 inq_pk_col VARCHAR2(100);
19 inq_pk_value varchar2(100);
20 l_proceed NUMBER;
21
22 BEGIN
23 SELECT nfn.DEFERRED_TRAN_ID,nfn.CLIENT_ID, nfn.SEQUENCE,nfn.DML,
24 nfn.OBJECT_NAME,pi.PRIMARY_KEY_COLUMN,nfn.OBJECT_ID,instr(pi.PRIMARY_KEY_COLUMN,',')
25 INTO old_tranid, old_client_id,old_seq,old_dml,inq_table,inq_pk_col,inq_pk_value,l_proceed
26 FROM CSM_DEFERRED_NFN_INFO nfn, ASG_PUB_ITEM pi
27 WHERE nfn.tracking_id=p_tracking_id
28 AND nfn.OBJECT_NAME = pi.item_id;
29
30 IF l_proceed > 0 THEN
31 CSM_UTIL_PKG.LOG('Multiple Pks in '||inq_table||' is not supported for correction',
32 'CSM_DEFERRED_TXNS_PKG.correct_inq_for_reapply',FND_LOG.LEVEL_PROCEDURE);
33 RETURN;
34 END IF;
35
36 BEGIN
37
38 l_qry:='SELECT 1 FROM '||inq_table||'_INQ WHERE '||inq_pk_col||'='''||inq_pk_value||''' AND TRANID$$='
39 ||p_tranid||' AND CLID$$CS='''||p_client_id||'''';
40
41 EXECUTE IMMEDIATE l_qry INTO l_proceed;
42
43 l_qry:='DELETE FROM '||inq_table||'_INQ WHERE '||inq_pk_col||'='''||inq_pk_value||''' AND TRANID$$='
44 ||old_tranid||' AND CLID$$CS='''||old_client_id||''' AND SEQNO$$='||old_seq;
45
46 EXECUTE IMMEDIATE l_qry;
47
48 IF(SQL%ROWCOUNT=0) THEN -- Chances are there that MFS admin discarded this record already
49 -- in which case let the so-called corrected record go in the same txn
50 CSM_UTIL_PKG.LOG('Tracking INQ record was lost',
51 'CSM_DEFERRED_TXNS_PKG.correct_inq_for_reapply',FND_LOG.LEVEL_PROCEDURE);
52
53 RETURN;
54 END IF;
55
56 l_qry:= 'UPDATE '||inq_table||'_INQ SET TRANID$$='||old_tranid||',CLID$$CS='''||old_client_id||''' ,'
57 ||' SEQNO$$='||old_seq||', DMLTYPE$$='''||old_dml||''' WHERE '||inq_pk_col||'='''||inq_pk_value||''' AND TRANID$$='
58 ||p_tranid||' AND CLID$$CS='''||p_client_id||'''';
59
60 EXECUTE IMMEDIATE l_qry;
61
62 EXCEPTION
63 WHEN NO_DATA_FOUND THEN
64 CSM_UTIL_PKG.LOG('No data found in '||inq_table||'_INQ'||' with pk:'||inq_pk_value,
65 'CSM_DEFERRED_TXNS_PKG.correct_inq_for_reapply',FND_LOG.LEVEL_EXCEPTION);
66
67 CSM_UTIL_PKG.LOG('Query is: '||l_qry,
68 'CSM_DEFERRED_TXNS_PKG.correct_inq_for_reapply',FND_LOG.LEVEL_EXCEPTION);
69
70 CSM_UTIL_PKG.LOG('If there is no issue with query then a blind Re-apply was initiated from client without a corrected root record.',
71 'CSM_DEFERRED_TXNS_PKG.correct_inq_for_reapply',FND_LOG.LEVEL_PROCEDURE);
72 END;
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN
75 NULL; -- can't happen as we check base table for existence before calling this api.
76 END correct_inq_for_reapply;
77
78
79
80 PROCEDURE APPLY_CLIENT_CHANGES
81 (
82 p_user_name IN VARCHAR2,
83 p_tranid IN NUMBER,
84 p_debug_level IN NUMBER,
85 x_return_status IN OUT NOCOPY VARCHAR2
86 )
87 IS
88 l_exists NUMBER;
89 l_qry VARCHAR2(400);
90
91 /* Select all inq records */
92 CURSOR c_inq_records( b_user_name VARCHAR2, b_tranid NUMBER)
93 IS
94 SELECT *
95 FROM CSM_DEFERRED_TRANSACTIONS_INQ
96 WHERE tranid$$ = b_tranid
97 AND clid$$cs = b_user_name;
98
99 CURSOR c_tracking_tree(b_tracking_id number)
100 IS
101 SELECT TRACKING_ID,CLIENT_ID FROM CSM_DEFERRED_NFN_INFO
102 connect by prior tracking_id=parent_id
103 start with tracking_id=b_tracking_id;
104
105 CURSOR c_reapply_txn(b_tranid NUMBER,b_name VARCHAR2)
106 IS
107 SELECT DISTINCT DEFERRED_TRAN_ID,CLIENT_ID
108 FROM CSM_DEFERRED_TRANSACTIONS_INQ
109 WHERE tranid$$ = b_tranid AND clid$$cs = b_name
110 AND ACTION = 'C';
111
112 TYPE l_reapply_type IS TABLE OF c_reapply_txn%rowtype index by binary_integer;
113 l_reapply_tab l_reapply_type;
114
115 TYPE l_num_tab_type is table of number index by binary_integer;
116 TYPE l_char_tab_type is table of varchar2(100) index by binary_integer;
117
118 l_track_tab l_num_tab_type;
119 l_uname_tab l_char_tab_type;
120
121 l_cnt NUMBER:=0;
122
123 l_rec c_inq_records%rowtype;
124 l_process_status VARCHAR2(1);
125 l_error_msg VARCHAR2(4000);
126 BEGIN
127
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129
130 -- Support for deleting tracking records as required by Laptop team
131 /**BEGIN*/
132 FOR dml_0_rec IN (SELECT TRACKING_ID,SEQNO$$ FROM CSM_DEFERRED_TRANSACTIONS_INQ inq
133 WHERE TRANID$$=p_tranid AND CLID$$CS=p_user_name AND dmltype$$='D')
134 LOOP
135
136 CSM_UTIL_PKG.LOG('Delete DML received for tracking Id: '||dml_0_rec.tracking_id||' from -'||p_user_name ||' in txn-'||p_tranid,
137 'CSM_DEFERRED_TXNS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
138
139 --root is deleted by client so no mark dirty required
140 DELETE FROM CSM_DEFERRED_TRANSACTIONS_ACC
141 WHERE tracking_id = dml_0_rec.tracking_id
142 AND USER_ID=asg_base.get_user_id(p_user_name);
143
144 --delete children
145 FOR rec IN (SELECT tracking_id FROM CSM_DEFERRED_NFN_INFO
146 WHERE PARENT_ID=dml_0_rec.tracking_id AND CLIENT_ID=p_user_name)
147 LOOP
148 CSM_ACC_PKG.Delete_Acc
149 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
150 ,P_ACC_TABLE_NAME => 'CSM_DEFERRED_TRANSACTIONS_ACC'
151 ,P_PK1_NAME => 'TRACKING_ID'
152 ,P_PK1_NUM_VALUE => rec.tracking_id
153 ,P_USER_ID => asg_base.get_user_id(p_user_name)
154 );
155 END LOOP;
156
157 DELETE FROM CSM_DEFERRED_NFN_INFO WHERE dml_0_rec.tracking_id IN (TRACKING_ID,PARENT_ID) AND CLIENT_ID=p_user_name;
158
159 CSM_UTIL_PKG.DELETE_RECORD
160 (
161 p_user_name,
162 p_tranid,
163 dml_0_rec.seqno$$,
164 dml_0_rec.tracking_id,
165 g_object_name,
166 g_pub_name,
167 l_error_msg, --OUT variable
168 l_process_status
169 );
170
171 END LOOP;
172 /**END*/
173
174 -- NO DEFER FOR this PI from CSM
175
176 -- PURGE-1 : CLEAR INQ RECORDS WITH NO ACTION
177 -- PURGE-2 : CLEAR INQ RECORDS WITH NO CORRESPONDING RECORDS IN THE BASE TABLE
178 -- PURGE-3 : CLEAR INQ RECORDS WITH REAPPLY ACTION AND THAT ARE NOT ROOT
179 l_cnt:=0;
180 FOR clear_rec IN (SELECT TRACKING_ID,SEQNO$$ FROM CSM_DEFERRED_TRANSACTIONS_INQ inq
181 WHERE TRANID$$=p_tranid AND CLID$$CS=p_user_name
182 AND
183 (
184 (ACTION IS NULL OR ACTION NOT IN ('C','D'))
185 OR
186 NOT EXISTS(SELECT 1 FROM CSM_DEFERRED_NFN_INFO b
187 WHERE b.tracking_id=inq.tracking_id)
188 OR
189 (
190 ACTION='C'
191 AND EXISTS(SELECT 1 FROM CSM_DEFERRED_NFN_INFO b
192 WHERE b.tracking_id=inq.tracking_id
193 AND PARENT_ID IS NOT NULL) /*REAPPLY supported only at ROOT level*/
194 )
195 ))
196 LOOP
197 CSM_UTIL_PKG.DELETE_RECORD
198 (
199 p_user_name,
200 p_tranid,
201 clear_rec.seqno$$,
202 clear_rec.tracking_id,
203 g_object_name,
204 g_pub_name,
205 l_error_msg, --OUT variable
206 l_process_status
207 );
208
209 BEGIN
210 SELECT 'S' INTO l_process_status
211 FROM CSM_DEFERRED_NFN_INFO
212 WHERE tracking_id=clear_rec.tracking_id;
213 EXCEPTION
214 WHEN NO_DATA_FOUND THEN
215 /*Send a reject row for recs in INQ that don't exist in Base*/
216 l_error_msg:= 'No such record in Base table';
217 asg_defer.reject_row(p_user_name,
218 p_tranid,
219 g_pub_name,
220 clear_rec.seqno$$,
221 l_error_msg, --IN variable of asg api
222 l_process_status);
223 l_cnt:=l_cnt+1;
224 END;
225
226 END LOOP;
227
228 IF(l_cnt>0) THEN
229 CSM_UTIL_PKG.LOG('Purged and rejected '||l_cnt|| ' tracking records from INQ as they are missing in base table for -'||p_user_name ||' in current txn-'||p_tranid,
230 'CSM_DEFERRED_TXNS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
231 END IF;
232
233
234 /***************************************PROCESS INQ RECORDS*********************************************/
235 --'D' for Discard : for a record, the record and all its related entries will discarded
236
237 --'C' for Reapply: the record should be root. Entire txn is reapplied but only this root record and its children
238 -- will be tracked
239
240 /* STORE REAPPLY TXNS - since INQ is cleared before reapply is called*/
241 OPEN c_reapply_txn(p_tranid,p_user_name);
242 FETCH c_reapply_txn BULK COLLECT INTO l_reapply_tab;
243 CLOSE c_reapply_txn;
244
245 FOR def_rec IN c_inq_records(p_user_name,p_tranid)
246 LOOP
247 CSM_UTIL_PKG.LOG('Found '||def_rec.tracking_id||' with action '||def_rec.action||' for -'||p_user_name ||' for txn-'||p_tranid,
248 'CSM_DEFERRED_TXNS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
249
250 /*Take action*/
251 IF def_rec.ACTION = 'D' THEN
252 -- Discard root(need not be main root,can be partial) and all its related entries
253 l_cnt:=0;
254 FOR disc_rec IN (SELECT * FROM CSM_DEFERRED_NFN_INFO
255 connect by prior tracking_id=parent_id
256 start with tracking_id=def_rec.tracking_id)
257 LOOP
258 IF(disc_rec.dml='I') THEN
259 asg_defer.reject_row(disc_rec.client_id,
260 disc_rec.deferred_tran_id,
261 disc_rec.OBJECT_NAME,
262 disc_rec.sequence,
263 disc_rec.error_msg, --IN variable of asg api
264 l_process_status);
265 END IF;
266
267 CSM_UTIL_PKG.LOG('Discarding Tracking Id: '||disc_rec.tracking_id,
268 'CSM_DEFERRED_TXNS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
269
270 asg_defer.discard_transaction(disc_rec.client_id,
271 disc_rec.deferred_tran_id,
272 disc_rec.OBJECT_NAME,
273 disc_rec.sequence,l_process_status,false);
274
275
276 CSM_ACC_PKG.Delete_Acc
277 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
278 ,P_ACC_TABLE_NAME => 'CSM_DEFERRED_TRANSACTIONS_ACC'
279 ,P_PK1_NAME => 'TRACKING_ID'
280 ,P_PK1_NUM_VALUE => disc_rec.tracking_id
281 ,P_USER_ID => asg_base.get_user_id(disc_rec.client_id)
282 );
283
284 l_cnt:=l_cnt+1;
285 l_track_tab(l_cnt):=disc_rec.tracking_id;
286
287 END LOOP;
288
289
290 FORALL I IN 1..l_cnt
291 DELETE FROM CSM_DEFERRED_NFN_INFO WHERE TRACKING_ID = l_track_tab(I);
292
293 ELSE /*IF def_rec.ACTION = 'C' THEN */
294
295 /*correct inq*/
296 correct_inq_for_reapply(def_rec.tracking_id,p_tranid,p_user_name);
297
298 /*delete entire tree to reapply so that new defer reports updated error*/
299 IF(l_track_tab.count >0) THEN
300 l_track_tab.DELETE;
301 END IF;
302 IF(l_uname_tab.count >0) THEN
303 l_uname_tab.DELETE;
304 END IF;
305 OPEN c_tracking_tree(def_rec.tracking_id);
306 FETCH c_tracking_tree BULK COLLECT INTO l_track_tab,l_uname_tab;
307 CLOSE c_tracking_tree;
308
309 FOR I IN 1..l_track_tab.COUNT
310 LOOP
311 CSM_ACC_PKG.Delete_Acc
312 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
313 ,P_ACC_TABLE_NAME => 'CSM_DEFERRED_TRANSACTIONS_ACC'
314 ,P_PK1_NAME => 'TRACKING_ID'
315 ,P_PK1_NUM_VALUE => l_track_tab(I)
316 ,P_USER_ID => asg_base.get_user_id(l_uname_tab(I))
317 );
318
319 DELETE FROM CSM_DEFERRED_NFN_INFO WHERE TRACKING_ID = l_track_tab(I);
320 END LOOP;
321
322 END IF;
323
324 --INQ Record processed successfully.
325 CSM_UTIL_PKG.DELETE_RECORD
326 (
327 p_user_name,
328 p_tranid,
329 def_rec.seqno$$,
330 def_rec.tracking_id,
331 g_object_name,
332 g_pub_name,
333 l_error_msg, --OUT variable
334 l_process_status
335 );
336
337 END LOOP;
338
339
340 --RE-APPLY
341 FOR I IN 1..l_reapply_tab.COUNT
342 LOOP
343 CSM_UTIL_PKG.LOG('Reapplying Transaction:'||l_reapply_tab(I).DEFERRED_TRAN_ID ||' of user:'||l_reapply_tab(I).CLIENT_ID,
344 'CSM_DEFERRED_TXNS_PKG.APPLY_CLIENT_CHANGES',FND_LOG.LEVEL_PROCEDURE);
345 ASG_DEFER.reapply_transaction(l_reapply_tab(I).CLIENT_ID,l_reapply_tab(I).DEFERRED_TRAN_ID,l_process_status,false);
346 END LOOP;
347
348
349 /* not required..done while upload in purge_pub_items
350 l_qry := 'DELETE FROM ' ||asg_base.G_OLITE_SCHEMA ||'.C$INQ '
351 ||' WHERE STORE='''||g_pub_name||''' AND TRANID$$='||p_tranid||' AND CLID$$CS= '''||p_user_name||'''';
352 execute immediate l_qry;
353 */
354
355 EXCEPTION WHEN OTHERS THEN
356 /*** catch and log exceptions ***/
357 CSM_UTIL_PKG.LOG
358 ( 'Exception occurred in ' || g_object_name || '.APPLY_CLIENT_CHANGES:' || ' ' || SQLERRM,
359 g_object_name || '.APPLY_CLIENT_CHANGES',
360 FND_LOG.LEVEL_EXCEPTION);
361 x_return_status := FND_API.G_RET_STS_ERROR;
362
363 END APPLY_CLIENT_CHANGES;
364
365 -- Set transaction status to discarded
366 PROCEDURE discard_transaction(p_user_name IN VARCHAR2,
367 p_tranid IN NUMBER,
368 x_return_status OUT NOCOPY VARCHAR2)
369 IS
370 BEGIN
371 BEGIN
372 /* process ROOT tracking records alone*/
373 FOR root_rec IN (SELECT TRACKING_ID FROM CSM_DEFERRED_NFN_INFO
374 WHERE CLIENT_ID=p_user_name AND DEFERRED_TRAN_ID=p_tranid
375 AND PARENT_ID IS NULL)
376 LOOP
377 FOR disc_rec IN (SELECT * FROM CSM_DEFERRED_NFN_INFO
378 connect by prior tracking_id=parent_id
379 start with tracking_id=root_rec.tracking_id)
380 LOOP
381 IF(disc_rec.dml='I') THEN
382 asg_defer.reject_row(disc_rec.client_id,
383 disc_rec.deferred_tran_id,
384 disc_rec.OBJECT_NAME,
385 disc_rec.sequence,
386 disc_rec.error_msg, --IN variable of asg api
387 x_return_status);
388 END IF;
389
390 CSM_ACC_PKG.Delete_Acc
391 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
392 ,P_ACC_TABLE_NAME => 'CSM_DEFERRED_TRANSACTIONS_ACC'
393 ,P_PK1_NAME => 'TRACKING_ID'
394 ,P_PK1_NUM_VALUE => disc_rec.tracking_id
395 ,P_USER_ID => asg_base.get_user_id(disc_rec.client_id)
396 );
397
398 END LOOP;
399 END LOOP;
400
401 DELETE FROM CSM_DEFERRED_NFN_INFO
402 WHERE CLIENT_ID=p_user_name
403 AND DEFERRED_TRAN_ID=p_tranid;
404
405 END; --processed tracking records
406
407
408 ASG_DEFER.discard_transaction(p_user_name,p_tranid,x_return_status);
409
410 END discard_transaction;
411
412 -- Discards the specified deferred row ONLY
413 -- no related records are discarded
414 PROCEDURE discard_transaction(p_user_name IN VARCHAR2,
415 p_tranid IN NUMBER,
416 p_pubitem IN VARCHAR2,
417 p_sequence IN NUMBER,
418 x_return_status OUT NOCOPY VARCHAR2,
419 p_commit_flag IN BOOLEAN)
420 IS
421
422 TYPE l_num_tab_type is table of number index by binary_integer;
423
424 l_track_tab l_num_tab_type;
425 l_tracking_id NUMBER;
426 l_cnt NUMBER;
427 BEGIN
428
429 BEGIN --process tracking records
430 SELECT TRACKING_ID INTO l_tracking_id
431 FROM CSM_DEFERRED_NFN_INFO
432 WHERE CLIENT_ID=p_user_name
433 AND DEFERRED_TRAN_ID=p_tranid
434 AND SEQUENCE = p_sequence
435 AND OBJECT_NAME = p_pubitem;
436
437 l_cnt:=0;
438 FOR disc_rec IN (SELECT * FROM CSM_DEFERRED_NFN_INFO
439 connect by prior tracking_id=parent_id
440 start with tracking_id=l_tracking_id)
441 LOOP
442 IF(disc_rec.dml='I') THEN
443 asg_defer.reject_row(disc_rec.client_id,
444 disc_rec.deferred_tran_id,
445 disc_rec.OBJECT_NAME,
446 disc_rec.sequence,
447 disc_rec.error_msg, --IN variable of asg api
448 x_return_status);
449 END IF;
450
451 CSM_ACC_PKG.Delete_Acc
452 ( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list('CSM_DEFERRED_TRANSACTIONS')
453 ,P_ACC_TABLE_NAME => 'CSM_DEFERRED_TRANSACTIONS_ACC'
454 ,P_PK1_NAME => 'TRACKING_ID'
455 ,P_PK1_NUM_VALUE => disc_rec.tracking_id
456 ,P_USER_ID => asg_base.get_user_id(disc_rec.client_id)
457 );
458
459 l_cnt:=l_cnt+1;
460 l_track_tab(l_cnt):=disc_rec.tracking_id;
461
462 END LOOP;
463
464 FORALL I IN 1..l_cnt
465 DELETE FROM CSM_DEFERRED_NFN_INFO WHERE TRACKING_ID = l_track_tab(I);
466 EXCEPTION
467 WHEN NO_DATA_FOUND THEN
468 NULL;
469 END; --processed tracking records
470
471 ASG_DEFER.discard_transaction(p_user_name,p_tranid,p_pubitem,p_sequence,x_return_status,p_commit_flag);
472
473 END discard_transaction;
474
475 END CSM_DEFERRED_TXNS_PKG;
[Home] [Help]