[Home] [Help]
PACKAGE BODY: APPS.CTO_MSUTIL_PUB
Source
1 package body CTO_MSUTIL_PUB as
2 /* $Header: CTOMSUTB.pls 120.17.12020000.3 2013/03/21 11:47:04 abhissri ship $*/
3
4 /*----------------------------------------------------------------------------+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA
6 | All rights reserved.
7 | Oracle Manufacturing
8 |
9 |FILE NAME : CTOMSUTB.pls
10 |
11 |DESCRIPTION : Contains modules to :
12 | 1. Populate temporary tables bom_cto_order_lines and
13 | bom_cto_src_orgs, used for intermediate CTO processing
14 | 2. Update these tables with the config_item_id
15 | 3. Copy sourcing rule assignments from model to config item
16 |
17 |HISTORY : Created on 04-OCT-2003 by Sushant Sawant
18 |
19 | Modified on 09-JAN-2004 by Sushant Sawant
20 | Fixed Bug# 3349142
21 | fixed insert into bcso for dropship/procure/no assignment set scenarios.
22 |
23 |
24 | Modified on 12-FEB-2004 by Sushant Sawant
25 | Fixed Bug# 3418684
26 | Changed logic to not fork processing based in source_type
27 | Supply Chain will be traversed for CIB = 1,2 irrespective of source_type
28 |
29 | Modified : 02-MAR-2004 Sushant Sawant
30 | Fixed Bug 3472654
31 | upgrades for matched config from CIB = 1 or 2 to 3 were not performed properly.
32 | data was not transformed to bcmo.
33 | perform_match check includes 'Y' and 'U'
34 |
35 |
36 | Modified : 17-MAR-2004 Sushant Sawant
37 | Fixed bug 3504744.
38 | bom_parameter may not exist for some organizations.
39 |
40 | Modified : 29-APR-2004 Sushant Sawant
41 | Fixed bug 3598139
42 | changed cursor c_parent_src_orgs to account for buy models and their children as
43 | create_bom flag may not be set to 'Y' for such models.
44 |
45 |
46 | Modified : 14-MAY-2004 Sushant Sawant
47 | Fixed bug 3484511.
48 |
49 |
50 | Modified : 14-MAY-2004 Sushant Sawant
51 | Fixed bug 3640783. Sourcing across Operating Units with PO and OE
52 | validation org as part of the supply chain for CIB = 1 results in errors.
53 | This issue has been addressed as part of this fix.
54 |
55 | modfieid 26-JUL-2004 Kiran Konada
56 | 3785158
57 | values were not incremented properly corrected
58 |
59 | Modified : 14-APR-2005 Sushant Sawant
60 | Fixed bug fp bug 4227127. This is fp for bug 4162642.
61 | Exception handling added for call to get_other_orgs.
62 | Exception handling added to get_other_orgs procedure.
63 | Original issue of handling sparse and or empty array after deleting orgs from
64 | the validation org list was already handled in 11.5.10 as part of bug 3640783.
65 |
66
67 | Modified : 05-Jul-2005 Renga Kannan
68 | Modified for MOAC project
69 +-----------------------------------------------------------------------------*/
70
71 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_MSUTIL_PUB';
72 TYPE TAB_BCOL is TABLE of bom_cto_order_lines%rowtype index by binary_integer ;
73 gMrpAssignmentSet number ;
74
75 gUserId number := nvl(fnd_global.user_id, -1);
76 gLoginId number := nvl(fnd_global.login_id, -1);
77
78 type x_orgs_tbl_type is table of number index by binary_integer; --Bugfix 7522447/7410091
79 x_orgs_tbl x_orgs_tbl_type; --Bugfix 7522447/7410091
80
81
82 procedure process_sourcing_chain(
83 p_model_item_id IN number
84 , p_organization_id IN number
85 , p_line_id IN number
86 , p_top_ato_line_id IN number
87 , p_mode IN varchar2 default 'AUTOCONFIG'
88 , p_config_item_id IN number default NULL
89 , px_concat_org_id IN OUT NOCOPY varchar2
90 , x_return_status OUT NOCOPY varchar2
91 , x_msg_count OUT NOCOPY number
92 , x_msg_data OUT NOCOPY varchar2
93
94 ) ;
95
96
97 procedure insert_type3_bcso( p_top_ato_line_id in NUMBER
98 , p_model_line_id in NUMBER
99 , p_model_item_id in NUMBER
100 , p_config_item_id in NUMBER default null ) ;
101
102
103
104 procedure insert_type3_bcmo_bcso( p_top_ato_line_id in NUMBER
105 , p_model_line_id in NUMBER
106 , p_model_item_id in NUMBER) ;
107
108
109
110
111
112 procedure insert_type3_referenced_bcso( p_top_ato_line_id in NUMBER
113 , p_model_line_id in NUMBER
114 , p_model_item_id in NUMBER
115 , p_config_item_id in NUMBER default null ) ;
116
117
118 procedure procured_model_bcso_override ( p_line_id in number
119 , p_model_item_id in number
120 , p_ship_org_id in number ) ;
121
122
123 --
124 -- Forward Declarations
125 --
126 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
127
128
129
130 /*--------------------------------------------------------------------------+
131 This function identifies the model items for which configuration items need
132 to be created and populates the temporary table bom_cto_src_orgs with all the
133 organizations that each configuration item needs to be created in.
134 +-------------------------------------------------------------------------*/
135
136 FUNCTION Populate_Src_Orgs(pTopAtoLineId in number,
137 x_return_status OUT NOCOPY varchar2,
138 x_msg_count OUT NOCOPY number,
139 x_msg_data OUT NOCOPY varchar2)
140 RETURN integer
141 IS
142
143 lStmtNumber number;
144 lLineId number;
145 lShipFromOrgId number;
146 lStatus number;
147
148 cursor c_model_lines is
149 select line_id,
150 ato_line_id,
151 inventory_item_id,
152 plan_level,
153 config_creation,
154 perform_match,
155 config_item_id,
156 option_specific
157 from bom_cto_order_lines
158 where ato_line_id = pTopAtoLineId
159 and bom_item_type = 1
160 and nvl(wip_supply_type,0) <> 6
161 order by plan_level;
162
163 cursor c_parent_src_orgs is
164 select distinct bcso.organization_id
165 from bom_cto_src_orgs bcso,
166 bom_cto_order_lines bcol
167 where bcol.line_id = lLineId
168 and bcol.parent_ato_line_id = bcso.line_id
169 and ( bcso.create_bom = 'Y' or bcso.organization_type in ( '3' , '4') ) ; /* 3598139 Buy Models may not have a bom */
170
171 cursor c_debug is
172 select line_id,
173 model_item_id,
174 rcv_org_id,
175 organization_id,
176 create_bom,
177 create_src_rules,
178 organization_type,
179 group_reference_id
180 from bom_cto_src_orgs
181 where top_model_line_id = pTopAtoLineId;
182
183
184 cursor get_each_type1_model is
185 select line_id , inventory_item_id , config_creation from bom_cto_order_lines
186 where bom_item_type = 1 and nvl(wip_supply_type, 1) <> 6
187 and ato_line_id = pTopAtoLineId order by plan_level ;
188
189
190
191 v_t_org_list CTO_MSUTIL_PUB.org_list;
192
193 v_current_model_line_id number ;
194 v_current_model_item_id number ;
195 v_config_creation bom_cto_order_lines.config_creation%type ;
196
197
198 v_group_reference_id number ;
199 v_orgs_list cto_oss_source_pk.orgs_list ;
200 x_orgs_list CTO_MSUTIL_PUB.org_list;
201
202 BEGIN
203
204 IF PG_DEBUG <> 0 THEN
205 oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::pTopAtoLineId::'||to_char(pTopAtoLineId),1);
206 END IF;
207
208 --
209 -- For each model item in all possible receiving orgs, call
210 -- get_all_item_orgs to populate bom_cto_src_orgs
211 --
212
213 lStmtNumber := 20;
214 IF PG_DEBUG <> 0 THEN
215 oe_debug_pub.add('populate_plan_level: ' || 'before loop',2);
216 END IF;
217
218
219 FOR v_model_lines IN c_model_lines LOOP
220
221 if( v_model_lines.config_creation in ( 1, 2 ) ) then
222
223 IF PG_DEBUG <> 0 THEN
224 oe_debug_pub.add('populate_plan_level: ' || 'loop::item::'||to_char(v_model_lines.inventory_item_id)||
225 '::line_id::'||to_char(v_model_lines.line_id),2);
226 END IF;
227 lStmtNumber := 30;
228
229 IF v_model_lines.ato_line_id = v_model_lines.line_id THEN
230 IF PG_DEBUG <> 0 THEN
231 oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id = line_id',2);
232 END IF;
233
234
235 lStmtNumber := 40;
236 select ship_from_org_id
237 into lShipFromOrgId
238 from bom_cto_order_lines
239 where line_id = v_model_lines.line_id;
240
241 lStmtNumber := 50;
242 IF PG_DEBUG <> 0 THEN
243 oe_debug_pub.add('populate_plan_level: ' || 'before calling GAIO',2);
244
245 oe_debug_pub.add('populate_plan_level: ' || 'line_id::'||to_char(v_model_lines.line_id)||
246 '::inv_id::'||to_char(v_model_lines.inventory_item_id)||
247 '::ship_from_org::'||to_char(lShipFromOrgId),2);
248 END IF;
249
250
251 lStatus := get_all_item_orgs(v_model_lines.line_id,
252 v_model_lines.inventory_item_id,
253 lShipFromOrgId,
254 x_return_status,
255 x_msg_count,
256 x_msg_data);
257
258 IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
259 IF PG_DEBUG <> 0 THEN
260 oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
261 END IF;
262 raise FND_API.G_EXC_UNEXPECTED_ERROR;
263
264 ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
265 IF PG_DEBUG <> 0 THEN
266 oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
267 END IF;
268 raise FND_API.G_EXC_ERROR;
269 END IF;
270 IF PG_DEBUG <> 0 THEN
271 oe_debug_pub.add('populate_plan_level: ' || 'after calling GAIO::lStatus::'||to_char(lStatus),2);
272 END IF;
273 ELSE
274 IF PG_DEBUG <> 0 THEN
275 oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id <> line_id',2);
276 END IF;
277 lStmtNumber := 60;
278 lLineId := v_model_lines.line_id;
279 IF PG_DEBUG <> 0 THEN
280 oe_debug_pub.add('populate_plan_level: ' || 'before PSO loop',2);
281 END IF;
282
283 FOR v_parent_src_ogs IN c_parent_src_orgs LOOP
284 IF PG_DEBUG <> 0 THEN
285 oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::rcv org::'||
286 to_char(v_parent_src_ogs.organization_id),2);
287
288 oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
289 to_char(v_model_lines.inventory_item_id),2);
290
291 oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::line id::'||
292 to_char(v_model_lines.line_id),2);
293 END IF;
294 lStmtNumber := 70;
295 lStatus := get_all_item_orgs(v_model_lines.line_id,
296 v_model_lines.inventory_item_id,
297 v_parent_src_ogs.organization_id,
298 x_return_status,
299 x_msg_count,
300 x_msg_data);
301
302 IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
303 IF PG_DEBUG <> 0 THEN
304 oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
305 END IF;
306 raise FND_API.G_EXC_UNEXPECTED_ERROR;
307
308 ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
309 IF PG_DEBUG <> 0 THEN
310 oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
311 END IF;
312 raise FND_API.G_EXC_ERROR;
313 END IF;
314
315 END LOOP;
316 END IF;
317
318
319 else
320
321 oe_debug_pub.add( 'config creation 3 not yet implemented ' , 1) ;
322
323
324 oe_debug_pub.add( '$$$$$$$$ TYPE 3 model line ' || v_model_lines.line_id , 1 ) ;
325
326
327 if( v_model_lines.perform_match = 'N' ) then
328
329
330 oe_debug_pub.add( '$$$$$$$$ GOING TO CALL insert_type3_bcso ' || v_model_lines.line_id , 1 ) ;
331
332 CTO_MSUTIL_PUB.insert_type3_bcso( pTopAtoLineId
333 , v_model_lines.line_id
334 , v_model_lines.inventory_item_id ) ;
335
336 elsif( v_model_lines.perform_match in ( 'Y' , 'C' ) ) then
337
338
339 oe_debug_pub.add( '$$$$$$$$ GOING TO CALL insert_type3_referenced_bcso ' || v_model_lines.line_id , 1 ) ;
340
341 CTO_MSUTIL_PUB.insert_type3_referenced_bcso( pTopAtoLineId
342 , v_model_lines.line_id
343 , v_model_lines.inventory_item_id
344 , v_model_lines.config_item_id) ;
345
346 /* ACHTUNG: CHECK WHETHER YOU NEED A SHORT-CIRCUIT to type3_bcmo_bcso for no data found */
347
348
349 elsif( v_model_lines.perform_match = 'U' ) then
350
351
352 oe_debug_pub.add( '$$$$$$$$ GOING TO CALL insert_type3_bcmo_bcso ' || v_model_lines.line_id , 1 ) ;
353
354 CTO_MSUTIL_PUB.insert_type3_bcmo_bcso( pTopAtoLineId
355 , v_model_lines.line_id
356 , v_model_lines.inventory_item_id ) ;
357
358 end if ;
359
360
361
362
363
364
365
366 end if ; /* config_creation check */
367
368
369
370
371 oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id ) , 1 ) ;
372 oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.option_specific , 1 ) ;
373
374 if( v_model_lines.option_specific in ( '1' , '2', '3' ) ) then /* do not execute this code as we will be changing it tomorrow */
375
376 oe_debug_pub.add( 'Going to Call GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id) , 1 ) ;
377
378
379 cto_oss_source_pk.get_oss_bom_orgs( p_line_id => v_model_lines.line_id
380 ,x_orgs_list => v_orgs_list
381 ,x_return_status => x_return_status
382 ,x_msg_count => x_msg_count
383 ,x_msg_data => x_msg_data ) ;
384
385
386
387
388
389
390 if x_return_status = FND_API.G_RET_STS_ERROR then
391
392 IF PG_DEBUG <> 0 THEN
393 oe_debug_pub.add ('GET_ALL_ITEM_ORGS: ' ||
394 'Failed in cto_oss_source_pk.get_oss_bom_orgs with expected error.', 1);
395 END IF;
396
397 raise FND_API.G_EXC_ERROR;
398
399 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
400
401 IF PG_DEBUG <> 0 THEN
402 oe_debug_pub.add ('GET_ALL_ITEM_ORGS: ' ||
403 'Failed in cto_oss_source_pk.get_oss_bom_orgs with unexpected error.', 1);
404 END IF;
405
406 raise FND_API.G_EXC_UNEXPECTED_ERROR;
407
408 end if;
409
410 IF PG_DEBUG <> 0 THEN
411 oe_debug_pub.add('Create_And_Link_Item: ' || 'Ater Populate_Bcol', 5);
412 END IF;
413
414
415
416 oe_debug_pub.add( 'OSS ORGS for Create BOM list size ' || v_orgs_list.count , 1 ) ;
417
418 oe_debug_pub.add( 'OSS ORGS for Create BOM UPDATE ' , 1 ) ;
419
420
421 if( v_orgs_list.count > 0 ) then
422
423
424 if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
425
426 select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
427 where line_id = v_model_lines.line_id ;
428
429 update bom_cto_model_orgs set create_bom = 'N'
430 where group_reference_id = v_group_reference_id ;
431
432 oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
433 else
434
435
436 update bom_cto_src_orgs_b set create_bom = 'N'
437 where line_id = v_model_lines.line_id ;
438
439 oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
440
441 end if;
442
443
444
445 for i in 1..v_orgs_list.count
446 loop
447
448 oe_debug_pub.add( 'OSS ORGS for Create BOM ' || v_orgs_list(i) , 1 ) ;
449
450 if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
451
452 select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
453 where line_id = v_model_lines.line_id ;
454
455 update bom_cto_model_orgs set create_bom = 'Y'
456 where group_reference_id = v_group_reference_id and organization_id = v_orgs_list(i)
457 -- bugfix 4274446 : Check create_config_bom parameter
458 and exists
459 ( select 1 from bom_parameters
460 where organization_id = v_orgs_list(i)
461 and nvl(create_config_bom,'N') = 'Y' );
462
463 oe_debug_pub.add( 'UPDATED BCMO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
464
465 else
466
467
468 update bom_cto_src_orgs_b set create_bom = 'Y'
469 where line_id = v_model_lines.line_id and organization_id = v_orgs_list(i)
470 -- bugfix 4274446 : Check create_config_bom parameter
471 and exists
472 ( select 1 from bom_parameters
473 where organization_id = v_orgs_list(i)
474 and nvl(create_config_bom,'N') = 'Y' );
475
476 oe_debug_pub.add( 'UPDATED BCSO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
477
478 end if;
479
480
481 end loop ;
482
483
484 end if ; /* v_orgs_list.count > 0 */
485
486
487
488 end if;
489
490
491
492
493 oe_debug_pub.add( '$$$$$$$$$$$$ DONE GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.line_id , 1 ) ;
494
495
496
497
498
499
500 END LOOP;
501
502 IF PG_DEBUG <> 0 THEN
503 oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
504
505 oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
506
507 oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules organization_type group_reference_id ', 2);
508 END IF;
509
510 FOR v_debug IN c_debug LOOP
511 IF PG_DEBUG <> 0 THEN
512 oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
513 to_char(v_debug.model_item_id)||' '||
514 nvl(to_char(v_debug.rcv_org_id),null)||' '||
515 to_char(v_debug.organization_id)||' '||
516 nvl(v_debug.create_bom, null)||' '||
517 nvl(v_debug.create_src_rules, null) || ' ' ||
518 nvl(v_debug.organization_type, null) || ' ' ||
519 nvl(v_debug.group_reference_id , null), 2);
520 END IF;
521 END LOOP;
522
523
524
525 /* make a call to get validation orgs and purchasing related orgs */
526
527 oe_debug_pub.add( '$$$$$$$$ Additional Type 1 and 2 Processing ' , 1 ) ;
528
529 open get_each_type1_model ;
530
531 loop
532
533 fetch get_each_type1_model into v_current_model_line_id , v_current_model_item_id , v_config_creation ;
534
535 exit when get_each_type1_model%notfound ;
536
537 oe_debug_pub.add( '$$$$$$$$ calling ORg List for model line ' || v_current_model_line_id , 1 ) ;
538
539 if( v_config_creation = 1) then
540
541 oe_debug_pub.add( '$$$$$$$$ TYPE 1 model line ' || v_current_model_line_id , 1 ) ;
542
543 CTO_MSUTIL_PUB.get_other_orgs( pmodellineid => v_current_model_line_id ,
544 xorglst => v_t_org_list ,
545 x_return_status => x_return_status ,
546 x_msg_count => x_msg_count ,
547 x_msg_data => x_msg_data );
548
549 /* bugfix 4227127 fp for bug 4162642 :added return status check */
550 if x_return_status = FND_API.G_RET_STS_ERROR then
551
552 if PG_DEBUG <> 0 then
553 oe_debug_pub.add( 'ERROR: get_other_orgs api return expected error' , 1 ) ;
554 end if;
555
556 Raise FND_API.G_EXC_ERROR;
557
558 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
559
560 if PG_DEBUG <> 0 then
561 oe_debug_pub.add( 'ERROR: get_other_orgs api return unexpected error' , 1 ) ;
562 end if;
563
564 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
565
566 end if;
567
568
569
570 oe_debug_pub.add( '$$$$$$$$ ORg List for model line ' || v_current_model_line_id , 1 ) ;
571
572
573 CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id, v_t_org_list ) ;
574
575
576 oe_debug_pub.add( '$$$$$$$$ ORg List DONE for model line ' || v_current_model_line_id , 1 ) ;
577
578 -- Added by Renga Kannan on 15-Sep-2005
579 -- Added for ATG performance Project
580
581 CTO_MSUTIL_PUB.Get_Master_orgs(
582 p_model_line_id => v_current_model_line_id ,
583 x_orgs_list => x_orgs_list,
584 x_return_status => x_return_status,
585 x_msg_count => x_msg_count,
586 x_msg_data => x_msg_data);
587
588 if x_return_status = FND_API.G_RET_STS_ERROR then
589
590 if PG_DEBUG <> 0 then
591 oe_debug_pub.add( 'ERROR: get_Master_orgs api return expected error' , 1 ) ;
592 end if;
593
594 Raise FND_API.G_EXC_ERROR;
595
596 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
597
598 if PG_DEBUG <> 0 then
599 oe_debug_pub.add( 'ERROR: get_Master_orgs api return unexpected error' , 1 ) ;
600 end if;
601
602 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
603
604 end if;
605 If x_orgs_list.count <> 0 then
606 CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId,
607 v_current_model_line_id,
608 v_current_model_item_id,
609 x_orgs_list ) ;
610 End if;
611
612 elsif( v_config_creation = 2 ) then
613
614 oe_debug_pub.add( '$$$$$$$$ TYPE 2 model line ' || v_current_model_line_id , 1 ) ;
615
616 CTO_MSUTIL_PUB.insert_all_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id ) ;
617
618 end if;
619
620
621 end loop ;
622
623 close get_each_type1_model ;
624
625
626
627
628 oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
629
630 /*Update Create_BOM Flag for Shared Costing Organizations */
631 update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
632 where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
633 from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
634 where bcso_b.top_model_line_id = pTopAtoLineId
635 and bcol.ato_line_id = pTopAtoLineId
636 and bcol.line_id = bcso_b.line_id
637 and bcol.config_creation in ( 1 , 2 )
638 and mp.organization_id = bcso_b.organization_id
639 and mp.organization_id <> mp.cost_organization_id
640 and bcso_b.create_bom = 'Y' )
641 and exists ( select 1 from bom_parameters bp
642 where bp.organization_id = bcso_b1.organization_id
643 and bp.create_config_bom = 'Y' ) ;
644
645
646
647 oe_debug_pub.add( '$$$$$$$$ Create BOM flag updated for Shared Costing Organizations ' || to_char(sql%rowcount) , 1 ) ;
648
649
650
651 /* print debug output for Shared Cost update */
652 if( sql%rowcount > 0 ) then
653 FOR v_debug IN c_debug LOOP
654 IF PG_DEBUG <> 0 THEN
655 oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
656 to_char(v_debug.model_item_id)||' '||
657 nvl(to_char(v_debug.rcv_org_id),null)||' '||
658 to_char(v_debug.organization_id)||' '||
659 nvl(v_debug.create_bom, null)||' '||
660 nvl(v_debug.create_src_rules, null) || ' ' ||
661 nvl(v_debug.organization_type, null) || ' ' ||
662 nvl(v_debug.group_reference_id , null), 2);
663 END IF;
664 END LOOP;
665 end if;
666
667
668
669
670 return(1);
671
672 EXCEPTION
673
674 when FND_API.G_EXC_UNEXPECTED_ERROR then
675 IF PG_DEBUG <> 0 THEN
676 oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::unexp error::'||lStmtNumber||sqlerrm,1);
677 END IF;
678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
679 CTO_MSG_PUB.Count_And_Get
680 (p_msg_count => x_msg_count
681 ,p_msg_data => x_msg_data
682 );
683 return(0);
684
685 when FND_API.G_EXC_ERROR then
686 IF PG_DEBUG <> 0 THEN
687 oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::exp error::'||lStmtNumber||sqlerrm,1);
688 END IF;
689 x_return_status := FND_API.G_RET_STS_ERROR;
690 CTO_MSG_PUB.Count_And_Get
691 (p_msg_count => x_msg_count
692 ,p_msg_data => x_msg_data);
693 return(0);
694
695 when others then
696 IF PG_DEBUG <> 0 THEN
697 oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::others::'||lStmtNumber||sqlerrm,1);
698 END IF;
699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
701 FND_MSG_PUB.Add_Exc_Msg
702 (G_PKG_NAME
703 ,'populate_src_orgs'
704 );
705 END IF;
706 CTO_MSG_PUB.Count_And_Get
707 (p_msg_count => x_msg_count
708 ,p_msg_data => x_msg_data
709 );
710 return(0);
711
712 END Populate_Src_Orgs;
713
714
715 /*--------------------------------------------------------------------------+
716 This function identifies the model items for which configuration items need
717 to be created and populates the temporary table bom_cto_src_orgs with all the
718 organizations that each configuration item needs to be created in.
719 This will be called by Upgrade batch program.
720 +-------------------------------------------------------------------------*/
721
722 FUNCTION Populate_Src_Orgs_Upg(pTopAtoLineId in number,
723 x_return_status OUT NOCOPY varchar2,
724 x_msg_count OUT NOCOPY number,
725 x_msg_data OUT NOCOPY varchar2)
726 RETURN integer
727 IS
728
729 lStmtNumber number;
730 lLineId number;
731 lShipFromOrgId number;
732 lStatus number;
733
734 cursor c_model_lines is
735 select line_id,
736 ato_line_id,
737 inventory_item_id,
738 plan_level,
739 config_creation,
740 perform_match,
741 config_item_id,
742 option_specific
743 from bom_cto_order_lines_upg
744 where ato_line_id = pTopAtoLineId
745 and bom_item_type = 1
746 and nvl(wip_supply_type,0) <> 6
747 order by plan_level;
748
749 cursor c_parent_src_orgs is
750 select distinct bcso.organization_id
751 from bom_cto_src_orgs bcso,
752 bom_cto_order_lines_upg bcol
753 where bcol.line_id = lLineId
754 and bcol.parent_ato_line_id = bcso.line_id
755 and bcso.create_bom = 'Y';
756
757 cursor c_debug is
758 select line_id,
759 model_item_id,
760 rcv_org_id,
761 organization_id,
762 create_bom,
763 create_src_rules
764 from bom_cto_src_orgs
765 where top_model_line_id = pTopAtoLineId;
766
767
768 cursor get_each_type1_model is
769 select line_id , inventory_item_id , config_creation, config_item_id
770 from bom_cto_order_lines_upg
771 where bom_item_type = 1 and nvl(wip_supply_type, 1) <> 6
772 and ato_line_id = pTopAtoLineId order by plan_level ;
773
774
775
776 v_t_org_list CTO_MSUTIL_PUB.org_list;
777
778 v_current_model_line_id number ;
779 v_current_model_item_id number ;
780 v_current_config_item_id number;
781 v_config_creation bom_cto_order_lines_upg.config_creation%type ;
782
783
784 v_group_reference_id number ;
785 v_orgs_list cto_oss_source_pk.orgs_list ;
786 -- Added by Renga Kannan on 15-Sep-2005
787 -- For ATG performance Project
788 x_orgs_list CTO_MSUTIL_PUB.org_list;
789
790 BEGIN
791 --Bugfix 13362916
792 x_return_status := FND_API.G_RET_STS_SUCCESS;
793
794 IF PG_DEBUG <> 0 THEN
795 oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::pTopAtoLineId::'||to_char(pTopAtoLineId),1);
796 END IF;
797
798 --
799 -- For each model item in all possible receiving orgs, call
800 -- get_all_item_orgs to populate bom_cto_src_orgs
801 --
802
803 lStmtNumber := 20;
804 IF PG_DEBUG <> 0 THEN
805 oe_debug_pub.add('populate_plan_level: ' || 'before loop',2);
806 END IF;
807
808
809 FOR v_model_lines IN c_model_lines LOOP
810
811 if( v_model_lines.config_creation in ( 1, 2 ) ) then
812
813 IF PG_DEBUG <> 0 THEN
814 oe_debug_pub.add('populate_plan_level: ' || 'loop::item::'||to_char(v_model_lines.inventory_item_id)||
815 '::line_id::'||to_char(v_model_lines.line_id),2);
816 END IF;
817 lStmtNumber := 30;
818
819 IF v_model_lines.ato_line_id = v_model_lines.line_id THEN
820 IF PG_DEBUG <> 0 THEN
821 oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id = line_id',2);
822 END IF;
823
824
825 lStmtNumber := 40;
826 select ship_from_org_id
827 into lShipFromOrgId
828 from bom_cto_order_lines_upg
829 where line_id = v_model_lines.line_id;
830
831 lStmtNumber := 50;
832 IF PG_DEBUG <> 0 THEN
833 oe_debug_pub.add('populate_plan_level: ' || 'before calling GAIO',2);
834
835 oe_debug_pub.add('populate_plan_level: ' || 'line_id::'||to_char(v_model_lines.line_id)||
836 '::inv_id::'||to_char(v_model_lines.inventory_item_id)||
837 '::ship_from_org::'||to_char(lShipFromOrgId),2);
838 END IF;
839
840
841 lStatus := get_all_item_orgs(v_model_lines.line_id,
842 v_model_lines.inventory_item_id,
843 lShipFromOrgId,
844 x_return_status,
845 x_msg_count,
846 x_msg_data,
847 'UPGRADE',
848 v_model_lines.config_item_id);
849
850 IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
851 IF PG_DEBUG <> 0 THEN
852 oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
853 END IF;
854 raise FND_API.G_EXC_UNEXPECTED_ERROR;
855
856 ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
857 IF PG_DEBUG <> 0 THEN
858 oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
859 END IF;
860 raise FND_API.G_EXC_ERROR;
861 END IF;
862 IF PG_DEBUG <> 0 THEN
863 oe_debug_pub.add('populate_plan_level: ' || 'after calling GAIO::lStatus::'||to_char(lStatus),2);
864 END IF;
865 ELSE
866 IF PG_DEBUG <> 0 THEN
867 oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id <> line_id',2);
868 END IF;
869 lStmtNumber := 60;
870 lLineId := v_model_lines.line_id;
871 IF PG_DEBUG <> 0 THEN
872 oe_debug_pub.add('populate_plan_level: ' || 'before PSO loop',2);
873 END IF;
874
875 FOR v_parent_src_ogs IN c_parent_src_orgs LOOP
876 IF PG_DEBUG <> 0 THEN
877 oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::rcv org::'||
878 to_char(v_parent_src_ogs.organization_id),2);
879
880 oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
881 to_char(v_model_lines.inventory_item_id),2);
882
883 oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::line id::'||
884 to_char(v_model_lines.line_id),2);
885 END IF;
886 lStmtNumber := 70;
887 lStatus := get_all_item_orgs(v_model_lines.line_id,
888 v_model_lines.inventory_item_id,
889 v_parent_src_ogs.organization_id,
890 x_return_status,
891 x_msg_count,
892 x_msg_data,
893 'UPGRADE',
894 v_model_lines.config_item_id);
895
896 IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
897 IF PG_DEBUG <> 0 THEN
898 oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
899 END IF;
900 raise FND_API.G_EXC_UNEXPECTED_ERROR;
901
902 ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
903 IF PG_DEBUG <> 0 THEN
904 oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
905 END IF;
906 raise FND_API.G_EXC_ERROR;
907 END IF;
908
909 END LOOP;
910 END IF;
911
912
913 else
914
915 oe_debug_pub.add( '$$$$$$$$ TYPE 3 model line ' || v_model_lines.line_id , 1 ) ;
916
917 /* Fixed bug 3472654 */
918 if( v_model_lines.perform_match in ( 'Y' , 'U') ) then
919 CTO_MSUTIL_PUB.insert_type3_referenced_bcso( pTopAtoLineId
920 , v_model_lines.line_id
921 , v_model_lines.inventory_item_id
922 , v_model_lines.config_item_id) ;
923
924
925 else
926
927
928 CTO_MSUTIL_PUB.insert_type3_bcso( pTopAtoLineId
929 , v_model_lines.line_id
930 , v_model_lines.inventory_item_id
931 , v_model_lines.config_item_id) ;
932
933
934
935
936
937 end if;
938
939
940 end if ; /* config_creation check */
941
942
943 oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id ) , 1 ) ;
944 oe_debug_pub.add( '$$$$$$$$$$$$Going to START GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.option_specific , 1 ) ;
945
946 if( v_model_lines.option_specific in ( '1' , '2', '3' ) ) then
947
948 oe_debug_pub.add( 'Going to Call GET OSS BOM ORGS for Create BOM Indication ' || to_char( v_model_lines.line_id) , 1 ) ;
949
950
951 cto_oss_source_pk.get_oss_bom_orgs( p_line_id => v_model_lines.line_id
952 ,x_orgs_list => v_orgs_list
953 ,x_return_status => x_return_status
954 ,x_msg_count => x_msg_count
955 ,x_msg_data => x_msg_data ) ;
956
957
958
959
960
961
962 if x_return_status = FND_API.G_RET_STS_ERROR then
963
964 IF PG_DEBUG <> 0 THEN
965 oe_debug_pub.add ('GET_ALL_ITEM_ORGS: ' ||
966 'Failed in cto_oss_source_pk.get_oss_bom_orgs with expected error.', 1);
967 END IF;
968
969 raise FND_API.G_EXC_ERROR;
970
971 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
972
973 IF PG_DEBUG <> 0 THEN
974 oe_debug_pub.add ('GET_ALL_ITEM_ORGS: ' ||
975 'Failed in cto_oss_source_pk.get_oss_bom_orgs with unexpected error.', 1);
976 END IF;
977
978 raise FND_API.G_EXC_UNEXPECTED_ERROR;
979
980 end if;
981
982 IF PG_DEBUG <> 0 THEN
983 oe_debug_pub.add('Create_And_Link_Item: ' || 'Ater Populate_Bcol', 5);
984 END IF;
985
986
987
988 oe_debug_pub.add( 'OSS ORGS for Create BOM list size ' || v_orgs_list.count , 1 ) ;
989
990 oe_debug_pub.add( 'OSS ORGS for Create BOM UPDATE ' , 1 ) ;
991
992
993 if( v_orgs_list.count > 0 ) then
994
995
996 if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
997
998 select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
999 where line_id = v_model_lines.line_id ;
1000
1001 update bom_cto_model_orgs set create_bom = 'N'
1002 where group_reference_id = v_group_reference_id ;
1003
1004 oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1005 else
1006
1007
1008 update bom_cto_src_orgs_b set create_bom = 'N'
1009 where line_id = v_model_lines.line_id ;
1010
1011 oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1012
1013 end if;
1014
1015
1016
1017 for i in 1..v_orgs_list.count
1018 loop
1019
1020 oe_debug_pub.add( 'OSS ORGS for Create BOM ' || v_orgs_list(i) , 1 ) ;
1021
1022 if( v_model_lines.config_creation = 3 and v_model_lines.perform_match in ( 'Y', 'U' ) ) then
1023
1024 select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
1025 where line_id = v_model_lines.line_id ;
1026
1027 update bom_cto_model_orgs set create_bom = 'Y'
1028 where group_reference_id = v_group_reference_id and organization_id = v_orgs_list(i)
1029 -- bugfix 4274446 : Check create_config_bom parameter
1030 and exists
1031 ( select 1 from bom_parameters
1032 where organization_id = v_orgs_list(i)
1033 and nvl(create_config_bom,'N') = 'Y' );
1034
1035 oe_debug_pub.add( 'UPDATED BCMO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1036
1037 else
1038
1039
1040 update bom_cto_src_orgs_b set create_bom = 'Y'
1041 where line_id = v_model_lines.line_id and organization_id = v_orgs_list(i)
1042 -- bugfix 4274446 : Check create_config_bom parameter
1043 and exists
1044 ( select 1 from bom_parameters
1045 where organization_id = v_orgs_list(i)
1046 and nvl(create_config_bom,'N') = 'Y' );
1047
1048 oe_debug_pub.add( 'UPDATED BCSO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
1049
1050 end if;
1051
1052
1053 end loop ;
1054
1055
1056 end if ; /* v_orgs_list.count > 0 */
1057
1058
1059
1060 end if;
1061
1062
1063
1064
1065 oe_debug_pub.add( '$$$$$$$$$$$$ DONE GET OSS BOM ORGS for Create BOM Indication ' || v_model_lines.line_id , 1 ) ;
1066
1067
1068
1069 END LOOP;
1070
1071 IF PG_DEBUG <> 0 THEN
1072 oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
1073
1074 oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
1075
1076 oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules', 2);
1077 END IF;
1078
1079 FOR v_debug IN c_debug LOOP
1080 IF PG_DEBUG <> 0 THEN
1081 oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
1082 to_char(v_debug.model_item_id)||' '||
1083 nvl(to_char(v_debug.rcv_org_id),null)||' '||
1084 to_char(v_debug.organization_id)||' '||
1085 nvl(v_debug.create_bom, null)||' '||
1086 nvl(v_debug.create_src_rules, null), 2);
1087 END IF;
1088 END LOOP;
1089
1090
1091
1092
1093
1094 oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
1095
1096 /*Update Create_BOM Flag for Shared Costing Organizations */
1097 update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
1098 where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
1099 from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
1100 where bcso_b.top_model_line_id = pTopAtoLineId
1101 and bcol.ato_line_id = pTopAtoLineId
1102 and bcol.line_id = bcso_b.line_id
1103 and bcol.config_creation in ( 1 , 2 )
1104 and mp.organization_id = bcso_b.organization_id
1105 and mp.organization_id <> mp.cost_organization_id
1106 and bcso_b.create_bom = 'Y' )
1107 and exists ( select 1 from bom_parameters bp
1108 where bp.organization_id = bcso_b1.organization_id
1109 and bp.create_config_bom = 'Y' ) ;
1110
1111
1112
1113 oe_debug_pub.add( '$$$$$$$$ Create BOM flag updated for Shared Costing Organizations ' || to_char(sql%rowcount) , 1 ) ;
1114
1115
1116
1117 /* print debug output for Shared Cost update */
1118 if( sql%rowcount > 0 ) then
1119 FOR v_debug IN c_debug LOOP
1120 IF PG_DEBUG <> 0 THEN
1121 oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
1122 to_char(v_debug.model_item_id)||' '||
1123 nvl(to_char(v_debug.rcv_org_id),null)||' '||
1124 to_char(v_debug.organization_id)||' '||
1125 nvl(v_debug.create_bom, null)||' '||
1126 nvl(v_debug.create_src_rules, null), 2);
1127 END IF;
1128 END LOOP;
1129
1130 end if;
1131
1132
1133
1134 /* make a call to get validation orgs and purchasing related orgs */
1135
1136 oe_debug_pub.add( '$$$$$$$$ Additional Type 1 and 2 Processing ' , 1 ) ;
1137
1138 open get_each_type1_model ;
1139
1140 loop
1141
1142 fetch get_each_type1_model into v_current_model_line_id , v_current_model_item_id , v_config_creation , v_current_config_item_id;
1143
1144 exit when get_each_type1_model%notfound ;
1145
1146 oe_debug_pub.add( '$$$$$$$$ calling ORg List for model line ' || v_current_model_line_id , 1 ) ;
1147
1148 if( v_config_creation = 1) then
1149
1150 oe_debug_pub.add( '$$$$$$$$ TYPE 1 model line ' || v_current_model_line_id , 1 ) ;
1151
1152 CTO_MSUTIL_PUB.get_other_orgs( pmodellineid => v_current_model_line_id ,
1153 p_mode => 'UPG',
1154 xorglst => v_t_org_list ,
1155 x_return_status => x_return_status ,
1156 x_msg_count => x_msg_count ,
1157 x_msg_data => x_msg_data );
1158
1159
1160 /* bugfix 4162642 :added return status check */
1161 if x_return_status = FND_API.G_RET_STS_ERROR then
1162
1163 if PG_DEBUG <> 0 then
1164 oe_debug_pub.add( 'ERROR: get_other_orgs api return expected error' , 1 ) ;
1165 end if;
1166
1167 Raise FND_API.G_EXC_ERROR;
1168
1169 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1170
1171 if PG_DEBUG <> 0 then
1172 oe_debug_pub.add( 'ERROR: get_other_orgs api return unexpected error' , 1 ) ;
1173 end if;
1174
1175 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1176
1177 end if;
1178
1179
1180 oe_debug_pub.add( '$$$$$$$$ ORg List for model line ' || v_current_model_line_id , 1 ) ;
1181
1182
1183 CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id, v_t_org_list , v_current_config_item_id) ;
1184
1185
1186 oe_debug_pub.add( '$$$$$$$$ ORg List DONE for model line ' || v_current_model_line_id , 1 ) ;
1187 -- Added by Renga Kannan on 15-Sep-2005
1188 -- Added for ATG performance Project
1189
1190 CTO_MSUTIL_PUB.Get_Master_orgs(
1191 p_model_line_id => v_current_model_line_id ,
1192 x_orgs_list => x_orgs_list,
1193 x_return_status => x_return_status,
1194 x_msg_count => x_msg_count,
1195 x_msg_data => x_msg_data);
1196
1197 if x_return_status = FND_API.G_RET_STS_ERROR then
1198
1199 if PG_DEBUG <> 0 then
1200 oe_debug_pub.add( 'ERROR: get_Master_orgs api return expected error' , 1 ) ;
1201 end if;
1202
1203 Raise FND_API.G_EXC_ERROR;
1204
1205 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1206
1207 if PG_DEBUG <> 0 then
1208 oe_debug_pub.add( 'ERROR: get_Master_orgs api return unexpected error' , 1 ) ;
1209 end if;
1210
1211 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1212
1213 end if;
1214 If x_orgs_list.count <> 0 then
1215 CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId,
1216 v_current_model_line_id,
1217 v_current_model_item_id,
1218 x_orgs_list ) ;
1219 End if;
1220
1221
1222 elsif( v_config_creation = 2 ) then
1223
1224 oe_debug_pub.add( '$$$$$$$$ TYPE 2 model line ' || v_current_model_line_id , 1 ) ;
1225
1226 CTO_MSUTIL_PUB.insert_all_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id , v_current_config_item_id) ;
1227
1228 end if;
1229
1230
1231 end loop ;
1232
1233 close get_each_type1_model ;
1234
1235 return(1);
1236
1237 EXCEPTION
1238
1239 when FND_API.G_EXC_UNEXPECTED_ERROR then
1240 IF PG_DEBUG <> 0 THEN
1241 oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::unexp error::'||lStmtNumber||sqlerrm,1);
1242 END IF;
1243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1244 CTO_MSG_PUB.Count_And_Get
1245 (p_msg_count => x_msg_count
1246 ,p_msg_data => x_msg_data
1247 );
1248 return(0);
1249
1250 when FND_API.G_EXC_ERROR then
1251 IF PG_DEBUG <> 0 THEN
1252 oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::exp error::'||lStmtNumber||sqlerrm,1);
1253 END IF;
1254 x_return_status := FND_API.G_RET_STS_ERROR;
1255 CTO_MSG_PUB.Count_And_Get
1256 (p_msg_count => x_msg_count
1257 ,p_msg_data => x_msg_data);
1258 return(0);
1259
1260 when others then
1261 IF PG_DEBUG <> 0 THEN
1262 oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::others::'||lStmtNumber||sqlerrm,1);
1263 END IF;
1264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1265 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1266 FND_MSG_PUB.Add_Exc_Msg
1267 (G_PKG_NAME
1268 ,'populate_src_orgs'
1269 );
1270 END IF;
1271 CTO_MSG_PUB.Count_And_Get
1272 (p_msg_count => x_msg_count
1273 ,p_msg_data => x_msg_data
1274 );
1275 return(0);
1276
1277 END Populate_Src_Orgs_Upg;
1278
1279
1280 /*--------------------------------------------------------------------------+
1281 This function populates the table bom_cto_src_orgs with all the organizations
1282 in which a configuration item needs to be created.
1283 The organizations include all potential sourcing orgs, receiving orgs,
1284 OE validation org and PO validation org.
1285 The line_id, rcv_org_id, organization_id combination is unique.
1286 It is called by Populate_Src_Orgs.
1287 +-------------------------------------------------------------------------*/
1288
1289
1290 /*--------------------------------------------------------------------------+
1291 --- Modified by Renga Kannan on 08/21/01 for procuring configuration Phase I
1292 --- This function is modified to support Buy sourcing and source type
1293
1294 The following is the main logic for Procuring Configuration
1295
1296 1. The sourcing rule should not ignore the BUY sourcing rules.
1297 2. You can have more than one buy sourcing rule. But you cannot have any combinations
1298 3. For the Buy model and its children in bcol the Buy_item_type_flag is populated with Y.
1299 Otherwise it will be 'N'
1300 4. For Top Buy model in bcso the source_type is populated with 3.
1301 All of its child it is populated with 4.
1302 5. For the given parent_ato_model there will be only one row in the combination
1303 of create_bom = 'y' and source_type = 3.
1304 That will be the top buy model in that level.
1305 6. For Buy model and its lower level components the copy_src_rule will
1306 allways set to be 'Y' in bcol.
1307
1308 +-------------------------------------------------------------------------*/
1309
1310 FUNCTION Get_All_Item_Orgs( pLineId in number,
1311 pModelItemId in number,
1312 pRcvOrgId in number,
1313 x_return_status OUT NOCOPY varchar2,
1314 x_msg_count OUT NOCOPY number,
1315 x_msg_data OUT NOCOPY varchar2,
1316 p_mode in varchar2 default 'AUTOCONFIG',
1317 p_config_item_id in number default NULL )
1318 RETURN integer
1319 IS
1320
1321 gUserId number;
1322 gLoginId number;
1323 lStmtNumber number;
1324 lMrpAssignmentSet number;
1325 lTopAtoLineId number;
1326 lExists varchar2(10);
1327 lProfileVal number;
1328 lValidationOrg number;
1329 lPoVAlidationOrg number;
1330 l_curr_RcvOrgId number;
1331 l_curr_src_org number;
1332 l_curr_assg_type number;
1333 l_curr_rank number;
1334 l_circular_src varchar2(1);
1335
1336 -- Added by Renga Kannan to get the source_type value
1337
1338 l_source_type number;
1339 l_sourcing_rule_count number;
1340 l_parent_ato_line_id Number;
1341 l_make_buy_code number;
1342
1343 -- End of addition on 08/26/01 for procuring configuration
1344
1345 multiorg_error exception;
1346 po_multiorg_error exception;
1347 lProgramId bom_cto_order_lines.program_id%type ;
1348
1349 v_source_type_code oe_order_lines_all.source_type_code%type ;
1350
1351 CURSOR c_circular_src IS
1352 select 'Y'
1353 from bom_cto_src_orgs bcso
1354 where line_id = pLineId
1355 and model_item_id = pModelItemId
1356 and rcv_org_id = l_curr_src_org;
1357
1358
1359 lConfigCreation bom_cto_order_lines.config_creation%type ;
1360 lPerformMatch bom_cto_order_lines.perform_match%type ;
1361 lOptionSpecific bom_cto_order_lines.option_specific%type ;
1362
1363 vx_concat_org_id varchar2(200) ;
1364 v_group_reference_id number ;
1365 v_100_procured varchar2(1) := 'N' ;
1366
1367 BEGIN
1368 --Bugfix 13362916
1369 x_return_status := FND_API.G_RET_STS_SUCCESS;
1370
1371 --
1372 -- pLineId is the line_id of the model line
1373 --
1374
1375 lStmtNumber := 10;
1376 gUserId := nvl(fnd_global.user_id, -1);
1377 gLoginId := nvl(fnd_global.login_id, -1);
1378
1379 /* get top model's ato_line_id */
1380 --
1381 -- The column top_model_line_id in bom_cto_src_orgs is being used
1382 -- to store the ato_line_id of the top ATO model
1383 -- This change was required in order to support multiple ATO models
1384 -- under a PTO model
1385 --
1386
1387 -- Added by Renga Kannan on 08/26/01
1388 -- Get the buy_item_flag from bcol for the given line id.
1389 -- If the buy_item_flag = 'Y' that means this part of some buy model
1390 -- In that case we should not look for sourcing rules for this model
1391 -- We need to create the item in its parents org.
1392
1393 lStmtNumber := 20;
1394
1395 /*
1396 select ato_line_id,
1397 program_id
1398 into lTopAtoLineId,
1399 lProgramId
1400 from bom_cto_order_lines
1401 where line_id = pLineId;
1402 */
1403
1404 /* BUG#1957336 Changes introduced by sushant for preconfigure bom */
1405
1406 select ato_line_id,parent_ato_line_id, nvl(program_id,0)
1407 , config_creation , perform_match , option_specific /* added by sushant for preconfigure bom identification */
1408 into lTopAtoLineId,l_parent_ato_line_id, lProgramId
1409 , lConfigCreation, lPerformMatch , lOptionSpecific
1410 from bom_cto_order_lines
1411 where line_id = pLineId;
1412
1413 -- Get the source type of its parent Model line
1414 -- If the parent model is of buy type we should not look for
1415 -- sourcing this model
1416
1417 lStmtNumber := 25;
1418
1419 BEGIN
1420 Select organization_type
1421 Into l_source_type
1422 from bom_cto_src_orgs bcso
1423 where bcso.line_id = l_parent_ato_line_id
1424 and ( bcso.create_bom = 'Y' or bcso.organization_type in ( '3', '2'))
1425 and organization_id = pRcvOrgId ;
1426 EXCEPTION
1427 WHEN NO_DATA_FOUND THEN
1428 Null;
1429
1430 when too_many_rows then
1431 IF PG_DEBUG <> 0 THEN
1432 oe_debug_pub.add('get_all_item_orgs: ' || 'too_many_rows happens when make and buy exist' ||
1433 to_char(l_parent_ato_line_id ),2);
1434
1435 END IF;
1436
1437 l_source_type := 2 ;
1438 END;
1439
1440 IF PG_DEBUG <> 0 THEN
1441 oe_debug_pub.add('populate_plan_level: ' || 'top ato line id::'||to_char(lTopAtoLineId),2);
1442 oe_debug_pub.add('populate_plan_level: ' || 'rcv org id::'||to_char(pRcvOrgId),2);
1443 oe_debug_pub.add('populate_plan_level: ' || 'model item id::'||to_char(pModelItemId),2);
1444 END IF;
1445
1446 -- Added by Renga Kannan on 08/23/01 for procuring configuration
1447
1448 IF PG_DEBUG <> 0 THEN
1449 oe_debug_pub.add('populate_plan_level: ' || 'Parent ATO line id = '||l_parent_ato_line_id,1);
1450 oe_debug_pub.add('populate_plan_level: ' || 'Parent source type = '||l_source_type,1);
1451 END IF;
1452
1453 lStmtNumber := 28 ;
1454
1455 if( lProgramId = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1456 v_source_type_code := 'INTERNAL' ;
1457
1458 IF PG_DEBUG <> 0 THEN
1459 oe_debug_pub.add('populate_plan_level: ' || ' pc bom source type code = '|| v_source_type_code ,1);
1460 END IF;
1461 else
1462 select source_type_code
1463 into v_source_type_code
1464 from oe_order_lines_all
1465 where line_id = pLineId ;
1466
1467 IF PG_DEBUG <> 0 THEN
1468 oe_debug_pub.add('populate_plan_level: ' || ' non pc bom source type code = '|| v_source_type_code ,1);
1469 END IF;
1470
1471 end if ;
1472
1473 IF PG_DEBUG <> 0 THEN
1474 oe_debug_pub.add('populate_plan_level: ' || 'source type code = '|| v_source_type_code ,1);
1475 END IF;
1476
1477
1478 lStmtNumber := 30;
1479 /* get MRP's default assignment set */
1480 lMrpAssignmentSet := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
1481
1482 --- The following if condition is added by Renga Kannan
1483 --- If the line is part of buy model then we need not look at the sourcing info
1484 --- we can keep the parents rcv org as the org for this item also. This can be identified
1485 --- from the bom_cto_order_lines_table flag
1486
1487
1488 lStmtNumber := 32;
1489
1490 IF nvl(l_source_type,'2') in (3,4) THEN
1491 -- Since this is part of existing buy model
1492 -- Set the source_type to 4 which is the indication for child buy model
1493 -- Since we are not looking at the sourcing here we need to set this flag as Y so that the sourcing rule
1494 -- will be copied
1495 lStmtNumber := 35;
1496 l_source_type := 4;
1497
1498 IF PG_DEBUG <> 0 THEN
1499 oe_debug_pub.add('populate_plan_level: ' || ' This is part of Buy model... No need to look for sourcing...',1);
1500 END IF;
1501
1502
1503
1504 lStmtNumber := 220;
1505 insert into bom_cto_src_orgs_b
1506 (
1507 top_model_line_id,
1508 line_id,
1509 model_item_id,
1510 rcv_org_id,
1511 organization_id,
1512 create_bom,
1513 cost_rollup,
1514 organization_type,
1515 config_item_id,
1516 create_src_rules,
1517 rank,
1518 creation_date,
1519 created_by,
1520 last_update_date,
1521 last_updated_by,
1522 last_update_login,
1523 program_application_id,
1524 program_id,
1525 program_update_date
1526 )
1527 select
1528 lTopAtoLineId,
1529 pLineId,
1530 pModelItemId,
1531 pRcvOrgId,
1532 pRcvOrgId,
1533 -- 'Y' , /* this statement is executed for lower buy models */
1534 'N' , /* create bom should be no for org type 4 */
1535 -- decode( bp.create_config_bom , 'Y', decode( bbom.common_bill_sequence_id , null ,'N' , 'Y') , 'N' ) , -- create_bom
1536 'N' , /* cost rollup should be no for org type 4 */
1537 -- decode( l_source_type , 4 , 'N' , 6 , 'N' , 'Y' ) , -- cost_rollup
1538 l_source_type, -- org_type is used to store the source_type
1539 p_config_item_id, -- config_item_id
1540 decode(l_curr_assg_type,6,'Y',3,'Y','N'), -- create_src_rules
1541 NULL, -- rank, n/a
1542 sysdate, -- creation_date
1543 gUserId, -- created_by
1544 sysdate, -- last_update_date
1545 gUserId, -- last_updated_by
1546 gLoginId, -- last_update_login
1547 null, -- program_application_id,??
1548 null, -- program_id,??
1549 sysdate -- program_update_date
1550 from bom_parameters bp, bom_bill_of_materials bbom
1551 where bp.organization_id = pRcvOrgId
1552 and bp.organization_id = bbom.organization_id (+)
1553 and pModelItemId = bbom.assembly_item_id (+)
1554 and bbom.alternate_bom_designator is null
1555 and NOT EXISTS
1556 (select NULL
1557 from bom_cto_src_orgs_b
1558 where line_id = pLineId
1559 and organization_id = pRcvOrgId
1560 and rcv_org_id = pRcvOrgId
1561 and model_item_id = pModelItemId);
1562
1563 IF PG_DEBUG <> 0 THEN
1564 oe_debug_pub.add('Get_All_Item_Orgs: ' || 'Inserted in BCSO for procured child model same org id, rcv org id ' || SQL%rowcount
1565 || ' at stmt ' || to_char(lStmtNumber) ,2);
1566 oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
1567 || ' line ' || to_char(pLineId) ,2);
1568 END IF;
1569
1570 ELSE
1571 lStmtNumber := 40;
1572
1573 IF lMrpAssignmentSet is null THEN
1574 IF PG_DEBUG <> 0 THEN
1575 oe_debug_pub.add('populate_plan_level: ' || 'Default assignment set is null',1);
1576 END IF;
1577
1578 -- added by Renga Kannan on 08/21/01
1579 -- When there is no sourcing rule defined we need to check for the make_buy_type of the
1580 -- item to determine the buy model
1581
1582 lStmtNumber := 50;
1583
1584 -- The following select statement is modified by Renga Kannan
1585 -- On 12/21/01. The where condition organization_id is modified
1586
1587 select planning_make_buy_code
1588 into l_make_buy_code
1589 from MTL_SYSTEM_ITEMS
1590 where inventory_item_id = pModelItemId
1591 and organization_id = pRcvOrgId;
1592
1593 IF PG_DEBUG <> 0 THEN
1594 oe_debug_pub.add('populate_plan_level: ' || 'Make buy code:: 1 means make 2 means buy',1);
1595 oe_debug_pub.add('populate_plan_level: ' || 'Planning make buy code for this item is ='||to_char(l_make_buy_code),1);
1596 END IF;
1597
1598 IF l_make_buy_code = 2 then
1599 l_source_type := 3; ----- Buy Type
1600 END IF;
1601
1602 lStmtNumber := 60;
1603
1604 l_curr_src_org := pRcvOrgId ;
1605
1606 if( l_make_buy_code = 2) then
1607 l_source_type := 3 ;
1608 v_100_procured := 'Y' ;
1609 else
1610 l_source_type := 2 ;
1611 v_100_procured := 'N' ;
1612 end if;
1613
1614 l_curr_rank := null ;
1615
1616 lStmtNumber := 70;
1617
1618 insert into bom_cto_src_orgs_b
1619 (
1620 top_model_line_id,
1621 line_id,
1622 model_item_id,
1623 rcv_org_id,
1624 organization_id,
1625 create_bom,
1626 cost_rollup,
1627 organization_type, -- Used to store the source type
1628 config_item_id,
1629 create_src_rules,
1630 rank,
1631 creation_date,
1632 created_by,
1633 last_update_date,
1634 last_updated_by,
1635 last_update_login,
1636 program_application_id,
1637 program_id,
1638 program_update_date
1639 )
1640 select -- distinct
1641 ltopatolineid ,
1642 plineid ,
1643 pmodelitemid ,
1644 null ,
1645 l_curr_src_org,
1646 decode( bp.create_config_bom , 'Y',
1647 decode( bbom.common_bill_sequence_id , null ,'N' , 'Y') , 'N' ) , -- create_bom
1648 'Y', -- cost_rollup
1649 l_source_type, -- org_type is used to store the source type
1650 p_config_item_id, -- config_item_id
1651 decode(l_curr_assg_type, 6, 'Y', 3, 'Y', 'N'),
1652 l_curr_rank,
1653 sysdate, -- creation_date
1654 gUserId, -- created_by
1655 sysdate, -- last_update_date
1656 gUserId, -- last_updated_by
1657 gLoginId, -- last_update_login
1658 null, -- program_application_id,??
1659 null, -- program_id,??
1660 sysdate -- program_update_date
1661 from bom_parameters bp, bom_bill_of_materials bbom
1662 where bp.organization_id = pRcvOrgId
1663 and bp.organization_id = bbom.organization_id (+)
1664 and pModelItemId = bbom.assembly_item_id (+)
1665 and bbom.alternate_bom_designator is null
1666 and NOT EXISTS
1667 (select NULL
1668 from bom_cto_src_orgs_b
1669 where line_id = pLineId
1670 and organization_id = pRcvOrgId
1671 and model_item_id = pModelItemId);
1672
1673 IF PG_DEBUG <> 0 THEN
1674 oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || SQL%ROWCOUNT || ' at stmt ' || to_char(lStmtNumber) ,2);
1675 oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
1676 || ' line ' || to_char(plineid) , 2 );
1677 END IF;
1678
1679 ELSE
1680 lStmtNumber := 80;
1681
1682 IF PG_DEBUG <> 0 THEN
1683 oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
1684 END IF;
1685
1686 vx_concat_org_id := to_char( pRcvOrgId ) ;
1687
1688 lStmtNumber := 90;
1689
1690 process_sourcing_chain( pModelItemId
1691 , pRcvOrgId
1692 , plineid
1693 , ltopatolineid
1694 , p_mode
1695 , p_config_item_id
1696 , vx_concat_org_id
1697 , x_return_status
1698 , x_msg_count
1699 , x_msg_data );
1700
1701 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1702 IF PG_DEBUG <> 0 THEN
1703 oe_debug_pub.add('get_all_item_orgs: ' || 'process_sourcing_chain returned with unexp error',1);
1704 END IF;
1705 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1706
1707 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1708 IF PG_DEBUG <> 0 THEN
1709 oe_debug_pub.add('get_all_item_orgs: ' || 'process_sourcing_chain returned with exp error',1);
1710 END IF;
1711 raise FND_API.G_EXC_ERROR;
1712 END IF;
1713
1714 IF PG_DEBUG <> 0 THEN
1715 oe_debug_pub.add('get_all_item_orgs: ' || 'after calling process_sourcing_chain::x_return_status::'||x_return_status,2);
1716 END IF;
1717
1718 END IF; /* MRP profile is not null */
1719
1720 END IF; /* check for DROP SHIP , BUY_ITEM_FLAG is not Y */
1721
1722 lStmtNumber := 140;
1723 IF lMrpAssignmentSet is not null THEN
1724
1725 --
1726 -- If mrp_sources_v does not insert any rows into
1727 -- bom_cto_src_orgs, this means that no sourcing rules are set-up
1728 -- for this model item in this org. Assuming that in this case
1729 -- the item in this org is sourced from itself, inserting a row
1730 -- with the receiving org as the sourcing org
1731
1732
1733 lStmtNumber := 160;
1734 insert into bom_cto_src_orgs_b
1735 (
1736 top_model_line_id,
1737 line_id,
1738 model_item_id,
1739 rcv_org_id,
1740 organization_id,
1741 create_bom,
1742 cost_rollup,
1743 organization_type,
1744 config_item_id,
1745 create_src_rules,
1746 rank,
1747 creation_date,
1748 created_by,
1749 last_update_date,
1750 last_updated_by,
1751 last_update_login,
1752 program_application_id,
1753 program_id,
1754 program_update_date
1755 )
1756 select
1757 lTopAtoLineId,
1758 pLineId,
1759 pModelItemId,
1760 pRcvOrgId,
1761 pRcvOrgId,
1762 'N' , /* this statement is executed when there are onyly transfer from sourcing rules in shipping org */
1763 /*decode( bp.create_config_bom , 'Y', decode( bbom.common_bill_sequence_id , null ,'N' , 'Y') , 'N' ) , -- create_bom */
1764 decode( l_source_type , 4 , 'N' , 6 , 'N' , 'Y' ) , -- cost_rollup
1765 l_source_type, -- org_type is used to store the source_type
1766 p_config_item_id, -- config_item_id
1767 decode(l_curr_assg_type,6,'Y',3,'Y','N'), -- create_src_rules
1768 NULL, -- rank, n/a
1769 sysdate, -- creation_date
1770 gUserId, -- created_by
1771 sysdate, -- last_update_date
1772 gUserId, -- last_updated_by
1773 gLoginId, -- last_update_login
1774 null, -- program_application_id,??
1775 null, -- program_id,??
1776 sysdate -- program_update_date
1777 from bom_parameters bp, bom_bill_of_materials bbom
1778 where bp.organization_id = pRcvOrgId
1779 and bp.organization_id = bbom.organization_id (+)
1780 and pModelItemId = bbom.assembly_item_id (+)
1781 and bbom.alternate_bom_designator is null
1782 and NOT EXISTS
1783 (select NULL
1784 from bom_cto_src_orgs_b
1785 where line_id = pLineId
1786 and organization_id = pRcvOrgId
1787 and rcv_org_id = pRcvOrgId
1788 and model_item_id = pModelItemId);
1789
1790 IF PG_DEBUG <> 0 THEN
1791 oe_debug_pub.add('Get_All_Item_Orgs: ' || 'Inserted in BCSO for transfer same org id, rcv org id ' || SQL%rowcount
1792 || ' at stmt ' || to_char(lStmtNumber) ,2);
1793 oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
1794 || ' line ' || to_char(plineid) ,2);
1795 END IF;
1796
1797 lStmtNumber := 180;
1798 IF PG_DEBUG <> 0 THEN
1799 oe_debug_pub.add('get_all_item_orgs : ' || 'done recursive chain for model '||to_char(pmodelitemid ), 1);
1800 END IF;
1801
1802 -- Bugfix 16315202: Putting an end if here so that procured_model_bcso_override gets called in cases
1803 -- when the MRP: Default Sourcing Assignment Set is null.
1804 -- Consider the following scenario:
1805 -- model1 (Buy Type - No Sourcing Rule)
1806 -- .model2 (Buy Type - No Sourcing Rule)
1807 -- ..itemX
1808 -- The default sourcing assignment set is null. In this scenario, model2 will be inserted into bcso with
1809 -- l_source_type = 4 and create_bom set to N. During BOM creation, since this model doesn't have create_bom
1810 -- = Y in any org, the process ends with BOM not created in any organization error. So we need the flow
1811 -- to go into procured_model_bcso_override() function even if assignment set is not set.
1812 END IF;
1813
1814 IF PG_DEBUG <> 0 THEN
1815 oe_debug_pub.add( ' procured_model_bcso_override mode ' || p_mode , 1);
1816 END IF;
1817
1818 if ( p_mode = 'AUTOCONFIG' ) then
1819 oe_debug_pub.add( ' going to call procured_model_bcso_override ' , 1);
1820
1821 lStmtNumber := 200;
1822 procured_model_bcso_override( p_model_item_id => pModelItemId
1823 ,p_line_id => pLineId
1824 ,p_ship_org_id => pRcvOrgId ) ;
1825 end if;
1826
1827 -- Bugfix 16315202: This else is not needed. Added an end if before.
1828 -- else
1829 -- --
1830 -- -- If mrp_sources_v does not insert any rows into
1831 -- -- bom_cto_src_orgs, this means that no sourcing rules are set-up
1832 -- -- for this model item in this org. Assuming that in this case
1833 -- -- the item in this org is sourced from itself, inserting a row
1834 -- -- with the receiving org as the sourcing org
1835 -- null ;
1836 -- end if; /* check whether assignment set is null */
1837
1838 return(1);
1839
1840 EXCEPTION
1841 when FND_API.G_EXC_ERROR then
1842 IF PG_DEBUG <> 0 THEN
1843 oe_debug_pub.add('populate_plan_level: ' || 'Get_All_item_orgs::exp error::'||to_char(lStmtNumber)||'::'||sqlerrm,1);
1844 END IF;
1845 x_return_status := FND_API.G_RET_STS_ERROR;
1846 CTO_MSG_PUB.Count_And_Get
1847 (p_msg_count => x_msg_count
1848 ,p_msg_data => x_msg_data
1849 );
1850 return(0);
1851
1852 when FND_API.G_EXC_UNEXPECTED_ERROR then
1853 IF PG_DEBUG <> 0 THEN
1854 oe_debug_pub.add('populate_plan_level: ' || 'Get_All_item_orgs::unexp error::'||to_char(lStmtNumber)||'::'||sqlerrm,1);
1855 END IF;
1856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1857 CTO_MSG_PUB.Count_And_Get (
1858 p_msg_count => x_msg_count
1859 ,p_msg_data => x_msg_data
1860 );
1861 return(0);
1862
1863 when OTHERS then
1864 IF PG_DEBUG <> 0 THEN
1865 oe_debug_pub.add('populate_plan_level: ' || 'Get_All_item_orgs::others::'||to_char(lStmtNumber)||'::'||sqlerrm,1);
1866 END IF;
1867 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1868 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1869 FND_MSG_PUB.Add_Exc_Msg
1870 (G_PKG_NAME
1871 ,'Get_All_Item_Orgs'
1872 );
1873 END IF;
1874 CTO_MSG_PUB.Count_And_Get
1875 (p_msg_count => x_msg_count
1876 ,p_msg_data => x_msg_data
1877 );
1878 return(0);
1879
1880 end Get_All_Item_Orgs;
1881
1882
1883
1884
1885 procedure process_sourcing_chain(
1886 p_model_item_id IN number
1887 , p_organization_id IN number
1888 , p_line_id IN number
1889 , p_top_ato_line_id IN number
1890 , p_mode IN varchar2 default 'AUTOCONFIG'
1891 , p_config_item_id IN number default NULL
1892 , px_concat_org_id IN OUT NOCOPY varchar2
1893 , x_return_status OUT NOCOPY varchar2
1894 , x_msg_count OUT NOCOPY number
1895 , x_msg_data OUT NOCOPY varchar2
1896
1897 )
1898 is
1899
1900
1901
1902 v_t_sourcing_info SOURCING_INFO;
1903 v_buy_traversed boolean := false ;
1904 v_source_type mrp_sources_v.source_type%type ;
1905 l_make_buy_code mtl_system_items.planning_make_buy_code%type ;
1906
1907 l_curr_src_org mrp_sources_v.source_organization_id%type ;
1908 l_source_type mrp_sources_v.source_type%type ;
1909 l_curr_assg_type mrp_sources_v.assignment_type%type ;
1910 l_curr_rank mrp_sources_v.rank%type ;
1911 v_sourcing_rule_exists varchar2(10) ;
1912
1913
1914 lstmtnumber number ;
1915 x_exp_error_code varchar2(100) ;
1916
1917 lStmtNum number ;
1918
1919
1920 v_option_specific varchar2(1) ;
1921 v_px_concat_org_id varchar2(200) ;
1922
1923 v_bcso_count number ;
1924 v_circular_src_exists varchar2(10);
1925 v_bom_created varchar2(1) := 'N' ;
1926 v_recursive_call varchar2(1) := 'Y' ;
1927 v_100_procured varchar2(1) := 'Y' ;
1928
1929 v_org_check varchar2(200) ;
1930
1931 l_orgs_index number; --Bugfix 7522447/7410091
1932 j number; --Bugfix 7522447/7410091
1933
1934 BEGIN
1935
1936
1937
1938
1939 x_return_status := FND_API.G_RET_STS_SUCCESS;
1940
1941
1942
1943
1944 IF PG_DEBUG <> 0 THEN
1945 oe_debug_pub.add( 'Entered process sourcing chain ' , 1 ) ;
1946 oe_debug_pub.add( 'Entered process sourcing chain line ' || p_line_id , 1 ) ;
1947 oe_debug_pub.add( 'Entered process sourcing chain org' || p_organization_id , 1 ) ;
1948 oe_debug_pub.add( 'Entered process sourcing chain model item ' || p_model_item_id , 1 ) ;
1949 END IF ;
1950
1951
1952 if( to_char(p_organization_id ) = px_concat_org_id ) then
1953 v_recursive_call := 'N' ;
1954
1955 end if;
1956
1957 lStmtNum := 0;
1958 --Bugfix 7522447/7410091
1959 l_orgs_index := p_organization_id;
1960 x_orgs_tbl(l_orgs_index) := p_organization_id;
1961
1962 IF PG_DEBUG <> 0 THEN
1963 oe_debug_pub.add( 'Printing values in x_orgs_tbl' , 1 ) ;
1964
1965 if x_orgs_tbl.count > 0 then
1966 IF PG_DEBUG <> 0 THEN
1967 oe_debug_pub.add('Count '|| x_orgs_tbl.Count, 5);
1968 END IF;
1969
1970 j := x_orgs_tbl.first;
1971 WHILE j IS NOT null LOOP
1972 IF PG_DEBUG <> 0 THEN
1973 oe_debug_pub.add('x_sparse_tbl(j) '|| x_orgs_tbl(j));
1974 END IF;
1975 j := x_orgs_tbl.NEXT(j);
1976 END LOOP;
1977 end if;
1978 END IF;
1979 --Bugfix 7522447/7410091
1980
1981 lStmtNum := 1;
1982 v_buy_traversed := FALSE ;
1983
1984
1985 IF PG_DEBUG <> 0 THEN
1986 oe_debug_pub.add( 'calling query sourcing org ' , 1 ) ;
1987 END IF;
1988
1989
1990 if( p_mode = 'AUTOCONFIG' ) then
1991 select nvl( option_specific , 'N' ) into v_option_specific from bom_cto_order_lines
1992 where line_id = p_line_id ;
1993
1994 else
1995 select nvl( option_specific , 'N' ) into v_option_specific from bom_cto_order_lines_upg
1996 where line_id = p_line_id ;
1997
1998 end if ;
1999
2000
2001
2002 if( v_option_specific = 'N' ) then
2003
2004 query_sourcing_org_ms ( p_model_item_id
2005 , p_organization_id
2006 , v_sourcing_rule_exists
2007 , v_source_type
2008 , v_t_sourcing_info
2009 , x_exp_error_code
2010 , x_return_status );
2011
2012
2013 else
2014
2015
2016 CTO_OSS_SOURCE_PK.query_oss_sourcing_org( p_line_id => p_line_id,
2017 p_inventory_item_id => p_model_item_id,
2018 p_organization_id => p_organization_id,
2019 x_sourcing_rule_exists => v_sourcing_rule_exists,
2020 x_source_type => v_source_type ,
2021 x_t_sourcing_info => v_t_sourcing_info,
2022 x_exp_error_code => x_exp_error_code,
2023 x_return_status => x_return_status,
2024 x_msg_data => x_msg_data,
2025 x_msg_count => x_msg_count );
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035 end if;
2036
2037
2038
2039 if( p_mode = 'AUTOCONFIG' ) then
2040 v_100_procured := 'Y' ;
2041
2042 FOR i in 1..v_t_sourcing_info.source_type.count
2043 LOOP
2044
2045 if( v_t_sourcing_info.source_type(i) in ( 1, 2) ) then
2046 v_100_procured := 'N' ;
2047 exit ;
2048 end if ;
2049 END LOOP ;
2050
2051 else
2052
2053 v_100_procured := 'N' ;
2054
2055 end if;
2056
2057
2058
2059
2060
2061 IF PG_DEBUG <> 0 THEN
2062 oe_debug_pub.add( 'output query sourcing org rule ' || v_t_sourcing_info.sourcing_rule_id.count , 1 ) ;
2063 oe_debug_pub.add( 'output query sourcing org src org ' || v_t_sourcing_info.source_organization_id.count , 1 ) ;
2064 oe_debug_pub.add( 'output query sourcing org src type' || v_t_sourcing_info.source_type.count , 1 ) ;
2065 END IF;
2066
2067
2068
2069 if( v_t_sourcing_info.source_type.count > 0 ) then
2070
2071 FOR i in 1..v_t_sourcing_info.source_type.count
2072 LOOP
2073
2074 IF PG_DEBUG <> 0 THEN
2075 oe_debug_pub.add( 'output query sourcing org type ' || v_t_sourcing_info.source_type(i) , 1 ) ;
2076 END IF;
2077
2078 /* Reinitialize variables */
2079 l_curr_src_org := null ;
2080 l_source_type := null ;
2081 l_curr_rank := null ;
2082
2083 IF PG_DEBUG <> 0 THEN
2084 if( v_t_sourcing_info.source_type(i) = 1 ) then
2085
2086 oe_debug_pub.add( 'output query sourcing org type 1 ' , 1) ;
2087
2088 elsif ( v_t_sourcing_info.source_type(i) = 2 ) then
2089 oe_debug_pub.add( 'output query sourcing org type 2 ' , 1) ;
2090
2091
2092 elsif ( v_t_sourcing_info.source_type(i) = 3 ) then
2093
2094 oe_debug_pub.add( 'output query sourcing org type 3 ' , 1) ;
2095
2096
2097 else
2098
2099 oe_debug_pub.add( 'output query sourcing org type else ' , 1) ;
2100
2101 end if ;
2102 END IF;
2103
2104
2105
2106 if( v_t_sourcing_info.source_type(i) in ( 1, 2 ) ) then
2107
2108
2109 IF PG_DEBUG <> 0 THEN
2110 oe_debug_pub.add( ' came into type 1,2 ' , 1 ) ;
2111 END IF;
2112
2113
2114 begin
2115 lStmtNum := 1 ;
2116 l_curr_src_org := v_t_sourcing_info.source_organization_id(i) ;
2117 lStmtNum := 2 ;
2118 l_source_type := v_t_sourcing_info.source_type(i) ;
2119 lStmtNum := 3 ;
2120 l_curr_assg_type := v_t_sourcing_info.assignment_type(i) ;
2121 lStmtNum := 4 ;
2122 l_curr_rank := v_t_sourcing_info.rank(i) ;
2123
2124 exception
2125 when others then
2126
2127 IF PG_DEBUG <> 0 THEN
2128 oe_debug_pub.add( ' errored into type 1,2 at ' || lStmtNum || ' err ' || SQLERRM , 1 ) ;
2129 END IF;
2130 end ;
2131
2132 IF PG_DEBUG <> 0 THEN
2133 oe_debug_pub.add( ' value for l_curr_src_org ' || l_curr_src_org , 1 ) ;
2134 oe_debug_pub.add( ' value for l_source_type ' || l_source_type , 1 ) ;
2135 oe_debug_pub.add( ' value for l_curr_rank ' || l_curr_rank , 1 ) ;
2136 END IF;
2137
2138
2139
2140
2141
2142 if( l_source_type = 1 ) then
2143
2144 IF PG_DEBUG <> 0 THEN
2145 oe_debug_pub.add( 'going to check for circular sourcing ' , 1 ) ;
2146 /* check for circular sourcing in bcso */
2147 oe_debug_pub.add( 'CIRCULAR SOURCE CHECK ' || px_concat_org_id ) ;
2148 END IF;
2149
2150
2151 v_org_check := to_char(l_curr_src_org) ;
2152
2153 IF PG_DEBUG <> 0 THEN
2154 oe_debug_pub.add( 'CIRCULAR SOURCE CHECK px_concat_org_id ' || px_concat_org_id || ' v_org_check ' || v_org_check ) ;
2155 END IF;
2156
2157
2158 /* Commenting as part of Bugfix 7522447/7410091
2159 if( instr( px_concat_org_id , v_org_check ) > 0 ) then
2160 v_circular_src_exists := 'Y' ;
2161 IF PG_DEBUG <> 0 THEN
2162 oe_debug_pub.add( 'CIRCULAR SOURCE DETECTED ' ) ;
2163 END IF;
2164
2165 else
2166
2167 v_circular_src_exists := 'N' ;
2168
2169 end if ;*/
2170
2171 --Begin Bugfix 7522447/7410091
2172 if (x_orgs_tbl.exists(l_curr_src_org)) then
2173 lStmtNum := 6;
2174 v_circular_src_exists := 'Y';
2175 IF PG_DEBUG <> 0 THEN
2176 oe_debug_pub.add( 'CIRCULAR SOURCE DETECTED ' ) ;
2177 END IF;
2178
2179 else
2180 v_circular_src_exists := 'N' ;
2181 end if ;
2182 --End Bugfix 7522447/7410091
2183
2184
2185 if( v_circular_src_exists = 'Y' OR ( l_source_type = 1 and p_organization_id = l_curr_src_org) ) then
2186
2187 lStmtNum := 5;
2188 IF PG_DEBUG <> 0 THEN
2189 oe_debug_pub.add('process_sourcing_chain: ' || 'Circular sourcing defined for model '
2190 || to_char(p_model_item_id)
2191 || ' in org '
2192 ||to_char(l_curr_src_org) || ' via org ' || to_char(p_organization_id ) , 1);
2193
2194
2195 oe_debug_pub.add('process_sourcing_chain: ' || 'Circular sourcing additional info '
2196 || px_concat_org_id || '::' || to_char(l_curr_src_org) , 1) ;
2197
2198 END IF;
2199
2200 cto_msg_pub.cto_message('BOM','CTO_INVALID_SOURCING');
2201 raise FND_API.G_EXC_ERROR;
2202
2203
2204
2205 end if;
2206
2207 end if; /* l_source_type = 1 */
2208
2209
2210 IF PG_DEBUG <> 0 THEN
2211 oe_debug_pub.add( 'going to insert bcso for type 1,2 ' , 1 ) ;
2212 END IF;
2213
2214 lStmtNum := 10 ;
2215 insert into bom_cto_src_orgs_b
2216 (
2217 top_model_line_id,
2218 line_id,
2219 model_item_id,
2220 rcv_org_id,
2221 organization_id,
2222 create_bom,
2223 cost_rollup,
2224 organization_type, -- Used to store the source type
2225 config_item_id,
2226 create_src_rules,
2227 rank,
2228 creation_date,
2229 created_by,
2230 last_update_date,
2231 last_updated_by,
2232 last_update_login,
2233 program_application_id,
2234 program_id,
2235 program_update_date
2236 )
2237 select -- distinct
2238 p_top_ato_line_id,
2239 p_line_id,
2240 p_model_item_id,
2241 p_organization_id,
2242 l_curr_src_org,
2243 decode( l_source_type , 2 ,
2244 decode( bp.create_config_bom, 'Y', 'Y' , 'N' )
2245 , 'N' ), -- create_bom
2246 'Y', -- cost_rollup
2247 l_source_type, -- org_type is used to store the source type
2248 p_config_item_id , -- config_item_id
2249 decode(l_curr_assg_type, 6, 'Y', 3, 'Y', 'N'),
2250 l_curr_rank,
2251 sysdate, -- creation_date
2252 gUserId, -- created_by
2253 sysdate, -- last_update_date
2254 gUserId, -- last_updated_by
2255 gLoginId, -- last_update_login
2256 null, -- program_application_id,??
2257 null, -- program_id,??
2258 sysdate -- program_update_date
2259 from bom_parameters bp
2260 where bp.organization_id = l_curr_src_org
2261 and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
2262 (select NULL
2263 from bom_cto_src_orgs_b
2264 where line_id = p_line_id
2265 and rcv_org_id = p_organization_id
2266 and organization_id = l_curr_src_org
2267 and organization_type = l_source_type
2268 and model_item_id = p_model_item_id );
2269
2270
2271
2272
2273 IF PG_DEBUG <> 0 THEN
2274 oe_debug_pub.add( 'inserted bcso for type 1,2 ' || SQL%rowcount , 1 ) ;
2275 oe_debug_pub.add( 'inserted bcso for type 1,2 rcv ' || p_organization_id || ' org ' || l_curr_src_org , 1 ) ;
2276 END IF;
2277
2278
2279
2280
2281 elsif( v_t_sourcing_info.source_type(i) = 3 and NOT v_buy_traversed ) then
2282
2283 v_buy_traversed := TRUE ;
2284
2285 oe_debug_pub.add( ' came into type 3 ' , 1 ) ;
2286
2287 lStmtNum := 20 ;
2288
2289 begin
2290 lStmtNum := 21 ;
2291 l_curr_src_org := nvl( v_t_sourcing_info.source_organization_id(i) , p_organization_id ) ; /* could be null please check ?? */
2292 lStmtNum := 22 ;
2293 l_source_type := v_t_sourcing_info.source_type(i) ;
2294 lStmtNum := 23 ;
2295 l_curr_assg_type := v_t_sourcing_info.assignment_type(i) ;
2296 lStmtNum := 24 ;
2297 l_curr_rank := v_t_sourcing_info.rank(i) ;
2298
2299 exception
2300 when others then
2301
2302 IF PG_DEBUG <> 0 THEN
2303 oe_debug_pub.add( ' errored into type 3 at ' || lStmtNum || ' err ' || SQLERRM , 1 ) ;
2304 END IF;
2305
2306 end ;
2307
2308
2309
2310
2311 lStmtNum := 30 ;
2312
2313 insert into bom_cto_src_orgs_b
2314 (
2315 top_model_line_id,
2316 line_id,
2317 model_item_id,
2318 rcv_org_id,
2319 organization_id,
2320 create_bom,
2321 cost_rollup,
2322 organization_type, -- Used to store the source type
2323 config_item_id,
2324 create_src_rules,
2325 rank,
2326 creation_date,
2327 created_by,
2328 last_update_date,
2329 last_updated_by,
2330 last_update_login,
2331 program_application_id,
2332 program_id,
2333 program_update_date
2334 )
2335 select -- distinct
2336 p_top_ato_line_id,
2337 p_line_id,
2338 p_model_item_id,
2339 p_organization_id,
2340 l_curr_src_org,
2341 decode( v_100_procured , 'Y' , 'N' ,
2342 decode( bp.create_config_bom, 'Y', 'Y' , 'N')
2343 ) ,-- create_bom /* 100 % procured will be 'N' */
2344 'Y', -- cost_rollup
2345 l_source_type, -- org_type is used to store the source type
2346 p_config_item_id, -- config_item_id
2347 decode(l_curr_assg_type, 6, 'Y', 3, 'Y', 'N'),
2348 l_curr_rank,
2349 sysdate, -- creation_date
2350 gUserId, -- created_by
2351 sysdate, -- last_update_date
2352 gUserId, -- last_updated_by
2353 gLoginId, -- last_update_login
2354 null, -- program_application_id,??
2355 null, -- program_id,??
2356 sysdate -- program_update_date
2357 from bom_parameters bp
2358 where bp.organization_id = l_curr_src_org
2359 and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
2360 (select NULL
2361 from bom_cto_src_orgs_b
2362 where line_id = p_line_id
2363 and rcv_org_id = p_organization_id
2364 and organization_id = l_curr_src_org
2365 and organization_type = l_source_type
2366 and model_item_id = p_model_item_id );
2367
2368
2369
2370 IF PG_DEBUG <> 0 THEN
2371 oe_debug_pub.add( 'inserted bcso for type 3 ' || SQL%rowcount , 1 ) ;
2372 oe_debug_pub.add( 'inserted bcso for type 3 rcv ' || p_organization_id || ' org ' || l_curr_src_org , 1 ) ;
2373 END IF;
2374
2375
2376
2377
2378 end if;
2379
2380
2381 lStmtNum := 40 ;
2382
2383 if( v_t_sourcing_info.source_type(i) = 1 ) then
2384
2385
2386 oe_debug_pub.add( 'calling process sourcing chain recursive ' , 1 ) ;
2387
2388 lStmtNum := 50 ;
2389
2390 /* implemented using another variable as it is a multipath tree recursion */
2391 v_px_concat_org_id := px_concat_org_id || '::' || to_char( v_t_sourcing_info.source_organization_id(i)) ;
2392
2393
2394 process_sourcing_chain( p_model_item_id
2395 , v_t_sourcing_info.source_organization_id(i)
2396 , p_line_id
2397 , p_top_ato_line_id
2398 , p_mode
2399 , p_config_item_id
2400 , v_px_concat_org_id
2401 , x_return_status
2402 , x_msg_count
2403 , x_msg_data );
2404
2405
2406
2407
2408 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2409 IF PG_DEBUG <> 0 THEN
2410 oe_debug_pub.add('process_sourcing_chain: ' || 'process_sourcing_chain returned with unexp error',1);
2411 END IF;
2412 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2413
2414 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2415 IF PG_DEBUG <> 0 THEN
2416 oe_debug_pub.add('process_sourcing_chain: ' || 'process_sourcing_chain returned with exp error',1);
2417 END IF;
2418 raise FND_API.G_EXC_ERROR;
2419 END IF;
2420
2421
2422 IF PG_DEBUG <> 0 THEN
2423 oe_debug_pub.add('process_sourcing_chain: ' || 'after calling process_sourcing_chain::x_return_status::'||x_return_status,2);
2424 END IF;
2425
2426
2427 end if;
2428
2429
2430 END LOOP ;
2431
2432
2433 else
2434
2435 -- When there is no sourcing rule defined we need to check for the make_buy_type of the
2436 -- item to determine the buy model
2437
2438 -- if( v_source_type_code = 'INTERNAL' ) then
2439
2440
2441 IF PG_DEBUG <> 0 THEN
2442 oe_debug_pub.add('process_sourcing_chain : ' || 'NDF::End of chain for model '||to_char(p_model_item_id), 1);
2443 oe_debug_pub.add('process_sourcing_chain : ' || 'NDF::End of chain in org '|| p_organization_id , 1);
2444 END IF;
2445
2446
2447 lStmtNumber := 70;
2448
2449 -- When the item is not defined in the sourcing org it needs to be
2450 -- treated as INVALID sourcing
2451
2452 BEGIN
2453
2454 SELECT planning_make_buy_code
2455 INTO l_make_buy_code
2456 FROM MTL_SYSTEM_ITEMS
2457 WHERE inventory_item_id = p_model_item_id
2458 AND organization_id = p_organization_id ;
2459
2460 EXCEPTION
2461 WHEN NO_DATA_FOUND THEN
2462
2463 IF PG_DEBUG <> 0 THEN
2464 oe_debug_pub.add('process_sourcing_chain: ' || 'Inventory_item_id = '|| to_char(p_model_item_id ),1);
2465
2466 oe_debug_pub.add('process_sourcing_chain: ' || 'Organization id = '|| to_char(p_organization_id),1);
2467
2468 oe_debug_pub.add('process_sourcing_chain: ' || 'ERROR::The item is not defined in the sourcing org',1);
2469 END IF;
2470
2471
2472 -- The following message handling is modified by Renga Kannan
2473 -- We need to give the add for once to FND function and other
2474 -- to OE, in both cases we need to set the message again
2475 -- This is because if we not set the token once again the
2476 -- second add will not get the message.
2477
2478 cto_msg_pub.cto_message('BOM','CTO_INVALID_SOURCING');
2479 raise FND_API.G_EXC_ERROR;
2480
2481 END;
2482
2483
2484 lStmtNumber := 80;
2485
2486 l_curr_src_org := p_organization_id ;
2487
2488 if( l_make_buy_code = 2) then
2489
2490 l_source_type := 3 ;
2491 else
2492 l_source_type := 2 ;
2493 end if;
2494
2495
2496 if( p_mode = 'AUTOCONFIG' and l_source_type = 3 ) then
2497
2498 v_100_procured := 'Y' ;
2499
2500 else
2501
2502 v_100_procured := 'N' ;
2503 end if ;
2504
2505
2506 l_curr_rank := null ;
2507
2508 lStmtNumber := 90;
2509
2510 insert into bom_cto_src_orgs_b
2511 (
2512 top_model_line_id,
2513 line_id,
2514 model_item_id,
2515 rcv_org_id,
2516 organization_id,
2517 create_bom,
2518 cost_rollup,
2519 organization_type, -- Used to store the source type
2520 config_item_id,
2521 create_src_rules,
2522 rank,
2523 creation_date,
2524 created_by,
2525 last_update_date,
2526 last_updated_by,
2527 last_update_login,
2528 program_application_id,
2529 program_id,
2530 program_update_date
2531 )
2532 select -- distinct
2533 p_top_ato_line_id ,
2534 p_line_id ,
2535 p_model_item_id ,
2536 p_organization_id, /* will work for end of chain source or no source */
2537 p_organization_id,
2538 decode( v_100_procured , 'Y' , 'N' , decode( bp.create_config_bom , 'Y',
2539 decode(bom.assembly_item_id, null , 'N', 'Y')
2540 , 'N')) , -- create_bom
2541 'Y', -- cost_rollup
2542 l_source_type, -- org_type is used to store the source type
2543 p_config_item_id , -- config_item_id
2544 decode(l_curr_assg_type, 6, 'Y', 3, 'Y', 'N'),
2545 l_curr_rank,
2546 sysdate, -- creation_date
2547 gUserId, -- created_by
2548 sysdate, -- last_update_date
2549 gUserId, -- last_updated_by
2550 gLoginId, -- last_update_login
2551 null, -- program_application_id,??
2552 null, -- program_id,??
2553 sysdate -- program_update_date
2554 from bom_bill_of_materials bom, bom_parameters bp
2555 where p_organization_id = bp.organization_id
2556 and p_model_item_id = bom.assembly_item_id(+)
2557 and bp.organization_id = bom.organization_id(+)
2558 and bom.alternate_bom_designator is null
2559 and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
2560 (select NULL
2561 from bom_cto_src_orgs_b
2562 where line_id = p_line_id
2563 and rcv_org_id = p_organization_id
2564 and organization_id = p_organization_id
2565 and organization_type = l_source_type
2566 and model_item_id = p_model_item_id ) ;
2567
2568
2569 IF PG_DEBUG <> 0 THEN
2570 oe_debug_pub.add( 'inserted bcso for end of chain ' || SQL%rowcount , 1 ) ;
2571 oe_debug_pub.add( 'inserted bcso for end of chain ' || p_organization_id ||
2572 ' org ' || p_organization_id
2573 , 1 ) ;
2574 END IF;
2575
2576
2577
2578 end if;
2579
2580
2581
2582 IF PG_DEBUG <> 0 THEN
2583 oe_debug_pub.add('process_sourcing_chain: ' || 'end p_organization_id '||to_char(p_organization_id), 1);
2584 oe_debug_pub.add('process_sourcing_chain: ' || 'end px_concat_org_id '|| px_concat_org_id , 1);
2585 END IF;
2586
2587 x_orgs_tbl.delete(p_organization_id); --Bugfix 7522447/7410091
2588 IF PG_DEBUG <> 0 THEN
2589 oe_debug_pub.add('process_sourcing_chain: ' || 'Org deleted from collection: '||to_char(p_organization_id), 1);
2590 END IF;
2591
2592
2593
2594
2595 EXCEPTION
2596 WHEN fnd_api.g_exc_error THEN
2597 IF PG_DEBUG <> 0 THEN
2598 oe_debug_pub.add('process_sourcing_chain: ' || 'Exception in stmt num: '
2599 || to_char(lStmtNum), 1);
2600 END IF;
2601 x_return_status := FND_API.G_RET_STS_ERROR;
2602 -- Get message count and data
2603 cto_msg_pub.count_and_get
2604 ( p_msg_count => x_msg_count
2605 , p_msg_data => x_msg_data
2606 );
2607 WHEN fnd_api.g_exc_unexpected_error THEN
2608 IF PG_DEBUG <> 0 THEN
2609 oe_debug_pub.add('process_sourcing_chain: ' || ' Unexpected Exception in stmt num: '
2610 || to_char(lStmtNum), 1);
2611 END IF;
2612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2613 -- Get message count and data
2614 cto_msg_pub.count_and_get
2615 ( p_msg_count => x_msg_count
2616 , p_msg_data => x_msg_data
2617 );
2618 WHEN OTHERS then
2619 IF PG_DEBUG <> 0 THEN
2620
2621 oe_debug_pub.add('process_sourcing_chain: ' || 'Others Exception in stmt num: '
2622 || to_char(lStmtNum), 1);
2623 oe_debug_pub.add('process_sourcing_chain: ' || 'errormsg='||sqlerrm, 1);
2624 END IF;
2625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2626 -- Get message count and data
2627 cto_msg_pub.count_and_get
2628 ( p_msg_count => x_msg_count
2629 , p_msg_data => x_msg_data
2630 );
2631
2632 END process_sourcing_chain;
2633
2634
2635
2636 /*
2637 ** This procedure checks whether a model has been sourced.
2638 ** It also checks for circular sourcing and flags an error if it detects one.
2639 ** This procedure keeps on chaining sourcing rules till no more sourcing rules exist.
2640 */
2641
2642
2643
2644 PROCEDURE query_sourcing_org_ms(
2645 p_inventory_item_id NUMBER
2646 , p_organization_id NUMBER
2647 , p_sourcing_rule_exists OUT NOCOPY varchar2
2648 , p_source_type OUT NOCOPY NUMBER -- Added by Renga Kannan on 08/21/01
2649 , p_t_sourcing_info OUT NOCOPY SOURCING_INFO
2650 , x_exp_error_code OUT NOCOPY NUMBER
2651 , x_return_status OUT NOCOPY varchar2
2652 )
2653 is
2654 v_sourcing_rule_id number ;
2655 l_stmt_num number ;
2656 v_source_type varchar2(1) ;
2657 v_sourcing_rule_count number; -- Added by Renga Kannan on 08/21/01
2658
2659 l_make_buy_code number;
2660
2661
2662
2663 cursor item_sources is
2664 select distinct
2665 source_organization_id,
2666 sourcing_rule_id,
2667 nvl(source_type,1) ,
2668 rank,
2669 assignment_id,
2670 assignment_type
2671 from mrp_sources_v msv
2672 where msv.assignment_set_id = gMrpAssignmentSet
2673 and msv.inventory_item_id = p_inventory_item_id
2674 and msv.organization_id = p_organization_id
2675 -- and nvl(msv.source_type,1) <> 3 commented by Renga for BUY odel
2676 and nvl(effective_date,sysdate) <= nvl(disable_date, sysdate) -- Nvl fun is added by Renga Kannan on 05/05/2001
2677 and nvl(disable_date, sysdate+1) > sysdate;
2678
2679 begin
2680 /*
2681 ** This routine should consider no data found or one make at sourcing rule
2682 ** as no sourcing rule exist.
2683 */
2684 l_stmt_num := 1 ;
2685
2686 -- Added by Renga Kannan on 06/26/01
2687 -- The following initialize_assignment_set is used to initialize the global variable
2688
2689 IF gMrpAssignmentSet is null THEN
2690 IF PG_DEBUG <> 0 THEN
2691 oe_debug_pub.add('query_sourcing_org_ms: ' || 'Initializing the assignment set',5);
2692 END IF;
2693 initialize_assignment_set(x_return_status);
2694 if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2695 IF PG_DEBUG <> 0 THEN
2696 oe_debug_pub.add('query_sourcing_org_ms: ' || 'Error in initializing assignment set',5);
2697 END IF;
2698 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2699 end if;
2700 End IF;
2701
2702
2703 p_sourcing_rule_exists := FND_API.G_FALSE ;
2704 x_return_status := FND_API.G_RET_STS_SUCCESS ;
2705
2706
2707 -- Added by Renga Kannan on 08/27/01
2708 -- If the default assignment set is not defined then it needs to
2709 -- get the source type based on make or buy rule;
2710
2711 IF gMrpAssignmentSet is NULL Then
2712 SELECT planning_make_buy_code
2713 INTO l_make_buy_code
2714 FROM MTL_SYSTEM_ITEMS
2715 WHERE inventory_item_id = p_inventory_item_id
2716 AND organization_id = p_organization_id;
2717
2718 IF l_make_buy_code = 2 THEN
2719 p_source_type := 3;
2720 -- Renga Kannan added on 09/13/01 to set the sourcin_rule_exists
2721 -- Output value to Y even in the case of Buy attribute
2722 p_sourcing_rule_exists := FND_API.G_TRUE;
2723
2724 ELSE
2725 p_source_type := 2;
2726
2727
2728 END IF;
2729 return;
2730 END IF;
2731
2732
2733
2734 /*
2735 ** Fix for Bug 1610583
2736 ** Source Type values in MRP_SOURCES_V
2737 ** 1 = Transfer From, 2 = Make At, 3 = Buy From.
2738 */
2739
2740
2741 -- In the following sql the Where condition is fixed by Renga Kannan
2742 -- on 04/30/2001. If the sourcing is defined in the org level the source_type
2743 -- will be null. Still we need to see that sourcing rule. So the condition
2744 -- Source_type <> 3 is replaced with nvl(source_type,1). When the source_type is
2745 -- Null it will be defaulted to 1(Transfer from). As per the discussion with Sushant.
2746
2747
2748 /* Please note the changes done for procuring config project */
2749 -- Since the buy sourcing needs to be supported the where condition for msv.source_type is removed
2750 -- from the following query. This is done by Renga Kannan
2751
2752 l_stmt_num := 10 ;
2753
2754
2755
2756 open item_sources;
2757
2758
2759 -- loop
2760
2761 fetch item_sources bulk collect into p_t_sourcing_info.source_organization_id
2762 , p_t_sourcing_info.sourcing_rule_id
2763 , p_t_sourcing_info.source_type
2764 , p_t_sourcing_info.rank
2765 , p_t_sourcing_info.assignment_id
2766 , p_t_sourcing_info.assignment_type ;
2767
2768 -- exit when item_sources%notfound ;
2769
2770
2771 -- end loop ;
2772
2773 close item_sources ;
2774
2775
2776 oe_debug_pub.add('query_sourcing_org_ms: ' || '****$$$$ count ' || p_t_sourcing_info.source_organization_id.count , 1 ) ;
2777
2778
2779 for i in 1..p_t_sourcing_info.sourcing_rule_id.count
2780 loop
2781
2782 oe_debug_pub.add('query_sourcing_org_ms: ' || '****$$$$ org ' || p_t_sourcing_info.source_organization_id(i)
2783 || '****$$$$ rule ' || p_t_sourcing_info.sourcing_rule_id(i)
2784 || '****$$$$ type ' || p_t_sourcing_info.source_type(i)
2785 || '****$$$$ rank ' || p_t_sourcing_info.rank(i)
2786 || '****$$$$ assig id ' || p_t_sourcing_info.assignment_id(i) , 1 ) ;
2787 end loop ;
2788
2789
2790
2791 /*
2792 ** item is multi-org if sourcing rule is transfer from.
2793 */
2794 l_stmt_num := 20 ;
2795
2796 --- The following assignment stmt is added by Renga Kannan
2797 --- to pass back the source type value as parameter
2798
2799
2800 if( p_t_sourcing_info.sourcing_rule_id.count > 0 ) then
2801 p_sourcing_rule_exists := FND_API.G_TRUE ;
2802
2803 end if ;
2804
2805
2806
2807 EXCEPTION
2808 WHEN NO_DATA_FOUND THEN
2809 IF PG_DEBUG <> 0 THEN
2810 oe_debug_pub.add('query_sourcing_org_ms: ' || ' came into no data when finding source ' || to_char(l_stmt_num ) , 1 );
2811 END IF;
2812 /* removed no sourcing flag as cascading of sourcing rules will
2813 ** be continued till no more sourcing rules can be cascaded
2814 */
2815
2816 --- Added by Renga Kannan on 08/21/01
2817 --- When there is no sourcing rule defined we need to look at the
2818 --- Planning_make_buy_code to determine the source_type
2819 --- If the planning_make_buy_code is 1(Make) we can return as it is
2820 --- If the planning_make_buy_code is 2(Buy) we need to set the p_source_type to 3 and return
2821 --- so that the calling application will knwo this as buy model
2822
2823 SELECT planning_make_buy_code
2824 INTO l_make_buy_code
2825 FROM MTL_SYSTEM_ITEMS
2826 WHERE inventory_item_id = p_inventory_item_id
2827 AND organization_id = p_organization_id;
2828
2829 IF l_make_buy_code = 2 THEN
2830 p_source_type := 3;
2831 p_sourcing_rule_exists := FND_API.G_TRUE ;
2832 ELSE
2833 p_source_type := 2;
2834 END IF;
2835
2836
2837 ---- End of addition by Renga
2838
2839
2840 WHEN OTHERS THEN
2841 IF PG_DEBUG <> 0 THEN
2842 oe_debug_pub.add('query_sourcing_org_ms: ' || 'query_sourcing_org_ms::others:: ' ||
2843 to_char(l_stmt_num) || '::' ||
2844 ' came into others when finding source ' , 1 );
2845
2846 oe_debug_pub.add('query_sourcing_org_ms: ' || ' SQLCODE ' || SQLCODE , 1 );
2847
2848 oe_debug_pub.add('query_sourcing_org_ms: ' || ' SQLERRM ' || SQLERRM , 1 );
2849
2850 oe_debug_pub.add('query_sourcing_org_ms: ' || ' came into others when finding source ' , 1 );
2851 END IF;
2852
2853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2854
2855
2856
2857 end query_sourcing_org_ms ;
2858
2859
2860
2861
2862
2863
2864
2865
2866 /*--------------------------------------------------------------------------+
2867 This procedure creates sourcing information for a configuration item.
2868 It copies the sourcing rule assignment of the model into the configuration
2869 item and adds this assignment to the MRP default assignment set.
2870 +-------------------------------------------------------------------------*/
2871
2872
2873 PROCEDURE Create_Sourcing_Rules(pModelItemId in number,
2874 pConfigId in number,
2875 pRcvOrgId in number,
2876 x_return_status OUT NOCOPY varchar2,
2877 x_msg_count OUT NOCOPY number,
2878 x_msg_data OUT NOCOPY varchar2,
2879 p_mode in varchar2 default 'AUTOCONFIG' )
2880 IS
2881
2882 lStmtNum number;
2883 lMrpAssignmentSet number;
2884 lAssignmentId number;
2885 lAssignmentType number;
2886 lConfigAssignmentId number;
2887 lAssignmentExists number;
2888 lAssignmentRec MRP_Src_Assignment_PUB.Assignment_Rec_Type;
2889 lAssignmentTbl MRP_Src_Assignment_PUB.Assignment_Tbl_Type;
2890 lAssignmentSetRec MRP_Src_Assignment_PUB.Assignment_Set_Rec_Type;
2891 xAssignmentSetRec MRP_Src_Assignment_PUB.Assignment_Set_Rec_Type;
2892 xAssignmentSetValRec MRP_Src_Assignment_PUB.Assignment_Set_Val_Rec_Type;
2893 xAssignmentTbl MRP_Src_Assignment_PUB.Assignment_Tbl_Type;
2894 xAssignmentValTbl MRP_Src_Assignment_PUB.Assignment_Val_Tbl_Type;
2895 l_return_status varchar2(1);
2896 l_msg_count number;
2897 l_msg_data varchar2(2000);
2898
2899 No_sourcing_defined Exception;
2900
2901 lUPGAssignmentSet number;
2902 BEGIN
2903
2904 x_return_status := FND_API.G_RET_STS_SUCCESS;
2905 lAssignmentExists := 0;
2906
2907 lStmtNum := 10;
2908 /* get MRP's default assignment set */
2909 lMrpAssignmentSet := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
2910
2911
2912
2913
2914 if( p_mode = 'AUTOCONFIG' ) then
2915
2916 lUPGAssignmentSet := lMrpAssignmentSet ;
2917
2918 else
2919
2920 select assignment_set_id into lUPGAssignmentSet
2921 from mrp_assignment_sets
2922 where assignment_set_name = 'CTO Configuration Updates' ;
2923
2924 end if;
2925
2926
2927
2928
2929
2930 IF lMrpAssignmentSet is null THEN
2931 IF PG_DEBUG <> 0 THEN
2932 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Default assignment set is null, returning from create_sourcing_rules procedure',1);
2933 END IF;
2934 return;
2935 ELSE
2936 IF PG_DEBUG <> 0 THEN
2937 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
2938 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Upgrade assignment set is '||to_char(lUPGAssignmentSet),2);
2939 END IF;
2940 END IF;
2941
2942 --
2943 -- from mrp view, get Assignment_id of assignment to be copied
2944 --
2945
2946 -- The buy sourcing rules are also need to be selected.
2947 -- The where condition source_type <> 3 needs to be removed
2948 -- Changes are made by Renga Kannan on 08/22/01 for procuring config Change
2949
2950
2951
2952 -- Modified by Renga Kannan on 13-NOV-2001
2953 -- Added condition for assignment type = 3,6
2954 -- Previously it was erroring out if the assignment type is not of 3, 6
2955 -- It should not error out, rather it should igonre this
2956 -- this is required becasue of multiple buy support
2957
2958
2959 lStmtNum := 20;
2960
2961
2962 -- When no data found it not an exception in this case
2963 -- It may not have any sourcing for assignment_type 3,6 so we need not error out for this
2964
2965 -- Fixed FP bug 5156690
2966 -- MPR_SOURCES_V will return all the sourcing info including the ones
2967 -- that are defined in the item definition
2968 -- we should copy only the explicit sourcing assignments defined by users
2969 -- added another filter condition assignment_id is not null to select
2970 -- only explicit sourcing rules from mrp_sources_v view definition
2971
2972 BEGIN
2973
2974 select distinct assignment_id, assignment_type
2975 into lAssignmentId, lAssignmentType
2976 from mrp_sources_v msv
2977 where msv.assignment_set_id = lMrpAssignmentSet
2978 and msv.inventory_item_id = pModelItemId
2979 and msv.organization_id = pRcvOrgId
2980 and effective_date <= nvl(disable_date, sysdate)
2981 and nvl(disable_date, sysdate+1) > sysdate
2982 and assignment_type in (3,6)
2983 and assignment_id is not null;
2984
2985 EXCEPTION
2986 WHEN NO_DATA_FOUND THEN
2987
2988 IF PG_DEBUG <> 0 THEN
2989 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'There is no sourcing rule defined ',1);
2990 END IF;
2991 raise no_sourcing_defined;
2992
2993 END;
2994
2995
2996 IF PG_DEBUG <> 0 THEN
2997 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'lAssnType::'||to_char(lAssignmentType)||'::lAssnId::'||to_char(lAssignmentId),2);
2998 END IF;
2999
3000 --
3001 -- copy assignment into lAssignmentRec
3002 --
3003 lStmtNum := 30;
3004
3005 --
3006 -- bug 6617686
3007 -- The MRP API uses a ASSIGNMENT_ID = p_Assignment_Id OR
3008 -- ASSIGNMENT_SET_ID = p_Assignment_Set_Id that leads to
3009 -- a full table scan on MRP_SR_ASSIGNMENTS and consequent
3010 -- performance issues. Since CTO does not pass ASSIGNMENT_SET_ID
3011 -- into the procedure, it is performance effective to directly
3012 -- query the MRP table
3013 -- ntungare
3014 --
3015 -- lAssignmentRec := MRP_Assignment_Handlers.Query_Row(lAssignmentId);
3016
3017 -- bug 8789722
3018 --
3019 /*
3020 SELECT ASSIGNMENT_ID
3021 , ASSIGNMENT_SET_ID
3022 , ASSIGNMENT_TYPE
3023 , ATTRIBUTE1
3024 , ATTRIBUTE10
3025 , ATTRIBUTE11
3026 , ATTRIBUTE12
3027 , ATTRIBUTE13
3028 , ATTRIBUTE14
3029 , ATTRIBUTE15
3030 , ATTRIBUTE2
3031 , ATTRIBUTE3
3032 , ATTRIBUTE4
3033 , ATTRIBUTE5
3034 , ATTRIBUTE6
3035 , ATTRIBUTE7
3036 , ATTRIBUTE8
3037 , ATTRIBUTE9
3038 , ATTRIBUTE_CATEGORY
3039 , CATEGORY_ID
3040 , CATEGORY_SET_ID
3041 , CREATED_BY
3042 , CREATION_DATE
3043 , CUSTOMER_ID
3044 , INVENTORY_ITEM_ID
3045 , LAST_UPDATED_BY
3046 , LAST_UPDATE_DATE
3047 , LAST_UPDATE_LOGIN
3048 , ORGANIZATION_ID
3049 , PROGRAM_APPLICATION_ID
3050 , PROGRAM_ID
3051 , PROGRAM_UPDATE_DATE
3052 , REQUEST_ID
3053 , SECONDARY_INVENTORY
3054 , SHIP_TO_SITE_ID
3055 , SOURCING_RULE_ID
3056 , SOURCING_RULE_TYPE
3057 into lAssignmentRec.ASSIGNMENT_ID
3058 , lAssignmentRec.ASSIGNMENT_SET_ID
3059 , lAssignmentRec.ASSIGNMENT_TYPE
3060 , lAssignmentRec.ATTRIBUTE1
3061 , lAssignmentRec.ATTRIBUTE10
3062 , lAssignmentRec.ATTRIBUTE11
3063 , lAssignmentRec.ATTRIBUTE12
3064 , lAssignmentRec.ATTRIBUTE13
3065 , lAssignmentRec.ATTRIBUTE14
3066 , lAssignmentRec.ATTRIBUTE15
3067 , lAssignmentRec.ATTRIBUTE2
3068 , lAssignmentRec.ATTRIBUTE3
3069 , lAssignmentRec.ATTRIBUTE4
3070 , lAssignmentRec.ATTRIBUTE5
3071 , lAssignmentRec.ATTRIBUTE6
3072 , lAssignmentRec.ATTRIBUTE7
3073 , lAssignmentRec.ATTRIBUTE8
3074 , lAssignmentRec.ATTRIBUTE9
3075 , lAssignmentRec.ATTRIBUTE_CATEGORY
3076 , lAssignmentRec.CATEGORY_ID
3077 , lAssignmentRec.CATEGORY_SET_ID
3078 , lAssignmentRec.CREATED_BY
3079 , lAssignmentRec.CREATION_DATE
3080 , lAssignmentRec.CUSTOMER_ID
3081 , lAssignmentRec.INVENTORY_ITEM_ID
3082 , lAssignmentRec.LAST_UPDATED_BY
3083 , lAssignmentRec.LAST_UPDATE_DATE
3084 , lAssignmentRec.LAST_UPDATE_LOGIN
3085 , lAssignmentRec.ORGANIZATION_ID
3086 , lAssignmentRec.PROGRAM_APPLICATION_ID
3087 , lAssignmentRec.PROGRAM_ID
3088 , lAssignmentRec.PROGRAM_UPDATE_DATE
3089 , lAssignmentRec.REQUEST_ID
3090 , lAssignmentRec.SECONDARY_INVENTORY
3091 , lAssignmentRec.SHIP_TO_SITE_ID
3092 , lAssignmentRec.SOURCING_RULE_ID
3093 , lAssignmentRec.SOURCING_RULE_TYPE
3094 FROM MRP_SR_ASSIGNMENTS
3095 WHERE ASSIGNMENT_ID = lAssignmentId;*/
3096
3097 /*-------------------
3098 bug 8789722
3099 Start
3100 --------------------*/
3101 BEGIN
3102 IF pConfigId IS NOT NULL THEN
3103 SELECT ASSIGNMENT_ID
3104 , ASSIGNMENT_SET_ID
3105 , ASSIGNMENT_TYPE
3106 , ATTRIBUTE1
3107 , ATTRIBUTE10
3108 , ATTRIBUTE11
3109 , ATTRIBUTE12
3110 , ATTRIBUTE13
3111 , ATTRIBUTE14
3112 , ATTRIBUTE15
3113 , ATTRIBUTE2
3114 , ATTRIBUTE3
3115 , ATTRIBUTE4
3116 , ATTRIBUTE5
3117 , ATTRIBUTE6
3118 , ATTRIBUTE7
3119 , ATTRIBUTE8
3120 , ATTRIBUTE9
3121 , ATTRIBUTE_CATEGORY
3122 , CATEGORY_ID
3123 , CATEGORY_SET_ID
3124 , CREATED_BY
3125 , CREATION_DATE
3126 , CUSTOMER_ID
3127 , INVENTORY_ITEM_ID
3128 , LAST_UPDATED_BY
3129 , LAST_UPDATE_DATE
3130 , LAST_UPDATE_LOGIN
3131 , ORGANIZATION_ID
3132 , PROGRAM_APPLICATION_ID
3133 , PROGRAM_ID
3134 , PROGRAM_UPDATE_DATE
3135 , REQUEST_ID
3136 , SECONDARY_INVENTORY
3137 , SHIP_TO_SITE_ID
3138 , SOURCING_RULE_ID
3139 , SOURCING_RULE_TYPE
3140 into lAssignmentRec.ASSIGNMENT_ID
3141 , lAssignmentRec.ASSIGNMENT_SET_ID
3142 , lAssignmentRec.ASSIGNMENT_TYPE
3143 , lAssignmentRec.ATTRIBUTE1
3144 , lAssignmentRec.ATTRIBUTE10
3145 , lAssignmentRec.ATTRIBUTE11
3146 , lAssignmentRec.ATTRIBUTE12
3147 , lAssignmentRec.ATTRIBUTE13
3148 , lAssignmentRec.ATTRIBUTE14
3149 , lAssignmentRec.ATTRIBUTE15
3150 , lAssignmentRec.ATTRIBUTE2
3151 , lAssignmentRec.ATTRIBUTE3
3152 , lAssignmentRec.ATTRIBUTE4
3153 , lAssignmentRec.ATTRIBUTE5
3154 , lAssignmentRec.ATTRIBUTE6
3155 , lAssignmentRec.ATTRIBUTE7
3156 , lAssignmentRec.ATTRIBUTE8
3157 , lAssignmentRec.ATTRIBUTE9
3158 , lAssignmentRec.ATTRIBUTE_CATEGORY
3159 , lAssignmentRec.CATEGORY_ID
3160 , lAssignmentRec.CATEGORY_SET_ID
3161 , lAssignmentRec.CREATED_BY
3162 , lAssignmentRec.CREATION_DATE
3163 , lAssignmentRec.CUSTOMER_ID
3164 , lAssignmentRec.INVENTORY_ITEM_ID
3165 , lAssignmentRec.LAST_UPDATED_BY
3166 , lAssignmentRec.LAST_UPDATE_DATE
3167 , lAssignmentRec.LAST_UPDATE_LOGIN
3168 , lAssignmentRec.ORGANIZATION_ID
3169 , lAssignmentRec.PROGRAM_APPLICATION_ID
3170 , lAssignmentRec.PROGRAM_ID
3171 , lAssignmentRec.PROGRAM_UPDATE_DATE
3172 , lAssignmentRec.REQUEST_ID
3173 , lAssignmentRec.SECONDARY_INVENTORY
3174 , lAssignmentRec.SHIP_TO_SITE_ID
3175 , lAssignmentRec.SOURCING_RULE_ID
3176 , lAssignmentRec.SOURCING_RULE_TYPE
3177 FROM MRP_SR_ASSIGNMENTS A
3178 WHERE ASSIGNMENT_ID = lAssignmentId
3179 AND NOT EXISTS(SELECT /*+ INDEX(B MRP_SR_ASSIGNMENTS_U2)*/ 1
3180 FROM MRP_SR_ASSIGNMENTS B
3181 --Bugfix 14270815: Checking for the config assignment in lUPGAssignmentSet also.
3182 --WHERE a.assignment_set_id = b.assignment_set_id and
3183 WHERE (a.assignment_set_id = b.assignment_set_id or
3184 b.assignment_set_id = lUPGAssignmentSet) and
3185 a.assignment_type = b.assignment_type and
3186 nvl(b.organization_id,-1) = nvl(a.organization_id,-1) and
3187 nvl(b.customer_id,-1) = nvl(a.customer_id,-1) and
3188 nvl(b.ship_to_site_id,-1) = nvl(a.ship_to_site_id,-1) and
3189 b.sourcing_rule_type = a.sourcing_rule_type and
3190 b.inventory_item_id = pConfigId and
3191 nvl(b.category_id,-1) = nvl(a.category_id,-1) and
3192 rownum = 1);
3193 ELSE
3194 SELECT ASSIGNMENT_ID
3195 , ASSIGNMENT_SET_ID
3196 , ASSIGNMENT_TYPE
3197 , ATTRIBUTE1
3198 , ATTRIBUTE10
3199 , ATTRIBUTE11
3200 , ATTRIBUTE12
3201 , ATTRIBUTE13
3202 , ATTRIBUTE14
3203 , ATTRIBUTE15
3204 , ATTRIBUTE2
3205 , ATTRIBUTE3
3206 , ATTRIBUTE4
3207 , ATTRIBUTE5
3208 , ATTRIBUTE6
3209 , ATTRIBUTE7
3210 , ATTRIBUTE8
3211 , ATTRIBUTE9
3212 , ATTRIBUTE_CATEGORY
3213 , CATEGORY_ID
3214 , CATEGORY_SET_ID
3215 , CREATED_BY
3216 , CREATION_DATE
3217 , CUSTOMER_ID
3218 , INVENTORY_ITEM_ID
3219 , LAST_UPDATED_BY
3220 , LAST_UPDATE_DATE
3221 , LAST_UPDATE_LOGIN
3222 , ORGANIZATION_ID
3223 , PROGRAM_APPLICATION_ID
3224 , PROGRAM_ID
3225 , PROGRAM_UPDATE_DATE
3226 , REQUEST_ID
3227 , SECONDARY_INVENTORY
3228 , SHIP_TO_SITE_ID
3229 , SOURCING_RULE_ID
3230 , SOURCING_RULE_TYPE
3231 into lAssignmentRec.ASSIGNMENT_ID
3232 , lAssignmentRec.ASSIGNMENT_SET_ID
3233 , lAssignmentRec.ASSIGNMENT_TYPE
3234 , lAssignmentRec.ATTRIBUTE1
3235 , lAssignmentRec.ATTRIBUTE10
3236 , lAssignmentRec.ATTRIBUTE11
3237 , lAssignmentRec.ATTRIBUTE12
3238 , lAssignmentRec.ATTRIBUTE13
3239 , lAssignmentRec.ATTRIBUTE14
3240 , lAssignmentRec.ATTRIBUTE15
3241 , lAssignmentRec.ATTRIBUTE2
3242 , lAssignmentRec.ATTRIBUTE3
3243 , lAssignmentRec.ATTRIBUTE4
3244 , lAssignmentRec.ATTRIBUTE5
3245 , lAssignmentRec.ATTRIBUTE6
3246 , lAssignmentRec.ATTRIBUTE7
3247 , lAssignmentRec.ATTRIBUTE8
3248 , lAssignmentRec.ATTRIBUTE9
3249 , lAssignmentRec.ATTRIBUTE_CATEGORY
3250 , lAssignmentRec.CATEGORY_ID
3251 , lAssignmentRec.CATEGORY_SET_ID
3252 , lAssignmentRec.CREATED_BY
3253 , lAssignmentRec.CREATION_DATE
3254 , lAssignmentRec.CUSTOMER_ID
3255 , lAssignmentRec.INVENTORY_ITEM_ID
3256 , lAssignmentRec.LAST_UPDATED_BY
3257 , lAssignmentRec.LAST_UPDATE_DATE
3258 , lAssignmentRec.LAST_UPDATE_LOGIN
3259 , lAssignmentRec.ORGANIZATION_ID
3260 , lAssignmentRec.PROGRAM_APPLICATION_ID
3261 , lAssignmentRec.PROGRAM_ID
3262 , lAssignmentRec.PROGRAM_UPDATE_DATE
3263 , lAssignmentRec.REQUEST_ID
3264 , lAssignmentRec.SECONDARY_INVENTORY
3265 , lAssignmentRec.SHIP_TO_SITE_ID
3266 , lAssignmentRec.SOURCING_RULE_ID
3267 , lAssignmentRec.SOURCING_RULE_TYPE
3268 FROM MRP_SR_ASSIGNMENTS A
3269 WHERE ASSIGNMENT_ID = lAssignmentId
3270 AND NOT EXISTS(SELECT /*+ INDEX(B MRP_SR_ASSIGNMENTS_U2)*/ 1
3271 FROM MRP_SR_ASSIGNMENTS B
3272 --Bugfix 14270815: Checking for the config assignment in lUPGAssignmentSet also.
3273 --WHERE a.assignment_set_id = b.assignment_set_id and
3274 WHERE (a.assignment_set_id = b.assignment_set_id or
3275 b.assignment_set_id = lUPGAssignmentSet) and
3276 a.assignment_type = b.assignment_type and
3277 nvl(b.organization_id,-1) = nvl(a.organization_id,-1) and
3278 nvl(b.customer_id,-1) = nvl(a.customer_id,-1) and
3279 nvl(b.ship_to_site_id,-1) = nvl(a.ship_to_site_id,-1) and
3280 b.sourcing_rule_type = a.sourcing_rule_type and
3281 b.inventory_item_id IS NULL and
3282 nvl(b.category_id,-1) = nvl(a.category_id,-1) and
3283 rownum = 1);
3284 END IF;
3285 EXCEPTION
3286 WHEN NO_DATA_FOUND THEN
3287
3288 IF PG_DEBUG <> 0 THEN
3289 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment exists already, do not recreate go to end of loop',2);
3290 END IF;
3291
3292 RETURN;
3293
3294 WHEN OTHERS THEN
3295 IF PG_DEBUG <> 0 THEN
3296 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'others exception while checking ifassignment exists, not handling, creating assignment:: '||sqlerrm,2);
3297 END IF;
3298 END;
3299
3300 IF PG_DEBUG <> 0 THEN
3301 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'after query row',2);
3302 END IF;
3303
3304 --
3305 -- check if this assignment already exists for config item
3306 --
3307 lStmtNum := 35;
3308 -- BEGIN
3309 --
3310 -- IF PG_DEBUG <> 0 THEN
3311 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
3312 --
3313 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
3314 --
3315 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
3316 --
3317 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
3318 --
3319 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
3320 --
3321 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
3322 --
3323 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
3324 --
3325 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
3326 -- END IF;
3327 --
3328 -- -- bug 6617686
3329 -- IF pConfigId IS NOT NULL THEN
3330 -- select 1
3331 -- into lAssignmentExists
3332 -- from mrp_sr_assignments
3333 -- where assignment_set_id = lUPGAssignmentSet /* lAssignmentRec.assignment_set_id commented for upgrade logic */
3334 -- and assignment_type = lAssignmentRec.assignment_type
3335 -- and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
3336 -- and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)
3337 -- and nvl(ship_to_site_id,-1) = nvl(lAssignmentRec.ship_to_site_id,-1)
3338 -- and sourcing_rule_type = lAssignmentRec.sourcing_rule_type
3339 -- and inventory_item_id = pConfigId
3340 -- and nvl(category_id,-1) = nvl(lAssignmentRec.category_id,-1);
3341 -- ELSE
3342 -- select 1
3343 -- into lAssignmentExists
3344 -- from mrp_sr_assignments
3345 -- where assignment_set_id = lUPGAssignmentSet /* lAssignmentRec.assignment_set_id commented for upgrade logic */
3346 -- and assignment_type = lAssignmentRec.assignment_type
3347 -- and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
3348 -- and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)
3349 -- and nvl(ship_to_site_id,-1) = nvl(lAssignmentRec.ship_to_site_id,-1)
3350 -- and sourcing_rule_type = lAssignmentRec.sourcing_rule_type
3351 -- and nvl(inventory_item_id,-1) IS NULL
3352 -- and nvl(category_id,-1) = nvl(lAssignmentRec.category_id,-1);
3353 -- END IF;
3354 -- -- end: bug 6617686
3355 --
3356 -- IF lAssignmentExists = 1 THEN
3357 -- IF PG_DEBUG <> 0 THEN
3358 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment exists already, do not recreate',2);
3359 -- END IF;
3360 -- return;
3361 -- END IF;
3362 --
3363 -- EXCEPTION
3364 -- when NO_DATA_FOUND then
3365 -- IF PG_DEBUG <> 0 THEN
3366 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment does not exist, create it',2);
3367 -- END IF;
3368 -- when OTHERS then
3369 -- IF PG_DEBUG <> 0 THEN
3370 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'others exception while checking ifassignment exists, not handling, creating assignment:: '||sqlerrm,2);
3371 -- END IF;
3372 -- END;
3373
3374 --
3375 -- get assignment id for config item
3376 --
3377 SELECT mrp_sr_assignments_s.nextval
3378 INTO lConfigAssignmentId
3379 FROM DUAL;
3380
3381 --
3382 -- form lAssignmentTbl from lAssignmentRec
3383 --
3384 lStmtNum := 40;
3385 lAssignmentTbl(1).Assignment_Id := lConfigAssignmentId;
3386 lAssignmentTbl(1).Assignment_Set_Id := lUPGAssignmentSet ; /* commented for upgrade logic lAssignmentRec.Assignment_Set_Id; */
3387 lAssignmentTbl(1).Assignment_Type := lAssignmentRec.Assignment_Type;
3388 lAssignmentTbl(1).Attribute1 := lAssignmentRec.Attribute1;
3389 lAssignmentTbl(1).Attribute10 := lAssignmentRec.Attribute10;
3390 lAssignmentTbl(1).Attribute11 := lAssignmentRec.Attribute11;
3391 lAssignmentTbl(1).Attribute12 := lAssignmentRec.Attribute12;
3392 lAssignmentTbl(1).Attribute13 := lAssignmentRec.Attribute13;
3393 lAssignmentTbl(1).Attribute14 := lAssignmentRec.Attribute14;
3394 lAssignmentTbl(1).Attribute15 := lAssignmentRec.Attribute15;
3395 lAssignmentTbl(1).Attribute2 := lAssignmentRec.Attribute2;
3396 lAssignmentTbl(1).Attribute3 := lAssignmentRec.Attribute3;
3397 lAssignmentTbl(1).Attribute4 := lAssignmentRec.Attribute4;
3398 lAssignmentTbl(1).Attribute5 := lAssignmentRec.Attribute5;
3399 lAssignmentTbl(1).Attribute6 := lAssignmentRec.Attribute6;
3400 lAssignmentTbl(1).Attribute7 := lAssignmentRec.Attribute7;
3401 lAssignmentTbl(1).Attribute8 := lAssignmentRec.Attribute8;
3402 lAssignmentTbl(1).Attribute9 := lAssignmentRec.Attribute9;
3403 lAssignmentTbl(1).Attribute_Category := lAssignmentRec.Attribute_Category;
3404 lAssignmentTbl(1).Category_Id := lAssignmentRec.Category_Id ;
3405 lAssignmentTbl(1).Category_Set_Id := lAssignmentRec.Category_Set_Id;
3406 lAssignmentTbl(1).Created_By := lAssignmentRec.Created_By;
3407 lAssignmentTbl(1).Creation_Date := lAssignmentRec.Creation_Date;
3408 lAssignmentTbl(1).Customer_Id := lAssignmentRec.Customer_Id;
3409 lAssignmentTbl(1).Inventory_Item_Id := pConfigId;
3410 lAssignmentTbl(1).Last_Updated_By := lAssignmentRec.Last_Updated_By;
3411 lAssignmentTbl(1).Last_Update_Date := lAssignmentRec.Last_Update_Date;
3412 lAssignmentTbl(1).Last_Update_Login := lAssignmentRec.Last_Update_Login;
3413 lAssignmentTbl(1).Organization_Id := lAssignmentRec.Organization_Id;
3414 lAssignmentTbl(1).Program_Application_Id:= lAssignmentRec.Program_Application_Id;
3415 lAssignmentTbl(1).Program_Id := lAssignmentRec.Program_Id;
3416 lAssignmentTbl(1).Program_Update_Date := lAssignmentRec.Program_Update_Date;
3417 lAssignmentTbl(1).Request_Id := lAssignmentRec.Request_Id;
3418 lAssignmentTbl(1).Secondary_Inventory := lAssignmentRec.Secondary_Inventory;
3419 lAssignmentTbl(1).Ship_To_Site_Id := lAssignmentRec.Ship_To_Site_Id;
3420 lAssignmentTbl(1).Sourcing_Rule_Id := lAssignmentRec.Sourcing_Rule_Id;
3421 lAssignmentTbl(1).Sourcing_Rule_Type := lAssignmentRec.Sourcing_Rule_Type;
3422 lAssignmentTbl(1).return_status := NULL;
3423 lAssignmentTbl(1).db_flag := NULL;
3424 lAssignmentTbl(1).operation := MRP_Globals.G_OPR_CREATE;
3425
3426 IF PG_DEBUG <> 0 THEN
3427 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'after forming lAssignmentTbl',2);
3428 END IF;
3429
3430 --
3431 -- form lAssignmentSetRec
3432 --
3433 lStmtNum := 50;
3434 lAssignmentSetRec.operation := MRP_Globals.G_OPR_NONE;
3435 IF PG_DEBUG <> 0 THEN
3436 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'after forming lAssignmentSetRec',2);
3437 END IF;
3438
3439 --
3440 -- call mrp API to insert rec into assignment set
3441 --
3442 lStmtNum := 60;
3443 IF PG_DEBUG <> 0 THEN
3444 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'before Process_Assignment',2);
3445 END IF;
3446
3447 -- currently, not passing commented out parameters, need to
3448 -- confirm with raghu, confirmed with stupe
3449
3450 MRP_Src_Assignment_PUB.Process_Assignment
3451 ( p_api_version_number => 1.0
3452 --, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3453 --, p_return_values IN VARCHAR2 := FND_API.G_FALSE
3454 --, p_commit IN VARCHAR2 := FND_API.G_FALSE
3455 , x_return_status => l_return_status
3456 , x_msg_count => l_msg_count
3457 , x_msg_data => l_msg_data
3458 , p_Assignment_Set_rec => lAssignmentSetRec
3459 --, p_Assignment_Set_val_rec IN Assignment_Set_Val_Rec_Type := G_MISS_ASSIGNMENT_SET_VAL_REC
3460 , p_Assignment_tbl => lAssignmentTbl
3461 --, p_Assignment_val_tbl IN Assignment_Val_Tbl_Type := G_MISS_ASSIGNMENT_VAL_TBL
3462 , x_Assignment_Set_rec => xAssignmentSetRec
3463 , x_Assignment_Set_val_rec => xAssignmentSetValRec
3464 , x_Assignment_tbl => xAssignmentTbl
3465 , x_Assignment_val_tbl => xAssignmentValTbl
3466 );
3467
3468 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3469 IF PG_DEBUG <> 0 THEN
3470 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'unexp error in process_assignment::'||sqlerrm,1);
3471 END IF;
3472 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3473
3474 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3475 IF PG_DEBUG <> 0 THEN
3476 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'error in process_assignment::'||sqlerrm,1);
3477 END IF;
3478
3479 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: count:'||l_msg_count , 1 );
3480
3481 IF l_msg_count > 0 THEN
3482 FOR l_index IN 1..l_msg_count LOOP
3483 l_msg_data := fnd_msg_pub.get(
3484 p_msg_index => l_index,
3485 p_encoded => FND_API.G_FALSE);
3486
3487 oe_debug_pub.add( 'CTO_MSUTIL_PUB.create_sourcing_rule: ' || substr(l_msg_data,1,250) , 1 );
3488 END LOOP;
3489
3490 oe_debug_pub.add(' CTO_MSUTIL_PUB.create_sourcing_rules: MSG:'|| xAssignmentSetRec.return_status);
3491 END IF;
3492
3493 oe_debug_pub.add('Failure!' , 1 );
3494
3495
3496 raise FND_API.G_EXC_ERROR;
3497
3498 END IF;
3499 IF PG_DEBUG <> 0 THEN
3500 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'success in process_assignment',2);
3501 END IF;
3502
3503 EXCEPTION
3504 When NO_sourcing_defined THEN
3505 null;
3506
3507 when FND_API.G_EXC_ERROR then
3508 IF PG_DEBUG <> 0 THEN
3509 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Create_Src_Rules::exp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
3510 END IF;
3511 x_return_status := FND_API.G_RET_STS_ERROR;
3512 CTO_MSG_PUB.Count_And_Get
3513 (p_msg_count => x_msg_count
3514 ,p_msg_data => x_msg_data
3515 );
3516
3517 when FND_API.G_EXC_UNEXPECTED_ERROR then
3518 IF PG_DEBUG <> 0 THEN
3519 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Create_Src_Rules::unexp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
3520 END IF;
3521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3522 CTO_MSG_PUB.Count_And_Get
3523 (p_msg_count => x_msg_count
3524 ,p_msg_data => x_msg_data
3525 );
3526
3527 when OTHERS then
3528 IF PG_DEBUG <> 0 THEN
3529 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'Create_Src_Rules::others::'||to_char(lStmtNum)||'::'||sqlerrm,1);
3530 END IF;
3531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3532 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3533 FND_MSG_PUB.Add_Exc_Msg
3534 (G_PKG_NAME
3535 ,'Create_Sourcing_Rules'
3536 );
3537 END IF;
3538 CTO_MSG_PUB.Count_And_Get
3539 (p_msg_count => x_msg_count
3540 ,p_msg_data => x_msg_data
3541 );
3542
3543
3544 END Create_Sourcing_Rules;
3545
3546
3547 PROCEDURE Create_TYPE3_Sourcing_Rules(pModelItemId in number,
3548 pConfigId in number,
3549 pRcvOrgId in number,
3550 x_return_status OUT NOCOPY varchar2,
3551 x_msg_count OUT NOCOPY number,
3552 x_msg_data OUT NOCOPY varchar2,
3553 p_mode in varchar2 default 'AUTOCONFIG' )
3554 IS
3555
3556 lStmtNum number;
3557 lMrpAssignmentSet number;
3558 lAssignmentId number;
3559 lAssignmentType number;
3560 lConfigAssignmentId number;
3561 lAssignmentExists number;
3562 lAssignmentRec MRP_Src_Assignment_PUB.Assignment_Rec_Type;
3563 lAssignmentTbl MRP_Src_Assignment_PUB.Assignment_Tbl_Type;
3564 lAssignmentSetRec MRP_Src_Assignment_PUB.Assignment_Set_Rec_Type;
3565 xAssignmentSetRec MRP_Src_Assignment_PUB.Assignment_Set_Rec_Type;
3566 xAssignmentSetValRec MRP_Src_Assignment_PUB.Assignment_Set_Val_Rec_Type;
3567 xAssignmentTbl MRP_Src_Assignment_PUB.Assignment_Tbl_Type;
3568 xAssignmentValTbl MRP_Src_Assignment_PUB.Assignment_Val_Tbl_Type;
3569 l_return_status varchar2(1);
3570 l_msg_count number;
3571 l_msg_data varchar2(2000);
3572
3573 No_sourcing_defined Exception;
3574
3575
3576 lUPGAssignmentSet number;
3577
3578 cursor c_type3_assignments ( c_def_assg_set number, c_item_id number)
3579 is
3580 select
3581 /*
3582 nvl(rcv.receipt_organization_id,assg.organization_id),
3583 src.source_organization_id,
3584 assg.customer_id,
3585 assg.ship_to_site_id,
3586 src.VENDOR_ID,
3587 vend.VENDOR_SITE_code,
3588 src.RANK,
3589 src.ALLOCATION_PERCENT,
3590 src.SOURCE_TYPE,
3591 assg.sourcing_rule_id,
3592 rcv.sr_receipt_id,
3593 src.sr_source_id,
3594 */
3595 --Bugfix 13029577: Adding a distinct. This sql returns same assignment_id multiple
3596 --times if there is a global transfer from sourcing rule from multiple orgs. Ex.
3597 --Let the rule be:
3598 --Transfer from M1:50%, M2:30%, M3:15%, M4:5%
3599 --For this sourcing rule, there would be 4 records in table mrp_sr_source_org for one
3600 --value of sr_receipt_id.
3601 --The result is that the same assignment is attempted multiple times. MRP API
3602 --process_assignment throws ORA-00001: unique constraint (MRP.MRP_SR_ASSIGNMENTS_U2)
3603 --violated error.
3604 distinct
3605 assg.assignment_id,
3606 assg.assignment_type
3607 from
3608 mrp_sr_receipt_org rcv,
3609 mrp_sr_source_org src,
3610 mrp_sr_assignments assg,
3611 mrp_sourcing_rules rule,
3612 po_vendor_sites_all vend
3613 where
3614 assg.assignment_set_id = c_def_assg_set
3615 and assg.inventory_item_id = c_item_id
3616 and assg.sourcing_rule_id = rcv.sourcing_rule_id
3617 and assg.sourcing_rule_id = rule.sourcing_rule_id
3618 and rule.planning_active = 1
3619 and rcv.effective_date <= sysdate
3620 and nvl(rcv.disable_date,sysdate+1)>sysdate
3621 and rcv.SR_RECEIPT_ID = src.sr_receipt_id
3622 and src.vendor_site_id = vend.vendor_site_id(+) ;
3623
3624 --debugging for bug 13029577
3625 cursor c_config_assignments(c_def_assg_set number, c_item_id number) is
3626 select assignment_set_id,
3627 assignment_type,
3628 organization_id,
3629 customer_id,
3630 ship_to_site_id,
3631 sourcing_rule_type,
3632 category_id
3633 from mrp_sr_assignments
3634 where assignment_set_id = c_def_assg_set
3635 and inventory_item_id = c_item_id;
3636 BEGIN
3637
3638 x_return_status := FND_API.G_RET_STS_SUCCESS;
3639 lAssignmentExists := 0;
3640
3641 lStmtNum := 10;
3642 /* get MRP's default assignment set */
3643 lMrpAssignmentSet := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
3644
3645 if( p_mode = 'AUTOCONFIG' ) then
3646
3647 lUPGAssignmentSet := lMrpAssignmentSet ;
3648
3649 else
3650
3651 select assignment_set_id into lUPGAssignmentSet
3652 from mrp_assignment_sets
3653 where assignment_set_name = 'CTO Configuration Updates' ;
3654
3655 end if;
3656
3657
3658
3659
3660
3661
3662
3663 IF lMrpAssignmentSet is null THEN
3664 IF PG_DEBUG <> 0 THEN
3665 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Default assignment set is null, returning from create_sourcing_rules procedure',1);
3666 END IF;
3667 return;
3668 ELSE
3669 IF PG_DEBUG <> 0 THEN
3670 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
3671 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'UPG assignment set is '||to_char(lUPGAssignmentSet),2);
3672 END IF;
3673 END IF;
3674
3675 --
3676 -- from mrp view, get Assignment_id of assignment to be copied
3677 --
3678
3679 -- The buy sourcing rules are also need to be selected.
3680 -- The where condition source_type <> 3 needs to be removed
3681 -- Changes are made by Renga Kannan on 08/22/01 for procuring config Change
3682
3683
3684
3685 -- Modified by Renga Kannan on 13-NOV-2001
3686 -- Added condition for assignment type = 3,6
3687 -- Previously it was erroring out if the assignment type is not of 3, 6
3688 -- It should not error out, rather it should igonre this
3689 -- this is required becasue of multiple buy support
3690
3691
3692 lStmtNum := 20;
3693
3694
3695 /*
3696
3697
3698 -- When no data found it not an exception in this case
3699 -- It may not have any sourcing for assignment_type 3,6 so we need not error out for this
3700
3701 BEGIN
3702
3703 select distinct assignment_id, assignment_type
3704 into lAssignmentId, lAssignmentType
3705 from mrp_sources_v msv
3706 where msv.assignment_set_id = lMrpAssignmentSet
3707 and msv.inventory_item_id = pModelItemId
3708 and msv.organization_id = pRcvOrgId
3709 and effective_date <= nvl(disable_date, sysdate)
3710 and nvl(disable_date, sysdate+1) > sysdate
3711 and assignment_type in (3,6);
3712
3713 EXCEPTION
3714 WHEN NO_DATA_FOUND THEN
3715
3716 IF PG_DEBUG <> 0 THEN
3717 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'There is no sourcing rule defined ',1);
3718 END IF;
3719 raise no_sourcing_defined;
3720
3721 END;
3722
3723
3724 */
3725
3726
3727
3728
3729
3730
3731 open c_type3_assignments ( lMrpAssignmentSet , pModelItemId ) ;
3732
3733
3734 LOOP
3735
3736
3737 fetch c_type3_assignments into lAssignmentId, lAssignmentType ;
3738
3739
3740
3741 exit when c_type3_assignments%notfound ;
3742
3743
3744 IF PG_DEBUG <> 0 THEN
3745 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'lAssnType::'||to_char(lAssignmentType)||'::lAssnId::'||to_char(lAssignmentId),2);
3746 END IF;
3747
3748 --
3749 -- copy assignment into lAssignmentRec
3750 --
3751
3752 lStmtNum := 30;
3753
3754 --
3755 -- bug 6617686
3756 -- The MRP API uses a ASSIGNMENT_ID = p_Assignment_Id OR
3757 -- ASSIGNMENT_SET_ID = p_Assignment_Set_Id that leads to
3758 -- a full table scan on MRP_SR_ASSIGNMENTS and consequent
3759 -- performance issues. Since CTO does not pass ASSIGNMENT_SET_ID
3760 -- into the procedure, it is performance effective to directly
3761 -- query the MRP table
3762 -- ntungare
3763 --
3764 -- lAssignmentRec := MRP_Assignment_Handlers.Query_Row(lAssignmentId);
3765
3766 -- bug 8789722
3767 --
3768 /*
3769 SELECT ASSIGNMENT_ID
3770 , ASSIGNMENT_SET_ID
3771 , ASSIGNMENT_TYPE
3772 , ATTRIBUTE1
3773 , ATTRIBUTE10
3774 , ATTRIBUTE11
3775 , ATTRIBUTE12
3776 , ATTRIBUTE13
3777 , ATTRIBUTE14
3778 , ATTRIBUTE15
3779 , ATTRIBUTE2
3780 , ATTRIBUTE3
3781 , ATTRIBUTE4
3782 , ATTRIBUTE5
3783 , ATTRIBUTE6
3784 , ATTRIBUTE7
3785 , ATTRIBUTE8
3786 , ATTRIBUTE9
3787 , ATTRIBUTE_CATEGORY
3788 , CATEGORY_ID
3789 , CATEGORY_SET_ID
3790 , CREATED_BY
3791 , CREATION_DATE
3792 , CUSTOMER_ID
3793 , INVENTORY_ITEM_ID
3794 , LAST_UPDATED_BY
3795 , LAST_UPDATE_DATE
3796 , LAST_UPDATE_LOGIN
3797 , ORGANIZATION_ID
3798 , PROGRAM_APPLICATION_ID
3799 , PROGRAM_ID
3800 , PROGRAM_UPDATE_DATE
3801 , REQUEST_ID
3802 , SECONDARY_INVENTORY
3803 , SHIP_TO_SITE_ID
3804 , SOURCING_RULE_ID
3805 , SOURCING_RULE_TYPE
3806 into lAssignmentRec.ASSIGNMENT_ID
3807 , lAssignmentRec.ASSIGNMENT_SET_ID
3808 , lAssignmentRec.ASSIGNMENT_TYPE
3809 , lAssignmentRec.ATTRIBUTE1
3810 , lAssignmentRec.ATTRIBUTE10
3811 , lAssignmentRec.ATTRIBUTE11
3812 , lAssignmentRec.ATTRIBUTE12
3813 , lAssignmentRec.ATTRIBUTE13
3814 , lAssignmentRec.ATTRIBUTE14
3815 , lAssignmentRec.ATTRIBUTE15
3816 , lAssignmentRec.ATTRIBUTE2
3817 , lAssignmentRec.ATTRIBUTE3
3818 , lAssignmentRec.ATTRIBUTE4
3819 , lAssignmentRec.ATTRIBUTE5
3820 , lAssignmentRec.ATTRIBUTE6
3821 , lAssignmentRec.ATTRIBUTE7
3822 , lAssignmentRec.ATTRIBUTE8
3823 , lAssignmentRec.ATTRIBUTE9
3824 , lAssignmentRec.ATTRIBUTE_CATEGORY
3825 , lAssignmentRec.CATEGORY_ID
3826 , lAssignmentRec.CATEGORY_SET_ID
3827 , lAssignmentRec.CREATED_BY
3828 , lAssignmentRec.CREATION_DATE
3829 , lAssignmentRec.CUSTOMER_ID
3830 , lAssignmentRec.INVENTORY_ITEM_ID
3831 , lAssignmentRec.LAST_UPDATED_BY
3832 , lAssignmentRec.LAST_UPDATE_DATE
3833 , lAssignmentRec.LAST_UPDATE_LOGIN
3834 , lAssignmentRec.ORGANIZATION_ID
3835 , lAssignmentRec.PROGRAM_APPLICATION_ID
3836 , lAssignmentRec.PROGRAM_ID
3837 , lAssignmentRec.PROGRAM_UPDATE_DATE
3838 , lAssignmentRec.REQUEST_ID
3839 , lAssignmentRec.SECONDARY_INVENTORY
3840 , lAssignmentRec.SHIP_TO_SITE_ID
3841 , lAssignmentRec.SOURCING_RULE_ID
3842 , lAssignmentRec.SOURCING_RULE_TYPE
3843 FROM MRP_SR_ASSIGNMENTS
3844 WHERE ASSIGNMENT_ID = lAssignmentId;*/
3845
3846 /*-------------------
3847 bug 8789722
3848 Start
3849 --------------------*/
3850 BEGIN
3851 IF pConfigId IS NOT NULL THEN
3852 SELECT ASSIGNMENT_ID
3853 , ASSIGNMENT_SET_ID
3854 , ASSIGNMENT_TYPE
3855 , ATTRIBUTE1
3856 , ATTRIBUTE10
3857 , ATTRIBUTE11
3858 , ATTRIBUTE12
3859 , ATTRIBUTE13
3860 , ATTRIBUTE14
3861 , ATTRIBUTE15
3862 , ATTRIBUTE2
3863 , ATTRIBUTE3
3864 , ATTRIBUTE4
3865 , ATTRIBUTE5
3866 , ATTRIBUTE6
3867 , ATTRIBUTE7
3868 , ATTRIBUTE8
3869 , ATTRIBUTE9
3870 , ATTRIBUTE_CATEGORY
3871 , CATEGORY_ID
3872 , CATEGORY_SET_ID
3873 , CREATED_BY
3874 , CREATION_DATE
3875 , CUSTOMER_ID
3876 , INVENTORY_ITEM_ID
3877 , LAST_UPDATED_BY
3878 , LAST_UPDATE_DATE
3879 , LAST_UPDATE_LOGIN
3880 , ORGANIZATION_ID
3881 , PROGRAM_APPLICATION_ID
3882 , PROGRAM_ID
3883 , PROGRAM_UPDATE_DATE
3884 , REQUEST_ID
3885 , SECONDARY_INVENTORY
3886 , SHIP_TO_SITE_ID
3887 , SOURCING_RULE_ID
3888 , SOURCING_RULE_TYPE
3889 into lAssignmentRec.ASSIGNMENT_ID
3890 , lAssignmentRec.ASSIGNMENT_SET_ID
3891 , lAssignmentRec.ASSIGNMENT_TYPE
3892 , lAssignmentRec.ATTRIBUTE1
3893 , lAssignmentRec.ATTRIBUTE10
3894 , lAssignmentRec.ATTRIBUTE11
3895 , lAssignmentRec.ATTRIBUTE12
3896 , lAssignmentRec.ATTRIBUTE13
3897 , lAssignmentRec.ATTRIBUTE14
3898 , lAssignmentRec.ATTRIBUTE15
3899 , lAssignmentRec.ATTRIBUTE2
3900 , lAssignmentRec.ATTRIBUTE3
3901 , lAssignmentRec.ATTRIBUTE4
3902 , lAssignmentRec.ATTRIBUTE5
3903 , lAssignmentRec.ATTRIBUTE6
3904 , lAssignmentRec.ATTRIBUTE7
3905 , lAssignmentRec.ATTRIBUTE8
3906 , lAssignmentRec.ATTRIBUTE9
3907 , lAssignmentRec.ATTRIBUTE_CATEGORY
3908 , lAssignmentRec.CATEGORY_ID
3909 , lAssignmentRec.CATEGORY_SET_ID
3910 , lAssignmentRec.CREATED_BY
3911 , lAssignmentRec.CREATION_DATE
3912 , lAssignmentRec.CUSTOMER_ID
3913 , lAssignmentRec.INVENTORY_ITEM_ID
3914 , lAssignmentRec.LAST_UPDATED_BY
3915 , lAssignmentRec.LAST_UPDATE_DATE
3916 , lAssignmentRec.LAST_UPDATE_LOGIN
3917 , lAssignmentRec.ORGANIZATION_ID
3918 , lAssignmentRec.PROGRAM_APPLICATION_ID
3919 , lAssignmentRec.PROGRAM_ID
3920 , lAssignmentRec.PROGRAM_UPDATE_DATE
3921 , lAssignmentRec.REQUEST_ID
3922 , lAssignmentRec.SECONDARY_INVENTORY
3923 , lAssignmentRec.SHIP_TO_SITE_ID
3924 , lAssignmentRec.SOURCING_RULE_ID
3925 , lAssignmentRec.SOURCING_RULE_TYPE
3926 FROM MRP_SR_ASSIGNMENTS A
3927 WHERE ASSIGNMENT_ID = lAssignmentId
3928 AND NOT EXISTS(SELECT /*+ INDEX(B MRP_SR_ASSIGNMENTS_U2)*/ 1
3929 FROM MRP_SR_ASSIGNMENTS B
3930 --Bugfix 14270815: Checking for the config assignment in lUPGAssignmentSet also.
3931 --WHERE a.assignment_set_id = b.assignment_set_id and
3932 WHERE (a.assignment_set_id = b.assignment_set_id or
3933 b.assignment_set_id = lUPGAssignmentSet) and
3934 a.assignment_type = b.assignment_type and
3935 nvl(b.organization_id,-1) = nvl(a.organization_id,-1) and
3936 nvl(b.customer_id,-1) = nvl(a.customer_id,-1) and
3937 nvl(b.ship_to_site_id,-1) = nvl(a.ship_to_site_id,-1) and
3938 b.sourcing_rule_type = a.sourcing_rule_type and
3939 b.inventory_item_id = pConfigId and
3940 nvl(b.category_id,-1) = nvl(a.category_id,-1) and
3941 rownum = 1);
3942 ELSE
3943 SELECT ASSIGNMENT_ID
3944 , ASSIGNMENT_SET_ID
3945 , ASSIGNMENT_TYPE
3946 , ATTRIBUTE1
3947 , ATTRIBUTE10
3948 , ATTRIBUTE11
3949 , ATTRIBUTE12
3950 , ATTRIBUTE13
3951 , ATTRIBUTE14
3952 , ATTRIBUTE15
3953 , ATTRIBUTE2
3954 , ATTRIBUTE3
3955 , ATTRIBUTE4
3956 , ATTRIBUTE5
3957 , ATTRIBUTE6
3958 , ATTRIBUTE7
3959 , ATTRIBUTE8
3960 , ATTRIBUTE9
3961 , ATTRIBUTE_CATEGORY
3962 , CATEGORY_ID
3963 , CATEGORY_SET_ID
3964 , CREATED_BY
3965 , CREATION_DATE
3966 , CUSTOMER_ID
3967 , INVENTORY_ITEM_ID
3968 , LAST_UPDATED_BY
3969 , LAST_UPDATE_DATE
3970 , LAST_UPDATE_LOGIN
3971 , ORGANIZATION_ID
3972 , PROGRAM_APPLICATION_ID
3973 , PROGRAM_ID
3974 , PROGRAM_UPDATE_DATE
3975 , REQUEST_ID
3976 , SECONDARY_INVENTORY
3977 , SHIP_TO_SITE_ID
3978 , SOURCING_RULE_ID
3979 , SOURCING_RULE_TYPE
3980 into lAssignmentRec.ASSIGNMENT_ID
3981 , lAssignmentRec.ASSIGNMENT_SET_ID
3982 , lAssignmentRec.ASSIGNMENT_TYPE
3983 , lAssignmentRec.ATTRIBUTE1
3984 , lAssignmentRec.ATTRIBUTE10
3985 , lAssignmentRec.ATTRIBUTE11
3986 , lAssignmentRec.ATTRIBUTE12
3987 , lAssignmentRec.ATTRIBUTE13
3988 , lAssignmentRec.ATTRIBUTE14
3989 , lAssignmentRec.ATTRIBUTE15
3990 , lAssignmentRec.ATTRIBUTE2
3991 , lAssignmentRec.ATTRIBUTE3
3992 , lAssignmentRec.ATTRIBUTE4
3993 , lAssignmentRec.ATTRIBUTE5
3994 , lAssignmentRec.ATTRIBUTE6
3995 , lAssignmentRec.ATTRIBUTE7
3996 , lAssignmentRec.ATTRIBUTE8
3997 , lAssignmentRec.ATTRIBUTE9
3998 , lAssignmentRec.ATTRIBUTE_CATEGORY
3999 , lAssignmentRec.CATEGORY_ID
4000 , lAssignmentRec.CATEGORY_SET_ID
4001 , lAssignmentRec.CREATED_BY
4002 , lAssignmentRec.CREATION_DATE
4003 , lAssignmentRec.CUSTOMER_ID
4004 , lAssignmentRec.INVENTORY_ITEM_ID
4005 , lAssignmentRec.LAST_UPDATED_BY
4006 , lAssignmentRec.LAST_UPDATE_DATE
4007 , lAssignmentRec.LAST_UPDATE_LOGIN
4008 , lAssignmentRec.ORGANIZATION_ID
4009 , lAssignmentRec.PROGRAM_APPLICATION_ID
4010 , lAssignmentRec.PROGRAM_ID
4011 , lAssignmentRec.PROGRAM_UPDATE_DATE
4012 , lAssignmentRec.REQUEST_ID
4013 , lAssignmentRec.SECONDARY_INVENTORY
4014 , lAssignmentRec.SHIP_TO_SITE_ID
4015 , lAssignmentRec.SOURCING_RULE_ID
4016 , lAssignmentRec.SOURCING_RULE_TYPE
4017 FROM MRP_SR_ASSIGNMENTS A
4018 WHERE ASSIGNMENT_ID = lAssignmentId
4019 AND NOT EXISTS(SELECT /*+ INDEX(B MRP_SR_ASSIGNMENTS_U2)*/ 1
4020 FROM MRP_SR_ASSIGNMENTS B
4021 --Bugfix 14270815: Checking for the config assignment in lUPGAssignmentSet also.
4022 --WHERE a.assignment_set_id = b.assignment_set_id and
4023 WHERE (a.assignment_set_id = b.assignment_set_id or
4024 b.assignment_set_id = lUPGAssignmentSet) and
4025 a.assignment_type = b.assignment_type and
4026 nvl(b.organization_id,-1) = nvl(a.organization_id,-1) and
4027 nvl(b.customer_id,-1) = nvl(a.customer_id,-1) and
4028 nvl(b.ship_to_site_id,-1) = nvl(a.ship_to_site_id,-1) and
4029 b.sourcing_rule_type = a.sourcing_rule_type and
4030 b.inventory_item_id IS NULL and
4031 nvl(b.category_id,-1) = nvl(a.category_id,-1) and
4032 rownum = 1);
4033 END IF;
4034
4035 EXCEPTION
4036 WHEN NO_DATA_FOUND THEN
4037 IF PG_DEBUG <> 0 THEN
4038 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'assignment exists already, do not recreate',2);
4039 END IF;
4040 goto END_OF_LOOP;
4041
4042 WHEN OTHERS THEN
4043 IF PG_DEBUG <> 0 THEN
4044 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: ' || 'others exception while checking ifassignment exists, not handling, creating assignment:: '||sqlerrm,2);
4045 END IF;
4046 END;
4047
4048 IF PG_DEBUG <> 0 THEN
4049 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after query row',2);
4050 END IF;
4051
4052 --
4053 -- check if this assignment already exists for config item
4054 --
4055 lStmtNum := 35;
4056 -- BEGIN
4057 --
4058 -- IF PG_DEBUG <> 0 THEN
4059 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
4060 --
4061 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Source assignment_set_id::'||to_char(lMrpAssignmentSet),2);
4062 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Destination assignment_set_id::'||to_char(lUPGAssignmentSet),2);
4063 --
4064 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
4065 --
4066 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
4067 --
4068 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
4069 --
4070 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
4071 --
4072 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
4073 --
4074 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
4075 --
4076 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
4077 -- END IF;
4078 --
4079 -- -- bug 6617686
4080 -- IF pConfigId IS NOT NULL THEN
4081 -- select 1
4082 -- into lAssignmentExists
4083 -- from mrp_sr_assignments
4084 -- where assignment_set_id = lUPGAssignmentSet /* commented for upgrade issues lAssignmentRec.assignment_set_id */
4085 -- and assignment_type = lAssignmentRec.assignment_type
4086 -- and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
4087 -- and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)
4088 -- and nvl(ship_to_site_id,-1) = nvl(lAssignmentRec.ship_to_site_id,-1)
4089 -- and sourcing_rule_type = lAssignmentRec.sourcing_rule_type
4090 -- and nvl(inventory_item_id,-1) = pConfigId
4091 -- and nvl(category_id,-1) = nvl(lAssignmentRec.category_id,-1);
4092 -- ELSE
4093 -- select 1
4094 -- into lAssignmentExists
4095 -- from mrp_sr_assignments
4096 -- where assignment_set_id = lUPGAssignmentSet /* commented for upgrade issues lAssignmentRec.assignment_set_id */
4097 -- and assignment_type = lAssignmentRec.assignment_type
4098 -- and nvl(organization_id,-1) = nvl(lAssignmentRec.organization_id,-1)
4099 -- and nvl(customer_id,-1) = nvl(lAssignmentRec.customer_id,-1)
4100 -- and nvl(ship_to_site_id,-1) = nvl(lAssignmentRec.ship_to_site_id,-1)
4101 -- and sourcing_rule_type = lAssignmentRec.sourcing_rule_type
4102 -- and inventory_item_id is null
4103 -- and nvl(category_id,-1) = nvl(lAssignmentRec.category_id,-1);
4104 -- END IF;
4105 -- -- end : bug 6617686
4106 --
4107 -- IF lAssignmentExists = 1 THEN
4108 -- IF PG_DEBUG <> 0 THEN
4109 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment exists already, do not recreate',2);
4110 -- END IF;
4111 --
4112 --
4113 --
4114 -- goto END_OF_LOOP ; /* continue with next record */
4115 --
4116 --
4117 -- END IF;
4118 --
4119 -- EXCEPTION
4120 -- when NO_DATA_FOUND then
4121 -- IF PG_DEBUG <> 0 THEN
4122 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'assignment does not exist, create it',2);
4123 -- END IF;
4124 -- when OTHERS then
4125 -- IF PG_DEBUG <> 0 THEN
4126 -- oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' ||
4127 -- 'others exception while checking ifassignment exists, not handling, creating assignment:: '||sqlerrm,2);
4128 -- END IF;
4129 -- END;
4130
4131 --
4132 -- get assignment id for config item
4133 --
4134
4135
4136
4137 SELECT mrp_sr_assignments_s.nextval
4138 INTO lConfigAssignmentId
4139 FROM DUAL;
4140
4141 --
4142 -- form lAssignmentTbl from lAssignmentRec
4143 --
4144 lStmtNum := 40;
4145 lAssignmentTbl(1).Assignment_Id := lConfigAssignmentId;
4146 lAssignmentTbl(1).Assignment_Set_Id := lUPGAssignmentSet ; /* commented for upgrade logic lAssignmentRec.Assignment_Set_Id; */
4147 lAssignmentTbl(1).Assignment_Type := lAssignmentRec.Assignment_Type;
4148 lAssignmentTbl(1).Attribute1 := lAssignmentRec.Attribute1;
4149 lAssignmentTbl(1).Attribute10 := lAssignmentRec.Attribute10;
4150 lAssignmentTbl(1).Attribute11 := lAssignmentRec.Attribute11;
4151 lAssignmentTbl(1).Attribute12 := lAssignmentRec.Attribute12;
4152 lAssignmentTbl(1).Attribute13 := lAssignmentRec.Attribute13;
4153 lAssignmentTbl(1).Attribute14 := lAssignmentRec.Attribute14;
4154 lAssignmentTbl(1).Attribute15 := lAssignmentRec.Attribute15;
4155 lAssignmentTbl(1).Attribute2 := lAssignmentRec.Attribute2;
4156 lAssignmentTbl(1).Attribute3 := lAssignmentRec.Attribute3;
4157 lAssignmentTbl(1).Attribute4 := lAssignmentRec.Attribute4;
4158 lAssignmentTbl(1).Attribute5 := lAssignmentRec.Attribute5;
4159 lAssignmentTbl(1).Attribute6 := lAssignmentRec.Attribute6;
4160 lAssignmentTbl(1).Attribute7 := lAssignmentRec.Attribute7;
4161 lAssignmentTbl(1).Attribute8 := lAssignmentRec.Attribute8;
4162 lAssignmentTbl(1).Attribute9 := lAssignmentRec.Attribute9;
4163 lAssignmentTbl(1).Attribute_Category := lAssignmentRec.Attribute_Category;
4164 lAssignmentTbl(1).Category_Id := lAssignmentRec.Category_Id ;
4165 lAssignmentTbl(1).Category_Set_Id := lAssignmentRec.Category_Set_Id;
4166 lAssignmentTbl(1).Created_By := lAssignmentRec.Created_By;
4167 lAssignmentTbl(1).Creation_Date := lAssignmentRec.Creation_Date;
4168 lAssignmentTbl(1).Customer_Id := lAssignmentRec.Customer_Id;
4169 lAssignmentTbl(1).Inventory_Item_Id := pConfigId;
4170 lAssignmentTbl(1).Last_Updated_By := lAssignmentRec.Last_Updated_By;
4171 lAssignmentTbl(1).Last_Update_Date := lAssignmentRec.Last_Update_Date;
4172 lAssignmentTbl(1).Last_Update_Login := lAssignmentRec.Last_Update_Login;
4173 lAssignmentTbl(1).Organization_Id := lAssignmentRec.Organization_Id;
4174 lAssignmentTbl(1).Program_Application_Id := lAssignmentRec.Program_Application_Id;
4175 lAssignmentTbl(1).Program_Id := lAssignmentRec.Program_Id;
4176 lAssignmentTbl(1).Program_Update_Date := lAssignmentRec.Program_Update_Date;
4177 lAssignmentTbl(1).Request_Id := lAssignmentRec.Request_Id;
4178 lAssignmentTbl(1).Secondary_Inventory := lAssignmentRec.Secondary_Inventory;
4179 lAssignmentTbl(1).Ship_To_Site_Id := lAssignmentRec.Ship_To_Site_Id;
4180 lAssignmentTbl(1).Sourcing_Rule_Id := lAssignmentRec.Sourcing_Rule_Id;
4181 lAssignmentTbl(1).Sourcing_Rule_Type := lAssignmentRec.Sourcing_Rule_Type;
4182 lAssignmentTbl(1).return_status := NULL;
4183 lAssignmentTbl(1).db_flag := NULL;
4184 lAssignmentTbl(1).operation := MRP_Globals.G_OPR_CREATE;
4185
4186 IF PG_DEBUG <> 0 THEN
4187 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after forming lAssignmentTbl',2);
4188 END IF;
4189
4190 --
4191 -- form lAssignmentSetRec
4192 --
4193 lStmtNum := 50;
4194 lAssignmentSetRec.operation := MRP_Globals.G_OPR_NONE;
4195 IF PG_DEBUG <> 0 THEN
4196 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after forming lAssignmentSetRec',2);
4197 END IF;
4198
4199 --
4200 -- call mrp API to insert rec into assignment set
4201 --
4202 lStmtNum := 60;
4203 IF PG_DEBUG <> 0 THEN
4204 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'before Process_Assignment',2);
4205 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'printing lAssignmentRec');
4206 oe_debug_pub.add('--------------------------------------------------------------------------');
4207 oe_debug_pub.add('lAssignmentRec.ASSIGNMENT_ID:' || lAssignmentRec.ASSIGNMENT_ID );
4208 oe_debug_pub.add('lAssignmentRec.ASSIGNMENT_SET_ID:' || lAssignmentRec.ASSIGNMENT_SET_ID );
4209 oe_debug_pub.add('lAssignmentRec.ASSIGNMENT_TYPE:' || lAssignmentRec.ASSIGNMENT_TYPE );
4210 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE1:' || lAssignmentRec.ATTRIBUTE1 );
4211 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE2:' || lAssignmentRec.ATTRIBUTE2 );
4212 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE3:' || lAssignmentRec.ATTRIBUTE3 );
4213 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE4:' || lAssignmentRec.ATTRIBUTE4 );
4214 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE5:' || lAssignmentRec.ATTRIBUTE5 );
4215 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE6:' || lAssignmentRec.ATTRIBUTE6 );
4216 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE7:' || lAssignmentRec.ATTRIBUTE7 );
4217 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE8:' || lAssignmentRec.ATTRIBUTE8 );
4218 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE9:' || lAssignmentRec.ATTRIBUTE9 );
4219 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE10:' || lAssignmentRec.ATTRIBUTE10 );
4220 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE11:' || lAssignmentRec.ATTRIBUTE11 );
4221 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE12:' || lAssignmentRec.ATTRIBUTE12 );
4222 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE13:' || lAssignmentRec.ATTRIBUTE13 );
4223 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE14:' || lAssignmentRec.ATTRIBUTE14 );
4224 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE15:' || lAssignmentRec.ATTRIBUTE15 );
4225 oe_debug_pub.add('lAssignmentRec.ATTRIBUTE_CATEGORY:' || lAssignmentRec.ATTRIBUTE_CATEGORY );
4226 oe_debug_pub.add('lAssignmentRec.CATEGORY_ID:' || lAssignmentRec.CATEGORY_ID );
4227 oe_debug_pub.add('lAssignmentRec.CATEGORY_SET_ID:' || lAssignmentRec.CATEGORY_SET_ID );
4228 oe_debug_pub.add('lAssignmentRec.CREATED_BY:' || lAssignmentRec.CREATED_BY );
4229 oe_debug_pub.add('lAssignmentRec.CREATION_DATE:' || lAssignmentRec.CREATION_DATE );
4230 oe_debug_pub.add('lAssignmentRec.CUSTOMER_ID:' || lAssignmentRec.CUSTOMER_ID );
4231 oe_debug_pub.add('lAssignmentRec.INVENTORY_ITEM_ID:' || lAssignmentRec.INVENTORY_ITEM_ID );
4232 oe_debug_pub.add('lAssignmentRec.LAST_UPDATED_BY:' || lAssignmentRec.LAST_UPDATED_BY );
4233 oe_debug_pub.add('lAssignmentRec.LAST_UPDATE_DATE:' || lAssignmentRec.LAST_UPDATE_DATE );
4234 oe_debug_pub.add('lAssignmentRec.LAST_UPDATE_LOGIN:' || lAssignmentRec.LAST_UPDATE_LOGIN );
4235 oe_debug_pub.add('lAssignmentRec.ORGANIZATION_ID:' || lAssignmentRec.ORGANIZATION_ID );
4236 oe_debug_pub.add('lAssignmentRec.PROGRAM_APPLICATION_ID:' || lAssignmentRec.PROGRAM_APPLICATION_ID );
4237 oe_debug_pub.add('lAssignmentRec.PROGRAM_ID:' || lAssignmentRec.PROGRAM_ID );
4238 oe_debug_pub.add('lAssignmentRec.PROGRAM_UPDATE_DATE:' || lAssignmentRec.PROGRAM_UPDATE_DATE );
4239 oe_debug_pub.add('lAssignmentRec.REQUEST_ID:' || lAssignmentRec.REQUEST_ID );
4240 oe_debug_pub.add('lAssignmentRec.SECONDARY_INVENTORY:' || lAssignmentRec.SECONDARY_INVENTORY );
4241 oe_debug_pub.add('lAssignmentRec.SHIP_TO_SITE_ID:' || lAssignmentRec.SHIP_TO_SITE_ID );
4242 oe_debug_pub.add('lAssignmentRec.SOURCING_RULE_ID:' || lAssignmentRec.SOURCING_RULE_ID );
4243 oe_debug_pub.add('lAssignmentRec.SOURCING_RULE_TYPE:' || lAssignmentRec.SOURCING_RULE_TYPE );
4244 oe_debug_pub.add('lAssignmentRec.OPERATION:' || lAssignmentRec.OPERATION );
4245 oe_debug_pub.add('--------------------------------------------------------------------------');
4246 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'printing lAssignmentRec');
4247 oe_debug_pub.add('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
4248 oe_debug_pub.add('lAssignmentTbl(1).Assignment_Id:' || lAssignmentTbl(1).Assignment_Id);
4249 oe_debug_pub.add('lAssignmentTbl(1).Assignment_Set_Id:' || lAssignmentTbl(1).Assignment_Set_Id);
4250 oe_debug_pub.add('lAssignmentTbl(1).Assignment_Type:' || lAssignmentTbl(1).Assignment_Type);
4251 oe_debug_pub.add('lAssignmentTbl(1).Attribute1:' || lAssignmentTbl(1).Attribute1);
4252 oe_debug_pub.add('lAssignmentTbl(1).Attribute10:' || lAssignmentTbl(1).Attribute10);
4253 oe_debug_pub.add('lAssignmentTbl(1).Attribute11:' || lAssignmentTbl(1).Attribute11);
4254 oe_debug_pub.add('lAssignmentTbl(1).Attribute12:' || lAssignmentTbl(1).Attribute12);
4255 oe_debug_pub.add('lAssignmentTbl(1).Attribute13:' || lAssignmentTbl(1).Attribute13);
4256 oe_debug_pub.add('lAssignmentTbl(1).Attribute14:' || lAssignmentTbl(1).Attribute14);
4257 oe_debug_pub.add('lAssignmentTbl(1).Attribute15:' || lAssignmentTbl(1).Attribute15);
4258 oe_debug_pub.add('lAssignmentTbl(1).Attribute2:' || lAssignmentTbl(1).Attribute2);
4259 oe_debug_pub.add('lAssignmentTbl(1).Attribute3:' || lAssignmentTbl(1).Attribute3);
4260 oe_debug_pub.add('lAssignmentTbl(1).Attribute4:' || lAssignmentTbl(1).Attribute4);
4261 oe_debug_pub.add('lAssignmentTbl(1).Attribute5:' || lAssignmentTbl(1).Attribute5);
4262 oe_debug_pub.add('lAssignmentTbl(1).Attribute6:' || lAssignmentTbl(1).Attribute6);
4263 oe_debug_pub.add('lAssignmentTbl(1).Attribute7:' || lAssignmentTbl(1).Attribute7);
4264 oe_debug_pub.add('lAssignmentTbl(1).Attribute8:' || lAssignmentTbl(1).Attribute8);
4265 oe_debug_pub.add('lAssignmentTbl(1).Attribute9:' || lAssignmentTbl(1).Attribute9);
4266 oe_debug_pub.add('lAssignmentTbl(1).Attribute_Category:' || lAssignmentTbl(1).Attribute_Category);
4267 oe_debug_pub.add('lAssignmentTbl(1).Category_Id:' || lAssignmentTbl(1).Category_Id);
4268 oe_debug_pub.add('lAssignmentTbl(1).Category_Set_Id:' || lAssignmentTbl(1).Category_Set_Id);
4269 oe_debug_pub.add('lAssignmentTbl(1).Created_By:' || lAssignmentTbl(1).Created_By);
4270 oe_debug_pub.add('lAssignmentTbl(1).Creation_Date:' || lAssignmentTbl(1).Creation_Date);
4271 oe_debug_pub.add('lAssignmentTbl(1).Customer_Id:' || lAssignmentTbl(1).Customer_Id);
4272 oe_debug_pub.add('lAssignmentTbl(1).Inventory_Item_Id:' || lAssignmentTbl(1).Inventory_Item_Id);
4273 oe_debug_pub.add('lAssignmentTbl(1).Last_Updated_By:' || lAssignmentTbl(1).Last_Updated_By);
4274 oe_debug_pub.add('lAssignmentTbl(1).Last_Update_Date:' || lAssignmentTbl(1).Last_Update_Date);
4275 oe_debug_pub.add('lAssignmentTbl(1).Last_Update_Login:' || lAssignmentTbl(1).Last_Update_Login);
4276 oe_debug_pub.add('lAssignmentTbl(1).Organization_Id:' || lAssignmentTbl(1).Organization_Id);
4277 oe_debug_pub.add('lAssignmentTbl(1).Program_Application_Id:' || lAssignmentTbl(1).Program_Application_Id);
4278 oe_debug_pub.add('lAssignmentTbl(1).Program_Id:' || lAssignmentTbl(1).Program_Id);
4279 oe_debug_pub.add('lAssignmentTbl(1).Program_Update_Date:' || lAssignmentTbl(1).Program_Update_Date);
4280 oe_debug_pub.add('lAssignmentTbl(1).Request_Id:' || lAssignmentTbl(1).Request_Id);
4281 oe_debug_pub.add('lAssignmentTbl(1).Secondary_Inventory:' || lAssignmentTbl(1).Secondary_Inventory);
4282 oe_debug_pub.add('lAssignmentTbl(1).Ship_To_Site_Id:' || lAssignmentTbl(1).Ship_To_Site_Id);
4283 oe_debug_pub.add('lAssignmentTbl(1).Sourcing_Rule_Id:' || lAssignmentTbl(1).Sourcing_Rule_Id);
4284 oe_debug_pub.add('lAssignmentTbl(1).Sourcing_Rule_Type:' || lAssignmentTbl(1).Sourcing_Rule_Type);
4285 oe_debug_pub.add('lAssignmentTbl(1).return_status:' || lAssignmentTbl(1).return_status);
4286 oe_debug_pub.add('lAssignmentTbl(1).db_flag:' || lAssignmentTbl(1).db_flag);
4287 oe_debug_pub.add('lAssignmentTbl(1).operation:' || lAssignmentTbl(1).operation);
4288 oe_debug_pub.add('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
4289 END IF;
4290
4291 -- currently, not passing commented out parameters, need to
4292 -- confirm with raghu, confirmed with stupe
4293
4294 MRP_Src_Assignment_PUB.Process_Assignment
4295 ( p_api_version_number => 1.0
4296 --, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4297 --, p_return_values IN VARCHAR2 := FND_API.G_FALSE
4298 --, p_commit IN VARCHAR2 := FND_API.G_FALSE
4299 , x_return_status => l_return_status
4300 , x_msg_count => l_msg_count
4301 , x_msg_data => l_msg_data
4302 , p_Assignment_Set_rec => lAssignmentSetRec
4303 --, p_Assignment_Set_val_rec IN Assignment_Set_Val_Rec_Type := G_MISS_ASSIGNMENT_SET_VAL_REC
4304 , p_Assignment_tbl => lAssignmentTbl
4305 --, p_Assignment_val_tbl IN Assignment_Val_Tbl_Type := G_MISS_ASSIGNMENT_VAL_TBL
4306 , x_Assignment_Set_rec => xAssignmentSetRec
4307 , x_Assignment_Set_val_rec => xAssignmentSetValRec
4308 , x_Assignment_tbl => xAssignmentTbl
4309 , x_Assignment_val_tbl => xAssignmentValTbl
4310 );
4311
4312 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4313 IF PG_DEBUG <> 0 THEN
4314 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'unexp error in process_assignment::'||sqlerrm,1);
4315 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'l_msg_data::'||l_msg_data,1);
4316 END IF;
4317 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4318
4319 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4320 IF PG_DEBUG <> 0 THEN
4321 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'error in process_assignment::'||sqlerrm,1);
4322 END IF;
4323
4324
4325 oe_debug_pub.add('CTO_MSUTIL_PUB.create_sourcing_rules: count:'||l_msg_count , 1 );
4326
4327 IF l_msg_count > 0 THEN
4328 FOR l_index IN 1..l_msg_count LOOP
4329 l_msg_data := fnd_msg_pub.get(
4330 p_msg_index => l_index,
4331 p_encoded => FND_API.G_FALSE);
4332
4333 oe_debug_pub.add( 'CTO_MSUTIL_PUB.create_sourcing_rule: ' || substr(l_msg_data,1,250) , 1 );
4334 END LOOP;
4335
4336 oe_debug_pub.add(' CTO_MSUTIL_PUB.create_sourcing_rules: MSG:'|| xAssignmentSetRec.return_status);
4337 END IF;
4338
4339 oe_debug_pub.add('Failure!' , 1 );
4340
4341
4342 raise FND_API.G_EXC_ERROR;
4343
4344 END IF;
4345 IF PG_DEBUG <> 0 THEN
4346 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'success in process_assignment',2);
4347 END IF;
4348
4349
4350 lStmtNum := 70;
4351
4352 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'before end_of_loop',2);
4353 <<END_OF_LOOP>>
4354 null ;
4355 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'after end_of_loop',2);
4356 END LOOP;
4357
4358
4359 if( c_type3_assignments%rowcount = 0 ) then
4360
4361 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'no sourcing assignments ',2);
4362
4363 end if;
4364
4365 lStmtNum := 80;
4366
4367 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'before close c_type3_assignments ',2);
4368
4369 close c_type3_assignments ;
4370
4371 --debugging for bug 13362916
4372 oe_debug_pub.add('++++++++++++++++++++++++++++++++++++++++++');
4373 oe_debug_pub.add('printing config assignment values');
4374 FOR v_config_assignments in c_config_assignments(lMrpAssignmentSet, pConfigId) LOOP
4375 oe_debug_pub.add('set_id:' || v_config_assignments.assignment_set_id ||
4376 '::type::' || v_config_assignments.assignment_type ||
4377 '::org::' || v_config_assignments.organization_id ||
4378 '::cust::' || v_config_assignments.customer_id ||
4379 '::ship::' || v_config_assignments.ship_to_site_id ||
4380 '::rule_type::' || v_config_assignments.sourcing_rule_type ||
4381 '::cat::' || v_config_assignments.category_id);
4382 END LOOP;
4383 oe_debug_pub.add('++++++++++++++++++++++++++++++++++++++++++');
4384 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || 'Exit Create_TYPE3_Sourcing_Rules.');
4385 --end debugging for bug 13051516
4386
4387 EXCEPTION
4388 When NO_sourcing_defined THEN
4389 null;
4390
4391 when FND_API.G_EXC_ERROR then
4392 IF PG_DEBUG <> 0 THEN
4393 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || '::exp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
4394 END IF;
4395 x_return_status := FND_API.G_RET_STS_ERROR;
4396 CTO_MSG_PUB.Count_And_Get
4397 (p_msg_count => x_msg_count
4398 ,p_msg_data => x_msg_data
4399 );
4400
4401 when FND_API.G_EXC_UNEXPECTED_ERROR then
4402 IF PG_DEBUG <> 0 THEN
4403 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || '::unexp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
4404 END IF;
4405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4406 CTO_MSG_PUB.Count_And_Get
4407 (p_msg_count => x_msg_count
4408 ,p_msg_data => x_msg_data
4409 );
4410
4411 when OTHERS then
4412 IF PG_DEBUG <> 0 THEN
4413 oe_debug_pub.add('CTO_MSUTIL_PUB.create_type3_sourcing_rules: ' || '::others::'||to_char(lStmtNum)||'::'||sqlerrm,1);
4414 END IF;
4415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4416 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4417 FND_MSG_PUB.Add_Exc_Msg
4418 (G_PKG_NAME
4419 ,'Create_Sourcing_Rules'
4420 );
4421 END IF;
4422 CTO_MSG_PUB.Count_And_Get
4423 (p_msg_count => x_msg_count
4424 ,p_msg_data => x_msg_data
4425 );
4426
4427
4428 END Create_TYPE3_Sourcing_Rules;
4429
4430
4431
4432
4433 PROCEDURE initialize_assignment_set ( x_return_status OUT NOCOPY varchar2 )
4434 IS
4435 l_stmt_num number;
4436 assign_set_name varchar2(80);
4437 INVALID_MRP_ASSIGNMENT_SET exception ;
4438
4439 BEGIN
4440 /* begin for static block */
4441 x_return_status := FND_API.G_RET_STS_SUCCESS ;
4442
4443 /*
4444 ** get MRP's default assignment set
4445 */
4446 l_stmt_num := 1 ;
4447
4448 IF gMrpAssignmentSet is null THEN
4449 begin
4450
4451 gMrpAssignmentSet := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
4452 exception
4453 when others then
4454 raise invalid_mrp_assignment_set ;
4455 end ;
4456
4457 l_stmt_num := 5 ;
4458
4459 IF( gMrpAssignmentSet is null )
4460 THEN
4461 IF PG_DEBUG <> 0 THEN
4462 oe_debug_pub.add('initialize_assignment_set: ' || '**$$ Default assignment set is null', 1);
4463 END IF;
4464
4465 ELSE
4466 IF PG_DEBUG <> 0 THEN
4467 oe_debug_pub.add('initialize_assignment_set: ' || 'Default assignment set is '||to_char(gMrpAssignmentSet),2);
4468 END IF;
4469
4470 l_stmt_num := 10 ;
4471
4472 begin
4473
4474
4475 select assignment_set_name into assign_set_name
4476 from mrp_Assignment_sets
4477 where assignment_set_id = gMrpAssignmentSet ;
4478
4479 exception
4480 when no_data_found then
4481 IF PG_DEBUG <> 0 THEN
4482 oe_debug_pub.add('initialize_assignment_set: ' || 'The assignment set pointed by the
4483 profile MRP_DEFAULT_ASSIGNMENT_SET
4484 does not exist in the database ' ,1);
4485 END IF;
4486
4487 RAISE INVALID_MRP_ASSIGNMENT_SET ;
4488
4489 when others then
4490
4491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4492 end ;
4493
4494 IF PG_DEBUG <> 0 THEN
4495 oe_debug_pub.add('initialize_assignment_set: ' || 'Default assignment set name is '||
4496 assign_set_name ,2);
4497 END IF;
4498
4499 END IF;
4500
4501 END IF;
4502 exception
4503 when INVALID_MRP_ASSIGNMENT_SET then
4504 x_return_status := FND_API.G_RET_STS_ERROR;
4505
4506 IF PG_DEBUG <> 0 THEN
4507 oe_debug_pub.add('initialize_assignment_set: ' || 'INITIALIZE_ASSIGNMENT_SET::INVALID ASSIGNMENT SET ::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
4508 END IF;
4509
4510
4511 when FND_API.G_EXC_UNEXPECTED_ERROR then
4512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4513 IF PG_DEBUG <> 0 THEN
4514 oe_debug_pub.add('initialize_assignment_set: ' || 'INITIALIZE_ASSIGNMENT_SET::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
4515 END IF;
4516
4517
4518 when OTHERS then
4519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4520 IF PG_DEBUG <> 0 THEN
4521 oe_debug_pub.add('initialize_assignment_set: ' || 'INITIALIZE_ASSIGNMENT_SET::others::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
4522 END IF;
4523
4524
4525 END initialize_assignment_set ;
4526
4527
4528
4529 procedure insert_val_into_bcso( p_top_ato_line_id in NUMBER
4530 , p_model_line_id in NUMBER
4531 , p_model_item_id in NUMBER
4532 , p_t_org_list in CTO_MSUTIL_PUB.org_list
4533 , p_config_item_id in number default null)
4534 is
4535 i number ;
4536 v_source_type_code varchar2(20) ;
4537 begin
4538
4539 oe_debug_pub.add( '$$$ insert val into bcso ' , 1 ) ;
4540 oe_debug_pub.add( '$$$ VAL ORGS count ' || p_t_org_list.count , 1 ) ;
4541
4542
4543
4544 begin
4545 select source_type_code into v_source_type_code
4546 from oe_order_lines_all
4547 where line_id = p_top_ato_line_id ;
4548 exception
4549 when others then
4550 v_source_type_code := 'INTERNAL' ;
4551 end ;
4552
4553 oe_debug_pub.add( '$$$ source type code ' || v_source_type_code , 1 ) ;
4554
4555
4556
4557 if( p_t_org_list.count > 0 ) then
4558 -- for i in 1..p_t_org_list.count
4559
4560 i := p_t_org_list.first ;
4561
4562 while i is not null
4563 loop
4564
4565 oe_debug_pub.add( '$$$ VAL ORGS ' || p_t_org_list(i) , 1 ) ;
4566
4567 insert into bom_cto_src_orgs_b
4568 (
4569 top_model_line_id,
4570 line_id,
4571 model_item_id,
4572 rcv_org_id,
4573 organization_id,
4574 create_bom,
4575 cost_rollup,
4576 organization_type, -- Used to store the source type
4577 config_item_id,
4578 create_src_rules,
4579 rank,
4580 creation_date,
4581 created_by,
4582 last_update_date,
4583 last_updated_by,
4584 last_update_login,
4585 program_application_id,
4586 program_id,
4587 program_update_date
4588 )
4589 select -- distinct
4590 p_top_ato_line_id ,
4591 p_model_line_id ,
4592 p_model_item_id ,
4593 null ,
4594 p_t_org_list(i),
4595 'N', -- create_bom
4596 'N', -- cost_rollup
4597 /* commented for dropship decode( v_source_type_code , 'INTERNAL' , NULL ,
4598 decode( p_top_ato_line_id, p_model_line_id, '5', '6' )) , -- org_type used for source type
4599 */
4600 NULL,
4601 p_config_item_id, -- config_item_id
4602 'N',
4603 NULL , /* rank */
4604 sysdate, -- creation_date
4605 gUserId, -- created_by
4606 sysdate, -- last_update_date
4607 gUserId, -- last_updated_by
4608 gLoginId, -- last_update_login
4609 null, -- program_application_id,??
4610 null, -- program_id,??
4611 sysdate -- program_update_date
4612 from dual
4613 where NOT EXISTS
4614 (select NULL
4615 from bom_cto_src_orgs_b
4616 where line_id = p_model_line_id
4617 and model_item_id = p_model_item_id
4618 and organization_id = p_t_org_list(i) );
4619
4620 i := p_t_org_list.next(i) ;
4621
4622 end loop ;
4623
4624 end if ;
4625
4626
4627
4628 end insert_val_into_bcso ;
4629
4630
4631
4632 procedure insert_all_into_bcso( p_top_ato_line_id in NUMBER
4633 , p_model_line_id in NUMBER
4634 , p_model_item_id in NUMBER
4635 , p_config_item_id in NUMBER default null)
4636 is
4637 v_source_type_code varchar2(20) ;
4638
4639 begin
4640
4641 oe_debug_pub.add( '$$$ insert all into bcso ' , 1 ) ;
4642
4643
4644 begin
4645 select source_type_code into v_source_type_code
4646 from oe_order_lines_all
4647 where line_id = p_top_ato_line_id ;
4648 exception
4649 when others then
4650 v_source_type_code := 'INTERNAL' ;
4651 end ;
4652
4653 oe_debug_pub.add( '$$$ source type code ' || v_source_type_code , 1 ) ;
4654
4655
4656
4657
4658 insert into bom_cto_src_orgs_b
4659 (
4660 top_model_line_id,
4661 line_id,
4662 model_item_id,
4663 rcv_org_id,
4664 organization_id,
4665 create_bom,
4666 cost_rollup,
4667 organization_type, -- Used to store the source type
4668 config_item_id,
4669 create_src_rules,
4670 rank,
4671 creation_date,
4672 created_by,
4673 last_update_date,
4674 last_updated_by,
4675 last_update_login,
4676 program_application_id,
4677 program_id,
4678 program_update_date
4679 )
4680 select -- distinct
4681 p_top_ato_line_id ,
4682 p_model_line_id ,
4683 p_model_item_id ,
4684 null ,
4685 msi.organization_id,
4686 'N', -- create_bom
4687 'N', -- cost_rollup
4688 /* commented for dropship
4689 decode( v_source_type_code , 'INTERNAL' , NULL ,
4690 decode( p_top_ato_line_id, p_model_line_id, '5', '6' )) , -- org_type used for source type
4691 */
4692 NULL,
4693 p_config_item_id, -- config_item_id
4694 'N',
4695 NULL , /* rank */
4696 sysdate, -- creation_date
4697 gUserId, -- created_by
4698 sysdate, -- last_update_date
4699 gUserId, -- last_updated_by
4700 gLoginId, -- last_update_login
4701 null, -- program_application_id,??
4702 null, -- program_id,??
4703 sysdate -- program_update_date
4704 from mtl_system_items msi
4705 where msi.inventory_item_id = p_model_item_id
4706 and NOT EXISTS
4707 (select NULL
4708 from bom_cto_src_orgs_b
4709 where line_id = p_model_line_id
4710 and model_item_id = msi.inventory_item_id
4711 and organization_id = msi.organization_id );
4712
4713
4714
4715 oe_debug_pub.add( '$$$ insert all into bcso ' || SQL%rowcount , 1 ) ;
4716
4717 end insert_all_into_bcso ;
4718
4719
4720
4721
4722 procedure insert_type3_bcso( p_top_ato_line_id in NUMBER
4723 , p_model_line_id in NUMBER
4724 , p_model_item_id in NUMBER
4725 , p_config_item_id in NUMBER default null )
4726 is
4727 begin
4728
4729 oe_debug_pub.add( '$$$ insert type3 bcso ' , 1 ) ;
4730
4731
4732 insert into bom_cto_src_orgs_b
4733 (
4734 top_model_line_id,
4735 line_id,
4736 model_item_id,
4737 rcv_org_id,
4738 organization_id,
4739 create_bom,
4740 cost_rollup,
4741 organization_type, -- Used to store the source type
4742 config_item_id,
4743 create_src_rules,
4744 rank,
4745 creation_date,
4746 created_by,
4747 last_update_date,
4748 last_updated_by,
4749 last_update_login,
4750 program_application_id,
4751 program_id,
4752 program_update_date
4753 )
4754 select -- distinct
4755 p_top_ato_line_id ,
4756 p_model_line_id ,
4757 p_model_item_id ,
4758 null ,
4759 msi.organization_id,
4760 decode( bp.create_config_bom , 'Y',
4761 decode(bom.assembly_item_id, msi.inventory_item_id, 'Y', 'N')
4762 , 'N') , -- create_bom
4763 decode(bp.organization_id , null , 'N' , 'Y') , -- cost_rollup
4764 decode( msi.planning_make_buy_code, 2, 3 , 2 ) , -- org_type should be 3(buy) for buy items else 2(make)
4765 p_config_item_id, -- config_item_id
4766 'N', -- create_src_rules
4767 NULL , /* rank */
4768 sysdate, -- creation_date
4769 gUserId, -- created_by
4770 sysdate, -- last_update_date
4771 gUserId, -- last_updated_by
4772 gLoginId, -- last_update_login
4773 null, -- program_application_id,??
4774 null, -- program_id,??
4775 sysdate -- program_update_date
4776 from mtl_system_items msi, bom_bill_of_materials bom, bom_parameters bp
4777 where msi.inventory_item_id = p_model_item_id
4778 and msi.inventory_item_id = bom.assembly_item_id(+)
4779 and msi.organization_id = bom.organization_id(+)
4780 and bom.alternate_bom_designator is null
4781 and msi.organization_id = bp.organization_id (+) /* added for bug 3504744 */
4782 and NOT EXISTS
4783 (select NULL
4784 from bom_cto_src_orgs_b
4785 where line_id = p_model_line_id
4786 and model_item_id = msi.inventory_item_id
4787 and organization_id = msi.organization_id );
4788
4789
4790
4791
4792 oe_debug_pub.add( '$$$ insert type3 bcso ' || SQL%rowcount , 1 ) ;
4793
4794 end insert_type3_bcso ;
4795
4796
4797
4798
4799 procedure insert_type3_bcmo_bcso( p_top_ato_line_id in NUMBER
4800 , p_model_line_id in NUMBER
4801 , p_model_item_id in NUMBER)
4802 is
4803 v_group_reference_id number(10);
4804 begin
4805
4806 oe_debug_pub.add( '$$$ insert type3 bcmo bcso ' , 1 ) ;
4807
4808 select bom_cto_model_orgs_s1.nextval into v_group_reference_id from dual ;
4809
4810
4811
4812 insert into bom_cto_model_orgs
4813 (
4814 reference_id,
4815 group_reference_id,
4816 model_item_id,
4817 rcv_org_id,
4818 organization_id,
4819 create_bom,
4820 cost_rollup,
4821 organization_type, -- Used to store the source type
4822 config_item_id,
4823 create_src_rules,
4824 rank,
4825 creation_date,
4826 created_by,
4827 last_update_date,
4828 last_updated_by,
4829 last_update_login,
4830 program_application_id,
4831 program_id,
4832 program_update_date
4833 )
4834 select -- distinct
4835 bom_cto_model_orgs_s1.nextval,
4836 v_group_reference_id,
4837 p_model_item_id ,
4838 null ,
4839 msi.organization_id,
4840 decode( bp.create_config_bom , 'Y',
4841 decode(bom.assembly_item_id, msi.inventory_item_id, 'Y', 'N')
4842 , 'N') , -- create_bom
4843 decode(bp.organization_id , null , 'N' , 'Y') , -- cost_rollup
4844 decode( msi.planning_make_buy_code, 2, 3 , 2 ) , -- org_type should be 3(buy) for buy items else 2(make)
4845 NULL, -- config_item_id
4846 'N',
4847 NULL , /* rank */
4848 sysdate, -- creation_date
4849 gUserId, -- created_by
4850 sysdate, -- last_update_date
4851 gUserId, -- last_updated_by
4852 gLoginId, -- last_update_login
4853 null, -- program_application_id,??
4854 null, -- program_id,??
4855 sysdate -- program_update_date
4856 from mtl_system_items msi, bom_bill_of_materials bom, bom_parameters bp
4857 where msi.inventory_item_id = p_model_item_id
4858 and msi.inventory_item_id = bom.assembly_item_id(+)
4859 and msi.organization_id = bom.organization_id(+)
4860 and bom.alternate_bom_designator is null
4861 and msi.organization_id = bp.organization_id (+) /* added for bug 3504744 */;
4862 /*
4863 and NOT EXISTS
4864 (select NULL
4865 from bom_cto_model_orgs bcmo
4866 where bcmo.model_item_id = msi.inventory_item_id
4867 and bcmo.organization_id = msi.organization_id );
4868 */
4869
4870
4871
4872 oe_debug_pub.add( '$$$ insert type3 bcmo bcmo ' || SQL%rowcount , 1 ) ;
4873
4874 insert into bom_cto_src_orgs_b
4875 (
4876 top_model_line_id,
4877 line_id,
4878 group_reference_id,
4879 model_item_id,
4880 rcv_org_id,
4881 organization_id,
4882 create_bom,
4883 cost_rollup,
4884 organization_type, -- Used to store the source type
4885 config_item_id,
4886 create_src_rules,
4887 rank,
4888 creation_date,
4889 created_by,
4890 last_update_date,
4891 last_updated_by,
4892 last_update_login,
4893 program_application_id,
4894 program_id,
4895 program_update_date
4896 )
4897 select -- distinct
4898 p_top_ato_line_id ,
4899 p_model_line_id ,
4900 v_group_reference_id,
4901 p_model_item_id ,
4902 null ,
4903 -1, -- organization_id is -1 for type 3 matched
4904 null, -- create_bom
4905 'Y', -- cost_rollup
4906 NULL , -- org_type is used to store the source type
4907 NULL, -- config_item_id
4908 NULL,
4909 NULL , /* rank */
4910 sysdate, -- creation_date
4911 gUserId, -- created_by
4912 sysdate, -- last_update_date
4913 gUserId, -- last_updated_by
4914 gLoginId, -- last_update_login
4915 null, -- program_application_id,??
4916 null, -- program_id,??
4917 sysdate -- program_update_date
4918 from dual
4919 where NOT EXISTS
4920 (select NULL
4921 from bom_cto_src_orgs_b
4922 where line_id = p_model_line_id );
4923
4924
4925
4926 oe_debug_pub.add( '$$$ insert type3 bcmo bcso ' || SQL%rowcount , 1 ) ;
4927
4928
4929
4930
4931 end insert_type3_bcmo_bcso ;
4932
4933
4934
4935
4936
4937
4938 procedure insert_type3_referenced_bcso( p_top_ato_line_id in NUMBER
4939 , p_model_line_id in NUMBER
4940 , p_model_item_id in NUMBER
4941 , p_config_item_id in NUMBER default null )
4942 is
4943 v_group_reference_id number(10);
4944 lCnt number; --debug 10240482
4945 begin
4946
4947 oe_debug_pub.add( '$$$ insert type3 referenced bcso ' , 1 ) ;
4948
4949 --debug 10240482
4950 oe_debug_pub.add( '$$$ insert type3 referenced bcso: p_top_ato_line_id:' || p_top_ato_line_id) ;
4951 oe_debug_pub.add( '$$$ insert type3 referenced bcso: p_model_line_id:' || p_model_line_id) ;
4952 oe_debug_pub.add( '$$$ insert type3 referenced bcso: p_model_item_id:' || p_model_item_id) ;
4953 oe_debug_pub.add( '$$$ insert type3 referenced bcso: p_config_item_id:' || p_config_item_id) ;
4954 --debug end
4955
4956 begin
4957 select group_reference_id into v_group_reference_id from bom_cto_model_orgs
4958 where config_item_id = p_config_item_id and rownum = 1 ; /* all records have the same group reference id */
4959
4960
4961 exception
4962 when no_data_found then
4963
4964 oe_debug_pub.add( '$$$ short circuit for type3 referenced bcso ' , 1 ) ;
4965 select bom_cto_model_orgs_s1.nextval into v_group_reference_id from dual ;
4966
4967
4968
4969 insert into bom_cto_model_orgs
4970 (
4971 reference_id,
4972 group_reference_id,
4973 model_item_id,
4974 rcv_org_id,
4975 organization_id,
4976 create_bom,
4977 cost_rollup,
4978 organization_type, -- Used to store the source type
4979 config_item_id,
4980 create_src_rules,
4981 rank,
4982 creation_date,
4983 created_by,
4984 last_update_date,
4985 last_updated_by,
4986 last_update_login,
4987 program_application_id,
4988 program_id,
4989 program_update_date
4990 )
4991 select -- distinct
4992 bom_cto_model_orgs_s1.nextval,
4993 v_group_reference_id,
4994 p_model_item_id ,
4995 null ,
4996 msi.organization_id,
4997 decode( bp.create_config_bom , 'Y',
4998 decode(bom.assembly_item_id, msi.inventory_item_id, 'Y', 'N')
4999 , 'N') , -- create_bom
5000 decode(bp.organization_id , null , 'N' , 'Y') , -- cost_rollup
5001 decode( msi.planning_make_buy_code, 2, 3 , 2 ) , -- org_type should be 3(buy) for buy items else 2(make)
5002 p_config_item_id, -- config_item_id
5003 'N',
5004 NULL , /* rank */
5005 sysdate, -- creation_date
5006 gUserId, -- created_by
5007 sysdate, -- last_update_date
5008 gUserId, -- last_updated_by
5009 gLoginId, -- last_update_login
5010 null, -- program_application_id,??
5011 null, -- program_id,??
5012 sysdate -- program_update_date
5013 from mtl_system_items msi, bom_bill_of_materials bom, bom_parameters bp
5014 where msi.inventory_item_id = p_model_item_id
5015 and msi.inventory_item_id = bom.assembly_item_id(+)
5016 and msi.organization_id = bom.organization_id(+)
5017 and bom.alternate_bom_designator is null
5018 and msi.organization_id = bp.organization_id (+) /* added for bug 3504744 */;
5019
5020 --debug 10240482
5021 lCnt := sql%rowcount;
5022 oe_debug_pub.add('$$$ insert type3 referenced bcso: Rows in bcmo:' || lCnt , 1 ) ;
5023
5024
5025
5026 when others then
5027
5028
5029 null ;
5030 raise ;
5031
5032 end ;
5033
5034
5035 insert into bom_cto_src_orgs_b
5036 (
5037 top_model_line_id,
5038 line_id,
5039 group_reference_id,
5040 model_item_id,
5041 rcv_org_id,
5042 organization_id,
5043 create_bom,
5044 cost_rollup,
5045 organization_type, -- Used to store the source type
5046 config_item_id,
5047 create_src_rules,
5048 rank,
5049 creation_date,
5050 created_by,
5051 last_update_date,
5052 last_updated_by,
5053 last_update_login,
5054 program_application_id,
5055 program_id,
5056 program_update_date
5057 )
5058 select -- distinct
5059 p_top_ato_line_id ,
5060 p_model_line_id ,
5061 v_group_reference_id,
5062 p_model_item_id ,
5063 null ,
5064 -1, -- organization_id is -1 for type3 matched
5065 null, -- create_bom
5066 'Y', -- cost_rollup /* TYPE3 rollup can be avoided for matched items */
5067 NULL , -- org_type is used to store the source type
5068 p_config_item_id, -- config_item_id
5069 NULL,
5070 NULL , /* rank */
5071 sysdate, -- creation_date
5072 gUserId, -- created_by
5073 sysdate, -- last_update_date
5074 gUserId, -- last_updated_by
5075 gLoginId, -- last_update_login
5076 null, -- program_application_id,??
5077 null, -- program_id,??
5078 sysdate -- program_update_date
5079 from dual
5080 where NOT EXISTS
5081 (select NULL
5082 from bom_cto_src_orgs_b
5083 where line_id = p_model_line_id );
5084
5085 --debug 10240482
5086 lCnt := sql%rowcount;
5087 oe_debug_pub.add('$$$ insert type3 referenced bcso: Rows in bcso:' || lCnt , 1 ) ;
5088 --oe_debug_pub.add( '$$$ insert type3 referenced bcso ' || SQL%rowcount , 1 ) ;
5089
5090
5091
5092
5093 end insert_type3_referenced_bcso ;
5094
5095
5096
5097
5098 /**********************************
5099 ASSUMPTIONS:
5100
5101 For each operating unit there shud be ONE OE Validation and
5102 ONE PO validation Org present
5103
5104 Is more than ONE OEV or POV a valid scenario ?
5105 Is it a valid scenario for oper unit having no OEV or POV ?
5106
5107 ***********************************/
5108
5109
5110 Procedure get_other_orgs (
5111 pModelLineId IN NUMBER,
5112 p_mode IN VARCHAR2 default 'ACC',
5113 xOrgLst OUT NOCOPY CTO_MSUTIL_PUB.Org_list,
5114 x_return_status OUT NOCOPY VARCHAR2,
5115 x_msg_count OUT NOCOPY NUMBER,
5116 x_msg_data OUT NOCOPY VARCHAR2
5117 ) IS
5118
5119 lOperUnit inv_organization_info_v.operating_unit%TYPE;
5120 xModelItemId bom_cto_src_orgs.model_item_id%TYPE;
5121 l_model_vendors PO_AUTOSOURCE_SV.vendor_record_details;
5122 l_doc_header_id Number;
5123 l_doc_type_code Varchar2(20);
5124 l_doc_line_num Number;
5125 l_doc_line_id Number;
5126 l_vendor_contact_id Number;
5127 -- 4283726 l_vendor_product_num Varchar2(50);
5128 l_vendor_product_num po_approved_supplier_list.primary_vendor_item%type; -- 4283726
5129 l_buyer_id Number;
5130 -- 4283726 l_purchase_uom Varchar2(10);
5131 l_purchase_uom po_asl_attributes.purchasing_unit_of_measure%type; -- 4283726
5132 l_doc_return Varchar2(5);
5133 l_ga_flag po_headers_all.global_Agreement_flag%TYPE;
5134 l_own_org po_headers_all.org_id%TYPE;
5135 l_chk_own_oper_unit Varchar2(1) := 'N'; -- 3348635
5136 l_enable_flag varchar2(1);
5137 l_own_pov_org Number;
5138 i Number := 0;
5139 z Number;
5140 lstmt_num Number;
5141 l_config_creation Varchar2(1);
5142 l_chk_org Varchar2(1) := 'N';
5143
5144 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
5145
5146 /* Get all bcso orgs . This is needed determine final xOrgList */
5147 cursor get_bcso_orgs is
5148 select organization_id bcso_org_id
5149 from bom_cto_src_orgs
5150 where line_id = pModelLineId;
5151
5152 xOrgLst_copy CTO_MSUTIL_PUB.Org_list;
5153 begin
5154
5155 /* Clear OrgList array if any element exist */
5156
5157
5158 xOrgLst.DELETE;
5159
5160 x_return_status := FND_API.G_RET_STS_SUCCESS ;
5161
5162
5163 IF p_mode = 'UPG' THEN
5164 select inventory_item_id,
5165 nvl(config_creation,1)
5166 into xModelItemId,
5167 l_config_creation
5168 from bom_cto_order_lines_upg
5169 where line_id=pModelLineId;
5170
5171 ELSE
5172
5173 select inventory_item_id,
5174 nvl(config_creation,1)
5175 into xModelItemId,
5176 l_config_creation
5177 from bom_cto_order_lines
5178 where line_id=pModelLineId;
5179 END IF;
5180
5181 if l_config_creation in ('2','3') then
5182
5183 lstmt_num := 99;
5184
5185 select organization_id
5186 BULK COLLECT into xOrgLst
5187 from mtl_system_items
5188 where inventory_item_id = xModelItemId
5189 and organization_id not in (
5190 select organization_id
5191 from bom_cto_src_orgs
5192 where line_id = pModelLineId);
5193
5194 else
5195
5196
5197 lstmt_num := 1;
5198
5199 -- rkaza. 3742393. 08/12/2004.
5200 -- Repalcing org_organization_definitions with inv_organization_info_v
5201
5202 select distinct ou_id
5203 BULK COLLECT INTO xOrgLst
5204 from
5205 ( select distinct to_number(nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',ood.operating_unit),-99)) ou_id
5206 from inv_organization_info_v ood,
5207 bom_cto_src_orgs bcso
5208 where ( ood.organization_id = bcso.organization_id
5209 or
5210 ood.organization_id = bcso.rcv_org_id
5211 )
5212 and bcso.line_id = pModelLineId
5213 UNION
5214 select distinct nvl(inventory_organization_id,-99) ou_id
5215 from financials_system_params_all
5216 where org_id in
5217 (
5218 select distinct ood.operating_unit
5219 from inv_organization_info_v ood,
5220 bom_cto_src_orgs bcso
5221 where ( ood.organization_id = bcso.organization_id
5222 or
5223 ood.organization_id = bcso.rcv_org_id
5224 )
5225 and bcso.line_id = pModelLineId
5226 )
5227 UNION /* added for bug 4291847. item should be enabled in validation org of operating unit where the order was entered */
5228 ( select
5229 to_number(nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',oel.org_id),-99)) ou_id
5230 from oe_order_lines_all oel where oel.line_id = pModelLineid )
5231 );
5232
5233
5234 -- Printing Orgs
5235 if xOrgLst.count > 0 then
5236 for x1 in xOrgLst.FIRST..xOrgLst.LAST loop
5237
5238 IF PG_DEBUG <> 0 THEN
5239 oe_debug_pub.add ('get_other_orgs:'||'OE and PO Validation Orgs ('||x1||') = '||xOrglst(x1),5);
5240 END IF;
5241
5242 end loop;
5243 end if;
5244
5245 -- Getting current number of elements in xOrgLst
5246
5247 i := xOrgLst.COUNT;
5248
5249
5250 begin
5251 select 'Y' into l_chk_org
5252 from dual
5253 where EXISTS (
5254 select 1
5255 from bom_cto_src_orgs
5256 where line_id = pModelLineId
5257 and organization_type in (3,5));
5258
5259
5260
5261 exception
5262 when no_data_found then
5263 l_chk_org := 'N' ;
5264
5265 when others then
5266 raise ;
5267
5268
5269 end ;
5270
5271
5272
5273 if l_chk_org = 'Y' then /* Only execute if any procuring org in bcso */
5274
5275
5276 lstmt_num := 2;
5277
5278 /* Get all ASL */
5279
5280 PO_AUTOSOURCE_SV.get_all_item_asl(
5281 x_item_id => xModelItemId,
5282 X_using_organization_id => -1,
5283 x_vendor_details => l_model_vendors,
5284 x_return_status => x_return_status,
5285 x_msg_count => x_msg_count,
5286 x_msg_data => x_msg_data);
5287
5288
5289
5290
5291 /* For each ASL get global blanket agreements */
5292
5293 lstmt_num := 3;
5294
5295 z := l_model_vendors.first;
5296
5297 while ( z is not null ) loop
5298
5299 IF PG_DEBUG <> 0 THEN
5300 oe_debug_pub.add ('get_other_orgs:'||'Vendor ID '||l_model_vendors(z).vendor_id,5);
5301 oe_debug_pub.add ('get_other_orgs:'||'Vendor Site ID '||l_model_vendors(z).vendor_site_id,5);
5302 oe_debug_pub.add ('get_other_orgs:'||'ASL ID '||l_model_vendors(z).asl_id,5);
5303
5304 END IF;
5305
5306 PO_AUTOSOURCE_SV.blanket_document_sourcing(
5307 x_item_id => xModelItemId,
5308 x_vendor_id => l_model_vendors(z).vendor_id,
5309 x_vendor_site_id => l_model_vendors(z).vendor_site_id,
5310 x_asl_id => l_model_vendors(z).asl_id,
5311 x_destination_doc_type => null,
5312 x_organization_id => -1,
5313 x_currency_code => null,
5314 x_item_rev => null,
5315 x_autosource_date => null,
5316 x_document_header_id => l_doc_header_id,
5317 x_document_type_code => l_doc_type_code,
5318 x_document_line_num => l_doc_line_num,
5319 x_document_line_id => l_doc_line_id,
5320 x_vendor_contact_id => l_vendor_contact_id,
5321 x_vendor_product_num => l_vendor_product_num,
5322 x_buyer_id => l_buyer_id,
5323 x_purchasing_uom => l_purchase_uom,
5324 x_multi_org => 'Y',
5325 x_doc_return => l_doc_return,
5326 x_return_status => x_return_status,
5327 x_msg_count => x_msg_count,
5328 x_msg_data => x_msg_data);
5329
5330 IF PG_DEBUG <> 0 THEN
5331
5332 oe_debug_pub.add ('get_other_orgs:'||'Doc Header ID '||l_doc_header_id,5);
5333
5334 END IF;
5335
5336 if l_doc_return = 'Y' then
5337
5338 IF PG_DEBUG <> 0 THEN
5339 oe_debug_pub.add('get_other_orgs:'|| 'Valid Blanket found for config ..',5);
5340 END IF;
5341
5342 else
5343 IF PG_DEBUG <> 0 THEN
5344 oe_debug_pub.add('get_other_orgs:' || 'Valid Blanket not found for this config',5);
5345 END IF;
5346
5347 exit;
5348
5349 end if;
5350
5351
5352 /* Chk if blanket global and get owning OU */
5353
5354 lstmt_num := 4;
5355
5356 select global_agreement_flag,org_id
5357 into l_ga_flag,l_own_org
5358 from po_headers_all
5359 where po_header_id = l_doc_header_id ;
5360
5361
5362
5363
5364 -- bugfix 3348635
5365 IF PG_DEBUG <> 0 THEN
5366 oe_debug_pub.add('get_other_orgs:' || 'Ga Flag: '||l_ga_flag||' :: Owning Org: '||l_own_org ,5);
5367 END IF;
5368
5369
5370
5371 /* If any global blanket , check if owning OU is enabled and in bcso with org_type in 3,5 */
5372
5373 lstmt_num := 5;
5374
5375 if l_ga_flag = 'Y' then
5376
5377 begin
5378 -- rkaza. 3742393. 08/12/2004.
5379 -- Repalcing org_organization_definitions with inv_organization_info_v
5380 select 'Y' into l_chk_own_oper_unit
5381 from dual
5382 where EXISTS (
5383 select po_header_id
5384 from po_ga_org_assignments
5385 where enabled_flag = 'Y'
5386 and organization_id in (
5387 select odd.operating_unit
5388 from inv_organization_info_v odd,
5389 bom_cto_src_orgs bcso
5390 where bcso.line_id = pModelLineId
5391 and bcso.organization_type in (3,5)
5392 and odd.organization_id = bcso.organization_id));
5393
5394
5395 exception
5396 WHEN NO_DATA_FOUND then
5397 l_chk_own_oper_unit := 'N';
5398 end;
5399
5400
5401
5402 -- bugfix 3348635
5403 IF PG_DEBUG <> 0 THEN
5404 oe_debug_pub.add('get_other_orgs:' || 'Owning Op Unit: '||l_chk_own_oper_unit,5);
5405 END IF;
5406
5407
5408
5409
5410 /* Get PO Validation org for owning OU's which are enabled */
5411
5412 lstmt_num := 6;
5413
5414 if l_chk_own_oper_unit = 'Y' then
5415 select inventory_organization_id
5416 into l_own_pov_org
5417 from financials_system_params_all
5418 where org_id = l_own_org;
5419 end if; /* lchk_own_oper_unit = Y */
5420
5421 end if; /* l_ga_flag = 'Y' */
5422
5423
5424
5425 -- bugfix 3348635
5426 IF PG_DEBUG <> 0 THEN
5427 oe_debug_pub.add('get_other_orgs:' || 'Owning POV Org: '||l_own_pov_org,5);
5428 END IF;
5429
5430
5431
5432 -- insert PO Validation Org in the list
5433
5434 lstmt_num := 7;
5435
5436 if l_own_pov_org is NOT NULL then -- bugfix 3348635
5437 xOrglst(i + 1) := l_own_pov_org;
5438 i := i + 1; -- 3785158
5439
5440 end if;
5441
5442
5443 -- increment i
5444
5445
5446
5447 z := l_model_vendors.next(z); --bugfix 3348635 , 3785158
5448
5449 end loop;
5450
5451 -- setting i to 0
5452
5453 i := 0;
5454
5455 -- Printing Orgs
5456 if xOrgLst.count > 0 then
5457 for x2 in xOrgLst.FIRST..xOrgLst.LAST loop
5458
5459 IF PG_DEBUG <> 0 THEN
5460 oe_debug_pub.add ('get_other_orgs:'||'Validation Orgs ('||x2||') = '||xOrglst(x2),5);
5461 END IF;
5462
5463 end loop;
5464
5465 end if;
5466
5467 end if ; -- Procure org chk in bcso
5468
5469
5470
5471 /* From final list , remove all orgs which are in bcso */
5472
5473 lstmt_num := 8;
5474
5475 for rget_bcso_orgs in get_bcso_orgs loop
5476
5477 if xOrgLst.count > 0 then
5478
5479 for x4 in xOrglst.FIRST..xOrglst.LAST loop
5480
5481
5482 if xOrgLst.exists(x4) then
5483 if xOrgLst(x4) = rget_bcso_orgs.bcso_org_id then
5484
5485 IF PG_DEBUG <> 0 THEN
5486 oe_debug_pub.add ('get_other_orgs:'||'Deleting Org ('||x4||') = '||xOrglst(x4),5);
5487 END IF;
5488
5489 xOrgLst.delete(x4);
5490
5491
5492 end if;
5493 end if;
5494
5495 end loop; /* End loop xOrgLst */
5496
5497 end if;
5498
5499 end loop; /* End loop bcso orgs */
5500
5501
5502 /*
5503 The copy loop has been placed outside the above loop as each org may be copied more than once.
5504 Also, Orgs that could be subsequently deleted may get copied during earlier iterations.
5505 */
5506
5507 if( xOrgLst.count > 0 ) then
5508 for x6 in xOrgLst.First..xOrgLst.Last loop
5509
5510 if xOrgLst.exists(x6) then
5511 oe_debug_pub.add ('get_other_orgs:'||'Copied Org ('||x6||') = '||xOrglst(x6),5);
5512 xOrgLst_copy(xOrgLst_copy.count + 1) := xOrgLst(x6) ;
5513
5514 end if;
5515
5516 end loop ;
5517
5518
5519 xOrgLst := xOrgLst_copy ; /* Assign Copied List to Original List */
5520
5521 end if;
5522
5523
5524 End if; /* CIB Attribute check */
5525
5526
5527
5528
5529 -- Printing Final List of Orgs
5530
5531 if xOrgLst.count > 0 then
5532
5533 for x5 in xOrgLst.FIRST..xOrgLst.LAST loop
5534
5535 if xOrgLst.exists(x5) then
5536
5537
5538 IF PG_DEBUG <> 0 THEN
5539 oe_debug_pub.add ('get_other_orgs:'||'Final Org List ('||x5||') = '||xOrglst(x5),5);
5540 END IF;
5541
5542
5543
5544 end if;
5545
5546 end loop;
5547
5548 end if;
5549
5550
5551 exception /* added exception handling for expected and unexpected error as part of bug 4227127 (fp for bug 4162642) */
5552 when FND_API.G_EXC_UNEXPECTED_ERROR then
5553 IF PG_DEBUG <> 0 THEN
5554 oe_debug_pub.add('get_other_orgs: ' || 'GET_OTHER_ORGS::unexp error::'|| to_char(lstmt_num) ||sqlerrm,1);
5555 END IF;
5556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5557 CTO_MSG_PUB.Count_And_Get
5558 (p_msg_count => x_msg_count
5559 ,p_msg_data => x_msg_data
5560 );
5561
5562 when FND_API.G_EXC_ERROR then
5563 IF PG_DEBUG <> 0 THEN
5564 oe_debug_pub.add('get_other_orgs: ' || 'GET_OTHER_ORGS::exp error::'|| to_char(lstmt_num) ||sqlerrm,1);
5565 END IF;
5566 x_return_status := FND_API.G_RET_STS_ERROR;
5567 CTO_MSG_PUB.Count_And_Get
5568 (p_msg_count => x_msg_count
5569 ,p_msg_data => x_msg_data);
5570
5571
5572 WHEN OTHERS THEN
5573 IF PG_DEBUG <> 0 THEN
5574 oe_debug_pub.add('get_other_orgs: ' || 'GET_OTHER_ORGS::unexp error:: '||to_char(lstmt_num)||'::'||sqlerrm,5);
5575 END IF;
5576
5577 /* commented raise as the calling api will handle the error using x_return_status RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
5578
5579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5580
5581 CTO_MSG_PUB.Count_And_Get(p_msg_count => x_msg_count ,p_msg_data => x_msg_data);
5582
5583
5584 END get_other_orgs;
5585
5586
5587
5588
5589 procedure procured_model_bcso_override ( p_line_id in number
5590 , p_model_item_id in number
5591 , p_ship_org_id in number )
5592 is
5593 v_procured_models_exist varchar2(1) := 'N' ;
5594 v_bom_created varchar2(1) := 'N' ;
5595 v_ship_org_bom_update number ;
5596 lStmtNumber number ;
5597
5598
5599 v_receiving_org number ;
5600 begin
5601 begin
5602
5603 select 'Y' into v_procured_models_exist from dual
5604 where exists
5605 ( select * from bom_cto_src_orgs_b
5606 where line_id = p_line_id
5607 and model_item_id = p_model_item_id
5608 and nvl(organization_type , 2 ) in( '3' , '4' ) );
5609
5610
5611 oe_debug_pub.add( ' atleast one procured/dropship model/child model exist ' , 1);
5612
5613
5614 exception
5615 when others then
5616 oe_debug_pub.add( ' No procured/dropship model/child model exist ' , 1);
5617 null ;
5618 end ;
5619
5620
5621
5622 if( v_procured_models_exist = 'Y' ) then
5623 begin
5624
5625 v_bom_created := 'N' ;
5626
5627 select 'Y' into v_bom_created from dual
5628 where exists
5629 ( select * from bom_cto_src_orgs_b where line_id = p_line_id
5630 and model_item_id = p_model_item_id
5631 and create_bom = 'Y' );
5632
5633
5634 oe_debug_pub.add( ' atleast one org has bom flag = Y ' , 1);
5635
5636
5637 exception
5638 when others then
5639 null ;
5640 end ;
5641
5642
5643
5644 if( v_bom_created = 'N' ) then
5645
5646
5647
5648
5649
5650
5651
5652
5653
5654 begin
5655 select organization_id into v_receiving_org
5656 from bom_cto_src_orgs
5657 where line_id = p_line_id and organization_id = rcv_org_id
5658 and organization_type in ( '3' , '4' ) and rownum = 1 ;
5659
5660 oe_debug_pub.add( ' Org to be Updated ' || to_char(v_receiving_org) , 1);
5661
5662 exception
5663 when others then
5664 null ;
5665 oe_debug_pub.add( ' Got Error ' || SQLERRM , 1);
5666 v_receiving_org := p_ship_org_id ;
5667 oe_debug_pub.add( ' Assigning Ship Org as Org to be Updated ' || to_char(v_receiving_org) , 1);
5668 end ;
5669
5670
5671
5672 oe_debug_pub.add( ' need to create bom in atleast one receiving org as bom flag = Y
5673 does not exist for any org ' , 1);
5674
5675 lStmtNumber := 140;
5676 update bom_cto_src_orgs_b
5677 set create_bom = 'Y' /* , organization_type = l_source_type */
5678 where line_id = p_line_id
5679 and model_item_id = p_model_item_id
5680 and organization_id = v_receiving_org
5681 and rcv_org_id = v_receiving_org
5682 and exists
5683 ( select * from bom_parameters bp, bom_bill_of_materials bbom
5684 where bp.organization_id = v_receiving_org
5685 and bbom.organization_id = bp.organization_id
5686 and bbom.assembly_item_id = p_model_item_id
5687 and bp.create_config_bom = 'Y' ) ;
5688
5689
5690 oe_debug_pub.add( ' updated for org ' || v_receiving_org || ' rcv org ' || v_receiving_org ) ;
5691 oe_debug_pub.add( ' Records updated ' || SQL%ROWCOUNT ) ;
5692
5693
5694 v_ship_org_bom_update := SQL%ROWCOUNT ;
5695
5696
5697
5698
5699
5700
5701 if( v_ship_org_bom_update = 0 ) then
5702
5703
5704
5705
5706 oe_debug_pub.add( ' need to create bom in any org as shipping org does not have model bom or bom param' , 1);
5707
5708 lStmtNumber := 140;
5709 update bom_cto_src_orgs_b
5710 set create_bom = 'Y' /* , organization_type = l_source_type */
5711 where line_id = p_line_id
5712 and model_item_id = p_model_item_id
5713 and rcv_org_id in (
5714 select bp.organization_id
5715 from bom_parameters bp, bom_bill_of_materials bbom
5716 where bbom.organization_id = bp.organization_id
5717 and bbom.assembly_item_id = p_model_item_id
5718 and bp.create_config_bom = 'Y' )
5719 and rownum = 1 ;
5720
5721
5722 oe_debug_pub.add( ' updated in any shipping org Records updated ' || SQL%ROWCOUNT ) ;
5723
5724
5725 end if ; /* shipping_org no bom */
5726
5727
5728 end if; /* bom_created = 'N' */
5729
5730 end if ; /* procured_models */
5731
5732
5733
5734
5735 end procured_model_bcso_override ;
5736
5737 --- Added by Renga Kannan on 15-Sep-2005
5738 --- Added for R12 ATG Performance Project
5739
5740
5741 /*--------------------------------------------------------------------------+
5742 This procedure will get the model line id as input to give the list of
5743 master orgs where the item needs to be enabled.
5744 This will look the bcso tables to identify the list of orgs where the config
5745 item needs to be enabled due to sourcing and derive the master orgs for these organization
5746 and return them in pl/sql record struct.
5747 +-------------------------------------------------------------------------*/
5748
5749 PROCEDURE Get_Master_Orgs(
5750 p_model_line_id IN Number,
5751 x_orgs_list OUT NOCOPY CTO_MSUTIL_PUB.org_list,
5752 x_msg_count OUT NOCOPY Number,
5753 x_msg_data OUT NOCOPY varchar2,
5754 x_return_status OUT NOCOPY varchar2) is
5755 LSTMT_NUM Number :=10;
5756 i Number;
5757 Begin
5758
5759 If PG_DEBUG <> 0 Then
5760 oe_debug_pub.add('Get_Master_orgs: Entering Get_Master_orgs API for Model Line id = '||p_model_line_id,3);
5761 End if;
5762
5763 Begin
5764 Select distinct mp1.master_organization_id
5765 Bulk Collect into
5766 x_orgs_list
5767 from mtl_parameters mp1,
5768 bom_cto_src_orgs bcso
5769 where bcso.line_id = p_model_line_id
5770 and bcso.organization_id = mp1.organization_id
5771 and mp1.master_organization_id not in
5772 ( Select organization_id
5773 from bom_cto_src_orgs
5774 where line_id = p_model_line_id);
5775
5776 If PG_DEBUG <> 0 Then
5777 i := x_orgs_list.first;
5778 while (i is not null)
5779 Loop
5780 oe_debug_pub.add('Get_Master_Orgs: Master Org = '||x_orgs_list(i),5);
5781 i := x_orgs_list.next(i);
5782 End Loop;
5783 If i is null Then
5784 oe_debug_pub.add('Get_Master_Orgs: No master orgs insterted..',5);
5785 End if;
5786 End if;
5787 Exception when no_data_found then
5788 If PG_DEBUG <> 0 Then
5789 oe_debug_pub.add('Get_Master_Orgs: No new Masters orgs are added.... ',1);
5790 End if;
5791 End;
5792
5793 x_return_status := FND_API.G_RET_STS_SUCCESS;
5794
5795 Exception /* added exception handling for expected and unexpected error as part of bug 4227127 (fp for bug 4162642) */
5796 when FND_API.G_EXC_UNEXPECTED_ERROR then
5797 IF PG_DEBUG <> 0 THEN
5798 oe_debug_pub.add('Get_Master_orgs: ' || 'GET_OTHER_ORGS::unexp error::'|| to_char(lstmt_num) ||sqlerrm,1);
5799 END IF;
5800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5801 CTO_MSG_PUB.Count_And_Get
5802 (p_msg_count => x_msg_count
5803 ,p_msg_data => x_msg_data
5804 );
5805
5806 when FND_API.G_EXC_ERROR then
5807 IF PG_DEBUG <> 0 THEN
5808 oe_debug_pub.add('Get_Master_orgs: ' || 'GET_OTHER_ORGS::exp error::'|| to_char(lstmt_num) ||sqlerrm,1);
5809 END IF;
5810 x_return_status := FND_API.G_RET_STS_ERROR;
5811 CTO_MSG_PUB.Count_And_Get
5812 (p_msg_count => x_msg_count
5813 ,p_msg_data => x_msg_data);
5814
5815
5816 WHEN OTHERS THEN
5817 IF PG_DEBUG <> 0 THEN
5818 oe_debug_pub.add('get_other_orgs: ' || 'Get_Master_Orgs::unexp error:: '||to_char(lstmt_num)||'::'||sqlerrm,5);
5819 END IF;
5820
5821 /* commented raise as the calling api will handle the error using x_return_status RAISE FND_API.G_EXC_UNEXPECTED_ERROR; */
5822
5823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5824
5825 CTO_MSG_PUB.Count_And_Get(p_msg_count => x_msg_count ,p_msg_data => x_msg_data);
5826
5827 End Get_Master_orgs;
5828
5829
5830
5831 -- rkaza. 11/08/2005. bom sturcture import enhancements. bug 4524248.
5832 Procedure set_bom_batch_id(x_return_status OUT NOCOPY varchar2) IS
5833
5834 Begin
5835
5836 x_return_status := FND_API.G_RET_STS_SUCCESS;
5837
5838 bom_batch_id := Bom_Import_Pub.Get_BatchId;
5839
5840 if bom_batch_id = 0 then
5841 oe_debug_pub.add('Get_bom_batch_id: batch_id is 0', 1);
5842 raise FND_API.G_EXC_UNEXPECTED_ERROR;
5843 end if;
5844
5845 IF PG_DEBUG <> 0 THEN
5846 oe_debug_pub.add('Get_bom_batch_id: Batch_id = ' || bom_batch_id, 1);
5847 END IF;
5848
5849 Exception
5850
5851 When FND_API.G_EXC_UNEXPECTED_ERROR then
5852 IF PG_DEBUG <> 0 THEN
5853 oe_debug_pub.add('Get_bom_batch_id: unexpected error: ' || sqlerrm, 1);
5854 END IF;
5855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5856
5857 When others then
5858 IF PG_DEBUG <> 0 THEN
5859 oe_debug_pub.add('Get_bom_batch_id: unexpected error: ' || sqlerrm, 1);
5860 END IF;
5861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5862
5863 End set_bom_batch_id;
5864
5865
5866 -- Added by Renga Kannan 03/30/06
5867 -- This is a wrapper API to call PLM team's to sync up item media index
5868 -- With out this sync up the item cannot be searched in Simple item search page
5869 -- This is fixed for bug 4656048
5870
5871 Procedure Syncup_Item_Media_index is
5872 Begin
5873 -- Calling PLM API to sync up item media index
5874 EGO_ITEM_PUB.SYNC_IM_INDEX ;
5875 Exception When others then
5876 raise fnd_api.g_exc_unexpected_error;
5877 End Syncup_item_media_index;
5878
5879
5880
5881 -- Added by Renga Kannan on 04/28/06
5882 -- Utility API to Switch CONTEXT TO ORDER LINE CONTEXT
5883 -- For Bug Fix 5122923
5884
5885 Procedure Switch_to_oe_Context(
5886 p_oe_org_id IN Number,
5887 x_current_mode OUT NOCOPY Varchar2,
5888 x_current_org OUT NOCOPY Number,
5889 x_context_switch_flag OUT NOCOPY Varchar2) is
5890 Begin
5891 x_context_switch_flag := 'N';
5892 x_current_mode := nvl(MO_GLOBAL.get_access_mode,'N');
5893 x_current_org := nvl(MO_GLOBAL.get_current_org_id,-99);
5894
5895 If PG_DEBUG <> 0 Then
5896 oe_debug_pub.add('Switch_to_oe_Context : Order Line Org Id = '||to_char(p_oe_org_id),5);
5897 oe_debug_pub.add('Switch_to_oe_Context : Current Mode = '||x_current_mode,5);
5898 oe_debug_pub.add('Switch_to_oe_Context : Current Org = '||to_char(x_current_org),5);
5899 cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_status_batch: Order Line Org Id ='||to_char(p_oe_org_id));
5900 cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_Status_batch: Current Mode = '||x_current_mode);
5901 cto_wip_workflow_api_pk.cto_debug('Switch_to_oe_Context','Change_status_batch: Current org = '||to_char(x_current_org));
5902 end if;
5903
5904 If x_current_mode = 'N' or x_current_mode = 'M' or(x_current_mode = 'S' and p_oe_org_id <> x_current_org)
5905 or x_current_mode = 'A' --5446723
5906 Then
5907 If x_current_mode <> 'N' then
5908 x_context_switch_flag := 'Y';
5909 End if;
5910 If PG_DEBUG <> 0 Then
5911 oe_debug_pub.add('Switch_to_oe_Context : Changing the operating unit context to Order Line context',5);
5912 End if;
5913 MO_GLOBAL.set_policy_context(p_access_mode => 'S',
5914 p_org_id => p_oe_org_id);
5915
5916 Else--5446723
5917 If PG_DEBUG <> 0 Then
5918 oe_debug_pub.add('Switch_to_oe_Context : UN-EXPECTED MOAC MODE',5);
5919 End if;
5920 End if;
5921
5922 If PG_DEBUG <> 0 Then
5923 oe_debug_pub.add('SWITCH_TO_OE_CONTEXT : Done with Chaning the context to OE ',5);
5924 cto_wip_workflow_api_pk.cto_Debug('Switch_to_oe_Context:', 'Done with Chaning the context to OE');
5925 End if;
5926 End Switch_to_oe_context;
5927
5928 -- Added by Renga Kannan on 04/28/06
5929 -- For bug fix 5122923
5930
5931 Procedure Switch_context_back(
5932 p_old_mode IN Varchar2,
5933 p_old_org IN varchar2) is
5934 Begin
5935
5936 If p_old_mode = 'S' then
5937 MO_GLOBAL.set_policy_context(p_access_mode => 'S',
5938 p_org_id => p_old_org);
5939 elsif p_old_mode = 'M' then
5940 MO_GLOBAL.set_policy_context(p_access_mode => 'M',
5941 p_org_id => null);
5942 elsif p_old_mode = 'A' then --5446723
5943 MO_GLOBAL.set_policy_context(p_access_mode => 'A',
5944 p_org_id => null);
5945
5946 end if; /* l_old_mode = 'S' */
5947
5948 If PG_DEBUG <> 0 Then
5949 oe_debug_pub.add('Switch_context_back : Done with Chaning the context BAck',5);
5950 cto_wip_workflow_api_pk.cto_Debug('Switch_to_oe_Context:', 'Done with Chaning the context Back');
5951 End if;
5952
5953 End Switch_context_back;
5954
5955 --Start Bugfix 8305535
5956 --This is a wrapper API to raise event for every config item created.
5957 Procedure Raise_event_for_seibel
5958 IS
5959 sql_stmt varchar2(5000);
5960 i number;
5961 l_config_exists number;
5962 l_org number;
5963
5964 CURSOR config_orgs(pConfigId number) IS
5965 select organization_id
5966 from mtl_system_items_b
5967 where inventory_item_id = pConfigId;
5968
5969 BEGIN
5970 IF PG_DEBUG <> 0 THEN
5971 oe_debug_pub.add('Inside Raise_event_for_seibel',2);
5972 oe_debug_pub.add('Count of configs:: '|| CTO_MSUTIL_PUB.cfg_tbl_var.count,2);
5973 END IF;
5974
5975 for i in 1..CTO_MSUTIL_PUB.cfg_tbl_var.count loop
5976 for l_org in config_orgs(CTO_MSUTIL_PUB.cfg_tbl_var(i)) loop
5977 IF PG_DEBUG <> 0 THEN
5978 oe_debug_pub.add('Config:: ' || CTO_MSUTIL_PUB.cfg_tbl_var(i),2);
5979 oe_debug_pub.add('Org:: ' || l_org.organization_id,2);
5980 END IF;
5981
5982 sql_stmt := 'BEGIN '||
5983 'INV_ITEM_EVENTS_PVT.Raise_Events( '||
5984 'p_inventory_item_id => :p_inventory_item_id, '||
5985 'p_organization_id => :p_organization_id, '||
5986 'p_event_name => ''EGO_WF_WRAPPER_PVT.G_ITEM_CREATE_EVENT'', '||
5987 'p_dml_type => ''CREATE''); '||
5988 'END;';
5989
5990
5991 EXECUTE IMMEDIATE sql_stmt
5992 using IN CTO_MSUTIL_PUB.cfg_tbl_var(i),
5993 IN l_org.organization_id;
5994 end loop; --loop for l_org
5995 end loop;
5996
5997 EXCEPTION
5998 When others then
5999 IF PG_DEBUG <> 0 THEN
6000 oe_debug_pub.add(G_PKG_NAME || ' Error: '||sqlerrm,5);
6001 oe_debug_pub.add('Not an error');
6002 END IF;
6003
6004 End Raise_event_for_seibel;
6005 --End Bugfix 8305535
6006
6007 END CTO_MSUTIL_PUB ;