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