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