1 PACKAGE BODY INV_TXNSTUB_PUB AS
2 /* $Header: INVTPUBB.pls 120.5 2006/06/23 06:55:34 pannapra noship $ */
3 /**
4 * p_header_id = TRANSACTION_HEADER_ID
5 * p_transaction_id = TRANSACTION_ID
6 * x_return_status = FND_API.G_RET_STS_*;
7 * in case of an error, the error should be put onto the message stake
8 * using fnd_message.set_name and fnd_msg_pub.add functions or similar
9 * functions in those packages. The caller would then retrieve the
10 * messages. If the return status is a normal (predicted) error or
11 * an unexpected error, then the transaction is aborted.
12 */
13
14 /** Global added to cache the value of fnd_installation for
15 * perfromance bug 3176229*/
16
17 g_fnd_install_status VARCHAR2(10) := NULL;
18
19 /*Bug#5349268. Modified the below procedure not to return with error when
20 'NO_DATA_FOUND' exception is raised during the execution of the query which
21 finds if the item is installbase trackable. In such case, the procedure does
22 not call 'CSI_INV_TXN_HOOK_PKG.postTransaction' but returns with a return
23 status of 'Success'. */
24 PROCEDURE postTransaction(p_header_id IN NUMBER,
25 p_transaction_id IN NUMBER,
26 x_return_status OUT NOCOPY VARCHAR2)
27 IS
28 l_install_base_id number := 542 ; -- ApplicationId of Oracle-Install-Base
29 l_status varchar2(10);
30 l_industry varchar2(10);
31 l_return_val boolean := FALSE;
32 --l_plsql_blk varchar2(2000);
33 l_ret_status varchar2(255);
34 l_debug number;--Bug#5194809
35 l_ib_trackable varchar2(1); --Bug#5208421 -Part 2
36 BEGIN
37 -- Check if Oracle-Install-Base is installed in this instance
38 -- If Install-Base is installed then call the stub of Install-Base,
39 -- otherwise return success
40
41 l_debug := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0); --Bug#5194809
42
43 IF (l_debug = 1) THEN
44 inv_log_util.trace('Entered INV_TXNSTUB_PUB.postTransaction', 'INV_TXNSTUB_PUB', 9);
45 END IF;
46
47
48 l_ib_trackable := 'N'; --Bug#5349268
49
50 IF ( g_fnd_install_status IS NULL) THEN
51
52 l_return_val := fnd_installation.get(
53 appl_id => l_install_base_id,
54 dep_appl_id => l_install_base_id,
55 status => l_status,
56 industry => l_industry );
57 g_fnd_install_status :=l_status;--set the global
58 END IF;
59
60
61 IF (g_fnd_install_status = 'I') THEN
62 /*Bug#5208421 -Part 2. Added the following query, to find if the item is installbase
63 trackable*/
64 BEGIN
65 SELECT NVL(comms_nl_trackable_flag,'N') into l_ib_trackable
66 FROM mtl_system_items a, mtl_material_transactions b
67 WHERE a.organization_id = b.organization_id
68 AND a.inventory_item_id = b.inventory_item_id
69 AND Nvl(b.inventory_item_id,-1) <> -1
70 AND enabled_flag = 'Y'
71 AND Nvl(start_date_active, sysdate) <= sysdate
72 AND Nvl(end_date_active, sysdate+1) > sysdate
73 AND b.transaction_id = p_transaction_id;
74
75 IF (l_debug = 1) THEN
76 inv_log_util.trace('InstallBase Trackable: '||l_ib_trackable, 'INV_TXNSTUB_PUB', 9);
77 END IF;
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 IF (l_debug = 1) THEN
81 inv_log_util.trace('No Data Found Exception raised', 'INV_TXNSTUB_PUB', 9);
82 END IF;
83 l_ib_trackable := 'N';
84 WHEN OTHERS THEN
85 IF (l_debug = 1) THEN
86 inv_log_util.trace('Exception while finding the InstallBase Trackable flag '||sqlerrm, 'INV_TXNSTUB_PUB', 9);
87 END IF;
88 x_return_status := FND_API.G_RET_STS_ERROR;
89 return;
90 END;
91 END IF;
92
93 if (g_fnd_install_status = 'I' AND l_ib_trackable = 'Y' ) then --Bug#5208421 -Part 2
94 /*Bug#5194809. Changed the call of 'CSI_INV_TXN_HOOK_PKG.postTransaction'
95 from dyanamic style to static style. */
96 IF (l_debug = 1) THEN
97 inv_log_util.trace('Calling CSI_INV_TXN_HOOK_PKG.postTransaction', 'INV_TXNSTUB_PUB', 9);
98 END IF;
99 CSI_INV_TXN_HOOK_PKG.postTransaction( p_header_id => p_header_id
100 , p_transaction_id => p_transaction_id
101 , x_return_status => l_ret_status );
102 IF (l_debug = 1) THEN
103 inv_log_util.trace('Return Status from CSI_INV_TXN_HOOK_PKG.postTransaction: '||l_ret_status, 'INV_TXNSTUB_PUB', 9);
104 END IF;
105
106 x_return_status := l_ret_status;
107 else
108 IF (l_debug = 1) THEN
109 inv_log_util.trace('g_fnd_install_status is not I or item is not installbase trackable' , 'INV_TXNSTUB_PUB', 9);
110 END IF;
111 x_return_status := FND_API.G_RET_STS_SUCCESS;
112 end if;
113 END;
114 END INV_TXNSTUB_PUB;