[Home] [Help]
PACKAGE BODY: APPS.INV_STANDALONE_SYNC_PUB
Source
1 PACKAGE BODY inv_standalone_sync_pub as
2 /* $Header: INVSLSPB.pls 120.1 2010/01/19 09:42:14 hjogleka noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_STANDALONE_SYNC_PUB';
5 g_debug NUMBER;
6 TYPE adj_txn_tbl_type IS TABLE OF MTL_ADJUSTMENT_TXN_SYNC_V%ROWTYPE INDEX BY BINARY_INTEGER;
7 -- Bug 8784314
8 TYPE txn_id_tbl_type IS TABLE OF NUMBER;
9 TYPE t_genref IS REF CURSOR;
10
11 PROCEDURE print_debug(p_message IN VARCHAR2, p_level IN NUMBER DEFAULT 11) IS
12 BEGIN
13 IF g_debug IS NULL THEN
14 g_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
15 END IF;
16
17 IF (g_debug = 1) THEN
18 inv_log_util.trace(p_message, g_pkg_name, p_level);
19 END IF;
20 END;
21
22 FUNCTION Update_RC_Extracted(
23 p_api_version IN NUMBER
24 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
25 , p_commit IN VARCHAR2 := FND_API.G_FALSE
26 , x_return_status OUT NOCOPY VARCHAR2
27 , x_msg_count OUT NOCOPY NUMBER
28 , x_msg_data OUT NOCOPY VARCHAR2
29 , p_start_date IN VARCHAR2
30 , p_end_date IN VARCHAR2
31 , p_category IN VARCHAR2 DEFAULT NULL
32 , p_warehouse IN VARCHAR2
33 , p_document_num IN VARCHAR2 DEFAULT NULL
34 , p_receipt_num IN VARCHAR2
35 , p_inventory_item IN VARCHAR2 DEFAULT NULL
36 , p_rc_extracted IN VARCHAR2
37 , p_transaction_id IN NUMBER DEFAULT NULL
38 ) RETURN VARCHAR2 IS
39
40 l_api_version CONSTANT NUMBER := 1.0;
41 l_api_name CONSTANT VARCHAR2(30) := 'Update_RC_Extracted';
42 err_msg VARCHAR2(100) := NULL;
43 l_from_date DATE;
44 l_to_date DATE;
45 l_category VARCHAR2(500);
46 l_warehouse VARCHAR2(30);
47 l_document_num VARCHAR2(30);
48 l_receipt_num VARCHAR2(500);
49 l_inventory_item VARCHAR2(500);
50 l_rc_extracted VARCHAR2(1);
51 l_transaction_id NUMBER;
52
53 BEGIN
54 IF NOT FND_API.Compatible_API_Call ( l_api_version, p_api_version ,
55 l_api_name , G_PKG_NAME )
56 THEN
57 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
58 END IF;
59
60 IF FND_API.to_Boolean( p_init_msg_list ) THEN
61 FND_MSG_PUB.initialize;
62 END IF;
63
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65 print_debug('In Update_RC_Extracted');
66
67 IF (g_debug = 1) THEN
68 print_debug('In Update_RC_Extracted');
69 print_debug('p_from_date : ' || p_start_date);
70 print_debug('p_to_date : ' || p_end_date);
71 print_debug('p_category : ' || p_category);
72 print_debug('p_warehouse : ' || p_warehouse);
73 print_debug('p_document_num : ' || p_document_num);
74 print_debug('p_receipt_num : ' || p_receipt_num);
75 print_debug('p_RC_Extracted : ' || p_rc_extracted);
76 print_debug('p_transaction_id : ' || p_transaction_id);
77 print_debug('p_inventory_item : ' || p_inventory_item);
78 END IF;
79
80 l_from_date := to_date(p_start_date);
81 l_to_date := to_date(p_end_date);
82 l_category := p_category;
83 l_warehouse := p_warehouse;
84 l_document_num := p_document_num;
85 l_receipt_num := p_receipt_num;
86 l_rc_extracted := p_rc_extracted;
87 l_transaction_id := p_transaction_id;
88 l_inventory_item := p_inventory_item;
89
90
91 UPDATE rcv_transactions
92 SET receipt_confirmation_extracted = l_RC_Extracted
93 WHERE transaction_id IN
94 (SELECT transaction_id
95 FROM rcv_receipt_confirmation_v
96 WHERE creation_date BETWEEN l_from_date AND l_to_date
97 AND category = nvl(l_category, category)
98 AND warehouse = l_warehouse
99 AND item = nvl(l_inventory_item, item)
100 AND document_number = nvl(l_document_num, document_number)
101 AND receipt = l_receipt_num
102 AND transaction_id = nvl(l_transaction_id, transaction_id)
103 AND RC_Extracted IS NULL);
104
105 IF (g_debug = 1) THEN
106 print_debug('Updated ' || SQL%ROWCOUNT || ' rows.');
107 END IF;
108
109 IF FND_API.To_Boolean( p_commit ) THEN
110 COMMIT WORK;
111 END IF;
112
113 RETURN 'S';
114
115 EXCEPTION
116 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
118 FND_MSG_PUB.Count_And_Get
119 ( p_count => x_msg_count ,
120 p_data => x_msg_data
121 );
122 IF (g_debug = 1) THEN
123 print_debug('API Version Incompatible in the call to Update_RC_Extracted' );
124 END IF;
125 RETURN 'U';
126
127 WHEN OTHERS THEN
128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
129 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
130 THEN
131 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
132 END IF;
133 FND_MSG_PUB.Count_And_Get
134 ( p_count => x_msg_count ,
135 p_data => x_msg_data
136 );
137 IF (g_debug = 1) THEN
138 print_debug('Error in Update_RC_Extracted' );
139 END IF;
140 RETURN 'U';
141
142 END Update_RC_Extracted;
143
144
145 FUNCTION sync_adjustment_transactions(
146 p_api_version IN NUMBER
147 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
148 , p_commit IN VARCHAR2 := FND_API.G_FALSE
149 , x_return_status OUT NOCOPY VARCHAR2
150 , x_msg_count OUT NOCOPY NUMBER
151 , x_msg_data OUT NOCOPY VARCHAR2
152 , p_from_date IN DATE
153 , p_to_date IN DATE
154 , p_organization_name IN VARCHAR2
155 , p_category_name IN VARCHAR2
156 , p_inventory_item IN VARCHAR2 DEFAULT NULL
157 , p_transaction_type IN VARCHAR2 DEFAULT NULL
158 , p_transaction_source IN VARCHAR2 DEFAULT NULL
159 , p_transaction_id IN NUMBER DEFAULT NULL
160 , p_extract_flag IN VARCHAR2
161 ) RETURN VARCHAR2 AS
162 l_api_version CONSTANT NUMBER := 1.0;
163 l_api_name CONSTANT VARCHAR2(30) := 'sync_adjustment_transactions';
164
165 l_organization_id NUMBER;
166 l_inventory_item_id NUMBER;
167 l_transaction_type_id NUMBER;
168 err_msg VARCHAR2(100) := NULL;
169 ret_cursor INV_STANDALONE_SYNC_PUB.t_genref := NULL;
170 BEGIN
171
172 print_debug('sync_adjustment_transactions(Value) Entered');
173 IF (g_debug = 1) THEN
174 print_debug('param = ' ||p_from_date||','||p_to_date||','||p_organization_name||','||p_category_name||','
175 ||p_inventory_item||','||p_transaction_type||','||p_transaction_source||','||p_transaction_id||','||p_extract_flag);
176 END IF;
177
178 -- Standard call to check for call compatibility.
179 IF NOT FND_API.Compatible_API_Call ( l_api_version, p_api_version ,
180 l_api_name , G_PKG_NAME )
181 THEN
182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
183 END IF;
184 -- Check p_init_msg_list
185 IF FND_API.to_Boolean( p_init_msg_list ) THEN
186 FND_MSG_PUB.initialize;
187 END IF;
188 -- Initialize API return status to success
189 x_return_status := FND_API.G_RET_STS_SUCCESS;
190
191 -- Handle Organization Name / Organization Id
192 IF p_organization_name IS NULL THEN
193 RAISE FND_API.G_EXC_ERROR;
194 ELSE
195 BEGIN
196 SELECT organization_id
197 INTO l_organization_id
198 FROM org_organization_definitions
199 WHERE organization_name = p_organization_name;
200 EXCEPTION
201 WHEN OTHERS THEN
202 fnd_message.set_name('INV', 'INV_INT_ORGCODE');
203 fnd_msg_pub.ADD;
204 RAISE FND_API.G_EXC_ERROR;
205 END;
206 END IF;
207
208 IF (g_debug = 1) THEN
209 print_debug('l_org_id = '||l_organization_id);
210 END IF;
211
212 -- Handle Item Number / Item Id
213 IF p_inventory_item IS NULL THEN
214 l_inventory_item_id := NULL;
215 ELSE
216 BEGIN
217 SELECT inventory_item_id
218 INTO l_inventory_item_id
219 FROM mtl_system_items_kfv
220 WHERE organization_id = l_organization_id
221 AND concatenated_segments = p_inventory_item;
222 EXCEPTION
223 WHEN OTHERS THEN
224 fnd_message.set_name('INV', 'INV_INT_ITMCODE');
225 fnd_msg_pub.ADD;
226 RAISE FND_API.G_EXC_ERROR;
227 END;
228 END IF;
229
230 IF (g_debug = 1) THEN
231 print_debug('l_inventory_item_id: '||l_inventory_item_id);
232 END IF;
233
234 -- Handle Transaction Type
235 IF p_transaction_type IS NULL THEN
236 l_transaction_type_id := NULL;
237 ELSE
238 BEGIN
239 SELECT transaction_type_id
240 INTO l_transaction_type_id
241 FROM mtl_transaction_types
242 WHERE transaction_type_name = p_transaction_type;
243 EXCEPTION
244 WHEN OTHERS THEN
245 fnd_message.set_name('INV', 'INV_INT_TRXTYPCODE');
246 fnd_msg_pub.ADD;
247 RAISE FND_API.G_EXC_ERROR;
248 END;
249 END IF;
250
251 IF (g_debug = 1) THEN
252 print_debug('l_transaction_type_id: '||l_transaction_type_id);
253 print_debug('p_extract_flag: '||p_extract_flag);
254 END IF;
255
256
257 sync_adjustment_transactions2(
258 p_api_version => 1.0
259 , x_return_status => x_return_status
260 , x_msg_count => x_msg_count
261 , x_msg_data => x_msg_data
262 , p_from_date => p_from_date
263 , p_to_date => p_to_date
264 , p_organization_id => l_organization_id
265 , p_category_name => p_category_name
266 , p_inventory_item_id => l_inventory_item_id
267 , p_transaction_type_id => l_transaction_type_id
268 , p_transaction_source => p_transaction_source
269 , p_transaction_id => p_transaction_id
270 , p_extract_flag => p_extract_flag
271 );
272
273 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
274 IF (g_debug = 1) THEN
275 print_debug('return status : '|| x_return_status || ' msg_count : ' || x_msg_count);
276 print_debug('error : '|| x_msg_data);
277 END IF;
278 RAISE FND_API.G_EXC_ERROR;
279 END IF;
280
281 IF FND_API.To_Boolean( p_commit ) THEN
282 COMMIT WORK;
283 END IF;
284
285 RETURN x_return_status;
286
287 EXCEPTION
288 WHEN FND_API.G_EXC_ERROR THEN
289 x_return_status := FND_API.G_RET_STS_ERROR ;
290 FND_MSG_PUB.Count_And_Get
291 ( p_count => x_msg_count ,
292 p_data => x_msg_data
293 );
294 RETURN 'E';
295
296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
298 FND_MSG_PUB.Count_And_Get
299 ( p_count => x_msg_count ,
300 p_data => x_msg_data
301 );
302 RETURN 'U';
303 WHEN OTHERS THEN
304 print_debug('In exception');
305 RETURN 'U';
306
307 END sync_adjustment_transactions;
308
309 PROCEDURE sync_adjustment_transactions2(
310 p_api_version IN NUMBER
311 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
312 , p_commit IN VARCHAR2 := FND_API.G_FALSE
313 , x_return_status OUT NOCOPY VARCHAR2
314 , x_msg_count OUT NOCOPY NUMBER
315 , x_msg_data OUT NOCOPY VARCHAR2
316 , p_from_date IN DATE
317 , p_to_date IN DATE
318 , p_organization_id IN NUMBER
319 , p_category_name IN VARCHAR2
320 , p_inventory_item_id IN NUMBER DEFAULT NULL
321 , p_transaction_type_id IN NUMBER DEFAULT NULL
322 , p_transaction_source IN VARCHAR2 DEFAULT NULL
323 , p_transaction_id IN NUMBER DEFAULT NULL
324 , p_extract_flag IN VARCHAR2
325 ) AS
326
327 l_api_version CONSTANT NUMBER := 1.0;
328 l_api_name CONSTANT VARCHAR2(30) := 'sync_adjustment_transactions2';
329
330 err_msg VARCHAR2(100) := NULL;
331 ret_cursor INV_STANDALONE_SYNC_PUB.t_genref := NULL;
332
333 l_from_date DATE;
334 l_to_date DATE;
335 l_organization_id NUMBER;
336 l_category_name VARCHAR2(240);
337 l_inventory_item_id NUMBER;
338 l_transaction_source VARCHAR2(240);
339 l_transaction_type_id NUMBER;
340 l_transaction_id NUMBER;
341 l_extract_flag VARCHAR2(1);
342
343 l_txn_detail adj_txn_tbl_type;
344
345 stmt VARCHAR2(400);
346 -- Bug 8784314
347 t_txn_id txn_id_tbl_type := txn_id_tbl_type();
348
349 BEGIN
350
351 print_debug('sync_adjustment_transactions(id) Entered');
352
353 IF (g_debug = 1) THEN
354 print_debug('param = ' ||p_from_date||','||p_to_date||','||p_organization_id||','||p_category_name||','
355 ||p_inventory_item_id||','||p_transaction_type_id||','||p_transaction_source||','||p_transaction_id||','||p_extract_flag);
356 END IF;
357
358 -- Standard begin of API savepoint
359 SAVEPOINT inv_sync_adj_txn1;
360 -- Standard call to check for call compatibility.
361 IF NOT FND_API.Compatible_API_Call ( l_api_version, p_api_version ,
362 l_api_name , G_PKG_NAME )
363 THEN
364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
365 END IF;
366 -- Check p_init_msg_list
367 IF FND_API.to_Boolean( p_init_msg_list ) THEN
368 FND_MSG_PUB.initialize;
369 END IF;
370 -- Initialize API return status to success
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372
373 l_from_date := p_from_date;
374 l_to_date := p_to_date;
375 l_organization_id := p_organization_id;
376 l_category_name := p_category_name;
377 l_inventory_item_id := p_inventory_item_id;
378 l_transaction_type_id := p_transaction_type_id;
379 l_transaction_source := p_transaction_source;
380 l_extract_flag := p_extract_flag;
381
382 IF l_organization_id IS NULL OR l_category_name IS NULL OR l_from_date IS NULL OR l_to_date IS NULL THEN
383 RAISE FND_API.G_EXC_ERROR;
384 END IF;
385
386 stmt := 'SELECT * '
387 || ' FROM MTL_ADJUSTMENT_TXN_SYNC_V '
388 || ' WHERE organization_id = :org '
389 || ' AND Category = :category '
390 || ' AND Creation_Date BETWEEN :fr_date AND :to_date ';
391
392
393 IF l_inventory_item_id IS NOT NULL
394 THEN
395 stmt := stmt || ' AND Inventory_item_id = :itemid ';
396 END IF;
397
398
399 IF l_transaction_type_id IS NOT NULL
400 THEN
401 stmt := stmt || ' AND transaction_type_id = :txntypeid ';
402 stmt := stmt || ' AND NVL(transaction_source,''@@@'') = NVL(:txnsource, ''@@@'') ';
403 END IF;
404
405 IF l_transaction_id IS NOT NULL
406 THEN
407 stmt := stmt || ' AND transaction_id = :txnid ';
408 END IF;
409
410
411 IF (g_debug = 1) THEN
412 print_debug('stmt = ' || stmt);
413 print_debug('bind = ' || l_organization_id ||','||l_category_name ||','||l_from_date||','||l_to_date||','
414 || l_inventory_item_id||','||l_transaction_type_id||','||l_transaction_source||','||l_transaction_id);
415 END IF;
416
417 IF l_inventory_item_id IS NULL
418 AND l_transaction_type_id IS NULL
419 AND l_transaction_id IS NULL
420 THEN
421 OPEN ret_cursor FOR stmt USING l_organization_id, l_category_name, l_from_date, l_to_date;
422
423 ELSIF l_inventory_item_id IS NOT NULL
424 AND l_transaction_type_id IS NULL
425 AND l_transaction_id IS NULL
426 THEN
427 OPEN ret_cursor FOR stmt USING l_organization_id, l_category_name, l_from_date, l_to_date, l_inventory_item_id;
428
429 ELSIF l_inventory_item_id IS NULL
430 AND l_transaction_type_id IS NOT NULL
431 AND l_transaction_id IS NULL
432 THEN
433 OPEN ret_cursor FOR stmt USING l_organization_id, l_category_name, l_from_date, l_to_date,
434 l_transaction_type_id, l_transaction_source;
435
436 ELSIF l_inventory_item_id IS NOT NULL
437 AND l_transaction_type_id IS NOT NULL
438 AND l_transaction_id IS NULL
439 THEN
440 OPEN ret_cursor FOR stmt USING l_organization_id, l_category_name, l_from_date, l_to_date,
441 l_inventory_item_id, l_transaction_type_id, l_transaction_source;
442 ELSIF l_inventory_item_id IS NULL
443 AND l_transaction_type_id IS NULL
444 AND l_transaction_id IS NOT NULL
445 THEN
446 OPEN ret_cursor FOR stmt USING l_organization_id, l_category_name, l_from_date, l_to_date, l_transaction_id;
447
448 ELSIF l_inventory_item_id IS NOT NULL
449 AND l_transaction_type_id IS NULL
450 AND l_transaction_id IS NOT NULL
451 THEN
452 OPEN ret_cursor FOR stmt USING l_organization_id, l_category_name, l_from_date, l_to_date, l_inventory_item_id, l_transaction_id;
453
454 ELSIF l_inventory_item_id IS NULL
455 AND l_transaction_type_id IS NOT NULL
456 AND l_transaction_id IS NOT NULL
457 THEN
458 OPEN ret_cursor FOR stmt USING l_organization_id, l_category_name, l_from_date, l_to_date,
459 l_transaction_type_id, l_transaction_source, l_transaction_id;
460
461 ELSIF l_inventory_item_id IS NOT NULL
462 AND l_transaction_type_id IS NOT NULL
463 AND l_transaction_id IS NOT NULL
464 THEN
465 OPEN ret_cursor FOR stmt USING l_organization_id, l_category_name, l_from_date, l_to_date,
466 l_inventory_item_id, l_transaction_type_id, l_transaction_source, l_transaction_id;
467 END IF;
468
469 FETCH ret_cursor BULK COLLECT INTO l_txn_detail;
470 CLOSE ret_cursor;
471
472 -- Bug 8784314, using scalar array instead of table of records for backward compatibility with 9i/10g..
473 t_txn_id.extend(l_txn_detail.last - l_txn_detail.first + 1);
474 FOR i IN l_txn_detail.first .. l_txn_detail.last LOOP
475 t_txn_id(i) := l_txn_detail(i).transaction_number;
476 END LOOP;
477
478 IF (g_debug = 1) THEN
479 print_debug('Updating MMT to set transaction_extracted = ' || l_extract_flag);
480 END IF;
481
482 -- Mark transactions with Synchronization Status.
483 -- Bug 8784314, replacing l_txn_detail(i).transaction_number by t_txn_id(i).
484
485 FORALL i IN l_txn_detail.first .. l_txn_detail.last
486 UPDATE mtl_material_transactions
487 SET transaction_extracted = l_extract_flag
488 WHERE transaction_id = t_txn_id(i);
489
490 IF FND_API.To_Boolean( p_commit ) THEN
491 COMMIT WORK;
492 END IF;
493
494 l_txn_detail.DELETE;
495 t_txn_id.DELETE;
496
497 EXCEPTION
498 WHEN FND_API.G_EXC_ERROR THEN
499 ROLLBACK TO inv_sync_adj_txn1;
500 x_return_status := FND_API.G_RET_STS_ERROR ;
501 FND_MSG_PUB.Count_And_Get
502 ( p_count => x_msg_count ,
503 p_data => x_msg_data
504 );
505 l_txn_detail.DELETE;
506
507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
508 ROLLBACK TO inv_sync_adj_txn1;
509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
510 FND_MSG_PUB.Count_And_Get
511 ( p_count => x_msg_count ,
512 p_data => x_msg_data
513 );
514 l_txn_detail.DELETE;
515
516 WHEN OTHERS THEN
517 ROLLBACK TO inv_sync_adj_txn1;
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
519 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
520 THEN
521 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
522 END IF;
523 FND_MSG_PUB.Count_And_Get
524 ( p_count => x_msg_count ,
525 p_data => x_msg_data
526 );
527 l_txn_detail.DELETE;
528
529 END sync_adjustment_transactions2;
530
531
532 END inv_standalone_sync_pub;