[Home] [Help]
PACKAGE BODY: APPS.CTO_GOP_INTERFACE_PK
Source
1 package body CTO_GOP_INTERFACE_PK as
2 /* $Header: CTOGOPIB.pls 120.2 2007/11/29 14:14:15 abhissri ship $*/
3 /*----------------------------------------------------------------------------+
4 | Copyright (c) 2003 Oracle Corporation Belmont, California, USA
5 | All rights reserved.
6 | Oracle Manufacturing
7 |
8 |FILE NAME : CTOGOPIB.pls
9 |
10 |DESCRIPTION : Contains modules to :
11 | conatins a wrapper procedure for ATP
12 | This calls match api, reuse api and option specific sources
13 | api
14 |HISTORY :
15 | 09-05-2003 Kiran Konada
16 |
17 |
18 | 10-21-2003 Kiran Konada
19 | removed x-Match_found as apramter to
20 | procedure Match_configured_item to keep in syn with
21 | spec change
22 |
23 | 11-05-2003 Kiran Konada
24 | put the debug statement with rows passed before
25 | insertion into BCOL
26 |
27 | 01-22-2004 Kiran Konada
28 | bugfix 3391383
29 | config_orgs attribute from model is inserted into bcol_gt
30 | bug was we tried to get for config_item_id
31 |
32 | 02-23-2004 Kiran Konada
33 | bugfix 3259017
34 |
35 | 05-17-2004 Kiran Konada
36 | bugfix 3555026
37 | --null value in config_orgs should be treated as
38 | based on sourcing
39 | --When ATP passes null in ship_from_org_id, we should
40 | NOT default to any other organization
41 | AS that org could be a ware house on SO pad
42 | during intial scheduling and hence bcol could have
43 | the data AND would create a problem in re-use,
44 | as configitem is reused if ware house is same
45 | before and after re-scheduling
46 -------------------------------------------------------------------------------
47 */
48 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_GOP_INTERFACE_PK';
49 --remove teh level 5
50 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
51
52
53
54 PROCEDURE CTO_GOP_WRAPPER_API (
55 p_Action IN VARCHAR2,
56 p_Source IN VARCHAR2,
57 p_match_rec_of_tab IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE,
58 X_oss_orgs_list OUT NOCOPY CTO_OSS_SOURCE_PK.Oss_orgs_list_rec_type,
59 x_return_status OUT NOCOPY VARCHAR2,
60 X_msg_count OUT NOCOPY number,
61 X_msg_data OUT NOCOPY Varchar2
62 )
63 IS
64 i number;
65 --why is match prfoile a varchr??
66 l_match_profile number;
67 l_match_found VARCHAR2 (1);
68
69 l_last_index number;
70 l_model_exists varchar2(1);
71 l_config_change varchar2(1);
72 lStmtNum number;
73
74 --dummy
75 l_count number;
76 l_PDS_ODS number; --PDS= 4, ODS= 5, any value other than 4 is treated as ODS(vivek)
77
78 lReuseProfile number; --Bugfix 6642016
79
80
81 CURSOR c_debug IS
82 SELECT config_item_id,
83 line_id,
84 link_to_line_id,
85 parent_ato_line_id,
86 gop_parent_ato_line_id,
87 ato_line_id,
88 top_model_line_id,
89 inventory_item_id,
90 ordered_quantity,
91 qty_per_parent_model,
92 ship_from_org_id,
93 validation_org,
94 plan_level,
95 wip_supply_type,
96 bom_item_type,
97 reuse_config,
98 perform_match,
99 config_creation,
100 option_specific,
101 oss_error_code
102 FROM bom_cto_order_lines_gt;
103
104
105 BEGIN
106
107 oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',5);
108
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110
111
112 lStmtNum := 10;
113 DELETE FROM bom_cto_order_lines_gt;
114
115 lStmtNum := 20;
116 l_match_profile := FND_PROFILE.Value('BOM:MATCH_CONFIG');
117
118
119
120 lStmtNum := 30;
121 l_PDS_ODS := FND_PROFILE.Value('INV_CTP');
122
123
124
125 --level1
126 IF PG_DEBUG <> 0 THEN
127 oe_debug_pub.add('match profile =>'||l_match_profile,1);
128 END IF;
129
130 lStmtNum := 40;
131 l_last_index := p_match_rec_of_tab.line_id.count;
132
133 IF PG_DEBUG = 5 THEN
134 lStmtNum := 50;
135 select count(*)
136 into l_count
137 from bom_cto_order_lines_gt;
138
139 oe_debug_pub.add('count before insert into bCOL =>'||l_count,1);
140
141 END IF;
142
143
144 --debug level 5
145 IF PG_DEBUG = 5 THEN
146 FOR i IN 1..l_last_index LOOP
147 oe_debug_pub.add('line_id=>'||p_match_rec_of_tab.line_id(i)||
148 'QTY=>'||p_match_rec_of_tab.ordered_quantity(i)||
149 'ship_frm_org=>'||p_match_rec_of_tab.SHIP_FROM_ORG_ID(i)||
150 'validation_org=>'||p_match_rec_of_tab.Validation_org(i),5);
151 END LOOP;
152
153 oe_debug_pub.add('Sql%row count ='||sql%rowcount,3);
154 END IF;
155
156
157
158 --used an diff insert than one from grp match api
159 --reason: for better performance
160 -- insert fo grp api needed an extend api
161 -- and un-ncessary insertion of null values
162 lStmtNum:=10;
163 FORALL i in 1..l_last_index
164 INSERT INTO bom_cto_order_lines_gt
165 (
166 ATO_LINE_ID,
167 COMPONENT_CODE,
168 COMPONENT_SEQUENCE_ID,
169 BOM_ITEM_TYPE,
170 WIP_SUPPLY_TYPE,
171 INVENTORY_ITEM_ID,
172 LINE_ID,
173 LINK_TO_LINE_ID,
174 ORDERED_QUANTITY,
175 ORDER_QUANTITY_UOM,
176 TOP_MODEL_LINE_ID,
177 SHIP_FROM_ORG_ID,
178 config_item_id,
179 VALIDATION_ORG --3503764
180 )
181 VALUES
182 (
183 p_match_rec_of_tab.ato_line_id(i),
184 p_match_rec_of_tab.component_code(i),
185 p_match_rec_of_tab.component_sequence_id(i),
186 --for oss pefromance improvement
187 --added -1
188 --bom_item_type
189 decode(p_match_rec_of_tab.top_model_line_id(i),null,-1, --ato item
190 -- 1 for topst ato model and default is -1
191 decode(p_match_rec_of_tab.ato_line_id(i),p_match_rec_of_tab.line_id(i),1,-1)
192 ),
193 -1, --wip_supply_type
194 p_match_rec_of_tab.inventory_item_id(i),
195 p_match_rec_of_tab.line_id(i),
196 p_match_rec_of_tab.link_to_line_id(i),
197 p_match_rec_of_tab.ordered_quantity(i),
198 p_match_rec_of_tab.order_quantity_uom(i),
199 p_match_rec_of_tab.top_model_line_id(i),
200 nvl(p_match_rec_of_tab.ship_from_org_id(i),-99),--3555026
201 --need for better prformance of OSS code
202 --if independent ato line order
203 --populate inv_item_id as config item id
204 --conatct info : kiran/renga
205 decode(p_match_rec_of_tab.top_model_line_id(i),
206 null,
207 p_match_rec_of_tab.inventory_item_id(i)
208 ),
209
210 p_match_rec_of_tab.validation_org(i)--3503764
211 );
212
213 IF PG_DEBUG <> 0 THEN
214 oe_debug_pub.add(sql%rowcount||' rows inserted into bcol_gt',5);
215 END IF;
216
217
218 lStmtNum:=40;
219 BEGIN
220 SELECT 'Y'
221 INTO l_model_exists
222 FROM bom_cto_order_lines_gt
223 WHERE line_id = ato_line_id
224 AND top_model_line_id is not null
225 AND rownum = 1;
226 EXCEPTION
227 WHEN others THEN
228 l_model_exists :='N';
229 END;
230
231 --l_model_exists := 'N';
232 IF l_model_exists = 'Y' THEN --ato model and maybe atoitem
233 lStmtNum:=50;
234 CTO_MATCH_CONFIG.prepare_bcol_temp_data(
235 p_source => p_Source,
236 p_match_rec_of_tab => p_match_rec_of_tab,
237 x_return_status => x_return_status,
238 x_msg_count => X_msg_count,
239 x_msg_data => X_msg_data
240 );
241 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
242 --level 1
243 -- IF PG_DEBUG <> 0 THEN
244 oe_debug_pub.add('SUCCESS after CTO_MATCH_CONFIG.prepare_bcol_temp_data',1);
245 -- END IF;
246 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
247 RAISE fnd_api.g_exc_error;
248 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
249 RAISE fnd_api.g_exc_unexpected_error;
250 END IF;
251
252
253 IF l_PDS_ODS = 4 THEN --PDS
254
255 lStmtNum:=60;
256 UPDATE bom_cto_order_lines_gt child
257 SET qty_per_parent_model =
258 --used round to be consistent with can_configuration code
259 ( SELECT ROUND(child.ordered_quantity/parent.ordered_quantity,7)
260 FROM bom_cto_order_lines_gt parent
261 WHERE child.parent_ato_line_id= parent.line_id
262 )
263 --to filter out ato item order lines
264 WHERE top_model_line_id is not null;
265
266 IF PG_DEBUG <> 0 THEN
267 oe_debug_pub.add('qty_per_parent_model is calculated for'||sql%rowcount||' rows in bcol_gt',5);
268 END IF;
269
270 --call reuse configuration API
271 lStmtNum:=70;
272 lReuseProfile := FND_PROFILE.Value('CTO_REUSE_CONFIG'); --Bugfix 6642016
273
274 IF PG_DEBUG <> 0 THEN
275 oe_debug_pub.add('Create_And_Link_Item: ' ||
276 ' Reuse Configuration profile: ' || to_char(lReuseProfile) , 5);
277 END IF; --Bugfix 6642016
278
279 if ( nvl(lReuseProfile,1) = 1 ) then ----Bugfix 6642016
280 lStmtNum:=80;
281 CTO_MATCH_CONFIG.CTO_REUSE_CONFIGURATION(
282 X_config_change =>l_config_change,
283 X_return_status =>x_return_status,
284 X_msg_count =>x_msg_count,
285 X_msg_data =>x_msg_data
286 ) ;
287 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
288 -- IF PG_DEBUG <> 0 THEN
289 oe_debug_pub.add('SUCCESS after CTO_MATCH_CONFIG.CTO_REUSE_CONFIGURATION',1);
290 -- END IF;
291 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
292 RAISE fnd_api.g_exc_error;
293 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
294 RAISE fnd_api.g_exc_unexpected_error;
295 END IF;
296 end if; --lReuseProfile = 1 Bugfix 6642016
297
298 IF l_match_profile = 1 THEN
299
300 lStmtNum:=30;
301 CTO_Configured_Item_GRP.MATCH_CONFIGURED_ITEM
302 (
303 p_api_version => 1.0,
304 x_return_status => x_return_status,
305 x_msg_count => X_msg_count,
306 x_msg_data => X_msg_data,
307 p_Action => p_Action,
308 p_Source => p_Source ,
309
310 p_cto_match_rec => p_match_rec_of_tab
311
312 );
313
314 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
315 IF PG_DEBUG <> 0 THEN
316 oe_debug_pub.add('SUCCESS after CTO_Configured_Item_GRP.MATCH_CONFIGURED_ITEM',1);
317 END IF;
318 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
319 RAISE fnd_api.g_exc_error;
320 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
321 RAISE fnd_api.g_exc_unexpected_error;
322 END IF;
323
324 END IF; -- l_match_pfoile = 1
325
326 --get config_creation attr for OSS purposes
327 --for matched.re-used config_item only
328
329 --as per perf std ahmed almori
330 --If the global temporary table is referenced in a sub-query in a
331 --SQL statement which accesses other tables.
332 --In such cases, the join order may not be optimal due to the lack
333 --of stats on the temp table, hence hints should be used to ensure the optimal join order.
334
335 UPDATE /*+ INDEX (GT BOM_CTO_ORDER_LINES_GT_N5) */ bom_cto_order_lines_gt GT
336 SET GT.config_creation = ( SELECT nvl(MTL.config_orgs,1)--3555026
337 FROM mtl_system_items MTL
338 WHERE MTL.inventory_item_id = GT.inventory_item_id -- bugfix 3391383
339 AND MTL.organization_id = GT.validation_org--3555026
340 AND GT.bom_item_type = '1'
341 AND GT.config_item_id is not null
342 )
343 WHERE GT.bom_item_type = '1'
344 AND GT.config_item_id is not null;
345
346 IF PG_DEBUG <> 0 THEN
347 oe_debug_pub.add('Updated '||sql%rowcount||' model rows with config_orgs or config_creation attribute',5);
348 END IF;
349
350
351 END IF; -- l_pds_ods =4
352
353 END IF; --ato model and maybe atoitem
354
355
356 --make a call to option dependent sources api
357 lStmtNum:=80;
358 CTO_OSS_SOURCE_PK.GET_OSS_ORGS_LIST(
359 X_OSS_ORGS_LIST => X_oss_orgs_list,
360 X_RETURN_STATUS =>x_return_status,
361 X_MSG_DATA =>X_msg_data,
362 X_MSG_COUNT =>X_msg_count
363 );
364
365 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
366 --level 1
367 -- IF PG_DEBUG <> 0 THEN
368 oe_debug_pub.add('SUCCESS after CTO_OSS_SOURCE_PK.GET_OSS_ORGS_LIST',1);
369 -- END IF;
370 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
371 RAISE fnd_api.g_exc_error;
372 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
373 RAISE fnd_api.g_exc_unexpected_error;
374 END IF;
375
376 --make sure floowling sql bring null values too
377 lStmtNum:=90;
378 SELECT oss_error_code,
379 config_item_id,
380 parent_ato_line_id,
381 gop_parent_ato_line_id,
382 bom_item_type,
383 wip_supply_type
384 BULK COLLECT INTO
385 p_match_rec_of_tab.oss_error_code,
386 --during Ut make sure next statement
387 --over writes existing values , ifnot
388 --additional rows may get created during
389 --for MATCH 0n cases. remove comment after UT
390 p_match_rec_of_tab.config_item_id,
391 p_match_rec_of_tab.parent_ato_line_id,
392 p_match_rec_of_tab.gop_parent_ato_line_id,
393 p_match_rec_of_tab.bom_item_type,
394 p_match_rec_of_tab.wip_supply_type
395 FROM bom_cto_order_lines_gt
396 ORDER BY line_id; --Bugfix 6055375
397
398
399 IF PG_DEBUG <> 0 THEN
400 oe_debug_pub.add('Line_id count=>'||p_match_rec_of_tab.line_id.count,5);
401 oe_debug_pub.add('config_id count=>'||p_match_rec_of_tab.config_item_id.count,5);
402 oe_debug_pub.add('oss error code count=>'||p_match_rec_of_tab.oss_error_code.count,5);
403 oe_debug_pub.add('wip supply_type=>'||p_match_rec_of_tab.WIP_SUPPLY_TYPE.count,5);
404
405 --oe_debug_pub.add('Matched item id=>'||p_match_rec_of_tab.config_item_id(1),5);
406
407
408 END IF;
409
410 lStmtNum:=95;
411 IF PG_DEBUG = 5 THEN
412 Oe_debug_pub.add(' config_item_id --'||
413 ' line_id --'||
414 ' link_to_line_id --'||
415 ' parent_ato_line_id --'||
416 ' gop_parent_ato_line_id --'||
417 ' ato_line_id --'||
418 ' top_model_line_id --'||
419 ' inventory_item_id --'||
420 ' ordered_qunatity --'||
421 ' qty_per_parent_model --'||
422 ' ship_from_org_id --'||
423 'validation_org--'||
424 ' plan_level --'||
425 ' wip_supply_type --'||
426 ' bom_item_type --'||
427 ' reuse_config --'||
428 ' perform_match --'||
429 ' config_creation --'||
430 ' option_specific --'||
431 ' oss_error_code ',5);
432 FOR debug_rec in c_debug
433 LOOP
434 oe_debug_pub.add(
435 debug_rec.config_item_id ||' -- '||
436 debug_rec.line_id ||' -- '||
437 debug_rec.link_to_line_id ||' -- '||
438 debug_rec.parent_ato_line_id ||' -- '||
439 debug_rec.gop_parent_ato_line_id ||' -- '||
440 debug_rec.ato_line_id ||' -- '||
441 debug_rec.top_model_line_id ||' -- '||
442 debug_rec.inventory_item_id ||' -- '||
443 debug_rec.ordered_quantity ||' -- '||
444 debug_rec.qty_per_parent_model ||' --'||
445 debug_rec.ship_from_org_id ||' -- '||
446 debug_rec.validation_org||' -- '||
447 debug_rec.plan_level ||' -- '||
448 debug_rec.wip_supply_type ||' -- '||
449 debug_rec.bom_item_type ||' -- '||
450 debug_rec.reuse_config ||' -- '||
451 debug_rec.perform_match ||' -- '||
452 debug_rec.config_creation ||' -- '||
453 debug_rec.option_specific ||' -- '||
454 debug_rec.oss_error_code ,5);
455 END LOOP;
456 END IF;--debug
457
458
459
460 lStmtNum:=100;
461
462 --need this when we make bcol_gt a session table
463 DELETE FROM bom_cto_order_lines_gt;
464
465 oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',5);
466
467 EXCEPTION
468 WHEN fnd_api.g_exc_error THEN
469 IF PG_DEBUG <> 0 THEN
470 oe_debug_pub.add('CTO_GOP_WRAPPER_API: ' || 'Exception in stmt num: '
471 || to_char(lStmtNum), 1);
472 END IF;
473 x_return_status := FND_API.G_RET_STS_ERROR;
474 -- Get message count and data
475 cto_msg_pub.count_and_get
476 ( p_msg_count => x_msg_count
477 , p_msg_data => x_msg_data
478 );
479 WHEN fnd_api.g_exc_unexpected_error THEN
480 IF PG_DEBUG <> 0 THEN
481 oe_debug_pub.add('CTO_GOP_WRAPPER_API: ' || ' Unexpected Exception in stmt num: '
482 || to_char(lStmtNum), 1);
483 END IF;
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
485 -- Get message count and data
486 cto_msg_pub.count_and_get
487 ( p_msg_count => x_msg_count
488 , p_msg_data => x_msg_data
489 );
490 WHEN OTHERS then
491 IF PG_DEBUG <> 0 THEN
492 oe_debug_pub.add('CTO_GOP_WRAPPER_API: ' || 'Others Exception in stmt num: '
493 || to_char(lStmtNum), 1);
494 oe_debug_pub.add('error='||sqlerrm);
495 END IF;
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497 -- Get message count and data
498 cto_msg_pub.count_and_get
499 ( p_msg_count => x_msg_count
500 , p_msg_data => x_msg_data
501 );
502 END CTO_GOP_WRAPPER_API;
503 END CTO_GOP_INTERFACE_PK;