[Home] [Help]
PACKAGE BODY: APPS.CSTPSCCR
Source
1 PACKAGE BODY CSTPSCCR AS
2 /* $Header: CSTSCCRB.pls 120.24.12020000.2 2012/07/11 13:18:48 vkatakam 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 /*+ INDEX (CIA CST_ITEM_COSTS_U1) */
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 /*+ INDEX (CIA CST_ITEM_COSTS_U1) */
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,
320 ORGANIZATION_ID,
321 COST_TYPE_ID,
322 LAST_UPDATE_DATE,
323 LAST_UPDATED_BY,
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
426 status := SQLCODE;
427 x_err_buf := 'CSTPSCCR.remove_rolledup_costs ' ||'stmt_num= '||l_stmt_num||':'|| substrb(sqlerrm,1,60);
428 return(status);
429
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
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);
572 else
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
705 SELECT inventory_item_id,
706 organization_id,
707 round_unit,
708 precision,
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,
851 l_org_id,
852 l_assignment_set_id,
853 l_buy_cost_type_id,
854 l_dest_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,
973 1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),1) *
974 DECODE(item_shrinkage_flag,1,
975 DECODE(bomres.BASIS_TYPE,4,1,5,1,6,1,
976 1/(1-NVL(cia.SHRINKAGE_RATE,0))),1)),
977 csllc.ext_precision),
978 br.COST_ELEMENT_ID, /* Resource cost element */
979 3, /* Rolled up */
980 DECODE(conc_flag, 1, req_id, NULL),
981 DECODE(conc_flag, 1, prgm_appl_id, NULL),
982 DECODE(conc_flag, 1, prgm_id, NULL),
983 DECODE(conc_flag, 1,
984 l_rollup_date, NULL)
985 FROM
986 CST_SC_LOW_LEVEL_CODES csllc,
987 MTL_PARAMETERS mp,
988 CST_ITEM_COSTS cia,
989 BOM_OPERATIONAL_ROUTINGS bor,
990 BOM_OPERATION_SEQUENCES bos,
991 BOM_OPERATION_RESOURCES bomres,
992 BOM_RESOURCES br,
993 CST_RESOURCE_COSTS crc
994 WHERE csllc.ROLLUP_ID = l_rollup_id
995 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
996 AND cia.ORGANIZATION_ID = csllc.organization_id
997 AND cia.COST_TYPE_ID = l_dest_cost_type_id
998 AND cia.BASED_ON_ROLLUP_FLAG = 1 /* Yes */
999 AND cia.INVENTORY_ASSET_FLAG = 1
1000 AND bor.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
1001 AND bor.ORGANIZATION_ID = cia.organization_id
1002 AND ((l_mfg_flag = 1
1003 AND
1004 bor.ROUTING_TYPE = 1)
1005 OR
1006 (l_mfg_flag = 2)
1007 )
1008 AND( NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none')
1009 =NVL(alt_rtg_designator, 'none')
1010 OR (
1011 (alt_rtg_designator IS NOT NULL)
1012 AND (bor.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
1013 AND NOT EXISTS
1014 (SELECT 'X'
1015 FROM BOM_OPERATIONAL_ROUTINGS bor1
1016 WHERE bor1.ASSEMBLY_ITEM_ID = bor.ASSEMBLY_ITEM_ID
1017 AND bor1.ORGANIZATION_ID = csllc.organization_id
1018 AND bor1.ALTERNATE_ROUTING_DESIGNATOR =
1019 alt_rtg_designator
1020 AND ((l_mfg_flag = 1
1021 AND
1022 bor1.ROUTING_TYPE = 1)
1023 OR
1024 (l_mfg_flag = 2)
1025 )
1026 )
1027 )
1028 )
1029 AND bos.ROUTING_SEQUENCE_ID = bor.COMMON_ROUTING_SEQUENCE_ID
1030
1031 /* Fix for BUG 1608765 */
1032 AND bos.EFFECTIVITY_DATE <= l_rev_datetime
1033 AND NVL( bos.DISABLE_DATE,
1034 l_rev_datetime + 1)
1035 >= l_rev_datetime /*Changed > to >= for bug 6389605*/
1036
1037 /* Right now, ECO does not support Op Yield */
1038 AND ( NVL( bos.include_in_rollup, 1 ) = 1 OR
1039 bos.change_notice is not null )
1040
1041 /* This section takes care of Unimplemented ECO Routings */
1042 AND (
1043 (
1044 unimp_flag = 2 AND
1045 bos.implementation_date is not null
1046 )
1047 OR
1048 (
1049 unimp_flag = 1 AND
1050 bos.effectivity_date =
1051 (
1052 select max( bos2.effectivity_date )
1053 from bom_operation_sequences bos2
1054 where bos2.routing_sequence_id = bos.routing_sequence_id
1055 and NVL( bos2.operation_type, 1 ) = NVL( bos.operation_type, 1 )
1056 and bos2.operation_seq_num = bos.operation_seq_num
1057
1058 /* Fix for BUG 1607662 */
1059 and bos2.EFFECTIVITY_DATE <=
1060 fnd_date.canonical_to_date( revision_date )
1061 )
1062 )
1063 )
1064
1065 AND NVL( bos.eco_for_production, 2 ) = 2
1066 AND bomres.OPERATION_SEQUENCE_ID = bos.OPERATION_SEQUENCE_ID
1067 AND NVL( bomres.acd_type, 1 ) <> 3
1068 AND br.RESOURCE_ID = bomres.RESOURCE_ID
1069 AND br.ORGANIZATION_ID = csllc.organization_id
1070 AND br.ALLOW_COSTS_FLAG = 1
1071 AND crc.RESOURCE_ID = bomres.RESOURCE_ID
1072 AND (
1073 crc.COST_TYPE_ID = l_dest_cost_type_id
1074 OR
1075 ( crc.COST_TYPE_ID = default_cost_type_id
1076 AND NOT EXISTS (SELECT 'X'
1077 FROM CST_RESOURCE_COSTS crc1
1078 WHERE crc1.RESOURCE_ID = bomres.RESOURCE_ID
1079 AND crc1.COST_TYPE_ID = l_dest_cost_type_id)
1080 )
1081 OR
1082 ( crc.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1083 AND NOT EXISTS (SELECT 'X'
1084 FROM CST_RESOURCE_COSTS crc2
1085 WHERE crc2.RESOURCE_ID = bomres.RESOURCE_ID
1086 AND crc2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
1087 )
1088 ) /* Supply chain enhancement: default valuation cost type */
1089 AND mp.ORGANIZATION_ID = csllc.organization_id;
1090
1091 sql_stmt_num := 65;
1092
1093 --======================================================================================
1094 -- Added the following Insert Statement as part of fix for Bug# 3967455 (foreport bug 4032733,4283663)
1095
1096 -- to Insert records into CICD for which Resource rates are not defined (in CST_RESOURCE_COSTS)
1097 -- but have OverHeads defined with Basis Type = 3 (i.e. Resource Unit based) only
1098 -- since for such Basis Type, the user need not assign any Resource Rate.
1099 -- This is added as a separate statement so as to avoid using OUTER Joins in the above Insert
1100 -- Statement (sql_stmt_num 60).
1101
1102 --NOTE : Any changes done to the Above Insert statement need to be incorporated into this
1103 -- Statement as well since they are for similar purpose and are majorly similar.
1104 --======================================================================================
1105 INSERT INTO CST_ITEM_COST_DETAILS (
1106 inventory_item_id,
1107 cost_type_id,
1108 last_update_date,
1109 last_updated_by,
1110 creation_date,
1111 created_by,
1112 last_update_login,
1113 organization_id,
1114 source_organization_id,
1115 operation_sequence_id,
1116 operation_seq_num,
1117 department_id,
1118 level_type,
1119 activity_id,
1120 resource_seq_num,
1121 resource_id,
1122 resource_rate,
1123 usage_rate_or_amount,
1124 basis_type,
1125 basis_factor,
1126 net_yield_or_shrinkage_factor,
1127 item_cost,
1128 cost_element_id,
1129 rollup_source_type,
1130 request_id,
1131 program_application_id,
1132 program_id,
1133 program_update_date)
1134 SELECT /*+ ORDERED INDEX(br BOM_RESOURCES_U2)*/
1135 bor.assembly_item_id,
1136 l_dest_cost_type_id,
1137 l_rollup_date,
1138 l_last_updated_by,
1139 l_rollup_date,
1140 l_last_updated_by,
1141 l_login_id,
1142 csllc.organization_id,
1143 csllc.organization_id,
1144 bos.operation_sequence_id,
1145 bos.operation_seq_num,
1146 bos.department_id,
1147 cm_this_level,
1148 bomres.activity_id,
1149 bomres.resource_seq_num,
1150 bomres.resource_id,
1151 0,
1152 bomres.usage_rate_or_amount,
1153 bomres.basis_type,
1154 DECODE(bomres.BASIS_TYPE,1,1,2, 1/DECODE(cia.lot_size,NULL,1,0,1,cia.lot_size),1),
1155 DECODE(item_shrinkage_flag,1,
1156 DECODE(bomres.basis_type,4,1,5,1,6,1,
1157 1/(1-NVL(cia.shrinkage_rate,0))),1),
1158 0,
1159 br.cost_element_id, /* resource cost element */
1160 3, /* Rolled up */
1161 DECODE(conc_flag, 1, req_id, NULL),
1162 DECODE(conc_flag, 1, prgm_appl_id, NULL),
1163 -100,
1164 DECODE(conc_flag, 1, l_rollup_date, NULL)
1165 FROM
1166 CST_SC_LOW_LEVEL_CODES csllc,
1167 MTL_PARAMETERS mp,
1168 CST_ITEM_COSTS cia,
1169 BOM_OPERATIONAL_ROUTINGS bor,
1170 BOM_OPERATION_SEQUENCES bos,
1171 BOM_OPERATION_RESOURCES bomres,
1172 BOM_RESOURCES br
1173 WHERE csllc.rollup_id = l_rollup_id
1174 AND cia.inventory_item_id = csllc.inventory_item_id
1175 AND cia.organization_id = csllc.organization_id
1176 AND cia.cost_type_id = l_dest_cost_type_id
1177 AND cia.based_on_rollup_flag = 1 /* yes */
1178 AND cia.inventory_asset_flag = 1
1179 AND bor.assembly_item_id = cia.inventory_item_id
1180 AND bor.organization_id = cia.organization_id
1181 AND ((l_mfg_flag = 1 AND bor.ROUTING_TYPE = 1) OR (l_mfg_flag = 2))
1182 AND( NVL(bor.alternate_routing_designator, 'none') = NVL(alt_rtg_designator, 'none')
1183 OR (
1184 alt_rtg_designator IS NOT NULL
1185 AND (bor.alternate_routing_designator IS NULL)
1186 AND NOT EXISTS
1187 (SELECT 'X'
1188 FROM bom_operational_routings bor1
1189 WHERE bor1.assembly_item_id = bor.assembly_item_id
1190 AND bor1.organization_id = csllc.organization_id
1191 AND bor1.alternate_routing_designator = alt_rtg_designator
1192 AND ((l_mfg_flag = 1 AND bor1.routing_type = 1) OR (l_mfg_flag = 2))
1193 )
1194 )
1195 )
1196 AND bos.routing_sequence_id = bor.common_routing_sequence_id
1197
1198 /* Fix for BUG 1608765 */
1199 AND bos.effectivity_date <= l_rev_datetime
1200 AND NVL( bos.disable_date, l_rev_datetime + 1) >= l_rev_datetime /*Changed > to >= for bug 6389605*/
1201
1202 /* Right now, ECO does not support Op Yield */
1203 AND ( NVL( bos.include_in_rollup, 1 ) = 1 OR bos.change_notice is not null )
1204
1205 /* This section takes care of Unimplemented ECO Routings */
1206 AND (
1207 (unimp_flag = 2 AND bos.implementation_date is not null)
1208 OR
1209 (unimp_flag = 1 AND bos.effectivity_date =
1210 (
1211 SELECT MAX( bos2.effectivity_date )
1212 FROM bom_operation_sequences bos2
1213 WHERE bos2.routing_sequence_id = bos.routing_sequence_id
1214 AND NVL( bos2.operation_type, 1 ) = NVL( bos.operation_type, 1 )
1215 AND bos2.operation_seq_num = bos.operation_seq_num
1216
1217 /* Fix for BUG 1607662 */
1218 AND bos2.effectivity_date <= fnd_date.canonical_to_date(revision_date)
1219 )
1220 )
1221 )
1222 AND NVL( bos.eco_for_production, 2 ) = 2
1223 AND bomres.operation_sequence_id = bos.operation_sequence_id
1224 AND NVL( bomres.acd_type, 1 ) <> 3
1225 AND br.RESOURCE_ID = bomres.RESOURCE_ID
1226 AND br.ORGANIZATION_ID = csllc.organization_id
1227 AND br.ALLOW_COSTS_FLAG = 1
1228
1229 -- To Exclude resource records that have rates defined
1230 AND NOT EXISTS (SELECT 'resource rate defined'
1231 FROM cst_resource_costs crc
1232 WHERE crc.resource_id = bomres.resource_id
1233 AND (crc.cost_type_id = l_dest_cost_type_id
1234 OR
1235 crc.COST_TYPE_ID = default_cost_type_id
1236 OR
1237 /* Supply chain enhancement: default valuation cost type */
1238 crc.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1239 )
1240 )
1241 -- To get Overhead records of Basis Type = 3 (i.e Resource Unit based OH) only
1242 AND EXISTS (SELECT 'overhead exists for resource rate not defined'
1243 FROM cst_resource_overheads cro,
1244 cst_department_overheads cdo
1245 WHERE cro.resource_id = bomres.resource_id
1246 AND (cro.cost_type_id = l_dest_cost_type_id
1247 OR
1248 cro.cost_type_id = default_cost_type_id
1249 OR
1250 cro.COST_TYPE_ID = DECODE(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1251 )
1252 AND cdo.department_id = bos.department_id
1253 AND cdo.overhead_id = cro.overhead_id
1254 AND cdo.basis_type = 3 --only for resource unit based OH
1255 AND cdo.rate_or_amount <> 0
1256 AND (
1257 cdo.cost_type_id = l_dest_cost_type_id
1258 OR
1259 cdo.cost_type_id = default_cost_type_id
1260 OR
1261 cdo.cost_type_id = DECODE(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id)
1262 )
1263 )
1264 AND mp.organization_id = csllc.organization_id;
1265
1266
1267 sql_stmt_num := 70;
1268 /*------------------------------------------------------------+
1269 | Calculate the this level overheads based on resources. |
1270 +------------------------------------------------------------*/
1271
1272 /*
1273 Modified the BASIS_FACTOR, NET_YIELD_OR_SHRINKAGE_FACTOR and ITEM_COST logic for FP bug 8304698
1274 BASIS_TYPE 4 - Resource Value
1275 BASIS_TYPE 3 - Resource Units
1276 USAGE_RATE_OR_AMOUNT * BASIS_FACTOR = Resource Units Factor
1277 USAGE_RATE_OR_AMOUNT * BASIS_FACTOR * RESOURCE_RATE = Resource Value Factor
1278 In Previous code, the NET_YIELD_OR_SHRINKAGE_FACTOR was wrongly populated as 1 for BASIS_TYPE 4,
1279 instead of 1/(1-CIC.SHRINKAGE_FACTOR)
1280 */
1281 INSERT INTO CST_ITEM_COST_DETAILS (
1282 INVENTORY_ITEM_ID,
1283 COST_TYPE_ID,
1284 LAST_UPDATE_DATE,
1285 LAST_UPDATED_BY,
1286 CREATION_DATE,
1287 CREATED_BY,
1288 LAST_UPDATE_LOGIN,
1289 SOURCE_ORGANIZATION_ID,
1290 ORGANIZATION_ID,
1291 OPERATION_SEQUENCE_ID,
1292 OPERATION_SEQ_NUM,
1293 DEPARTMENT_ID,
1294 LEVEL_TYPE,
1295 ACTIVITY_ID,
1296 RESOURCE_SEQ_NUM,
1297 RESOURCE_ID,
1298 RESOURCE_RATE,
1299 USAGE_RATE_OR_AMOUNT,
1300 BASIS_TYPE,
1301 BASIS_RESOURCE_ID,
1302 BASIS_FACTOR,
1303 NET_YIELD_OR_SHRINKAGE_FACTOR,
1304 ITEM_COST,
1305 COST_ELEMENT_ID,
1306 ROLLUP_SOURCE_TYPE,
1307 REQUEST_ID,
1308 PROGRAM_APPLICATION_ID,
1309 PROGRAM_ID,
1310 PROGRAM_UPDATE_DATE)
1311 SELECT cicd.INVENTORY_ITEM_ID,
1312 l_dest_cost_type_id,
1313 l_rollup_date,
1314 l_last_updated_by,
1315 l_rollup_date,
1316 l_last_updated_by,
1317 l_login_id,
1318 csllc.organization_id,
1319 csllc.organization_id,
1320 cicd.OPERATION_SEQUENCE_ID,
1321 cicd.OPERATION_SEQ_NUM,
1322 cicd.DEPARTMENT_ID,
1323 CM_THIS_LEVEL,
1324 cdo.ACTIVITY_ID,
1325 cicd.RESOURCE_SEQ_NUM,
1326 cdo.OVERHEAD_ID,
1327 NULL,
1328 cdo.RATE_OR_AMOUNT,
1329 cdo.BASIS_TYPE,
1330 cro.RESOURCE_ID,
1331 /* Modified for bug 6821381 */
1332 DECODE(cdo.BASIS_TYPE,
1333 4, NVL(cicd.RESOURCE_RATE, 1), 1) * cicd.USAGE_RATE_OR_AMOUNT * cicd.BASIS_FACTOR,
1334 DECODE(item_shrinkage_flag,1,
1335 1/(1-NVL(cia.SHRINKAGE_RATE,0)),1),
1336 ROUND((cdo.RATE_OR_AMOUNT *
1337 DECODE(cdo.BASIS_TYPE,
1338 4, NVL(cicd.RESOURCE_RATE, 1), 1) * cicd.USAGE_RATE_OR_AMOUNT * cicd.BASIS_FACTOR *
1339 DECODE(item_shrinkage_flag,1,
1340 1/(1-NVL(cia.SHRINKAGE_RATE,0)),1)),
1341 csllc.ext_precision),
1342 5, /* Overhead cost element */
1343 3, /* Rolled up */
1344 DECODE(conc_flag, 1, req_id, NULL),
1345 DECODE(conc_flag, 1, prgm_appl_id, NULL),
1346 DECODE(conc_flag, 1, prgm_id, NULL),
1347 DECODE(conc_flag, 1,
1348 l_rollup_date, NULL)
1349 FROM
1350 CST_SC_LOW_LEVEL_CODES csllc,
1351 CST_ITEM_COSTS cia,
1352 CST_ITEM_COST_DETAILS cicd,
1353 CST_RESOURCE_OVERHEADS cro,
1354 CST_DEPARTMENT_OVERHEADS cdo,
1355 MTL_PARAMETERS mp
1356 WHERE csllc.ROLLUP_ID = l_rollup_id
1357 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
1358 AND cia.ORGANIZATION_ID = csllc.organization_id
1359 AND cia.COST_TYPE_ID = l_dest_cost_type_id
1360 AND cia.BASED_ON_ROLLUP_FLAG = 1 /* YES */
1361 AND cia.INVENTORY_ASSET_FLAG = 1
1362 AND cicd.ORGANIZATION_ID = csllc.organization_id
1363 AND cicd.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
1364 AND cicd.COST_TYPE_ID = l_dest_cost_type_id
1365 AND cicd.RESOURCE_ID = cro.RESOURCE_ID
1366 AND (
1367 cro.COST_TYPE_ID = l_dest_cost_type_id
1368 OR
1369 ( cro.COST_TYPE_ID = default_cost_type_id
1370 AND NOT EXISTS (SELECT 'X'
1371 FROM CST_RESOURCE_OVERHEADS cro1
1372 WHERE cro1.RESOURCE_ID = cicd.RESOURCE_ID
1373 AND cro1.COST_TYPE_ID = l_dest_cost_type_id)
1374 )
1375 OR
1376 ( cro.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1377 AND NOT EXISTS (SELECT 'X'
1378 FROM CST_RESOURCE_OVERHEADS cro2
1379 WHERE cro2.RESOURCE_ID = cicd.RESOURCE_ID
1380 AND cro2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
1381 )
1382 ) /* Supply chain enhancement: default valuation cost type */
1383 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
1384 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
1385 AND mp.ORGANIZATION_ID = csllc.organization_id
1386 AND cdo.DEPARTMENT_ID = cicd.DEPARTMENT_ID
1387 AND cdo.OVERHEAD_ID = cro.OVERHEAD_ID
1388 AND cdo.BASIS_TYPE IN (3,4)
1389 AND cdo.RATE_OR_AMOUNT <> 0
1390 AND (
1391 cdo.COST_TYPE_ID = l_dest_cost_type_id
1392 OR
1393 ( cdo.COST_TYPE_ID = default_cost_type_id
1394 AND NOT EXISTS
1395 (SELECT 'X'
1396 FROM CST_DEPARTMENT_OVERHEADS cdo1
1397 WHERE cdo1.DEPARTMENT_ID = cicd.DEPARTMENT_ID
1398 AND cdo1.COST_TYPE_ID = l_dest_cost_type_id)
1399 )
1400 OR
1401 ( cdo.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1402 AND NOT EXISTS
1403 (SELECT 'X'
1404 FROM CST_DEPARTMENT_OVERHEADS cdo2
1405 WHERE cdo2.DEPARTMENT_ID = cicd.DEPARTMENT_ID
1406 AND cdo2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
1407 )
1408 ); /* Supply chain enhancement: default valuation cost type */
1409
1410
1411 sql_stmt_num := 80;
1412 /*------------------------------------------------------------+
1413 | Calculate the this level departmental overhead charges. |
1414 +------------------------------------------------------------*/
1415 INSERT INTO CST_ITEM_COST_DETAILS (
1416 INVENTORY_ITEM_ID,
1417 COST_TYPE_ID,
1418 LAST_UPDATE_DATE,
1419 LAST_UPDATED_BY,
1420 CREATION_DATE,
1421 CREATED_BY,
1422 LAST_UPDATE_LOGIN,
1423 SOURCE_ORGANIZATION_ID,
1424 ORGANIZATION_ID,
1425 OPERATION_SEQUENCE_ID,
1426 OPERATION_SEQ_NUM,
1427 DEPARTMENT_ID,
1428 LEVEL_TYPE,
1429 ACTIVITY_ID,
1430 RESOURCE_SEQ_NUM,
1431 RESOURCE_ID,
1432 RESOURCE_RATE,
1433 USAGE_RATE_OR_AMOUNT,
1434 BASIS_TYPE,
1435 BASIS_FACTOR,
1436 NET_YIELD_OR_SHRINKAGE_FACTOR,
1437 ITEM_COST,
1438 COST_ELEMENT_ID,
1439 ROLLUP_SOURCE_TYPE,
1440 REQUEST_ID,
1441 PROGRAM_APPLICATION_ID,
1442 PROGRAM_ID,
1443 PROGRAM_UPDATE_DATE)
1444 SELECT
1445 bor.ASSEMBLY_ITEM_ID,
1446 l_dest_cost_type_id,
1447 l_rollup_date,
1448 l_last_updated_by,
1449 l_rollup_date,
1450 l_last_updated_by,
1451 l_login_id,
1452 csllc.organization_id,
1453 csllc.organization_id,
1454 bos.OPERATION_SEQUENCE_ID,
1455 bos.OPERATION_SEQ_NUM,
1456 bos.DEPARTMENT_ID,
1457 CM_THIS_LEVEL,
1458 cdo.ACTIVITY_ID,
1459 NULL,
1460 cdo.OVERHEAD_ID,
1461 NULL,
1462 cdo.RATE_OR_AMOUNT,
1463 cdo.BASIS_TYPE,
1464 DECODE(cdo.BASIS_TYPE,1,1,2,
1465 1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),0),
1466 DECODE(item_shrinkage_flag,1,
1467 DECODE(cdo.BASIS_TYPE,4,1,5,1,6,1,
1468 1/(1-NVL(cia.SHRINKAGE_RATE,0))),1),
1469 ROUND((cdo.RATE_OR_AMOUNT *
1470 DECODE(cdo.BASIS_TYPE,1,1,2,
1471 1/DECODE(cia.LOT_SIZE,NULL,1,0,1,cia.LOT_SIZE),0) *
1472 DECODE(item_shrinkage_flag,1,
1473 DECODE(cdo.BASIS_TYPE,4,1,5,1,6,1,
1474 1/(1-NVL(cia.SHRINKAGE_RATE,0))),1)),
1475 csllc.ext_precision),
1476 5, /* overhead cost element */
1477 3, /* Rolled up */
1478 DECODE(conc_flag, 1, req_id, NULL),
1479 DECODE(conc_flag, 1, prgm_appl_id, NULL),
1480 DECODE(conc_flag, 1, prgm_id, NULL),
1481 DECODE(conc_flag, 1,
1482 l_rollup_date, NULL)
1483 FROM
1484 CST_SC_LOW_LEVEL_CODES csllc,
1485 CST_ITEM_COSTS cia,
1486 BOM_OPERATIONAL_ROUTINGS bor,
1487 BOM_OPERATION_SEQUENCES bos,
1488 CST_DEPARTMENT_OVERHEADS cdo,
1489 MTL_PARAMETERS mp
1490 WHERE csllc.ROLLUP_ID = l_rollup_id
1491 AND cia.BASED_ON_ROLLUP_FLAG (+) = 1 /* YES */
1492 AND cia.INVENTORY_ASSET_FLAG (+) = 1
1493 AND cia.INVENTORY_ITEM_ID (+) = csllc.INVENTORY_ITEM_ID
1494 AND cia.ORGANIZATION_ID (+) = csllc.organization_id
1495 AND cia.COST_TYPE_ID (+) = l_dest_cost_type_id
1496 AND cia.INVENTORY_ITEM_ID is not null
1497 AND bor.ASSEMBLY_ITEM_ID = csllc.INVENTORY_ITEM_ID
1498 AND bor.ORGANIZATION_ID = csllc.organization_id
1499 AND ((l_mfg_flag = 1
1500 AND
1501 bor.ROUTING_TYPE = 1)
1502 OR
1503 (l_mfg_flag = 2)
1504 )
1505 AND( NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none')
1506 =NVL(alt_rtg_designator, 'none')
1507 OR (
1508 (alt_rtg_designator IS NOT NULL)
1509 AND (bor.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
1510 AND NOT EXISTS
1511 (SELECT
1512 'X'
1513 FROM BOM_OPERATIONAL_ROUTINGS bor1
1514 WHERE bor1.ASSEMBLY_ITEM_ID = bor.ASSEMBLY_ITEM_ID
1515 AND bor1.ORGANIZATION_ID = csllc.organization_id
1516 AND bor1.ALTERNATE_ROUTING_DESIGNATOR =
1517 alt_rtg_designator
1518 AND ((l_mfg_flag = 1
1519 AND
1520 bor1.ROUTING_TYPE = 1)
1521 OR
1522 (l_mfg_flag = 2)
1523 )
1524 )
1525 )
1526 )
1527 AND bos.ROUTING_SEQUENCE_ID = bor.COMMON_ROUTING_SEQUENCE_ID
1528
1529 /* Fix for BUG 1608765 */
1530 AND bos.EFFECTIVITY_DATE <= l_rev_datetime
1531 AND NVL( bos.DISABLE_DATE,
1532 l_rev_datetime + 1)
1533 >= l_rev_datetime /*Changed > to >= for bug 6389605*/
1534
1535 /* Right now, ECO does not support Op Yield */
1536 AND ( NVL( bos.include_in_rollup, 1 ) = 1 OR
1537 bos.change_notice is not null )
1538
1539 /* This section takes care of Unimplemented ECO Routings */
1540 AND (
1541 (
1542 unimp_flag = 2 AND
1543 bos.implementation_date is not null
1544 )
1545 OR
1546 (
1547 unimp_flag = 1 AND
1548 bos.effectivity_date =
1549 (
1550 select max( bos2.effectivity_date )
1551 from bom_operation_sequences bos2
1552 where bos2.routing_sequence_id = bos.routing_sequence_id
1553 and NVL( bos2.operation_type, 1 ) = NVL( bos.operation_type, 1 )
1554 and bos2.operation_seq_num = bos.operation_seq_num
1555
1556 /* Fix for BUG 1607662 */
1557 and bos2.EFFECTIVITY_DATE <=
1558 fnd_date.canonical_to_date( revision_date )
1559 )
1560 )
1561 )
1562
1563
1564 AND NVL( bos.eco_for_production, 2 ) = 2
1565 AND cdo.DEPARTMENT_ID = bos.DEPARTMENT_ID
1566 AND cdo.BASIS_TYPE IN (1,2)
1567 AND cdo.RATE_OR_AMOUNT <> 0
1568 AND (
1569 cdo.COST_TYPE_ID = l_dest_cost_type_id
1570 OR
1571 ( cdo.COST_TYPE_ID = default_cost_type_id
1572 AND NOT EXISTS
1573 (SELECT 'X'
1574 FROM CST_DEPARTMENT_OVERHEADS cdo1
1575 WHERE cdo1.DEPARTMENT_ID = cdo.DEPARTMENT_ID
1576 AND cdo1.COST_TYPE_ID = l_dest_cost_type_id)
1577 )
1578 OR
1579 ( cdo.COST_TYPE_ID = decode(mp.PRIMARY_COST_METHOD, 1, 1, mp.AVG_RATES_COST_TYPE_ID)
1580 AND NOT EXISTS
1581 (SELECT 'X'
1582 FROM CST_DEPARTMENT_OVERHEADS cdo2
1583 WHERE cdo2.DEPARTMENT_ID = cdo.DEPARTMENT_ID
1584 AND cdo2.COST_TYPE_ID in (l_dest_cost_type_id,default_cost_type_id))
1585 )
1586 ) /* Supply chain enhancement: default valuation cost type */
1587 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
1588 AND mp.ORGANIZATION_ID = csllc.organization_id
1589 /* Fix for bug 2142170 */
1590 /* -----------------------------------------------------------+
1591 | If the routing is a flow routing, then the operation type |
1592 | should be an event. |
1593 +-------------------------------------------------------------*/
1594 AND ((nvl(bor.cfm_routing_flag, 2) <> 1)
1595 OR (nvl(bor.cfm_routing_flag, 2) = 1 and nvl(bos.operation_type, 1) = 1))
1596 ;
1597
1598 sql_stmt_num := 90;
1599 /*------------------------------------------------------------+
1600 | Calculate this level material overhead with basis of |
1601 | resource units and resource value and applied to this |
1602 | level associated resources. |
1603 +------------------------------------------------------------*/
1604 OPEN cllc1_cur;
1605 LOOP
1606 FETCH cllc1_cur BULK COLLECT INTO
1607 l_inv_item_tbl,
1608 l_org_id_tbl,
1609 l_roun_tbl,
1610 l_prec_tbl,
1611 l_ext_tbl
1612 LIMIT 1000;
1613
1614 FORALL i IN l_inv_item_tbl.first..l_inv_item_tbl.last
1615 UPDATE CST_ITEM_COST_DETAILS cicd
1616 SET (BASIS_FACTOR,
1617 ITEM_COST) =
1618 (SELECT DECODE(cicd.BASIS_TYPE,
1619 3,NVL(SUM(ROUND(cicd1.USAGE_RATE_OR_AMOUNT*
1620 cicd1.BASIS_FACTOR,
1621 l_ext_tbl(i))),0),
1622 4,NVL(SUM(cicd1.ITEM_COST),0)),
1623 DECODE(cicd.BASIS_TYPE,
1624 3,ROUND(cicd.USAGE_RATE_OR_AMOUNT*
1625 cicd.NET_YIELD_OR_SHRINKAGE_FACTOR *
1626 NVL(SUM(ROUND(cicd1.USAGE_RATE_OR_AMOUNT*
1627 cicd1.BASIS_FACTOR,
1628 l_ext_tbl(i))),0),
1629 l_ext_tbl(i)),
1630 4,ROUND(cicd.USAGE_RATE_OR_AMOUNT*
1631 NVL(SUM(cicd1.ITEM_COST),0),l_ext_tbl(i)))
1632 FROM
1633 CST_ITEM_COST_DETAILS cicd1,
1634 CST_RESOURCE_OVERHEADS cro
1635 WHERE cicd1.INVENTORY_ITEM_ID = cicd.INVENTORY_ITEM_ID
1636 AND cicd1.ORGANIZATION_ID = l_org_id_tbl(i)
1637 AND cicd1.COST_TYPE_ID = l_dest_cost_type_id
1638 AND cicd1.RESOURCE_ID = cro.RESOURCE_ID
1639 AND cicd1.LEVEL_TYPE = CM_THIS_LEVEL
1640 AND cicd1.COST_ELEMENT_ID in (3,4)
1641 AND cro.OVERHEAD_ID = cicd.RESOURCE_ID
1642 AND cro.COST_TYPE_ID = l_dest_cost_type_id)
1643 WHERE cicd.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
1644 AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
1645 AND cicd.ROLLUP_SOURCE_TYPE = 1 /* user entered */
1646 AND cicd.COST_ELEMENT_ID = 2 /* material overhead */
1647 AND cicd.BASIS_TYPE in (3,4) /* resource units, value */
1648 AND cicd.COST_TYPE_ID = l_dest_cost_type_id;
1649
1650 EXIT WHEN cllc1_cur%NOTFOUND;
1651 END LOOP;
1652
1653 CLOSE cllc1_cur;
1654
1655 /*-----------------------------------------------------------+
1656 |Snapshot The BOM's if the cost type option demands so |
1657 +-----------------------------------------------------------*/
1658 sql_stmt_num :=95;
1659 if (report_option<>CM_REPORT_ONLY) AND (bom_snapshot_flag = 1) THEN
1660 return_code := supply_chain_snapshot(
1661 l_rollup_id,
1662 l_dest_cost_type_id,
1663 l_mfg_flag,
1664 alt_bom_designator,
1665 conc_flag,
1666 unimp_flag,
1667 revision_date,
1668 l_last_updated_by,
1669 rollup_date,
1670 req_id,
1671 prgm_appl_id,
1672 prgm_id,
1673 err_buf);
1674 if return_code <> 0 then
1675 raise snapshot_error;
1676 end if;
1677 end if;
1678
1679 sql_stmt_num := 100;
1680
1681 /*------------------------------------------------------------+
1682 | Determine the maximum level code in the rollup. |
1683 +------------------------------------------------------------*/
1684 SELECT NVL(MAX(LOW_LEVEL_CODE),0)
1685 INTO max_level
1686 FROM CST_SC_LOW_LEVEL_CODES
1687 WHERE ROLLUP_ID = l_rollup_id;
1688
1689 /*------------------------------------------------------------+
1690 | Determine the minimum level code in the rollup. |
1691 +------------------------------------------------------------*/
1692 min_level := 0; /* Always */
1693
1694
1695 sql_stmt_num := 110;
1696 /*------------------------------------------------------------+
1697 | Calculate the previous level costs for the assemblies |
1698 | level by level. |
1699 +------------------------------------------------------------*/
1700
1701 <<calc>>
1702 for cur_level in min_level..max_level loop
1703
1704 -- Bug 3590153: removed the join with cst_sc_low_level_codes
1705 -- and added the cursor for loop as below
1706
1707 OPEN cllc10_cur(cur_level);
1708 LOOP
1709 FETCH cllc10_cur BULK COLLECT INTO
1710 l_inv_item_tbl,
1711 l_org_id_tbl,
1712 l_roun_tbl,
1713 l_prec_tbl,
1714 l_ext_tbl,
1715 l_pcm_tbl
1716 LIMIT 1000;
1717
1718 sql_stmt_num := 112;
1719
1720 FORALL i IN l_inv_item_tbl.first..l_inv_item_tbl.last
1721 INSERT INTO cst_item_cost_details
1722 (INVENTORY_ITEM_ID,
1723 COST_TYPE_ID,
1724 LAST_UPDATE_DATE,
1725 LAST_UPDATED_BY,
1726 CREATION_DATE,
1727 CREATED_BY,
1728 LAST_UPDATE_LOGIN,
1729 SOURCE_ORGANIZATION_ID,
1730 ORGANIZATION_ID,
1731 OPERATION_SEQ_NUM,
1732 LEVEL_TYPE,
1733 ACTIVITY_ID,
1734 RESOURCE_ID,
1735 RESOURCE_RATE,
1736 USAGE_RATE_OR_AMOUNT,
1737 BASIS_TYPE,
1738 BASIS_FACTOR,
1739 NET_YIELD_OR_SHRINKAGE_FACTOR,
1740 ITEM_COST,
1741 COST_ELEMENT_ID,
1742 ROLLUP_SOURCE_TYPE,
1743 REQUEST_ID,
1744 PROGRAM_APPLICATION_ID,
1745 PROGRAM_ID,
1746 PROGRAM_UPDATE_DATE,
1747 BASIS_RESOURCE_ID,
1748 OPERATION_SEQUENCE_ID,
1749 RESOURCE_SEQ_NUM
1750 )
1751
1752 SELECT
1753
1754 l_inv_item_tbl(i),
1755 l_dest_cost_type_id,
1756 TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS'),
1757 l_last_updated_by,
1758 TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS'),
1759 l_last_updated_by,
1760 l_login_id,
1761 l_org_id_tbl(i),
1762 l_org_id_tbl(i),
1763 DECODE(pl_operation_flag, 1, max(bic.operation_seq_num), NULL),
1764
1765 -- If subassembly is phantom, and use_phatom_routings
1766 -- for resources, overhead and OSP, should be this level
1767 -- rather than previous level
1768
1769 DECODE(cicd.level_type, CM_THIS_LEVEL,
1770 DECODE(bp.use_phantom_routings, 1,
1771 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1772 DECODE(cicd.cost_element_id,3,CM_THIS_LEVEL,4,CM_THIS_LEVEL,5,CM_THIS_LEVEL,CM_PREVIOUS_LEVEL),
1773 CM_PREVIOUS_LEVEL),
1774 CM_PREVIOUS_LEVEL),
1775 CM_PREVIOUS_LEVEL),
1776
1777 DECODE(pl_activity_flag, 1, max(cicd.ACTIVITY_ID), NULL),
1778 DECODE(pl_cost_code_flag, 1, max(cicd.RESOURCE_ID), NULL),
1779 DECODE(pl_cost_code_flag, 1, max(cicd.RESOURCE_RATE), NULL),
1780 /* USAGE_RATE_OR_AMOUNT Start*/
1781 DECODE(pl_cost_code_flag, 1,
1782 SUM(cicd.USAGE_RATE_OR_AMOUNT*cicd.BASIS_FACTOR*cicd.NET_YIELD_OR_SHRINKAGE_FACTOR*
1783 DECODE(bp.use_phantom_routings, 1,
1784 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1785 DECODE(cicd.cost_element_id,
1786 1, DECODE(cicd.basis_type, 2, bic.COMPONENT_QUANTITY/cicd.basis_factor, bic.COMPONENT_QUANTITY),
1787 2, DECODE(cicd.basis_type, 2, bic.COMPONENT_QUANTITY/cicd.basis_factor, bic.COMPONENT_QUANTITY),
1788 3, DECODE(cicd.basis_type, 2, 1/decode(cicd.basis_factor,0,1,cicd.basis_factor), bic.COMPONENT_QUANTITY),
1789 4, DECODE(cicd.basis_type, 2, 1/decode(cicd.basis_factor,0,1,cicd.basis_factor), bic.COMPONENT_QUANTITY),
1790 5, DECODE(cicd.basis_type, 2, 1/decode(cicd.basis_factor,0,1,cicd.basis_factor),
1791 3, 1/decode(cicd.basis_factor,0,1,cicd.basis_factor),
1792 4, 1/decode(cicd.basis_factor,0,1,cicd.basis_factor),
1793 bic.COMPONENT_QUANTITY)),
1794 bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY)*
1795 NVL(bic.PLANNING_FACTOR / 100,1)/DECODE(comp_yield_flag,1,bic.COMPONENT_YIELD_FACTOR,1)),
1796 SUM(cicd.ITEM_COST *
1797 -- 1/(1-nvl(cia_assy.SHRINKAGE_RATE,0)) * --FP of bug 8304698
1798 DECODE(bp.use_phantom_routings, 1,
1799 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1800 DECODE(cicd.cost_element_id,
1801 3, DECODE(cicd.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
1802 4, DECODE(cicd.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
1803 5, DECODE(cicd.basis_type, 2, 1,
1804 3, DECODE(bomres.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
1805 4, DECODE(bomres.basis_type, 2, 1, bic.COMPONENT_QUANTITY),
1806 bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY),
1807 bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY)*
1808 nvl(bic.PLANNING_FACTOR / 100,1)/DECODE(comp_yield_flag,1,bic.COMPONENT_YIELD_FACTOR,1))),
1809 /* USAGE_RATE_OR_AMOUNT END*/
1810 /* Start changes for LBM */
1811 /* Update BASIS_TYPE if phantom - Bug 2076990*/
1812 DECODE(pl_cost_code_flag, 1,
1813 DECODE(bp.USE_PHANTOM_ROUTINGS, 1,
1814 DECODE(NVL(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)), 6,
1815 DECODE(cicd.BASIS_TYPE, 2, 2, 3, 3, 4, 4, 1),
1816 DECODE(bic.basis_type,2, 2, 1)),
1817 DECODE(bic.basis_type,2, 2, 1)),
1818 DECODE(bic.basis_type,2, 2, 1)),
1819 /* BASIS_FACTOR changed for LBM project */
1820 DECODE(pl_cost_code_flag, 1,
1821 DECODE(bp.use_phantom_routings, 1,
1822 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1823 DECODE(cicd.cost_element_id,
1824 1, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
1825 2, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
1826 3, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
1827 4, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
1828 5, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE,
1829 3, DECODE(bomres.basis_type, 2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size, cicd.basis_factor),
1830 4, DECODE(bomres.basis_type, 2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size, cicd.basis_factor),
1831 1)),
1832 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
1833 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
1834 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
1835 /* Net Yield or Shrinkage factor not changed */
1836 max(1/(1-NVL(cia_assy.SHRINKAGE_RATE,0))),
1837 /* If subassembly is a phantom which uses a lot based resource,
1838 then number of such subassemblies is not used in the calculation
1839 of the final cost of the assembly due to the lot based resource
1840 - Bug 2076990*/
1841 /* Item Cost calculation changed for LBM */
1842 /* Previous Level Costs would have basis type as 1 and hence uses default cost calulation logic */
1843 /* If phatom material is Yes, then only this level material costs would be included in cost of item.
1844 This is ensured in WHERE clause filter */
1845 ROUND((SUM(cicd.ITEM_COST*1/(1-NVL(cia_assy.SHRINKAGE_RATE,0))/
1846 DECODE(comp_yield_flag,1,bic.COMPONENT_YIELD_FACTOR,1)*NVL(bic.PLANNING_FACTOR / 100,1)*
1847 DECODE(bp.use_phantom_routings, 1,
1848 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1849 DECODE(cicd.cost_element_id,
1850 1, DECODE(cicd.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
1851 2, DECODE(cicd.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
1852 3, DECODE(cicd.basis_type, 2, 1/(cia_assy.LOT_SIZE*cicd.basis_factor), bic.COMPONENT_QUANTITY),
1853 4, DECODE(cicd.basis_type, 2, 1/(cia_assy.LOT_SIZE*cicd.basis_factor), bic.COMPONENT_QUANTITY),
1854 5, DECODE(cicd.basis_type,
1855 2, 1/(cia_assy.LOT_SIZE*cicd.basis_factor),
1856 3, DECODE(bomres.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
1857 4, DECODE(bomres.basis_type, 2, cia_comp.lot_size/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY),
1858 bic.COMPONENT_QUANTITY), bic.COMPONENT_QUANTITY),
1859 DECODE(bic.basis_type,2, bic.COMPONENT_QUANTITY/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY)),
1860 DECODE(bic.basis_type,2, bic.COMPONENT_QUANTITY/cia_assy.LOT_SIZE, bic.COMPONENT_QUANTITY)))) , l_ext_tbl(i)),
1861 /* end changes for LBM */
1862 DECODE(pl_element_flag, 1,max(cicd.COST_ELEMENT_ID),1),
1863 3,
1864 DECODE(conc_flag, 1, req_id, NULL),
1865 DECODE(conc_flag, 1, prgm_appl_id, NULL),
1866 DECODE(conc_flag, 1, prgm_id, NULL),
1867 DECODE(conc_flag, 1, TO_DATE(rollup_date,'YYYY/MM/DD HH24:MI:SS'), NULL),
1868 DECODE(pl_cost_code_flag, 1,
1869 DECODE(bp.use_phantom_routings, 1,
1870 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1871 DECODE(cicd.cost_element_id, 3, cicd.basis_resource_id,
1872 4, cicd.basis_resource_id,
1873 5, cicd.basis_resource_id, null), null), null), null),
1874 DECODE(pl_cost_code_flag, 1,
1875 DECODE(bp.use_phantom_routings, 1,
1876 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1877 DECODE(cicd.cost_element_id, 3, cicd.operation_sequence_id,
1878 4, cicd.operation_sequence_id,
1879 5, cicd.operation_sequence_id, null), null), null), null),
1880 DECODE(pl_cost_code_flag, 1,
1881 DECODE(bp.use_phantom_routings, 1,
1882 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
1883 DECODE(cicd.cost_element_id, 3, cicd.resource_seq_num,
1884 4, cicd.resource_seq_num,
1885 5, cicd.resource_seq_num, null), null), null), null)
1886 FROM BOM_BILL_OF_MATERIALS bbom,
1887 BOM_INVENTORY_COMPONENTS bic,
1888 CST_ITEM_COSTS cia_assy,
1889 CST_ITEM_COST_DETAILS cicd,
1890 CST_ITEM_COSTS cia_comp,
1891 BOM_PARAMETERS bp,
1892 mtl_system_items msi,
1893 BOM_OPERATION_RESOURCES bomres
1894
1895 WHERE cia_assy.ORGANIZATION_ID = l_org_id_tbl(i)
1896 AND cia_assy.COST_TYPE_ID = l_dest_cost_type_id
1897 AND cia_assy.BASED_ON_ROLLUP_FLAG = 1
1898 AND cia_assy.INVENTORY_ASSET_FLAG = 1
1899 AND cia_assy.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
1900 AND bbom.ORGANIZATION_ID = l_org_id_tbl(i)
1901 AND bbom.ASSEMBLY_ITEM_ID = l_inv_item_tbl(i)
1902 AND((l_mfg_flag = 1
1903 AND
1904 bbom.ASSEMBLY_TYPE = 1)
1905 OR
1906 (l_mfg_flag = 2)
1907 )
1908 AND((bbom.ALTERNATE_BOM_DESIGNATOR IS NULL
1909 AND
1910 alt_bom_designator IS NULL)
1911 OR
1912 (alt_bom_designator IS NOT NULL
1913 AND
1914 bbom.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator)
1915 OR ((alt_bom_designator IS NOT NULL)
1916 AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL)
1917 AND NOT EXISTS
1918 (SELECT /*+ PUSH_SUBQ */ 'X' -- Added hint for 5678464
1919 FROM BOM_BILL_OF_MATERIALS bbom1
1920 WHERE bbom1.ASSEMBLY_ITEM_ID =
1921 bbom.ASSEMBLY_ITEM_ID
1922 AND bbom1.ORGANIZATION_ID = bbom.ORGANIZATION_ID
1923 AND bbom1.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator
1924 AND((l_mfg_flag = 1
1925 AND
1926 bbom1.ASSEMBLY_TYPE = 1)
1927 OR
1928 (l_mfg_flag = 2)
1929 )
1930 )))
1931 -- Added for 5678464
1932 AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL
1933 OR
1934 bbom.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator
1935 )
1936
1937 AND bic.BILL_SEQUENCE_ID = bbom.COMMON_BILL_SEQUENCE_ID
1938 AND bic.INCLUDE_IN_COST_ROLLUP = 1
1939 AND NVL( bic.eco_for_production, 2 ) = 2
1940
1941 /* Fix for BUG 1604207 */
1942 AND NVL( bic.acd_type, 1 ) <> 3
1943
1944 AND (bic.EFFECTIVITY_DATE <= l_rev_datetime)
1945 AND NVL(bic.DISABLE_DATE, l_rev_datetime+1) > l_rev_datetime
1946 AND (
1947 ( unimp_flag = 2 AND (bic.IMPLEMENTATION_DATE IS NOT NULL))
1948 OR (
1949 unimp_flag = 1
1950 AND bic.EFFECTIVITY_DATE =
1951 (SELECT /*+ PUSH_SUBQ */ MAX(bic1.EFFECTIVITY_DATE) -- Added hint for 5678464
1952 FROM BOM_INVENTORY_COMPONENTS bic1
1953 WHERE bic1.COMPONENT_ITEM_ID = bic.COMPONENT_ITEM_ID
1954 AND NVL( bic1.eco_for_production, 2 ) = 2
1955 AND bic1.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
1956 AND ((bic1.OPERATION_SEQ_NUM = bic.OPERATION_SEQ_NUM)
1957 OR
1958 (decode(bic1.IMPLEMENTATION_DATE, NULL,
1959 bic1.OLD_COMPONENT_SEQUENCE_ID,
1960 bic1.COMPONENT_SEQUENCE_ID) =
1961 decode(bic.IMPLEMENTATION_DATE, NULL,
1962 bic.OLD_COMPONENT_SEQUENCE_ID,
1963 bic.COMPONENT_SEQUENCE_ID)
1964 )
1965 )
1966 AND bic1.EFFECTIVITY_DATE <= l_rev_datetime
1967 )
1968 )
1969 )
1970
1971 -- Bug 2381452 and 3063633, phantom's TL resource/overhead/osp controlled by the use_phantom_routings flag
1972 -- phantom's TL material/moh costs controlled by the profile
1973 -- Bug 2455770. Do not rollup the TL yielded resource costs for a phantom subassembly since the resources are used at the parent
1974
1975 AND bp.ORGANIZATION_ID(+) = bbom.organization_id
1976 AND msi.ORGANIZATION_ID = l_org_id_tbl(i)
1977 AND msi.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
1978 /* Bug 4547027 - Added the check to ignore the cost of inactive items. */
1979 AND NVL(msi.inventory_item_status_code, 'NOT'||bp.bom_delete_status_code) <> NVL(bp.bom_delete_status_code,' ')
1980 AND (
1981 ( nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)) <> 6)
1982 OR
1983 ( nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)) = 6
1984 AND cicd.yielded_cost is null
1985 AND ((NVL(bp.use_phantom_routings,2) = 1 and cicd.cost_element_id NOT in (1,2))
1986 OR
1987 (l_phantom_mat = 1 and cicd.cost_element_id in (1,2))
1988 OR
1989 cicd.level_type = 2
1990 )
1991 )
1992 )
1993 AND cia_comp.ORGANIZATION_ID = l_org_id_tbl(i)
1994 AND cia_comp.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
1995 AND (
1996 cia_comp.COST_TYPE_ID = l_dest_cost_type_id
1997 OR
1998 ( cia_comp.COST_TYPE_ID = default_cost_type_id
1999 AND NOT EXISTS (
2000 SELECT /*+ PUSH_SUBQ */ 'X' -- Added hint for 5678464
2001 FROM CST_ITEM_COSTS cia1
2002 WHERE cia1.ORGANIZATION_ID = l_org_id_tbl(i)
2003 AND cia1.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
2004 AND cia1.COST_TYPE_ID = l_dest_cost_type_id)
2005 )
2006 OR
2007 ( cia_comp.COST_TYPE_ID = l_pcm_tbl(i)
2008 AND NOT EXISTS (
2009 SELECT /*+ PUSH_SUBQ */ 'X' -- Added hint for 5678464
2010 FROM CST_ITEM_COSTS cia2
2011 WHERE cia2.ORGANIZATION_ID = l_org_id_tbl(i)
2012 AND cia2.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
2013 AND cia2.COST_TYPE_ID in (l_dest_cost_type_id, default_cost_type_id))
2014 )
2015 )
2016 AND cia_comp.COST_TYPE_ID IN (l_dest_cost_type_id,default_cost_type_id,l_pcm_tbl(i)) -- Added for 5678464
2017 AND cia_comp.INVENTORY_ASSET_FLAG = 1
2018
2019 AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
2020 AND cicd.INVENTORY_ITEM_ID = cia_comp.INVENTORY_ITEM_ID
2021 AND cicd.COST_TYPE_ID = cia_comp.COST_TYPE_ID
2022 AND cicd.operation_sequence_id = bomres.operation_sequence_id (+)
2023 AND cicd.resource_seq_num = bomres.resource_seq_num (+)
2024 AND cicd.basis_resource_id = bomres.resource_id (+)
2025
2026 GROUP BY
2027 l_inv_item_tbl(i),
2028 l_org_id_tbl(i),
2029 l_ext_tbl(i),
2030 DECODE(pl_operation_flag, 1, bic.operation_seq_num, NULL),
2031 DECODE(pl_cost_code_flag, 1, cicd.RESOURCE_ID, NULL),
2032 DECODE(pl_cost_code_flag, 1, DECODE(cicd.COST_ELEMENT_ID, 1, NULL, cicd.RESOURCE_RATE), NULL),
2033 DECODE(pl_element_flag, 1, cicd.COST_ELEMENT_ID, NULL),
2034 DECODE(pl_activity_flag, 1, cicd.ACTIVITY_ID, NULL),
2035 /*Start changes for LBM */
2036 /* basis type */
2037 DECODE(PL_COST_CODE_FLAG, 1,
2038 DECODE(BP.USE_PHANTOM_ROUTINGS, 1,
2039 DECODE(NVL(BIC.WIP_SUPPLY_TYPE, NVL(msi.WIP_SUPPLY_TYPE,1)),6,
2040 DECODE(CICD.BASIS_TYPE, 2, 2, 3, 3, 4, 4,1),
2041 DECODE(bic.basis_type,2,2,1)),
2042 DECODE(bic.basis_type,2,2,1)),
2043 DECODE(bic.basis_type,2,2,1)),
2044 /* basis_factor changed */
2045 DECODE(pl_cost_code_flag, 1,
2046 DECODE(bp.use_phantom_routings, 1,
2047 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2048 DECODE(cicd.cost_element_id,
2049 1, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2050 2, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2051 3, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2052 4, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE, 1),
2053 5, DECODE(cicd.basis_type, 2, 1/cia_assy.LOT_SIZE,
2054 3, DECODE(bomres.basis_type,
2055 2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
2056 cicd.basis_factor),
2057 4, DECODE(bomres.basis_type,
2058 2, (cicd.basis_factor*cia_comp.lot_size)/cia_assy.lot_size,
2059 cicd.basis_factor),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 DECODE(bic.basis_type,2, 1/cia_assy.LOT_SIZE,1)),
2063 /* end changes for LBM */
2064 DECODE(pl_cost_code_flag, 1,
2065 DECODE(bp.use_phantom_routings, 1,
2066 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2067 DECODE(cicd.cost_element_id, 3, cicd.basis_resource_id,
2068 4, cicd.basis_resource_id,
2069 5, cicd.basis_resource_id, null), null), null), null),
2070 DECODE(pl_cost_code_flag, 1,
2071 DECODE(bp.use_phantom_routings, 1,
2072 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2073 DECODE(cicd.cost_element_id, 3, cicd.operation_sequence_id,
2074 4, cicd.operation_sequence_id,
2075 5, cicd.operation_sequence_id, null), null), null), null),
2076 DECODE(pl_cost_code_flag, 1,
2077 DECODE(bp.use_phantom_routings, 1,
2078 DECODE(nvl(bic.WIP_SUPPLY_TYPE, nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2079 DECODE(cicd.cost_element_id, 3, cicd.resource_seq_num,
2080 4, cicd.resource_seq_num,
2081 5, cicd.resource_seq_num, null), null), null), null),
2082 1/(1-NVL(cia_assy.SHRINKAGE_RATE,0)),
2083 DECODE(cicd.level_type, CM_THIS_LEVEL,
2084 DECODE(bp.use_phantom_routings, 1,
2085 DECODE(nvl(bic.WIP_SUPPLY_TYPE, Nvl(msi.WIP_SUPPLY_TYPE,1)),6,
2086 DECODE(cicd.cost_element_id,3,CM_THIS_LEVEL,4,CM_THIS_LEVEL,5,CM_THIS_LEVEL,CM_PREVIOUS_LEVEL),
2087 CM_PREVIOUS_LEVEL),
2088 CM_PREVIOUS_LEVEL), CM_PREVIOUS_LEVEL);
2089
2090
2091 EXIT WHEN cllc10_cur%NOTFOUND;
2092 END LOOP; -- Bug 3590153
2093
2094 close cllc10_cur;
2095
2096
2097 /*------------------------------------------------------------+
2098 | Operation Yield Changes |
2099 +-------------------------------------------------------------*/
2100
2101 FOR csllc_level_org IN csllc_org_level_cur(cur_level)
2102 LOOP
2103
2104 sql_stmt_num := 115;
2105
2106 l_wsm_enabled := 'N';
2107
2108 select UPPER(wsm_enabled_flag)
2109 into l_wsm_enabled
2110 from mtl_parameters
2111 where organization_id = csllc_level_org.organization_id;
2112
2113 if (l_wsm_enabled = 'Y') then
2114
2115 sql_stmt_num := 118;
2116
2117
2118 l_oerr_code := process_sc_rollup_op_yields (
2119 csllc_level_org.ext_precision,
2120 l_rollup_id,
2121 conc_flag,
2122 req_id,
2123 prgm_appl_id,
2124 prgm_id,
2125 l_last_updated_by,
2126 alt_rtg_designator,
2127 /* Bug 2305807. Need Effectivity Date */
2128 revision_date,
2129 csllc_level_org.organization_id,
2130 cur_level,
2131 l_dest_cost_type_id,
2132 -- Obtain error message for bug 3097347
2133 err_buf);
2134 end if;
2135
2136 IF (l_oerr_code <> 0) THEN
2137 /* Propagation for Bug 2347889.
2138 Need to return error_code */
2139 return_code := l_oerr_code;
2140 RAISE STANDARD_ERROR;
2141 END IF;
2142
2143 END LOOP;
2144
2145 /*------------------------------------------------------------+
2146 | Calculate the material overhead charges based on total |
2147 | item value level by level. |
2148 +-------------------------------------------------------------*/
2149
2150 /* Now for all cases of rollup including specific rollup and all items rollup the cursor processing path will be taken */
2151
2152 FOR inv_cursor IN cllc2_cur(cur_level) LOOP
2153
2154
2155 sql_stmt_num := 120;
2156 l_oerr_code := 0;
2157
2158 IF (l_assignment_set_id IS NOT NULL) THEN
2159
2160 l_oerr_code := CSTPSCCM.merge_costs (
2161 l_rollup_id,
2162 l_dest_cost_type_id,
2163 l_buy_cost_type_id,
2164 inv_cursor.inventory_item_id,
2165 inv_cursor.organization_id,
2166 l_assignment_set_id,
2167 err_buf,
2168 buy_cost_detail -- SCAPI: option to preserve buy cost details
2169 );
2170 END IF;
2171
2172 IF (l_oerr_code <> 0) THEN
2173 return_code := l_oerr_code;
2174 RAISE STANDARD_ERROR;
2175 END IF;
2176
2177
2178 sql_stmt_num := 130;
2179
2180
2181 UPDATE CST_ITEM_COST_DETAILS cicd
2182 SET (ITEM_COST,BASIS_FACTOR) =
2183 (SELECT ROUND((cicd.USAGE_RATE_OR_AMOUNT *
2184 NVL(SUM(cicd1.ITEM_COST),0)),inv_cursor.ext_precision),
2185 NVL(SUM(cicd1.ITEM_COST),0) /
2186 NVL(cicd.net_yield_or_shrinkage_factor,1) /
2187 DECODE(cicd.resource_rate,0 , 1 , NVL(cicd.resource_rate,1))
2188 FROM CST_ITEM_COST_DETAILS cicd1
2189 WHERE cicd1.ORGANIZATION_ID = inv_cursor.organization_id
2190 AND cicd1.COST_TYPE_ID = l_dest_cost_type_id
2191 AND cicd1.INVENTORY_ITEM_ID = cicd.INVENTORY_ITEM_ID
2192 AND cicd1.BASIS_TYPE <> 5 /* Total Value */
2193 AND NOT (cicd1.LEVEL_TYPE = CM_THIS_LEVEL
2194 AND
2195 cicd1.COST_ELEMENT_ID = 2))
2196 WHERE cicd.INVENTORY_ITEM_ID = inv_cursor.inventory_item_id
2197 AND cicd.ORGANIZATION_ID = inv_cursor.organization_id
2198 AND cicd.COST_TYPE_ID = l_dest_cost_type_id
2199 AND cicd.BASIS_TYPE = 5 /* Total Value */
2200 AND cicd.COST_ELEMENT_ID = 2;
2201
2202
2203 END LOOP;
2204
2205
2206 END LOOP calc; /* FOR LOOP ENDS HERE */
2207
2208
2209
2210 sql_stmt_num := 140;
2211
2212
2213 IF (pl_operation_flag = 1) THEN
2214 OPEN cllc3_cur(l_rollup_id);
2215 LOOP
2216 FETCH cllc3_cur BULK COLLECT INTO
2217 l_inv_item_tbl,
2218 l_org_id_tbl,
2219 l_roun_tbl,
2220 l_prec_tbl,
2221 l_ext_tbl
2222 LIMIT 1000;
2223
2224 FORALL i IN l_inv_item_tbl.first..l_inv_item_tbl.last
2225 UPDATE CST_ITEM_COST_DETAILS cicd
2226 SET (OPERATION_SEQUENCE_ID,
2227 DEPARTMENT_ID) =
2228 (SELECT bos.OPERATION_SEQUENCE_ID,
2229 bos.DEPARTMENT_ID
2230 FROM BOM_OPERATIONAL_ROUTINGS bor,
2231 BOM_OPERATION_SEQUENCES bos
2232 WHERE bor.ASSEMBLY_ITEM_ID = cicd.INVENTORY_ITEM_ID
2233 AND bor.ORGANIZATION_ID = l_org_id_tbl(i)
2234 AND ((l_mfg_flag = 1 AND bor.ROUTING_TYPE = 1)
2235 OR
2236 (l_mfg_flag = 2)
2237 )
2238 AND bor.COMMON_ROUTING_SEQUENCE_ID = bos.ROUTING_SEQUENCE_ID
2239 AND bos.OPERATION_SEQ_NUM = cicd.OPERATION_SEQ_NUM
2240 AND (NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none') =NVL(alt_rtg_designator, 'none')
2241 OR(alt_rtg_designator IS NOT NULL
2242 AND (bor.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
2243 AND NOT EXISTS
2244 (SELECT 'X'
2245 FROM BOM_OPERATIONAL_ROUTINGS bor1
2246 WHERE bor1.ASSEMBLY_ITEM_ID = bor.ASSEMBLY_ITEM_ID
2247 AND bor1.ORGANIZATION_ID = l_org_id_tbl(i)
2248 AND bor1.ALTERNATE_ROUTING_DESIGNATOR =alt_rtg_designator
2249 AND ((l_mfg_flag = 1 AND bor1.ROUTING_TYPE = 1)
2250 OR
2251 (l_mfg_flag = 2)
2252 )
2253 )
2254 )
2255 )
2256
2257 /* Fix for BUG 1608765 */
2258 AND bos.EFFECTIVITY_DATE <= l_rev_datetime
2259 AND NVL( bos.DISABLE_DATE, l_rev_datetime + 1) >= l_rev_datetime /*Changed > to >= for bug 6389605*/
2260
2261 -- This extra clause is because for
2262 -- Flow Manufacturing a new column has been added to the
2263 -- primary key of bos
2264 AND nvl(bos.operation_type, 1) = 1
2265
2266 /* Right now, ECO does not support Op Yield */
2267 AND ( NVL( bos.include_in_rollup, 1 ) = 1
2268 OR bos.change_notice is not null
2269 )
2270
2271 /* This section takes care of Unimplemented ECO Routings */
2272 AND ((unimp_flag = 2 AND bos.implementation_date is not null)
2273 OR
2274 ( unimp_flag = 1
2275 AND bos.effectivity_date =
2276 (SELECT MAX(bos2.effectivity_date )
2277 FROM bom_operation_sequences bos2
2278 WHERE bos2.routing_sequence_id = bos.routing_sequence_id
2279 AND NVL(bos2.operation_type, 1) = NVL(bos.operation_type, 1)
2280 AND bos2.operation_seq_num = bos.operation_seq_num
2281
2282 /* Fix for BUG 1607662 */
2283 AND bos2.EFFECTIVITY_DATE <= fnd_date.canonical_to_date( revision_date )
2284 )
2285 )
2286 )
2287
2288 AND NVL( bos.eco_for_production, 2 ) = 2
2289 -- Added for Bug: 1078491 by ADEY
2290 -- No to select disabled rows with same op seq num.
2291 AND NVL(DISABLE_DATE, SYSDATE+1) >= SYSDATE /*Changed > to >= for bug 6389605*/
2292 )
2293
2294 WHERE cicd.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
2295 AND cicd.COST_TYPE_ID = l_dest_cost_type_id
2296 AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
2297 AND cicd.LEVEL_TYPE = CM_PREVIOUS_LEVEL;
2298
2299 EXIT WHEN cllc3_cur%NOTFOUND;
2300 END LOOP;
2301 CLOSE cllc3_cur;
2302 END IF;
2303
2304
2305 sql_stmt_num := 150;
2306 /*------------------------------------------------------------+
2307 | Update rows in CST_ITEM_COSTS which had costs altered. |
2308 | Because we need to include denormalized cost information. |
2309 +------------------------------------------------------------*/
2310
2311 OPEN cllc_cur;
2312 LOOP
2313 FETCH cllc_cur BULK COLLECT INTO
2314 l_inv_item_tbl,
2315 l_org_id_tbl,
2316 l_roun_tbl,
2317 l_prec_tbl,
2318 l_ext_tbl
2319 LIMIT 1000;
2320
2321 FORALL i IN l_inv_item_tbl.first..l_inv_item_tbl.last
2322 UPDATE CST_ITEM_COSTS cic
2323 SET ( PL_MATERIAL ,
2324 PL_MATERIAL_OVERHEAD ,
2325 PL_RESOURCE ,
2326 PL_OUTSIDE_PROCESSING ,
2327 PL_OVERHEAD ,
2328 TL_MATERIAL ,
2329 TL_MATERIAL_OVERHEAD ,
2330 TL_RESOURCE ,
2331 TL_OUTSIDE_PROCESSING ,
2332 TL_OVERHEAD ,
2333 MATERIAL_COST ,
2334 MATERIAL_OVERHEAD_COST ,
2335 RESOURCE_COST ,
2336 OUTSIDE_PROCESSING_COST ,
2337 OVERHEAD_COST ,
2338 PL_ITEM_COST ,
2339 TL_ITEM_COST ,
2340 ITEM_COST ,
2341 UNBURDENED_COST ,
2342 BURDEN_COST ,
2343 REQUEST_ID ,
2344 PROGRAM_APPLICATION_ID ,
2345 PROGRAM_ID ,
2346 PROGRAM_UPDATE_DATE ,
2347 LAST_UPDATE_DATE ,
2348 LAST_UPDATED_BY,
2349 ROLLUP_ID,
2350 ASSIGNMENT_SET_ID) =
2351
2352 ( SELECT
2353 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,
2354 cicd.ITEM_COST,0),0)),
2355 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,2,
2356 cicd.ITEM_COST,0),0)),
2357 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,3,
2358 cicd.ITEM_COST,0),0)),
2359 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,4,
2360 cicd.ITEM_COST,0),0)),
2361 SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,5,
2362 cicd.ITEM_COST,0),0)),
2363 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,1,
2364 cicd.ITEM_COST,0),0)),
2365 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,2,
2366 cicd.ITEM_COST,0),0)),
2367 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,3,
2368 cicd.ITEM_COST,0),0)),
2369 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,4,
2370 cicd.ITEM_COST,0),0)),
2371 SUM(DECODE(LEVEL_TYPE,1,DECODE(COST_ELEMENT_ID,5,
2372 cicd.ITEM_COST,0),0)),
2373 SUM(DECODE(COST_ELEMENT_ID,1,cicd.ITEM_COST)),
2374 SUM(DECODE(COST_ELEMENT_ID,2,cicd.ITEM_COST)),
2375 SUM(DECODE(COST_ELEMENT_ID,3,cicd.ITEM_COST)),
2376 SUM(DECODE(COST_ELEMENT_ID,4,cicd.ITEM_COST)),
2377 SUM(DECODE(COST_ELEMENT_ID,5,cicd.ITEM_COST)),
2378 SUM(DECODE(LEVEL_TYPE,2,cicd.ITEM_COST,0)),
2379 SUM(DECODE(LEVEL_TYPE,1,cicd.ITEM_COST,0)),
2380 NVL(SUM(cicd.ITEM_COST),0),
2381 SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,2,
2382 cicd.ITEM_COST,0),
2383 cicd.ITEM_COST)),
2384 SUM(DECODE(COST_ELEMENT_ID, 2,DECODE(LEVEL_TYPE,1,
2385 cicd.ITEM_COST,0),0)),
2386 DECODE(conc_flag, 1, req_id, NULL),
2387 DECODE(conc_flag, 1, prgm_appl_id, NULL),
2388 DECODE(conc_flag, 1, prgm_id, NULL),
2389 DECODE(conc_flag, 1, l_rollup_date, NULL),
2390 l_rollup_date,
2391 l_last_updated_by,
2392 l_rollup_id,
2393 l_assignment_set_id
2394 FROM CST_ITEM_COST_DETAILS cicd
2395 WHERE cicd.INVENTORY_ITEM_ID = cic.INVENTORY_ITEM_ID
2396 AND cicd.ORGANIZATION_ID = l_org_id_tbl(i)
2397 AND cicd.COST_TYPE_ID = l_dest_cost_type_id)
2398
2399 WHERE cic.INVENTORY_ITEM_ID = l_inv_item_tbl(i)
2400 AND COST_TYPE_ID = l_dest_cost_type_id
2401 AND cic.ORGANIZATION_ID = l_org_id_tbl(i);
2402
2403 EXIT WHEN cllc_cur%NOTFOUND;
2404 END LOOP;
2405 CLOSE cllc_cur;
2406
2407 return_code := 0;
2408 err_buf := 'CSTPSCCR.cstsccru ' || 'Success';
2409 return(return_code);
2410
2411 EXCEPTION
2412 when STANDARD_ERROR then
2413 return(return_code);
2414 when SNAPSHOT_ERROR then
2415 return(return_code);
2416 when OTHERS then
2417 return_code := SQLCODE;
2418 err_buf := 'CSTPSCR.cstsccru ' || 'stmt_num = ' || sql_stmt_num || ': '
2419 || substrb(sqlerrm,1,200);
2420 return(return_code);
2421
2422 END cstsccru;
2423
2424
2425 PROCEDURE populate_markup_costs (
2426 l_rollup_id IN NUMBER,
2427 l_item_id IN NUMBER,
2428 l_org_id IN NUMBER,
2429 l_assignment_set_id IN NUMBER,
2430 l_buy_cost_type_id IN NUMBER,
2431 l_dest_cost_type_id IN NUMBER,
2432 x_err_code OUT NOCOPY NUMBER,
2433 x_err_buf OUT NOCOPY VARCHAR2)
2434 IS
2435 l_stmt_num NUMBER;
2436 l_src_org_id NUMBER;
2437 l_markup_code NUMBER;
2438 l_markup NUMBER;
2439 STANDARD_ERROR EXCEPTION;
2440
2441 CURSOR markup_org_cur(
2442 l_rollup_id IN NUMBER,
2443 l_inventory_item_id IN NUMBER,
2444 l_dest_organization_id IN NUMBER,
2445 l_assignment_set_id IN NUMBER) IS
2446 SELECT
2447 CSSR.source_organization_id
2448 FROM
2449 CST_SC_SOURCING_RULES CSSR
2450 WHERE
2451 CSSR.ROLLUP_ID = l_rollup_id
2452 AND CSSR.inventory_item_id = l_inventory_item_id
2453 AND CSSR.organization_id = l_dest_organization_id
2454 AND CSSR.assignment_set_id = l_assignment_set_id
2455 AND CSSR.source_type = 1;
2456
2457
2458
2459 BEGIN
2460
2461 l_stmt_num := 10;
2462
2463 OPEN markup_org_cur(
2464 l_rollup_id,
2465 l_item_id,
2466 l_org_id,
2467 l_assignment_set_id);
2468 LOOP
2469 FETCH markup_org_cur INTO l_src_org_id;
2470 IF (markup_org_cur%NOTFOUND) THEN
2471 EXIT;
2472 END IF;
2473
2474
2475
2476 CSTPSCHO.get_markup_hook
2477 (
2478 l_rollup_id,
2479 l_item_id,
2480 l_org_id,
2481 l_src_org_id,
2482 l_dest_cost_type_id,
2483 l_buy_cost_type_id,
2484 l_markup,
2485 l_markup_code,
2486 x_err_code,
2487 x_err_buf
2488 );
2489
2490 IF (x_err_code <> 0) THEN
2491 RAISE STANDARD_ERROR;
2492 NULL;
2493 END IF;
2494
2495 UPDATE CST_SC_SOURCING_RULES CSSR
2496 SET CSSR.MARKUP = l_markup,
2497 CSSR.MARKUP_CODE = l_markup_code
2498 WHERE CSSR.ROLLUP_ID = l_rollup_id
2499 AND CSSR.inventory_item_id = l_item_id
2500 AND CSSR.organization_id = l_org_id
2501 AND CSSR.assignment_set_id = l_assignment_set_id
2502 AND CSSR.source_type = 1
2503 AND CSSR.source_organization_id = l_src_org_id
2504 AND l_markup_code <> -1
2505 AND l_markup_code IN (2,3); -- Req value or percent only
2506
2507
2508 UPDATE CST_SC_SOURCING_RULES
2509 SET (MARKUP,
2510 MARKUP_CODE) =
2511 (SELECT
2512 INTERORG_TRNSFR_CHARGE_PERCENT,
2513 MATL_INTERORG_TRANSFER_CODE
2514 FROM MTL_INTERORG_PARAMETERS MIP
2515 WHERE MIP.from_organization_id = l_src_org_id
2516 AND MIP.to_organization_id = l_org_id
2517 AND MIP.MATL_INTERORG_TRANSFER_CODE = 4
2518 -- to Support only predefined %
2519 )
2520 WHERE
2521 ROLLUP_ID = l_rollup_id
2522 AND inventory_item_id = l_item_id
2523 AND organization_id = l_org_id
2524 AND assignment_set_id = l_assignment_set_id
2525 AND source_type = 1
2526 AND source_organization_id = l_src_org_id
2527 AND markup_code IS NULL
2528 AND markup IS NULL;
2529
2530
2531
2532 END LOOP;
2533 CLOSE markup_org_cur;
2534
2535 x_err_code := 0;
2536 x_err_buf := 'CSTSCBOM.populate_markup_costs:' || 'S' || l_stmt_num || ': SUCCESS';
2537 return;
2538
2539 EXCEPTION
2540 when others then
2541 x_err_code := SQLCODE;
2542 x_err_buf := 'CSTSCBOM.populate_markup_costs:' || 'S' || l_stmt_num || ' :' || substrb(sqlerrm,1,62);
2543 return;
2544
2545
2546 END populate_markup_costs;
2547
2548 PROCEDURE populate_shipping_costs (
2549 l_rollup_id IN NUMBER,
2550 l_item_id IN NUMBER,
2551 l_org_id IN NUMBER,
2552 l_assignment_set_id IN NUMBER,
2553 l_buy_cost_type_id IN NUMBER,
2554 l_dest_cost_type_id IN NUMBER,
2555 x_err_code OUT NOCOPY NUMBER,
2556 x_err_buf OUT NOCOPY VARCHAR2)
2557 IS
2558 l_stmt_num NUMBER;
2559 l_src_org_id NUMBER;
2560 x_ship_charge_code NUMBER;
2561 x_ship_charge NUMBER;
2562 l_ship_method VARCHAR2(30);
2563 STANDARD_ERROR EXCEPTION;
2564
2565 CURSOR shipping_cost_cur(
2566 l_rollup_id IN NUMBER,
2567 l_inventory_item_id IN NUMBER,
2568 l_dest_organization_id IN NUMBER,
2569 l_assignment_set_id IN NUMBER) IS
2570 SELECT
2571 CSSR.source_organization_id,
2572 CSSR.ship_method
2573 FROM
2574 CST_SC_SOURCING_RULES CSSR
2575 WHERE
2576 CSSR.ROLLUP_ID = l_rollup_id
2577 AND CSSR.inventory_item_id = l_inventory_item_id
2578 AND CSSR.organization_id = l_dest_organization_id
2579 AND CSSR.assignment_set_id = l_assignment_set_id
2580 AND CSSR.source_type = 1;
2581
2582
2583
2584 BEGIN
2585
2586 l_stmt_num := 10;
2587
2588 OPEN shipping_cost_cur(
2589 l_rollup_id,
2590 l_item_id,
2591 l_org_id,
2592 l_assignment_set_id);
2593 LOOP
2594 FETCH shipping_cost_cur INTO l_src_org_id, l_ship_method;
2595 IF (shipping_cost_cur%NOTFOUND) THEN
2596 EXIT;
2597 END IF;
2598 l_stmt_num := 20;
2599
2600
2601 CSTPSCHO.get_shipping_hook
2602 (
2603 l_rollup_id,
2604 l_item_id,
2605 l_org_id,
2606 l_src_org_id,
2607 l_dest_cost_type_id,
2608 l_buy_cost_type_id,
2609 l_ship_method,
2610 x_ship_charge,
2611 x_ship_charge_code,
2612 x_err_code,
2613 x_err_buf
2614 );
2615
2616 IF (x_err_code <> 0) THEN
2617 RAISE STANDARD_ERROR;
2618 NULL;
2619 END IF;
2620 l_stmt_num := 30;
2621
2622 UPDATE CST_SC_SOURCING_RULES CSSR
2623 SET CSSR.SHIP_CHARGE = x_ship_charge,
2624 CSSR.SHIP_CHARGE_CODE = x_ship_charge_code
2625 WHERE CSSR.ROLLUP_ID = l_rollup_id
2626 AND CSSR.inventory_item_id = l_item_id
2627 AND CSSR.organization_id = l_org_id
2628 AND CSSR.assignment_set_id = l_assignment_set_id
2629 AND CSSR.source_type = 1
2630 AND CSSR.source_organization_id = l_src_org_id
2631 AND x_ship_charge <> -1
2632 AND x_ship_charge_code IN (2,3); -- Req value or percent only
2633
2634
2635
2636 END LOOP;
2637 CLOSE shipping_cost_cur;
2638
2639 x_err_code := 0;
2640 x_err_buf := 'CSTSCBOM.populate_shipping_costs:' || 'S' || l_stmt_num || ': SUCCESS';
2641 return;
2642
2643 EXCEPTION
2644 when others then
2645 x_err_code := SQLCODE;
2646 x_err_buf := 'CSTSCBOM.populate_shipping_costs:' || 'S' || l_stmt_num || ' :' || substrb(sqlerrm,1,62);
2647 return;
2648
2649
2650 END populate_shipping_costs;
2651
2652
2653 PROCEDURE populate_buy_costs (
2654 l_rollup_id IN NUMBER,
2655 l_assignment_set_id IN NUMBER,
2656 l_item_id IN NUMBER,
2657 l_org_id IN NUMBER,
2658 l_buy_cost_type_id IN NUMBER,
2659 x_err_code OUT NOCOPY NUMBER,
2660 x_err_buf OUT NOCOPY VARCHAR2)
2661 IS
2662 l_stmt_num NUMBER;
2663 l_vendor_id NUMBER;
2664 l_buy_cost NUMBER;
2665 l_curr_rowid ROWID;
2666 l_site_id NUMBER;
2667 l_ship_method NUMBER;
2668 default_cost_type_id NUMBER;
2669 STANDARD_ERROR EXCEPTION;
2670
2671 CURSOR vendors_cur( l_rollup_id IN NUMBER,
2672 l_inventory_item_id IN NUMBER,
2673 l_dest_organization_id IN NUMBER,
2674 l_assignment_set_id IN NUMBER)
2675 IS
2676 SELECT
2677 CSSR.ROWID,
2678 CSSR.vendor_id,
2679 CSSR.vendor_site_id,
2680 CSSR.ship_method
2681 FROM
2682 CST_SC_SOURCING_RULES CSSR
2683 WHERE
2684 CSSR.ROLLUP_ID = l_rollup_id
2685 AND CSSR.inventory_item_id = l_inventory_item_id
2686 AND CSSR.organization_id = l_dest_organization_id
2687 AND CSSR.assignment_set_id = l_assignment_set_id
2688 AND CSSR.source_type = 3
2689 FOR UPDATE;
2690
2691
2692
2693 BEGIN
2694 OPEN vendors_cur(l_rollup_id,l_item_id,l_org_id,l_assignment_set_id);
2695 LOOP
2696 FETCH vendors_cur INTO l_curr_rowid,l_vendor_id,l_site_id,
2697 l_ship_method;
2698 IF (vendors_cur%NOTFOUND) THEN
2699 EXIT;
2700 END IF;
2701
2702 l_buy_cost := CSTPSCHO.get_buy_cost_hook
2703 (
2704 l_rollup_id,
2705 l_assignment_set_id,
2706 l_item_id,
2707 l_org_id,
2708 l_vendor_id,
2709 l_site_id,
2710 l_ship_method,
2711 x_err_code,
2712 x_err_buf
2713 );
2714
2715 IF (x_err_code <> 0) THEN
2716 NULL;
2717 RAISE STANDARD_ERROR;
2718 END IF;
2719
2720 UPDATE CST_SC_SOURCING_RULES CSSR
2721 SET CSSR.ITEM_COST = l_buy_cost,
2722 CSSR.BUY_COST_FLAG = 'Y'
2723 WHERE CSSR.ROLLUP_ID = l_rollup_id
2724 AND CSSR.inventory_item_id = l_item_id
2725 AND CSSR.organization_id = l_org_id
2726 AND CSSR.assignment_set_id = l_assignment_set_id
2727 AND CSSR.source_type = 3
2728 AND l_buy_cost <> -1
2729 AND CSSR.ROWID = l_curr_rowid;
2730
2731 END LOOP;
2732 CLOSE vendors_cur;
2733
2734 /* Supply chain enhancement: default cost type */
2735 SELECT DEFAULT_COST_TYPE_ID
2736 INTO default_cost_type_id
2737 FROM CST_COST_TYPES
2738 WHERE COST_TYPE_ID = l_buy_cost_type_id;
2739
2740 UPDATE CST_SC_SOURCING_RULES
2741 SET (ITEM_COST,
2742 BUY_COST_FLAG) =
2743 (SELECT NVL(SUM(CICD.ITEM_COST),0),
2744 'Y'
2745 FROM CST_ITEM_COST_DETAILS CICD, MTL_PARAMETERS MP
2746 WHERE CICD.inventory_item_id = l_item_id
2747 AND CICD.organization_id = l_org_id
2748 AND MP.organization_id = l_org_id
2749 AND (
2750 CICD.cost_type_id = l_buy_cost_type_id
2751 OR
2752 (
2753 CICD.cost_type_id = default_cost_type_id
2754 AND NOT EXISTS (
2755 SELECT 'X'
2756 FROM CST_ITEM_COSTS cia1
2757 WHERE cia1.inventory_item_id = l_item_id
2758 AND cia1.organization_id = l_org_id
2759 AND cia1.cost_type_id = l_buy_cost_type_id)
2760 )
2761 OR
2762 (
2763 CICD.cost_type_id = MP.primary_cost_method
2764 AND NOT EXISTS (
2765 SELECT 'X'
2766 FROM CST_ITEM_COSTS cia2
2767 WHERE cia2.inventory_item_id = l_item_id
2768 AND cia2.organization_id = l_org_id
2769 AND cia2.cost_type_id in (l_buy_cost_type_id, default_cost_type_id))
2770 )
2771 )
2772 ) /* Supply chain enhancement: default valuation cost type */
2773
2774 WHERE
2775 ROLLUP_ID = l_rollup_id
2776 AND inventory_item_id = l_item_id
2777 AND organization_id = l_org_id
2778 AND assignment_set_id = l_assignment_set_id
2779 AND source_type = 3
2780 AND item_cost IS NULL
2781 AND buy_cost_flag IS NULL;
2782
2783
2784
2785
2786 x_err_code := 0;
2787 x_err_buf := 'CSTSCBOM.populate_buy_costs:' || 'S' || l_stmt_num || ': SUCCESS';
2788 return;
2789
2790 EXCEPTION
2791 when others then
2792 x_err_code := SQLCODE;
2793 x_err_buf := 'CSTSCBOM.populate_buy_costs:' || 'S' || l_stmt_num || ':'
2794 || substrb(sqlerrm,1,62);
2795 return;
2796
2797
2798 END populate_buy_costs;
2799
2800 /*
2801 Resource_ID for yielded_costs are not required.
2802 Resource_ID is mandatory only for TL Material Overhead. Yielded_cost
2803 can only be PL Material or PL MOH, never TL (Mat or MOH)
2804 Changes for Bug 2482828 have been reversed.
2805 Cost Copy has been modified to validate only the TL MOH subelement
2806 Vinit (Bug2731332)
2807 */
2808
2809 FUNCTION process_sc_rollup_op_yields(ext_precision IN NUMBER,
2810 l_rollup_id IN NUMBER,
2811 conc_flag IN NUMBER,
2812 req_id IN NUMBER,
2813 prgm_appl_id IN NUMBER,
2814 prgm_id IN NUMBER,
2815 l_last_updated_by IN NUMBER,
2816 alt_rtg_designator IN VARCHAR2,
2817 rollup_date IN VARCHAR2,
2818 l_organization_id IN NUMBER,
2819 l_level IN NUMBER,
2820 l_cost_type_id IN NUMBER,
2821 -- Output error message for bug 3097347
2822 x_err_buf OUT NOCOPY VARCHAR2)
2823 return NUMBER IS
2824 x_err_num NUMBER;
2825 x_err_msg VARCHAR2(200);
2826 sql_stmt_num NUMBER := 0;
2827 l_opYldFactor NUMBER := 1;
2828 l_invalid_rows NUMBER := 0;
2829 l_err_msg VARCHAR2(240);
2830 invalid_rows_found EXCEPTION;
2831 CONC_STATUS BOOLEAN;
2832 esa_flag NUMBER;
2833 l_login_id number := -1;
2834 l_rollup_date DATE := fnd_date.canonical_to_date(rollup_date);
2835 dummy number;
2836
2837 TYPE inv_item_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2838 TYPE dep_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2839 TYPE op_seq_id_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2840 TYPE op_seq_num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2841 TYPE op_yield_factor_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2842
2843
2844 l_inv_item_tbl inv_item_tbl_type;
2845 l_dep_id_tbl dep_id_tbl_type;
2846 l_op_seq_id_tbl op_seq_id_tbl_type;
2847 l_op_seq_num_tbl op_seq_num_tbl_type;
2848 l_op_yield_factor_tbl op_yield_factor_tbl_type;
2849
2850
2851 CURSOR opseq_cur IS
2852 SELECT cllc.inventory_item_id INVENTORY_ITEM_ID,
2853 bos.department_id DEPARTMENT_ID,
2854 bos.operation_sequence_id OPERATION_SEQUENCE_ID,
2855 bos.operation_seq_num OPERATION_SEQ_NUM,
2856 ((1/NVL(bos.yield,1))-1) OPERATION_YIELD_FACTOR
2857 FROM bom_operation_sequences bos,
2858 bom_operational_routings bor,
2859 cst_sc_low_level_codes cllc
2860 WHERE cllc.rollup_id = l_rollup_id
2861 AND cllc.low_level_code = l_level
2862 AND bor.assembly_item_id = cllc.inventory_item_id
2863
2864 /* Bug 3152221: use primary routing when we specify an alternate routing which
2865 has not been defined */
2866 AND ( NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, 'none')
2867 = NVL(alt_rtg_designator, 'none')
2868 OR (
2869 (alt_rtg_designator IS NOT NULL)
2870 AND (bor.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
2871 AND NOT EXISTS
2872 (SELECT 'X'
2873 FROM BOM_OPERATIONAL_ROUTINGS bor1
2874 WHERE bor1.ASSEMBLY_ITEM_ID = bor.assembly_item_id
2875 AND bor1.ORGANIZATION_ID = l_organization_id
2876 AND bor1.ALTERNATE_ROUTING_DESIGNATOR =
2877 alt_rtg_designator
2878 )
2879 )
2880 )
2881
2882 and bor.organization_id = l_organization_id
2883 /* Bug 2379908. Use common_routing_sequence_id */
2884 and bos.routing_sequence_id = bor.common_routing_sequence_id
2885 and fnd_date.canonical_to_date(rollup_date) >= bos.effectivity_date
2886
2887 and NVL( bos.disable_date,
2888 fnd_date.canonical_to_date(rollup_date) + 1 )
2889 >= fnd_date.canonical_to_date(rollup_date)
2890
2891 /* Right now, ECO does not support Op Yield */
2892 AND bos.implementation_date is not null
2893 AND NVL( bos.include_in_rollup, 1 ) = 1
2894
2895 AND NVL( bos.eco_for_production, 2 ) = 2
2896 and bos.operation_yield_enabled = 1
2897 and bos.yield <> 1
2898 /* Added join to remove extra rows for op yields in CICD */
2899 and cllc.organization_id = bor.organization_id
2900 order by inventory_item_id,
2901 operation_seq_num;
2902 BEGIN
2903 sql_stmt_num := 5;
2904
2905 select nvl(LAST_UPDATE_LOGIN ,-1 )
2906 into l_login_id
2907 from cst_sc_rollup_history
2908 where rollup_id = l_rollup_id
2909 and rownum =1;
2910
2911
2912 sql_stmt_num := 10;
2913
2914 /* Use OSFM API */
2915
2916 esa_flag := WSMPUTIL.WSM_ESA_ENABLED(NULL,
2917 x_err_num, x_err_msg,
2918 l_organization_id );
2919
2920 OPEN opseq_cur;
2921 LOOP
2922 FETCH opseq_cur BULK COLLECT INTO
2923 l_inv_item_tbl,
2924 l_dep_id_tbl,
2925 l_op_seq_id_tbl,
2926 l_op_seq_num_tbl,
2927 l_op_yield_factor_tbl LIMIT 1000;
2928
2929 IF (esa_flag = 1) then
2930 sql_stmt_num := 30;
2931 FORALL i in l_inv_item_tbl.first..l_inv_item_tbl.last
2932 INSERT INTO CST_ITEM_COST_DETAILS (
2933 inventory_item_id,
2934 organization_id,
2935 SOURCE_ORGANIZATION_ID,
2936 cost_type_id,
2937 last_update_date,
2938 last_updated_by,
2939 creation_date,
2940 created_by,
2941 last_update_login,
2942 operation_sequence_id,
2943 operation_seq_num,
2944 department_id,
2945 level_type,
2946 usage_rate_or_amount,
2947 basis_type,
2948 basis_factor,
2949 net_yield_or_shrinkage_factor,
2950 item_cost,
2951 cost_element_id,
2952 rollup_source_type,
2953 request_id,
2954 program_application_id,
2955 program_id,
2956 program_update_date,
2957 yielded_cost,
2958 resource_id)
2959 SELECT l_inv_item_tbl(i),
2960 l_organization_id,
2961 l_organization_id,
2962 l_cost_type_id,
2963 l_rollup_date,
2964 l_last_updated_by,
2965 l_rollup_date,
2966 l_last_updated_by,
2967 l_login_id,
2968 l_op_seq_id_tbl(i),
2969 l_op_seq_num_tbl(i),
2970 l_dep_id_tbl(i),
2971 decode(cost_element_id,1,2,2,2,1),
2972 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
2973 1,
2974 1,
2975 1,
2976 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
2977 cost_element_id,
2978 3,
2979 decode(conc_flag,1,req_id,NULL),
2980 decode(conc_flag,1,prgm_appl_id,NULL),
2981 decode(conc_flag,1,prgm_id,NULL),
2982 decode(conc_flag,1,sysdate,NULL),
2983 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
2984 NULL
2985 FROM cst_item_cost_details
2986 WHERE organization_id = l_organization_id
2987 AND inventory_item_id = l_inv_item_tbl(i)
2988 AND cost_type_id = l_cost_type_id
2989 AND operation_seq_num <= l_op_seq_num_tbl(i)
2990 GROUP BY cost_element_id;
2991 ELSE
2992 /* If Estimated Scrap Accouting is disabled,
2993 then we need to distinguish between the this level
2994 and previous level operation yield costs */
2995 sql_stmt_num := 35;
2996 FORALL i in l_inv_item_tbl.first..l_inv_item_tbl.last
2997 INSERT into cst_item_cost_details (
2998 inventory_item_id,
2999 organization_id,
3000 SOURCE_ORGANIZATION_ID,
3001 cost_type_id,
3002 last_update_date,
3003 last_updated_by,
3004 creation_date,
3005 created_by,
3006 last_update_login,
3007 operation_sequence_id,
3008 operation_seq_num,
3009 department_id,
3010 level_type,
3011 usage_rate_or_amount,
3012 basis_type,
3013 basis_factor,
3014 net_yield_or_shrinkage_factor,
3015 item_cost,
3016 cost_element_id,
3017 rollup_source_type,
3018 request_id,
3019 program_application_id,
3020 program_id,
3021 program_update_date,
3022 yielded_cost,
3023 resource_id)
3024 SELECT l_inv_item_tbl(i),
3025 l_organization_id,
3026 l_organization_id,
3027 l_cost_type_id,
3028 l_rollup_date,
3029 l_last_updated_by,
3030 l_rollup_date,
3031 l_last_updated_by,
3032 l_login_id,
3033 l_op_seq_id_tbl(i),
3034 l_op_seq_num_tbl(i),
3035 l_dep_id_tbl(i),
3036 decode(cost_element_id,1,2,2,2,level_type),
3037 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
3038 1,
3039 1,
3040 1,
3041 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
3042 cost_element_id,
3043 3,
3044 decode(conc_flag,1,req_id,NULL),
3045 decode(conc_flag,1,prgm_appl_id,NULL),
3046 decode(conc_flag,1,prgm_id,NULL),
3047 decode(conc_flag,1,sysdate,NULL),
3048 round(sum(item_cost*l_op_yield_factor_tbl(i)),ext_precision),
3049 NULL
3050 FROM cst_item_cost_details
3051 WHERE organization_id = l_organization_id
3052 AND inventory_item_id = l_inv_item_tbl(i)
3053 AND cost_type_id = l_cost_type_id
3054 AND operation_seq_num <= l_op_seq_num_tbl(i)
3055 GROUP BY cost_element_id, level_type;
3056 END IF;
3057
3058 sql_stmt_num := 40;
3059
3060 dummy := l_inv_item_tbl.first;
3061
3062 WHILE TRUE LOOP
3063
3064 if (dummy is NULL) then
3065 exit;
3066 end if;
3067
3068 SELECT count(*)
3069 INTO l_invalid_rows
3070 FROM cst_item_cost_details
3071 WHERE organization_id = l_organization_id
3072 AND inventory_item_id = l_inv_item_tbl(dummy)
3073 AND cost_type_id = l_cost_type_id
3074 AND yielded_cost IS NOT NULL
3075 AND yielded_cost <> item_cost
3076 AND rownum=1;
3077
3078 IF (l_invalid_rows > 0) then
3079 raise INVALID_ROWS_FOUND;
3080 END IF;
3081
3082 dummy := l_inv_item_tbl.next (dummy);
3083 END LOOP;
3084
3085 EXIT WHEN opseq_cur%NOTFOUND;
3086 END LOOP;
3087 CLOSE opseq_cur;
3088
3089 return (0);
3090
3091 EXCEPTION
3092 WHEN INVALID_ROWS_FOUND THEN
3093 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';
3094 x_err_buf := l_err_msg; -- Output error message for bug 3097347
3095 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
3096 fnd_file.put_line(fnd_file.log,l_err_msg);
3097 return(sql_stmt_num);
3098
3099 WHEN OTHERS THEN
3100 l_err_msg := 'process_sc_rollup_op_yields: ' || sql_stmt_num || ': ' || substrb(sqlerrm,1,62);
3101 x_err_buf := l_err_msg; -- Output error message for bug 3097347
3102 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
3103 fnd_file.put_line(fnd_file.log,l_err_msg);
3104 return(sql_stmt_num);
3105
3106 END process_sc_rollup_op_yields;
3107
3108 /*-----------------------------------------------------------+
3109 |This Function will create the snapshot of the BOM's used in |
3110 |the supply chain cost rollup process |
3111 +------------------------------------------------------------*/
3112
3113 function supply_chain_snapshot (
3114 l_rollup_id in number,
3115 l_cost_type_id in number,
3116 l_mfg_flag in number,
3117 alt_bom_designator in varchar2,
3118 l_conc_flag in number,
3119 l_unimp_flag in number,
3120 revision_date in varchar2,
3121 l_last_updated_by in number,
3122 l_rollup_date in varchar2,
3123 req_id in number,
3124 p_prg_appl_id in number,
3125 p_prg_id in number,
3126 err_buf out NOCOPY varchar2)
3127 return integer
3128 is
3129 return_code NUMBER;
3130
3131 sql_stmt_num NUMBER := 0;
3132 l_snapshot_designator VARCHAR2(40);
3133 alt_org NUMBER:=0;
3134 p_rollup_date CONSTANT DATE := TO_DATE(l_rollup_date,'YYYY/MM/DD HH24:MI:SS');
3135 p_revision_date CONSTANT DATE := TO_DATE(revision_date,'YYYY/MM/DD HH24:MI:SS');
3136 p_canonical_rev_date CONSTANT DATE := fnd_date.canonical_to_date(revision_date);
3137 l_req_id NUMBER;
3138 l_prg_appl_id NUMBER;
3139 l_prg_id NUMBER;
3140 l_prg_update_date DATE;
3141
3142 BEGIN
3143 return_code := 8888;
3144
3145 IF (l_conc_flag = 1)THEN
3146 l_req_id := req_id;
3147 l_prg_appl_id := p_prg_appl_id;
3148 l_prg_id := p_prg_id;
3149 l_prg_update_Date := p_rollup_date;
3150
3151 ELSE
3152 l_req_id := NULL;
3153 l_prg_appl_id := NULL;
3154 l_prg_id := NULL;
3155 l_prg_update_date := NULL;
3156
3157 END IF;
3158
3159
3160 sql_stmt_num := 5;
3161 select alternate_bom_designator
3162 into l_snapshot_designator
3163 from cst_cost_types
3164 where cost_type_id = l_cost_type_id;
3165
3166 if (l_snapshot_designator IS NULL) then
3167 return(return_code);
3168 end if;
3169
3170
3171 sql_stmt_num := 10;
3172 if l_snapshot_designator <> NVL(alt_bom_designator,(l_snapshot_designator||'_NOT')) THEN
3173 DELETE BOM_INVENTORY_COMPONENTS bic
3174 WHERE bic.BILL_SEQUENCE_ID IN
3175 (SELECT bic2.BILL_SEQUENCE_ID
3176 FROM BOM_INVENTORY_COMPONENTS bic2,
3177 BOM_BILL_OF_MATERIALS bbom,
3178 CST_SC_LOW_LEVEL_CODES csllc,
3179 cst_sc_sourcing_rules cssr
3180 WHERE csllc.ROLLUP_ID = l_rollup_id
3181 AND cssr.rollup_id (+)= l_rollup_id
3182 AND cssr.inventory_item_id (+)= csllc.inventory_item_id
3183 AND cssr.organization_id (+)= csllc.organization_id
3184 AND decode(cssr.source_type,2,1,NULL,1,0)=1
3185 AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
3186 AND bbom.ASSEMBLY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3187 AND bbom.ORGANIZATION_ID = csllc.organization_id
3188 AND bbom.ALTERNATE_BOM_DESIGNATOR =l_snapshot_designator
3189 AND bic2.BILL_SEQUENCE_ID = bbom.BILL_SEQUENCE_ID
3190 );
3191 END IF;
3192
3193 sql_stmt_num := 15;
3194 DELETE BOM_REFERENCE_DESIGNATORS brd
3195 WHERE NOT EXISTS
3196 (SELECT 'Component Header exists'
3197 FROM BOM_INVENTORY_COMPONENTS bic
3198 WHERE bic.COMPONENT_SEQUENCE_ID = brd.COMPONENT_SEQUENCE_ID);
3199
3200 sql_stmt_num := 20;
3201 DELETE BOM_SUBSTITUTE_COMPONENTS bsc
3202 WHERE NOT EXISTS
3203 (SELECT 'Component Header exists'
3204 FROM BOM_INVENTORY_COMPONENTS bic
3205 WHERE bic.COMPONENT_SEQUENCE_ID = bsc.COMPONENT_SEQUENCE_ID);
3206
3207 /* ADD SQL TO DELETE FROM THE BOM_BILL_OF_MATERIALS. */
3208 /*This should be done to update the WHO column of the table*/
3209 if l_snapshot_designator <> NVL(alt_bom_designator,(l_snapshot_designator||'_NOT')) THEN
3210 sql_stmt_num:=25;
3211 Delete BOM_BILL_OF_MATERIALS bbom
3212 WHERE bbom.BILL_SEQUENCE_ID IN
3213 (SELECT bbom2.BILL_SEQUENCE_ID
3214 FROM BOM_BILL_OF_MATERIALS bbom2,
3215 CST_SC_LOW_LEVEL_CODES csllc,
3216 cst_item_costs cia,
3217 cst_sc_sourcing_rules cssr
3218 WHERE csllc.ROLLUP_ID = l_rollup_id
3219 AND cssr.rollup_id (+)= l_rollup_id
3220 AND cssr.inventory_item_id (+)= csllc.inventory_item_id
3221 AND cssr.organization_id (+)= csllc.organization_id
3222 AND decode(cssr.source_type,2,1,NULL,1,0)=1
3223 AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
3224 AND bbom2.ASSEMBLY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3225 AND bbom2.ORGANIZATION_ID = csllc.organization_id
3226 AND bbom2.ALTERNATE_BOM_DESIGNATOR =l_snapshot_designator
3227 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3228 AND cia.ORGANIZATION_ID = csllc.organization_id
3229 AND cia.COST_TYPE_ID = l_cost_type_id
3230 AND cia.BASED_ON_ROLLUP_FLAG = 1
3231 );
3232 END IF;
3233
3234 sql_stmt_num := 30;
3235 INSERT INTO BOM_BILL_OF_MATERIALS(
3236 assembly_item_id,
3237 organization_id,
3238 alternate_bom_designator,
3239 last_update_date,
3240 last_updated_by,
3241 creation_date,
3242 created_by,
3243 last_update_login,
3244 specific_assembly_comment,
3245 pending_from_ecn,
3246 attribute_category,
3247 attribute1,
3248 attribute2,
3249 attribute3,
3250 attribute4,
3251 attribute5,
3252 attribute6,
3253 attribute7,
3254 attribute8,
3255 attribute9,
3256 attribute10,
3257 attribute11,
3258 attribute12,
3259 attribute13,
3260 attribute14,
3261 attribute15,
3262 assembly_type,
3263 bill_sequence_id,
3264 common_bill_sequence_id,
3265 request_id,
3266 program_application_id,
3267 program_id,
3268 program_update_date,
3269 pk1_value,
3270 pk2_value,
3271 source_bill_sequence_id
3272 )
3273 select
3274 bbom.assembly_item_id,
3275 bbom.organization_id,
3276 l_snapshot_designator,
3277 p_rollup_date,
3278 l_last_updated_by,
3279 p_rollup_date,
3280 l_last_updated_by,
3281 l_last_updated_by,
3282 bbom.specific_assembly_comment,
3283 bbom.pending_from_ecn,
3284 bbom.attribute_category,
3285 bbom.attribute1,
3286 bbom.attribute2,
3287 bbom.attribute3,
3288 bbom.attribute4,
3289 bbom.attribute5,
3290 bbom.attribute6,
3291 bbom.attribute7,
3292 bbom.attribute8,
3293 bbom.attribute9,
3294 bbom.attribute10,
3295 bbom.attribute11,
3296 bbom.attribute12,
3297 bbom.attribute13,
3298 bbom.attribute14,
3299 bbom.attribute15,
3300 bbom.assembly_type,
3301 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
3302 bbom.common_bill_sequence_id,
3303 l_req_id,
3304 l_prg_appl_id,
3305 l_prg_id,
3306 l_prg_update_date,
3307 bbom.assembly_item_id,
3308 bbom.organization_id,
3309 BOM_INVENTORY_COMPONENTS_S.NEXTVAL
3310 FROM BOM_BILL_OF_MATERIALS bbom,
3311 CST_ITEM_COSTS cia,
3312 CST_SC_LOW_LEVEL_CODES csllc,
3313 cst_sc_sourcing_rules cssr
3314 WHERE csllc.ROLLUP_ID = l_rollup_id
3315 AND cssr.rollup_id(+) = l_rollup_id
3316 AND cssr.inventory_item_id (+)= csllc.inventory_item_id
3317 AND cssr.organization_id (+)= csllc.organization_id
3318 AND decode(cssr.source_type,2,1,NULL,1,0)=1
3319 AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
3320 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3321 AND cia.ORGANIZATION_ID = csllc.organization_id
3322 AND cia.COST_TYPE_ID = l_cost_type_id
3323 AND cia.BASED_ON_ROLLUP_FLAG = 1
3324 AND bbom.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
3325 AND bbom.ORGANIZATION_ID = csllc.organization_id
3326 AND((l_mfg_flag = 1
3327 AND
3328 bbom.ASSEMBLY_TYPE = 1)
3329 OR
3330 (l_mfg_flag = 2)
3331 )
3332 AND( (NVL(bbom.ALTERNATE_BOM_DESIGNATOR, 'NONE') = NVL(alt_bom_designator, 'NONE'))
3333 OR ((alt_bom_designator IS NOT NULL)
3334 AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL)
3335 AND NOT EXISTS
3336 (SELECT 'X'
3337 FROM BOM_BILL_OF_MATERIALS bbom1
3338 WHERE bbom.ASSEMBLY_ITEM_ID = bbom1.ASSEMBLY_ITEM_ID
3339 AND bbom.ORGANIZATION_ID = bbom1.ORGANIZATION_ID
3340 AND bbom1.ALTERNATE_BOM_DESIGNATOR = alt_bom_designator
3341 AND((l_mfg_flag = 1
3342 AND
3343 bbom1.ASSEMBLY_TYPE = 1)
3344 OR
3345 (l_mfg_flag = 2)
3346 )
3347 ))
3348 )
3349 AND EXISTS(
3350 select 'ALternate Exist in the organizations'
3351 from bom_alternate_designators bad
3352 where bad.organization_id = csllc.organization_id
3353 and bad.alternate_designator_code = l_snapshot_designator
3354 )
3355 AND NOT EXISTS(
3356 select 'Bom exixts with alternate as snapshot'
3357 from bom_bill_of_materials bbom2
3358 where bbom2.assembly_item_id = cia.inventory_item_id
3359 and bbom2.organization_id = csllc.organization_id
3360 and bbom2.alternate_bom_designator = l_snapshot_designator
3361 );
3362
3363 sql_stmt_num := 40;
3364 INSERT INTO BOM_INVENTORY_COMPONENTS
3365 (
3366 OPERATION_SEQ_NUM,
3367 COMPONENT_ITEM_ID,
3368 LAST_UPDATE_DATE,
3369 LAST_UPDATED_BY,
3370 CREATION_DATE,
3371 CREATED_BY,
3372 LAST_UPDATE_LOGIN,
3373 ITEM_NUM,
3374 BASIS_TYPE,
3375 COMPONENT_QUANTITY,
3376 COMPONENT_YIELD_FACTOR,
3377 COMPONENT_REMARKS,
3378 EFFECTIVITY_DATE,
3379 CHANGE_NOTICE,
3380 IMPLEMENTATION_DATE,
3381 DISABLE_DATE,
3382 ATTRIBUTE_CATEGORY,
3383 ATTRIBUTE1,
3384 ATTRIBUTE2,
3385 ATTRIBUTE3,
3386 ATTRIBUTE4,
3387 ATTRIBUTE5,
3388 ATTRIBUTE6,
3389 ATTRIBUTE7,
3390 ATTRIBUTE8,
3391 ATTRIBUTE9,
3392 ATTRIBUTE10,
3393 ATTRIBUTE11,
3394 ATTRIBUTE12,
3395 ATTRIBUTE13,
3396 ATTRIBUTE14,
3397 ATTRIBUTE15,
3398 PLANNING_FACTOR,
3399 QUANTITY_RELATED,
3400 SO_BASIS,
3401 OPTIONAL,
3402 MUTUALLY_EXCLUSIVE_OPTIONS,
3403 INCLUDE_IN_COST_ROLLUP,
3404 CHECK_ATP,
3405 SHIPPING_ALLOWED,
3406 REQUIRED_TO_SHIP,
3407 REQUIRED_FOR_REVENUE,
3408 INCLUDE_ON_SHIP_DOCS,
3409 INCLUDE_ON_BILL_DOCS,
3410 LOW_QUANTITY,
3411 HIGH_QUANTITY,
3412 ACD_TYPE,
3413 OLD_COMPONENT_SEQUENCE_ID,
3414 COMPONENT_SEQUENCE_ID,
3415 BILL_SEQUENCE_ID,
3416 REQUEST_ID,
3417 PROGRAM_APPLICATION_ID,
3418 PROGRAM_ID,
3419 PROGRAM_UPDATE_DATE,
3420 WIP_SUPPLY_TYPE,
3421 OPERATION_LEAD_TIME_PERCENT,
3422 REVISED_ITEM_SEQUENCE_ID,
3423 SUPPLY_LOCATOR_ID,
3424 SUPPLY_SUBINVENTORY,
3425 PICK_COMPONENTS,
3426 BOM_ITEM_TYPE)
3427 SELECT
3428 bic.OPERATION_SEQ_NUM,
3429 bic.COMPONENT_ITEM_ID,
3430 p_rollup_date,
3431 l_last_updated_by,
3432 p_rollup_date,
3433 l_last_updated_by,
3434 l_last_updated_by,
3435 bic.ITEM_NUM,
3436 bic.BASIS_TYPE,
3437 bic.COMPONENT_QUANTITY,
3438 bic.COMPONENT_YIELD_FACTOR,
3439 bic.COMPONENT_REMARKS,
3440 p_revision_date,
3441 NULL,
3442 p_revision_date,
3443 NULL,
3444 bic.ATTRIBUTE_CATEGORY,
3445 bic.ATTRIBUTE1,
3446 bic.ATTRIBUTE2,
3447 bic.ATTRIBUTE3,
3448 bic.ATTRIBUTE4,
3449 bic.ATTRIBUTE5,
3450 bic.ATTRIBUTE6,
3451 bic.ATTRIBUTE7,
3452 bic.ATTRIBUTE8,
3453 bic.ATTRIBUTE9,
3454 bic.ATTRIBUTE10,
3455 bic.ATTRIBUTE11,
3456 bic.ATTRIBUTE12,
3457 bic.ATTRIBUTE13,
3458 bic.ATTRIBUTE14,
3459 bic.ATTRIBUTE15,
3460 bic.PLANNING_FACTOR,
3461 bic.QUANTITY_RELATED,
3462 bic.SO_BASIS,
3463 bic.OPTIONAL,
3464 bic.MUTUALLY_EXCLUSIVE_OPTIONS,
3465 bic.INCLUDE_IN_COST_ROLLUP,
3466 bic.CHECK_ATP,
3467 bic.SHIPPING_ALLOWED,
3468 bic.REQUIRED_TO_SHIP,
3469 bic.REQUIRED_FOR_REVENUE,
3470 bic.INCLUDE_ON_SHIP_DOCS,
3471 bic.INCLUDE_ON_BILL_DOCS,
3472 bic.LOW_QUANTITY,
3473 bic.HIGH_QUANTITY,
3474 NULL, /* ACD_TYPE */
3475 NULL,
3476 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
3477 bbom2.BILL_SEQUENCE_ID,
3478 DECODE(l_conc_flag, 1, req_id, NULL),
3479 DECODE(l_conc_flag, 1, p_prg_appl_id, NULL),
3480 DECODE(l_conc_flag, 1, p_prg_id, NULL),
3481 DECODE(l_conc_flag, 1,
3482 p_rollup_date, NULL),
3483 bic.WIP_SUPPLY_TYPE,
3484 bic.OPERATION_LEAD_TIME_PERCENT,
3485 NULL,
3486 bic.SUPPLY_LOCATOR_ID,
3487 bic.SUPPLY_SUBINVENTORY,
3488 bic.PICK_COMPONENTS,
3489 bic.BOM_ITEM_TYPE
3490 FROM BOM_BILL_OF_MATERIALS bbom,
3491 BOM_BILL_OF_MATERIALS bbom2,
3492 bom_inventory_components bic,
3493 CST_ITEM_COSTS cia,
3494 CST_SC_LOW_LEVEL_CODES csllc,
3495 cst_sc_sourcing_rules cssr
3496 WHERE csllc.ROLLUP_ID = l_rollup_id
3497 AND cssr.rollup_id(+) = l_rollup_id
3498 AND cssr.inventory_item_id (+)= csllc.inventory_item_id
3499 AND cssr.organization_id (+)= csllc.organization_id
3500 AND decode(cssr.source_type,2,1,NULL,1,0)=1
3501 AND decode(cssr.allocation_percent,0,0,NULL,1,1)=1
3502 AND cia.INVENTORY_ITEM_ID = csllc.INVENTORY_ITEM_ID
3503 AND cia.ORGANIZATION_ID = csllc.organization_id
3504 AND cia.COST_TYPE_ID = l_cost_type_id
3505 AND cia.BASED_ON_ROLLUP_FLAG = 1
3506 AND bbom.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
3507 AND bbom.ORGANIZATION_ID = csllc.organization_id
3508 AND( (NVL(bbom.ALTERNATE_BOM_DESIGNATOR, 'NONE') = NVL(alt_bom_designator, 'NONE')
3509 AND (nvl(l_snapshot_designator,'NONE') <> NVL(alt_bom_designator,'NONE')))
3510 OR ((alt_bom_designator IS NOT NULL)
3511 AND (bbom.ALTERNATE_BOM_DESIGNATOR IS NULL)
3512 AND (NOT EXISTS
3513 (SELECT 'X'
3514 FROM BOM_BILL_OF_MATERIALS bbom1
3515 WHERE bbom.ASSEMBLY_ITEM_ID = bbom1.ASSEMBLY_ITEM_ID
3516 AND bbom.ORGANIZATION_ID = bbom1.ORGANIZATION_ID
3517 AND bbom1.ALTERNATE_BOM_DESIGNATOR =
3518 alt_bom_designator
3519 AND((l_mfg_flag = 1
3520 AND
3521 bbom1.ASSEMBLY_TYPE = 1)
3522 OR
3523 (l_mfg_flag = 2)
3524 )
3525 )
3526 or (NVL(l_snapshot_designator,'NONE') = NVL(alt_bom_designator,'NONE'))))
3527 )
3528 AND bic.bill_sequence_id = bbom.common_bill_sequence_id
3529 AND NVL( bic.eco_for_production, 2 ) = 2
3530
3531 /* Fix for BUG 1604207 */
3532 AND NVL( bic.acd_type, 1 ) <> 3
3533
3534 AND bbom2.ASSEMBLY_ITEM_ID = cia.INVENTORY_ITEM_ID
3535 AND bbom2.ORGANIZATION_ID = csllc.organization_id
3536 AND bbom2.ALTERNATE_BOM_DESIGNATOR = l_snapshot_designator
3537 AND bic.EFFECTIVITY_DATE <=fnd_date.canonical_to_date(revision_date)
3538 AND NVL(bic.DISABLE_DATE,fnd_date.canonical_to_date(revision_date)+1)> fnd_date.canonical_to_date(revision_date)
3539 AND(((l_unimp_flag = 2)AND(bic.IMPLEMENTATION_DATE IS NOT NULL))
3540 OR((l_unimp_flag = 1)
3541 AND bic.EFFECTIVITY_DATE = (
3542 SELECT MAX(bic1.EFFECTIVITY_DATE)
3543 FROM BOM_INVENTORY_COMPONENTS bic1
3544 WHERE ((bic1.OPERATION_SEQ_NUM = bic.OPERATION_SEQ_NUM)
3545 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))
3546 )
3547 AND bic1.BILL_SEQUENCE_ID = bic.BILL_SEQUENCE_ID
3548 AND bic1.COMPONENT_ITEM_ID = bic.COMPONENT_ITEM_ID
3549 AND bic1.EFFECTIVITY_DATE <=fnd_date.canonical_to_date(revision_date)
3550 AND NVL( bic1.eco_for_production, 2 ) = 2
3551 )
3552 )
3553 )
3554 AND EXISTS (
3555 select 'ALternate Exist in the organization'
3556 from bom_alternate_designators bad
3557 where bad.organization_id = csllc.organization_id
3558 and bad.alternate_designator_code = l_snapshot_designator
3559 )
3560 AND NOT EXISTS(
3561 select 'Bom exists with alternate as snapshot'
3562 from bom_inventory_components bic2,
3563 bom_bill_of_materials bbom3
3564 where bbom3.organization_id = csllc.organization_id
3565 and bbom3.assembly_item_id = cia.inventory_item_id
3566 and bbom3.alternate_bom_designator = l_snapshot_designator
3567 and bic2.bill_sequence_id = bbom3.bill_sequence_id
3568 );
3569
3570 /* Update the common_bill_sequence_id to bill_sequence_id for the all */
3571 /* the asemblies headers were created for */
3572
3573 sql_stmt_num := 60;
3574 update bom_bill_of_materials bbom
3575 set bbom.common_bill_sequence_id = bbom.bill_sequence_id
3576 where EXISTS(
3577 select 1
3578 from cst_sc_low_level_codes csllc,
3579 cst_sc_sourcing_rules cssr
3580 where csllc.rollup_id = l_rollup_id
3581 and cssr.rollup_id(+) = l_rollup_id
3582 and cssr.inventory_item_id (+)= csllc.inventory_item_id
3583 and cssr.organization_id (+)= csllc.organization_id
3584 and decode(cssr.source_type,2,1,null,1,0)=1
3585 and decode(cssr.allocation_percent,0,0,null,1,1)=1
3586 and bbom.organization_id = csllc.organization_id
3587 and bbom.assembly_item_id = csllc.inventory_item_id
3588 )
3589 and bbom.alternate_bom_designator = l_snapshot_designator
3590 and (nvl(l_snapshot_designator,'NONE') <> nvl(alt_bom_designator,'NONE')
3591 or
3592 bbom.common_bill_sequence_id = (select common_bill_sequence_id
3593 from bom_bill_of_materials bbom2
3594 where bbom2.assembly_item_id = bbom.assembly_item_id
3595 and bbom2.organization_id = bbom.organization_id
3596 and bbom2.alternate_bom_designator is null
3597 )
3598 );
3599 return_code := 0;
3600 return (return_code);
3601
3602 EXCEPTION
3603 when others then
3604 return_code := SQLCODE;
3605 err_buf := 'SUPPLY_CHAIN_SNAPSHOT:' || 'S' || sql_stmt_num || ':'
3606 || substrb(sqlerrm,1,62);
3607 return(return_code);
3608 END supply_chain_snapshot;
3609 END CSTPSCCR;