[Home] [Help]
PACKAGE BODY: APPS.CSTPSCEX
Source
1 package body CSTPSCEX as
2 /* $Header: CSTSCEXB.pls 120.19 2011/04/21 12:01:31 pbasrani ship $ */
3
4
5 -- This is the low level code for the bottom most component in an explosion
6 LOWEST_LEVEL_CODE CONSTANT NUMBER(15) := 0;
7
8
9
10 procedure insert_assembly_items (
11 i_rollup_id in number,
12 i_user_id in number,
13 i_login_id in number,
14 i_request_id in number,
15 i_prog_id in number,
16 i_prog_appl_id in number,
17 o_error_code out NOCOPY number,
18 o_error_msg out NOCOPY varchar2
19 )
20 is
21 l_stmt_num NUMBER(15);
22 begin
23
24
25 /* OPM INVCONV umoogala 17-oct-2004
26 ** Delete process org/item combinations, if any
27 */
28 l_stmt_num := 5;
29
30 delete cst_sc_lists csl
31 where exists (select 'process org'
32 from mtl_parameters mp
33 where mp.organization_id = csl.organization_id
34 and NVL(mp.process_enabled_flag, 'N') = 'Y')
35 ;
36 /* End OPM INVCONV change */
37
38 l_stmt_num := 10;
39
40 insert into cst_sc_bom_explosion
41 (
42 ROLLUP_ID,
43 ASSEMBLY_ITEM_ID,
44 ASSEMBLY_ORGANIZATION_ID,
45 COMPONENT_SEQUENCE_ID,
46 COMPONENT_ITEM_ID,
47 COMPONENT_ORGANIZATION_ID,
48 COMPONENT_QUANTITY,
49 DELETED_FLAG,
50 EXPLODED_FLAG,
51 PLAN_LEVEL,
52 LAST_UPDATE_DATE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_LOGIN,
55 CREATION_DATE,
56 CREATED_BY,
57 REQUEST_ID,
58 PROGRAM_APPLICATION_ID,
59 PROGRAM_ID,
60 PROGRAM_UPDATE_DATE
61 )
62 select
63 i_rollup_id, -- ROLLUP_ID
64 -1, -- ASSEMBLY_ITEM_ID
65 -1, -- ASSEMBLY_ORGANIZATION_ID
66 null, -- COMPONENT_SEQUENCE_ID
67 CSL.inventory_item_id, -- COMPONENT_ITEM_ID
68 CSL.organization_id, -- COMPONENT_ORGANIZATION_ID
69 1, -- COMPONENT_QUANTITY
70 'N', -- DELETED_FLAG
71 'N', -- EXPLODED_FLAG
72 1, -- PLAN_LEVEL
73 sysdate, -- LAST_UPDATE_DATE
74 i_user_id, -- LAST_UPDATED_BY
75 i_login_id, -- LAST_UPDATE_LOGIN
76 sysdate, -- CREATION_DATE
77 i_user_id, -- CREATED_BY
78 i_request_id, -- REQUEST_ID
79 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
80 i_prog_id, -- PROGRAM_ID
81 sysdate -- PROGRAM_UPDATE_DATE
82 from
83 cst_sc_lists CSL
84 where
85 CSL.rollup_id = i_rollup_id;
86
87
88 exception
89 when OTHERS then
90 o_error_code := SQLCODE;
91 o_error_msg := 'CSTPSCEX.insert_assembly_items():' ||
92 to_char(l_stmt_num) || ':' ||
93 substrb(SQLERRM, 1, 1000);
94
95 end insert_assembly_items;
96
97
98
99 procedure snapshot_sc_sourcing_rules (
100 i_rollup_id in number,
101 i_assignment_set_id in number,
102 i_inventory_item_id in number,
103 i_organization_id in number,
104 i_effective_date in date,
105 i_user_id in number,
106 i_login_id in number,
107 i_request_id in number,
108 i_prog_id in number,
109 i_prog_appl_id in number,
110 o_error_code out NOCOPY number,
111 o_error_msg out NOCOPY varchar2
112 ) is
113
114 l_stmt_num number(15);
115 l_sourcing_rules_count number(15);
116
117 l_min_rank number(15);
118
119 /* OPM INVCONV umoogala 17-oct-2004 */
120 l_sourcing_rule_name mrp_sourcing_rules.sourcing_rule_name%TYPE;
121 l_organization_code mtl_parameters.organization_code%TYPE;
122
123 begin
124 o_error_code := 0;
125 o_error_msg := null;
126
127
128 if i_assignment_set_id is null then
129 return;
130 end if;
131
132 -- SCAPI: use minimum sourcing rule rank
133 l_stmt_num := 15;
134
135 select min(MSV.rank)
136 into l_min_rank
137 from mrp_sources_v MSV
138 where
139 MSV.assignment_set_id = i_assignment_set_id and
140 MSV.inventory_item_id = i_inventory_item_id and
141 MSV.organization_id = i_organization_id and
142 MSV.allocation_percent is not null and
143 MSV.source_type is not null and
144 MSV.effective_date <= i_effective_date and
145 nvl( MSV.disable_date, i_effective_date + 1 ) > i_effective_date;
146
147 --
148 -- stmt_num 20
149 -- Take snapshot from MRP_SOURCES_V, all rows except for
150 -- same org rows. Those will be inserted in the next
151 -- SQL statement using the percentage left of the 100%.
152 -- Note:
153 -- source_type codes:
154 -- After this function executes, the possible values
155 -- for source_type are:
156 -- 1: Transfer From. It is guarenteed that
157 -- source_organization_id <> organization_id.
158 -- 2: Make At. It is guarenteed that
159 -- source_organization_id = organization_id.
160 -- 3: Buy From. It is gurenteed that
161 -- source_organization_id is null and
162 -- vendor_id is not null.
163 l_stmt_num := 20;
164
165 insert into CST_SC_SOURCING_RULES
166 (
167 ROLLUP_ID,
168 ASSIGNMENT_SET_ID,
169 INVENTORY_ITEM_ID,
170 ORGANIZATION_ID,
171 SOURCE_ORGANIZATION_ID,
172 VENDOR_ID,
173 VENDOR_SITE_ID,
174 SOURCE_TYPE,
175 SHIP_METHOD,
176 ALLOCATION_PERCENT,
177 MARKUP_CODE,
178 MARKUP,
179 ITEM_COST,
180 LAST_UPDATE_DATE,
181 LAST_UPDATED_BY,
182 LAST_UPDATE_LOGIN,
183 CREATION_DATE,
184 CREATED_BY,
185 REQUEST_ID,
186 PROGRAM_APPLICATION_ID,
187 PROGRAM_ID,
188 PROGRAM_UPDATE_DATE,
189 SOURCING_RULE_NAME
190 )
191 select
192 i_rollup_id, -- ROLLUP_ID
193 MSV.assignment_set_id, -- ASSIGNMENT_SET_ID
194 MSV.inventory_item_id, -- INVENTORY_ITEM_ID
195 MSV.organization_id, -- ORGANIZATION_ID
196 MSV.source_organization_id, -- SOURCE_ORGANIZATION_ID
197 MSV.vendor_id, -- VENDOR_ID
198 MSV.vendor_site_id, -- VENDOR_SITE_ID
199 MSV.source_type, -- SOURCE_TYPE
200 MSV.ship_method, -- SHIP_METHOD
201 MSV.allocation_percent, -- ALLOCATION_PERCENT
202 null, -- MARKUP_CODE
203 null, -- MARKUP
204 null, -- ITEM_COST
205 sysdate, -- LAST_UPDATE_DATE
206 i_user_id, -- LAST_UPDATED_BY
207 i_login_id, -- LAST_UPDATE_LOGIN
208 sysdate, -- CREATION_DATE
209 i_user_id, -- CREATED_BY
210 i_request_id, -- REQUEST_ID
211 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
212 i_prog_id, -- PROGRAM_ID
213 sysdate, -- PROGRAM_UPDATE_DATE
214 msv.sourcing_rule_name
215 from
216 mrp_sources_v MSV
217 where
218 MSV.assignment_set_id = i_assignment_set_id and
219 MSV.inventory_item_id = i_inventory_item_id and
220 MSV.organization_id = i_organization_id and
221 MSV.rank = l_min_rank and -- SCAPI: use minimum rank
222 MSV.allocation_percent is not null and
223 MSV.source_type is not null and
224 MSV.effective_date <= i_effective_date and
225 nvl( MSV.disable_date, i_effective_date + 1 ) > i_effective_date
226 and exists (select 1
227 from mtl_system_items msi
228 where msi.inventory_item_id = i_inventory_item_id
229 and msi.organization_id = nvl(MSV.source_organization_id,msi.organization_id));
230
231
232 /* OPM INVCONV umoogala 17-oct-2004
233 ** Exit the program if there are any sourcing rules which
234 ** contains process org as sourcing org.
235 */
236 BEGIN
237 l_stmt_num := 30;
238
239 select cssr.sourcing_rule_name, mp.organization_code
240 into l_sourcing_rule_name, l_organization_code
241 from cst_sc_sourcing_rules cssr, mtl_parameters mp
242 where rollup_id = i_rollup_id
243 and cssr.inventory_item_id = i_inventory_item_id
244 and cssr.organization_id = i_organization_id
245 and cssr.assignment_set_id = i_assignment_set_id
246 and mp.organization_id = cssr.source_organization_id
247 and NVL(mp.process_enabled_flag, 'N') = 'Y'
248 ;
249
250 FND_MESSAGE.set_name( 'GMF', 'GMF_SCR_PROCESS_ORG_ERROR' );
251 FND_MESSAGE.set_token( 'SOURCING_RULE_NAME', l_sourcing_rule_name );
252 FND_MESSAGE.set_token( 'PROCESS_ORG', l_organization_code );
253 o_error_code := -1;
254 o_error_msg := FND_MESSAGE.get;
255 RETURN;
256
257 EXCEPTION
258 WHEN NO_DATA_FOUND THEN
259 NULL;
260 /* when others will be handled by the main exception below */
261 END;
262 /* End INVCONV change */
263
264 exception
265 when OTHERS then
266 o_error_code := SQLCODE;
267 o_error_msg := 'CSTPSCEX.snapshot_sc_sourcing_rules():' ||
268 to_char(l_stmt_num) || ':' ||
269 substrb(SQLERRM, 1, 1000);
270 end snapshot_sc_sourcing_rules;
271
272
273
274
275
276 procedure snapshot_sc_conversion_rates (
277 i_rollup_id in number,
278 i_conversion_type in varchar2,
279 o_error_code out NOCOPY number,
280 o_error_msg out NOCOPY varchar2
281 )
282 is
283 l_stmt_num NUMBER(15);
284
285 cursor rates_cur is
286 select distinct
287 CSSR.source_organization_id,
288 SOB_FROM.currency_code from_currency,
289 CSSR.organization_id,
290 SOB_TO.currency_code to_currency
291 from
292 cst_sc_sourcing_rules CSSR,
293 hr_organization_information OOD_FROM,
294 gl_sets_of_books SOB_FROM,
295 hr_organization_information OOD_TO,
296 gl_sets_of_books SOB_TO
297 where
298 CSSR.rollup_id = i_rollup_id and
299 CSSR.source_organization_id is not null and
300 CSSR.organization_id is not null and
301 OOD_FROM.organization_id = CSSR.source_organization_id AND
302 OOD_FROM.org_information_context = 'Accounting Information' AND
303 SOB_FROM.set_of_books_id = OOD_FROM.org_information1 and
304 OOD_TO.organization_id = CSSR.organization_id AND
305 OOD_TO.org_information_context = 'Accounting Information' AND
306 SOB_TO.set_of_books_id = OOD_TO.org_information1;
307
308
309 begin
310
311
312 FOR rate IN rates_cur LOOP
313
314 BEGIN
315 l_stmt_num := 10;
316
317 update cst_sc_sourcing_rules CSSR
318 set
319 CSSR.conversion_type = i_conversion_type,
320 CSSR.conversion_rate =
321 gl_currency_api.get_rate
322 (
323 rate.from_currency,
324 rate.to_currency,
325 sysdate,
326 i_conversion_type
327 )
328 where
329 CSSR.rollup_id = i_rollup_id and
330 CSSR.organization_id = rate.organization_id and
331 CSSR.source_organization_id = rate.source_organization_id;
332
333
334 exception
335 when OTHERS then
336 FND_MESSAGE.SET_NAME( 'SQLGL', 'MRC_RATE_NOT_FOUND' );
337 FND_MESSAGE.SET_TOKEN( 'MODULE', null );
338 FND_MESSAGE.SET_TOKEN( 'FROM', rate.from_currency );
339 FND_MESSAGE.SET_TOKEN( 'TO', rate.to_currency );
340 FND_MESSAGE.SET_TOKEN( 'TRANS_DATE',
341 FND_DATE.DATE_TO_CHARDATE( sysdate ) );
342 FND_MESSAGE.SET_TOKEN( 'TYPE', i_conversion_type );
343 APP_EXCEPTION.RAISE_EXCEPTION;
344 RETURN;
345
346 END;
347 END LOOP;
348
349 exception
350 when OTHERS then
351 o_error_code := SQLCODE;
352 o_error_msg := 'CSTPSCEX.snapshot_sc_conversion_rates():' ||
353 to_char(l_stmt_num) || ':' ||
354 substrb(SQLERRM, 1, 1000);
355 end snapshot_sc_conversion_rates;
356
357
358
359
360 procedure explode_sc_bom (
361 i_rollup_id in number,
362 i_explosion_levels in number,
363 i_assignment_set_id in number,
364 i_effective_date in date,
365 i_inc_unimpl_ecn in number, -- 1 = Include Unimplemented, 2 = No
366 i_inc_eng_bill in number, -- 1 = Include Engineering Bills, 2 = No
367 i_alt_bom_desg in varchar2,
368 i_user_id in number,
369 i_login_id in number,
370 i_request_id in number,
371 i_prog_id in number,
372 i_prog_appl_id in number,
373 o_error_code out NOCOPY number,
374 o_error_msg out NOCOPY varchar2
375 )
376 is
377
378 cursor CSBE_cursor IS
379 /*Removed the N1 index hint from below query as per bug 9676587*/
380 select
381 CSBE.component_item_id,
382 CSBE.component_organization_id,
383 min( CSBE.plan_level ) prior_plan_level
384 from
385 cst_sc_bom_explosion CSBE
386 where
387 CSBE.rollup_id = i_rollup_id and
388 CSBE.exploded_flag = 'N' and
389 CSBE.plan_level <= decode( i_explosion_levels, null, CSBE.plan_level+1,
390 i_explosion_levels ) and
391 not exists
392 (
393 select /*+ INDEX (CSBE2 CST_SC_BOM_EXPLOSION_N1)*/ 'x'
394 from cst_sc_bom_explosion CSBE2
395 where
396 CSBE2.rollup_id = CSBE.rollup_id and
397 CSBE2.component_item_id = CSBE.component_item_id and
398 CSBE2.component_organization_id = CSBE.component_organization_id and
399 CSBE2.exploded_flag <> 'N'
400 )
401 group by
402 CSBE.component_item_id,
403 CSBE.component_organization_id;
404
405
406 l_rows_processed NUMBER(15);
407 l_stmt_num NUMBER(15);
408 l_active_flag NUMBER(2) ; /* Added for bug 4547027 */
409
410 begin
411
412 loop
413 l_rows_processed := 0;
414
415
416 l_stmt_num := 10;
417
418 for CSBE in CSBE_cursor loop
419
420 if i_assignment_set_id is not null then
421
422 /* Added for Bug 6124274 */
423 BEGIN
424 /* Added for bug 4547027 */
425 select decode(nvl(msi.inventory_item_status_code,'NOT'||bp.bom_delete_status_code),
426 nvl(bp.bom_delete_status_code,' '),2,1)
427 into l_active_flag
428 from mtl_system_items msi,
429 bom_parameters bp
430 where msi.inventory_item_id = CSBE.component_item_id
431 and msi.organization_id = CSBE.component_organization_id
432 and bp.organization_id (+) = msi.organization_id;
433 /*Added exception to avoid the request erroring due to incorrect sourcing rule set*/
434 EXCEPTION
435 WHEN OTHERS THEN
436 l_active_flag := 2;
437 fnd_file.put_line(FND_FILE.LOG, 'Missing Source Org/item in MSI.. Item:= ' || CSBE.component_item_id || ' Org: ' || CSBE.component_organization_id);
438 END;
439
440
441 if l_active_flag = 1 then
442
443 l_stmt_num := 20;
444 CSTPSCEX.snapshot_sc_sourcing_rules
445 (
446 i_rollup_id,
447 i_assignment_set_id,
448 CSBE.component_item_id,
449 CSBE.component_organization_id,
450 i_effective_date,
451 i_user_id,
452 i_login_id,
453 i_request_id,
454 i_prog_id,
455 i_prog_appl_id,
456 o_error_code,
457 o_error_msg
458 );
459
460 if o_error_code <> 0 then
461 return;
462 end if;
463 end if; -- l_active_flag
464 end if; -- i_assignment_set_id is not null
465
466
467 l_stmt_num := 30;
468 insert into cst_sc_bom_explosion
469 (
470 ROLLUP_ID,
471 ASSEMBLY_ITEM_ID,
472 ASSEMBLY_ORGANIZATION_ID,
473 OPERATION_SEQ_NUM,
474 COMPONENT_SEQUENCE_ID,
475 COMPONENT_ITEM_ID,
476 COMPONENT_ORGANIZATION_ID,
477 COMPONENT_QUANTITY,
478 DELETED_FLAG,
479 EXPLODED_FLAG,
480 PLAN_LEVEL,
481 LAST_UPDATE_DATE,
482 LAST_UPDATED_BY,
483 LAST_UPDATE_LOGIN,
484 CREATION_DATE,
485 CREATED_BY,
486 REQUEST_ID,
487 PROGRAM_APPLICATION_ID,
488 PROGRAM_ID,
489 PROGRAM_UPDATE_DATE
490 )
491 select
492 i_rollup_id, -- ROLLUP_ID
493 CSSR.inventory_item_id, -- ASSEMBLY_ITEM_ID
494 CSSR.organization_id, -- ASSEMBLY_ORGANIZATION_ID
495 to_number( null ), -- OPERATION_SEQ_NUM
496 to_number( null ), -- COMPONENT_SEQUENCE_ID
497 CSSR.inventory_item_id, -- COMPONENT_ITEM_ID
498 CSSR.source_organization_id, -- COMPONENT_ORGANIZATION_ID
499 CSSR.allocation_percent / 100, -- COMPONENT_QUANTITY
500 'N', -- DELETED_FLAG
501 'N', -- EXPLODED_FLAG
502 CSBE.prior_plan_level + 1, -- PLAN_LEVEL
503 sysdate, -- LAST_UPDATE_DATE
504 i_user_id, -- LAST_UPDATED_BY
505 i_login_id, -- LAST_UPDATE_LOGIN
506 sysdate, -- CREATION_DATE
507 i_user_id, -- CREATED_BY
508 i_request_id, -- REQUEST_ID
509 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
510 i_prog_id, -- PROGRAM_ID
511 sysdate -- PROGRAM_UPDATE_DATE
512 from
513 cst_sc_sourcing_rules CSSR, mtl_system_items msi /* Bug 6124274 */
514 where
515 CSSR.rollup_id = i_rollup_id and
516 msi.inventory_item_id = cssr.inventory_item_id and
517 msi.organization_id = cssr.organization_id and
518 CSSR.inventory_item_id = CSBE.component_item_id and
519 CSSR.organization_id = CSBE.component_organization_id and
520 CSSR.source_type = 1 -- Transfer items only
521
522
523 -- all we need is a UNION ALL, but I'm using UNION to
524 -- force an implicit sort so that the resulting connect by
525 -- select will (usually) be sorted by op_seq_num
526 union
527
528 select
529 i_rollup_id, -- ROLLUP_ID
530 BOM.assembly_item_id, -- ASSEMBLY_ITEM_ID
531 BOM.organization_id, -- ASSEMBLY_ORGANIZATION_ID
532 BIC.operation_seq_num, -- OPERATION_SEQ_NUM
533 BIC.component_sequence_id, -- COMPONENT_SEQUENCE_ID
534 BIC.component_item_id, -- COMPONENT_ITEM_ID
535 BOM.organization_id, -- COMPONENT_ORGANIZATION_ID
536 BIC.component_quantity, -- COMPONENT_QUANTITY
537 'N', -- DELETED_FLAG
538 'N', -- EXPLODED_FLAG
539 CSBE.prior_plan_level + 1, -- PLAN_LEVEL
540 sysdate, -- LAST_UPDATE_DATE
541 i_user_id, -- LAST_UPDATED_BY
542 i_login_id, -- LAST_UPDATE_LOGIN
543 sysdate, -- CREATION_DATE
544 i_user_id, -- CREATED_BY
545 i_request_id, -- REQUEST_ID
546 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
547 i_prog_id, -- PROGRAM_ID
548 sysdate -- PROGRAM_UPDATE_DATE
549 from
550 bom_bill_of_materials BOM,
551 bom_inventory_components BIC
552 where
553 BOM.common_bill_sequence_id = BIC.bill_sequence_id and
554 BOM.assembly_item_id = CSBE.component_item_id and
555 BOM.organization_id = CSBE.component_organization_id and
556 ----------------------------
557 --- effectivity checking
558 ----------------------------
559 BIC.effectivity_date <= i_effective_date and
560 nvl( BIC.disable_date, i_effective_date + 1 ) > i_effective_date and
561 ----------------------------
562 --- alternate bom designator
563 ----------------------------
564 BOM.assembly_type =
565 decode( i_inc_eng_bill, 1, BOM.assembly_type, 1 ) AND
566 (
567 (
568 i_alt_bom_desg IS NULL AND
569 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
570 )
571 OR
572 (
573 i_alt_bom_desg IS NOT NULL AND
574 BOM.ALTERNATE_BOM_DESIGNATOR = i_alt_bom_desg
575 )
576 OR
577 ( i_alt_bom_desg IS NOT NULL AND
578 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL AND
579 NOT EXISTS
580 (
581 SELECT 'X'
582 FROM BOM_BILL_OF_MATERIALS BOM2
583 WHERE BOM2.ORGANIZATION_ID = BOM.ORGANIZATION_ID AND
584 BOM2.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID AND
585 BOM2.ALTERNATE_BOM_DESIGNATOR = i_alt_bom_desg AND
586 BOM2.assembly_type =
587 decode( i_inc_eng_bill, 1, BOM2.assembly_type, 1 )
588 )
589 )
590 ) AND
591 ( BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
592 OR
593 BOM.ALTERNATE_BOM_DESIGNATOR = i_alt_bom_desg
594 ) AND
595 ----------------------------
596 --- implementation option
597 ----------------------------
598 (
599 (
600 i_inc_unimpl_ecn = 2 AND
601 BIC.IMPLEMENTATION_DATE IS NOT NULL
602 )
603 OR
604 (
605 i_inc_unimpl_ecn = 1 AND
606 BIC.EFFECTIVITY_DATE =
607 (
608 SELECT MAX(EFFECTIVITY_DATE)
609 FROM BOM_INVENTORY_COMPONENTS BIC2
610 WHERE
611 BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID AND
612 BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID AND
613 (
614 decode( BIC2.IMPLEMENTATION_DATE,
615 NULL, BIC2.OLD_COMPONENT_SEQUENCE_ID,
616 BIC2.COMPONENT_SEQUENCE_ID ) =
617 decode( BIC.IMPLEMENTATION_DATE,
618 NULL, BIC.OLD_COMPONENT_SEQUENCE_ID,
619 BIC.COMPONENT_SEQUENCE_ID )
620 OR
621 BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
622 )
623 AND
624 BIC2.EFFECTIVITY_DATE <= i_effective_date
625 AND
626 NVL( BIC2.eco_for_production, 2 ) = 2
627 ) -- end of subquery
628 )
629 ) AND
630 ----------------------------------------------------
631 -- This should take care of excluding model and oc
632 ----------------------------------------------------
633 BIC.INCLUDE_IN_COST_ROLLUP = 1 and
634 ----------------------------------------------------
635 -- This is for ECO changes in 11i.4
636 ----------------------------------------------------
637 NVL( BIC.eco_for_production, 2 ) = 2 and
638
639 /* Fix for BUG 1604207 */
640 NVL( bic.acd_type, 1 ) <> 3 and
641
642 ----------------------------------------------------
643 -- only insert BOM if there is a Make rule
644 ----------------------------------------------------
645 0 < (
646 select nvl( sum( decode( CSSR.source_type, 2,
647 CSSR.allocation_percent, 0 ) ), 100 )
648 from cst_sc_sourcing_rules CSSR
649 where
650 CSSR.rollup_id = i_rollup_id and
651 CSSR.inventory_item_id = CSBE.component_item_id and
652 CSSR.organization_id = CSBE.component_organization_id
653 );
654
655
656
657 l_stmt_num := 40;
658
659 update cst_sc_bom_explosion
660 set exploded_flag = 'Y'
661 where rollup_id = i_rollup_id and
662 component_item_id = CSBE.component_item_id and
663 component_organization_id = CSBE.component_organization_id;
664
665 l_rows_processed := l_rows_processed + 1;
666 end loop;
667
668 exit when l_rows_processed = 0;
669 end loop;
670
671
672
673 -- This will scale down the component_quantity of components of
674 -- assemblies that have partial Make sourcing rules.
675 update cst_sc_bom_explosion CSBE
676 set CSBE.component_quantity
677 = (
678 select CSBE.component_quantity *
679 nvl( sum( decode( CSSR.source_type, 2,
680 CSSR.allocation_percent, 0 ) ) / 100, 1 )
681 from cst_sc_sourcing_rules CSSR
682 where CSSR.rollup_id = CSBE.rollup_id and
683 CSSR.inventory_item_id = CSBE.assembly_item_id and
684 CSSR.organization_id = CSBE.assembly_organization_id
685 )
686 where CSBE.rollup_id = i_rollup_id and
687 CSBE.assembly_organization_id = component_organization_id;
688
689
690
691 -- This will clear out all exploded rows, essentially the rows
692 -- that are stuck in a loop.
693 --Removed the Index N1 hint from below query as it was incorrect(Bug:9962574)
694 update cst_sc_bom_explosion CSBE
695 set exploded_flag = 'Y'
696 where
697 rollup_id = i_rollup_id and
698 exploded_flag = 'N' and
699 exists (
700 select /*+ INDEX (CSBE2 CST_SC_BOM_EXPLOSION_N1)*/ 'x'
701 from cst_sc_bom_explosion CSBE2
702 where
703 CSBE2.rollup_id = CSBE.rollup_id and
704 CSBE2.component_item_id = CSBE.component_item_id and
705 CSBE2.component_organization_id = CSBE.component_organization_id and
706 CSBE2.exploded_flag = 'Y'
707 );
708
709
710 exception
711 when OTHERS then
712 o_error_code := SQLCODE;
713 o_error_msg := 'CSTPSCEX.explode_sc_bom():' ||
714 to_char(l_stmt_num) || ':' ||
715 substrb(SQLERRM, 1, 1000);
716
717 end explode_sc_bom;
718
719
720
721
722
723
724 procedure explode_sc_cost_flags (
725 i_rollup_id in number,
726 i_cost_type_id in number,
727 o_error_code out NOCOPY number,
728 o_error_msg out NOCOPY varchar2
729 )
730 is
731 cursor assm_cursor is
732 select
733 CSBS.rowid,
734 decode( CIC.inventory_asset_flag, 2, 2,
735 decode( CIC.based_on_rollup_flag, 2, 2, 1 ) ) new_ext_cost_flag
736 from
737 cst_sc_bom_structures CSBS,
738 cst_item_costs CIC
739 where
740 CSBS.rollup_id = i_rollup_id and
741 CSBS.assembly_item_id = -1 and
742 CIC.inventory_item_id = CSBS.component_item_id and
743 CIC.organization_id = CSBS.component_organization_id and
744 CIC.cost_type_id = i_cost_type_id;
745
746
747 /* the outer join to CIC is necessary because we're joining
748 to the assembly, and assembly_id can be -1 */
749
750
751 -- Note that this join to CSSR depends on the fact that
752 -- there can be at most one Make At sourcing rule for an item.
753 -- This is currently being enforced by the MRP forms.
754
755 -- Added planning factor for bug 2947036
756 /* Bug 4547027 Changed the cursor to get active_flag for the component */
757 cursor component_cursor is
758 select
759 CSBS.top_inventory_item_id top_inventory_item_id,
760 CSBS.top_organization_id top_organization_id,
761 CSBS.sort_order sort_order,
762 CSBS.rowid,
763 CSBS.bom_level,
764 BIC.basis_type,
765 /* LBM Project 3926918: Changes made to support Lot Based Materials. Added decode to check for basis type
766 of the component. If lot based then component qty becomes compt_qty/lot_size else it is unchanged. */
767 CSBS.component_quantity,
768 BIC.include_in_cost_rollup include_in_cost_rollup,
769 nvl(BIC.component_yield_factor, 1) component_yield_factor,
770 nvl(BIC.planning_factor/100, 1) component_planning_factor,
771 nvl(CIC.inventory_asset_flag,2) inventory_asset_flag,
772 nvl(CIC.based_on_rollup_flag,2) based_on_rollup_flag, -- added NVL for bug 4377129
773 decode(bp.use_phantom_routings, 1, decode(nvl(BIC.wip_supply_type, nvl( MSI.wip_supply_type, 1)), 6, 1, 2), 2) phantom_flag,
774 decode(nvl(msi.inventory_item_status_code,'NOT'||bp.bom_delete_status_code),nvl(bp.bom_delete_status_code,' '), 2, 1) active_flag,
775 0 shrinkage_rate,
776 decode(CIC.lot_size, 0, 1, NULL, 1, CIC.lot_size) lot_size
777 from
778 cst_sc_bom_structures CSBS,
779 cst_item_costs CIC,
780 mtl_system_items MSI,
781 bom_inventory_components BIC,
782 bom_parameters bp /* Bug 4547027 */
783 where
784 CSBS.rollup_id = i_rollup_id and
785 CSBS.assembly_item_id = -1 and
786 CIC.inventory_item_id (+) = CSBS.top_inventory_item_id and
787 CIC.organization_id (+) = CSBS.top_organization_id and
788 CIC.cost_type_id (+) = i_cost_type_id and
789 MSI.inventory_item_id = CSBS.component_item_id and
790 MSI.organization_id = CSBS.component_organization_id and
791 bp.organization_id (+) = CSBS.component_organization_id and /* Bug 4547027 */
792 BIC.component_sequence_id (+) = CSBS.component_sequence_id
793 UNION ALL
794 select
795 CSBS.top_inventory_item_id top_inventory_item_id,
796 CSBS.top_organization_id top_organization_id,
797 CSBS.sort_order sort_order,
798 CSBS.rowid,
799 CSBS.bom_level,
800 BIC.basis_type,
801 /* LBM Project 3926918: Changes made to support Lot Based Materials. Added decode to check for basis type
802 of the component. If lot based then component qty becomes compt_qty/lot_size else it is unchanged. */
803 CSBS.component_quantity,
804 BIC.include_in_cost_rollup include_in_cost_rollup,
805 nvl(BIC.component_yield_factor, 1) component_yield_factor,
806 nvl(BIC.planning_factor/100, 1) component_planning_factor,
807 nvl(CIC.inventory_asset_flag,2) inventory_asset_flag,
808 nvl(CIC.based_on_rollup_flag,2) based_on_rollup_flag, -- added NVL for bug 4377129
809 decode(bp.use_phantom_routings, 1, decode(nvl(BIC.wip_supply_type, nvl( MSI.wip_supply_type, 1)), 6, 1, 2), 2) phantom_flag,
810 decode(nvl(msi.inventory_item_status_code,'NOT'||bp.bom_delete_status_code),nvl(bp.bom_delete_status_code,' '), 2, 1) active_flag,
811 decode(CSBS.assembly_organization_id, CSBS.component_organization_id, nvl(CIC.shrinkage_rate, 0), 0) shrinkage_rate,
812 decode(CIC.lot_size, 0, 1, NULL, 1, CIC.lot_size) lot_size
813 from
814 cst_sc_bom_structures CSBS,
815 cst_item_costs CIC,
816 mtl_system_items MSI,
817 bom_inventory_components BIC,
818 bom_parameters bp /* Bug 4547027 */
819 where
820 CSBS.rollup_id = i_rollup_id and
821 CSBS.assembly_item_id <> -1 and
822 CIC.inventory_item_id (+) = CSBS.assembly_item_id and
823 CIC.organization_id (+) = CSBS.assembly_organization_id and
824 CIC.cost_type_id (+) = i_cost_type_id and
825 MSI.inventory_item_id = CSBS.component_item_id and
826 MSI.organization_id = CSBS.component_organization_id and
827 bp.organization_id (+) = CSBS.component_organization_id and /* Bug 4547027 */
828 BIC.component_sequence_id (+) = CSBS.component_sequence_id
829 order by
830 top_inventory_item_id,
831 top_organization_id,
832 sort_order;
833
834 TYPE STACK_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
835 ext_qty_stack STACK_TYPE;
836
837 ext_cost_flag_stack STACK_TYPE;
838
839 phtm_factor_stack STACK_TYPE;
840
841 l_stmt_num number(15);
842
843 l_comp_yield_flag number(15);
844
845 begin
846
847 -- top level extended_quantity is always 1
848 ext_qty_stack(0) := 1;
849
850 ext_cost_flag_stack(0) := 1;
851
852 phtm_factor_stack(0) := 1;
853
854 /* Get component_yield_fla: Bug 2297027 */
855
856 select component_yield_flag
857 into l_comp_yield_flag
858 from cst_cost_types
859 where cost_type_id = i_cost_type_id;
860
861 -- set up the top level extend_cost_flag
862 l_stmt_num := 10;
863
864 FOR assm in assm_cursor LOOP
865 update cst_sc_bom_structures CSBS
866 set CSBS.extend_cost_flag = assm.new_ext_cost_flag
867 where CSBS.rowid = assm.rowid;
868 END LOOP;
869
870 FOR comp in component_cursor LOOP
871
872 l_stmt_num := 15;
873 /* Bug 4547027 Added extra check so that the cost of the components
874 of inactive assemblies is not shown in the report */
875 IF ext_cost_flag_stack(comp.bom_level - 1) = 2 OR
876 comp.inventory_asset_flag = 2 OR comp.based_on_rollup_flag = 2 OR
877 comp.active_flag = 2 OR nvl(comp.include_in_cost_rollup, 1) = 2 THEN
878 ext_cost_flag_stack(comp.bom_level) := 2;
879 ELSE
880 ext_cost_flag_stack(comp.bom_level) := 1;
881 END IF;
882
883 /* Only active components are considered */
884 IF ext_cost_flag_stack(comp.bom_level) = 1 THEN
885
886 /* Added for bug#7418952 to include shrinakge rate from the previous levels into consideration */
887 l_stmt_num := 20;
888 ext_qty_stack(comp.bom_level) := ext_qty_stack(comp.bom_level - 1) *
889 comp.component_quantity * comp.component_planning_factor / (1-comp.shrinkage_rate);
890
891 l_stmt_num := 25;
892 /* Consider component_yield_factor and planning_factor in Extended Quantity
893 Bug 2297027 and Bug 2947036 */
894 IF l_comp_yield_flag = 1 THEN
895 ext_qty_stack(comp.bom_level) := ext_qty_stack(comp.bom_level) / comp.component_yield_factor;
896 END IF;
897
898 l_stmt_num := 30;
899 /* Added this stmt to set the proper extended quantity in case if components are lot based LBM Project. In this case we
900 have to consider the lot size of the assembly, while calculating the extended quantity of the component */
901 IF comp.basis_type = 2 THEN
902 ext_qty_stack(comp.bom_level) := ext_qty_stack(comp.bom_level) / comp.lot_size;
903 END IF;
904
905 l_stmt_num := 35;
906 /* If a sub-assembly is phantom it cannot be a Lot Based Material from Bills Of Materials Forms
907 A phantom will not include the component lot size and component quantity and only considers
908 assembly lot size for calculation. The assembly cost will be calculated same, irrespective of
909 whether it is a phantom or not. The difference happens in the way the component phantom costs
910 goes into assembly.
911 The new phantom factor column is used to display phantom material correctly */
912 l_stmt_num := 40;
913 IF comp.phantom_flag = 1 THEN
914 select cic.lot_size / (comp.lot_size * comp.component_quantity)
915 into phtm_factor_stack(comp.bom_level)
916 from cst_item_costs cic,
917 cst_sc_bom_structures csbs
918 where csbs.rowid = comp.rowid
919 and CIC.inventory_item_id (+) = CSBS.component_item_id
920 and CIC.organization_id (+) = CSBS.component_organization_id
921 and CIC.cost_type_id (+) = i_cost_type_id;
922 ELSE
923 phtm_factor_stack(comp.bom_level) := 1;
924 END IF;
925
926 ELSE -- ext_cost_flag_stack(comp.bom_level) = 2
927 ext_qty_stack(comp.bom_level) := 1;
928 phtm_factor_stack(comp.bom_level) := 1;
929 END IF;
930
931 l_stmt_num := 50;
932
933 update cst_sc_bom_structures CSBS
934 set
935 CSBS.component_quantity = comp.component_quantity,
936 CSBS.extended_quantity = ext_qty_stack(comp.bom_level),
937 CSBS.include_in_cost_rollup = comp.include_in_cost_rollup,
938 CSBS.extend_cost_flag = ext_cost_flag_stack(comp.bom_level),
939 CSBS.phantom_flag = comp.phantom_flag,
940 CSBS.phantom_factor = phtm_factor_stack(comp.bom_level)
941 where CSBS.rowid = comp.rowid;
942
943 END LOOP;
944
945 EXCEPTION
946 WHEN OTHERS THEN
947 o_error_code := SQLCODE;
948 o_error_msg := 'CSTPSCEX.explode_sc_cost_flags():' ||
949 to_char(l_stmt_num) || ':' ||
950 substrb(SQLERRM, 1, 1000);
951
952 end explode_sc_cost_flags;
953
954
955
956
957
958 procedure snapshot_sc_bom_structures (
959 i_rollup_id in number,
960 i_cost_type_id in number,
961 i_report_levels in number,
962 i_effective_date in date,
963 i_user_id in number,
964 i_login_id in number,
965 i_request_id in number,
966 i_prog_id in number,
967 i_prog_appl_id in number,
968 o_error_code out NOCOPY number,
969 o_error_msg out NOCOPY varchar2,
970 i_report_type_type in number
971 )
972 is
973 cursor top_assembly_cursor is
974 select
975 CSBE.component_item_id,
976 CSBE.component_organization_id
977 from
978 cst_sc_bom_explosion CSBE
979 where
980 CSBE.rollup_id = i_rollup_id and
981 CSBE.assembly_item_id = -1 and
982 CSBE.deleted_flag = 'Y'; -- Bug 3665428: make snapshot only for valid items without loop
983
984
985
986
987 l_err_code NUMBER(15);
988 l_err_mesg VARCHAR2(100);
989
990 l_bom_level number(15);
991
992 l_stmt_num number(15);
993
994 begin
995
996 IF i_report_levels IS NULL THEN
997 return;
998 END IF;
999
1000
1001 -- SCAPI: delete previous data;
1002 delete cst_sc_bom_structures
1003 where rollup_id in (i_rollup_id, -1*i_rollup_id);
1004
1005
1006 l_stmt_num := 10;
1007 FOR top_assm in top_assembly_cursor LOOP
1008 BEGIN
1009
1010 l_stmt_num := 20;
1011
1012 insert into cst_sc_bom_structures
1013 (
1014 ROLLUP_ID,
1015 TOP_INVENTORY_ITEM_ID,
1016 TOP_ORGANIZATION_ID,
1017 SORT_ORDER,
1018 BOM_LEVEL,
1019 ASSEMBLY_ITEM_ID,
1020 ASSEMBLY_ORGANIZATION_ID,
1021 COMPONENT_SEQUENCE_ID,
1022 COMPONENT_ITEM_ID,
1023 COMPONENT_ORGANIZATION_ID,
1024 COMPONENT_QUANTITY,
1025 EXTENDED_QUANTITY,
1026 INCLUDE_IN_COST_ROLLUP,
1027 EXTEND_COST_FLAG,
1028 PHANTOM_FLAG,
1029 PHANTOM_FACTOR, -- Added for bug 11844126
1030 LAST_UPDATE_DATE,
1031 LAST_UPDATED_BY,
1032 LAST_UPDATE_LOGIN,
1033 CREATION_DATE,
1034 CREATED_BY,
1035 REQUEST_ID,
1036 PROGRAM_APPLICATION_ID,
1037 PROGRAM_ID,
1038 PROGRAM_UPDATE_DATE
1039 )
1040 select
1041 i_rollup_id, -- ROLLUP_ID
1042 top_assm.component_item_id, -- TOP_INVENTORY_ITEM_ID
1043 top_assm.component_organization_id, -- TOP_ORGANIZATION_ID
1044 rownum, -- SORT_ORDER
1045 level, -- BOM_LEVEL
1046 CSBE.assembly_item_id, -- ASSEMBLY_ITEM_ID
1047 CSBE.assembly_organization_id, -- ASSEMBLY_ORGANIZATION_ID
1048 CSBE.component_sequence_id, -- COMPONENT_SEQUENCE_ID
1049 CSBE.component_item_id, -- COMPONENT_ITEM_ID
1050 CSBE.component_organization_id, -- COMPONENT_ORGANIZATION_ID
1051 CSBE.component_quantity, -- COMPONENT_QUANTITY
1052 1, -- EXTENDED_QUANTITY
1053 1, -- INCLUDE_IN_COST_ROLLUP
1054 1, -- EXTEND_COST_FLAG
1055 2, -- PHANTOM_FLAG
1056 1, -- PHANTOM_FACTOR
1057 sysdate, -- LAST_UPDATE_DATE
1058 i_user_id, -- LAST_UPDATED_BY
1059 i_login_id, -- LAST_UPDATE_LOGIN
1060 sysdate, -- CREATION_DATE
1061 i_user_id, -- CREATED_BY
1062 i_request_id, -- REQUEST_ID
1063 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1064 i_prog_id, -- PROGRAM_ID
1065 sysdate -- PROGRAM_UPDATE_DATE
1066 from
1067 cst_sc_bom_explosion CSBE
1068 start with
1069 rollup_id = i_rollup_id and
1070 assembly_item_id = -1 and
1071 component_item_id = top_assm.component_item_id and
1072 component_organization_id = top_assm.component_organization_id
1073 connect by
1074 prior rollup_id = rollup_id and
1075 prior component_item_id = assembly_item_id and
1076 prior component_organization_id = assembly_organization_id and
1077 level <= i_report_levels;
1078
1079
1080 EXCEPTION
1081 WHEN OTHERS THEN
1082
1083 l_err_code := SQLCODE;
1084 l_err_mesg := substrb( SQLERRM, 1, 100 );
1085
1086 insert into cst_sc_bom_structures
1087 (
1088 ROLLUP_ID,
1089 TOP_INVENTORY_ITEM_ID,
1090 TOP_ORGANIZATION_ID,
1091 SORT_ORDER,
1092 BOM_LEVEL,
1093 ASSEMBLY_ITEM_ID,
1094 ASSEMBLY_ORGANIZATION_ID,
1095 COMPONENT_SEQUENCE_ID,
1096 COMPONENT_ITEM_ID,
1097 COMPONENT_ORGANIZATION_ID,
1098 COMPONENT_QUANTITY,
1099 EXTENDED_QUANTITY,
1100 INCLUDE_IN_COST_ROLLUP,
1101 EXTEND_COST_FLAG,
1102 PHANTOM_FLAG,
1103 PHANTOM_FACTOR, -- Added for bug 11844126
1104 ERROR_CODE,
1105 ERROR_MESG,
1106 LAST_UPDATE_DATE,
1107 LAST_UPDATED_BY,
1108 LAST_UPDATE_LOGIN,
1109 CREATION_DATE,
1110 CREATED_BY,
1111 REQUEST_ID,
1112 PROGRAM_APPLICATION_ID,
1113 PROGRAM_ID,
1114 PROGRAM_UPDATE_DATE
1115 )
1116 values
1117 (
1118 i_rollup_id, -- ROLLUP_ID
1119 top_assm.component_item_id, -- TOP_INVENTORY_ITEM_ID
1120 top_assm.component_organization_id, -- TOP_ORGANIZATION_ID
1121 0, -- SORT_ORDER
1122 0, -- BOM_LEVEL
1123 -1, -- ASSEMBLY_ITEM_ID
1124 -1, -- ASSEMBLY_ORGANIZATION_ID
1125 null, -- COMPONENT_SEQUENCE_ID
1126 top_assm.component_item_id, -- COMPONENT_ITEM_ID
1127 top_assm.component_organization_id, -- COMPONENT_ORGANIZATION_ID
1128 0, -- COMPONENT_QUANTITY
1129 0, -- EXTENDED_QUANTITY
1130 2, -- INCLUDE_IN_COST_ROLLUP
1131 2, -- EXTEND_COST_FLAG
1132 2, -- PHANTOM_FLAG
1133 1, -- PHANTOM_FACTOR
1134 l_err_code, -- ERROR_CODE
1135 l_err_mesg, -- ERROR_MESG
1136 sysdate, -- LAST_UPDATE_DATE
1137 i_user_id, -- LAST_UPDATED_BY
1138 i_login_id, -- LAST_UPDATE_LOGIN
1139 sysdate, -- CREATION_DATE
1140 i_user_id, -- CREATED_BY
1141 i_request_id, -- REQUEST_ID
1142 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1143 i_prog_id, -- PROGRAM_ID
1144 sysdate -- PROGRAM_UPDATE_DATE
1145 );
1146
1147 END;
1148 END LOOP;
1149
1150
1151
1152 l_stmt_num := 30;
1153
1154 -- update the item revision column
1155 update cst_sc_bom_structures CSBS
1156 set CSBS.component_revision =
1157 (
1158 select
1159 substr( max( to_char( MIR.effectivity_date, 'YYYY/MM/DD HH24:MI:SS' ) ||
1160 MIR.revision ), 20 )
1161 from
1162 mtl_item_revisions MIR
1163 where
1164 MIR.inventory_item_id = CSBS.component_item_id and
1165 MIR.organization_id = CSBS.component_organization_id and
1166 MIR.effectivity_date <= i_effective_date
1167 )
1168 where CSBS.rollup_id = i_rollup_id;
1169
1170
1171
1172 l_stmt_num := 40;
1173 IF i_cost_type_id is not null THEN
1174 explode_sc_cost_flags
1175 (
1176 i_rollup_id,
1177 i_cost_type_id,
1178 o_error_code,
1179 o_error_msg
1180 );
1181
1182 IF o_error_code <> 0 THEN
1183 RETURN;
1184 END IF;
1185 END IF;
1186
1187 -- SCAPI: insert data for consolidated report using negative rollup_id
1188 l_stmt_num := 50;
1189 IF i_report_type_type = 2 THEN
1190 insert into cst_sc_bom_structures
1191 (
1192 ROLLUP_ID,
1193 TOP_INVENTORY_ITEM_ID,
1194 TOP_ORGANIZATION_ID,
1195 SORT_ORDER,
1196 BOM_LEVEL,
1197 ASSEMBLY_ITEM_ID,
1198 ASSEMBLY_ORGANIZATION_ID,
1199 COMPONENT_SEQUENCE_ID,
1200 COMPONENT_ITEM_ID,
1201 COMPONENT_ORGANIZATION_ID,
1202 COMPONENT_QUANTITY,
1203 EXTENDED_QUANTITY,
1204 INCLUDE_IN_COST_ROLLUP,
1205 EXTEND_COST_FLAG,
1206 PHANTOM_FLAG,
1207 PHANTOM_FACTOR, -- Added for bug 11844126
1208 COMPONENT_REVISION,
1209 LAST_UPDATE_DATE,
1210 LAST_UPDATED_BY,
1211 LAST_UPDATE_LOGIN,
1212 CREATION_DATE,
1213 CREATED_BY,
1214 REQUEST_ID,
1215 PROGRAM_APPLICATION_ID,
1216 PROGRAM_ID,
1217 PROGRAM_UPDATE_DATE
1218 )
1219 select
1220 -1*i_rollup_id, -- ROLLUP_ID
1221 CSBS.top_inventory_item_id, -- TOP_INVENTORY_ITEM_ID
1222 CSBS.top_organization_id, -- TOP_ORGANIZATION_ID
1223 max(CSBS.sort_order), -- SORT_ORDER
1224 max(CSBS.bom_level), -- BOM_LEVEL
1225 max(CSBS.assembly_item_id), -- ASSEMBLY_ITEM_ID
1226 CSBS.assembly_organization_id, -- ASSEMBLY_ORGANIZATION_ID
1227 null, -- COMPONENT_SEQUENCE_ID
1228 CSBS.component_item_id, -- COMPONENT_ITEM_ID
1229 CSBS.component_organization_id, -- COMPONENT_ORGANIZATION_ID
1230 sum(CSBS.component_quantity), -- COMPONENT_QUANTITY
1231 sum(CSBS.extended_quantity), -- EXTENDED_QUANTITY
1232 null, -- INCLUDE_IN_COST_ROLLUP
1233 CSBS.extend_cost_flag, -- EXTEND_COST_FLAG
1234 CSBS.phantom_flag, -- PHANTOM_FLAG
1235 CSBS.phantom_factor, -- PHANTOM_FACTOR
1236 CSBS.component_revision, -- COMPONENT_REVISION
1237 sysdate, -- LAST_UPDATE_DATE
1238 i_user_id, -- LAST_UPDATED_BY
1239 i_login_id, -- LAST_UPDATE_LOGIN
1240 sysdate, -- CREATION_DATE
1241 i_user_id, -- CREATED_BY
1242 i_request_id, -- REQUEST_ID
1243 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1244 i_prog_id, -- PROGRAM_ID
1245 sysdate -- PROGRAM_UPDATE_DATE
1246 from
1247 cst_sc_bom_structures CSBS
1248 where
1249 rollup_id = i_rollup_id
1250 group by
1251 CSBS.top_inventory_item_id,
1252 CSBS.top_organization_id,
1253 CSBS.assembly_organization_id,
1254 CSBS.component_item_id,
1255 CSBS.component_organization_id,
1256 CSBS.extend_cost_flag,
1257 CSBS.phantom_flag,
1258 CSBS.PHANTOM_FACTOR,
1259 CSBS.component_revision;
1260 END IF;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 o_error_code := SQLCODE;
1265 o_error_msg := 'CSTPSCEX.snapshot_sc_bom_structures():' ||
1266 to_char(l_stmt_num) || ':' ||
1267 substrb(SQLERRM, 1, 1000);
1268
1269 end snapshot_sc_bom_structures;
1270
1271 PROCEDURE check_loop (i_rollup_id in number,
1272 o_error_code out NOCOPY number,
1273 o_error_msg out NOCOPY varchar2)
1274 IS
1275 cursor loop_cursor is
1276 select plan_level-1 plan_level,
1277 ASSM.concatenated_segments assembly_item,
1278 MP1.organization_code assembly_organization,
1279 COMP.concatenated_segments component_item,
1280 MP2.organization_code component_organization
1281 from cst_sc_bom_explosion CSBE,
1282 mtl_system_items_kfv ASSM,
1283 mtl_parameters MP1,
1284 mtl_system_items_kfv COMP,
1285 mtl_parameters MP2
1286 where CSBE.rollup_id = i_rollup_id
1287 and CSBE.deleted_flag = 'N'
1288 and ASSM.inventory_item_id = CSBE.assembly_item_id
1289 and ASSM.organization_id = CSBE.assembly_organization_id
1290 and MP1.organization_id = CSBE.assembly_organization_id
1291 and COMP.inventory_item_id = CSBE.component_item_id
1292 and COMP.organization_id = CSBE.component_organization_id
1293 and MP2.organization_id = CSBE.component_organization_id
1294 order by CSBE.plan_level;
1295
1296 l_stmt_num number;
1297 l_loop_flag boolean := FALSE;
1298
1299 BEGIN
1300
1301 l_stmt_num := 10;
1302
1303 for rec in loop_cursor loop
1304 l_stmt_num := 20;
1305 l_loop_flag := TRUE;
1306 fnd_file.put_line(fnd_file.log, LPAD(rec.plan_level, 3)||' : '||
1307 rec.assembly_item||'[Org:'||rec.assembly_organization||']'||' ==> '||
1308 rec.component_item||'[Org:'||rec.component_organization||']');
1309 end loop;
1310
1311 l_stmt_num := 30;
1312 if l_loop_flag then
1313 fnd_file.put_line(fnd_file.log, 'Warning: Please check for Loop in the BOM structure above.');
1314 end if;
1315
1316 EXCEPTION
1317
1318 when OTHERS then
1319 o_error_code := SQLCODE;
1320 o_error_msg := 'CSTPSCEX.check_loop():' ||
1321 to_char(l_stmt_num) || ':' ||
1322 substrb(SQLERRM, 1, 1000);
1323 END check_loop;
1324
1325 procedure compute_sc_low_level_codes (
1326 i_rollup_id in number,
1327 i_explosion_levels in number,
1328 i_cost_type_id in number,
1329 i_user_id in number,
1330 i_login_id in number,
1331 i_request_id in number,
1332 i_prog_id in number,
1333 i_prog_appl_id in number,
1334 o_error_code out NOCOPY number,
1335 o_error_msg out NOCOPY varchar2,
1336 i_report_option_type in number -- SCAPI: for supply chain cost reports
1337 )
1338 is
1339 l_low_level_code NUMBER(15);
1340 l_frozen_standard_flag number(15);
1341
1342 l_stmt_num number(15);
1343 begin
1344
1345 l_low_level_code := LOWEST_LEVEL_CODE;
1346
1347 /* Supply chain enhancement: if not a full rollup, only assign low level codes
1348 for items that exist in cst_sc_lists */
1349
1350 IF i_explosion_levels is not null THEN
1351
1352 l_stmt_num := 5;
1353
1354 update cst_sc_bom_explosion CSBE
1355 set deleted_flag = 'Y'
1356 where
1357 CSBE.rollup_id = i_rollup_id and
1358 CSBE.deleted_flag = 'N' and
1359 not exists ( select 'Item in List'
1360 from cst_sc_lists CSL
1361 where CSL.rollup_id = i_rollup_id
1362 and CSL.inventory_item_id = CSBE.component_item_id
1363 and CSL.organization_id = CSBE.component_organization_id );
1364
1365 END IF;
1366
1367 LOOP
1368
1369 l_stmt_num := 10;
1370
1371 insert into cst_sc_low_level_codes
1372 (
1373 ROLLUP_ID,
1374 INVENTORY_ITEM_ID,
1375 ORGANIZATION_ID,
1376 LOW_LEVEL_CODE,
1377 LAST_UPDATE_DATE,
1378 LAST_UPDATED_BY,
1379 LAST_UPDATE_LOGIN,
1380 CREATION_DATE,
1381 CREATED_BY,
1382 REQUEST_ID,
1383 PROGRAM_APPLICATION_ID,
1384 PROGRAM_ID,
1385 PROGRAM_UPDATE_DATE
1386 )
1387 select distinct
1388 i_rollup_id, -- ROLLUP_ID
1389 CSBE.component_item_id, -- INVENTORY_ITEM_ID
1390 CSBE.component_organization_id, -- ORGANIZATION_ID
1391 l_low_level_code, -- LOW_LEVEL_CODE
1392 sysdate, -- LAST_UPDATE_DATE
1393 i_user_id, -- LAST_UPDATED_BY
1394 i_login_id, -- LAST_UPDATE_LOGIN
1395 sysdate, -- CREATION_DATE
1396 i_user_id, -- CREATED_BY
1397 i_request_id, -- REQUEST_ID
1398 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1399 i_prog_id, -- PROGRAM_ID
1400 sysdate -- PROGRAM_UPDATE_DATE
1401 from
1402 cst_sc_bom_explosion CSBE
1403 where
1404 CSBE.rollup_id = i_rollup_id and
1405 CSBE.deleted_flag = 'N' and
1406 not exists
1407 (
1408 select 'x'
1409 from cst_sc_bom_explosion CSBE2
1410 where
1411 CSBE2.rollup_id = CSBE.rollup_id and
1412 CSBE2.assembly_item_id = CSBE.component_item_id and
1413 CSBE2.assembly_organization_id = CSBE.component_organization_id and
1414 CSBE2.deleted_flag = 'N'
1415 );
1416
1417 l_stmt_num := 20;
1418
1419 update cst_sc_bom_explosion CSBE
1420 set deleted_flag = 'Y'
1421 where
1422 CSBE.rollup_id = i_rollup_id and
1423 CSBE.deleted_flag = 'N' and
1424 not exists
1425 (
1426 select 'x'
1427 from cst_sc_bom_explosion CSBE2
1428 where
1429 CSBE2.rollup_id = CSBE.rollup_id and
1430 CSBE2.assembly_item_id = CSBE.component_item_id and
1431 CSBE2.assembly_organization_id = CSBE.component_organization_id and
1432 CSBE2.deleted_flag = 'N'
1433 );
1434
1435 l_low_level_code := l_low_level_code + 1;
1436
1437 EXIT WHEN SQL%ROWCOUNT = 0;
1438
1439 END LOOP;
1440
1441
1442
1443 IF i_cost_type_id is not null THEN
1444
1445 l_stmt_num := 30;
1446
1447 select CCT.frozen_standard_flag
1448 into l_frozen_standard_flag
1449 from cst_cost_types CCT
1450 where CCT.cost_type_id = i_cost_type_id;
1451
1452 -- SCAPI: to support supply chain cost reports
1453 IF ( (l_frozen_standard_flag = 1) and (i_report_option_type <> -1 or i_report_option_type is null) ) THEN
1454
1455 l_stmt_num := 40;
1456
1457 delete cst_sc_low_level_codes CSLLC
1458 where
1459 CSLLC.rollup_id = i_rollup_id and
1460 exists
1461 (
1462 select 'x'
1463 from mtl_material_transactions MMT
1464 where MMT.inventory_item_id = CSLLC.inventory_item_id and
1465 MMT.organization_id = CSLLC.organization_id
1466 );
1467
1468 IF SQL%ROWCOUNT > 0 THEN
1469 o_error_code := 1001;
1470 o_error_msg :=
1471 'CSTPSCEX.compute_sc_low_level_codes():' ||
1472 to_char(l_stmt_num) || ':' ||
1473 'Cannot update standard cost for ' || to_char(SQL%ROWCOUNT) ||
1474 ' items due to existing MMT transactions';
1475 END IF;
1476
1477 END IF;
1478
1479 END IF;
1480
1481 exception
1482 when OTHERS then
1483 o_error_code := SQLCODE;
1484 o_error_msg := 'CSTPSCEX.compute_sc_low_level_codes():' ||
1485 to_char(l_stmt_num) || ':' ||
1486 substrb(SQLERRM, 1, 1000);
1487
1488 end compute_sc_low_level_codes;
1489
1490
1491 procedure supply_chain_rollup (
1492 i_rollup_id in number, -- rollup ID, CST_LISTS_S
1493 i_explosion_levels in number, -- levels to explode, NULL for all levels
1494 i_report_levels in number, -- levels in report, NULL for no report
1495 i_assignment_set_id in number, -- MRP assignment_set_id, NULL for none
1496 i_conversion_type in varchar2, -- GL_DAILY_CONVERSION_TYPES
1497 i_cost_type_id in number, -- rollup cost type
1498 i_buy_cost_type_id in number, -- buy cost cost type
1499 i_effective_date in date, -- BIC.effectivity_date
1500 i_exclude_unimpl_eco in number, -- 1 = exclude unimplemented, 2 = include
1501 i_exclude_eng in number, -- 1 = exclude eng items, 2 = include
1502 i_alt_bom_desg in varchar2,
1503 i_alt_rtg_desg in varchar2,
1504 i_lock_flag in number, -- 1 = wait for locks, 2 = no
1505 i_user_id in number,
1506 i_login_id in number,
1507 i_request_id in number,
1508 i_prog_id in number,
1509 i_prog_appl_id in number,
1510 o_error_code out NOCOPY number,
1511 o_error_msg out NOCOPY varchar2,
1512 i_lot_size_option in number, -- SCAPI: dynamic lot size
1513 i_lot_size_setting in number,
1514 i_report_option_type in number,
1515 i_report_type_type in number,
1516 i_buy_cost_detail in number
1517 )
1518 is
1519 l_include_unimpl_eco number(15);
1520 l_include_eng number(15);
1521 l_rollup_id number(15);
1522
1523 l_rollup_option number(15);
1524
1525 l_stmt_num number(15);
1526
1527 l_timestamp date;
1528
1529 l_no_bom_org number(15); -- SCAPI: check for bom parameters setup
1530
1531 l_report_levels number(15); -- := i_report_levels; commented to remove GSCC warning
1532
1533 begin
1534
1535 l_report_levels := i_report_levels; -- added to remove GSCC warning
1536
1537 l_stmt_num := 0;
1538 l_rollup_id := i_rollup_id;
1539 IF l_rollup_id IS NULL THEN
1540 select cst_lists_s.nextval
1541 into l_rollup_id
1542 from dual;
1543 END IF;
1544
1545 l_stmt_num := 10;
1546 IF i_exclude_eng = 1 THEN
1547 l_include_eng := 2;
1548 ELSE
1549 l_include_eng := 1;
1550 END IF;
1551
1552 l_stmt_num := 20;
1553 IF i_exclude_unimpl_eco = 1 THEN
1554 l_include_unimpl_eco := 2;
1555 ELSE
1556 l_include_unimpl_eco := 1;
1557 END IF;
1558
1559 l_stmt_num := 30;
1560 -- SCAPI: no insert for supply chain cost reports
1561 IF (i_report_option_type <> -1 or i_report_option_type is null) THEN
1562 insert into cst_sc_rollup_history
1563 (
1564 rollup_id,
1565 explosion_level,
1566 report_level,
1567 assignment_set_id,
1568 conversion_type,
1569 cost_type_id,
1570 buy_cost_type_id,
1571 revision_date,
1572 INC_UNIMP_ECN_FLAG,
1573 ENG_BILL_FLAG,
1574 alt_bom_desg,
1575 alt_rtg_desg,
1576 LAST_UPDATE_DATE,
1577 LAST_UPDATED_BY,
1578 LAST_UPDATE_LOGIN,
1579 CREATION_DATE,
1580 CREATED_BY,
1581 REQUEST_ID,
1582 PROGRAM_APPLICATION_ID,
1583 PROGRAM_ID,
1584 PROGRAM_UPDATE_DATE
1585 )
1586 select
1587 l_rollup_id,
1588 i_explosion_levels,
1589 l_report_levels,
1590 i_assignment_set_id,
1591 i_conversion_type,
1592 i_cost_type_id,
1593 i_buy_cost_type_id,
1594 i_effective_date,
1595 l_include_unimpl_eco,
1596 l_include_eng,
1597 i_alt_bom_desg,
1598 i_alt_rtg_desg,
1599 sysdate, -- LAST_UPDATE_DATE
1600 i_user_id, -- LAST_UPDATED_BY
1601 i_login_id, -- LAST_UPDATE_LOGIN
1602 sysdate, -- CREATION_DATE
1603 i_user_id, -- CREATED_BY
1604 i_request_id, -- REQUEST_ID
1605 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1606 i_prog_id, -- PROGRAM_ID
1607 sysdate -- PROGRAM_UPDATE_DATE
1608 from dual
1609 where not exists
1610 (
1611 select 'x'
1612 from cst_sc_rollup_history
1613 where rollup_id = l_rollup_id
1614 );
1615 END IF;
1616
1617
1618
1619 l_stmt_num := 40;
1620 CSTPSCEX.insert_assembly_items
1621 (
1622 l_rollup_id,
1623 i_user_id,
1624 i_login_id,
1625 i_request_id,
1626 i_prog_id,
1627 i_prog_appl_id,
1628 o_error_code,
1629 o_error_msg
1630 );
1631 IF o_error_code <> 0 THEN
1632 RETURN;
1633 END IF;
1634
1635
1636
1637 l_timestamp := SYSDATE;
1638
1639 l_stmt_num := 50;
1640 CSTPSCEX.explode_sc_bom
1641 (
1642 l_rollup_id,
1643 i_explosion_levels,
1644 i_assignment_set_id,
1645 i_effective_date,
1646 l_include_unimpl_eco,
1647 l_include_eng,
1648 i_alt_bom_desg,
1649 i_user_id,
1650 i_login_id,
1651 i_request_id,
1652 i_prog_id,
1653 i_prog_appl_id,
1654 o_error_code,
1655 o_error_msg
1656 );
1657 IF o_error_code <> 0 THEN
1658 RETURN;
1659 END IF;
1660
1661 update cst_sc_rollup_history CSRH
1662 set CSRH.explosion_time = (SYSDATE - l_timestamp) * 86400
1663 where CSRH.rollup_id = l_rollup_id;
1664
1665
1666
1667 l_stmt_num := 60;
1668 CSTPSCEX.snapshot_sc_conversion_rates
1669 (
1670 l_rollup_id,
1671 i_conversion_type,
1672 o_error_code,
1673 o_error_msg
1674 );
1675 IF o_error_code <> 0 THEN
1676 RETURN;
1677 END IF;
1678
1679
1680
1681 l_timestamp := SYSDATE;
1682
1683 l_stmt_num := 70;
1684 CSTPSCEX.compute_sc_low_level_codes
1685 (
1686 l_rollup_id,
1687 i_explosion_levels,
1688 i_cost_type_id,
1689 i_user_id,
1690 i_login_id,
1691 i_request_id,
1692 i_prog_id,
1693 i_prog_appl_id,
1694 o_error_code,
1695 o_error_msg,
1696 i_report_option_type
1697 );
1698 IF o_error_code <> 0 THEN
1699 RETURN;
1700 END IF;
1701
1702 --To print the BOM structure loops if any, to the log file.
1703 l_stmt_num := 75;
1704 CSTPSCEX.check_loop (
1705 l_rollup_id,
1706 o_error_code,
1707 o_error_msg
1708 );
1709
1710 IF o_error_code <> 0 THEN
1711 RETURN;
1712 END IF;
1713
1714 -- SCAPI: always use the maximum report level for consolidated reports
1715 l_stmt_num := 76;
1716 IF ((l_report_levels IS NOT NULL) and (i_report_type_type = 2)) THEN
1717 select max(low_level_code)+2
1718 into l_report_levels
1719 from cst_sc_low_level_codes
1720 where rollup_id = l_rollup_id;
1721 END IF;
1722
1723
1724 update cst_sc_rollup_history CSRH
1725 set CSRH.low_level_code_time = (SYSDATE - l_timestamp) * 86400
1726 where CSRH.rollup_id = l_rollup_id;
1727
1728
1729 l_timestamp := SYSDATE;
1730
1731 l_stmt_num := 80;
1732 -- SCAPI: no costs removal for supply chain cost reports
1733 IF (i_report_option_type <> -1 or i_report_option_type is null) THEN
1734 o_error_code := CSTPSCCR.REMOVE_ROLLEDUP_COSTS
1735 (
1736 l_rollup_id,
1737 to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), -- P_ROLLUP_DATE VARCHAR2 IN
1738 i_buy_cost_type_id, -- P_SRC_COST_TYPE_ID NUMBER IN
1739 i_cost_type_id, -- P_DEST_COST_TYPE_ID NUMBER IN
1740 null, -- P_CONC_FLAG NUMBER IN
1741 i_request_id, -- REQ_ID NUMBER IN
1742 i_prog_appl_id, -- PRGM_APPL_ID NUMBER IN
1743 i_prog_id, -- PRGM_ID NUMBER IN
1744 o_error_msg, -- X_ERR_BUF VARCHAR2 OUT
1745 i_lot_size_option,
1746 i_lot_size_setting,
1747 i_lock_flag -- Bug 3111820
1748 );
1749
1750 IF o_error_code <> 0 THEN
1751 RETURN;
1752 END IF;
1753 END IF;
1754
1755 update cst_sc_rollup_history CSRH
1756 set CSRH.remove_costs_time = (SYSDATE - l_timestamp) * 86400
1757 where CSRH.rollup_id = l_rollup_id;
1758
1759
1760
1761 l_stmt_num := 90;
1762 IF i_explosion_levels IS NULL THEN
1763 l_rollup_option := 2; -- full rollup option
1764 ELSE
1765 l_rollup_option := 1; -- single level rollup option
1766 END IF;
1767
1768
1769
1770 l_timestamp := SYSDATE;
1771
1772 l_stmt_num := 100;
1773 -- SCAPI: no cost calculation for supply chain cost reports
1774 IF (i_report_option_type <> -1 or i_report_option_type is null) THEN
1775 o_error_code := CSTPSCCR.CSTSCCRU
1776 (
1777 l_rollup_id, -- L_ROLLUP_ID NUMBER IN
1778 i_request_id, -- REQ_ID NUMBER IN
1779 i_buy_cost_type_id, -- L_SRC_COST_TYPE_ID NUMBER IN
1780 i_cost_type_id, -- L_DEST_COST_TYPE_ID NUMBER IN
1781 i_assignment_set_id, -- L_ASSIGNMENT_SET_ID NUMBER IN
1782 i_prog_appl_id, -- PRGM_APPL_ID NUMBER IN
1783 i_prog_id, -- PRGM_ID NUMBER IN
1784 i_user_id, -- L_LAST_UPDATED_BY NUMBER IN
1785 1, -- CONC_FLAG NUMBER IN
1786 l_include_unimpl_eco, -- UNIMP_FLAG NUMBER IN
1787 i_lock_flag, -- LOCKING_FLAG NUMBER IN
1788 to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), -- ROLLUP_DATE VARCHAR2 IN
1789 /* Bug 2305807. Need Effectivity Date. Bug 3098303: pass full time components */
1790 to_char(i_effective_date, 'YYYY/MM/DD HH24:MI:SS'),
1791 i_alt_bom_desg, -- ALT_BOM_DESIGNATOR VARCHAR2 IN
1792 i_alt_rtg_desg, -- ALT_RTG_DESIGNATOR VARCHAR2 IN
1793 l_rollup_option, -- ROLLUP_OPTION NUMBER IN
1794 1, -- REPORT_OPTION NUMBER IN
1795 i_exclude_eng, -- L_MFG_FLAG NUMBER IN
1796 o_error_msg, -- ERR_BUF VARCHAR2 OUT
1797 i_buy_cost_detail -- BUY_COST_DETAIL NUMBER IN
1798 );
1799
1800 IF o_error_code <> 0 THEN
1801 RETURN;
1802 END IF;
1803 END IF;
1804
1805 update cst_sc_rollup_history CSRH
1806 set CSRH.rollup_time = (SYSDATE - l_timestamp) * 86400
1807 where CSRH.rollup_id = l_rollup_id;
1808
1809
1810
1811 l_timestamp := SYSDATE;
1812
1813 IF l_report_levels IS NOT NULL THEN
1814
1815 l_stmt_num := 105;
1816 CSTPSCEX.snapshot_sc_bom_structures
1817 (
1818 l_rollup_id,
1819 i_cost_type_id,
1820 l_report_levels,
1821 i_effective_date,
1822 i_user_id,
1823 i_login_id,
1824 i_request_id,
1825 i_prog_id,
1826 i_prog_appl_id,
1827 o_error_code,
1828 o_error_msg,
1829 i_report_type_type -- SCAPI: support consolidated report
1830 );
1831 IF o_error_code <> 0 THEN
1832 RETURN;
1833 END IF;
1834
1835 END IF;
1836
1837 update cst_sc_rollup_history CSRH
1838 set CSRH.bom_structure_time = (SYSDATE - l_timestamp) * 86400
1839 where CSRH.rollup_id = l_rollup_id;
1840
1841 l_timestamp := SYSDATE;
1842
1843 /* Removed this code for bug 5678464 */
1844 /* IF i_request_id is NOT NULL THEN -- Bug 4244467
1845 l_stmt_num := 110;
1846 o_error_code := CSTPSCCM.remove_rollup_history
1847 (
1848 p_rollup_id => l_rollup_id,
1849 p_sc_cost_type_id => i_cost_type_id,
1850 p_rollup_option => l_rollup_option,
1851 x_err_buf => o_error_msg
1852 );
1853 END IF;
1854 */
1855
1856 exception
1857 when OTHERS then
1858 o_error_code := SQLCODE;
1859 o_error_msg := 'CSTPSCEX.supply_chain_rollup():' ||
1860 to_char(l_stmt_num) || ':' ||
1861 substrb(SQLERRM, 1, 1000);
1862
1863 end supply_chain_rollup;
1864
1865
1866
1867 end CSTPSCEX;