[Home] [Help]
PACKAGE BODY: APPS.CSI_TRANSACTIONS_PVT
Source
1 PACKAGE BODY csi_transactions_pvt AS
2 /* $Header: csivtrxb.pls 120.3 2005/12/15 13:41:57 brmanesh noship $ */
3 -- start of comments
4 -- PACKAGE name : csi_transactions_pvt
5 -- purpose :
6 -- history :
7 -- note :
8 -- END of comments
9
10
11 g_pkg_name CONSTANT VARCHAR2(30):= 'csi_transactions_pvt';
12 g_file_name CONSTANT VARCHAR2(12) := 'csivtrxb.pls';
13
14 /* ---------------------------------------------------------------------------------- */
15 /* --- This local procedure is used to validate --- */
16 /* --- the transaction_id passed --- */
17 /* ---------------------------------------------------------------------------------- */
18
19 -- this procudure defines the columns for the dynamic sql.
20 PROCEDURE define_columns(
21 p_txnfind_rec IN csi_datastructures_pub.transaction_header_rec,
22 p_cur_get_transactions IN NUMBER
23 )
24 IS
25 BEGIN
26
27 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 1, p_txnfind_rec.transaction_id);
28 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 2, p_txnfind_rec.transaction_date);
29 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 3, p_txnfind_rec.source_transaction_date);
30 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 4, p_txnfind_rec.transaction_type_id);
34 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 8, p_txnfind_rec.source_header_ref,50);
31 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 5, p_txnfind_rec.source_group_ref_id);
32 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 6, p_txnfind_rec.source_group_ref,50);
33 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 7, p_txnfind_rec.source_header_ref_id );
35 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 9, p_txnfind_rec.source_line_ref_id);
36 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 10, p_txnfind_rec.source_line_ref,50);
37 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 11, p_txnfind_rec.source_dist_ref_id1);
38 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 12, p_txnfind_rec.source_dist_ref_id2);
39 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 13, p_txnfind_rec.inv_material_transaction_id);
40 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 14, p_txnfind_rec.transaction_quantity);
41 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 15, p_txnfind_rec.transaction_uom_code,3);
42 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 16, p_txnfind_rec.transacted_by);
43 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 17, p_txnfind_rec.transaction_status_code,30);
44 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 18, p_txnfind_rec.transaction_action_code,30);
45 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 19, p_txnfind_rec.message_id);
46 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 21, p_txnfind_rec.attribute1,150);
47 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 22, p_txnfind_rec.attribute2,150);
48 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 23, p_txnfind_rec.attribute3,150);
49 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 24, p_txnfind_rec.attribute4,150);
50 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 25, p_txnfind_rec.attribute5,150);
51 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 26, p_txnfind_rec.attribute6,150);
52 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 27, p_txnfind_rec.attribute7,150);
53 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 28, p_txnfind_rec.attribute8,150);
54 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 29, p_txnfind_rec.attribute9,150);
55 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 30, p_txnfind_rec.attribute10,150);
56 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 31, p_txnfind_rec.attribute11,150);
57 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 32, p_txnfind_rec.attribute12,150);
58 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 33, p_txnfind_rec.attribute13,150);
59 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 34, p_txnfind_rec.attribute14,150);
60 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 35, p_txnfind_rec.attribute15,150);
61 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 36, p_txnfind_rec.object_version_number);
62 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 37, p_txnfind_rec.txn_sub_type_id);
63 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 38, p_txnfind_rec.transaction_status_code,30);
64 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 39, p_txnfind_rec.split_reason_code,30);
65 DBMS_SQL.DEFINE_COLUMN(p_cur_get_transactions, 40, p_txnfind_rec.txn_user_id);
66 END define_columns;
67
68 -- This procudure gets column values by the dynamic sql.
69 PROCEDURE get_column_values(
70 p_cur_get_transactions IN NUMBER,
71 x_txnfind_rec OUT NOCOPY csi_datastructures_pub.transaction_header_rec
72 )
73 IS
74 BEGIN
75 -- get all column values for csi_transactions table
76 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 1, x_txnfind_rec.transaction_id);
77 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 2, x_txnfind_rec.transaction_date);
78 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 3, x_txnfind_rec.source_transaction_date);
79 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 4, x_txnfind_rec.transaction_type_id);
80 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 5, x_txnfind_rec.source_group_ref_id);
81 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 6, x_txnfind_rec.source_group_ref);
82 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 7, x_txnfind_rec.source_header_ref_id);
83 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 8, x_txnfind_rec.source_header_ref);
84 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 9, x_txnfind_rec.source_line_ref_id);
85 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 10, x_txnfind_rec.source_line_ref);
86 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 11, x_txnfind_rec.source_dist_ref_id1);
87 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 12, x_txnfind_rec.source_dist_ref_id2);
88 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 13, x_txnfind_rec.inv_material_transaction_id);
89 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 14, x_txnfind_rec.transaction_quantity);
90 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 15, x_txnfind_rec.transaction_uom_code);
91 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 16, x_txnfind_rec.transacted_by);
92 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 17, x_txnfind_rec.transaction_status_code);
93 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 18, x_txnfind_rec.transaction_action_code);
94 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 19, x_txnfind_rec.message_id);
95 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 21, x_txnfind_rec.attribute1);
96 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 22, x_txnfind_rec.attribute2);
97 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 23, x_txnfind_rec.attribute3);
98 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 24, x_txnfind_rec.attribute4);
99 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 25, x_txnfind_rec.attribute5);
100 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 26, x_txnfind_rec.attribute6);
101 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 27, x_txnfind_rec.attribute7);
102 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 28, x_txnfind_rec.attribute8);
103 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 29, x_txnfind_rec.attribute9);
104 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 30, x_txnfind_rec.attribute10);
105 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 31, x_txnfind_rec.attribute11);
109 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 35, x_txnfind_rec.attribute15);
106 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 32, x_txnfind_rec.attribute12);
107 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 33, x_txnfind_rec.attribute13);
108 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 34, x_txnfind_rec.attribute14);
110 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 36, x_txnfind_rec.object_version_number);
111 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 37, x_txnfind_rec.txn_sub_type_id);
112 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 38, x_txnfind_rec.transaction_status_code);
113 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 39, x_txnfind_rec.split_reason_code);
114 DBMS_SQL.COLUMN_VALUE(p_cur_get_transactions, 40, x_txnfind_rec.txn_user_id);
115 END get_column_values;
116
117
118 PROCEDURE gen_transactions_order_cl(
119 p_order_by_rec IN csi_datastructures_pub.transaction_sort_rec,
120 x_order_by_cl OUT NOCOPY VARCHAR2,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2
124 )
125 IS
126 l_order_by_cl VARCHAR2(1000) := NULL;
127 l_util_order_by_tbl util_order_by_tbl_type;
128 l_column VARCHAR2(30) :=fnd_api.g_miss_char;
129 BEGIN
130
131 IF p_order_by_rec.transaction_date = 'Y' THEN
132 l_column:=' transaction_date';
133 ELSIF p_order_by_rec.transaction_type_id = 'Y' THEN
134 l_column:=' transaction_type_id';
135 END IF;
136
137 IF l_column <> fnd_api.g_miss_char THEN
138 x_order_by_cl := ' order by' || l_column;
139 ELSE
140 x_order_by_cl :=l_order_by_cl;
141 END IF;
142
143 END gen_transactions_order_cl;
144
145 -- This procedure bind the variables for the dynamic sql
146 PROCEDURE bind(
147 p_txnfind_rec IN csi_datastructures_pub.transaction_query_rec,
148 p_cur_get_transactions IN NUMBER
149 )
150 IS
151 BEGIN
152 IF( (p_txnfind_rec.transaction_id IS NOT NULL) AND (p_txnfind_rec.transaction_id <> fnd_api.g_miss_num) )
153 THEN
154 dbms_sql.bind_variable(p_cur_get_transactions, 'transaction_id', p_txnfind_rec.transaction_id);
155 END IF;
156
157 IF( (p_txnfind_rec.transaction_type_id IS NOT NULL) AND (p_txnfind_rec.transaction_type_id <> fnd_api.g_miss_num) )
158 THEN
159 dbms_sql.bind_variable(p_cur_get_transactions, ':transaction_type_id', p_txnfind_rec.transaction_type_id);
160 END IF;
161
162 IF( (p_txnfind_rec.txn_sub_type_id IS NOT NULL) AND (p_txnfind_rec.txn_sub_type_id <> fnd_api.g_miss_num) )
163 THEN
164 dbms_sql.bind_variable(p_cur_get_transactions, ':txn_sub_type_id', p_txnfind_rec.txn_sub_type_id);
165 END IF;
166
167 IF( (p_txnfind_rec.source_group_ref_id IS NOT NULL) AND (p_txnfind_rec.source_group_ref_id<> fnd_api.g_miss_num) )
168 THEN
169 dbms_sql.bind_variable(p_cur_get_transactions, ':source_group_ref_id', p_txnfind_rec.source_group_ref_id);
170 END IF;
171
172 IF( (p_txnfind_rec.source_group_ref IS NOT NULL) AND (p_txnfind_rec.source_group_ref <> fnd_api.g_miss_char) )
173 THEN
174 dbms_sql.bind_variable(p_cur_get_transactions, ':source_group_ref', p_txnfind_rec.source_group_ref);
175 END IF;
176
177 IF( (p_txnfind_rec.source_header_ref_id IS NOT NULL) AND (p_txnfind_rec.source_header_ref_id <> fnd_api.g_miss_num) )
178 THEN
179 dbms_sql.bind_variable(p_cur_get_transactions, ':source_header_ref_id', p_txnfind_rec.source_header_ref_id);
180 END IF;
181
182 IF( (p_txnfind_rec.source_header_ref IS NOT NULL) AND (p_txnfind_rec.source_header_ref <> fnd_api.g_miss_char) )
183 THEN
184 dbms_sql.bind_variable(p_cur_get_transactions, ':source_header_ref', p_txnfind_rec.source_header_ref);
185 END IF;
186
187 IF( (p_txnfind_rec.source_line_ref_id IS NOT NULL) AND (p_txnfind_rec.source_line_ref_id <> fnd_api.g_miss_num) )
188 THEN
189 dbms_sql.bind_variable(p_cur_get_transactions, ':source_line_ref_id', p_txnfind_rec.source_line_ref_id);
190 END IF;
191
192 IF( (p_txnfind_rec.source_line_ref IS NOT NULL) AND (p_txnfind_rec.source_line_ref <> fnd_api.g_miss_char) )
193 THEN
194 dbms_sql.bind_variable(p_cur_get_transactions, ':source_line_ref', rtrim(ltrim(p_txnfind_rec.source_line_ref)));
195 END IF;
196
197 IF( (p_txnfind_rec.source_transaction_date IS NOT NULL) AND (p_txnfind_rec.source_transaction_date <> fnd_api.g_miss_date) )
198 THEN
199 dbms_sql.bind_variable(p_cur_get_transactions, ':source_txn_date', p_txnfind_rec.source_transaction_date);
200 END IF;
201
202 IF( (p_txnfind_rec.inv_material_transaction_id IS NOT NULL) AND (p_txnfind_rec.inv_material_transaction_id <> fnd_api.g_miss_num) )
203 THEN
204 dbms_sql.bind_variable(p_cur_get_transactions, ':inv_material_transaction_id', p_txnfind_rec.inv_material_transaction_id);
205 END IF;
206
207 IF( (p_txnfind_rec.message_id IS NOT NULL) AND (p_txnfind_rec.message_id <> fnd_api.g_miss_num) )
208 THEN
209 dbms_sql.bind_variable(p_cur_get_transactions, ':message_id', p_txnfind_rec.message_id);
210 END IF;
211
212 IF( (p_txnfind_rec.instance_id IS NOT NULL) AND (p_txnfind_rec.instance_id <> fnd_api.g_miss_num) )
213 THEN
214 dbms_sql.bind_variable(p_cur_get_transactions, ':instance_id', p_txnfind_rec.instance_id);
215 END IF;
216
217
218 IF( (p_txnfind_rec.transaction_start_date IS NOT NULL) AND (p_txnfind_rec.transaction_start_date <> fnd_api.g_miss_date) )
219 THEN
220 dbms_sql.bind_variable(p_cur_get_transactions, ':transaction_start_date', p_txnfind_rec.transaction_start_date);
221 END IF;
222
223
227 END IF;
224 IF( (p_txnfind_rec.transaction_end_date IS NOT NULL) AND (p_txnfind_rec.transaction_end_date <> fnd_api.g_miss_date) )
225 THEN
226 dbms_sql.bind_variable(p_cur_get_transactions, ':transaction_end_date', p_txnfind_rec.transaction_end_date);
228
229 IF( (p_txnfind_rec.transaction_status_code IS NOT NULL) AND (p_txnfind_rec.transaction_status_code <> fnd_api.g_miss_char) )
230 THEN
231 dbms_sql.bind_variable(p_cur_get_transactions, ':transaction_status_code', p_txnfind_rec.transaction_status_code);
232 END IF;
233
234 END bind;
235
236 /* ---------------------------------------------------------------------------------- */
237 /* --- When instance_id is passed then select from csi_inst_trx_details_v --- */
238 /* --- else from csi_transactions --- */
239 /* ---------------------------------------------------------------------------------- */
240
241 PROCEDURE gen_select(
242 p_txnfind_rec IN csi_datastructures_pub.transaction_query_rec,
243 x_select_cl OUT NOCOPY VARCHAR2
244 )
245 IS
246 l_table_name VARCHAR2(30);
247 BEGIN
248 IF ( (p_txnfind_rec.instance_id IS NOT NULL) AND (p_txnfind_rec.instance_id <> fnd_api.g_miss_num) ) THEN
249 l_table_name:='csi_inst_trx_details_v';
250 ELSE
251 l_table_name:='csi_transactions';
252 END IF;
253
254 x_select_cl := 'SELECT transaction_id,transaction_date, source_transaction_date, '||
255 'transaction_type_id, source_group_ref_id, source_group_ref, source_header_ref_id, '||
256 'source_header_ref, source_line_ref_id, source_line_ref, source_dist_ref_id1, '||
257 'source_dist_ref_id2, inv_material_transaction_id, transaction_quantity, '||
258 'transaction_uom_code, transacted_by, transaction_status_code, '||
259 'transaction_action_code, message_id, context, attribute1, attribute2, attribute3, '||
260 'attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, '||
261 'attribute11, attribute12, attribute13, attribute14, attribute15, '||
262 'object_version_number, txn_sub_type_id, transaction_status_code, split_reason_code, '||
263 'last_updated_by FROM '||l_table_name;
264
265 END gen_select;
266
267
268 PROCEDURE gen_transactions_where(
269 p_txnfind_rec IN csi_datastructures_pub.transaction_query_rec,
270 x_transactions_where OUT NOCOPY VARCHAR2
271 )
272 IS
273 -- cursors to check if wildcard values '%' and '_' have been passed
274 -- as item values
275 CURSOR c_chk_str1(p_rec_item VARCHAR2) IS
276 SELECT instr(p_rec_item, '%', 1, 1)
277 FROM dual;
278 CURSOR c_chk_str2(p_rec_item VARCHAR2) IS
279 SELECT instr(p_rec_item, '_', 1, 1)
280 FROM dual;
281
282 -- return values from cursors
283 str_csr1 NUMBER;
284 str_csr2 NUMBER;
285 i NUMBER ;
286 l_operator VARCHAR2(10);
287
288 BEGIN
289
290
291 IF( (p_txnfind_rec.transaction_id IS NOT NULL) AND (p_txnfind_rec.transaction_id <> fnd_api.g_miss_num) )
292 THEN
293
294 IF(x_transactions_where IS NULL) THEN
295 x_transactions_where := ' WHERE ';
296 ELSE
297 x_transactions_where := x_transactions_where || ' AND ';
298 END IF;
299 x_transactions_where := x_transactions_where || 'transaction_id = :transaction_id';
300 END IF;
301
302 IF( (p_txnfind_rec.transaction_type_id IS NOT NULL) AND (p_txnfind_rec.transaction_type_id <> fnd_api.g_miss_num) )
303 THEN
304
305 IF(x_transactions_where IS NULL) THEN
306 x_transactions_where := ' WHERE ';
307 ELSE
308 x_transactions_where := x_transactions_where || ' AND ';
309 END IF;
310 x_transactions_where := x_transactions_where || 'transaction_type_id = :transaction_type_id';
311 END IF;
312
313 IF( (p_txnfind_rec.txn_sub_type_id IS NOT NULL) AND (p_txnfind_rec.txn_sub_type_id <> fnd_api.g_miss_num) )
314 THEN
315
316 IF(x_transactions_where IS NULL) THEN
317 x_transactions_where := ' WHERE ';
318 ELSE
319 x_transactions_where := x_transactions_where || ' AND ';
320 END IF;
321 x_transactions_where := x_transactions_where || 'txn_sub_type_id = :txn_sub_type_id';
322 END IF;
323
324 IF( (p_txnfind_rec.source_group_ref_id IS NOT NULL) AND (p_txnfind_rec.source_group_ref_id <> fnd_api.g_miss_num) )
325 THEN
326
327
328 IF(x_transactions_where IS NULL) THEN
329 x_transactions_where := ' WHERE ';
330 ELSE
331 x_transactions_where := x_transactions_where || ' AND ';
332 END IF;
333 x_transactions_where := x_transactions_where || 'source_group_ref_id = :source_group_ref_id';
334 END IF;
335
336 IF( (p_txnfind_rec.source_group_ref IS NOT NULL) AND (p_txnfind_rec.source_group_ref <> fnd_api.g_miss_char) )
337 THEN
338
339 i:=0;
340 -- check if item value contains '%' wildcard
341 OPEN c_chk_str1(p_txnfind_rec.source_group_ref);
342 FETCH c_chk_str1 INTO str_csr1;
343 CLOSE c_chk_str1;
344
345 IF(str_csr1 <> 0) THEN
346
347 l_operator := ' LIKE ';
348 i:=1;
349 ELSE
350
351 l_operator := ' = ';
352 END IF;
353 IF i=0 THEN
354 -- check if item value contains '_' wildcard
355 OPEN c_chk_str2(p_txnfind_rec.source_group_ref);
356 FETCH c_chk_str2 INTO str_csr2;
357 CLOSE c_chk_str2;
358
362 ELSE
359 IF(str_csr2 <> 0) THEN
360
361 l_operator := ' LIKE ';
363
364 l_operator := ' = ';
365 END IF;
366 END IF;
367 IF(x_transactions_where IS NULL) THEN
368 x_transactions_where := ' WHERE ';
369 ELSE
370 x_transactions_where := x_transactions_where || ' AND ';
371 END IF;
372 x_transactions_where := x_transactions_where || 'source_group_ref ' || l_operator || ' :source_group_ref';
373 END IF;
374
375
376 IF( (p_txnfind_rec.source_header_ref_id IS NOT NULL) AND (p_txnfind_rec.source_header_ref_id <> fnd_api.g_miss_num) )
377 THEN
378
379
380 IF(x_transactions_where IS NULL) THEN
381 x_transactions_where := ' WHERE ';
382 ELSE
383 x_transactions_where := x_transactions_where || ' AND ';
384 END IF;
385 x_transactions_where := x_transactions_where || 'source_header_ref_id = :source_header_ref_id';
386 END IF;
387
388 IF( (p_txnfind_rec.source_header_ref IS NOT NULL) AND (p_txnfind_rec.source_header_ref <> fnd_api.g_miss_char) )
389 THEN
390 i:=0;
391 -- check if item value contains '%' wildcard
392 OPEN c_chk_str1(p_txnfind_rec.source_header_ref);
393 FETCH c_chk_str1 INTO str_csr1;
394 CLOSE c_chk_str1;
395
396 IF(str_csr1 <> 0) THEN
397 l_operator := ' LIKE ';
398 i:=1;
399 ELSE
400 l_operator := ' = ';
401 END IF;
402 IF i=0 THEN
403 -- check if item value contains '_' wildcard
404 OPEN c_chk_str2(p_txnfind_rec.source_header_ref);
405 FETCH c_chk_str2 INTO str_csr2;
406 CLOSE c_chk_str2;
407
408 IF(str_csr2 <> 0) THEN
409 l_operator := ' LIKE ';
410 ELSE
411 l_operator := ' = ';
412 END IF;
413 END IF;
414 IF(x_transactions_where IS NULL) THEN
415 x_transactions_where := ' WHERE ';
416 ELSE
417 x_transactions_where := x_transactions_where || ' AND ';
418 END IF;
419 x_transactions_where := x_transactions_where || 'source_header_ref ' || l_operator || ' :source_header_ref';
420 END IF;
421
422
423
424 IF( (p_txnfind_rec.source_line_ref_id IS NOT NULL) AND (p_txnfind_rec.source_line_ref_id <> fnd_api.g_miss_num) )
425 THEN
426
427 IF(x_transactions_where IS NULL) THEN
428 x_transactions_where := ' WHERE ';
429 ELSE
430 x_transactions_where := x_transactions_where || ' AND ';
431 END IF;
432 x_transactions_where := x_transactions_where || 'source_line_ref_id = :source_line_ref_id';
433 END IF;
434
435 IF( (p_txnfind_rec.source_line_ref IS NOT NULL) AND (p_txnfind_rec.source_line_ref <> fnd_api.g_miss_char) )
436 THEN
437 i:=0;
438 -- check if item value contains '%' wildcard
439 OPEN c_chk_str1(p_txnfind_rec.source_line_ref);
440 FETCH c_chk_str1 INTO str_csr1;
441 CLOSE c_chk_str1;
442
443 IF(str_csr1 <> 0) THEN
444 l_operator := ' LIKE ';
445 i:=1;
446 ELSE
447 l_operator := ' = ';
448 END IF;
449 IF i=0 THEN
450 -- check if item value contains '_' wildcard
451 OPEN c_chk_str2(p_txnfind_rec.source_line_ref);
452 FETCH c_chk_str2 INTO str_csr2;
453 CLOSE c_chk_str2;
454
455 IF(str_csr2 <> 0) THEN
456 l_operator := ' LIKE ';
457 ELSE
458 l_operator := ' = ';
459 END IF;
460 END IF;
461 IF(x_transactions_where IS NULL) THEN
462 x_transactions_where := ' WHERE ';
463 ELSE
464 x_transactions_where := x_transactions_where || ' AND ';
465 END IF;
466 x_transactions_where := x_transactions_where || 'source_line_ref ' || l_operator || ' :source_line_ref';
467 END IF;
468
469 IF( (p_txnfind_rec.source_transaction_date IS NOT NULL) AND (p_txnfind_rec.source_transaction_date <> fnd_api.g_miss_date) )
470 THEN
471 i:=0;
472 -- check if item value contains '%' wildcard
473 OPEN c_chk_str1(p_txnfind_rec.source_transaction_date);
474 FETCH c_chk_str1 INTO str_csr1;
475 CLOSE c_chk_str1;
476
477 IF(str_csr1 <> 0) THEN
478 l_operator := ' LIKE ';
479 i:=1;
480 ELSE
481 l_operator := ' = ';
482 END IF;
483
484 -- check if item value contains '_' wildcard
485 OPEN c_chk_str2(p_txnfind_rec.source_transaction_date);
486 FETCH c_chk_str2 INTO str_csr2;
487 CLOSE c_chk_str2;
488 IF i=0 THEN
489 IF(str_csr2 <> 0) THEN
490 l_operator := ' LIKE ';
491 ELSE
492 l_operator := ' = ';
493 END IF;
494 END IF;
495 IF(x_transactions_where IS NULL) THEN
496 x_transactions_where := ' WHERE ';
497 ELSE
498 x_transactions_where := x_transactions_where || ' AND ';
499 END IF;
500 -- bug 4349049 x_transactions_where := x_transactions_where || 'trunc(source_transaction_date) ' || l_operator || ' trunc(:source_txn_date)';
501 IF l_operator = ' LIKE ' THEN
505 END IF;
502 x_transactions_where := x_transactions_where || 'trunc(source_transaction_date) ' || l_operator || ' trunc(:source_txn_date) ';
503 ELSE
504 x_transactions_where := x_transactions_where || 'source_transaction_date between :source_txn_date and :source_txn_date+1 ';
506 END IF;
507
508
509 IF( (p_txnfind_rec.inv_material_transaction_id IS NOT NULL) AND (p_txnfind_rec.inv_material_transaction_id <> fnd_api.g_miss_num) )
510 THEN
511
512 IF(x_transactions_where IS NULL) THEN
513 x_transactions_where := ' WHERE ';
514 ELSE
515 x_transactions_where := x_transactions_where || ' AND ';
516 END IF;
517 x_transactions_where := x_transactions_where || 'inv_material_transaction_id = :inv_material_transaction_id';
518 END IF;
519
520 IF( (p_txnfind_rec.message_id IS NOT NULL) AND (p_txnfind_rec.message_id <> fnd_api.g_miss_num) )
521 THEN
522
523 IF(x_transactions_where IS NULL) THEN
524 x_transactions_where := ' WHERE ';
525 ELSE
526 x_transactions_where := x_transactions_where || ' AND ';
527 END IF;
528 x_transactions_where := x_transactions_where || 'message_id = :message_id';
529 END IF;
530
531
532 IF( (p_txnfind_rec.transaction_start_date IS NOT NULL) AND (p_txnfind_rec.transaction_start_date <> fnd_api.g_miss_date) )
533 THEN
534 IF ( (p_txnfind_rec.transaction_end_date IS NOT NULL) AND (p_txnfind_rec.transaction_end_date <> fnd_api.g_miss_date) )
535 THEN
536 i:=0;
537 IF(x_transactions_where IS NULL) THEN
538 x_transactions_where := ' WHERE ';
539 ELSE
540 x_transactions_where := x_transactions_where || ' AND ';
541 END IF;
542 x_transactions_where := x_transactions_where || ' transaction_date between :transaction_start_date AND :transaction_end_date ';
543 ELSE
544 IF(x_transactions_where IS NULL) THEN
545 x_transactions_where := ' WHERE ';
546 ELSE
547 x_transactions_where := x_transactions_where || ' AND ';
548 END IF;
549 -- bug 4349049 x_transactions_where := x_transactions_where || 'trunc(transaction_date) ' || l_operator || ' trunc(:transaction_start_date)';
550 x_transactions_where := x_transactions_where || 'transaction_date between :transaction_start_date and :transaction_start_date+1 ';
551 END IF;
552 END IF;
553
554 IF( (p_txnfind_rec.instance_id IS NOT NULL) AND (p_txnfind_rec.instance_id <> fnd_api.g_miss_num) )
555 THEN
556
557 IF(x_transactions_where IS NULL) THEN
558 x_transactions_where := ' WHERE ';
559 ELSE
560 x_transactions_where := x_transactions_where || ' AND ';
561 END IF;
562 x_transactions_where := x_transactions_where || 'instance_id = :instance_id';
563 END IF;
564
565 IF( (p_txnfind_rec.transaction_status_code IS NOT NULL) AND (p_txnfind_rec.transaction_status_code <> fnd_api.g_miss_char) )
566 THEN
567 i:=0;
568 -- check if item value contains '%' wildcard
569 OPEN c_chk_str1(p_txnfind_rec.transaction_status_code);
570 FETCH c_chk_str1 INTO str_csr1;
571 CLOSE c_chk_str1;
572
573 IF(str_csr1 <> 0) THEN
574 l_operator := ' LIKE ';
575 i:=1;
576 ELSE
577 l_operator := ' = ';
578 END IF;
579 IF i=0 THEN
580 -- check if item value contains '_' wildcard
581 OPEN c_chk_str2(p_txnfind_rec.transaction_status_code);
582 FETCH c_chk_str2 INTO str_csr2;
583 CLOSE c_chk_str2;
584
585 IF(str_csr2 <> 0) THEN
586 l_operator := ' LIKE ';
587 ELSE
588 l_operator := ' = ';
589 END IF;
590 END IF;
591 IF(x_transactions_where IS NULL) THEN
592 x_transactions_where := ' WHERE ';
593 ELSE
594 x_transactions_where := x_transactions_where || ' AND ';
595 END IF;
596 x_transactions_where := x_transactions_where || 'transaction_status_code ' || l_operator || ' :transaction_status_code';
597 END IF;
598 END gen_transactions_where;
599
600 PROCEDURE get_transactions(
601 p_api_version_number IN NUMBER,
602 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
603 p_commit IN VARCHAR2 := fnd_api.g_false,
604 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
605 p_txnfind_rec IN csi_datastructures_pub.transaction_query_rec ,
606 p_rec_requested IN NUMBER := g_default_num_rec_fetch,
607 p_start_rec_prt IN NUMBER := 1,
608 p_return_tot_count IN VARCHAR2 := fnd_api.g_false,
609 p_order_by_rec IN csi_datastructures_pub.transaction_sort_rec,
610 x_return_status OUT NOCOPY VARCHAR2,
611 x_msg_count OUT NOCOPY NUMBER,
612 x_msg_data OUT NOCOPY VARCHAR2,
613 x_transaction_tbl OUT NOCOPY csi_datastructures_pub.transaction_header_tbl,
614 x_returned_rec_count OUT NOCOPY NUMBER,
615 x_next_rec_ptr OUT NOCOPY NUMBER,
616 x_tot_rec_count OUT NOCOPY NUMBER
617 )
618
619 IS
620 l_api_name CONSTANT VARCHAR2(30) := 'get_transactions';
621 l_api_version_number CONSTANT NUMBER := 1.0;
625 -- total number of records accessable by caller
622 l_returned_rec_count NUMBER := 0; -- number of records returned in x_txnfind_rec
623 l_next_record_ptr NUMBER := 1;
624 l_ignore NUMBER;
626 l_tot_rec_count NUMBER := 0;
627 l_tot_rec_amount NUMBER := 0;
628 -- status local variables
629 l_return_status VARCHAR2(1); -- return value from procedures
630 l_return_status_full VARCHAR2(1); -- calculated return status from
631 -- dynamic sql statement elements
632 l_cur_get_transactions NUMBER;
633 l_select_cl VARCHAR2(2000) := '';
634 l_order_by_cl VARCHAR2(2000);
635 l_transactions_where VARCHAR2(2000) := '';
636 -- local scratch record
637 l_transaction_rec csi_datastructures_pub.transaction_query_rec;
638 l_crit_transaction_rec csi_datastructures_pub.transaction_query_rec :=p_txnfind_rec;
639
640 l_txn_rec csi_datastructures_pub.transaction_header_rec;
641 l_def_transaction_rec csi_datastructures_pub.transaction_header_rec;
642 l_debug_level NUMBER;
643
644
645 BEGIN
646
647 -- standard start of api savepoint
648 IF fnd_api.to_boolean(p_commit)
649 THEN
650 SAVEPOINT get_transactions_pvt;
651 END IF;
652
653 -- standard call to check for call compatibility.
654 IF NOT fnd_api.compatible_api_call ( l_api_version_number,
655 p_api_version_number,
656 l_api_name,
657 g_pkg_name)
658 THEN
659 RAISE fnd_api.g_exc_unexpected_error;
660 END IF;
661
662
663 -- initialize message list if p_init_msg_list is set to true.
664 IF fnd_api.to_boolean( p_init_msg_list )
665 THEN
666 fnd_msg_pub.initialize;
667 END IF;
668
669
670 -- initialize api return status to success
671 x_return_status := fnd_api.g_ret_sts_success;
672
673 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
674 IF (l_debug_level > 0) THEN
675 CSI_gen_utility_pvt.put_line( 'get_transactions');
676 END IF;
677
678 IF (l_debug_level > 1) THEN
679
680 CSI_gen_utility_pvt.put_line(
681 p_api_version_number ||'-'||
682 p_init_msg_list ||'-'||
683 p_commit ||'-'||
684 p_validation_level ||'-'||
685 p_rec_requested ||'-'||
686 p_start_rec_prt ||'-'||
687 p_return_tot_count
688 );
689
690 csi_gen_utility_pvt.dump_txn_query_rec(p_txnfind_rec);
691 csi_gen_utility_pvt.dump_txn_sort_rec(p_order_by_rec);
692
693
694 END IF;
695
696
697
698 --
699 -- api BODY
700 --
701 -- *************************************************
702 -- generate dynamic sql based on criteria passed in.
703
704 IF( ((p_txnfind_rec.transaction_id IS NULL) OR (p_txnfind_rec.transaction_id = fnd_api.g_miss_num))
705 AND ((p_txnfind_rec.transaction_type_id IS NULL) OR (p_txnfind_rec.transaction_type_id = fnd_api.g_miss_num))
706 AND ((p_txnfind_rec.txn_sub_type_id IS NULL) OR (p_txnfind_rec.txn_sub_type_id = fnd_api.g_miss_num))
707 AND ((p_txnfind_rec.source_group_ref_id IS NULL) OR (p_txnfind_rec.source_group_ref_id = fnd_api.g_miss_num))
708 AND ((p_txnfind_rec.source_group_ref IS NULL) OR (p_txnfind_rec.source_group_ref = fnd_api.g_miss_char))
709 AND ((p_txnfind_rec.source_header_ref_id IS NULL) OR (p_txnfind_rec.source_header_ref_id = fnd_api.g_miss_num))
710 AND ((p_txnfind_rec.source_header_ref IS NULL) OR (p_txnfind_rec.source_header_ref = fnd_api.g_miss_char))
711 AND ((p_txnfind_rec.source_line_ref_id IS NULL) OR (p_txnfind_rec.source_line_ref_id = fnd_api.g_miss_num))
712 AND ((p_txnfind_rec.source_line_ref IS NULL) OR (p_txnfind_rec.source_line_ref = fnd_api.g_miss_char))
713 AND ((p_txnfind_rec.source_transaction_date IS NULL) OR (p_txnfind_rec.source_transaction_date = fnd_api.g_miss_date))
714 AND ((p_txnfind_rec.inv_material_transaction_id IS NULL) OR (p_txnfind_rec.inv_material_transaction_id = fnd_api.g_miss_num))
715 AND ((p_txnfind_rec.message_id IS NULL) OR (p_txnfind_rec.message_id = fnd_api.g_miss_num))
716 AND ((p_txnfind_rec.instance_id IS NULL) OR (p_txnfind_rec.instance_id = fnd_api.g_miss_num))
717 AND ((p_txnfind_rec.transaction_start_date IS NULL) OR (p_txnfind_rec.transaction_start_date = fnd_api.g_miss_date))
718 AND ((p_txnfind_rec.transaction_end_date IS NULL) OR (p_txnfind_rec.transaction_end_date = fnd_api.g_miss_date))
719 AND ((p_txnfind_rec.transaction_status_code IS NULL) OR (p_txnfind_rec.transaction_status_code = fnd_api.g_miss_char))
720
721 ) THEN
722 fnd_message.set_name('CSI', 'CSI_INVALID_PARAMETERS');
723 fnd_msg_pub.add;
724 x_return_status := fnd_api.g_ret_sts_error;
725 RAISE fnd_api.g_exc_error;
726 END IF;
727 x_tot_rec_count:=l_tot_rec_count;
728 x_returned_rec_count:=l_returned_rec_count;
729 gen_select(l_crit_transaction_rec,l_select_cl);
730 gen_transactions_where(l_crit_transaction_rec, l_transactions_where);
731 gen_transactions_order_cl(p_order_by_rec, l_order_by_cl, l_return_status, x_msg_count, x_msg_data);
732
733 IF dbms_sql.is_open(l_cur_get_transactions) THEN
734 dbms_sql.close_cursor(l_cur_get_transactions);
735 END IF;
739 bind(l_crit_transaction_rec, l_cur_get_transactions);
736
737 l_cur_get_transactions := dbms_sql.open_cursor;
738 dbms_sql.parse(l_cur_get_transactions, l_select_cl|| l_transactions_where || l_order_by_cl , dbms_sql.native);
740 define_columns(l_def_transaction_rec, l_cur_get_transactions);
741 l_ignore := dbms_sql.execute(l_cur_get_transactions);
742
743 LOOP
744
745 IF((dbms_sql.fetch_rows(l_cur_get_transactions)>0) AND ((p_return_tot_count = fnd_api.g_true)
746 OR (l_returned_rec_count<p_rec_requested) OR (p_rec_requested=fnd_api.g_miss_num)))
747 THEN
748 get_column_values(l_cur_get_transactions, l_txn_rec);
749 l_tot_rec_count := l_tot_rec_count + 1;
750 x_tot_rec_count := l_tot_rec_count;
751 IF(l_returned_rec_count < p_rec_requested) AND (l_tot_rec_count >= p_start_rec_prt) THEN
752 l_returned_rec_count := l_returned_rec_count + 1;
753 x_returned_rec_count := l_returned_rec_count;
754
755 -- defaulting the transaction user name added by brmanesh
756 BEGIN
757
758 SELECT user_name
759 INTO l_txn_rec.txn_user_name
760 FROM fnd_user
761 WHERE user_id = l_txn_rec.txn_user_id;
762
763 EXCEPTION
764 WHEN others THEN
765 l_txn_rec.txn_user_name := NULL;
766 END;
767
768 -- resolved the foreign key columns: Bug# 2136312 - 12/18/01 rtalluri
769 BEGIN
770
771 SELECT source_txn_type_name
772 INTO l_txn_rec.transaction_type_name
773 FROM csi_txn_types
774 WHERE transaction_type_id = l_txn_rec.transaction_type_id;
775
776 EXCEPTION
777 WHEN others THEN
778 l_txn_rec.transaction_type_name := NULL;
779 END;
780
781 BEGIN
782
783 SELECT name
784 INTO l_txn_rec.txn_sub_type_name
785 FROM csi_txn_sub_types
786 WHERE transaction_type_id = l_txn_rec.transaction_type_id
787 AND sub_type_id = l_txn_rec.txn_sub_type_id;
788
789 EXCEPTION
790 WHEN others THEN
791 l_txn_rec.txn_sub_type_name := NULL;
792 END;
793
794 BEGIN
795
796 SELECT application_name
797 INTO l_txn_rec.source_application_name
798 FROM fnd_application_vl
799 WHERE application_id IN (SELECT source_application_id
800 FROM csi_txn_types
801 WHERE transaction_type_id = l_txn_rec.transaction_type_id);
802
803 EXCEPTION
804 WHEN others THEN
805 l_txn_rec.source_application_name := NULL;
806 END;
807
808 BEGIN
809 SELECT meaning
810 INTO l_txn_rec.transaction_status_name
811 FROM csi_lookups
812 WHERE lookup_code = l_txn_rec.transaction_status_code
813 AND lookup_type = 'CSI_TRANSACTION_STATUS_CODE';
814 EXCEPTION
815 WHEN others THEN
816 l_txn_rec.transaction_status_name := NULL;
817 END;
818
819 x_transaction_tbl(l_returned_rec_count) :=l_txn_rec;
820 END IF;
821 ELSE
822 EXIT;
823 END IF;
824 END LOOP;
825 --
826 -- end of api body
827 --
828 dbms_sql.close_cursor(l_cur_get_transactions);
829
830 -- standard call to get message count and if count is 1, get message info.
831 fnd_msg_pub.count_and_get
832 ( p_count => x_msg_count,
833 p_data => x_msg_data
834 );
835 EXCEPTION
836 WHEN fnd_api.g_exc_error THEN
837 IF fnd_api.to_boolean(p_commit)
838 THEN
839 ROLLBACK TO get_transactions_pvt;
840 END IF;
841 x_return_status := fnd_api.g_ret_sts_error ;
842 fnd_msg_pub.count_and_get
843 (p_count => x_msg_count ,
844 p_data => x_msg_data
845 );
846
847 WHEN fnd_api.g_exc_unexpected_error THEN
848 IF fnd_api.to_boolean(p_commit)
849 THEN
850 ROLLBACK TO get_transactions_pvt;
851 END IF;
852 x_return_status := fnd_api.g_ret_sts_unexp_error ;
853 fnd_msg_pub.count_and_get
854 (p_count => x_msg_count ,
855 p_data => x_msg_data
856 );
857
858 WHEN OTHERS THEN
859 IF fnd_api.to_boolean(p_commit)
860 THEN
861 ROLLBACK TO get_transactions_pvt;
862 END IF;
863 x_return_status := fnd_api.g_ret_sts_unexp_error ;
864 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
865 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
866 END IF;
867 fnd_msg_pub.count_and_get
868 ( p_count => x_msg_count ,
872
869 p_data => x_msg_data
870 );
871 END get_transactions;
873 /* ---------------------------------------------------------------------------------- */
874 /* --- This local procedure is used to validate --- */
875 /* --- the transaction_id passed --- */
876 /* ---------------------------------------------------------------------------------- */
877
878 PROCEDURE validate_transaction_id (
879 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
880 p_validation_mode IN VARCHAR2,
881 p_transaction_id IN NUMBER,
882 x_return_status OUT NOCOPY VARCHAR2,
883 x_msg_count OUT NOCOPY NUMBER,
884 x_msg_data OUT NOCOPY VARCHAR2
885 );
886
887 /* ---------------------------------------------------------------------------------- */
888 /* --- This local procedure is used to validate --- */
889 /* --- the transaction_date passed --- */
890 /* ---------------------------------------------------------------------------------- */
891
892
893
894 PROCEDURE validate_s_transaction_date (
895 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
896 p_source_transaction_date IN DATE,
897 x_return_status OUT NOCOPY VARCHAR2,
898 x_msg_count OUT NOCOPY NUMBER,
899 x_msg_data OUT NOCOPY VARCHAR2
900 );
901
902 /* ---------------------------------------------------------------------------------- */
903 /* --- This local procedure is used to validate --- */
904 /* --- the transaction_type_id passed --- */
905 /* ---------------------------------------------------------------------------------- */
906
907 PROCEDURE validate_transaction_type_id (
908 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
909 p_transaction_type_id IN NUMBER,
910 x_return_status OUT NOCOPY VARCHAR2,
911 x_msg_count OUT NOCOPY NUMBER,
912 x_msg_data OUT NOCOPY VARCHAR2
913 );
914
915 /* --------------------------------------------------------------------------------- */
916 /* --- This local procedure is used to validate --- */
917 /* --- the object_version_number passed --- */
918 /* --------------------------------------------------------------------------------- */
919
920 PROCEDURE validate_object_version_num (
921 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
922 p_validation_mode IN VARCHAR2,
923 p_object_version_number IN NUMBER,
924 x_return_status OUT NOCOPY VARCHAR2,
925 x_msg_count OUT NOCOPY NUMBER,
926 x_msg_data OUT NOCOPY VARCHAR2
927 );
928
929 /* ---------------------------------------------------------------------------------- */
930 /* --- This local procedure is used to validate --- */
931 /* --- the transaction_type_id passed --- */
932 /* ---------------------------------------------------------------------------------- */
933
934 PROCEDURE validate_source_object (
935 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
936 p_transaction_type_id IN NUMBER,
937 p_source_line_ref_id IN NUMBER,
938 x_return_status OUT NOCOPY VARCHAR2,
939 x_msg_count OUT NOCOPY NUMBER,
940 x_msg_data OUT NOCOPY VARCHAR2
941 );
942
943 /* ---------------------------------------------------------------------------------- */
944 /* --- This local procedure is used to validate --- */
945 /* --- the split_reason_code passed --- */
946 /* ---------------------------------------------------------------------------------- */
947
948
949 PROCEDURE validate_split_code (
950 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
951 p_split_reason_code IN VARCHAR2,
952 x_return_status OUT NOCOPY VARCHAR2,
953 x_msg_count OUT NOCOPY NUMBER ,
954 x_msg_data OUT NOCOPY VARCHAR2
955 );
956
957
958 /* ---------------------------------------------------------------------------------- */
959 /* --- This local procedure is used to validate --- */
960 /* --- the transactions for the parameters passed --- */
961 /* ---------------------------------------------------------------------------------- */
962
963
964 PROCEDURE validate_transactions(
965 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
966 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
967 p_validation_mode IN VARCHAR2,
968 p_transaction_rec IN csi_datastructures_pub.transaction_rec,
969 x_return_status OUT NOCOPY VARCHAR2,
970 x_msg_count OUT NOCOPY NUMBER,
971 x_msg_data OUT NOCOPY VARCHAR2
972 );
973
974 /* ---------------------------------------------------------------------------------- */
975 /* --- This procedure is used to create transactions --- */
976 /* --- and call table handler after performing all the validations --- */
977 /* ---------------------------------------------------------------------------------- */
978
979
980 PROCEDURE create_transaction(
984 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
981 p_api_version IN NUMBER,
982 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
983 p_commit IN VARCHAR2 := fnd_api.g_false,
985 p_success_if_exists_flag IN VARCHAR2 := 'N',
986 p_transaction_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec ,
987 x_return_status OUT NOCOPY VARCHAR2,
988 x_msg_count OUT NOCOPY NUMBER,
989 x_msg_data OUT NOCOPY VARCHAR2
990 )
991
992 IS
993 l_api_name CONSTANT VARCHAR2(30) := 'create_transaction';
994 l_api_version CONSTANT NUMBER := 1.0;
995 l_transaction_date DATE; -- local variable for sysdate
996 l_return_status_full VARCHAR2(1);
997 l_access_flag VARCHAR2(1);
998 l_dummy VARCHAR2(1);
999 l_create_flag VARCHAR2(1):='Y';
1000 l_debug_level NUMBER;
1001
1002 BEGIN
1003
1004 -- standard start of api savepoint
1005 IF fnd_api.to_boolean(p_commit)
1006 THEN
1007 SAVEPOINT create_transactions_pvt;
1008 END IF;
1009
1010 -- standard call to check for call compatibility.
1011 IF NOT fnd_api.compatible_api_call ( l_api_version,
1012 p_api_version,
1013 l_api_name,
1014 g_pkg_name)
1015 THEN
1016 RAISE fnd_api.g_exc_unexpected_error;
1017 END IF;
1018
1019
1020 -- initialize message list if p_init_msg_list is set to true.
1021 IF fnd_api.to_boolean( p_init_msg_list )
1022 THEN
1023 fnd_msg_pub.initialize;
1024 END IF;
1025
1026
1027 -- initialize api return status to success
1028 x_return_status := fnd_api.g_ret_sts_success;
1029
1030 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
1031 IF (l_debug_level > 0) THEN
1032 CSI_gen_utility_pvt.put_line( 'create_transaction');
1033 END IF;
1034
1035 IF (l_debug_level > 1) THEN
1036 CSI_gen_utility_pvt.put_line(
1037 p_api_version ||'-'||
1038 p_init_msg_list ||'-'||
1039 p_commit ||'-'||
1040 p_validation_level ||'-'||
1041 p_success_if_exists_flag
1042 );
1043 csi_gen_utility_pvt.dump_txn_rec(p_transaction_rec);
1044
1045
1046 END IF;
1047 --
1048 -- api body
1049 --
1050
1051 IF p_success_if_exists_flag ='Y' THEN
1052 /* If success flag is Y then */
1053 IF ((p_transaction_rec.transaction_id IS NOT NULL) AND
1054 (p_transaction_rec.transaction_id <> fnd_api.g_miss_num)) THEN
1055 /* If success flag is Y then check for id if present return success */
1056
1057 BEGIN
1058 SELECT 'x'
1059 INTO l_dummy
1060 FROM csi_transactions
1061 WHERE transaction_id=p_transaction_rec.transaction_id
1062 AND rownum=1;
1063 IF SQL%FOUND THEN
1064 x_return_status := fnd_api.g_ret_sts_success;
1065 l_create_flag :='N';
1066 END IF;
1067
1068 EXCEPTION WHEN no_data_found THEN
1069 /* If success flag is Y then if passed id not found then proceed with validations */
1070 validate_transactions(
1071 p_init_msg_list => fnd_api.g_false,
1072 p_validation_level => p_validation_level,
1073 p_validation_mode => 'CREATE',
1074 p_transaction_rec => p_transaction_rec,
1075 x_return_status => x_return_status,
1076 x_msg_count => x_msg_count,
1077 x_msg_data => x_msg_data);
1078 END;
1079 ELSE
1080 /* If success flag is Y then if id not passed proceed with validations */
1081 validate_transactions(
1082 p_init_msg_list => fnd_api.g_false,
1083 p_validation_level => p_validation_level,
1084 p_validation_mode => 'CREATE',
1085 p_transaction_rec => p_transaction_rec,
1086 x_return_status => x_return_status,
1087 x_msg_count => x_msg_count,
1088 x_msg_data => x_msg_data);
1089 END IF;
1090 ELSE
1091
1092 /* If success flag is 'N' then if id passed already exists then return error mesg */
1093
1094 IF ((p_transaction_rec.transaction_id IS NOT NULL) AND
1095 (p_transaction_rec.transaction_id <> fnd_api.g_miss_num)) THEN
1096 BEGIN
1097 SELECT 'x'
1098 INTO l_dummy
1099 FROM csi_transactions
1100 WHERE transaction_id=p_transaction_rec.transaction_id
1101 AND rownum=1;
1102
1103 IF SQL%FOUND THEN
1104 fnd_message.set_name('CSI', 'CSI_TXN_ID_ALREADY_EXISTS');
1105 fnd_message.set_token('transaction_id',p_transaction_rec.transaction_id);
1109
1106 fnd_msg_pub.add;
1107 x_return_status := fnd_api.g_ret_sts_error;
1108 END IF;
1110 EXCEPTION WHEN no_data_found THEN
1111 /* If success flag is 'N' then if id passed not exists then proceed with validations */
1112 validate_transactions(
1113 p_init_msg_list => fnd_api.g_false,
1114 p_validation_level => p_validation_level,
1115 p_validation_mode => 'CREATE',
1116 p_transaction_rec => p_transaction_rec,
1117 x_return_status => x_return_status,
1118 x_msg_count => x_msg_count,
1119 x_msg_data => x_msg_data);
1120 END;
1121 -- added code for flag ='n and id passed
1122 ELSE
1123 /* If success flag is 'N' then if id not passed then proceed with validations */
1124 validate_transactions(
1125 p_init_msg_list => fnd_api.g_false,
1126 p_validation_level => p_validation_level,
1127 p_validation_mode => 'CREATE',
1128 p_transaction_rec => p_transaction_rec,
1129 x_return_status => x_return_status,
1130 x_msg_count => x_msg_count,
1131 x_msg_data => x_msg_data);
1132 END IF;
1133 END IF;
1134
1135
1136
1137 IF x_return_status<>fnd_api.g_ret_sts_success THEN
1138
1139 RAISE fnd_api.g_exc_error;
1140 END IF;
1141
1142
1143
1144 SELECT SYSDATE
1145 INTO l_transaction_date
1146 FROM dual;
1147
1148 IF p_transaction_rec.transaction_date=fnd_api.g_miss_date THEN
1149 l_transaction_date:=l_transaction_date;
1150 END IF;
1151
1152 IF p_transaction_rec.gl_interface_status_code IS NULL OR
1153 p_transaction_rec.gl_interface_status_code = FND_API.G_MISS_NUM
1154 THEN
1155 p_transaction_rec.gl_interface_status_code :=1;
1156 ELSE
1157 IF p_transaction_rec.gl_interface_status_code NOT IN (1,2,3)
1158 THEN
1159 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_GL_STATUS');
1160 FND_MESSAGE.SET_TOKEN('STATUS_CODE',p_transaction_rec.gl_interface_status_code);
1161 FND_MSG_PUB.Add;
1162 RAISE fnd_api.g_exc_error;
1163 END IF;
1164 END IF;
1165
1166 --x_transaction_id:=p_transaction_rec.transaction_id;
1167 -- invoke table handler(csi_transactions_pkg.insert_row)
1168 IF l_create_flag ='Y' THEN
1169 p_transaction_rec.object_version_number:=1;
1170 csi_transactions_pkg.insert_row(
1171 px_transaction_id => p_transaction_rec.transaction_id,
1172 p_transaction_date => l_transaction_date,
1173 p_source_transaction_date => p_transaction_rec.source_transaction_date,
1174 p_transaction_type_id => p_transaction_rec.transaction_type_id,
1175 p_txn_sub_type_id => p_transaction_rec.txn_sub_type_id,
1176 p_source_group_ref_id => p_transaction_rec.source_group_ref_id,
1177 p_source_group_ref => p_transaction_rec.source_group_ref,
1178 p_source_header_ref_id => p_transaction_rec.source_header_ref_id,
1179 p_source_header_ref => p_transaction_rec.source_header_ref,
1180 p_source_line_ref_id => p_transaction_rec.source_line_ref_id,
1181 p_source_line_ref => p_transaction_rec.source_line_ref,
1182 p_source_dist_ref_id1 => p_transaction_rec.source_dist_ref_id1,
1183 p_source_dist_ref_id2 => p_transaction_rec.source_dist_ref_id2,
1184 p_inv_material_transaction_id => p_transaction_rec.inv_material_transaction_id,
1185 p_transaction_quantity => p_transaction_rec.transaction_quantity,
1186 p_transaction_uom_code => p_transaction_rec.transaction_uom_code,
1187 p_transacted_by => p_transaction_rec.transacted_by,
1188 p_transaction_status_code => p_transaction_rec.transaction_status_code,
1189 p_transaction_action_code => p_transaction_rec.transaction_action_code,
1190 p_message_id => p_transaction_rec.message_id,
1191 p_context => p_transaction_rec.context,
1192 p_attribute1 => p_transaction_rec.attribute1,
1193 p_attribute2 => p_transaction_rec.attribute2,
1194 p_attribute3 => p_transaction_rec.attribute3,
1195 p_attribute4 => p_transaction_rec.attribute4,
1196 p_attribute5 => p_transaction_rec.attribute5,
1197 p_attribute6 => p_transaction_rec.attribute6,
1198 p_attribute7 => p_transaction_rec.attribute7,
1199 p_attribute8 => p_transaction_rec.attribute8,
1200 p_attribute9 => p_transaction_rec.attribute9,
1201 p_attribute10 => p_transaction_rec.attribute10,
1202 p_attribute11 => p_transaction_rec.attribute11,
1203 p_attribute12 => p_transaction_rec.attribute12,
1204 p_attribute13 => p_transaction_rec.attribute13,
1205 p_attribute14 => p_transaction_rec.attribute14,
1206 p_attribute15 => p_transaction_rec.attribute15,
1207 p_created_by => fnd_global.user_id,
1208 p_creation_date => SYSDATE,
1209 p_last_updated_by => fnd_global.user_id,
1210 p_last_update_date => SYSDATE,
1211 p_last_update_login => fnd_global.conc_login_id,
1212 p_object_version_number => p_transaction_rec.object_version_number,
1216
1213 p_split_reason_code => p_transaction_rec.split_reason_code,
1214 p_gl_interface_status_code => p_transaction_rec.gl_interface_status_code
1215 );
1217 END IF;
1218 -- hint: primary key should be returned.
1219 -- x_transaction_id := px_transaction_id;
1220 --p_transaction_rec.transaction_id := px_transaction_id;
1221
1222 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1223 RAISE fnd_api.g_exc_error;
1224 END IF;
1225
1226 --
1227 -- end of api body
1228 --
1229
1230 -- standard check for p_commit
1231 IF fnd_api.to_boolean( p_commit )
1232 THEN
1233 COMMIT WORK;
1234 END IF;
1235
1236
1237 -- standard call to get message count and if count is 1, get message info.
1238 fnd_msg_pub.count_and_get
1239 ( p_count => x_msg_count,
1240 p_data => x_msg_data
1241 );
1242
1243 EXCEPTION
1244 WHEN fnd_api.g_exc_error THEN
1245 IF fnd_api.to_boolean(p_commit)
1246 THEN
1247 ROLLBACK TO create_transactions_pvt;
1248 END IF;
1249 x_return_status := fnd_api.g_ret_sts_error ;
1250 fnd_msg_pub.count_and_get
1251 (p_count => x_msg_count ,
1252 p_data => x_msg_data
1253 );
1254
1255 WHEN fnd_api.g_exc_unexpected_error THEN
1256 IF fnd_api.to_boolean(p_commit)
1257 THEN
1258 ROLLBACK TO create_transactions_pvt;
1259 END IF;
1260 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1261 fnd_msg_pub.count_and_get
1262 (p_count => x_msg_count ,
1263 p_data => x_msg_data
1264 );
1265
1266 WHEN OTHERS THEN
1267 IF fnd_api.to_boolean(p_commit)
1268 THEN
1269 ROLLBACK TO create_transactions_pvt;
1270 END IF;
1271 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1272 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1273 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
1274 END IF;
1275 fnd_msg_pub.count_and_get
1276 (p_count => x_msg_count ,
1277 p_data => x_msg_data
1278 );
1279
1280
1281 END create_transaction;
1282
1283
1284 /* ---------------------------------------------------------------------------------- */
1285 /* --- This procedure is used to update transactions --- */
1286 /* --- and call table handler after performing all the validations --- */
1287 /* ---------------------------------------------------------------------------------- */
1288
1289
1290 PROCEDURE update_transactions(
1291 p_api_version IN NUMBER,
1292 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1293 p_commit IN VARCHAR2 := fnd_api.g_false,
1294 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1295 p_transaction_rec IN csi_datastructures_pub.transaction_rec,
1296 x_return_status OUT NOCOPY VARCHAR2,
1297 x_msg_count OUT NOCOPY NUMBER,
1298 x_msg_data OUT NOCOPY VARCHAR2
1299 )
1300
1301 IS
1302 CURSOR lock_csr (p_transaction_rec IN csi_datastructures_pub.transaction_rec) IS
1303 SELECT object_version_number
1304 FROM csi_transactions
1305 WHERE transaction_id = p_transaction_rec.transaction_id
1306 FOR UPDATE OF object_version_number;
1307
1308
1309 l_api_name CONSTANT VARCHAR2(30) := 'update_transactions';
1310 l_api_version CONSTANT NUMBER := 1.0;
1311 -- local variables
1312 l_tar_transaction_rec csi_datastructures_pub.transaction_rec := p_transaction_rec;
1313 l_row_notfound BOOLEAN := FALSE;
1314 l_object_version_number NUMBER;
1315 l_last_update_date DATE;
1316 l_rowid ROWID;
1317 l_debug_level NUMBER;
1318
1319 BEGIN
1320 -- standard start of api savepoint
1321 IF fnd_api.to_boolean(p_commit)
1322 THEN
1323 SAVEPOINT update_transactions_pvt;
1324 END IF;
1325
1326 -- standard call to check for call compatibility.
1327 IF NOT fnd_api.compatible_api_call ( l_api_version,
1328 p_api_version,
1329 l_api_name,
1330 g_pkg_name)
1331 THEN
1332 RAISE fnd_api.g_exc_unexpected_error;
1333 END IF;
1334
1335
1336 -- initialize message list if p_init_msg_list is set to true.
1337 IF fnd_api.to_boolean( p_init_msg_list )
1338 THEN
1339 fnd_msg_pub.initialize;
1340 END IF;
1341 -- initialize api return status to success
1342 x_return_status := fnd_api.g_ret_sts_success;
1343
1344 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
1345 IF (l_debug_level > 0) THEN
1346 CSI_gen_utility_pvt.put_line( 'update_transactions');
1347 END IF;
1348 IF (l_debug_level > 1) THEN
1349 CSI_gen_utility_pvt.put_line(
1350 p_api_version ||'-'||
1351 p_init_msg_list ||'-'||
1355
1352 p_commit ||'-'||
1353 p_validation_level );
1354 csi_gen_utility_pvt.dump_txn_rec(p_transaction_rec);
1356
1357 END IF;
1358
1359 --
1360 -- api body
1361 --
1362
1363 OPEN lock_csr (p_transaction_rec);
1364 FETCH lock_csr INTO l_object_version_number;
1365 IF ( (l_object_version_number<>p_transaction_rec.object_version_number)
1366 AND (p_transaction_rec.object_version_number <> fnd_api.g_miss_num) ) THEN
1367 fnd_message.set_name('CSI', 'CSI_RECORD_CHANGED');
1368 fnd_msg_pub.add;
1369 RAISE fnd_api.g_exc_error;
1370 END IF;
1371
1372 IF lock_csr%NOTFOUND THEN
1373 fnd_message.set_name('CSI', 'CSI_RECORD_LOCKED');
1374 fnd_msg_pub.add;
1375 RAISE fnd_api.g_exc_error;
1376 END IF;
1377
1378 CLOSE lock_csr;
1379
1380 -- invoke validation procedures
1381
1382 validate_transaction_id (
1383 p_init_msg_list => fnd_api.g_false,
1384 p_validation_mode => 'UPDATE',
1385 p_transaction_id => p_transaction_rec.transaction_id,
1386 x_return_status => x_return_status,
1387 x_msg_count => x_msg_count,
1388 x_msg_data => x_msg_data);
1389
1390 IF x_return_status=fnd_api.g_ret_sts_success THEN
1391 validate_object_version_num (
1392 p_init_msg_list => fnd_api.g_false,
1393 p_validation_mode => 'UPDATE',
1394 p_object_version_number => p_transaction_rec.object_version_number,
1395 x_return_status => x_return_status,
1396 x_msg_count => x_msg_count,
1397 x_msg_data => x_msg_data);
1398 END IF;
1399
1400 IF x_return_status<>fnd_api.g_ret_sts_success THEN
1401 RAISE fnd_api.g_exc_error;
1402 END IF;
1403
1404 IF p_transaction_rec.gl_interface_status_code IS NOT NULL AND
1405 p_transaction_rec.gl_interface_status_code <> FND_API.G_MISS_NUM AND
1406 p_transaction_rec.gl_interface_status_code NOT IN (1,2,3)
1407 THEN
1408 FND_MESSAGE.SET_NAME('CSI','CSI_INVALID_GL_STATUS');
1409 FND_MESSAGE.SET_TOKEN('STATUS_CODE',p_transaction_rec.gl_interface_status_code);
1410 FND_MSG_PUB.Add;
1411 RAISE fnd_api.g_exc_error;
1412 END IF;
1413
1414 -- invoke table handler(csi_transactions_pkg.update_row)
1415 csi_transactions_pkg.update_row(
1416 p_transaction_id => p_transaction_rec.transaction_id,
1417 p_transaction_status_code => p_transaction_rec.transaction_status_code,
1418 p_transaction_action_code => p_transaction_rec.transaction_action_code,
1419 p_source_group_ref => p_transaction_rec.source_group_ref,
1420 p_source_group_ref_id => p_transaction_rec.source_group_ref_id,
1421 p_source_dist_ref_id2 => p_transaction_rec.source_dist_ref_id2,
1422 p_source_header_ref => p_transaction_rec.source_header_ref,
1423 p_source_header_ref_id => p_transaction_rec.source_header_ref_id,
1424 p_source_line_ref => p_transaction_rec.source_line_ref,
1425 p_source_line_ref_id => p_transaction_rec.source_line_ref_id,
1426 p_last_updated_by => fnd_global.user_id,
1427 p_last_update_date => SYSDATE,
1428 p_last_update_login => fnd_global.conc_login_id,
1429 p_object_version_number => p_transaction_rec.object_version_number,
1430 p_gl_interface_status_code => p_transaction_rec.gl_interface_status_code);
1431 --
1432 -- end of api body.
1433 --
1434
1435 -- standard check for p_commit
1436 IF fnd_api.to_boolean( p_commit )
1437 THEN
1438 COMMIT WORK;
1439 END IF;
1440
1441
1442
1443
1444 -- standard call to get message count and if count is 1, get message info.
1445 fnd_msg_pub.count_and_get
1446 ( p_count => x_msg_count,
1447 p_data => x_msg_data
1448 );
1449
1450 EXCEPTION
1451 WHEN fnd_api.g_exc_error THEN
1452 IF fnd_api.to_boolean(p_commit)
1453 THEN
1454 ROLLBACK TO update_transactions_pvt;
1455 END IF;
1456 x_return_status := fnd_api.g_ret_sts_error ;
1457 fnd_msg_pub.count_and_get
1458 (p_count => x_msg_count ,
1459 p_data => x_msg_data
1460 );
1461
1462 WHEN fnd_api.g_exc_unexpected_error THEN
1463 IF fnd_api.to_boolean(p_commit)
1464 THEN
1465 ROLLBACK TO update_transactions_pvt;
1466 END IF;
1467 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1468 fnd_msg_pub.count_and_get
1469 (p_count => x_msg_count ,
1470 p_data => x_msg_data
1471 );
1472
1473 WHEN OTHERS THEN
1474 IF fnd_api.to_boolean(p_commit)
1475 THEN
1476 ROLLBACK TO update_transactions_pvt;
1477 END IF;
1478 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1479 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1480 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
1481 END IF;
1482 fnd_msg_pub.count_and_get
1483 (p_count => x_msg_count ,
1484 p_data => x_msg_data
1485 );
1489 /* ---------------------------------------------------------------------------------- */
1486
1487 END update_transactions;
1488
1490 /* --- This procedure is used to insert records into csi_txn_errors --- */
1491 /* ---------------------------------------------------------------------------------- */
1492
1493 PROCEDURE create_txn_error
1494 (
1495 p_api_version IN NUMBER,
1496 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1497 p_commit IN VARCHAR2 := fnd_api.g_false,
1498 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1499 p_txn_error_rec IN csi_datastructures_pub.transaction_error_rec,
1500 x_return_status OUT NOCOPY VARCHAR2,
1501 x_msg_count OUT NOCOPY NUMBER,
1502 x_msg_data OUT NOCOPY VARCHAR2,
1503 x_transaction_error_id OUT NOCOPY NUMBER
1504 ) IS
1505 l_api_name CONSTANT VARCHAR2(30) := 'create_txn_error';
1506 l_api_version CONSTANT NUMBER := 1.0;
1507 l_return_status_full VARCHAR2(1);
1508 l_access_flag VARCHAR2(1);
1509 l_debug_level NUMBER;
1510
1511 BEGIN
1512 -- standard start of api savepoint
1513 IF fnd_api.to_boolean(p_commit)
1514 THEN
1515 SAVEPOINT create_txn_error_pvt;
1516 END IF;
1517 -- standard call to check for call compatibility.
1518 IF NOT fnd_api.compatible_api_call ( l_api_version,
1519 p_api_version,
1520 l_api_name,
1521 g_pkg_name)
1522 THEN
1523 RAISE fnd_api.g_exc_unexpected_error;
1524 END IF;
1525
1526
1527 -- initialize message list if p_init_msg_list is set to true.
1528 IF fnd_api.to_boolean( p_init_msg_list )
1529 THEN
1530 fnd_msg_pub.initialize;
1531 END IF;
1532
1533 -- initialize api return status TO success
1534 x_return_status := fnd_api.g_ret_sts_success;
1535
1536 /***** COMMENTED l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
1537 IF (l_debug_level > 0) THEN
1538 CSI_gen_utility_pvt.put_line( 'create_txn_error');
1539 END IF;
1540 IF (l_debug_level > 1) THEN
1541 CSI_gen_utility_pvt.put_line(
1542 p_api_version ||'-'||
1543 p_init_msg_list ||'-'||
1544 p_commit ||'-'||
1545 p_validation_level );
1546
1547 -- dump the txn error records
1548 csi_gen_utility_pvt.dump_txn_error_rec(p_txn_error_rec);
1549
1550
1551 END IF; **** END OF COMMENT *****/
1552
1553 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1554 RAISE fnd_api.g_exc_error;
1555 END IF;
1556
1557 /* End of addition for 11.5.10 */
1558
1559 -- invoke table handler(csi_txn_errors_pkg.insert_row)
1560 csi_txn_errors_pkg.insert_row(
1561 px_transaction_error_id => x_transaction_error_id,
1562 p_transaction_id => p_txn_error_rec.transaction_id,
1563 p_message_id => p_txn_error_rec.message_id,
1564 p_error_text => p_txn_error_rec.error_text,
1565 p_source_type => p_txn_error_rec.source_type,
1566 p_source_id => p_txn_error_rec.source_id,
1567 p_processed_flag => p_txn_error_rec.processed_flag,
1568 p_created_by => fnd_global.user_id,
1569 p_creation_date => SYSDATE,
1570 p_last_updated_by => fnd_global.user_id,
1571 p_last_update_date => SYSDATE,
1572 p_last_update_login => fnd_global.conc_login_id,
1573 p_object_version_number => 1,
1574 p_transaction_type_id => p_txn_error_rec.transaction_type_id ,
1575 p_source_group_ref => p_txn_error_rec.source_group_ref,
1576 p_source_group_ref_id => p_txn_error_rec.source_group_ref_id,
1577 p_source_header_ref => p_txn_error_rec.source_header_ref,
1578 p_source_header_ref_id => p_txn_error_rec.source_header_ref_id,
1579 p_source_line_ref => p_txn_error_rec.source_line_ref,
1580 p_source_line_ref_id => p_txn_error_rec.source_line_ref_id,
1581 p_source_dist_ref_id1 => p_txn_error_rec.source_dist_ref_id1,
1582 p_source_dist_ref_id2 => p_txn_error_rec.source_dist_ref_id2,
1583 p_inv_material_transaction_id => p_txn_error_rec.inv_material_transaction_id,
1584 p_error_stage => p_txn_error_rec.error_stage,
1585 p_message_string => p_txn_error_rec.message_string,
1586 p_instance_id => p_txn_error_rec.instance_id,
1587 p_inventory_item_id => p_txn_error_rec.inventory_item_id,
1588 p_serial_number => p_txn_error_rec.serial_number,
1589 p_lot_number => p_txn_error_rec.lot_number,
1590 p_transaction_error_date => p_txn_error_rec.transaction_error_date,
1591 p_src_serial_num_ctrl_code => p_txn_error_rec.src_serial_num_ctrl_code,
1592 p_src_location_ctrl_code => p_txn_error_rec.src_location_ctrl_code,
1593 p_src_lot_ctrl_code => p_txn_error_rec.src_lot_ctrl_code,
1594 p_src_rev_qty_ctrl_code => p_txn_error_rec.src_rev_qty_ctrl_code,
1595 p_dst_serial_num_ctrl_code => p_txn_error_rec.dst_serial_num_ctrl_code,
1596 p_dst_location_ctrl_code => p_txn_error_rec.dst_location_ctrl_code,
1597 p_dst_lot_ctrl_code => p_txn_error_rec.dst_lot_ctrl_code,
1601 -- x_transaction_error_id := px_transaction_error_id;
1598 p_dst_rev_qty_ctrl_code => p_txn_error_rec.dst_rev_qty_ctrl_code,
1599 p_comms_nl_trackable_flag => p_txn_error_rec.comms_nl_trackable_flag);
1600 -- hint: primary key should be returned.
1602
1603 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1604 RAISE fnd_api.g_exc_error;
1605 END IF;
1606
1607 --
1608 -- end of api body
1609 --
1610
1611 -- standard check for p_commit
1612 IF fnd_api.to_boolean( p_commit )
1613 THEN
1614 COMMIT WORK;
1615 END IF;
1616
1617 -- standard call to get message count and if count is 1, get message info.
1618 fnd_msg_pub.count_and_get
1619 ( p_count => x_msg_count,
1620 p_data => x_msg_data
1621 );
1622
1623 EXCEPTION
1624 WHEN fnd_api.g_exc_error THEN
1625 IF fnd_api.to_boolean(p_commit)
1626 THEN
1627 ROLLBACK TO create_txn_error_pvt;
1628 END IF;
1629 x_return_status := fnd_api.g_ret_sts_error ;
1630 fnd_msg_pub.count_and_get
1631 (p_count => x_msg_count ,
1632 p_data => x_msg_data
1633 );
1634
1635 WHEN fnd_api.g_exc_unexpected_error THEN
1636 IF fnd_api.to_boolean(p_commit)
1637 THEN
1638 ROLLBACK TO create_txn_error_pvt;
1639 END IF;
1640 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1641 fnd_msg_pub.count_and_get
1642 (p_count => x_msg_count ,
1643 p_data => x_msg_data
1644 );
1645
1646 WHEN OTHERS THEN
1647 IF fnd_api.to_boolean(p_commit)
1648 THEN
1649 ROLLBACK TO create_txn_error_pvt;
1650 END IF;
1651 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1652 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1653 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
1654 END IF;
1655 fnd_msg_pub.count_and_get
1656 (p_count => x_msg_count ,
1657 p_data => x_msg_data
1658 );
1659
1660
1661 END create_txn_error;
1662
1663 /* ---------------------------------------------------------------------------------- */
1664 /* --- This procedure is used to validate transaction_id --- */
1665 /* --- 1. for validation_mode='CREATE' return success or no validation --- */
1666 /* --- 2. for validation_mode='UPDATE' check for not null and validate against --- */
1667 /* --- csi_transactions table --- */
1668 /* ---------------------------------------------------------------------------------- */
1669
1670
1671 PROCEDURE validate_transaction_id (
1672 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1673 p_validation_mode IN VARCHAR2,
1674 p_transaction_id IN NUMBER,
1675 x_return_status OUT NOCOPY VARCHAR2,
1676 x_msg_count OUT NOCOPY NUMBER,
1677 x_msg_data OUT NOCOPY VARCHAR2
1678 )
1679 IS
1680 l_dummy VARCHAR2(1);
1681 BEGIN
1682 -- initialize message list if p_init_msg_list is set to true.
1683 IF fnd_api.to_boolean( p_init_msg_list )
1684 THEN
1685 fnd_msg_pub.initialize;
1686 END IF;
1687
1688
1689 -- initialize api return status TO success
1690 x_return_status := fnd_api.g_ret_sts_success;
1691
1692 -- validate not null column
1693
1694 IF(p_validation_mode ='CREATE') THEN
1695 x_return_status := fnd_api.g_ret_sts_success;
1696 ELSIF(p_validation_mode = 'UPDATE') THEN
1697 IF ( (p_transaction_id IS NULL) OR (p_transaction_id = fnd_api.g_miss_num) ) THEN
1698 fnd_message.set_name('CSI', 'CSI_TXN_ID_NOT_PASSED');
1699 fnd_msg_pub.add;
1700 x_return_status := fnd_api.g_ret_sts_error;
1701 ELSE
1702 BEGIN
1703 SELECT 'x'
1704 INTO l_dummy
1705 FROM csi_transactions
1706 WHERE transaction_id=p_transaction_id;
1707 EXCEPTION
1708 WHEN no_data_found THEN
1709 fnd_message.set_name('CSI', 'CSI_INVALID_TXN_ID');
1710 fnd_message.set_token('transaction_id',p_transaction_id);
1711 fnd_msg_pub.add;
1712 x_return_status := fnd_api.g_ret_sts_error;
1713 END;
1714 END IF;
1715 END IF;
1716
1717 -- standard call to get message count and if count is 1, get message info.
1718 fnd_msg_pub.count_and_get
1719 ( p_count => x_msg_count,
1720 p_data => x_msg_data
1721 );
1722
1723 END validate_transaction_id;
1724
1725 /* ---------------------------------------------------------------------------------- */
1726 /* --- This procedure is used to validate source_transaction_date --- */
1727 /* --- 1. check for not null and return an error message --- */
1728 /* ---------------------------------------------------------------------------------- */
1729
1730
1731 PROCEDURE validate_s_transaction_date (
1732 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1736 x_msg_data OUT NOCOPY VARCHAR2
1733 p_source_transaction_date IN DATE,
1734 x_return_status OUT NOCOPY VARCHAR2,
1735 x_msg_count OUT NOCOPY NUMBER,
1737 )
1738 IS
1739 BEGIN
1740 -- initialize message list if p_init_msg_list is set to true.
1741 IF fnd_api.to_boolean( p_init_msg_list )
1742 THEN
1743 fnd_msg_pub.initialize;
1744 END IF;
1745
1746
1747 -- initialize api return status to success
1748 x_return_status := fnd_api.g_ret_sts_success;
1749
1750 -- validate not null column
1751 IF ( (p_source_transaction_date IS NULL) OR (p_source_transaction_date = fnd_api.g_miss_date) ) THEN
1752 fnd_message.set_name('CSI', 'CSI_NO_TXN_DATE');
1753 fnd_msg_pub.add;
1754 x_return_status := fnd_api.g_ret_sts_error;
1755 END IF;
1756
1757 -- standard call to get message count and if count is 1, get message info.
1758 fnd_msg_pub.count_and_get
1759 ( p_count => x_msg_count,
1760 p_data => x_msg_data
1761 );
1762
1763 END validate_s_transaction_date;
1764
1765
1766 /* ---------------------------------------------------------------------------------- */
1767 /* --- This procedure is used to validate transaction_type_id --- */
1768 /* --- 1. for null return error status --- */
1769 /* --- 2. for not null check for the exsistence in the csi_txn_types table --- */
1770 /* ---------------------------------------------------------------------------------- */
1771
1772 PROCEDURE validate_transaction_type_id (
1773 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1774 p_transaction_type_id IN NUMBER,
1775 x_return_status OUT NOCOPY VARCHAR2,
1776 x_msg_count OUT NOCOPY NUMBER,
1777 x_msg_data OUT NOCOPY VARCHAR2
1778 )
1779 IS
1780 l_dummy VARCHAR2(1);
1781 BEGIN
1782 -- initialize message list if p_init_msg_list is set to true.
1783 IF fnd_api.to_boolean( p_init_msg_list )
1784 THEN
1785 fnd_msg_pub.initialize;
1786 END IF;
1787
1788
1789 -- initialize api return status to success
1790 x_return_status := fnd_api.g_ret_sts_success;
1791
1792 -- validate not null column
1793
1794 IF ( (p_transaction_type_id IS NULL) OR (p_transaction_type_id = fnd_api.g_miss_num) ) THEN
1795 fnd_message.set_name('CSI', 'CSI_NO_TXN_TYPE_ID');
1796 fnd_msg_pub.add;
1797 x_return_status := fnd_api.g_ret_sts_error;
1798 ELSE
1799 BEGIN
1800 SELECT 'x'
1801 INTO l_dummy
1802 FROM csi_txn_types
1803 WHERE transaction_type_id=p_transaction_type_id;
1804
1805 EXCEPTION
1806 WHEN no_data_found THEN
1807 fnd_message.set_name('CSI', 'CSI_INVALID_TXN_TYPE_ID');
1808 fnd_msg_pub.add;
1809 x_return_status := fnd_api.g_ret_sts_error;
1810 END;
1811 END IF;
1812 -- standard call to get message count and if count is 1, get message info.
1813 fnd_msg_pub.count_and_get
1814 ( p_count => x_msg_count,
1815 p_data => x_msg_data
1816 );
1817 END validate_transaction_type_id;
1818
1819
1820 /* ---------------------------------------------------------------------------------- */
1821 /* --- This procedure is used to validate source_object_code --- */
1822 /* --- 1. validation will only be performed if source_line_ref_id is passed --- */
1823 /* --- 2. check for existence of a record if not exsist then return error --- */
1824 /* ---------------------------------------------------------------------------------- */
1825
1826 PROCEDURE validate_source_object (
1827 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1828 p_transaction_type_id IN NUMBER,
1829 p_source_line_ref_id IN NUMBER,
1830 x_return_status OUT NOCOPY VARCHAR2,
1831 x_msg_count OUT NOCOPY NUMBER,
1832 x_msg_data OUT NOCOPY VARCHAR2
1833 )
1834 IS
1835 CURSOR c_ref_csr (p_object_code IN VARCHAR2) IS
1836 SELECT select_id
1837 ,select_name
1838 ,from_table
1839 ,where_clause
1840 FROM jtf_objects_vl jov,jtf_object_usages jou
1841 WHERE jov.object_code = p_object_code
1842 AND jov.object_code = jou.object_code
1843 AND jou.object_user_code = 'CSI_TXN';
1844
1845 l_source_ln_ref_id NUMBER;
1846 l_source_code VARCHAR2(30);
1847 l_id_column VARCHAR2(200);
1848 l_name_column VARCHAR2(200);
1849 l_FROM_clause VARCHAR2(200);
1850 l_where_clause VARCHAR2(2000);
1851 l_flag VARCHAR2(1):='f';
1852 sql_stmt VARCHAR2(2000):='';
1853 l_object_name VARCHAR2(80);
1854 l_object_id NUMBER;
1855 BEGIN
1856 -- initialize message list if p_init_msg_list is set to true.
1857 IF fnd_api.to_boolean( p_init_msg_list )
1858 THEN
1859 fnd_msg_pub.initialize;
1860 END IF;
1861
1862
1863 -- initialize api return status to success
1864 x_return_status := fnd_api.g_ret_sts_success;
1865 BEGIN
1866 BEGIN
1870 SELECT source_object_code
1867 IF ( (p_source_line_ref_id IS NOT NULL) AND (p_source_line_ref_id <> fnd_api.g_miss_num) ) THEN
1868 l_flag:='t';
1869 END IF;
1871 INTO l_source_code
1872 FROM csi_txn_types
1873 WHERE transaction_type_id=p_transaction_type_id;
1874 EXCEPTION
1875 WHEN no_data_found THEN
1876 l_flag:='f';
1877 END;
1878
1879 BEGIN
1880 IF ( (l_flag='t') AND (l_source_code IS NOT NULL) ) THEN
1881 OPEN c_ref_csr(l_source_code);
1882 FETCH c_ref_csr INTO l_id_column,
1883 l_name_column,
1884 l_from_clause,
1885 l_where_clause;
1886
1887 IF c_ref_csr%notfound
1888 THEN
1889 fnd_message.set_name ('CSI', 'CSI_INVALID_OBJECT_CODE');
1890 fnd_msg_pub.add;
1891 x_return_status := fnd_api.g_ret_sts_error;
1892 END IF;
1893
1894 IF x_return_status <> fnd_api.g_ret_sts_error THEN
1895 IF(l_where_clause IS NULL) THEN
1896 l_where_clause := ' WHERE ';
1897 ELSE
1898 l_where_clause := l_where_clause || ' AND ';
1899 END IF;
1900
1901 sql_stmt := ' SELECT ' ||
1902 l_name_column ||
1903 ' , ' ||
1904 l_id_column ||
1905 ' FROM ' ||
1906 l_FROM_clause ||
1907 l_where_clause ||
1908 l_id_column ||
1909 ' = :source_line_ref_id ';
1910
1911 EXECUTE IMMEDIATE sql_stmt
1912 INTO l_object_name, l_object_id
1913 USING p_source_line_ref_id;
1914 END IF;
1915
1916 CLOSE c_ref_csr;
1917
1918 END IF;
1919 EXCEPTION
1920 WHEN no_data_found THEN
1921 fnd_message.set_name('CSI', 'CSI_REF_NOT_FOUND');
1922 fnd_msg_pub.add;
1923 x_return_status := fnd_api.g_ret_sts_error;
1924
1925 END;
1926 END;
1927
1928 -- standard call to get message count and if count is 1, get message info.
1929 fnd_msg_pub.count_and_get
1930 ( p_count => x_msg_count,
1931 p_data => x_msg_data
1932 );
1933 END validate_source_object;
1934
1935
1936 /* ---------------------------------------------------------------------------------- */
1937 /* --- this procedure is used to validate transaction_id --- */
1938 /* --- 1. for validation_mode='CREATE' return success or no validation --- */
1939 /* --- 2. for validation_mode='UPDATE' check for not null and validate against --- */
1940 /* --- csi_transactions table --- */
1941 /* ---------------------------------------------------------------------------------- */
1942
1943
1944 PROCEDURE validate_object_version_num (
1945 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1946 p_validation_mode IN VARCHAR2,
1947 p_object_version_number IN NUMBER,
1948 x_return_status OUT NOCOPY VARCHAR2,
1949 x_msg_count OUT NOCOPY NUMBER,
1950 x_msg_data OUT NOCOPY VARCHAR2
1951 )
1952 IS
1953 l_dummy VARCHAR2(1);
1954 BEGIN
1955 -- initialize message list if p_init_msg_list is set to true.
1956 IF fnd_api.to_boolean( p_init_msg_list )
1957 THEN
1958 fnd_msg_pub.initialize;
1959 END IF;
1960
1961
1962 -- initialize api return status to success
1963 x_return_status := fnd_api.g_ret_sts_success;
1964
1965 IF(p_validation_mode = 'UPDATE') THEN
1966 IF ( (p_object_version_number IS NULL) OR (p_object_version_number = fnd_api.g_miss_num) ) THEN
1967 fnd_message.set_name('CSI', 'CSI_MISSING_OBJ_VER_NUM');
1968 fnd_msg_pub.add;
1969 x_return_status := fnd_api.g_ret_sts_error;
1970 END IF;
1971 END IF;
1972
1973 -- standard call to get message count and if count is 1, get message info.
1974 fnd_msg_pub.count_and_get
1975 ( p_count => x_msg_count,
1976 p_data => x_msg_data
1977 );
1978
1979 END validate_object_version_num;
1980
1981 /* ---------------------------------------------------------------------------------- */
1982 /* --- This local procedure is used to validate --- */
1983 /* --- the split_reason_code passed --- */
1984 /* ---------------------------------------------------------------------------------- */
1985
1986 PROCEDURE validate_split_code (
1987 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1988 p_split_reason_code IN VARCHAR2,
1989 x_return_status OUT NOCOPY VARCHAR2,
1990 x_msg_count OUT NOCOPY NUMBER ,
1991 x_msg_data OUT NOCOPY VARCHAR2
1992 )
1993 IS
1994 l_dummy VARCHAR2(1);
1995 BEGIN
1996 -- initialize message list if p_init_msg_list is set to true.
1997 IF fnd_api.to_boolean( p_init_msg_list )
1998 THEN
1999 fnd_msg_pub.initialize;
2000 END IF;
2001
2002
2003 -- initialize api return status to success
2004 x_return_status := fnd_api.g_ret_sts_success;
2005
2006 -- validate not null column
2007
2008 IF ( (p_split_reason_code IS NOT NULL) AND (p_split_reason_code <> fnd_api.g_miss_char) ) THEN
2009 BEGIN
2010 SELECT 'x'
2011 INTO l_dummy
2012 FROM csi_lookups
2013 WHERE lookup_type='CSI_SPLIT_REASON_CODE'
2014 AND lookup_code=p_split_reason_code;
2015 EXCEPTION
2016 WHEN no_data_found THEN
2017 fnd_message.set_name('CSI', 'CSI_INVALID_REASON_CODE');
2018 fnd_msg_pub.add;
2019 x_return_status := fnd_api.g_ret_sts_error;
2020 END;
2021 END IF;
2022 -- standard call to get message count and if count is 1, get message info.
2023 fnd_msg_pub.count_and_get
2024 ( p_count => x_msg_count,
2025 p_data => x_msg_data
2026 );
2027 END validate_split_code;
2028
2029
2030 /* ---------------------------------------------------------------------------------- */
2031 /* --- This procedure is used to call all validation transactions like --- */
2032 /* --- 1. transaction_id --- */
2033 /* --- 2. source_transaction_id --- */
2034 /* --- 3. transaction_type_id --- */
2035 /* ---------------------------------------------------------------------------------- */
2036
2037
2038
2039 PROCEDURE validate_transactions(
2040 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2041 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2042 p_validation_mode IN VARCHAR2,
2043 p_transaction_rec IN csi_datastructures_pub.transaction_rec,
2044 x_return_status OUT NOCOPY VARCHAR2,
2045 x_msg_count OUT NOCOPY NUMBER,
2046 x_msg_data OUT NOCOPY VARCHAR2
2047 )
2048 IS
2049 l_api_name CONSTANT VARCHAR2(30) := 'validate_transactions';
2050 BEGIN
2051 -- initialize api return status to success
2052 x_return_status := fnd_api.g_ret_sts_success;
2053 IF (p_validation_level >= fnd_api.g_valid_level_full) THEN
2054 validate_transaction_id(
2055 p_init_msg_list => fnd_api.g_false,
2056 p_validation_mode => p_validation_mode,
2057 p_transaction_id => p_transaction_rec.transaction_id,
2058 x_return_status => x_return_status,
2059 x_msg_count => x_msg_count,
2060 x_msg_data => x_msg_data);
2061 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2062 RAISE fnd_api.g_exc_error;
2063 END IF;
2064
2065
2066 validate_s_transaction_date(
2067 p_init_msg_list => fnd_api.g_false,
2068 p_source_transaction_date => p_transaction_rec.source_transaction_date,
2069 x_return_status => x_return_status,
2070 x_msg_count => x_msg_count,
2071 x_msg_data => x_msg_data);
2072 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2073 RAISE fnd_api.g_exc_error;
2074 END IF;
2075
2076 validate_transaction_type_id(
2077 p_init_msg_list => fnd_api.g_false,
2078 p_transaction_type_id => p_transaction_rec.transaction_type_id,
2079 x_return_status => x_return_status,
2080 x_msg_count => x_msg_count,
2081 x_msg_data => x_msg_data);
2082 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2083 RAISE fnd_api.g_exc_error;
2084 END IF;
2085
2086 validate_source_object(
2087 p_init_msg_list => fnd_api.g_false,
2088 p_transaction_type_id => p_transaction_rec.transaction_type_id,
2089 p_source_line_ref_id => p_transaction_rec.source_line_ref_id,
2090 x_return_status => x_return_status,
2091 x_msg_count => x_msg_count,
2092 x_msg_data => x_msg_data);
2093 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2094 RAISE fnd_api.g_exc_error;
2095 END IF;
2096
2097
2098 validate_split_code(
2099 p_init_msg_list => fnd_api.g_false,
2100 p_split_reason_code => p_transaction_rec.split_reason_code,
2101 x_return_status => x_return_status,
2102 x_msg_count => x_msg_count,
2103 x_msg_data => x_msg_data);
2104 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2105 RAISE fnd_api.g_exc_error;
2106 END IF;
2107
2108
2109 END IF;
2110
2111 END validate_transactions;
2112
2113 END csi_transactions_pvt;