[Home] [Help]
PACKAGE BODY: APPS.CSTPSCCR
Source
1 PACKAGE BODY CSTPSCCR AS
2 /* $Header: CSTSCCRB.pls 120.15.12010000.3 2008/10/21 12:37:06 smsasidh ship $ */
3
4
5 FUNCTION remove_rolledup_costs(
6 p_rollup_id IN NUMBER,
7 p_rollup_date IN VARCHAR2,
8 p_buy_cost_type_id IN NUMBER,
9 p_dest_cost_type_id IN NUMBER,
10 p_conc_flag IN NUMBER,
11 req_id IN NUMBER,
12 prgm_appl_id IN NUMBER,
13 prgm_id IN NUMBER,
14 x_err_buf OUT NOCOPY VARCHAR2,
15 p_lot_size_option IN NUMBER, -- SCAPI: dynamic lot size
16 p_lot_size_setting IN NUMBER,
17 p_locking_flag IN NUMBER -- Bug 3111280
18 )
19 RETURN INTEGER
20 IS
21 status NUMBER;
22 l_stmt_num NUMBER;
23 default_cost_type_id NUMBER;
24 l_last_updated_by NUMBER;
25 l_login_id NUMBER := -1;
26 l_rollup_date CONSTANT DATE := TO_DATE(p_rollup_date,'YYYY/MM/DD HH24:MI:SS');
27 locking_error EXCEPTION;
28 return_code NUMBER;
29
30 BEGIN
31
32 status := 0;
33
34 /* Get Last_Updated_By from cst_sc_rollup_history instead of FND_GLOBAL structure */
35 l_stmt_num := 5;
36 SELECT LAST_UPDATED_BY ,
37 LAST_UPDATE_LOGIN
38 INTO l_last_updated_by,
39 l_login_id
40 FROM cst_sc_rollup_history
41 WHERE rollup_id = p_rollup_id
42 AND ROWNUM=1;
43
44 l_stmt_num := 10;
45 /*------------------------------------------------------------+
46 | Get the default cost type ID and an assortment of flags |
47 | for the cost type being rolled up. |
48 +------------------------------------------------------------*/
49
50 SELECT DEFAULT_COST_TYPE_ID
51 INTO default_cost_type_id
52 FROM CST_COST_TYPES
53 WHERE COST_TYPE_ID = p_dest_cost_type_id;
54
55 l_stmt_num := 15;
56 -- SCAPI: always do explicit lock
57 -- Bug 3111820: moved cstrwait_lock from cstsccru
58 return_code := cstpsccr.cstrwait_lock(
59 p_dest_cost_type_id,
60 default_cost_type_id,
61 p_rollup_id,
62 x_err_buf,
63 p_locking_flag);
64
65 IF return_code <> 0 THEN
66 raise locking_error;
67 END IF;
68
69 l_stmt_num := 20;
70
71 /*------------------------------------------------------------+
72 | Delete item costs that were generated by the previous cost |
73 | rollup. |
74 +------------------------------------------------------------*/
75
76 DELETE CST_ITEM_COST_DETAILS cicd
77 WHERE cicd.COST_TYPE_ID = p_dest_cost_type_id
78 AND (cicd.ROLLUP_SOURCE_TYPE = 2
79 OR cicd.ROLLUP_SOURCE_TYPE = 3
80 )
81 AND (cicd.inventory_item_id, cicd.organization_id) IN
82 ( SELECT
83 csllc.inventory_item_id,
84 csllc.organization_id
85 FROM cst_sc_low_level_codes csllc,
86 cst_item_costs cia
87 WHERE
88 csllc.rollup_id = p_rollup_id
89 AND csllc.inventory_item_id = cia.inventory_item_id
90 AND cia.organization_id = csllc.organization_id
91 AND cia.cost_type_id = p_dest_cost_type_id
92 AND (cia.BASED_ON_ROLLUP_FLAG = 1 OR cia.DEFAULTED_FLAG = 1) -- Added for 7237848
93 );
94
95 /* SCAPI: Store the existing lot size in CSLLC */
96 l_stmt_num := 25;
97
98 IF (p_lot_size_option IS NOT NULL) THEN
99 UPDATE cst_sc_low_level_codes CSLLC
100 SET CSLLC.lot_size =
101 (SELECT CIC.lot_size
102 FROM cst_item_costs CIC
103 WHERE CIC.cost_type_id = p_dest_cost_type_id
104 AND CIC.inventory_item_id = CSLLC.inventory_item_id
105 AND CIC.organization_id = CSLLC.organization_id
106 )
107 WHERE CSLLC.rollup_id = p_rollup_id;
108 END IF;
109
110 l_stmt_num := 30;
111 /*------------------------------------------------------------+
112 | Delete item attributes that were generated by the cost |
113 | rollup. |
114 +------------------------------------------------------------*/
115 DELETE CST_ITEM_COSTS cia
116 WHERE
117 COST_TYPE_ID = p_dest_cost_type_id
118 AND DEFAULTED_FLAG = 1 /* YES */
119 AND default_cost_type_id <> p_dest_cost_type_id
120 AND (cia.INVENTORY_ITEM_ID, cia.ORGANIZATION_ID) IN
121 (SELECT CSLLC.INVENTORY_ITEM_ID, CSLLC.ORGANIZATION_ID
122 FROM CST_SC_LOW_LEVEL_CODES CSLLC
123 WHERE CSLLC.ROLLUP_ID = p_rollup_id
124 );
125
126 l_stmt_num := 40;
127 /*------------------------------------------------------------+
128 | Create rows in CST_ITEM_COSTS for make items that do |
129 | not already have rows in there from default cost type |
130 +------------------------------------------------------------*/
131 INSERT INTO CST_ITEM_COSTS (
132 INVENTORY_ITEM_ID,
133 ORGANIZATION_ID,
134 COST_TYPE_ID,
135 LAST_UPDATE_DATE,
136 LAST_UPDATED_BY,
137 CREATION_DATE,
138 CREATED_BY,
139 LAST_UPDATE_LOGIN,
140 INVENTORY_ASSET_FLAG,
141 LOT_SIZE,
142 BASED_ON_ROLLUP_FLAG,
143 SHRINKAGE_RATE,
144 DEFAULTED_FLAG,
145 REQUEST_ID,
146 PROGRAM_APPLICATION_ID,
147 PROGRAM_ID,
148 PROGRAM_UPDATE_DATE)
149 SELECT
150 cia.INVENTORY_ITEM_ID,
151 cia.ORGANIZATION_ID,
152 p_dest_cost_type_id,
153 l_rollup_date,
154 l_last_updated_by,
155 l_rollup_date,
156 l_last_updated_by,
157 l_login_id,
158 cia.INVENTORY_ASSET_FLAG,
159 NVL(csllc.LOT_SIZE, NVL( cia.LOT_SIZE, 1 )), -- SCAPI: use existing lot size
160 1, -- YES
161 NVL( cia.SHRINKAGE_RATE, 0 ),
162 1, -- YES
163 DECODE(p_conc_flag, 1, req_id, NULL),
164 DECODE(p_conc_flag, 1, prgm_appl_id, NULL),
165 DECODE(p_conc_flag, 1, prgm_id, NULL),
166 DECODE(p_conc_flag, 1,
167 l_rollup_date, NULL)
168 FROM
169 CST_SC_LOW_LEVEL_CODES csllc,
170 CST_ITEM_COSTS cia,
171 MTL_PARAMETERS mp
172 WHERE cia.ORGANIZATION_ID = csllc.organization_id
173 AND csllc.ROLLUP_ID = p_rollup_id
174 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
175 AND cia.BASED_ON_ROLLUP_FLAG = 1 -- YES
176 AND mp.ORGANIZATION_ID = csllc.organization_id
177 AND cia.COST_TYPE_ID IN (default_cost_type_id, mp.PRIMARY_COST_METHOD) -- Added for 5678464
178 AND ( cia.COST_TYPE_ID = default_cost_type_id
179 OR
180 (
181 (cia.COST_TYPE_ID = mp.PRIMARY_COST_METHOD)
182 AND
183 (NOT EXISTS
184 (SELECT 'X'
185 FROM CST_ITEM_COSTS cia2
186 WHERE cia2.INVENTORY_ITEM_ID = cia.INVENTORY_ITEM_ID
187 AND cia2.ORGANIZATION_ID = cia.ORGANIZATION_ID
188 AND cia2.COST_TYPE_ID = default_cost_type_id)
189 )
190 )
191 ) /* Supply chain enhancement: default valuation cost type */
192 AND NOT EXISTS
193 (SELECT 'X'
194 FROM CST_ITEM_COSTS cia1
195 WHERE cia1.INVENTORY_ITEM_ID = cia.INVENTORY_ITEM_ID
196 AND cia1.ORGANIZATION_ID = cia.ORGANIZATION_ID
197 AND cia1.COST_TYPE_ID = p_dest_cost_type_id
198 );
199
200
201 l_stmt_num := 50;
202 /*------------------------------------------------------------+
203 | Create the user entered rows for make items that do not |
204 | already have costs within the cost type being rolled up. |
205 | from the default cost type |
206 +------------------------------------------------------------*/
207 /* man: modfied the AND clause so that even defaulted rows
208 from the defaulted cost type would be copied over */
209 INSERT INTO CST_ITEM_COST_DETAILS (
210 INVENTORY_ITEM_ID,
211 COST_TYPE_ID,
212 LAST_UPDATE_DATE,
213 LAST_UPDATED_BY,
214 CREATION_DATE,
215 CREATED_BY,
216 LAST_UPDATE_LOGIN,
217 ORGANIZATION_ID,
218 SOURCE_ORGANIZATION_ID,
219 OPERATION_SEQUENCE_ID,
220 OPERATION_SEQ_NUM,
221 DEPARTMENT_ID,
222 LEVEL_TYPE,
223 ACTIVITY_ID,
224 RESOURCE_SEQ_NUM,
225 RESOURCE_ID,
226 RESOURCE_RATE,
227 ITEM_UNITS,
228 ACTIVITY_UNITS,
229 USAGE_RATE_OR_AMOUNT,
230 BASIS_TYPE,
231 BASIS_RESOURCE_ID,
232 BASIS_FACTOR,
233 NET_YIELD_OR_SHRINKAGE_FACTOR,
234 ITEM_COST,
235 COST_ELEMENT_ID,
236 ROLLUP_SOURCE_TYPE,
237 REQUEST_ID,
238 PROGRAM_APPLICATION_ID,
239 PROGRAM_ID,
240 PROGRAM_UPDATE_DATE)
241 SELECT
242 cicd.INVENTORY_ITEM_ID,
243 p_dest_cost_type_id,
244 l_rollup_date,
245 l_last_updated_by,
246 l_rollup_date,
247 l_last_updated_by,
248 l_login_id,
249 --l_last_updated_by,
250 cicd.ORGANIZATION_ID,
251 cicd.ORGANIZATION_ID,
252 cicd.OPERATION_SEQUENCE_ID,
253 cicd.OPERATION_SEQ_NUM,
254 cicd.DEPARTMENT_ID,
255 1, -- This level
256 cicd.ACTIVITY_ID,
257 cicd.RESOURCE_SEQ_NUM,
258 cicd.RESOURCE_ID,
259 cicd.RESOURCE_RATE,
260 cicd.ITEM_UNITS,
261 cicd.ACTIVITY_UNITS,
262 cicd.USAGE_RATE_OR_AMOUNT,
263 cicd.BASIS_TYPE,
264 cicd.BASIS_RESOURCE_ID,
265 decode(cicd.BASIS_TYPE, 2, NVL(1/cia.LOT_SIZE, cicd.BASIS_FACTOR),
266 cicd.BASIS_FACTOR), -- SCAPI: use existing lot size
267 cicd.NET_YIELD_OR_SHRINKAGE_FACTOR,
268 decode(cicd.BASIS_TYPE, 2, NVL(cicd.ITEM_COST/(cicd.BASIS_FACTOR*cia.LOT_SIZE),
269 cicd.ITEM_COST), cicd.ITEM_COST), -- SCAPI: use existing lot size
270 cicd.COST_ELEMENT_ID,
271 2, -- Default
272 DECODE(p_conc_flag, 1, req_id, NULL),
273 DECODE(p_conc_flag, 1, prgm_appl_id, NULL),
274 DECODE(p_conc_flag, 1, prgm_id, NULL),
275 DECODE(p_conc_flag, 1,
276 l_rollup_date, NULL)
277 FROM
278 CST_SC_LOW_LEVEL_CODES csllc,
279 CST_ITEM_COSTS cia,
280 CST_ITEM_COST_DETAILS cicd,
281 MTL_PARAMETERS mp
282 WHERE csllc.ROLLUP_ID = p_rollup_id
283 AND cia.ORGANIZATION_ID = csllc.organization_id
284 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
285 AND cia.COST_TYPE_ID = p_dest_cost_type_id
286 AND cia.INVENTORY_ASSET_FLAG = 1 -- YES
287 AND cia.DEFAULTED_FLAG = 1 -- YES
288 AND cia.BASED_ON_ROLLUP_FLAG = 1 -- YES
289 AND cicd.ORGANIZATION_ID = csllc.organization_id
290 AND cicd.INVENTORY_ITEM_ID = cia.INVENTORY_ITEM_ID
291 AND cicd.ROLLUP_SOURCE_TYPE in (1,2)
292 AND mp.ORGANIZATION_ID = csllc.organization_id
293 AND cicd.COST_TYPE_ID IN (default_cost_type_id, mp.PRIMARY_COST_METHOD) -- Added for 5678464
294 AND ( cicd.COST_TYPE_ID = default_cost_type_id
295 OR
296 (
297 (cicd.COST_TYPE_ID = mp.PRIMARY_COST_METHOD)
298 AND
299 (NOT EXISTS
300 (SELECT 'X'
301 FROM CST_ITEM_COSTS cia2
302 WHERE cia2.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
303 AND cia2.ORGANIZATION_ID = csllc.ORGANIZATION_ID
304 AND cia2.COST_TYPE_ID = default_cost_type_id)
305 )
306 )
307 ) /* Supply chain enhancement: default valuation cost type */
308 AND cicd.COST_TYPE_ID NOT IN (2, 5, 6); -- Bug 2288462
309
310
311 l_stmt_num := 55;
312 /*------------------------------------------------------------+
313 | Create the user rows for items that do not |
314 | already have costs within the cost type in CIC |
315 +------------------------------------------------------------*/
316
317
318 INSERT INTO CST_ITEM_COSTS (
319 INVENTORY_ITEM_ID,
323 LAST_UPDATED_BY,
320 ORGANIZATION_ID,
321 COST_TYPE_ID,
322 LAST_UPDATE_DATE,
324 CREATION_DATE,
325 CREATED_BY,
326 LAST_UPDATE_LOGIN,
327 INVENTORY_ASSET_FLAG,
328 LOT_SIZE,
329 BASED_ON_ROLLUP_FLAG,
330 SHRINKAGE_RATE,
331 DEFAULTED_FLAG,
332 ITEM_COST,
333 REQUEST_ID,
334 PROGRAM_APPLICATION_ID,
335 PROGRAM_ID,
336 PROGRAM_UPDATE_DATE)
337 SELECT
338 csllc.INVENTORY_ITEM_ID,
339 csllc.ORGANIZATION_ID,
340 p_dest_cost_type_id,
341 l_rollup_date,
342 l_last_updated_by,
343 l_rollup_date,
344 l_last_updated_by,
345 l_login_id,
346 --l_last_updated_by,
347 DECODE(msi.INVENTORY_ASSET_FLAG,'Y',1,2),
348 NVL(csllc.LOT_SIZE, NVL( msi.STD_LOT_SIZE, 1 )), -- SCAPI: use existing lot size
349 1, -- YES
350 NVL( msi.SHRINKAGE_RATE, 0 ),
351 1, -- YES
352 0, -- ITEM_COST
353 DECODE(p_conc_flag, 1, req_id, NULL),
354 DECODE(p_conc_flag, 1, prgm_appl_id, NULL),
355 DECODE(p_conc_flag, 1, prgm_id, NULL),
356 DECODE(p_conc_flag, 1,
357 l_rollup_date, NULL)
358 FROM
359 CST_SC_LOW_LEVEL_CODES csllc,
360 MTL_SYSTEM_ITEMS msi
361 WHERE
362 msi.ORGANIZATION_ID = csllc.organization_id
363 AND csllc.ROLLUP_ID = p_rollup_id
364 AND msi.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
365 AND NOT EXISTS
366 (SELECT 'X'
367 FROM CST_ITEM_COSTS cia1, MTL_PARAMETERS mp
368 WHERE cia1.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
369 AND cia1.ORGANIZATION_ID = csllc.ORGANIZATION_ID
370 AND mp.ORGANIZATION_ID = csllc.ORGANIZATION_ID
371 AND cia1.COST_TYPE_ID
372 in (default_cost_type_id,p_dest_cost_type_id,mp.PRIMARY_COST_METHOD));
373 /* Supply chain enhancement: default valuation cost type */
374
375
376 -- SCAPI: Dynamic lot size logic
377 -- lot_size_option = 2 is set to number
378 -- lot_size_option = 3 is set to factor
379 if ((p_lot_size_option = 2) or (p_lot_size_option = 3)) then
380 l_stmt_num := 60;
381 UPDATE cst_item_costs CIC
382 SET CIC.lot_size =
383 (SELECT decode(p_lot_size_option, 2, nvl(p_lot_size_setting, CIC.lot_size),
384 3, nvl(p_lot_size_setting*MSI.std_lot_size, CIC.lot_size))
385 FROM mtl_system_items MSI
386 WHERE MSI.inventory_item_id = CIC.inventory_item_id
387 AND MSI.organization_id = CIC.organization_id)
388 WHERE CIC.cost_type_id = p_dest_cost_type_id
389 AND CIC.based_on_rollup_flag = 1
390 AND (CIC.inventory_item_id, CIC.organization_id) IN
391 (SELECT CSL.inventory_item_id, CSL.organization_id
392 FROM cst_sc_lists CSL
393 WHERE CSL.rollup_id = p_rollup_id);
394
395 l_stmt_num := 70;
396 UPDATE cst_item_cost_details CICD
397 SET (CICD.basis_factor, CICD.item_cost) =
398 (SELECT nvl(1/CIC.lot_size, CICD.basis_factor),
399 nvl(CICD.item_cost/(CICD.basis_factor*CIC.lot_size), CICD.item_cost)
400 FROM cst_item_costs CIC
401 WHERE CIC.inventory_item_id = CICD.inventory_item_id
402 AND CIC.organization_id = CICD.organization_id
403 AND CIC.cost_type_id = CICD.cost_type_id)
404 WHERE CICD.cost_type_id = p_dest_cost_type_id
405 AND CICD.basis_type = 2
406 AND CICD.level_type = 1
407 AND (CICD.inventory_item_id, CICD.organization_id) IN
408 (SELECT CSL.inventory_item_id, CSL.organization_id
409 FROM cst_sc_lists CSL
410 WHERE CSL.rollup_id = p_rollup_id)
411 AND EXISTS
412 (SELECT 'x' FROM cst_item_costs CIC2
413 WHERE CIC2.inventory_item_id = CICD.inventory_item_id
414 AND CIC2.organization_id = CICD.organization_id
415 AND CIC2.cost_type_id = CICD.cost_type_id
416 AND CIC2.based_on_rollup_flag = 1);
417 end if;
418
419 x_err_buf := 'CSTPSCCR.remove_rolledup_costs: ' ||'Success';
420 return(status);
421
422 EXCEPTION
423 when LOCKING_ERROR then
424 return(return_code);
425 when others then
429
426 status := SQLCODE;
427 x_err_buf := 'CSTPSCCR.remove_rolledup_costs ' ||'stmt_num= '||l_stmt_num||':'|| substrb(sqlerrm,1,60);
428 return(status);
430 END remove_rolledup_costs;
431
432
433 FUNCTION cstrlock (
434 table_name IN VARCHAR2,
435 l_dest_cost_type_id IN NUMBER,
436 l_default_cost_type_id IN NUMBER,
437 l_rollup_id IN NUMBER,
438 err_buf OUT NOCOPY VARCHAR2,
439 l_locking_flag IN NUMBER
440 )
441 RETURN INTEGER
442 IS
443
444 status number;
445 NO_DATAS_FOUND number := 100;
446
447 CURSOR cc is
448 SELECT
449 CIA.INVENTORY_ITEM_ID
450 FROM
451 CST_ITEM_COSTS CIA,
452 CST_SC_LOW_LEVEL_CODES CSLLC
453 WHERE CSLLC.ROLLUP_ID = l_rollup_id
454 AND CIA.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
455 AND CIA.ORGANIZATION_ID = CSLLC.organization_id
456 AND CIA.COST_TYPE_ID = l_dest_cost_type_id
457 FOR UPDATE OF CIA.INVENTORY_ITEM_ID NOWAIT; /* Modified for Bug 6467821 */
458
459 CURSOR cd is
460 SELECT CICD.INVENTORY_ITEM_ID
461 FROM CST_SC_LOW_LEVEL_CODES CSLLC,
462 CST_ITEM_COST_DETAILS CICD
463 WHERE CSLLC.ROLLUP_ID = l_rollup_id
464 AND CICD.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
465 AND CICD.ORGANIZATION_ID = CSLLC.organization_id
466 AND CICD.COST_TYPE_ID = l_dest_cost_type_id
467 FOR UPDATE OF CICD.INVENTORY_ITEM_ID NOWAIT; /* Modified for Bug 6467821 */
468
469 -- SCAPI: if lock_flag is Yes, do not use NOWAIT
470 CURSOR ce is
471 SELECT
472 CIA.INVENTORY_ITEM_ID
473 FROM
474 CST_ITEM_COSTS CIA,
475 CST_SC_LOW_LEVEL_CODES CSLLC
476 WHERE CSLLC.ROLLUP_ID = l_rollup_id
477 AND CIA.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
478 AND CIA.ORGANIZATION_ID = CSLLC.organization_id
479 AND CIA.COST_TYPE_ID = l_dest_cost_type_id
480 FOR UPDATE OF CIA.LAST_UPDATED_BY;
481
482 CURSOR cf is
483 SELECT CICD.INVENTORY_ITEM_ID
484 FROM CST_SC_LOW_LEVEL_CODES CSLLC,
485 CST_ITEM_COST_DETAILS CICD
486 WHERE CSLLC.ROLLUP_ID = l_rollup_id
487 AND CICD.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
488 AND CICD.ORGANIZATION_ID = CSLLC.organization_id
489 AND CICD.COST_TYPE_ID = l_dest_cost_type_id
490 FOR UPDATE OF CICD.LAST_UPDATED_BY;
491
492 BEGIN
493 -- SCAPI: use different cursors based on the locking flag
494
495 if (table_name = 'CST_ITEM_COSTS' and l_locking_flag = 2) then
496 OPEN cc;
497 status := SQLCODE;
498 elsif (table_name = 'CST_ITEM_COST_DETAILS' and l_locking_flag = 2) then
499 OPEN cd;
500 status := SQLCODE;
501 elsif (table_name = 'CST_ITEM_COSTS' and l_locking_flag = 1) then
502 OPEN ce;
503 status := SQLCODE;
504 elsif (table_name = 'CST_ITEM_COST_DETAILS' and l_locking_flag = 1) then
505 OPEN cf;
506 status := SQLCODE;
507 else
508 status := NO_DATAS_FOUND;
509 end if;
510
511 return (status);
512 EXCEPTION
513 when others then
514 status := SQLCODE;
515 err_buf := 'CSTRLOCK:' || substrb(sqlerrm,1,60);
516 return(status);
517 END cstrlock;
518
519
520
521 FUNCTION cstrwait_lock(
522 l_dest_cost_type_id IN NUMBER,
523 l_default_cost_type_id IN NUMBER,
524 l_rollup_id IN NUMBER,
525 err_buf OUT NOCOPY VARCHAR2,
526 l_locking_flag IN NUMBER
527 )
528 RETURN INTEGER
529 IS
530
531 status number := -54;
532 counter number := 0;
533 BEGIN
534 /*
535 ** Lock the table of CST_ITEM_COSTS
536 */
537 -- SCAPI: use loop only if locking_flag is not yes
538 if (l_locking_flag = 1) then
539 status := CSTPSCCR.cstrlock('CST_ITEM_COSTS',l_dest_cost_type_id,
540 l_default_cost_type_id,
541 l_rollup_id,err_buf,l_locking_flag);
542 else
543 WHILE (counter < NUM_TRIES and status = -54)
544 LOOP
545 status := CSTPSCCR.cstrlock('CST_ITEM_COSTS',l_dest_cost_type_id,
546 l_default_cost_type_id,
547 l_rollup_id,err_buf,l_locking_flag);
548 if status = -54 then
549 DBMS_LOCK.SLEEP(SLEEP_TIME);
550 end if;
551 counter := counter + 1;
552 END LOOP;
553 end if;
554
555 if status <> 0 then
556 if status = -54 then
557 err_buf := 'CST_LOCK_FAILED_CIC: ';
558 status := 8888;
559 end if;
560 err_buf := err_buf || status;
561 return(status);
562 else
563 /*
564 ** Lock the table of CST_ITEM_COST_DETAILS
565 */
566 -- SCAPI: use loop only if locking_flag is not yes
567 status := -54;
568 if (l_locking_flag = 1) then
572 else
569 status := CSTPSCCR.cstrlock('CST_ITEM_COST_DETAILS',l_dest_cost_type_id,
570 l_default_cost_type_id,
571 l_rollup_id,err_buf,l_locking_flag);
573 while (counter < NUM_TRIES and status = -54)
574 LOOP
575 status := CSTPSCCR.cstrlock('CST_ITEM_COST_DETAILS',
576 l_dest_cost_type_id, l_default_cost_type_id,
577 l_rollup_id,err_buf,l_locking_flag);
578 if status = -54 then
579 DBMS_LOCK.SLEEP(SLEEP_TIME);
580 end if;
581 counter := counter + 1;
582 END LOOP;
583 end if;
584 end if;
585
586 if status = -54 then
587 err_buf := 'CST_LOCK_FAILED_CICD: ';
588 status := 8888;
589 end if;
590 err_buf := err_buf || status;
591
592 return(status);
593 EXCEPTION
594 when others then
595 status := SQLCODE;
596 err_buf := 'CSTRWAIT_LOCK:' || substrb(sqlerrm,1,60);
597 return(status);
598 END cstrwait_lock;
599
600
601 FUNCTION cstsccru (
602 l_rollup_id IN NUMBER,
603 req_id IN NUMBER,
604 l_buy_cost_type_id IN NUMBER,
605 l_dest_cost_type_id IN NUMBER,
606 l_assignment_set_id IN NUMBER,
607 prgm_appl_id IN NUMBER,
608 prgm_id IN NUMBER,
609 l_last_updated_by IN NUMBER,
610 conc_flag IN NUMBER,
611 unimp_flag IN NUMBER,
612 locking_flag IN NUMBER,
613 rollup_date IN VARCHAR2,
614 revision_date IN VARCHAR2,
615 alt_bom_designator IN VARCHAR2,
616 alt_rtg_designator IN VARCHAR2,
617 rollup_option IN NUMBER,
618 report_option IN NUMBER,
619 l_mfg_flag IN NUMBER,
620 err_buf OUT NOCOPY VARCHAR2,
621 buy_cost_detail IN NUMBER -- SCAPI: option to preserve buy cost details
622 )
623 RETURN INTEGER
624 IS
625
626
627 l_count NUMBER;
628 max_level NUMBER;
629 min_level NUMBER;
630 cur_level NUMBER;
631 pl_activity_flag NUMBER;
632 pl_cost_code_flag NUMBER;
633 pl_operation_flag NUMBER;
634 pl_element_flag NUMBER;
635 comp_yield_flag NUMBER;
636 item_shrinkage_flag NUMBER;
637 bom_snapshot_flag NUMBER;
638 l_snapshot_designator VARCHAR2(10);
639 default_cost_type_id NUMBER;
640 return_code NUMBER;
641 l_round_unit NUMBER;
642 l_precision NUMBER;
643 l_ext_precision NUMBER;
644 l_rev_datetime DATE;
645 l_item_id NUMBER;
646 l_org_id NUMBER;
647 CM_THIS_LEVEL NUMBER := 1;
648 CM_PREVIOUS_LEVEL NUMBER := 2;
649 CM_REPORT_ONLY NUMBER := 3;
650 CM_FROZEN_STANDARD NUMBER := 1;
651 l_oerr_code NUMBER;
652 l_buy_cost NUMBER;
653 l_vendor_id NUMBER;
654 l_src_org_id NUMBER;
655 l_markup NUMBER;
656 l_markup_code NUMBER;
657 l_wsm_enabled VARCHAR2(1);
658 l_ret_code NUMBER;
659 l_phantom_mat NUMBER;
660 sql_stmt_num NUMBER := 0;
661 standard_error EXCEPTION;
662 snapshot_error EXCEPTION;
663 l_login_id NUMBER := -1;
664 l_rollup_date CONSTANT DATE := TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS');
665
666 TYPE inv_item_tbl_type IS TABLE OF CST_SC_LOW_LEVEL_CODES.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
667 TYPE org_id_tbl_type IS TABLE OF CST_SC_LOW_LEVEL_CODES.organization_id%TYPE INDEX BY BINARY_INTEGER;
668 TYPE roun_tbl_type IS TABLE OF CST_SC_LOW_LEVEL_CODES.round_unit%TYPE INDEX BY BINARY_INTEGER;
669 TYPE prec_tbl_type IS TABLE OF CST_SC_LOW_LEVEL_CODES.precision%TYPE INDEX BY BINARY_INTEGER;
670 TYPE ext_tbl_type IS TABLE OF CST_SC_LOW_LEVEL_CODES.ext_precision%TYPE INDEX BY BINARY_INTEGER;
671 TYPE pcm_tbl_type IS TABLE OF MTL_PARAMETERS.PRIMARY_COST_METHOD%TYPE INDEX BY BINARY_INTEGER;
672
673 l_inv_item_tbl inv_item_tbl_type;
674 l_org_id_tbl org_id_tbl_type;
675 l_roun_tbl roun_tbl_type;
676 l_prec_tbl prec_tbl_type;
677 l_ext_tbl ext_tbl_type;
678 l_pcm_tbl pcm_tbl_type;
679
680 CURSOR csllc_org_cur IS
681 SELECT DISTINCT
682 organization_id
683 FROM CST_SC_LOW_LEVEL_CODES
684 WHERE rollup_id = l_rollup_id;
685
686
687 CURSOR csllc_org_level_cur( p_cur_level NUMBER) IS
688 SELECT DISTINCT
689 organization_id,
690 ext_precision
691 FROM CST_SC_LOW_LEVEL_CODES
692 WHERE rollup_id = l_rollup_id
693 AND low_level_code = p_cur_level;
694
695
696
697 CURSOR cllc_org_item_cur(p_org_id IN NUMBER) IS
698 SELECT inventory_item_id
699 FROM CST_SC_LOW_LEVEL_CODES
700 WHERE rollup_id = l_rollup_id
701 AND organization_id = p_org_id;
702
703
704 CURSOR cllc_cur IS
708 precision,
705 SELECT inventory_item_id,
706 organization_id,
707 round_unit,
709 ext_precision
710 FROM CST_SC_LOW_LEVEL_CODES csllc
711 WHERE csllc.rollup_id = l_rollup_id;
712
713 CURSOR cllc1_cur IS
714 SELECT inventory_item_id,
715 organization_id,
716 round_unit,
717 precision,
718 ext_precision
719 FROM CST_SC_LOW_LEVEL_CODES csllc
720 WHERE csllc.rollup_id = l_rollup_id;
721
722 CURSOR cllc3_cur (p_rollup_id NUMBER) IS
723 SELECT inventory_item_id,
724 organization_id,
725 round_unit,
726 precision,
727 ext_precision
728 FROM CST_SC_LOW_LEVEL_CODES csllc
729 WHERE csllc.rollup_id = p_rollup_id;
730
731
732 CURSOR cllc2_cur (current_level in number) IS
733 SELECT inventory_item_id,
734 organization_id,
735 round_unit,
736 precision,
737 ext_precision
738 FROM cst_sc_low_level_codes
739 WHERE rollup_id = l_rollup_id
740 AND low_level_code = current_level;
741
742 CURSOR cllc10_cur (current_level in number) IS
743 SELECT csllc.inventory_item_id AS inventory_item_id,
744 csllc.organization_id AS organization_id,
745 csllc.round_unit AS round_unit,
746 csllc.precision AS precision,
747 csllc.ext_precision AS ext_precision,
748 mp.primary_cost_method AS primary_cost_method
749 FROM cst_sc_low_level_codes csllc,
750 mtl_parameters mp
751 WHERE csllc.rollup_id = l_rollup_id
752 AND csllc.low_level_code = current_level
753 AND mp.organization_id = csllc.organization_id;
754
755
756 BEGIN
757
758
759 /* Bug 3098303: initialize l_rev_datetime, revision_date has all time components */
760 /* Bug 3590153: use local variables l_rev_datetime and l_phantom_mat to improve performance */
761 sql_stmt_num := 1;
762
763 l_rev_datetime := fnd_date.canonical_to_date(revision_date);
764
765 sql_stmt_num := 2;
766
767 l_phantom_mat := nvl(fnd_profile.value('CST_RU_PHANTOM_MATERIAL'),1);
768 sql_stmt_num := 5;
769
770 select LAST_UPDATE_LOGIN
771 into l_login_id
772 from cst_sc_rollup_history
773 where rollup_id = l_rollup_id
774 and rownum=1;
775
776
777 sql_stmt_num := 10;
778 /*------------------------------------------------------------+
779 | Get the default cost type ID and an assortment of flags |
780 | for the destination cost type |
781 +------------------------------------------------------------*/
782 SELECT DEFAULT_COST_TYPE_ID,
783 1,
784 BOM_SNAPSHOT_FLAG,
785 ALTERNATE_BOM_DESIGNATOR,
786 COMPONENT_YIELD_FLAG,
787 PL_OPERATION_FLAG,
788 PL_ACTIVITY_FLAG,
789 PL_RESOURCE_FLAG,
790 PL_ELEMENT_FLAG
791 INTO default_cost_type_id,
792 item_shrinkage_flag,
793 bom_snapshot_flag,
794 l_snapshot_designator,
795 comp_yield_flag,
796 pl_operation_flag,
797 pl_activity_flag,
798 pl_cost_code_flag,
799 pl_element_flag
800 FROM CST_COST_TYPES
801 WHERE COST_TYPE_ID = l_dest_cost_type_id;
802
803
804
805 /*------------------------------------------------------------+
806 | Get the currency precision |
807 +------------------------------------------------------------*/
808 sql_stmt_num := 12;
809
810 OPEN csllc_org_cur;
811 LOOP
812 FETCH csllc_org_cur INTO l_org_id;
813 IF (csllc_org_cur%NOTFOUND) THEN
814 EXIT;
815 END IF;
816
817 l_round_unit := 0;
818 l_precision := 0;
819 l_ext_precision := 0;
820
821 sql_stmt_num := 15;
822
823 CSTPUTIL.CSTPUGCI(l_org_id, l_round_unit, l_precision, l_ext_precision);
824
825
826 sql_stmt_num := 20;
827
828 UPDATE CST_SC_LOW_LEVEL_CODES CSLLC
829 SET
830 CSLLC.ROUND_UNIT = l_round_unit,
831 CSLLC.PRECISION = l_precision,
832 CSLLC.EXT_PRECISION = l_ext_precision
833 WHERE CSLLC.organization_id = l_org_id
834 AND CSLLC.ROLLUP_ID = l_rollup_id;
835
836
837 IF (l_assignment_set_id IS NOT NULL) THEN
838
839 OPEN cllc_org_item_cur(l_org_id);
840 LOOP
841 FETCH cllc_org_item_cur INTO l_item_id;
842 IF (cllc_org_item_cur%NOTFOUND) THEN
843 EXIT;
844 END IF;
845
846
847 sql_stmt_num := 25;
848 populate_markup_costs (
849 l_rollup_id,
850 l_item_id,
854 l_dest_cost_type_id,
851 l_org_id,
852 l_assignment_set_id,
853 l_buy_cost_type_id,
855 return_code,
856 err_buf);
857
858 IF (return_code <> 0) THEN
859 RAISE STANDARD_ERROR;
860 END IF;
861
862
863 sql_stmt_num := 30;
864 populate_shipping_costs (
865 l_rollup_id,
866 l_item_id,
867 l_org_id,
868 l_assignment_set_id,
869 l_buy_cost_type_id,
870 l_dest_cost_type_id,
871 return_code,
872 err_buf);
873
874 IF (return_code <> 0) THEN
875 RAISE STANDARD_ERROR;
876 END IF;
877
878
879
880
881 sql_stmt_num := 35;
882 populate_buy_costs (
883 l_rollup_id,
884 l_assignment_set_id,
885 l_item_id,
886 l_org_id,
887 l_buy_cost_type_id,
888 return_code,
889 err_buf);
890
891 IF (return_code <> 0) THEN
892 RAISE STANDARD_ERROR;
893 END IF;
894
895
896 END LOOP;
897 CLOSE cllc_org_item_cur;
898
899 END IF;
900
901 END LOOP;
902 CLOSE csllc_org_cur;
903
904
905 sql_stmt_num := 60;
906
907 /*------------------------------------------------------------+
908 | Calculate this level resource costs for assemblies. |
909 | Note: Will use cursor for 1 item, mainly for create config |
910 | program. This improves performance from 150 sec to |
911 | less than a second in a database. |
912 +------------------------------------------------------------*/
913 INSERT INTO CST_ITEM_COST_DETAILS (
914 INVENTORY_ITEM_ID,
915 COST_TYPE_ID,
916 LAST_UPDATE_DATE,
917 LAST_UPDATED_BY,
918 CREATION_DATE,
919 CREATED_BY,
920 LAST_UPDATE_LOGIN,
921 ORGANIZATION_ID,
922 SOURCE_ORGANIZATION_ID,
923 OPERATION_SEQUENCE_ID,
924 OPERATION_SEQ_NUM,
925 DEPARTMENT_ID,
926 LEVEL_TYPE,
927 ACTIVITY_ID,
928 RESOURCE_SEQ_NUM,
929 RESOURCE_ID,
930 RESOURCE_RATE,
931 USAGE_RATE_OR_AMOUNT,
932 BASIS_TYPE,
933 BASIS_FACTOR,
934 NET_YIELD_OR_SHRINKAGE_FACTOR,
935 ITEM_COST,
936 COST_ELEMENT_ID,
937 ROLLUP_SOURCE_TYPE,
938 REQUEST_ID,
939 PROGRAM_APPLICATION_ID,
940 PROGRAM_ID,
941 PROGRAM_UPDATE_DATE)
942 SELECT /*+ ORDERED INDEX(br BOM_RESOURCES_U2)*/
943 bor.ASSEMBLY_ITEM_ID,
944 l_dest_cost_type_id,
945 l_rollup_date,
946 l_last_updated_by,
947 l_rollup_date,
948 l_last_updated_by,
949 l_login_id,
950 csllc.organization_id,
951 csllc.organization_id,
952 bos.OPERATION_SEQUENCE_ID,
953 bos.OPERATION_SEQ_NUM,
954 bos.DEPARTMENT_ID,
955 CM_THIS_LEVEL,
956 bomres.ACTIVITY_ID,
957 bomres.RESOURCE_SEQ_NUM,
958 bomres.RESOURCE_ID,
959 DECODE(br.FUNCTIONAL_CURRENCY_FLAG,
960 1,1,NVL(crc.RESOURCE_RATE,0)),
961 bomres.USAGE_RATE_OR_AMOUNT,
962 bomres.BASIS_TYPE,
963 DECODE(bomres.BASIS_TYPE,1,1,2,
964 1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),1),
965 DECODE(item_shrinkage_flag,1,
966 DECODE(bomres.BASIS_TYPE,4,1,5,1,6,1,
967 1/(1-NVL(cia.SHRINKAGE_RATE,0))),1),
968 ROUND((DECODE(br.FUNCTIONAL_CURRENCY_FLAG,
969 1,1,
970 NVL(crc.RESOURCE_RATE,0)) *
971 bomres.USAGE_RATE_OR_AMOUNT *
972 DECODE(bomres.BASIS_TYPE,1,1,2,
976 DECODE(bomres.BASIS_TYPE,4,1,5,1,6,1,
973 1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),
974 1) *
975 DECODE(item_shrinkage_flag,1,
977 1/(1-NVL(cia.SHRINKAGE_RATE,0))),1)),
978 csllc.ext_precision),
979 br.COST_ELEMENT_ID, /* Resource cost element */
980 3, /* Rolled up */
981 DECODE(conc_flag, 1, req_id, NULL),
982 DECODE(conc_flag, 1, prgm_appl_id, NULL),
983 DECODE(conc_flag, 1, prgm_id, NULL),
984 DECODE(conc_flag, 1,
985 l_rollup_date, NULL)
986 FROM
987 CST_SC_LOW_LEVEL_CODES csllc,
988 MTL_PARAMETERS mp,
989 CST_ITEM_COSTS cia,
990 BOM_OPERATIONAL_ROUTINGS bor,
991 BOM_OPERATION_SEQUENCES bos,
992 BOM_OPERATION_RESOURCES bomres,
993 BOM_RESOURCES br,
994 CST_RESOURCE_COSTS crc
995 WHERE csllc.ROLLUP_ID = l_rollup_id
996 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
997 AND cia.ORGANIZATION_ID = csllc.organization_id
998 AND cia.COST_TYPE_ID = l_dest_cost_type_id
999 AND cia.BASED_ON_ROLLUP_FLAG = 1 /* Yes */
1000 AND cia.INVENTORY_ASSET_FLAG = 1
1001 AND bor.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
1002 AND bor.ORGANIZATION_ID = cia.organization_id
1003 AND ((l_mfg_flag = 1
1004 AND
1005 bor.ROUTING_TYPE = 1)
1006 OR
1007 (l_mfg_flag = 2)
1008 )
1009 AND( NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none')
1010 =NVL(alt_rtg_designator, 'none')
1011 OR (
1012 (alt_rtg_designator IS NOT NULL)
1013 AND (bor.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
1014 AND NOT EXISTS
1015 (SELECT 'X'
1016 FROM BOM_OPERATIONAL_ROUTINGS bor1
1017 WHERE bor1.ASSEMBLY_ITEM_ID = bor.ASSEMBLY_ITEM_ID
1018 AND bor1.ORGANIZATION_ID = csllc.organization_id
1019 AND bor1.ALTERNATE_ROUTING_DESIGNATOR =
1020 alt_rtg_designator
1021 AND ((l_mfg_flag = 1
1022 AND
1023 bor1.ROUTING_TYPE = 1)
1024 OR
1025 (l_mfg_flag = 2)
1026 )
1027 )
1028 )
1029 )
1030 AND bos.ROUTING_SEQUENCE_ID = bor.COMMON_ROUTING_SEQUENCE_ID
1031
1032 /* Fix for BUG 1608765 */
1033 AND bos.EFFECTIVITY_DATE <= l_rev_datetime
1034 AND NVL( bos.DISABLE_DATE,
1035 l_rev_datetime + 1)
1036 >= l_rev_datetime /*Changed > to >= for bug 6389605*/
1037
1038 /* Right now, ECO does not support Op Yield */
1039 AND ( NVL( bos.include_in_rollup, 1 ) = 1 OR
1040 bos.change_notice is not null )
1041
1042 /* This section takes care of Unimplemented ECO Routings */
1043 AND (
1044 (
1045 unimp_flag = 2 AND
1046 bos.implementation_date is not null
1047 )
1048 OR
1049 (
1050 unimp_flag = 1 AND
1051 bos.effectivity_date =
1052 (
1053 select max( bos2.effectivity_date )
1054 from bom_operation_sequences bos2
1055 where bos2.routing_sequence_id = bos.routing_sequence_id
1056 and NVL( bos2.operation_type, 1 ) = NVL( bos.operation_type, 1 )
1057 and bos2.operation_seq_num = bos.operation_seq_num
1058
1059 /* Fix for BUG 1607662 */
1060 and bos2.EFFECTIVITY_DATE <=
1061 fnd_date.canonical_to_date( revision_date )
1062 )
1063 )
1064 )
1065
1066 AND NVL( bos.eco_for_production, 2 ) = 2
1067 AND bomres.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
1068 AND NVL( bomres.acd_type, 1 ) <> 3
1069 AND br.RESOURCE_ID = bomres.RESOURCE_ID
1070 AND br.ORGANIZATION_ID = csllc.organization_id
1071 AND br.ALLOW_COSTS_FLAG = 1
1072 AND crc.RESOURCE_ID = bomres.RESOURCE_ID
1073 AND (
1074 crc.COST_TYPE_ID = l_dest_cost_type_id
1075 OR
1076 ( crc.COST_TYPE_ID = default_cost_type_id
1077 AND NOT EXISTS (SELECT 'X'
1078 FROM CST_RESOURCE_COSTS crc1
1079 WHERE crc1.RESOURCE_ID = bomres.RESOURCE_ID
1080 AND crc1.COST_TYPE_ID = l_dest_cost_type_id)
1081 )
1082 OR
1083 ( crc.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1087 AND crc2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
1084 AND NOT EXISTS (SELECT 'X'
1085 FROM CST_RESOURCE_COSTS crc2
1086 WHERE crc2.RESOURCE_ID = bomres.RESOURCE_ID
1088 )
1089 ) /* Supply chain enhancement: default valuation cost type */
1090 AND mp.ORGANIZATION_ID = csllc.organization_id;
1091
1092 sql_stmt_num := 65;
1093
1094 --======================================================================================
1095 -- Added the following Insert Statement as part of fix for Bug# 3967455 (foreport bug 4032733,4283663)
1096
1097 -- to Insert records into CICD for which Resource rates are not defined (in CST_RESOURCE_COSTS)
1098 -- but have OverHeads defined with Basis Type = 3 (i.e. Resource Unit based) only
1099 -- since for such Basis Type, the user need not assign any Resource Rate.
1100 -- This is added as a separate statement so as to avoid using OUTER Joins in the above Insert
1101 -- Statement (sql_stmt_num 60).
1102
1103 --NOTE : Any changes done to the Above Insert statement need to be incorporated into this
1104 -- Statement as well since they are for similar purpose and are majorly similar.
1105 --======================================================================================
1106 INSERT INTO CST_ITEM_COST_DETAILS (
1107 inventory_item_id,
1108 cost_type_id,
1109 last_update_date,
1110 last_updated_by,
1111 creation_date,
1112 created_by,
1113 last_update_login,
1114 organization_id,
1115 source_organization_id,
1116 operation_sequence_id,
1117 operation_seq_num,
1118 department_id,
1119 level_type,
1120 activity_id,
1121 resource_seq_num,
1122 resource_id,
1123 resource_rate,
1124 usage_rate_or_amount,
1125 basis_type,
1126 basis_factor,
1127 net_yield_or_shrinkage_factor,
1128 item_cost,
1129 cost_element_id,
1130 rollup_source_type,
1131 request_id,
1132 program_application_id,
1133 program_id,
1134 program_update_date)
1135 SELECT /*+ ORDERED INDEX(br BOM_RESOURCES_U2)*/
1136 bor.assembly_item_id,
1137 l_dest_cost_type_id,
1138 l_rollup_date,
1139 l_last_updated_by,
1140 l_rollup_date,
1141 l_last_updated_by,
1142 l_login_id,
1143 csllc.organization_id,
1144 csllc.organization_id,
1145 bos.operation_sequence_id,
1146 bos.operation_seq_num,
1147 bos.department_id,
1148 cm_this_level,
1149 bomres.activity_id,
1150 bomres.resource_seq_num,
1151 bomres.resource_id,
1152 0,
1153 bomres.usage_rate_or_amount,
1154 bomres.basis_type,
1155 DECODE(bomres.BASIS_TYPE,1,1,2, 1/DECODE(cia.lot_size,NULL,1,0,1,cia.lot_size),1),
1156 DECODE(item_shrinkage_flag,1,
1157 DECODE(bomres.basis_type,4,1,5,1,6,1,
1158 1/(1-NVL(cia.shrinkage_rate,0))),1),
1159 0,
1160 br.cost_element_id, /* resource cost element */
1161 3, /* Rolled up */
1162 DECODE(conc_flag, 1, req_id, NULL),
1163 DECODE(conc_flag, 1, prgm_appl_id, NULL),
1164 -100,
1165 DECODE(conc_flag, 1, l_rollup_date, NULL)
1166 FROM
1167 CST_SC_LOW_LEVEL_CODES csllc,
1168 MTL_PARAMETERS mp,
1169 CST_ITEM_COSTS cia,
1170 BOM_OPERATIONAL_ROUTINGS bor,
1171 BOM_OPERATION_SEQUENCES bos,
1172 BOM_OPERATION_RESOURCES bomres,
1173 BOM_RESOURCES br
1174 WHERE csllc.rollup_id = l_rollup_id
1175 AND cia.inventory_item_id = csllc.inventory_item_id
1176 AND cia.organization_id = csllc.organization_id
1177 AND cia.cost_type_id = l_dest_cost_type_id
1178 AND cia.based_on_rollup_flag = 1 /* yes */
1179 AND cia.inventory_asset_flag = 1
1180 AND bor.assembly_item_id = cia.inventory_item_id
1181 AND bor.organization_id = cia.organization_id
1182 AND ((l_mfg_flag = 1 AND bor.ROUTING_TYPE = 1) OR (l_mfg_flag = 2))
1186 AND (bor.alternate_routing_designator IS NULL)
1183 AND( NVL(bor.alternate_routing_designator, 'none') = NVL(alt_rtg_designator, 'none')
1184 OR (
1185 alt_rtg_designator IS NOT NULL
1187 AND NOT EXISTS
1188 (SELECT 'X'
1189 FROM bom_operational_routings bor1
1190 WHERE bor1.assembly_item_id = bor.assembly_item_id
1191 AND bor1.organization_id = csllc.organization_id
1192 AND bor1.alternate_routing_designator = alt_rtg_designator
1193 AND ((l_mfg_flag = 1 AND bor1.routing_type = 1) OR (l_mfg_flag = 2))
1194 )
1195 )
1196 )
1197 AND bos.routing_sequence_id = bor.common_routing_sequence_id
1198
1199 /* Fix for BUG 1608765 */
1200 AND bos.effectivity_date <= l_rev_datetime
1201 AND NVL( bos.disable_date, l_rev_datetime + 1) >= l_rev_datetime /*Changed > to >= for bug 6389605*/
1202
1203 /* Right now, ECO does not support Op Yield */
1204 AND ( NVL( bos.include_in_rollup, 1 ) = 1 OR bos.change_notice is not null )
1205
1206 /* This section takes care of Unimplemented ECO Routings */
1207 AND (
1208 (unimp_flag = 2 AND bos.implementation_date is not null)
1209 OR
1210 (unimp_flag = 1 AND bos.effectivity_date =
1211 (
1212 SELECT MAX( bos2.effectivity_date )
1213 FROM bom_operation_sequences bos2
1214 WHERE bos2.routing_sequence_id = bos.routing_sequence_id
1215 AND NVL( bos2.operation_type, 1 ) = NVL( bos.operation_type, 1 )
1216 AND bos2.operation_seq_num = bos.operation_seq_num
1217
1218 /* Fix for BUG 1607662 */
1219 AND bos2.effectivity_date <= fnd_date.canonical_to_date(revision_date)
1220 )
1221 )
1222 )
1223 AND NVL( bos.eco_for_production, 2 ) = 2
1224 AND bomres.operation_sequence_id = bos.operation_sequence_id
1225 AND NVL( bomres.acd_type, 1 ) <> 3
1226 AND br.RESOURCE_ID = bomres.RESOURCE_ID
1227 AND br.ORGANIZATION_ID = csllc.organization_id
1228 AND br.ALLOW_COSTS_FLAG = 1
1229
1230 -- To Exclude resource records that have rates defined
1231 AND NOT EXISTS (SELECT 'resource rate defined'
1232 FROM cst_resource_costs crc
1233 WHERE crc.resource_id = bomres.resource_id
1234 AND (crc.cost_type_id = l_dest_cost_type_id
1235 OR
1236 crc.COST_TYPE_ID = default_cost_type_id
1237 OR
1238 /* Supply chain enhancement: default valuation cost type */
1239 crc.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1240 )
1241 )
1242 -- To get Overhead records of Basis Type = 3 (i.e Resource Unit based OH) only
1243 AND EXISTS (SELECT 'overhead exists for resource rate not defined'
1244 FROM cst_resource_overheads cro,
1245 cst_department_overheads cdo
1246 WHERE cro.resource_id = bomres.resource_id
1247 AND (cro.cost_type_id = l_dest_cost_type_id
1248 OR
1249 cro.cost_type_id = default_cost_type_id
1250 OR
1251 cro.COST_TYPE_ID = DECODE(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1252 )
1253 AND cdo.department_id = bos.department_id
1254 AND cdo.overhead_id = cro.overhead_id
1255 AND cdo.basis_type = 3 --only for resource unit based OH
1256 AND cdo.rate_or_amount <> 0
1257 AND (
1258 cdo.cost_type_id = l_dest_cost_type_id
1259 OR
1260 cdo.cost_type_id = default_cost_type_id
1261 OR
1262 cdo.cost_type_id = DECODE(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id)
1263 )
1264 )
1265 AND mp.organization_id = csllc.organization_id;
1266
1267
1268 sql_stmt_num := 70;
1269 /*------------------------------------------------------------+
1270 | Calculate the this level overheads based on resources. |
1271 +------------------------------------------------------------*/
1272 INSERT INTO CST_ITEM_COST_DETAILS (
1273 INVENTORY_ITEM_ID,
1277 CREATION_DATE,
1274 COST_TYPE_ID,
1275 LAST_UPDATE_DATE,
1276 LAST_UPDATED_BY,
1278 CREATED_BY,
1279 LAST_UPDATE_LOGIN,
1280 SOURCE_ORGANIZATION_ID,
1281 ORGANIZATION_ID,
1282 OPERATION_SEQUENCE_ID,
1283 OPERATION_SEQ_NUM,
1284 DEPARTMENT_ID,
1285 LEVEL_TYPE,
1286 ACTIVITY_ID,
1287 RESOURCE_SEQ_NUM,
1288 RESOURCE_ID,
1289 RESOURCE_RATE,
1290 USAGE_RATE_OR_AMOUNT,
1291 BASIS_TYPE,
1292 BASIS_RESOURCE_ID,
1293 BASIS_FACTOR,
1294 NET_YIELD_OR_SHRINKAGE_FACTOR,
1295 ITEM_COST,
1296 COST_ELEMENT_ID,
1297 ROLLUP_SOURCE_TYPE,
1298 REQUEST_ID,
1299 PROGRAM_APPLICATION_ID,
1300 PROGRAM_ID,
1301 PROGRAM_UPDATE_DATE)
1302 SELECT cicd.INVENTORY_ITEM_ID,
1303 l_dest_cost_type_id,
1304 l_rollup_date,
1305 l_last_updated_by,
1306 l_rollup_date,
1307 l_last_updated_by,
1308 l_login_id,
1309 csllc.organization_id,
1310 csllc.organization_id,
1311 cicd.OPERATION_SEQUENCE_ID,
1312 cicd.OPERATION_SEQ_NUM,
1313 cicd.DEPARTMENT_ID,
1314 CM_THIS_LEVEL,
1315 cdo.ACTIVITY_ID,
1316 cicd.RESOURCE_SEQ_NUM,
1317 cdo.OVERHEAD_ID,
1318 NULL,
1319 cdo.RATE_OR_AMOUNT,
1320 cdo.BASIS_TYPE,
1321 cro.RESOURCE_ID,
1322 /* Modified for bug 6821381 */
1323 DECODE(cdo.BASIS_TYPE,
1324 3, DECODE(cicd.USAGE_RATE_OR_AMOUNT * cicd.BASIS_FACTOR,
1325 0, 1,
1326 cicd.USAGE_RATE_OR_AMOUNT * cicd.BASIS_FACTOR),
1327 4, DECODE(cicd.ITEM_COST, 0, 1, cicd.ITEM_COST)),
1328 DECODE(cdo.BASIS_TYPE,3,
1329 DECODE(item_shrinkage_flag,1,
1330 1/(1-NVL(cia.SHRINKAGE_RATE,0)),1),1),
1331 ROUND((cdo.RATE_OR_AMOUNT *
1332 DECODE(cdo.BASIS_TYPE,
1333 3, cicd.USAGE_RATE_OR_AMOUNT * cicd.BASIS_FACTOR,
1334 4, cicd.ITEM_COST) *
1335 DECODE(cdo.BASIS_TYPE,3,
1336 DECODE(item_shrinkage_flag,1,
1337 1/(1-NVL(cia.SHRINKAGE_RATE,0)),1),1)),
1338 csllc.ext_precision),
1339 5, /* Overhead cost element */
1340 3, /* Rolled up */
1341 DECODE(conc_flag, 1, req_id, NULL),
1342 DECODE(conc_flag, 1, prgm_appl_id, NULL),
1343 DECODE(conc_flag, 1, prgm_id, NULL),
1344 DECODE(conc_flag, 1,
1345 l_rollup_date, NULL)
1346 FROM
1347 CST_SC_LOW_LEVEL_CODES csllc,
1348 CST_ITEM_COSTS cia,
1349 CST_ITEM_COST_DETAILS cicd,
1350 CST_RESOURCE_OVERHEADS cro,
1351 CST_DEPARTMENT_OVERHEADS cdo,
1352 MTL_PARAMETERS mp
1353 WHERE csllc.ROLLUP_ID = l_rollup_id
1354 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
1355 AND cia.ORGANIZATION_ID = csllc.organization_id
1356 AND cia.COST_TYPE_ID = l_dest_cost_type_id
1357 AND cia.BASED_ON_ROLLUP_FLAG = 1 /* YES */
1358 AND cia.INVENTORY_ASSET_FLAG = 1
1359 AND cicd.ORGANIZATION_ID = csllc.organization_id
1360 AND cicd.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
1361 AND cicd.COST_TYPE_ID = l_dest_cost_type_id
1362 AND cicd.RESOURCE_ID = cro.RESOURCE_ID
1363 AND (
1364 cro.COST_TYPE_ID = l_dest_cost_type_id
1365 OR
1366 ( cro.COST_TYPE_ID = default_cost_type_id
1367 AND NOT EXISTS (SELECT 'X'
1368 FROM CST_RESOURCE_OVERHEADS cro1
1369 WHERE cro1.RESOURCE_ID = cicd.RESOURCE_ID
1370 AND cro1.COST_TYPE_ID = l_dest_cost_type_id)
1371 )
1372 OR
1373 ( cro.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1374 AND NOT EXISTS (SELECT 'X'
1378 )
1375 FROM CST_RESOURCE_OVERHEADS cro2
1376 WHERE cro2.RESOURCE_ID = cicd.RESOURCE_ID
1377 AND cro2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
1379 ) /* Supply chain enhancement: default valuation cost type */
1380 AND cro.COST_TYPE_ID IN (l_dest_cost_type_id,default_cost_type_id,decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)) -- Added for 5678464
1381 AND cdo.COST_TYPE_ID IN (l_dest_cost_type_id,default_cost_type_id,decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)) -- Added for 5678464
1382 AND mp.ORGANIZATION_ID = csllc.organization_id
1383 AND cdo.DEPARTMENT_ID = cicd.DEPARTMENT_ID
1384 AND cdo.OVERHEAD_ID = cro.OVERHEAD_ID
1385 AND cdo.BASIS_TYPE IN (3,4)
1386 AND cdo.RATE_OR_AMOUNT <> 0
1387 AND (
1388 cdo.COST_TYPE_ID = l_dest_cost_type_id
1389 OR
1390 ( cdo.COST_TYPE_ID = default_cost_type_id
1391 AND NOT EXISTS
1392 (SELECT 'X'
1393 FROM CST_DEPARTMENT_OVERHEADS cdo1
1394 WHERE cdo1.DEPARTMENT_ID = cicd.DEPARTMENT_ID
1395 AND cdo1.COST_TYPE_ID = l_dest_cost_type_id)
1396 )
1397 OR
1398 ( cdo.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1399 AND NOT EXISTS
1400 (SELECT 'X'
1401 FROM CST_DEPARTMENT_OVERHEADS cdo2
1402 WHERE cdo2.DEPARTMENT_ID = cicd.DEPARTMENT_ID
1403 AND cdo2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
1404 )
1405 ); /* Supply chain enhancement: default valuation cost type */
1406
1407
1408 sql_stmt_num := 80;
1409 /*------------------------------------------------------------+
1410 | Calculate the this level departmental overhead charges. |
1411 +------------------------------------------------------------*/
1412 INSERT INTO CST_ITEM_COST_DETAILS (
1413 INVENTORY_ITEM_ID,
1414 COST_TYPE_ID,
1415 LAST_UPDATE_DATE,
1416 LAST_UPDATED_BY,
1417 CREATION_DATE,
1418 CREATED_BY,
1419 LAST_UPDATE_LOGIN,
1420 SOURCE_ORGANIZATION_ID,
1421 ORGANIZATION_ID,
1422 OPERATION_SEQUENCE_ID,
1423 OPERATION_SEQ_NUM,
1424 DEPARTMENT_ID,
1425 LEVEL_TYPE,
1426 ACTIVITY_ID,
1427 RESOURCE_SEQ_NUM,
1428 RESOURCE_ID,
1429 RESOURCE_RATE,
1430 USAGE_RATE_OR_AMOUNT,
1431 BASIS_TYPE,
1432 BASIS_FACTOR,
1433 NET_YIELD_OR_SHRINKAGE_FACTOR,
1434 ITEM_COST,
1435 COST_ELEMENT_ID,
1436 ROLLUP_SOURCE_TYPE,
1437 REQUEST_ID,
1438 PROGRAM_APPLICATION_ID,
1439 PROGRAM_ID,
1440 PROGRAM_UPDATE_DATE)
1441 SELECT
1442 bor.ASSEMBLY_ITEM_ID,
1443 l_dest_cost_type_id,
1444 l_rollup_date,
1445 l_last_updated_by,
1446 l_rollup_date,
1447 l_last_updated_by,
1448 l_login_id,
1449 csllc.organization_id,
1450 csllc.organization_id,
1451 bos.OPERATION_SEQUENCE_ID,
1452 bos.OPERATION_SEQ_NUM,
1453 bos.DEPARTMENT_ID,
1454 CM_THIS_LEVEL,
1455 cdo.ACTIVITY_ID,
1456 NULL,
1457 cdo.OVERHEAD_ID,
1458 NULL,
1459 cdo.RATE_OR_AMOUNT,
1460 cdo.BASIS_TYPE,
1461 DECODE(cdo.BASIS_TYPE,1,1,2,
1462 1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),0),
1463 DECODE(item_shrinkage_flag,1,
1464 DECODE(cdo.BASIS_TYPE,4,1,5,1,6,1,
1465 1/(1-NVL(cia.SHRINKAGE_RATE,0))),1),
1466 ROUND((cdo.RATE_OR_AMOUNT *
1467 DECODE(cdo.BASIS_TYPE,1,1,2,
1468 1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),0) *
1469 DECODE(item_shrinkage_flag,1,
1470 DECODE(cdo.BASIS_TYPE,4,1,5,1,6,1,
1471 1/(1-NVL(cia.SHRINKAGE_RATE,0))),1)),
1472 csllc.ext_precision),
1473 5, /* overhead cost element */
1474 3, /* Rolled up */
1475 DECODE(conc_flag, 1, req_id, NULL),
1476 DECODE(conc_flag, 1, prgm_appl_id, NULL),
1477 DECODE(conc_flag, 1, prgm_id, NULL),
1481 CST_SC_LOW_LEVEL_CODES csllc,
1478 DECODE(conc_flag, 1,
1479 l_rollup_date, NULL)
1480 FROM
1482 CST_ITEM_COSTS cia,
1483 BOM_OPERATIONAL_ROUTINGS bor,
1484 BOM_OPERATION_SEQUENCES bos,
1485 CST_DEPARTMENT_OVERHEADS cdo,
1486 MTL_PARAMETERS mp
1487 WHERE csllc.ROLLUP_ID = l_rollup_id
1488 AND cia.BASED_ON_ROLLUP_FLAG (+) = 1 /* YES */
1489 AND cia.INVENTORY_ASSET_FLAG (+) = 1
1490 AND cia.INVENTORY_ITEM_ID (+) = csllc.INVENTORY_ITEM_ID
1491 AND cia.ORGANIZATION_ID (+) = csllc.organization_id
1492 AND cia.COST_TYPE_ID (+) = l_dest_cost_type_id
1493 AND cia.INVENTORY_ITEM_ID is not null
1494 AND bor.ASSEMBLY_ITEM_ID = csllc.INVENTORY_ITEM_ID
1495 AND bor.ORGANIZATION_ID = csllc.organization_id
1496 AND ((l_mfg_flag = 1
1497 AND
1498 bor.ROUTING_TYPE = 1)
1499 OR
1500 (l_mfg_flag = 2)
1501 )
1502 AND( NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none')
1503 =NVL(alt_rtg_designator, 'none')
1504 OR (
1505 (alt_rtg_designator IS NOT NULL)
1506 AND (bor.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
1507 AND NOT EXISTS
1508 (SELECT
1509 'X'
1510 FROM BOM_OPERATIONAL_ROUTINGS bor1
1511 WHERE bor1.ASSEMBLY_ITEM_ID = bor.ASSEMBLY_ITEM_ID
1512 AND bor1.ORGANIZATION_ID = csllc.organization_id
1513 AND bor1.ALTERNATE_ROUTING_DESIGNATOR =
1514 alt_rtg_designator
1515 AND ((l_mfg_flag = 1
1516 AND
1517 bor1.ROUTING_TYPE = 1)
1518 OR
1519 (l_mfg_flag = 2)
1520 )
1521 )
1522 )
1523 )
1524 AND bos.ROUTING_SEQUENCE_ID = bor.COMMON_ROUTING_SEQUENCE_ID
1525
1526 /* Fix for BUG 1608765 */
1527 AND bos.EFFECTIVITY_DATE <= l_rev_datetime
1528 AND NVL( bos.DISABLE_DATE,
1529 l_rev_datetime + 1)
1530 >= l_rev_datetime /*Changed > to >= for bug 6389605*/
1531
1532 /* Right now, ECO does not support Op Yield */
1533 AND ( NVL( bos.include_in_rollup, 1 ) = 1 OR
1534 bos.change_notice is not null )
1535
1536 /* This section takes care of Unimplemented ECO Routings */
1537 AND (
1538 (
1539 unimp_flag = 2 AND
1540 bos.implementation_date is not null
1541 )
1542 OR
1543 (
1544 unimp_flag = 1 AND
1545 bos.effectivity_date =
1546 (
1547 select max( bos2.effectivity_date )
1548 from bom_operation_sequences bos2
1549 where bos2.routing_sequence_id = bos.routing_sequence_id
1550 and NVL( bos2.operation_type, 1 ) = NVL( bos.operation_type, 1 )
1551 and bos2.operation_seq_num = bos.operation_seq_num
1552
1553 /* Fix for BUG 1607662 */
1554 and bos2.EFFECTIVITY_DATE <=
1555 fnd_date.canonical_to_date( revision_date )
1556 )
1557 )
1558 )
1559
1560
1561 AND NVL( bos.eco_for_production, 2 ) = 2
1562 AND cdo.DEPARTMENT_ID = bos.DEPARTMENT_ID
1563 AND cdo.BASIS_TYPE IN (1,2)
1564 AND cdo.RATE_OR_AMOUNT <> 0
1565 AND (
1566 cdo.COST_TYPE_ID = l_dest_cost_type_id
1567 OR
1568 ( cdo.COST_TYPE_ID = default_cost_type_id
1569 AND NOT EXISTS
1570 (SELECT 'X'
1571 FROM CST_DEPARTMENT_OVERHEADS cdo1
1572 WHERE cdo1.DEPARTMENT_ID = cdo.DEPARTMENT_ID
1573 AND cdo1.COST_TYPE_ID = l_dest_cost_type_id)
1574 )
1575 OR
1576 ( cdo.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1577 AND NOT EXISTS
1578 (SELECT 'X'
1579 FROM CST_DEPARTMENT_OVERHEADS cdo2
1580 WHERE cdo2.DEPARTMENT_ID = cdo.DEPARTMENT_ID
1581 AND cdo2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
1582 )
1583 ) /* Supply chain enhancement: default valuation cost type */
1584 AND cdo.COST_TYPE_ID IN (l_dest_cost_type_id, default_cost_type_id, decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)) --Added for 5678464
1585 AND mp.ORGANIZATION_ID = csllc.organization_id
1586 /* Fix for bug 2142170 */
1587 /* -----------------------------------------------------------+
1588 | If the routing is a flow routing, then the operation type |
1589 | should be an event. |
1590 +-------------------------------------------------------------*/
1594
1591 AND ((nvl(bor.cfm_routing_flag, 2) <> 1)
1592 OR (nvl(bor.cfm_routing_flag, 2) = 1 and nvl(bos.operation_type, 1) = 1))
1593 ;
1595 sql_stmt_num := 90;
1596 /*------------------------------------------------------------+
1597 | Calculate this level material overhead with basis of |
1598 | resource units and resource value and applied to this |
1599 | level associated resources. |
1600 +------------------------------------------------------------*/
1601 OPEN cllc1_cur;
1602 LOOP
1603 FETCH cllc1_cur BULK COLLECT INTO
1604 l_inv_item_tbl,
1605 l_org_id_tbl,
1606 l_roun_tbl,
1607 l_prec_tbl,
1608 l_ext_tbl
1609 LIMIT 1000;
1610
1611 FORALL i IN l_inv_item_tbl.first..l_inv_item_tbl.last
1612 UPDATE CST_ITEM_COST_DETAILS cicd
1613 SET (BASIS_FACTOR,
1614 ITEM_COST) =
1615 (SELECT DECODE(cicd.BASIS_TYPE,
1616 3,NVL(SUM(ROUND(cicd1.USAGE_RATE_OR_AMOUNT*
1617 cicd1.BASIS_FACTOR,
1618 l_ext_tbl(i))),0),
1619 4,NVL(SUM(cicd1.ITEM_COST),0)),
1620 DECODE(cicd.BASIS_TYPE,
1621 3,ROUND(cicd.USAGE_RATE_OR_AMOUNT*
1622 cicd.NET_YIELD_OR_SHRINKAGE_FACTOR *
1623 NVL(SUM(ROUND(cicd1.USAGE_RATE_OR_AMOUNT*
1624 cicd1.BASIS_FACTOR,
1625 l_ext_tbl(i))),0),
1626 l_ext_tbl(i)),
1627 4,ROUND(cicd.USAGE_RATE_OR_AMOUNT*
1628 NVL(SUM(cicd1.ITEM_COST),0),l_ext_tbl(i)))
1629 FROM
1630 CST_ITEM_COST_DETAILS cicd1,
1631 CST_RESOURCE_OVERHEADS cro
1632 WHERE cicd1.INVENTORY_ITEM_ID = cicd.INVENTORY_ITEM_ID
1633 AND cicd1.ORGANIZATION_ID = l_org_id_tbl(i)
1634 AND cicd1.COST_TYPE_ID = l_dest_cost_type_id
1635 AND cicd1.RESOURCE_ID = cro.RESOURCE_ID
1636 AND cicd1.LEVEL_TYPE = CM_THIS_LEVEL
1637 AND cicd1.COST_ELEMENT_ID in (3,4)
1638 AND cro.OVERHEAD_ID = cicd.RESOURCE_ID
1639 AND cro.COST_TYPE_ID = l_dest_cost_type_id)
1640 WHERE cicd.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
1641 AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
1642 AND cicd.ROLLUP_SOURCE_TYPE = 1 /* user entered */
1643 AND cicd.COST_ELEMENT_ID = 2 /* material overhead */
1644 AND cicd.BASIS_TYPE in (3,4) /* resource units, value */
1645 AND cicd.COST_TYPE_ID = l_dest_cost_type_id;
1646
1647 EXIT WHEN cllc1_cur%NOTFOUND;
1648 END LOOP;
1649
1650 CLOSE cllc1_cur;
1651
1652 /*-----------------------------------------------------------+
1653 |Snapshot The BOM's if the cost type option demands so |
1654 +-----------------------------------------------------------*/
1655 sql_stmt_num :=95;
1656 if (report_option<>CM_REPORT_ONLY) AND (bom_snapshot_flag = 1) THEN
1657 return_code := supply_chain_snapshot(
1658 l_rollup_id,
1659 l_dest_cost_type_id,
1660 l_mfg_flag,
1661 alt_bom_designator,
1662 conc_flag,
1663 unimp_flag,
1664 revision_date,
1665 l_last_updated_by,
1666 rollup_date,
1667 req_id,
1668 prgm_appl_id,
1669 prgm_id,
1670 err_buf);
1671 if return_code <> 0 then
1672 raise snapshot_error;
1673 end if;
1674 end if;
1675
1676 sql_stmt_num := 100;
1677
1678 /*------------------------------------------------------------+
1679 | Determine the maximum level code in the rollup. |
1680 +------------------------------------------------------------*/
1681 SELECT NVL(MAX(LOW_LEVEL_CODE),0)
1682 INTO max_level
1683 FROM CST_SC_LOW_LEVEL_CODES
1684 WHERE ROLLUP_ID = l_rollup_id;
1685
1686 /*------------------------------------------------------------+
1687 | Determine the minimum level code in the rollup. |
1688 +------------------------------------------------------------*/
1689 min_level := 0; /* Always */
1690
1691
1692 sql_stmt_num := 110;
1693 /*------------------------------------------------------------+
1694 | Calculate the previous level costs for the assemblies |
1695 | level by level. |
1696 +------------------------------------------------------------*/
1697
1698 <<calc>>
1699 for cur_level in min_level..max_level loop
1700
1701 -- Bug 3590153: removed the join with cst_sc_low_level_codes
1702 -- and added the cursor for loop as below
1703
1704 OPEN cllc10_cur(cur_level);
1705 LOOP
1706 FETCH cllc10_cur BULK COLLECT INTO
1707 l_inv_item_tbl,
1708 l_org_id_tbl,
1709 l_roun_tbl,
1710 l_prec_tbl,
1711 l_ext_tbl,
1712 l_pcm_tbl
1713 LIMIT 1000;
1714
1715 sql_stmt_num := 112;
1716
1717 FORALL i IN l_inv_item_tbl.first..l_inv_item_tbl.last
1721 LAST_UPDATE_DATE,
1718 INSERT INTO cst_item_cost_details
1719 (INVENTORY_ITEM_ID,
1720 COST_TYPE_ID,
1722 LAST_UPDATED_BY,
1723 CREATION_DATE,
1724 CREATED_BY,
1725 LAST_UPDATE_LOGIN,
1726 SOURCE_ORGANIZATION_ID,
1727 ORGANIZATION_ID,
1728 OPERATION_SEQ_NUM,
1729 LEVEL_TYPE,
1730 ACTIVITY_ID,
1731 RESOURCE_ID,
1732 RESOURCE_RATE,
1733 USAGE_RATE_OR_AMOUNT,
1734 BASIS_TYPE,
1735 BASIS_FACTOR,
1736 NET_YIELD_OR_SHRINKAGE_FACTOR,
1737 ITEM_COST,
1738 COST_ELEMENT_ID,
1739 ROLLUP_SOURCE_TYPE,
1740 REQUEST_ID,
1741 PROGRAM_APPLICATION_ID,
1742 PROGRAM_ID,
1743 PROGRAM_UPDATE_DATE,
1744 BASIS_RESOURCE_ID,
1745 OPERATION_SEQUENCE_ID,
1746 RESOURCE_SEQ_NUM
1747 )
1748
1749 SELECT
1750
1751 l_inv_item_tbl(i),
1752 l_dest_cost_type_id,
1753 TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS'),
1754 l_last_updated_by,
1755 TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS'),
1756 l_last_updated_by,
1757 l_login_id,
1758 l_org_id_tbl(i),
1759 l_org_id_tbl(i),
1760 DECODE(pl_operation_flag, 1, max(bic.operation_seq_num), NULL),
1761
1762 -- If subassembly is phantom, and use_phatom_routings
1763 -- for resources, overhead and OSP, should be this level
1764 -- rather than previous level
1765
1766 DECODE(cicd.level_type, CM_THIS_LEVEL,
1767 DECODE(NVL(bp.use_phantom_routings,2), 1,
1768 DECODE(nvl(bic.WIP_SUPPLY_TYPE, Nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1769 DECODE(cicd.cost_element_id,3,CM_THIS_LEVEL,4,CM_THIS_LEVEL,5,CM_THIS_LEVEL,CM_PREVIOUS_LEVEL),
1770 CM_PREVIOUS_LEVEL),
1771 CM_PREVIOUS_LEVEL),
1772 CM_PREVIOUS_LEVEL),
1773
1774 DECODE(pl_activity_flag, 1, max(cicd.ACTIVITY_ID), NULL),
1775 DECODE(pl_cost_code_flag, 1, max(cicd.RESOURCE_ID), NULL),
1776 DECODE(pl_cost_code_flag, 1, max(cicd.RESOURCE_RATE), NULL),
1777 /* Usage Rate or amount Start*/
1778 DECODE(pl_cost_code_flag, 1, SUM(cicd.USAGE_RATE_OR_AMOUNT*
1779 cicd.BASIS_FACTOR*
1780 DECODE(NVL(bp.use_phantom_routings,2), 1,
1781 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1782 DECODE(cicd.cost_element_id,
1783 1, DECODE(cicd.basis_type, 2, bic.COMPONENT_QUANTITY/cicd.basis_factor, bic.COMPONENT_QUANTITY),
1784 2, DECODE(cicd.basis_type, 2, bic.COMPONENT_QUANTITY/cicd.basis_factor, bic.COMPONENT_QUANTITY),
1785 3, DECODE(cicd.basis_type, 2, 1/cicd.basis_factor, bic.COMPONENT_QUANTITY),
1786 4, DECODE(cicd.basis_type, 2, 1/cicd.basis_factor, bic.COMPONENT_QUANTITY),
1787 5, DECODE(cicd.basis_type, 2, 1/cicd.basis_factor,
1788 3, 1/cicd.basis_factor,
1789 4, 1/cicd.basis_factor, bic.COMPONENT_QUANTITY)), bic.COMPONENT_QUANTITY),
1790 bic.COMPONENT_QUANTITY)/
1791 DECODE(comp_yield_flag,1,bic.COMPONENT_YIELD_FACTOR,1)*
1792 NVL(bic.PLANNING_FACTOR / 100,1)*
1793 cicd.NET_YIELD_OR_SHRINKAGE_FACTOR),
1794 SUM(cicd.ITEM_COST*
1795 1/(1-NVL(cia_assy.SHRINKAGE_RATE,0))*
1796 DECODE(NVL(bp.use_phantom_routings,2), 1,
1797 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1798 DECODE(cicd.cost_element_id, 3,
1799 DECODE(cicd.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
1800 4, DECODE(cicd.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
1801 5, DECODE(cicd.basis_type, 2, 1,
1802 3, DECODE(bomres.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
1803 4, DECODE(bomres.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
1804 bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY),
1805 bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY)*
1806 NVL(bic.PLANNING_FACTOR / 100,1)/
1807 DECODE(comp_yield_flag,1,bic.COMPONENT_YIELD_FACTOR,1))), /* Usage Rate or amount END*/
1808
1809 /* Update basis_type if phantom - Bug 2076990*/
1810 /* Start changes for LBM */
1811 DECODE(PL_COST_CODE_FLAG, 1,
1812 DECODE(BP.USE_PHANTOM_ROUTINGS, 1,
1813 DECODE(NVL(BIC.WIP_SUPPLY_TYPE, NVL(msi.WIP_SUPPLY_TYPE,1)),6,
1814 DECODE(CICD.BASIS_TYPE, 2, 2, 3, 3, 4, 4,1),
1815 DECODE(bic.basis_type,2,2,1)),
1816 DECODE(bic.basis_type,2,2,1)),
1817 DECODE(bic.basis_type,2,2,1)),
1818 /*basis_factor changed for LBM project */
1819 DECODE(pl_cost_code_flag, 1,
1820 DECODE(NVL(bp.use_phantom_routings,2), 1,
1821 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1822 DECODE(cicd.cost_element_id,
1823 1, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
1824 2, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
1825 3, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
1826 4, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
1827 5, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE,
1828 3, DECODE(bomres.basis_type,
1829 2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
1830 cicd.basis_factor),
1834 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
1831 4, DECODE(bomres.basis_type,
1832 2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
1833 cicd.basis_factor),1)),
1835 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
1836 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
1837 /* Net Yield or Shrinkage factor not changed */
1838 max(1/(1-NVL(cia_assy.SHRINKAGE_RATE,0))),
1839 /* If subassembly is a phantom which uses a lot based resource,
1840 then number of such subassemblies is not used in the calculation
1841 of the final cost of the assembly due to the lot based resource
1842 - Bug 2076990*/
1843 /* Item Cost calculation changed for LBM */
1844 ROUND((SUM(cicd.ITEM_COST*1/(1-NVL(cia_assy.SHRINKAGE_RATE,0))/
1845 DECODE(comp_yield_flag,1,bic.COMPONENT_YIELD_FACTOR,1)*NVL(bic.PLANNING_FACTOR / 100,1)*
1846 DECODE(NVL(bp.use_phantom_routings,2), 1,
1847 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1848 DECODE(cicd.cost_element_id,
1849 1, DECODE(cicd.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
1850 2, DECODE(cicd.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
1851 3, DECODE(cicd.basis_type, 2, 1/(cia_assy.LOT_SIZE*cicd.basis_factor), bic.COMPONENT_QUANTITY),
1852 4, DECODE(cicd.basis_type, 2, 1/(cia_assy.LOT_SIZE*cicd.basis_factor), bic.COMPONENT_QUANTITY),
1853 5, DECODE(cicd.basis_type,
1854 2, 1/(cia_assy.LOT_SIZE*cicd.basis_factor),
1855 3, DECODE(bomres.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
1856 4, DECODE(bomres.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
1857 bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY),
1858 DECODE(bic.basis_type,2, bic.COMPONENT_QUANTITY/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY)),
1859 DECODE(bic.basis_type,2, bic.COMPONENT_QUANTITY/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY)))) , l_ext_tbl(i)),
1860 /* end changes for LBM */
1861 DECODE(pl_element_flag, 1,max(cicd.COST_ELEMENT_ID),1),
1862 3,
1863 DECODE(conc_flag, 1, req_id, NULL),
1864 DECODE(conc_flag, 1, prgm_appl_id, NULL),
1865 DECODE(conc_flag, 1, prgm_id, NULL),
1866 DECODE(conc_flag, 1, TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS'), NULL),
1867 DECODE(pl_cost_code_flag, 1,
1868 DECODE(NVL(bp.use_phantom_routings,2), 1,
1869 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1870 DECODE(cicd.cost_element_id, 3, cicd.basis_resource_id,
1871 4, cicd.basis_resource_id,
1872 5, cicd.basis_resource_id, null), null), null), null),
1873 DECODE(pl_cost_code_flag, 1,
1874 DECODE(NVL(bp.use_phantom_routings,2), 1,
1875 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1876 DECODE(cicd.cost_element_id, 3, cicd.operation_sequence_id,
1877 4, cicd.operation_sequence_id,
1878 5, cicd.operation_sequence_id, null), null), null), null),
1879 DECODE(pl_cost_code_flag, 1,
1880 DECODE(NVL(bp.use_phantom_routings,2), 1,
1881 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1882 DECODE(cicd.cost_element_id, 3, cicd.resource_seq_num,
1883 4, cicd.resource_seq_num,
1884 5, cicd.resource_seq_num, null), null), null), null)
1885 FROM BOM_BILL_OF_MATERIALS bbom,
1886 BOM_INVENTORY_COMPONENTS bic,
1887 CST_ITEM_COSTS cia_assy,
1888 CST_ITEM_COST_DETAILS cicd,
1889 CST_ITEM_COSTS cia_comp,
1890 BOM_PARAMETERS bp,
1891 mtl_system_items msi,
1892 BOM_OPERATION_RESOURCES bomres
1893
1894 WHERE cia_assy.ORGANIZATION_ID = l_org_id_tbl(i)
1895 AND cia_assy.COST_TYPE_ID = l_dest_cost_type_id
1896 AND cia_assy.BASED_ON_ROLLUP_FLAG = 1
1897 AND cia_assy.INVENTORY_ASSET_FLAG = 1
1898 AND cia_assy.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
1899 AND bbom.ORGANIZATION_ID = l_org_id_tbl(i)
1900 AND bbom.ASSEMBLY_ITEM_ID = l_inv_item_tbl(i)
1901 AND((l_mfg_flag = 1
1902 AND
1903 bbom.ASSEMBLY_TYPE = 1)
1904 OR
1905 (l_mfg_flag = 2)
1906 )
1907 AND((bbom.ALTERNATE_BOM_DESIGNATOR IS NULL
1908 AND
1909 alt_bom_designator IS NULL)
1910 OR
1911 (alt_bom_designator IS NOT NULL
1912 AND
1913 bbom.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator)
1914 OR ((alt_bom_designator IS NOT NULL)
1915 AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL)
1916 AND NOT EXISTS
1917 (SELECT /*+ PUSH_SUBQ */ 'X' -- Added hint for 5678464
1918 FROM BOM_BILL_OF_MATERIALS bbom1
1919 WHERE bbom1.ASSEMBLY_ITEM_ID =
1920 bbom.ASSEMBLY_ITEM_ID
1921 AND bbom1.ORGANIZATION_ID = bbom.ORGANIZATION_ID
1922 AND bbom1.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator
1923 AND((l_mfg_flag = 1
1924 AND
1925 bbom1.ASSEMBLY_TYPE = 1)
1926 OR
1927 (l_mfg_flag = 2)
1928 )
1929 )))
1930 -- Added for 5678464
1934 )
1931 AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL
1932 OR
1933 bbom.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator
1935
1936 AND bic.BILL_SEQUENCE_ID = bbom.COMMON_BILL_SEQUENCE_ID
1937 AND bic.INCLUDE_IN_COST_ROLLUP = 1
1938 AND NVL( bic.eco_for_production, 2 ) = 2
1939
1940 /* Fix for BUG 1604207 */
1941 AND NVL( bic.acd_type, 1 ) <> 3
1942
1943 AND (bic.EFFECTIVITY_DATE <= l_rev_datetime)
1944 AND NVL(bic.DISABLE_DATE, l_rev_datetime+1) > l_rev_datetime
1945 AND (
1946 ( unimp_flag = 2 AND (bic.IMPLEMENTATION_DATE IS NOT NULL))
1947 OR (
1948 unimp_flag = 1
1949 AND bic.EFFECTIVITY_DATE =
1950 (SELECT /*+ PUSH_SUBQ */ MAX(bic1.EFFECTIVITY_DATE) -- Added hint for 5678464
1951 FROM BOM_INVENTORY_COMPONENTS bic1
1952 WHERE bic1.COMPONENT_ITEM_ID = bic.COMPONENT_ITEM_ID
1953 AND NVL( bic1.eco_for_production, 2 ) = 2
1954 AND bic1.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
1955 AND ((bic1.OPERATION_SEQ_NUM = bic.OPERATION_SEQ_NUM)
1956 OR
1957 (decode(bic1.IMPLEMENTATION_DATE, NULL,
1958 bic1.OLD_COMPONENT_SEQUENCE_ID,
1959 bic1.COMPONENT_SEQUENCE_ID) =
1960 decode(bic.IMPLEMENTATION_DATE, NULL,
1961 bic.OLD_COMPONENT_SEQUENCE_ID,
1962 bic.COMPONENT_SEQUENCE_ID)
1963 )
1964 )
1965 AND bic1.EFFECTIVITY_DATE <= l_rev_datetime
1966 )
1967 )
1968 )
1969
1970 -- Bug 2381452 and 3063633, phantom's TL resource/overhead/osp controlled by the use_phantom_routings flag
1971 -- phantom's TL material/moh costs controlled by the profile
1972 -- Bug 2455770. Do not rollup the TL yielded resource costs for a phantom subassembly since the resources are used at the parent
1973
1974 AND bp.ORGANIZATION_ID(+) = bbom.organization_id
1975 AND msi.ORGANIZATION_ID = l_org_id_tbl(i)
1976 AND msi.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
1977 /* Bug 4547027 - Added the check to ignore the cost of inactive items. */
1978 AND NVL(msi.inventory_item_status_code, 'NOT'||bp.bom_delete_status_code) <> NVL(bp.bom_delete_status_code,' ')
1979 AND (
1980 ( nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)) <> 6)
1981 OR
1982 ( nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)) = 6
1983 AND cicd.yielded_cost is null
1984 AND ((NVL(bp.use_phantom_routings,2) = 1 and cicd.cost_element_id NOT in (1,2))
1985 OR
1986 (l_phantom_mat = 1 and cicd.cost_element_id in (1,2))
1987 OR
1988 cicd.level_type = 2
1989 )
1990 )
1991 )
1992 AND cia_comp.ORGANIZATION_ID = l_org_id_tbl(i)
1993 AND cia_comp.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
1994 AND (
1995 cia_comp.COST_TYPE_ID = l_dest_cost_type_id
1996 OR
1997 ( cia_comp.COST_TYPE_ID = default_cost_type_id
1998 AND NOT EXISTS (
1999 SELECT /*+ PUSH_SUBQ */ 'X' -- Added hint for 5678464
2000 FROM CST_ITEM_COSTS cia1
2001 WHERE cia1.ORGANIZATION_ID = l_org_id_tbl(i)
2002 AND cia1.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
2003 AND cia1.COST_TYPE_ID = l_dest_cost_type_id)
2004 )
2005 OR
2006 ( cia_comp.COST_TYPE_ID = l_pcm_tbl(i)
2007 AND NOT EXISTS (
2008 SELECT /*+ PUSH_SUBQ */ 'X' -- Added hint for 5678464
2009 FROM CST_ITEM_COSTS cia2
2010 WHERE cia2.ORGANIZATION_ID = l_org_id_tbl(i)
2011 AND cia2.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
2012 AND cia2.COST_TYPE_ID in (l_dest_cost_type_id, default_cost_type_id))
2013 )
2014 )
2015 AND cia_comp.COST_TYPE_ID IN (l_dest_cost_type_id,default_cost_type_id,l_pcm_tbl(i)) -- Added for 5678464
2016 AND cia_comp.INVENTORY_ASSET_FLAG = 1
2017
2018 AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
2019 AND cicd.INVENTORY_ITEM_ID = cia_comp.INVENTORY_ITEM_ID
2020 AND cicd.COST_TYPE_ID = cia_comp.COST_TYPE_ID
2021 AND cicd.operation_sequence_id = bomres.operation_sequence_id (+)
2022 AND cicd.resource_seq_num = bomres.resource_seq_num (+)
2023 AND cicd.basis_resource_id = bomres.resource_id (+)
2024
2025 GROUP BY
2026 l_inv_item_tbl(i),
2027 l_org_id_tbl(i),
2028 l_ext_tbl(i),
2029 DECODE(pl_operation_flag, 1, bic.operation_seq_num, NULL),
2030 DECODE(pl_cost_code_flag, 1, cicd.RESOURCE_ID, NULL),
2031 DECODE(pl_cost_code_flag, 1, DECODE(cicd.COST_ELEMENT_ID, 1, NULL, cicd.RESOURCE_RATE), NULL),
2032 DECODE(pl_element_flag, 1, cicd.COST_ELEMENT_ID, NULL),
2033 DECODE(pl_activity_flag, 1, cicd.ACTIVITY_ID, NULL),
2034 /*Start changes for LBM */
2035 /* basis type */
2036 DECODE(PL_COST_CODE_FLAG, 1,
2037 DECODE(BP.USE_PHANTOM_ROUTINGS, 1,
2038 DECODE(NVL(BIC.WIP_SUPPLY_TYPE, NVL(msi.WIP_SUPPLY_TYPE,1)),6,
2039 DECODE(CICD.BASIS_TYPE, 2, 2, 3, 3, 4, 4,1),
2040 DECODE(bic.basis_type,2,2,1)),
2041 DECODE(bic.basis_type,2,2,1)),
2045 DECODE(NVL(bp.use_phantom_routings,2), 1,
2042 DECODE(bic.basis_type,2,2,1)),
2043 /* basis_factor changed */
2044 DECODE(pl_cost_code_flag, 1,
2046 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2047 DECODE(cicd.cost_element_id,
2048 1, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2049 2, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2050 3, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2051 4, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2052 5, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE,
2053 3, DECODE(bomres.basis_type,
2054 2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
2055 cicd.basis_factor),
2056 4, DECODE(bomres.basis_type,
2057 2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
2058 cicd.basis_factor),1)),
2059 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
2060 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
2061 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
2062 /* end changes for LBM */
2063 DECODE(pl_cost_code_flag, 1,
2064 DECODE(NVL(bp.use_phantom_routings,2), 1,
2065 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2066 DECODE(cicd.cost_element_id, 3, cicd.basis_resource_id,
2067 4, cicd.basis_resource_id,
2068 5, cicd.basis_resource_id, null), null), null), null),
2069 DECODE(pl_cost_code_flag, 1,
2070 DECODE(NVL(bp.use_phantom_routings,2), 1,
2071 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2072 DECODE(cicd.cost_element_id, 3, cicd.operation_sequence_id,
2073 4, cicd.operation_sequence_id,
2074 5, cicd.operation_sequence_id, null), null), null), null),
2075 DECODE(pl_cost_code_flag, 1,
2076 DECODE(NVL(bp.use_phantom_routings,2), 1,
2077 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2078 DECODE(cicd.cost_element_id, 3, cicd.resource_seq_num,
2079 4, cicd.resource_seq_num,
2080 5, cicd.resource_seq_num, null), null), null), null),
2081 1/(1-NVL(cia_assy.SHRINKAGE_RATE,0)),
2082 DECODE(cicd.level_type, CM_THIS_LEVEL,
2083 DECODE(NVL(bp.use_phantom_routings,2), 1,
2084 DECODE(nvl(bic.WIP_SUPPLY_TYPE, Nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2085 DECODE(cicd.cost_element_id,3,CM_THIS_LEVEL,4,CM_THIS_LEVEL,5,CM_THIS_LEVEL,CM_PREVIOUS_LEVEL),
2086 CM_PREVIOUS_LEVEL),
2087 CM_PREVIOUS_LEVEL), CM_PREVIOUS_LEVEL);
2088
2089
2090 EXIT WHEN cllc10_cur%NOTFOUND;
2091 END LOOP; -- Bug 3590153
2092
2093 close cllc10_cur;
2094
2095
2096 /*------------------------------------------------------------+
2097 | Operation Yield Changes |
2098 +-------------------------------------------------------------*/
2099
2100 FOR csllc_level_org IN csllc_org_level_cur(cur_level)
2101 LOOP
2102
2103 sql_stmt_num := 115;
2104
2105 l_wsm_enabled := 'N';
2106
2107 select UPPER(wsm_enabled_flag)
2108 into l_wsm_enabled
2109 from mtl_parameters
2110 where organization_id = csllc_level_org.organization_id;
2111
2112 if (l_wsm_enabled = 'Y') then
2113
2114 sql_stmt_num := 118;
2115
2116
2117 l_oerr_code := process_sc_rollup_op_yields (
2118 csllc_level_org.ext_precision,
2119 l_rollup_id,
2120 conc_flag,
2121 req_id,
2122 prgm_appl_id,
2123 prgm_id,
2124 l_last_updated_by,
2125 alt_rtg_designator,
2126 /* Bug 2305807. Need Effectivity Date */
2127 revision_date,
2128 csllc_level_org.organization_id,
2129 cur_level,
2130 l_dest_cost_type_id,
2131 -- Obtain error message for bug 3097347
2132 err_buf);
2133 end if;
2134
2135 IF (l_oerr_code <> 0) THEN
2136 /* Propagation for Bug 2347889.
2137 Need to return error_code */
2138 return_code := l_oerr_code;
2139 RAISE STANDARD_ERROR;
2140 END IF;
2141
2142 END LOOP;
2143
2144 /*------------------------------------------------------------+
2145 | Calculate the material overhead charges based on total |
2146 | item value level by level. |
2147 +-------------------------------------------------------------*/
2148
2152
2149 /* Now for all cases of rollup including specific rollup and all items rollup the cursor processing path will be taken */
2150
2151 FOR inv_cursor IN cllc2_cur(cur_level) LOOP
2153
2154 sql_stmt_num := 120;
2155 l_oerr_code := 0;
2156
2157 IF (l_assignment_set_id IS NOT NULL) THEN
2158
2159 l_oerr_code := CSTPSCCM.merge_costs (
2160 l_rollup_id,
2161 l_dest_cost_type_id,
2162 l_buy_cost_type_id,
2163 inv_cursor.inventory_item_id,
2164 inv_cursor.organization_id,
2165 l_assignment_set_id,
2166 err_buf,
2167 buy_cost_detail -- SCAPI: option to preserve buy cost details
2168 );
2169 END IF;
2170
2171 IF (l_oerr_code <> 0) THEN
2172 return_code := l_oerr_code;
2173 RAISE STANDARD_ERROR;
2174 END IF;
2175
2176
2177 sql_stmt_num := 130;
2178
2179
2180 UPDATE CST_ITEM_COST_DETAILS cicd
2181 SET (ITEM_COST,BASIS_FACTOR) =
2182 (SELECT ROUND((cicd.USAGE_RATE_OR_AMOUNT *
2183 NVL(SUM(cicd1.ITEM_COST),0)),inv_cursor.ext_precision),
2184 NVL(SUM(cicd1.ITEM_COST),0) /
2185 NVL(cicd.net_yield_or_shrinkage_factor,1) /
2186 DECODE(cicd.resource_rate,0 , 1 , NVL(cicd.resource_rate,1))
2187 FROM CST_ITEM_COST_DETAILS cicd1
2188 WHERE cicd1.ORGANIZATION_ID = inv_cursor.organization_id
2189 AND cicd1.COST_TYPE_ID = l_dest_cost_type_id
2190 AND cicd1.INVENTORY_ITEM_ID = cicd.INVENTORY_ITEM_ID
2191 AND cicd1.BASIS_TYPE <> 5 /* Total Value */
2192 AND NOT (cicd1.LEVEL_TYPE = CM_THIS_LEVEL
2193 AND
2194 cicd1.COST_ELEMENT_ID = 2))
2195 WHERE cicd.INVENTORY_ITEM_ID = inv_cursor.inventory_item_id
2196 AND cicd.ORGANIZATION_ID = inv_cursor.organization_id
2197 AND cicd.COST_TYPE_ID = l_dest_cost_type_id
2198 AND cicd.BASIS_TYPE = 5 /* Total Value */
2199 AND cicd.COST_ELEMENT_ID = 2;
2200
2201
2202 END LOOP;
2203
2204
2205 END LOOP calc; /* FOR LOOP ENDS HERE */
2206
2207
2208
2209 sql_stmt_num := 140;
2210
2211
2212 IF (pl_operation_flag = 1) THEN
2213 OPEN cllc3_cur(l_rollup_id);
2214 LOOP
2215 FETCH cllc3_cur BULK COLLECT INTO
2216 l_inv_item_tbl,
2217 l_org_id_tbl,
2218 l_roun_tbl,
2219 l_prec_tbl,
2220 l_ext_tbl
2221 LIMIT 1000;
2222
2223 FORALL i IN l_inv_item_tbl.first..l_inv_item_tbl.last
2224 UPDATE CST_ITEM_COST_DETAILS cicd
2225 SET (OPERATION_SEQUENCE_ID,
2226 DEPARTMENT_ID) =
2227 (SELECT bos.OPERATION_SEQUENCE_ID,
2228 bos.DEPARTMENT_ID
2229 FROM BOM_OPERATIONAL_ROUTINGS bor,
2230 BOM_OPERATION_SEQUENCES bos
2231 WHERE bor.ASSEMBLY_ITEM_ID = cicd.INVENTORY_ITEM_ID
2232 AND bor.ORGANIZATION_ID = l_org_id_tbl(i)
2233 AND ((l_mfg_flag = 1 AND bor.ROUTING_TYPE = 1)
2234 OR
2235 (l_mfg_flag = 2)
2236 )
2237 AND bor.COMMON_ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
2238 AND bos.OPERATION_SEQ_NUM = cicd.OPERATION_SEQ_NUM
2239 AND (NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none') =NVL(alt_rtg_designator, 'none')
2240 OR(alt_rtg_designator IS NOT NULL
2241 AND (bor.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
2242 AND NOT EXISTS
2243 (SELECT 'X'
2244 FROM BOM_OPERATIONAL_ROUTINGS bor1
2245 WHERE bor1.ASSEMBLY_ITEM_ID = bor.ASSEMBLY_ITEM_ID
2246 AND bor1.ORGANIZATION_ID = l_org_id_tbl(i)
2247 AND bor1.ALTERNATE_ROUTING_DESIGNATOR =alt_rtg_designator
2248 AND ((l_mfg_flag = 1 AND bor1.ROUTING_TYPE = 1)
2249 OR
2250 (l_mfg_flag = 2)
2251 )
2252 )
2253 )
2254 )
2255
2256 /* Fix for BUG 1608765 */
2257 AND bos.EFFECTIVITY_DATE <= l_rev_datetime
2258 AND NVL( bos.DISABLE_DATE, l_rev_datetime + 1) >= l_rev_datetime /*Changed > to >= for bug 6389605*/
2259
2260 -- This extra clause is because for
2261 -- Flow Manufacturing a new column has been added to the
2262 -- primary key of bos
2263 AND nvl(bos.operation_type, 1) = 1
2264
2265 /* Right now, ECO does not support Op Yield */
2266 AND ( NVL( bos.include_in_rollup, 1 ) = 1
2267 OR bos.change_notice is not null
2268 )
2269
2270 /* This section takes care of Unimplemented ECO Routings */
2271 AND ((unimp_flag = 2 AND bos.implementation_date is not null)
2272 OR
2273 ( unimp_flag = 1
2274 AND bos.effectivity_date =
2275 (SELECT MAX(bos2.effectivity_date )
2279 AND bos2.operation_seq_num = bos.operation_seq_num
2276 FROM bom_operation_sequences bos2
2277 WHERE bos2.routing_sequence_id = bos.routing_sequence_id
2278 AND NVL(bos2.operation_type, 1) = NVL(bos.operation_type, 1)
2280
2281 /* Fix for BUG 1607662 */
2282 AND bos2.EFFECTIVITY_DATE <= fnd_date.canonical_to_date( revision_date )
2283 )
2284 )
2285 )
2286
2287 AND NVL( bos.eco_for_production, 2 ) = 2
2288 -- Added for Bug: 1078491 by ADEY
2289 -- No to select disabled rows with same op seq num.
2290 AND NVL(DISABLE_DATE, SYSDATE+1) >= SYSDATE /*Changed > to >= for bug 6389605*/
2291 )
2292
2293 WHERE cicd.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
2294 AND cicd.COST_TYPE_ID = l_dest_cost_type_id
2295 AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
2296 AND cicd.LEVEL_TYPE = CM_PREVIOUS_LEVEL;
2297
2298 EXIT WHEN cllc3_cur%NOTFOUND;
2299 END LOOP;
2300 CLOSE cllc3_cur;
2301 END IF;
2302
2303
2304 sql_stmt_num := 150;
2305 /*------------------------------------------------------------+
2306 | Update rows in CST_ITEM_COSTS which had costs altered. |
2307 | Because we need to include denormalized cost information. |
2308 +------------------------------------------------------------*/
2309
2310 OPEN cllc_cur;
2311 LOOP
2312 FETCH cllc_cur BULK COLLECT INTO
2313 l_inv_item_tbl,
2314 l_org_id_tbl,
2315 l_roun_tbl,
2316 l_prec_tbl,
2317 l_ext_tbl
2318 LIMIT 1000;
2319
2320 FORALL i IN l_inv_item_tbl.first..l_inv_item_tbl.last
2321 UPDATE CST_ITEM_COSTS cic
2322 SET ( PL_MATERIAL ,
2323 PL_MATERIAL_OVERHEAD ,
2324 PL_RESOURCE ,
2325 PL_OUTSIDE_PROCESSING ,
2326 PL_OVERHEAD ,
2327 TL_MATERIAL ,
2328 TL_MATERIAL_OVERHEAD ,
2329 TL_RESOURCE ,
2330 TL_OUTSIDE_PROCESSING ,
2331 TL_OVERHEAD ,
2332 MATERIAL_COST ,
2333 MATERIAL_OVERHEAD_COST ,
2334 RESOURCE_COST ,
2335 OUTSIDE_PROCESSING_COST ,
2336 OVERHEAD_COST ,
2337 PL_ITEM_COST ,
2338 TL_ITEM_COST ,
2339 ITEM_COST ,
2340 UNBURDENED_COST ,
2341 BURDEN_COST ,
2342 REQUEST_ID ,
2343 PROGRAM_APPLICATION_ID ,
2344 PROGRAM_ID ,
2345 PROGRAM_UPDATE_DATE ,
2346 LAST_UPDATE_DATE ,
2347 LAST_UPDATED_BY,
2348 ROLLUP_ID,
2349 ASSIGNMENT_SET_ID) =
2350
2351 ( SELECT
2352 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,
2353 cicd.ITEM_COST,0),0)),
2354 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,
2355 cicd.ITEM_COST,0),0)),
2356 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,
2357 cicd.ITEM_COST,0),0)),
2358 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,
2359 cicd.ITEM_COST,0),0)),
2360 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,
2361 cicd.ITEM_COST,0),0)),
2362 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,
2363 cicd.ITEM_COST,0),0)),
2364 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,
2365 cicd.ITEM_COST,0),0)),
2366 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,
2367 cicd.ITEM_COST,0),0)),
2368 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,4,
2369 cicd.ITEM_COST,0),0)),
2370 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,
2371 cicd.ITEM_COST,0),0)),
2372 SUM(DECODE(COST_ELEMENT_ID,1,cicd.ITEM_COST)),
2373 SUM(DECODE(COST_ELEMENT_ID,2,cicd.ITEM_COST)),
2374 SUM(DECODE(COST_ELEMENT_ID,3,cicd.ITEM_COST)),
2375 SUM(DECODE(COST_ELEMENT_ID,4,cicd.ITEM_COST)),
2376 SUM(DECODE(COST_ELEMENT_ID,5,cicd.ITEM_COST)),
2377 SUM(DECODE(LEVEL_TYPE,2,cicd.ITEM_COST,0)),
2378 SUM(DECODE(LEVEL_TYPE,1,cicd.ITEM_COST,0)),
2379 NVL(SUM(cicd.ITEM_COST),0),
2380 SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,2,
2381 cicd.ITEM_COST,0),
2382 cicd.ITEM_COST)),
2383 SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,1,
2384 cicd.ITEM_COST,0),0)),
2385 DECODE(conc_flag, 1, req_id, NULL),
2386 DECODE(conc_flag, 1, prgm_appl_id, NULL),
2387 DECODE(conc_flag, 1, prgm_id, NULL),
2388 DECODE(conc_flag, 1, l_rollup_date, NULL),
2389 l_rollup_date,
2390 l_last_updated_by,
2391 l_rollup_id,
2392 l_assignment_set_id
2393 FROM CST_ITEM_COST_DETAILS cicd
2394 WHERE cicd.INVENTORY_ITEM_ID = cic.INVENTORY_ITEM_ID
2395 AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
2396 AND cicd.COST_TYPE_ID = l_dest_cost_type_id)
2397
2398 WHERE cic.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
2399 AND COST_TYPE_ID = l_dest_cost_type_id
2403 END LOOP;
2400 AND cic.ORGANIZATION_ID = l_org_id_tbl(i);
2401
2402 EXIT WHEN cllc_cur%NOTFOUND;
2404 CLOSE cllc_cur;
2405
2406 return_code := 0;
2407 err_buf := 'CSTPSCCR.cstsccru ' || 'Success';
2408 return(return_code);
2409
2410 EXCEPTION
2411 when STANDARD_ERROR then
2412 return(return_code);
2413 when SNAPSHOT_ERROR then
2414 return(return_code);
2415 when OTHERS then
2416 return_code := SQLCODE;
2417 err_buf := 'CSTPSCR.cstsccru ' || 'stmt_num = ' || sql_stmt_num || ': '
2418 || substrb(sqlerrm,1,200);
2419 return(return_code);
2420
2421 END cstsccru;
2422
2423
2424 PROCEDURE populate_markup_costs (
2425 l_rollup_id IN NUMBER,
2426 l_item_id IN NUMBER,
2427 l_org_id IN NUMBER,
2428 l_assignment_set_id IN NUMBER,
2429 l_buy_cost_type_id IN NUMBER,
2430 l_dest_cost_type_id IN NUMBER,
2431 x_err_code OUT NOCOPY NUMBER,
2432 x_err_buf OUT NOCOPY VARCHAR2)
2433 IS
2434 l_stmt_num NUMBER;
2435 l_src_org_id NUMBER;
2436 l_markup_code NUMBER;
2437 l_markup NUMBER;
2438 STANDARD_ERROR EXCEPTION;
2439
2440 CURSOR markup_org_cur(
2441 l_rollup_id IN NUMBER,
2442 l_inventory_item_id IN NUMBER,
2443 l_dest_organization_id IN NUMBER,
2444 l_assignment_set_id IN NUMBER) IS
2445 SELECT
2446 CSSR.source_organization_id
2447 FROM
2448 CST_SC_SOURCING_RULES CSSR
2449 WHERE
2450 CSSR.ROLLUP_ID = l_rollup_id
2451 AND CSSR.inventory_item_id = l_inventory_item_id
2452 AND CSSR.organization_id = l_dest_organization_id
2453 AND CSSR.assignment_set_id = l_assignment_set_id
2454 AND CSSR.source_type = 1;
2455
2456
2457
2458 BEGIN
2459
2460 l_stmt_num := 10;
2461
2462 OPEN markup_org_cur(
2463 l_rollup_id,
2464 l_item_id,
2465 l_org_id,
2466 l_assignment_set_id);
2467 LOOP
2468 FETCH markup_org_cur INTO l_src_org_id;
2469 IF (markup_org_cur%NOTFOUND) THEN
2470 EXIT;
2471 END IF;
2472
2473
2474
2475 CSTPSCHO.get_markup_hook
2476 (
2477 l_rollup_id,
2478 l_item_id,
2479 l_org_id,
2480 l_src_org_id,
2481 l_dest_cost_type_id,
2482 l_buy_cost_type_id,
2483 l_markup,
2484 l_markup_code,
2485 x_err_code,
2486 x_err_buf
2487 );
2488
2489 IF (x_err_code <> 0) THEN
2490 RAISE STANDARD_ERROR;
2491 NULL;
2492 END IF;
2493
2494 UPDATE CST_SC_SOURCING_RULES CSSR
2495 SET CSSR.MARKUP = l_markup,
2496 CSSR.MARKUP_CODE = l_markup_code
2497 WHERE CSSR.ROLLUP_ID = l_rollup_id
2498 AND CSSR.inventory_item_id = l_item_id
2499 AND CSSR.organization_id = l_org_id
2500 AND CSSR.assignment_set_id = l_assignment_set_id
2501 AND CSSR.source_type = 1
2502 AND CSSR.source_organization_id = l_src_org_id
2503 AND l_markup_code <> -1
2504 AND l_markup_code IN (2,3); -- Req value or percent only
2505
2506
2507 UPDATE CST_SC_SOURCING_RULES
2508 SET (MARKUP,
2509 MARKUP_CODE) =
2510 (SELECT
2511 INTERORG_TRNSFR_CHARGE_PERCENT,
2512 MATL_INTERORG_TRANSFER_CODE
2513 FROM MTL_INTERORG_PARAMETERS MIP
2514 WHERE MIP.from_organization_id = l_src_org_id
2515 AND MIP.to_organization_id = l_org_id
2516 AND MIP.MATL_INTERORG_TRANSFER_CODE = 4
2517 -- to Support only predefined %
2518 )
2519 WHERE
2520 ROLLUP_ID = l_rollup_id
2521 AND inventory_item_id = l_item_id
2522 AND organization_id = l_org_id
2523 AND assignment_set_id = l_assignment_set_id
2524 AND source_type = 1
2525 AND source_organization_id = l_src_org_id
2526 AND markup_code IS NULL
2527 AND markup IS NULL;
2528
2529
2530
2531 END LOOP;
2532 CLOSE markup_org_cur;
2533
2534 x_err_code := 0;
2535 x_err_buf := 'CSTSCBOM.populate_markup_costs:' || 'S' || l_stmt_num || ': SUCCESS';
2536 return;
2537
2538 EXCEPTION
2539 when others then
2540 x_err_code := SQLCODE;
2544
2541 x_err_buf := 'CSTSCBOM.populate_markup_costs:' || 'S' || l_stmt_num || ' :' || substrb(sqlerrm,1,62);
2542 return;
2543
2545 END populate_markup_costs;
2546
2547 PROCEDURE populate_shipping_costs (
2548 l_rollup_id IN NUMBER,
2549 l_item_id IN NUMBER,
2550 l_org_id IN NUMBER,
2551 l_assignment_set_id IN NUMBER,
2552 l_buy_cost_type_id IN NUMBER,
2553 l_dest_cost_type_id IN NUMBER,
2554 x_err_code OUT NOCOPY NUMBER,
2555 x_err_buf OUT NOCOPY VARCHAR2)
2556 IS
2557 l_stmt_num NUMBER;
2558 l_src_org_id NUMBER;
2559 x_ship_charge_code NUMBER;
2560 x_ship_charge NUMBER;
2561 l_ship_method VARCHAR2(30);
2562 STANDARD_ERROR EXCEPTION;
2563
2564 CURSOR shipping_cost_cur(
2565 l_rollup_id IN NUMBER,
2566 l_inventory_item_id IN NUMBER,
2567 l_dest_organization_id IN NUMBER,
2568 l_assignment_set_id IN NUMBER) IS
2569 SELECT
2570 CSSR.source_organization_id,
2571 CSSR.ship_method
2572 FROM
2573 CST_SC_SOURCING_RULES CSSR
2574 WHERE
2575 CSSR.ROLLUP_ID = l_rollup_id
2576 AND CSSR.inventory_item_id = l_inventory_item_id
2577 AND CSSR.organization_id = l_dest_organization_id
2578 AND CSSR.assignment_set_id = l_assignment_set_id
2579 AND CSSR.source_type = 1;
2580
2581
2582
2583 BEGIN
2584
2585 l_stmt_num := 10;
2586
2587 OPEN shipping_cost_cur(
2588 l_rollup_id,
2589 l_item_id,
2590 l_org_id,
2591 l_assignment_set_id);
2592 LOOP
2593 FETCH shipping_cost_cur INTO l_src_org_id, l_ship_method;
2594 IF (shipping_cost_cur%NOTFOUND) THEN
2595 EXIT;
2596 END IF;
2597 l_stmt_num := 20;
2598
2599
2600 CSTPSCHO.get_shipping_hook
2601 (
2602 l_rollup_id,
2603 l_item_id,
2604 l_org_id,
2605 l_src_org_id,
2606 l_dest_cost_type_id,
2607 l_buy_cost_type_id,
2608 l_ship_method,
2609 x_ship_charge,
2610 x_ship_charge_code,
2611 x_err_code,
2612 x_err_buf
2613 );
2614
2615 IF (x_err_code <> 0) THEN
2616 RAISE STANDARD_ERROR;
2617 NULL;
2618 END IF;
2619 l_stmt_num := 30;
2620
2621 UPDATE CST_SC_SOURCING_RULES CSSR
2622 SET CSSR.SHIP_CHARGE = x_ship_charge,
2623 CSSR.SHIP_CHARGE_CODE = x_ship_charge_code
2624 WHERE CSSR.ROLLUP_ID = l_rollup_id
2625 AND CSSR.inventory_item_id = l_item_id
2626 AND CSSR.organization_id = l_org_id
2627 AND CSSR.assignment_set_id = l_assignment_set_id
2628 AND CSSR.source_type = 1
2629 AND CSSR.source_organization_id = l_src_org_id
2630 AND x_ship_charge <> -1
2631 AND x_ship_charge_code IN (2,3); -- Req value or percent only
2632
2633
2634
2635 END LOOP;
2636 CLOSE shipping_cost_cur;
2637
2638 x_err_code := 0;
2639 x_err_buf := 'CSTSCBOM.populate_shipping_costs:' || 'S' || l_stmt_num || ': SUCCESS';
2640 return;
2641
2642 EXCEPTION
2643 when others then
2644 x_err_code := SQLCODE;
2645 x_err_buf := 'CSTSCBOM.populate_shipping_costs:' || 'S' || l_stmt_num || ' :' || substrb(sqlerrm,1,62);
2646 return;
2647
2648
2649 END populate_shipping_costs;
2650
2651
2652 PROCEDURE populate_buy_costs (
2653 l_rollup_id IN NUMBER,
2654 l_assignment_set_id IN NUMBER,
2655 l_item_id IN NUMBER,
2656 l_org_id IN NUMBER,
2657 l_buy_cost_type_id IN NUMBER,
2658 x_err_code OUT NOCOPY NUMBER,
2659 x_err_buf OUT NOCOPY VARCHAR2)
2660 IS
2661 l_stmt_num NUMBER;
2662 l_vendor_id NUMBER;
2663 l_buy_cost NUMBER;
2664 l_curr_rowid ROWID;
2665 l_site_id NUMBER;
2666 l_ship_method NUMBER;
2667 default_cost_type_id NUMBER;
2668 STANDARD_ERROR EXCEPTION;
2669
2670 CURSOR vendors_cur( l_rollup_id IN NUMBER,
2671 l_inventory_item_id IN NUMBER,
2672 l_dest_organization_id IN NUMBER,
2673 l_assignment_set_id IN NUMBER)
2674 IS
2675 SELECT
2676 CSSR.ROWID,
2677 CSSR.vendor_id,
2678 CSSR.vendor_site_id,
2679 CSSR.ship_method
2680 FROM
2681 CST_SC_SOURCING_RULES CSSR
2682 WHERE
2683 CSSR.ROLLUP_ID = l_rollup_id
2687 AND CSSR.source_type = 3
2684 AND CSSR.inventory_item_id = l_inventory_item_id
2685 AND CSSR.organization_id = l_dest_organization_id
2686 AND CSSR.assignment_set_id = l_assignment_set_id
2688 FOR UPDATE;
2689
2690
2691
2692 BEGIN
2693 OPEN vendors_cur(l_rollup_id,l_item_id,l_org_id,l_assignment_set_id);
2694 LOOP
2695 FETCH vendors_cur INTO l_curr_rowid,l_vendor_id,l_site_id,
2696 l_ship_method;
2697 IF (vendors_cur%NOTFOUND) THEN
2698 EXIT;
2699 END IF;
2700
2701 l_buy_cost := CSTPSCHO.get_buy_cost_hook
2702 (
2703 l_rollup_id,
2704 l_assignment_set_id,
2705 l_item_id,
2706 l_org_id,
2707 l_vendor_id,
2708 l_site_id,
2709 l_ship_method,
2710 x_err_code,
2711 x_err_buf
2712 );
2713
2714 IF (x_err_code <> 0) THEN
2715 NULL;
2716 RAISE STANDARD_ERROR;
2717 END IF;
2718
2719 UPDATE CST_SC_SOURCING_RULES CSSR
2720 SET CSSR.ITEM_COST = l_buy_cost,
2721 CSSR.BUY_COST_FLAG = 'Y'
2722 WHERE CSSR.ROLLUP_ID = l_rollup_id
2723 AND CSSR.inventory_item_id = l_item_id
2724 AND CSSR.organization_id = l_org_id
2725 AND CSSR.assignment_set_id = l_assignment_set_id
2726 AND CSSR.source_type = 3
2727 AND l_buy_cost <> -1
2728 AND CSSR.ROWID = l_curr_rowid;
2729
2730 END LOOP;
2731 CLOSE vendors_cur;
2732
2733 /* Supply chain enhancement: default cost type */
2734 SELECT DEFAULT_COST_TYPE_ID
2735 INTO default_cost_type_id
2736 FROM CST_COST_TYPES
2737 WHERE COST_TYPE_ID = l_buy_cost_type_id;
2738
2739 UPDATE CST_SC_SOURCING_RULES
2740 SET (ITEM_COST,
2741 BUY_COST_FLAG) =
2742 (SELECT NVL(SUM(CICD.ITEM_COST),0),
2743 'Y'
2744 FROM CST_ITEM_COST_DETAILS CICD, MTL_PARAMETERS MP
2745 WHERE CICD.inventory_item_id = l_item_id
2746 AND CICD.organization_id = l_org_id
2747 AND MP.organization_id = l_org_id
2748 AND (
2749 CICD.cost_type_id = l_buy_cost_type_id
2750 OR
2751 (
2752 CICD.cost_type_id = default_cost_type_id
2753 AND NOT EXISTS (
2754 SELECT 'X'
2755 FROM CST_ITEM_COSTS cia1
2756 WHERE cia1.inventory_item_id = l_item_id
2757 AND cia1.organization_id = l_org_id
2758 AND cia1.cost_type_id = l_buy_cost_type_id)
2759 )
2760 OR
2761 (
2762 CICD.cost_type_id = MP.primary_cost_method
2763 AND NOT EXISTS (
2764 SELECT 'X'
2765 FROM CST_ITEM_COSTS cia2
2766 WHERE cia2.inventory_item_id = l_item_id
2767 AND cia2.organization_id = l_org_id
2768 AND cia2.cost_type_id in (l_buy_cost_type_id, default_cost_type_id))
2769 )
2770 )
2771 ) /* Supply chain enhancement: default valuation cost type */
2772
2773 WHERE
2774 ROLLUP_ID = l_rollup_id
2775 AND inventory_item_id = l_item_id
2776 AND organization_id = l_org_id
2777 AND assignment_set_id = l_assignment_set_id
2778 AND source_type = 3
2779 AND item_cost IS NULL
2780 AND buy_cost_flag IS NULL;
2781
2782
2783
2784
2785 x_err_code := 0;
2786 x_err_buf := 'CSTSCBOM.populate_buy_costs:' || 'S' || l_stmt_num || ': SUCCESS';
2787 return;
2788
2789 EXCEPTION
2790 when others then
2791 x_err_code := SQLCODE;
2792 x_err_buf := 'CSTSCBOM.populate_buy_costs:' || 'S' || l_stmt_num || ':'
2793 || substrb(sqlerrm,1,62);
2794 return;
2795
2796
2797 END populate_buy_costs;
2798
2799 /*
2800 Resource_ID for yielded_costs are not required.
2801 Resource_ID is mandatory only for TL Material Overhead. Yielded_cost
2802 can only be PL Material or PL MOH, never TL (Mat or MOH)
2803 Changes for Bug 2482828 have been reversed.
2804 Cost Copy has been modified to validate only the TL MOH subelement
2805 Vinit (Bug2731332)
2806 */
2807
2808 FUNCTION process_sc_rollup_op_yields(ext_precision IN NUMBER,
2809 l_rollup_id IN NUMBER,
2810 conc_flag IN NUMBER,
2811 req_id IN NUMBER,
2812 prgm_appl_id IN NUMBER,
2813 prgm_id IN NUMBER,
2814 l_last_updated_by IN NUMBER,
2818 l_level IN NUMBER,
2815 alt_rtg_designator IN VARCHAR2,
2816 rollup_date IN VARCHAR2,
2817 l_organization_id IN NUMBER,
2819 l_cost_type_id IN NUMBER,
2820 -- Output error message for bug 3097347
2821 x_err_buf OUT NOCOPY VARCHAR2)
2822 return NUMBER IS
2823 x_err_num NUMBER;
2824 x_err_msg VARCHAR2(200);
2825 sql_stmt_num NUMBER := 0;
2826 l_opYldFactor NUMBER := 1;
2827 l_invalid_rows NUMBER := 0;
2828 l_err_msg VARCHAR2(240);
2829 invalid_rows_found EXCEPTION;
2830 CONC_STATUS BOOLEAN;
2831 esa_flag NUMBER;
2832 l_login_id number := -1;
2833 l_rollup_date DATE := fnd_date.canonical_to_date(rollup_date);
2834 dummy number;
2835
2836 TYPE inv_item_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2837 TYPE dep_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2838 TYPE op_seq_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2839 TYPE op_seq_num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2840 TYPE op_yield_factor_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2841
2842
2843 l_inv_item_tbl inv_item_tbl_type;
2844 l_dep_id_tbl dep_id_tbl_type;
2845 l_op_seq_id_tbl op_seq_id_tbl_type;
2846 l_op_seq_num_tbl op_seq_num_tbl_type;
2847 l_op_yield_factor_tbl op_yield_factor_tbl_type;
2848
2849
2850 CURSOR opseq_cur IS
2851 SELECT cllc.inventory_item_id INVENTORY_ITEM_ID,
2852 bos.department_id DEPARTMENT_ID,
2853 bos.operation_sequence_id OPERATION_SEQUENCE_ID,
2854 bos.operation_seq_num OPERATION_SEQ_NUM,
2855 ((1/NVL(bos.yield,1))-1) OPERATION_YIELD_FACTOR
2856 FROM bom_operation_sequences bos,
2857 bom_operational_routings bor,
2858 cst_sc_low_level_codes cllc
2859 WHERE cllc.rollup_id = l_rollup_id
2860 AND cllc.low_level_code = l_level
2861 AND bor.assembly_item_id = cllc.inventory_item_id
2862
2863 /* Bug 3152221: use primary routing when we specify an alternate routing which
2864 has not been defined */
2865 AND ( NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none')
2866 = NVL(alt_rtg_designator, 'none')
2867 OR (
2868 (alt_rtg_designator IS NOT NULL)
2869 AND (bor.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
2870 AND NOT EXISTS
2871 (SELECT 'X'
2872 FROM BOM_OPERATIONAL_ROUTINGS bor1
2873 WHERE bor1.ASSEMBLY_ITEM_ID = bor.assembly_item_id
2874 AND bor1.ORGANIZATION_ID = l_organization_id
2875 AND bor1.ALTERNATE_ROUTING_DESIGNATOR =
2876 alt_rtg_designator
2877 )
2878 )
2879 )
2880
2881 and bor.organization_id = l_organization_id
2882 /* Bug 2379908. Use common_routing_sequence_id */
2883 and bos.routing_sequence_id = bor.common_routing_sequence_id
2884 and fnd_date.canonical_to_date(rollup_date) >= bos.effectivity_date
2885
2886 and NVL( bos.disable_date,
2887 fnd_date.canonical_to_date(rollup_date) + 1 )
2888 >= fnd_date.canonical_to_date(rollup_date)
2889
2890 /* Right now, ECO does not support Op Yield */
2891 AND bos.implementation_date is not null
2892 AND NVL( bos.include_in_rollup, 1 ) = 1
2893
2894 AND NVL( bos.eco_for_production, 2 ) = 2
2895 and bos.operation_yield_enabled = 1
2896 and bos.yield <> 1
2897 /* Added join to remove extra rows for op yields in CICD */
2898 and cllc.organization_id = bor.organization_id
2899 order by inventory_item_id,
2900 operation_seq_num;
2901 BEGIN
2902 sql_stmt_num := 5;
2903
2904 select nvl(LAST_UPDATE_LOGIN ,-1 )
2905 into l_login_id
2906 from cst_sc_rollup_history
2907 where rollup_id = l_rollup_id
2908 and rownum =1;
2909
2910
2911 sql_stmt_num := 10;
2912
2913 /* Use OSFM API */
2914
2915 esa_flag := WSMPUTIL.WSM_ESA_ENABLED(NULL,
2916 x_err_num, x_err_msg,
2917 l_organization_id );
2918
2919 OPEN opseq_cur;
2920 LOOP
2921 FETCH opseq_cur BULK COLLECT INTO
2922 l_inv_item_tbl,
2923 l_dep_id_tbl,
2924 l_op_seq_id_tbl,
2925 l_op_seq_num_tbl,
2926 l_op_yield_factor_tbl LIMIT 1000;
2927
2928 IF (esa_flag = 1) then
2929 sql_stmt_num := 30;
2930 FORALL i in l_inv_item_tbl.first..l_inv_item_tbl.last
2931 INSERT INTO CST_ITEM_COST_DETAILS (
2932 inventory_item_id,
2933 organization_id,
2934 SOURCE_ORGANIZATION_ID,
2935 cost_type_id,
2936 last_update_date,
2937 last_updated_by,
2941 operation_sequence_id,
2938 creation_date,
2939 created_by,
2940 last_update_login,
2942 operation_seq_num,
2943 department_id,
2944 level_type,
2945 usage_rate_or_amount,
2946 basis_type,
2947 basis_factor,
2948 net_yield_or_shrinkage_factor,
2949 item_cost,
2950 cost_element_id,
2951 rollup_source_type,
2952 request_id,
2953 program_application_id,
2954 program_id,
2955 program_update_date,
2956 yielded_cost,
2957 resource_id)
2958 SELECT l_inv_item_tbl(i),
2959 l_organization_id,
2960 l_organization_id,
2961 l_cost_type_id,
2962 l_rollup_date,
2963 l_last_updated_by,
2964 l_rollup_date,
2965 l_last_updated_by,
2966 l_login_id,
2967 l_op_seq_id_tbl(i),
2968 l_op_seq_num_tbl(i),
2969 l_dep_id_tbl(i),
2970 decode(cost_element_id,1,2,2,2,1),
2971 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
2972 1,
2973 1,
2974 1,
2975 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
2976 cost_element_id,
2977 3,
2978 decode(conc_flag,1,req_id,NULL),
2979 decode(conc_flag,1,prgm_appl_id,NULL),
2980 decode(conc_flag,1,prgm_id,NULL),
2981 decode(conc_flag,1,sysdate,NULL),
2982 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
2983 NULL
2984 FROM cst_item_cost_details
2985 WHERE organization_id = l_organization_id
2986 AND inventory_item_id = l_inv_item_tbl(i)
2987 AND cost_type_id = l_cost_type_id
2988 AND operation_seq_num <= l_op_seq_num_tbl(i)
2989 GROUP BY cost_element_id;
2990 ELSE
2991 /* If Estimated Scrap Accouting is disabled,
2992 then we need to distinguish between the this level
2993 and previous level operation yield costs */
2994 sql_stmt_num := 35;
2995 FORALL i in l_inv_item_tbl.first..l_inv_item_tbl.last
2996 INSERT into cst_item_cost_details (
2997 inventory_item_id,
2998 organization_id,
2999 SOURCE_ORGANIZATION_ID,
3000 cost_type_id,
3001 last_update_date,
3002 last_updated_by,
3003 creation_date,
3004 created_by,
3005 last_update_login,
3006 operation_sequence_id,
3007 operation_seq_num,
3008 department_id,
3009 level_type,
3010 usage_rate_or_amount,
3011 basis_type,
3012 basis_factor,
3013 net_yield_or_shrinkage_factor,
3014 item_cost,
3015 cost_element_id,
3016 rollup_source_type,
3017 request_id,
3018 program_application_id,
3019 program_id,
3020 program_update_date,
3021 yielded_cost,
3022 resource_id)
3023 SELECT l_inv_item_tbl(i),
3024 l_organization_id,
3025 l_organization_id,
3026 l_cost_type_id,
3027 l_rollup_date,
3028 l_last_updated_by,
3029 l_rollup_date,
3030 l_last_updated_by,
3031 l_login_id,
3032 l_op_seq_id_tbl(i),
3033 l_op_seq_num_tbl(i),
3034 l_dep_id_tbl(i),
3035 decode(cost_element_id,1,2,2,2,level_type),
3036 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
3037 1,
3038 1,
3039 1,
3040 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
3041 cost_element_id,
3042 3,
3043 decode(conc_flag,1,req_id,NULL),
3044 decode(conc_flag,1,prgm_appl_id,NULL),
3045 decode(conc_flag,1,prgm_id,NULL),
3046 decode(conc_flag,1,sysdate,NULL),
3047 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
3048 NULL
3049 FROM cst_item_cost_details
3050 WHERE organization_id = l_organization_id
3051 AND inventory_item_id = l_inv_item_tbl(i)
3052 AND cost_type_id = l_cost_type_id
3053 AND operation_seq_num <= l_op_seq_num_tbl(i)
3054 GROUP BY cost_element_id, level_type;
3055 END IF;
3056
3057 sql_stmt_num := 40;
3058
3059 dummy := l_inv_item_tbl.first;
3060
3061 WHILE TRUE LOOP
3062
3063 if (dummy is NULL) then
3064 exit;
3065 end if;
3066
3067 SELECT count(*)
3068 INTO l_invalid_rows
3069 FROM cst_item_cost_details
3070 WHERE organization_id = l_organization_id
3071 AND inventory_item_id = l_inv_item_tbl(dummy)
3072 AND cost_type_id = l_cost_type_id
3073 AND yielded_cost IS NOT NULL
3077 IF (l_invalid_rows > 0) then
3074 AND yielded_cost <> item_cost
3075 AND rownum=1;
3076
3078 raise INVALID_ROWS_FOUND;
3079 END IF;
3080
3081 dummy := l_inv_item_tbl.next (dummy);
3082 END LOOP;
3083
3084 EXIT WHEN opseq_cur%NOTFOUND;
3085 END LOOP;
3086 CLOSE opseq_cur;
3087
3088 return (0);
3089
3090 EXCEPTION
3091 WHEN INVALID_ROWS_FOUND THEN
3092 l_err_msg := 'process_sc_rollup_op_yields: ' || sql_stmt_num || ': Value in item_cost should be equal to value in yielded_cost for operation yield cost rows';
3093 x_err_buf := l_err_msg; -- Output error message for bug 3097347
3094 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
3095 fnd_file.put_line(fnd_file.log,l_err_msg);
3096 return(sql_stmt_num);
3097
3098 WHEN OTHERS THEN
3099 l_err_msg := 'process_sc_rollup_op_yields: ' || sql_stmt_num || ': ' || substrb(sqlerrm,1,62);
3100 x_err_buf := l_err_msg; -- Output error message for bug 3097347
3101 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
3102 fnd_file.put_line(fnd_file.log,l_err_msg);
3103 return(sql_stmt_num);
3104
3105 END process_sc_rollup_op_yields;
3106
3107 /*-----------------------------------------------------------+
3108 |This Function will create the snapshot of the BOM's used in |
3109 |the supply chain cost rollup process |
3110 +------------------------------------------------------------*/
3111
3112 function supply_chain_snapshot (
3113 l_rollup_id in number,
3114 l_cost_type_id in number,
3115 l_mfg_flag in number,
3116 alt_bom_designator in varchar2,
3117 l_conc_flag in number,
3118 l_unimp_flag in number,
3119 revision_date in varchar2,
3120 l_last_updated_by in number,
3121 l_rollup_date in varchar2,
3122 req_id in number,
3123 p_prg_appl_id in number,
3124 p_prg_id in number,
3125 err_buf out NOCOPY varchar2)
3126 return integer
3127 is
3128 return_code NUMBER;
3129
3130 sql_stmt_num NUMBER := 0;
3131 l_snapshot_designator VARCHAR2(40);
3132 alt_org NUMBER:=0;
3133 p_rollup_date CONSTANT DATE := TO_DATE(l_rollup_date,'YYYY/MM/DD HH24:MI:SS');
3134 p_revision_date CONSTANT DATE := TO_DATE(revision_date,'YYYY/MM/DD HH24:MI:SS');
3135 p_canonical_rev_date CONSTANT DATE := fnd_date.canonical_to_date(revision_date);
3136 l_req_id NUMBER;
3137 l_prg_appl_id NUMBER;
3138 l_prg_id NUMBER;
3139 l_prg_update_date DATE;
3140
3141 BEGIN
3142 return_code := 8888;
3143
3144 IF (l_conc_flag = 1)THEN
3145 l_req_id := req_id;
3146 l_prg_appl_id := p_prg_appl_id;
3147 l_prg_id := p_prg_id;
3148 l_prg_update_Date := p_rollup_date;
3149
3150 ELSE
3151 l_req_id := NULL;
3152 l_prg_appl_id := NULL;
3153 l_prg_id := NULL;
3154 l_prg_update_date := NULL;
3155
3156 END IF;
3157
3158
3159 sql_stmt_num := 5;
3160 select alternate_bom_designator
3161 into l_snapshot_designator
3162 from cst_cost_types
3163 where cost_type_id = l_cost_type_id;
3164
3165 if (l_snapshot_designator IS NULL) then
3166 return(return_code);
3167 end if;
3168
3169
3170 sql_stmt_num := 10;
3171 if l_snapshot_designator <> NVL(alt_bom_designator,(l_snapshot_designator||'_NOT')) THEN
3172 DELETE BOM_INVENTORY_COMPONENTS bic
3173 WHERE bic.BILL_SEQUENCE_ID IN
3174 (SELECT bic2.BILL_SEQUENCE_ID
3175 FROM BOM_INVENTORY_COMPONENTS bic2,
3176 BOM_BILL_OF_MATERIALS bbom,
3177 CST_SC_LOW_LEVEL_CODES csllc,
3178 cst_sc_sourcing_rules cssr
3179 WHERE csllc.ROLLUP_ID = l_rollup_id
3180 AND cssr.rollup_id (+)= l_rollup_id
3181 AND cssr.inventory_item_id (+)= csllc.inventory_item_id
3182 AND cssr.organization_id (+)= csllc.organization_id
3183 AND decode(cssr.source_type,2,1,NULL,1,0)=1
3184 AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
3185 AND bbom.ASSEMBLY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3186 AND bbom.ORGANIZATION_ID = csllc.organization_id
3187 AND bbom.ALTERNATE_BOM_DESIGNATOR =l_snapshot_designator
3188 AND bic2.BILL_SEQUENCE_ID = bbom.BILL_SEQUENCE_ID
3189 );
3190 END IF;
3191
3192 sql_stmt_num := 15;
3193 DELETE BOM_REFERENCE_DESIGNATORS brd
3194 WHERE NOT EXISTS
3195 (SELECT 'Component Header exists'
3196 FROM BOM_INVENTORY_COMPONENTS bic
3197 WHERE bic.COMPONENT_SEQUENCE_ID = brd.COMPONENT_SEQUENCE_ID);
3198
3199 sql_stmt_num := 20;
3200 DELETE BOM_SUBSTITUTE_COMPONENTS bsc
3201 WHERE NOT EXISTS
3202 (SELECT 'Component Header exists'
3203 FROM BOM_INVENTORY_COMPONENTS bic
3204 WHERE bic.COMPONENT_SEQUENCE_ID = bsc.COMPONENT_SEQUENCE_ID);
3208 if l_snapshot_designator <> alt_bom_designator THEN
3205
3206 /* ADD SQL TO DELETE FROM THE BOM_BILL_OF_MATERIALS. */
3207 /*This should be done to update the WHO column of the table*/
3209 sql_stmt_num:=25;
3210 Delete BOM_BILL_OF_MATERIALS bbom
3211 WHERE bbom.BILL_SEQUENCE_ID IN
3212 (SELECT bbom2.BILL_SEQUENCE_ID
3213 FROM BOM_BILL_OF_MATERIALS bbom2,
3214 CST_SC_LOW_LEVEL_CODES csllc,
3215 cst_item_costs cia,
3216 cst_sc_sourcing_rules cssr
3217 WHERE csllc.ROLLUP_ID = l_rollup_id
3218 AND cssr.rollup_id (+)= l_rollup_id
3219 AND cssr.inventory_item_id (+)= csllc.inventory_item_id
3220 AND cssr.organization_id (+)= csllc.organization_id
3221 AND decode(cssr.source_type,2,1,NULL,1,0)=1
3222 AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
3223 AND bbom2.ASSEMBLY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3224 AND bbom2.ORGANIZATION_ID = csllc.organization_id
3225 AND bbom2.ALTERNATE_BOM_DESIGNATOR =l_snapshot_designator
3226 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3227 AND cia.ORGANIZATION_ID = csllc.organization_id
3228 AND cia.COST_TYPE_ID = l_cost_type_id
3229 AND cia.BASED_ON_ROLLUP_FLAG = 1
3230 );
3231 END IF;
3232
3233 sql_stmt_num := 30;
3234 INSERT INTO BOM_BILL_OF_MATERIALS(
3235 assembly_item_id,
3236 organization_id,
3237 alternate_bom_designator,
3238 last_update_date,
3239 last_updated_by,
3240 creation_date,
3241 created_by,
3242 last_update_login,
3243 specific_assembly_comment,
3244 pending_from_ecn,
3245 attribute_category,
3246 attribute1,
3247 attribute2,
3248 attribute3,
3249 attribute4,
3250 attribute5,
3251 attribute6,
3252 attribute7,
3253 attribute8,
3254 attribute9,
3255 attribute10,
3256 attribute11,
3257 attribute12,
3258 attribute13,
3259 attribute14,
3260 attribute15,
3261 assembly_type,
3262 bill_sequence_id,
3263 common_bill_sequence_id,
3264 request_id,
3265 program_application_id,
3266 program_id,
3267 program_update_date,
3268 pk1_value,
3269 pk2_value,
3270 source_bill_sequence_id
3271 )
3272 select
3273 bbom.assembly_item_id,
3274 bbom.organization_id,
3275 l_snapshot_designator,
3276 p_rollup_date,
3277 l_last_updated_by,
3278 p_rollup_date,
3279 l_last_updated_by,
3280 l_last_updated_by,
3281 bbom.specific_assembly_comment,
3282 bbom.pending_from_ecn,
3283 bbom.attribute_category,
3284 bbom.attribute1,
3285 bbom.attribute2,
3286 bbom.attribute3,
3287 bbom.attribute4,
3288 bbom.attribute5,
3289 bbom.attribute6,
3290 bbom.attribute7,
3291 bbom.attribute8,
3292 bbom.attribute9,
3293 bbom.attribute10,
3294 bbom.attribute11,
3295 bbom.attribute12,
3296 bbom.attribute13,
3297 bbom.attribute14,
3298 bbom.attribute15,
3299 bbom.assembly_type,
3300 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
3301 bbom.common_bill_sequence_id,
3302 l_req_id,
3303 l_prg_appl_id,
3304 l_prg_id,
3305 l_prg_update_date,
3306 bbom.assembly_item_id,
3307 bbom.organization_id,
3308 BOM_INVENTORY_COMPONENTS_S.NEXTVAL
3309 FROM BOM_BILL_OF_MATERIALS bbom,
3310 CST_ITEM_COSTS cia,
3311 CST_SC_LOW_LEVEL_CODES csllc,
3312 cst_sc_sourcing_rules cssr
3313 WHERE csllc.ROLLUP_ID = l_rollup_id
3314 AND cssr.rollup_id(+) = l_rollup_id
3315 AND cssr.inventory_item_id (+)= csllc.inventory_item_id
3316 AND cssr.organization_id (+)= csllc.organization_id
3317 AND decode(cssr.source_type,2,1,NULL,1,0)=1
3321 AND cia.COST_TYPE_ID = l_cost_type_id
3318 AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
3319 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3320 AND cia.ORGANIZATION_ID = csllc.organization_id
3322 AND cia.BASED_ON_ROLLUP_FLAG = 1
3323 AND bbom.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
3324 AND bbom.ORGANIZATION_ID = csllc.organization_id
3325 AND((l_mfg_flag = 1
3326 AND
3327 bbom.ASSEMBLY_TYPE = 1)
3328 OR
3329 (l_mfg_flag = 2)
3330 )
3331 AND( (NVL(bbom.ALTERNATE_BOM_DESIGNATOR, 'NONE') = NVL(alt_bom_designator, 'NONE'))
3332 OR ((alt_bom_designator IS NOT NULL)
3333 AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL)
3334 AND NOT EXISTS
3335 (SELECT 'X'
3336 FROM BOM_BILL_OF_MATERIALS bbom1
3337 WHERE bbom.ASSEMBLY_ITEM_ID = bbom1.ASSEMBLY_ITEM_ID
3338 AND bbom.ORGANIZATION_ID = bbom1.ORGANIZATION_ID
3339 AND bbom1.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator
3340 AND((l_mfg_flag = 1
3341 AND
3342 bbom1.ASSEMBLY_TYPE = 1)
3343 OR
3344 (l_mfg_flag = 2)
3345 )
3346 ))
3347 )
3348 AND EXISTS(
3349 select 'ALternate Exist in the organizations'
3350 from bom_alternate_designators bad
3351 where bad.organization_id = csllc.organization_id
3352 and bad.alternate_designator_code = l_snapshot_designator
3353 )
3354 AND NOT EXISTS(
3355 select 'Bom exixts with alternate as snapshot'
3356 from bom_bill_of_materials bbom2
3357 where bbom2.assembly_item_id = cia.inventory_item_id
3358 and bbom2.organization_id = csllc.organization_id
3359 and bbom2.alternate_bom_designator = l_snapshot_designator
3360 );
3361
3362 sql_stmt_num := 40;
3363 INSERT INTO BOM_INVENTORY_COMPONENTS
3364 (
3365 OPERATION_SEQ_NUM,
3366 COMPONENT_ITEM_ID,
3367 LAST_UPDATE_DATE,
3368 LAST_UPDATED_BY,
3369 CREATION_DATE,
3370 CREATED_BY,
3371 LAST_UPDATE_LOGIN,
3372 ITEM_NUM,
3373 BASIS_TYPE,
3374 COMPONENT_QUANTITY,
3375 COMPONENT_YIELD_FACTOR,
3376 COMPONENT_REMARKS,
3377 EFFECTIVITY_DATE,
3378 CHANGE_NOTICE,
3379 IMPLEMENTATION_DATE,
3380 DISABLE_DATE,
3381 ATTRIBUTE_CATEGORY,
3382 ATTRIBUTE1,
3383 ATTRIBUTE2,
3384 ATTRIBUTE3,
3385 ATTRIBUTE4,
3386 ATTRIBUTE5,
3387 ATTRIBUTE6,
3388 ATTRIBUTE7,
3389 ATTRIBUTE8,
3390 ATTRIBUTE9,
3391 ATTRIBUTE10,
3392 ATTRIBUTE11,
3393 ATTRIBUTE12,
3394 ATTRIBUTE13,
3395 ATTRIBUTE14,
3396 ATTRIBUTE15,
3397 PLANNING_FACTOR,
3398 QUANTITY_RELATED,
3399 SO_BASIS,
3400 OPTIONAL,
3401 MUTUALLY_EXCLUSIVE_OPTIONS,
3402 INCLUDE_IN_COST_ROLLUP,
3403 CHECK_ATP,
3404 SHIPPING_ALLOWED,
3405 REQUIRED_TO_SHIP,
3406 REQUIRED_FOR_REVENUE,
3407 INCLUDE_ON_SHIP_DOCS,
3408 INCLUDE_ON_BILL_DOCS,
3409 LOW_QUANTITY,
3410 HIGH_QUANTITY,
3411 ACD_TYPE,
3412 OLD_COMPONENT_SEQUENCE_ID,
3413 COMPONENT_SEQUENCE_ID,
3414 BILL_SEQUENCE_ID,
3415 REQUEST_ID,
3416 PROGRAM_APPLICATION_ID,
3417 PROGRAM_ID,
3418 PROGRAM_UPDATE_DATE,
3419 WIP_SUPPLY_TYPE,
3420 OPERATION_LEAD_TIME_PERCENT,
3421 REVISED_ITEM_SEQUENCE_ID,
3422 SUPPLY_LOCATOR_ID,
3423 SUPPLY_SUBINVENTORY,
3424 PICK_COMPONENTS,
3425 BOM_ITEM_TYPE)
3426 SELECT
3427 bic.OPERATION_SEQ_NUM,
3428 bic.COMPONENT_ITEM_ID,
3429 p_rollup_date,
3430 l_last_updated_by,
3431 p_rollup_date,
3432 l_last_updated_by,
3433 l_last_updated_by,
3437 bic.COMPONENT_YIELD_FACTOR,
3434 bic.ITEM_NUM,
3435 bic.BASIS_TYPE,
3436 bic.COMPONENT_QUANTITY,
3438 bic.COMPONENT_REMARKS,
3439 p_revision_date,
3440 NULL,
3441 p_revision_date,
3442 NULL,
3443 bic.ATTRIBUTE_CATEGORY,
3444 bic.ATTRIBUTE1,
3445 bic.ATTRIBUTE2,
3446 bic.ATTRIBUTE3,
3447 bic.ATTRIBUTE4,
3448 bic.ATTRIBUTE5,
3449 bic.ATTRIBUTE6,
3450 bic.ATTRIBUTE7,
3451 bic.ATTRIBUTE8,
3452 bic.ATTRIBUTE9,
3453 bic.ATTRIBUTE10,
3454 bic.ATTRIBUTE11,
3455 bic.ATTRIBUTE12,
3456 bic.ATTRIBUTE13,
3457 bic.ATTRIBUTE14,
3458 bic.ATTRIBUTE15,
3459 bic.PLANNING_FACTOR,
3460 bic.QUANTITY_RELATED,
3461 bic.SO_BASIS,
3462 bic.OPTIONAL,
3463 bic.MUTUALLY_EXCLUSIVE_OPTIONS,
3464 bic.INCLUDE_IN_COST_ROLLUP,
3465 bic.CHECK_ATP,
3466 bic.SHIPPING_ALLOWED,
3467 bic.REQUIRED_TO_SHIP,
3468 bic.REQUIRED_FOR_REVENUE,
3469 bic.INCLUDE_ON_SHIP_DOCS,
3470 bic.INCLUDE_ON_BILL_DOCS,
3471 bic.LOW_QUANTITY,
3472 bic.HIGH_QUANTITY,
3473 NULL, /* ACD_TYPE */
3474 NULL,
3475 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
3476 bbom2.BILL_SEQUENCE_ID,
3477 DECODE(l_conc_flag, 1, req_id, NULL),
3478 DECODE(l_conc_flag, 1, p_prg_appl_id, NULL),
3479 DECODE(l_conc_flag, 1, p_prg_id, NULL),
3480 DECODE(l_conc_flag, 1,
3481 p_rollup_date, NULL),
3482 bic.WIP_SUPPLY_TYPE,
3483 bic.OPERATION_LEAD_TIME_PERCENT,
3484 NULL,
3485 bic.SUPPLY_LOCATOR_ID,
3486 bic.SUPPLY_SUBINVENTORY,
3487 bic.PICK_COMPONENTS,
3488 bic.BOM_ITEM_TYPE
3489 FROM BOM_BILL_OF_MATERIALS bbom,
3490 BOM_BILL_OF_MATERIALS bbom2,
3491 bom_inventory_components bic,
3492 CST_ITEM_COSTS cia,
3493 CST_SC_LOW_LEVEL_CODES csllc,
3494 cst_sc_sourcing_rules cssr
3495 WHERE csllc.ROLLUP_ID = l_rollup_id
3496 AND cssr.rollup_id(+) = l_rollup_id
3497 AND cssr.inventory_item_id (+)= csllc.inventory_item_id
3498 AND cssr.organization_id (+)= csllc.organization_id
3499 AND decode(cssr.source_type,2,1,NULL,1,0)=1
3500 AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
3501 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3502 AND cia.ORGANIZATION_ID = csllc.organization_id
3503 AND cia.COST_TYPE_ID = l_cost_type_id
3504 AND cia.BASED_ON_ROLLUP_FLAG = 1
3505 AND bbom.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
3506 AND bbom.ORGANIZATION_ID = csllc.organization_id
3507 AND( (NVL(bbom.ALTERNATE_BOM_DESIGNATOR, 'NONE') = NVL(alt_bom_designator, 'NONE')
3508 AND (nvl(l_snapshot_designator,'NONE') <> NVL(alt_bom_designator,'NONE')))
3509 OR ((alt_bom_designator IS NOT NULL)
3510 AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL)
3511 AND (NOT EXISTS
3512 (SELECT 'X'
3513 FROM BOM_BILL_OF_MATERIALS bbom1
3514 WHERE bbom.ASSEMBLY_ITEM_ID = bbom1.ASSEMBLY_ITEM_ID
3515 AND bbom.ORGANIZATION_ID = bbom1.ORGANIZATION_ID
3516 AND bbom1.ALTERNATE_BOM_DESIGNATOR =
3517 alt_bom_designator
3518 AND((l_mfg_flag = 1
3519 AND
3520 bbom1.ASSEMBLY_TYPE = 1)
3521 OR
3522 (l_mfg_flag = 2)
3523 )
3524 )
3525 or (NVL(l_snapshot_designator,'NONE') = NVL(alt_bom_designator,'NONE'))))
3526 )
3527 AND bic.bill_sequence_id = bbom.common_bill_sequence_id
3528 AND NVL( bic.eco_for_production, 2 ) = 2
3529
3530 /* Fix for BUG 1604207 */
3531 AND NVL( bic.acd_type, 1 ) <> 3
3532
3533 AND bbom2.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
3534 AND bbom2.ORGANIZATION_ID = csllc.organization_id
3535 AND bbom2.ALTERNATE_BOM_DESIGNATOR = l_snapshot_designator
3536 AND bic.EFFECTIVITY_DATE <=fnd_date.canonical_to_date(revision_date)
3537 AND NVL(bic.DISABLE_DATE,fnd_date.canonical_to_date(revision_date)+1)> fnd_date.canonical_to_date(revision_date)
3538 AND(((l_unimp_flag = 2)AND(bic.IMPLEMENTATION_DATE IS NOT NULL))
3539 OR((l_unimp_flag = 1)
3540 AND bic.EFFECTIVITY_DATE = (
3541 SELECT MAX(bic1.EFFECTIVITY_DATE)
3542 FROM BOM_INVENTORY_COMPONENTS bic1
3543 WHERE ((bic1.OPERATION_SEQ_NUM = bic.OPERATION_SEQ_NUM)
3544 OR(decode(bic1.IMPLEMENTATION_DATE, NULL,bic1.OLD_COMPONENT_SEQUENCE_ID,bic1.COMPONENT_SEQUENCE_ID) =decode(bic.IMPLEMENTATION_DATE, NULL,bic.OLD_COMPONENT_SEQUENCE_ID,bic.COMPONENT_SEQUENCE_ID))
3545 )
3546 AND bic1.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
3550 )
3547 AND bic1.COMPONENT_ITEM_ID = bic.COMPONENT_ITEM_ID
3548 AND bic1.EFFECTIVITY_DATE <=fnd_date.canonical_to_date(revision_date)
3549 AND NVL( bic1.eco_for_production, 2 ) = 2
3551 )
3552 )
3553 AND EXISTS (
3554 select 'ALternate Exist in the organization'
3555 from bom_alternate_designators bad
3556 where bad.organization_id = csllc.organization_id
3557 and bad.alternate_designator_code = l_snapshot_designator
3558 )
3559 AND NOT EXISTS(
3560 select 'Bom exists with alternate as snapshot'
3561 from bom_inventory_components bic2,
3562 bom_bill_of_materials bbom3
3563 where bbom3.organization_id = csllc.organization_id
3564 and bbom3.assembly_item_id = cia.inventory_item_id
3565 and bbom3.alternate_bom_designator = l_snapshot_designator
3566 and bic2.bill_sequence_id = bbom3.bill_sequence_id
3567 );
3568
3569 /* Update the common_bill_sequence_id to bill_sequence_id for the all */
3570 /* the asemblies headers were created for */
3571
3572 sql_stmt_num := 60;
3573 update bom_bill_of_materials bbom
3574 set bbom.common_bill_sequence_id = bbom.bill_sequence_id
3575 where EXISTS(
3576 select 1
3577 from cst_sc_low_level_codes csllc,
3578 cst_sc_sourcing_rules cssr
3579 where csllc.rollup_id = l_rollup_id
3580 and cssr.rollup_id(+) = l_rollup_id
3581 and cssr.inventory_item_id (+)= csllc.inventory_item_id
3582 and cssr.organization_id (+)= csllc.organization_id
3583 and decode(cssr.source_type,2,1,null,1,0)=1
3584 and decode(cssr.allocation_percent,0,0,null,1,1)=1
3585 and bbom.organization_id = csllc.organization_id
3586 and bbom.assembly_item_id = csllc.inventory_item_id
3587 )
3588 and bbom.alternate_bom_designator = l_snapshot_designator
3589 and (nvl(l_snapshot_designator,'NONE') <> nvl(alt_bom_designator,'NONE')
3590 or
3591 bbom.common_bill_sequence_id = (select common_bill_sequence_id
3592 from bom_bill_of_materials bbom2
3593 where bbom2.assembly_item_id = bbom.assembly_item_id
3594 and bbom2.organization_id = bbom.organization_id
3595 and bbom2.alternate_bom_designator is null
3596 )
3597 );
3598 return_code := 0;
3599 return (return_code);
3600
3601 EXCEPTION
3602 when others then
3603 return_code := SQLCODE;
3604 err_buf := 'SUPPLY_CHAIN_SNAPSHOT:' || 'S' || sql_stmt_num || ':'
3605 || substrb(sqlerrm,1,62);
3606 return(return_code);
3607 END supply_chain_snapshot;
3608 END CSTPSCCR;