[Home] [Help]
PACKAGE BODY: APPS.CTO_GOP_INTERFACE_PK
Source
1 package body CTO_GOP_INTERFACE_PK as
2 /* $Header: CTOGOPIB.pls 120.4.12020000.5 2013/03/29 10:09:10 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 PROCEDURE init_rec(p_match_rec_of_tab IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE)
53 AS
54 BEGIN
55 p_match_rec_of_tab.CONFIG_ITEM_ID.delete;
56 p_match_rec_of_tab.LINE_ID.delete;
57 p_match_rec_of_tab.LINK_TO_LINE_ID.delete;
58 p_match_rec_of_tab.ATO_LINE_ID.delete;
59 p_match_rec_of_tab.TOP_MODEL_LINE_ID.delete;
60 p_match_rec_of_tab.INVENTORY_ITEM_ID.delete;
61 p_match_rec_of_tab.COMPONENT_CODE.delete;
62 p_match_rec_of_tab.COMPONENT_SEQUENCE_ID.delete;
63 p_match_rec_of_tab.VALIDATION_ORG.delete;
64 p_match_rec_of_tab.QTY_PER_PARENT_MODEL.delete;
65 p_match_rec_of_tab.ORDERED_QUANTITY.delete;
66 p_match_rec_of_tab.ORDER_QUANTITY_UOM.delete;
67 p_match_rec_of_tab.PARENT_ATO_LINE_ID.delete;
68 p_match_rec_of_tab.GOP_PARENT_ATO_LINE_ID.delete;
69 p_match_rec_of_tab.PERFORM_MATCH.delete;
70 p_match_rec_of_tab.PLAN_LEVEL.delete;
71 p_match_rec_of_tab.BOM_ITEM_TYPE.delete;
72 p_match_rec_of_tab.WIP_SUPPLY_TYPE.delete;
73 p_match_rec_of_tab.OSS_ERROR_CODE.delete;
74 p_match_rec_of_tab.SHIP_FROM_ORG_ID.delete;
75 p_match_rec_of_tab.Attribute_1.delete;
76 p_match_rec_of_tab.Attribute_2.delete;
77 p_match_rec_of_tab.Attribute_3.delete;
78 p_match_rec_of_tab.Attribute_4.delete;
79 p_match_rec_of_tab.Attribute_5.delete;
80 p_match_rec_of_tab.Attribute_6 := CTO_Configured_Item_GRP.date_arr_tbl_type(NULL);
81 p_match_rec_of_tab.Attribute_6.delete;
82 p_match_rec_of_tab.Attribute_7 := CTO_Configured_Item_GRP.date_arr_tbl_type(NULL);
83 p_match_rec_of_tab.Attribute_7.delete;
84 p_match_rec_of_tab.Attribute_8 := CTO_Configured_Item_GRP.date_arr_tbl_type(NULL);
85 p_match_rec_of_tab.Attribute_8.delete;
86 p_match_rec_of_tab.Attribute_9.delete;
87 END init_rec;
88
89 --
90 -- bug 14328455
91 -- The ATP inquiry processing should not retain the locks
92 -- as it would result in contention. Hence issuing an
93 -- Autonomous commit in this API
94 --
95 PROCEDURE call_match_item(
96 p_api_version IN NUMBER,
97 x_return_status OUT NOCOPY VARCHAR2,
98 x_msg_count OUT NOCOPY NUMBER,
99 x_msg_data OUT NOCOPY VARCHAR2,
100 p_Action IN VARCHAR2,
101 p_Source IN VARCHAR2 ,
102 p_cto_match_rec IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE) AS
103
104 --
105 -- Bug 16352937
106 -- Removed the Pragma and the Commit. this is because the match API would
107 -- change the data in BCOLGT and that data wont be visisble outside since
108 -- data in a temp changed in an Autonomous API is not visisble outside
109 --
110 -- PRAGMA AUTONOMOUS_TRANSACTION;
111
112 -- Bugfix 16531547: hash_value should be a varchar variable.
113 -- l_hash_value NUMBER;
114 l_hash_value VARCHAR2(2000);
115 l_return_status VARCHAR2(2000);
116 l_msg_count NUMBER;
117 l_msg_data VARCHAR2(2000);
118 BEGIN
119 oe_debug_pub.add('Inside API call_match_item',5);
120
121 --
122 -- bug 16352937
123 --
124 CTO_Configured_Item_GRP.MATCH_CONFIGURED_ITEM (
125 p_api_version => p_api_version,
126 x_return_status => x_return_status,
127 x_msg_count => X_msg_count,
128 x_msg_data => X_msg_data,
129 p_Action => p_Action,
130 p_Source => p_Source ,
131 p_cto_match_rec => p_cto_match_rec,
132 x_hash_value => l_hash_value);
133
134 oe_debug_pub.add('Inside API call_match_item l_hash_value :'||l_hash_value ,5);
135
136 --
137 -- bug 16311347
138 -- using the release lock API to release the lock instead of COMMIT
139 --
140 CTO_UTILITY_PK.release_lock(
141 x_return_status => l_return_status,
142 x_msg_count => l_msg_count,
143 x_msg_data => l_msg_data,
144 p_hash_value => l_hash_value);
145
146 oe_debug_pub.add('Inside API call_match_item After release lock' ,5);
147 --COMMIT;
148 END;
149
150 PROCEDURE CTO_GOP_WRAPPER_API (
151 p_Action IN VARCHAR2,
152 p_Source IN VARCHAR2,
153 p_match_rec_of_tab IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE,
154 X_oss_orgs_list OUT NOCOPY CTO_OSS_SOURCE_PK.Oss_orgs_list_rec_type,
155 x_return_status OUT NOCOPY VARCHAR2,
156 X_msg_count OUT NOCOPY number,
157 X_msg_data OUT NOCOPY Varchar2
158 )
159 IS
160 i number;
161 --why is match prfoile a varchr??
162 l_match_profile number;
163 l_match_found VARCHAR2 (1);
164
165 l_last_index number;
166 l_model_exists varchar2(1);
167 l_config_change varchar2(1);
168 lStmtNum number;
169
170 --dummy
171 l_count number;
172 l_PDS_ODS number; --PDS= 4, ODS= 5, any value other than 4 is treated as ODS(vivek)
173
174 lReuseProfile number; --Bugfix 6642016
175
176
177 CURSOR c_debug IS
178 SELECT config_item_id,
179 line_id,
180 link_to_line_id,
181 parent_ato_line_id,
182 gop_parent_ato_line_id,
183 ato_line_id,
184 top_model_line_id,
185 inventory_item_id,
186 ordered_quantity,
187 qty_per_parent_model,
188 ship_from_org_id,
189 validation_org,
190 plan_level,
191 wip_supply_type,
192 bom_item_type,
193 reuse_config,
194 perform_match,
195 config_creation,
196 option_specific,
197 oss_error_code
198 FROM bom_cto_order_lines_gt;
199
200
201 BEGIN
202
203 oe_debug_pub.add(' START TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',5);
204
205 x_return_status := FND_API.G_RET_STS_SUCCESS;
206
207
208 lStmtNum := 10;
209 DELETE FROM bom_cto_order_lines_gt;
210
211 lStmtNum := 20;
212 l_match_profile := FND_PROFILE.Value('BOM:MATCH_CONFIG');
213
214
215
216 lStmtNum := 30;
217 l_PDS_ODS := FND_PROFILE.Value('INV_CTP');
218
219
220
221 --level1
222 IF PG_DEBUG <> 0 THEN
223 oe_debug_pub.add('match profile =>'||l_match_profile,1);
224 END IF;
225
226 lStmtNum := 40;
227 l_last_index := p_match_rec_of_tab.line_id.count;
228
229 IF PG_DEBUG = 5 THEN
230 lStmtNum := 50;
231 select count(*)
232 into l_count
233 from bom_cto_order_lines_gt;
234
235 oe_debug_pub.add('count before insert into bCOL =>'||l_count,1);
236
237 END IF;
238
239
240 --debug level 5
241 IF PG_DEBUG = 5 THEN
242 FOR i IN 1..l_last_index LOOP
243 oe_debug_pub.add('line_id=>'||p_match_rec_of_tab.line_id(i)||
244 'QTY=>'||p_match_rec_of_tab.ordered_quantity(i)||
245 'ship_frm_org=>'||p_match_rec_of_tab.SHIP_FROM_ORG_ID(i)||
246 'validation_org=>'||p_match_rec_of_tab.Validation_org(i),5);
247 END LOOP;
248
249 oe_debug_pub.add('Sql%row count ='||sql%rowcount,3);
250 END IF;
251
252
253
254 --used an diff insert than one from grp match api
255 --reason: for better performance
256 -- insert fo grp api needed an extend api
257 -- and un-ncessary insertion of null values
258 lStmtNum:=10;
259 FORALL i in 1..l_last_index
260 INSERT INTO bom_cto_order_lines_gt
261 (
262 ATO_LINE_ID,
263 COMPONENT_CODE,
264 COMPONENT_SEQUENCE_ID,
265 BOM_ITEM_TYPE,
266 WIP_SUPPLY_TYPE,
267 INVENTORY_ITEM_ID,
268 LINE_ID,
269 LINK_TO_LINE_ID,
270 ORDERED_QUANTITY,
271 ORDER_QUANTITY_UOM,
272 TOP_MODEL_LINE_ID,
273 SHIP_FROM_ORG_ID,
274 config_item_id,
275 VALIDATION_ORG --3503764
276 )
277 VALUES
278 (
279 p_match_rec_of_tab.ato_line_id(i),
280 p_match_rec_of_tab.component_code(i),
281 p_match_rec_of_tab.component_sequence_id(i),
282 --for oss pefromance improvement
283 --added -1
284 --bom_item_type
285 decode(p_match_rec_of_tab.top_model_line_id(i),null,-1, --ato item
286 -- 1 for topst ato model and default is -1
287 decode(p_match_rec_of_tab.ato_line_id(i),p_match_rec_of_tab.line_id(i),1,-1)
288 ),
289 -1, --wip_supply_type
290 p_match_rec_of_tab.inventory_item_id(i),
291 p_match_rec_of_tab.line_id(i),
292 p_match_rec_of_tab.link_to_line_id(i),
293 p_match_rec_of_tab.ordered_quantity(i),
294 p_match_rec_of_tab.order_quantity_uom(i),
295 p_match_rec_of_tab.top_model_line_id(i),
296 nvl(p_match_rec_of_tab.ship_from_org_id(i),-99),--3555026
297 --need for better prformance of OSS code
298 --if independent ato line order
299 --populate inv_item_id as config item id
300 --conatct info : kiran/renga
301 decode(p_match_rec_of_tab.top_model_line_id(i),
302 null,
303 p_match_rec_of_tab.inventory_item_id(i)
304 ),
305
306 p_match_rec_of_tab.validation_org(i)--3503764
307 );
308
309 IF PG_DEBUG <> 0 THEN
310 oe_debug_pub.add(sql%rowcount||' rows inserted into bcol_gt',5);
311 END IF;
312
313
314 lStmtNum:=40;
315 BEGIN
316 SELECT 'Y'
317 INTO l_model_exists
318 FROM bom_cto_order_lines_gt
319 WHERE line_id = ato_line_id
320 AND top_model_line_id is not null
321 AND rownum = 1;
322 EXCEPTION
323 WHEN others THEN
324 l_model_exists :='N';
325 END;
326
327 --l_model_exists := 'N';
328 IF l_model_exists = 'Y' THEN --ato model and maybe atoitem
329 lStmtNum:=50;
330 CTO_MATCH_CONFIG.prepare_bcol_temp_data(
331 p_source => p_Source,
332 p_match_rec_of_tab => p_match_rec_of_tab,
333 x_return_status => x_return_status,
334 x_msg_count => X_msg_count,
335 x_msg_data => X_msg_data
336 );
337 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
338 --level 1
339 -- IF PG_DEBUG <> 0 THEN
340 oe_debug_pub.add('SUCCESS after CTO_MATCH_CONFIG.prepare_bcol_temp_data',1);
341 -- END IF;
342 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
343 RAISE fnd_api.g_exc_error;
344 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
345 RAISE fnd_api.g_exc_unexpected_error;
346 END IF;
347
348
349 IF l_PDS_ODS = 4 THEN --PDS
350
351 lStmtNum:=60;
352 /* Bugfix 9452600: This sql returns "ORA-01476: divisor is equal to zero"
353 if some of the lines in a configuration are cancelled.
354 UPDATE bom_cto_order_lines_gt child
355 SET qty_per_parent_model =
356 --used round to be consistent with can_configuration code
357 ( SELECT ROUND(child.ordered_quantity/parent.ordered_quantity,7)
358 FROM bom_cto_order_lines_gt parent
359 WHERE child.parent_ato_line_id= parent.line_id
360 )
361 --to filter out ato item order lines
362 WHERE top_model_line_id is not null;
363 */
364
365 UPDATE bom_cto_order_lines_gt child
366 SET qty_per_parent_model =
367 ( SELECT ROUND(DECODE(parent.ordered_quantity, 0 , 0, (child.ordered_quantity/parent.ordered_quantity)),7)
368 FROM bom_cto_order_lines_gt parent
369 WHERE child.parent_ato_line_id= parent.line_id
370 )
371 WHERE top_model_line_id IS NOT NULL;
372 --End bugfix 9452600
373
374 IF PG_DEBUG <> 0 THEN
375 oe_debug_pub.add('qty_per_parent_model is calculated for'||sql%rowcount||' rows in bcol_gt',5);
376 END IF;
377
378 --call reuse configuration API
379 lStmtNum:=70;
380 lReuseProfile := FND_PROFILE.Value('CTO_REUSE_CONFIG'); --Bugfix 6642016
381
382 IF PG_DEBUG <> 0 THEN
383 oe_debug_pub.add('Create_And_Link_Item: ' ||
384 ' Reuse Configuration profile: ' || to_char(lReuseProfile) , 5);
385 END IF; --Bugfix 6642016
386
387 if ( nvl(lReuseProfile,1) = 1 ) then ----Bugfix 6642016
388 lStmtNum:=80;
389 CTO_MATCH_CONFIG.CTO_REUSE_CONFIGURATION(
390 X_config_change =>l_config_change,
391 X_return_status =>x_return_status,
392 X_msg_count =>x_msg_count,
393 X_msg_data =>x_msg_data
394 ) ;
395 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
396 -- IF PG_DEBUG <> 0 THEN
397 oe_debug_pub.add('SUCCESS after CTO_MATCH_CONFIG.CTO_REUSE_CONFIGURATION',1);
398 -- END IF;
399 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
400 RAISE fnd_api.g_exc_error;
401 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
402 RAISE fnd_api.g_exc_unexpected_error;
403 END IF;
404 end if; --lReuseProfile = 1 Bugfix 6642016
405
406 IF l_match_profile = 1 THEN
407
408 lStmtNum:=30;
409
410 IF PG_DEBUG <> 0 THEN
411 oe_debug_pub.add('Calling call_match_item API ',1);
412 END IF;
413
414 --
415 -- bug 14328455
416 -- The ATP inquiry processing should not retain the locks
417 -- as it would result in contention. Hence moved the call to the
418 -- match API to a new procedure with Autonomous commit to ensure
419 -- that the lock is released immediately.
420 --
421 call_match_item(
422 p_api_version => 1.0,
423 x_return_status => x_return_status,
424 x_msg_count => X_msg_count,
425 x_msg_data => X_msg_data,
426 p_Action => p_Action,
427 p_Source => p_Source ,
428 p_cto_match_rec => p_match_rec_of_tab);
429
430 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
431 IF PG_DEBUG <> 0 THEN
432 oe_debug_pub.add('SUCCESS after CTO_Configured_Item_GRP.MATCH_CONFIGURED_ITEM',1);
433 END IF;
434 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
435 RAISE fnd_api.g_exc_error;
436 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
437 RAISE fnd_api.g_exc_unexpected_error;
438 END IF;
439
440 END IF; -- l_match_pfoile = 1
441
442 --get config_creation attr for OSS purposes
443 --for matched.re-used config_item only
444
445 --as per perf std ahmed almori
446 --If the global temporary table is referenced in a sub-query in a
447 --SQL statement which accesses other tables.
448 --In such cases, the join order may not be optimal due to the lack
449 --of stats on the temp table, hence hints should be used to ensure the optimal join order.
450
451 UPDATE /*+ INDEX (GT BOM_CTO_ORDER_LINES_GT_N5) */ bom_cto_order_lines_gt GT
452 SET GT.config_creation = ( SELECT nvl(MTL.config_orgs,1)--3555026
453 FROM mtl_system_items MTL
454 WHERE MTL.inventory_item_id = GT.inventory_item_id -- bugfix 3391383
455 AND MTL.organization_id = GT.validation_org--3555026
456 AND GT.bom_item_type = '1'
457 AND GT.config_item_id is not null
458 )
459 WHERE GT.bom_item_type = '1'
460 AND GT.config_item_id is not null;
461
462 IF PG_DEBUG <> 0 THEN
463 oe_debug_pub.add('Updated '||sql%rowcount||' model rows with config_orgs or config_creation attribute',5);
464 END IF;
465
466
467 END IF; -- l_pds_ods =4
468
469 END IF; --ato model and maybe atoitem
470
471
472 --make a call to option dependent sources api
473 lStmtNum:=80;
474 CTO_OSS_SOURCE_PK.GET_OSS_ORGS_LIST(
475 X_OSS_ORGS_LIST => X_oss_orgs_list,
476 X_RETURN_STATUS =>x_return_status,
477 X_MSG_DATA =>X_msg_data,
478 X_MSG_COUNT =>X_msg_count
479 );
480
481 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
482 --level 1
483 -- IF PG_DEBUG <> 0 THEN
484 oe_debug_pub.add('SUCCESS after CTO_OSS_SOURCE_PK.GET_OSS_ORGS_LIST',1);
485 -- END IF;
486 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
487 RAISE fnd_api.g_exc_error;
488 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
489 RAISE fnd_api.g_exc_unexpected_error;
490 END IF;
491
492 --make sure floowling sql bring null values too
493 lStmtNum:=90;
494 /*Commenting this sql as part of bugfix 8636348
495 SELECT oss_error_code,
496 config_item_id,
497 parent_ato_line_id,
498 gop_parent_ato_line_id,
499 bom_item_type,
500 wip_supply_type
501 BULK COLLECT INTO
502 p_match_rec_of_tab.oss_error_code,
503 --during Ut make sure next statement
504 --over writes existing values , ifnot
505 --additional rows may get created during
506 --for MATCH 0n cases. remove comment after UT
507 p_match_rec_of_tab.config_item_id,
508 p_match_rec_of_tab.parent_ato_line_id,
509 p_match_rec_of_tab.gop_parent_ato_line_id,
510 p_match_rec_of_tab.bom_item_type,
511 p_match_rec_of_tab.wip_supply_type
512 FROM bom_cto_order_lines_gt
513 ORDER BY line_id; --Bugfix 6055375*/
514
515 --Bugfix 8636348: The collection needs to be cleaned up before repopulating
516 IF PG_DEBUG <> 0 THEN
517 oe_debug_pub.add('CTOGOPIB: Before cleaning p_match_rec_of_tab');
518 END IF;
519
520 init_rec(p_match_rec_of_tab);
521
522 IF PG_DEBUG <> 0 THEN
523 oe_debug_pub.add('CTOGOPIB: After cleaning p_match_rec_of_tab');
524 END IF;
525
526 select
527 CONFIG_ITEM_ID,
528 LINE_ID,
529 LINK_TO_LINE_ID,
530 ATO_LINE_ID,
531 TOP_MODEL_LINE_ID,
532 INVENTORY_ITEM_ID,
533 COMPONENT_CODE,
534 COMPONENT_SEQUENCE_ID,
535 VALIDATION_ORG,
536 QTY_PER_PARENT_MODEL,
537 ORDERED_QUANTITY,
538 ORDER_QUANTITY_UOM,
539 PARENT_ATO_LINE_ID,
540 GOP_PARENT_ATO_LINE_ID,
541 PERFORM_MATCH,
542 PLAN_LEVEL,
543 BOM_ITEM_TYPE,
544 WIP_SUPPLY_TYPE,
545 OSS_ERROR_CODE,
546 SHIP_FROM_ORG_ID
547 BULK COLLECT INTO
548 p_match_rec_of_tab.CONFIG_ITEM_ID,
549 p_match_rec_of_tab.LINE_ID,
550 p_match_rec_of_tab.LINK_TO_LINE_ID,
551 p_match_rec_of_tab.ATO_LINE_ID,
552 p_match_rec_of_tab.TOP_MODEL_LINE_ID,
553 p_match_rec_of_tab.INVENTORY_ITEM_ID,
554 p_match_rec_of_tab.COMPONENT_CODE,
555 p_match_rec_of_tab.COMPONENT_SEQUENCE_ID,
556 p_match_rec_of_tab.VALIDATION_ORG,
557 p_match_rec_of_tab.QTY_PER_PARENT_MODEL,
558 p_match_rec_of_tab.ORDERED_QUANTITY,
559 p_match_rec_of_tab.ORDER_QUANTITY_UOM,
560 p_match_rec_of_tab.PARENT_ATO_LINE_ID,
561 p_match_rec_of_tab.GOP_PARENT_ATO_LINE_ID,
562 p_match_rec_of_tab.PERFORM_MATCH,
563 p_match_rec_of_tab.PLAN_LEVEL,
564 p_match_rec_of_tab.BOM_ITEM_TYPE,
565 p_match_rec_of_tab.WIP_SUPPLY_TYPE,
566 p_match_rec_of_tab.OSS_ERROR_CODE,
567 p_match_rec_of_tab.SHIP_FROM_ORG_ID
568 FROM bom_cto_order_lines_gt
569 ORDER BY line_id;
570 --End Bugfix 8636348
571
572
573 IF PG_DEBUG <> 0 THEN
574 oe_debug_pub.add('Line_id count=>'||p_match_rec_of_tab.line_id.count,5);
575 oe_debug_pub.add('config_id count=>'||p_match_rec_of_tab.config_item_id.count,5);
576 oe_debug_pub.add('oss error code count=>'||p_match_rec_of_tab.oss_error_code.count,5);
577 oe_debug_pub.add('wip supply_type=>'||p_match_rec_of_tab.WIP_SUPPLY_TYPE.count,5);
578
579 --oe_debug_pub.add('Matched item id=>'||p_match_rec_of_tab.config_item_id(1),5);
580 END IF;
581
582 lStmtNum:=95;
583 IF PG_DEBUG = 5 THEN
584 Oe_debug_pub.add(' config_item_id --'||
585 ' line_id --'||
586 ' link_to_line_id --'||
587 ' parent_ato_line_id --'||
588 ' gop_parent_ato_line_id --'||
589 ' ato_line_id --'||
590 ' top_model_line_id --'||
591 ' inventory_item_id --'||
592 ' ordered_qunatity --'||
593 ' qty_per_parent_model --'||
594 ' ship_from_org_id --'||
595 'validation_org--'||
596 ' plan_level --'||
597 ' wip_supply_type --'||
598 ' bom_item_type --'||
599 ' reuse_config --'||
600 ' perform_match --'||
601 ' config_creation --'||
602 ' option_specific --'||
603 ' oss_error_code ',5);
604 FOR debug_rec in c_debug
605 LOOP
606 oe_debug_pub.add(
607 debug_rec.config_item_id ||' -- '||
608 debug_rec.line_id ||' -- '||
609 debug_rec.link_to_line_id ||' -- '||
610 debug_rec.parent_ato_line_id ||' -- '||
611 debug_rec.gop_parent_ato_line_id ||' -- '||
612 debug_rec.ato_line_id ||' -- '||
613 debug_rec.top_model_line_id ||' -- '||
614 debug_rec.inventory_item_id ||' -- '||
615 debug_rec.ordered_quantity ||' -- '||
616 debug_rec.qty_per_parent_model ||' --'||
617 debug_rec.ship_from_org_id ||' -- '||
618 debug_rec.validation_org||' -- '||
619 debug_rec.plan_level ||' -- '||
620 debug_rec.wip_supply_type ||' -- '||
621 debug_rec.bom_item_type ||' -- '||
622 debug_rec.reuse_config ||' -- '||
623 debug_rec.perform_match ||' -- '||
624 debug_rec.config_creation ||' -- '||
625 debug_rec.option_specific ||' -- '||
626 debug_rec.oss_error_code ,5);
627 END LOOP;
628 END IF;--debug
629
630
631
632 lStmtNum:=100;
633
634 --need this when we make bcol_gt a session table
635 DELETE FROM bom_cto_order_lines_gt;
636
637 --Bugfix 8636348: Adding this portion for debug purposes
638 IF PG_DEBUG <> 0 THEN
639 oe_debug_pub.add('CTOGOPIB: After deleting records from bcol_gt');
640 END IF;
641
642 l_last_index := p_match_rec_of_tab.line_id.count;
643
644 IF PG_DEBUG = 5 THEN
645 oe_debug_pub.add('-----------------------------------------------------------');
646 oe_debug_pub.add('CTOGOPIB: Printing values in the record we pass to GOP');
647 FOR i IN 1..l_last_index LOOP
648 oe_debug_pub.add('line_id=>'|| p_match_rec_of_tab.line_id(i)||
649 ' LINK_TO_LINE_ID=>'|| p_match_rec_of_tab.LINK_TO_LINE_ID(i)||
650 ' ATO_LINE_ID=>'|| p_match_rec_of_tab.ATO_LINE_ID(i)||
651 ' TOP_MODEL_LINE_ID=>'|| p_match_rec_of_tab.TOP_MODEL_LINE_ID(i)||
652 ' PARENT_ATO_LINE_ID=>'|| p_match_rec_of_tab.PARENT_ATO_LINE_ID(i)||
653 ' GOP_PARENT_ATO_LINE_ID=>'|| p_match_rec_of_tab.GOP_PARENT_ATO_LINE_ID(i)||
654 ' CONFIG_ITEM_ID=>'|| p_match_rec_of_tab.CONFIG_ITEM_ID(i)||
655 ' INVENTORY_ITEM_ID=>'|| p_match_rec_of_tab.INVENTORY_ITEM_ID(i)||
656 ' QTY=>'|| p_match_rec_of_tab.ordered_quantity(i)||
657 ' ship_frm_org=>'|| p_match_rec_of_tab.SHIP_FROM_ORG_ID(i)||
658 ' validation_org=>'|| p_match_rec_of_tab.Validation_org(i)||
659 ' PERFORM_MATCH=>'|| p_match_rec_of_tab.PERFORM_MATCH(i)||
660 ' PLAN_LEVEL=>'|| p_match_rec_of_tab.PLAN_LEVEL(i), 5);
661 END LOOP;
662 oe_debug_pub.add('-----------------------------------------------------------');
663 oe_debug_pub.add('CTOGOPIB: After printing values.');
664 END IF;
665 --End debugging. Bugfix 8636348.
666
667 oe_debug_pub.add(' END TIME STAMP : '||to_char(sysdate,'hh:mi:ss')||' ',5);
668
669 EXCEPTION
670 WHEN fnd_api.g_exc_error THEN
671 IF PG_DEBUG <> 0 THEN
672 oe_debug_pub.add('CTO_GOP_WRAPPER_API: ' || 'Exception in stmt num: '
673 || to_char(lStmtNum), 1);
674 END IF;
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 -- Get message count and data
677 cto_msg_pub.count_and_get
678 ( p_msg_count => x_msg_count
679 , p_msg_data => x_msg_data
680 );
681 WHEN fnd_api.g_exc_unexpected_error THEN
682 IF PG_DEBUG <> 0 THEN
683 oe_debug_pub.add('CTO_GOP_WRAPPER_API: ' || ' Unexpected Exception in stmt num: '
684 || to_char(lStmtNum), 1);
685 END IF;
686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
687 -- Get message count and data
688 cto_msg_pub.count_and_get
689 ( p_msg_count => x_msg_count
690 , p_msg_data => x_msg_data
691 );
692 WHEN OTHERS then
693 IF PG_DEBUG <> 0 THEN
694 oe_debug_pub.add('CTO_GOP_WRAPPER_API: ' || 'Others Exception in stmt num: '
695 || to_char(lStmtNum), 1);
696 oe_debug_pub.add('error='||sqlerrm);
697 END IF;
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
699 -- Get message count and data
700 cto_msg_pub.count_and_get
701 ( p_msg_count => x_msg_count
702 , p_msg_data => x_msg_data
703 );
704 END CTO_GOP_WRAPPER_API;
705 END CTO_GOP_INTERFACE_PK;