1 PACKAGE BODY OE_CONFIG_TSO_GRP AS
2 /* $Header: OEXGTSOB.pls 120.3 2005/10/27 17:25:16 akurella noship $ */
3
4 -------------------------------------------------------
5 -- Local Variables and Procedures
6 -------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_CONFIG_TSO_GRP';
8
9 PROCEDURE Print_Time (p_msg IN VARCHAR2);
10
11 PROCEDURE Print_Time (p_msg IN VARCHAR2)
12 IS
13 l_time VARCHAR2(100);
14 l_debug_level CONSTANT NUMBER := OE_DEBUG_PUB.G_DEBUG_LEVEL;
15 BEGIN
16 l_time := to_char(new_time(sysdate,'PST','EST'),'DD-MON-YY HH24:MI:SS');
17 IF l_debug_level > 0 THEN
18 OE_DEBUG_PUB.Add (p_msg||':'||l_time,1);
19 END IF;
20 END Print_Time;
21
22
23 -----------------------------------------------------
24 -- Group APIs section
25 -----------------------------------------------------
26
27 /* API: Get_MACD_Action_Mode
28 *
29 * Type: Group API, only for Oracle products internal use
30 *
31 * Input parameters: p_top_model_line_id and p_line_id both are NUMBER and
32 * indicate the line_id of the line for which we need the MACD_Action_Mode
33 *
34 * Output Parameters: x_config_mode NUMBER indicates the MACD action mode
35 * and x_return_status VARCHAR2 which indicates success/error
36 *
37 * Validation: Performs a check whether the line passed belongs to a
38 * container model by calling OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
39 * Only if it is part of a container model will it call the MACD_Action_Mode
40 * API. If it is not a part of container model, then it returns with the
41 * following values: x_config_mode = NULL and x_return_status = ERROR
42 *
43 * There are no error messages that we populate so it is upto the caller to
44 * examine the output parameters and take action if there is an error. (The
45 * reason we do not populate error messages is because the passed in line may
46 * not be part of a container model or the top model line may be passed in
47 * as child line etc. So the number of error messages required would be too
48 * many.)
49 *
50 * Assumption: line being passed is already in the in oe_order_lines_all table.
51 *
52 * Description of API internals:
53 * The Private API called by this procedure uses different SQLs depending
54 * on what is the input parameter
55 * It goes in order of p_top_model_line_id and p_line_id
56 *
57 * As mentioned above, the config mode would depend upon what is the input
58 * parameter.
59 *
60 * I. p_top_model_line_id (The API looks for this first)
61 *
62 * The API checks for a baseline revision in the CZ/OM tables and if it
63 * is not found, the config_mode is returned as 1 (new configuration).
64 * If a baseline revision is found in the CZ/OM tables, the config_mode is
65 * updated to 3. It then checks for a change made to line in configurator and
66 * if a delta is found, the config_mode is updated to 4 and returned. If no
67 * delta is found, the config_mode is retained at 3 and returned.
68 *
69 * II. p_line_id (If no top_model_line_id is passed, then API checks for this)
70 *
71 * The API checks for a baseline revision in the CZ/OM tables and if no
72 * baseline exists, config_mode is returned as 1 (new configuration).
73 * If a baseline revision for the line is found in CZ/OM tables, config_mode
74 * is set to 3. It then proceeds to check for a change made to line in
75 * configurator. If such a delta exists, the config_mode is set to 2 and
76 * returned. Else value of 3 is retained and returned.
77 *
78 * If the config_mode is returned as NULL, it is to be interpreted that there
79 * is some error (either the line is not part of container model or OTHERS
80 * exception in OE_CONFIG_TSO_PVT.Get_MACD_Action_Mode)
81 *
82 * IMPORTANT: If the correct mode is to be obtained, then a top model line
83 * must be passed ONLY into p_top_model_line_id (and not into p_line_id)
84 * Similarly, for a child line of a container model, the line_id should
85 * be passed ONLY into p_line_id (and not into p_top_model_line_id). Since SQL
86 * goes in order of top_model_line_id and then line_id, when a child line
87 * is being passed, the child line's line_id should be passed to p_line_id
88 * and the p_top_model_line_id should necessarily be NULL
89 *
90 */
91
92 PROCEDURE Get_MACD_Action_Mode
93 (
94 p_line_id IN NUMBER := NULL
95 ,p_top_model_line_id IN NUMBER := NULL
96 ,x_config_mode OUT NOCOPY NUMBER
97 ,x_return_status OUT NOCOPY VARCHAR2
98 )
99 IS
100 l_debug_level CONSTANT NUMBER := OE_DEBUG_PUB.G_DEBUG_LEVEL;
101 l_top_container_model VARCHAR2(1);
102 l_part_of_container VARCHAR2(1);
103 -- MOAC
104 l_org_id NUMBER;
105 l_current_access_mode VARCHAR2(1);
106 l_current_org_id NUMBER;
107 l_reset_policy BOOLEAN := FALSE;
108 BEGIN
109 Print_Time ('Entering OE_CONFIG_TSO_GRP.Get_MACD_Action_Mode..');
110 IF l_debug_level > 0 THEN
111 OE_DEBUG_PUB.Add('LineID:'||p_line_id,3);
112 OE_DEBUG_PUB.Add('TopModelLineID:'||p_top_model_line_id,3);
113 END IF;
114
115 x_return_status := FND_API.G_RET_STS_SUCCESS; --Nocopy changes
116
117 -- MOAC change
118 -- Check if org context has been set before doing any process
119 -- Set the context if not already or if the org on the line_id is different
120 -- than the org previously set.
121
122 l_current_access_mode := mo_global.Get_access_mode(); -- MOAC
123 l_current_org_id := mo_global.get_current_org_id();
124
125 BEGIN
126
127
128 SELECT org_id
129 INTO l_org_id
130 FROM oe_order_lines_all
131 WHERE line_id = p_line_id;
132
133
134 EXCEPTION
135
136 WHEN OTHERS THEN
137 IF l_debug_level > 0 THEN
138 OE_DEBUG_PUB.Add('Null org id' || sqlerrm,3);
139 END IF;
140 RAISE FND_API.G_EXC_ERROR;
141 END;
142
143 IF l_debug_level > 0 THEN
144 oe_debug_pub.add('SO Org Id: ' ||l_org_id , 1 ) ;
145 END IF;
146
147
148 IF nvl(l_current_org_id,-99) <> l_org_id THEN
149 Mo_Global.Set_Policy_Context (p_access_mode => 'S', p_org_id => l_org_id);
150 l_reset_policy := TRUE;
151 END IF;
152
153
154 OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
155 ( p_line_id => p_line_id
156 ,p_top_model_line_id => p_top_model_line_id
157 ,x_top_container_model => l_top_container_model
158 ,x_part_of_container => l_part_of_container
159 );
160
161 IF l_part_of_container = 'N' THEN
162 x_config_mode := NULL;
163 x_return_status := FND_API.G_RET_STS_ERROR;
164 IF l_debug_level > 0 THEN
168 END IF;
165 OE_DEBUG_PUB.Add('ERR: Only part of container models can use API',1);
166 OE_DEBUG_PUB.Add('Setting Return Status:'||x_return_status,1);
167 OE_DEBUG_PUB.Add('Config Mode:'||x_config_mode,2);
169 Print_Time ('Exiting OE_CONFIG_TSO_GRP.Get_MACD_Action_Mode..');
170 RAISE FND_API.G_EXC_ERROR;
171 END IF;
172
173 --following code is executed only if line is part of container model
174 IF l_debug_level > 0 THEN
175 OE_DEBUG_PUB.Add('L_PartOfContainer:'||l_part_of_container,3);
176 OE_DEBUG_PUB.Add('Eligible to call TSO_PVT.Get_MACD_Action_Mode',2);
177 END IF;
178 OE_CONFIG_TSO_PVT.Get_MACD_Action_Mode
179 ( p_line_id => p_line_id
180 ,p_top_model_line_id => p_top_model_line_id
181 ,x_config_mode => x_config_mode
182 ,x_return_status => x_return_status
183 );
184 IF l_debug_level > 0 THEN
185 OE_DEBUG_PUB.Add('Return Status:'||x_return_status,1);
186 OE_DEBUG_PUB.Add('Config Mode:'||x_config_mode,2);
187 END IF;
188
189 IF l_reset_policy THEN -- MOAC
190 Mo_Global.Set_Policy_Context (p_access_mode => l_current_access_mode, p_org_id => l_current_org_id);
191 END IF;
192 Print_Time ('Exiting OE_CONFIG_TSO_GRP.Get_MACD_Action_Mode..');
193
194 EXCEPTION
195
196 WHEN FND_API.G_EXC_ERROR THEN
197
198 IF l_reset_policy THEN -- MOAC
199 Mo_Global.Set_Policy_Context (p_access_mode => l_current_access_mode, p_org_id => l_current_org_id);
200 END IF;
201 x_return_status := FND_API.G_RET_STS_ERROR;
202 x_config_mode := NULL;
203 IF l_debug_level > 0 THEN
204 OE_DEBUG_PUB.Add('Expected Exception in TSO_GRP.Get_MACD_Action_Mode'
205 ||sqlerrm,1);
206 END IF;
207
208
209 WHEN OTHERS THEN
210 IF l_reset_policy THEN -- MOAC
211 Mo_Global.Set_Policy_Context (p_access_mode => l_current_access_mode, p_org_id => l_current_org_id);
212 END IF;
213 x_config_mode := NULL;
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
215 IF l_debug_level > 0 THEN
216 OE_DEBUG_PUB.Add('Other Exception in TSO_GRP.Get_MACD_Action_Mode'
217 ||sqlerrm,1);
218 END IF;
219 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
220 OE_MSG_PUB.Add_Exc_Msg
221 ( G_PKG_NAME
222 ,'Get_MACD_Action_Mode' );
223 END IF;
224
225 END Get_MACD_Action_Mode;
226
227 Procedure Process_MACD_Order
228 (P_api_version_number IN NUMBER,
229 P_sold_to_org_id IN NUMBER,
230 P_x_header_id IN OUT NOCOPY NUMBER,
231 P_MACD_Action IN VARCHAR2,
232 P_Instance_Tbl IN csi_datastructures_pub.instance_cz_tbl,
233 P_Extended_Attrib_Tbl IN csi_datastructures_pub.ext_attrib_values_tbl,
234 X_container_line_id OUT NOCOPY NUMBER,
235 X_number_of_containers OUT NOCOPY NUMBER,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY VARCHAR2,
238 x_msg_data OUT NOCOPY VARCHAR2)
239 IS
240 l_debug_level CONSTANT NUMBER := OE_DEBUG_PUB.G_DEBUG_LEVEL;
241 l_line_tbl OE_ORDER_PUB.line_tbl_type;
242 l_number_of_containers NUMBER;
243 l_org_id NUMBER;
244 BEGIN
245 Print_Time ('Entering OE_CONFIG_TSO_GRP.Process_MACD_Order..');
246
247
248 -- Main Logic
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250
251 -- MOAC change
252 -- Check if org context has been set before doing any process
253 -- If there is no org context set, we stop calling group process order API
254 -- and raise an error though we don't do any validation for the org_id.
255
256 l_org_id := MO_GLOBAL.get_current_org_id;
257 IF (l_org_id IS NULL OR l_org_id = FND_API.G_MISS_NUM) THEN
258
259 IF l_debug_level > 0 THEN
260 OE_DEBUG_PUB.Add('Null org id',3);
261 END IF;
262
263 FND_MESSAGE.set_name('FND','MO_ORG_REQUIRED');
264 OE_MSG_PUB.Add;
265 RAISE FND_API.G_EXC_ERROR;
266 END IF;
267
268 IF l_debug_level > 0 THEN
269 OE_DEBUG_PUB.Add('Return Status:'||x_return_status,1);
270 OE_DEBUG_PUB.Add('Org Id:' || l_org_id,1);
271 END IF;
272
273 Oe_config_tso_pvt.Process_MACD_Order
274 (P_API_VERSION_NUMBER => P_API_VERSION_NUMBER,
275 P_caller => 'G', -- Group
276 P_sold_to_org_id => p_sold_to_org_id,
277 P_x_header_id => p_x_header_id,
278 P_MACD_Action => p_macd_action,
279 P_Instance_Tbl => p_instance_tbl,
280 P_x_Line_Tbl => l_line_tbl,
281 P_Extended_Attrib_Tbl => P_Extended_Attrib_Tbl,
282 X_container_line_id => x_container_line_id,
283 X_number_of_containers => X_number_of_containers,
284 X_return_status => x_return_status,
285 X_msg_count => x_msg_count,
286 X_msg_data => x_msg_data);
287
288 Print_Time ('Exiting OE_CONFIG_TSO_GRP.Process_MACD_Order..');
289
290 EXCEPTION
291
292 WHEN FND_API.G_EXC_ERROR THEN
293
294 x_return_status := FND_API.G_RET_STS_ERROR;
295
296 -- Get message count and data
297
298 OE_MSG_PUB.Count_And_Get
299 ( p_count => x_msg_count
300 , p_data => x_msg_data
301 );
302
303 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
304
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
306
307 -- Get message count and data
308
309 OE_MSG_PUB.Count_And_Get
310 ( p_count => x_msg_count
311 , p_data => x_msg_data
312 );
313 WHEN OTHERS THEN
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315 IF l_debug_level > 0 THEN
316 OE_DEBUG_PUB.Add('Other Exception in TSO_GRP.Process_MACD_Order'
317 ||sqlerrm,1);
318 END IF;
319 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
320 OE_MSG_PUB.Add_Exc_Msg
321 ( G_PKG_NAME
322 ,'Process_MACD_Order' );
323 END IF;
324
325 END Process_MACD_Order;
326
327 END OE_CONFIG_TSO_GRP;