1 PACKAGE BODY INV_DS_LOGICAL_TRX_INFO_PUB AS
2 /* $Header: INVLTIPB.pls 115.4 2004/05/12 18:45:01 vipartha noship $ */
3
4 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5
6 PROCEDURE print_debug
7 (
8 p_err_msg IN VARCHAR2,
9 p_level IN NUMBER := 9
10 ) IS
11 BEGIN
12 INV_LOG_UTIL.trace
13 (p_message => p_err_msg,
14 p_module => 'INV_DS_LOGICAL_TRX_INFO_PUB',
15 p_level => p_level);
16 END print_debug;
17
18
19
20 /*==========================================================================*
21 | Procedure : GET_LOGICAL_ATTR_VALUES |
22 | |
23 | Description : This API will be called by Install base for a |
24 | specific transafction to get all the attributes tied to |
25 | a drop shipment so that they can update the inventory |
26 | accordingly |
27 | |
28 | |
29 | Input Parameters : |
30 | p_api_version_number - API version number |
31 | p_init_msg_lst - Whether initialize the error message list or not|
32 | Should be fnd_api.g_false or fnd_api.g_true |
33 | p_transaction_id - transaction id of the inserted SO issue MMT |
34 | record. |
35 | Output Parameters : |
36 | x_return_status - fnd_api.g_ret_sts_success, if succeeded |
37 | fnd_api.g_ret_sts_exc_error, if an expected |
38 | error occurred |
39 | fnd_api.g_ret_sts_unexp_error, if an unexpected |
40 | eror occurred |
41 | x_msg_count - Number of error message in the error message |
42 | list |
43 | x_msg_data - If the number of error message in the error |
44 | message list is one, the error message is in |
45 | this output parameter |
46 | x_logical_trx_attr_values - returns a record type with all the attributes|
47 | for a logical transaction. |
48 *==========================================================================*/
49
50 PROCEDURE GET_LOGICAL_ATTR_VALUES
51 (
52 x_return_status OUT NOCOPY VARCHAR2
53 , x_msg_count OUT NOCOPY NUMBER
54 , x_msg_data OUT nocopy VARCHAR2
55 , x_logical_trx_attr_values OUT NOCOPY INV_DROPSHIP_GLOBALS.logical_trx_attr_tbl
56 , p_api_version_number IN NUMBER := 1.0
57 , p_init_msg_lst IN VARCHAR2 := G_FALSE
58 , p_transaction_id IN NUMBER
59 )
60 IS
61 --Bug 3620584: Changed the SQL to improve performance. Removed the OR
62 -- condition and used a UNION in its place.
63
64 CURSOR logical_transactions(l_txn_id NUMBER)
65 IS
66 SELECT transaction_id,transaction_type_id,
67 transaction_source_type_id,transaction_action_id,
68 parent_transaction_id,logical_trx_type_code,
69 intercompany_cost,intercompany_pricing_option,
70 trx_flow_header_id,logical_transactions_created,
71 logical_transaction,intercompany_currency_code
72
73 FROM MTL_MATERIAL_TRANSACTIONS
74 WHERE TRANSACTION_ACTION_ID NOT IN (24,30) AND
75 (transaction_id = p_transaction_id)
76
77 UNION
78
79 SELECT transaction_id,transaction_type_id,
80 transaction_source_type_id,transaction_action_id,
81 parent_transaction_id,logical_trx_type_code,
82 intercompany_cost,intercompany_pricing_option,
83 trx_flow_header_id,logical_transactions_created,
84 logical_transaction,intercompany_currency_code
85 FROM MTL_MATERIAL_TRANSACTIONS
86 WHERE TRANSACTION_ACTION_ID NOT IN (24,30) AND
87 ( PARENT_TRANSACTION_ID IN
88 (SELECT PARENT_TRANSACTION_ID FROM MTL_MATERIAL_TRANSACTIONS
89 WHERE TRANSACTION_ACTION_ID NOT IN (24,30) AND
90 (transaction_id = p_transaction_id)) AND
91 PARENT_TRANSACTION_ID IS NOT NULL);
92
93 l_index NUMBER := 0;
94
95
96
97 BEGIN
98
99 IF (l_debug = 1) THEN
100 print_debug('enter get logical attr values', 9);
101 print_debug('p_transaction_id = ' || p_transaction_id, 9);
102 END IF;
103
104
105 FOR l_logical_transactions IN logical_transactions(p_transaction_id)
106
107 LOOP
108
109 l_index := l_index + 1;
110
111 x_logical_trx_attr_values(l_index).transaction_id :=
112 l_logical_transactions.transaction_id;
113 x_logical_trx_attr_values(l_index).transaction_type_id :=
114 l_logical_transactions.transaction_type_id;
115 x_logical_trx_attr_values(l_index).transaction_source_type_id :=
116 l_logical_transactions.transaction_source_type_id;
117 x_logical_trx_attr_values(l_index).transaction_action_id :=
118 l_logical_transactions.transaction_action_id;
119 x_logical_trx_attr_values(l_index).parent_transaction_id :=
120 l_logical_transactions.parent_transaction_id;
121 x_logical_trx_attr_values(l_index).logical_trx_type_code :=
122 l_logical_transactions.logical_trx_type_code;
123 x_logical_trx_attr_values(l_index).intercompany_cost :=
124 l_logical_transactions.intercompany_cost;
125 x_logical_trx_attr_values(l_index).intercompany_pricing_option
126 := l_logical_transactions.intercompany_pricing_option;
127 x_logical_trx_attr_values(l_index).trx_flow_header_id :=
128 l_logical_transactions.trx_flow_header_id;
129 x_logical_trx_attr_values(l_index).logical_transactions_created
130 := l_logical_transactions.logical_transactions_created;
131 x_logical_trx_attr_values(l_index).logical_transaction :=
132 l_logical_transactions.logical_transaction;
133 x_logical_trx_attr_values(l_index).intercompany_currency_code :=
134 l_logical_transactions.intercompany_currency_code;
135
136 IF (l_debug = 1) THEN
137 print_debug('l_transaction_id' || p_transaction_id, 9);
138 END IF;
139
140 END LOOP;
141
142 x_return_status := g_ret_sts_success;
143
144 EXCEPTION
145 WHEN FND_API.G_EXC_ERROR THEN
146 x_return_status := G_RET_STS_ERROR;
147 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
148 IF (l_debug = 1) THEN
149 print_debug('INV_DS_LOGICAL_TRX_INFO_PUB: no_data_found error', 9);
150 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
151 END IF;
152 WHEN OTHERS THEN
153 x_return_status := G_RET_STS_UNEXP_ERROR;
154
155 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
156 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'INV_DS_LOGICAL_TRX_INFO_PUB');
157 END IF;
158 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
159 IF (l_debug = 1) THEN
160 print_debug('INV_DS_LOGICAL_TRX_INFO_PUB: others error', 9);
161 print_debug('SQL Error: ' || Sqlerrm(SQLCODE), 9);
162 END IF;
163
164 END get_logical_attr_values;
165
166 END INV_DS_LOGICAL_TRX_INFO_PUB;
167
[Home] [Help]