[Home] [Help]
PACKAGE BODY: APPS.CTO_CONFIGURED_ITEM_GRP
Source
1 package body CTO_Configured_Item_GRP AS
2 /* $Header: CTOGCFGB.pls 120.1.12020000.4 2013/03/29 10:07:35 abhissri ship $*/
3
4 /*----------------------------------------------------------------------------+
5 | Copyright (c) 2003 Oracle Corporation RedwoodShores, California, USA
6 | All rights reserved.
7 | Oracle Manufacturing
8 |
9 |FILE NAME : CTOGCFGB.pls
10 |
11 |DESCRIPTION :
12 |
13 -- Start of comments
14 -- API name : MATCH_CONFIGURED_ITEM
15 -- Type : Group
16 -- Function :To match configured items
17 -- Pre-reqs :1. table BOMC_TO_ORDER_LINES_TEMP/DMF J
18 -- Parameters :
19 -- IN : p_api_version IN NUMBER Required
20 -- p_init_msg_list IN VARCHAR2 Optional
21 -- Default = FND_API.G_FALSE
22 -- p_commit IN VARCHAR2 Optional
23 -- Default = FND_API.G_FALSE
24 -- Action IN VARCHAR2(30)
25 -- Source IN VARCHAR2(30)
26 -- p_cto_match_rec IN OUT CTO_MATCH_REC_TYPE .
27 -- .
28 -- OUT : x_return_status OUT VARCHAR2(1)
29 -- x_msg_count OUT NUMBER
30 -- x_msg_data OUT VARCHAR2(2000)
31 -- x_match_found OUT Varchar2(1) Y/N
32 -- .
33 -- Version : Current version 1.0
34 -- Changed....
35 --
36 -- Initial version 1.0
37 ---
38 --
39 -- Notes : Note text
40 --
41 --
42 -- End of comments
43
44 */
45
46
47 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_Configured_Item_GRP';
48
49 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
50
51 --
52 -- bug 16352937
53 -- This is the old signature of the API that was overloaded
54 -- to add a new parameter to capture the hash value
55 --
56 PROCEDURE MATCH_CONFIGURED_ITEM
57 (
58 p_api_version IN NUMBER,
59 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
60 p_commit IN VARCHAR2 default FND_API.G_FALSE,
61 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
62 x_return_status OUT NOCOPY VARCHAR2,
63 x_msg_count OUT NOCOPY NUMBER,
64 x_msg_data OUT NOCOPY VARCHAR2,
65 p_Action IN VARCHAR2,
66 p_Source IN VARCHAR2 ,
67 p_cto_match_rec IN OUT NOCOPY CTO_MATCH_REC_TYPE
68 )
69 IS
70 -- Bugfix 16531547: hash_value should be a varchar variable.
71 -- l_hash_value NUMBER;
72 l_hash_value VARCHAR2(2000);
73 BEGIN
74 MATCH_CONFIGURED_ITEM(
75 p_api_version ,
76 p_init_msg_list ,
77 p_commit ,
78 p_validation_level ,
79 x_return_status ,
80 x_msg_count ,
81 x_msg_data ,
82 p_Action ,
83 p_Source ,
84 p_cto_match_rec ,
85 l_hash_value);
86
87 END MATCH_CONFIGURED_ITEM;
88
89 PROCEDURE MATCH_CONFIGURED_ITEM
90 (
91 --std parameters
92 p_api_version IN NUMBER,
93 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
94 p_commit IN VARCHAR2 default FND_API.G_FALSE,
95 p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
96 x_return_status OUT NOCOPY VARCHAR2,
97 x_msg_count OUT NOCOPY NUMBER,
98 x_msg_data OUT NOCOPY VARCHAR2,
99 --program parameters
100 p_Action IN VARCHAR2,
101 p_Source IN VARCHAR2 ,
102 p_cto_match_rec IN OUT NOCOPY CTO_MATCH_REC_TYPE,
103 -- bug 16352937
104 -- Bugfix 16531547: hash_value should be a varchar variable.
105 -- x_hash_value OUT NOCOPY NUMBER
106 x_hash_value OUT NOCOPY VARCHAR2
107 )
108
109 IS
110
111 l_model_exists VARCHAR2(1);
112
113 Type number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
114
115 l_ato_line_tbl number_tbl_type ;
116
117 l_last_index number;
118 l_custom_match_profile VARCHAR2(10);
119 lStmtNum number;
120 i NUMBER;
121
122 l_api_name CONSTANT VARCHAR2(30) := 'MATCH_CONFIGURED_ITEM';
123 l_api_version CONSTANT NUMBER := 1.0;
124
125 l_dummy_line_id number;
126 j number;
127
128 l_match_flag_tab CTO_MATCH_CONFIG.MATCH_FLAG_TBL_TYPE;
129 x_sparse_match_tab CTO_MATCH_CONFIG.MATCH_FLAG_TBL_TYPE;
130 l_match_flag_rec_of_tab CTO_MATCH_CONFIG.Match_flag_rec_of_tab;
131
132 CURSOR c_models_match_flag
133 IS
134 SELECT line_id,
135 parent_ato_line_id,
136 ato_line_id,
137 perform_match
138 FROM bom_cto_order_lines_gt
139 WHERE bom_item_type = '1'
140 AND wip_supply_type <> 6;
141
142 CURSOR c_debug IS
143 SELECT config_item_id,
144 line_id,
145 link_to_line_id,
146 parent_ato_line_id,
147 gop_parent_ato_line_id,
148 ato_line_id,
149 top_model_line_id,
150 inventory_item_id,
151 ordered_quantity,
152 qty_per_parent_model,
153 ship_from_org_id,
154 plan_level,
155 wip_supply_type,
156 bom_item_type,
157 reuse_config,
158 perform_match,
159 config_creation,
160 option_specific,
161 oss_error_code
162 FROM bom_cto_order_lines_gt;
163
164 BEGIN
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166
167 --Enable this call in future versions
168 lStmtNum:= 10;
169 IF NOT FND_API.Compatible_API_call(l_api_version,
170 p_api_version,
171 l_api_name,
172 G_PKG_NAME)
173 THEN
174 RAISE fnd_api.g_exc_unexpected_error;
175 END IF;
176
177
178 lStmtNum:=20;
179 --no need of match profile as upto customer discretion to
180 --call macth api
181 --l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
182
183 IF PG_DEBUG <> 0 THEN
184 oe_debug_pub.add('MATCH_CONFIGURED_ITEM:'||'CUSTOM_MATCH: ' || l_custom_match_profile, 1);
185 END IF;
186
187 IF p_Source <> 'GOP' THEN
188 lStmtNum:=30;
189
190 CTO_MATCH_CONFIG.Insert_into_bcol_gt(p_match_rec_of_tab =>p_cto_match_rec,
191 x_return_status =>x_return_status,
192 x_msg_count =>X_msg_count,
193 x_msg_data =>X_msg_data
194 );
195
196 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
197
198 -- IF PG_DEBUG <> 0 THEN
199 oe_debug_pub.add('MATCH_CONFIGURED_ITEM:'||'success after CTO_MATCH_CONFIG.Insert_into_bcol_gt', 1);
200 -- END IF;
201
202 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
203 RAISE fnd_api.g_exc_error;
204 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
205 RAISE fnd_api.g_exc_unexpected_error;
206 END IF;
207
208 END IF;
209
210 lStmtNum:=40;
211 BEGIN
212 SELECT 'Y'
213 INTO l_model_exists
214 FROM bom_cto_order_lines_gt
215 WHERE line_id = ato_line_id
216 AND top_model_line_id is not null
217 AND rownum = 1;
218 EXCEPTION
219 WHEN others THEN
220 l_model_exists :='N';
221 END;
222
223 IF l_model_exists = 'Y' THEN
224
225 lStmtNum:=50;
226 CTO_MATCH_CONFIG.Update_BCOLGT_with_match_flag
227 (
228 x_return_status => x_return_status,
229 x_msg_count => x_msg_count,
230 x_msg_data => x_msg_data
231 );
232
233 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
234 --level1
235 -- IF PG_DEBUG <> 0 THEN
236 oe_debug_pub.add('MATCH_CONFIGURED_ITEM:'||'success after CTO_MATCH_CONFIG.Update_BCOLGT_with_match_flag', 1);
237 -- END IF;
238 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
239 RAISE fnd_api.g_exc_error;
240 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
241 RAISE fnd_api.g_exc_unexpected_error;
242 END IF;
243
244 lStmtNum:=60;
245 IF p_Source NOT IN ('CTO','GOP') THEN
246
247 lStmtNum:=70;
248 CTO_MATCH_CONFIG.prepare_bcol_temp_data(
249 p_source => p_Source,
250 p_match_rec_of_tab => p_cto_match_rec,
251 x_return_status => x_return_status,
252 x_msg_count => X_msg_count,
253 x_msg_data => X_msg_data
254 );
255 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
256 --level1
257 -- IF PG_DEBUG <> 0 THEN
258 oe_debug_pub.add('MATCH_CONFIGURED_ITEM:'||'success after CTO_MATCH_CONFIG.prepare_bcol_temp_data', 1);
259 -- END IF;
260 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
261 RAISE fnd_api.g_exc_error;
262 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
263 RAISE fnd_api.g_exc_unexpected_error;
264 END IF;
265
266 END IF;--source = not cto/gop
267
268 --Following needs to be executed for both
269 --CTO and GOP
270 lStmtNum:=80;
271 SELECT ato_line_id
272 BULK COLLECT INTO
273 l_ato_line_tbl
274 FROM bom_cto_order_lines_gt
275 WHERE line_id =ato_line_id
276 AND top_model_line_id is not null
277 AND config_item_id is null
278 AND bom_item_type = '1'; --implies item not re-used
279
280 IF PG_DEBUG <> 0 THEN
281 oe_debug_pub.add('MATCH_CONFIGURED_ITEM:'||'# of top most ato models=>'||sql%rowcount, 5);
282 END IF;
283
284 lStmtNum:=81;
285 l_last_index := l_ato_line_tbl.count;
286
287 IF PG_DEBUG <> 0 THEN
288 oe_debug_pub.add('MATCH_CONFIGURED_ITEM:'||'l_ato_line_tbl.count='||l_last_index, 5);
289 END IF;
290
291 lStmtNum:=90;
292 FOR i in 1..l_last_index LOOP
293
294 lStmtNum:= 100;
295 CTO_MATCH_CONFIG.perform_match
296 (
297 p_ato_line_id => l_ato_line_tbl(i),
298 -- p_custom_match_profile => l_custom_match_profile,
299 x_return_status => x_return_status,
300 x_msg_count => x_msg_count,
301 x_msg_data => x_msg_data,
302 -- bug 16352937
303 x_hash_value => x_hash_value
304 );
305
306 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
307 --level 3
308 IF PG_DEBUG <> 0 THEN
309 oe_debug_pub.add('MATCH_CONFIGURED_ITEM:'||'success after CTO_MATCH_CONFIG.perform_match for line_id=>'
310 ||l_ato_line_tbl(i), 3);
311 END IF;
312
313 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
314 RAISE fnd_api.g_exc_error;
315 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
316 RAISE fnd_api.g_exc_unexpected_error;
317 END IF;
318
319 END LOOP;
320
321 lStmtNum:= 110;
322
323 SELECT config_item_id,
324 perform_match
325 BULK COLLECT INTO
326 --during Ut make sure next statement
327 --over writes existing values , ifnot
328 --additional rows may get created during
329 --for MATCH 0n cases. remove comment after UT
330 p_cto_match_rec.config_item_id,
331 p_cto_match_rec.perform_match
332 FROM bom_cto_order_lines_gt
333 order by line_id; --Bugfix 6055375
334
335 END IF; --if model eixsts
336
337 lStmtNum:= 120;
338 IF PG_DEBUG = 5 THEN
339 Oe_debug_pub.add(' config_item_id --'||
340 ' line_id --'||
341 ' link_to_line_id --'||
342 ' parent_ato_line_id --'||
343 ' gop_parent_ato_line_id --'||
344 ' ato_line_id --'||
345 ' top_model_line_id --'||
346 ' inventory_item_id --'||
347 ' ordered_qunatity --'||
348 ' qty_per_parent_model --'||
349 ' ship_from_org_id --'||
350 ' plan_level --'||
351 ' wip_supply_type --'||
352 ' bom_item_type --'||
353 ' reuse_config --'||
354 ' perform_match --'||
355 ' config_creation --'||
356 ' option_specific --'||
357 ' oss_error_code ',5);
358 FOR debug_rec in c_debug
359 LOOP
360 oe_debug_pub.add(
361 debug_rec.config_item_id ||' -- '||
362 debug_rec.line_id ||' -- '||
363 debug_rec.link_to_line_id ||' -- '||
364 debug_rec.parent_ato_line_id ||' -- '||
365 debug_rec.gop_parent_ato_line_id ||' -- '||
366 debug_rec.ato_line_id ||' -- '||
367 debug_rec.top_model_line_id ||' -- '||
368 debug_rec.inventory_item_id ||' -- '||
369 debug_rec.ordered_quantity ||' -- '||
370 debug_rec.qty_per_parent_model ||' --'||
371 debug_rec.ship_from_org_id ||' -- '||
372 debug_rec.plan_level ||' -- '||
373 debug_rec.wip_supply_type ||' -- '||
374 debug_rec.bom_item_type ||' -- '||
375 debug_rec.reuse_config ||' -- '||
376 debug_rec.perform_match ||' -- '||
377 debug_rec.config_creation ||' -- '||
378 debug_rec.option_specific ||' -- '||
379 debug_rec.oss_error_code ,5);
380 END LOOP;
381 END IF;--debug
382
383 EXCEPTION
384 WHEN fnd_api.g_exc_error THEN
385 IF PG_DEBUG <> 0 THEN
386 oe_debug_pub.add('MATCH_CONFIGURED_ITEM: ' || 'Exception in stmt num: '
387 || to_char(lStmtNum), 1);
388 END IF;
389 x_return_status := FND_API.G_RET_STS_ERROR;
390 -- Get message count and data
391 cto_msg_pub.count_and_get
392 ( p_msg_count => x_msg_count
393 , p_msg_data => x_msg_data
394 );
395 WHEN fnd_api.g_exc_unexpected_error THEN
396 IF PG_DEBUG <> 0 THEN
397 oe_debug_pub.add('MATCH_CONFIGURED_ITEM: ' || ' Unexpected Exception in stmt num: '
398 || to_char(lStmtNum), 1);
399 END IF;
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
401 -- Get message count and data
402 cto_msg_pub.count_and_get
403 ( p_msg_count => x_msg_count
404 , p_msg_data => x_msg_data
405 );
406 WHEN OTHERS then
407 IF PG_DEBUG <> 0 THEN
408 oe_debug_pub.add('MATCH_CONFIGURED_ITEM: ' || 'Others Exception in stmt num: '
409 || to_char(lStmtNum), 1);
410 oe_debug_pub.add('error '||sqlerrm,1);
411 END IF;
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413 -- Get message count and data
414 cto_msg_pub.count_and_get
415 ( p_msg_count => x_msg_count
416 , p_msg_data => x_msg_data
417 );
418
419 END MATCH_CONFIGURED_ITEM;
420
421
422 END CTO_Configured_Item_GRP;