[Home] [Help]
PACKAGE BODY: APPS.CSTPSCEX
Source
1 package body CSTPSCEX as
2 /* $Header: CSTSCEXB.pls 120.12.12010000.2 2008/08/08 12:33:20 smsasidh 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 select /*+ INDEX (CSBE CST_SC_BOM_EXPLOSION_N1)*/
380 CSBE.component_item_id,
381 CSBE.component_organization_id,
382 min( CSBE.plan_level ) prior_plan_level
383 from
384 cst_sc_bom_explosion CSBE
385 where
386 CSBE.rollup_id = i_rollup_id and
387 CSBE.exploded_flag = 'N' and
388 CSBE.plan_level <= decode( i_explosion_levels, null, CSBE.plan_level+1,
389 i_explosion_levels ) and
390 not exists
391 (
392 select /*+ INDEX (CSBE2 CST_SC_BOM_EXPLOSION_N1)*/ 'x'
393 from cst_sc_bom_explosion CSBE2
394 where
395 CSBE2.rollup_id = CSBE.rollup_id and
396 CSBE2.component_item_id = CSBE.component_item_id and
397 CSBE2.component_organization_id = CSBE.component_organization_id and
398 CSBE2.exploded_flag <> 'N'
399 )
400 group by
401 CSBE.component_item_id,
402 CSBE.component_organization_id;
403
404
405 l_rows_processed NUMBER(15);
406 l_stmt_num NUMBER(15);
407 l_active_flag NUMBER(2) ; /* Added for bug 4547027 */
408
409 begin
410
411 loop
412 l_rows_processed := 0;
413
414
415 l_stmt_num := 10;
416
417 for CSBE in CSBE_cursor loop
418
419 if i_assignment_set_id is not null then
420
421 /* Added for Bug 6124274 */
422 BEGIN
423 /* Added for bug 4547027 */
424 select decode(nvl(msi.inventory_item_status_code,'NOT'||bp.bom_delete_status_code),
425 nvl(bp.bom_delete_status_code,' '),2,1)
426 into l_active_flag
427 from mtl_system_items msi,
428 bom_parameters bp
429 where msi.inventory_item_id = CSBE.component_item_id
430 and msi.organization_id = CSBE.component_organization_id
431 and bp.organization_id (+) = msi.organization_id;
432 /*Added exception to avoid the request erroring due to incorrect sourcing rule set*/
433 EXCEPTION
434 WHEN OTHERS THEN
435 l_active_flag := 2;
436 fnd_file.put_line(FND_FILE.LOG, 'Missing Source Org/item in MSI.. Item:= ' || CSBE.component_item_id || ' Org: ' || CSBE.component_organization_id);
437 END;
438
439
440 if l_active_flag = 1 then
441
442 l_stmt_num := 20;
443 CSTPSCEX.snapshot_sc_sourcing_rules
444 (
445 i_rollup_id,
446 i_assignment_set_id,
447 CSBE.component_item_id,
448 CSBE.component_organization_id,
449 i_effective_date,
450 i_user_id,
451 i_login_id,
452 i_request_id,
453 i_prog_id,
454 i_prog_appl_id,
455 o_error_code,
456 o_error_msg
457 );
458
459 if o_error_code <> 0 then
460 return;
461 end if;
462 end if; -- l_active_flag
463 end if; -- i_assignment_set_id is not null
464
465
466 l_stmt_num := 30;
467 insert into cst_sc_bom_explosion
468 (
469 ROLLUP_ID,
470 ASSEMBLY_ITEM_ID,
471 ASSEMBLY_ORGANIZATION_ID,
472 OPERATION_SEQ_NUM,
473 COMPONENT_SEQUENCE_ID,
474 COMPONENT_ITEM_ID,
475 COMPONENT_ORGANIZATION_ID,
476 COMPONENT_QUANTITY,
477 DELETED_FLAG,
478 EXPLODED_FLAG,
479 PLAN_LEVEL,
480 LAST_UPDATE_DATE,
481 LAST_UPDATED_BY,
482 LAST_UPDATE_LOGIN,
483 CREATION_DATE,
484 CREATED_BY,
485 REQUEST_ID,
486 PROGRAM_APPLICATION_ID,
487 PROGRAM_ID,
488 PROGRAM_UPDATE_DATE
489 )
490 select
491 i_rollup_id, -- ROLLUP_ID
492 CSSR.inventory_item_id, -- ASSEMBLY_ITEM_ID
493 CSSR.organization_id, -- ASSEMBLY_ORGANIZATION_ID
494 to_number( null ), -- OPERATION_SEQ_NUM
495 to_number( null ), -- COMPONENT_SEQUENCE_ID
496 CSSR.inventory_item_id, -- COMPONENT_ITEM_ID
497 CSSR.source_organization_id, -- COMPONENT_ORGANIZATION_ID
498 CSSR.allocation_percent / 100, -- COMPONENT_QUANTITY
499 'N', -- DELETED_FLAG
500 'N', -- EXPLODED_FLAG
501 CSBE.prior_plan_level + 1, -- PLAN_LEVEL
502 sysdate, -- LAST_UPDATE_DATE
503 i_user_id, -- LAST_UPDATED_BY
504 i_login_id, -- LAST_UPDATE_LOGIN
505 sysdate, -- CREATION_DATE
506 i_user_id, -- CREATED_BY
507 i_request_id, -- REQUEST_ID
508 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
509 i_prog_id, -- PROGRAM_ID
510 sysdate -- PROGRAM_UPDATE_DATE
511 from
512 cst_sc_sourcing_rules CSSR, mtl_system_items msi /* Bug 6124274 */
513 where
514 CSSR.rollup_id = i_rollup_id and
515 msi.inventory_item_id = cssr.inventory_item_id and
516 msi.organization_id = cssr.organization_id and
517 CSSR.inventory_item_id = CSBE.component_item_id and
518 CSSR.organization_id = CSBE.component_organization_id and
519 CSSR.source_type = 1 -- Transfer items only
520
521
522 -- all we need is a UNION ALL, but I'm using UNION to
523 -- force an implicit sort so that the resulting connect by
524 -- select will (usually) be sorted by op_seq_num
525 union
526
527 select
528 i_rollup_id, -- ROLLUP_ID
529 BOM.assembly_item_id, -- ASSEMBLY_ITEM_ID
530 BOM.organization_id, -- ASSEMBLY_ORGANIZATION_ID
531 BIC.operation_seq_num, -- OPERATION_SEQ_NUM
532 BIC.component_sequence_id, -- COMPONENT_SEQUENCE_ID
533 BIC.component_item_id, -- COMPONENT_ITEM_ID
534 BOM.organization_id, -- COMPONENT_ORGANIZATION_ID
535 BIC.component_quantity, -- COMPONENT_QUANTITY
536 'N', -- DELETED_FLAG
537 'N', -- EXPLODED_FLAG
538 CSBE.prior_plan_level + 1, -- PLAN_LEVEL
539 sysdate, -- LAST_UPDATE_DATE
540 i_user_id, -- LAST_UPDATED_BY
541 i_login_id, -- LAST_UPDATE_LOGIN
542 sysdate, -- CREATION_DATE
543 i_user_id, -- CREATED_BY
544 i_request_id, -- REQUEST_ID
545 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
546 i_prog_id, -- PROGRAM_ID
547 sysdate -- PROGRAM_UPDATE_DATE
548 from
549 bom_bill_of_materials BOM,
550 bom_inventory_components BIC
551 where
552 BOM.common_bill_sequence_id = BIC.bill_sequence_id and
553 BOM.assembly_item_id = CSBE.component_item_id and
554 BOM.organization_id = CSBE.component_organization_id and
555 ----------------------------
556 --- effectivity checking
557 ----------------------------
558 BIC.effectivity_date <= i_effective_date and
559 nvl( BIC.disable_date, i_effective_date + 1 ) > i_effective_date and
560 ----------------------------
561 --- alternate bom designator
562 ----------------------------
563 BOM.assembly_type =
564 decode( i_inc_eng_bill, 1, BOM.assembly_type, 1 ) AND
565 (
566 (
567 i_alt_bom_desg IS NULL AND
568 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
569 )
570 OR
571 (
572 i_alt_bom_desg IS NOT NULL AND
573 BOM.ALTERNATE_BOM_DESIGNATOR = i_alt_bom_desg
574 )
575 OR
576 ( i_alt_bom_desg IS NOT NULL AND
577 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL AND
578 NOT EXISTS
579 (
580 SELECT 'X'
581 FROM BOM_BILL_OF_MATERIALS BOM2
582 WHERE BOM2.ORGANIZATION_ID = BOM.ORGANIZATION_ID AND
583 BOM2.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID AND
584 BOM2.ALTERNATE_BOM_DESIGNATOR = i_alt_bom_desg AND
585 BOM2.assembly_type =
586 decode( i_inc_eng_bill, 1, BOM2.assembly_type, 1 )
587 )
588 )
589 ) AND
590 ( BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
591 OR
592 BOM.ALTERNATE_BOM_DESIGNATOR = i_alt_bom_desg
593 ) AND
594 ----------------------------
595 --- implementation option
596 ----------------------------
597 (
598 (
599 i_inc_unimpl_ecn = 2 AND
600 BIC.IMPLEMENTATION_DATE IS NOT NULL
601 )
602 OR
603 (
604 i_inc_unimpl_ecn = 1 AND
605 BIC.EFFECTIVITY_DATE =
606 (
607 SELECT MAX(EFFECTIVITY_DATE)
608 FROM BOM_INVENTORY_COMPONENTS BIC2
609 WHERE
610 BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID AND
611 BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID AND
612 (
613 decode( BIC2.IMPLEMENTATION_DATE,
614 NULL, BIC2.OLD_COMPONENT_SEQUENCE_ID,
615 BIC2.COMPONENT_SEQUENCE_ID ) =
616 decode( BIC.IMPLEMENTATION_DATE,
617 NULL, BIC.OLD_COMPONENT_SEQUENCE_ID,
618 BIC.COMPONENT_SEQUENCE_ID )
619 OR
620 BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
621 )
622 AND
623 BIC2.EFFECTIVITY_DATE <= i_effective_date
624 AND
625 NVL( BIC2.eco_for_production, 2 ) = 2
626 ) -- end of subquery
627 )
628 ) AND
629 ----------------------------------------------------
630 -- This should take care of excluding model and oc
631 ----------------------------------------------------
632 BIC.INCLUDE_IN_COST_ROLLUP = 1 and
633 ----------------------------------------------------
634 -- This is for ECO changes in 11i.4
635 ----------------------------------------------------
636 NVL( BIC.eco_for_production, 2 ) = 2 and
637
638 /* Fix for BUG 1604207 */
639 NVL( bic.acd_type, 1 ) <> 3 and
640
641 ----------------------------------------------------
642 -- only insert BOM if there is a Make rule
643 ----------------------------------------------------
644 0 < (
645 select nvl( sum( decode( CSSR.source_type, 2,
646 CSSR.allocation_percent, 0 ) ), 100 )
647 from cst_sc_sourcing_rules CSSR
648 where
649 CSSR.rollup_id = i_rollup_id and
650 CSSR.inventory_item_id = CSBE.component_item_id and
651 CSSR.organization_id = CSBE.component_organization_id
652 );
653
654
655
656 l_stmt_num := 40;
657
658 update cst_sc_bom_explosion
659 set exploded_flag = 'Y'
660 where rollup_id = i_rollup_id and
661 component_item_id = CSBE.component_item_id and
662 component_organization_id = CSBE.component_organization_id;
663
664 l_rows_processed := l_rows_processed + 1;
665 end loop;
666
667 exit when l_rows_processed = 0;
668 end loop;
669
670
671
672 -- This will scale down the component_quantity of components of
673 -- assemblies that have partial Make sourcing rules.
674 update cst_sc_bom_explosion CSBE
675 set CSBE.component_quantity
676 = (
677 select CSBE.component_quantity *
678 nvl( sum( decode( CSSR.source_type, 2,
679 CSSR.allocation_percent, 0 ) ) / 100, 1 )
680 from cst_sc_sourcing_rules CSSR
681 where CSSR.rollup_id = CSBE.rollup_id and
682 CSSR.inventory_item_id = CSBE.assembly_item_id and
683 CSSR.organization_id = CSBE.assembly_organization_id
684 )
685 where CSBE.rollup_id = i_rollup_id and
686 CSBE.assembly_organization_id = component_organization_id;
687
688
689
690 -- This will clear out all exploded rows, leaving only the
691 -- rows that are stuck in a loop.
692 update /*+ INDEX (CSBE CST_SC_BOM_EXPLOSION_N1)*/
693 cst_sc_bom_explosion CSBE
694 set exploded_flag = 'Y'
695 where
696 rollup_id = i_rollup_id and
697 exists (
698 select /*+ INDEX (CSBE2 CST_SC_BOM_EXPLOSION_N1)*/ 'x'
699 from cst_sc_bom_explosion CSBE2
700 where
701 CSBE2.rollup_id = CSBE.rollup_id and
702 CSBE2.component_item_id = CSBE.component_item_id and
703 CSBE2.component_organization_id = CSBE.component_organization_id and
704 CSBE2.exploded_flag = 'Y'
705 );
706
707
708 exception
709 when OTHERS then
710 o_error_code := SQLCODE;
711 o_error_msg := 'CSTPSCEX.explode_sc_bom():' ||
712 to_char(l_stmt_num) || ':' ||
713 substrb(SQLERRM, 1, 1000);
714
715 end explode_sc_bom;
716
717
718
719
720
721
722 procedure explode_sc_cost_flags (
723 i_rollup_id in number,
724 i_cost_type_id in number,
725 o_error_code out NOCOPY number,
726 o_error_msg out NOCOPY varchar2
727 )
728 is
729 cursor assm_cursor is
730 select
731 CSBS.rowid,
732 decode( CIC.inventory_asset_flag, 2, 2,
733 decode( CIC.based_on_rollup_flag, 2, 2, 1 ) ) new_ext_cost_flag
734 from
735 cst_sc_bom_structures CSBS,
736 cst_item_costs CIC
737 where
738 CSBS.rollup_id = i_rollup_id and
739 CSBS.assembly_item_id = -1 and
740 CIC.inventory_item_id = CSBS.component_item_id and
741 CIC.organization_id = CSBS.component_organization_id and
742 CIC.cost_type_id = i_cost_type_id;
743
744
745 /* the outer join to CIC is necessary because we're joining
746 to the assembly, and assembly_id can be -1 */
747
748
749 -- Note that this join to CSSR depends on the fact that
750 -- there can be at most one Make At sourcing rule for an item.
751 -- This is currently being enforced by the MRP forms.
752
753 -- Added planning factor for bug 2947036
754 /* Bug 4547027 Changed the cursor to get active_flag for the component */
755 cursor component_cursor is
756 select
757 CSBS.top_inventory_item_id top_inventory_item_id,
758 CSBS.top_organization_id top_organization_id,
759 CSBS.sort_order sort_order,
760 CSBS.rowid,
761 CSBS.bom_level,
762 BIC.basis_type,
763 /* LBM Project 3926918: Changes made to support Lot Based Materials. Added decode to check for basis type
764 of the component. If lot based then component qty becomes compt_qty/lot_size else it is unchanged. */
765 DECODE(BIC.basis_type, 2, CSBS.component_quantity/nvl(CIC.lot_size, 1), CSBS.component_quantity) component_quantity,
766 -- Bug 5139919; nvl is added to CIC.lot_size
767 BIC.include_in_cost_rollup include_in_cost_rollup,
768 nvl(BIC.component_yield_factor, 1) component_yield_factor,
769 nvl(BIC.planning_factor/100, 1) component_planning_factor,
770 CIC.inventory_asset_flag,
771 NVL(CIC.based_on_rollup_flag,2) based_on_rollup_flag, -- added NVL for bug 4377129
772 decode( nvl( BIC.wip_supply_type, nvl( MSI.wip_supply_type, 1 ) ),
773 6, 1, 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 from
776 cst_sc_bom_structures CSBS,
777 cst_item_costs CIC,
778 mtl_system_items MSI,
779 bom_inventory_components BIC,
780 bom_parameters bp /* Bug 4547027 */
781 where
782 CSBS.rollup_id = i_rollup_id and
783 CSBS.assembly_item_id = -1 and
784 CIC.inventory_item_id (+) = CSBS.top_inventory_item_id and
785 CIC.organization_id (+) = CSBS.top_organization_id and
786 CIC.cost_type_id (+) = i_cost_type_id and
787 MSI.inventory_item_id = CSBS.component_item_id and
788 MSI.organization_id = CSBS.component_organization_id and
789 bp.organization_id (+) = CSBS.component_organization_id and /* Bug 4547027 */
790 BIC.component_sequence_id (+) = CSBS.component_sequence_id
791 UNION ALL
792 select
793 CSBS.top_inventory_item_id top_inventory_item_id,
794 CSBS.top_organization_id top_organization_id,
795 CSBS.sort_order sort_order,
796 CSBS.rowid,
797 CSBS.bom_level,
798 BIC.basis_type,
799 /* LBM Project 3926918: Changes made to support Lot Based Materials. Added decode to check for basis type
800 of the component. If lot based then component qty becomes compt_qty/lot_size else it is unchanged. */
801 DECODE(BIC.basis_type, 2, CSBS.component_quantity/nvl(CIC.lot_size, 1), CSBS.component_quantity) component_quantity,
802 -- Bug 5139919; nvl is added to CIC.lot_size
803 BIC.include_in_cost_rollup include_in_cost_rollup,
804 nvl(BIC.component_yield_factor, 1) component_yield_factor,
805 nvl(BIC.planning_factor/100, 1) component_planning_factor,
806 CIC.inventory_asset_flag,
807 NVL(CIC.based_on_rollup_flag,2) based_on_rollup_flag, -- added NVL for bug 4377129
808 decode( nvl( BIC.wip_supply_type, nvl( MSI.wip_supply_type, 1 ) ),
809 6, 1, 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 from
812 cst_sc_bom_structures CSBS,
813 cst_item_costs CIC,
814 mtl_system_items MSI,
815 bom_inventory_components BIC,
816 bom_parameters bp /* Bug 4547027 */
817 where
818 CSBS.rollup_id = i_rollup_id and
819 CSBS.assembly_item_id <> -1 and
820 CIC.inventory_item_id (+) = CSBS.assembly_item_id and
821 CIC.organization_id (+) = CSBS.assembly_organization_id and
822 CIC.cost_type_id (+) = i_cost_type_id and
823 MSI.inventory_item_id = CSBS.component_item_id and
824 MSI.organization_id = CSBS.component_organization_id and
825 bp.organization_id (+) = CSBS.component_organization_id and /* Bug 4547027 */
826 BIC.component_sequence_id (+) = CSBS.component_sequence_id
827 order by
828 top_inventory_item_id,
829 top_organization_id,
830 sort_order;
831
832 TYPE STACK_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
833 ext_qty_stack STACK_TYPE;
834
835 ext_cost_flag_stack STACK_TYPE;
836
837 phtm_sub_assy_stack STACK_TYPE;
838
839 l_stmt_num number(15);
840
841 l_comp_yield_flag number(15);
842
843 begin
844
845 -- top level extended_quantity is always 1
846 ext_qty_stack( 0 ) := 1;
847
848 ext_cost_flag_stack( 0 ) := 1;
849
850 phtm_sub_assy_stack( 0 ) := 2;
851
852 /* Get component_yield_fla: Bug 2297027 */
853
854 select component_yield_flag
855 into l_comp_yield_flag
856 from cst_cost_types
857 where cost_type_id = i_cost_type_id;
858
859
860
861 -- set up the top level extend_cost_flag
862 l_stmt_num := 10;
863
864 FOR assm in assm_cursor LOOP
865
866 update cst_sc_bom_structures CSBS
867 set CSBS.extend_cost_flag
868 = assm.new_ext_cost_flag
869 where CSBS.rowid = assm.rowid;
870
871 END LOOP;
872
873 l_stmt_num := 20;
874
875 FOR comp in component_cursor LOOP
876 /* Consider component_yield_factor and planning_factor in Extended Quantity
877 Bug 2297027 and Bug 2947036
878 */
879 l_stmt_num := 30;
880 IF l_comp_yield_flag = 1 THEN
881 ext_qty_stack( comp.bom_level ) :=
882 ext_qty_stack( comp.bom_level - 1 )*comp.component_quantity*comp.component_planning_factor/
883 comp.component_yield_factor;
884 ELSE
885 ext_qty_stack( comp.bom_level ) :=
886 ext_qty_stack( comp.bom_level - 1 )*comp.component_quantity*comp.component_planning_factor;
887 END IF;
888
889 /* Added this stmt to set the proper extended quantity in case the subassembly is phantom and its components are lot based.
890 In this case we have to consider the lot size of the top assembly instead of subassembly while calculating the extended
891 quantity of the component
892 */
893 IF (phtm_sub_assy_stack( comp.bom_level -1) = 1 AND comp.basis_type = 2) THEN
894
895 l_stmt_num := 35;
896
897 select ext_qty_stack(comp.bom_level)* (cic1.lot_size/cic2.lot_size)
898 into ext_qty_stack( comp.bom_level )
899 from cst_item_costs cic1,
900 cst_item_costs cic2,
901 cst_sc_bom_structures csbs
902 where csbs.rowid = comp.rowid
903 and CIC1.inventory_item_id (+) = CSBS.assembly_item_id
904 and CIC1.organization_id (+) = CSBS.assembly_organization_id
905 and CIC1.cost_type_id (+) = i_cost_type_id
906 and CIC2.inventory_item_id (+) = CSBS.top_inventory_item_id
907 and CIC2.organization_id (+) = CSBS.top_organization_id
908 and CIC2.cost_type_id (+) = i_cost_type_id ;
909 END IF;
910
911
912
913 l_stmt_num := 40;
914
915 IF ext_cost_flag_stack( comp.bom_level - 1 ) = 2 OR
916 comp.inventory_asset_flag = 2 OR
917 comp.based_on_rollup_flag = 2 OR
918 /* Bug 4547027 Added extra check so that the cost of the components
919 of inactive assemblies is not shown in the report */
920 comp.active_flag = 2 OR
921 nvl( comp.include_in_cost_rollup, 1 ) = 2 THEN
922 ext_cost_flag_stack( comp.bom_level ) := 2;
923 ELSE
924 ext_cost_flag_stack( comp.bom_level ) := 1;
925 END IF;
926
927 l_stmt_num := 40;
928
929 IF phtm_sub_assy_stack( comp.bom_level - 1 ) = 1 OR
930 comp.phantom_flag = 1 THEN
931 phtm_sub_assy_stack( comp.bom_level ) := 1;
932 ELSE
933 phtm_sub_assy_stack( comp.bom_level ) := 2;
934 END IF;
935
936
937
938 l_stmt_num := 50;
939
940 update cst_sc_bom_structures CSBS
941 set
942 CSBS.component_quantity = comp.component_quantity,
943 CSBS.extended_quantity = ext_qty_stack( comp.bom_level ),
944 CSBS.include_in_cost_rollup = comp.include_in_cost_rollup,
945 CSBS.extend_cost_flag = ext_cost_flag_stack( comp.bom_level ),
946 CSBS.phantom_flag = comp.phantom_flag,
947 CSBS.phantom_sub_assy_flag = phtm_sub_assy_stack( comp.bom_level )
948 where CSBS.rowid = comp.rowid;
949
950
951
952 END LOOP;
953
954 EXCEPTION
955 WHEN OTHERS THEN
956 o_error_code := SQLCODE;
957 o_error_msg := 'CSTPSCEX.explode_sc_cost_flags():' ||
958 to_char(l_stmt_num) || ':' ||
959 substrb(SQLERRM, 1, 1000);
960
961 end explode_sc_cost_flags;
962
963
964
965
966
967 procedure snapshot_sc_bom_structures (
968 i_rollup_id in number,
969 i_cost_type_id in number,
970 i_report_levels in number,
971 i_effective_date in date,
972 i_user_id in number,
973 i_login_id in number,
974 i_request_id in number,
975 i_prog_id in number,
976 i_prog_appl_id in number,
977 o_error_code out NOCOPY number,
978 o_error_msg out NOCOPY varchar2,
979 i_report_type_type in number
980 )
981 is
982 cursor top_assembly_cursor is
983 select
984 CSBE.component_item_id,
985 CSBE.component_organization_id
986 from
987 cst_sc_bom_explosion CSBE
988 where
989 CSBE.rollup_id = i_rollup_id and
990 CSBE.assembly_item_id = -1 and
991 CSBE.deleted_flag = 'Y'; -- Bug 3665428: make snapshot only for valid items without loop
992
993
994
995
996 l_err_code NUMBER(15);
997 l_err_mesg VARCHAR2(100);
998
999 l_bom_level number(15);
1000
1001 l_stmt_num number(15);
1002
1003 begin
1004
1005 IF i_report_levels IS NULL THEN
1006 return;
1007 END IF;
1008
1009
1010 -- SCAPI: delete previous data;
1011 delete cst_sc_bom_structures
1012 where rollup_id in (i_rollup_id, -1*i_rollup_id);
1013
1014
1015 l_stmt_num := 10;
1016 FOR top_assm in top_assembly_cursor LOOP
1017 BEGIN
1018
1019 l_stmt_num := 20;
1020
1021 insert into cst_sc_bom_structures
1022 (
1023 ROLLUP_ID,
1024 TOP_INVENTORY_ITEM_ID,
1025 TOP_ORGANIZATION_ID,
1026 SORT_ORDER,
1027 BOM_LEVEL,
1028 ASSEMBLY_ITEM_ID,
1029 ASSEMBLY_ORGANIZATION_ID,
1030 COMPONENT_SEQUENCE_ID,
1031 COMPONENT_ITEM_ID,
1032 COMPONENT_ORGANIZATION_ID,
1033 COMPONENT_QUANTITY,
1034 EXTENDED_QUANTITY,
1035 INCLUDE_IN_COST_ROLLUP,
1036 EXTEND_COST_FLAG,
1037 PHANTOM_FLAG,
1038 PHANTOM_SUB_ASSY_FLAG,
1039 LAST_UPDATE_DATE,
1040 LAST_UPDATED_BY,
1041 LAST_UPDATE_LOGIN,
1042 CREATION_DATE,
1043 CREATED_BY,
1044 REQUEST_ID,
1045 PROGRAM_APPLICATION_ID,
1046 PROGRAM_ID,
1047 PROGRAM_UPDATE_DATE
1048 )
1049 select
1050 i_rollup_id, -- ROLLUP_ID
1051 top_assm.component_item_id, -- TOP_INVENTORY_ITEM_ID
1052 top_assm.component_organization_id, -- TOP_ORGANIZATION_ID
1053 rownum, -- SORT_ORDER
1054 level, -- BOM_LEVEL
1055 CSBE.assembly_item_id, -- ASSEMBLY_ITEM_ID
1056 CSBE.assembly_organization_id, -- ASSEMBLY_ORGANIZATION_ID
1057 CSBE.component_sequence_id, -- COMPONENT_SEQUENCE_ID
1058 CSBE.component_item_id, -- COMPONENT_ITEM_ID
1059 CSBE.component_organization_id, -- COMPONENT_ORGANIZATION_ID
1060 CSBE.component_quantity, -- COMPONENT_QUANTITY
1061 1, -- EXTENDED_QUANTITY
1062 1, -- INCLUDE_IN_COST_ROLLUP
1063 1, -- EXTEND_COST_FLAG
1064 2, -- PHANTOM_FLAG
1065 2, -- PHANTOM_SUB_ASSY_FLAG
1066 sysdate, -- LAST_UPDATE_DATE
1067 i_user_id, -- LAST_UPDATED_BY
1068 i_login_id, -- LAST_UPDATE_LOGIN
1069 sysdate, -- CREATION_DATE
1070 i_user_id, -- CREATED_BY
1071 i_request_id, -- REQUEST_ID
1072 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1073 i_prog_id, -- PROGRAM_ID
1074 sysdate -- PROGRAM_UPDATE_DATE
1075 from
1076 cst_sc_bom_explosion CSBE
1077 start with
1078 rollup_id = i_rollup_id and
1079 assembly_item_id = -1 and
1080 component_item_id = top_assm.component_item_id and
1081 component_organization_id = top_assm.component_organization_id
1082 connect by
1083 prior rollup_id = rollup_id and
1084 prior component_item_id = assembly_item_id and
1085 prior component_organization_id = assembly_organization_id and
1086 level <= i_report_levels;
1087
1088
1089 EXCEPTION
1090 WHEN OTHERS THEN
1091
1092 l_err_code := SQLCODE;
1093 l_err_mesg := substrb( SQLERRM, 1, 100 );
1094
1095 insert into cst_sc_bom_structures
1096 (
1097 ROLLUP_ID,
1098 TOP_INVENTORY_ITEM_ID,
1099 TOP_ORGANIZATION_ID,
1100 SORT_ORDER,
1101 BOM_LEVEL,
1102 ASSEMBLY_ITEM_ID,
1103 ASSEMBLY_ORGANIZATION_ID,
1104 COMPONENT_SEQUENCE_ID,
1105 COMPONENT_ITEM_ID,
1106 COMPONENT_ORGANIZATION_ID,
1107 COMPONENT_QUANTITY,
1108 EXTENDED_QUANTITY,
1109 INCLUDE_IN_COST_ROLLUP,
1110 EXTEND_COST_FLAG,
1111 PHANTOM_FLAG,
1112 PHANTOM_SUB_ASSY_FLAG,
1113 ERROR_CODE,
1114 ERROR_MESG,
1115 LAST_UPDATE_DATE,
1116 LAST_UPDATED_BY,
1117 LAST_UPDATE_LOGIN,
1118 CREATION_DATE,
1119 CREATED_BY,
1120 REQUEST_ID,
1121 PROGRAM_APPLICATION_ID,
1122 PROGRAM_ID,
1123 PROGRAM_UPDATE_DATE
1124 )
1125 values
1126 (
1127 i_rollup_id, -- ROLLUP_ID
1128 top_assm.component_item_id, -- TOP_INVENTORY_ITEM_ID
1129 top_assm.component_organization_id, -- TOP_ORGANIZATION_ID
1130 0, -- SORT_ORDER
1131 0, -- BOM_LEVEL
1132 -1, -- ASSEMBLY_ITEM_ID
1133 -1, -- ASSEMBLY_ORGANIZATION_ID
1134 null, -- COMPONENT_SEQUENCE_ID
1135 top_assm.component_item_id, -- COMPONENT_ITEM_ID
1136 top_assm.component_organization_id, -- COMPONENT_ORGANIZATION_ID
1137 0, -- COMPONENT_QUANTITY
1138 0, -- EXTENDED_QUANTITY
1139 2, -- INCLUDE_IN_COST_ROLLUP
1140 2, -- EXTEND_COST_FLAG
1141 2, -- PHANTOM_FLAG
1142 2, -- PHANTOM_SUB_ASSY_FLAG
1143 l_err_code, -- ERROR_CODE
1144 l_err_mesg, -- ERROR_MESG
1145 sysdate, -- LAST_UPDATE_DATE
1146 i_user_id, -- LAST_UPDATED_BY
1147 i_login_id, -- LAST_UPDATE_LOGIN
1148 sysdate, -- CREATION_DATE
1149 i_user_id, -- CREATED_BY
1150 i_request_id, -- REQUEST_ID
1151 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1152 i_prog_id, -- PROGRAM_ID
1153 sysdate -- PROGRAM_UPDATE_DATE
1154 );
1155
1156 END;
1157 END LOOP;
1158
1159
1160
1161 l_stmt_num := 30;
1162
1163 -- update the item revision column
1164 update cst_sc_bom_structures CSBS
1165 set CSBS.component_revision =
1166 (
1167 select
1168 substr( max( to_char( MIR.effectivity_date, 'YYYY/MM/DD HH24:MI:SS' ) ||
1169 MIR.revision ), 20 )
1170 from
1171 mtl_item_revisions MIR
1172 where
1173 MIR.inventory_item_id = CSBS.component_item_id and
1174 MIR.organization_id = CSBS.component_organization_id and
1175 MIR.effectivity_date <= i_effective_date
1176 )
1177 where CSBS.rollup_id = i_rollup_id;
1178
1179
1180
1181 l_stmt_num := 40;
1182 IF i_cost_type_id is not null THEN
1183 explode_sc_cost_flags
1184 (
1185 i_rollup_id,
1186 i_cost_type_id,
1187 o_error_code,
1188 o_error_msg
1189 );
1190
1191 IF o_error_code <> 0 THEN
1192 RETURN;
1193 END IF;
1194 END IF;
1195
1196 -- SCAPI: insert data for consolidated report using negative rollup_id
1197 l_stmt_num := 50;
1198 IF i_report_type_type = 2 THEN
1199 insert into cst_sc_bom_structures
1200 (
1201 ROLLUP_ID,
1202 TOP_INVENTORY_ITEM_ID,
1203 TOP_ORGANIZATION_ID,
1204 SORT_ORDER,
1205 BOM_LEVEL,
1206 ASSEMBLY_ITEM_ID,
1207 ASSEMBLY_ORGANIZATION_ID,
1208 COMPONENT_SEQUENCE_ID,
1209 COMPONENT_ITEM_ID,
1210 COMPONENT_ORGANIZATION_ID,
1211 COMPONENT_QUANTITY,
1212 EXTENDED_QUANTITY,
1213 INCLUDE_IN_COST_ROLLUP,
1214 EXTEND_COST_FLAG,
1215 PHANTOM_FLAG,
1216 PHANTOM_SUB_ASSY_FLAG,
1217 COMPONENT_REVISION,
1218 LAST_UPDATE_DATE,
1219 LAST_UPDATED_BY,
1220 LAST_UPDATE_LOGIN,
1221 CREATION_DATE,
1222 CREATED_BY,
1223 REQUEST_ID,
1224 PROGRAM_APPLICATION_ID,
1225 PROGRAM_ID,
1226 PROGRAM_UPDATE_DATE
1227 )
1228 select
1229 -1*i_rollup_id, -- ROLLUP_ID
1230 CSBS.top_inventory_item_id, -- TOP_INVENTORY_ITEM_ID
1231 CSBS.top_organization_id, -- TOP_ORGANIZATION_ID
1232 max(CSBS.sort_order), -- SORT_ORDER
1233 max(CSBS.bom_level), -- BOM_LEVEL
1234 max(CSBS.assembly_item_id), -- ASSEMBLY_ITEM_ID
1235 CSBS.assembly_organization_id, -- ASSEMBLY_ORGANIZATION_ID
1236 null, -- COMPONENT_SEQUENCE_ID
1237 CSBS.component_item_id, -- COMPONENT_ITEM_ID
1238 CSBS.component_organization_id, -- COMPONENT_ORGANIZATION_ID
1239 sum(CSBS.component_quantity), -- COMPONENT_QUANTITY
1240 sum(CSBS.extended_quantity), -- EXTENDED_QUANTITY
1241 null, -- INCLUDE_IN_COST_ROLLUP
1242 CSBS.extend_cost_flag, -- EXTEND_COST_FLAG
1243 CSBS.phantom_flag, -- PHANTOM_FLAG
1244 max(CSBS.phantom_sub_assy_flag), -- PHANTOM_SUB_ASSY_FLAG
1245 CSBS.component_revision, -- COMPONENT_REVISION
1246 sysdate, -- LAST_UPDATE_DATE
1247 i_user_id, -- LAST_UPDATED_BY
1248 i_login_id, -- LAST_UPDATE_LOGIN
1249 sysdate, -- CREATION_DATE
1250 i_user_id, -- CREATED_BY
1251 i_request_id, -- REQUEST_ID
1252 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1253 i_prog_id, -- PROGRAM_ID
1254 sysdate -- PROGRAM_UPDATE_DATE
1255 from
1256 cst_sc_bom_structures CSBS
1257 where
1258 rollup_id = i_rollup_id
1259 group by
1260 CSBS.top_inventory_item_id,
1261 CSBS.top_organization_id,
1262 CSBS.assembly_organization_id,
1263 CSBS.component_item_id,
1264 CSBS.component_organization_id,
1265 CSBS.extend_cost_flag,
1266 CSBS.phantom_flag,
1267 CSBS.component_revision;
1268 END IF;
1269
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 o_error_code := SQLCODE;
1273 o_error_msg := 'CSTPSCEX.snapshot_sc_bom_structures():' ||
1274 to_char(l_stmt_num) || ':' ||
1275 substrb(SQLERRM, 1, 1000);
1276
1277 end snapshot_sc_bom_structures;
1278
1279
1280
1281 procedure compute_sc_low_level_codes (
1282 i_rollup_id in number,
1283 i_explosion_levels in number,
1284 i_cost_type_id in number,
1285 i_user_id in number,
1286 i_login_id in number,
1287 i_request_id in number,
1288 i_prog_id in number,
1289 i_prog_appl_id in number,
1290 o_error_code out NOCOPY number,
1291 o_error_msg out NOCOPY varchar2,
1292 i_report_option_type in number -- SCAPI: for supply chain cost reports
1293 )
1294 is
1295 l_low_level_code NUMBER(15);
1296 l_frozen_standard_flag number(15);
1297
1298 l_stmt_num number(15);
1299 begin
1300
1301 l_low_level_code := LOWEST_LEVEL_CODE;
1302
1303 /* Supply chain enhancement: if not a full rollup, only assign low level codes
1304 for items that exist in cst_sc_lists */
1305
1306 IF i_explosion_levels is not null THEN
1307
1308 l_stmt_num := 5;
1309
1310 update cst_sc_bom_explosion CSBE
1311 set deleted_flag = 'Y'
1312 where
1313 CSBE.rollup_id = i_rollup_id and
1314 CSBE.deleted_flag = 'N' and
1315 not exists ( select 'Item in List'
1316 from cst_sc_lists CSL
1317 where CSL.rollup_id = i_rollup_id
1318 and CSL.inventory_item_id = CSBE.component_item_id
1319 and CSL.organization_id = CSBE.component_organization_id );
1320
1321 END IF;
1322
1323 LOOP
1324
1325 l_stmt_num := 10;
1326
1327 insert into cst_sc_low_level_codes
1328 (
1329 ROLLUP_ID,
1330 INVENTORY_ITEM_ID,
1331 ORGANIZATION_ID,
1332 LOW_LEVEL_CODE,
1333 LAST_UPDATE_DATE,
1334 LAST_UPDATED_BY,
1335 LAST_UPDATE_LOGIN,
1336 CREATION_DATE,
1337 CREATED_BY,
1338 REQUEST_ID,
1339 PROGRAM_APPLICATION_ID,
1340 PROGRAM_ID,
1341 PROGRAM_UPDATE_DATE
1342 )
1343 select distinct
1344 i_rollup_id, -- ROLLUP_ID
1345 CSBE.component_item_id, -- INVENTORY_ITEM_ID
1346 CSBE.component_organization_id, -- ORGANIZATION_ID
1347 l_low_level_code, -- LOW_LEVEL_CODE
1348 sysdate, -- LAST_UPDATE_DATE
1349 i_user_id, -- LAST_UPDATED_BY
1350 i_login_id, -- LAST_UPDATE_LOGIN
1351 sysdate, -- CREATION_DATE
1352 i_user_id, -- CREATED_BY
1353 i_request_id, -- REQUEST_ID
1354 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1355 i_prog_id, -- PROGRAM_ID
1356 sysdate -- PROGRAM_UPDATE_DATE
1357 from
1358 cst_sc_bom_explosion CSBE
1359 where
1360 CSBE.rollup_id = i_rollup_id and
1361 CSBE.deleted_flag = 'N' and
1362 not exists
1363 (
1364 select 'x'
1365 from cst_sc_bom_explosion CSBE2
1366 where
1367 CSBE2.rollup_id = CSBE.rollup_id and
1368 CSBE2.assembly_item_id = CSBE.component_item_id and
1369 CSBE2.assembly_organization_id = CSBE.component_organization_id and
1370 CSBE2.deleted_flag = 'N'
1371 );
1372
1373 l_stmt_num := 20;
1374
1375 update cst_sc_bom_explosion CSBE
1376 set deleted_flag = 'Y'
1377 where
1378 CSBE.rollup_id = i_rollup_id and
1379 CSBE.deleted_flag = 'N' and
1380 not exists
1381 (
1382 select 'x'
1383 from cst_sc_bom_explosion CSBE2
1384 where
1385 CSBE2.rollup_id = CSBE.rollup_id and
1386 CSBE2.assembly_item_id = CSBE.component_item_id and
1387 CSBE2.assembly_organization_id = CSBE.component_organization_id and
1388 CSBE2.deleted_flag = 'N'
1389 );
1390
1391 l_low_level_code := l_low_level_code + 1;
1392
1393 EXIT WHEN SQL%ROWCOUNT = 0;
1394
1395 END LOOP;
1396
1397
1398
1399 IF i_cost_type_id is not null THEN
1400
1401 l_stmt_num := 30;
1402
1403 select CCT.frozen_standard_flag
1404 into l_frozen_standard_flag
1405 from cst_cost_types CCT
1406 where CCT.cost_type_id = i_cost_type_id;
1407
1408 -- SCAPI: to support supply chain cost reports
1409 IF ( (l_frozen_standard_flag = 1) and (i_report_option_type <> -1 or i_report_option_type is null) ) THEN
1410
1411 l_stmt_num := 40;
1412
1413 delete cst_sc_low_level_codes CSLLC
1414 where
1415 CSLLC.rollup_id = i_rollup_id and
1416 exists
1417 (
1418 select 'x'
1419 from mtl_material_transactions MMT
1420 where MMT.inventory_item_id = CSLLC.inventory_item_id and
1421 MMT.organization_id = CSLLC.organization_id
1422 );
1423
1424 IF SQL%ROWCOUNT > 0 THEN
1425 o_error_code := 1001;
1426 o_error_msg :=
1427 'CSTPSCEX.compute_sc_low_level_codes():' ||
1428 to_char(l_stmt_num) || ':' ||
1429 'Cannot update standard cost for ' || to_char(SQL%ROWCOUNT) ||
1430 ' items due to existing MMT transactions';
1431 END IF;
1432
1433 END IF;
1434
1435 END IF;
1436
1437
1438 exception
1439 when OTHERS then
1440 o_error_code := SQLCODE;
1441 o_error_msg := 'CSTPSCEX.compute_sc_low_level_codes():' ||
1442 to_char(l_stmt_num) || ':' ||
1443 substrb(SQLERRM, 1, 1000);
1444
1445 end compute_sc_low_level_codes;
1446
1447
1448 procedure supply_chain_rollup (
1449 i_rollup_id in number, -- rollup ID, CST_LISTS_S
1450 i_explosion_levels in number, -- levels to explode, NULL for all levels
1451 i_report_levels in number, -- levels in report, NULL for no report
1452 i_assignment_set_id in number, -- MRP assignment_set_id, NULL for none
1453 i_conversion_type in varchar2, -- GL_DAILY_CONVERSION_TYPES
1454 i_cost_type_id in number, -- rollup cost type
1455 i_buy_cost_type_id in number, -- buy cost cost type
1456 i_effective_date in date, -- BIC.effectivity_date
1457 i_exclude_unimpl_eco in number, -- 1 = exclude unimplemented, 2 = include
1458 i_exclude_eng in number, -- 1 = exclude eng items, 2 = include
1459 i_alt_bom_desg in varchar2,
1460 i_alt_rtg_desg in varchar2,
1461 i_lock_flag in number, -- 1 = wait for locks, 2 = no
1462 i_user_id in number,
1463 i_login_id in number,
1464 i_request_id in number,
1465 i_prog_id in number,
1466 i_prog_appl_id in number,
1467 o_error_code out NOCOPY number,
1468 o_error_msg out NOCOPY varchar2,
1469 i_lot_size_option in number, -- SCAPI: dynamic lot size
1470 i_lot_size_setting in number,
1471 i_report_option_type in number,
1472 i_report_type_type in number,
1473 i_buy_cost_detail in number
1474 )
1475 is
1476 l_include_unimpl_eco number(15);
1477 l_include_eng number(15);
1478 l_rollup_id number(15);
1479
1480 l_rollup_option number(15);
1481
1482 l_stmt_num number(15);
1483
1484 l_timestamp date;
1485
1486 l_no_bom_org number(15); -- SCAPI: check for bom parameters setup
1487
1488 l_report_levels number(15); -- := i_report_levels; commented to remove GSCC warning
1489
1490 begin
1491
1492 l_report_levels := i_report_levels; -- added to remove GSCC warning
1493
1494 l_stmt_num := 0;
1495 l_rollup_id := i_rollup_id;
1496 IF l_rollup_id IS NULL THEN
1497 select cst_lists_s.nextval
1498 into l_rollup_id
1499 from dual;
1500 END IF;
1501
1502 l_stmt_num := 10;
1503 IF i_exclude_eng = 1 THEN
1504 l_include_eng := 2;
1505 ELSE
1506 l_include_eng := 1;
1507 END IF;
1508
1509 l_stmt_num := 20;
1510 IF i_exclude_unimpl_eco = 1 THEN
1511 l_include_unimpl_eco := 2;
1512 ELSE
1513 l_include_unimpl_eco := 1;
1514 END IF;
1515
1516 l_stmt_num := 30;
1517 -- SCAPI: no insert for supply chain cost reports
1518 IF (i_report_option_type <> -1 or i_report_option_type is null) THEN
1519 insert into cst_sc_rollup_history
1520 (
1521 rollup_id,
1522 explosion_level,
1523 report_level,
1524 assignment_set_id,
1525 conversion_type,
1526 cost_type_id,
1527 buy_cost_type_id,
1528 revision_date,
1529 INC_UNIMP_ECN_FLAG,
1530 ENG_BILL_FLAG,
1531 alt_bom_desg,
1532 alt_rtg_desg,
1533 LAST_UPDATE_DATE,
1534 LAST_UPDATED_BY,
1535 LAST_UPDATE_LOGIN,
1536 CREATION_DATE,
1537 CREATED_BY,
1538 REQUEST_ID,
1539 PROGRAM_APPLICATION_ID,
1540 PROGRAM_ID,
1541 PROGRAM_UPDATE_DATE
1542 )
1543 select
1544 l_rollup_id,
1545 i_explosion_levels,
1546 l_report_levels,
1547 i_assignment_set_id,
1548 i_conversion_type,
1549 i_cost_type_id,
1550 i_buy_cost_type_id,
1551 i_effective_date,
1552 l_include_unimpl_eco,
1553 l_include_eng,
1554 i_alt_bom_desg,
1555 i_alt_rtg_desg,
1556 sysdate, -- LAST_UPDATE_DATE
1557 i_user_id, -- LAST_UPDATED_BY
1558 i_login_id, -- LAST_UPDATE_LOGIN
1559 sysdate, -- CREATION_DATE
1560 i_user_id, -- CREATED_BY
1561 i_request_id, -- REQUEST_ID
1562 i_prog_appl_id, -- PROGRAM_APPLICATION_ID
1563 i_prog_id, -- PROGRAM_ID
1564 sysdate -- PROGRAM_UPDATE_DATE
1565 from dual
1566 where not exists
1567 (
1568 select 'x'
1569 from cst_sc_rollup_history
1570 where rollup_id = l_rollup_id
1571 );
1572 END IF;
1573
1574
1575
1576 l_stmt_num := 40;
1577 CSTPSCEX.insert_assembly_items
1578 (
1579 l_rollup_id,
1580 i_user_id,
1581 i_login_id,
1582 i_request_id,
1583 i_prog_id,
1584 i_prog_appl_id,
1585 o_error_code,
1586 o_error_msg
1587 );
1588 IF o_error_code <> 0 THEN
1589 RETURN;
1590 END IF;
1591
1592
1593
1594 l_timestamp := SYSDATE;
1595
1596 l_stmt_num := 50;
1597 CSTPSCEX.explode_sc_bom
1598 (
1599 l_rollup_id,
1600 i_explosion_levels,
1601 i_assignment_set_id,
1602 i_effective_date,
1603 l_include_unimpl_eco,
1604 l_include_eng,
1605 i_alt_bom_desg,
1606 i_user_id,
1607 i_login_id,
1608 i_request_id,
1609 i_prog_id,
1610 i_prog_appl_id,
1611 o_error_code,
1612 o_error_msg
1613 );
1614 IF o_error_code <> 0 THEN
1615 RETURN;
1616 END IF;
1617
1618 update cst_sc_rollup_history CSRH
1619 set CSRH.explosion_time = (SYSDATE - l_timestamp) * 86400
1620 where CSRH.rollup_id = l_rollup_id;
1621
1622
1623
1624 l_stmt_num := 60;
1625 CSTPSCEX.snapshot_sc_conversion_rates
1626 (
1627 l_rollup_id,
1628 i_conversion_type,
1629 o_error_code,
1630 o_error_msg
1631 );
1632 IF o_error_code <> 0 THEN
1633 RETURN;
1634 END IF;
1635
1636
1637
1638 l_timestamp := SYSDATE;
1639
1640 l_stmt_num := 70;
1641 CSTPSCEX.compute_sc_low_level_codes
1642 (
1643 l_rollup_id,
1644 i_explosion_levels,
1645 i_cost_type_id,
1646 i_user_id,
1647 i_login_id,
1648 i_request_id,
1649 i_prog_id,
1650 i_prog_appl_id,
1651 o_error_code,
1652 o_error_msg,
1653 i_report_option_type
1654 );
1655 IF o_error_code <> 0 THEN
1656 RETURN;
1657 END IF;
1658
1659 -- SCAPI: always use the maximum report level for consolidated reports
1660 l_stmt_num := 76;
1661 IF ((l_report_levels IS NOT NULL) and (i_report_type_type = 2)) THEN
1662 select max(low_level_code)+2
1663 into l_report_levels
1664 from cst_sc_low_level_codes
1665 where rollup_id = l_rollup_id;
1666 END IF;
1667
1668
1669 update cst_sc_rollup_history CSRH
1670 set CSRH.low_level_code_time = (SYSDATE - l_timestamp) * 86400
1671 where CSRH.rollup_id = l_rollup_id;
1672
1673
1674 l_timestamp := SYSDATE;
1675
1676 l_stmt_num := 80;
1677 -- SCAPI: no costs removal for supply chain cost reports
1678 IF (i_report_option_type <> -1 or i_report_option_type is null) THEN
1679 o_error_code := CSTPSCCR.REMOVE_ROLLEDUP_COSTS
1680 (
1681 l_rollup_id,
1682 to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), -- P_ROLLUP_DATE VARCHAR2 IN
1683 i_buy_cost_type_id, -- P_SRC_COST_TYPE_ID NUMBER IN
1684 i_cost_type_id, -- P_DEST_COST_TYPE_ID NUMBER IN
1685 null, -- P_CONC_FLAG NUMBER IN
1686 i_request_id, -- REQ_ID NUMBER IN
1687 i_prog_appl_id, -- PRGM_APPL_ID NUMBER IN
1688 i_prog_id, -- PRGM_ID NUMBER IN
1689 o_error_msg, -- X_ERR_BUF VARCHAR2 OUT
1690 i_lot_size_option,
1691 i_lot_size_setting,
1692 i_lock_flag -- Bug 3111820
1693 );
1694
1695 IF o_error_code <> 0 THEN
1696 RETURN;
1697 END IF;
1698 END IF;
1699
1700 update cst_sc_rollup_history CSRH
1701 set CSRH.remove_costs_time = (SYSDATE - l_timestamp) * 86400
1702 where CSRH.rollup_id = l_rollup_id;
1703
1704
1705
1706 l_stmt_num := 90;
1707 IF i_explosion_levels IS NULL THEN
1708 l_rollup_option := 2; -- full rollup option
1709 ELSE
1710 l_rollup_option := 1; -- single level rollup option
1711 END IF;
1712
1713
1714
1715 l_timestamp := SYSDATE;
1716
1717 l_stmt_num := 100;
1718 -- SCAPI: no cost calculation for supply chain cost reports
1719 IF (i_report_option_type <> -1 or i_report_option_type is null) THEN
1720 o_error_code := CSTPSCCR.CSTSCCRU
1721 (
1722 l_rollup_id, -- L_ROLLUP_ID NUMBER IN
1723 i_request_id, -- REQ_ID NUMBER IN
1724 i_buy_cost_type_id, -- L_SRC_COST_TYPE_ID NUMBER IN
1725 i_cost_type_id, -- L_DEST_COST_TYPE_ID NUMBER IN
1726 i_assignment_set_id, -- L_ASSIGNMENT_SET_ID NUMBER IN
1727 i_prog_appl_id, -- PRGM_APPL_ID NUMBER IN
1728 i_prog_id, -- PRGM_ID NUMBER IN
1729 i_user_id, -- L_LAST_UPDATED_BY NUMBER IN
1730 1, -- CONC_FLAG NUMBER IN
1731 l_include_unimpl_eco, -- UNIMP_FLAG NUMBER IN
1732 i_lock_flag, -- LOCKING_FLAG NUMBER IN
1733 to_char(sysdate,'YYYY/MM/DD HH24:MI:SS'), -- ROLLUP_DATE VARCHAR2 IN
1734 /* Bug 2305807. Need Effectivity Date. Bug 3098303: pass full time components */
1735 to_char(i_effective_date, 'YYYY/MM/DD HH24:MI:SS'),
1736 i_alt_bom_desg, -- ALT_BOM_DESIGNATOR VARCHAR2 IN
1737 i_alt_rtg_desg, -- ALT_RTG_DESIGNATOR VARCHAR2 IN
1738 l_rollup_option, -- ROLLUP_OPTION NUMBER IN
1739 1, -- REPORT_OPTION NUMBER IN
1740 i_exclude_eng, -- L_MFG_FLAG NUMBER IN
1741 o_error_msg, -- ERR_BUF VARCHAR2 OUT
1742 i_buy_cost_detail -- BUY_COST_DETAIL NUMBER IN
1743 );
1744
1745 IF o_error_code <> 0 THEN
1746 RETURN;
1747 END IF;
1748 END IF;
1749
1750 update cst_sc_rollup_history CSRH
1751 set CSRH.rollup_time = (SYSDATE - l_timestamp) * 86400
1752 where CSRH.rollup_id = l_rollup_id;
1753
1754
1755
1756 l_timestamp := SYSDATE;
1757
1758 IF l_report_levels IS NOT NULL THEN
1759
1760 l_stmt_num := 105;
1761 CSTPSCEX.snapshot_sc_bom_structures
1762 (
1763 l_rollup_id,
1764 i_cost_type_id,
1765 l_report_levels,
1766 i_effective_date,
1767 i_user_id,
1768 i_login_id,
1769 i_request_id,
1770 i_prog_id,
1771 i_prog_appl_id,
1772 o_error_code,
1773 o_error_msg,
1774 i_report_type_type -- SCAPI: support consolidated report
1775 );
1776 IF o_error_code <> 0 THEN
1777 RETURN;
1778 END IF;
1779
1780 END IF;
1781
1782 update cst_sc_rollup_history CSRH
1783 set CSRH.bom_structure_time = (SYSDATE - l_timestamp) * 86400
1784 where CSRH.rollup_id = l_rollup_id;
1785
1786 l_timestamp := SYSDATE;
1787
1788 /* Removed this code for bug 5678464 */
1789 /* IF i_request_id is NOT NULL THEN -- Bug 4244467
1790 l_stmt_num := 110;
1791 o_error_code := CSTPSCCM.remove_rollup_history
1792 (
1793 p_rollup_id => l_rollup_id,
1794 p_sc_cost_type_id => i_cost_type_id,
1795 p_rollup_option => l_rollup_option,
1796 x_err_buf => o_error_msg
1797 );
1798 END IF;
1799 */
1800
1801 exception
1802 when OTHERS then
1803 o_error_code := SQLCODE;
1804 o_error_msg := 'CSTPSCEX.supply_chain_rollup():' ||
1805 to_char(l_stmt_num) || ':' ||
1806 substrb(SQLERRM, 1, 1000);
1807
1808 end supply_chain_rollup;
1809
1810
1811
1812 end CSTPSCEX;