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