1 PACKAGE BODY CZ_IB_TSO_GRP AS
2 /* $Header: czibtsob.pls 120.7 2007/02/09 19:40:26 skudryav ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(255) := 'CZ_IB_TSO_GRP';
5
6 --
7 -- section for a different DEBUG procedures
8 --
9 PROCEDURE DEBUG(p_str IN VARCHAR2) IS
10 BEGIN
11 --DBMS_OUTPUT.PUT_LINE(p_str);
12 NULL;
13 END DEBUG;
14
15 PROCEDURE DEBUG(p_var_name IN VARCHAR2, p_var_value IN VARCHAR2) IS
16 BEGIN
17 DEBUG(p_var_name || ' = ' || p_var_value);
18 END DEBUG;
19
20 PROCEDURE DEBUG(p_var_name IN VARCHAR2, p_var_value IN NUMBER) IS
21 BEGIN
22 DEBUG(p_var_name || ' = ' || TO_CHAR(p_var_value));
23 END DEBUG;
24
25 PROCEDURE DEBUG(p_var_name IN VARCHAR2, p_var_value IN DATE) IS
26 BEGIN
27 DEBUG(p_var_name || ' = ' ||
28 TO_CHAR(p_var_value, 'DD-MM-YYYY HH24:MI:SS'));
29 END DEBUG;
30
31 PROCEDURE dump_Error_Stack(p_prefix IN VARCHAR2) IS
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(4000);
34 l_msg_index number;
35 BEGIN
36 DEBUG('------------ Start of '||p_prefix||' ----------------');
37 l_msg_index := 1;
38 l_msg_count := fnd_msg_pub.COUNT_MSG();
39 DEBUG(p_prefix||' '||TO_CHAR(l_msg_count)||' error messages .');
40 WHILE l_msg_count > 0 LOOP
41 l_msg_data := fnd_msg_pub.GET(l_msg_index,fnd_api.g_false);
42 DEBUG(p_prefix||l_msg_data);
43 l_msg_index := l_msg_index + 1;
44 l_msg_count := l_msg_count - 1;
45 END LOOP;
46 DEBUG('------------ End of '||p_prefix||' ----------------');
47 END dump_Error_Stack;
48
49 --
50 -- Removes returned config item by marking it with a special flags returned_flag
51 --
52
53 /* # Removes returned config item by marking it with a special flags returned_flag
54 * @param p_instance_hdr_id Identifies instance_hdr_id OF returned config item
55 * @param p_instance_rev_nbr Identifies instance_rev_nbr OF returned config item
56 * @param p_returned_config_item Identifies config_item_id OF returned config item
57 * @param p_locked_instance_rev_nbr Identifies locked revision OF returned config item
58 * IF it IS NULL THEN this means that config item was NOT locked ( no pending orders WITH this item )
59 * @param p_application_id - application Id OF caller ( IF NULL THEN by defualt it's 542(IB))
60 * @p_config_eff_date - configuration effectivity date ( if NULL then by default it's SYSDATE )
61 * @param x_validation_status Returns either fnd_api.g_true IF configuration IS valid
62 * OR fnd_api.g_false IF configuration IS NOT valid
63 * @param x_return_status Returns one of three values :
64 * FND_API.G_RET_STS_SUCCESS IF no errors occurred
65 * FND_API.G_RET_STS_ERROR IF AT LEAST one error occurred
66 * FND_API.G_RET_STS_UNEXP_ERROR IF AT LEAST one unexpected error occurred
67 * @param x_msg_count Indicates how many messages exist ON ERROR_HANDLER
68 * message stack upon completion OF processing.
69 * @param x_msg_data IF exactly one message EXISTS ON ERROR_HANDLER
70 * message stack upon completion OF processing, this parameter contains
71 * that message.
72 *
73 * @rep:scope PUBLIC
74 * @rep:lifecycle active
75 * @rep:displayname Remove Returned Config Item
76 */
77 PROCEDURE remove_Returned_Config_Item
78 (
79 p_instance_hdr_id IN NUMBER,
80 p_instance_rev_nbr IN NUMBER,
81 p_returned_config_item_id IN NUMBER,
82 p_locked_instance_rev_nbr IN NUMBER,
83 p_application_id IN NUMBER,
84 p_config_eff_date IN DATE,
85 x_validation_status OUT NOCOPY VARCHAR2,
86 x_return_status OUT NOCOPY VARCHAR2,
87 x_msg_count OUT NOCOPY NUMBER,
88 x_msg_data OUT NOCOPY VARCHAR2
89 ) IS
90
91 -- procedure sets autonomous transaction
92 --
93 PRAGMA AUTONOMOUS_TRANSACTION;
94
95 l_config_tbl CZ_API_PUB.config_tbl_type;
96 l_appl_param_rec CZ_API_PUB.appl_param_rec_type;
97 l_config_model_tbl CZ_API_PUB.config_model_tbl_type;
98 l_config_status CZ_CONFIG_HDRS.config_status%TYPE;
99
100 l_locked_item_returned_flag VARCHAR2(1) := '0';
101 l_returned_flag VARCHAR2(1) := '0';
102 l_tangible_item_flag VARCHAR2(1);
103 l_return_status VARCHAR2(50);
104 l_msg_data VARCHAR2(4000);
105 l_config_hdr_id NUMBER;
106 l_config_rev_nbr NUMBER;
107 l_msg_count NUMBER;
108 l_ndebug NUMBER;
109 l_api_name VARCHAR2(255) := 'remove_Returned_Config_Item';
110 CZ_ITEM_IS_NOT_TANGIBLE EXCEPTION;
111
112 BEGIN
113
114 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
115 l_ndebug := 0;
116 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
117 'List of parameters : ',
118 fnd_log.LEVEL_PROCEDURE);
119
120 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
121 'p_instance_hdr_id='||TO_CHAR(p_instance_hdr_id),
122 fnd_log.LEVEL_PROCEDURE);
123
124 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
125 'p_instance_rev_nbr ='||TO_CHAR(p_instance_rev_nbr ),
126 fnd_log.LEVEL_PROCEDURE);
127
128 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
129 'p_returned_config_item_id='||TO_CHAR( p_returned_config_item_id),
130 fnd_log.LEVEL_PROCEDURE);
131
132 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
133 'p_locked_instance_rev_nbr='||TO_CHAR(p_locked_instance_rev_nbr),
134 fnd_log.LEVEL_PROCEDURE);
135
136 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
137 'p_application_id='||TO_CHAR(p_application_id),
138 fnd_log.LEVEL_PROCEDURE);
139
140 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
141 'p_config_eff_date='||TO_CHAR(p_config_eff_date,'DD-MM-YYYY HH24:MI'),
142 fnd_log.LEVEL_PROCEDURE);
143
144 END IF;
145
146 --
147 -- initialize OUT variables
148 --
149 x_return_status := FND_API.G_RET_STS_SUCCESS;
150 x_msg_count := 0;
151 x_msg_data := NULL;
152
153 --
154 -- find returned_flag of returned config item
155 -- and validate parameters p_instance_hdr_id/ p_instance_rev_nbr/ p_returned_config_item_id
156 --
157 BEGIN
158 SELECT config_hdr_id,config_rev_nbr,NVL(returned_flag, '0'), NVL(tangible_item_flag,'0')
159 INTO l_config_hdr_id,l_config_rev_nbr,l_returned_flag, l_tangible_item_flag
160 FROM CZ_CONFIG_ITEMS
161 WHERE instance_hdr_id=p_instance_hdr_id AND
162 instance_rev_nbr=p_instance_rev_nbr AND
163 config_item_id=p_returned_config_item_id AND
164 deleted_flag='0';
165
166 IF l_tangible_item_flag='0' THEN
167
168 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name, 'Config item instance_hdr_id/instance_rev_nbr/config_item_id = '||
169 to_char(p_instance_hdr_id)||'/'||to_char(p_instance_rev_nbr)||'/'||to_char(p_returned_config_item_id)||
170 ' is not tangible.');
171
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 fnd_msg_pub.count_and_get(p_count => x_msg_count,
174 p_data => x_msg_data);
175
176 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
177 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
178 'Config item instance_hdr_id/instance_rev_nbr/config_item_id = '||
179 to_char(p_instance_hdr_id)||'/'||to_char(p_instance_rev_nbr)||'/'||to_char(p_returned_config_item_id)||
180 ' is not tangible.',
181 fnd_log.LEVEL_ERROR);
182 END IF;
183
184 x_msg_data := 'Config item instance_hdr_id/instance_rev_nbr/config_item_id = '||
185 to_char(p_instance_hdr_id)||'/'||to_char(p_instance_rev_nbr)||'/'||to_char(p_returned_config_item_id)||
186 ' is not tangible.';
187
188 RETURN;
189
190 END IF;
191
192 EXCEPTION
193 WHEN NO_DATA_FOUND THEN
194 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name, 'Config item instance_hdr_id/instance_rev_nbr/config_item_id = '||
195 to_char(p_instance_hdr_id)||'/'||to_char(p_instance_rev_nbr)||'/'||to_char(p_returned_config_item_id)||
196 ' does not exist.');
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 fnd_msg_pub.count_and_get(p_count => x_msg_count,
199 p_data => x_msg_data);
200
201 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
202 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
203 'Config item instance_hdr_id/instance_rev_nbr/config_item_id = '||
204 to_char(p_instance_hdr_id)||'/'||to_char(p_instance_rev_nbr)||'/'||to_char(p_returned_config_item_id)||
205 ' does not exist.',
206 fnd_log.LEVEL_ERROR);
207 END IF;
208
209 x_msg_data := 'Config item instance_hdr_id/instance_rev_nbr/config_item_id = '||
210 to_char(p_instance_hdr_id)||'/'||to_char(p_instance_rev_nbr)||'/'||to_char(p_returned_config_item_id)||
211 ' does not exist.';
212
213 RETURN;
214
215 END;
216
217 --
218 -- find returned_flag of locked config item
219 -- and validate parameters p_instance_hdr_id/ p_locked_instance_rev_nbr/ p_returned_config_item_id
220 --
221 IF p_locked_instance_rev_nbr IS NOT NULL OR p_locked_instance_rev_nbr<>0 THEN
222 BEGIN
223 SELECT NVL(returned_flag, '0') INTO l_locked_item_returned_flag FROM CZ_CONFIG_ITEMS
224 WHERE instance_hdr_id=p_instance_hdr_id AND
225 instance_rev_nbr=p_locked_instance_rev_nbr AND
226 config_item_id=p_returned_config_item_id AND
227 deleted_flag='0';
228 EXCEPTION
229 WHEN NO_DATA_FOUND THEN
230 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name, 'Config item instance_hdr_id/instance_rev_nbr/config_item_id = '||
231 to_char(p_instance_hdr_id)||'/'||to_char(p_locked_instance_rev_nbr)||'/'||to_char(p_returned_config_item_id)||
232 ' does not exist.');
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 fnd_msg_pub.count_and_get(p_count => x_msg_count,
235 p_data => x_msg_data);
236
237 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
238 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
239 'Locked config item instance_hdr_id/instance_rev_nbr/config_item_id = '||
240 to_char(p_instance_hdr_id)||'/'||to_char(p_locked_instance_rev_nbr)||'/'||to_char(p_returned_config_item_id)||
241 ' does not exist.',
242 fnd_log.LEVEL_ERROR);
243 END IF;
244
245 x_msg_data := 'Locked config item instance_hdr_id/instance_rev_nbr/config_item_id = '||
246 to_char(p_instance_hdr_id)||'/'||to_char(p_locked_instance_rev_nbr)||'/'||to_char(p_returned_config_item_id)||
247 ' does not exist.';
248
249 RETURN;
250
251 END;
252 END IF;
253
254 -- if item was not processed before
255 -- then process it now
256 --
257 IF ( l_returned_flag='0' ) THEN
258 --
259 -- set returned_flag to 1 for config_item corresponding to the returned tangible item
260 --
261 UPDATE CZ_CONFIG_ITEMS
262 SET returned_flag = '1'
263 WHERE instance_hdr_id=p_instance_hdr_id AND
264 instance_rev_nbr=p_instance_rev_nbr AND
265 config_item_id=p_returned_config_item_id AND
266 deleted_flag='0';
267 ELSE
268 RETURN;
269 END IF;
270
271 IF (p_locked_instance_rev_nbr IS NOT NULL AND p_locked_instance_rev_nbr<>0) AND
272 l_locked_item_returned_flag='0' THEN
273 --
274 -- set returned_flag to 1 for config_item corresponding to the pending order
275 --
276 UPDATE CZ_CONFIG_ITEMS
277 SET returned_flag = '1'
278 WHERE instance_hdr_id=p_instance_hdr_id AND
279 instance_rev_nbr= p_locked_instance_rev_nbr AND
280 config_item_id=p_returned_config_item_id AND
281 deleted_flag='0';
282 END IF;
283
284 -- do commit for the current autonomous transaction
285 -- after that batch validation can see the change
286 COMMIT;
287
288 -- Batch Validation starts here --
289 BEGIN
290 -- populate array of instances which will be used as a parameter in cz_network_api_pub.generate_config_trees
291 --
292 l_config_tbl(1).config_hdr_id := p_instance_hdr_id;
293 l_config_tbl(1).config_rev_nbr := p_instance_rev_nbr;
294
295 -- populate record structure of applicability parameters which will be used as a parameter in
296 -- cz_network_api_pub.generate_config_trees
297 --
298 IF p_config_eff_date IS NOT NULL THEN
299 l_appl_param_rec.config_effective_date := p_config_eff_date ;
300 ELSE
301 l_appl_param_rec.config_effective_date := SYSDATE; -- pass SYSDATE as default for effective_date of instance
302 END IF;
303
304 IF p_application_id IS NOT NULL THEN
305 l_appl_param_rec.calling_application_id := p_application_id;
306 ELSE
307 l_appl_param_rec.calling_application_id := 542; -- caller is IB
308 END IF;
309
310 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
311 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
312 'cz_network_api_pub.generate_config_trees() will be called.',
313 fnd_log.LEVEL_PROCEDURE);
314 END IF;
315
316 --
317 -- generate a new session hdr and
318 -- call batch validation for configuration validating.
319 -- This batch validate will be a new type of batch validate called VALIDATE_RETURN.
320 -- We need this new type because in this mode we want to update the instance status but not save config
321 -- item tree at all. The instance will be marked as valid only if there was no contradiction on removing the
322 -- returned item, the configuration is valid and complete and if there are no deltas from installed values
323 --
324 cz_network_api_pub.generate_config_trees
325 (p_api_version => 1.0,
326 p_config_tbl => l_config_tbl,
327 p_tree_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE,
328 p_appl_param_rec => l_appl_param_rec,
329 p_validation_context => CZ_API_PUB.G_INSTALLED,
330 p_validation_type => CZ_API_PUB.VALIDATE_RETURN,
331 x_config_model_tbl => l_config_model_tbl,
332 x_return_status => l_return_status,
333 x_msg_count => l_msg_count,
334 x_msg_data => l_msg_data);
335
336 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
337 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
338 'cz_network_api_pub.generate_config_trees() called been called return_status='||x_return_status,
339 fnd_log.LEVEL_PROCEDURE);
340 END IF;
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
345 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name, 'Fatal error : cz_network_api_pub.generate_config_trees() : '||SQLERRM);
346 fnd_msg_pub.count_and_get(p_count => x_msg_count,
347 p_data => x_msg_data);
348 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
349 cz_utils.log_report(G_PKG_NAME, l_api_name, l_ndebug,
350 'Fatal error : cz_network_api_pub.generate_config_trees() : '||SQLERRM,
351 FND_LOG.LEVEL_ERROR);
352 END IF;
353 END;
354
355 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
356
357 UPDATE CZ_CONFIG_ITEMS
358 SET returned_flag = l_returned_flag
359 WHERE instance_hdr_id=p_instance_hdr_id AND
360 instance_rev_nbr= p_instance_rev_nbr AND
361 config_item_id=p_returned_config_item_id AND
362 NVL(returned_flag, '0') <> l_returned_flag;
363
364 UPDATE CZ_CONFIG_ITEMS
365 SET returned_flag = l_locked_item_returned_flag
366 WHERE instance_hdr_id=p_instance_hdr_id AND
367 instance_rev_nbr= p_locked_instance_rev_nbr AND
368 config_item_id=p_returned_config_item_id AND
369 NVL(returned_flag, '0') <> l_locked_item_returned_flag;
370
371 x_validation_status := fnd_api.g_false;
372
373 x_return_status := FND_API.G_RET_STS_ERROR;
374 x_msg_count := x_msg_count + l_msg_count;
375 x_msg_data := l_msg_data;
376
377 ELSE
378 -- query for config status
379 SELECT config_status INTO l_config_status FROM CZ_CONFIG_HDRS
380 WHERE config_hdr_id=l_config_model_tbl(1).config_hdr_id AND
381 config_rev_nbr=l_config_model_tbl(1).config_rev_nbr;
382 -- convert config status into fnd_api.g_true/ fnd_api.g_false OUT parameter x_validation_status
383 --
384 IF l_config_status<>'2' THEN -- '2' means that BATCH validation complete
385 x_validation_status := fnd_api.g_false;
386 ELSE
387 BEGIN
388 SELECT 'F' INTO x_validation_status FROM dual
389 WHERE EXISTS(SELECT NULL FROM CZ_CONFIG_MESSAGES
390 WHERE config_hdr_id=l_config_model_tbl(1).config_hdr_id AND
391 config_rev_nbr=l_config_model_tbl(1).config_rev_nbr);
392 x_validation_status := fnd_api.g_false;
393 EXCEPTION
394 WHEN NO_DATA_FOUND THEN
395 x_validation_status := fnd_api.g_true;
396 END;
397 END IF;
398
399 END IF;
400
401 COMMIT;
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 -- raise fatal error (unexpected error ) , add a corresponding FND error message to error stack,
406 -- populate x_msg_count and x_msg_data
407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 fnd_msg_pub.add_exc_msg(G_PKG_NAME, l_api_name, 'Fatal error : '||SQLERRM);
409 fnd_msg_pub.count_and_get(p_count => x_msg_count,
410 p_data => x_msg_data);
411 END remove_Returned_Config_Item;
412
413 END CZ_IB_TSO_GRP;
[Home] [Help]